<?php

/**
 * Description of Project_Model
 *
 * @author NaYeM
 */
class Accounting_model extends MY_Model
{

	public $_table_name;
	public $_order_by;
	public $_primary_key;


	public function generate_journal_entry_prefix()
	{
		$start_no_raw = config_item('journal_entry_prefix_start_no');
		$start_no_raw = $start_no_raw === null ? '1' : (string) $start_no_raw;
		$start_no = (int) $start_no_raw;
		$strlen = strlen($start_no_raw);
		$query = $this->db->query('SELECT reference_no, journal_id FROM tbl_journals WHERE journal_id = (SELECT MAX(journal_id) FROM tbl_journals)');
		if ($query->num_rows() > 0) {
			$row = $query->row();
			$ref_number = intval(substr($row->reference_no, -$strlen));
			$next_number = ++$row->journal_id;
			if ($next_number < $ref_number) {
				$next_number = $ref_number + 1;
			}
			if ($ref_number < $start_no) {
				$next_number = $start_no;
			}
			$next_number = $this->journal_entry_reference_no_exists($next_number);

			$next_number = sprintf('%04d', $next_number);
		} else {
			$next_number = sprintf('%04d', $start_no);
		}
		if (!empty(config_item('journal_entry_format'))) {
			$invoice_format = config_item('journal_entry_format');
			$journal_entry_prefix = str_replace("[" . config_item('journal_entry_prefix') . "]", config_item('journal_entry_prefix'), $invoice_format);
			$yyyy = str_replace("[yyyy]", date('Y'), $journal_entry_prefix);
			$yy = str_replace("[yy]", date('y'), $yyyy);
			$mm = str_replace("[mm]", date('M'), $yy);
			$m = str_replace("[m]", date('m'), $mm);
			$dd = str_replace("[dd]", date('d'), $m);
			$next_number = str_replace("[number]", $next_number, $dd);
		}
		return $next_number;
	}

	public function journal_entry_reference_no_exists($next_number)
	{
		$enext_number = sprintf('%04d', $next_number);
		if (!empty(config_item('journal_entry_format'))) {
			$invoice_format = config_item('journal_entry_format');
			$journal_entry_prefix = str_replace("[" . config_item('journal_entry_prefix') . "]", config_item('journal_entry_prefix'), $invoice_format);
			$yyyy = str_replace("[yyyy]", date('Y'), $journal_entry_prefix);
			$yy = str_replace("[yy]", date('y'), $yyyy);
			$mm = str_replace("[mm]", date('M'), $yy);
			$m = str_replace("[m]", date('m'), $mm);
			$dd = str_replace("[dd]", date('d'), $m);
			$enext_number = str_replace("[number]", $next_number, $dd);
		}
		$records = $this->db->where('reference_no', $enext_number)->get('tbl_journals')->num_rows();
		if ($records > 0) {
			return $this->journal_entry_reference_no_exists($next_number + 1);
		} else {
			return $next_number;
		}
	}

	public function generate_payment_voucher_prefix()
	{
		$start_no_raw = config_item('payment_voucher_prefix_start_no');
		$start_no_raw = $start_no_raw === null ? '1' : (string) $start_no_raw;
		$start_no = (int) $start_no_raw;
		$strlen = strlen($start_no_raw);
		$query = $this->db->query('SELECT reference_no, voucher_id FROM tbl_payment_vouchers WHERE voucher_id = (SELECT MAX(voucher_id) FROM tbl_payment_vouchers)');
		if ($query->num_rows() > 0) {
			$row = $query->row();
			$ref_number = intval(substr($row->reference_no, -$strlen));
			$next_number = ++$row->voucher_id;
			if ($next_number < $ref_number) {
				$next_number = $ref_number + 1;
			}
			if ($ref_number < $start_no) {
				$next_number = $start_no;
			}
			$next_number = $this->payment_voucher_reference_no_exists($next_number);

			$next_number = sprintf('%04d', $next_number);
		} else {
			$next_number = sprintf('%04d', $start_no);
		}
		if (!empty(config_item('payment_voucher_format'))) {
			$invoice_format = config_item('payment_voucher_format');
			$payment_voucher_prefix = str_replace("[" . config_item('payment_voucher_prefix') . "]", config_item('payment_voucher_prefix'), $invoice_format);
			$yyyy = str_replace("[yyyy]", date('Y'), $payment_voucher_prefix);
			$yy = str_replace("[yy]", date('y'), $yyyy);
			$mm = str_replace("[mm]", date('M'), $yy);
			$m = str_replace("[m]", date('m'), $mm);
			$dd = str_replace("[dd]", date('d'), $m);
			$next_number = str_replace("[number]", $next_number, $dd);
		}
		return $next_number;
	}

	public function payment_voucher_reference_no_exists($next_number)
	{
		$enext_number = sprintf('%04d', $next_number);
		if (!empty(config_item('payment_voucher_format'))) {
			$invoice_format = config_item('payment_voucher_format');
			$payment_voucher_prefix = str_replace("[" . config_item('payment_voucher_prefix') . "]", config_item('payment_voucher_prefix'), $invoice_format);
			$yyyy = str_replace("[yyyy]", date('Y'), $payment_voucher_prefix);
			$yy = str_replace("[yy]", date('y'), $yyyy);
			$mm = str_replace("[mm]", date('M'), $yy);
			$m = str_replace("[m]", date('m'), $mm);
			$dd = str_replace("[dd]", date('d'), $m);
			$enext_number = str_replace("[number]", $next_number, $dd);
		}
		$records = $this->db->where('reference_no', $enext_number)->get('tbl_payment_vouchers')->num_rows();
		if ($records > 0) {
			return $this->payment_voucher_reference_no_exists($next_number + 1);
		} else {
			return $next_number;
		}
	}


	public function generate_receipt_voucher_prefix()
	{
		$start_no_raw = config_item('receipt_voucher_prefix_start_no');
		$start_no_raw = $start_no_raw === null ? '1' : (string) $start_no_raw;
		$start_no = (int) $start_no_raw;
		$strlen = strlen($start_no_raw);
		$query = $this->db->query('SELECT reference_no, voucher_id FROM tbl_receipt_vouchers WHERE voucher_id = (SELECT MAX(voucher_id) FROM tbl_receipt_vouchers)');
		if ($query->num_rows() > 0) {
			$row = $query->row();
			$ref_number = intval(substr($row->reference_no, -$strlen));
			$next_number = ++$row->voucher_id;
			if ($next_number < $ref_number) {
				$next_number = $ref_number + 1;
			}
			if ($ref_number < $start_no) {
				$next_number = $start_no;
			}
			$next_number = $this->receipt_voucher_reference_no_exists($next_number);

			$next_number = sprintf('%04d', $next_number);
		} else {
			$next_number = sprintf('%04d', $start_no);
		}
		if (!empty(config_item('receipt_voucher_format'))) {
			$invoice_format = config_item('receipt_voucher_format');
			$receipt_voucher_prefix = str_replace("[" . config_item('receipt_voucher_prefix') . "]", config_item('receipt_voucher_prefix'), $invoice_format);
			$yyyy = str_replace("[yyyy]", date('Y'), $receipt_voucher_prefix);
			$yy = str_replace("[yy]", date('y'), $yyyy);
			$mm = str_replace("[mm]", date('M'), $yy);
			$m = str_replace("[m]", date('m'), $mm);
			$dd = str_replace("[dd]", date('d'), $m);
			$next_number = str_replace("[number]", $next_number, $dd);
		}
		return $next_number;
	}

	public function receipt_voucher_reference_no_exists($next_number)
	{
		$enext_number = sprintf('%04d', $next_number);
		if (!empty(config_item('receipt_voucher_format'))) {
			$invoice_format = config_item('receipt_voucher_format');
			$receipt_voucher_prefix = str_replace("[" . config_item('receipt_voucher_prefix') . "]", config_item('receipt_voucher_prefix'), $invoice_format);
			$yyyy = str_replace("[yyyy]", date('Y'), $receipt_voucher_prefix);
			$yy = str_replace("[yy]", date('y'), $yyyy);
			$mm = str_replace("[mm]", date('M'), $yy);
			$m = str_replace("[m]", date('m'), $mm);
			$dd = str_replace("[dd]", date('d'), $m);
			$enext_number = str_replace("[number]", $next_number, $dd);
		}
		$records = $this->db->where('reference_no', $enext_number)->get('tbl_receipt_vouchers')->num_rows();
		if ($records > 0) {
			return $this->receipt_voucher_reference_no_exists($next_number + 1);
		} else {
			return $next_number;
		}
	}

	public function get_chart_of_accounts_by_search($search)
	{
		// get result from tbl_account_type,tbl_account_sub_type,tbl_chart_of_accounts
		$this->db->select('tbl_chart_of_accounts.*,tbl_account_type.account_type,tbl_account_sub_type.account_sub_type');
		$this->db->from('tbl_chart_of_accounts');
		$this->db->join('tbl_account_type', 'tbl_account_type.account_type_id = tbl_chart_of_accounts.account_type_id', 'left');
		$this->db->join('tbl_account_sub_type', 'tbl_account_sub_type.account_sub_type_id = tbl_chart_of_accounts.account_sub_type_id', 'left');
		$search = trim((string) $search);
		if ($search !== '') {
			$this->db->group_start()
				->like('tbl_chart_of_accounts.code', $search)
				->or_like('tbl_chart_of_accounts.name', $search)
				->or_like('tbl_account_type.account_type', $search)
				->or_like('tbl_account_sub_type.account_sub_type', $search)
				->group_end();
		}
		$this->db->order_by('tbl_chart_of_accounts.name', 'asc');
		$query = $this->db->get();
		return $query->result();
	}

	public function get_trial_balance()
	{
		// get trial balance from tbl_journals,tbl_journal_items and tbl_chart_of_accounts,tbl_account_type,tbl_account_sub_type
		// if date is empty then takes 1 year date
		$range = $this->input->post('range', true);
		if (!empty($range)) {
			$range = explode('-', $this->input->post('range', true));
		} else {
			$range = [];
		}

		if (!empty($range[0])) {
			$start_date = date('Y-m-d', safe_strtotime($range[0]));
			$end_date = date('Y-m-d', safe_strtotime($range[1]));
		} else {
			$start_date = date('Y-m-d', safe_strtotime('-1 year'));
			$end_date = date('Y-m-d');
		}

		$this->db->select('tbl_journal_items.*,tbl_journals.*,tbl_chart_of_accounts.name,tbl_chart_of_accounts.code');
		$this->db->from('tbl_journal_items');
		$this->db->join('tbl_journals', 'tbl_journal_items.journal_id = tbl_journals.journal_id', 'left');
		$this->db->join('tbl_chart_of_accounts', 'tbl_journal_items.chart_of_account_id = tbl_chart_of_accounts.chart_of_account_id', 'left');
		$this->db->where('tbl_journals.date >=', $start_date);
		$this->db->where('tbl_journals.date <=', $end_date);
		$this->db->order_by('tbl_chart_of_accounts.name', 'asc');
		$query = $this->db->get();
		$accounts = $query->result();
		$data = array();
		foreach ($accounts as $account) {
			if (!isset($data[$account->code])) {
				$data[$account->code] = array(
					'name' => $account->name,
					'code' => $account->code,
					'debit' => 0,
					'credit' => 0,
					'balance' => 0,
				);
			}
			$data[$account->code]['name'] = $account->name;
			$data[$account->code]['code'] = $account->code;
			$data[$account->code]['debit'] += (float) $account->debit;
			$data[$account->code]['credit'] += (float) $account->credit;
			$data[$account->code]['balance'] += (float) $account->debit - (float) $account->credit;
		}
		return $data;
	}

	public function get_ledger_report()
	{
		$account_id = $this->input->post('account_id', true);
		if (empty($account_id)) {
			// get first account id from tbl_chart_of_accounts
			$account = $this->db->order_by('chart_of_account_id', 'asc')->limit(1)->get('tbl_chart_of_accounts')->row();
			$account_id = $account->chart_of_account_id;
		}
		$range = $this->input->post('range', true);
		if (!empty($range)) {
			$range = explode('-', $this->input->post('range', true));
		} else {
			$range = [];
		}
		if (!empty($range[0])) {
			$start_date = date('Y-m-d', safe_strtotime($range[0]));
			$end_date = date('Y-m-d', safe_strtotime($range[1]));
		} else {
			$start_date = date('Y-m-d', safe_strtotime('-1 year'));
			$end_date = date('Y-m-d');
		}
		$this->db->select('tbl_journal_items.*,tbl_journals.*,tbl_chart_of_accounts.name,tbl_chart_of_accounts.code,tbl_account_details.fullname');
		$this->db->from('tbl_journal_items');
		$this->db->join('tbl_journals', 'tbl_journal_items.journal_id = tbl_journals.journal_id', 'left');
		$this->db->join('tbl_chart_of_accounts', 'tbl_journal_items.chart_of_account_id = tbl_chart_of_accounts.chart_of_account_id', 'left');
		$this->db->join('tbl_account_details', 'tbl_journals.created_by = tbl_account_details.user_id', 'left');
		$this->db->where('tbl_journals.date >=', $start_date);
		$this->db->where('tbl_journals.date <=', $end_date);
		$this->db->where('tbl_journal_items.chart_of_account_id', $account_id);
		$this->db->order_by('tbl_chart_of_accounts.name', 'asc');
		$query = $this->db->get();
		return $query->result();
	}

	public function get_balance_sheet()
	{
		$range = $this->input->post('range', true);
		if (!empty($range)) {
			$range = explode('-', $this->input->post('range', true));
		} else {
			$range = [];
		}
		if (!empty($range[0])) {
			$start_date = date('Y-m-d', safe_strtotime($range[0]));
			$end_date = date('Y-m-d', safe_strtotime($range[1]));
		} else {
			$start_date = date('Y-m-d', safe_strtotime('-1 year'));
			$end_date = date('Y-m-d');
		}
		$this->db->select('tbl_journal_items.*,tbl_journals.*,tbl_chart_of_accounts.name,tbl_chart_of_accounts.code,tbl_account_type.account_type,tbl_account_sub_type.account_sub_type');
		$this->db->from('tbl_journal_items');
		$this->db->join('tbl_journals', 'tbl_journal_items.journal_id = tbl_journals.journal_id', 'left');
		$this->db->join('tbl_chart_of_accounts', 'tbl_journal_items.chart_of_account_id = tbl_chart_of_accounts.chart_of_account_id', 'left');
		$this->db->join('tbl_account_type', 'tbl_chart_of_accounts.account_type_id = tbl_account_type.account_type_id', 'left');
		$this->db->join('tbl_account_sub_type', 'tbl_chart_of_accounts.account_sub_type_id = tbl_account_sub_type.account_sub_type_id', 'left');
		$this->db->where('tbl_journals.date >=', $start_date);
		$this->db->where('tbl_journals.date <=', $end_date);
		$this->db->order_by('tbl_chart_of_accounts.name', 'asc');
		$query = $this->db->get();
		$accounts = $query->result();
		$data = array();
		foreach ($accounts as $account) {
			if (!isset($data[$account->account_type])) {
				$data[$account->account_type] = array(
					'name' => $account->account_type,
					'balance' => 0,
					'debit' => 0,
					'credit' => 0,
					'sub' => array(),
				);
			}
			if (!isset($data[$account->account_type]['sub'][$account->account_sub_type])) {
				$data[$account->account_type]['sub'][$account->account_sub_type] = array(
					'name' => $account->account_sub_type,
					'balance' => 0,
					'debit' => 0,
					'credit' => 0,
					'accounts' => array(),
				);
			}
			if (!isset($data[$account->account_type]['sub'][$account->account_sub_type]['accounts'][$account->code])) {
				$data[$account->account_type]['sub'][$account->account_sub_type]['accounts'][$account->code] = array(
					'name' => $account->name,
					'code' => $account->code,
					'debit' => 0,
					'credit' => 0,
					'balance' => 0,
				);
			}
			$data[$account->account_type]['name'] = $account->account_type;
			$data[$account->account_type]['balance'] += (float) $account->debit - (float) $account->credit;
			$data[$account->account_type]['debit'] += (float) $account->debit;
			$data[$account->account_type]['credit'] += (float) $account->credit;

			$data[$account->account_type]['sub'][$account->account_sub_type]['name'] = $account->account_sub_type;
			$data[$account->account_type]['sub'][$account->account_sub_type]['balance'] += (float) $account->debit - (float) $account->credit;
			$data[$account->account_type]['sub'][$account->account_sub_type]['debit'] += (float) $account->debit;
			$data[$account->account_type]['sub'][$account->account_sub_type]['credit'] += (float) $account->credit;
			$data[$account->account_type]['sub'][$account->account_sub_type]['accounts'][$account->code]['name'] = $account->name;
			$data[$account->account_type]['sub'][$account->account_sub_type]['accounts'][$account->code]['code'] = $account->code;
			$data[$account->account_type]['sub'][$account->account_sub_type]['accounts'][$account->code]['debit'] += (float) $account->debit;
			$data[$account->account_type]['sub'][$account->account_sub_type]['accounts'][$account->code]['credit'] += (float) $account->credit;
			$data[$account->account_type]['sub'][$account->account_sub_type]['accounts'][$account->code]['balance'] += (float) $account->debit - (float) $account->credit;
		}
		return $data;
	}

	public function get_all_account_balance()
	{
		$this->db->select('
			coa.chart_of_account_id,
			coa.name AS coa_name,
			coa.code,
			at.account_type,
			ast.account_sub_type,
			COALESCE(SUM(COALESCE(ji.debit, 0)), 0) AS debit,
			COALESCE(SUM(COALESCE(ji.credit, 0)), 0) AS credit
		', false);
		$this->db->from('tbl_chart_of_accounts coa');
		$this->db->join('tbl_account_type at', 'at.account_type_id = coa.account_type_id', 'left');
		$this->db->join('tbl_account_sub_type ast', 'ast.account_sub_type_id = coa.account_sub_type_id', 'left');
		$this->db->join('tbl_journal_items ji', 'ji.chart_of_account_id = coa.chart_of_account_id', 'left');
		$this->db->group_by(array('coa.chart_of_account_id', 'coa.name', 'coa.code', 'at.account_type', 'ast.account_sub_type'));
		$this->db->order_by('at.account_type', 'asc');
		$this->db->order_by('ast.account_sub_type', 'asc');
		$query = $this->db->get();
		$data = array();
		foreach ($query->result() as $account) {
			$accountTypeKey = (string) ($account->account_type ?? '');
			$accountSubTypeKey = (string) ($account->account_sub_type ?? '');
			$accountTypeLabel = $accountTypeKey !== '' ? $accountTypeKey : 'Uncategorized';
			$accountSubTypeLabel = $accountSubTypeKey !== '' ? $accountSubTypeKey : 'Uncategorized';
			$debit = (float) $account->debit;
			$credit = (float) $account->credit;

			if (!isset($data[$accountTypeKey]['balance'])) {
				$data[$accountTypeKey]['balance'] = 0;
			}
			if (!isset($data[$accountTypeKey]['debit'])) {
				$data[$accountTypeKey]['debit'] = 0;
			}
			if (!isset($data[$accountTypeKey]['credit'])) {
				$data[$accountTypeKey]['credit'] = 0;
			}
			if (!isset($data[$accountTypeKey]['sub'][$accountSubTypeKey]['balance'])) {
				$data[$accountTypeKey]['sub'][$accountSubTypeKey]['balance'] = 0;
			}
			if (!isset($data[$accountTypeKey]['sub'][$accountSubTypeKey]['debit'])) {
				$data[$accountTypeKey]['sub'][$accountSubTypeKey]['debit'] = 0;
			}
			if (!isset($data[$accountTypeKey]['sub'][$accountSubTypeKey]['credit'])) {
				$data[$accountTypeKey]['sub'][$accountSubTypeKey]['credit'] = 0;
			}

			$data[$accountTypeKey]['name'] = $accountTypeLabel;
			$data[$accountTypeKey]['balance'] += $debit - $credit;
			$data[$accountTypeKey]['debit'] += $debit;
			$data[$accountTypeKey]['credit'] += $credit;
			$data[$accountTypeKey]['sub'][$accountSubTypeKey]['name'] = $accountSubTypeLabel;
			$data[$accountTypeKey]['sub'][$accountSubTypeKey]['balance'] += $debit - $credit;
			$data[$accountTypeKey]['sub'][$accountSubTypeKey]['debit'] += $debit;
			$data[$accountTypeKey]['sub'][$accountSubTypeKey]['credit'] += $credit;
		}
		return $data;
	}

	public function get_accounting_periods()
	{
		$this->db->order_by('start_date', 'desc');
		return $this->db->get('tbl_accounting_periods')->result();
	}

	public function get_bank_reconciliations($account_id = null, $range = array())
	{
		$this->db->select('tbl_bank_reconciliations.*, tbl_accounts.account_name, tbl_journals.reference_no, tbl_journals.date, tbl_journals.status as journal_status');
		$this->db->from('tbl_bank_reconciliations');
		$this->db->join('tbl_accounts', 'tbl_accounts.account_id = tbl_bank_reconciliations.account_id', 'left');
		$this->db->join('tbl_journals', 'tbl_journals.journal_id = tbl_bank_reconciliations.journal_id', 'left');
		if (!empty($account_id)) {
			$this->db->where('tbl_bank_reconciliations.account_id', (int) $account_id);
		}
		if (!empty($range[0]) && !empty($range[1])) {
			$this->db->where('tbl_bank_reconciliations.statement_date >=', $range[0]);
			$this->db->where('tbl_bank_reconciliations.statement_date <=', $range[1]);
		}
		$this->db->order_by('tbl_bank_reconciliations.reconciliation_id', 'desc');
		return $this->db->get()->result();
	}

	public function create_journal_reversal($journal_id, $user_id = null)
	{
		$journal = $this->db->where('journal_id', (int) $journal_id)->get('tbl_journals')->row_array();
		if (empty($journal) || !empty($journal['reversal_journal_id'])) {
			return null;
		}
		$items = $this->db->where('journal_id', (int) $journal_id)->order_by('journal_item_id', 'asc')->get('tbl_journal_items')->result_array();
		if (empty($items)) {
			return null;
		}

		$created_by = !empty($user_id) ? (int) $user_id : (int) $this->session->userdata('user_id');
		$reversal_reference = $journal['reference_no'] . '-REV';
		$existing = $this->db->where('reference_no', $reversal_reference)->get('tbl_journals')->row_array();
		if (!empty($existing)) {
			return (int) $existing['journal_id'];
		}

		$this->db->trans_start();
		$this->db->insert('tbl_journals', array(
			'reference_no' => $reversal_reference,
			'date' => $journal['date'],
			'total_debit' => $journal['total_credit'],
			'total_credit' => $journal['total_debit'],
			'total_amount' => $journal['total_amount'],
			'created_by' => $created_by,
			'status' => 'posted',
			'notes' => 'Reversal of ' . $journal['reference_no'],
			'source_module' => 'journal_reversal',
			'source_id' => (int) $journal_id,
			'is_reversal' => 1,
			'reversal_of_journal_id' => (int) $journal_id,
		));
		$reversal_journal_id = (int) $this->db->insert_id();

		foreach ($items as $item) {
			$this->db->insert('tbl_journal_items', array(
				'journal_id' => $reversal_journal_id,
				'chart_of_account_id' => (int) $item['chart_of_account_id'],
				'debit' => (float) $item['credit'],
				'credit' => (float) $item['debit'],
				'description' => 'Reversal of ' . $journal['reference_no'],
			));
		}
		$this->db->where('journal_id', (int) $journal_id)->update('tbl_journals', array('reversal_journal_id' => $reversal_journal_id));
		$this->db->trans_complete();
		return $reversal_journal_id;
	}

	public function mark_journal_reconciled($journal_id, $status = 'reconciled')
	{
		if (empty($journal_id)) {
			return false;
		}
		$data = array(
			'reconciled_status' => $status,
			'reconciled_at' => date('Y-m-d H:i:s'),
			'reconciled_by' => (int) $this->session->userdata('user_id'),
		);
		$this->db->where('journal_id', (int) $journal_id)->update('tbl_journals', $data);
		return true;
	}

	public function save_accounting_period(array $data, $id = null)
	{
		$this->_table_name = 'tbl_accounting_periods';
		$this->_primary_key = 'period_id';
		if (empty($id) && empty($data['created_by'])) {
			$data['created_by'] = (int) $this->session->userdata('user_id');
		}
		if (!empty($id)) {
			$data['updated_at'] = date('Y-m-d H:i:s');
		}
		return $this->save($data, $id);
	}
}
