#!/perl/bin/perl.exe
$|=1;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# Information Software SA
# Date In: 2006-05-04
# Updates: 2006-05-04
# Author: Rodolfo Machon
# File Name: ltmdm.pl
use CGI;
use XML::Simple;
use Data::Dumper;
use DBI;
use CGI::Carp qw(fatalsToBrowser);
use Date::Calc qw(Add_Delta_YMD);
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');
$passback     	= $cgiparms->param('passback');
$opener_form   	= $cgiparms->param('opener_form');
$field_focus   	= $cgiparms->param('field_focus');
$id      	    = $cgiparms->param('id');
$period_values	= $cgiparms->param('period_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 * 1024;
$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 * 1024;
$dbh1->{LongTruncOk} = 1;
$dbh2 	  = DBI->connect("dbi:ODBC:$appid",'','',{PrintError => 1, RaiseError => 1});
if (!$dbh2) {print "error: connection: $DBI::err\n$DBI::errstr\n$DBI::state\n";}
$dbh2->{LongReadLen} = 512 * 1024;
$dbh2->{LongTruncOk} = 1;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# MAIN PROGRAM
$elements	= $data->{elements};
%combos		= get_combos($elements);
%associations = ();
if($id ne ""){
      $select_order	      = get_select_order();
      $where_statement	      = get_where_statement($id);
      %records		      = get_record($select_order,$where_statement);
      %records_labels         = get_record_labels(%records);
      %associations_tables    = get_associations($id);
      %associations_labels    = get_association_labels(%associations);
      %ids		      = get_ids($id);
      ($dt_since_field,$dt_upto_field) = split(/\,/,$data->{master}->{period_document_fields});
      if($period_values ne ""){
	    ($dt_since_value,$dt_upto_value) = split(/\,/,$period_values);
      }else{
	    $dt_since_value = get_actual_date();
	    $dt_upto_value  = get_upto_date($dt_since_value,$data->{master}->{periods});
      }      
}
construct_page();
exit;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# MAIN SUB-ROUTINES
sub get_select_order {
      my @fields = ();
      my $select_order="";
      my @displayed_fields = split(/\,/,$data->{master}->{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 ($where_value)=@_;
    my $where_field = $data->{master}->{id_field};
    my $ws = "WHERE $where_field='$where_value'";
    return $ws;
}
sub get_record {
      my ($select_order,$where_statement)=@_;
      my @fields   = split(/\,/,$data->{master}->{select_fields});
      my $table    = $data->{master}->{table};
      my $database = $data->{master}->{database};      
      my $sql      = "SELECT $select_order FROM $table $where_statement ORDER BY 1";
      $sth 	= $dbh->prepare($sql);
      $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 %all_labels 		= ();
	foreach $key(keys %record) {
	    if ($data->{master}->{select_labels}->{$key}){
                  
                  
		my %rl = get_label($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});
		$all_labels{$key} = {%rl};
	    }		
	}
	return %all_labels;
}
sub get_associations {
    my ($id)=@_;
    my %all_ass = ();
    my %all_ass_table = ();    
    my @ass = split(/\,/,$data->{master}->{associations});
    for (my $i=0;$i<=$#ass;$i++){
      if ($data->{master}->{association}->{$ass[$i]}){
            my $ws    = "WHERE ";
            my @splt1 = ();
            if ($data->{master}->{association}->{$ass[$i]}->{filters}){
                  my @splt  = split(/\,/,$data->{master}->{association}->{$ass[$i]}->{filters}->{fields});
                  if ($data->{master}->{association}->{$ass[$i]}->{filters}->{values}){
                        @splt1 = split(/\|/,$data->{master}->{association}->{$ass[$i]}->{filters}->{values});
                  }else{
                        @splt1 = ($record{$data->{master}->{association}->{$ass[$i]}->{filters}->{fields}});
                  }
                  for (my $i=0;$i<=$#splt;$i++){
                        $ws .= $splt[$i]."='".$splt1[$i]."' and ";    
                  }
            }
            if ($data->{master}->{association}->{$ass[$i]}->{type} eq "soft"){$ws .= $data->{master}->{association}->{$ass[$i]}->{keys}."='".$id."'";}
            if ($data->{master}->{association}->{$ass[$i]}->{type} eq "hard"){$ws .= $data->{master}->{association}->{$ass[$i]}->{keys}."='".$record{$data->{master}->{association}->{$ass[$i]}->{keys}}."'";}
            if ($data->{master}->{association}->{$ass[$i]}->{scope} eq "table"){
                my $rl_table = get_association_table($ass[$i],$data->{master}->{association}->{$ass[$i]}->{database},$data->{master}->{association}->{$ass[$i]}->{fields},$data->{master}->{association}->{$ass[$i]}->{table},$ws,$data->{master}->{association}->{$ass[$i]}->{document_fields});
                $all_ass_table{$ass[$i]} = $rl_table;
            }else{
                my %rl = get_association($data->{master}->{association}->{$ass[$i]}->{database},$data->{master}->{association}->{$ass[$i]}->{fields},$data->{master}->{association}->{$ass[$i]}->{table},$ws,$data->{master}->{association}->{$ass[$i]}->{document_fields});
                $associations{$ass[$i]} = {%rl};
            }
      }
    }
    return %all_ass_table;
}
sub get_association_labels {
      my (%assos)=@_;
      my %all_ass_labels = ();
      foreach $key(keys %assos) {
            my @ass_fields = split(/\,/,$data->{master}->{association}->{$key}->{fields});
            my @ass_docums = split(/\,/,$data->{master}->{association}->{$key}->{document_fields});
            for (my $i=0;$i<=$#ass_docums;$i++){
                  if ($data->{master}->{association}->{$key}->{select_labels}->{$ass_fields[$i]}){
                        my %rl		= ();
                        my $database	= $data->{master}->{association}->{$key}->{select_labels}->{$ass_fields[$i]}->{database};
                        my $table	= $data->{master}->{association}->{$key}->{select_labels}->{$ass_fields[$i]}->{table};
                        my $columns	= $data->{master}->{association}->{$key}->{select_labels}->{$ass_fields[$i]}->{fields};
                        my $doc_columns = $data->{master}->{association}->{$key}->{select_labels}->{$ass_fields[$i]}->{document_fields};
                        my $id_field	= $data->{master}->{association}->{$key}->{select_labels}->{$ass_fields[$i]}->{id_field};
                        my @id_fields	= split(/\,/,$id_field);
                        if($#id_fields>0){
                              my $id_value = "";
                              for(my $a=0;$a<=$#id_fields;$a++){
                                    $id_value .= $assos{$key}{$id_fields[$a]}.",";
                              }
                              $id_value = substr($id_value,0,length($id_value)-1);
                              %rl = get_label($database,$columns,$table,$id_field,$id_value,$doc_columns);			
                        }else{
                              %rl = get_label($database,$columns,$table,$id_field,$assos{$key}{$ass_docums[$i]},$doc_columns);
                        }                  
                        $all_ass_labels{$key}{$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 get_combos {
      my ($elems)=@_;
      my @fields = split(/\,/,$elems);
      for (my $i=0;$i<=$#fields;$i++){
	      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 get_ids {
      my ($id_values)=@_;
      my %ids	   = ();
      my $id_field = $data->{master}->{id_field};
      my @fields   = split(/\,/,$id_field);
      my @values   = split(/\|/,$id_values);
      for (my $i=0;$i<=$#fields;$i++){
	    $ids{$fields[$i]} = $values[$i];	
      }
      return %ids;
}
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/{title}/$data->{title}/g;
	    $line =~ s/{passback}/$passback/g;
	    $line =~ s/{opener_form}/$opener_form/g;
	    $line =~ s/{field_focus}/$field_focus/g;
	    $line =~ s/{id}/$id/g;
	    $line =~ s/{$dt_since_field}/$dt_since_value/g;
	    $line =~ s/{$dt_upto_field}/$dt_upto_value/g;
            
	    foreach $key(keys %record){$line =~ s/{$key}/$record{$key}/g}
	    foreach $key(keys %associations_tables){$line =~ s/{$key}/$associations_tables{$key}/g}
            foreach $key(keys %records_labels){
                foreach $key1(keys %{$records_labels{$key}}){$line =~ s/{$key1}/$records_labels{$key}{$key1}/g}
            }
            foreach $key(keys %associations){
                foreach $key1(keys %{$associations{$key}}){
                    $line =~ s/{$key1}/$associations{$key}{$key1}/g
                }
            }
            foreach $key(sort keys %associations_labels){
                foreach $key1(sort keys %{$associations_labels{$key}}){
                        foreach $key2(sort keys %{$associations_labels{$key}{$key1}}){
                              $line =~ s/{$key2}/$associations_labels{$key}{$key1}{$key2}/g
                        }
                  }
            }
	    foreach $key(keys %combos){$line =~ s/{$key}/$combos{$key}/g}
	    foreach $key(keys %ids){$line =~ s/{$key}/$ids{$key}/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."_ltmd.xml";
	my $xml        = new XML::Simple;
	$data 	  = $xml->XMLin("../xml/".$schema);
	$path	  = $data->{base_path}."/".$lan."/";
	$file	  = $lan."_".$cmp."_ltmdm.html";
}
sub SQL_fail {
    my ($query,$dbh,$section)= @_;
    print "SQL failed in Section $section<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_combo {
	my ($db,$table,$option_value_field,$option_caption_field,$selected_option)=@_;
	my $combo = "";
	if (!($dbh 	= new Win32::ODBC($db))){connect_fail($db)}
	my $trc		= "<option value={option_value} {selected}>{option_caption}</option>";
	my $query	= "SELECT $option_value_field,$option_caption_field FROM $table ORDER BY 1 DESC";
	if ( $dbh->Sql($query) ) {print "SQL failed.\n";print "$query<br>";print "Error: ".$dbh->Error()."\n";$dbh->Close();exit;}
	while ($dbh-> FetchRow()) {
		undef @row_c;
		@row_c = $dbh->Data();
		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";
	}
	$dbh->Close();	
	return $combo;
}  
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 @id_field= split(/\,/,$field_id);
      my @id_value= split(/\,/,$label_id);
      my $sql     = "";
      if($#id_field>0){
            my $where	= "";
            for(my $j=0;$j<=$#id_field;$j++){
                  $where .= "$id_field[$j]='$id_value[$j]' and ";
            }
            $where	= substr($where,0,length($where)-4);
            $sql	="SELECT $so FROM $fo WHERE $where ORDER BY 1";
      }else{
            $sql	="SELECT $so FROM $fo WHERE $field_id='$label_id' ORDER BY 1";
      }
      $sth 	= $dbh->prepare($sql);
      $sth->execute or die SQL_fail($sql,$dbh,'sub get_record');
      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";
      $sth 	= $dbh->prepare($sql);
      $sth->execute or die SQL_fail($sql,$dbh,'sub get_record');
      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++){
                  $la{$fields[$i]} = "";
            }
      }
      return %la;
}
sub get_association_table {
      my ($assoc,$db,$so,$fo,$WHERE,$do)=@_;    
      $do 	      =~ s/{lan}/$lan/g;
      $so 	      =~ s/{lan}/$lan/g;
      my $fila    = "";
      my $filas   = "";
      my $patron  = "</TD><TD>";
      my @fields  = split(/\,/,$do);
      my @sfields = split(/\,/,$so);
      my $sql1	  ="SELECT $so FROM $fo $WHERE ORDER BY 1";
      $sth1 	= $dbh1->prepare($sql1);
      $sth1->execute or die SQL_fail($sql1,$dbh1,'sub get_association_table');
      while (my @row_a = $sth1->fetchrow_array) {
            for(my $i=0;$i<=$#fields;$i++){
            if($fields[$i] ne $sfields[$i]){
                if($data->{master}->{association}->{$assoc}->{select_labels}->{$sfields[$i]}){
                    my $tabla     = $data->{master}->{association}->{$assoc}->{select_labels}->{$sfields[$i]}->{table};
                    my $new_value = get_association_table_label($db,$fields[$i],$tabla,$sfields[$i],$row_a[$i]);
                    if($new_value ne ""){$row_a[$i]=$new_value}
                }
            }    
        }
        $fila       = join("|",@row_a);
        $fila 	    =~ s/\|/$patron/g;
        $filas    .=  "<TR class=\"my_style1\"><TD>".$fila."</TD></TR>";
      }
      return $filas;
}
sub get_association_table_label {
      my ($db,$so,$fo,$field_id,$label_id)=@_;    
      $so 	=~ s/{lan}/$lan/g;
      my $label = "";
      my $sql2	="SELECT $so FROM $fo WHERE $field_id='$label_id' ORDER BY 1";
      $sth2 	= $dbh2->prepare($sql2);
      $sth2->execute or die SQL_fail($sql2,$dbh2,'sub association_table_label');
      if (my @row_b = $sth2->fetchrow_array) {
            $label    = $row_b[0];
      }
      return $label;
}
sub get_actual_date {
      my $database = $data->{master}->{database};      
      my $ad   = "";
      my $sql  = "SELECT DATE_FORMAT(CURRENT_DATE,'%Y-%m-%d')";
      $sth 	   = $dbh->prepare($sql);
      $sth->execute or die SQL_fail($sql,$dbh,'sub get_actual_date');
      if (my @row = $sth->fetchrow_array) {
            $ad=$row[0]
      }
      return $ad;
}
sub get_upto_date {
      my ($today,$Dm) = @_;
      my ($tyear,$tmonth,$tday) = split(/-/,$today);
      my ($year,$month,$day) = Add_Delta_YMD($tyear,$tmonth,$tday, 0,-$Dm,0);
      if(length($month)<2){$month="0$month"}
      if(length($day)<2){$day="0$day"}
      my $upto_date = "$year-$month-$day";
      return $upto_date;
}
#-------------------------------------------------------------------------