· 6 years ago · Jul 29, 2019, 03:04 PM
1USE Lozon;
2GO
3SET ANSI_NULLS ON;
4
5SET QUOTED_IDENTIFIER ON;
6GO
7IF (OBJECT_ID(N'Sorter.GetManualReductNode', N'P') IS NULL)
8BEGIN
9 EXEC (N'CREATE PROCEDURE Sorter.GetManualReductNode AS RETURN 0');
10END;
11GO
12EXECUTE dbo.ObjectDescriptionSet
13 @ObjectName = 'Sorter.GetManualReductNode'
14, @Description = 'Получение вариантов физических ограничений для постинга';
15GO
16
17ALTER PROCEDURE Sorter.GetManualReductNode
18 @ID dbo.BIDENT
19 , @Weight dbo.BIDENT = 0
20 , @Length dbo.BIDENT = 0
21 , @Width dbo.BIDENT = 0
22 , @Height dbo.BIDENT = 0
23 , @ConditionParamsManualReducts dbo.string OUTPUT
24
25 , @CntArticleInPosting int OUTPUT
26 , @ArticleWeight float OUTPUT
27 , @MinDimension dbo.BIDENT OUTPUT
28 , @MiddleDimension dbo.BIDENT OUTPUT
29 , @MaxDimension dbo.BIDENT OUTPUT
30 , @SumDimensions dbo.BIDENT OUTPUT
31 , @Price money OUTPUT
32 , @ArticlePay money OUTPUT
33 , @isPrepay bit OUTPUT
34 , @isLegal bit OUTPUT
35
36AS
37BEGIN
38 SET NOCOUNT ON;
39
40DECLARE
41 @w1 float
42 , @w2 float
43 , @w3 float
44 , @ObjectTypeID_ObjectAttributeMaterialWeight dbo.BIDENT = 2939084000 --'ObjectAttributeMaterialWeight'
45 , @ObjectTypeID_ObjectAttributeMaterialWeightExternal dbo.BIDENT = 1618489823000 --'ObjectAttributeMaterialWeightExternal'
46
47 , @ObjectTypeID_ObjectAttributeMaterialWidthExternal dbo.BIDENT = 5927697887000 --'ObjectAttributeMaterialWidthExternal'
48 , @ObjectTypeID_ObjectAttributeMaterialHeightExternal dbo.BIDENT = 5927697647000 --'ObjectAttributeMaterialHeightExternal'
49 , @ObjectTypeID_ObjectAttributeMaterialLengthExternal dbo.BIDENT = 5927697989000 --'ObjectAttributeMaterialLengthExternal'
50
51 , @ObjectAttributeMoneyClientPrice_TypeID dbo.BIDENT = 2939068000 --ExtValueClientOrderID
52 , @ObjectAttributeMoneyPrice_TypeID dbo.BIDENT = 2939074000 --ObjectAttributeMoneyPrice
53
54 ;
55
56 -- Инициализация
57 SELECT
58 @CntArticleInPosting = 0
59 , @MinDimension = 0
60 , @MiddleDimension = 0
61 , @MaxDimension = 0
62 , @SumDimensions = 0
63 , @Price = 0
64 , @ArticlePay = 0
65 , @isPrepay = 0
66 , @isLegal = 0
67 , @ConditionParamsManualReducts = NULL;
68
69select
70 @CntArticleInPosting = count(distinct CA.ArticleID)
71from
72 dbo.ContainerArticle CA
73where
74 CA.ContainerID = @ID
75 AND CA.MomentOut IS NULL;
76
77-- если вандер отправил нулевой вес -> берем из БД
78SET @ArticleWeight = @Weight
79IF (@Weight = 0)
80 BEGIN
81 SELECT TOP (1)
82 @w1 = OA.[Value]
83 FROM
84 dbo.ObjectAttribute OA WITH (NOLOCK)
85 WHERE
86 (OA.OwnerObjectID = @ID)
87 AND (OA.ObjectTypeID = @ObjectTypeID_ObjectAttributeMaterialWeight)
88 OPTION (KEEP PLAN);
89
90 SELECT TOP (1)
91 @w2 = OA.[Value]
92 FROM
93 dbo.ObjectAttribute OA WITH (NOLOCK)
94 WHERE
95 (OA.OwnerObjectID = @ID)
96 AND (OA.ObjectTypeID = @ObjectTypeID_ObjectAttributeMaterialWeightExternal)
97 OPTION (KEEP PLAN, FAST 1);
98
99 SELECT
100 @w3 = SUM(OA.[Value])
101 FROM
102 dbo.ContainerArticle CA WITH (NOLOCK)
103 INNER JOIN dbo.ObjectAttribute OA WITH (NOLOCK) ON (OA.OwnerObjectID = CA.ArticleID)
104 AND (OA.ObjectTypeID = @ObjectTypeID_ObjectAttributeMaterialWeight)
105 WHERE
106 (CA.ContainerID = @ID)
107 AND (CA.MomentOut IS NULL)
108 AND (OA.[Value] IS NOT NULL)
109 OPTION (KEEP PLAN);
110
111 SET @ArticleWeight = ISNULL(CAST(COALESCE(@w1, @w2, @w3) AS bigint), 0);
112END;
113
114-- если вандер отправил нулевые ОВХ -> берем из внешних систем
115IF (@Width = 0 OR @Width IS NULL OR @Height = 0 OR @Height IS NULL OR @Length = 0 OR @Length IS NULL)
116BEGIN
117 SELECT TOP (1)
118 @Width = OA.Value
119 FROM
120 dbo.ObjectAttribute OA WITH (NOLOCK)
121 WHERE
122 OA.OwnerObjectID = @ID
123 AND OA.ObjectTypeID = @ObjectTypeID_ObjectAttributeMaterialWidthExternal
124 OPTION (KEEP PLAN);
125
126 SELECT TOP (1)
127 @Height = OA.Value
128 FROM
129 dbo.ObjectAttribute OA WITH (NOLOCK)
130 WHERE
131 OA.OwnerObjectID = @ID
132 AND OA.ObjectTypeID = @ObjectTypeID_ObjectAttributeMaterialHeightExternal
133 OPTION (KEEP PLAN);
134
135 SELECT TOP (1)
136 @Length = OA.Value
137 FROM
138 dbo.ObjectAttribute OA WITH (NOLOCK)
139 WHERE
140 OA.OwnerObjectID = @ID
141 AND OA.ObjectTypeID = @ObjectTypeID_ObjectAttributeMaterialLengthExternal
142 OPTION (KEEP PLAN);
143
144SET @ConditionParamsManualReducts = 'use external WHL: '
145+ 'external Width = ' + ISNULL(CONVERT(varchar(255), @Width), '')
146+ ', external Height = ' + ISNULL(CONVERT(varchar(255), @Height), '')
147+ ', external Length = ' + ISNULL(CONVERT(varchar(255), @Length), '')
148
149END;
150
151---
152SELECT
153 @MinDimension = [1]
154, @MiddleDimension = [2]
155, @MaxDimension = [3]
156FROM ( SELECT
157 ROW_NUMBER() OVER (ORDER BY
158 Src.Parameter ASC) AS ID
159 , Src.Parameter
160 FROM ( SELECT
161 @Length AS Parameter
162 UNION ALL
163 SELECT
164 @Width
165 UNION ALL
166 SELECT
167 @Height) Src ) x
168PIVOT ( MAX(Parameter)
169 FOR ID IN ([1], [2], [3])) PivotTable;
170
171 set @SumDimensions = @Length + @Width + @Height;
172
173 -- Legal
174 SELECT
175 @isLegal = dbo.ObjectIs(AP.PersonHumanID, 'PersonLegal')
176 FROM
177 dbo.ArticlePosting AP WITH (NOLOCK)
178 WHERE
179 (AP.ID = @ID)
180 OPTION (KEEP PLAN, FAST 1);
181
182
183 -- Price
184 BEGIN
185 SELECT
186 @Price = OA.[Value]
187 FROM
188 dbo.ObjectAttribute OA WITH (NOLOCK, FORCESEEK, INDEX = IX_ObjectAttribute_OwnerObjectID_ObjectTypeID)
189 WHERE
190 (OA.OwnerObjectID = @ID)
191 AND (OA.ObjectTypeID = @ObjectAttributeMoneyPrice_TypeID)
192 OPTION (KEEP PLAN, FAST 1);
193
194 SET @Price = ISNULL(@Price, 0);
195 END;
196
197
198
199 -- Prepay
200SELECT
201 @ArticlePay = OA.[Value]
202FROM
203 dbo.ObjectAttribute OA WITH (NOLOCK, FORCESEEK, INDEX = IX_ObjectAttribute_OwnerObjectID_ObjectTypeID)
204WHERE
205 (OA.OwnerObjectID = @ID)
206 AND (OA.ObjectTypeID = @ObjectAttributeMoneyClientPrice_TypeID)
207OPTION (KEEP PLAN, FAST 1);
208
209-- если с клиента ноль -> полная предоплата
210IF (@ArticlePay = 0)
211BEGIN
212 SET @isPrepay = 1
213END
214
215INSERT INTO #Sorter_GetChute_ManualReduct
216 ( ID, NodeReduct )
217SELECT
218 MR.ID
219 , MR.NodeReduct
220FROM
221 Sorter.ManualReduct MR
222WHERE
223 ( MR.MinItemsQTY IS NULL
224 OR MR.MinItemsQTY <= @CntArticleInPosting)
225 AND ( MR.MaxItemsQTY IS NULL
226 OR MR.MaxItemsQTY >= @CntArticleInPosting)
227 AND ( MR.MaxWeight IS NULL OR MR.MaxWeight >= @ArticleWeight)
228 AND ( MR.MaxHeight IS NULL OR MR.MaxHeight >= @MinDimension)
229 AND ( MR.MaxWidth IS NULL OR MR.MaxWidth >= @MiddleDimension)
230 AND ( MR.MaxLength IS NULL OR MR.MaxLength >= @MaxDimension)
231 AND ( MR.Max3DSum IS NULL OR MR.Max3DSum >= @SumDimensions)
232 AND ( MR.MaxPrice IS NULL OR MR.MaxPrice >= @Price)
233 AND ( MR.isPrepay IS NULL OR MR.isPrepay = @isPrepay)
234 AND ( MR.isLegal IS NULL OR MR.isLegal = @isLegal)
235
236
237
238DELETE FROM #Sorter_GetChute_SortingFlow_Reduce
239WHERE V_ManualReducts IS NOT NULL
240 AND NOT EXISTS (
241 SELECT
242 1
243 FROM
244 #Sorter_GetChute_ManualReduct MR
245 WHERE
246 V_ManualReducts = MR.NodeReduct
247 )
248
249END;
250
251GO
252-- --
253EXECUTE dbo.SysComponentRegister
254 @ID = NULL
255, @SysName = N'Sorter.GetManualReductNode';
256GO
257
258SET ANSI_NULLS ON;
259
260SET QUOTED_IDENTIFIER ON;
261GO
262IF (OBJECT_ID(N'SorterNM.GetSupplyByOzonNode', N'P') IS NULL)
263BEGIN
264 EXEC (N'CREATE PROCEDURE SorterNM.GetSupplyByOzonNode AS RETURN 0');
265END;
266GO
267EXECUTE dbo.ObjectDescriptionSet
268 @ObjectName = 'SorterNM.GetSupplyByOzonNode'
269, @Description = 'Получение вариантов собственной доставки для постинга';
270GO
271
272ALTER PROCEDURE SorterNM.GetSupplyByOzonNode
273 @ID dbo.BIDENT
274 , @PolygonID dbo.BIDENT
275 , @DeliveryVariantID dbo.BIDENT
276 , @DeliveryTypeID dbo.BIDENT
277 , @PolygonOwnerID dbo.BIDENT
278 , @IsOurDelivery bit
279 , @DstPlaceID dbo.BIDENT = NULL
280 , @ConditionParamsSupplyByOzon dbo.string OUTPUT
281
282 , @PolygonObjectTypeID dbo.BIDENT OUTPUT
283
284-- procedure result - INSERT INTO #Sorter_GetChute_SupplyByOzon
285
286AS
287BEGIN
288 SET NOCOUNT ON;
289
290IF OBJECT_ID(N'tempdb..#Sorter_GetChute_SupplyByOzon_TempNodes', N'U') IS NOT NULL
291BEGIN
292 DROP TABLE #Sorter_GetChute_SupplyByOzon_TempNodes;
293END;
294
295CREATE TABLE #Sorter_GetChute_SupplyByOzon_TempNodes
296(
297 ID dbo.BIDENT NOT NULL
298 ,
299 NodeSC dbo.string
300 ,
301 RelatePriority dbo.string
302 ,
303 Priority int
304);
305
306
307 SELECT @ConditionParamsSupplyByOzon = NULL;
308
309-- слой
310SELECT top(1)
311 @PolygonObjectTypeID = O.ObjectTypeID
312FROM
313 dbo.Object O WITH (NOLOCK)
314WHERE
315 O.ID = @PolygonID;
316
317INSERT INTO #Sorter_GetChute_SupplyByOzon_TempNodes
318 ( ID, NodeSC, RelatePriority, Priority )
319SELECT
320 SO.ID
321 , SO.NodeSC
322 , SO.RelatePriority
323 , SO.Priority
324FROM
325 SorterNM.SupplyByOzon SO
326WHERE
327 ( SO.PolygonID IS NULL
328 OR SO.PolygonID = @PolygonObjectTypeID)
329 AND
330 ( SO.PolygonOwnerID IS NULL
331 OR SO.PolygonOwnerID = @PolygonOwnerID)
332 AND
333 ( SO.DeliveryTypeID IS NULL
334 OR SO.DeliveryTypeID = @DeliveryTypeID)
335 AND
336 ( SO.IsOurDelivery IS NULL
337 OR SO.IsOurDelivery = @IsOurDelivery)
338 AND
339 ( SO.DeliveryVariantID IS NULL
340 OR SO.DeliveryVariantID = @DeliveryVariantID)
341 AND
342 ( SO.DstPlaceID IS NULL
343 OR SO.DstPlaceID = @DstPlaceID)
344
345INSERT INTO #Sorter_GetChute_SupplyByOzon
346 ( ID, NodeSC )
347SELECT
348 TN.ID
349 , TN.NodeSC
350FROM
351 #Sorter_GetChute_SupplyByOzon_TempNodes TN
352WHERE
353 TN.RelatePriority IS NULL
354
355INSERT INTO #Sorter_GetChute_SupplyByOzon
356 ( ID, NodeSC )
357SELECT
358 RN.ID
359 , RN.NodeSC
360FROM (
361SELECT
362 row_number() over (partition BY RelatePriority ORDER BY Priority ) as num
363 , TN.NodeSC
364 , TN.ID
365 FROM
366 #Sorter_GetChute_SupplyByOzon_TempNodes TN
367 WHERE
368 TN.RelatePriority IS NOT NULL
369 ) RN
370WHERE num = 1
371
372
373
374DELETE FROM #Sorter_GetChute_SortingFlow_Reduce
375WHERE II_SupplyByOzon IS NOT NULL
376AND NOT EXISTS (
377 SELECT
378 1
379 FROM
380 #Sorter_GetChute_SupplyByOzon SO
381 WHERE
382 II_SupplyByOzon = SO.NodeSC
383)
384
385 SET @ConditionParamsSupplyByOzon =
386 '; PolygonID = ' + ISNULL(CONVERT(varchar(255), @PolygonID), '')
387 + '; PolygonOwnerID = ' + ISNULL(CONVERT(varchar(255), @PolygonOwnerID), '')
388 + '; DeliveryTypeID = ' + ISNULL(CONVERT(varchar(255), @DeliveryTypeID), '')
389 + '; IsOurDelivery = ' + ISNULL(CONVERT(varchar(255), @IsOurDelivery), '')
390 + '; PolygonObjectTypeID = ' + ISNULL(CONVERT(varchar(255), @PolygonObjectTypeID), '')
391 ;
392END;
393
394GO
395
396EXECUTE dbo.SysComponentRegister
397 @ID = NULL
398, @SysName = N'SorterNM.GetSupplyByOzonNode';
399GO
400--USE Lozon;
401GO
402SET ANSI_NULLS ON;
403
404SET QUOTED_IDENTIFIER ON;
405GO
406IF (OBJECT_ID(N'SorterNM.GetSupplyBy3PLNode', N'P') IS NULL)
407BEGIN
408 EXEC (N'CREATE PROCEDURE SorterNM.GetSupplyBy3PLNode AS RETURN 0');
409END;
410GO
411EXECUTE dbo.ObjectDescriptionSet
412 @ObjectName = 'SorterNM.GetSupplyBy3PLNode'
413, @Description = 'Получение вариантов доставки 3PL для постинга';
414GO
415
416ALTER PROCEDURE SorterNM.GetSupplyBy3PLNode
417 @ID dbo.BIDENT
418 , @CurDateTime datetime
419 , @Polygon3PLID dbo.BIDENT
420 , @DeliveryTypeID dbo.BIDENT
421 , @DeliveryVariantID dbo.BIDENT
422 , @IsOurDelivery bit
423 , @DstPlaceID dbo.BIDENT = NULL
424 , @ConditionParamsSupplyBy3PL dbo.string OUTPUT
425
426 , @PolygonOwnerID dbo.BIDENT OUTPUT
427 , @PolygonObjectTypeID dbo.BIDENT OUTPUT
428
429-- procedure result - INSERT INTO #Sorter_GetChute_SupplyBy3PL
430
431AS
432BEGIN
433 SET NOCOUNT ON;
434
435-- DECLARE
436-- @PolygonOwnerID dbo.BIDENT
437-- , @PolygonObjectTypeID dbo.BIDENT
438-- ;
439
440 -- Инициализация
441 SELECT @ConditionParamsSupplyBy3PL = NULL;
442
443-- владелец полигона и слой
444 SELECT top(1)
445 @PolygonOwnerID = O.OwnerObjectID
446 , @PolygonObjectTypeID = O.ObjectTypeID
447 FROM
448 dbo.Object O WITH (NOLOCK)
449 WHERE
450 O.ID = @Polygon3PLID;
451
452IF OBJECT_ID(N'tempdb..#Sorter_GetChute_SupplyBy3PL_TempNodes', N'U') IS NOT NULL
453BEGIN
454 DROP TABLE #Sorter_GetChute_SupplyBy3PL_TempNodes;
455END;
456
457CREATE TABLE #Sorter_GetChute_SupplyBy3PL_TempNodes
458(
459 ID dbo.BIDENT NOT NULL
460 ,
461 Node3PL dbo.string
462 ,
463 RelatePriority dbo.string
464 ,
465 Priority int
466);
467
468-- настройки 3PL
469IF OBJECT_ID(N'tempdb..#Sorter_GetChute_QtyPostings3PL', N'U') IS NOT NULL
470BEGIN
471 DROP TABLE #Sorter_GetChute_QtyPostings3PL;
472END;
473
474CREATE TABLE #Sorter_GetChute_QtyPostings3PL
475(
476 ID dbo.BIDENT NOT NULL
477 , Node3PL dbo.string NULL
478 , Count int NULL
479 , MinQTY int NULL
480 , Rate int NULL
481 , MaxQTY int NULL
482 , Priority int NULL
483 , RelatePriority dbo.string NULL
484);
485
486INSERT INTO #Sorter_GetChute_QtyPostings3PL
487 (
488 Node3PL
489 , Count
490 , MinQTY
491 , Rate
492 , MaxQTY
493 , Priority
494 , RelatePriority
495 , ID
496 )
497SELECT
498 S3PL.Node3PL
499 , DC.Count
500 , S3PL.MinQTY
501 , S3PL.Rate
502 , S3PL.MaxQTY
503 , S3PL.Priority
504 , S3PL.RelatePriority
505 , S3PL.ID
506FROM
507 SorterNM.SupplyBy3PL S3PL
508 left join ( -- оптимизировать с помощью #Sorter_GetChute_SortingFlow_Reduce
509 select
510 D3PL.Name3PL
511 , SUM(D3PL.Count) as Count
512 from
513 Sorter.Divert3pl D3PL
514 where
515 EXISTS (
516 select
517 1
518 from
519 Sorter.Divert3pl DPL
520 inner join SorterNM.SortingTree ST on ST.II_SupplyBy3PL = DPL.Name3pl
521 inner join SorterNM.DeliveryTime DT on ST.III_DeliveryTime = DT.NodeTime
522 where
523 D3PL.id = DPL.id
524 AND DPL.DateDivert = CAST(DATEADD(day, DT.SortDayOffset , @CurDateTime) AS DATE)
525 AND DPL.SortStart = DT.CutInTime
526 AND DPL.SortEnd = DT.CutOffTime
527 )
528 GROUP BY
529 D3PL.Name3PL
530 ) DC ON DC.Name3PL = S3PL.Node3PL
531WHERE
532 ( S3PL.PolygonID IS NULL
533 OR S3PL.PolygonID = @PolygonObjectTypeID)
534 AND
535 ( S3PL.PolygonOwnerID IS NULL
536 OR S3PL.PolygonOwnerID = @PolygonOwnerID)
537 AND
538 ( S3PL.DeliveryTypeID IS NULL
539 OR S3PL.DeliveryTypeID = @DeliveryTypeID)
540 AND
541 ( S3PL.IsOurDelivery IS NULL
542 OR S3PL.IsOurDelivery = @IsOurDelivery)
543 AND
544 ( S3PL.DeliveryVariantID IS NULL
545 OR S3PL.DeliveryVariantID = @DeliveryVariantID)
546 AND
547 ( S3PL.DstPlaceID IS NULL
548 OR S3PL.DstPlaceID = @DstPlaceID)
549
550
551INSERT INTO #Sorter_GetChute_SupplyBy3PL_TempNodes
552 ( ID, Node3PL, Priority, RelatePriority )
553SELECT
554 TS.ID, TS.Node3PL, TS.Priority, TS.RelatePriority
555FROM
556 #Sorter_GetChute_QtyPostings3PL TS
557WHERE
558 TS.MinQTY <> 0 AND TS.MaxQTY <> 0 AND
559 (TS.Count IS NULL OR TS.Count < TS.MaxQTY)
560 AND (
561 (TS.Count IS NULL OR TS.Count = 0 OR TS.Count < TS.MinQTY)
562 OR (TS.Count >= TS.MinQTY AND TS.Count < TS.MaxQTY AND ((TS.Count - TS.MinQTY) % TS.Rate) > 0)
563 OR (TS.Count >= TS.MinQTY AND TS.Count < TS.MaxQTY AND ((TS.Count - TS.MinQTY) % TS.Rate) = 0
564 AND NOT EXISTS ( SELECT
565 1
566 FROM
567 #Sorter_GetChute_QtyPostings3PL TS
568 WHERE
569 TS.Count IS NULL OR TS.Count = 0 OR TS.Count < TS.MinQTY OR
570 (TS.Count >= TS.MinQTY AND TS.Count < TS.MaxQTY AND ((TS.Count - TS.MinQTY) % TS.Rate) > 0)
571 )
572 )
573 )
574
575INSERT INTO #Sorter_GetChute_SupplyBy3PL
576 ( ID, Node3PL )
577SELECT
578 TN.ID
579 , TN.Node3PL
580FROM
581 #Sorter_GetChute_SupplyBy3PL_TempNodes TN
582WHERE
583 TN.RelatePriority IS NULL
584
585INSERT INTO #Sorter_GetChute_SupplyBy3PL
586 ( ID, Node3PL )
587SELECT
588 RN.ID
589 , RN.Node3PL
590FROM (
591SELECT
592 row_number() over (partition BY RelatePriority ORDER BY Priority ) as num
593 , TN.Node3PL
594 , TN.ID
595 FROM
596 #Sorter_GetChute_SupplyBy3PL_TempNodes TN
597 WHERE
598 TN.RelatePriority IS NOT NULL
599 ) RN
600WHERE num = 1
601
602
603DELETE FROM #Sorter_GetChute_SortingFlow_Reduce
604WHERE II_SupplyBy3PL IS NOT NULL
605 AND NOT EXISTS (
606 SELECT
607 1
608 FROM
609 #Sorter_GetChute_SupplyBy3PL S3PL
610 WHERE
611 II_SupplyBy3PL = S3PL.Node3PL
612)
613
614 SET @ConditionParamsSupplyBy3PL = ''
615 + '; Polygon3PLOwnerID = ' + ISNULL(CONVERT(varchar(255), @PolygonOwnerID), '')
616 + '; PolygonObjectTypeID = ' + ISNULL(CONVERT(varchar(255), @PolygonObjectTypeID), '')
617;
618END;
619
620GO
621
622EXECUTE dbo.SysComponentRegister
623 @ID = NULL
624, @SysName = N'SorterNM.GetSupplyBy3PLNode';
625GO
626
627SET ANSI_NULLS ON;
628
629SET QUOTED_IDENTIFIER ON;
630GO
631IF (OBJECT_ID(N'SorterNM.GetManualReductNode', N'P') IS NULL)
632BEGIN
633 EXEC (N'CREATE PROCEDURE SorterNM.GetManualReductNode AS RETURN 0');
634END;
635GO
636EXECUTE dbo.ObjectDescriptionSet
637 @ObjectName = 'SorterNM.GetManualReductNode'
638, @Description = 'Получение вариантов физических ограничений для постинга';
639GO
640
641ALTER PROCEDURE SorterNM.GetManualReductNode
642 @ID dbo.BIDENT
643 , @Weight dbo.BIDENT = 0
644 , @Length dbo.BIDENT = 0
645 , @Width dbo.BIDENT = 0
646 , @Height dbo.BIDENT = 0
647 , @ConditionParamsManualReducts dbo.string OUTPUT
648
649 , @CntArticleInPosting int OUTPUT
650 , @ArticleWeight float OUTPUT
651 , @MinDimension dbo.BIDENT OUTPUT
652 , @MiddleDimension dbo.BIDENT OUTPUT
653 , @MaxDimension dbo.BIDENT OUTPUT
654 , @SumDimensions dbo.BIDENT OUTPUT
655 , @Price money OUTPUT
656 , @ArticlePay money OUTPUT
657 , @isPrepay bit OUTPUT
658 , @isLegal bit OUTPUT
659
660AS
661BEGIN
662 SET NOCOUNT ON;
663
664DECLARE
665 @w1 float
666 , @w2 float
667 , @w3 float
668 , @ObjectTypeID_ObjectAttributeMaterialWeight dbo.BIDENT = 2939084000 --'ObjectAttributeMaterialWeight'
669 , @ObjectTypeID_ObjectAttributeMaterialWeightExternal dbo.BIDENT = 1618489823000 --'ObjectAttributeMaterialWeightExternal'
670
671 , @ObjectTypeID_ObjectAttributeMaterialWidthExternal dbo.BIDENT = 5927697887000 --'ObjectAttributeMaterialWidthExternal'
672 , @ObjectTypeID_ObjectAttributeMaterialHeightExternal dbo.BIDENT = 5927697647000 --'ObjectAttributeMaterialHeightExternal'
673 , @ObjectTypeID_ObjectAttributeMaterialLengthExternal dbo.BIDENT = 5927697989000 --'ObjectAttributeMaterialLengthExternal'
674
675 , @ObjectAttributeMoneyClientPrice_TypeID dbo.BIDENT = 2939068000 --ExtValueClientOrderID
676 , @ObjectAttributeMoneyPrice_TypeID dbo.BIDENT = 2939074000 --ObjectAttributeMoneyPrice
677
678 ;
679
680 -- Инициализация
681 SELECT
682 @CntArticleInPosting = 0
683 , @MinDimension = 0
684 , @MiddleDimension = 0
685 , @MaxDimension = 0
686 , @SumDimensions = 0
687 , @Price = 0
688 , @ArticlePay = 0
689 , @isPrepay = 0
690 , @isLegal = 0
691 , @ConditionParamsManualReducts = NULL;
692
693select
694 @CntArticleInPosting = count(distinct CA.ArticleID)
695from
696 dbo.ContainerArticle CA
697where
698 CA.ContainerID = @ID
699 AND CA.MomentOut IS NULL;
700
701-- если вандер отправил нулевой вес -> берем из БД
702SET @ArticleWeight = @Weight
703IF (@Weight = 0)
704 BEGIN
705 SELECT TOP (1)
706 @w1 = OA.[Value]
707 FROM
708 dbo.ObjectAttribute OA WITH (NOLOCK)
709 WHERE
710 (OA.OwnerObjectID = @ID)
711 AND (OA.ObjectTypeID = @ObjectTypeID_ObjectAttributeMaterialWeight)
712 OPTION (KEEP PLAN);
713
714 SELECT TOP (1)
715 @w2 = OA.[Value]
716 FROM
717 dbo.ObjectAttribute OA WITH (NOLOCK)
718 WHERE
719 (OA.OwnerObjectID = @ID)
720 AND (OA.ObjectTypeID = @ObjectTypeID_ObjectAttributeMaterialWeightExternal)
721 OPTION (KEEP PLAN, FAST 1);
722
723 SELECT
724 @w3 = SUM(OA.[Value])
725 FROM
726 dbo.ContainerArticle CA WITH (NOLOCK)
727 INNER JOIN dbo.ObjectAttribute OA WITH (NOLOCK) ON (OA.OwnerObjectID = CA.ArticleID)
728 AND (OA.ObjectTypeID = @ObjectTypeID_ObjectAttributeMaterialWeight)
729 WHERE
730 (CA.ContainerID = @ID)
731 AND (CA.MomentOut IS NULL)
732 AND (OA.[Value] IS NOT NULL)
733 OPTION (KEEP PLAN);
734
735 SET @ArticleWeight = ISNULL(CAST(COALESCE(@w1, @w2, @w3) AS bigint), 0);
736END;
737
738-- если вандер отправил нулевые ОВХ -> берем из внешних систем
739IF (@Width = 0 OR @Width IS NULL OR @Height = 0 OR @Height IS NULL OR @Length = 0 OR @Length IS NULL)
740BEGIN
741 SELECT TOP (1)
742 @Width = OA.Value
743 FROM
744 dbo.ObjectAttribute OA WITH (NOLOCK)
745 WHERE
746 OA.OwnerObjectID = @ID
747 AND OA.ObjectTypeID = @ObjectTypeID_ObjectAttributeMaterialWidthExternal
748 OPTION (KEEP PLAN);
749
750 SELECT TOP (1)
751 @Height = OA.Value
752 FROM
753 dbo.ObjectAttribute OA WITH (NOLOCK)
754 WHERE
755 OA.OwnerObjectID = @ID
756 AND OA.ObjectTypeID = @ObjectTypeID_ObjectAttributeMaterialHeightExternal
757 OPTION (KEEP PLAN);
758
759 SELECT TOP (1)
760 @Length = OA.Value
761 FROM
762 dbo.ObjectAttribute OA WITH (NOLOCK)
763 WHERE
764 OA.OwnerObjectID = @ID
765 AND OA.ObjectTypeID = @ObjectTypeID_ObjectAttributeMaterialLengthExternal
766 OPTION (KEEP PLAN);
767
768SET @ConditionParamsManualReducts = 'use external WHL: '
769+ 'external Width = ' + ISNULL(CONVERT(varchar(255), @Width), '')
770+ ', external Height = ' + ISNULL(CONVERT(varchar(255), @Height), '')
771+ ', external Length = ' + ISNULL(CONVERT(varchar(255), @Length), '')
772
773END;
774
775---
776SELECT
777 @MinDimension = [1]
778, @MiddleDimension = [2]
779, @MaxDimension = [3]
780FROM ( SELECT
781 ROW_NUMBER() OVER (ORDER BY
782 Src.Parameter ASC) AS ID
783 , Src.Parameter
784 FROM ( SELECT
785 @Length AS Parameter
786 UNION ALL
787 SELECT
788 @Width
789 UNION ALL
790 SELECT
791 @Height) Src ) x
792PIVOT ( MAX(Parameter)
793 FOR ID IN ([1], [2], [3])) PivotTable;
794
795 set @SumDimensions = @Length + @Width + @Height;
796
797 -- Legal
798 SELECT
799 @isLegal = dbo.ObjectIs(AP.PersonHumanID, 'PersonLegal')
800 FROM
801 dbo.ArticlePosting AP WITH (NOLOCK)
802 WHERE
803 (AP.ID = @ID)
804 OPTION (KEEP PLAN, FAST 1);
805
806
807 -- Price
808 BEGIN
809 SELECT
810 @Price = OA.[Value]
811 FROM
812 dbo.ObjectAttribute OA WITH (NOLOCK, FORCESEEK, INDEX = IX_ObjectAttribute_OwnerObjectID_ObjectTypeID)
813 WHERE
814 (OA.OwnerObjectID = @ID)
815 AND (OA.ObjectTypeID = @ObjectAttributeMoneyPrice_TypeID)
816 OPTION (KEEP PLAN, FAST 1);
817
818 SET @Price = ISNULL(@Price, 0);
819 END;
820
821
822
823 -- Prepay
824SELECT
825 @ArticlePay = OA.[Value]
826FROM
827 dbo.ObjectAttribute OA WITH (NOLOCK, FORCESEEK, INDEX = IX_ObjectAttribute_OwnerObjectID_ObjectTypeID)
828WHERE
829 (OA.OwnerObjectID = @ID)
830 AND (OA.ObjectTypeID = @ObjectAttributeMoneyClientPrice_TypeID)
831OPTION (KEEP PLAN, FAST 1);
832
833-- если с клиента ноль -> полная предоплата
834IF (@ArticlePay = 0)
835BEGIN
836 SET @isPrepay = 1
837END
838
839INSERT INTO #Sorter_GetChute_ManualReduct
840 ( ID, NodeReduct )
841SELECT
842 MR.ID
843 , MR.NodeReduct
844FROM
845 SorterNM.ManualReduct MR
846WHERE
847 ( MR.MinItemsQTY IS NULL
848 OR MR.MinItemsQTY <= @CntArticleInPosting)
849 AND ( MR.MaxItemsQTY IS NULL
850 OR MR.MaxItemsQTY >= @CntArticleInPosting)
851 AND ( MR.MaxWeight IS NULL OR MR.MaxWeight >= @ArticleWeight)
852 AND ( MR.MaxHeight IS NULL OR MR.MaxHeight >= @MinDimension)
853 AND ( MR.MaxWidth IS NULL OR MR.MaxWidth >= @MiddleDimension)
854 AND ( MR.MaxLength IS NULL OR MR.MaxLength >= @MaxDimension)
855 AND ( MR.Max3DSum IS NULL OR MR.Max3DSum >= @SumDimensions)
856 AND ( MR.MaxPrice IS NULL OR MR.MaxPrice >= @Price)
857 AND ( MR.isPrepay IS NULL OR MR.isPrepay = @isPrepay)
858 AND ( MR.isLegal IS NULL OR MR.isLegal = @isLegal)
859
860
861
862DELETE FROM #Sorter_GetChute_SortingFlow_Reduce
863WHERE V_ManualReducts IS NOT NULL
864 AND NOT EXISTS (
865 SELECT
866 1
867 FROM
868 #Sorter_GetChute_ManualReduct MR
869 WHERE
870 V_ManualReducts = MR.NodeReduct
871)
872
873END;
874
875GO
876
877EXECUTE dbo.SysComponentRegister
878 @ID = NULL
879, @SysName = N'SorterNM.GetManualReductNode';
880GO
881
882SET ANSI_NULLS ON;
883
884SET QUOTED_IDENTIFIER ON;
885GO
886IF (OBJECT_ID(N'Sorter.GetSupplyByOzonNode', N'P') IS NULL)
887BEGIN
888 EXEC (N'CREATE PROCEDURE Sorter.GetSupplyByOzonNode AS RETURN 0');
889END;
890GO
891EXECUTE dbo.ObjectDescriptionSet
892 @ObjectName = 'Sorter.GetSupplyByOzonNode'
893, @Description = 'Получение вариантов собственной доставки для постинга';
894GO
895
896ALTER PROCEDURE Sorter.GetSupplyByOzonNode
897 @ID dbo.BIDENT
898 , @PolygonID dbo.BIDENT
899 , @DeliveryVariantID dbo.BIDENT
900 , @DeliveryTypeID dbo.BIDENT
901 , @PolygonOwnerID dbo.BIDENT
902 , @IsOurDelivery bit
903 , @DstPlaceID dbo.BIDENT = NULL
904 , @ConditionParamsSupplyByOzon dbo.string OUTPUT
905
906 , @PolygonObjectTypeID dbo.BIDENT OUTPUT
907
908-- procedure result - INSERT INTO #Sorter_GetChute_SupplyByOzon
909
910AS
911BEGIN
912 SET NOCOUNT ON;
913
914IF OBJECT_ID(N'tempdb..#Sorter_GetChute_SupplyByOzon_TempNodes', N'U') IS NOT NULL
915BEGIN
916 DROP TABLE #Sorter_GetChute_SupplyByOzon_TempNodes;
917END;
918
919CREATE TABLE #Sorter_GetChute_SupplyByOzon_TempNodes
920(
921 ID dbo.BIDENT NOT NULL
922 ,
923 NodeSC dbo.string
924 ,
925 RelatePriority dbo.string
926 ,
927 Priority int
928);
929
930
931 SELECT @ConditionParamsSupplyByOzon = NULL;
932
933-- слой
934SELECT top(1)
935 @PolygonObjectTypeID = O.ObjectTypeID
936FROM
937 dbo.Object O WITH (NOLOCK)
938WHERE
939 O.ID = @PolygonID;
940
941INSERT INTO #Sorter_GetChute_SupplyByOzon_TempNodes
942 ( ID, NodeSC, RelatePriority, Priority )
943SELECT
944 SO.ID
945 , SO.NodeSC
946 , SO.RelatePriority
947 , SO.Priority
948FROM
949 Sorter.SupplyByOzon SO
950WHERE
951 ( SO.PolygonID IS NULL
952 OR SO.PolygonID = @PolygonObjectTypeID)
953 AND
954 ( SO.PolygonOwnerID IS NULL
955 OR SO.PolygonOwnerID = @PolygonOwnerID)
956 AND
957 ( SO.DeliveryTypeID IS NULL
958 OR SO.DeliveryTypeID = @DeliveryTypeID)
959 AND
960 ( SO.IsOurDelivery IS NULL
961 OR SO.IsOurDelivery = @IsOurDelivery)
962 AND
963 ( SO.DeliveryVariantID IS NULL
964 OR SO.DeliveryVariantID = @DeliveryVariantID)
965 AND
966 ( SO.DstPlaceID IS NULL
967 OR SO.DstPlaceID = @DstPlaceID)
968
969INSERT INTO #Sorter_GetChute_SupplyByOzon
970 ( ID, NodeSC )
971SELECT
972 TN.ID
973 , TN.NodeSC
974FROM
975 #Sorter_GetChute_SupplyByOzon_TempNodes TN
976WHERE
977 TN.RelatePriority IS NULL
978
979INSERT INTO #Sorter_GetChute_SupplyByOzon
980 ( ID, NodeSC )
981SELECT
982 RN.ID
983 , RN.NodeSC
984FROM (
985SELECT
986 row_number() over (partition BY RelatePriority ORDER BY Priority ) as num
987 , TN.NodeSC
988 , TN.ID
989 FROM
990 #Sorter_GetChute_SupplyByOzon_TempNodes TN
991 WHERE
992 TN.RelatePriority IS NOT NULL
993 ) RN
994WHERE num = 1
995
996DELETE FROM #Sorter_GetChute_SortingFlow_Reduce
997WHERE II_SupplyByOzon IS NOT NULL
998AND NOT EXISTS (
999 SELECT
1000 1
1001 FROM
1002 #Sorter_GetChute_SupplyByOzon SO
1003 WHERE
1004 II_SupplyByOzon = SO.NodeSC
1005)
1006
1007 SET @ConditionParamsSupplyByOzon =
1008 '; PolygonID = ' + ISNULL(CONVERT(varchar(255), @PolygonID), '')
1009 + '; PolygonOwnerID = ' + ISNULL(CONVERT(varchar(255), @PolygonOwnerID), '')
1010 + '; DeliveryTypeID = ' + ISNULL(CONVERT(varchar(255), @DeliveryTypeID), '')
1011 + '; IsOurDelivery = ' + ISNULL(CONVERT(varchar(255), @IsOurDelivery), '')
1012 + '; PolygonObjectTypeID = ' + ISNULL(CONVERT(varchar(255), @PolygonObjectTypeID), '')
1013 ;
1014END;
1015
1016GO
1017
1018EXECUTE dbo.SysComponentRegister
1019 @ID = NULL
1020, @SysName = N'Sorter.GetSupplyByOzonNode';
1021GO
1022
1023SET ANSI_NULLS ON;
1024
1025SET QUOTED_IDENTIFIER ON;
1026GO
1027IF (OBJECT_ID(N'Sorter.GetSupplyBy3PLNode', N'P') IS NULL)
1028BEGIN
1029 EXEC (N'CREATE PROCEDURE Sorter.GetSupplyBy3PLNode AS RETURN 0');
1030END;
1031GO
1032EXECUTE dbo.ObjectDescriptionSet
1033 @ObjectName = 'Sorter.GetSupplyBy3PLNode'
1034, @Description = 'Получение вариантов доставки 3PL для постинга';
1035GO
1036
1037ALTER PROCEDURE Sorter.GetSupplyBy3PLNode
1038 @ID dbo.BIDENT
1039 , @CurDateTime datetime
1040 , @Polygon3PLID dbo.BIDENT
1041 , @DeliveryTypeID dbo.BIDENT
1042 , @DeliveryVariantID dbo.BIDENT
1043 , @IsOurDelivery bit
1044 , @DstPlaceID dbo.BIDENT = NULL
1045 , @ConditionParamsSupplyBy3PL dbo.string OUTPUT
1046
1047 , @PolygonOwnerID dbo.BIDENT OUTPUT
1048 , @PolygonObjectTypeID dbo.BIDENT OUTPUT
1049
1050-- procedure result - INSERT INTO #Sorter_GetChute_SupplyBy3PL
1051
1052AS
1053BEGIN
1054 SET NOCOUNT ON;
1055
1056-- DECLARE
1057-- @PolygonOwnerID dbo.BIDENT
1058-- , @PolygonObjectTypeID dbo.BIDENT
1059-- ;
1060
1061 -- Инициализация
1062 SELECT @ConditionParamsSupplyBy3PL = NULL;
1063
1064-- владелец полигона и слой
1065 SELECT top(1)
1066 @PolygonOwnerID = O.OwnerObjectID
1067 , @PolygonObjectTypeID = O.ObjectTypeID
1068 FROM
1069 dbo.Object O WITH (NOLOCK)
1070 WHERE
1071 O.ID = @Polygon3PLID;
1072
1073IF OBJECT_ID(N'tempdb..#Sorter_GetChute_SupplyBy3PL_TempNodes', N'U') IS NOT NULL
1074BEGIN
1075 DROP TABLE #Sorter_GetChute_SupplyBy3PL_TempNodes;
1076END;
1077
1078CREATE TABLE #Sorter_GetChute_SupplyBy3PL_TempNodes
1079(
1080 ID dbo.BIDENT NOT NULL
1081 ,
1082 Node3PL dbo.string
1083 ,
1084 RelatePriority dbo.string
1085 ,
1086 Priority int
1087);
1088
1089-- настройки 3PL
1090IF OBJECT_ID(N'tempdb..#Sorter_GetChute_QtyPostings3PL', N'U') IS NOT NULL
1091BEGIN
1092 DROP TABLE #Sorter_GetChute_QtyPostings3PL;
1093END;
1094
1095CREATE TABLE #Sorter_GetChute_QtyPostings3PL
1096(
1097 ID dbo.BIDENT NOT NULL
1098 , Node3PL dbo.string NULL
1099 , Count int NULL
1100 , MinQTY int NULL
1101 , Rate int NULL
1102 , MaxQTY int NULL
1103 , Priority int NULL
1104 , RelatePriority dbo.string NULL
1105);
1106
1107INSERT INTO #Sorter_GetChute_QtyPostings3PL
1108 (
1109 Node3PL
1110 , Count
1111 , MinQTY
1112 , Rate
1113 , MaxQTY
1114 , Priority
1115 , RelatePriority
1116 , ID
1117 )
1118SELECT
1119 S3PL.Node3PL
1120 , DC.Count
1121 , S3PL.MinQTY
1122 , S3PL.Rate
1123 , S3PL.MaxQTY
1124 , S3PL.Priority
1125 , S3PL.RelatePriority
1126 , S3PL.ID
1127FROM
1128 Sorter.SupplyBy3PL S3PL
1129 left join ( -- оптимизировать с помощью #Sorter_GetChute_SortingFlow_Reduce
1130 select
1131 D3PL.Name3PL
1132 , SUM(D3PL.Count) as Count
1133 from
1134 Sorter.Divert3pl D3PL
1135 where
1136 EXISTS (
1137 select
1138 1
1139 from
1140 Sorter.Divert3pl DPL
1141 inner join Sorter.SortingTree ST on ST.II_SupplyBy3PL = DPL.Name3pl
1142 inner join Sorter.DeliveryTime DT on ST.III_DeliveryTime = DT.NodeTime
1143 where
1144 D3PL.id = DPL.id
1145 AND DPL.DateDivert = CAST(DATEADD(day, DT.SortDayOffset , @CurDateTime) AS DATE)
1146 AND DPL.SortStart = DT.CutInTime
1147 AND DPL.SortEnd = DT.CutOffTime
1148 )
1149 GROUP BY
1150 D3PL.Name3PL
1151 ) DC ON DC.Name3PL = S3PL.Node3PL
1152WHERE
1153 ( S3PL.PolygonID IS NULL
1154 OR S3PL.PolygonID = @PolygonObjectTypeID)
1155 AND
1156 ( S3PL.PolygonOwnerID IS NULL
1157 OR S3PL.PolygonOwnerID = @PolygonOwnerID)
1158 AND
1159 ( S3PL.DeliveryTypeID IS NULL
1160 OR S3PL.DeliveryTypeID = @DeliveryTypeID)
1161 AND
1162 ( S3PL.IsOurDelivery IS NULL
1163 OR S3PL.IsOurDelivery = @IsOurDelivery)
1164 AND
1165 ( S3PL.DeliveryVariantID IS NULL
1166 OR S3PL.DeliveryVariantID = @DeliveryVariantID)
1167 AND
1168 ( S3PL.DstPlaceID IS NULL
1169 OR S3PL.DstPlaceID = @DstPlaceID)
1170
1171
1172INSERT INTO #Sorter_GetChute_SupplyBy3PL_TempNodes
1173 ( ID, Node3PL, Priority, RelatePriority )
1174SELECT
1175 TS.ID, TS.Node3PL, TS.Priority, TS.RelatePriority
1176FROM
1177 #Sorter_GetChute_QtyPostings3PL TS
1178WHERE
1179 TS.MinQTY <> 0 AND TS.MaxQTY <> 0 AND
1180 (TS.Count IS NULL OR TS.Count < TS.MaxQTY)
1181 AND (
1182 (TS.Count IS NULL OR TS.Count = 0 OR TS.Count < TS.MinQTY)
1183 OR (TS.Count >= TS.MinQTY AND TS.Count < TS.MaxQTY AND ((TS.Count - TS.MinQTY) % TS.Rate) > 0)
1184 OR (TS.Count >= TS.MinQTY AND TS.Count < TS.MaxQTY AND ((TS.Count - TS.MinQTY) % TS.Rate) = 0
1185 AND NOT EXISTS ( SELECT
1186 1
1187 FROM
1188 #Sorter_GetChute_QtyPostings3PL TS
1189 WHERE
1190 TS.Count IS NULL OR TS.Count = 0 OR TS.Count < TS.MinQTY OR
1191 (TS.Count >= TS.MinQTY AND TS.Count < TS.MaxQTY AND ((TS.Count - TS.MinQTY) % TS.Rate) > 0)
1192 )
1193 )
1194 )
1195
1196INSERT INTO #Sorter_GetChute_SupplyBy3PL
1197 ( ID, Node3PL )
1198SELECT
1199 TN.ID
1200 , TN.Node3PL
1201FROM
1202 #Sorter_GetChute_SupplyBy3PL_TempNodes TN
1203WHERE
1204 TN.RelatePriority IS NULL
1205
1206INSERT INTO #Sorter_GetChute_SupplyBy3PL
1207 ( ID, Node3PL )
1208SELECT
1209 RN.ID
1210 , RN.Node3PL
1211FROM (
1212SELECT
1213 row_number() over (partition BY RelatePriority ORDER BY Priority ) as num
1214 , TN.Node3PL
1215 , TN.ID
1216 FROM
1217 #Sorter_GetChute_SupplyBy3PL_TempNodes TN
1218 WHERE
1219 TN.RelatePriority IS NOT NULL
1220 ) RN
1221WHERE num = 1
1222
1223
1224DELETE FROM #Sorter_GetChute_SortingFlow_Reduce
1225WHERE II_SupplyBy3PL IS NOT NULL
1226 AND NOT EXISTS (
1227 SELECT
1228 1
1229 FROM
1230 #Sorter_GetChute_SupplyBy3PL S3PL
1231 WHERE
1232 II_SupplyBy3PL = S3PL.Node3PL
1233)
1234
1235 SET @ConditionParamsSupplyBy3PL = ''
1236 + '; Polygon3PLOwnerID = ' + ISNULL(CONVERT(varchar(255), @PolygonOwnerID), '')
1237 + '; PolygonObjectTypeID = ' + ISNULL(CONVERT(varchar(255), @PolygonObjectTypeID), '')
1238;
1239END;
1240
1241GO
1242
1243EXECUTE dbo.SysComponentRegister
1244 @ID = NULL
1245, @SysName = N'Sorter.GetSupplyBy3PLNode';
1246GO
1247
1248SET ANSI_NULLS ON;
1249
1250SET QUOTED_IDENTIFIER ON;
1251GO
1252IF (OBJECT_ID(N'Sorter.GetSortingLogFull', N'P') IS NULL)
1253BEGIN
1254 EXEC (N'CREATE PROCEDURE Sorter.GetSortingLogFull AS RETURN 0');
1255END;
1256GO
1257EXECUTE dbo.ObjectDescriptionSet
1258 @ObjectName = 'Sorter.GetSortingLogFull'
1259, @Description = 'Получение лога сортировки';
1260GO
1261
1262ALTER PROCEDURE Sorter.GetSortingLogFull
1263 @SorterID int = 4
1264 , @ArticleID dbo.BIDENT = NULL
1265 , @DateFrom datetime
1266 , @DateTo datetime
1267 , @Success bit = NULL -- = 0 неотсортированые, = 1 - отсортирование, NULL - все подряд
1268
1269AS
1270BEGIN
1271 SET NOCOUNT ON;
1272-- table.column --'russian name'
1273 select
1274 SL.ID --'№ строки'
1275 , O.Name as ArticleName --'№ отправления'
1276 , SL.ArticleID --'ID отправления'
1277 , SL.CurrentDate --'Печать этикетки'
1278 , StickerCamomile.Code as PostingSortID --'Ромашка'
1279 , SortingRack.Code as SortingRack --'Шкаф сортировки'
1280
1281 , PolygonObjectType.Name as PolygonObjectTypeName --'2. Полигон (Ozon)'
1282 , PolygonOwner.Name as PolygonOwnerName --'2. Владелец (Ozon)'
1283 , Polygon3plObjectType.Name as Polygon3plObjectTypeName --'2. Полигон (Агентский)'
1284 , Polygon3plOwner.Name as Polygon3plOwnerName --'2. Владелец (Агентский)'
1285 , DeliveryType.Name as DeliveryTypeName --'2. Доставка (тип)'
1286 , SL.DeliveryVariantID --'2. DeliveryVariantID'
1287
1288 , SL.DstPlaceID --'2. DstPlaceID'
1289 , SL.IsOurDelivery --'2. Наша доставка'
1290
1291 , cast(SL.TimeSlotDateTimeFrom as date) as TimeSlotDate--'3. Дата таймслота'
1292 , CONVERT(varchar(5), cast(SL.TimeSlotDateTimeFrom as time(0))) + ' - ' + CONVERT(varchar(5), cast(SL.TimeSlotDateTimeTo as time(0))) as TimeSlotTimeInterval --'3. Интервал таймслота'
1293 , SL.TimeSlotTemplateID --'3. TimeSlotTemplateID'
1294 , SL.DVD --'4. День в День'
1295 , CONVERT(varchar, SL.MinDimension) + 'x' + CONVERT(varchar, SL.MiddleDimension) + 'x' + CONVERT(varchar, SL.MaxDimension) as Dimensions--'4. Габариты (мм)'
1296 , SL.SumDimensions --'4. ∑ сторон (мм)'
1297 , SL.ArticleWeight --'4. Вес (гр)'
1298 , SL.CntArticleInPosting --'4. Кол-во экземпляров'
1299 , SL.isPrepay --'4. 100% предоплата'
1300 , SL.isLegal --'4. Юр.лицо'
1301 , SL.Price --'4. Стоимость (руб.)'
1302
1303 , CAST(CASE AttributeFresh.AttributeFresh WHEN 1 THEN 1 ELSE 0 END AS BIT) as AttributeFresh --'5. Fresh'
1304 , CAST(CASE AttributeConsolidationFresh.AttributeConsolidationFresh WHEN 1 THEN 1 ELSE 0 END AS BIT) as AttributeConsolidationFresh --'5. К-я Fresh'
1305 , CAST(CASE AttributeisOzonCard.AttributeisOzonCard WHEN 1 THEN 1 ELSE 0 END AS BIT) as AttributeisOzonCard --'5. Ozon.Card'
1306 , CAST(CASE AttributePremium.AttributePremium WHEN 1 THEN 1 ELSE 0 END AS BIT) as AttributePremium --'5. Premium'
1307 , CAST(CASE AttributePharmacy.AttributePharmacy WHEN 1 THEN 1 ELSE 0 END AS BIT) as AttributePharmacy --'5. Аптека'
1308 , CAST(CASE AttributeIgnore3pl.AttributeIgnore3pl WHEN 1 THEN 1 ELSE 0 END AS BIT) as AttributeIgnore3pl --'5. Вместе с возвратом'
1309 , CAST(CASE AttributeDeliveryToDoor.AttributeDeliveryToDoor WHEN 1 THEN 1 ELSE 0 END AS BIT) as AttributeDeliveryToDoor --'5. Доставка до двери'
1310 , CAST(CASE AttributeBulky.AttributeBulky WHEN 1 THEN 1 ELSE 0 END AS BIT) as AttributeBulky --'5. КГТ'
1311 , CAST(CASE AttributeOneBoxOrder.AttributeOneBoxOrder WHEN 1 THEN 1 ELSE 0 END AS BIT) as AttributeOneBoxOrder --'5. Однокоробочный'
1312 , CAST(CASE AttributeJewelry.AttributeJewelry WHEN 1 THEN 1 ELSE 0 END AS BIT) as AttributeJewelry --'5. Ювелир'
1313
1314 from
1315 Sorter.SortingLog SL WITH (NOLOCK)
1316 inner join dbo.Object O WITH (NOLOCK) on O.ID = SL.ArticleID
1317 OUTER APPLY -- Код ручной подсортировки (на этикетке постинга) + Ромашка
1318 ( SELECT TOP 1
1319 DL.Code
1320 , O.Name
1321 FROM dbo.ObjectDirectory OD
1322 inner join dbo.Object O on O.ID = OD.DirectoryID
1323 inner join dbo.ObjectType OT on OT.id = O.ObjectTypeID and OT.SysName= 'DirectoryCamomile'
1324 inner join dbo.DirectoryLst DL on DL.ID = O.ID
1325 where
1326 OD.ObjectID = SL.DeliveryVariantID) StickerCamomile
1327 OUTER APPLY -- Шкаф сортировки
1328 ( SELECT TOP 1
1329 O.Name
1330 , D.Code
1331 FROM dbo.ObjectDirectory OD
1332 inner join dbo.Directory D on D.ID = OD.DirectoryID
1333 inner join dbo.Object O on O.ID = OD.DirectoryID
1334 inner join dbo.ObjectType OT on OT.id = O.ObjectTypeID and OT.SysName= 'DirectorySortingRack'
1335 where
1336 OD.ObjectID = SL.DeliveryVariantID) SortingRack
1337
1338 OUTER APPLY
1339 (SELECT top (1)
1340 OO.Name
1341 FROM
1342 dbo.Object O
1343 inner join dbo.ObjectType OT on O.ObjectTypeID = OT.ID
1344 inner join dbo.Object OO on OO.ID = O.OwnerObjectID
1345 WHERE
1346 OT.ParentObjectTypeID = 15171683428000
1347 and OO.ID = SL.PolygonOwnerID ) PolygonOwner
1348 OUTER APPLY
1349 (SELECT top (1)
1350 OO.Name
1351 FROM
1352 dbo.Object O
1353 inner join dbo.ObjectType OT on O.ObjectTypeID = OT.ID
1354 inner join dbo.Object OO on OO.ID = O.OwnerObjectID
1355 WHERE
1356 OT.ParentObjectTypeID = 15171683428000
1357 and OO.ID = SL.Polygon3plOwnerID ) Polygon3plOwner
1358 OUTER APPLY
1359 (SELECT top(1)
1360 OT.Name
1361 FROM
1362 dbo.ObjectType OT
1363 WHERE
1364 OT.ParentObjectTypeID = 104672000
1365 and OT.ID = SL.DeliveryTypeID) DeliveryType
1366 OUTER APPLY
1367 (SELECT top(1)
1368 O.Name
1369 FROM
1370 dbo.Object O
1371 WHERE
1372 O.StateID = 2828190836000
1373 and O.ID = SL.DeliveryVariantID) DeliveryVariant
1374
1375 OUTER APPLY
1376 (SELECT TOP(1)
1377 OT.Name
1378 FROM
1379 dbo.ObjectType OT
1380 WHERE
1381 OT.ParentObjectTypeID = 15171683428000
1382 and OT.ID = SL.PolygonObjectTypeID) PolygonObjectType
1383 OUTER APPLY
1384 (SELECT TOP(1)
1385 OT.Name
1386 FROM
1387 dbo.ObjectType OT
1388 WHERE
1389 OT.ParentObjectTypeID = 15171683428000
1390 and OT.ID = SL.Polygon3plObjectTypeID) Polygon3plObjectType
1391
1392 OUTER APPLY
1393 (SELECT top(1)
1394 1 as AttributePremium
1395 FROM
1396 dbo.ArticleAttribute AA
1397 WHERE
1398 AA.ArticleID = SL.ArticleID
1399 AND AA.AttributeID = 15456334457000 ) AttributePremium
1400 OUTER APPLY
1401 (SELECT top(1)
1402 1 as AttributePharmacy
1403 FROM
1404 dbo.ArticleAttribute AA
1405 WHERE
1406 AA.ArticleID = SL.ArticleID
1407 AND AA.AttributeID = 15149349735000 ) AttributePharmacy
1408 OUTER APPLY
1409 (SELECT top(1)
1410 1 as AttributeJewelry
1411 FROM
1412 dbo.ArticleAttribute AA
1413 WHERE
1414 AA.ArticleID = SL.ArticleID
1415 AND AA.AttributeID = 15149349738000 ) AttributeJewelry
1416 OUTER APPLY
1417 (SELECT top(1)
1418 1 as AttributeFresh
1419 FROM
1420 dbo.ArticleAttribute AA
1421 WHERE
1422 AA.ArticleID = SL.ArticleID
1423 AND AA.AttributeID = 15149349739000 ) AttributeFresh
1424 OUTER APPLY
1425 (SELECT top(1)
1426 1 as AttributeisOzonCard
1427 FROM
1428 dbo.ArticleAttribute AA
1429 WHERE
1430 AA.ArticleID = SL.ArticleID
1431 AND AA.AttributeID = 15427126326000 ) AttributeisOzonCard
1432 OUTER APPLY
1433 (SELECT top(1)
1434 1 as AttributeConsolidationFresh
1435 FROM
1436 dbo.ArticleAttribute AA
1437 WHERE
1438 AA.ArticleID = SL.ArticleID
1439 AND AA.AttributeID = 15587973565000 ) AttributeConsolidationFresh
1440 OUTER APPLY
1441 (SELECT top(1)
1442 1 as AttributeOneBoxOrder
1443 FROM
1444 dbo.ArticleAttribute AA
1445 WHERE
1446 AA.ArticleID = SL.ArticleID
1447 AND AA.AttributeID = 15740897123000 ) AttributeOneBoxOrder
1448 OUTER APPLY
1449 (SELECT top(1)
1450 1 as AttributeIgnore3pl
1451 FROM
1452 dbo.ArticleAttribute AA
1453 WHERE
1454 AA.ArticleID = SL.ArticleID
1455 AND AA.AttributeID = 15759461237000 ) AttributeIgnore3pl
1456 OUTER APPLY
1457 (SELECT top(1)
1458 1 as AttributeDeliveryToDoor
1459 FROM
1460 dbo.ArticleAttribute AA
1461 WHERE
1462 AA.ArticleID = SL.ArticleID
1463 AND AA.AttributeID = 15841712498000 ) AttributeDeliveryToDoor
1464 OUTER APPLY
1465 (SELECT top(1)
1466 1 as AttributeBulky
1467 FROM
1468 dbo.ArticleAttribute AA
1469 WHERE
1470 AA.ArticleID = SL.ArticleID
1471 AND AA.AttributeID = 15149349736000 ) AttributeBulky
1472
1473 where
1474 SL.CurrentDate between @DateFrom and @DateTo
1475 and
1476 SL.SorterID = @SorterID
1477 and
1478 (@ArticleID is NULL OR SL.ArticleID = @ArticleID)
1479 and
1480 SL.CurrentDate in (
1481 select
1482 max(SLS.CurrentDate)
1483 from
1484 Sorter.SortingLog SLS
1485 where
1486 SLS.SorterID = @SorterID
1487 and
1488 SLS.CurrentDate between @DateFrom and @DateTo
1489 and
1490 SLS.ArticleID = SL.ArticleID
1491 )
1492 AND (
1493 (@Success is NULL)
1494 OR
1495 (@Success = 1 AND SL.DEST1 IS NOT NULL)
1496 OR
1497 (@Success = 0 AND SL.DEST1 IS NULL)
1498 )
1499 ORDER BY
1500 SL.CurrentDate
1501END
1502
1503GO
1504
1505EXECUTE dbo.SysComponentRegister
1506 @ID = NULL
1507, @SysName = N'Sorter.GetSortingLogFull';
1508GO
1509
1510SET ANSI_NULLS ON;
1511
1512SET QUOTED_IDENTIFIER ON;
1513GO
1514IF (OBJECT_ID(N'Sorter.GetSortingLog', N'P') IS NULL)
1515BEGIN
1516 EXEC (N'CREATE PROCEDURE Sorter.GetSortingLog AS RETURN 0');
1517END;
1518GO
1519EXECUTE dbo.ObjectDescriptionSet
1520 @ObjectName = 'Sorter.GetSortingLog'
1521, @Description = 'Получение лога сортировки';
1522GO
1523
1524ALTER PROCEDURE Sorter.GetSortingLog
1525 @SorterID int = 4
1526 , @ArticleID dbo.BIDENT = NULL
1527 , @DateFrom datetime
1528 , @DateTo datetime
1529 , @Success bit = NULL -- = 0 неотсортированые, = 1 - отсортирование, NULL - все подряд
1530
1531AS
1532BEGIN
1533 SET NOCOUNT ON;
1534
1535
1536 select
1537 SL.ID
1538 , SL.ArticleID
1539 , SL.CurrentDate
1540 , SL.DEST1
1541 , SL.DEST2
1542 , SL.SortingDate
1543 , SL.Weight
1544 , SL.Volume
1545 , SL.Width
1546 , SL.Height
1547 , SL.Length
1548 , SL.DeliveryTypeID
1549 , SL.IsOurDelivery
1550 , SL.DeliveryVariantID
1551 , SL.TimeSlotTemplateID
1552 , SL.TimeSlotDateTimeFrom
1553 , SL.TimeSlotDateTimeTo
1554 , SL.SortingCenterID
1555 , SL.DstPlaceID
1556 , SL.PolygonID
1557 , SL.Polygon3PLID
1558 , SL.PolygonOwnerID
1559 , SL.PolygonObjectTypeID
1560 , SL.Polygon3plOwnerID
1561 , SL.Polygon3plObjectTypeID
1562 , SL.DVD
1563 , SL.TimeSlotTimeMiddle
1564 , SL.CntArticleInPosting
1565 , SL.ArticleWeight
1566 , SL.MinDimension
1567 , SL.MiddleDimension
1568 , SL.MaxDimension
1569 , SL.SumDimensions
1570 , SL.Price
1571 , SL.ArticlePay
1572 , SL.isPrepay
1573 , SL.isLegal
1574 , SL.II_SupplyByOzon
1575 , SL.II_SupplyBy3PL
1576 , SL.III_DeliveryTime
1577 , SL.IV_PostingAttribute
1578 , SL.V_ManualReducts
1579 , SL.SortingFlowReduce
1580 , SL.SortingTreeID
1581 , StickerCamomile.Code as PostingSortID
1582 , StickerCamomile.Name as Camomile --'Ромашка'
1583 , SortingRack.Name as SortingRack --'Шкаф сортировки'
1584 from
1585 Sorter.SortingLog SL
1586 OUTER APPLY -- Код ручной подсортировки (на этикетке постинга) + Ромашка
1587 ( SELECT TOP 1
1588 DL.Code
1589 , O.Name
1590 FROM dbo.ObjectDirectory OD
1591 inner join dbo.Object O on O.ID = OD.DirectoryID
1592 inner join dbo.ObjectType OT on OT.id = O.ObjectTypeID and OT.SysName= 'DirectoryCamomile'
1593 inner join dbo.DirectoryLst DL on DL.ID = O.ID
1594 where
1595 OD.ObjectID = SL.DeliveryVariantID) StickerCamomile
1596 OUTER APPLY -- Шкаф сортировки
1597 ( SELECT TOP 1
1598 O.Name
1599 FROM dbo.ObjectDirectory OD
1600 inner join dbo.Object O on O.ID = OD.DirectoryID
1601 inner join dbo.ObjectType OT on OT.id = O.ObjectTypeID and OT.SysName= 'DirectorySortingRack'
1602 where
1603 OD.ObjectID = SL.DeliveryVariantID) SortingRack
1604 where
1605 SL.CurrentDate between @DateFrom and @DateTo
1606 and
1607 SL.SorterID = @SorterID
1608 and
1609 (@ArticleID is NULL OR SL.ArticleID = @ArticleID)
1610 and
1611 SL.CurrentDate in (
1612 select
1613 max(SLS.CurrentDate)
1614 from
1615 Sorter.SortingLog SLS
1616 where
1617 SLS.SorterID = @SorterID
1618 and
1619 SLS.CurrentDate between @DateFrom and @DateTo
1620 and
1621 SLS.ArticleID = SL.ArticleID
1622 )
1623 AND (
1624 (@Success is NULL)
1625 or
1626 (@Success = 1 AND SL.DEST1 IS NOT NULL)
1627 or
1628 (@Success = 0 AND SL.DEST1 IS NULL)
1629 )
1630
1631
1632END
1633
1634GO
1635
1636EXECUTE dbo.SysComponentRegister
1637 @ID = NULL
1638, @SysName = N'Sorter.GetSortingLog';
1639GO
1640
1641SET ANSI_NULLS ON;
1642
1643SET QUOTED_IDENTIFIER ON;
1644GO
1645IF (OBJECT_ID(N'Sorter.GetPostingType', N'P') IS NULL)
1646BEGIN
1647 EXEC (N'CREATE PROCEDURE Sorter.GetPostingType AS RETURN 0');
1648END;
1649GO
1650EXECUTE dbo.ObjectDescriptionSet
1651 @ObjectName = 'Sorter.GetPostingType'
1652, @Description = 'Получение типа постинга на основе товарных ограничений из таблицы Sorter.PostingLimits';
1653GO
1654
1655ALTER PROCEDURE Sorter.GetPostingType
1656 @ID dbo.BIDENT
1657, @Weight dbo.BIDENT = 0
1658, @Length dbo.BIDENT = 0
1659, @Width dbo.BIDENT = 0
1660, @Height dbo.BIDENT = 0
1661, @ISFind int OUTPUT
1662, @PostingType dbo.string OUTPUT
1663, @ConditionParams dbo.string OUTPUT
1664
1665AS
1666BEGIN
1667 SET NOCOUNT ON;
1668
1669DECLARE
1670 @CntArticleInPosting int = 0
1671 , @MinDimension BIGINT = 0
1672 , @MiddleDimension BIGINT = 0
1673 , @MaxDimension BIGINT = 0
1674 , @SumDimensions BIGINT = 0
1675 , @Price money = 0
1676 , @ArticlePay money = 0
1677 , @Prepay int = NULL
1678 , @Legal bit = 0
1679 , @AttributeType dbo.string
1680 , @PostingLimitsID dbo.BIDENT
1681 , @ObjectAttributeMoneyPrice_TypeID dbo.BIDENT = 2939074000 --ObjectAttributeMoneyPrice
1682 , @ObjectAttributeMoneyClientPrice_TypeID dbo.BIDENT = 2939068000 --ExtValueClientOrderID
1683 ;
1684
1685 -- Инициализвция
1686 SELECT
1687 @PostingType = NULL
1688 , @ConditionParams = NULL
1689 , @ISFind = NULL;
1690
1691 --
1692 select
1693 @CntArticleInPosting = count(distinct CA.ArticleID)
1694 from
1695 dbo.ContainerArticle CA
1696 where
1697 CA.ContainerID = @ID
1698 AND CA.MomentOut IS NULL;
1699
1700---
1701SELECT
1702 @MinDimension = [1]
1703, @MiddleDimension = [2]
1704, @MaxDimension = [3]
1705FROM ( SELECT
1706 ROW_NUMBER() OVER (ORDER BY
1707 Src.Parameter ASC) AS ID
1708 , Src.Parameter
1709 FROM ( SELECT
1710 @Length AS Parameter
1711 UNION ALL
1712 SELECT
1713 @Width
1714 UNION ALL
1715 SELECT
1716 @Height) Src ) x
1717PIVOT ( MAX(Parameter)
1718 FOR ID IN ([1], [2], [3])) PivotTable;
1719
1720
1721 set @SumDimensions = @Length + @Width + @Height;
1722
1723 -- Legal
1724 SELECT
1725 @Legal = dbo.ObjectIs(AP.PersonHumanID, 'PersonLegal')
1726 FROM
1727 dbo.ArticlePosting AP WITH (NOLOCK)
1728 WHERE
1729 (AP.ID = @ID)
1730 OPTION (KEEP PLAN, FAST 1);
1731
1732
1733 -- Price
1734 BEGIN
1735 SELECT
1736 @Price = OA.[Value]
1737 FROM
1738 dbo.ObjectAttribute OA WITH (NOLOCK, FORCESEEK, INDEX = IX_ObjectAttribute_OwnerObjectID_ObjectTypeID)
1739 WHERE
1740 (OA.OwnerObjectID = @ID)
1741 AND (OA.ObjectTypeID = @ObjectAttributeMoneyPrice_TypeID)
1742 OPTION (KEEP PLAN, FAST 1);
1743
1744 SET @Price = ISNULL(@Price, 0);
1745 END;
1746
1747
1748
1749 -- Prepay
1750SELECT
1751 @ArticlePay = OA.[Value]
1752FROM
1753 dbo.ObjectAttribute OA WITH (NOLOCK, FORCESEEK, INDEX = IX_ObjectAttribute_OwnerObjectID_ObjectTypeID)
1754WHERE
1755 (OA.OwnerObjectID = @ID)
1756 AND (OA.ObjectTypeID = @ObjectAttributeMoneyClientPrice_TypeID)
1757OPTION (KEEP PLAN, FAST 1);
1758
1759-- если с клиента ноль -> полная предоплата
1760IF (@ArticlePay = 0)
1761BEGIN
1762 SET @Prepay = 1
1763END
1764
1765-- get posting type from attributes
1766
1767IF OBJECT_ID(N'tempdb..#Sorter_GetChute_PostingAttribute_TempPostingType', N'U') IS NOT NULL
1768BEGIN
1769 DROP TABLE #Sorter_GetChute_PostingAttribute_TempPostingType;
1770END;
1771
1772CREATE TABLE #Sorter_GetChute_PostingAttribute_TempPostingType
1773(
1774 Type dbo.string NULL
1775);
1776
1777 IF NOT EXISTS (
1778 select 1
1779 from dbo.ArticleAttribute AA
1780 where
1781 AA.ArticleID = @ID
1782 and
1783 AA.AttributeID in (
1784 15427126326000,
1785 15149349735000,
1786 15149349738000,
1787 15149349739000,
1788 15587973565000
1789 )
1790 )
1791 BEGIN
1792 INSERT INTO #Sorter_GetChute_PostingAttribute_TempPostingType
1793 values
1794 ('Attr_Maksi')
1795 END
1796
1797 IF NOT EXISTS (
1798 select 1
1799 from dbo.ArticleAttribute AA
1800 where
1801 AA.ArticleID = @ID
1802 and
1803 AA.AttributeID in (
1804 15427126326000,
1805 15149349735000,
1806 15149349739000,
1807 15587973565000
1808 )
1809 )
1810 BEGIN
1811 INSERT INTO #Sorter_GetChute_PostingAttribute_TempPostingType
1812 values
1813 ('MAU_Attr')
1814 END
1815
1816 IF NOT EXISTS (
1817 select 1
1818 from dbo.ArticleAttribute AA
1819 where
1820 AA.ArticleID =@ID
1821 )
1822 BEGIN
1823 INSERT INTO #Sorter_GetChute_PostingAttribute_TempPostingType
1824 values ('Attr_Maksi'),
1825 ('MAU_Attr')
1826 END
1827
1828SELECT TOP (1)
1829 @PostingLimitsID = PL.ID
1830 , @PostingType = PL.PostingType
1831 , @AttributeType = PL.AttributeType
1832FROM
1833 Sorter.PostingLimits PL
1834WHERE
1835 ( PL.MaxCntArticleInPosting IS NULL OR PL.MaxCntArticleInPosting >= @CntArticleInPosting)
1836 AND ( PL.MaxWeight IS NULL OR PL.MaxWeight >= @Weight)
1837 AND ( PL.MaxLength IS NULL OR PL.MaxLength >= @MinDimension)
1838 AND ( PL.MaxWidth IS NULL OR PL.MaxWidth >= @MiddleDimension)
1839 AND ( PL.MaxHeight IS NULL OR PL.MaxHeight >= @MaxDimension)
1840 AND ( PL.MaxDimension IS NULL OR PL.MaxDimension >= @SumDimensions)
1841 AND ( PL.MaxPrice IS NULL OR PL.MaxPrice >= @Price)
1842 AND ( PL.Prepay IS NULL OR PL.Prepay = @Prepay)
1843 AND ( PL.Legal IS NULL OR PL.Legal = @Legal)
1844
1845 AND ( PL.AttributeType IS NULL OR PL.AttributeType in (
1846 select distinct Type from #Sorter_GetChute_PostingAttribute_TempPostingType
1847 ))
1848ORDER BY
1849 PL.Priority;
1850
1851IF (@PostingLimitsID IS NOT NULL)
1852BEGIN
1853 SET @ISFind = 1;
1854END
1855
1856 SET @ConditionParams =
1857 '; @PostingLimitsID = ' + ISNULL(CONVERT(varchar(255), @PostingLimitsID), '')
1858 + '; @PostingType = ' + ISNULL(CONVERT(varchar(255), @PostingType), '')
1859 + '; @CntArticleInPosting = ' + ISNULL(CONVERT(varchar(255), @CntArticleInPosting), '')
1860 + '; @Weight = ' + ISNULL(CONVERT(varchar(255), @Weight), '')
1861 + '; @Length(min) = ' + ISNULL(CONVERT(varchar(255), @MinDimension), '')
1862 + '; @Width(middle) = ' + ISNULL(CONVERT(varchar(255), @MiddleDimension), '')
1863 + '; @Height(max) = ' + ISNULL(CONVERT(varchar(255), @MaxDimension), '')
1864 + '; @SumDimensions = ' + ISNULL(CONVERT(varchar(255), @SumDimensions), '')
1865 + '; @Price = ' + ISNULL(CONVERT(varchar(255), @Price), '')
1866 + '; @ArticlePay = ' + ISNULL(CONVERT(varchar(255), @ArticlePay), '')
1867 + '; @Prepay = ' + ISNULL(CONVERT(varchar(255), @Prepay), '')
1868 + '; @Legal = ' + ISNULL(CONVERT(varchar(255), @Legal), '')
1869 + '; @AttributeType = ' + ISNULL(CONVERT(varchar(255), @AttributeType), '')
1870
1871-- + '; @Jewelry = ' + ISNULL(CONVERT(varchar(255), @Jewelry), '')
1872-- + '; @Pharmacy = ' + ISNULL(CONVERT(varchar(255), @Pharmacy), '')
1873-- + '; @Fresh = ' + ISNULL(CONVERT(varchar(255), @Fresh), '')
1874-- + '; @Premium = ' + ISNULL(CONVERT(varchar(255), @Premium), '')
1875-- + '; @BankCard = ' + ISNULL(CONVERT(varchar(255), @BankCard), '')
1876-- + '; @OCard = ' + ISNULL(CONVERT(varchar(255), @OCard), '')
1877-- + '; @Cash = ' + ISNULL(CONVERT(varchar(255), @Cash), '')
1878-- + '; @InHome = ' + ISNULL(CONVERT(varchar(255), @InHome), '')
1879 ;
1880
1881end;
1882
1883GO
1884
1885EXECUTE dbo.SysComponentRegister
1886 @ID = NULL
1887, @SysName = N'Sorter.GetPostingType';
1888GO
1889
1890SET ANSI_NULLS ON;
1891
1892SET QUOTED_IDENTIFIER ON;
1893GO
1894IF (OBJECT_ID(N'Sorter.GetPostingAttributeNode', N'P') IS NULL)
1895BEGIN
1896 EXEC (N'CREATE PROCEDURE Sorter.GetPostingAttributeNode AS RETURN 0');
1897END;
1898GO
1899EXECUTE dbo.ObjectDescriptionSet
1900 @ObjectName = 'Sorter.GetPostingAttributeNode'
1901, @Description = 'Получение вариантов атрибутов для постинга';
1902GO
1903
1904ALTER PROCEDURE Sorter.GetPostingAttributeNode
1905 @ID dbo.BIDENT
1906 -- , @ConditionParamsPostingAttributes dbo.string OUTPUT
1907
1908-- procedure result - INSERT INTO #Sorter_GetChute_PostingAttribute
1909AS
1910BEGIN
1911 SET NOCOUNT ON;
1912
1913 -- Инициализация
1914 -- SELECT @ConditionParamsPostingAttributes = NULL;
1915
1916-- temp table for node with not available attr but exist in posting
1917IF OBJECT_ID(N'tempdb..#Sorter_GetChute_PostingAttribute_Minus', N'U') IS NOT NULL
1918BEGIN
1919 DROP TABLE #Sorter_GetChute_PostingAttribute_Minus;
1920END;
1921
1922CREATE TABLE #Sorter_GetChute_PostingAttribute_Minus
1923(
1924 NodeAttribute dbo.string NULL
1925);
1926
1927INSERT INTO #Sorter_GetChute_PostingAttribute_Minus
1928 (NodeAttribute)
1929 SELECT
1930 PA.NodeAttribute
1931 FROM
1932 Sorter.PostingAttribute PA
1933 inner join dbo.ArticleAttribute AA on PA.AttributeID = AA.AttributeID and AA.ArticleID = @ID
1934 where
1935 PA.IsAvailable = 0
1936
1937-- temp table for available attributes and nodes
1938IF OBJECT_ID(N'tempdb..#Sorter_GetChute_PostingAttribute_Plus_Attr', N'U') IS NOT NULL
1939BEGIN
1940 DROP TABLE #Sorter_GetChute_PostingAttribute_Plus_Attr;
1941END;
1942
1943CREATE TABLE #Sorter_GetChute_PostingAttribute_Plus_Attr
1944(
1945 NodeAttribute dbo.string
1946 , AttributeID dbo.BIDENT
1947);
1948
1949INSERT INTO #Sorter_GetChute_PostingAttribute_Plus_Attr
1950 (NodeAttribute, AttributeID)
1951SELECT
1952 PA.NodeAttribute, PA.AttributeID
1953FROM
1954 Sorter.PostingAttribute PA
1955where
1956 PA.IsAvailable = 1
1957
1958
1959-- all posible nodes
1960INSERT INTO #Sorter_GetChute_PostingAttribute
1961 ( NodeAttribute )
1962select
1963 PA.NodeAttribute
1964from
1965 Sorter.PostingAttribute PA
1966
1967
1968
1969-- удаляем ноды если есть запрещенные атрибуты
1970DELETE SGPA FROM #Sorter_GetChute_PostingAttribute SGPA
1971WHERE
1972 EXISTS (
1973 SELECT
1974 1
1975 FROM
1976 #Sorter_GetChute_PostingAttribute_Minus PAM
1977 WHERE
1978 PAM.NodeAttribute = SGPA.NodeAttribute
1979 )
1980
1981-- удаляем все положительные подходящие ноды - остануться неподходящие
1982DELETE SGPAP FROM #Sorter_GetChute_PostingAttribute_Plus_Attr SGPAP
1983WHERE
1984 EXISTS (
1985 SELECT
1986 1
1987 FROM
1988 Sorter.PostingAttribute PA
1989 inner join dbo.ArticleAttribute AA on PA.AttributeID = AA.AttributeID and AA.ArticleID = @ID
1990 where
1991 PA.IsAvailable = 1
1992 and PA.AttributeID = SGPAP.AttributeID
1993 )
1994
1995-- чистка положительных неподходящих нод
1996DELETE SGPA FROM #Sorter_GetChute_PostingAttribute SGPA
1997WHERE
1998 EXISTS (
1999 SELECT
2000 1
2001 FROM
2002 #Sorter_GetChute_PostingAttribute_Plus_Attr PAP
2003 WHERE
2004 PAP.NodeAttribute = SGPA.NodeAttribute
2005 )
2006
2007
2008DELETE FROM #Sorter_GetChute_SortingFlow_Reduce
2009WHERE IV_PostingAttribute IS NOT NULL
2010 AND NOT EXISTS (
2011 SELECT
2012 1
2013 FROM
2014 #Sorter_GetChute_PostingAttribute PA
2015 WHERE
2016 IV_PostingAttribute = PA.NodeAttribute
2017)
2018
2019
2020 -- SET @ConditionParamsPostingAttributes =
2021 -- '; ' + ISNULL(CONVERT(varchar(max), @XmlTmpTable), '')
2022 -- ;
2023
2024END;
2025
2026GO
2027
2028-- --
2029EXECUTE dbo.SysComponentRegister
2030 @ID = NULL
2031, @SysName = N'Sorter.GetPostingAttributeNode';
2032GO
2033
2034SET ANSI_NULLS ON;
2035
2036SET QUOTED_IDENTIFIER ON;
2037GO
2038IF (OBJECT_ID(N'Sorter.GetDeliveryTimeNode', N'P') IS NULL)
2039BEGIN
2040 EXEC (N'CREATE PROCEDURE Sorter.GetDeliveryTimeNode AS RETURN 0');
2041END;
2042GO
2043EXECUTE dbo.ObjectDescriptionSet
2044 @ObjectName = 'Sorter.GetDeliveryTimeNode'
2045, @Description = 'Получение времени (волны) сортировки';
2046GO
2047
2048ALTER PROCEDURE Sorter.GetDeliveryTimeNode
2049 @ID dbo.BIDENT
2050 , @TimeSlotTemplateID dbo.BIDENT
2051 , @TimeSlotDateTimeFrom datetime
2052 , @TimeSlotDateTimeTo datetime
2053 , @CurDateTime datetime
2054 , @ConditionParamsDeliveryTime dbo.string OUTPUT
2055 , @DVD bit OUTPUT -- доставка день в день
2056 , @TimeSlotTimeMiddle time(0) OUTPUT
2057
2058-- procedure result - INSERT INTO #Sorter_GetChute_PostingAttributes
2059
2060AS
2061BEGIN
2062 SET NOCOUNT ON;
2063
2064DECLARE
2065 @TimeSlotDateFrom date = CAST(@TimeSlotDateTimeFrom AS date)
2066 , @TimeSlotTimeFrom time(0) = CAST(@TimeSlotDateTimeFrom AS time(0))
2067 , @TimeSlotTimeTo time(0) = CAST(@TimeSlotDateTimeTo AS time(0))
2068 , @CurDate datetime = CAST(@CurDateTime AS date)
2069 , @CurTime time(0) = CAST(@CurDateTime AS time(0))
2070
2071 ;
2072
2073 SELECT
2074 @DVD = 0
2075 , @ConditionParamsDeliveryTime = NULL;
2076
2077IF OBJECT_ID(N'tempdb..#Sorter_GetChute_GetDeliveryTimeNode_TempNodes', N'U') IS NOT NULL
2078BEGIN
2079 DROP TABLE #Sorter_GetChute_GetDeliveryTimeNode_TempNodes;
2080END;
2081
2082CREATE TABLE #Sorter_GetChute_GetDeliveryTimeNode_TempNodes
2083(
2084 ID dbo.BIDENT NOT NULL
2085 , NodeTime dbo.string
2086 , RelatePriority dbo.string
2087 , Priority int
2088);
2089
2090 -- признак доставка день в день
2091 BEGIN
2092 SELECT
2093 @DVD = 1
2094 FROM
2095 dbo.ArticlePosting ArP
2096 WHERE
2097 ArP.ID = @ID
2098 AND CONVERT(date, ArP.OrderDate) = @TimeSlotDateFrom;
2099 END;
2100
2101 SET @TimeSlotTimeMiddle = CAST(DATEADD(ms, DATEDIFF(ss, @TimeSlotDateTimeFrom, @TimeSlotDateTimeTo) * 500, @TimeSlotDateTimeFrom) AS time(0))
2102
2103INSERT INTO #Sorter_GetChute_GetDeliveryTimeNode_TempNodes
2104 (
2105 ID
2106 , NodeTime
2107 , RelatePriority
2108 , Priority )
2109 SELECT
2110 DT.ID
2111 , DT.NodeTime
2112 , DT.RelatePriority
2113 , DT.Priority
2114 FROM
2115 Sorter.DeliveryTime DT
2116 WHERE
2117 (
2118 (
2119 DT.WaveStartTime is not NULL and DT.WaveEndTime is not NULL and DT.ChangeWaveMiddle is NULL and
2120 @TimeSlotTimeMiddle BETWEEN DT.WaveStartTime
2121 AND
2122 CAST(DATEADD(ms, DATEDIFF(ss, DT.WaveStartTime, DT.WaveEndTime) * 500, DT.WaveStartTime) AS time(0))
2123 )
2124 OR
2125 (
2126 DT.ChangeWaveStart is NULL and DT.ChangeWaveEnd is not NULL and
2127 @TimeSlotTimeTo <= DT.ChangeWaveEnd
2128 )
2129 OR
2130 (
2131 DT.ChangeWaveMiddle is not NULL and DT.WaveStartTime is not NULL
2132 and @TimeSlotTimeMiddle BETWEEN DT.WaveStartTime AND DT.ChangeWaveMiddle
2133 )
2134 OR
2135 (
2136 DT.ChangeWaveStart is not NULL and DT.ChangeWaveEnd is not NULL and
2137 @TimeSlotTimeFrom>= DT.ChangeWaveStart
2138 AND
2139 @TimeSlotTimeTo <= DT.ChangeWaveEnd
2140 )
2141
2142 OR (
2143 (
2144 DT.TSTemplateStart is not NULL AND DT.TSTemplateStart = @TimeSlotTimeFrom
2145 )
2146 AND (
2147 DT.TSTemplateEnd is not NULL AND DT.TSTemplateEnd = @TimeSlotTimeTo
2148 )
2149 )
2150 OR (
2151 DT.TStemplateID is not NULL AND DT.TStemplateID = @TimeSlotTemplateID
2152 )
2153 )
2154 AND
2155 (
2156 @TimeSlotDateFrom BETWEEN CAST(DATEADD(DAY, DT.WaveStartDay, @CurDate) AS date) AND CAST(DATEADD(DAY, DT.WaveEndDay, @CurDate) AS date)
2157 AND @CurTime between DT.CutInTime and DT.CutOffTime
2158 )
2159 AND ( DT.DvD IS NULL OR DT.DvD = @DVD)
2160
2161-- волна
2162INSERT INTO #Sorter_GetChute_DeliveryTime
2163 ( ID, NodeTime )
2164SELECT
2165 TN.ID
2166 , TN.NodeTime
2167FROM
2168 #Sorter_GetChute_GetDeliveryTimeNode_TempNodes TN
2169WHERE
2170 TN.RelatePriority IS NULL
2171
2172INSERT INTO #Sorter_GetChute_DeliveryTime
2173 ( ID, NodeTime )
2174SELECT
2175 RN.ID
2176 , RN.NodeTime
2177FROM (
2178SELECT
2179 row_number() over (partition BY RelatePriority ORDER BY Priority ) as num
2180 , TN.NodeTime
2181 , TN.ID
2182FROM
2183 #Sorter_GetChute_GetDeliveryTimeNode_TempNodes TN
2184WHERE
2185 TN.RelatePriority IS NOT NULL
2186 ) RN
2187WHERE num = 1
2188
2189-- если ничего не начиталось - начитать нулевую волну - для настройки ошибочных кейсов
2190IF NOT EXISTS ( SELECT
2191 1
2192 FROM
2193 #Sorter_GetChute_DeliveryTime)
2194BEGIN
2195 INSERT INTO #Sorter_GetChute_DeliveryTime
2196 ( ID, NodeTime )
2197 VALUES
2198 (99999, 'Wave_Null')
2199END;
2200
2201DELETE FROM #Sorter_GetChute_SortingFlow_Reduce
2202WHERE III_DeliveryTime IS NOT NULL
2203 AND NOT EXISTS (
2204 SELECT
2205 1
2206 FROM
2207 #Sorter_GetChute_DeliveryTime DT
2208 WHERE
2209 III_DeliveryTime = DT.NodeTime
2210)
2211
2212 SET @ConditionParamsDeliveryTime = ''
2213 + ' TimeSlotDateFrom = ' + ISNULL(CONVERT(varchar(255), @TimeSlotDateFrom), '')
2214 + '; TimeSlotTimeFrom = ' + ISNULL(CONVERT(varchar(255), @TimeSlotTimeFrom), '')
2215 + '; TimeSlotTimeMiddle = ' + ISNULL(CONVERT(varchar(255), @TimeSlotTimeMiddle), '')
2216 + '; TimeSlotTimeTo = ' + ISNULL(CONVERT(varchar(255), @TimeSlotTimeTo), '')
2217 + '; DVD = ' + ISNULL(CONVERT(varchar(255), @DVD), '')
2218 ;
2219END;
2220
2221GO
2222
2223EXECUTE dbo.SysComponentRegister
2224 @ID = NULL
2225, @SysName = N'Sorter.GetDeliveryTimeNode';
2226GO
2227--