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

class m_template_putusan_perdata extends CI_Model {

function get_data_putusan_pdt($idperkara){
	try {
			if(!is_numeric($idperkara)){
				return 'dd';
			}
			return $this->db->query("select a.tanggal_pendaftaran, 
				a.nomor_perkara, 
				if(a.tanggal_surat is null,'...........',a.tanggal_surat) as tgl_gugatan, 
				a.nomor_surat, 
				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  ','...................',' 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  ','....................',' sebagai '),
					concat('bertempat tinggal di ',j.`alamatt`,' , sebagai ')) as statust,
				info.*, 
				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 ,g.*, i.*,
				k.tanggal_putusan, k.amar_putusan,l.*,
				(select value from sys_config where id ='64') as namaketua,
				(select value from sys_config where id ='63') as alamatPN,
				m.jurusita_id, m.jurusita_nama
			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 id,value as namapengadilan from sys_config where id=62) as info on  info.id = 62
			left join perkara_penetapan as e on  e.perkara_id=a.perkara_id
			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 a.perkara_id, a.penetapan_penunjukan_mediator,a.nomor_sk_penetapan_mediator,
				a.mediator_id, a.keputusan_mediasi, b.id, b.nama_gelar as nama_mediator, b.profesi
				from 
				perkara_mediasi as a,
				mediator as b where a.mediator_id = b.id ) as g on g.perkara_id=$idperkara
			left join (select perkara_id as idperkarabiaya,
				sum(if(jenis_transaksi =-1 && tahapan_id=10 ,jumlah,0))   as totbiaya,
				sum(if(kategori_id =4 && tahapan_id=10 ,jumlah,0))   as panggilan,
				sum(if(kategori_id =5 && tahapan_id=10 ,jumlah,0))   as sita,
				sum(if(kategori_id =7 && tahapan_id=10 ,jumlah,0))   as ps,
				sum(if(kategori_id =8 && tahapan_id=10 ,jumlah,0))   as materai,
				sum(if(kategori_id =11 && tahapan_id=10 ,jumlah,0))   as pnbp,
				sum(if(kategori_id =12 && tahapan_id=10 ,jumlah,0))   as atk,
				sum(if(kategori_id =13 && tahapan_id=10 ,jumlah,0))   as hhk,
				SUM(IF(kategori_id =14 && tahapan_id=10 ,jumlah,0))   AS redaksi
				from perkara_biaya
				where perkara_id =$idperkara) as i on i.idperkarabiaya=a.perkara_id
			left join perkara_putusan as k on a.perkara_id = k.perkara_id
			left join (select perkara_id as idperkara_phk3, pp1.pihak_id, pp1.urutan,  pp1.nama, pp1.alamat, p.jenis_pihak_id 
                        from (select perkara_id, pihak_id, urutan,  nama, alamat from perkara_pihak3 where perkara_id = $idperkara) pp1 
                        left join pihak p on pp1.pihak_id=p.id order by pp1.urutan asc) as l on l.idperkara_phk3=$idperkara
			left join perkara_jurusita as m on a.perkara_id=m.perkara_id
			where a.perkara_id=$idperkara");
		} catch (Exception $e) {
		}
	}

	function get_data_putusan_pdt_s($idperkara){
	try {
			if(!is_numeric($idperkara)){
				return 'dd';
			}
			return $this->db->query("SELECT a.tanggal_pendaftaran, 
					a.nomor_perkara, 
					a.tanggal_surat AS tgl_gugatan, 
					a.nomor_surat, 
					info.*, p1.*,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 ,i.*,
					k.tanggal_putusan, k.amar_putusan,
					(SELECT value FROM sys_config WHERE id ='64') AS namaketua
				FROM perkara AS a
				LEFT JOIN (SELECT 'P' AS pihak, p.nama AS namap, p.tempat_lahir AS tlp,p.tanggal_lahir AS tgllhrp,IF(p.jenis_kelamin='L','Laki-Laki','Perempuan') AS jkp,p.alamat AS alamatp,p.agama_nama AS agamap,p.status_kawin AS kawinp,p.pekerjaan AS pekerjaanp,p.pendidikan AS pendidikanp,p.warga_negara AS wnp,   
					p1.urutan, p1.perkara_id
					FROM perkara_pihak1 AS p1
					LEFT JOIN pihak AS p ON p1.pihak_id=p.id
						WHERE p1.perkara_id=$idperkara)  AS p1 ON p1.perkara_id=$idperkara
					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, 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 (SELECT id,value AS namapengadilan FROM sys_config WHERE id=62) AS info ON  info.id = 62
				LEFT JOIN perkara_penetapan AS e ON  e.perkara_id=a.perkara_id
				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 perkara_id AS idperkarabiaya,
					SUM(IF(jenis_transaksi =-1 && tahapan_id=10 ,jumlah,0))   AS totbiaya,
					SUM(IF(kategori_id =4 && tahapan_id=10 ,jumlah,0))   AS panggilan,
					SUM(IF(kategori_id =5 && tahapan_id=10 ,jumlah,0))   AS sita,
					SUM(IF(kategori_id =7 && tahapan_id=10 ,jumlah,0))   AS ps,
					SUM(IF(kategori_id =8 && tahapan_id=10 ,jumlah,0))   AS materai,
					SUM(IF(kategori_id =11 && tahapan_id=10 ,jumlah,0))   AS pnbp,
					SUM(IF(kategori_id =12 && tahapan_id=10 ,jumlah,0))   AS atk,
					SUM(IF(kategori_id =13 && tahapan_id=10 ,jumlah,0))   AS hhk
					FROM perkara_biaya
					WHERE perkara_id =$idperkara) AS i ON i.idperkarabiaya=a.perkara_id
				LEFT JOIN perkara_putusan AS k ON a.perkara_id = k.perkara_id
				WHERE a.perkara_id=$idperkara");
		} catch (Exception $e) {
		}
	}

	function getTurutTergugat($idperkara){
		try {
			return $this->db->query( "SELECT zz.*, 
									IF( zz.pengacara_idt IS NOT NULL,CONCAT('berkedudukan di  ', zz.alamatt,' dalam hal ini memberikan kuasa kepada ', zz.namapengacarat,' beralamat di ', zz.alamatpengacarat,' berdasarkan surat kuasa khusus tanggal  ',IF(zz.tanggal_surat IS NULL,'.....',zz.tanggal_surat),' sebagai '),
										CONCAT('bertempat tinggal di ', zz.`alamatt`,' , sebagai ')) AS statustt
									FROM
									(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,
									      a.tanggal_surat 
									    FROM
									      perkara AS a 
									      LEFT JOIN perkara_pihak4 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)
									WHERE a.perkara_id = '".$idperkara."') AS zz");
			} catch(Exception $e) {
				return false;
			}
	}

	function get_data_putusan_pdt_keberatan($idperkara){
	try {
			if(!is_numeric($idperkara)){
				return 'dd';
			}
			return $this->db->query("SELECT a.tanggal_pendaftaran, 
				a.nomor_perkara, 
				a.tanggal_surat AS tgl_gugatan, 
				a.nomor_surat, 
				info.*, p1.*,p2.*,
				e.penetapan_majelis_hakim, 
				e.penetapan_panitera_pengganti,
				f.nama_gelar AS nama_hakim,
				e.penetapan_hari_sidang AS penetapanharisidang , 
				e.sidang_pertama AS sidang_pertama ,i.*,
				k.putusan_keberatan, k.amar_putusan_keberatan,k.status_putusan_keberatan_text,g.nama_gelar AS namapp,
				(SELECT value FROM sys_config WHERE id ='64') AS namaketua
			FROM perkara AS a
			LEFT JOIN (SELECT pkd.urutan_keberatan AS urutanp, pkd.status_pihak_id AS statuspihakp,pkd.perkara_id,
				p.nama AS namap, p.tempat_lahir AS tlp,p.tanggal_lahir AS tgllhrp,IF(p.jenis_kelamin='L','Laki-Laki','Perempuan') AS jkp,p.alamat AS alamatp,p.agama_nama AS agamap,p.status_kawin AS kawinp,p.pekerjaan AS pekerjaanp,p.pendidikan AS pendidikanp,p.warga_negara AS wnp   
				FROM perkara_keberatan_detil AS pkd
				LEFT JOIN pihak AS p ON pkd.pihak_id=p.id
					WHERE pkd.perkara_id=$idperkara AND pkd.status_pihak_id=1 ORDER BY pkd.urutan_keberatan )  AS p1 ON p1.perkara_id=$idperkara
			LEFT JOIN (SELECT pkd.urutan_keberatan AS urutant, pkd.status_pihak_id AS statuspihakt,pkd.perkara_id,
				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   
				FROM perkara_keberatan_detil AS pkd
				LEFT JOIN pihak AS p ON pkd.pihak_id=p.id
					WHERE pkd.perkara_id=$idperkara AND pkd.status_pihak_id=3 ORDER BY pkd.urutan_keberatan) AS p2 ON p2.perkara_id=$idperkara
			LEFT JOIN (SELECT id,value AS namapengadilan FROM sys_config WHERE id=62) AS info ON  info.id = 62
			LEFT JOIN perkara_penetapan AS e ON  e.perkara_id=a.perkara_id
			LEFT JOIN (SELECT perkara_id AS idperkarabiaya,
				SUM(IF(jenis_transaksi =-1 && tahapan_id=10 ,jumlah,0))   AS totbiaya,
				SUM(IF(kategori_id =4 && tahapan_id=10 ,jumlah,0))   AS panggilan,
				SUM(IF(kategori_id =5 && tahapan_id=10 ,jumlah,0))   AS sita,
				SUM(IF(kategori_id =7 && tahapan_id=10 ,jumlah,0))   AS ps,
				SUM(IF(kategori_id =8 && tahapan_id=10 ,jumlah,0))   AS materai,
				SUM(IF(kategori_id =11 && tahapan_id=10 ,jumlah,0))   AS pnbp,
				SUM(IF(kategori_id =12 && tahapan_id=10 ,jumlah,0))   AS atk,
				SUM(IF(kategori_id =13 && tahapan_id=10 ,jumlah,0))   AS hhk
				FROM perkara_biaya
				WHERE perkara_id =$idperkara) AS i ON i.idperkarabiaya=a.perkara_id
			LEFT JOIN perkara_keberatan AS k ON a.perkara_id = k.perkara_id
			LEFT JOIN hakim_pn AS f ON FIND_IN_SET(f.id,k.majelis_hakim_id)
			LEFT JOIN panitera_pn AS g ON FIND_IN_SET(g.id,k.panitera_pengganti_id)
			WHERE a.perkara_id=$idperkara
			ORDER BY p1.urutanp, p2.urutant");
		} catch (Exception $e) {
		}
	}

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

	function getAlurPerkara($idperkara){
		try {
			return $this->db->query( "SELECT alur_perkara_id FROM perkara WHERE perkara_id=$idperkara ");
			} catch(Exception $e) {
				return false;
			}
	}


	function get_data_putusan_verzet($idperkara){
	try {
			if(!is_numeric($idperkara)){
				return 'dd';
			}
			return $this->db->query("SELECT a.tanggal_pendaftaran, 
					a.nomor_perkara, 
					a.tanggal_surat AS tgl_gugatan, 
					a.nomor_surat, 
					info.*, p1.*,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 ,i.*,
					k.tanggal_putusan, k.amar_putusan,
					(SELECT value FROM sys_config WHERE id ='64') AS namaketua
				FROM perkara AS a
				LEFT JOIN (SELECT 'P' AS pihak, p.nama AS namap, p.tempat_lahir AS tlp,p.tanggal_lahir AS tgllhrp,IF(p.jenis_kelamin='L','Laki-Laki','Perempuan') AS jkp,p.alamat AS alamatp,p.agama_nama AS agamap,p.status_kawin AS kawinp,p.pekerjaan AS pekerjaanp,p.pendidikan AS pendidikanp,p.warga_negara AS wnp,   
					p1.urutan, p1.perkara_id
					FROM perkara_pihak1 AS p1
					LEFT JOIN pihak AS p ON p1.pihak_id=p.id
						WHERE p1.perkara_id=$idperkara)  AS p1 ON p1.perkara_id=$idperkara
					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, 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 (SELECT id,value AS namapengadilan FROM sys_config WHERE id=62) AS info ON  info.id = 62
				LEFT JOIN perkara_penetapan AS e ON  e.perkara_id=a.perkara_id
				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 perkara_id AS idperkarabiaya,
					SUM(IF(jenis_transaksi =-1 && tahapan_id=10 ,jumlah,0))   AS totbiaya,
					SUM(IF(kategori_id =4 && tahapan_id=10 ,jumlah,0))   AS panggilan,
					SUM(IF(kategori_id =5 && tahapan_id=10 ,jumlah,0))   AS sita,
					SUM(IF(kategori_id =7 && tahapan_id=10 ,jumlah,0))   AS ps,
					SUM(IF(kategori_id =8 && tahapan_id=10 ,jumlah,0))   AS materai,
					SUM(IF(kategori_id =11 && tahapan_id=10 ,jumlah,0))   AS pnbp,
					SUM(IF(kategori_id =12 && tahapan_id=10 ,jumlah,0))   AS atk,
					SUM(IF(kategori_id =13 && tahapan_id=10 ,jumlah,0))   AS hhk
					FROM perkara_biaya
					WHERE perkara_id =$idperkara) AS i ON i.idperkarabiaya=a.perkara_id
				LEFT JOIN perkara_putusan AS k ON a.perkara_id = k.perkara_id
				WHERE a.perkara_id=$idperkara");
		} catch (Exception $e) {
		}
	}

}
?>