795 lines
28 KiB
TypeScript
795 lines
28 KiB
TypeScript
import * as ExcelJS from 'exceljs';
|
|
import * as FileSaver from 'file-saver';
|
|
|
|
interface ReportData {
|
|
id: number;
|
|
createdDate: string;
|
|
shift: string;
|
|
area: { name: string };
|
|
dredgerLocation: { name: string };
|
|
dredgerLineLength: number;
|
|
reclamationLocation: { name: string };
|
|
shoreConnection: number;
|
|
reclamationHeight: { base: number; extra: number };
|
|
pipelineLength: { main: number; ext1: number; reserve: number; ext2: number };
|
|
stats: { Dozers: number; Exc: number; Loaders: number; Foreman: string; Laborer: number };
|
|
timeSheet: Array<{
|
|
machine: string;
|
|
from1: string;
|
|
to1: string;
|
|
from2: string;
|
|
to2: string;
|
|
total: string;
|
|
reason: string;
|
|
}>;
|
|
stoppages: Array<{
|
|
from: string;
|
|
to: string;
|
|
total: string;
|
|
reason: string;
|
|
responsible: string;
|
|
note: string;
|
|
}>;
|
|
notes: string;
|
|
}
|
|
|
|
interface ReportSheet {
|
|
id: string;
|
|
date: string;
|
|
area: string;
|
|
dredgerLocation: string;
|
|
reclamationLocation: string;
|
|
dayReport?: ReportData;
|
|
nightReport?: ReportData;
|
|
}
|
|
|
|
// Function to add logo images to worksheet
|
|
async function addLogosToWorksheet(worksheet: ExcelJS.Worksheet, currentRow: number) {
|
|
try {
|
|
// Try to add company logo (left side)
|
|
try {
|
|
const logoResponse = await fetch('/logo03.png');
|
|
if (logoResponse.ok) {
|
|
const logoBuffer = await logoResponse.arrayBuffer();
|
|
const logoImageId = worksheet.workbook.addImage({
|
|
buffer: logoBuffer,
|
|
extension: 'png',
|
|
});
|
|
worksheet.addImage(logoImageId, {
|
|
tl: { col: 0, row: currentRow - 1 },
|
|
ext: { width: 100, height: 60 }
|
|
});
|
|
}
|
|
} catch (error) {
|
|
console.log('Company logo not found, using text placeholder');
|
|
}
|
|
|
|
// Try to add Arab Potash logo (right side)
|
|
try {
|
|
const arabPotashResponse = await fetch('/logo-light.png');
|
|
if (arabPotashResponse.ok) {
|
|
const arabPotashBuffer = await arabPotashResponse.arrayBuffer();
|
|
const arabPotashImageId = worksheet.workbook.addImage({
|
|
buffer: arabPotashBuffer,
|
|
extension: 'png',
|
|
});
|
|
worksheet.addImage(arabPotashImageId, {
|
|
tl: { col: 3, row: currentRow - 1 },
|
|
ext: { width: 100, height: 60 }
|
|
});
|
|
}
|
|
} catch (error) {
|
|
console.log('Arab Potash logo not found, using text placeholder');
|
|
}
|
|
} catch (error) {
|
|
console.log('Error loading images:', error);
|
|
}
|
|
}
|
|
|
|
export async function exportReportToExcel(reportOrSheet: ReportData | ReportSheet) {
|
|
const workbook = new ExcelJS.Workbook();
|
|
const worksheet = workbook.addWorksheet('Report');
|
|
|
|
// Set RTL (Right-to-Left) layout
|
|
worksheet.views = [{ rightToLeft: false }];
|
|
|
|
// Set column widths to match the report view layout exactly
|
|
worksheet.columns = [
|
|
{ width: 25 }, // A - First column (25% width)
|
|
{ width: 25 }, // B - Second column (25% width)
|
|
{ width: 25 }, // C - Third column (25% width)
|
|
{ width: 25 }, // D - Fourth column (25% width)
|
|
];
|
|
|
|
let currentRow = 1;
|
|
|
|
// Check if this is a ReportSheet (combined day/night) or single Report
|
|
const isReportSheet = 'dayReport' in reportOrSheet || 'nightReport' in reportOrSheet;
|
|
|
|
if (isReportSheet) {
|
|
await exportReportSheet(worksheet, reportOrSheet as ReportSheet, currentRow);
|
|
} else {
|
|
await exportSingleReport(worksheet, reportOrSheet as ReportData, currentRow);
|
|
}
|
|
|
|
// Generate and save file
|
|
const buffer = await workbook.xlsx.writeBuffer();
|
|
const blob = new Blob([buffer], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
|
|
|
|
let fileName: string;
|
|
if (isReportSheet) {
|
|
const sheet = reportOrSheet as ReportSheet;
|
|
fileName = `ReportSheet_${sheet.id}_${new Date(sheet.date).toLocaleDateString('en-GB').replace(/\//g, '-')}.xlsx`;
|
|
} else {
|
|
const report = reportOrSheet as ReportData;
|
|
fileName = `Report_${report.id}_${new Date(report.createdDate).toLocaleDateString('en-GB').replace(/\//g, '-')}.xlsx`;
|
|
}
|
|
|
|
FileSaver.saveAs(blob, fileName);
|
|
}
|
|
|
|
async function exportReportSheet(worksheet: ExcelJS.Worksheet, sheet: ReportSheet, startRow: number) {
|
|
let currentRow = startRow;
|
|
|
|
// Add logos
|
|
await addLogosToWorksheet(worksheet, currentRow);
|
|
|
|
// 1. HEADER SECTION
|
|
currentRow = await addReportSheetHeader(worksheet, sheet, currentRow);
|
|
|
|
// 2. REPORT INFO SECTION
|
|
currentRow = await addReportSheetInfo(worksheet, sheet, currentRow);
|
|
|
|
// 3. DREDGER SECTION
|
|
currentRow = await addDredgerSection(worksheet, sheet.area, currentRow);
|
|
|
|
// 4. LOCATION DATA SECTION
|
|
const report = sheet.dayReport || sheet.nightReport;
|
|
if (report) {
|
|
currentRow = await addLocationData(worksheet, report, currentRow);
|
|
currentRow = await addPipelineLength(worksheet, report, currentRow);
|
|
}
|
|
|
|
// 5. DAY SHIFT SECTION
|
|
if (sheet.dayReport) {
|
|
currentRow = await addShiftSection(worksheet, sheet.dayReport, 'Day', currentRow);
|
|
}
|
|
|
|
// 6. NIGHT SHIFT SECTION
|
|
if (sheet.nightReport) {
|
|
currentRow = await addShiftSection(worksheet, sheet.nightReport, 'Night', currentRow);
|
|
}
|
|
|
|
// 7. FOOTER
|
|
await addFooter(worksheet, currentRow);
|
|
}
|
|
|
|
async function exportSingleReport(worksheet: ExcelJS.Worksheet, report: ReportData, startRow: number) {
|
|
let currentRow = startRow;
|
|
|
|
// Add logos
|
|
await addLogosToWorksheet(worksheet, currentRow);
|
|
|
|
// 1. HEADER SECTION
|
|
currentRow = await addSingleReportHeader(worksheet, report, currentRow);
|
|
|
|
// 2. REPORT INFO SECTION
|
|
currentRow = await addSingleReportInfo(worksheet, report, currentRow);
|
|
|
|
// 3. DREDGER SECTION
|
|
currentRow = await addDredgerSection(worksheet, report.area, currentRow);
|
|
|
|
// 4. LOCATION DATA SECTION
|
|
currentRow = await addLocationData(worksheet, report, currentRow);
|
|
|
|
// 5. PIPELINE LENGTH SECTION
|
|
currentRow = await addPipelineLength(worksheet, report, currentRow);
|
|
|
|
// 6. SHIFT SECTION
|
|
currentRow = await addShiftSection(worksheet, report, report.shift, currentRow);
|
|
|
|
// 7. FOOTER
|
|
await addFooter(worksheet, currentRow);
|
|
}
|
|
|
|
// Helper functions for building Excel sections
|
|
async function addReportSheetHeader(worksheet: ExcelJS.Worksheet, sheet: ReportSheet, currentRow: number): Promise<number> {
|
|
// Create the 3-column header layout
|
|
worksheet.mergeCells(`A${currentRow}:A${currentRow + 2}`);
|
|
const logoLeftCell = worksheet.getCell(`A${currentRow}`);
|
|
logoLeftCell.value = '';
|
|
logoLeftCell.style = {
|
|
font: { name: 'Arial', size: 12, bold: true },
|
|
alignment: { horizontal: 'center', vertical: 'middle' },
|
|
border: {
|
|
top: { style: 'thick', color: { argb: '000000' } },
|
|
left: { style: 'thick', color: { argb: '000000' } },
|
|
bottom: { style: 'thick', color: { argb: '000000' } },
|
|
right: { style: 'thick', color: { argb: '000000' } }
|
|
}
|
|
};
|
|
|
|
// Middle section - main header
|
|
worksheet.mergeCells(`B${currentRow}:C${currentRow}`);
|
|
const headerMainCell = worksheet.getCell(`B${currentRow}`);
|
|
headerMainCell.value = 'Reclamation Work Diary';
|
|
headerMainCell.style = {
|
|
font: { name: 'Arial', size: 16, bold: true },
|
|
alignment: { horizontal: 'center', vertical: 'middle' },
|
|
border: {
|
|
top: { style: 'thick', color: { argb: '000000' } },
|
|
left: { style: 'thick', color: { argb: '000000' } },
|
|
bottom: { style: 'thin', color: { argb: '000000' } },
|
|
right: { style: 'thick', color: { argb: '000000' } }
|
|
}
|
|
};
|
|
|
|
// QF code
|
|
worksheet.mergeCells(`B${currentRow + 1}:C${currentRow + 1}`);
|
|
const qfCell = worksheet.getCell(`B${currentRow + 1}`);
|
|
qfCell.value = 'QF-3.6.1-08';
|
|
qfCell.style = {
|
|
font: { name: 'Arial', size: 12 },
|
|
alignment: { horizontal: 'center', vertical: 'middle' },
|
|
border: {
|
|
top: { style: 'thin', color: { argb: '000000' } },
|
|
left: { style: 'thick', color: { argb: '000000' } },
|
|
bottom: { style: 'thin', color: { argb: '000000' } },
|
|
right: { style: 'thick', color: { argb: '000000' } }
|
|
}
|
|
};
|
|
|
|
// Rev code
|
|
worksheet.mergeCells(`B${currentRow + 2}:C${currentRow + 2}`);
|
|
const revCell = worksheet.getCell(`B${currentRow + 2}`);
|
|
revCell.value = 'Rev. 1.0';
|
|
revCell.style = {
|
|
font: { name: 'Arial', size: 12 },
|
|
alignment: { horizontal: 'center', vertical: 'middle' },
|
|
border: {
|
|
top: { style: 'thin', color: { argb: '000000' } },
|
|
left: { style: 'thick', color: { argb: '000000' } },
|
|
bottom: { style: 'thick', color: { argb: '000000' } },
|
|
right: { style: 'thick', color: { argb: '000000' } }
|
|
}
|
|
};
|
|
|
|
// Right logo
|
|
worksheet.mergeCells(`D${currentRow}:D${currentRow + 2}`);
|
|
const logoRightCell = worksheet.getCell(`D${currentRow}`);
|
|
logoRightCell.value = '';
|
|
logoRightCell.style = {
|
|
font: { name: 'Arial', size: 12, bold: true },
|
|
alignment: { horizontal: 'center', vertical: 'middle' },
|
|
border: {
|
|
top: { style: 'thick', color: { argb: '000000' } },
|
|
left: { style: 'thick', color: { argb: '000000' } },
|
|
bottom: { style: 'thick', color: { argb: '000000' } },
|
|
right: { style: 'thick', color: { argb: '000000' } }
|
|
}
|
|
};
|
|
|
|
return currentRow + 4;
|
|
}
|
|
|
|
async function addSingleReportHeader(worksheet: ExcelJS.Worksheet, report: ReportData, currentRow: number): Promise<number> {
|
|
return await addReportSheetHeader(worksheet, { area: report.area.name } as ReportSheet, currentRow);
|
|
}
|
|
|
|
async function addReportSheetInfo(worksheet: ExcelJS.Worksheet, sheet: ReportSheet, currentRow: number): Promise<number> {
|
|
const dateCell = worksheet.getCell(`A${currentRow}`);
|
|
dateCell.value = 'Date:';
|
|
dateCell.style = {
|
|
font: { name: 'Arial', size: 12, bold: true },
|
|
alignment: { horizontal: 'left', vertical: 'middle' },
|
|
border: {
|
|
top: { style: 'thick', color: { argb: '000000' } },
|
|
left: { style: 'thick', color: { argb: '000000' } },
|
|
bottom: { style: 'thick', color: { argb: '000000' } },
|
|
right: { style: 'thick', color: { argb: '000000' } }
|
|
}
|
|
};
|
|
|
|
const dateValueCell = worksheet.getCell(`B${currentRow}`);
|
|
dateValueCell.value = new Date(sheet.date).toLocaleDateString('en-GB');
|
|
dateValueCell.style = {
|
|
font: { name: 'Arial', size: 12 },
|
|
alignment: { horizontal: 'left', vertical: 'middle' },
|
|
border: {
|
|
top: { style: 'thick', color: { argb: '000000' } },
|
|
left: { style: 'thick', color: { argb: '000000' } },
|
|
bottom: { style: 'thick', color: { argb: '000000' } },
|
|
right: { style: 'thick', color: { argb: '000000' } }
|
|
}
|
|
};
|
|
|
|
const reportNoCell = worksheet.getCell(`C${currentRow}`);
|
|
reportNoCell.value = 'Report No.';
|
|
reportNoCell.style = {
|
|
font: { name: 'Arial', size: 12, bold: true },
|
|
alignment: { horizontal: 'left', vertical: 'middle' },
|
|
border: {
|
|
top: { style: 'thick', color: { argb: '000000' } },
|
|
left: { style: 'thick', color: { argb: '000000' } },
|
|
bottom: { style: 'thick', color: { argb: '000000' } },
|
|
right: { style: 'thick', color: { argb: '000000' } }
|
|
}
|
|
};
|
|
|
|
const reportNoValueCell = worksheet.getCell(`D${currentRow}`);
|
|
reportNoValueCell.value = sheet.id.toString();
|
|
reportNoValueCell.style = {
|
|
font: { name: 'Arial', size: 12 },
|
|
alignment: { horizontal: 'left', vertical: 'middle' },
|
|
border: {
|
|
top: { style: 'thick', color: { argb: '000000' } },
|
|
left: { style: 'thick', color: { argb: '000000' } },
|
|
bottom: { style: 'thick', color: { argb: '000000' } },
|
|
right: { style: 'thick', color: { argb: '000000' } }
|
|
}
|
|
};
|
|
|
|
return currentRow + 2;
|
|
}
|
|
|
|
async function addSingleReportInfo(worksheet: ExcelJS.Worksheet, report: ReportData, currentRow: number): Promise<number> {
|
|
const dateCell = worksheet.getCell(`A${currentRow}`);
|
|
dateCell.value = 'Date:';
|
|
dateCell.style = {
|
|
font: { name: 'Arial', size: 12, bold: true },
|
|
alignment: { horizontal: 'left', vertical: 'middle' },
|
|
border: {
|
|
top: { style: 'thick', color: { argb: '000000' } },
|
|
left: { style: 'thick', color: { argb: '000000' } },
|
|
bottom: { style: 'thick', color: { argb: '000000' } },
|
|
right: { style: 'thick', color: { argb: '000000' } }
|
|
}
|
|
};
|
|
|
|
const dateValueCell = worksheet.getCell(`B${currentRow}`);
|
|
dateValueCell.value = new Date(report.createdDate).toLocaleDateString('en-GB');
|
|
dateValueCell.style = {
|
|
font: { name: 'Arial', size: 12 },
|
|
alignment: { horizontal: 'left', vertical: 'middle' },
|
|
border: {
|
|
top: { style: 'thick', color: { argb: '000000' } },
|
|
left: { style: 'thick', color: { argb: '000000' } },
|
|
bottom: { style: 'thick', color: { argb: '000000' } },
|
|
right: { style: 'thick', color: { argb: '000000' } }
|
|
}
|
|
};
|
|
|
|
const shiftNoCell = worksheet.getCell(`C${currentRow}`);
|
|
shiftNoCell.value = 'Shift No.';
|
|
shiftNoCell.style = {
|
|
font: { name: 'Arial', size: 12, bold: true },
|
|
alignment: { horizontal: 'left', vertical: 'middle' },
|
|
border: {
|
|
top: { style: 'thick', color: { argb: '000000' } },
|
|
left: { style: 'thick', color: { argb: '000000' } },
|
|
bottom: { style: 'thick', color: { argb: '000000' } },
|
|
right: { style: 'thick', color: { argb: '000000' } }
|
|
}
|
|
};
|
|
|
|
const shiftNoValueCell = worksheet.getCell(`D${currentRow}`);
|
|
shiftNoValueCell.value = report.id.toString();
|
|
shiftNoValueCell.style = {
|
|
font: { name: 'Arial', size: 12 },
|
|
alignment: { horizontal: 'left', vertical: 'middle' },
|
|
border: {
|
|
top: { style: 'thick', color: { argb: '000000' } },
|
|
left: { style: 'thick', color: { argb: '000000' } },
|
|
bottom: { style: 'thick', color: { argb: '000000' } },
|
|
right: { style: 'thick', color: { argb: '000000' } }
|
|
}
|
|
};
|
|
|
|
return currentRow + 2;
|
|
}
|
|
|
|
async function addDredgerSection(worksheet: ExcelJS.Worksheet, area: { name: string } | string, currentRow: number): Promise<number> {
|
|
const areaName = typeof area === 'string' ? area : area.name;
|
|
|
|
worksheet.mergeCells(`A${currentRow}:D${currentRow}`);
|
|
const dredgerCell = worksheet.getCell(`A${currentRow}`);
|
|
dredgerCell.value = `${areaName} Dredger`;
|
|
dredgerCell.style = {
|
|
font: { name: 'Arial', size: 18, bold: true, underline: true },
|
|
alignment: { horizontal: 'center', vertical: 'middle' }
|
|
};
|
|
|
|
return currentRow + 2;
|
|
}
|
|
|
|
async function addLocationData(worksheet: ExcelJS.Worksheet, report: ReportData, currentRow: number): Promise<number> {
|
|
const locationData = [
|
|
['Dredger Location', report.dredgerLocation.name, 'Dredger Line Length', report.dredgerLineLength.toString()],
|
|
['Reclamation Location', report.reclamationLocation.name, 'Shore Connection', report.shoreConnection.toString()],
|
|
['Reclamation Height', `${report.reclamationHeight?.base || 0}m - ${(report.reclamationHeight?.base || 0) + (report.reclamationHeight?.extra || 0)}m`, '', '']
|
|
];
|
|
|
|
locationData.forEach((rowData, index) => {
|
|
const row = currentRow + index;
|
|
|
|
rowData.forEach((cellValue, colIndex) => {
|
|
const cell = worksheet.getCell(row, colIndex + 1);
|
|
cell.value = cellValue;
|
|
|
|
const isGreenHeader = (colIndex === 0 || colIndex === 2) && cellValue !== '';
|
|
cell.style = {
|
|
font: { name: 'Arial', size: 11, bold: isGreenHeader, color: isGreenHeader ? { argb: 'FFFFFF' } : { argb: '000000' } },
|
|
alignment: { horizontal: 'center', vertical: 'middle' },
|
|
fill: isGreenHeader ? { type: 'pattern', pattern: 'solid', fgColor: { argb: '10B981' } } : { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FFFFFF' } },
|
|
border: {
|
|
top: { style: 'thick', color: { argb: '000000' } },
|
|
left: { style: 'thick', color: { argb: '000000' } },
|
|
bottom: { style: 'thick', color: { argb: '000000' } },
|
|
right: { style: 'thick', color: { argb: '000000' } }
|
|
}
|
|
};
|
|
});
|
|
});
|
|
|
|
return currentRow + 4;
|
|
}
|
|
|
|
async function addPipelineLength(worksheet: ExcelJS.Worksheet, report: ReportData, currentRow: number): Promise<number> {
|
|
// Expand to 7 columns for pipeline section
|
|
worksheet.columns = [
|
|
{ width: 30 }, // A - Pipeline header
|
|
{ width: 15 }, // B - Main
|
|
{ width: 15 }, // C - extension
|
|
{ width: 15 }, // D - total
|
|
{ width: 15 }, // E - Reserve
|
|
{ width: 15 }, // F - extension
|
|
{ width: 15 } // G - total
|
|
];
|
|
|
|
// Pipeline header row
|
|
const pipelineHeaderCell = worksheet.getCell(`A${currentRow}`);
|
|
pipelineHeaderCell.value = 'Pipeline Length "from Shore Connection"';
|
|
pipelineHeaderCell.style = {
|
|
font: { name: 'Arial', size: 11, bold: true, color: { argb: 'FFFFFF' } },
|
|
alignment: { horizontal: 'center', vertical: 'middle' },
|
|
fill: { type: 'pattern', pattern: 'solid', fgColor: { argb: '10B981' } },
|
|
border: {
|
|
top: { style: 'thick', color: { argb: '000000' } },
|
|
left: { style: 'thick', color: { argb: '000000' } },
|
|
bottom: { style: 'thick', color: { argb: '000000' } },
|
|
right: { style: 'thick', color: { argb: '000000' } }
|
|
}
|
|
};
|
|
|
|
// Pipeline sub-headers
|
|
const pipelineSubHeaders = ['Main', 'extension', 'total', 'Reserve', 'extension', 'total'];
|
|
pipelineSubHeaders.forEach((header, colIndex) => {
|
|
const cell = worksheet.getCell(currentRow, colIndex + 2);
|
|
cell.value = header;
|
|
cell.style = {
|
|
font: { name: 'Arial', size: 11, bold: true, color: { argb: 'FFFFFF' } },
|
|
alignment: { horizontal: 'center', vertical: 'middle' },
|
|
fill: { type: 'pattern', pattern: 'solid', fgColor: { argb: '10B981' } },
|
|
border: {
|
|
top: { style: 'thick', color: { argb: '000000' } },
|
|
left: { style: 'thick', color: { argb: '000000' } },
|
|
bottom: { style: 'thick', color: { argb: '000000' } },
|
|
right: { style: 'thick', color: { argb: '000000' } }
|
|
}
|
|
};
|
|
});
|
|
|
|
// Pipeline data row
|
|
currentRow++;
|
|
const pipelineDataCell = worksheet.getCell(`A${currentRow}`);
|
|
pipelineDataCell.value = '';
|
|
pipelineDataCell.style = {
|
|
border: {
|
|
top: { style: 'thick', color: { argb: '000000' } },
|
|
left: { style: 'thick', color: { argb: '000000' } },
|
|
bottom: { style: 'thick', color: { argb: '000000' } },
|
|
right: { style: 'thick', color: { argb: '000000' } }
|
|
}
|
|
};
|
|
|
|
const pipelineData = [
|
|
(report.pipelineLength?.main || 0).toString(),
|
|
(report.pipelineLength?.ext1 || 0).toString(),
|
|
((report.pipelineLength?.main || 0) + (report.pipelineLength?.ext1 || 0)).toString(),
|
|
(report.pipelineLength?.reserve || 0).toString(),
|
|
(report.pipelineLength?.ext2 || 0).toString(),
|
|
((report.pipelineLength?.reserve || 0) + (report.pipelineLength?.ext2 || 0)).toString()
|
|
];
|
|
|
|
pipelineData.forEach((data, colIndex) => {
|
|
const cell = worksheet.getCell(currentRow, colIndex + 2);
|
|
cell.value = data;
|
|
cell.style = {
|
|
font: { name: 'Arial', size: 11 },
|
|
alignment: { horizontal: 'center', vertical: 'middle' },
|
|
border: {
|
|
top: { style: 'thick', color: { argb: '000000' } },
|
|
left: { style: 'thick', color: { argb: '000000' } },
|
|
bottom: { style: 'thick', color: { argb: '000000' } },
|
|
right: { style: 'thick', color: { argb: '000000' } }
|
|
}
|
|
};
|
|
});
|
|
|
|
return currentRow + 2;
|
|
}
|
|
|
|
async function addShiftSection(worksheet: ExcelJS.Worksheet, report: ReportData, shiftName: string, currentRow: number): Promise<number> {
|
|
// SHIFT HEADER SECTION
|
|
worksheet.mergeCells(`A${currentRow}:G${currentRow}`);
|
|
const shiftCell = worksheet.getCell(`A${currentRow}`);
|
|
shiftCell.value = `${shiftName.charAt(0).toUpperCase() + shiftName.slice(1)} Shift`;
|
|
shiftCell.style = {
|
|
font: { name: 'Arial', size: 14, bold: true, color: { argb: 'FFFFFF' } },
|
|
alignment: { horizontal: 'center', vertical: 'middle' },
|
|
fill: { type: 'pattern', pattern: 'solid', fgColor: { argb: '10B981' } },
|
|
border: {
|
|
top: { style: 'thick', color: { argb: '000000' } },
|
|
left: { style: 'thick', color: { argb: '000000' } },
|
|
bottom: { style: 'thick', color: { argb: '000000' } },
|
|
right: { style: 'thick', color: { argb: '000000' } }
|
|
}
|
|
};
|
|
|
|
currentRow += 1;
|
|
|
|
// EQUIPMENT STATS SECTION
|
|
const equipmentHeaders = ['Dozers', 'Exc.', 'Loader', 'Foreman', 'Laborer'];
|
|
|
|
// Adjust columns back to 5 for equipment section
|
|
worksheet.columns = [
|
|
{ width: 20 }, // A - Dozers
|
|
{ width: 20 }, // B - Exc.
|
|
{ width: 20 }, // C - Loader
|
|
{ width: 20 }, // D - Foreman
|
|
{ width: 20 } // E - Laborer
|
|
];
|
|
|
|
equipmentHeaders.forEach((header, colIndex) => {
|
|
const cell = worksheet.getCell(currentRow, colIndex + 1);
|
|
cell.value = header;
|
|
cell.style = {
|
|
font: { name: 'Arial', size: 11, bold: true },
|
|
alignment: { horizontal: 'center', vertical: 'middle' },
|
|
border: {
|
|
top: { style: 'thick', color: { argb: '000000' } },
|
|
left: { style: 'thick', color: { argb: '000000' } },
|
|
bottom: { style: 'thick', color: { argb: '000000' } },
|
|
right: { style: 'thick', color: { argb: '000000' } }
|
|
}
|
|
};
|
|
});
|
|
|
|
currentRow++;
|
|
const equipmentData = [
|
|
(report.stats?.Dozers || 0).toString(),
|
|
(report.stats?.Exc || 0).toString(),
|
|
(report.stats?.Loaders || 0).toString(),
|
|
report.stats?.Foreman || '',
|
|
(report.stats?.Laborer || 0).toString()
|
|
];
|
|
|
|
equipmentData.forEach((data, colIndex) => {
|
|
const cell = worksheet.getCell(currentRow, colIndex + 1);
|
|
cell.value = data;
|
|
cell.style = {
|
|
font: { name: 'Arial', size: 11 },
|
|
alignment: { horizontal: 'center', vertical: 'middle' },
|
|
border: {
|
|
top: { style: 'thick', color: { argb: '000000' } },
|
|
left: { style: 'thick', color: { argb: '000000' } },
|
|
bottom: { style: 'thick', color: { argb: '000000' } },
|
|
right: { style: 'thick', color: { argb: '000000' } }
|
|
}
|
|
};
|
|
});
|
|
|
|
currentRow += 2;
|
|
|
|
// TIME SHEET SECTION
|
|
// Expand to 7 columns for time sheet
|
|
worksheet.columns = [
|
|
{ width: 20 }, // A - Time Sheet
|
|
{ width: 15 }, // B - From
|
|
{ width: 15 }, // C - To
|
|
{ width: 15 }, // D - From
|
|
{ width: 15 }, // E - To
|
|
{ width: 15 }, // F - Total
|
|
{ width: 30 } // G - Reason
|
|
];
|
|
|
|
const timeSheetHeaders = ['Time Sheet', 'From', 'To', 'From', 'To', 'Total', 'Reason'];
|
|
|
|
timeSheetHeaders.forEach((header, colIndex) => {
|
|
const cell = worksheet.getCell(currentRow, colIndex + 1);
|
|
cell.value = header;
|
|
cell.style = {
|
|
font: { name: 'Arial', size: 11, bold: true },
|
|
alignment: { horizontal: 'center', vertical: 'middle' },
|
|
border: {
|
|
top: { style: 'thick', color: { argb: '000000' } },
|
|
left: { style: 'thick', color: { argb: '000000' } },
|
|
bottom: { style: 'thick', color: { argb: '000000' } },
|
|
right: { style: 'thick', color: { argb: '000000' } }
|
|
}
|
|
};
|
|
});
|
|
|
|
currentRow++;
|
|
|
|
const timeSheetData = Array.isArray(report.timeSheet) && report.timeSheet.length > 0
|
|
? report.timeSheet
|
|
: [{ machine: 'No time sheet entries', from1: '', to1: '', from2: '', to2: '', total: '', reason: '' }];
|
|
|
|
timeSheetData.forEach((entry) => {
|
|
const rowData = [entry.machine, entry.from1, entry.to1, entry.from2, entry.to2, entry.total, entry.reason];
|
|
rowData.forEach((data, colIndex) => {
|
|
const cell = worksheet.getCell(currentRow, colIndex + 1);
|
|
cell.value = data;
|
|
cell.style = {
|
|
font: { name: 'Arial', size: 10, bold: colIndex === 0 },
|
|
alignment: { horizontal: colIndex === 0 ? 'left' : 'center', vertical: 'middle' },
|
|
border: {
|
|
top: { style: 'thick', color: { argb: '000000' } },
|
|
left: { style: 'thick', color: { argb: '000000' } },
|
|
bottom: { style: 'thick', color: { argb: '000000' } },
|
|
right: { style: 'thick', color: { argb: '000000' } }
|
|
}
|
|
};
|
|
});
|
|
currentRow++;
|
|
});
|
|
|
|
currentRow += 1;
|
|
|
|
// STOPPAGES SECTION
|
|
worksheet.mergeCells(`A${currentRow}:G${currentRow}`);
|
|
const stoppagesHeaderCell = worksheet.getCell(`A${currentRow}`);
|
|
stoppagesHeaderCell.value = 'Dredger Stoppages';
|
|
stoppagesHeaderCell.style = {
|
|
font: { name: 'Arial', size: 14, bold: true, color: { argb: 'FFFFFF' } },
|
|
alignment: { horizontal: 'center', vertical: 'middle' },
|
|
fill: { type: 'pattern', pattern: 'solid', fgColor: { argb: '10B981' } },
|
|
border: {
|
|
top: { style: 'thick', color: { argb: '000000' } },
|
|
left: { style: 'thick', color: { argb: '000000' } },
|
|
bottom: { style: 'thick', color: { argb: '000000' } },
|
|
right: { style: 'thick', color: { argb: '000000' } }
|
|
}
|
|
};
|
|
|
|
currentRow++;
|
|
|
|
// Adjust columns for stoppages (6 columns)
|
|
worksheet.columns = [
|
|
{ width: 15 }, // A - From
|
|
{ width: 15 }, // B - To
|
|
{ width: 15 }, // C - Total
|
|
{ width: 25 }, // D - Reason
|
|
{ width: 20 }, // E - Responsible
|
|
{ width: 30 } // F - Notes
|
|
];
|
|
|
|
const stoppagesHeaders = ['From', 'To', 'Total', 'Reason', 'Responsible', 'Notes'];
|
|
|
|
stoppagesHeaders.forEach((header, colIndex) => {
|
|
const cell = worksheet.getCell(currentRow, colIndex + 1);
|
|
cell.value = header;
|
|
cell.style = {
|
|
font: { name: 'Arial', size: 11, bold: true },
|
|
alignment: { horizontal: 'center', vertical: 'middle' },
|
|
border: {
|
|
top: { style: 'thick', color: { argb: '000000' } },
|
|
left: { style: 'thick', color: { argb: '000000' } },
|
|
bottom: { style: 'thick', color: { argb: '000000' } },
|
|
right: { style: 'thick', color: { argb: '000000' } }
|
|
}
|
|
};
|
|
});
|
|
|
|
currentRow++;
|
|
|
|
const stoppagesData = Array.isArray(report.stoppages) && report.stoppages.length > 0
|
|
? report.stoppages
|
|
: [{ from: 'No stoppages recorded', to: '', total: '', reason: '', responsible: '', note: '' }];
|
|
|
|
stoppagesData.forEach((entry) => {
|
|
const rowData = [entry.from, entry.to, entry.total, entry.reason, entry.responsible, entry.note];
|
|
rowData.forEach((data, colIndex) => {
|
|
const cell = worksheet.getCell(currentRow, colIndex + 1);
|
|
cell.value = data;
|
|
cell.style = {
|
|
font: { name: 'Arial', size: 10 },
|
|
alignment: { horizontal: 'center', vertical: 'middle' },
|
|
border: {
|
|
top: { style: 'thick', color: { argb: '000000' } },
|
|
left: { style: 'thick', color: { argb: '000000' } },
|
|
bottom: { style: 'thick', color: { argb: '000000' } },
|
|
right: { style: 'thick', color: { argb: '000000' } }
|
|
}
|
|
};
|
|
});
|
|
currentRow++;
|
|
});
|
|
|
|
currentRow += 1;
|
|
|
|
// NOTES SECTION
|
|
worksheet.mergeCells(`A${currentRow}:F${currentRow}`);
|
|
const notesHeaderCell = worksheet.getCell(`A${currentRow}`);
|
|
notesHeaderCell.value = 'Notes & Comments';
|
|
notesHeaderCell.style = {
|
|
font: { name: 'Arial', size: 14, bold: true, color: { argb: 'FFFFFF' } },
|
|
alignment: { horizontal: 'center', vertical: 'middle' },
|
|
fill: { type: 'pattern', pattern: 'solid', fgColor: { argb: '10B981' } },
|
|
border: {
|
|
top: { style: 'thick', color: { argb: '000000' } },
|
|
left: { style: 'thick', color: { argb: '000000' } },
|
|
bottom: { style: 'thick', color: { argb: '000000' } },
|
|
right: { style: 'thick', color: { argb: '000000' } }
|
|
}
|
|
};
|
|
|
|
currentRow++;
|
|
|
|
worksheet.mergeCells(`A${currentRow}:F${currentRow + 3}`);
|
|
const notesContentCell = worksheet.getCell(`A${currentRow}`);
|
|
notesContentCell.value = report.notes || 'No additional notes';
|
|
notesContentCell.style = {
|
|
font: { name: 'Arial', size: 11 },
|
|
alignment: { horizontal: 'center', vertical: 'middle', wrapText: true },
|
|
border: {
|
|
top: { style: 'thick', color: { argb: '000000' } },
|
|
left: { style: 'thick', color: { argb: '000000' } },
|
|
bottom: { style: 'thick', color: { argb: '000000' } },
|
|
right: { style: 'thick', color: { argb: '000000' } }
|
|
}
|
|
};
|
|
|
|
return currentRow + 5;
|
|
}
|
|
|
|
async function addFooter(worksheet: ExcelJS.Worksheet, currentRow: number): Promise<number> {
|
|
worksheet.mergeCells(`A${currentRow}:F${currentRow}`);
|
|
const footerCell = worksheet.getCell(`A${currentRow}`);
|
|
footerCell.value = '';
|
|
footerCell.style = {
|
|
font: { name: 'Arial', size: 12 },
|
|
alignment: { horizontal: 'center', vertical: 'middle' },
|
|
border: {
|
|
top: { style: 'thick', color: { argb: '000000' } },
|
|
left: { style: 'thick', color: { argb: '000000' } },
|
|
bottom: { style: 'thick', color: { argb: '000000' } },
|
|
right: { style: 'thick', color: { argb: '000000' } }
|
|
}
|
|
};
|
|
|
|
// Set row heights for better appearance
|
|
worksheet.eachRow((row, rowNumber) => {
|
|
row.height = 20;
|
|
});
|
|
|
|
// Set print settings
|
|
worksheet.pageSetup = {
|
|
paperSize: 9, // A4
|
|
orientation: 'portrait',
|
|
fitToPage: true,
|
|
fitToWidth: 1,
|
|
fitToHeight: 0,
|
|
margins: {
|
|
left: 0.7,
|
|
right: 0.7,
|
|
top: 0.75,
|
|
bottom: 0.75,
|
|
header: 0.3,
|
|
footer: 0.3
|
|
}
|
|
};
|
|
|
|
return currentRow + 1;
|
|
} |