#!/perl/bin/perl.exe
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# Information Software SA
# Date In: 2014-12-07
# Updates: 2014-12-07
# Author:Rodolfo Machon
# File Name: cobales_mandamiento_p.pl
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#Perl Modules
use Win32::ODBC;
use CGI::Carp qw(fatalsToBrowser);
use CGI;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#HTML printing activation
print "Content-type: text/html\n\n";
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#Get parameters
$form	 	= new CGI;
$lan	 	= $form->param('lan');
$appid	 	= $form->param('appid');
$alumbrado	= $form->param('alumbrado');
$aseo		= $form->param('aseo');
$calle		= $form->param('calle');
$barrido	= $form->param('barrido');
$desecho	= $form->param('desecho');
$otro		= $form->param('otro');
$cuota		= $form->param('cuota');
$total		= $form->param('total');
$dt_emision	= $form->param('dt_emision');
$dt_desde	= $form->param('dt_desde');
$dt_hasta	= $form->param('dt_hasta');
#-------------------------------------------------------------------------
#print "ALU:$alumbrado<br>ASE:$aseo<br>CAL:$calle<br>BAR:$barrido<br>DES:$desecho<br>OTR:$otro<>CUO:$cuota<>TOT:$total<br>";
#-------------------------------------------------------------------------
#Define subdirectories and general variables
$PATH 				= "../".$lan."/";
$file_html			= "es_cobales_mandamiento_p.html";
$i8_mandamiento_id	= 0;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# Open Data Base Connections
if (!($dbh  = new Win32::ODBC($appid))){connect_fail($appid)}
if (!($dbh1 = new Win32::ODBC($appid))){connect_fail($appid)}
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#Main Program
$user	= get_remote_user();
$uid	= get_user_name();

crear_recibo();
construct_page();
exit;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# SUB-ROUTINES
sub get_remote_user {
        local($username) = $ENV{"REMOTE_USER"};
        local($identify) = $ENV{"REMOTE_IDENT"};
        local($user) = $username || $identify;
        return($user);
}
sub get_user_name {
    my ($str);
	my $sql	= " SELECT a.i8_user_id
					  ,a.v32_lname
					  ,a.v32_fname
				FROM sdm_users a
				WHERE a.v16_login='$user'
			  ";
	if ( $dbh->Sql($sql) ) {SQLfail($dbh,$sql,'get_user_name')}
	while($dbh->FetchRow()){
		my @row	= $dbh->Data();
		$str  = $row[0];
	}
    return $str;
}
sub crear_recibo {
	my $v128_contribuyente		= "VARIOS CONTRIBUYENTES";
	my $i8_contribuyente_id		= 99999999;
	my $i2_tipo_pago_id			= 1;
	my $i2_tipo_departamento_id	= 2;
	my $c3_tipo_generador_id	= "INM";
	my $c2_tipo_mandamiento_id	= "RE";
	my $txt_concepto			= "REMESA CAESS DEL $dt_desde HASTA $dt_hasta";
	my $c4_status_mandamiento_id= "OPEN";
	my $i2_cajero				= 1;
	my $tasas					= 0;	
	my $sql = "INSERT INTO sdm_mandamientos
					   (i8_contribuyente_id,i2_tipo_pago_id,i2_tipo_departamento_id,c3_tipo_generador_id,c2_tipo_mandamiento_id,v128_contribuyente,dt_emision,dt_vencimiento,txt_concepto,c4_status_mandamiento_id,i2_cajero_id,i8_user_id,i8_emisor_id,ts_in,ts_update)
				VALUES ($i8_contribuyente_id,$i2_tipo_pago_id,$i2_tipo_departamento_id,'$c3_tipo_generador_id','$c2_tipo_mandamiento_id','$v128_contribuyente','$dt_emision','$dt_emision','$txt_concepto','$c4_status_mandamiento_id',$i2_cajero,'$uid','$uid',now(),now())
			   ";
	if ( $dbh->Sql($sql) ) {SQL_fail($dbh,$sql,'clear_recibo')}
	#print "$sql <br>";
	my $sql1 = "SELECT LAST_INSERT_ID()";
	if ( $dbh->Sql($sql1) ) {SQLfail($dbh,$sql1,'clear_recibo')}
	while($dbh->FetchRow()){
		my @row	= $dbh->Data();
		$i8_mandamiento_id=$row[0];
	}
	#print "$i8_mandamiento_id <br>";
	if($otro>0){
		my $c16_tarifa_id 	= "";
		my $v128_tarifa		= "OTROS";
		my $otr 			= round(($otro/1.05),2);
		my $sql = "SELECT c16_tarifa_id,v128_tarifa FROM sdm_tarifas WHERE c8_tributo_id='TSINMOTR'  order by dt_desde desc limit 1";
		if ( $dbh->Sql($sql) ) {SQLfail($dbh,$sql,'clean_inm_tables')}
		while($dbh->FetchRow()){
			my @row				= $dbh->Data();
			$c16_tarifa_id 	= $row[0];
		}
		$sql="insert into sdd_mandamientos (i8_mandamiento_id,c16_tarifa_id,v128_tarifa,d82_cantidad,d82_tarifa)
		values($i8_mandamiento_id,'$c16_tarifa_id','$v128_tarifa',1,$otr)";
		if ( $dbh->Sql($sql) ) {SQLfail($dbh,$sql,'clean_inm_tables')}
		$tasas +=$otr;
		#print "$sql <br>";
	}
	if($cuota>0){
		my $c16_tarifa_id 	= "";
		my $v128_tarifa		= "CUOTA PENDIENTE";
		my $cuo 			= round(($cuota/1.05),2);		
		my $sql = "SELECT c16_tarifa_id,v128_tarifa FROM sdm_tarifas WHERE c8_tributo_id='TSINMCUP'  order by dt_desde desc limit 1";
		if ( $dbh->Sql($sql) ) {SQLfail($dbh,$sql,'clean_inm_tables')}
		while($dbh->FetchRow()){
			my @row			= $dbh->Data();
			$c16_tarifa_id 	= $row[0];
		}
		$sql="insert into sdd_mandamientos (i8_mandamiento_id,c16_tarifa_id,v128_tarifa,d82_cantidad,d82_tarifa)
		values($i8_mandamiento_id,'$c16_tarifa_id','$v128_tarifa',1,$cuo)";
		if ( $dbh->Sql($sql) ) {SQLfail($dbh,$sql,'clean_inm_tables')}
		$tasas +=$cuo;
		#print "$sql <br>";		
	}
	if($alumbrado>0){
		my $c16_tarifa_id 	= "";
		my $v128_tarifa		= "ALUMBRADO";
		my $alu 			= round(($alumbrado/1.05),2);
		my $sql = "SELECT c16_tarifa_id,v128_tarifa FROM sdm_tarifas WHERE c8_tributo_id='TSINMALU' ORDER BY dt_desde desc LIMIT 1";
		if ( $dbh->Sql($sql) ) {SQLfail($dbh,$sql,'clean_inm_tables')}
		while($dbh->FetchRow())	{
			my @row			= $dbh->Data();
			$c16_tarifa_id 	= $row[0];
		}
		$sql = "insert into sdd_mandamientos (i8_mandamiento_id,c16_tarifa_id,v128_tarifa,d82_cantidad,d82_tarifa)
		values($i8_mandamiento_id,'$c16_tarifa_id','$v128_tarifa',1,$alu)";
		if ( $dbh->Sql($sql) ) {SQLfail($dbh,$sql,'clean_inm_tables')}
		$tasas +=$alu;
		#print "$sql <br>";
	}
	if($aseo>0){
		my $c16_tarifa_id 	= "";
		my $v128_tarifa		= "ASEO";
		my $ase 			= round(($aseo/1.05),2);
		my $sql = "SELECT c16_tarifa_id,v128_tarifa FROM sdm_tarifas WHERE c8_tributo_id='TSINMASE' order by dt_desde desc limit 1";
		if ( $dbh->Sql($sql) ) {SQLfail($dbh,$sql,'clean_inm_tables')}
		while($dbh->FetchRow()){
			my @row			= $dbh->Data();
			$c16_tarifa_id 	= $row[0];
		}
		$sql = "insert into sdd_mandamientos (i8_mandamiento_id,c16_tarifa_id,v128_tarifa,d82_cantidad,d82_tarifa)
		values($i8_mandamiento_id,'$c16_tarifa_id','$v128_tarifa',1,$ase)";
		if ( $dbh->Sql($sql) ) {SQLfail($dbh,$sql,'clean_inm_tables')}
		$tasas +=$ase;
		#print "$sql <br>";
	}
	if($calle>0){
		my $c16_tarifa_id 	= "";
		my $v128_tarifa		= "PAVIMENTACION";
		my $cal 			= round(($calle/1.05),2);
		my $sql = "SELECT c16_tarifa_id,v128_tarifa FROM sdm_tarifas WHERE c8_tributo_id='TSINMCAL' order by dt_desde desc limit 1";
		if ( $dbh->Sql($sql) ) {SQLfail($dbh,$sql,'clean_inm_tables')}
		while($dbh->FetchRow()){
			my @row			= $dbh->Data();
			$c16_tarifa_id 	= $row[0];
		}
		$sql = "insert into sdd_mandamientos (i8_mandamiento_id,c16_tarifa_id,v128_tarifa,d82_cantidad,d82_tarifa)
		values($i8_mandamiento_id,'$c16_tarifa_id','$v128_tarifa',1,$cal)";
		if ( $dbh->Sql($sql) ) {SQLfail($dbh,$sql,'clean_inm_tables')}
		$tasas +=$cal;
		#print "$sql <br>";
	}
	if($barrido>0){
		my $c16_tarifa_id 	= "";
		my $v128_tarifa		= "BARRIDO";
		my $bar 			= round(($barrido/1.05),2);		
		my $sql = "SELECT c16_tarifa_id,v128_tarifa FROM sdm_tarifas WHERE c8_tributo_id='TSINMBAR' order by dt_desde desc limit 1";
		if ( $dbh->Sql($sql) ) {SQLfail($dbh,$sql,'clean_inm_tables')}
		while($dbh->FetchRow()){
			my @row			= $dbh->Data();
			$c16_tarifa_id 	= $row[0];
		}
		$sql="insert into sdd_mandamientos (i8_mandamiento_id,c16_tarifa_id,v128_tarifa,d82_cantidad,d82_tarifa)
		values($i8_mandamiento_id,'$c16_tarifa_id','$v128_tarifa',1,$bar)";
		if ( $dbh->Sql($sql) ) {SQLfail($dbh,$sql,'clean_inm_tables')}
		$tasas +=$bar;
		#print "$sql <br>";
	}
	if($desecho>0){
		my $c16_tarifa_id 	= "";
		my $v128_tarifa		= "DESECHOS SOLIDOS";
		my $des 			= round(($desecho/1.05),2);		
		my $sql = "SELECT c16_tarifa_id,v128_tarifa FROM sdm_tarifas WHERE c8_tributo_id='TSINMDES'  order by dt_desde desc limit 1";
		if ( $dbh->Sql($sql) ) {SQLfail($dbh,$sql,'clean_inm_tables')}
		while($dbh->FetchRow()){
			my @row			= $dbh->Data();
			$c16_tarifa_id 	= $row[0];
		}
		$sql="insert into sdd_mandamientos (i8_mandamiento_id,c16_tarifa_id,v128_tarifa,d82_cantidad,d82_tarifa)
		values($i8_mandamiento_id,'$c16_tarifa_id','$v128_tarifa',1,$des)";
		if ( $dbh->Sql($sql) ) {SQLfail($dbh,$sql,'clean_inm_tables')}
		$tasas +=$des;
		#print "$sql <br>";
	}
	if($total>0 && $tasas>0){
		my $c16_tarifa_id 	= "";
		my $v128_tarifa		= "FIESTAS";
		my $fiesta			=  round(($total-$tasas),2);
		my $sql = "SELECT c16_tarifa_id,v128_tarifa FROM sdm_tarifas WHERE c8_tributo_id='ACINMFIE' order by dt_desde desc limit 1";
		if ( $dbh->Sql($sql) ) {SQLfail($dbh,$sql,'clean_inm_tables')}
		while($dbh->FetchRow()){
			my @row			= $dbh->Data();
			$c16_tarifa_id 	= $row[0];
		}
		$sql="insert into sdd_mandamientos (i8_mandamiento_id,c16_tarifa_id,v128_tarifa,d82_cantidad,d82_tarifa)
		values($i8_mandamiento_id,'$c16_tarifa_id','$v128_tarifa',1,$fiesta)";
		if ( $dbh->Sql($sql) ) {SQLfail($dbh,$sql,'clean_inm_tables')}
		#print "$sql <br>";		
	}	
}
sub construct_page {
	open (DATA, "$PATH$file_html");
		while ($line = <DATA>){
			$line =~ s/{lan}/$lan/g;
			$line =~ s/{appid}/$appid/g;
			$line =~ s/{i8_mandamiento_id}/$i8_mandamiento_id/g;
			
			print $line;
		}		
	close (DATA);
}
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#AUXILIAR SUB-ROUTINES
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;
}
sub round {
  my ($n, $places) = @_;
  my $abs = abs $n;
  my $val = substr($abs + ('0.' . '0' x $places . '5'),0,length(int($abs)) +(($places > 0) ? $places + 1 : 0));
  ($n < 0) ? "-" . $val : $val;
}
#--------------------------------------------------------------------