#!/perl/bin/perl.exe
$|=1;
#--------------------------------------------------------------------------------
#--------------------------------------------------------------------------------
# Information Software SA
# Date In: 2010-10-06
# Updates: 2010-10-06
# Author:Rodolfo Machon
# File Name: ingresos_consolidados_rept_p.pl
#--------------------------------------------------------------------------------
#--------------------------------------------------------------------------------
#Perl Modules
use CGI;
use Win32::ODBC;
#--------------------------------------------------------------------------------
#--------------------------------------------------------------------------------
#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');
$option		= $form->param('option');
$i2_cajero_id	= $form->param('i2_cajero_id');
#--------------------------------------------------------------------------------
#--------------------------------------------------------------------------------
#Define subdirectories and general variables
$PATH		= "../".$lan."/";
$file 		= $lan."_ingresos_consolidados_rept_p.html";
$template_rows  = "";
#--------------------------------------------------------------------------------
#--------------------------------------------------------------------------------
# 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
if($i2_cajero_id==0){$cajero = ""}else {$cajero = "and a.i2_cajero_id=$i2_cajero_id"}
if($option eq "1"){$rowset = get_ingresos_recibos()}
if($option eq "2"){$rowset = get_ingresos_departamentos()}
if($option eq "3"){$rowset = get_ingresos_cuentas_contables()}
if($option eq "4"){$rowset = get_ingresos_tributos()}
if($option eq "5"){$rowset = get_ingresos_tarifas()}
construct_page($rowset);
exit;
#-------------------------------------------------------------------
#MAIN-SUBROUTINES
sub get_ingresos_recibos {
	my $r	    	= "";
	my $rs	    	= "";
	$contador   	= 0;
	$temp_total 	= 0;
	$template_rows = "<TR class=my_style555><TD>No</TD><TD>RECIBO</TD><TD>FECHA</TD><TD>EMISOR</TD><TD>CLIENTE</TD><TD class=my_style5555>VALOR</TD></TR>";
	my $query 	= "SELECT
				a.c16_recibo_id,
				a.dt_emision,
				b.v64_tipo_departamento,
				a.i8_contribuyente_id,
				a.v128_contribuyente
			   FROM sdm_recibos a
			        inner join sdm_tipo_departamentos b on a.i2_tipo_departamento_id=b.i2_tipo_departamento_id
			   WHERE a.dt_emision >= '$dt_desde' and
			        a.dt_emision <= '$dt_hasta' $cajero
			   ORDER BY 1";
	if ( $dbh->Sql($query) ){SQL_fail($dbh,$query,'get_ingresos_recibos')};
	while($dbh->FetchRow()){
		my $suma = "0.00";
		$contador= $contador+1;
		undef my @row;
		@row = $dbh->Data();
		my $query1 = "SELECT v64_tipo_departamento FROM sdm_tipo_departamentos WHERE i2_tipo_departamento_id='$row[2]'";
		if ( $dbh1->Sql($query1) ){SQL_fail($dbh1,$query1,'sub get_records1')};
		if($dbh1->FetchRow()){undef my @row1;@row1 = $dbh1->Data();$row[2]=$row1[0]}
		my $query3 = "SELECT sum(d82_cantidad*d82_tarifa) FROM sdd_recibos WHERE c16_recibo_id='$row[0]' GROUP BY c16_recibo_id";
		if ( $dbh1->Sql($query3) ){SQL_fail($dbh1,$query3,'sub get_records1')};
		if($dbh1->FetchRow()){undef my @row3;@row3 = $dbh1->Data();$suma=sprintf("%.2f",$row3[0]);}
		$r	    = join('|',@row);
		$rs	   .= $r."|".$suma."~~";
		$temp_total = $temp_total+$suma;
		$template_rows=$template_rows."<TR class=my_style55><TD>$contador</TD><TD>$row[0]</TD><TD>$row[1]</TD><TD>$row[2]</TD><TD>$row[4]</TD><TD class=my_style5>$suma</TD></TR>";
	}
	$temp_total    = sprintf("%.2f",$temp_total);
	$template_rows = $template_rows."<TR class=my_style5555><TD colspan=5>TOTAL</TD><TD>$temp_total</TD></TR>";
	return $rs;
}
sub get_ingresos_departamentos {
	my $r	  	= "";
	my $rs	  	= "";
	$template_rows	= "<TR class=my_style555><TD>No</TD><TD>CODIGO</TD><TD>DEPARTAMENTO</TD><TD class=my_style5555>VALOR</TD></TR>";
	$contador	= 0;
	my $query 	= "SELECT b.i2_tipo_departamento_id,
				b.`v64_tipo_departamento`,
				sum(c.d82_cantidad * c.d82_tarifa)
			   FROM sdm_tributos e
				inner join sdd_recibos c on e.c8_tributo_id = substring(c.c16_tarifa_id,1,8)
				inner join sdm_recibos a on c.c16_recibo_id = a.c16_recibo_id
				inner join sdm_tipo_departamentos b on a.i2_tipo_departamento_id=b.i2_tipo_departamento_id
			   WHERE a.dt_emision >= '$dt_desde' and
				a.dt_emision <= '$dt_hasta' $cajero
			   GROUP BY b.i2_tipo_departamento_id
			   ORDER BY 1";
	if ( $dbh->Sql($query) ){SQL_fail($dbh,$query,'sub get_records1')};
	while($dbh->FetchRow()){
		undef my @row;
		@row 	  = $dbh->Data();
		my $suma  = "0.00";
		$contador = $contador+1;
		$suma	  = sprintf("%.2f",$row[2]);;
		$r	  = $row[0]."|".$row[1];
		$rs	 .= $r."|".$suma."~~";
		$row[2]	  = sprintf("%.2f",$row[2]);
		$temp_total=$temp_total+$row[2];
		$template_rows=$template_rows."<TR class=my_style55><TD>$contador</TD><TD>$row[0]</TD><TD>$row[1]</TD><TD class=my_style5>$row[2]</TD></TR>";
		
	}
	$template_rows=$template_rows."<TR class=my_style5555><TD colspan=3>TOTAL</TD><TD>$temp_total</TD></TR>";
	return $rs;
}
sub get_ingresos_cuentas_contables{
	my $r	    = "";
	my $rs	    = "";
	$contador   = 0;
	$temp_total = 0;
	$template_rows = "<TR class=my_style555><TD>No</TD><TD>CODIGO</TD><TD>CUENTA CONTABLE</TD><TD class=my_style5555>VALOR</TD></TR>";
	my $query    = "SELECT d.c16_cuenta_contable_id,d.v128_cuenta_contable,sum(b.d82_cantidad*b.d82_tarifa)
			FROM  sdm_recibos a,sdd_recibos b,sdm_tarifas c,sdm_cuentas_contables d,sdm_tributos e
			WHERE a.c16_recibo_id = b.c16_recibo_id and
				b.c16_tarifa_id = c.c16_tarifa_id and
				substring(b.c16_tarifa_id, 1, 8) = e.c8_tributo_id and
				c.c16_cuenta_recurso_id = d.c16_cuenta_contable_id and
				a.dt_emision >= '$dt_desde' and
				a.dt_emision <= '$dt_hasta' $cajero
			GROUP BY d.c16_cuenta_contable_id
			ORDER BY 2";
	if ( $dbh->Sql($query) ){SQL_fail($dbh,$query,'sub get_records')};
	while($dbh->FetchRow()){
		undef my @row;
		$contador = $contador+1;
		@row	  = $dbh->Data();
		$row[2]   = sprintf("%.2f",$row[2]);
		$r	  = join('|',@row);
		$rs	 .= $r."~~";
		$row[2]=sprintf("%.2f",$row[2]);
		$temp_total=$temp_total+$row[2];
		$template_rows=$template_rows."<TR class=my_style55><TD>$contador</TD><TD>$row[0]</TD><TD>$row[1]</TD><TD class=my_style5>$row[2]</TD></TR>";
	}
	$template_rows=$template_rows."<TR class=my_style5555><TD colspan=3>TOTAL</TD><TD>$temp_total</TD></TR>";
	return $rs;
}
sub get_ingresos_tributos {
	$contador   = 0;
	$temp_total = 0;
	my $r	    = "";
	my $rs	    = "";
	my $query   = "SELECT b.c16_tarifa_id,
			e.c8_tributo_id,
			e.v64_tributo,
			sum(b.d82_cantidad * b.d82_tarifa)
		FROM sdm_recibos a,
			sdd_recibos b,
			sdm_tarifas c,
			sdm_tributos e
		WHERE a.c16_recibo_id = b.c16_recibo_id and
			b.c16_tarifa_id = c.c16_tarifa_id and
			substring(c.c16_tarifa_id, 1, 8) = e.c8_tributo_id and
			a.dt_emision >= '$dt_desde' and
			a.dt_emision <= '$dt_hasta' $cajero
		GROUP BY e.c8_tributo_id
		ORDER BY 2";
	if ( $dbh->Sql($query) ){SQL_fail($dbh,$query,'sub get_records')};
	while($dbh->FetchRow()){
		$contador=$contador+1;
		undef my @row;
		@row	= $dbh->Data();
		$row[3] = sprintf("%.2f",$row[3]);
		$r	= join('|',@row);
		$rs	.= $r."~~";
		$row[3]=sprintf("%.2f",$row[3]);
		$temp_total=$temp_total+$row[3];
		$template_rows=$template_rows."<TR class=my_style55><TD>$contador</TD><TD>$row[1]</TD><TD>$row[2]</TD><TD class=my_style5>$row[3]</TD></TR>";
	}
	$template_rows=$template_rows."<TR class=my_style5555><TD colspan=3>TOTAL</TD><TD>$temp_total</TD></TR>";
	return $rs;
}
sub get_ingresos_tarifas {
	my $r	  = "";
	my $rs	  = "";
	$contador=0;
	$temp_total=0;
	$template_rows="";
	$template_rows=$template_rows."<TR class=my_style555><TD>No</TD><TD>CODIGO</TD><TD>TARIFA</TD><TD class=my_style5555>VALOR</TD></TR>";
	my $query = "SELECT b.c16_tarifa_id,
				c.v128_tarifa,
				sum(b.d82_cantidad * b.d82_tarifa)
			FROM sdm_recibos a,
				sdd_recibos b,
				sdm_tarifas c
			WHERE a.c16_recibo_id = b.c16_recibo_id and
				b.c16_tarifa_id = c.c16_tarifa_id and
				a.dt_emision >= '$dt_desde' and
				a.dt_emision <= '$dt_hasta' $cajero
			GROUP BY b.c16_tarifa_id
			ORDER BY 2";
	if($dbh->Sql($query)){SQL_fail($dbh,$query,'sub get_records')};
	while($dbh->FetchRow()){
		$contador=$contador+1;
		undef my @row;
		@row	= $dbh->Data();
		$row[2] = sprintf("%.2f",$row[2]);
		$r	= join('|',@row);
		$rs	.= $r."~~";
		$row[2]=sprintf("%.2f",$row[2]);
		$temp_total=$temp_total+$row[2];
		$template_rows=$template_rows."<TR class=my_style55><TD>$contador</TD><TD>$row[0]</TD><TD>$row[1]</TD><TD class=my_style5>$row[2]</TD></TR>";
	}
	$template_rows=$template_rows."<TR class=my_style5555><TD colspan=3>TOTAL</TD><TD>$temp_total</TD></TR>";
	return $rs;
}
sub construct_page {
	my ($rowset)= @_;
	open (DATA, "$PATH$file");
	while ($line = <DATA>) {	
		$line =~ s/\{lan}/$lan/g;
		$line =~ s/\{appid}/$appid/g;
		$line =~ s/\{rowset}/$rowset/g;
		$line =~ s/\{dt_desde}/$dt_desde/g;
		$line =~ s/\{dt_hasta}/$dt_hasta/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;
}
#--------------------------------------------------------------------