<?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_REP_FILESYSTEM';
// ----------------------------------------------------------------
// $ Revision:	2.0 $
// Creator:	Joe Hunt
// date_:	2005-05-19
// Title:	Sales Summary Report
// ----------------------------------------------------------------
$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");
// include_once($path_to_root . "/dimensions/includes/project_timeline_alloc_db.inc");

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

print_sales_summary_report();

function getTransactions($from, $to, $file_system){

    $sql = "SELECT  IFNULL(a.gl_seq,0) as gl_seq,
        gl.tran_date,
        gl.type as trans_type,
        gl.type_no as trans_no,
        bt.bank_act as bank_account,
        fs.id as file_system,
        IF(MAX(gl.person_id), MAX(gl.person_id), IFNULL(st.supplier_id, IFNULL(grn.supplier_id, IFNULL(dt.debtor_no, bt.person_id)))) as person_id,
        IF(ISNULL(st.supp_reference), '', st.supp_reference) AS supp_reference,
        refs.reference,
          amount_calc.amount as amount,
        com.memo_,
        IF(ISNULL(u.user_id),'',u.user_id) as user_id,
        IF(MAX(gl.person_id), MAX(gl.person_type_id), IF(!ISNULL(st.supplier_id) OR !ISNULL(grn.supplier_id),".  PT_SUPPLIER . "," .  "IF(dt.debtor_no," . PT_CUSTOMER . "," .
        "IF(bt.person_id != '' AND !ISNULL(bt.person_id), bt.person_type_id, -1)))) as person_type_id
        FROM ".TB_PREF."gl_trans as gl
        LEFT JOIN 
        (SELECT type, type_no, 
            IF(type=".ST_BANKTRANSFER.", MAX(amount), SUM(IF(amount>0, amount, 0))) as amount 
	     FROM 0_gl_trans 
	     WHERE tran_date >= '" . date2sql($from) . "' AND tran_date <= '" . date2sql($to) . "'
	       AND amount != 0 
	     GROUP BY type, type_no) as amount_calc ON gl.type = amount_calc.type AND gl.type_no = amount_calc.type_no 
	       JOIN ".TB_PREF."file_system fs
         LEFT JOIN ".TB_PREF."audit_trail as a ON
            (gl.type=a.type AND gl.type_no=a.trans_no)
         LEFT JOIN ".TB_PREF."comments as com ON
            (gl.type=com.type AND gl.type_no=com.id)
         LEFT JOIN ".TB_PREF."refs as refs ON
            (gl.type=refs.type AND gl.type_no=refs.id)
         LEFT JOIN ".TB_PREF."users as u ON
            a.user=u.id
         LEFT JOIN ".TB_PREF."grn_batch grn ON grn.id=gl.type_no AND gl.type=".ST_SUPPRECEIVE." AND fs.id = grn.file_system 
         LEFT JOIN ".TB_PREF."bank_trans bt ON bt.type=gl.type AND bt.trans_no=gl.type_no AND bt.amount!=0
                 AND (bt.person_id != '' AND !ISNULL(bt.person_id)) AND fs.id = bt.file_system
         LEFT JOIN ".TB_PREF."debtor_trans dt ON dt.type=gl.type AND gl.type_no=dt.trans_no AND gl.person_id=dt.debtor_no AND gl.person_type_id=2 AND fs.id = dt.file_system
         LEFT JOIN ".TB_PREF."supp_trans st ON st.type=gl.type AND gl.type_no=st.trans_no AND gl.person_id=st.supplier_id AND gl.person_type_id=3 AND  fs.id = st.file_system
     	  
         WHERE gl.tran_date >= '" . date2sql($from) . "'
        AND gl.tran_date <= '" . date2sql($to) . "'
        AND gl.amount!=0 AND gl.type=10 OR gl.type=20 OR gl.type=1";

        // JOIN ".TB_PREF."file_system fs
		  //   ON fs.id = grn.file_system 
		  //   OR fs.id = bt.file_system
		  //   OR fs.id = dt.file_system 
		  //   OR fs.id = st.file_system

     if ($file_system != '') {
        $sql .= " AND fs.id=".db_escape($file_system);
   	 }
    $sql .= " GROUP BY gl.tran_date, a.gl_seq, gl.type, gl.type_no";
    // display_error($sql);
    return db_query($sql);
}

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

function print_sales_summary_report()
{
	global $path_to_root;
    $form = $_POST['PARAM_0'];
    $to = $_POST['PARAM_1'];
    $file_system = $_POST['PARAM_2'];
    $comments = $_POST['PARAM_3'];
	$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');

    $dec = user_price_dec();
	
    if ($file_system == '')
		$file_system_ = _('All File System');
	else
		$file_system_ = get_file_system_name($file_system);

	$headers = array(_('Date'), _('Type'), _('Trans #'), _('Reference'), _('File System'), _('Counterparty'), _('Amount'), _('Memo'));
	$cols = array(4, 50, 140, 190, 250, 320, 440, 510);

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

	$params =   array( 	
				0 => $comments,
                1 => array('text' => _('File System'), 'from' => $file_system_, 'to' => ''));

  	$rep = new FrontReport(_('File System Report'), "FileSystem", user_pagesize(), 9, $orientation);
      if ($orientation == 'L')
    	recalculate_cols($cols);

	$rep->Font();
	$rep->Info($params, $cols, $headers, $aligns);
	$rep->NewPage();
	$transactions = getTransactions($form, $to, $file_system);

	$purch_amt=0;
	$sales_amt=0;
	
	while ($trans=db_fetch($transactions))
	{	
		global $systypes_array;
	
				$rep->TextCol(0, 1, sql2date($trans['tran_date']));
				$rep->TextCol(1, 2, $systypes_array[$trans['trans_type']]);
				$rep->TextCol(2, 3, $trans['trans_no']);
				$rep->TextCol(3, 4, $trans['reference']);
				$rep->TextCol(4, 5, get_file_system_name($trans['file_system']));
				$oldrow = $rep->row;
				$rep->TextColLines(5, 6, payment_person_name($trans["person_type_id"],$trans["person_id"]));
				$newrow = $rep->row;
				$rep->row = $oldrow;
				$rep->AmountCol(6, 7, $trans['amount'],$dec);
				$rep->TextCol(7, 8, $trans['memo_'] ? $trans['memo_'] : '-');
				$rep->row = $newrow;

				if($trans['trans_type']==ST_SALESINVOICE)
					$sales_amt+=$trans['amount'];
				else
					$purch_amt+=$trans['amount'];

				// $rep->NewLine();
				if ($rep->row < $rep->bottomMargin + $rep->lineHeight)
				{
					$rep->Line($rep->row - 2);
					$rep->NewPage();
				}
	}

	$rep->Font('bold');
	$rep->NewLine();
	$rep->Line($rep->row + $rep->lineHeight);
	// $rep->aligns[0]='left'; 
	$rep->TextCol(0, 2,	_("Total Sales Amount:"));
	$rep->TextCol(2, 3, number_format2($sales_amt));
	$rep->NewLine();
	$rep->TextCol(0, 2,	_("Total Purchase Amount:"));
	$rep->TextCol(2, 3, number_format2($purch_amt));
	$rep->NewLine();
	$rep->TextCol(0, 2,	_("Net Balance Amount:"));
	$rep->TextCol(2, 3, number_format2($sales_amt-$purch_amt));
	// $rep->Font();
	$rep->End();
}