501 lines
14 KiB
JavaScript
501 lines
14 KiB
JavaScript
const cron = require('node-cron');
|
|
const mysql = require('mysql2/promise');
|
|
const SubmissionAutoFillService = require('./auto_fill_missing_quarterly_submissions_service'); // Adjust path
|
|
const IPICalculationService = require('./ipi_calculation_service');
|
|
const { sendEmailService } = require("./email.service");
|
|
|
|
// Database configuration
|
|
const dbConfig = {
|
|
host: process.env.DB_HOST,
|
|
user: process.env.DB_USER,
|
|
password: process.env.DB_PASSWORD,
|
|
database: process.env.DB_NAME,
|
|
dialect: process.env.DB_DIALECT || 'mysql',
|
|
pool: {
|
|
max: 5,
|
|
min: 0,
|
|
acquire: 30000,
|
|
idle: 10000
|
|
}
|
|
};
|
|
|
|
|
|
|
|
class AutomatedSchedulerService {
|
|
|
|
|
|
constructor() {
|
|
this.isRunning = false;
|
|
this.pool = mysql.createPool(dbConfig);
|
|
}
|
|
|
|
/**
|
|
* Fetch the latest entry from quarterly_windows_configuration_master table
|
|
*/
|
|
async getLatestQuarterlyWindow() {
|
|
const connection = await this.pool.getConnection();
|
|
try {
|
|
const query = `
|
|
SELECT
|
|
survey_name,
|
|
year,
|
|
quarter,
|
|
end_date,
|
|
start_date
|
|
FROM quarterly_windows_configuration_master
|
|
WHERE ipi_calculated = 0
|
|
ORDER BY year DESC,
|
|
CASE quarter
|
|
WHEN 'Q1' THEN 1
|
|
WHEN 'Q2' THEN 2
|
|
WHEN 'Q3' THEN 3
|
|
WHEN 'Q4' THEN 4
|
|
END DESC
|
|
LIMIT 1
|
|
`;
|
|
|
|
// const query = `
|
|
// SELECT
|
|
// year,
|
|
// quarter,
|
|
// end_date,
|
|
// start_date
|
|
// FROM quarterly_windows_configuration_master
|
|
// ORDER BY id DESC
|
|
// LIMIT 1
|
|
// `;
|
|
|
|
const [rows] = await connection.query(query);
|
|
return rows[0] || null;
|
|
} finally {
|
|
connection.release();
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Execute survey auto submit logic
|
|
*/
|
|
async executeSurveyAutoSubmit(year, quarter) {
|
|
console.log(`[${new Date().toISOString()}] Starting survey_auto_submit for ${year} ${quarter}`);
|
|
|
|
try {
|
|
const autoSubmissionService = new SubmissionAutoFillService(dbConfig);
|
|
|
|
// Auto-fill missing submissions for specific year and quarter
|
|
const result = await autoSubmissionService.autoFillMissingSubmissions(year, quarter);
|
|
|
|
// Verify completeness for specific quarter
|
|
const result3 = await autoSubmissionService.verifyCompleteness(year, quarter);
|
|
|
|
console.log(`[${new Date().toISOString()}] survey_auto_submit completed successfully`);
|
|
|
|
return {
|
|
success: true,
|
|
autoFillMissingSubmissions: result,
|
|
verifyCompleteness: result3
|
|
};
|
|
} catch (error) {
|
|
console.error(`[${new Date().toISOString()}] Error in survey_auto_submit:`, error);
|
|
throw error;
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Execute calculate quarter logic
|
|
*/
|
|
async executeCalculateQuarter(year, quarter) {
|
|
console.log(`[${new Date().toISOString()}] Starting calculate_quarter for ${year} ${quarter}`);
|
|
|
|
try {
|
|
if (!['Q1', 'Q2', 'Q3', 'Q4'].includes(quarter)) {
|
|
throw new Error('Invalid quarter. Accepted values: Q1, Q2, Q3, Q4');
|
|
}
|
|
|
|
const service = new IPICalculationService(dbConfig);
|
|
const result = await service.runCompleteCalculation(year, quarter);
|
|
|
|
await service.close();
|
|
|
|
console.log(`[${new Date().toISOString()}] calculate_quarter completed successfully`);
|
|
|
|
return {
|
|
success: true,
|
|
message: `IPI calculated for ${year} ${quarter}`,
|
|
data: result
|
|
};
|
|
} catch (error) {
|
|
console.error(`[${new Date().toISOString()}] Error in calculate_quarter:`, error);
|
|
throw error;
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Update ipi_calculated flag in quarterly_windows_configuration_master
|
|
*/
|
|
async updateIPICalculatedFlag(year, quarter) {
|
|
const connection = await this.pool.getConnection();
|
|
try {
|
|
const query = `
|
|
UPDATE quarterly_windows_configuration_master
|
|
SET ipi_calculated = 1
|
|
WHERE year = ? AND quarter = ?
|
|
`;
|
|
|
|
await connection.query(query, [year, quarter]);
|
|
console.log(`[${new Date().toISOString()}] Updated ipi_calculated flag to 1 for ${year} ${quarter}`);
|
|
} catch (error) {
|
|
console.error(`[${new Date().toISOString()}] Error updating ipi_calculated flag:`, error);
|
|
throw error;
|
|
} finally {
|
|
connection.release();
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Main scheduler logic - runs daily
|
|
*/
|
|
async runScheduledTask() {
|
|
// Prevent concurrent execution
|
|
if (this.isRunning) {
|
|
console.log(`[${new Date().toISOString()}] Scheduler already running, skipping this execution`);
|
|
return;
|
|
}
|
|
|
|
this.isRunning = true;
|
|
|
|
try {
|
|
console.log(`[${new Date().toISOString()}] ========================================`);
|
|
console.log(`[${new Date().toISOString()}] Starting scheduled task execution`);
|
|
|
|
// Step 1: Get latest quarterly window configuration
|
|
const latestWindow = await this.getLatestQuarterlyWindow();
|
|
|
|
if (!latestWindow) {
|
|
console.log(`[${new Date().toISOString()}] No quarterly window configuration found`);
|
|
return;
|
|
}
|
|
|
|
console.log(`[${new Date().toISOString()}] Latest window: Year=${latestWindow.year}, Quarter=${latestWindow.quarter}, End Date=${latestWindow.end_date}`);
|
|
|
|
// Step 2: Check if current date is greater than end_date
|
|
const currentDate = new Date();
|
|
const endDate = new Date(latestWindow.end_date);
|
|
|
|
console.log(`[${new Date().toISOString()}] Current Date: ${currentDate.toISOString()}`);
|
|
console.log(`[${new Date().toISOString()}] End Date: ${endDate.toISOString()}`);
|
|
|
|
if (currentDate < endDate) {
|
|
console.log(`[${new Date().toISOString()}] Current date is not greater than end_date. Skipping execution.`);
|
|
return;
|
|
}
|
|
|
|
console.log(`[${new Date().toISOString()}] Current date is greater than end_date. Proceeding with execution.`);
|
|
|
|
// Step 3: Execute survey_auto_submit
|
|
const surveyResult = await this.executeSurveyAutoSubmit(
|
|
latestWindow.year,
|
|
latestWindow.quarter
|
|
);
|
|
|
|
// Step 4: Execute calculate_quarter after survey_auto_submit completes
|
|
const calculationResult = await this.executeCalculateQuarter(
|
|
latestWindow.year,
|
|
latestWindow.quarter
|
|
);
|
|
|
|
// Step 5: Update ipi_calculated flag to 1
|
|
await this.updateIPICalculatedFlag(latestWindow.year, latestWindow.quarter);
|
|
|
|
console.log(`[${new Date().toISOString()}] ========================================`);
|
|
console.log(`[${new Date().toISOString()}] All scheduled tasks completed successfully`);
|
|
console.log(`[${new Date().toISOString()}] Survey Result:`, JSON.stringify(surveyResult, null, 2));
|
|
console.log(`[${new Date().toISOString()}] Calculation Result:`, JSON.stringify(calculationResult, null, 2));
|
|
|
|
} catch (error) {
|
|
console.error(`[${new Date().toISOString()}] Error in scheduled task:`, error);
|
|
// You might want to send an alert/notification here
|
|
} finally {
|
|
this.isRunning = false;
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Start the scheduler
|
|
* Runs every day at 2:00 AM (you can adjust the time)
|
|
*/
|
|
start() {
|
|
console.log(`[${new Date().toISOString()}] Starting automated scheduler...`);
|
|
|
|
// Run every day at 2:00 AM
|
|
// Format: minute hour day month weekday
|
|
// '0 2 * * *' = At 2:00 AM every day
|
|
cron.schedule('0 2 * * *', () => {
|
|
this.runScheduledTask();
|
|
});
|
|
|
|
console.log(`[${new Date().toISOString()}] Scheduler started successfully. Will run daily at 2:00 AM.`);
|
|
|
|
}
|
|
|
|
/**
|
|
* Manual trigger for testing
|
|
*/
|
|
async manualTrigger() {
|
|
console.log(`[${new Date().toISOString()}] Manual trigger initiated...`);
|
|
await this.runScheduledTask();
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
//------------------------------------------------------------------------------
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
async getEstablishmentsWithoutSubmission(year, quarter) {
|
|
|
|
const connection = await this.pool.getConnection();
|
|
|
|
try {
|
|
const query = `
|
|
SELECT
|
|
e.id AS establishment_id,
|
|
e.factory_name,
|
|
eu.id AS user_id,
|
|
eu.name AS user_name,
|
|
eu.email
|
|
FROM establishments e
|
|
LEFT JOIN establishment_users eu
|
|
ON eu.establishment_id = e.id AND eu.is_active = 1
|
|
|
|
WHERE NOT EXISTS (
|
|
SELECT 1
|
|
FROM submission s
|
|
WHERE s.establishment_id = e.id
|
|
AND s.year = ?
|
|
AND s.quarter = ? ) AND e.is_active = 1
|
|
`;
|
|
|
|
const [rows] = await connection.query(query, [year, quarter]);
|
|
|
|
return rows;
|
|
|
|
} catch (error) {
|
|
console.error("Error fetching pending establishments:", error);
|
|
return [];
|
|
} finally {
|
|
connection.release();
|
|
}
|
|
}
|
|
|
|
async sendReminderEmails(rows, latestWindow) {
|
|
try {
|
|
if (!rows.length) {
|
|
console.log("No pending establishments.");
|
|
return;
|
|
}
|
|
|
|
const basePlaceholder = {
|
|
survey_name: latestWindow.survey_name,
|
|
quarter: latestWindow.quarter,
|
|
year: latestWindow.year,
|
|
opens_on: latestWindow.start_date,
|
|
closes_on: latestWindow.end_date,
|
|
portal_url: process.env.FE_BASE_URL,
|
|
support_email: process.env.SUPPORT_EMAIL,
|
|
support_phone: process.env.SUPPORT_PHONE,
|
|
logo_url: `${process.env.APP_BASE_URL}/assets/FCSCLogo.png`
|
|
};
|
|
|
|
|
|
|
|
const promises = rows.map((r) =>
|
|
sendEmailService(r.email, "submission_last_day_reminder_mail", {
|
|
...basePlaceholder,
|
|
user_name: r.user_name,
|
|
establishment_name: r.factory_name,
|
|
})
|
|
);
|
|
|
|
await Promise.all(promises);
|
|
|
|
console.log(`Sent ${promises.length} reminder emails.`);
|
|
|
|
} catch (err) {
|
|
console.error("Reminder email error:", err);
|
|
}
|
|
}
|
|
|
|
async sendEmailReminders(latestWindow) {
|
|
console.log(
|
|
`[${new Date().toISOString()}] Sending reminders for Year=${latestWindow.year}, Quarter=${latestWindow.quarter}`
|
|
);
|
|
|
|
const establishments = await this.getEstablishmentsWithoutSubmission(
|
|
latestWindow.year,
|
|
latestWindow.quarter
|
|
);
|
|
|
|
if (!establishments.length) {
|
|
console.log("No pending establishments found.");
|
|
return;
|
|
}
|
|
|
|
await this.sendReminderEmails(establishments, latestWindow);
|
|
|
|
console.log(`[${new Date().toISOString()}] Email reminders completed.`);
|
|
}
|
|
|
|
async getAllQuarterlyWindows() {
|
|
const connection = await this.pool.getConnection();
|
|
try {
|
|
const query = `
|
|
SELECT
|
|
survey_name,
|
|
year,
|
|
quarter,
|
|
end_date,
|
|
start_date
|
|
FROM quarterly_windows_configuration_master
|
|
WHERE ipi_calculated = 0
|
|
ORDER BY year DESC,
|
|
CASE quarter
|
|
WHEN 'Q1' THEN 1
|
|
WHEN 'Q2' THEN 2
|
|
WHEN 'Q3' THEN 3
|
|
WHEN 'Q4' THEN 4
|
|
END DESC
|
|
`;
|
|
|
|
// const query = `
|
|
// SELECT
|
|
// year,
|
|
// quarter,
|
|
// end_date,
|
|
// start_date
|
|
// FROM quarterly_windows_configuration_master
|
|
// ORDER BY id DESC
|
|
// LIMIT 1
|
|
// `;
|
|
|
|
const [rows] = await connection.query(query);
|
|
return rows || null;
|
|
} finally {
|
|
connection.release();
|
|
}
|
|
}
|
|
|
|
async runScheduledEmails() {
|
|
|
|
// find all quarterly window data ordered by year desc and quarter desc
|
|
const latestWindow = await this.getAllQuarterlyWindows();
|
|
|
|
// check if data exists
|
|
if (!latestWindow || latestWindow.length === 0) {
|
|
console.log(`[${new Date().toISOString()}] No quarterly window configuration found for email reminders`);
|
|
return;
|
|
}
|
|
|
|
// loop through all windows
|
|
for (const window of latestWindow) {
|
|
|
|
console.log(
|
|
`[${new Date().toISOString()}] Processing window: Year=${window.year}, Quarter=${window.quarter}, End Date=${window.end_date}`
|
|
);
|
|
|
|
let currentDateStr = new Date().toLocaleDateString("en-CA");
|
|
let endDateStr = new Date(window.end_date).toLocaleDateString("en-CA");
|
|
|
|
console.log(`[${new Date().toISOString()}] Current Date: ${currentDateStr}`);
|
|
console.log(`[${new Date().toISOString()}] End Date: ${endDateStr}`);
|
|
|
|
// Check if today is the end date
|
|
if (currentDateStr === endDateStr) {
|
|
console.log(`[${new Date().toISOString()}] Today is the last day. Sending reminder emails.`);
|
|
await this.sendEmailReminders(window);
|
|
} else {
|
|
console.log(`[${new Date().toISOString()}] Not the last day. Skipping reminder emails.`);
|
|
}
|
|
}
|
|
}
|
|
|
|
// async runScheduledEmails() {
|
|
|
|
// //find latest quarterly window configuration
|
|
// const latestWindow = await this.getLatestQuarterlyWindow();
|
|
|
|
// if (!latestWindow) {
|
|
// console.log(`[${new Date().toISOString()}] No quarterly window configuration found for email reminders`);
|
|
// return;
|
|
// }
|
|
|
|
// console.log(`[${new Date().toISOString()}] Latest window for email reminders: Year=${latestWindow.year}, Quarter=${latestWindow.quarter}, End Date=${latestWindow.end_date}`);
|
|
|
|
|
|
// // Check if current date is greater than end_date
|
|
// const currentDate = new Date();
|
|
// const endDate = new Date(latestWindow.end_date);
|
|
|
|
// const currentDateStr = new Date().toLocaleDateString("en-CA");
|
|
// const endDateStr = new Date(latestWindow.end_date).toLocaleDateString("en-CA");
|
|
|
|
// console.log(`[${new Date().toISOString()}] Current Date: ${currentDateStr}`);
|
|
// console.log(`[${new Date().toISOString()}] End Date: ${endDateStr}`);
|
|
|
|
// if (currentDateStr === endDateStr) {
|
|
// console.log(`[${new Date().toISOString()}] Today is the last day. Sending reminder emails.`);
|
|
// await this.sendEmailReminders(latestWindow);
|
|
// } else {
|
|
// console.log(`[${new Date().toISOString()}] Not the last day. Skipping reminder emails.`);
|
|
// }
|
|
|
|
|
|
// }
|
|
/**
|
|
* Start the scheduler
|
|
* Runs every day at 6:00 AM (you can adjust the time)
|
|
*/
|
|
startRemainderEmail() {
|
|
console.log(`[${new Date().toISOString()}] Starting automated email reminder scheduler...`);
|
|
// Run every day at 6:00 AM
|
|
// Format: minute hour day month weekday
|
|
// '0 6 * * *' = At 6:00 AM every day
|
|
cron.schedule('0 6 * * *', () => {
|
|
this.runScheduledEmails();
|
|
});
|
|
|
|
console.log(`[${new Date().toISOString()}] Scheduler started successfully. Will run daily at 6:00 AM.`);
|
|
|
|
}
|
|
|
|
}
|
|
|
|
module.exports = AutomatedSchedulerService;
|