#!/perl/bin/perl.exe
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# Information Software SA
# Date In: 2014-12-07
# Updates: 2014-12-07
# Author:Rodolfo Machon
# File Name: cobales_expedientes_p.pl
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#Perl Modules
use Win32::ODBC;
use CGI;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#HTML printing activation
print "Content-type: text/html\n\n";
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#Getting parameters
$form		= new CGI;
$lan		= $form->param('lan');
$appid		= $form->param('appid');
$dt_cobal	= $form->param('dt_cobal');
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# GLOBAL VARIABLES
$PATH 	= "../".$lan."/";
$FILE 	= $lan."_cobal_expedientes_p.html";
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# Open Data Base Connections
if (!($dbh  = new Win32::ODBC($appid))){connect_fail($appid)}
#-------------------------------------------------------------------
#-------------------------------------------------------------------
# MAIN-PROGRAM
get_records();
construct_page();
exit;
#-------------------------------------------------------------------
#MAIN-SUBROUTINES
sub get_records{
	my $t_alumbrado	= 0; 
	my $t_aseo 		= 0; 
	my $t_calle 	= 0; 
	my $t_desecho 	= 0; 
	my $t_barrido  	= 0; 
	my $t_cuota  	= 0; 
	my $t_otros  	= 0; 
	my $t_total		= 0; 
	my $t_recibos	= 0;
	my $count		= 1;

	$table .= "<TABLE id=grid>";	
	$table .= "<TR>
					<TD>PERIODO</TD>
					<TD>EXPEDIENTE</TD>					
					<TD>ALUMBRADO</TD>
					<TD>ASEO</TD>
					<TD>BARRIDO</TD>
					<TD>CUOTA PEN.</TD>
					<TD>DESECHOS</TD>
					<TD>OTROS</TD>
					<TD>CALLES</TD>
					<TD>TOTAL</TD>
				</TR>";
#i8_expediente_id,i8_nic_id,i8_nis_id,c3_tipo_generador_id,c3_facturador_id,c8_tributo_id,dt_pago,i8_periodo,d82_pago,c4_lugar_id
		$query = "SELECT SUM(IF(c8_tributo_id='TSINMALU',(d82_pago)/100,0))
						,SUM(IF(c8_tributo_id='TSINMASE',(d82_pago)/100,0))
						,SUM(IF(c8_tributo_id='TSINMCAL',(d82_pago)/100,0))
						,SUM(IF(c8_tributo_id='TSINMDES',(d82_pago)/100,0))
						,SUM(IF(c8_tributo_id='TSINMBAR',(d82_pago)/100,0))
						,SUM(IF(c8_tributo_id='TSINMCUP',(d82_pago)/100,0))
						,SUM(IF(c8_tributo_id='TSINMOTR',(d82_pago)/100,0))
						,i8_expediente_id
						,i8_nic_id
						,i8_periodo
						,dt_pago
			  FROM sdr_cobales
			  WHERE dt_cobal='$dt_cobal'
			  GROUP BY i8_periodo,i8_expediente_id
			  ORDER BY i8_periodo,i8_expediente_id
			  ";
	#print "$query <br>";
	if ( $dbh->Sql($query) ){SQL_fail($dbh,$query,'sub get_records')};
	while($dbh->FetchRow())	{
		my @row		 	= $dbh->Data();
		my $alumbrado	= round($row[0],2);
		my $aseo 		= round($row[1],2);
		my $calle 		= round($row[2],2);
		my $desecho 	= round($row[3],2);
		my $barrido  	= round($row[4],2);
		my $cuota   	= round($row[5],2);
		my $otros		= round($row[6],2);
		my $expediente	= $row[7];
		my $periodo		= $row[9];
		my $dt_pago		= $row[10];
		my $total		= $alumbrado+$aseo+$calle+$desecho+$barrido+$cuota+$otros;
		
		$t_alumbrado	+= $alumbrado;
		$t_aseo 		+= $aseo;
		$t_calle 		+= $calle;
		$t_desecho 		+= $desecho;
		$t_barrido  	+= $barrido;
		$t_cuota  		+= $cuota;
		$t_otros  		+= $otros;
		$t_total		+= $total;

		$table.="<TR>
					<TD>$periodo</TD>
					<TD>$expediente</TD>
					<TD>$alumbrado</TD>
					<TD>$aseo</TD>
					<TD>$barrido</TD>
					<TD>$cuota</TD>
					<TD>$desecho</TD>
					<TD>$otros</TD>		
					<TD>$calle</TD>		
					<TD>$total</TD>
					<TD>$count</TD>					
				</TR>
	       ";
		$count++;
	}
	$t_alumbrado = round($t_alumbrado,2);
	$t_aseo 	 = round($t_aseo,2);
	$t_barrido 	 = round($t_barrido,2);
	$t_cuota 	 = round($t_cuota,2);
	$t_desecho 	 = round($t_desecho,2);
	$t_otros 	 = round($t_otros,2);
	$t_calle 	 = round($t_calle,2);
	$t_total 	 = round($t_total,2);	
	$table.="<TR><TD colspan=2>TOTALES</TD>
				<TD><input type=text class='entrada' id='alumbrado' value='$t_alumbrado'></TD>
				<TD><input type=text class='entrada' id='aseo' value='$t_aseo'></TD>
				<TD><input type=text class='entrada' id='barrido' value='$t_barrido'></TD>
				<TD><input type=text class='entrada' id='cuota' value='$t_cuota'></TD>
				<TD><input type=text class='entrada' id='desecho' value='$t_desecho'></TD>
				<TD><input type=text class='entrada' id='otro' value='$t_otros'></TD>
				<TD><input type=text class='entrada' id='calle' value='$t_calle'></TD>
				<TD><input type=text class='entrada' id='total' value='$t_total'></TD>
			
			</TR>
			";		
	$table.="</table>";
}
sub construct_page{
	open (DATA, "$PATH$FILE");
	while ($line = <DATA>){
	  $line =~ s/{lan}/$lan/g;
	  $line =~ s/{appid}/$appid/g;
	  $line =~ s/{dt_cobal}/$dt_cobal/g;
	  $line =~ s/{consolidado}/$table/g;
	  print "$line";
	}
}
#--------------------------------------------------------------------
#AUXILIAR-SUBROUTINES
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;
}
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;
}
#--------------------------------------------------------------------