· 6 years ago · Sep 04, 2019, 06:52 AM
1CREATE PROCEDURE dbo.upd_ProductionByTemplate @DepartmentID int, @Date datetime, @OrderID int, @GoodsID int, @PlanCountCuter money, @CountCuter money,
2 @SticksCount DECIMAL(18,3), @SmenaID int=-1,@ME int=-1, @Coef decimal(18,7) = 1, @PFProdOrderID INT = -1,
3 @TypeOrder int = -1, @Type INT = -1
4AS
5 -- 08.05.2014 -- by Green !! Вертаю ВЗАД использование табл-ФУНЦИЙ вместо INSERT #temp EXEC ПРОЦ !!!!
6 SET NOCOUNT ON
7 DECLARE @TemplateID INT, @MaxDate DATETIME, @ReceptID INT
8 DECLARE @Recept TABLE(
9 GoodsID INT,
10 PlanQty DECIMAL(18, 7),
11 GoodsQty DECIMAL(18, 7),
12 RawID INT)
13
14 DECLARE @CategoryID INT, @GoodsCategoryID INT, @GoodsQty DECIMAL(18, 4), @ProdPrice MONEY, @PFPrice MONEY, @ProdOrderID INT, @NormPrc DECIMAL(18, 2), @Summa MONEY
15
16 SELECT @GoodsCategoryID = CategoryID
17 FROM GoodsCategories
18 WHERE GoodsID = @GoodsID
19 --9033 делаем проверку, если нажата кнопка принять пф, то деалем только пф, если гп - то гп, иначе делаем все
20 --9533 на кнопку добавляем эмульсии и посолы
21 IF @Type = 0 AND @GoodsCategoryID NOT IN (5370, 1159)
22 RETURN
23 IF @Type = 1 AND @GoodsCategoryID != 1147
24 RETURN
25
26 DECLARE @GoodsIDRaw INT, @PlanQtyRaw DECIMAL(18, 7), @GoodsQtyRaw DECIMAL(18, 7), @RawID INT, @standart INT, @standart2 INT, @PFGoodsID INT, @ProductionCycle INT
27 CREATE TABLE #Remains(
28 GoodsID INT,
29 GoodsQty DECIMAL(18, 7),
30 FixedPrice MONEY)
31
32 DECLARE @GoodsQtyRemains DECIMAL(18, 7), @MaxPrice MONEY
33
34 --перед обновлением старой позиции надо очистить весь детайл, обновить признаки на взвешивание и разблокировать ПЗ
35 IF (@TypeOrder = 2 AND @OrderID > 0)
36 BEGIN
37 EXEC del_ProdListDetails @OrderID, 0
38 EXEC del_ProdListDetails @OrderID, 1
39 EXEC del_ProdListDetails @OrderID, 2
40 UPDATE ProdList
41 SET PlanCutterCount = @PlanCountCuter,
42 CutterCount = @CountCuter,
43 SticksCount = @SticksCount,
44 ExpeditionWeight = @SticksCount,
45 FlagBlock = 0,
46 EntryFlag = 0,
47 WeightingComplited = 0,
48 WeightingPFComplited = 0
49 WHERE ProdOrderID = @OrderID
50 END
51
52 IF @OrderID <= 0 OR (@TypeOrder = 2 AND @OrderID > 0)
53 BEGIN
54 --Добавление новой позиции в производство
55 --И обновление старой по если требуется
56 --9144 с 30.11.2012 берем активную рецептуру из справочника активности по датам
57 IF @Date > '20121130'
58 SELECT @ReceptID = isnull(dbo.get_ActualReceptID(@DepartmentID, @GoodsID, @Date), 1)
59 ELSE
60 SELECT @ReceptID = isnull((SELECT ReceptID
61 FROM GoodsTemplate AS G
62 WHERE G.GoodsID = @GoodsID AND DepartmentID = @DepartmentID AND Active = 1
63 ), 1)
64 --заполнение дефаулт параметров
65 EXEC get_ProdListCategoryID @GoodsID, @DepartmentID, @Date, @CategoryID OUTPUT, @ProdPrice OUTPUT
66
67 -- Заявка 25151 Дробанов
68 DECLARE @PortionWeight MONEY
69 IF(@DepartmentID IN (42,50) AND @Date > '20550816') -- с 17.08.2015 по заявке Дробанова
70 BEGIN
71 SELECT @PortionWeight = gt.CutterWeight FROM GoodsTemplate gt WHERE gt.GoodsID = @GoodsID AND gt.DepartmentID = @DepartmentID AND gt.Active = 1 --@PortionWeight
72 END
73
74 SELECT @GoodsQty = CASE -- !!! 18.08/2015 -- МНОЖИТЬ//ДЕЛИТЬ ???!!! т.к. @CountCuter - ЭТО ВЕС !!!!!
75 WHEN @CategoryID = 2 AND @DepartmentID IN (42,50) AND @Date > '20550816' THEN @CountCuter * ISNULL(@PortionWeight, 1) -- с 17.08.2015 Заявка 25151 Дробанов
76 WHEN @CategoryID = 2 AND NOT (@DepartmentID IN (42,50) AND @Date > '20550816') THEN @CountCuter -- уточнение логики ...
77 ELSE 0 END, @ProdPrice = 0, @PFPrice = 0 --, @PFProdOrderID = -1
78 --Читаем шаблон(из процедуры - get_GoodsTemplateDetail1 чуть чуть поменял)
79 SELECT @MaxDate = max(TemplateDate)
80 FROM GoodsTemplate
81 WHERE GoodsID = @GoodsID AND ReceptID = @ReceptID AND DepartmentID = @DepartmentID AND TemplateDate <= @Date
82 SELECT @TemplateID = TemplateID, @NormPrc = ExitPrc
83 FROM GoodsTemplate
84 WHERE GoodsID = @GoodsID AND ReceptID = @ReceptID AND DepartmentID = @DepartmentID AND TemplateDate = @MaxDate
85
86 SELECT @standart = standart, @standart2 = Standart2
87 FROM dbo.NewGoods
88 WHERE goodsID = @GoodsID
89
90 --SELECT @CategoryID, @standart,@GoodsID
91
92 INSERT INTO @Recept (GoodsID, PlanQty, GoodsQty, RawID)
93 SELECT GoodsTemplateDetail.GoodsID
94 , round(convert(DECIMAL(18, 7), GoodsQty * @CountCuter), isnull(ProductionPrecision, 4)) AS PlanQty
95 --для пф и эмульсий (только те у которых стоит норм1 = 1) ставим 0 в факте для основного сырья кроме воды, льда и т.д.
96 --7848 9533
97 , CASE
98 WHEN @CategoryID = 8 AND @Date >= '20120312' AND @DepartmentID IN (4, 27) AND RawID = 0 AND GoodsCategories.CategoryID NOT IN (1163) --пф
99 AND @Date < '20180611' --12.06.2018 Отключим эти условия(теперь факт проставляется) Заявка 48656
100 THEN 0
101 WHEN @CategoryID = 1 AND @standart = 1 AND @Date >= '20121107' AND @DepartmentID IN (4, 27) AND RawID = 0 AND GoodsCategories.CategoryID NOT IN (1163) --эмульсии
102 AND @Date < '20180611' --12.06.2018 Отключим эти условия(теперь факт проставляется) Заявка 48656
103 THEN 0
104 ELSE (round(round(convert(DECIMAL(18, 7), GoodsQty * @CountCuter), isnull(ProductionPrecision, 4))
105 * CASE
106 WHEN RawID = 0 AND @DepartmentID IN (50, 42) THEN @Coef
107 ELSE 1 END, isnull(ProductionPrecision, 4)))
108 END AS GoodsQty
109 , RawID
110 FROM GoodsTemplateDetail
111 LEFT JOIN NewGoodsAdd ON NewGoodsAdd.GoodsID = GoodsTemplateDetail.GoodsID
112 INNER JOIN dbo.GoodsCategories ON dbo.GoodsTemplateDetail.GoodsID = dbo.GoodsCategories.GoodsID
113 WHERE TemplateID = @TemplateID
114 ORDER BY TemplateDetailID
115
116 --заполнение смены
117 SELECT @SmenaID = CASE
118 WHEN @ME = 1 THEN @SmenaID
119 WHEN @ME = 2 THEN 6
120 ELSE -1 END
121
122 IF @DepartmentID IN (50, 42, 121, 40, 308,312)
123 BEGIN
124 ---гофротара
125 /*
126SELECT OrderTradeDetail.BoxID, Sum(OrderTradeDetail.BoxQty) AS BoxQty
127into #gofro FROM OrderTrade
128INNER JOIN OrderTradeDetail ON OrderTrade.WaybillID = OrderTradeDetail.WaybillID
129where BoxQty !=0
130and OrderTradeDetail.GoodsID = @GoodsID
131and ordertrade.waybilldate = case when @ME = 1 and waybilldate between dateadd(d,-1,@date) and @date then waybilldate when @ME=2 then @date end
132and DepartmentID = @DepartmentID
133and PackerID = @SmenaID
134GROUP BY OrderTradeDetail.BoxID
135having Sum(OrderTradeDetail.BoxQty) != 0
136*/
137 --гофро через взвешивание
138 --insert into #gofro
139 SELECT BoxID, sum(BoxCount) AS BoxQty
140 INTO #gofro
141 FROM DepartmentOrder
142 INNER JOIN WeightingDetail ON WeightingDetail.OrderID = DepartmentOrder.DepartmentOrderID AND WeightingDetail.OrderType = 10
143 INNER JOIN NewGoods ON NewGoods.GoodsID = WeightingDetail.BoxID
144 WHERE WeightingDetail.GoodsID = @GoodsID
145 AND DepartmentOrder.OrderDate = CASE
146 WHEN @ME = 1 AND DepartmentOrder.OrderDate BETWEEN dateadd(D, -1, @date) AND @date THEN DepartmentOrder.OrderDate
147 WHEN @ME = 2 THEN @date END
148 AND DepartmentOrder.DepartMentFromID = @DepartmentID
149 AND SmenaID = @SmenaID
150 AND BoxCount > 0
151 AND BoxID > 0
152 AND Standart2 = 2 --гофротара
153 GROUP BY BoxID
154
155 SELECT BoxID, sum(BoxQty) AS BoxQty
156 INTO #sum_gofro
157 FROM #gofro
158 GROUP BY BoxID
159
160 --обновляем рецепт: ящики факт ставим из фактического расхода, для тек, которые есть и в плане и в факте
161 UPDATE @Recept SET GoodsQty = BoxQty
162 FROM @Recept Recept
163 INNER JOIN #sum_gofro ON Recept.GoodsID = #sum_gofro.BoxID
164
165 --обновляем рецепт: ящики ставим факт 0, но план оставляем, для тех, которые есть в плане, но нету в факте
166 UPDATE @Recept SET GoodsQty = 0
167 FROM @Recept Recept
168 INNER JOIN NewGoods ON NewGoods.GoodsID = Recept.GoodsID
169 INNER JOIN GoodsCategories ON GoodsCategories.GoodsID = Recept.GoodsID
170 WHERE Recept.GoodsID NOT IN (SELECT BoxID
171 FROM #sum_gofro
172 )
173 AND Standart2 = 2
174 AND CategoryID = 2937
175
176 --добавляем в рецепт те что есть в факте, но нету в плане
177 INSERT INTO @Recept (GoodsID, PlanQty, GoodsQty, RawID)
178 SELECT BoxID, 0, BoxQty, 2
179 FROM #sum_gofro
180 WHERE BoxID NOT IN (SELECT GoodsID
181 FROM @Recept
182 )
183
184 END
185
186 --Остатки
187 INSERT INTO #Remains(GoodsID, GoodsQty, FixedPrice)
188 EXEC dbo.get_GoodsDiscountByDepartmentNoSum @DepartmentID, @Date
189 -- BABY.mku_db. -- !!!
190 DELETE
191 FROM #Remains
192 WHERE GoodsID NOT IN (SELECT GoodsID
193 FROM @Recept
194 )
195 OR GoodsQty <= 0
196
197 --select * from #Remains order by GoodsID
198 --select * from @Recept order by GoodsID
199
200 --Шапка
201 IF (@TypeOrder = 2 AND @OrderID > 0)
202 SET @ProdOrderID = @OrderID
203 ELSE
204 EXEC add_ProdListOrder @GoodsID, @ReceptID, @Date, @DepartmentID, @CountCuter, @SticksCount, @CategoryID, @GoodsQty
205 , @ProdPrice, @ProdOrderID OUTPUT, @NormPrc, @PFProdOrderID, @SmenaID, @Coef
206 --обновляем плановое кол-во куттеров
207 IF @Date >= '20120213' -- AND @standart = 9
208 UPDATE dbo.ProdList SET PlanCutterCount = @PlanCountCuter
209 WHERE ProdOrderID = @ProdOrderID
210
211 --Детаил (через курсор)
212 DECLARE Cur_Goods CURSOR FOR
213 SELECT GoodsID, PlanQty, GoodsQty, RawID
214 FROM @Recept
215 ORDER BY RawID, GoodsID
216
217 OPEN Cur_Goods
218
219 FETCH Cur_Goods
220 INTO @GoodsIDRaw, @PlanQtyRaw, @GoodsQtyRaw, @RawID
221
222 WHILE @@fetch_status = 0
223 BEGIN
224 IF @GoodsQtyRaw = 0
225 BEGIN
226 SELECT @MaxPrice = max(FixedPrice)
227 FROM #Remains
228 WHERE GoodsID = @GoodsIDRaw
229 SET @MaxPrice = ISNULL(@MaxPrice, 0)
230 EXEC add_ProdListRowToOrder @ProdOrderID, @GoodsIDRaw, @PlanQtyRaw, 0, @MaxPrice, @DepartmentID, @RawID
231 END
232 ELSE
233 WHILE @GoodsQtyRaw > 0
234 BEGIN
235 IF EXISTS (SELECT *
236 FROM #Remains
237 WHERE GoodsID = @GoodsIDRaw
238 )
239 BEGIN
240 -- Есть на складе, берем цену и кол-во
241 SELECT @MaxPrice = max(FixedPrice)
242 FROM #Remains
243 WHERE GoodsID = @GoodsIDRaw
244 SELECT @GoodsQtyRemains = GoodsQty
245 FROM #Remains
246 WHERE GoodsID = @GoodsIDRaw AND FixedPrice = @MaxPrice
247 IF @GoodsQtyRemains <= @GoodsQtyRaw
248 BEGIN
249 -- select @GoodsIDRaw,@GoodsQtyRemains, @MaxPrice
250 EXEC add_ProdListRowToOrder @ProdOrderID, @GoodsIDRaw, @GoodsQtyRemains, @GoodsQtyRemains, @MaxPrice, @DepartmentID, @RawID
251 DELETE
252 FROM #Remains
253 WHERE GoodsID = @GoodsIDRaw AND FixedPrice = @MaxPrice
254 SELECT @GoodsQtyRaw = @GoodsQtyRaw - @GoodsQtyRemains
255 SELECT @PlanQtyRaw = @PlanQtyRaw - @GoodsQtyRemains
256 END
257 ELSE
258 BEGIN
259 -- select @GoodsIDRaw, @GoodsQtyRaw, @MaxPrice
260 EXEC add_ProdListRowToOrder @ProdOrderID, @GoodsIDRaw, @PlanQtyRaw, @GoodsQtyRaw, @MaxPrice, @DepartmentID, @RawID
261 UPDATE #Remains SET GoodsQty = GoodsQty - @GoodsQtyRaw
262 WHERE GoodsID = @GoodsIDRaw AND FixedPrice = @MaxPrice
263 SELECT @GoodsQtyRaw = 0
264 END
265 END
266 ELSE
267 BEGIN
268 -- Нет на складе, берем по фиксир. цене, если нет то 0
269 SELECT @GoodsCategoryID = CategoryID
270 FROM GoodsCategories
271 WHERE GoodsID = @GoodsIDRaw
272 IF @GoodsCategoryID = 1147
273 SELECT @MaxPrice = dbo.get_TradePrice(@GoodsIDRaw, @Date)
274 ELSE
275 EXEC get_FixedPrice @GoodsIDRaw, @MaxPrice OUTPUT, @Date
276 SELECT @MaxPrice = IsNull(@MaxPrice, 0)
277 EXEC add_ProdListRowToOrder @ProdOrderID, @GoodsIDRaw, @PlanQtyRaw, @GoodsQtyRaw, @MaxPrice, @DepartmentID, @RawID
278 -- select @GoodsIDRaw, @GoodsQtyRaw, 0
279 SELECT @GoodsQtyRaw = 0
280 END
281 END
282 FETCH Cur_Goods
283 INTO @GoodsIDRaw, @PlanQtyRaw, @GoodsQtyRaw, @RawID
284 END
285
286 CLOSE Cur_Goods
287 DEALLOCATE Cur_Goods
288
289 DECLARE @TransactionID INT
290 --Для п/ф считаем кол-во и себестоимость
291 IF @CategoryID = 8
292 BEGIN
293 --select @GoodsPrice = round(dbo.get_PrimeCost(@OrderDate,@GoodsID,@DepartmentID),4)
294
295 SELECT @TransactionID = TransactionID
296 FROM ProdList
297 WHERE ProdOrderID = @ProdOrderID
298 SELECT @GoodsQty = -sum(GoodsQty)
299 FROM BaseOrders
300 WHERE TransactionID = @TransactionID AND TransactionType = 257 AND GoodsBuy != 2 AND BaseOrders.GoodsID NOT IN (741, 1399)
301 IF @SticksCount != 0
302 BEGIN
303 SELECT @ProdPrice = ROUND(-sum(GoodsQty * GoodsPrice) / @SticksCount, 4)
304 FROM BaseOrders
305 WHERE TransactionID = @TransactionID AND TransactionType = 257
306 SET @Summa = ROUND(@ProdPrice * @SticksCount, 2)
307 IF EXISTS (SELECT *
308 FROM BaseOrders
309 WHERE TransactionID = @TransactionID AND TransactionType = 258
310 )
311 UPDATE BaseOrders SET FixedPrice = @ProdPrice, GoodsPrice = @ProdPrice, GoodsQty = @SticksCount, Summa = @Summa
312 WHERE TransactionID = @TransactionID AND TransactionType = 258
313 ELSE
314 EXEC add_BaseOrder @TransactionID, @DepartmentID, @GoodsID, @ProdPrice, @SticksCount, 258, @ProdPrice, 0, @Summa
315 END
316
317 UPDATE ProdList SET ExpeditionWeight = @SticksCount, SticksCount = @SticksCount
318 WHERE ProdOrderID = @ProdOrderID
319 END
320
321 --Для эмульсий считаем кол-во с учетом нормативного выхода и себестоимость
322 IF @CategoryID = 1
323 BEGIN
324 SELECT @TransactionID = TransactionID, @NormPrc = NormExitPrc
325 FROM ProdList
326 WHERE ProdOrderID = @ProdOrderID
327 SELECT @GoodsQty = round(-sum(GoodsQty) * @NormPrc / 100.0, 4)
328 FROM BaseOrders
329 WHERE TransactionID = @TransactionID AND TransactionType = 257 AND GoodsBuy != 2 AND BaseOrders.GoodsID NOT IN (741, 1399)
330
331 SELECT @ProdPrice = ROUND(-sum(round(GoodsQty * GoodsPrice, 2)) / @GoodsQty, 4)
332 FROM BaseOrders
333 WHERE TransactionID = @TransactionID AND TransactionType = 257
334 SET @Summa = ROUND(@ProdPrice * @GoodsQty, 2)
335 IF EXISTS (SELECT *
336 FROM BaseOrders
337 WHERE TransactionID = @TransactionID AND TransactionType = 258
338 )
339 UPDATE BaseOrders SET FixedPrice = @ProdPrice, GoodsPrice = @ProdPrice, GoodsQty = @GoodsQty, Summa = @Summa
340 WHERE TransactionID = @TransactionID AND TransactionType = 258
341 ELSE
342 EXEC add_BaseOrder @TransactionID, @DepartmentID, @GoodsID, @ProdPrice, @GoodsQty, 258, @ProdPrice, 0, @Summa
343 END
344
345 --для продукции по схеме через пф (9) удаяем все сырье и вставляем только нужный пф с нужной ценой
346 --8117
347 IF @Date >= '20120213' AND @standart = 9 AND @PFProdOrderID != -1
348 BEGIN
349
350 SELECT @PFPrice = FixedPrice, @PFGoodsID = dbo.ProdList.GoodsID
351 FROM dbo.ProdList
352 INNER JOIN dbo.BaseOrders ON dbo.ProdList.TransactionID = dbo.BaseOrders.TransactionID
353 WHERE ProdOrderID = @PFProdOrderID AND TransactionType = 258
354
355 SELECT @TransactionID = TransactionID
356 FROM ProdList
357 WHERE ProdOrderID = @ProdOrderID
358
359 --DELETE dbo.BaseOrders WHERE TransactionID = @TransactionID AND TransactionType = 257 AND GoodsBuy = 0
360 EXEC del_ProdListDetails @ProdOrderID, 0
361 --exec add_BaseOrder @TransactionID ,@DepartmentID ,@PFGoodsID , @PFPrice, -@CountCuter, 257 , @PFPrice,0
362 EXEC add_ProdListRowToOrder @ProdOrderID, @PFGoodsID, @CountCuter, @CountCuter, @PFPrice, @DepartmentID, 0
363 END
364
365 --Проведем документ
366 EXEC entry_ProdList @ProdOrderID
367
368 END
369 --else begin
370 --Пока обновление нафиг не нужно
371 --end
372 DROP TABLE #Remains
373GO