#!/perl/bin/perl.exe
$|=1;
#--------------------------------------------------------------------------------
#--------------------------------------------------------------------------------
# Web Software
# Date In: 2013-05-25
# Updates: 2013-05-25,2016-05-26
# Author:Rodolfo Machon:
# Update by: Guillermo Machon
# File Name: agua_mora_sectores_rept.pl
#------------------------------------------------------------------------- 
#-------------------------------------------------------------------------
#PERL MODULES
use CGI;
use CGI::Carp qw(fatalsToBrowser);
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; charset=utf-8\n\n";
#--------------------------------------------------------------------------
#--------------------------------------------------------------------------
# GET PARAMETERS
$form       = new CGI;
$entidad 	= $form->param('entidad');
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# GET CLASE SCHEMA AND VARIABLES
get_app_data();
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# GLOBAL VARIABLES
$PATH 		= "../".$lan."/";
$file       = $lan."_agua_mora_sectores_rept.html";
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# GET CLASE SCHEMA AND VARIABLES
$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;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# PROGRAM DESCRIPTION
# PROCUREMENT
%sector_aguas	= get_sector_aguas();
%sectores_mora	 = get_sectores_mora();
# INTEGRATION
$table 		   		= construct_table();
# PRESENTATION
construct_page();
exit;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#SUB-ROUTINES -> LEVEL 1
sub get_sector_aguas {
    my %hash  = ();
    my $sql = "SELECT  a.cod_sector
                     ,a.c2_zona_id
                     ,a.c11_sector_id
                 FROM sdm_aguas a
                 WHERE a.c16_estado='ACEPTADO'
				";
    my $sth = $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_sectores_expedientes ');
	while (my @row = $sth->fetchrow_array) {
		$hash{$row[0]}{$row[1]} = $row[2];
    }
    return %hash;
}
sub get_sectores_mora {
	my %secs= ();	
	my $sql	= "select  a.cod_sector,
                        a.c2_zona_id,
                        b.dt_generado,
                        count(DISTINCT(a.i8_agua_id)),
                        SUM(b.d82_val),
                        SUM(b.d82_fie),
                        SUM(b.d82_int),
                        SUM(b.d82_mul)
                 from sdm_aguas a
                      left join sdd_agua_estados_mensuales b on a.i8_agua_id = b.i8_agua_id
                 where b.i4_dias_mora>0 and b.d82_val>0  and a.c16_estado='ACEPTADO'
                 group by a.cod_sector,a.c2_zona_id
                 order by a.cod_sector,a.c2_zona_id
		   ";
		#print $sql;

  my $sth = $dbh->prepare($sql);
  $sth->execute or die 'sub get_sectores_mora';
  while (@row = $sth->fetchrow_array) {
		$secs{$row[0]}{$row[1]}    = [@row];
		$secs{$row[0]}{$row[1]}[8] = sprintf("%.2f",$row[4]+$row[5]+$row[6]+$row[7]);
	}
	return %secs;
}
sub construct_table {
    my $count   = 1;
    my $str   	= "";
    my $rowset  = "";
    my $tbody   = "";
    my $t_exp = 0;        
    my $t_exp_nora = 0;
    my $t_val = 0;
    my $t_fie = 0;               
    my $t_int = 0;
    my $t_mul = 0;
    my $t_mora= 0;
    my $thead   = "<thead>
                        <tr>
                            <td>No.</td>
                            <td>COD</td>
                            <td>SECTOR ID</td>
                            <td>TIPO SECTOR</td>
                            <td>SECTOR</td>
                            <td>COD</td>
                            <td>ZONA</td>
                            <td>EXPEDIENTES</td>
                            <td>GENERADO</td>
                            <td>EN MORA</td>
                            <td>VALOR</td>
                            <td>FIESTA</td>
                            <td>INTERES</td>
                            <td>MULTA</td>
                            <td>MORA</td>
                        </tr>
                   </thead>";
    my $tfoot   = "<tfoot>
                        <tr>
                            <td colspan=6></td>
                            <td>TOTAL</td>
                            <td align=right>{t_exp}</td>
                            <td></td>
                            <td align=right>{t_exp_mora}</td>
                            <td align=right>{t_val}</td>
                            <td align=right>{t_fie}</td>
                            <td align=right>{t_int}</td>
                            <td align=right>{t_mul}</td>
                            <td align=right>{t_mora}</td>
                        </tr>
                   </tfoot>";
	foreach my $k (sort keys %sector_aguas){
        foreach my $kk (sort keys %{$sector_aguas{$k}}){
            my @sector = get_sector($sector_aguas{$k}{$kk});
            my @mora = @{$sectores_mora{$k}{$kk}};
            my $zona = get_zona($k,$kk);
            my $qty  = get_sector_zona_aguas($k,$kk);
            if($qty>0){
                my $row   = "<td>".$count."</td>
                             <td>".$k."</td>
                             <td>".$sector_aguas{$k}{$kk}."</td>
                             <td>".$sector[1]."</td>
                             <td><a href=\"javascript:OpenSector('$sector_aguas{$k}{$kk}','$sector[1] $sector[0]','$kk','$zona')\">".$sector[0]."</a></td>
                             <td>".$kk."</td> 
                             <td>".$zona."</td> 
                             <td>".$qty."</td>
                             <td>".$mora[2]."</td>
                             <td>".$mora[3]."</td>
                             <td>".round($mora[4],2)."</td>
                             <td>".round($mora[5],2)."</td>
                             <td>".round($mora[6],2)."</td>
                             <td>".round($mora[7],2)."</td>
                             <td>".round($mora[8],2)."</td>
                             ";					 
                $rowset  .= "<tr>".$row."</tr>";
                $t_exp += $qty;
                $t_exp_nora += $mora[3];
                $t_val  += round($mora[4],2);
                $t_fie  += round($mora[5],2);               
                $t_int  += round($mora[6],2);               
                $t_mul  += round($mora[7],2);               
                $t_mora += round($mora[8],2);               

                $count++;
            }
        }
	}
    $tfoot	=~ s/\{t_exp}/$t_exp/g;
    $tfoot	=~ s/\{t_exp_mora}/$t_exp_nora/g;
    $tfoot	=~ s/\{t_val}/$t_val/g;
    $tfoot	=~ s/\{t_fie}/$t_fie/g;
    $tfoot	=~ s/\{t_int}/$t_int/g;
    $tfoot	=~ s/\{t_mul}/$t_mul/g;
    $tfoot	=~ s/\{t_mora}/$t_mora/g;
            
    $tbody	= "<tbody>".$rowset."</tbody>";
    $str	= "<table id=grid>".$thead.$tbody.$tfoot."</table>";
    return $str;
}
sub construct_page {
    open (DATA, "$PATH$file");
    while ($line = <DATA>) {	
        $line =~ s/\{lan}/$lan/g;
        $line =~ s/\{appid}/$appid/g;
        $line =~ s/\{entidad}/$entidad/g;
        $line =~ s/\{table}/$table/g;
        $line =~ s/\{city}/$city/g;
        $line =~ s/\{state}/$state/g;
        print "$line";
    }
}

#-------------------------------------------------------------------------
# LEVEL 2
sub get_sector {
    my($id)=@_;
    my @array = ();
    my $sql = "SELECT  a.v64_sector
                      ,b.v64_tipo_sector
                FROM sdm_sectores a
                LEFT JOIN sdm_tipo_sectores b ON a.i2_tipo_sector_id = b.i2_tipo_sector_id
                WHERE a.c11_sector_id = '$id'
                ";
    #print "$sql <br>";
    my $sth = $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_sector');
	while (my @row = $sth->fetchrow_array) {
		@array = @row;
    }
    return @array;    
}
sub get_zona {
    my($sector,$zona)=@_;
    my $str = ();
    my $sql = "SELECT  a.nbr_zona
                FROM sdm_zonas a
                WHERE a.cod_sector='$sector' and a.cod_zona = '$zona'
                ";
    #print "$sql <br>";
    my $sth = $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_zonas');
	while (my @row = $sth->fetchrow_array) {
		$str = $row[0];
    }
    return $str;     
}
sub get_sector_zona_aguas {
    my($sector,$zona)=@_;
    my $str = ();
    my $sql = "SELECT  count(i8_agua_id)
                FROM sdm_aguas a
                WHERE a.cod_sector = '$sector' and a.c2_zona_id='$zona'
                ";
    #print "$sql <br>";
    my $sth = $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_sector');
	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};    
}
sub SQL_fail {
    my ($sql,$dbh,$section)= @_;
    print "SQL failed in Section $section<br>";
    print "Query: ".$sql."<br>";    
    exit;
}
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;
}
#-------------------------------------------------------------------------
