145 lines
4.2 KiB
JavaScript
145 lines
4.2 KiB
JavaScript
require('dotenv').config({ path: require('path').join(__dirname, '..', '.env') });
|
|
const mysql = require('mysql2/promise');
|
|
|
|
async function main() {
|
|
const apply = process.argv.includes('--apply');
|
|
|
|
const pool = await mysql.createPool({
|
|
host: process.env.DB_HOST,
|
|
user: process.env.DB_USER,
|
|
password: process.env.DB_PASSWORD,
|
|
database: process.env.DB_NAME,
|
|
port: process.env.DB_PORT || 3306
|
|
});
|
|
|
|
try {
|
|
const [rows] = await pool.execute(`
|
|
SELECT
|
|
mp.id,
|
|
mp.name,
|
|
mp.area_based,
|
|
mp.time_per_sqm,
|
|
mp.estimated_hours,
|
|
mp.hourly_rate,
|
|
mp.total_estimated_price,
|
|
COALESCE(task_totals.task_hours, 0) AS task_hours,
|
|
COALESCE(task_totals.task_count, 0) AS task_count,
|
|
COALESCE(material_totals.material_cost, 0) AS material_cost,
|
|
COALESCE(material_totals.material_count, 0) AS material_count
|
|
FROM material_packages mp
|
|
LEFT JOIN (
|
|
SELECT
|
|
package_id,
|
|
COUNT(*) AS task_count,
|
|
COALESCE(SUM(hours), 0) AS task_hours
|
|
FROM smart_package_tasks
|
|
GROUP BY package_id
|
|
) task_totals ON task_totals.package_id = mp.id
|
|
LEFT JOIN (
|
|
SELECT
|
|
package_id,
|
|
COUNT(*) AS material_count,
|
|
COALESCE(SUM(COALESCE(total_price, quantity * unit_price)), 0) AS material_cost
|
|
FROM package_materials
|
|
GROUP BY package_id
|
|
) material_totals ON material_totals.package_id = mp.id
|
|
WHERE mp.is_active = 1
|
|
ORDER BY mp.id
|
|
`);
|
|
|
|
const materialMismatches = [];
|
|
const taskMismatches = [];
|
|
const areaBasedHourWarnings = [];
|
|
|
|
for (const row of rows) {
|
|
const headerMaterials = Number(row.total_estimated_price || 0);
|
|
const actualMaterials = Number(row.material_cost || 0);
|
|
const headerHours = Number(row.estimated_hours || 0);
|
|
const taskHours = Number(row.task_hours || 0);
|
|
const timePerSqm = Number(row.time_per_sqm || 0);
|
|
const isAreaBased = Number(row.area_based || 0) === 1;
|
|
|
|
if (Math.abs(headerMaterials - actualMaterials) > 1) {
|
|
materialMismatches.push({
|
|
id: row.id,
|
|
name: row.name,
|
|
headerMaterials,
|
|
actualMaterials
|
|
});
|
|
}
|
|
|
|
if (taskHours > 0 && Math.abs(headerHours - taskHours) > 0.1) {
|
|
taskMismatches.push({
|
|
id: row.id,
|
|
name: row.name,
|
|
headerHours,
|
|
taskHours
|
|
});
|
|
}
|
|
|
|
if (isAreaBased && timePerSqm > 0 && taskHours === 0) {
|
|
areaBasedHourWarnings.push({
|
|
id: row.id,
|
|
name: row.name,
|
|
headerHours,
|
|
timePerSqm
|
|
});
|
|
}
|
|
}
|
|
|
|
console.log(JSON.stringify({
|
|
totalPackages: rows.length,
|
|
materialMismatches: materialMismatches.length,
|
|
taskMismatches: taskMismatches.length,
|
|
areaBasedHourWarnings: areaBasedHourWarnings.length,
|
|
examples: {
|
|
materialMismatches: materialMismatches.slice(0, 10),
|
|
taskMismatches: taskMismatches.slice(0, 10),
|
|
areaBasedHourWarnings: areaBasedHourWarnings.slice(0, 10)
|
|
}
|
|
}, null, 2));
|
|
|
|
if (!apply) {
|
|
return;
|
|
}
|
|
|
|
await pool.execute(`
|
|
UPDATE material_packages mp
|
|
LEFT JOIN (
|
|
SELECT
|
|
package_id,
|
|
COALESCE(SUM(COALESCE(total_price, quantity * unit_price)), 0) AS material_cost
|
|
FROM package_materials
|
|
GROUP BY package_id
|
|
) material_totals ON material_totals.package_id = mp.id
|
|
SET mp.total_estimated_price = COALESCE(material_totals.material_cost, 0),
|
|
mp.updated_at = NOW()
|
|
WHERE mp.is_active = 1
|
|
`);
|
|
|
|
await pool.execute(`
|
|
UPDATE material_packages mp
|
|
JOIN (
|
|
SELECT
|
|
package_id,
|
|
COALESCE(SUM(hours), 0) AS task_hours
|
|
FROM smart_package_tasks
|
|
GROUP BY package_id
|
|
) task_totals ON task_totals.package_id = mp.id
|
|
SET mp.estimated_hours = task_totals.task_hours,
|
|
mp.updated_at = NOW()
|
|
WHERE mp.is_active = 1
|
|
AND task_totals.task_hours > 0
|
|
`);
|
|
|
|
console.log('Applied smart package header sync for material totals and task-based hours.');
|
|
} finally {
|
|
await pool.end();
|
|
}
|
|
}
|
|
|
|
main().catch((error) => {
|
|
console.error(error);
|
|
process.exit(1);
|
|
});
|