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