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

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');
#$org_name	= $form->param('org_name');
$ts_since	= $form->param('ts_since');
$ts_upto	= $form->param('ts_upto');
$option		= $form->param('option');
$clase		= $form->param('clase');
#------------------------------------------------------------------
#------------------------------------------------------------------
#Define subdirectories and general variables
$PATH		= "../".$lan."/";
$file 		= "100106r_consolidado_es.html";
$file1 		= "100106r_consolidado1_es.html";
$file3 		= "100106r_consolidado3_es.html";
$file4 		= "100106r_consolidado4_es.html";
$file0 		= "100106r_consolidado0_es.html"; #este es el ultimo 21-02-2007
#$file0 		= "es_reporte_ingreso_sectores";
$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 = 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 (!($db1 = 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')}
#-------------------------------------------------------------------
#-------------------------------------------------------------------
# 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>";


#if($option eq "1"){$t=2;$rowset = get_records($db_tes,'sdm_recibos a,sdd_recibos b,sdm_service_products c','b.v32_service_product_id,c.v128_name,sum(b.f8_subtotal)')}
#if($option eq "1"){$t=2;$rowset = get_records($db_tes,'sdm_recibos a,sdd_recibos b,sdm_tarifas c','b.c16_tarifa_id,c.v128_tarifa,sum(b.d82_cantidad*b.d82_tarifa)')}
#if($option eq "2"){$t=1;$rowset = get_records1()}
#if($option eq "3"){$t=3;$rowset = get_records3($db_tes,'sdm_recibos a,sdd_recibos b,sdm_tarifas c,sdm_cuentas_contables d','d.c16_cuenta_contable_id,d.v128_cuenta_contable,sum(b.d82_cantidad*b.d82_tarifa)')}
#if($option eq "4"){$t=4;$rowset = get_records4()}

$rowset=get_records0();
construct_page($rowset,$t);

exit;
#-------------------------------------------------------------------
#MAIN-SUBROUTINES
sub get_records0{
	$sql="select v64_sector from sdm_sectores where c11_sector_id='$option'";
	
	if ( $db->Sql($sql) ){SQLfail($db_cc,$sql,'sub get_records')};
	while($db->FetchRow())
	{
		undef @rw;
		@rw=$db->Data();
		$v64_sector=$rw[0];
		
	}
	#POR SECTOR
	#CUATRO COLUMNAS
	#No. INMUEBLE_ID TARIFA VALOR
	
	
	my $query = "";
	my $r	  = "";
	my $rs	  = "";
	#if(ts_upto eq ""){$query = "SELECT $fields FROM $tables WHERE a.c16_recibo_id=b.c16_recibo_id and b.c16_tarifa_id=c.c16_tarifa_id and substring(a.dt_emision+0,1,8)='$since' GROUP BY b.c16_tarifa_id ORDER BY 2"}
	#if(ts_upto ne ""){$query = "SELECT $fields FROM $tables WHERE a.c16_recibo_id=b.c16_recibo_id and b.c16_tarifa_id=c.c16_tarifa_id and substring(a.dt_emision+0,1,8)>='$since' and substring(a.dt_emision+0,1,8)<='$upto' GROUP BY b.c16_tarifa_id ORDER BY 2"}
	
	$template_rows="";
	
	
	$template_rows=$template_rows."<TR class=my_style555><TD>No</TD><TD>Tributo ID</TD><TD>Tributo</TD><TD class=my_style5555>VALOR</TD></TR>";
	#print "ejecutado";
	$contador=0;
	$temp_total=0;
	
	if($clase eq "inmueble" or $clase eq "empresa")
	{
		$table1="sdd_".$clase."_abonos";
		$table2="sdm_".$clase."s";
		if($clase eq "empresa"){$table2="sdd_".$clase."_ubicacion"}
		
		$field="i8_".$clase."_id";
		$sql = "select b.c8_tributo_id, sum(b.d82_abono) as 'total'
				from $table2 a inner join $table1 b on ( b.$field = a.$field)			
				where a.c11_sector_id = '$option' and b.dt_abono between '$ts_since' and '$ts_upto' group by b.c8_tributo_id
				";
		#print "$sql";
		if($db->Sql($sql)){print "Error"}
		while($db->FetchRow())
		{		
			$contador=$contador+1;
			undef my @row;
			@row	= $db->Data();
			#$row[2] = sprintf("%.2f",$row[2]);
			#$r	= join('|',@row);
			#$rs	.= $r."~~";
			#$row[2]=sprintf("%.2f",$row[2]);
			$temp_total=$temp_total+$row[1];
			
			#my $query3 = "SELECT v128_tarifa FROM sdm_tarifas WHERE c16_tarifa_id='$row[0]'";
			#if ( $db_tes1->Sql($query3) ){SQL_fail($db_tes1,$query3,'sub get_records1111')};
			#if($db_tes1->FetchRow()){undef my @row3;@row3 = $db_tes1->Data();$row[1]=$row3[0];}			
			$sql="select v64_tributo from sdm_tributos where c8_tributo_id='$row[0]'";			
			if ( $db1->Sql($sql) ){SQL_fail($db_cc,$sql,'sub get_records')};
			while($db1->FetchRow())
			{
				undef @dat;
				@dat=$db1->Data();
				$tarifa=$dat[0];								
			}			
			$row[1]=sprintf("%.2f",$row[1]);
			
			$template_rows=$template_rows."<TR class=my_style55><TD>$contador</TD><TD>$row[0]</TD><TD>$tarifa</TD><TD class=my_style5>$row[1]</TD></TR>";
			
			#b.c16_tarifa_id,c.v128_tarifa,sum(b.d82_cantidad*b.d82_tarifa)
		}
		
	}
	else
	{
		##vamos a sacar todos tanto empresas como inmuebles
		$table1="sdd_inmueble_abonos";
		$table2="sdm_inmuebles";
		$field="i8_inmueble_id";
		$sql = "select b.c8_tributo_id, sum(b.d82_abono) as 'total'
				from $table2 a inner join $table1 b on ( b.$field = a.$field)			
				where a.c11_sector_id = '$option' and b.dt_abono between '$ts_since' and '$ts_upto' group by b.c8_tributo_id
				";
		#print "<br>$sql";
		if ( $db->Sql($sql) ){SQL_fail($db_cc,$sql,'sub get_records')};		
		while($db->FetchRow())
		{		
			$contador=$contador+1;
			undef my @row;
			@row	= $db->Data();
			#$row[2] = sprintf("%.2f",$row[2]);
			#$r	= join('|',@row);
			#$rs	.= $r."~~";
			#$row[2]=sprintf("%.2f",$row[2]);
			$temp_total=$temp_total+$row[1];
			
			#my $query3 = "SELECT v128_tarifa FROM sdm_tarifas WHERE c16_tarifa_id='$row[0]'";
			#if ( $db_tes1->Sql($query3) ){SQL_fail($db_tes1,$query3,'sub get_records1111')};
			#if($db_tes1->FetchRow()){undef my @row3;@row3 = $db_tes1->Data();$row[1]=$row3[0];}			
			$sql="select v64_tributo from sdm_tributos where c8_tributo_id='$row[0]'";			
			if ( $db1->Sql($sql) ){SQL_fail($db_cc,$sql,'sub get_records')};
			while($db1->FetchRow())
			{
				undef @dat;
				@dat=$db1->Data();
				$tarifa=$dat[0];								
			}			
			$row[1]=sprintf("%.2f",$row[1]);
			
			$template_rows=$template_rows."<TR class=my_style55><TD>$contador</TD><TD>$row[0]</TD><TD>$tarifa</TD><TD class=my_style5>$row[1]</TD></TR>";
			
			#b.c16_tarifa_id,c.v128_tarifa,sum(b.d82_cantidad*b.d82_tarifa)
		}
		$table1="sdd_empresa_abonos";
		$table2="sdd_empresa_ubicacion";
		$field="i8_empresa_id";
		$sql = "select b.c8_tributo_id, sum(b.d82_abono) as 'total'
				from $table2 a inner join $table1 b on ( b.$field = a.$field)			
				where a.c11_sector_id = '$option' and b.dt_abono between '$ts_since' and '$ts_upto' group by b.c8_tributo_id
				";
				#print "<br>$sql";
		if ( $db->Sql($sql) ){SQL_fail($db_cc,$sql,'sub get_records')};
		while($db->FetchRow())
		{		
			$contador=$contador+1;
			undef my @row;
			@row	= $db->Data();
			#$row[2] = sprintf("%.2f",$row[2]);
			#$r	= join('|',@row);
			#$rs	.= $r."~~";
			#$row[2]=sprintf("%.2f",$row[2]);
			$temp_total=$temp_total+$row[1];
			
			#my $query3 = "SELECT v128_tarifa FROM sdm_tarifas WHERE c16_tarifa_id='$row[0]'";
			#if ( $db_tes1->Sql($query3) ){SQL_fail($db_tes1,$query3,'sub get_records1111')};
			#if($db_tes1->FetchRow()){undef my @row3;@row3 = $db_tes1->Data();$row[1]=$row3[0];}			
			$sql="select v64_tributo from sdm_tributos where c8_tributo_id='$row[0]'";			
			if ( $db1->Sql($sql) ){SQL_fail($db_cc,$sql,'sub get_records')};
			while($db1->FetchRow())
			{
				undef @dat;
				@dat=$db1->Data();
				$tarifa=$dat[0];								
			}			
			$row[1]=sprintf("%.2f",$row[1]);
			
			$template_rows=$template_rows."<TR class=my_style55><TD>$contador</TD><TD>$row[0]</TD><TD>$tarifa</TD><TD class=my_style5>$row[1]</TD></TR>";
			
			#b.c16_tarifa_id,c.v128_tarifa,sum(b.d82_cantidad*b.d82_tarifa)
		}
		
	}
	#print "$query<br>";
	#print "<BR>query: $query<BR>";
	
	
	
	$temp_total=sprintf("%.2f",$temp_total);
	$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>";
	return $rs;
}



sub construct_page {
	my ($rowset,$t)= @_;
	
	if($t==2){
		$arc=$file1;}
	else{
		if($t==1){$arc=$file;}
		else{
			if($t==3){$arc=$file3;}
			else{$arc=$file4;}
		}
	}
	$arc=$file1;#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/\##org_name/$org_name/g;
		$line =~ s/\{rowset}/$rowset/g;
		$line =~ s/\{ts_since}/$ts_since/g;
		$line =~ s/\{ts_upto}/$ts_upto/g;
		$line =~ s/\{id_sector}/$option/g;
		$line =~ s/\{v64_sector}/$v64_sector/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;
}
#--------------------------------------------------------------------