<?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, quantity',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, quantity,CONCAT(loc.code," (",loc.desc,")") locationid',false)
					->from('view_inventorylist_subtotal')
					->join('tbl_location loc','loc.fldid = view_inventorylist_subtotal.locationid','LEFT')
					->where(array('view_inventorylist_subtotal.companyid'=>element('compID', $this->session->userdata('logged_in'))));
		}*/
		if($locID != ''){
			$this->db->select('iDet.itemID,i.name,iCat.name category,i.description,iDet.onHandQty quantity',false)
					->from('item_details iDet')
					->join('item i','i.ID = iDet.itemID','LEFT')
					->join('comp_item_category iCat','iCat.ID = i.itemCategoryID','LEFT')
					->where(array(
								'iDet.companyid'=>element('compID', $this->session->userdata('logged_in')),
								'iDet.locationID'=>$locID
							));
		}else{
			$this->db->select('iDet.itemID,i.name,iCat.name category,i.description,iDet.onHandQty quantity,CONCAT(loc.code," (",loc.desc,")") itemLocation',false)
					->from('item_details iDet')
					->join('item i','i.ID = iDet.itemID','LEFT')
					->join('comp_item_category iCat','iCat.ID = i.itemCategoryID','LEFT')
					->join('tbl_location loc','loc.fldid = iDet.locationID','LEFT')
					->where(array(
								'iDet.companyid'=>element('compID', $this->session->userdata('logged_in'))
							));
		}
		$query = $this->db->get();
		//echo $this->db->last_query();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return false;
		}
	}

	//Inventory balance item remove
	public function deleteBalance($receiveID){
		$rcvInfo = $this->db->select("locationID")->where('ID',$receiveID)->get('comp_receive_item')->result();
		$rcvDet = $this->db->select("itemID,quantityReceived")->where('receiveID',$receiveID)->get('comp_receive_item_detail')->result();
		print_r($rcvInfo);
		print_r($rcvDet);
		$where = array(
				'companyID' => element('compID', $this->session->userdata('logged_in')),
				'locationID' => $rcvInfo[0]->locationID,
				'itemID' => $rcvDet[0]->itemID,
			);

		$this->db->set('onHandQty','onHandQty-'.$rcvDet[0]->quantityReceived,false)
				->where($where)->update('item_details');
		$this->db->delete('comp_receive_item', array('ID' => $receiveID)); 
		$this->db->delete('comp_receive_item_detail', array('receiveID' => $receiveID)); 
		return $receiveID;
	}
	//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("[",i.itemCode,"]",i.name) 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("[",i.itemCode,"]",i.name) 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 all kind of transaction (transaction in/ Transaction out)
	public function getInventoryByTransaction($dateFrom = '',$dateTo = '',$locID = '',$itemID = ''){
		if($locID != 'all'){
			$this->db
				->select('formNo,i.name,qtyIn quantityIn,IF(qtyOut=0,qtyOut,-qtyOut) quantityOut,transType transactionType,um unitOfMeasure,supplierName supplierCustomer,DATE_FORMAT(transDate,"%d-%m-%Y") as TransactionDate,a.createdTS',false)
				->from('view_inventory_transaction a')
				->join('item i','i.ID = a.itemID','LEFT')
				->order_by('a.createdTS',"ASC")
				->where(array('a.companyID'=>element('compID', $this->session->userdata('logged_in')),'a.locationID'=>$locID,'itemID'=>$itemID,'transDate >='=>date('Y-m-d',strtotime($dateFrom)),'transDate <='=>date('Y-m-d',strtotime($dateTo))));	

		}else{
			$this->db
				->select('formNo,i.name,qtyIn quantityIn,IF(qtyOut=0,qtyOut,-qtyOut) quantityOut,transType transactionType,um unitOfMeasure,supplierName supplierCustomer,DATE_FORMAT(transDate,"%d-%m-%Y") as TransactionDate,a.createdTS',false)
				->from('view_inventory_transaction a')
				->join('item i','i.ID = a.itemID','LEFT')
				->order_by('a.createdTS',"ASC")
				->where(array('a.companyID'=>element('compID', $this->session->userdata('logged_in')),'itemID'=>$itemID,'transDate >='=>date('Y-m-d',strtotime($dateFrom)),'transDate <='=>date('Y-m-d',strtotime($dateTo))));
		}
		$query = $this->db->get();
		
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return false;
		}
	}
	//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 itemID,(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(array(
											'so.companyID'=>element('compID', $this->session->userdata('logged_in')),
											'so.is_delete' => 0
											))
								->get();
		$sales = $salesOrder->result();
		
		$purchaseOrder = $this->db->select("i.ID itemID,po.ID poID,poDet.purchaseOrderID,(poDet.quantityOrder-poDet.quantityReceivedTotal) as purchaseBalance,poDet.ID poDetID")
								->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(array(
											'po.companyID'=>element('compID', $this->session->userdata('logged_in')),
											'po.is_delete' => 0
											))
								->get();
		$purchase = $purchaseOrder->result();
		/*foreach($purchase as $v){
			$query = $this->db->select('riDet.itemID,riDet.poDetailID,SUM(riDet.quantityReceived) qtyRcv',false)
							->from('comp_receive_item_detail riDet')
							//->join('comp_receive_item ri','ri.ID = riDet.receiveID','left inner')
							->where(array(
									'riDet.poDetailID'=>$v->poDetID,
									'riDet.itemID'=>$v->itemID,
									//'ri.companyID'=>element('compID', $this->session->userdata('logged_in')),
									//'ri.locationID'=>$locID
								))
							->group_by('riDet.poDetailID,riDet.itemID')
							->get();
			$qtyRcv = $query->result();
			if(!empty($qtyRcv)){
				$v->purchaseBalance = $v->purchaseBalance - $qtyRcv[0]->qtyRcv;
			}
			/*print_r($qtyRcv);
			print_r($v);
			echo "<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>><br />";
		}*/
		//exit();
		//Gather item detail
		//$itemQry = $this->db->select('i.ID,i.itemCode,i.name,SUM(iDet.onHandQty) qtyOnHand')
		//				->from('comp_receive_item_detail')
		$this->db->select('i.ID,i.itemCode,i.name,SUM(iDet.onHandQty) qtyOnHand')
						->from('item_details as iDet')
						->join('item as i','i.ID = iDet.itemID','left')
						->where(array('iDet.companyID'=>element('compID', $this->session->userdata('logged_in'))))
						->group_by("iDet.itemID");
		if($locID != 'all'){
			$this->db->where("iDet.locationID",$locID);
		}
		$itemQry = $this->db->get();

		$item = $itemQry->result();
		foreach($item as &$row){

			//Sales balance
			$sum = 0;
			foreach($sales as $v){
				if($v->itemID == $row->ID)
					$sum += $v->salesBalance;
			}
			$row->salesBalance = $sum;
			
			//purchase balance
			$sum = 0;
			foreach($purchase as $v){
				if($v->itemID == $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("um.code unitMesure,
									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,
									((round(riDet.quantityOrder/IFNULL(umConv.factor,1))-SUM(round(riDet.quantityReceived/IFNULL(umConv.factor,1))))*poDet.unitPrice) as total,
									round(riDet.quantityOrder/IFNULL(umConv.factor,1)) totalOrder,
									SUM(round(riDet.quantityReceived/IFNULL(umConv.factor,1))) totalRcv,
									(poDet.quantityOrder-IFNULL(SUM(round(riDet.quantityReceived/IFNULL(umConv.factor,1))),0)) Qty,
									poDet.itemID,
								",false)

						->from('comp_purchase_order_detail as poDet')
						->join('comp_purchase_order as po','po.ID = poDet.purchaseOrderID','LEFT')
						->join('comp_receive_item_detail riDet','riDet.poDetailID = poDet.ID',"LEFT")
						->join('comp_receive_item ri','ri.ID = riDet.receiveID',"LEFT")
						->join('item as i','i.ID=poDet.itemID','left')
						->join('_sys_um um','um.fldid=poDet.umID','left')
						->join('_sys_um_conv umConv','umConv.itemID = poDet.itemID and umConv.from_um=riDet.standUM and umConv.to_um=riDet.umID and umConv.companyID=po.companyID','LEFT')
						->where(array(
								'po.companyID'	=>element('compID', $this->session->userdata('logged_in')),
								'po.updateAble'	=>0,
								//'poDet.itemID'	=>94,
								'ri.updateAble'	=>0,
							))
						//->where('(poDet.quantityOrder-IFNULL(SUM(round(riDet.quantityReceived/IFNULL(umConv.factor,1))),0)) > 0')
						->group_by('poDet.ID')
						->get();
		/*$query = $this->db->select('*')
						->where(array(
									'companyID'=>element('compID', $this->session->userdata('logged_in')),
									'QtyLeft >'=> 0
								))
						->get('view_grn_report');*/
		//echo $this->db->last_query();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return false;
		}							
	}

	//Inventory valuation summary
	public function getInventoryValuation(){
		$query = $this->db->select('CONCAT("[",item.itemCode,"] ",item.name) as items,i.companyID,i.itemID,SUM(i.onHandQty) onHand, um.desc um,IFNULL(sp.price,0) salesPrice,(IFNULL(sp.price,0)*SUM(i.onHandQty)) retailValue',false)
						->from('item_details i')
						->join('item','item.ID = i.itemID','LEFT')
						->join('_sys_um um','um.fldid = item.unitOfMeasureID','left')
						->join('comp_sales_price sp','sp.itemID = i.itemID','left outer')
						->where(array(
									'i.companyID'=>element('compID',$this->session->userdata('logged_in')),
									//'sp.startDate <='=>date('Y-m-d'),'sp.endDate >='=>date('Y-m-d')
								))
						->order_by('itemID','ASC')
						->group_by('itemID')
						->distinct()
						->get();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return false;
		}	
	}

	//Gather asset value according to items
	public function getAssetVal($id){
		$query = $this->db->select('riDet.unitPrice,riDet.balanceItem,(riDet.unitPrice*riDet.balanceItem) assetVal')
						->from('comp_receive_item_detail riDet')
						->join('comp_receive_item ri','ri.ID = riDet.receiveID','LEFT')
						->where(array('balanceItem >'=>0,'itemID'=>$id,'ri.updateAble'=>0))
						->get();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return false;
		}	
	}

	//Inventory list
	public function getItemList(){
		$query = $this->db->select('i.ID,i.name,i.itemCode,iCat.name category,i.description',false)
						->from('item i')
						->join('item_details iDet','iDet.itemID = i.ID','LEFT')
						->join('comp_item_category iCat','iCat.ID = i.itemCategoryID','LEFT')
						->distinct()
						->where(array(
									'i.companyid'=>element('compID', $this->session->userdata('logged_in'))
								))
						->get()->result();
		if(!empty($query)){
			return $query;
		}else{
			return false;
		}
	}
}