<?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('concat(comp_sales_invoice.formNo,"[",lpad(comp_sales_invoice.serialNo,8,"0"),"]") as formNo', false);
		$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.*, CONCAT(comp_sales_invoice.formNo,'[',lpad(comp_sales_invoice.serialNo,8,0),']') as formNo, 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('f.agentName');
		$this->db->select('b.*');
        $this->db->select('e.line1 as sline1, e.line2 as sline2, e.line3 as sline3, e.postCode as scustomerPostCode, e.city as scity');
		$this->db->select('g.fldname as tstate, h.fldcountry as ucountry');
		$this->db->select('c.fldcurr_code');
		$this->db->select('d.termName,d.termDescription');
		$this->db->select('concat(a.formNo,"[",lpad(a.serialNo,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('customer_address e', 'e.ID = a.shipToID', 'left outer');
                $this->db->join('comp_sales_agent f', 'f.ID = a.agentID', 'left outer');
				$this->db->join('_ref_state g', 'g.fldid = b.customerStateID', 'left outer');
				$this->db->join('_ref_country h', 'h.fldid = b.customerCountryID', '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.*,a.quantity qty');
		$this->db->select('c.companyID');
		$this->db->where('a.invoiceID', $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(t.code,"(",a.taxRate,")") as taxRate',false);
		$this->db->select('d.code');
		/*$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('tax_master t', 't.ID = a.taxID','left');
		$this->db->join('comp_sales_invoice c', 'c.ID = a.invoiceID');
		$this->db->join('item b', 'b.ID = a.itemID');
		$this->db->join('_sys_um d', 'd.fldid = a.umID', 'LEFT');
		//$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->select('concat(a.formNo,"[",lpad(a.serialNo,8,"0"),"]") as formNo', false);
		$this->db->where('a.ID', $ID);
		$this->db->select('b.*');
		$this->db->select('concat("[",c.acctCode,"]",c.acctName) as acctNameCode', false);
		$this->db->select('CONCAT(t.code,"(",b.taxRate,")") as taxRate', 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','left');
		$this->db->join('tax_master t', 't.ID = b.taxID','left');
		$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.serialNo,8,'0'),']') as formNo";
		$sql1 .= ",concat(comp_sales_invoice.formNo,'[',lpad(comp_sales_invoice.serialNo,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.*,si.*,concat(si.formNo,"[",lpad(si.ID,8,"0"),"]") as salesInvoiceNo',false);
		$this->db->select('c.fldcurr_code');
		$this->db->select('d.*');
		$this->db->select('concat(a.formNo,"[",lpad(a.serialNo,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('comp_sales_invoice si','si.ID = a.salesInvoiceID','left');
		$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.*,a.quantity itmQty');
		$this->db->where('a.debitnoteID', $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('CONCAT(t.code,"(",a.taxRate,")") as taxRate', false);
		$this->db->select('c.companyID');
		$this->db->select('d.code');
		/*$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('tax_master t', 't.ID = a.taxID','left');
		$this->db->join('comp_sales_debitnote c', 'c.ID = a.debitnoteID','LEFT');
		$this->db->join('item b', 'b.ID = a.itemID','LEFT');
		$this->db->join('_sys_um d', 'd.fldid = a.umID','LEFT');
		//$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->select('CONCAT(t.code,"(",b.taxRate,")") as taxRate', 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('tax_master t', 't.ID = b.taxID','left');
		$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.serialNo,8,'0'),']') as formNo";
		$sql1 .= ",concat(comp_sales_invoice.formNo,'[',lpad(comp_sales_invoice.serialNo,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;
		}
	}
	
	//Modified by Sayem to collect information about sales invoice no
	public function salescreditnote_id($ID){
		$this->db->select('a.*');
		$this->db->select('b.*,si.*,concat(si.formNo,"[",lpad(si.ID,8,"0"),"]") as salesInvoiceNo',false);
		$this->db->select('c.*');
		$this->db->select('d.*');
		$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('comp_sales_invoice si','si.ID = a.salesInvoiceID','left');
		$this->db->join('customer b', 'b.ID = a.customerID');
		$this->db->join('_ref_currency c', 'c.fldid = a.currencyID', 'left');
		$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.*,a.quantity itmQty');
		$this->db->select('c.code');
		$this->db->where('a.creditNoteID', $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('_sys_um c', 'c.fldid = a.umID');
		//$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->select('concat(a.formNo,"[",lpad(a.serialNo,8,"0"),"]") as creditNoteNo', false);
		$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.*, CONCAT(comp_receive_payment.formNo, '[', lpad(comp_receive_payment.serialNo,8,'0'), ']')as formNo, 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;
		}
	}
	
}

