<?php

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

class M_disposisi 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 getAnalis($id_uji){
        $cek_sampel = $this->db->get_where('data_sampel', ['id_uji'=>$id_uji])->result_array();
        
        foreach($cek_sampel as $ckey => $ck){
            $cek_lhu = $this->db->get_where('lhu', ['id_sampel'=>$ck['id']])->row_array();
            if(!$cek_lhu){
                $this->db->insert('lhu', ['id_sampel'=>$ck['id']]);
            }else{
                $cek_key = array_keys($cek_lhu);
                $data_keys = [];
                foreach($cek_key as $ckk){
                    if(strpos('1'.$cek_lhu[$ckk], 'kadar') != false){
                        $data_keys[$ckk] = null;
                    }
                    $this->db->where('id_sampel', $cek_lhu['id_sampel']);
                    $this->db->update('lhu', $data_keys);
                }
            }
            $jenis_pengujian = json_decode($ck['jenis_pengujian']);
            if(in_array(15, $jenis_pengujian)){
                $jenis_pengujian[] = 1;
                $jenis_pengujian[] = 2;
                $jenis_pengujian[] = 3;
                $jenis_pengujian[] = 4;
                $jenis_pengujian[] = 5;
            } elseif(in_array(16, $jenis_pengujian)){
                $jenis_pengujian[] = 1;
                $jenis_pengujian[] = 2;
                $jenis_pengujian[] = 14;
                $jenis_pengujian[] = 4;
                $jenis_pengujian[] = 5;
            }
            foreach($jenis_pengujian as $jpkey => $jp){
                if(!in_array($jp, [15])){
                    $dlhu = $this->get_table_uji($ck['id'], $jp);
                    $dlhu2[] = $dlhu;
                    if($dlhu['flhu'] != ''){
                        $this->db->where('id_sampel', $ck['id']);
                        $this->db->update('lhu', [$dlhu['flhu'] => $dlhu['kadar_uji']]);
                    }
                }
            }
            //tampil_array($jenis_pengujian);
        }
        //die();

        $query = $this->db->query("SELECT js.*, ds.*,
        l.*, l.air as l_air, l.abu as l_abu, l.protein_kasar as l_protein_kasar, l.lemak_kasar as l_lemak_kasar, l.serat_kasar as l_serat_kasar,l.kalsium as l_kalsium, l.fosfor as l_fosfor, l.tdn as l_tdn, l.ur as l_ur, l.af as l_af, l.andf as l_andf, l.mbm as l_mbm, l.betn as l_betn, l.ca as l_ca, l.pkd as l_pkd, js.nama as jenis_sampel,
        gj.detail as gol_jenis_sampel, js.id as id_jenis_sampel, pu.ket_lhu
        FROM data_sampel ds
        LEFT JOIN lhu l ON l.id_sampel = ds.id AND l.is_active = '1'
        LEFT JOIN jenis_sampel js ON js.id = ds.id_jenis_sampel
        LEFT JOIN gol_jenis_sampel gj ON gj.id = js.id_gol_sampel
        LEFT JOIN permintaan_uji pu ON pu.id = ds.id_uji
        WHERE ds.id_uji = ? AND ds.is_active ='1'", $id_uji);
        return $query->result_array();
    }

    function get_table_uji($id_sampel, $id_parameter_uji){
        if($id_parameter_uji ==  1) {$select = 'kadar_air'; $table = 'kadar_air'; $lhu_field = 'air';}
        else if($id_parameter_uji ==  2) {$select = 'kadar_abu'; $table = 'kadar_abu'; $lhu_field = 'abu';}
        else if($id_parameter_uji ==  3) {$select = 'kadar_pk'; $table = 'kadar_protein_kasar'; $lhu_field = 'protein_kasar';}
        else if($id_parameter_uji ==  4) {$select = 'kadar_lk'; $table = 'kadar_lemak_kasar'; $lhu_field = 'lemak_kasar';}
        else if($id_parameter_uji ==  5) {$select = 'kadar_sk'; $table = 'kadar_serat_kasar'; $lhu_field = 'serat_kasar';}
        else if($id_parameter_uji ==  6) {$select = 'kadar_ca'; $table = 'kadar_kalsium'; $lhu_field = 'kalsium';}
        else if($id_parameter_uji ==  7) {$select = 'kadar_p'; $table = 'kadar_fosfor'; $lhu_field = 'fosfor';}
        else if($id_parameter_uji ==  8) {$select = 'bk_tdn'; $table = 'tdn'; $lhu_field = 'tdn';}
        else if($id_parameter_uji ==  9) {$select = 'kadar_ur'; $table = 'kadar_ur'; $lhu_field = 'ur';}
        else if($id_parameter_uji == 10) {$select = 'kadar_af'; $table = 'kadar_af'; $lhu_field = 'af';}
        else if($id_parameter_uji == 11) {$select = 'kadar_andf'; $table = 'kadar_andf'; $lhu_field = 'andf';}
        else if($id_parameter_uji == 12) {$select = 'kadar_mbm'; $table = 'kadar_mbm'; $lhu_field = 'mbm';}
        else if($id_parameter_uji == 13) {$select = 'kadar_ca'; $table = 'kadar_ca'; $lhu_field = 'ca';}
        else if($id_parameter_uji == 14) {$select = 'protein_kasar'; $table = 'kadar_pkd'; $lhu_field = 'pkd';}
        else if($id_parameter_uji == 17) {$select = 'bk_betn'; $table = 'tdn'; $lhu_field = 'betn';}
        else {$select = ''; $table = '';}

        if($table != ''){
            $this->db->select($select.' as kadar_uji');
            $query = $this->db->get_where($table, ['id_sampel' => $id_sampel]);
            $result = $query->row_array();
            $kadar_uji = $result['kadar_uji'];
            if(in_array($kadar_uji, ['', null])){
                $result['kadar_uji'] = null;
            }
            $result['flhu'] = $lhu_field;
        }else{
            $result['kadar_uji'] = null;
            $result['flhu'] = null;
        }

        return $result;
    }

    function getDataUji($is_count = false, $limit = null, $start = null, $id = null, $history=false){
        $param = array();

        $id_group =  $this->id_group.'|';
        $getStatus = $this->db->query("SELECT id FROM status_sampel WHERE id_group_user LIKE ?", '%'.$id_group.'%')->result_array();
        $id_status = array();
        $id_status[] = 0;
        foreach($getStatus as $r){
            $id_status[] = $r['id'];
        }

        if($is_count)
            $query = "SELECT COUNT(id) AS jumlah";
        else
            $query ="SELECT pu.id, ss.nama as status_sampel, pu.id_status, ss.detail, pu.keterangan";

        $query .= " FROM permintaan_uji pu";

        if(!$is_count)
            $query .= " LEFT JOIN status_sampel ss ON ss.id = pu.id_status";

        $query .= " WHERE pu.is_active = '1' AND pu.id_status > ? AND pu.id_status <> '14' ";

        $status = 1;
        if(!in_array($this->id_group, array('1','2','4'))) $status = 2;
        $param[] = $status;

        if(!is_null($id_status)){
            $query .= " AND pu.id_status IN ?";
            $param[] = $id_status;
        }

        if($history){
            foreach($getStatus as $r){
                $query .= " || pu.id_status > ?";
                $param[] = $r['id'];
            }
            $query .= " AND last_modified_user = ?";
            $param[] = $this->id_user;
        }

        $query .= " ORDER BY pu.id ASC";

        if($is_count == false) {
            if (isset($limit)) {
                $query .= " LIMIT ?, ?";
                $param[] = intval($start);
                $param[] = intval($limit);
            }
        }

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

        if($is_count){
                return $result->row_array()['jumlah'];
        } else {
                if($id != null)
                    return $result->row_array();
                else
                    return $result->result_array();
        }
    }

    function save_lhp($param)
    {
        $this->db->select('id_sampel');
        $cek = $this->db->get_where('lhp', ['id_sampel'=>$param['id_sampel']])->result_array();
        if(count($cek) >= 1){
            $this->db->where('id_sampel', $param['id_sampel']);
            $this->db->update('lhp', $param);
            return 2;
        }else{
            $this->db->insert('lhp', $param);
            return 1;
        }
    }

    function getDisposisi($is_count = false, $limit = null, $start = null, $id = null, $history=false){
        $param = array();

        $id_group =  $this->id_group.'|';
        $getStatus = $this->db->query("SELECT id FROM status_sampel WHERE id_group_user LIKE ?", '%'.$id_group.'%')->result_array();
        $id_status = array();
        $id_status[] = 0;
        foreach($getStatus as $r){
            $id_status[] = $r['id'];
        }

        if($is_count){
            $query = "SELECT COUNT(d.id_disposisi) as c FROM data_sampel ds
                WHERE ds.id_uji = ? AND ds.is_active = '1'";

            $result = $this->db->query($query, $id_uji)->row_array();
        }else {
            $query = "SELECT ds.id as id_sampel, ds.jenis_pengujian, ds.kualitas, ps.nama AS jenis_penanganan,d.kemasan, bs.nama AS jenis_berat, d.berat_sampel, sk.id as sampel_kerja, sa.id as sampel_arsip, pu.id, ss.nama as status_sampel, pu.id_status, ss.detail, pu.keterangan, gjs.nama as jenis_klasifikasi, ds.nomor_sampel, pu.registrasi_laboratorium
                FROM data_sampel ds
                LEFT JOIN disposisi d ON d.id_sampel = ds.id AND d.is_active = '1'
                LEFT JOIN penanganan_sampel ps ON ps.id = d.id_penanganan_sampel
                LEFT JOIN kondisi_sampel bs ON bs.id = d.id_kondisi_sampel
                LEFT JOIN penyimpanan_sampel sk ON sk.id = d.id_sampel_kerja
                LEFT JOIN penyimpanan_sampel sa ON sa.id = d.id_sampel_arsip
                LEFT JOIN permintaan_uji pu ON pu.id = ds.id_uji
                LEFT JOIN status_sampel ss ON ss.id = pu.id_status
                LEFT JOIN gol_jenis_sampel gjs ON gjs.id = ds.klasifikasi
                WHERE pu.is_active = '1' AND ds.is_active = '1' AND pu.id_status > ? AND pu.id_status <> '14'";

            $status = 2;
            if(!in_array($this->id_group, array('1','2','4'))) $status = 2;
            $param[] = $status;

            if(!is_null($id_status)){
                $query .= " AND pu.id_status IN ?";
                $param[] = $id_status;
            }

            if($history){
                foreach($getStatus as $r){
                    $query .= " || pu.id_status > ?";
                    $param[] = $r['id'];
                }
                $query .= " AND pu.last_modified_user = ?";
                $param[] = $this->id_user;
            }

            if(!empty($this->input->post('filter_cari'))){
                $data = $this->input->post();
                $where = $this->whereHandler($query, $param, $data);

                $query = $where['query'];
                $param = $where['param'];
            }

            $query .= " ORDER BY pu.id ASC";

            if($is_count == false) {
                if (isset($limit)) {
                    $query .= " LIMIT ?, ?";
                    $param[] = intval($start);
                    $param[] = intval($limit);
                }
            }
        }

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

        if($is_count){
            return $result->row_array()['jumlah'];
        } else {
            if($id != null)
                return $result->row_array();
            else
                return $result->result_array();
        }
    }

    function whereHandler($query, $param, $data){

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

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

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

        if(isset($data['parameter_uji'])){
            $query .= " AND ds.jenis_pengujian LIKE ?";
            $param[] = "%".$data['parameter_uji']."|%";
        }

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

        $result['query'] = $query;
        $result['param'] = $param;

        return $result;
    }

    function siapkanSampel($data){
        $this->db->trans_begin();
        $id_ttpp = $this->db->get_where('permintaan_uji', ['id'=>$data['id_uji']])->row_array()['id_ttpp'];
        $param = array();
        $param[] = $data['id_status'];
        $param[] = $data['id_user'];

        $query = "UPDATE permintaan_uji SET id_status = ?, last_modified_user = ?, last_modified_time = NOW() ";

        if($data['id_status'] == 9){
        $date = strtotime(date('Y-m-d H:i:s'));
        $query .= " , tgl_pukul_terbit_lhu = ? ";
        $param[] = $date;
        }

        if($data['id_status'] == 11){
        $date = strtotime(date('Y-m-d H:i:s'));
        $query .= " , tgl_pukul_terbit_lhp = ? ";
        $param[] = $date;
        }

        $query .= " WHERE id_ttpp = ? ";
        $param[] = $id_ttpp;

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

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

    function penyiapanUji(){
        $this->db->select('d.id');
        $this->db->join('permintaan_uji p', 'p.id = d.id_uji', 'left');
        $selectSampel = $this->db->get_where('data_sampel d', ['p.id_status'=>4, 'd.is_active'=>'1'])->result_array();

        $id = [];
        foreach ($selectSampel as $r) {
            $this->db->select('id');
            $cek = $this->db->get_where('penyiapan_sampel', ['id_sampel'=>$r['id'], 'is_active'=>'1'])->row_array();
            if($cek){
            $id[] = $cek['id'];
            }else {
            $this->db->insert('penyiapan_sampel', ['id_sampel'=>$r['id']]);
            $id[] = $this->db->insert_id();
            }
        }

        $query = $this->db->query("SELECT ps.*, ds.id as id_sampel, ds.bentuk_jenis, pu.tgl_penyiapan_sampel, jg.nama as nama_grinder, jl.nama as nama_layanan, ds.nomor_sampel
            FROM data_sampel ds
            LEFT JOIN penyiapan_sampel ps ON ps.id_sampel = ds.id AND ps.is_active = '1'
            LEFT JOIN jenis_grinder jg ON jg.id = ps.id_grinder
            LEFT JOIN permintaan_uji pu ON pu.id = ds.id_uji
            LEFT JOIN jenis_layanan jl ON jl.id = pu.id_layanan
        WHERE pu.id_status >= 4 AND ds.is_active = '1' AND ps.status_penyiapan = 0")->result_array();

        return $query;
    }

    function penyiapan_sampel($data, $id = '') {
        $param = [
            'pukul_mulai' => $data['pukul_mulai'],
            'pukul_selesai' => $data['pukul_selesai'],
            'id_bentuk_sampel' => $data['id_bentuk_sampel'],
            'id_penanganan_arsip' => $data['id_penanganan_arsip'],
            'id_penanganan_kerja' => $data['id_penanganan_kerja'],
            'id_grinder' => $data['id_grinder'],
            'keterangan' => $data['keterangan'],
            'last_modified_user' => $this->id_user,
            //'ket_penanganan_kerja' => ($data['id_penanganan_kerja'] == 5) ? $data['ket_penanganan_kerja'] : null,
            'jenis_sampel' => $data['jenis_sampel'],
            'tanggal_penyiapan' => strtotime($data['tanggal_penyiapan']),
            'is_active' => '1',
            'status_penyiapan' => 1,
            'nomor_batch' => $data['nomor_batch'],
            'is_acc' => 0
        ];

        if($id != '') {
            $this->db->where(['id_sampel'=>$id, 'is_active'=>'1']);
            $update = $this->db->update('penyiapan_sampel', $param);

            if($update){
                $data_disposisi = [
                // 'id_sampel_kerja' => $data['id_sampel_kerja'],
                // 'id_sampel_arsip' => $data['id_sampel_arsip'],
                // 'berat_sampel' => $data['berat_sampel'],
                // 'satuan_berat' => $data['satuan_sampel'],
                // 'kemasan' => $data['kemasan']
                'bentuk_jenis' => $data['bentuk_jenis'],
                'nomor_sampel' => $data['nomor_sampel'],
                ];

                //$update_disposisi = $this->update_disposisi($data_disposisi, $id);
                $this->db->where('id', $id);
                $this->db->update('data_sampel', $data_disposisi);

            }
        }
        return 1;
    }

    function get_sampel_by_id($id) {
        $this->db->select('*');
        return $this->db->get_where('data_sampel', ['id'=>$id])->row_array();
    }

    function get_disposisi_by_id($id_sampel){
        $this->db->select('*');
        return $this->db->get_where('disposisi', ['id_sampel'=>$id_sampel])->row_array();
    }

    function update_disposisi($data, $id_sampel = ''){
        $this->db->where(['id_sampel'=>$id_sampel]);
        $this->db->update('disposisi', $data);
        return 1;
    }

    function getEditSampel($id_sampel){
        return $this->db->query("SELECT ps.*, ks.nama as kondisi_sampel, jp.nama as arsip_penanganan, ks.nama as kerja_penanganan, jg.nama as nama_grinder, d.id_sampel_kerja, d.id_sampel_arsip, d.kemasan, d.satuan_berat, d.berat_sampel
            FROM penyiapan_sampel ps
            LEFT JOIN bentuk_sampel ks ON ks.id = ps.id_bentuk_sampel
            LEFT JOIN jenis_penanganan_sampel jp ON jp.id = ps.id_penanganan_arsip
            LEFT JOIN jenis_penanganan_sampel js ON js.id = ps.id_penanganan_kerja
            LEFT JOIN jenis_grinder jg ON jg.id = ps.id_grinder
            LEFT JOIN disposisi d ON d.id_sampel = ps.id_sampel
            WHERE ps.id_sampel = ? AND d.id_sampel = ? AND ps.is_active = ?", array($id_sampel, $id_sampel, '1'))->row_array();
    }

    function deletePenyiapanSampel($id_uji){
        $sampel = $this->db->query("SELECT id_sampel FROM data_sampel WHERE id_uji = ? AND is_active = ?", array($id_uji, '1'))->result_array();

        $this->db->trans_begin();
        foreach ($sampel as $r) {
            $this->db->query("DELETE FROM penyiapan_sampel WHERE id_sampel = ? AND is_active = ?", array($r['id_sampel'], '1'));
        }

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

    function tolak_deputi($data){
        $this->db->trans_begin();
        $this->db->query("UPDATE permintaan_uji SET keterangan = ? WHERE id = ?", array($data['keterangan'], $data['id_uji2']));

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

    function saveAnalis($data){
        $this->db->trans_begin();
        $cek = $this->db->query("SELECT id_sampel FROM lhu WHERE id_sampel = ? AND is_active = '1'", $data['id_sampel'])->row_array()['id_sampel'];

        if(isset($cek)){
            $data['id_sampel_param'] = $cek;
            $this->db->query("UPDATE lhu SET id_sampel = ?, air = ?, abu = ?, protein_kasar = ?, lemak_kasar = ?, serat_kasar = ?, kalsium = ?, fosfor = ?, tdn = ?, ur = ?, af = ?, andf = ?, mbm = ?,
            mulai_uji_air = ?, mulai_uji_abu = ?, mulai_uji_protein_kasar = ?, mulai_uji_lemak_kasar = ?, mulai_uji_serat_kasar = ?, mulai_uji_kalsium = ?, mulai_uji_fosfor = ?, mulai_uji_tdn = ?, mulai_uji_ur = ?, mulai_uji_af = ?, mulai_uji_andf = ?, mulai_uji_mbm = ?,
            selesai_uji_air = ?, selesai_uji_abu = ?, selesai_uji_protein_kasar = ?, selesai_uji_lemak_kasar = ?, selesai_uji_serat_kasar = ?, selesai_uji_kalsium = ?, selesai_uji_fosfor = ?, selesai_uji_tdn = ?, selesai_uji_ur = ?, selesai_uji_af = ?, selesai_uji_andf = ?, selesai_uji_mbm = ?,
            last_modified_user = ? WHERE id_sampel = ?",$data);
        }else{
            $this->db->query("INSERT INTO lhu(id_sampel, air, abu, protein_kasar, lemak_kasar, serat_kasar, kalsium, fosfor, tdn, ur, af, andf, mbm,
            mulai_uji_air, mulai_uji_abu, mulai_uji_protein_kasar, mulai_uji_lemak_kasar, mulai_uji_serat_kasar, mulai_uji_kalsium, mulai_uji_fosfor, mulai_uji_tdn, mulai_uji_ur, mulai_uji_af, mulai_uji_andf, mulai_uji_mbm,
            selesai_uji_air, selesai_uji_abu, selesai_uji_protein_kasar, selesai_uji_lemak_kasar, selesai_uji_serat_kasar, selesai_uji_kalsium, selesai_uji_fosfor, selesai_uji_tdn, selesai_uji_ur, selesai_uji_af, selesai_uji_andf, selesai_uji_mbm,
            last_modified_user) VALUE
            (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?, ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", $data);
        }

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

    function getPenangananSampelArsip($type) {
        $query = "SELECT id, nama FROM jenis_penanganan_sampel WHERE is_active ='1' AND detail = 'Arsip'";

        if($type === '1') $query .= " AND id = '1'";
        if($type !== '1') $query .= " AND id <> '1'";

        $query = $this->db->query($query)->result_array();

        $result = '<option></option>';
        foreach($query as $r) {
            $result .= "<option value='".$r['id']."'>".$r['nama']."</option>";
        }

        return $result;
    }

    function udpateLhu($id_sampel, $tabel, $field, $field2){
        $query = "SELECT id_sampel, ".$field.", mulai_uji, selesai_uji FROM ".$tabel." WHERE id_sampel = ? AND is_active = '1'";
        $data = $this->db->query($query, $id_sampel)->row_array();
        $data['mulai_uji'] = toi_date01($data['mulai_uji']);
        $data['selesai_uji'] = toi_date01($data['selesai_uji']);
        $id_sampel = $this->db->query("SELECT id_sampel FROM lhu WHERE id_sampel = ? AND is_active = '1'",$id_sampel)->row_array()['id_sampel'];

        $this->db->trans_begin();
        if(isset($id_sampel)) {
            $query = "UPDATE lhu SET ".$field2." = ?, mulai_uji_".$field2." = ?, selesai_uji_".$field2." = ? WHERE id_sampel = ?";
            $this->db->query($query, array($data[$field], $data['mulai_uji'], $data['selesai_uji'], $data['id_sampel']));
        }else {
            $query = "INSERT INTO lhu (id_sampel, ".$field2.", mulai_uji_".$field2.", selesai_uji_".$field2.") VALUES
            (?,?,?,?)";
            $this->db->query($query, array($data['id_sampel'], $data[$field], $data['mulai_uji'], $data['selesai_uji']));
        }

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

    function get_data_kadar($table, $id_sampel){
        return $this->db->query("SELECT * FROM $table WHERE id_sampel = ? AND is_active = '1'", $id_sampel)->row_array();
    }

    function cek_kadar($tabel, $id_sampel){
        $query = 'SELECT id_kadar FROM '.$tabel.' WHERE id_sampel = ?';
        return $this->db->query($query, $id_sampel)->row_array()['id_kadar'];
    }

    function dataUji($id_uji) {
        return $this->db->query("SELECT * FROM permintaan_uji WHERE id = ? AND is_active = '1'", $id_uji)->row_array();
    }

    function get_tdn($data){
        $param = array();
        $query = "SELECT t.*, ds.id as nomor_sampel, l.*, pu.registrasi_laboratorium as nomor_lab FROM data_sampel ds
            LEFT JOIN tdn t ON ds.id = t.id_sampel
            LEFT JOIN lhu l ON ds.id = l.id_sampel
            LEFT JOIN permintaan_uji pu ON pu.id = ds.id_uji
            WHERE pu.id = ? AND pu.id_status = ? AND pu.is_active = '1' AND ds.is_active = '1'
        ";

        $param[] = $data['id_uji'];
        $param[] = $data['status'];

        $result =  $this->db->query($query, $param);
        return $result->result_array();
    }

    function set_tdn($param, $param_key){
        $this->db->trans_begin();
        $ctdn = $this->db->query("SELECT id_sampel FROM tdn WHERE id_sampel  = ?", $param['id_sampel'])->row_array()['id_sampel'];

        if(!isset($ctdn)){
            $query = "INSERT INTO tdn (".$param_key['insert'].") VALUES(".$param_key['insert_p'].")";
        }else {
            $query = "UPDATE tdn SET ".$param_key['update']." WHERE ".$param_key['update_p'];
        }

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

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

    function get_kadar_air_total($data){
        $param = array();
        $query = "SELECT ds.id as nomor_sampel, t.kadar_air3, t.pj_air3, t.tgl_selesai_perhitungan as tgl3, t.tgl_mulai_perhitungan as tgl3_mulai,
            l.kadar_air as kadar_air1, l.nama_pelaksana as pj_air1, l.tgl_pukul_setelah_dioven as tgl1,
            lh.air as kadar_air2, ka.nama_analis as pj_air2,lh.selesai_uji_air as tgl2,
            pu.registrasi_laboratorium as nomor_lab
            FROM data_sampel ds
            LEFT JOIN kadar_air_total t ON ds.id = t.id_sampel
            LEFT JOIN pelayuan_sampel l ON ds.id = l.id_sampel
            LEFT JOIN kadar_air ka ON ka.id_sampel = ds.id
            LEFT JOIN lhu lh ON lh.id_sampel = ds.id
            JOIN permintaan_uji pu ON pu.id = ds.id_uji
            WHERE pu.id = ? AND pu.id_status = ? AND pu.is_active = '1' AND ds.is_active = '1'
        ";

        $param[] = (int)$data['id_uji'];
        $param[] = $data['status'];

        $result =  $this->db->query($query, $param);
        return $result->result_array();
    }

    function set_kat($data){
        $this->db->trans_begin();
        $param = array();

        $cek = $this->db->query("SELECT id_sampel FROM kadar_air_total WHERE id_sampel = ?", $data['id_sampel'])->row_array()['id_sampel'];

        if($cek){
            $query = "UPDATE kadar_air_total SET kadar_air3 = ?, pj_air3 = ?, tgl_mulai_perhitungan = ?, tgl_selesai_perhitungan =? WHERE id_sampel = ?";
        }else{
            $query = "INSERT INTO kadar_air_total (kadar_air3, pj_air3, tgl_mulai_perhitungan, tgl_selesai_perhitungan, id_sampel) VALUES(?,?,?,?,?)";
        }

        $param[] = $data['kadar_air3'];
        $param[] = $data['pj_air3'];
        $param[] = date('Y-m-d H:i:s');
        $param[] = date('Y-m-d H:i:s', strtotime('+10 minutes', time()));
        $param[] = $data['id_sampel'];

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

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

    function get_deret_fosfor($nomor_batch=null){
        $param = array();
        $query = "SELECT * FROM deret_fosfor WHERE 1";

        $query .=" AND nomor_batch = ?";
        $param[] = $nomor_batch;
        $result = $this->db->query($query, $param);
        return $result->result_array();
    }

    function get_deret_af($nomor_batch=null){
        $param = array();
        $query = "SELECT * FROM deret_af WHERE 1";

        $query .=" AND nomor_batch = ?";
        $param[] = $nomor_batch;
        $result = $this->db->query($query, $param);
        return $result->result_array();
    }

    function get_deret_ur($nomor_batch=null){
        $param = array();
        $query = "SELECT * FROM deret_ur WHERE 1";

        $query .=" AND nomor_batch = ?";
        $param[] = $nomor_batch;
        $result = $this->db->query($query, $param);
        return $result->result_array();
    }

    function get_deret_ca($nomor_batch=null){
        $param = array();
        $query = "SELECT * FROM deret_ca WHERE 1";

        $query .=" AND nomor_batch = ?";
        $param[] = $nomor_batch;
        $result = $this->db->query($query, $param);
        return $result->result_array();
    }

    function set_deret_ur($data){
        $this->db->trans_begin();
        $cek =  $this->db->query('SELECT id FROM deret_ur WHERE deret_std_urea = ? AND nomor_batch = ?', array($data['deret_std_urea'], $data['nomor_batch']))->row_array();

        $param = array();
        $param[] = $data['nomor_batch'];
        $param[] = $data['deret_std_urea'];
        $param[] = $data['abs'];

        if(!empty($cek['id'])){
            $query = "UPDATE deret_ur SET nomor_batch = ?, deret_std_urea = ?, `abs` = ? WHERE id = ?";
            $param[] = $cek['id'];
        }else{
            $query =  "INSERT INTO deret_ur (nomor_batch, deret_std_urea, `abs`) VALUES
            (?,?,?)";
        }

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

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

    function set_deret_ca($data){
        $this->db->trans_begin();
        $cek =  $this->db->query('SELECT id FROM deret_ca WHERE deret_std_ca = ? AND nomor_batch = ?', array($data['deret_std_ca'], $data['nomor_batch']))->row_array();

        $param = array();
        $param[] = $data['nomor_batch'];
        $param[] = $data['deret_std_ca'];
        // $param[] = $data['volume_pipet_std_ca'];
        // $param[] = $data['labu_ukur_std_ca'];
        // $param[] = $data['std_ca40_ppm'];
        $param[] = $data['abs'];

        if(!empty($cek['id'])){
            $query = "UPDATE deret_ca SET nomor_batch = ?, deret_std_ca = ?, `abs` = ? WHERE id = ?";
            $param[] = $cek['id'];
        }else{
            $query =  "INSERT INTO deret_ca (nomor_batch, deret_std_ca, `abs`) VALUES
            (?,?,?)";
        }

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

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

    function set_deret_af($data){
        $this->db->trans_begin();
        $cek =  $this->db->query('SELECT id FROM deret_af WHERE level_standart = ? AND nomor_batch = ?', array($data['level_standart'], $data['nomor_batch']))->row_array();

        $param = array();
        $param[] = $data['nomor_batch'];
        $param[] = $data['level_standart'];
        $param[] = $data['abs'];

        if(!empty($cek['id'])){
            $query = "UPDATE deret_af SET nomor_batch = ?, level_standart = ?, `abs` = ? WHERE id = ?";
            $param[] = $cek['id'];
        }else{
            $query =  "INSERT INTO deret_af (nomor_batch, level_standart, `abs`) VALUES
            (?,?,?)";
        }

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

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

    function set_deret_fosfor($data){
        $this->db->trans_begin();
        $cek =  $this->db->query('SELECT id FROM deret_fosfor WHERE deret_std_p = ? AND nomor_batch = ?', array($data['deret_std_p'], $data['nomor_batch']))->row_array();

        $param = array();
        $param[] = $data['nomor_batch'];
        $param[] = $data['deret_std_p'];
        $param[] = $data['volume_pipet_std_p'];
        $param[] = $data['labu_ukur_std_p'];
        $param[] = $data['std_p_100_ppm'];
        $param[] = $data['abs'];

        if(!empty($cek['id'])){
            $query = "UPDATE deret_fosfor SET nomor_batch = ?, deret_std_p = ?, volume_pipet_std_p = ?, labu_ukur_std_p = ?, std_p_100_ppm = ?, `abs` = ? WHERE id = ?";
            $param[] = $cek['id'];
        }else{
            $query =  "INSERT INTO deret_fosfor (nomor_batch, deret_std_p, volume_pipet_std_p, labu_ukur_std_p, std_p_100_ppm, `abs`) VALUES
            (?,?,?,?,?,?)";
        }

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

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

    function set_tgl_persetujuan($id_uji){
        $this->db->trans_begin();
        $data_sampel = $this->db->get_where('data_sampel', ['id_uji'=>$id_uji, 'is_active'=>'1'])->result_array();
        $parameter_uji = $this->db->get_where('parameter_uji', ['is_active' => '1'])->result_array();

        $tabel_uji = array('air', 'abu', 'protein_kasar', 'lemak_kasar', 'serat_kasar', 'kalsium', 'fosfor', 'tdn', 'ur', 'af', 'andf', 'mbm');

        if($data_sampel){
            foreach($data_sampel as $d){
                $p_uji = $this->db->query("SELECT jenis_pengujian FROM data_sampel WHERE id = ? AND is_active = '1'", $d['id'])->row_array()['jenis_pengujian'];
                if($p_uji){
                    $p_uji = json_decode($p_uji);
                    foreach($p_uji as $p){
                        if(!is_null($p) && $p != ''){
                            if(!in_array((int)$p, array(7,8,9,10,11,12,13,14,15))){
                                $tabel = 'kadar_'.$tabel_uji[(int)$p];
                                $this->db->query("UPDATE ".$tabel." SET persetujuan = ? WHERE id_sampel = ?", array(date('Y-m-d H:i:s'), $d['id']));
                            }
                        }
                    }
                }
            }
        }
        if($this->db->trans_status() == false){
            $this->db->trans_rollback();
            return false;
        }else{
            $this->db->trans_commit();
            return true;
        }
    }

    function update_deret($data, $jenis_uji)
    {
        $this->db->trans_begin();
        $x = count($data['abs']);
        for($i=0; $i<$x; $i++){
            if($jenis_uji == 'fosfor'){
            $table = 'deret_fosfor';
            $field = 'c_std_p, deret_std_p';
            }
            if($jenis_uji == 'ur'){
            $table = 'deret_ur';
            $field = 'c_std_urea, deret_std_urea';
            }
            if($jenis_uji == 'af'){
            $table = 'deret_af';
            $field = 'total_af, level_standart';
            }
            if($jenis_uji == 'ca'){
            $table = 'deret_ca';
            $field = 'c_std_ca, deret_std_ca';
            }
            $param = array();
            $query = "INSERT INTO ".$table." (abs, intercept, slope, ".$field;

            if(in_array($jenis_uji, array('ca', 'ur', 'fosfor'))){
                $query .=", d_range, nomor_batch ";
                $query .= ') value(?,?,?,?,?,?,?)';
            }

            if($jenis_uji == 'af'){
                $query .= ", k_b, k_c, k_d, d_range, nomor_batch ";
                $query .= ') value(?,?,?,?,?,?,?,?,?,?)';
            }
            $param[] = $data['abs'][$i];
            $param[] = $data['intercept'];
            $param[] = $data['slope'];
            $param[] = $data['c_std'][$i];
            $param[] = $data['deret_level'][$i];

            if(in_array($jenis_uji, array('ca', 'ur', 'fosfor'))){
                $param[] = $data['d_range'];
            }
            if($jenis_uji == 'af'){
                $param[] = $data['k_b'][$i];
                $param[] = $data['k_c'][$i];
                $param[] = $data['k_d'][$i];
                $param[] = $data['d_range'];   
            }

            $param[] = $data['nomor_batch'];

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

        }

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

    function set_intruksi($data)
    {
        $this->db->trans_begin();
            $this->db->query("UPDATE lhu SET ".$data['nama_field']." = ? WHERE id_sampel = ?", array($data['ket'], $data['id_sampel']));
        if($this->db->trans_status() == false) {
            $this->db->trans_rollback();
            return false;
        }else {
            $this->db->trans_commit();
            return true;
        }
    }

    function riwayat_penyiapan($nomor_batch ='')
    {
        $query = $this->db->query("SELECT ps.*, ds.id as id_sampel, ds.bentuk_jenis, pu.tgl_penyiapan_sampel, jg.nama as nama_grinder, jl.nama as nama_layanan, ds.nomor_sampel
            FROM data_sampel ds
            LEFT JOIN penyiapan_sampel ps ON ps.id_sampel = ds.id AND ps.is_active = '1'
            LEFT JOIN jenis_grinder jg ON jg.id = ps.id_grinder
            LEFT JOIN permintaan_uji pu ON pu.id = ds.id_uji
            LEFT JOIN jenis_layanan jl ON jl.id = pu.id_layanan
        WHERE pu.id_status >= 4 AND ds.is_active = '1' AND ps.nomor_batch = ?", [$nomor_batch])->result_array();
        return $query;
    }

    function get_batch_penyiapan($is_acc = '', $is_history='')
    {
        $param = [];
        $query = "
            SELECT DISTINCT nomor_batch
            FROM penyiapan_sampel
            WHERE is_active = '1' AND nomor_batch IS NOT NULL
        ";

        if($is_acc != ''){
            $query .= " AND is_acc = ?";
            $param[] = $is_acc;
        }

        if($is_history != ''){
            $query .= " AND is_acc IN (1,2)";
        }
        
        $query = $this->db->query($query, $param);
        $result = $query->result_array();
        return $result;
    }
}
