#!/perl/bin/perl.exe
$|=1;
#--------------------------------------------------------------------------------
#--------------------------------------------------------------------------------
# WebSoft SA
# Date In: 2013-05-21
# Updates: 2013-05-21, 2016-03-30
# Author:Rodolfo Machon
# File Name: empresas_sector_avisos_cobros_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');
$c11_sector_id 	= $cgiparms->param('c11_sector_id');
$v64_sector    	= $cgiparms->param('v64_sector');
$ubi_id     	= $cgiparms->param('ubi_id');
$ubi_tipo   	= $cgiparms->param('ubi_tipo');
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# CHECK PARAMETERS
if ($lan eq ""){print "Your are not passing LAN parameter<br>";}
if ($appid eq ""){print "Your are not passing APPID parameter<br>";}
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# GLBAL VARIABLES
$PATH 		= "../".$lan."/";
$file       = $lan."_empresas_sector_detalle_avisos_cobros_rept.html";
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# GET CLASE SCHEMA AND VARIABLES
if (!($dbh  = new Win32::ODBC($appid))){connect_fail($appid)}
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# PROGRAM DESCRIPTION
# PROCUREMENT
($uid,$uf,$ul,$rev,$sup)= get_user();
$dt_vencimiento			= get_vencimiento();
$ubi_field				= get_ubi_field($ubi_tipo);
%tarifas				= get_tarifas();
%tributos				= get_tributos();
%expedientes			= get_expedientes();
%expedientes_nis		= get_nis();
%expedientes_direccion	= get_expedientes_direccion();
%expedientes_servicios	= get_expedientes_servicios();
%expedientes_bases		= get_expedientes_bases();
%expedientes_imagenes	= get_expedientes_imagenes();
# INTEGRATION
%expedientes_tributos   = get_expedientes_tributos();
%expedientes_direccion_servicios = get_expedientes_direccion_servicios();
# PRESENTATION
$table 		   	= construct_table();
construct_page();
exit;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#SUB-ROUTINES -> LEVEL 1
sub get_user {
	my $uid	= "";
	my $uf	= "";
	my $ul	= "";
	my $re	= "";
	my $su	= "";
    my $user  = $ENV{"REMOTE_USER"};
	my $query = "SELECT a.i8_user_id,a.v32_fname,a.v32_lname,a.c2_revisador,a.c2_supervisor 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];
		$re  = $row[3];
		$su  = $row[4];		
	}
	return $uid,$uf,$ul,$re,$su;
}
sub get_vencimiento {
    my $str  = "";
    my $query = "SELECT a.dt_generado
				 FROM sdd_empresa_estados_mensuales a
				 GROUP BY a.dt_generado
				 ";
    if ( $dbh->Sql($query) ){SQL_fail($query,$dbh,'sub get_vencimiento')};
		while($dbh->FetchRow()){
		my @row = $dbh->Data();
		$str = $row[0];
    }
    return $str;	
}
sub get_ubi_field {
	my ($ubit)=@_;
	$ubif{'calles'}   = "d.c4_calle_id";
	$ubif{'avenidas'} = "d.c4_avenida_id";
	$ubif{'pasajes'}  = "d.c2_pasaje_id";
	$ubif{'sendas'}   = "d.c2_senda_id";
	$ubif{'poligonos'}= "d.c2_poligono_id";
	$ubif{'blocks'}   = "d.c2_block_id";	
	return $ubif{$ubit};
}
sub get_tarifas {
    my %hash   = ();
	my $query = "SELECT a.c8_tributo_id,
						a.c1_tipo_tarifa_id,
						a.c2_codigo_id,
					    b. c2_tipo_base_imponible_id
				 FROM sdm_tarifas a
				 INNER JOIN sdd_tarifas b ON a.c16_tarifa_id=b.c16_tarifa_id
				 ORDER BY a.c16_tarifa_id
				 ";
    if ( $dbh->Sql($query) ){SQL_fail($query,$dbh,'sub get_tarifas')};
    while($dbh->FetchRow()){
		my @row = $dbh->Data();
		$hash{$row[0]}{$row[1]}{$row[2]} = $row[3];
    }
    return %hash;
}
sub get_tributos {
    my %hash   = ();
	my $query = "SELECT c8_tributo_id,v64_tributo FROM sdm_tributos";
    if ( $dbh->Sql($query) ){SQL_fail($query,$dbh,'sub get_tributos')};
    while($dbh->FetchRow()){
		my @row = $dbh->Data();
		$hash{$row[0]} = [@row];
    }
    return %hash;
}
sub get_expedientes {
    my %hash   = ();
	if($ubi_id ne "" && $ubi_tipo ne ""){$filtro = "AND a.$ubi_field='$ubi_id'"}else{$filtro = ""}	
	my $query = "SELECT a.i8_empresa_id,c.v64_apellidos,c.v64_nombres,a.c8_base_empresa
				FROM sdm_empresas a
				LEFT JOIN sdd_empresa_contribuyentes b ON a.i8_empresa_id=b.i8_empresa_id and b.i2_tipo_contribuyente_id='1'
				LEFT JOIN sdm_contribuyentes c ON b.i8_contribuyente_id=c.i8_contribuyente_id
				LEFT JOIN sdd_empresa_ubicacion d on  a.i8_empresa_id=d.i8_empresa_id
				WHERE a.c16_estado='ACEPTADO' AND d.c11_sector_id='$c11_sector_id' $filtro
				GROUP BY a.i8_empresa_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_nis {
    my %hash   = ();
    my $exps   = "'0',";
    foreach my $k (sort numeros keys %expedientes){$exps .= "'".$k."',";}
    $exps = substr($exps,0,length($exps)-1);	
	my $query = "SELECT a.i8_empresa_id,b.i8_nic_id,b.c8_estado
				FROM sdm_empresas a
				LEFT JOIN sdd_empresa_suministro_nics b ON a.i8_empresa_id=b.i8_empresa_id
				WHERE a.i8_empresa_id in ($exps)
				GROUP BY a.i8_empresa_id";
    if ( $dbh->Sql($query) ){SQL_fail($query,$dbh,'sub get_nis')};
    while($dbh->FetchRow()){
		my @row = $dbh->Data();
		$hash{$row[0]} = [@row];
    }
    return %hash;
}
sub get_expedientes_direccion {
    my %hash   = ();
    my $tabla  = "";
    my $exps   = "'0',";
    foreach my $k (sort numeros keys %expedientes){$exps .= "'".$k."',";}
    $exps = substr($exps,0,length($exps)-1);
    my $query = "SELECT a.i8_empresa_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,
			a.txt_referencia			
		 FROM sdd_empresa_ubicacion 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_empresa_id in ($exps)
		 ORDER BY a.i8_empresa_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 .= " ".$row[1].","}
		if($row[2] ne ""){$dir .= " ".$row[2].","}
		if($row[3] ne ""){$dir .= " ".$row[3].","}
		if($row[4] ne ""){$dir .= " ".$row[4].","}
		if($row[5] ne ""){$dir .= " ".$row[5].","}
		if($row[6] ne ""){$dir .= " ".$row[6].","}
		if($row[7] ne ""){$dir .= " ".$row[7].","}		
		if($row[8] ne ""){$dir .= "# ".$row[8].","}
		if($row[9] ne ""){$dir .= " ".$row[9].","}		
		
		$dir = substr($dir,0,length($dir)-1);	
		$hash{$row[0]} = $dir;
    }
    return %hash;
}
sub get_expedientes_servicios {
     my %hash  = ();
    my $exps   = "'0',";
    foreach my $k (sort numeros keys %expedientes){$exps .= "'".$k."',";}
    $exps = substr($exps,0,length($exps)-1);	
    my $query  = "SELECT a.i8_empresa_id,
						 b.c8_tributo_id,
						 b.c1_tipo_tarifa_id,
						 b.c2_codigo_id,
						 b.c2_gravable,
						 b.i2_grupo_id	 
				  FROM sdm_empresas a
				  INNER JOIN sdd_empresa_servicios b on a.i8_empresa_id=b.i8_empresa_id
				  WHERE a.i8_empresa_id in ($exps)
				 ";
    if ( $dbh->Sql($query) ){SQL_fail($query,$dbh,'sub get_servicios');}
    while($dbh->FetchRow()){
        undef my @row;
        @row = $dbh->Data();
        $hash{$row[0]}{$row[1]}{$row[5]}= [@row];
    }
 	return %hash;
}
sub get_expedientes_bases {
     my %hash  = ();
    my $exps   = "'0',";
    foreach my $k (sort numeros keys %expedientes){$exps .= "'".$k."',";}
    $exps = substr($exps,0,length($exps)-1);
    my $query  = "SELECT a.i8_empresa_id,
						 b.c2_tipo_base_imponible_id,
						 (b.f4_total-b.f4_deducible),
						 b.i2_grupo_id						 
				  FROM sdm_empresas a
				  INNER JOIN sdd_empresa_bases_imponibles b ON a.i8_empresa_id=b.i8_empresa_id
				  WHERE a.i8_empresa_id in ($exps)
				  ";
	#print "$query";
    if ( $dbh->Sql($query) ){SQL_fail($query,$dbh,'sub get_bases');}
    while($dbh->FetchRow()){
        undef my @row;
        @row = $dbh->Data();
        $hash{$row[0]}{$row[1]}{$row[3]}= [@row];
    }
 	return %hash;
}
sub get_expedientes_imagenes {
	my %hash    	= ();
	my $files_path 	= "../"."fichas_cuentas_empresas/";
	opendir (DIR,$files_path) or die "$!";
	@files = readdir DIR;	
	close DIR;
	foreach $fi (@files){
		my $fina =substr($fi,0,7);		
		$hash{$fina} += 1;
	}
	return %hash;
}
sub get_expedientes_tributos {
    my %hash  = ();
    foreach my $k (sort keys %expedientes_servicios){
		foreach my $kk (sort keys %{$expedientes_servicios{$k}}){
			foreach my $kkk (sort keys %{$expedientes_servicios{$k}{$kk}}){		
				my $bit 				  = $tarifas{$kk}{$expedientes_servicios{$k}{$kk}{$kkk}[2]}{$expedientes_servicios{$k}{$kk}{$kkk}[3]};
				$hash{$k}{$kk}{'tributo'} = $kk;
				$hash{$k}{$kk}{'tip_tar'} = $expedientes_servicios{$k}{$kk}{$kkk}[2];
				$hash{$k}{$kk}{'codigo'}  = $expedientes_servicios{$k}{$kk}{$kkk}[3];
				$hash{$k}{$kk}{'gravable'}= $expedientes_servicios{$k}{$kk}{$kkk}[4];			
				$hash{$k}{$kk}{'tip_bas'} = $bit;
				$hash{$k}{$kk}{'medida'}  = $expedientes_bases{$k}{$bit}{$kkk}[2];
			}
		}	
    }
    return %hash;
}
sub get_expedientes_direccion_servicios {
    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}{'ficha'}		 = $expedientes{$k}[3];
    }
    return %hash;
}
sub construct_table {
    my $count   	= 1;
	my %servicios	= ();
    my $table   	= "";
    my %tar_cue 	= ();    
    my %thead_h 	= ();
    my $thead_s 	= "";
    my $rowset  	= "";
	my $str_notificacion = "Agregar";
    my $thead   = "<thead><tr><td>No.</td><td>ID</td><td>NOMBRE CONTRIBUYENTE</td><td>DIRECCION</td><td>FICHA</td>{thead_s}</tr></thead>";
    foreach my $k (sort keys %expedientes_direccion_servicios){
		my $estado_r  	= "";
		my $estado_s  	= "";	
		my $nic = $expedientes_nis{$k}[1];
		my $act = $expedientes_nis{$k}[2];		
		if($nic eq ""){$nic = " - "; $act = " - "}
		if($expedientes_imagenes{$k} eq ""){$expedientes_imagenes{$k} = " - ";}		
		if(!$expedientes_direccion_servicios{$k}{'direccion'}){$expedientes_direccion_servicios{$k}{'direccion'}= "-"}
		my $row   = "<td><a href=\"javascript:OpenExpediente('$k')\">".$count."</a></td>
					 <td><a ('$k')\">".$k."</a></td>
					 <td><a ('$k')\">".$expedientes_direccion_servicios{$k}{'apellidos'}.", ".$expedientes_direccion_servicios{$k}{'nombres'}."</a></td>
					 <td><a ('$k')\">".$expedientes_direccion_servicios{$k}{'direccion'}."</a></td>
					 <td><a href=\"javascript:OpenServicios('$k')\">".$expedientes_direccion_servicios{$k}{'ficha'}."</a></td>
					 
				    ";
		$rowset .= "<tr>".$row."</tr>";
		$count++;		
    }
	$thead  =~ s/\{thead_s}/$thead_s/g;
    $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/\{table}/$table/g;
        $line =~ s/\{c11_sector_id}/$c11_sector_id/g;
        $line =~ s/\{v64_sector}/$v64_sector/g;
        $line =~ s/\{ubi_id}/$ubi_id/g;
        $line =~ s/\{ubi_tipo}/$ubi_tipo/g;
        $line =~ s/\{dt_vencimiento}/$dt_vencimiento/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;
}
#-------------------------------------------------------------------------