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

class m_template_mil_grasi extends CI_Model {
	function getDataPN(){
		try{
			$sql = "SELECT * FROM 
					(SELECT value AS NamaPN FROM sys_config WHERE id = 62) AS T1,
					(SELECT value AS KetuaPNNama FROM sys_config WHERE id = 64) AS T2,
					(SELECT value AS AlamatPN FROM sys_config WHERE id = 63) AS T3,
					(SELECT value AS WakilKetuaPNNama FROM sys_config WHERE id = 66) AS T4,
					(SELECT value AS PansekPNNama FROM sys_config WHERE id = 68) AS T5,
					(SELECT value AS WaPanNama FROM sys_config WHERE id = 70) AS T6,
					(SELECT value AS IDPN FROM sys_config WHERE id = 81) AS T7,
					(SELECT value AS PansekPNNip FROM sys_config WHERE id = 69) AS T8,
					(SELECT value AS PansekPNPangkat FROM sys_config WHERE id = 86) AS T9, 
					(SELECT value AS KodePN FROM sys_config WHERE id = 61) AS T10"; 
			$query=$this->db->query($sql);
			return $query;	
		}catch(Exception $e) { return false; }
	}
	function getDataPejabat(){
		try {
			return $this->db->query("SELECT b.id,a.namaPengadilan,b.id,IF (b.nama = 'PanSekNama','Panitera Sekretaris','Wakil Panitera' ) AS pejabat,b.pw,        
									 	(SELECT value AS nip FROM sys_config WHERE id IN (if(b.id='68','69','71'))) AS nip,
									  	(SELECT value AS nip FROM sys_config WHERE id IN (if(b.id='68','86','87'))) AS pangkat
									FROM    (SELECT id,value AS namaPengadilan FROM sys_config WHERE id=62) AS a,
									         (SELECT id,value AS pw, name AS nama FROM sys_config WHERE id IN (68,70)) AS b");
		} catch (Exception $e) {
		}
	}
	function mDetilGrasi($grasiId){
		try{
			$sql="select pp2.alamat as alamat_terdakwa, 
						pp2.pangkat as pangkat_terdakwa, 
						pp2.nrp as nrp_terdakwa, 
						pp2.jabatan as jabatan_terdakwa, 
						pp2.kesatuan as kesatuan_terdakwa, 
						p.tempat_lahir as tempat_lahir_terdakwa,
						p.tanggal_lahir as tanggal_lahir_terdakwa,
						p.jenis_kelamin as jenis_kelamin_terdakwa,
						a.nama as agama_terdakwa,
						pg.* from 
					perkara_grasi pg 
					left join (select perkara_id, pihak_id, alamat, pangkat, nrp, jabatan, kesatuan from perkara_pihak2) pp2 on pp2.perkara_id=pg.perkara_id and pp2.pihak_id=pg.terdakwa_id
					left join pihak p on p.id=pp2.pihak_id
					left join agama a on a.id=p.agama_id
					where pg.id=".$grasiId;
			return $this->db->query($sql);
		} catch (Exception $e) {
         	log_message('error', $e);
		}
	}
	
	function getDataPihak($pihak_id){
		try {
			$sql="Select tempat_lahir,tanggal_lahir,jenis_kelamin, alamat, a.nama as agama_nama,pekerjaan,
						n.nama as warga_negara
				from pihak p 
					left join agama a on a.id=p.agama_id
					left join negara n on p.warga_negara_id=n.id
				where p.id=".$pihak_id;
			$res=$this->db->query($sql);
			if ($res->num_rows()>0){
				return $res->row();
			}else{
				return '';
			}
		} catch (Exception $e) {
			return false;
		}

	}
	
	function getDataOditur($idperkara){
		try{
			if(!is_numeric($idperkara)) { return ''; }
			$sql = "SELECT a.perkara_id, a.pihak_id,a.nama, b.nip AS nrp, b.pangkat, b.jabatan, b.kesatuan
					FROM 
					perkara_pihak1 AS a 
					LEFT JOIN pihak_jaksa AS b ON a.pihak_id=b.pihak_id
					WHERE a.perkara_id=".$idperkara." ";
			$query=$this->db->query($sql);
			return $query;
		}catch(Exception $e) { return false; }
	}

	function getDataPutusan($idperkara, $idput=''){
		try {
			$sql=("SELECT tahapan.* FROM 
                    (SELECT '1' AS idPut,
                    (SELECT value FROM sys_config WHERE id = 62) AS tahapan,
                    (SELECT nomor_perkara FROM perkara WHERE perkara_id=1) AS nomorPerkara,
                    	tanggal_putusan AS tglPutus, perkara_id, tanggal_bht as tanggal_bht, pemberitahuan_putusan_pihak2 as pemberitahuan_putusan
                    FROM perkara_putusan AS a 
                    UNION ALL
                    SELECT '2' AS idPut,( SELECT value  FROM sys_config WHERE id=76 ),
                    	nomor_perkara_banding,putusan_banding,perkara_id, DATE_ADD(pemberitahuan_putusan_banding, INTERVAL 14 DAY) as tanggal_bht, pemberitahuan_putusan_banding as pemberitahuan_putusan 
                    FROM perkara_banding AS a
                	UNION ALL
                    SELECT '3' AS idPut,'MAHKAMAH AGUNG REPUBLIK INDONESIA',
                    	nomor_perkara_kasasi,putusan_kasasi,perkara_id, DATE_ADD(pemberitahuan_putusan_kasasi, INTERVAL 14 DAY) as tanggal_bht, pemberitahuan_putusan_kasasi as pemberitahuan_putusan 
                    FROM perkara_kasasi AS b
                    UNION ALL
                    SELECT '4' AS idPut,'MAHKAMAH AGUNG REPUBLIK INDONESIA',
                    	nomor_perkara_pk,putusan_pk,perkara_id, DATE_ADD(pemberitahuan_putusan_pk, INTERVAL 14 DAY) as tanggal_bht, pemberitahuan_putusan_pk as pemberitahuan_putusan 
                    FROM perkara_pk AS b) AS tahapan 
                WHERE tahapan.perkara_id=".$idperkara);
			if ($idput!=''){
				$sql.=" AND idPut=".$idput;
			}
			return $this->db->query($sql);
		} catch (Exception $e) {
		}
	}


	function getDataDocumentTemplate($idalurperkara,$idatahapan,$idproses,$notemplate,$keyword)
	{
		if (empty($keyword) or $keyword=='')
		{
		$sql="SELECT a.nama AS jnsperkara,b.nomor AS notenmplate,b.kode AS kdtemplate,b.nama AS namatemplate 
				FROM alur_perkara a,template_dokumen b 
				WHERE a.id=b.alur_perkara_id 	AND a.id=$idalurperkara 
												AND b.tahapan_id =$idatahapan 
												AND b.proses_id=$idproses 
												AND b.nomor=$notemplate";
		}
		else
		{
		$sql="SELECT a.nama AS jnsperkara,b.nomor AS notenmplate,b.kode AS kdtemplate,b.nama AS namatemplate 
				FROM alur_perkara a,template_dokumen b 
				WHERE a.id=b.alur_perkara_id 	AND a.id=$idalurperkara 
												AND b.tahapan_id =$idatahapan 
												AND b.proses_id=$idproses 
												AND b.nama LIKE '%$keyword%'";
		}
		$query=$this->db->query($sql);
		return $query;
	}
}
