317 lines
11 KiB
Perl
317 lines
11 KiB
Perl
|
#!/usr/bin/perl
|
||
|
|
|
||
|
|
BEGIN { use lib '/usr/home/cfg' ; require push_inc ; }
|
||
|
|
|
||
|
|
require cfg ;
|
||
|
|
# unless ($username eq 'handre') { 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 ;
|
||
|
|
|
||
|
|
#---------------------------------------------------------------------------------------------------------------------------------------------------------------------
|
||
|
|
|
||
|
|
print "Content-type: text/html\n\n";
|
||
|
|
|
||
|
|
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 '' ;
|
||
|
|
&common_min_action;
|
||
|
|
$db_ignore_open_close = 0 ; # do one open and one close instead of repeating it
|
||
|
|
&db_close_conn ;
|
||
|
|
|
||
|
|
exit;
|
||
|
|
|
||
|
|
#------------------------------------------------------------------------------------------
|
||
|
|
|
||
|
|
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;
|
||
|
|
}
|
||
|
|
}
|
||
|
|
|
||
|
|
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} 00:00:00')" ;
|
||
|
|
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}}" ;
|
||
|
|
# }
|
||
|
|
|
||
|
|
# if ($i{operator_id}) {
|
||
|
|
# push @report_results, "Client = $client{$i{client_id}}" ;
|
||
|
|
# }
|
||
|
|
|
||
|
|
# 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{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 {
|
||
|
|
|
||
|
|
my $srch_where_sql = join(' AND ', @report_sql) ;
|
||
|
|
|
||
|
|
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","operators","date_from","date_to","days","amount") ;
|
||
|
|
our @col_display_excel = @sql_col_display ;
|
||
|
|
# my $xlsxrow = 0 ;
|
||
|
|
&report_xlsx_export_header_events("Operators_Report$xlsreportname",'events') ;
|
||
|
|
&load_list_vars("$srch_where_sql$add_sql_where") ;
|
||
|
|
my $field = "";
|
||
|
|
|
||
|
|
my %seen_op = () ;
|
||
|
|
my $ops = "" ;
|
||
|
|
my $total_ops = 0 ;
|
||
|
|
|
||
|
|
foreach my $id (sort { $a <=> $b } keys %{$db{$table}}) {
|
||
|
|
|
||
|
|
%seen_op = () ;
|
||
|
|
$total_ops = 0 ;
|
||
|
|
@op_work = split(/\;/,$db{event_quotes_min}{$id}{operator_workings_event}) ;
|
||
|
|
@des_work = split(/\;/,$db{event_quotes_min}{$id}{description_workings_event}) ;
|
||
|
|
@amnt_work = split(/\;/,$db{event_quotes_min}{$id}{amount_workings_event}) ;
|
||
|
|
|
||
|
|
for my $opp_cnt (1 .. 50) {
|
||
|
|
# next if $i{operator_id} and $db{$table}{$id}{"operator_$opp_cnt\_workings_event"} != $i{operator_id} ;
|
||
|
|
# next unless $db{$table}{$id}{"description_$opp_cnt\_workings_event"} eq '14' ;
|
||
|
|
# next unless $db{$table}{$id}{"operator_$opp_cnt\_workings_event"} ;
|
||
|
|
# $total_amount{$id}{$opp_cnt} += $db{$table}{$id}{"amount_$opp_cnt\_workings_event"} ;
|
||
|
|
# next if $seen_op{$db{$table}{$id}{"operator_$opp_cnt\_workings_event"}} ;
|
||
|
|
# $seen_op{$db{$table}{$id}{"operator_$opp_cnt\_workings_event"}} = 1;
|
||
|
|
# $total_ops += 1 ;
|
||
|
|
# $ops = $casual_names_from_ids{$db{$table}{$id}{"operator_$opp_cnt\_workings_event"}} ;
|
||
|
|
|
||
|
|
next if $i{operator_id} and $op_work[$opp_cnt - 1] != $i{operator_id} ;
|
||
|
|
# next unless $des_work[$opp_cnt - 1] eq '14' ;
|
||
|
|
next unless $op_work[$opp_cnt - 1] ;
|
||
|
|
$total_amount{$id}{$op_work[$opp_cnt - 1]} += $amnt_work[$opp_cnt - 1] ;
|
||
|
|
next if $seen_op{$op_work[$opp_cnt - 1]} ;
|
||
|
|
$seen_op{$op_work[$opp_cnt - 1]} = 1;
|
||
|
|
$total_ops += 1 ;
|
||
|
|
$ops = $casual_names_from_ids{$op_work[$opp_cnt - 1]} ;
|
||
|
|
$operators_list{$id}{$op_work[$opp_cnt - 1]} = $opp_cnt ;
|
||
|
|
# $operators_list{$id}{$opp_cnt} = $ops ;
|
||
|
|
}
|
||
|
|
next if $total_ops == 0 ;
|
||
|
|
foreach my $op_id (keys %{$operators_list{$id}}) {
|
||
|
|
$xlsxcol=0 ;
|
||
|
|
$print_tbody .= qq~<tr>~ ;
|
||
|
|
foreach (@sql_col_display) {
|
||
|
|
# next unless $_ ; # blank for the buttons column
|
||
|
|
# my $align = '' ;
|
||
|
|
# my $center = qq~ class="dt-center"~ ;
|
||
|
|
# $align = $center ;
|
||
|
|
my $val = $db{$table}{$id}{$_} ;
|
||
|
|
$val = &get_val($id,$_,$op_id) ;
|
||
|
|
if ($_ eq 'amount'){ &xlsxcreator_write_xlsx($ws,$xlsxrow,$xlsxcol,$val,$format_management_report_data) ; }
|
||
|
|
else { &xlsxcreator_write_xlsx($ws,$xlsxrow,$xlsxcol,$val,$format_event_data) ; }
|
||
|
|
$xlsxcol++;
|
||
|
|
# if ($_ eq 'ref_nr' or $_ eq 'camera_nr') { $val = &common_camera_links($table,$id,$val) ;}
|
||
|
|
$print_tbody .= qq~<td class="dt-center">$val</td>~ ;
|
||
|
|
}
|
||
|
|
$print_tbody .= qq~</tr>~ ;
|
||
|
|
$xlsxrow++;
|
||
|
|
}
|
||
|
|
}
|
||
|
|
|
||
|
|
# $print_tbody .= qq~<tr>~ ;
|
||
|
|
# $print_tbody .= qq~</tr>~ ;
|
||
|
|
|
||
|
|
$worksheet{$ws} -> set_column(0, 1, 40) ;
|
||
|
|
$worksheet{$ws} -> set_column(2, 5, 15) ;
|
||
|
|
|
||
|
|
$worksheet{$ws} -> set_row(0, 30) ;
|
||
|
|
$worksheet{$ws} -> set_row(2, 20) ;
|
||
|
|
$worksheet{$ws} -> set_row(3, 5) ;
|
||
|
|
|
||
|
|
&report_xlsx_export_footer('L',15,'events') ;
|
||
|
|
|
||
|
|
# $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") ;
|
||
|
|
|
||
|
|
} #------------------------------------------------------------------------------------------
|
||
|
|
|
||
|
|
sub get_val {
|
||
|
|
|
||
|
|
my ($id,$col,$op_id) = @_ ;
|
||
|
|
|
||
|
|
if ($col eq 'date_from' or $col eq 'date_to'){
|
||
|
|
$val = $db{$table}{$id}{date_from} if $col eq 'date_from' ;
|
||
|
|
$val = $db{$table}{$id}{date_to} if $col eq 'date_to' ;
|
||
|
|
$val = substr($val, 0, 10) ;
|
||
|
|
} elsif ($col eq 'days') {
|
||
|
|
$val = $db{$table}{$id}{qty} ;
|
||
|
|
} elsif ($col eq 'amount') {
|
||
|
|
$val = $total_amount{$id}{$op_id} ;
|
||
|
|
$val = sprintf("%.2f", $val) ;
|
||
|
|
} elsif ($col eq 'event') {
|
||
|
|
$val = $db{$table}{$id}{ref} ;
|
||
|
|
} elsif ($col eq 'operators') {
|
||
|
|
$val = $casual_names_from_ids{$op_id} ;
|
||
|
|
} elsif ($_ eq 'id'){
|
||
|
|
$val = $id ;
|
||
|
|
}
|
||
|
|
|
||
|
|
return ($val) ;
|
||
|
|
|
||
|
|
} #------------------------------------------------------------------------------------------
|
||
|
|
|
||
|
|
sub load_list_vars {
|
||
|
|
|
||
|
|
my ($where) = @_ ;
|
||
|
|
|
||
|
|
our %casual_names_from_ids = () ;
|
||
|
|
our %total_amount = () ;
|
||
|
|
our %operators_list = () ;
|
||
|
|
|
||
|
|
&db_min_ro('users','id, name, user_type',"user_type = 'casual_a' OR user_type = 'casual_b' OR user_type = 'casual_c'",'','') ;
|
||
|
|
foreach my $id (keys %{$db{'users'}}){ $casual_names_from_ids{$id} = $db{'users'}{$id}{name} ; }
|
||
|
|
|
||
|
|
db_min_ro('event_quotes_min','id,operator_workings_event,description_workings_event,amount_workings_event,qty_workings_event','','','') ;
|
||
|
|
|
||
|
|
my $sql_select = "id,date_from,date_to,ref, qty" ;
|
||
|
|
# for (1 .. 15){ $sql_select .= ",description_$_\_workings_event" ; $sql_select .= ",operator_$_\_workings_event" ; $sql_select .= ",amount_$_\_workings_event" ; $sql_select .= ",qty_$_\_workings_event" ; }
|
||
|
|
|
||
|
|
&db_min_ro($table,$sql_select,$where,'','') ;
|
||
|
|
|
||
|
|
} #------------------------------------------------------------------------------------------
|
||
|
|
|
||
|
|
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%'") ;
|
||
|
|
|
||
|
|
my @datefrom = &common_add_delta_ymd(-2,0,0) ;
|
||
|
|
|
||
|
|
$print_box_content_rows .= &common_min_form_datepicker('date_from',"$datefrom[0]-01-01") ;
|
||
|
|
$print_box_content_rows .= &common_min_form_datepicker('date_to',"$now_year-$now_mm-$now_dd") ;
|
||
|
|
|
||
|
|
# 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_forms_end('','','report') ;
|
||
|
|
|
||
|
|
&common_min_search_screen ;
|
||
|
|
|
||
|
|
} #-------------------------------------------------------------------------------
|
||
|
|
|
||
|
|
use common ;
|
||
|
|
use report ;
|
||
|
|
use xlsxcreator ;
|
||
|
|
use today ;
|
||
|
|
|
||
|
|
1;
|