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

set_time_limit(0);
ini_set('max_execution_time', 3600);
ini_set('memory_limit', '1024M');
ini_set('max_input_time', 3600);

class Dirput_model extends CI_Model {

    public function list_get(){
        $datauser = $this->db->query('select id, name, value from sys_config where id in(88,89,90)')->result_array();
        foreach($datauser as $userpwd){
            $data['userpwd'][$userpwd['name']] = $userpwd['value'];
        }
      return $data['userpwd'];
    }

    public function create_dirput($perkara_id){
        return $this->db->query("SELECT
				p.nomor_perkara, p.alur_perkara_id as alur_perkara_id,
				p.tanggal_pendaftaran AS tanggal_register,
				pp.tanggal_putusan AS tanggal_diputus,
				p.para_pihak AS para_pihak,
				pp.amar_putusan AS catatan_amar,
				IF(sp.nama IS NULL, pt.nama,sp.nama) AS amar,
				pb.amar_putusan_banding AS catatan_amar_banding,
				pk.amar_putusan_kasasi AS catatan_amar_kasasi,
				IF(LENGTH(ppt.majelis_hakim_id) - LENGTH(REPLACE(ppt.majelis_hakim_id,',','')) > 1,'1','2') AS hakim,
				SUBSTRING_INDEX(REPLACE(ppt.majelis_hakim_text,'</br>','<br />'), '<br />', 1) AS hakim_ketua,
				SUBSTRING_INDEX(REPLACE(ppt.majelis_hakim_text,'</br>','<br />'), '<br />', -2) AS hakim_anggota,
				ppt.panitera_pengganti_text AS panitera,
				CASE WHEN pp.tanggal_bht IS NULL THEN 0 ELSE 1 END AS bht,
				p.jenis_perkara_nama AS kata_kunci,
				ddn.filename AS file_putusan_nama,
				ddn.path_filename AS file_putusan,
				dsr.guid,
				dsr.parentguid,
				ff.status_penahanan,
				ddn.dokumen_ref_id,
                                ddn.published,
				drf.urutan
				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 pb
				ON p.perkara_id = pb.perkara_id
				LEFT JOIN perkara_kasasi pk
				ON p.perkara_id= pk.perkara_id
				LEFT JOIN perkara_penetapan AS ppt
				ON p.perkara_id = ppt.perkara_id
				LEFT JOIN dirput_sipp_ref AS dsr
				ON p.alur_perkara_id = dsr.alur_perkara_id
				AND p.jenis_perkara_id = dsr.jenis_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 z.perkara_id, z.status_putusan_id,w.nama FROM perkara_putusan_terdakwa AS z LEFT JOIN status_putusan w ON z.status_putusan_id = w.id GROUP BY z.perkara_id) AS pt ON p.perkara_id=pt.perkara_id
				LEFT JOIN dirput_dokumen AS ddn
				ON p.perkara_id = ddn.perkara_id
				LEFT JOIN dirput_dokumen_ref AS drf
				ON ddn.dokumen_ref_id = drf.dokumen_ref_id
				WHERE
				p.perkara_id = '$perkara_id'
                                ORDER BY drf.urutan DESC;")->result_array();
    }
    public function simpan_dirput_perkara($perkara_id,$putusan_id,$nomor_perkara,$pengajuan_id,$nomor_perkara_key,$putusan_link){
        $data = array(
            'id' => null,
            'perkara_id' => $perkara_id,
            'putusan_id' => $putusan_id,
            'nomor_perkara' => $nomor_perkara,
            'pengajuan_id' => 1,
            'nomor_perkara_key' => $nomor_perkara_key
        );
        $this->db->query("update dirput_dokumen set link_dirput='$putusan_link' where perkara_id=$perkara_id and pengajuan_id=1;");
          return $this->db->replace('dirput_perkara', $data);
    }


    public function file_pendukung($perkara_id, $pengajuan_id){
        $query = "select dp.perkara_id, dp.putusan_id, dd.id as file_id, dd.filename as file_nama, dd.path_filename as file,
                  dr.keterangan_file, dr.pengajuan_id from dirput_perkara as dp
                  left join dirput_dokumen as dd on dp.perkara_id = dd.perkara_id
                  left join dirput_dokumen_ref as dr on dd.dokumen_ref_id = dr.dokumen_ref_id
                  where dp.perkara_id = $perkara_id and dr.pengajuan_id = $pengajuan_id;";

        return $this->db->query($query)->result_array();
    }

	    public function update_file_pendukung($file_id, $keterangan, $flag){
        $this->db->where('id',$file_id)
                ->set('keterangan',$keterangan)
				->set('flag_sync',$flag);
        return $this->db->update('dirput_dokumen');
    }

    public function masuk_antrian($perkara_id){
        $antrianmasuk = array(
            'id'            => null,
            'perkara_id'    => $perkara_id,
            'tgl_masuk'     => date('Y-m-d H:i:s'),
            'status'        => 0,
            'ketarangan'    => 'Antrian awal masuk dari form putusan akhir'
        );
        return $this->db->insert('dirput_antrian',$antrianmasuk);
    }

    public function range_antrian(){
        $cek_antrian = $this->db->query('select * from dirput_antrian where status = 0 order by last_tried desc limit 50;');
        $antrian['ngantri'] = $cek_antrian->num_rows();
        $antrian['antrian'] = $cek_antrian->result_array();
        return $antrian;
    }

    public function update_antrian($id, $perkara_id, $status, $keterangan){
        $updateantrian = array(
            'id'            => $id,
            'perkara_id'    => $perkara_id,
            'status'        => $status,
            'last_tried'    => date('Y-m-d H:i:s'),
            'keterangan'    => $keterangan
        );
        $this->db->where('id',$id);
        $this->db->update('dirput_antrian',$updateantrian);
    }

	public function antrian_convert($tanggal_daftar){
        $cek_antrian = $this->db->query("SELECT pp.perkara_id, ddr.alur_perkara_id, ddr.dokumen_ref_id,
					p.tanggal_pendaftaran, pp.amar_putusan_dok FROM perkara_putusan AS pp
					LEFT JOIN perkara AS p
					ON p.perkara_id = pp.perkara_id
					INNER JOIN dirput_dokumen_ref AS ddr
					ON p.alur_perkara_id = ddr.alur_perkara_id
					WHERE RIGHT(pp.amar_putusan_dok, 3) <> 'pdf'
					AND pp.amar_putusan_dok IS NOT NULL
					AND pp.amar_putusan_dok <> ''
					AND (YEAR(p.tanggal_pendaftaran) >= $tanggal_daftar)
					AND
					(CASE
					WHEN (SELECT VALUE FROM sys_config WHERE id = 83) = 1 THEN ddr.urutan = 99
					WHEN (SELECT VALUE FROM sys_config WHERE id = 83) = 2 THEN ddr.urutan = 99
					WHEN (SELECT VALUE FROM sys_config WHERE id = 83) = 3 THEN ddr.urutan = 99
					WHEN (SELECT VALUE FROM sys_config WHERE id = 83) = 4 THEN ddr.urutan = 100
					END)
					AND ddr.jenis_pengadilan = (SELECT VALUE FROM sys_config WHERE id = 83)
					LIMIT 50");
        $antrian['ngantri'] = $cek_antrian->num_rows();
        $antrian['antrian'] = $cek_antrian->result_array();
        return $antrian;
    }

	public function convert_insert($perkara_id,$filetarget,$dokumen_ref_id,$status_publish){
		$arr=explode('/',$filetarget);
		//print_r($arr);
		if(count($arr)>0)
			$filename = $arr[count($arr)-1];
		else
			$filename = $filetarget;
		$dokumen = array(
					'id' 				=> null,
					'perkara_id' 		=> $perkara_id,
					'dokumen_ref_id' 	=> $dokumen_ref_id,
					'filename' 			=> $filename,
					'path_filename'		=> $filetarget,
					'created_by'		=> 'SYSTEM',
					'created_date'		=> date('Y-m-d'),
					'updated_by'		=> 'SYSTEM',
					'updated_date'		=> date('Y-m-d'),
					'keterangan'        => $keterangan,
					'pengajuan_id'		=> 1,
					'published'			=> $status_publish
					);
		return $this->db->replace('dirput_dokumen',$dokumen);
	}

	public function convert_insert_Annmous($perkara_id,$filetarget,$dokumen_ref_id){
		$arr=explode('/',$filetarget);
		if(count($arr)>0)
			$filename = $arr[count($arr)-1];
		else
			$filename = $filetarget;
		$dokumen = array(
					'id' 				=> null,
					'perkara_id' 		=> $perkara_id,
					'dokumen_ref_id' 	=> $dokumen_ref_id,
					'filename' 			=> $filename,
					'path_filename'		=> $filetarget,
					'created_by'		=> 'SYSTEM',
					'created_date'		=> date('Y-m-d'),
					'updated_by'		=> 'SYSTEM',
					'updated_date'		=> date('Y-m-d'),
					'keterangan'        => $keterangan,
					'pengajuan_id'		=> 1,
					'published'			=> '1'
					);
		return $this->db->replace('dirput_dokumen',$dokumen);
	}

	public function convert_update_Annmous($perkara_id,$filetarget,$dokumen_ref_id){
		$arr=explode('/',$filetarget);
		if(count($arr)>0)
			$filename = $arr[count($arr)-1];
		else
			$filename = $filetarget;
		$dokumen = array(
					'filename' 			=> $filename,
					'path_filename'		=> $filetarget
					);
				$this->db->where('perkara_id',$perkara_id);
				$this->db->where('dokumen_ref_id',$dokumen_ref_id);
		return $this->db->update('dirput_dokumen',$dokumen);
	}

	public function insert_antrian_upload($perkara_id){
		$antrian = array(
					'id'				=> null,
					'perkara_id'		=> $perkara_id,
					'status'			=> 0,
					'keterangan'		=> null,
					'tgl_masuk'			=> date('Y-m-d H:i:s')
					);
		return $this->db->insert('dirput_antrian',$antrian);
	}

    public function ambilputusanid($perkara_id){
            $this->db->where('perkara_id',$perkara_id);
            return $this->db->get('dirput_perkara')->result_array();
    }

    public function pengajuan($perkara_id,$pengajuan_id){
        $query = "SELECT p.dakwaan as dakwaan, ppnun.isi_penuntutan as tuntutan_jpu, p.posita as posita, 
                  prk.isi_petitum as rekonvensi, prk.isi_petitum as petitum, pput.amar_putusan as amar_tingkat_1, 
                  pb.amar_putusan_banding as amar_tingkat_banding, pk.amar_putusan_kasasi as amar_kasasi
                FROM perkara AS p
                LEFT JOIN perkara_penuntutan AS ppnun ON p.perkara_id = ppnun.perkara_id 
                LEFT JOIN perkara_rekonvensi AS prk ON p.perkara_id = prk.perkara_id 
                LEFT JOIN perkara_putusan AS pput ON p.perkara_id = pput.perkara_id 
                LEFT JOIN perkara_banding AS pb ON p.perkara_id = pb.perkara_id 
                LEFT JOIN perkara_kasasi AS pk ON p.perkara_id = pk.perkara_id 
                LEFT JOIN dirput_dokumen AS ddn ON p.perkara_id = ddn.perkara_id
                WHERE p.perkara_id = $perkara_id AND ddn.pengajuan_id = $pengajuan_id";
        return $this->db->query($query)->row_array();
    }
   
    public function update_pengajuan($perkara_id,$pengajuan_id,$status){
        $this->db->where('perkara_id',$perkara_id);
        $this->db->where('pengajuan_id',$pengajuan_id);
        $this->db->set('status_pengajuan',$status);
        $this->db->set('tgl_status_pengajuan', date('Y-m-d'));
        return $this->db->update('dirput_pengajuan');
    }
    
    public function update_kasasi($perkara_id,$tabel,$data){
        $this->db->where('perkara_id',$perkara_id);
        return $this->db->update($tabel,$data);
    }
    
    public function update_pk($perkara_id,$tabel,$data){
        $this->db->where('perkara_id',$perkara_id);
        return $this->db->update($tabel,$data);
    }
    
    public function putusan_dirput($perkara_id,$tabel,$field){       
        return $this->db->query("select $field as file_id from $tabel where perkara_id = $perkara_id;")->result_array();
    }
    
public function pengantar($perkara_id,$pengajuan_id){
		$query = $this->db->query("SELECT
		  ddn.perkara_id,
		  dp.putusan_id,
		  ddn.filename,
		  ddn.path_filename,
		  dpj.nomor_surat_pengantar,
		  dpj.tgl_surat_pengantar,
		  dpj.no_resi_pos,
		  dpj.pengajuan_id,
		  pb.nomor_putusan_banding,
          pk.nomor_perkara_kasasi,
          pk.nomor_putusan_kasasi
		FROM
		  dirput_dokumen AS ddn
		  LEFT JOIN dirput_perkara AS dp
			ON ddn.perkara_id = dp.perkara_id
		  LEFT JOIN dirput_dokumen_ref AS drf
			ON ddn.dokumen_ref_id = drf.dokumen_ref_id
		  LEFT JOIN dirput_pengajuan AS dpj
			ON (dp.perkara_id = dpj.perkara_id AND ddn.pengajuan_id = dpj.pengajuan_id)
		  LEFT JOIN perkara_banding AS pb
			ON dpj.perkara_id = pb.perkara_id
                  LEFT JOIN perkara_kasasi AS pk
			ON dpj.perkara_id = pk.perkara_id
		WHERE ddn.perkara_id = $perkara_id
		AND ddn.pengajuan_id = $pengajuan_id
		AND drf.urutan = 11;");
		return $query->first_row('array');
		}

# ------------------------------------------------------------------------------------------------
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->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 AND a.pengajuan_id = b.pengajuan_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,$idpihak,$alur_perkara_id) {

	try {
	     $kweri="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 (SELECT * FROM dirput_dokumen WHERE perkara_id=$perkara_id AND pihak_detil_id=$idpihak) b
							ON a.dokumen_ref_id=b.dokumen_ref_id AND a.pengajuan_id AND b.pengajuan_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 a.alur_perkara_id=$alur_perkara_id AND a.keterangan_file NOT LIKE '%surat pengantar%'  ORDER BY a.urutan ASC";
		return $this->db->query($kweri);
		} 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;
		}

}

#-------------------------------------------------------------------------------------------------------
	public function ambil_dari_dirput($putusan_id,$nomor_perkara,$pengajuan_id,$nomor_perkara_key){
            $datanya = explode('/',$nomor_perkara);
            $invalid = substr($nomor_perkara, 0, 30);
            
            if(count($datanya)!==4){
                $nomor_perkara = "INVALID format on ".$invalid;
                //$nomor_perkara_key = $invalid;
            }else{
                if(strlen($datanya[2]!==4)){
                    //$nomor_perkara = "INVALID format on ".$invalid;
                    //$nomor_perkara_key = $invalid;
                    $nomor_perkara = $invalid;
                }
                if(strlen($datanya[3])>6){
                    //$nomor_perkara = "INVALID format on ".$invalid;
                    //$nomor_perkara_key = $invalid;
                    $nomor_perkara = $invalid;
                }
            }
            $insert_data = array(
					'id'                    => null,
					'perkara_id'		=> null,
					'putusan_id'		=> $putusan_id,
					'nomor_perkara'		=> $nomor_perkara,
					'pengajuan_id'		=> $pengajuan_id,
					'nomor_perkara_key'	=> $nomor_perkara_key
					);
                if(!$this->db->replace("dirput_perkara",$insert_data)){ return FALSE;}else{
//		$query = ("UPDATE dirput_perkara AS dp
//				  LEFT JOIN perkara AS p
//				  ON dp.nomor_perkara_key = (SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(UPPER(p.nomor_perkara), '/', ''),
//				  '.',''),'-',''),' ',''),'',''))SET dp.perkara_id = p.perkara_id");
//		return $this->db->query($query);
                    return TRUE;
                }
	}

        public function ambil_pengajuan($perkara_id,$pengajuan_id){
           try {
               $this->db->where('perkara_id',$perkara_id)->where('pengajuan_id',$pengajuan_id);
                return $this->db->get('dirput_pengajuan')->result_array();
           } catch (Exception $e) {
			return FALSE;
		}

        }
        
        public function ambil_file_id($perkara_id,$pengajuan_id){
            $query = "select keterangan from dirput_dokumen
                    where perkara_id = $perkara_id and pengajuan_id = $pengajuan_id
                    and filename not like '%surat_pengantar%'";
            return $this->db->query($query)->result_array();
        }
		
		public function ambil_pihak($perkara_id){
			$querynya = "SELECT * FROM (SELECT '1' AS status_pihak, perkara_id, pp1.nama AS nama, pp1.urutan AS urutan, pp1.alamat AS alamat
						FROM perkara_pihak1 AS pp1 
						WHERE perkara_id = ".$perkara_id."
						UNION ALL 
						SELECT '2' AS status_pihak, pp2.perkara_id, pp2.nama, pp2.urutan, pp2.alamat 
						FROM perkara_pihak2 AS pp2
						WHERE perkara_id = ".$perkara_id."
						UNION ALL
						SELECT '3' AS status_pihak, pp2.perkara_id, pp2.nama, pp2.urutan, pp2.alamat 
						FROM perkara_pihak3 AS pp2
						WHERE perkara_id = ".$perkara_id." 
						UNION ALL 
						SELECT '4' AS status_pihak, pp2.perkara_id, pp2.nama, pp2.urutan, pp2.alamat 
						FROM perkara_pihak4 AS pp2
						WHERE perkara_id = ".$perkara_id."
						UNION ALL 
						SELECT '5' AS status_pihak, pp2.perkara_id, pp2.nama, pp2.urutan, pp2.alamat 
						FROM perkara_pihak5 AS pp2
						WHERE perkara_id = ".$perkara_id.") 
						AS a
						ORDER BY status_pihak, urutan ASC";
			return $this->db->query($querynya)->result_array();			
		}

public function coook(){
   $configs = $this->db->where('guid','772c9abf5316d920ae871459a979fc48')->get('dirput_ref')->result_array();
   if(empty($configs)){return FALSE;}else{
        $config['server']="https://putusan.mahkamahagung.go.id/api";
        $config['http_user'] = base64_decode($configs[0]['category']);
        $config['http_pass'] = base64_decode($configs[0]['sub_category']);
        $config['http_auth'] = base64_decode($configs[0]['parentguid']);
	$config['ssl_verify_peer'] = FALSE;
	return $config;
	}
}

}
