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

class Register_mediasi_m extends CI_Model{
	
	function get_tahun(){
		try {
			return $this->db->query('SELECT LEFT(tanggal_pendaftaran,4) AS years FROM perkara GROUP BY LEFT(tanggal_pendaftaran,4);;');
		} catch (Exception $e) {
			
		}
	}

	function get_reg_mediasi($periode){
		try {
			return $this->db->query("SELECT 
									medi.mediasi_id,
									penetapan_penunjukan_mediator,
									data_mediasi1.*,
									data_mediasi2.*,
									
									DATE_FORMAT(med_b.tgl_permohonan_banding,'%d-%m-%Y') 		AS permohonan_banding,
									med_b.nomor_perkara_banding 					AS nomor_perkara_banding,
									DATE_FORMAT(med_b.tgl_kesepakatan_perdamaian_b,'%d-%m-%Y') 	AS tgl_kesepakatan_perdamaian_b,
									DATE_FORMAT(med_b.tgl_pengajuan_kesepakatan_b,'%d-%m-%Y') 	AS tgl_pengajuan_kesepakatan_b,
									med_b.mediator_b 						AS mediator_b,
									med_b.isi_kesekapatan_perdamaian_b 				AS isi_kesekapatan_perdamaian_b,	
									DATE_FORMAT(med_b.tgl_pemberitahuan_ke_ptma_b,'%d-%m-%Y') 	AS tgl_pemberitahuan_ke_ptma_b,
									DATE_FORMAT(med_b.akta_perdamaian_b,'%d-%m-%Y') 		AS akta_perdamaian_b,
									med_b.isi_akta_perdamaian 					AS isi_akta_perdamaian,
									
									DATE_FORMAT(med_k.tgl_permohonan_kasasi,'%d-%m-%Y') 		AS permohonan_kasasi,
									med_k.nomor_perkara_kasasi 					AS nomor_perkara_kasasi,
									DATE_FORMAT(med_k.tgl_kesepakatan_perdamaian_k,'%d-%m-%Y') 	AS tgl_kesepakatan_perdamaian_k,
									DATE_FORMAT(med_k.tgl_pengajuan_kesepakatan_k,'%d-%m-%Y') 	AS tgl_pengajuan_kesepakatan_k,
									med_k.mediator_k 						AS mediator_k,
									med_k.isi_kesekapatan_perdamaian_k 				AS isi_kesekapatan_perdamaian_k,
									DATE_FORMAT(med_k.tgl_pengajuan_kesepakatan_k,'%d-%m-%Y') 	AS tgl_pengajuan_kesepakatan_k,
									DATE_FORMAT(med_k.tgl_pemberitahuan_ke_ptma_k,'%d-%m-%Y') 	AS tgl_pemberitahuan_ke_ptma_k,
									DATE_FORMAT(med_k.akta_perdamaian_k,'%d-%m-%Y') 		AS akta_perdamaian_k,
									med_k.isi_akta_perdamaian_k 					AS isi_akta_perdamaian,
									
									DATE_FORMAT(med_pk.tgl_permohonan_pk,'%d-%m-%Y') 		AS permohonan_pk,
									med_pk.nomor_perkara_pk 					AS nomor_perkara_pk,
									DATE_FORMAT(med_pk.tgl_kesepakatan_perdamaian_pk,'%d-%m-%Y') 	AS tgl_kesepakatan_perdamaian_pk,
									DATE_FORMAT(med_pk.tgl_pengajuan_kesepakatan_pk,'%d-%m-%Y') 	AS tgl_pengajuan_kesepakatan_pk,
									med_pk.mediator_pk 						AS mediator_pk,
									med_pk.isi_kesekapatan_perdamaian_pk 				AS isi_kesekapatan_perdamaian_pk,
									DATE_FORMAT(med_pk.tgl_pengajuan_kesepakatan_pk,'%d-%m-%Y') 	AS tgl_pengajuan_kesepakatan_pk,
									DATE_FORMAT(med_pk.tgl_pemberitahuan_ke_ptma_pk,'%d-%m-%Y') 	AS tgl_pemberitahuan_ke_ptma_pk,
									DATE_FORMAT(med_pk.akta_perdamaian_pk,'%d-%m-%Y') 		AS akta_perdamaian_pk,
									med_pk.isi_akta_perdamaian_pk 					AS isi_akta_perdamaian,
									
									DATE_FORMAT(med_ek.tgl_permohonan_ek,'%d-%m-%Y')		AS tgl_permohonan_ek,
									DATE_FORMAT(med_ek.penetapan_teguran_ek,'%d-%m-%Y')		AS penetapan_teguran_ek,
									DATE_FORMAT(med_ek.teguran_ek,'%d-%m-%Y')			AS teguran_ek,
									DATE_FORMAT(med_ek.penetapan_sita_ek,'%d-%m-%Y')		AS penetapan_sita_ek,
									med_ek.nomor_penetapan_sita_ek					AS nomor_penetapan_sita_ek,
									DATE_FORMAT(med_ek.pelaksanaan_sita_ek,'%d-%m-%Y') 		AS pelaksanaan_sita_ek,
									DATE_FORMAT(med_ek.penetapan_ek,'%d-%m-%Y')			AS penetapan_ek,
									DATE_FORMAT(med_ek.pelaksanaan_ek,'%d-%m-%Y') 			AS pelaksanaan_ek

										
								FROM perkara_mediasi AS medi
								LEFT JOIN (
									SELECT 
										p.perkara_id 						AS perkara_id,
										p.nomor_perkara 					AS nomor_perkara,
										p.jenis_perkara_nama 					AS jenis_perkara,	
										pm.penetapan_penunjukan_mediator 			AS tgl_penunjukan_mediator,
										dphk1.data_pihak1 					AS data_pihak1,
										dpphk1.data_pengacara_pihak1 				AS pengacara_pihak1,
										dphk2.data_pihak2 					AS data_pihak2,
										dpphk2.data_pengacara_pihak2 				AS pengacara_pihak2,
										pnt.majelis_hakim_nama 					AS majelis_hakim,
										pnt.panitera_pengganti_text 				AS panitera_pengganti,
										DATE_FORMAT(pm.penetapan_penunjukan_mediator,'%d-%m-%Y') 	AS penunjukan_mediator,
										med.data_mediator 					AS mediator,
										pjmi.jadwal_mediasi 					AS jadwal_mediasi,
										DATE_FORMAT(pm.tgl_laporan_mediator,'%d-%m-%Y') 	AS tgl_laporan_mediator,
										IF(pm.hasil_mediasi='Y','Berhasil',
										IF(pm.hasil_mediasi='S','Berhasil Sebagian',
										IF(pm.hasil_mediasi='T','Tidak Berhasil',
										IF(pm.hasil_mediasi='D','Tidak Dapat Dilaksanakan',
										'')))) AS hasil_mediasi,
										IF((pm.hasil_mediasi='Y' OR pm.hasil_mediasi='S') AND akta_perdamaian IS NOT NULL,DATE_FORMAT(akta_perdamaian,'%d-%m-%Y'),
										(SELECT DATE_FORMAT(tanggal_putusan,'%d-%m-%Y') FROM perkara_putusan WHERE perkara_id=pm.perkara_id)) AS tanggal_akta_or_cabut,
										IF(pm.hasil_mediasi='Y' OR pm.hasil_mediasi='S',pm.isi_akta_perdamaian,'') 				AS isi_akta_perdamaian
										
									FROM perkara_mediasi AS pm
									LEFT JOIN (SELECT perkara_id,nomor_perkara,jenis_perkara_nama FROM perkara) AS p ON pm.perkara_id=p.perkara_id
									LEFT JOIN (
										SELECT 
											pp1.perkara_id,GROUP_CONCAT(CONCAT('- ',CONCAT(phk.nama,',',phk.jenis_kelamin,',',phk.tempat_lahir,',',DATE_FORMAT(phk.tanggal_lahir,'%d-%m-%Y'),',',phk.alamat))SEPARATOR ',<br>') AS data_pihak1
										FROM perkara_pihak1 AS pp1
										LEFT JOIN pihak AS phk ON pp1.pihak_id=phk.id GROUP BY pp1.perkara_id
									) AS dphk1 ON pm.perkara_id=dphk1.perkara_id
									LEFT JOIN (
										SELECT 
											ppp1.perkara_id,GROUP_CONCAT(CONCAT('- ',CONCAT(ppp1.nama,',',ppp1.alamat))SEPARATOR ',<br>') AS data_pengacara_pihak1
										FROM (SELECT * FROM perkara_pengacara WHERE pihak_ke=1 GROUP BY perkara_id,pengacara_id) AS ppp1 GROUP BY ppp1.perkara_id
									) AS dpphk1 ON pm.perkara_id=dpphk1.perkara_id

									LEFT JOIN (
										SELECT 
											pp2.perkara_id,GROUP_CONCAT(CONCAT('- ',CONCAT(phk.nama,',',phk.jenis_kelamin,',',phk.tempat_lahir,',',DATE_FORMAT(phk.tanggal_lahir,'%d-%m-%Y'),',',phk.alamat))SEPARATOR ',<br>') AS data_pihak2
										FROM perkara_pihak2 AS pp2
										LEFT JOIN pihak AS phk ON pp2.pihak_id=phk.id GROUP BY pp2.perkara_id
									) AS dphk2 ON pm.perkara_id=dphk2.perkara_id
									LEFT JOIN (
										SELECT 
											ppp2.perkara_id,GROUP_CONCAT(CONCAT('- ',CONCAT(ppp2.nama,',',ppp2.alamat))SEPARATOR ',<br>') AS data_pengacara_pihak2
										FROM (SELECT * FROM perkara_pengacara WHERE pihak_ke=2 GROUP BY perkara_id,pengacara_id) AS ppp2 GROUP BY ppp2.perkara_id
									) AS dpphk2 ON pm.perkara_id=dpphk2.perkara_id
									LEFT JOIN (SELECT perkara_id,majelis_hakim_nama,panitera_pengganti_text FROM perkara_penetapan) AS pnt ON pm.perkara_id=pnt.perkara_id
									LEFT JOIN (
										SELECT 
											mediator.perkara_id,GROUP_CONCAT(CONCAT('- ',CONCAT(mediator.nama_mediator,',',mediator.status_mediator))SEPARATOR ',<br>') AS data_mediator
										FROM (SELECT perkara_id,nama_mediator,status_mediator FROM perkara_mediator) AS mediator GROUP BY mediator.perkara_id
									) AS med ON pm.perkara_id=med.perkara_id
									LEFT JOIN (
										SELECT 
											pjm.mediasi_id,GROUP_CONCAT(CONCAT('- ',CONCAT(DATE_FORMAT(pjm.tanggal_mediasi,'%d-%m-%Y')))SEPARATOR ',<br>') AS jadwal_mediasi
										FROM (SELECT mediasi_id,tanggal_mediasi FROM perkara_jadwal_mediasi) AS pjm GROUP BY pjm.mediasi_id
									) AS pjmi ON pm.mediasi_id=pjmi.mediasi_id
									WHERE pm.jenis_mediasi=1
								) AS data_mediasi1 ON medi.perkara_id=data_mediasi1.perkara_id
								LEFT JOIN (
									SELECT 
										pm.perkara_id 				AS perkara_id,
										DATE_FORMAT(pm.penetapan_penunjukan_mediator,'%d-%m-%Y') 	AS tanggal_penunjukan_mediator2,
										med.data_mediator 			AS mediator2,
										DATE_FORMAT(pm.tgl_laporan_mediator,'%d-%m-%Y') 		AS tgl_laporan_mediator2,
										IF(pm.hasil_mediasi='Y','Berhasil',
										IF(pm.hasil_mediasi='S','Berhasil Sebagian',
										IF(pm.hasil_mediasi='T','Tidak Berhasil',
										IF(pm.hasil_mediasi='D','Tidak Dapat Dilaksanakan',
										'-')))) 				AS hasil_mediasi2,
										IF((pm.hasil_mediasi='Y' OR pm.hasil_mediasi='S') AND akta_perdamaian IS NOT NULL,akta_perdamaian,
										(SELECT tanggal_putusan FROM perkara_putusan WHERE perkara_id=pm.perkara_id)) AS tanggal_akta_or_cabut2,
										pm.isi_akta_perdamaian 			AS isi_akta_perdamaian2
									FROM perkara_mediasi AS pm 
									LEFT JOIN (
										SELECT 
											mediator.perkara_id,GROUP_CONCAT(CONCAT('- ',CONCAT(mediator.nama_mediator,',',mediator.status_mediator))SEPARATOR ',<br>') AS data_mediator
										FROM (SELECT perkara_id,nama_mediator,status_mediator FROM perkara_mediator) AS mediator GROUP BY mediator.perkara_id
									) AS med ON pm.perkara_id=med.perkara_id
									WHERE pm.jenis_mediasi=2
								) AS data_mediasi2 ON medi.perkara_id=data_mediasi2.perkara_id
								LEFT JOIN (
									SELECT 
										pb.`permohonan_banding` 		AS tgl_permohonan_banding,
										pb.`nomor_perkara_banding` 		AS nomor_perkara_banding,
										pmb.mediasi_id 				AS mediasi_id_b,
										pmb.perkara_id				AS perkara_id_b,
										pmb.`tgl_kesepakatan_perdamaian` 	AS tgl_kesepakatan_perdamaian_b,
										pmb.`tgl_pengajuan_kesepakatan`  	AS tgl_pengajuan_kesepakatan_b,
										medb.data_mediator 			AS mediator_b,
										pmb.`isi_kesepakatan_perdamaian` 	AS isi_kesekapatan_perdamaian_b,
										IF(pmb.akta_perdamaian IS NULL,pb.putusan_banding,pmb.`akta_perdamaian`) AS akta_perdamaian_b,
										pmb.tgl_pemberitahuan_ke_ptma 		AS tgl_pemberitahuan_ke_ptma_b,
										IF(pmb.`isi_akta_perdamaian` IS NULL,pb.amar_putusan_banding,pmb.isi_akta_perdamaian) 		AS isi_akta_perdamaian
									FROM perkara_mediasi AS pmb 
									LEFT JOIN perkara_banding AS pb ON pb.`perkara_id`=pmb.`perkara_id`
									LEFT JOIN (	
										SELECT 
											mediator.perkara_id,GROUP_CONCAT(CONCAT('- ',CONCAT('1. ',mediator.nama_mediator,'<br>2. ,',IF(mediator.`bersertifikat`='Y','Bersertifikat','Tidak Bersertifikat'),'<br>3. ,',mediator.`lembaga_sertifikasi`,'<br>4. ,',mediator.`no_sertifikasi`,'dan ',DATE_FORMAT(mediator.`tgl_sertifikasi`,'%d-%m-%Y')))SEPARATOR ',<br>') AS data_mediator
										FROM (SELECT pmm.perkara_id,pmm.nama_mediator,pmm.status_mediator,mm.bersertifikat,mm.lembaga_sertifikasi,mm.no_sertifikasi,DATE_FORMAT(mm.tgl_sertifikasi,'%d-%m-%Y') AS tgl_sertifikasi 
										      FROM perkara_mediator AS pmm
										      LEFT JOIN mediator AS mm ON pmm.`mediator_id`=mm.id) AS mediator GROUP BY mediator.perkara_id
									) AS medb ON pmb.perkara_id=medb.perkara_id	
									WHERE tahapan_id=20) AS med_b ON medi.perkara_id=med_b.perkara_id_b
									
								LEFT JOIN (
									SELECT 
										pb.`permohonan_kasasi` 			AS tgl_permohonan_kasasi,
										pb.`nomor_perkara_kasasi` 		AS nomor_perkara_kasasi,
										pmb.mediasi_id 				AS mediasi_id_k,
										pmb.perkara_id				AS perkara_id_k,
										pmb.`tgl_kesepakatan_perdamaian` 	AS tgl_kesepakatan_perdamaian_k,
										pmb.`tgl_pengajuan_kesepakatan`  	AS tgl_pengajuan_kesepakatan_k,
										medb.data_mediator 			AS mediator_k,
										pmb.`isi_kesepakatan_perdamaian` 	AS isi_kesekapatan_perdamaian_k,
										IF(pmb.akta_perdamaian IS NULL,pb.putusan_kasasi,pmb.`akta_perdamaian`) AS akta_perdamaian_k,
										pmb.tgl_pemberitahuan_ke_ptma 		AS tgl_pemberitahuan_ke_ptma_k,
										IF(pmb.`isi_akta_perdamaian` IS NULL,pb.amar_putusan_kasasi,pmb.isi_akta_perdamaian) 		AS isi_akta_perdamaian_k
									FROM perkara_mediasi AS pmb 
									LEFT JOIN perkara_kasasi AS pb ON pb.`perkara_id`=pmb.`perkara_id`
									LEFT JOIN (	
										SELECT 
											mediator.perkara_id,GROUP_CONCAT(CONCAT('- ',CONCAT('1. ',mediator.nama_mediator,'<br>2. ,',IF(mediator.`bersertifikat`='Y','Bersertifikat','Tidak Bersertifikat'),'<br>3. ,',mediator.`lembaga_sertifikasi`,'<br>4. ,',mediator.`no_sertifikasi`,'dan ',DATE_FORMAT(mediator.`tgl_sertifikasi`,'%d-%m-%Y')))SEPARATOR ',<br>') AS data_mediator
										FROM (SELECT pmm.perkara_id,pmm.nama_mediator,pmm.status_mediator,mm.bersertifikat,mm.lembaga_sertifikasi,mm.no_sertifikasi,DATE_FORMAT(mm.tgl_sertifikasi,'%d-%m-%Y') AS tgl_sertifikasi 
										      FROM perkara_mediator AS pmm
										      LEFT JOIN mediator AS mm ON pmm.`mediator_id`=mm.id) AS mediator GROUP BY mediator.perkara_id
									) AS medb ON pmb.perkara_id=medb.perkara_id	
									WHERE tahapan_id=30) AS med_k ON medi.perkara_id=med_k.perkara_id_k

								LEFT JOIN (
									SELECT 
										pb.`permohonan_pk` 			AS tgl_permohonan_pk,
										pb.`nomor_perkara_pk`	 		AS nomor_perkara_pk,
										pmb.mediasi_id 				AS mediasi_id_pk,
										pmb.perkara_id				AS perkara_id_pk,
										pmb.`tgl_kesepakatan_perdamaian` 	AS tgl_kesepakatan_perdamaian_pk,
										pmb.`tgl_pengajuan_kesepakatan`  	AS tgl_pengajuan_kesepakatan_pk,
										medb.data_mediator 			AS mediator_pk,
										pmb.`isi_kesepakatan_perdamaian` 	AS isi_kesekapatan_perdamaian_pk,
										IF(pmb.akta_perdamaian IS NULL,pb.putusan_pk,pmb.`akta_perdamaian`) AS akta_perdamaian_pk,
										pmb.tgl_pemberitahuan_ke_ptma 		AS tgl_pemberitahuan_ke_ptma_pk,
										IF(pmb.`isi_akta_perdamaian` IS NULL,pb.amar_putusan_pk,pmb.isi_akta_perdamaian) 		AS isi_akta_perdamaian_pk
									FROM perkara_mediasi AS pmb 
									LEFT JOIN perkara_pk AS pb ON pb.`perkara_id`=pmb.`perkara_id`
									LEFT JOIN (	
										SELECT 
											mediator.perkara_id,GROUP_CONCAT(CONCAT('- ',CONCAT('1. ',mediator.nama_mediator,'<br>2. ,',IF(mediator.`bersertifikat`='Y','Bersertifikat','Tidak Bersertifikat'),'<br>3. ,',mediator.`lembaga_sertifikasi`,'<br>4. ,',mediator.`no_sertifikasi`,'dan ',DATE_FORMAT(mediator.`tgl_sertifikasi`,'%d-%m-%Y')))SEPARATOR ',<br>') AS data_mediator
										FROM (SELECT pmm.perkara_id,pmm.nama_mediator,pmm.status_mediator,mm.bersertifikat,mm.lembaga_sertifikasi,mm.no_sertifikasi,DATE_FORMAT(mm.tgl_sertifikasi,'%d-%m-%Y') AS tgl_sertifikasi 
										      FROM perkara_mediator AS pmm
										      LEFT JOIN mediator AS mm ON pmm.`mediator_id`=mm.id) AS mediator GROUP BY mediator.perkara_id
									) AS medb ON pmb.perkara_id=medb.perkara_id	
									WHERE tahapan_id=40) AS med_pk ON medi.perkara_id=med_pk.perkara_id_pk

								LEFT JOIN (
									SELECT 
										pb.`permohonan_eksekusi`		AS tgl_permohonan_ek,
										pb.`penetapan_teguran_eksekusi`		AS penetapan_teguran_ek,
										pb.`pelaksanaan_teguran_eksekusi`	AS teguran_ek,
										pb.`penetapan_sita_eksekusi`		AS penetapan_sita_ek,
										pb.`nomor_penetapan_sita_eksekusi`	AS nomor_penetapan_sita_ek,
										pb.`pelaksanaan_sita_eksekusi`		AS pelaksanaan_sita_ek,
										pb.`penetapan_perintah_eksekusi_rill`	AS penetapan_ek,
										pb.`pelaksanaan_eksekusi_rill`		AS pelaksanaan_ek,
										pmb.mediasi_id 				AS mediasi_id_pk,
										pmb.perkara_id				AS perkara_id_pk
									FROM perkara_mediasi AS pmb 
									LEFT JOIN perkara_eksekusi AS pb ON pb.`perkara_id`=pmb.`perkara_id`
									LEFT JOIN (	
										SELECT 
											mediator.perkara_id,GROUP_CONCAT(CONCAT('- ',CONCAT('1. ',mediator.nama_mediator,'<br>2. ,',IF(mediator.`bersertifikat`='Y','Bersertifikat','Tidak Bersertifikat'),'<br>3. ,',mediator.`lembaga_sertifikasi`,'<br>4. ,',mediator.`no_sertifikasi`,'dan ',DATE_FORMAT(mediator.`tgl_sertifikasi`,'%d-%m-%Y')))SEPARATOR ',<br>') AS data_mediator
										FROM (SELECT pmm.perkara_id,pmm.nama_mediator,pmm.status_mediator,mm.bersertifikat,mm.lembaga_sertifikasi,mm.no_sertifikasi,DATE_FORMAT(mm.tgl_sertifikasi,'%d-%m-%Y') AS tgl_sertifikasi 
										      FROM perkara_mediator AS pmm
										      LEFT JOIN mediator AS mm ON pmm.`mediator_id`=mm.id) AS mediator GROUP BY mediator.perkara_id
									) AS medb ON pmb.perkara_id=medb.perkara_id	
									WHERE tahapan_id=50) AS med_ek ON medi.perkara_id=med_ek.perkara_id_pk
								WHERE LEFT(penetapan_penunjukan_mediator,7)='".$periode."';");
		} catch (Exception $e) {
			
		}
	}

	function get_reg_mediasi_luar($periode){
		try {
			return $this->db->query("SELECT 
									p.perkara_id 				AS perkara_id,
									p.nomor_perkara 			AS nomor_perkara,
									p.jenis_perkara_text 			AS jenis_perkara,
									DATE_FORMAT(p.tanggal_pendaftaran,'%d-%m-%Y')	AS tanggal_pendaftaran,
									dphk1.data_pihak1 			AS data_pihak1,
									dpphk1.data_pengacara_pihak1 		AS pengacara_pihak1,
									dphk2.data_pihak2 			AS data_pihak2,
									dpphk2.data_pengacara_pihak2 		AS pengacara_pihak2,
									med.data_mediator 			AS mediator,
									DATE_FORMAT(pm.tgl_kesepakatan_perdamaian,'%d-%m-%Y')		AS tgl_kesepakatan,
									pm.`isi_kesepakatan_perdamaian` 	AS isi_kesepakatan,
									DATE_FORMAT(pp.`penetapan_majelis_hakim`,'%d-%m-%Y') AS tgl_penetapan_majelis,
									pp.`majelis_hakim_nama` 		AS majelis_hakim,
									pp.`panitera_pengganti_text` 		AS panitera_pengganti,
									DATE_FORMAT(pp.`penetapan_hari_sidang`,'%d-%m-%Y') 		AS tgl_penetapan_sidang,
									pjs.sidang 				AS sidang,
									pm.`akta_perdamaian` 			AS tgl_akta_perdamaian,
									pm.`isi_akta_perdamaian` 		AS isi_akta_perdamaian
										
								FROM perkara AS p
								LEFT JOIN perkara_mediasi pm ON p.`perkara_id`=pm.`perkara_id`
								LEFT JOIN perkara_penetapan AS pp ON pp.`perkara_id`=p.`perkara_id`
								LEFT JOIN (SELECT perkara_id,GROUP_CONCAT(CONCAT('- ',CONCAT(DATE_FORMAT(tanggal_sidang,'%d-%m-%Y'),',<br> alasan ditunda : ',alasan_ditunda)) SEPARATOR '<br>') AS sidang FROM perkara_jadwal_sidang GROUP BY perkara_id) AS pjs ON p.perkara_id=pjs.perkara_id
								LEFT JOIN (	
									SELECT 
										mediator.perkara_id,GROUP_CONCAT(CONCAT('- ',CONCAT('1. ',mediator.nama_mediator,'<br>2. ,',IF(mediator.`bersertifikat`='Y','Bersertifikat','Tidak Bersertifikat'),'<br>3. ,',mediator.`lembaga_sertifikasi`,'<br>4. ,',mediator.`no_sertifikasi`,'dan ',DATE_FORMAT(mediator.`tgl_sertifikasi`,'%d-%m-%Y')))SEPARATOR ',<br>') AS data_mediator
									FROM (SELECT pmm.perkara_id,pmm.nama_mediator,pmm.status_mediator,mm.bersertifikat,mm.lembaga_sertifikasi,mm.no_sertifikasi,DATE_FORMAT(mm.tgl_sertifikasi,'%d-%m-%Y') AS tgl_sertifikasi 
									      FROM perkara_mediator AS pmm
									      LEFT JOIN mediator AS mm ON pmm.`mediator_id`=mm.id) AS mediator GROUP BY mediator.perkara_id
								) AS med ON p.perkara_id=med.perkara_id
								LEFT JOIN (
									SELECT 
										pp1.perkara_id,GROUP_CONCAT(CONCAT('- ',CONCAT(phk.nama,',',phk.jenis_kelamin,',',phk.tempat_lahir,',',DATE_FORMAT(phk.tanggal_lahir,'%d-%m-%Y'),',',phk.alamat))SEPARATOR ',<br>') AS data_pihak1
									FROM perkara_pihak1 AS pp1
									LEFT JOIN pihak AS phk ON pp1.pihak_id=phk.id GROUP BY pp1.perkara_id
								) AS dphk1 ON p.perkara_id=dphk1.perkara_id
								LEFT JOIN (
									SELECT 
										ppp1.perkara_id,GROUP_CONCAT(CONCAT('- ',CONCAT(ppp1.nama,',',ppp1.alamat))SEPARATOR ',<br>') AS data_pengacara_pihak1
									FROM (SELECT * FROM perkara_pengacara WHERE pihak_ke=1 GROUP BY perkara_id,pengacara_id) AS ppp1 GROUP BY ppp1.perkara_id
								) AS dpphk1 ON p.perkara_id=dpphk1.perkara_id

								LEFT JOIN (
									SELECT 
										pp2.perkara_id,GROUP_CONCAT(CONCAT('- ',CONCAT(phk.nama,',',phk.jenis_kelamin,',',phk.tempat_lahir,',',DATE_FORMAT(phk.tanggal_lahir,'%d-%m-%Y'),',',phk.alamat))SEPARATOR ',<br>') AS data_pihak2
									FROM perkara_pihak2 AS pp2
									LEFT JOIN pihak AS phk ON pp2.pihak_id=phk.id GROUP BY pp2.perkara_id
								) AS dphk2 ON p.perkara_id=dphk2.perkara_id
								LEFT JOIN (
									SELECT 
										ppp2.perkara_id,GROUP_CONCAT(CONCAT('- ',CONCAT(ppp2.nama,',',ppp2.alamat))SEPARATOR ',<br>') AS data_pengacara_pihak2
									FROM (SELECT * FROM perkara_pengacara WHERE pihak_ke=2 GROUP BY perkara_id,pengacara_id) AS ppp2 GROUP BY ppp2.perkara_id
								) AS dpphk2 ON p.perkara_id=dpphk2.perkara_id

								WHERE jenis_perkara_id=624 AND LEFT(p.`tanggal_pendaftaran`,7)='".$periode."';");
		} catch (Exception $e) {
			
		}
	}

	function get_reg_mediator(){
		try {
			return $this->db->query("SELECT 
										id 				AS id,
										foto			AS foto,
										nama_gelar		AS nama,
										tempat_lahir 	AS tempat_lahir,
										DATE_FORMAT(tgl_lahir,'%d-%m-%Y') 	AS tanggal_lahir,
										pendidikan 		AS pendidikan,
										alamat 			AS alamat,
										DATE_FORMAT(tgl_sertifikasi,'%d-%m-%Y') AS tgl_sertifikat,
										no_sertifikasi 	AS nomor_sertifikat,
										lembaga_sertifikasi	as lembaga_sertifikasi,
										nomor_sk_ketua 	AS nomor_sk_ketua,
										DATE_FORMAT(tgl_sk_ketua,'%d-%m-%Y') 	AS tgl_sk_ketua,
										profesi			AS prefesi,
										keahlian 		AS keahlian,
										keterangan 		AS keterangan		
									FROM mediator where aktif='Y';");
		} catch (Exception $e) {
			
		}
	}


}
?>