File: //home/evaluation-leave/models/evaluation.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 = {
findAllEvaluations: async () => {
return new Promise((resolve, reject) => {
pool.getConnection((err, connection) => {
if (err) {
return reject(err);
}
connection.query("SELECT * FROM evaluation", (error, result) => {
connection.release();
if (error) {
return reject(error);
}
resolve(result);
});
});
});
},
findAllEvaluationsForSession: async (session_id) => {
return new Promise((resolve, reject) => {
pool.getConnection((err, connection) => {
if (err) {
return reject(err);
}
connection.query(
"SELECT * FROM evaluation where session_id=?",
session_id,
(error, result) => {
connection.release();
if (error) {
return reject(error);
}
resolve(result);
}
);
});
});
},
findEvaluationById: async (id) => {
return new Promise((resolve, reject) => {
pool.getConnection((err, connection) => {
if (err) {
return reject(err);
}
connection.query(
"SELECT * FROM evaluation where id = ?",
id,
(error, result) => {
connection.release();
if (error) {
return reject(error);
}
resolve(result[0]);
}
);
});
});
},
findEvaluationByHodId: async (id) => {
return new Promise((resolve, reject) => {
pool.getConnection((err, connection) => {
if (err) {
return reject(err);
}
connection.query(
"SELECT * FROM evaluation where hod_id=?",
id,
(error, result) => {
connection.release();
if (error) {
return reject(error);
}
resolve(result);
}
);
});
});
},
findEvaluationByUserId: async (id) => {
return new Promise((resolve, reject) => {
pool.getConnection((err, connection) => {
if (err) {
return reject(err);
}
connection.query(
"SELECT * FROM evaluation where user_id=?",
id,
(error, result) => {
connection.release();
if (error) {
return reject(error);
}
resolve(result);
}
);
});
});
},
findPersonalEvaluation: async (id) => {
return new Promise((resolve, reject) => {
pool.getConnection((err, connection) => {
if (err) {
return reject(err);
}
connection.query(
"SELECT * FROM evaluation where user_id=?",
id,
(error, result) => {
connection.release();
if (error) {
return reject(error);
}
resolve(result);
}
);
});
});
},
createEvaluation: async (evaluation) => {
return new Promise((resolve, reject) => {
pool.getConnection((err, connection) => {
if (err) {
return reject(err);
}
connection.query(
"INSERT INTO evaluation SET ?",
evaluation,
(error, result) => {
connection.release();
if (error) {
return reject(error);
}
resolve(result);
}
);
});
});
},
updateEvaluation: async (evaluationUpdate) => {
return new Promise((resolve, reject) => {
pool.getConnection((err, connection) => {
if (err) {
return reject(err);
}
// console.log(evaluationUpdate);
const query = `
UPDATE evaluation SET
status = 'Evaluated',
supervisor_rating= ?,
supervisor_comment= ?,
supervisor_recommendation= ?,
updated_at= ?,
hod_id= ?,
updated_by= ?
WHERE id = ?
`;
const values = [
evaluationUpdate.supervisor_rating,
evaluationUpdate.supervisor_comment,
evaluationUpdate.supervisor_recommendation,
evaluationUpdate.updated_at,
evaluationUpdate.hod_id,
evaluationUpdate.updated_by,
evaluationUpdate.id,
];
connection.query(query, values, (error, result) => {
connection.release();
if (error) {
return reject(error);
}
resolve(result);
});
});
});
},
dvcRecommendEvaluation: async (dvcRecommendation) => {
return new Promise((resolve, reject) => {
pool.getConnection((err, connection) => {
if (err) {
return reject(err);
}
const query = `
UPDATE evaluation SET
status = 'Confirmed',
dvc_recommendation= ?,
updated_at= ?,
updated_by= ?
WHERE id = ?
`;
const values = [
dvcRecommendation.dvc_recommendation,
dvcRecommendation.updated_at,
dvcRecommendation.updated_by,
dvcRecommendation.id,
];
connection.query(query, values, (error, result) => {
connection.release();
if (error) {
return reject(error);
}
resolve(result);
});
});
});
},
vcRecommendEvaluation: async (vcRecommendation) => {
return new Promise((resolve, reject) => {
pool.getConnection((err, connection) => {
if (err) {
return reject(err);
}
const query = `
UPDATE evaluation SET
vc_recommendation= ?,
updated_at= ?,
updated_by= ?
WHERE id = ?
`;
const values = [
vcRecommendation.vc_recommendation,
vcRecommendation.updated_at,
vcRecommendation.updated_by,
vcRecommendation.id,
];
connection.query(query, values, (error, result) => {
connection.release();
if (error) {
return reject(error);
}
resolve(result);
});
});
});
},
// Reports
findCompletedEvaluationsGraph: async (id) => {
return new Promise((resolve, reject) => {
pool.getConnection((err, connection) => {
if (err) {
return reject(err);
}
connection.query(
"select department as name, count(*) as value from vw_evaluatedUsers WHERE session_id = ? and status = 'Confirmed' GROUP BY department;",
[id],
(error, result) => {
connection.release();
if (error) {
return reject(error);
}
resolve(result);
}
);
});
});
},
// Report of Faculty Exceed expectation
findUsersExceedExpeGraph: async (id) => {
return new Promise((resolve, reject) => {
pool.getConnection((err, connection) => {
if (err) {
return reject(err);
}
connection.query(
"select name, average_score as value from vw_evaluatedUsers WHERE session_id = ? and status = 'Confirmed' and average_score BETWEEN 120 AND 136 GROUP BY name, average_score;",
[id],
(error, result) => {
connection.release();
if (error) {
return reject(error);
}
resolve(result);
}
);
});
});
},
// Report of Faculty the met expectation
findUsersMetExpeGraph: async (id) => {
return new Promise((resolve, reject) => {
pool.getConnection((err, connection) => {
if (err) {
return reject(err);
}
connection.query(
"select name, average_score as value from vw_evaluatedUsers WHERE session_id = ? and status = 'Confirmed' and average_score BETWEEN 86 AND 119 GROUP BY name, average_score;",
[id],
(error, result) => {
connection.release();
if (error) {
return reject(error);
}
resolve(result);
}
);
});
});
},
// Report of Faculty Approching expectation
findUsersAproachingExpeGraph: async (id) => {
return new Promise((resolve, reject) => {
pool.getConnection((err, connection) => {
if (err) {
return reject(err);
}
connection.query(
"select name, average_score as value from vw_evaluatedUsers WHERE session_id = ? and status = 'Confirmed' and average_score BETWEEN 52 AND 85 GROUP BY name, average_score;",
[id],
(error, result) => {
connection.release();
if (error) {
return reject(error);
}
resolve(result);
}
);
});
});
},
// Report of Faculty Failed to meet expectation
findUsersFailedExpeGraph: async (id) => {
return new Promise((resolve, reject) => {
pool.getConnection((err, connection) => {
if (err) {
return reject(err);
}
connection.query(
"select name, average_score as value from vw_evaluatedUsers WHERE session_id = ? and status = 'Confirmed' and average_score BETWEEN 0 AND 51 GROUP BY name, average_score;",
[id],
(error, result) => {
connection.release();
if (error) {
return reject(error);
}
resolve(result);
}
);
});
});
},
// Report of GENERAL FUCULTY PERFORMANCE
findFacultyGeneralResultGraph: async (id) => {
return new Promise((resolve, reject) => {
pool.getConnection((err, connection) => {
if (err) {
return reject(err);
}
connection.query(
"select name, value FROM vw_generalFacultyPerformance WHERE session_id = ?",
[id],
(error, result) => {
connection.release();
if (error) {
return reject(error);
}
resolve(result);
}
);
});
});
},
};