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

class m_template_pidana_akta_penerimaan_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 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) AS tahapan 
                WHERE tahapan.perkara_id=$idperkara");
		} catch (Exception $e) {
		}
	}

	function getDataJaksaKasasi($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_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 	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($sql);
			return $query;
		} catch (Exception $e) {
		}
	}

	function getDataPHkasasi($idperkara){
		try {
			return $this->db->query("SELECT pihak.perkara_id, pihak.pihak_id, pihak.nama,a.pemohon_nama,
                a.pihak_diwakili, a.perkara_id, a.pihak_id, 
                if(a.penerimaan_memori_kasasi is null,'1',a.penerimaan_memori_kasasi) as penerimaan_memori_kasasi 
                FROM perkara_kasasi_detil AS a 
                LEFT JOIN (SELECT perkara_id,pihak_id, nama FROM perkara_pihak2 WHERE perkara_id=$idperkara) AS pihak ON pihak.perkara_id = $idperkara
                WHERE a.perkara_id=$idperkara AND a.pihak_id=pihak.pihak_id and a.pihak_diwakili='Y' ");
		} catch (Exception $e) {
		}
	}
	
	function cetak_penerimaan_memori_kasasi($idperkara,$idpihak,$idpembuatakta,$idput){
		try {			
			$sql="	SELECT 	b.pihak_id,
							a.pemohon_tanggal_surat,
							a.pemohon_nomor_surat,
							f.nomor_perkara,
							a.pemohon_nama, 
							b.nama AS nama_terdakwa,
							a.penerimaan_memori_kasasi AS tglTerimaKas, 
							IF (b.alamat IS NULL ,'#data belum lengkap#',b.`alamat` ) AS alamat, 
							IF (p.tempat_lahir IS NULL, '#data belum lengkap#',p.`tempat_lahir`) AS tempat_lahir, 
							IF (p.tanggal_lahir IS NULL,'#data belum lengkap#', p.`tanggal_lahir`) AS tanggal_lahir, 
							IF (p.jenis_kelamin = 'L', 'Laki-Laki','Perempuan') AS jenis_kelamin, 
							IF (p.warga_negara IS NULL, '#data belum lengkap#',p.`warga_negara`) AS warga_negara, 
							IF (p.agama_nama IS NULL,'#data belum lengkap#',p.`agama_nama`) AS agama_nama, 
							IF (p.pekerjaan IS NULL,'#data belum lengkap#',p.`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 
					FROM 	pihak p, perkara AS f 
							LEFT JOIN perkara_kasasi_detil AS a ON f.perkara_id = a.perkara_id 
							LEFT JOIN perkara_pihak2 AS b ON a.perkara_id=b.perkara_id AND b.pihak_id=a.pihak_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 
							b.pihak_id=$idpihak AND
							b.pihak_id=p.id";
				$query=$this->db->query($sql);
				return $query;
		} catch (Exception $e) {
		}
	}

	function cetak_permohonan_kasasi_ph($idperkara,$idpembuatakta,$idput){
		try {		
			$sql="SELECT a.pihak_id,
				z.nomor_perkara,
				a.pemohon_tanggal_surat,
				a.pemohon_nomor_surat,
				a.permohonan_kasasi,a.pemohon_nama,
				c.nama AS namaPihak,
				c.alamat AS alamatPihak,
				c.tempat_lahir AS tempatLahirPihak,
				c.tanggal_lahir  AS tanggalLahirPihak,
				(YEAR(z.tanggal_pendaftaran) - YEAR(c.tanggal_lahir)) AS umurPihak,
				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,
				put.*, info.*
		FROM perkara_kasasi_detil AS a
		LEFT JOIN perkara AS z ON a.perkara_id=z.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, 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,perkara_id 
				FROM perkara_banding AS a 
				WHERE a.perkara_id=$idperkara) AS put ON put.idPut IN ('1','2')
		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)
		WHERE a.perkara_id=$idperkara AND info.id=$idpembuatakta AND put.idput=$idput AND a.pihak_diwakili ='Y' AND a.pihak_asal !=1  ";
		$query=$this->db->query($sql);
		return $query;
		} catch (Exception $e) {
		}
	}

	function cetak_penerimaan_memori_kasasi_pu($idperkara,$idpihak,$idpembuatakta,$idput){
		try {			
			$sql="	SELECT 	b.pihak_id,
							f.nomor_perkara,
							a.pemohon_nama, 
							b.nama AS nama_terdakwa,
							a.penerimaan_memori_kasasi AS tglTerimaKas, 
							IF (b.alamat IS NULL ,'#data belum lengkap#',b.`alamat` ) AS alamat, 
							IF (p.tempat_lahir IS NULL, '#data belum lengkap#',p.`tempat_lahir`) AS tempat_lahir, 
							IF (p.tanggal_lahir IS NULL,'#data belum lengkap#', p.`tanggal_lahir`) AS tanggal_lahir, 
							IF (p.jenis_kelamin = 'L', 'Laki-Laki','Perempuan') AS jenis_kelamin, 
							IF (p.warga_negara IS NULL, '#data belum lengkap#',p.`warga_negara`) AS warga_negara, 
							IF (p.agama_nama IS NULL,'#data belum lengkap#',p.`agama_nama`) AS agama_nama, 
							IF (p.pekerjaan IS NULL,'#data belum lengkap#',p.`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 
					FROM 	pihak p, perkara AS f 
							LEFT JOIN perkara_kasasi_detil AS a ON f.perkara_id = a.perkara_id 
							LEFT JOIN perkara_pihak1 AS b ON a.perkara_id=b.perkara_id AND b.pihak_id=a.pihak_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 
							b.pihak_id=$idpihak AND
							b.pihak_id=p.id";
				$query=$this->db->query($sql);
				return $query;
		} 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 	pemohon_kasasi IS NOT NULL AND pihak_asal=2 AND perkara_id=$idperkara ");
		} 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 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;
	}
}

