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

class Dirput extends CI_Model{
	
function ambildata_dirput($perkara_id){
		try {
			return $this->db->query("SELECT
					p.nomor_perkara,
					p.tanggal_pendaftaran AS tanggal_register,
					pp.tanggal_putusan AS tanggal_diputus,
					p.para_pihak AS para_pihak,
					pp.amar_putusan AS catatan_amar,
					sp.nama AS amar,
					pb.amar_putusan_banding As catatan_amar_banding,
					IF(LENGTH(ppt.majelis_hakim_id) - LENGTH(REPLACE(ppt.majelis_hakim_id,',','')) > 1,'2','1') AS hakim,
					gg.hakim_ketua,
					gg.hakim_anggota, 
					ppt.panitera_pengganti_text AS panitera, 
					pp.tanggal_bht AS bht, 
					p.jenis_perkara_nama AS kata_kunci,
					SUBSTRING_INDEX(pp.amar_putusan_dok,'/',-1) AS file_putusan_nama, 
					pp.amar_putusan_dok AS file_putusan, 
					ee.guid, 
					ee.parentguid, 
					ff.status_penahanan 
					FROM
					  perkara AS p
					  LEFT JOIN perkara_putusan AS pp
						ON p.perkara_id = pp.perkara_id
					  LEFT JOIN status_putusan AS sp
						ON sp.id = pp.status_putusan_id 
					  LEFT JOIN perkara_banding AS pb
                        ON p.perkara_id = pb.perkara_id					  
					  LEFT JOIN perkara_penetapan AS ppt 
						ON p.perkara_id = ppt.perkara_id 
					  LEFT JOIN (SELECT p.perkara_id, p.alur_perkara_id,p.jenis_perkara_id, dsr.guid, drf.parentguid AS parentguid2,
								IF(drf.`parentguid` IS NULL,dd.parentguid,drf.parentguid) AS parentguid 
								FROM perkara AS p 
								LEFT JOIN dirput_sipp_ref AS dsr ON p.jenis_perkara_id = dsr.jenis_perkara_id AND p.alur_perkara_id = dsr.alur_perkara_id
								LEFT JOIN dirput_ref AS drf ON dsr.guid = drf.guid
								LEFT JOIN (SELECT alur_perkara_id,parentguid FROM dirput_ref GROUP BY alur_perkara_id ) AS dd ON p.alur_perkara_id=dd.alur_perkara_id) AS ee 
								ON p.perkara_id = ee.perkara_id
					LEFT JOIN (SELECT IF(ppt.status_putusan_id IN (12,13,14),'1','0') AS status_penahanan, 
					ppt.perkara_id, ppt.status_putusan_id FROM perkara_putusan_terdakwa AS ppt 
                                        LEFT JOIN status_putusan AS sp ON ppt.status_putusan_id = sp.id 
                                        GROUP BY ppt.perkara_id) AS ff
                                        ON p.perkara_id = ff.perkara_id                                                                                
					LEFT JOIN (SELECT perkara_id,SUBSTRING_INDEX(GROUP_CONCAT(CONCAT('Hakim Anggota : ',hakim_nama) SEPARATOR '<br>'),'<br>',-2) AS hakim_anggota,
					SUBSTRING_INDEX(GROUP_CONCAT(CONCAT('Hakim Ketua : ',hakim_nama) SEPARATOR '<br>'),'<br>',1) AS hakim_ketua
					FROM perkara_hakim_pn
					GROUP BY perkara_id) AS gg	
                                        ON p.perkara_id = gg.perkara_id where p.perkara_id=".$perkara_id);
		} catch (Exception $e) {
		   return FALSE;	
		}
	}
	
function doc_ref($jenis_pengadilan,$jenis_pengajuan,$alurid){
		try {
			return $this->db->query("select * from dirput_dokumen_ref where jenis_pengadilan=".$jenis_pengadilan." and pengajuan_id=".$jenis_pengajuan." and alur_perkara_id=".$alurid." and keterangan_file not like '%Surat Pengantar%' order by urutan asc");
		} catch (Exception $e) {
		   return FALSE;	
		}
	}
	
	
	
	
function doc_ref_pengantar($jenis_pengadilan,$jenis_pengajuan,$alurid){
		try {
			return $this->db->query("select dokumen_ref_id from dirput_dokumen_ref where jenis_pengadilan=".$jenis_pengadilan." and pengajuan_id=".$jenis_pengajuan." and alur_perkara_id=".$alurid." and keterangan_file like '%Surat Pengantar%' order by urutan asc");
		} catch (Exception $e) {
		   return FALSE;	
		}
	}
		
	
// function simpan_doc($data){
		 
		   // try {
				// $this->db->insert('dirput_dokumen',$data);		
				// return TRUE;
		// } catch (Exception $e) {
			// return FALSE;
		// }
		   
	// }		
	
	
function simpan_doc($data){
		 
		   try {
				$this->db->replace('dirput_dokumen',$data);		
				return TRUE;
		} catch (Exception $e) {
			return FALSE;
		}
		   
	}		
	
	
function ambil_ketfile($docref){
	
    	try {
			return $this->db->query("select keterangan_file,urutan,format from dirput_dokumen_ref where dokumen_ref_id=".$docref);
		} catch (Exception $e) {
		   return FALSE;	
		}
		   
	}


function ambil_pengantar($perkara_id,$pengajuan_id) {
	
	try {
			return $this->db->query("SELECT * FROM dirput_pengajuan a LEFT JOIN dirput_dokumen b ON a.perkara_id=b.perkara_id 
										WHERE a.perkara_id=".$perkara_id." AND a.pengajuan_id=".$pengajuan_id." AND b.filename LIKE '%surat_pengantar%'");
		} catch (Exception $e) {
		   return FALSE;	
		}
	
}	


function ambil_nomor_perkara($perkara_id,$tabel) {
	
	$kweri ="select nomor_perkara_pn as nomor_perkara from ".$tabel." where perkara_id=".$perkara_id;
	try {
			return $this->db->query($kweri);
		} catch (Exception $e) {
		   return FALSE;	
		}
	
	
}


function simpan_pengantar($data){		 
		   try {
				$this->db->replace('dirput_pengajuan',$data);		
				return TRUE;
		} catch (Exception $e) {
			return FALSE;
		}
		   
	}

	
function ambil_dokumen($perkara_id,$jenis_pengadilan,$jenis_pengajuan,$idkasasi) {
	
	try {	
	    return $this->db->query("SELECT *,a.dokumen_ref_id as referensi,c.status_pihak_text,c.urutan_kasasi,c.pihak_asal_text,c.pihak_diwakili,c.pemohon_kasasi,c.pihak_nama FROM  dirput_dokumen_ref a 
		                         LEFT JOIN dirput_dokumen b ON a.dokumen_ref_id=b.dokumen_ref_id AND b.perkara_id=".$perkara_id."  
								 LEFT JOIN perkara_kasasi_detil c ON  b.pihak_detil_id = c.id                               
								 WHERE a.jenis_pengadilan=".$jenis_pengadilan." AND a.pengajuan_id=".$jenis_pengajuan." AND b.pihak_detil_id=".$idkasasi." AND a.keterangan_file NOT LIKE '%surat pengantar%'  ORDER BY b.pihak_detil_id DESC,a.urutan ASC");
		} catch (Exception $e) {
		   return FALSE;	
		}
	
}	
	
	
function ambil_dokumenku($perkara_id,$jenis_pengadilan,$jenis_pengajuan,$idkasasi) {
	
	try {	
	    return $this->db->query("SELECT *,a.dokumen_ref_id as referensi FROM  dirput_dokumen_ref a 
		                         LEFT JOIN dirput_dokumen b ON a.dokumen_ref_id=b.dokumen_ref_id AND b.perkara_id=".$perkara_id."  
								 LEFT JOIN perkara_kasasi_detil c ON  b.pihak_detil_id = c.id
                                 LEFT JOIN pihak p ON c.pemohon_id=p.id	
								 WHERE a.jenis_pengadilan=".$jenis_pengadilan." AND a.pengajuan_id=".$jenis_pengajuan." AND b.pihak_detil_id=".$idkasasi." AND a.keterangan_file NOT LIKE '%surat pengantar%'  ORDER BY b.pihak_detil_id DESC,a.urutan ASC");
		} catch (Exception $e) {
		   return FALSE;	
		}
	
}		
	
	
	
	
	
function ambil_maxid() {
	
	try {	
	    return $this->db->query("select max(id) as max_id from dirput_dokumen");
		} catch (Exception $e) {
		   return FALSE;	
		}
	
	
}	


function ambil_maxid_pengantar() {
	
	try {	
	    return $this->db->query("select max(id) as max_id from dirput_pengajuan");
		} catch (Exception $e) {
		   return FALSE;	
		}
	
	
}	


function ambil_pihak_kasasi_detil($perkara_id) {
	
	try {	
	    return $this->db->query("select id from perkara_kasasi_detil where perkara_id=".$perkara_id." limit 0,1");
		} catch (Exception $e) {
		   return FALSE;	
		}
	
}



function pihak_kasasi($perkara_id) {
	
	try {	
	    return $this->db->query("SELECT * FROM perkara_kasasi_detil WHERE perkara_id=".$perkara_id);
		} catch (Exception $e) {
		   return FALSE;	
		}
	
}





# end functions of integration 	
	
	
}order

?>