821 lines
29 KiB
JavaScript
821 lines
29 KiB
JavaScript
// IPI Index Calculation Service
|
||
const mysql = require('mysql2/promise');
|
||
const logger = require("./logger");
|
||
|
||
class IPICalculationService {
|
||
|
||
|
||
constructor(dbConfig) {
|
||
this.pool = mysql.createPool(dbConfig);
|
||
}
|
||
|
||
|
||
// STEP 1: Calculate and Store Base Year Average Production (Quarter-based)
|
||
async calculateBaseYearProduction(baseYear = 2022, forceRecalculate = false) {
|
||
|
||
const connection = await this.pool.getConnection();
|
||
let logId = null;
|
||
|
||
try {
|
||
await connection.beginTransaction();
|
||
|
||
logger.info(`calculateBaseYearProduction - Calculating Base Year Production for ${baseYear}...`);
|
||
|
||
// If force recalculate, delete existing data
|
||
if (forceRecalculate) {
|
||
logger.info('calculateBaseYearProduction - Force recalculate enabled , clearing existing data...');
|
||
await connection.query(
|
||
`DELETE FROM base_year_production WHERE base_year = ?`,
|
||
[baseYear]
|
||
);
|
||
}
|
||
|
||
// Log calculation start
|
||
logId = await this.logRecord( 'base_year', baseYear, null, 'Started', null, 0, null, connection);
|
||
|
||
// Quarter-based base year production using submission_products.current_quantity
|
||
const query = `
|
||
SELECT
|
||
p.id AS product_id,
|
||
p.hs_code AS product_hs_code,
|
||
u.uom_short_name AS unit,
|
||
SUM(CASE WHEN s.quarter = 'Q1' THEN COALESCE(sp.current_quantity, 0) END) AS q1,
|
||
SUM(CASE WHEN s.quarter = 'Q2' THEN COALESCE(sp.current_quantity, 0) END) AS q2,
|
||
SUM(CASE WHEN s.quarter = 'Q3' THEN COALESCE(sp.current_quantity, 0) END) AS q3,
|
||
SUM(CASE WHEN s.quarter = 'Q4' THEN COALESCE(sp.current_quantity, 0) END) AS q4,
|
||
ROUND((
|
||
COALESCE(SUM(CASE WHEN s.quarter='Q1' THEN sp.current_quantity END),0) +
|
||
COALESCE(SUM(CASE WHEN s.quarter='Q2' THEN sp.current_quantity END),0) +
|
||
COALESCE(SUM(CASE WHEN s.quarter='Q3' THEN sp.current_quantity END),0) +
|
||
COALESCE(SUM(CASE WHEN s.quarter='Q4' THEN sp.current_quantity END),0)
|
||
) / 4, 2) AS avg_by_production
|
||
FROM submission_products sp
|
||
JOIN submission s ON s.id = sp.submission_id
|
||
JOIN products p ON p.id = sp.product_id
|
||
LEFT JOIN unit_master u ON u.id = p.unit_id
|
||
WHERE s.year = ? AND sp.is_active = 1 AND s.status = 'Approved'
|
||
GROUP BY p.id
|
||
`;
|
||
|
||
const [products] = await connection.query(query, [baseYear]);
|
||
|
||
|
||
logger.info(`calculateBaseYearProduction - Found ${products.length} products to process...`);
|
||
|
||
|
||
// Insert into base_year_production table
|
||
let processedCount = 0;
|
||
for (const product of products) {
|
||
await connection.query(
|
||
`INSERT INTO base_year_production
|
||
(product_id, product_hs_code, base_year, unit, q1, q2, q3, q4, avg_by_production)
|
||
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
|
||
[
|
||
product.product_id, product.product_hs_code, baseYear, product.unit,
|
||
product.q1 || 0, product.q2 || 0, product.q3 || 0, product.q4 || 0,
|
||
product.avg_by_production
|
||
]
|
||
);
|
||
processedCount++;
|
||
|
||
// Log progress every 50 products
|
||
if (processedCount % 50 === 0) {
|
||
logger.info(`calculateBaseYearProduction - Processed ${processedCount}/${products.length} products...`);
|
||
}
|
||
}
|
||
|
||
// Update log
|
||
await this.logRecord('base_year', baseYear, null, 'Completed', logId, products.length, null,connection);
|
||
|
||
|
||
await connection.commit();
|
||
logger.info(`calculateBaseYearProduction - Base Year Production calculated: ${products.length} products`);
|
||
return { success: true, productsProcessed: products.length };
|
||
|
||
} catch (error) {
|
||
await connection.rollback();
|
||
console.error('Error calculating base year production:', error);
|
||
logger.error(`calculateBaseYearProduction - Error calculating base year production: ${error} `);
|
||
// Update log OUTSIDE the failed transaction
|
||
if (logId) {
|
||
await this.logRecord('base_year', baseYear, null, 'Failed', logId, 0, error.message);
|
||
}
|
||
|
||
throw error;
|
||
} finally {
|
||
connection.release();
|
||
}
|
||
}
|
||
|
||
|
||
// STEP 2: Aggregate Quarterly Production
|
||
async aggregateQuarterlyProduction(year, quarter) {
|
||
const connection = await this.pool.getConnection();
|
||
let logId = null;
|
||
|
||
try {
|
||
await connection.beginTransaction();
|
||
|
||
logger.info(`aggregateQuarterlyProduction - Aggregating production for ${year}-${quarter}...`);
|
||
|
||
// Log calculation start
|
||
logId = await this.logRecord('monthly_production', year, quarter, 'Started', null, 0, null, connection);
|
||
|
||
const validQuarters = new Set(['Q1', 'Q2', 'Q3', 'Q4']);
|
||
if (!validQuarters.has(quarter)) {
|
||
throw new Error('Invalid quarter. Accepted values: Q1, Q2, Q3, Q4');
|
||
}
|
||
|
||
// Aggregate production by product
|
||
const query = `
|
||
SELECT
|
||
p.id AS product_id,
|
||
p.hs_code AS product_hs_code,
|
||
u.uom_short_name AS unit,
|
||
SUM(COALESCE(sp.current_quantity, 0)) AS production_quantity
|
||
FROM submission_products sp
|
||
JOIN submission s ON s.id = sp.submission_id
|
||
JOIN products p ON p.id = sp.product_id
|
||
LEFT JOIN unit_master u ON u.id = p.unit_id
|
||
WHERE s.year = ? AND s.quarter = ?
|
||
AND sp.is_active = 1
|
||
AND s.status = 'Approved'
|
||
GROUP BY p.id
|
||
`;
|
||
|
||
const [products] = await connection.query(query, [year, quarter]);
|
||
|
||
// Insert monthly production
|
||
for (const product of products) {
|
||
await connection.query(
|
||
`INSERT INTO monthly_production
|
||
(product_id, product_hs_code, year, quarter, quarter_name,
|
||
production_quantity, unit)
|
||
VALUES (?, ?, ?, ?, ?, ?, ?)
|
||
ON DUPLICATE KEY UPDATE
|
||
production_quantity=VALUES(production_quantity),
|
||
quarter_name=VALUES(quarter_name),
|
||
unit=VALUES(unit)`,
|
||
[
|
||
product.product_id, product.product_hs_code, year, quarter,
|
||
quarter, product.production_quantity || 0, product.unit
|
||
]
|
||
);
|
||
}
|
||
|
||
|
||
// Update log
|
||
await this.logRecord('monthly_production', year, quarter, 'Completed', logId, products.length, null, connection);
|
||
|
||
await connection.commit();
|
||
logger.info(`aggregateQuarterlyProduction - Quarterly production aggregated: ${products.length} products`);
|
||
return { success: true, productsProcessed: products.length };
|
||
|
||
} catch (error) {
|
||
await connection.rollback();
|
||
console.error('Error aggregating monthly production:', error);
|
||
logger.error(`aggregateQuarterlyProduction - Error aggregating quarterly production: ${error}`);
|
||
// Update log OUTSIDE the failed transaction
|
||
if (logId) {
|
||
await this.logRecord('monthly_production', year, quarter, 'Failed', logId, 0, error.message);
|
||
}
|
||
throw error;
|
||
} finally {
|
||
connection.release();
|
||
}
|
||
}
|
||
|
||
|
||
// STEP 3: Calculate Item Level Indices
|
||
async calculateItemLevelIndices(year, quarter) {
|
||
const connection = await this.pool.getConnection();
|
||
let logId = null;
|
||
try {
|
||
await connection.beginTransaction();
|
||
|
||
logger.info(`calculateItemLevelIndices - Calculating item level indices for ${year}-${quarter}...`);
|
||
|
||
// Log calculation start
|
||
logId = await this.logRecord('item_level_indices', year, quarter, 'Started', null, 0, null, connection);
|
||
|
||
// Calculate indices: Ri = Current Production / Base Year Avg Production
|
||
// Ii = Ri × 100
|
||
const query = `
|
||
SELECT
|
||
mp.product_id,
|
||
mp.product_hs_code,
|
||
mp.production_quantity AS current_production,
|
||
byp.avg_by_production AS base_year_avg_production,
|
||
(mp.production_quantity / byp.avg_by_production) AS production_relative,
|
||
((mp.production_quantity / byp.avg_by_production) * 100) AS item_index
|
||
FROM monthly_production mp
|
||
JOIN base_year_production byp ON byp.product_id = mp.product_id
|
||
WHERE mp.year = ? AND mp.quarter = ?
|
||
AND byp.avg_by_production > 0
|
||
`;
|
||
|
||
const [items] = await connection.query(query, [year, quarter]);
|
||
|
||
// Insert item level indices
|
||
for (const item of items) {
|
||
await connection.query(
|
||
`INSERT INTO item_level_indices
|
||
(product_id, product_hs_code, year, quarter, quarter_name,
|
||
current_production, base_year_avg_production,
|
||
production_relative, item_index)
|
||
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
|
||
ON DUPLICATE KEY UPDATE
|
||
current_production=VALUES(current_production),
|
||
base_year_avg_production=VALUES(base_year_avg_production),
|
||
production_relative=VALUES(production_relative),
|
||
item_index=VALUES(item_index)`,
|
||
[
|
||
item.product_id, item.product_hs_code, year, quarter, quarter,
|
||
item.current_production, item.base_year_avg_production,
|
||
item.production_relative, item.item_index
|
||
]
|
||
);
|
||
}
|
||
|
||
// Update log
|
||
await this.logRecord('item_level_indices', year, quarter, 'Completed', logId, items.length, null, connection);
|
||
|
||
await connection.commit();
|
||
logger.info(`calculateItemLevelIndices - Item level indices calculated: ${items.length} items`);
|
||
return { success: true, itemsProcessed: items.length };
|
||
|
||
} catch (error) {
|
||
await connection.rollback();
|
||
console.error('Error calculating item indices:', error);
|
||
logger.error(`calculateItemLevelIndices - Error calculating item indices: ${error}`);
|
||
// Update log OUTSIDE the failed transaction
|
||
if (logId) {
|
||
await this.logRecord('item_level_indices', year, quarter, 'Failed', logId, 0, error.message);
|
||
}
|
||
throw error;
|
||
} finally {
|
||
connection.release();
|
||
}
|
||
}
|
||
|
||
|
||
// STEP 4: Calculate ISIC 4-Digit Level Indices
|
||
async calculateISIC4DigitIndices(year, quarter) {
|
||
const connection = await this.pool.getConnection();
|
||
let logId = null;
|
||
try {
|
||
await connection.beginTransaction();
|
||
|
||
logger.info(`calculateISIC4DigitIndices - Calculating ISIC 4-digit indices for ${year}-${quarter}...`);
|
||
|
||
// Log calculation start
|
||
logId = await this.logRecord('isic_4digit_indices', year, quarter, 'Started', null, 0, null, connection);
|
||
|
||
// ISIC 4-digit grouping uses establishments.isic_code (via approved submissions).
|
||
// One establishment per product-period is chosen (highest current_quantity, then submission id)
|
||
// so item_index and weight_in_ib are not multiplied when several establishments report the same product.
|
||
const query = `
|
||
WITH product_isic_from_establishment AS (
|
||
SELECT
|
||
ili.product_id,
|
||
ili.item_index,
|
||
LEFT(TRIM(CAST(e.isic_code AS CHAR)), 4) AS isic_4digit_code,
|
||
ROW_NUMBER() OVER (
|
||
PARTITION BY ili.product_id
|
||
ORDER BY COALESCE(sp.current_quantity, 0) DESC, s.id ASC
|
||
) AS rn
|
||
FROM item_level_indices ili
|
||
JOIN submission_products sp ON sp.product_id = ili.product_id
|
||
JOIN submission s ON s.id = sp.submission_id
|
||
AND s.year = ?
|
||
AND s.quarter = ?
|
||
AND s.status = 'Approved'
|
||
JOIN establishments e ON e.id = s.establishment_id
|
||
WHERE ili.year = ?
|
||
AND ili.quarter = ?
|
||
AND sp.is_active = 1
|
||
AND e.isic_code IS NOT NULL
|
||
AND TRIM(CAST(e.isic_code AS CHAR)) <> ''
|
||
)
|
||
SELECT
|
||
pie.isic_4digit_code,
|
||
SUM(COALESCE(p.weight_in_ib, 0)) AS total_weight,
|
||
SUM(COALESCE(p.weight_in_ib, 0) * pie.item_index) AS weighted_index_sum,
|
||
(
|
||
SUM(COALESCE(p.weight_in_ib, 0) * pie.item_index) /
|
||
NULLIF(SUM(COALESCE(p.weight_in_ib, 0)), 0)
|
||
) AS isic_4digit_index
|
||
FROM product_isic_from_establishment pie
|
||
JOIN products p ON p.id = pie.product_id
|
||
WHERE pie.rn = 1
|
||
AND p.weight_in_ib IS NOT NULL
|
||
AND p.weight_in_ib > 0
|
||
GROUP BY pie.isic_4digit_code
|
||
`;
|
||
|
||
const [isic4Results] = await connection.query(query, [year, quarter, year, quarter]);
|
||
|
||
for (const result of isic4Results) {
|
||
await connection.query(
|
||
`INSERT INTO isic_4digit_indices
|
||
(isic_4digit_code, year, quarter, quarter_name, total_weight,
|
||
weighted_index_sum, isic_4digit_index)
|
||
VALUES (?, ?, ?, ?, ?, ?, ?)
|
||
ON DUPLICATE KEY UPDATE
|
||
total_weight=VALUES(total_weight),
|
||
weighted_index_sum=VALUES(weighted_index_sum),
|
||
isic_4digit_index=VALUES(isic_4digit_index)`,
|
||
[
|
||
result.isic_4digit_code, year, quarter, quarter,
|
||
result.total_weight,
|
||
result.weighted_index_sum,
|
||
result.isic_4digit_index
|
||
]
|
||
);
|
||
}
|
||
|
||
// Update log
|
||
await this.logRecord('isic_4digit_indices', year, quarter, 'Completed', logId, isic4Results.length, null, connection);
|
||
|
||
await connection.commit();
|
||
logger.info(`calculateISIC4DigitIndices - ISIC 4-digit indices calculated: ${isic4Results.length} codes`);
|
||
return { success: true, codesProcessed: isic4Results.length };
|
||
|
||
} catch (error) {
|
||
await connection.rollback();
|
||
console.error('Error calculating ISIC 4-digit indices:', error);
|
||
logger.error(`calculateISIC4DigitIndices - Error calculating ISIC 4-digit indices: ${error} `);
|
||
// Update log OUTSIDE the failed transaction
|
||
if (logId) {
|
||
await this.logRecord('isic_4digit_indices', year, quarter, 'Failed', logId, 0, error.message);
|
||
}
|
||
throw error;
|
||
} finally {
|
||
connection.release();
|
||
}
|
||
}
|
||
|
||
|
||
// STEP 5: Calculate ISIC 3-Digit Level Indices
|
||
async calculateISIC3DigitIndices(year, quarter) {
|
||
const connection = await this.pool.getConnection();
|
||
let logId = null;
|
||
try {
|
||
await connection.beginTransaction();
|
||
|
||
logger.info(`calculateISIC3DigitIndices - Calculating ISIC 3-digit indices for ${year}-${quarter}...`);
|
||
|
||
// Log calculation start
|
||
logId = await this.logRecord('isic_3digit_indices', year, quarter, 'Started', null, 0, null, connection);
|
||
|
||
const query = `
|
||
SELECT
|
||
LEFT(isic_4digit_code, 3) AS isic_3digit_code,
|
||
SUM(total_weight) AS total_weight,
|
||
SUM(total_weight * isic_4digit_index) AS weighted_index_sum,
|
||
(SUM(total_weight * isic_4digit_index) / SUM(total_weight)) AS isic_3digit_index
|
||
FROM isic_4digit_indices
|
||
WHERE year = ? AND quarter = ?
|
||
GROUP BY LEFT(isic_4digit_code, 3)
|
||
`;
|
||
|
||
const [isic3Results] = await connection.query(query, [year, quarter]);
|
||
|
||
for (const result of isic3Results) {
|
||
await connection.query(
|
||
`INSERT INTO isic_3digit_indices
|
||
(isic_3digit_code, year, quarter, quarter_name, total_weight,
|
||
weighted_index_sum, isic_3digit_index)
|
||
VALUES (?, ?, ?, ?, ?, ?, ?)
|
||
ON DUPLICATE KEY UPDATE
|
||
total_weight=VALUES(total_weight),
|
||
weighted_index_sum=VALUES(weighted_index_sum),
|
||
isic_3digit_index=VALUES(isic_3digit_index)`,
|
||
[
|
||
result.isic_3digit_code, year, quarter, quarter,
|
||
result.total_weight, result.weighted_index_sum,
|
||
result.isic_3digit_index
|
||
]
|
||
);
|
||
}
|
||
|
||
// Update log
|
||
await this.logRecord('isic_3digit_indices', year, quarter, 'Completed', logId, isic3Results.length, null, connection);
|
||
|
||
await connection.commit();
|
||
logger.info(`calculateISIC3DigitIndices - ISIC 3-digit indices calculated: ${isic3Results.length} codes`);
|
||
return { success: true, codesProcessed: isic3Results.length };
|
||
|
||
} catch (error) {
|
||
await connection.rollback();
|
||
console.error('Error calculating ISIC 3-digit indices:', error);
|
||
logger.error(`calculateISIC3DigitIndices - Error calculating ISIC 3-digit indices: ${error} `);
|
||
// Update log OUTSIDE the failed transaction
|
||
if (logId) {
|
||
await this.logRecord('isic_3digit_indices', year, quarter, 'Failed', logId, 0, error.message);
|
||
}
|
||
throw error;
|
||
} finally {
|
||
connection.release();
|
||
}
|
||
}
|
||
|
||
|
||
// STEP 6: Calculate ISIC 2-Digit Level Indices
|
||
async calculateISIC2DigitIndices(year, quarter) {
|
||
const connection = await this.pool.getConnection();
|
||
let logId = null;
|
||
try {
|
||
await connection.beginTransaction();
|
||
|
||
logger.info(`calculateISIC2DigitIndices - Calculating ISIC 2-digit indices for ${year}-${quarter}...`);
|
||
|
||
// Log calculation start
|
||
logId = await this.logRecord('isic_2digit_indices', year, quarter, 'Started', null, 0, null, connection);
|
||
|
||
const query = `
|
||
SELECT
|
||
LEFT(isic_3digit_code, 2) AS isic_2digit_code,
|
||
SUM(total_weight) AS total_weight,
|
||
SUM(total_weight * isic_3digit_index) AS weighted_index_sum,
|
||
(SUM(total_weight * isic_3digit_index) / SUM(total_weight)) AS isic_2digit_index
|
||
FROM isic_3digit_indices
|
||
WHERE year = ? AND quarter = ?
|
||
GROUP BY LEFT(isic_3digit_code, 2)
|
||
`;
|
||
|
||
const [isic2Results] = await connection.query(query, [year, quarter]);
|
||
|
||
for (const result of isic2Results) {
|
||
await connection.query(
|
||
`INSERT INTO isic_2digit_indices
|
||
(isic_2digit_code, year, quarter, quarter_name, total_weight,
|
||
weighted_index_sum, isic_2digit_index)
|
||
VALUES (?, ?, ?, ?, ?, ?, ?)
|
||
ON DUPLICATE KEY UPDATE
|
||
total_weight=VALUES(total_weight),
|
||
weighted_index_sum=VALUES(weighted_index_sum),
|
||
isic_2digit_index=VALUES(isic_2digit_index)`,
|
||
[
|
||
result.isic_2digit_code, year, quarter, quarter,
|
||
result.total_weight, result.weighted_index_sum,
|
||
result.isic_2digit_index
|
||
]
|
||
);
|
||
}
|
||
|
||
// Update log
|
||
await this.logRecord('isic_2digit_indices', year, quarter, 'Completed', logId, isic2Results.length, null, connection);
|
||
|
||
await connection.commit();
|
||
logger.info(`calculateISIC2DigitIndices - ISIC 2-digit indices calculated: ${isic2Results.length} codes`);
|
||
return { success: true, codesProcessed: isic2Results.length };
|
||
|
||
} catch (error) {
|
||
await connection.rollback();
|
||
console.error('Error calculating ISIC 2-digit indices:', error);
|
||
logger.error(`calculateISIC2DigitIndices - Error calculating ISIC 2-digit indices: ${error}`);
|
||
// Update log OUTSIDE the failed transaction
|
||
if (logId) {
|
||
await this.logRecord('isic_2digit_indices', year, quarter, 'Failed', logId, 0, error.message);
|
||
}
|
||
throw error;
|
||
} finally {
|
||
connection.release();
|
||
}
|
||
}
|
||
|
||
|
||
// STEP 7: Calculate Manufacturing IPI (Headline Index)
|
||
async calculateManufacturingIPI(year, quarter) {
|
||
const connection = await this.pool.getConnection();
|
||
let logId = null;
|
||
try {
|
||
await connection.beginTransaction();
|
||
|
||
logger.info(`calculateManufacturingIPI - Calculating Manufacturing indices for ${year}-${quarter}...`);
|
||
|
||
// Log calculation start
|
||
logId = await this.logRecord('manufacturing_ipi', year, quarter, 'Started', null, 0, null, connection);
|
||
|
||
// Calculate Manufacturing IPI
|
||
const query = `
|
||
SELECT
|
||
SUM(total_weight) AS total_weight,
|
||
SUM(total_weight * isic_2digit_index) AS weighted_index_sum,
|
||
(SUM(total_weight * isic_2digit_index) / SUM(total_weight)) AS manufacturing_index
|
||
FROM isic_2digit_indices
|
||
WHERE year = ? AND quarter = ?
|
||
`;
|
||
|
||
const [result] = await connection.query(query, [year, quarter]);
|
||
|
||
// If no data found (or aggregate returns NULLs), skip insert to avoid NOT NULL DB errors
|
||
if (
|
||
!result ||
|
||
result.length === 0 ||
|
||
result[0].total_weight == null ||
|
||
result[0].weighted_index_sum == null ||
|
||
result[0].manufacturing_index == null
|
||
) {
|
||
await this.logRecord('manufacturing_ipi', year, quarter, 'Completed', logId, 0, null, connection);
|
||
await connection.commit();
|
||
|
||
logger.info(`calculateManufacturingIPI - No valid ISIC 2-digit aggregates found for ${year}-${quarter}`);
|
||
return {
|
||
success: true,
|
||
index: null,
|
||
qoqChange: null,
|
||
momChange: null,
|
||
yoyChange: null,
|
||
message: "No IPI data found for the given year and quarter"
|
||
};
|
||
}
|
||
|
||
const ipiData = result[0];
|
||
|
||
// Calculate QoQ and YoY changes
|
||
let qoqChange = null;
|
||
let yoyChange = null;
|
||
|
||
// Previous quarter (QoQ)
|
||
const quarterOrder = ['Q1', 'Q2', 'Q3', 'Q4'];
|
||
const quarterIdx = quarterOrder.indexOf(quarter);
|
||
const prevQuarter = quarterIdx === 0 ? 'Q4' : quarterOrder[quarterIdx - 1];
|
||
const prevYear = quarterIdx === 0 ? year - 1 : year;
|
||
|
||
const [prevQuarterData] = await connection.query(
|
||
`SELECT manufacturing_index FROM manufacturing_ipi
|
||
WHERE year = ? AND quarter = ?`,
|
||
[prevYear, prevQuarter]
|
||
);
|
||
|
||
if (prevQuarterData.length > 0 && prevQuarterData[0].manufacturing_index) {
|
||
const prevIndex = prevQuarterData[0].manufacturing_index;
|
||
if (Number(prevIndex) !== 0) {
|
||
qoqChange = ((ipiData.manufacturing_index - prevIndex) / prevIndex) * 100;
|
||
}
|
||
}
|
||
|
||
// Get same quarter last year for YoY
|
||
const [lastYearData] = await connection.query(
|
||
`SELECT manufacturing_index FROM manufacturing_ipi
|
||
WHERE year = ? AND quarter = ?`,
|
||
[year - 1, quarter]
|
||
);
|
||
|
||
if (lastYearData.length > 0 && lastYearData[0].manufacturing_index) {
|
||
const lastYearIndex = lastYearData[0].manufacturing_index;
|
||
if (Number(lastYearIndex) !== 0) {
|
||
yoyChange = ((ipiData.manufacturing_index - lastYearIndex) / lastYearIndex) * 100;
|
||
}
|
||
}
|
||
|
||
const quarterStartMonth = { Q1: '01', Q2: '04', Q3: '07', Q4: '10' };
|
||
const referenceDate = `${year}-${quarterStartMonth[quarter]}-01`;
|
||
|
||
// Insert Manufacturing IPI
|
||
await connection.query(
|
||
`INSERT INTO manufacturing_ipi
|
||
(year, quarter, quarter_name, reference_date, total_weight,
|
||
weighted_index_sum, manufacturing_index, mom_change, yoy_change, status, generated_on)
|
||
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, 'Completed',?)
|
||
ON DUPLICATE KEY UPDATE
|
||
total_weight=VALUES(total_weight),
|
||
weighted_index_sum=VALUES(weighted_index_sum),
|
||
manufacturing_index=VALUES(manufacturing_index),
|
||
mom_change=VALUES(mom_change),
|
||
yoy_change=VALUES(yoy_change),
|
||
status=VALUES(status),
|
||
generated_on = VALUES(generated_on)`,
|
||
[
|
||
year, quarter, quarter, referenceDate,
|
||
ipiData.total_weight, ipiData.weighted_index_sum,
|
||
ipiData.manufacturing_index, qoqChange, yoyChange , new Date()
|
||
]
|
||
);
|
||
|
||
|
||
// Update log
|
||
await this.logRecord('manufacturing_ipi', year, quarter, 'Completed', logId, result.length, null, connection);
|
||
|
||
await connection.commit();
|
||
|
||
|
||
logger.info(`calculateManufacturingIPI - Manufacturing IPI calculated: ${ipiData.manufacturing_index}`);
|
||
logger.info(`calculateManufacturingIPI - QoQ: ${qoqChange ? qoqChange.toFixed(2) + '%' : 'N/A'}`);
|
||
logger.info(`calculateManufacturingIPI - YoY: ${yoyChange ? yoyChange.toFixed(2) + '%' : 'N/A'}`);
|
||
|
||
return {
|
||
success: true,
|
||
index: ipiData.manufacturing_index,
|
||
qoqChange,
|
||
momChange: qoqChange,
|
||
yoyChange
|
||
};
|
||
|
||
} catch (error) {
|
||
await connection.rollback();
|
||
console.error('Error calculating Manufacturing IPI:', error);
|
||
logger.error(`calculateManufacturingIPI - Error calculating Manufacturing IPI: ${error}`);
|
||
// Update log OUTSIDE the failed transaction
|
||
if (logId) {
|
||
await this.logRecord('manufacturing_ipi', year, quarter, 'Failed', logId, 0, error.message);
|
||
}
|
||
throw error;
|
||
} finally {
|
||
connection.release();
|
||
}
|
||
}
|
||
|
||
|
||
// MASTER FUNCTION: Run Complete Calculation Pipeline
|
||
async runCompleteCalculation(year, quarter) {
|
||
|
||
logger.info(`**************************** Starting IPI Calculation for ${year}-${quarter} ****************************`);
|
||
|
||
try {
|
||
// Step 1: Aggregate quarterly production
|
||
await this.aggregateQuarterlyProduction(year, quarter);
|
||
|
||
// Step 2: Calculate item level indices
|
||
await this.calculateItemLevelIndices(year, quarter);
|
||
|
||
// Step 3: Calculate ISIC 4-digit indices
|
||
await this.calculateISIC4DigitIndices(year, quarter);
|
||
|
||
// Step 4: Calculate ISIC 3-digit indices
|
||
await this.calculateISIC3DigitIndices(year, quarter);
|
||
|
||
// Step 5: Calculate ISIC 2-digit indices
|
||
await this.calculateISIC2DigitIndices(year, quarter);
|
||
|
||
// Step 6: Calculate Manufacturing IPI
|
||
const result = await this.calculateManufacturingIPI(year, quarter);
|
||
|
||
logger.info(`**************************** IPI Calculation Completed Successfully ****************************`);
|
||
|
||
|
||
return result;
|
||
|
||
} catch (error) {
|
||
console.error('Error in complete calculation:', error);
|
||
logger.error(` Error in complete calculation: ${error}`);
|
||
throw error;
|
||
}
|
||
}
|
||
|
||
|
||
// HELPER : calculation log
|
||
async logRecord(calculation_type, reference_year, reference_month = null, status = 'Started', logId = null, records_processed = 0, error_message = null, useConnection = null) {
|
||
|
||
const connection = useConnection || await this.pool.getConnection();
|
||
try {
|
||
if (!logId) {
|
||
const [result] = await connection.query(
|
||
`INSERT INTO calculation_log (calculation_type, reference_year, reference_month, status)
|
||
VALUES (?, ?, ?, ?)`,
|
||
[calculation_type, reference_year, reference_month, status]
|
||
);
|
||
return result.insertId;
|
||
}
|
||
|
||
if(records_processed == 0)
|
||
{
|
||
status = 'Failed';
|
||
}
|
||
|
||
await connection.query(
|
||
`UPDATE calculation_log
|
||
SET status=?, records_processed=?, error_message=?, completed_at=NOW()
|
||
WHERE id=?`,
|
||
[status, records_processed, error_message?.substring(0,65535) || null, logId]
|
||
);
|
||
|
||
} catch (err) {
|
||
console.error('[LOG ERROR]', err.message);
|
||
logger.error(` Log record error: ${err.message} `);
|
||
} finally {
|
||
if (!useConnection) connection.release();
|
||
}
|
||
}
|
||
|
||
|
||
// HELPER: Verify Base Year Data
|
||
async verifyBaseYearData(baseYear = 2022) {
|
||
const connection = await this.pool.getConnection();
|
||
|
||
try {
|
||
|
||
logger.info(` Verifying Base Year ${baseYear} Data...`);
|
||
|
||
// Count products
|
||
const [countResult] = await connection.query(
|
||
`SELECT COUNT(*) as count FROM base_year_production WHERE base_year = ?`,
|
||
[baseYear]
|
||
);
|
||
|
||
// Get sample data
|
||
const [sampleData] = await connection.query(
|
||
`SELECT
|
||
product_id,
|
||
product_hs_code,
|
||
avg_by_production,
|
||
q1, q2, q3, q4
|
||
FROM base_year_production
|
||
WHERE base_year = ?
|
||
ORDER BY product_id
|
||
LIMIT 5`,
|
||
[baseYear]
|
||
);
|
||
|
||
// Check for products with zero average
|
||
const [zeroAvgResult] = await connection.query(
|
||
`SELECT COUNT(*) as count
|
||
FROM base_year_production
|
||
WHERE base_year = ? AND (avg_by_production = 0 OR avg_by_production IS NULL)`,
|
||
[baseYear]
|
||
);
|
||
|
||
logger.info(` Total products in base year: ${countResult[0].count}`);
|
||
logger.info(` Products with zero/null average: ${zeroAvgResult[0].count}`);
|
||
logger.info(` Sample data (first 5 products): ${sampleData} `);
|
||
|
||
|
||
return {
|
||
totalProducts: countResult[0].count,
|
||
zeroAverageProducts: zeroAvgResult[0].count,
|
||
sampleData: sampleData
|
||
};
|
||
|
||
} finally {
|
||
connection.release();
|
||
}
|
||
}
|
||
|
||
|
||
// HELPER: Clear All Calculated Data for a Period
|
||
async clearCalculatedData(year, quarter) {
|
||
const connection = await this.pool.getConnection();
|
||
|
||
try {
|
||
await connection.beginTransaction();
|
||
|
||
logger.info(` Clearing calculated data for ${year}-${quarter} `);
|
||
|
||
await connection.query(
|
||
`DELETE FROM monthly_production WHERE year = ? AND quarter = ?`,
|
||
[year, quarter]
|
||
);
|
||
|
||
await connection.query(
|
||
`DELETE FROM item_level_indices WHERE year = ? AND quarter = ?`,
|
||
[year, quarter]
|
||
);
|
||
|
||
await connection.query(
|
||
`DELETE FROM isic_4digit_indices WHERE year = ? AND quarter = ?`,
|
||
[year, quarter]
|
||
);
|
||
|
||
await connection.query(
|
||
`DELETE FROM isic_3digit_indices WHERE year = ? AND quarter = ?`,
|
||
[year, quarter]
|
||
);
|
||
|
||
await connection.query(
|
||
`DELETE FROM isic_2digit_indices WHERE year = ? AND quarter = ?`,
|
||
[year, quarter]
|
||
);
|
||
|
||
await connection.query(
|
||
`DELETE FROM manufacturing_ipi WHERE year = ? AND quarter = ?`,
|
||
[year, quarter]
|
||
);
|
||
|
||
await connection.commit();
|
||
logger.info(` Data cleared for ${year}-${quarter} `);
|
||
|
||
return { success: true };
|
||
|
||
} catch (error) {
|
||
await connection.rollback();
|
||
console.error('Error clearing data:', error);
|
||
logger.error(` Error clearing data: ${error} `);
|
||
throw error;
|
||
} finally {
|
||
connection.release();
|
||
}
|
||
}
|
||
|
||
// Close pool
|
||
async close() {
|
||
await this.pool.end();
|
||
}
|
||
|
||
}
|
||
|
||
|
||
|
||
|
||
module.exports = IPICalculationService; |