#!/perl/bin/perl.exe
$|=1;
#--------------------------------------------------------------------------------
#--------------------------------------------------------------------------------
# Information Software SA
# Date In: 2012-10-01
# Updates: 2012-10-01
# Author:Rodolfo Machon
# File Name: ingresos_exportar_csv_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."/";
$PATH1		= "C:../".$lan."/downloads/archivos_csv/";
$dir		= $appid."/".$lan."/downloads/archivos_csv";
$file 		= $lan."_archivo_csv_generado.html";
$template_rows  = "";
#--------------------------------------------------------------------------------
#--------------------------------------------------------------------------------
# Open Data Base Connections
if (!($dbh  = new Win32::ODBC($appid))){connect_fail($appid)}
#--------------------------------------------------------------------------------
#--------------------------------------------------------------------------------
# MAIN-PROGRAM
$desde   = $dt_desde;
$desde   =~ s/-//g;
$hasta   = $dt_hasta;
$hasta 	 =~ s/-//g;
$archivo = "RECIBOS_".$desde."_".$hasta.".csv";

erase_archivo();

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);
$dbh->Close();
exit;
#-------------------------------------------------------------------
#MAIN-SUBROUTINES
sub erase_archivo {
	my ($log,$pas)=@_;
	my $dir  = "";
	my $line = "C:\\Program Files (x86)\\Apache Group\\Apache\\htdocs\\SIM_v20\\es\\downloads\\archivos_csv\\$archivo";
	#print "$line";
	unlink($line);
}
sub get_ingresos_recibos {
	my $r	  = "";
	my $rs	  = "";
	my $count = 1;
	my $total = 0;
	my $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,
			    sum(c.d82_cantidad*c.d82_tarifa)
		     FROM sdm_recibos a
		     LEFT JOIN sdm_tipo_departamentos b on a.i2_tipo_departamento_id = b.i2_tipo_departamento_id
		     LEFT JOIN sdd_recibos c on a.c16_recibo_id = c.c16_recibo_id
		     WHERE a.dt_emision BETWEEN '$dt_desde' and '$dt_hasta' $cajero
		     GROUP BY a.c16_recibo_id
		     ORDER BY a.c16_recibo_id+0
		     INTO OUTFILE '$PATH1$archivo'
		     FIELDS TERMINATED BY ','
		     OPTIONALLY ENCLOSED BY '\"'
		     ESCAPED BY '\"'
		     LINES TERMINATED BY '\\n'		     
		     ";
	#print "$query<br>";
	if ( $dbh->Sql($query) ){SQL_fail($dbh,$query,'get_ingresos_recibos')};
	#while($dbh->FetchRow()){
	#	undef my @row;
	#	@row 	 = $dbh->Data();
	#}
}
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/\{dir}/$dir/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;
}
#--------------------------------------------------------------------