#!/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');
#------------------------------------------------------------------
#------------------------------------------------------------------
#Define subdirectories and general variables
$since		= $ts_since;
$upto		= $ts_upto;
$since		=~ s/\-//g;
$upto		=~ s/\-//g;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# GLBAL VARIABLES
$PATH 		= "../".$lan."/";
$FILE       	= $lan."_consolidado_cobales_reporte.html";
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# Open Data Base Connections
#if (!($db_tes = new Win32::ODBC('100106'))){connection_fail('100106')}
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;}
if (!($dbh2 = new Win32::ODBC($appid))){print "Error connecting to $appid <br>";print "Error: " . Win32::ODBC::Error() . "<br>";exit;}

#-------------------------------------------------------------------
#-------------------------------------------------------------------
# MAIN-PROGRAM
$user  = $ENV{"REMOTE_USER"};
get_records();
construct_page();
$dbh->Close();
$dbh1->Close();
$dbh2->Close();
exit;
#-------------------------------------------------------------------
#MAIN-SUBROUTINES
sub get_records{
	my $link1 		= "$ts_since";
	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 $t_comision	= 0; 

	if($user eq "admin" || $user eq "Administrador_RTM" || $user eq "awpineda"){$link1 = "<a href=javascript:AbonosCheck('$ts_since')>$ts_since</a>"}	
	$table .= "<p align='center'>ALCALDIA MUNICIPAL DE QUEZALTEPEQUE";
	$table .= "<br>INFORME DE INGRESOS COBRADOS POR CAESS";
	$table .= "<br>DEL $link1 al $ts_upto</p>";
	$table .= "<TABLE border=1 cellspacing=0 cellpadding=4 bordercolor='8BA0B6' width='100%'>";	
	$table .= "<TR>
		<TD class=tdheader align='center'>&nbsp;FECHA</TD>
		<TD class=tdheader align='center'>&nbsp;ALUMB</TD>
		<TD class=tdheader align='center'>&nbsp;ASEO</TD>
		<TD class=tdheader align='center'>&nbsp;CALLES</TD>
		<TD class=tdheader align='center'>&nbsp;BARR</TD>
		<TD class=tdheader align='center'>&nbsp;DESE</TD>
		<TD class=tdheader align='center'>&nbsp;OTROS</TD>
		<TD class=tdheader align='center'>&nbsp;CUOTA</TD>		
		<TD class=tdheader align='center'>&nbsp;FIESTA</TD>
		
		<TD class=tdheader align='center'>&nbsp;TOTAL</TD>
		<TD class=tdheader align='center'>&nbsp;RECIBOS</TD>
		<TD class=tdheader align='center'>&nbsp;COMISION</TD>
		</TR>";
	if($since<20110619){	
		$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(*)
						,dt_cobal
			FROM sdm_cobales WHERE dt_cobal BETWEEN '$since' and '$upto' 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_expediente_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 '$since' and '$upto' group by dt_cobal
			  ";
	}
	if ( $dbh->Sql($query) ){SQL_fail($dbh,$query,'sub get_records')};
	$contador	= 0;
	$temp_total	= 0;
	while($dbh->FetchRow())	{
		my @row		 	= $dbh->Data();
		my $intereses	= sprintf("%.2f",0);
		my $alumbrado	= sprintf("%.2f",$row[0]/1.05);
		my $aseo 		= sprintf("%.2f",$row[1]/1.05);
		my $calle 		= sprintf("%.2f",$row[2]/1.05);
		my $desecho 	= sprintf("%.2f",$row[3]/1.05);
		my $barrido  	= sprintf("%.2f",$row[4]/1.05);
		my $cuota   	= sprintf("%.2f",$row[8]/1.05);
		my $otros		= sprintf("%.2f",$row[9]/1.05);		
		my $recibos		= $row[6];
		my $link 		= "$row[7]";
		
		my $total 		= sprintf("%.2f",$row[0]+$row[1]+$row[2]+$row[3]+$row[4]+$row[8]+$row[9]);
		my $fiesta		= sprintf("%.2f",($total/1.05)*0.05);		
		my $comision	= sprintf("%.2f",($recibos*(2.59/8.75)));
		
		$t_alumbrado	+= $alumbrado;
		$t_aseo 		+= $aseo;
		$t_calle 		+= $calle;
		$t_desecho 		+= $desecho;
		$t_barrido  	+= $barrido;
		$t_cuota  		+= $cuota;
		$t_otros  		+= $otros;
		$t_fiesta  		+= $fiesta;		
		$t_total		+= $total;
		$t_recibos		+= $recibos;
		$t_comision		+= $comision;

		if($user eq "admin" || $user eq "Administrador_RTM" || $user eq "awpineda"){$link  = "<a href=javascript:CobalBorrar('$row[7]','$ts_since','$ts_upto')>$row[7]</a>"}

		$table.="<TR>
					<TD align='right'>$link</TD>
					<TD align='right'>$alumbrado</TD>
					<TD align='right'>$aseo</TD>
					<TD align='right'>$calle</TD>
					<TD align='right'>$barrido</TD>
					<TD align='right'>$desecho</TD>
					<TD align='right'>$otros</TD>		
					<TD align='right'>$cuota</TD>		
					<TD align='right'>$fiesta</TD>
					<TD align='right'>$total</TD>
					<TD align='right'>$recibos</TD>
					<TD align='right'>$comision</TD>
				</TR>
	       ";
	}
	$t_alumbrado= sprintf("%.2f",$t_alumbrado);
	$t_aseo 	= sprintf("%.2f",$t_aseo);
	$t_calle 	= sprintf("%.2f",$t_calle);
	$t_barrido  = sprintf("%.2f",$t_barrido);
	$t_desecho 	= sprintf("%.2f",$t_desecho);
	$t_cuota  	= sprintf("%.2f",$t_cuota);
	$t_otros  	= sprintf("%.2f",$t_otros);
	$t_fiesta 	= sprintf("%.2f",$t_fiesta);
	$t_total	= sprintf("%.2f",$t_total);
	$t_recibos	= sprintf("%.2f",$t_recibos);
	$t_comision = sprintf("%.2f",$t_comision);
	
	$table.="<TR><TD align='center'>TOTALES</TD>
				<TD align='right'><strong>$t_alumbrado</strong></TD>
				<TD align='right'><strong>$t_aseo</strong></TD>
				<TD align='right'><strong>$t_calle</strong></TD>
				<TD align='right'><strong>$t_barrido</strong></TD>
				<TD align='right'><strong>$t_desecho</strong></TD>
				<TD align='right'><strong>$t_otros</strong></TD>
				<TD align='right'><strong>$t_cuota</strong></TD>
				<TD align='right'><strong>$t_fiesta</strong></TD>				
				<TD align='right'><strong>$t_total</strong></TD>
				<TD align='right'><strong>$t_recibos</strong></TD>				
				<TD align='right'><strong>$t_comision</strong></TD>
			</TR>
			";		
	$table.="</table>";
}
sub get_barrido{
	my($dt_cobal)=@_;
	$sql="select i8_expediente_id,i8_periodo,d82_abono_calle from sdm_cobales where dt_cobal='$dt_cobal'";
	#print "<br>$sql";
	if ( $dbh1->Sql($sql) ){SQL_fail($dbh,$sql,'sub get_records')};
	$contador=0;
	$temp_total=0;
	while($dbh1->FetchRow()){
		undef my @row;
		@row	= $dbh1->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 "<br>$sql";
			if($dbh2->Sql($sql)){SQL_fail($dbh1,$sql,'get barrido')}
			$d82_calle=0;
			while($dbh2->FetchRow())
			{
				undef @row1;
				@row1=$dbh2->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'";
				#print "<br>$sql";
				if($dbh2->Sql($sql)){SQL_fail($dbh1,$sql,'get barrido')}
				while($dbh2->FetchRow())
				{
					undef @row1;
					@row1=$dbh2->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/{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;
}
#--------------------------------------------------------------------