#!/perl/bin/perl.exe
$|=1;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# WebSoft SA
# Date In: 2012-11-27
# Updates: 2012-11-27, 2016-03-30
# Author: Rodolfo Machon
# File Name: generar_mensualidades_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');
$i8_periodo 	        = $cgiparms->param('i8_periodo');
#-------------------------------------------------------------------------
#lan         = "es";
#appid       = "RTM_v30";
#$clase      = "empresa";
#$i8_periodo = "201011";
#-------------------------------------------------------------------------
# CHECK PARAMETERS
if ($lan eq ""){print "Your are not passing LAN parameter<br>";}
if ($appid eq ""){print "Your are not passing APPID parameter<br>";}
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# GLOBAL VARIABLES
$PATH 	     = "../".$lan."/";
$FILE        = $lan."_mensualidades_generadas_p".".html";
$dir         ="../archivos_carga/";
$year        = substr($i8_periodo,0,4);
$month       = substr($i8_periodo,4,2);
$day         = "30";
$dt_actual   = $year."-".$month."-".$day;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# SET DATABASE HANDLERS
if (!($dbh  = new Win32::ODBC($appid))){connect_fail($appid)}
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# PROGRAM DESCRIPTION
# PROCUREMENT
%tarifas     = get_tarifas();
%tarifas_d   = get_tarifas_detalles();      
%accesorios  = get_accesorios();
%servicios   = get_servicios();
%bases       = get_bases();
@expedientes = get_expedientes($clase);
# INTEGRATION
for (my $a=0;$a<=$#expedientes;$a++){
      $id       = $expedientes[$a];
      %cargos	= get_cargos($id);
      delete_cargo_from_database($id);
      insert_cargo_into_database($id);
}
# PRESENTATION
construct_page();
exit;
#------------------------------------------------------------------------- 
#-------------------------------------------------------------------------
#SUB-ROUTINES -> LEVEL 1
sub get_tarifas {
      my %tari  = ();
      my $query	= "SELECT MAX(c16_tarifa_id),c8_tributo_id,c1_tipo_tarifa_id,c2_codigo_id,c1_frecuencia_tarifa_id,c1_composicion_tarifa_id,d82_tarifa,MAX(dt_desde) FROM sdm_tarifas group by c8_tributo_id,c1_tipo_tarifa_id,c2_codigo_id";
      if ( $dbh->Sql($query) ){SQL_fail($query,$dbh,'sub get_tarifas');}                  
      while($dbh->FetchRow()){
            undef my @row;
            @row = $dbh->Data();
            $tari{$row[1]}{$row[2]}{$row[3]}{$row[4]}{$row[5]} = [@row];
      }
#      print "TARIFAS\n<br>";      
#      foreach $k (sort keys %tari){
#	    foreach $kk (sort keys %{$tari{$k}}){
#            	    foreach $kkk (sort keys %{$tari{$k}{$kk}}){
#                        foreach $kkkk (sort keys %{$tari{$k}{$kk}{$kkk}}){
#                              foreach $kkkkk (sort keys %{$tari{$k}{$kk}{$kkk}{$kkkk}}){
#                                    print "$k=>$kk=>$kkk=>$kkkk=>$kkkkk<>$tari{$k}{$kk}{$kkk}{$kkkk}{$kkkkk}[6]<br>";
#                              }
#                        }
#            	    }
#            }
#	    print "\n<br>";
#      }
      return %tari;
}
sub get_tarifas_detalles {
      my %tarid  = ();
      my $query	= "SELECT c16_tarifa_id,c16_tipo_tarifa_proceso_id,c2_tipo_base_imponible_id,i2_rango,d82_valor,d82_excedente,d82_divisor,d82_rango_minimo,d82_rango_maximo FROM sdd_tarifas";
      if ( $dbh->Sql($query) ){SQL_fail($query,$dbh,'sub get_tarifas');}                  
      while($dbh->FetchRow()){
            undef my @row;
            @row = $dbh->Data();
            $tarid{$row[0]} = [@row]
      }
      return %tarid;
}
sub get_accesorios {
      my %acce	= ();
      my $query = "SELECT c8_tributo_id,c8_accesorio_id,dt_desde,dt_hasta FROM sdd_tributo_accesorios";
      if ( $dbh->Sql($query) ){SQL_fail($query,$dbh,'sub get_accesorios 1');}
      while($dbh->FetchRow()){
            undef my @row;
            @row = $dbh->Data();
            $acce{$row[0]}{$row[1]}=[@row];
      }
#      print "ACCE\n<br>";      
#      foreach $k (sort keys %acce){
#	    foreach $kk (sort keys %{$acce{$k}}){
#                   print "$k=>$kk=><>$acce{$k}{$kk}[2]<br>";
#            }
#	    print "\n<br>";
#      } 
      return %acce;
}
sub get_servicios {
      my %serv = ();
      my $query = "SELECT i8_".$clase."_id,i2_grupo_id,c8_tributo_id,c1_tipo_tarifa_id,c2_codigo_id,dt_desde,dt_hasta
                        FROM sdd_".$clase."_servicios WHERE c2_gravable='SI' and dt_desde<='$dt_actual' and dt_hasta>='$dt_actual' and c8_tributo_id!='TSINMSEF' ORDER BY 1,2,3";
      #print "$query";
      if ( $dbh->Sql($query) ){SQL_fail($query,$dbh,'sub get_tributos');}
      while($dbh->FetchRow()){
	    undef my @row;
	    @row            = $dbh->Data();
	    $serv{$row[0]}{$row[1]}{$row[2]} = [@row];
      }
      return %serv;
}
sub get_bases {
      my %base	 = ();
      my $query = "SELECT i8_".$clase."_id,i2_grupo_id,c2_tipo_base_imponible_id,f4_total,f4_deducible,i2_tipo_nivel_id,i2_divisor,dt_desde
                        FROM sdd_".$clase."_bases_imponibles";
      if ( $dbh->Sql($query) ){SQL_fail($query,$dbh,'sub get_bases');}
      while($dbh->FetchRow()){
            undef my @row;
            @row   = $dbh->Data();
            $base{$row[0]}{$row[1]}{$row[2]} = [@row];
      }
#      print "BASES\n<br>";      
#      foreach $k (sort keys %base){
#	    foreach $kk (sort keys %{$base{$k}}){
#            	    foreach $kkk (sort keys %{$base{$k}{$kk}}){
#                           print "$k=>$kk=>$kkk=><>$base{$k}{$kk}{$kkk}[0]<br>";
#            	    }
#            }
#	    print "\n<br>";
#      } 
      return %base;
}
sub get_expedientes {
      my $stream = "";
      my $query  = "SELECT i8_".$clase."_id FROM sdd_".$clase."_suministro_nics WHERE c8_estado='ACT' ORDER BY 1";
      if ( $dbh->Sql($query) ){SQL_fail($query,$dbh,'sub get_tributos');}
      while($dbh->FetchRow()){
	    undef my @row;
	    @row     = $dbh->Data();
	    $stream .= $row[0].",";
      }
      $stream  = substr($stream,0,length($stream)-1);
      my @expe = split(/\,/,$stream);
      return @expe;
}
sub get_cargos {
      my ($id)  = @_;
      my %cargo = ();
      foreach my $k (sort keys %{$servicios{$id}}){
            foreach my $kk (sort keys %{$servicios{$id}{$k}}){
                  my $tip                     = $servicios{$id}{$k}{$kk}[3];
                  my $cod                     = $servicios{$id}{$k}{$kk}[4];
                  ($tar_cod,$base_imponible)  = get_base_imponible($id,$k,$kk,$tip,$cod);
                  $cargo{$id}{$tar_cod}{'VAL'} = sprintf("%.2f",$base_imponible); 
                  $cargo{$id}{$tar_cod}{'FIE'} = get_fiesta($tar_cod,$base_imponible);
            }
      }
#      print "CARGOS<br>\n";      
#      foreach $k (sort keys %cargo){
#	    foreach $kk (sort keys %{$cargo{$k}}){
#		  foreach $kkk (sort keys %{$cargo{$k}{$kk}}){  
#			print "$k=>$kk=>$kkk=>$cargo{$k}{$kk}{$kkk}<br>\n";
#		  }
#	    }
#	    print "\n";	    
#      }      
      return %cargo;
}
sub delete_cargo_from_database {
      my ($id)=@_;
      my $query    = "DELETE FROM sdd_".$clase."_mensualidades WHERE i8_".$clase."_id='$id' and i8_periodo='$i8_periodo'";
      #print "$query<br>\n";
      if ( $dbh->Sql($query) ){SQL_fail($query,$dbh,'sub delete_cargo')}
}
sub insert_cargo_into_database {
      my ($id)          = @_;
      my $table		= "sdd_".$clase."_mensualidades";
      my $insert_fields	= "i8_".$clase."_id,i8_periodo,i4_dias_mora,c16_tarifa_id,d82_val,d82_fie,d82_int,d82_mul";    
      foreach $kk (sort keys %{$cargos{$id}}){
            my $val   = $cargos{$id}{$kk}{'VAL'};
            my $fie   = $cargos{$id}{$kk}{'FIE'};
            if($val ne ""){
                  my $query = "INSERT INTO $table ($insert_fields) VALUES ('$id','$i8_periodo','0','$kk',$val,$fie,0,0)";
                  #print "$query<br>\n";
                  #print ".";
                  if ( $dbh->Sql($query) ){SQL_fail($query,$dbh,'sub insert_cargo')}
            }
      }
}
sub construct_page {
      open (DATA, "$PATH$FILE");
      while ($line = <DATA>){
	    $line =~ s/{lan}/$lan/g;
	    $line =~ s/{appid}/$appid/g;
	    $line =~ s/{periodo}/$i8_periodo/g;
            $line =~ s/{directorio}/$dir/g;
	    print "$line";
      }
}
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#SUB-ROUTINES -> LEVEL 2
sub get_base_imponible {
      my ($id,$gr,$trb,$tip,$cod)= @_;
      my $imponible  = 0;
      my $tariff_cod = "";
      foreach $l (sort keys %{$tarifas{$trb}{$tip}{$cod}{'M'}}){
            $tariff_cod = $tarifas{$trb}{$tip}{$cod}{'M'}{$l}[0];
            if ($l eq "F"){$imponible  = $tarifas{$trb}{$tip}{$cod}{'M'}{$l}[6];}
            if ($l eq "C"){$imponible = get_tarifa_detalle_valor($tariff_cod,$id,$gr);}
      }
      return ($tariff_cod,$imponible);
}
sub get_fiesta {
    my ($tarifa,$base_imponible)= @_;
    my $fiesta= 0;
    my $trib  = substr($tarifa,0,8);
    my $desde = substr($accesorios{$trib}{'ACINMFIE'}[2],0,7);
    my $hasta = substr($accesorios{$trib}{'ACINMFIE'}[3],0,7);
    $desde    =~ s/\-//g;
    $hasta    =~ s/\-//g;
    #~print "$i8_periodo<>$desde<>$hasta<br>";
    if($i8_periodo>=$desde && $i8_periodo<=$hasta){
            $fiesta = sprintf("%.2f",$base_imponible*0.05);
    }
    return $fiesta;
}
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#SUB-ROUTINES -> LEVEL 3
sub get_tarifa_detalle_valor {
      my ($tarifa,$id,$gr)  = @_;
      my $val_trib  = 0;
      my $imponible = 0;
      my $valor     = 0;
      #HASTA ACA ESTA REVISADO, CONTINUAR AQUI     
      if($tarifas_d{$tarifa}[1] eq "BXV"){
            $tbp   = $tarifas_d{$tarifa}[2];
            $valor = $tarifas_d{$tarifa}[4];
            #print "TBP:$tbp<>BI:$bases{$id}{$gr}{$tbp}[3]<>DD:$bases{$id}{$gr}{$tbp}[4]<>$valor<br>";
            $imponible = $bases{$id}{$gr}{$tbp}[3]-$bases{$id}{$gr}{$tbp}[4];
            $val_trib += $imponible*$valor;
            if($rango_minimo>0){if($val_trib<$rango_minimo){$val_trib=$rango_minimo}}
            if($rango_minimo>0){if($val_trib>$rango_maximo){$val_trib=$rango_maximo}}
      }
      if($tarifas_d{$tarifa}[1] eq "BVT"){
            $tbp = $tarifas_d{$tarifa}[2];
            $valor = $tarifas_d{$tarifa}[4];             
            $imponible = $bases{$id}{$gr}{$tbp}[3]-$bases{$id}{$gr}{$tbp}[4];            
            my $calculo = $imponible*$valor;
            if($calculo > $rango_maximo){
                  $calculo = $rango_maximo;
            }
            $val_trib += $calculo;
      }
      if($tarifas_d{$tarifa}[1] eq "BME"){
            $tbp = $tarifas_d{$tarifa}[2];
            $valor = $tarifas_d{$tarifa}[4];             
            $imponible = $bases{$id}{$gr}{$tbp}[3]-$bases{$id}{$gr}{$tbp}[4];            
            if ($imponible>$rango_maximo){
                  if($excendente ne ""){
                        if($divisor==0 || $divisor eq ""){$divisor=1}
                        $val_trib += $valor+((($imponible-$rango_maximo)/$divisor)*$excendente);                                                      
                  }
            }else{
                  $val_trib += $valor;
            }
      }
      if($tarifas_d{$tarifa}[1] eq "CR"){
            $tbp = $tarifas_d{$tarifa}[2];
            $valor = $tarifas_d{$tarifa}[4];             
            $imponible = $bases{$id}{$gr}{$tbp}[3]-$bases{$id}{$gr}{$tbp}[4];            
            if ($imponible>=$rango_minimo && $imponible<=$rango_maximo){
                  if($excendente ne ""){
                        if($divisor==0){$divisor=1}
                        my $resultado = (($imponible-$rango_minimo)/$divisor);
                        my $entero    = int($resultado);
                        my $decimal   = sprintf("%.2f", ($resultado - int($resultado)));
                        if ($decimal>.00){$entero++}
                        $val_trib += $valor+($entero*$excendente);
                  }else{
                        $val_trib += $valor;
                  }
            }
      }
      return $val_trib;
}
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#AUXILIAR-SUBROUTINES
sub SQLfail {
    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;
}
#-------------------------------------------------------------------------