#!/perl/bin/perl.exe
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# Information Software SA
# Date In: 2023-02-12
# Updates: 2023-02-12
# Author:Rodolfo Machon
# File Name: agua_sector_notificacion_rept.pl
#------------------------------------------------------------------------- 
#-------------------------------------------------------------------------
#Perl Modules
use CGI;
use DBI;
use utf8;
use Encode;
use Encode qw/is_utf8 decode/;
use CGI::Carp qw(fatalsToBrowser);
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#HTML printing activation
print "Content-type: text/html; charset=utf-8\n\n";
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#Getting parameters
$form = new CGI;
$c11_sector_id= $form->param('c11_sector_id');
$v64_sector	= $form->param('v64_sector');
$year   = $form->param('year');
$month  = $form->param('month');
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# GET CLASE SCHEMA AND VARIABLES
get_app_data();
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#Define subdirectories and general variables
$file 		= "es_agua_sector_notificaciones_rept.html";
#------------------------------------------------------------------------- 
#-------------------------------------------------------------------------
# Open Data Base Connections
$dbh 	  = DBI->connect("dbi:ODBC:$appid",'','',{PrintError => 1, RaiseError => 1});
if (!$dbh) {print "error: connection: $DBI::err\n$DBI::errstr\n$DBI::state\n";}
$dbh->{LongReadLen} = 512 * 1024;
#------------------------------------------------------------------------- 
#-------------------------------------------------------------------------
# MAIN-PROGRAM
#%remuneraciones		    	= get_remuneraciones();
#%inasistencias		    	= get_inasistencias();
#%planilla					= get_planilla();

#if (!%planilla) {$planilla_boletas = "<br><br><br><center>NO HA GENERADO LA PLANILLA</center>";construct_page();exit;}

#($str_empleados,%empleados) = get_planilla_empleados();
%empleados = get_notificaciones();
#print "$str_empleados <br>";

#if ($str_empleados eq ""){print "La Planilla No Tiene Empleados Asignados !!";exit;}

#%planilla_lineas   			= get_planilla_lineas();
#%planilla_proyectos			= get_planilla_proyectos();
#%planilla_retenciones		= get_planilla_retenciones();

#%empleados_tiempos   		= get_planilla_empleados_tiempos();
#%empleados_remuneraciones   = get_planilla_empleados_remuneraciones();
#%empleados_inasistencias    = get_planilla_empleados_inasistencias();
#%empleados_retenciones      = get_planilla_empleados_retenciones();

$planilla_boletas 			= construct_planilla_boletas();

construct_page();

exit;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#SUBROUTINES LEVEL I
sub get_remuneraciones {
    my %hash = ();
    my $sql  = "SELECT a.c2_tipo_remuneracion_id,a.v64_tipo_remuneracion
                FROM sdm_tipo_remuneraciones a
                ORDER BY a.c2_tipo_remuneracion_id";
	my $sth  = $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_remuneraciones');
	while (my @row = $sth->fetchrow_array) {
        $hash{$row[0]} = $row[1] ;
    }
    return %hash;
}
sub get_inasistencias {
    my %hash = ();
    my $sql  = "SELECT a.c2_tipo_inasistencia_id,a.v64_tipo_inasistencia
                FROM sdm_tipo_inasistencias a
                ORDER BY a.c2_tipo_inasistencia_id";
	my $sth  = $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_inasistencias');
	while (my @row = $sth->fetchrow_array) {
        $hash{$row[0]} = $row[1] ;
    }
    return %hash;
}
sub get_planilla {
	my %hash = ();
	my $cols = get_columns('sdr_planillas');
	my @flds = split(/\,/,$cols);
	my $sql  = "SELECT $cols FROM sdr_planillas WHERE i8_planilla_id='$i8_planilla_id'";
	my $sth = $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_planilla');
	while (my @row = $sth->fetchrow_array) {	
		for(my $i=0;$i<=$#flds;$i++){
			$hash{$flds[$i]} = $row[$i];
		}
    }
	#print %hash, "\n";
	return %hash;
}
sub get_planilla_lineas {
    my %hash   = ();
	my $filter = "";
	if ($c8_sublinea_trabajo_id ne "") {$filter = "AND c8_sublinea_trabajo_id=$c8_sublinea_trabajo_id"}
    my $sql = "SELECT a.c8_sublinea_trabajo_id,a.v64_sublinea_trabajo,a.c8_sublinea_trabajo_id,a.v64_linea_trabajo,a.c4_unidad_presupuestaria_id,a.v64_unidad_presupuestaria,a.c2_area_gestion_id,a.v64_area_gestion	
                FROM sdr_planillas_lineas a
				WHERE i8_planilla_id='$i8_planilla_id' $filter
                ORDER BY a.c8_sublinea_trabajo_id
            ";
	my $sth = $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'get_planilla_lineas');
	while (my @row = $sth->fetchrow_array) {
        $hash{$row[0]} = [@row];
    }
    return %hash;    
}
sub get_planilla_proyectos {
    my %hash = ();
    my $sql = "SELECT a.i8_proyecto_id,a.v128_proyecto,a.i8_participante_id,a.v128_participante,a.v128_cargo
                FROM sdr_planillas_proyectos a
				WHERE i8_planilla_id='$i8_planilla_id'
                ORDER BY a.i8_proyecto_id
            ";
	my $sth = $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'get_planilla_proyectos');
	while (my @row = $sth->fetchrow_array) {
        $hash{$row[0]} = [@row];
    }
    return %hash;    
}
sub get_planilla_retenciones {
    my %hash = ();
    my $sql = "SELECT a.i8_retencion_id,a.v128_retencion
                FROM sdr_planillas_retenciones a
				WHERE i8_planilla_id='$i8_planilla_id'
                ORDER BY a.i8_retencion_id
            ";
	#print "$sql <br>";
	my $sth = $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'get_retenciones');
	while (my @row = $sth->fetchrow_array) {
        $hash{$row[0]} = $row[1];
    }
    return %hash;    
}
sub get_planilla_empleados {
	my %hash = ();
	my $cols = get_columns('sdr_planillas_empleados');
	my $filter = "";
	if ($c8_sublinea_trabajo_id ne "") {$filter = "AND c8_sublinea_trabajo_id='$c8_sublinea_trabajo_id'"}
	if ($i8_empleado_id ne "") {$filter = "AND i8_empleado_id='$i8_empleado_id'"}	
	my $sql  = "SELECT $cols
				FROM sdr_planillas_empleados
				WHERE i8_planilla_id='$i8_planilla_id' $filter
			   ";
	#print "$sql <br>";
	my $sth = $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_empleados');
	while (my @row = $sth->fetchrow_array) {
		$hash{$row[1]} = [@row];
        $str .= $row[1].",";
    }
    $str = substr($str,0,length($str)-1);
    return ($str,%hash);
}
sub get_planilla_empleados_tiempos {
    my %hash= ();
    my $sql = "SELECT a.i8_empleado_id,a.c2_tipo_tiempo_id,a.d82_valor
			   FROM sdr_planillas_empleados_tiempos a
			   WHERE a.i8_planilla_id='$i8_planilla_id' and a.i8_empleado_id IN($str_empleados)
			   ORDER BY a.i8_empleado_id
			  ";
	my $sth = $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_planilla_empleados_tiempos');
	while (my @row = $sth->fetchrow_array) {
	    $hash{$row[0]}{$row[1]} = $row[2] ;
    }
    return %hash;
}
sub get_planilla_empleados_remuneraciones {
    my %hash= ();
    my $sql = "SELECT a.i8_empleado_id,a.c2_tipo_remuneracion_id,a.d82_valor
                FROM sdr_planillas_empleados_remuneraciones a
                WHERE a.i8_planilla_id='$i8_planilla_id' and a.i8_empleado_id in($str_empleados)
                ORDER BY a.i8_empleado_id
			  ";
	my $sth = $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_planilla_empleados_remuneraciones');
	while (my @row = $sth->fetchrow_array) {
        $hash{$row[0]}{$row[1]} = $row[2] ;
    }
    return %hash;
}
sub get_planilla_empleados_inasistencias {
    my %hash= ();
    my $sql = "SELECT a.i8_empleado_id,a.c2_tipo_inasistencia_id,a.d82_valor
                FROM sdr_planillas_empleados_inasistencias a
                WHERE a.i8_planilla_id='$i8_planilla_id' and a.i8_empleado_id in($str_empleados)
                ORDER BY a.i8_empleado_id";
	my $sth = $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_planilla_empleados_inasistencias');
	while (my @row = $sth->fetchrow_array) {
        $hash{$row[0]}{$row[1]} = $row[2];
    }
    return %hash;
}
sub get_planilla_empleados_retenciones {
    my %hash= ();
    my $sql = "SELECT a.i8_empleado_id,a.i8_retencion_id,a.v128_referencia,a.d82_valor
                FROM sdr_planillas_empleados_retenciones a
                WHERE a.i8_planilla_id='$i8_planilla_id' and a.i8_empleado_id IN($str_empleados)
                ORDER BY a.i8_empleado_id";
	my $sth = $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_planilla_empleados_retenciones');
	while (my @row = $sth->fetchrow_array) {
        $hash{$row[0]}{$row[1]}{$row[2]} = $row[3];
    }
    return %hash;
}
sub construct_planilla_boletas {
    my $boletas = "";
    my $boleta  = get_boleta();
    my $bal_row = "<tr><td>(+) {descripcion}</td><td align=right>{valor}</td></tr>";
    foreach my $k (sort numeros keys %empleados){
		my $rows  				= "";
		my $bol   				= $boleta;
		my $inasistencias_tab	=  "";
		my $ingresos_tab		=  "";
		my $descuentos_tab		=  "";
		my $retenciones_tab		=  "";
		my $ina   =  sprintf("%.2f",0.00);
		my $des   =  sprintf("%.2f",0.00);		
		my $ret   =  sprintf("%.2f",0.00);
		my $ing   =  sprintf("%.2f",0.00);
		my $end   =  sprintf("%.2f",0.00);
		#my $desde = get_fecha_letras();
		foreach my $kk (sort numeros keys %{$empleados_inasistencias{$k}}){
			my $line        = $bal_row;
			my $descripcion = $inasistencias{$kk};
			$line  			=~ s/\{descripcion}/$descripcion/g;
			$line  			=~ s/\{valor}/$empleados_inasistencias{$k}{$kk}/g;
			$rows 		   .= $line;
			$ina  		   +=  sprintf("%.2f",$empleados_inasistencias{$k}{$kk});
		}
		if($ina>0.00){$inasistencias_tab = "<table id=inasistencias_tab><tr><td><strong>(=) TOTAL INASISTENCIAS </strong></td><td>$ina</td></tr>".$rows."</table>";}
		$rows = "";	
		foreach my $kk (sort numeros keys %{$empleados_remuneraciones{$k}}){
			my $line        = $bal_row;
			my $descripcion = $remuneraciones{$kk};
			$line  =~ s/\{descripcion}/$descripcion/g;
			$line  =~ s/\{valor}/$empleados_remuneraciones{$k}{$kk}/g;
			if($kk eq "BO" || $kk eq "AS" || $kk eq "VA" || $kk eq "IN" || $kk eq "XD" || $kk eq "XN"){
				$rows .= $line;
				$ing  += sprintf("%.2f",$empleados_remuneraciones{$k}{$kk});
			}
		}		
		if($ing>0.00){$ingresos_tab = "<table id=ingresos_tab><tr><td><strong>(=) OTROS INGRESOS </strong></td><td>$ing</td></tr>".$rows."</table>";}
		$rows = "";
		foreach my $kk (sort numeros keys %{$empleados_retenciones{$k}}){
			foreach my $kkk (sort numeros keys %{$empleados_retenciones{$k}{$kk}}){
				my $line        = $bal_row;
				my $descripcion = $planilla_retenciones{$kk};
				$line  =~ s/\{descripcion}/$kk. $descripcion/g;
				$line  =~ s/\{valor}/$empleados_retenciones{$k}{$kk}{$kkk}/g;
				$rows .= $line;
				$ret  += sprintf("%.2f",$empleados_retenciones{$k}{$kk}{$kkk});					
			}
		}		
		if($ret>0.00){$retenciones_tab = "<table id=retenciones_tab><tr><td><strong>(=) TOTAL DESCUENTOS </strong></td><td>".sprintf("%.2f",$ret+$des)."</td></tr>".$rows."</table>";}
		if($empleados_remuneraciones{$k}{'NO'}>0){$ende  =  sprintf("%.2f",(($ret)/$empleados_remuneraciones{$k}{'NO'})*100);}
		
		$des   =  sprintf("%.2f",$des);
		$ret   =  sprintf("%.2f",$ret);		
		$bol   =~ s/\{v128_empleado}/$empleados{$k}[2]/g;
		$bol   =~ s/\{i8_empleado_id}/$i8_empleado_id/g;
		$bol   =~ s/\{v64_tipo_departamento}/$empleados{$k}[4]/g;
		$bol   =~ s/\{v64_cargo}/$empleados{$k}[8]/g;
		$bol   =~ s/\{dt_desde}/$planilla{'dt_desde'}/g;
		$bol   =~ s/\{dt_hasta}/$planilla{'dt_hasta'}/g;
		$bol   =~ s/\{dt_emision}/$planilla{'dt_planilla'}/g;
		$bol   =~ s/\{rem_NO}/$empleados_remuneraciones{$k}{'NO'}/g;
		$bol   =~ s/\{rem_RE}/$empleados_remuneraciones{$k}{'RE'}/g;
		$bol   =~ s/\{rem_DE}/$empleados_remuneraciones{$k}{'DE'}/g;
		$bol   =~ s/\{rem_LI}/$empleados_remuneraciones{$k}{'LI'}/g;		  
		$bol   =~ s/\{ina}/$ina/g;
		$bol   =~ s/\{des}/$des/g;
		$bol   =~ s/\{ret}/$ret/g;				  
		$bol   =~ s/\{ing}/$ing/g;
		$bol   =~ s/\{endeudamiento}/$ende/g;
		$bol   =~ s/\{inasistencias_tab}/$inasistencias_tab/g;
		$bol   =~ s/\{ingresos_tab}/$ingresos_tab/g;
		$bol   =~ s/\{descuentos_tab}/$descuentos_tab/g;
		$bol   =~ s/\{retenciones_tab}/$retenciones_tab/g;
		#$boletas .= "<DIV class='boleta'><DIV ID='orginal'>".$bol."</DIV><br><DIV ID='copia'>".$bol."</DIV></DIV><div class=\"page-break\"></div>";
		$boletas .= "<DIV class='boleta'><DIV ID='orginal'>".$bol."</DIV><br></DIV>";	
    }
    return $boletas;
}
sub construct_page {
	open (DATA, "$PATH$file");
	while ($line = <DATA>) {
		$line =~ s/\{lan}/$lan/g;
		$line =~ s/\{appid}/$appid/g;
		$line =~ s/\{boletas}/$planilla_boletas/g;
		print $line;
	}	
}
#-------------------------------------------------------------------------
#SUBROUTINES LEVEL II
sub get_columns {
	my ($table) = @_;	
	my $cols = "";
	my $sql  = "SHOW COLUMNS FROM $table";
	my $sth  = $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_culumns');
	while (my @row = $sth->fetchrow_array) {	
		$cols .= $row[0].",";
    }
	$cols = substr($cols,0,length($cols)-1);
	return $cols;	
}

#------------------------------------------------------------------------- 
#AUXILIAR-SUBROUTINES
sub get_app_data {
    my $schema  = "app.data.xml";
    my $xml     = new XML::Simple;
    my $data    = $xml->XMLin("../xml/".$schema);
    $appid      = $data->{portal}->{app};
    $lan        = $data->{portal}->{lang};
	$city	    = $data->{portal}->{city};
	$state      = $data->{portal}->{state};    
}
sub SQL_fail {
    my ($dbh,$query,$section)= @_;
    print "SQL failed in Section $section<br>";
    print "Query: ".$query."<br>";    
    exit;
}
sub numeros {$a<=>$b}
sub get_fecha_hoy {
	my $fec  	= "";
	my $query 	= "SELECT DATE_FORMAT(CURDATE(),'%Y-%m-%d')";
	if ( $dbh->Sql($query) ){SQL_fail($query,$dbh,'get_fecha')}
	if ($dbh-> FetchRow()){
		my @row_c = $dbh->Data();
		$fec 	  = $row_c[0];
	}	
	return $fec;	
}
sub get_fecha_letras {
	my ($fecha) = @_;
	my $fec  	= "";
	my $cloc 	= "";
	my $country = "SV";
	my @fec_a   = split(/\-/,$fecha);
	my @meses	= ('','enero','febrero','marzo','abril','mayo','junio','julio','agosto','septiembre','octubre','noviembre','diciembre');
	my @dias	= ('','uno','dos','tres','cuatro','cinco','seis','siete','ocho','nueve','diez','once','doce','trece','catorce','quince','dieciseis','diecisiete','dieciocho','diecinueve','veinte','veitiuno','veintidos','veintitres','veiticuatro','veinticinco','veintiseis','veintisiete','veintiocho','veintinueve','treinta','treinta y uno');	
	my $dia 	= $dias[$fec_a[2]*1];
	my $mes 	= $meses[$fec_a[1]*1];
	my $ano 	= get_entero_letras($fec_a[0]*1);
	
	$fec = $dia." de ".$mes." de ".$ano;
	$fec =~ s/un mil/mil/g;
	
	return uc($fec);	
}
#-------------------------------------------------------------------------
#boleta original
sub get_boleta1 {
	my $boleta ="<DIV ID='encabezado'>    
					<TABLE>
						<TR> 
						<TD><p>ALCALDIA MUNICIPAL DE CIUDAD DELGADO<br>COMPROBANTE DE PAGO<br>DEPARTAMENTO DE RECURSOS HUMANOS<br></p></TD>
						<TD align=center>ENDEUDAMIENTO<br>{endeudamiento} % </TD>						
						</TR>
					</TABLE>
				</DIV>
	            <DIV ID='empleado'>
					<TABLE>
						<TR>
							<TH>Nombre Empleado</TH>
							<TD>{v128_empleado}</TD>
							<TH>ID Empleado</TH>
							<TD>{i8_empleado_id}</TD>
						</TR>
						<TR>
							<TH>Departamento</TH>
							<TD>{v64_tipo_departamento}</TD>
							<TH>Cargo</TH>
							<TD>{v64_cargo}</TD>					
						</TR>
						<TR>
							<TH>Periodo Pagado</TH>
							<TD>DESDE: {dt_desde} HASTA: {dt_hasta}</TD>
							<TH>Fecha Emision:</TH>
							<TD>{dt_emision}</TD>
						</TR>
					</TABLE>
			    </DIV>
				<DIV ID='remuneraciones-descuentos'>
					<table id='rem-des'>
						<tr>
							<td valign=top><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ESTADO DE CUENTA<BR>
								<table id=estado>
									<tr>
										<td><strong>(+) Sueldo Nominal</strong></td>
										<td align=right>{rem_NO}</td>
									</tr>
									<tr>
										<td><strong>(-) Inasistencias</strong></td>
										<td align=right>{ina}</td>
									</tr>
									<tr>
										<td><strong>(=) Sueldo Mes</strong></td>
										<td align=right>{rem_RE}</td>
									</tr>
									<tr>
										<td><strong>(+) Otros Ingresos</strong></td>
										<td align=right>{ing}</td>
									</tr>
									<tr>
										<td><strong>(=) Sueldo Devengado</strong></td>
										<td align=right>{rem_DE}</td>
									</tr>
									<tr>
										<td><strong>(-) Descuentos de Ley</strong></td>
										<td align=right>{des}</td>
									</tr>
									<tr>
										<td><strong>(-) Otros Descuentos</strong></td>
										<td align=right>{ret}</td>
									</tr>
									<tr>
										<td><strong>(=) Sueldo Liquido</strong></td>
										<td align=right>{rem_LI}</td>
									</tr>
								</table><br>
							</td>
							<td valign=top>{inasistencias_tab}<br>{ingresos_tab}<br>{descuentos_tab}</td>
							<td valign=top>{retenciones_tab}</td>
						</tr>
					</table>
				</DIV>
				<DIV ID='firma'>
					<TABLE>
						<TR> 
							<TD width=50%>HE RECIBIDO EL IMPORTE NETO DE ESTA LIQUIDACION Y ESTOY DE ACUERDO CON EL DETALLE DE MIS DEVENGOS, DESCUENTOS Y RETENCIONES</TD>
							<TD align=center><br><br><br><br>________________________________________<br>Firma Empleado</TD>
						</TR>
					</TABLE>
				</DIV>";
	return $boleta;
} 