#!/perl/bin/perl.exe
$|=1;
#--------------------------------------------------------------------------------
#--------------------------------------------------------------------------------
# Information Software SA
# Date In: 2013-01-24
# Updates: 2013-01-24
# Author:Rodolfo Machon
# File Name: notificaciones_rept.pl
#--------------------------------------------------------------------------------
#--------------------------------------------------------------------------------
# PERL MODULES
use CGI;
use CGI::Carp qw(fatalsToBrowser);
use Win32::ODBC;
use Date::Calc qw(Days_in_Month);
use Date::Calc qw(Today);
#--------------------------------------------------------------------------------
#--------------------------------------------------------------------------------
# HTML PRINTING ACTIVATION
print "Content-type: text/html\n\n";
#--------------------------------------------------------------------------------
#--------------------------------------------------------------------------------
#GET PARAMETERS
$form 	    = new CGI;
$lan        = $form->param('lan');
$appid      = $form->param('appid');
$clase      = $form->param('clase');
$id         = $form->param('id');
$tipo	    = $form->param('tipo');
$i8_periodo = $form->param('i8_periodo');
#--------------------------------------------------------------------------------
#--------------------------------------------------------------------------------
# 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		= $lan."_notificacion_".$tipo."_".$clase."_rept.html";
$table_d 	= "sdr_".$clase."s_mora_detalle_".$i8_periodo;
#--------------------------------------------------------------------------------
#--------------------------------------------------------------------------------
# GET CLASE SCHEMA AND VARIABLES
if (!($dbh  = new Win32::ODBC($appid))){connect_fail($appid)}
#--------------------------------------------------------------------------------
#--------------------------------------------------------------------------------
# PROGRAM DESCRIPTION
# PROCUREMENT
$nombre		= get_nombre();
$direccion	= get_direccion();
$tipo		= get_tipo();
%mora		= get_mora();
%corriente	= get_corriente();
%receptor   = get_receptor();
# INTEGRATION
$table 		= construct_table();
# PRESENTATION
construct_page();
exit;
#--------------------------------------------------------------------------------
#--------------------------------------------------------------------------------
# MAIN-SUBROUTINES
sub get_nombre{
    my $nom   = "";
    my $query = "SELECT CONCAT(b.v64_nombres,' ',b.v64_apellidos)
				 FROM sdd_".$clase."_contribuyentes a
				 INNER JOIN sdm_contribuyentes b
				 WHERE a.i8_".$clase."_id='$id' and a.i8_contribuyente_id=b.i8_contribuyente_id  and (i2_tipo_contribuyente_id='1' or i2_tipo_contribuyente_id='3')";    
    if ( $dbh->Sql($query) ){SQL_fail($dbh,$query,'sub get_nombre')};
    while($dbh->FetchRow()){
	    my @row = $dbh->Data();
	    $nom    = $row[0];
    }
    return $nom;
}
sub get_direccion {
    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_direccion')};
    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 get_tipo {
    my $tipo = 0;
    my $query = "SELECT a.i2_tipo_notificacion_id FROM sdd_".$clase."_administrativo a WHERE a.i8_".$clase."_id='$id' and c8_gestion_estado='ACTIVO' order by a.dt_administrativo desc limit 1";    
    if ( $dbh->Sql($query) ){SQL_fail($query,$dbh,'sub get_tipo')};
    while($dbh->FetchRow()){
	    my @row = $dbh->Data();
	    $tipo   = $row[0];
    }
    if($tipo>=5){$tipo=0}
    return $tipo;
}
sub get_mora {
	my %hash	= ();
    my $query 	= "SELECT SUM(d82_val),SUM(d82_fie),SUM(d82_int),SUM(d82_mul),MIN(i8_periodo),MAX(i8_periodo) FROM $table_d where i8_".$clase."_id='$id' and i4_dias_mora>0 and d82_val>0";
    if ( $dbh->Sql($query) ){SQL_fail($query,$dbh,'sub get_mora')};
    while($dbh->FetchRow()){
		my @rowx	= $dbh->Data();
		$hash{$id}	= [@rowx];
    }
	return %hash;
}
sub get_corriente {
	my %hash	= ();
    my $query 	= "SELECT SUM(d82_val),SUM(d82_fie),SUM(d82_int),SUM(d82_mul) FROM $table_d where i8_".$clase."_id='$id' and i4_dias_mora=0 and d82_val>0";
    if ( $dbh->Sql($query) ){SQL_fail($query,$dbh,'sub get_corriente')};
    while($dbh->FetchRow()){
		my @rowx	= $dbh->Data();
		$hash{$id}	= [@rowx];
    }
	return %hash;	
}
sub get_receptor {
	my %hash  = ();
    my $query = "SELECT a.v64_receptor_nombres,
						a.v64_receptor_apellidos,
						a.c11_receptor_dui,
						a.v32_receptor_telefono,
						b.v64_tipo_contribuyente,
						a.dt_emision,
						a.dt_notificacion,
						a.i2_tipo_receptor_id
				FROM sdd_".$clase."_notificaciones a
				LEFT JOIN sdm_tipo_contribuyentes b on a.i2_tipo_receptor_id=b.i2_tipo_contribuyente_id
				WHERE a.i8_".$clase."_id='$id' and a.i2_tipo_notificacion_id='$tipo'";
    if ( $dbh->Sql($query) ){SQL_fail($query,$dbh,'sub get_notificaciones')};
    while($dbh->FetchRow()){
		my @row = $dbh->Data();
		if($row[7] eq "10"){$row[4]="PADRE"}
		if($row[7] eq "11"){$row[4]="MADRE"}
		if($row[7] eq "12"){$row[4]="ESPOSA"}
		if($row[7] eq "14"){$row[4]="HERMANO"}
		if($row[7] eq "15"){$row[4]="TIO(A)"}
		if($row[7] eq "16"){$row[4]="PRIMO(A)"}
		if($row[7] eq "17"){$row[4]="VECINO(A)"}
		if($row[7] eq "13"){$row[4]="NO APLICA"}
		$hash{$id} = [@row];
    }
    return %hash;
}
sub construct_table {
	$desde_mor	= "01-".substr($mora{$id}[4],4,2)."-".substr($mora{$id}[4],0,4);
	$hasta_mor	= get_fin_mes($mora{$id}[5]);
	$desde_cor	= "01-".substr($i8_periodo,4,2)."-".substr($i8_periodo,0,4);
	$hasta_cor	= get_fin_mes($i8_periodo);
    $mora		= big_money($mora{$id}[0]+$mora{$id}[1]+$mora{$id}[2]+$mora{$id}[3]);
	$mul_int	= big_money($mora{$id}[1]+$mora{$id}[2]+$mora{$id}[3]);
    $corriente	= big_money($corriente{$id}[0]+$corriente{$id}[1]+$corriente{$id}[2]+$corriente{$id}[3]);
    $deuda		= big_money($mora{$id}[0]+$mora{$id}[1]+$mora{$id}[2]+$mora{$id}[3]+$corriente{$id}[0]+$corriente{$id}[1]+$corriente{$id}[2]+$corriente{$id}[3]);
    my $tab 	  = "<table class=Grid>
						<tr><td>MORA DESDE $desde_mor HASTA $hasta_mor</td><td align=right>$mora</td></tr>
						<tr><td>MESES CORRIENTES DESDE $desde_cor HASTA $hasta_cor</td><td align=right>$corriente</td></tr>
						<tr><td><b>TOTAL</b></td><td align=right><b>$deuda</b></td></tr>
						</table>
					";
    return $tab;
}
sub construct_page {
    my $pagina	= "";
    my $todate	= "<script language='javascript'>document.write(dateString)</script>";
    open (DATA, "$PATH$file");
	#print "$PATH$file <br>";
    while ($line = <DATA>) {	
		$line =~ s/\{lan}/$lan/g;
		$line =~ s/\{appid}/$appid/g;
		$line =~ s/\{id}/$id/g;
		$line =~ s/\{nombre}/$nombre/g;
		$line =~ s/\{direccion}/$direccion/g;
		$line =~ s/\{generador}/$clase/g;
		$line =~ s/\{table}/$table/g;
		$line =~ s/\{deuda}/$deuda/g;
		$line =~ s/\{mora}/$mora/g;		 
		$line =~ s/\{mul_int}/$mul_int/g;
		$line =~ s/\{desde_mor}/$desde_mor/g;
		$line =~ s/\{hasta_mor}/$hasta_mor/g;
		$line =~ s/\{recibio}/$receptor{$id}[0] $receptor{$id}[1]/g;
		$line =~ s/\{dui}/$receptor{$id}[2]/g;
		$line =~ s/\{telefono}/$receptor{$id}[3]/g;
		$line =~ s/\{relacion}/$receptor{$id}[4]/g;
		$line =~ s/\{todate}/$receptor{$id}[5]/g;
		$line =~ s/\{entregado}/$receptor{$id}[6]/g;
		$pagina .= $line;
    }
	$pagina = convertir_tildes($pagina);
    print "$pagina";
}
#--------------------------------------------------------------------------------
#--------------------------------------------------------------------------------
# AUXILIAR-SUBROUTINES
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 big_money {
  my $number = sprintf "%.2f", shift @_;
  # Add one comma each time through the do-nothing loop
  1 while $number =~ s/^(-?\d+)(\d\d\d)/$1,$2/;
  # Put the dollar sign in the right place
  $number =~ s/^(-?)/$1\$/;
  $number;
}
sub get_fin_mes {
    my ($per) = @_;
    my $ano = substr($per,0,4)*1;
    my $mes = substr($per,4,2)*1;
	
    my $dia = Days_in_Month($ano,$mes);
    return "$dia-$mes-$ano";	
}
#-------------------------------------------------------------------------
