-
Notifications
You must be signed in to change notification settings - Fork 0
/
Импорт - обновление продаж (select).sql
120 lines (107 loc) · 7.17 KB
/
Импорт - обновление продаж (select).sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
--- Îïðåäåëåíèå äàòû îáíîâëåíèÿ
declare @OperReloadDate datetime
set @OperReloadDate = (select min(ReloadDate) from ShopdateReload)
----=============================== Òîâàðîäâèäåíèå =======================
--- Óäàëåíèå
--delete from dbo.ForecastOperationsPart where Date >= @OperReloadDate
--- Îáíîâëåíèå äàííûõ
declare @DateBegin datetime, @DateEnd datetime
set @DateBegin = @OperReloadDate
set @DateEnd = DateAdd(Day,-1,convert(varchar(10),GETDATE(),102))
----- Âñå ÏÐÈÕÎÄÍÛÅ îïåðàöèè
--insert into dbo.ForecastOperationsPart (ShopId,Date,ItemId,ForecastOperationTypeId,Qty)
select P.ShopId, P.Date, P.ItemId,
case
--ïðîäàæè
when T.OperationTypeGroupID = 2 and T.OperationTypeId in (3,204,302) and P.OperationStatusId <> 4 then 2 --âîçâðàò ñ ðîçíè÷íûõ ïðîäàæ áåç óöåíêè è íåîáðàáîòêè
when T.OperationTypeGroupID = 2 and T.OperationTypeId in (3,204,302) and P.OperationStatusId = 4 then 4 --âîçâðàò ñ ðîçíè÷íûõ ïðîäàæ ïî óöåíêå
--ïîñòàâêè
when T.OperationTypeGroupID = 1 and C.ContractorTypeId in (1,9) then 8 --ïîñòàâêè âíåøíèå îò ïîñòàâùèêà è ÐÖ
when T.OperationTypeGroupID = 1 and C.ContractorTypeId in (5) and P.OperationStatusId=3 then 9 --ïîñòàâêè èçëèøêîâ (ïî èíâåíòàðèçàöèè)
when T.OperationTypeGroupID = 1 and C.ContractorTypeId in (5) and P.OperationStatusId=1 then 10 --ïîñòàâêè èçëèøêîâ äëÿ êîððåêòèðîâêè íåîáðàáîòàííûõ ïðîäàæ (ïî èíâåíòàðèçàöèè)
when T.OperationTypeGroupID = 1 and C.ContractorTypeId in (2,3,4,6,7,8,10,11,12,13,14,15) then 11 --ïðî÷èå ïîñòàâêè (âíóòðåííèå îò ìàãàçèíîâ, ñîáñòâåííîå ïðîèçâîäñòâî è äð.)
else 14 end as ForecastOperationTypeId, --ñóììà âñåõ ïðî÷èõ îïåðàöèé ñ ïëþñîì
sum(Qty) as Qty
from [SQL-STORE].BackStore.dbo.OperationsPart P (nolock)
join [SQL-STORE].BackStore.dbo.EntOperationTypes T (nolock) on T.OperationTypeID=P.OperationTypeId
left join [SQL-STORE].BackStore.dbo.Contractors C (nolock) on P.CounteragentId=C.ContractorId
where P.ShopId in (250,248,116,27)
and P.Date between @DateBegin and @DateEnd
and T.DSign = 1
group by ShopId, Date, ItemId,
case
--ïðîäàæè
when T.OperationTypeGroupID = 2 and T.OperationTypeId in (3,204,302) and P.OperationStatusId <> 4 then 2 --âîçâðàò ñ ðîçíè÷íûõ ïðîäàæ áåç óöåíêè è íåîáðàáîòêè
when T.OperationTypeGroupID = 2 and T.OperationTypeId in (3,204,302) and P.OperationStatusId = 4 then 4 --âîçâðàò ñ ðîçíè÷íûõ ïðîäàæ ïî óöåíêå
--ïîñòàâêè
when T.OperationTypeGroupID = 1 and C.ContractorTypeId in (1,9) then 8 --ïîñòàâêè âíåøíèå îò ïîñòàâùèêà è ÐÖ
when T.OperationTypeGroupID = 1 and C.ContractorTypeId in (5) and P.OperationStatusId=3 then 9 --ïîñòàâêè èçëèøêîâ (ïî èíâåíòàðèçàöèè)
when T.OperationTypeGroupID = 1 and C.ContractorTypeId in (5) and P.OperationStatusId=1 then 10 --ïîñòàâêè èçëèøêîâ äëÿ êîððåêòèðîâêè íåîáðàáîòàííûõ ïðîäàæ (ïî èíâåíòàðèçàöèè)
when T.OperationTypeGroupID = 1 and C.ContractorTypeId in (2,3,4,6,7,8,10,11,12,13,14,15) then 11 --ïðî÷èå ïîñòàâêè (âíóòðåííèå îò ìàãàçèíîâ, ñîáñòâåííîå ïðîèçâîäñòâî è äð.)
else 14 end
----- Âñå ÐÀÑÕÎÄÍÛÅ îïåðàöèè
--insert into dbo.ForecastOperationsPart (ShopId,Date,ItemId,ForecastOperationTypeId,Qty)
select P.ShopId, P.Date, P.ItemId,
case
--ïðîäàæè
when T.OperationTypeGroupID = 2 and T.OperationTypeId in (2,203,321) and P.OperationStatusId <> 4 then 1 --ðîçíè÷íûå ïðîäàæè áåç óöåíêè è íåîáðàáîòêè
when T.OperationTypeGroupID = 2 and T.OperationTypeId in (2,203,321) and P.OperationStatusId = 4 then 3 --ðîçíè÷íûå ïðîäàæè ïî óöåíêå
when T.OperationTypeID in (10) then 5 --ðîçíè÷íûå ïðîäàæè ïî íåîáðàáîòêå
when T.OperationTypeGroupID = 6 then 6 --ðîçíè÷íûå ïðîäàæè ïî íåîáðàáîòêå, ñêîððåêòèðîâàííûå â ó÷åò (ïî èíâåíòàðèçàöèè)
when T.OperationTypeGroupID = 3 and C.ContractorTypeId in (3) then 7 --îïòîâûå ïðîäàæè
--ñïèñàíèÿ
when T.OperationTypeID in (8) then 12 --ñïèñàíèå íåäîñòàòêà (ïî èíâåíòàðèçàöèè)
when T.OperationTypeID in (16,64,74) then 13 --ñïèñàíèÿ ïî ñðîêó ãîäíîñòè/ïîð÷å
else 15 end as ForecastOperationTypeId, --ñóììà âñåõ ïðî÷èõ îïåðàöèé ñ ìèíóñîì
sum(Qty) as Qty
from [SQL-STORE].BackStore.dbo.OperationsPart P (nolock)
join [SQL-STORE].BackStore.dbo.EntOperationTypes T (nolock) on T.OperationTypeID=P.OperationTypeId
left join [SQL-STORE].BackStore.dbo.Contractors C (nolock) on P.CounteragentId=C.ContractorId
where P.ShopId in (250,248,116,27)
and P.Date between @DateBegin and @DateEnd
and T.DSign = -1
group by ShopId, Date, ItemId,
case
--ïðîäàæè
when T.OperationTypeGroupID = 2 and T.OperationTypeId in (2,203,321) and P.OperationStatusId <> 4 then 1 --ðîçíè÷íûå ïðîäàæè áåç óöåíêè è íåîáðàáîòêè
when T.OperationTypeGroupID = 2 and T.OperationTypeId in (2,203,321) and P.OperationStatusId = 4 then 3 --ðîçíè÷íûå ïðîäàæè ïî óöåíêå
when T.OperationTypeID in (10) then 5 --ðîçíè÷íûå ïðîäàæè ïî íåîáðàáîòêå
when T.OperationTypeGroupID = 6 then 6 --ðîçíè÷íûå ïðîäàæè ïî íåîáðàáîòêå, ñêîððåêòèðîâàííûå â ó÷åò (ïî èíâåíòàðèçàöèè)
when T.OperationTypeGroupID = 3 and C.ContractorTypeId in (3) then 7 --îïòîâûå ïðîäàæè
--ñïèñàíèÿ
when T.OperationTypeID in (8) then 12 --ñïèñàíèå íåäîñòàòêà (ïî èíâåíòàðèçàöèè)
when T.OperationTypeID in (16,64,74) then 13 --ñïèñàíèÿ ïî ñðîêó ãîäíîñòè/ïîð÷å
else 15 end
--====================== Èñïîëíåíèå çàêàçîâ =============================
--- Óäàëåíèå
--delete from dbo.OrderSupply where FactSupplyDate >= @OperReloadDate
--- Äîáàâëåíèå
--insert into dbo.OrderSupply (OrderId,ShopId,ItemId,FactSupplyDate,FactQty)
select P.ParentDocNum, P.ShopId, P.ItemId, P.DATE, sum(P.Qty) as Qty
from [SQL-STORE].BackStore.dbo.OperationsPart P (nolock)
join [SQL-STORE].BackStore.dbo.EntOperationTypes T (nolock) on T.OperationTypeID=P.OperationTypeId
where P.ShopId in (250,248,116,27)
and P.Date between @DateBegin and @DateEnd
and T.DSign = 1
and T.OperationTypeGroupID = 1
and ISNULL(P.ParentDocNum,0)<>0
group by P.ParentDocNum, P.ShopId, P.ItemId, P.DATE
-- Èñïðàâëåíèÿ êîäà çàêàçà â ñâÿçè ñ ïåðåíîñîì â ÖÎ
--update dbo.OrderSupply set OrderId = T.OrderId
--from dbo.OrderSupply O
--join
--(select ShopId, OrderDate, OrderId, ShopOrderId
--from DL580G2.BaseOrders.dbo.Orders H (nolock)
--where OrderDate>='2013.12.01' and isnull(ShopOrderId,0)<>0 and OrderId<>ShopOrderId) as T
--on T.ShopId=O.ShopId and O.OrderId=T.ShopOrderId
----====================== Çàïèñü ëîãà îáíîâëåíèÿ =========================
--insert into UpdateOperationsLog (ShopId, Date, StartDate, FinishStatus)
--select ShopId, GETDATE(), @OperReloadDate, 1
--from dbo.EntShops
--where ShopId in (select ShopId from ShopDataIntegration)
----====================== Ñòàòóñ íàëè÷èÿ äàííûõ ==========================
--delete from OperationsPartStatus where Date>=@OperReloadDate
--insert into OperationsPartStatus (ShopId, Date, OperationTypeForStatus, LoadingStatus)
--select distinct ShopId, Date, 1, 1 from dbo.ForecastOperationsPart where Date>=@OperReloadDate order by ShopId, Date
--insert into OperationsPartStatus (ShopId, Date, OperationTypeForStatus, LoadingStatus)
--select distinct ShopId, Date, 2, 1 from dbo.ForecastOperationsPart where Date>=@OperReloadDate order by ShopId, Date