#!/perl/bin/perl.exe
$|=1;
#-------------------------------------------------------------------
#-------------------------------------------------------------------
#Perl Modules
use Win32::ODBC;
use CGI;
#-------------------------------------------------------------------
#-------------------------------------------------------------------
#HTML printing activation
#print "Content-type: text/html\n\n";
print "Content-type: text/html\n\n";
#-------------------------------------------------------------------
#-------------------------------------------------------------------
#Getting parameters
$form		= new CGI;
$lan		= $form->param('lan');
$appid		= $form->param('appid');
$ts_since	= $form->param('ts_since');
$ts_upto	= $form->param('ts_upto');
#$option		= $form->param('option');
#------------------------------------------------------------------
#------------------------------------------------------------------
#Define subdirectories and general variables
$PATH		= "../".$lan."/";
$FILE 		= "es_recibo_cobales.html";
$since		= $ts_since;
$upto		= $ts_upto;
$since		=~ s/\-//g;
$upto		=~ s/\-//g;
#-------------------------------------------------------------------
#-------------------------------------------------------------------
# Open Data Base Connections
if (!($dbh = new Win32::ODBC($appid))){print "Error connecting to $appid <br>";print "Error: " . Win32::ODBC::Error() . "<br>";exit;}
if (!($dbh1 = new Win32::ODBC($appid))){print "Error connecting to $appid <br>";print "Error: " . Win32::ODBC::Error() . "<br>";exit;}
#-------------------------------------------------------------------
#-------------------------------------------------------------------
# MAIN-PROGRAM
get_records();
construct_page();
exit;
#-------------------------------------------------------------------
#MAIN-SUBROUTINES
sub get_records{
	$alumbrado	= sprintf("%.2f",0); 
	$aseo 		= sprintf("%.2f",0); 
	$calle 		= sprintf("%.2f",0); 
	$desecho 	= sprintf("%.2f",0); 
	$barrido  	= sprintf("%.2f",0); 
	$cuota  	= sprintf("%.2f",0); 
	$otro  		= sprintf("%.2f",0); 
	$total		= sprintf("%.2f",0); 
	$recibos	= sprintf("%.2f",0); 
	$comision	= sprintf("%.2f",0);
	$intereses	= sprintf("%.2f",0);
	if($since<20110619){
		$query 		= "SELECT i8_expediente_id  FROM sdm_cobales
					   WHERE dt_cobal BETWEEN '$since' and '$upto'
					   GROUP BY i8_expediente_id,i8_periodo
					   ";
		if ( $dbh->Sql($query) ){SQL_fail($dbh,$query,'sub get_records')};
		while($dbh->FetchRow())	{
			my @row	= $dbh->Data();
			$recibos  += 1;
		}
		$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(*)
			   FROM sdm_cobales
			   WHERE dt_cobal BETWEEN '$since' and '$upto'
			   ";
	}else{
		$query 		= "SELECT i8_expediente_id  FROM sdr_cobales
					   WHERE dt_cobal BETWEEN '$since' and '$upto'
					   GROUP BY i8_expediente_id,i8_periodo
					   ";
		if ( $dbh->Sql($query) ){SQL_fail($dbh,$query,'sub get_records')};
		while($dbh->FetchRow())	{
			my @row	= $dbh->Data();
			$recibos  += 1;
		}
		$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_expediente_id))
						,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 '$since' and '$upto'
			  ";
	}
	#print "$query";
	if ( $dbh->Sql($query) ){SQL_fail($dbh,$query,'sub get_records')};
	while($dbh->FetchRow())	{
		my @row			= $dbh->Data();
		#$alumbrado	= sprintf("%.2f",$row[0]/1.05);
		#$aseo 		= sprintf("%.2f",$row[1]/1.05);
		#$calle 	= sprintf("%.2f",$row[2]/1.05);
		#$desecho 	= sprintf("%.2f",$row[3]/1.05);
		#$barrido 	= sprintf("%.2f",$row[4]/1.05);
		#$otro 		= sprintf("%.2f",$row[8]/1.05);
		#$cuota 	= sprintf("%.2f",$row[7]/1.05);
		$alumbrado	= round(($row[0]/1.05),2);
		$aseo 		= round(($row[1]/1.05),2);
		$calle 		= round(($row[2]/1.05),2);
		$desecho 	= round(($row[3]/1.05),2);
		$barrido 	= round(($row[4]/1.05),2);
		$otro 		= round(($row[8]/1.05),2);
		$cuota 		= round(($row[7]/1.05),2);		
		
		$total 		= $row[0]+$row[1]+$row[2]+$row[3]+$row[4]+$row[8]+$row[7];
		$tributo 	= $alumbrado+$aseo+$calle+$desecho+$barrido+$otro+$cuota;
		$fiesta		= $total-$tributo;
	}
	
	$comision		= round(($recibos*(2.59/8.75)),2);
	
	$table	 = "<TABLE border=1 cellspacing=0 cellpadding=4 bordercolor='8BA0B6' width='100%'>";
	$table	.= "<TR><p align=center><H3 align=center>Remesa CAESS cobales desde $ts_since hasta $ts_upto</H3></p></TR>";
	$table	.= "<TR>
					<TD class=tdheader align='center'>&nbsp;ALUMBRADO</TD>
					<TD class=tdheader align='center'>&nbsp;ASEO</TD>
					<TD class=tdheader align='center'>&nbsp;CALLES</TD>
					<TD class=tdheader align='center'>&nbsp;BARRIDO</TD>
					<TD class=tdheader align='center'>&nbsp;DESECHOS</TD>
					<TD class=tdheader align='center'>&nbsp;OTROS</TD>
				</TR>
				<TR>
					<TD align='center'><input type=text value=$alumbrado size=5></TD>
					<TD align='center'><input type=text value=$aseo size=5></TD>
					<TD align='center'><input type=text value=$calle size=5></TD>
					<TD align='center'><input type=text value=$barrido size=5></TD>
					<TD align='center'><input type=text value=$desecho size=5></TD>
					<TD align='center'><input type=text value=$otro size=5></TD>
				</TR>
				<TR>
					<TD class=tdheader align='center'>&nbsp;CUOTA</TD>			
					<TD class=tdheader align='center'>&nbsp;FIESTA</TD>
					<TD class=tdheader align='center'>&nbsp;INTERESES</TD>
					<TD class=tdheader align='center'>&nbsp;TOTAL COBRADO</TD>
					<TD class=tdheader align='center'>&nbsp;TOTAL RECIBOS</TD>
					<TD class=tdheader align='center'>&nbsp;COMISION</TD>
				</TR>
				<TR>
					<TD align='center'><input type=text value=$cuota size=5></TD>				
					<TD align='center'><input type=text value=$fiesta size=5></TD>
					<TD align='center'><strong>$intereses</strong></TD>
					<TD align='center'><input type=text value=$total size=5></TD>
					<TD align='center'><strong>$recibos</strong></TD>
					<TD align='center'><strong>$comision</strong></TD>
				</TR>
				";
}
sub get_barrido {
	$sql="select i8_expediente_id,i8_periodo,d82_abono_calle from sdm_cobales where dt_cobal between '$ts_since' and '$ts_upto'";
	if ( $dbh->Sql($sql) ){SQL_fail($dbh,$sql,'sub get_records')};
	while($dbh->FetchRow())	{
		undef my @row;
		@row	= $dbh->Data();
		$i8_inmueble_id=$row[0];
		$i8_periodo=$row[1];
		if($i8_periodo==200703 or $i8_periodo==200704 or $i8_periodo==200705 or $i8_periodo==200706 or $i8_periodo==200707)
		{}
		else
		{
			$d82_abono_calle=$row[2]/100;
			$d82_abono_calle=sprintf("%.2f",$d82_abono_calle);
			$sql="select d82_val+d82_fie from sdd_inmueble_estados_mensuales where i8_inmueble_id=$i8_inmueble_id and substring(c16_tarifa_id,1,8)='TSINMCAL' and i8_periodo='$i8_periodo'";
			#print "$sql";
			if($dbh1->Sql($sql)){SQL_fail($dbh1,$sql,'get barrido')}
			$d82_calle=0;
			while($dbh1->FetchRow()){
				undef @row1;
				@row1=$dbh1->Data();
				$d82_calle=$row1[0];
			}
			#print "--$d82_abono_calle-$d82_calle<br>";
			if($d82_abono_calle==$d82_calle or $d82_calle==0){
				##no pasa nada 			
			}else{
				$sql="select d82_val+d82_fie from sdd_inmueble_estados_mensuales where i8_inmueble_id=$i8_inmueble_id and substring(c16_tarifa_id,1,8)='TSINMBAR' and i8_periodo='$i8_periodo'";
				if($dbh1->Sql($sql)){SQL_fail($dbh1,$sql,'get barrido')}
				while($dbh1->FetchRow()){
					undef @row1;
					@row1=$dbh1->Data();
					$total_barrido+=$row1[0];
				}
				
			}
		}
		
	}
	return $total_barrido;
		
}
sub construct_page{
	open (DATA, "$PATH$FILE");
	while ($line = <DATA>){
		$line =~ s/{lan}/$lan/g;
		$line =~ s/{appid}/$appid/g;
		$line =~ s/{dt_desde}/$ts_since/g;
		$line =~ s/{dt_hasta}/$ts_upto/g;
		$line =~ s/{table}/$table/g;
		$line =~ s/{alumbrado}/$alumbrado/g;
		$line =~ s/{aseo}/$aseo/g;
		$line =~ s/{calle}/$calle/g;
		$line =~ s/{barrido}/$barrido/g;
		$line =~ s/{desecho}/$desecho/g;
		$line =~ s/{otro}/$otro/g;
		$line =~ s/{cuota}/$cuota/g;		
		$line =~ s/{fiesta}/$fiesta/g;
		$line =~ s/{interes}/$intereses/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;
}
#--------------------------------------------------------------------