<?xml version="1.0" encoding="UTF-8"?>
<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="no_inf_presupuestal" language="groovy" pageWidth="612" pageHeight="792" whenNoDataType="NoDataSection" columnWidth="572" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20">
	<property name="ireport.zoom" value="1.948717100000003"/>
	<property name="ireport.x" value="0"/>
	<property name="ireport.y" value="0"/>
	<parameter name="id_periodo_filtro" class="java.lang.String">
		<defaultValueExpression><![CDATA[]]></defaultValueExpression>
	</parameter>
	<parameter name="whereClasePlanta" class="java.lang.String">
		<defaultValueExpression><![CDATA[""]]></defaultValueExpression>
	</parameter>
	<parameter name="desc_clase" class="java.lang.String">
		<defaultValueExpression><![CDATA[]]></defaultValueExpression>
	</parameter>
	<parameter name="tag_rete" class="java.math.BigDecimal">
		<defaultValueExpression><![CDATA[0]]></defaultValueExpression>
	</parameter>
	<parameter name="comando" class="java.lang.String">
		<defaultValueExpression><![CDATA[]]></defaultValueExpression>
	</parameter>
	<parameter name="wherePago" class="java.lang.String">
		<defaultValueExpression><![CDATA[""]]></defaultValueExpression>
	</parameter>
	<queryString>
		<![CDATA[select
tab.rubro,
tab.des_rub,
sum(tab.valor) as valor,
tab.periodo

from(

      select
      rubro.rubro,
      rubro.descripcion as des_rub,
      informe.id_periodo,
      informe.tipcod,
      informe.tipo,
      informe.tipcod || ' - ' || informe.tipo as completo_tipo,
      informe.codigo,
      informe.descripcion,
      informe.valor,
      funcionarios.cantidad as cantidad,
      (
      select distinct periodo.periodo
      from no_periodo_v periodo
      where periodo.id_periodo = $P{id_periodo_filtro}
      ) as periodo

      from(

      select id_rubro,id_concep_nomina,id_periodo,tipcod,tipo,codigo,descripcion, sum(valor_liquidado) valor
      from(

      select
      concep.id_rubro,
      concep.operador,
      liquida.id_concep_nomina,
      liquida.id_periodo,tipo.codigo as tipcod,tipo.descripcion as tipo,
      concep.codigo,concep.descripcion,
      --liquida.valor_liquidado,
      (case when concep.operador = 1 then liquida.valor_liquidado else liquida.valor_liquidado*(-1) end) as valor_liquidado,
      liquida.num_doc
      from no_liquida_nomina liquida
      left join no_concep_nomina concep on concep.id_concep_nomina = liquida.id_concep_nomina and concep.id_tipo_concepto_org = 1
      left join no_tipo_concepto tipo on tipo.id_tipo_concepto = concep.id_tipo_concepto
      left join (

                select nom.*,
                case when tab.num_doc is not null then 1 else 0 end as pago
                from no_nombramiento_v nom

                left join (select distinct
                pago.id_tipo_doc,
                pago.num_doc
                from no_pago_func pago
                where
                (select fecha_fin from no_periodo where id_periodo = $P{id_periodo_filtro}) between pago.fecha_activacion and nvl(pago.fecha_terminacion, (select fecha_fin from no_periodo where id_periodo = $P{id_periodo_filtro}))
                )tab
                on tab.id_tipo_doc = nom.id_tipo_doc and tab.num_doc = nom.num_doc

          )tab0
          on tab0.id_tipo_doc = liquida.id_tipo_doc and tab0.num_doc = liquida.num_doc

      where liquida.id_periodo = $P{id_periodo_filtro} and tipo.id_tipo_concepto!=4
      $P!{whereClasePlanta} $P!{wherePago}
      --filtro func con retefuente - 20140710
      and (
      0 = $P{tag_rete}
      or
      liquida.num_doc in (select distinct tab.num_doc from no_liquida_nomina tab where tab.id_periodo = 41 and tab.id_concep_nomina = (select no_param_concep.id_concep_rete from no_param_concep))
      )

      )group by id_periodo,tipcod,tipo,codigo,descripcion, id_concep_nomina, id_rubro

      )informe

      left join(

      select concep.codigo,count(liquida.num_doc) cantidad
      from no_liquida_nomina liquida
      left join no_concep_nomina concep on concep.id_concep_nomina = liquida.id_concep_nomina and concep.id_tipo_concepto_org = 1
      left join no_tipo_concepto tipo on tipo.id_tipo_concepto = concep.id_tipo_concepto
      left join (

                select nom.*,
                case when tab.num_doc is not null then 1 else 0 end as pago
                from no_nombramiento_v nom

                left join (select distinct
                pago.id_tipo_doc,
                pago.num_doc
                from no_pago_func pago
                where
                (select fecha_fin from no_periodo where id_periodo = $P{id_periodo_filtro}) between pago.fecha_activacion and nvl(pago.fecha_terminacion, (select fecha_fin from no_periodo where id_periodo = $P{id_periodo_filtro}))
                )tab
                on tab.id_tipo_doc = nom.id_tipo_doc and tab.num_doc = nom.num_doc

          )tab0
          on tab0.id_tipo_doc = liquida.id_tipo_doc and tab0.num_doc = liquida.num_doc

      where liquida.id_periodo = $P{id_periodo_filtro} and tipo.id_tipo_concepto!=4
      $P!{whereClasePlanta} $P!{wherePago}
      --filtro func con retefuente - 20140710
      and (
      0 = $P{tag_rete}
      or
      liquida.num_doc in (select distinct tab.num_doc from no_liquida_nomina tab where tab.id_periodo = $P{id_periodo_filtro} and tab.id_concep_nomina = (select no_param_concep.id_concep_rete from no_param_concep))
      )

      group by concep.codigo

      )funcionarios on funcionarios.codigo = informe.codigo

      left join PR_RUBRO_PRESUP_V rubro
      on rubro.id_rubro = informe.id_rubro

)tab

where
tab.rubro is not null
--and tab.valor > 0

group by
tab.rubro,
tab.des_rub,
tab.periodo

order by
tab.rubro]]>
	</queryString>
	<field name="RUBRO" class="java.lang.String"/>
	<field name="DES_RUB" class="java.lang.String"/>
	<field name="VALOR" class="java.math.BigDecimal"/>
	<field name="PERIODO" class="java.lang.String"/>
	<variable name="total" class="java.math.BigDecimal" calculation="Sum">
		<variableExpression><![CDATA[$F{VALOR}]]></variableExpression>
	</variable>
	<background>
		<band splitType="Stretch"/>
	</background>
	<title>
		<band height="80" splitType="Stretch">
			<textField pattern="dd-MMM-yyyy   hh:mm aaa">
				<reportElement mode="Opaque" x="452" y="40" width="120" height="12" forecolor="#000000" backcolor="#FFFFFF"/>
				<textElement textAlignment="Right" verticalAlignment="Middle" rotation="None" markup="none">
					<font fontName="SansSerif" size="8" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
				</textElement>
				<textFieldExpression class="java.util.Date"><![CDATA[new java.util.Date()]]></textFieldExpression>
			</textField>
			<staticText>
				<reportElement x="141" y="0" width="311" height="40"/>
				<textElement textAlignment="Center" verticalAlignment="Bottom" markup="none">
					<font fontName="SansSerif" size="12" isBold="true"/>
				</textElement>
				<text><![CDATA[Sistema General de Nómina
Informe Rubro Presupuestal]]></text>
			</staticText>
			<image>
				<reportElement x="0" y="10" width="140" height="40"/>
				<imageExpression class="java.lang.String"><![CDATA["repo:LogoEmpresa003.jpg"]]></imageExpression>
			</image>
			<staticText>
				<reportElement mode="Opaque" x="452" y="28" width="120" height="12" forecolor="#000000" backcolor="#FFFFFF"/>
				<textElement textAlignment="Right" verticalAlignment="Middle" rotation="None" markup="none">
					<font fontName="SansSerif" size="8" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
				</textElement>
				<text><![CDATA[Fecha de Impresión:]]></text>
			</staticText>
			<textField>
				<reportElement x="141" y="40" width="311" height="20"/>
				<textElement textAlignment="Center" verticalAlignment="Middle">
					<font size="12" isBold="true"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{PERIODO}]]></textFieldExpression>
			</textField>
			<textField isBlankWhenNull="true">
				<reportElement x="141" y="60" width="311" height="20" isRemoveLineWhenBlank="true"/>
				<textElement textAlignment="Center" verticalAlignment="Middle">
					<font size="12" isBold="true"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$P{desc_clase}]]></textFieldExpression>
			</textField>
		</band>
	</title>
	<pageHeader>
		<band height="20" splitType="Stretch"/>
	</pageHeader>
	<columnHeader>
		<band height="10" splitType="Stretch">
			<staticText>
				<reportElement mode="Opaque" x="41" y="0" width="100" height="10" forecolor="#000000" backcolor="#88C2F9"/>
				<box>
					<pen lineWidth="0.75"/>
					<topPen lineWidth="0.75"/>
					<leftPen lineWidth="0.75"/>
					<bottomPen lineWidth="0.75"/>
					<rightPen lineWidth="0.75"/>
				</box>
				<textElement textAlignment="Center" verticalAlignment="Middle" rotation="None" markup="none">
					<font fontName="SansSerif" size="8" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
				</textElement>
				<text><![CDATA[Rubro]]></text>
			</staticText>
			<staticText>
				<reportElement mode="Opaque" x="141" y="0" width="311" height="10" forecolor="#000000" backcolor="#88C2F9"/>
				<box>
					<pen lineWidth="0.75"/>
					<topPen lineWidth="0.75"/>
					<leftPen lineWidth="0.75"/>
					<bottomPen lineWidth="0.75"/>
					<rightPen lineWidth="0.75"/>
				</box>
				<textElement textAlignment="Center" verticalAlignment="Middle" rotation="None" markup="none">
					<font fontName="SansSerif" size="8" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
				</textElement>
				<text><![CDATA[Descripción]]></text>
			</staticText>
			<staticText>
				<reportElement mode="Opaque" x="452" y="0" width="100" height="10" forecolor="#000000" backcolor="#88C2F9"/>
				<box>
					<pen lineWidth="0.75"/>
					<topPen lineWidth="0.75"/>
					<leftPen lineWidth="0.75"/>
					<bottomPen lineWidth="0.75"/>
					<rightPen lineWidth="0.75"/>
				</box>
				<textElement textAlignment="Center" verticalAlignment="Middle" rotation="None" markup="none">
					<font fontName="SansSerif" size="8" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
				</textElement>
				<text><![CDATA[Valor]]></text>
			</staticText>
		</band>
	</columnHeader>
	<detail>
		<band height="12" splitType="Stretch">
			<textField pattern="" isBlankWhenNull="true">
				<reportElement mode="Transparent" x="41" y="0" width="100" height="12" forecolor="#000000" backcolor="#FFFFFF"/>
				<box>
					<pen lineWidth="0.75"/>
					<topPen lineWidth="0.75"/>
					<leftPen lineWidth="0.75"/>
					<bottomPen lineWidth="0.75"/>
					<rightPen lineWidth="0.75"/>
				</box>
				<textElement textAlignment="Center" verticalAlignment="Middle" rotation="None" markup="none">
					<font fontName="SansSerif" size="6" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{RUBRO}]]></textFieldExpression>
			</textField>
			<textField pattern="" isBlankWhenNull="true">
				<reportElement mode="Transparent" x="141" y="0" width="311" height="12" forecolor="#000000" backcolor="#FFFFFF"/>
				<box leftPadding="2">
					<pen lineWidth="0.75"/>
					<topPen lineWidth="0.75"/>
					<leftPen lineWidth="0.75"/>
					<bottomPen lineWidth="0.75"/>
					<rightPen lineWidth="0.75"/>
				</box>
				<textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none">
					<font fontName="SansSerif" size="6" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{DES_RUB}]]></textFieldExpression>
			</textField>
			<textField pattern="#,##0.00;(#,##0.00)" isBlankWhenNull="true">
				<reportElement mode="Transparent" x="452" y="0" width="100" height="12" forecolor="#000000" backcolor="#FFFFFF"/>
				<box rightPadding="2">
					<pen lineWidth="0.75"/>
					<topPen lineWidth="0.75"/>
					<leftPen lineWidth="0.75"/>
					<bottomPen lineWidth="0.75"/>
					<rightPen lineWidth="0.75"/>
				</box>
				<textElement textAlignment="Right" verticalAlignment="Middle" rotation="None" markup="none">
					<font fontName="SansSerif" size="6" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
				</textElement>
				<textFieldExpression class="java.math.BigDecimal"><![CDATA[$F{VALOR}]]></textFieldExpression>
			</textField>
		</band>
	</detail>
	<columnFooter>
		<band height="23" splitType="Stretch"/>
	</columnFooter>
	<pageFooter>
		<band height="24" splitType="Stretch">
			<textField>
				<reportElement mode="Opaque" x="211" y="12" width="89" height="12" forecolor="#575757" backcolor="#FFFFFF"/>
				<textElement textAlignment="Right" verticalAlignment="Middle" rotation="None" markup="none">
					<font fontName="SansSerif" size="6" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA["Página "+$V{PAGE_NUMBER}+" de "]]></textFieldExpression>
			</textField>
			<textField evaluationTime="Report" pattern="" isBlankWhenNull="false">
				<reportElement mode="Opaque" x="301" y="12" width="60" height="12" forecolor="#575757" backcolor="#FFFFFF"/>
				<textElement verticalAlignment="Middle" rotation="None" markup="none">
					<font fontName="SansSerif" size="6" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$V{PAGE_NUMBER}]]></textFieldExpression>
			</textField>
			<staticText>
				<reportElement mode="Opaque" x="211" y="0" width="150" height="12" forecolor="#575757"/>
				<textElement textAlignment="Center" verticalAlignment="Middle">
					<font size="6"/>
				</textElement>
				<text><![CDATA[Websafi - Nómina]]></text>
			</staticText>
		</band>
	</pageFooter>
	<summary>
		<band height="92" splitType="Stretch">
			<textField>
				<reportElement mode="Opaque" x="41" y="0" width="411" height="12" backcolor="#D2D8E0"/>
				<box leftPadding="2">
					<pen lineWidth="0.75"/>
					<topPen lineWidth="0.75"/>
					<leftPen lineWidth="0.75"/>
					<bottomPen lineWidth="0.75"/>
					<rightPen lineWidth="0.75"/>
				</box>
				<textElement verticalAlignment="Middle">
					<font size="8" isBold="true"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA["Total "]]></textFieldExpression>
			</textField>
			<textField pattern="#,##0.00;(#,##0.00)" isBlankWhenNull="true">
				<reportElement mode="Transparent" x="452" y="0" width="100" height="12" forecolor="#000000" backcolor="#FFFFFF"/>
				<box rightPadding="2">
					<pen lineWidth="0.75"/>
					<topPen lineWidth="0.75"/>
					<leftPen lineWidth="0.75"/>
					<bottomPen lineWidth="0.75"/>
					<rightPen lineWidth="0.75"/>
				</box>
				<textElement textAlignment="Right" verticalAlignment="Middle" rotation="None" markup="none">
					<font fontName="SansSerif" size="6" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
				</textElement>
				<textFieldExpression class="java.math.BigDecimal"><![CDATA[$V{total}]]></textFieldExpression>
			</textField>
			<subreport>
				<reportElement stretchType="RelativeToTallestObject" x="302" y="30" width="250" height="62"/>
				<subreportParameter name="comando">
					<subreportParameterExpression><![CDATA[$P{comando}]]></subreportParameterExpression>
				</subreportParameter>
				<connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>
				<subreportExpression class="java.lang.String"><![CDATA["repo:firma2"]]></subreportExpression>
			</subreport>
			<subreport>
				<reportElement stretchType="RelativeToBandHeight" x="23" y="30" width="250" height="62"/>
				<subreportParameter name="comando">
					<subreportParameterExpression><![CDATA[$P{comando}]]></subreportParameterExpression>
				</subreportParameter>
				<connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>
				<subreportExpression class="java.lang.String"><![CDATA["repo:firmas1"]]></subreportExpression>
			</subreport>
		</band>
	</summary>
	<noData>
		<band height="20">
			<staticText>
				<reportElement x="0" y="0" width="572" height="20"/>
				<textElement textAlignment="Center" verticalAlignment="Middle">
					<font isBold="true"/>
				</textElement>
				<text><![CDATA[No hay objetos a mostrar.]]></text>
			</staticText>
		</band>
	</noData>
</jasperReport>
