k8/aisa
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
rory-1314-4
aisa/scripts/operators_report.pl

1056 lines
39 KiB
Perl
Raw Permalink Normal View History

Initial commit
2025-11-26 09:31:54 +00:00
#!/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;
&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 ;
# -------- 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 ;
# &report_ifields;
# &list_screen ;
# &screen3;
# # &report_ifields;
# # &list_screen ;
# # &common_min_screen3;
# }
# if ($iaction eq 'paid') {
# &set_to_paid ;
# &list_screen ;
# &common_min_screen3;
# }
&common_min_action;
$db_ignore_open_close = 0 ; # do one open and one close instead of repeating it
&db_close_conn ;
exit;
#------------------------------------------------------------------------------------------
# sub set_to_paid {
# my ($where) = @_ ;
# &db_min_ro($table,'*',"$where",'','') ;
# my @ids = () ;
# foreach (keys %{$db{$table}}) {
# push @ids,"`id`='$_'" ;
# }
# my $ids_sql = join (" OR ",@ids) ;
# %i = () ;
# for (1 .. 50) {
# $i{paid_workings_event} .= qq~1;~ ;
# }
# chop $i{paid_workings_event} if $i{paid_workings_event} ;
# # &db_min_upd('event_quotes_min',"$ids_sql") ;
# &common_debug("UPDATE event_quotes_min SET paid_workings_event = $i{paid_workings_event} WHERE $ids_sql") ;
# # &db_min_ro('event_quotes_min','id,paid_workings_event',"$ids_sql",'','') ;
# # foreach my $id (sort {$a <=> $b} keys %{$db{$table}}) {
# # @paid_work = split(";", $db{event_quotes_min}{$id}{paid_workings_event}) ; # ????
# # %i = () ;
# # foreach (@paid_work) {
# # $i{paid_workings_event} .= qq~1;~ ;
# # }
# # chop $i{paid_workings_event} if $i{paid_workings_event} ;
# # &db_min_upd('event_quotes_min',"`id`='$id'") ;
# # }
# } #------------------------------------------------------------------------------------------
# sub update {
# my %iii = %i ;
# my %ii = () ;
# local $min_id = 0 ; local $max_id = 0 ;
# foreach (sort keys %i) {
# local $cell_name = $_ ;
# local @a = split("_") ;
# next if $a[-2] ne int($a[-2]) ;
# $ii{$a[-2]}{operator_dates_paid} .= qq~$i{$_};~ if $a[0] eq 'date' ;
# $ii{$a[-2]}{operator_amounts_paid} .= qq~$i{$_}~ if $a[0] eq 'amount' and $i{"paid_$a[-2]\_$a[-1]"} and $i{"amount_payble_$a[-2]\_$a[-1]"} ;
# $ii{$a[-2]}{operator_amounts_paid} .= qq~0.00~ if $a[0] eq 'amount' and $i{"paid_$a[-2]\_$a[-1]"} and not $i{"amount_payble_$a[-2]\_$a[-1]"} ;
# $ii{$a[-2]}{operator_amounts_paid} .= qq~;~ if $a[0] eq 'amount' ;
# $min_id = $a[-2] if ($min_id and $a[-2] < $min_id) or not $min_id ;
# $max_id = $a[-2] if $a[-2] > $max_id ;
# }
# # &db_common_ro('event_quotes_min',"id,operator_dates_paid,operator_dates_paid","`id` <= '$max_id' AND `id` >= '$min_id'",'','') ;
# &common_debug("SELECT id,operator_dates_paid,operator_dates_paid FROM event_quotes_min WHERE `id` <= '$max_id' AND `id` >= '$min_id'") ;
# foreach my $id (sort keys %ii) {
# %i = () ;
# $i{operator_dates_paid} = '' ;
# $i{operator_amounts_paid} = '' ;
# $i{operator_dates_paid} = $ii{$id}{operator_dates_paid} ;
# $i{operator_amounts_paid} = $ii{$id}{operator_amounts_paid} ;
# chop $i{operator_dates_paid} if $i{operator_dates_paid} ;
# chop $i{operator_amounts_paid} if $i{operator_amounts_paid} ;
# $i{operator_dates_paid} = '' if $ii{$id}{operator_dates_paid} eq $db{event_quotes_min}{$id}{operator_dates_paid} ;
# $i{operator_amounts_paid} = '' if $ii{$id}{operator_amounts_paid} eq $db{event_quotes_min}{$id}{operator_amounts_paid} ;
# # &db_min_upd("event_quotes_min","id='$id'") if $i{operator_dates_paid} or $i{operator_amounts_paid} ;
# &common_debug("UPDATE event_quotes_min SET operator_dates_paid = $i{operator_dates_paid},operator_amounts_paid = $i{operator_amounts_paid} WHERE id = '$id'") if $i{operator_dates_paid} or $i{operator_amounts_paid} ;
# }
# %i = %iii ;
# # operator_dates_paid, operator_amounts_paid
# } #------------------------------------------------------------------------------------------
sub report_ifields {
&load_search_vars ;
# if ($i{camera_system_id} and $i{camera_system_id} ne 'main' and $i{camera_system_id} ne 'other' and $i{camera_system_id} ne 'all') {
# push @report_sql, "(`camera_system_id`='$i{camera_system_id}')" ;
# push @report_results, "camera_system = $camera_system{$i{camera_system_id}}" ;
# $xlsreportname .= '-' . $i{camera_id} ;
# # our $acc_id_sql_where = "`camera_id`='$i{camera_id}'"
# } elsif ($i{camera_system_id} eq 'main' or $i{camera_system_id} eq 'other' or $i{camera_system_id} eq 'all') {
# push @report_results, "camera_system = $i{camera_system_id}" ;
# }
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(CAMERAS FROM ($i{date_from}) > CAMERAS TO ($i{date_to})); &report_screen;
}
}
# $i{date_from} = $i{date_from_i_field} if $i{date_from_i_field} ;
# $i{date_to} = $i{date_to_i_field} if $i{date_to_i_field} ;
# $i{operator_id} = $i{operator_id_i_field} if $i{operator_id_i_field} ;
if ($i{date_from}) {
# push @report_sql, "(`date_active` >= '$i{date_from}')" ;
# push @report_results, "date_active >= $i{date_from}" ;
push @report_sql, "(`quote_date` >= '$i{date_from} 00:00:00')" ;
push @report_results, "date_time >= $i{date_from}" ;
}
if ($i{date_to}) {
# push @report_sql, "(`date_active` <= '$i{date_to}')" ;
# push @report_results, "date_active <= $i{date_to}" ;
push @report_sql, "(`quote_date` <= '$i{date_to} 23:59:59')" ;
push @report_results, "date_time <= $i{date_to}" ;
}
# if ($i{client_id}) {
# # push @report_sql, "(`client_id` = '$i{client_id}')" ;
# push @report_results, "Client = $client{$i{client_id}}" ;
# }
our $where2 = qq~~ ;
if ($i{operator_id}) {
$where2 = "operator_workings_event LIKE '%;$i{operator_id}%' OR operator_workings_event LIKE '%$i{operator_id};%'" ;
push @report_results, "OPERATOR = $db{users}{1}{name}" ;
}
if ($i{event_quote_id}) {
&db_min_ro('event_quotes','1,ref',"`id`='$i{event_quote_id}'",'','') ;
push @report_sql, "(`id` = '$i{event_quote_id}')" ;
push @report_results, "EVENT QUOTE = $db{event_quotes}{1}{ref}" ;
}
# if ($i{operators_paid}) {
# push @report_sql2, "`operator_amounts_paid` NOT LIKE '%;;%'" ;
# push @report_sql2, "`operator_amounts_paid` != ';'" ;
# push @report_sql2, "`operator_amounts_paid` IS NOT NULL" ;
# push @report_results, "OPERATOR ARE ALL PAID" ;
# }
# if ($i{stock}) {
# push @report_results, "Stock = $i{stock}" ;
# }
$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) ;
}
} #------------------------------------------------------------------------------------------
sub load_search_vars {
if ($i{operator_id}) {
&db_min_ro('users','1,name',"`id` = '$i{operator_id}'",'','') ;
}
# if ($i{client_id}) {
# my $customers_sql_where = "`id`='$i{client_id}'" ;
# &db_min_ro('customers','*',$customers_sql_where,'','') ;
# foreach my $id (keys %{$db{customers}}) {
# $client{$id} = $db{customers}{$id}{name} ;
# }
# }
} #------------------------------------------------------------------------------------------
sub list_screen {
# $print_box_content_rows .= &common_min_forms_start('report') ;
# $print_box_content_rows .= &common_min_forms_start('invoice') ;
my $srch_where_sql = join(' AND ', @report_sql) ;
my $srch_where_sql2 = join(' AND ', @report_sql2) ;
my $add_sql_where = '' ;
# our @sql_col_display = ("id","camera_nr","camera_serial_nr","date_received","amount_usd","amount_zar","in_stock") ;
our @sql_col_display = ("event_quote_nr","operator_names","event_name","event_date_from","event_date_to","description","amount","date_paid","amount_paid") ;
my $table_colspan = 0 ;
foreach (@sql_col_display) {
$table_colspan += 1 ;
}
&report_xls_export_header("Operators_Report$xlsreportname",'operators_report') ;
&load_list_vars("$srch_where_sql$add_sql_where","$where2","$srch_where_sql2") ;
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~<div style = "display:none;">~ ;
$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("operator_id",$i{operator_id}) ; #operator_id_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("operators_paid",$i{operators_paid}) ;
$print_box_content_rows .= qq~</div>~ ;
$fcol = 12 ; my $val_min = 0 ; $row_cnt = 0 ;
my $ttl_light_blue = $workbook->set_custom_color(11,'#71919B');
my $total_format_1 = $workbook -> add_format() ;
$total_format_1 -> copy($format_management_report_data_ref_nr) ;
$total_format_1 -> set_num_format('#,##0.00');
$total_format_1 -> set_bold() ;
$total_format_1 -> set_bg_color($ttl_light_blue);
$total_format_1 -> set_color('white') ;
$total_format_1 -> set_size(11) ;
my $total_format_2 = $workbook -> add_format() ;
$total_format_2 -> copy($format_management_report_months) ;
$total_format_2 -> set_bold() ;
$total_format_2 -> set_bg_color($ttl_light_blue);
$total_format_2 -> set_color('white') ;
$total_format_2 -> set_size(11) ;
foreach my $id (sort {$a <=> $b} keys %{$db{$table}}) {
if ($i{client_id}) {
next unless $client{$i{client_id}} eq $quote_customer{$id} ;
}
next unless $db{event_quotes_min}{$id}{operator_dates_paid} ;
# if ($i{operators_paid}) {
# next unless $operator_all_paid{$_} ;
# }
$cnt_in_stock = -1 ;
my $in_stock = "" ;
@date_pay = split(";",$db{event_quotes_min}{$id}{operator_dates_paid}) ;
@amount_pay = split(";",$db{event_quotes_min}{$id}{operator_amounts_paid}) ;
my %operator_paid_details = () ; my $cnt = 0 ;
foreach (@amount_pay) {
my @c = split(":",$_) ;
$operator_paid_details{$c[0]}{$date_pay[$cnt]}{$cnt} = $c[1] ;
$cnt++ ;
}
# @paid = split(";",$db{event_quotes_min}{$id}{amount_is_paid_workings_event}) ;
@qty_work = split(";", $db{event_quotes_min}{$id}{qty_workings_event}) ;
@amnt_usd_work = split(";", $db{event_quotes_min}{$id}{amount_usd_workings_event}) ;
@amnt_work = split(";", $db{event_quotes_min}{$id}{amount_workings_event}) ;
@op_work = split(";", $db{event_quotes_min}{$id}{operator_workings_event}) ;
@paid_work = split(";", $db{event_quotes_min}{$id}{paid_workings_event}) ; # ????
@description = split(";", $db{event_quotes_min}{$id}{description_workings_event}) ;
my %cnt_to_op = () ; $cnt = 0 ;
foreach (@op_work) {
$cnt++ ;
$cnt_to_op{$_}{$cnt} = 1 ;
}
# &common_debug("NEW: $id, $db{event_quotes_min}{$id}{qty_workings_event}") ;
$first_id = $id unless $first_id ;
$last_id = $id if $last_id < $id ;
my %amnt_tot = () ; my $amnt_paid_tot = 0 ;
foreach my $op_id (sort keys %cnt_to_op) {
next if $i{operator_id} and $i{operator_id} ne $op_id ;
foreach my $column_count (sort keys %{$cnt_to_op{$op_id}}) {
# $field = "operator_$column_count\_workings_event" ;
$xlscol = 0 ;
# next if not $db{$table}{$id}{$field} ;
next unless $op_work[$column_count - 1] ;
# &common_debug("OP: $id, $db{$table}{$id}{$field}, $op_work[$column_count - 1]") ;
$print_tbody .= qq~<tr id="$id">~ ;
# $row_cnt++ ;
foreach (@sql_col_display) {
next unless $_ ; # blank for the buttons column
$display = 1 ;
my $val = $db{$table}{$id}{$_} ;
my $align = '' ;
my $center = qq~ class="dt-center"~ ;
$align = $center ;
if ($_ eq 'event_date_from' or $_ eq 'event_date_to'){
$db{$table}{$id}{date_from} = substr($db{$table}{$id}{date_from},0,10) ;
$db{$table}{$id}{date_to} = substr($db{$table}{$id}{date_to},0,10) ;
$val = $db{$table}{$id}{date_from} if $_ eq 'event_date_from' ;
$val = $db{$table}{$id}{date_to} if $_ eq 'event_date_to' ;
$val = substr($val, 0, 10) ;
} elsif ($_ eq 'operator_names') {
# $val = $casual_names_from_ids{$db{$table}{$id}{$field}} ;
$val = $casual_names_from_ids{$op_work[$column_count - 1]} ;
}
# elsif ($_ eq 'type') {
# # if ($type_a_users{$db{$table}{$id}{$field}}) {
# if ($type_a_users{$op_work[$column_count - 1]}) {
# # $val = $type_a_users{$db{$table}{$id}{$field}} ;
# $val = $type_a_users{$op_work[$column_count - 1]} ;
# } else {
# # $val = $type_b_users{$db{$table}{$id}{$field}} ;
# $val = $type_b_users{$op_work[$column_count - 1]} ;
# }
# } elsif ($_ eq 'qty') {
# # $val = $db{$table}{$id}{"qty_$column_count\_workings_event"} ;
# $val = $qty_work[$column_count - 1] ;
# }
elsif ($_ eq 'amount') {
# $val = $db{$table}{$id}{"amount_usd_$column_count\_workings_event"} ;
$val = &common_commify($amnt_work[$column_count - 1]) ;
$amnt_tot{$op_id} += $amnt_work[$column_count - 1] ;
}
elsif ($_ eq 'total_amount') {
# $val = $db{$table}{$id}{"amount_$column_count\_workings_event"} ;
$val = &common_commify($amnt_work[$column_count - 1]) ;
}
elsif ($_ eq 'event_quote_nr') {
my $class = 'info' ;
if ($quote_status{$id} == 1) {
$class = 'danger' ;
} elsif ($quote_status{$id} == 2) {
$class = 'success' ;
} elsif ($quote_status{$id} == 3) {
$class = 'danger' ;
} elsif ($quote_status{$id} == 4) {
$class = 'warning' ;
}
$row_cnt++ ;
$row_cnt = sprintf("%03d",$row_cnt) ;
$val = qq~<a class="btn btn-$class btn-xs" href="javascript:editMinItem('$id');"><span style="display:none;">$row_cnt</span>$id</a>~ ;
}
# elsif ($_ eq 'availablity') {
# if ($availability{$id}{$db{$table}{$id}{$field}} eq "Available" ) {
# $val = qq~<span style="display:none;">2</span><span class="label label-success" title data-toggle="tooltip" data-original-title="Available"><i class="glyphicon glyphicon-ok"></i></span>~ ;
# # $val .= $availability{$id}{$db{$table}{$id}{$field}} ;
# } elsif ($availability{$id}{$db{$table}{$id}{$field}} eq "Not Available" ) {
# $val = qq~<span style="display:none;">0</span><span class="label label-danger" title data-toggle="tooltip" data-original-title="Not Available"><i class="glyphicon glyphicon-remove"></i> </span>~ ;
# # $val .= $availability{$id}{$db{$table}{$id}{$field}} ;
# }
# else {
# $val = qq~<span class="label label-warning" title data-toggle="tooltip" data-original-title="No Response"><i class="glyphicon glyphicon-question-sign"></i></span>~ ;
# # $val .= "No Response" ;
# }
# }
elsif ($_ eq 'date_paid') {
# if ($date_pay[$column_count - 1]) {
# $val = &common_min_form_input_col("date_$id\_$row_cnt",$date_pay[$column_count - 1]) ;
# $val = $date_pay[$column_count - 1] ;
# $val_min = $val ;
# } else {
# $val = "$now_year-$now_mm-$now_dd" ;
# &common_min_form_input_col("date_$id\_$row_cnt","$now_year-$now_mm-$now_dd") ;
# $val_min = $val ;
# "$now_year-$now_mm-$now_dd" ;
# }
} elsif ($_ eq 'amount_paid') {
# if ($amount_pay[$column_count - 1]) {
# $val = $amount_pay[$column_count - 1] ; # &common_min_form_input_col("amount_payble_$id\_$row_cnt",$amount_pay[$column_count - 1]) ;
# $val_min = $val ;
# } elsif ($amnt_work[$column_count - 1]) {
# $val = $amnt_work[$column_count - 1] ; # &common_min_form_input_col("amount_payble_$id\_$row_cnt",$amnt_work[$column_count - 1]) ;
# $val_min = $val ;
# }
}
# elsif ($_ eq 'paid') {
# if ($amount_pay[$column_count - 1]) {
# $val = &common_min_form_checkbox_col("paid_$id\_$row_cnt",1) ;
# $val_min = "Yes" ;
# } else {
# $val = &common_min_form_checkbox_col("paid_$id\_$row_cnt",0) ;
# $val_min = "No" ;
# }
# }
elsif ($_ eq 'event_name') {
# $len = length($db{$table}{$id}{ref}) ;
$val = "$db{$table}{$id}{ref}" ;
} elsif ($_ eq 'description') {
$val = $db{event_cost_items}{$description[$column_count - 1]}{name} ;
}
if ($_ ne 'event_quote_nr' and $_ ne 'availablity' and $_ ne 'qty' and $_ ne 'date_paid' and $_ ne 'amount_paid' and $_ ne 'paid') {
&xlsxcreator_write_xlsx($ws, $xlsrow, $xlscol, $val, $format_management_report_data) ;
} elsif ($_ eq 'event_quote_nr') {
&xlsxcreator_write_xlsx($ws, $xlsrow, $xlscol, $id, $format_management_report_data_ref_nr) ;
} elsif ($_ eq 'availablity') {
if ($availability{$id}{$db{$table}{$id}{$field}}) {
&xlsxcreator_write_xlsx($ws, $xlsrow, $xlscol, $availability{$id}{$db{$table}{$id}{$field}}, $format_management_report_data) ;
} else {
&xlsxcreator_write_xlsx($ws, $xlsrow, $xlscol, "No Response", $format_management_report_data) ;
}
} elsif ($_ eq 'qty'){
&xlsxcreator_write_xlsx($ws, $xlsrow, $xlscol, $val, $format_management_report_data_ref_nr) ;
} elsif ($_ eq 'date_paid' or $_ eq 'amount_paid' or $_ eq 'paid') {
# &xlsxcreator_write_xlsx($ws, $xlsrow, $xlscol, $val_min, $format_management_report_data) ;
}
$xlscol++;
# if ($_ eq 'ref_nr' or $_ eq 'camera_nr') { $val = &common_camera_links($table,$id,$val) ;}
$print_tbody .= qq~<td $align>$val</td>~ ;
if ($_ eq 'date_paid') {
# my $amnt_ownd = $amnt_tot{$op_id} ; my $dates = qq~~ ; my $amnts = qq~~ ;
# foreach my $date (sort keys %{$operator_paid_details{$op_id}}) {
# foreach my $ct (keys %{$operator_paid_details{$op_id}{$date}}) {
# $dates .= qq~<br >~ if $dates ; $dates .= qq~$dates~ ;
# $amnts .= qq~<br>~ if $amnts ; $amnts .= qq~$operator_paid_details{$op_id}{$date}{$ct}~ ;
# $amnt_ownd -= $operator_paid_details{$op_id}{$date}{$ct} ;
# }
# }
# $amnt_ownd = sprintf("%.2f",$amnt_ownd) ;
# $print_tbody .= qq~<td $align>$dates</td><td $align>$amnts</td><td $align>$amnt_ownd</td>~ ;
}
}
$print_tbody .= qq~</tr>~ ;
$xlsrow++ ;
}
$row_cnt++ ;
# $print_tbody .= qq~<tr><td style="background-color:#71919b;color:white;font-weight:bold;font-size:100%;"><span style="display:none;">$row_cnt</span>Total</td>~ ;
# for (1 .. $table_colspan-1) {
# # $print_tbody .= qq~<td style="background-color:white;">&nbsp;</td>~ ;
# $print_tbody .= qq~<td style="background-color:#71919b;">&nbsp;</td>~ ;
# }
# $print_tbody .= qq~</tr>~ ;
my $dates = qq~~ ; my $amnts = qq~~ ;
my $amnt_ownd = $amnt_tot{$op_id} ;
foreach my $date (sort keys %{$operator_paid_details{$op_id}}) {
foreach my $ct (keys %{$operator_paid_details{$op_id}{$date}}) {
$row_cnt++ ;
$dates .= qq~<br>~ if $dates ; $dates .= qq~$date~ ;
$amnts .= qq~<br>~ if $amnts ; $amnts .= qq~$operator_paid_details{$op_id}{$date}{$ct}~ ;
$amnt_ownd -= $operator_paid_details{$op_id}{$date}{$ct} ;
}
}
$row_cnt = sprintf("%03d",$row_cnt) ;
$print_tbody .= qq~<tr>~ ;
local $top_row = $xlsrow ;
foreach my $tot_row (split("<br>",$dates)) {
for (0 .. $xlscol-1) {
&xlsxcreator_write_xlsx($ws, $xlsrow, $_,"", $total_format_1) ;
}
$xlsrow++ ;
}
$xlsrow = $top_row ;
$xlscol-- ; $xlscol-- ;
foreach (@sql_col_display) {
if ($_ eq 'event_quote_nr') {
$print_tbody .= qq~<td style="background-color:#71919b;"><span style="display:none;">$row_cnt</span></td>~ ;
} elsif ($_ eq 'operator_names') {
$print_tbody .= qq~<td style="background-color:#71919b;"><span style="display:none;">$casual_names_from_ids{$op_id}</span></td>~ ;
} elsif ($_ eq 'event_name') {
$print_tbody .= qq~<td style="background-color:#71919b;"><span style="display:none;">$db{$table}{$id}{ref}</span></td>~ ;
} elsif ($_ eq 'event_date_from') {
$print_tbody .= qq~<td style="background-color:#71919b;"><span style="display:none;">$db{$table}{$id}{date_from}</span></td>~ ;
} elsif ($_ eq 'event_date_to') {
$print_tbody .= qq~<td style="background-color:#71919b;"><span style="display:none;">$db{$table}{$id}{date_to}</span></td>~ ;
} elsif ($_ eq 'description') {
$print_tbody .= qq~<td style="background-color:#71919b;font-weight:bold;font-size:100%;color:white;" class="dt-right"><span style="display:none;">$row_cnt</span>Total</td>~ ;
&xlsxcreator_write_xlsx($ws, $xlsrow, $xlscol-2, "Total", $total_format_2) ;
} elsif ($_ eq 'amount') {
$amnt_tot{$op_id} = &common_commify(sprintf("%.2f",$amnt_tot{$op_id})) ;
$print_tbody .= qq~<td style="background-color:#71919b;color:white;" class="dt-center"><b>$amnt_tot{$op_id}</b></td>~ ;
&xlsxcreator_write_xlsx($ws, $xlsrow, $xlscol-1, $amnt_tot{$op_id}, $total_format_1) ;
} elsif ($_ eq 'date_paid') {
$print_tbody .= qq~<td style="background-color:#71919b;color:white;" class="dt-center"><b>$dates</b></td>~ ;
$top_row = $xlsrow ;
foreach (split("<br>",$dates)) {
&xlsxcreator_write_xlsx($ws,$xlsrow,$xlscol,$_,$total_format_1) ;
$xlsrow++ ;
}
$xlsrow = $top_row ;
$xlscol++;
} elsif ($_ eq 'amount_paid') {
$top_row = $xlsrow ;
local $new_amnt = 0 ;
local $new_amnt_str = qq~~ ;
foreach (split("<br>",$amnts)) {
&xlsxcreator_write_xlsx($ws,$xlsrow,$xlscol,$_,$total_format_1) ;
$xlsrow++ ;
$new_amnt = &common_commify(sprintf("%.2f",$_)) ;
$new_amnt_str .= qq~$new_amnt<br>~ ;
}
$new_amnt_str = substr($new_amnt_str,0,-4) if $new_amnt_str ;
# $xlsrow = $top_row ;
$xlscol++;
$print_tbody .= qq~<td style="background-color:#71919b;color:white;" class="dt-center"><b>$new_amnt_str</b></td>~ ;
} elsif ($_ eq 'outstanding_amount') {
$amnt_ownd = sprintf("%.2f",$amnt_ownd) ;
$print_tbody .= qq~<td style="background-color:#71919b;color:white;" class="dt-center"><b>$amnt_ownd</b></td>~ ;
}
}
# $xlsrow++ ;
# for (0 .. $table_colspan-6) {
# # $print_tbody .= qq~<td style="background-color:white;">&nbsp;</td>~ ;
# $print_tbody .= qq~<tr><td style="background-color:#71919b;"><span style="display:none;">$row_cnt</span></td>~ unless $_ ;
# $print_tbody .= qq~<td style="background-color:#71919b;">&nbsp;</td>~ if $_ ;
# }
$print_tbody .= qq~</tr>~ ;
}
# $print_tbody .= qq~<tr><td style="background-color:#71919b;color:white;font-weight:bold;font-size:100%;"><span style="display:none;">$id/2</span>Total</td>~ ;
# for (1 .. $table_colspan-1) {
# # $print_tbody .= qq~<td style="background-color:white;">&nbsp;</td>~ ;
# $print_tbody .= qq~<td style="background-color:#71919b;">&nbsp;</td>~ ;
# }
# $print_tbody .= qq~</tr>~ ;
}
# $print_tbody .= qq~<tr>~ ;
# $print_tbody .= qq~</tr>~ ;
$worksheet{$ws} -> set_column(1, 3, 20) ;
$worksheet{$ws} -> set_column(10, 10, 25) ;
$worksheet{$ws} -> set_row(0, 30) ;
$worksheet{$ws} -> set_row(2, 20) ;
add local backups
2026-02-03 12:35:43 +00:00
$box_icon .= qq~<div class="box-icon" title='View Invoice PDF' data-toggle="tooltip" data-placement="left"><a class="btn btn-default btn-round" href="javascript:dlgMdl('$useropts{'scripts'}/pdf/operators_invoice_pdf.pl?$i{date_from}&$i{date_to}&$i{operator_id}&$i{event_quote_id}','OPERATORS INVOICE','','waybill-dialog');" style="padding-top:1px;"><i class="glyphicon glyphicon-print icon-white"></i></a></div>~ if $i{operator_id} ;
Initial commit
2025-11-26 09:31:54 +00:00
# $box_icon .= qq~<div class="box-icon" title='Set All To Paid' data-toggle="tooltip" data-placement="left"><a class="btn btn-default btn-round" href="javascript:payAllInvoices('1');" style="padding-top:1px;"><i class="glyphicon glyphicon-usd icon-white"></i></a></div>~ ;
# $box_icon .= qq~<div class="box-icon" title='Toggle all Selected' data-toggle="tooltip" data-placement="left"><a class="btn btn-default btn-round" href="javascript:selectAll();" style="padding-top:1px;"><i class="glyphicon glyphicon-usd icon-white"></i></a></div>~ ;
&report_xls_export_footer('L',15,'operators_report') ;
# $box_icon = $box_icon . '' . $box_icon_pdf . '' . $box_icon_paid ;
# $fnsortcol = 3 ;
# $fnsortorder = 'asc' ;
$fnsortcol = 0;
$fnsortorder = 'asc' ;
$print_more_boxes .= qq~<br><button type="button" class="btn btn-default" onclick="history.go(-1);">Back</button><br><br>~ ;
&common_min_extra_crumb("$lcpage-report","Search Screen") ;
# $trigger_jquery_raw .= qq~
# function selectAll() {
# var row_cnt = $row_cnt ; var id_1 = $first_id ; var id_2 = $last_id ; var found_id = 0 ;
# console.log("row_cnt : "+row_cnt+" , id_1 : "+id_1+" , id_2 : "+id_2) ;
# // var filteredRows = \$("#itv-table").dataTable().rows({ filter: "applied" }).nodes();
# var filteredRows = \$("#itv-table").dataTable()._('tr', {"filter":"applied"}) ;
# var filteredRows2 = \$("#itv-table").dataTable().fnGetNodes() ;
# console.log("filteredRows : "+filteredRows) ;
# console.log("filteredRows2 : "+filteredRows2) ;
# // if (!filteredRows) {
# // filteredRows = \$("#itv-table").dataTable() ;
# // }
# if (row_cnt) {
# for (let i = 1; i <= row_cnt; i++) {
# found_id = 0 ;
# for (let j = id_1; j <= id_2; j++) {
# // \$(\$("#itv-table").dataTable().fnGetNodes()).find(\$("input[name=paid_"+j+"_"+i+"]")).each(function () {
# \$(\$("#itv-table").dataTable().fnGetNodes()).find(\$("input[name=paid_"+j+"_"+i+"]")).each(function () {
# id_1 = j ;
# found_id = 1 ;
# if(\$(this).is(':checked')) {
# \$(this).prop('checked',false);
# } else {
# \$(this).prop('checked',true);
# }
# }) ;
# if (found_id) {
# break ;
# }
# }
# }
# }
# // \$(filteredRows).each(function() {
# // var rowId = \$(this).attr("id");
# // console.log("Row ID: " + rowId);
# // });
# // console.log("filteredRows : "+filteredRows) ;
# // \$(\$("#itv-table").dataTable().fnGetNodes()).find(\$("input[name=input_array]")).each(function () {
# // if(\$(this).is(':checked')) {
# // \$(this).prop('checked',false);
# // } else {
# // \$(this).prop('checked',true);
# // }
# // });
# }
# // function payAllInvoices (id)
# // {
# // BootstrapDialog.confirm({
# // title: 'Confirm',
# // message: 'Are you sure you want to confirm all as paid?',
# // type: BootstrapDialog.TYPE_PRIMARY, // <-- Default value is BootstrapDialog.TYPE_PRIMARY <-- Default value is BootstrapDialog.TYPE_WARNING
# // callback: function(result) {
# // if(result) {
# // document.paid_min_form.$id.value = id ;
# // \$('#paid-form').submit();
# // }
# // }
# // });
# // }
# // function payAllInvoices() {
# // var list = [];
# // var filteredrows = \$("#itv-table").dataTable()._('tr', {"filter": "applied"});
# // // var lastclient = '' ;
# // for ( var i = 0; i < filteredrows.length; i++ ) {
# // // if ((lastclient) && (filteredrows[i][1] != lastclient)) { noty({text:'Error : Please select jobs from one client only!',layout:"center",type:"error",timeout:3000}); return ; }
# // list.push(filteredrows[i][0]);
# // // lastclient=filteredrows[i][1];
# // };
# // var params = list.join("&");
# // var conf = list.join(", ");
# // if (!params) { noty({text:'Error : No operators selected!',layout:"center",type:"error",timeout:3000}); return ; }
# // var reply = '' ;
# // BootstrapDialog.confirm({
# // title: 'Confirm Pay Invoices',
# // message: 'Are you sure you want to pay these invoices?',
# // type: BootstrapDialog.TYPE_SUCCESS, // <-- Default value is BootstrapDialog.TYPE_PRIMARY
# // callback: function(result) {
# // if(result) {
# // noty({text:'Pay these: '+params,layout:"center",type:"success",timeout:3000});
# // // var url = "$useropts{'scripts'}/get/get_db_pay_invoices.pl?" + params ;
# // // \$.get(url, function(json) {
# // // \$.each(json, function(key, data) {
# // // reply = data.inv_id ;
# // // // alert (reply) ;
# // // if ((reply) && (reply != 'error')) {
# // // noty({text:'Invoices succesfully paid!',layout:"center",type:"success",timeout:3000});
# // // for ( var i = 0; i < filteredrows.length; i++ ) {
# // // \$('#' + filteredrows[i][0]).hide(); // hide row by id
# // // };
# // // }
# // // else
# // // {
# // // noty({text:'Error paying invoices!',layout:"center",type:"error",timeout:3000}); return ;
# // // }
# // // });
# // // },
# // // 'json');
# // } else {
# // // do nothing
# // }
# // }
# // });
# // }
# ~;
# $trigger_jquery_raw .= qq~\$("#savebutt").click(function() {
# \$('#invoice-form').submit();
# });
# ~ ;
} #------------------------------------------------------------------------------------------
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 load_list_vars {
my ($where,$where_2,$where_3) = @_ ;
# our %ref_nr = () ;
# &db_min_ro('quotes','id,camera_system_id, quote_nr','','','') ;
# foreach my $id (keys %{$db{quotes}}) {
# $ref_nr{$db{quotes}{$id}{camera_system_id}} = $db{quotes}{$id}{quote_nr} ;
# }
# my %casual
# my @sql_casuals_where = () ;
# my $string = "" ;
# for (1 .. 15) {
# $string = " description_$_" ;
# $string .= "_workings_casual_users not like '' " ;
# push (@sql_casuals_where, $string)
# }
# my $sql_casuals_where_string = join("and", @sql_casuals_where)
# &db_min_ro('event_quotes','*',"$sql_casuals_where_string",'','') ;
# &common_load_quote_vars('>0');
# &common_load_quote_vars("cameras.quote_nr>0 AND cameras.quote_nr=quotes.quote_nr");
our %quote_status = () ;
# &db_min_ro('event_quotes',"*",'','','');
# foreach my $id (keys %{$db{event_quotes}}) {
# foreach my $col (sort keys %{$db{event_quotes}{$id}}) {
# }
# last ;
# }
# &db_min_ro('event_quotes_min',"*",'','','');
# foreach my $id (keys %{$db{event_quotes_min}}) {
# foreach my $col (sort keys %{$db{event_quotes_min}{$id}}) {
# }
# last ;
# }
&db_min_ro('event_quotes',"id, quote_cancelled, quote_accepted, if(concat(quote_expiry, ' 00:00:00') < now(), 1, 0) as 'expired', quote_pending","$where",'','');
foreach my $id (keys %{$db{event_quotes}}) {
if ($db{event_quotes}{$id}{quote_cancelled} == 1){
$quote_status{$id} = 1 ;
} elsif ($db{event_quotes}{$id}{quote_accepted} == 1){
$quote_status{$id} = 2 ;
} elsif ($db{event_quotes}{$id}{quote_pending} == 1) {
$quote_status{$id} = 4 ;
}
if ($db{event_quotes}{$id}{expired} == 1) {
$quote_status{$id} = 3 unless $db{event_quotes}{$id}{quote_accepted} == 1;
}
}
# &db_min_raw("
# select id, quote_cancelled, quote_accepted, if(concat(quote_expiry, ' 00:00:00') < now(), 1, 0) as 'expired', quote_pending from event_quotes ;
# " ) ;
# foreach $row (@$rows_array_ref) {
# if (@$row[1] == 1){
# $quote_status{@$row[0]} = 1 ;
# } elsif (@$row[2] == 1){
# $quote_status{@$row[0]} = 2 ;
# } elsif (@$row[4] == 1) {
# $quote_status{@$row[0]} = 4 ;
# }
# if (@$row[3] == 1) {
# $quote_status{@$row[0]} = 3 unless @$row[2] == 1;
# }
# }
our %availability = () ;
&db_min_raw("
select e.event_quote_nr, e.user_id, e.category from events e
left join (
select id, name, user_type from users where user_type like '%casual%'
) u on u.id = e.user_id ;
") ;
foreach $row (@$rows_array_ref) {
if (@$row[2] eq 'available') {
$availability{@$row[0]}{@$row[1]} = "Available" ;
} elsif (@$row[2] ne 'available' and @$row[2]) {
$availability{@$row[0]}{@$row[1]} = "Not Available" ;
}
}
our %type_a_users = () ;
our %type_b_users = () ;
our %casual_names_from_ids = () ;
&db_min_ro('users','id,name,user_type',"user_type = 'casual_a' or user_type = 'casual_b'",'','') ;
foreach my $id (keys %{$db{'users'}}){
$type_a_users{$id} = "A" if $db{'users'}{$id}{user_type} eq "casual_a" ;
$type_b_users{$id} = "B" if $db{'users'}{$id}{user_type} eq "casual_b" ;
$casual_names_from_ids{$id} = $db{'users'}{$id}{name} ;
}
my @event_quote_ids = () ;
&db_min_ro($table,'*',"$where",'','') ;
foreach (keys %{$db{$table}}) {
}
our %operator_all_paid = () ;
&db_min_ro('event_quotes_min','*',"$where_2$where_3",'','') ;
foreach (keys %{$db{event_quotes_min}}) {
$operator_all_paid{$db{event_quotes_min}{$_}{quote_id}} = 1 ;
}
&db_min_ro('event_cost_items','id,name','','','') ;
} #------------------------------------------------------------------------------------------
sub thead {
&common_min_thead ;
} #-------------------------------------------------------------------------------
sub page_opts {
our $glyphicon = 'list' ;
our $lcpage = 'operators' ;
&common_page_name ;
our $table = 'event_quotes' ;
$page_title = 'Operators' ;
} #-------------------------------------------------------------------------------
sub report_screen {
our $lcol = 3 ;
our $fcol = 5 ;
$print_box_content_rows .= &common_min_forms_start('report') ;
# &common_camera_opts ;
# &common_min_select_opts('client_id','customers','name',$i{client_id},'') ;
&common_min_select_opts('operator_id','users',"name",$i{operator_id},'','',"user_type LIKE 'casual%'") ;
&common_min_select_opts('event_quote_id','event_quotes',"ref",$i{event_quote_id},'','',"") ;
my $current_day_of_week = Day_of_Week($now_year,$now_mm,$now_dd);
my $days_to_subtract = $current_day_of_week - 1 ;
my ($first_day_year,$first_day_month,$first_day) = Add_Delta_Days($now_year,$now_mm,$now_dd,-$days_to_subtract);
$first_day_month = sprintf("%02d",$first_day_month) ; $first_day = sprintf("%02d",$first_day) ;
$print_box_content_rows .= &common_min_form_datepicker('date_from',"$i{date_from}") if $i{date_from} ;
$print_box_content_rows .= &common_min_form_datepicker('date_from',"$first_day_year-$first_day_month-$first_day") unless $i{date_from} ;
$print_box_content_rows .= &common_min_form_datepicker('date_to',"$i{date_to}") if $i{date_to} ;
$print_box_content_rows .= &common_min_form_datepicker('date_to',"$now_year-$now_mm-$now_dd") unless $i{date_to} ;
# our %type_a_users = () ;
# our %type_b_users = () ;
# &db_min_ro('users','id, name, user_type',"user_type = 'casual_a' or user_type = 'casual_b'",'','') ;
# foreach my $id (keys %{$db{'users'}}){
# $opts{$field} .= qq(<option value="$id" $selected $selected_multi{$val}>$db{$table}{$id}{$dispfield}$disp_addfield$disp_addmore</option>) if $db{'users'}{$id}{user_type} eq "casual_a" ;
# $opts{$field} .= qq(<option value="$id" $selected $selected_multi{$val}>$db{$table}{$id}{$dispfield}$disp_addfield$disp_addmore</option>) if $db{'users'}{$id}{user_type} eq "casual_b" ;
# }
# $print_box_content_rows .= &common_min_form_select('client_id','') ;
$print_box_content_rows .= &common_min_form_select('operator_id','') ;
$print_box_content_rows .= &common_min_form_select('event_quote_id','') ;
# $print_box_content_rows .= &common_min_form_checkbox('operators_paid','') ;
$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 ;
# &common_min_extra_crumb("manage-$lcpage\s","Manage $ucfirstpage\s") ;
# $print_box_content_rows .= &common_min_forms_end('','','save') ;
require _blank ;
exit ;
} #------------------------------------------------------------------------------------------
use common ;
use report ;
use xlsxcreator ;
use today ;
1;
Reference in New Issue Copy Permalink