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