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

class CompAcctDet extends CI_Model{
	
	public function AcctDet_all_period($year, $period){
		$this->db->select('a.*');
		$this->db->select('b.*');
		$this->db->select('c.total as lasttotal');
		$this->db->where('b.companyID', element('compID', $this->session->userdata('logged_in')));
		$this->db->order_by('a.year');
		$this->db->order_by('a.period');
		$this->db->order_by('b.acctCode');
		$this->db->from('comp_chart_of_acct a');
		$this->db->join('comp_acc_det a', 'a.acctID = b.ID and a.year ='.$year .' and a.period ='.$period, 'left outer');
//		$this->db->join('comp_acc_det c', 'a.acctID = c.acctID and c.period = a.period - 1 and c.year = a.year', 'left outer');
		$this->db->join('comp_acc_det c', 'b.ID = c.acctID and c.year + c.period <= '.$year.sprintf('%02d', $period - 1), 'left outer');
		$query = $this->db->get();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return false;
		}
	}
	
	public function AcctDet_allfy($year){
		$this->db->select('a.amountdr as currentyeardebit');
		$this->db->select('a.amountcr as currentyearcredit');
		$this->db->select('a.total');
		$this->db->select('c.*');
		$this->db->select('b.*');
		$this->db->select('sum(c.amountdr) as lastyeardebit');
		$this->db->select('sum(c.amountcr) as lastyearcredit');
		$this->db->where('b.companyID', element('compID', $this->session->userdata('logged_in')));
		$this->db->order_by('b.acctCode');
		$this->db->group_by('a.year');
		$this->db->group_by('a.period');
		$this->db->group_by('b.acctCode');
		$this->db->from('comp_chart_of_acct b');
		$this->db->join('comp_acc_det a', 'a.acctID = b.ID and a.year ='.$year, 'left outer');
		$this->db->join('comp_acc_det c', 'b.ID = c.acctID and c.year < '.$year, 'left outer');
		$query = $this->db->get();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return false;
		}
	}
	public function AcctDet_allfypnl($year){
		$this->db->select('sum(b.amountdr) as currentyeardebit');
		$this->db->select('sum(b.amountcr) as currentyearcredit');
		$this->db->select('sum(b.total) as total');
		$this->db->select('a.*');
		$this->db->select('(select sum(c.amountcr) from `comp_budget` c 
	where `c`.`acctID` = `a`.`ID` and c.year = '.$year.' and c.year = b.year and c.companyID = '.element('compID', $this->session->userdata('logged_in')).') as budgetAmount', FALSE);
		$this->db->select('d.acctTypeID');
		$this->db->where('b.companyID', element('compID', $this->session->userdata('logged_in')));
		$this->db->order_by('a.acctCode');
		$this->db->group_by('a.ID');
		$this->db->from('comp_chart_of_acct a');
		$this->db->join('comp_acc_det b', 'b.acctID = a.ID and b.year ='.$year.
			' and b.companyID = ' .element('compID', $this->session->userdata('logged_in')), 'left outer');
		$this->db->join('comp_acct_group d', 'd.ID = a.acctGroupID and (d.acctTypeID = "3" OR d.acctTypeID = "4")');
		$query = $this->db->get();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return false;
		}
	}
	public function AcctDet_allfypnlxxx($year){
		$this->db->select('sum(b.amountdr) as currentyeardebit');
		$this->db->select('sum(b.amountcr) as currentyearcredit');
		$this->db->select('sum(b.total) as total');
		$this->db->select('a.*');
		$this->db->select('sum(c.amountcr) as budgetAmount');
		$this->db->select('d.acctTypeID');
		$this->db->where('b.companyID', element('compID', $this->session->userdata('logged_in')));
		$this->db->order_by('a.acctCode');
		$this->db->group_by('a.ID');
		$this->db->from('comp_chart_of_acct a');
		$this->db->join('comp_acc_det b', 'b.acctID = a.ID and b.year ='.$year.
			' and b.companyID = ' .element('compID', $this->session->userdata('logged_in')), 'left outer');
		$this->db->join('comp_budget c', 'c.acctID = a.ID and c.year ='.$year.
			' and c.companyID = ' .element('compID', $this->session->userdata('logged_in')), 'left outer');
		$this->db->join('comp_acct_group d', 'd.ID = a.acctGroupID and (d.acctTypeID = "3" OR d.acctTypeID = "4")');
		$query = $this->db->get();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return false;
		}
	}
	public function AcctDet_allfypnl1($year){
		$this->db->select('a.amountdr as currentyeardebit');
		$this->db->select('a.amountcr as currentyearcredit');
		$this->db->select('a.total');
		$this->db->select('b.*');
		$this->db->select('c.acctTypeID');
		$this->db->where('b.companyID', element('compID', $this->session->userdata('logged_in')));
		$this->db->order_by('b.acctCode');
		$this->db->group_by('a.year');
		$this->db->group_by('a.period');
		$this->db->group_by('b.acctCode');
		$this->db->from('comp_chart_of_acct b');
		$this->db->join('comp_acc_det a', 'a.acctID = b.ID and a.year ='.$year);
		$this->db->join('comp_acct_group c', 'c.ID = b.acctGroupID and (c.acctTypeID = "3" OR c.acctTypeID = "4")');
		$query = $this->db->get();
		if($query->num_rows() >= 1){
			$dataPNL =  $query->result();
			$newYear = $year + 1;
			$totalAmount = 0;
			$totalCredit = 0;
			$totalDebit = 0;
			$seq = 0;
			$this->db->flush_cache();
			$this->db->limit(1);
			$this->db->select('a.accountID');
	     	$this->db->where('a.is_delete', 0);
	     	$this->db->where('a.accountCode', 'retainedEarnings');
		 	$this->db->where('a.companyID', element('compID', $this->session->userdata('logged_in')));
			$this->db->select('b.acctGroupID');
			$this->db->from('comp_control_acct_setup a');
			$this->db->join('comp_chart_of_acct b', 'b.ID = a.accountID');
		 	$query3 = $this->db->get();
			$dataRE =  $query3->result();
			$this->db->flush_cache();
			$this->db->limit(1);
			$this->db->select('a.accountID');
	     	$this->db->where('a.is_delete', 0);
	     	$this->db->where('a.accountCode', 'profitlostYear');
		 	$this->db->where('a.companyID', element('compID', $this->session->userdata('logged_in')));
			$this->db->select('b.acctGroupID');
			$this->db->from('comp_control_acct_setup a');
			$this->db->join('comp_chart_of_acct b', 'b.ID = a.accountID');
		 	$query9 = $this->db->get();
			$dataPL =  $query9->result();
			$this->db->flush_cache();
			$this->load->model('Glseq');
			$query1 = $this->Glseq->glSequence_YE($newYear);
            $newData = array(
                'companyID' => element('compID', $this->session->userdata('logged_in')),
                'journalID' => $query1['seqNumber'],
                'description' => 'Year End Close',
                'module' => '1',
				'status' => 0,
                'effective_date' => date("Y-m-d"),
				'year' => $query1['financialYear'],
				'period' => $query1['period'],
                'createdBy' => element('role', $this->session->userdata('logged_in'))
            );
            $this->load->model('Journal');
            $query3 = $this->Journal->journal_insert($newData);
			$jlID = $this->db->insert_id();
			foreach($dataPNL as $row){
				$seq++;
				$this->db->flush_cache();
/*				$newData = array(
					'companyID' => element('compID', $this->session->userdata('logged_in')),
					'acctGroupID' => $row->acctGroupID,
					'acctID' => $row->ID,
					'year' => $newYear,
					'period' => 0,
					'amountdr' => $row->currentyeardebit * -1,
					'amountcr' => $row->currentyearcredit * -1,
					'total' => $row->total * -1,
					'createdBy' => element('role', $this->session->userdata('logged_in')),
				);
				$totalAmount = $totalAmount + $row->total;
				$INSquery = $this->db->insert('comp_acc_det', $newData);
				$this->db->flush_cache(); */
				$totalCredit = $totalCredit + ($row->currentyearcredit * -1);
				$totalDebit = $totalDebit + ($row->currentyeardebit * -1);
				$totalAmount = $totalAmount + $row->total;
					$newdata = array(
						'companyID' => element('compID', $this->session->userdata('logged_in')),
						'journalID' => $query1['seqNumber'],
						'sequence' => $seq,
						'description' => 'Year End Close',
						'acctCode' => $row->ID,
						'year' => $newYear,
						'period' => 0,
						'status' => 0,
						'currencyID' => element('compCurrency', $this->session->userdata('logged_in')),
						'amount_cr' => $row->currentyearcredit * -1,
						'amount_dr' => $row->currentyeardebit * -1,
						'createdBy' => element('role', $this->session->userdata('logged_in'))
					);
					$query = $this->Journal->journalDetails_insert($newdata);
				$this->db->flush_cache();
				$seq++;
				$newdata = array(
					'companyID' => element('compID', $this->session->userdata('logged_in')),
					'journalID' => $query1['seqNumber'],
					'sequence' => $seq,
					'description' => 'Year End Close',
					'acctCode' => $dataRE[0]->accountID,
					'year' => $newYear,
					'period' => 0,
					'status' => 0,
					'currencyID' => element('compCurrency', $this->session->userdata('logged_in')),
					'amount_cr' => $row->currentyearcredit,
					'amount_dr' => $row->currentyeardebit,
					'createdBy' => element('role', $this->session->userdata('logged_in'))
				);
				$query = $this->Journal->journalDetails_insert($newdata);
			}
				$this->db->flush_cache();
				$totalCredit = $totalCredit + ($totalAmount * -1);
				$updateHeader = array(
					'total_amount_dr' => $totalCredit,
					'total_amount_cr' => $totalDebit,
					'total_amount'=>0
				);
				$this->db->where('companyID', element('compID', $this->session->userdata('logged_in')));
				$this->db->where('journalID', $query1['seqNumber']);
				$queryJournal = $this->db->update('comp_journal', $updateHeader); 
				$journalNo = $this->input->post('journalNo');
				$data = array(
					'status'=>'1'
				);
				$this->load->model('Journal');
				$query5 = $this->Journal->journal_save($jlID, $query1['seqNumber'], $data);
			return true;
		}else{
			return false;
		}
	}
	
	public function AcctDet_allfy_period($year, $period){
		$this->db->select('b.*');
//		$this->db->select('c.*');
		$this->db->select('a.amountdr as thismonthdr');
		$this->db->select('a.amountcr as thismonthcr');
		$this->db->select('sum(c.amountdr) as yeardebit');
		$this->db->select('sum(c.amountcr) as yearcredit');
		$this->db->where('b.companyID', element('compID', $this->session->userdata('logged_in')));
		$this->db->order_by('b.acctCode');
		$this->db->group_by('a.year');
		$this->db->group_by('a.period');
		$this->db->group_by('b.acctCode');
		$this->db->from('comp_chart_of_acct b');
		$this->db->join('comp_acc_det a', 'a.acctID = b.ID and a.year ='.$year .' and a.period ='.$period, 'left outer');
		$this->db->join('comp_acc_det c', 'b.ID = c.acctID and CONCAT(c.year,if(c.period<=9, CONCAT("0",c.period),c.period)) <= '.$year.sprintf('%02d', $period), 'left outer');
		$query = $this->db->get();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return false;
		}
	}
	
	public function AcctDet_allfy1_period($year, $period){
		$this->db->select('a.amountdr as thismonthdr');
		$this->db->select('a.amountcr as thismonthcr');
		$this->db->select('a.total');
		$this->db->select('b.*');
		$this->db->select('sum(c.amountdr) as yeardebit');
		$this->db->select('sum(c.amountcr) as yearcredit');
		$this->db->where('b.companyID', element('compID', $this->session->userdata('logged_in')));
		$this->db->order_by('b.acctCode');
		$this->db->group_by('a.year');
		$this->db->group_by('a.period');
		$this->db->group_by('b.acctCode');
		$this->db->from('comp_chart_of_acct b');
		$this->db->join('comp_acc_det a', 'a.acctID = b.ID and a.year ='.$year .' and a.period ='.$period, 'left outer');
		$this->db->join('comp_acc_det c', 'b.ID = c.acctID and CONCAT(c.year,if(c.period<=9, CONCAT("0",c.period),c.period)) < '.$year.sprintf('%02d', $period), 'left outer');
		$query = $this->db->get();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return false;
		}
	}
	
	public function AcctDet_allfy1_period_acctNo($year, $period, $acctNo){
		$this->db->select('a.amountdr as thismonthdr');
		$this->db->select('a.amountcr as thismonthcr');
		$this->db->select('a.total');
		$this->db->select('b.*');
		$this->db->select('sum(c.amountdr) as yeardebit');
		$this->db->select('sum(c.amountcr) as yearcredit');
		$this->db->where('b.companyID', element('compID', $this->session->userdata('logged_in')));
		$this->db->where('b.ID', $acctNo);
		$this->db->order_by('b.acctCode');
		$this->db->group_by('a.year');
		$this->db->group_by('a.period');
		$this->db->group_by('b.acctCode');
		$this->db->from('comp_chart_of_acct b');
		$this->db->join('comp_acc_det a', 'a.acctID = b.ID and a.year ='.$year .' and a.period ='.$period, 'left outer');
		$this->db->join('comp_acc_det c', 'b.ID = c.acctID and CONCAT(c.year,if(c.period<=9, CONCAT("0",c.period),c.period)) < '.$year.sprintf('%02d', $period), 'left outer');
		$query = $this->db->get();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return false;
		}
	}
	
	public function accountDet(){
		$where = "(acctTypeID = 1 OR acctTypeID = 2)";
		$this->db->where('companyID', element('compID', $this->session->userdata('logged_in')));
		$this->db->where('parentID', 0);
		$this->db->where($where);
		$this->db->order_by('acctTypeID');
		$this->db->order_by('parentID');
		$this->db->order_by('orderNo');
		$query = $this->db->get('comp_acct_group');
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return FALSE;
		}
	}
}
/* End of file compAcctDet.php */
/* Location: ./application/model/compAcctDet.php */
/* Created By : Yahaya Abdollah */
