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

class Location_transfer extends CI_Model{

	//Gether all location transfer information
	public function get_loc_transfer_all($id){
		$this->db
			->select('lt.ID,CONCAT(lt.formNo,"[",lpad(lt.ID,8,0),"]") as FormNo,loc.code as fromLoc,locTo.code as toLoc,mc.name as movetype,memo,DATE_FORMAT(dateTransfer,"%d-%m-%Y") as dateTransfer',false)
			->from('comp_location_transfer as lt')
			->join('tbl_location as loc','loc.fldid=lt.fromLocationID','left')
			->join('tbl_location as locTo','locTo.fldid=lt.toLocationID','left')
			->join('master_code as mc','mc.ID=movementTypeID','left')
			->where(array('lt.companyID'=>$id,'updateable'=>1));
		
		$query = $this->db->get();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return false;
		}
	}
	
	//Confirm location transfer transactions
	public function confirm($id,$compID){
		$query = $this->db->select('ltd.itemID,lt.fromLocationID,lt.toLocationID,lt.formNo,ltd.quantity')
						->from('comp_location_transfer as lt')
						->join('comp_location_transfer_detail as ltd','ltd.locationTransferID = lt.ID','left')
						->where(array('lt.ID'=>$id))
						->get();
									
		foreach($query->result() as $v){
			$fromLoc = $this->db->select('ID,onHandQty')->from('item_details')->where(array('companyID'=>$compID,'locationID'=>$v->fromLocationID,'itemID'=>$v->itemID))->get()->result();
			$toLoc   = $this->db->select('ID,onHandQty')->from('item_details')->where(array('companyID'=>$compID,'locationID'=>$v->toLocationID,'itemID'=>$v->itemID))->get()->result();
			if(!empty($fromLoc) && !empty($toLoc)){
				$this->db->where('ID', $fromLoc[0]->ID)->update('item_details', array('onHandQty'=>($fromLoc[0]->onHandQty-$v->quantity)));
				$this->db->where('ID', $toLoc[0]->ID)->update('item_details', array('onHandQty'=>($toLoc[0]->onHandQty+$v->quantity)));
				$this->db->where('ID', $id)->update('comp_location_transfer', array('updateable'=>0));
			}else{
				$this->db->where('ID', $fromLoc[0]->ID)->update('item_details', array('onHandQty'=>($fromLoc[0]->onHandQty-$v->quantity)));
				$this->db->insert('item_details', array('companyID'=>$compID,'itemID'=>$v->itemID,'locationID'=>$v->toLocationID,'onHandQty'=>$v->quantity));
				$this->db->where('ID', $id)->update('comp_location_transfer', array('updateable'=>0));
			}
						
		}
		return true;
	}
	
	//Gether Location information
	public function location_byCompany($id){
		$this->db
			->select('fldid,code')
			->from('tbl_location')
			->where(array('companyID'=>$id));
			
		$query = $this->db->get();
		
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return false;
		}
	}
	
	//Insert Location information
	public function insert($data){
		$query = $this->db->insert('comp_location_transfer', $data);
		
		if($query){
			return $this->db->insert_id();
		}else{
			return FALSE;
		}	
	}
	
	//Insert Location Detail information
	public function insertDetail($data){
		$query = $this->db->insert('comp_location_transfer_detail', $data);
		
		if($query){
			return $this->db->insert_id();
		}else{
			return FALSE;
		}	
	}
	//Gather Item lists by location
	public function itemByLoc($locID){
		$query = $this->db->select('i.itemCode,i.name')
						->from('item_details as iDet')
						->join('item as i','i.ID = iDet.itemID','left')
						->where(array('iDet.locationID'=>$locID))
						->get();
		if($this->db->affected_rows() > 0){
			return $query->result();
		}else{
			return false;
		}
	}
	
	//Gather item lists by item code
	public function get_byID($id,$locID){
		$query = $this->db->select('i.ID,i.itemCode,i.description,iDet.onHandQty as stock')
						->from('item_details as iDet')
						->join('item as i','i.ID=iDet.itemID','left')
						->where(array('itemCode'=>$id,'iDet.locationID'=>$locID))->get();
		return $query->result();
	}
	
	/**********************
	-----------Edit--------
	**********************/
	
	public function get_loc_trans_by_id($id){
		//Transfer information
		$query1 = $this->db
						->select('*')
						->from('comp_location_transfer')
						->where(array("ID"=>$id))
						->get();
					
		$data['transInfo'] = $query1->result();
		
		//Transfer item detail
		$this->db
			->select('ld.*,item.itemCode,item.description')
			->from('comp_location_transfer_detail as ld')
			->join('item','item.ID=ld.itemID','left')
			->where(array('locationTransferID'=>$data['transInfo'][0]->ID));
		
		$query2 = $this->db->get();
		$data['itemList'] = $query2->result();
		
		if($query1->num_rows() >= 1){
			return $data;
		}else{
			return false;
		}
	}
	
	/**********************
	-----------Update--------
	**********************/
	
	public function update($id,$data){
		$this->db
			->where('ID', $id)
			->update('comp_location_transfer', $data);
		$rows = $this->db->affected_rows();
		
		
		if($rows >= 1){
			return $rows;
		} else {
			$last_query = $this->db->last_query();
			return false;
		}
	}
	
	public function updateDetail($id,$data){
		$rows = 0;
		$query = $this->db->select('ID')->from('comp_location_transfer_detail')->where('itemID',$id)->get();
		if(empty($query->result())){
			$query = $this->db->insert('comp_location_transfer_detail', $data);
			$rows = $this->db->affected_rows();
			
		}else{
			$this->db
				->where('ID', $id)
				->update('comp_location_transfer_detail', $data);
			$rows = $this->db->affected_rows();
		}
				
		if($rows >= 1){
			return $rows;
		} else {
			$last_query = $this->db->last_query();
			return false;
		}
	}
	
	//Update row information
	public function updaterow($id,$data){
		$this->db
			->where('ID', $id)
			->update('comp_location_transfer_detail', $data);
		$rows = $this->db->affected_rows();
		
		
		if($rows >= 1){
			return $rows;
		} else {
			$last_query = $this->db->last_query();
			return false;
		}
	}
	
	/**********************
	---------Remove--------
	**********************/
	public function removeItem($ID){
		$query = $this->db->where(array('ID'=> $ID))
				->delete('comp_location_transfer_detail');
		$rows = $this->db->affected_rows();
		if($rows >= 1){
			return $rows;
		} else {
			return false;
		}		
	}
	
	
	public function remove($id){
		$query = $this->db->where('locationTransferID', $id)
						->delete('comp_location_transfer_detail');
		
		$query = $this->db->where('ID', $id)
						->delete('comp_location_transfer');		
		
		return	$this->db->affected_rows(); 
	}
	
	/****************************************
	----Report Location Transfer List--------
	****************************************/
	public function rptList($srtDate = '',$endDate = ''){
		if($srtDate != '' && $endDate != ''){
			$query = $this->db->select("clt.ID,CONCAT(clt.formNo,'[',lpad(clt.ID,8,0),']') as formNo,lf.code as fromLoc,lt.code as toLoc,mc.name as movType,clt.memo,DATE_FORMAT(clt.dateTransfer,'%d-%m-%Y') as dateTransfer,clt.updateable",false)
							->from('comp_location_transfer as clt')
							->join('tbl_location as lf','lf.fldid = clt.fromLocationID','left')
							->join('tbl_location as lt','lt.fldid = clt.toLocationID','left')
							->join('master_code as mc','mc.ID = clt.movementTypeID','left')
							->where(array('clt.companyID'=>element('compID', $this->session->userdata('logged_in')),'clt.createdTS >='=>$srtDate,'clt.createdTS <='=>$endDate))
							->get();
		}
		if($this->db->affected_rows()){
			return $query->result();
		}else{
			return false;
		}
	}
	
	//Report Detail
	public function rptDetail($id){
		$query = $this->db->select('CONCAT("[",i.itemCode,"]",i.name) as itemName,i.description,iltDet.fromStock as qtyOnHand,iltDet.quantity',false)
						->from('comp_location_transfer_detail as iltDet')
						->join('item as i','i.ID=iltDet.itemID','left')
						->where('locationTransferID',$id)
						->get();
		
		if($this->db->affected_rows()){
			return $query->result();
		}else{
			return false;
		}
	}
	
	//Transfer Direction
	public function transDirect($id){
		$query = $this->db->select('CONCAT("from ",lf.code," to ",lt.code) as locationTransfer,CONCAT(ilt.formNo,"[",lpad(ilt.ID,8,0),"]") as formNo,mc.name as movType,DATE_FORMAT(ilt.dateTransfer,"%d-%m-%Y") as dateTransfer',false)
						->from('comp_location_transfer as ilt')
						->join('tbl_location as lf','lf.fldid = ilt.fromLocationID','left')
						->join('tbl_location as lt','lt.fldid = ilt.toLocationID','left')
						->join('master_code as mc','mc.ID = ilt.movementTypeID','left')
						->where('ilt.ID',$id)
						->get();
		
		if($this->db->affected_rows()){
			return $query->result();
		}else{
			return false;
		}	
	}
}

