#!/usr/bin/perl use lib "/home/libs/modules" ; use lib "/home/libs/html" ; 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"); # my $string = "+279602011290457 # +279602011444254 # +27647925954 # +279602011886308 # +279602011715576 # +27605593071 # +279602011357390 # +279602011290459 # +279602011310279 # +279602011997711 # +279602011715022 # +279602011706979 # +279602011290451 # +279602011628623 # +279602011706607 # +27605592992 # +27716725757 # +279602011572528 # +279602011575456 # +279602011366919 # +279602011706920 # +279602011510386 # +279602011706336 # +279602011364458 # +279602011309234 # +279602011614887 # +27720533165 # +279602011503877 # +279602011576998 # +279602011444195 # +279602011818731 # +279602011575543 # +279602011506237 # +279602011230162 # +279602011681499 # +279602011625545 # +279602011367006 # +279602011706609 # +27723976037 # +279602011503901 # +279602011570904 # +279602011706297 # +279602011997709 # +27605592958 # +279602011706408 # +279602011577036 # +27605593163 # +279602011705878 # +279602011364497 # +279602011309470 # +279602011441195 # +279602011706427 # +279602011706989 # +279602011706997 # +279602011272309 # +279602011726176 # +279602011572561 # +27716723249 # +279602011705868 # +279602011997715 # +279602011507545 # +279602011705872 # +279602011441147 # +279602011440526 # +27720513208 # +279602011506226 # +279602011614879 # +279602011628640 # +279602011628668 # +27637371071 # +279602011628707 # +279602011290455 # +279602011570898 # +279602011706086 # +279602011443459 # +279602011507547 # +279602011576975 # +27605592424 # +279602011706730 # +279602011503883 # +279602011365842 # +279602011507567 # +279602011441219 # +279602011281152 # +279602011818848 # +27605593066 # +279602011706354 # +279602011625595 # +279602011444914 # +279602011441000 # +279602011628677 # +279602011624198 # +279602011357385 # +279602011997713 # +279602011358673 # +279602011715313 # +279602011705825 # +279602011706976 # +279602011444075 # +279602011274413 # +279602011309491 # +279602011628680 # +279602011706321 # +279602011681509 # +279602011705955 # +279602011357095 # +27797164232 # +279602011628701 # +279602011681543 # +279602011441189 # +279602011706485 # +279602011364327 # +279602011721199 # +279602011706470 # +279602011317909 # +279602011357418 # +279602011706530 # +279602011444194 # +279602011681445 # +279602011507758 # +27797192415 # +279602011309256 # +279602011503899 # +279602011507757 # +279602011628793 # +279602011705874 # +279602011443461 # +279602011706983 # +279602011706911 # +279602011706131 # +279602011503920 # +279602011441205 # +279602011309490 # +279602011357103 # +279602011444679" ; # # $string =~ s/\s{2,}/ /g; # # $string =~ s/\xA0//g; # $string =~ s/\n/\"\,\"/g; # # $string =~ s/\,/\"\,\"/g; # print "\n" ; # print "\"$string\"" ; # print "\n" ; 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 ; } #------------------------------------------------------------------------------------------ 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) ; } #------------------------------------------------------------------------------------------ sub list_screen { $print_box_content_rows .= &common_min_forms_start($lcpage) ; # $fcol = 2 ; # $print_box_content_rows .= qq~
~ ; # $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~
~ ; $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~~ ; $opts{network_test} = qq~~ ; $opts{system_test} = qq~~ ; $opts{test_event} = qq~~ ; &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 &fixedsystem_build_table_and_or_excel(1) ; $fnsortorder = 'desc' ; &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");~ ; } } #------------------------------------------------------------------------------------------ sub set_tick { my ($color,$tooltip) = @_ ; $tick{green} = qq~2~ ; $tick{orange} = qq~1~ ; $tick{red} = qq~0~ ; return ($tick{$color}) ; } #------------------------------------------------------------------------------------------ sub set_cross { my ($color,$tooltip) = @_ ; $tick{green} = qq~2~ ; $tick{orange} = qq~1~ ; $tick{red} = qq~0~ ; 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' ; } #------------------------------------------------------------------------------- sub 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~~ ; &common_min_select_opts('region_id','regions','name','','','code','','','',"id,code,name",'name') ; $selected{overall_status}{all} = 'SELECTED' ; $opts{overall_status} .= qq~~ ; $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 { &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~ ~; $print_box_content_rows .= &common_min_forms_end('','','save') ; $useropts{'common'}{'css'} .= qq~tbody tr td { font-size: 10px; } ~ ; require _blank ; exit ; } #------------------------------------------------------------------------------------------ sub redirect_screen { &common_min_alert_type ; print < $useropts{title}
ENDOFTEXT # exit ; } #------------------------------------------------------------------------------------------ use common ; use report ; use xlsxcreator ; use today ; use fixedsystem_test ; 1;