· 6 years ago · Oct 24, 2019, 09:22 AM
1USE [MRCDMS]
2GO
3/****** Object: StoredProcedure [dbo].[sp_DMS_MDM_GenerateMCP] Script Date: 10/24/2019 4:18:54 PM ******/
4SET ANSI_NULLS ON
5GO
6SET QUOTED_IDENTIFIER ON
7GO
8-- =============================================
9-- Author: Dinh Hoang Lam
10-- Create date: 2015/04/02
11-- Description:
12-- =============================================
13ALTER PROCEDURE [dbo].[sp_DMS_MDM_GenerateMCP]
14 @CompanyID INT
15 , @RefNbr VARCHAR(15)
16 , @FromDate DATETIME
17 , @ToDate DATETIME
18 , @CreatedByID UNIQUEIDENTIFIER
19 , @CreatedByScreenID CHAR(8)
20 , @IsAll BIT
21AS
22BEGIN
23 SET NOCOUNT ON
24
25 DECLARE @_CompanyID INT = @CompanyID
26 DECLARE @_RefNbr VARCHAR(15) = @RefNbr
27 DECLARE @_FromDate DATETIME = @FromDate
28 DECLARE @_ToDate DATETIME = @ToDate
29 DECLARE @_CreatedByID UNIQUEIDENTIFIER = @CreatedByID
30 DECLARE @_CreatedByScreenID CHAR(8) = @CreatedByScreenID
31 DECLARE @_IsAll BIT = @IsAll
32 DECLARE @_RouteCD VARCHAR(15)
33 DECLARE @_SalespersonID INT
34 DECLARE @_SalespersonIDPre INT
35 DECLARE @_EffectiveDate DATETIME
36 DECLARE @_FirstDayOfMonth DATETIME = CONVERT(VARCHAR(4), YEAR(@_FromDate)) + '/' + CONVERT(VARCHAR(2), MONTH(@_FromDate)) + '/01'
37 DECLARE @_LastDayOfMonth DATETIME = DATEADD(dd, -1, DATEADD(mm, 1, @_FirstDayOfMonth))
38 DECLARE @_WeekInMonth INT
39 DECLARE @_WeekStartDate DATETIME
40 DECLARE @_WeekEndDate DATETIME
41 DECLARE @_SMEndDate DATETIME
42 DECLARE @_SMActive BIT
43 DECLARE @_SMPreActiveDate DATETIME
44 DECLARE @_SMPreActive BIT
45 DECLARE @_StartDate DATETIME -- Biến này dùng để xóa visitplan mỗi khi GEN MCP.
46 DECLARE @_SMPreEndDate DATETIME
47 DECLARE @_MonthID VARCHAR(7)
48 DECLARE @_tmpRouteSetting TABLE
49 (
50 CompanyID INT
51 , RefNbr VARCHAR(15)
52 , RouteCD VARCHAR(15)
53 , SalespersonID INT
54 , PreviousSalesMan INT
55 , PreviousSalesManEndDate DATETIME
56 , SalespersonSubID INT
57 , SalesForceID INT
58 , HeaderEffDate DATETIME
59 , HeaderStartDate DATETIME
60 , HeaderEndDate DATETIME
61 , HeaderSMPreEndDate DATETIME
62 , EffectiveDate DATETIME
63 , EndDate DATETIME
64 , EffectiveDatePre DATETIME
65 , EndDatePre DATETIME
66 , CustomerID INT
67 , CustomerLocationID INT
68 , DistributorID INT
69 , SiteID INT
70 , VisitOrder INT
71 , Monday BIT
72 , Tuesday BIT
73 , Wednesday BIT
74 , Thursday BIT
75 , Friday BIT
76 , Saturday BIT
77 , Sunday BIT
78 , StartWeek INT
79 , Frequency INT
80 , LastVisitDate DATETIME
81 )
82
83 DECLARE @_tmpVisitPlan TABLE
84 (
85 CompanyID INT
86 , RefNbr VARCHAR(15)
87 , DistributorID INT
88 , RouteCD VARCHAR(15)
89 , SalespersonID INT
90 , SalespersonSubID INT
91 , SalesForceID INT
92 , CustomerID INT
93 , CustomerLocationID INT
94 , SiteID INT
95 , VisitDate DATETIME
96 , VisitOrder INT
97 , IsValid BIT
98 )
99
100 INSERT INTO @_tmpRouteSetting
101 SELECT
102 rs.CompanyID
103 , rs.RefNbr
104 , rs.RouteCD
105 , rs.SalespersonID
106 , rs.PreviousSalesMan
107 , rs.EndDatePre
108 , rs.SalespersonSubID
109 , rs.SalesForceID
110 , HeaderEffDate = rs.EffectiveDate
111 , HeaderStartDate = rs.StartDate
112 , HeaderEndDate = ISNULL(rs.EndDate, @_ToDate)
113 , HeaderSMPreEndDate = rs.EndDatePre
114 -- ngày bắt đầu chạy cho outlet này
115 , EffectiveDate = (CASE WHEN mcp.EffectiveDate >= rs.StartDate THEN mcp.EffectiveDate
116 ELSE rs.StartDate END)
117 -- ngày kết thúc cho outlet này
118 , EndDate = ISNULL(mcp.EndDate, @_ToDate)
119
120 -- ngày bắt đầu chạy cho outlet này
121 , EffectiveDatePre = (CASE WHEN mcp.EffectiveDate >= rs.StartDatePre THEN mcp.EffectiveDate
122 ELSE rs.StartDatePre END)
123 -- ngày kết thúc cho outlet này
124 , EndDatePre = rs.EndDatePre
125 , mcp.CustomerID
126 , mcp.CustomerLocationID
127 , mcp.DistributorID
128 , mcp.SiteID
129 , mcp.VisitOrder
130 , mcp.Monday
131 , mcp.Tuesday
132 , mcp.Wednesday
133 , mcp.Thursday
134 , mcp.Friday
135 , mcp.Saturday
136 , mcp.Sunday
137 , mcp.StartWeek
138 , mcp.Frequency
139 , mcp.LastVisitDate
140 FROM
141 dbo.DMSRouteSetting rs
142 JOIN dbo.DMSMCPDetail mcp ON mcp.CompanyID = rs.CompanyID
143 AND mcp.RefNbr = rs.RefNbr
144 WHERE
145 rs.CompanyID = @_CompanyID
146 AND rs.Active = 1
147 AND rs.RefNbr = @_RefNbr
148
149 ---- lấy thông tin Effective Date và Route của MCP đang Gen
150 SELECT TOP 1 @_SalespersonID = SalespersonID, @_EffectiveDate = HeaderEffDate, @_RouteCD = RouteCD, @_SalespersonIDPre = PreviousSalesMan FROM @_tmpRouteSetting
151
152 ---- lấy thông tin của endate và active của salesman mới nhất trên route setting
153 SELECT @_SMEndDate = ISNULL(EndDate, @_ToDate), @_SMActive = ISNULL(Active, 0) FROM dbo.DMSSalesForce WHERE CompanyID = @_CompanyID AND EmployeeID = @_SalespersonID
154
155 ---- lấy thông tin của endate và active của salesmanprevious
156 SELECT @_SMPreActiveDate = ISNULL(EndDate, @_ToDate), @_SMPreActive = ISNULL(Active, 0) FROM dbo.DMSSalesForce WHERE CompanyID = @_CompanyID AND EmployeeID = @_SalespersonIDPre
157
158 ---- Update lại last visit date để gen lại cho đúng, sau khi qua câu lệnh này thì last visit date sẽ là null nếu chưa chạy, nếu chạy rồi thì là ngày viếng thăm cuối cùng của tháng trước
159
160 UPDATE rs
161 SET
162 rs.LastVisitDate = CASE WHEN rs.EffectiveDate > tmp.LastVisitDate THEN NULL ELSE tmp.LastVisitDate END
163 FROM
164 @_tmpRouteSetting rs
165 LEFT JOIN (SELECT
166 CompanyID
167 , RefNbr
168 , CustomerID
169 , CustomerLocationID
170 , SiteID
171 , BranchID
172 , LastVisitDate = MAX(VisitDate)
173 FROM dbo.DMSVisitPlan
174 WHERE
175 CompanyID = @_CompanyID
176 AND RefNbr = @_RefNbr
177 AND VisitDate < @_FirstDayOfMonth
178 GROUP BY
179 CompanyID
180 , RefNbr
181 , CustomerID
182 , CustomerLocationID
183 , SiteID
184 , BranchID
185 )tmp ON tmp.CompanyID = rs.CompanyID
186 AND tmp.RefNbr = rs.RefNbr
187 AND tmp.CustomerID = rs.CustomerID
188 AND tmp.CustomerLocationID = rs.CustomerLocationID
189 AND tmp.SiteID = rs.SiteID
190 AND tmp.BranchID = rs.DistributorID
191
192
193 ---- Nếu SM đã bị inactive
194 --IF @_SMActive = 0
195 -- BEGIN
196 -- DELETE FROM dbo.DMSVisitPlan WHERE CompanyID = @_CompanyID AND SalespersonID = @_SalespersonID AND VisitDate >= @_FromDate
197 -- END
198 --ELSE
199 BEGIN
200 DECLARE @_Weekend CHAR(3) = (SELECT Weekend FROM dbo.DMSSalesCalendar WITH (NOLOCK) WHERE CompanyID = @_CompanyID AND [Year] = YEAR(@_FirstDayOfMonth))
201 ----- Lấy danh sách và thứ tự tuần trong tháng
202 SELECT
203 RowNumber = ROW_NUMBER() OVER(ORDER BY Week ASC)
204 , *
205 INTO #tmpWeekInMonth
206 FROM
207 dbo.DMSWeek
208 WHERE
209 CompanyID = @_CompanyID
210 AND [Year] = YEAR(@_FirstDayOfMonth)
211 AND (StartDate BETWEEN @_FirstDayOfMonth AND @_LastDayOfMonth
212 OR EndDate BETWEEN @_FirstDayOfMonth AND @_LastDayOfMonth)
213
214 SELECT
215 *
216 INTO #tmpWeekInYear
217 FROM
218 dbo.DMSWeek
219 WHERE
220 CompanyID = @_CompanyID
221 AND [Year] = YEAR(@_FirstDayOfMonth)
222
223 -- lấy ra ngày end date của sales man trước đó, nếu không có thì là null
224 SELECT @_SMPreEndDate = rs.HeaderSMPreEndDate FROM (SELECT TOP 1 HeaderEffDate, HeaderStartDate, HeaderSMPreEndDate FROM @_tmpRouteSetting) rs
225
226 ---- begin transaction 1
227 BEGIN TRY
228 ---- xóa dữ liệu visit plan trong khoảng thời gian gen lại MCP (từ ngày hiện tại tới cuối tháng)
229 DELETE vp
230 FROM
231 dbo.DMSVisitPlan vp
232 JOIN
233 (
234 SELECT
235 CompanyID
236 , RouteCD
237 FROM
238 @_tmpRouteSetting
239 GROUP BY
240 CompanyID, RouteCD
241 ) tmp ON tmp.CompanyID = vp.CompanyID
242 AND tmp.RouteCD = vp.RouteCD
243 WHERE
244 vp.CompanyID = @_CompanyID
245 AND vp.VisitDate BETWEEN @_FromDate AND @_LastDayOfMonth
246
247 DECLARE @_Date DATETIME = @_FirstDayOfMonth
248 -- @_ToDate lúc này là ngày cuối tháng
249 -- GEN theo EndDate cua Sales Force: Nếu EndDate của sales force > = cuối tháng thì GEN theo cuối tháng
250 -- Ngược lại GEN theo EndDate của sales force.
251 SET @_ToDate = CASE WHEN (@_SMEndDate IS NULL OR @_SMEndDate >= @_ToDate) THEN @_ToDate ELSE @_SMEndDate END
252 -- @_ToDate là ngày cuối cùng cần gen MCP
253
254 -- @_SMPreEndDate lúc này là ngày EndDatePre trên định nghĩa
255 -- Lấy ngày cuối cùng hoạt động của salesman trước đó trên Route dựa vào ngày hết hạn hoặc ngày salesman inactive
256 SET @_SMPreEndDate = CASE WHEN @_SMPreEndDate >= @_SMPreActiveDate THEN @_SMPreActiveDate ELSE @_SMPreEndDate END
257
258 -- @_Date là ngày đầu tiên của tháng
259 -- Chạy từ ngày đầu tiên của tháng
260 WHILE @_Date <= @_ToDate
261 BEGIN
262 ---- delete dữ liệu trong table tạm
263 DELETE FROM @_tmpVisitPlan
264
265 SELECT
266 @_WeekInMonth = w.RowNumber
267 , @_WeekStartDate = w.StartDate
268 , @_WeekEndDate = w.EndDate
269 FROM
270 #tmpWeekInMonth w
271 WHERE
272 w.CompanyID = @_CompanyID
273 AND @_Date BETWEEN w.StartDate AND w.EndDate
274
275
276
277 ---- insert dữ liệu vào table tạm để update Last Visit Date
278 INSERT INTO @_tmpVisitPlan
279 SELECT
280 CompanyID = @_CompanyID
281 , rs.RefNbr
282 , rs.DistributorID
283 , rs.RouteCD
284 -- lấy Salesman đang chạy ở ngày hiện tại (@_Date)
285 , SalespersonID = CASE WHEN rs.PreviousSalesManEndDate IS NULL OR @_Date > rs.PreviousSalesManEndDate THEN rs.SalespersonID ELSE rs.PreviousSalesMan END
286 , rs.SalespersonSubID
287 , rs.SalesForceID
288 , rs.CustomerID
289 , rs.CustomerLocationID
290 , rs.SiteID
291 , VisitDate = @_Date
292 , rs.VisitOrder
293 -- Nếu ngày hiện tại (@_Date thuộc về khoảng thời gian chạy của 2 salesman thì hợp lệ)
294 , IsValid = CASE WHEN @_Date BETWEEN rs.EffectiveDate AND rs.EndDate OR @_Date BETWEEN rs.EffectiveDatePre AND rs.EndDatePre THEN 1 ELSE 0 END
295 FROM
296 @_tmpRouteSetting rs
297 WHERE
298 @_Date <= rs.HeaderEndDate
299 -- không lấy những ngày không thuộc MCP thời gian hoạt động của 2 salesman
300 AND (rs.PreviousSalesManEndDate IS NULL OR NOT (@_Date > @_SMPreEndDate AND @_Date < rs.HeaderStartDate))
301 ---- ngược lại nếu effective date trước tháng gen MCP thì không cần phải kiểm tra
302 ---- nếu tuần đó đã có viếng thăm thì @_Date phải thuộc khoản thời gian từ ngày viếng thăm cuối cùng đến ngày cuối cùng của tuần đó
303 AND (@_Date BETWEEN
304 CASE
305 WHEN EXISTS (SELECT TOP 1 * FROM #tmpWeekInYear WHERE rs.LastVisitDate BETWEEN StartDate AND EndDate AND @_Date BETWEEN StartDate AND EndDate)
306 THEN rs.LastVisitDate
307 ELSE (SELECT TOP 1 StartDate FROM #tmpWeekInYear WHERE
308 CASE WHEN @_Weekend = UPPER(SUBSTRING(DATENAME(dw, rs.LastVisitDate), 1, 3))
309 THEN DATEADD(dd, 1, DATEADD(WEEK, rs.Frequency-1, rs.LastVisitDate))
310 ELSE DATEADD(WEEK, rs.Frequency, rs.LastVisitDate)
311 END
312 BETWEEN StartDate AND EndDate AND @_Date BETWEEN StartDate AND EndDate)
313 END
314 AND
315 CASE
316 WHEN EXISTS (SELECT TOP 1 * FROM #tmpWeekInYear WHERE rs.LastVisitDate BETWEEN StartDate AND EndDate AND @_Date BETWEEN StartDate AND EndDate)
317 THEN (SELECT TOP 1 EndDate FROM #tmpWeekInYear WHERE rs.LastVisitDate BETWEEN StartDate AND EndDate AND @_Date BETWEEN StartDate AND EndDate)
318 ELSE (SELECT TOP 1 EndDate FROM #tmpWeekInYear WHERE
319 CASE WHEN @_Weekend = UPPER(SUBSTRING(DATENAME(dw, rs.LastVisitDate), 1, 3))
320 THEN DATEADD(dd, 1, DATEADD(WEEK, rs.Frequency-1, rs.LastVisitDate))
321 ELSE DATEADD(WEEK, rs.Frequency, rs.LastVisitDate)
322 END
323 BETWEEN StartDate AND EndDate AND @_Date BETWEEN StartDate AND EndDate)
324 END
325 OR (rs.LastVisitDate IS NULL AND (rs.Frequency = 1
326
327 OR (rs.Frequency = 2 AND rs.StartWeek = 1 AND @_WeekInMonth IN (1, 3, 5))
328 OR (rs.Frequency = 2 AND rs.StartWeek = 2 AND @_WeekInMonth IN (2, 4))
329 OR (rs.Frequency = 2 AND rs.StartWeek = 3 AND @_WeekInMonth IN (3, 5))
330 OR (rs.Frequency = 2 AND rs.StartWeek = 4 AND @_WeekInMonth = 4)
331
332 OR (rs.Frequency = 3 AND rs.StartWeek = 1 AND @_WeekInMonth IN (1, 4))
333 OR (rs.Frequency = 3 AND rs.StartWeek = 2 AND @_WeekInMonth IN (2, 5)
334 OR (rs.Frequency = 3 AND rs.StartWeek = 3 AND @_WeekInMonth = 3)
335
336 OR (rs.Frequency = 4 AND rs.StartWeek = 1 AND @_WeekInMonth = 1)
337 OR (rs.Frequency = 4 AND rs.StartWeek = 2 AND @_WeekInMonth = 2)
338 OR (rs.Frequency = 4 AND rs.StartWeek = 3 AND @_WeekInMonth = 3)
339 OR (rs.Frequency = 4 AND rs.StartWeek = 4 AND @_WeekInMonth = 4)))))
340 AND CASE
341 WHEN DATEPART(WEEKDAY, @_Date) = 1 AND rs.Sunday = 1 THEN 1
342 WHEN DATEPART(WEEKDAY, @_Date) = 2 AND rs.Monday = 1 THEN 1
343 WHEN DATEPART(WEEKDAY, @_Date) = 3 AND rs.Tuesday = 1 THEN 1
344 WHEN DATEPART(WEEKDAY, @_Date) = 4 AND rs.Wednesday = 1 THEN 1
345 WHEN DATEPART(WEEKDAY, @_Date) = 5 AND rs.Thursday = 1 THEN 1
346 WHEN DATEPART(WEEKDAY, @_Date) = 6 AND rs.Friday = 1 THEN 1
347 WHEN DATEPART(WEEKDAY, @_Date) = 7 AND rs.Saturday = 1 THEN 1
348 ELSE 0
349 END = 1
350
351 ---- update lại visit date để không lấy lại lần nữa
352 UPDATE rs
353 SET LastVisitDate = CASE
354 WHEN rs.LastVisitDate IS NULL THEN vp.VisitDate
355 WHEN rs.LastVisitDate <= vp.VisitDate THEN vp.VisitDate
356 ELSE rs.LastVisitDate
357 END
358 FROM
359 @_tmpRouteSetting rs
360 JOIN @_tmpVisitPlan vp ON vp.CompanyID = rs.CompanyID
361 AND vp.RefNbr = rs.RefNbr
362 AND vp.DistributorID = rs.DistributorID
363 AND vp.SiteID = rs.SiteID
364 AND vp.CustomerID = rs.CustomerID
365 AND vp.CustomerLocationID = rs.CustomerLocationID
366
367 IF @_Date >= @_FromDate AND NOT EXISTS (SELECT TOP 1 * FROM dbo.DMSHoliday h
368 WHERE CONVERT(INT, h.Year) = YEAR(@_FirstDayOfMonth) AND @_Date BETWEEN h.FromDate AND h.ToDate)
369 BEGIN
370
371 INSERT INTO dbo.DMSVisitPlan
372 (
373 CompanyID
374 , RefNbr
375 , BranchID
376 , RouteCD
377 , SalespersonID
378 , SalespersonSubID
379 , SalesForceID
380 , CustomerID
381 , CustomerLocationID
382 , SiteID
383 , VisitDate
384 , VisitOrder
385 , CreatedByID
386 , CreatedByScreenID
387 , CreatedDateTime
388 , LastModifiedByID
389 , LastModifiedByScreenID
390 , LastModifiedDateTime
391 )
392 SELECT
393 CompanyID
394 , RefNbr
395 , DistributorID
396 , RouteCD
397 , SalespersonID
398 , SalespersonSubID
399 , SalesForceID
400 , CustomerID
401 , CustomerLocationID
402 , SiteID
403 , VisitDate
404 , VisitOrder
405 , @_CreatedByID
406 , @_CreatedByScreenID
407 , GETDATE()
408 , @_CreatedByID
409 , @_CreatedByScreenID
410 , GETDATE()
411 FROM
412 @_tmpVisitPlan
413 WHERE
414 IsValid = 1
415 END
416
417 SET @_Date = DATEADD(d, 1, @_Date)
418 END
419
420 UPDATE dbo.DMSMCPDetail SET Status = 'G' WHERE CompanyID = @_CompanyID AND RefNbr = @_RefNbr
421 UPDATE mcp
422 SET
423 LastVisitDate = rs.LastVisitDate
424 FROM
425 dbo.DMSMCPDetail mcp
426 JOIN @_tmpRouteSetting rs ON rs.CompanyID = mcp.CompanyID
427 AND rs.RefNbr = mcp.RefNbr
428 AND rs.DistributorID = mcp.DistributorID
429 AND rs.SiteID = mcp.SiteID
430 AND rs.CustomerID = mcp.CustomerID
431 AND rs.CustomerLocationID = mcp.CustomerLocationID
432
433 UPDATE dbo.DMSRouteSetting SET Status = 'G' WHERE CompanyID = @_CompanyID AND RefNbr = @_RefNbr
434 UPDATE dbo.DMSRouteSetting
435 SET EndDate = DATEADD(dd, -1, @_EffectiveDate)
436 WHERE
437 CompanyID = @_CompanyID
438 AND RouteCD = @_RouteCD
439 AND RefNbr <> @_RefNbr
440 AND EndDate IS NULL
441 AND EffectiveDate = (SELECT MAX(EffectiveDate)
442 FROM dbo.DMSRouteSetting
443 WHERE
444 CompanyID = @_CompanyID
445 AND RouteCD = @_RouteCD
446 AND RefNbr <> @_RefNbr
447 AND EffectiveDate < @_EffectiveDate
448 AND Status = 'G')
449 END TRY
450 BEGIN CATCH
451 PRINT ERROR_MESSAGE()
452 END CATCH
453 END
454
455 ---- Update thông tin từ RouteSetting về RouteMaster
456 UPDATE r
457 SET r.SalespersonID = rs.SalespersonID,
458 r.SalesForceID = rs.SalesForceID,
459 r.SellingCategoryID = rs.SellingCategoryID,
460 r.SalesAreaID = rs.SalesAreaID
461 FROM dbo.DMSRoute r
462 INNER JOIN dbo.DMSRouteSetting rs
463 ON rs.CompanyID = r.CompanyID
464 AND r.RouteCD = rs.RouteCD
465 WHERE rs.RefNbr = @_RefNbr
466 AND rs.CompanyID = @_CompanyID
467
468 ---Update thông tin Crossbeat
469 SELECT @_MonthID = MonthID FROM dbo.DMSMonth WHERE @_FromDate BETWEEN StartDate AND EndDate;
470 EXEC sp_DMS_MCP_UpdateInfoCrossBeat @_RefNbr, @_MonthID, @_CompanyID, @_CreatedByID, @_CreatedByScreenID;
471END