<?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="certif_general" language="groovy" pageWidth="612" pageHeight="792" whenNoDataType="NoDataSection" columnWidth="468" leftMargin="72" rightMargin="72" topMargin="72" bottomMargin="72">
	<property name="ireport.zoom" value="1.100000000000001"/>
	<property name="ireport.x" value="0"/>
	<property name="ireport.y" value="0"/>
	<parameter name="whereFuncionarioNumDoc" class="java.lang.String">
		<defaultValueExpression><![CDATA[" "]]></defaultValueExpression>
	</parameter>
	<parameter name="whereFuncionarioTipoDoc" class="java.lang.String">
		<defaultValueExpression><![CDATA[" "]]></defaultValueExpression>
	</parameter>
	<parameter name="periodo" class="java.lang.String">
		<defaultValueExpression><![CDATA[""]]></defaultValueExpression>
	</parameter>
	<parameter name="id_periodo" class="java.math.BigDecimal">
		<defaultValueExpression><![CDATA[]]></defaultValueExpression>
	</parameter>
	<parameter name="fecha_corte" class="java.lang.String">
		<defaultValueExpression><![CDATA[]]></defaultValueExpression>
	</parameter>
	<parameter name="numero_letras" class="java.lang.String">
		<defaultValueExpression><![CDATA[""]]></defaultValueExpression>
	</parameter>
	<parameter name="destino" class="java.lang.String">
		<defaultValueExpression><![CDATA[""]]></defaultValueExpression>
	</parameter>
	<parameter name="comando" class="java.lang.String">
		<defaultValueExpression><![CDATA[]]></defaultValueExpression>
	</parameter>
	<queryString>
		<![CDATA[SELECT

(select firma_inf.encabezado from no_inf_firma firma_inf where firma_inf.comando = 'no_certif_labor') as enc,
(select firma_inf.pie_pagina from no_inf_firma firma_inf where firma_inf.comando = 'no_certif_labor') as pie,

case when (select min(sexo) as sexo from datos_funcionario where num_doc in (
select firma.documento from no_inf_firma firma_inf
left join no_firma firma ON firma.id_firma = firma_inf.id_firma
where firma_inf.comando = $P{comando}
)) = 1 then 'LA' else 'EL' end as letra_el_titulo,

nvl(replace($P{destino},'null',''), 'quien interese') as destino,

case when area2.descripcion is null then ' ' else initcap(area2.descripcion) ||', ' end as DEPENDENCIA_niv,
initcap(area1.descripcion) as area1,
initcap(area2.descripcion) as area2,

'$ ' || TO_CHAR(tabper.valor, 'FM9,999,999,999') as asig,
--tabper.valor,

LPAD(TO_CHAR(sysdate,'dd'),2,'0') as DIA_actual,
lower(Numero_a_Texto ( to_char(sysdate,'dd'), 2, 'ESP' )) as dia_ACTUAL_letra,
trim(INITCAP(TO_CHAR(sysdate,'MONTH'))) as MES_actual,
TO_CHAR(sysdate,'yyyy') as anho_actual,
lower(Numero_a_Texto(TO_CHAR(sysdate,'yyyy') , 2, 'ESP' )) as anho_actual_letras,
lower($P{numero_letras}) as numeros_letras,

(select DATOS_COMPANIA.NOMBRE_EMPRESA from DATOS_COMPANIA) as empresa,
lower(Numero_a_Texto( to_char(tab1.fecha_ingreso,'dd'), 2, 'ESP' )) as dia_ingreso_letra,
LPAD(to_char(tab1.fecha_ingreso,'dd'),2,'0') as dia_ingreso,
trim(initcap(to_char(tab1.fecha_ingreso,'MONTH'))) as mes_ingreso,
TO_CHAR(TAB1.FECHA_INGRESO,'yyyy') as anho_INGRESO,


lower(Numero_a_Texto( to_char(tab1.fecha_termina,'dd'), 2, 'ESP' )) as dia_termina_letra,
LPAD(to_char(tab1.fecha_termina,'dd'),2,'0') as dia_termina,
trim(initcap(to_char(tab1.fecha_termina,'MONTH'))) as mes_termina,
TO_CHAR(TAB1.FECHA_termina,'yyyy') as anho_termina,

TAB0.ID_TIPO_DOC as ID_TIPO_DOC,
TAB0.NUM_DOC as NUM_DOC,
replace(trim(TO_CHAR(TAB0.NUM_DOC, '9,999,999,999,999')),',','.') as NUM_DOC_puntos,

TAB0.NOMBRE_COMPLETO as NOMBRE_COMPLETO,
tab0.nombre_apellido as nombre_apellido, tab0.apellidos, tab0.cargo as cargo,
tab0.codigo_comp as codigo_comp,
TAB0.GRADO_COMP as GRADO_COMP,
initcap_sofhouse(initcap(TAB0.DENOMINACION_COMP)) as DENOMINACION_COMP,
initcap_sofhouse(initcap(TAB0.DEPENDENCIA)) as DEPENDENCIA,
TAB0.ASIGNACION_BASICA as ASIGNACION_BASICA, TAB0.DEVENGADOS as DEVENGADOS, TAB0.DEDUCIDOS as DEDUCIDOS,
tab0.neto as neto, tab0.id_periodo as id_periodo, tab0.banco as banco, tab0.cuenta as cuenta,
tab0.tipper as tipper, tab0.periodo as periodo, tab0.tipper||'  '||tab0.periodo as periodo_completo,
tab0.email as email, tab0.id_dp as id_dp, to_char(tab1.fecha_ingreso, 'YYYY-MM-DD') as fecha_ingreso,
-- si tiene novedad de encargo poner en tipo_nombramiento - 20131021
--lower(tipnom1.nombre) as tipo_nombramiento
case when (select count(*) from no_novedad_personal
where id_tipo_doc = TAB0.ID_TIPO_DOC and num_doc = TAB0.NUM_DOC and estado = 1 and id_novedad = 3
and sysdate between fecha_inicial and fecha_terminacion) > 0 then 'encargo' else lower(tipnom1.nombre) end as tipo_nombramiento,
tab0.sexo,
initcap_sofhouse(initcap(nvl(tab0.lugar_doc,'Bogotá'))) as lugar_doc

FROM NO_LIQUIDA_V tab0

left join(
    SELECT
    tab0.*,
    ROW_NUMBER() OVER (
        partition by tab0.num_doc
        ORDER BY tab0.num_doc, tab0.id_anhos desc, tab0.id_meses desc, tab0.fecha_hasta desc
    ) AS rn
    from no_concepto_periodo tab0
    join no_periodo per on per.id_periodo = tab0.periodo
    where
    tab0.id_concep_nomina = (select no_param_concep.id_concep_basica from no_param_concep where no_param_concep.id_param_concep = 1)
    $P!{whereFuncionarioNumDoc} $P!{whereFuncionarioTipoDoc}
)tabper
ON tab0.id_tipo_doc = tabper.id_tipo_doc
AND tab0.num_doc = tabper.num_doc
and tabper.rn = 1

JOIN NO_NOMBRAMIENTO_V tab1 ON tab0.id_tipo_doc = tab1.id_tipo_doc
AND tab0.num_doc = tab1.num_doc

left join rh_sa_niv2 tipnom1
on tipnom1.id_sa_niv2 = tab1.id_sa_niv2

join rh_dp rhdp
on tab0.id_dp = rhdp.id_dp

left join area area1
on area1.id_area = rhdp.id_ea

left join area area2
on area2.id_area = area1.id_dependencia

WHERE  tab0.id_periodo != '-1'
AND tab0.id_periodo in (

  select distinct tab.periodo from(
          SELECT
           tab0.*,
           ROW_NUMBER() OVER (
            partition by tab0.num_doc
            ORDER BY tab0.num_doc, tab0.id_anhos desc, tab0.id_meses desc
           ) AS rn
          from no_estado_func tab0
          join no_periodo per on per.id_periodo = tab0.periodo
          where
          per.id_tipo_periodo = 1
          $P!{whereFuncionarioNumDoc} $P!{whereFuncionarioTipoDoc}
    ) tab
    where  tab.rn = 1

)
$P!{whereFuncionarioNumDoc} $P!{whereFuncionarioTipoDoc}


ORDER BY num_doc]]>
	</queryString>
	<field name="ENC" class="java.lang.String"/>
	<field name="PIE" class="java.lang.String"/>
	<field name="LETRA_EL_TITULO" class="java.lang.String"/>
	<field name="DESTINO" class="java.lang.String"/>
	<field name="DEPENDENCIA_NIV" class="java.lang.String"/>
	<field name="AREA1" class="java.lang.String"/>
	<field name="AREA2" class="java.lang.String"/>
	<field name="ASIG" class="java.lang.String"/>
	<field name="DIA_ACTUAL" class="java.lang.String"/>
	<field name="DIA_ACTUAL_LETRA" class="java.lang.String"/>
	<field name="MES_ACTUAL" class="java.lang.String"/>
	<field name="ANHO_ACTUAL" class="java.lang.String"/>
	<field name="ANHO_ACTUAL_LETRAS" class="java.lang.String"/>
	<field name="NUMEROS_LETRAS" class="java.lang.String"/>
	<field name="EMPRESA" class="java.lang.String"/>
	<field name="DIA_INGRESO_LETRA" class="java.lang.String"/>
	<field name="DIA_INGRESO" class="java.lang.String"/>
	<field name="MES_INGRESO" class="java.lang.String"/>
	<field name="ANHO_INGRESO" class="java.lang.String"/>
	<field name="DIA_TERMINA_LETRA" class="java.lang.String"/>
	<field name="DIA_TERMINA" class="java.lang.String"/>
	<field name="MES_TERMINA" class="java.lang.String"/>
	<field name="ANHO_TERMINA" class="java.lang.String"/>
	<field name="ID_TIPO_DOC" class="java.math.BigDecimal"/>
	<field name="NUM_DOC" class="java.lang.String"/>
	<field name="NUM_DOC_PUNTOS" class="java.lang.String"/>
	<field name="NOMBRE_COMPLETO" class="java.lang.String"/>
	<field name="NOMBRE_APELLIDO" class="java.lang.String"/>
	<field name="APELLIDOS" class="java.lang.String"/>
	<field name="CARGO" class="java.lang.String"/>
	<field name="CODIGO_COMP" class="java.lang.String"/>
	<field name="GRADO_COMP" class="java.lang.String"/>
	<field name="DENOMINACION_COMP" class="java.lang.String"/>
	<field name="DEPENDENCIA" class="java.lang.String"/>
	<field name="ASIGNACION_BASICA" class="java.math.BigDecimal"/>
	<field name="DEVENGADOS" class="java.math.BigDecimal"/>
	<field name="DEDUCIDOS" class="java.math.BigDecimal"/>
	<field name="NETO" class="java.math.BigDecimal"/>
	<field name="ID_PERIODO" class="java.math.BigDecimal"/>
	<field name="BANCO" class="java.lang.String"/>
	<field name="CUENTA" class="java.lang.String"/>
	<field name="TIPPER" class="java.lang.String"/>
	<field name="PERIODO" class="java.lang.String"/>
	<field name="PERIODO_COMPLETO" class="java.lang.String"/>
	<field name="EMAIL" class="java.lang.String"/>
	<field name="ID_DP" class="java.math.BigDecimal"/>
	<field name="FECHA_INGRESO" class="java.lang.String"/>
	<field name="TIPO_NOMBRAMIENTO" class="java.lang.String"/>
	<field name="SEXO" class="java.lang.String"/>
	<field name="LUGAR_DOC" class="java.lang.String"/>
	<variable name="letra_a" class="java.lang.String">
		<variableExpression><![CDATA[( '1' == $F{SEXO}
    ? "a"
    : "o"
)]]></variableExpression>
	</variable>
	<variable name="letra_del" class="java.lang.String">
		<variableExpression><![CDATA[( '1' == $F{SEXO}     ? "de la"     : "del" )]]></variableExpression>
	</variable>
	<variable name="letra_a_sin" class="java.lang.String">
		<variableExpression><![CDATA[( '1' == $F{SEXO}     ? "a"     : "" )]]></variableExpression>
	</variable>
	<variable name="letra_el" class="java.lang.String">
		<variableExpression><![CDATA[( '1' == $F{SEXO}     ? "la"     : "el" )]]></variableExpression>
	</variable>
	<background>
		<band splitType="Stretch"/>
	</background>
	<title>
		<band height="81" splitType="Stretch">
			<image hAlign="Center">
				<reportElement x="0" y="0" width="468" height="81"/>
				<imageExpression class="java.lang.String"><![CDATA["repo:logo_servicio_bicen.jpg"]]></imageExpression>
			</image>
		</band>
	</title>
	<pageHeader>
		<band height="60" splitType="Stretch">
			<textField>
				<reportElement x="0" y="0" width="468" height="60"/>
				<box leftPadding="0">
					<pen lineWidth="0.75"/>
					<topPen lineWidth="0.0"/>
					<leftPen lineWidth="0.0"/>
					<bottomPen lineWidth="0.0"/>
					<rightPen lineWidth="0.0"/>
				</box>
				<textElement textAlignment="Center" verticalAlignment="Middle">
					<font size="11" isBold="true"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{LETRA_EL_TITULO} + " " + $F{ENC} + " " + $F{EMPRESA}]]></textFieldExpression>
			</textField>
		</band>
	</pageHeader>
	<columnHeader>
		<band height="59" splitType="Stretch">
			<textField>
				<reportElement x="0" y="0" width="468" height="59"/>
				<box leftPadding="0">
					<pen lineWidth="0.75"/>
					<topPen lineWidth="0.0"/>
					<leftPen lineWidth="0.0"/>
					<bottomPen lineWidth="0.0"/>
					<rightPen lineWidth="0.0"/>
				</box>
				<textElement textAlignment="Center" verticalAlignment="Middle">
					<font size="11" isBold="true"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA["HACE CONSTAR"]]></textFieldExpression>
			</textField>
		</band>
	</columnHeader>
	<detail>
		<band height="233" splitType="Stretch">
			<textField isStretchWithOverflow="true">
				<reportElement stretchType="RelativeToTallestObject" x="0" y="0" width="468" height="50"/>
				<box leftPadding="0">
					<pen lineWidth="0.75"/>
					<topPen lineWidth="0.0"/>
					<leftPen lineWidth="0.0"/>
					<bottomPen lineWidth="0.0"/>
					<rightPen lineWidth="0.0"/>
				</box>
				<textElement textAlignment="Justified" verticalAlignment="Top" markup="styled">
					<font size="11"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA["Que revisado el aplicativo de nómina "+$V{letra_el}+ " Señor"+$V{letra_a_sin}+ " " +  "<style isBold=\"true\">" + $F{NOMBRE_APELLIDO} + "</style>" +", identificad"+$V{letra_a}+ " con la cédula de ciudadanía No. " + $F{NUM_DOC_PUNTOS} + " expedida en "+$F{LUGAR_DOC}+", prestó sus servicios a esta Entidad desde el día " + $F{DIA_INGRESO} + " de " + $F{MES_INGRESO} + " de " + $F{ANHO_INGRESO} + " hasta el " +  $F{DIA_TERMINA} + " de " + $F{MES_TERMINA} + " de " + $F{ANHO_TERMINA} + "."]]></textFieldExpression>
			</textField>
			<textField isStretchWithOverflow="true">
				<reportElement stretchType="RelativeToTallestObject" x="0" y="50" width="468" height="50"/>
				<box leftPadding="0">
					<pen lineWidth="0.75"/>
					<topPen lineWidth="0.0"/>
					<leftPen lineWidth="0.0"/>
					<bottomPen lineWidth="0.0"/>
					<rightPen lineWidth="0.0"/>
				</box>
				<textElement textAlignment="Justified" verticalAlignment="Top" markup="styled">
					<font size="11"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA["Que al momento de su desvinculación "+$V{letra_el}+" señor"+$V{letra_a_sin}+ " " +"<style isBold=\"true\">" +$F{APELLIDOS}+"</style>" + " ostentaba el empleo de " + $F{DENOMINACION_COMP} + ", Código " + $F{CODIGO_COMP} + ", Grado " + $F{GRADO_COMP} + " con una asignación básica mensual de " + $F{NUMEROS_LETRAS} + " pesos (" + $F{ASIG} + ") Mcte."]]></textFieldExpression>
			</textField>
			<textField isStretchWithOverflow="true">
				<reportElement stretchType="RelativeToTallestObject" x="0" y="100" width="468" height="50"/>
				<box leftPadding="0">
					<pen lineWidth="0.75"/>
					<topPen lineWidth="0.0"/>
					<leftPen lineWidth="0.0"/>
					<bottomPen lineWidth="0.0"/>
					<rightPen lineWidth="0.0"/>
				</box>
				<textElement textAlignment="Justified" verticalAlignment="Top">
					<font size="11"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA["La presente constancia se expide a solicitud del interesado, a los " + $F{DIA_ACTUAL_LETRA} + " (" + $F{DIA_ACTUAL} + ") días del mes de " + $F{MES_ACTUAL} + " de " + $F{ANHO_ACTUAL_LETRAS} + "(" + $F{ANHO_ACTUAL} + ")."]]></textFieldExpression>
			</textField>
			<subreport>
				<reportElement stretchType="RelativeToBandHeight" x="0" y="150" width="350" height="62"/>
				<subreportParameter name="comando">
					<subreportParameterExpression><![CDATA[$P{comando}]]></subreportParameterExpression>
				</subreportParameter>
				<connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>
				<subreportExpression class="java.lang.String"><![CDATA["repo:firma_sin_cargo"]]></subreportExpression>
			</subreport>
		</band>
	</detail>
	<columnFooter>
		<band height="16" splitType="Stretch"/>
	</columnFooter>
	<pageFooter>
		<band height="42" splitType="Stretch">
			<textField>
				<reportElement mode="Opaque" x="350" y="30" 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="440" y="30" width="28" 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>
			<textField>
				<reportElement x="0" y="0" width="468" height="20"/>
				<textElement textAlignment="Center" markup="none">
					<font size="8"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{PIE}]]></textFieldExpression>
			</textField>
		</band>
	</pageFooter>
	<noData>
		<band height="20">
			<staticText>
				<reportElement x="0" y="0" width="468" height="20"/>
				<textElement textAlignment="Center" verticalAlignment="Middle">
					<font isBold="true"/>
				</textElement>
				<text><![CDATA[No hay objetos a mostrar.]]></text>
			</staticText>
		</band>
	</noData>
</jasperReport>
