#!/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 ; &db_open_ro ; our $db_ignore_open_close = 1 ; # do one open and one close instead of repeating it &report_screen if $iaction eq '' ; if ($iaction eq 'report') { &common_min_load_params ; &report_ifields; &list_screen ; &screen3 ; } if ($iaction eq 'save') { &common_min_load_params ; &update ; &screen4 ; } &common_min_action ; $db_ignore_open_close = 0 ; # do one open and one close instead of repeating it &db_close_conn ; exit; #------------------------------------------------------------------------------------------ sub update { &db_min_ro($table,'id,logistics_dates_paid,logistics_amounts_paid,logistics_suppliers,logistics_amounts_paid_by','logistics_dates_paid IS NOT NULL OR logistics_amounts_paid IS NOT NULL OR logistics_suppliers IS NOT NULL','','') ; my %iii = %i ; my %ii = () ; foreach (sort keys %i) { local $cell_name = $_ ; local @a = split(/\_/,$cell_name) ; if ($_ =~ /paid_/ && $i{"paid_$a[-2]\_$a[-1]"}) { my $amnt_payable_field = "amount_payble_$a[-2]\_$a[-1]" ; $i{$amnt_payable_field} = sprintf("%.2f",$i{$amnt_payable_field}) ; $ii{$a[-1]}{$a[-2]}{logistics_amounts_paid} .= qq~$i{$amnt_payable_field}~ ; $ii{$a[-1]}{$a[-2]}{logistics_dates_paid} .= qq~$now_year-$now_mm-$now_dd~ ; $ii{$a[-1]}{$a[-2]}{logistics_amounts_paid_by} .= qq~$userid~ ; } } foreach my $event_id (sort keys %ii) { %i = () ; $i{logistics_dates_paid} = qq~$ii{$event_id}{from}{logistics_dates_paid};$ii{$event_id}{to}{logistics_dates_paid}~ if $ii{$event_id}{from}{logistics_dates_paid} || $ii{$event_id}{to}{logistics_dates_paid} ; $i{logistics_amounts_paid} = qq~$ii{$event_id}{from}{logistics_amounts_paid};$ii{$event_id}{to}{logistics_amounts_paid}~ if $ii{$event_id}{from}{logistics_amounts_paid} || $ii{$event_id}{to}{logistics_amounts_paid} ; $i{logistics_amounts_paid_by} = qq~$ii{$event_id}{from}{logistics_amounts_paid_by};$ii{$event_id}{to}{logistics_amounts_paid_by}~ if $ii{$event_id}{from}{logistics_amounts_paid_by} || $ii{$event_id}{to}{logistics_amounts_paid_by} ; # $i{logistics_suppliers} = qq~$ii{$event_id}{from}{logistics_suppliers}-$ii{$event_id}{to}{logistics_suppliers}~ if $ii{$event_id}{from}{logistics_suppliers} || $ii{$event_id}{to}{logistics_suppliers} ; $i{logistics_amounts_paid} = qq~$db{$table}{$event_id}{logistics_amounts_paid}|$i{logistics_amounts_paid}~ if $db{$table}{$event_id}{logistics_amounts_paid} && $i{logistics_amounts_paid} ; $ignore{logistics_amounts_paid} = 1 if (!$i{logistics_amounts_paid} && !$db{$table}{$event_id}{logistics_amounts_paid}) || $i{logistics_amounts_paid} eq $db{$table}{$event_id}{logistics_amounts_paid} ; $i{logistics_dates_paid} = qq~$db{$table}{$event_id}{logistics_dates_paid}|$i{logistics_dates_paid}~ if $db{$table}{$event_id}{logistics_dates_paid} && $i{logistics_dates_paid} ; $ignore{logistics_dates_paid} = 1 if (!$i{logistics_dates_paid} && !$db{$table}{$event_id}{logistics_dates_paid}) || $i{logistics_dates_paid} eq $db{$table}{$event_id}{logistics_dates_paid} ; $i{logistics_amounts_paid_by} = qq~$db{$table}{$event_id}{logistics_amounts_paid_by}|$i{logistics_amounts_paid_by}~ if $db{$table}{$event_id}{logistics_amounts_paid_by} && $i{logistics_amounts_paid_by} ; $ignore{logistics_amounts_paid_by} = 1 if (!$i{logistics_amounts_paid_by} && !$db{$table}{$event_id}{logistics_amounts_paid_by}) || $i{logistics_amounts_paid_by} eq $db{$table}{$event_id}{logistics_amounts_paid_by} ; # $ignore{logistics_suppliers} = 1 if (!$i{logistics_suppliers} && !$db{$table}{$event_id}{logistics_suppliers}) || $i{logistics_suppliers} eq $db{$table}{$event_id}{logistics_suppliers} ; &db_min_upd("$table","id='$event_id'") if $i{logistics_amounts_paid} || $i{logistics_dates_paid} ; } %i = %iii ; } #------------------------------------------------------------------------------------------ sub load_search_vars { &db_min_ro('logistics_suppliers','*',"`id`='$i{supplier_id}'",'','') if $i{supplier_id} ; &db_min_ro('logistics_locations','*',"`id`='$i{system_location_id}'",'','') if $i{system_location_id} ; } #------------------------------------------------------------------------------------------ sub report_ifields { &load_search_vars ; if ($i{date_from} && !$i{date_to}) { # check dates push @report_sql, "`date_from` >= '$i{date_from} 00:00:00'" ; ############################################### > OR >= push @report_results, "(EVENT AFTER '$i{date_from}')" ; } elsif (!$i{date_from}) { $error = qq~PLEASE ENTER A START DATE!~ ; &report_screen; } elsif ($i{date_from} && $i{date_to}) { # check dates # push @report_sql, "(('$i{date_from} 00:00:00' <= `date_from` AND `date_from` <= '$i{date_to} 23:59:59') OR ('$i{date_from} 00:00:00' <= `date_to` AND `date_to` <= '$i{date_to} 23:59:59'))" ; ############################################### > OR >= push @report_sql, "( ('$i{date_from} 00:00:00' <= `date_from` AND `date_from` <= '$i{date_to} 23:59:59') OR ('$i{date_from} 00:00:00' <= `date_to` AND `date_to` <= '$i{date_to} 23:59:59') OR ('$i{date_from} 00:00:00' >= `date_from` AND `date_to` >= '$i{date_to} 23:59:59') OR ('$i{date_from} 00:00:00' <= `date_from` AND `date_to` <= '$i{date_to} 23:59:59') )" ; ############################################### > OR >= push @report_results, "(EVENT BETWEEN '$i{date_from}' AND '$i{date_to}')" ; } if ($i{client_id} && $i{client_id} ne 'all') { &db_min_ro('customers','1,name',"id='$i{client_id}'",'','') ; push @report_sql, "(`quote_to` = '$i{client_id}')" ; push @report_results, "Client = `$db{customers}{1}{name}`" ; } # if ($i{organisation_ids}) { # push @report_sql, "(FIND_IN_SET($i{organisation_ids},organisation_ids))" ; # push @report_results, "Venue = `$db{organisations}{$i{organisation_ids}}{name}`" ; # } # if ($i{system_location_id}) { # push @report_sql, "logistics_system_location_id='$i{system_location_id}'" ; # push @report_results, "System Location = `$db{logistics_locations}{$i{system_location_id}}{location}`" ; # } else { # push @report_sql,"logistics_system_location_id != '1'" ; # } if ($i{supplier_id}) { # push @report_sql, "((logistics_suppliers REGEXP '(^|;)[^-]*-$i{supplier_id}(\$\|;)') OR (logistics_suppliers REGEXP '(^|;)($i{supplier_id})-[^;]*(\$\|;)'))" ; push @report_sql, "(logistics_suppliers LIKE '%-$i{supplier_id}' OR logistics_suppliers LIKE '$i{supplier_id}-%')" ; push @report_results, "Supplier = `$db{logistics_suppliers}{$i{supplier_id}}{supplier}`" ; } else { push @report_sql, "(logistics_suppliers NOT LIKE '%-2' OR logistics_suppliers NOT LIKE '2-%')" ; } if ($i{operator_id}) { &db_min_ro('users','id,name',"`user_type` LIKE 'casual_%'",'','') ; push @report_results, "Operator Is `$db{users}{$i{operator_id}}{name}`" ; push @report_sql, "FIND_IN_SET('$i{operator_id}',REPLACE(logistics_shipment_operator_id,'-',','))" ; } # if ($is_dcb_partner && $i{status_id} != 1) { $i{status_id} = 1 ; } # if ($i{status_id}) { # local %status_hash = (1 =>"Booked",2 => "Pending",3=>"In Transit",4 =>"Delivered") ; # push @report_results, "Status = `$status_hash{$i{status_id}}`" ; # push @report_sql, "((logistics_status_ids REGEXP '(^|;)[^-]*-$i{status_id}(\$\|;)') OR (logistics_status_ids REGEXP '(^|;)($i{status_id})-[^;]*(\$\|;)'))" ; # } # if ($i{region_id}) { # push @report_sql, "`region_id` = '$i{region_id}'" ; # push @report_results, "Region = `$db{regions}{$i{region_id}}{name} [$db{regions}{$i{region_id}}{code}]`" ; # } push @report_sql , "logistics_shipment_operator_id IS NOT NULL AND logistics_shipment_operator_id != '-'" ; $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('logistics-payments') ; our $open_new_tab = qq~target="_blank"~ ; # opens booking page in a new tab my $srch_where_sql = join(' AND ',@report_sql) ; our @sql_col_display = ("event_quote_nr","event_name","client_name","from_or_to_event","from","to","city","supplier","date_of_event","delivery_date","operator_name","default_amount","amount_date_paid","amount_paid_by","amount_paid","amount_payable","paid") ; foreach (reverse @sql_col_display) { $lastchild++ ; $last_child{$_} = $lastchild ; } # &common_debug("last-child : $_ [$last_child{$_}]"); &report_xlsx_export_header("Logistics Payments Report",'logistics_payments') ; &load_list_vars("$srch_where_sql","$where_min") ; my $field = "" ; my @qty_work = () ; my @amnt_usd_work = () ; my @amnt_work = () ; my @op_work = () ; my @description = () ; my @paid_work = () ; my $row_cnt = 0 ; my $first_id = 0 ; my $last_id = 0 ; my %row_cnt_to_id = () ; $fcol = 2 ; $print_box_content_rows .= qq~
~ ; $print_box_content_rows .= &common_min_form_input_col("date_from",$i{date_from}) ; #date_from_i_field $print_box_content_rows .= &common_min_form_input_col("date_to",$i{date_to}) ; #date_to_i_field $print_box_content_rows .= &common_min_form_input_col("event_quote_id",$i{event_quote_id}) ; $print_box_content_rows .= &common_min_form_input_col("supplier_id",$i{supplier_id}) ; $print_box_content_rows .= &common_min_form_input_col("paid_status",$i{paid_status}) ; $print_box_content_rows .= qq~
~ ; $fcol = 12 ; my $valxlsx = 0 ; my $table_row_cnt = 0 ; my $f_t_event_ids = qq~~ ; my $op_ids = qq~~ ; my %seen_op = () ; my %xlsxrow_ops = () ; my @from_to = ('to','from') ; $f_t_pos{from} = 0 ; $f_t_pos{to} = 1 ; our @month_names = ("","Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec") ; our @months = @month_names ; my $amounts_ids = qq~~ ; &db_min_ro('users all_users','id,username','','','') ; foreach my $id (sort {$a <=> $b} keys %{$db{$table}}) { next unless $db{customers}{$db{$table}{$id}{quote_to}}{id} ; my %tot_amnt_paid = () ; my %dates_paid_tooltip = () ; my @date_payable = () ; my $cnt = 0 ; my @date_pay = split(/\|/,$db{$table}{$id}{logistics_dates_paid}) ; my @amount_pay = split(/\|/,$db{$table}{$id}{logistics_amounts_paid}) ; my @amount_pay_by = split(/\|/,$db{$table}{$id}{logistics_amounts_paid_by}) ; foreach my $date_pay_1 (@date_pay) { my $amount_pay_1 = $amount_pay[$cnt] ; $cnt++ ; my @date_pay_2 = split(/\;/,$date_pay_1) ; my @amount_pay_2 = split(/\;/,$amount_pay_1) ; $tot_amnt_paid{from} += $amount_pay_2[0] ; $tot_amnt_paid{to} += $amount_pay_2[1] ; my $date_string_1 = &common_min_date_as_string($date_pay_2[0]) ; my $date_string_2 = &common_min_date_as_string($date_pay_2[1]) ; # # # # $dates_paid_tooltip{from} .= qq~
~ if $dates_paid_tooltip{from} ; # # # # $dates_paid_tooltip{to} .= qq~
~ if $dates_paid_tooltip{to} ; # # # # $dates_paid_tooltip{from} .= qq~$amount_pay_2[0] PAID $date_string_1~ if $date_string_1 ; # # # # $dates_paid_tooltip{to} .= qq~$amount_pay_2[1] PAID $date_string_2~ if $date_string_2 ; $dates_paid_tooltip{from} .= qq~$date_string_1~ if $date_string_1 ; $dates_paid_tooltip{to} .= qq~$date_string_2~ if $date_string_2 ; my @amount_pay_2_by = split(/\;/,$amount_pay_by[$cnt-1]) ; $paid_by_tooltip{from} = $db{'users all_users'}{$amount_pay_2_by[0]}{username} ; $paid_by_tooltip{to} = $db{'users all_users'}{$amount_pay_2_by[1]}{username} ; } my @suppliers = split(/\-/,$db{$table}{$id}{logistics_suppliers}) ; my @operators = split(/\-/,$db{$table}{$id}{logistics_shipment_operator_id}) ; foreach my $f_t (@from_to) { # next if $suppliers[$f_t_pos{$f_t}] eq '2' || $db{$table}{$id}{logistics_system_location_id} eq '1' ; next if !$AISA_suppliers{$suppliers[$f_t_pos{$f_t}]} || !$operators[$f_t_pos{$f_t}] ; my $cost_of_shipment = $db{event_cost_items}{1}{rates} ; foreach (keys %shipments_cost) { if (($f_t eq 'to' && &common_min_greater_date_time("$shipments_cost{$_}{valid_from} 00:00:00",$db{$table}{$id}{date_from}) && &common_min_greater_date_time($db{$table}{$id}{date_from},"$shipments_cost{$_}{valid_to} 23:59:59")) || ($f_t eq 'from' && &common_min_greater_date_time("$shipments_cost{$_}{valid_from} 00:00:00",$db{$table}{$id}{date_to}) && &common_min_greater_date_time($db{$table}{$id}{date_to},"$shipments_cost{$_}{valid_to} 00:00:00"))) { $cost_of_shipment = $shipments_cost{$_}{cost} ; last ; } } local $amnt_owed = $cost_of_shipment - $tot_amnt_paid{$f_t} ; $amnt_owed = 0 if $tot_amnt_paid{$f_t} || $dates_paid_tooltip{$f_t} ; next if $i{paid_status} eq 'outstanding' && $amnt_owed <= 0 ; next if $i{paid_status} eq 'paid' && $amnt_owed > 0 ; $amnt_owed = &common_commify(sprintf("%.2f",$amnt_owed)) ; $column_count++ ; $xlsxcol = 0 ; $print_tbody .= qq~~ ; $table_row_cnt++ ; my $table_col_cnt = 0 ; # title="$amnt_toolip{$op_id}" data-toggle="tooltip" data-placement="top" my @logistics_delivery_datetime = split(/\|/,$db{$table}{$id}{logistics_delivery_datetime}) ; if ($db{users}{$operators[$f_t_pos{$f_t}]}{name} && !$seen_op{$db{users}{$operators[$f_t_pos{$f_t}]}{name}}) { &xlsxcreator_add_worksheet($db{users}{$operators[$f_t_pos{$f_t}]}{name}) ; $seen_op{$db{users}{$operators[$f_t_pos{$f_t}]}{name}} = 1 ; $xlsxrow_ops{$operators[$f_t_pos{$f_t}]} = 2 ; foreach (@sql_col_display) { my $coltitle = $_ ; $coltitle =~ s/\_id//g ; $coltitle =~ s/\_/ /g ; my $ucfirst = join '', map { ucfirst lc } split /(\s+)/, $coltitle; &xlsxcreator_write_xlsx($db{users}{$operators[$f_t_pos{$f_t}]}{name},0,$xlsxcol,$ucfirst,$format_management_report_headings); $xlsxcol++; } $xlsxcol = 0 ; &xlsxcreator_freeze_panes($db{users}{$operators[$f_t_pos{$f_t}]}{name},1,0) ; # Freeze the first 3 rows } foreach (@sql_col_display) { next unless $_ ; # blank for the buttons column $display = 1 ; our $val = $db{$table}{$id}{$_} ; our $valxlsx = $val ; my $align = '' ; my $center = qq~ class="dt-center"~ ; $align = $center ; $table_col_cnt++ ; my $nowrap = '' ; # ("event_quote_nr","event_name","from_or_to_event","system_location","supplier","default_amount","amount_paid","amount_payable","date_payable","paid") ; if ($_ eq 'event_quote_nr') { $val = &common_min_get_event_quote_button($id,$table) ; } elsif ($_ eq 'event_name') { $align = qq~ class="dt-left"~ ; $val = $db{$table}{$id}{ref} ; } elsif ($_ eq 'client_name') { $nowrap = 'nowrap' ; $val = $db{customers}{$db{$table}{$id}{quote_to}}{name} ; } elsif ($_ eq 'from_or_to_event') { $val = uc $f_t ; } elsif ($_ eq 'from' || $_ eq 'to') { # if (($col_disp eq 'to' && $from_to eq 'to') || ($col_disp eq 'from' && $from_to eq 'from')) { if ($f_t eq $_) { if ($db{$table}{$id}{logistics_venue_id}) { my @logistics_venue_id = split(/\-/,$db{$table}{$id}{logistics_venue_id}) ; my $venue_id = ($_ eq 'from') ? $logistics_venue_id[0] : $logistics_venue_id[1] ; $val = qq~$db{organisations}{$venue_id}{name}~ ; $val .= qq~ [$db{organisations}{$venue_id}{region_code}]~ if $db{organisations}{$venue_id}{region_code} ; } } elsif ($f_t ne $_) { if ($db{$table}{$id}{logistics_system_location_id}) { my @logistics_system_location_id = split(/\-/,$db{$table}{$id}{logistics_system_location_id}) ; my $system_id = ($_ eq 'from') ? $logistics_system_location_id[0] : $logistics_system_location_id[1] ; $val = $db{logistics_locations}{$system_id}{location} ; if ($system_id eq '5') { my @saved_field_from_to = split(/\:\|\:/,$db{$table}{$id}{logistics_location_other}) ; my @saved_field_from_to_2 = ($_ eq 'from') ? split(/\|/,$saved_field_from_to[0]) : split(/\|/,$saved_field_from_to[1]) ; $val .= ($f_t eq 'from' && $saved_field_from_to_2[0]) ? qq~ [$saved_field_from_to_2[0]]~ : ($f_t eq 'to' && $saved_field_from_to_2[1]) ? qq~ [$saved_field_from_to_2[1]]~ : qq~~ ; } } } $valxlsx = $val ; } # elsif ($_ eq 'system_location') { # $val = $db{logistics_locations}{$db{$table}{$id}{logistics_system_location_id}}{location} ; # } elsif ($_ eq 'city') { $val = join (",", map { "$db{cities}{$_}{name}" } split(/\,/,$db{$table}{$id}{city_id}) ) ; } elsif ($_ eq 'date_of_event') { &logistics_dates($from_to,$id) ; $val = $valxlsx ; $nowrap = 'nowrap' ; } elsif ($_ eq 'delivery_date') { $nowrap = 'nowrap' ; $val = ($logistics_delivery_datetime[$f_t_pos{$f_t}]) ? &common_min_date_as_string(substr($logistics_delivery_datetime[$f_t_pos{$f_t}],0,10)) : qq~~ ; $valxlsx = $val ; } elsif ($_ eq 'supplier') { # $opts{"supplier_$f_t\_$id"} = $opts{suppliers} ; # $val = &common_min_table_select("supplier_$f_t\_$id",$suppliers[$f_t_pos{$f_t}],'',$table_row_cnt,$last_child{$_},'',$suppliers[$f_t_pos{$f_t}],'','','') ; $val = $db{logistics_suppliers}{$suppliers[$f_t_pos{$f_t}]}{supplier} ; $valxlsx = $val ; } elsif ($_ eq 'operator_name') { $val = $db{users}{$operators[$f_t_pos{$f_t}]}{name} ; $valxlsx = $val ; } elsif ($_ eq 'default_amount') { my $tot_amnt = &common_commify(sprintf("%.2f",$cost_of_shipment)) ; $val = $tot_amnt ; $valxlsx = $val ; } elsif ($_ eq 'amount_date_paid') { $val = ($dates_paid_tooltip{$f_t}) ? $dates_paid_tooltip{$f_t} : "N/A" ; } elsif ($_ eq 'amount_paid_by') { $val = ($paid_by_tooltip{$f_t}) ? $paid_by_tooltip{$f_t} : "N/A" ; } elsif ($_ eq 'amount_paid') { $tot_amnt_paid{$f_t} = sprintf("%.2f",$tot_amnt_paid{$f_t}) ; $val = qq~
$tot_amnt_paid{$f_t}
~ ; $valxlsx = $dates_paid_tooltip{$f_t} ; $valxlsx =~ s/\
/\;/g; # $valxlsx = ($valxlsx) ? qq~$amnt_owed [$valxlsx]~ : qq~$amnt_owed~ ; $valxlsx = ($valxlsx) ? qq~$tot_amnt_paid{$f_t} [$valxlsx]~ : qq~$tot_amnt_paid{$f_t}~ ; } elsif ($_ eq 'amount_payable') { $amounts_ids .= qq~input[name='amount_payble_$f_t\_$id'],~ ; $amount_ids_to_default .= qq~"inputAmount_payble_$f_t\_$id":"$amnt_owed",~ ; $val = ($amnt_owed > 0) ? &common_min_form_input_col("amount_payble_$f_t\_$id",$amnt_owed) : $amnt_owed ; $valxlsx = $amnt_owed ; } # elsif ($_ eq 'date_payable') { # if ($date_payable_arr[$f_t_pos{$f_t}]) { # # $val = ($amnt_owed > 0) ? &common_min_form_input_col("date_$id\_$op_id",$date_pay[$column_count - 1]) : "N/A" ; # # $val = ($amnt_owed > 0) ? &common_min_form_input_col("date_$f_t\_$id",$date_pay[$f_t_pos{$f_t}]) : "N/A" ; # $val = ($amnt_owed > 0) ? &common_min_table_datepicker("date_$f_t\_$id",$date_payable[$f_t_pos{$f_t}],'',$table_row_cnt,$table_col_cnt,'',1,'','') : "N/A" ; # $valxlsx = ($amnt_owed > 0) ? $date_pay[$f_t_pos{$f_t}] : "N/A" ; # } else { # # # my $date_payable = ($i{date_to}) ? $i{date_to} : "$now_year-$now_mm-$now_dd" ; # my $date_payable = "$now_year-$now_mm-$now_dd" ; # # $val = &common_min_form_input_col("date_$id\_$op_id",$date_payable) ; # $val = &common_min_table_datepicker("date_$f_t\_$id",$date_payable) ; # $valxlsx = &common_min_date_as_string($date_payable) ; # } # } elsif ($_ eq 'paid') { if ($amount_pay[$f_t_pos{$f_t}]) { $val = ($amnt_owed > 0) ? &common_min_form_checkbox_col("paid_$f_t\_$id",1) : '' ; } else { $val = ($amnt_owed > 0) ? &common_min_form_checkbox_col("paid_$f_t\_$id",0) : '' ; } $valxlsx = ($amnt_owed > 0) ? "No" : "Yes" ; } if ($_ ne 'event_quote_nr' && $_ ne 'date_payable' && $_ ne 'amount_payable' && $_ ne 'paid' && $_ ne 'amount_paid' && $_ ne 'default_amount' && $_ ne 'supplier') { &xlsxcreator_write_xlsx($ws,$xlsxrow,$xlsxcol,$val,$format_management_report_data) ; &xlsxcreator_write_xlsx($db{users}{$operators[$f_t_pos{$f_t}]}{name},$xlsxrow_ops{$operators[$f_t_pos{$f_t}]},$xlsxcol,$val,$format_management_report_data) ; } elsif ($_ eq 'event_quote_nr') { &xlsxcreator_write_xlsx($ws,$xlsxrow,$xlsxcol,$id,$format_management_report_data_ref_nr) ; &xlsxcreator_write_xlsx($db{users}{$operators[$f_t_pos{$f_t}]}{name},$xlsxrow_ops{$operators[$f_t_pos{$f_t}]},$xlsxcol,$id,$format_management_report_data_ref_nr) ; } elsif ($_ eq 'date_payable' || $_ eq 'amount_payable' || $_ eq 'paid' || $_ eq 'default_amount' || $_ eq 'amount_paid' || $_ eq 'supplier') { &xlsxcreator_write_xlsx($ws,$xlsxrow,$xlsxcol,$valxlsx,$format_management_report_data) ; &xlsxcreator_write_xlsx($db{users}{$operators[$f_t_pos{$f_t}]}{name},$xlsxrow_ops{$operators[$f_t_pos{$f_t}]},$xlsxcol,$valxlsx,$format_management_report_data) ; } $xlsxcol++; # if ($_ eq 'ref_nr' or $_ eq 'camera_nr') { $val = &common_camera_links($tables,$id,$val) ;} $print_tbody .= qq~$val~ ; } $print_tbody .= qq~~ ; $xlsxrow++ ; $xlsxrow_ops{$operators[$f_t_pos{$f_t}]}++ ; $f_t_event_ids .= qq~"$f_t\_$id",~ ; } # chop $op_ids if $op_ids ; # $f_t_event_ids .= qq~"$row_cnt_$id":[$op_ids],~ if $op_ids ; } chop $f_t_event_ids if $f_t_event_ids ; chop $amounts_ids if $amounts_ids ; chop $amount_ids_to_default if $amount_ids_to_default ; $trigger_jquery_raw .= qq~ let default_amnt_ownd = {$amount_ids_to_default} ; let prev_amnt_ownd = {} ; // itv-table // \$("$amounts_ids").change( function () { \$("#$useropts{table_id}").on("change","[id^='inputAmount_payble_']", function () { default_amnt_ownd[this.id] = parseFloat(default_amnt_ownd[this.id]) ; // if ((\$(this).val() <= 0 || \$(this).val() > default_amnt_ownd[this.id]) && !prev_amnt_ownd[this.id]) { if ((\$(this).val() <= 0) && !prev_amnt_ownd[this.id]) { \$(this).val(default_amnt_ownd[this.id]) ; // } else if ((\$(this).val() <= 0 || \$(this).val() > default_amnt_ownd[this.id]) && prev_amnt_ownd[this.id]) { } else if ((\$(this).val() <= 0) && prev_amnt_ownd[this.id]) { \$(this).val(prev_amnt_ownd[this.id]) ; } else { prev_amnt_ownd[this.id] = \$(this).val() ; } let abc = \$(this).val() ; abc = parseFloat(abc) ; abc = abc.toFixed(2) ; \$(this).val(abc) ; }) ; ~ ; $xlsxrow_ops{$ws} = 1 ; $db{users}{$ws}{name} = $ws ; foreach (keys %xlsxrow_ops) { $worksheet{$db{users}{$_}{name}} -> set_column(0,0,15) ; $worksheet{$db{users}{$_}{name}} -> set_column(2,8,25) ; $worksheet{$db{users}{$_}{name}} -> set_column(1,1,40) ; $worksheet{$db{users}{$_}{name}} -> set_column(9,15,15) ; $worksheet{$db{users}{$_}{name}} -> set_column(16,16,12) ; $worksheet{$db{users}{$_}{name}} -> set_row(0,20) if $db{users}{$_}{name} ne $ws ; } $worksheet{$ws} -> set_row(0,30) ; $worksheet{$ws} -> set_row(2,20) ; $box_icon .= qq~
~ ; &report_xlsx_export_footer('L',15,'logistics_payments') ; $fnsortcol = 0; $fnsortorder = 'asc' ; &common_min_extra_crumb("$lcpage\s","Search Screen") ; $trigger_jquery_raw .= qq~ function selectAll() { console.log("selectAll") ; let jsObject = [ $f_t_event_ids ]; for (let key in jsObject) { console.log("key : "+key) ; \$(\$("#itv-table").dataTable().fnGetNodes()).find(\$("input[name=paid_"+jsObject[key]+"]")).each(function () { if(\$(this).is(':checked')) { \$(this).prop('checked',false); } else { \$(this).prop('checked',true); } }) ; } } ~; # my $savebuttjs = ($count_outstanding > 0) ? '$("#invoice-form").submit();' : 'noty({text:"There are no operators to pay!",layout:"center",type:"error",timeout:3000});return;' ; $trigger_jquery_raw .= qq~ \$("#savebutt").click(function() { $savebuttjs \$("#logistics-payments-form").submit(); }); ~ ; &common_min_table_datepicker_jquery ; } #------------------------------------------------------------------------------------------ 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 load_list_vars { my ($where) = @_ ; &db_min_ro($table,"id,quote_to,ref,quote_accepted,logistics_suppliers,logistics_system_location_id,logistics_dates_paid,logistics_amounts_paid,logistics_amounts_paid_by,date_from,date_to,logistics_delivery_datetime,logistics_shipment_operator_id,logistics_venue_id,city_id","$where AND `quote_accepted` = '1'",'','') ; &db_min_ro('customers','id,name',"events='1'",'','') ; # my %seen_poc = () ; load_list_vars foreach (sort {$db{$table}{$a}{poc_name} cmp $db{$table}{$b}{poc_name}} keys %{$db{$table}}) { # next if $seen_poc{$db{$table}{$_}{poc_name}}{$db{$table}{$_}{poc_contact_nr}} || !$db{$table}{$_}{poc_name} ; # $seen_poc{$db{$table}{$_}{poc_name}}{$db{$table}{$_}{poc_contact_nr}} = 1 ; my $poc_name = ($db{$table}{$_}{recipient_name}) ? $db{$table}{$_}{recipient_name} : $db{$table}{$_}{poc_name} ; my $poc_nr = ($db{$table}{$_}{recipient_nr}) ? $db{$table}{$_}{recipient_nr} : $db{$table}{$_}{poc_contact_nr} ; next unless $poc_name ; $poc_nr =~ s/\'//g ; $opts{poc}{$_} .= qq~~ ; } # &db_min_ro('event_systems','*',"(`system_type` = 'solo' OR `system_type` = 'mobile' OR `description` lIKE 'cricket%')",'','') ; # our %logistics_waybill_ids_hash = () ; # our %fval = () ; # our %col_database_col = () ; # foreach my $id (keys %{$db{$table}}) { # $fval{item}{$id}{from} = $db{$table}{$id}{logistics_item_ids} ; # $fval{item}{$id}{to} = $db{$table}{$id}{logistics_item_ids} ; # my @split_values = split(/\-/, $db{$table}{$id}{logistics_suppliers}) ; # $fval{supplier}{$id}{from} = $split_values[0] ; # $fval{supplier}{$id}{to} = $split_values[1] ; # @split_values = split(/\-/,$db{$table}{$id}{logistics_waybill_nrs}) ; # $logistics_waybill_ids_hash{$id}{from} = $split_values[0] ; # $logistics_waybill_ids_hash{$id}{to} = $split_values[1] ; # $fval{waybill_nr}{$id}{from} = $split_values[0] ; # $fval{waybill_nr}{$id}{to} = $split_values[1] ; # @split_values = split(/\-/,$db{$table}{$id}{logistics_status_ids}) ; # $fval{status}{$id}{from} = $split_values[0] ; # $fval{status}{$id}{to} = $split_values[1] ; # @split_values = split(/\-\-/,$db{$table}{$id}{logistics_dcb_comments}) ; # $fval{dcb_comments}{$id}{from} = $split_values[0] ; # $fval{dcb_comments}{$id}{to} = $split_values[1] ; # @split_values = split(/\-\-/,$db{$table}{$id}{logistics_special_instructions}) ; # my @split_from = split(/\:\|\:/,$split_values[0]) ; # my @split_to = split(/\:\|\:/,$split_values[1]) ; # $fval{special_instructions}{$id}{from} = $split_from[0] ; # $fval{other_special_instructions}{$id}{from} = $split_from[1] ; # $fval{special_instructions}{$id}{to} = $split_to[0] ; # $fval{other_special_instructions}{$id}{to} = $split_to[1] ; # my $def_poc_name = ($db{$table}{$id}{recipient_name}) ? $db{$table}{$id}{recipient_name} : $db{$table}{$id}{poc_name} ; # my $def_poc_nr = ($db{$table}{$id}{recipient_nr}) ? $db{$table}{$id}{recipient_nr} : $db{$table}{$id}{poc_contact_nr} ; # $fval{poc}{$id}{from} = qq~$def_poc_name\_$def_poc_nr~ if $def_poc_name || $def_poc_nr; # $fval{poc}{$id}{to} = $fval{poc}{$id}{from} ; # my @delivery_split = split(/\|/,$db{$table}{$id}{logistics_delivery_datetime}) ; # my @delivery_from = split("T",$delivery_split[0]) ; # my @delivery_to = split("T",$delivery_split[1]) ; # $fval{delivery_date}{$id}{to} = $delivery_to[0] || &get_default_date($db{$table}{$id}{date_to},-1) ; # $fval{delivery_time}{$id}{to} = $delivery_to[1] || '15:00:00' ; # $fval{delivery_date}{$id}{from} = $delivery_from[0] || &get_default_date($db{$table}{$id}{date_from},1) ; # $fval{delivery_time}{$id}{from} = $delivery_from[1] || '15:00:00' ; # my @location_other_split = split(/\:\|\:/,$db{$table}{$id}{logistics_location_other}) ; # my @location_other_to_split = split(/\|/,$location_other_split[1]) ; # $fval{other_to}{$id}{from} = $location_other_to_split[0] ; # $fval{other_to}{$id}{to} = $location_other_to_split[1] ; # my @location_other_from_split = split(/\|/,$location_other_split[0]) ; # $fval{other_from}{$id}{from} = $location_other_from_split[0] ; # $fval{other_from}{$id}{to} = $location_other_from_split[1] ; # $fval{received_by}{$id}{to} = $db{$table}{$id}{logistics_received_by} ; # $fval{received_by}{$id}{from} = $fval{received_by}{$id}{to} ; # } # &db_min_ro('cities','id,city,city_short','','','') ; # &db_min_ro('sport_types','id,name','','','') ; # &db_min_ro('regions','code,id','','','') ; # &db_min_ro('organisations','id,name,region_code','','','') ; # foreach (sort {$db{organisations}{$a}{name} cmp $db{organisations}{$b}{name}} keys %{$db{organisations}}) { # $opts{venue_location}{$db{regions}{$db{organisations}{$_}{region_code}}{id}} .= qq~~ ; # # &common_debug("1. venue_location >>>>>> []"); # } # $opts{venue_location}{other} .= qq~~ ; &db_min_ro('users','id,name,user_type,username',"`user_type` LIKE 'casual_%' AND `name` NOT LIKE 'Rory%'",'','') ; # my $dcb_sql = ($is_dcb_partner) ? "id='2'" : '' ; # &db_min_ro('logistics_suppliers','*','','','') ; &db_min_ro('logistics_suppliers','*',"active = '1'",'','') ; our %AISA_suppliers = () ; foreach (sort {$db{logistics_suppliers}{$a}{supplier} cmp $db{logistics_suppliers}{$b}{supplier}} keys %{$db{logistics_suppliers}}) { $opts{suppliers} .= qq~~ ; $AISA_suppliers{$_} = 1 if $db{logistics_suppliers}{$_}{supplier} =~ /AISA/ ; } # foreach (sort {$db{logistics_suppliers}{$a}{supplier} cmp $db{logistics_suppliers}{$b}{supplier}} keys %{$db{logistics_suppliers}}) { # $logistics_suppliers_selected = ($_ == 2) ? 'SELECTED' : '' ; # DCB Logistics # $opts{supplier} .= qq~~ ; # $db{logistics_suppliers}{$_}{name} = $db{logistics_suppliers}{$_}{supplier} ; # } # $opts{supplier_to} = $opts{supplier_from} ; &db_min_ro('logistics_locations','*','','','') ; &db_min_ro('logistics_shipments_cost','*','','','') ; ## id,cost,valid_from,valid_to our %shipments_cost = () ; foreach (keys %{$db{logistics_shipments_cost}}) { $shipments_cost{$_}{valid_from} = $db{logistics_shipments_cost}{$_}{valid_from} ; $shipments_cost{$_}{valid_to} = $db{logistics_shipments_cost}{$_}{valid_to} ; $shipments_cost{$_}{cost} = $db{logistics_shipments_cost}{$_}{cost} ; } &db_min_ro('organisations','id,name,region_code','','','') ; ## id,cost,valid_from,valid_to &db_min_ro('cities','id,name','','','') ; &db_min_ro('event_systems','*',"(`system_type` = 'solo' OR `system_type` = 'mobile' OR `description` lIKE 'cricket%')",'','') ; &db_min_ro('event_cost_items','1,rates',"name = 'Logistics Shipments'",'','') ; # foreach (sort {$db{logistics_locations}{$a}{location} cmp $db{logistics_locations}{$b}{location}} keys %{$db{logistics_locations}}) { # $opts{system_location} .= qq~~ ; # } # &common_debug("1. system_location >>>>>> [$opts{system_location}]"); # our %status_hash = (1 =>"Booked",2 => "Pending",3=>"In Transit",4 =>"Delivered") ; # $opts{status} .= qq~~ ; # $opts{status} .= qq~~ ; # $opts{status} .= qq~~ ; # $opts{status} .= qq~~ ; # $opts{status} = $opts{status_to} ; # &db_min_ro('logistics_items','*','','','') ; # foreach (sort {($db{logistics_items}{$a}{name} =~ /^(\d+)/)[0] <=> ($db{logistics_items}{$b}{name} =~ /^(\d+)/)[0]} keys %{$db{logistics_items}}) { # next if lc $db{logistics_items}{$_}{name} eq 'other' ; # $opts{item} .= qq~~ ; # $logistics_items_qty{$db{logistics_items}{$_}{qty}} .= "$_," if $db{logistics_items}{$_}{name} =~ /Tripod/ || $db{logistics_items}{$_}{name} =~ /Extension/ || $db{logistics_items}{$_}{name} =~ /Mobile System/ ; # } # foreach (keys %logistics_items_qty) { # chop $logistics_items_qty{$_} ; # } # $opts{item} .= qq~~ ; } #------------------------------------------------------------------------------------------ sub thead { &common_min_thead ; } #------------------------------------------------------------------------------- sub page_opts { our $glyphicon = 'list' ; our $lcpage = 'logistics-payment' ; &common_page_name ; our $table = 'event_quotes' ; $page_title = 'Logistics Payments' ; } #------------------------------------------------------------------------------- sub report_screen { our $lcol = 3 ; our $fcol = 5 ; $allow_deselect{client_id} = 1 ; $allow_deselect{supplier_id} = 1 ; $allow_deselect{operator_id} = 1 ; $i{client_id} = "" if $i{client_id} eq 'all' ; $opts{client_id} = ($i{client_id}) ? qq~~ : qq~~ ; &common_min_select_opts('client_id','customers','name',$i{client_id},'','',"events='1'") ; # &common_min_select_opts('organisation_ids','organisations','name','','','','','','','','name') ; &common_min_select_opts('supplier_id','logistics_suppliers','supplier','','','',"supplier NOT LIKE 'DCB%' AND active = '1'",'','','','supplier') ; &common_min_select_opts('operator_id','users','name','','','',"`user_type` LIKE 'casual_%' AND `name` NOT LIKE 'Rory%'",'','','','name') ; # &common_min_select_opts('system_location_id','logistics_locations','location','','','',"location NOT LIKE '%JHB'",'','','','location') ; # &common_min_select_opts('region_id','regions','name','','','code','','','','','name') ; # %status_hash = ($is_dcb_partner) ? (1 =>"Booked") : (1 =>"Booked",2 => "Pending",3=>"In Transit",4 =>"Delivered") ; # $i{status_id} = ($is_dcb_partner && !$i{status_id}) ? 1 : $i{status_id} ; # $selected{$i{status_id}} = "SELECTED" if $i{status_id} ; # for (1 .. 4) { $opts{status_id} .= qq~~ ; } $preferred_title{organisation_ids} = "Venue" ; $print_box_content_rows .= &common_min_forms_start('report') ; # $trigger_jquery .= qq~ # \$("#selectClient_id").val('5').trigger("chosen:updated") ; # ~ ; # $allow_deselect{organisation_ids} = 1 ; # $allow_deselect{supplier_id} = 1 ; # $allow_deselect{status_id} = 1 ; # $allow_deselect{region_id} = 1 ; $print_box_content_rows .= &common_min_form_select('client_id',$i{client_id}) ; my ($monday,$sunday) = &common_get_this_monday_sunday ; $print_box_content_rows .= &common_min_form_datepicker('date_from',$monday) ; #unless $i{date_from} ; $print_box_content_rows .= &common_min_form_datepicker('date_to',$sunday) ; #unless $i{date_from} ; # $print_box_content_rows .= &common_min_form_select('organisation_ids','') ; $print_box_content_rows .= &common_min_form_select('supplier_id','') ; $print_box_content_rows .= &common_min_form_select('operator_id','') ; # $print_box_content_rows .= &common_min_form_select('system_location_id','') ; # $print_box_content_rows .= &common_min_form_select('status_id','') ; # $print_box_content_rows .= &common_min_form_select('region_id','') ; $opts{paid_status} .= qq~~ ; $opts{paid_status} .= qq~~ ; $opts{paid_status} .= qq~~ ; $print_box_content_rows .= &common_min_form_select('paid_status','') ; $print_box_content_rows .= &common_min_forms_end('','','report') ; &common_min_search_screen ; } #------------------------------------------------------------------------------- sub screen3 { our %custom_column_styles = () ; $custom_column_styles{date_payable} = qq~style="min-width:120px;"~ ; $custom_column_styles{amount_payable} = qq~style="min-width:120px;"~ ; # $custom_column_styles{supplier} = qq~style="min-width:120px;width:15%;"~ ; # &common_min_table_select_jquery("#$useropts{table_id} td:nth-last-child($last_child{supplier})") ; &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 ; # &common_min_extra_crumb("manage-$lcpage\s","Manage $ucfirstpage\s") ;ST STITHIANS COLLEGE BASKETBALL TOU $extra_form_fields .= qq~ ~; $print_box_content_rows .= &common_min_forms_end('','','save') ; require _blank ; exit ; } #------------------------------------------------------------------------------------------ sub screen4 { &common_min_alert_type ; print < $useropts{title}
ENDOFTEXT # exit ; } #------------------------------------------------------------------------------------------ use common ; use report ; use xlsxcreator ; use today ; use logistics ; 1;