406 lines
14 KiB
Perl
406 lines
14 KiB
Perl
|
#!/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;
|
||
|
|
use Time::Seconds;
|
||
|
|
|
||
|
|
# my $start_time_str = "09:30:00" ;
|
||
|
|
# my $end_time_str = "17:45:00" ;
|
||
|
|
|
||
|
|
# Create Time::Piece objects for start and end times
|
||
|
|
# my $start_time = Time::Piece->strptime($start_time_str, "%H:%M:%S");
|
||
|
|
# my $end_time = Time::Piece->strptime($end_time_str, "%H:%M:%S");
|
||
|
|
|
||
|
|
# Calculate the difference in seconds and convert to hours
|
||
|
|
# my $time_diff_in_hours = (Time::Piece->strptime($end_time_str,"%H:%M:%S") - Time::Piece->strptime($start_time_str,"%H:%M:%S")) / ONE_HOUR;
|
||
|
|
|
||
|
|
&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 $open_new_tab = qq~target="_blank"~ ;
|
||
|
|
|
||
|
|
# 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 '' ;
|
||
|
|
$db_ignore_open_close = 0 ; # do one open and one close instead of repeating it
|
||
|
|
&db_close_conn ;
|
||
|
|
|
||
|
|
# Create table bank_transaction_categories (
|
||
|
|
# id int(5) not null,
|
||
|
|
# name varchar(100) not null,
|
||
|
|
# PRIMARY key(id)
|
||
|
|
# )
|
||
|
|
|
||
|
|
# create table bank_transactions (
|
||
|
|
# id int(10) not null,
|
||
|
|
# date_time datetime not null,
|
||
|
|
# transaction_date date not null,
|
||
|
|
# amount decimal(13,2) default '0.00',
|
||
|
|
# balance decimal(13,2) default '0.00',
|
||
|
|
# decription varchar(100) not null,
|
||
|
|
# account_nr varchar(25) not null,
|
||
|
|
# category_id int(5) default '0',
|
||
|
|
# client_id int(10) default '0'.
|
||
|
|
# invoice_nr varchar(25) not null,
|
||
|
|
# PRIMARY KEY(id)
|
||
|
|
# )
|
||
|
|
|
||
|
|
# if ($iaction eq 'report') {
|
||
|
|
# &common_min_load_params ;
|
||
|
|
# &db_open_ro ;
|
||
|
|
# our $db_ignore_open_close = 1 ; # do one open and one close instead of repeating it
|
||
|
|
# &report_ifields ;
|
||
|
|
# &list_screen ;
|
||
|
|
# $db_ignore_open_close = 0 ; # do one open and one close instead of repeating it
|
||
|
|
# &db_close_conn ;
|
||
|
|
# &screen3 ;
|
||
|
|
# }
|
||
|
|
|
||
|
|
# if ($iaction eq 'save') {
|
||
|
|
# &common_min_load_params ;
|
||
|
|
# &db_open_upd ;
|
||
|
|
# our $db_ignore_open_close = 1 ; # do one open and one close instead of repeating it
|
||
|
|
# &update ;
|
||
|
|
# $db_ignore_open_close = 0 ; # do one open and one close instead of repeating it
|
||
|
|
# &db_close_conn ;
|
||
|
|
# exit if $username eq 'rory' && $testing ;
|
||
|
|
# &screen4 ;
|
||
|
|
# }
|
||
|
|
|
||
|
|
# &common_min_action;
|
||
|
|
|
||
|
|
exit ;
|
||
|
|
|
||
|
|
#------------------------------------------------------------------------------------------
|
||
|
|
|
||
|
|
# sub update {
|
||
|
|
|
||
|
|
|
||
|
|
# } #------------------------------------------------------------------------------------------
|
||
|
|
|
||
|
|
# sub report_ifields {
|
||
|
|
|
||
|
|
# if ($i{month}) {
|
||
|
|
# my ($year_input,$month_input) = split(/\-/,$i{month}) ;
|
||
|
|
# my @months = ("","Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec") ;
|
||
|
|
# push @report_results, "(TRANSACTION DATE IN '$months[$month_input] $year_input')" ;
|
||
|
|
# push @report_sql, "(transaction_date LIKE '%$i{month}%')" ;
|
||
|
|
# }
|
||
|
|
|
||
|
|
# $report_results_msg = uc join(', ',@report_results) ;
|
||
|
|
|
||
|
|
# if (not $report_results_msg) {
|
||
|
|
# } else {
|
||
|
|
# $isaved = qq(SELECT WHERE $report_results_msg) ;
|
||
|
|
# }
|
||
|
|
|
||
|
|
# } #------------------------------------------------------------------------------------------
|
||
|
|
|
||
|
|
# sub list_screen {
|
||
|
|
|
||
|
|
# $print_box_content_rows .= &common_min_forms_start('bank-transactions') ;
|
||
|
|
|
||
|
|
# my $srch_where_sql = join(' AND ', @report_sql) ;
|
||
|
|
# my $srch_where_sql_2 = join(' AND ', @report_sql_2) ;
|
||
|
|
|
||
|
|
# &load_list_vars("$srch_where_sql","$srch_where_sql_2") ;
|
||
|
|
|
||
|
|
# my ($year_input,$month_input) = split(/\-/,$i{month}) ;
|
||
|
|
# my @months = ("","Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec") ;
|
||
|
|
|
||
|
|
# our $xlsxheading = "$months[$month_input] $year_input" ;
|
||
|
|
# &report_xlsx_export_header("Bank Transactions",'bank_transactions') ;
|
||
|
|
|
||
|
|
# $xlsxrow = 2 ;
|
||
|
|
|
||
|
|
# $xlsxcol = 0 ;
|
||
|
|
# foreach my $dc (sort { $b cmp $a } keys %bank_transaction_categories) {
|
||
|
|
# &xlsxcreator_write_xlsx($ws,$xlsxrow,$xlsxcol,uc $dc,$format117) ;
|
||
|
|
# $xlsxrow++ ;
|
||
|
|
# foreach my $cat_id (sort { $bank_transaction_categories{$dc}{$a} cmp $bank_transaction_categories{$dc}{$b} } keys %{$bank_transaction_categories{$dc}}) {
|
||
|
|
# next if $cat_id == 27 ;
|
||
|
|
# &xlsxcreator_write_xlsx($ws,$xlsxrow,$xlsxcol,$db{bank_transaction_categories}{$cat_id}{name},$format118) ;
|
||
|
|
# $xlsxrow++ ;
|
||
|
|
# my $start_row = $xlsxrow ;
|
||
|
|
# foreach my $trans_id (keys %{$transactions_by_category{$cat_id}}) {
|
||
|
|
# &xlsxcreator_write_xlsx($ws,$xlsxrow,0,"",$format1) ;
|
||
|
|
# &xlsxcreator_write_xlsx($ws,$xlsxrow,1,substr($db{$table}{$trans_id}{transaction_date},8,2) . "/" . substr($db{$table}{$trans_id}{transaction_date},5,2) . "/" . substr($db{$table}{$trans_id}{transaction_date},0,4),$format1) ;
|
||
|
|
# &xlsxcreator_write_xlsx($ws,$xlsxrow,2,"",$format1) ;
|
||
|
|
# &xlsxcreator_write_xlsx($ws,$xlsxrow,3,$db{$table}{$trans_id}{amount},$format120) ;
|
||
|
|
# &xlsxcreator_write_xlsx($ws,$xlsxrow,4,$db{$table}{$trans_id}{description},$format1) ;
|
||
|
|
# $xlsxrow++ ;
|
||
|
|
# }
|
||
|
|
# $xlsxrow++ ;
|
||
|
|
# &xlsxcreator_write_formula($ws,$xlsxrow,3,"=SUM(D$start_row:D$xlsxrow)",$format119) ;
|
||
|
|
# $xlsxrow++ ;
|
||
|
|
# }
|
||
|
|
# }
|
||
|
|
|
||
|
|
# my $cat_id = 27 ;
|
||
|
|
|
||
|
|
# for (0 .. 5) {
|
||
|
|
# &xlsxcreator_write_xlsx($ws,$xlsxrow,$_," ",$format121) ;
|
||
|
|
# }
|
||
|
|
|
||
|
|
# my $start_row = $xlsxrow ;
|
||
|
|
|
||
|
|
# $xlsxrow++ ;
|
||
|
|
# &xlsxcreator_write_xlsx($ws,$xlsxrow,$xlsxcol,$db{bank_transaction_categories}{$cat_id}{name},$format118) ;
|
||
|
|
# $xlsxrow++ ;
|
||
|
|
# my $start_row = $xlsxrow ;
|
||
|
|
# foreach my $trans_id (keys %{$transactions_by_category{$cat_id}}) {
|
||
|
|
# &xlsxcreator_write_xlsx($ws,$xlsxrow,0,"",$format1) ;
|
||
|
|
# &xlsxcreator_write_xlsx($ws,$xlsxrow,1,substr($db{$table}{$trans_id}{transaction_date},8,2) . "/" . substr($db{$table}{$trans_id}{transaction_date},5,2) . "/" . substr($db{$table}{$trans_id}{transaction_date},0,4),$format1) ;
|
||
|
|
# &xlsxcreator_write_xlsx($ws,$xlsxrow,2,"",$format1) ;
|
||
|
|
# &xlsxcreator_write_xlsx($ws,$xlsxrow,3,$db{$table}{$trans_id}{amount},$format120) ;
|
||
|
|
# &xlsxcreator_write_xlsx($ws,$xlsxrow,4,$db{$table}{$trans_id}{description},$format1) ;
|
||
|
|
# $xlsxrow++ ;
|
||
|
|
# }
|
||
|
|
# $xlsxrow++ ;
|
||
|
|
# &xlsxcreator_write_formula($ws,$xlsxrow,3,"=SUM(D$start_row:D$xlsxrow)",$format119) ;
|
||
|
|
# $xlsxrow++ ;
|
||
|
|
|
||
|
|
# for (0 .. 5) {
|
||
|
|
# &xlsxcreator_write_xlsx($ws,$xlsxrow,$_," ",$format121) ;
|
||
|
|
# }
|
||
|
|
|
||
|
|
# for ($start_row - 1 .. $xlsxrow) {
|
||
|
|
# &xlsxcreator_write_xlsx($ws,$_,6," ",$format122) ;
|
||
|
|
# }
|
||
|
|
|
||
|
|
# $worksheet{$ws}->set_column(1,3,20) ;
|
||
|
|
# $worksheet{$ws}->set_column(4,5,30) ;
|
||
|
|
# &report_xlsx_export_footer('F',15,'bank_transactions') ;
|
||
|
|
|
||
|
|
# } #------------------------------------------------------------------------------------------
|
||
|
|
|
||
|
|
# sub load_list_vars {
|
||
|
|
|
||
|
|
# my ($where,$where_2) = @_ ;
|
||
|
|
|
||
|
|
# &db_min_ro($table,"*","$where",'','') ;
|
||
|
|
|
||
|
|
# our %transactions_by_category = () ;
|
||
|
|
|
||
|
|
# foreach (keys %{$db{$table}}) {
|
||
|
|
# $transactions_by_category{$db{$table}{$_}{category_id}}{$_} = 1 ;
|
||
|
|
# }
|
||
|
|
|
||
|
|
# &db_min_ro('bank_transaction_categories',"*","",'','') ;
|
||
|
|
|
||
|
|
# our %bank_transaction_categories = () ;
|
||
|
|
|
||
|
|
# foreach (keys %{$db{bank_transaction_categories}}) {
|
||
|
|
|
||
|
|
# if ($db{bank_transaction_categories}{$_}{income_source}) {
|
||
|
|
# $bank_transaction_categories{income}{$_} = $db{bank_transaction_categories}{$_}{name} ;
|
||
|
|
# } else {
|
||
|
|
# $bank_transaction_categories{expenses}{$_} = $db{bank_transaction_categories}{$_}{name} ;
|
||
|
|
# }
|
||
|
|
# }
|
||
|
|
|
||
|
|
# } #------------------------------------------------------------------------------------------
|
||
|
|
|
||
|
|
# sub get_events_with_other_costs {
|
||
|
|
|
||
|
|
# my ($where) = @_ ;
|
||
|
|
|
||
|
|
# } #------------------------------------------------------------------------------------------
|
||
|
|
|
||
|
|
sub thead {
|
||
|
|
|
||
|
|
&common_min_thead ;
|
||
|
|
|
||
|
|
} #-------------------------------------------------------------------------------
|
||
|
|
|
||
|
|
sub page_opts {
|
||
|
|
|
||
|
|
our $glyphicon = 'list' ;
|
||
|
|
our $lcpage = 'bank-transactions-report' ;
|
||
|
|
|
||
|
|
&common_page_name ;
|
||
|
|
|
||
|
|
our $table = 'bank_transactions' ;
|
||
|
|
$page_title = 'Bank Transactions' ;
|
||
|
|
|
||
|
|
$box_icon .= qq~
|
||
|
|
<div class="box-icon">
|
||
|
|
<a class="btn btn-primary btn-round" href="javascript:importDoc('csv','',1);" style="padding-top:1px;" data-toggle="tooltip" data-placement="left" data-original-title="Import Bank Transactions">
|
||
|
|
<i class="glyphicon glyphicon-upload icon-white"></i>
|
||
|
|
</a>
|
||
|
|
</div>
|
||
|
|
<div class="box-icon">
|
||
|
|
<a class="btn btn-primary btn-round" href="javascript:importDoc('csv','',2);" style="padding-top:1px;" data-toggle="tooltip" data-placement="left" data-original-title="Import Bank Transaction Categories">
|
||
|
|
<i class="glyphicon glyphicon-upload icon-white"></i>
|
||
|
|
</a>
|
||
|
|
</div>
|
||
|
|
<div class="box-icon" id='build_excel_button'>
|
||
|
|
<a class='btn btn-primary btn-round' href=javascript:build_excel('create','$now_year-01-01','$now_year-$now_mm-$now_dd'); style='padding-top:1px;' data-toggle='tooltip' data-placement='left' data-original-title='Build Bank Transactions'>
|
||
|
|
<i class='glyphicon glyphicon-upload icon-white'></i>
|
||
|
|
</a>
|
||
|
|
</div>
|
||
|
|
~ ;
|
||
|
|
|
||
|
|
$trigger_jquery_raw .= qq~
|
||
|
|
function importDoc(t,b,a) {
|
||
|
|
let script = (a == 1) ? "import_bank_trans" : (a ==2) ? "import_bank_trans_cat" : "" ;
|
||
|
|
dlgMdl("$useropts{'scripts'}/dialog/"+script+".pl?"+t+"&"+b,"Import CSV",'','max-dialog') ;
|
||
|
|
}
|
||
|
|
|
||
|
|
function build_excel(a,b,c) {
|
||
|
|
let script = "get_bank_transactions" ;
|
||
|
|
dlgMdl("$useropts{'scripts'}/dialog/"+script+".pl?"+a+"&"+b+"&"+c,"Get Bank Transactions",'','max-dialog') ;
|
||
|
|
}
|
||
|
|
~;
|
||
|
|
|
||
|
|
$trigger_jquery_raw .= qq~
|
||
|
|
console.log("Hello World") ;
|
||
|
|
\$('#datetimepickerDate_from,#datetimepickerDate_to').on('changeDate', function(e) {
|
||
|
|
\$("#build_excel_button").html("<a class='btn btn-primary btn-round' href=javascript:build_excel('create','"+\$("input[name='date_from']").val()+"','"+\$("input[name='date_to']").val()+"'); style='padding-top:1px;' data-toggle='tooltip' data-placement='left' data-original-title='Build Bank Transactions'><i class='glyphicon glyphicon-upload icon-white'></i></a>") ;
|
||
|
|
}) ;
|
||
|
|
~ ;
|
||
|
|
|
||
|
|
} #-------------------------------------------------------------------------------
|
||
|
|
|
||
|
|
sub report_screen {
|
||
|
|
|
||
|
|
our $lcol = 1 ;
|
||
|
|
our $fcol = 3 ;
|
||
|
|
|
||
|
|
# $print_box_content_rows .= &common_min_forms_start('report') ;
|
||
|
|
|
||
|
|
my $event_na_exists = 0 ; my $events_exists = 0 ; my %selected = () ;
|
||
|
|
|
||
|
|
# &get_events_with_other_costs ;
|
||
|
|
|
||
|
|
$print_box_content_rows .= &common_min_form_datepicker('date_from',"$now_year-01-01") ;
|
||
|
|
$print_box_content_rows .= &common_min_form_datepicker('date_to',"$now_year-$now_mm-$now_dd") ;
|
||
|
|
|
||
|
|
# $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") ;
|
||
|
|
|
||
|
|
# $extra_form_fields .= qq~
|
||
|
|
# <input type="hidden" name="client_id" value="$i{client_id}">
|
||
|
|
# <input type="hidden" name="date_from" value="$i{date_from}">
|
||
|
|
# <input type="hidden" name="date_to" value="$i{date_to}">
|
||
|
|
# <input type="hidden" name="event_costing_id" value="$i{event_costing_id}">
|
||
|
|
# <input type="hidden" name="event_quote_id" value="$i{event_quote_id}">
|
||
|
|
# <input type="hidden" name="paid_status" value="$i{paid_status}">
|
||
|
|
# ~ ;
|
||
|
|
|
||
|
|
# $trigger_jquery_raw .= qq~
|
||
|
|
# \$("#savebutt").click( function () {
|
||
|
|
# \$("#bank-transactions-form").submit() ;
|
||
|
|
# }) ;
|
||
|
|
# ~ ;
|
||
|
|
|
||
|
|
# $print_box_content_rows .= &common_min_forms_end('','','save') ;
|
||
|
|
|
||
|
|
# require _blank ;
|
||
|
|
|
||
|
|
# exit ;
|
||
|
|
|
||
|
|
# } #------------------------------------------------------------------------------------------
|
||
|
|
|
||
|
|
# sub screen4 {
|
||
|
|
|
||
|
|
# &common_min_alert_type ;
|
||
|
|
|
||
|
|
# print <<endoftext;
|
||
|
|
# <!doctype html>
|
||
|
|
# <html lang="en">
|
||
|
|
# <head>
|
||
|
|
# <meta charset="utf-8">
|
||
|
|
# <title>$useropts{title}</title>
|
||
|
|
# <script src="$useropts{'bower_components'}/jquery/jquery.min.js"></script>
|
||
|
|
# </head>
|
||
|
|
# <body>
|
||
|
|
# <script>
|
||
|
|
|
||
|
|
# \$(document).ready(function(){ \$('#$lcpage-form').submit(); })
|
||
|
|
|
||
|
|
# </script>
|
||
|
|
# </body>
|
||
|
|
|
||
|
|
# <form role="form" id="$lcpage-form" method="post" action="">
|
||
|
|
# <input type="hidden" name="iaction" value="report">
|
||
|
|
# <input type="hidden" name="client_id" value="$i{client_id}">
|
||
|
|
# <input type="hidden" name="date_from" value="$i{date_from}">
|
||
|
|
# <input type="hidden" name="date_to" value="$i{date_to}">
|
||
|
|
# <input type="hidden" name="event_costing_id" value="$i{event_costing_id}">
|
||
|
|
# <input type="hidden" name="event_quote_id" value="$i{event_quote_id}">
|
||
|
|
# <input type="hidden" name="paid_status" value="$i{paid_status}">
|
||
|
|
# <input type="hidden" name="isaved" value="$success">
|
||
|
|
# </form>
|
||
|
|
|
||
|
|
# </html>
|
||
|
|
# endoftext
|
||
|
|
|
||
|
|
|
||
|
|
# exit ;
|
||
|
|
|
||
|
|
# } #------------------------------------------------------------------------------------------
|
||
|
|
|
||
|
|
use common ;
|
||
|
|
use report ;
|
||
|
|
use xlsxcreator ;
|
||
|
|
use today ;
|
||
|
|
|
||
|
|
1;
|