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

class SalesInvoice_model extends CI_Model{
	
	
	public function salesInvoice_all(){
		$this->db->where('comp_sales_invoice.companyID', element('compID', $this->session->userdata('logged_in')));
		$this->db->select('comp_sales_invoice.*');
		$this->db->select('customer.customerName as customerName');
		$this->db->from('comp_sales_invoice');
		$this->db->join('customer', 'customer.ID = comp_sales_invoice.customerID');
		$query = $this->db->get();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return false;
		}
	}
	
	
	
	//Sales Invoice
	public function salesInvoice_Date($dateFrom, $dateTo){
		$sql1 = "SELECT comp_sales_invoice.*, if (comp_sales_invoice.updateAble=1, 'Yes', 'No' ) as confirm, customer.customerName as customerName, date_format(comp_sales_invoice.salesInvoiceDate, '%d-%m-%Y') as invoiceDate";
		$sql2 = " FROM (comp_sales_invoice JOIN customer ON customer.ID = comp_sales_invoice.customerID)";
		$sql3 = " WHERE comp_sales_invoice.companyID = ".element('compID', $this->session->userdata('logged_in'))." AND comp_sales_invoice.salesInvoiceDate between '".$dateFrom."' and '".$dateTo."'";
		$sql4 = " ORDER BY salesInvoiceDate desc"; 
		$query = $this->db->query($sql1.$sql2.$sql3.$sql4);
		//echo $this->db->last_query();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return false;
		}
	}
	
	public function salesInvoice_id($ID){
		$this->db->select('a.*');
		$this->db->select('b.*');
		$this->db->select('c.fldcurr_code');
		$this->db->select('d.termName');
		$this->db->select('concat(a.formNo,"[",lpad(a.ID,8,"0"),"]") as salesInvoiceNo', false);
		$this->db->where('a.ID', $ID);
		$this->db->where('a.companyID', element('compID', $this->session->userdata('logged_in')));
		$this->db->from('comp_sales_invoice a');
		$this->db->join('customer b', 'b.ID = a.customerID');
		$this->db->join('_ref_currency c', 'c.fldid = a.currencyID', 'left outer');
		$this->db->join('terms d', 'd.ID = a.termsID');
		$query = $this->db->get();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return false;
		}
	}
	
	public function salesInvoiceDetails_ID($ID){
		$this->db->select('a.*');
		$this->db->select('c.companyID');
		$this->db->where('a.ID', $ID);
		/*$this->db->select('b.quantity');
		$this->db->select('b.unitPrice');
		$this->db->select('b.amountExcludedTax');
		$this->db->select('b.amountIncludedTax');
		$this->db->select('b.taxRate');
		$this->db->select('b.taxAmount');*/
		$this->db->select('b.*');
		/*$this->db->select('c.description');
		$this->db->select('c.quantity');*/
		$this->db->where('c.companyID', element('compID', $this->session->userdata('logged_in')));
		$this->db->from('comp_sales_invoice_detail a');
		$this->db->join('comp_sales_invoice c', 'c.ID = a.invoiceID');
		$this->db->join('item b', 'b.ID = a.itemID');
		//$this->db->join('item c', 'c.ID = b.itemID');
		$query = $this->db->get();
		//echo $this->db->last_query();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return false;
		}
	 }
	 
	 public function salesInvoiceCharges_ID($ID){
		$this->db->select('a.*');
		$this->db->where('a.ID', $ID);
		$this->db->select('b.*');
		$this->db->select('concat("[",c.acctCode,"]",c.acctName) as acctNameCode', false);
		$this->db->where('a.companyID', element('compID', $this->session->userdata('logged_in')));
		$this->db->from('comp_sales_invoice a');
		$this->db->join('comp_sales_invoice_charges b', 'b.invoiceID = a.ID');
		$this->db->join('comp_chart_of_acct c', 'c.ID = b.accountNoID');
		$query = $this->db->get();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return false;
		}
	 }
	
	//Sales Invoice Update Posting
	public function updatePosting($ID){
		$this->db->where('ID', $ID);
		$updatepayment = array('updateAble'=>0);
		$query = $this->db->update('comp_sales_invoice', $updatepayment); 
		if($query){
			return TRUE;
		}else{
			return FALSE;
		}
		
	}
	//Debit Note
	public function salesdebitnote_Date($dateFrom, $dateTo){
		$sql1 = "SELECT comp_sales_debitnote.*, if (comp_sales_debitnote.updateAble=1, 'Yes', 'No' ) as confirm, customer.customerName as customerName, date_format(comp_sales_debitnote.debitNoteDate, '%d-%m-%Y') as debitDate, concat(comp_sales_debitnote.formNo,'[',lpad(comp_sales_debitnote.ID,8,'0'),']') as debitNoteNo";
		$sql1 .= ",concat(comp_sales_invoice.formNo,'[',lpad(comp_sales_invoice.ID,8,'0'),']') as invoiceNo";
		$sql2 = " FROM (comp_sales_debitnote JOIN customer ON customer.ID = comp_sales_debitnote.customerID)";
		$sql2 .= " join comp_sales_invoice on comp_sales_invoice.ID = comp_sales_debitnote.salesInvoiceID";
		$sql3 = " WHERE comp_sales_debitnote.companyID = ".element('compID', $this->session->userdata('logged_in'))." AND comp_sales_debitnote.debitNoteDate between '".$dateFrom."' and '".$dateTo."'";
		$sql4 = " ORDER BY debitNoteDate desc"; 
		$query = $this->db->query($sql1.$sql2.$sql3.$sql4);
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return false;
		}
	}
	
	public function salesdebitnote_id($ID){
		$this->db->select('a.*');
		$this->db->select('b.*');
		$this->db->select('c.fldcurr_code');
		$this->db->select('d.termName');
		$this->db->select('concat(a.formNo,"[",lpad(a.ID,8,"0"),"]") as debitNoteNo', false);
		$this->db->where('a.ID', $ID);
		$this->db->where('a.companyID', element('compID', $this->session->userdata('logged_in')));
		$this->db->from('comp_sales_debitnote a');
		$this->db->join('customer b', 'b.ID = a.customerID');
		$this->db->join('_ref_currency c', 'c.fldid = a.currencyID', 'left outer');
		$this->db->join('terms d', 'd.ID = a.termsID', 'left outer');
		$query = $this->db->get();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return false;
		}
	}
	
	public function salesdebitnoteDetails_id($ID){
		$this->db->select('a.*');
		$this->db->where('a.ID', $ID);
		/*$this->db->select('b.quantity');
		$this->db->select('b.unitPrice');
		$this->db->select('b.amountExcludedTax');
		$this->db->select('b.amountIncludedTax');
		$this->db->select('b.taxRate');
		$this->db->select('b.taxAmount');*/
		$this->db->select('b.*,CONCAT("[",b.itemCode,"] ",b.name) as itemNameCode',false);
		$this->db->select('c.companyID');
		/*$this->db->select('c.description');
		$this->db->select('c.quantity');*/
		$this->db->where('c.companyID', element('compID', $this->session->userdata('logged_in')));
		$this->db->from('comp_sales_debitnote_detail a');
		$this->db->join('comp_sales_debitnote c', 'c.ID = a.debitnoteID');
		$this->db->join('item b', 'b.ID = a.itemID');
		//$this->db->join('item c', 'c.ID = b.itemID');
		$query = $this->db->get();
		//echo $this->db->last_query();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return false;
		}
	 }
	 
	 public function salesdebitnoteCharges_id($ID){
		$this->db->select('a.*');
		$this->db->where('a.ID', $ID);
		$this->db->select('b.*');
		$this->db->select('concat("[",c.acctCode,"]",c.acctName) as acctNameCode', false);
		$this->db->where('a.companyID', element('compID', $this->session->userdata('logged_in')));
		$this->db->from('comp_sales_debitnote a');
		$this->db->join('comp_sales_debitnote_charges b', 'b.debitNoteID = a.ID');
		$this->db->join('comp_chart_of_acct c', 'c.ID = b.accountNoID');
		$query = $this->db->get();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return false;
		}
	 }
	
	//Credit Note
	public function salescreditnote_Date($dateFrom, $dateTo){
		$sql1 = "SELECT comp_sales_creditnote.*, if (comp_sales_creditnote.updateAble=1, 'Yes', 'No' ) as confirm, customer.customerName as customerName, date_format(comp_sales_creditnote.creditNoteDate, '%d-%m-%Y') as creditDate, concat(comp_sales_creditnote.formNo,'[',lpad(comp_sales_creditnote.ID,8,'0'),']') as creditNoteNo";
		$sql1 .= ",concat(comp_sales_invoice.formNo,'[',lpad(comp_sales_invoice.ID,8,'0'),']') as invoiceNo";
		$sql2 = " FROM (comp_sales_creditnote JOIN customer ON customer.ID = comp_sales_creditnote.customerID)";
		$sql2 .= " join comp_sales_invoice on comp_sales_invoice.ID = comp_sales_creditnote.salesInvoiceID";
		$sql3 = " WHERE comp_sales_creditnote.companyID = ".element('compID', $this->session->userdata('logged_in'))." AND comp_sales_creditnote.creditNoteDate between '".$dateFrom."' and '".$dateTo."'";
		$sql4 = " ORDER BY creditNoteDate desc"; 
		$query = $this->db->query($sql1.$sql2.$sql3.$sql4);
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return false;
		}
	}
	
	public function salescreditnote_id($ID){
		$this->db->select('a.*');
		$this->db->select('b.*');
		$this->db->select('c.fldcurr_code');
		$this->db->select('d.termName');
		$this->db->select('concat(a.formNo,"[",lpad(a.ID,8,"0"),"]") as creditNoteNo', false);
		$this->db->where('a.ID', $ID);
		$this->db->where('a.companyID', element('compID', $this->session->userdata('logged_in')));
		$this->db->from('comp_sales_creditnote a');
		$this->db->join('customer b', 'b.ID = a.customerID');
		$this->db->join('_ref_currency c', 'c.fldid = a.currencyID', 'left outer');
		$this->db->join('terms d', 'd.ID = a.termsID', 'left outer');
		$query = $this->db->get();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return false;
		}
	}
	
	public function salescreditnoteDetails_id($ID){
		$this->db->select('a.*');
		$this->db->where('a.ID', $ID);
		/*$this->db->select('b.quantity');
		$this->db->select('b.unitPrice');
		$this->db->select('b.amountExcludedTax');
		$this->db->select('b.amountIncludedTax');
		$this->db->select('b.taxRate');
		$this->db->select('b.taxAmount');*/
		$this->db->select('b.*,CONCAT("[",b.itemCode,"] ",b.name) as itemNameCode',false);
		/*$this->db->select('c.description');
		$this->db->select('c.quantity');*/
		//$this->db->where('a.companyID', element('compID', $this->session->userdata('logged_in')));
		$this->db->from('comp_sales_creditnote_detail a');
		$this->db->join('item b', 'b.ID = a.itemID');
		//$this->db->join('item c', 'c.ID = b.itemID');
		$query = $this->db->get();
		//echo $this->db->last_query();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return false;
		}
	 }
	 
	 public function salescreditnoteCharges_id($ID){
		$this->db->select('a.*');
		$this->db->where('a.ID', $ID);
		$this->db->select('b.*');
		$this->db->select('concat("[",c.acctCode,"]",c.acctName) as acctNameCode', false);
		$this->db->where('a.companyID', element('compID', $this->session->userdata('logged_in')));
		$this->db->from('comp_sales_creditnote a');
		$this->db->join('comp_sales_creditnote_charges b', 'b.creditNoteID = a.ID');
		$this->db->join('comp_chart_of_acct c', 'c.ID = b.accountNoID');
		$query = $this->db->get();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return false;
		}
	 }
	
	//Receive Payment
	public function rcvpayment_Date($dateFrom, $dateTo){
		$sql1 = "SELECT comp_receive_payment.*, if (comp_receive_payment.updateAble=1, 'Yes', 'No' ) as confirm, customer.customerName as customerName, comp_receive_payment.receivePaymentDate";
		$sql2 = " FROM (comp_receive_payment JOIN customer ON customer.ID = comp_receive_payment.customerID)";
		$sql3 = " WHERE comp_receive_payment.companyID = '1' AND comp_receive_payment.receivePaymentDate between '".$dateFrom."' and '".$dateTo."'";
		$sql4 = " ORDER BY receivePaymentDate desc"; 
		$query = $this->db->query($sql1.$sql2.$sql3.$sql4);
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return false;
		}
	}
	
		public function salesinv_delete($ID){
		$this->db->where('ID', $ID);
		$query = $this->db->delete('comp_sales_invoice'); 
		if($query == TRUE){
			return TRUE;
		} else {
			return FALSE;
		}
	}
	
}

