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

class Report_keuangan extends CI_Model{
	function get_info_pn(){
		try {
			$this->db->select('*');
			$this->db->where('id >=', 61); 
			return $this->db->get('sys_config');
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}
	function getJenisPerkara($idalurperkara){
		if(empty($idalurperkara)) return '';
		try {
			$this->db->where('id',$idalurperkara);
			return $this->db->get('alur_perkara')->row()->nama;
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}

	function get_tahun($idalurperkara){
		try {
			return $this->db->query('SELECT YEAR(min(tanggal_pendaftaran)) AS years FROM perkara WHERE alur_perkara_id IN ('.$idalurperkara.');');
		} catch (Exception $e) {
			
		}
	}

	function get_saldo_awal($period){
		try {
			return $this->db->query("SELECT SUM(IF('$period'>=LEFT(tanggal_saldo_awal,7),jumlah_saldo_awal,0)) AS saldo_awal, MAX(tanggal_saldo_awal) AS tgl_saldo_awal FROM saldo_awal WHERE alur_perkara_id IN (1,2,8,15,16);");
		} catch (Exception $e) {
			
		}
	}


	function get_stat_perkara($period,$idalurperkara){
		try {
			return '';
		} catch (Exception $e) {
			
		}
	}

	function get_lap_perkara($period,$idalurperkara,$period_saldoawal){
		try {
			return $this->db->query("SELECT kategori_id, 
							SUM(
							CASE
								WHEN LEFT(tanggal_transaksi,7)='".$period."' THEN jumlah
								ELSE 0
							END 
							) AS nominal,
							SUM(
							CASE
								WHEN LEFT(tanggal_transaksi,7)<'".$period."' AND LEFT(tanggal_transaksi,7)>='".$period_saldoawal."'  THEN jumlah
								ELSE 0
							END 
							) AS masuk_lampau,
							jenis_transaksi
							FROM v_perkara_biaya_detil 
							WHERE LEFT(tanggal_transaksi,7)<='".$period."' 
							AND alur_perkara_id IN (".$idalurperkara.")
							AND kategori_id IS NOT NULL 
							GROUP BY kategori_id;");
		} catch (Exception $e) {
			
		}
	}
}