<?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->id_group = $this->session->userdata("id_group");
        $this->id_user = $this->session->userdata('id_user');
    }

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

        $result = $this->db->query($query, $param)->row_array();
        $array_result = array_keys($result);
        if($result['id_sampel'] != 0 && $result['id_sampel'] != '' && !is_null($result['id_sampel'])){
            $param2 = array();
            $query2 = "UPDATE ".$table." SET ";
            foreach($array_result as $ar){
                if(!in_array($ar, array('id', 'id_sampel', 'last_modified_time', 'last_modified_user', 'is_active'))){
                    $query2 .= $ar." = null, ";
                }
            }
            $query2 = substr($query2, 0, -2);
            $query2 .= " WHERE id = ?";
            $param2[] = $r;

            $this->db->query($query2, $param2);
        }else {
            $this->db->query("DELETE FROM $table WHERE id = ?", $result['id']);
        }
        
      }

      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();
      
      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', '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;
      }
    }

    //--------------------------------
    //    Model baru
    //--------------------------------
    function get_analis($jenis_uji, $id_uji='', $id_param = [])
    {   
        $this->db->select('ds.id');
        $this->db->like('ds.jenis_pengujian', '"'.$id_uji.'"');
        $this->db->where(['ds.is_active'=>'1', 'pu.is_active'=>'1', 'pu.id_status'=>6]);
        if(in_array($id_uji, [1,2,3,4,5])){
            $this->db->or_like('ds.jenis_pengujian', '"15"');
            $this->db->where(['ds.is_active'=>'1', 'pu.is_active'=>'1', 'pu.id_status'=>6]);
        }
        $this->db->join('permintaan_uji pu', 'pu.id = ds.id_uji', 'left');
        if(in_array($jenis_uji, ['fosfor', 'kalsium', 'ca'])){
            $this->db->where('ka.kontrol_sampel is NOT NULL', NULL, FALSE);
            $this->db->join('kadar_abu ka', 'ds.id=ka.id_sampel');
        }
        $sampel = $this->db->get('data_sampel ds')->result_array();
        
        foreach($sampel as $s){
            $cek_sampel = $this->db->get_where('kadar_'.$jenis_uji, ['id_sampel'=>$s['id'], 'is_active'=>'1'])->num_rows();
            if($cek_sampel < 1) {
                $this->db->insert('kadar_'.$jenis_uji, ['id_sampel'=>$s['id']]);
            }
        }

        $param = [];
        $param['tb.nomor_batch =']  = null;
        $param['tb.id_sampel !=']   = 0;
        $param['ds.is_active']      = '1';
        $param['tb.is_active']      = '1';
        $this->db->select('tb.*, ds.nomor_sampel');
        $this->db->join('data_sampel ds', 'ds.id = tb.id_sampel', 'left');

        if(in_array($jenis_uji, ['fosfor', 'kalsium', 'ca'])){
            $this->db->join('kadar_abu ka', 'ka.id_sampel = tb.id_sampel');
            $this->db->select('ka.kontrol_sampel as abu_ks, ka.berat_sampel as abu_bs, ka.kode_krusebel as abu_kb');
        }
        $this->db->where($param);
        if($id_param){
            $this->db->where_in('tb.id', $id_param);
        }
        $this->db->order_by('ds.nomor_sampel', 'asc');
        $query = $this->db->get('kadar_'.$jenis_uji.' tb');
        
        return $query;
    }

    function get_abu_ks($jenis_uji, $id_param)
    {
        $this->db->select('id_sampel');
        $this->db->where_in('id', $id_param);
        $cek_sampel = $this->db->get('kadar_'.$jenis_uji)->result_array();

        $id_sampel = [];
        foreach($cek_sampel as $c){
            $id_sampel[] = $c['id_sampel'];
        }

        $this->db->select('nomor_batch');
        $this->db->where_in('id_sampel', $id_sampel);
        $cek_abu_batch = $this->db->get('kadar_abu')->result_array();

        $batch_abu = [];
        foreach($cek_abu_batch as $cb){
            $batch_abu[] = $cb['nomor_batch'];
        }
        
        $this->db->select('kontrol_sampel, berat_sampel, kode_krusebel, c_range as abu_range');
        $this->db->where('id_sampel', 0);
        $this->db->where_in('nomor_batch', $batch_abu);
        $cek_abu_ks = $this->db->get('kadar_abu')->result_array();

        $desc_table = $this->db->query('desc kadar_'.$jenis_uji)->result_array();
        $field_table = [];
        foreach($desc_table as $d){
            $field_table[] = $d['Field'];
        }

        $result = [];
        foreach($cek_abu_ks as $ca){
            $ftable = [];
            foreach($field_table as $ft){
                $ftable[$ft] = null;
            }
            $ftable['id'] = 0;
            $ftable['kontrol_sampel'] = $ca['kontrol_sampel'];
            $ftable['c_range'] = $ca['abu_range'];
            $ftable['abu_ks'] = $ca['kontrol_sampel'];
            $ftable['abu_bs'] = $ca['berat_sampel'];
            $ftable['abu_kb'] = $ca['kode_krusebel'];
            $result[] = $ftable;
        }

        return $result;
    }

    function get_edit_analis($jenis_uji, $nomor_batch, $kontrol_sampel)
    {
        $param = [];
        $param['nomor_batch']    = $nomor_batch;
        $param['kontrol_sampel'] = $kontrol_sampel;
        $this->db->select('tb.*, ds.nomor_sampel');
        $this->db->join('data_sampel ds', 'ds.id = tb.id_sampel', 'left');
        $this->db->where($param);
        $this->db->order_by('tb.id', 'desc');
        $query = $this->db->get('kadar_'.$jenis_uji.' tb');
        return $query;
    }

    function get_data_batch($jenis_uji, $tgl_kode='')
    {   
        $this->db->where('is_active', '1');
        if($tgl_kode != ''){
            $this->db->like('nomor_batch', $tgl_kode);
        }
        $query = $this->db->get_where('kadar_'.$jenis_uji);
        return $query;
    }

    function save_batch_uji($jenis_uji, $data)
    {
        if($data['id_sampel']){
            $this->db->where('id_sampel', $data['id_sampel']);
            $this->db->update('kadar_'.$jenis_uji, $data);

            $cek_lhu = $this->db->get_where('lhu', ['id_sampel'=>$data['id_sampel']])->num_rows();

            switch ($jenis_uji) {
                case 'pkd':
                    $param_uji = 'protein_kasar';
                    break;
                case 'fosfor':
                    $param_uji = 'kadar_p';
                    break;
                
                default:
                    $param_uji = 'kadar_'.$jenis_uji;
                    break;
            }

            $data_lhu = [
              $jenis_uji                => $param_uji,
              'mulai_uji_'.$jenis_uji   => $data['mulai_uji'],
              'selesai_uji_'.$jenis_uji => $data['selesai_uji'],
            ];
            if($cek_lhu < 1){
                $data_lhu['id_sampel'] = $data['id_sampel'];
                $this->db->insert('lhu', $data_lhu);
            }else {
                $this->db->where('id_sampel', $data['id_sampel']);
                $this->db->update('lhu', $data_lhu);
            }

            $tgl_mulai = $this->update_tgl_mulai($data['id_sampel'], $data['mulai_uji']);
            $tgl_selesai = $this->update_tgl_selesai($data['id_sampel'], $data['selesai_uji']);
        }else {
            $this->db->insert('kadar_'.$jenis_uji, $data);
        }
        return 1;
    }

    function update_tgl_mulai($id_sampel = '', $tgl = ''){
        if($tgl != ''){
            $this->db->select('pu.tgl_mulai_uji, ds.id_uji');
            $this->db->join('permintaan_uji pu', 'pu.id = ds.id_uji');
            $cek_tgl = $this->db->get_where('data_sampel ds', ['ds.id'=>$id_sampel])->row_array();

            if(is_null($cek_tgl['tgl_mulai_uji']) || $cek_tgl['tgl_mulai_uji'] == '' || $cek_tgl['tgl_mulai_uji']>$tgl){
                $this->db->where('id', $cek_tgl['id_uji']);
                $this->db->update('permintaan_uji', ['tgl_mulai_uji'=>$tgl]);
            }
        }
        return 1;
    }

    function update_tgl_selesai($id_sampel = '', $tgl = ''){
        if($tgl != ''){
            $this->db->select('pu.tgl_mulai_uji, ds.id_uji');
            $this->db->join('permintaan_uji pu', 'pu.id = ds.id_uji');
            $cek_tgl = $this->db->get_where('data_sampel ds', ['ds.id'=>$id_sampel])->row_array();

            $this->db->where('id', $cek_tgl['id_uji']);
            $this->db->update('permintaan_uji', ['tgl_selesai_uji'=>$tgl]);
        }
        return 1;
    }

    function update_batch_uji($jenis_uji, $data, $id)
    {
        $this->db->where(['id'=>$id]);
        $this->db->update('kadar_'.$jenis_uji, $data);

        if($data['id_sampel'] != 0){
            $param = [];
            $param[$jenis_uji] = ($jenis_uji == 'pkd') ? $data['protein_kasar'] : $data['kadar_'.$jenis_uji];
            $param['mulai_uji_'.$jenis_uji]   = $data['mulai_uji'];
            $param['selesai_uji_'.$jenis_uji] = $data['selesai_uji'];
            $this->db->where(['id_sampel'=>$data['id_sampel']]);
            $this->db->update('lhu', $param);
        }
        return $id;
    }

    function get_batch_list($jenis_uji)
    { 
      $this->db->group_by('nomor_batch');
      $query = $this->db->get_where('kadar_'.$jenis_uji, ['is_active'=>'1', 'nomor_batch !='=>null]);
      return $query;
    }

    function get_kontrol_sampel_list($jenis_uji, $nomor_batch)
    {
        $this->db->group_by('kontrol_sampel');
        $query = $this->db->get_where('kadar_'.$jenis_uji, ['is_active'=>'1', 'nomor_batch'=>$nomor_batch, 'kontrol_sampel !='=>null]);
        return $query;
    }

    function get_batch_sampel_list($jenis_uji, $nomor_batch, $kontrol_sampel)
    {
        $param = [
            'tb.is_active'      =>'1', 
            'tb.nomor_batch'    =>$nomor_batch, 
            'tb.kontrol_sampel' =>$kontrol_sampel
        ];
        $this->db->select('tb.*, ds.nomor_sampel');
        $this->db->join('data_sampel ds', "ds.id = tb.id_sampel AND ds.is_active = '1'", 'left');
        $this->db->where($param);
        $this->db->order_by('id_sampel', 'asc');
        $query = $this->db->get_where('kadar_'.$jenis_uji.' tb');

        return $query;
    }

    function save_deret_uji($jenis_uji, $data)
    {
        $this->db->insert('deret_'.$jenis_uji, $data);
    }

    function get_nama_analis($id)
    {   
        $this->db->select('u.username, p.nama');
        $this->db->join('personil p', 'p.id_user = u.id', 'left');
        $query = $this->db->get_where('user u', ['u.id'=>$id]);
        $query = $query->row_array();
        if(empty($query['nama'])) {
            $analis = $query['username'];
        }else{
          $analis = $query['nama'];
        }
        return $analis;
    }

    function get_alat_uji($id_uji='', $kategori = '')
    {   
        $this->db->select('id, nama_alat, jenis_alat');
        $this->db->like('jenis_uji', '"'.$id_uji.'"');
        $this->db->where(['jenis_alat'=>'Peralatan Utama', 'is_active'=>'1']);
        if($kategori != ''){
            $this->db->where('kategori', $kategori);
        }
        $this->db->order_by('id', 'asc');
        $query = $this->db->get('peralatan');
        return $query;
    }

    function get_alat_uji_by_id($id)
    {
        $this->db->select('nama_alat');
        $query = $this->db->get_where('peralatan', ['id'=>$id]);
        return $query->row_array();
    }

    function cek_parameter($in_id, $id_sampel){
        $this->db->where('id', $id_sampel);
        foreach($in_id as $i){
            $this->db->like('ds.jenis_pengujian', '"'.$i.'"');
        }
        $query = $this->db->get('data_sampel ds');
        $result = $query->row_array();
        return $result;
    }

  }
