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