<?php
class Nattestmap extends MY_Controller
{
    var $db;
    public function index()
    {
        echo "NAT TEST MAP API";
    }
    public function __construct()
    {
        parent::__construct();
        // $this->db_onedev = $this->load->database("onedev", true);
    }

    function search()
    {
        try {
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }
            $prm = $this->sys_input;
            $search = "";
            if (isset($prm['search'])) {
                $search = trim($prm["search"]);
                if ($search != "") {
                    $search = '%' . $prm['search'] . '%';
                } else {
                    $search = '%%';
                }
            }

            $status = $prm['status'];
            // $all = $prm['all'];
            $filter = '';
            if ($status != 'A') {
                $filter .= "AND status = '{$status}' ";
            } else {
                $filter .= "";
            }

            $number_offset = 0;
            $number_limit = 10;
            if ($prm["current_page"] > 0) {
                $number_offset = ($prm["current_page"] - 1) * $number_limit;
            }

            $total_result = 0;
            $total_belum_result = 0;
            $total_price = 0;
            $total_belum_price = 0;
            $sql_result = "SELECT COUNT(*) AS total FROM
                (SELECT 
                IF(ISNULL(Nat_TestMapID),'N','Y') as status,
                Nat_TestID,
                Nat_TestCode,
                Nat_TestName,
                Nat_TestMapID,
                Nat_TestMapNat_TestID,
                Nat_TestMapCode,
                IFNULL(Nat_TestMapCode, '') AS TestMapCode
                FROM nat_test
                LEFT JOIN nat_testmap ON Nat_TestID = Nat_TestMapNat_TestID
                AND Nat_TestMapIsActive = 'Y'
                WHERE Nat_TestIsActive = 'Y'
                AND Nat_TestIsResult = 'Y') x";
            $qry_result = $this->db->query($sql_result);
            if ($qry_result) {
                $total_result = $qry_result->result_array()[0]["total"];
            } else {
                $this->sys_error_db("nat_test count", $this->db);
                exit;
            }

            $sql_belum_result = "SELECT COUNT(*) AS total FROM
                (SELECT 
                IF(ISNULL(Nat_TestMapID),'N','Y') as status,
                Nat_TestID,
                Nat_TestCode,
                Nat_TestName,
                Nat_TestMapID,
                Nat_TestMapNat_TestID,
                Nat_TestMapCode,
                IFNULL(Nat_TestMapCode, '') AS TestMapCode
                FROM nat_test
                LEFT JOIN nat_testmap ON Nat_TestID = Nat_TestMapNat_TestID
                AND Nat_TestMapIsActive = 'Y'
                WHERE Nat_TestIsActive = 'Y'
                AND Nat_TestIsResult = 'Y') x
                WHERE status = 'N'";
            $qry_belum_result = $this->db->query($sql_belum_result);
            if ($qry_belum_result) {
                $total_belum_result = $qry_belum_result->result_array()[0]["total"];
            } else {
                $this->sys_error_db("nat_test count", $this->db);
                exit;
            }

            $sql_price = "SELECT COUNT(*) AS total FROM
                (SELECT 
                IF(ISNULL(Nat_TestMapID),'N','Y') as status,
                Nat_TestID,
                Nat_TestCode,
                Nat_TestName,
                Nat_TestMapID,
                Nat_TestMapNat_TestID,
                Nat_TestMapCode,
                IFNULL(Nat_TestMapCode, '') AS TestMapCode
                FROM nat_test
                LEFT JOIN nat_testmap ON Nat_TestID = Nat_TestMapNat_TestID
                AND Nat_TestMapIsActive = 'Y'
                WHERE Nat_TestIsActive = 'Y'
                AND Nat_TestIsPrice = 'Y') x";
            $qry_price = $this->db->query($sql_price);
            if ($qry_price) {
                $total_price = $qry_price->result_array()[0]["total"];
            } else {
                $this->sys_error_db("nat_test count", $this->db);
                exit;
            }

            $sql_belum_price = "SELECT COUNT(*) AS total FROM
                (SELECT 
                IF(ISNULL(Nat_TestMapID),'N','Y') as status,
                Nat_TestID,
                Nat_TestCode,
                Nat_TestName,
                Nat_TestMapID,
                Nat_TestMapNat_TestID,
                Nat_TestMapCode,
                IFNULL(Nat_TestMapCode, '') AS TestMapCode
                FROM nat_test
                LEFT JOIN nat_testmap ON Nat_TestID = Nat_TestMapNat_TestID
                AND Nat_TestMapIsActive = 'Y'
                WHERE Nat_TestIsActive = 'Y'
                AND Nat_TestIsPrice = 'Y') x
                WHERE status = 'N'";
            $qry_belum_price = $this->db->query($sql_belum_price);
            if ($qry_belum_price) {
                $total_belum_price = $qry_belum_price->result_array()[0]["total"];
            } else {
                $this->sys_error_db("nat_test count", $this->db);
                exit;
            }


            $sql_tot = "SELECT COUNT(*) AS total FROM
                (SELECT 
                IF(ISNULL(Nat_TestMapID),'N','Y') as status,
                Nat_TestID,
                Nat_TestCode,
                Nat_TestName,
                Nat_TestMapID,
                Nat_TestMapNat_TestID,
                Nat_TestMapCode,
                IFNULL(Nat_TestMapCode, '') AS TestMapCode
                FROM nat_test
                LEFT JOIN nat_testmap ON Nat_TestID = Nat_TestMapNat_TestID
                AND Nat_TestMapIsActive = 'Y'
                WHERE Nat_TestIsActive = 'Y'
                AND (Nat_TestIsResult = 'Y'
                OR Nat_TestIsPrice = 'Y')) x
                WHERE (Nat_TestCode LIKE ? OR Nat_TestName LIKE ? OR TestMapCode LIKE ?) $filter
                ";
            $qry_tot = $this->db->query($sql_tot, [$search, $search, $search]);
            $tot_count = 0;
            $tot_page = 0;
            if ($qry_tot) {
                $tot_count = $qry_tot->result_array()[0]["total"];
                $tot_page = ceil($tot_count / $number_limit);
            } else {
                $this->sys_error_db("nat_test count", $this->db);
                exit;
            }

            $sql = "SELECT * FROM
                (SELECT 
                IF(ISNULL(Nat_TestMapID),'N','Y') as status,
                Nat_TestID,
                Nat_TestCode,
                Nat_TestName,
                Nat_TestMapID,
                Nat_TestMapNat_TestID,
                Nat_TestMapCode,
                IFNULL(Nat_TestMapCode, '') AS TestMapCode
                FROM nat_test
                LEFT JOIN nat_testmap ON Nat_TestID = Nat_TestMapNat_TestID
                AND Nat_TestMapIsActive = 'Y'
                WHERE Nat_TestIsActive = 'Y'
                AND (Nat_TestIsResult = 'Y'
                OR Nat_TestIsPrice = 'Y')) x
                WHERE (Nat_TestCode LIKE ? OR Nat_TestName LIKE ? OR TestMapCode LIKE ?)
                $filter
                ORDER BY Nat_TestCode ASC
                LIMIT ? OFFSET ?";
            $qry = $this->db->query($sql, [$search, $search, $search, $number_limit, $number_offset]);
            // echo $this->db->last_query();
            // exit;
            if ($qry) {
                $rows = $qry->result_array();
            } else {
                $this->sys_error_db("select nat_test error", $this->db);
                exit;
            }

            $result = array(
                "total_result" => $total_result,
                "total_belum_result" => $total_belum_result,
                "total_price" => $total_price,
                "total_belum_price" => $total_belum_price,
                "total_page" => $tot_page,
                "total_filter" => $tot_count,
                "records" => $rows
            );
            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }

    function saverow()
    {
        try {
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }

            $this->db->trans_begin();
            $prm = $this->sys_input;
            $userid = $this->sys_user["M_UserID"];

            if ($prm["Nat_TestMapID"]) {
                // print_r("sudah ada sample id");
                // exit;
                $sql = "UPDATE nat_testmap SET
                    Nat_TestMapCode = '{$prm["codeMapping"]}',
                    Nat_TestMapLastUpdated = NOW(),
                    Nat_TestMapUserID = '{$userid}'
                    WHERE Nat_TestMapID = {$prm["Nat_TestMapID"]}";
                $qry = $this->db->query($sql);
                if (!$qry) {
                    $this->db->trans_rollback();
                    $this->sys_error_db("update nat_testmap error", $this->db);
                    exit;
                }

                $sql_selec_del = "SELECT Nat_TestMapID,
                    Nat_TestMapCode
                    FROM nat_testmap 
                    WHERE Nat_TestMapIsActive = 'Y'
                    AND Nat_TestMapID = {$prm["Nat_TestMapID"]}";
                $row = $this->db->query($sql_selec_del)->row_array();

                if ($row["Nat_TestMapCode"] == "") {
                    $sql_del = "DELETE FROM nat_testmap
                    WHERE Nat_TestMapID = {$prm["Nat_TestMapID"]}";
                    $qry_del = $this->db->query($sql_del);
                    if (!$qry_del) {
                        $this->db->trans_rollback();
                        $this->sys_error_db("delete nat_testmap error", $this->db);
                        exit;
                    }
                }
            } else {
                // print_r("belum ada sample id");
                // exit;
                $sql = "INSERT INTO nat_testmap(
                    Nat_TestMapNat_TestID,
                    Nat_TestMapCode,
                    Nat_TestMapCreated,
                    Nat_TestMapLastUpdated,
                    Nat_TestMapUserID
                    ) VALUES(
                    {$prm["Nat_TestID"]},
                    '{$prm["codeMapping"]}',
                    NOW(),
                    NOW(),
                    {$userid})";
                $qry = $this->db->query($sql);
                if (!$qry) {
                    $this->db->trans_rollback();
                    $this->sys_error_db("nat_testmap insert 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);
        }
    }
}