ФРОВ_.контроль_остатков@.sql

/*Çàÿâêà áåç íîìåðà*/
/*Çàïóñê ñ [sql-store]; øàãè 1-3 çàíèìàþò 04:02*/
declare @ReportDate date = convert(date,getdate())

--(1) Âûáîð ÅÒÑ èç ãðóïïû ÔÐÎÂ (1)
DECLARE @itemid TABLE (itemid INT, ItemName nvarchar(255))

insert into @itemid
select itemid, ItemName
from [BackStore].[dbo].[EntItems] t1
where [IdLevel_2] =16 --Êàòåãîðèÿ "Îâîùè, ôðóêòû"
	and not ItemName like 'Óäàëåíî%'


--(2) Ñðåäíèå ïðîäàæè è ïîñòàâêè çà 7 äíåé; 02:36--
DECLARE @Money1 TABLE ([ShopId] INT, ItemId INT, Sales decimal(18,2), Supply decimal(18,2))

insert into @Money1
SELECT t1.[ShopId]
		,t1.[ItemId]
		,sum([Sale])/7 as Sales --Ñðåäíèå ïðîäàæè
		,SUM(Supply) as Supply --Ïîëó÷åíî
FROM spsssql.[FORECAST].[dbo].[t_SupplySale] t1
join @itemid t2 on t2.ItemId=t1.ItemId
where t1.[Date] >= DATEADD(day,-7,@ReportDate)
group by t1.[ShopId],t1.[ItemId]


DECLARE @Money TABLE ([ShopId] INT, ItemId INT, Sales decimal(18,2), Supply decimal(18,2))

INSERT INTO @Money
select t1.* from @Money1 t1
join @itemid t2 on t2.ItemId=t1.ItemId


-- (3) Îñòàòêè; 00:02 ñ ñåðâåðà 
DECLARE @SqlString2 NVARCHAR(MAX)
DECLARE @Rest2 TABLE (shopid INT, itemid INT, Qty decimal(19,4))

SET @SqlString2 = '
SELECT * FROM OPENQUERY( [spsssql],''
select t1.ShopId, t1.ItemId, t1.Qty
from [FORECAST].[dbo].[RestDataShopItem]  t1
where t1.Date=convert(date,getdate())
order by t1.ShopId, t1.ItemId'') a'
       
INSERT INTO @Rest2
EXEC (@SqlString2)


DECLARE @Rest TABLE (shopid INT, itemid INT, Qty decimal(19,4))

INSERT INTO @Rest
select t1.* from @Rest2 t1
join @itemid t2 on t2.ItemId=t1.ItemId


-- (4) Çàêàçû; 06:31
declare @Orders2 TABLE ([ShopId] INT, ItemId INT, OrderDate date,  Ordered decimal(18,2))

insert into @Orders2
SELECT 
		t1.ShopId
		,t1.ItemId --Êîä ÅÒÑ
		,t1.OrderDate
		,sum(case when t1.DeliveryDate<=@ReportDate then t1.ShopOrderedQty else 0 end) as Ordered --Çàêàçàíî
		--,SUM(t2.[ReceivedQty]) as [ReceivedQty] --Ïîëó÷åíî ïî çàêçàì
FROM openquery(dl580g2,'select ShopId, t2.ItemId, OrderDate, DeliveryDate, ShopOrderedQty from [BaseOrders].[dbo].[Orders] t1
join [BaseOrders].[dbo].[OrdersItems] t2 on t2.OrderId=t1.OrderId
join Orders.dbo.Items i on t2.itemid=i.itemid
join orders.dbo.fngetcategoryfulllist(3) f on i.marketcategoryid=f.idlevel_5 and idlevel_2=16
where t1.OrderDate>=DATEADD(d,-2,convert(Date,getdate())) and t1.[OrderStatusId]=3') t1
group by t1.ShopId
		,t1.ItemId
		,t1.OrderDate
		

declare @Orders TABLE ([ShopId] INT, ItemId INT, Ordered decimal(18,2), Avail INT)

insert into @Orders
select t1.ShopId, t1.Itemid, sum(isnull(t1.Ordered,0)) as Ordered
,SUM(case when Ordered is null then 0 when t1.ItemId is not null then 1 else 0 end) as Avail --Äîñòóïíî ê çàêàçó
from @Orders2 t1
right join @itemid t2 on t2.itemid=t1.itemid
group by t1.ShopId, t1.Itemid


-- (5) Äàòà ïðîñòàâëåíèÿ òîâàðà â ìàòðèöó  
--[dl580g2].dbo.Orders.fnGetAssortmentStates


-- (5) Ñîâìåñòíî --
select t1.Shopid, t1.itemId
	, 0 as MatrixDate , t2.Qty as CurrentRest
	,(case when t1.Sales = 0 then null else t2.Qty/t1.Sales end) as Stock
	, t1.Sales, t3.Avail, t3.Ordered, t1.Supply 
	,(case when t3.Ordered = 0 then -1 when t1.Supply/t3.Ordered >1 then 1 else t1.Supply/t3.Ordered end) as Perc
into #Result
from @Money t1
full outer join @Rest t2 on t1.itemid=t2.itemid and t1.ShopId=t2.ShopId
full outer join @Orders t3 on t1.itemid=t3.itemid and t1.ShopId=t3.ShopId




/*

declare @Orders TABLE ([ShopId] INT, ItemId INT, Ordered decimal(18,2), Avail INT)

insert into @Orders
select a.ShopId, a.Itemid, sum(isnull(a.Ordered,0)) as Ordered
,SUM(case when Ordered is null then 0 when a.ItemId is not null then 1 else 0 end) as Avail --Äîñòóïíî ê çàêàçó
from 
	(SELECT 
		t1.ShopId
		,t1.ItemId --Êîä ÅÒÑ
		,t1.OrderDate
		,sum(case when t1.DeliveryDate<=@ReportDate then t1.ShopOrderedQty else 0 end) as Ordered --Çàêàçàíî
		--,SUM(t2.[ReceivedQty]) as [ReceivedQty] --Ïîëó÷åíî ïî çàêçàì
FROM openquery(dl580g2,'select ShopId, t2.ItemId, OrderDate, DeliveryDate, ShopOrderedQty from [BaseOrders].[dbo].[Orders] t1
join [BaseOrders].[dbo].[OrdersItems] t2 on t2.OrderId=t1.OrderId
join Orders.dbo.Items i on t2.itemid=i.itemid
join orders.dbo.fngetcategoryfulllist(3) f on i.marketcategoryid=f.idlevel_5 and idlevel_2=16
where t1.OrderDate>=DATEADD(d,-2,convert(Date,getdate())) and t1.[OrderStatusId]=3') t1
group by t1.ShopId
		,t1.ItemId
		,t1.OrderDate) a
right join @itemid t2 on t2.itemid=a.itemid
group by a.ShopId, a.Itemid	
*/