#!/perl/bin/perl.exe
$|=1;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# Information Software SA
# Date In: 2023-02-15
# Updates: 2023-02-15
# Author:Rodolfo Machon
# File Name: agua_sector_notificaciones_rept.pl
#------------------------------------------------------------------------- 
#-------------------------------------------------------------------------
# PERL MODULES
use CGI;
use DBI;
use XML::Simple;
use Data::Dumper;
use Date::Calc qw(Add_Delta_YMD);
use CGI::Carp qw(fatalsToBrowser);
binmode STDOUT, ":encoding(utf8)";
#------------------------------------------------------------------------- 
#-------------------------------------------------------------------------
# HTML PRINTING ACTIVATION
print "Content-type: text/html\n\n";
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#GET PARAMETERS
$form   	    = new CGI;
$c11_sector_id 	= $form->param('c11_sector_id');
$v64_sector    	= $form->param('v64_sector');
$zona           = $form->param('zona');
$v64_zona       = $form->param('v64_zona');
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# CHECK PARAMETERS

#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# GET CLASE SCHEMA AND VARIABLES
get_app_data();
db_connect();
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# GLBAL VARIABLES
$PATH 		= "../".$lan."/";
$file       = $lan."_agua_sector_notificaciones_rept.html";
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# PROGRAM DESCRIPTION
# PROCUREMENT
#$periodo            = $year.$month;
$hoy                = get_fecha_actual();
$periodoAbierto     = get_periodoAbierto();
$periodoAnterior    = get_periodoAnterior();
$trimestreAnterior  = get_trimestreAnterior();
%contribuyentes     = get_contribuyentes();
# INTEGRATION
$notificaciones	= construct_notificaciones();
# PRESENTATION
construct_page();
exit;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#SUB-ROUTINES -> LEVEL 1
sub get_periodoAbierto {
    my $str = "";
	my $sql = "SELECT a.i8_aguaPeriodo_id
			   FROM sdm_aguaPeriodos a
			   WHERE a.c8_estado = 'ABIERTO'
			  ";
    my $sth = $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_periodoAbierto');
	while (my @row = $sth->fetchrow_array) {
		$str = $row[0];
    }
    return $str;
}
sub get_periodoAnterior {
    my $year  = substr($periodoAbierto,0,4);
    my $month = substr($periodoAbierto,4,2);
    my $day   = "01";     
    my ($y,$m,$d) = Add_Delta_YMD($year,$month,$day,0,-1,0);
    if(length($m)<2){$m="0".$m}
    if(length($d)<2){$d="0".$d}
    return ($y.$m)
}
sub get_trimestreAnterior {
    my $year  = substr($periodoAbierto,0,4);
    my $month = substr($periodoAbierto,4,2);
    my $day   = "01";     
    my ($y,$m,$d) = Add_Delta_YMD($year,$month,$day,0,-3,0);
    if(length($m)<2){$m="0".$m}
    if(length($d)<2){$d="0".$d}
    return ($y.$m)
}
sub get_contribuyentes {
    my %hash   = ();
    my $str = "";
    if($zona ne ""){
        $str = " AND a.c2_zona_id='$zona' ";
    }
	my $sql = "SELECT a.i8_agua_id,b.i8_contribuyente_id,CONCAT(c.v64_apellidos,', ',c.v64_nombres),c.cod_contribuyente
			   FROM sdm_aguas a
			   INNER JOIN sdd_agua_contribuyentes b ON b.i8_agua_id=a.i8_agua_id and b.i2_tipo_contribuyente_id='1'
			   LEfT JOIN sdm_contribuyentes c ON c.i8_contribuyente_id=b.i8_contribuyente_id
			   WHERE a.c11_sector_id='$c11_sector_id' AND a.c16_estado='ACEPTADO' $str 
			   GROUP BY c.v64_apellidos,c.v64_nombres
			  ";
    #print "$sql <br>";
    my $sth = $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_contribuyentes');
	while (my @row = $sth->fetchrow_array) {
		$hash{$row[0]} = [@row];
    }
    return %hash;	
}
sub construct_notificaciones {
    my $count = 1;
    my $notificaciones = "";
    my %mes = ('01'=>'ENE','02'=>'FEB','03'=>'MAR','04'=>'ABR','05'=>'MAY','06'=>'JUN','07'=>'JUL','08'=>'AGO','09'=>'SEP','10'=>'OCT','11'=>'NOV','12'=>'DIC');
    foreach my $k (sort {$contribuyentes{$a}[2] cmp $contribuyentes{$b}[2] } keys %contribuyentes){
        my $rowset      = "";
        my $row_head    = "";
        my $row_body    = "";
        my %expediente  = get_expediente($k);
        my %lectura     = get_lectura($k);
        my @cargo       = get_cargo($k);
        if($c11_sector_id eq "SV051214001"){@cargo=get_mensualidad($k);}
        my @saldo       = get_saldo($k);
        my %historial   = get_historial($k);
        
        my $notificacion= get_notificacion();

        my $consumo     = $lectura{'d82_lectura_actual'}-$lectura{'d82_lectura_anterior'};

        foreach my $k (sort keys %historial){
            $row_head .= "<th>".$mes{substr($k,4,2)}."</th>";
            $row_body .= "<td>".int($historial{$k})."</td>";
        }
        $row_head .= "
                        <th>CONTADOR</th>
                        <th>PERIODO </th>
                        <th>LEIDO   </th>
                        <th>INICIAL </th>
                        <th>FINAL   </th>
                        <th>CONSUMO </th>
                        <th>APLICAR </th>        
                    ";
        $row_body .= "<td>".$expediente{'c8_sgm_id'}."</td>";
        $row_body .= "<td>".$lectura{'i8_periodo'}."</td>";
        $row_body .= "<td>".$lectura{'dt_lectura'}."</td>";
        $row_body .= "<td>".$lectura{'d82_lectura_anterior'}."</td>";
        $row_body .= "<td>".$lectura{'d82_lectura_actual'}."</td>";
        $row_body .= "<td>".$consumo."</td>";
        $row_body .= "<td>".$lectura{'dt_aplicacion'}."</td>";        
        
        $rowset = "<tr>".$row_head."</tr><tr>".$row_body."</tr>";
        
        $notificacion =~ s/\{v128_contribuyente}/$contribuyentes{$k}[2]/g;
        $notificacion =~ s/\{i8_contribuyente_id}/$contribuyentes{$k}[1]/g;
        $notificacion =~ s/\{cod_contribuyente}/$contribuyentes{$k}[3]/g;
                       
        $notificacion =~ s/\{txt_referencia}/$expediente{'txt_referencia'}/g;
        $notificacion =~ s/\{i8_agua_id}/$expediente{'i8_agua_id'}/g;
        $notificacion =~ s/\{c8_cuenta_rct_id}/$expediente{'c8_cuenta_rct_id'}/g;
        $notificacion =~ s/\{contador}/$expediente{'c8_sgm_id'}/g;
                       
        $notificacion =~ s/\{rowset}/$rowset/g;
        
        my $val = round($cargo[3],2);
        my $fie = round($cargo[4],2);
        my $tot = round($val+$fie,2);        
        
        $notificacion =~ s/\{i8_periodo}/$cargo[0]/g;
        $notificacion =~ s/\{c16_tarifa_id}/$cargo[1]/g;
        $notificacion =~ s/\{v128_tarifa}/$cargo[2]/g;
        $notificacion =~ s/\{d82_val}/$val/g;
        $notificacion =~ s/\{d82_fie}/$fie/g;
        $notificacion =~ s/\{d82_tot}/$tot/g;
        
        my $saldo_val = round($saldo[0],2);
        my $saldo_fie = round($saldo[1],2);
        my $saldo_int = round($saldo[2],2);
        my $saldo_mul = round($saldo[3],2);
        
        if($saldo_mul<2.87){$saldo_mul=2.87}
        
        my $saldo_tot = round($saldo_val+$saldo_fie+$saldo_int+$saldo_mul,2);        
        
        $notificacion =~ s/\{saldo_val}/$saldo_val/g;
        $notificacion =~ s/\{saldo_fie}/$saldo_fie/g;
        $notificacion =~ s/\{saldo_int}/$saldo_int/g;
        $notificacion =~ s/\{saldo_mul}/$saldo_mul/g;
        $notificacion =~ s/\{saldo_tot}/$saldo_tot/g;
                       
        my $deuda_tot = round($tot+$saldo_tot,2);        
                       
        $notificacion =~ s/\{mes_tot}/$tot/g;
        $notificacion =~ s/\{pendiente_tot}/$saldo_tot/g;
        $notificacion =~ s/\{deuda_tot}/$deuda_tot/g;

        if($count == 2*int($count/2)) {
            $notificaciones .= "<DIV class='even'>".$notificacion."</DIV><br>";
        }else {
            $notificaciones .= "<DIV class='odd'>".$notificacion."</DIV><br>";
        }
        $count++;
    }
    return $notificaciones;
}
sub construct_page {
    open (DATA, "$PATH$file");
	#print "$PATH$file <br>";
    while ($line = <DATA>) {	
        $line =~ s/\{notificaciones}/$notificaciones/g;
        $line =~ s/\{city}/$city/g;
        $line =~ s/\{c11_sector_id}/$c11_sector_id/g;
        $line =~ s/\{v64_sector}/$v64_sector/g;
        $line =~ s/\{zona}/$zona/g;
        $line =~ s/\{v64_zona}/$v64_zona/g;
        $line =~ s/\{hoy}/$hoy/g;
        print "$line";
    }
}
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# LEVEL 2
sub get_notificacion {
	my $boleta="<TABLE class='encabezado'>
                    <TR> 
                        <TD width='10%'><img src='../img/logo.jpg' width='70' height='70'></TD>
                        <TD>
                            <p align='center'>ALCALDIA MUNICIPAL DE {city}<br>
                                SERVICIO DE AGUA -- {v64_sector}</br>
                            </p>
                            <p id='fecha' align='center'>EMITIDO {hoy}</p>
                        </TD>
                        <TD width='10%'><img src='../img/escudo.jpg' width=70' height='70'></TD>                
                    </TR>
                </TABLE>
                <TABLE class='tabla'>
                    <TR>
                        <TH>ID</TH>                        
                        <TH>CODIGO</TH>
                        <TH>CONTRIBUYENTE</TH>
                        <TH>EXPEDIENTE ID</TH>
                        <TH>CUENTA</TH>
                    </TR>
                    <TR>
                        <TD>{i8_contribuyente_id}</TD>
                        <TD>{cod_contribuyente}</TD>
                        <TD>{v128_contribuyente}</TD>
                        <TD>{i8_agua_id}</TD>
                        <TD>{c8_cuenta_rct_id}</TD>
                    </TR>
                </TABLE>
                <TABLE class='tabla'>
                    <TR>
                        <TH>DIRECCION</TH>
                    </TR>
                    <TR>
                        <TD>{txt_referencia}</TD>
                    </TR>
                </TABLE>
                <TABLE class='tabla'>
                    <TR><TH colspan=3>HISTORIAL DE CONSUMO MENSUAL (M3)</TH><TH colspan=7>DATOS DE LECTURA  (M3)</TH></TR>
                    {rowset}
                </TABLE>                            
                <TABLE class='cargo'>
                    <TR><TH colspan=6>CARGOS DEL MES</TH></TR>
                    <TR>
                        <TH>PERIODO</TH>
                        <TH>CODIGO</TH>
                        <TH>TASA</TH>
                        <TH>VALOR</TH>
                        <TH>FIESTAS</TH>
                        <TH>TOTAL</TH>
                    </TR>
                    <TR>
                        <TD>{i8_periodo}</TD>
                        <TD>{c16_tarifa_id}</TD>
                        <TD>{v128_tarifa}</TD>
                        <TD>{d82_val}</TD>
                        <TD>{d82_fie}</TD>
                        <TD>{d82_tot}</TD>
                    </TR>
                </TABLE>
                <TABLE class='tabla'>
                    <TR><TH rowspan='3' width='25%'><img src='../img/escudo.jpg' width='100' height='100'></TH><TH colspan=5>SALDO PENDIENTE</TH><TH colspan=5>TOTALES</TH></TR>
                    <TR>
                        <TH>TASA</TH>
                        <TH>FIESTAS</TH>
                        <TH>INTERESES</TH>
                        <TH>MULTAS</TH>
                        <TH>TOTAL</TH>
                        <TH>CARGO</TH>
                        <TH>SALDO</TH>
                        <TH>TOTAL</TH> 
                    </TR>
                    <TR>
                        <TD>{saldo_val}</TD>
                        <TD>{saldo_fie}</TD>
                        <TD>{saldo_int}</TD>
                        <TD>{saldo_mul}</TD>
                        <TD>{saldo_tot}</TD>
                        <TD>{mes_tot}</TD>
                        <TD>{pendiente_tot}</TD>
                        <TD>{deuda_tot}</TD>
                    </TR>
                </TABLE>                            
                ";
	return $boleta;
}
sub get_expediente {
    my ($id) = @_;
	my %hash	= ();
	my @fields	= split(/\,/,"i8_agua_id,c11_sector_id,c16_codigo_catastral_id,c8_cuenta_rct_id,i2_tipo_sector_id,c2_poligono_id,c2_block_id,c2_zona_id,c2_barrio_id,c4_calle_id,c4_avenida_id,c2_pasaje_id,c2_senda_id,c4_casa,i4_parcela,txt_referencia,c16_estado,dt_aceptacion,c2_zona_id,cod_ruta,v64_sector,nbr_zona,nbr_ruta,c8_sgm_id");
	my $sql	 = "SELECT   e.i8_agua_id
						,e.c11_sector_id
						,e.c16_codigo_catastral_id
						,e.c8_cuenta_rct_id
						,e.i2_tipo_sector_id
						,e.c2_poligono_id
						,e.c2_block_id
						,e.c2_zona_id
						,e.c2_barrio_id
						,e.c4_calle_id
						,e.c4_avenida_id
						,e.c2_pasaje_id
						,e.c2_senda_id
						,e.c4_casa
						,e.i4_parcela
						,e.txt_referencia
						,e.c16_estado
						,e.dt_aceptacion
						,e.c2_zona_id
						,e.cod_ruta
						,s.v64_sector
						,z.nbr_zona
						,r.nbr_ruta
                        ,e.c8_sgm_id
				FROM sdm_aguas e
				LEFT JOIN sdm_sectores s ON s.c11_sector_id=e.c11_sector_id
				LEFT JOIN sdm_zonas z ON z.cod_sector=e.cod_sector AND z.cod_zona=e.c2_zona_id
				LEFT JOIN sdm_rutas r ON r.cod_sector=e.cod_sector AND r.cod_zona=e.c2_zona_id AND r.cod_ruta=e.cod_ruta		
				WHERE e.i8_agua_id='$id' and e.c16_estado='ACEPTADO'
			  ";
	#print "$sql";
    my $sth = $dbh->prepare($sql);
    $sth->execute or die 'sub get_user';
    if (my @row = $sth->fetchrow_array) {
		for(my $i;$i<=$#row;$i++){
			$hash{$fields[$i]} = $row[$i];
		}
	}
    return %hash;
}
sub get_lectura {
    my ($id) = @_;    
	my %hash	= ();
	my @fields	= split(/\,/,"i8_agua_id,i8_periodo,dt_lectura,d82_lectura_anterior,d82_lectura_actual,dt_aplicacion,c3_tipo_lectura_id,txt_comentario");
	my $sql	 = "SELECT   l.i8_agua_id
						,l.i8_periodo
						,l.dt_lectura
						,l.d82_lectura_anterior
						,l.d82_lectura_actual
						,l.dt_aplicacion
						,l.c3_tipo_lectura_id
						,l.txt_comentario
				FROM sdd_agua_lecturas l
				WHERE l.i8_agua_id='$id' and l.i8_periodo='$periodoAnterior'
			  ";
	#print "$sql";
    my $sth = $dbh->prepare($sql);
    $sth->execute or die 'sub get_lectura';
    if (my @row = $sth->fetchrow_array) {
		for(my $i;$i<=$#row;$i++){
			$hash{$fields[$i]} = $row[$i];
		}
	}
    return %hash;
}
sub get_cargo {
    my ($id) = @_;    
	my $sql	 = "SELECT   e.i8_periodo
                        ,e.c16_tarifa_id
                        ,t.v128_tarifa
                        ,e.d82_val
                        ,e.d82_fie
				FROM sdd_agua_estados_mensuales e
                LEFT JOIN sdm_tarifas t ON t.c16_tarifa_id=e.c16_tarifa_id
				WHERE e.i8_agua_id='$id' and e.i8_periodo='$periodoAbierto'
			  ";
	#print "$sql";
    my $sth = $dbh->prepare($sql);
    $sth->execute or die 'sub get_cargo';
    my @row = $sth->fetchrow_array;
    return @row;
}
sub get_mensualidad {
    my ($id) = @_;    
	my $sql	 = "SELECT   e.i8_periodo
                        ,e.c16_tarifa_id
                        ,t.v128_tarifa
                        ,e.d82_val
                        ,e.d82_fie
				FROM sdd_agua_mensualidades e
                LEFT JOIN sdm_tarifas t ON t.c16_tarifa_id=e.c16_tarifa_id
				WHERE e.i8_agua_id='$id' and e.i8_periodo='$periodoAbierto '
			  ";
	#print "$sql";
    my $sth = $dbh->prepare($sql);
    $sth->execute or die 'sub get_cargo';
    my @row = $sth->fetchrow_array;
    return @row;
}
sub get_saldo {
    my ($id) = @_;    
	my $sql	 = "SELECT sum(e.d82_val) as 'val',
                       sum(e.d82_fie) as 'fie',
                       sum(e.d82_int) as 'int',
                       sum(e.d82_mul) as 'mul'
                FROM sdd_agua_estados_mensuales e
                WHERE e.i8_agua_id='$id' and
                      e.i8_periodo != '$periodoAbierto'
                GROUP BY e.i8_agua_id
			  ";
	#print "$sql";
    my $sth = $dbh->prepare($sql);
    $sth->execute or die 'sub get_saldo';
    my @row = $sth->fetchrow_array;
    return @row;
}
sub get_historial {
    my ($id) = @_;    
	my %hash	= ();
	my $sql	 = "SELECT   b.i2_grupo_id
                        ,b.f4_total
				FROM sdd_agua_bases_imponibles b
				WHERE b.i8_agua_id='$id' and b.i2_grupo_id>='$trimestreAnterior' and b.i2_grupo_id<'$periodoAbierto'
                ORDER BY b.i2_grupo_id
			  ";
	#print "$sql";
    my $sth = $dbh->prepare($sql);
    $sth->execute or die 'sub get_estado';
    while (my @row = $sth->fetchrow_array) {
		$hash{$row[0]} = $row[1];
	}
    return %hash;
}
#-------------------------------------------------------------------------
# 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};
    $country    = $data->{portal}->{country};
}
sub SQL_fail {
    my ($query,$dbh,$section)= @_;
    print "SQL failed in Section $section<br>";
    print "Query: ".$query."<br>";    
    exit;
}
sub db_connect {
	$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;	
}
sub get_fecha_actual {
    my $sql = "SELECT DATE_FORMAT(CURRENT_DATE,'%Y-%m-%d')";
	my $sth = $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_fecha_actual');
	if (my @row = $sth->fetchrow_array) {
		return ($row[0]);
	}else{
		return (0)
	}
}
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;
}
#-------------------------------------------------------------------------