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

class m_template_mil_akta_pemberitahuan_putusan_kasasi extends CI_Model {
	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 getDataPejabat(){
		try {
			return $this->db->query("SELECT b.id,a.namaPengadilan,b.id,IF (b.nama = 'PanSekNama','Katera','Wakil Katera' ) 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 pkd.pihak_id,pkd.perkara_id, 
										pkd.status_pihak_id, 
										pkd.permohonan_kasasi AS tglPemohonKasasi, 
										pkd.pihak_asal, 
										pkd.pihak_nama, 
										pkd.pihak_diwakili, 
										pkd.pemohon_id AS pengacara,
										pp2.nrp as nrp_terdakwa, 
										pp2.jabatan as jabatan_terdakwa, 
										pp2.kesatuan as kesatuan_terdakwa, 
										pp2.pangkat as pangkat_terdakwa
									FROM perkara_kasasi_detil pkd
										LEFT JOIN perkara_pihak2 pp2 ON pp2.perkara_id= pkd.perkara_id AND pkd.pihak_id=pp2.pihak_id
									WHERE 	pkd.permohonan_kasasi IS NOT NULL 
										AND pkd.pihak_asal=2 AND pkd.pihak_diwakili='T' AND pkd.perkara_id=$idperkara ");
		} catch (Exception $e) {
		}
	}
	// function getInfo($idperkara){
		// try {
			// $sql= "SELECT pemohon_kasasi, pihak_asal_text, IF(pihak_diwakili='Y', CONCAT(pemohon_nama), 'Tidak Diwakili') AS diwakili, pemohon_nama
					// FROM perkara_kasasi_detil 
					// WHERE perkara_id=$idperkara AND permohonan_kasasi IS NOT NULL AND status_pihak_id=1";
			// $query=$this->db->query($sql);
			// return $query;
		// } catch (Exception $e) {
		// }
	// }
	function cetak_permohonan_kasasi($idperkara,$idpembuatakta,$idput, $idpemohon){
		try {		
			$qry="SELECT a.pemohon_id,
				z.nomor_perkara,
				a.permohonan_kasasi,
				c.nama AS namaPihak,
				b.nrp,
				b.pangkat,
				b.jabatan,
				b.kesatuan,
				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,
				ag.nama  AS agamaPihak,
				c.pekerjaan  AS pekerjaanPihak,
				c.pendidikan  AS pendidikanPihak,
				c.warga_negara  AS negaraPihak,
				pkas.nomor_putusan_kasasi,
				pkas.putusan_kasasi,
				put.*, info.*, a.pihak_diwakili
		FROM perkara_kasasi_detil AS a
		LEFT JOIN perkara_kasasi AS pkas ON a.perkara_id=pkas.perkara_id
		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 and b.perkara_id=a.perkara_id 
		LEFT JOIN pihak AS c ON b.pihak_id=c.id
		LEFT JOIN agama AS ag ON c.agama_id=ag.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_kasasi AS a 
				WHERE a.perkara_id=$idperkara) AS put ON put.idPut IN ('1','2')
		LEFT JOIN (SELECT 	b.id, a.namaPengadilan, aa.kodePengadilan,
			IF (b.nama = 'PanSekNama','Katera','Wakil Katera' ) AS pejabat,b.pw,
			if (b.nama='PanSekNama', (select value as nrp from sys_config where id = 69), (select value as nrp from sys_config where id = 71)) as nrp_pejabat,
			if (b.nama='PanSekNama', (select value as nrp from sys_config where id = 86), (select value as nrp from sys_config where id = 87)) as pangkat_pejabat
			FROM    (SELECT id,value AS namaPengadilan FROM sys_config WHERE id=62) AS a, (SELECT id,VALUE AS kodePengadilan FROM sys_config WHERE id=61) AS aa,

		        (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_asal !=1 AND c.id=$idpemohon";
		//echo $qry;
		$query=$this->db->query($qry);
		return $query;
		} catch (Exception $e) {
		}
	}

	function cetak_permohonan_kasasi_oditur($idperkara,$idpembuatakta,$idput, $idpemohon){
		try {
			$qry="SELECT a.pemohon_id,
				z.nomor_perkara,a.permohonan_kasasi,
				b.pihak_id,d.*,
				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,				
				pkas.nomor_putusan_kasasi,
				pkas.putusan_kasasi,
				put.*, info.*, a.pihak_diwakili,
				pp2.nama AS namaTerdakwa,
				pp2.nrp AS nrpTerdakwa,
				pp2.pangkat AS pangkatTerdakwa,
				pp2.kesatuan AS kesatuanTerdakwa
			FROM perkara_kasasi_detil AS a
			LEFT JOIN perkara_pihak2 AS pp2 ON pp2.perkara_id = a.perkara_id
			LEFT JOIN perkara_kasasi AS pkas ON a.perkara_id=pkas.perkara_id
			LEFT JOIN perkara AS z ON a.perkara_id=z.perkara_id
			LEFT JOIN perkara_pihak1  AS b ON a.pihak_id=b.pihak_id AND a.perkara_id=b.perkara_id
			LEFT JOIN pihak AS c ON b.pihak_id=c.id
			LEFT JOIN pihak_jaksa AS d ON d.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, aa.kodePengadilan,
				IF (b.nama = 'PanSekNama','Panitera Sekretaris','Wakil Panitera' ) AS pejabat,b.pw,
				if (b.nama='PanSekNama', (select value as nrp from sys_config where id = 69), (select value as nrp from sys_config where id = 71)) as nrp_pejabat,
				if (b.nama='PanSekNama', (select value as nrp from sys_config where id = 86), (select value as nrp from sys_config where id = 87)) as pangkat_pejabat
			FROM    (SELECT id,value AS namaPengadilan FROM sys_config WHERE id=62) AS a,
				(SELECT id,VALUE AS kodePengadilan FROM sys_config WHERE id=61) AS aa,
		        (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 ='T' AND a.pihak_asal =1 AND c.id=$idpemohon";
			//echo $qry;
			return $this->db->query($qry);
		} catch (Exception $e) {
		}
	}
	function getDataOditur($idperkara){
		try {			
			$qry="	SELECT 	p.pemohon_id, 
						b.nama AS nama_oditur, 
						b.pekerjaan,
						r.NIP AS nrp,
						r.kejaksaan AS oditurat,
						r.alamat_kantor,
						r.pangkat AS pangkat_oditur,
						r.kesatuan AS kesatuan_oditur
					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 	p.permohonan_kasasi IS NOT NULL AND 
						p.pihak_asal='1' AND 
						p.pihak_id=q.pihak_id AND 
						p.perkara_id=$idperkara AND
						q.pihak_id=b.id";
							//echo $qry;
				$query=$this->db->query($qry);
				return $query;
		} catch (Exception $e) {
		}
	}	
}
/* End of file m_template_mil_akta_pemberitahuan_putusan_kasasi.php */
/* Location: ./application/models/generate_template/m_template_mil_akta_pemberitahuan_putusan_kasasi.php */