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