#!/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;