#!/perl/bin/perl.exe
$|=1;
#-------------------------------------------------------------------------------
#-------------------------------------------------------------------------------
# Information Software SA
# Date In: 2013-02-21
# Updates: 2013-02-21
# Author:Rodolfo Machon
# File Name: mora_expedientes_notificacion_rept.pl
#-------------------------------------------------------------------------------
#-------------------------------------------------------------------------------
# PERL MODULES
use Win32::ODBC;
use CGI;
use Date::Calc qw(Days_in_Month);
use Date::Calc qw(Today);
#-------------------------------------------------------------------------------
#-------------------------------------------------------------------------------
print "Content-type: text/html\n\n";
#-------------------------------------------------------------------------------
#-------------------------------------------------------------------------------
$form 			 = new CGI;
$appid           = $form->param('appid');
$clase           = $form->param('clase');
$lan             = $form->param('lan');
$str_expedientes = $form->param('str_expedientes');
#-------------------------------------------------------------------------------
#-------------------------------------------------------------------------------
$PATH		= "../".$lan."/";
$file 		= $lan."_mora_expedientes_notificacion_rept.html";
#-------------------------------------------------------------------------------
#-------------------------------------------------------------------------------
# GET CLASE SCHEMA AND VARIABLES
if (!($dbh  = new Win32::ODBC($appid))){connect_fail($appid)}
$str_expedientes = substr($str_expedientes,0,length($str_expedientes)-1);
#-------------------------------------------------------------------------------
#-------------------------------------------------------------------------------
# PROGRAM DESCRIPTION
# PROCUREMENT
($emision,$periodo)	= get_emision_periodo();
%contribuyentes  	= get_contribuyentes();
%direcciones    	= get_direcciones();
%tributos			= get_tributos();
# INTEGRATION
%tributos_tab		= get_tributos_tab();
# PRESENTATION
$notificaciones 	= get_notificaciones();
construct_page();
exit;
#-------------------------------------------------------------------------------
#-------------------------------------------------------------------------------
#SUB-ROUTINES -> LEVEL 1
sub get_emision_periodo {
	my $dt  	 = "";
	my $per 	 = "";
	my ($y,$m,$d)= Today();
	my $m1		 = ($m*1-1);
	if(length($m)<2){$m = "0".$m}
	if(length($d)<2){$d = "0".$d}
	if(length($m1)<2){$m1 = "0".$m1}	
	$dt  = $d."-".$m."-".$y;
	$per = $y.$m1;
	return ($dt,$per)
}
sub get_contribuyentes {
    my %hash   = ();
    my $query  = "	SELECT a.i8_".$clase."_id,
						   b.v64_nombres,
						   b.v64_apellidos
					FROM sdd_".$clase."_contribuyentes a
						 INNER JOIN sdm_contribuyentes b on a.i8_contribuyente_id = b.i8_contribuyente_id
					WHERE a.i2_tipo_contribuyente_id='1' and a.i8_".$clase."_id IN ($str_expedientes)
					ORDER BY a.i8_".$clase."_id
				";
    if ( $dbh->Sql($query) ){SQL_fail($query,$dbh,'sub get_contribuyentes')};
    while($dbh->FetchRow()){
		my @row   = $dbh->Data();
		$hash{$row[0]} = [@row];
    }
    return %hash;	
}
sub get_direcciones {
    my %hash   = ();
    my $tabla  = "";
	my $filtro = ""; 
    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 IN ($str_expedientes)
		 ORDER BY a.i8_".$clase."_id";
    if ( $dbh->Sql($query) ){SQL_fail($query,$dbh,'sub get_expedientes_direccion')};
    while($dbh->FetchRow()){
		my @row   = $dbh->Data();
		my $dir   = "";
		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);	
		$hash{$row[0]} = $dir;
    }
    return %hash;
}
sub get_tributos {
    my %hash  = ();
	my $query = "SELECT a.i8_".$clase."_id,
						a.c16_tarifa_id,
						b.v128_tarifa,
						MIN(a.i8_periodo),
						MAX(a.i8_periodo),
						SUM(a.d82_val),
						SUM(a.d82_fie),
						SUM(a.d82_int),
						SUM(a.d82_mul),
						SUM(a.d82_val+a.d82_fie+a.d82_int+a.d82_mul)
				 FROM sdd_".$clase."_estados_mensuales a
				 LEFT JOIN sdm_tarifas b ON a.c16_tarifa_id=b.c16_tarifa_id
				 WHERE a.i4_dias_mora>0 and a.i8_".$clase."_id IN ($str_expedientes)
				 GROUP BY a.i8_".$clase."_id,a.c16_tarifa_id
				 ORDER BY a.i8_".$clase."_id
				";
    if ( $dbh->Sql($query) ){SQL_fail($query,$dbh,'sub get_tributos')};
    while($dbh->FetchRow()){
		my @row = $dbh->Data();
		$hash{$row[0]}{$row[1]} = [@row];
    }
    return %hash;	
}
sub get_tributos_tab {
	my %hash  = ();
	my $thead = "<thead><tr><td>No.</td><td>CODIGO</td><td>TRIBUTO</td><td>DESDE</TD><td>HASTA</TD><td>VALOR</td><td>FIESTAS</td><td>INTERESES</td><td>MULTAS</TD><td>TOTAL</td></tr></thead>";
	my $tfoot = "<tfoot><tr><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>TOTAL</td><td>{t_valor}</td><td>{t_fiesta}</td><td>{t_interes}</td><td>{t_multa}</td><td>{t_total}</td></tr></tfoot>";
	foreach my $k (sort keys %tributos){
		my $count   = 1;
		my $rowset  = "";
		my $tfoot_s = $tfoot;    
		foreach my $kk (sort keys %{$tributos{$k}}){
			my $row = "<td>$count</td>
						<td>".$kk."</td>
						<td>".$tributos{$k}{$kk}[2]."</td>
						<td>".$tributos{$k}{$kk}[3]."</td>
						<td>".$tributos{$k}{$kk}[4]."</td>
						<td>".$tributos{$k}{$kk}[5]."</td>
						<td>".$tributos{$k}{$kk}[6]."</td>
						<td>".$tributos{$k}{$kk}[7]."</td>
						<td>".$tributos{$k}{$kk}[8]."</td>
						<td>".$tributos{$k}{$kk}[9]."</td>
					";
			$rowset    .= "<tr>".$row."</tr>";
			$t_valor   += $tributos{$k}{$kk}[5];
			$t_fiesta  += $tributos{$k}{$kk}[6];
			$t_interes += $tributos{$k}{$kk}[7];
			$t_multa   += $tributos{$k}{$kk}[8];
			$t_total   += $tributos{$k}{$kk}[9];
			$count++;	
		}
		$t_valor   = sprintf("%.2f",$t_valor);
		$t_fiesta  = sprintf("%.2f",$t_fiesta);
		$t_interes = sprintf("%.2f",$t_interes);
		$t_multa   = sprintf("%.2f",$t_multa);
		$t_total   = sprintf("%.2f",$t_total);
		$tfoot_s   =~ s/\{t_valor}/$t_valor/g;
		$tfoot_s   =~ s/\{t_fiesta}/$t_fiesta/g;
		$tfoot_s   =~ s/\{t_interes}/$t_interes/g;
		$tfoot_s   =~ s/\{t_multa}/$t_multa/g;
		$tfoot_s   =~ s/\{t_total}/$t_total/g;
		$rowset    = "<tbody>".$rowset."</tbody>";
		$hash{$k} = "<table class='tributos_tab'>".$thead.$rowset.$tfoot_s."</table>";
	}
	return %hash;
}
sub get_notificaciones {
	my $count = 1;
	my %hash  = ();
	my $notes = "";
	my @emisi = split(/\-/,$emision);
	foreach my $k (sort keys %tributos){
		$notes .= "<div id='frame_$count' class='frame_div'>";
		$notes .= "<div class='alcaldia_div'>
						<p>ALCALDIA DE QUEZALTEPEQUE</p>
						<p>Palacio Municipal, Primerca Calle y Segunda Avenida Sur #1 y 2, PUERTO DE LA LIBERTAD, LA LIBERTAD.Tels.:2214-0600 / 01 ext. 136</p>
						<p>ESTADO DE MORA</p>
						<p>EN US DOLARES</p>
					</div>";		
		$notes .= "<div class='contribuyente_div'><ol><li>Señor(a) :</li><li>".$contribuyentes{$k}[1]." ".$contribuyentes{$k}[2]."</li><li>".$direcciones{$k}."</li></ol></div>";
		$notes .= "<div class='generales_div'><table class='generales_tab'>
							<tr><td>Fecha Emision</td><td>".$emision."</td></tr>
							<tr><td>Expediente ID</td><td>".$k."</td></tr>
							<tr><td>Generador</td><td>".uc($clase)."</td></tr>
							<tr><td>Periodo Mora</td><td>".$periodo."</td></tr>
							<tr><td>Notificacion ID</td><td>".uc(substr($clase,0,3)).$k.$periodo.$emisi[0].$emisi[1].$emisi[2]."</td></tr>
						</table>
					</div>
				   ";
		$notes .= "<div class='tributos_div'>".$tributos_tab{$k}."</div>";
		$notes .= "<div class='recuperacion_div'>
						<p>DEPARTAMENTO DE RECUPERACION DE MORA</p><br>
						<p>* Si al momento de recibir esta nota, usted ya cancelo favor ignorela.</p>
					</div>";
		$notes .= "</div><br><br>";
		$count++;
	}
	return $notes;
}
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/\{notificaciones}/$notificaciones/g;
        print "$line";
    }
}
#-------------------------------------------------------------------------------
#-------------------------------------------------------------------------------
#AUXILIAR-SUBROUTINES
sub SQL_fail {
    my ($query,$dbh,$section)= @_;
    print "SQL failed in Section $section<br>";
    print "Error: ".$dbh->Error()."\n<br>";
    print "Query: ".$query."<br>";    
    $dbh->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;
}
#-------------------------------------------------------------------------------