#!/perl/bin/perl.exe
$|=1;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# Author:Rodolfo Machon
# Company:WebSoft, SA de CV 
# Nombre del archivo: qwsp.pl
# Created: 2006-07-13,2016-08-03
# Updates: 2006-07-13, 2011-06-21,2016-08-03
# Copyright - All Rights Reserved
#------------------------------------------------------------------------- 
#-------------------------------------------------------------------------
# PERL MODULES
use CGI;
use CGI::Carp qw(fatalsToBrowser);
use DBI;
use XML::Simple;
use Data::Dumper;
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');
$filters	= $cgiparms->param('filters');
$filters_values	= $cgiparms->param('filters_values');
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# GET CLASE SCHEMA AND VARIABLES
get_app_data();
get_component_schema($clase);
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# CHECK PARAMETERS
if ($clase eq ""){print "Your are not passing CLASE parameter<br>";}
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# 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;
$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;
#------------------------------------------------------------------------- 
#-------------------------------------------------------------------------
#MAIN PROGRAM
if($filters eq ""){$filters = $data->{source}->{filters}->{fields}}
($grid_header,$field_reference)	= get_grid_header();
$master_select_order  	 		= get_select_order();
$where_statement				= get_where_statement();

($master_select_values,$master_labels_values) = get_records_stream($master_select_order,$where_statement);

construct_page();
exit;
#-------------------------------------------------------------------
# SUB-ROUTINES
sub get_filters {
    my $s_filters = "";
    my @fields	  = split(/\,/,$data->{source}->{filters}->{fields});
    for (my $i=0;$i<=$#fields;$i++){
		my $type  = $data->{source}->{filters}->{$filters[$i]}->{type};
		if($type eq "get"){$s_filters .= $fields[$i].",";}
    }
    if($s_filters ne ""){$s_filters = substr($s_filters,0,length($s_filters)-1);}
    return $s_filters;
}
sub get_grid_header {
      my $grid_columns	= $data->{source}->{grid_columns};
      my @fields 		= split(/\,/,$grid_columns);
      my $hd 			= "";
      my $fr 			= "";
      for(my $i=0;$i<=$#fields;$i++){
	    my $properties = "";
	    $hd .= $fields[$i]."|";
        $hd .= $data->{source}->{column}->{$fields[$i]}->{header}."|";
	    $hd .= $data->{source}->{column}->{$fields[$i]}->{width}."|";
	    $hd .= $data->{source}->{column}->{$fields[$i]}->{element_type}."|";
	    %element_properties = %{$data->{source}->{column}->{$fields[$i]}->{element_properties}};
	    foreach $key(keys %element_properties){
			if($element_properties{$key} ne "NO"){
				if($key ne "content"){$properties .= $key."=".$element_properties{$key}." ";}
			}
	    }
	    $properties = substr($properties,0,length($properties)-1);
	    $hd .= $properties."~~";
	    if($data->{source}->{column}->{$fields[$i]}->{component}){$fr .= $data->{source}->{column}->{$fields[$i]}->{component}."~~"}
      }
      return ($hd,$fr);
}
sub get_select_order {
	my $select_fields = $data->{source}->{select_fields};
	$select_order="";
	@displayed_fields = split(/\,/,$select_fields);
	for ($w=0;$w<=$#displayed_fields;$w++){
		$fields[$w]= $displayed_fields[$w];
		#$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 $pair 		= "";
    my $ws 		 	= "";
    #my $value		= "";
    #$str			= "";
    my @a_filters 	 	= split(/\,/,$filters);
    my @a_filters_values= split(/\|/,$filters_values);
    my @fields		 	= split(/\,/,$data->{source}->{filters}->{fields});

    for (my $i=0;$i<=$#fields;$i++){
		my $type  = $data->{source}->{filters}->{field}->{$fields[$i]}->{type};
		my $valor = $data->{source}->{filters}->{field}->{$fields[$i]}->{value};
		
		#if($type eq "post"){$pair .= "$fields[$i]='$valor' and ";}
		if($type eq "post"){
			my $nvals= ""; 
			my @vals = split(/\,/,$valor);
			for (my $j=0;$j<=$#vals;$j++){			
				if (substr($fields[$i],0,1) eq "i"){$nvals .= $vals[$j].",";}
				if (substr($fields[$i],0,1) eq "c"){$nvals .= "'".$vals[$j]."',";}
				if (substr($fields[$i],0,1) eq "v"){$nvals .= "'".$vals[$j]."',";}
			}
			$nvals = substr($nvals,0,length($nvals)-1);
			$pair .= "$fields[$i] IN ($nvals) and ";
		}		
		if($type eq "get"){
			for (my $j=0;$j<=$#a_filters;$j++){
				#$str .= $fields[$i]."|".$a_filters[$j]."~";
				if($fields[$i] eq $a_filters[$j]){
					if($a_filters_values[$j] ne ""){$pair .= $fields[$i]."='".$a_filters_values[$j]."' and ";}
				}
			}
		}
    }
    if($pair ne ""){$pair = substr($pair,0,length($pair)-5);$ws = "WHERE ".$pair}
    return $ws;
}
sub get_records_stream {
    my ($select_order,$ws)=@_;
    my $select_fields 	= $data->{source}->{select_fields};
    my $table 			= $data->{source}->{table};
    my $db 				= $data->{source}->{database};    
    my @fields1			= split(/\,/,$select_fields);
    my $stream_select_values = "";
    my $stream_labels_values = "";	
    my $sql = "SELECT $select_order FROM $table $ws ORDER BY 1";
    $mquery = $sql;
    #print "$sql<br>";
	my $sth = $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_records');
	while (my @row = $sth->fetchrow_array) {
        $rs="";$rl="";
        for($i=0;$i<=$#fields1;$i++){
            if(substr($fields1[$i],0,2) eq "ts"){
                $rf = $row[$i];		
            }else{
                $rf = $row[$i];
            }
            $rs = $rs.$rf."|";
            if ($data->{source}->{select_labels}->{$fields1[$i]}){
                my $fid = "";
                #print "<br>"..$data->{source}->{select_labels}->{$fields1[$i]}->{id_type}."<br>";
                if ($data->{source}->{select_labels}->{$fields1[$i]}->{id_type} eq "PIVOT"){$fid=$data->{source}->{select_labels}->{$fields1[$i]}->{id_field}}else{$fid=$row[$i]}
                %rl = get_labels(
                                 $data->{source}->{select_labels}->{$fields1[$i]}->{database},
                                 $data->{source}->{select_labels}->{$fields1[$i]}->{fields},                                     
                                 $data->{source}->{select_labels}->{$fields1[$i]}->{table},
                                 $fid,
                                 $row[$i],
                                 $data->{source}->{select_labels}->{$fields1[$i]}->{document_fields}
                                 );
                foreach $key(keys %rl){
                    $rs = $rs.$rl{$key}."|";		    
                }
            }
        }
        $stream_select_values = $stream_select_values.$rs."~~";
    }
    if($stream_select_values eq ""){$stream_select_values="~~"}
    return ($stream_select_values,$stream_labels_values);
}
sub construct_page {
    my $tdheader_style	= $data->{source}->{tdheader_style};
    my $tdbody_style	= $data->{source}->{tdbody_style};
    my $bodyrows	= $data->{source}->{bodyrows};
    my $doc = $grid_header."^^".$master_select_values."^^".$field_reference."^^".$filters."^^".$filters_values."^^".$tdheader_style."^^".$tdbody_style."^^".$bodyrows."^^";
    $doc =~ s/\r\n/\\n/g;
    $doc =~ s/\r/\\n/g;
    print $doc;      
}
#-------------------------------------------------------------------
#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."_qwsr.xml";
    my $xml          = new XML::Simple;
    $data 	  = $xml->XMLin("../xml/".$schema);
    $mquery	  = "";
}
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 = "$mo\-$d\-$y";
	return $standard_timestamp;
}
sub get_labels {
    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";
    #print "$sql<br>";
	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;
}
#-------------------------------------------------------------------