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

class Report_penanganan_perkara 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_rekap($idalurperkara,$year){
		try {
			return $this->db->query("select 
			b.id as bulan_id
			,b.name_id as bulan_nama
			,(
			SELECT SUM(if(tanggal_minutasi is not null and DATEDIFF(tanggal_minutasi,tanggal_pendaftaran) <= 120 
			and (concat('".$year."-', RIGHT(CONCAT('0',b.id),2)) <= left(CURDATE(),7))
			,1,0)) 
			FROM v_perkara where alur_perkara_id in (".$idalurperkara.") and left(tanggal_pendaftaran,7) <= concat('".$year."-', RIGHT(CONCAT('0',b.id),2))
			) as SudahMinutasiKurang
			,(
			SELECT SUM(if(tanggal_minutasi is not null and DATEDIFF(tanggal_minutasi,tanggal_pendaftaran) > 120 
			and (concat('".$year."-', RIGHT(CONCAT('0',b.id),2)) <= left(CURDATE(),7))
			,1,0)) 
			FROM v_perkara where alur_perkara_id in (".$idalurperkara.") and left(tanggal_pendaftaran,7) <= concat('".$year."-', RIGHT(CONCAT('0',b.id),2))
			) as SudahMinutasiLebih

			,(
			SELECT SUM(if(tanggal_minutasi is null and DATEDIFF(CURDATE(),tanggal_pendaftaran) <= 120 
			and (concat('".$year."-', RIGHT(CONCAT('0',b.id),2)) <= left(CURDATE(),7))
			,1,0)) 
			FROM v_perkara where alur_perkara_id in (".$idalurperkara.") and left(tanggal_pendaftaran,7) <= concat('".$year."-', RIGHT(CONCAT('0',b.id),2))
			) as BelumMinutasiKurang
			,(
			SELECT SUM(if(tanggal_minutasi is null and DATEDIFF(CURDATE(),tanggal_pendaftaran) > 120 
			and (concat('".$year."-', RIGHT(CONCAT('0',b.id),2)) <= left(CURDATE(),7))
			,1,0)) 
			FROM v_perkara where alur_perkara_id in (".$idalurperkara.") and left(tanggal_pendaftaran,7) <= concat('".$year."-', RIGHT(CONCAT('0',b.id),2))
			) as BelumMinutasiLebih

			from `sys_month_name` as b
			group by bulan_id
			,bulan_nama
			order by bulan_id");
		} catch (Exception $e) {
			
		}
	}
}