#!/perl/bin/perl.exe
$|=1;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# Information Software SA
# Date In: 2005-02-02
# Updates: 2005-02-03,2006-01-17,2006-05-01,2014-03-03,2018-06-14
# Author:Rodolfo Machon
# File Name: mdx.pl
#------------------------------------------------------------------------- 
#-------------------------------------------------------------------------
#PERL MODULES
use CGI;
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');
$master_values 	= $cgiparms->param('mv');
$detail_values 	= $cgiparms->param('dv');
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# 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;
$dbh->{LongTruncOk} = 1;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#MAIN PROGRAM
($grid_header,$field_reference,$field_process)	= get_grid_header($data->{detail}->{grid_columns});
if($id ne ""){
    $master_select_order  	 = get_select_order($data->{master}->{select_fields});
    $where_statement	  	 = get_where_statement($data->{master}->{id_field},$id);
    %master_select_hash 	 = get_record($data->{master}->{database},$data->{master}->{select_fields},$master_select_order,$data->{master}->{table},$where_statement);
    %master_labels_hashes 	 = get_record_labels(%master_select_hash);
    $detail_select_order  	 = get_select_order($data->{detail}->{select_fields});
    $where_statement	  	 = get_where_statement($data->{detail}->{id_field},$id);

    ($master_select_fields,$master_select_values) = split_hashes(1,%master_select_hash);    
    ($master_labels_fields,$master_labels_values) = split_hashes(2,%master_labels_hashes);
    ($detail_select_values,$detail_labels_values) = get_records_stream($data->{detail}->{database},$data->{detail}->{insert_grid_order},$detail_select_order,$data->{detail}->{table},$where_statement,$data->{detail}->{order_by});
    construct_page();
}else{construct_empty_page()}
exit;
#-------------------------------------------------------------------
# SUB-ROUTINES
sub get_grid_header {
      my ($grid_columns)=@_;
      my @fields = split(/\,/,$grid_columns);
      my $hd = "";
      my $fr = "";
      my $fp = "";      
      for(my $i=0;$i<=$#fields;$i++){
	    my $properties = "";
	    $hd .= $fields[$i]."|";
        $hd .= $data->{detail}->{column}->{$fields[$i]}->{header}."|";
	    $hd .= $data->{detail}->{column}->{$fields[$i]}->{width}."|";
	    $hd .= $data->{detail}->{column}->{$fields[$i]}->{element_type}."|";
	    %element_properties = %{$data->{detail}->{column}->{$fields[$i]}->{element_properties}};
	    foreach $key(keys %element_properties){
			if($element_properties{$key} ne "NO"){
				if($key ne "content"){$properties .= $key."=".$element_properties{$key}." ";}
			}
	    }
	    $properties = substr($properties,0,length($properties)-1);
	    $hd .= $properties."~~";
	    if($data->{detail}->{column}->{$fields[$i]}->{component}){$fr .= $data->{detail}->{column}->{$fields[$i]}->{component}."~~"}
	    if($data->{detail}->{column}->{$fields[$i]}->{process}){
			my $foot = "";
			if($data->{detail}->{column}->{$fields[$i]}->{process}->{footer}){
				my $type 	= $data->{detail}->{column}->{$fields[$i]}->{process}->{footer}->{type};
				my $conten = $data->{detail}->{column}->{$fields[$i]}->{process}->{footer}->{conten};
				$foot = $type.",".$conten;
			}
			$fp .= $fields[$i]."|".$data->{detail}->{column}->{$fields[$i]}->{process}->{row}."|".$foot."~~";
	    }
      }
      if($data->{count} eq "YES"){$hd=$data->{detail}->{column}->{count}->{header}."|".$data->{detail}->{column}->{count}->{element_type}."|".$data->{detail}->{column}->{count}->{default_checked}."|"."~~".$hd;}
      if($data->{all} eq "YES"){$hd.=$data->{detail}->{column}->{all}->{header}."|".$data->{detail}->{column}->{all}->{element_type}."|".$data->{detail}->{column}->{all}->{default_checked}."|"."~~";}
      return ($hd,$fr,$fp);
}
sub get_select_order {
	my ($select_fields)=@_;
	$select_order="";
	@displayed_fields = split(/\,/,$select_fields);
	for ($w=0;$w<=$#displayed_fields;$w++){
		$fields[$w]= $displayed_fields[$w];
		$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)=@_;
    $ws = "WHERE $where_field='$where_value'";
    return $ws;
}
sub get_record {
    my ($db,$select_fields,$select_order,$table,$where_statement)=@_;
    @fields	= split(/\,/,$select_fields);
    $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 (%record)=@_;
	my $database;
	my $select_order	= "";
	my $table_order		= "";
	my $where_order		= "";
	my $fields_order	= ""; 
	my %all_labels 		= ();
	foreach $key(keys %record) {
	    if ($data->{master}->{select_labels}->{$key}){
		    my %rl = ();
		    if (uc($data->{master}->{select_labels}->{$key}->{id_type}) eq "PIVOT"){
                %rl = get_labels($data->{master}->{select_labels}->{$key}->{database},
                                 $data->{master}->{select_labels}->{$key}->{fields},
                                 $data->{master}->{select_labels}->{$key}->{table},
                                 $data->{master}->{select_labels}->{$key}->{id_field},
                                 $record{$key},$data->{master}->{select_labels}->{$key}->{document_fields});
		    }else{
                %rl = get_labels($data->{master}->{select_labels}->{$key}->{database},$data->{master}->{select_labels}->{$key}->{fields},$data->{master}->{select_labels}->{$key}->{table},$key,$record{$key},$data->{master}->{select_labels}->{$key}->{document_fields});                              
		    }
		    $all_labels{$key} = {%rl};
	    }		
	}
	return %all_labels;
}
sub split_hashes {
    my ($level,%hashes)=@_;
    my $fields_stream ="";
    my $values_stream ="";
    if($level==1){
	foreach $key(keys %hashes){
	    $fields_stream = $fields_stream.$key.",";
	    $values_stream = $values_stream.$hashes{$key}."|";
	}
	return ($fields_stream,$values_stream);
    }elsif($level==2){
	foreach $key(keys %hashes){
	    my $fs = "";
	    my $vs = "";
	    foreach $key1(keys %{$hashes{$key}}){
		$fs = $fs.$key1.",";
		$vs = $vs.$hashes{$key}{$key1}."|";
	    }
	    $fields_stream = $fields_stream.$fs."~~";	    
	    $values_stream = $values_stream.$vs."~~";
	}
	return ($fields_stream,$values_stream);	
    }
}
sub get_records_stream {
    my ($db,$select_fields,$select_order,$table,$where_statement,$order_by)=@_;
    my @fields = split(/\,/,$select_fields);
    my @fields1 = split(/\,/,$select_order);
    my $stream_select_values = "";
    my $stream_labels_values = "";
    my $query = "";    
    if($order_by ne ""){$query="SELECT $select_order FROM $table $where_statement ORDER BY $order_by";}else{$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_records_stream');
	while (my @row = $sth->fetchrow_array) {
		my $rs = "";
        my $rll= "";
		my %rl = ();
		for(my $i=0;$i<=$#fields1;$i++){
			for(my $j=0;$j<=$#fields;$j++){
			if($fields1[$i] eq $fields[$j]){
				if(substr($fields1[$i],0,2) eq "ts"){
				$rf = standard_timestamp($row[$i]);
				}else{$rf = $row[$i]}
				$rs = $rs.$rf."|";
				if ($data->{detail}->{select_labels}->{$fields1[$i]}){
							my $dat = $data->{detail}->{select_labels}->{$fields1[$i]}->{database};
							my $fie = $data->{detail}->{select_labels}->{$fields1[$i]}->{fields};
							my $tab = $data->{detail}->{select_labels}->{$fields1[$i]}->{table};
							my $id_ = $data->{detail}->{select_labels}->{$fields1[$i]}->{id_field};
							my $doc = $data->{detail}->{select_labels}->{$fields1[$i]}->{document_fields};
				%rl = get_labels($dat,$fie,$tab,$id_,$row[$i],$doc);
				foreach $key(keys %rl){
					$rll = $rll.$row[$i]."|".$rl{$key}."|";		    
				}
				}
			}
			}
		}
		$stream_select_values = $stream_select_values.$rs."~~";
		$stream_labels_values = $stream_labels_values.$rll."~~";
    }
    return ($stream_select_values,$stream_labels_values);
}
sub get_master_labels {
    my $mlf = "";
    my $mlv = "";    
    my @master_fields = split(/\,/,$data->{master}->{select_fields});
    my @master_values = split(/\|/,$master_select_values);    
    for(my $a=0;$a<$#master_fields;$a++){
	    if ($data->{master}->{select_labels}->{$master_fields[$0]}){
		my $lf = "";
		my $lv = "";
		my $database = $data->{master}->{select_labels}->{$master_fields[$0]}->{database};
		my $table    = $data->{master}->{select_labels}->{$master_fields[$0]}->{table};		
		my $fields   = $data->{master}->{select_labels}->{$master_fields[$0]}->{fields};
		my $id_field = $data->{master}->{select_labels}->{$master_fields[$0]}->{id_field};
		my $dfields  = $data->{master}->{select_labels}->{$master_fields[$0]}->{document_fields};
		my %rl = get_labels($database,$fields,$table,$id_field,$master_values[$a],$dfields);
		foreach my $k(keys %rl){
		    $lf .= $k.",";
		    $lv .= $rl{$k}."|";
		}
		$mlf .= "~~";
		$mlv .= "~~";		
	    }
    }
    return ($mlf,$mlv);
}
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/{id_field}/$data->{master}->{id_field}/g;
	$line =~ s/{bodyrows}/$data->{detail}->{bodyrows}/g;	
	$line =~ s/{id}/$id/g;	
	$line =~ s/{process_bin}/$data->{process_bin}/g;
	$line =~ s/{process_schema}/?schema=$data->{process_schema}/g;	
	$line =~ s/{master_select_fields}/$master_select_fields/g;
	$line =~ s/{master_labels_fields}/$master_labels_fields/g;
	$line =~ s/{master_insert_fields}/$data->{master}->{insert_fields}/g;	
	$line =~ s/{master_select_values}/$master_select_values/g;
	$line =~ s/{master_labels_values}/$master_labels_values/g;
	$line =~ s/{detail_insert_fields}/$data->{detail}->{insert_grid_order}/g;				
	$line =~ s/{detail_select_values}/$detail_select_values/g;				
	$line =~ s/{detail_labels_values}/$detail_labels_values/g;
	$line =~ s/{detail_grid_header}/$grid_header/g;	
	$line =~ s/{detail_grid_values}//g;
	$line =~ s/{tdheader_style}/$data->{detail}->{tdheader_style}/g;
	$line =~ s/{tdbody_style}/$data->{detail}->{tdbody_style}/g;
	$line =~ s/{detail_field_reference}/$field_reference/g;
	$line =~ s/{detail_field_process}/$field_process/g;
	$line =~ s/{exist}/true/g;	
	$line =~ s/{count}/$count/g;
	print $line;
    }
}
sub construct_empty_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/{id_field}/$data->{master}->{id_field}/g;
	$line =~ s/{bodyrows}/$data->{detail}->{bodyrows}/g;
	$line =~ s/{id}//g;
	$line =~ s/{process_bin}/$data->{process_bin}/g;
	$line =~ s/{process_schema}/?schema=$data->{process_schema}/g;	
	$line =~ s/{master_select_fields}//g;
	$line =~ s/{master_labels_fields}//g;
	$line =~ s/{master_insert_fields}/$data->{master}->{insert_fields}/g;		
	$line =~ s/{master_select_values}//g;
	$line =~ s/{master_labels_values}//g;
	$line =~ s/{detail_select_fields}//g;				
	$line =~ s/{detail_labels_fields}//g;
	$line =~ s/{detail_insert_fields}/$data->{detail}->{insert_grid_order}/g;			
	$line =~ s/{detail_select_values}//g;				
	$line =~ s/{detail_labels_values}//g;
	$line =~ s/{detail_grid_header}/$grid_header/g;
	$line =~ s/{detail_grid_values}//g;
	$line =~ s/{tdheader_style}/$data->{detail}->{tdheader_style}/g;
	$line =~ s/{tdbody_style}/$data->{detail}->{tdbody_style}/g;
	$line =~ s/{detail_field_reference}/$field_reference/g;	
	$line =~ s/{detail_field_process}/$field_process/g;
	$line =~ s/{exist}/false/g;	
	$line =~ s/{count}/$count/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}."/libraries/html/";
    $file	  = "mdx.html";
}
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>";
    print "Error:".Win32::ODBC::Error()."<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 = "$y\-$mo\-$d";
	return $standard_timestamp;
}
sub get_clase_order {
    my ($field,$id) = @_;
    my $select_order= "";
    my $table_order	= "";
    my $where_order	= "";
    my $fields_order	= "";
    my $sc_keys		= "";
    my $sc_campos	= "";
    my $sc_doc_campos	= "";    
    my $sc_table	= "";	
    my $sc_to		= "";
    my $sc_wo		= "";
    my $talias		= "";
    my $c_keys		= $data->{master}->{field}->{$field}->{keys};
    my $c_campos	= $data->{master}->{field}->{$field}->{fields};
    my $c_doc_campos	= $data->{master}->{field}->{$field}->{document_fields};    
    my $c_table		= $data->{master}->{field}->{$field}->{table};
    my $database	= $data->{master}->{field}->{$field}->{database};	
    my $so		= "";
    my $to		= get_table_order($c_table,'');
    my $wo		= "";
    my $fo		= "";
    my @fields = split(/\,/,$c_campos);
    for (my $i=0;$i<=$#fields;$i++){
	if($data->{master}->{field}->{$field}->{field}->{$fields[$i]}){
	    $sc_keys		= $data->{master}->{field}->{$field}->{field}->{$fields[$i]}->{keys};
	    $sc_campos		= $data->{master}->{field}->{$field}->{field}->{$fields[$i]}->{fields};
	    $sc_doc_campos	= $data->{master}->{field}->{$field}->{field}->{$fields[$i]}->{document_fields};
	    $sc_table		= $data->{master}->{field}->{$field}->{field}->{$fields[$i]}->{table};
	    $so			= get_select_order1($sc_campos,$sc_table);
	    $fo			= $sc_doc_campos;
	    $sc_to		.= get_table_order($sc_table,'').",";
	    $sc_wo		.= get_where_order($fields[$i],$c_table,$sc_keys,$sc_table)." and ";
	}else{
	    $so			= get_table_field_pair($fields[$i],$c_table);
	    $fo			= $c_doc_campos;
	}
	$select_order		.= $so.",";
	$fields_order		= $fo;
    }
    $table_order = $sc_to.$to;
    if($id ne ""){
	$talias	= substr($c_table,4,length($c_table)-4);
	$wo 	= $talias.".".$c_keys."='".$id."'";
    }else{
	$sc_wo	= substr($sc_wo,0,length($sc_wo)-4);
    }
    $where_order = $sc_wo.$wo;
    $select_order= substr($select_order,0,length($select_order)-1);
    return ($database,$select_order,$table_order,$where_order,$fields_order);
}
sub get_labels {
	my ($db,$so,$fo,$field_id,$label_id,$do)=@_;
	$do 		=~ s/{lan}/$lan/g;
	$so 		=~ s/{lan}/$lan/g;
	my $i 		= 0;
	my %labels	= ();
	my @fields= split(/\,/,$do);
	my $query	="SELECT $so FROM $fo WHERE $field_id='$label_id' ORDER BY 1";
	#print "$query<br>";
	my $sth = $dbh->prepare($query);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_labels');
	if (my @row_a = $sth->fetchrow_array) {
		for($i=0;$i<=$#fields;$i++){
			if(substr($fields[$i],0,2) eq "ts"){
				$labels{$fields[$i]}= standard_timestamp($row_a[$i]);
			}else{
				$labels{$fields[$i]}=$row_a[$i];
			}
		}
	}else{
		for($i=0;$i<=$#fields;$i++){
			if(substr($fields[$i],0,2) eq "ts"){
				$labels{$fields[$i]} = "";
			}else{
				$labels{$fields[$i]} = "";
			}
		}
	}
	return %labels;
}
sub get_select_order1 {
    my ($fields,$table)=@_;
    my $so = "";
    my $talias = substr($table,4,length($table)-4);
    my @displayed_fields = split(/\,/,$fields);
    for (my $i=0;$i<=$#displayed_fields;$i++){
	$fields[$i]= $displayed_fields[$i];
	$timestamp= substr($fields[$i],0,2);			
	if ($timestamp eq "ts"){
	    $fields[$i] = "unix_timestamp(".$talias.".".$fields[$i].")";
	}else{
	    $fields[$i] = $talias.".".$fields[$i];	
	}
	if ( $#displayed_fields > $i ){
	    $so .= $fields[$i].",";
	}else{
	    $so .= $fields[$i];
	}
    }
    return $so;
}
sub get_table_order {
    my ($c_table,$sc_table) = @_;
    my $table = "";
    my $c_talias = substr($c_table,4,length($c_table)-4);
    if($sc_table ne ""){
	my $sc_talias = substr($sc_table,4,length($sc_table)-4);
	$table = $c_table." ".$c_talias.",".$sc_table." ".$sc_talias;
    }else{
	$table = $c_table." ".$c_talias;
    }
    return $table;
}
sub get_where_order {
    my ($c_field,$c_table,$sc_field,$sc_table)=@_;
    my $c_talias	= substr($c_table,4,length($c_table)-4);
    my $sc_talias	= substr($sc_table,4,length($sc_table)-4); 
    my $whr		= $c_talias.".".$c_field."=".$sc_talias.".".$sc_field;
    return $whr;
}
sub get_table_field_pair {
    my ($field,$table)=@_;
    my $talias = substr($table,4,length($table)-4);
    $timestamp= substr($field,0,2);			
    if ($timestamp eq "ts"){
	$field = "unix_timestamp(".$talias.".".$field.")";
    }else{
	$field = $talias.".".$field;	
    }
    return $field;
}
sub get_labels_values {
    my ($db,$so,$fo,$wo,$co)=@_;
    my @fields	= split(/\,/,$so);
    my @c_order	= split(/\,/,$co);    
    my %labels 	= ();
    if($wo ne ""){$query="SELECT $so FROM $fo WHERE $wo ORDER BY 1"}else{$query="SELECT $so FROM $fo ORDER BY 1"}
	my $sth = $dbh->prepare($query);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_labels');
	if (my @row = $sth->fetchrow_array) {
		for(my $i=0;$i<=$#c_order;$i++){
			if(substr($c_order[$i],0,2) eq "ts"){
				$labels{$c_order[$i]}= standard_timestamp($row[$i]);
			}else{
				$labels{$c_order[$i]}=$row[$i];
			}
		}
    }
    return %labels;
}
#-------------------------------------------------------------------