#!/perl/bin/perl.exe
$|=1;
#--------------------------------------------------------------------------------
#--------------------------------------------------------------------------------
# Information Software SA
# Date In: 2013-01-17
# Updates: 2013-01-17
# Author:Rodolfo Machon
# File Name: financiamientos_rept.pl
#------------------------------------------------------------------------- 
#-------------------------------------------------------------------------
# PERL MODULES
use CGI;
use Win32::ODBC;
#------------------------------------------------------------------------- 
#-------------------------------------------------------------------------
# HTML PRINTING ACTIVATION
print "Content-type: text/html\n\n";
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#GET PARAMETERS
$cgiparms = new CGI;
$lan	  = $cgiparms->param('lan');
$appid	  = $cgiparms->param('appid');
$clase	  = $cgiparms->param('clase');
$id	      = $cgiparms->param('id');
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# CHECK PARAMETERS
if ($lan eq ""){print "Your are not passing LAN parameter<br>";}
if ($appid eq ""){print "Your are not passing APPID parameter<br>";}
if ($clase eq ""){print "Your are not passing CLASE parameter<br>";}
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# GLBAL VARIABLES
$PATH 	= "../".$lan."/";
$file   = "es_financiamientos_rept.html";          
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# GET CLASE SCHEMA AND VARIABLES
if (!($dbh  = new Win32::ODBC($appid))){connect_fail($appid)}
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# PROGRAM DESCRIPTION
# PROCUREMENT
($v64_nombres,$v64_apellidos) = get_contribuyente();
%financiamientos     	      = get_financiamientos($id);
# INTEGRATION
# PRESENTATION
if(keys %financiamientos){$table = construct_table($id)}
construct_page();
exit;
#-------------------------------------------------------------------
#-------------------------------------------------------------------
# MAIN-SUBROUTINES
sub get_contribuyente {
    my  $nom ="";
    my  $ape =""; 
    my	$query = "SELECT b.v64_nombres,b.v64_apellidos
		  FROM sdd_".$clase."_contribuyentes a
		  LEFT JOIN sdm_contribuyentes b ON a.i8_contribuyente_id=b.i8_contribuyente_id
		  WHERE a.i8_".$clase."_id='$id' and a.i2_tipo_contribuyente_id
		  ";
    if ( $dbh->Sql($query) ){SQL_fail($query,$dbh,'sub get_expedientes')};
    while($dbh->FetchRow()){
	my @row = $dbh->Data();
	$nom = $row[0];
	$ape = $row[1];
    }
    return $nom,$ape;
}
sub get_financiamientos {
	my $cnt = 0;	
	my($id) = @_;
	my %thd = ();
	my %noti= ();
	my $sql = "SELECT
			a.i8_".$clase."_id,
			a.i8_financiamiento_id,
			a.c8_estado,
			a.i6_desde,
			a.i6_hasta,
			a.i2_cuotas,
			a.i4_periodos_mora,
			a.d82_tributos,
			a.d82_fiesta,
			a.d82_interes,
			a.d82_multa,
			a.d82_mora,
			a.d82_tributos_corriente,
			a.d82_fiesta_corriente,
			a.d82_cargo_corriente,
			a.c2_fiesta,
			a.c2_interes,
			a.c2_multa,
			a.c2_icc
		   FROM sdd_".$clase."_financiamientos a 
		   WHERE a.i8_".$clase."_id='$id'
		   ORDER BY a.i8_financiamiento_id";
	if ( $dbh->Sql($sql) ){print "Error $sql"};
	#print "$sql";
	while($dbh->FetchRow()) {
		undef my @row;
		@row	       = $dbh->Data();
		$noti{$cnt} = [@row];
		$cnt++;
	}
	return %noti;
}
sub construct_table {
    my $count   = 1;
    my $table   = "";
    my %tar_cue = ();    
    my %thead_h = ();
    my %tfoot_h = ();    
    my $thead_s = "";
    my $tfoot_s = "";    
    my $rowset  = "";
    my $thead   = "<thead>
			<tr>
				<td colspan=6>FINANCIAMIENTO</td>
				<td colspan=6>COBRO MORA</td>
				<td colspan=3>COBRO CORRIENTE</td>
				<td colspan=4>INCLUIR EN CUOTA</td>
			</tr>
			<tr>
				<td>No.</td>
				<td>ID</td>
				<td>ESTADO</td>
				<td>DESDE</td>
				<td>HASTA</td>
				<td>CUOTAS</td>
				<td>PERIODOS</td>
				<td>TRIBUTOS</td>
				<td>FIESTAS</td>
				<td>INTERESES</td>
				<td>MULTAS</td>
				<td>TOTAL</td>
				<td>TRIBUTOS</td>
				<td>FIESTAS</td>
				<td>TOTAL</td>
				<td>FIESTAS</td>
				<td>INTERESES</td>
				<td>MULTAS</td>
				<td>CORRIENTE</td>				
			</tr>
		   </thead>";
    foreach my $k (sort keys %financiamientos){
	my $row   = "<td><a href=\"javascript:OpenContratoText('$id','$clase','$financiamientos{$k}[1]')\">".$count."</a></td>
		     <td><a href=\"javascript:OpenContratoUpload('$id','$clase','$financiamientos{$k}[1]')\">".$financiamientos{$k}[1]."</a></td>
		     <td><a href=\"javascript:OpenContratoImagen('$id','$clase','$financiamientos{$k}[1]')\">".$financiamientos{$k}[2]."</a></td>
		     <td><a href=\"javascript:OpenConvenioText('$id','$clase','$financiamientos{$k}[1]')\">".$financiamientos{$k}[3]."</a></td>
		     <td><a href=\"javascript:OpenConvenioUpload('$id','$clase','$financiamientos{$k}[1]')\">".$financiamientos{$k}[4]."</a></td>
		     <td><a href=\"javascript:OpenConvenioImagen('$id','$clase','$financiamientos{$k}[1]')\">".$financiamientos{$k}[5]."</a></td>
		     <td>".$financiamientos{$k}[6]."</td>
		     <td>".$financiamientos{$k}[7]."</td>
		     <td>".$financiamientos{$k}[8]."</td>
		     <td>".$financiamientos{$k}[9]."</td>
		     <td>".$financiamientos{$k}[10]."</td>
		     <td>".$financiamientos{$k}[11]."</td>
		     <td>".$financiamientos{$k}[12]."</td>
		     <td>".$financiamientos{$k}[13]."</td>
		     <td>".$financiamientos{$k}[14]."</td>
		     <td>".$financiamientos{$k}[15]."</td>
		     <td>".$financiamientos{$k}[16]."</td>
		     <td>".$financiamientos{$k}[17]."</td>
		     <td>".$financiamientos{$k}[18]."</td>
		     ";
	$rowset    .= "<tr>".$row."</tr>";
	$count++;	
    }
    $rowset    = "<tbody>".$rowset."</tbody>";
    $table     = "<table id=grid>".$thead.$rowset."</table>";
    $table     = convertir_tildes($table);
    return $table;
}
sub construct_page {
	open (DATA, "$PATH$file");
	while ($line = <DATA>) {
                $line =~ s/\{lan}/$lan/g;
                $line =~ s/\{appid}/$appid/g;
                $line =~ s/\{clase}/$clase/g;
                $line =~ s/\{id}/$id/g;
                $line =~ s/\{v64_nombres}/$v64_nombres/g;
                $line =~ s/\{v64_apellidos}/$v64_apellidos/g;
		$line =~ s/\{table}/$table/g;
		print "$line";
	}
}
#AUXILIAR-SUBROUTINES
sub numeros {$a<=>$b}
sub SQL_fail {
    my ($query,$dbhh,$section)= @_;
    print "SQL failed in Section $section<br>";
    print "Error: ".$dbhh->Error()."\n<br>";
    print "Query: ".$query."<br>";    
    $dbhh->Close();
    exit;
}
sub connect_fail {
    my ($DSN)=@_;
    print "Error connecting to $DSN<br>";
    print "Error:".Win32::ODBC::Error()."<br>";
    exit;
}
sub convertir_tildes{
      my ($str) = @_;
      $str =~ s/\Ã±/ñ/g;
      $str =~ s/\Ã/Ñ/g;
      $str =~ s/\Ã¡/á/g;
      $str =~ s/\Ã©/é/g;
      $str =~ s/\Ã­/í/g;
      $str =~ s/\Ã³/ó/g;
      $str =~ s/\Ãº/ú/g;
      return $str;
}
#-------------------------------------------------------------------------
#($uid,$uf,$ul) = get_user();
#sub get_user {
#	my $uid   = "";
#	my $uf   = "";
#	my $ul   = "";	
#        my $user  = $ENV{"REMOTE_USER"};
#	my $query = "SELECT a.i8_user_id,a.`v32_fname`,a.`v32_lname` FROM sdm_users a where a.`v16_login`='$user'";
#	if ( $dbh->Sql($query) ){SQL_fail($query,$dbh,'sub get_user')};
#	while($dbh->FetchRow()){
#		my @row = $dbh->Data();
#		$uid = $row[0];
#		$uf  = $row[1];
#		$ul  = $row[2];
#	}
#	return $uid,$uf,$ul;
#}