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

class PurchaseInvoice_list extends CI_Model{
	
	/*public function purchaseInvoice_all1($id){
		$sql='SELECT sup.supplierName, 
		sui.formNo,
		sui.invoiceDate, 
		sui.supplierInvoiceNo 
		FROM supplier sup, 
		comp_supplier_invoice sui 
		WHERE sup.ID = sui.supplierID 
		AND sui.companyID = '.$id.' 
		ORDER BY invoiceDate DESC';
		
		$query = $this->db->query($sql);

		if($query->num_rows() >=1){
			return $query->result();
		}else{
			return FALSE;
		}
	}*/
	
	public function purchaseInvoice_id($id){
		$query = $this->db->get_where('comp_supplier_invoice', array('ID'=>$id), 1);		
		if($query->num_rows() == 1){
			return $query->result();
		}else{
			return false;
		}
	}
	
	public function purchaseInvoice_all(){
		$this->db->where('comp_supplier_invoice.companyID', element('compID', $this->session->userdata('logged_in')));
		$this->db->select('comp_supplier_invoice.*');
                $this->db->select('concat(comp_supplier_invoice.formNo,"[",lpad(comp_supplier_invoice.serialNo,8,"0"),"]") as formNo', false);
		$this->db->select('supplier.supplierName as supplierName');
		$this->db->from('comp_supplier_invoice');
		$this->db->join('supplier', 'supplier.ID = comp_supplier_invoice.supplierID');
		$query = $this->db->get();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return false;
		}
	}
	public function purchaseInvoice_allOpen(){
		$this->db->where('a.totalPayable !=', 0);
		$this->db->where('a.companyID', element('compID', $this->session->userdata('logged_in')));
		$this->db->select('a.*');
                 $this->db->select('concat(a.formNo,"[",lpad(a.serialNo,8,"0"),"]") as formNo', false);
		$this->db->select('b.supplierName as supplierName');
		$this->db->select('b.phoneNumber1 as phoneNumber1');
		$this->db->select('b.attention as attention');
		$this->db->select('c.fldcurr_code');
		$this->db->from('comp_supplier_invoice a');
		$this->db->join('supplier b', 'b.ID = a.supplierID');
		$this->db->join('_ref_currency c', 'c.fldid = a.currencyID');	
		$query = $this->db->get();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return false;
		}
	}
	
	public function purchaseInvoiceDate($dateFrom, $dateTo){
		$this->db->where('comp_supplier_invoice.companyID', element('compID', $this->session->userdata('logged_in')));
		$this->db->where('comp_supplier_invoice.invoiceDate between  "'.$dateFrom.'" and "'.$dateTo.'"');
		$this->db->select('comp_supplier_invoice.*');
                 $this->db->select('concat(comp_supplier_invoice.formNo,"[",lpad(comp_supplier_invoice.serialNo,8,"0"),"]") as formNo', false);
		$this->db->select('supplier.supplierName as supplierName');
		$this->db->from('comp_supplier_invoice');
		$this->db->join('supplier', 'supplier.ID = comp_supplier_invoice.supplierID');
		$this->db->order_by('invoiceDate desc');
		$query = $this->db->get();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return false;
		}
	}
	
	public function purchaseInvoiceDate_totalPayable($dateFrom, $dateTo, $totalPayable){
		$this->db->where('comp_supplier_invoice.companyID', element('compID', $this->session->userdata('logged_in')));
		$this->db->where('comp_supplier_invoice.invoiceDate between  "'.$dateFrom.'" and "'.$dateTo.'"');
		if ($totalPayable == 1){
			$this->db->where('comp_supplier_invoice.totalPayable > 0');
		}
		else if($totalPayable == 2){
			$this->db->where('comp_supplier_invoice.totalPayable <= 0');
		}
		$this->db->select('comp_supplier_invoice.*');
		$this->db->select('concat(comp_supplier_invoice.formNo, "[", lpad(comp_supplier_invoice.serialNo,8,"0"), "]") as suppNo', false);
		$this->db->select('date_format(comp_supplier_invoice.invoiceDate,"%d-%m-%Y")  as InvDate ', false);
		$this->db->select('supplier.supplierName as supplierName');
		$this->db->from('comp_supplier_invoice');
		$this->db->join('supplier', 'supplier.ID = comp_supplier_invoice.supplierID');
		$this->db->order_by('invoiceDate desc');
		$query = $this->db->get();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return false;
		}
	}

	/*****Sayem (17th July, 2014)*****/
	public function purchaseInvoiceDetail($id){
		$query = $this->db->select('CONCAT("[",i.itemCode,"] ",i.name) as itemCodeName,i.description,siDet.quantity,siDet.unitPrice,siDet.amountExcludedTax,t.taxPercentage,siDet.amountIncludedTax,siDet.taxAmount',false)
						->from('comp_supplier_invoice_detail siDet')
						->join('item i','i.ID = siDet.itemID','left')
						->join('tax_master t','t.ID=siDet.taxID','left')
						->where(array('invoiceID'=>$id))
						->get();

		if($query->num_rows() >= 1){
			$data['detail'] = $query->result();
			$query = $this->db->select('si.*,s.*,CONCAT(si.formNo, "[", lpad(si.serialNo,8,"0"), "]") as invNo',false)
							->from('comp_supplier_invoice si')
							->join('supplier s','s.ID=si.supplierID','left')
							->where(array('si.ID' => $id))
							->get();
			$data['info'] = $query->result();
			return $data;
		}else{
			return false;
		}
	}
	/*****End purchaseInvoiceDetail***/
}