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

class m_template_tun_akta_permintaan_kasasi 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,$idpemohon){
		try {
			return $this->db->query("SELECT pihak_nama,pemohon_nama,
				status_pihak_text,
				pihak_diwakili AS diwakili,
				status_pihak_id
				FROM perkara_kasasi_detil WHERE perkara_id=$idperkara AND permohonan_kasasi IS NOT NULL AND status_pihak_id IN (1,2,5) AND pemohon_id=$idpemohon ");
		} 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
				FROM perkara_kasasi_detil WHERE perkara_id=$idperkara AND pihak_id=$idpihak ");
		} 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', nomor_perkara_kasasi,putusan_kasasi,perkara_id
		 			FROM perkara_kasasi WHERE perkara_id=$idperkara) AS tahapan 
                	WHERE tahapan.perkara_id=$idperkara limit 2");
		} catch (Exception $e) {
		}
	}


	function getDataPenggugat($idperkara){
		try {
			$sql="	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($sql);
			return $query;
		} catch (Exception $e) {
		}
	}

	function infoDataTergugat($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_permintaan_kasasi($idperkara,$idpembuatakta,$idput,$idpemohon){
		try {
			$sql="SELECT 	a.pihak_id,
							a.pihak_nama AS pihakAsli,
							a.pihak_asal,
							a.pihak_asal_text,
							a.pihak_diwakili,
							z.nomor_perkara, 
							a.permohonan_kasasi,
							a.tanggal_cabut, 
							a.pemohon_nama AS namaPihak, 
							a.pemohon_alamat AS alamatPihak, 
							a.pemohon_tanggal_surat AS tanggalSuratKuasa,
							IF(c.jenis_kelamin='L','Laki-Laki','Perempuan' )AS kelaminPihak,
							put.*,
							info.* 
					FROM 	perkara_kasasi_detil AS a 
							LEFT JOIN perkara AS z ON a.perkara_id=z.perkara_id
							LEFT JOIN pihak AS c ON a.pemohon_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, z.namaPT,
										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 namaPT FROM sys_config WHERE id=76) AS z,
											(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) 
					WHERE a.perkara_id=$idperkara AND info.id=$idpembuatakta AND a.permohonan_kasasi  AND put.idput=$idput AND a.pemohon_id = $idpemohon";
			//echo $sql;
			$query=$this->db->query($sql);
			return $query;
		} catch (Exception $e) {
		}
	}
function cetak_permintaan_kasasi2($idperkara,$idpembuatakta,$idput,$idpemohon){
		try {
			$sql="SELECT 	a.pihak_id,
							a.pihak_nama AS pihakAsli,
							a.pihak_asal,
							a.pihak_asal_text,
							a.pihak_diwakili,
							z.nomor_perkara, 
							a.permohonan_kasasi,
							a.tanggal_cabut, 
							a.pemohon_nama AS namaPihak, 
							a.pemohon_alamat AS alamatPihak, 
							a.pemohon_tanggal_surat AS tanggalSuratKuasa,
							IF(c.jenis_kelamin='L','Laki-Laki','Perempuan' )AS kelaminPihak,
							put.*,
							info.* 
					FROM 	perkara_kasasi_detil AS a 
							LEFT JOIN perkara AS z ON a.perkara_id=z.perkara_id
							LEFT JOIN pihak AS c ON a.pemohon_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, 
										z.namaPT,
										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 namaPT FROM sys_config WHERE id=76) AS z,
											(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) 
					WHERE a.perkara_id=$idperkara 
					AND info.id=$idpembuatakta 
					AND  put.idput=$idput 
					AND a.pihak_id = $idpemohon";
			//echo $sql;
			$query=$this->db->query($sql);
			return $query;
		} catch (Exception $e) {
		}
	}

//------------untuk looping penggugat di template---------//
	function getPihakPenggugat($idperkara){
		try{
			if(!is_numeric($idperkara)){
				return '';
			}
			return $this->db->query ("SELECT pihak2.pihak_id, pihak2.urutan, urutan_kasasi,status_pihak_text,status_pihak_id,pihak2.nama, pihak2.alamat, pihak2.jenis_pihak_id AS info, p.pekerjaan, IF(p.warga_negara IS NULL,'Indonesia','') AS warga_negara
									FROM perkara_pihak1 AS pihak2
									LEFT JOIN pihak AS p ON pihak2.pihak_id=p.id
									LEFT JOIN perkara_kasasi_detil AS k
									ON k.perkara_id = pihak2.perkara_id AND k.pihak_asal = 1 AND k.pihak_id = pihak2.pihak_id
									WHERE pihak2.perkara_id =".$idperkara." ORDER BY urutan ASC");
			}catch(PDOException $e) {
		}
	}

	function getPihakTergugat($idperkara){
		try{
			if(!is_numeric($idperkara)){
				return '';
			}
			return $this->db->query ("SELECT pihak2.pihak_id, pihak2.urutan, urutan_kasasi,status_pihak_text,status_pihak_id,pihak2.nama, pihak2.alamat, pihak2.jenis_pihak_id AS info, p.pekerjaan, IF(p.warga_negara IS NULL,'Indonesia','') AS warga_negara
									FROM perkara_pihak2 AS pihak2
									LEFT JOIN pihak AS p ON pihak2.pihak_id=p.id
									LEFT JOIN perkara_kasasi_detil AS k
									ON k.perkara_id = pihak2.perkara_id AND k.pihak_asal = 2 AND k.pihak_id = pihak2.pihak_id
									WHERE pihak2.perkara_id =".$idperkara." ORDER BY urutan ASC");
			}catch(PDOException $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 getDataKasasi($idperkara){
		try{
			$sql="SELECT
				kasasi.*,
				putusan.amar_putusan
				FROM perkara_kasasi AS kasasi
				LEFT JOIN perkara_putusan AS putusan ON putusan.perkara_id=kasasi.perkara_id 
				WHERE kasasi.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 getTglKasasi($idperkara,$idpihak){
		try {
			return $this->db->query('SELECT * FROM perkara_kasasi_detil WHERE perkara_id = '.$idperkara.' AND pihak_id='.$idpihak);
		} catch (Exception $e) {
			
		}
	}

	function getListpihak($idperkara,$idpihak){
		try {
			return $this->db->query('SELECT pihak_id, pihak_nama
									FROM perkara_kasasi_detil 
									WHERE perkara_id='.$idperkara.' AND 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_kasasi_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) {
			
		}
	}
}

?>