<?php
/*
drop table if exists auto_verif;
create table auto_verif(
  AutoVerifID int not null auto_increment primary key,
  AutoVerifDate datetime default current_timestamp,
  AutoVerifHaveReq varchar(1) default 'N',
  AutoVerifT_OrderDetailID int,
  AutoVerifIsT_TestIsDeltaCheck varchar(1),
  AutoVerifIsT_TestIsTrendAnalysis varchar(1),
  AutoVerifIsActive varchar(1) default 'Y',
  AutoVerifDeltaCheckStatus varchar(1),
  AutoVerifDeltaCheckNote text,
  AutoVerifDeltaCheckLastID int,
  AutoVerifDeltaCheckLastResult varchar(300),
  AutoVerifTrendAnalysisStatus varchar(1),
  AutoVerifTrendAnalysisImage varchar(300),
  AutoVerifTrendAnalysisNote text,
  key (AutoVerifDate),
  key (AutoVerifT_OrderDetailID),
  key (AutoVerifIsActive),
  key(AutoVerifIsT_TestIsTrendAnalysis),
  key(AutoVerifIsT_TestIsDeltaCheck),
  key(AutoVerifHaveReq)
);
alter table auto_verif add AutoVerifReviewRangeIsOK varchar(1) default 'N';

alter table auto_verif add AutoVerifAmrIsOk varchar(1) default 'N',
add AutoVerifAmrNote varchar(300);

create table auto_verif_v2(
  autoVerifID int not null auto_increment primary key,
  AutoVerifT_OrderDetailID int,
  autoVerifIsPanicValue varchar(1) default 'N',
  autoVerifPanicValueNote varchar(200) default '',
  autoVerifIsPreAnalityc varchar(1) default 'N',
  autoVerifPreAnalitycNote varchar(200) default '',
  autoVerifIsInAmr varchar(1) default 'N',
  autoVerifAmrNote varchar(200) default '',
  autoVerifIsUjiTrendOK varchar(1) default 'N',
  autoVerifUjiTrendNote varchar(200),
  autoVerifImage varchar(300),
  autoVerifCreated datetime,
  autoVerifIsActive varchar(1) default 'Y',
  key(AutoVerifT_OrderDetailID),
  key(AutoVerifIsActive)
);
*/
class Auto_verif extends MY_Controller
{
    var $isUpdate = true;
    function debug($id)
    {
        $this->print_table_style();
        $sql = "select T_OrderDetailID, 
      if(T_TestIsDeltaCheck = 'Y', fn_process_re_deltacheck_v2(T_OrderDetailID), '')  delta_check,
      if(T_TestIsTrendAnalysis = 'Y', fn_process_re_trendanalysis_v2_debug(T_OrderDetailID),'') trend_analysis, 
      ifnull(T_OrderReqStatus,'Y') T_OrderReqStatus,
      T_TestIsDeltaCheck,
      T_TestIsTrendAnalysis
      from t_orderdetail
      join t_test on T_OrderDetailID = ? and T_OrderDetailIsActive = 'Y'
        and T_OrderDetailT_TestID = T_TestID 
      left join t_orderreq on T_OrderDetailT_OrderHeaderID = T_OrderReqT_OrderHeaderID 
        and T_OrderReqIsActive = 'Y'";
        $qry = $this->db->query($sql, [$id]);
        if (!$qry) {
            $this->reply_error(
                $this->db->error()["message"] . "\n|" . $this->db->last_query()
            );
            exit();
        }
        $rows = $qry->result_array();
        $this->print_table($rows, array_keys($rows[0]));
    }

    //status IsError , Message
    function uji_trend(
        $orderDate,
        $natTestID,
        $natTestName,
        $result,
        $decimalDigit
    ) {
        //rata-rata harian
        $sql = "select Nat_TrendAnalysisLow, Nat_TrendAnalysisHigh, Nat_TrendAnalysisMinCount
    from nat_trend_analysis
    where Nat_TrendAnalysisNat_TestID = ?";
        $qry = $this->db->query($sql, [$natTestID]);
        if (!$qry) {
            $this->reply_error(
                $this->db->error()["message"] . "\n|" . $this->db->last_query()
            );
            exit();
        }
        $rows = $qry->result_array();
        if (count($rows) == 0) {
            return ["N", "TrendAnalysis belum di setting", ""];
        }
        // - SD  s/d SD
        $low = round($rows[0]["Nat_TrendAnalysisLow"], $decimalDigit);
        $high = round($rows[0]["Nat_TrendAnalysisHigh"], $decimalDigit);

        if (!(is_numeric($low) && is_numeric($high))) {
            return ["N", "-SD dan +SD tidak numerik.", ""];
        }
        $mean = $low + ($high - $low) / 2;
        $sd = ($high - $low) / 4;
        $minSd = $mean - $sd;
        $plusSd = $mean + $sd;

        $minCount = $rows[0]["Nat_TrendAnalysisMinCount"];
        //yyyy-hh-mm
        $startDate = substr($orderDate, 0, 10) . " 00:00:01";
        $endDate = substr($orderDate, 0, 10) . " 23:59:59";

        $sql = " select T_OrderDetailResult
         from
         t_orderdetail
         join t_orderheader on T_OrderDetailT_OrderHeaderID = T_OrderHeaderID
            and T_OrderHeaderDate >= ? and T_OrderHeaderDate <= ?
            and T_OrderDetailIsActive = 'Y' and T_OrderHeaderIsActive = 'Y'
         join t_test on T_OrderDetailT_TestID = T_TestID
        and T_TestNat_TestID = ?";
        $qry = $this->db->query($sql, [$startDate, $endDate, $natTestID]);
        if (!$qry) {
            return ["N", "Error : " . $this->db->error()["message"], ""];
        }
        $rows = $qry->result_array();
        $count = 0;
        $total = 0;
        $a_result = [];
        foreach ($rows as $r) {
            if (is_numeric($r["T_OrderDetailResult"])) {
                $count++;
                $total = $total + $r["T_OrderDetailResult"];
                $a_result[] = $r["T_OrderDetailResult"];
            }
        }
        if ($count == 0) {
            return ["N", "Sample Result tidak mencukupi [0 < $minCount]", ""];
        }
        $s_result = implode("^", $a_result);
        $avg = round($total / $count, $decimalDigit);
        if ($count < $minCount) {
            return [
                "N",
                "Sample Result tidak mencukupi [$count < $minCount ]",
                "",
            ];
        }
        $note = "Rata-rata harian: $avg | -1SD : $minSd , +1SD: $plusSd";
        $image =
            "/charts/index_v2.php?l={$low}&h={$high}&t=" .
            str_replace(" ", "+", $natTestName) .
            "&c={$result}&avg={$avg}&d={$s_result}";

        if ($avg < $minSd || $avg > $plusSd) {
            return ["N", $note, $image];
        }
        return ["Y", $note, $image];
    }

    function check($nolab)
    {
        $sql = "select 
        T_OrderHeaderDate, T_OrderHeaderLabNumber, 
        T_TestName, T_OrderDetailID, T_OrderDetailVerDate, T_OrderDetailVerUserID,
        T_OrderDetailResult
        from t_orderheader 
        join t_orderdetail on T_OrderHeaderLabNumber = ? 
        and T_OrderHeaderID = T_OrderDetailT_OrderHeaderID
        and T_OrderHeaderIsActive = 'Y'
        and T_OrderDetailIsActive = 'Y'
          join t_test on T_OrderDetailT_TestID = T_TestID and T_TestIsResult = 'Y'
          order by T_OrderHeaderID,T_TestSasCode
        ";
        $qry = $this->db->query($sql, [$nolab]);
        if (!$qry) {
            $this->reply_error(
                $this->db->error()["message"] . "\n|" . $this->db->last_query()
            );
            exit();
        }
        $rows = $qry->result_array();
        foreach ($rows as $idx => $r) {
            $rows[$idx]["Process"] =
                "<a href='/one-api/process/auto_verif/debug/" .
                $r["T_OrderDetailID"] .
                "'> Debug </a>";
        }
        $this->print_table_style();
        $this->print_table($rows, array_keys($rows[0]));
    }

    function list($date = "", $format = "html")
    {
        if ($date == "") {
            $date = date("Y-m-d");
        }
        $start = $date . " 00:00:01";
        $end = $date . " 23:59:59";
        $sql = "select 
      T_OrderHeaderDate, T_OrderHeaderLabNumber, T_OrderDetailT_TestName T_TestName, 
      AutoVerifDate,
      AutoVerifHaveReq HaveRequirement,
      AutoVerifIsT_TestIsDeltaCheck, AutoVerifDeltaCheckStatus, AutoVerifDeltaCheckNote,
      AutoVerifIsT_TestIsTrendAnalysis,AutoVerifTrendAnalysisStatus,AutoVerifTrendAnalysisNote,
      AutoVerifTrendAnalysisImage image
      from auto_verif 
      join t_orderdetail on AutoVerifT_OrderDetailID = T_OrderDetailID 
        and AutoVerifIsT_TestIsDeltaCheck = 'Y' and AutoVerifIsT_TestIsTrendAnalysis = 'Y'
      join t_orderheader on T_OrderDetailT_OrderHeaderID = T_OrderHeaderID
    join t_test on T_OrderDetailT_TestID = T_TestID
      and T_TestNat_GroupID = 1
      where T_OrderHeaderDate >= ? and T_OrderHeaderDate <= ? 
      order by T_OrderHeaderID, T_TestSasCode
      ";
        $qry = $this->db->query($sql, [$start, $end]);
        if (!$qry) {
            echo "Error : " .
                $this->db->error()["message"] .
                " | " .
                $this->db->last_query();
            exit();
        }
        $rows = $qry->result_array();
        $header = [];
        $header[] = [
            "TotalPx" => 0,
            "TotalAutoVerif" => 0,
            "TotalPx with DeltaCheck and TrendAnalysis" => 0,
        ];
        $prevLabNo = "";
        foreach ($rows as $idx => $r) {
            if (
                $r["HaveRequirement"] == "N" &&
                $r["AutoVerifDeltaCheckStatus"] == "Y" &&
                $r["AutoVerifTrendAnalysisStatus"] === "Y"
            ) {
                $header[0]["TotalAutoVerif"]++;
            }
            if (
                $r["AutoVerifIsT_TestIsDeltaCheck"] == "Y" &&
                $r["AutoVerifIsT_TestIsTrendAnalysis"] == "Y"
            ) {
                $header[0]["TotalPx with DeltaCheck and TrendAnalysis"]++;
            }
            $header[0]["TotalPx"]++;
            if ($r["T_OrderHeaderLabNumber"] == $prevLabNo) {
                $rows[$idx]["T_OrderHeaderLabNumber"] = "";
                $rows[$idx]["T_OrderHeaderDate"] = "";
            }
            $prevLabNo = $r["T_OrderHeaderLabNumber"];
            if ($r["image"] != "") {
                $rows[$idx]["image"] =
                    "<a href='" . $r["image"] . "'>View Image</a>";
            }
        }
        if ($format == "html") {
            $this->print_table_style();
            $this->print_table($header, array_keys($header[0]));
            $this->print_table($rows, array_keys($rows[0]));
        }
    }
    function index()
    {
        $this->list();
    }
    function get_param()
    {
        $jparam = file_get_contents("php://input");
        $param = json_decode($jparam, true);
        return $param;
    }
    /*
    alter table auto_verif add AutoVerifReqNote varchar(300) default '';
  */
    function re_panic_value($id, $debug = "")
    {
        $sql = "select
        T_OrderDetailID, T_TestName, min(NatMultiruleExtraLow) ExtraLow, max(NatMultiruleExtraHigh) ExtraHigh,
        T_OrderDetailResult, T_OrderDetailT_OrderHeaderID, 
        if( (T_OrderDetailLastUpdated + interval 3  MINUTE) > now(), 'Y', 'N') lessThen3Minute
        from t_orderdetail
        join t_test on T_OrderDetailID = ?
        and T_OrderDetailIsActive = 'Y'
        and T_OrderDetailT_TestID = T_TestID 
        and T_TestIsResult = 'Y'
        and T_TestIsQuantitative='Y'
        and T_OrderDetailResult <> ''
        and T_OrderDetailID not in (
          select CriticalValueT_OrderDetailID 
          from critical_value 
          where CriticalValueT_OrderDetailID = ?
        )
        join nat_multirule on T_TestNat_TestID = NatMultiruleNat_TestID
        group by T_OrderDetailID";
        $qry = $this->db->query($sql, [$id, $id]);
        if (!$qry) {
            echo json_encode([
                "status" => "ERR",
                "message" =>
                    $this->db->error()["message"] .
                    "|\n" .
                    $this->db->last_query(),
            ]);
            exit();
        }
        $rows = $qry->result_array();
        foreach ($rows as $idx => $r) {
            $status = "X";
            if ($r["T_OrderDetailResult"] == "") {
                $rows[$idx]["status"] = "X";
                $rows[$idx]["note"] = "Result Empty";
                continue;
            }
            if (!is_numeric($r["T_OrderDetailResult"])) {
                $rows[$idx]["status"] = "X";
                $rows[$idx]["note"] = "Result Not Numeric";
                continue;
            }
            if (
                is_numeric($r["ExtraLow"]) &&
                floatval($r["ExtraLow"]) > floatval($r["T_OrderDetailResult"])
            ) {
                $rows[$idx]["status"] = "N";
                $rows[$idx][
                    "note"
                ] = "Result {$r["T_OrderDetailResult"]} < Extra Low {$r["ExtraLow"]}";
                continue;
            }
            if (
                is_numeric($r["ExtraHigh"]) &&
                floatval($r["ExtraHigh"]) < floatval($r["T_OrderDetailResult"])
            ) {
                $rows[$idx]["status"] = "N";
                $rows[$idx][
                    "note"
                ] = "Result {$r["T_OrderDetailResult"]} > Extra High {$r["ExtraHigh"]}";
                continue;
            }
            $rows[$idx]["status"] = "Y";
            $status = "";
            if (is_numeric($r["ExtraLow"]) && is_numeric($r["ExtraHigh"])) {
                $rows[$idx][
                    "note"
                ] = "Result dalama range ExtraLow {$r["ExtraLow"]} - Extra High {$r["ExtraHigh"]}";
                continue;
            }
            if (!is_numeric($r["ExtraLow"])) {
                $status .= $r["ExtraLow"] . " ExtraLow not numeric ";
            }
            if (!is_numeric($r["ExtraHigh"])) {
                $status .= $r["ExtraHigh"] . " ExtraHigh not numeric ";
            }
            $rows[$idx]["note"] = $status;
        }
        $result = array_filter($rows, function ($r) {
            return $r["status"] == "N";
        });
        $sql = "insert into critical_value(CriticalValueT_OrderHeaderID,CriticalValueT_OrderDetailID,CriticalValueDescription)
          values(?,?,?)
          on duplicate key update 
          CriticalValueID = CriticalValueID
        ";
        $updateID = [];
        foreach ($result as $r) {
            $qry = $this->db->query($sql, [
                $r["T_OrderDetailT_OrderHeaderID"],
                $r["T_OrderDetailID"],
                $r["note"],
            ]);
            if (!$qry) {
                echo json_encode([
                    "status" => "ERR",
                    "message" =>
                        $this->db->error()["message"] .
                        "|\n" .
                        $this->db->last_query(),
                ]);
                $this->db->trans_rollback();
                exit();
            }
            $updateID[] = $this->db->insert_id();
        }
        if ($debug != "") {
            echo "Update : \n";
            print_r($updateID);
        }
    }
    function re_process($id, $debug = "")
    {
        //1. Panic Value
        //2. Pre-Analytic
        //3. a. Quantitative
        //3. b. Kualitatif
        //4. Amr
        //5. Uji Trend Hanya utk Kualitatif

        $this->db->trans_begin();

        $sql = "delete from auto_verif_v2 where AutoVerifT_OrderDetailID = ?";
        $qry = $this->db->query($sql, [$id]);
        if (!$qry) {
            $this->reply_error(
                $this->db->error()["message"] . "\n|" . $this->db->last_query()
            );
            $this->db->trans_rollback();
            exit();
        }

        $this->re_panic_value($id);

        //1. Panic Value
        $sql = "select * 
        from critical_value 
        where criticalValueIsActive = 'Y' 
        and criticalValueT_OrderDetailID = ?";
        $qry = $this->db->query($sql, [$id]);
        if (!$qry) {
            $this->reply_error(
                $this->db->error()["message"] . "\n|" . $this->db->last_query()
            );
            $this->db->trans_rollback();
            exit();
        }
        $rows = $qry->result_array();
        $haveCriticalValue = "N";
        $criticalValueNote = "";
        if (count($rows) > 0) {
            $haveCriticalValue = "Y";
            $criticalValueNote = $rows[0]["CriticalValueDescription"];
        }
        // abaikan yang lain jika criticalValue
        $dt_row = [
            "AutoVerifT_OrderDetailID" => $id,
            "AutoVerifIsActive" => "Y",
            "autoVerifCreated" => date("Y-m-d H:i:s"),
            "autoVerifIsPanicValue" => "N",
            "autoVerifPanicValueNote" => $criticalValueNote,
        ];
        if ($haveCriticalValue == "Y") {
            $dt_row = [
                "AutoVerifT_OrderDetailID" => $id,
                "AutoVerifIsActive" => "Y",
                "autoVerifCreated" => date("Y-m-d H:i:s"),
                "autoVerifIsPanicValue" => "Y",
                "autoVerifPanicValueNote" => $criticalValueNote,
            ];

            if ($debug != "") {
                print_r($dt_row);
            }
            $this->insert_or_update("auto_verif_v2", $dt_row, [
                "AutoVerifT_OrderDetailID",
                "AutoVerifIsActive",
            ]);
            $this->db->trans_commit();
            return;
        }
        //check panic_value

        //2. Pre-Analytic
        $sql = "select t_orderreq.*, T_OrderDetailT_TestID 
        from t_orderreq 
        join t_orderdetail on 
        T_OrderDetailID = ?
        and T_OrderReqT_OrderHeaderID = T_OrderDetailT_OrderHeaderID
        and T_OrderReqStatus <> 'Y' 
        and T_OrderReqIsActive='Y'";
        $qry = $this->db->query($sql, [$id]);
        if (!$qry) {
            $this->reply_error(
                $this->db->error()["message"] . "\n|" . $this->db->last_query()
            );
            $this->db->trans_rollback();
            exit();
        }
        $rows = $qry->result_array();
        $havePreAnalytical = "N";
        $preAnalyticalNote = "";
        $jreqs = [];
        $testID = 0;
        foreach ($rows as $r) {
            $testID = $r["T_OrderDetailT_TestID"];
            $jreqs = array_merge($jreqs, json_decode($r["T_OrderReqs"], true));
        }
        if (count($jreqs) > 0) {
            $reqsId = implode(",", $jreqs);
            $sql = "select Nat_RequirementID,Nat_RequirementName, Nat_RequirementIsAllTest, group_concat(ifnull(T_TestID,0)) all_test
            from nat_requirement
            left join nat_testrequirement on Nat_RequirementID in ($reqsId) 
            and Nat_RequirementID = Nat_TestRequirementNat_RequirementID
            left join t_test on Nat_TestRequirementNat_TestID = T_TestNat_TestID
            where Nat_RequirementID in ($reqsId)
            group by Nat_RequirementID";
            $qry = $this->db->query($sql);
            if (!$qry) {
                $this->reply_error(
                    $this->db->error()["message"] .
                        "\n|" .
                        $this->db->last_query()
                );
                $this->db->trans_rollback();
                exit();
            }
            $rows = $qry->result_array();
            foreach ($rows as $r) {
                if ($r["Nat_RequirementIsAllTest"] == "Y") {
                    $havePreAnalytical = "Y";
                    if ($preAnalyticalNote != "") {
                        $preAnalyticalNote .= ", ";
                    }
                    $preAnalyticalNote .= $r["Nat_RequirementName"];
                } else {
                    if (strpos($r["all_test"], $testID) !== false) {
                        $havePreAnalytical = "Y";
                        if ($preAnalyticalNote != "") {
                            $preAnalyticalNote .= ", ";
                        }
                        $preAnalyticalNote .= $r["Nat_RequirementName"];
                    }
                }
            }
        }
        //abaikan lainnya jika ada preAnalyticalNote
        $dt_row["autoVerifIsPreAnalityc"] = $havePreAnalytical;
        $dt_row["autoVerifPreAnalitycNote"] = $preAnalyticalNote;
        if ($havePreAnalytical == "Y") {
            $this->insert_or_update("auto_verif_v2", $dt_row, [
                "AutoVerifT_OrderDetailID",
                "AutoVerifIsActive",
            ]);
            $this->db->trans_commit();
            return;
        }

        //check isQuantitative , OrderDate , Nat_TestID , T_TestID
        $sql = "select T_TestIsQuantitative, T_OrderHeaderDate, T_TestNat_TestID, T_TestID , T_OrderDetailResult,
        T_TestName,T_TestNat_TestID
        from 
        t_orderdetail 
        join t_orderheader on T_OrderDetailID = ? 
        and T_OrderDetailT_OrderHeaderID = T_OrderHeaderID
        join t_test on T_OrderDetailT_TestID = T_TestID
        ";
        $qry = $this->db->query($sql, [$id]);

        if (!$qry) {
            $this->reply_error(
                $this->db->error()["message"] . "\n|" . $this->db->last_query()
            );
            exit();
        }
        $rows = $qry->result_array();
        if (count($rows) == 0) {
            $this->reply_error(
                "Test For OrderDetailID {$id} not found. " .
                    "\n|" .
                    $this->db->last_query()
            );
            exit();
        }
        $orderDate = $rows[0]["T_OrderHeaderDate"];
        $isQuantitative = $rows[0]["T_TestIsQuantitative"];
        $testID = $rows[0]["T_TestID"];
        $testName = $rows[0]["T_TestName"];
        $natTestID = $rows[0]["T_TestNat_TestID"];
        $orderResult = $rows[0]["T_OrderDetailResult"];
        $decimalDigit = 0;
        if (strpos($orderResult, ".") !== false) {
            $decimalDigit =
                strlen($orderResult) - strpos($orderResult, ".") - 1;
            if ($decimalDigit < 0) {
                $decimalDigit = 0;
            }
        }
        $isSedimen = "N";
        $arrNatTestSedimen = [4348, 4349, 4350, 4351, 4352, 4353];
        if (in_array($natTestID, $arrNatTestSedimen)) {
            $isSedimen = "Y";
            $tmpResult = explode("-", $orderResult);
            $orgResult = $orderResult;
            if (count($tmpResult) > 1) {
                $orderResult = trim($tmpResult[1]);
            }
        }
        //3. IsInAMR  -> AMR Masuk atau tidak
        // jika lebih dari satu limitdetection diambil yang tertingi , linearity diambil yg ter-rendah
        $isInAmr = "N";
        $amrNote = "";

        if ($debug != "") {
            echo " $isQuantitative |$orderResult| " . is_numeric($orderResult);
            echo "isUpdate : " . ($this->isUpdate ? "Y" : "N");
        }
        if ($isQuantitative == "N" || !is_numeric($orderResult)) {
            //a. isQuantitative
            //Selain Anti-HBS | Anti-HBS Titer
            $isNegatif =
                stripos($orderResult, "Jernih") !== false ||
                stripos($orderResult, "Kuning muda") !== false ||
                stripos($orderResult, "Kuning") !== false ||
                stripos($orderResult, "Non Reaktif") !== false ||
                stripos($orderResult, "Negatif") !== false ||
                stripos($orderResult, "Normal") !== false;
            if ($isNegatif === false) {
                $isInAmr = "N";
                $amrNote = "Hasil " . $orderResult;
            } else {
                $isInAmr = "Y";
                $amrNote = "Hasil " . $orderResult;
            }
        } else {
            // Anti-HBS => T_TestIsQuantitative = Y
            if ($natTestID == 4844 || $natTestID == 4845) {
                $isInAmr = "Y";
                $amrNote = "Hasil " . $rows[0]["T_OrderDetailResult"];
            } else {
                $sql = "select
                T_OrderDetailID,
                max(NatMultiruleLimitDetection) LimitDetection, min(NatMultiruleLinearity) Linearity,
                T_OrderDetailResult 
                from t_orderdetail
                join t_test on T_OrderDetailID = ?
                and T_OrderDetailIsActive = 'Y'
                and T_OrderDetailT_TestID = T_TestID 
                and T_TestIsResult = 'Y'
                and T_TestIsQuantitative='Y'
                and T_OrderDetailResult <> ''
                join nat_multirule on T_TestNat_TestID = NatMultiruleNat_TestID
                and NatMultiruleIsActive = 'Y'
                group by T_OrderDetailID";
                $qry = $this->db->query($sql, [$id]);
                if (!$qry) {
                    $this->reply_error(
                        $this->db->error()["message"] .
                            "\n|" .
                            $this->db->last_query()
                    );
                    exit();
                }
                $rows = $qry->result_array();
                if ($debug != "") {
                    echo $this->db->last_query();
                    print_r($rows);
                }
                if (count($rows) > 0) {
                    foreach ($rows as $r) {
                        $limitDetection = round(
                            $r["LimitDetection"],
                            $decimalDigit
                        );
                        $linearity = round($r["Linearity"], $decimalDigit);
                        if (
                            $r["T_OrderDetailResult"] < $limitDetection ||
                            $r["T_OrderDetailResult"] > $linearity
                        ) {
                            $isInAmr = "N";
                            $amrNote = "Hasil : {$r["T_OrderDetailResult"]}, Limit Detection : {$limitDetection} , Linearity: {$linearity}";
                        } else {
                            $isInAmr = "Y";
                            $amrNote = "Hasil : {$r["T_OrderDetailResult"]}, Limit Detection : {$limitDetection} , Linearity: {$linearity}";
                        }
                    }
                } else {
                    $isInAmr = "N";
                    $amrNote = "AMR belum di setting.";
                    if ($isSedimen == "Y") {
                        $sql = "select
                T_OrderDetailID,
                max(Nat_TrendAnalysisLow) Low, 
                min(Nat_TrendAnalysisHigh) High,
                T_OrderDetailResult 
                from t_orderdetail
                join t_test on T_OrderDetailID = ?
                and T_OrderDetailIsActive = 'Y'
                and T_OrderDetailT_TestID = T_TestID 
                and T_TestIsResult = 'Y'
                and T_TestIsQuantitative='Y'
                and T_OrderDetailResult <> ''
                join nat_trend_analysis on T_TestNat_TestID = Nat_TrendAnalysisNat_TestID 
                and Nat_TrendAnalysisIsActive = 'Y'
                group by T_OrderDetailID";
                        $qry = $this->db->query($sql, [$id]);
                        if (!$qry) {
                            $this->reply_error(
                                $this->db->error()["message"] .
                                    "\n|" .
                                    $this->db->last_query()
                            );
                            exit();
                        }
                        $rows = $qry->result_array();
                        if (count($rows) == 0) {
                            $amrNote .= ", Sedimen $orgResult => $orderResult";
                        }
                        $r = $rows[0];
                        if (
                            $r["Low"] <= $orderResult &&
                            $orderResult <= $r["High"]
                        ) {
                            $isInAmr = "Y";
                            $amrNote = "Hasil : $orderResult. Low: {$r["Low"]} , Hight: {$r["High"]}";
                        }
                    }
                }
            }
        }
        // abaikan yang laeviewka amr tidak masuk
        $dt_row["autoVerifIsInAmr"] = $isInAmr;
        $dt_row["autoVerifAmrNote"] = $amrNote;
        if ($isInAmr != "Y") {
            $this->insert_or_update("auto_verif_v2", $dt_row, [
                "AutoVerifT_OrderDetailID",
                "AutoVerifIsActive",
            ]);
            $this->db->trans_commit();
            return;
        }
        //4. Uji Trend
        if (
            $isQuantitative == "N" ||
            !is_numeric($orderResult) ||
            $isSedimen == "Y"
        ) {
            $ujiTrendIsOK = "Y";
            $ujiTrendNote = "Qualitative, Hasil : {$orderResult} N/A";
            if ($isSedimen == "Y") {
                $ujiTrendNote = "Sedimen, Hasil : {$orderResult} N/A";
            }
            $ujiTrendImage = "";
        } else {
            list(
                $ujiTrendIsOK,
                $ujiTrendNote,
                $ujiTrendImage,
            ) = $this->uji_trend(
                $orderDate,
                $natTestID,
                $testName,
                $orderResult,
                $decimalDigit
            );
        }

        $dt_row["autoVerifIsUjiTrendOK"] = $ujiTrendIsOK;
        $dt_row["autoVerifUjiTrendNote"] = $ujiTrendNote;
        $dt_row["autoVerifImage"] = $ujiTrendImage;
        $this->insert_or_update("auto_verif_v2", $dt_row, [
            "AutoVerifT_OrderDetailID",
            "AutoVerifIsActive",
        ]);
        $this->db->trans_commit();
        if ($debug != "") {
            print_r($dt_row);
            exit();
        }
        if (
            $this->isUpdate &&
            ($haveCriticalValue == "Y" ||
                $havePreAnalytical == "Y" ||
                $isInAmr == "N" ||
                $ujiTrendIsOK == "N")
        ) {
            return false;
        } else {
            $sql = "update t_orderdetail set T_OrderDetailVerification='Y'
                ,T_OrderDetailVerDate = now(), T_OrderDetailVerUserID = 1500,
                T_OrderDetailVerDeltaCheck='Y', T_OrderDetailVerTrendAnalysis='Y'
                where T_OrderDetailID = ? and T_OrderDetailVerification='X'";
            $qry = $this->db->query($sql, [$id]);
            if (!$qry) {
                $this->reply_error(
                    $this->db->error()["message"] .
                        "\n|" .
                        $this->db->last_query()
                );
                exit();
            }

            return true;
        }
    }
    function process_v2()
    {
        $param = $this->get_param();
        $ids = $param["ids"];
        $is_update = "N";
        if (isset($param["is_update"])) {
            $is_update = $param["is_update"];
        }
        if ($ids == "") {
            $this->reply(["Total Order" => 0]);
            exit();
        }
        $total = 0;
        $total_ok = 0;
        $total_not_ok = 0;
        $a_ids = explode(",", $ids);
        foreach ($a_ids as $id) {
            $isOk = $this->re_process($id);
            $total++;
            if ($isOk) {
                $total_ok++;
            } else {
                $total_not_ok++;
            }
        }
        $this->reply([
            "total" => $total,
            "total_ok" => $total_ok,
            "total_not_ok" => $total_not_ok,
        ]);
    }
    function process()
    {
        // AMR = review range
        $param = $this->get_param();
        $ids = $param["ids"];
        $is_update = "N";
        if (isset($param["is_update"])) {
            $is_update = $param["is_update"];
        }
        if ($ids == "") {
            $this->reply(["Total Order" => 0]);
            exit();
        }
        $sql = "select T_OrderDetailID, 
        if(T_TestIsDeltaCheck = 'Y', fn_process_re_deltacheck_v2(T_OrderDetailID), '')  delta_check,
        if(T_TestIsTrendAnalysis = 'Y', fn_process_re_trendanalysis_v2(T_OrderDetailID),'') trend_analysis, 
        ifnull(T_OrderReqStatus,'Y') T_OrderReqStatus,
        T_TestIsDeltaCheck,
        T_TestIsTrendAnalysis,
        T_TestNat_TestID
        from t_orderdetail
        join t_test on T_OrderDetailIsActive = 'Y'
          and T_OrderDetailT_TestID = T_TestID 
        left join t_orderreq on T_OrderDetailT_OrderHeaderID = T_OrderReqT_OrderHeaderID 
          and T_OrderReqIsActive = 'Y'
          
        where T_OrderDetailID in ( $ids ) 
        and T_OrderDetailID not in (select AutoVerifT_OrderDetailID from auto_verif where AutoVerifIsActive = 'Y' )";
        $qry = $this->db->query($sql);
        if (!$qry) {
            $this->reply_error(
                $this->db->error()["message"] . "\n|" . $this->db->last_query()
            );
            exit();
        }
        $rows = $qry->result_array();
        $tot_ok = 0;
        $tot_not_ok = 0;
        $tot_req = 0;
        $tot_no_dc = 0;
        $tot_no_ta = 0;

        foreach ($rows as $r) {
            $haveReq = "N";
            if ($r["T_OrderReqStatus"] != "Y") {
                $haveReq = "Y";
            }

            if ($r["T_TestIsDeltaCheck"] == "N") {
                $dc = [
                    "status" => "X",
                    "note" => "T_TestIsDeltaCheck not active",
                    "lastID" => 0,
                    "lastResult" => "",
                ];
            } else {
                $dc = json_decode($r["delta_check"], true);
            }
            if ($r["T_TestIsDeltaCheck"] == "N") {
                $ta = [
                    "status" => "X",
                    "note" => "T_TestIsTrendAnalysis not active",
                    "image" => "",
                ];
            } else {
                $ta = json_decode($r["trend_analysis"], true);
            }
            //add amr check
            $data = [
                "AutoVerifT_OrderDetailID" => $r["T_OrderDetailID"],
                "AutoVerifHaveReq" => $haveReq,
                "AutoVerifIsT_TestIsDeltaCheck" => $r["T_TestIsDeltaCheck"],
                "AutoVerifIsT_TestIsTrendAnalysis" =>
                    $r["T_TestIsTrendAnalysis"],
                "AutoVerifDeltaCheckStatus" => $dc["status"],
                "AutoVerifDeltaCheckNote" => $dc["note"],
                "AutoVerifDeltaCheckLastID" => $dc["lastID"],
                "AutoVerifDeltaCheckLastResult" => $dc["lastResult"],
                "AutoVerifTrendAnalysisStatus" => $ta["status"],
                "AutoVerifTrendAnalysisNote" => $ta["note"],
                "AutoVerifTrendAnalysisImage" => $ta["image"],
                "AutoVerifReviewRangeIsOK" => $ta["review_range"],
            ];
            $qry = $this->db->insert("auto_verif", $data);
            if (!$qry) {
                $this->reply_error(
                    $this->db->error()["message"] .
                        "\n|" .
                        $this->db->last_query()
                );
                exit();
            }
            if ($haveReq == "Y") {
                $tot_req++;
            } elseif ($dc["status"] == "Y" && $ta["status"] == "Y") {
                $tot_ok++;
            } else {
                $tot_not_ok++;
            }
            if ($r["T_TestIsDeltaCheck"] == "N") {
                $tot_no_dc++;
            }
            if ($r["T_TestIsTrendAnalysis"] == "N") {
                $tot_no_ta++;
            }
            if (
                $haveReq == "N" &&
                //$dc["status"] == "Y" &&
                $ta["status"] == "Y" &&
                $ta["review_range"] == "Y"
            ) {
                $sql = "update t_orderdetail set T_OrderDetailVerification='Y'
                ,T_OrderDetailVerDate = now(), T_OrderDetailVerUserID = 1500,
                T_OrderDetailVerDeltaCheck='Y', T_OrderDetailVerTrendAnalysis='Y'
                where T_OrderDetailID = ? and T_OrderDetailVerification='X'";
                $qry = $this->db->query($sql, [$r["T_OrderDetailID"]]);
                if (!$qry) {
                    $this->reply_error(
                        $this->db->error()["message"] .
                            "\n|" .
                            $this->db->last_query()
                    );
                    exit();
                }
            }
        }
        $this->reply([
            "total" => count($rows),
            "no_delta_check" => $tot_no_dc,
            "no_trendanalysis" => $tot_no_ta,
            "requirement" => $tot_req,
            "ok" => $tot_ok,
            "not_ok" => $tot_not_ok,
        ]);
    }
    function order_v2($date = "")
    {
        if ($date == "") {
            $date = "2021-07-21";
        }

        $start_date = $date . " 00:00:01";
        $end_date = $date . " 23:59:59";

        $sql = "select 
        T_OrderHeaderID, T_OrderHeaderDate, T_OrderHeaderLabNumber, 
        group_concat(T_TestName) TestName,
        group_concat(T_OrderDetailID) OrderID
        from t_orderheader 
        join t_orderdetail on T_OrderHeaderDate >= ? and T_OrderHeaderDate < ?
          and T_OrderHeaderID = T_OrderDetailT_OrderHeaderID
          and T_OrderHeaderIsActive = 'Y'
          and T_OrderDetailIsActive = 'Y'
          and T_OrderDetailID not in (
            select AutoVerifT_OrderDetailID
            from auto_verif_v2
            where AutoVerifIsActive = 'Y'
          )
    join t_test on T_OrderDetailT_TestID = T_TestID and T_TestIsResult = 'Y'
        and T_TestNat_GroupID = 1
        where T_OrderDetailResult <> '' 
        group by T_OrderHeaderID, T_OrderHeaderDate, T_OrderHeaderLabNumber
        limit 0,20
        ";
        $qry = $this->db->query($sql, [$start_date, $end_date]);
        if (!$qry) {
            $this->reply_error(
                $this->db->error()["message"] . "\n|" . $this->db->last_query()
            );
            exit();
        }
        $rows = $qry->result_array();
        $this->reply($rows);
    }
    function order($date = "")
    {
        if ($date == "") {
            $date = "2021-07-21";
        }

        $start_date = $date . " 00:00:01";
        $end_date = $date . " 23:59:59";

        $sql = "select 
        T_OrderHeaderID, T_OrderHeaderDate, T_OrderHeaderLabNumber, 
        group_concat(T_TestName) TestName,
        group_concat(T_OrderDetailID) OrderID
        from t_orderheader 
        join t_orderdetail on T_OrderHeaderDate >= ? and T_OrderHeaderDate < ?
          and T_OrderHeaderID = T_OrderDetailT_OrderHeaderID
          and T_OrderHeaderIsActive = 'Y'
          and T_OrderDetailIsActive = 'Y'
          and T_OrderDetailID not in (
            select AutoVerifT_OrderDetailID
            from auto_verif 
            where AutoVerifIsActive = 'Y'
          )
        join t_test on T_OrderDetailT_TestID = T_TestID and T_TestIsResult = 'Y'
        where T_OrderDetailResult <> '' 
        group by T_OrderHeaderID, T_OrderHeaderDate, T_OrderHeaderLabNumber
        limit 0,100
        ";
        $qry = $this->db->query($sql, [$start_date, $end_date]);
        if (!$qry) {
            $this->reply_error(
                $this->db->error()["message"] . "\n|" . $this->db->last_query()
            );
            exit();
        }
        $rows = $qry->result_array();
        $this->reply($rows);
    }

    function reply_error($message)
    {
        echo json_encode(["status" => "ERR", "message" => $message]);
    }
    function reply($data)
    {
        echo json_encode(["status" => "OK", "data" => $data]);
    }

    public function print_table_style()
    {
        echo "
        <style>
        th, td {
            padding: 15px;
            text-align: left;
          }
          tr:nth-child(even) {background-color: #f2f2f2;}
          tr.green {background-color: #00ee00;}
          table {
            border: solid 1px ;
            min-width:600px;
            margin-left:auto;
            margin-right:auto;
          }
        </style>
        ";
    }

    public function print_table($rows, $keys)
    {
        $this->print_table_style();
        echo "<table>";
        echo "<tr>";
        foreach ($keys as $k) {
            echo "<td>$k</td>";
        }
        echo "</tr>\n";
        foreach ($rows as $r) {
            if (
                $r["HaveRequirement"] == "N" &&
                $r["AutoVerifDeltaCheckStatus"] == "Y" &&
                $r["AutoVerifTrendAnalysisStatus"] === "Y"
            ) {
                echo "<tr class='green'>";
            } else {
                echo "<tr>";
            }
            foreach ($keys as $k) {
                echo "<td>" . $r[$k] . "</td>";
            }
            echo "</tr>";
        }
        echo "</table>";
    }
    function insert_or_update($table, $data, $key)
    {
        $s_where = "";
        foreach ($key as $k) {
            if ($s_where != "") {
                $s_where .= " and ";
            }
            $s_where .= " $k = '" . $data[$k] . "' ";
        }
        $this->db->trans_begin();
        $sql = "select " . $key[0] . " from $table " . " where $s_where ";
        $qry = $this->db->query($sql, $key);
        if (!$qry) {
            $this->reply_error(
                "Error Insert/Update $table Check | " .
                    $this->db->error()["message"] .
                    "\n" .
                    $this->db->last_query()
            );
            $this->db->trans_rollback();
            exit();
        }
        $rows = $qry->result_array();
        if (count($rows) > 0) {
            foreach ($key as $k) {
                $this->db->where($k, $data[$k]);
            }
            $qry = $this->db->update($table, $data);
            $state = "U";
        } else {
            $qry = $this->db->insert($table, $data);
            $state = "I";
        }
        if (!$qry) {
            $this->reply_error(
                "Error Insert/Update $table | " .
                    $this->db->error()["message"] .
                    "\n" .
                    $this->db->last_query()
            );
            $this->db->trans_rollback();
            exit();
        }
        $this->db->trans_commit();
        return $state;
    }
}