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

class Purchase_model extends CI_Model{
	
	
	
	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 {
	//		$last_query = $this->db->last_query();
	//		return $last_query;
			return 0;
		}
	}
	
	
	// is used in items received
	public function getPoList ($supID,$companyID){

		if ($supID!="null")
			$sqlWhere = ' and supplierID ='.$supID;
		else $sqlWhere = '';
		//problem : ci treat '[' as a field
/*		 $this->db->where('supplierID',$supID); 
		if ($companyID > 0)
		    $this->db->where('companyID',$companyID); 
		$this->db->select("concat(formNo,'[' ,lpad(ID,8,'0' ),']' ) as formNo");
		$this->db->select('ID');
		$query = $this->db->get('comp_purchase_order');
		*/
		$sql ="select DISTINCT comp_purchase_order.ID, concat(comp_purchase_order.formNo,'[' ,lpad(comp_purchase_order.serialNo,8,'0' ),']' ) as formNo ";
		$sql .= ' from comp_purchase_order, comp_purchase_order_detail';
		$sql .= ' where comp_purchase_order.ID = comp_purchase_order_detail.purchaseOrderID And  		
		 comp_purchase_order_detail.quantityReceivedTotal < comp_purchase_order_detail.quantityOrder AND   
		  comp_purchase_order.companyID='.$companyID;
		$sql .= $sqlWhere; 
		$query = $this->db->query($sql);
		if($query->num_rows() >0 ){
			return $query->result();
		}else{
			return false;
		}
	}
	
	public function getPoItemList($idPo){
		$sql='SELECT comp_purchase_order_detail.*,  comp_purchase_order_detail.ID AS poDetailID, item.name as itemName, item.ID as itemID, item.itemCode as itemCodee 
			FROM comp_purchase_order_detail
			LEFT JOIN item
			ON comp_purchase_order_detail.itemID=item.ID
			WHERE comp_purchase_order_detail.quantityReceivedTotal<comp_purchase_order_detail.quantityOrder 
				AND comp_purchase_order_detail.purchaseOrderID ='.$idPo;
			$query = $this->db->query($sql);
			
		if($query->num_rows() >=1){
			$res = $query->result();
				//for ajax call 
			$JsonRecords = '{"records":[';
			
			$n = sizeof($res);
			$i= 0;
			 foreach($res as $row){
				$JsonRecords .= '{';
				$JsonRecords .= '"lineNo":"'.$row->lineNo.'","itemId":"' . $row->itemID . '","poDetailId":"'.$row->poDetailID.
				'", "itemCode":"'. $row->itemCodee.'","itemName":"'. $row->itemName.'","description":"'. $row->description
				.'", "unitPrice":"'.$row->unitPrice.'","quantityReceivedTotal":"'.$row->quantityReceivedTotal.'","quantityOrder":"'
				.$row->quantityOrder.'"';
				$JsonRecords .= '}';
				if ($i < $n - 1)
					$JsonRecords .= ',';
				$i++;
			}
			$JsonRecords .= ']}';
			
			//$resul= '{"supplier":['. json_encode($this->supplier_model->suppliermaster_all()).']}';
			 return $JsonRecords;
		}else{
			return FALSE;
		}
	}
	
	
	public function get_Detail_byID($id){
		$sql='SELECT comp_purchase_order_detail.*, 
                       comp_purchase_order_detail.unitPrice*quantityOrder as total, item.id as itemid, item.name, item.itemCode as itemCode 
			FROM comp_purchase_order_detail
			LEFT JOIN item
			ON comp_purchase_order_detail.itemID=item.ID
			where purchaseOrderID ='.$id;
			$query = $this->db->query($sql);

		if($query->num_rows() >=1){
			return $query->result();
		}else{
			return FALSE;
		}
	}
	
	public function get_byID($tname, $id){
		$sql ="";
		switch ($tname) {
    		case "comp_purchase_order":
       			 $sql='SELECT comp_purchase_order.*, comp_purchase_order.ID as poID, CONCAT(comp_purchase_order.formNo, "[", lpad(comp_purchase_order.serialNo,8,"0"), "]")as formNo,  supplier.supplierName,'
                        . ' terms.termName, terms.termDescription, _ref_currency.fldcurr_code as currencyWord, comp_employee.employeeName'
                        . '   FROM (comp_purchase_order left outer join terms on comp_purchase_order.termAndConditionID = terms.ID) '
                        . '   Left outer Join comp_employee ON comp_employee.ID = comp_purchase_order.purchaserID,'
                        . ' _ref_currency , supplier where comp_purchase_order.updateAble = 1 AND supplier.ID=comp_purchase_order.supplierID '
                        . ' AND _ref_currency.fldid=supplier.currencyID AND comp_purchase_order.ID =  '.$id .
                            ' AND comp_purchase_order.companyID='.element('compID', $this->session->userdata('logged_in'));
      		 	 break;
  	 		
		   }
		
		$query = $this->db->query($sql);

		if($query->num_rows() >0){
			return $query->result();
		}else{
			return FALSE;
		}

	}
	
        public function deleteByTame($tname,$id){
		$this->db->where('ID', $id);
		$query = $this->db->delete($tname); 
		if($query === TRUE){
			return TRUE;
		} else {
			return FALSE;
		}
	}
		
	public function delete($id){
		$this->db->where('ID', $id);
		$query = $this->db->delete("comp_purchase_order_detail"); 
		if($query === TRUE){
			return TRUE;
		} else {
			return FALSE;
		}
	}
	
	public function updateByTname ($id, $data, $tname){
		$this->db->where('ID', $id);
		$query = $this->db->update($tname, $data); 
		if($query === TRUE){
			return TRUE;
		} else {
			return FALSE;
		}
	}
	
	public function updatePoDetail($id, $data){
		$this->db->where('ID', $id);
		$query = $this->db->update('comp_purchase_order_detail', $data); 
		if($query === TRUE){
			return TRUE;
		} else {
			return FALSE;
		}
	}
	
	public function update($id, $data){
		$this->db->where('ID', $id);
		$query = $this->db->update('comp_purchase_order_detail', $data); 
		if($query === TRUE){
			return TRUE;
		} else {
			return FALSE;
		}
	}
	
	
	public function  getLasIdInsert($tname){
		$sql='SELECT LAST_INSERT_ID() as myId from '.$tname;
		$query = $this->db->query($sql);
		$return = NULL;
 
		if($query->num_rows() >= 1){
			foreach($query->result() as $row)
				$return =  $row->myId;
		}
		
		return $return;
	}

	/*public function purchaseOrder_all(){
		$this->db->where('comp_purchase_order.companyID', element('compID', $this->session->userdata('logged_in')));
		$this->db->select('comp_purchase_order.*');
		$this->db->select('supplier.supplierName as supplierName');
		$this->db->from('comp_purchase_order');
		$this->db->join('supplier', 'supplier.ID = comp_purchase_order.supplierID');
		$query = $this->db->get();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return false;
		}
	}*/
	
	public function updatePosting($ID){
		$this->db->where('ID', $ID);
		$updatepayment = array('updateAble'=>0);
		$query = $this->db->update('comp_purchase_order', $updatepayment); 
		if($query){
			return TRUE;
		}else{
			return FALSE;
		}
		
	}
	
	public function getPoItemTotal($id){
		$sql='SELECT quantityReceivedTotal
			From comp_purchase_order_detail where  ID='.$id;
      		 
		$query = $this->db->query($sql);

		if($query->num_rows() >0){
			$r = $query->result();
			$val ="null";
			 foreach ($r as $row)
				$val= $row->quantityReceivedTotal;
			return $val;
		}else{
			return FALSE;
		}
	} 
	
	 public function purchaseOrderPrintDetails_ID($ID){
		$this->db->select('comp_purchase_order_detail.*');
		$this->db->select('concat("[",item.itemCode,"] ",item.name) as item', false);
		$this->db->where('purchaseOrderID', $ID);
		$this->db->from('comp_purchase_order_detail');		
		$this->db->join('item','item.ID = comp_purchase_order_detail.itemID');
		$query = $this->db->get();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return false;
		}
	 }
	 
	  public function purchaseOrder_id($ID){
		$this->db->select('a.*');
		$this->db->select('b.*');
		$this->db->select('concat(a.formNo,"[",lpad(a.serialNo,8,"0"),"]") as purchaseOrderNo', false);
		$this->db->where('a.ID', $ID);
		$this->db->where('a.companyID', element('compID', $this->session->userdata('logged_in')));
		$this->db->from('comp_purchase_order a');
		$this->db->join('supplier b', 'b.ID = a.supplierID');
		$query = $this->db->get();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return false;
		}
	}
	
	public function getReceiveDetail ($id){
			$sql='SELECT  item.name , item.itemcode as itemCodee, comp_receive_item_detail.*, 
                                comp_receive_item_detail.id as myid, item.id as itemid
			From comp_receive_item_detail left outer join item on item.ID = comp_receive_item_detail.itemID where  receiveID='.$id;
      		 
		$query = $this->db->query($sql);

		if($query->num_rows() >0){
			return $query->result();
		}else{
			return FALSE;
		}
	}
	
	public function getReceiveByID($id) {
		$sql='SELECT comp_receive_item.ID AS myid,comp_receive_item.memo, comp_employee.employeeName , supplier.supplierName, comp_receive_item.formNo,
                          CONCAT(comp_purchase_order.formNo, "[", lpad(comp_purchase_order.serialNo,8,"0"), "]") AS poFormNo, comp_purchase_order.ID as PoNO, tbl_location.state, tbl_location.address, 
                        comp_purchase_order.exchangeRate,  CONCAT(comp_receive_item.formNo, "[", lpad(comp_receive_item.serialNo,8,"0"), "]" ) AS reFormNo,
                        deliveryDate, supplierDoNo, master_code.code as shipping,_ref_currency.fldcurr_code as currencyWord
                    FROM ((((comp_receive_item left outer join comp_employee on comp_receive_item.employeeID=comp_employee.ID)
                         LEFT OUTER JOIN tbl_location on comp_receive_item.locationID=tbl_location.fldid)
                         left outer join master_code on master_code.ID = comp_receive_item.shippingMethodID)
                         LEFT OUTER JOIN _ref_currency ON _ref_currency.fldid = comp_receive_item.currencyID),
                          comp_purchase_order  left outer join supplier on supplier.ID=comp_purchase_order.supplierID
		 WHERE comp_purchase_order.ID= comp_receive_item.purchaseOrderID AND comp_receive_item.ID =  '.$id;
      		 
		
		$query = $this->db->query($sql);

		if($query->num_rows() >0){
			return $query->result();
		}else{
			return FALSE;
		}

	}
        
	
	public function purchaseOrderDate($dateFrom, $dateTo,$updateable=0){
		$this->db->where('comp_purchase_order.companyID', element('compID', $this->session->userdata('logged_in')));
		$this->db->where('poDate between  "'.$dateFrom.'" and "'.$dateTo.'"');
                $this->db->select('concat(comp_purchase_order.formNo,"[",lpad(comp_purchase_order.serialNo,8,"0"),"]") as formNo', false);
		$this->db->select('concat(comp_purchase_order.formNo,"[",lpad(comp_purchase_order.serialNo,8,"0"),"]") as purchaseNo', false);
		
                $this->db->select('date_format(comp_purchase_order.poDate, "%d-%m-%Y") as poDates', false);
		$this->db->where('comp_purchase_order.updateAble', 1);
		$this->db->select('if(comp_purchase_order.updateable=1,"No","Yes") as confirm ', false);
		$this->db->select('comp_purchase_order.*');
                $this->db->select('concat(comp_purchase_order.formNo,"[",lpad(comp_purchase_order.serialNo,8,"0"),"]") as formNo', false);
		
		
		$this->db->from('comp_purchase_order');
		$this->db->select('supplier.supplierName as supplierName');
		$this->db->join('supplier', 'supplier.ID = comp_purchase_order.supplierID');
		$this->db->order_by('poDate desc');
		$query = $this->db->get();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return false;
		}
	}
	
	public function receiveItemDate($dateFrom, $dateTo){
		$this->db->where('comp_receive_item.companyID', element('compID', $this->session->userdata('logged_in')));
		$this->db->where('deliveryDate between  "'.$dateFrom.'" and "'.$dateTo.'"');
		$this->db->where('updateAble', 1);
		$this->db->select('comp_receive_item.*');
		$this->db->from('comp_receive_item');
		$this->db->select('supplier.supplierName as supplierName');
		$this->db->join('supplier', 'supplier.ID = comp_receive_item.supplierID');
		$this->db->order_by('deliveryDate desc');
		$query = $this->db->get();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return false;
		}
	}
	// yahaya abdollah
	// Edited by Sayem (formNo with ID) 5-8-2014 
	// received unpost
	public function receiveItemDate_unpost($dateFrom, $dateTo){
		$this->db->where('ri.companyID', element('compID', $this->session->userdata('logged_in')));
		$this->db->where('ri.deliveryDate between  "'.$dateFrom.'" and "'.$dateTo.'"');
		$this->db->where('ri.updateAble', 1);
		$this->db->select('ri.*,CONCAT(ri.formNo," [",lpad(ri.ID,8,"0"),"]") as formNo',false);
		$this->db->from('comp_receive_item ri');
		$this->db->select('supplier.supplierName as supplierName');
		$this->db->join('supplier', 'supplier.ID = ri.supplierID');
		$this->db->order_by('ri.deliveryDate desc');
		$query = $this->db->get();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return false;
		}
	}
	public function purchaseReceiveDetails_ID($receiveID){
		$this->db->select('a.*');
		$this->db->where('a.receiveID', $receiveID);
		$this->db->order_by('a.receiveID');
		$this->db->from('view_unpost_receive_item a');
		$query = $this->db->get();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return FALSE;
		}
	}
	public function purchaseReceiveDetails_ID1($receiveID){
		$this->db->select('a.*');
		$this->db->select('b.itemCode');
		$this->db->select('b.description');
		$this->db->select('c.code');
		$this->db->select('c.inventoriesID as accountID');
		$this->db->where('a.receiveID', $receiveID);
		$this->db->order_by('a.itemID');
		$this->db->from('comp_receive_item_detail a');
		$this->db->join('item b', 'b.ID = a.itemID');	
		$this->db->join('_sys_um c', 'c.fldid = a.umID');	
		$query = $this->db->get();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return FALSE;
		}
	}
	
	 public function purchaseReceivePost($id, $data){
		$this->db->where('ID', $id);
//		$this->db->where('updateAble', 1);
		$query = $this->db->update('comp_receive_item', $data);
		if($query){
			$this->db->flush_cache();
			$this->db->where('ID', $id);
			$this->db->from('comp_receive_item');
			$query1 = $this->db->get();
			return $query1->result();
		}else{
			return false;
		}
	 }
	public function purchaseReceive_Update($ID, $data){
		$this->db->where('ID', $ID);
		$query = $this->db->update('comp_receive_item', $data); 
		if($query === TRUE){
			return $this->db->last_query();
		} else {
			return FALSE;
		}
	}
	
	 public function purchaseReceiveDetails_receiveID($journalID, $header, $detailsData, $year, $period){
		//foreadh item received pls update quantity in item_details table -akmal
		if($detailsData){
			$seq = 0;
			$journalAmount = 0;
			foreach($detailsData as $row):
				$seq++;
				$exchangeRate = ($row->exchangeRate == 0)? 1:$row->exchangeRate;
				$amountLine = $row->amount * $exchangeRate;
				$newData = array(
					'companyID' => element('compID', $this->session->userdata('logged_in')),
					'journalID' => $journalID,
					'sequence' => $seq,
					'description' => $row->itemName,
					'acctCode' => $row->crAccountID,
					'amount_cr' => $amountLine,
					'currencyID' => ($header->currencyID == 0)? 1: $header->currencyID,
					'exchange_rate' => $exchangeRate,
					'currency_amount' => $row->amount,
					'period' => $period,
					'year' => $year,
					'line' => $row->lineNo,
					'formNo' => $row->formNo,
					'createdBy' => element('role', $this->session->userdata('logged_in'))
				);
				$queryInsert = $this->db->insert('comp_journal_detail', $newData);
				$seq++;
				$newData = array(
					'companyID' => element('compID', $this->session->userdata('logged_in')),
					'sequence' => $seq,
					'journalID' => $journalID,
					'description' => $row->itemName,
					'acctCode' => $row->drAccountID,
					'amount_dr' => $amountLine,
					'currencyID' => ($header->currencyID == 0)? 1: $header->currencyID,
					'exchange_rate' => $exchangeRate,
					'currency_amount' => $row->amount,
					'period' => $period,
					'year' => $year,
					'formNo' => $header->formNo . '[' . sprintf('%08d', $header->ID) . ']',
					'line' => $row->lineNo,
					'createdBy' => element('role', $this->session->userdata('logged_in'))
				);
				$queryInsert = $this->db->insert('comp_journal_detail', $newData);
				$journalAmount = $journalAmount + $amountLine;
			endforeach;
			if($journalAmount != 0){
				$data = array(
					'total_amount_dr'=>$journalAmount,
					'total_amount_cr'=>$journalAmount,
					'total_amount'=>0
				);
				$this->db->where('companyID', element('compID', $this->session->userdata('logged_in')));
				$this->db->where('journalID', $journalID);
				$queryJournal = $this->db->update('comp_journal', $data); 
			}
		}else{
			return false;
		}
	 }
	public function rptGetPaymentAdviceDetail($id){
		$sql='SELECT comp_payment_detail.amountApplied, comp_supplier_invoice.supplierInvoiceNo,
					comp_supplier_invoice.invoiceDate
			  FROM comp_payment_detail,comp_supplier_invoice
			  WHERE comp_payment_detail.purchaseInvoiceID = comp_supplier_invoice.ID AND
					  comp_payment_detail.paymentID ='.$id;
      		 
		$query = $this->db->query($sql);

		if($query->num_rows() >0){
			return $query->result();
		}else{
			return FALSE;
		}

	}
	
	
 	public function rptGetPaymentAdvice($id){
		$sql='SELECT supplier.supplierName, supplier.line1, supplier.line2, supplier.line3, supplier.supplierPostCode, comp_payment.ID,
		              supplier.city,_ref_state.fldname, _ref_country.fldcountry, comp_payment.amountPaid, master.name AS pMethod, 
					  comp_payment.paymentDate, comp_payment.referenceNo,_ref_currency.fldcurr_code as currencyCode, comp_payment.exchangeRate, comp_payment.formNo
			  FROM comp_payment LEFT OUTER JOIN master ON comp_payment.paymentMethodID=master.ID, (((Supplier LEFT OUTER JOIN _ref_state ON         
			  		Supplier.supplierStateID=_ref_state.fldid) 
			        LEFT OUTER JOIN _ref_country ON Supplier.supplierCountryID=_ref_country.fldid) 
					LEFT OUTER JOIN _ref_currency ON _ref_currency.fldid = Supplier.currencyID)
			  WHERE Supplier.ID=comp_payment.supplierID  
			  		AND comp_payment.ID ='.$id.' LIMIT 1';
      		 
		$query = $this->db->query($sql);

		if($query->num_rows() >0){
			return $query->result();
		}else{
			return FALSE;
		}

	}
	 public function receiveItemPrintDetails_ID($ID){
//		$this->db->select('*');
//		$this->db->select('concat(formNo,"[",lpad(ID,8,"0"),"]") as purchaseOrderNo', false);
		$this->db->select('a.*');
		$this->db->select('concat("[",b.itemCode,"] ",b.name) as item', false);
		$this->db->where('a.receiveID', $ID);
		$this->db->from('comp_receive_item_detail a');
		$this->db->join('item b', 'b.ID = a.itemID');
		$query = $this->db->get();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return false;
		}
	 }
	 
	  public function receiveItem_id($ID){
		$this->db->select('a.*');
		$this->db->select('b.*');
		$this->db->select('concat(a.formNo,"[",lpad(a.serialNo,8,"0"),"]") as purchaseOrderNo', false);
		$this->db->select('concat(c.formNo,"[",lpad(c.serialNo,8,"0"),"]") as purchaseFormNo', false);
		$this->db->where('a.ID', $ID);
		$this->db->where('a.purchaseOrderID', 'c.ID', false);
		$this->db->where('b.ID', 'a.supplierID', false);
		$this->db->where('a.companyID', element('compID', $this->session->userdata('logged_in')));
		$this->db->from('comp_receive_item a');
		$this->db->from('supplier b');
		$this->db->from('comp_purchase_order c');
		$query = $this->db->get();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return false;
		}
	}
	
} // end