<?php

/* * ********************************************************************
  Copyright (C) FrontAccounting, LLC.
  Released under the terms of the GNU General Public License, GPL,
  as published by the Free Software Foundation, either version 3
  of the License, or (at your option) any later version.
  This program is distributed in the hope that it will be useful,
  but WITHOUT ANY WARRANTY; without even the implied warranty of
  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
  See the License here <http://www.gnu.org/licenses/gpl-3.0.html>.
 * ********************************************************************* */
$page_security = 'SA_CUSTPAYMREP';
// ----------------------------------------------------------------
// $ Revision:	2.0 $
// Creator:	Joe Hunt
// date_:	2005-05-19
// Title:	Aged Customer Balances
// ----------------------------------------------------------------
$path_to_root = "..";

include_once($path_to_root . "/includes/session.inc");
include_once($path_to_root . "/includes/date_functions.inc");
include_once($path_to_root . "/includes/data_checks.inc");
include_once($path_to_root . "/gl/includes/gl_db.inc");

//----------------------------------------------------------------------------------------------------

print_aged_customer_analysis();

function get_invoices($customer_id,$start, $to) {
    $fromdate = date2sql($start);
    $todate = date2sql($to);
    $PastDueDays1 = get_company_pref('past_due_days');
    $PastDueDays2 = 2 * $PastDueDays1;

    $value = "(ov_amount + ov_gst + ov_freight + ov_freight_tax )";
    $sign = "IF(`type` IN(" . implode(',', array(ST_CUSTCREDIT, ST_CUSTPAYMENT, ST_BANKDEPOSIT, ST_JOURNAL,ST_CUSTWHTAX,ST_CUSTDISCOUNT)) . "), 1, -1)";
    $due = "IF (type=" . ST_SALESINVOICE . ", due_date, tran_date)";

    $cust_filter = '';
    if ($customer_id != ALL_TEXT)
            $cust_filter = " AND debtor_no=".db_escape($customer_id) ." ";
    
    $sql = "SELECT type, reference, tran_date,debtor_no,
		$sign*$value as amount,
		IF ((TO_DAYS('$todate') - TO_DAYS($due)) > 0,$sign*$value,0) AS Due,
		IF ((TO_DAYS('$todate') - TO_DAYS($due)) > $PastDueDays1,$sign*$value,0) AS Overdue1,
		IF ((TO_DAYS('$todate') - TO_DAYS($due)) > $PastDueDays2,$sign*$value,0) AS Overdue2

		FROM " . TB_PREF . "debtor_trans trans

		WHERE type in (" . ST_CUSTPAYMENT . "," . ST_BANKDEPOSIT . "," . ST_BANKDEPOSIT . "," . ST_JOURNAL . "," . ST_BANKPAYMENT . "," . ST_CUSTWHTAX . "," . ST_CUSTDISCOUNT . ")
			$cust_filter
			AND tran_date between '$fromdate' and '$todate'
			AND ABS(ov_amount + ov_gst + ov_freight + ov_freight_tax ) > " . FLOAT_COMP_DELTA;
    
    $sql .= "ORDER BY tran_date";

    return db_query($sql, "The customer transactions could not be retrieved");
}

//----------------------------------------------------------------------------------------------------

function print_aged_customer_analysis() {
    global $path_to_root, $systypes_array, $SysPrefs;

    $start = $_POST['PARAM_0'];
    $to = $_POST['PARAM_1'];
    $fromcust = $_POST['PARAM_3'];
    $currency = $_POST['PARAM_2'];
    $orientation = $_POST['PARAM_4'];
    $destination = $_POST['PARAM_5'];
    if ($destination)
        include_once($path_to_root . "/reporting/includes/excel_report.inc");
    else
        include_once($path_to_root . "/reporting/includes/pdf_report.inc");
    $orientation = ($orientation ? 'L' : 'P');


    if ($fromcust == ALL_TEXT)
        $from = _('All');
    else
        $from = get_customer_name($fromcust);
    $dec = user_price_dec();


    if ($currency == ALL_TEXT) {
        $convert = true;
        $currency = _('Balances in Home Currency');
    } else
        $convert = false;
    
    $PastDueDays1 = get_company_pref('past_due_days');
    $PastDueDays2 = 2 * $PastDueDays1;
    $nowdue = "1-" . $PastDueDays1 . " " . _('Days');
    $pastdue1 = $PastDueDays1 + 1 . "-" . $PastDueDays2 . " " . _('Days');
    $pastdue2 = _('Over') . " " . $PastDueDays2 . " " . _('Days');
    
    

    $cols = array(0, 100, 150, 200, 400, 450, 550);
    $headers = array(_('Type'),_('Ref'),_('Date'),_('Customer'),_('Currency'),_('Amount'));

    $aligns = array('left', 'left', 'left', 'left', 'left', 'right');
    $params = array(
        0 => '',
        1 => array('text' => _('Date'), 'from' => $to, 'to' => $to),
        2 => array('text' => _('Customer'), 'from' => $from, 'to' => ''),
        3 => array('text' => _('Currency'), 'from' => $currency, 'to' => ''));

    
    $rep = new FrontReport(_('Sales By Dates Report'), "salesbydates", user_pagesize(), 9, $orientation);
    if ($orientation == 'L')
        recalculate_cols($cols);

    $rep->Font();
    $rep->Info($params, $cols, $headers, $aligns);
    $rep->NewPage();

    $res = get_invoices($fromcust, $start,$to);

    $total = 0;
    while ($trans = db_fetch($res)) {
        $sql = "SELECT debtor_no, name, curr_code FROM " . TB_PREF . "debtors_master";
        $sql .= " WHERE debtor_no=" . db_escape($trans['debtor_no']);
        $result = db_query($sql, "The customers could not be retrieved");
        $myrow = db_fetch($result);
        if (!$convert && $currency != $myrow['curr_code'])
            continue;
        $rep->NewLine(1, 2);
        $rep->TextCol(0, 1, $systypes_array[$trans['type']], -2);
        $rep->TextCol(1, 2, $trans['reference'], -2);
        $rep->DateCol(2, 3, $trans['tran_date'], true, -2);
        $rep->TextCol(3, 4, $myrow['name'], -2);
        $rep->TextCol(4, 5, $myrow['curr_code'], -2);

        if ($trans['type'] == ST_CUSTCREDIT ) {
            //$trans['amount'] *= -1;
        }

        $rep->AmountCol(5, 6, $trans['amount'], $dec);
        $total += $trans['amount'];
    }
    $rep->Line($rep->row - 8);
    $rep->NewLine(2);

    $rep->fontSize += 2;
    $rep->TextCol(0, 1, _('Grand Total'));
    $rep->fontSize -= 2;
    $rep->AmountCol(5, 2, $total, $dec);

    $rep->Line($rep->row - 8);

    $rep->NewLine();
    $rep->End();
}
