<?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_INVENTORY_VALUATION_REPORT';
// ----------------------------------------------------------------
// $ Revision:	2.4 $
// Creator:		Joe Hunt, boxygen
// date_:		2014-05-13
// Title:		Inventory Valuation
// ----------------------------------------------------------------
$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 . "/inventory/includes/db/items_category_db.inc");

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

print_inventory_valuation_report();

function get_domestic_price($myrow, $stock_id)
{
    if ($myrow['type'] == ST_SUPPRECEIVE || $myrow['type'] == ST_SUPPCREDIT)
     {
        $price = $myrow['price'];
        if ($myrow['person_id'] > 0)
        {
            // Do we have foreign currency?
            $supp = get_supplier($myrow['person_id']);
            $currency = $supp['curr_code'];
            $ex_rate = $myrow['ex_rate'];
            $price *= $ex_rate;
        }
    }
    else
        $price = $myrow['standard_cost']; //pick standard_cost for sales deliveries

    return $price;
}

// function get_batch_packing($batch_no){

// $sql = "SELECT * FROM ".TB_PREF."grn_items WHERE batch_no=".db_escape($batch_no);
// $res=db_query($sql);
// $row=db_fetch($res);
// return $row;

// }




function get_grn_add_cost($grn_no)
{
	$sql = "SELECT SUM(amount) as amt FROM ".TB_PREF."bank_trans WHERE amount!=0 AND grn_no=".db_escape($grn_no);
	// return ($sql, "posted cheque no is already paid");
	$result = db_query($sql, "posted cheque no is already paid");
	// display_error(db_num_rows($result)."  num row");
	// exit();
	return db_fetch($result);
}

function get_grn_batch_data($id){
	$sql = " SELECT * FROM ".TB_PREF."grn_batch WHERE id = '$id' ";
	$result =  db_query($sql, " ");
	return db_fetch($result);
}
function getAverageCost($stock_id, $location, $to_date)
{
	if ($to_date == null)
		$to_date = Today();

	$to_date = date2sql($to_date);

  	$sql = "SELECT move.*, supplier.supplier_id person_id, IF(ISNULL(grn.rate), credit.rate, grn.rate) ex_rate
  		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=".db_escape($stock_id)."
			AND move.tran_date <= '$to_date' AND standard_cost > 0.001 AND qty <> 0 AND move.type <> ".ST_LOCTRANSFER;

	if ($location != 'all')
		$sql .= " AND move.loc_code = ".db_escape($location);

	$sql .= " ORDER BY move.stock_id";	

	$result = db_query($sql, "No standard cost transactions were returned");
    
    if ($result == false)
    	return 0;
	$qty = $tot_cost = 0;
	while ($row=db_fetch($result))
	{
		$qty += $row['qty'];	
		$price = get_domestic_price($row, $stock_id);
        $tran_cost = $row['qty'] * $price;
        $tot_cost += $tran_cost;
	}
	if ($qty == 0)
		return 0;
	return $tot_cost / $qty;
}

function getTransactions($category, $location, $date, $kv_batch, $kv_exp_date)
{
	$date = date2sql($date);

	$sql = "SELECT item.category_id,
			category.description AS cat_description,
			item.stock_id,
			item.units,
			item.description, item.inactive, item.inventory_account ,
			move.loc_code,
			SUM(move.qty) AS QtyOnHand, 
			item.material_cost AS UnitCost,
			move.price,
			SUM(move.qty) * move.price AS ItemTotal";
			if($kv_batch)
				$sql .= ", move.batch_no";
			elseif($kv_exp_date)
				$sql .= ", move.batch_no, move.exp_date";
		$sql .="	FROM "
			.TB_PREF."stock_master item,"
			.TB_PREF."stock_category category,"
			.TB_PREF."stock_moves move
		WHERE item.stock_id=move.stock_id
		AND item.category_id=category.category_id
		 AND mb_flag <> 'F' AND  item.mb_flag<>'D' 
		AND move.tran_date <= '$date' ";
		if ($category != 0)
			$sql .= " AND item.category_id = ".db_escape($category);
		if ($location != 'all')
			$sql .= " AND move.loc_code = ".db_escape($location)."   ";
		else
			$sql.= " AND  move.type!=16";
	 
	 $sql.=" GROUP BY   move.stock_id , move.batch_no , ";
		if ($location != 'all')
			$sql .= "move.loc_code, ";
		if($kv_batch || $kv_exp_date)
			$sql .="move.batch_no";
		$sql .= " HAVING ROUND(SUM(move.qty),4)!=0";
		$sql .= " ORDER BY    item.stock_id  ";  //item.category_id, item.stock_id ,
 
	  // display_error($sql);

    return db_query($sql,"No transactions were returned");
}

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

function print_inventory_valuation_report()
{
    global $path_to_root, $SysPrefs;

	$date = $_POST['PARAM_0'];
    $category = $_POST['PARAM_1'];
    $location = $_POST['PARAM_2'];
    $show_loc = $_POST['PARAM_3'];
    $comments = $_POST['PARAM_4'];
	$orientation = $_POST['PARAM_5'];
	$destination = $_POST['PARAM_6'];
	if ($destination)
		include_once($path_to_root . "/reporting/includes/excel_report.inc");
	else
		include_once($path_to_root . "/reporting/includes/pdf_report.inc");
	$detail = !$detail;
    $dec =  2; //user_price_dec();

	$orientation = ($orientation ? 'L' : 'P');
	if ($category == ALL_NUMERIC)
		$category = 0;
	if ($category == 0)
		$cat = _('All');
	else
		$cat = get_category_name($category);
	
	$kv_batch = get_company_pref('kv_batch');
	$kv_exp_date = get_company_pref('kv_exp_date');
	
	if ($location == ALL_TEXT)
		$location = 'all';
	if ($location == 'all')
		$loc = _('All');
	else
		$loc = get_location_name($location);
	// if($kv_batch || $kv_exp_date)
		$cols = array(0, 45, 120, 165, 200, 260, 310, 390,470, 520 ,570);
	// else
	// 	$cols = array(0, 75, 225, 250, 350, 450, 515);

	$headers = array(_('Batch #'), _("Ref."), _("P/Date"), _('P/Rt'), _('Purchased'), _('Sale'), _('Mill Process'), _('Stock In Hand'), _("Rate"), _("Stock Amount"));
	// if($kv_exp_date){
	// 	array_splice( $headers, 4, 0, _("Batch No") );
	// 	array_splice( $headers, 5, 0, _("Exp Date") );
	// }elseif($kv_batch)
	// 	array_splice( $headers, 4, 0, _("Batch No") );
	// if($kv_batch || $kv_exp_date)
		$aligns = array('left',	'left',	'left', 'left', 'left',	'left', 'left', 'left', 'left', 'left', 'left');
	// else
	// 	$aligns = array('left',	'left',	'left', 'right', 'right', 'right');

    $params =   array( 	0 => $comments,
    					1 => array('text' => _('End Date'), 'from' => $date, 		'to' => ''),
    				    2 => array('text' => _('Category'), 'from' => $cat, 'to' => ''),
    				    3 => array('text' => _('Location'), 'from' => $loc, 'to' => ''));

    $rep = new FrontReport(_('Stock Report'), "Stock Report", user_pagesize(), 9, $orientation);
    if ($orientation == 'L')
    	recalculate_cols($cols);
    $rep->Font();
    $rep->Info($params, $cols, $headers, $aligns);
    $rep->NewPage();

	$res = getTransactions($category, $location, $date, $kv_batch, $kv_exp_date);
	$total = $grandtotal = 0.0;
	$catt = '';
	$prev_stock_id = '';
	while ($trans=db_fetch($res))
	{
		if ($catt != $trans['inventory_account'])
		{
			if ($catt != '')
			{
				if ($detail)
				{
					$rep->NewLine(2, 3);
					if($kv_batch || $kv_exp_date)
						$rep->TextCol(0, 6, _('Total'));
					else
						$rep->TextCol(0, 4, _('Total'));
				}
				if($kv_batch || $kv_exp_date)
					$rep->AmountCol(7, 8, $total, $dec);
				else
					$rep->AmountCol(5, 6, $total, $dec);
				if ($detail)	{
					$rep->Line($rep->row - 2);
					$rep->NewLine();
				}
				$rep->NewLine();
				$grandtotal += $total;
				$total = 0.0;
			}
			$rep->Font('bold');
			$rep->TextCol(0, 1, $trans['category_id']);
			$rep->TextCol(1, 4, $trans['inventory_account']." - ".get_gl_account_name($trans['inventory_account'])) ;
			$rep->Font();
			$catt = $trans['inventory_account'];
			if ($detail)
				$rep->NewLine();
		}
		if ($prev_stock_id != $trans['stock_id']) {
			if($prev_stock_id != ''){
				$rep->NewLine(1.5);
                $rep->Font('bold');
				$rep->TextCol(4, 6, "Grand Total of ");
						$rep->TextCol(7, 8, $item_tot_bags."      ".$item_qoh, $dec2);
						$rep->TextCol(8, 9, round($item_amt_sum/$item_qoh,2));
						$rep->TextCol(9, 10, $item_amt_sum);
                        
						

						$item_tot_bags=$item_qoh=$item_amt_sum=0;
						$rep->NewLine();

			    $rep->Font();
			}
			elseif($prev_stock_id == '')
				$rep->NewLine(0.5);
			$rep->Font('bold');
			$rep->TextCol(0, 2, $trans['stock_id'] . ' - ' . $trans['description']);
			$rep->Font();
			$prev_stock_id = $trans['stock_id'];
			$rep->NewLine(0.5);
		}

		// if (isset($SysPrefs->use_costed_values) && $SysPrefs->use_costed_values==1)
		// {
		// 	$UnitCost = getAverageCost($trans['stock_id'], $location, $date);
		// 	$ItemTotal = $trans['QtyOnHand'] * $UnitCost;
		// }	
		// else
		// {
			 $price=get_betch_price($trans['batch_no'],$trans['stock_id'],true,$date);
			 	 if($trans['price']!=$price){
			 	
			 }
			 // display_error($trans['price']." == ".$price." == ".$trans['batch_no']." - ".$trans['stock_id']." - ".$trans['inventory_account']." * ".$trans['QtyOnHand']);
			 // display_error( $trans['price']." == ".$trans['batch_no']." == ".get_betch_price($trans['batch_no'],$trans['stock_id'],true) );
			$UnitCost = $price;
			$ItemTotal = $trans['QtyOnHand'] *  $price ;// $trans['QtyOnHand'] *  $price ; //$trans['ItemTotal'];
		// }	
		if ($detail)
		{
			$rep->NewLine();
			$rep->fontSize -= 2;
			
            
				$grn_batch_row=get_batch_packing($trans['batch_no']);
			if($grn_batch_row['tran_type']==25)
			{
                $grn_batch=get_grn_batch_data($grn_batch_row['grn_batch_id']);
				 // print_r($grn_batch_row);
				 // exit();
				$ref_type = get_supplier_name($grn_batch['supplier_id']);
				$tran_date= sql2date($grn_batch['delivery_date']);

				$purchase_=$grn_batch_row['no_of_pack']."    ".$grn_batch_row['qty_recd'];
             
			}elseif($grn_batch_row['tran_type']==28){
				$purchase_="-";

			}else{

				$adjustment_items = get_stock_adjustment_items($grn_batch_row['trans_no']);
				$adjustment = db_fetch($adjustment_items);
					$purchase_="-";
				// print_r($adjustment);
				// exit();
				$ref_type = "Opening Adjustment";
				$tran_date= sql2date($adjustment['tran_date']);
				// $date_

			}		

			$rep->TextCol(0, 1, $trans['batch_no']);
			$rep->TextCol(1, 2, $ref_type, -1);
			$rep->TextCol(2, 3, $tran_date);
			$rep->AmountCol(3, 4, $UnitCost, $dec2);
			$rep->TextCol(4, 5, $purchase_, 2);
			$rep->TextCol(5, 6, '-', 2);
			$rep->TextCol(6, 7, '-', 2);
			// $rep->AmountCol(7, 8, $trans['QtyOnHand'], 2); //get_qty_dec($trans['stock_id'])
			// if($kv_exp_date) {
				// $rep->TextCol(4, 5, $trans['batch_no']);
			// 	$rep->TextCol(5, 6, sql2date($trans['exp_date']));
			// } elseif($kv_batch ){ 
			// 	$rep->TextCol(4, 5, $trans['batch_no']);
			// 	// $rep->TextCol(5, 6, '');
			// }
			$dec2 = 0;
			price_decimal_format($UnitCost, $dec2);
			// if($kv_batch) {
				// $trans['inventory_account']
				// $rep->TextCol(5, 6, $trans['inventory_account']);
				if($grn_batch_row){
                //   display_error($grn_batch_row['pack_size']." - ".$grn_batch_row['pack_size']);
					if(!$grn_batch_row['pack_size'])
						$grn_batch_row['pack_size']=1;
					   	if($grn_batch_row['tran_type']==25)
					   		$grn_batch_row['no_of_pack']=$grn_batch_row['pack_size'];
						$rep->TextCol(7, 8, round($trans['QtyOnHand']/$grn_batch_row['no_of_pack'],0)."      ".$trans['QtyOnHand'], $dec2);

					$item_qoh+=$trans['QtyOnHand'];
					$item_tot_bags+=round($trans['QtyOnHand']/$grn_batch_row['no_of_pack'],0);	
				    $rep->AmountCol(8, 9, $UnitCost, $dec2);
                  if($grn_batch_row['grn_batch_id'])
                  {

                  $bnk_row=get_grn_add_cost($grn_batch_row['grn_batch_id']);
                  	// $rep->TextCol(8, 9,  "(".abs($bnk_row['amt']).")".round(abs($bnk_row['amt'])/$trans['QtyOnHand'],2),$dec2);
                  	 $UnitCost+=round(abs($bnk_row['amt'])/$trans['QtyOnHand'],2);
                  	 $ItemTotal = $trans['QtyOnHand'] *  $UnitCost ;
                  }
                  // else
                  // $rep->TextCol(8, 9,  '-',$dec2);	
				
				}
				else{
						$rep->TextCol(7, 8, '-', $dec2);
					$rep->AmountCol(8, 9, $UnitCost, $dec2);
				$rep->AmountCol(9, 10, 0, $dec2);
				}
				$rep->AmountCol(9, 10, $ItemTotal, $dec);

				$item_amt_sum+=$ItemTotal;

			if($show_loc)
			{
				 $rep->Font('B');
				$loc_res=get_item_locations(0);
				while($loc_row=db_fetch($loc_res)){
                      $rep->NewLine();
						$rep->TextCol(0, 1, $loc_row['location_name']);
						$qoh = get_qoh_batch_on_date($trans['stock_id'],$trans['batch_no'], $loc_row['loc_code'], $date);
						$rep->TextCol(1, 2,$qoh);

				}
				 $rep->Font();
				$rep->Line($rep->row  - 4);
			}	
			// }
			// elseif ($kv_exp_date) {
			// 	$rep->AmountCol(6, 7, $UnitCost, $dec2);
			// 	$rep->AmountCol(7, 8, $ItemTotal, $dec);
			// } else {
			// 	$rep->AmountCol(4, 5, $UnitCost, $dec2);
			// 	$rep->AmountCol(5, 6, $ItemTotal, $dec);
			// }
			$rep->fontSize += 2;
		}
		$total += $ItemTotal;
		
	}

			$grandtotal += $total;
	if ($detail)
	{
		$rep->NewLine(2, 3);
		if($kv_batch || $kv_exp_date)
			$rep->TextCol(0, 6, _('Total'));
		else
			$rep->TextCol(0, 4, _('Total'));
	}
	if($kv_batch || $kv_exp_date)
		$rep->Amountcol(7, 8, $total, $dec);
	else
		$rep->Amountcol(5, 6, $total, $dec);
	if ($detail)
	{
		$rep->Line($rep->row - 2);
		$rep->NewLine();
	}
	$rep->NewLine(2, 1);
	if($kv_batch || $kv_exp_date) {
		$rep->TextCol(0, 6, _('Grand Total'));
		$rep->AmountCol(7, 8, $grandtotal, $dec);
	} else {
		$rep->TextCol(0, 4, _('Grand Total'));
		$rep->AmountCol(5, 6, $grandtotal, $dec);
	}
	$rep->Line($rep->row  - 4);
	$rep->NewLine();
    $rep->End();
}

