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

class m_template_pemberitahuan_putusan_tun extends CI_Model {

	function getDataUmum($idperkara){
		try {
			return $this->db->query("SELECT pkr.nomor_perkara, ptus.tanggal_putusan, p3.panitera_nama,ptus.amar_putusan
								FROM perkara AS pkr
								LEFT JOIN perkara_putusan AS ptus ON pkr.perkara_id=ptus.perkara_id
								LEFT JOIN perkara_panitera_pn AS p3 ON pkr.perkara_id=p3.perkara_id AND p3.aktif='Y' AND p3.urutan=1
								WHERE pkr.perkara_id=".$idperkara);
		} catch (Exception $e) {
		}
	}

	function getDataPenggugat($idperkara,$idpenggugat){
		try {
			return $this->db->query("SELECT pihak1.urutan AS urutanP, pihak1.nama AS namaP, 
								pihak1.alamat AS alamatP, p.warga_negara AS wargaP, p.pekerjaan AS pekerjaanP,p4.tanggal_pemberitahuan_putusan 
								FROM perkara_pihak1 AS pihak1 
								LEFT JOIN pihak AS p ON pihak1.pihak_id=p.id
								LEFT JOIN perkara_putusan_pemberitahuan_putusan AS p4 ON pihak1.id=p4.pihak_id 
								WHERE pihak1.perkara_id=".$idperkara."  AND pihak1.pihak_id=".$idpenggugat);
		} 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 getKuasaHukumP($idperkara){
		try {
			return $this->db->query("SELECT a.pengacara_id AS id,a.urutan, a.pihak_id, a.nama, b.alamat, b.pekerjaan 
				FROM perkara_pengacara a, pihak b
				WHERE a.pengacara_id=b.id AND perkara_id='$idperkara' AND pihak_ke=1");
		} catch (Exception $e) {
		}
	}


	function getDataTergugat($idperkara,$idtergugat){
		try {
			return $this->db->query("SELECT pihak2.urutan AS urutan, pihak2.nama AS namaT, 
									pihak2.alamat AS alamatT, p.warga_negara AS wargaT, p.pekerjaan AS pekerjaanT,p4.tanggal_pemberitahuan_putusan 
									FROM perkara_pihak2 AS pihak2 
									LEFT JOIN pihak AS p ON pihak2.pihak_id=p.id
									LEFT JOIN perkara_putusan_pemberitahuan_putusan AS p4 ON pihak2.id=p4.pihak_id 
									WHERE pihak2.perkara_id=".$idperkara." AND pihak2.pihak_id=".$idtergugat);
		} catch (Exception $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 getDataPN(){
		try{
			$sql = "SELECT * FROM 
					(SELECT value AS NamaPN FROM sys_config WHERE id = 62) AS T1,
					(SELECT value AS KetuaPNNama FROM sys_config WHERE id = 64) AS T2,
					(SELECT value AS AlamatPN FROM sys_config WHERE id = 63) AS T3,
					(SELECT value AS WakilKetuaPNNama FROM sys_config WHERE id = 66) AS T4,
					(SELECT value AS PansekPNNama FROM sys_config WHERE id = 68) AS T5,
					(SELECT value AS WaPanNama FROM sys_config WHERE id = 70) AS T6, 
					(SELECT value AS ZonaWaktu FROM sys_config WHERE id = 75) AS T7";
			$query=$this->db->query($sql);
			return $query;	
		}catch(Exception $e) { return false; }
	}

	#penetapan persiapan
	function getDataPenetapanPersiapan($idperkara){
		try{
			if(!is_numeric($idperkara)){
						return '';
					}
			return $this->db->query ("SELECT p.nomor_perkara, p.tanggal_surat,p.tanggal_pendaftaran,ps.tanggal_penetapan_persiapan, j.id,j.tanggal_musyawarah,j.agenda_musyawarah,j.waktu_musyawarah,ph.tanggal_penetapan,ph.hakim_nama
									FROM perkara AS p
									LEFT JOIN perkara_persiapan AS ps ON p.perkara_id= ps.perkara_id
									LEFT JOIN perkara_persiapan_proses AS j ON p.perkara_id=j.perkara_id AND j.urutan=1
									LEFT JOIN perkara_hakim_pn AS ph ON p.perkara_id=ph.perkara_id AND ph.urutan=1
									WHERE p.perkara_id=".$idperkara);
			}catch(PDOException $e) {
		}
	}

	function getDataPersiapan($idperkara,$idjadwal){
		try{
			if(!is_numeric($idperkara) AND !is_numeric($idjadwal) ){
						return '';
					}
			return $this->db->query ("SELECT p.nomor_perkara, ps.tanggal_penetapan_persiapan, j.id,j.tanggal_musyawarah,j.agenda_musyawarah,j.waktu_musyawarah,pp.panitera_nama
									FROM perkara AS p
									LEFT JOIN perkara_persiapan AS ps ON p.perkara_id= ps.perkara_id
									LEFT JOIN perkara_persiapan_proses AS j ON p.perkara_id=j.perkara_id
									LEFT JOIN perkara_panitera_pn AS pp ON p.perkara_id=pp.perkara_id AND pp.urutan=1
									WHERE p.perkara_id=".$idperkara." AND j.id=".$idjadwal);
			}catch(PDOException $e) {
		}
	}

	function getListPihak($idperkara,$pihakke){
		try{
			return $this->db->query("SELECT pihak_id AS id, nama FROM perkara_pihak".$pihakke." WHERE perkara_id=$idperkara");
		}catch(Exception $e){ return false; }
	}

	function penandaTangan(){
		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) {				
		}
	}

	#Data Dismissal
	function getPanggilDismissal($idperkara,$idjadwal){
		try{
			if(!is_numeric($idperkara) AND !is_numeric($idjadwal) ){
						return '';
					}
			return $this->db->query ("SELECT p.nomor_perkara,ppd.tanggal_panggilan,ppd.waktu
								FROM perkara AS p
								LEFT JOIN perkara_panggilan_dismissal AS ppd ON p.perkara_id = ppd.perkara_id
								WHERE p.perkara_id=".$idperkara." AND ppd.id=".$idjadwal);
			}catch(PDOException $e) {
		}
	}

}

?>