#!/perl/bin/perl.exe
$|=1;
#--------------------------------------------------------------------------------
#--------------------------------------------------------------------------------
# Information Software SA
# Date In: 2012-12-12
# Updates: 2012-12-12
# Author:Rodolfo Machon
# File Name: convenio_pagos_rept.pl
#------------------------------------------------------------------------- 
#-------------------------------------------------------------------------
# PERL MODULES
use CGI;
use Win32::ODBC;
#------------------------------------------------------------------------- 
#-------------------------------------------------------------------------
# HTML PRINTING ACTIVATION
print "Content-type: text/html; charset=utf-8\n\n";
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#GET PARAMETERS
$cgiparms = new CGI;
$lan	  = $cgiparms->param('lan');
$appid	  = $cgiparms->param('appid');
$clase	  = $cgiparms->param('clase');
$id	  	  = $cgiparms->param('id');
$fid	  = $cgiparms->param('fid');
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# 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>";}
if ($id eq ""){print "Your are not passing ID parameter<br>";}
if ($fid eq ""){print "Your are not passing FID parameter<br>";}
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# GLBAL VARIABLES
$PATH 	= "../".$lan."/";
$file   = "es_convenio_pago_rept.html";          
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# GET CLASE SCHEMA AND VARIABLES
if (!($dbh  = new Win32::ODBC($appid))){connect_fail($appid)}
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# PROGRAM DESCRIPTION
# PROCUREMENT
($ano,$mes,$dia) = get_fecha_actual();
($v64_nombres,$v64_apellidos,$c11_dui_id,$v64_municipio,$v64_departamento) = get_contribuyente();
$ubicacion 			    = get_ubicacion();
%financiamientos_show 	      	    = show_financiamientos();
%financiamientos_detalle_show 	    = show_financiamientos_detalle();
%expediente_financiamientos   	    = get_expediente_financiamientos();
%expediente_financiamientos_detalle = get_expediente_financiamientos_detalle();
# INTEGRATION
$plan_pago = construct_table();
# PRESENTATION
construct_page();
exit;
#-------------------------------------------------------------------
#-------------------------------------------------------------------
# MAIN-SUBROUTINES
sub get_contribuyente {
    my $nom   = "";
    my $ape   = "";
    my $dui   = "";
    my $mun   = "";
    my $dep   = "";
    my $query = "SELECT a.v64_nombres,a.v64_apellidos,a.c11_dui_id,a.v64_municipio,a.v64_departamento
		 FROM sdm_contribuyentes a
                 INNER JOIN sdd_".$clase."_contribuyentes b on a.i8_contribuyente_id = b.i8_contribuyente_id
		 WHERE b.i2_tipo_contribuyente_id=1 and b.i8_".$clase."_id=$id";
    if ( $dbh->Sql($query) ){SQL_fail($query,$dbh,'sub get_contribuyente')};
    while($dbh->FetchRow()){
	my @row    = $dbh->Data();
	$nom = $row[0];
	$ape = $row[1];
	$dui = $row[2];
	$mun = $row[3];
	$dep = $row[4];
    }
    return $nom,$ape,$dui,$mun,$dep;
}
sub get_ubicacion {
    my $dir   = "";
    my $tabla = "";
    if($clase eq "inmueble"){$tabla = "sdm_inmuebles"}
    if($clase eq "empresa"){$tabla = "sdd_empresa_ubicacion"}
    my $query = "SELECT a.i8_".$clase."_id,
			b.v64_barrio,
			c.v64_poligono,
			d.v64_block,
			e.v64_calle,
			f.v64_avenida,
			g.v64_pasaje,
			h.v64_senda,
			a.c4_casa,
			i.v64_sector,
			j.v64_tipo_sector
		 FROM ".$tabla." a
		 LEFT JOIN sdd_sector_barrios b ON a.c11_sector_id=b.c11_sector_id and a.c2_barrio_id=b.c2_barrio_id
		 LEFT JOIN sdd_sector_poligonos c ON a.c11_sector_id=c.c11_sector_id and a.c2_poligono_id=c.c2_poligono_id
		 LEFT JOIN sdd_sector_blocks d ON a.c11_sector_id=d.c11_sector_id and a.c2_block_id=d.c2_block_id
		 LEFT JOIN sdd_sector_calles e ON a.c11_sector_id=e.c11_sector_id and a.c4_calle_id=e.c4_calle_id
		 LEFT JOIN sdd_sector_avenidas f ON a.c11_sector_id=f.c11_sector_id and a.c4_avenida_id=f.c4_avenida_id
		 LEFT JOIN sdd_sector_pasajes g ON  a.c11_sector_id=g.c11_sector_id and a.c2_pasaje_id=g.c2_pasaje_id
		 LEFT JOIN sdd_sector_sendas h ON  a.c11_sector_id=h.c11_sector_id and a.c2_senda_id=h.c2_senda_id
		 LEFT JOIN sdm_sectores i ON a.c11_sector_id=i.c11_sector_id
		 LEFT JOIN sdm_tipo_sectores j ON a.i2_tipo_sector_id=j.i2_tipo_sector_id		 
		 WHERE a.i8_".$clase."_id='$id'
		 ORDER BY a.i8_".$clase."_id";
    if ( $dbh->Sql($query) ){SQL_fail($query,$dbh,'sub get_ubicacion')};
    while($dbh->FetchRow()){
	my @row   = $dbh->Data();
	if($row[1] ne ""){$dir .= "Barrio ".$row[1].","}
	if($row[2] ne ""){$dir .= "Poligono ".$row[2].","}
	if($row[3] ne ""){$dir .= "Block ".$row[3].","}
	if($row[4] ne ""){$dir .= "Calle ".$row[4].","}
	if($row[5] ne ""){$dir .= "Avenidad ".$row[5].","}
	if($row[6] ne ""){$dir .= "Pasaje ".$row[6].","}
	if($row[7] ne ""){$dir .= "Senda ".$row[7].","}
	if($row[8] ne ""){$dir .= "# ".$row[8].","}	
	$dir = $row[10]." ".$row[9].", ".$dir;
	$dir = substr($dir,0,length($dir)-1);	
    }
    return $dir;
}
sub show_financiamientos {
    my $count = 0;
    my %hash  = ();
    my $query = "SHOW COLUMNS FROM `sdd_".$clase."_financiamientos`";
    if ( $dbh->Sql($query) ){SQL_fail($query,$dbh,'sub show_financiamientos')};
    while($dbh->FetchRow()){
	my @row = $dbh->Data();
	$hash{$count} = [@row];
	$count++;
    }
    return %hash;
}
sub show_financiamientos_detalle {
    my $count = 0;
    my %hash  = ();
    my $query = "SHOW COLUMNS FROM `sdd_".$clase."_financiamientos_detalle`";
    if ( $dbh->Sql($query) ){SQL_fail($query,$dbh,'sub show_financiamientos_detalle')};
    while($dbh->FetchRow()){
	my @row = $dbh->Data();
	$hash{$count} = [@row];
	$count++;
    }
    return %hash;
}
sub get_expediente_financiamientos {
    my $str   = "";
    my %hash  = ();
    foreach my $k (sort keys %financiamientos_show){$str .= $financiamientos_show{$k}[0].","}
    $str = substr($str,0,length($str)-1);
    my @fields = split(/\,/,$str);
    my $query  = "SELECT ".$str."
		  FROM sdd_".$clase."_financiamientos
		  WHERE i8_".$clase."_id='$id' and i8_financiamiento_id='$fid' and c8_estado='ACTIVO'
		 ";
    if ( $dbh->Sql($query) ){SQL_fail($query,$dbh,'sub get_expediente_financiamientos')};
    while($dbh->FetchRow()){
	my @row    = $dbh->Data();
	for(my $i=0;$i<$#row;$i++){$hash{$fields[$i]} = $row[$i];}
    }
    return %hash;
}
sub get_expediente_financiamientos_detalle {
    my $str   = "";
    my $count = 1;
    my %hash  = ();
    foreach my $k (sort keys %financiamientos_detalle_show){$str .= $financiamientos_detalle_show{$k}[0].","}
    $str = substr($str,0,length($str)-1);
    my $fin_id = $expediente_financiamientos{'i8_financiamiento_id'};
    my @fields = split(/\,/,$str);
    my $query  = "SELECT $str
		  FROM sdd_".$clase."_financiamientos_detalle
		  WHERE i8_".$clase."_id='$id' and i8_financiamiento_id='$fin_id'
		 ";
    if ( $dbh->Sql($query) ){SQL_fail($query,$dbh,'sub get_expediente_financiamientos_detalle')};
    while($dbh->FetchRow()){
	my @row    = $dbh->Data();
	for(my $i=0;$i<$#fields;$i++){
		$hash{$count}{$fields[$i]} = $row[$i];
		#print "$count>$fields[$i]>".$hash{$count}{$fields[$i]}."<br>";
	}
	$count++;
    }
    return %hash;
}
sub construct_table {
    my $table   = "";
    my %tar_cue = ();    
    my %thead_h = ();
    my %tfoot_h = ();    
    my $thead_s = "";
    my $tfoot_s = "";    
    my $rowset  = "";
    my $thead   = "<thead><tr><td>CUOTA</td><td>PERIODOS</td><td>DESDE</td><td>HASTA</td><td>TRIBUTOS</td><td>INTERES</td><td>MULTA</td><td>CORRIENTE</td><td>CUOTA</td><td>VENCIMIENTO</td></tr></thead>";
    my $tfoot   = "<tfoot><tr><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>TOTAL</td><td>{t_tributos}</td><td>{t_interes}</td><td>{t_multa}</td><td>{t_corriente}</td><td>{t_cuota}</td><td>&nbsp;</td></tr></tfoot>";    
    foreach my $k (sort numeros keys %expediente_financiamientos_detalle){
	my $tri = $expediente_financiamientos_detalle{$k}{'d82_tributos'}+$expediente_financiamientos_detalle{$k}{'d82_fiesta'};
	my $cor = $expediente_financiamientos_detalle{$k}{'d82_tributos_corriente'}+$expediente_financiamientos_detalle{$k}{'d82_fiesta_corriente'};	
	my $row   = "<td>$k</td><td>$expediente_financiamientos_detalle{$k}{'i4_periodos_mora'}</td>
		     <td>$expediente_financiamientos_detalle{$k}{'i6_desde'}</td>
		     <td>$expediente_financiamientos_detalle{$k}{'i6_hasta'}</td>
		     <td>$tri</td>
		     <td>$expediente_financiamientos_detalle{$k}{'d82_interes'}</td>
		     <td>$expediente_financiamientos_detalle{$k}{'d82_multa'}</td>
		     <td>$cor</td>
		     <td>$expediente_financiamientos_detalle{$k}{'d82_cuota'}</td>
		     <td>$expediente_financiamientos_detalle{$k}{'dt_vencimiento'}</td>
		     ";
	$rowset  .= "<tr>".$row."</tr>";
	$t_tributos  += $tri;	
	$t_interes   += $expediente_financiamientos_detalle{$k}{'d82_interes'};
	$t_multa     += $expediente_financiamientos_detalle{$k}{'d82_multa'};
	$t_corriente += $cor;
	$t_cuota     += $expediente_financiamientos_detalle{$k}{'d82_cuota'};
    }
    $tfoot     =~ s/\{t_tributos}/$t_tributos/g;    
    $tfoot     =~ s/\{t_interes}/$t_interes/g;    
    $tfoot     =~ s/\{t_multa}/$t_multa/g;
    $tfoot     =~ s/\{t_corriente}/$t_corriente/g;
    $tfoot     =~ s/\{t_cuota}/$t_cuota/g;
    $rowset    = "<tbody>".$rowset."</tbody>";
    $table     = "<table id=grid>".$thead.$rowset.$tfoot."</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/\{c11_dui_id}/$c11_dui_id/g;
		$line =~ s/\{v64_municipio}/$v64_municipio/g;
		$line =~ s/\{v64_departamento}/$v64_departamento/g;
		$line =~ s/\{ubicacion}/$ubicacion/g;
		$line =~ s/\{table}/$plan_pago/g;	 
		$line =~ s/\{dia}/$dia/g;
		$line =~ s/\{mes}/$mes/g;
		$line =~ s/\{ano}/$ano/g;
	        foreach $key(keys %expediente_financiamientos){$line =~ s/{$key}/$expediente_financiamientos{$key}/g}
		$line = convertir_tildes($line);
		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;
}
sub mes_letras {
	my ($mes) = @_;
	my @mes_letras = ('','enero', 'febrero', 'marzo', 'abril', 'mayo', 'junio', 'julio', 'agosto', 'septiembre', 'octubre', 'noviembre', 'diciembre');
	return $mes_letras[$mes];
}
sub dia_mes_letras {
	my ($dia) = @_;
	my @dias_mes_letras = ('','un','dos','tres','cuatro','cinco','seis','siete','ocho','nueve','diez','once','doce','trece','catorce','quince','dieciseis','diecisiete','dieciocho','diecinueve','veinte','veintiun','veintidos','veintitres','veinticuatro','veinticinco','veintiseis','veintisiete','veintiocho','veintinueve','treinta','treinta y un');
	return $dias_mes_letras[$dia];
}
sub get_fecha_actual {
      my $fec_r ="";	
      my $query= "SELECT DATE_FORMAT(CURRENT_DATE,'%Y-%m-%d')";
      if ($dbh->Sql($query)){SQL_fail($dbh,$query,'sub get_fecha_actual')};
      if ($dbh->FetchRow()){undef my @row; @row=$dbh->Data();$fec_r=$row[0]}
      my @fec = split(/\-/,$fec_r);
      my $m = mes_letras($fec[1]*1);
      my $d = dia_mes_letras($fec[2]*1);
      my $y = dia_mes_letras(substr($fec[0],2,2));      
      return $y,$m,$d;
}
#-------------------------------------------------------------------------