#!/perl/bin/perl.exe
$|=1;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# Web Softtware SA
# Date In: 2005-07-12
# Updates: 2005-07-12,2016-02-02,2016-01-28
# File Name: rcrd.pl
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# PERL MODULES
use CGI;
use Web qw(rtmchk);
use XML::Simple;
use Data::Dumper;
use DBI;
use CGI::Carp qw(fatalsToBrowser);
binmode STDOUT, ":encoding(utf8)";
#--------------------------------------------------------------------------
#--------------------------------------------------------------------------
# HTML PRINTING ACTIVATION
print "Content-type: text/html; charset=utf-8\n\n";
#--------------------------------------------------------------------------
#--------------------------------------------------------------------------
#GET PARAMETERS
$cgiparms 	= new CGI;
$clase      = $cgiparms->param('clase');
$id		    = $cgiparms->param('id');
$campos     = $cgiparms->param('fields');
$valores    = $cgiparms->param('values');
$passback	= $cgiparms->param('getback');
$opener_form= $cgiparms->param('opener_form');
$field_focus= $cgiparms->param('field_focus');
$search_fields= $cgiparms->param('search_fields');
$search_values= $cgiparms->param('search_values');
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# CHECK PARAMETERS
if ($clase eq ""){print "Your are not passing CLASE parameter<br>";}
#if ($id eq ""){print "Your are not passing ID parameter<br>";}
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# 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";}
$dbh->{LongReadLen} = 512 * 2048;
$dbh->{'LongTruncOk'} = 1;
$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 * 2048;
$dbh1->{'LongTruncOk'} = 1;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# MAIN PROGRAM
$exist      	= "false";
$elements		= $data->{elements};
%labels_passed	= get_labels_passed();
if($elements ne "" && $elements ne "NO"){%combos = construct_combos($elements)}
if($elements ne "" && $elements ne "NO"){%textos = construct_textos($elements)}
if($id ne ""){
      $select_order	                            = get_select_order();
      $where_statement	                        = get_where_statement($id,$campos,$valores);
      %record		                            = get_record($select_order,$where_statement);
      %labels                                   = get_record_labels(%record);
      %associations                             = get_associations($id);
      %associations_labels                      = get_association_labels(%associations);
      ($select_fields,$select_values)           = split_hashes(1,%record);
      ($labels_fields,$labels_values)           = split_hashes(2,%labels);
      ($association_fields,$association_values) = split_hashes(2,%associations);
      ($association_labels_fields,$association_labels_values) = split_hashes(2,%associations_labels);      
      if($select_values ne ""){$exist="true"}
}
$associations_needed_fields = get_associations_needed_fields();
construct_page();
exit;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# MAIN SUB-ROUTINES
sub get_labels_passed {
	my %hash= ();
	my @sf 	= split(/\,/,$search_fields);
	my @sv	= split(/\|/,$search_values);
	for (my $i=0;$i<=$#sf;$i++){
		$hash{$sf[$i]} = $sv[$i];
		#print "$sf[$i] > $sv[$i] <br>";
	}
	return %hash;
}
sub construct_combos {
      my ($elems) = @_;
      my %cmb           = {};
      my @fields        = split(/\,/,$elems);
      my @camps         = split(/\,/,$campos);
      my @valrs         = split(/\^/,$valores);
      my $where_fields  = "";
      my $where_values  = "";
      for (my $i=0;$i<=$#fields;$i++){
            for (my $j=0;$j<=$#camps;$j++){
                  if($camps[$j]==$fields[$i]){$where_fields=$camps[$j];$where_values=$valrs[$j];}
            }
            my $selected_value = "";
            if ($data->{element}->{$fields[$i]}->{properties}->{type} eq "combo"){
                  if ($data->{element}->{$fields[$i]}->{clase}->{selected_value}){$selected_value = $data->{element}->{$fields[$i]}->{clase}->{selected_value}}                  
                  $cmb{$fields[$i]} = construct_combo($data->{element}->{$fields[$i]}->{clase}->{database},$data->{element}->{$fields[$i]}->{clase}->{table},$fields[$i],$data->{element}->{$fields[$i]}->{clase}->{fields},$selected_value);
            }
      }
      return %cmb;
}
sub construct_textos {
      my ($elems) = @_;
      my %tt            = {};
      my %txt           = ();
      my @fields        = split(/\,/,$elems);
      my @camps         = split(/\,/,$campos);
      my @valrs         = split(/\^/,$valores);
      my $where_fields  = "";
      my $where_values  = "";
      for (my $i=0;$i<=$#fields;$i++){
            for (my $j=0;$j<=$#camps;$j++){
                  if($camps[$j]==$fields[$i]){$where_fields=$camps[$j];$where_values=$valrs[$j];}
            }
            if ($data->{element}->{$fields[$i]}->{properties}->{type} eq "texto"){
                  %tt = construct_texto($data->{element}->{$fields[$i]}->{clase}->{database},$data->{element}->{$fields[$i]}->{clase}->{table},$fields[$i],$data->{element}->{$fields[$i]}->{clase}->{fields},$where_fields,$where_values);
                  $txt{$fields[$i]} = {%tt};
            }
      }
      return %txt;
}
sub get_associations_needed_fields{
    my $aif = "";
    my @ass = split(/\,/,$data->{clase}->{associations});
    for (my $i=0;$i<=$#ass;$i++){
          if ($data->{clase}->{association}->{$ass[$i]}){
                my $table = $data->{clase}->{association}->{$ass[$i]}->{table};
                if($data->{clase}->{association}->{$ass[$i]}->{insert_fields}){
                      $aif .= $table."|".$data->{clase}->{association}->{$ass[$i]}->{insert_fields}."~~";
                }
          }
    }
    return $aif;
}
sub get_select_order {
      my @fields = ();
      my $select_order="";
      my @displayed_fields = split(/\,/,$data->{clase}->{select_fields});
      for (my $w=0;$w<=$#displayed_fields;$w++){
            $fields[$w]= $displayed_fields[$w];
            my $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 ($id,$f,$v) = @_;
      my $ws            = "WHERE ";
      my $timestamp	= "";
      my $ts_min	= "";
      my $ts_max	= "";      
      my $where		= "";
      my @splt 		= split(/\,/,$f);
      my @splt1		= split(/\^/,$v);
      my $where_field   = $data->{clase}->{keys};
      if($f ne "" && $v ne ""){
	    for (my $i=0;$i<=$#splt;$i++){
		  $timestamp = substr($splt[$i],0,2);		
		  if ($timestamp eq "ts"){
			@ts = split(/-/,$splt1[$i]);
			$ts_min = $ts[2].$ts[0].$ts[1]."000000";
			$ts_max = $ts[2].$ts[0].$ts[1]."235959";
			$ws    .= $splt[$i].">='$ts_min' and ".$splt[$i]."<='$ts_max' and ";		
		  }else{
			$ws    .= $splt[$i]."='".$splt1[$i]."' and ";
		  }
	    }
	    $where = substr($ws,0,length($ws)-4);
            if($id ne ""){if($where ne ""){$where.= "and $where_field='$id'"}else{$where = "WHERE $where_field='$id'"}}
      }else {
            $where = "WHERE $where_field='$id'";            
      }
      return $where;
}
sub get_record {
      my ($select_order,$where_statement)=@_;
      my @fields   = split(/\,/,$data->{clase}->{select_fields});
      my $table    = $data->{clase}->{table};
      my $database = $data->{clase}->{database};      
      my $sql      = "SELECT $select_order FROM $table $where_statement ORDER BY 1";
      my $sth 	   = $dbh->prepare($sql);
      $sth->execute or die SQL_fail($sql,$dbh,'sub get_records');
      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 %all_labels 	= ();
      foreach $key(keys %record) {
          if ($data->{clase}->{select_labels}->{$key}){
                my %rl = get_label($data->{clase}->{select_labels}->{$key}->{database},
                                   $data->{clase}->{select_labels}->{$key}->{fields},
                                   $data->{clase}->{select_labels}->{$key}->{table},
                                   $data->{clase}->{select_labels}->{$key}->{id_field},
                                   $record{$key},$data->{clase}->{select_labels}->{$key}->{document_fields});
                $all_labels{$key} = {%rl};
          }		
      }
      return %all_labels;
}
sub get_associations {
    my ($id)=@_;
    my %all_ass = ();
    my @ass = split(/\,/,$data->{clase}->{associations});
    for (my $i=0;$i<=$#ass;$i++){
        if ($data->{clase}->{association}->{$ass[$i]}){
            my $ws    = "WHERE ";
            my @splt1 = ();
            if ($data->{clase}->{association}->{$ass[$i]}->{filters}){
                  my @splt  = split(/\,/,$data->{clase}->{association}->{$ass[$i]}->{filters}->{fields});
                  if ($data->{clase}->{association}->{$ass[$i]}->{filters}->{values}){
                        @splt1 = split(/\|/,$data->{clase}->{association}->{$ass[$i]}->{filters}->{values});
                  }else{
                        @splt1 = ($record{$data->{clase}->{association}->{$ass[$i]}->{filters}->{fields}});
                  }
                  for (my $i=0;$i<=$#splt;$i++){
                        $ws .= $splt[$i]."='".$splt1[$i]."' and ";    
                  }
            }
            if ($data->{clase}->{association}->{$ass[$i]}->{type} eq "soft"){$ws .= $data->{clase}->{association}->{$ass[$i]}->{keys}."='".$id."'";}
            if ($data->{clase}->{association}->{$ass[$i]}->{type} eq "hard"){$ws .= $data->{clase}->{association}->{$ass[$i]}->{keys}."='".$record{$data->{clase}->{association}->{$ass[$i]}->{keys}}."'";}
            my %rl = get_association($data->{clase}->{association}->{$ass[$i]}->{database},
                                     $data->{clase}->{association}->{$ass[$i]}->{fields},
                                     $data->{clase}->{association}->{$ass[$i]}->{table},
                                     $ws,
                                     $data->{clase}->{association}->{$ass[$i]}->{document_fields});
            $all_ass{$ass[$i]} = {%rl};
        }
    }
    return %all_ass;
}
sub get_association_labels {
	my (%assos)=@_;
	my %all_ass_labels = ();
	foreach $key(keys %assos) {
	    my @ass_fields = split(/\,/,$data->{clase}->{association}->{$key}->{fields});
	    my @ass_docums = split(/\,/,$data->{clase}->{association}->{$key}->{document_fields});
	    for (my $i=0;$i<=$#ass_docums;$i++){
            if ($data->{clase}->{association}->{$key}->{select_labels}->{$ass_fields[$i]}){
                my %rl = get_label($data->{clase}->{association}->{$key}->{select_labels}->{$ass_fields[$i]}->{database},
                                   $data->{clase}->{association}->{$key}->{select_labels}->{$ass_fields[$i]}->{fields},
                                   $data->{clase}->{association}->{$key}->{select_labels}->{$ass_fields[$i]}->{table},
                                   $data->{clase}->{association}->{$key}->{select_labels}->{$ass_fields[$i]}->{id_field},
                                   $assos{$key}{$ass_docums[$i]},
                                   $data->{clase}->{association}->{$key}->{select_labels}->{$ass_fields[$i]}->{document_fields});
                $all_ass_labels{$ass_docums[$i]} = {%rl};
            }
	    }		
	}
	return %all_ass_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 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/{id}/$id/g;
	    $line =~ s/{passback}/$passback/g;
	    $line =~ s/{opener_form}/$opener_form/g;
	    $line =~ s/{field_focus}/$field_focus/g;
            
	    $line =~ s/{exist}/$exist/g;
	    $line =~ s/{fields}/$campos/g;
	    $line =~ s/{values}/$valores/g;
            
	    $line =~ s/{$data->{clase}->{keys}}/$id/g;          
	    $line =~ s/{title}/$data->{title}/g;
	    $line =~ s/{height}/$data->{height}/g;
	    $line =~ s/{width}/$data->{width}/g;
            $line =~ s/{associations_needed_fields}/$associations_needed_fields/g;
	    $line =~ s/{needed_fields}/$data->{clase}->{needed_fields}/g;
	    $line =~ s/{autoincrement}/$data->{clase}->{autoincrement}/g;

	    if($exist eq "false"){$line =~ s/{insert_update_fields}/$data->{clase}->{insert_fields}/g;}else{$line =~ s/{insert_update_fields}/$data->{clase}->{update_fields}/g;}
	    if($data->{clase}->{checked_field}){$line =~ s/{checked_field}/$data->{clase}->{checked_field}/g;}

	    $line =~ s/{select_fields}/$select_fields/g;
	    $line =~ s/{select_values}/$select_values/g;
	    $line =~ s/{labels_fields}/$labels_fields/g;
	    $line =~ s/{labels_values}/$labels_values/g;
            
	    $line =~ s/{association_fields}/$association_fields/g;
	    $line =~ s/{association_values}/$association_values/g;
	    $line =~ s/{association_labels_fields}/$association_labels_fields/g;
	    $line =~ s/{association_labels_values}/$association_labels_values/g;

	    foreach $key(keys %labels_passed){$line =~ s/{$key}/$labels_passed{$key}/g;}
	    foreach $key(keys %combos){$line =~ s/{$key}/$combos{$key}/g;}
	    foreach $key(keys %record){$line =~ s/{$key}/$record{$key}/g;}
            foreach $key(keys %textos){
                foreach $key1(keys %{$textos{$key}}){$line =~ s/{$key1}/$textos{$key}{$key1}/g}
            }
            foreach $key(keys %labels){
                foreach $key1(keys %{$labels{$key}}){$line =~ s/{$key1}/$labels{$key}{$key1}/g}
            }
	    print "$line";
      }
}
#-------------------------------------------------------------------------
# AUXILIAR-SUBROUTINES
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."_rcrd.xml";
      my $xml        = new XML::Simple;
      $file       = "../xml/$schema";
      $data 	  = $xml->XMLin($file);
      $path	  = $data->{base_path}."/".$lan."/";
      $file	  = $lan."_".$cmp."_rcrd.html";
}
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;
}
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 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 2";
      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 eq $row_c[0]){$trc_line =~ s/{selected}/selected/g;}else{$trc_line =~ s/{selected}//g;}
            $combo = "$combo"."\n"."$trc_line";
      }	
      return $combo;
}
sub construct_texto {
      my ($db,$table,$option_value_field,$option_caption_field,$wf,$wv)=@_;
      my %tx      = {};
      my $sql     = "";
      if($wf ne ""){$sql = "SELECT $option_value_field,$option_caption_field FROM $table WHERE $wf='$wv' ORDER BY 1"}else{$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_texto');
      if (my @row_c = $sth->fetchrow_array) {
            $tx{$option_value_field}   = $row_c[0];
            $tx{$option_caption_field} = $row_c[1];
      }
      return %tx;
}
sub get_label {
    my ($db,$so,$fo,$field_id,$label_id,$do)=@_;    
    $do 	    =~ s/{lan}/$lan/g;
    $so 	    =~ s/{lan}/$lan/g;
    my %labels  = ();
    my $i 	    = 0;
    my @fields  = split(/\,/,$do);
    my $sql	    = "SELECT $so FROM $fo WHERE $field_id='$label_id' ORDER BY 1";
    my $sth1    = $dbh1->prepare($sql);
    $sth1->execute or die SQL_fail($sql,$dbh1,'sub get_labels');
    if (my @row_a = $sth1->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_association {
    my ($db,$so,$fo,$WHERE,$do)=@_;    
    $do 	    =~ s/{lan}/$lan/g;
    $so 	    =~ s/{lan}/$lan/g;
    my %la      = ();
    my $i 	    = 0;
    my @fields  = split(/\,/,$do);
    my $sql	    = "SELECT $so FROM $fo $WHERE ORDER BY 1";
    my $sth1 	= $dbh1->prepare($sql);
    $sth1->execute or die SQL_fail($sql,$dbh1,'sub get_asociation');
    if (my @row_a = $sth1->fetchrow_array) {
        for($i=0;$i<=$#fields;$i++){
            if(substr($fields[$i],0,2) eq "ts"){
                $la{$fields[$i]}= standard_timestamp($row_a[$i]);
            }else{
                $la{$fields[$i]}=$row_a[$i];
            }
        }
    }else{
        for($i=0;$i<=$#fields;$i++){
            if(substr($fields[$i],0,2) eq "ts"){
                $la{$fields[$i]} = "";
            }else{
                $la{$fields[$i]} = "";
            }
        }
    }
    return %la;
}
#-------------------------------------------------------------------------