#!/perl/bin/perl.exe
$|=1;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# Author:Rodolfo Machon
# Company:WebSoft, SA de CV 
# Nombre del archivo: mora_sectores_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;
$clase		= $form->param('clase');
#--------------------------------------------------------------------------
#--------------------------------------------------------------------------
# OPEN DATABASE CONNECTIONS - ODBCs
get_app_data();
open_database_connections();
#--------------------------------------------------------------------------
#--------------------------------------------------------------------------
# GLOBAL VARIABLES
$PATH		= "../".$lan."/";
$file   	= "es_mora_sectores_rept.html";
$titulo_clase   = $clase."S";
$titulo_clase   =~ tr/a-z/A-Z/;
#--------------------------------------------------------------------------
#--------------------------------------------------------------------------
# MAIN-PROGRAM
%sectores	 = get_sectores();
%sectores_mora	 = get_sectores_mora();
$table		 = construct_table();
construct_page();

exit;
#---------------------------------------------------------------
#---------------------------------------------------------------
#funcion para obtener expedientes
sub get_sectores {
	my %secs = ();
	my $sql  = "select a.c11_sector_id,b.v64_sector,count(a.i8_inmueble_id)
			from sdm_inmuebles a
			   left join sdm_sectores b
			   on a.c11_sector_id=b.c11_sector_id
			where a.c16_estado='ACEPTADO'
			group by a.c11_sector_id
		    ";
	if($clase eq "empresa"){
	   $sql  = "select a.c11_sector_id,b.v64_sector,count(a.i8_empresa_id)
			from sdd_empresa_ubicacion a
			   left join sdm_sectores b on a.c11_sector_id=b.c11_sector_id
			   left join sdm_empresas c on a.i8_empresa_id=c.i8_empresa_id
			where c.c16_estado='ACEPTADO'
			group by a.c11_sector_id
		   ";
	}
  my $sth = $dbh->prepare($sql);
  $sth->execute or die 'sub get_sectores';
  while (@row = $sth->fetchrow_array) {
		$secs{$row[0]} = [@row]; 
	}
	return %secs;
}
sub get_sectores_mora {
	my %secs= ();	
	my $sql	= "select a.c11_sector_id,
			       count(DISTINCT(a.i8_inmueble_id)),
			       SUM(b.d82_val),
			       SUM(b.d82_fie),
			       SUM(b.d82_int),
			       SUM(b.d82_mul),
						 b.dt_generado
			from sdm_inmuebles a
			     left join sdd_inmueble_estados_mensuales b on a.i8_inmueble_id = b.i8_inmueble_id
			where b.i4_dias_mora>0 and b.d82_val>0  and a.c16_estado='ACEPTADO'
			group by a.c11_sector_id
			order by a.c11_sector_id
		   ";
		#print $sql;
	if($clase eq "empresa"){
	   $sql	= "select a.c11_sector_id,
			       count(DISTINCT(a.i8_empresa_id)),
			       SUM(b.d82_val),
			       SUM(b.d82_fie),
			       SUM(b.d82_int),
			       SUM(b.d82_mul),
						 b.dt_generado
			from sdd_empresa_ubicacion a
			     left join sdd_empresa_estados_mensuales b on a.i8_empresa_id = b.i8_empresa_id
			     left join sdm_empresas c on a.i8_empresa_id=c.i8_empresa_id
			where b.i4_dias_mora>0 and b.d82_val>0  and c.c16_estado='ACEPTADO'
			group by a.c11_sector_id
			order by a.c11_sector_id
		   ";
	}
  my $sth = $dbh->prepare($sql);
  $sth->execute or die 'sub get_sectores_mora';
  while (@row = $sth->fetchrow_array) {
		$secs{$row[0]}    = [@row];
		$secs{$row[0]}[7] = sprintf("%.2f",$row[2]+$row[3]+$row[4]+$row[5]);
	}
	return %secs;
}
sub construct_table {
	my $rows  = "";
	my $count = 1;
	my $table = "<table id=tabla>
	 <thead>
		      <tr>
			  <td>No.</td>
			  <TD>TIPO SECTOR</TD>
			  <td>DESCRIPCION</td>
				<TD>GENERADO</TD>
			  <td>TOTAL</td>
			  <td>EN MORA</td>
			  <td>VALOR</td>
			  <td>FIESTA</td>
			  <td>INTERES</td>
			  <td>MULTA</td>
			  <td>MORA</td>
			  <td class=tdheader2>( % )</td>
		       </tr>
		       {rows}
		     </table>
		   ";
	my $row  = "<tr>
			<td class=my_style2>{count}</td>
			<TD class=my_style2>{sector}</TD>
			<td class=my_style2><a ('{sector}')>{sector_label}</a></td>
			<td class=my_style2 align=right>{generado}</td>
			<td class=my_style2 align=right>{exp}</td>
			<td class=my_style2 align=right><a href=javascript:OpenMoraSectorExpedientes('{sector}')>{exp_mora}</a></td>
			<td class=my_style2 align=right>{val}</td>
			<td class=my_style2 align=right>{fie}</td>
			<td class=my_style2 align=right>{int}</td>
			<td class=my_style2 align=right>{mul}</td>
			<td class=my_style2 align=right><a ('{sector}','2')>{monto}</a></td>
			<td class=my_style2 align=right><a ('{sector}','1')>{exp_mora_por}</a></td>
		     </tr>
		    ";
	my $footrow = "<tr>
			<td class=my_style2>&nbsp;</td>
			<TD class=my_style2>&nbsp;</TD>
				<TD class=my_style2>&nbsp;</TD>
			<td class=my_style2 align=right>TOTALES</td>
			<td class=my_style2 align=right>{total_exp}</td>
			<td class=my_style2 align=right>{total_exp_mora}</td>
			<td class=my_style2 align=right>{total_val}</td>
			<td class=my_style2 align=right>{total_fie}</td>
			<td class=my_style2 align=right>{total_int}</td>
			<td class=my_style2 align=right>{total_mul}</td>
			<td class=my_style2 align=right>{total_mora}</td>
			<td class=my_style2 align=right>{total_emp}</td>
		     </tr>
		    ";
	foreach my $k (sort keys %sectores){
		if($sectores{$k}[2] eq "" or $sectores{$k}[2] eq "null"){$sectores{$k}[2]=0.00}
		if($sectores_mora{$k}[1] eq "" or $sectores_mora{$k}[1] eq "null"){$sectores_mora{$k}[1]=0.00}
		if($sectores_mora{$k}[2] eq "" or $sectores_mora{$k}[2] eq "null"){$sectores_mora{$k}[2]=0.00}else{$sectores_mora{$k}[2]=sprintf("%.2f",$sectores_mora{$k}[2])}
		if($sectores_mora{$k}[3] eq "" or $sectores_mora{$k}[3] eq "null"){$sectores_mora{$k}[3]=0.00}else{$sectores_mora{$k}[3]=sprintf("%.2f",$sectores_mora{$k}[3])}
		if($sectores_mora{$k}[4] eq "" or $sectores_mora{$k}[4] eq "null"){$sectores_mora{$k}[4]=0.00}else{$sectores_mora{$k}[4]=sprintf("%.2f",$sectores_mora{$k}[4])}
		if($sectores_mora{$k}[5] eq "" or $sectores_mora{$k}[5] eq "null"){$sectores_mora{$k}[5]=0.00}else{$sectores_mora{$k}[5]=sprintf("%.2f",$sectores_mora{$k}[5])}
		if($sectores_mora{$k}[7] eq "" or $sectores_mora{$k}[7] eq "null"){$sectores_mora{$k}[7]=0.00}else{$sectores_mora{$k}[7]=sprintf("%.2f",$sectores_mora{$k}[7])}

		$emp  		 = sprintf("%.2f",($sectores_mora{$k}[1]/$sectores{$k}[2])*100);
		$total_exp 	+= $sectores{$k}[2];
		$total_exp_mora += $sectores_mora{$k}[1];
		$total_val	+= $sectores_mora{$k}[2];
		$total_fie	+= $sectores_mora{$k}[3];
		$total_int	+= $sectores_mora{$k}[4];		
		$total_mul	+= $sectores_mora{$k}[5];
		$total_mora	+= $sectores_mora{$k}[7];
		$val		 = big_money($sectores_mora{$k}[2]);
                $fie             = big_money($sectores_mora{$k}[3]);
                $int             = big_money($sectores_mora{$k}[4]);
                $mul             = big_money($sectores_mora{$k}[5]);
                $monto           = big_money($sectores_mora{$k}[7]);
		$line = $row;
		$line =~ s/\{count}/$count/g;
		$line =~ s/\{sector}/$k/g;
		$line =~ s/\{sector_label}/$sectores{$k}[1]/g;
		$line =~ s/\{generado}/$sectores_mora{$k}[6]/g;
		$line =~ s/\{exp}/$sectores{$k}[2]/g;
		$line =~ s/\{exp_mora}/$sectores_mora{$k}[1]/g;
		$line =~ s/\{val}/$val/g;	 
		$line =~ s/\{fie}/$fie/g;	 
		$line =~ s/\{int}/$int/g;	 
		$line =~ s/\{mul}/$mul/g;	 
		$line =~ s/\{monto}/$monto/g;
		$line =~ s/\{exp_mora_por}/$emp/g;
		$rows .= $line;
		$count++;
	}
	$total_val  = big_money($total_val);
	$total_fie  = big_money($total_fie);
	$total_int  = big_money($total_int);		
	$total_mul  = big_money($total_mul);
	$total_mora = big_money($total_mora);
	$total_emp  = sprintf("%.2f",($total_exp_mora/$total_exp)*100);
	$rows .= $footrow;
	$rows =~ s/\{total_exp}/$total_exp/g;
	$rows =~ s/\{total_exp_mora}/$total_exp_mora/g;
	$rows =~ s/\{total_val}/$total_val/g;
	$rows =~ s/\{total_fie}/$total_fie/g;
	$rows =~ s/\{total_int}/$total_int/g;
	$rows =~ s/\{total_mul}/$total_mul/g;
	$rows =~ s/\{total_mora}/$total_mora/g;
	$rows =~ s/\{total_emp}/$total_emp/g;
	$table=~ s/\{rows}/$rows/g;
	return $table;
}
sub construct_page {
	open (DATA, "$PATH$file");
	while ($line = <DATA>) {	
                $line =~ s/\{lan}/$lan/g;
                $line =~ s/\{appid}/$appid/g;
                $line =~ s/\{clase}/$clase/g;
                $line =~ s/\{titulo_clase}/$titulo_clase/g;
		$line =~ s/\{table}/$table/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;
}
sub big_money {
  my $number = sprintf "%.2f", shift @_;
  # Add one comma each time through the do-nothing loop
  1 while $number =~ s/^(-?\d+)(\d\d\d)/$1,$2/;
  # Put the dollar sign in the right place
  $number =~ s/^(-?)/$1\$/;
  $number;
}

