#!/perl/bin/perl.exe
$|=1;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# Web Softtware SA
# Date In: 2005-01-17
# Updates: 2005-01-17,2016-02-02
# Author:Rodolfo Machon
# File Name: commf.pl
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# PERL MODULE
use CGI;
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_field   = $cgiparms->param('id_field');
$id      	= $cgiparms->param('id');
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# CHECK PARAMETERS
if ($clase eq ""){print "Your are not passing CLASE 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 * 1024;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#MAIN PROGRAM
%keys       = ();
$elements   = $data->{elements};
if($elements ne "" && $elements ne "NO"){%combos = construct_combos($elements)}
if($elements ne "" && $elements ne "NO"){%textos = construct_textos($elements)}
if($id ne ""){
      my @keys_array = split(/\,/,$data->{clase}->{keys});
      for (my $w=0;$w<=$#keys_array;$w++){$keys{$keys_array[$w]}=$keys_array[$w]}
      if($keys{$id_field}){
            $new_id=$id;
      }else{
            ($id_field,$new_id) = get_new_id($id_field,$id);
            if($new_id eq ""){construct_page();exit}            
      }
      #-------------------------------------------------------------------
      $select_order         = get_select_order();
      $where_statement      = get_where_statement($new_id,$campos,$valores);
      ($count,%record)      = get_record($select_order,$where_statement);
      if($count>0){
            %labels              = get_record_labels(%record);
            %associations        = get_associations($new_id);
            %associations_labels = get_association_labels(%associations);
      }      
}
construct_page();
exit;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#SUB-ROUTINES
sub get_new_id {
      my ($id_field,$id)=@_;
      my $ni     = "";
      my $fields = "";    
      if($data->{clase}->{association}->{$id_field}){
            $fields      = $data->{clase}->{association}->{$id_field}->{fields};
            my $table    = $data->{clase}->{association}->{$id_field}->{table};
            my $database = $data->{clase}->{association}->{$id_field}->{database};      
            my $sql      = "SELECT $fields FROM $table WHERE $id_field='$id' ORDER BY 1";
            my $sth 	 = $dbh->prepare($sql);
            $sth->execute or die SQL_fail($sql,$dbh,'sub get_new_id');
            if (my @row = $sth->fetchrow_array) {$ni=$row[0]}
      }
      return ($fields,$ni);
}
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 $id_field='$id'"}else{$where = "WHERE $id_field='$id'"}}
            #if($id ne ""){if($where ne ""){$where.= "and $where_field='$id'"}else{$where = "WHERE $where_field='$id'"}}            
      }else {
            $where = "WHERE $id_field='$id'";
            #$where = "WHERE $id_field='$id'";            
      }
      return $where;
}
sub get_record {
      my ($select_order,$where_statement)=@_;
      my $cnt      = 0;
      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) {
            $cnt++;
            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 ($cnt,%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]}->{fields}."='".$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]}->{keys},
                                     $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 construct_page{
      open (DATA, "$path$file");
      #print "$path$file <br>";
      while ($line = <DATA>){
	    $line =~ s/{lan}/$lan/g;
	    $line =~ s/{appid}/$data->{appid}/g;
	    $line =~ s/{clase}/$data->{clase}/g;            	    
	    $line =~ s/{id}/$id/g;
	    
	    foreach $key(keys %combos){$line =~ s/{$key}/$combos{$key}/g;}
        foreach $key(keys %textos){
            foreach $key1(keys %{$textos{$key}}){$line =~ s/{$key1}/$textos{$key}{$key1}/g}
        }
	    foreach $key(keys %record){$line =~ s/{$key}/$record{$key}/g;}
        foreach $key(keys %labels){
            foreach $key1(keys %{$labels{$key}}){$line =~ s/{$key1}/$labels{$key}{$key1}/g}
        }
        foreach $k(keys %associations){
            foreach $kk(keys %{$associations{$k}}){
                $line =~ s/{$kk}/$associations{$k}{$kk}/g;
            }
        }
        foreach $key(keys %associations_labels){
            foreach $key1(keys %{$associations_labels{$key}}){$line =~ s/{$key1}/$associations_labels{$key}{$key1}/g}
        }
	    @clear_empty_fields  = split(/\,/,$data->{clase}->{clear_fields});
	    for (my $i=0;$i<=$#clear_empty_fields;$i++){$line =~ s/{$clear_empty_fields[$i]}//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."_comm.xml";
    my $xml          = new XML::Simple;
    $data 	  = $xml->XMLin("../xml/".$schema);
    $path	  = "../".$lan."/";    
    $file	  = $lan."_".$data->{html_file}."_commf.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>";
    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_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];}
            }
            if ($data->{element}->{$fields[$i]}->{properties}->{type} eq "combo"){		
                  $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},'no_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_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 $sth 	= $dbh->prepare($sql);
      $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_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 $sth 	  = $dbh->prepare($sql);
      $sth->execute or die SQL_fail($sql,$dbh,'sub get_association');
      if (my @row_a = $sth->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;
}
#-------------------------------------------------------------------------