647 lines
22 KiB
PHP
647 lines
22 KiB
PHP
<?php
|
|
class User extends MY_Controller
|
|
{
|
|
var $load;
|
|
var $db_mitra;
|
|
var $db_mitra_log;
|
|
public function __construct()
|
|
{
|
|
parent::__construct();
|
|
$this->db_regional = $this->load->database("regional", true);
|
|
$this->db_mitra = "one_mitra";
|
|
$this->db_mitra_log = "mitra_log";
|
|
}
|
|
|
|
public function index()
|
|
{
|
|
// $cek = $this->db_regional->query("select database() as current_db")->result();
|
|
// print_r($cek);
|
|
echo "MASTER USER";
|
|
}
|
|
|
|
function search()
|
|
{
|
|
try {
|
|
if (!$this->isLogin) {
|
|
$this->sys_error("Invalid Token");
|
|
exit;
|
|
}
|
|
$prm = $this->sys_input;
|
|
$userid = $this->sys_user['M_UserID'];
|
|
$search = "";
|
|
if (isset($prm["search"])) {
|
|
$search = trim($prm["search"]);
|
|
if ($search != "") {
|
|
$search = "%" . $prm["search"] . "%";
|
|
} else {
|
|
$search = "%%";
|
|
}
|
|
}
|
|
|
|
$number_offset = 0;
|
|
$number_limit = 10;
|
|
if ($prm["current_page"] > 0) {
|
|
$number_offset = ($prm["current_page"] - 1) * $number_limit;
|
|
}
|
|
|
|
$sql_filter = "SELECT count(*) as total
|
|
FROM $this->db_mitra.m_user
|
|
JOIN m_company ON M_UserM_CompanyID = M_CompanyID
|
|
AND M_CompanyIsActive = 'Y'
|
|
JOIN s_regional ON M_UserS_RegionalID = S_RegionalID
|
|
AND S_RegionalIsActive = 'Y'
|
|
JOIN m_mou ON M_UserM_MouID = M_MouID
|
|
AND M_MouIsActive = 'Y'
|
|
WHERE M_UserIsActive = 'Y'
|
|
AND (M_UserUsername LIKE ?)";
|
|
|
|
$qry_filter = $this->db_regional->query($sql_filter, [$search]);
|
|
$tot_count = 0;
|
|
$tot_page = 0;
|
|
if ($qry_filter) {
|
|
$tot_count = $qry_filter->result_array()[0]["total"];
|
|
$tot_page = ceil($tot_count / $number_limit);
|
|
} else {
|
|
$this->sys_error_db("user total error", $this->db_regional);
|
|
exit;
|
|
}
|
|
|
|
$sql = "SELECT M_UserID,
|
|
M_CompanyID,
|
|
M_CompanyName,
|
|
S_RegionalID,
|
|
S_RegionalName,
|
|
M_MouID,
|
|
M_MouName,
|
|
M_UserUsername,
|
|
M_UserPassword,
|
|
M_UserLastAccess,
|
|
M_UserIsLoggedIn,
|
|
M_UserM_UserID
|
|
FROM $this->db_mitra.m_user
|
|
JOIN m_company ON M_UserM_CompanyID = M_CompanyID
|
|
AND M_CompanyIsActive = 'Y'
|
|
JOIN s_regional ON M_UserS_RegionalID = S_RegionalID
|
|
AND S_RegionalIsActive = 'Y'
|
|
JOIN m_mou ON M_UserM_MouID = M_MouID
|
|
AND M_MouIsActive = 'Y'
|
|
WHERE M_UserIsActive = 'Y' AND (M_UserUsername LIKE ?)
|
|
LIMIT ? OFFSET ?";
|
|
|
|
$qry = $this->db_regional->query($sql, [$search, $number_limit, $number_offset]);
|
|
if ($qry) {
|
|
$rows = $qry->result_array();
|
|
} else {
|
|
$this->sys_error_db("select user error", $this->db_regional);
|
|
exit;
|
|
}
|
|
|
|
$result = array(
|
|
"total_page" => $tot_page,
|
|
"total_filter" => $tot_count,
|
|
"records" => $rows
|
|
);
|
|
$this->sys_ok($result);
|
|
} catch (Exception $exc) {
|
|
$message = $exc->getMessage();
|
|
$this->sys_error($message);
|
|
}
|
|
}
|
|
|
|
function search_company()
|
|
{
|
|
try {
|
|
if (!$this->isLogin) {
|
|
$this->sys_error("Invalid Token");
|
|
exit;
|
|
}
|
|
$prm = $this->sys_input;
|
|
|
|
$search = "";
|
|
$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_company
|
|
WHERE M_CompanyIsActive = 'Y'
|
|
AND (M_CompanyName LIKE ?)
|
|
LIMIT ?";
|
|
$qry_filter = $this->db_regional->query($sql_filter, [$search, $number_limit]);
|
|
if ($qry_filter) {
|
|
$tot_count = $qry_filter->result_array()[0]["total"];
|
|
} else {
|
|
$this->sys_error_db("company count");
|
|
exit;
|
|
}
|
|
|
|
$sql_search = "SELECT M_CompanyID,
|
|
M_CompanyName,
|
|
M_CompanyNumber
|
|
FROM m_company
|
|
WHERE M_CompanyIsActive = 'Y'
|
|
AND (M_CompanyName LIKE ?)
|
|
LIMIT ?";
|
|
$qry_search = $this->db_regional->query($sql_search, [$search, $number_limit]);
|
|
if ($qry_search) {
|
|
$rows = $qry_search->result_array();
|
|
} else {
|
|
$this->db_regional->trans_rollback();
|
|
$this->sys_error_db("company select error", $this->db_regional);
|
|
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 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_regional->query($sql);
|
|
if ($qry) {
|
|
$rows = $qry->result_array();
|
|
} else {
|
|
$this->sys_error_db("regional select error", $this->db_regional);
|
|
exit;
|
|
}
|
|
|
|
$result = array(
|
|
"records" => $rows,
|
|
"sql" => $this->db_regional->last_query()
|
|
);
|
|
$this->sys_ok($result);
|
|
} catch (Exception $exc) {
|
|
$message = $exc->getMessage();
|
|
$this->sys_error($message);
|
|
}
|
|
}
|
|
|
|
function search_mou()
|
|
{
|
|
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
|
|
JOIN m_company ON M_MouM_CompanyID = M_CompanyID
|
|
AND M_CompanyIsActive = 'Y'
|
|
AND M_MouM_CompanyID = ?
|
|
WHERE M_MouIsActive = 'Y'
|
|
AND M_MouIsReleased = 'Y'
|
|
AND M_MouEndDate >= NOW()
|
|
AND (M_MouName LIKE ?)
|
|
LIMIT ?";
|
|
$qry_filter = $this->db_regional->query($sql_filter, [$companyId, $search, $number_limit]);
|
|
if ($qry_filter) {
|
|
$tot_count = $qry_filter->result_array()[0]["total"];
|
|
} else {
|
|
$this->sys_error_db("mou count");
|
|
exit;
|
|
}
|
|
|
|
$sql_search = "SELECT M_CompanyID,
|
|
M_MouID,
|
|
M_MouName,
|
|
M_MouNumber
|
|
FROM m_mou
|
|
JOIN m_company ON M_MouM_CompanyID = M_CompanyID
|
|
AND M_CompanyIsActive = 'Y'
|
|
AND M_MouM_CompanyID = ?
|
|
WHERE M_MouIsActive = 'Y'
|
|
AND M_MouIsReleased = 'Y'
|
|
AND M_MouEndDate >= NOW()
|
|
AND (M_MouName LIKE ?)
|
|
LIMIT ?";
|
|
$qry_search = $this->db_regional->query($sql_search, [$companyId, $search, $number_limit]);
|
|
if ($qry_search) {
|
|
$rows = $qry_search->result_array();
|
|
} else {
|
|
$this->db_regional->trans_rollback();
|
|
$this->sys_error_db("mou select error", $this->db_regional);
|
|
exit;
|
|
}
|
|
|
|
$result = array(
|
|
"total" => $tot_count,
|
|
"total_display" => sizeof($rows),
|
|
"records" => $rows
|
|
);
|
|
$this->sys_ok($result);
|
|
} catch (Exception $exc) {
|
|
$message = $exc->getMessage();
|
|
$this->sys_error($message);
|
|
}
|
|
}
|
|
|
|
function add()
|
|
{
|
|
try {
|
|
if (!$this->isLogin) {
|
|
$this->sys_error("Invalid Token");
|
|
exit;
|
|
}
|
|
$this->db_regional->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"]);
|
|
}
|
|
|
|
if ($password !== $confirm_password) {
|
|
$error = "password dan konfirmasi password harus sama";
|
|
$this->sys_error_db($error);
|
|
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_regional->query($sql_cek_username, [$username]);
|
|
if ($qry_cek_username) {
|
|
$get_count_username = $qry_cek_username->row_array();
|
|
} else {
|
|
$this->db_regional->trans_rollback();
|
|
$this->sys_error_db("ERROR, cek user", $this->db_regional);
|
|
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_regional->query($sql_insert, [
|
|
$companyId,
|
|
$regionalId,
|
|
$mouId,
|
|
$username,
|
|
$sm_password,
|
|
$userid
|
|
]);
|
|
if (!$qry_insert) {
|
|
$this->db_regional->trans_rollback();
|
|
$this->sys_error_db("user insert error", $this->db_regional);
|
|
exit;
|
|
}
|
|
|
|
$insert_id = $this->db_regional->insert_id();
|
|
|
|
$sql_json_before = "SELECT *
|
|
FROM $this->db_mitra.m_user
|
|
WHERE M_UserIsActive = 'Y'
|
|
AND M_UserID = ?";
|
|
$qry_json_before = $this->db_regional->query($sql_json_before, [$insert_id]);
|
|
if (!$qry_json_before) {
|
|
$this->db_regional->trans_rollback();
|
|
$this->sys_error_db("m_user select json error", $this->db_regional);
|
|
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_regional->query($sql_insert_log, [
|
|
$insert_id,
|
|
$json_after_log,
|
|
$userid
|
|
]);
|
|
if (!$qry_insert_log) {
|
|
$this->db_regional->trans_rollback();
|
|
$this->sys_error_db("m_user_log insert error", $this->db_regional);
|
|
exit;
|
|
}
|
|
} else {
|
|
$this->db_regional->trans_rollback();
|
|
$this->sys_error_db("Username sudah digunakan. Silahkan masukkan username yang lain", $this->db_regional);
|
|
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);
|
|
}
|
|
}
|
|
|
|
function edit()
|
|
{
|
|
try {
|
|
if (!$this->isLogin) {
|
|
$this->sys_error("Invalid Token");
|
|
exit;
|
|
}
|
|
$this->db_regional->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"]);
|
|
}
|
|
|
|
// 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}' AND (
|
|
M_UserM_MouID != '{$mouId}'
|
|
)";
|
|
$qry_cek_username = $this->db_regional->query($sql_cek_username);
|
|
if ($qry_cek_username) {
|
|
$get_rows_username = $qry_cek_username->row_array();
|
|
} else {
|
|
$this->db_regional->trans_rollback();
|
|
$this->sys_error_db("ERROR, cek user", $this->db_regional);
|
|
exit;
|
|
}
|
|
|
|
if ($get_rows_username["total_user"] == 0) {
|
|
// json before
|
|
$sql_json_before = "SELECT *
|
|
FROM $this->db_mitra.m_user
|
|
WHERE M_UserIsActive = 'Y'
|
|
AND M_UserID = ?";
|
|
|
|
$qry_json_before = $this->db_regional->query($sql_json_before, [
|
|
$Id
|
|
]);
|
|
|
|
if (!$qry_json_before) {
|
|
$this->db_regional->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_UserM_MouID = ?,
|
|
M_UserUsername = ?,
|
|
M_UserLastUpdated = NOW(),
|
|
M_UserM_UserID = ?
|
|
WHERE M_UserID = ?";
|
|
$qry_insert = $this->db_regional->query($sql_insert, [
|
|
$companyId,
|
|
$regionalId,
|
|
$mouId,
|
|
$username,
|
|
$userid,
|
|
$Id
|
|
]);
|
|
if (!$qry_insert) {
|
|
$this->db_regional->trans_rollback();
|
|
$this->sys_error_db("update user error", $this->db_regional);
|
|
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_regional->query($sql_json_after, [$Id]);
|
|
if (!$qry_json_after) {
|
|
$this->db_regional->trans_rollback();
|
|
$this->sys_error_db("m_user select json error", $this->db_regional);
|
|
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_regional->query($sql_insert_log, [
|
|
$Id,
|
|
$json_before_log,
|
|
$json_after_log,
|
|
$userid
|
|
]);
|
|
if (!$qry_insert_log) {
|
|
$this->db_regional->trans_rollback();
|
|
$this->sys_error_db("m_user_log insert error", $this->db_regional);
|
|
exit;
|
|
}
|
|
} else {
|
|
$this->db_regional->trans_rollback();
|
|
$this->sys_error_db("Username sudah digunakan. Silahkan masukkan username yang lain", $this->db_regional);
|
|
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);
|
|
}
|
|
}
|
|
|
|
function deleterow()
|
|
{
|
|
try {
|
|
if (!$this->isLogin) {
|
|
$this->sys_error("Invalid Token");
|
|
exit;
|
|
}
|
|
$this->db_regional->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_regional->query($sql, [$userid, $Id]);
|
|
if (!$qry) {
|
|
$this->db_regional->trans_rollback();
|
|
$this->sys_error_db("m_user delete error", $this->db_regional);
|
|
exit;
|
|
}
|
|
|
|
$sql_json_before = "SELECT *
|
|
FROM $this->db_mitra.m_user
|
|
WHERE M_UserIsActive = 'N'
|
|
AND M_UserID = ?";
|
|
|
|
$qry_json_before = $this->db_regional->query($sql_json_before, [$Id]);
|
|
|
|
if (!$qry_json_before) {
|
|
$this->db_regional->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_regional->query($sql_insert_log, [
|
|
$Id,
|
|
$json_after_log,
|
|
$userid
|
|
]);
|
|
if (!$qry_insert_log) {
|
|
$this->db_regional->trans_rollback();
|
|
$this->sys_error_db("m_user_log insert error", $this->db_regional);
|
|
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);
|
|
}
|
|
}
|
|
}
|