298 lines
9.1 KiB
Perl
298 lines
9.1 KiB
Perl
|
#!/usr/bin/perl
|
||
|
|
|
||
|
|
BEGIN { use lib '/usr/home/cfg' ; require push_inc ; }
|
||
|
|
|
||
|
|
require cfg ;
|
||
|
|
|
||
|
|
print "Content-type: text/html\n\n";
|
||
|
|
|
||
|
|
&today ;
|
||
|
|
|
||
|
|
#---------------------------------------------------------------------------------------------------------------------------------------------------------------------
|
||
|
|
|
||
|
|
use DBI;
|
||
|
|
use CGI::Carp qw(fatalsToBrowser);
|
||
|
|
use CGI;
|
||
|
|
use Excel::Writer::XLSX ;
|
||
|
|
|
||
|
|
@ARGV = split(/\\*\&/, $ENV{'QUERY_STRING'});
|
||
|
|
$action = $ARGV[0] ;
|
||
|
|
$date_from = $ARGV[1] ;
|
||
|
|
$date_to = $ARGV[2] ;
|
||
|
|
|
||
|
|
our $only_update_number = ($action eq 'add_number_only') ? 1 : 0 ;
|
||
|
|
|
||
|
|
# our $debug = 1 ;
|
||
|
|
our $table = 'bank_transactions' ;
|
||
|
|
|
||
|
|
our ($q) = CGI -> new() ;
|
||
|
|
$iaction = $q -> param('iaction') || $action ;
|
||
|
|
|
||
|
|
$iaction = "add" if $iaction eq 'add_number_only' ;
|
||
|
|
|
||
|
|
#---------------------------------------------------------------------------------------------------------------------------------------------------------------------
|
||
|
|
|
||
|
|
# &db_switch_conn('aisa') ;
|
||
|
|
|
||
|
|
if ($iaction eq 'create') {
|
||
|
|
&common_min_add_screen ;
|
||
|
|
&build_excel ;
|
||
|
|
&screen2 ;
|
||
|
|
}
|
||
|
|
|
||
|
|
# if ($iaction eq 'save') {
|
||
|
|
# &common_min_load_params ;
|
||
|
|
# &build_excel ;
|
||
|
|
# &screen2 ;
|
||
|
|
# }
|
||
|
|
|
||
|
|
&common_min_action;
|
||
|
|
|
||
|
|
exit ;
|
||
|
|
|
||
|
|
#------------------------------------------------------------------------------------------
|
||
|
|
|
||
|
|
sub insert {
|
||
|
|
|
||
|
|
} #------------------------------------------------------------------------------------------
|
||
|
|
|
||
|
|
sub validate {
|
||
|
|
|
||
|
|
} #------------------------------------------------------------------------------------------
|
||
|
|
|
||
|
|
sub build_excel {
|
||
|
|
|
||
|
|
my @months = ("","Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec") ;
|
||
|
|
|
||
|
|
my ($year_input1,$month_input1,$day_input1,$year_input2,$month_input2,$day_input2) ;
|
||
|
|
|
||
|
|
if ($date_from) {
|
||
|
|
($year_input1,$month_input1,$day_input1) = split(/\-/,$date_from) ;
|
||
|
|
# push @report_results, "(TRANSACTION DATE AFTER '$day_input1 $months[$month_input1] $year_input1')" ;
|
||
|
|
push @report_sql, "(transaction_date >= '$date_from')" ;
|
||
|
|
}
|
||
|
|
|
||
|
|
if ($date_to) {
|
||
|
|
($year_input2,$month_input2,$day_input2) = split(/\-/,$date_to) ;
|
||
|
|
# push @report_results, "(TRANSACTION DATE BEFORE '$day_input2 $months[$month_input2] $year_input2')" ;
|
||
|
|
push @report_sql, "(transaction_date <= '$date_to')" ;
|
||
|
|
}
|
||
|
|
|
||
|
|
# $report_results_msg = uc join(', ',@report_results) ;
|
||
|
|
my $srch_where_sql = join(' AND ', @report_sql) ;
|
||
|
|
|
||
|
|
&load_list_vars("$srch_where_sql") ;
|
||
|
|
|
||
|
|
our $xlsxheading = ($year_input1 ne $year_input2) ? "$day_input1 $months[$month_input1] $year_input1 TO $day_input2 $months[$month_input2] $year_input2" :
|
||
|
|
($year_input1 eq $year_input2 && $month_input1 ne $month_input2) ? "$day_input1 $months[$month_input1] TO $day_input2 $months[$month_input2] $year_input2" :
|
||
|
|
($year_input1 eq $year_input2 && $month_input1 eq $month_input2 && $day_input1 ne $day_input2) ? "$day_input1 TO $day_input2 $months[$month_input1] $year_input1" :
|
||
|
|
($year_input1 eq $year_input2 && $month_input1 eq $month_input2 && $day_input1 eq $day_input2) ? "$day_input1 $months[$month_input1] $year_input1" : "" ;
|
||
|
|
|
||
|
|
$xlsxheading = "Bank Transactions " . $xlsxheading ;
|
||
|
|
|
||
|
|
&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,$trans_id,$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,$db{$table}{$trans_id}{account_nr},$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) ;
|
||
|
|
&xlsxcreator_write_xlsx($ws,$xlsxrow,5,$db{bank_transaction_categories}{$cat_id}{name},$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 add_screen {
|
||
|
|
|
||
|
|
$box_header_title = 'Get Bank Transactions' ;
|
||
|
|
$hidden_action = 'search' ;
|
||
|
|
$head_col_width = 2 ;
|
||
|
|
|
||
|
|
} #------------------------------------------------------------------------------------------
|
||
|
|
|
||
|
|
sub add_db_fields {
|
||
|
|
|
||
|
|
$ignore{iaction} = 1 ;
|
||
|
|
$ignore{id} = 1 ;
|
||
|
|
$required{date_from} = 1 ;
|
||
|
|
$required{date_to} = 1 ;
|
||
|
|
$datepicker{date_from} = 1 ;
|
||
|
|
$datepicker{date_to} = 1 ;
|
||
|
|
$sort_field{1} = 'date_from' ;
|
||
|
|
$sort_field{2} = 'date_to' ;
|
||
|
|
$db{$table}{''}{date_from} = "$now_year-01-01" ;
|
||
|
|
|
||
|
|
} #------------------------------------------------------------------------------------------
|
||
|
|
|
||
|
|
sub thead {
|
||
|
|
|
||
|
|
&common_min_thead ;
|
||
|
|
|
||
|
|
} #-------------------------------------------------------------------------------
|
||
|
|
|
||
|
|
sub screen1 {
|
||
|
|
|
||
|
|
# $trigger_jquery_raw .= qq~
|
||
|
|
# \$('#btn-close-customer').click(function (e) {
|
||
|
|
# parent.BootstrapDialog.closeAll() ;
|
||
|
|
# });
|
||
|
|
# ~ ;
|
||
|
|
|
||
|
|
&dialog_load_screen ;
|
||
|
|
|
||
|
|
} #------------------------------------------------------------------------------------------
|
||
|
|
|
||
|
|
sub screen2 {
|
||
|
|
|
||
|
|
$xlsxpath =~ s|/var/www/html/||;
|
||
|
|
|
||
|
|
$trigger_jquery_raw = qq~
|
||
|
|
let url = "https://$useropts{web}/$xlsxpath" + "/" + "$xlsxname" ;
|
||
|
|
\$.get(url) ;
|
||
|
|
parent.BootstrapDialog.closeAll() ;
|
||
|
|
~;
|
||
|
|
|
||
|
|
&common_min_dialog_save_screen ;
|
||
|
|
|
||
|
|
} #------------------------------------------------------------------------------------------
|
||
|
|
|
||
|
|
sub dialog_load_screen {
|
||
|
|
|
||
|
|
&dialog_common ;
|
||
|
|
|
||
|
|
print <<ENDOFTEXT;
|
||
|
|
$dialog{'common'}{'head'}
|
||
|
|
|
||
|
|
<div id="content" class="col-lg-12 col-sm-12">
|
||
|
|
<!-- content starts -->
|
||
|
|
|
||
|
|
$alert
|
||
|
|
|
||
|
|
<div class="row">
|
||
|
|
<div class="box col-md-12">
|
||
|
|
<div class="box-inner">
|
||
|
|
<div class="box-content">
|
||
|
|
$print_box_content_rows
|
||
|
|
</div>
|
||
|
|
</div>
|
||
|
|
</div>
|
||
|
|
</div><!--/row-->
|
||
|
|
|
||
|
|
</div><!--/fluid-row-->
|
||
|
|
|
||
|
|
</div><!--/.fluid-container-->
|
||
|
|
|
||
|
|
<!-- external javascript -->
|
||
|
|
|
||
|
|
<script src="$useropts{'bower_components'}/chosen/chosen.jquery.min.js"></script>
|
||
|
|
<script src="$useropts{'bower_components'}/bootstrap/dist/js/bootstrap.min.js"></script>
|
||
|
|
<script src="$useropts{'js'}/jquery.form-validator.min.js"></script>
|
||
|
|
|
||
|
|
<script src="/js/bootstrap-datetimepicker.min.js"></script>
|
||
|
|
<script src="/js/bootstrap-datepicker.js"></script>
|
||
|
|
|
||
|
|
<script>
|
||
|
|
\$.validate();
|
||
|
|
|
||
|
|
$trigger_jquery_raw
|
||
|
|
|
||
|
|
</script>
|
||
|
|
|
||
|
|
</body>
|
||
|
|
</html>
|
||
|
|
ENDOFTEXT
|
||
|
|
#
|
||
|
|
|
||
|
|
exit;
|
||
|
|
|
||
|
|
} #------------------------------------------------------------------------------------------
|
||
|
|
|
||
|
|
use today ;
|
||
|
|
use dialog ;
|
||
|
|
use common ;
|
||
|
|
use common_min ;
|
||
|
|
use report ;
|
||
|
|
use xlsxcreator ;
|
||
|
|
|
||
|
|
1;
|