· 6 years ago · Aug 06, 2019, 06:30 AM
1IF EXISTS
2(
3 SELECT *
4 FROM sys.objects
5 WHERE type = 'P'
6 AND name = 'DMS_pp_TotalFCSSalesReps58603_data'
7)
8 DROP PROCEDURE [DMS_pp_TotalFCSSalesReps58603_data];
9SET QUOTED_IDENTIFIER ON;
10GO
11-- =============================================
12-- Author: Thu.Nguyen
13-- Create date: 2019-06-26
14-- Description: <Store dùng để tính toán data insert vào bảng DMSBLTotalFCSSalesReps58603, xuất rpt 'Trả thưởng SML' >
15--
16-- =============================================
17--EXEC [dbo].[DMS_pp_TotalFCSSalesReps58603_data] @Month = '06', -- char(2)
18-- @Year = '2019', -- char(4)
19-- @LoginID = 'ADMIN', -- nvarchar(20)
20-- @RegionID = NULL, -- varchar(15)
21-- @AreaID = NULL, -- varchar(15)
22-- @CompanyID = 3, -- int
23-- @DistributorID = null -- varchar(max)
24--------------------
25
26CREATE PROCEDURE [dbo].[DMS_pp_TotalFCSSalesReps58603_data]
27AS
28BEGIN
29 SET NOCOUNT ON;
30 SET FMTONLY OFF;
31 SET NO_BROWSETABLE OFF;
32 DECLARE @Year CHAR(4) = DATEPART(YEAR, GETDATE());
33 -- DECLARE @Year CHAR(4)='2019';
34 DECLARE @LoginID NVARCHAR(20) = '27727';
35 DECLARE @RegionID VARCHAR(15) = NULL;
36 DECLARE @AreaID VARCHAR(15) = NULL;
37 DECLARE @CompanyID INT = 3;
38 DECLARE @DistributorID VARCHAR(MAX) = NULL;
39
40 DECLARE @_RegionID VARCHAR(15) = NULLIF(@RegionID, '');
41 DECLARE @_AreaID VARCHAR(15) = NULLIF(@AreaID, '');
42 DECLARE @_CompanyID INT = NULLIF(@CompanyID, 0);
43 DECLARE @_VisitAtLeast DECIMAL(18, 2);
44 DECLARE @_FirstSyncTime VARCHAR(10);
45 DECLARE @_FirstVisit VARCHAR(10);
46 DECLARE @_LastVisit VARCHAR(10);
47 DECLARE @_PCAtLeast INT;
48 DECLARE @_ImageReceive VARCHAR(10);
49 DECLARE @_OutletHasImage DECIMAL(18, 2);
50 DECLARE @_GPS BIT;
51 DECLARE @_AirPlanMode BIT;
52 DECLARE @_VisitTime INT;
53 DECLARE @_VistiTimeValid INT = 15;
54 DECLARE @_VisitTimeMax INT = 30;
55 DECLARE @_RateImageValid DECIMAL(18, 2);
56 DECLARE @_RateDisplayValidOldSM DECIMAL(18, 2);
57 DECLARE @_RateDisplayValidNewSM DECIMAL(18, 2);
58 DECLARE @_CurrentDate DATETIME = CONVERT(DATE, GETDATE());
59
60 --set quyền login id có usersrole nhiều role nhất
61 --DECLARE @LoginID NVARCHAR(10)='27727'
62 DECLARE @_DistributorID VARCHAR(MAX) = NULLIF(LTRIM(RTRIM(@DistributorID)), ''); --nghia.tran: dùng list
63 DECLARE @FromDate DATETIME;
64 -- SET @FromDate = CONVERT(DATETIME,@Year + '-'
65 -- + @Month + '-01');
66 --RETURN
67 DECLARE @ToDate DATETIME;
68 --SET @ToDate = ( SELECT DATEADD(s, -1,
69 -- DATEADD(mm,
70 -- DATEDIFF(m, 0, @FromDate) + 1,
71 -- 0))
72 --);
73
74 DECLARE @param INT = 4;
75 DECLARE @ToDate1 DATE = '2019-07-31'; --GETDATE();
76 DECLARE @FromDate1 DATE = DATEADD(MONTH, DATEDIFF(MONTH, 0, @ToDate1), 0);
77 DECLARE @DateFirstMonth INT = DATEDIFF(dd, @FromDate1, @ToDate1);
78 SET @FromDate1 = CASE
79 WHEN @DateFirstMonth <= @param THEN
80 DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(MONTH, -1, @ToDate1)), 0)
81 ELSE
82 @FromDate1
83 END;
84 SET @ToDate1 = CASE
85 WHEN @DateFirstMonth <= @param THEN
86 EOMONTH(@FromDate1)
87 ELSE
88 @ToDate1
89 END;
90 DECLARE @month CHAR(2) = CASE
91 WHEN @DateFirstMonth <= @param THEN
92 DATEPART(MONTH, EOMONTH(@FromDate1))
93 ELSE
94 DATEPART(MONTH, EOMONTH(@ToDate1))
95 END;
96 SET @month = CASE
97 WHEN @month < 10 THEN
98 '0' + @month
99 ELSE
100 @month
101 END;
102 DECLARE @LinkedVisibility VARCHAR(10) = --'STAGING';
103 '[10.86.67.16]';
104 SELECT TOP 1
105 @FromDate = MonthStartDate,
106 @ToDate = MonthEndDate
107 FROM dbo.DMSBLSalesCalendarMonth
108 WHERE CompanyID = @_CompanyID
109 AND Year = @Year
110 AND Month = @month;
111
112 IF OBJECT_ID('tempdb..#Permission') IS NOT NULL
113 DROP TABLE #Permission;
114 IF OBJECT_ID('tempdb..#tmpDisplay') IS NOT NULL
115 DROP TABLE #tmpDisplay;
116 IF OBJECT_ID('tempdb..#tmpTrackingSM') IS NOT NULL
117 DROP TABLE #tmpTrackingSM;
118 IF OBJECT_ID('tempdb..#tmpSDO') IS NOT NULL
119 DROP TABLE #tmpSDO;
120 IF OBJECT_ID('tempdb..#tmpSDOPivot') IS NOT NULL
121 DROP TABLE #tmpSDOPivot;
122 IF OBJECT_ID('tempdb..#tmpFullMCP') IS NOT NULL
123 DROP TABLE #tmpFullMCP;
124 IF OBJECT_ID('tempdb..#tmpMCP') IS NOT NULL
125 DROP TABLE #tmpMCP;
126 IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
127 DROP TABLE #tmp;
128 IF OBJECT_ID('tempdb..#tmpFCS') IS NOT NULL
129 DROP TABLE #tmpFCS;
130 IF OBJECT_ID('tempdb..#tmpMCPVisitDate') IS NOT NULL
131 DROP TABLE #tmpMCPVisitDate;
132 IF OBJECT_ID('tempdb..#tmpFCSTranDate') IS NOT NULL
133 DROP TABLE #tmpFCSTranDate;
134 IF OBJECT_ID('tempdb..#tmpResult') IS NOT NULL
135 DROP TABLE #tmpResult;
136 IF OBJECT_ID('tempdb..#tmpBigCola') IS NOT NULL
137 DROP TABLE #tmpBigCola;
138
139 IF OBJECT_ID('tempdb..#tmpRouteMaster') IS NOT NULL
140 DROP TABLE #tmpRouteMaster;
141
142 --Chuyển list @_DistributorID về array
143 SELECT *
144 INTO #ListDistributor
145 FROM dbo.fn_DMS_ConvertIDListToTable(@_DistributorID);
146
147 SET @month = ISNULL(@month, MONTH(GETDATE()));
148 SET @Year = ISNULL(@Year, YEAR(GETDATE()));
149
150 SELECT RegionID,
151 AreaID,
152 DistributorID,
153 CompanyID,
154 TerritoryType,
155 RegionCD,
156 AreaCD
157 INTO #Permission
158 FROM dbo.DMSBLSFPermission
159 WHERE LoginID = @LoginID
160 AND
161 (
162 RegionCD = @_RegionID
163 OR @_RegionID IS NULL
164 )
165 AND
166 (
167 AreaCD = @_AreaID
168 OR @_AreaID IS NULL
169 )
170 AND
171 (
172 @_DistributorID IS NULL
173 OR EXISTS
174 (
175 SELECT * FROM #ListDistributor d WHERE d.ID = DistributorID
176 )
177 )
178 AND CompanyID = @_CompanyID
179 GROUP BY RegionID,
180 AreaID,
181 DistributorID,
182 CompanyID,
183 TerritoryType,
184 RegionCD,
185 AreaCD;
186
187 ---- Lấy working position của user đăng nhập
188 DECLARE @_TerritoryType CHAR(1);
189 SELECT @_TerritoryType =
190 (
191 SELECT TOP 1 TerritoryType FROM #Permission
192 );
193 IF @_TerritoryType IS NULL
194 SET @_TerritoryType = 'X';
195
196 SELECT DISTINCT
197 CompanyID,
198 RegionID
199 INTO #tmpprmr
200 FROM #Permission;
201 SELECT DISTINCT
202 CompanyID,
203 AreaID
204 INTO #tmpprma
205 FROM #Permission;
206 SELECT DISTINCT
207 CompanyID,
208 DistributorID
209 INTO #tmpprmd
210 FROM #Permission;
211
212 IF
213 (
214 SELECT COUNT(*) FROM #Permission
215 ) = 0
216 BEGIN
217 IF @_DistributorID IS NULL
218 BEGIN
219 INSERT INTO #tmpprmd
220 SELECT @CompanyID,
221 BranchID
222 FROM THPDMS2.dbo.Branch
223 WHERE CompanyID = @CompanyID;
224 END;
225 ELSE
226 BEGIN
227 INSERT INTO #tmpprmd
228 SELECT @CompanyID,
229 ID
230 FROM #ListDistributor;
231 END;
232 END;
233 DECLARE @PeriodID NVARCHAR(6);
234
235 SET @PeriodID = RIGHT('0' + CONVERT(NVARCHAR(2), @month), 2) + CONVERT(NVARCHAR(4), @Year);
236
237 --- Tính plan mandays - số ngày bán hàng trong tháng
238 DECLARE @PlanMandays INT;
239 SET @PlanMandays =
240 (
241 SELECT [dbo].[CountSellingDayPerMonth](@month, @Year)
242 );
243
244 SELECT @_VisitAtLeast = c.MinVisit,
245 @_FirstSyncTime = c.BeginDaySync,
246 @_FirstVisit = c.FirstVisitBefore,
247 @_LastVisit = c.LastVisitAfter,
248 @_PCAtLeast = c.MinPC,
249 @_ImageReceive = c.SendImageBefore,
250 @_OutletHasImage = c.PercentImageToServerOnTime,
251 @_GPS = ISNULL(c.GPS, 0),
252 @_AirPlanMode = ISNULL(c.AirplaneMode, 0),
253 @_VisitTime = c.MinTimeVisit,
254 @_VistiTimeValid = c.MaxTimeVisit,
255 @_VisitTimeMax = c.WarningTimeVisit,
256 @_RateImageValid = ISNULL(c.PercentPassImage, 0),
257 @_RateDisplayValidOldSM = ISNULL(c.MinPassDisplayOldSM, 0),
258 @_RateDisplayValidNewSM = ISNULL(c.MinPassDisplayNewSM, 0)
259 FROM THPDMS2.dbo.DMSReportParameterConfig c
260 WHERE c.PeriodID = @PeriodID
261 AND c.CompanyID = @_CompanyID;
262
263 ---- select mcp
264 --nghia.tran comment 2018.06.04
265 -- SELECT mcp.CompanyID ,
266 --mcp.BranchID,
267 --mcp.BranchCD,
268 -- mcp.RouteID ,
269 -- mcp.SalespersonCD ,
270 -- mcp.OutletID ,
271 -- mcp.OutletCD ,
272 -- mcp.VisitDate
273 -- INTO #tmpFullMCP
274 -- FROM dbo.DMSBLMCPAssignment mcp
275 -- WHERE mcp.PeriodID = @PeriodID
276 -- --AND EXISTS ( SELECT *
277 -- -- FROM #tmpprmr
278 -- -- WHERE mcp.RegionID = #tmpprmr.RegionID )
279 -- --AND EXISTS ( SELECT *
280 -- -- FROM #tmpprma
281 -- -- WHERE mcp.AreaID = #tmpprma.AreaID )
282 -- AND EXISTS ( SELECT *
283 -- FROM #tmpprmd
284 -- WHERE mcp.BranchID = #tmpprmd.DistributorID
285 -- AND mcp.CompanyID = #tmpprmd.CompanyID)
286
287 -- CREATE NONCLUSTERED INDEX [tmpMCP_Index] ON #tmpFullMCP(CompanyID ASC, BranchID ASC, RouteID ASC, SalespersonCD ASC);
288
289 --.nghia.tran
290
291
292 SELECT DISTINCT
293 CompanyID = 3,
294 BranchID = mcp.DistributorID,
295 BranchCD = dis.DistributorCD,
296 RouteID = mcp.RouteID,
297 SalespersonCD = mcp.SalesmanID,
298 OutletID = cus.CustomerID,
299 OutletCD = mcp.OutletID,
300 mcp.VisitDate,
301 MCP = COUNT(mcp.OutletID)
302 INTO #tmpFullMCP
303 FROM [10.86.67.16].[THPeRoute2].dbo.VisitPlanHistory mcp WITH (NOLOCK)
304 INNER JOIN [THPDMS2].dbo.DMSViewDistributor dis
305 ON dis.DistributorID = mcp.DistributorID
306 AND dis.CompanyID = 3
307 INNER JOIN [THPDMS2].dbo.DMSViewCustomerLocation cus
308 ON mcp.OutletID = cus.CustomerCD
309 AND cus.CompanyID = 3
310 WHERE RIGHT('0' + CONVERT(VARCHAR(2), MONTH(mcp.VisitDate)), 2) + CONVERT(CHAR(4), YEAR(mcp.VisitDate)) = @PeriodID
311 AND EXISTS
312 (
313 SELECT * FROM #tmpprmd WHERE mcp.DistributorID = #tmpprmd.DistributorID
314 )
315
316 --and mcp.SalesmanID = 'SM00016655'
317 -- and mcp.routeID = 'RR001985'
318 GROUP BY mcp.DistributorID,
319 dis.DistributorCD,
320 mcp.SalesmanID,
321 mcp.RouteID,
322 mcp.VisitDate,
323 cus.CustomerID,
324 mcp.OutletID;
325
326
327 ---- select fcs
328 SELECT FCS.CompanyID,
329 FCS.RouteID,
330 FCS.SalePersonCD,
331 FCS.TranDate,
332 FCS.PC,
333 FCS.LPPCTarget,
334 FCS.TL,
335 FCS.Target,
336 FCS.TotalOrder,
337 FCS.ManuallyOrder,
338 FCS.SaleOut,
339 FCS.SDO1Target,
340 FCS.SDO1Type,
341 FCS.SDO2Target,
342 FCS.SDO2Type,
343 FCS.SDO3Target,
344 FCS.SDO3Type,
345 FCS.SDO4Target,
346 FCS.SDO4Type,
347 FCS.SDO5Target,
348 FCS.SDO5Type
349 INTO #tmp
350 FROM [dbo].DMSBLFCS AS FCS
351 WHERE FCS.PeriodNbr = @PeriodID
352 AND FCS.RouteID != 'Other'
353 --AND EXISTS ( SELECT *
354 -- FROM #tmpprmr
355 -- WHERE FCS.RegionID = #tmpprmr.RegionID )
356 --AND EXISTS ( SELECT *
357 -- FROM #tmpprma
358 -- WHERE FCS.AreaID = #tmpprma.AreaID )
359 AND EXISTS
360 (
361 SELECT * FROM #tmpprmd WHERE FCS.CompanyID = #tmpprmd.DistributorID
362 );
363 --test
364
365 SELECT sp.CompanyID,
366 SalespersonCD = sp.EmployeeCD,
367 UsrInitDate = COALESCE(BL.TranDate, sp.EndDate),
368 sp.EndDate,
369 leavePerson.LeaveDate
370 INTO #tmpSalesmanInfo
371 FROM THPDMS2.dbo.DMSSalesForce sp
372 LEFT JOIN
373 (
374 SELECT SalePersonCD,
375 TranDate = MIN(TranDate)
376 FROM [dbo].DMSBLFCS AS FCS
377 WHERE FCS.RouteID != 'Other'
378 --AND EXISTS ( SELECT *
379 -- FROM #tmpprmr
380 -- WHERE FCS.RegionID = #tmpprmr.RegionID )
381 --AND EXISTS ( SELECT *
382 -- FROM #tmpprma
383 -- WHERE FCS.AreaID = #tmpprma.AreaID )
384 AND EXISTS
385 (
386 SELECT * FROM #tmpprmd WHERE FCS.CompanyID = #tmpprmd.DistributorID
387 )
388 GROUP BY SalePersonCD
389 ) BL
390 ON BL.SalePersonCD = sp.EmployeeCD
391 LEFT JOIN THPDMS2.dbo.DMSSurveyLeaveSalesperson leavePerson --ON leavePerson.CompanyID = sp.CompanyID
392 -- AND
393 ON leavePerson.SalesmanID = sp.EmployeeCD
394 AND leavePerson.CompanyID = @_CompanyID
395 WHERE sp.CompanyID = @_CompanyID
396 GROUP BY sp.EmployeeCD,
397 sp.EndDate,
398 BL.TranDate,
399 leavePerson.LeaveDate,
400 sp.CompanyID;
401 -- Select * from #tmpSalesmanInfo where SalespersonCD='SM00017141'
402
403
404 SELECT CompanyID = MCP.BranchID,
405 MCP.RouteID,
406 MCP.BranchID,
407 MCP.SalespersonCD,
408 UsrInitDate,
409 TargetMandays = CASE
410 WHEN @month = MONTH(sp.UsrInitDate)
411 AND @Year = YEAR(sp.UsrInitDate) THEN
412 (
413 SELECT [dbo].[CountSellingDayPerMonthWithStartDate](UsrInitDate, @month, @Year)
414 )
415 ELSE
416 @PlanMandays
417 END,
418 TotalOutlet = COUNT(DISTINCT MCP.OutletID),
419 NewSalesman = CASE
420 WHEN @month = MONTH(sp.UsrInitDate)
421 AND @Year = YEAR(sp.UsrInitDate) THEN
422 'X'
423 ELSE
424 ''
425 END,
426 -- NVBH Mới tháng N-2( Có ngày phát sinh PC sau ngày 15 của Tháng N-2)
427 NewSalesmanIn2Months = CASE
428 WHEN @month - 2 = MONTH(sp.UsrInitDate)
429 AND @Year = YEAR(sp.UsrInitDate)
430 AND 15 < DAY(sp.UsrInitDate) THEN
431 'X'
432 ELSE
433 ''
434 END,
435 -- NVBH Mới tháng N-1
436 NewSalesmanIn1Months = CASE
437 WHEN @month - 1 = MONTH(sp.UsrInitDate)
438 AND @Year = YEAR(sp.UsrInitDate) THEN
439 'X'
440 ELSE
441 ''
442 END,
443 OldSalesman = CASE
444 WHEN @month = MONTH(sp.EndDate)
445 AND @Year = YEAR(sp.EndDate) THEN
446 'X'
447 ELSE
448 ''
449 END,
450 sp.LeaveDate
451 INTO #tmpMCP
452 FROM #tmpFullMCP AS MCP
453 INNER JOIN #tmpSalesmanInfo sp
454 ON MCP.SalespersonCD = sp.SalespersonCD
455 AND sp.CompanyID = MCP.CompanyID
456 GROUP BY MCP.CompanyID,
457 MCP.RouteID,
458 MCP.SalespersonCD,
459 sp.UsrInitDate,
460 sp.EndDate,
461 sp.LeaveDate,
462 MCP.BranchID;
463 -- select * from #tmpMCP
464 --return;
465 CREATE NONCLUSTERED INDEX [tmpMCP_Index]
466 ON #tmpMCP
467 (
468 CompanyID,
469 BranchID
470 );
471
472 --Select * from #tmpFullMCP
473 --Select * from #tmpMCP return;
474
475 SELECT FCS.CompanyID,
476 FCS.RouteID,
477 FCS.SalePersonCD,
478 ActualMandays = SUM( CASE
479 WHEN FCS.PC >= @_PCAtLeast THEN
480 1
481 ELSE
482 0
483 END
484 ), --COUNT(DISTINCT FCS.TranDate)
485 TargetPCLPPC = CASE
486 WHEN @month = MONTH(sp.UsrInitDate)
487 AND @Year = YEAR(sp.UsrInitDate) THEN
488 (CAST(MAX(ISNULL(FCS.LPPCTarget, 0)) AS DECIMAL(18, 6)) / @PlanMandays) *
489 (
490 SELECT [dbo].[CountSellingDayPerMonthWithStartDate](UsrInitDate, @month, @Year)
491 )
492 ELSE
493 MAX(ISNULL(FCS.LPPCTarget, 0))
494 END,
495 ActualPCLPPC = SUM(ISNULL(FCS.TL, 0)),
496 TargetRouteSales = CASE
497 WHEN @month = MONTH(sp.UsrInitDate)
498 AND @Year = YEAR(sp.UsrInitDate) THEN
499 (CAST(MAX(ISNULL(FCS.Target, 0)) AS DECIMAL(18, 6)) / @PlanMandays) *
500 (
501 SELECT [dbo].[CountSellingDayPerMonthWithStartDate](UsrInitDate, @month, @Year)
502 )
503 ELSE
504 MAX(ISNULL(FCS.Target, 0))
505 END,
506 ActualRouteSales = SUM(ISNULL(FCS.SaleOut, 0)),
507 TotalOrder = SUM(ISNULL(FCS.TotalOrder, 0)),
508 ManuallyOrder = SUM(ISNULL(FCS.ManuallyOrder, 0)),
509 TotalSaleOut = SUM(ISNULL(SaleOut, 0)),
510 TotalPC = SUM(ISNULL(PC, 0)),
511 StartDatePC = MIN(FCS.TranDate),
512 EndDatePC = MAX(FCS.TranDate),
513 AVGPC = CASE
514 WHEN COUNT(DISTINCT FCS.TranDate) = 0 THEN
515 SUM(ISNULL(PC, 0))
516 ELSE
517 SUM(ISNULL(PC, 0)) / COUNT(DISTINCT FCS.TranDate)
518 END
519 INTO #tmpFCS
520 FROM #tmp AS FCS
521 INNER JOIN #tmpSalesmanInfo sp
522 ON FCS.SalePersonCD = sp.SalespersonCD
523 GROUP BY FCS.CompanyID,
524 FCS.RouteID,
525 FCS.SalePersonCD,
526 sp.UsrInitDate;
527
528 --CREATE NONCLUSTERED INDEX [tmpFCS_Index] ON #tmpFCS(CompanyID);
529
530
531 DECLARE @FirstDay DATETIME;
532 DECLARE @LastDay DATETIME;
533 SET @FirstDay = @FromDate;
534 SET @LastDay = DATEADD(d, -1, DATEADD(m, 1, @FirstDay));
535 DECLARE @sql NVARCHAR(MAX);
536 SET @sql
537 = 'SET NOCOUNT ON;
538 SELECT
539 DistributorID
540 , RouteID
541 , SalesmanID
542 , MMCP
543 , MMCPSales
544 , MMCPSalesByVisitPlan
545 , MImageCount
546 , MSMLastPass
547 , MSMLastPass_MMCPSales
548 , MImagePass
549 , MImagePass_MMCPSales
550 , Result_OutletHasImage_MCP
551 , strResult_OutletHasImage_MCP
552 FROM
553 THPVisibility2.dbo.[fnCalcSalesForceCompliance_Daily18871] (1,''''' + CONVERT(VARCHAR(20), @FirstDay, 111)
554 + ''''',''''' + CONVERT(VARCHAR(20), @LastDay, 111) + ''''',''''' + ISNULL(@_RegionID, '') + ''''','''''
555 + ISNULL(@_AreaID, '') + ''''','''''''',' + CONVERT(VARCHAR(20), ISNULL(@_DistributorID, 0))
556 + ','''''''','''''''',0,0,1,0,''''' + @LoginID + ''''')';
557 -- Baseline theo ngày
558 DECLARE @sqlDaily NVARCHAR(MAX);
559 SET @sqlDaily
560 = 'SET NOCOUNT ON;
561 SELECT
562 DistributorID
563 , RouteID
564 , SalesmanID
565 , VisitDate -- Cot ngay
566 , RateImageValid -- % Hình Chụp Hợp Lệ
567 FROM
568 THPVisibility2.dbo.[fnCalcSalesForceCompliance_VisitDaily12962] (1,''''' + CONVERT(VARCHAR(20), @FirstDay, 111)
569 + ''''',''''' + CONVERT(VARCHAR(20), @LastDay, 111) + ''''',''''' + ISNULL(@_RegionID, '') + ''''','''''
570 + ISNULL(@_AreaID, '') + ''''','''''''',' + CONVERT(VARCHAR(20), ISNULL(@_DistributorID, 0))
571 + ','''''''','''''''',0,0,1,0,''''' + @LoginID + ''''')';
572
573 CREATE TABLE #tmpDisplay
574 (
575 DistributorID INT,
576 RouteID NVARCHAR(50),
577 SalesmanID NVARCHAR(50),
578 MMCP DECIMAL(18, 2),
579 MMCPSales DECIMAL(18, 2),
580 MMCPSalesByVisitPlan DECIMAL(18, 2),
581 MImageCount DECIMAL(18, 2),
582 MSMLastPass DECIMAL(18, 2),
583 MSMLastPass_MMCPSales DECIMAL(18, 2),
584 MImagePass DECIMAL(18, 2),
585 MImagePass_MMCPSales DECIMAL(18, 2),
586 Result_OutletHasImage_MCP INT,
587 strResult_OutletHasImage_MCP NVARCHAR(50)
588 );
589
590 CREATE TABLE #tmpDisplayDaily
591 (
592 DistributorID INT,
593 RouteID NVARCHAR(50),
594 SalesmanID NVARCHAR(50),
595 VisitDate SMALLDATETIME,
596 PercentImgPassMCP DECIMAL(12, 2)
597 );
598
599 DECLARE @openquery NVARCHAR(MAX);
600 SET @openquery = '
601 INSERT INTO #tmpDisplay
602 SELECT
603 *
604 FROM OPENQUERY([10.86.67.16],''' + @sql + ''')';
605 --[10.86.67.16]
606
607 EXEC sys.sp_executesql @openquery;
608 --SELECT * FROM #tmpDisplay
609
610 DECLARE @openqueryDaily NVARCHAR(MAX);
611 SET @openqueryDaily = '
612 INSERT INTO #tmpDisplayDaily
613 SELECT
614 *
615 FROM OPENQUERY([10.86.67.16],''' + @sqlDaily + ''')';
616 EXEC sys.sp_executesql @openqueryDaily;
617
618
619
620 ---- select tracking sm
621 DECLARE @DayInMonth INT;
622 DECLARE @i INT;
623 SET @DayInMonth = DATEDIFF(d, @FirstDay, @LastDay) + 1;
624 SET @i = 1;
625
626 SELECT mcp.CompanyID,
627 mcp.BranchID,
628 mcp.BranchCD,
629 mcp.RouteID,
630 mcp.SalespersonCD,
631 mcp.VisitDate,
632 COUNT(mcp.OutletID) AS MCP
633 INTO #tmpMCPVisitDate
634 FROM #tmpFullMCP mcp
635 GROUP BY mcp.CompanyID,
636 mcp.RouteID,
637 mcp.SalespersonCD,
638 mcp.VisitDate,
639 mcp.BranchID,
640 mcp.BranchCD;
641
642 SELECT fcs.CompanyID,
643 fcs.RouteID,
644 fcs.SalePersonCD,
645 fcs.TranDate,
646 fcs.PC
647 INTO #tmpFCSTranDate
648 FROM #tmp fcs
649 GROUP BY fcs.CompanyID,
650 fcs.RouteID,
651 fcs.SalePersonCD,
652 fcs.TranDate,
653 fcs.PC;
654
655 --SELECT SalesmanCode ,
656 -- DistributorCode ,
657 -- WorkingDate ,
658 -- FirstSyncCheck = CASE WHEN CONVERT(TIME(0), CONVERT(VARCHAR(50), FirstSyncTime)) <= @_FirstSyncTime
659 -- THEN 1
660 -- ELSE 0
661 -- END
662 --INTO #tmpSMFirstSync
663 --FROM [192.168.1.14].eRoute.dbo.SalesmanFirstSync
664 --WHERE MONTH(WorkingDate) = @Month
665 -- AND YEAR(WorkingDate) = @Year
666 -- AND DistributorCode IN ( SELECT DISTINCT
667 -- DistributorID
668 -- FROM #Permission );
669
670 PRINT (CONVERT(NVARCHAR(50), GETDATE(), 109) + ' tmpSMFirstSync');
671
672
673 SELECT DISTINCT
674 DistributorCode,
675 SalesmanCode,
676 WorkingDate = WorkingDate,
677 FirstSyncCheck = CASE
678 WHEN CONVERT(TIME(0), CONVERT(VARCHAR(50), FirstSyncTime)) <= @_FirstSyncTime THEN
679 1
680 ELSE
681 0
682 END,
683 FirstSyncTime = CONVERT(TIME(0), FirstSyncTime)
684 INTO #tmpSMFirstSync
685 FROM
686 (
687 SELECT RowNum = ROW_NUMBER() OVER (PARTITION BY SalesmanCode,
688 DistributorCode,
689 VisitDate
690 ORDER BY SyncTime ASC
691 ),
692 DistributorCode,
693 SalesmanCode,
694 WorkingDate = VisitDate,
695 FirstSyncTime = SyncTime
696 FROM
697 (
698 SELECT DistanceValid = CASE
699 WHEN
700 --Nếu có khóa khoảng cách, và khoảng cách hợp lệ -> hợp lệ
701 SMSync.[CheckDistance] = 1
702 AND SMSync.[Distance] <= SMSync.[DistanceTarget] THEN
703 1
704 WHEN
705 --Nếu không khóa khoảng cách -> hợp lệ
706 (
707 SMSync.[CheckDistance] = 0
708 OR SMSync.ByPass = 1
709 )
710 AND SMSync.LatitudeSync <> 0.0000000000 -- loai truong hop zero GPS van tinh hop le
711 AND SMSync.LatitudeBase <> 0.0000000000 THEN
712 1
713 WHEN SMSync.ByPass = 1 -- them truong hop zero gps nhung dc bypass anh.nguyen 20180618
714 THEN
715 1
716 ELSE
717 0
718 END,
719 *
720 FROM [10.86.67.16].THPeRoute2.dbo.AIMSyncStore SMSync
721 WHERE VisitDate
722 BETWEEN @FromDate AND @ToDate
723 --AND DistributorCode IN (
724 --SELECT DISTINCT
725 -- DistributorID
726 --FROM #Permission )
727 ) X
728 WHERE DistanceValid = 1
729 ) XX
730 WHERE XX.RowNum = 1;
731
732 CREATE NONCLUSTERED INDEX [IX_SMFirstSync]
733 ON #tmpSMFirstSync
734 (
735 DistributorCode,
736 SalesmanCode,
737 WorkingDate
738 );
739 PRINT (CONVERT(NVARCHAR(50), GETDATE(), 109) + ' #tmpSMFirstSync');
740
741
742
743
744
745
746
747
748 ---- Lấy tất cả đơn hàng trong khoảng thời gian đã chọn, không quan tâm hoàn tất hay không
749 SELECT soo.CompanyID,
750 DistributorID = soo.BranchID,
751 --, DistributorLocationID = NULL
752 soo.OrderNbr,
753 soo.OrderType,
754 soo.OrderDate,
755 soo.CustomerOrderNbr,
756 soo.CustomerID,
757 soo.CustomerLocationID,
758 SalesRepsID = soo.UsrSalesReps,
759 sf.EmployeeCD,
760 Route = soo.UsrRoute,
761 sol.InventoryID,
762 sol.UnitPrice,
763 sol.LineNbr,
764 OrderQty = ISNULL(sol.UsrOrgQty, sol.BaseOrderQty),
765 ShippedQty = ISNULL(sol.ShippedQty, 0),
766 --, OrderAmt = ISNULL(sol.UsrOrgQty, sol.BaseOrderQty) * sol.UnitPrice - ISNULL(sol.UsrOrgDiscAmt, 0)
767 --, ShippedAmt = ISNULL(sol.ShippedQty,0) * sol.UnitPrice ,
768 --, sol.IsFree
769 --, IsDelete = CASE
770 -- WHEN soo.Cancelled = 1 THEN 1
771 -- WHEN ISNULL(soo.UsrIsDispose, 0) = 1 THEN 1
772 -- WHEN soo.OrderType = 'CM' AND ISNULL(soo.UsrIsRevenueDeduction, 0) = 0 THEN 1
773 -- END,
774 soo.Status,
775 soshipment.ShipDate
776 INTO #tmpAllOrder
777 FROM THPDMS2.dbo.SOOrder soo WITH (NOLOCK)
778 JOIN THPDMS2.dbo.SOLine sol WITH (NOLOCK)
779 ON sol.CompanyID = soo.CompanyID
780 AND sol.OrderNbr = soo.OrderNbr
781 AND sol.OrderType = soo.OrderType
782 AND sol.BranchID = soo.BranchID
783 JOIN THPDMS2.dbo.SOOrderShipment soshipment
784 ON soshipment.CompanyID = soo.CompanyID
785 AND soshipment.OrderNbr = soo.OrderNbr
786 AND soshipment.OrderType = soo.OrderType
787 JOIN THPDMS2.dbo.DMSSalesForce sf
788 ON sf.CompanyID = soo.CompanyID
789 AND sf.EmployeeID = soo.UsrSalesReps
790 WHERE soo.CompanyID = @_CompanyID
791 AND soo.OrderType = 'SO'
792 AND soo.UsrReturnType IS NULL
793 AND
794 (
795 soo.BranchID IN (
796 SELECT ID FROM #ListDistributor
797 )
798 OR @_DistributorID IS NULL
799 )
800 AND MONTH(soo.OrderDate) = @month
801 AND YEAR(soo.OrderDate) = @Year
802 AND soo.UsrSourceType = 'PDA'
803 AND sol.IsFree = 0
804 AND soo.Status = 'C';
805 -- AND ( @_TerritoryType = 'X'
806 --OR EXISTS (SELECT TOP 1 * FROM #tmpPrmRoute pr WHERE pr.RouteCD = soo.UsrRoute)
807 -- ); ---- Kiểm tra permission với working position là SS
808
809 CREATE NONCLUSTERED INDEX [#tmpAllOrder_index]
810 ON #tmpAllOrder
811 (
812 CompanyID,
813 OrderNbr,
814 OrderType,
815 LineNbr
816 );
817 --CREATE NONCLUSTERED INDEX [#tmpAllOrder_index_delete] ON #tmpAllOrder(IsDelete)
818
819 DECLARE @_Weekend CHAR(3) = (
820 SELECT Weekend
821 FROM THPDMS2.dbo.DMSSalesCalendar WITH (NOLOCK)
822 WHERE CompanyID = @_CompanyID
823 AND [Year] = @Year
824 );
825 ---- Lấy raw data
826 SELECT ao.CompanyID,
827 ao.DistributorID,
828 --, ao.DistributorLocationID
829 ao.OrderNbr,
830 ao.OrderType,
831 ao.OrderDate,
832 ao.CustomerOrderNbr,
833 ao.CustomerID,
834 ao.CustomerLocationID,
835 ao.SalesRepsID,
836 SalespersonCD = ao.EmployeeCD,
837 ao.Route,
838 --Đếm số đơn hàng trạng thái C và không bị trả hàng có ngày giao hàng – ngày đặt hàng > 1 của NVBH trong tháng xem báo cáo
839 TuanThuPDA = CASE
840 WHEN ao.Status = 'C'
841 AND DATEDIFF(dd, ao.OrderDate, ao.ShipDate) > 1
842 AND UPPER(SUBSTRING(DATENAME(dw, ao.OrderDate), 1, 3)) = ISNULL(@_Weekend, '')
843 AND EXISTS
844 (
845 SELECT TOP 1
846 *
847 FROM THPDMS2.dbo.DMSHoliday h
848 WHERE CONVERT(INT, h.Year) = @Year
849 AND ao.OrderDate
850 BETWEEN h.FromDate AND h.ToDate
851 ) THEN
852 1
853 ELSE
854 0
855 END,
856 -- Đếm số lượng đơn hàng giao hàng 1 phần không bị trả hàng của NVBH trong tháng xem báo cáo
857 Giao1Phan = CASE
858 WHEN SUM(ao.OrderQty - ao.ShippedQty) > 0
859 AND ao.Status = 'C' THEN
860 1
861 ELSE
862 0
863 END
864 INTO #tmpRawData
865 FROM #tmpAllOrder ao
866 GROUP BY ao.CompanyID,
867 --, ao.DistributorID
868 --, ao.DistributorLocationID
869 ao.OrderNbr,
870 ao.OrderType,
871 ao.OrderDate,
872 ao.ShipDate,
873 ao.CustomerOrderNbr,
874 ao.CustomerID,
875 ao.CustomerLocationID,
876 ao.SalesRepsID,
877 ao.Route,
878 ao.Status,
879 ao.DistributorID,
880 ao.EmployeeCD;
881
882 CREATE NONCLUSTERED INDEX [#tmpRawData_index]
883 ON #tmpRawData
884 (
885 CompanyID,
886 CustomerID,
887 CustomerLocationID,
888 SalesRepsID
889 );
890 CREATE NONCLUSTERED INDEX [#tmpRawData_index_update]
891 ON #tmpRawData
892 (
893 CompanyID,
894 OrderDate,
895 SalesRepsID
896 );
897 -- SELECT * FROM #tmpRawData
898 --RETURN
899
900 --select * from #tmpSMFirstSync
901 --where SalesmanCode = 'SM00017721'
902 --return;
903
904 SELECT RegionID = CONVERT(NVARCHAR(50), ''),
905 AreaID = CONVERT(NVARCHAR(50), ''),
906 distributor_code,
907 customer_code,
908 sale_code,
909 start_time,
910 end_time,
911 RouteID,
912 reason,
913 IsEnableAirPlaneMode,
914 IsEnableGPSMode,
915 IsEnableNetworkMode,
916 IsRightRoute,
917 created_at
918 INTO #tmpeRouteOrder
919 FROM [STAGING].[THPeRoute2].dbo.orders
920 WHERE created_at
921 BETWEEN @FromDate AND @ToDate
922 AND RouteID IS NOT NULL;
923 --and distributor_code IN (SELECT DISTINCT DistributorID FROM #Permission)
924
925
926 DELETE FROM #tmpeRouteOrder
927 WHERE distributor_code NOT IN (
928 SELECT DISTINCT DistributorID FROM #Permission
929 );
930
931 UPDATE ero
932 SET RegionID = d.RegionID,
933 AreaID = d.AreaID
934 FROM #tmpeRouteOrder ero
935 JOIN vs_GetDistributorLastest d
936 ON d.CompanyID = ero.distributor_code;
937
938 PRINT (CONVERT(NVARCHAR(50), GETDATE(), 109) + ' tmpTrackFromERoute');
939 SELECT DISTINCT
940 distributor_code,
941 sale_code,
942 created_at,
943 TotalVisit = SUM(IsVisit),
944 TotalVisitTimeViolate = SUM(VisitTimeLess),
945 -- Cột mới để đánh dấu là fail ngày làm việc
946 ActualFail = CASE
947 WHEN SUM(VisitTimeOver) >= 1
948 OR SUM(t.VisitTimeLess) > 5 THEN
949 1
950 ELSE
951 0
952 END,
953 VisitOverValidTime = SUM(VisitTimeOver),
954 FirstVisit = MIN(FirstVisit),
955 LastVisit = MAX(LastVisit),
956 IsEnableGPSMode = MIN(IsEnableGPSMode),
957 IsEnableAirPlaneMode = MAX(IsEnableAirPlaneMode)
958 INTO #tmpTrackFromERoute
959 FROM
960 (
961 SELECT DISTINCT
962 distributor_code,
963 customer_code,
964 sale_code,
965 created_at,
966 IsVisit = MAX(IsVisit),
967 -- đếm lượt viếng thăm hợp lệ trong thời gian cho phép
968 VisitTimeCheck = CASE
969 WHEN MAX( CASE NotCheckVisitTime
970 WHEN 1 THEN
971 NULL
972 ELSE
973 VisitTime
974 END
975 ) IS NULL THEN
976 -1
977 WHEN MAX( CASE NotCheckVisitTime
978 WHEN 1 THEN
979 NULL
980 ELSE
981 VisitTime
982 END
983 )
984 BETWEEN @_VisitTime AND @_VistiTimeValid THEN
985 1
986 ELSE
987 0
988 END,
989 -- Đánh dấu lượt viết thăm >30 p hoac chi vieng tham 1 lan co thoi gian >15
990 VisitTimeOver = CASE
991 WHEN (MAX( CASE NotCheckVisitTime
992 WHEN 1 THEN
993 NULL
994 ELSE
995 VisitTime
996 END
997 ) > @_VisitTimeMax
998 )
999 OR
1000 (
1001 MAX( CASE NotCheckVisitTime
1002 WHEN 1 THEN
1003 NULL
1004 ELSE
1005 VisitTime
1006 END
1007 ) > @_VistiTimeValid
1008 AND SUM(IsVisit) = 1
1009 )
1010 OR
1011 (
1012 SUM(IsVisit) > 1
1013 AND
1014 -- khong co luot vieng tham nao hop le va cac luot deu >15p > 30p
1015 SUM(CountNotOverValid) = 0
1016 AND
1017 (
1018 SUM(CountOverValid) > 1
1019 OR SUM(CountOverValidMax) > 0
1020 )
1021 ) THEN
1022 1
1023 ELSE
1024 0
1025 END,
1026 -- Đánh dấu lượt viết thăm <5p
1027 VisitTimeLess = CASE
1028 WHEN MAX( CASE NotCheckVisitTime
1029 WHEN 1 THEN
1030 NULL
1031 ELSE
1032 VisitTime
1033 END
1034 ) < @_VisitTime
1035 AND SUM(IsVisit) = 1 THEN
1036 1
1037 ELSE
1038 CASE
1039 WHEN SUM(IsVisit) > 1
1040 AND
1041 -- khong co luot vieng tham nao hop le va cac luot deu >15p > 30p
1042 SUM(CountNotOverValid) = 0
1043 AND
1044 (
1045 SUM(CountOverValid) > 1
1046 OR SUM(CountOverValidMax) > 0
1047 ) THEN
1048 0
1049 ELSE
1050 CASE
1051 WHEN SUM(IsVisit) > 1
1052 AND SUM(CountLessTime) > 0
1053 AND SUM(CountNotOverValid) < 1 THEN
1054 1
1055 ELSE
1056 0
1057 END
1058 END
1059 END,
1060 FirstVisit = MIN( CASE NotCheckFirstVisit
1061 WHEN 1 THEN
1062 NULL
1063 ELSE
1064 CAST(CONVERT(NVARCHAR(20), start_time, 120) AS DATETIME)
1065 END
1066 ),
1067 LastVisit = MAX( CASE NotCheckLastVisit
1068 WHEN 1 THEN
1069 NULL
1070 ELSE
1071 CAST(CONVERT(NVARCHAR(20), end_time, 120) AS DATETIME)
1072 END
1073 ),
1074 -- IsEnableGPSMode = MIN(IsEnableGPSMode) ,
1075 IsEnableGPSMode = MIN( CASE NotCheckGPSMode
1076 WHEN 1 THEN
1077 1
1078 ELSE
1079 IsEnableGPSMode
1080 END
1081 ),
1082 IsEnableAirPlaneMode = MAX(IsEnableAirPlaneMode)
1083 FROM
1084 (
1085 SELECT DISTINCT
1086 o.distributor_code,
1087 o.customer_code,
1088 o.sale_code,
1089 o.RouteID,
1090 o.created_at,
1091 o.start_time,
1092 o.end_time,
1093 IsVisit = CASE
1094 WHEN reason LIKE '1 -%'
1095 OR reason LIKE '98 -%'
1096 OR reason LIKE '99 -%' THEN
1097 0
1098 ELSE
1099 1
1100 END,
1101 CountOverValid = CASE
1102 WHEN reason LIKE '1 -%'
1103 --OR reason LIKE '2 -%'
1104 --OR reason LIKE '5 -%'
1105 --OR reason LIKE '10 -%'
1106 OR reason LIKE '98 -%'
1107 OR reason LIKE '99 -%' THEN
1108 0
1109 ELSE
1110 CASE
1111 WHEN DATEDIFF(
1112 ss,
1113 start_time,
1114 CASE
1115 WHEN CONVERT(NVARCHAR(20), end_time, 111) = '1900/01/01' THEN
1116 start_time
1117 ELSE
1118 end_time
1119 END
1120 ) / CAST(60 AS DECIMAL(18, 2))
1121 BETWEEN @_VistiTimeValid AND @_VisitTimeMax THEN
1122 1
1123 ELSE
1124 0
1125 END
1126 END,
1127 CountOverValidMax = CASE
1128 WHEN reason LIKE '1 -%'
1129 --OR reason LIKE '2 -%'
1130 --OR reason LIKE '5 -%'
1131 --OR reason LIKE '10 -%'
1132 OR reason LIKE '98 -%'
1133 OR reason LIKE '99 -%' THEN
1134 0
1135 ELSE
1136 CASE
1137 WHEN DATEDIFF(
1138 ss,
1139 start_time,
1140 CASE
1141 WHEN CONVERT(NVARCHAR(20), end_time, 111) = '1900/01/01' THEN
1142 start_time
1143 ELSE
1144 end_time
1145 END
1146 ) / CAST(60 AS DECIMAL(18, 2)) > @_VisitTimeMax THEN
1147 1
1148 ELSE
1149 0
1150 END
1151 END,
1152 CountNotOverValid = CASE
1153 WHEN reason LIKE '1 -%'
1154 --OR reason LIKE '2 -%'
1155 --OR reason LIKE '5 -%'
1156 --OR reason LIKE '10 -%'
1157 OR reason LIKE '98 -%'
1158 OR reason LIKE '99 -%' THEN
1159 0
1160 ELSE
1161 CASE
1162 WHEN DATEDIFF(
1163 ss,
1164 start_time,
1165 CASE
1166 WHEN CONVERT(NVARCHAR(20), end_time, 111) = '1900/01/01' THEN
1167 start_time
1168 ELSE
1169 end_time
1170 END
1171 ) / CAST(60 AS DECIMAL(18, 2))
1172 BETWEEN @_VisitTime AND @_VistiTimeValid THEN
1173 1
1174 ELSE
1175 0
1176 END
1177 END,
1178 CountLessTime = CASE
1179 WHEN reason LIKE '1 -%'
1180 --OR reason LIKE '2 -%'
1181 --OR reason LIKE '5 -%'
1182 --OR reason LIKE '10 -%'
1183 OR reason LIKE '98 -%'
1184 OR reason LIKE '99 -%' --OR RegionID = '5' OR RegionID = '4' OR RegionID = '1'
1185 THEN
1186 0
1187 ELSE
1188 CASE
1189 WHEN DATEDIFF(
1190 ss,
1191 start_time,
1192 CASE
1193 WHEN CONVERT(NVARCHAR(20), end_time, 111) = '1900/01/01' THEN
1194 start_time
1195 ELSE
1196 end_time
1197 END
1198 ) / CAST(60 AS DECIMAL(18, 2)) < @_VisitTime THEN
1199 1
1200 ELSE
1201 0
1202 END
1203 END,
1204 VisitTime = DATEDIFF(
1205 ss,
1206 start_time,
1207 CASE
1208 WHEN CONVERT(NVARCHAR(20), end_time, 111) = '1900/01/01' THEN
1209 start_time
1210 ELSE
1211 end_time
1212 END
1213 ) / CAST(60 AS DECIMAL(18, 2)),
1214 NotCheckVisitTime = CASE
1215 WHEN reason LIKE '1 -%'
1216 --OR reason LIKE '2 -%'
1217 --OR reason LIKE '5 -%'
1218 --OR reason LIKE '10 -%'
1219 OR reason LIKE '98 -%'
1220 OR reason LIKE '99 -%' THEN
1221 1
1222 ELSE
1223 0
1224 END,
1225 --, NotCheckVisitTime = 1
1226 NotCheckFirstVisit = CASE
1227 WHEN reason LIKE '1 -%'
1228 OR reason LIKE '98 -%'
1229 OR reason LIKE '99 -%' THEN
1230 1
1231 ELSE
1232 0
1233 END,
1234 NotCheckLastVisit = CASE
1235 WHEN reason LIKE '1 -%'
1236 OR reason LIKE '98 -%'
1237 OR reason LIKE '99 -%' THEN
1238 1
1239 ELSE
1240 0
1241 END,
1242 reason,
1243 IsEnableGPSMode,
1244 IsEnableAirPlaneMode,
1245 NotCheckGPSMode = CASE
1246 WHEN reason LIKE '1 -%'
1247 OR reason LIKE '98 -%'
1248 OR reason LIKE '99 -%' THEN
1249 1
1250 ELSE
1251 0
1252 END
1253 FROM ---- [192.168.1.14]
1254 #tmpeRouteOrder o
1255 --WHERE
1256 -- o.created_at BETWEEN @_FromDate AND @_ToDate
1257 -- AND o.RouteID IS NOT NULL
1258 -- AND (o.RouteID = @_RouteID OR @_RouteID IS NULL)
1259 -- AND EXISTS (SELECT * FROM #tmpPermission WHERE DistributorID = o.distributor_code)
1260 ) tmp
1261 GROUP BY distributor_code,
1262 customer_code,
1263 sale_code,
1264 created_at
1265 ) t
1266 GROUP BY distributor_code,
1267 sale_code,
1268 created_at;
1269
1270 CREATE NONCLUSTERED INDEX [IX_TrackFromERoute]
1271 ON #tmpTrackFromERoute
1272 (
1273 distributor_code,
1274 sale_code,
1275 created_at
1276 );
1277 PRINT (CONVERT(NVARCHAR(50), GETDATE(), 109) + ' #tmpTrackFromERoute');
1278
1279
1280 --SELECT
1281 -- DistributorID
1282 -- , RouteID
1283 -- , SalesmanID
1284 -- , ImageDate = CONVERT(VARCHAR(50), ImageDate, 111)
1285 -- , Qty = COUNT(DISTINCT iifs.OutletID)
1286 --INTO #tmpCheckImgDaily
1287 --FROM
1288 -- ---- [10.86.67.16]
1289 -- [10.86.67.16].[10.86.67.16].THPVisibility2.dbo.DMSImageInfoFromStaging iifs
1290 --WHERE
1291 -- MONTH(DateReceived) = @Month
1292 -- AND YEAR(DateReceived) = @Year
1293 -- AND CONVERT(NVARCHAR(20), DateReceived, 111) = CONVERT(NVARCHAR(20), ImageDate, 111)
1294 -- AND CONVERT(TIME(0), CONVERT(VARCHAR(50), ISNULL(DateReceived, ImageDate))) <= @_ImageReceive
1295 -- --AND EXISTS (SELECT * FROM #Permission prm WHERE iifs.DistributorID = prm.DistributorID)
1296 --GROUP BY
1297 -- DistributorID
1298 -- , RouteID
1299 -- , SalesmanID
1300 -- , CONVERT(VARCHAR(50), ImageDate, 111)
1301
1302 CREATE TABLE #tmpCheckImgDaily
1303 (
1304 DistributorID INT,
1305 RouteID NVARCHAR(50),
1306 SalesmanID NVARCHAR(50),
1307 ImageDate DATETIME,
1308 Qty INT
1309 );
1310
1311 DECLARE @querytmpCheckImgDaily NVARCHAR(MAX);
1312 SET @querytmpCheckImgDaily
1313 = '
1314 SELECT DistributorID ,
1315 RouteID,
1316 SalesmanID ,
1317 ImageDate ,
1318 Qty = COUNT(DISTINCT OutletID)
1319
1320 FROM (
1321 SELECT iifs2.DistributorID ,
1322 iifs2.RouteID,
1323 iifs2.SalesmanID ,
1324 ImageDate = doi.ImageDate ,
1325 iifs2.OutletID ,
1326 Num = CASE WHEN DATEDIFF(DAY, CAST(iifs2.Imagedate AS DATE),
1327 CAST(iifs2.DateReceived AS DATE)) <> 0
1328 THEN 0
1329 ELSE CASE WHEN CONVERT(TIME(0), CONVERT(VARCHAR(50), iifs2.DateReceived, 108)) <= CAST('''''
1330 + @_ImageReceive
1331 + ''''' AS TIME)
1332 THEN 1
1333 ELSE 0
1334 END
1335 END
1336
1337 FROM THPVisibility2.dbo.DMSImageInfoFromStaging iifs2
1338 INNER join THPVisibility2.dbo.DisplayOutletImage doi on
1339 Doi.DistributorID = iifs2.DistributorID
1340 AND Doi.OutletID = iifs2.OutletID
1341 AND Doi.SalesmanID = iifs2.SalesmanID
1342 AND Doi.VisitID = iifs2.VisitID
1343 AND Doi.ImageFile = iifs2.ImagePath
1344 AND DOI.DisplayID=1
1345 WHERE doi.ImageDate BETWEEN''''' + CONVERT(VARCHAR(20), @FromDate, 111) + ''''' AND '''''
1346 + CONVERT(VARCHAR(20), @ToDate, 111)
1347 + '''''
1348 AND doi.DisplayID=1
1349 AND iifs2.RouteID NOT LIKE ''''%_T''''
1350
1351 ) result
1352 WHERE Num = 1
1353 GROUP BY DistributorID ,
1354 RouteID,
1355 SalesmanID ,
1356 ImageDate;';
1357 DECLARE @openquerytmpCheckImgDaily NVARCHAR(MAX);
1358 SET @openquerytmpCheckImgDaily
1359 = '
1360 INSERT INTO #tmpCheckImgDaily
1361 SELECT
1362 *
1363 FROM OPENQUERY([10.86.67.16], ''' + @querytmpCheckImgDaily + ''')';
1364
1365
1366 EXEC sys.sp_executesql @openquerytmpCheckImgDaily;
1367
1368 CREATE NONCLUSTERED INDEX [IX_CheckImgDaily]
1369 ON #tmpCheckImgDaily
1370 (
1371 DistributorID,
1372 SalesmanID,
1373 ImageDate
1374 );
1375
1376 PRINT (CONVERT(NVARCHAR(50), GETDATE(), 109) + ' #tmpCheckImgDaily');
1377
1378 --SELECT * FROM #tmpCheckImgDaily
1379 --SELECT * FROM #tmpMCPVisitDate
1380 SELECT *
1381 INTO #tmpResult
1382 FROM
1383 (
1384 SELECT
1385 -- MCP = ISNULL(mcp.MCP, 0),
1386 --TotalVisit,
1387 --TotalVisitDiv = ISNULL(( CAST(TotalVisit AS FLOAT) / mcp.MCP ), 0),
1388 CompanyID = mcp.BranchID,
1389 mcp.RouteID,
1390 mcp.SalespersonCD,
1391 VisitDate = CONVERT(NVARCHAR(20), mcp.VisitDate, 103),
1392 TranDate = CONVERT(NVARCHAR(20), fcs.TranDate, 103),
1393 IsVisit = CONVERT(
1394 INT,
1395 CASE
1396 WHEN smfs.WorkingDate IS NULL THEN
1397 1 ---- có mcp nhưng không đi làm
1398 WHEN ISNULL(PC, 0) < @_PCAtLeast
1399 OR trc.IsEnableGPSMode != @_GPS
1400 OR trc.IsEnableAirPlaneMode != @_AirPlanMode
1401 -- OR CONVERT(TIME(0), trc.FirstVisit) > @_FirstVisit
1402 OR CONVERT(TIME(0), trc.LastVisit) < @_LastVisit
1403 --OR FirstSyncCheck = 0
1404 OR (CASE
1405 WHEN ISNULL(mcp.MCP, 0) > 0 THEN
1406 ISNULL((CAST(TotalVisit AS FLOAT) / mcp.MCP), 0)
1407 ELSE
1408 0
1409 END
1410 ) < @_VisitAtLeast
1411 OR (CASE
1412 WHEN ISNULL(mcp.MCP, 0) > 0 THEN
1413 ISNULL((CAST(img.Qty AS FLOAT) / mcp.MCP), 0)
1414 ELSE
1415 0
1416 END
1417 ) < @_OutletHasImage
1418 OR daily.PercentImgPassMCP < @_RateImageValid THEN
1419 2 ---- không chấm công
1420 WHEN TotalVisitTimeViolate > 5
1421 OR trc.ActualFail = 1 --AND distributor.RegionID != 2 AND distributor.RegionID != 3
1422 THEN
1423 2 ---- không chấm công
1424 --WHEN TotalViolate >= 3 --AND distributor.RegionID != 2 AND distributor.RegionID != 3
1425 -- THEN 3 ---- chấm 1/2 công
1426 --nghia.tran: 25/12/2017
1427 WHEN smfs.FirstSyncTime <= CONVERT(TIME(0), @_FirstSyncTime)
1428 AND CONVERT(TIME(0), trc.FirstVisit) <= @_FirstVisit --CONVERT(TIME(0),'08:45:00')
1429 THEN
1430 6 --value = 1
1431 WHEN (
1432 (
1433 smfs.FirstSyncTime > CONVERT(TIME(0), @_FirstSyncTime)
1434 AND smfs.FirstSyncTime <= CONVERT(
1435 TIME(0),
1436 DATEADD(
1437 MINUTE,
1438 15,
1439 @_FirstSyncTime
1440 )
1441 )
1442 )
1443 AND CONVERT(TIME(0), trc.FirstVisit) <= @_FirstVisit
1444 )
1445 OR
1446 (
1447 smfs.FirstSyncTime <= CONVERT(TIME(0), @_FirstSyncTime)
1448 AND CONVERT(TIME(0), trc.FirstVisit) > @_FirstVisit
1449 AND CONVERT(TIME(0), trc.FirstVisit) <= DATEADD(
1450 mi,
1451 15,
1452 CONVERT(
1453 TIME(0),
1454 @_FirstVisit
1455 )
1456 )
1457 )
1458 OR
1459 (
1460 smfs.FirstSyncTime > CONVERT(TIME(0), @_FirstSyncTime)
1461 AND smfs.FirstSyncTime <= DATEADD(
1462 mi,
1463 15,
1464 CONVERT(TIME(0), @_FirstSyncTime)
1465 )
1466 AND CONVERT(TIME(0), trc.FirstVisit) > @_FirstVisit
1467 AND CONVERT(TIME(0), trc.FirstVisit) <= DATEADD(
1468 mi,
1469 15,
1470 CONVERT(
1471 TIME(0),
1472 @_FirstVisit
1473 )
1474 )
1475 ) --OR ( smfs.FirstSyncTime > DATEADD(mi,15, CONVERT(TIME(0),@_FirstSyncTime)) AND smfs.FirstSyncTime <= DATEADD(mi,30, CONVERT(TIME(0),@_FirstSyncTime))
1476 -- AND CONVERT(TIME(0), trc.FirstVisit) <= CONVERT(TIME(0),@_FirstVisit))
1477 THEN
1478 7 --value = 0.75
1479 WHEN (
1480 (
1481 smfs.FirstSyncTime > CONVERT(
1482 TIME(0),
1483 DATEADD(MINUTE, 15, @_FirstSyncTime)
1484 )
1485 AND smfs.FirstSyncTime <= CONVERT(
1486 TIME(0),
1487 DATEADD(
1488 MINUTE,
1489 30,
1490 @_FirstSyncTime
1491 )
1492 )
1493 )
1494 AND CONVERT(TIME(0), trc.FirstVisit) <= @_FirstVisit
1495 ) --CONVERT(TIME(0),'08:45:00')
1496 OR
1497 (
1498 smfs.FirstSyncTime <= CONVERT(TIME(0), @_FirstSyncTime)
1499 AND CONVERT(TIME(0), trc.FirstVisit) > DATEADD(
1500 mi,
1501 15,
1502 CONVERT(
1503 TIME(0),
1504 @_FirstVisit
1505 )
1506 )
1507 AND CONVERT(TIME(0), trc.FirstVisit) <= DATEADD(
1508 mi,
1509 30,
1510 CONVERT(
1511 TIME(0),
1512 @_FirstVisit
1513 )
1514 )
1515 )
1516 OR
1517 (
1518 smfs.FirstSyncTime > CONVERT(TIME(0), @_FirstSyncTime)
1519 AND smfs.FirstSyncTime <= DATEADD(
1520 mi,
1521 15,
1522 CONVERT(TIME(0), @_FirstSyncTime)
1523 )
1524 AND CONVERT(TIME(0), trc.FirstVisit) > DATEADD(
1525 mi,
1526 15,
1527 CONVERT(
1528 TIME(0),
1529 @_FirstVisit
1530 )
1531 )
1532 AND CONVERT(TIME(0), trc.FirstVisit) <= DATEADD(
1533 mi,
1534 30,
1535 CONVERT(
1536 TIME(0),
1537 @_FirstVisit
1538 )
1539 )
1540 )
1541 OR
1542 (
1543 smfs.FirstSyncTime > DATEADD(mi, 15, CONVERT(TIME(0), @_FirstSyncTime))
1544 AND smfs.FirstSyncTime <= DATEADD(
1545 mi,
1546 30,
1547 CONVERT(TIME(0), @_FirstSyncTime)
1548 )
1549 AND CONVERT(TIME(0), trc.FirstVisit) > @_FirstVisit
1550 AND (CONVERT(TIME(0), trc.FirstVisit) <= DATEADD(
1551 mi,
1552 15,
1553 CONVERT(
1554 TIME(0),
1555 @_FirstVisit
1556 )
1557 )
1558 )
1559 )
1560 OR
1561 (
1562 smfs.FirstSyncTime > CONVERT(
1563 TIME(0),
1564 DATEADD(MINUTE, 15, @_FirstSyncTime)
1565 )
1566 AND smfs.FirstSyncTime <= CONVERT(
1567 TIME(0),
1568 DATEADD(MINUTE, 30, @_FirstSyncTime)
1569 )
1570 AND CONVERT(TIME(0), trc.FirstVisit) > DATEADD(
1571 mi,
1572 15,
1573 CONVERT(
1574 TIME(0),
1575 @_FirstVisit
1576 )
1577 )
1578 AND CONVERT(TIME(0), trc.FirstVisit) <= DATEADD(
1579 mi,
1580 30,
1581 CONVERT(
1582 TIME(0),
1583 @_FirstVisit
1584 )
1585 )
1586 ) THEN
1587 8 --value = 0.5
1588 WHEN
1589 (
1590 smfs.FirstSyncTime > CONVERT(TIME(0), DATEADD(MINUTE, 30, @_FirstSyncTime))
1591 OR CONVERT(TIME(0), trc.FirstVisit) > DATEADD(
1592 mi,
1593 30,
1594 CONVERT(TIME(0), @_FirstVisit)
1595 )
1596 ) --CONVERT(TIME(0),'08:45:00'))
1597 THEN
1598 2 --> không chấm công
1599
1600 --.nghia.tran: 25/12/2017
1601 ELSE
1602 4 ---- chấm full công
1603 END
1604 ),
1605 trc.FirstVisit
1606 FROM #tmpMCPVisitDate AS mcp
1607 LEFT JOIN #tmpFCSTranDate AS fcs
1608 ON fcs.CompanyID = mcp.BranchID
1609 AND fcs.RouteID = mcp.RouteID
1610 AND fcs.SalePersonCD = mcp.SalespersonCD
1611 AND fcs.TranDate = mcp.VisitDate
1612 LEFT JOIN #tmpTrackFromERoute AS trc
1613 ON trc.distributor_code = mcp.BranchID
1614 -- AND trc.s = mcp.RouteID
1615 AND trc.sale_code = mcp.SalespersonCD
1616 AND trc.created_at = mcp.VisitDate
1617 LEFT JOIN #tmpCheckImgDaily AS img
1618 ON img.DistributorID = mcp.BranchID
1619 AND img.RouteID = mcp.RouteID
1620 AND img.SalesmanID = mcp.SalespersonCD
1621 AND img.ImageDate = mcp.VisitDate
1622 LEFT JOIN #tmpSMFirstSync AS smfs
1623 ON smfs.DistributorCode = mcp.BranchID --mcp.BranchCD--nghia.tran edit 2018.04.20
1624 AND smfs.SalesmanCode = mcp.SalespersonCD
1625 AND smfs.WorkingDate = mcp.VisitDate
1626 LEFT JOIN #tmpDisplayDaily daily
1627 ON daily.DistributorID = mcp.BranchID
1628 AND daily.RouteID = mcp.RouteID
1629 AND daily.SalesmanID = mcp.SalespersonCD
1630 AND daily.VisitDate = mcp.VisitDate
1631 LEFT JOIN vs_GetDistributorLastest distributor
1632 ON distributor.BranchID = trc.distributor_code
1633 ) rs;
1634
1635
1636 --select * from #tmpMCPVisitDate
1637 --where SalespersonCD = 'SM00019057'
1638 -- --and RouteID = 'RR001985'
1639 ----order by VisitDate
1640 --return;
1641
1642 --- CR #15513 cập nhật nếu SM đó nghỉ phép
1643 --
1644 SELECT dis.DistributorID,
1645 dis.DistributorCD,
1646 dis.DistributorName,
1647 SalespersonCD = sale.EmployeeCD,
1648 sale.Descr,
1649 header.RefNbr,
1650 header.Status,
1651 LeaveDate = CONVERT(NVARCHAR(50), detail.LeaveDate, 103),
1652 detail.Reason,
1653 header.CompanyID
1654 INTO #tmpLeaveRequest
1655 FROM THPDMS2.dbo.DMSRegisterLeaveSalesperson header
1656 INNER JOIN THPDMS2.dbo.DMSRegisterLeaveSalespersonDetail detail
1657 ON detail.CompanyID = header.CompanyID
1658 AND detail.RefNbr = header.RefNbr
1659 AND detail.status = 'D' -- và các lượt đăng ký nghỉ phép được Duyệt trong ref tháng (#42528)
1660 INNER JOIN THPDMS2.dbo.DMSViewDistributor dis
1661 ON header.CompanyID = dis.CompanyID
1662 INNER JOIN THPDMS2.dbo.DMSSalesForce sale
1663 ON sale.CompanyID = detail.CompanyID
1664 AND sale.EmployeeCD = detail.SalesmanID
1665 WHERE header.Status = 'A'; -- chi lay nhung ngay phep nao duoc approve, và line
1666
1667 -- cap nhat lai is vist =-1 thi la ngay nghi phep
1668 UPDATE #tmpResult
1669 SET IsVisit = 100
1670 FROM #tmpResult r
1671 INNER JOIN #tmpLeaveRequest lr
1672 ON r.CompanyID = lr.DistributorID
1673 AND r.SalespersonCD = lr.SalespersonCD
1674 AND r.VisitDate = lr.LeaveDate;
1675
1676
1677 CREATE TABLE #tmpMonth
1678 (
1679 DayInMonth INT,
1680 SalesPersonCD NVARCHAR(20)
1681 );
1682
1683 DECLARE @ii INT = 1;
1684 WHILE @ii <= 31
1685 BEGIN
1686 INSERT INTO #tmpMonth
1687 VALUES
1688 (@ii, NULL);
1689 SET @ii = @ii + 1;
1690 END;
1691
1692
1693 SELECT *
1694 INTO #tmpTrackingSM
1695 FROM
1696 (
1697 SELECT #tmpMonth.DayInMonth AS DAYiN,
1698 tmp.CompanyID,
1699 RouteID,
1700 tmp.SalespersonCD,
1701 IsVisit = ISNULL(IsVisit, 0)
1702 FROM #tmpMonth
1703 LEFT JOIN
1704 (
1705 SELECT fcs.CompanyID,
1706 fcs.RouteID,
1707 fcs.SalespersonCD,
1708 fcs.IsVisit,
1709 fcs.VisitDate
1710 FROM #tmpResult fcs
1711 ) tmp
1712 ON #tmpMonth.DayInMonth = DAY(CONVERT(DATE, tmp.VisitDate, 103))
1713 ) src
1714 PIVOT
1715 (
1716 MAX(IsVisit)
1717 FOR DAYiN IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15], [16], [17],
1718 [18], [19], [20], [21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31]
1719 )
1720 ) AS pvt
1721 WHERE CompanyID IS NOT NULL;
1722
1723
1724
1725
1726
1727 -- DECLARE @pivotCol NVARCHAR(MAX);
1728 -- DECLARE @pivotGroupCol NVARCHAR(MAX);
1729 -- DECLARE @tmp NVARCHAR(MAX);
1730 -- SET @pivotCol = ( SELECT VisitDate + '],['
1731 -- FROM ( SELECT DISTINCT
1732 -- VisitDate
1733 -- FROM #tmpResult
1734 -- ) rs
1735 -- ORDER BY VisitDate ASC
1736 -- FOR
1737 -- XML PATH('')
1738 -- );
1739
1740 -- SET @pivotCol = '[' + SUBSTRING(@pivotCol, 0, LEN(@pivotCol) - 1);
1741
1742 -- SET @tmp = @pivotCol;
1743 -- SET @pivotGroupCol = '';
1744
1745 -- CREATE TABLE #tmpTrackingSM
1746 -- (
1747 -- CompanyID INT ,
1748 -- RouteID NVARCHAR(50) ,
1749 -- SalespersonCD NVARCHAR(50),
1750 -- );
1751
1752 -- DECLARE @col NVARCHAR(100);
1753
1754 -- DECLARE @index INT;
1755 -- WHILE @tmp != ''
1756 -- BEGIN
1757 -- SET @index = CHARINDEX(',', @tmp);
1758 -- IF @index > 0
1759 -- BEGIN
1760 -- SET @col = SUBSTRING(@tmp, 0, @index);
1761 -- SET @pivotGroupCol = @pivotGroupCol + 'MAX(ISNULL('
1762 -- + @col + ', 0)) AS ' + @col + ',';
1763 -- SET @tmp = REPLACE(@tmp, @col + ',', '');
1764 -- END;
1765 -- ELSE
1766 -- BEGIN
1767 -- SET @col = @tmp;
1768 -- SET @pivotGroupCol = @pivotGroupCol + 'MAX(ISNULL('
1769 -- + @tmp + ', 0)) AS ' + @tmp;
1770 -- SET @tmp = '';
1771 -- END;
1772 -- SET @sql = 'ALTER TABLE #tmpTrackingSM ADD ' + @col + ' INT';
1773 -- EXEC sys.sp_executesql @sql;
1774 -- END;
1775
1776
1777 -- SET @sql = '
1778 --INSERT INTO #tmpTrackingSM
1779 --SELECT
1780 -- result.*
1781 --FROM
1782 --(
1783 -- SELECT
1784 -- CompanyID,
1785 -- RouteID,
1786 -- SalespersonCD,
1787 -- ' + @pivotGroupCol + '
1788 -- FROM
1789 -- (
1790 -- SELECT
1791 -- CompanyID,
1792 -- RouteID,
1793 -- SalespersonCD,
1794 -- ' + @pivotCol + '
1795 -- FROM
1796 -- (
1797 -- SELECT * FROM #tmpResult
1798 -- )rs
1799 -- PIVOT
1800 -- (
1801 -- MAX(IsVisit)
1802 -- FOR [VisitDate] IN (' + @pivotCol + ')
1803 -- )rsPivot
1804 -- )rs
1805 -- GROUP BY
1806 -- CompanyID,
1807 -- RouteID,
1808 -- SalespersonCD
1809 --)result';
1810
1811
1812 -- EXEC sys.sp_executesql @sql;
1813
1814 ---- begin select bigcola saleout
1815
1816 SELECT bigcola.CompanyID,
1817 bigcola.RouteID,
1818 bigcola.SalePersonCD,
1819 BigColaSaleOut = SUM(bigcola.SaleOut),
1820 --BigColaSaleOut2 = CASE WHEN MAX(unit.UnitMultDiv) = 'M'
1821 -- THEN SUM(bigcola.SaleOut)
1822 -- * MAX(unit.UnitRate)
1823 -- ELSE SUM(bigcola.SaleOut)
1824 -- / MAX(unit.UnitRate)
1825 BigColaSaleOut2 = SUM(bigcola.SaleOut) / 2
1826 -- Quang update cột 22 theo #12024
1827 INTO #tmpBigCola
1828 FROM [dbo].DMSBLSalesOutBySKU bigcola
1829 JOIN THPDMS2.dbo.INUnit unit
1830 ON unit.InventoryID = bigcola.InventoryID
1831 WHERE bigcola.PeriodNbr = @PeriodID
1832 --nghia.tran: 2018.05.04 Sua lai cho nay vi ben 1.0 co rieng 1 bang de luu BigCola
1833 AND bigcola.InventoryCD IN ( '130000230' )
1834 --AND bigcola.InventoryCD IN ( '139000003', '139000004', '139000005',
1835 -- '139000011', '139000022', '139000023',
1836 -- '139000024', '139000025' )
1837 AND EXISTS
1838 (
1839 SELECT * FROM #tmpprmd WHERE #tmpprmd.DistributorID = bigcola.CompanyID
1840 )
1841 AND unit.CompanyID = 3
1842 AND unit.FromUnit <> unit.ToUnit --nghia.tran: 2018.06.15 add
1843 GROUP BY bigcola.CompanyID,
1844 bigcola.RouteID,
1845 bigcola.SalePersonCD;
1846
1847 ---- end select bigcola saleout
1848
1849 ---- do phu
1850
1851 CREATE TABLE #tempDoPhu
1852 (
1853 RegionID NVARCHAR(50),
1854 RegionName NVARCHAR(50),
1855 AreaID NVARCHAR(50),
1856 AreaName NVARCHAR(50),
1857 ProvinceID NVARCHAR(50),
1858 ProvinceName NVARCHAR(50),
1859 DistributorID INT,
1860 DistributorCode NVARCHAR(50),
1861 DistributorName NVARCHAR(50),
1862 ASMID NVARCHAR(50),
1863 ASMName NVARCHAR(50),
1864 SalesSupID NVARCHAR(50),
1865 SalesSupName NVARCHAR(50),
1866 RouteID NVARCHAR(50),
1867 RouteName NVARCHAR(50),
1868 SalesmanID NVARCHAR(50),
1869 SalesmanName NVARCHAR(50),
1870 isTHP INT,
1871 THP NVARCHAR(10),
1872 InventoryName NVARCHAR(200),
1873 MMCP INT,
1874 MCountOutletHave INT,
1875 CoverPercent FLOAT
1876 );
1877 --SELECT @FromDate,@ToDate
1878 --Select @_CompanyID
1879 SET @sql
1880 = 'SET FMTONLY OFF
1881 EXEC THPVisibility2.[dbo].[pp_ReportOutletSKU_ERoute]
1882 ''''' + CONVERT(NVARCHAR(50), @FromDate, 111) + ''''',
1883 ''''' + CONVERT(NVARCHAR(50), @ToDate, 111) + ''''',
1884 ''''' + ISNULL(@RegionID, '') + ''''',
1885 ''''' + ISNULL(@AreaID, '') + ''''',
1886 N'''''''',
1887 ' + CAST(ISNULL(@_DistributorID, 0) AS NVARCHAR(10))
1888 + ', -- int
1889 N'''''''', -- nvarchar(20)
1890 N'''''''', -- nvarchar(20)
1891 0, -- int
1892 0, -- int
1893 0, -- int
1894 1, -- int
1895 0, -- int
1896 ''''' + CAST(@LoginID AS NVARCHAR(50)) + ''''',1';
1897
1898 SET @openquery = '
1899 INSERT INTO #tempDoPhu
1900 SELECT
1901 *
1902 FROM OPENQUERY([10.86.67.16],''' + @sql + ''')';
1903 ---- [10.86.67.16]
1904
1905 --EXEC sys.sp_executesql @openquery
1906
1907 SELECT --RegionID,AreaID,
1908 DistributorID,
1909 RouteID,
1910 SalesmanID,
1911 SUM(tmpCount) AS tmpCount
1912 INTO #tmpResultCover
1913 FROM
1914 (
1915 SELECT -- RegionID,AreaID,
1916 DistributorID,
1917 RouteID,
1918 SalesmanID,
1919 COUNT(DISTINCT InventoryName) AS tmpCount
1920 FROM #tempDoPhu
1921 WHERE CoverPercent >= 50
1922 GROUP BY --RegionID,AreaID,
1923 DistributorID,
1924 RouteID,
1925 SalesmanID
1926 ) R
1927 GROUP BY --RegionID,AreaID,
1928 DistributorID,
1929 RouteID,
1930 SalesmanID;
1931 --SELECT * FROM #tmpResultCover
1932
1933 --SELECT * FROM #tmpDisplay
1934 --SELECT * FROM #tmpFCS
1935 SELECT --DISTINCT
1936 CompanyID,
1937 CompanyCD,
1938 SalePersonCD,
1939 SalePersonName
1940 INTO #tempSM
1941 FROM vs_GetSalePersonLastest;
1942
1943
1944 --SELECT
1945 -- routemaster.CompanyID,
1946 -- routemaster.RouteID,
1947 -- routemaster.RouteCD,
1948 -- routemaster.RoutePosition
1949 --INTO #tmpRouteMaster
1950 --FROM thpdms2..DMSHORouteMaster routemaster
1951 --WHERE routemaster.[Active] = 1
1952 -- CREATE NONCLUSTERED INDEX [tmprouteMaster_Index] ON #tmpRouteMaster(CompanyID ASC, RouteID ASC);
1953
1954 --SELECT * FROM #tmpRouteMaster
1955 -- SELECT * FROM #tmpMCP
1956 -- SELECT * FROM #tmpRawData
1957 --RETURN
1958
1959 SELECT rd.CompanyID,
1960 rd.SalesRepsID,
1961 rd.SalespersonCD,
1962 rd.Route,
1963 rd.DistributorID,
1964 SUM(rd.TuanThuPDA) AS slTuanThuPDA,
1965 SUM(rd.Giao1Phan) AS slGiao1Phan
1966 INTO #tmpSOofSM
1967 FROM #tmpRawData rd
1968 GROUP BY rd.SalesRepsID,
1969 rd.SalespersonCD,
1970 rd.Route,
1971 rd.DistributorID,
1972 rd.CompanyID;
1973
1974 --Thu.Nguyen 2019-07-30 : Update Target hình đạt trưng bày - RedmineID: 63774
1975 SELECT r.CompanyID,
1976 RouteCD = LTRIM(RTRIM(r.RouteCD)),
1977 r.SalespersonID,
1978 SalespersonCD = LTRIM(RTRIM(sf.EmployeeCD)),
1979 CountCustomer = MAX(c.CountCustomer)
1980 INTO #tmpTargetImagePass
1981 FROM -- Danh sách nhân viên và Route có hiệu lực trong tháng
1982 (
1983 SELECT rss.CompanyID,
1984 rss.RouteCD,
1985 rss.SalespersonID,
1986 rss.RefNbr,
1987 RowNumber = ROW_NUMBER() OVER (PARTITION BY rss.CompanyID,
1988 rss.SalespersonID,
1989 rss.RouteCD
1990 ORDER BY rss.VisitDate DESC
1991 )
1992 FROM DMSBLVisitPlanHistory rss
1993 WHERE rss.VisitDate
1994 BETWEEN @FromDate AND @ToDate
1995 AND rss.CompanyID = 3
1996 ) r
1997 INNER JOIN -- Đếm số lượng khách hàng của từng ref
1998 (
1999 SELECT rs.CompanyID,
2000 rs.RefNbr,
2001 CountCustomer = COUNT(DISTINCT rs.CustomerID)
2002 FROM THPDMS2..DMSMCPDetail rs
2003 GROUP BY rs.CompanyID,
2004 rs.RefNbr
2005 ) c
2006 ON c.CompanyID = r.CompanyID
2007 AND c.RefNbr = r.RefNbr
2008 INNER JOIN THPDMS2..DMSSalesForce sf
2009 ON sf.CompanyID = r.CompanyID
2010 AND sf.EmployeeID = r.SalespersonID
2011 WHERE r.RowNumber = 1 -- Chỉ lấy route có hiệu lực gần nhất
2012 GROUP BY r.CompanyID,
2013 r.RouteCD,
2014 r.SalespersonID,
2015 sf.EmployeeCD;
2016 --- Thu.Nguyen 2019-07-30 : Update Cửa hàng đạt trưng bày tự nhiên trong tháng, Số cửa hàng đóng cửa trong tháng - RedmineID: 63774
2017 CREATE TABLE #tmpClosedWeek
2018 (
2019 RegionID NVARCHAR(30),
2020 Regionname NVARCHAR(50),
2021 AreaID NVARCHAR(30),
2022 AreaName NVARCHAR(50),
2023 RSMID NVARCHAR(30),
2024 RSMName NVARCHAR(50),
2025 ASMID NVARCHAR(30),
2026 ASMName NVARCHAR(50),
2027 SaleSupID NVARCHAR(30),
2028 SaleSupName NVARCHAR(50),
2029 ProvinceID NVARCHAR(30),
2030 ProvinceName NVARCHAR(50),
2031 DistributorCode NVARCHAR(30),
2032 DistributorName NVARCHAR(60),
2033 RouteID NVARCHAR(30),
2034 OutletID NVARCHAR(30),
2035 OutletName NVARCHAR(60),
2036 DisWeek1 INT,
2037 DisWeek2 INT,
2038 DisWeek3 INT,
2039 DisWeek4 INT,
2040 DisWeek5 INT,
2041 CloseWeek1 INT,
2042 CloseWeek2 INT,
2043 CloseWeek3 INT,
2044 CloseWeek4 INT,
2045 CloseWeek5 INT,
2046 SalesmanID NVARCHAR(30),
2047 SalePersonName NVARCHAR(255),
2048 DisWeek INT,
2049 CloseWeek INT
2050 );
2051
2052
2053 INSERT INTO #tmpClosedWeek
2054 EXEC [10.86.67.16].[THPeRoute2].[dbo].[pp_ReportResultCloseOutletWeek] @ToDate,
2055 '',
2056 '',
2057 0,
2058 '';
2059
2060 DELETE DMSBLTotalFCSSalesReps58603
2061 WHERE PeriodID = @PeriodID
2062 AND
2063 (
2064 @_DistributorID IS NULL
2065 OR DistributorID IN (
2066 SELECT ID FROM #ListDistributor
2067 )
2068 );
2069 SELECT DISTINCT
2070 distributor.RegionID,
2071 distributor.RegionName,
2072 distributor.AreaID,
2073 distributor.AreaName,
2074 CompanyCD = distributor.DistributorCD,
2075 CompanyName = distributor.DistributorName,
2076 distributor.ProvinceName,
2077 saleperson.SalePersonCD,
2078 saleperson.SalePersonName,
2079 -- UsrInitDate ,
2080 MCP.NewSalesman,
2081 MCP.NewSalesmanIn2Months,
2082 MCP.NewSalesmanIn1Months,
2083 MCP.OldSalesman,
2084 SOofSM.slTuanThuPDA,
2085 SOofSM.slGiao1Phan,
2086 MCP.LeaveDate,
2087 --MCP.RouteID ,
2088 rt.RouteName,
2089 MCP.TargetMandays,
2090 MCP.TotalOutlet,
2091 FCS.ActualMandays,
2092 PercentMandays = CASE
2093 WHEN NULLIF(MCP.TargetMandays, 0) IS NULL THEN
2094 NULL
2095 ELSE
2096 CAST(FCS.ActualMandays AS FLOAT) / MCP.TargetMandays * 100
2097 END,
2098 TargetPCLPPC = FCS.TargetPCLPPC,
2099 ActualPCLPPC = FCS.ActualPCLPPC,
2100 PercentPCLPPC = CASE
2101 WHEN NULLIF(FCS.TargetPCLPPC, 0) IS NULL THEN
2102 NULL
2103 WHEN NULLIF(MCP.TargetMandays, 0) IS NULL THEN
2104 NULL
2105 ELSE
2106 FCS.ActualPCLPPC / FCS.TargetPCLPPC * 100
2107 END,
2108 FCS.TotalOrder,
2109 FCS.ManuallyOrder,
2110 ResultManually = CASE
2111 WHEN FCS.ManuallyOrder = 0 THEN
2112 'Y'
2113 WHEN FCS.TotalOrder IS NULL THEN
2114 ''
2115 ELSE
2116 'N'
2117 END,
2118 FCS.TargetRouteSales,
2119 FCS.ActualRouteSales,
2120 PercentRouteSales = CASE
2121 WHEN NULLIF(FCS.TargetRouteSales, 0) IS NULL THEN
2122 NULL
2123 ELSE
2124 (FCS.ActualRouteSales - ISNULL(bigCola.BigColaSaleOut, 0) + ISNULL(bigCola.BigColaSaleOut2, 0))
2125 / FCS.TargetRouteSales * 100
2126 END,
2127 TotalRouteSales = FCS.ActualRouteSales - ISNULL(bigCola.BigColaSaleOut, 0) + ISNULL(bigCola.BigColaSaleOut2, 0),
2128
2129
2130 --test
2131 BigColaSaleOut = ISNULL(bigCola.BigColaSaleOut, 0),
2132 BigColaSaleOut2 = ISNULL(bigCola.BigColaSaleOut2, 0),
2133 --.test
2134 MPercentRImage = CASE
2135 WHEN display.MMCPSalesByVisitPlan = 0 THEN
2136 0
2137 ELSE
2138 ROUND((ISNULL(display.MImageCount / display.MMCPSalesByVisitPlan, 0)) * 100, 2)
2139 END,
2140 display.MSMLastPass_MMCPSales,
2141 MImagePass_MMCPSales = display.MImagePass / (targetimage.CountCustomer - cw.SumClosedWeek), --- Thu.Nguyen 2019-07-30 : Update Cửa hàng đạt trưng bày tự nhiên trong tháng, Số cửa hàng đóng cửa trong tháng - RedmineID: 63774
2142 --MImagePass_MMCPSales = CASE
2143 -- WHEN rMaster.RoutePosition = ''T'' THEN display.MImagePass_MMCPSales
2144 -- ELSE (MImagePass * 100.0)/(35 * 0.95)
2145 -- --ELSE (MImagePass * 100.0)/(display.MMCPSales * 35 * 0.95)
2146 -- END,
2147 MMCP = targetimage.CountCustomer, --Thu.Nguyen 2019-07-30 : Update Target hình đạt trưng bày - RedmineID: 63774
2148 --ROUND( CASE
2149 -- WHEN MImagePass_MMCPSales = 0 THEN
2150 -- 0
2151 -- ELSE
2152 -- (MImagePass * 100) / (MImagePass_MMCPSales * 0.95)
2153 -- END,
2154 -- 0
2155 -- ),
2156 test0 = MImagePass_MMCPSales,
2157 test = MImagePass,
2158 test2 = (MImagePass_MMCPSales),
2159 MImagePass = cw.SumDisweek, --- Thu.Nguyen 2019-07-30 : Update Cửa hàng đạt trưng bày tự nhiên trong tháng, Số cửa hàng đóng cửa trong tháng - RedmineID: 63774
2160 ClosedOutlet = cw.SumClosedWeek, --- Thu.Nguyen 2019-07-30 : Update Cửa hàng đạt trưng bày tự nhiên trong tháng, Số cửa hàng đóng cửa trong tháng - RedmineID: 63774
2161 BonusCondition = CASE
2162 WHEN
2163 --CASE WHEN display.MMCPSalesByVisitPlan = 0
2164 -- THEN 0
2165 -- ELSE
2166 --ROUND(( ISNULL(display.MImageCount
2167 -- / display.MMCPSalesByVisitPlan,
2168 -- 0) ) * 100, 2)
2169 -- END >= 100
2170 -- AND
2171 ROUND(ISNULL(display.MSMLastPass_MMCPSales, 0), 2) >= @_RateImageValid
2172 AND ROUND(ISNULL(display.MImagePass_MMCPSales, 0), 2) >= CASE
2173 WHEN MCP.NewSalesman = 'X'
2174 --OR MCP.NewSalesmanIn2Months = 'X'
2175 --OR MCP.NewSalesmanIn1Months = 'X'
2176 THEN
2177 @_RateDisplayValidNewSM
2178 ELSE
2179 @_RateDisplayValidOldSM
2180 END THEN
2181 N'Đạt'
2182 ELSE
2183 N'Không Đạt'
2184 END,
2185 SalaryCondition = CASE
2186 WHEN CASE
2187 WHEN display.MMCPSalesByVisitPlan = 0 THEN
2188 0
2189 ELSE
2190 ROUND(
2191 (ISNULL(display.MImageCount / display.MMCPSalesByVisitPlan, 0))
2192 * 100,
2193 2
2194 )
2195 END >= @_VisitAtLeast
2196 AND ROUND(ISNULL(display.MSMLastPass_MMCPSales, 0), 2) >= @_RateImageValid
2197 -- AND ROUND(ISNULL(display.MImagePass_MMCPSales, 0), 2) >= 15
2198 THEN
2199 N'Đạt'
2200 ELSE
2201 N'Không Đạt'
2202 END,
2203 ISNULL(rCover.tmpCount, 0) AS CountInvCover,
2204 FCS.TotalSaleOut,
2205 FCS.TotalPC,
2206 FCS.StartDatePC,
2207 FCS.EndDatePC,
2208 FCS.AVGPC,
2209 --bigCola.BigColaSaleOut ,
2210 --bigCola.BigColaSaleOut2 ,
2211 tracking.*,
2212 distributor.DistributorID
2213 INTO #tmpdata
2214 FROM #tmpMCP AS MCP
2215 LEFT JOIN #tmpFCS AS FCS
2216 ON MCP.CompanyID = FCS.CompanyID
2217 AND MCP.RouteID = FCS.RouteID
2218 AND MCP.SalespersonCD = FCS.SalePersonCD
2219 LEFT JOIN #tmpDisplay display
2220 ON MCP.CompanyID = display.DistributorID
2221 AND MCP.RouteID = display.RouteID
2222 AND MCP.SalespersonCD = display.SalesmanID
2223 LEFT JOIN #tmpTrackingSM tracking
2224 ON MCP.CompanyID = tracking.CompanyID
2225 AND MCP.RouteID = tracking.RouteID
2226 AND MCP.SalespersonCD = tracking.SalespersonCD
2227 LEFT JOIN #tmpBigCola bigCola
2228 ON MCP.CompanyID = bigCola.CompanyID
2229 AND MCP.RouteID = bigCola.RouteID
2230 AND MCP.SalespersonCD = bigCola.SalePersonCD
2231 LEFT JOIN [THPDMS2].dbo.DMSViewDistributor distributor
2232 ON distributor.DistributorID = MCP.CompanyID
2233 AND distributor.CompanyID = 3 --namPT chuyen tu vs_GetDistributorLastest do 1 NPP co 2 area --> double so SM
2234 LEFT JOIN #tempSM saleperson
2235 ON saleperson.SalePersonCD = MCP.SalespersonCD
2236 AND saleperson.CompanyID = MCP.CompanyID
2237 LEFT JOIN vs_GetRouteLastest AS rt
2238 ON rt.RouteID = MCP.RouteID
2239 AND rt.CompanyID = MCP.CompanyID
2240 LEFT JOIN #tmpResultCover AS rCover
2241 ON MCP.CompanyID = rCover.DistributorID
2242 AND MCP.RouteID = rCover.RouteID
2243 AND MCP.SalespersonCD = rCover.SalesmanID
2244 LEFT JOIN #tmpSOofSM AS SOofSM
2245 ON SOofSM.CompanyID = @_CompanyID
2246 AND SOofSM.DistributorID = MCP.CompanyID
2247 AND SOofSM.Route = MCP.RouteID
2248 AND SOofSM.SalespersonCD = MCP.SalespersonCD
2249 LEFT JOIN #tmpTargetImagePass targetimage
2250 ON targetimage.CompanyID = MCP.CompanyID
2251 AND targetimage.RouteCD = MCP.RouteID
2252 AND targetimage.SalespersonCD = MCP.SalespersonCD
2253 LEFT JOIN
2254 (
2255 SELECT SalesmanID,
2256 RouteID,
2257 SumDisweek = SUM(DisWeek),
2258 SumClosedWeek = SUM(CloseWeek)
2259 FROM #tmpClosedWeek
2260 GROUP BY SalesmanID,
2261 RouteID
2262 ) cw
2263 ON cw.SalesmanID = MCP.SalespersonCD
2264 AND cw.RouteID = MCP.RouteID
2265 --LEFT JOIN #tmpRouteMaster as rMaster ON rMaster.RouteCD = MCP.RouteID
2266 WHERE ISNULL(FCS.ActualMandays, 0) > 0;
2267
2268 INSERT INTO [DMSBLTotalFCSSalesReps58603]
2269 (
2270 [BaselineDate],
2271 [PeriodID],
2272 [RegionID],
2273 [RegionName],
2274 [AreaID],
2275 [AreaName],
2276 [CompanyCD],
2277 [CompanyName],
2278 [ProvinceName],
2279 [SalePersonCD],
2280 [SalePersonName],
2281 [NewSalesman],
2282 [NewSalesmanIn2Months],
2283 [NewSalesmanIn1Months],
2284 [OldSalesman],
2285 [slTuanThuPDA],
2286 [slGiao1Phan],
2287 [LeaveDate],
2288 [RouteName],
2289 [TargetMandays],
2290 [TotalOutlet],
2291 [ActualMandays],
2292 [PercentMandays],
2293 [TargetPCLPPC],
2294 [ActualPCLPPC],
2295 [PercentPCLPPC],
2296 [TotalOrder],
2297 [ManuallyOrder],
2298 [ResultManually],
2299 [TargetRouteSales],
2300 [ActualRouteSales],
2301 [PercentRouteSales],
2302 [TotalRouteSales],
2303 [BigColaSaleOut],
2304 [BigColaSaleOut2],
2305 [MPercentRImage],
2306 [MSMLastPass_MMCPSales],
2307 [MImagePass_MMCPSales],
2308 [MMCP],
2309 [test0],
2310 [test],
2311 [test2],
2312 [MImagePass],
2313 [BonusCondition],
2314 [SalaryCondition],
2315 [CountInvCover],
2316 [TotalSaleOut],
2317 [TotalPC],
2318 [StartDatePC],
2319 [EndDatePC],
2320 [AVGPC],
2321 [31],
2322 [30],
2323 [29],
2324 [28],
2325 [27],
2326 [26],
2327 [25],
2328 [24],
2329 [23],
2330 [22],
2331 [21],
2332 [20],
2333 [19],
2334 [18],
2335 [17],
2336 [16],
2337 [15],
2338 [14],
2339 [13],
2340 [12],
2341 [11],
2342 [10],
2343 [9],
2344 [8],
2345 [7],
2346 [6],
2347 [5],
2348 [4],
2349 [3],
2350 [2],
2351 [1],
2352 [SalespersonCD],
2353 [RouteID],
2354 [CompanyID],
2355 [DistributorID],
2356 [ClosedOutlet]
2357 )
2358 SELECT GETDATE(),
2359 @PeriodID,
2360 result.*
2361 FROM
2362 (
2363 SELECT [RegionID],
2364 [RegionName],
2365 [AreaID],
2366 [AreaName],
2367 [CompanyCD],
2368 [CompanyName],
2369 [ProvinceName],
2370 [SalePersonCD],
2371 [SalePersonName],
2372 [NewSalesman],
2373 [NewSalesmanIn2Months],
2374 [NewSalesmanIn1Months],
2375 [OldSalesman],
2376 [slTuanThuPDA],
2377 [slGiao1Phan],
2378 [LeaveDate],
2379 [RouteName],
2380 [TargetMandays],
2381 [TotalOutlet],
2382 [ActualMandays],
2383 [PercentMandays],
2384 TargetPCLPPC = CONVERT(DECIMAL(18, 2), [TargetPCLPPC]),
2385 ActualPCLPPC = CONVERT(DECIMAL(18, 2), [ActualPCLPPC]),
2386 [PercentPCLPPC],
2387 [TotalOrder],
2388 [ManuallyOrder],
2389 [ResultManually],
2390 TargetRouteSales = CONVERT(DECIMAL(18, 2), [TargetRouteSales]),
2391 ActualRouteSales = CONVERT(DECIMAL(18, 2), [ActualRouteSales]),
2392 [PercentRouteSales],
2393 TotalRouteSales = CONVERT(DECIMAL(18, 2), [TotalRouteSales]),
2394 BigColaSaleOut = CONVERT(DECIMAL(18, 2), [BigColaSaleOut]),
2395 BigColaSaleOut2 = CONVERT(DECIMAL(18, 2), [BigColaSaleOut2]),
2396 [MPercentRImage],
2397 MSMLastPass_MMCPSales = CONVERT(DECIMAL(18, 2), [MSMLastPass_MMCPSales]),
2398 MImagePass_MMCPSales = CONVERT(DECIMAL(18, 2), [MImagePass_MMCPSales]),
2399 [MMCP],
2400 [test0],
2401 [test],
2402 [test2],
2403 [MImagePass],
2404 [BonusCondition],
2405 [SalaryCondition],
2406 [CountInvCover],
2407 [TotalSaleOut],
2408 [TotalPC],
2409 [StartDatePC],
2410 [EndDatePC],
2411 [AVGPC],
2412 [31],
2413 [30],
2414 [29],
2415 [28],
2416 [27],
2417 [26],
2418 [25],
2419 [24],
2420 [23],
2421 [22],
2422 [21],
2423 [20],
2424 [19],
2425 [18],
2426 [17],
2427 [16],
2428 [15],
2429 [14],
2430 [13],
2431 [12],
2432 [11],
2433 [10],
2434 [9],
2435 [8],
2436 [7],
2437 [6],
2438 [5],
2439 [4],
2440 [3],
2441 [2],
2442 [1],
2443 [SalespersonCD],
2444 [RouteID],
2445 [CompanyID],
2446 [DistributorID],
2447 [ClosedOutlet]
2448 FROM #tmpdata result
2449 ) result
2450 ORDER BY --result.RegionName ,
2451 -- result.AreaName ,
2452 result.CompanyCD ASC;
2453 -- result.RouteName ,
2454 -- result.SalePersonName ASC;
2455 DROP TABLE #Permission;
2456 DROP TABLE #tmpDisplay;
2457 DROP TABLE #tmpTrackingSM;
2458 DROP TABLE #tempDoPhu;
2459 DROP TABLE #tmpTrackFromERoute;
2460 DROP TABLE #tmpCheckImgDaily;
2461 DROP TABLE #tmpRawData;
2462 DROP TABLE #tmpAllOrder;
2463 DROP TABLE #tmpSOofSM;
2464 DROP TABLE #tmpResult;
2465 DROP TABLE #tmpTargetImagePass;
2466 DROP TABLE #tmpClosedWeek;
2467END;