#!/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: comml.pl
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# PERL MODULES
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
$links		 = "";
$collapsing_menu = "";
if ($id_field eq ""){
      my @keys_array   = split(/\,/,$data->{clase}->{keys});
      $id_field = $keys_array[0];
}
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}){
      ($id_field,$id)     = get_new_id($id_field,$id);
}
if ($id ne ""){
      $select_order	       = get_select_order();
      $where_statement	   = get_where_statement($id);
      ($count)	   	       = get_record($select_order,$where_statement);
      if($count>0){
	    ($links,$array_size) = construct_values($data->{links});
	    $collapsing_menu     = get_collapsing_menu();
	    construct_page($links,'');
      }else{
	    $msg = "<DIV class=my_style2>$data->{msg_empty}</DIV>";
	    construct_page('',$msg);
      }
}else{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'";            
      }
      return $where;
}
sub get_record {
      my ($select_order,$where_statement)=@_;
      my $count	   = 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";
      #print "$sql";
      my $sth 	   = $dbh->prepare($sql);
      $sth->execute or die SQL_fail($sql,$dbh,'sub get_records');
      if (my @row = $sth->fetchrow_array) {
	    $count++;
      }
      return $count;
}
sub construct_values {
      my ($lks)=@_;
      my @lnks = split(/\,/,$lks);
      my $l="";
      my $count=1;
      for(my $i=0;$i<=$#lnks;$i++){
	    my $component	= $data->{link}->{$lnks[$i]}->{component};
	    my $class		= $data->{link}->{$lnks[$i]}->{clase};
	    my $left		= $data->{link}->{$lnks[$i]}->{left};
	    my $top		= $data->{link}->{$lnks[$i]}->{top};
	    my $caption		= $data->{link}->{$lnks[$i]}->{caption};
	    my $open		= $data->{link}->{$lnks[$i]}->{open};
	    my $fields		= $data->{link}->{$lnks[$i]}->{open_defaults}->{fields};
	    my $values		= $data->{link}->{$lnks[$i]}->{open_defaults}->{values};
	    $l			= $l.$count."|".$component.",".$class.",".$left.",".$top."|".$caption."|".$open."|".$fields."|".$values."~~";
	    $count++;
      }
      return ($l,$#lnks);
}
sub get_collapsing_menu{
      my $options="";
      open (DATA,"$path_div$file_div");
      #print "$path_div$file_div <br>";
      while ($line = <DATA>){
	    $line =~ s/{lan}/$lan/g;
	    $line =~ s/{appid}/$appid/g;
	    $line =~ s/{clase}/$clase/g;
            $options .= $line;
      }
      close DATA;
      return $options;
}
sub construct_page {
      my ($links,$msg)=@_;
      open (DATA,"$path$file");
      #print "$path$file";
      while ($line = <DATA>){
	    $line =~ s/{lan}/$lan/g;
	    $line =~ s/{appid}/$appid/g;
	    $line =~ s/{clase}/$clase/g;
	    
	    $line =~ s/{collapsing_menu}/$collapsing_menu/g;
	    $line =~ s/{links}/$links/g;
	    $line =~ s/{msg}/$msg/g;
	    $line =~ s/{array_size}/$array_size/g;
	    $line =~ s/{select_fields}/$select_fields/g;
	    $line =~ s/{select_values}/$select_values/g;
	    
	    $line =~ s/{id_field}/$data->{id_field}/g;
	    $line =~ s/{header}/$data->{grid_header}/g;
	    $line =~ s/{height}/$data->{height}/g;
	    $line =~ s/{width}/$data->{width}/g;
	    $line =~ s/{msg_empty}/$data->{master}->{msg_empty}/g;
	    $line =~ s/{tdbody_style}/$data->{tdbody_style}/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	      = "../libraries/html/";
      $file	      = "comml.html";
      $path_div   = "../".$lan."/";
      $file_div   = $lan."_".$cmp."_comm.div";
}
sub SQL_fail {
    ($query,$dbh,$section)= @_;
    print "SQL failed in Section $section<br>";
    print "Query: ".$query."<br>";    
    exit;
}
#-------------------------------------------------------------------------