File: //home/evaluation-leave/models/leave.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 = {
findAllLeaveTypes: async () => {
return new Promise((resolve, reject) => {
pool.getConnection((err, connection) => {
if (err) {
return reject(err);
}
connection.query("SELECT * FROM leave_type", (error, result) => {
connection.release();
if (error) {
return reject(error);
}
resolve(result);
});
});
});
},
findAllLeaves: async () => {
return new Promise((resolve, reject) => {
pool.getConnection((err, connection) => {
if (err) {
return reject(err);
}
connection.query("SELECT * FROM leaves", (error, result) => {
connection.release();
if (error) {
return reject(error);
}
resolve(result);
});
});
});
},
createLeave: async (leave) => {
return new Promise((resolve, reject) => {
pool.getConnection((err, connection) => {
if (err) {
return reject(err);
}
connection.query("INSERT INTO leaves SET ?", leave, (error, result) => {
connection.release();
if (error) {
return reject(error);
}
resolve(result);
});
});
});
},
findLeaveById: async (id) => {
return new Promise((resolve, reject) => {
pool.getConnection((err, connection) => {
if (err) {
return reject(err);
}
connection.query(
"SELECT * FROM leaves where id=?",
id,
(error, result) => {
connection.release();
if (error) {
return reject(error);
}
resolve(result[0]);
}
);
});
});
},
findMyLeaves: async (id) => {
return new Promise((resolve, reject) => {
pool.getConnection((err, connection) => {
if (err) {
return reject(err);
}
connection.query(
"SELECT * FROM leaves where user_id=?",
id,
(error, result) => {
connection.release();
if (error) {
return reject(error);
}
resolve(result);
}
);
});
});
},
findOpenLeaves: async () => {
return new Promise((resolve, reject) => {
pool.getConnection((err, connection) => {
if (err) {
return reject(err);
}
connection.query(
"SELECT * FROM VW_DVC_APPROVAL_LIST where status='Open' or status='Accepted' and sup_att='N' and dvc_att='N' and vc_att='N'",
(error, result) => {
connection.release();
if (error) {
return reject(error);
}
resolve(result);
}
);
});
});
},
findApprovedLeaves: async () => {
return new Promise((resolve, reject) => {
pool.getConnection((err, connection) => {
if (err) {
return reject(err);
}
connection.query(
"SELECT * FROM VW_DVC_APPROVAL_LIST where status='Approved'",
(error, result) => {
connection.release();
if (error) {
return reject(error);
}
resolve(result);
}
);
});
});
},
findRejectedLeaves: async () => {
return new Promise((resolve, reject) => {
pool.getConnection((err, connection) => {
if (err) {
return reject(err);
}
connection.query(
"SELECT * FROM VW_DVC_APPROVAL_LIST where status='Rejected'",
(error, result) => {
connection.release();
if (error) {
return reject(error);
}
resolve(result);
}
);
});
});
},
findDeclinedLeaves: async () => {
return new Promise((resolve, reject) => {
pool.getConnection((err, connection) => {
if (err) {
return reject(err);
}
connection.query(
"SELECT * FROM VW_DVC_APPROVAL_LIST where status='Declined'",
(error, result) => {
connection.release();
if (error) {
return reject(error);
}
resolve(result);
}
);
});
});
},
findHodLeavesApproval: async (id) => {
return new Promise((resolve, reject) => {
pool.getConnection((err, connection) => {
if (err) {
return reject(err);
}
connection.query(
`SELECT * FROM VW_DVC_APPROVAL_LIST where status='Open' and sup_att='N' and dvc_att='N' and vc_att='N' and sup_id=?`,
id,
(error, result) => {
connection.release();
if (error) {
return reject(error);
}
resolve(result);
}
);
});
});
},
findDVCLeavesApproval: async (id) => {
return new Promise((resolve, reject) => {
pool.getConnection((err, connection) => {
if (err) {
return reject(err);
}
connection.query(
`SELECT * FROM VW_DVC_APPROVAL_LIST
WHERE
(status = 'Open' AND dvc_att = 'N' AND vc_att = 'N' AND sup_id = ?)
OR
(status = 'Accepted' AND dvc_att = 'N' AND vc_att = 'N' AND dvc_id = ?);
`,
[id, id],
(error, result) => {
connection.release();
if (error) {
return reject(error);
}
resolve(result);
}
);
});
});
},
findHodLeavesApproved: async (id) => {
return new Promise((resolve, reject) => {
pool.getConnection((err, connection) => {
if (err) {
return reject(err);
}
connection.query(
"SELECT * FROM VW_DVC_APPROVAL_LIST where status='Approved' and sup_id=?",
id,
(error, result) => {
connection.release();
if (error) {
return reject(error);
}
resolve(result);
}
);
});
});
},
findHodLeavesAccepted: async (id) => {
return new Promise((resolve, reject) => {
pool.getConnection((err, connection) => {
if (err) {
return reject(err);
}
connection.query(
"SELECT * FROM VW_DVC_APPROVAL_LIST where status='Accepted' and sup_id=?",
id,
(error, result) => {
connection.release();
if (error) {
return reject(error);
}
resolve(result);
}
);
});
});
},
findHodLeavesDeclined: async (id) => {
return new Promise((resolve, reject) => {
pool.getConnection((err, connection) => {
if (err) {
return reject(err);
}
connection.query(
"SELECT * FROM VW_DVC_APPROVAL_LIST where status='Declined' and sup_id=?",
id,
(error, result) => {
connection.release();
if (error) {
return reject(error);
}
resolve(result);
}
);
});
});
},
findHodLeavesRejected: async (id) => {
return new Promise((resolve, reject) => {
pool.getConnection((err, connection) => {
if (err) {
return reject(err);
}
connection.query(
"SELECT * FROM VW_DVC_APPROVAL_LIST where status='Rejected' and sup_id=?",
id,
(error, result) => {
connection.release();
if (error) {
return reject(error);
}
resolve(result);
}
);
});
});
},
findDVCLeavesApproved: async () => {
return new Promise((resolve, reject) => {
pool.getConnection((err, connection) => {
if (err) {
return reject(err);
}
connection.query(
"SELECT * FROM VW_DVC_APPROVAL_LIST where status='Approved'",
(error, result) => {
connection.release();
if (error) {
return reject(error);
}
resolve(result);
}
);
});
});
},
findDVCLeavesRejected: async () => {
return new Promise((resolve, reject) => {
pool.getConnection((err, connection) => {
if (err) {
return reject(err);
}
connection.query(
"SELECT * FROM VW_DVC_APPROVAL_LIST where status='Rejected'",
(error, result) => {
connection.release();
if (error) {
return reject(error);
}
resolve(result);
}
);
});
});
},
findMyOpenLeave: async (id) => {
return new Promise((resolve, reject) => {
pool.getConnection((err, connection) => {
if (err) {
return reject(err);
}
connection.query(
"SELECT * FROM leaves where status='Open' and user_id= ?",
id,
(error, result) => {
connection.release();
if (error) {
return reject(error);
}
resolve(result[0]);
}
);
});
});
},
findApprovedLeaves: async () => {
return new Promise((resolve, reject) => {
pool.getConnection((err, connection) => {
if (err) {
return reject(err);
}
connection.query(
"SELECT * FROM VW_DVC_APPROVAL_LIST where status='Approved'",
(error, result) => {
connection.release();
if (error) {
return reject(error);
}
resolve(result);
}
);
});
});
},
rejectLeave: async (leave) => {
console.log(leave);
return new Promise((resolve, reject) => {
pool.getConnection((err, connection) => {
if (err) {
return reject(err);
}
const query =
"UPDATE leaves SET status = 'Rejected', rejected_yn = 'Y', dvc_att='Y', dvc_comment=?, updatedAt = ?, rejectedBy =? WHERE id = ? and dvc_id=? OR sup_id=?";
const values = [
leave.description,
leave.updated_at,
leave.updated_by,
leave.id,
leave.user_id,
leave.user_id,
];
connection.query(query, values, (error, result) => {
connection.release();
if (error) {
return reject(error);
}
resolve(result);
});
});
});
},
approveLeave: async (leave) => {
return new Promise((resolve, reject) => {
pool.getConnection((err, connection) => {
if (err) {
return reject(err);
}
const query =
`UPDATE leaves
SET
status = 'Approved',
approved_yn = 'Y',
sup_att = 'Y',
dvc_att = 'Y',
dvc_comment = ?,
updatedAt = ?,
approvedBy = ?
WHERE
(id = ? AND dvc_id = ?)
OR
(id = ? AND sup_id = ?);
`;
const values = [
leave.description,
leave.updated_at,
leave.updated_by,
leave.id,
leave.user_id,
leave.id,
leave.user_id,
];
connection.query(query, values, (error, result) => {
connection.release();
if (error) {
return reject(error);
}
resolve(result);
});
});
});
},
acceptLeaveRequest: async (leave) => {
return new Promise((resolve, reject) => {
pool.getConnection((err, connection) => {
if (err) {
return reject(err);
}
const query =
"UPDATE leaves SET status = 'Accepted', sup_att= 'Y', sup_comment=?, updatedAt = ?, approvedBy =?, dvc_id=? WHERE id = ? and sup_id = ? and status <> 'Accepted'";
const values = [
leave.description,
leave.updated_at,
leave.updated_by,
leave.dvc_id,
leave.id,
leave.user_id,
];
connection.query(query, values, (error, result) => {
connection.release();
if (error) {
return reject(error);
}
resolve(result);
});
});
});
},
declineLeaveRequest: async (leave) => {
console.log(leave);
return new Promise((resolve, reject) => {
pool.getConnection((err, connection) => {
if (err) {
return reject(err);
}
const query =
"UPDATE leaves SET status = 'Declined', sup_att= 'Y', sup_comment=?, updatedAt = ?, rejectedBy =? WHERE id = ? and sup_id = ? and status <> 'Declined'";
const values = [
leave.description,
leave.updated_at,
leave.updated_by,
leave.id,
leave.user_id,
];
connection.query(query, values, (error, result) => {
connection.release();
if (error) {
return reject(error);
}
resolve(result);
});
});
});
},
/**
* Graphical Response
* */
// Report of GENERAL FUCULTY PERFORMANCE
// Monthly
findMonthlyLeaveTypeGraph: async () => {
return new Promise((resolve, reject) => {
pool.getConnection((err, connection) => {
if (err) {
return reject(err);
}
connection.query(
"SELECT DISTINCT(LV.leave_type_id), LT.name, COUNT(LV.id) as value from leaves LV INNER JOIN leave_type LT on LV.leave_type_id = LT.id WHERE LV.createdAt <= LAST_DAY(curdate()) AND LV.createdAt >= date_add(date_add(LAST_DAY(curdate()),interval 1 DAY),interval -1 MONTH) GROUP BY LV.leave_type_id ORDER BY LV.createdAt DESC;",
(error, result) => {
connection.release();
if (error) {
return reject(error);
}
resolve(result);
}
);
});
});
},
// Weekly
findWeeklyLeaveTypeGraph: async () => {
return new Promise((resolve, reject) => {
pool.getConnection((err, connection) => {
if (err) {
return reject(err);
}
connection.query(
"SELECT DISTINCT(LV.leave_type_id), LT.name, COUNT(LV.id) as value from leaves LV INNER JOIN leave_type LT on LV.leave_type_id = LT.id WHERE LV.createdAt <= adddate(curdate(), INTERVAL 7-DAYOFWEEK(curdate()) DAY) AND LV.createdAt >= adddate(curdate(), INTERVAL 1-DAYOFWEEK(curdate()) DAY) GROUP BY LV.leave_type_id ORDER BY LV.createdAt DESC",
(error, result) => {
connection.release();
if (error) {
return reject(error);
}
resolve(result);
}
);
});
});
},
// Department or SchoolWise
// Weekly
findWeeklyLeaveDepartmentGraph: async () => {
return new Promise((resolve, reject) => {
pool.getConnection((err, connection) => {
if (err) {
return reject(err);
}
connection.query(
"SELECT DISTINCT(D.name)as name, COUNT(L.id) as value FROM leaves L INNER JOIN users U on L.user_id = U.id INNER JOIN department D on U.departmentId = D.id WHERE L.createdAt <= adddate(curdate(), INTERVAL 7-DAYOFWEEK(curdate()) DAY) AND L.createdAt >= adddate(curdate(), INTERVAL 1-DAYOFWEEK(curdate()) DAY) GROUP BY D.name ORDER BY L.createdAt DESC",
(error, result) => {
connection.release();
if (error) {
return reject(error);
}
resolve(result);
}
);
});
});
},
// Monthly
findMothlyLeaveDepartmentGraph: async () => {
return new Promise((resolve, reject) => {
pool.getConnection((err, connection) => {
if (err) {
return reject(err);
}
connection.query(
"SELECT DISTINCT(D.name)as name, COUNT(L.id) as value FROM leaves L INNER JOIN users U on L.user_id = U.id INNER JOIN department D on U.departmentId = D.id WHERE L.createdAt <= LAST_DAY(curdate()) AND L.createdAt >= date_add(date_add(LAST_DAY(curdate()),interval 1 DAY),interval -1 MONTH) GROUP BY D.name ORDER BY L.createdAt DESC;",
(error, result) => {
connection.release();
if (error) {
return reject(error);
}
resolve(result);
}
);
});
});
},
// The Monthly (JAN) reports
findJanuaryGraph: async () => {
return new Promise((resolve, reject) => {
pool.getConnection((err, connection) => {
if (err) {
return reject(err);
}
connection.query(
"SELECT DISTINCT(D.name) AS name, COUNT(L.id) AS value FROM leaves L INNER JOIN users U ON L.user_id = U.id INNER JOIN department D ON U.departmentId = D.id WHERE L.createdAt >= CONCAT(YEAR(curdate()), '-01-01 00:00:00') AND L.createdAt < CONCAT(YEAR(curdate()), '-02-01 00:00:00') GROUP BY D.name ORDER BY MAX(L.createdAt) DESC;",
(error, result) => {
connection.release();
if (error) {
return reject(error);
}
resolve(result);
}
);
});
});
},
// The Monthly (FEB) reports
findFebruaryGraph: async () => {
return new Promise((resolve, reject) => {
pool.getConnection((err, connection) => {
if (err) {
return reject(err);
}
connection.query(
"SELECT DISTINCT(D.name) AS name, COUNT(L.id) AS value FROM leaves L INNER JOIN users U ON L.user_id = U.id INNER JOIN department D ON U.departmentId = D.id WHERE L.createdAt >= CONCAT(YEAR(curdate()), '-02-01 00:00:00') AND L.createdAt < CONCAT(YEAR(curdate()), '-03-01 00:00:00') GROUP BY D.name ORDER BY MAX(L.createdAt) DESC;",
(error, result) => {
connection.release();
if (error) {
return reject(error);
}
resolve(result);
}
);
});
});
},
// The Monthly (MAR) reports
findMarchGraph: async () => {
return new Promise((resolve, reject) => {
pool.getConnection((err, connection) => {
if (err) {
return reject(err);
}
connection.query(
"SELECT DISTINCT(D.name) AS name, COUNT(L.id) AS value FROM leaves L INNER JOIN users U ON L.user_id = U.id INNER JOIN department D ON U.departmentId = D.id WHERE L.createdAt >= CONCAT(YEAR(curdate()), '-03-01 00:00:00') AND L.createdAt < CONCAT(YEAR(curdate()), '-04-01 00:00:00') GROUP BY D.name ORDER BY MAX(L.createdAt) DESC;",
(error, result) => {
connection.release();
if (error) {
return reject(error);
}
resolve(result);
}
);
});
});
},
// The Monthly (APRIL) reports
findAprilGraph: async () => {
return new Promise((resolve, reject) => {
pool.getConnection((err, connection) => {
if (err) {
return reject(err);
}
connection.query(
"SELECT DISTINCT(D.name) AS name, COUNT(L.id) AS value FROM leaves L INNER JOIN users U ON L.user_id = U.id INNER JOIN department D ON U.departmentId = D.id WHERE L.createdAt >= CONCAT(YEAR(curdate()), '-04-01 00:00:00') AND L.createdAt < CONCAT(YEAR(curdate()), '-05-01 00:00:00') GROUP BY D.name ORDER BY MAX(L.createdAt) DESC;",
(error, result) => {
connection.release();
if (error) {
return reject(error);
}
resolve(result);
}
);
});
});
},
// The Monthly (MAY) reports
findMayGraph: async () => {
return new Promise((resolve, reject) => {
pool.getConnection((err, connection) => {
if (err) {
return reject(err);
}
connection.query(
"SELECT DISTINCT(D.name) AS name, COUNT(L.id) AS value FROM leaves L INNER JOIN users U ON L.user_id = U.id INNER JOIN department D ON U.departmentId = D.id WHERE L.createdAt >= CONCAT(YEAR(curdate()), '-05-01 00:00:00') AND L.createdAt < CONCAT(YEAR(curdate()), '-06-01 00:00:00') GROUP BY D.name ORDER BY MAX(L.createdAt) DESC;",
(error, result) => {
connection.release();
if (error) {
return reject(error);
}
resolve(result);
}
);
});
});
},
// The Monthly (JUNE) reports
findJuneGraph: async () => {
return new Promise((resolve, reject) => {
pool.getConnection((err, connection) => {
if (err) {
return reject(err);
}
connection.query(
"SELECT DISTINCT(D.name) AS name, COUNT(L.id) AS value FROM leaves L INNER JOIN users U ON L.user_id = U.id INNER JOIN department D ON U.departmentId = D.id WHERE L.createdAt >= CONCAT(YEAR(curdate()), '-06-01 00:00:00') AND L.createdAt < CONCAT(YEAR(curdate()), '-07-01 00:00:00') GROUP BY D.name ORDER BY MAX(L.createdAt) DESC;",
(error, result) => {
connection.release();
if (error) {
return reject(error);
}
resolve(result);
}
);
});
});
},
// The Monthly (JULY) reports
findJulyGraph: async () => {
return new Promise((resolve, reject) => {
pool.getConnection((err, connection) => {
if (err) {
return reject(err);
}
connection.query(
"SELECT DISTINCT(D.name) AS name, COUNT(L.id) AS value FROM leaves L INNER JOIN users U ON L.user_id = U.id INNER JOIN department D ON U.departmentId = D.id WHERE L.createdAt >= CONCAT(YEAR(curdate()), '-07-01 00:00:00') AND L.createdAt < CONCAT(YEAR(curdate()), '-08-01 00:00:00') GROUP BY D.name ORDER BY MAX(L.createdAt) DESC;",
(error, result) => {
connection.release();
if (error) {
return reject(error);
}
resolve(result);
}
);
});
});
},
// The Monthly (AUG) reports
findAugustGraph: async () => {
return new Promise((resolve, reject) => {
pool.getConnection((err, connection) => {
if (err) {
return reject(err);
}
connection.query(
"SELECT DISTINCT(D.name) AS name, COUNT(L.id) AS value FROM leaves L INNER JOIN users U ON L.user_id = U.id INNER JOIN department D ON U.departmentId = D.id WHERE L.createdAt >= CONCAT(YEAR(curdate()), '-08-01 00:00:00') AND L.createdAt < CONCAT(YEAR(curdate()), '-09-01 00:00:00') GROUP BY D.name ORDER BY MAX(L.createdAt) DESC;",
(error, result) => {
connection.release();
if (error) {
return reject(error);
}
resolve(result);
}
);
});
});
},
// The Monthly (SEP) reports
findSeptemberGraph: async () => {
return new Promise((resolve, reject) => {
pool.getConnection((err, connection) => {
if (err) {
return reject(err);
}
connection.query(
"SELECT DISTINCT(D.name) AS name, COUNT(L.id) AS value FROM leaves L INNER JOIN users U ON L.user_id = U.id INNER JOIN department D ON U.departmentId = D.id WHERE L.createdAt >= CONCAT(YEAR(curdate()), '-09-01 00:00:00') AND L.createdAt < CONCAT(YEAR(curdate()), '-10-01 00:00:00') GROUP BY D.name ORDER BY MAX(L.createdAt) DESC;",
(error, result) => {
connection.release();
if (error) {
return reject(error);
}
resolve(result);
}
);
});
});
},
// The Monthly (OCT) reports
findOctoberGraph: async () => {
return new Promise((resolve, reject) => {
pool.getConnection((err, connection) => {
if (err) {
return reject(err);
}
connection.query(
"SELECT DISTINCT(D.name) AS name, COUNT(L.id) AS value FROM leaves L INNER JOIN users U ON L.user_id = U.id INNER JOIN department D ON U.departmentId = D.id WHERE L.createdAt >= CONCAT(YEAR(curdate()), '-10-01 00:00:00') AND L.createdAt < CONCAT(YEAR(curdate()), '-11-01 00:00:00') GROUP BY D.name ORDER BY MAX(L.createdAt) DESC",
(error, result) => {
connection.release();
if (error) {
return reject(error);
}
resolve(result);
}
);
});
});
},
// The Monthly (NOV) reports
findNovemberGraph: async () => {
return new Promise((resolve, reject) => {
pool.getConnection((err, connection) => {
if (err) {
return reject(err);
}
connection.query(
"SELECT DISTINCT(D.name) AS name, COUNT(L.id) AS value FROM leaves L INNER JOIN users U ON L.user_id = U.id INNER JOIN department D ON U.departmentId = D.id WHERE L.createdAt >= CONCAT(YEAR(curdate()), '-11-01 00:00:00') AND L.createdAt < CONCAT(YEAR(curdate()), '-12-01 00:00:00') GROUP BY D.name ORDER BY MAX(L.createdAt) DESC;",
(error, result) => {
connection.release();
if (error) {
return reject(error);
}
resolve(result);
}
);
});
});
},
// The Monthly (DEC) reports
findDecemberGraph: async () => {
return new Promise((resolve, reject) => {
pool.getConnection((err, connection) => {
if (err) {
return reject(err);
}
connection.query(
"SELECT DISTINCT(D.name) AS name, COUNT(L.id) AS value FROM leaves L INNER JOIN users U ON L.user_id = U.id INNER JOIN department D ON U.departmentId = D.id WHERE L.createdAt >= CONCAT(YEAR(curdate()), '-12-01 00:00:00') GROUP BY D.name ORDER BY MAX(L.createdAt) DESC;",
(error, result) => {
connection.release();
if (error) {
return reject(error);
}
resolve(result);
}
);
});
});
},
/**
*
* @returns Yearly Report
*/
// Yearly Rejected chart report
findRejectedChartGraph: async () => {
return new Promise((resolve, reject) => {
pool.getConnection((err, connection) => {
if (err) {
return reject(err);
}
connection.query(
"SELECT * FROM vw_rejected_leave_chart",
(error, result) => {
connection.release();
if (error) {
return reject(error);
}
resolve(result);
}
);
});
});
},
// APPROVED chart report
findApprovedChartGraph: async () => {
return new Promise((resolve, reject) => {
pool.getConnection((err, connection) => {
if (err) {
return reject(err);
}
connection.query(
"SELECT * FROM vw_approved_leave_chart",
(error, result) => {
connection.release();
if (error) {
return reject(error);
}
resolve(result);
}
);
});
});
},
// APPROVED chart report
findAppliedChartGraph: async () => {
return new Promise((resolve, reject) => {
pool.getConnection((err, connection) => {
if (err) {
return reject(err);
}
connection.query(
"SELECT * FROM vw_applied_leave_chart",
(error, result) => {
connection.release();
if (error) {
return reject(error);
}
resolve(result);
}
);
});
});
},
};