<?php
class Dokterapi extends MY_Controller
{

    var $db_regional;

    public function index()
    {
        echo "DOKTER API";
    }

    public function __construct()
    {
        parent::__construct();
        // db wilayah contoh surabaya
        $this->db_regional = $this->load->database("regional", true);

        // string database
        $this->db_str_doctor = 'one_doctor';
        $this->db_str_doctor_log = 'one_doctor_log';
    }

    public function search()
    {
        try {
            # cek token valid
            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 = '%%';
                }
            }
            $sortBy = $prm['sortBy'];
            // $sortStatus = $prm['sortStatus'];
            $q_sort = '';
            $limit = '';
            if ($sortBy) {
                $q_sort = "ORDER BY " . $sortBy;
            }
            if ($all == 'N') {
                $limit = ' LIMIT 10';
            }
            $number_limit = 10;
            // $number_offset = ($prm['current_page'] - 1) * $number_limit ;

            $number_offset = 0;
            if ($prm['current_page'] > 0) {
                $number_offset = ($prm['current_page'] - 1) * $number_limit;
            }

            $sql_filter = "select count(*) as total 
        from (
            select u.*
            from $this->db_str_doctor.m_user as u
                join m_doctor ON u.M_UserM_DoctorID = M_DoctorID
                AND M_DoctorIsActive = 'Y' 
                left join nat_staff ON M_DoctorM_StaffID = Nat_StaffID
                AND Nat_StaffIsActive = 'Y'
                JOIN m_mou ON M_UserM_MouID = m_mou.M_MouID
                AND M_MouIsActive = 'Y'
                AND M_MouIsReleased = 'Y'
                AND M_MouM_CompanyID = 1710 
                join m_user as ureg ON ureg.M_UserID = u.M_UserM_UserID
                AND ureg.M_UserIsActive = 'Y'
        where u.M_UserIsActive = 'Y' AND 
        (
            u.M_UserUsername LIKE ?
            OR M_DoctorName LIKE ?
            OR M_DoctorCode LIKE ?
        )
        AND u.M_UserM_UserID = ?
        ) x";
            // $sql_param = array($search);
            //  $total = $this->db_regional->query($sql,$sql_param)->row()->total;

            $query_filter = $this->db_regional->query($sql_filter, [
                $search,
                $search,
                $search,
                $userid
            ]);
            $tot_count = 0;
            $tot_page = 0;
            if ($query_filter) {
                $tot_count = $query_filter->result_array()[0]["total"];
                $tot_page = ceil($tot_count / $number_limit);
            } else {
                $this->db->trans_rollback();
                $this->sys_error_db("m_user count", $this->db_regional);
                exit;
            }


            // $sql_data = "select M_UserID, M_UserUsername, M_UserM_DoctorCode, M_UserM_DoctorID, 
            // M_DoctorName, Nat_StaffID, Nat_StaffName, M_UserIsLoggedIn, M_UserPassword,
            // M_UserID as id, M_UserM_MouID, 
            // Concat(M_CompanyName,' ',M_MouName) as M_MouName,
            // m_mou.M_MouID as M_MouID
            // from $this->db_str_doctor.m_user 
            //     JOIN m_doctor ON M_UserM_DoctorID = M_DoctorID
            //     AND M_DoctorIsActive = 'Y' 
            //     left join nat_staff ON M_DoctorM_StaffID = Nat_StaffID
            //     AND Nat_StaffIsActive = 'Y'
            //     JOIN m_mou ON M_UserM_MouID = m_mou.M_MouID
            //     AND M_MouIsActive = 'Y'
            //     AND M_MouIsReleased = 'Y'
            //     AND M_MouM_CompanyID = 1710 
            //     join m_company ON M_MouM_CompanyID = M_CompanyID 
            // where M_UserIsActive = 'Y'
            // AND (
            //     M_UserUsername LIKE ?
            //     OR M_DoctorName LIKE ?
            //     OR M_DoctorCode LIKE ?
            // )
            // limit ? offset ?";

            $sql_data = " select u.M_UserID, u.M_UserUsername, u.M_UserM_DoctorCode, u.M_UserM_DoctorID, 
            M_DoctorName, Nat_StaffID, Nat_StaffName, u.M_UserIsLoggedIn, u.M_UserPassword,
            u.M_UserID as id, u.M_UserM_MouID, 
            Concat(M_CompanyName,' ',M_MouName) as M_MouName,
            m_mou.M_MouID as M_MouID
            from $this->db_str_doctor.m_user as u
                JOIN m_doctor ON u.M_UserM_DoctorID = M_DoctorID
                AND M_DoctorIsActive = 'Y' 
                left join nat_staff ON M_DoctorM_StaffID = Nat_StaffID
                AND Nat_StaffIsActive = 'Y'
                JOIN m_mou ON M_UserM_MouID = m_mou.M_MouID
                AND M_MouIsActive = 'Y'
                AND M_MouIsReleased = 'Y'
                AND M_MouM_CompanyID = 1710 
                join m_company ON M_MouM_CompanyID = M_CompanyID 
                join m_user as ureg ON ureg.M_UserID = u.M_UserM_UserID
                AND ureg.M_UserIsActive = 'Y'
            where u.M_UserIsActive = 'Y'
            AND (
                u.M_UserUsername LIKE ?
                OR M_DoctorName LIKE ?
                OR M_DoctorCode LIKE ?
            )
            AND u.M_UserM_UserID = ?
            limit ? offset ?";

            // select u.M_UserID, u.M_UserUsername, u.M_UserM_DoctorCode, u.M_UserM_DoctorID, 
            // M_DoctorName, Nat_StaffID, Nat_StaffName, u.M_UserIsLoggedIn, u.M_UserPassword,
            // u.M_UserID as id, u.M_UserM_MouID, 
            // Concat(M_CompanyName,' ',M_MouName) as M_MouName,
            // m_mou.M_MouID as M_MouID
            // from one_doctor.m_user as u
            //     JOIN m_doctor ON u.M_UserM_DoctorID = M_DoctorID
            //     AND M_DoctorIsActive = 'Y' 
            //     left join nat_staff ON M_DoctorM_StaffID = Nat_StaffID
            //     AND Nat_StaffIsActive = 'Y'
            //     JOIN m_mou ON M_UserM_MouID = m_mou.M_MouID
            //     AND M_MouIsActive = 'Y'
            //     AND M_MouIsReleased = 'Y'
            //     AND M_MouM_CompanyID = 1235 
            //     join m_company ON M_MouM_CompanyID = M_CompanyID
            //      join m_user as ureg ON u.M_UserM_UserID = ureg.M_UserM_UserID
            //     AND ureg.M_UserIsActive = 'Y'
            // where u.M_UserIsActive = 'Y'
            // AND (
            //     u.M_UserUsername LIKE '%CHESIA%'
            //     OR M_DoctorName LIKE '%CHESIA%'
            //     OR M_DoctorCode LIKE '%CHESIA%'
            // )

            // $sql_param = array($search);
            $query_data = $this->db_regional->query($sql_data, [
                $search,
                $search,
                $search,
                $userid,
                $number_limit,
                $number_offset
            ]);
            // echo $this->db_regional->last_query();
            // exit;

            if ($query_data) {
                $rows = $query_data->result_array();
            } else {
                $this->db->trans_rollback();
                $this->sys_error_db("m_user select");
                exit;
            }

            $result = array("total" => $tot_page, "total_filter" => count($rows), "records" => $rows, 'sql' => $this->db_regional->last_query());
            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }

    // autocomplete doctor
    public function search_doctor()
    {
        try {
            //# cek token valid
            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 = '%%';
                }
            }
            $all = $prm['all'];
            $limit = '';
            if ($all == 'N') {
                $limit = ' LIMIT 10';
            }
            $number_limit = 10;
            $number_offset = 0;
            // $number_offset = ($prm['current_page'] - 1) * $number_limit ;
            if ($prm['current_page'] > 0) {
                $number_offset = ($prm['current_page'] - 1) * $number_limit;
            }
            $sql_filter = "select COUNT(*) as total
			FROM(
                SELECT *
                FROM m_doctor 
                WHERE
                (
                    M_DoctorName LIKE ?
                    OR M_DoctorCode LIKE ?
                )
                AND M_DoctorIsActive = 'Y') x";
            //  $total = $this->db_regional->query($sql,$sql_param)->row()->total;
            $query_filter = $this->db_regional->query($sql_filter, [$search, $search]);
            //   print_r($this->db_regional->last_query());

            $tot_count = 0;
            $tot_page = 0;
            if ($query_filter) {
                $tot_count = $query_filter->result_array()[0]["total"];
                $tot_page = ceil($tot_count / $number_limit);
            } else {
                $this->db_regional->trans_rollback();
                $this->sys_error_db("m_doctor count", $this->db_regional);
                exit;
            }

            $sql_data = "select 
                        M_DoctorID as id,
                        M_DoctorName as name,
                        M_DoctorID,
                        M_DoctorName,
                        M_DoctorCode
                        from m_doctor 
                        WHERE
                        (
                            M_DoctorName LIKE ?
                            OR M_DoctorCode LIKE ?
                        )
                        AND M_DoctorIsActive = 'Y'
                    ORDER BY M_DoctorName ASC
                    limit $number_limit offset $number_offset";
            $sql_param = array($search);
            $query_data = $this->db_regional->query($sql_data, [$search, $search]);
            // echo $this->db_regional->last_query();
            if ($query_data) {
                $rows = $query_data->result_array();
            } else {
                $this->db_regional->trans_rollback();
                $this->sys_error_db("m_doctor select");
                exit;
            }

            $result = array(
                "total" => $tot_page,
                "total_filter" => count($rows),
                "records" => $rows
            );
            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }

    // autocomplete aggreement
    public function search_mou()
    {
        try {
            //# cek token valid
            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 = '%%';
                }
            }
            $all = $prm['all'];
            $limit = '';
            if ($all == 'N') {
                $limit = ' LIMIT 10';
            }
            $number_limit = 10;
            $number_offset = 0;
            // $number_offset = ($prm['current_page'] - 1) * $number_limit ;
            if ($prm['current_page'] > 0) {
                $number_offset = ($prm['current_page'] - 1) * $number_limit;
            }
            $sql_filter = "select COUNT(*) as total
			FROM(
                SELECT *
                FROM m_mou 
                WHERE
                (
                    M_MouName LIKE ?
                )
                AND M_MouIsActive = 'Y'
                AND M_MouIsReleased = 'Y'
                AND M_MouM_CompanyID = 1710 
            ) x";
            //  $total = $this->db_regional->query($sql,$sql_param)->row()->total;
            $query_filter = $this->db_regional->query($sql_filter, [$search]);
            //   print_r($this->db_regional->last_query());

            $tot_count = 0;
            $tot_page = 0;
            if ($query_filter) {
                $tot_count = $query_filter->result_array()[0]["total"];
                $tot_page = ceil($tot_count / $number_limit);
            } else {
                $this->db_regional->trans_rollback();
                $this->sys_error_db("m_mou count", $this->db_regional);
                exit;
            }

            $sql_data = "select 
                        M_MouID as id, M_MouID, 
                        Concat(M_CompanyName,' ',M_MouName) as name, 
                        Concat(M_CompanyName,' ',M_MouName) as M_MouName
                        from m_mou
                        join m_company ON M_MouM_CompanyID = M_CompanyID 
                        WHERE
                        (
                            Concat(M_CompanyName,' ',M_MouName) LIKE ?
                        )
                        AND M_MouIsActive = 'Y'
                        AND M_MouIsReleased = 'Y'
                        AND M_MouM_CompanyID = 1710 
                    ORDER BY M_MouName ASC
                    limit $number_limit offset $number_offset";
            $sql_param = array($search);
            $query_data = $this->db_regional->query($sql_data, [$search]);
            // echo $this->db_regional->last_query();
            if ($query_data) {
                $rows = $query_data->result_array();
            } else {
                $this->db_regional->trans_rollback();
                $this->sys_error_db("m_mou select");
                exit;
            }

            $result = array(
                "total" => $tot_page,
                "total_filter" => count($rows),
                "records" => $rows,
                "sql" => $this->db_regional->last_query()
            );
            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }

    // add old
    public function add_20_04_2023()
    {
        // alur
        // ambil M_DoctorID dari parameter terus search di surabaya.m_doctor where M_DoctorID and M_DoctorIsActive = 'Y'
        //  lalu ambil M_DoctorCode tampung di variabel

        try {
            //# cek token valid
            if (! $this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }

            //begin transaction 
            $this->db->trans_begin();

            //# ambil parameter input
            $prm = $this->sys_input;

            $userid = $this->sys_user['M_UserID'];
            // $userid = 1;
            $M_DoctorName = "";

            // username: this.xusername,
            // password: this.xpassword,
            // confirm_password: this.xpasswordconfirm,
            // id_doctor_prm: this.xitemdokter.M_DoctorID,
            // id_mou : this.xitemmou.M_MouID,
            // doctor_id: this.xdoctorid,

            // test code

            // $error = array(
            //     "message" => "cek show error",
            // );
            // $this->sys_error_db("cek show error");
            // exit;

            // id doctor untuk code
            $M_UserM_DoctorID = "";
            if (isset($prm['id_doctor_prm'])) {
                // $M_UserM_DoctorID = trim($prm["M_UserM_DoctorID"]);
                $M_UserM_DoctorID = trim($prm["id_doctor_prm"]);
            }

            if ($M_UserM_DoctorID == "" || !$M_UserM_DoctorID) {
                $error = "Dokter harus dipilih";
                $this->sys_error_db($error);
                exit;
            }

            // doktor id
            $doctor_id = "";
            if (isset($prm['doctor_id'])) {
                // $M_UserM_DoctorID = trim($prm["M_UserM_DoctorID"]);
                $doctor_id = intval($prm["doctor_id"]);
            }

            if ($doctor_id == "" || !$doctor_id) {
                $error = "Dokter Id harus diisi";
                $this->sys_error_db($error);
                exit;
            }

            // username
            $M_UserUsername = "";
            if (isset($prm['username'])) {
                $M_UserUsername = trim($prm["username"]);
            }

            if ($M_UserUsername == "" || !$M_UserUsername) {
                $error = "username harus diinput";
                $this->sys_error_db($error);
                exit;
            }

            // cek doctor id dan username
            // if($doctor_id == $M_UserUsername){
            //     $error = "doctor id dan username tidak boleh sama";
            //     $this->sys_error_db($error);
            //     exit;
            // }

            // password
            $M_UserPassword = "";
            if (isset($prm['password'])) {
                // $sm_password = md5($this->one_salt . $prm["M_UserPassword"] . $this->one_salt);
                $M_UserPassword = trim($prm["password"]);
            }

            if ($M_UserPassword == "" || !$M_UserPassword) {
                $error = "password harus di isi";
                $this->sys_error_db($error);
                exit;
            }

            // confirm password
            $confirm_password = "";
            if (isset($prm['confirm_password'])) {
                // $sm_password = md5($this->one_salt . $prm["M_UserPassword"] . $this->one_salt);
                $confirm_password = trim($prm["confirm_password"]);
            }

            if ($confirm_password == "" || !$confirm_password) {
                $error = "confirm password harus di isi";
                $this->sys_error_db($error);
                exit;
            }

            // cek password dan confirm
            if ($M_UserPassword !== $confirm_password) {
                $error = "password dan konfirmasi harus sama";
                $this->sys_error_db($error);
                exit;
            }

            // id_mou
            $M_UserM_MouID = "";
            if (isset($prm['id_mou'])) {
                // $M_UserM_DoctorID = trim($prm["M_UserM_DoctorID"]);
                $M_UserM_MouID = intval($prm["id_mou"]);
            }

            if ($M_UserM_MouID == "" || !$M_UserM_MouID) {
                $error = "Aggrement harus di pilih";
                $this->sys_error_db($error);
                exit;
            }

            // cek M_UserDoctorCode dan M_Username same
            $sql_cek_username_code_double = "select count(*) as total_double
            from $this->db_str_doctor.m_user 
            where M_UserM_DoctorCode = ? 
            and M_UserUsername = ?
            AND M_UserIsActive = 'Y'";

            $query_cek = $this->db_regional->query($sql_cek_username_code_double, [
                $doctor_id,
                $M_UserUsername
            ]);

            if (!$query_cek) {
                // print_r($this->db_regional->last_query());
                $this->db_regional->trans_rollback();
                $this->sys_error_db("ERR Code: m_user cek username and code", $this->db);
                exit;
            }



            $get_count_cek = $query_cek->row_array();
            if ($get_count_cek['total_double'] == 0) {
                // insert ke database

                // query insert
                $sm_password = md5($this->one_salt . $prm["password"] . $this->one_salt);
                // print_r($sm_password);

                // $M_UserPassword = $sm_password;
                $sql_insert = "INSERT INTO $this->db_str_doctor.m_user
                (
                    M_UserM_DoctorID,
                    M_UserM_DoctorCode,
                    M_UserUsername,
                    M_UserPassword,
                    M_UserM_MouID,
                    M_UserCreated,
                    M_UserLastUpdated,
                    M_UserLastAccess,
                    M_UserM_UserID
                )
                VALUES (?, ?, ?, ?, ?,
                now(), now(), now(), ?)";

                $query_insert = $this->db->query($sql_insert, [
                    $M_UserM_DoctorID,
                    $doctor_id,
                    $M_UserUsername,
                    $sm_password,
                    $M_UserM_MouID,
                    $userid
                ]);

                if (!$query_insert) {
                    $this->db->trans_rollback();
                    // print_r($M_UserPassword);
                    $this->sys_error_db("m_user insert");
                    exit;
                }

                // var_dump($this->db->affected_rows());
                $insert_id = $this->db->insert_id();
                // print_r($insert_id);

                $sql_json_before = "SELECT * 
                FROM $this->db_str_doctor.m_user
                WHERE M_UserIsActive = 'Y'
                AND M_UserID = ?";

                $qry_json_before = $this->db->query(
                    $sql_json_before,
                    [
                        $insert_id
                    ]
                );

                if (!$qry_json_before) {
                    $this->db->trans_rollback();
                    $this->sys_error_db("m_user select json");
                    exit;
                }

                $data_by_id = $qry_json_before->row();

                $json_after_log = json_encode($data_by_id);

                // print_r($json_after_log);

                $sql_insert_log = "INSERT INTO $this->db_str_doctor_log.m_user_log(
                    M_UserLogM_UserID,
                    M_UserLogStatus,
                    M_UserLogJSONBefore,
                    M_UserLogJSONAfter,
                    M_UserLogUserID,
                    M_UserLogCreated
                ) VALUES (
                    ?,
                    'ADD',
                    null,
                    ?,
                    ?,
                    now()
                )";

                $qry_insert_log = $this->db->query(
                    $sql_insert_log,
                    [
                        $insert_id,
                        $json_after_log,
                        $userid
                    ]
                );

                if (!$qry_insert_log) {
                    $this->db->trans_rollback();
                    $this->sys_error_db("m_user_log insert log");
                    exit;
                }

                // sukses
                $this->db->trans_commit();
                $result = array(
                    "total" => 1,
                    "records" => array("xid" => 0)
                );
                $this->sys_ok($result);
            } else {
                $this->db_regional->trans_rollback();
                $this->sys_error_db("Username dan Doktor ID telah digunakan. Silahkan anda pilih yang lain", $this->db);
                exit;
            }
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }

    // add
    public function add()
    {
        // alur
        // ambil M_DoctorID dari parameter terus search di surabaya.m_doctor where M_DoctorID and M_DoctorIsActive = 'Y'
        //  lalu ambil M_DoctorCode tampung di variabel

        try {
            //# cek token valid
            if (! $this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }

            //begin transaction 
            $this->db->trans_begin();

            //# ambil parameter input
            $prm = $this->sys_input;

            $userid = $this->sys_user['M_UserID'];
            // $userid = 1;
            $M_DoctorName = "";

            // username: this.xusername,
            // password: this.xpassword,
            // confirm_password: this.xpasswordconfirm,
            // id_doctor_prm: this.xitemdokter.M_DoctorID,
            // id_mou : this.xitemmou.M_MouID,
            // doctor_id: this.xdoctorid,

            // test code

            // $error = array(
            //     "message" => "cek show error",
            // );
            // $this->sys_error_db("cek show error");
            // exit;

            // id doctor untuk code
            $M_UserM_DoctorID = "";
            if (isset($prm['id_doctor_prm'])) {
                // $M_UserM_DoctorID = trim($prm["M_UserM_DoctorID"]);
                $M_UserM_DoctorID = trim($prm["id_doctor_prm"]);
            }

            if ($M_UserM_DoctorID == "" || !$M_UserM_DoctorID) {
                $error = "Dokter harus dipilih";
                $this->sys_error_db($error);
                exit;
            }

            // doktor id
            $doctor_id = "";
            if (isset($prm['doctor_id'])) {
                // $M_UserM_DoctorID = trim($prm["M_UserM_DoctorID"]);
                $doctor_id = intval($prm["doctor_id"]);
            }

            if ($doctor_id == "" || !$doctor_id) {
                $error = "Dokter Id harus diisi";
                $this->sys_error_db($error);
                exit;
            }

            // username
            $M_UserUsername = "";
            if (isset($prm['username'])) {
                $M_UserUsername = trim($prm["username"]);
            }

            if ($M_UserUsername == "" || !$M_UserUsername) {
                $error = "username harus diinput";
                $this->sys_error_db($error);
                exit;
            }

            // cek doctor id dan username
            // if($doctor_id == $M_UserUsername){
            //     $error = "doctor id dan username tidak boleh sama";
            //     $this->sys_error_db($error);
            //     exit;
            // }

            // password
            $M_UserPassword = "";
            if (isset($prm['password'])) {
                // $sm_password = md5($this->one_salt . $prm["M_UserPassword"] . $this->one_salt);
                $M_UserPassword = trim($prm["password"]);
            }

            if ($M_UserPassword == "" || !$M_UserPassword) {
                $error = "password harus di isi";
                $this->sys_error_db($error);
                exit;
            }

            // confirm password
            $confirm_password = "";
            if (isset($prm['confirm_password'])) {
                // $sm_password = md5($this->one_salt . $prm["M_UserPassword"] . $this->one_salt);
                $confirm_password = trim($prm["confirm_password"]);
            }

            if ($confirm_password == "" || !$confirm_password) {
                $error = "confirm password harus di isi";
                $this->sys_error_db($error);
                exit;
            }

            // cek password dan confirm
            if ($M_UserPassword !== $confirm_password) {
                $error = "password dan konfirmasi harus sama";
                $this->sys_error_db($error);
                exit;
            }

            // id_mou
            $M_UserM_MouID = "";
            if (isset($prm['id_mou'])) {
                // $M_UserM_DoctorID = trim($prm["M_UserM_DoctorID"]);
                $M_UserM_MouID = intval($prm["id_mou"]);
            }

            if ($M_UserM_MouID == "" || !$M_UserM_MouID) {
                $error = "Aggrement harus di pilih";
                $this->sys_error_db($error);
                exit;
            }

            // cek M_UserDoctorCode dan M_Username same
            $sql_cek_username_code_double = "select count(*) as total_double
            from $this->db_str_doctor.m_user 
            where M_UserM_DoctorCode = ? 
            and M_UserUsername = ?
            AND M_UserIsActive = 'Y'";

            $query_cek = $this->db_regional->query($sql_cek_username_code_double, [
                $doctor_id,
                $M_UserUsername
            ]);

            if (!$query_cek) {
                // print_r($this->db_regional->last_query());
                $this->db_regional->trans_rollback();
                $this->sys_error_db("ERR Code: m_user cek username and code", $this->db);
                exit;
            }



            $get_count_cek = $query_cek->row_array();
            if ($get_count_cek['total_double'] == 0) {

                // cek user ada yg sama
                $sql_cek_user_sama = "select count(*) as total_user
                from $this->db_str_doctor.m_user 
                where M_UserM_DoctorCode = ? 
                and M_UserM_DoctorID = ?
                AND M_UserIsActive = 'Y'";

                $query_cek_user = $this->db_regional->query($sql_cek_user_sama, [
                    $doctor_id,
                    $M_UserM_DoctorID
                ]);

                if (!$query_cek_user) {
                    // print_r($this->db_regional->last_query());
                    $this->db_regional->trans_rollback();
                    $this->sys_error_db("ERR Code: m_user cek user sama", $this->db);
                    exit;
                }

                $get_count_cek_user = $query_cek_user->row_array();
                if ($get_count_cek_user['total_user'] == 0) {
                    // insert ke database

                    // query insert
                    $sm_password = md5($this->one_salt . $prm["password"] . $this->one_salt);
                    // print_r($sm_password);

                    // $M_UserPassword = $sm_password;
                    $sql_insert = "INSERT INTO $this->db_str_doctor.m_user
                    (
                        M_UserM_DoctorID,
                        M_UserM_DoctorCode,
                        M_UserUsername,
                        M_UserPassword,
                        M_UserM_MouID,
                        M_UserCreated,
                        M_UserLastUpdated,
                        M_UserLastAccess,
                        M_UserM_UserID
                    )
                    VALUES (?, ?, ?, ?, ?,
                    now(), now(), now(), ?)";

                    $query_insert = $this->db->query($sql_insert, [
                        $M_UserM_DoctorID,
                        $doctor_id,
                        $M_UserUsername,
                        $sm_password,
                        $M_UserM_MouID,
                        $userid
                    ]);

                    if (!$query_insert) {
                        $this->db->trans_rollback();
                        // print_r($M_UserPassword);
                        $this->sys_error_db("m_user insert");
                        exit;
                    }

                    // var_dump($this->db->affected_rows());
                    $insert_id = $this->db->insert_id();
                    // print_r($insert_id);

                    $sql_json_before = "SELECT * 
                    FROM $this->db_str_doctor.m_user
                    WHERE M_UserIsActive = 'Y'
                    AND M_UserID = ?";

                    $qry_json_before = $this->db->query(
                        $sql_json_before,
                        [
                            $insert_id
                        ]
                    );

                    if (!$qry_json_before) {
                        $this->db->trans_rollback();
                        $this->sys_error_db("m_user select json");
                        exit;
                    }

                    $data_by_id = $qry_json_before->row();

                    $json_after_log = json_encode($data_by_id);

                    // print_r($json_after_log);

                    $sql_insert_log = "INSERT INTO $this->db_str_doctor_log.m_user_log(
                        M_UserLogM_UserID,
                        M_UserLogStatus,
                        M_UserLogJSONBefore,
                        M_UserLogJSONAfter,
                        M_UserLogUserID,
                        M_UserLogCreated
                    ) VALUES (
                        ?,
                        'ADD',
                        null,
                        ?,
                        ?,
                        now()
                    )";

                    $qry_insert_log = $this->db->query(
                        $sql_insert_log,
                        [
                            $insert_id,
                            $json_after_log,
                            $userid
                        ]
                    );

                    if (!$qry_insert_log) {
                        $this->db->trans_rollback();
                        $this->sys_error_db("m_user_log insert log");
                        exit;
                    }

                    // sukses
                    $this->db->trans_commit();
                    $result = array(
                        "total" => 1,
                        "records" => array("xid" => 0)
                    );
                    $this->sys_ok($result);
                } else {
                    $this->db_regional->trans_rollback();
                    $this->sys_error_db("Dokter ID $doctor_id sudah ada.", $this->db);
                    exit;
                }
            } else {
                $this->db_regional->trans_rollback();
                $this->sys_error_db("Username dan Doktor ID telah digunakan. Silahkan anda pilih yang lain", $this->db);
                exit;
            }
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }

    // edit old
    public function edit_old_20_04_2023()
    {
        try {
            //# cek token valid
            if (! $this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }

            //begin transaction 
            $this->db->trans_begin();

            //# ambil parameter input
            $prm = $this->sys_input;

            $userid = $this->sys_user['M_UserID'];
            // $userid = 1;
            $id = $prm['id'];
            if (isset($prm['id'])) {
                $id = intval($prm["id"]);
            }
            if ($id == "" || !$id) {
                $error = array(
                    "message" => "id is mandatory",
                );
                $this->sys_error_db($error);
                exit;
            }

            // id doctor untuk code
            $M_UserM_DoctorID = "";
            if (isset($prm['id_doctor_prm'])) {
                // $M_UserM_DoctorID = trim($prm["M_UserM_DoctorID"]);
                $M_UserM_DoctorID = trim($prm["id_doctor_prm"]);
            }

            if ($M_UserM_DoctorID == "" || !$M_UserM_DoctorID) {
                $error = "Dokter harus dipilih";
                $this->sys_error_db($error);
                exit;
            }

            // doktor id
            $doctor_id = "";
            if (isset($prm['doctor_id'])) {
                // $M_UserM_DoctorID = trim($prm["M_UserM_DoctorID"]);
                $doctor_id = intval($prm["doctor_id"]);
            }

            if ($doctor_id == "" || !$doctor_id) {
                $error = "Dokter Id harus diisi";
                $this->sys_error_db($error);
                exit;
            }

            // username
            $M_UserUsername = "";
            if (isset($prm['username'])) {
                $M_UserUsername = trim($prm["username"]);
            }

            if ($M_UserUsername == "" || !$M_UserUsername) {
                $error = "username harus diinput";
                $this->sys_error_db($error);
                exit;
            }

            // cek doctor id dan username
            // if($doctor_id == $M_UserUsername){
            //     $error = "doctor id dan username tidak boleh sama";
            //     $this->sys_error_db($error);
            //     exit;
            // }

            // password
            // $M_UserPassword = "";
            // if (isset($prm['password'])) {
            //     // $sm_password = md5($this->one_salt . $prm["M_UserPassword"] . $this->one_salt);
            //     $M_UserPassword = trim($prm["password"]);
            // }

            // if ($M_UserPassword == "" || !$M_UserPassword) {
            //     $error = "password harus di isi";
            //     $this->sys_error_db($error);
            //     exit;
            // }

            // confirm password
            // $confirm_password = "";
            // if (isset($prm['confirm_password'])) {
            //     // $sm_password = md5($this->one_salt . $prm["M_UserPassword"] . $this->one_salt);
            //     $confirm_password = trim($prm["confirm_password"]);
            // }

            // if ($confirm_password == "" || !$confirm_password) {
            //     $error = "confirm password harus di isi";
            //     $this->sys_error_db($error);
            //     exit;
            // }

            // cek password dan confirm
            // if($M_UserPassword !== $confirm_password){
            //     $error = "password dan konfirmasi harus sama";
            //     $this->sys_error_db($error);
            //     exit;
            // }

            // id_mou
            $M_UserM_MouID = "";
            if (isset($prm['id_mou'])) {
                // $M_UserM_DoctorID = trim($prm["M_UserM_DoctorID"]);
                $M_UserM_MouID = intval($prm["id_mou"]);
            }

            if ($M_UserM_MouID == "" || !$M_UserM_MouID) {
                $error = "Aggrement harus di pilih";
                $this->sys_error_db($error);
                exit;
            }

            // password
            // $M_UserPassword = "";
            // if (isset($prm['M_UserPassword'])) {
            //     $sm_password = md5($this->one_salt . $prm["M_UserPassword"] . $this->one_salt);
            //     $M_UserPassword = $sm_password;
            // }

            // if ($M_UserPassword == "" || !$M_UserPassword) {
            //     $error = array(
            //         "message" => "password is mandatory",
            //     );
            //     $this->sys_error_db($error);
            //     exit;
            // }

            // cek M_UserDoctorCode dan M_Username same
            $sql_cek_username_code_double = "select count(*) as total_double
            from $this->db_str_doctor.m_user 
            where M_UserM_DoctorCode = ? 
            and M_UserUsername = ?
            AND M_UserIsActive = 'Y'";

            $query_cek = $this->db_regional->query($sql_cek_username_code_double, [
                $doctor_id,
                $M_UserUsername
            ]);

            if (!$query_cek) {
                // print_r($this->db_regional->last_query());
                $this->db_regional->trans_rollback();
                $this->sys_error_db("ERR Code: m_user cek username and code", $this->db);
                exit;
            }



            $get_count_cek = $query_cek->row_array();
            // if($get_count['exist'] == 0){
            // json before
            $sql_json_before = "SELECT * 
                FROM $this->db_str_doctor.m_user
                WHERE M_UserIsActive = 'Y'
                AND M_UserID = ?";

            $qry_json_before = $this->db->query(
                $sql_json_before,
                [
                    $id
                ]
            );

            if (!$qry_json_before) {
                $this->db->trans_rollback();
                $this->sys_error_db("m_user select json before");
                exit;
            }

            $data_before_by_id = $qry_json_before->row();

            $json_before_log = json_encode($data_before_by_id);

            // print_r($json_before_log);

            // query update
            // $sql_update = "UPDATE $this->db_str_doctor.m_user
            // SET
            //     M_UserM_DoctorID = ?,
            //     M_UserM_DoctorCode = ?,
            //     M_UserPassword = ?,
            //     M_UserUsername = ?,
            //     M_UserLastUpdated = now(),
            //     M_UserM_UserID = ?
            // WHERE M_UserID = ? ";

            // $query_update = $this->db->query(
            //     $sql_update,[
            //     $M_UserM_DoctorID, 
            //     $M_UserM_DoctorCode,
            //     $M_UserPassword, 
            //     $M_UserUsername,
            //     $userid, 
            //     $id
            // ]);

            $sql_update = "UPDATE $this->db_str_doctor.m_user
                SET
                    M_UserM_DoctorID = ?,
                    M_UserM_DoctorCode = ?,
                    M_UserUsername = ?,
                    M_UserM_MouID = ?,
                    M_UserLastUpdated = now(),
                    M_UserM_UserID = ?
                WHERE M_UserID = ? ";

            $query_update = $this->db->query(
                $sql_update,
                [
                    $M_UserM_DoctorID,
                    $doctor_id,
                    $M_UserUsername,
                    $M_UserM_MouID,
                    $userid,
                    $id
                ]
            );

            if (!$query_update) {
                $this->db->trans_rollback();
                $this->sys_error_db("m_user update");
                exit;
            }

            // json after
            $sql_json_after = "SELECT * 
                FROM $this->db_str_doctor.m_user
                WHERE M_UserIsActive = 'Y'
                AND M_UserID = ?";

            $qry_json_after = $this->db->query(
                $sql_json_after,
                [
                    $id
                ]
            );

            if (!$qry_json_after) {
                $this->db->trans_rollback();
                $this->sys_error_db("m_user select json after");
                exit;
            }

            $data_after_by_id = $qry_json_after->row();

            $json_after_log = json_encode($data_after_by_id);

            $sql_insert_log = "INSERT INTO $this->db_str_doctor_log.m_user_log(
                    M_UserLogM_UserID,
                    M_UserLogStatus,
                    M_UserLogJSONBefore,
                    M_UserLogJSONAfter,
                    M_UserLogUserID,
                    M_UserLogCreated
                ) VALUES (
                    ?,
                    'EDIT',
                    ?,
                    ?,
                    ?,
                    now()
                )";

            $qry_insert_log = $this->db->query(
                $sql_insert_log,
                [
                    $id,
                    $json_before_log,
                    $json_after_log,
                    $userid
                ]
            );

            if (!$qry_insert_log) {
                $this->db_doctor_log->trans_rollback();
                $this->sys_error_db("m_user edit log");
                exit;
            }

            // sukses
            $this->db->trans_commit();
            $result = array(
                "total" => 1,
                "records" => array("xid" => 0)
            );
            $this->sys_ok($result);

            // }else{
            //     $this->db_regional->trans_rollback();
            //     $this->sys_error_db("Username dan Doktor ID telah digunakan. Silahkan anda pilih yang lain", $this->db);
            //     exit;
            // }

            // $M_UserM_DoctorCode = "";
            // $sql_get_doctor = "SELECT M_DoctorCode 
            // from m_doctor 
            // WHERE M_DoctorIsActive = 'Y'
            // AND M_DoctorID = ?";
            // $query_data_doctor = $this->db->query($sql_get_doctor, [$M_UserM_DoctorID]);

            // if(!$query_data_doctor){
            //     $this->db->trans_rollback();
            //     $this->sys_error_db("m_doctor get data", $this->db);
            //     exit;
            // }

            // $get_data_doctor = $query_data_doctor->row_array();
            // $M_UserM_DoctorCode = $get_data_doctor['M_DoctorCode'];

            // $sql_count = "SELECT COUNT(*) as exist 
            // FROM $this->db_str_doctor.m_user 
            // WHERE M_UserIsActive = 'Y' 
            // AND M_UserM_DoctorID = ?";
            // $query_count =  $this->db->query($sql_count,[
            //     $M_UserM_DoctorID
            // ]);

            // $last_query_count = $this->db->last_query();

            // if(!$query_count){
            //     $this->db->trans_rollback();
            //     $this->sys_error_db("itemcategory search & count by name");
            //     exit;
            // }else{
            //     $get_count = $query_count->row_array();
            // if($get_count['exist'] == 0)
            // {
            // // json before
            // $sql_json_before = "SELECT * 
            // FROM $this->db_str_doctor.m_user
            // WHERE M_UserIsActive = 'Y'
            // AND M_UserID = ?";

            // $qry_json_before = $this->db->query(
            //     $sql_json_before, 
            // [
            //     $id
            // ]);

            // if (!$qry_json_before) {
            //     $this->db->trans_rollback();
            //     $this->sys_error_db("m_user select json before");
            //     exit;
            // } 

            // $data_before_by_id = $qry_json_before->row();

            // $json_before_log = json_encode($data_before_by_id);

            // // print_r($json_before_log);

            // // query update
            // // $sql_update = "UPDATE $this->db_str_doctor.m_user
            // // SET
            // //     M_UserM_DoctorID = ?,
            // //     M_UserM_DoctorCode = ?,
            // //     M_UserPassword = ?,
            // //     M_UserUsername = ?,
            // //     M_UserLastUpdated = now(),
            // //     M_UserM_UserID = ?
            // // WHERE M_UserID = ? ";

            // // $query_update = $this->db->query(
            // //     $sql_update,[
            // //     $M_UserM_DoctorID, 
            // //     $M_UserM_DoctorCode,
            // //     $M_UserPassword, 
            // //     $M_UserUsername,
            // //     $userid, 
            // //     $id
            // // ]);

            // $sql_update = "UPDATE $this->db_str_doctor.m_user
            // SET
            //     M_UserM_DoctorID = ?,
            //     M_UserM_DoctorCode = ?,
            //     M_UserUsername = ?,
            //     M_UserLastUpdated = now(),
            //     M_UserM_UserID = ?
            // WHERE M_UserID = ? ";

            // $query_update = $this->db->query(
            //     $sql_update,[
            //     $M_UserM_DoctorID,
            //     $M_UserM_DoctorCode,
            //     $M_UserUsername,
            //     $userid, 
            //     $id
            // ]);

            // if (!$query_update) {
            //     $this->db->trans_rollback();
            //     $this->sys_error_db("m_user update");
            //     exit;
            // } 

            // // json after
            // $sql_json_after = "SELECT * 
            // FROM $this->db_str_doctor.m_user
            // WHERE M_UserIsActive = 'Y'
            // AND M_UserID = ?";

            // $qry_json_after = $this->db->query($sql_json_after, 
            // [
            //     $id
            // ]);

            // if (!$qry_json_after) {
            //     $this->db->trans_rollback();
            //     $this->sys_error_db("m_user select json after");
            //     exit;
            // } 

            // $data_after_by_id = $qry_json_after->row();

            // $json_after_log = json_encode($data_after_by_id);

            // $sql_insert_log = "INSERT INTO $this->db_str_doctor_log.m_user_log(
            //     M_UserLogM_UserID,
            //     M_UserLogStatus,
            //     M_UserLogJSONBefore,
            //     M_UserLogJSONAfter,
            //     M_UserLogUserID,
            //     M_UserLogCreated
            // ) VALUES (
            //     ?,
            //     'EDIT',
            //     ?,
            //     ?,
            //     ?,
            //     now()
            // )";

            // $qry_insert_log = $this->db->query($sql_insert_log,
            // [
            //     $id,
            //     $json_before_log,
            //     $json_after_log,
            //     $userid
            // ]);

            // if (!$qry_insert_log) {
            //     $this->db_doctor_log->trans_rollback();
            //     $this->sys_error_db("m_user edit log");
            //     exit;
            // } 

            // // sukses
            // $this->db->trans_commit();
            // $result = array(
            //     "total" => 1, 
            //     "records" => array("xid" => 0)
            // );
            // $this->sys_ok($result);
            // }

            // else{
            //     $errors = array(); 
            //     if($get_count['exist'] != 0){
            //         array_push($errors,array(
            //             'field'=>'name',
            //             'msg'=>'Nama sudah ada'
            //         ));
            //     }

            //     $result = array (
            //         "total" => -1,
            //         "errors" => $errors, 
            //         "records" => 0);
            //     $this->sys_ok($result);
            // }

        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }

    public function edit()
    {
        try {
            //# cek token valid
            if (! $this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }

            //begin transaction 
            $this->db->trans_begin();

            //# ambil parameter input
            $prm = $this->sys_input;

            $userid = $this->sys_user['M_UserID'];
            // $userid = 1;
            $id = $prm['id'];
            if (isset($prm['id'])) {
                $id = intval($prm["id"]);
            }
            if ($id == "" || !$id) {
                $error = array(
                    "message" => "id is mandatory",
                );
                $this->sys_error_db($error);
                exit;
            }

            // id doctor untuk code
            $M_UserM_DoctorID = "";
            if (isset($prm['id_doctor_prm'])) {
                // $M_UserM_DoctorID = trim($prm["M_UserM_DoctorID"]);
                $M_UserM_DoctorID = trim($prm["id_doctor_prm"]);
            }

            if ($M_UserM_DoctorID == "" || !$M_UserM_DoctorID) {
                $error = "Dokter harus dipilih";
                $this->sys_error_db($error);
                exit;
            }

            // doktor id
            $doctor_id = "";
            if (isset($prm['doctor_id'])) {
                // $M_UserM_DoctorID = trim($prm["M_UserM_DoctorID"]);
                $doctor_id = intval($prm["doctor_id"]);
            }

            if ($doctor_id == "" || !$doctor_id) {
                $error = "Dokter Id harus diisi";
                $this->sys_error_db($error);
                exit;
            }

            // username
            $M_UserUsername = "";
            if (isset($prm['username'])) {
                $M_UserUsername = trim($prm["username"]);
            }

            if ($M_UserUsername == "" || !$M_UserUsername) {
                $error = "username harus diinput";
                $this->sys_error_db($error);
                exit;
            }

            // cek doctor id dan username
            // if($doctor_id == $M_UserUsername){
            //     $error = "doctor id dan username tidak boleh sama";
            //     $this->sys_error_db($error);
            //     exit;
            // }

            // password
            // $M_UserPassword = "";
            // if (isset($prm['password'])) {
            //     // $sm_password = md5($this->one_salt . $prm["M_UserPassword"] . $this->one_salt);
            //     $M_UserPassword = trim($prm["password"]);
            // }

            // if ($M_UserPassword == "" || !$M_UserPassword) {
            //     $error = "password harus di isi";
            //     $this->sys_error_db($error);
            //     exit;
            // }

            // confirm password
            // $confirm_password = "";
            // if (isset($prm['confirm_password'])) {
            //     // $sm_password = md5($this->one_salt . $prm["M_UserPassword"] . $this->one_salt);
            //     $confirm_password = trim($prm["confirm_password"]);
            // }

            // if ($confirm_password == "" || !$confirm_password) {
            //     $error = "confirm password harus di isi";
            //     $this->sys_error_db($error);
            //     exit;
            // }

            // cek password dan confirm
            // if($M_UserPassword !== $confirm_password){
            //     $error = "password dan konfirmasi harus sama";
            //     $this->sys_error_db($error);
            //     exit;
            // }

            // id_mou
            $M_UserM_MouID = "";
            if (isset($prm['id_mou'])) {
                // $M_UserM_DoctorID = trim($prm["M_UserM_DoctorID"]);
                $M_UserM_MouID = intval($prm["id_mou"]);
            }

            if ($M_UserM_MouID == "" || !$M_UserM_MouID) {
                $error = "Aggrement harus di pilih";
                $this->sys_error_db($error);
                exit;
            }

            // password
            // $M_UserPassword = "";
            // if (isset($prm['M_UserPassword'])) {
            //     $sm_password = md5($this->one_salt . $prm["M_UserPassword"] . $this->one_salt);
            //     $M_UserPassword = $sm_password;
            // }

            // if ($M_UserPassword == "" || !$M_UserPassword) {
            //     $error = array(
            //         "message" => "password is mandatory",
            //     );
            //     $this->sys_error_db($error);
            //     exit;
            // }

            // cek M_UserDoctorCode dan M_Username same
            $sql_cek_username_code_double = "select count(*) as total_double
            from $this->db_str_doctor.m_user 
            where M_UserM_DoctorCode = ? 
            and M_UserUsername = ?
            AND M_UserIsActive = 'Y'";

            $query_cek = $this->db_regional->query($sql_cek_username_code_double, [
                $doctor_id,
                $M_UserUsername
            ]);

            if (!$query_cek) {
                // print_r($this->db_regional->last_query());
                $this->db_regional->trans_rollback();
                $this->sys_error_db("ERR Code: m_user cek username and code", $this->db);
                exit;
            }

            $get_count_cek = $query_cek->row_array();
            // if($get_count['exist'] == 0){
            // json before
            $sql_json_before = "SELECT * 
                FROM $this->db_str_doctor.m_user
                WHERE M_UserIsActive = 'Y'
                AND M_UserID = ?";

            $qry_json_before = $this->db->query(
                $sql_json_before,
                [
                    $id
                ]
            );

            if (!$qry_json_before) {
                $this->db->trans_rollback();
                $this->sys_error_db("m_user select json before");
                exit;
            }

            $data_before_by_id = $qry_json_before->row();

            $json_before_log = json_encode($data_before_by_id);

            // print_r($json_before_log);

            // query update
            // $sql_update = "UPDATE $this->db_str_doctor.m_user
            // SET
            //     M_UserM_DoctorID = ?,
            //     M_UserM_DoctorCode = ?,
            //     M_UserPassword = ?,
            //     M_UserUsername = ?,
            //     M_UserLastUpdated = now(),
            //     M_UserM_UserID = ?
            // WHERE M_UserID = ? ";

            // $query_update = $this->db->query(
            //     $sql_update,[
            //     $M_UserM_DoctorID, 
            //     $M_UserM_DoctorCode,
            //     $M_UserPassword, 
            //     $M_UserUsername,
            //     $userid, 
            //     $id
            // ]);


            // cek mou berbeda atau tidak
            $sql_cek_mou = "select M_UserM_MouID
                from $this->db_str_doctor.m_user 
                where M_UserM_DoctorID = ? 
                and M_UserM_DoctorCode = ?
                AND M_UserIsActive = 'Y'";

            $query_cek_mou = $this->db_regional->query($sql_cek_mou, [
                $M_UserM_DoctorID,
                $doctor_id
            ]);

            if (!$query_cek_mou) {
                // print_r($this->db_regional->last_query());
                $this->db_regional->trans_rollback();
                $this->sys_error_db("ERR Code: mou cek sama atau tidak", $this->db);
                exit;
            }

            $data_mou_by_doctor = $query_cek_mou->row_array();
            if ($data_mou_by_doctor['M_UserM_MouID'] == $M_UserM_MouID) {
                // update biasa
                // proses update m_user

                // print_r("satu");

                $sql_update = "UPDATE $this->db_str_doctor.m_user
                    SET
                        M_UserM_DoctorID = ?,
                        M_UserM_DoctorCode = ?,
                        M_UserUsername = ?,
                        M_UserM_MouID = ?,
                        M_UserLastUpdated = now(),
                        M_UserM_UserID = ?
                    WHERE M_UserID = ? ";

                $query_update = $this->db->query(
                    $sql_update,
                    [
                        $M_UserM_DoctorID,
                        $doctor_id,
                        $M_UserUsername,
                        $M_UserM_MouID,
                        $userid,
                        $id
                    ]
                );

                if (!$query_update) {
                    $this->db->trans_rollback();
                    $this->sys_error_db("m_user update");
                    exit;
                }

                // json after
                $sql_json_after = "SELECT * 
                    FROM $this->db_str_doctor.m_user
                    WHERE M_UserIsActive = 'Y'
                    AND M_UserID = ?";

                $qry_json_after = $this->db->query(
                    $sql_json_after,
                    [
                        $id
                    ]
                );

                if (!$qry_json_after) {
                    $this->db->trans_rollback();
                    $this->sys_error_db("m_user select json after");
                    exit;
                }

                $data_after_by_id = $qry_json_after->row();

                $json_after_log = json_encode($data_after_by_id);

                $sql_insert_log = "INSERT INTO $this->db_str_doctor_log.m_user_log(
                        M_UserLogM_UserID,
                        M_UserLogStatus,
                        M_UserLogJSONBefore,
                        M_UserLogJSONAfter,
                        M_UserLogUserID,
                        M_UserLogCreated
                    ) VALUES (
                        ?,
                        'EDIT',
                        ?,
                        ?,
                        ?,
                        now()
                    )";

                $qry_insert_log = $this->db->query(
                    $sql_insert_log,
                    [
                        $id,
                        $json_before_log,
                        $json_after_log,
                        $userid
                    ]
                );

                if (!$qry_insert_log) {
                    $this->db_doctor_log->trans_rollback();
                    $this->sys_error_db("m_user edit log");
                    exit;
                }

                // sukses
                $this->db->trans_commit();
                $result = array(
                    "total" => 1,
                    "records" => array("xid" => 0)
                );
                $this->sys_ok($result);
            } else {
                // insert ke log dan update

                $sql_insert_select = "INSERT INTO $this->db_str_doctor_log.doctor_test_edit_mou (DoctorTestLogDoctorTestID,
                    DoctorTestLogDoctorTestM_DoctorID, DoctorTestLogDoctorTestT_TestID)
                    SELECT DoctorTestID, DoctorTestM_DoctorID, DoctorTestT_TestID
                    FROM $this->db_str_doctor.doctor_test
                    WHERE DoctorTestM_DoctorID =  ?
                    AND DoctorTestIsActive = 'Y'";

                // print_r($sql_insert_select);

                $query_insert = $this->db->query($sql_insert_select, [
                    $M_UserM_DoctorID
                ]);


                if (!$query_insert) {
                    // print_r($this->db_regional->last_query());
                    $this->db->trans_rollback();
                    $this->sys_error_db("ERR Code: insert log doctor test failed", $this->db);
                    exit;
                }

                // delete doctor test after insert
                $sql_delete = "UPDATE $this->db_str_doctor.doctor_test 
                    SET DoctorTestIsActive = 'N',
                    DoctorTestLastUpdated = now()
                    WHERE DoctorTestM_DoctorID = ?";

                // print_r($sql_delete);

                $query_delete = $this->db->query($sql_delete, [
                    $M_UserM_DoctorID
                ]);

                if (!$query_delete) {
                    $this->db->trans_rollback();
                    $this->sys_error_db("doctor_test delete");
                    exit;
                }

                // proses update
                // proses update m_user
                $sql_update = "UPDATE $this->db_str_doctor.m_user
                    SET
                        M_UserM_DoctorID = ?,
                        M_UserM_DoctorCode = ?,
                        M_UserUsername = ?,
                        M_UserM_MouID = ?,
                        M_UserLastUpdated = now(),
                        M_UserM_UserID = ?
                    WHERE M_UserID = ? ";

                $query_update = $this->db->query(
                    $sql_update,
                    [
                        $M_UserM_DoctorID,
                        $doctor_id,
                        $M_UserUsername,
                        $M_UserM_MouID,
                        $userid,
                        $id
                    ]
                );

                if (!$query_update) {
                    $this->db->trans_rollback();
                    $this->sys_error_db("m_user update");
                    exit;
                }

                // json after
                $sql_json_after = "SELECT * 
                    FROM $this->db_str_doctor.m_user
                    WHERE M_UserIsActive = 'Y'
                    AND M_UserID = ?";

                $qry_json_after = $this->db->query(
                    $sql_json_after,
                    [
                        $id
                    ]
                );

                if (!$qry_json_after) {
                    $this->db->trans_rollback();
                    $this->sys_error_db("m_user select json after");
                    exit;
                }

                $data_after_by_id = $qry_json_after->row();

                $json_after_log = json_encode($data_after_by_id);

                $sql_insert_log = "INSERT INTO $this->db_str_doctor_log.m_user_log(
                        M_UserLogM_UserID,
                        M_UserLogStatus,
                        M_UserLogJSONBefore,
                        M_UserLogJSONAfter,
                        M_UserLogUserID,
                        M_UserLogCreated
                    ) VALUES (
                        ?,
                        'EDIT',
                        ?,
                        ?,
                        ?,
                        now()
                    )";

                $qry_insert_log = $this->db->query(
                    $sql_insert_log,
                    [
                        $id,
                        $json_before_log,
                        $json_after_log,
                        $userid
                    ]
                );

                if (!$qry_insert_log) {
                    $this->db_doctor_log->trans_rollback();
                    $this->sys_error_db("m_user edit log");
                    exit;
                }

                // sukses
                $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);
        }
    }

    // delete
    public function delete()
    {
        try {
            //# cek token valid
            if (! $this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }

            //begin transaction 
            $this->db_regional->trans_begin();

            //# ambil parameter input
            $prm = $this->sys_input;
            $id = "";
            if (isset($prm['id'])) {
                $id = intval($prm["id"]);
            }
            if ($id == "" || !$id) {
                $error = array(
                    "message" => "id is mandatory",
                );
                $this->sys_error_db($error);
                exit;
            }
            $userid = $this->sys_user['M_UserID'];

            $DoctorTestM_DoctorID = "";

            $sql_get_data = "SELECT M_UserM_DoctorID
            FROM $this->db_str_doctor.m_user 
            WHERE M_UserID = $id";

            $qry_data = $this->db->query($sql_get_data, [
                $id
            ]);

            if (!$qry_data) {
                $this->db->trans_rollback();
                $this->sys_error_db("m_user select");
                exit;
            }
            $data_get = $qry_data->result_array();
            $DoctorTestM_DoctorID = $data_get[0]['M_UserM_DoctorID'];

            // print_r($sql_get_data);

            //$userid = 1;

            $sql_delete = "UPDATE $this->db_str_doctor.m_user 
            SET M_UserIsActive = 'N',
            M_UserLastUpdated = now(),
            M_UserM_UserID = ?
            WHERE M_UserID = ?";

            $query_delete = $this->db->query($sql_delete, [
                $userid,
                $id
            ]);

            if (!$query_delete) {
                $this->db->trans_rollback();
                $this->sys_error_db("m_user delete");
                exit;
            }

            // print_r($this->db_regional->last_query());

            // var_dump($this->db->affected_rows());
            // print_r($insert_id);

            $sql_json_before = "SELECT * 
            FROM $this->db_str_doctor.m_user
            WHERE M_UserIsActive = 'N'
            AND M_UserID = ?";

            $qry_json_before = $this->db->query(
                $sql_json_before,
                [
                    $id
                ]
            );

            if (!$qry_json_before) {
                $this->db->trans_rollback();
                $this->sys_error_db("m_user select json");
                exit;
            }

            $data_by_id = $qry_json_before->row();

            $json_after_log = json_encode($data_by_id);

            // print_r($json_after_log);

            $sql_insert_log = "INSERT INTO $this->db_str_doctor_log.m_user_log(
                M_UserLogM_UserID,
                M_UserLogStatus,
                M_UserLogJSONBefore,
                M_UserLogJSONAfter,
                M_UserLogUserID,
                M_UserLogCreated
            ) VALUES (
                ?,
                'DELETE',
                null,
                ?,
                ?,
                now()
            )";

            $qry_insert_log = $this->db->query(
                $sql_insert_log,
                [
                    $id,
                    $json_after_log,
                    $userid
                ]
            );

            if (!$qry_insert_log) {
                $this->db->trans_rollback();
                $this->sys_error_db("m_user delete log");
                exit;
            }

            // print_r($this->db_regional->last_query());

            // delete doctor test
            $sql_delete = "UPDATE $this->db_str_doctor.doctor_test 
            SET DoctorTestIsActive = 'N',
            DoctorTestLastUpdated = now()
            WHERE DoctorTestM_DoctorID = $DoctorTestM_DoctorID";

            // print_r($sql_delete);

            $query_delete = $this->db->query($sql_delete);

            if (!$query_delete) {
                $this->db->trans_rollback();
                $this->sys_error_db("doctor_test delete");
                exit;
            }

            // sukses
            $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);
        }
    }
}