export const lotQuery = `
select IdMaschinen_ProdPlanung as LabelOnlineID, 
    IdMaschine as machineID, 
    MaschinenStandort as MachineLocation, 
    MaschinenBez as MachineDescription,
    IdProdPlanung as lot,
	prolink.lot as ProlinkLot,
    IdArtikelvarianten as AV,
    ArtikelVariantenBez as Alias,
    convert(varchar, add_date, 20) as Add_Date,
    Add_user,
    idEtikettenDrucker as printerID,
	b.name as PrinterName,
    CAST(TotalPlannedLoadingUnits as float) as PlannedQTY,
    CAST(TotalProducedLoadingUnits as float) as Produced,
    ROUND(CAST(TotalPlannedLoadingUnits as float) - CAST(TotalProducedLoadingUnits as float),2) as Remaining,
        case 
	-- checks if someone changed the criteria to something else to trigger the over run 
	when  x.ProzentReserveAnzahlPaletten > 0 then 'yes'

	-- if the lot has a decimal in it to allow over running with no intervention 
	when CAST(TotalPlannedLoadingUnits as float) - floor(CAST(TotalPlannedLoadingUnits as float)) > 0 then 'yes' 
	else 'no' end as overPrinting,
	CustomerHumanReadableId as CustomerId,
	CustomerName as CustomerName,
	idMaschine as MachineID,
	prolink.lastProlinkUpdate as lastProlinkUpdate,
    IdEtikettenLayoutPalette as palletLabel,
	AnzahlKopienPalette as pallerCopies,
	IdEtikettenLayoutKarton as cartonLabel,
	AnzahlKopienKarton as cartonCopies,
	IsTechnicallyReleased
	--*
    from AlplaPROD_test1.dbo.V_Maschinen_ProdPlanungen x (nolock)
    join
    [test1_AlplaPROD2.0_Read].[productionControlling].[ProducedLot] on  
    x.IdProdPlanung = 
    [test1_AlplaPROD2.0_Read].[productionControlling].[ProducedLot].ProductionLotHumanReadableId
	left join
	[test1_AlplaPROD2.0_Read].masterData.Printer as b on
	x.IdEtikettenDrucker = b.HumanReadableId
	-- adding in prolink lot
	left join
	(SELECT * from (SELECT IdMaschine as prolinkMachineId, 
    Produktionslos as lot,  
	Upd_Date as lastProlinkUpdate,
    ROW_NUMBER() OVER (PARTITION BY IdMaschine ORDER BY Upd_Date DESC) RN_Prolink
    FROM AlplaPROD_test1.dbo.T_HistoryProduktionsdaten (nolock)
    WHERE Upd_Date BETWEEN DATEADD(DD, - 300, getdate()) AND DATEADD(DD, 1, getdate())) p
	WHERE RN_Prolink = 1
     ) as prolink on
	 x.idMaschine = prolink.prolinkMachineId 

     where IsTechnicallyReleased = 1
`;
