#!/perl/bin/perl.exe
$|=1;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# Information Software SA
# Date In: 02-02-2005
# Updates: 02-03-2005,2018-06-13
# Author:Rodolfo Machon
# File Name: mdf.pl
#------------------------------------------------------------------------- 
#-------------------------------------------------------------------------
#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');
$id 			= $cgiparms->param('id');
$select_fields	= $cgiparms->param('select_fields');
$labels_fields	= $cgiparms->param('labels_fields');
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# 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>";}
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# GET CLASE SCHEMA AND VARIABLES
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";}
$dbh->{LongReadLen} = 512 * 1024;
$dbh->{LongTruncOk} = 1;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# Main Program
($v16_login,$v32_fnames,$v32_lnames,$i8_user_id) = get_user();
$today			= get_fecha();
$todate			= get_date();
$hora			= get_time();
$combo_fields	= $data->{master}->{select_fields};
%combos			= get_combos($combo_fields);
if($id ne ""){
	$select_order	= get_select_order($select_fields);
	$where_statement= get_where_statement('i8_mandamiento_pago_id',$id);	
	%record			= get_record($select_fields,$select_order,$table,$where_statement);	
	%record_labels	= get_record_labels($labels_fields,%record);
	%documents		= get_documents();
}
construct_page();
exit;
#-------------------------------------------------------------------
#-------------------------------------------------------------------
#Main Sub-Routines
sub get_user {
	my $nombres    = "";
	my $apellidos  = "";
	my $user_id    = "";	
    local($login)  = $ENV{"REMOTE_USER"};
	
	my $sql = "select a.v32_fname,a.v32_lname,a.i8_user_id from sdm_users a where a.v16_login='$login'";
	my $sth = $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_users');
	if (my @row = $sth->fetchrow_array) {
	      $nombres   = $row[0];
	      $apellidos = $row[1];
	      $user_id 	 = $row[2];		  
	}
    return($login,$nombres,$apellidos,$user_id);
}
sub get_combos {
	my ($cmb_fields)=@_;
	my @fields = split(/\,/,$cmb_fields);
	for (my $i=0;$i<=$#fields;$i++){
		if ($data->{master}->{field}->{$fields[$i]}->{element_type} eq "combo"){		
			$cmb{$fields[$i]} = construct_combo($data->{master}->{field}->{$fields[$i]}->{database},$data->{master}->{field}->{$fields[$i]}->{table},$fields[$i],$data->{master}->{field}->{$fields[$i]}->{fields},'no_value');	
		}
	}
	return %cmb;
}
sub get_select_order {
	my ($select_fields)=@_;
	$select_order="";
	@displayed_fields = split(/\,/,$select_fields);
	for ($w=0;$w<=$#displayed_fields;$w++){
		$fields[$w]= substr($displayed_fields[$w],5,length($displayed_fields[$w])-5);
		$timestamp= substr($fields[$w],0,2);			
		if ($timestamp eq "ts"){
			$fields[$w] = "unix_timestamp(".$fields[$w].")";
		}
		if ( $#displayed_fields > $w ){
			$select_order = $select_order.$fields[$w].",";
		}else{
			$select_order = $select_order.$fields[$w];
		}
	}
	return $select_order;
}
sub get_where_statement {
	my ($where_field,$where_value)=@_;
	$where_statement = "WHERE $where_field=$where_value";
}
sub get_record {
	my ($select_fields,$select_order,$table,$where_statement)=@_;
	@fields = split(/\,/,$select_fields);
	for ($i=0;$i<=$#fields;$i++){$fields[$i]= substr($fields[$i],5,length($fields[$i])-5);}

	my $query="SELECT $select_order FROM $table $where_statement ORDER BY 1";
	my $sth = $dbh->prepare($query);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_record');
	if (my @row = $sth->fetchrow_array) {
		for($i=0;$i<=$#fields;$i++){
			if(substr($fields[$i],0,2) eq "ts"){
				$record{$fields[$i]}= standard_timestamp($row[$i]);
			}else{
				$record{$fields[$i]}=$row[$i];
			}
		}
	}
	return %record;
}
sub get_record_labels {
	my ($labels,%record)=@_;
	@lt = split(/~~/,$labels);
	$j=0;
	foreach $key(keys %record) {
		for($i=0;$i<=$#lt;$i++){
			@label_fields	= split(/\|/,$lt[$i]);
			$field_id 	= $label_fields[2];
			$ky = $key;
			if($key eq "i8_client_id"){$ky="i8_contribuyente_id";$i8_contribuyente_id=$record{$key}}
			if ($ky eq $field_id){
				%rl = get_labels($dbh,$label_fields[1],$label_fields[2],$label_fields[3],$label_fields[4],$record{$key});
				$all_labels{$j} = {%rl};
				$j++;
			}		
		}
	}
	return %all_labels;
}
sub get_labels {
	($where_statement)=@_;
	($dbh,$table,$field_id,$table_fields,$form_fields,$id)=@_;    
	@fields = split(/\,/,$form_fields);

	my $sql="SELECT $table_fields FROM $table WHERE $field_id=$id ORDER BY $field_id";
	my $sth = $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_labels');
	while (my @row = $sth->fetchrow_array) {
		for($i=0;$i<=$#fields;$i++){
			if(substr($fields[$i],0,2) eq "ts"){
				$labels{$fields[$i]}= standard_timestamp($row[$i]);
			}else{
				$labels{$fields[$i]}=$row[$i];
			}
		}
	}
	$dbh->Close();
	return %labels;
}
sub get_documents {
	$docs{'c11_dui_id'}="";$docs{'c14_nit_id'}="";$docs{'c11_iva_id'}="";
	my $sql="SELECT c16_documento_id,i2_tipo_documento_id FRM sdd_contribuyentes ORDER BY 1";
	my $sth 	   = $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_documents');
	while (my @row = $sth->fetchrow_array) {
		if($row[1] eq "1"){$docs{'c11_dui_id'}=$row[0];}elsif($row[1] eq "2"){$docs{'c14_nit_id'}=$row[0];}elsif($row[1] eq "3"){$docs{'c11_iva_id'}=$row[0];}
		
	}
	return %docs;
}
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/{width}/$data->{width}/g;
		$line =~ s/{height}/$data->{height}/g;
		$line =~ s/{today}/$today/g;
		$line =~ s/{todate}/$todate/g;
		$line =~ s/{time}/$hora/g;
		$line =~ s/{v16_login}/$v16_login/g;
		$line =~ s/{i8_user_id}/$i8_user_id/g;		
		$line =~ s/{v16_login}/$v16_login/g;
		$line =~ s/{v32_fnames}/$v32_fnames/g;
		$line =~ s/{v32_lnames}/$v32_lnames/g;

		foreach $key(keys %combos){$line =~ s/{$key}/$combos{$key}/g}
		foreach $key(keys %record){$line =~ s/{$key}/$record{$key}/g}
		foreach $key(keys %documents){$line =~ s/{$key}/$documents{$key}/g}

		foreach $key(keys %record_labels){
			foreach $key1(keys %{$record_labels{$key}}){
				$line =~ s/{$key1}/$record_labels{$key}{$key1}/g;
			}		
		}
		print "$line";
	}
}
#-------------------------------------------------------------------
#AUXILIAR-SUBROUTINES
sub get_component_schema {
    ($cmp)=@_;
    $schema       = $cmp."_mdfx.xml";
    $xml          = new XML::Simple;
    $data 	  = $xml->XMLin("../xml/".$schema);
    $path	  = $data->{base_path}."/".$lan."/";
    $file	  = $lan."_".$data->{html_file}."_mdf.html";
    $country	  = "SV";    
}
sub SQL_fail {
    my ($query,$dbh,$section)= @_;
    print "SQL failed in Section $section<br>";
    print "Query: ".$query."<br>";    
    exit;
}
sub connect_fail {
    my ($DSN)=@_;
    print "Error connecting to $DSN<br>";
    exit;
}
sub standard_timestamp {
	my ($unix_timestamp)=@_;
	($s,$m,$h,$d,$mo,$y,$w,$yd,$is)	= localtime($unix_timestamp);
	$mo=$mo+1;
	$y=$y+1900;
	$mo1=length($mo);
	if ($mo1 == 1){$mo="0".$mo;}
	$d1=length($d);	if ($d1 == 1){$d="0".$d;}
	$standard_timestamp = "$mo\-$d\-$y";
	return $standard_timestamp;
}
sub construct_combo {
	my ($db,$table,$option_value_field,$option_caption_field,$selected_option)=@_;
	my $combo = "";
	my $trc	  = "<option value={option_value} {selected}>{option_caption}</option>";
	my $sql = "SELECT $option_value_field,$option_caption_field FROM $table ORDER BY 1";
	my $sth 	   = $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub construct_combo');
	while (my @row_c = $sth->fetchrow_array) {
		my $trc_line = $trc;
		$trc_line =~ s/{option_value}/$row_c[0]/g;
		$trc_line =~ s/{option_caption}/$row_c[1]/g;
		if ($selected_option==$row_c[0]){$trc_line =~ s/{selected}/selected/g;}else{$trc_line =~ s/{selected}//g;}
		$combo = "$combo"."\n"."$trc_line";
	}
	return $combo;
}
sub get_fecha {
	my $fec  = "";
	my $cloc = "";
	
	my $sql	= "SELECT \@\@lc_time_names";
	my $sth	= $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_fecha');
	if (my @row_c = $sth->fetchrow_array) {$cloc = $row_c[0];}
	
	my $sql1 = "SET lc_time_names ='".$lan."_".$country."'";
	$dbh->do($sql1);
	
	my $sql2 = "SELECT UPPER(DATE_FORMAT(CURDATE(),'%W %e de %M %Y'))";
	my $sth1	= $dbh->prepare($sql2);
	$sth1->execute or die SQL_fail($sql2,$dbh,'sub get_fecha');
	if (my @row_c = $sth1->fetchrow_array) {$fec = $row_c[0];}	

	my $sql3 = "SET lc_time_names ='$cloc'";
	$dbh->do($sql3);

	return $fec;	
}
sub get_date {
	my $fec  = "";
	my $cloc = "";
	my $country = "SV";

	my $sql = "SELECT DATE_FORMAT(CURDATE(),'%Y-%m-%d')";
	my $sth	= $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_date');
	if (my @row_c = $sth->fetchrow_array) {
		$fec = $row_c[0];
	}		
	return $fec;	
}
sub get_time {
	my $fec  = "";
	my $cloc = "";
	my $country = "SV";

	my $sql = "SELECT TIME_FORMAT(CURRENT_TIME(), '%H %i')";
	my $sth	= $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_date');
	if (my @row_c = $sth->fetchrow_array) {
		$fec = $row_c[0];
	}	
	return $fec;	
}
#-------------------------------------------------------------------