<?php 
if (!defined('BASEPATH')) exit('No direct script access allowed');
class m_template_tun extends CI_Model {

	function fetch_data($sql){
		try {

			$result = $this->db->query($sql);
			if($result->num_rows>0){
				return $result->row()->data;
			}else{
				return '';
			}
		} catch (Exception $e) {
			
		}
	}

	function fetch_filename($idtemplate){
		try {
			$result = $this->db->query("SELECT kode FROM template_dokumen WHERE id = ".$idtemplate);
			if($result->num_rows>0){
				return $result->row()->kode;
			}else{
				return '';
			}
		} catch (Exception $e) {
			
		}
	}
	function get_cek_perubahan_pp($idperkara){
		try{
			if(!is_numeric($idperkara)){ return '';	}
			$sql = "SELECT perkara_id,aktif
					FROM perkara_panitera_pn
					WHERE aktif='T' AND perkara_id=$idperkara";
			$query=$this->db->query($sql);
			return $query;
		}catch(Exception $e){ return false; }
	}
	function getDataPenetapanSidang($idperkara){
		try{
			$sql = "SELECT *
					FROM perkara_hakim_pn AS a
					lEFT JOIN
						perkara_jadwal_sidang AS b
					ON a.perkara_id=b.perkara_id AND b.urutan=1
					LEFT JOIN perkara_verzet as c
					ON a.perkara_id=c.perkara_id
					WHERE  verzet = 'Y' AND jabatan_hakim_id = 1 AND a.perkara_id=$idperkara";
			$query=$this->db->query($sql);
			return $query;
		}catch(Exception $e){ return false; }
	}

	function getDataJadwalSidang($idperkara,$idjadwal){
		try{
			return $this->db->query("SELECT * FROM perkara_jadwal_sidang WHERE  perkara_id=$idperkara AND id=$idjadwal");
		}catch(Exception $e){ return false; }
	}

	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,
					(SELECT value AS NamaPT FROM sys_config WHERE id = 76) AS T9,
					(SELECT value AS KodePN FROM sys_config WHERE id = 61) AS T8";
			$query=$this->db->query($sql);
			return $query;	
		}catch(Exception $e) { return false; }
	}

	function getDataPihak($idpihak){
		try{
			return $this->db->query("SELECT n.nama AS warganegara_text, p.* FROM pihak as p LEFT JOIN negara as n ON n.id = p.warga_negara_id WHERE  p.id=$idpihak");
		}catch(Exception $e){ return false; }
	}

	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 getPaniteraPkr($idperkara){
		try{
			return $this->db->query("SELECT panitera_nama FROM perkara_panitera_pn WHERE perkara_id=$idperkara");
		}catch(Exception $e){ return false; }
	}

	function getDataPerkara($idperkara){
		try{
			return $this->db->query("SELECT  tanggal_pendaftaran, tanggal_surat, nomor_perkara, petitum  FROM  perkara WHERE  perkara_id=$idperkara");
		}catch(Exception $e){ return false; }
	}
	function getBiayaPerkara($idperkara){
		try{
			return $this->db->query("SELECT SUM(IF(jenis_transaksi=-1,jumlah,0)) AS biaya FROM perkara_biaya WHERE perkara_id=$idperkara AND tahapan_id=10");
		}catch(Exception $e){ return false; }
	}
	function getDataPutusan($idperkara){
		try{
			return $this->db->query("SELECT tanggal_putusan, amar_putusan FROM perkara_putusan WHERE perkara_id=$idperkara");
		}catch(Exception $e){ return false; }
	}
	function getDataDismissal($idperkara){
		try{
			return $this->db->query("SELECT tanggal_penetapan, amar, hasil, pemberitahuan_penggugat, pemberitahuan_tergugat,ppd.tanggal_panggilan
								FROM perkara_dismissal AS pd
								LEFT JOIN perkara_panggilan_dismissal AS ppd ON pd.perkara_id=ppd.perkara_id
								WHERE pd.perkara_id=".$idperkara);
		}catch(Exception $e){ return false; }
	}
	function getDataPanggilanPersiapan($idperkara){
		try{
			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");
		}catch(Exception $e){ return false; }
	}

	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,a.aktif_mulai
				FROM perkara_pengacara a, pihak b
				WHERE a.pengacara_id=b.id AND perkara_id='$idperkara' AND pihak_ke=1");
		} catch (Exception $e) { return false; }
	}
	function getDataPerlawanan($idperkara){
		try{
			return $this->db->query("SELECT tanggal_surat, tanggal_pendaftaran_verzet, penetapan_majelis_hakim, tanggal_sidang_pertama_verzet, tanggal_penetapan_sidang_verzet, SUBSTRING_INDEX(majelis_hakim_nama,'</br>',1) AS ketuamajelis FROM perkara_verzet WHERE perkara_id=$idperkara");
		}catch(Exception $e){ return false; }
	}
	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 getPihakIntervensiPenggugat($idperkara){
		try{
			if(!is_numeric($idperkara)){
						return '';
					}
			return $this->db->query ("SELECT pihak3.pihak_id, pihak3.urutan, pihak3.nama, pihak3.alamat, pihak3.jenis_pihak_id AS info, p.pekerjaan, IF(p.warga_negara IS NULL,'Indonesia','') AS warga_negara
									FROM perkara_pihak3 AS pihak3
									LEFT JOIN pihak AS p ON pihak3.pihak_id=p.id 
									WHERE perkara_id =".$idperkara." AND tergabung_ke_pihak=1 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 getPihakIntervensiTergugat($idperkara){
		try{
			if(!is_numeric($idperkara)){
						return '';
					}
			return $this->db->query ("SELECT pihak3.pihak_id, pihak3.urutan, pihak3.nama, pihak3.alamat, pihak3.jenis_pihak_id AS info, p.pekerjaan, IF(p.warga_negara IS NULL,'Indonesia','') AS warga_negara
									FROM perkara_pihak3 AS pihak3
									LEFT JOIN pihak AS p ON pihak3.pihak_id=p.id 
									WHERE perkara_id =".$idperkara." AND tergabung_ke_pihak=2 ORDER BY urutan ASC");
			}catch(PDOException $e) {
		}
	}

	function getPenetapanHakim($idperkara){
		try{
			if(!is_numeric($idperkara)){
						return '';
					}
			return $this->db->query ("SELECT  ph.tanggal_penetapan,ph.nomor_sk_penetapan,ph.urutan,ph.hakim_nama,IF(pp.jenis_acara=1,'Acara Cepat','Acara Biasa') AS jenis_acara,
									pp.penetapan_hari_sidang,pp.sidang_pertama, pjs.jam_sidang
									FROM perkara_hakim_pn AS ph
									LEFT JOIN perkara_penetapan AS pp ON pp.perkara_id=ph.perkara_id
									LEFT JOIN (SELECT * FROM perkara_jadwal_sidang WHERE perkara_id IN (SELECT perkara_id FROM perkara_hakim_pn) AND urutan=1 AND verzet='T') AS pjs ON ph.perkara_id=pjs.perkara_id 
									WHERE ph.perkara_id=$idperkara AND ph.tahapan_id=10 AND ph.aktif='Y'");
			}catch(PDOException $e) {
		}
	}

}
?>