#!/perl/bin/perl.exe
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# WebSoft SA
# Date In: 2014-12-07
# Updates: 2014-12-07, 2016-03-31
# Author:Rodolfo Machon
# File Name: cobales_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_desde	= $form->param('dt_desde');
$dt_hasta	= $form->param('dt_hasta');
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# GLOBAL VARIABLES
$PATH 	= "../".$lan."/";
$FILE 	= $lan."_cobales_p.html";
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# Open Data Base Connections
if (!($dbh  = new Win32::ODBC($appid))){connect_fail($appid)}
#-------------------------------------------------------------------
#-------------------------------------------------------------------
# MAIN-PROGRAM
get_records();
construct_page();
$dbh->Close();
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 $tf_alumbrado= 0;
	my $tf_aseo 	= 0;
	my $tf_calle 	= 0;
	my $tf_desecho 	= 0;
	my $tf_barrido  = 0;
	my $tf_cuota  	= 0;
	my $tf_otros  	= 0;
	my $tf_total	= 0;
	

	$table .= "<TABLE id=grid>";	
	$table .= "<TR>
		<TD>FECHA</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>
		<TD>RECIBOS</TD>
		</TR>";
	if($dt_desde<2011-06-19){	
		$query = "SELECT (SUM(d82_abono_alumbrado)/100)
						,(SUM(d82_abono_aseo)/100)
						,(SUM(d82_abono_calle)/100)
						,(SUM(d82_abono_desecho)/100)
						,(SUM(d82_abono_barrido)/100)
						,(SUM(d82_total)/100)
						,count(i8_nic_id)
						,dt_cobal
			FROM sdm_cobales WHERE dt_cobal BETWEEN '$dt_desde' and '$dt_hasta' group by dt_cobal";
	}else{
		$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((d82_pago)/100)
						,count(DISTINCT(i8_nic_id))
						,dt_cobal
						,SUM(IF(c8_tributo_id='TSINMCUP',(d82_pago)/100,0))
						,SUM(IF(c8_tributo_id='TSINMOTR',(d82_pago)/100,0))				
			  FROM sdr_cobales
			  WHERE dt_cobal
			  BETWEEN '$dt_desde' and '$dt_hasta' group by dt_cobal
			  ";
	}
	if ( $dbh->Sql($query) ){SQL_fail($dbh,$query,'sub get_records')};
	while($dbh->FetchRow())	{
		my @row		 	= $dbh->Data();
		my $alumbrado	= round($row[0]/1.05,2);
		my $aseo 		= round($row[1]/1.05,2);
		my $calle 		= round($row[2]/1.05,2);
		my $desecho 	= round($row[3]/1.05,2);
		my $barrido  	= round($row[4]/1.05,2);
		my $cuota   	= round($row[8]/1.05,2);
		my $otros		= round($row[9]/1.05,2);
		
		my $f_alumbrado	= round($alumbrado*0.05,2);
		my $f_aseo 		= round($aseo*0.05,2);
		my $f_calle 	= round($calle*0.05,2);
		my $f_desecho 	= round($desecho*0.05,2);
		my $f_barrido  	= round($barrido*0.05,2);
		my $f_cuota   	= round($cuota*0.05,2);
		my $f_otros		= round($otros*0.05,2);		
		
		my $total		= $alumbrado+$aseo+$calle+$desecho+$barrido+$cuota+$otros;
		my $f_total		= $f_alumbrado+$f_aseo+$f_calle+$f_desecho+$f_barrido+$f_cuota+$f_otros;
		my $recibos		= "<a href=javascript:CobalBorrar('$row[7]')>$row[6]</a>";
		my $dt_cobal	= "<a href=javascript:CobalExpedientes('$row[7]','$dt_desde','$dt_hasta')>$row[7]</a>";
		
		$t_alumbrado	+= $alumbrado+ $f_alumbrado;
		$t_aseo 		+= $aseo+$f_aseo;
		$t_calle 		+= $calle+$f_calle;
		$t_desecho 		+= $desecho+$f_desecho;
		$t_barrido  	+= $barrido+$f_barrido;
		$t_cuota  		+= $cuota+$f_cuota;
		$t_otros  		+= $otros+$f_otros;
		$t_total		+= $total+$f_total;
		$t_recibos		+= $row[6];
		
		$tf_alumbrado	+= $f_alumbrado;
		$tf_aseo 		+= $f_aseo;
		$tf_calle 		+= $f_calle;
		$tf_desecho 	+= $f_desecho;
		$tf_barrido  	+= $f_barrido;
		$tf_cuota  		+= $f_cuota;
		$tf_otros  		+= $f_otros;
		$tf_total		+= $f_total;		

		$table.="<TR>
					<TD>$dt_cobal</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>$recibos</TD>
				</TR>
	       ";
	}
	$table.="<TR><TD>FIESTAS</TD>
				<TD><input type=text class='fiestas' name='f_alumbrado' value='$tf_alumbrado'></TD>
				<TD><input type=text class='fiestas' name='f_aseo' value='$tf_aseo'></TD>
				<TD><input type=text class='fiestas' name='f_barrido' value='$tf_barrido'></TD>
				<TD><input type=text class='fiestas' name='f_cuota' value='$tf_cuota'></TD>
				<TD><input type=text class='fiestas' name='f_desecho' value='$tf_desecho'></TD>
				<TD><input type=text class='fiestas' name='f_otro' value='$tf_otros'></TD>
				<TD><input type=text class='fiestas' name='f_calle' value='$tf_calle'></TD>
				<TD><input type=text class='fiestas' name='f_total' value='$tf_total'></TD>
				<TD></TD>				
			</TR>
			";		
	$table.="<TR><TD>TOTALES</TD>
				<TD><input type=text class='entrada' name='alumbrado' value='$t_alumbrado'></TD>
				<TD><input type=text class='entrada' name='aseo' value='$t_aseo'></TD>
				<TD><input type=text class='entrada' name='barrido' value='$t_barrido'></TD>
				<TD><input type=text class='entrada' name='cuota' value='$t_cuota'></TD>
				<TD><input type=text class='entrada' name='desecho' value='$t_desecho'></TD>
				<TD><input type=text class='entrada' name='otro' value='$t_otros'></TD>
				<TD><input type=text class='entrada' name='calle' value='$t_calle'></TD>
				<TD><input type=text class='entrada' name='total' value='$t_total'></TD>
				<TD><input type=text class='entrada' name='recibos' value='$t_recibos'></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_desde}/$dt_desde/g;
	  $line =~ s/{dt_hasta}/$dt_hasta/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;
}
#--------------------------------------------------------------------