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

class Perkara extends CI_Model{
	
	function getJenisPerkara($idalurperkara){
		if(empty($idalurperkara)) return '';
		try {
			$this->db->where('id',$idalurperkara);
			$result = $this->db->get('alur_perkara');
			if($result->num_rows>0){
				return $result->row()->nama;
			}else{
				return 'Alur Perkara Tidak Ditemukan';
			}
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}
	
	function isNomorPerkara($txt){
		$re1='(\\d+)';	# Integer Number 1
		$re2='(\\/)';	# Any Single Character 1
		$re3='.*?';	# Non-greedy match on filler
		$re4='(\\/)';	# Any Single Character 2
		$re5='((?:(?:[1]{1}\\d{1}\\d{1}\\d{1})|(?:[2]{1}\\d{3})))(?![\\d])';	# Year 1
		$re6='(\\/)';	# Any Single Character 3
		$re7='(PN)';	# Variable Name 1
		if ($c=preg_match_all ("/".$re1.$re2.$re3.$re4.$re5.$re6.$re7."/is", $txt, $matches)){
		      $int1=$matches[1][0];
		      $c1=$matches[2][0];
		      $c2=$matches[3][0];
		      $year1=$matches[4][0];
		      $c3=$matches[5][0];
		      $var1=$matches[6][0];
		      return 1;
		}else{
			return 0;
		}
	}

	function parseOrderby($col){
		if($col==1){
			return "nomor_perkara"; 
		}else if($col==2){
			return "tanggal_pendaftaran"; 
		}else if($col==3){
			return "jenis_perkara_nama"; 
		}else if($col==4){
			return "para_pihak"; 
		}else if($col==5){
			return "tahapan_terakhir_text"; 
		}else if($col==6){
			return "proses_terakhir_text"; 
		}else if($col==7){
			return "durasi"; 
		}else{
			return "tanggal_pendaftaran";
		}
	}

	function parseOrderbyTaskList($col){
		if($col==1){
			return "nomor_perkara"; 
		}else if($col==2){
			return "tanggal_pendaftaran"; 
		}else if($col==3){
			return "jenis_perkara_nama"; 
		}else if($col==4){
			return "para_pihak"; 
		}else if($col==5){
			return "tahapan_terakhir_text"; 
		}else if($col==6){
			return "proses_terakhir_text"; 
		}else if($col==7){
			return "durasi"; 
		}else{
			return "tanggal_pendaftaran";
		}
	}

	function getPerkaraList($idalurperkara='',$col=2,$type='DESC',$begin=0,$key=''){
		$orderby = $this->parseOrderby($col);
		$where = '';
		
		if($idalurperkara==1 OR $idalurperkara==7){
			$idalurperkara = '1,7';
		}
		if($idalurperkara>0){
			$where = "AND alur_perkara_id IN (".$idalurperkara.")";
		}else{
			$where = "";
		}

		if(!empty($key)){
			$where_like = ' WHERE (nomor_perkara LIKE "%'.$key.'%" OR tanggal_pendaftaran LIKE "%'.$key.'%" OR jenis_perkara_nama LIKE "%'.$key.'%" OR para_pihak LIKE "%'.$key.'%" OR tahapan_terakhir_text LIKE "%'.$key.'%" OR proses_terakhir_text LIKE "%'.$key.'%") ';
		}else{
			$where_like =" ";
			if($idalurperkara>0){
				$where = " WHERE alur_perkara_id IN (".$idalurperkara.")";
			}
		}
		try {
			$sql="SELECT 
						SQL_CALC_FOUND_ROWS 
						v.perkara_id, 
						v.perkara_id,
						alur_perkara_id,
						jenis_perkara_nama,
						nomor_perkara,
						tanggal_pendaftaran,
						para_pihak, 
						tahapan_terakhir_text, 
						proses_terakhir_text,
						IF(proses_terakhir_id<210,
							DATEDIFF(CURDATE(),tanggal_pendaftaran)-IFNULL((SELECT durasi_mediasi FROM v_durasi_mediasi a WHERE a.perkara_id=v.perkara_id),0),
							DATEDIFF(tanggal_putusan,tanggal_pendaftaran)-IFNULL((SELECT durasi_mediasi FROM v_durasi_mediasi a WHERE a.perkara_id=v.perkara_id),0)
						)+1	AS durasi
					FROM v_perkara AS v
					".$where_like." ".$where." ORDER BY ".$orderby." ".$type." ,nomor_indeks DESC LIMIT ".$begin.",50";
			$query = $this->db->query($sql);
			return $query;
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}
	
	function getPerkaraList_search_detail($idalurperkara='',$col=2,$type='DESC',$begin=0,$key=''){
		$orderby = $this->parseOrderby($col);
		$where = '';
		if($idalurperkara==1 OR $idalurperkara==7){
			$idalurperkara = '1,7';
		}
		if(!empty($key)){
			$where_like = ' WHERE '.$key.' ';
		}else{
			$where_like =" ";
			if($idalurperkara>0){
				$where = " WHERE alur_perkara_id IN (".$idalurperkara.")";
			}
		}

		try {
			$sql="SELECT 
						SQL_CALC_FOUND_ROWS 
						v.perkara_id, 
						v.perkara_id,
						alur_perkara_id,
						jenis_perkara_nama,
						nomor_perkara,
						tanggal_pendaftaran,
						para_pihak, 
						tahapan_terakhir_text, 
						proses_terakhir_text,
						IF(proses_terakhir_id<210,
							DATEDIFF(CURDATE(),tanggal_pendaftaran)-IFNULL(med.durasi_mediasi,0),
							DATEDIFF(tanggal_putusan,tanggal_pendaftaran)-IFNULL(med.durasi_mediasi,0)
						)+1 AS durasi
					FROM v_perkara_sidang_keliling AS v
					LEFT JOIN (SELECT perkara_id, SUM(DATEDIFF(IF(tgl_laporan_mediator IS NULL,NOW(),tgl_laporan_mediator),dimulai_mediasi)) AS durasi_mediasi FROM perkara_mediasi WHERE tahapan_id = 10 GROUP BY perkara_id) AS med ON v.perkara_id = med.perkara_id
					".$where_like." ".$where." ORDER BY ".$orderby." ".$type." ,nomor_indeks DESC LIMIT ".$begin.",50";
			$query = $this->db->query($sql);
			return $query;
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}

	function getPerkaraTaskList($userid='',$idalurperkara='',$col=2,$type='DESC',$begin=0,$keyword='', $wheres=''){
		$orderby = $this->parseOrderbyTaskList($col);
		$where = '';
		$wherez_like = '';
		if($idalurperkara==1 OR $idalurperkara==7){
			$idalurperkara = '1,7';
		}
		if($idalurperkara>0){
			$where = "AND alur_perkara_id IN (".$idalurperkara.")";
		}else{
			$where = "";
		}

		if(!empty($keyword)){
			$wherez_like = ' AND (nomor_perkara LIKE "%'.$keyword.'%" OR tanggal_pendaftaran LIKE "%'.$keyword.'%" OR jenis_perkara_nama LIKE "%'.$keyword.'%" OR para_pihak LIKE "%'.$keyword.'%" OR tahapan_terakhir_text LIKE "%'.$keyword.'%" OR proses_terakhir_text LIKE "%'.$keyword.'%") ';
		}else{
			$where_like =" ";
			if($idalurperkara>0){
				$wherez = " AND alur_perkara_id IN (".$idalurperkara.")";
			}
		}
		try {
			$sql="SELECT 
						SQL_CALC_FOUND_ROWS 
						v.perkara_id, 
						v.perkara_id,
						alur_perkara_id,
						jenis_perkara_nama,
						nomor_perkara,
						tanggal_pendaftaran,
						para_pihak, 
						tahapan_terakhir_text, 
						proses_terakhir_text,
						IF(proses_terakhir_id<210,
							DATEDIFF(CURDATE(),tanggal_pendaftaran)-IFNULL(med.durasi_mediasi,0),
							DATEDIFF(tanggal_putusan,tanggal_pendaftaran)-IFNULL(med.durasi_mediasi,0)
						)+1 AS durasi
					FROM v_perkara_list as v
					LEFT JOIN (SELECT perkara_id, SUM(DATEDIFF(IF(tgl_laporan_mediator IS NULL,NOW(),tgl_laporan_mediator),dimulai_mediasi)) AS durasi_mediasi FROM perkara_mediasi WHERE tahapan_id = 10 GROUP BY perkara_id) AS med ON v.perkara_id = med.perkara_id
					WHERE ".$wheres.' '.$wherez_like." AND alur_perkara_id<>114 ORDER BY ".$orderby." ".$type.",nomor_indeks DESC LIMIT ".$begin.",50";			
			$query = $this->db->query($sql);
			return $query;
			
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}

	function getDetilUser($userid){
		try {
			$query = $this->db->query("SELECT * FROM v_users WHERE userid=".$userid.";");
			if($query->num_rows>0){
				return $query->row(0);
			}else{
				return 0;
    		}
		} catch (Exception $e) {
			log_message('error', $e);
		}
    }

	function fecth_data_num($idalurperkara,$key){
		$query = $this->db->query("SELECT FOUND_ROWS() as cnt;");
		if($query->num_rows>0){
			foreach ($query->result() as $row) {
				return $row->cnt;
			}
    		return $query->num_rows;
    	}else{
    		return 0;
    	}
	}


	function getPerkaraCetak($idalurperkara='',$key=''){
		$where = '';
		if($idalurperkara==1 OR $idalurperkara==7){
			$idalurperkara = '1,7';
		}
		if($idalurperkara>0){
			$where = "AND alur_perkara_id IN (".$idalurperkara.")";
		}else{
			$where = "";
		}

		if(!empty($key)){
			$where_like = ' WHERE (nomor_perkara LIKE "%'.$key.'%" OR tanggal_pendaftaran LIKE "%'.$key.'%" OR jenis_perkara_nama LIKE "%'.$key.'%" OR para_pihak LIKE "%'.$key.'%" OR tahapan_terakhir_text LIKE "%'.$key.'%" OR proses_terakhir_text LIKE "%'.$key.'%") ';
		}else{
			$where_like =" ";
			if($idalurperkara>0){
				$where = " WHERE alur_perkara_id IN (".$idalurperkara.")";
			}
		}
		try {
			$sql="SELECT 
						SQL_CALC_FOUND_ROWS 
						v.perkara_id, 
						v.perkara_id,
						alur_perkara_id,
						jenis_perkara_nama,
						nomor_perkara,
						tanggal_pendaftaran,
						para_pihak, 
						tahapan_terakhir_text, 
						proses_terakhir_text,
						IF(proses_terakhir_id<210,
							DATEDIFF(CURDATE(),tanggal_pendaftaran)-IFNULL(med.durasi_mediasi,0),
							DATEDIFF(tanggal_putusan,tanggal_pendaftaran)-IFNULL(med.durasi_mediasi,0)
						)+1 AS durasi
					FROM v_perkara as v
					LEFT JOIN (SELECT perkara_id, SUM(DATEDIFF(IF(tgl_laporan_mediator IS NULL,NOW(),tgl_laporan_mediator),dimulai_mediasi)) AS durasi_mediasi FROM perkara_mediasi WHERE tahapan_id = 10 GROUP BY perkara_id) AS med ON v.perkara_id = med.perkara_id
					".$where_like." ".$where." ORDER BY tanggal_pendaftaran ASC";
			$query = $this->db->query($sql);
			return $query;
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}
}

?>