#!/usr/bin/perl

BEGIN { use lib '/usr/home/cfg' ; require push_inc ; }
    
require cfg ;

print "Content-type: text/html\n\n";

# unless ($useropts{it}{$username}) { print "<<<<<<<<<<<< MAINTENANCE IN PROGRESS >>>>>>>>>" ; exit ; }

#---------------------------------------------------------------------------------------------------------------------------------------------------------------------

use CGI::Carp qw(fatalsToBrowser);
use CGI;
use Mail::Sendmail;
use Fcntl qw(:flock);
use File::Copy;
use File::Basename;
use Date::Calc qw(:all);
use Excel::Writer::XLSX;
use Time::Piece ; 

&today;

#---------------------------------------------------------------------------------------------------------------------------------------------------------------------

@ARGV   = split(/\\*\&/, $ENV{'QUERY_STRING'});
$action = $ARGV[0] ;

our ($q)                 = CGI -> new() ;
our $iaction             = $q -> param('iaction')                || $action ;
our $isaved              = $q -> param('isaved')                 || '' ;

# our $debug = 1 ;
# our $testing = 1 ;

#-------- page opts --------------------------------------------------------------------------------------------------------------------------------------------------

&page_opts ;

#---------------------------------------------------------------------------------------------------------------------------------------------------------------------
	
our $srchscr   = 1 ;

&common_debug("iaction=$iaction");

our $open_new_tab = qq~target="_blank"~ ;	

if ($iaction eq 'save') { 
	&common_min_load_params ;
	&db_open_upd ;
		our $db_ignore_open_close = 1 ; # do one open and one close instead of repeating it	
			&report_ifields;
			&update ;
			# &list_screen ;
		$db_ignore_open_close = 0 ; # do one open and one close instead of repeating it
	&db_close_conn ;			
	# &screen3;		
	&redirect_screen;
} elsif ($iaction eq 'report') {  
	&common_min_load_params ;
	&db_open_ro ;
		our $db_ignore_open_close = 1 ; # do one open and one close instead of repeating it	
			&report_ifields;
			&list_screen ;   
		$db_ignore_open_close = 0 ; # do one open and one close instead of repeating it
	&db_close_conn ;			
	&screen3;
} elsif ($iaction eq '') {
	&report_screen  ; 
}	

exit;

#------------------------------------------------------------------------------------------

sub update {
	
	# foreach (keys %i) {
		# local @abc = split("_",$_) ;	
		# if (substr($_,0,14) eq 'online_status_') {
			# $seen_event_id{$abc[-2]} = 1 ;
		# }
	# }
	
	# local @event_ids = () ; 
	# foreach (keys %seen_event_id) {
		# push @event_ids , "`id` = '$_'" ; 
	# }
	# local $e_q_sql = join (" OR ",@event_ids) ;
	
	# &db_min_ro($table,'id,event_system_id_multiple,fixed_system_online_status,fixed_system_network_test,fixed_system_system_test,fixed_system_test_event,fixed_system_sound_test,fixed_system_overall_status,fixed_system_additional_comments',"$e_q_sql",'','') ;
	
	# my %ii = () ; my %iii = %i  ;
	# foreach (keys %i) {
		# local @a = split("_",$_) ;
		
		# if (substr($_,0,14) eq 'online_status_') {
			# $ii{$a[-2]}{fixed_system_online_status}{$a[-1]} = $i{$_} ;
		# } elsif (substr($_,0,13) eq 'network_test_') {
			# $ii{$a[-2]}{fixed_system_network_test}{$a[-1]} = $i{$_} ;
		# } elsif (substr($_,0,12) eq 'system_test_') {
			# $ii{$a[-2]}{fixed_system_system_test}{$a[-1]} = $i{$_} ;
		# } elsif (substr($_,0,11) eq 'test_event_') {
			# $ii{$a[-2]}{fixed_system_test_event}{$a[-1]} = $i{$_} ;
		# } elsif (substr($_,0,11) eq 'sound_test_') {
			# $ii{$a[-2]}{fixed_system_sound_test}{$a[-1]} = $i{$_} ;
		# } elsif (substr($_,0,15) eq 'overall_status_') {
			# $ii{$a[-2]}{fixed_system_overall_status}{$a[-1]} = $i{$_} ;
		# } elsif (substr($_,0,20) eq 'additional_comments_') {
			# $ii{$a[-2]}{fixed_system_additional_comments}{$a[-1]} = $i{$_} ;
		# } 
	# }
	
	# foreach my $event_id (sort keys %ii) {
		# %i = () ;
		# $upd_event{$event_id} = 0 ;
		
		# foreach my $col (keys %{$ii{$event_id}}) {	
			# foreach my $sys_id (split(/;/,$db{$table}{$event_id}{event_system_id_multiple})) {	
				# $i{$col} .= qq~$ii{$event_id}{$col}{$sys_id};~ ; 
				# # next unless $ii{$event_id}{$col}{$sys_id} ;
				# next if !$ii{$event_id}{$col}{$sys_id} && !$db{$table}{$event_id}{$col} ;
				# &common_debug("$col=$i{$col}");	
				# $upd_event{$event_id} = 1 ;
			# }
			# # chop $i{$col} if $i{$col} ;
		# }
		# # my $col_updates = qq~~ ; 
		# # foreach my $col (keys %{$ii{$event_id}}) {
			# # $ignore{$col} = 1 if $i{$col} eq $db{$table}{$event_id}{$col} ;	
			# # $col_updates .= qq~`$col`='$i{$col}',~ if $i{$col} ne $db{$table}{$event_id}{$col} ;	
		# # }
		# # chop $col_updates if $col_updates ;
		# next unless $upd_event{$event_id} ;
		# &common_debug("UPDATE $table WHERE id='$event_id'");	
		# &db_min_upd($table,"`id`='$event_id'") ; 		
	# }

	# %i = %iii ;
	
	&fixed_systems_update ;
	
} #------------------------------------------------------------------------------------------

sub report_ifields {
	
	# if ($i{customer_id}) {
		# push @report_sql,"`quote_to`='$i{customer_id}'" ;
		# &db_min_ro('customers','id,name',"`id`='$i{customer_id}'",'','') ; 
		# push @report_results,"`CLIENT` = '$db{customers}{$i{customer_id}}{name}'" ;
	# } else {
		# if ($is_installation_partner || $glob_custids{$userid}) {
			# @report_sql_or = () ;
			# foreach $_cust_id (keys %{$glob_custids{$userid}}) {
				# push @report_sql_or, "quote_to = '$_cust_id'"
			# }
			# my $sql_or = join(' OR ',@report_sql_or) ;
			# push @report_sql, "($sql_or)" if $sql_or ;
		# }
	# }
	# if ($i{region_id} and $i{region_id} ne 'all') {
		# push @report_sql,"`region_id`='$i{region_id}'" ;
		# &db_min_ro('regions','id,name,code',"`id`='$i{region_id}'",'','') ; 
		# push @report_results,"`REGION` = '$db{regions}{$i{region_id}}{name} [$db{regions}{$i{region_id}}{code}]'" ;
	# } else {
		# if ($is_schools_manager || $is_operator || $glob_regids{$userid}) {
			# @report_sql_or = () ;
			# foreach $_reg_id (keys %{$glob_regids{$userid}}) {
				# push @report_sql_or, "region_id = '$_reg_id'"
			# }
			# my $sql_or = join(' OR ',@report_sql_or) ;
			# push @report_sql, "($sql_or)" if $sql_or ;
		# }
	# }
		
	# if ($i{date_from} and $i{date_to}) { # check dates
		# my $date_from_check = $i{date_from} ;
		   # $date_from_check =~ s/\-//g ;
		# my $date_to_check = $i{date_to} ;
		   # $date_to_check =~ s/\-//g ;
		# if ($date_from_check > $date_to_check) { 
			# $error = qq~'$i{date_from}' > '$i{date_to}'~ ; &report_screen ; 
		# }
		# # push @report_sql, "((`date_from` >= '$i{date_from} 00:00:00' AND `date_from` <= '$i{date_to} 23:59:59') OR (`date_to` <= '$i{date_to} 23:59:59' AND `date_to` >= '$i{date_from} 00:00:00'))" ;
		# # push @report_sql, "(('$i{date_from} 23:59:59' >= `date_from` AND '$i{date_from} 00:00:00' <= `date_to`) OR ('$i{date_to} 23:59:59' >= `date_from` AND '$i{date_to} 00:00:00' <= `date_to`))" ;
		# # push @report_sql, "((`date_from` >= '$i{date_from} 00:00:00' AND `date_from` <= '$i{date_to} 23:59:59') OR (`date_to` >= '$i{date_from} 00:00:00' AND `date_to` <= '$i{date_to} 23:59:59'))" ;
		
		# my @srch_ed_from = &common_split_sql_time($i{date_from}) ;
		# my @srch_ed_to   = &common_split_sql_time($i{date_to}) ; 
		
		# &common_date_array("$srch_ed_from[0]$srch_ed_from[1]$srch_ed_from[2]","$srch_ed_to[0]$srch_ed_to[1]$srch_ed_to[2]") ;
		
		# @report_sql_or = () ;	
		
		# foreach my $_srch_date (@common_date_array) {
			# my $srch_ccyy_mm_dd = substr($_srch_date,0,4) . '-' . substr($_srch_date,4,2) . '-' . substr($_srch_date,6,2) ;
			# push @report_sql_or, "('$srch_ccyy_mm_dd 23:59:59' >= date_from AND '$srch_ccyy_mm_dd 00:00:00' <= date_to)"
		# }
		
		# my $sql_or = join(' OR ',@report_sql_or) ;
		# push @report_sql, "($sql_or)" if $sql_or ; 
		
		# push @report_results, "(BETWEEN '$i{date_from}' AND '$i{date_to}')" ;
	# } elsif (not $i{date_from}) {
		# $error = qq~PLEASE ENTER A START DATE!~ ;
		# &report_screen; 		
	# } elsif (not $i{date_to}) {
		# $error = qq~PLEASE ENTER AN END DATE!~ ; 
		# &report_screen; 
	# }
	
	# if ($i{overall_status} and $i{overall_status} ne 'all') {
		# &db_min_ro('fixed_system_status','id,status',"`id`='$i{overall_status}'",'','') ;
		# push @report_results,"`STATUS` = '$db{fixed_system_status}{$i{overall_status}}{status}'" ; 
		# push @report_sql, "(`fixed_system_overall_status` LIKE '$i{overall_status};%' OR `fixed_system_overall_status` LIKE '%;$i{overall_status};%' OR `fixed_system_overall_status` LIKE '%;$i{overall_status}')" ;
	# }
	
	# if ($i{system_id} and $i{system_id} ne '-1') {
		# &db_min_ro('event_systems',"id,name,description","`id`='$i{system_id}'",'','') ;
		# push @report_sql, "(event_system_id_multiple LIKE '%;$i{system_id};%' OR event_system_id_multiple LIKE '$i{system_id};%' OR event_system_id_multiple LIKE '%;$i{system_id}')" ;
		# push @report_results, "event_systems = `$db{event_systems}{$i{system_id}}{name} [$db{event_systems}{$i{system_id}}{description}]`" if $db{event_systems}{$i{system_id}}{description} ; 
		# push @report_results, "event_systems = `$db{event_systems}{$i{system_id}}{name}`" unless $db{event_systems}{$i{system_id}}{description} ; 
	# }
		
	# $report_results_msg = uc join(', ', @report_results) ;

	# if (not $report_results_msg) { 
		# $error = qq(ENTER AT LEAST ONE SEARCH PARAMETER); 
		# &report_screen; 
	# } else { 
		# $isaved = qq(SELECT WHERE $report_results_msg) ; 
	# }
	
	# our $srch_where_sql = join(' AND ',@report_sql) ; 
	
	&fixed_systems_report_ifields ;
 
} #------------------------------------------------------------------------------------------

sub list_screen {
	
	# $print_box_content_rows .= &common_min_forms_start($lcpage) ;
 
	# # $fcol = 2 ; 

	# # $print_box_content_rows .= qq~<div style = "display:none;">~ ;
	# # $print_box_content_rows .= &common_min_form_input_col('customer_id',"$i{customer_id}") ;  
	# # $print_box_content_rows .= &common_min_form_input_col('region_id',"$i{region_id}") ;  
	# # $print_box_content_rows .= &common_min_form_input_col('date_from',"$i{date_from}") ;  
	# # $print_box_content_rows .= &common_min_form_input_col('date_to',"$i{date_to}") ; 
	# # $print_box_content_rows .= &common_min_form_input_col('overall_status',"$i{overall_status}") ;  
	# # $print_box_content_rows .= qq~</div>~ ;
	
	# $fcol = 12 ; 
	
	# my $report = 1 ; 
	
	# our $xlsxdir = "fixed_systems_report" ; 
	# our $xlsxreportname = "Fixed_Systems_Report" ; 
	# my $date = &common_write_date_interval($i{date_from},$i{date_to}) ;
	# our $xlsx_title_heading = "Fixed System Report $date" ;

	# &common_min_select_opts('overall_status','fixed_system_status','status','',0,'','','','',"",'name') ;
	# &common_min_select_opts('sound_test','fixed_system_sound_test','name','',0,'','','','',"",'name') ;
	# $opts{online_status} = qq~<option value="-1">Offline</option><option value="1">Online</option>~ ; 
	# $opts{network_test} = qq~<option value="-1">Failed</option><option value="1">Passed</option>~ ; 
	# $opts{system_test} = qq~<option value="-1">Failed</option><option value="1">Passed</option>~ ; 
	# $opts{test_event} = qq~<option value="-1">Not Good</option><option value="1">Good</option>~ ;
	
	# &db_min_ro('event_systems','*',"`system_type` = 'fixed'",'','') ;
	# &db_min_ro('regions','*','','','')  ; 
	# &db_min_ro('organisations','*','','','')  ; 
 
	&db_min_ro($table,'id,ref,organisation_ids,region_id,date_from,date_to,event_system_id_multiple,club_ids,operator_ids,fixed_system_online_status,fixed_system_network_test,fixed_system_system_test,fixed_system_test_event,fixed_system_sound_test,fixed_system_overall_status,fixed_system_additional_comments',"$srch_where_sql AND `quote_accepted` = '1'",'','') ; #### $srch_where_sql AND  `id` = 5136

	&db_switch_conn('sss') ;
	
	my $table_sss = "$table sss" ;
	
	my $sss_where = join (" OR ",map { "aisa_quote_nr='$_'" } keys %{$db{$table}}) ;
 
	$sss_where = ($sss_where) ? "($sss_where) AND aisa_quote_nr <> '0'" : "" ;
	
	if ($sss_where) {
		
		&db_min_ro($table_sss,'aisa_quote_nr,supplier_type_ids,supplier_ids',"($sss_where) AND aisa_quote_nr <> '0'",'','') ; #### $srch_wh

		foreach my $id (keys %{$db{$table_sss}}) {

			$db{$table}{$id}{supplier_ids} = $db{$table_sss}{$id}{supplier_ids} ;
			$db{$table}{$id}{supplier_type_ids} = $db{$table_sss}{$id}{supplier_type_ids} ;
			
		}
	}
	
	&db_switch_conn('aisa') ;
		
	# &fixedsystem_build_table_and_or_excel(1) ; 

	# $fnsortorder = 'asc' ;
	
	# &common_min_extra_crumb("$lcpage","Search Screen") ;
 
	# # if $glod_user_level > 2 ;
	
	# if ($glod_user_level > 2 || $is_installation_partner || $is_schools_manager) {
		# $trigger_jquery_raw .= qq~\$("#savebutt").click(function() { \$("#$lcpage-form").submit(); });~ ;
	# } else {
		# $trigger_jquery_raw .= qq~\$("#savebutt").css("display","none");~ ;
	# }
	
	&fixed_systems_list_screen ;
	
} #------------------------------------------------------------------------------------------

# sub set_tick {
	
	# my ($color,$tooltip) = @_ ;
		
	# $tick{green}  = qq~<span style="display:none;">2</span><span class="label label-success"><i $tooltip class="glyphicon glyphicon-ok"></i></span>~ ;
	# $tick{orange} = qq~<span style="display:none;">1</span><span class="label label-warning"><i $tooltip class="glyphicon glyphicon-ok"></i></span>~ ;
	# $tick{red}    = qq~<span style="display:none;">0</span><span class="label label-danger"><i $tooltip class="glyphicon glyphicon-ok"></i></span>~ ;

	# return ($tick{$color}) ;
			
# } #------------------------------------------------------------------------------------------

# sub set_cross {
	
	# my ($color,$tooltip) = @_ ;
		
	# $tick{green}  = qq~<span style="display:none;">2</span><span class="label label-success"><i $tooltip class="glyphicon glyphicon-remove"></i></span>~ ;
	# $tick{orange} = qq~<span style="display:none;">1</span><span class="label label-warning"><i $tooltip class="glyphicon glyphicon-remove"></i></span>~ ;
	# $tick{red}    = qq~<span style="display:none;">0</span><span class="label label-danger"><i $tooltip class="glyphicon glyphicon-remove"></i></span>~ ;

	# return ($tick{$color}) ;
			
# } #------------------------------------------------------------------------------------------

sub thead {
	
	&common_min_thead ;
	
} #-------------------------------------------------------------------------------

sub page_opts {

	# our $glyphicon = 'list' ;
	# our $lcpage    = 'fixed-systems-report' ;

	# &common_page_name ;
	   
	# our $table     = 'event_quotes' ;
	# $page_title    = 'Fixed Systems Report' ;

	&fixed_systems_page_opts ;

} #-------------------------------------------------------------------------------

sub report_screen {
	
	&fixed_systems_report_screen ;
	
	# our $lcol = 3 ;
	# our $fcol = 5 ;

	# $print_box_content_rows .= &common_min_forms_start('report') ;

	# my ($sql_next_day) = &common_add_delta_days(1) ;
	# my ($next_year,$next_month,$next_day) = split(/\-/,$sql_next_day) ;
	
	# $preferred_title{customer_id} = "Client" ;
	# # $selected{customer_id}{5} = 'SELECTED' if $glod_user_level < 4 ; # 5 = SuperSport Schools (Pty) Ltd
	
	# if ($is_installation_partner || $glob_custids{$userid}) {
		# foreach $_cust_id (keys %{$glob_custids{$userid}}) {
			# $filter_val{customers}{$_cust_id} = 1 ;
		# }
		# $filter_select_opts{customers} = 1 ;
	# }
	
	# &common_min_select_opts('customer_id','customers','name',5,'','','','','',"id,name",'name') ;
	
	# if ($is_schools_manager || $is_operator || $glob_regids{$userid}) {
		# foreach $_reg_id (keys %{$glob_regids{$userid}}) {
			# $filter_val{regions}{$_reg_id} = 1 ;
		# }
		# $filter_select_opts{regions} = 1 ;
	# }
	
	# $opts{region_id} .= qq~<option value="all" SELECTED>All</option>~ ; 
	# &common_min_select_opts('region_id','regions','name','','','code','','','',"id,code,name",'name') ;	
	
	# $selected{overall_status}{all} = 'SELECTED' ;
	# $opts{overall_status} .= qq~<option value="all" $selected{overall_status}{all} >All</option>~ ; 
	# $preferred_title{overall_status} = "Status" ;
	# &common_min_select_opts('overall_status','fixed_system_status','status','',0,'','','','',"",'name') ;
	
	# $print_box_content_rows .= &common_min_form_select('customer_id','') ; 
	# $print_box_content_rows .= &common_min_form_select('region_id','') ; 
	
	# &common_min_select_opts('system_id','event_systems','name','','','description','','','','','name') ;
	# $print_box_content_rows .= &common_min_form_select('system_id','') ;
	
	# $print_box_content_rows .= &common_min_form_datepicker('date_from',"$next_year-$next_month-$next_day") ; #unless $i{date_from} ;  
	# $print_box_content_rows .= &common_min_form_datepicker('date_to',"$next_year-$next_month-$next_day") ;
	# $print_box_content_rows .= &common_min_form_select('overall_status','') ; 
	
	# $print_box_content_rows  .= &common_min_forms_end('','','report') ;

	# &common_min_search_screen ;

} #-------------------------------------------------------------------------------

sub screen3 {

	# $custom_column_styles{online_status} = qq~style="width:8%;"~ ;
	# $custom_column_styles{network_test} = $custom_column_styles{online_status} ;
	# $custom_column_styles{system_test} = $custom_column_styles{online_status} ;
	# $custom_column_styles{test_event} = $custom_column_styles{online_status} ;
	# $custom_column_styles{sound_test} = $custom_column_styles{online_status} ;
	# $custom_column_styles{overall_status} = $custom_column_styles{online_status} ;

	# &thead;

	# if ($isaved) { $alert = &common_min_alert('success',"$ucpage $isaved!",'ok') ; } else { &common_min_alert_type ; }

	# # my $page = "$lcpage\s" ;

	# my $page = "event-quotes" ;

	# if ($s{no}) { $page = $lcpage ; }

	# &common_min_table('id',$page,'list') ;

	# our $savjqy = 1 ;

	# $extra_form_fields .= qq~
	# <input type="hidden" name="date_from" value="$i{date_from}">
	# <input type="hidden" name="date_to" value="$i{date_to}">
	# <input type="hidden" name="region_id" value="$i{region_id}">
	# <input type="hidden" name="customer_id" value="$i{customer_id}">
	# <input type="hidden" name="overall_status" value="$i{overall_status}">~;

	# $print_box_content_rows .= &common_min_forms_end('','','save') ;

	# $useropts{'common'}{'css'} .= qq~tbody tr td { font-size: 10px; } ~ ;

	# require _blank ;

	# exit ;

	&fixed_systems_screen3 ;

} #------------------------------------------------------------------------------------------

sub redirect_screen {
	
&common_min_alert_type ;

print <<ENDOFTEXT;
<!DOCTYPE html>
<html lang="en">
<head>    
    <meta charset="utf-8">
    <title>$useropts{title}</title>
    <script src="$useropts{'bower_components'}/jquery/jquery.min.js"></script>   
</head>
<body>
<script>

\$(document).ready(function(){ \$('#$lcpage-form').submit(); })

</script>
</body>

<form role="form" id="$lcpage-form" method="post" action="$lcpage">
<input type="hidden" name="iaction"				value="report">
<input type="hidden" name="date_from"        	value="$i{date_from}">
<input type="hidden" name="date_to"          	value="$i{date_to}">
<input type="hidden" name="region_id" 			value="$i{region_id}">
<input type="hidden" name="customer_id" 		value="$i{customer_id}">
<input type="hidden" name="overall_status"		value="$i{overall_status}">
<input type="hidden" name="isaved"				value="$success">
</form>

</html>
ENDOFTEXT
#

exit ;

} #------------------------------------------------------------------------------------------

use common ;
use report ;
use xlsxcreator ;
use today ;
use fixed_systems ;

1;