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

class m_template_pidana_akta_penerimaan_kontra_memori_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 infoDataTerdakwa($idperkara){
		try {
			return $this->db->query("SELECT pihak_id,perkara_id, 
				status_pihak_id, 
				permohonan_kasasi AS tglPemohonKasasi, 
				pihak_asal, 
				pihak_nama, 
				pihak_diwakili, 
				pemohon_id AS pengacara
			FROM perkara_kasasi_detil 
			WHERE pihak_asal=2 AND perkara_id=$idperkara ");
		} catch (Exception $e) {
		}
	}

	function getDataPutusan($idperkara){
		try {
			$qry="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) AS tahapan 
                WHERE tahapan.perkara_id=$idperkara";
              $query=$this->db->query($qry);
              return $query;
		} catch (Exception $e) {
		}
	}

	function getDataJaksaKasasi($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_kasasi_detil 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.perkara_id=$idperkara AND 
							p.pihak_asal='1' AND 
							p.pihak_id=q.pihak_id";
			$query=$this->db->query($qry);
			return $query;
		} catch (Exception $e) {
		}
	}


	function infoDataPHKasasi($idperkara){
		try {
			return $this->db->query("SELECT pihak_id,perkara_id, 
			status_pihak_id, 
			permohonan_kasasi AS tglPemohonKasasi, 
			pihak_asal, 
			pihak_nama, 
			pihak_diwakili, 
			pemohon_id AS pengacara_id,
			pemohon_nama AS namaPengacara
		FROM 	perkara_kasasi_detil 
		WHERE 	pemohon_kasasi IS NOT NULL AND pihak_asal='2' AND pihak_diwakili='Y' AND
				perkara_id=$idperkara ");
		} catch (Exception $e) {
		}
	}
	
	function cetak_penerimaan_kontra_memori_kasasi($idperkara,$idpembuatakta,$idterdakwa,$idput){
		try {			
			$qry="SELECT 	phk2.pihak_id,f.nomor_perkara,a.pemohon_nama, a.pemohon_tanggal_surat,a.pemohon_nomor_surat,a.penyerahan_memori_kasasi, a.penerimaan_kontra_kasasi, b.nama AS nama_terdakwa,a.penerimaan_memori_kasasi AS tglTerimaKasasi,
						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,
						d.pw, IF (d.jabatan ='PanSekNama','Panitera','Wakil Panitera') AS jabatan,
						e.value AS nama_pengadilan, put.idPut, put.tahapan, put.nomorPerkara ,put.putusan,
						(SELECT penerimaan_memori_kasasi FROM perkara_kasasi_detil WHERE perkara_id=$idperkara AND penerimaan_memori_kasasi IS NOT NULL) as tglTerimaKas
				FROM perkara AS f
				LEFT JOIN perkara_kasasi_detil AS a ON f.perkara_id = a.perkara_id
				LEFT JOIN perkara_pihak2 AS phk2 ON a.perkara_id=phk2.perkara_id AND a.pihak_id=phk2.pihak_id
				LEFT JOIN pihak AS b ON phk2.pihak_id=b.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=68
				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,
							(SELECT nomor_perkara FROM perkara WHERE perkara_id=$idperkara) AS nomorPerkara,
							tanggal_putusan AS putusan, 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),
								nomor_perkara_banding,putusan_banding,perkara_id FROM 
								perkara_banding AS a WHERE a.perkara_id=$idperkara) AS put ON put.idput=$idput
				WHERE a.perkara_id=$idperkara AND phk2.pihak_id IS NOT NULL"; 
				
				if ($idterdakwa!='')
				{
					$qry=$qry." AND phk2.pihak_id =$idterdakwa";

				}
			
				$query=$this->db->query($qry);
				return $query;
		} catch (Exception $e) {
		}
	}

	function cetak_penerimaan_kontra_memori_jpu($idperkara,$idpembuatakta,$idpihak,$idput){
	try {
				$qry="	SELECT	phk1.pihak_id,
								f.nomor_perkara,
								a.pemohon_nama,
								a.pemberitahuan_kasasi, 
								a.penerimaan_kontra_kasasi, 
								b.nama AS nama_terdakwa,
								a.penerimaan_memori_kasasi AS tglTerimaKas, 
								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,
								d.pw, 
								IF (d.jabatan ='PanSekNama','Panitera','Wakil Panitera') AS jabatan, 
								e.value AS nama_pengadilan, 
								put.idPut, 
								put.tahapan, 
								put.nomorPerkara,
								put.putusan,phk2.nama AS namaTdkw 
						FROM 	perkara AS f LEFT JOIN perkara_kasasi_detil AS a ON f.perkara_id = a.perkara_id 
								AND a.penerimaan_kontra_kasasi IS NOT NULL 
								LEFT JOIN perkara_pihak1 AS phk1 ON a.perkara_id=phk1.perkara_id AND a.pihak_id=phk1.pihak_id 
								LEFT JOIN perkara_pihak2 AS phk2 ON a.perkara_id=phk2.perkara_id 
								LEFT JOIN pihak AS b ON phk1.pihak_id=b.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, 
											(SELECT nomor_perkara FROM perkara WHERE perkara_id=$idperkara) AS nomorPerkara, 
											tanggal_putusan AS putusan, 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 ),
											nomor_perkara_banding,
											putusan_banding,
											perkara_id FROM perkara_banding AS a WHERE a.perkara_id=$idperkara) AS put ON put.idput=$idput 
						WHERE 	a.perkara_id=$idperkara AND 
								a.penerimaan_kontra_kasasi IS NOT NULL AND phk1.pihak_id =$idpihak";			

			$query=$this->db->query($qry);
			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;
	}
}
