#!/perl/bin/perl.exe
$|=1;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# Web Softtware SA
# Date In: 2005-02-02
# Updates: 2005-02-03,2006-01-17,2016-02-02
# Author:Rodolfo Machon
# File Name: rept.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				= $cgiparms->param('id');
$passback   	= $cgiparms->param('passback');
$opener_form	= $cgiparms->param('opener_form');
$field_focus	= $cgiparms->param('field_focus');
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# CHECK PARAMETERS
if ($clase eq ""){print "Your are not passing CLASE parameter<br>";exit}
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
%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);
}
construct_page();
exit;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# MAIN SUB-ROUTINES
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] = "standard_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->{clase}->{keys};
    my $ws = "WHERE $where_field='$where_value'";
    return $ws;
}
sub get_record {
	my ($select_order,$where_statement)=@_;
	my @fields   = split(/\,/,$data->{clase}->{select_fields});
	my $order_by = "1";      
	my $table    = $data->{clase}->{table};
	my $database = $data->{clase}->{database};
	if($data->{clase}->{order_by}){$order_by = $data->{clase}->{order_by};}
    my $sql	= "SELECT $select_order FROM $table $where_statement ORDER BY $order_by";
	my $sth = $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_record');
	while (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(sort {$a<=>$b} 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 %all_ass_table = ();    
    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 ";    
                  }
            }
            my $order_by = "1";
            if($data->{clase}->{association}->{$ass[$i]}->{order_by}){$order_by  = $data->{clase}->{association}->{$ass[$i]}->{order_by};            }
            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}}."'";}
            if ($data->{clase}->{association}->{$ass[$i]}->{scope} eq "table"){
                my $rl_table = get_association_table($ass[$i],$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},$order_by);
                $all_ass_table{$ass[$i]} = $rl_table;
            }else{
                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},$order_by);
                $associations{$ass[$i]} = {%rl};
            }
      }
    }
    return %all_ass_table;
}
sub get_association_labels {
	my (%assos)=@_;
	my %all_ass_labels = ();
	foreach $key(sort {$a<=>$b} 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 $filter 	  = "";
		    my $database  = $data->{clase}->{association}->{$key}->{select_labels}->{$ass_fields[$i]}->{database};
		    my $fields    = $data->{clase}->{association}->{$key}->{select_labels}->{$ass_fields[$i]}->{fields};
		    my $table     = $data->{clase}->{association}->{$key}->{select_labels}->{$ass_fields[$i]}->{table};
		    my $id_field  = $data->{clase}->{association}->{$key}->{select_labels}->{$ass_fields[$i]}->{id_field};
		    my $doc_field = $data->{clase}->{association}->{$key}->{select_labels}->{$ass_fields[$i]}->{document_fields};
		    if ($data->{clase}->{association}->{$key}->{select_labels}->{$ass_fields[$i]}->{filters}){
			my $filter_value = "";
			my $filter_field = $data->{clase}->{association}->{$key}->{select_labels}->{$ass_fields[$i]}->{filters}->{fields};
			foreach $kk(sort {$a<=>$b} keys %{$assos{$key}}){
			    if($kk eq $filter_field){$filter_value = $assos{$key}{$kk}}
			}
			$filter .= "and ".$filter_field."='".$filter_value."'";
		    }
		    my %rl = get_label($database,$fields,$table,$id_field,$assos{$key}{$ass_docums[$i]},$doc_field,$filter);
		    $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/{city}/$city/g;
		$line =~ s/{state}/$state/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/{height}/$data->{height}/g;
	    $line =~ s/{width}/$data->{width}/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(keys %associations_labels){
                foreach $key1(keys %{$associations_labels{$key}}){$line =~ s/{$key1}/$associations_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};
    $city	  = $data->{portal}->{city};
    $state	  = $data->{portal}->{state};	
}
sub get_component_schema {
    my ($cmp)=@_;
    my $schema	= $cmp."_rept.xml";
    my $xml		= new XML::Simple;
      $data		= $xml->XMLin("../xml/".$schema);
      $path		= $data->{base_path}."/".$lan."/";
      $file		= $lan."_".$cmp."_rept.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 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 = "$mo\-$d\-$y";
	return $standard_timestamp;
} 
sub get_label {
	my ($db,$so,$fo,$field_id,$label_id,$do,$filter)=@_;    
	$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' $filter ORDER BY 1";
	my $sth 	= $dbh1->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh1,'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,$ob)=@_;    
	$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 $ob";
	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];
		  }
                #print "$la{$fields[$i]}<br>";    
	    }
      }else{
	    for($i=0;$i<=$#fields;$i++){
                $la{$fields[$i]} = "";
	    }
      }
      return %la;
}
sub get_association_table {
    my ($assoc,$db,$so,$fo,$WHERE,$do,$ob)=@_;    
    $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 $sql		= "SELECT $so FROM $fo $WHERE ORDER BY $ob";
	my $sth 	= $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_association_table');
	while (my @row_a = $sth->fetchrow_array) {
		for(my $i=0;$i<=$#fields;$i++){
            if($fields[$i] ne $sfields[$i]){
                if($data->{clase}->{association}->{$assoc}->{select_labels}->{$sfields[$i]}){
                    my $tabla   = $data->{clase}->{association}->{$assoc}->{select_labels}->{$sfields[$i]}->{table};
                    my $order_by = "1";
                    if($data->{clase}->{association}->{$assoc}->{select_labels}->{$sfields[$i]}->{order_by}){
                        $order_by  = $data->{clase}->{association}->{$assoc}->{select_labels}->{$sfields[$i]}->{order_by};
                    }                    
                    my $new_value = get_association_table_label($db,$fields[$i],$tabla,$sfields[$i],$row_a[$i],$order_by);
                    if($new_value ne ""){$row_a[$i]=$new_value}
                }
            }    
        }
        $fila       = join("|",@row_a);
        $fila 	    =~ s/\|/$patron/g;
        $filas    .=  "<TR class=\"my_style\"><TD>".$fila."</TD></TR>";
    }
    #print "$filas<br>";
    return $filas;
}
sub get_association_table_label {
	my ($db,$so,$fo,$field_id,$label_id,$ob)=@_;    
	$so 		=~ s/{lan}/$lan/g;
	my $label	= "";
	my $sql		= "SELECT $so FROM $fo WHERE $field_id='$label_id' ORDER BY $ob";
	my $sth 	= $dbh1->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh1,'sub get_association_table_label');
	while (my @row_b = $sth->fetchrow_array) {
            $label    = $row_b[0];
    }
    return $label;
}
sub license_ok {
      my ($license) = @_;
      $license =~ s/\-//g;
      my $ok = "false";
      $ref = {};
      $host = $ARGV[0];
      Win32::NetAdmin::GetTransports( $host, $ref ) || die "Could not get the MAC Address!";
      foreach $trans(keys %{$ref}){
            $mac = $ref-> {$trans}->{'transport_address'};
	    if($license eq $mac){
		  $ok = "true";
	    }
      }
      return $ok;
}
#------------------------------------------------------------------------- 