#!/perl/bin/perl.exe
$|=1;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# Author:Rodolfo Machon
# Company:WebSoft, SA de CV 
# Nombre del archivo: mora_sectore_expedietes_rept.pl
# Created: 2022-11-27
# Updates: 2022-11-27
# Copyright - All Rights Reserved
#------------------------------------------------------------------------- 
#-------------------------------------------------------------------------
#PERL MODULES
use CGI;
use CGI::Carp qw(fatalsToBrowser);
use XML::Simple;
use DBI;
binmode STDOUT, ":encoding(utf8)";
#--------------------------------------------------------------------------
#--------------------------------------------------------------------------
print "Content-type: text/html; charset=utf-8\n\n";
#--------------------------------------------------------------------------
#--------------------------------------------------------------------------
# GET PARAMETERS
$form 		= new CGI;
$lan		= $form->param('lan');
$appid		= $form->param('appid');
$clase		= $form->param('clase');
$str_expedientes= $form->param('str_expedientes');
$c11_sector_id	= $form->param('c11_sector_id');
#--------------------------------------------------------------------------
#--------------------------------------------------------------------------
# OPEN DATABASE CONNECTIONS - ODBCs
get_app_data();
open_database_connections();
#-------------------------------------------------------------------
#-------------------------------------------------------------------
#Define los subdirectorios a utilizar en el programa
$PATH	= "../".$lan."/";
$file   = "es_mora_sector_expedientes_rept.html";          
$table = "<table id=Grid cellspacing=0 cellpadding=0 width=100% border=1 bordercolor=#E5E5E5 style=border-width:0.5>";
#-------------------------------------------------------------------
#-------------------------------------------------------------------
if($c11_sector_id ne ""){$str_expedientes = get_expedientes($c11_sector_id)}
generar_lista_expedientes();
construct_page();

exit;
#-------------------------------------------------------------------
#-------------------------------------------------------------------
# AUXILIAR-SUBROUTINES
sub get_expedientes {
	my $stream        = "";
	my $database	= $appid;
	my $table		= "sdm_".$clase."s";
	my $select_fields	= "i8_".$clase."_id";
	my $order_by	= "1";
	my $sql         = "";
	if($clase eq "empresa"){
	      $sql ="SELECT a.i8_empresa_id FROM $table a,sdd_empresa_ubicacion b WHERE a.i8_empresa_id=b.i8_empresa_id and a.c16_estado='ACEPTADO' AND b.c11_sector_id='$c11_sector_id' ORDER BY $order_by";
	}else{
	      $sql ="SELECT $select_fields FROM $table WHERE c16_estado='ACEPTADO' AND c11_sector_id='$c11_sector_id' ORDER BY $order_by";                  
	}
	my $sth = $dbh->prepare($sql);
	$sth->execute or die 'sub get_sectores';
	while (@row = $sth->fetchrow_array) {
	      $stream .= $row[0].",";
	}
	$stream = substr($stream,0,length($stream)-1);
	return $stream;
}
sub generar_lista_expedientes {
        $table.="<tr><td class=tdheader1><input type=checkbox name=chk onClick=MarkAll() class=checkbox>No.</td><td class=tdheader1>EXPEDIENTE</td><td class=tdheader1>PARCELA</td><td class=tdheader1>NOMBRE&nbsp;DEL&nbsp;TITULAR&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</td><td class=tdheader1>DIRECCION</td><td class=tdheader1>VALOR_TRIBUTOS</td><td class=tdheader1>ACC_FIESTA</td><td class=tdheader1>ACC_INTERES</td><td class=tdheader1>ACC_MULTA</td><td class=tdheader1>SUBTOTAL</td><td class=tdheader1>COBRO_ADMIN&nbsp;</td></tr>";
        @expedientes=split(",",$str_expedientes);
        $cnt=$#expedientes;
        $contador=1;
        for($i=0;$i<=$cnt;$i++)	{
                $i8_expediente_id=$expedientes[$i];
                ($d82_val,$d82_fie,$d82_int,$d82_mul)= get_mora($i8_expediente_id,$clase);
		if($d82_val>0){
			#print "<br>$i8_inmueble_id,$d82_val,$d82_fie,$d82_int,$d82_mul,$d82_total";
			my($sector,$barrio,$calle,$avenida,$pasaje,$senda,$poligono,$block,$casa)=get_direccion($i8_expediente_id,$clase);
			$str_direccion=$sector;
			if($avenida ne ""){$str_direccion.=", $avenida"}
			if($calle ne ""){$str_direccion.=", $calle"}
			if($pasaje ne ""){$str_direccion.=", $pasaje"}
			if($senda ne ""){$str_direccion.=", $senda"}
			if($poligono ne ""){$str_direccion.=", $poligono"}
			if($block ne ""){$str_direccion.=", $block"}
			if($casa ne ""){$str_direccion.=" # $casa"}
			
			#print "<br>Direccion:$sector";
			my($contribuyente)=get_nombre($i8_expediente_id,$clase);
			#print "<br>Contribuyente:$contribuyente";
			
			$str_notificacion="Agregar";
			$sql="select b.v64_tipo_notificacion,a.dt_administrativo from sdd_".$clase."_administrativo a 
			inner join sdm_tipo_notificaciones b on a.i2_tipo_notificacion_id=b.i2_tipo_notificacion_id
			where a.i8_".$clase."_id=$i8_expediente_id
			order by b.i2_tipo_notificacion_id desc limit 1";
			my $sth = $dbh->prepare($sql);
			$sth->execute or die 'sub get_sectores';
			while (@row = $sth->fetchrow_array) {
				$str_notificacion=$row[0];
				$str_notificacion.=" *";
				$str_notificacion.=$row[1];
			}
	
			$d82_subtotal=$d82_val+$d82_fie+$d82_int+$d82_mul;
			$d82_total+=$d82_subtotal;
			$d82_val_t+=$d82_val;
			$d82_fie_t+=$d82_fie;
			$d82_int_t+=$d82_int;
			$d82_mul_t+=$d82_mul;
			$d82_subtotal_t+=$d82_subtotal;
			$d82_subtotal=sprintf("%.2f",$d82_subtotal);
			$table.="<tr>
			<td class=tdheader1><input type=checkbox name=chk_$contador class=checkbox>$contador</td>
			<td class=tdheader1><a href=javascript:OpenBalance($i8_expediente_id)>$i8_expediente_id</a></td>
			<td class=tdheader1>$c16_codigo_catastral_id&nbsp;</td>
			<td class=tdheader1>$contribuyente&nbsp;</td>
			<td class=tdheader1>$str_direccion&nbsp;</td>
			<td class=tdheader1 align=right>$d82_val&nbsp;</td>
			<td class=tdheader1 align=right>$d82_fie&nbsp;</td>
			<td class=tdheader1 align=right>$d82_int&nbsp;</td>
			<td class=tdheader1 align=right>$d82_mul&nbsp;</td>
			<td class=tdheader1 align=right><a ($i8_expediente_id)>$d82_subtotal&nbsp;</a></td>
			<td class=tdheader1 align=right><a href=javascript:OpenAdministrativo($i8_expediente_id,'$clase')>$str_notificacion</a>
			</tr>";
			$contador++;
		}
	}
        $d82_val_t=sprintf("%.2f",$d82_val_t);
        $d82_fie_t=sprintf("%.2f",$d82_fie_t);
        $d82_int_t=sprintf("%.2f",$d82_int_t);
        $d82_mul_t=sprintf("%.2f",$d82_mul_t);
        $d82_subtotal_t=sprintf("%.2f",$d82_subtotal_t);
        $table.="<tr>
		<td class=tdheader1 colspan=5 align=right>TOTALES</td>
		<td class=tdheader1 align=right>\$$d82_val_t&nbsp;</td>
		<td class=tdheader1 align=right>\$$d82_fie_t&nbsp;</td>
		<td class=tdheader1 align=right>\$$d82_int_t&nbsp;</td>
		<td class=tdheader1 align=right>\$$d82_mul_t&nbsp;</td>
		<td class=tdheader1 align=right>\$$d82_subtotal_t&nbsp;</td>
		<td class=tdheader1 align=right>&nbsp;</tr>";
        $table.="</table>";
        #print $table;
}
sub get_mora {
	my($id,$clase)=@_;
	my $valor_val	= "";
	my $valor_fie	= "";
	my $valor_int	= "";
	my $valor_mul	= "";
	my $sql = "select sum(d82_val) as valor,sum(d82_fie) as fiestas,sum(d82_int) as intereses, sum(d82_mul) as multas from sdd_".$clase."_estados_mensuales where i8_".$clase."_id='$id' and i4_dias_mora>0;";
	my $sth = $dbh->prepare($sql);
	$sth->execute or die 'sub get_mora';
	while (@row = $sth->fetchrow_array) {
		$valor_val	= $row[0];
		$valor_fie	= $row[1];
		$valor_int	= $row[2];
		$valor_mul	= $row[3];
	}
	if($valor_mul>0 && $valor_mul<2.86){
	    $valor_mul=2.86;
	}
	$valor_val = sprintf("%.2f",$valor_val);
	$valor_fie = sprintf("%.2f",$valor_fie);
	$valor_int = sprintf("%.2f",$valor_int);
	$valor_mul = sprintf("%.2f",$valor_mul);    
	return ($valor_val,$valor_fie,$valor_int,$valor_mul);
}
sub get_direccion {
	my($expediente_id,$clase)=@_;
    my $sector="";
	my $calle="";
	my $avenida="";
	my $pasaje="";
	my $senda="";
	my $casa="";
	my $poligono="";
	my $block="";
	my $barrio="";
	if($clase eq "inmueble"){$tabl="sdm_inmuebles";}
	if($clase eq "empresa"){$tabl="sdd_empresa_ubicacion";}
	
	my $sql = "select c11_sector_id,c4_calle_id,c4_avenida_id,c2_pasaje_id,c2_senda_id,c4_casa,c2_block_id,c2_poligono_id,c2_barrio_id from $tabl where i8_".$clase."_id='$expediente_id'";
	my $sth = $dbh->prepare($sql);
	$sth->execute or die 'sub get_direccion';
	while (@row = $sth->fetchrow_array) {
	    #my $direccion=$row[0];
		my $sector_id	= $row[0];
		my $calle_id	= $row[1];
		my $avenida_id	= $row[2];
		my $pasaje_id	= $row[3];
		my $senda_id	= $row[4];
		my $casa		= $row[5];
		my $c2_poligono_id= $row[6];
		my $c2_block_id	= $row[7];
		my $c2_barrio_id= $row[8];
		my $sql1 = "select i2_tipo_sector_id, v64_sector from sdm_sectores where c11_sector_id='$sector_id' and c11_sector_id='$sector_id'";
		my $sth1 = $dbh1->prepare($sql1);
		$sth1->execute or die 'sub get_direccion';
		while (@row1 = $sth1->fetchrow_array) {
			$tipo_sector_id = $row1[0];
			$sector	= $row1[1];
			my $sql2 = "select v64_tipo_sector from sdm_tipo_sectores where i2_tipo_sector_id='$tipo_sector_id'";
			my $sth2 = $dbh2->prepare($sql2);
			$sth->execute or die 'sub get_direccion';
			while (@row2 = $sth->fetchrow_array) {
				$tipo_sector=$row2[0];
			}
			if($tipo_sector eq "OTROS"){
				$direccion=$sector;
			}else{
				$direccion=$tipo_sector." ".$sector;
			}
		}
        $sector=$direccion;
               
		$sql1="select v64_barrio from sdd_sector_barrios where c2_barrio_id='$c2_barrio_id' and c11_sector_id='$sector_id'";
		my $sth1 = $dbh1->prepare($sql1);
		$sth1->execute or die 'sub get_direccion';
		while (@row1 = $sth1->fetchrow_array) {$barrio=$row1[0];$direccion=$direccion.", ".$barrio;}
                
		$sql1="select v64_calle from sdd_sector_calles where c4_calle_id='$calle_id' and c11_sector_id='$sector_id'";
		my $sth1 = $dbh1->prepare($sql1);
		$sth1->execute or die 'sub get_direccion';
		while (@row1 = $sth1->fetchrow_array) {$calle=$row1[0];$direccion=$direccion.", ".$calle;}

		$sql1="select v64_avenida from sdd_sector_avenidas where c4_avenida_id='$avenida_id' and c11_sector_id='$sector_id'";
		my $sth1 = $dbh1->prepare($sql1);
		$sth1->execute or die 'sub get_direccion';
		while (@row1 = $sth1->fetchrow_array) {$avenida=$row1[0];$direccion=$direccion.", ".$avenida;}

		$sql1="select v64_pasaje from sdd_sector_pasajes where c2_pasaje_id='$pasaje_id' and c11_sector_id='$sector_id'";
		my $sth1 = $dbh1->prepare($sql1);
		$sth1->execute or die 'sub get_direccion';
		while (@row1 = $sth1->fetchrow_array) {$pasaje=$row1[0];$direccion=$direccion.", ".$pasaje;}

		$sql1="select v64_senda from sdd_sector_sendas where c2_senda_id='$senda_id' and c11_sector_id='$sector_id'";
		my $sth1 = $dbh1->prepare($sql1);
		$sth1->execute or die 'sub get_direccion';
		while (@row1 = $sth1->fetchrow_array) {$senda=$row1[0];$direccion=$direccion.", ".$senda;}
                
        $sql1="select v64_poligono from sdd_sector_poligonos where c2_poligono_id='$c2_poligono_id' and c11_sector_id='$sector_id'";		my $sth1 = $dbh1->prepare($sql1);
		$sth1->execute or die 'sub get_direccion';
		while (@row1 = $sth1->fetchrow_array) {$poligono=$row1[0];$direccion=$direccion.", ".$poligono;}
                
		$sql1="select v64_block from sdd_sector_blocks where c2_block_id='$c2_block_id' and c11_sector_id='$sector_id'";
		my $sth1 = $dbh1->prepare($sql1);
		$sth1->execute or die 'sub get_direccion';
		while (@row1 = $sth1->fetchrow_array) {$block=$row1[0];$direccion=$direccion.", ".$block;}
                
		$direccion=$direccion."".$casa;
		#print $direccion;
	}
	#print $direccion;
	return ($sector,$barrio,$calle,$avenida,$pasaje,$senda,$poligono,$block,$casa);
}
sub get_nombre{
	my ($id,$clase)= @_;
	my $r = "";
    my $sql="select a.v64_nombres,a.v64_apellidos from sdm_contribuyentes a inner join sdd_".$clase."_contribuyentes b on a.i8_contribuyente_id = b.i8_contribuyente_id where b.i2_tipo_contribuyente_id=1 and b.i8_".$clase."_id=$id";
	my $sth = $dbh->prepare($sql);
	$sth->execute or die 'sub get_nombre';
	while (@row = $sth->fetchrow_array) {
		$r = $row[0]." ".$row[1];
	}
	return $r;
}
sub construct_page {
	open (DATA, "$PATH$file");
	#print "$PATH$file";
	while ($line = <DATA>) {	
		$line =~ s/\{table}/$table/g;
		$line =~ s/\{appid}/$appid/g;
		$line =~ s/\{clase}/$clase/g;
		print "$line";
	}
}
#--------------------------------------------------------------------------
#--------------------------------------------------------------------------
# AUXILIAR
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};
}
sub SQL_fail {
    my ($query,$dbh,$section)= @_;
    print "SQL failed in Section $section<br>";
    print "Query: ".$query."<br>";    
    exit;
}
sub open_database_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";}
	$dbh->{LongReadLen} = 512 * 1024;
	$dbh->{LongTruncOk} = 1;
	$dbh1 	= DBI->connect("dbi:ODBC:$appid",'','',{PrintError => 1, RaiseError => 1});
	if (!$dbh1) {print "error: connection: $DBI::err\n$DBI::errstr\n$DBI::state\n";}
	$dbh1->{LongReadLen} = 512 * 1024;
	$dbh1->{LongTruncOk} = 1;
	$dbh2 	= DBI->connect("dbi:ODBC:$appid",'','',{PrintError => 1, RaiseError => 1});
	if (!$dbh2) {print "error: connection: $DBI::err\n$DBI::errstr\n$DBI::state\n";}
	$dbh2->{LongReadLen} = 512 * 1024;
	$dbh2->{LongTruncOk} = 1;
}