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

class m_template_tun_pemberitahuan_kontra_memori_pk 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){
		try {
			return $this->db->query("SELECT 
				CONCAT(pihak_nama,' / ',status_pihak_text,'/',pihak_asal_text) AS pihak_nama,
				status_pihak_text,
				IF(pihak_diwakili='Y', CONCAT(pemohon_nama), 'Tidak Diwakili') AS diwakili
				FROM perkara_pk_detil WHERE perkara_id=$idperkara AND permohonan_pk IS NOT NULL AND status_pihak_id=1 ");
		} 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 RI', nomor_perkara_kasasi,putusan_kasasi,perkara_id
		 			FROM perkara_kasasi WHERE perkara_id=$idperkara) AS tahapan 
                	WHERE tahapan.perkara_id=$idperkara");
		} catch (Exception $e) {
		}
	}

//-------------------------------------------------------//	
		function getPihakPenggugat($idperkara){
		try{
			if(!is_numeric($idperkara)){
						return '';
					}
			return $this->db->query ("SELECT pihak1.pihak_id, pihak1.urutan, pihak1.nama, pihak1.alamat, pihak1.jenis_pihak_id AS info, p.pekerjaan, IF(p.warga_negara IS NULL,'Indonesia','') AS warga_negara
									FROM perkara_pihak1 AS pihak1
									LEFT JOIN pihak AS p ON pihak1.pihak_id=p.id
									WHERE 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, 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
									WHERE perkara_id =".$idperkara." ORDER BY urutan ASC");
			}catch(PDOException $e) {
		}
	}

	function getPihakIntervensi($idperkara){
		try{
			if(!is_numeric($idperkara)){
						return '';
					}
			return $this->db->query ("SELECT pihak4.pihak_id, pihak4.urutan, pihak4.nama, pihak4.alamat, pihak4.jenis_pihak_id AS info, p.pekerjaan, IF(p.warga_negara IS NULL,'Indonesia','') AS warga_negara
									FROM perkara_pihak4 AS pihak4
									LEFT JOIN pihak AS p ON pihak4.pihak_id=p.id
									WHERE perkara_id =".$idperkara." ORDER BY urutan ASC");
			}catch(PDOException $e) {
		}
	}
//-----------------------------------------------------//

	function getDataJaksaPK($idperkara){
		try {
			return $this->db->query("SELECT  p.*,q.*, r.*
				FROM 
					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 p.pihak_asal='1' AND p.pihak_id=q.pihak_id AND p.perkara_id=$idperkara");
		} catch (Exception $e) {
		}
	}

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

	function infoDataTerdakwa($idperkara){
		try {
			return $this->db->query("SELECT 	pihak_id,perkara_id, 
				status_pihak_id, 
				permohonan_pk AS tglPemohonPK, 
				permohonan_pk,
				pihak_asal, 
				CONCAT(pihak_nama,' / ',pihak_asal_text) AS  pihak_nama,
				pihak_diwakili, 
				pemohon_id 
			FROM 	perkara_pk_detil 
			WHERE 	pihak_diwakili='T' AND perkara_id=$idperkara AND pihak_asal=2");
		} 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 getAllJurusita(){
		try{			
		return $this->db->query("SELECT id, nama_gelar FROM jurusita WHERE aktif!='T' GROUP BY nama_gelar ASC");
		} catch(PDOException $e) {		
		}
	}
	function cetakJurusita($idjurusita){
		try{
			if(!is_numeric($idjurusita)){
					return '';
		}			
		return $this->db->query("SELECT id, nama_gelar FROM jurusita WHERE id=".$idjurusita);
		} catch(PDOException $e) {		
		}
	}

	function cetak_relass_kontra_memori_pk($idperkara,$idpembuatakta,$idput){
		try {			
			$sql="	SELECT	a.pihak_id,
							a.pihak_nama, 
							z.nomor_perkara, 
							a.permohonan_pk, 
							c.nama AS namaPihak, 
							c.alamat AS alamatPihak, 
							c.tempat_lahir AS tempatLahirPihak, 
							c.tanggal_lahir AS tanggalLahirPihak, 
							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, 
							a.pemberitahuan_putusan_pk, a.penerimaan_memori_pk,
							a.penyerahan_memori_pk, a.penyerahan_kontra_pk,
							put.*, 
							info.*, 
							putPK.* 
					FROM 	perkara_pk_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 UNION ALL SELECT '3' AS idput, 
													'Mahkamah Agung RI', 
													nomor_perkara_kasasi,
													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, 
													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) 
										LEFT JOIN (SELECT 	perkara_id,
													nomor_perkara_kasasi,
													putusan_kasasi,
													amar_putusan_pk 
											FROM perkara_pk WHERE perkara_id=$idperkara) AS putPK ON putPK.perkara_id=$idperkara 
					WHERE 	a.perkara_id=$idperkara AND 
							info.id=$idpembuatakta AND 
							a.permohonan_pk IS NOT NULL AND put.idput=$idput ";
										$query=$this->db->query($sql);
										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;
}
}
