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

class DebitNote_model extends CI_Model{
	
	
	public function debitnote_all(){
		$this->db->where('comp_supplier_debitnote.companyID', element('compID', $this->session->userdata('logged_in')));
		$this->db->select('comp_supplier_debitnote.*');
		$this->db->select('supplier.supplierName as supplierName');
		$this->db->from('comp_supplier_debitnote');
		$this->db->join('supplier', 'supplier.ID = comp_supplier_debitnote.supplierID');
		$query = $this->db->get();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return false;
		}
	}
	
	public function debitnote_id($ID){
		$this->db->select('a.*');
		$this->db->select('b.*');
		$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_supplier_debitnote 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 debitnote_Date($dateFrom, $dateTo){
		
		$sql1 = "SELECT comp_supplier_debitnote.*, if (comp_supplier_debitnote.updateAble=1, 'Yes', 'No' ) as confirm, supplier.supplierName as supplierName, comp_supplier_invoice.supplierInvoiceNo";
		$sql2 = " FROM (comp_supplier_debitnote JOIN supplier ON supplier.ID = comp_supplier_debitnote.supplierID) JOIN comp_supplier_invoice ON comp_supplier_invoice.ID = comp_supplier_debitnote.supplierInvoiceID";
		$sql3 = " WHERE comp_supplier_debitnote.companyID = '".element('compID', $this->session->userdata('logged_in'))."' AND comp_supplier_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 debitnote_unpost($dateFrom, $dateTo){
		$this->db->select('a.*');
                 $this->db->select('concat(a.formNo,"[",lpad(a.serialNo,8,"0"),"]") as formNo', false);

		$this->db->select('a.updateAble as Yes');
		$this->db->select('b.supplierName');
		$this->db->select('c.formNo as supplierInvoiceNo');
		$this->db->where('a.companyID', element('compID', $this->session->userdata('logged_in')));
		$this->db->where('debitNoteDate >=', $dateFrom);
		$this->db->where('debitNoteDate <=', $dateTo);
		$this->db->where('a.updateAble', 1);
		$this->db->order_by('debitNoteDate', 'desc');
		$this->db->from('comp_supplier_debitnote a');
		$this->db->join('supplier b', 'b.ID = a.supplierID');
		$this->db->join('comp_supplier_invoice c','c.ID = a.supplierInvoiceID');
		$query = $this->db->get();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return false;
		}
	}
	 public function debitnotedetails_debitnoteID($journalID, $header, $detailsData, $year, $period){
		if($detailsData){
			$seq = 0;
			$journalAmount = 0;
			$lineAmount = 0;
			foreach($detailsData as $row):
				if($row->amountExcludedtax != 0){
				$seq++;
				$lineAmount = $row->amountExcludedtax * $header->exchangeRate;
				$newData = array(
					'companyID' => element('compID', $this->session->userdata('logged_in')),
					'journalID' => $journalID,
					'sequence' => $seq,
					'description' => $row->itemName,
					'acctCode' => $row->crAccountID,
					'supplier' => $row->supplier,
					'amount_cr' => $lineAmount,
					'currencyID' => ($header->currencyID == 0)? 1: $header->currencyID,
					'exchange_rate' => $header->exchangeRate,
					'currency_amount' => $row->amountExcludedtax,
					'period' => $period,
					'year' => $year,
					'line' => $row->ID,
					'formNo' => $header->formNo . '[' . sprintf('%08d', $header->ID) . ']',
					'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,
					'supplier' => $row->supplier,
					'amount_dr' => $lineAmount,
					'currencyID' => ($header->currencyID == 0)? 1: $header->currencyID,
					'exchange_rate' => $header->exchangeRate,
					'currency_amount' => $row->amountExcludedtax,
					'period' => $period,
					'year' => $year,
					'formNo' => $header->formNo . '[' . sprintf('%08d', $header->ID) . ']',
					'line' => $row->ID,
					'createdBy' => element('role', $this->session->userdata('logged_in'))
				);
				$queryInsert = $this->db->insert('comp_journal_detail', $newData);
				$journalAmount = $journalAmount + $lineAmount;
				}
				if($row->taxamount !=0 ){
				$lineAmount = $row->taxamount * $header->exchangeRate;
				$seq++;
				$newData = array(
					'companyID' => element('compID', $this->session->userdata('logged_in')),
					'sequence' => $seq,
					'journalID' => $journalID,
					'description' => $row->itemName,
					'acctCode' => $row->drTaxAccountID,
					'supplier' => $row->supplier,
					'amount_dr' => $lineAmount,
					'currencyID' => ($header->currencyID == 0)? 1: $header->currencyID,
					'exchange_rate' => $header->exchangeRate,
					'currency_amount' => $row->taxamount,
					'period' => $period,
					'year' => $year,
					'formNo' => $header->formNo . '[' . sprintf('%08d', $header->ID) . ']',
					'line' => $row->ID,
					'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->crTaxAccountID,
					'supplier' => $row->supplier,
					'amount_cr' => $lineAmount,
					'currencyID' => ($header->currencyID == 0)? 1: $header->currencyID,
					'exchange_rate' => $header->exchangeRate,
					'currency_amount' => $row->taxamount,
					'period' => $period, 
					'year' => $year,
					'formNo' => $header->formNo . '[' . sprintf('%08d', $header->ID) . ']',
					'line' => $row->ID,
					'createdBy' => element('role', $this->session->userdata('logged_in'))
				);
				$queryInsert = $this->db->insert('comp_journal_detail', $newData);
				$journalAmount = $journalAmount + $lineAmount;
				}
			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 debitNotePrintDetails_ID($ID){
	 	$query = $this->db->select("sdDet.*,CONCAT('[',i.itemCode,'] ',i.name) as itemNamCode",false)
			 			->from('comp_supplier_debitnote_detail sdDet')
			 			->join('item as i','i.ID = sdDet.itemID','left')
			 			->where(array('debitnoteID'=>$ID))
			 			->get();
	 	
//		$this->db->select ();
//		$this->db->select ('b.*');
//		$this->db->where('debitnoteID', $ID);
//		$this->db->from('comp_supplier_debitnote a');
//		$this->db->join('comp_supplier_debitnote_detail b', 'a.id = b.debitNoteID', 'left outer');
//		$query = $this->db->get('comp_supplier_debitnote_detail');
		
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return false;
		}
	 }

	 public function debitNoteDetails_ID($ID){
		$this->db->where('debitnoteID', $ID);
		$this->db->from('view_unpost_ap_debitnote');
		$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_supplier_debitnote', $updatepayment); 
		if($query){
			return TRUE;
		}else{
			return FALSE;
		}
		
	}
	 public function debitNotepost($id, $data){
		$this->db->where('ID', $id);
//		$this->db->where('updateAble', 1);
		$query = $this->db->update('comp_supplier_debitnote', $data);
		if($query){
			$this->db->flush_cache();
                        $this->db->select('comp_supplier_debitnote.*');
                         $this->db->select('concat(comp_supplier_debitnote.formNo,"[",lpad(comp_supplier_debitnote.serialNo,8,"0"),"]") as formNo', false);

			$this->db->where('ID', $id);
			$this->db->from('comp_supplier_debitnote');
			$query1 = $this->db->get();
			return $query1->result();
		}else{
			return false;
		}
	 }
	 
}
/* End of file debitNote_model.php */
/* Location: ./application/model/debitNote_model.php */

