<?php

use SebastianBergmann\Environment\Console;

use function PHPSTORM_META\type;

class Userv2 extends MY_Controller
{
    var $db_onedev;
    var $db_mitra;
    var $db_mitra_log;
    public function index()
    {
        echo "ANTIBIOTIC API";
    }
    public function __construct()
    {
        parent::__construct();
        $this->db_onedev = $this->load->database("regional", true);
        $this->db_mitra = "one_mitra";
        $this->db_mitra_log = "mitra_log";
    }

    function lookupmoubyidname()
    {
        try {
            //# cek token valid
            if (! $this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }

            $prm = $this->sys_input;
            $id = $prm['id'];
            $mouId = $prm['mouId'];
            $search = $prm['search'];
            $all = $prm['all'];
            $limit = '';
            if ($all == 'N') {
                $limit = ' LIMIT 10';
            }
            $number_limit = 10;
            $number_offset = ($prm['current_page'] - 1) * $number_limit;
            $sql = "select COUNT(*) as total
			FROM(SELECT *
                    from one_mitra.m_user
                    JOIN one_mitra.m_user_mou ON M_UserMouM_UserID = M_UserID
                    JOIN m_mou ON M_MouID = M_UserMouM_MouID
                    JOIN m_company ON M_CompanyID = M_UserM_CompanyID
                    WHERE
                    (M_UserMouAliasName LIKE '%$search%' OR
                    M_MouName LIKE '%$search%') AND
                    M_UserID = $id AND
				    M_UserIsActive = 'Y' 
                    GROUP BY M_UserMouID) a";
            //  $total = $this->db_onedev->query($sql,$sql_param)->row()->total;
            $query = $this->db_onedev->query($sql);
            //echo $this->db_onedev->last_query();
            $tot_count = 0;
            $tot_page = 0;
            if ($query) {
                $tot_count = $query->result_array()[0]["total"];
                $tot_page = ceil($tot_count / $number_limit);
            } else {
                $this->sys_error_db("m_mou count", $this->db_onedev);
                exit;
            }

            $sql = "select M_UserMouID as id,
                M_UserID,
                M_MouID,
                M_MouName,
                M_CompanyID,
                M_CompanyName,
                '' as isdefault,
                m_user_mou.*
                from one_mitra.m_user
                    JOIN one_mitra.m_user_mou ON M_UserMouM_UserID = M_UserID and M_UserMouIsActive = 'Y'
                    JOIN m_mou ON M_MouID = M_UserMouM_MouID
                    JOIN m_company ON M_CompanyID = M_UserM_CompanyID
                    WHERE
                    (M_UserMouAliasName LIKE '%$search%' OR
                    M_MouName LIKE '%$search%') AND
                    M_UserID = $id AND
				    M_UserIsActive = 'Y'
                    ORDER BY M_UserMouID DESC
                    limit $number_limit offset $number_offset";
            $sql_param = array($search);
            $query = $this->db_onedev->query($sql);
            // echo $this->db_onedev->last_query();
            // exit;
            if ($query) {
                $rows = $query->result_array();
            } else {
                $this->sys_error_db("m_mou 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);
        }
    }
    public function lookupcompanybyname()
    {
        try {
            //# cek token valid
            if (! $this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }

            $prm = $this->sys_input;
            $company = $prm['company'];
            $username = $prm['username'];
            $search = $prm['search'];
            $limit = '';
            if ($all == 'N') {
                $limit = ' LIMIT 10';
            }
            $number_limit = 10;
            $number_offset = ($prm['current_page'] - 1) * $number_limit;
            $sql = "select COUNT(*) as total
                    FROM(SELECT *
                            from one_mitra.m_user
                            JOIN m_company ON M_CompanyID = M_UserM_CompanyID
                            JOIN s_regional ON M_UserS_RegionalID = S_RegionalID
                            WHERE 
                            (M_CompanyName LIKE CONCAT('%','{$search}','%') OR
                            M_UserUsername LIKE CONCAT('%','{$search}','%')) AND
                            M_UserIsActive = 'Y'
                            GROUP BY M_UserID) a";
            // $sql_param = array($search);
            //  $total = $this->db_onedev->query($sql,$sql_param)->row()->total;
            $query = $this->db_onedev->query($sql);
            $tot_count = 0;
            $tot_page = 0;
            if ($query) {
                $tot_count = $query->result_array()[0]["total"];
                $tot_page = ceil($tot_count / $number_limit);
            } else {
                $this->sys_error_db("m_company count", $this->db_onedev);
                exit;
            }

            $sql = "select M_UserID as id,
                    M_CompanyID,
                    M_CompanyName,
                    m_user.*,
                    S_RegionalID,
                    S_RegionalName
                            from one_mitra.m_user
                            JOIN m_company ON M_CompanyID = M_UserM_CompanyID
                            JOIN s_regional ON M_UserS_RegionalID = S_RegionalID
                            WHERE 
                            (M_CompanyName LIKE CONCAT('%','{$search}','%') OR
                            M_UserUsername LIKE CONCAT('%','{$search}','%')) AND
                            M_UserIsActive = 'Y'
                            GROUP BY M_UserID
                            ORDER BY M_UserID DESC
                            limit $number_limit offset $number_offset";
            // $sql_param = array($search);
            $query = $this->db_onedev->query($sql);
            // echo $this->db_onedev->last_query();
            if ($query) {
                $rows = $query->result_array();
            } else {
                $this->sys_error_db("m_company 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);
        }
    }
    function addnewuser()
    {
        try {
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }
            $this->db_onedev->trans_begin();
            $prm = $this->sys_input;
            $userid = $this->sys_user['M_UserID'];

            $companyId = "";
            if (isset($prm["companyId"])) {
                $companyId = trim($prm["companyId"]);
            }

            $regionalId = "";
            if (isset($prm["regionalId"])) {
                $regionalId = trim($prm["regionalId"]);
            }

            $mouId = "";
            if (isset($prm["mouId"])) {
                $mouId = trim($prm["mouId"]);
            }

            $username = "";
            if (isset($prm["username"])) {
                $username = trim($prm["username"]);
            }

            $password = "";
            if (isset($prm["password"])) {
                $password = trim($prm["password"]);
            }

            $confirm_password = "";
            if (isset($prm["confirm_password"])) {
                $confirm_password = trim($prm["confirm_password"]);
            }

            $errors = array();
            if ($password !== $confirm_password) {
                array_push($errors, array('field' => 'password', 'msg' => 'password dan konfirmasi password harus sama'));
                $result = array(
                    "total" => -1,
                    "errors" => $errors,
                    "records" => array("xid" => 0)
                );
                $this->sys_ok($result);
                exit;
                exit;
            }

            // cek username tidak boleh sama
            $sql_cek_username = "SELECT count(*) as total_user
                FROM $this->db_mitra.m_user
                WHERE M_UserIsActive = 'Y'
                AND M_UserUsername = ?";
            $qry_cek_username = $this->db_onedev->query($sql_cek_username, [$username]);
            if ($qry_cek_username) {
                $get_count_username = $qry_cek_username->row_array();
            } else {
                $this->db_onedev->trans_rollback();
                $this->sys_error_db("ERROR, cek user", $this->db_onedev);
                exit;
            }

            if ($get_count_username["total_user"] == 0) {
                $sm_password = md5($this->one_salt . $prm["password"] . $this->one_salt);

                $sql_insert = "INSERT INTO $this->db_mitra.m_user(
                    M_UserM_CompanyID,
                    M_UserS_RegionalID,
                    M_UserM_MouID,
                    M_UserUsername,
                    M_UserPassword,
                    M_UserCreated,
                    M_UserLastUpdated,
                    M_UserLastAccess,
                    M_UserM_UserID) VALUES(?,?,?,?,?,NOW(),NOW(),NOW(),?)";
                $qry_insert = $this->db_onedev->query($sql_insert, [
                    $companyId,
                    $regionalId,
                    $mouId,
                    $username,
                    $sm_password,
                    $userid
                ]);
                if (!$qry_insert) {
                    $this->db_onedev->trans_rollback();
                    $this->sys_error_db("user insert error", $this->db_onedev);
                    exit;
                }

                $insert_id = $this->db_onedev->insert_id();

                $sql_usermou = "INSERT INTO $this->db_mitra.m_user_mou(
                        M_UserMouM_UserID,
                        M_UserMouM_MouID,
                        M_UserMouUserID,
                        M_UserMouCreated) VALUES(?,?,?,NOW())";
                $qry_usermou = $this->db_onedev->query($sql_usermou, [
                    $insert_id,
                    $mouId,
                    $userid
                ]);
                if (!$qry_usermou) {
                    $this->db_onedev->trans_rollback();
                    $this->sys_error_db("user mou insert error", $this->db_onedev);
                    exit;
                }

                $sql_json_before = "SELECT * 
                    FROM $this->db_mitra.m_user
                    WHERE M_UserIsActive = 'Y'
                    AND M_UserID = ?";
                $qry_json_before = $this->db_onedev->query($sql_json_before, [$insert_id]);
                if (!$qry_json_before) {
                    $this->db_onedev->trans_rollback();
                    $this->sys_error_db("m_user select json error", $this->db_onedev);
                    exit;
                }

                $data_by_id = $qry_json_before->row();

                $json_after_log = json_encode($data_by_id);

                $sql_insert_log = "INSERT INTO $this->db_mitra_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_onedev->query($sql_insert_log, [
                    $insert_id,
                    $json_after_log,
                    $userid
                ]);
                if (!$qry_insert_log) {
                    $this->db_onedev->trans_rollback();
                    $this->sys_error_db("m_user_log insert error", $this->db_onedev);
                    exit;
                }

                $this->db_onedev->trans_commit();
                $result = array(
                    "total" => 1,
                    "errors" => $errors,
                    "records" => array("xid" => 0)
                );
                $this->sys_ok($result);
            } else {
                $this->db_onedev->trans_rollback();
                array_push($errors, array('field' => 'username', 'msg' => 'Username sudah digunakan. Silahkan masukkan username yang lain'));
                $result = array(
                    "total" => -1,
                    "errors" => $errors,
                    "records" => array("xid" => 0)
                );
                $this->sys_ok($result);
                exit;
            }
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }

    function edituser()
    {
        try {
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }
            $this->db_onedev->trans_begin();
            $prm = $this->sys_input;
            $userid = $this->sys_user['M_UserID'];

            $Id = "";
            if (isset($prm["Id"])) {
                $Id = trim($prm["Id"]);
            }

            $companyId = "";
            if (isset($prm["companyId"])) {
                $companyId = trim($prm["companyId"]);
            }

            $regionalId = "";
            if (isset($prm["regionalId"])) {
                $regionalId = trim($prm["regionalId"]);
            }

            $mouId = "";
            if (isset($prm["mouId"])) {
                $mouId = trim($prm["mouId"]);
            }

            $username = "";
            if (isset($prm["username"])) {
                $username = trim($prm["username"]);
            }

            $errors = array();

            // cek username tidak boleh sama
            $sql_cek_username = "SELECT count(*) as total_user,
                M_UserID
                M_UserUsername,
                M_UserIsActive
                FROM $this->db_mitra.m_user
                WHERE M_UserIsActive = 'Y' 
                AND M_UserUsername = '{$username}' AND M_UserID != '{$Id}'";
            $qry_cek_username = $this->db_onedev->query($sql_cek_username);
            if ($qry_cek_username) {
                $get_rows_username = $qry_cek_username->row_array();
            } else {
                $this->db_onedev->trans_rollback();
                $this->sys_error_db("ERROR, cek user", $this->db_onedev);
                exit;
            }

            if ($get_rows_username["total_user"] == 0) {
                // print_r($username);
                // exit;
                // json before
                $sql_json_before = "SELECT * 
                FROM $this->db_mitra.m_user
                WHERE M_UserIsActive = 'Y'
                AND M_UserID = ?";

                $qry_json_before = $this->db_onedev->query($sql_json_before, [
                    $Id
                ]);

                if (!$qry_json_before) {
                    $this->db_onedev->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);

                $sql_insert = "UPDATE $this->db_mitra.m_user SET
                    M_UserM_CompanyID = ?,
                    M_UserS_RegionalID = ?,
                    M_UserUsername = ?,
                    M_UserLastUpdated = NOW(),
                    M_UserM_UserID = ?
                    WHERE M_UserID = ?";
                $qry_insert = $this->db_onedev->query($sql_insert, [
                    $companyId,
                    $regionalId,
                    $username,
                    $userid,
                    $Id
                ]);
                if (!$qry_insert) {
                    $this->db_onedev->trans_rollback();
                    $this->sys_error_db("update user error", $this->db_onedev);
                    exit;
                }

                // json after
                $sql_json_after = "SELECT * 
                    FROM $this->db_mitra.m_user
                    WHERE M_UserIsActive = 'Y'
                    AND M_UserID = ?";
                $qry_json_after = $this->db_onedev->query($sql_json_after, [$Id]);
                if (!$qry_json_after) {
                    $this->db_onedev->trans_rollback();
                    $this->sys_error_db("m_user select json error", $this->db_onedev);
                    exit;
                }

                $data_by_id = $qry_json_after->row();

                $json_after_log = json_encode($data_by_id);

                $sql_insert_log = "INSERT INTO $this->db_mitra_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_onedev->query($sql_insert_log, [
                    $Id,
                    $json_before_log,
                    $json_after_log,
                    $userid
                ]);
                if (!$qry_insert_log) {
                    $this->db_onedev->trans_rollback();
                    $this->sys_error_db("m_user_log insert error", $this->db_onedev);
                    exit;
                }


                $this->db_onedev->trans_commit();
                $result = array(
                    "total" => 1,
                    "records" => array("xid" => 0)
                );
                $this->sys_ok($result);
            } else {
                $this->db_onedev->trans_rollback();
                array_push($errors, array('field' => 'username', 'msg' => 'Username sudah digunakan. Silahkan masukkan username yang lain'));
                $result = array(
                    "total" => -1,
                    "errors" => $errors,
                    "records" => array("xid" => 0)
                );
                $this->sys_ok($result);
                exit;
            }
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }

    public function addnewmou()
    {
        try {
            //# cek token valid
            if (! $this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }

            //# ambil parameter input
            $prm = $this->sys_input;
            $companyid = $prm['companyid'];
            $M_MouName = $prm['M_MouName'];
            $M_MouMinValueR = $prm['M_MouMinValueR'];
            $M_MouMaxValueR = $prm['M_MouMaxValueR'];
            $M_MouMinInclusiveR = $prm['M_MouMinInclusiveR'];
            $M_MouMaxInclusiveR = $prm['M_MouMaxInclusiveR'];
            $M_MouNoteR = $prm['M_MouNoteR'];
            $M_MouMinValueS = $prm['M_MouMinValueS'];
            $M_MouMaxValueS = $prm['M_MouMaxValueS'];
            $M_MouMinInclusiveS = $prm['M_MouMinInclusiveS'];
            $M_MouMaxInclusiveS = $prm['M_MouMaxInclusiveS'];
            $M_MouNoteS = $prm['M_MouNoteS'];
            $M_MouMinValueI = $prm['M_MouMinValueI'];
            $M_MouMaxValueI = $prm['M_MouMaxValueI'];
            $M_MouMinInclusiveI = $prm['M_MouMinInclusiveI'];
            $M_MouMaxInclusiveI = $prm['M_MouMaxInclusiveI'];
            $M_MouNoteI = $prm['M_MouNoteI'];
            $userid = $this->sys_user["M_UserID"];

            if ($companyid == 0) {
                $errors = array();
                if ($companyid == 0) {
                    array_push($errors, array('field' => 'company', 'msg' => 'Bakteri dipilih dulu dong'));
                }
                $result = array("total" => -1, "errors" => $errors, "records" => 0);
                $this->sys_ok($result);
            } else {
                if ($prm['xid'] == 0) {
                    $sql = "insert into m_mou(
                        M_MouName,	
                        M_MouM_CompanyID,
                        M_MouMinValueR,	
                        M_MouMaxValueR,	
                        M_MouMinInclusiveR,
                        M_MouMaxInclusiveR,
                        M_MouNoteR,	
                        M_MouMinValueS,	
                        M_MouMaxValueS,
                        M_MouMinInclusiveS,
                        M_MouMaxInclusiveS,
                        M_MouNoteS,	
                        M_MouMinValueI,	
                        M_MouMaxValueI,	
                        M_MouMinInclusiveI,
                        M_MouMaxInclusiveI,
                        M_MouNoteI,	
                        M_MouUserID,
                        M_MouCreated,	
                        M_MouLastUpdated)
                     values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,now(),now())";
                    $query = $this->db_onedev->query(
                        $sql,
                        array(
                            $M_MouName,
                            $companyid,
                            $M_MouMinValueR,
                            $M_MouMaxValueR,
                            $M_MouMinInclusiveR,
                            $M_MouMaxInclusiveR,
                            $M_MouNoteR,
                            $M_MouMinValueS,
                            $M_MouMaxValueS,
                            $M_MouMinInclusiveS,
                            $M_MouMaxInclusiveS,
                            $M_MouNoteS,
                            $M_MouMinValueI,
                            $M_MouMaxValueI,
                            $M_MouMinInclusiveI,
                            $M_MouMaxInclusiveI,
                            $M_MouNoteI,
                            $userid
                        )
                    );
                    if (!$query) {
                        $this->sys_error_db("m_mou insert", $this->db_onedev);
                        exit;
                    }
                    $last_id = $this->db_onedev->insert_id();
                    $result = array("total" => 1, "records" => array("xid" => 0));
                    $this->sys_ok($result);
                } else {
                    $sql = "UPDATE m_mou SET M_MouM_CompanyID = '{$companyid}',
                M_MouName = '{$M_MouName}',	
                M_MouMinValueR = '{$M_MouMinValueR}',		
                M_MouMaxValueR = '{$M_MouMaxValueR}',	
                M_MouMinInclusiveR = '{$M_MouMinInclusiveR}',
                M_MouMaxInclusiveR = '{$M_MouMaxInclusiveR}',
                M_MouNoteR = '{$M_MouNoteR}',	
                M_MouMinValueS = '{$M_MouMinValueS}',	
                M_MouMaxValueS = '{$M_MouMaxValueS}',
                M_MouMinInclusiveS = '{$M_MouMinInclusiveS}',
                M_MouMaxInclusiveS = '{$M_MouMaxInclusiveS}',
                M_MouNoteS = '{$M_MouNoteS}',	
                M_MouMinValueI = '{$M_MouMinValueI}',	
                M_MouMaxValueI = '{$M_MouMaxValueI}',	
                M_MouMinInclusiveI = '{$M_MouMinInclusiveI}',
                M_MouMaxInclusiveI = '{$M_MouMaxInclusiveI}',
                M_MouNoteI = '{$M_MouNoteI}',	
                M_MouUserID = '{$userid}',	
                M_MouLastUpdated  = now()
                WHERE M_MouID = '{$prm['xid']}'";
                    //echo $sql;
                    $query = $this->db_onedev->query($sql);
                    $result = array("total" => 1, "records" => array("xid" => 0));
                    $this->sys_ok($result);
                }
            }
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }

    function deleteuser()
    {
        try {
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }
            $this->db_onedev->trans_begin();
            $prm = $this->sys_input;
            $userid = $this->sys_user['M_UserID'];

            $Id = "";
            if (isset($prm["Id"])) {
                $Id = trim($prm["Id"]);
            }

            $sql = "UPDATE $this->db_mitra.m_user SET 
                M_UserIsActive = 'N',
                M_UserLastUpdated = NOW(),
                M_UserM_UserID = ?
                WHERE M_UserID = ?";
            $qry = $this->db_onedev->query($sql, [$userid, $Id]);
            if (!$qry) {
                $this->db_onedev->trans_rollback();
                $this->sys_error_db("m_user delete error", $this->db_onedev);
                exit;
            }

            $sql_json_before = "SELECT * 
            FROM $this->db_mitra.m_user
            WHERE M_UserIsActive = 'N'
            AND M_UserID = ?";

            $qry_json_before = $this->db_onedev->query($sql_json_before, [$Id]);

            if (!$qry_json_before) {
                $this->db_onedev->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);

            $sql_insert_log = "INSERT INTO $this->db_mitra_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_onedev->query($sql_insert_log, [
                $Id,
                $json_after_log,
                $userid
            ]);
            if (!$qry_insert_log) {
                $this->db_onedev->trans_rollback();
                $this->sys_error_db("m_user_log insert error", $this->db_onedev);
                exit;
            }

            $sql_usermou = "UPDATE  $this->db_mitra.m_user_mou SET
                    M_UserMouIsActive = 'N',
                    M_UserMouUserID = ?,
                    M_UserMouLastUpdated = NOW()
                    WHERE M_UserMouID = ? ";
            $qry_usermou = $this->db_onedev->query($sql_usermou, [$userid, $Id]);
            if (!$qry_usermou) {
                $this->db_onedev->trans_rollback();
                $this->sys_error_db("m_user mou delete error", $this->db_onedev);
                exit;
            }

            $this->db_onedev->trans_commit();
            $result = array(
                "total" => 1,
                "records" => array("xid" => 0)
            );
            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }

    public function deletemou()
    {
        try {
            //# cek token valid
            if (! $this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }

            //# ambil parameter input
            $prm = $this->sys_input;
            $userid = $this->sys_user["M_UserID"];
            $sql = "update $this->db_mitra.m_user_mou SET 
            M_UserMouIsActive = 'N',
            M_UserMouUserID = ?,
            M_UserMouLastUpdated = now()
                    WHERE
                    M_UserMouID = ?
                
                    ";

            $query = $this->db_onedev->query(
                $sql,
                array(
                    $userid,
                    $prm['id']
                )
            );
            // echo $query;
            if (!$query) {
                $this->sys_error_db("m_mou delete");
                exit;
            }


            $result = array("total" => 1, "records" => array("xid" => 0));
            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }

    public function addnewusermou()
    {
        try {
            //# cek token valid
            if (! $this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }

            //# ambil parameter input
            $prm = $this->sys_input;
            $userid = $this->sys_user["M_UserID"];

            $mouId = $prm["mouId"];
            $muserId = $prm["muserId"];
            $name = $prm["name"];

            $sql_usermou = "INSERT INTO $this->db_mitra.m_user_mou(
                M_UserMouM_UserID,
                M_UserMouM_MouID,
                M_UserMouAliasName,
                M_UserMouIsDefault,
                M_UserMouUserID,
                M_UserMouCreated) VALUES(?,?,?,'N',?,NOW())";
            $qry_usermou = $this->db_onedev->query($sql_usermou, [
                $muserId,
                $mouId,
                $name,
                $userid
            ]);

            if (!$qry_usermou) {
                $this->sys_error_db("m_user_mou insert error");
                exit;
            }

            $result = array("total" => 1, "records" => array("xid" => 0));
            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }

    public function updateusermou()
    {
        try {
            //# cek token valid
            if (! $this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }

            //# ambil parameter input
            $this->db_regional->trans_begin();
            $prm = $this->sys_input;
            $userid = $this->sys_user["M_UserID"];

            $id = $prm["id"];
            $name = $prm["name"];
            $muserId = $prm["muserId"];
            $mouId = $prm["mouId"];
            $usermoId = $prm["usermoId"];
            $isdefault = $prm["isdefault"];

            if ($isdefault === 'Y') {

                // Update semua status default menjadi N
                $sql_unset_default = "UPDATE $this->db_mitra.m_user_mou
                      SET M_UserMouIsDefault = 'N',
                          M_UserMouUserID = ?, 
                          M_UserMouLastUpdated = NOW()
                      WHERE M_UserMouM_UserID = ?
                        AND M_UserMouIsDefault = 'Y'";
                $qry_unset_default = $this->db_onedev->query($sql_unset_default, [$userid, $muserId]);

                if (!$qry_unset_default) {
                    $this->sys_error_db("Unset default error", $this->db_onedev);
                    exit;
                }

                // Set M_UserMouM_MouID = 2789 menjadi default
                $sql_set_new_default = "UPDATE $this->db_mitra.m_user_mou
                        SET M_UserMouIsDefault = 'Y',
                            M_UserMouUserID = ?, 
                            M_UserMouLastUpdated = NOW()
                        WHERE M_UserMouM_UserID = ?
                          AND M_UserMouM_MouID = ?";
                $qry_set_new_default = $this->db_onedev->query($sql_set_new_default, [$userid, $muserId, $mouId]);

                if (!$qry_set_new_default) {
                    $this->sys_error_db("Set new default error", $this->db_onedev);
                    exit;
                }

                // Update M_UserM_MouID di m_user
                $sql_update_user = "UPDATE $this->db_mitra.m_user
                    SET M_UserM_MouID = ?,
                        M_UserM_UserID = ?,
                        M_UserLastUpdated = NOW()
                    WHERE M_UserID = ?";
                $qry_update_user = $this->db_onedev->query($sql_update_user, [$mouId, $userid, $muserId]);

                if (!$qry_update_user) {
                    $this->sys_error_db("Update user error", $this->db_onedev);
                    exit;
                }

                // $sql_select = "SELECT M_UserMouID FROM $this->db_mitra.m_user_mou WHERE M_UserMouIsActive = 'Y' AND M_UserMouIsDefault = 'Y'
                // AND M_UserMouM_UserID = ? AND M_UserMouM_MouID = ?";
                // $qry_select = $this->db_onedev->query($sql_select, [$muserId, $mouId]);
                // // echo $this->db_onedev->last_query();
                // // exit;
                // if ($qry_select) {
                //     $rows_cek = $qry_select->result_array();
                // } else {
                //     $this->sys_error_db("m user mou error", $this->db_onedev);
                //     exit;
                // }

                // if (count($rows_cek) > 0) {
                //     print_r("is default jadi n");
                //     exit;
                //     $sql = "UPDATE $this->db_mitra.m_user_mou SET
                //             M_UserMouIsDefault = 'N',
                //             M_UserMouUserID = ?,
                //             M_UserMouLastUpdated = NOW()
                //             WHERE M_UserMouID = ? AND M_UserMouIsDefault = 'Y";
                //     $qry = $this->db_onedev->query($sql, [$userid, $id, $mouId]);
                //     if (!$qry) {
                //         $this->sys_error_db("update user mou error", $this->db_onedev);
                //         exit;
                //     }
                // } else {
                //     print_r("is default tetap y");
                //     exit;
                //     $sql = "UPDATE $this->db_mitra.m_user_mou SET
                //     M_UserMouAliasName = ?,
                //     M_UserMouIsDefault = ?,
                //     M_UserMouUserID = ?,
                //     M_UserMouLastUpdated = NOW()
                //     WHERE M_UserMouID = ?";
                //     $qry = $this->db_onedev->query($sql, [$name, $isdefault, $userid, $id]);
                //     if (!$qry) {
                //         $this->sys_error_db("update user mou error", $this->db_onedev);
                //         exit;
                //     }

                //     $sql_user = "UPDATE $this->db_mitra.m_user SET 
                //     M_UserM_MouID = ?,
                //     M_UserLastUpdated = NOW(),
                //     M_UserM_UserID = ?
                //     WHERE M_UserID = ?";
                //     $qry_user = $this->db_onedev->query($sql_user, [$mouId, $userid, $muserId]);
                //     if (!$qry_user) {
                //         $this->sys_error_db("update user error", $this->db_onedev);
                //         exit;
                //     }

                //     $sql = "UPDATE $this->db_mitra.m_user_mou SET
                //     M_UserMouIsDefault = 'N',
                //     M_UserMouUserID = ?,
                //     M_UserMouLastUpdated = NOW()
                //     WHERE M_UserMouID = ? AND M_UserMouIsDefault = 'Y";
                //     $qry = $this->db_onedev->query($sql, [$userid, $id, $mouId]);
                //     if (!$qry) {
                //         $this->sys_error_db("update user mou error", $this->db_onedev);
                //         exit;
                //     }
                // }
            } else {
                $sql_select = "SELECT M_UserMouID FROM $this->db_mitra.m_user_mou WHERE M_UserMouIsActive = 'Y' AND M_UserMouIsDefault = 'Y'
                AND M_UserMouM_UserID = ? AND M_UserMouM_MouID = ?";
                $qry_select = $this->db_onedev->query($sql_select, [$muserId, $mouId]);
                if ($qry_select) {
                    $rows_cek = $qry_select->result_array();
                } else {
                    $this->sys_error_db("m user mou error", $this->db_onedev);
                    exit;
                }

                if (count($rows_cek) > 0) {
                    $sql = "UPDATE $this->db_mitra.m_user_mou SET
                    M_UserMouAliasName = ?,
                    M_UserMouUserID = ?,
                    M_UserMouLastUpdated = NOW()
                    WHERE M_UserMouID = ?";
                    $qry = $this->db_onedev->query($sql, [$name, $userid, $id]);
                    if (!$qry) {
                        $this->sys_error_db("update user mou error", $this->db_onedev);
                        exit;
                    }
                } else {
                    $sql = "UPDATE $this->db_mitra.m_user_mou SET
                    M_UserMouAliasName = ?,
                    M_UserMouIsDefault = 'N',
                    M_UserMouUserID = ?,
                    M_UserMouLastUpdated = NOW()
                    WHERE M_UserMouID = ?";
                    $qry = $this->db_onedev->query($sql, [$name, $userid, $id]);
                    if (!$qry) {
                        $this->sys_error_db("update user mou error", $this->db_onedev);
                        exit;
                    }
                }
            }

            $this->db_regional->trans_commit();
            $result = array("total" => 1, "records" => array("xid" => 0));
            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }

    public function searchmou()
    {
        try {
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }
            $prm = $this->sys_input;

            $search = "";
            $companyId = $prm['companyId'];
            $number_limit = 10;
            $tot_count = 0;

            if (isset($prm['search'])) {
                $search = trim($prm["search"]);
                if ($search != "") {
                    $search = '%' . $prm['search'] . '%';
                } else {
                    $search = '%%';
                }
            }

            $sql_filter = "SELECT count(*) as total
                FROM m_mou
                    WHERE M_MouM_CompanyID = ?
                    AND M_MouIsActive = 'Y'
                    AND M_MouIsReleased = 'Y'
                    AND (M_MouName LIKE ?)
                    ";
            $qry_filter = $this->db_onedev->query($sql_filter, [$companyId, $search]);
            if ($qry_filter) {
                $tot_count = $qry_filter->result_array()[0]["total"];
            } else {
                $this->sys_error_db("mou count");
                exit;
            }

            $sql_search = "SELECT
                M_MouID,
                M_MouName,
                M_MouNumber
                FROM m_mou
                    WHERE M_MouM_CompanyID = ?
                    AND M_MouIsActive = 'Y'
                    AND M_MouIsReleased = 'Y'
                    AND (M_MouName LIKE ?)
                    ";
            $qry_search = $this->db_onedev->query($sql_search, [$companyId, $search]);
            if ($qry_search) {
                $rows = $qry_search->result_array();
            } else {
                $this->db_onedev->trans_rollback();
                $this->sys_error_db("mou select error", $this->db_onedev);
                exit;
            }

            $result = array(
                "total" => $tot_count,
                "total_display" => sizeof($rows),
                "records" => $rows
            );
            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }

    public function searchmoualias()
    {
        try {
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }
            $prm = $this->sys_input;

            $search = "";
            $companyId = $prm['companyId'];
            $muserId = $prm['muserId'];
            $mouId = $prm['mouId'];
            $number_limit = 10;
            $tot_count = 0;

            if (isset($prm['search'])) {
                $search = trim($prm["search"]);
                if ($search != "") {
                    $search = '%' . $prm['search'] . '%';
                } else {
                    $search = '%%';
                }
            }

            $sql_select = "SELECT M_UserMouM_MouID FROM $this->db_mitra.m_user_mou WHERE M_UserMouIsActive = 'Y'
                        AND M_UserMouM_UserID = ?";
            $qry_select = $this->db_onedev->query($sql_select, [$muserId]);
            if ($qry_select) {
                $rows_cek = $qry_select->result_array();
            } else {
                $this->sys_error_db("m user mou error", $this->db_onedev);
                exit;
            }

            $excludedMouIds = array_column($rows_cek, 'M_UserMouM_MouID');
            // Buat string placeholder untuk NOT IN jika ada data
            $placeholders = implode(',', $excludedMouIds);
            // print_r($placeholders);
            // exit;


            $sql_filter = "SELECT count(*) as total
                FROM m_mou
                    WHERE M_MouM_CompanyID = ?
                    AND M_MouIsActive = 'Y'
                    AND M_MouIsReleased = 'Y'
                    AND (M_MouName LIKE ?)
                    AND M_MouID NOT IN ($placeholders)
                    ";
            $qry_filter = $this->db_onedev->query($sql_filter, [$companyId, $search]);
            // echo $this->db_onedev->last_query();
            // exit;
            if ($qry_filter) {
                $tot_count = $qry_filter->result_array()[0]["total"];
            } else {
                $this->sys_error_db("mou count");
                exit;
            }

            $sql_search = "SELECT
                M_MouID,
                M_MouName,
                M_MouNumber
                FROM m_mou
                    WHERE M_MouM_CompanyID = ?
                    AND M_MouIsActive = 'Y'
                    AND M_MouIsReleased = 'Y'
                    AND (M_MouName LIKE ?)
                    AND M_MouID NOT IN ($placeholders)
                    ";
            $qry_search = $this->db_onedev->query($sql_search, [$companyId, $search]);
            // echo $this->db_onedev->last_query();
            // exit;
            if ($qry_search) {
                $rows = $qry_search->result_array();
            } else {
                $this->db_onedev->trans_rollback();
                $this->sys_error_db("mou select error", $this->db_onedev);
                exit;
            }


            $result = array(
                "total" => $tot_count,
                "total_display" => sizeof($rows),
                "records" => $rows
            );
            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }

    function searchcompanybyname()
    {
        if (! $this->isLogin) {
            $this->sys_error("Invalid Token");
            exit;
        }
        $prm = $this->sys_input;

        $max_rst = 12;
        $tot_count = 0;

        $q = [
            'search'     => '%'
        ];

        if ($prm['search'] != '') {
            $q['search'] = "%{$prm['search']}%";
        }

        // QUERY TOTAL
        $sql = "SELECT count(*) as total
                FROM m_company
                WHERE 
                M_CompanyName like ?
                AND M_CompanyIsActive = 'Y'";
        $query = $this->db_onedev->query($sql, $q['search']);
        //echo $query;
        if ($query) {
            $tot_count = $query->result_array()[0]["total"];
        } else {
            $this->sys_error_db("m_company count", $this->db_onedev);
            exit;
        }

        $sql = "
        SELECT M_CompanyID, M_CompanyName
        FROM m_company
            WHERE 
            M_CompanyName like ?
            AND M_CompanyIsActive = 'Y'
            ORDER BY M_CompanyName ASC
          ";
        $query = $this->db_onedev->query($sql, array($q['search']));

        if ($query) {
            $rows = $query->result_array();
            //echo $this->db_onedev->last_query();
            $result = array("total" => $tot_count, "records" => $rows, "total_display" => sizeof($rows));
            $this->sys_ok($result);
        } else {
            $this->sys_error_db("m_company rows", $this->db_onedev);
            exit;
        }
    }


    function searchcity()
    {
        if (! $this->isLogin) {
            $this->sys_error("Invalid Token");
            exit;
        }
        $prm = $this->sys_input;

        $max_rst = 12;
        $tot_count = 0;

        $q = [
            'search'     => '%'
        ];

        if ($prm['search'] != '') {
            $q['search'] = "%{$prm['search']}%";
        }

        // QUERY TOTAL
        $sql = "SELECT count(*) as total
                FROM m_city
                WHERE 
                M_CityName like ?
                AND M_CityIsActive = 'Y'";
        $query = $this->db_onedev->query($sql, $q['search']);
        //echo $query;
        if ($query) {
            $tot_count = $query->result_array()[0]["total"];
        } else {
            $this->sys_error_db("m_city count", $this->db_onedev);
            exit;
        }

        $sql = "
            SELECT *
            FROM m_city
            WHERE 
            M_CityName like ?
            AND M_CityIsActive = 'Y'
            ORDER BY M_CityName ASC
          ";
        $query = $this->db_onedev->query($sql, array($q['search']));

        if ($query) {
            $rows = $query->result_array();
            //echo $this->db_onedev->last_query();
            $result = array("total" => $tot_count, "records" => $rows, "total_display" => sizeof($rows));
            $this->sys_ok($result);
        } else {
            $this->sys_error_db("m_city rows", $this->db_onedev);
            exit;
        }
    }

    function searchdistrict()
    {
        if (! $this->isLogin) {
            $this->sys_error("Invalid Token");
            exit;
        }
        $prm = $this->sys_input;
        $id = $prm['id'];

        $max_rst = 12;
        $tot_count = 0;

        $q = [
            'search'     => '%'
        ];

        if ($prm['search'] != '') {
            $q['search'] = "%{$prm['search']}%";
        }

        // QUERY TOTAL
        $sql = "SELECT count(*) as total
            FROM m_district
            WHERE 
            M_DistrictName like ?
            AND M_DistrictM_CityID = '{$id}'
            AND M_DistrictIsActive = 'Y'";
        $query = $this->db_onedev->query($sql, $q['search']);
        //echo $query;
        if ($query) {
            $tot_count = $query->result_array()[0]["total"];
        } else {
            $this->sys_error_db("m_district count", $this->db_onedev);
            exit;
        }

        $sql = "
        SELECT *
        FROM m_district
        WHERE 
        M_DistrictName like ?
        AND M_DistrictM_CityID = '{$id}'
        AND M_DistrictIsActive = 'Y'
        ORDER BY M_DistrictName ASC
      ";
        $query = $this->db_onedev->query($sql, array($q['search']));

        if ($query) {
            $rows = $query->result_array();
            //echo $this->db_onedev->last_query();
            $result = array("total" => $tot_count, "records" => $rows, "total_display" => sizeof($rows));
            $this->sys_ok($result);
        } else {
            $this->sys_error_db("m_district rows", $this->db_onedev);
            exit;
        }
    }
    function searchkelurahan()
    {
        if (! $this->isLogin) {
            $this->sys_error("Invalid Token");
            exit;
        }
        $prm = $this->sys_input;
        $id = $prm['id'];

        $max_rst = 12;
        $tot_count = 0;

        $q = [
            'search'     => '%'
        ];

        if ($prm['search'] != '') {
            $q['search'] = "%{$prm['search']}%";
        }

        // QUERY TOTAL
        $sql = "SELECT count(*) as total
            FROM m_kelurahan
            WHERE 
            M_KelurahanName like ?
            AND M_KelurahanM_DistrictID = '{$id}'
            AND M_KelurahanIsActive = 'Y'";
        $query = $this->db_onedev->query($sql, $q['search']);
        //echo $query;
        if ($query) {
            $tot_count = $query->result_array()[0]["total"];
        } else {
            $this->sys_error_db("m_district count", $this->db_onedev);
            exit;
        }

        $sql = "
        SELECT *
        FROM m_kelurahan
        WHERE 
        M_KelurahanName like ?
        AND M_KelurahanM_DistrictID = '{$id}'
        AND M_KelurahanIsActive = 'Y'
        ORDER BY M_KelurahanName ASC
      ";
        $query = $this->db_onedev->query($sql, array($q['search']));

        if ($query) {
            $rows = $query->result_array();
            //echo $this->db_onedev->last_query();
            $result = array("total" => $tot_count, "records" => $rows, "total_display" => sizeof($rows));
            $this->sys_ok($result);
        } else {
            $this->sys_error_db("m_district rows", $this->db_onedev);
            exit;
        }
    }
    function searchcompany()
    {
        if (! $this->isLogin) {
            $this->sys_error("Invalid Token");
            exit;
        }
        $prm = $this->sys_input;

        $max_rst = 12;
        $tot_count = 0;

        $q = [
            'search'     => '%'
        ];

        if ($prm['search'] != '') {
            $q['search'] = "%{$prm['search']}%";
        }

        // QUERY TOTAL
        $sql = "SELECT count(*) as total
            FROM m_company
            WHERE 
            M_CompanyName like ?
            AND M_CompanyIsActive = 'Y'";
        $query = $this->db_onedev->query($sql, $q['search']);
        //echo $query;
        if ($query) {
            $tot_count = $query->result_array()[0]["total"];
        } else {
            $this->sys_error_db("m_company count", $this->db_onedev);
            exit;
        }

        $sql = "
        SELECT *
        FROM m_company
        WHERE 
        M_CompanyName like ?
        AND M_CompanyIsActive = 'Y'
        ORDER BY M_CompanyName ASC
      ";
        $query = $this->db_onedev->query($sql, array($q['search']));

        if ($query) {
            $rows = $query->result_array();
            //echo $this->db_onedev->last_query();
            $result = array("total" => $tot_count, "records" => $rows, "total_display" => sizeof($rows));
            $this->sys_ok($result);
        } else {
            $this->sys_error_db("m_company rows", $this->db_onedev);
            exit;
        }
    }
    function searchmou_bckp()
    {
        if (! $this->isLogin) {
            $this->sys_error("Invalid Token");
            exit;
        }
        $prm = $this->sys_input;
        $id = $prm['id'];

        $max_rst = 12;
        $tot_count = 0;

        $q = [
            'search'     => '%'
        ];

        if ($prm['search'] != '') {
            $q['search'] = "%{$prm['search']}%";
        }

        // QUERY TOTAL
        $sql = "SELECT count(*) as total
            FROM m_mou
            WHERE 
            M_MouName like ?
            AND M_MouM_CompanyID = '{$id}'
            AND M_MouIsActive = 'Y'";
        $query = $this->db_onedev->query($sql, $q['search']);
        //echo $query;
        if ($query) {
            $tot_count = $query->result_array()[0]["total"];
        } else {
            $this->sys_error_db("m_mou count", $this->db_onedev);
            exit;
        }

        $sql = "SELECT *
        FROM m_mou
        WHERE 
        M_MouName like ?
        AND M_MouM_CompanyID = '{$id}'
        AND M_MouIsActive = 'Y'
        ORDER BY M_MouName ASC
      ";
        $query = $this->db_onedev->query($sql, array($q['search']));

        if ($query) {
            $rows = $query->result_array();
            //  echo $this->db_onedev->last_query();
            $result = array("total" => $tot_count, "records" => $rows, "total_display" => sizeof($rows));
            $this->sys_ok($result);
        } else {
            $this->sys_error_db("m_mou rows", $this->db_onedev);
            exit;
        }
    }
    function searchdoctor()
    {
        if (! $this->isLogin) {
            $this->sys_error("Invalid Token");
            exit;
        }
        $prm = $this->sys_input;

        $max_rst = 12;
        $tot_count = 0;

        $q = [
            'search'     => '%'
        ];

        if ($prm['search'] != '') {
            $q['search'] = "%{$prm['search']}%";
        }

        // QUERY TOTAL
        $sql = "SELECT count(*) as total
            FROM(SELECT M_DoctorID, CONCAT(M_DoctorPrefix, ' ',M_DoctorName) as M_DoctorName
            FROM m_doctor
            WHERE M_DoctorIsActive = 'Y') a
                WHERE 
                M_DoctorName like ?";
        $query = $this->db_onedev->query($sql, $q['search']);
        //echo $query;
        if ($query) {
            $tot_count = $query->result_array()[0]["total"];
        } else {
            $this->sys_error_db("m_doctor count", $this->db_onedev);
            exit;
        }

        $sql = "SELECT * FROM(SELECT M_DoctorID, CONCAT(M_DoctorPrefix, ' ',M_DoctorName) as M_DoctorName
    FROM m_doctor
    WHERE M_DoctorIsActive = 'Y') a
        WHERE 
        M_DoctorName like ?
        ORDER BY M_DoctorName ASC
      ";
        $query = $this->db_onedev->query($sql, array($q['search']));

        if ($query) {
            $rows = $query->result_array();
            //echo $this->db_onedev->last_query();
            $result = array("total" => $tot_count, "records" => $rows, "total_display" => sizeof($rows));
            $this->sys_ok($result);
        } else {
            $this->sys_error_db("m_doctor rows", $this->db_onedev);
            exit;
        }
    }
    function selectaddressdoctor()
    {

        try {
            //# cek token valid
            if (! $this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }
            $prm = $this->sys_input;
            $rows = [];
            $query = "SELECT M_DoctorAddressID,
            CONCAT(M_DoctorAddressNote, ': ',M_DoctorAddressDescription) as M_DoctorAddressNote
            FROM
            m_doctoraddress
            WHERE M_DoctorAddressIsActive = 'Y' AND M_DoctorAddressM_DoctorID = '{$id}'";
            //echo $query;
            $rows['addressdoctors'] = $this->db_onedev->query($query)->result_array();


            $result = array(
                "total" => count($rows),
                "records" => $rows,
            );
            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }

    function get_regional()
    {
        try {
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }
            $prm = $this->sys_input;

            $sql = "SELECT S_RegionalID,
                S_RegionalName
                FROM s_regional
                WHERE S_RegionalIsActive = 'Y'
                AND S_RegionalIsDefault = 'Y'";
            $qry = $this->db_onedev->query($sql);
            if ($qry) {
                $rows = $qry->result_array();
            } else {
                $this->sys_error_db("regional select error", $this->db_onedev);
                exit;
            }

            $result = array(
                "records" => $rows,
                "sql" => $this->db_onedev->last_query()
            );
            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }
}