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

class Glseq extends CI_Model{
	
	public function glSequence_jl(){
		$this->load->model('Compfinancialyear');
		$query = $this->Compfinancialyear->compfinancialyear_date($this->input->post('effdate'));
		$datafcal = $query[0];
		$data['financialYear'] = $datafcal->financialYear;
		$data['period'] = $datafcal->period;
		
		$this->load->model('Compjlseq');
		$query = $this->Compjlseq->compjlseq_yearperiod( '1', 'JL', $datafcal->financialYear, $datafcal->period );
		if($query){
			$oldseq = $query[0];
			$updatedata = array(
				'seqNo' => $oldseq->seqNo + 1
			);
			$this->Compjlseq->compjlseq_update($updatedata, $oldseq->ID);
			$data['seqNumber'] = $oldseq->initial . $oldseq->year . sprintf('%02d', $oldseq->period) . sprintf('%08d', ($oldseq->seqNo + 1));
			return $data;
//    		print json_encode(array("status"=>"success", "message"=>$data['seqNumber']));
		}else{
			$newdata = array(
				'compID' => element('compID', $this->session->userdata('logged_in')),
				'moduleID' => '1',
				'year' => $datafcal->financialYear,
				'period' => $datafcal->period,
				'initial' => 'JL',
				'seqNo' => 1
			);
			$sql = $this->Compjlseq->compjlseq_insert($newdata);
			$data['seqNumber'] = 'JL' . $datafcal->financialYear . sprintf('%02d', $datafcal->period) . sprintf('%08d', (1));
			return $data;
		}
	}
	
	public function glSequence_YE($year){
		$data['financialYear'] = $year;
		$data['period'] = 0;
		
		$this->load->model('Compjlseq');
		$query = $this->Compjlseq->compjlseq_yearperiod( '1', 'JL', $year, 0 );
		if($query){
			$oldseq = $query[0];
			$updatedata = array(
				'seqNo' => $oldseq->seqNo + 1
			);
			$this->Compjlseq->compjlseq_update($updatedata, $oldseq->ID);
			$data['seqNumber'] = $oldseq->initial . $oldseq->year . sprintf('%02d', $oldseq->period) . sprintf('%08d', ($oldseq->seqNo + 1));
			return $data;
//    		print json_encode(array("status"=>"success", "message"=>$data['seqNumber']));
		}else{
			$newdata = array(
				'compID' => element('compID', $this->session->userdata('logged_in')),
				'moduleID' => '1',
				'year' => $year,
				'period' => 0,
				'initial' => 'JL',
				'seqNo' => 1
			);
			$sql = $this->Compjlseq->compjlseq_insert($newdata);
			$data['seqNumber'] = 'JL' . $year . sprintf('%02d', 0) . sprintf('%08d', (1));
			return $data;
		}
	}
	
	public function glSequence_rv(){
		$this->load->model('Compfinancialyear');
		$query = $this->Compfinancialyear->compfinancialyear_date($this->input->post('effdate'));
		$datafcal = $query[0];
		$data['financialYear'] = $datafcal->financialYear;
		$data['period'] = $datafcal->period;
		
		$this->load->model('Compjlseq');
		$query = $this->Compjlseq->compjlseq_yearperiod( '1', 'RV', $datafcal->financialYear, $datafcal->period );
		if($query){
			$oldseq = $query[0];
			$updatedata = array(
				'seqNo' => $oldseq->seqNo + 1
			);
			$this->Compjlseq->compjlseq_update($updatedata, $oldseq->ID);
			$data['seqNumber'] = $oldseq->initial . $oldseq->year . sprintf('%02d', $oldseq->period) . sprintf('%08d', ($oldseq->seqNo + 1));
			return $data;
//    		print json_encode(array("status"=>"success", "message"=>$data['seqNumber']));
		}else{
			$newdata = array(
				'compID' => element('compID', $this->session->userdata('logged_in')),
				'moduleID' => '1',
				'year' => $datafcal->financialYear,
				'period' => $datafcal->period,
				'initial' => 'RV',
				'seqNo' => 1
			);
			$sql = $this->Compjlseq->compjlseq_insert($newdata);
			$data['seqNumber'] = 'RV' . $datafcal->financialYear . sprintf('%02d', $datafcal->period) . sprintf('%08d', (1));
			return $data;
		}
	}
	
	public function glSequence_sub($module, $initial, $year, $period){
		$this->load->model('Compjlseq');
		$query = $this->Compjlseq->compjlseq_yearperiod( $module, $initial, $year, $period );
		if($query){
			$oldseq = $query[0];
			$updatedata = array(
				'seqNo' => $oldseq->seqNo + 1
			);
			$this->Compjlseq->compjlseq_update($updatedata, $oldseq->ID);
			$data['seqNumber'] = $oldseq->initial . $oldseq->year . sprintf('%02d', $oldseq->period) . sprintf('%08d', ($oldseq->seqNo + 1));
			return $data;
//    		print json_encode(array("status"=>"success", "message"=>$data['seqNumber']));
		}else{
			$newdata = array(
				'compID' => element('compID', $this->session->userdata('logged_in')),
				'moduleID' => $module,
				'year' => $year,
				'period' => $period,
				'initial' => $initial,
				'seqNo' => 1
			);
			$sql = $this->Compjlseq->compjlseq_insert($newdata);
			$data['seqNumber'] = $initial . $year . sprintf('%02d', $period) . sprintf('%08d', (1));
			return $data;
		}
	}

	//GST Transaction (sayem-26-3-2015)
	public function gstInsert($table,$data){
		$this->db->insert($table,$data);
		if($this->db->affected_rows() > 0){
			return $this->db->insert_id();
		}else{
			return false;
		}
	}
	
	//GST Transaction update (sayem-26-3-2015)
	public function gstUpdate($table,$data,$id){
		$this->db->where('ID',$id)
				->update($table,$data);
		if($this->db->affected_rows() > 0){
			return true;
		}else{
			return false;
		}
	}
	//Get GST Transaction List
	public function gstTransactionList($start,$end){
		$query = $this->db->select("a.*,CONCAT(a.formNo,'[',lpad(a.serialNo,8,0),']') as formNo",false)
						->where(array(
								'companyID' => element('compID', $this->session->userdata('logged_in')),
								'updateAble' => 1,
								'date >=' => $start,
								'date <=' => $end,
							))
						->get('comp_gst_transaction a')
						->result();
		if(!empty($query)){
			return $query;
		}else{
			return false;
		}
	}

	//Get GST transaction information
	public function gstTransID($id){
		$query = $this->db->select("a.ID as aID,a.*,b.*,b.Description as detDesc,t.code as TaxCode,lpad(a.serialNo,8,0)",false)
						->where('a.ID',$id)
						->join('comp_gst_transaction_detail b','b.gstTransID = a.ID','right')
						->join('tax_master t','t.ID = b.taxID','left')
						->get('comp_gst_transaction a')->result();

		if(!empty($query)){
			return $query;
		}else{
			return false;
		}
	}

	//Remove GST Transaction
	public function removeGST($id = null){
		if($id == null){
			return false;
		}

		$this->db->where('ID',$id)->delete('comp_gst_transaction');


		if($this->db->affected_rows() > 0 ){
			$this->db->where('gstTransID',$id)->delete('comp_gst_transaction_detail');			
			return true;
		}else{
			return false;
		}
	}

	//Get GST Transaction information for journal posting
	public function gstTransactionDetails_ID($ID){
		$this->db->where('paymentID', $ID);
		$this->db->from('view_unpost_gst_transaction');
		$query = $this->db->get();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return false;
		}
	 }

	 //GST Transaction data update
	 public function gstTransactionPosting($id, $data){
		$this->db->where('ID', $id);
		$query = $this->db->update('comp_gst_transaction', $data);

		if($query){
			$this->db->flush_cache();
			$this->db->select('comp_gst_transaction.*');
			$this->db->select('concat(comp_gst_transaction.formNo,"[",lpad(comp_gst_transaction.serialNo,8,"0"),"]") as formNo', false);
	
			$this->db->where('ID', $id);
			$this->db->from('comp_gst_transaction');
			$query1 = $this->db->get();
			return $query1->result();
		}else{
			return false;
		}
	 }

	 //GST Transaction journal detail entry
	 public function gstJournalDetail($journalID, $header, $detailsData, $year, $period){
		if($detailsData){
			if ($header->exchangeRate == '' || $header->exchangeRate == 0) $header->exchangeRate = 1;
			$seq = 0;
			$journalAmount = 0;
			$lineAmount = 0;
			foreach($detailsData as $row):
				if($row->amtPaid_amtAppl != 0){
				$seq++;
				$lineAmount = $row->amtPaid_amtAppl * $header->exchangeRate;
				$newData = array(
					'companyID' => element('compID', $this->session->userdata('logged_in')),
					'journalID' => $journalID,
					'sequence' => $seq,
					'description' => $row->supplierName,
					'acctCode' => $row->crAccountID,
					'amount_cr' => $row->amtPaid_amtAppl * $header->exchangeRate,
					'currencyID' => (!isset($header->currencyID))? 1: $header->currencyID,
					'exchange_rate' => $header->exchangeRate,
					'currency_amount' => $row->amtPaid_amtAppl,
					'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')),
					'journalID' => $journalID,
					'sequence' => $seq,
					'description' => $row->supplierName,
					'acctCode' => $row->drAccountID,
					'amount_dr' => $row->amtPaid_amtAppl * $header->exchangeRate,
					'currencyID' => (!isset($header->currencyID))? 1: $header->currencyID,
					'exchange_rate' => $header->exchangeRate,
					'currency_amount' => $row->amtPaid_amtAppl,
					'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);

				$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;
		}
	 }
}

