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

class m_template_penetapan_pdt_pp 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 getdata_pp($idperkara){
		try {
			$qry="SELECT a.panitera_id , b.nama_gelar
                FROM 
                perkara_panitera_pn AS a ,
                panitera_pn AS b
                WHERE 
                a.panitera_id=b.id AND
                a.perkara_id=$idperkara";
			return $this->db->query($qry);
		} catch (Exception $e) {
		}
	}
    
	function get_data_penetapan_pp($idperkara,$pembuat){
		try {
			if($pembuat=="1"){$pembuat="68";}else{$pembuat="70";}
			return $this->db->query("SELECT 	d.*,
					IF(d.pengacara_idP IS NOT NULL,CONCAT('Berkedudukan di  ',d.alamatP,'dalam hal ini memberikan kuasa kepada ',d.namaPengacaraP,' beralamat di ',d.alamatPengacaraP,' berdasarkan Surat Kuasa Khusus tanggal  ',IF(a.tanggal_surat IS NULL,'.....',a.tanggal_surat),' sebagai '),
						CONCAT('Bertempat tinggal di ',d.`alamatP`,' , Sebagai ')) AS statusP,
					j.*,
					IF(j.pengacara_idT IS NOT NULL,CONCAT('Berkedudukan di  ',j.alamatT,'dalam hal ini memberikan kuasa kepada ',j.namaPengacaraT,' beralamat di ',j.alamatPengacaraT,' berdasarkan Surat Kuasa Khusus tanggal  ',IF(a.tanggal_surat IS NULL,'.....',a.tanggal_surat),' sebagai '),
						CONCAT('Bertempat tinggal di ',j.`alamatT`,' , Sebagai ')) AS statusT, 
				info.*, 
				a.tanggal_pendaftaran, a.nomor_perkara, a.tanggal_surat, a.nomor_surat,
				e.penetapan_majelis_hakim, e.penetapan_panitera_pengganti
				FROM perkara AS a 
				LEFT JOIN 	(SELECT c.pengacara_id AS pengacara_idP, 
							a.perkara_id AS perkara_idP,
							b.`pihak_id` AS pihak_idP, 
							b.nama AS namaP, 
							b.urutan AS urutanP,
							b.alamat AS alamatP,
							c.nama AS namaPengacaraP,
							c.alamat AS alamatPengacaraP, 
							c.pihak_id AS pihak_idPC
							FROM 	perkara AS a
							LEFT JOIN perkara_pihak1 AS b ON a.perkara_id=b.perkara_id
							LEFT JOIN perkara_pengacara AS c ON a.perkara_id=c.perkara_id AND FIND_IN_SET(b.pihak_id,c.pihak_id)
							) AS d ON d.perkara_idP=$idperkara
				LEFT JOIN 	(SELECT c.pengacara_id AS pengacara_idT, 
							a.perkara_id AS perkara_idT, 
							b.`pihak_id` AS pihak_idT,
							b.urutan AS urutanT,
							b.nama AS namaT,
							b.alamat AS alamatT,
							c.nama AS namaPengacaraT,
							c.alamat AS alamatPengacaraT, 
							c.pihak_id AS pihak_idTC
							FROM perkara AS a
							LEFT JOIN perkara_pihak2 AS b ON a.perkara_id=b.perkara_id 
							LEFT JOIN perkara_pengacara AS c ON a.perkara_id=c.perkara_id AND  FIND_IN_SET(b.pihak_id,c.pihak_id)
							) AS j ON j.perkara_idT=$idperkara
				LEFT JOIN 	(SELECT (SELECT value FROM sys_config WHERE id=62) AS namaPengadilan,
							a.id,
							IF(a.name = 'PanSekNama','Panitera','Wakil Panitera') AS jabatan,
							a.value AS nama_pejabat
							FROM (SELECT id,name,value FROM sys_config UNION SELECT id,name, value FROM sys_config) AS a
							) AS info ON info.id IN ('68','70')
				
							LEFT JOIN perkara_penetapan AS e ON  e.perkara_id=a.perkara_id
				WHERE a.perkara_id=$idperkara AND info.id=$pembuat");
		} catch (Exception $e) {
		}
	}
}