<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Inventory_list extends CI_Model{
	
	//Gather all inventory by location
	public function getInventoryByLoc($locID){

		if($locID != ''){
			$this->db->select('itemID,itemName,category,description,stockValue',false)
					->from('view_inventorylist_subtotal')
					->where(array('companyid'=>element('compID', $this->session->userdata('logged_in')),'locationid'=>$locID));
		}else{
			$this->db->select('itemID,itemName,category,description,stockValue',false)
					->from('view_inventorylist_subtotal')
					->where(array('companyid'=>element('compID', $this->session->userdata('logged_in'))));
		}
		$query = $this->db->get();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return false;
		}
	}

	//Inventory subtotal detail information
	public function rptSubtotalDetail($itemID = '',$locID = ''){
		
		/*
		 $query = $this->db->select('i.itemCode,i.name,mc.name as Category,mcU.desc as unit,riDet.balanceItem,riDet.unitPrice,(riDet.balanceItem*riDet.unitPrice) as total')
							->from('comp_item_details iDet')
							->join('comp_receive_item ri','ri.ID = riDet.receiveID','left')
							->join('item as i','i.ID = iDet.itemID','left')
			 				->join('master_code as mc','mc.ID = i.itemCategoryID','left')
			 				->join('_sys_um as mcU','mcU.fldid = riDet.umID','left')
							->where(array('ri.companyID'=>element('compID', $this->session->userdata('logged_in')),'riDet.itemID'=>$itemID,'ri.locationID'=>$locID))
							->get();
		 */
		
		if($locID != ''){
//			$query = $this->db->select('riDet.itemCode,riDet.itemName,mc.name as Category,mcU.desc as unit,riDet.balanceItem,riDet.unitPrice,(riDet.balanceItem*riDet.unitPrice) as total')
//****change format - akmal 20140708
			$query = $this->db->select('concat("[",riDet.itemCode,"]",riDet.itemName) as itemCode,riDet.description as itemName,mc.name as Category,mcU.desc as unit,riDet.balanceItem,riDet.unitPrice,(riDet.balanceItem*riDet.unitPrice) as total', false)
							->from('comp_receive_item_detail riDet')
							->join('comp_receive_item ri','ri.ID = riDet.receiveID','left')
							->join('item as i','i.ID = riDet.itemID','left')
			 				->join('master_code as mc','mc.ID = i.itemCategoryID','left')
			 				->join('_sys_um as mcU','mcU.fldid = riDet.umID','left')
							->where(array('ri.companyID'=>element('compID', $this->session->userdata('logged_in')),'riDet.itemID'=>$itemID,'ri.locationID'=>$locID))
							->get();
		}else{
			$query = $this->db->select('concat("[",riDet.itemCode,"]",riDet.itemName) as itemCode,riDet.description as itemName,mc.name as Category,mcU.desc as unit,riDet.balanceItem,riDet.unitPrice,(riDet.balanceItem*riDet.unitPrice) as total', false)
							->from('comp_receive_item_detail riDet')
							->join('comp_receive_item ri','ri.ID = riDet.receiveID','left')
							->join('item as i','i.ID = riDet.itemID','left')
							->join('master_code as mc','mc.ID = i.itemCategoryID','left')
							->join('_sys_um as mcU','mcU.fldid = riDet.umID','left')
							->where(array('ri.companyID'=>element('compID', $this->session->userdata('logged_in')),'riDet.itemID'=>$itemID))
							->get();
		}
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return false;
		}
		
	}
// 		if($locID != ''){
// 			$this->db
// 				->select('i.itemCode,i.name itemName,i.description,mc.name as category,mcU.desc as UOM,iDet.onHandQty as quantity,FORMAT(i.supplierPrice,2) as purchasePrice,FORMAT((iDet.onHandQty*i.supplierPrice),2) as value',false)
// 				->from('item_details iDet')
// 				->join('item as i','i.ID = iDet.itemID','left')
// 				->join('master_code as mc','mc.ID = i.itemCategoryID','left')
// 				->join('_sys_um as mcU','mcU.fldid = i.unitOfMeasureID','left')
// 				->where(array('i.companyID'=>element('compID', $this->session->userdata('logged_in')),'iDet.locationID'=>$locID,'i.is_delete'=>0));	
// 		}else{
// 			//CONCAT("[",i.itemCode,"] ",i.name) as itemName
// 			$this->db
// 				->select('i.itemCode,i.name itemName,i.description,mc.name as category,mcU.desc as UOM,iDet.onHandQty as quantity,FORMAT(i.supplierPrice,2) as purchasePrice,FORMAT((iDet.onHandQty*i.supplierPrice),2) as value',false)
// 				->from('item_details iDet')
// 				->join('item as i','i.ID = iDet.itemID','left')
// 				->join('master_code as mc','mc.ID = i.itemCategoryID','left')
// 				->join('_sys_um as mcU','mcU.fldid = i.unitOfMeasureID','left')
// 				->where(array('i.companyID'=>element('compID', $this->session->userdata('logged_in')),'i.is_delete'=>0));
// 		}
// 		$query = $this->db->get();
//	}
	
	//Gather inventory by movement
	public function getInventoryByMovement($dateFrom = '',$dateTo = '',$locID = '',$itemID = ''){
		if($locID != 'all'){
			$this->db
				->select('formNo,name,quantityIn,quantityOut,transactionType,unitOfMeasure,supplierCustomer,DATE_FORMAT(transactionDate,"%d-%m-%Y") as TransactionDate',false)
				->from('view_inventory_statement')
				->where(array('companyID'=>element('compID', $this->session->userdata('logged_in')),'locationID'=>$locID,'itemID'=>$itemID,'transactionDate >='=>date('Y-m-d',strtotime($dateFrom)),'transactionDate <='=>date('Y-m-d',strtotime($dateTo))));	
		}else{
			$this->db
				->select('formNo,name,quantityIn,quantityOut,transactionType,unitOfMeasure,supplierCustomer,DATE_FORMAT(transactionDate,"%d-%m-%Y") as TransactionDate',false)
				->from('view_inventory_statement')
				->where(array('companyID'=>element('compID', $this->session->userdata('logged_in')),'itemID'=>$itemID,'transactionDate >='=>date('Y-m-d',strtotime($dateFrom)),'transactionDate <='=>date('Y-m-d',strtotime($dateTo))));
		}
		$query = $this->db->get();
		
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return false;
		}
	}
	
	//Get item by locaiton
	public function getItemsbyLoc($locID = ''){
		if(!empty($locID) && $locID != 'all' ){
			$query = $this->db->select('i.ID,i.name')
							->from('item_details as iDet')
							->join('item as i','i.ID=iDet.itemID','left')
							->where(array('i.companyID'=>element('compID', $this->session->userdata('logged_in')),'iDet.locationID'=>$locID))
							->distinct()->get();
		}else{
			$query = $this->db->select('i.ID,i.name')
							->from('item_details as iDet')
							->join('item as i','i.ID=iDet.itemID','left')
							->where(array('i.companyID'=>element('compID', $this->session->userdata('logged_in'))))
							->distinct()->get();
		}
		
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return false;
		}
	}
	
	//Gather inventory status
	public function getInventoryStatus($locID = ''){
		$salesOrder = $this->db->select("i.ID,(sd.quantityOrder-sd.quantityShip) as salesBalance")
								->from('comp_sales_order as so')
								->join('comp_sales_detail as sd','sd.salesID = so.ID','left')
								->join('item as i','i.ID = sd.itemID','left')
								->where('so.ID NOT IN (SELECT DISTINCT salesOrderID FROM comp_delivery_order WHERE updateAble = 0)',null,false)
								->where('so.companyID',element('compID', $this->session->userdata('logged_in')))
								->get();
		$sales = $salesOrder->result();
		
		$purchaseOrder = $this->db->select("i.ID,po.ID poID,poDet.purchaseOrderID,(poDet.quantityOrder-poDet.quantityReceivedTotal) as purchaseBalance")
								->from('comp_purchase_order as po')
								->join('comp_purchase_order_detail as poDet','poDet.purchaseOrderID = po.ID','left')
								->join('item as i','i.ID = poDet.itemID','left')
								->where('po.ID NOT IN (SELECT DISTINCT purchaseOrderID FROM comp_receive_item WHERE updateAble = 0)',null,false)
								->where('po.companyID',element('compID', $this->session->userdata('logged_in')))
								->get();
		$purchase = $purchaseOrder->result();
		
		//Gather item detail
		$itemQry = $this->db->select('i.ID,i.itemCode,i.name,iDet.onHandQty as qtyOnHand')
						->from('item_details as iDet')
						->join('item as i','i.ID = iDet.itemID','left')
						->where(array('iDet.locationID'=>$locID))
						->distinct()->get();
		$item = $itemQry->result();
		foreach($item as &$row){
			//Sales balance
			$sum = 0;
			foreach($sales as $v){
				if($v->ID == $row->ID)
					$sum += $v->salesBalance;
			}
			$row->salesBalance = $sum;
			
			//purchase balance
			$sum = 0;
			foreach($purchase as $v){
				if($v->ID == $row->ID)
					$sum += $v->purchaseBalance;
			}
			$row->purchaseBalance = $sum;
		}

		if(sizeof($item) >= 1){
			return $item;
		}else{
			return false;			
		}
	}
	
	//Gather GRN Valuation
	public function getGrnValuation(){
		$query= $this->db->select("poDet.ID,i.itemCode,i.name as itemName,CONCAT(po.formNo,'[',lpad(po.serialNo,8,0),']') as poNo,DATE_FORMAT(po.poDate,'%d-%m-%Y') as PoDate,poDet.unitPrice,poDet.quantityOrder,(poDet.quantityOrder-poDet.quantityReceivedTotal) as Qty,((poDet.quantityOrder-poDet.quantityReceivedTotal)*poDet.unitPrice) as total",false)
						->from('comp_purchase_order_detail as poDet')
						->join('comp_purchase_order as po','po.ID = poDet.purchaseOrderID','left')
						->join('item as i','i.ID=poDet.itemID','left')
						->where(array(
								'po.companyID'=>element('compID', $this->session->userdata('logged_in')),
							))
						->where('poDet.quantityOrder > poDet.quantityReceivedTotal')
						->get();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return false;
		}							
	}
}