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

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

	public function get_tahun_banding(){
		try {
			return $this->db->query('SELECT LEFT(permohonan_banding,4) AS tahun FROM perkara_banding GROUP BY LEFT(permohonan_banding,4)');
		} catch (Exception $e) {
				
		}
	}

	public function get_tahun_kasasi(){
		try {
			return $this->db->query('SELECT LEFT(permohonan_kasasi,4) AS tahun FROM perkara_kasasi GROUP BY LEFT(permohonan_kasasi,4)');
		} catch (Exception $e) {
				
		}
	}

	public function get_tahun_anakanak(){
		try {
			return $this->db->query('SELECT LEFT(tgl_permohonan,4) AS tahun FROM diversi_register_blm_12tahun GROUP BY LEFT(tgl_permohonan,4)');
		} catch (Exception $e) {
				
		}
	}

	public function get_alur_perkara($idalurperkara){
		try {
			$this->db->select('nama');
			$this->db->where('id',$idalurperkara);
			return $this->db->get('alur_perkara');
		} catch (Exception $e) {
				
		}	
	}

	public function get_register_utama($alur_perkara,$periode){
		try {
		return $this->db->query("SELECT
			A.perkara_id,
			A.nomor_perkara,
			A.jenis_perkara_nama,
			J.terdakwa,
			K.ortu_terdakwa,
			L.pengacara_terdakwa,
			M.pk_bapas,
			MAX(CASE WHEN N.jenis_penahanan_id='1' THEN DATE_FORMAT(N.mulai,'%d-%m-%Y') ELSE NULL END) AS PNY1,
			MAX(CASE WHEN N.jenis_penahanan_id='1' THEN DATE_FORMAT(N.sampai,'%d-%m-%Y') ELSE NULL END) AS PNY2,
			MAX(CASE WHEN N.jenis_penahanan_id='1' THEN N.jenis_tahanan ELSE NULL END) AS jenis_PNY,
			MAX(CASE WHEN N.jenis_penahanan_id='2' THEN DATE_FORMAT(N.mulai,'%d-%m-%Y') ELSE NULL END) AS PNY_PU1,
			MAX(CASE WHEN N.jenis_penahanan_id='2' THEN DATE_FORMAT(N.sampai,'%d-%m-%Y') ELSE NULL END) AS PNY_PU2,
			MAX(CASE WHEN N.jenis_penahanan_id='2' THEN N.jenis_tahanan ELSE NULL END) AS jenis_PNY_PU,
			MAX(CASE WHEN N.jenis_penahanan_id='4' THEN DATE_FORMAT(N.mulai,'%d-%m-%Y') ELSE NULL END) AS PU1,
			MAX(CASE WHEN N.jenis_penahanan_id='4' THEN DATE_FORMAT(N.sampai,'%d-%m-%Y') ELSE NULL END) AS PU2,
			MAX(CASE WHEN N.jenis_penahanan_id='4' THEN N.jenis_tahanan ELSE NULL END) AS jenis_PU,
			MAX(CASE WHEN N.jenis_penahanan_id='5' THEN DATE_FORMAT(N.mulai,'%d-%m-%Y') ELSE NULL END) AS PU_KPN1,
			MAX(CASE WHEN N.jenis_penahanan_id='5' THEN DATE_FORMAT(N.sampai,'%d-%m-%Y') ELSE NULL END) AS PU_KPN2,
			MAX(CASE WHEN N.jenis_penahanan_id='5' THEN N.jenis_tahanan ELSE NULL END) AS jenis_PU_KPN,
			MAX(CASE WHEN N.jenis_penahanan_id='7' THEN DATE_FORMAT(N.mulai,'%d-%m-%Y') ELSE NULL END) AS HKPN1,
			MAX(CASE WHEN N.jenis_penahanan_id='7' THEN DATE_FORMAT(N.sampai,'%d-%m-%Y') ELSE NULL END) AS HKPN2,
			MAX(CASE WHEN N.jenis_penahanan_id='7' THEN N.jenis_tahanan ELSE NULL END) AS jenis_HKPN,
			MAX(CASE WHEN N.jenis_penahanan_id='8' THEN DATE_FORMAT(N.mulai,'%d-%m-%Y') ELSE NULL END) AS HKPN_KPN1,
			MAX(CASE WHEN N.jenis_penahanan_id='8' THEN DATE_FORMAT(N.sampai,'%d-%m-%Y') ELSE NULL END) AS HKPN_KPN2,
			MAX(CASE WHEN N.jenis_penahanan_id='8' THEN N.jenis_tahanan ELSE NULL END) AS jenis_HKPN_KPN,
			MAX(CASE WHEN N.jenis_penahanan_id='10' THEN DATE_FORMAT(N.mulai,'%d-%m-%Y') ELSE NULL END) AS HKPT1,
			MAX(CASE WHEN N.jenis_penahanan_id='10' THEN DATE_FORMAT(N.sampai,'%d-%m-%Y') ELSE NULL END) AS HKPT2,
			MAX(CASE WHEN N.jenis_penahanan_id='10' THEN N.jenis_tahanan ELSE NULL END) AS jenis_HKPT,
			MAX(CASE WHEN N.jenis_penahanan_id='11' THEN DATE_FORMAT(N.mulai,'%d-%m-%Y') ELSE NULL END) AS HKPT_KPT1,
			MAX(CASE WHEN N.jenis_penahanan_id='11' THEN DATE_FORMAT(N.sampai,'%d-%m-%Y') ELSE NULL END) AS HKPT_KPT2,
			MAX(CASE WHEN N.jenis_penahanan_id='11' THEN N.jenis_tahanan ELSE NULL END) AS jenis_HKPT_KPT,
			MAX(CASE WHEN N.jenis_penahanan_id='13' THEN DATE_FORMAT(N.mulai,'%d-%m-%Y') ELSE NULL END) AS HKMA1,
			MAX(CASE WHEN N.jenis_penahanan_id='13' THEN DATE_FORMAT(N.sampai,'%d-%m-%Y') ELSE NULL END) AS HKMA2,
			MAX(CASE WHEN N.jenis_penahanan_id='13' THEN N.jenis_tahanan ELSE NULL END) AS jenis_HKMA,
			MAX(CASE WHEN N.jenis_penahanan_id='14' THEN DATE_FORMAT(N.mulai,'%d-%m-%Y') ELSE NULL END) AS HKMA_KMA1,
			MAX(CASE WHEN N.jenis_penahanan_id='14' THEN DATE_FORMAT(N.sampai,'%d-%m-%Y') ELSE NULL END) AS HKMA_KMA2,
			MAX(CASE WHEN N.jenis_penahanan_id='14' THEN N.jenis_tahanan ELSE NULL END) AS jenis_HKMA_KMA,
			DATE_FORMAT(A.tanggal_surat,'%d-%m-%Y') AS tanggal_surat,
			A.dakwaan,
			A.pasal_dakwaan,
			DATE_FORMAT(B.penetapan_majelis_hakim,'%d-%m-%Y') AS penetapan_majelis_hakim,
			DATE_FORMAT(B.penetapan_panitera_pengganti,'%d-%m-%Y') AS penetapan_panitera_pengganti,
			B.majelis_hakim_text AS majelis_hakim,
			B.panitera_pengganti_text AS panitera_pengganti,
			DATE_FORMAT(C.tgl_penetapan_musyawarah,'%d-%m-%Y') AS tgl_penetapan_musyawarah,
			DATE_FORMAT(C.tgl_musyawarah,'%d-%m-%Y') AS tgl_musyawarah,
			DATE_FORMAT(C.tgl_kesepakatan_diversi,'%d-%m-%Y') AS tgl_kesepakatan_diversi,
			C.isi_singkat_diversi,
			B.penetapan_hari_sidang,
			B.sidang_pertama,
			DATE_FORMAT(D.tanggal_penuntutan,'%d-%m-%Y') AS tanggal_penuntutan,
			D.isi_penuntutan,
			DATE_FORMAT(E.tanggal_putusan,'%d-%m-%Y') AS tanggal_putusan,
			DATE_FORMAT(E.amar_putusan,'%d-%m-%Y') AS amar_putusan,
			O.tanggal_pemberitahuan_putusan1,
			P.tanggal_pemberitahuan_putusan2,
			O.tanggal_menerima_putusan1,
			P.tanggal_menerima_putusan2,
			O.tanggal_kirim_salinan_putusan1,
			P.tanggal_kirim_salinan_putusan2,
			DATE_FORMAT(E.tanggal_minutasi,'%d-%m-%Y') AS tanggal_minutasi,
			DATE_FORMAT(F.permohonan_banding,'%d-%m-%Y') AS permohonan_banding,
			F.pemohon_banding,
			DATE_FORMAT(F.pemberitahuan_permohonan_banding,'%d-%m-%Y') AS pemberitahuan_permohonan_banding,
			DATE_FORMAT(F.penerimaan_memori_banding,'%d-%m-%Y') AS penerimaan_memori_banding,
			DATE_FORMAT(F.penyerahan_memori_banding,'%d-%m-%Y') AS penyerahan_memori_banding,
			DATE_FORMAT(F.penerimaan_kontra_banding,'%d-%m-%Y') AS penerimaan_kontra_banding,
			DATE_FORMAT(F.penyerahan_kontra_banding,'%d-%m-%Y') AS penyerahan_kontra_banding,
			DATE_FORMAT(F.pelaksanaan_inzage_pembanding,'%d-%m-%Y') AS pelaksanaan_inzage_pembanding,
			DATE_FORMAT(F.pelaksanaan_inzage_terbanding,'%d-%m-%Y') AS pelaksanaan_inzage_terbanding,
			DATE_FORMAT(F.pengiriman_berkas_banding,'%d-%m-%Y') AS pengiriman_berkas_banding,
			F.nomor_surat_pengiriman_berkas_banding,
			DATE_FORMAT(F.penerimaan_kembali_berkas_banding,'%d-%m-%Y') AS penerimaan_kembali_berkas_banding,
			DATE_FORMAT(F.putusan_banding,'%d-%m-%Y') AS putusan_banding,
			F.nomor_perkara_banding,
			F.amar_putusan_banding,
			Q.pemberitahuan_putusan_banding_terdakwa,
			R.pemberitahuan_putusan_banding_PU,
			DATE_FORMAT(G.permohonan_kasasi,'%d-%m-%Y') AS permohonan_kasasi,
			G.pemohon_kasasi,
			DATE_FORMAT(G.pemberitahuan_kasasi,'%d-%m-%Y') AS pemberitahuan_kasasi,
			DATE_FORMAT(G.penerimaan_memori_kasasi,'%d-%m-%Y') AS penerimaan_memori_kasasi,
			DATE_FORMAT(G.penyerahan_memori_kasasi,'%d-%m-%Y') AS penyerahan_memori_kasasi,
			DATE_FORMAT(G.penerimaan_kontra_kasasi,'%d-%m-%Y') AS penerimaan_kontra_kasasi,
			DATE_FORMAT(G.penyerahan_kontra_kasasi,'%d-%m-%Y') AS penyerahan_kontra_kasasi,
			DATE_FORMAT(G.pengiriman_berkas_kasasi,'%d-%m-%Y') AS pengiriman_berkas_kasasi,
			G.nomor_surat_pengiriman_berkas_kasasi,
			DATE_FORMAT(G.penerimaan_berkas_kasasi,'%d-%m-%Y') AS penerimaan_berkas_kasasi,
			DATE_FORMAT(G.putusan_kasasi,'%d-%m-%Y') AS putusan_kasasi,
			G.nomor_perkara_kasasi,
			G.amar_putusan_kasasi,
			S.pemberitahuan_putusan_kasasi_terdakwa,
			T.pemberitahuan_putusan_kasasi_PU,
			DATE_FORMAT(H.permohonan_pk,'%d-%m-%Y') AS permohonan_pk,
			DATE_FORMAT(H.penetapan_majelis_hakim_pk,'%d-%m-%Y') AS penetapan_majelis_hakim_pk,
			DATE_FORMAT(H.penetapan_pemeriksaan_pk,'%d-%m-%Y') AS penetapan_pemeriksaan_pk,
			DATE_FORMAT(H.penyerahan_memori_pk,'%d-%m-%Y') AS penyerahan_memori_pk,
			DATE_FORMAT(H.pemeriksaan_pk,'%d-%m-%Y') AS pemeriksaan_pk,
			DATE_FORMAT(H.pendapat_hakim,'%d-%m-%Y') AS pendapat_hakim,
			DATE_FORMAT(H.pengiriman_berkas_pk,'%d-%m-%Y') AS pengiriman_berkas_pk,
			H.nomor_surat_pengiriman_berkas_pk,
			DATE_FORMAT(H.penerimaan_berkas_pk,'%d-%m-%Y') AS penerimaan_berkas_pk,
			DATE_FORMAT(H.putusan_pk,'%d-%m-%Y') AS putusan_pk,
			H.nomor_putusan_pk,
			H.amar_putusan_pk,
			U.pemberitahuan_putusan_pk_pemohon,
			V.pemberitahuan_putusan_pk_termohon,
			DATE_FORMAT(I.permohonan_grasi,'%d-%m-%Y') AS permohonan_grasi,
			I.pemohon_nama,
			DATE_FORMAT(I.pengiriman_berkas_grasi_ke_ma,'%d-%m-%Y') AS pengiriman_berkas_grasi_ke_ma,
			I.no_pengiriman_berkas_grasi_ke_ma,
			DATE_FORMAT(I.tanggal_penerimaan_kembali_berkas_grasi,'%d-%m-%Y') AS tanggal_penerimaan_kembali_berkas_grasi,
			DATE_FORMAT(I.tanggal_putusan_grasi,'%d-%m-%Y') AS tanggal_putusan_grasi,
			I.nomor_putusan_grasi,
			I.amar_putusan_grasi,
			I.pemberitahuan_putusan_grasi,
			W.tanggal_sidang,
			Z.agenda
			FROM perkara AS A
			LEFT JOIN perkara_penetapan AS B ON B.perkara_id=A.perkara_id
			LEFT JOIN perkara_diversi AS C ON C.perkara_id=A.perkara_id
			LEFT JOIN perkara_penuntutan AS D ON D.perkara_id=A.perkara_id
			LEFT JOIN perkara_putusan AS E ON E.perkara_id=A.perkara_id
			LEFT JOIN perkara_banding AS F ON F.perkara_id=A.perkara_id
			LEFT JOIN perkara_kasasi AS G ON G.perkara_id=A.perkara_id
			LEFT JOIN perkara_pk AS H ON H.perkara_id=A.perkara_id
			LEFT JOIN perkara_grasi AS I ON I.perkara_id=A.perkara_id
			LEFT JOIN (SELECT
				A.perkara_id,
				GROUP_CONCAT(CONCAT('- ',CONCAT(B.nama,', '
					,(CASE WHEN B.tempat_lahir IS NOT NULL THEN B.tempat_lahir ELSE '' END),', '
					,(CASE WHEN B.tanggal_lahir IS NOT NULL THEN TIMESTAMPDIFF(YEAR,B.tanggal_lahir,NOW()) ELSE NULL END),' Tahun / ',DATE_FORMAT(B.tanggal_lahir,'%d-%m-%Y'),', '
					,(CASE WHEN B.jenis_kelamin='P' THEN 'Perempuan' ELSE 'Laki-Laki' END),', '
					,(CASE WHEN B.warga_negara IS NOT NULL THEN B.warga_negara ELSE '' END),', '
					,(CASE WHEN B.alamat IS NOT NULL THEN B.alamat ELSE '' END),', '
					,(CASE WHEN B.agama_nama IS NOT NULL THEN B.agama_nama ELSE '' END),', '
					,(CASE WHEN B.pekerjaan IS NOT NULL THEN B.pekerjaan ELSE '' END),', '
					,(CASE WHEN B.pendidikan IS NOT NULL THEN B.pendidikan ELSE '' END)
					))SEPARATOR ',<br>') AS terdakwa
				FROM perkara_pihak2 AS A
				LEFT JOIN pihak AS B ON B.id=A.pihak_id
				GROUP BY A.perkara_id) AS J ON J.perkara_id=A.perkara_id
			LEFT JOIN (SELECT
				A.perkara_id,
				GROUP_CONCAT(CONCAT('- ',CONCAT(B.nama))SEPARATOR ',<br>') AS ortu_terdakwa
				FROM perkara_pihak_orang_tua AS A
				LEFT JOIN pihak AS B ON B.id=A.id_orang_tua
				WHERE A.jenis_pihak_anak='0'
				GROUP BY A.perkara_id) AS K ON K.perkara_id=A.perkara_id
			LEFT JOIN (SELECT
				A.perkara_id,
				GROUP_CONCAT(CONCAT('- ',CONCAT(A.nama))SEPARATOR ',<br>') AS pengacara_terdakwa
				FROM perkara_pengacara AS A
				GROUP BY A.perkara_id) AS L ON L.perkara_id=A.perkara_id
			LEFT JOIN (SELECT
				A.perkara_id,
				GROUP_CONCAT(CONCAT('- ',CONCAT(A.nama))SEPARATOR ',<br>') AS pk_bapas
				FROM perkara_pihak_pembimbing_masyarakat AS A
				GROUP BY A.perkara_id) AS M ON M.perkara_id=A.perkara_id
			LEFT JOIN (SELECT
				A.perkara_id,
				A.jenis_penahanan_id,
				A.status_penahanan_id,
				B.nama AS status_penahanan,
				A.jenis_tahanan_id,
				C.nama AS jenis_tahanan,
				A.tanggal_surat,
				A.mulai,
				A.sampai,
				A.lama_penahanan
				FROM penahanan_terdakwa AS A
				LEFT JOIN status_penahanan AS B ON B.id=A.status_penahanan_id
				LEFT JOIN jenis_tahanan AS C ON C.id=A.jenis_tahanan_id
				GROUP BY A.perkara_id) AS N ON N.perkara_id=A.perkara_id
			LEFT JOIN (SELECT
				A.perkara_id,
				GROUP_CONCAT(CONCAT('- ',CONCAT(DATE_FORMAT(A.tanggal_pemberitahuan_putusan,'%d-%m-%Y'))) SEPARATOR ',<br>')  AS tanggal_pemberitahuan_putusan1,
				GROUP_CONCAT(CONCAT('- ',CONCAT(DATE_FORMAT(A.tanggal_menerima_putusan,'%d-%m-%Y'))) SEPARATOR ',<br>')  AS tanggal_menerima_putusan1,
				GROUP_CONCAT(CONCAT('- ',CONCAT(DATE_FORMAT(A.tanggal_kirim_salinan_putusan,'%d-%m-%Y'))) SEPARATOR ',<br>')  AS tanggal_kirim_salinan_putusan1
				FROM perkara_putusan_pemberitahuan_putusan AS A
				WHERE A.putusan_akhir='2'
				AND A.pihak='1'
				GROUP BY A.perkara_id) AS O ON O.perkara_id=A.perkara_id
			LEFT JOIN (SELECT
				A.perkara_id,
				GROUP_CONCAT(CONCAT('- ',CONCAT(DATE_FORMAT(A.tanggal_pemberitahuan_putusan,'%d-%m-%Y'))) SEPARATOR ',<br>')  AS tanggal_pemberitahuan_putusan2,
				GROUP_CONCAT(CONCAT('- ',CONCAT(DATE_FORMAT(A.tanggal_menerima_putusan,'%d-%m-%Y'))) SEPARATOR ',<br>')  AS tanggal_menerima_putusan2,
				GROUP_CONCAT(CONCAT('- ',CONCAT(DATE_FORMAT(A.tanggal_kirim_salinan_putusan,'%d-%m-%Y'))) SEPARATOR ',<br>')  AS tanggal_kirim_salinan_putusan2
				FROM perkara_putusan_pemberitahuan_putusan AS A
				WHERE A.putusan_akhir='2'
				AND A.pihak='2'
				GROUP BY A.perkara_id) AS P ON P.perkara_id=A.perkara_id
			LEFT JOIN (SELECT 
				A.perkara_id,
				GROUP_CONCAT(CONCAT('- ',CONCAT(DATE_FORMAT(A.pemberitahuan_putusan_banding,'%d-%m-%Y'))) SEPARATOR ',<br>')  AS pemberitahuan_putusan_banding_terdakwa
				FROM perkara_banding_detil AS A
				WHERE A.pihak_asal='2'
				GROUP BY A.perkara_id) AS Q ON Q.perkara_id=A.perkara_id 
			LEFT JOIN (SELECT 
				A.perkara_id,
				GROUP_CONCAT(CONCAT('- ',CONCAT(DATE_FORMAT(A.pemberitahuan_putusan_banding,'%d-%m-%Y'))) SEPARATOR ',<br>')  AS pemberitahuan_putusan_banding_PU
				FROM perkara_banding_detil AS A
				WHERE A.pihak_asal='1'
				GROUP BY A.perkara_id) AS R ON R.perkara_id=A.perkara_id
			LEFT JOIN (SELECT 
				A.perkara_id,
				GROUP_CONCAT(CONCAT('- ',CONCAT(DATE_FORMAT(A.pemberitahuan_putusan_kasasi,'%d-%m-%Y'))) SEPARATOR ',<br>')  AS pemberitahuan_putusan_kasasi_terdakwa
				FROM perkara_kasasi_detil AS A
				WHERE A.pihak_asal='2'
				GROUP BY A.perkara_id) AS S ON S.perkara_id=A.perkara_id
			LEFT JOIN (SELECT 
				A.perkara_id,
				GROUP_CONCAT(CONCAT('- ',CONCAT(DATE_FORMAT(A.pemberitahuan_putusan_kasasi,'%d-%m-%Y'))) SEPARATOR ',<br>')  AS pemberitahuan_putusan_kasasi_PU
				FROM perkara_kasasi_detil AS A
				WHERE A.pihak_asal='1'
				GROUP BY A.perkara_id) AS T ON S.perkara_id=A.perkara_id
			LEFT JOIN (SELECT 
				A.perkara_id,
				GROUP_CONCAT(CONCAT('- ',CONCAT(DATE_FORMAT(A.pemberitahuan_putusan_pk,'%d-%m-%Y'))) SEPARATOR ',<br>')  AS pemberitahuan_putusan_pk_pemohon
				FROM perkara_pk_detil AS A
				WHERE A.pihak_asal='1'
				GROUP BY A.perkara_id) AS U ON U.perkara_id=A.perkara_id
			LEFT JOIN (SELECT 
				A.perkara_id,
				GROUP_CONCAT(CONCAT('- ',CONCAT(DATE_FORMAT(A.pemberitahuan_putusan_pk,'%d-%m-%Y'))) SEPARATOR ',<br>')  AS pemberitahuan_putusan_pk_termohon
				FROM perkara_pk_detil AS A
				WHERE A.pihak_asal='1'
				GROUP BY A.perkara_id) AS V ON V.perkara_id=A.perkara_id
			LEFT JOIN (SELECT
				perkara_id,
				GROUP_CONCAT(CONCAT('- ',CONCAT(DATE_FORMAT(tanggal_sidang,'%d-%m-%Y')))SEPARATOR ',<br>') AS tanggal_sidang
				FROM perkara_jadwal_sidang
				GROUP BY perkara_id
				ORDER BY urutan) AS W ON W.perkara_id=A.perkara_id
			LEFT JOIN (SELECT
				perkara_id,
				GROUP_CONCAT(CONCAT('- ',CONCAT(agenda))SEPARATOR ',<br>') AS agenda
				FROM perkara_jadwal_sidang
				GROUP BY perkara_id
				ORDER BY urutan) AS Z ON Z.perkara_id=A.perkara_id
			WHERE A.alur_perkara_id='".$alur_perkara."'
			AND LEFT(A.tanggal_pendaftaran,7)='".$periode."'
			GROUP BY A.perkara_id");
		} catch (Exception $e) {
			
		}
	}

	public function get_register_banding_anak($periode){
		try {
		return $this->db->query("SELECT
			A.nomor_perkara_pn AS kolom2,
			B.terdakwa AS kolom3,
			CONCAT(C.majelis_hakim_text,' <br/> ',C.panitera_pengganti_text) AS kolom4,
			DATE_FORMAT(A.putusan_pn,'%d-%m-%Y') AS kolom5,
			DATE_FORMAT(D.tanggal_minutasi,'%d-%m-%Y') AS kolom6,
			A.pemohon_banding AS kolom7,
			CONCAT('a.<br/>',DATE_FORMAT(A.permohonan_banding,'%d-%m-%Y'),'<br/>
					b.<br/>',DATE_FORMAT(A.pemberitahuan_permohonan_banding,'%d-%m-%Y'),'<br/>
					c.<br/>',DATE_FORMAT(A.penerimaan_memori_banding,'%d-%m-%Y'),'<br/>
					d.<br/>',DATE_FORMAT(A.penyerahan_memori_banding,'%d-%m-%Y')) AS kolom8,
			CONCAT('a.<br/>',DATE_FORMAT(A.penerimaan_kontra_banding,'%d-%m-%Y'),'<br/>b.<br/>',DATE_FORMAT(A.penyerahan_kontra_banding,'%d-%m-%Y')) AS kolom9,		
			CONCAT('a.<br/>',DATE_FORMAT(A.pemberitahuan_inzage_pembanding,'%d-%m-%Y'),'<br/>b.<br/>',DATE_FORMAT(A.pemberitahuan_inzage_terbanding,'%d-%m-%Y')) AS kolom10,	
			CONCAT(DATE_FORMAT(A.pengiriman_berkas_banding,'%d-%m-%Y'),' <br/> ',A.nomor_surat_pengiriman_berkas_banding) AS kolom11,
			CONCAT('a.<br/>',DATE_FORMAT(A.putusan_banding,'%d-%m-%Y'),'<br/>b.<br/>',A.amar_putusan_banding) AS kolom12,
			CONCAT('a.<br/>',DATE_FORMAT(F.pemberitahuan_putusan_pembanding,'%d-%m-%Y'),'<br/>b.<br/>',DATE_FORMAT(G.pemberitahuan_putusan_terbanding,'%d-%m-%Y')) AS kolom13
			FROM perkara_banding AS A
			LEFT JOIN (SELECT
				A.perkara_id,
				GROUP_CONCAT(CONCAT('- ',CONCAT(B.nama,', '
									,(CASE WHEN B.tempat_lahir IS NOT NULL THEN B.tempat_lahir ELSE '' END),', '
									,(CASE WHEN B.tanggal_lahir IS NOT NULL THEN TIMESTAMPDIFF(YEAR,B.tanggal_lahir,NOW()) ELSE NULL END),' Tahun / ',DATE_FORMAT(B.tanggal_lahir,'%d-%m-%Y'),', '
									,(CASE WHEN B.jenis_kelamin='P' THEN 'Perempuan' ELSE 'Laki-Laki' END),', '
									,(CASE WHEN B.warga_negara IS NOT NULL THEN B.warga_negara ELSE '' END),', '
									,(CASE WHEN B.alamat IS NOT NULL THEN B.alamat ELSE '' END),', '
									,(CASE WHEN B.agama_nama IS NOT NULL THEN B.agama_nama ELSE '' END),', '
									,(CASE WHEN B.pekerjaan IS NOT NULL THEN B.pekerjaan ELSE '' END),', '
									,(CASE WHEN B.pendidikan IS NOT NULL THEN B.pendidikan ELSE '' END)
									))SEPARATOR ',<br>') AS terdakwa
				FROM perkara_banding_detil AS A
				LEFT JOIN pihak AS B ON B.id=A.pihak_id
				WHERE A.pihak_asal='2'
				GROUP BY A.perkara_id) AS B ON B.perkara_id=A.perkara_id
			LEFT JOIN perkara_penetapan AS C ON C.perkara_id=A.perkara_id
			LEFT JOIN perkara_putusan AS D ON D.perkara_id=A.perkara_id
			LEFT JOIN perkara_banding_detil AS E ON E.perkara_id=A.perkara_id
			LEFT JOIN (SELECT
				perkara_id,
				GROUP_CONCAT(CONCAT('- ',CONCAT(DATE_FORMAT(A.pemberitahuan_putusan_banding,'%d-%m-%Y'))) SEPARATOR ',<br>')  AS pemberitahuan_putusan_pembanding
				FROM perkara_banding_detil AS A
				WHERE A.status_pihak_id IN (1,2)
				GROUP BY A.perkara_id) AS F ON F.perkara_id=A.perkara_id
			LEFT JOIN (SELECT
				perkara_id,
				GROUP_CONCAT(CONCAT('- ',CONCAT(DATE_FORMAT(A.pemberitahuan_putusan_banding,'%d-%m-%Y'))) SEPARATOR ',<br>')  AS pemberitahuan_putusan_terbanding
				FROM perkara_banding_detil AS A
				WHERE A.status_pihak_id IN (4,5)
				GROUP BY A.perkara_id) AS G ON G.perkara_id=A.perkara_id
			WHERE A.alur_perkara_id='118'
			AND LEFT(A.permohonan_banding,7)='".$periode."'
			GROUP BY A.perkara_id");
		} catch (Exception $e) {
			
		}
	}

	public function get_register_kasasi_anak($periode){
		try {
		return $this->db->query("SELECT
			A.nomor_perkara_pn AS kolom2,
			B.terdakwa AS kolom3,
			CONCAT('a.<br/>',DATE_FORMAT(C.tanggal_putusan,'%d-%m-%Y'),'<br/>
					b.<br/>',C.amar_putusan) AS kolom4,
			CONCAT('a.<br/>',DATE_FORMAT(D.putusan_banding,'%d-%m-%Y'),'<br/>
					b.<br/>',D.amar_putusan_banding) AS kolom5,
			CONCAT('a.<br/>',DATE_FORMAT(E.pemberitahuan_putusan_pembanding,'%d-%m-%Y'),'<br/>
					b.<br/>',DATE_FORMAT(F.pemberitahuan_putusan_terbanding,'%d-%m-%Y')) AS kolom6,
			A.pemohon_kasasi AS kolom7,
			CONCAT('a.<br/>',DATE_FORMAT(A.permohonan_kasasi,'%d-%m-%Y'),'<br/>
					b.<br/>',DATE_FORMAT(A.pemberitahuan_kasasi,'%d-%m-%Y'),'<br/>
					c.<br/>',DATE_FORMAT(A.penerimaan_memori_kasasi,'%d-%m-%Y'),'<br/>
					d.<br/>',DATE_FORMAT(A.penyerahan_memori_kasasi,'%d-%m-%Y'),'<br/>
					e.<br/>',DATE_FORMAT(A.penerimaan_kontra_kasasi,'%d-%m-%Y'),'<br/>
					f.<br/>',DATE_FORMAT(A.penyerahan_kontra_kasasi,'%d-%m-%Y')) AS kolom8,
			CONCAT('a.<br/>&nbsp;<br/>
					b.<br/>&nbsp;<br/>
					c.<br/>',DATE_FORMAT(A.tidak_memenuhi_syarat,'%d-%m-%Y')) AS kolom9,
			CONCAT(DATE_FORMAT(A.pengiriman_berkas_kasasi,'%d-%m-%Y'),'<br/>',A.nomor_surat_pengiriman_berkas_kasasi) AS kolom10,
			CONCAT(DATE_FORMAT(A.penerimaan_berkas_kasasi,'%d-%m-%Y')) AS kolom11,
			CONCAT('a.<br/>',DATE_FORMAT(A.putusan_kasasi,'%d-%m-%Y'),'<br/>',A.nomor_perkara_kasasi,'<br/>
					b.<br/>',A.amar_putusan_kasasi) AS kolom12,
			CONCAT('a.<br/>',DATE_FORMAT(G.pemberitahuan_putusan_pemohon,'%d-%m-%Y'),'<br/>
					b.<br/>',DATE_FORMAT(H.pemberitahuan_putusan_termohon,'%d-%m-%Y'),'<br/>
					c.<br/>&nbsp;') AS kolom13
			FROM perkara_kasasi AS A
			LEFT JOIN (SELECT
				A.perkara_id,
				GROUP_CONCAT(CONCAT('- ',CONCAT(B.nama,', '
												,(CASE WHEN B.tempat_lahir IS NOT NULL THEN B.tempat_lahir ELSE '' END),', '
												,(CASE WHEN B.tanggal_lahir IS NOT NULL THEN TIMESTAMPDIFF(YEAR,B.tanggal_lahir,NOW()) ELSE NULL END),' Tahun / ',DATE_FORMAT(B.tanggal_lahir,'%d-%m-%Y'),', '
												,(CASE WHEN B.jenis_kelamin='P' THEN 'Perempuan' ELSE 'Laki-Laki' END),', '
												,(CASE WHEN B.warga_negara IS NOT NULL THEN B.warga_negara ELSE '' END),', '
												,(CASE WHEN B.alamat IS NOT NULL THEN B.alamat ELSE '' END),', '
												,(CASE WHEN B.agama_nama IS NOT NULL THEN B.agama_nama ELSE '' END),', '
												,(CASE WHEN B.pekerjaan IS NOT NULL THEN B.pekerjaan ELSE '' END),', '
												,(CASE WHEN B.pendidikan IS NOT NULL THEN B.pendidikan ELSE '' END)
												))SEPARATOR ',<br>') AS terdakwa
				FROM perkara_banding_detil AS A
				LEFT JOIN pihak AS B ON B.id=A.pihak_id
				WHERE A.pihak_asal='2'
				GROUP BY A.perkara_id) AS B ON B.perkara_id=A.perkara_id
			LEFT JOIN perkara_putusan AS C ON C.perkara_id=A.perkara_id
			LEFT JOIN perkara_banding AS D ON D.perkara_id=A.perkara_id
			LEFT JOIN (SELECT
				perkara_id,
				GROUP_CONCAT(CONCAT('- ',CONCAT(DATE_FORMAT(A.pemberitahuan_putusan_banding,'%d-%m-%Y'))) SEPARATOR ',<br>')  AS pemberitahuan_putusan_pembanding
				FROM perkara_banding_detil AS A
				WHERE A.status_pihak_id IN (1,2)
				GROUP BY A.perkara_id) AS E ON E.perkara_id=A.perkara_id
			LEFT JOIN (SELECT
				perkara_id,
				GROUP_CONCAT(CONCAT('- ',CONCAT(DATE_FORMAT(A.pemberitahuan_putusan_banding,'%d-%m-%Y'))) SEPARATOR ',<br>')  AS pemberitahuan_putusan_terbanding
				FROM perkara_banding_detil AS A
				WHERE A.status_pihak_id IN (4,5)
				GROUP BY A.perkara_id) AS F ON F.perkara_id=A.perkara_id
			LEFT JOIN (SELECT
				perkara_id,
				GROUP_CONCAT(CONCAT('- ',CONCAT(DATE_FORMAT(A.pemberitahuan_putusan_banding,'%d-%m-%Y'))) SEPARATOR ',<br>')  AS pemberitahuan_putusan_pemohon
				FROM perkara_kasasi_detil AS A
				WHERE A.status_pihak_id IN (1,2)
				GROUP BY A.perkara_id) AS G ON G.perkara_id=A.perkara_id
			LEFT JOIN (SELECT
				perkara_id,
				GROUP_CONCAT(CONCAT('- ',CONCAT(DATE_FORMAT(A.pemberitahuan_putusan_banding,'%d-%m-%Y'))) SEPARATOR ',<br>')  AS pemberitahuan_putusan_termohon
				FROM perkara_kasasi_detil AS A
				WHERE A.status_pihak_id IN (4,5)
				GROUP BY A.perkara_id) AS H ON H.perkara_id=A.perkara_id
			WHERE A.alur_perkara_id='118'
			AND LEFT(A.permohonan_kasasi,7)='".$periode."'
			GROUP BY A.perkara_id");
		} catch (Exception $e) {
			
		}
	}

	public function get_register_pk_anak($periode){
		try {
		return $this->db->query("SELECT 
			B.nomor_perkara,
			B.jenis_perkara_nama,
			C.pemohon,
			D.orang_tua,
			H.amar_putusan,
			DATE_FORMAT(A.putusan_pn,'%d-%m-%Y') AS putusan_pn,
			A.nomor_perkara_pn,
			DATE_FORMAT(I.nomor_perkara_banding,'%d-%m-%Y') AS putusan_banding,
			I.nomor_perkara_banding,
			DATE_FORMAT(J.putusan_kasasi,'%d-%m-%Y') AS putusan_kasasi,
			J.nomor_perkara_kasasi,
			DATE_FORMAT(A.pemberitahuan_putusan_pn,'%d-%m-%Y') AS pemberitahuan_putusan_pn,
			DATE_FORMAT(A.pemberitahuan_putusan_banding,'%d-%m-%Y') AS pemberitahuan_putusan_banding,
			DATE_FORMAT(A.pemberitahuan_putusan_kasasi,'%d-%m-%Y') AS pemberitahuan_putusan_kasasi,
			DATE_FORMAT(A.permohonan_pk,'%d-%m-%Y') AS permohonan_pk,
			A.alasan_pk,
			DATE_FORMAT(A.penetapan_majelis_hakim_pk,'%d-%m-%Y') AS penetapan_majelis_hakim_pk,
			A.hakim_nama_pk,
			A.panitera_pengganti_pk,
			DATE_FORMAT(A.penetapan_pemeriksaan_pk,'%d-%m-%Y') AS penetapan_pemeriksaan_pk,
			DATE_FORMAT(A.pemeriksaan_pk,'%d-%m-%Y') AS pemeriksaan_pk, 
			DATE_FORMAT(A.penyerahan_memori_pk,'%d-%m-%Y') AS penyerahan_memori_pk,
			DATE_FORMAT(A.pemeriksaan_pk,'%d-%m-%Y') AS pemeriksaan_pk,
			DATE_FORMAT(A.pendapat_hakim,'%d-%m-%Y') AS pendapat_hakim,
			DATE_FORMAT(A.pengiriman_berkas_pk,'%d-%m-%Y') AS pengiriman_berkas_pk,
			A.nomor_surat_pengiriman_berkas_pk,
			DATE_FORMAT(A.penerimaan_berkas_pk,'%d-%m-%Y') AS penerimaan_berkas_pk,
			DATE_FORMAT(A.putusan_pk,'%d-%m-%Y') AS putusan_pk,
			A.nomor_putusan_pk,
			A.amar_putusan_pk,
			F.pemberitahuan_putusan_pemohon,
			G.pemberitahuan_putusan_termohon
			FROM perkara_pk AS A
			LEFT JOIN perkara AS B ON B.perkara_id=A.perkara_id
			LEFT JOIN (SELECT
				A.perkara_id,
				(CASE WHEN A.pihak_diwakili='T'
					THEN GROUP_CONCAT(CONCAT('- ',CONCAT(A.pihak_nama)))
					ELSE GROUP_CONCAT(CONCAT('- ',CONCAT(A.pemohon_nama)))
					END) AS pemohon
				FROM perkara_pk_detil AS A
				GROUP BY A.perkara_id) AS C ON C.perkara_id=A.perkara_id
			LEFT JOIN (SELECT
				A.perkara_id,
				(CASE WHEN B.nama IS NOT NULL THEN GROUP_CONCAT(CONCAT('- ',CONCAT(B.nama))) ELSE NULL END) AS orang_tua
				FROM perkara_pihak_orang_tua AS A
				LEFT JOIN pihak AS B ON B.id=A.id_orang_tua
				GROUP BY A.perkara_id) AS D ON D.perkara_id=A.perkara_id
			LEFT JOIN perkara_banding AS E ON E.perkara_id=A.perkara_id
			LEFT JOIN (SELECT
				perkara_id,
				GROUP_CONCAT(CONCAT('- ',CONCAT(DATE_FORMAT(A.pemberitahuan_putusan_pk,'%d-%m-%Y'))) SEPARATOR ',<br>')  AS pemberitahuan_putusan_pemohon
				FROM perkara_pk_detil AS A
				WHERE A.status_pihak_id='1'
				GROUP BY A.perkara_id) AS F ON F.perkara_id=A.perkara_id
			LEFT JOIN (SELECT
				perkara_id,
				GROUP_CONCAT(CONCAT('- ',CONCAT(DATE_FORMAT(A.pemberitahuan_putusan_pk,'%d-%m-%Y'))) SEPARATOR ',<br>')  AS pemberitahuan_putusan_termohon
				FROM perkara_pk_detil AS A
				WHERE A.status_pihak_id='4'
				GROUP BY A.perkara_id) AS G ON G.perkara_id=A.perkara_id
			LEFT JOIN perkara_putusan AS H ON H.perkara_id=A.perkara_id
			LEFT JOIN perkara_banding AS I ON I.perkara_id=A.perkara_id
			LEFT JOIN perkara_kasasi AS J ON J.perkara_id=A.perkara_id
			WHERE LEFT(A.permohonan_pk,7)='".$periode."'
			AND A.alur_perkara_id='118'
			GROUP BY A.perkara_id");
		} catch (Exception $e) {
			
		}
	}


	public function get_register_penahanan_anak($periode){
		try {
		return $this->db->query("SELECT
			A.nomor_perkara,
			C.terdakwa,
			A.pasal_dakwaan,
			MAX(CASE WHEN B.jenis_penahanan_id='1' THEN DATE_FORMAT(B.mulai,'%d-%m-%Y') ELSE NULL END) AS PNY1,
			MAX(CASE WHEN B.jenis_penahanan_id='1' THEN DATE_FORMAT(B.sampai,'%d-%m-%Y') ELSE NULL END) AS PNY2,
			MAX(CASE WHEN B.jenis_penahanan_id='1' THEN B.jenis_tahanan ELSE NULL END) AS jenis_PNY,
			MAX(CASE WHEN B.jenis_penahanan_id='2' THEN DATE_FORMAT(B.mulai,'%d-%m-%Y') ELSE NULL END) AS PNY_PU1,
			MAX(CASE WHEN B.jenis_penahanan_id='2' THEN DATE_FORMAT(B.sampai,'%d-%m-%Y') ELSE NULL END) AS PNY_PU2,
			MAX(CASE WHEN B.jenis_penahanan_id='2' THEN B.jenis_tahanan ELSE NULL END) AS jenis_PNY_PU,

			MAX(CASE WHEN B.jenis_penahanan_id='4' THEN DATE_FORMAT(B.mulai,'%d-%m-%Y') ELSE NULL END) AS PU1,
			MAX(CASE WHEN B.jenis_penahanan_id='4' THEN DATE_FORMAT(B.sampai,'%d-%m-%Y') ELSE NULL END) AS PU2,
			MAX(CASE WHEN B.jenis_penahanan_id='4' THEN B.jenis_tahanan ELSE NULL END) AS jenis_PU,
			MAX(CASE WHEN B.jenis_penahanan_id='5' THEN DATE_FORMAT(B.mulai,'%d-%m-%Y') ELSE NULL END) AS PU_KPN1,
			MAX(CASE WHEN B.jenis_penahanan_id='5' THEN DATE_FORMAT(B.sampai,'%d-%m-%Y') ELSE NULL END) AS PU_KPN2,
			MAX(CASE WHEN B.jenis_penahanan_id='5' THEN B.jenis_tahanan ELSE NULL END) AS jenis_PU_KPN,

			MAX(CASE WHEN B.jenis_penahanan_id='7' THEN DATE_FORMAT(B.mulai,'%d-%m-%Y') ELSE NULL END) AS HKPN1,
			MAX(CASE WHEN B.jenis_penahanan_id='7' THEN DATE_FORMAT(B.sampai,'%d-%m-%Y') ELSE NULL END) AS HKPN2,
			MAX(CASE WHEN B.jenis_penahanan_id='7' THEN B.jenis_tahanan ELSE NULL END) AS jenis_HKPN,
			MAX(CASE WHEN B.jenis_penahanan_id='8' THEN DATE_FORMAT(B.mulai,'%d-%m-%Y') ELSE NULL END) AS HKPN_KPN1,
			MAX(CASE WHEN B.jenis_penahanan_id='8' THEN DATE_FORMAT(B.sampai,'%d-%m-%Y') ELSE NULL END) AS HKPN_KPN2,
			MAX(CASE WHEN B.jenis_penahanan_id='8' THEN B.jenis_tahanan ELSE NULL END) AS jenis_HKPN_KPN,

			MAX(CASE WHEN B.jenis_penahanan_id='10' THEN DATE_FORMAT(B.mulai,'%d-%m-%Y') ELSE NULL END) AS HKPT1,
			MAX(CASE WHEN B.jenis_penahanan_id='10' THEN DATE_FORMAT(B.sampai,'%d-%m-%Y') ELSE NULL END) AS HKPT2,
			MAX(CASE WHEN B.jenis_penahanan_id='10' THEN B.jenis_tahanan ELSE NULL END) AS jenis_HKPT,
			MAX(CASE WHEN B.jenis_penahanan_id='11' THEN DATE_FORMAT(B.mulai,'%d-%m-%Y') ELSE NULL END) AS HKPT_KPT1,
			MAX(CASE WHEN B.jenis_penahanan_id='11' THEN DATE_FORMAT(B.sampai,'%d-%m-%Y') ELSE NULL END) AS HKPT_KPT2,
			MAX(CASE WHEN B.jenis_penahanan_id='11' THEN B.jenis_tahanan ELSE NULL END) AS jenis_HKPT_KPT,

			MAX(CASE WHEN B.jenis_penahanan_id='13' THEN DATE_FORMAT(B.mulai,'%d-%m-%Y') ELSE NULL END) AS HKMA1,
			MAX(CASE WHEN B.jenis_penahanan_id='13' THEN DATE_FORMAT(B.sampai,'%d-%m-%Y') ELSE NULL END) AS HKMA2,
			MAX(CASE WHEN B.jenis_penahanan_id='13' THEN B.jenis_tahanan ELSE NULL END) AS jenis_HKMA,
			MAX(CASE WHEN B.jenis_penahanan_id='14' THEN DATE_FORMAT(B.mulai,'%d-%m-%Y') ELSE NULL END) AS HKMA_KMA1,
			MAX(CASE WHEN B.jenis_penahanan_id='14' THEN DATE_FORMAT(B.sampai,'%d-%m-%Y') ELSE NULL END) AS HKMA_KMA2,
			MAX(CASE WHEN B.jenis_penahanan_id='14' THEN B.jenis_tahanan ELSE NULL END) AS jenis_HKMA_KMA
			FROM perkara AS A
			LEFT JOIN (SELECT
							A.perkara_id,
							A.jenis_penahanan_id,
							A.status_penahanan_id,
							B.nama AS status_penahanan,
							A.jenis_tahanan_id,
							C.nama AS jenis_tahanan,
							A.tanggal_surat,
							A.mulai,
							A.sampai,
							A.lama_penahanan
							FROM penahanan_terdakwa AS A
							LEFT JOIN status_penahanan AS B ON B.id=A.status_penahanan_id
							LEFT JOIN jenis_tahanan AS C ON C.id=A.jenis_tahanan_id) AS B ON B.perkara_id=A.perkara_id
			LEFT JOIN (SELECT
							A.perkara_id,
							GROUP_CONCAT(CONCAT('- ',CONCAT(B.nama,', '
								,(CASE WHEN B.tempat_lahir IS NOT NULL THEN B.tempat_lahir ELSE '' END),', '
								,(CASE WHEN B.tanggal_lahir IS NOT NULL THEN TIMESTAMPDIFF(YEAR,B.tanggal_lahir,NOW()) ELSE NULL END),' Tahun / ',DATE_FORMAT(B.tanggal_lahir,'%d-%m-%Y'),', '
								,(CASE WHEN B.jenis_kelamin='P' THEN 'Perempuan' ELSE 'Laki-Laki' END),', '
								,(CASE WHEN B.warga_negara IS NOT NULL THEN B.warga_negara ELSE '' END),', '
								,(CASE WHEN B.alamat IS NOT NULL THEN B.alamat ELSE '' END),', '
								,(CASE WHEN B.agama_nama IS NOT NULL THEN B.agama_nama ELSE '' END),', '
								,(CASE WHEN B.pekerjaan IS NOT NULL THEN B.pekerjaan ELSE '' END),', '
								,(CASE WHEN B.pendidikan IS NOT NULL THEN B.pendidikan ELSE '' END)
								))SEPARATOR ',<br>') AS terdakwa
							FROM perkara_pihak2 AS A
							LEFT JOIN pihak AS B ON B.id=A.pihak_id
							GROUP BY A.perkara_id) AS C ON C.perkara_id=A.perkara_id
			WHERE A.alur_perkara_id='118'
			AND LEFT(A.tanggal_pendaftaran,7)='".$periode."'		
			GROUP BY A.perkara_id");
		} catch (Exception $e) {
			
		}
	}

	public function get_register_diversi($periode){
		try {
		return $this->db->query("SELECT
			DATE_FORMAT(A.tgl_permohonan_diversi,'%d-%m-%Y') AS tgl_permohonan_diversi,
			A.no_diversi,
			A.no_reg_penyidik,
			A.no_reg_penuntut_umum,
			A.no_perkara,
			D.penyidik,
			E.penuntut,
			C.majelis_hakim_text,
			F.terdakwa,
			G.orang_tua_terdakwa,
			H.pengacara_terdakwa,
			I.pk_bapas,
			J.korban,
			K.orang_tua_korban,
			A.pasal_sangkaan_dakwaan,
			DATE_FORMAT(A.tgl_kesepakatan_diversi,'%d-%m-%Y') AS tgl_kesepakatan_diversi,
			A.isi_kesepakatan_diversi,
			DATE_FORMAT(A.tgl_pengiriman_penetapan_kpn,'%d-%m-%Y') AS tgl_pengiriman_penetapan_kpn,
			DATE_FORMAT(A.tgl_pelaksanaan_isi_diversi,'%d-%m-%Y') AS tgl_pelaksanaan_isi_diversi
			FROM diversi_register AS A
			LEFT JOIN perkara AS B ON B.perkara_id=A.perkara_id
			LEFT JOIN perkara_penetapan AS C ON C.perkara_id=A.perkara_id
			LEFT JOIN (SELECT
				A.diversi_register_id,
				GROUP_CONCAT(CONCAT('- ',CONCAT(B.nama))SEPARATOR ',<br>') AS penyidik
				FROM diversi_penyidik AS A
				LEFT JOIN pihak AS B ON B.id=A.pihak_id
				GROUP BY A.diversi_register_id) AS D ON D.diversi_register_id=A.id
			LEFT JOIN (SELECT
				A.diversi_register_id,
				GROUP_CONCAT(CONCAT(CONCAT('- ',B.nama))SEPARATOR ',<br>') AS penuntut
				FROM diversi_penuntut AS A
				LEFT JOIN pihak AS B ON B.id=A.pihak_id
				GROUP BY A.diversi_register_id) AS E ON E.diversi_register_id=A.id
			LEFT JOIN (SELECT
				A.diversi_register_id,
				GROUP_CONCAT(CONCAT('- ',CONCAT(B.nama,', '
					,(CASE WHEN B.tempat_lahir IS NOT NULL THEN B.tempat_lahir ELSE '' END),', '
					,(CASE WHEN B.tanggal_lahir IS NOT NULL THEN TIMESTAMPDIFF(YEAR,B.tanggal_lahir,NOW()) ELSE NULL END),' Tahun / ',DATE_FORMAT(B.tanggal_lahir,'%d-%m-%Y'),', '
					,(CASE WHEN B.jenis_kelamin='P' THEN 'Perempuan' ELSE 'Laki-Laki' END),', '
					,(CASE WHEN B.warga_negara IS NOT NULL THEN B.warga_negara ELSE '' END),', '
					,(CASE WHEN B.alamat IS NOT NULL THEN B.alamat ELSE '' END),', '
					,(CASE WHEN B.agama_nama IS NOT NULL THEN B.agama_nama ELSE '' END),', '
					,(CASE WHEN B.pekerjaan IS NOT NULL THEN B.pekerjaan ELSE '' END),', '
					,(CASE WHEN B.pendidikan IS NOT NULL THEN B.pendidikan ELSE '' END)
					))SEPARATOR ',<br>') AS terdakwa
				FROM diversi_anak_orang_tua AS A
				LEFT JOIN pihak AS B ON B.id=A.pihak_anak
				GROUP BY A.diversi_register_id) AS F ON F.diversi_register_id=A.id
			LEFT JOIN (SELECT
				A.diversi_register_id,
				GROUP_CONCAT(CONCAT('- ',CONCAT(B.nama))SEPARATOR ',<br>') AS orang_tua_terdakwa
				FROM diversi_anak_orang_tua AS A
				LEFT JOIN pihak AS B ON B.id=A.pihak_orang_tua
				GROUP BY A.diversi_register_id) AS G ON G.diversi_register_id=A.id
			LEFT JOIN (SELECT
				A.diversi_register_id,
				GROUP_CONCAT(CONCAT('- ',CONCAT(B.nama))SEPARATOR ',<br>') AS pengacara_terdakwa
				FROM diversi_penasihat_hukum AS A
				LEFT JOIN pihak AS B ON B.id=A.pengacara_id
				WHERE A.aktif='Y'
				GROUP BY A.diversi_register_id) AS H ON H.diversi_register_id=A.id
			LEFT JOIN (SELECT
				A.diversi_register_id,
				GROUP_CONCAT(CONCAT('- ',CONCAT(B.nama))SEPARATOR ',<br>') AS pk_bapas
				FROM diversi_pembimbing_kemasyarakatan AS A
				LEFT JOIN pihak AS B ON B.id=A.pihak_id
				GROUP BY A.diversi_register_id) AS I ON I.diversi_register_id=A.id
			LEFT JOIN (SELECT
				A.diversi_register_id,
				GROUP_CONCAT(CONCAT('- ',CONCAT(B.nama))SEPARATOR ',<br>') AS korban
				FROM diversi_korban_orang_tua AS A
				LEFT JOIN pihak AS B ON B.id=A.pihak_anak
				GROUP BY A.diversi_register_id) AS J ON J.diversi_register_id=A.id
			LEFT JOIN (SELECT
				A.diversi_register_id,
				GROUP_CONCAT(CONCAT('- ',CONCAT(B.nama))SEPARATOR ',<br>') AS orang_tua_korban
				FROM diversi_korban_orang_tua AS A
				LEFT JOIN pihak AS B ON B.id=A.pihak_orang_tua
				GROUP BY A.diversi_register_id) AS K ON K.diversi_register_id=A.id
			WHERE LEFT(A.tgl_permohonan_diversi,7)='".$periode."'
			GROUP BY A.id");
		} catch (Exception $e) {
			
		}
	}

	public function get_register_anak_korban_anak_saksi($periode){
		try {
		return $this->db->query("SELECT
			F.nomor_perkara,
			F.jenis_perkara_nama,
			DATE_FORMAT(F.tanggal_pendaftaran,'%d-%m-%Y') AS tanggal_pendaftaran,
			F.pasal_dakwaan,
			DATE_FORMAT(G.tanggal_putusan,'%d-%m-%Y') AS tanggal_putusan,
			G.amar_putusan,
			A.korban,
			B.saksi,
			C.orang_tua_saksi,
			D.orang_tua_korban,
			E.terdakwa
			FROM (SELECT
					A.perkara_id,
					GROUP_CONCAT(CONCAT('- ',CONCAT(B.nama,', '
					,(CASE WHEN B.tempat_lahir IS NOT NULL THEN B.tempat_lahir ELSE '' END),', '
					,(CASE WHEN B.tanggal_lahir IS NOT NULL THEN TIMESTAMPDIFF(YEAR,B.tanggal_lahir,NOW()) ELSE NULL END),' Tahun / ',DATE_FORMAT(B.tanggal_lahir,'%d-%m-%Y'),', '
					,(CASE WHEN B.jenis_kelamin='P' THEN 'Perempuan' ELSE 'Laki-Laki' END),', '
					,(CASE WHEN B.warga_negara IS NOT NULL THEN B.warga_negara ELSE '' END),', '
					,(CASE WHEN B.alamat IS NOT NULL THEN B.alamat ELSE '' END),', '
					,(CASE WHEN B.agama_nama IS NOT NULL THEN B.agama_nama ELSE '' END),', '
					,(CASE WHEN B.pekerjaan IS NOT NULL THEN B.pekerjaan ELSE '' END),', '
					,(CASE WHEN B.pendidikan IS NOT NULL THEN B.pendidikan ELSE '' END)
					))SEPARATOR ',<br>') AS korban
				FROM perkara_pihak_korban AS A
				LEFT JOIN pihak AS B ON B.id=A.pihak_id
				GROUP BY A.perkara_id) AS A
			LEFT JOIN  (SELECT
					A.perkara_id,
					GROUP_CONCAT(CONCAT('- ',CONCAT(B.nama,', '
					,(CASE WHEN B.tempat_lahir IS NOT NULL THEN B.tempat_lahir ELSE '' END),', '
					,(CASE WHEN B.tanggal_lahir IS NOT NULL THEN TIMESTAMPDIFF(YEAR,B.tanggal_lahir,NOW()) ELSE NULL END),' Tahun / ',DATE_FORMAT(B.tanggal_lahir,'%d-%m-%Y'),', '
					,(CASE WHEN B.jenis_kelamin='P' THEN 'Perempuan' ELSE 'Laki-Laki' END),', '
					,(CASE WHEN B.warga_negara IS NOT NULL THEN B.warga_negara ELSE '' END),', '
					,(CASE WHEN B.alamat IS NOT NULL THEN B.alamat ELSE '' END),', '
					,(CASE WHEN B.agama_nama IS NOT NULL THEN B.agama_nama ELSE '' END),', '
					,(CASE WHEN B.pekerjaan IS NOT NULL THEN B.pekerjaan ELSE '' END),', '
					,(CASE WHEN B.pendidikan IS NOT NULL THEN B.pendidikan ELSE '' END)
					))SEPARATOR ',<br>') AS saksi
				FROM perkara_pihak5 AS A
				LEFT JOIN pihak AS B ON B.id=A.pihak_id
				GROUP BY A.perkara_id) AS B ON B.perkara_id=A.perkara_id
			LEFT JOIN (SELECT
				A.perkara_id,
				GROUP_CONCAT(CONCAT('- ',CONCAT(B.nama))SEPARATOR ',<br>') AS orang_tua_saksi
				FROM perkara_pihak_orang_tua AS A
				LEFT JOIN pihak AS B ON B.id=A.id_orang_tua
				WHERE A.jenis_pihak_anak='2'
				GROUP BY perkara_id) AS C ON C.perkara_id=B.perkara_id
			LEFT JOIN (SELECT
				A.perkara_id,
				GROUP_CONCAT(CONCAT('- ',CONCAT(B.nama))SEPARATOR ',<br>') AS orang_tua_korban
				FROM perkara_pihak_orang_tua AS A
				LEFT JOIN pihak AS B ON B.id=A.id_orang_tua
				WHERE A.jenis_pihak_anak='1'
				GROUP BY perkara_id) AS D ON D.perkara_id=A.perkara_id
			LEFT JOIN (SELECT
					A.perkara_id,
					GROUP_CONCAT(CONCAT('- ',CONCAT(B.nama,', '
					,(CASE WHEN B.tempat_lahir IS NOT NULL THEN B.tempat_lahir ELSE '' END),', '
					,(CASE WHEN B.tanggal_lahir IS NOT NULL THEN TIMESTAMPDIFF(YEAR,B.tanggal_lahir,NOW()) ELSE NULL END),' Tahun / ',DATE_FORMAT(B.tanggal_lahir,'%d-%m-%Y'),', '
					,(CASE WHEN B.jenis_kelamin='P' THEN 'Perempuan' ELSE 'Laki-Laki' END),', '
					,(CASE WHEN B.warga_negara IS NOT NULL THEN B.warga_negara ELSE '' END),', '
					,(CASE WHEN B.alamat IS NOT NULL THEN B.alamat ELSE '' END),', '
					,(CASE WHEN B.agama_nama IS NOT NULL THEN B.agama_nama ELSE '' END),', '
					,(CASE WHEN B.pekerjaan IS NOT NULL THEN B.pekerjaan ELSE '' END),', '
					,(CASE WHEN B.pendidikan IS NOT NULL THEN B.pendidikan ELSE '' END)))SEPARATOR ',<br>') AS terdakwa
				FROM perkara_pihak2 AS A
				LEFT JOIN pihak AS B ON B.id=A.pihak_id
				LEFT JOIN perkara AS C ON C.perkara_id=A.perkara_id
				WHERE C.alur_perkara_id='118'
				GROUP BY A.perkara_id) AS E ON E.perkara_id=A.perkara_id
			LEFT JOIN perkara AS F ON F.perkara_id=A.perkara_id
			LEFT JOIN perkara_putusan AS G ON G.perkara_id=F.perkara_id
			WHERE LEFT(F.tanggal_pendaftaran,7)='".$periode."'
			GROUP BY  F.perkara_id");
		} catch (Exception $e) {
			
		}
	}


	public function get_register_anak_anak($periode){
		try {
		return $this->db->query("SELECT
			A.no_urut,
			DATE_FORMAT(A.tgl_permohonan,'%d-%m-%Y') AS tgl_permohonan,
			A.nomor_permohonan,
			B.anak,
			DATE_FORMAT(A.tgl_lap_kemasyarakatan,'%d-%m-%Y') AS tgl_lap_kemasyarakatan,
			A.nomor_lap_kemasyarakatan,
			DATE_FORMAT(A.tgl_kep_penyidik,'%d-%m-%Y') AS tgl_kep_penyidik,
			A.nomor_kep_penyidik,
			A.isi_kep_penyidik,
			DATE_FORMAT(A.tgl_tindak_pidana,'%d-%m-%Y') AS tgl_tindak_pidana,
			A.uraian_tindak_pidana,
			DATE_FORMAT(A.tgl_penetapan,'%d-%m-%Y') AS tgl_penetapan,
			A.nomor_penetapan
			FROM diversi_register_blm_12tahun AS A
			LEFT JOIN (SELECT
							A.register_id,
							GROUP_CONCAT(CONCAT('- ',CONCAT(B.nama,', '
								,(CASE WHEN B.tempat_lahir IS NOT NULL THEN B.tempat_lahir ELSE '' END),', '
								,(CASE WHEN B.tanggal_lahir IS NOT NULL THEN TIMESTAMPDIFF(YEAR,B.tanggal_lahir,NOW()) ELSE NULL END),' Tahun / ',DATE_FORMAT(B.tanggal_lahir,'%d-%m-%Y'),', '
								,(CASE WHEN B.jenis_kelamin='P' THEN 'Perempuan' ELSE 'Laki-Laki' END),', '
								,(CASE WHEN B.warga_negara IS NOT NULL THEN B.warga_negara ELSE '' END),', '
								,(CASE WHEN B.alamat IS NOT NULL THEN B.alamat ELSE '' END),', '
								,(CASE WHEN B.agama_nama IS NOT NULL THEN B.agama_nama ELSE '' END),', '
								,(CASE WHEN B.pekerjaan IS NOT NULL THEN B.pekerjaan ELSE '' END),', '
								,(CASE WHEN B.pendidikan IS NOT NULL THEN B.pendidikan ELSE '' END)
								))SEPARATOR ',<br>') AS anak
							FROM diversi_anak_blm_12tahun AS A
							LEFT JOIN pihak AS B ON B.id=A.pihak_anak
							GROUP BY A.register_id) AS B ON B.register_id=A.id
			WHERE LEFT(A.tgl_permohonan,7)='".$periode."'
			GROUP BY A.id");
		} catch (Exception $e) {
			
		}
	}


}
?>