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