lst/lstV2/server/services/dataMart/controller/psiPlanningDataImproved.ts

import { format } from "date-fns-tz";
import { tryCatch } from "../../../globalUtils/tryCatch.js";
import { query } from "../../sqlServer/prodSqlServer.js";

const improvedQuery = `

DECLARE @StartDate DATE = '[startDate]' -- 2025-1-1
DECLARE @EndDate DATE = '[endDate]' -- 2025-1-31

SELECT 
	[RunningNumber] as lot
      ,[ProfitCentreDescription]
      ,[MachineDescription]
      ,[ArticleHumanReadableId]
      ,[ArticleDescription]
      ,[DeliveryAddressHumanReadableId]
      ,[DeliveryAddressDescription]
      ,[MouldHumanReadableId]
      ,[BlowheadHumanReadableId1]
      ,[PackagingInstructionHumanReadableId]
      ,[PackagingInstructionDescription]
      ,[MainMaterialHumanReadableId]
      ,[MainMaterialDescription]
      ,[CompoundHumanReadableId]
      ,[CompoundDescription]
      ,[ProductionLotState]
      ,[PlanType]
      ,[ProducedQuantityLoadingUnit]
      ,[ProducedQuantityPieces]
      ,[PlanStart]
      ,[PlanEnd]
	  ,[ProdStart] 
	  ,[TheoreticEnd]
    	  ,[ProdDuration]
      ,[SetupDuration]  
      ,[StartupDuration]
    
      ,[NetEquipmentEfficiency]
      ,[UtilisationDuration]
      ,[CycleTime]
      ,[Cavities]
      ,[FixedQuantity]
      ,[ProducedQuantityTrucks]
      ,[ProducedQuantityTradeUnit]
      ,[MaxRegrind]
      ,[Conflict]
      ,[ProductionOrderHumanReadableId]
      ,[ProductionDataImportSource]
      ,[Remark]
      ,[BlowheadDescription1]
      ,[MouldDescription]
      ,[ProcessLossPercentage]
      ,[SetupTypeNumberOfPersons]
      ,[UnplannedDowntimePercentage]
      ,[PlanQuantityLoadingUnit]
      ,[PlanQuantityPieces]
      ,[PlanQuantityTradeUnit]
      ,[PlanQuantityTrucks]
      ,[PublishState]
      ,[LastChange]
      ,[MaterialConsumed]
      ,[MaterialStaged]
      ,[MachineLocation]
      ,[HasPrioritization]
      ,[ArticleAlias]

  FROM [test1_AlplaPROD2.0_Read].[productionScheduling].[ProductionLot] with (nolock)
  where PlanEnd between @StartDate and @EndDate
  and ArticleHumanReadableId in ([articles])
    and PublishState = 1
  order by PlanStart 

`;
export const improvedPsiPlanningInfo = async (something: any) => {
	const { data, error } = (await tryCatch(
		query(
			improvedQuery
				.replace("[articles]", something.avs)
				.replace("[startDate]", something.startDate)
				.replace("[endDate]", something.endDate),
			"PSI planning info",
		),
	)) as any;

	// add error handling in later here

	return splitProduction(data.data);
};

const splitProduction = (runs: any) => {
	const results: any = [];
	const WORKDAY_START_HOUR = 7; // 07:00 start well later get this from the shift def

	runs.forEach((e: any) => {
		const {
			PlanStart,
			PlanEnd,
			PlanQuantityPieces,
			ArticleHumanReadableId,
			ProdDuration,
		} = e;

		const prodStart: any = new Date(PlanStart);
		const prodEnd: any = new Date(PlanEnd);
		const prodDuration = ProdDuration
			? ProdDuration * 60 * 60 * 1000
			: prodEnd - prodStart;

		// get the prod date the production falls under
		function getProdDayStart(date: Date) {
			const d = new Date(date);
			d.setHours(WORKDAY_START_HOUR, 0, 0, 0);

			if (date.getHours() < WORKDAY_START_HOUR) {
				// before 07:00, belongs to previous calendar day
				d.setDate(d.getDate() - 1);
			}
			return d;
		}

		// current pointer starts at the work-day start that contains our start time
		let currentStart = new Date(prodStart);
		let prodDayStart = getProdDayStart(currentStart);

		while (prodDayStart < prodEnd) {
			// 1️⃣ The next day’s start = prodDayStart + 1 day at 07:00
			const nextProdDayStart = new Date(prodDayStart);
			nextProdDayStart.setDate(nextProdDayStart.getDate() + 1);

			// 2️⃣ Segment end is either the next work-day start or the actual end, whichever is sooner
			const segmentEnd = new Date(
				Math.min(nextProdDayStart.getTime(), prodEnd.getTime()),
			);

			// 3️⃣ Determine overlap window within (startTime..endTime)
			const segStart: any = new Date(
				Math.max(prodDayStart.getTime(), prodStart.getTime()),
			);
			const segEnd: any = segmentEnd;

			if (segEnd > segStart) {
				const segMs = segEnd - segStart;
				const proportion = segMs / prodDuration;
				const qty = PlanQuantityPieces * proportion;
				const pal = e.PlanQuantityLoadingUnit * proportion;

				results.push({
					Article: ArticleHumanReadableId,
					Description: e.ArticleAlias,
					MachineId: e.MachineLocation,
					MachineName: e.MachineDescription,
					LotNumber: e.lot,
					ProductionDay: format(prodDayStart, "M/d/yyyy"),
					TotalPlanned: e.PlanQuantityPieces,
					// PlanEnd,
					// TheoreticEnd,
					QTYPerDay: parseInt(qty.toFixed(0)),
					PalDay: parseFloat(pal.toFixed(2)),
					finished: e.ProductionLotState === 3 ? 1 : 0,
					cavities: e.Cavities,
					//prodDuration,
				});
			}

			// move to next production-day window
			prodDayStart = nextProdDayStart;
		}
	});

	return results;
};