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

class m_template_tun_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,pihak_diwakili,pemohon_nama,
				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 getInfoPihak($idperkara,$idpihak){
		try {
			return $this->db->query("SELECT pihak_nama,pemohon_nama,
				status_pihak_text,
				pihak_diwakili AS diwakili,
				status_pihak_id,
				pemohon_id, pihak_id, pemberitahuan_pk,penyerahan_memori_pk,penyerahan_kontra_pk, pemberitahuan_putusan_pk
				FROM perkara_pk_detil WHERE perkara_id=$idperkara AND pihak_id=$idpihak");
		} catch (Exception $e) {
		}
	}

	function getPihakPemohonPK($idperkara){
		try {
			return $this->db->query("SELECT * FROM perkara_pk_detil WHERE perkara_id = $idperkara AND pemohon_id IS NOT NULL");
		} catch (Exception $e) {
		}
	}
	function getPihakKontraPK($idperkara){
		try {
			return $this->db->query("SELECT * FROM perkara_pk_detil WHERE perkara_id = $idperkara AND penerimaan_kontra_pk IS NOT NULL");
		} 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 getAllPemohon($idperkara){
		try{
			if(!is_numeric($idperkara)){
						return '';
					}
			return $this->db->query ("SELECT pihak.nama,pihak.alamat,pihak.pekerjaan,pihak.jenis_pihak_id,ppd.pihak_asal_text,ppd.pihak_asal,ppd.status_pihak_text FROM perkara_pk_detil AS ppd 
							    LEFT JOIN (SELECT * FROM pihak  WHERE id IN (SELECT pihak_id FROM perkara_pk_detil WHERE perkara_id = $idperkara)) AS pihak 
							      ON pihak.id = ppd.pihak_id WHERE ppd.perkara_id = $idperkara AND ppd.pemohon_id IS NOT NULL");
			}catch(PDOException $e) {
		}
	}

	function getAllTermohon($idperkara){
		try{
			if(!is_numeric($idperkara)){
						return '';
					}
			return $this->db->query ("SELECT pihak.nama,pihak.alamat,pihak.pekerjaan,pihak.jenis_pihak_id,ppd.pihak_asal_text,ppd.pihak_asal,ppd.status_pihak_text FROM perkara_pk_detil AS ppd 
							    LEFT JOIN (SELECT * FROM pihak  WHERE id IN (SELECT pihak_id FROM perkara_pk_detil WHERE perkara_id = $idperkara)) AS pihak 
							      ON pihak.id = ppd.pihak_id WHERE ppd.perkara_id = $idperkara AND ppd.pemohon_id IS NULL");
			}catch(PDOException $e) {
		}
	}

	function getPihakIntervensi($idperkara){
		try{
			if(!is_numeric($idperkara)){
						return '';
					}
			return $this->db->query ("SELECT pihak3.pihak_id, pihak3.urutan, pihak3.nama, pihak3.alamat, pihak3.jenis_pihak_id AS info, p.pekerjaan, IF(p.warga_negara IS NULL,'Indonesia','') AS warga_negara
									FROM perkara_pihak3 AS pihak3
									LEFT JOIN pihak AS p ON pihak4.pihak_id=p.id
									WHERE perkara_id =".$idperkara." ORDER BY urutan ASC");
			}catch(PDOException $e) {
		}
	}
//-----------------------------------------------------//

	function getDataJaksaPK($idperkara){
		try {
			return $this->db->query("SELECT  p.*,q.*, r.*
				FROM 
					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 p.pihak_asal='1' AND p.pihak_id=q.pihak_id AND p.perkara_id=$idperkara");
		} catch (Exception $e) {
		}
	}

	function getDataJaksa($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,
							IF (r.alamat_kantor IS NULL ,'#isi dulu di aplikasi#',r.alamat_kantor ) AS alamat_kantor
					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 infoDataTerdakwa($idperkara){
		try {
			return $this->db->query("SELECT 	pihak_id,perkara_id, 
				status_pihak_id, 
				permohonan_pk AS tglPemohonPK, 
				permohonan_pk,
				pihak_asal, 
				CONCAT(pihak_nama,' / ',pihak_asal_text) AS  pihak_nama,
				pihak_diwakili, 
				pemohon_id 
			FROM 	perkara_pk_detil 
			WHERE 	pihak_diwakili='T' AND perkara_id=$idperkara AND pihak_asal=2");
		} 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_peninjauan_kembali($idperkara,$idpembuatakta,$idput,$idpemohon){
		try {			
			$sql="	SELECT	DISTINCT a.pihak_id,
							a.pihak_nama, 
							z.nomor_perkara, 
							a.permohonan_pk, 
							c.nama AS namaPihak, 
							c.alamat AS alamatPihak, 
							c.tempat_lahir AS tempatLahirPihak, 
							c.tanggal_lahir AS tanggalLahirPihak, 
							IF(c.jenis_kelamin='L','Laki-Laki','Perempuan' )AS kelaminPihak, 
							c.agama_nama AS agamaPihak, 
							c.pekerjaan AS pekerjaanPihak, 
							c.pendidikan AS pendidikanPihak, 
							c.warga_negara AS negaraPihak, c.jenis_pihak_id, 
							a.pemberitahuan_putusan_pk, a.penerimaan_memori_pk,
							a.penyerahan_memori_pk, a.penyerahan_kontra_pk,a.pihak_asal,a.pihak_diwakili,
							put.*, a.pemohon_tanggal_surat AS tanggalSuratKuasa,
							info.*, ppk.catatan_putusan_pk, a.pihak_asal_text, ppk.tanggal_cabut,
							putPK.*, ppk.putusan_pk AS tglPutusPK, ppk.pemberitahuan_putusan_pk_pihak1, 
							ppk.pemberitahuan_putusan_pk_pihak2, ppk.nomor_putusan_pk
					FROM 	perkara_pk_detil AS a 	
										LEFT JOIN perkara AS z ON a.perkara_id=z.perkara_id 
										LEFT JOIN perkara_pk AS ppk ON a.perkara_id=ppk.perkara_id
										LEFT JOIN perkara_pihak2 AS b ON a.pihak_id=b.pihak_id 
										LEFT JOIN pihak AS c ON b.pihak_id=c.id 
										LEFT JOIN (SELECT 	'1' AS idPut,
													(SELECT value FROM sys_config WHERE id = 62) AS Tahapan, 
													(SELECT nomor_perkara FROM perkara WHERE perkara_id=$idperkara ) AS nomorPerkaraTahapan, 
													tanggal_putusan AS putusan, amar_putusan AS amarPutusTahapan, 
													perkara_id FROM perkara_putusan 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 nomorPerkaraTahapan, 
													putusan_banding AS putusan, amar_putusan_banding,
													perkara_id FROM perkara_banding AS a WHERE a.perkara_id=$idperkara UNION ALL SELECT '3' AS idput, 
													'Mahkamah Agung RI', 
													nomor_perkara_kasasi, putusan_kasasi, amar_putusan_kasasi,
													perkara_id FROM perkara_kasasi WHERE perkara_id=$idperkara) AS put ON put.idPut IN ('1','2','3') 
										LEFT JOIN (SELECT 	b.id,
													a.namaPengadilan, 
													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) AS info ON info.id IN (68,70) 
										LEFT JOIN (SELECT 	perkara_id,
													nomor_perkara_kasasi,
													putusan_kasasi,
													amar_putusan_pk 
											FROM perkara_pk WHERE perkara_id=$idperkara) AS putPK ON putPK.perkara_id=$idperkara 
					WHERE 	a.perkara_id=$idperkara AND 
							info.id=$idpembuatakta AND 
							a.pihak_id=$idpemohon AND put.idput=$idput ";
										$query=$this->db->query($sql);
										return $query;
					} catch (Exception $e) {
					}
	}

	function cetak_akta_peninjauan_kembali($idperkara,$idpembuatakta,$idput,$idpemohon){
		try {			
			$sql="	SELECT	DISTINCT a.pihak_id,
							a.pihak_nama, 
							z.nomor_perkara, 
							a.permohonan_pk, 
							c.nama AS namaPihak, 
							c.alamat AS alamatPihak, 
							c.tempat_lahir AS tempatLahirPihak, 
							c.tanggal_lahir AS tanggalLahirPihak, 
							IF(c.jenis_kelamin='L','Laki-Laki','Perempuan' )AS kelaminPihak, 
							c.agama_nama AS agamaPihak, 
							c.pekerjaan AS pekerjaanPihak, 
							c.pendidikan AS pendidikanPihak,a.pemohon_nama,a.pemohon_pekerjaan,a.pemohon_alamat, 
							c.warga_negara AS negaraPihak, c.jenis_pihak_id,
							a.pemberitahuan_putusan_pk, a.penerimaan_memori_pk,
							a.penyerahan_memori_pk, a.penyerahan_kontra_pk,a.pihak_asal,a.pihak_diwakili,
							put.*, a.pemohon_tanggal_surat AS tanggalSuratKuasa,
							info.*, ppk.catatan_putusan_pk, a.pihak_asal_text,
							putPK.*, ppk.putusan_pk AS tglPutusPK, ppk.pemberitahuan_putusan_pk_pihak1, 
							ppk.pemberitahuan_putusan_pk_pihak2, ppk.nomor_putusan_pk, ppk.tanggal_cabut
					FROM 	perkara_pk_detil AS a 	
										LEFT JOIN perkara AS z ON a.perkara_id=z.perkara_id 
										LEFT JOIN perkara_pk AS ppk ON a.perkara_id=ppk.perkara_id
										LEFT JOIN perkara_pihak2 AS b ON a.pihak_id=b.pihak_id 
										LEFT JOIN pihak AS c ON b.pihak_id=c.id 
										LEFT JOIN (SELECT 	'1' AS idPut,
													(SELECT value FROM sys_config WHERE id = 62) AS Tahapan, 
													(SELECT nomor_perkara FROM perkara WHERE perkara_id=$idperkara ) AS nomorPerkaraTahapan, 
													tanggal_putusan AS putusan, amar_putusan AS amarPutusTahapan, 
													perkara_id FROM perkara_putusan 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 nomorPerkaraTahapan, 
													putusan_banding AS putusan, amar_putusan_banding,
													perkara_id FROM perkara_banding AS a WHERE a.perkara_id=$idperkara UNION ALL SELECT '3' AS idput, 
													'Mahkamah Agung RI', 
													nomor_perkara_kasasi, putusan_kasasi, amar_putusan_kasasi,
													perkara_id FROM perkara_kasasi WHERE perkara_id=$idperkara) AS put ON put.idPut IN ('1','2','3') 
										LEFT JOIN (SELECT 	b.id,
													a.namaPengadilan, 
													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) AS info ON info.id IN (68,70) 
										LEFT JOIN (SELECT 	perkara_id,
													nomor_perkara_kasasi,
													putusan_kasasi,
													amar_putusan_pk 
											FROM perkara_pk WHERE perkara_id=$idperkara) AS putPK ON putPK.perkara_id=$idperkara 
					WHERE 	a.perkara_id=$idperkara AND 
							info.id=$idpembuatakta AND 
							a.pemohon_id=$idpemohon AND put.idput=$idput ";
										$query=$this->db->query($sql);
										return $query;
					} 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;
}

function getDataPK($idperkara){
		try{
			$sql="SELECT
				pk.*,
				isiputusan.amar_putusan
				FROM perkara_pk AS pk
				LEFT JOIN perkara_putusan AS isiputusan ON isiputusan.perkara_id=pk.perkara_id 
				WHERE pk.perkara_id=$idperkara";
			$query=$this->db->query($sql);
			return $query;	
		}catch(Exception $e) { 
			log_message('error', $e); 
		}
	}

	function getListPemohon($idperkara,$idpemohon){
		try {
			return $this->db->query('SELECT pemohon_id,pemohon_nama FROM perkara_kasasi_detil WHERE permohonan_kasasi IS NOT NULL AND pihak_id <> '.$idpemohon.' AND perkara_id = '.$idperkara.';');
		} catch (Exception $e) {
			
		}
	}

	function infoPemohonPK($idperkara,$idpemohon){
		try {
			return $this->db->query("SELECT ppd.pihak_asal,pihak.nama,IF(pihak.warga_negara IS NULL,'Indonesia','') AS kewarganegaraan,pihak.alamat,pihak.pekerjaan,ppd.status_pihak_text,ppd.pihak_asal_text,pihak.jenis_pihak_id,ppd.penerimaan_memori_pk,ppd.penyerahan_memori_pk
								FROM perkara_pk_detil AS ppd
								LEFT JOIN pihak AS pihak ON ppd.pemohon_id=pihak.id
								WHERE ppd.perkara_id=$idperkara AND ppd.pemohon_id=$idpemohon");
			} catch (Exception $e) {
			
		}
	}
	function infoTermohonPK($idperkara,$idpihak){
		try {
			return $this->db->query("SELECT ppd.pihak_asal,pihak.nama,IF(pihak.warga_negara IS NULL,'Indonesia','') AS kewarganegaraan,pihak.alamat,pihak.pekerjaan,ppd.status_pihak_text,ppd.pihak_asal_text,pihak.jenis_pihak_id,ppd.pemberitahuan_pk,ppd.penyerahan_memori_pk,ppd.penyerahan_kontra_pk
								FROM perkara_pk_detil AS ppd
								LEFT JOIN pihak AS pihak ON ppd.pihak_id=pihak.id
								WHERE ppd.perkara_id=$idperkara AND ppd.pihak_id=$idpihak");
			} catch (Exception $e) {
			
		}
	}
	function infoAllPihak($idperkara,$idpihak){
		try {
			return $this->db->query("SELECT ppd.*,pihak.nama,IF(pihak.warga_negara IS NULL,'Indonesia','') AS kewarganegaraan,pihak.alamat,pihak.pekerjaan,pihak.jenis_pihak_id FROM perkara_pk_detil AS ppd 
							  LEFT JOIN pihak AS pihak ON ppd.pihak_id = pihak.id WHERE ppd.perkara_id = $idperkara AND ppd.pihak_id =$idpihak");
			} catch (Exception $e) {			
		}
	}

	function getDataPemberitahuan($idperkara,$idpihak){
		try {
			return $this->db->query('SELECT IF(pihak_diwakili="Y",pemohon_nama,pihak_nama) AS namapihak,
						IF(pihak_diwakili="Y",pemohon_alamat,alamat) AS alamatpihak,
						IF(pihak_diwakili="Y",pemohon_pekerjaan,pekerjaan) AS pekerjaanpihak,
						p.*,
						k.* FROM perkara_pk_detil  as k
						LEFT JOIN pihak as p
						ON p.id = k.pihak_id OR p.id = k.pemohon_id
						WHERE k.perkara_id='.$idperkara.' AND p.id ='.$idpihak);
			} catch (Exception $e) {			
		}
	}

	/*function getDataPemberitahuan($idperkara,$idpihak){
		try {
			return $this->db->query('SELECT IF(pihak_diwakili="Y",pemohon_nama,pihak_nama) AS namapihak,
				IF(pihak_diwakili="Y",pemohon_alamat,alamat) AS alamatpihak,
				IF(pihak_diwakili="Y",pemohon_pekerjaan,pekerjaan) AS pekerjaanpihak,
				p.*,
				k.* FROM perkara_pk_detil  as k
				LEFT JOIN pihak as p
				ON p.id = k.pihak_id OR p.id = k.pemohon_id
				WHERE p.id = $idpihak AND perkara_id =$idperkara');
			} catch (Exception $e) {			
		}
	}*/
}
