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

class m_template_pidana_akta_pencabutan_peninjauan_kembali extends CI_Model {
	#getDataPejabat
	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
                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 getInfo($idperkara){
		try {
			return $this->db->query("SELECT 
				CONCAT(pihak_nama,' / ',status_pihak_text,' / ',pihak_asal_text) AS pihak_nama,
				status_pihak_text,
				IF(pihak_diwakili='Y', CONCAT(pemohon_nama), 'Tidak Diwakili') AS diwakili
				FROM perkara_pk_detil WHERE perkara_id=$idperkara AND permohonan_pk IS NOT NULL AND status_pihak_id=1 ");
		} catch (Exception $e) {
		}
	}


	function getDataPutusan($idperkara){
		try {
			return $this->db->query("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=$idperkara) AS nomorPerkara,
					tanggal_putusan AS tglPutus, 
					perkara_id 
					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 
                    FROM perkara_banding AS a
        		UNION ALL SELECT '3' AS idput, 'Mahkamah Agung RI', nomor_perkara_kasasi,putusan_kasasi,perkara_id
		 			FROM perkara_kasasi WHERE perkara_id=$idperkara) AS tahapan 
                	WHERE tahapan.perkara_id=$idperkara");
		} catch (Exception $e) {
		}
	}


	function getDataJaksaPK($idperkara){
		try {
			$qry="	SELECT 	q.pihak_id, 
							b.nama, 
							IF (b.pekerjaan IS NULL ,'#isi dulu di aplikasi#',b.pekerjaan ) AS pekerjaan,
							IF (r.NIP IS NULL ,'#isi dulu di aplikasi#',r.NIP ) AS NIP,
							IF (r.kejaksaan IS NULL ,'#isi dulu di aplikasi#',r.kejaksaan ) AS kejaksaan
					FROM 	pihak b, perkara_pk_detil AS p 
							LEFT JOIN perkara_pihak1 AS q ON p.perkara_id=q.perkara_id 
							LEFT JOIN pihak_jaksa AS r ON q.pihak_id=r.pihak_id
					WHERE 	q.pihak_id=b.id AND
							p.pihak_asal='1' AND 
							p.pihak_id=q.pihak_id AND 
							p.perkara_id=$idperkara";
			$query=$this->db->query($qry);
			return $query;
		} catch (Exception $e) {
		}
	}

	function getDataJaksa($idperkara){
		try {
			return $this->db->query("SELECT p.* , q.*
				FROM 
				perkara_kasasi_detil AS p 
				LEFT JOIN perkara_pihak1 AS q ON p.perkara_id=q.perkara_id 
				LEFT JOIN pihak_jaksa AS r ON q.pihak_id=r.pihak_id
				WHERE 
				p.pihak_asal='1' AND p.pihak_id=q.pihak_id AND p.pihak_diwakili='T' AND  p.perkara_id=$idperkara");
		} catch (Exception $e) {
		}
	}

	function infoDataTerdakwa($idperkara){
		try {
			return $this->db->query("SELECT pihak_id,perkara_id, 
				status_pihak_id, 
				permohonan_pk AS tglPemohonPK, 
				permohonan_pk,
				pihak_asal, 
				pihak_nama, 
				pihak_diwakili, 
				pemohon_id AS pengacara
			FROM 	perkara_pk_detil 
			WHERE 	permohonan_pk IS NOT NULL AND 
					pihak_asal=2 AND pihak_diwakili='T' AND perkara_id=$idperkara ");
		} catch (Exception $e) {
		}
	}

	function infoDataPH_PK($idperkara){
		try {
			return $this->db->query("SELECT pihak_id,perkara_id, 
				status_pihak_id, 
				permohonan_pk AS tglPemohonPK, 
				pihak_asal, 
				pihak_nama, 
				pihak_diwakili, 
				pemohon_id AS pengacara_id,
				pemohon_nama AS namaPengacara
			FROM 	perkara_pk_detil 
			WHERE 	permohonan_pk IS NOT NULL AND pihak_asal='2' AND pihak_diwakili='Y' AND
					perkara_id=$idperkara");
		} catch (Exception $e) {
		}
	}


	function cetak_cabut_ph_pk($idperkara,$idpembuatakta,$idput,$pengacara_id){
		try {
			$sql="SELECT 
				phk2.pihak_id,
				f.nomor_perkara, 
				b.nama AS nama_terdakwa,
				a.permohonan_pk AS tglPk,
				a.pemohon_id AS pengacara_id,
				a.pemohon_nama, 
				h.catatan_putusan_pk AS tglCabut,
				IF (b.alamat IS NULL ,'#data belum lengkap#',b.`alamat` ) AS alamat, 
				IF (b.tempat_lahir IS NULL, '#data belum lengkap#',b.`tempat_lahir`) AS tempat_lahir,
				IF (b.tanggal_lahir IS NULL,'#data belum lengkap#', b.`tanggal_lahir`) AS tanggal_lahir, 
				IF (b.jenis_kelamin = 'L', 'Laki-Laki','Perempuan') AS jenis_kelamin, 
				IF (b.warga_negara IS NULL, '#data belum lengkap#',b.`warga_negara`) AS warga_negara, 
				IF (b.agama_nama IS NULL,'#data belum lengkap#',b.`agama_nama`) AS agama_nama, 
				IF (b.pekerjaan IS NULL,'#data belum lengkap#',b.`pekerjaan`) AS pekerjaan_terdakwa,
				d.pw, IF (d.jabatan ='PanSekNama','Panitera','Wakil Panitera') AS jabatan,
				e.value AS nama_pengadilan, put.idPut, put.tahapan, put.nomorPerkara , put.permohonan_pk, put.putusan, pihak_jaksa.*
		FROM perkara AS f
		LEFT JOIN perkara_pk_detil AS a ON f.perkara_id = a.perkara_id
		LEFT JOIN perkara_pihak2 AS phk2 ON a.pihak_id=phk2.pihak_id
		LEFT JOIN pihak AS b ON phk2.pihak_id=b.id
		LEFT JOIN perkara_pk AS h ON f.perkara_id=h.perkara_id
		LEFT JOIN (SELECT id,name AS jabatan,value AS pw FROM sys_config) AS d ON d.id IN (68,70) AND d.id=$idpembuatakta
		LEFT JOIN (SELECT id,value  FROM sys_config) AS e ON e.id=62
		LEFT JOIN (SELECT '1' AS idPut,(SELECT value FROM sys_config WHERE id=62) AS tahapan,nomor_perkara_pn AS nomorPerkara,putusan_pn AS putusan, permohonan_pk AS permohonan_pk FROM perkara_pk AS a WHERE a.perkara_id=$idperkara
			UNION ALL
				SELECT '2' AS idPut,(SELECT value FROM sys_config WHERE id=76) AS tahapan,nomor_perkara_banding AS nomorPerkara,putusan_banding AS putusan, permohonan_pk AS permohonan_pk FROM perkara_pk AS a WHERE a.perkara_id=$idperkara
			UNION ALL
				SELECT '3' AS idPut,'Kasasi Mahkamah Agung' AS tahapan,nomor_perkara_kasasi AS nomorPerkara,putusan_kasasi AS putusan, permohonan_pk AS permohonan_pk FROM perkara_pk AS a WHERE a.perkara_id=$idperkara) AS put ON put.idput=$idput
		LEFT JOIN (SELECT pp1.perkara_id AS pp1_perkara_id, 
			pp1.pihak_id AS pp1_pihak_id,
			pp1.nama AS nama_jaksa, 
			pj.pihak_id, pj.nip AS nip_jaksa, 
			pj.kejaksaan AS kejaksaan, 
			pj.alamat_kantor AS alamat_jaksa
		   FROM perkara_pihak1 AS pp1
		   LEFT JOIN pihak_jaksa AS pj ON pp1.pihak_id=pj.pihak_id
	   		WHERE pp1.perkara_id=$idperkara) AS pihak_jaksa ON pihak_jaksa.pp1_perkara_id=$idperkara
		WHERE a.perkara_id=$idperkara AND a.status_pihak_id =1 AND a.pihak_id=phk2.pihak_id AND a.permohonan_pk IS NOT NULL";

		if ($pengacara_id!='')
		{
		 $sql=$sql." AND a.pemohon_id=$pengacara_id";
		}
		$query=$this->db->query($sql);
		return $query;
		} catch (Exception $e) {
		}
	}

	function cetak_cabut_terdakwa_pk($idperkara,$idpembuatakta,$idput,$terdakwa_id){
		try {
			$qry="SELECT 
				phk2.pihak_id,
				f.nomor_perkara, 
				b.nama AS nama_terdakwa,
				a.permohonan_pk AS tglPk,
				a.pemohon_id AS pengacara_id,
				a.pemohon_nama, 
				h.catatan_putusan_pk AS tglCabut,
				IF (b.alamat IS NULL ,'#data belum lengkap#',b.`alamat` ) AS alamat, 
				IF (b.tempat_lahir IS NULL, '#data belum lengkap#',b.`tempat_lahir`) AS tempat_lahir,
				IF (b.tanggal_lahir IS NULL,'#data belum lengkap#', b.`tanggal_lahir`) AS tanggal_lahir, 
				IF (b.jenis_kelamin = 'L', 'Laki-Laki','Perempuan') AS jenis_kelamin, 
				IF (b.warga_negara IS NULL, '#data belum lengkap#',b.`warga_negara`) AS warga_negara, 
				IF (b.agama_nama IS NULL,'#data belum lengkap#',b.`agama_nama`) AS agama_nama, 
				IF (b.pekerjaan IS NULL,'#data belum lengkap#',b.`pekerjaan`) AS pekerjaan_terdakwa,
				d.pw, IF (d.jabatan ='PanSekNama','Panitera','Wakil Panitera') AS jabatan,
				e.value AS nama_pengadilan, put.idPut, put.tahapan, put.nomorPerkara , put.permohonan_pk, put.putusan, pihak_jaksa.*
		FROM perkara AS f
		LEFT JOIN perkara_pk_detil AS a ON f.perkara_id = a.perkara_id
		LEFT JOIN perkara_pihak2 AS phk2 ON a.pihak_id=phk2.pihak_id
		LEFT JOIN pihak AS b ON phk2.pihak_id=b.id
		LEFT JOIN perkara_pk AS h ON f.perkara_id=h.perkara_id
		LEFT JOIN (SELECT id,name AS jabatan,value AS pw FROM sys_config) AS d ON d.id IN (68,70) AND d.id=$idpembuatakta
		LEFT JOIN (SELECT id,value  FROM sys_config) AS e ON e.id=62
		LEFT JOIN (SELECT '1' AS idPut,(SELECT value FROM sys_config WHERE id=62) AS tahapan,nomor_perkara_pn AS nomorPerkara,putusan_pn AS putusan, permohonan_pk AS permohonan_pk FROM perkara_pk AS a WHERE a.perkara_id=$idperkara
			UNION ALL
				SELECT '2' AS idPut,(SELECT value FROM sys_config WHERE id=76) AS tahapan,nomor_perkara_banding AS nomorPerkara,putusan_banding AS putusan, permohonan_pk AS permohonan_pk FROM perkara_pk AS a WHERE a.perkara_id=$idperkara
			UNION ALL
				SELECT '3' AS idPut,'Kasasi Mahkamah Agung' AS tahapan,nomor_perkara_kasasi AS nomorPerkara,putusan_kasasi AS putusan, permohonan_pk AS permohonan_pk FROM perkara_pk AS a WHERE a.perkara_id=$idperkara) AS put ON put.idput=$idput
		LEFT JOIN (SELECT pp1.perkara_id AS pp1_perkara_id, 
			pp1.pihak_id AS pp1_pihak_id,
			pp1.nama AS nama_jaksa, 
			pj.pihak_id, pj.nip AS nip_jaksa, 
			pj.kejaksaan AS kejaksaan, 
			pj.alamat_kantor AS alamat_jaksa
		   FROM perkara_pihak1 AS pp1
		   LEFT JOIN pihak_jaksa AS pj ON pp1.pihak_id=pj.pihak_id
	   		WHERE pp1.perkara_id=$idperkara) AS pihak_jaksa ON pihak_jaksa.pp1_perkara_id=$idperkara
		WHERE a.perkara_id=$idperkara AND a.status_pihak_id =1 AND a.pihak_id=phk2.pihak_id AND a.permohonan_pk IS NOT NULL AND a.pihak_id=$terdakwa_id";
		$query=$this->db->query($qry);
		return $query;
		} catch (Exception $e) {
		}
	}

	function cetak_cabut_jpu_pk($idperkara,$idpembuatakta,$idput,$jpu_id){
		try {
			return $this->db->query("SELECT 
				phk2.pihak_id,
				f.nomor_perkara, 
				b.nama AS nama_terdakwa,
				a.permohonan_pk AS tglPk,
				a.pemohon_id AS pengacara_id,
				a.pemohon_nama, 
				h.catatan_putusan_pk AS tglCabut,
				IF (b.alamat IS NULL ,'#data belum lengkap#',b.`alamat` ) AS alamat, 
				IF (b.tempat_lahir IS NULL, '#data belum lengkap#',b.`tempat_lahir`) AS tempat_lahir,
				IF (b.tanggal_lahir IS NULL,'#data belum lengkap#', b.`tanggal_lahir`) AS tanggal_lahir, 
				IF (b.jenis_kelamin = 'L', 'Laki-Laki','Perempuan') AS jenis_kelamin, 
				IF (b.warga_negara IS NULL, '#data belum lengkap#',b.`warga_negara`) AS warga_negara, 
				IF (b.agama_nama IS NULL,'#data belum lengkap#',b.`agama_nama`) AS agama_nama, 
				IF (b.pekerjaan IS NULL,'#data belum lengkap#',b.`pekerjaan`) AS pekerjaan_terdakwa,
				d.pw, IF (d.jabatan ='PanSekNama','Panitera','Wakil Panitera') AS jabatan,
				e.value AS nama_pengadilan, put.idPut, put.tahapan, put.nomorPerkara , put.permohonan_pk, put.putusan, pihak_jaksa.*
		FROM perkara AS f
		LEFT JOIN perkara_pk_detil AS a ON f.perkara_id = a.perkara_id
		LEFT JOIN perkara_pihak1 AS phk2 ON a.pihak_id=phk2.pihak_id
		LEFT JOIN pihak AS b ON phk2.pihak_id=b.id
		LEFT JOIN perkara_pk AS h ON f.perkara_id=h.perkara_id
		LEFT JOIN (SELECT id,name AS jabatan,value AS pw FROM sys_config) AS d ON d.id IN (68,70) AND d.id=$idpembuatakta
		LEFT JOIN (SELECT id,value  FROM sys_config) AS e ON e.id=62
		LEFT JOIN (SELECT '1' AS idPut,(SELECT value FROM sys_config WHERE id=62) AS tahapan,nomor_perkara_pn AS nomorPerkara,putusan_pn AS putusan, permohonan_pk AS permohonan_pk FROM perkara_pk AS a WHERE a.perkara_id=$idperkara
			UNION ALL
				SELECT '2' AS idPut,(SELECT value FROM sys_config WHERE id=76) AS tahapan,nomor_perkara_banding AS nomorPerkara,putusan_banding AS putusan, permohonan_pk AS permohonan_pk FROM perkara_pk AS a WHERE a.perkara_id=$idperkara
			UNION ALL
				SELECT '3' AS idPut,'Kasasi Mahkamah Agung' AS tahapan,nomor_perkara_kasasi AS nomorPerkara,putusan_kasasi AS putusan, permohonan_pk AS permohonan_pk FROM perkara_pk AS a WHERE a.perkara_id=$idperkara) AS put ON put.idput=$idput
		LEFT JOIN (SELECT pp1.perkara_id AS pp1_perkara_id, 
			pp1.pihak_id AS pp1_pihak_id,
			pp1.nama AS nama_jaksa, 
			pj.pihak_id, if(pj.nip = '','Data Tidak Ada',pj.nip) AS nip_jaksa, 
			pj.kejaksaan AS kejaksaan, 
			pj.alamat_kantor AS alamat_jaksa
		   FROM perkara_pihak1 AS pp1
		   LEFT JOIN pihak_jaksa AS pj ON pp1.pihak_id=pj.pihak_id
	   		WHERE pp1.perkara_id=$idperkara) AS pihak_jaksa ON pihak_jaksa.pp1_perkara_id=$idperkara
		WHERE a.perkara_id=$idperkara AND a.pihak_asal=1 AND a.pihak_id=$jpu_id");
		} 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;
}

}