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

class Report_gugatan_sederhana_jumlah_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 
				bln.id AS bulan_id,
				bln.bulan_nama,
				(SELECT COUNT(*) FROM v_perkara WHERE alur_perkara_id='".$idalurperkara."' AND LEFT(tanggal_pendaftaran,7) = CONCAT('".$year."','-',RIGHT(CONCAT('0',bln.id),2))) AS perkara_diterima,
				(SELECT GROUP_CONCAT(perkara_id) FROM v_perkara WHERE alur_perkara_id='".$idalurperkara."' AND LEFT(tanggal_pendaftaran,7) = CONCAT('".$year."','-',RIGHT(CONCAT('0',bln.id),2))) AS list_no_perkara_diterima,
				(SELECT COUNT(*) FROM v_perkara 
					WHERE alur_perkara_id='".$idalurperkara."' AND
					status_putusan_id IS NOT NULL AND
					LEFT(tanggal_putusan,7) = CONCAT('".$year."','-',RIGHT(CONCAT('0',bln.id),2))) AS perkara_putus,
				(SELECT COUNT(*) FROM v_perkara WHERE alur_perkara_id = '".$idalurperkara."' AND
					LEFT(akta_perdamaian,7) = CONCAT('".$year."','-',RIGHT(CONCAT('0',bln.id),2)) AND 
					akta_perdamaian IS NOT NULL) AS akta_perdamaian,
				(SELECT COUNT(*) FROM v_perkara WHERE alur_perkara_id = '".$idalurperkara."' AND
					LEFT(tanggal_putusan,7) = CONCAT('".$year."','-',RIGHT(CONCAT('0',bln.id),2)) AND 
					status_putusan_id='29') AS penetepan_gugur,
				(SELECT COUNT(*) FROM v_perkara WHERE alur_perkara_id='".$idalurperkara."' AND status_putusan_id=28 AND
					LEFT(tanggal_putusan,7) = CONCAT('".$year."','-',RIGHT(CONCAT('0',bln.id),2))) AS dismissal,
				(SELECT COUNT(*) FROM v_perkara WHERE alur_perkara_id='".$idalurperkara."' AND
					status_putusan_id NOT IN (28,29) AND
					LEFT(tanggal_putusan,7) = CONCAT('".$year."','-',RIGHT(CONCAT('0',bln.id),2))) AS putusan,
				((SELECT COUNT(c.jenis_kelamin) FROM perkara AS a
					LEFT JOIN perkara_pihak1 AS b ON a.perkara_id=b.perkara_id
					LEFT JOIN pihak AS c ON b.pihak_id=c.id
					WHERE a.alur_perkara_id='".$idalurperkara."' AND LEFT(a.tanggal_pendaftaran,7) = CONCAT('".$year."','-',RIGHT(CONCAT('0',bln.id),2)) AND c.jenis_kelamin='L') +
				(SELECT COUNT(c.jenis_kelamin) FROM perkara AS a
					LEFT JOIN perkara_pihak2 AS b ON a.perkara_id=b.perkara_id
					LEFT JOIN pihak AS c ON b.pihak_id=c.id
					WHERE a.alur_perkara_id='".$idalurperkara."' AND LEFT(a.tanggal_pendaftaran,7) = CONCAT('".$year."','-',RIGHT(CONCAT('0',bln.id),2)) AND c.jenis_kelamin='L')) AS laki2,
				((SELECT COUNT(c.jenis_kelamin) FROM perkara AS a
					LEFT JOIN perkara_pihak1 AS b ON a.perkara_id=b.perkara_id
					LEFT JOIN pihak AS c ON b.pihak_id=c.id
					WHERE a.alur_perkara_id='".$idalurperkara."' AND LEFT(a.tanggal_pendaftaran,7) = CONCAT('".$year."','-',RIGHT(CONCAT('0',bln.id),2)) AND c.jenis_kelamin='P') +
				(SELECT COUNT(c.jenis_kelamin) FROM perkara AS a
					LEFT JOIN perkara_pihak2 AS b ON a.perkara_id=b.perkara_id
					LEFT JOIN pihak AS c ON b.pihak_id=c.id
					WHERE a.alur_perkara_id='".$idalurperkara."' AND LEFT(a.tanggal_pendaftaran,7) = CONCAT('".$year."','-',RIGHT(CONCAT('0',bln.id),2)) AND c.jenis_kelamin='P')) AS perempuan
			FROM 
			(SELECT 
			b.id AS id,
			b.name_id AS bulan_nama, 
			CONCAT('".$year."','-',RIGHT(CONCAT('0',b.id),2)) AS months
			FROM sys_month_name AS b) AS bln
			ORDER BY bln.id");
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}
}