
/****** Object:  StoredProcedure [dbo].[ApiOrderQueue]    Script Date: 13.09.2021 16:52:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[ApiOrderQueue]
	 @itemId as INT,
	 @lineId AS INT,
	 @regionId as INT
AS 
BEGIN
--declare @itemId int;
---set @itemId = 16;

DECLARE @Stock TABLE
(  stId int, 
    Quantity decimal(16,2), 
	TotalQuantity decimal(16,2), 
    DeliveryDate DateTime
)

/*
create table #stock
(
    Id int, 
    Quantity decimal(16,2), 
    DeliveryDate DateTime
	);
*/
	-- dodajemy stany magazynowe
	INSERT INTO @stock(stId,Quantity,TotalQuantity,DeliveryDate)
	select 0 stId, SUM(Stock) Quantity, SUM(Stock) TotalQuantity, '2000-01-01' DeliveryDate  from dbo.ApiStockView2 where ItemId = @itemId;

	-- dodajemy zamowienia dostawy
	INSERT INTO @stock(stId,Quantity,TotalQuantity,DeliveryDate)
	select l.Id stId,  quantity Quantity,  quantity TotalQuantity, d.Date2 DeliveryDate from dbo.ApiLines l 
JOIN dbo.ApiDocuments d ON d.Id = l.documentId
JOIN dbo.ApiDefinitions def on d.DefId = def.Id and def.FlowDirection =1
where itemId = @itemId ORDER BY Date2

	--select * from @stock;


	DECLARE @orderQueue TABLE
(  [Id] [int] IDENTITY(1,1) NOT NULL,
   
	OrderLineId int,
    OrgQuantity decimal(16,2), 
	Quantity decimal(16,2), 
	StockId int,
	StockQuantity decimal(16,2)    
)

	 declare @LineOrderId int;
	 declare @quantity decimal(16,2);

DECLARE Order_Cursor CURSOR FOR	 
select l.Id, quantity  from dbo.ApiLines l 
JOIN dbo.ApiDocuments d ON d.Id = l.documentId
JOIN dbo.ApiDefinitions def on d.DefId = def.Id and def.FlowDirection =-1
where itemId = @itemId;
  ;

OPEN Order_Cursor;
FETCH NEXT FROM Order_Cursor INTO @LineOrderId, @quantity
WHILE @@FETCH_STATUS = 0
  BEGIN
	  
	--  select @LineOrderId,@quantity;
	  INSERT INTO @orderQueue( OrderLineId,OrgQuantity,Quantity,StockId, StockQuantity) 
	  select @LineOrderId OrderLineId, @quantity OrgQuantity, case when ( s.Quantity> @quantity) then @quantity else s.Quantity end Quantity, s.stId StockId, s.Quantity 
	  from @stock s where Quantity>0 order by DeliveryDate;
	 
	 --  ustawiamy właściwe ilości	 
	  update @orderQueue set
	  Quantity = IIF(OrgQuantity - quantSum <0, 0, IIF( (OrgQuantity - quantSum)<Quantity,(OrgQuantity - quantSum), Quantity ))
	  from (select SUM(oo.Quantity) quantSum, oo.Id Id2 from @orderQueue oo where oo.OrderLineId = @LineOrderId GROUP BY oo.Id) ooo where Id> ooo.Id2
	 
	 -- usuwamy puste
	  delete @orderQueue where Quantity=0 and OrderLineId = @LineOrderId;
	  -- aktualizujemy zasoby... bez zużytych
	  update @Stock set Quantity = TotalQuantity - ISNULL((select sum(Quantity) from @orderQueue where stId = StockId),0);

--	  select * from @orderQueue;
	--  select * from @Stock;
	 
	 -- dodajemy pozycje z brakującymi zasobami.
	  declare @SumLine decimal(16,2);
	  set @SumLine = ISNULL( ( select  sum(Quantity) from @orderQueue where OrderLineId = @LineOrderId),0);
	  IF (@quantity - @SumLine>0) 
	  BEGIN
		  INSERT INTO @orderQueue( OrderLineId,OrgQuantity,Quantity,StockId, StockQuantity) 
		  VALUES ( @LineOrderId, @quantity,@quantity - @SumLine,-1,cast(0 as decimal));
	  END


	  FETCH NEXT FROM Order_Cursor INTO @LineOrderId,@quantity;
   END;
CLOSE Order_Cursor;
DEALLOCATE Order_Cursor;

-- ddoajemy niewykorzystane zasoby
  INSERT INTO @orderQueue( OrderLineId,OrgQuantity,Quantity,StockId, StockQuantity) 
  select 0 OrderLineId, cast(0 as decimal) OrgQuantity,Quantity, stId StockId, TotalQuantity StockQuantity from @Stock where  Quantity>0 

		  

	--drop table stock;
		  --select * from @orderQueue;
select * from (
  select ISNULL(OrderLineId,0) OrderLineId, ISNULL(oDocs.Id,0) OrderId, ISNULL(oDocs.NumberString,'') OrderNumber, ISNULL(oDocs.ErpNumberString,'') OrderExtNumber, ISNULL(oDocs.Date,'2020-01-01') OrderDate,
  ISNULL(oDocs.Info,'') OrderCustInfo, ISNULL(oDocs.CustId,0)  CustId,
  @itemId ItemId, ISNULL(olines.itemName,'') ItemName, ISNULL(olines.itemCode,'') ItemCode,
  oq.Quantity Quantity, oq.StockId StockId,oq.StockQuantity,
  ISNULL(sDocs.Id,0) StockDocId, ISNULL(sDocs.NumberString,'') StockDocNumber, ISNULL(sDocs.Date2, '2000-01-01') StockDeliveryDate,
  ISNULL(sDocs.StateDoc,0) StockStateId
   from @orderQueue oq
  LEFT OUTER JOIN dbo.ApiLines olines ON OrderLineId  = olines.Id  
  LEFT OUTER JOIN dbo.ApiDocuments oDocs ON olines.documentId  = oDocs.Id
  LEFT OUTER JOIN dbo.ApiLines slines ON StockId>0 and  slines.Id = StockId 
  LEFT OUTER JOIN dbo.ApiDocuments sDocs ON slines.documentId = sDocs.Id)
  s
  where (@lineId =0 OR  @lineId = s.OrderLineId )
   
END

	  --select * from @Stock;