#!/perl/bin/perl.exe
$|=1;
#--------------------------------------------------------------------------------
#--------------------------------------------------------------------------------
# Information Software SA
# Date In: 2012-12-08
# Updates: 2012-12-08
# Author:Rodolfo Machon
# File Name: mora_cartera_sector_expedientes_p.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');
$c11_sector_id = $cgiparms->param('c11_sector_id');
$v64_sector    = $cgiparms->param('v64_sector');
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# 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 ($c11_sector_id eq ""){print "Your are not passing SECTOR parameter<br>";}
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# GLBAL VARIABLES
$PATH 		= "../".$lan."/";
$file       	= $lan."_mora_cartera_sector_expedientes_gestor_p.html";
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# GET CLASE SCHEMA AND VARIABLES
if (!($dbh  = new Win32::ODBC($appid))){connect_fail($appid)}
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# PROGRAM DESCRIPTION
# PROCUREMENT
($uid,$uf,$ul)         = get_user();
$expedientes_gestor    = get_expedientes_gestor();
if($expedientes_gestor ne ""){
	%expedientes  	       = get_expedientes();
	%expedientes_direccion = get_expedientes_direccion();
	%expedientes_mora      = get_expedientes_mora();
# INTEGRATION
	%expedientes_direccion_mora = get_expedientes_direccion_mora();
# PRESENTATION
	$table 		   = construct_table();
}
construct_page();
exit;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#SUB-ROUTINES -> LEVEL 1
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;
}
sub get_expedientes_gestor {
    my $str   = "";
    my $query = "SELECT a.i8_".$clase."_id
		 FROM sdd_".$clase."_gestores a
		 WHERE a.c8_estado='ACTIVO' and a.i8_gestor_id='$uid'
		 ORDER BY a.i8_".$clase."_id";
    if ( $dbh->Sql($query) ){SQL_fail($query,$dbh,'sub get_expedientes')};
    while($dbh->FetchRow()){
	my @row = $dbh->Data();
	$str .= $row[0].",";
    }
    $str = substr($str,0,length($str)-1);
    return $str;
}
sub get_expedientes {
    my %hash  = ();
    my $query = "";
    if($clase eq "inmueble"){
	$query = "SELECT a.i8_".$clase."_id,c.v64_apellidos,c.v64_nombres
		  FROM sdm_".$clase."s a
		  LEFT JOIN sdd_".$clase."_contribuyentes b ON a.i8_".$clase."_id=b.i8_".$clase."_id
		  LEFT JOIN sdm_contribuyentes c ON b.i8_contribuyente_id=c.i8_contribuyente_id
		  WHERE a.c16_estado='ACEPTADO' AND a.c11_sector_id='$c11_sector_id' and a.i8_".$clase."_id IN ($expedientes_gestor)
		  GROUP BY a.i8_".$clase."_id";
    }
    if($clase eq "empresa"){
	$query = "SELECT  a.i8_".$clase."_id,c.v64_apellidos,c.v64_nombres
		  FROM sdm_".$clase."s a
		  LEFT JOIN sdd_".$clase."_ubicacion b ON a.i8_".$clase."_id=b.i8_".$clase."_id
		  LEFT JOIN sdd_".$clase."_contribuyentess c ON a.i8_".$clase."_id=c.i8_".$clase."_id
		  LEFT JOIN sdm_contribuyentes d ON c.i8_contribuyente_id=d.i8_i8_contribuyente_id
		  WHERE a.c16_estado='ACEPTADO' AND b.c11_sector_id='$c11_sector_id' and a.i8_".$clase."_id IN ($expedientes_gestor)
		  GROUP BY a.i8_".$clase."_id";
    }
    if ( $dbh->Sql($query) ){SQL_fail($query,$dbh,'sub get_expedientes')};
    while($dbh->FetchRow()){
	my @row = $dbh->Data();
	$hash{$row[0]} = [@row];
    }
    return %hash;
}
sub get_expedientes_direccion {
    my %hash  = ();
    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.c11_sector_id='$c11_sector_id' and a.i8_".$clase."_id IN ($expedientes_gestor)
		 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_expedientes_mora {
    my %hash  = ();
    if($clase eq "inmueble"){
	$query = "SELECT a.i8_".$clase."_id,sum(a.d82_val),sum(a.d82_fie),sum(a.d82_int),sum(a.d82_mul),(sum(a.d82_val)+sum(a.d82_fie)+sum(a.d82_int)+sum(a.d82_mul))
		  FROM sdd_".$clase."_estados_mensuales a
		  INNER JOIN sdm_".$clase."s  b	on a.i8_".$clase."_id=b.i8_".$clase."_id
		  WHERE b.c11_sector_id='$c11_sector_id' and a.i4_dias_mora>0 and a.i8_".$clase."_id IN ($expedientes_gestor)
		  GROUP BY a.i8_".$clase."_id";
    }
    if($clase eq "empresa"){
	$query = "SELECT  a.i8_".$clase."_id,,sum(a.d82_val),sum(a.d82_fie),sum(a.d82_int),sum(a.d82_mul),(sum(a.d82_val)+sum(a.d82_fie)+sum(a.d82_int)+sum(a.d82_mul))
		  FROM sdd_".$clase."_estados_mensuales a
		  INNER JOIN sdm_".$clase."s  b	ON a.i8_".$clase."_id=b.i8_".$clase."_id
		  INNER JOIN sdd_".$clase."_ubicacion c ON a.i8_".$clase."_id=c.i8_".$clase."_id
		  WHERE c.c11_sector_id='$c11_sector_id' and a.i4_dias_mora>0 and a.i8_".$clase."_id IN ($expedientes_gestor)	
		  GROUP BY a.i8_".$clase."_id";
    }
    if ( $dbh->Sql($query) ){SQL_fail($query,$dbh,'sub get_expedientes_mora')};
    while($dbh->FetchRow()){
	my @row = $dbh->Data();
	$hash{$row[0]} = [@row];
    }
    return %hash;
}
sub get_expedientes_direccion_mora {
    my %hash  = ();
    foreach my $k (sort keys %expedientes){
	$hash{$k}{'apellidos'}  = $expedientes{$k}[1];
	$hash{$k}{'nombres'}	= $expedientes{$k}[2];	
	$hash{$k}{'direccion'}	= $expedientes_direccion{$k};
	$hash{$k}{'valor'}	= $expedientes_mora{$k}[1];
	$hash{$k}{'fiesta'}	= $expedientes_mora{$k}[2];
	$hash{$k}{'interes'}	= $expedientes_mora{$k}[3];
	$hash{$k}{'multa'}	= $expedientes_mora{$k}[4];
	$hash{$k}{'total'}	= $expedientes_mora{$k}[5];	
    }
    return %hash;
}
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>No.</td><td>ID</td><td>APELLIDOS</td><td>NOMBRES</td><td>DIRECCION</td><td>VALOR</td><td>FIESTA</TD><td>INTERES</td><td>MULTA</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 %expedientes_direccion_mora){
	my $row   = "<td><a href=\"javascript:OpenLink('$k','$clase')\">$count</a></td>
		     <td>".$k."</td>
		     <td>".$expedientes_direccion_mora{$k}{'apellidos'}."</td>
		     <td>".$expedientes_direccion_mora{$k}{'nombres'}."</td>
		     <td>".$expedientes_direccion_mora{$k}{'direccion'}."</td>
		     <td>".$expedientes_direccion_mora{$k}{'valor'}."</td>
		     <td>".$expedientes_direccion_mora{$k}{'fiesta'}."</td>
		     <td>".$expedientes_direccion_mora{$k}{'interes'}."</td>
		     <td>".$expedientes_direccion_mora{$k}{'multa'}."</td>
		     <td>".$expedientes_direccion_mora{$k}{'total'}."</td>
		     ";
	$rowset    .= "<tr>".$row."</tr>";
	$t_valor   += $expedientes_direccion_mora{$k}{'valor'};
	$t_fiesta  += $expedientes_direccion_mora{$k}{'fiesta'};
	$t_interes += $expedientes_direccion_mora{$k}{'interes'};
	$t_multa   += $expedientes_direccion_mora{$k}{'multa'};
	$t_total   += $expedientes_direccion_mora{$k}{'total'};
	$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/\{t_valor}/$t_valor/g;
    $tfoot     =~ s/\{t_fiesta}/$t_fiesta/g;
    $tfoot     =~ s/\{t_interes}/$t_interes/g;
    $tfoot     =~ s/\{t_multa}/$t_multa/g;
    $tfoot     =~ s/\{t_total}/$t_total/g;
    $rowset    = "<tbody>".$rowset."</tbody>";
    $table     = "<table id=grid>".$thead.$rowset.$tfoot."</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/\{table}/$table/g;
        $line =~ s/\{c11_sector_id}/$c11_sector_id/g;
        $line =~ s/\{v64_sector}/$v64_sector/g;
        print "$line";
    }
}
#-------------------------------------------------------------------------
#AUXILIAR-SUBROUTINES
sub numeros {$a<=>$b}
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;
}
#-------------------------------------------------------------------------