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