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

class m_template_penetapan_mil extends CI_Model {

	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";
			$query=$this->db->query($sql);
			return $query;	
		}catch(Exception $e) { return false; }
	}

	function getDataPenetapanHakimMil($idperkara,$pembuat){
		try {
			if(!is_numeric($idperkara) OR !is_numeric($pembuat)){
				return 'dd';
			}
			return $this->db->query("SELECT 
								  a.tanggal_pendaftaran,a.nomor_perkara, a.nomor_urut_perkara, a.tanggal_surat AS tgl_surat_dakwaan, a.nomor_surat, b.matra, b.tanggal_skeppera, b.nomor_skeppera, b.tanggal_surat_dakwaan, b.nomor_surat_dakwaan,
								  c.tanggal_sidang, c.jam_sidang, c.agenda, c.ruangan,  
								  p2.*, e.penetapan_majelis_hakim, e.penetapan_panitera_pengganti,
								  f.nama_gelar AS nama_hakim, f.urutan AS urutan_hakim, f.jabatan_hakim_id, f.jabatan_hakim_nama, pp.nama_gelar AS nama_pp, pp.tanggal_penetapan AS tgl_pen_pp,
								  e.penetapan_hari_sidang AS penetapanharisidang, e.sidang_pertama AS sidang_pertama,
								  (SELECT VALUE FROM sys_config WHERE id = '64') AS namaketua,
								  (SELECT VALUE FROM sys_config WHERE id = 62) AS namaPengadilan,
								  (SELECT VALUE FROM sys_config WHERE id = 64) AS KetuaPNNama,
								  (SELECT VALUE FROM sys_config WHERE id = 63) AS AlamatPN,
								  (SELECT VALUE FROM sys_config WHERE id = 66) AS WakilKetuaPNNama,
								  (SELECT VALUE FROM sys_config WHERE id = 68) AS PansekPNNama,
								  pn.* , info.* 
								FROM
								  perkara AS a 
								  LEFT JOIN perkara_matra AS b ON a.perkara_id = b.perkara_id 
								  LEFT JOIN 
								    (SELECT 
								      'T' AS pihak, p.nama AS namat,p.tempat_lahir AS tlt,p.tanggal_lahir AS tgllhrt,
								      IF(p.jenis_kelamin = 'L','Laki-Laki','Perempuan') AS jkt,
								      p.alamat AS alamatt, p.agama_nama AS agamat,p.status_kawin AS kawint, p.pekerjaan AS pekerjaant,
								      p.pendidikan AS pendidikant,p.warga_negara AS wnt, p2.urutan AS urutanT,p2.perkara_id 
								      FROM
								      perkara_pihak2 AS p2 
								      LEFT JOIN pihak AS p  ON p2.pihak_id = p.id 
								    WHERE p2.perkara_id = $idperkara) AS p2 ON p2.perkara_id = $idperkara 
								  LEFT JOIN perkara_penetapan AS e ON e.perkara_id = a.perkara_id 
								  LEFT JOIN perkara_jadwal_sidang AS c ON c.perkara_id=a.perkara_id AND c.urutan=1
								  LEFT JOIN (SELECT 
									      b.nama_gelar,
									      a.perkara_id,
									      a.tanggal_penetapan,
									      a.hakim_id,
									      a.urutan,
									      a.jabatan_hakim_id,
									      a.jabatan_hakim_nama 
									FROM
									      perkara_hakim_pn AS a,
									      hakim_pn AS b 
									WHERE a.hakim_id = b.id 
									AND a.perkara_id = $idperkara 
									AND a.aktif = 'y') AS f  ON f.perkara_id = a.`perkara_id` 
								  LEFT JOIN (SELECT a.perkara_id AS ppperkaraid, a.tanggal_penetapan, a.panitera_id, a.urutan, b.nama_gelar 
									    FROM
									      perkara_panitera_pn AS a,
									      panitera_pn AS b 
									    WHERE a.panitera_id = b.id 
									      AND a.perkara_id = $idperkara) AS pp 
									    ON pp.ppperkaraid = a.perkara_id 
								  LEFT JOIN (SELECT id, nama AS pengadilan,jenis_pengadilan, kode_pn 
										FROM  pengadilan_negeri) AS pn   ON pn.id =  (SELECT  VALUE FROM sys_config WHERE id = '81') 
								  LEFT JOIN (SELECT  id, NAME, VALUE as ditetapkanoleh FROM sys_config) AS info  ON info.id IN ('64', '66','68','70') 
								WHERE a.perkara_id = $idperkara
								AND info.id = $pembuat;");
		} 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;
	}
}
?>