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

#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# GET CLASE SCHEMA AND VARIABLES
get_app_data();
db_connect();
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# GLBAL VARIABLES
$PATH 		= "../".$lan."/";
$file       = $lan."_agua_sector_lecturas_rept.html";
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# PROGRAM DESCRIPTION
# PROCUREMENT
$periodoAbierto = get_periodoAbierto();
%contribuyentes = get_contribuyentes();
%periodos	= get_periodos();
%lecturas	= get_lecturas();
# INTEGRATION

# PRESENTATION
$table 		   	= construct_table();
construct_page();
exit;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#SUB-ROUTINES -> LEVEL 1
sub get_contribuyentes {
    my %hash   = ();
	my $sql = "SELECT a.i8_agua_id,b.i8_contribuyente_id,CONCAT(c.v64_apellidos,', ',c.v64_nombres)
			   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'
			   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 get_periodos {
    my %hash   = ();
	my $sql = "SELECT a.i8_periodo,SUBSTRING(CAST(a.i8_periodo as CHAR(6)),5,2) as mes
			   FROM sdd_agua_lecturas a
			   INNER JOIN sdm_aguas b ON b.i8_agua_id=a.i8_agua_id AND b.c16_estado='ACEPTADO'
			   WHERE SUBSTRING(a.i8_periodo,1,4) = '$year' AND b.c11_sector_id='$c11_sector_id'
			   GROUP BY a.i8_periodo
			  ";
    #print $sql;
    my $sth = $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_periodos');
	while (my @row = $sth->fetchrow_array) {
		$hash{$row[0]} = $row[1];
    }
    return %hash;
}
sub get_lecturas {
    my %hash   = ();
	my $sql = "SELECT a.i8_agua_id,a.i8_periodo,a.d82_lectura_anterior,a.d82_lectura_actual,a.dt_lectura
				 FROM sdd_agua_lecturas a
				 INNER JOIN sdm_aguas b ON b.i8_agua_id=a.i8_agua_id AND b.c16_estado='ACEPTADO'
				 WHERE SUBSTRING(i8_periodo,1,4) = '$year' AND b.c11_sector_id='$c11_sector_id'
				 ORDER BY a.i8_agua_id
				 ";
    #print "$sql <br>";
    my $sth = $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_tarifas');
	while (my @row = $sth->fetchrow_array) {
		$hash{$row[0]}{$row[1]} = [@row];
    }
    return %hash;
}
sub construct_table {
    my $count   = 1;
	my $rowset	= "";	
	my $thead_s	= "";
    my $thead_l	= "";
	my %mes = ('01'=>'ENERO','02'=>'FEBRERO','03'=>'MARZO','04'=>'ABRIL','05'=>'MAYO','06'=>'JUNIO','07'=>'JULIO','08'=>'AGOSTO','09'=>'SEPTIEMBRE','10'=>'OCTUBRE','11'=>'NOVIEMBRE','12'=>'DICIEMBRE');	
	my $thead   = "<thead>
                        <tr><td colspan=4></td>{thead_s}</tr>
                        <tr><td>No.</td><td>CUENTA ID</td><td>CONTRIBU.ID</td><td>CONTRIBUYENTE</td>{thead_l}</tr>
                   </thead>";
    my $periodoAbierto = get_periodoAbierto();
    if(!%periodos){
        if($periodoAbierto ne ""){
            %periodos = ($periodoAbierto=>substr($periodoAbierto,4,2))
        }
    }
	foreach my $k (sort  {$a<=>$b} keys %periodos){
        $thead_s .= "<td colspan=3 align=center>".$mes{$periodos{$k}}."</td>";
        $thead_l .= "<td align=center>ANTES</td><td align=center>ACTUAL</td><td align=center>TOMADO</td>";
    }
    foreach my $k (sort {$contribuyentes{$a}[2] cmp $contribuyentes{$b}[2] } keys %contribuyentes){
		my $row = "<td>".$count."</td>";
		$row .= "<td>".$k."</td>";
		$row .= "<td>".$contribuyentes{$k}[1]."</td>";
		$row .= "<td>".$contribuyentes{$k}[2]."</td>";		
		foreach my $kk (sort {$a<=>$b} keys %periodos){
			if($periodoAbierto eq $kk){
				$row .= "<td><input type='number' id='B_$k' onchange=saveAgualectura('$k','$kk',this.value) value='".int($lecturas{$k}{$kk}[2])."'></td>";
                $row .= "<td><input type='number' id='A_$k' onchange=saveAgualectura('$k','$kk',this.value) value='".int($lecturas{$k}{$kk}[3])."'></td>";
                $row .= "<td><input type='number' id='F_$k' onchange=saveAgualectura('$k','$kk',this.value) value='".$lecturas{$k}{$kk}[4]."'></td>";
			}else{
				$row .= "<td>".int($lecturas{$k}{$kk}[2])."</td>";
                $row .= "<td>".int($lecturas{$k}{$kk}[3])."</td>";
                $row .= "<td>".$lecturas{$k}{$kk}[4]."</td>";
			}
		}				 
		$rowset .= "<tr>".$row."</tr>";
		$count++;		
    }
	$thead  =~ s/\{thead_s}/$thead_s/g;
	$thead  =~ s/\{thead_l}/$thead_l/g;          
    $rowset = "<tbody>".$rowset."</tbody>";
    $table  = "<table id=grid>".$thead.$rowset."</table>";
    return $table;
}
sub construct_page {
    open (DATA, "$PATH$file");
	#print "$PATH$file <br>";
    while ($line = <DATA>) {	
        $line =~ s/\{lan}/$lan/g;
        $line =~ s/\{appid}/$appid/g;
        $line =~ s/\{table}/$table/g;
        $line =~ s/\{c11_sector_id}/$c11_sector_id/g;
        $line =~ s/\{v64_sector}/$v64_sector/g;
        $line =~ s/\{hoy}/$hoy/g;
        $line =~ s/\{city}/$city/g;
        $line =~ s/\{state}/$state/g;
        $line =~ s/\{periodoAbierto}/$periodoAbierto/g;
        print "$line";
    }
}
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# LEVEL 2
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;
}
#-------------------------------------------------------------------------
# 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;	
}
#-------------------------------------------------------------------------