#!/perl/bin/perl.exe
$|=1;
#-------------------------------------------------------------------
#-------------------------------------------------------------------
#Perl Modules
use CGI;
use Win32::ODBC;
#-------------------------------------------------------------------
#-------------------------------------------------------------------
#HTML printing activation
print "Content-type: text/html\n\n";
#-------------------------------------------------------------------
#-------------------------------------------------------------------
#Getting parameters
$form			= new CGI;
$lan			= $form->param('lan');
$appid			= $form->param('appid');
$clase			= $form->param('clase');
$i2_tipo_rango_mora_id  = $form->param('i2_tipo_rango_mora_id');
$c11_sector_id          = $form->param('c11_sector_id');
$field_value            = $form->param('field_value'); ## avenida, pasaje, calle, senda, poligono, block
$field                  = $form->param('field');
#------------------------------------------------------------------
#------------------------------------------------------------------
#Define subdirectories and general variables
$PATH		= "../".$lan."/";
$file 		= $lan."_mora_sector_dias_rept.html";
$table 		= "<table cellspacing=0 cellpadding=0 width=100% border=1 bordercolor=#E5E5E5 style=border-width:0.5>";
#-------------------------------------------------------------------
#-------------------------------------------------------------------
# Open Data Base Connections
if (!($db = new Win32::ODBC($appid))){connection_fail('$appid')}
#-------------------------------------------------------------------
#-------------------------------------------------------------------
# MAIN-PROGRAM
%tributos	=get_tributos();
%rangos         =get_rangos();
get_expedientes_rangos();
get_mora();
construct_page();
$db->Close();
exit;
#-------------------------------------------------------------------
#MAIN-SUBROUTINES
sub get_tributos {
	%tributos=();
	$sql = "select DISTINCT(a.`c8_tributo_id`), b.`v64_tributo`
		from sdd_".$clase."_servicios a
		inner join sdm_tributos b on a.`c8_tributo_id` = b.`c8_tributo_id` and a.c2_gravable='SI'
		order by 1";
        if ( $db->Sql($sql) ){SQL_fail($db,$sql,'sub get_records - obtener Tributos')};
	while($db->FetchRow()){
		undef my @row1;
		@row	= $db->Data();
                $tributos{$row[0]} = [@row];
        }
	return %tributos;
}
sub get_rangos {
	%rangos=();
	$sql = "select i2_rango_id,i4_rango_inicial,i4_rango_final, v64_rango from sdm_rangos_dim where i2_tipo_rango_mora_id = $i2_tipo_rango_mora_id order by 1";
        if ( $db->Sql($sql) ){SQL_fail($db,$sql,'sub get_records - obtener Tributos')};
	while($db->FetchRow()){
		undef my @row1;
		@row	= $db->Data();
                $rangos{$row[0]} = [@row];
                $str_rangos.="<td>$row[3]</td>";
        }
	return %rangos;
}
sub get_expedientes_rangos {
        $str_filtro = get_filtro_ubicacion();
        foreach $k (sort numeros keys %rangos) {
                $rango_inicial  = $rangos{$k}[1];
                $rango_final    = $rangos{$k}[2];
                ($rangos{$k}[4],$rangos{$k}[5])  = get_expedientes($rango_inicial,$rango_final,$str_filtro);
        }
}
sub get_mora {
        %rangos_tributos=();
        $table="<table cellspacing=0 cellpadding=0 width=100% border=1 bordercolor=#E5E5E5 style=border-width:0.5><tr class=my_style2><td><b>TRIBUTO</td>$str_rangos<td><strong>SUBTOTAL</strong></td></tr>";
        foreach $k (sort keys %tributos) {
                $c8_tributo_id  = $tributos{$k}[0];
                $v64_tributo    = $tributos{$k}[1];
                $subtotal=0;
                #print "Tributo:$c8_tributo_id,- $v64_tributo";
                $table.="<tr><td>$v64_tributo</td>";
                foreach $kk (sort numeros keys %rangos) {
                        $rango_inicial     =    $rangos{$kk}[1];
                        $rango_final       =    $rangos{$kk}[2];
                        #$rango            =    $rangos{$kk}[3];
                        $str_expedientes   =    $rangos{$kk}[4];
                        my ($d82_val,$d82_fie,$d82_int,$d82_mul)  =    get_mora_tributo($c8_tributo_id,$str_expedientes,$rango_inicial,$rango_final);
                        $rangos_tributos{$rangos{$kk}[0]}{$c8_tributo_id}[0]=$d82_val;
                        $rangos_tributos{$rangos{$kk}[0]}{$c8_tributo_id}[1]=$d82_fie;
                        $rangos_tributos{$rangos{$kk}[0]}{$c8_tributo_id}[2]=$d82_int;
                        $rangos_tributos{$rangos{$kk}[0]}{$c8_tributo_id}[3]=$d82_mul;
                        $subtotal+=$d82_val;
                        $table            .=   "<td align=right>$d82_val</td>";      
                }
                $table.="<td align=right>$subtotal</td></tr>";
        }
        ##accesorios
        $table.="<tr><td>FIESTAS</td>";
        $subtotal=0;
        foreach $k (sort numeros keys %rangos){
                $d82_fie=0;
                foreach $kk(sort keys %tributos){$d82_fie+=$rangos_tributos{$k}{$kk}[1];$subtotal+=$rangos_tributos{$k}{$kk}[1];}
                $table.="<td align=right>$d82_fie</td>";
        }
        $table.="<td align=right>$subtotal</td></tr>";
        $table.="<tr><td>INTERESES</td>";
        $subtotal=0;
        foreach $k (sort numeros keys %rangos) {
                $d82_int=0;
                foreach $kk(sort keys %tributos){$d82_int+=$rangos_tributos{$k}{$kk}[2];$subtotal+=$rangos_tributos{$k}{$kk}[2];}
                $table.="<td align=right>$d82_int</td>";
        }
        $table.="<td align=right>$subtotal</td></tr>";
        $table.="<tr><td>MULTAS</td>";
        $subtotal=0;
        foreach $k (sort numeros keys %rangos) {
                $d82_mul=0;
                foreach $kk(sort keys %tributos){$d82_mul+=$rangos_tributos{$k}{$kk}[3];$subtotal+=$rangos_tributos{$k}{$kk}[3];}
                $table.="<td align=right>$d82_mul</td>";
        }
        $table.="<td align=right>$subtotal</td></tr>";
        ####TOTAL
        $table.="<tr><td>TOTALES</td>";
        $subtotal=0;
        foreach $k (sort numeros keys %rangos) {
                $stt=0;
                foreach $kk(sort keys %tributos){$stt+=($rangos_tributos{$k}{$kk}[0]+$rangos_tributos{$k}{$kk}[1]+$rangos_tributos{$k}{$kk}[2]+$rangos_tributos{$k}{$kk}[3]);$subtotal+=($rangos_tributos{$k}{$kk}[0]+$rangos_tributos{$k}{$kk}[1]+$rangos_tributos{$k}{$kk}[2]+$rangos_tributos{$k}{$kk}[3]);}
                $table.="<td align=right>\$$stt</td>";
        }
        $table.="<td align=right>\$$subtotal</td></tr>";
        ####Total Cuentas x rango
        $table.="<tr><td>EXPEDIENTES</td>";
        $subtotal=0;
        $str_expedientes="";
        foreach $k (sort numeros keys %rangos) {
                $stt=$rangos{$k}[5];
                $subtotal+=$stt;
                if($rangos{$k}[4] ne "0") {
                        $str_expedientes.=$rangos{$k}[4].",";
                        #print "<br>Rangos:$rangos{$k}[4]";
                        #print "<br>Expedientes:$str_expedientes";
                }
                $table.="<td align=right><a href=javascript:OpenListExpedientes('$rangos{$k}[4]',$rangos{$k}[5])>$stt</a></td>";
        }
        $table.="<td align=right><a href=javascript:OpenListExpedientes('$str_expedientes',$subtotal)>$subtotal</a></td></tr>";
        $table.="</table>";
        #print $table;
}
sub construct_page {
	my ($rowset)= @_;
	open (DATA, "$PATH$file");
	if($clase eq "inmueble"){$clases=" INMUEBLES "}
	if($clase eq "empresa"){$clases=" EMPRESAS "}
	while ($line = <DATA>) {	
		$line =~ s/\{lan}/$lan/g;
		$line =~ s/\{appid}/$appid/g;
		$line =~ s/\{table}/$table/g;	 
		$line =~ s/\{clase}/$clase/g;
		$line =~ s/\{clases}/$clases/g;
		print "$line";
	}
}
#-------------------------------------------------------------------
#SUBROUTINES Level 1
sub get_filtro_ubicacion {
        $str_exp_ubicacion="";
        if($c11_sector_id ne "" and $clase eq "inmueble"){
                if($field_value ne ""){$add_sql=" and $field=$field_value"}
                #Filtrando expedientes por ubicacion
                $sql="select i8_".$clase."_id from sdm_".$clase."s where c11_sector_id like '$c11_sector_id%' $add_sql";
                #$print $sql;
                if ( $db->Sql($sql) ){SQL_fail($db,$sql,'sub get_records - obtener Expedientes')};
                while($db->FetchRow()) {
                        undef my @row1;
                        @row	                   = $db->Data();
                        $str_exp_ubicacion        .= $row[0].",";
                }
                if($str_exp_ubicacion ne ""){$str_exp_ubicacion=substr($str_exp_ubicacion,0,length($str_exp_ubicacion)-1);}else{print "No se encontraron Expedientes !!";exit;}
        }
        return $str_exp_ubicacion;
}
sub get_expedientes {
        my($rango_inicial,$rango_final,$filtro)=@_;
        if($filtro ne ""){$filtro_exp=" and i8_".$clase."_id in ($filtro) "}
        $str_expedientes="";
        if($i2_tipo_rango_mora_id eq "1"){$condition=" max(i4_dias_mora) "}else{$condition=" (sum(d82_val)+sum(d82_fie)+sum(d82_int)+sum(d82_mul)) "}
        $sql="select i8_".$clase."_id from sdd_".$clase."_estados_mensuales where i4_dias_mora>0 $filtro_exp group by i8_".$clase."_id having $condition between $rango_inicial and  $rango_final";
        $count=0;
        #print "$sql<br>";
        if ( $db->Sql($sql) ){SQL_fail($db,$sql,'sub get_records - obtener Expedientes')};
	while($db->FetchRow()) {
		undef my @row1;
		@row	= $db->Data();
                $str_expedientes        .= $row[0].",";
                $count++;
        }
        if($str_expedientes ne ""){$str_expedientes=substr($str_expedientes,0,length($str_expedientes)-1);}else{$str_expedientes = "0"}
        return ($str_expedientes,$count);
}
sub get_mora_tributo {
        my($c8_tributo_id,$str_expedientes,$rango_inicial,$rango_final)=@_;
        $sql="select sum(d82_val),sum(d82_fie),sum(d82_int),sum(d82_mul)
        from sdd_".$clase."_estados_mensuales where c16_tarifa_id like '$c8_tributo_id%' and i8_".$clase."_id in ($str_expedientes) and i4_dias_mora > 0";

        if ( $db->Sql($sql) ){SQL_fail($db,$sql,'sub get_records - Get Cell Value')};
	while($db->FetchRow()) {
		undef my @row1;
		@row	 = $db->Data();
                $d82_val = $row[0];
                $d82_fie = $row[1];
                $d82_int = $row[2];
                $d82_mul = $row[3];
        }
        if($d82_val eq ""){$d82_val=0}
        if($d82_fie eq ""){$d82_fie=0}
        if($d82_int eq ""){$d82_int=0}
        if($d82_mul eq ""){$d82_mul=0}
        return ($d82_val,$d82_fie,$d82_int,$d82_mul);
}
#--------------------------------------------------------------------
#AUXILIAR-SUBROUTINES
sub numeros{$a<=>$b}
sub SQL_fail {
    my ($dbh,$query,$section)= @_;
    print "SQL failed in Section $section<br>";
    print "Query: ".$query."<br>";    
    print "Error: ".$dbh->Error()."\n<br>";
    $dbh->Close();
    exit;
}
sub connect_fail {
    my ($DSN)=@_;
    print "Error connecting to $DSN<br>";
    print "Error:".Win32::ODBC::Error()."<br>";
    exit;
}
#--------------------------------------------------------------------