<?php
	defined('BASEPATH') OR exit('No direct script access allowed');
	
	class Journal_model extends CI_Model
	{
		protected $table = "jurnal";
		public function getAccountData()
		{
			return $this->db->get('akun')->result_array();
		}
		
		public function getPerkiraan()
		{
			return $this->db->get('perkiraan')->result_array();
		}
		
		public function insertJournal($tipe_kas, $nominal, $bukti_transaksi){
			$idAkun = (array) $this->input->post('idAkun[]', true);
			$perkiraan = (array) $this->input->post('perkiraan[]', true);
			$nominal = (array) $nominal;
			if (!$this->isValidJournalDetail($idAkun, $perkiraan, $nominal)) {
				return false;
			}

			$this->db->trans_start();
			$data = [
			"id_jurnal" 	=> "",
			"no_trans" 		=> $this->input->post('noTransaksi', true),
			"tgl"	 		=> $this->input->post('tglTransaksi', true),
			"keterangan" 	=> $this->input->post('keterangan', true),
			"bukti" 	    => $bukti_transaksi,
			"id_user" 		=> $this->input->post('idUser', true)
			];
			
			$this->db->insert('jurnal', $data);
			$jurnal_id 	= $this->db->insert_id();

		    $result = array();
			foreach($idAkun AS $key => $val){
				$result[] = array(
				'id_jurnal_detail' 	=> '',
				'id_jurnal' 		=> $jurnal_id,
				'id_akun'			=> (int) $idAkun[$key],
				'tipe_kas'			=> $tipe_kas,
				'id_perkiraan'		=> (int) $perkiraan[$key],
				'nilai'				=> (int) $nominal[$key],
				);
			}    
			$this->db->insert_batch('jurnal_detail', $result);
			$this->db->trans_complete();

			return $this->db->trans_status();
		}

		private function isValidJournalDetail($accountIds, $sides, $amounts)
		{
			$count = count($accountIds);
			if ($count < 2 || $count !== count($sides) || $count !== count($amounts)) {
				return false;
			}

			$ids = array_values(array_unique(array_map('intval', $accountIds)));
			if (in_array(0, $ids, true)) {
				return false;
			}
			$validCount = $this->db->where_in('id_akun', $ids)->count_all_results('akun');
			if ($validCount !== count($ids)) {
				return false;
			}

			$debit = 0;
			$credit = 0;
			foreach ($sides as $key => $side) {
				$side = (int) $side;
				$amount = (int) $amounts[$key];
				if (!in_array($side, array(1, 2), true) || $amount <= 0) {
					return false;
				}
				$side === 1 ? $debit += $amount : $credit += $amount;
			}

			return $debit === $credit && $debit > 0;
		}
		
		// datatables server side jurnal umum
		var $column_order = array(
    	'jurnal.id_jurnal', 'jurnal.no_trans', 'jurnal.tgl', 'jurnal.keterangan', 'jurnal.bukti', 'jurnal.id_user'
    	); //set column field database for datatable orderable
		var $column_search = array('jurnal.no_trans', 'jurnal.tgl'); //set column field database for datatable searchable 
		var $order = array('jurnal.no_trans' => 'desc'); // default order 
		
		private function _get_datatables_query()
		{
			$this->db->select('jurnal.*, SUM(jurnal_detail.nilai) AS nominal', false);
			$this->db->from('jurnal');
			$this->db->join('jurnal_detail', 'jurnal.id_jurnal = jurnal_detail.id_jurnal', 'left');
			$this->db->where('jurnal_detail.id_perkiraan', 1);
			$this->db->group_by('jurnal.id_jurnal');
			
			$i = 0;
			
			foreach ($this->column_search as $item) // loop column 
			{
				if ($_POST['search']['value']) // if datatable send POST for search
				{
					
					if ($i === 0) // first loop
					{
						$this->db->group_start(); // open bracket. query Where with OR clause better with bracket. because maybe can combine with other WHERE with AND.
						$this->db->like($item, $_POST['search']['value']);
						} else {
						$this->db->or_like($item, $_POST['search']['value']);
					}
					
					if (count($this->column_search) - 1 == $i) //last loop
                    $this->db->group_end(); //close bracket
				}
				$i++;
			}
			
			if (isset($_POST['order'])) // here order processing
			{
				$this->db->order_by($this->column_order[$_POST['order']['0']['column']], $_POST['order']['0']['dir']);
				} else if (isset($this->order)) {
				$order = $this->order;
				$this->db->order_by(key($order), $order[key($order)]);
			}
		}
		
		function get_datatables()
		{
			$this->_get_datatables_query();
			if ($_POST['length'] != -1)
            $this->db->limit($_POST['length'], $_POST['start']);
			$query = $this->db->get();
			return $query->result_array();
		}
		
		function count_filtered()
		{
			$this->_get_datatables_query();
			$query = $this->db->get();
			return $query->num_rows();
		}
		
		public function count_all()
		{
			$this->db->select('jurnal.*, SUM(jurnal_detail.nilai) AS nominal', false);
			$this->db->from('jurnal');
			$this->db->join('jurnal_detail', 'jurnal.id_jurnal = jurnal_detail.id_jurnal', 'left');
			$this->db->where('jurnal_detail.id_perkiraan', 1);
			$this->db->group_by('jurnal.id_jurnal');
			return $this->db->count_all_results();
		}
		// tutup datatables serverside
		
		
		
		public function getJournal()
		{
			$this->db->select('jurnal.*, SUM(jurnal_detail.nilai) AS nominal');
			$this->db->from('jurnal_detail');
			$this->db->join('jurnal', 'jurnal_detail.id_jurnal = jurnal.id_jurnal');
			$this->db->where('jurnal_detail.id_perkiraan', 1);
			$this->db->group_by('jurnal_detail.id_jurnal');
			$this->db->order_by('jurnal.no_trans', 'DESC');
			$query = $this->db->get()->result_array();
			return $query;
		}
		
		public function get_jurnal_by_id($id)
		{
			$this->db->select('
			jurnal_detail.id_akun, jurnal_detail.nilai, jurnal_detail.id_perkiraan, 
			jurnal.id_jurnal, jurnal.tgl, jurnal.no_trans, jurnal.keterangan, jurnal.bukti, 
			akun.noakun, akun.nama, 
			kelompok_akun.kel_akun, 
			perkiraan.nama AS perkiraan
			');
			$this->db->from('jurnal_detail');
			$this->db->join('jurnal', 'jurnal_detail.id_jurnal = jurnal.id_jurnal');
			$this->db->join('akun', 'jurnal_detail.id_akun = akun.id_akun');
			$this->db->join('kelompok_akun', 'akun.id_kelompok_akun = kelompok_akun.id_kelompok_akun');
			$this->db->join('perkiraan', 'jurnal_detail.id_perkiraan = perkiraan.id_perkiraan');
			$this->db->where('jurnal_detail.id_jurnal', $id); 
			$query = $this->db->get();
			$result = $query->num_rows() > 0 ? $query->result_array() : false;
			return $result;
		}
		
		public function delete_journal($id)
		{
			// $this->db->delete('jurnal_detail', array('id_jurnal' => $id));
			$this->db->delete('jurnal', array('id_jurnal' => $id));
			return true;
		}
		
		public function check_number_tramsaction()
		{
			$prefix = date('ymd');
			$this->db->select('MAX(no_trans) AS no_trans', false);
			$this->db->from('jurnal');
			$this->db->like('no_trans', $prefix, 'after');
			$this->db->where('CHAR_LENGTH(no_trans)', 10);
			$this->db->where("no_trans REGEXP '^[0-9]{10}$'", null, false);
			$hasil = $this->db->get()->row();
			return $hasil ? $hasil->no_trans : null;
		}
		
		public function check_number_akun($id)
		{
			$query = $this->db->query("SELECT MAX(noakun) as noakun from akun where id_kelompok_akun=".$id);
			$hasil = $query->row();
			return $hasil->noakun;
		}
		
		public function getIdPerkiraan($id)
		{
			return $this->db->get_where('perkiraan', ['id_perkiraan' => $id])->row_array();
		}
		
		public function changeJournal($id, $tglTransaksi, $noTransaksi, $keterangan, $idAkun, $nominal, $tipe_kas, $bukti_transaksi)
		{
			$this->db->trans_start();
			if($bukti_transaksi == "") {
				$data = [
				"no_trans" 		=> $noTransaksi,
				"tgl"	 		=> $tglTransaksi,
				"keterangan" 	=> $keterangan,
				"id_user" 		=> $this->input->post('idUser', true)
				];
				} else {
				$data = [
				"no_trans" 		=> $noTransaksi,
				"tgl"	 		=> $tglTransaksi,
				"keterangan" 	=> $keterangan,
				"bukti" 		=> $bukti_transaksi,
				"id_user" 		=> $this->input->post('idUser', true)
				];
			}
			$this->db->where('id_jurnal', $id);
			$this->db->update('jurnal', $data);
			
			//DELETE DETAIL PACKAGE
			$this->db->delete('jurnal_detail', array('id_jurnal' => $id));
			
			$result = array();
			foreach($idAkun AS $key => $val){
				$result[] = array(
				'id_jurnal_detail' 	=> '',
				'id_jurnal' 		=> $id,
				'id_akun'			=> $_POST['idAkun'][$key],
				'tipe_kas'			=> $tipe_kas,
				'id_perkiraan'		=>$_POST['perkiraan'][$key], 
				'nilai'				=>$nominal[$key], 
				);
			}    
			
			//MULTIPLE INSERT TO DETAIL TABLE
			$this->db->insert_batch('jurnal_detail', $result);
			$this->db->trans_complete();
		}
		
		public function reportJournal($tglAwal, $tglAkhir)
		{
			$this->db->select('
			jurnal.id_jurnal, jurnal.tgl, jurnal.no_trans, 
			jurnal_detail.id_perkiraan, jurnal_detail.nilai, 
			akun.noakun, akun.nama, 
			kelompok_akun.kel_akun');
			$this->db->from('jurnal_detail');
			$this->db->join('jurnal', 'jurnal_detail.id_jurnal = jurnal.id_jurnal');
			$this->db->join('akun', 'jurnal_detail.id_akun = akun.id_akun');
			$this->db->join('kelompok_akun', 'akun.id_kelompok_akun = kelompok_akun.id_kelompok_akun');
			$array = array('jurnal.tgl >=' => $tglAwal, 'jurnal.tgl <=' => $tglAkhir);
			$this->db->where($array);
			// $this->db->order_by('jurnal.id', 'ASC');
			$this->db->order_by('jurnal.tgl', 'ASC');
			$this->db->order_by('jurnal_detail.id_jurnal_detail', 'ASC');
			$query = $this->db->get()->result_array();
			
			return $query;
		}
		
		public function chartOmset($bulan){
			$like = '2023-'. $bulan;
			
			$this->db->select('jurnal_detail.nilai');
			$this->db->from('jurnal_detail');
			$this->db->join('jurnal', 'jurnal_detail.id_jurnal = jurnal.id_jurnal');
			$this->db->join('akun', 'jurnal_detail.id_akun = akun.id_akun');
			$this->db->join('kelompok_akun', 'akun.id_kelompok_akun = kelompok_akun.id_kelompok_akun');
			$this->db->where('jurnal_detail.id_perkiraan', 2);
			$array = array(410);
			$this->db->where_in('kelompok_akun.kel_akun', $array);
			$this->db->like('jurnal.tgl', $like);
			$this->db->group_by('jurnal_detail.id_jurnal');
			$this->db->order_by('jurnal.no_trans', 'DESC');
			$query = $this->db->get()->result_array();
			return $query;
		}
		
		function get_Journal($params = array()){
		    $this->db->select('jurnal.*, SUM(jurnal_detail.nilai) AS nominal', false);
		    $this->db->from('jurnal');
		    $this->db->join('jurnal_detail', 'jurnal.id_jurnal = jurnal_detail.id_jurnal', 'left');
		    $this->db->group_by('jurnal_detail.id_jurnal');

		    if(array_key_exists("where", $params)){
		        foreach($params['where'] as $key => $val){
		            $this->db->where($key, $val);
		        }
		    }

		    if(array_key_exists("search", $params)){
		        if(!empty($params['search']['keywords'])){
		            $this->db->like('jurnal.no_trans', $params['search']['keywords']);
		            $this->db->or_like('jurnal.keterangan', $params['search']['keywords']);
		            $this->db->group_by('jurnal_detail.id_jurnal');
		        }
		    }

		    // =========================
		    // FIX SORTING (Terbaru/Terlama)
		    // - jurnal.tgl hanya DATE, jadi tambahkan id_jurnal sebagai tie-breaker
		    // - jangan override lagi di bawah
		    // =========================
		    $sortBy = 'DESC';
		    if (isset($params['search']['sortBy'])) {
		        $sb = strtoupper(trim((string)$params['search']['sortBy']));
		        if (in_array($sb, ['ASC','DESC'], true)) $sortBy = $sb;
		    }
		    $this->db->order_by('jurnal.tgl', $sortBy);
		    $this->db->order_by('jurnal.id_jurnal', $sortBy);

		    if(array_key_exists("returnType",$params) && $params['returnType'] == 'count'){
		        $result = $this->db->count_all_results();
		    }else{
		        if(array_key_exists("id", $params) || (array_key_exists("returnType", $params) && $params['returnType'] == 'single')){
		            $query  = $this->db->get();
		            $result = ($query->num_rows() > 0) ? $query->result_array() : FALSE;
		        }else{
		            if(array_key_exists("start",$params) && array_key_exists("limit",$params)){
		                $this->db->limit($params['limit'],$params['start']);
		            }elseif(!array_key_exists("start",$params) && array_key_exists("limit",$params)){
		                $this->db->limit($params['limit']);
		            }

		            $query  = $this->db->get();
		            $result = ($query->num_rows() > 0) ? $query->result() : FALSE;
		        }
		    }

		    return $result;
		}

		public function cek_id($id)
		{
			$query = $this->db->select('id_jurnal');
			$query = $this->db->where('id_jurnal', $id);
			$query = $this->db->get($this->table);
			$result = $query->num_rows();
			
			if ( $result < 1 )
			$result = 0;
			
			return $result;
		}
		public function delete_by_id($val = 0)
		{
			if ($this->cek_id($val) == 1) 
			{
				$this->db->where('id_jurnal', $val)->delete($this->table);
				return TRUE;
			}
			else
			{
				return FALSE;
			}
		}
		
		public function reportAkun()
		{
			$this->db->select('
			jurnal.id_jurnal, jurnal.tgl, jurnal.no_trans, 
			jurnal_detail.id_perkiraan, jurnal_detail.nilai, 
			akun.noakun, akun.nama, 
			kelompok_akun.kel_akun');
			$this->db->from('jurnal_detail');
			$this->db->join('jurnal', 'jurnal_detail.id_jurnal = jurnal.id_jurnal');
			$this->db->join('akun', 'jurnal_detail.id_akun = akun.id_akun');
			$this->db->join('kelompok_akun', 'akun.id_kelompok_akun = kelompok_akun.id_kelompok_akun');
			$this->db->order_by('jurnal.tgl', 'ASC');
			$this->db->order_by('jurnal_detail.id_jurnal_detail', 'ASC');
			$query = $this->db->get()->result_array();
			
			return $query;
		}
	}
	
// , 'akun.id' => 8, 'akun.id' => 41, 'akun.id' => 42
