483 lines
13 KiB
JavaScript
483 lines
13 KiB
JavaScript
const ExcelJS = require("exceljs");
|
|
const db = require("../models");
|
|
const { Op } = require("sequelize");
|
|
|
|
const Submission = db.Submission;
|
|
const SubmissionProduct = db.SubmissionProduct;
|
|
const Establishment = db.Establishment;
|
|
const Product = db.Product;
|
|
const UnitMaster = db.UnitMaster;
|
|
|
|
const QUARTERS = ["Q1", "Q2", "Q3", "Q4"];
|
|
const BASE_YEAR = Number(process.env.BASE_YEAR) || 2022;
|
|
|
|
const parseYears = (yearsParam) => {
|
|
if (!yearsParam) return null;
|
|
const years = String(yearsParam)
|
|
.split(",")
|
|
.map((y) => parseInt(y.trim(), 10))
|
|
.filter((y) => !Number.isNaN(y));
|
|
return years.length ? years : null;
|
|
};
|
|
|
|
const parseStatuses = (statusParam) => {
|
|
if (!statusParam) return ["Approved", "Submitted", "Resubmitted"];
|
|
return String(statusParam)
|
|
.split(",")
|
|
.map((s) => s.trim())
|
|
.filter(Boolean);
|
|
};
|
|
|
|
const parseNumber = (value) => {
|
|
if (value === null || value === undefined || value === "") return null;
|
|
const num = Number(value);
|
|
return Number.isNaN(num) ? null : num;
|
|
};
|
|
|
|
const sanitizeUom = (uom) => {
|
|
if (uom === null || uom === undefined) return "";
|
|
const trimmed = String(uom).trim();
|
|
if (!trimmed || trimmed.toLowerCase() === "not defined") return "";
|
|
return trimmed;
|
|
};
|
|
|
|
const joinUnique = (set) =>
|
|
[...set].map(sanitizeUom).filter(Boolean).sort().join(", ");
|
|
|
|
const hasPeriodData = (cell) => {
|
|
if (!cell) return false;
|
|
return cell.quantity !== null || cell.value !== null;
|
|
};
|
|
|
|
const addPeriodValues = (target, source) => {
|
|
if (!source) return;
|
|
if (source.quantity !== null) {
|
|
target.quantity = (target.quantity ?? 0) + source.quantity;
|
|
}
|
|
if (source.value !== null) {
|
|
target.value = (target.value ?? 0) + source.value;
|
|
}
|
|
};
|
|
|
|
const aggregateRowsByHsCode = (rawRows) => {
|
|
const hsMap = new Map();
|
|
|
|
for (const row of rawRows) {
|
|
const hsKey = String(row.hs_code);
|
|
if (!hsMap.has(hsKey)) {
|
|
hsMap.set(hsKey, {
|
|
hs_code: row.hs_code,
|
|
establishments: new Set(),
|
|
units: new Set(),
|
|
periods: {},
|
|
});
|
|
}
|
|
|
|
const entry = hsMap.get(hsKey);
|
|
const rowHasData = Object.values(row.periods).some(hasPeriodData);
|
|
if (row.establishment && rowHasData) entry.establishments.add(row.establishment);
|
|
if (row.unit && rowHasData) entry.units.add(row.unit);
|
|
|
|
for (const [periodKey, cell] of Object.entries(row.periods)) {
|
|
if (!hasPeriodData(cell)) continue;
|
|
if (!entry.periods[periodKey]) {
|
|
entry.periods[periodKey] = { quantity: null, value: null };
|
|
}
|
|
addPeriodValues(entry.periods[periodKey], cell);
|
|
}
|
|
}
|
|
|
|
return [...hsMap.values()]
|
|
.map((entry) => ({
|
|
establishment: joinUnique(entry.establishments),
|
|
hs_code: entry.hs_code,
|
|
unit: joinUnique(entry.units),
|
|
periods: entry.periods,
|
|
}))
|
|
.sort((a, b) => Number(a.hs_code) - Number(b.hs_code));
|
|
};
|
|
|
|
const yearHasData = (year, rows) => {
|
|
const suffix = `-${year}`;
|
|
return rows.some((row) =>
|
|
Object.entries(row.periods).some(
|
|
([key, cell]) => key.endsWith(suffix) && hasPeriodData(cell)
|
|
)
|
|
);
|
|
};
|
|
|
|
const resolveExportYears = (discoveredYears, rows, requestedYears) => {
|
|
const yearsWithData = new Set();
|
|
|
|
for (const year of discoveredYears) {
|
|
if (yearHasData(year, rows)) yearsWithData.add(year);
|
|
}
|
|
|
|
if (requestedYears?.length) {
|
|
for (const year of requestedYears) {
|
|
if (yearHasData(year, rows)) yearsWithData.add(year);
|
|
}
|
|
}
|
|
|
|
yearsWithData.add(BASE_YEAR);
|
|
|
|
const otherYears = [...yearsWithData]
|
|
.filter((y) => y !== BASE_YEAR)
|
|
.sort((a, b) => a - b);
|
|
|
|
return [BASE_YEAR, ...otherYears].filter(
|
|
(year, index, arr) => arr.indexOf(year) === index
|
|
);
|
|
};
|
|
|
|
const buildAnnexIIPeriods = (years, rows) => {
|
|
const periods = [];
|
|
|
|
for (const year of years) {
|
|
if (year === BASE_YEAR) {
|
|
for (const quarter of QUARTERS) {
|
|
periods.push(`${quarter}-${year}`);
|
|
}
|
|
continue;
|
|
}
|
|
|
|
for (const quarter of QUARTERS) {
|
|
const periodKey = `${quarter}-${year}`;
|
|
const hasData = rows.some((row) => hasPeriodData(row.periods[periodKey]));
|
|
if (hasData) periods.push(periodKey);
|
|
}
|
|
}
|
|
|
|
return periods;
|
|
};
|
|
|
|
const buildAnnexIIYearPeriods = (years, rows) => {
|
|
const result = [];
|
|
for (const year of years) {
|
|
if (year === BASE_YEAR) {
|
|
result.push({ year, quarters: [...QUARTERS] });
|
|
continue;
|
|
}
|
|
const quarters = QUARTERS.filter((q) =>
|
|
rows.some((row) => hasPeriodData(row.periods[`${q}-${year}`]))
|
|
);
|
|
if (quarters.length) result.push({ year, quarters });
|
|
}
|
|
return result;
|
|
};
|
|
|
|
const styleHeaderRow = (row) => {
|
|
row.eachCell((cell) => {
|
|
cell.font = { bold: true };
|
|
cell.alignment = { horizontal: "center", vertical: "middle", wrapText: true };
|
|
cell.border = {
|
|
top: { style: "thin" },
|
|
left: { style: "thin" },
|
|
bottom: { style: "thin" },
|
|
right: { style: "thin" },
|
|
};
|
|
});
|
|
};
|
|
|
|
const setExcelResponseHeaders = (res, filename) => {
|
|
res.setHeader("X-Frame-Options", "DENY");
|
|
res.setHeader("X-Content-Type-Options", "nosniff");
|
|
res.setHeader(
|
|
"Content-Type",
|
|
"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
|
|
);
|
|
res.setHeader("Content-Disposition", `attachment; filename=${filename}`);
|
|
};
|
|
|
|
const fetchExportRows = async ({ years, statuses, emirate_id, establishment_id }) => {
|
|
const submissionWhere = {
|
|
status: { [Op.in]: statuses },
|
|
};
|
|
|
|
if (years?.length) {
|
|
submissionWhere.year = { [Op.in]: years };
|
|
}
|
|
|
|
const establishmentWhere = {};
|
|
if (emirate_id) {
|
|
establishmentWhere.establishment_emirate_id = emirate_id;
|
|
}
|
|
if (establishment_id) {
|
|
establishmentWhere.id = establishment_id;
|
|
}
|
|
|
|
const submissions = await Submission.findAll({
|
|
where: submissionWhere,
|
|
include: [
|
|
{
|
|
model: Establishment,
|
|
as: "establishment",
|
|
attributes: ["id", "establishment_code", "factory_name"],
|
|
where: Object.keys(establishmentWhere).length ? establishmentWhere : undefined,
|
|
},
|
|
{
|
|
model: SubmissionProduct,
|
|
as: "products",
|
|
where: { is_active: true },
|
|
required: true,
|
|
include: [
|
|
{
|
|
model: Product,
|
|
as: "product",
|
|
attributes: ["id", "hs_code", "product_name", "unit_id"],
|
|
include: [
|
|
{
|
|
model: UnitMaster,
|
|
as: "unit",
|
|
attributes: ["uom"],
|
|
},
|
|
],
|
|
},
|
|
],
|
|
},
|
|
],
|
|
order: [
|
|
["year", "ASC"],
|
|
["quarter", "ASC"],
|
|
["id", "DESC"],
|
|
],
|
|
});
|
|
|
|
const rawRows = [];
|
|
const discoveredYears = new Set();
|
|
|
|
for (const submission of submissions) {
|
|
const establishmentCode =
|
|
submission.establishment?.establishment_code ||
|
|
submission.establishment?.factory_name ||
|
|
`EST-${submission.establishment_id}`;
|
|
|
|
const periodKey = `${submission.quarter}-${submission.year}`;
|
|
discoveredYears.add(submission.year);
|
|
|
|
for (const sp of submission.products) {
|
|
const hsCode = sp.product?.hs_code;
|
|
if (!hsCode) continue;
|
|
|
|
const unit = sanitizeUom(sp.product?.unit?.uom);
|
|
rawRows.push({
|
|
establishment: establishmentCode,
|
|
hs_code: hsCode,
|
|
unit,
|
|
periods: {
|
|
[periodKey]: {
|
|
quantity: parseNumber(sp.current_quantity),
|
|
value: parseNumber(sp.current_cost),
|
|
},
|
|
},
|
|
});
|
|
}
|
|
}
|
|
|
|
const rows = aggregateRowsByHsCode(rawRows);
|
|
const resolvedYears = resolveExportYears(
|
|
[...discoveredYears],
|
|
rows,
|
|
years
|
|
);
|
|
|
|
return { rows, years: resolvedYears };
|
|
};
|
|
|
|
const buildAnnexIIWorkbook = (rows, years) => {
|
|
const workbook = new ExcelJS.Workbook();
|
|
const worksheet = workbook.addWorksheet("Sheet1");
|
|
const periods = buildAnnexIIPeriods(years, rows);
|
|
|
|
const headerRow1 = ["Establishment", "Product \n(HS 8Digit)", "Unit"];
|
|
const headerRow2 = ["", "", ""];
|
|
|
|
for (const period of periods) {
|
|
headerRow1.push(period);
|
|
headerRow1.push("");
|
|
headerRow2.push("Quantity", "Value");
|
|
}
|
|
|
|
worksheet.addRow(headerRow1);
|
|
worksheet.addRow(headerRow2);
|
|
|
|
worksheet.mergeCells(1, 1, 2, 1);
|
|
worksheet.mergeCells(1, 2, 2, 2);
|
|
worksheet.mergeCells(1, 3, 2, 3);
|
|
|
|
let colIndex = 4;
|
|
for (const period of periods) {
|
|
worksheet.mergeCells(1, colIndex, 1, colIndex + 1);
|
|
worksheet.getCell(1, colIndex).value = period;
|
|
colIndex += 2;
|
|
}
|
|
|
|
styleHeaderRow(worksheet.getRow(1));
|
|
styleHeaderRow(worksheet.getRow(2));
|
|
|
|
worksheet.getColumn(1).width = 24;
|
|
worksheet.getColumn(2).width = 18;
|
|
worksheet.getColumn(3).width = 20;
|
|
|
|
for (const row of rows) {
|
|
const values = [row.establishment, row.hs_code, row.unit];
|
|
for (const period of periods) {
|
|
const cell = row.periods[period] || {};
|
|
values.push(cell.quantity ?? null, cell.value ?? null);
|
|
}
|
|
const excelRow = worksheet.addRow(values);
|
|
for (let c = 4; c < values.length; c += 2) {
|
|
const qtyCell = excelRow.getCell(c);
|
|
const valCell = excelRow.getCell(c + 1);
|
|
if (qtyCell.value !== null) qtyCell.numFmt = "#,##0.##";
|
|
if (valCell.value !== null) valCell.numFmt = "#,##0.##";
|
|
}
|
|
}
|
|
|
|
return workbook;
|
|
};
|
|
|
|
const buildAnnexIIIColumns = (years, rows) => {
|
|
const columns = [];
|
|
for (const year of years) {
|
|
const quarters =
|
|
year === BASE_YEAR
|
|
? [...QUARTERS]
|
|
: QUARTERS.filter((q) =>
|
|
rows.some((row) => hasPeriodData(row.periods[`${q}-${year}`]))
|
|
);
|
|
for (const quarter of quarters) {
|
|
columns.push({
|
|
year,
|
|
quarter,
|
|
periodKey: `${quarter}-${year}`,
|
|
});
|
|
}
|
|
}
|
|
return columns;
|
|
};
|
|
|
|
const buildAnnexIIIWorkbook = (rows, years, metric) => {
|
|
const workbook = new ExcelJS.Workbook();
|
|
const worksheet = workbook.addWorksheet("Sheet1");
|
|
const isQuantity = metric === "quantity";
|
|
const columns = buildAnnexIIIColumns(years, rows);
|
|
|
|
const yearRow = ["Establishment ", "Product(HS-8D)", "Unit"];
|
|
const quarterRow = ["", "", ""];
|
|
|
|
for (const col of columns) {
|
|
yearRow.push("");
|
|
quarterRow.push(col.quarter);
|
|
}
|
|
|
|
worksheet.addRow(yearRow);
|
|
worksheet.addRow(quarterRow);
|
|
|
|
worksheet.mergeCells(1, 1, 2, 1);
|
|
worksheet.mergeCells(1, 2, 2, 2);
|
|
worksheet.mergeCells(1, 3, 2, 3);
|
|
|
|
let colIndex = 4;
|
|
let i = 0;
|
|
while (i < columns.length) {
|
|
const year = columns[i].year;
|
|
const yearLabel =
|
|
year === BASE_YEAR ? `BASE YEAR ${year}` : `AND ${year}`;
|
|
let j = i;
|
|
while (j < columns.length && columns[j].year === year) j++;
|
|
const span = j - i;
|
|
|
|
worksheet.getCell(1, colIndex).value = yearLabel;
|
|
if (span > 1) {
|
|
worksheet.mergeCells(1, colIndex, 1, colIndex + span - 1);
|
|
}
|
|
|
|
colIndex += span;
|
|
i = j;
|
|
}
|
|
|
|
styleHeaderRow(worksheet.getRow(1));
|
|
styleHeaderRow(worksheet.getRow(2));
|
|
|
|
worksheet.getColumn(1).width = 24;
|
|
worksheet.getColumn(2).width = 18;
|
|
worksheet.getColumn(3).width = 20;
|
|
for (let c = 4; c < 4 + columns.length; c++) {
|
|
worksheet.getColumn(c).width = 12;
|
|
}
|
|
|
|
for (const row of rows) {
|
|
const values = [
|
|
row.establishment,
|
|
row.hs_code,
|
|
isQuantity ? row.unit : "AED",
|
|
];
|
|
|
|
for (const col of columns) {
|
|
const cell = row.periods[col.periodKey] || {};
|
|
values.push(isQuantity ? (cell.quantity ?? null) : (cell.value ?? null));
|
|
}
|
|
|
|
const excelRow = worksheet.addRow(values);
|
|
for (let c = 4; c < 4 + columns.length; c++) {
|
|
const dataCell = excelRow.getCell(c);
|
|
if (dataCell.value !== null) dataCell.numFmt = "#,##0.##";
|
|
}
|
|
}
|
|
|
|
return workbook;
|
|
};
|
|
|
|
const exportAnnex = async (res, { type, years, statuses, emirate_id, establishment_id }) => {
|
|
const parsedYears = parseYears(years);
|
|
const parsedStatuses = parseStatuses(statuses);
|
|
|
|
const { rows, years: resolvedYears } = await fetchExportRows({
|
|
years: parsedYears,
|
|
statuses: parsedStatuses,
|
|
emirate_id,
|
|
establishment_id,
|
|
});
|
|
|
|
if (!rows.length) {
|
|
const err = new Error("No submission data found for the given filters");
|
|
err.statusCode = 404;
|
|
throw err;
|
|
}
|
|
|
|
let workbook;
|
|
let filename;
|
|
|
|
if (type === "annex_ii") {
|
|
workbook = buildAnnexIIWorkbook(rows, resolvedYears);
|
|
filename = `Annex_II_Establishment_Quarterly_${Date.now()}.xlsx`;
|
|
} else if (type === "annex_iii_quantity") {
|
|
workbook = buildAnnexIIIWorkbook(rows, resolvedYears, "quantity");
|
|
filename = `Annex_III_Item_Level_Quantity_${Date.now()}.xlsx`;
|
|
} else if (type === "annex_iii_values") {
|
|
workbook = buildAnnexIIIWorkbook(rows, resolvedYears, "value");
|
|
filename = `Annex_III_Item_Level_Values_${Date.now()}.xlsx`;
|
|
} else {
|
|
const err = new Error("Invalid export type");
|
|
err.statusCode = 400;
|
|
throw err;
|
|
}
|
|
|
|
setExcelResponseHeaders(res, filename);
|
|
await workbook.xlsx.write(res);
|
|
res.end();
|
|
};
|
|
|
|
module.exports = {
|
|
exportAnnex,
|
|
parseYears,
|
|
parseStatuses,
|
|
fetchExportRows,
|
|
aggregateRowsByHsCode,
|
|
resolveExportYears,
|
|
buildAnnexIIWorkbook,
|
|
buildAnnexIIIWorkbook,
|
|
BASE_YEAR,
|
|
};
|