#!/usr/bin/perl
#  Copyright 2001-2024 Leslie Richardson

#  This file is part of Open Admin for Schools.

#  Open Admin for Schools is free software; you can redistribute it 
#  and/or modify it under the terms of the GNU General Public License
#  as published by the Free Software Foundation; either version 2 of 
#  the License, or (at your option) any later version.

# A script to export the transactions into an accounting system in
# summary format (Cash, Revenue, and Taxes only) for one month.


my %lex = ('Export Summary Transactions' => 'Export Summary Transactions',
	   'Main' => 'Main',
	   'Fees' => 'Fees',
	   'Continue' => 'Continue',
	   'Month to Export (yyyy-mm)' => 'Month to Export (yyyy-mm)',
	   'Error in Date Format' => 'Error in Date Format',
	   'Month' => 'Month',
	   'Year' => 'Year',
	   'No Transaction(s) Found' => 'No Transaction(s) Found',
	   'Download CSV File' => 'Download CSV File',
	   'Error' => 'Error',

	   );

my $self = 'exportmonthjrl.pl';

use CGI;
use DBI;
use Text::CSV_XS;
use Cwd;

# calc current date
my @tim = localtime(time);
my $year = @tim[5] + 1900;
my $month = @tim[4] + 1;
my $day = @tim[3];
my $currdate = "$year-$month-$day";

if ( $day < 15 ) { # assume previous month to export...
    if ( $month == 1 ) {
	$month = 12;
	$year--;
    } else {
	$month--;
    }
}

my $exportMonth = "$year\-$month";

eval require "../../etc/admin.conf";
if ( $@ ) {
    print $lex{Error}. ": $@<br>\n";
    die $lex{Error}. ": $@\n";
}

eval require "../../etc/fees.conf";
if ( $@ ) {
    print $lex{Error}. ": $@<br>\n";
    die $lex{Error}. ": $@\n";
}

my $q = new CGI;
print $q->header( -charset, $charset ); 
my %arr = $q->Vars;

my $checked = $arr{checked};
delete $arr{checked};

# Debit/Credit Sign Convention.
my ($creditVal, $debitVal);
if ( $debitSign eq '+' ) { # from fees.conf
    $debitVal = '';
    $creditVal = '-';
} else {
    $debitVal = '-';
    $creditVal = '';
}



my $dsn = "DBI:$dbtype:dbname=$dbase";
my $dbh = DBI->connect($dsn,$user,$password);


# Get current dir so know what CSS to display;
#if (getcwd() =~ /tcgi/){ # we are in tcgi
#    $css = $tchcss;
#}

# Show page Header
my $title = $lex{'Export Summary Transactions'};

print qq{$doctype\n<html><head><title>$title</title>\n};
print qq{<link rel="stylesheet" href="$css" type="text/css">\n};
print qq{$chartype\n</head><body>[ <a href="$homepage">$lex{Main}</a> |\n};
print qq{<a href="$feespage">$lex{Fees}</a> ]\n};

print qq{<h1>$title</h1>\n};

if ( not $arr{page} ) {
    showStartPage();
} else {
    getMonthlyData( $arr{date} );
}




#----------------
sub showStartPage {
#----------------


    # Setup the form and start of table.
    print qq{<form action="$self" method="post">\n};
    print qq{<input type="hidden" name="page" value="1">\n};
    print qq{<table cellpadding="3" border="0" cellspacing="0">\n};
    

    print qq{<tr><td align="right">$lex{'Month to Export (yyyy-mm)'}</td><td align="left">\n};
    print qq{<input type="text" name="date" size="12" value="$exportMonth" maxlength="7">\n};
    print qq{</td></tr>\n};

    print qq{<tr><td></td><td>\n};
    print qq{<input type="submit"  value="$lex{Continue}">};
    print qq{</td></tr>\n};

    print qq{</table>};
    print qq{</form>\n};

    print qq{</body></html>\n};

    exit;

}


#----------------
sub getMonthlyData {
#----------------

    my $date = shift;
    my ($year, $month) = split /-/, $date;
    if (not $year or not $month) {
	print qq{<h1>$lex{'Error in Date Format'}</h1>\n};
	print qq{<h1>$lex{Year}: $year - $lex{Month}: $month</h1>\n};
	print qq{</body></html>\n};
	exit;
    }

    # End of the Month dates.
    my %eom = (1 => 31, 2 => 28, 3 => 31, 4 => 30, 5 => 31, 6 => 30,
	       7 => 31, 8 => 31, 9 => 30, 10 => 31, 11 => 30, 12 => 31);
    if ( $year % 4 == 0 ) { $eom{2} = 29; }

    my $exportDate = "$year-$month-$eom{$month}";

    # Find transactions in selected month, year
    my $fieldlist = "trans_type, subtotal, tax1, tax1_name, tax2," .
	" tax2_name, tax3, tax3_name, tax4, tax4_name, total, paid_id";

    my $sth = $dbh->prepare("select $fieldlist from fees_jrl
      where extract(MONTH FROM trans_date) = ? and extract(YEAR FROM trans_date) = ?");
    $sth->execute( $month, $year );
    if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; }
    $rows = $sth->rows;

    if ($rows < 1){ # No outstanding transactions found
	print qq{<h1>$lex{'No Transaction(s) Found'}!</h1>\n};
	print qq{[ <a href="$homepage">$lex{Main}</a> ]\n}; 
	print qq{</body></html>\n};
	exit;
    }

    my ( $totalRev, $totalAR, $totalCash, %taxes ); # for charge transactions.
    my ( $totalPayCash, $totalRoaCash); # for pay and roa transactions

    #while ( my ( $trans_type, $subtotal, $tax1, $tax1_name, $tax2,
    #$tax2_name, $tax3, $tax3_name, $tax4, $tax4_name, $total, $paid_id
    #) = $sth->fetchrow ) {

    while ( my @recs = $sth->fetchrow ) {
	my $trans_type = $recs[0];
	my $subtotal = $recs[1];
	my $total = $recs[10];
	my $paid_id = $recs[11];


	if ( $trans_type eq 'chg' ) { # A charge transaction...
	    # Example A/R: $107 Debit; Tax1(Liability): $7 Credit; Revenue: $100 Credit;
	    # A/R is Total field; Taxes are 4 tax fields; Revenue is Subtotal field.
	    #  Don't care about the paid_id (ie. paid or not); covered in pay transactions.

	    $totalAR += $total;
	    $totalRev += $subtotal;

	    # Now do taxes...
	    for my $idx (1..4) {
		$taxfield = $idx * 2;
		$taxname =  $taxfield + 1;

		if ( $recs[$taxfield] ) { # if we have a a value there...
		    if ( defined $taxes{ $recs[$taxname] } ) {
			$taxes{ $recs[$taxname] } += $recs[$taxfield];
		    } else { #  a value
			$taxes{ $recs[$taxname] } = $recs[$taxfield];
		    }
		} # end of tax value checking.

	    } # end of taxes loop.

	} elsif ( $trans_type eq 'pay' ) {
	    $totalPayCash += $total;
	} else { # roa transaction.
	    $totalRoaCash += $total;
	}

    } # end of transaction reading...
		

    # Test Output...
    #print qq{A/R: $totalAR  Revenue: $totalRev<br>\n};
    #foreach my $key ( keys %taxes ) {
	#print qq{K: $key V: $taxes{$key}<br>\n};
    #}
    #print qq{Payments/ROA- Pay:$totalPayCash Roa:$totalRoaCash<br>\n};
    
    # Strip any signs...
    $totalPayCash =~ s/^-//;
    $totalRoaCash =~ s/^-//;


    # Start Export
    $csv = Text::CSV_XS->new({binary => 1});

    # Open output file
    my $filename = "csvexportjrl$$.csv";
    open (EX,">$filename") || die "Can't open Export file $filename";

    # Write CSV File
    # First the charge Transactions...
    # Date, Account, Amount
    my @rec;
    push @rec, $exportDate;
    push @rec, 'Accounts Receivable';
    push @rec, $debitVal. $totalAR;

    writeCSV( @rec);

    @rec = ();
    push @rec, $exportDate;
    push @rec, 'Revenue';
    push @rec, $creditVal. $totalRev;

    writeCSV( @rec);

    foreach my $key (sort keys %taxes ) {
	my @rec;
	push @rec, $exportDate;
	push @rec, $key;
	push @rec, $creditVal. $taxes{$key};

	writeCSV( @rec );

    }


    # Now do the payment transactions.
    if ( $totalPayCash ) {
	@rec = ();
	push @rec, $exportDate;
	push @rec, 'Cash';
	push @rec, $debitVal. $totalPayCash;
	writeCSV( @rec);

	@rec = ();
	push @rec, $exportDate;
	push @rec, 'Accounts Receivable';
	push @rec, $creditVal. $totalPayCash;
	writeCSV( @rec);
    
    }


    # Now do the roa transactions
    if ( $totalRoaCash ) {
	@rec = ();
	push @rec, $exportDate;
	push @rec, 'Cash';
	push @rec, $debitVal. $totalRoaCash;
	writeCSV( @rec);

	@rec = ();
	push @rec, $exportDate;
	push @rec, 'Accounts Receivable';
	push @rec, $creditVal. $totalRoaCash;
	writeCSV( @rec);

    }

    # Close and Link to Exported File
    close EX;
    system("mv $filename $downloaddir");

    print qq{<p>[ <a href="$webdownloaddir/$filename">$lex{'Download CSV File'}</a> ]</p>\n};
    print qq{</body></html>\n};

    exit;


}


#-----------
sub writeCSV {
#-----------

    my @rec = @_;

    if ( $csv->combine(@rec) ) {
	my $record = $csv->string;
	print EX $record, "\r\n";
    } else {
	my $err = $csv->error_input;
	print qq{Combine failed on input: ",$err,":\n\n};
    }


}
