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

class Data_dashboard extends CI_Model{

  function getJenisPerkara($idalurperkara){
    if(empty($idalurperkara)) return '';
    try {
      if($this->db=='xxx'){
        show_error('Database belum disetting.');
      }
      $this->db->where('id',$idalurperkara);
      return $this->db->get('alur_perkara')->row()->nama;
    } catch (Exception $e) {
      log_message('error', $e);
    }
  }

  function alur_perdata(){
    try {
      $query = $this->db->query("SELECT id,nama FROM alur_perkara WHERE aktif='Y' AND id <111;"); 
      return $query;
    } catch (Exception $e) {
      log_message('error', $e);
    }

  }

  function alur_pidana(){
    try {

      $query = $this->db->query("SELECT id,nama FROM alur_perkara WHERE aktif='Y' AND id >=111;"); 
      return $query;

    } catch (Exception $e) {
      log_message('error', $e);
    }

  }

  function tahun_perkara(){
    try {
      if($this->db=='xxx'){
          show_error('Database belum disetting.');
      }
      $query = $this->db->query('SELECT DISTINCT(LEFT(tanggal_pendaftaran,4)) AS tahun FROM perkara ORDER BY tahun DESC;'); 
      return $query;

    } catch (Exception $e) {
      log_message('error', $e);
    }

  }

  function get_data_indikator($bulanini,$bulandepan){
    try {

      $jenis_pengadilan = $this->session->userdata('jenis_pengadilan');
      $query = $this->db->query("SELECT 
            a.id,
            a.nama,
            (if (a.id<>118,
                (SELECT 
                  COUNT(p.perkara_id) 
                FROM
                  perkara AS p 
                  LEFT OUTER JOIN perkara_putusan AS putus 
                    ON p.perkara_id = putus.perkara_id 
                WHERE p.alur_perkara_id = a.id 
                  AND LEFT(p.tanggal_pendaftaran, 7) < '".$bulanini."' 
                  AND (
                    putus.tanggal_putusan IS NULL 
                    OR LEFT(putus.tanggal_putusan, 7) >= '".$bulanini."'
                  ) 
                  AND '".$bulanini."' <= CONCAT(YEAR(CURRENT_DATE),'-',RIGHT(CONCAT('0',CAST(MONTH(CURRENT_DATE) AS CHAR(2))),2)
                  )),(SELECT 
                          COUNT(p.perkara_id)
                        FROM
                          perkara AS p 
                          LEFT JOIN perkara_putusan AS putus 
                            ON p.perkara_id = putus.perkara_id 
                        WHERE p.alur_perkara_id = a.id 
                          AND LEFT(p.tanggal_pendaftaran, 7) < '".$bulanini."' 
                          AND (putus.tanggal_putusan IS NULL 
                            OR LEFT(putus.tanggal_putusan, 7) >= '".$bulanini."')
                          AND p.perkara_id NOT IN (SELECT diversi.perkara_id FROM perkara_diversi AS diversi WHERE diversi.hasil_diversi = 1 
                          AND (diversi.tgl_penetapan_kesepakatan_diversi <> '0000-00-00' 
                            OR LEFT(diversi.tgl_penetapan_kesepakatan_diversi,7) >= '".$bulandepan."'
                          )))
                  )) 
            AS belum_putus,
            (SELECT 
              COUNT(p.perkara_id) 
            FROM
              perkara AS p 
            WHERE p.alur_perkara_id = a.id 
              AND LEFT(p.tanggal_pendaftaran, 7) = '".$bulanini."') AS masuk,
            (IF(a.id <> 118, (SELECT 
              COUNT(p.perkara_id) 
            FROM
              perkara AS p 
              LEFT OUTER JOIN perkara_putusan AS putus 
                ON p.perkara_id = putus.perkara_id 
            WHERE p.alur_perkara_id = a.id 
              AND LEFT(p.tanggal_pendaftaran, 7) <= '".$bulanini."'
              AND LEFT(putus.tanggal_putusan, 7) = '".$bulanini."'), 
            IF((SELECT 
              COUNT(p.perkara_id) 
            FROM
              perkara AS p 
              LEFT OUTER JOIN perkara_putusan AS putus 
                ON p.perkara_id = putus.perkara_id 
            WHERE p.alur_perkara_id = a.id 
              AND LEFT(p.tanggal_pendaftaran, 7) <= '".$bulanini."'
              AND LEFT(putus.tanggal_putusan, 7) = '".$bulanini."')=0,
              (SELECT 
              COUNT(p.perkara_id)+(SELECT 
                      COUNT(p.perkara_id) 
                    FROM
                      perkara AS p 
                      LEFT OUTER JOIN perkara_putusan AS putus 
                        ON p.perkara_id = putus.perkara_id 
                    WHERE p.alur_perkara_id = a.id 
                      AND LEFT(p.tanggal_pendaftaran, 7) <= '".$bulanini."'
                      AND LEFT(putus.tanggal_putusan, 7) = '".$bulanini."') 
            FROM
              perkara AS p 
              LEFT OUTER JOIN perkara_diversi AS putus 
                ON p.perkara_id = putus.perkara_id 
            WHERE p.alur_perkara_id = a.id 
              AND LEFT(p.tanggal_pendaftaran, 7) <= '".$bulanini."'
              AND LEFT(putus.tgl_penetapan_kesepakatan_diversi, 7) = '".$bulanini."'
              AND putus.hasil_diversi=1 AND tgl_minutasi IS NOT NULL AND dibuka_kembali=0), 
            (SELECT 
              COUNT(p.perkara_id) 
            FROM
              perkara AS p 
              LEFT OUTER JOIN perkara_putusan AS putus 
                ON p.perkara_id = putus.perkara_id 
            WHERE p.alur_perkara_id = a.id 
              AND LEFT(p.tanggal_pendaftaran, 7) <= '".$bulanini."'
              AND LEFT(putus.tanggal_putusan, 7) = '".$bulanini."'))              
              )) AS putus,
            (IF (a.id<>118,
                (SELECT 
                  COUNT(p.perkara_id) 
                FROM
                  perkara AS p 
                  LEFT OUTER JOIN perkara_putusan AS putus 
                    ON p.perkara_id = putus.perkara_id 
                WHERE p.alur_perkara_id = a.id 
                  AND LEFT(p.tanggal_pendaftaran, 7) < '".$bulandepan."'
                  AND (putus.tanggal_putusan IS NULL 
                    OR LEFT(putus.tanggal_putusan, 7) >= '".$bulandepan."')
                ),(SELECT 
                          COUNT(p.perkara_id)
                        FROM
                          perkara AS p 
                          LEFT JOIN perkara_putusan AS putus 
                            ON p.perkara_id = putus.perkara_id 
                        WHERE p.alur_perkara_id = a.id 
                          AND LEFT(p.tanggal_pendaftaran, 7) < '".$bulandepan."' 
                          AND (putus.tanggal_putusan IS NULL 
                            OR LEFT(putus.tanggal_putusan, 7) >= '".$bulandepan."')
                          AND p.perkara_id NOT IN (SELECT diversi.perkara_id FROM perkara_diversi AS diversi WHERE diversi.hasil_diversi = 1 
                          AND tgl_minutasi IS NOT NULL AND dibuka_kembali=0
                          AND (diversi.tgl_penetapan_kesepakatan_diversi <> '0000-00-00' 
                            OR LEFT(diversi.tgl_penetapan_kesepakatan_diversi,7) >= '".$bulandepan."'
                          )))))
            AS sisa,
            (SELECT 
              COUNT(*) 
            FROM
              perkara_banding 
            WHERE alur_perkara_id = a.id 
              AND LEFT(permohonan_banding, 7) = '".$bulanini."') AS banding,
            (SELECT 
              COUNT(*) 
            FROM
              perkara_kasasi 
            WHERE alur_perkara_id = a.id 
              AND LEFT(permohonan_kasasi, 7) = '".$bulanini."' 
              AND '".$bulanini."'  <= '".$bulanini."' ) AS kasasi,
            (SELECT 
              COUNT(*) 
            FROM
              perkara_pk 
            WHERE alur_perkara_id = a.id 
              AND LEFT(permohonan_pk, 7) = '".$bulanini."'
              AND '".$bulanini."'  <= '".$bulanini."' ) AS pk,
            (SELECT 
              COUNT(*) 
            FROM
              perkara_eksekusi 
            WHERE alur_perkara_id = a.id 
              AND LEFT(permohonan_eksekusi, 7) = '".$bulanini."'
              AND '".$bulanini."'  <= '".$bulanini."' ) AS eksekusi,
            (SELECT 
              COUNT(*) 
            FROM
              perkara_grasi 
            WHERE alur_perkara_id = a.id 
              AND LEFT(permohonan_grasi, 7) = '".$bulanini."'
              AND '".$bulanini."'  <= '".$bulanini."' ) AS grasi 
          FROM
            alur_perkara AS a 
          WHERE a.aktif = 'Y' AND jenis_pengadilan = '".$jenis_pengadilan."'
          ORDER BY a.id ");
      return $query;
    } catch (Exception $e) {
      log_message('error', $e);
    }
  }

  function get_penambahan_perkara($bulanini,$bulandepan){
    try {
      $jenis_pengadilan = $this->session->userdata('jenis_pengadilan');
      $query = $this->db->query("select a.id,a.nama
             ,(select count(*) from v_perkara where alur_perkara_id = a.id
                  and tanggal_pendaftaran >= '".$bulanini."-01'
                    and tanggal_pendaftaran <= '".$bulanini."-07'
              ) AS masuk1
             ,(select count(*) from v_perkara where alur_perkara_id = a.id
                  and tanggal_pendaftaran >= '".$bulanini."-08'
                    and tanggal_pendaftaran <= '".$bulanini."-14'
              ) AS masuk2
             ,(select count(*) from v_perkara where alur_perkara_id = a.id
                  and tanggal_pendaftaran >= '".$bulanini."-15'
                    and tanggal_pendaftaran <= '".$bulanini."-21'
              ) AS masuk3
             ,(select count(*) from v_perkara where alur_perkara_id = a.id
                  and tanggal_pendaftaran >= '".$bulanini."-22'
                    and tanggal_pendaftaran <= '".$bulanini."-28'
              ) AS masuk4
             ,(select count(*) from v_perkara where alur_perkara_id = a.id
                  and tanggal_pendaftaran >= '".$bulanini."-29'
                    and tanggal_pendaftaran <= '".$bulanini."-31'
              ) AS masuk5
            from alur_perkara as a
            where a.aktif='Y' AND jenis_pengadilan = '".$jenis_pengadilan."'
            order by a.id");
      return $query;

    } catch (Exception $e) {
      log_message('error', $e);
    }
  }

  function get_tahanan_min7(){
    try {
      $jenis_pengadilan = $this->session->userdata('jenis_pengadilan');
      $query =  $this->db->query("SELECT p.alur_perkara_id, p.alur_perkara_nama AS nama, count(*) AS jumlah
                                  FROM penahanan_terdakwa n 
                                  INNER JOIN (
                                    SELECT id, pihak_id, mulai, MAX(sampai) AS sampai
                                    FROM penahanan_terdakwa WHERE  status_penahanan_id = 1 AND jenis_tahanan_id > 0
                                    GROUP BY pihak_id
                                  ) AS max USING (pihak_id, sampai)
                                  LEFT JOIN v_perkara AS p
                                  ON p.perkara_id = n.perkara_id
                                  WHERE p.jenis_pengadilan = '".$jenis_pengadilan."'  AND p.tanggal_putusan IS NULL  AND DATEDIFF(CURRENT_DATE,n.sampai) >=-7 AND DATEDIFF(CURRENT_DATE,n.sampai) <= 0 
                                  AND n.perkara_id NOT IN 
                                    (SELECT 
                                      perkara_id 
                                    FROM
                                      perkara_diversi 
                                    WHERE tgl_penetapan_kesepakatan_diversi IS NOT NULL 
                                      AND tgl_minutasi IS NOT NULL 
                                      AND hasil_diversi = 1) 
                                  AND n.pihak_id NOT IN (SELECT b.pihak_id
                                      FROM    
                                      (SELECT pihak_id, MAX(id) AS id_pen
                                      FROM penahanan_terdakwa
                                      GROUP BY pihak_id ) AS a
                                      LEFT JOIN penahanan_terdakwa AS b ON a.pihak_id=b.pihak_id AND a.id_pen = b.id
                                      WHERE b.jenis_tahanan_id < 1 AND status_penahanan_id =2)
                                  GROUP BY p.alur_perkara_id
                                  ORDER BY p.alur_perkara_id ASC;");
      return $query;

    } catch (Exception $e) {
      log_message('error', $e);
    }
  }

  function get_tahanan_habis(){
    try {
      $jenis_pengadilan = $this->session->userdata('jenis_pengadilan');
      $query =  $this->db->query("SELECT p.alur_perkara_id, p.alur_perkara_nama AS nama, count(*) AS jumlah
                                  FROM penahanan_terdakwa n 
                                  INNER JOIN (
                                    SELECT id, pihak_id, mulai, MAX(sampai) AS sampai
                                    FROM penahanan_terdakwa WHERE  status_penahanan_id = 1 AND jenis_tahanan_id > 0
                                    GROUP BY pihak_id
                                  ) AS max USING (pihak_id, sampai)
                                  LEFT JOIN v_perkara AS p
                                  ON p.perkara_id = n.perkara_id
                                  WHERE p.jenis_pengadilan = '".$jenis_pengadilan."'  AND p.tanggal_putusan IS NULL  AND DATEDIFF(CURRENT_DATE,n.sampai) >0 
                                  AND n.perkara_id NOT IN 
                                    (SELECT 
                                      perkara_id 
                                    FROM
                                      perkara_diversi 
                                    WHERE tgl_penetapan_kesepakatan_diversi IS NOT NULL 
                                      AND tgl_minutasi IS NOT NULL 
                                      AND hasil_diversi = 1) 
                                      AND n.pihak_id NOT IN (SELECT b.pihak_id
                                          FROM    
                                          (SELECT pihak_id, MAX(id) AS id_pen
                                          FROM penahanan_terdakwa
                                          GROUP BY pihak_id ) AS a
                                          LEFT JOIN penahanan_terdakwa AS b ON a.pihak_id=b.pihak_id AND a.id_pen = b.id
                                          WHERE b.jenis_tahanan_id < 1 AND status_penahanan_id = 2 OR status_penahanan_id = 5)
                                  GROUP BY p.alur_perkara_id
                                  ORDER BY p.alur_perkara_id ASC;");
      return $query;
    } catch (Exception $e) {
      log_message('error', $e);
    }
  }  

  function get_tahanan_min7_task($idhakim,$idpanitera){
    try {
      $jenis_pengadilan = $this->session->userdata('jenis_pengadilan');
      $query =  $this->db->query("SELECT a.* ,COUNT(*) AS jumlah 
                            FROM 
                            (SELECT p.alur_perkara_id, p.alur_perkara_nama AS nama, p.`perkara_id`
                            FROM penahanan_terdakwa n 
                            INNER JOIN (
                              SELECT id, pihak_id, mulai, MAX(sampai) AS sampai
                              FROM penahanan_terdakwa WHERE  status_penahanan_id = 1 AND jenis_tahanan_id > 0
                              GROUP BY pihak_id
                            ) AS MAX USING (pihak_id, sampai)
                            LEFT JOIN v_perkara AS p
                            ON p.perkara_id = n.perkara_id
                            WHERE p.tanggal_putusan IS NULL  AND DATEDIFF(CURRENT_DATE,n.sampai) >=-7 AND DATEDIFF(CURRENT_DATE,n.sampai) <= 0
                            AND n.perkara_id NOT IN 
                                    (SELECT 
                                      perkara_id 
                                    FROM
                                      perkara_diversi 
                                    WHERE tgl_penetapan_kesepakatan_diversi IS NOT NULL 
                                      AND tgl_minutasi IS NOT NULL 
                                      AND hasil_diversi = 1)  
                                        AND n.pihak_id NOT IN (SELECT b.pihak_id
                                        FROM    
                                        (SELECT pihak_id, MAX(id) AS id_pen
                                        FROM penahanan_terdakwa
                                        GROUP BY pihak_id ) AS a
                                        LEFT JOIN penahanan_terdakwa AS b ON a.pihak_id=b.pihak_id AND a.id_pen = b.id
                                        WHERE b.jenis_tahanan_id < 1 AND status_penahanan_id =2) 
                            ORDER BY p.alur_perkara_id ASC) AS a,
                            perkara_penetapan AS b
                            WHERE a.perkara_id=b.`perkara_id` AND (( b.majelis_hakim_id IS NOT NULL AND 
                      CONCAT(',',b.majelis_hakim_id,',') LIKE '%,".$idhakim.",%'  AND '".$idhakim."' <> '')
                      OR 
                      (b.panitera_pengganti_id IS NOT NULL AND 
                      CONCAT(',',b.panitera_pengganti_id,',') LIKE '%,".$idpanitera.",%' AND '".$idpanitera."' <> ''))
                      GROUP BY a.alur_perkara_id
                      ORDER BY a.alur_perkara_id ASC");
      return $query;

    } catch (Exception $e) {
      log_message('error', $e);
    }
  }

    function get_tahanan_habis_task($idhakim,$idpanitera){
    try {
      $jenis_pengadilan = $this->session->userdata('jenis_pengadilan');
      $query =  $this->db->query("SELECT a.* ,COUNT(*) AS jumlah 
                            FROM 
                            (SELECT p.alur_perkara_id, p.alur_perkara_nama AS nama, p.`perkara_id`
                            FROM penahanan_terdakwa n 
                            INNER JOIN (
                              SELECT id, pihak_id, mulai, MAX(sampai) AS sampai
                              FROM penahanan_terdakwa WHERE  status_penahanan_id = 1 AND jenis_tahanan_id > 0
                              GROUP BY pihak_id
                            ) AS MAX USING (pihak_id, sampai)
                            LEFT JOIN v_perkara AS p
                            ON p.perkara_id = n.perkara_id
                            WHERE p.tanggal_putusan IS NULL  AND DATEDIFF(CURRENT_DATE,n.sampai) >0
                            AND n.perkara_id NOT IN 
                                    (SELECT 
                                      perkara_id 
                                    FROM
                                      perkara_diversi 
                                    WHERE tgl_penetapan_kesepakatan_diversi IS NOT NULL 
                                      AND tgl_minutasi IS NOT NULL 
                                      AND hasil_diversi = 1)   
                                      AND n.pihak_id NOT IN (SELECT b.pihak_id
                                      FROM    
                                      (SELECT pihak_id, MAX(id) AS id_pen
                                      FROM penahanan_terdakwa
                                      GROUP BY pihak_id ) AS a
                                      LEFT JOIN penahanan_terdakwa AS b ON a.pihak_id=b.pihak_id AND a.id_pen = b.id
                                      WHERE b.jenis_tahanan_id < 1 AND status_penahanan_id =2)
                            ORDER BY p.alur_perkara_id ASC) AS a,
                            perkara_penetapan AS b
                            WHERE a.perkara_id=b.`perkara_id` AND (( b.majelis_hakim_id IS NOT NULL AND 
                      CONCAT(',',b.majelis_hakim_id,',') LIKE '%,".$idhakim.",%'  AND '".$idhakim."' <> '')
                      OR 
                      (b.panitera_pengganti_id IS NOT NULL AND 
                      CONCAT(',',b.panitera_pengganti_id,',') LIKE '%,".$idpanitera.",%' AND '".$idpanitera."' <> ''))
                      GROUP BY a.alur_perkara_id
                      ORDER BY a.alur_perkara_id ASC");
      return $query;

    } catch (Exception $e) {
      log_message('error', $e);
    }
  }  

  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 "lama_proses"; 
        }else{
          return "tanggal_pendaftaran";
        }
      }

  function parseOrderbyTahanan($col){
    if($col==1){
      return "nomor_perkara"; 
        }else if($col==2){
          return "nama"; 
        }else if($col==3){
          return "tempat_lahir"; 
        }else if($col==4){
          return "tanggal_lahir"; 
        }else if($col==5){
          return "umur"; 
        }else if($col==6){
          return "jenis_kelamin"; 
        }else if($col==7){
          return "alamat"; 
        }else if($col==8){
          return "jenis_penahanan_nama"; 
        }else if($col==9){
          return "mulai"; 
        }else if($col==9){
          return "lama_proses"; 
        }else{
          return "tanggal_pendaftaran";
        }
      }

  function get_perkara_by_alur_perkara(){
    try {
      $jenis_pengadilan = $this->session->userdata('jenis_pengadilan');
      $query = $this->db->query("SELECT perkara.alur_perkara_id, COUNT(*)AS jumlah_perkara 
        FROM perkara 
        LEFT OUTER JOIN perkara_penetapan ON perkara.perkara_id=perkara_penetapan.perkara_id 
        WHERE perkara.alur_perkara_id IN (SELECT id FROM alur_perkara WHERE jenis_pengadilan = '".$jenis_pengadilan."') 
        GROUP BY perkara.alur_perkara_id;
        "); 
      return $query;

    } catch (Exception $e) {
      log_message('error', $e);
    }

  }

  function get_status_perkara(){
    try {
      $jenis_pengadilan = $this->session->userdata('jenis_pengadilan');
      $query = $this->db->query("SELECT 
          ap.nama,
          pa.proses_id,
          pa.id as id_proses_alur_perkara,
          pa.proses_nama,
          pa.batas_waktu,
          pa.satuan,
          p.nama AS dari_proses_nama,
          pa.nama_tabel_dari,
          pa.nama_kolom_dari,
          pa.nama_tabel_ke,
          pa.nama_kolom_ke,
          pa.tahapan_id, pa.*
        FROM 
          proses_alur_perkara AS pa
        LEFT OUTER JOIN proses AS p ON pa.dari_proses_id=p.id  
        LEFT OUTER JOIN (SELECT * FROM alur_perkara WHERE jenis_pengadilan = '".$jenis_pengadilan."') AS ap ON ap.id=pa.alur_perkara_id
        WHERE pa.alur_perkara_id>0 AND 
        pa.batas_waktu >0 AND pa.reminder=1;"); 
      return $query;

    } catch (Exception $e) {
      log_message('error', $e);
    }

  }

  function get_status_perkara_all(){
    try {
      $jenis_pengadilan = $this->session->userdata('jenis_pengadilan');
      $sql=("SELECT 
          ap.nama,
          pa.proses_id,
          pa.id as id_proses_alur_perkara,
          pa.proses_nama,
          pa.batas_waktu,
          pa.satuan,
          p.nama AS dari_proses_nama,
          pa.nama_tabel_dari,
          pa.nama_kolom_dari,
          pa.nama_tabel_ke,
          pa.nama_kolom_ke,
          pa.tahapan_id, pa.*
        FROM 
          proses_alur_perkara AS pa
        LEFT OUTER JOIN proses AS p ON pa.dari_proses_id=p.id  
        LEFT OUTER JOIN alur_perkara AS ap ON ap.id=pa.alur_perkara_id
        WHERE ap.jenis_pengadilan = '".$jenis_pengadilan."' and pa.alur_perkara_id>0 AND 
        pa.batas_waktu >0 AND pa.reminder=1;
        "); 
      $query = $this->db->query($sql);
      return $query;

    } catch (Exception $e) {
      log_message('error', $e);
    }

  }

  function getTotal($query){
    try {
        $query = $this->db->query($query);
        $result = $query->result_array();
        if (!empty($result)){
            return $result[0];  
        }
    } catch (Exception $e) {
      log_message('error', $e);
    }
  }


  function getPenahananListing($userid='',$idalurperkara='',$col=2,$type='DESC',$begin=0,$key='',$wheres=''){
    $orderby = $this->parseOrderbyTahanan($col);
    $where = '';
    $wherez_like = '';
    if($idalurperkara>0){
      $where = "AND alur_perkara_id IN (".$idalurperkara.")";
    }else{
      $where = "";
    }

    if(!empty($key)){
      $wherez_like = ' AND (nomor_perkara LIKE "%'.$key.'%" 
                            OR nama LIKE "%'.$key.'%" 
                            OR tanggal_pendaftaran LIKE "%'.$key.'%" 
                            OR tempat_lahir LIKE "%'.$key.'%"
                            OR alamat LIKE "%'.$key.'%"
                            OR pekerjaan LIKE "%'.$key.'%"
                            OR jenis_penahanan_nama LIKE "%'.$key.'%") ';
    }else{
      $where_like =" ";
      if($idalurperkara>0){
        $wherez = " AND alur_perkara_id IN ('".$idalurperkara."')";
      }
    }
    try {
      $query = $this->db->query("SELECT SQL_CALC_FOUND_ROWS
                perkara_id, alur_perkara_id, tanggal_pendaftaran,jenis_perkara_id,
                nomor_perkara,pihak_id,  nama, tempat_lahir, tanggal_lahir, umur,
                jenis_kelamin, alamat, pekerjaan, status_penahanan_id,status_penahanan_nama,
                jenis_tahanan_id,jenis_tahanan_nama,jenis_penahanan_id,
                jenis_penahanan_nama, mulai, sampai, alur_perkara_aktif   
            FROM v_penahanan 
            WHERE ".$wheres.' '.$wherez_like."  ORDER BY ".$orderby." ".$type.",nomor_indeks DESC LIMIT ".$begin.",50");
      return $query;
      
    } catch (Exception $e) {
      log_message('error', $e);
    }
  }

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

    if(!empty($key)){
      $wherez_like = ' AND (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){
        $wherez = " AND alur_perkara_id IN (".$idalurperkara.")";
      }
    }
    try {
      $query = $this->db->query("SELECT SQL_CALC_FOUND_ROWS
            perkara_id, alur_perkara_id, nomor_perkara, nomor_indeks, tanggal_pendaftaran,
            jenis_perkara_nama, jenis_perkara_text, pihak1_text, pihak2_text, para_pihak, pihak_dipublikasikan,
            tahapan_terakhir_text, proses_terakhir_text,
            (IF(tanggal_putusan IS NULL, 
              DATEDIFF(CURRENT_DATE,tanggal_pendaftaran),
                DATEDIFF(tanggal_putusan,tanggal_pendaftaran))) AS lama_proses,
            panitera_pengganti_text 
            FROM v_perkara_list 
            WHERE ".$wheres.' '.$wherez_like." ORDER BY ".$orderby." ".$type.",nomor_indeks DESC LIMIT ".$begin.",50");
      return $query;
    } catch (Exception $e) {
      log_message('error', $e);
    }
  }

  function status_perkara_list($userid='',$idalurperkara='',$col=2,$type='DESC',$begin=0,$key='',$leftJoin=''){
    $orderby = $this->parseOrderby($col);
    $where = '';
    $wherez_like = '';
    if($idalurperkara>0){
      $where = "AND alur_perkara_id IN (".$idalurperkara.")";
    }else{
      $where = "";
    }

    if(!empty($key)){
      $wherez_like = ' AND (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){
        $wherez = " AND alur_perkara_id IN (".$idalurperkara.")";
      }
    }
    try {
      $query = $this->db->query("SELECT SQL_CALC_FOUND_ROWS
            perkara_id, alur_perkara_id, nomor_perkara, nomor_indeks, tanggal_pendaftaran,
            jenis_perkara_nama, jenis_perkara_text, pihak1_text, pihak2_text, para_pihak, pihak_dipublikasikan,
            tahapan_terakhir_text, proses_terakhir_text,
            (IF(tanggal_putusan IS NULL, 
              DATEDIFF(CURDATE(),tanggal_pendaftaran)-IFNULL(med.durasi_mediasi,0),
              DATEDIFF(tanggal_putusan,tanggal_pendaftaran)-IFNULL(med.durasi_mediasi,0))) AS lama_proses,
            panitera_pengganti_text 
            FROM v_perkara_list 
            LEFT JOIN (SELECT perkara_id as id_perkara, 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_list.perkara_id = med.id_perkara
            ".$leftJoin."
            WHERE b.no_perkara =  v_perkara_list.perkara_id ".$wherez_like." ORDER BY ".$orderby." ".$type.",nomor_indeks DESC LIMIT ".$begin.",50");
      return $query;
    } catch (Exception $e) {
      log_message('error', $e);
    }
  }


  function getDetilUser($userid){
    $query = $this->db->query("SELECT * FROM v_users WHERE userid=".$userid.";");
        return $query->row(0);
      }

  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 getDetilProsesAlurPerkara($idx){
    $query = $this->db->query("SELECT * FROM proses_alur_perkara WHERE id=".$idx.";");
        return $query->row(0);
      }

  
  function get_sisa_panjar(){
    try {
      $jenis_pengadilan = $this->session->userdata('jenis_pengadilan');
      $query = $this->db->query("SELECT v.`nama`,v.jenis_pengadilan, z.* , COUNT(IF(z.tahapan_id = 10, z.perkara_id, NULL)) AS 'pn',
          COUNT(IF(z.tahapan_id = 20, z.perkara_id, NULL)) AS 'banding',
          COUNT(IF(z.tahapan_id = 30, z.perkara_id, NULL)) AS 'kasasi',
          COUNT(IF(z.tahapan_id = 40, z.perkara_id, NULL)) AS 'pk'
          FROM 
          (SELECT a.* , (b.pemasukan-b.pengeluaran) AS 'sisa'
          FROM 
          v_putus_pdt AS a
          LEFT JOIN v_sum_perkara_biaya AS b ON a.`perkara_id`=b.`perkara_id` AND a.`tahapan_id`=b.`tahapan_id`
          WHERE a.`tanggal_putusan` IS NOT NULL
          HAVING sisa >0) AS z,
          alur_perkara AS v
          WHERE v.jenis_pengadilan = '".$jenis_pengadilan."' AND z.alur_perkara_id = v.`id`
          GROUP BY z.alur_perkara_id"); 
      return $query;
    } catch (Exception $e) {
      log_message('error', $e);
    }
  }

  function get_panjar_minus(){
    try {
      $jenis_pengadilan = $this->session->userdata('jenis_pengadilan');
      $query = $this->db->query("SELECT v.`nama`,v.jenis_pengadilan, z.* , COUNT(IF(z.tahapan_id = 10, z.perkara_id, NULL)) AS 'pn',
          COUNT(IF(z.tahapan_id = 20, z.perkara_id, NULL)) AS 'banding',
          COUNT(IF(z.tahapan_id = 30, z.perkara_id, NULL)) AS 'kasasi',
          COUNT(IF(z.tahapan_id = 40, z.perkara_id, NULL)) AS 'pk'
          FROM 
          (SELECT a.* , (b.pemasukan-b.pengeluaran) AS 'sisa'
          FROM 
          v_putus_pdt AS a
          LEFT JOIN v_sum_perkara_biaya AS b ON a.`perkara_id`=b.`perkara_id` AND a.`tahapan_id`=b.`tahapan_id`
          LEFT JOIN perkara AS p ON a.perkara_id = p.perkara_id
          WHERE a.`tanggal_putusan` IS NULL AND p.`prodeo` = 0 AND a.`tanggal_cabut` IS NULL AND a.`tanggal_tms` IS NULL
          HAVING sisa <= 100000) AS z,
          alur_perkara AS v
          WHERE v.jenis_pengadilan = '".$jenis_pengadilan."' AND  z.alur_perkara_id = v.`id`
          GROUP BY z.alur_perkara_id"); 
      return $query;

    } catch (Exception $e) {
      log_message('error', $e);
    }
  }
	function get_tanggal_sidang_terakhir_tanggal_putus(){
		try {
			$jenis_pengadilan = $this->session->userdata('jenis_pengadilan');
			$query = $this->db->query("SELECT COUNT(A.nomor_perkara) AS jumlah,
					A.jenis_perkara_nama,
					AP.id as id_proses_alur_perkara,
					FROM perkara A 
					LEFT JOIN perkara_penetapan F ON A.perkara_id=F.perkara_id 
					LEFT JOIN perkara_putusan B ON A.perkara_id=B.perkara_id 
					INNER JOIN perkara_jadwal_sidang C ON A.perkara_id=C.perkara_id 
					LEFT JOIN perkara_biaya D ON A.perkara_id=D.perkara_id
					LEFT JOIN perkara_verzet E ON A.perkara_id=E.perkara_id
						LEFT JOIN alur_perkara AP ON A.alur_perkara_id=AP.id
					WHERE AP.jenis_pengadilan = '".$jenis_pengadilan."' AND B.tanggal_putusan IS NOT NULL
					AND (C.urutan IN (SELECT MAX(urutan) FROM perkara_jadwal_sidang WHERE perkara_id=A.perkara_id) AND C.perkara_id=A.perkara_id)
					AND C.tanggal_sidang != B.tanggal_putusan AND C.ikrar_talak='T' AND D.uraian='Meterai' AND E.perkara_id IS NULL
					GROUP BY A.alur_perkara_id 
					ORDER BY alur_perkara_id DESC");
			return $query;
		} catch (Exception $e) {
			return FALSE;
		
			}
		}
} 
?>