596 lines
22 KiB
Perl
596 lines
22 KiB
Perl
#!/usr/bin/perl
|
|
|
|
BEGIN { use lib '/usr/home/cfg' ; require push_inc ; }
|
|
|
|
use CGI qw( :standard );
|
|
use CGI::Carp qw(fatalsToBrowser);
|
|
use Date::Calc qw(:all);
|
|
|
|
use PDF::API2::Lite;
|
|
use constant mm => 25.4/72;
|
|
use constant in => 1/72;
|
|
use constant pt => 1;
|
|
|
|
use Spreadsheet::WriteExcel;
|
|
use Spreadsheet::WriteExcel::Utility;
|
|
|
|
require cfg ;
|
|
|
|
print header; # CGI.pm method
|
|
|
|
#---------------------------------------------------------------------------------------------------------------------------------------------------------------------
|
|
|
|
&today;
|
|
|
|
#---------------------------------------------------------------------------------------------------------------------------------------------------------------------
|
|
|
|
@ARGV = split(/\\*\&/, $ENV{'QUERY_STRING'});
|
|
$action = $ARGV[0] ;
|
|
|
|
our ($q) = CGI -> new() ;
|
|
our $iaction = $q -> param('iaction') || $action ;
|
|
|
|
# our $debug = 1 ;
|
|
# our $testing = 1 ;
|
|
|
|
# &common_min_restriction_super ;
|
|
|
|
#-------- page opts --------------------------------------------------------------------------------------------------------------------------------------------------
|
|
|
|
&page_opts ;
|
|
|
|
#---------------------------------------------------------------------------------------------------------------------------------------------------------------------
|
|
|
|
# unless ($username eq 'rory') { print "This page is down for maintenance!" ; exit ; }
|
|
|
|
if ($iaction eq ''){
|
|
&report_screen ;
|
|
}
|
|
elsif ($iaction eq 'report') {
|
|
&common_min_load_params ;
|
|
&report_ifields ;
|
|
&list_screen ;
|
|
&report_screen ;
|
|
}
|
|
elsif ($iaction eq 'delete') {
|
|
&common_min_load_params ;
|
|
&common_min_delete ;
|
|
&report_ifields ;
|
|
&list_screen ;
|
|
&report_screen ;
|
|
}
|
|
|
|
exit;
|
|
|
|
#------------------------------------------------------------------------------------------
|
|
|
|
sub report_ifields {
|
|
|
|
# if (($i{leave_year}) and ($i{leave_year} ne 'all')) {
|
|
# push @report_sql, "(`leave_year` = '$i{leave_year}')" ;
|
|
# push @report_results, "Leave Year = $i{leave_year}" ;
|
|
# }
|
|
|
|
if (($i{leave_year}) and ($i{leave_year} ne 'all')) {
|
|
push @report_sql, "((`startdate` LIKE '$i{leave_year}%') OR (`enddate` LIKE '$i{leave_year}%'))" ;
|
|
push @report_results, "Start Date LIKE $i{leave_year} OR End Date LIKE $i{leave_year}" ;
|
|
}
|
|
|
|
# &db_min_ro('users','*',"`id`='$i{user_id}'",'','') ;
|
|
&db_min_ro('users','*','','','') ;
|
|
foreach my $id (keys %{$db{users}}) {
|
|
$user{$id} = $db{users}{$id}{name} ;
|
|
if ($db{users}{$id}{based_in} eq 'CT') { push @cpt_sql, "(`user_id` = '$id')" ; }
|
|
}
|
|
|
|
if (($i{user_id}) and ($i{user_id} ne 'all') and ($i{user_id} ne 'CT')) {
|
|
push @report_sql, "(`user_id` = '$i{user_id}')" ;
|
|
push @report_results, "Consultant = $user{$i{user_id}}" ;
|
|
}
|
|
|
|
if ($i{user_id} eq 'CT') {
|
|
my $srch_cpt_sql = join(' OR ', @cpt_sql) ;
|
|
push @report_sql, "($srch_cpt_sql)" ;
|
|
push @report_results, "Consultant based_in = CT" ;
|
|
}
|
|
|
|
# if (($i{user_id}) and ($i{user_id} ne 'all')) {
|
|
# push @report_sql, "(`user_id` = '$i{user_id}')" ;
|
|
# push @report_results, "Consultant = $user{$i{user_id}}" ;
|
|
# }
|
|
|
|
$srch_where_sql = join(' AND ', @report_sql) ;
|
|
|
|
$report_results_msg = uc join(', ', @report_results) ;
|
|
|
|
unless ($i{user_id}) { $error = qq(PLEASE SELECT A CONSULTANT); &report_screen; } else { $success = qq(REPORT WHERE $report_results_msg) ; }
|
|
unless ($report_results_msg) { $error = qq(ENTER AT LEAST ONE REPORT PARAMETER); &report_screen; } else { $success = qq(REPORT WHERE $report_results_msg) ; }
|
|
|
|
} #------------------------------------------------------------------------------------------
|
|
|
|
sub list_screen {
|
|
|
|
# &db_min_ro('users','*',"`id`='$i{user_id}'",'','') ;
|
|
# foreach my $id (keys %{$db{users}}) { $users{$id} = $db{users}{$id}{name} ; }
|
|
|
|
our $leave_settings_exist = 0 ;
|
|
# our $leave_uploads = '' ;
|
|
|
|
# &db_min_ro('leave_settings','*',"`user_id`='$i{user_id}'",'','') ;
|
|
&db_min_ro('leave_settings','*',"`user_id`='$i{user_id}' AND `leave_year`='$i{leave_year}'",'','') ;
|
|
foreach my $id (keys %{$db{leave_settings}}) {
|
|
# my $cons_id = $db{leave_settings}{$id}{user_id} ;
|
|
my $cat_type = $db{leave_settings}{$id}{category} ;
|
|
$days_entitled{$cat_type} = $db{leave_settings}{$id}{days_entitled} ;
|
|
$days_carried_over{$cat_type} = $db{leave_settings}{$id}{days_carried_over} ;
|
|
$leave_start_date{$cat_type} = $db{leave_settings}{$id}{leave_start_date} ;
|
|
$cats{$cat_type} = 1 ;
|
|
$leave_settings_exist = 1 ;
|
|
|
|
# &common_min_debug("common_min_get_uploads [leave/$id]") ;
|
|
|
|
# $leave_uploads .= &common_min_get_uploads("leave/$id",'leave') ;
|
|
# $leave_uploads =~ s/\<i class\=\"glyphicon glyphicon-trash\"\>\<\/i\>//g ;
|
|
}
|
|
|
|
# our @sql_col_display = ("id","date_time","title","type","startdate","enddate","days","user_id","notes") ;
|
|
our @sql_col_display = ("user_id","type","startdate","enddate","days","notes","") ;
|
|
|
|
my $xlsfolder = 'leavereport' ;
|
|
|
|
&report_xls_export_header("$user{$i{user_id}}_Leave_Report",$xlsfolder) ;
|
|
|
|
&db_min_ro($table,'*',"$srch_where_sql AND `category`='leave'",'`id` DESC','') ;
|
|
|
|
# foreach my $id (keys %{$db{$table}}) {
|
|
foreach my $id (sort {$db{$table}{$a}{startdate} cmp $db{$table}{$b}{startdate} } keys %{$db{$table}}) {
|
|
|
|
# my $leave_ccyymm = substr($db{$table}{$id}{leave_date},0,7) ; $leave_ccyymm =~ s/\-//g;
|
|
|
|
my ($sdate,$stime) = split(/ /,$db{$table}{$id}{startdate}) ;
|
|
my $s_ccyymmdd = &common_min_convert_sql_date_time($sdate);
|
|
my ($edate,$etime) = split(/ /,$db{$table}{$id}{enddate}) ;
|
|
my $e_ccyymmdd = &common_min_convert_sql_date_time($edate);
|
|
|
|
&dates_array($s_ccyymmdd,$e_ccyymmdd) ;
|
|
|
|
$print_tbody .= qq(<tr>) ;
|
|
|
|
$xlscol=0;
|
|
|
|
foreach (@sql_col_display) {
|
|
|
|
unless ($_) { next ; } # blank for the buttons column
|
|
my $val = $db{$table}{$id}{$_} ;
|
|
my $align = qq(class="dt-left");
|
|
|
|
# if ($money_format{$_}) { $align = qq(class="dt-right"); }
|
|
|
|
if (($_ eq 'days') and ($db{$table}{$id}{$_} == 0)) {
|
|
my ($s_dy,$s_dm,$s_dd,$s_th,$s_tm,$s_ts) = &common_min_split_sql_date_time($db{$table}{$id}{startdate});
|
|
my ($e_dy,$e_dm,$e_dd,$e_th,$e_tm,$e_ts) = &common_min_split_sql_date_time($db{$table}{$id}{enddate});
|
|
$Dd = Delta_Days($s_dy,$s_dm,$s_dd,$e_dy,$e_dm,$e_dd);
|
|
# ($Dd,$Dh,$Dm,$Ds) = Delta_DHMS($s_dy,$s_dm,$s_dd,$s_th,$s_tm,$s_ts, $e_dy,$e_dm,$e_dd,$e_th,$e_tm,$e_ts);
|
|
$val = $Dd ;
|
|
}
|
|
|
|
if ($_ eq 'days') {
|
|
$days_taken{$db{$table}{$id}{type}}+=$val;
|
|
$cats{$db{$table}{$id}{type}} = 1 ;
|
|
|
|
my $scalar = scalar @dates_array ; my $taken_per_day = 1 ;
|
|
|
|
if ($scalar>0) {
|
|
$taken_per_day = $val / $scalar ;
|
|
}
|
|
else
|
|
{
|
|
# $taken_per_day = $days_taken{$db{$table}{$id}{type}} ;
|
|
$taken_per_day = $db{$table}{$id}{days} ;
|
|
my $sccyymmdd = &common_min_convert_sql_date_time($db{$table}{$id}{startdate});
|
|
push @dates_array, $sccyymmdd ; # create array when start and end date same
|
|
}
|
|
|
|
# &common_min_debug("$db{$table}{$id}{type} [$val] [$days_taken{$db{$table}{$id}{type}}] scalar=$scalar");
|
|
|
|
# if ($db{$table}{$id}{type} eq 'compassionate') { &common_min_debug("$db{$table}{$id}{type} [$val] [$taken_per_day] scalar=$scalar"); }
|
|
|
|
foreach my $date (@dates_array) {
|
|
my $ccyy = substr($date,0,4) ;
|
|
my $mm = substr($date,4,2) ; $mm = sprintf("%0.0f",$mm) ;
|
|
my $dd = substr($date,6,2) ; $dd = sprintf("%0.0f",$dd) ;
|
|
$days_taken_by_month{$mm}{$ccyy}{$db{$table}{$id}{type}}+=$taken_per_day;
|
|
$days_taken_by_year{$ccyy}{$db{$table}{$id}{type}}+=$taken_per_day;
|
|
|
|
# if ($db{$table}{$id}{type} eq 'compassionate') { &common_min_debug("*** [$mm] [$taken_per_day] compassionate"); }
|
|
|
|
if (($day_of_week{$date} > 0) and (($day_of_week{$date} < 6))) { # mon - fri
|
|
$days_txt{$mm}{$db{$table}{$id}{type}} .= "$dd/" ;
|
|
}
|
|
|
|
&common_min_debug(">>> $db{$table}{$id}{type} [$mm] [$days_taken_by_month{$mm}{$ccyy}{$db{$table}{$id}{type}}] [$taken_per_day]");
|
|
}
|
|
}
|
|
|
|
if ($_ eq 'user_id') { $val = $user{$db{$table}{$id}{$_}} ; }
|
|
if ($_ eq 'startdate') { $val = substr($val,0,10) ; }
|
|
if ($_ eq 'enddate') { $val = substr($val,0,10) ; }
|
|
|
|
&report_xls_export_process_cell('','',$val) ;
|
|
|
|
if ($_ eq 'type') { $val = '<span class="label-default label">' . ucfirst $val . '</span>' ; }
|
|
|
|
$xlscol++;
|
|
|
|
$print_tbody .= qq(<td $align>$val</td>) ;
|
|
}
|
|
|
|
my $attach_butt = '' ; $attach_options = &common_min_get_uploads("leave/$id",'all') ; if ($attach_options) { $attach_butt = qq( <a href="#" data-toggle="popover" data-placement="left" data-content="$attach_options" title="Uploaded Documents" data-html="true" data-trigger="focus" class="btn btn-default btn-xs"><i class="glyphicon glyphicon-paperclip" style="top:4px;"></i></a>) ; }
|
|
|
|
$print_tbody .= qq(<td nowrap>
|
|
$attach_butt
|
|
</td>
|
|
</tr>) ;
|
|
|
|
$xlsrow++ ;
|
|
|
|
}
|
|
|
|
&xls_totals_footer('J',15,$xlsfolder) ; # set xls column width
|
|
|
|
} #------------------------------------------------------------------------------------------
|
|
|
|
sub dates_array {
|
|
|
|
my ($start_date,$end_date) = @_ ;
|
|
|
|
my $array_date = $start_date ;
|
|
|
|
@dates_array=() ;
|
|
|
|
while ($array_date < $end_date) {
|
|
|
|
push @dates_array, $array_date ;
|
|
|
|
my $stay_date_dd = substr($array_date,6,2) ;
|
|
my $stay_date_mm = substr($array_date,4,2) ;
|
|
my $stay_date_ccyy = substr($array_date,0,4) ;
|
|
|
|
$day_of_week{$array_date} = Day_of_Week($stay_date_ccyy,$stay_date_mm,$stay_date_dd) ; # 1 is Monday
|
|
|
|
my ($ccyy,$month,$day) = Add_Delta_Days($stay_date_ccyy,$stay_date_mm,$stay_date_dd,1) ;
|
|
$month = sprintf("%02s", $month) ;
|
|
$day = sprintf("%02s", $day) ;
|
|
|
|
$array_date = "$ccyy$month$day" ;
|
|
}
|
|
|
|
} #-------------------------------------------------------------------------------
|
|
|
|
sub xls_totals_footer {
|
|
|
|
my ($resize_cell_to,$col_width,$xlsfolder) = @_ ;
|
|
|
|
$worksheet{$ws}->set_column("A:$resize_cell_to",$col_width);
|
|
|
|
$worksheet{$ws}->activate();
|
|
$worksheet{$ws}->select();
|
|
|
|
my @months = ("dummy","Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec") ;
|
|
|
|
$xlsrow+=4;$xlscol=0; # my $last_year=$now_year-1 ;
|
|
my $last_year=$i{leave_year}-1 ;
|
|
|
|
$nxlsrow=$xlsrow+1;
|
|
$worksheet{$ws}->merge_range($xlsrow,$xlscol,$nxlsrow,$xlscol,"",$format35); $xlscol++ ;
|
|
$worksheet{$ws}->merge_range($xlsrow,$xlscol,$nxlsrow,$xlscol,"Opening\nBalance",$format75); $xlscol++ ;
|
|
$worksheet{$ws}->merge_range($xlsrow,$xlscol,$nxlsrow,$xlscol,"Leave\nCredit",$format75); $xlscol++ ;
|
|
$worksheet{$ws}->merge_range($xlsrow,$xlscol,$nxlsrow,$xlscol,"Leave\nTaken",$format75); $xlscol++ ;
|
|
$worksheet{$ws}->merge_range($xlsrow,$xlscol,$nxlsrow,$xlscol,"Closing\nBalance",$format74); $xlscol++ ;
|
|
# $worksheet{$ws}->merge_range($xlsrow,$xlscol,$nxlsrow,$xlscol,"Leave\nDates",$format75); $xlscol++ ;
|
|
$worksheet{$ws}->merge_range($xlsrow,$xlscol,$nxlsrow,$xlscol,"Compassionate\nLeave",$format75); $xlscol++ ;
|
|
$worksheet{$ws}->merge_range($xlsrow,$xlscol,$nxlsrow,$xlscol,"Sick\nLeave",$format75); $xlscol++ ;
|
|
$worksheet{$ws}->merge_range($xlsrow,$xlscol,$nxlsrow,$xlscol,"Maternity\nLeave",$format75); $xlscol=0;
|
|
|
|
$print_tbody_summary = qq(<THEAD><TR>
|
|
<TH class="dt-center"></TH>
|
|
<TH class="dt-center">Opening Balance</TH>
|
|
<TH class="dt-center">Leave Credit</TH>
|
|
<TH class="dt-center">Leave Taken</TH>
|
|
<TH class="dt-center">Closing Balance</TH>
|
|
<TH class="dt-center">Compassionate Leave</TH>
|
|
<TH class="dt-center">Sick Leave</TH>
|
|
<TH class="dt-center">Maternity Leave</TH>
|
|
</TR></THEAD>);
|
|
|
|
$xlsrow++ ; # next row
|
|
$xlsrow++ ; # next row
|
|
$nxlsrow=$xlsrow+1;
|
|
$worksheet{$ws}->merge_range($xlsrow,$xlscol,$nxlsrow,$xlscol,"Carried Over\nFrom $last_year",$format75); $xlscol++ ;
|
|
$worksheet{$ws}->merge_range($xlsrow,$xlscol,$nxlsrow,$xlscol,"$days_carried_over{annual}",$format76); $xlscol++ ;
|
|
$worksheet{$ws}->merge_range($xlsrow,$xlscol,$nxlsrow,$xlscol,"",$format76); $xlscol++ ;
|
|
$worksheet{$ws}->merge_range($xlsrow,$xlscol,$nxlsrow,$xlscol,"",$format76); $xlscol++ ;
|
|
$worksheet{$ws}->merge_range($xlsrow,$xlscol,$nxlsrow,$xlscol,"",$format76); $xlscol++ ;
|
|
# $worksheet{$ws}->merge_range($xlsrow,$xlscol,$nxlsrow,$xlscol,"",$format76); $xlscol++ ;
|
|
$worksheet{$ws}->merge_range($xlsrow,$xlscol,$nxlsrow,$xlscol,"",$format76); $xlscol++ ;
|
|
$worksheet{$ws}->merge_range($xlsrow,$xlscol,$nxlsrow,$xlscol,"",$format76); $xlscol++ ;
|
|
$worksheet{$ws}->merge_range($xlsrow,$xlscol,$nxlsrow,$xlscol,"",$format76); $xlscol=0; $xlsrow++ ; $xlsrow++ ;
|
|
|
|
$print_tbody_summary .= qq(<tbody><TR>
|
|
<th class="dt-center">Carried Over From $last_year</th>
|
|
<td class="dt-center"><strong>$days_carried_over{annual}</strong></td>
|
|
<td></td>
|
|
<td></td>
|
|
<td></td>
|
|
<td></td>
|
|
<td></td>
|
|
<td></td>
|
|
</TR>);
|
|
|
|
my ($l_dy,$l_dm,$l_dd,$l_th,$l_tm,$l_ts) = &common_min_split_sql_date_time($leave_start_date{annual});
|
|
|
|
my $start_month = 1 ;
|
|
my $months_to_calculate = 12 ;
|
|
|
|
# if ($l_dy eq $now_year) {
|
|
if ($l_dy eq $i{leave_year}) {
|
|
my $m = sprintf("%0.0f",$l_dm) ; # month
|
|
$start_month = $m ;
|
|
$months_to_calculate = 12-$m+1 ;
|
|
}
|
|
|
|
# my $days_entitled_pm = sprintf("%0.2f",($days_entitled{annual} / 12)) ;
|
|
my $days_entitled_pm = sprintf("%0.2f",($days_entitled{annual} / $months_to_calculate)) ;
|
|
# my $balance = $days_carried_over{annual} ;
|
|
my $balance = sprintf("%0.2f",($days_carried_over{annual})) ;
|
|
|
|
my $cell4 = '' ; my $sx=0;
|
|
|
|
foreach (1 .. 12) {
|
|
|
|
&xlscreator_write_xls($ws,$xlsrow,$xlscol,"$months[$_] $i{leave_year}",$format40); $xlscol++ ;
|
|
if ($_ == 1) { &xlscreator_write_xls($ws,$xlsrow,$xlscol,"$balance",$format1); } $xlscol++ ;
|
|
|
|
$print_tbody_summary .= qq(<TR>
|
|
<th class="dt-center">$months[$_] $i{leave_year}</th>
|
|
<td class="dt-center">$balance</td>
|
|
);
|
|
|
|
if ($_ < $start_month) { $xlscol=0; $xlsrow++ ; next ; }
|
|
|
|
$sx++;
|
|
|
|
$balance += $days_entitled_pm ;
|
|
$balance -= $days_taken_by_month{$_}{$i{leave_year}}{annual} ;
|
|
|
|
# &common_min_debug("annual balance = $balance [+$days_entitled_pm - $days_taken_by_month{$_}{$i{leave_year}}{annual}] [$months[$_] $i{leave_year}]") ;
|
|
|
|
$balance = sprintf("%0.2f",$balance) ;
|
|
|
|
# my $days_annual = $days_taken_by_month{$_}{$i{leave_year}}{annual} * -1 ;
|
|
my $days_annual = sprintf("%0.2f",($days_taken_by_month{$_}{$i{leave_year}}{annual})) ; if ($days_annual == 0) { $days_annual = '' ; }
|
|
&xlscreator_write_xls($ws,$xlsrow,$xlscol,"$days_entitled_pm",$format1); $xlscol++ ;
|
|
&xlscreator_write_xls($ws,$xlsrow,$xlscol,"$days_annual",$format1); $xlscol++ ;
|
|
# &xlscreator_write_xls($ws,$xlsrow,$xlscol,"$balance",$format73); $xlscol++ ;
|
|
|
|
my $format = $format1 ; if ($_ == 12) { $format = $format73 ; }
|
|
|
|
my $cell1 = &xl_rowcol_to_cell($xlsrow,1) ;
|
|
my $cell2 = &xl_rowcol_to_cell($xlsrow,2) ;
|
|
my $cell3 = &xl_rowcol_to_cell($xlsrow,3) ;
|
|
# &xlscreator_write_formula($ws,$xlsrow,$xlscol,"=SUM($cellfrom:$cellto)",$format); $xlscol++ ;
|
|
|
|
# &common_min_debug("$ws,$xlsrow,$xlscol,($cell1+$cell2-$cell3)") ;
|
|
|
|
if ($sx == 1) {
|
|
&xlscreator_write_formula($ws,$xlsrow,$xlscol,"($cell1+$cell2-$cell3)",$format1); $xlscol++ ;
|
|
}
|
|
else
|
|
{
|
|
&xlscreator_write_formula($ws,$xlsrow,1,"($cell4)",$format1);
|
|
&xlscreator_write_formula($ws,$xlsrow,$xlscol,"($cell4+$cell2-$cell3)",$format); $xlscol++ ;
|
|
}
|
|
|
|
$cell4 = &xl_rowcol_to_cell($xlsrow,4) ;
|
|
|
|
# &xlscreator_write_xls($ws,$xlsrow,$xlscol,"$days_txt",$format1); $xlscol++ ;
|
|
my $days_compassionate = sprintf("%0.2f",($days_taken_by_month{$_}{$i{leave_year}}{compassionate})) ; if ($days_compassionate == 0) { $days_compassionate = '' ; }
|
|
&xlscreator_write_xls($ws,$xlsrow,$xlscol,$days_compassionate,$format1); $xlscol++ ;
|
|
my $days_sick = sprintf("%0.2f",($days_taken_by_month{$_}{$i{leave_year}}{sick})) ; if ($days_sick == 0) { $days_sick = '' ; }
|
|
&xlscreator_write_xls($ws,$xlsrow,$xlscol,$days_sick,$format1); $xlscol++ ;
|
|
my $days_maternity = sprintf("%0.2f",($days_taken_by_month{$_}{$i{leave_year}}{maternity})) ; if ($days_maternity == 0) { $days_maternity = '' ; }
|
|
&xlscreator_write_xls($ws,$xlsrow,$xlscol,$days_maternity,$format1); $xlscol=0 ;
|
|
$xlsrow++ ;
|
|
|
|
if ($_ == 12) { $t = 'th' ; } else { $t = 'td' ; }
|
|
|
|
$print_tbody_summary .= qq(
|
|
<td class="dt-center">$days_entitled_pm</td>
|
|
<td class="dt-center">$days_annual</td>
|
|
<$t class="dt-center">$balance</$t>
|
|
<td class="dt-center">$days_compassionate</td>
|
|
<td class="dt-center">$days_sick</td>
|
|
<td class="dt-center">$days_maternity</td>
|
|
</TR>);
|
|
}
|
|
|
|
$print_tbody_summary .= qq(<tbody>) ;
|
|
|
|
my $output_row = $xlsrow+1 ;
|
|
|
|
foreach my $col (1 .. 7) {
|
|
|
|
if (($col==1) or ($col==4)) { next ; }
|
|
|
|
my $cellfrom = &xl_rowcol_to_cell($xlsrow-14,$col) ;
|
|
my $cellto = &xl_rowcol_to_cell($xlsrow,$col) ;
|
|
|
|
# &common_min_debug("$ws,$output_row,$col,SUM($cellfrom:$cellto)") ;
|
|
&xlscreator_write_formula($ws,$output_row,$col,"=SUM($cellfrom:$cellto)",$format67);
|
|
|
|
|
|
}
|
|
|
|
&xlscreator_close_xls;
|
|
|
|
if ((-f "$xlspath/$xlsname") and ($iaction eq 'report')) {
|
|
# $box_icon .= qq(<div class="box-icon" title='Export to Excel' data-toggle="tooltip" data-placement="left"><a class="btn btn-default btn-round" href="/xls/export/$xlsname" style="padding-top:1px;"><i class="glyphicon glyphicon-download-alt icon-white"></i></a></div>) ;
|
|
$xtrabtns .= qq(<a class="btn btn-info btn" href="javascript:sendReport();"><i class="glyphicon glyphicon-envelope"></i></a>);
|
|
$xtrabtns .= qq(<a style="margin-left:10px;" href="/xls/$xlsfolder/$xlsname"><img src="/img/icons/doc/xls.png" style="width:40px;height:40px;"></a>);
|
|
}
|
|
|
|
} #------------------------------------------------------------------------------------------
|
|
|
|
sub report_screen {
|
|
|
|
our $lcol = 3 ;
|
|
our $fcol = 5 ;
|
|
|
|
if ($i{leave_year}) { $selected{leave_year}{$i{leave_year}} = 'SELECTED' ; } else { $selected{leave_year}{$now_year} = 'SELECTED' ; }
|
|
|
|
my @years = ($now_year-3,$now_year-2,$now_year-1,$now_year,$now_year+1) ;
|
|
|
|
foreach (@years) {
|
|
$opts{leave_year} .= qq(<option value="$_" $selected{leave_year}{$_}>$_</option>) ;
|
|
}
|
|
|
|
# &common_debug("report_screen - super=$useropts{super}{$username} - userid=$userid") ;
|
|
|
|
# my $sql_where = "`username`<>'rory' AND `username`<>'duvan'" ;
|
|
|
|
@user_excl_sql = () ;
|
|
|
|
foreach (keys %{$useropts{it}}) {
|
|
push @user_excl_sql, "username <> '$_'" ;
|
|
}
|
|
|
|
my $sql_where = join(" AND ", @user_excl_sql) ;
|
|
|
|
if ((lc $username eq 'bruce') or (lc $username eq 'graham')) {
|
|
if ($i{user_id}) { $selected{user_id}{$i{user_id}} = 'SELECTED' ; } else { $selected{user_id}{CT} = 'SELECTED' ; }
|
|
$opts{user_id} = qq(<option value="CT" $selected{user_id}{CT}>Cape Town</option>) ;
|
|
$sql_where .= " AND `based_in`='CT'" ;
|
|
}
|
|
elsif ($useropts{super}{lc $username}) {
|
|
if ($i{user_id}) { $selected{user_id}{$i{user_id}} = 'SELECTED' ; } else { $selected{user_id}{all} = 'SELECTED' ; }
|
|
$opts{user_id} = qq(<option value="all" $selected{user_id}{all}>All</option>) ;
|
|
}
|
|
else
|
|
{
|
|
$sql_where = "`id`='$userid'" ;
|
|
}
|
|
|
|
%db=();
|
|
|
|
# &common_min_select_opts('user_id','users','name',$i{user_id},'','',"$sql_where") ;
|
|
&common_min_select_opts('user_id','users','name','','','',"$sql_where") ;
|
|
|
|
# &common_min_debug("report_screen [$opts{user_id}]") ;
|
|
|
|
if ($i{leave_report}) { $leave_report_checked = 'CHECKED'; } else { $leave_report_checked = ''; }
|
|
|
|
$print_box_content_rows .= &common_min_forms_start('report') ;
|
|
$print_box_content_rows .= &common_min_form_select('leave_year','','',1,'') ;
|
|
$print_box_content_rows .= &common_min_form_select('user_id','','',1,'','Consultant') ;
|
|
$print_box_content_rows .= &common_min_forms_end('','','report') ;
|
|
|
|
if ($print_tbody) { &thead; }
|
|
|
|
# unless ($i{leave_report}) { $trigger_jquery = qq(var oTable = \$('#whitestar-table').dataTable(); oTable.fnSort( [ [3,'asc'] ] );) ; }
|
|
# unless ($i{leave_report}) { $display_search_option = 'datatable' ; }
|
|
|
|
$print_box_content_rows .= qq(<div class='row'>
|
|
<div class="col-md-12">
|
|
<table id="whitestar-table" class="table table-striped table-bordered bootstrap-datatable $display_search_option responsive">
|
|
$print_thead
|
|
<tbody>
|
|
$print_tbody
|
|
</tbody>
|
|
</table>
|
|
</div>
|
|
</div>) ;
|
|
|
|
my $annual_taken = sprintf("%0.2f",($days_taken_by_year{$i{leave_year}}{annual})) ;
|
|
my $compassionate_taken = sprintf("%0.2f",($days_taken_by_year{$i{leave_year}}{compassionate})) ;
|
|
my $sick_taken = sprintf("%0.2f",($days_taken_by_year{$i{leave_year}}{sick})) ;
|
|
my $maternity_taken = sprintf("%0.2f",($days_taken_by_year{$i{leave_year}}{maternity})) ;
|
|
|
|
if ($leave_settings_exist) {
|
|
$print_tbody_summary .= qq(<TR>
|
|
<th class="dt-center"></th>
|
|
<th class="dt-center"></th>
|
|
<th class="dt-center">$days_entitled{annual}</th>
|
|
<th class="dt-center">$annual_taken</th>
|
|
<th class="dt-center"></th>
|
|
<th class="dt-center">$compassionate_taken</th>
|
|
<th class="dt-center">$sick_taken</th>
|
|
<th class="dt-center">$maternity_taken</th>
|
|
</TR>);
|
|
|
|
$print_box_content_rows .= qq(<div class='row'><div class="col-md-12"> </div></div>
|
|
<div class='row'>
|
|
<div class="col-md-12">
|
|
<table id="whitestar-table" class="table table-striped table-bordered bootstrap-datatable responsive">
|
|
$print_tbody_summary
|
|
</table>
|
|
</div>
|
|
</div>) ;
|
|
}
|
|
|
|
$trigger_jquery_raw .= qq(
|
|
function sendReport() {
|
|
dlgMdl("/scripts/dialog/send_report.pl?$user{$i{user_id}} Leave Report&Please see attached report.&$xlsname+&$xlspath","Send Report",'height:450px;','') ;
|
|
}
|
|
);
|
|
|
|
&screen1 ;
|
|
|
|
} #-------------------------------------------------------------------------------
|
|
|
|
sub thead {
|
|
|
|
&common_min_thead ;
|
|
|
|
$print_thead_ttl = qq(<THEAD><TR>
|
|
<TH class="dt-center"></TH>
|
|
<TH class="dt-center">TYPE</TH>
|
|
<TH class="dt-center">CARRIED OVER</TH>
|
|
<TH class="dt-center">ENTITLED</TH>
|
|
<TH class="dt-center">TAKEN</TH>
|
|
<TH class="dt-center">BALANCE</TH>
|
|
<TH class="dt-center"></TH>
|
|
</TR></THEAD>);
|
|
|
|
} #-------------------------------------------------------------------------------
|
|
|
|
sub page_opts {
|
|
|
|
our $glyphicon = 'global' ;
|
|
our $lcpage = 'leave-report' ; $ucpage = uc $lcpage ; $ucfirstpage = ucfirst $lcpage ;
|
|
# our $table = 'leave_settings' ;
|
|
our $table = 'events' ;
|
|
our $srchscr = 1 ;
|
|
# our $addbkb = 1 ;
|
|
# our $srchjqy = 1 ; # incorporate jquery into the save event to accommodate the multiple chosen options
|
|
|
|
} #-------------------------------------------------------------------------------
|
|
|
|
sub screen1 {
|
|
|
|
&thead;
|
|
|
|
&common_min_alert_type ;
|
|
|
|
require _blank ;
|
|
|
|
exit ;
|
|
|
|
} #------------------------------------------------------------------------------------------
|
|
|
|
use common ;
|
|
use report ;
|
|
use xlscreator ;
|
|
use today ;
|
|
use pdf ;
|
|
|
|
1; |