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

class m_template_agama_pk extends CI_Model {
	function getDataPN(){
		try {
			return $this->db->query ("SELECT * FROM 
					(SELECT value AS NamaPN FROM sys_config WHERE id = 62) AS T1,
					(SELECT value AS Panses FROM sys_config WHERE id = 68) AS T2,
					(SELECT value AS AlamatPN FROM sys_config WHERE id = 63) AS T3,
					(SELECT value AS Wapan FROM sys_config WHERE id = 70) AS T4,
					(SELECT value AS ZonaWaktu FROM sys_config WHERE id = 75) AS T5,
					(SELECT value AS NamaPT FROM sys_config WHERE id = 76) AS T6,
					(SELECT value AS Ketua FROM sys_config WHERE id = 64) AS T7,
					(SELECT value AS Wakilketua FROM sys_config WHERE id = 66) AS T8;");
			} catch(PDOException $e) {
			}
	}

	function getKuasaPenggugat($idperkara,$idpihak){
		try {
			if(!is_numeric($idperkara) AND !is_numeric($idpihak)){
					return '';
				}
			return $this->db->query("SELECT  pemohon_nama AS nama, pemohon_id AS IDPihak, pihak_asal AS pihakke, permohonan_banding AS tglPemberitahuan, pemohon_tanggal_surat AS tglKuasa, pemohon_nomor_surat AS nomorKuasa, penerimaan_memori_banding AS tglMemori 
						FROM perkara_pk_detil 
						WHERE pemohon_id NOT IN (SELECT pihak_id FROM perkara_pihak2 WHERE perkara_id =$idperkara)
						AND perkara_id =$idperkara AND pihak_asal = 1 AND pihak_id=$idpihak AND pihak_diwakili='Y' ");
		} catch(PDOException $e) {		
		}
	}

	function getKuasaTergugat($idperkara,$idpihak){
		try {
			if(!is_numeric($idperkara) AND !is_numeric($idpihak)){
					return '';
				}
			return $this->db->query("SELECT  pemohon_nama AS nama, pemohon_id AS IDPihak, pihak_asal AS pihakke, permohonan_banding AS tglPemberitahuan, pemohon_tanggal_surat AS tglKuasa, pemohon_nomor_surat AS nomorKuasa, penerimaan_memori_banding AS tglMemori 
						FROM perkara_pk_detil 
						WHERE pemohon_id NOT IN (SELECT pihak_id FROM perkara_pihak1 WHERE perkara_id =$idperkara)
						AND perkara_id =$idperkara AND pihak_asal = 2 AND pihak_id=$idpihak AND pihak_diwakili='Y' ");
		} catch(PDOException $e) {		
		}
	}
	function getKuasaIntervensi($idperkara,$idpihak){
		try {
			if(!is_numeric($idperkara) AND !is_numeric($idpihak)){
					return '';
				}
			return $this->db->query("SELECT  pemohon_nama AS nama, pemohon_id AS IDPihak, pihak_asal AS pihakke, permohonan_banding AS tglPemberitahuan, pemohon_tanggal_surat AS tglKuasa, pemohon_nomor_surat AS nomorKuasa, penerimaan_memori_banding AS tglMemori 
						FROM perkara_pk_detil 
						WHERE pemohon_id NOT IN (SELECT pihak_id FROM perkara_pihak1 WHERE perkara_id =$idperkara)
						AND pemohon_id NOT IN ((SELECT pihak_id FROM perkara_pihak2 WHERE perkara_id =$idperkara))
						AND perkara_id =$idperkara AND pihak_asal = 3 AND pihak_id=$idpihak AND pihak_diwakili='Y' ");
		} catch(PDOException $e) {		
		}
	}
	function infoKuasaPenggugat($idperkara,$idpihak){
		try {
			if(!is_numeric($idperkara) AND !is_numeric($idpihak)){
					return '';
				}
			return $this->db->query("SELECT  pemohon_nama AS nama, pemohon_id AS IDPihak, pihak_asal AS pihakke, permohonan_banding AS tglPemberitahuan, pemohon_tanggal_surat AS tglKuasa, pemohon_nomor_surat AS nomorKuasa, penerimaan_memori_banding AS tglMemori 
						FROM perkara_pk_detil 
						WHERE perkara_id =$idperkara AND pihak_asal = 1 AND pemohon_id=$idpihak AND pihak_diwakili='Y' ");
		} catch(PDOException $e) {		
		}
	}
	function infoKuasaTergugat($idperkara,$idpihak){
		try {
			if(!is_numeric($idperkara) AND !is_numeric($idpihak)){
					return '';
				}
			return $this->db->query("SELECT  pemohon_nama AS nama, pemohon_id AS IDPihak, pihak_asal AS pihakke, permohonan_banding AS tglPemberitahuan, pemohon_tanggal_surat AS tglKuasa, pemohon_nomor_surat AS nomorKuasa, penerimaan_memori_banding AS tglMemori 
						FROM perkara_pk_detil 
						WHERE perkara_id =$idperkara AND pihak_asal = 2 AND pemohon_id=$idpihak AND pihak_diwakili='Y' ");
		} catch(PDOException $e) {		
		}
	}
	function infoKuasaIntervensi($idperkara,$idpihak){
		try {
			if(!is_numeric($idperkara) AND !is_numeric($idpihak)){
					return '';
				}
			return $this->db->query("SELECT  pemohon_nama AS nama, pemohon_id AS IDPihak, pihak_asal AS pihakke, permohonan_banding AS tglPemberitahuan, pemohon_tanggal_surat AS tglKuasa, pemohon_nomor_surat AS nomorKuasa, penerimaan_memori_banding AS tglMemori 
						FROM perkara_pk_detil 
						WHERE perkara_id =$idperkara AND pihak_asal = 3 AND pemohon_id=$idpihak AND pihak_diwakili='Y' ");
		} catch(PDOException $e) {		
		}
	}
	
	function getInfo($idperkara){
		try {
				$qry="SELECT 
				CONCAT(pihak_nama,' / ',status_pihak_text) AS pihak_nama,
				status_pihak_text,
				IF(pihak_diwakili='Y', CONCAT(pemohon_nama), 'Tidak Diwakili') AS diwakili, pihak_id
				FROM perkara_pk_detil WHERE perkara_id=$idperkara AND permohonan_pk IS NOT NULL AND status_pihak_id=1 ";
		return $this->db->query($qry);
		} catch (Exception $e) {
		}
	}
	function listAllPemohonpk($idperkara){
		try {
			return $this->db->query ("SELECT pihak_nama,pihak_asal_text,status_pihak_text,urutan_pk FROM perkara_pk_detil WHERE perkara_id=$idperkara AND status_pihak_id IN(1,2) AND pemohon_id IS NOT NULL;");
			} catch(PDOException $e) {
			}
	}
	function listAllTermohonpk($idperkara){
		try {
			return $this->db->query ("SELECT pihak_nama,pihak_asal_text,status_pihak_text,urutan_pk FROM perkara_pk_detil WHERE perkara_id=$idperkara AND status_pihak_id IN(3,4,5);");
			} catch(PDOException $e) {
			}
	}
	function listBiayaPemberitahuan($idperkara){
		try {
			return $this->db->query ("SELECT jenis_biaya_id,uraian,jumlah FROM perkara_biaya
							WHERE perkara_id=$idperkara AND kategori_id=6 AND tahapan_id=20;");
			} catch(PDOException $e) {
			}
	}

	function getDataPerkara($idperkara){
		try {
			return $this->db->query("SELECT * 
                    FROM perkara_pk WHERE 
                    perkara_id=$idperkara");
		} 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 limit 2");
		} catch (Exception $e) {
		}
	}
	function pembuatAkta(){
		try {			
			return $this->db->query("SELECT id,CONCAT('PanSek-> [ ',value,' ]') AS namaPanitera FROM sys_config WHERE id = '68' UNION
				SELECT id,CONCAT('WaPan-> [ ',value,' ]') AS namaPanitera FROM sys_config WHERE id = '70' ");
		} catch(PDOException $e) {				
		}
	}
	
	function cetak_AktaPermohonanPK($idperkara,$idpembuatakta,$pihakid,$idput){
		try {
			$qry="SELECT a.pihak_id,z.nomor_perkara,a.permohonan_pk,a.penerimaan_memori_pk, a.penerimaan_kontra_pk,
				c.nama AS namaPihak,
				c.alamat AS alamatPihak,
				c.tempat_lahir AS tempatLahirPihak,
				c.tanggal_lahir  AS tanggalLahirPihak,
				c.jenis_kelamin AS kelaminPihak,
				c.agama_nama  AS agamaPihak,
				c.pekerjaan  AS pekerjaanPihak,
				tp.nama  AS pendidikanPihak,
				c.warga_negara  AS negaraPihak,
				a.pihak_asal_text,a.status_pihak_text,
				a.tanggal_cabut AS tanggalCabutPK,
				put.*, info.*
		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 tingkat_pendidikan AS tp ON tp.id=c.pendidikan_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 a.pemohon_id=$pihakid  AND info.id=$idpembuatakta AND put.idput=$idput AND a.pihak_diwakili ='T' AND a.status_pihak_id='1' ";
		
		return $this->db->query($qry);
		} 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;
	}
	function cetaktglpk($id){
		try {
			return $this->db->query ("SELECT * FROM perkara_pk_detil WHERE id =".$id);
			} catch(PDOException $e) {
			}
	}
}