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

class m_template_putusan_pidana extends CI_Model {

	function getDataPenetapanSidang($idperkara){
			try{
			if(!is_numeric($idperkara)) { return ''; }
			$sql = "SELECT a.penetapan_hari_sidang, a.sidang_pertama, b.tanggal_sidang,  b.jam_sidang
					FROM perkara_penetapan AS a, perkara_jadwal_sidang AS b
					WHERE a.perkara_id=b.perkara_id AND b.urutan=1 AND a.perkara_id = ".$idperkara."";
			$query=$this->db->query($sql);
			return $query;
		} catch(Exception $e) { return false; }
	}

	function getRefJenisTahanan(){
		try{
			return $this->db->query( "SELECT * FROM jenis_tahanan");
		} 
		catch(Exception $e) { return false; }
	}

	function getDataPerkara($idperkara){
		try{
			if(!is_numeric($idperkara))	{ return ''; }
			$sql = "SELECT nomor_perkara,nomor_surat,tanggal_surat, tanggal_pendaftaran, dakwaan 
					FROM perkara 
					WHERE perkara_id=".$idperkara.";";
			$query=$this->db->query($sql);
			return $query;
		}catch(Exception $e) { return false;	}
	}

	function getJenisPerkara($idperkara){
		try{
			if(!is_numeric($idperkara)) { return ''; }
			$sql = "SELECT nama 
					FROM jenis_perkara 
					WHERE id=(SELECT jenis_perkara_id 
								FROM perkara 
								WHERE perkara_id=".$idperkara.")";
			$query=$this->db->query($sql);
			return $query;
		}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;";
			$query=$this->db->query($sql);
			return $query;
		}catch(Exception $e) { return false; }
	}

	function getDataPenetapan($idperkara){
		try{
			if(!is_numeric($idperkara)) { return ''; }
			$sql = "SELECT * 
					FROM perkara_penetapan 
					WHERE perkara_id=".$idperkara.";";
			$query=$this->db->query($sql);
			return $query;
		} catch(Exception $e) {	return false; }
	}

	function getDataPihakPenuntut($idperkara){
		try{
			if(!is_numeric($idperkara)) { return ''; }
			$sql = "SELECT pihak_id, urutan, nama, alamat 
					FROM perkara_pihak1 
					WHERE perkara_id=".$idperkara." 
					ORDER BY urutan ASC";
			$query=$this->db->query($sql);
			return $query;
		}catch(Exception $e) { return false; }
	}

	function getDataPengacara($idperkara,$pihakke){
		try{
			if(!is_numeric($idperkara)) { return ''; }
			$sql = "SELECT pihak_id, urutan, b.nama, b.alamat, pekerjaan, a.id 
					FROM perkara_pengacara a, pihak b
					WHERE a.pihak_id=b.id AND perkara_id=".$idperkara." AND pihak_ke=".$pihakke."
					ORDER BY urutan ASC";
			$query=$this->db->query($sql);
			return $query;
		} catch(Exception $e) { return false; }
	}

	function getDataPihak($idperkara){
		try {
				$sql = "SELECT * FROM pihak AS p
						LEFT JOIN perkara_pihak2 AS pp
						ON p.id = pp.pihak_id
						WHERE pp.perkara_id=$idperkara";
				$query=$this->db->query($sql);
				return $query;
			} catch(Exception $e) {	return false; }
	}

	function getDataPanitera($idperkara){
		try{
			if(!is_numeric($idperkara)) { return ''; }
			$sql = "SELECT DISTINCT perkara_id, urutan,panitera_id, panitera_nama, tanggal_penetapan 
					FROM perkara_panitera_pn
					WHERE perkara_id=$idperkara";
			$query=$this->db->query($sql);
			return $query;
		} catch(Exception $e) {	return false; }
	}

	function getDataTerdakwa($idperkara){
		try {
			if(!is_numeric($idperkara)){
					return '';
			}
				$sql="SELECT a.urutan AS urutan, 
							 a.pihak_id AS pihak_id, 
							 b.*
						   
							 FROM perkara_pihak2 AS a
							 LEFT JOIN pihak AS b ON a.pihak_id=b.id
							  WHERE a.perkara_id=$idperkara
							  ORDER BY a.urutan ASC";

			$query=$this->db->query($sql);
			return $query;
		} catch(Exception $e) {
			return false;
		}
	}

	function getDataPihakTerdakwa1($idperkara, $idpihak){
		try {
			if(!is_numeric($idperkara) OR !is_numeric($idpihak)){
					return '';
			}
			$sql = "SELECT pihak_id, urutan,  nama, alamat, tempat_lahir, tanggal_lahir, umur, jenis_kelamin, warga_negara, agama_nama, pekerjaan  
					FROM perkara_pihak2 
					WHERE perkara_id = $idperkara and pihak_id=$idpihak 
					ORDER BY urutan ASC";
			$query=$this->db->query($sql);
			return $query;
		}catch(Exception $e) {
			return false;
		}
	}

	function getDataPenahananTerdakwa($idperkara){
		try{
			if(!is_numeric($idperkara)) { return ''; }
			$sql = "SELECT jp.nama AS oleh, sp.nama AS status_penahanan, jt.nama AS jenis_tahanan, 
					pt.mulai AS sejak, pt.sampai, pt.lama_penahanan, pt.pihak_id 
					FROM (SELECT * 
							FROM penahanan_terdakwa 
							WHERE perkara_id=$idperkara  
							ORDER BY jenis_penahanan_id, mulai ASC) pt 
					LEFT JOIN jenis_penahanan jp ON pt.jenis_penahanan_id = jp.id
					LEFT JOIN status_penahanan sp ON pt.status_penahanan_id = sp.id
					LEFT JOIN jenis_tahanan jt ON pt.jenis_tahanan_id = jt.id";
			$query=$this->db->query($sql);
			return $query;
		}catch(Exception $e) { return false; }
	}

	function getDataHakim($idperkara){
		try{
			if(!is_numeric($idperkara)) { return ''; }
			$sql = "SELECT * 
					FROM (SELECT perkara_id, urutan, jabatan_hakim_id, jabatan_hakim_nama, hakim_id, tanggal_penetapan, nomor_sk_penetapan 
							FROM perkara_hakim_pn 
							WHERE perkara_id = $idperkara 
							GROUP BY hakim_id) AS ph
					LEFT JOIN (SELECT id, nama_gelar 
								FROM hakim_pn) AS h
					ON ph.hakim_id = h.id
					ORDER BY jabatan_hakim_id,urutan ASC";
			$query=$this->db->query($sql);
			return $query;
		}catch(Exception $e) { return false; }
	}

	function dataPenahanan($idperkara,$idpihak){
		try {
			if(!is_numeric($idperkara) OR !is_numeric($idpihak)){
					return '';
			}
			$sql = "SELECT 
					penahanan_terdakwa.perkara_id AS perkaraid,
					penahanan_terdakwa.jenis_penahanan_id AS penahanan_id,
					jenis_penahanan.nama AS jenis_penahanan,
					jenis_tahanan.nama AS jenis_tahanan,
					penahanan_terdakwa.tanggal_surat AS tgl_surat_penahanan,
					penahanan_terdakwa.nomor_surat AS nomor_surat_penahanan,
					penahanan_terdakwa.mulai AS penahanan_mulai,
					penahanan_terdakwa.sampai AS penahanan_sampai,
					penahanan_terdakwa.lama_penahanan AS penahanan_lama
					FROM penahanan_terdakwa
					LEFT JOIN jenis_penahanan ON jenis_penahanan.id=penahanan_terdakwa.jenis_penahanan_id
					LEFT JOIN jenis_tahanan ON jenis_tahanan.id=penahanan_terdakwa.jenis_tahanan_id
					WHERE penahanan_terdakwa.perkara_id=$idperkara AND penahanan_terdakwa.pihak_id=$pihakid";
			$query=$this->db->query($sql);
			return $query;
		 } catch (Exception $e) { return false; }
	}

	function getDataPenetapanHakim($idperkara){
		try{
			if(!is_numeric($idperkara)) { return ''; }
			$sql = "SELECT * FROM 
					(SELECT perkara_id,urutan,jabatan_hakim_id, jabatan_hakim_nama, hakim_nama, hakim_id,tanggal_penetapan FROM perkara_hakim_pn WHERE perkara_id = $idperkara GROUP BY hakim_id) AS ph
					LEFT JOIN (SELECT id, nama_gelar FROM hakim_pn) AS h
					ON ph.hakim_id = h.id
					ORDER BY urutan ASC";
			$query=$this->db->query($sql);
			return $query;
		} catch(Exception $e) { return false; }
	}

	function getDataPutusan($idperkara){
		try{
			if(!is_numeric($idperkara)) { return ''; }
			$sql = "SELECT * 
					FROM perkara_putusan 
					WHERE perkara_id=$idperkara";
			$query=$this->db->query($sql);
			return $query;
		}catch(Exception $e) { return false; }
	}

	function getDataPutusanSela($idperkara){
		try{
			if(!is_numeric($idperkara)) { return ''; }
			$sql = "SELECT * 
					FROM perkara_putusan_sela 
					WHERE perkara_id=$idperkara";
			$query=$this->db->query($sql);
			return $query;
		}
		catch(Exception $e) { return false; }
	}

	function getDataJadwalSidang($idperkara){
		try{
			$sql = "SELECT * 
					FROM perkara_jadwal_sidang 
					WHERE perkara_id=$idperkara
					ORDER BY tanggal_sidang ASC";
			$query=$this->db->query($sql);
			return $query;
		} 
		catch(PDOException $e) { return false; }
	}

	function getDataTuntutan($idperkara){
		try{
			$sql = "SELECT isi_penuntutan FROM perkara_penuntutan WHERE perkara_id=$idperkara ";
			$query=$this->db->query($sql);
			return $query;
		} 
		catch(PDOException $e) { return false; }
	}

	function getDataBarangBukti($idperkara){
		try{
			$sql = "SELECT jenis_barang_bukti FROM perkara_barang_bukti WHERE perkara_id=$idperkara ";
			$query=$this->db->query($sql);
			return $query;
		} 
		catch(PDOException $e) { return false; }
	}

}
?>