<?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_STOCK_LEDGER_REPORT';
// ----------------------------------------------------------------
// $ 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");

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


print_sales_summary_report();

// function getTaxTransactions($from, $to, $stock_id, $order_num, $batch_no , $item_group){
// 	$fromdate = date2sql($from);
// 	$todate = date2sql($to);
// 	$sql = " SELECT wo.*, sm.*, stm.* FROM ".TB_PREF."workorders as wo, ".TB_PREF."stock_moves as sm, ".TB_PREF."stock_master as stm, ".TB_PREF."wo_manufacture as wom WHERE wom.workorder_id=wo.id AND wom.id=sm.trans_no AND sm.type=29  AND stm.stock_id=sm.stock_id";
// 			 $sql .= " AND wo.date_ >=".db_escape($fromdate)." AND wo.date_<=".db_escape($todate);
// 			 if ($stock_id != '')
// 			 $sql .= " AND sm.stock_id =".db_escape($stock_id);
// 			if ($order_num != '')
// 			 $sql .= " AND wo.id =".db_escape($order_num);
// 			if ($batch_no != '')
// 			 $sql .= " AND sm.batch_no =".db_escape($batch_no);
// 			if ($item_group != '')
// 			 $sql .= " AND stm.item_group_id =".db_escape($item_group);
// 			 // display_error($sql);
// 			 $result = db_query($sql);
// 			 return $result;
// }
// new 
function get_stock_movements_report($from, $to, $location = '', $stock_id='', $transection_type)
{
	// $fromdate = date2sql($from);
	// $todate = date2sql($to);

	// $before_date = date2sql($BeforeDate);
	// $after_date = date2sql($AfterDate);
	// if($exp_date == null)
	// 	$exp_date = Today();
	// $exp_date = date2sql($exp_date);

	$from = date2sql($from);
	$to = date2sql($to);

	
	
  	$sql = "SELECT move.*, IF(ISNULL(supplier.supplier_id), debtor.name, supplier.supp_name) name";

	// if(!$StockLocation) {
	// 	 $sql .= ", move.loc_code";
	// }
  	$sql.=    " FROM ".TB_PREF."stock_moves move
				LEFT JOIN ".TB_PREF."supp_trans credit ON credit.trans_no=move.trans_no AND credit.type=move.type
				LEFT JOIN ".TB_PREF."grn_batch grn ON grn.id=move.trans_no AND 25=move.type
				LEFT JOIN ".TB_PREF."suppliers supplier ON IFNULL(grn.supplier_id, credit.supplier_id)=supplier.supplier_id
				LEFT JOIN ".TB_PREF."debtor_trans cust_trans ON cust_trans.trans_no=move.trans_no AND cust_trans.type=move.type
				LEFT JOIN ".TB_PREF."debtors_master debtor ON cust_trans.debtor_no=debtor.debtor_no
		WHERE  stock_id!=''  	AND move.tran_date>='$from'
		AND move.tran_date<='$to' ";

  	if ($location !='') {
    	$sql.= " AND move.loc_code=".db_escape($location);
	}else{
			if ($_SESSION["wa_current_user"]->to_location!='')
		 $sql.=" AND  move.loc_code IN (".$_SESSION["wa_current_user"]->to_location.") ";
	}
	// new
	// $sql .= " AND move.tran_date >=".db_escape($fromdate)." AND move.tran_date<=".db_escape($todate);


	// if($batch_no)
	// 	$sql .= " move.batch_no LIKE " . db_escape("%" . $batch_no. "%") . " AND";

	// if($filterType){
	// 	if($filterType == 1)
	// 		$sql .=" move.exp_date >= ". db_escape($exp_date). "	AND ";
	// 	elseif($filterType == 2)
	// 		$sql .=" move.exp_date <= ". db_escape($exp_date) . "	AND ";
	// 	elseif($filterType == 3)
	// 		$sql .=" move.exp_date <> ". db_escape($exp_date) . "	AND ";
	// }
		 if ($stock_id != '')
			 $sql .= " AND move.stock_id =".db_escape($stock_id);

			 if ($transection_type != -1)
			 $sql .= " AND move.type =".db_escape($transection_type);
	
  $sql .=" ORDER BY  move.tran_date";
	// display_error($sql);
  	return db_query($sql, "could not query stock moves");
}

function get_items_name($stock_id)
{
	$sql = "SELECT description FROM ".TB_PREF."stock_master WHERE stock_id=".db_escape($stock_id);
	// display_error($sql);
	$result = db_query($sql);
	$row = db_fetch_row($result);
	return is_array($row) ? $row[0] : false;
}

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

function print_sales_summary_report()
{
	global $path_to_root;
	
		$from = $_POST['PARAM_0'];
		$to = $_POST['PARAM_1'];
		$location = $_POST['PARAM_2'];
		$stock_id = $_POST['PARAM_3'];
		$transection_type = $_POST['PARAM_4'];
		$orientation = $_POST['PARAM_5'];
		$destination = $_POST['PARAM_6'];
	// $from = $_POST['PARAM_0'];
	// $to = $_POST['PARAM_1'];
	// $batch_no = $_POST['PARAM_4'];
	// $item_group = $_POST['PARAM_5'];

		if(	!$stock_id ){

			display_error(_("Item must be select for check stock ledger."));
			exit();
		}

	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 ($location == '')
		$loc = _('All');
	else
		$loc = get_location_name($location);


	
	if ($transection_type == -1) 
		$type_new = _('All');
	else
		global $systypes_array;
		$type_new = $systypes_array[$transection_type];

	

	$rep = new FrontReport(_('Stock Ledger Report'), "SalesReport", user_pagesize(), 9, $orientation);

	$params =   array( 	0 => $comments,
	                 	1 => array('text' => _('Period'),'from' => $from, 'to' => $to),
    				    2 => array('text' => _('Location'), 'from' => $loc, 'to' => ''),
						3 => array( 'text' => _('Item Stock'),'from' => $stock_id,'to' => ''),
						4 => array( 'text' => _('Transection Type'),'from' => $type_new,'to' => ''),
						// 2 => array( 'text' => _('Customer Id'),'from' => $cust,'to' => ''),
						// 4 => array( 'text' => _('Sales Person'),'from' => $sale_Prsn,'to' => ''),
						// 6 => array( 'text' => _('Items Group'),'from' => $item_group,'to' => ''),
						// 7 => array( 'text' => _('Sales Type'),'from' => $type,'to' => '')
					);
						

	$cols = array(0, 70, 170, 250, 390, 450, 510); //, 680

	$headers = array(_('Date'),_('Type'), _('Ref NO'), _('Description'), _('Stock In'), _('Stock Out'), _('Balance'));
	$aligns = array('left', 'left', 'left', 'left', 'left', 'left', 'left', 'right'); //, 'left'
    if ($orientation == 'L')
    	recalculate_cols($cols);

	$rep->Font();
	$rep->Info($params, $cols, $headers, $aligns);
	$rep->NewPage();
	
	$totalnet = 0.0;
	$transactions = get_stock_movements_report($from, $to, $location ,$stock_id, $transection_type);

	$rep->NewLine(2);

	$total_in = 0;
	$total_out = 0;
	while ($trans=db_fetch($transactions))
	{
				$rep->TextCol(0, 1, $trans['tran_date']);
				global $systypes_array;
				$type_name = $systypes_array[$trans["type"]];
				$rep->TextCol(1, 2, $type_name); //. $trans["batch_no"]
			
				$rep->TextCol(2, 3, $trans['reference']);
				$oldrow = $rep->row;
				$rep->TextColLines(3, 4, $trans['stock_id']." - ".get_items_name($trans['stock_id']), -2);
				$newrow = $rep->row;
				$rep->row = $oldrow;

				if ($trans["qty"] > 0)
				{
					$quantity_formatted = number_format2($trans["qty"], $dec);
					$total_in += $trans["qty"];
				}
				else
				{
					$quantity_formatted = number_format2(-$trans["qty"], $dec);
					$total_out += -$trans["qty"];
				}
				$after_qty += $trans["qty"];

				label_cell((($trans["qty"] >= 0) ? $quantity_formatted : ""), "nowrap align=right");
				label_cell((($trans["qty"] < 0) ? $quantity_formatted : ""), "nowrap align=right");
				qty_cell($after_qty, false, $dec);
				$rep->TextCol(4, 5,	(($trans["qty"] >= 0) ? $quantity_formatted : "-"));
				$rep->TextCol(5, 6,	(($trans["qty"] < 0) ? $quantity_formatted : "-"));
				$rep->TextCol(6, 7, $after_qty);
				$rep->row = $newrow;

				
//-------------------------------------------------------------------------------------------------------------------------------------------
				
				// $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->TextCol(0, 2,	_("Total"));
	$rep->AmountCol(4, 5, $total_in, $dec);
	$rep->AmountCol(5, 6, $total_out, $dec);
	$rep->AmountCol(6, 7, $after_qty, $dec);
	$rep->Line($rep->row - 5);
	$rep->Font();
	$rep->End();
}

