#!/perl/bin/perl.exe
$|=1;
#-------------------------------------------------------------------
#-------------------------------------------------------------------
#Perl Modules
#use CGI;
#use Win32::ODBC;

use Win32::ODBC;
#use Date::Calc qw(Add_Delta_Days); 

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');
#$org_name	= $form->param('org_name');
$ts_since	= $form->param('ts_since');
$ts_upto	= $form->param('ts_upto');
$option		= $form->param('option');
$generador	= $form->param('generador');


#$ts_since="2007-07-11";

#print "<br>generador: $generador<br>";
#------------------------------------------------------------------
#------------------------------------------------------------------
#Define subdirectories and general variables
$PATH		= "../".$lan."/";

$file0 		= "100106r_consolidado0_es.html"; #este es el ultimo 21-02-2007

$since		= $ts_since;
$upto		= $ts_upto;
$since		=~ s/\-//g;
$upto		=~ s/\-//g;
$t		= 1;


#Variables para la tabla
$template_table = "<TABLE border=1 cellspacing=0 cellpadding=4 bordercolor='000000'>{body}</TABLE>";
$template_rows  = "";
$remplate_cols = "<TD>{valu}</TD>";
$template_head  = "<TD colspand=2>{tributo}</TD>";
$template_body  = "<TD>{registros}</TD><TD>{valor}</TD>";
$template_foot  = "<TD>{tregistros}</TD><TD>{tvalor}</TD>";
#-------------------------------------------------------------------
#-------------------------------------------------------------------
# Open Data Base Connections
#if (!($db_tes = new Win32::ODBC('100106'))){connection_fail('100106')}
if (!($db_tes = new Win32::ODBC($appid))){print "Error connecting to $appid <br>";print "Error: " . Win32::ODBC::Error() . "<br>";exit;}
#if (!($db_tes1= new Win32::ODBC('100106'))){connection_fail('100106')}
if (!($db_tes1 = new Win32::ODBC($appid))){print "Error connecting to $appid <br>";print "Error: " . Win32::ODBC::Error() . "<br>";exit;}
#if (!($db_cc  = new Win32::ODBC('100105'))){connection_fail('100105')}
if (!($db_cc = new Win32::ODBC($appid))){print "Error connecting to $appid <br>";print "Error: " . Win32::ODBC::Error() . "<br>";exit;}
#if (!($db_con = new Win32::ODBC('100199'))){connection_fail('100199')}
if (!($db_con = new Win32::ODBC($appid))){print "Error connecting to $appid <br>";print "Error: " . Win32::ODBC::Error() . "<br>";exit;}
#-------------------------------------------------------------------
#-------------------------------------------------------------------
# MAIN-PROGRAM
#print "<BR>la opcion es:$option<BR>";
#$template_rows="<TR class=my_style5><TD style=\"BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 60pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent\"></TD><TD colspan='2'>ALUMBRADO</TD><TD colspan='2'>ASEO</TD><TD colspan='2'>CALLES</TD><TD colspan='2'>DESECHOS</TD><TD></TD><TD></TD><TD></TD><TD></TD></TR>";


$template_rows="";

get_records5();
construct_page($rowset,$t);
exit;


#-------------------------------------------------------------------
#MAIN-SUBROUTINES

sub get_records5{
	#POR TRIBUTO
	#CUATRO COLUMNAS
	#No. CODIGO DESCRIPCION VALOR
	$dbh=$db_tes;
	$tables='';
	$fields='';
	
	my $query = "";
	my $r	  = "";
	my $rs	  = "";
	
	
	#$template_rows=$template_rows."<TR class=my_style555><TD>No</TD><TD>CODIGO</TD><TD>TRIBUTO</TD><TD class=my_style5555>VALOR</TD></TR>";
	
	
	$query = "SELECT DATEDIFF('$ts_upto','$ts_since');";
	$dias="";
	if ( $dbh->Sql($query) ){SQL_fail($dbh,$query,'sub get_records')};
	while($dbh->FetchRow()){
		undef my @row;
		@row	= $dbh->Data();
		$dias=$row[0];
	}
	
	
	$query = "SELECT  e.c8_tributo_id, e.v64_tributo,consulta1.valor from `sdm_tributos` e left join (SELECT c.`c8_tributo_id` as c8_tributo_id,sum(b.d82_cantidad*b.d82_tarifa) as valor FROM sdm_recibos a inner join sdd_recibos b on a.`c16_recibo_id`=b.`c16_recibo_id` inner join sdm_tarifas c on b.`c16_tarifa_id`=c.`c16_tarifa_id` WHERE a.dt_emision='$since' GROUP BY c.`c8_tributo_id` ORDER BY 2) as consulta1 on e.c8_tributo_id=consulta1.c8_tributo_id ORDER by e.`v64_tributo` ASC;";
	$template_rows=$template_rows."<TR class=my_style55><TD>FECHA</TD>";	
	if ( $dbh->Sql($query) ){SQL_fail($dbh,$query,'sub get_records')};
	
	$contador=0;
	$temp_total=0;
	while($dbh->FetchRow()){
		$contador=$contador+1;
		undef my @row;
		@row	= $dbh->Data();
		$template_rows=$template_rows."<TD>$row[1]</TD>";
	}	
	$template_rows=$template_rows."<TD>TOTAL FECHA</TD></TR>";


	$dia_actual=0;

	while($dia_actual<=($dias)){
		$query = "SELECT  e.c8_tributo_id, e.v64_tributo,consulta1.valor,DATE_ADD('$ts_since',INTERVAL $dia_actual DAY) as fecha from `sdm_tributos` e left join (SELECT c.`c8_tributo_id` as c8_tributo_id,sum(b.d82_cantidad*b.d82_tarifa) as valor FROM sdm_recibos a inner join sdd_recibos b on a.`c16_recibo_id`=b.`c16_recibo_id` inner join sdm_tarifas c on b.`c16_tarifa_id`=c.`c16_tarifa_id` WHERE a.dt_emision=DATE_ADD('$ts_since',INTERVAL $dia_actual DAY) GROUP BY c.`c8_tributo_id` ORDER BY 2) as consulta1 on e.c8_tributo_id=consulta1.c8_tributo_id ORDER by e.`v64_tributo` ASC;";
		#print "$query<br>";
		
		$template_rows=$template_rows."<TR class=my_style55>";	
		if ( $dbh->Sql($query) ){SQL_fail($dbh,$query,'sub get_records')};
		$contador=0;
		$temp_total=0;
		while($dbh->FetchRow()){
			
			$contador=$contador+1;
			undef my @row;
			@row	= $dbh->Data();
			
			
			if($contador==1){
				$template_rows=$template_rows."<TD>$row[3]</TD>";
			}
			
			
			
			if($row[2] eq ""){
			 $row[2]=0;
			}
			
			$row[2] = sprintf("%.2f",$row[2]);
			$temp_total=$temp_total+$row[2];
			
			$template_rows=$template_rows."<TD>$row[2]</TD>";
			
		}
	
		$template_rows=$template_rows."<TD>$temp_total</TD></TR>";
	
		$dia_actual=$dia_actual+1;
	}#while
	
	
	
	
	
	
	

	
	
	
	
	
	
	
	
	
	#$template_rows=$template_rows."<TR class=my_style5555><TD colspan=3>TOTAL</TD><TD>$temp_total</TD></TR>";
	#$template_table = "<TABLE border=1 cellspacing=0 cellpadding=4 bordercolor='000000'>$template_rows</TABLE>";
}



sub construct_page {
	my ($rowset,$t)= @_;
	
	
	$arc=$file0;#con esto las 10 lineas anteriores quedan sobrando, pero se conservan temporalmente hasta que esta modificacion quede completada
	open (DATA, "$PATH$arc");
	while ($line = <DATA>) {	
		$line =~ s/\{lan}/$lan/g;
		$line =~ s/\{appid}/$appid/g;
		$line =~ s/\{rowset}/$rowset/g;
		$line =~ s/\{ts_since}/$ts_since/g;
		$line =~ s/\{ts_upto}/$ts_upto/g;
		$line =~ s/\{template_rows}/$template_rows/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;
}
#--------------------------------------------------------------------