143 lines
3.8 KiB
JavaScript
143 lines
3.8 KiB
JavaScript
const pool = require("../config");
|
|
|
|
const getAllUsersDb = async (param) => {
|
|
// limit & offset masuk fixed param
|
|
let fixedParams = [param.fixed.limit, param.fixed.offset, param.fixed.tenantID];
|
|
|
|
const { whereOrConditions, whereParam } = pool.buildStringOrIlike(
|
|
param.filterCriteria.column,
|
|
param.filterCriteria.criteria,
|
|
fixedParams
|
|
);
|
|
const { whereConditions, queryParams } = pool.buildFilterQuery(param.filterQuery, whereParam);npm
|
|
|
|
const query = `
|
|
SELECT mut.*, mr.role_name, COUNT(*) OVER() AS total
|
|
FROM m_users mut
|
|
LEFT JOIN system.role_tenant mr ON mr.role_id = mut.role_id
|
|
WHERE mut.deleted_at IS NULL AND mut.is_sa != 1 AND mut.tenant_id = $3
|
|
${whereConditions.length > 0 ? ` AND ${whereConditions.join(" AND ")}` : ""}
|
|
${whereOrConditions ? whereOrConditions : ""}
|
|
ORDER BY mut.user_id
|
|
OFFSET $2 ROWS FETCH NEXT $1 ROWS ONLY
|
|
`;
|
|
|
|
const result = await pool.query(query, queryParams);
|
|
const rows = result.recordset;
|
|
|
|
const total = rows.length > 0 ? parseInt(rows[0].total, 10) : 0;
|
|
return { data: rows, total };
|
|
};
|
|
|
|
const createUserDb = async (param) => {
|
|
const insertData = {
|
|
tenant_id: param.tenantID,
|
|
user_fullname: param.userFullname,
|
|
user_name: param.userName,
|
|
user_email: param.userEmail ?? null,
|
|
user_password: param.userPassword,
|
|
role_id: param.roleId ?? null,
|
|
is_active: param.isActive ? 1 : 0,
|
|
created_by: param.userID,
|
|
updated_by: param.userID,
|
|
};
|
|
|
|
const { query, values } = pool.buildDynamicInsert("m_users", insertData);
|
|
|
|
const result = await pool.query(query, values);
|
|
return result.recordset[0];
|
|
};
|
|
|
|
const getUserByIdDb = async (id) => {
|
|
const query = `
|
|
SELECT mut.*
|
|
FROM m_users mut
|
|
WHERE mut.user_id = $1
|
|
`;
|
|
const result = await pool.query(query, [id]);
|
|
return result.recordset[0];
|
|
};
|
|
|
|
const getUserByUsernameDb = async (username) => {
|
|
const query = `
|
|
SELECT mut.*
|
|
FROM m_users mut
|
|
WHERE LOWER(mut.username) = LOWER($1)
|
|
`;
|
|
const result = await pool.query(query, [username]);
|
|
return result.recordset[0];
|
|
};
|
|
|
|
const getUserByUserEmailDb = async (userEmail) => {
|
|
const query = `
|
|
SELECT mut.*
|
|
FROM m_users mut
|
|
WHERE LOWER(mut.user_email) = LOWER($1)
|
|
`;
|
|
const result = await pool.query(query, [userEmail]);
|
|
return result.recordset[0];
|
|
};
|
|
|
|
const updateUserDb = async (param) => {
|
|
const updateData = {
|
|
tenant_id: param.tenantID,
|
|
user_fullname: param.userFullname,
|
|
user_name: param.userName,
|
|
user_email: param.userEmail ?? null,
|
|
user_password: param.userPassword,
|
|
role_id: param.roleId ?? null,
|
|
is_active: param.isActive ? 1 : 0,
|
|
updated_by: param.userID,
|
|
};
|
|
|
|
const whereData = { user_id: param.id };
|
|
|
|
const { query, values } = pool.buildDynamicUpdate("m_users", updateData, whereData);
|
|
|
|
const result = await pool.query(query, values);
|
|
return result.recordset[0];
|
|
};
|
|
|
|
const deleteUserDb = async (id, userID) => {
|
|
const query = `
|
|
UPDATE m_users
|
|
SET deleted_at = GETDATE(), deleted_by = $1
|
|
WHERE user_id = $2;
|
|
|
|
SELECT * FROM m_users WHERE user_id = $2
|
|
`;
|
|
const result = await pool.query(query, [userID, id]);
|
|
return result.recordset[0];
|
|
};
|
|
|
|
const changeUserPasswordDb = async (hashedPassword, userEmail, tenantId) => {
|
|
const query = `
|
|
UPDATE m_users
|
|
SET user_password = $1
|
|
WHERE user_email = $2 AND tenant_id = $3
|
|
`;
|
|
return pool.query(query, [hashedPassword, userEmail, tenantId]);
|
|
};
|
|
|
|
const getAllRoleDb = async (tenantId) => {
|
|
const query = `
|
|
SELECT *
|
|
FROM system.role_tenant
|
|
WHERE deleted_at IS NULL AND tenant_id = $1
|
|
`;
|
|
const result = await pool.query(query, [tenantId]);
|
|
return result.recordset;
|
|
};
|
|
|
|
module.exports = {
|
|
getAllUsersDb,
|
|
getUserByIdDb,
|
|
getUserByUserEmailDb,
|
|
updateUserDb,
|
|
createUserDb,
|
|
deleteUserDb,
|
|
getUserByUsernameDb,
|
|
changeUserPasswordDb,
|
|
getAllRoleDb,
|
|
};
|