// this query pulls all the inventory except the inv locations.

export const totalInvNoRn = `
select 
x.idartikelVarianten as av,
x.ArtikelVariantenAlias as Alias
--x.Lfdnr as RunningNumber,
,round(sum(EinlagerungsMengeVPKSum),0) as Total_Pallets
,sum(EinlagerungsMengeSum) as Total_PalletQTY 
,round(sum(VerfuegbareMengeVPKSum),0) as Avalible_Pallets
,sum(VerfuegbareMengeSum) as Avaliable_PalletQTY
,sum(case when c.Description LIKE '%COA%' then GesperrteMengeVPKSum else 0 end) as COA_Pallets
,sum(case when c.Description LIKE '%COA%' then GesperrteMengeSum else 0 end) as COA_QTY
,sum(case when c.Description NOT LIKE '%COA%' or x.IdMainDefect = -1 then GesperrteMengeVPKSum else 0 end) as Held_Pallets
,sum(case when c.Description NOT LIKE '%COA%' or x.IdMainDefect = -1 then GesperrteMengeSum else 0 end) as Held_QTY
,sum(case when x.WarenLagerLagerTyp = 8 then VerfuegbareMengeSum else 0 end) as Consigment
,IdProdPlanung as Lot
----,IdAdressen,
,x.AdressBez
,x.IdLagerAbteilung as 'location'
,x.LagerAbteilungKurzBez
,x.IdWarenlager as warehouseID
,x.WarenLagerKurzBez as warehouseName
--,*
from [AlplaPROD_test1].dbo.[V_LagerPositionenBarcodes] (nolock) x

left join
[AlplaPROD_test1].dbo.T_EtikettenGedruckt as l(nolock) on 
x.Lfdnr = l.Lfdnr AND l.Lfdnr > 1

left join

(SELECT *
  FROM [AlplaPROD_test1].[dbo].[T_BlockingDefects] where Active = 1) as c
  on x.IdMainDefect = c.IdBlockingDefect
/* 
The data below will be controlled by the user in excell by default everything will be passed over
 IdAdressen = 3
*/
where /*IdArtikelTyp = 1  and */x.IdWarenlager not in (6, 1)

group by x.idartikelVarianten, ArtikelVariantenAlias, c.Description
--,IdAdressen
,x.AdressBez
,IdProdPlanung
,x.IdLagerAbteilung
,x.LagerAbteilungKurzBez
,x.IdWarenlager
,x.WarenLagerKurzBez
--, x.Lfdnr
order by x.IdArtikelVarianten

`;

export const totalInvRn = `
select x.idartikelVarianten as av,
ArtikelVariantenAlias as Alias,
x.Lfdnr as RunningNumber,
round(sum(EinlagerungsMengeVPKSum),0) as Total_Pallets,
sum(EinlagerungsMengeSum) as Total_PalletQTY, 
round(sum(VerfuegbareMengeVPKSum),0) as Avalible_Pallets,
sum(VerfuegbareMengeSum) as Avaliable_PalletQTY,
sum(case when c.Description LIKE '%COA%' then GesperrteMengeVPKSum else 0 end) as COA_Pallets,
sum(case when c.Description LIKE '%COA%' then GesperrteMengeSum else 0 end) as COA_QTY, 
sum(case when c.Description NOT LIKE '%COA%' or x.IdMainDefect = -1 then GesperrteMengeVPKSum else 0 end) as Held_Pallets,
sum(case when c.Description NOT LIKE '%COA%' or x.IdMainDefect = -1 then GesperrteMengeSum else 0 end) as Held_QTY
,IdProdPlanung as Lot
,IdAdressen,
x.AdressBez
,x.lagerabteilungkurzbez as location
,lot.machine
--,lot.planstart
--,lot.planend
,produktionsdatummin
,'728'
    + RIGHT(CAST(YEAR(produktionsdatummin) AS varchar(4)), 1)
    + CAST(DATEDIFF(DAY, DATEFROMPARTS(YEAR(produktionsdatummin), 1, 1), produktionsdatummin) + 1 AS varchar(3))
    + CAST(lot.machine AS varchar(10))  as batch
--,*
from [AlplaPROD_test1].dbo.[V_LagerPositionenBarcodes] (nolock) x

left join
[AlplaPROD_test1].dbo.T_EtikettenGedruckt as l(nolock) on 
x.Lfdnr = l.Lfdnr AND l.Lfdnr > 1

left join

(SELECT *
  FROM [AlplaPROD_test1].[dbo].[T_BlockingDefects] where Active = 1) as c
  on x.IdMainDefect = c.IdBlockingDefect

/* 
get lot and machine info
*/
left join
(select location as machine,
runningnumber as lot
,planstart
,planend
--,* 
from [test1_AlplaPROD2.0_Read].[productionScheduling].[ProductionLot] (nolock) x

left join
[test1_AlplaPROD2.0_Read].[masterData].[Machine] (nolock) m on
m.id = x.machineid) as lot on 
lot.lot = IdProdPlanung
/* 
The data below will be controlled by the user in excell by default everything will be passed over
 IdAdressen = 3
*/
where IdArtikelTyp = 1  and x.IdWarenlager not in (6, 1)

group by x.idartikelVarianten, ArtikelVariantenAlias, c.Description, IdAdressen,
x.AdressBez , x.Lfdnr,
IdProdPlanung -- this will be flagged as being removed when we do historical.
,x.lagerabteilungkurzbez
,lot.machine
--,lot.planstart
--,lot.planend
,produktionsdatummin
order by x.IdArtikelVarianten
`;

const totalInvValue = ``;

const totalInvValueRn = ``;