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

class Item_count_adjust extends CI_Model{
	
	//Gather form data
	public function getData($locID = ''){
		if($locID){
			$query = $this->db
				->select('i.ID,i.itemCode,i.name,iDet.onHandQty as quantity,i.supplierPrice,(iDet.onHandQty*i.supplierPrice) as costQty')
				->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')),'iDet.locationID'=>$locID))
				->get();
		}else{
			$query = $this->db
				->select('i.ID,i.itemCode,i.name,iDet.onHandQty as quantity,i.supplierPrice,(iDet.onHandQty*i.supplierPrice) as costQty')
				->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'))))
				->get();
		}
		
		$data['datatbls'] = $query->result();
		return $data;
	}
	
	//Get Item information
	public function getItem($id,$locID = ''){
		$query = $this->db
			->select('iDet.umID,iDet.onHandQty,i.supplierPrice')
			->from('item as i')
			->join('item_details as iDet','iDet.itemID = i.ID')
			->where(array('i.ID'=>$id,'iDet.locationID'=>$locID))
			->get();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return false;
		}
	}
	
	//Save adjustment
	public function save($data){
		$query = $this->db->insert('item_adjustment', $data);
		if($this->db->affected_rows() > 0)	
			return $this->db->insert_id();
		else
			return false;
	}
	//Save adjustment detail
	public function saveDetail($dataDetail){
		$query = $this->db->insert('item_adjustment_detail', $dataDetail);	
		if($this->db->affected_rows() > 1)	
			return $this->db->affected_rows();
		else
			return false;
	}
	
	//Gather items that is belongs to one adjustment transaction
	public function getItemsAsAdjust($adjstID){
		$query = $this->db->select('iaDet.itemID,iaDet.qtyOnHand,iaDet.counted,ia.locationID,ia.companyID')
				->from('item_adjustment as ia')
				->join('item_adjustment_detail as iaDet','iaDet.adjustmentID=ia.ID')
				->where(array('ia.ID'=>$adjstID))
				->get();
		
		if($this->db->affected_rows() > 0){
			return $query->result();
		}else{
			return false;
		}
	}
	
	//Update balance item in comp_receive_item_detail
	public function updateBlnc($itemID,$counted,$quantity){
		//Item information
		$query = $this->db
			->select('quantity,supplierPrice,')
			->from('item')
			->where('ID',$itemID)
			->get();
		$prevItem = $query->result();
		if(($required = $quantity - $counted) > 0){
			//Get Item received information
			$query = $this->db
				->select('ID,quantityReceived,balanceItem,createdTS')
				->from('comp_receive_item_detail')
				->order_by('createdTS','ASC')
				->where(array('itemID'=>$itemID,'balanceItem !=' => 0))
				->get();
			$prevItemRcv = $query->result();
			foreach($prevItemRcv as $row){
				if(($required - $row->balanceItem) > 0 ){
					$required = $required - $row->balanceItem;
					$query = $this->db->where('ID',$row->ID)->update('comp_receive_item_detail',array('balanceItem'=>0));
				}elseif(($required - $row->balanceItem) <= 0 ){
					$query = $this->db->where('ID',$row->ID)->update('comp_receive_item_detail',array('balanceItem'=>($row->balanceItem - $required)));
					$required = 0;
					break;
				}
			}
		}elseif($required < 0){
			//Get Item received information
			$query = $this->db
				->select('ID,quantityReceived,balanceItem,createdTS')
				->from('comp_receive_item_detail')
				->order_by('createdTS','DESC')
				->where(array('itemID'=>$itemID))
				->get();
			
			$prevItemRcv = $query->result();
			$required = -$required;			
			
			foreach($prevItemRcv as $row){
				if(($fillNeed = $row->quantityReceived - $row->balanceItem) > 0){
					if($fillNeed < $required){
						$required = $required - $fillNeed;
						$query = $this->db->where('ID',$row->ID)->update('comp_receive_item_detail',array('balanceItem'=>$row->quantityReceived));
					}else{
						$query = $this->db->where('ID',$row->ID)->update('comp_receive_item_detail',array('balanceItem'=>$row->balanceItem+$required));
						$required = 0;
						break;
					}
				}
			}
		}else{
			return false;
		}
	}	
	
	//Update counted item quantity
	public function updateQty($newData){
		
		$query = $this->db
					->where(array('itemID'=> $newData['itemID'],'companyID'=> $newData['companyID'],'locationID'=> $newData['locationID']))
					->update('item_details', array('onHandQty'=>$newData['counted']));
	
		if($this->db->affected_rows() > 0){
			return $this->db->affected_rows();
		} else {
			return 0;
		}
	}
	
	//Update counted item quantity
	/*public function updateQty($id,$data,$quantity){
		
		
		//Keep record for adjustment.
		$query = $this->db
			->select('quantity,supplierPrice,')
			->from('item')
			->where('ID',$id)
			->get();
		$prevItem = $query->result();
		
		if($quantity != $data['quantity']){
			/*$data = array(
					'companyID'		=> element('compID', $this->session->userdata('logged_in')),
					'locationID'	=> 
				);*/
			
		/*	$newData = array(
					'itemID'		=> $id,
					'qtyOnHand' 	=> $prevItem[0]->quantity,
					'counted'		=> $data['quantity'],
					'onDatePrice' 	=> $prevItem[0]->supplierPrice,
					'createdBy'		=> element('role',$this->session->userdata('logged_in')),
				);
			$query = $this->db->insert('item_adjustment_detail', $newData);
			$rows1 = $this->db->affected_rows();
			
			$this->db
				->where('ID', $id)
				->update('item', $data);
			$rows2 = $this->db->affected_rows();
		}
		
		
		
		if(isset($rows1) && isset($rows2)){
			return 1;
		} else {
			return 0;
		}
	}*/
	
	//Gather transaction information from database
	public function getTransaction(){
		$query = $this->db->select('ia.ID,CONCAT(ia.formNo,"[",ia.ID,"]") as formNo,l.code,ia.adjustmentDate',false)
				->from('item_adjustment as ia')
				->join('tbl_location as l','l.fldid=ia.locationID','left')
				->where(array('ia.companyId'=>element('compID', $this->session->userdata('logged_in')),'updateAble'=>1))
				->get();
		if($this->db->affected_rows() > 0){
			return $query->result();
		}else{
			return false;
		}
	}
	
	//Get transaction data for edit
	public function getItemEdit($id){
		$query = $this->db->select('iaDet.ID,ia.locationID,ia.formNo,i.itemCode,i.name,iaDet.qtyOnHand,iaDet.onDatePrice,iaDet.counted')
						->from('item_adjustment as ia')
						->join('item_adjustment_detail as iaDet','iaDet.adjustmentID = ia.ID','left')
						->join('item as i','i.ID = iaDet.itemID','left')
						->where(array('ia.ID'=>$id))->get();
		if($this->db->affected_rows() > 0){
			return $query->result();
		}else{
			return false;
		}
	}
	
	//Update adjust item information
	public function updateAdjustInfo($data){
		$query = $this->db->where('ID',$data['ID'])->update('item_adjustment_detail',$data);
		if($this->db->affected_rows() > 0){
			return true;
		}else{
			return false;
		}
	}
	
	//Update transaction updateAble status
	public function cngUpdateAble($id){
		$this->db->where('ID',$id)->update('item_adjustment',array('updateAble'=>0));
		
		if($this->db->affected_rows() > 0){
			return $this->db->affected_rows();
		}else{
			return false;
		}
	}
	
	/****************************************
	----Report Location Transfer List--------
	****************************************/
	public function rptList($srtDate = '',$endDate = ''){
		if($srtDate != '' && $endDate != ''){
			$query = $this->db->select("ia.ID,CONCAT(ia.formNo,'[',lpad(ia.ID,8,0),']') as formNo,lf.code as Location,DATE_FORMAT(ia.adjustmentDate,'%d-%m-%Y') as adjustmentDate,ia.updateable",false)
							->from('item_adjustment as ia')
							->join('tbl_location as lf','lf.fldid = ia.locationID','left')
							->where(array('ia.companyID'=>element('compID', $this->session->userdata('logged_in')),'ia.createdTS >='=>$srtDate,'ia.createdTS <='=>$endDate))
							->get();
		}
		if($this->db->affected_rows()){
			return $query->result();
		}else{
			return false;
		}
	}
	
	//Gather adjustment detail
	public function rptDetail($id){
		$query = $this->db->select('CONCAT("[",i.itemCode,"] ",i.name) as itemName,description,iaDet.qtyOnHand as qtyOnHand,iaDet.counted as actualCount,iaDet.onDatePrice',false)
						->from('item_adjustment_detail as iaDet')
						->join('item as i','i.ID=iaDet.itemID','left')
						->where('iaDet.adjustmentID',$id)
						->get();
		
		if($this->db->affected_rows()){
			return $query->result();
		}else{
			return false;
		}
	}
	
	//Adjustment Detain information
	public function adjustDetail($id){
		//,CONCAT(u.username," (",u.email,")" as userInfo,CONCAT(ia.formNo,"[",lpad(ia.ID,8,0),"]") as formNo,DATE_FORMAT(ia.adjustmentDate,"%d-%m-%Y") as adjustmentDate',false
		$query = $this->db->select('l.code as location,CONCAT(u.username," (",u.email,")") AS userInfo, CONCAT(ia.formNo,"[",lpad(ia.ID,8,0),"]") as formNo,DATE_FORMAT(ia.adjustmentDate,"%d-%m-%Y") as adjustmentDate',false)
						->from('item_adjustment as ia')
						->join('tbl_location as l','l.fldid = ia.locationID','left')
						->join('tbl_usermaster as u','u.fldid = ia.createdBy','left')
						->where('ia.ID',$id)
						->get();
		
		if($this->db->affected_rows()){
			return $query->result();
		}else{
			return false;
		}	
	}
}