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

class purchaseInvoice_model extends CI_Model{
	
	/******************************************
	 *
	*  - Sayem
	*  start
	*
	*/
	//temporary
	/*public function setPurchaseOrder(){
		$query = $this->db->select("po.ID,CONCAT(po.formNo,'-',lpad(po.ID,8,0)) as fromNo",false)
				->from('comp_purchase_order po')
				->get();
		foreach($query->result() as $row){
			$this->db->where("ID",$row->ID)->update('comp_purchase_order',array('purchaseOrderNo'=>$row->fromNo));	
		}
	}*/
	
	//Gather supplier wise purchase items
	public function getOrderItems($suppID,$date){
		$query = $this->db->select("riDet.*,ri.ID as rcvID,CONCAT(ri.formNo,'-',lpad(ri.serialNo,8,0)) as formNo,i.ID as itemID,i.name,i.description,i.outputTaxID,t.taxPercentage",false)
							->from('comp_receive_item ri')
							->join('comp_receive_item_detail riDet','riDet.receiveID = ri.ID','left')
							->join('item i','i.ID=riDet.itemID','left')
							->join('tax_master t','t.ID=i.outputTaxID','left')
							->where(array('riDet.invoiceID'=>0,'ri.companyID'=>element('compID', $this->session->userdata('logged_in')),'ri.supplierID'=>$suppID,'deliveryDate <='=>$date))
							->get();
		//echo $this->db->last_query();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return false;
		}
	}
	
	//Gather supplier wise purchase orders
	public function getOrder($custID){
		$query = $this->db->select("po.purchaseOrderNo,po.ID",false)
						->from('comp_purchase_order po')
						->where(array('companyID'=>element('compID', $this->session->userdata('logged_in')),'supplierID'=>$custID,'invoiceID'=>0))
						->get();
	
		//["321000100-A12 Wooden Chair", "331000100-A23 Plastic Chair", "331000200-A24 Plastic Chair", "331000300-A25 Plastic Chair"]
		if($query->num_rows() >= 1){
			$str = "";
			foreach($query->result() as $row){
				echo "<option value=".$row->ID.">".$row->purchaseOrderNo."</option>";
			}
			return $str;
		}else{
			return "";
		}
	}
	//Gathering order detail from purchase order table
	public function getOrderByID($ordID){
		$query = $this->db->select("po.*,DATE_FORMAT(po.poDate,'%d-%m-%Y') as purchaseOrderDate,t.termDescription",false)
						->from('comp_purchase_order po')
						->join('terms t','t.ID=po.termAndConditionID','left')
						//->join('comp_address a','a.ID = so.deliveryToID','left')
						->where(array('po.ID'=>$ordID))
						->get();
		 
		if($query->num_rows() >= 1){
			$data["info"] = $query->result();
			$query = $this->db->select("poDet.*,i.ID as itemID,i.name,i.description,i.outputTaxID,t.taxPercentage")
							->from('comp_purchase_order_detail poDet')
							->join('item i','i.ID=poDet.itemID')
							->join('tax_master t','t.ID=i.outputTaxID')
							->where(array('purchaseOrderID'=>$ordID))
							->get();
			$data['ordDet'] = $query->result();
			return $data;
		}else{
			return false;
		}
	}
	
	//insert table data
	public function insert($tname, $newData){
		$sql = $this->db->insert_string($tname, $newData);
		$query = $this->db->query($sql);
		if($query === TRUE){
			return $this->db->insert_id();
		} else {
			return 0;
		}
	}	
	
	//Update Purchase invoice detail
	public function updatePurchaseInvDet($invID,$invDetail){
		$arr = json_decode($invDetail);
		if(isset($arr) && !empty($arr)){
			foreach($arr as $v){
				$data = array(
						"invoiceID"=>$invID,
						"lineNo"=>$v->lineNo,
						"receiveID"=>$v->receiveID,
						"itemID"=>$v->itemID,
						"itemname"=>$v->itemname,
						"itemdescription"=>$v->itemdescription,
						"quantity"=>$v->quantity,
						"unitPrice"=>$v->unitPrice,
						"amountExcludedTax"=>$v->amountExcludedTax,
						"taxID"=>$v->taxID,
						"taxAmount"=>$v->taxAmount,
						"amountIncludedTax"=>$v->amountIncludedTax,
						"createdBy"=>element('role', $this->session->userdata('logged_in'))
				);
				$this->db->insert('comp_supplier_invoice_detail', $data);
				$this->db->where(array('receiveID'=>$v->receiveID,'itemID'=>$v->itemID))->update('comp_receive_item_detail',array('invoiceID'=>$invID));		
			}
		}
	}
	
	//Update invoice information
	public function updateInvoice($invID,$data){
		$this->db->where('ID',$invID)->update('comp_supplier_invoice',$data);
		if($this->db->affected_rows() >= 1 ){
			return true;
		}else{
			return false;
		}
	}
	
	//update Purchase order table
	public function updatePurchaseOrd($id,$invID){
		$this->db->where('ID',$id)->update('comp_purchase_order',array('invoiceID'=>$invID));
		if($this->db->affected_rows() >= 1 ){
			return true;
		}else{
			return false;
		}
	}
	/*****************************************
	 *
	 *  - Sayem
	 *  end
	 *
	 */
}

?>