<?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_SALESANALYTIC';
// ----------------------------------------------------------------
// $ Revision:	2.0 $
// Creator:	Stefan Sotirov, modified slightly by Joe Hunt.
// date_:	01-12-2017
// Title:	Inventory Purchasing - Transaction Based
// ----------------------------------------------------------------
$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 . "/includes/banking.inc");
include_once($path_to_root . "/gl/includes/gl_db.inc");
include_once($path_to_root . "/inventory/includes/db/items_category_db.inc");

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

print_inventory_purchase();

function getTransactions($category, $location, $fromcust, $item, $from, $to) {
    $from = date2sql($from);
    $to = date2sql($to);
    $sql = "SELECT item.category_id,category.description AS cat_description,
			item.stock_id,item.description, item.inactive,
			d.debtor_no,d.name AS customer_name,
			dt.debtor_trans_no as trans_no,trans.tran_date,dt.debtor_trans_type as type,
			IF(dt.debtor_trans_type=".ST_CUSTCREDIT.",dt.quantity * -1,f.qty_cleared) AS qty,
                        IF(dt.debtor_trans_type=".ST_CUSTCREDIT.",dt.standard_cost,f.cost) AS cost,
                        f.from_id,f.from_type,f.from_trans_no,
                        IF(trans.tax_included,dt.unit_price - dt.unit_tax,dt.unit_price) as price,
			dt.unit_price,dt.unit_tax,trans.tax_included
		FROM " . TB_PREF . "debtor_trans_details dt
                    LEFT JOIN " . TB_PREF . "fifo_allocs f on f.type=dt.debtor_trans_type AND f.trans_no=dt.debtor_trans_no AND f.detail_id=dt.id 
                    LEFT JOIN " . TB_PREF . "debtor_trans trans ON dt.debtor_trans_type=trans.type AND dt.debtor_trans_no=trans.trans_no
                    lEFT JOIN " . TB_PREF . "debtors_master d ON d.debtor_no=trans.debtor_no
                    LEFT JOIN " . TB_PREF . "stock_master item ON item.stock_id=dt.stock_id
                    LEFT JOIN " . TB_PREF . "stock_category category ON item.category_id=category.category_id
		WHERE (f.qty_cleared<>0 || dt.debtor_trans_type=".ST_CUSTCREDIT.") AND dt.quantity<>0 AND trans.tran_date>='$from'
		AND trans.tran_date<='$to'
		AND (dt.debtor_trans_type=" . ST_CUSTDELIVERY . " OR dt.debtor_trans_type=" . ST_CUSTCREDIT . ")";
    if ($category != 0)
        $sql .= " AND item.category_id = " . db_escape($category);
    
    if ($fromcust != '')
        $sql .= " AND d.debtor_no = " . db_escape($fromcust);
    if ($item != '')
        $sql .= " AND item.stock_id = " . db_escape($item);
    $sql .= " ORDER BY trans.tran_date, dt.debtor_trans_no, item.category_id, item.stock_id";
    return db_query($sql, "No transactions were returned");
}

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

function print_inventory_purchase() {
    global $path_to_root,$systypes_array;

    $from = $_POST['PARAM_0'];
    $to = $_POST['PARAM_1'];
    $category = $_POST['PARAM_2'];
    $location = $_POST['PARAM_3'];
    $fromcust = $_POST['PARAM_4'];
    $item = $_POST['PARAM_5'];
    $comments = $_POST['PARAM_6'];
    $orientation = $_POST['PARAM_7'];
    $destination = $_POST['PARAM_8'];
    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');
    $dec = user_price_dec();

    if ($category == ALL_NUMERIC)
        $category = 0;
    if ($category == 0)
        $cat = _('All');
    else
        $cat = get_category_name($category);

    if ($location == '')
        $loc = _('All');
    else
        $loc = get_location_name($location);

    if ($fromcust == '')
        $fromc = _('All');
    else
        $fromc = get_customer_name($fromcust);

    if ($item == '')
        $itm = _('All');
    else
        $itm = $item;

    $cols = array(0, 60, 180, 230, 275, 325, 450, 470, 530, 590, 650, 710, 770);

    $headers = array(_('Type'), _('Item'), _('Date'), _('#'), _("Cost #"), _('Customer'), _('Qty'), _('Unit Price'), _('Sale Amt'), _("Unit Cost"), _("Cost Amt"), _("Margin"));
    if ($fromcust != '')
        $headers[4] = '';

    $aligns = array('left', 'left', 'left', 'left', 'left', 'left', 'left', 'right', 'right', 'right', 'right', 'right');

    $params = array(0 => $comments,
        1 => array('text' => _('Period'), 'from' => $from, 'to' => $to),
        2 => array('text' => _('Category'), 'from' => $cat, 'to' => ''),
        3 => array('text' => _('Location'), 'from' => $loc, 'to' => ''),
        4 => array('text' => _('Customer'), 'from' => $fromc, 'to' => ''),
        5 => array('text' => _('Item'), 'from' => $itm, 'to' => ''),
        6 => array('text' => _('Note'), 'from' => _('The lines separate the transactions.'),
            'to' => ''));

    $rep = new FrontReport(_('Cost Of Sales Report'), "CostOfSalesReport", user_pagesize(), 9, $orientation);
    if ($orientation == 'L')
        recalculate_cols($cols);

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

    $res = getTransactions($category, $location, $fromcust, $item, $from, $to);

    $totalamt = $totalcost = $totalmargin = 0.0;
    $event = '';
    while ($trans = db_fetch($res)) {
        if ($event != $trans['trans_no']) {
            if ($event != '') {
                $rep->NewLine();
                $rep->Line($rep->row - 2);
            }
            $event = $trans['trans_no'];
        }
        $curr = get_customer_currency($trans['debtor_no']);
        $rate = get_exchange_rate_from_home_currency($curr, sql2date($trans['tran_date']));
        $trans['price'] *= $rate;

        $rep->TextCol(0, 1, $systypes_array[$trans['type']]);
        $rep->TextCol(1, 2, $trans['stock_id'] . " " . $trans['description'] . ($trans['inactive'] == 1 ? " (" . _("Inactive") . ")" : ""), -1);
        $rep->TextCol(2, 3, sql2date($trans['tran_date']));
        $rep->TextCol(3, 4, $trans['trans_no']);
        $rep->TextCol(4, 5, isset($trans['from_type']) ? $systypes_array[$trans['from_type']]." #: ".$trans['from_trans_no'] : "");
        if ($fromcust == ALL_TEXT) {
            $rep->TextCol(5, 6, $trans['customer_name']);
        }
        $rep->AmountCol(6, 7, $trans['qty'], get_qty_dec($trans['stock_id']));
        $rep->AmountCol(7, 8, $trans['price'], $dec);
        $amt = $trans['qty'] * $trans['price'];
        $costamt = $trans['cost'] * $trans['qty'];
        $margin = $amt - $costamt;
        $rep->AmountCol(8, 9, $amt, $dec);
        $rep->AmountCol(9, 10, $trans['cost'], $dec);
        $rep->AmountCol(10, 11, $costamt, $dec);
        $rep->AmountCol(11, 12, $margin, $dec);
        $rep->NewLine();

        $totalamt += $amt;
        $totalcost += $costamt;
        $totalmargin += $margin;
    }

    $rep->NewLine();
    $rep->AmountCol(8, 9, $totalamt, $dec);
    $rep->AmountCol(10, 11, $totalcost, $dec);
    $rep->AmountCol(11, 12, $totalmargin, $dec);
    $rep->Line($rep->row - 4);
    $rep->NewLine();
    $rep->End();
}
