<?php

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

class M_analis extends CI_Model {

    protected $idGroup = null;
    protected $iduser = null;

    public function __construct() {
        parent::__construct();
        $this->idGroup = $this->session->userdata("r_idGroup");
        $this->idUser = $this->session->userdata('r_userId');
    }

    function get_uji_analis($table, $parameter_uji, $data = null){
      $this->db->trans_begin();

      $query = $this->db->query("SELECT ds.id_sampel FROM data_sampel ds
        LEFT JOIN permintaan_uji pu ON pu.id_uji = ds.id_uji
        WHERE ds.jenis_pengujian LIKE ? AND ds.is_active = '1' AND pu.id_status = 6", "%".$parameter_uji."|%")->result_array();

      foreach ($query as $r) {
        $cek = $this->db->query("SELECT id_sampel FROM ".$table." WHERE id_sampel = ? AND is_active = '1'", $r['id_sampel'])->row_array()['id_sampel'];

        if(!isset($cek)){
          $this->db->query("INSERT INTO ".$table." (id_sampel) VALUES (?)", $r['id_sampel']);
        }
      }

      if($this->db->trans_status() == false){
        $this->db->trans_rollback();
      }else{
        $this->db->trans_commit();
      }

      $param = array();

      $query ="SELECT t1.* FROM ".$table." t1
        LEFT JOIN data_sampel ds ON ds.id_sampel = t1.id_sampel
        WHERE t1.is_active = '1'
      ";

      //var_dump($data);

      if(empty($data['method']) || !empty($data['method']) && $data['method'] == 'edit'){
          $query .= " AND nomor_batch is null";
      }

      if(!empty($data['method']) && in_array($data['method'], array('edit','batch_sampel','kontrol_sampel'))) {
        if($data['id_sampel']){
          $query .=" AND t1.id_sampel IN ?";
          $param[] = $data['id_sampel'];
        }
        if($data['id_kadar']){
          $query .=" AND t1.id_kadar IN ?";
          $param[] = $data['id_kadar'];
        }
        
        if(!in_array($data['method'], array('edit', 'batch_sampel'))){
          //$query .=" || t1.id_sampel is null AND t1.nomor_batch = ? ";
          //$param[] = $data['nomor_batch'];

          //if($data['kontrol_sampel']){
          //  $query .= " AND t1.kontrol_sampel IN ?";
          //  $param[] = $data['kontrol_sampel']; 
         // }
        }
      }else{
        $query .=" AND t1.id_sampel is not null";
      }

      if($this->input->post('filter_cari')){
        $data = $this->input->post();

        if($table != 'fosfor'){
            if(isset($data['mulai_uji']) && $data['mulai_uji'] != ''){
              $query .=" AND t1.mulai_uji BETWEEN ? AND ? ";
              $param[] = date('Y-m-d', strtotime($data['mulai_uji'])).' 00:00:00';
              $param[] = date('Y-m-d', strtotime($data['mulai_uji'])).' 23:59:59';
            }

            if(isset($data['persetujuan']) && $data['persetujuan'] != ''){
              $query .=" AND t1.persetujuan BETWEEN ? AND ? ";
              $param[] = date('Y-m-d', strtotime($data['persetujuan'])).' 00:00:00';
              $param[] = date('Y-m-d', strtotime($data['persetujuan'])).' 23:59:59';
            }
        }

        if(isset($data['id_sampel']) && $data['id_sampel'] != ''){
          $query .=" AND t1.id_sampel = ? ";
          $param[] = $data['id_sampel'];
        }

        if(isset($data['gol_jenis_sampel']) && $data['gol_jenis_sampel'] != ''){
          $query .=" AND ds.id_keterangan = ? ";
          $param[] = $data['gol_jenis_sampel'];
        }

      }

      $query .= " ORDER BY t1.kontrol_sampel, t1.id_kadar DESC";
      
      $result = $this->db->query($query, $param)->result_array();

      //print('<pre>'.print_r($result, true).'</pre>'); die();
      // $c = count($result);
      // if(!empty($data['method']) && in_array($data['method'], array('edit'))) {
      //   $k_panel = array_keys($result[0]);
      //   foreach($k_panel as $k){
      //     $result[$c][$k] = null;
      //   }
      //   $result = array_reverse($result);
      // }

      //echo $this->db->last_query(); die();
      
      
      return $result;
    }

    function save_analisis($table, $field_table, $param, $id=null, $field_id, $kontrol_sampel2=null, $field_table2=null){
      $this->db->trans_begin();

      //var_dump($field_table2); die();

      if(is_null($id) || $id == ''){
        $cek_k_sampel = $this->db->query("SELECT id_kadar FROM ".$table." WHERE id_sampel IS NULL AND nomor_batch = ? AND kontrol_sampel = ? AND is_active = '1'", array($param['nomor_batch'], $kontrol_sampel2))->row_array()['id_kadar'];

        if($cek_k_sampel){
          $query = "UPDATE ".$table." SET ".$field_table2." WHERE id_sampel IS NULL AND nomor_batch = ? AND kontrol_sampel = ? AND is_active = '1'";
          $param2 = $param;
          $param = array();
          $param = $param2;
          $param[] = $param2['nomor_batch'];
          $param[] = $kontrol_sampel2;
          
        }else{
          $query = "INSERT INTO ".$table."(".$field_table.") VALUES(".$field_id.")";
        }
        
      }else{
        $param[] = $id;
        $query = "UPDATE ".$table." SET ".$field_table." WHERE is_active = '1' AND ".$field_id;
      }
      $this->db->query($query, $param);

      //echo $this->db->last_query();
      //die();

      //echo $this->db->last_query(); die();

      if(!is_null($id) && $id != '' && !in_array($table, array('kadar_pkd'))){
        $cek_sampel_lhu = $this->db->query("SELECT id_sampel FROM lhu WHERE id_sampel = ?", $id)->row_array()['id_sampel'];
        $tfield = str_replace('kadar_', '', $table);

        $param2 = array();
        if(isset($cek_sampel_lhu)){
          $qlhu = "UPDATE lhu SET ".$tfield." = ?, mulai_uji_".$tfield." = ?, selesai_uji_".$tfield." = ?, last_modified_user = ?, last_modified_time = NOW() WHERE id_sampel = ?";
        }else{
          $qlhu = "INSERT INTO lhu (".$tfield.", mulai_uji_".$tfield.", selesai_uji_".$tfield.", last_modified_user, id_sampel, last_modified_time) VALUES
          (?,?,?,?,?,NOW())";
        }

        if($table == 'kadar_lemak_kasar') $table = 'kadar_lk';
        if($table == 'kadar_protein_kasar') $table = 'kadar_pk';
        if($table == 'kadar_serat_kasar') $table = 'kadar_sk';
        if($table == 'kadar_fosfor') $table = 'kadar_p';
        if($table == 'kadar_kalsium') $table = 'kadar_ca';

        $param2[] = $param[$table];
        if($table == 'kadar_p'){
          $param2[] = date('Y-m-d H:i:s');
          $param2[] = date('Y-m-d H:i:s');
        }else{
          $param2[] = $param['mulai_uji'];
          $param2[] = $param['selesai_uji'];
        }

        $param2[] = $this->idUser;
        $param2[] = $id;

        $this->db->query($qlhu, $param2);
      }

      if($this->db->trans_status() == false){
        $this->db->trans_rollback();
        return false;
      }else{
        $this->db->trans_commit();
        return true;
      }
    }

    function hapus_sampel_batch($table, $data){
      $this->db->trans_begin();
      foreach($data['id_sampel'] as $r){
        $param = array();
        $query = "SELECT * FROM ".$table." WHERE id_sampel = ?";
        $param[] = $r;

        $result = $this->db->query($query, $param)->row_array();
        $array_result = array_keys($result);

        $param2 = array();
        $query2 = "UPDATE ".$table." SET ";
        foreach($array_result as $ar){
          if(!in_array($ar, array('id_kadar', 'id_sampel', 'last_modified_time', 'last_modified_user', 'is_active'))){
            $query2 .= $ar." = null, ";
          }
        }
        $query2 = substr($query2, 0, -2);
        $query2 .= " WHERE id_sampel = ?";
        $param2[] = $r;

        $this->db->query($query2, $param2);
      }

      if($this->db->trans_status() == false){
        $this->db->trans_rollback();
        return false;
      }else{
        $this->db->trans_commit();
        return true;
      }
      
    }

    function hapus_batch($table, $data){
      $jenis_uji = $table;
      $table = 'kadar_'.$jenis_uji;
      $table_deret = 'deret_'.$jenis_uji;
      $this->db->trans_begin();
      $data_batch = $this->db->query("SELECT * FROM ".$table." WHERE nomor_batch = ?", $data['nomor_batch'])->result_array();

      //print("<pre>".print_r($data_batch, true)."</pre>"); 
      
      foreach($data_batch as $d){
        if(is_null($d['id_sampel']) || $d['id_sampel'] == ''){
          $this->db->query("DELETE FROM ".$table." WHERE nomor_batch = ? AND (id_sampel IS NULL || id_sampel = '' )", $data['nomor_batch']);
          $this->db->query("DELETE FROM ".$table_deret." WHERE nomor_batch = ?", $data['nomor_batch']);
        }else{
          $param2 = array();
          $query2 = "UPDATE ".$table." SET ";
          $array_batch = array_keys($d);
          foreach($array_batch as $ar){
            if(!in_array($ar, array('id_kadar', 'id_sampel', 'last_modified_time', 'last_modified_user', 'is_active', 'range'))) {
              $query2 .= $ar." = null, ";
            }
          }
          $query2 = substr($query2, 0, -2);
          $query2 .= " WHERE id_sampel = ?";
          $param2[] = $d['id_sampel'];

          $this->db->query($query2, $param2);
        }
      }

      if($this->db->trans_status() == false) {
        $this->db->trans_rollback();
        return false;
      }else{
        $this->db->trans_commit();
        return true;
      }
    }

    function get_batch($data = null) {
      if(!is_null($data)){
        $param = array();
        if(empty($data['method'])){
          $select = 'SELECT DISTINCT tb.nomor_batch ';
        }else {
          if($data['method'] == 'kontrol_sampel') $select = 'SELECT DISTINCT tb.kontrol_sampel, tb.nomor_batch ';
          else $select = 'SELECT tb.*';
        }

        $query = $select." FROM ".$data['table']." tb
          WHERE tb.is_active = '1' AND tb.nomor_batch LIKE ?
        ";
        $param[] = '%'.$data['kode_batch'].'%';

        if(!empty($data['nomor_batch'])){
          $query .= ' AND tb.nomor_batch = ?';
          $param[] = $data['nomor_batch'];
        }

        if(!empty($data['kontrol_sampel'])){
          $query .= ' AND tb.kontrol_sampel = ?';
          $param[] = $data['kontrol_sampel'];
        }

        $query .= " ORDER BY tb.kontrol_sampel, tb.id_kadar DESC";

        $query = $this->db->query($query, $param);

        //echo $this->db->last_query(); die();

        $result = $query->result_array();

        return $result;
      }
    }

    function get_user_analis($id_user){
      $param = array();
      $query = "SELECT u.username, p.nama FROM user u
        LEFT JOIN personil p ON p.id_user = u.id_user AND p.is_active = '1'
        WHERE 1
      ";
      $query .= " AND u.id_user = ? AND u.is_active = '1'";

      $param[] = $id_user;
      $result = $this->db->query($query, $param);

      return $result->row_array();
    }

    function cek_batch($table, $batch){
      $query = $this->db->query("SELECT COUNT(DISTINCT nomor_batch) as total_batch FROM ".$table." WHERE nomor_batch LIKE ?", "%".$batch."%")->row_array()['total_batch'];
      return intval((int)$query + 1);
    }

  }
