Untitled

const buildSummaryWorksheet = (inventory: InventoryItem, costGroups: object): XLSX.WorkSheet => {
	const worksheet: XLSX.WorkSheet = XLSX.utils.json_to_sheet([]);
	const generalStyle = { alignment: { horizontal: 'right' } };
	const generalData = [
		[
			{ v: 'Date', t: 's' },
			{
				v: inventory?.closed_at ?? '',
				t: 'd',
				s: { ...generalStyle, numFmt: 'yyyy-mm-dd' }
			}
		],
		[
			{ v: 'Restaurant', t: 's' },
			{ v: projectStore.getCurrentRestaurantName(), t: 's', s: generalStyle }
		],
		[
			{ v: 'Inventory list', t: 's' },
			{ v: inventory?.type ?? 'N/A', t: 's', s: generalStyle }
		]
	];

	const costGroupSums = [] as any;
	for (const [costGroupName, costGroupItems] of Object.entries(costGroups)) {
		const sum = costGroupItems.reduce((_sum: number, item: any) => {
			_sum += item?.Value ?? 0;
			return _sum;
		}, 0);
		costGroupSums.push([costGroupName, roundToNearestCent(sum)]);
	}

	const costGroupData = [['Cost groups', ''], ...costGroupSums];
	const costGroupsStartRow = generalData.length + 1;

	XLSX.utils.sheet_add_aoa(worksheet, generalData);
	XLSX.utils.sheet_add_aoa(worksheet, costGroupData, { origin: costGroupsStartRow });

	// add number format
	costGroupSums.forEach((row, rowIndex) => {
		const sumCellIndex = 1;
		const cellCoord = XLSX.utils.encode_cell({ r: costGroupsStartRow + 1 + rowIndex, c: sumCellIndex });
		worksheet[cellCoord].s = { numFmt: '#,##0.00' };
	});

	return worksheet;
};

const buildInventoryWorksheet = (costGroups: object): XLSX.WorkSheet => {
	const worksheet: XLSX.WorkSheet = XLSX.utils.json_to_sheet([]);
	let totalRows = 0;
	const sumRowCoords = [] as String[];
	const reportedLevelLetter = String.fromCharCode(65 + Object.keys(columnKeyMap).indexOf('Reported Level'));
	const defaultValueLetter = String.fromCharCode(
		65 + Object.keys(columnKeyMap).indexOf('Value / default unit')
	);
	const sumColumnLetter = String.fromCharCode(65 + Object.keys(columnKeyMap).indexOf('Value'));
	const sumStyle = { font: { bold: true }, numFmt: '#,##0.00' };

	for (const [costGroup, groupData] of Object.entries(costGroups) as any[]) {
		XLSX.utils.sheet_add_json(worksheet, groupData, { origin: totalRows++ === 0 ? 0 : -1 });
		XLSX.utils.sheet_add_aoa(worksheet, [getSumRow(costGroup)], { origin: -1 });

		for (let i = totalRows + 1; i < totalRows + groupData.length + 1; i++) {
			worksheet[`${reportedLevelLetter}${i}`].s = { numFmt: '#,##0.00' };
			worksheet[`${defaultValueLetter}${i}`].s = { numFmt: '#,##0.00' };
			worksheet[`${sumColumnLetter}${i}`].s = { numFmt: '#,##0.00' };
		}

		const sumRowCoord = `${totalRows + groupData.length + 1}`;
		worksheet[`A${sumRowCoord}`].s = sumStyle;
		worksheet[`${sumColumnLetter}${sumRowCoord}`] = {
			t: 'n',
			f: `SUM(${sumColumnLetter}${totalRows}:${sumColumnLetter}${totalRows + groupData.length})`,
			s: sumStyle
		};
		XLSX.utils.sheet_add_json(worksheet, [], { origin: -1 });
		sumRowCoords.push(`${sumColumnLetter}${sumRowCoord}`);
		totalRows += groupData.length + 2;
	}

	XLSX.utils.sheet_add_aoa(worksheet, [getSumRow('total')], { origin: -1 });

	const totalSumFormula = sumRowCoords.join('+');
	const range = XLSX.utils.decode_range(worksheet['!ref']!);
	const lastRowNumber = range.e.r;
	worksheet[`A${lastRowNumber + 1}`].s = sumStyle;
	worksheet[`${sumColumnLetter}${lastRowNumber + 1}`] = {
		t: 'n',
		f: `${totalSumFormula}`,
		s: sumStyle
	};
	return worksheet;
};