#!/perl/bin/perl.exe
$|=1;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# Author:Rodolfo Machon
# Company:WebSoft, SA de CV 
# Nombre del archivo: recibo.id.check.pl
# Created: 2016-08-07
# Updates: 2016-08-07
# Copyright - All Rights Reserved
#------------------------------------------------------------------------- 
#-------------------------------------------------------------------------
# PERL MODULES
use CGI;
use CGI::Carp qw(fatalsToBrowser);
use DBI;
use XML::Simple;
use Data::Dumper;
use Encode;
use Encode qw/is_utf8 decode/;
#------------------------------------------------------------------------- 
#-------------------------------------------------------------------------
# HTML PRINTING ACTIVATION
print "Content-type: text/html; charset=utf-8\n\n";
#------------------------------------------------------------------------- 
#-------------------------------------------------------------------------
# GET PARAMETERS
$cgiparms 				= new CGI;
$clase      			= $cgiparms->param('clase');
$sid 					= $cgiparms->param('sid');#source_id
$tid 					= $cgiparms->param('tid');#target_id
$master_insert_values	= $cgiparms->param("miv");
$detail_insert_values	= $cgiparms->param("div");
#$exist			= $cgiparms->param("exist");
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# GET CLASE SCHEMA AND VARIABLES
get_app_data();
get_component_schema($clase);
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# 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";}
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# MAIN PROGRAM
# Master Section
insert_records('master',$data->{master}->{target_table},$data->{master}->{target_insert_fields},$master_insert_values,$data->{master}->{target_autoincrement});

insert_records('detail',$data->{detail}->{target_table},$data->{detail}->{target_insert_grid_order},$detail_insert_values,$data->{detail}->{target_autoincrement});
construct_page();

exit;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# SUB-ROUTINES
sub insert_records {
	($section,$table,$insert_fields,$insert_values,$autoincrement) = @_;
	#print "$section,$table,$insert_fields,$insert_values,$autoincrement";
	if ($section eq "master" && $autoincrement eq "YES"){
		my $query_temp 	= "INSERT INTO $table (ts_in) VALUES (now())";
		$dbh->do($query_temp);

		my $sql = "SELECT LAST_INSERT_ID()";
		my $sth = $dbh->prepare($sql);
		$sth->execute or die SQL_fail($sql,$dbh,'sub insert_records');
		while (my @row = $sth->fetchrow_array) {$tid=$row[0]}

		update_records($section,$table,$insert_fields,$insert_values);		
	}
	if ($section eq "detail" || ($section eq "master" && $autoincrement eq "NO")){	
		$columns	= "";
		$id_field  	= $data->{master}->{target_id_field};
		@fields 	= split(',',$insert_fields);	
		$fields_amount	= scalar(@fields);
		for(my $i=0;$i<$fields_amount;$i++){$columns = $columns.$fields[$i].",";}
		if($section eq "detail"){
			$columns = $columns.$id_field;
		}else{$columns=substr($columns,0,length($columns)-1)}
		@records	= split(/~~/,"$insert_values");
		$records_amount	= scalar(@records);
		for ($i=0;$i<$records_amount;$i++){
			$values			="";
			@records_values	= split(/\|/,"$records[$i]");	
			for ($j=0;$j<$fields_amount;$j++){
				$records_values[$j] = decode_utf8($records_values[$j]);
				$records_values[$j] = encode("iso-8859-1",$records_values[$j]);				
				if ($fields[$j] eq "ts_in"){$records_values[$j]="now()"}
				if (substr($fields[$j],0,1) eq "f" || substr($fields[$j],0,1) eq "i" || substr($fields[$j],0,2) eq "ts" || substr($fields[$j],0,2) eq "d8"){
					if($records_values[$j] ne ""){$values=$values.$records_values[$j].","}else{$values=$values."NULL,"}
				}else {
					if($records_values[$j] eq "" && substr($fields[$j],0,2) eq "dt" ){
						$values=$values."NULL,";
					}else{
						$values=$values."'".$records_values[$j]."',";
					}
				}
			}
			if($section eq "detail"){
				if (substr($id_field,0,1) eq "f" || substr($fields[$j],0,1) eq "i" || substr($id_field,0,2) eq "ts"){
					$values=$values.$tid;
				}else{$values=$values."'".$tid."'"}
			}else{$values=substr($values,0,length($values)-1)}
			$values =~ s/(\d{2})-(\d{2})-(\d{4})/$3$1$2$more_time/g;
			
			my $query = "INSERT INTO $table ($columns) VALUES ($values)";
			#print "$query";
			$dbh->do($query);
		}
	}
}
sub update_records {
	($section,$table,$update_fields,$update_values) = @_;
	if ($section eq "master"){$wheres="";$wheres_values="";}else{$wheres=$detail_update_wheres;$wheres_values=$detail_update_wheres;}		
	@fields 		= split(',',$update_fields);	
	$fields_amount	= scalar(@fields);
	$id_field  		= $data->{master}->{target_id_field};
	@records		= split(/~~/,"$update_values");
	$records_amount = scalar(@records);
	for ($i=0;$i<$records_amount;$i++){
		@record_values = split(/\|/,"$records[$i]");	
		for ($j=0;$j<$fields_amount;$j++){
			my $val = decode_utf8($record_values[$j]);
			$val = encode("iso-8859-1",$val);			
			if ($record_values[$j] ne ""){
				if (substr($fields[$j],0,1) eq "i" || substr($fields[$j],0,1) eq "f" || substr($fields[$j],0,2) eq "ts"){
					$set=$set.$fields[$j]."=".$val.",";
				}else {
					if($val eq "" && substr($fields[$j],0,2) eq "dt" ){
						$values=$values."NULL,";
						$set =$set.$fields[$j]."=NULL,";						
					}else{
						$set=$set.$fields[$j]."='".$val."'".",";
					}
				}
			}
		}
		$len=length($set);
		$set=substr($set,0,$len-1);		
		$set=~ s/(\d{2})-(\d{2})-(\d{4})/$3$1$2$more_time/g;

		my $query = "UPDATE $table SET $set WHERE $id_field='$tid'";
		$dbh->do($query);
	}
}
sub delete_records {
	($section,$table,$delete_field,$delete_value) = @_;
	my $query = "DELETE FROM $table WHERE $delete_field='$delete_value'";
	$dbh->do($query);
	
}
sub construct_page {
	print "target_id=$tid";
}
#-------------------------------------------------------------------------
#Auxiliar Sub-Routines
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 get_component_schema {
    my ($cmp)=@_;
    my $schema     = $cmp."_qmd.xml";
    my $xml        = new XML::Simple;
    $data 	= $xml->XMLin("../xml/".$schema);
    $more_time	= "000000";
}
sub SQL_fail {
    my ($query,$dbh,$section)= @_;
    print "SQL failed in Section $section\n";
    print "Query: ".$query."\n";    
    exit;
}
#-------------------------------------------------------------------------
