#!/perl/bin/perl.exe
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# Information Software SA
# Date In: 2017-09-25
# Updates: 2017-09-25
# Author:Rodolfo Machon
# File Name: inmuebles_cargas_delsur_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;
$lan	    = $form->param('lan');
$appid	    = $form->param('appid');
$i8_periodo	= $form->param('i8_periodo');
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#Define subdirectories and general variables
$PATH		= "/Apache24/htdocs/RTM_Cuisnahuat/$appid/$lan/";
$file 		= "es_inmuebles_cargas_delsur_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";}
#------------------------------------------------------------------------- 
#-------------------------------------------------------------------------
# MAIN-PROGRAM

%carga	    = get_carga();
$fiestas    = round((get_fiesta()),2);
$carga_aseo	= get_carga_aseo();
$carga_alum	= get_carga_alum();
$carga_call	= get_carga_call();
$carga_dese	= get_carga_dese();
$carga_barr	= get_carga_barr();
$carta      = construct_carta();

construct_page();

exit;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#SUBROUTINES LEVEL I
sub get_carga {
    my %hash = ();
    my $sql  = "SELECT a.c16_tarifa_id,a.d82_val
                FROM sdd_inmueble_cargas a
                WHERE a.i8_periodo='$i8_periodo' and c3_tipo_facturador_id='DEL'
                ";
    #print "$sql <br>";
	my $sth  = $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_carga');
	while (my @row = $sth->fetchrow_array) {
        $hash{substr($row[0],0,7)} += round(($row[1]),2) ;
    }
    return %hash;
}
sub get_fiesta {
    my $mon = 0;
    my $sql = "SELECT a.d82_fie
                FROM sdd_inmueble_cargas a
                WHERE a.i8_periodo='$i8_periodo' and c3_tipo_facturador_id='DEL'
                ";
    #print "$sql <br>";
	my $sth  = $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_carga');
	while (my @row = $sth->fetchrow_array) {
        $mon += round(($row[0]),2) ;
    }
    return $mon;
}
sub get_cta_pendiente {
    my $mon = 0;
    my $sql = "SELECT a.d82_fie
                FROM sdd_inmueble_cargas a
                WHERE a.i8_periodo='$i8_periodo' and c3_tipo_facturador_id='DEL'
                ";
    #print "$sql <br>";
	my $sth  = $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_carga');
	while (my @row = $sth->fetchrow_array) {
        $mon += round(($row[0]),2) ;
    }
    return $mon;
}
sub get_carga_aseo {
    my %hash  = ();
    my $count =0;
    my $sql  = "SELECT a.c16_tarifa_id
                FROM sdd_inmueble_cargas a
                WHERE SUBSTRING(a.c16_tarifa_id,1,7)='TSINMAS' and a.i8_periodo='$i8_periodo'  and c3_tipo_facturador_id='DEL'
                ";
	my $sth  = $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_carga_registros');
	while (my @row = $sth->fetchrow_array) {
        $count++;
    }
    return $count;
}
sub get_carga_alum {
    my %hash  = ();
    my $count =0;
    my $sql  = "SELECT a.c16_tarifa_id
                FROM sdd_inmueble_cargas a
                WHERE SUBSTRING(a.c16_tarifa_id,1,7)='TSINMAL' and a.i8_periodo='$i8_periodo'  and c3_tipo_facturador_id='DEL'
                ";
	my $sth  = $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_carga_registros');
	while (my @row = $sth->fetchrow_array) {
        $count++;
    }
    return $count;
}
sub get_carga_call {
    my %hash  = ();
    my $count =0;
    my $sql  = "SELECT a.c16_tarifa_id
                FROM sdd_inmueble_cargas a
                WHERE SUBSTRING(a.c16_tarifa_id,1,8)='TSINMCAL' and a.i8_periodo='$i8_periodo'  and c3_tipo_facturador_id='DEL'
                ";
	my $sth  = $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_carga_registros');
	while (my @row = $sth->fetchrow_array) {
        $count++;
    }
    return $count;
}
sub get_carga_cam {
    my %hash  = ();
    my $count =0;
    my $sql  = "SELECT a.c16_tarifa_id
                FROM sdd_inmueble_cargas a
                WHERE SUBSTRING(a.c16_tarifa_id,1,8)='TSINMCAV' and a.i8_periodo='$i8_periodo'  and c3_tipo_facturador_id='DEL'
                ";
	my $sth  = $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_carga_registros');
	while (my @row = $sth->fetchrow_array) {
        $count++;
    }
    return $count;
}
sub get_carga_dese {
    my %hash  = ();
    my $count =0;
    my $sql  = "SELECT a.c16_tarifa_id
                FROM sdd_inmueble_cargas a
                WHERE SUBSTRING(a.c16_tarifa_id,1,8)='TSINMDES' and a.i8_periodo='$i8_periodo'  and c3_tipo_facturador_id='DEL'
                ";
	my $sth  = $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_carga_registros');
	while (my @row = $sth->fetchrow_array) {
        $count++;
    }
    return $count;
}
sub get_carga_barr {
    my %hash  = ();
    my $count =0;
    my $sql  = "SELECT a.c16_tarifa_id
                FROM sdd_inmueble_cargas a
                WHERE SUBSTRING(a.c16_tarifa_id,1,8)='TSINMBAR' and a.i8_periodo='$i8_periodo'  and c3_tipo_facturador_id='DEL'
                ";
	my $sth  = $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_carga_registros');
	while (my @row = $sth->fetchrow_array) {
        $count++;
    }
    return $count;
}
sub get_carga_rec {
    my %hash  = ();
    my $count =0;
    my $sql  = "SELECT a.c16_tarifa_id
                FROM sdd_inmueble_cargas a
                WHERE SUBSTRING(a.c16_tarifa_id,1,8)='TSINMDES' and a.i8_periodo='$i8_periodo' and c3_tipo_facturador_id='DEL'
                ";
	my $sth  = $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_carga_registros');
	while (my @row = $sth->fetchrow_array) {
        $count++;
    }
    return $count;
}
sub construct_carta {
    my $rows    = "";
    my $t_regs  = 0;
    my $t_monto = 0;
    my $count   = 1;
    my $fie     = 0;
    my $car     = get_carta();;
	my $dt_hoy  = get_dt_hoy();
    my $table   = "<table id='carga'><thead>{thead}</thead><tbody>{tbody}</tbody><tfoot>{tfoot}</tfoot></table>";
    my $row_head= "<tr><td>No.</td><td>RUBROS</td><td align=right>REGISTROS</td><td align=right>MONTO (USD)</td>";
    my $row_body= "<tr><td>{count}</td><td>{rubro}</td><td align=right>{registros}</td><td align=right>{monto}</td>";
    my $row_foot= "<tr><td colspan=3>Total</td><td>{t_monto}</td>";
    my %rubros = (TSINMAS=>'ASEO',TSINMAL=>'ALUMBRADO',TSINMCA=>'PAVIMENTO',TSINMDE=>'DESECHOS SOLIDOS',TSINMCP=>'CUENTAS PENDIENTES');
	my @meses	= ('','enero','febrero','marzo','abril','mayo','junio','julio','agosto','septiembre','octubre','noviembre','diciembre');
    my $mes     = $meses[(substr($i8_periodo,4,2)*1)];
    my $ano     = substr($i8_periodo,0,4);
    
    foreach my $k (sort keys %carga){
        my $regs    = 0;
        my $row     = $row_body; 
		my $cod     = $k;
        my $rubro   = $rubros{$cod};
        my $monto   = round($carga{$k},2);
        my $fmonto  = big_money(round($carga{$k},2));
        
        #print "$k > $rubro > $monto<br>";
        if ($cod eq "TSINMAS") {$regs = $carga_aseo}
        if ($cod eq "TSINMAL") {$regs = $carga_alum}
        if ($cod eq "TSINMCA") {$regs = $carga_call}
        if ($cod eq "TSINMDE") {$regs = $carga_dese}
        if ($regs >0) {
            $row    =~ s/\{count}/$count/g;
            $row    =~ s/\{rubro}/$rubro/g;
            $row    =~ s/\{registros}/$regs/g;
            $row    =~ s/\{monto}/$fmonto/g;
            
            $rows   .= $row;
            $t_regs += $regs;
            $t_monto+= $monto;
            
            $count++;
        }
    }
    $t_monto   += $monto+$fiestas;
    $t_monto    = big_money($t_monto);
    my $fiestas1= big_money($fiestas);
    
    my $row     = $row_body;
    $row    =~ s/\{count}/4/g;
    $row    =~ s/\{rubro}/FIESTAS/g;
    $row    =~ s/\{registros}//g;
    $row    =~ s/\{monto}/$fiestas1/g;
    $rows   .= $row;

    my $row1 = $row_body;
    $row1    =~ s/\{count}/5/g;
    $row1    =~ s/\{rubro}/COBRO PENDIENTE/g;
    $row1    =~ s/\{registros}//g;
    $row1    =~ s/\{monto}//g;
    $rows   .= $row1;
    
    $row_foot =~ s/\{t_regs}/$t_regs/g;
    $row_foot =~ s/\{t_monto}/$t_monto/g;
    $table    =~ s/\{thead}/$row_head/g;
    $table    =~ s/\{tbody}/$rows/g;
    $table    =~ s/\{tfoot}/$row_foot/g;
               
	$car   =~ s/\{carga_table}/$table/g;
    $car   =~ s/\{fecha_hoy_letras}/$dt_hoy/g;
	$car   =~ s/\{mes}/$mes/g;
	$car   =~ s/\{ano}/$ano/g;         

    return $car;
}
sub construct_page {
	open (DATA, "$PATH$file");
	while ($line = <DATA>) {
		$line =~ s/\{lan}/$lan/g;
		$line =~ s/\{appid}/$appid/g;
		$line =~ s/\{carta}/$carta/g;
		print $line;
	}	
}
#-------------------------------------------------------------------------
#SUBROUTINES LEVEL II
sub get_carta {
	my $constancia	="<DIV id='pagina'>
						<DIV ID='membrete' contenteditable=true>
							<TABLE>
								<TR><TD rowspan=2 width=10%><img src=/icons/RTM_v30/logo.jpg width=90px height=100px></TD><TD align=center><h1>ALCALDIA MUNICIPAL DE QUEZALTEPEQUE</h1></TD><TD rowspan=2 width=10%><img src=/icons/RTM_v30/escudo.jpg width=100px height=100px></TD></TR>
								<TR><TD align=centert>
                                        <TABLE>
                                            <TR><TD>&nbsp;&nbsp;Direccion:</TD></TR>
                                            <TR><TD>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Quezaltepeque, La Libertad,El Salvador, Centro Am&eacute;rica</TD></TR>
                                            <TR><TD>&nbsp;&nbsp;Telefono: </TD></TR>
                                        </TABLE>                                
                                    </TD>
                                </TR>
							</TABLE>
						</DIV>						
						<DIV ID='fecha' contenteditable='true'>TALNIQUE, {fecha_hoy_letras}</DIV>
						<DIV ID='titulo' contenteditable=true>Se&ntilde;ores:<br>*******<br>Presente</DIV>
						<DIV ID='generales' contenteditable=true><p>Estimados se&ntilde;ores:</p> <br> <p>Reciban un cordial saludo, a la vez deseando que sus actividades en su organizaci&oacute;n sean exitosas.</p><br>
										 <p>El motivo de la presente es para que sirvan cargar a nuestros contribuyentes las siguientes tasas municipales contenidas en el archivo anexo
                                          al correo electr&oacute;nico: ****@aes.com, correspondientes al mes de {mes} de {ano}. </p><br>
                                          <p>Los montos totales se detallan de la siguiente manera:</p>
						</DIV>
						<DIV ID='remuneraciones-descuentos' contenteditable=true>
							{carga_table}
						</DIV>
						<DIV ID='nota' contenteditable=true></DIV>
						<DIV ID='aclaracion' contenteditable=true></DIV>
						<DIV ID='firma' contenteditable=true>
							<TABLE>
								<TR><TD class=linea>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</TD><TD>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</TD><TD class=linea>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</TD></TR>
								<TR><TD>Nombre:</TD><TD></TD><TD></TD></TR>                                
								<TR><TD>Autoriza Gerencia General</TD><TD></TD><TD>Elabor&oacute; Cuentas Corrientes</TD></TR>
							</TABLE>
                        </DIV>
						<DIV ID='pie' >Esta carta es v&aacute;lida &uacute;nicamente con firmas y sellos originales de las personas autorizadas (Art.101 del C&oacute;digo Municipal), cualquier alteraci&oacute;n anula esta carta.</DIV>
					</DIV>
					";
	return $constancia;
}
sub get_dt_hoy {
	my $fec  	= "";
	my $sql 	= "SELECT DATE_FORMAT(CURDATE(),'%Y-%m-%d')";
	my $sth 	= $dbh->prepare($sql);	
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_fecha_hoy');
	while (my @row = $sth->fetchrow_array) {
		$fec 	  = $row[0];
	}	
	$fec = get_fecha_letras($fec);
	return $fec;
}
#------------------------------------------------------------------------- 
#AUXILIAR-SUBROUTINES
sub SQL_fail {
    my ($dbh,$query,$section)= @_;
    print "SQL failed in Section $section<br>";
    print "Query: ".$query."<br>";    
    exit;
}
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 = ($fec_a[2]*1)." de ".$mes." de ".($fec_a[0]*1);	
	$fec =~ s/un mil/mil/g;
	
	#return uc($fec);
	return $fec;
}
sub get_entero_letras {
	my ($cant)  = @_;
	my $cl		= "";
	my $millones= ($cant - ($cant % 1000000))/1000000;
	$cant 	   -= $millones * 1000000;
	my $miles   = ($cant - ($cant % 1000))/1000;
	$cant 	   -= $miles * 1000;
	my $cientos = ($cant - ($cant % 100))/100;
	$cant 	   -= $cientos * 100;
	
	if ($millones>0){$cl .= get_letras($millones,'m');$cl .= " millones "}
	if ($miles>0){$cl .= get_letras($miles,'t');$cl .= " mil "}	
	if ($cientos>0){$cl .= get_letras($cientos,'h');$cl .= " "}
	$cl .= get_letras($cant,'u');

	return $cl;
}
sub get_letras {
	my ($cant,$mul)  = @_;
	my $cl		= "";
	my @ns		= ('','un','dos','tres','cuatro','cinco','seis','siete','ocho','nueve','diez','once','doce','trece','catorce','quince','dieciseis','diecisiete','dieciocho','diecinueve','veinte');
	my @ns10  	= ('','diez','veinte','treinta','cuarenta','cincuenta','sesenta','setenta','ochenta','noventa');
	my @ns20  	= ('','diez','veinti','treinta','cuarenta','cincuenta','sesenta','setenta','ochenta','noventa');
	my @ns100 	= ('','cien','doscientos','trescientos','cuatrocientos','quinientos','seiscientos','setecientos','ochocientos','novecientos');
	my @Ns100 	= ('','ciento','','','','','','','','');
	my $centenas= 0;
	my $decenas = ($cant - ($cant % 10))/10;
	my $unidades= $cant -($cant - ($cant % 10));
	if ($cant<=20) {
		if($mul eq "t"){$cl = $ns[$cant]}		
		if($mul eq "h"){$cl = $ns100[$cant]}
		if($mul eq "u"){$cl = $ns[$cant]}
	}
	if ($cant>20 && $cant<30) {
		$cl      = $ns20[$decenas].$ns[$unidades];
	}
	if ($cant>30 && $cant<100) {
		$cl      = $ns10[$decenas];
		if($decenas>0 && $unidades>0 ){$cl .= " y ".$ns[$unidades]}else{$cl .= $ns[$unidades]}
	}
	if ($cant>=100) {
		$centenas = substr($cant,0,1);
		$decenas  = substr($cant,1,1);
		$unidades = substr($cant,2,1);		
		$cl       = $ns100[$decenas];
		if($centenas>0 && $decenas>0 ){$cl .= " ".$ns10[$decenas]}else{$cl .= $ns10[$decenas]}
		if($decenas>0 && $unidades>0 ){$cl .= " y ".$ns[$unidades]}else{$cl .= $ns[$unidades]}		
	}	
	return $cl;
}
sub round {
    my ($n, $places) = @_;
    my $abs = abs $n;
    my $val = substr($abs + ('0.' . '0' x $places . '5'),0,length(int($abs)) +(($places > 0) ? $places + 1 : 0));
    ($n < 0) ? "-" . $val : $val;
}
sub big_money {
  my $number = sprintf "%.2f", shift @_;
  # Add one comma each time through the do-nothing loop
  1 while $number =~ s/^(-?\d+)(\d\d\d)/$1,$2/;
  # Put the dollar sign in the right place
  $number =~ s/^(-?)/$1\$/;
  $number;
}
#-------------------------------------------------------------------------