<?php

class Fisiktemplate extends MY_Controller
{
    public function __construct()
    {
        parent::__construct();

        $current_method = $this->router->fetch_method();
        $excluded_methods = ['index'];

        if (!in_array($current_method, $excluded_methods)) {
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }
        }
    }

    public function index()
    {
        echo "Fisik Template Api";
    }

    public function fisikTemplateMappingList()
    {
        if (! $this->isLogin) {
            $this->sys_error("Invalid Token");
            exit;
        }
        $prm = $this->sys_input;
        $fisikname = isset($prm['fisikname']) ? $prm['fisikname'] : '';
        $current_page = isset($prm['current_page']) ? (int) $prm['current_page'] : 1;
        $status = isset($prm['status']) ? $prm['status'] : null;

        $number_limit = 10;
        $number_offset = ($current_page - 1) * $number_limit;

        try {
            $this->db->select('FisikTemplateMappingID');
            $this->db->from('fisik_template_mapping');
            $this->db->where('FisikTemplateMappingIsActive', 'Y');
            $this->db->like('FisikTemplateMappingName', $fisikname);
            $this->db->group_by('FisikTemplateMappingID');
            $count_query = $this->db->get();

            if (!$count_query) {
                $this->sys_error_db("fisik_template_mapping count", $this->db);
                return;
            }

            $tot_count = $count_query->num_rows();
            $tot_page = ceil($tot_count / $number_limit);

            $this->db->select('FisikTemplateMappingID as id, FisikTemplateMappingID, FisikTemplateMappingName');
            $this->db->from('fisik_template_mapping');
            $this->db->where('FisikTemplateMappingIsActive', 'Y');
            $this->db->like('FisikTemplateMappingName', $fisikname);
            $this->db->group_by('FisikTemplateMappingID');
            $this->db->order_by('FisikTemplateMappingName', 'ASC');
            $this->db->limit($number_limit, $number_offset);
            $query = $this->db->get();

            if (!$query) {
                $this->sys_error_db("fisik_template_mapping select", $this->db);
                return;
            }

            $rows = $query->result_array();

            $this->sys_ok([
                "total" => $tot_page,
                "total_filter" => count($rows),
                "records" => $rows
            ]);
        } catch (Exception $exc) {
            $this->sys_error($exc->getMessage());
        }
    }

    function addFisikTemplateMapping()
    {
        if (! $this->isLogin) {
            $this->sys_error("Invalid Token");
            exit;
        }
        $prm = $this->sys_input;
        $userid = isset($this->sys_user['M_UserID']) ? $this->sys_user['M_UserID'] : null;
        $name = isset($prm['name']) ? trim($prm['name']) : '';
        $nat_test_id = isset($prm['nat_test_id']) ? $prm['nat_test_id'] : null;

        if (empty($name)) {
            $this->sys_error("Nama fisik tidak boleh kosong.");
            return;
        }

        $this->db->trans_begin();

        try {
            $insert = $this->db->insert('fisik_template_mapping', [
                'FisikTemplateMappingName' => $name,
                'FisikTemplateMappingCreated' => date('Y-m-d H:i:s'),
                'FisikTemplateMappingCreatedUserID' => $userid,
                'FisikTemplateMappingLastUpdated' => date('Y-m-d H:i:s'),
            ]);

            if (!$insert) {
                $this->db->trans_rollback();
                $this->sys_error_db("save fisik_template_mapping error", $this->db);
                return;
            }

            $last_id = $this->db->insert_id();

            /*$sql = "UPDATE fisik_map_test_template 
                    SET Fisik_MapTestTemplateIsActive = 'N',
                    Fisik_MapTestTemplateDeleted = NOW(),
                    Fisik_MapTestTemplateDeletedUserID = ?
                WHERE 
                    Fisik_MapTestTemplateNat_TestID = ? AND 
                    Fisik_MapTestTemplateIsActive = 'Y'";
            $this->db->query($sql, [$nat_test_id, $userid]);

            $sql = "INSERT INTO fisik_map_test_template (
            Fisik_MapTestTemplateFisikTemplateMappingID, 
            Fisik_MapTestTemplateNat_TestID, 
            Fisik_MapTestTemplateCreated, 
            Fisik_MapTestTemplateCreatedUserID,
            Fisik_MapTestTemplateIsActive) 
            VALUES (?, ?, ?, ?, 'Y')";
            $this->db->query($sql, [$last_id, $nat_test_id, date('Y-m-d H:i:s'), $userid]);*/

            $this->db->trans_commit();

            $this->sys_ok([
                "total" => 1,
                "affected_rows" => $this->db->affected_rows()
            ]);
        } catch (Exception $exc) {
            $this->db->trans_rollback();
            $this->sys_error($exc->getMessage());
        }
    }

    public function searchNatTest()
    {
        if (! $this->isLogin) {
            $this->sys_error("Invalid Token");
            exit;
        }
        $prm = $this->sys_input;
        $search = isset($prm['search_nat_test']) ? $prm['search_nat_test'] : '';
        $filter_status = isset($prm['filter_status']) ? $prm['filter_status'] : null;
        $q_join = "";
        $and_isnull = "";
        $id = isset($prm['id']) ? $prm['id'] : null;
        if ($id == null) {
            $this->sys_error("Anda belum memilih template fisik.");
            exit;
        }
        if ($filter_status['value'] == 'Y') {
            $q_join = "JOIN fisik_map_test_template template_status ON Nat_TestID = template_status.Fisik_MapTestTemplateNat_TestID AND 
            template_status.Fisik_MapTestTemplateFisikTemplateMappingID = ? AND
            template_status.Fisik_MapTestTemplateIsActive = 'Y'";
        } else {
            $q_join = "LEFT JOIN fisik_map_test_template template_status ON Nat_TestID = template_status.Fisik_MapTestTemplateNat_TestID AND 
            template_status.Fisik_MapTestTemplateFisikTemplateMappingID = ? AND
            template_status.Fisik_MapTestTemplateIsActive = 'Y'
            LEFT JOIN fisik_map_test_template template_other ON Nat_TestID = template_other.Fisik_MapTestTemplateNat_TestID AND 
            template_other.Fisik_MapTestTemplateIsActive = 'Y'";
            $and_isnull = "AND template_other.Fisik_MapTestTemplateID IS NULL";
        }

        try {
            $sql = "SELECT nat_test.*, IF(template_status.Fisik_MapTestTemplateID IS NOT NULL, 'Y', 'N') AS status
                    FROM nat_test
                    $q_join
                    WHERE Nat_TestIsActive = 'Y'
                    AND Nat_TestIsActive = 'Y'
                    AND Nat_TestName LIKE CONCAT('%', ?, '%')
                    AND Nat_TestIsActive = 'Y'  $and_isnull
                    ORDER BY Nat_TestName ASC
                    LIMIT 25";
            $query = $this->db->query($sql, [$id, $search]);
            // echo $this->db->last_query();

            if (!$query) {
                $this->sys_error_db("nat_test select", $this->db);
                return;
            }

            $rows = $query->result_array();

            $this->sys_ok([
                "total" => count($rows),
                "records" => $rows
            ]);
        } catch (Exception $exc) {
            $this->sys_error($exc->getMessage());
        }
    }

    public function findFisikTemplate()
    {
        if (! $this->isLogin) {
            $this->sys_error("Invalid Token");
            exit;
        }
        $prm = $this->sys_input;

        $id = isset($prm['id']) ? (int) $prm['id'] : 0;
        $name = isset($prm['name']) ? $prm['name'] : '';
        $current_page = isset($prm['current_page']) ? (int) $prm['current_page'] : 1;
        $status = isset($prm['status']) ? $prm['status'] : null;
        $q_join = "";
        $and_isnull = "";
        if ($status == 'N') {
            $q_join = "LEFT JOIN fisik_template_mapping_detail template_status ON 
            FisikTemplateID = template_status.FisikTemplateMappingDetailFisikTemplateID AND 
            template_status.FisikTemplateMappingDetailFisikTemplateMappingID = ? AND 
            template_status.FisikTemplateMappingDetailIsActive = 'Y'";
            $and_isnull = "AND template_status.FisikTemplateMappingDetailID IS NULL";
        } else {
            $q_join = "JOIN fisik_template_mapping_detail template_status ON 
            FisikTemplateID = template_status.FisikTemplateMappingDetailFisikTemplateID AND 
            template_status.FisikTemplateMappingDetailFisikTemplateMappingID = ? AND 
            template_status.FisikTemplateMappingDetailIsActive = 'Y'";
        }

        $number_limit = 10;
        $number_offset = ($current_page - 1) * $number_limit;

        try {
            $count_sql = "
                SELECT COUNT(*) AS total FROM (
                    SELECT FisikTemplateID
                    FROM fisik_template
                    $q_join
                    WHERE ( FisikTemplateCode LIKE ?
                    OR FisikTemplateTitle LIKE ? )
                    $and_isnull
                    GROUP BY FisikTemplateID
                ) AS x
            ";

            $count_query = $this->db->query($count_sql, [
                $id,
                "%$name%",
                "%$name%"
            ]);

            if (!$count_query || $count_query->num_rows() == 0) {
                $this->sys_error_db("fisik_template count", $this->db);
                return;
            }

            $tot_count = $count_query->row()->total;
            $tot_page = ceil($tot_count / $number_limit);

            $data_sql = "
                SELECT 
                    FisikTemplateID AS id,
                    FisikTemplateJSON AS xjson,
                    FisikTemplateID,
                    FisikTemplateType,
                    FisikTemplateCode,
                    FisikTemplateTableName,
                    FisikTemplateTitle,
                    FisikTemplateMappingDetailID,
                    FisikTemplateMappingDetailFisikTemplateMappingID,
                    FisikTemplateMappingDetailFisikTemplateID,
                    FisikTemplateIsActive,
                    IF(IFNULL(FisikTemplateMappingDetailID, 0) > 0, 'Y', 'N') AS status
                FROM fisik_template
                $q_join
                WHERE FisikTemplateIsActive = 'Y'
                AND ( FisikTemplateCode LIKE ?
                OR FisikTemplateTitle LIKE ? )
                $and_isnull
                GROUP BY FisikTemplateID
                ORDER BY FisikTemplateCode ASC
                LIMIT ? OFFSET ?
            ";

            $query = $this->db->query($data_sql, [
                $id,
                "%$name%",
                "%$name%",
                $number_limit,
                $number_offset
            ]);

            //echo $this->db->last_query();

            if (!$query) {
                $this->sys_error_db("fisik_template select", $this->db);
                return;
            }

            $rows = $query->result_array();

            foreach ($rows as $key => $value) {
                $rows[$key]['xjson'] = json_decode($value['xjson']);
            }

            $result = [
                "total" => $tot_page,
                "total_filter" => count($rows),
                "records" => $rows
            ];

            $this->sys_ok($result);
        } catch (Exception $exc) {
            $this->sys_error($exc->getMessage());
        }
    }


    public function saveAddFisikTemplateMappingDetail()
    {
        if (! $this->isLogin) {
            $this->sys_error("Invalid Token");
            exit;
        }
        $prm = $this->sys_input;
        $mappingdetailid = isset($prm['FisikTemplateMappingDetailID']) ? $prm['FisikTemplateMappingDetailID'] : null;
        $templatemappingID = isset($prm['templatemappingID']) ? $prm['templatemappingID'] : null;
        $templateID = isset($prm['FisikTemplateID']) ? $prm['FisikTemplateID'] : null;
        $status = isset($prm['status']) ? $prm['status'] : null;
        $userid = isset($this->sys_user["M_UserID"]) ? $this->sys_user["M_UserID"] : null;

        $this->db->trans_begin();

        try {
            if ($status === 'Y') {
                $data = [
                    'FisikTemplateMappingDetailFisikTemplateMappingID' => $templatemappingID,
                    'FisikTemplateMappingDetailFisikTemplateID' => $templateID,
                    'FisikTemplateMappingDetailCreated' => date('Y-m-d H:i:s'),
                    'FisikTemplateMappingDetailCreatedUserID' => $userid,
                    'FisikTemplateMappingDetailLastUpdated' => date('Y-m-d H:i:s'),
                ];

                $insert = $this->db->insert('fisik_template_mapping_detail', $data);

                if (!$insert) {
                    $this->db->trans_rollback();
                    $this->sys_error_db("fisik_template_mapping_detail insert", $this->db);
                    return;
                }

                $xid = $this->db->insert_id();
            } else {
                $data = [
                    'FisikTemplateMappingDetailIsActive' => 'N',
                    'FisikTemplateMappingDetailLastUpdated' => date('Y-m-d H:i:s'),
                    'FisikTemplateMappingDetailLastUpdatedUserID' => $userid
                ];

                $this->db->where('FisikTemplateMappingDetailID', $mappingdetailid);
                $update = $this->db->update('fisik_template_mapping_detail', $data);

                if (!$update) {
                    $this->db->trans_rollback();
                    $this->sys_error_db("fisik_template_mapping_detail update", $this->db);
                    return;
                }

                $xid = $mappingdetailid;
            }

            $this->db->trans_commit();

            $this->sys_ok([
                "total" => 1,
                "records" => ["xid" => $xid]
            ]);
        } catch (Exception $exc) {
            $this->db->trans_rollback();
            $this->sys_error($exc->getMessage());
        }
    }

    function addNatTestMapping()
    {
        if (! $this->isLogin) {
            $this->sys_error("Invalid Token");
            exit;
        }
        $prm = $this->sys_input;
        $id = isset($prm['id']) ? $prm['id'] : null;
        $nat_test_id = isset($prm['Nat_TestID']) ? $prm['Nat_TestID'] : null;
        $status = isset($prm['status']) ? $prm['status'] : null;
        if ($id == null) {
            $this->sys_error("Anda belum memilih template fisik.");
            exit;
        }
        if ($nat_test_id == null) {
            $this->sys_error("Anda belum memilih tes nat.");
            exit;
        }
        $userid = isset($this->sys_user["M_UserID"]) ? $this->sys_user["M_UserID"] : null;

        $this->db->trans_begin();

        try {
            $sql = "UPDATE fisik_map_test_template SET 
                    Fisik_MapTestTemplateIsActive = 'N', 
                    Fisik_MapTestTemplateDeleted = NOW(), 
                    Fisik_MapTestTemplateDeletedUserID = ? 
                    WHERE Fisik_MapTestTemplateNat_TestID = ? AND 
                    Fisik_MapTestTemplateIsActive = 'Y'";
            $del_rst = $this->db->query($sql, [$userid, $nat_test_id]);
            if (!$del_rst) {
                $this->db->trans_rollback();
                $this->sys_error_db("fisik_map_test_template update", $this->db);
                return;
            }

            if ($status == 'Y') {
                $sql = "INSERT INTO fisik_map_test_template (
                        Fisik_MapTestTemplateFisikTemplateMappingID, 
                        Fisik_MapTestTemplateNat_TestID, 
                        Fisik_MapTestTemplateCreated, 
                        Fisik_MapTestTemplateCreatedUserID, 
                        Fisik_MapTestTemplateIsActive) VALUES (?, ?, ?, ?, 'Y')";
                $insert_rst = $this->db->query($sql, [$id, $nat_test_id, date('Y-m-d H:i:s'), $userid]);
                if (!$insert_rst) {
                    $this->db->trans_rollback();
                    $this->sys_error_db("fisik_map_test_template insert", $this->db);
                    return;
                }

                $last_id = $this->db->insert_id();
            }

            $this->db->trans_commit();

            $this->sys_ok([
                "total" => 1,
                "records" => ["xid" => $last_id, "status" => $status, "nat_test_id" => $nat_test_id]
            ]);
        } catch (Exception $exc) {
            $this->db->trans_rollback();
            $this->sys_error($exc->getMessage());
        }
    }
}