File: //home/evaluation-leave/models/users.js
const config = require("../config/config");
const mysql = require("mysql2"); // Use mysql2 for connection pooling
// Create a connection pool
const pool = mysql.createPool({
host: config.connection.host,
user: config.connection.user,
password: config.connection.password,
database: config.connection.database,
waitForConnections: true,
connectionLimit: 10, // Adjust as needed
queueLimit: 0, // Unlimited queue size
});
module.exports = {
findUserByEmail: async (email) => {
return new Promise((resolve, reject) => {
pool.getConnection((err, connection) => {
if (err) {
return reject(err);
}
connection.query(
"SELECT * FROM users WHERE email = ?",
email,
(error, result) => {
connection.release();
if (error) {
return reject(error);
}
resolve(result[0]);
}
);
});
});
},
findAllActiveUser: async () => {
return new Promise((resolve, reject) => {
pool.getConnection((err, connection) => {
if (err) {
return reject(err);
}
connection.query(
"SELECT * FROM users WHERE active_yn =1",
(error, result) => {
connection.release();
if (error) {
return reject(error);
}
resolve(result);
}
);
});
});
},
findAllUserTypes: async () => {
return new Promise((resolve, reject) => {
pool.getConnection((err, connection) => {
if (err) {
return reject(err);
}
connection.query(
"SELECT * FROM user_type",
(error, result) => {
connection.release();
if (error) {
return reject(error);
}
resolve(result);
}
);
});
});
},
findUsersforHR: async () => {
return new Promise((resolve, reject) => {
pool.getConnection((err, connection) => {
if (err) {
return reject(err);
}
connection.query(
`SELECT U.id as userId, U.fname, U.lname, U.email, D.name as department, UT.name AS userType, r.name as role, U.leave_b_foward, U.leave_earned, U.leave_taken
FROM users U INNER JOIN department D on U.departmentId = D.id INNER JOIN roles r ON U.roleid = r.id INNER JOIN user_type UT ON U.user_type_id = UT.id
WHERE U.active_yn = 1;`,
(error, result) => {
connection.release();
if (error) {
return reject(error);
}
resolve(result);
}
);
});
});
},
findUsersByDepartment: async (data) => {
return new Promise((resolve, reject) => {
pool.getConnection((err, connection) => {
if (err) {
return reject(err);
}
connection.query(
`SELECT U.id as userId, U.fname, U.lname, U.email, E.id as EvaluationId, E.self_rating, E.supervisor_rating, E.status, E.created_at FROM users U
INNER JOIN evaluation E on U.id = E.user_id
WHERE U.departmentId = ? and U.id <> ?`,
[data.departmentId, data.userId],
(error, result) => {
connection.release();
if (error) {
return reject(error);
}
resolve(result);
}
);
});
});
},
findHODuserDepartment: async (data) => {
return new Promise((resolve, reject) => {
pool.getConnection((err, connection) => {
if (err) {
return reject(err);
}
connection.query(
`SELECT U.id as userId, U.fname, U.lname, U.email, E.id as EvaluationId, E.self_rating, E.supervisor_rating, E.status, E.created_at FROM users U
INNER JOIN evaluation E on U.id = E.user_id
WHERE U.roleid =1 and U.id <> ?`,
[data.userId],
(error, result) => {
connection.release();
if (error) {
return reject(error);
}
resolve(result);
}
);
});
});
},
findHODForLeave: async () => {
return new Promise((resolve, reject) => {
pool.getConnection((err, connection) => {
if (err) {
return reject(err);
}
connection.query(
`SELECT id, fname, lname, email FROM users
WHERE roleid =1 and active_yn=1`,
(error, result) => {
connection.release();
if (error) {
return reject(error);
}
resolve(result);
}
);
});
});
},
findHODForLeaveHod: async () => {
return new Promise((resolve, reject) => {
pool.getConnection((err, connection) => {
if (err) {
return reject(err);
}
connection.query(
`SELECT id, fname, lname, email FROM users
WHERE roleid =5 and active_yn=1`,
(error, result) => {
connection.release();
if (error) {
return reject(error);
}
resolve(result);
}
);
});
});
},
findHODForLeaveDvc: async () => {
return new Promise((resolve, reject) => {
pool.getConnection((err, connection) => {
if (err) {
return reject(err);
}
connection.query(
`SELECT id, fname, lname, email FROM users
WHERE roleid =6 and active_yn=1`,
(error, result) => {
connection.release();
if (error) {
return reject(error);
}
resolve(result);
}
);
});
});
},
createUser: async (user) => {
return new Promise((resolve, reject) => {
pool.getConnection((err, connection) => {
if (err) {
return reject(err);
}
connection.query("INSERT INTO users SET ?", user, (error, result) => {
connection.release();
if (error) {
return reject(error);
}
resolve(result);
});
});
});
},
updateUser: async (user) => {
return new Promise((resolve, reject) => {
pool.getConnection((err, connection) => {
if (err) {
return reject(err);
}
const query =
"UPDATE users SET fname = ?, lname = ?, password = ?, roleid=?, facultyId = ?, active_yn = ?, updatedAt = ?, updated_by = ?, WHERE email = ?";
const values = [
user.name,
user.password,
user.mobile,
user.status,
user.address,
user.location_id,
user.updated_at,
user.email,
];
connection.query(query, values, (error, result) => {
connection.release();
if (error) {
return reject(error);
}
resolve(result);
});
});
});
},
randomCodeGeneration: async () => {
const codeLength = 6;
const characters = "2TQ345YCGD6789";
let code = "";
for (let i = 0; i < codeLength; i++) {
const randomIndex = Math.floor(Math.random() * characters.length);
code += characters.charAt(randomIndex);
}
return code;
},
resetUserPassword: async (user) => {
return new Promise((resolve, reject) => {
pool.getConnection((err, connection) => {
if (err) {
return reject(err);
}
const query =
"UPDATE users SET password = ?, updatedAt=? WHERE email = ?";
const values = [
user.password,
user.updated_at,
user.email,
user.reg_code,
];
connection.query(query, values, (error, result) => {
connection.release();
if (error) {
return reject(error);
}
resolve(result);
});
});
});
},
findUserById: async (id) => {
return new Promise((resolve, reject) => {
pool.getConnection((err, connection) => {
if (err) {
return reject(err);
}
connection.query(
"SELECT fname, lname, email, roleid, departmentId, active_yn, current_eval_status FROM users WHERE id =?",
id,
(error, result) => {
connection.release();
if (error) {
return reject(error);
}
resolve(result[0]);
}
);
});
});
},
findUserByfaculty: async (id) => {
return new Promise((resolve, reject) => {
pool.getConnection((err, connection) => {
if (err) {
return reject(err);
}
connection.query(
"SELECT fname, lname, email, roleid, departmentId, active_yn, current_eval_status FROM users WHERE facultyId =?",
id,
(error, result) => {
connection.release();
if (error) {
return reject(error);
}
resolve(result);
}
);
});
});
},
};