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