#!/perl/bin/perl.exe
$|=1;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# Author:Rodolfo Machon
# Company:WebSoft, SA de CV 
# Nombre del archivo: upwsp.pl
# Created: 2006-07-13,2016-08-03
# Updates: 2006-07-13, 2011-06-21,2016-08-03
# Copyright - All Rights Reserved
#------------------------------------------------------------------------- 
#-------------------------------------------------------------------------
# PERL MODULES
use CGI;
use CGI::Carp qw(fatalsToBrowser);
use DBI;
use XML::Simple;
use Data::Dumper;
binmode STDOUT, ":encoding(utf8)";
#------------------------------------------------------------------------- 
#-------------------------------------------------------------------------
# HTML PRINTING ACTIVATION
print "Content-type: text/html; charset=utf-8\n\n";
#------------------------------------------------------------------------- 
#-------------------------------------------------------------------------
#GET PARAMETERS
$cgiparms 	    = new CGI;
$lan	 	    = $cgiparms->param('lan');
$appid	 	    = $cgiparms->param('appid');
$clase      	= $cgiparms->param('clase');
$archivo        = $cgiparms->param('archivo');
$nombre_archivo = $cgiparms->param('nombre_archivo');
$custom_fields	= $cgiparms->param('custom_fields');
$custom_values	= $cgiparms->param('custom_values');
#-------------------------------------------------------------------------
#print "$lan>$appid>$clase>$nombre_archivo>$archivo <br>";
#-------------------------------------------------------------------------
# GET CLASE SCHEMA AND VARIABLES
get_component_schema($clase);
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# 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>";}
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# OPEN DATABASE CONNECTIONS - ODBCs
$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;
$dbh1 	= DBI->connect("dbi:ODBC:$appid",'','',{PrintError => 1, RaiseError => 1});
if (!$dbh1) {print "error: connection: $DBI::err\n$DBI::errstr\n$DBI::state\n";}
$dbh1->{LongReadLen} = 512 * 1024;
#------------------------------------------------------------------------- 
#-------------------------------------------------------------------------
#MAIN PROGRAM
%sd   = ();
%data = ();
upload_file();
get_stream_definition();
parse_file();
delete_data();
insert_data();
run_processes();
construct_page();
exit;
#-------------------------------------------------------------------
# SUB-ROUTINES
sub upload_file{
    $file=$archivo;
    $file=~m/^.*(\\|\/)(.*)/; # strip the remote path and keep the filename
    $file_name=$dir.$nombre_archivo.".txt";
    #print "$file_name <br>";
    open("LOCAL",">".$file_name);
    while(<$file>){
        print LOCAL $_;
    }
    close(LOCAL);
}
sub get_stream_definition{
    my $database    	     = $data->{stream_definition}->{database};
    my $table	    	     = $data->{stream_definition}->{table};
    my $key	    	         = $data->{stream_definition}->{key};
    my $key_value    	     = $data->{stream_definition}->{key_value};    
    my $field_order 	     = $data->{stream_definition}->{field_order};
    my $field_name  	     = $data->{stream_definition}->{field_name};
    my $field_type  	     = $data->{stream_definition}->{field_type};
    my $field_ins_del  	     = $data->{stream_definition}->{field_ins_del};    
    my $field_value  	     = $data->{stream_definition}->{field_value};    
    my $field_length  	     = $data->{stream_definition}->{field_length};
    my $field_delimiter_type = $data->{stream_definition}->{field_delimiter_type};
    my $field_delimiter      = $data->{stream_definition}->{field_delimiter};
    my $sql = "SELECT $field_order,$field_name,$field_type,$field_value,$field_length,$field_ins_del FROM $table WHERE $key='$key_value' ORDER BY $field_order";
    #print "$sql<br>";
	my $sth = $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_stream_definition');
	while (my @row = $sth->fetchrow_array) {
        $sd{$row[0]}=[@row];	
    }
}
sub parse_file{
    my $i=0;
    my $archivo  = $nombre_archivo.".txt";
    open(A,"$dir$archivo");
    while($line = <A>) {
        $line =~ s/^\s*(.*\S)\s*$/$1/;
        my $desde = 0;
        foreach $k(sort numeros keys %sd){
            if($sd{$k}[2] eq "FIELD"){$data{$i}{$sd{$k}[1]}=substr($line,$desde,$sd{$k}[4]);}
            if($sd{$k}[2] eq "VALUE"){$data{$i}{$sd{$k}[3]}=substr($line,$desde,$sd{$k}[4]);}
            $desde += $sd{$k}[4];
        }
        $i++;
    }
}
sub delete_data{
    my $where   = "";
    my $database= $data->{clase}->{database};
    my $table   = $data->{clase}->{table};
	my $sql     = "delete from $table where v128_nombre_archivo='$nombre_archivo'";
    $dbh->do($sql);
    #foreach $k(sort numeros keys %data){
    #    foreach $kk(sort keys %{$data{$k}}){
    #        foreach $l(sort numeros keys %sd){
    #            if($sd{$l}[2] eq "VALUE" && $sd{$l}[3] eq $kk){
    #                my $where   = get_where_field_value($l,$k,$kk,$data{$k}{$kk});
    #                my $sql     = "delete from $table where $where";
    #                $dbh->do($sql);
    #            }
    #        }
    #    }
    #}
}
sub insert_data{
    my $where   	= "";
    my $fields		= "";
    my $values		= "";
    my $database	= $data->{clase}->{database};
    my $table 		= $data->{clase}->{table};
    my $insert_fields 	= $data->{clase}->{insert_fields};    
    my $dv		= $data->{stream_definition}->{descriminated_values};    
    foreach $k(sort numeros keys %data){
        foreach $kk(sort keys %{$data{$k}}){
            foreach $l(sort numeros keys %sd){
                if($sd{$l}[2] eq "VALUE" && $sd{$l}[3] eq $kk){
                    if($data{$k}{$kk} ne $dv){
                        ($fields,$values) = get_values($sd{$l}[1],$k,$kk,$data{$k}{$kk});
                        my $sql="insert into $table($fields,v128_nombre_archivo) values($values,'$nombre_archivo')";
                        print "";
                        $dbh->do($sql);
                    }
                }
            }
        }
    }    
}
sub run_processes {
    my @processes = split(',',$data->{processes});
    for(my $i=0;$i<=$#processes;$i++){
        my $process_type = $data->{process}->{$processes[$i]}->{type};
        if($process_type eq "function"){my $function = $processes[$i]."()";eval($function);}
        if($process_type eq "script"){
            my $path   		= $data->{process}->{$processes[$i]}->{script_path};
            my $file   		= $data->{process}->{$processes[$i]}->{script_file};
            my $interpreter	= $data->{process}->{$processes[$i]}->{script_interpreter};
            my $interpreter_path= $data->{process}->{$processes[$i]}->{script_interpreter_path};		    
            my $script 		= "\"$interpreter_path$interpreter\" \"$path$file\" \"$lan\" \"$appid\" \"$clase\" \"$custom_fields\" \"$custom_values\" >>garbage.txt";
            #print "$script";
            system("$script");
        }
        if($process_type eq "bin"){}
    }
}
sub construct_page {
    my $schema = "<p><br><center>Data se Inserto Correctamente<center><br> lan='{lan}' appid='{appid}' clase='{clase}'</p>";
    $line = $schema;
    $line =~ s/{lan}/$lan/g;
    $line =~ s/{appid}/$appid/g;
    $line =~ s/{clase}/$clase/g;
    print $line;
}
#-------------------------------------------------------------------
#AUXILIAR-SUBROUTINES
sub get_component_schema {
    ($cmp)=@_;
    $schema       = $cmp."_upws.xml";
    $xml          = new XML::Simple;
    $data 	      = $xml->XMLin("../xml/".$schema);
    $upload_path  = $data->{upload_path};
    $dir	      = $upload_path;
}
sub numeros{$a<=>$b}
sub get_where_field_value {
    my ($fo,$z,$zz,$data_value) = @_;
    my %sdt		= ();
    my $where		= "";
    my @splt 		= split(/\,/,$data->{clase}->{delete_fields});
    my $key		= $data->{stream_definition}->{key};
    my $key_value	= $data->{stream_definition}->{key_value};
    my $ws		= "$key = '$key_value' and "."$sd{$fo}[1]='$zz' and ";
    for (my $i=0;$i<=$#splt;$i++){$sdt{$splt[$i]} = $splt[$i];}
    foreach $x(sort numeros keys %sd){	
	if($sd{$x}[2] eq "FIELD"){
	    foreach $y(sort keys %{$data{$z}}){
		if($sd{$x}[1] eq $y){
		    for (my $i=0;$i<=$#splt;$i++){
			if($sd{$x}[1] eq $sdt{$splt[$i]} && $sd{$x}[5] eq "SI" ){
			    $ws .= "$y='$data{$z}{$y}' and ";
			}
		    }
		}
	    }
	}	    
    }
    $where = substr($ws,0,length($ws)-4);
    #print "$where<br>";
    return $where;	
}
sub get_values {
    my ($f,$z,$zz,$data_value) = @_;
    my %sdt		    = ();
    my %ivt		    = ();
    my $values		= "";
    my @cf  		= split(/\,/,$custom_fields);
    my @cv  		= split(/\,/,$custom_values);    
    my @splt 		= split(/\,/,$data->{clase}->{insert_fields});
    my $key		    = $data->{stream_definition}->{key};
    my $key_value	= $data->{stream_definition}->{key_value};
    my $value_field	= $data->{stream_definition}->{value_field};    
    my $vals		= "";
    my $fs		    = "";
    $ivt{$key}		= $key_value,    
    $ivt{$f} 		= $zz;
    $ivt{$value_field}  = $data_value;
    for (my $i=0;$i<=$#splt;$i++){$sdt{$splt[$i]} = $splt[$i];}
    for (my $i=0;$i<=$#cf;$i++){$ivt{$cf[$i]} = $cv[$i];}
    foreach $w(sort keys %sdt){    
        foreach $x(sort numeros keys %sd){
            if($sd{$x}[1] eq $w){
                foreach $y(sort keys %{$data{$z}}){
                    if($sd{$x}[1] eq $y){
                        if($sd{$x}[5] eq "SI" ){
                            $ivt{$y} = $data{$z}{$y}
                        }
                    }
                }
            }	    
        }
    }
    foreach $v(sort keys %ivt){
	$fs	.= "$v,";
	$vals 	.= "'$ivt{$v}',";
    }
    $fs = substr($fs,0,length($fs)-1);    
    $values = substr($vals,0,length($vals)-1);
    #print "$fs<br>$values<br>";
    return ($fs,$values);	
}
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;
}
#-------------------------------------------------------------------