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

class Antrian_dirput_m extends CI_Model{

  function get_antrian($batas =null,$offset=null,$key=null) {
        if(!empty($key)){
          $wheres = ' WHERE (c.nama LIKE "%'.$key.'%" 
                      OR b.nomor_perkara LIKE "%'.$key.'%") ';
        }else{
          $wheres =" ";
        }
        $query = $this->db->query("SELECT c.nama, b.nomor_perkara,a.* 
                                  FROM dirput_antrian AS a
                                  LEFT JOIN perkara AS b ON a.perkara_id=b.perkara_id
                                  LEFT JOIN alur_perkara AS c ON b.alur_perkara_id=c.id ".$wheres."
                                  ORDER BY a.status ASC LIMIT  ".$batas." OFFSET  ".$offset.";"); 
        if($batas != null){
           $this->db->limit($batas,$offset);
        }
        if ($key != null) {
           $this->db->or_like($key);
        }

        $result = $query->result();
        if ($query->num_rows() > 0) {
            return $result;
        }
    }

  function get_total_antrian($key=null){
    if(!empty($key)){
          $wheres = ' WHERE (c.nama LIKE "%'.$key.'%" 
                      OR b.nomor_perkara LIKE "%'.$key.'%") ';
        }else{
          $wheres =" ";
    }
    $q = $this->db->query("SELECT c.nama, b.nomor_perkara,a.* 
                          FROM dirput_antrian AS a
                          LEFT JOIN perkara AS b ON a.perkara_id=b.perkara_id
                          LEFT JOIN alur_perkara AS c ON b.alur_perkara_id=c.id ".$wheres." ");
    return $q;
    }

  function  count_barang_search($orlike) {
      $this->db->or_like($orlike);
      $query = $this->db->get($this->tabel);
      return $query->num_rows();
  }

  function get_nomor_perkara($batas =null,$offset=null,$key=null) {
        if(!empty($key)){
          $wheres = ' WHERE b.nomor_perkara LIKE "%'.$key.'%" ';
        }else{
          $wheres =" ";
        }
        $query = $this->db->query("SELECT b.nomor_perkara
                                  FROM dirput_antrian AS a
                                  LEFT JOIN perkara AS b ON a.perkara_id=b.perkara_id
                                  LEFT JOIN alur_perkara AS c ON b.alur_perkara_id=c.id ".$wheres."
                                  ORDER BY a.status ASC LIMIT  ".$batas." OFFSET  ".$offset.";"); 
        if($batas != null){
           $this->db->limit($batas,$offset);
        }
        if ($key != null) {
           $this->db->or_like($key);
        }

        $result = $query->result();
        if ($query->num_rows() > 0) {
            return $result;
        }
    }

function get_data_minutasi(){
    try {
      $query = $this->db->query("SELECT  SUM(CASE
              WHEN a.tanggal_putusan IS NOT NULL THEN 1 ELSE 0 END) AS jumlah_put,
              SUM(CASE WHEN a.tanggal_minutasi IS NOT NULL THEN 1 ELSE 0 END) AS jumlah_minutasi,
              SUM(CASE WHEN a.tanggal_minutasi IS NULL THEN 1 ELSE 0 END) AS jumlah_belum_minutasi,
              SUM(CASE WHEN a.amar_putusan_dok IS NOT NULL THEN 1 ELSE 0 END) AS jumlah_edoc,
              SUM(CASE WHEN a.amar_putusan_dok IS NULL THEN 1 ELSE 0 END) AS jum_non_edoc
            FROM perkara_putusan AS a 
            LEFT JOIN perkara AS b ON a.perkara_id=b.perkara_id
            WHERE b.alur_perkara_id NOT IN (112,113,114);");
      return $query;
    } catch (Exception $e) {
      log_message('error', $e);
    }
  }

function get_perkara_putusan($batas =null,$offset=null,$key=null,$mainWhere=null){
        if(!empty($key)){
          $wheres = ' AND (c.nama LIKE "%'.$key.'%" 
                      OR b.nomor_perkara LIKE "%'.$key.'%"
                      OR c.nama LIKE "%'.$key.'%"
                      OR b.jenis_perkara_nama LIKE "%'.$key.'%"
                      OR d.majelis_hakim_text LIKE "%'.$key.'%"
                      OR d.panitera_pengganti_text LIKE "%'.$key.'%") ';
        }else{
          $wheres =" ";
        }
        $query = $this->db->query("SELECT CONCAT(b.nomor_perkara,'</br>',c.nama,'</br>',b.jenis_perkara_nama) AS nomor_perkara,
              d.penetapan_majelis_hakim,
              d.majelis_hakim_text,
              d.penetapan_panitera_pengganti,
              d.panitera_pengganti_text,
              a.tanggal_putusan,a.tanggal_minutasi,
              a.amar_putusan_dok
            FROM perkara_putusan AS a
            LEFT JOIN perkara AS b ON a.perkara_id=b.perkara_id
            LEFT JOIN alur_perkara AS c ON b.alur_perkara_id=c.id
            LEFT JOIN perkara_penetapan AS d ON a.perkara_id=d.perkara_id
            WHERE b.alur_perkara_id NOT IN (112,113,114) ".$mainWhere."
            ".$wheres." ORDER BY a.tanggal_putusan DESC LIMIT  ".$batas." OFFSET  ".$offset.";");
        if($batas != null){
           $this->db->limit($batas,$offset);
        }
        if ($key != null) {
           $this->db->or_like($key);
        }
        $result = $query->result();
        if ($query->num_rows() > 0) {
            return $result;
        }
  }

  function get_total_perkara_putusan($key=null,$mainWhere){
    if(!empty($key)){
          $wheres = ' AND (c.nama LIKE "%'.$key.'%" 
                      OR b.nomor_perkara LIKE "%'.$key.'%"
                      OR c.nama LIKE "%'.$key.'%"
                      OR b.jenis_perkara_nama LIKE "%'.$key.'%"
                      OR d.majelis_hakim_text LIKE "%'.$key.'%"
                      OR d.panitera_pengganti_text LIKE "%'.$key.'%") ';
        }else{
          $wheres =" ";
    }
    $q = $this->db->query("SELECT   CONCAT(b.nomor_perkara,'</br>',c.nama,'</br>',b.jenis_perkara_nama) AS nomor_perkara
            FROM perkara_putusan AS a
            LEFT JOIN perkara AS b ON a.perkara_id=b.perkara_id
            LEFT JOIN alur_perkara AS c ON b.alur_perkara_id=c.id
            LEFT JOIN perkara_penetapan AS d ON a.perkara_id=d.perkara_id
            WHERE b.alur_perkara_id NOT IN (112,113,114) ".$mainWhere." ".$wheres." ");
    return $q;
    }

   function get_jumlah_antrian(){
       $query = "select (select count(status) from dirput_antrian where status = 0) as masuk,
                 (select count(status) from dirput_antrian where status = 1) as sukses,
                 (select count(status) from dirput_antrian where status = -1) as error;";
       return $this->db->query($query)->result_array();
   }

   function getPerkaraDirput($batas =null,$offset=null,$key=null,$mainWhere=null){
        if(!empty($key)){
          $wheres = ' AND (c.nama LIKE "%'.$key.'%" 
                      OR b.nomor_perkara LIKE "%'.$key.'%"
                      OR c.nama LIKE "%'.$key.'%"
                      OR b.jenis_perkara_nama LIKE "%'.$key.'%"
                      OR d.majelis_hakim_text LIKE "%'.$key.'%"
                      OR d.panitera_pengganti_text LIKE "%'.$key.'%") ';
        }else{
          $wheres =" ";
        }
        $query = $this->db->query("SELECT CONCAT(b.nomor_perkara,'</br>',c.nama,'</br>',b.jenis_perkara_nama) AS nomor_perkara,dp.putusan_id,
              d.penetapan_majelis_hakim,
              d.majelis_hakim_text,
              d.penetapan_panitera_pengganti,
              d.panitera_pengganti_text,
              a.tanggal_putusan,a.tanggal_minutasi,
              a.amar_putusan_dok
            FROM dirput_perkara AS dp
            LEFT JOIN perkara_putusan AS a ON dp.perkara_id=a.perkara_id
            LEFT JOIN perkara AS b ON a.perkara_id=b.perkara_id
            LEFT JOIN alur_perkara AS c ON b.alur_perkara_id=c.id
            LEFT JOIN perkara_penetapan AS d ON a.perkara_id=d.perkara_id
            WHERE b.alur_perkara_id NOT IN (112,113,114) 
            ".$wheres." ORDER BY a.tanggal_putusan DESC LIMIT  ".$batas." OFFSET  ".$offset.";");
        if($batas != null){
           $this->db->limit($batas,$offset);
        }
        if ($key != null) {
           $this->db->or_like($key);
        }
        $result = $query->result();
        if ($query->num_rows() > 0) {
            return $result;
        }
  }

  function getTotalPerkaraDirput($key=null){
    if(!empty($key)){
          $wheres = ' AND (c.nama LIKE "%'.$key.'%" 
                      OR b.nomor_perkara LIKE "%'.$key.'%"
                      OR c.nama LIKE "%'.$key.'%"
                      OR b.jenis_perkara_nama LIKE "%'.$key.'%"
                      OR d.majelis_hakim_text LIKE "%'.$key.'%"
                      OR d.panitera_pengganti_text LIKE "%'.$key.'%") ';
        }else{
          $wheres =" ";
    }
    $q = $this->db->query("SELECT dp.putusan_id
            FROM dirput_perkara AS dp
            LEFT JOIN perkara_putusan AS a ON dp.perkara_id=a.perkara_id
            LEFT JOIN perkara AS b ON a.perkara_id=b.perkara_id
            LEFT JOIN alur_perkara AS c ON b.alur_perkara_id=c.id
            LEFT JOIN perkara_penetapan AS d ON a.perkara_id=d.perkara_id
            WHERE b.alur_perkara_id NOT IN (112,113,114)  ".$wheres." ");
    return $q;
    }

  function getDirputAmbigu($batas =null,$offset=null,$key=null){
        if(!empty($key)){
          $wheres = ' AND nomor_perkara LIKE "%'.$key.'%" ';
        }else{
          $wheres =" ";
        }
        $query = $this->db->query("SELECT * FROM dirput_perkara WHERE perkara_id IS NULL
            ".$wheres." LIMIT  ".$batas." OFFSET  ".$offset.";");
        if($batas != null){
           $this->db->limit($batas,$offset);
        }
        if ($key != null) {
           $this->db->or_like($key);
        }
        $result = $query->result();
        if ($query->num_rows() > 0) {
            return $result;
        }
  }

  function getTotalDirputAmbigu($key=null){
    if(!empty($key)){
          $wheres = ' AND nomor_perkara LIKE "%'.$key.'%"  ';
        }else{
          $wheres =" ";
    }
    $q = $this->db->query("SELECT * FROM dirput_perkara WHERE perkara_id IS NULL ".$wheres." ");
    return $q;
    }

  function get_antrian_upaya_hukum(){
    try {
      $query = $this->db->query("SELECT   SUM(CASE WHEN a.pengajuan_id=1 THEN 1 ELSE 0 END)AS putusan_tk1_upload,
              SUM(CASE WHEN a.pengajuan_id=1 AND a.keterangan IS NOT NULL THEN 1 ELSE 0 END)AS putusan_tk1_berhasil_upload,
              SUM(CASE WHEN a.pengajuan_id=2 THEN 1 ELSE 0 END)AS putusan_bdg_upload,
              SUM(CASE WHEN a.pengajuan_id=2 AND a.keterangan IS NOT NULL THEN 1 ELSE 0 END)AS putusan_bdg_berhasil_upload,
              SUM(CASE WHEN a.pengajuan_id=3 THEN 1 ELSE 0 END)AS putusan_kasasi_upload,
              SUM(CASE WHEN a.pengajuan_id=3 AND a.keterangan IS NOT NULL THEN 1 ELSE 0 END)AS putusan_kasasi_berhasil_upload,
              SUM(CASE WHEN a.pengajuan_id=4 THEN 1 ELSE 0 END)AS putusan_pk_upload,
              SUM(CASE WHEN a.pengajuan_id=4 AND a.keterangan IS NOT NULL THEN 1 ELSE 0 END)AS putusan_pk_berhasil_upload
            FROM 
            dirput_dokumen AS a
            LEFT JOIN perkara AS b ON a.perkara_id=b.perkara_id;");
      return $query;
    } catch (Exception $e) {
      log_message('error', $e);
    }
  }

  function getUpayaHukumList($batas =null,$offset=null,$key=null,$mainWhere){
        if(!empty($key)){
          $wheres = ' AND (c.nama LIKE "%'.$key.'%" 
                      OR b.nomor_perkara LIKE "%'.$key.'%"
                      OR c.nama LIKE "%'.$key.'%"
                      OR b.jenis_perkara_nama LIKE "%'.$key.'%"
                      OR a.filename LIKE "%'.$key.'%") ';
        }else{
          $wheres =" ";
        }
        $sql = "SELECT b.nomor_perkara,c.nama,b.jenis_perkara_nama,d.keterangan_file,a.*
            FROM dirput_dokumen AS a
            LEFT JOIN perkara AS b ON a.perkara_id=b.perkara_id 
            LEFT JOIN alur_perkara AS c ON b.alur_perkara_id=c.id
            LEFT JOIN dirput_dokumen_ref AS d ON a.dokumen_ref_id = d.dokumen_ref_id ".$mainWhere.$wheres." LIMIT  ".$batas." OFFSET  ".$offset." ";
        
        $query = $this->db->query($sql);
        if($batas != null){
           $this->db->limit($batas,$offset);
        }
        if ($key != null) {
           $this->db->or_like($key);
        }
        $result = $query->result();
        if ($query->num_rows() > 0) {
            return $result;
        }
  }

  function getTotalUpayaHukumList($key=null){
    if(!empty($key)){
          $wheres = ' WHERE (c.nama LIKE "%'.$key.'%" 
                      OR b.nomor_perkara LIKE "%'.$key.'%"
                      OR c.nama LIKE "%'.$key.'%"
                      OR b.jenis_perkara_nama LIKE "%'.$key.'%"
                      OR a.filename LIKE "%'.$key.'%") ';
        }else{
          $wheres =" ";
    }
    $q = $this->db->query("SELECT count(*)
          FROM dirput_dokumen AS a
          LEFT JOIN perkara AS b ON a.perkara_id=b.perkara_id 
          LEFT JOIN alur_perkara AS c ON b.alur_perkara_id=c.id
          LEFT JOIN dirput_dokumen_ref AS d ON a.dokumen_ref_id = d.dokumen_ref_id ".$wheres." ");
    return $q;
    }

} 