<?php
class Etlreport extends MY_Controller
{
    var $db;
    public function __construct()
    {
        parent::__construct();
    }

    public function index()
    {
        // $cek = $this->db->query("select database() as current_db")->result();
        // print_r($cek);
        echo "KELAINAN LAB API";
    }

    function search()
    {
        try {
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }
            $prm = $this->sys_input;
            $userid = $this->sys_user['M_UserID'];

            $search = "";
            if (isset($prm["search"])) {
                $search = trim($prm["search"]);
                if ($search != "") {
                    $search = "%" . $prm["search"] . "%";
                } else {
                    $search = "%%";
                }
            }

            $number_offset = 0;
            $number_limit = 10;

            if ($prm["current_page"] > 0) {
                $number_offset = ($prm["current_page"] - 1) * $number_limit;
            }

            $sql_total = "SELECT count(*) as total
                    FROM etl_report
                    JOIN r_report ON R_ReportID = Etl_ReportR_ReportID
                    WHERE Etl_ReportIsActive = 'Y' 
                    AND (CONCAT(R_ReportCode,' ',R_ReportName) LIKE ?)";
            $qry_total = $this->db->query($sql_total, [$search]);
            $last_qry = $this->db->last_query();
            $tot_count = 0;
            $tot_page = 0;
            if ($qry_total) {
                $tot_count = $qry_total->result_array()[0]["total"];
                $tot_page = ceil($tot_count / $number_limit);
            } else {
                $this->db->trans_rollback();
                $message['last_qry'] = $last_qry;
                $this->sys_error_db("etl_report count error", $this->db);
                exit;
            }

            $sql = "SELECT etl_report.*,
                    R_ReportCode,
                    R_ReportName,
                    DATE_FORMAT(Etl_ReportRequestDate,'%d-%m-%Y %h:%i:%s') as request_date,
                    DATE_FORMAT(Etl_ReportComplDate,'%d-%m-%Y %h:%i:%s') as compl_date,
                    '' as param_data,
                    CONCAT(R_ReportCode,' ',R_ReportName) as codeNameReport,
                    CASE  
                    WHEN Etl_ReportStatus = 'N' THEN 'Baru'
                    ELSE ''
                    END as status_etl
                    FROM etl_report
                    JOIN r_report ON R_ReportID = Etl_ReportR_ReportID
                    WHERE Etl_ReportIsActive = 'Y' 
                    AND (CONCAT(R_ReportCode,' ',R_ReportName) LIKE ?)
                    ORDER BY Etl_ReportID DESC
                    limit ? offset ?";

            $qry = $this->db->query($sql, [$search, $number_limit, $number_offset]);
            $last_qry = $this->db->last_query();
            if (!$qry) {
                $message = $this->db->error();
                $message['last_qry'] = $last_qry;
                $this->sys_error_db("etl_report select error", $this->db);
                exit;
            }

            $rows = $qry->result_array();
            foreach ($rows as $k => $v) {
                $xno = ($k + 1) + $number_offset;
                $rows[$k]['rownumber'] = $xno;
            }


            $result = [
                "total_page" => $tot_page,
                "total" => $tot_count,
                "records" => $rows,
                "last_qry" => $last_qry
            ];
            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }

    function searchreport()
    {
        try {
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }
            $prm = $this->sys_input;
            $userid = $this->sys_user['M_UserID'];

            $number_limit = 10;
            $tot_count = 0;

            $search = "";
            if (isset($prm['search'])) {
                $search = trim($prm["search"]);
                if ($search != "") {
                    $search = '%' . $prm['search'] . '%';
                } else {
                    $search = '%%';
                }
            }

           
            $sql_search = "SELECT *,
                CONCAT(R_ReportCode,' ',R_ReportName) as codeNameReport
                FROM r_report
                JOIN etl_map_report ON Etl_MapReportR_ReportID = R_ReportID
                WHERE R_ReportIsActive = 'Y'
                AND (CONCAT(R_ReportCode,' ',R_ReportName) LIKE ?)
                LIMIT ?";
            $qry_search = $this->db->query($sql_search, [$search, $number_limit]);
            if ($qry_search) {
                $rows = $qry_search->result_array();
            } else {
                $this->db->trans_rollback();
                $this->sys_error_db("r_report select error", $this->db);
                exit;
            }

            $result = array(
                "total" => sizeof($rows),
                "total_display" => sizeof($rows),
                "records" => $rows
            );
            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }

    function getparam()
    {
        try {
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }
            $prm = $this->sys_input;
            $userid = $this->sys_user['M_UserID'];
            $id = $prm['id'];

            $sql_search = "SELECT *
                FROM r_reportdetail
                JOIN r_inputtype ON R_InputTypeID = R_ReportDetailR_InputTypeID
                WHERE
                R_ReportDetailIsActive = 'Y' AND
                R_ReportDetailR_ReportID = {$id}
                GROUP BY R_ReportDetailLabel,R_ReportDetailParam,R_ReportDetailSourceSp,R_InputTypeID";
            $qry_search = $this->db->query($sql_search);
            if ($qry_search) {
                $rows = $qry_search->result_array();
            } else {
                $this->db->trans_rollback();
                $this->sys_error_db("r_report select error", $this->db);
                exit;
            }

            $result = array(
                "total" => sizeof($rows),
                "total_display" => sizeof($rows),
                "records" => $rows
            );
            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }

    function newData(){
        try {
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }

            $prm = $this->sys_input;
            $userid = $this->sys_user['M_UserID']; 
            $nat_test = $prm['nat_test'];
            $kelainan_id = $prm['kelainan']['Mcu_KelainanID'];
            $type = $prm['type']['id'];
            
            $Mcu_SummaryLabGender = $prm['gender']['id'];
            $Mcu_SummaryLabType = '';
            $Mcu_SummaryLabIsNormalValue = 'N';
            $Mcu_SummaryLabIsFixValue = 'N';
            $Mcu_SummaryLabValue = '';
            $Mcu_SummaryLabIsRange = 'N';
            $Mcu_SummaryLabMinInclusive = 'N';
            $Mcu_SummaryLabMinValue = 0;
            $Mcu_SummaryLabMaxInclusive = 'N';
            $Mcu_SummaryLabMaxValue = 0;
            if($type == "range"){
                $Mcu_SummaryLabIsRange = 'Y';
                $Mcu_SummaryLabMinInclusive = $prm['min_inclusive'] == true ? 'Y':'N';
                $Mcu_SummaryLabMinValue = $prm['min_value'];
                $Mcu_SummaryLabMaxInclusive = $prm['max_inclusive'] == true ? 'Y':'N';
                $Mcu_SummaryLabMaxValue = $prm['max_value'];
            }

            if($type == "fixvalue"){
                $Mcu_SummaryLabIsFixValue = "Y";
                $Mcu_SummaryLabValue = $prm['value_pembanding'];
            }

            if($type == "nomalvalue"){
                $Mcu_SummaryLabIsNormalValue = "Y";
            }

            if($type != "range"){
                switch ($prm['operator_pembanding']['id']) {
                    case "KD":
                        $Mcu_SummaryLabType = '<';
                        break;
                    case "LD":
                        $Mcu_SummaryLabType = '>';
                        break;
                    case "SD":
                        $Mcu_SummaryLabType = '=';
                        break;
                    case "KDSD":
                        $Mcu_SummaryLabType = '<=';
                        break;
                    case "LDSD":
                        $Mcu_SummaryLabType = '>=';
                        break;
                    case "TSD":
                        $Mcu_SummaryLabType = '!=';
                        break;
                    default:
                        $Mcu_SummaryLabType = '';
                }
            }

            $sql = "INSERT INTO mcu_summarylab (
                                Mcu_SummaryLabNat_TestID,
                                Mcu_SummaryLabNat_TestCode,
                                Mcu_summaryLabMcu_KelainanID,
                                Mcu_SummaryLabType,
                                Mcu_SummaryLabIsNormalValue,
                                Mcu_SummaryLabIsFixValue,
                                Mcu_SummaryLabIsRange,
                                Mcu_SummaryLabValue,
                                Mcu_SummaryLabGender,
                                Mcu_SummaryLabMinInclusive,
                                Mcu_SummaryLabMinValue,
                                Mcu_SummaryLabMaxInclusive,
                                Mcu_SummaryLabMaxValue,
                                Mcu_SummaryLabCreatedUserID,
                                Mcu_SummaryLabCreated
                            ) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,NOW())";
            $qry = $this->db->query($sql, [
                $nat_test['Nat_TestID'],
                $nat_test['Nat_TestCode'],
                $kelainan_id,
                $Mcu_SummaryLabType,
                $Mcu_SummaryLabIsNormalValue,
                $Mcu_SummaryLabIsFixValue,
                $Mcu_SummaryLabIsRange,
                $Mcu_SummaryLabValue,
                $Mcu_SummaryLabGender,
                $Mcu_SummaryLabMinInclusive,
                $Mcu_SummaryLabMinValue,
                $Mcu_SummaryLabMaxInclusive,
                $Mcu_SummaryLabMaxValue,
                $userid 
            ]);

            if(!$qry){
                $this->sys_error_db("gagal insert");
                exit;
            }

            $result = array(
                "status" => "OKE"
            );
            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }

    function editData(){
        try {
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }

            $prm = $this->sys_input;
            $userid = $this->sys_user['M_UserID']; 
            $xid = $prm['xid'];
            $nat_test = $prm['nat_test'];
            $kelainan_id = $prm['kelainan']['Mcu_KelainanID'];
            $type = $prm['type']['id'];
            
            $Mcu_SummaryLabGender = $prm['gender']['id'];
            $Mcu_SummaryLabType = '';
            $Mcu_SummaryLabIsNormalValue = 'N';
            $Mcu_SummaryLabIsFixValue = 'N';
            $Mcu_SummaryLabValue = '';
            $Mcu_SummaryLabIsRange = 'N';
            $Mcu_SummaryLabMinInclusive = 'N';
            $Mcu_SummaryLabMinValue = 0;
            $Mcu_SummaryLabMaxInclusive = 'N';
            $Mcu_SummaryLabMaxValue = 0;
            if($type == "range"){
                $Mcu_SummaryLabIsRange = 'Y';
                $Mcu_SummaryLabMinInclusive = $prm['min_inclusive'] == true ? 'Y':'N';
                $Mcu_SummaryLabMinValue = $prm['min_value'];
                $Mcu_SummaryLabMaxInclusive = $prm['max_inclusive'] == true ? 'Y':'N';
                $Mcu_SummaryLabMaxValue = $prm['max_value'];
            }

            if($type == "fixvalue"){
                $Mcu_SummaryLabIsFixValue = "Y";
                $Mcu_SummaryLabValue = $prm['value_pembanding'];
            }

            if($type == "nomalvalue"){
                $Mcu_SummaryLabIsNormalValue = "Y";
            }

            if($type != "range"){
                switch ($prm['operator_pembanding']['id']) {
                    case "KD":
                        $Mcu_SummaryLabType = '<';
                        break;
                    case "LD":
                        $Mcu_SummaryLabType = '>';
                        break;
                    case "SD":
                        $Mcu_SummaryLabType = '=';
                        break;
                    case "KDSD":
                        $Mcu_SummaryLabType = '<=';
                        break;
                    case "LDSD":
                        $Mcu_SummaryLabType = '>=';
                        break;
                    case "TSD":
                        $Mcu_SummaryLabType = '!=';
                        break;
                    default:
                        $Mcu_SummaryLabType = '';
                }
            }

            $sql = "UPDATE mcu_summarylab SET 
                                Mcu_SummaryLabNat_TestID = ?,
                                Mcu_SummaryLabNat_TestCode = ?,
                                Mcu_summaryLabMcu_KelainanID = ?,
                                Mcu_SummaryLabType = ?,
                                Mcu_SummaryLabIsNormalValue= ?,
                                Mcu_SummaryLabIsFixValue = ?,
                                Mcu_SummaryLabIsRange = ?,
                                Mcu_SummaryLabValue = ?,
                                Mcu_SummaryLabGender = ?,
                                Mcu_SummaryLabMinInclusive = ?,
                                Mcu_SummaryLabMinValue= ?,
                                Mcu_SummaryLabMaxInclusive = ?,
                                Mcu_SummaryLabMaxValue = ?,
                                Mcu_SummaryLabLastUpdatedUserID = ?,
                                Mcu_SummaryLabLastUpdated = NOW()
                    WHERE
                    Mcu_SummaryLabID = ?";
            $qry = $this->db->query($sql, [
                $nat_test['Nat_TestID'],
                $nat_test['Nat_TestCode'],
                $kelainan_id,
                $Mcu_SummaryLabType,
                $Mcu_SummaryLabIsNormalValue,
                $Mcu_SummaryLabIsFixValue,
                $Mcu_SummaryLabIsRange,
                $Mcu_SummaryLabValue,
                $Mcu_SummaryLabGender,
                $Mcu_SummaryLabMinInclusive,
                $Mcu_SummaryLabMinValue,
                $Mcu_SummaryLabMaxInclusive,
                $Mcu_SummaryLabMaxValue,
                $userid,
                $xid
            ]);

            if(!$qry){
                $this->sys_error_db("gagal update");
                exit;
            }

            $result = array(
                "status" => "OKE"
            );
            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }

    function searchkelainan()
    {
        try {
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }
            $prm = $this->sys_input;
            $userid = $this->sys_user['M_UserID'];

            $number_limit = 10;
            $tot_count = 0;

            $search = "";
            if (isset($prm['search'])) {
                $search = trim($prm["search"]);
                if ($search != "") {
                    $search = '%' . $prm['search'] . '%';
                } else {
                    $search = '%%';
                }
            }

            $sql_total = "SELECT count(*) as total
                    FROM mcu_kelainan
                WHERE Mcu_KelainanIsActive = 'Y'
                AND (Mcu_KelainanName LIKE ?)
                LIMIT ?";
            $qry_total = $this->db->query($sql_total, [$search, $number_limit]);
            if ($qry_total) {
                $tot_count = $qry_total->result_array()[0]["total"];
            } else {
                $this->db->trans_rollback();
                $this->sys_error_db("kelainan count");
                exit;
            }

            $sql_search = "SELECT Mcu_KelainanID,
                Mcu_KelainanName,
                Mcu_KelainanClasification
                FROM mcu_kelainan
                WHERE Mcu_KelainanIsActive = 'Y'
                AND (Mcu_KelainanName LIKE ?)
                LIMIT ?";
            $qry_search = $this->db->query($sql_search, [$search, $number_limit]);
            if ($qry_search) {
                $rows = $qry_search->result_array();
            } else {
                $this->db->trans_rollback();
                $this->sys_error_db("kelainan select error", $this->db);
                exit;
            }

            $result = array(
                "total" => $tot_count,
                "total_display" => sizeof($rows),
                "records" => $rows
            );
            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }

    function add()
    {
        try {
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }
            $this->db->trans_begin();
            $prm = $this->sys_input;
            $userid = $this->sys_user['M_UserID'];

            $testId = "";
            if (isset($prm["testId"])) {
                $testId = trim($prm["testId"]);
            }
            $kelainanId = "";
            if (isset($prm["kelainanId"])) {
                $kelainanId = trim($prm["kelainanId"]);
            }
            $testCode = "";
            if (isset($prm["testCode"])) {
                $testCode = trim($prm["testCode"]);
            }
            $type = "";
            if (isset($prm["type"])) {
                $type = trim($prm["type"]);
            }
            $normalValue = "";
            if (isset($prm["normalValue"])) {
                $normalValue = trim($prm["normalValue"]);
            }
            $value = "";
            if (isset($prm["value"])) {
                $value = trim($prm["value"]);
            }

            $sql_exist = "SELECT Mcu_SummaryLabID,
                Mcu_SummaryLabNat_TestID,
                Mcu_summaryLabMcu_KelainanID
                FROM mcu_summarylab
                WHERE Mcu_SummaryLabIsActive = 'Y'
                AND Mcu_SummaryLabNat_TestID = ?
                AND Mcu_summaryLabMcu_KelainanID = ?";
            $qry_exist = $this->db->query($sql_exist, [$testId, $kelainanId]);
            if ($qry_exist) {
                $tot_exist = $qry_exist->result_array();
            } else {
                $this->db->trans_rollback();
                $this->sys_error_db("mcu_summarylab count");
                exit;
            }

            if (count($tot_exist) > 0) {
                echo json_encode(["status" => "OK", "flag" => true, "msg" => "Tidak bisa disimpan karena ada kelainan yang sama dengan pemeriksaan yang sudah ada, silahkan pilih kelainan lainnya!"]);
                exit;
            }

            $datatype = "";
            switch ($type) {
                case "KD":
                    $datatype = '<';
                    break;
                case "LD":
                    $datatype = '>';
                    break;
                case "SD":
                    $datatype = '=';
                    break;
                case "KDSD":
                    $datatype = '<=';
                    break;
                case "LDSD":
                    $datatype = '>=';
                    break;
                case "TSD":
                    $datatype = '!=';
                    break;
                default:
                    $datatype = '';
            }

            $sql = "INSERT INTO mcu_summarylab(
                Mcu_SummaryLabNat_TestID,
                Mcu_SummaryLabNat_TestCode,
                Mcu_summaryLabMcu_KelainanID,
                Mcu_SummaryLabType,
                Mcu_SummaryLabIsNormalValue,
                Mcu_SummaryLabValue,
                Mcu_SummaryLabIsActive,
                Mcu_SummaryLabUserID,
                Mcu_SummaryLabCreated,
                Mcu_SummaryLabLastUpdated
                ) VALUES(?,?,?,?,?,?,'Y',?,NOW(),NOW())";
            $qry = $this->db->query($sql, array(
                $testId,
                $testCode,
                $kelainanId,
                $datatype,
                $normalValue,
                $value,
                $userid
            ));
            if (!$qry) {
                $this->db->trans_rollback();
                $this->sys_error_db("save mcu_summarylab error", $this->db);
                exit;
            }

            $this->db->trans_commit();
            $result = array(
                "total" => 1,
                "affected_rows" => $this->db->affected_rows()
            );
            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }

    function edit()
    {
        try {
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }
            $this->db->trans_begin();
            $prm = $this->sys_input;
            $userid = $this->sys_user['M_UserID'];

            $xid = "";
            if (isset($prm["xid"])) {
                $xid = trim($prm["xid"]);
            }
            $type = "";
            if (isset($prm["type"])) {
                $type = trim($prm["type"]);
            }
            $normalValue = "";
            if (isset($prm["normalValue"])) {
                $normalValue = trim($prm["normalValue"]);
            }
            $value = "";
            if (isset($prm["value"])) {
                $value = trim($prm["value"]);
            }

            $datatype = "";
            switch ($type) {
                case "KD":
                    $datatype = '<';
                    break;
                case "LD":
                    $datatype = '>';
                    break;
                case "SD":
                    $datatype = '=';
                    break;
                case "KDSD":
                    $datatype = '<=';
                    break;
                case "LDSD":
                    $datatype = '>=';
                    break;
                case "TSD":
                    $datatype = '!=';
                    break;
                default:
                    $datatype = '';
            }

            $sql = "UPDATE mcu_summarylab SET 
                Mcu_SummaryLabType = ?,
                Mcu_SummaryLabIsNormalValue = ?,
                Mcu_SummaryLabValue = ?,
                Mcu_SummaryLabUserID = ?,
                Mcu_SummaryLabLastUpdated = NOW()
                WHERE Mcu_SummaryLabID = ?";
            $qry = $this->db->query($sql, [
                $datatype,
                $normalValue,
                $value,
                $userid,
                $xid
            ]);
            if (!$qry) {
                $this->db->trans_rollback();
                $this->sys_error_db("update mcu_summarylab error", $this->db);
                exit;
            }

            $this->db->trans_commit();
            $result = array(
                "total" => 1,
                "records" => array("xid" => 0)
            );
            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }

    function deleterow()
    {
        try {
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }
            $this->db->trans_begin();
            $prm = $this->sys_input;
            $userid = $this->sys_user['M_UserID'];

            $xid = "";
            if (isset($prm["xid"])) {
                $xid = trim($prm["xid"]);
            }

            $sql = "UPDATE mcu_summarylab SET 
                Mcu_SummaryLabIsActive = 'N',
                Mcu_SummaryLabUserID = ?,
                Mcu_SummaryLabLastUpdated = NOW()
                WHERE Mcu_SummaryLabID = ?";
            $qry = $this->db->query($sql, [
                $userid,
                $xid
            ]);
            if (!$qry) {
                $this->db->trans_rollback();
                $this->sys_error_db("delete mcu_summarylab error", $this->db);
                exit;
            }

            $this->db->trans_commit();
            $result = array(
                "total" => 1,
                "records" => array("xid" => 0)
            );
            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }
}