#!/perl/bin/perl.exe
$|=1;
#--------------------------------------------------------------------------------
#--------------------------------------------------------------------------------
# Information Software SA
# Date In: 2012-12-09
# Updates: 2012-12-09
# Author:Rodolfo Machon
# File Name: mora_cartera_gestor_guardar.pl
#------------------------------------------------------------------------- 
#-------------------------------------------------------------------------
# PERL MODULES
use CGI;
use CGI::Carp qw/fatalsToBrowser warningsToBrowser/;
use DBI;
use XML::Simple;
binmode STDOUT, ":encoding(utf8)";
#------------------------------------------------------------------------- 
#-------------------------------------------------------------------------
# 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');
$str	       = $cgiparms->param('str_expedientes');
$str1	       = $cgiparms->param('str_expedientes1');
$c11_sector_id = $cgiparms->param('c11_sector_id');
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# CHECK PARAMETERS
if ($lan eq ""){print "Your are not passing LAN parameter<br>";}
if ($appid eq ""){print "Your are not passing APPID parameter<br>";}
#--------------------------------------------------------------------------
#--------------------------------------------------------------------------
# OPEN DATABASE CONNECTIONS - ODBCs
get_app_data();
open_database_connections();
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# GLBAL VARIABLES
$PATH	="../libraries/html/";
$file 	= "msg_processing.html";
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# PROGRAM DESCRIPTION
# PROCUREMENT
$today		= get_fecha();
if($str eq ""){update_gestores();construct_page();exit}
($uid,$uf,$ul)  = get_user();
%expedientes    = get_expedientes();
%gestor 	= get_gestor();
# INTEGRATION
delete_gestores();
insert_gestores();
# PRESENTATION
construct_page();
exit;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#SUB-ROUTINES -> LEVEL 1
sub get_fecha {
	my $fec     = "";
	my $sql   = "SELECT DATE_FORMAT(CURDATE(),'%Y-%m-%d')";
	my $sth = $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_');
	while (my @row = $sth->fetchrow_array) {
		$fec = $row[0];
	}	
	return $fec;	
}
sub get_user {
	my $uid   = "";
	my $uf   = "";
	my $ul   = "";	
    my $user  = $ENV{"REMOTE_USER"};
	my $sql = "SELECT a.i8_user_id,a.`v32_fname`,a.`v32_lname` FROM sdm_users a where a.`v16_login`='$user'";
	my $sth = $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_');
	while (my @row = $sth->fetchrow_array) {
		$uid = $row[0];
		$uf  = $row[1];
		$ul  = $row[2];
	}
	return $uid,$uf,$ul;
}
sub get_expedientes {
	my %hash = ();
	my @expedientes = split(/\~/,$str);
	for($i=0;$i<=$#expedientes;$i++) {
		my @exp = split(/\|/,$expedientes[$i]);
		$hash{$exp[0]} = $exp[1];
		#print $hash{$exp[0]}."<br>";
	}
	return %hash;
}
sub get_gestor {
	my %hash = ();	
	my $str  = "";
    foreach my $k (sort keys %expedientes){$str .= $expedientes{$k}.",";}
	$str = substr($str,0,length($str)-1);
	my $sql = "SELECT a.i8_user_id,a.v32_fname,a.v32_lname
		     FROM sdm_users a
		     WHERE a.i8_user_id IN ($str)
		     ORDER BY a.i8_user_id";
	my $sth = $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_');
	while (my @row = $sth->fetchrow_array) {
	    $hash{$row[0]} = [@row];
	}
	return %hash;
}
sub delete_gestores {
	foreach my $k (sort keys %expedientes){
		my $sql = "DELETE FROM sdd_".$clase."_gestores WHERE i8_".$clase."_id='".$k."' and i8_gestor_id='".$expedientes{$k}."'";
		$dbh->do($sql);
	}
}
sub insert_gestores {
	foreach my $k (sort keys %expedientes){
		my $gid = $gestor{$expedientes{$k}}[0];
		my $gf  = $gestor{$expedientes{$k}}[1];
		my $gl  = $gestor{$expedientes{$k}}[2];		
		my $sql = "INSERT INTO sdd_".$clase."_gestores
			     (i8_".$clase."_id,c2_tipo_gestor_id,i8_gestor_id,v64_nombres_gestor,v64_apellidos_gestor,
			     c8_estado,dt_activacion,i8_asignador_id,v64_nombres_asignador,v64_apellidos_asignador,ts_in)
			     VALUES ('$k','IN','$gid','$gf','$gl','ACTIVO','$today','$uid','$uf','$ul',now())";
		$dbh->do($sql);
	}
}
sub update_gestores {
	my %hash = ();
	my @expedientes = split(/\~/,$str1);
	for($i=0;$i<=$#expedientes;$i++) {
		my @exp = split(/\|/,$expedientes[$i]);
		$hash{$exp[0]} = $exp[1];
	}
	foreach my $k (sort keys %hash){
		my $sql = "UPDATE sdd_".$clase."_gestores SET c8_estado='INACTIVO',dt_inactivacion='$today' WHERE i8_".$clase."_id='".$k."' and i8_gestor_id='".$hash{$k}."'";
		$dbh->do($sql);
	}
}
sub construct_page {
    $data= "mora_cartera_sector_expedientes_p.pl?clase=$clase&c11_sector_id=$c11_sector_id";
    open (DATA, "$PATH$file");
    while ($line = <DATA>) {
	    $line =~ s/{CGI}/$data/g;
	    $line =~ s/{message}/Procesando Por Favor Espere.../g;
	    print $line;
    }        
}
#-------------------------------------------------------------------------
#AUXILIAR-SUBROUTINES
sub numeros {$a<=>$b}
sub get_app_data {
    my $schema  = "app.data.xml";
    my $xml     = new XML::Simple;
    my $data    = $xml->XMLin("../xml/".$schema);
    $appid	  = $data->{portal}->{app};
    $lan	  = $data->{portal}->{lang};
}
sub open_database_connections {
	$dbh 	= DBI->connect("dbi:ODBC:$appid",'','',{PrintError => 1, RaiseError => 1});
	if (!$dbh) {print "error: connection: $DBI::err\n$DBI::errstr\n$DBI::state\n";}
	$dbh->{LongReadLen} = 512 * 1024;	
}
sub SQL_fail {
    my ($query,$dbh,$section)= @_;
    print "SQL failed in Section $section<br>";
    print "Query: ".$query."<br>";    
    exit;
}
#-------------------------------------------------------------------------