lst/lstV2/server/services/notifications/controller/materialsPerDay/materialPerDay.ts

import {
	addDays,
	format,
	formatISO,
	isBefore,
	parseISO,
	startOfWeek,
} from "date-fns";
import { db } from "../../../../../database/dbclient.js";
import { invHistoricalData } from "../../../../../database/schema/historicalINV.js";
import { tryCatch } from "../../../../globalUtils/tryCatch.js";
import { createLog } from "../../../logger/logger.js";
import { query } from "../../../sqlServer/prodSqlServer.js";
import {
	materialPerDay,
	materialPurchasesPerDay,
} from "../../../sqlServer/querys/dataMart/materialPerDay.js";
import { singleArticle } from "../../../sqlServer/querys/misc/singleArticle.js";
import { sendEmail } from "../sendMail.js";
import { materialPurchases } from "./materialPurchases.js";
import { buildInventoryTimeline } from "./materialWithInv.js";

const getInv = async () => {
	const { data, error } = await tryCatch(db.select().from(invHistoricalData));

	if (error) {
		return [];
	}

	return data;
};

function toDate(val: any) {
	if (val instanceof Date) return val;
	if (typeof val === "string") return parseISO(val.replace(" ", "T"));
	return new Date(val);
}

function sumByMaterialAndWeek(data: any) {
	/** @type {Record<string, Record<string, number>>} */
	const grouped: any = {};

	for (const r of data) {
		const mat = String(r.MaterialHumanReadableId);
		const d = toDate(r.CalDate);
		const week = formatISO(startOfWeek(d, { weekStartsOn: 1 }), {
			representation: "date",
		});

		grouped[mat] ??= {};
		grouped[mat][week] ??= 0;
		grouped[mat][week] += Number(r.DailyMaterialDemand) || 0;
	}

	const result = [];
	for (const [mat, weeks] of Object.entries(grouped)) {
		// @ts-ignore
		for (const [week, total] of Object.entries(weeks)) {
			result.push({
				MaterialHumanReadableId: mat,
				WeekStart: week,
				WeeklyDemand: Number(total).toFixed(2),
			});
		}
	}

	return result;
}

export default async function materialPerDayCheck() {
	/**
	 * getting the shipped pallets
	 */

	const startDate = format(new Date(Date.now()), "yyyy-MM-dd");
	const endDate = format(addDays(new Date(Date.now()), 90), "yyyy-MM-dd");

	const { data, error } = (await tryCatch(
		query(
			materialPerDay
				.replace("[startDate]", startDate)
				.replace("[endDate]", endDate),
			"material check",
		),
	)) as any;

	if (error) {
		return {
			success: false,
			message: "Error getting the material data",
			error,
		};
	}

	if (!data.success) {
		return {
			success: false,
			message: data.message,
			data: [],
		};
	}

	// purchases

	const pOrders = (await materialPurchases({ startDate, endDate })) as any;

	//console.log(pOrders);
	const openingInventory: Record<string, number> = {};
	const inventoryRows = await getInv();
	for (const row of inventoryRows) {
		openingInventory[row.article] = Number(row.total_QTY) || 0;
	}

	const materialsDemand = buildInventoryTimeline(
		sumByMaterialAndWeek(data.data) as any,
		openingInventory,
		pOrders,
	);

	const { data: av, error: eav } = await tryCatch(
		query(singleArticle.replace("[av]", "107"), "single article"),
	);

	const formattedMaterials = materialsDemand
		.filter((n) => n.MaterialHumanReadableId === `${av?.data[0].article}`)
		.map((i) => ({
			...i,
			OpeningInventory: i.OpeningInventory?.toLocaleString("en-US", {
				minimumFractionDigits: 2,
				maximumFractionDigits: 2,
			}),
			Purchases: i.Purchases?.toLocaleString("en-US", {
				minimumFractionDigits: 2,
				maximumFractionDigits: 2,
			}),
			Consumption: i.Consumption?.toLocaleString("en-US", {
				minimumFractionDigits: 2,
				maximumFractionDigits: 2,
			}),
			ClosingInventory: i.ClosingInventory?.toLocaleString("en-US", {
				minimumFractionDigits: 2,
				maximumFractionDigits: 2,
			}),
		}));

	// send the email stuff
	const emailSetup = {
		email: "blake.matthes@alpla.com",
		subject: `Material Week.`,
		template: "materialPerDay",
		context: {
			items: formattedMaterials,
			article: av?.data[0].combined,
		},
	};

	const { data: sentEmail, error: sendEmailError } = await tryCatch(
		sendEmail(emailSetup),
	);
	if (sendEmailError) {
		createLog(
			"error",
			"blocking",
			"notify",
			"Failed to send email, will try again on next interval",
		);
		return {
			success: false,
			message: "Failed to send email, will try again on next interval",
		};
	}

	return {
		success: true,
		message: "material data",
		data: formattedMaterials,
	};
}