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

class m_template_mil_permohonan_akta_kasasi_pid extends CI_Model {
	function getDataPejabat(){
		try {
			$qry="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";
						//echo $qry;
			return $this->db->query($qry);
		} 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 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 getDataOditur($idperkara){
		try {			
			$qry="	SELECT 	q.pihak_id, p.pemohon_id, 
							b.nama, 
							b.pekerjaan,
							r.NIP,
							r.kejaksaan,
							r.alamat_kantor,
							r.pangkat AS pangkat_oditur, 
							z.nomor_perkara, p.permohonan_kasasi, p.pihak_diwakili, 
							(SELECT VALUE AS namaPengadilan FROM sys_config WHERE id=62) as namaPengadilan, 
							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 
					LEFT JOIN perkara AS z ON p.perkara_id=z.perkara_id
					WHERE 	 
							p.pihak_asal='1' AND 
							p.pihak_id=q.pihak_id AND 
							p.perkara_id=$idperkara AND
							q.pihak_id=b.id";
				$query=$this->db->query($qry);
				
				return $query;
		} catch (Exception $e) {
		}
	}

	function infoDataTerdakwa($idperkara,$idpemohon){
		try {
			// $qry="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";
			$qry="SELECT pkd.pihak_id,pkd.perkara_id, 
										pkd.status_pihak_id, 
										pkd.permohonan_kasasi, 
										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.pihak_asal=2 AND pkd.perkara_id=$idperkara AND pkd.pemohon_id=$idpemohon";
//echo $qry; 
//exit();
			return $this->db->query($qry);
		} catch (Exception $e) {
		}
	}

//Kuasa Hukum
	function infoDataTerdakwa2($idperkara){
		try {
			// $qry="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";
			$qry="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.pihak_asal=2 AND pkd.perkara_id=$idperkara";
										//echo $qry;
			return $this->db->query($qry);
		} 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_permohonan_kasasi($idperkara,$idpembuatakta,$idput,$idterdakwa){
		try {		
			$qry="SELECT a.pemohon_id,
					z.nomor_perkara,
					z.perkara_id,
					a.permohonan_kasasi,a.pemberitahuan_kasasi,
					a.penerimaan_memori_kasasi,a.penyerahan_memori_kasasi,a.penerimaan_memori_kasasi,
					a.penyerahan_memori_kasasi,a.penerimaan_kontra_kasasi,a.penyerahan_kontra_kasasi,
					a.pemberitahuan_inzage_kasasi,a.pemberitahuan_putusan_kasasi,a.tanggal_cabut, 
					a.pihak_diwakili, 
					b.nrp,
					b.pangkat,
					b.jabatan,
					b.kesatuan,
					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,
					ag.nama AS agamaPihak,
					c.pekerjaan AS pekerjaanPihak,
					c.pendidikan AS pendidikanPihak,
					c.warga_negara AS negaraPihak,
					d.kesatuan AS kode_otmil, 
					pkas.nomor_putusan_kasasi,
					pkas.putusan_kasasi AS tgl_putusan_kasasi,  
					pkas.tidak_memenuhi_syarat,
					zput.tanggal_bht,
					put.*, 
					info.*, 
					(SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(VALUE,' ',3),' ',-1) FROM sys_config WHERE id = 76) AS kota_pt
			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_putusan zput ON zput.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 ag.id=c.agama_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.kadilmil, ab.nrpkadilmil, ac.pangkatkadilmil,
							IF (b.nama = 'PanSekNama','Panitera','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 kadilmil FROM sys_config WHERE id=64) AS aa,
							(SELECT id,VALUE AS nrpkadilmil FROM sys_config WHERE id=65) AS ab,
							(SELECT id,VALUE AS pangkatkadilmil FROM sys_config WHERE id=85) AS ac,
								(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=$idterdakwa";
		//echo $qry;
		//exit();
		$query=$this->db->query($qry);
		return $query;
		} catch (Exception $e) {
		}
	}

	function cetak_permohonan_kasasi_oditur($idperkara,$idpembuatakta,$idput,$idpemohon){
		try {
			$sql="SELECT a.pemohon_id,
					z.nomor_perkara,a.permohonan_kasasi,a.pemberitahuan_kasasi,
					a.penerimaan_memori_kasasi,a.penyerahan_memori_kasasi,a.penerimaan_memori_kasasi,
					a.penyerahan_memori_kasasi,a.penerimaan_kontra_kasasi,a.penyerahan_kontra_kasasi,
					a.pemberitahuan_inzage_kasasi,a.pemberitahuan_putusan_kasasi,a.tanggal_cabut, 
					a.pihak_diwakili,
					z.perkara_id,
					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,	
					d.kesatuan AS kode_otmil, 
					pkas.nomor_putusan_kasasi,
					pkas.putusan_kasasi AS tgl_putusan_kasasi,
					pkas.tidak_memenuhi_syarat,
					pp2.nama AS namaTerdakwa,
					pp2.nrp AS nrpTerdakwa,
					pp2.pangkat AS pangkatTerdakwa,
					pp2.kesatuan AS kesatuanTerdakwa, 
					zput.tanggal_bht,
					put.*, 
					info.*, 
					(SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(VALUE,' ',3),' ',-1) FROM sys_config WHERE id = 76) AS kota_pt
				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_putusan zput ON zput.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 and c.id=a.pihak_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.kadilmil, ab.nrpkadilmil, ac.pangkatkadilmil,
								IF (b.nama = 'PanSekNama','Panitera','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 kadilmil FROM sys_config WHERE id=64) AS aa,
								(SELECT id,VALUE AS nrpkadilmil FROM sys_config WHERE id=65) AS ab,
								(SELECT id,VALUE AS pangkatkadilmil FROM sys_config WHERE id=85) AS ac,
								(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";
			
			return $this->db->query($sql);
		} 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;
}
}
/* End of file m_template_mil_permohonan_akta_kasasi_pid.php */
/* Location: ./application/models/generate_template/m_template_mil_permohonan_akta_kasasi_pid.php */