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