Lưu ý:
- Người đọc cần có kiến thức cơ bản về CSDL quan hệ và ngôn ngữ truy vấn SQL.
- Các truy vấn ví dụ sử dụng trong bài được viết bằng cú pháp T-SQL của SQL Server.
- Câu truy vấn tốt hơn được cung cấp tại phần 2.
Vấn đề
Khi phát triển hệ thống nghiệp vụ cho công ty, tôi hay được yêu cầu lập các bản báo cáo dựa trên trục ngày tháng, ví dụ như báo cáo số đơn hàng mỗi ngày trong tháng. Trong một bài viết trước, tôi đã giới thiệu giải pháp để tạo một danh sách ngày tháng dưới dạng bản ghi để giải quyết vấn đề đó.
Tuy nhiên nhiều khi người dùng yêu cầu thêm chức năng như hiển thị xem mỗi ngày trên báo cáo có phải ngày nghỉ hay ko, và nếu ko thì là ngày làm việc thứ mấy trong tháng. Nếu chỉ là nghỉ thứ Bảy Chủ Nhật thì quá đơn giản, nhưng người dùng muốn hiển thị cả các ngày nghỉ lễ Tết, thậm chí là cả các ngày nghỉ do công ty tự quy định, và muốn có thể thiết lập thêm ngày nghỉ một cách dễ dàng. Nói tóm lại tôi cần phải làm một bộ lịch dưới dạng bản ghi dành riêng cho công ty.
Giải pháp sơ cấp
Cũng như vấn đề ở bài viết trước, giải pháp đơn giản nhất là lưu các dữ liệu cần thiết thành bảng trong CSDL. Nếu tôi đã có sẵn một bảng tất cả các ngày tháng, thì tôi sẽ thêm một cột
bit/boolean chứa thông tin ngày đó có phải ngày nghỉ hay ko.| Ngày | Nghỉ |
|---|---|
| 2015/1/1 | 1 |
| 2015/1/2 | 0 |
| ... | ... |
| 2015/4/24 | 0 |
| 2015/4/25 | 1 |
| 2015/4/26 | 1 |
| ... | ... |
Khi người dùng muốn thêm bớt ngày nghỉ tôi sẽ để họ sửa cột đó cho từng ngày nghỉ mà họ muốn thiết lập. Đối với những ngày nghỉ định kỳ như thứ Bảy Chủ Nhật và lễ Tết, tôi có thể chạy một vài câu truy vấn
UPDATE để thiết lập sẵn thông tin cho tất cả các ngày nghỉ có trong bảng.UPDATE [Bảng ngày] SET [Nghỉ] = 1
WHERE DATEPART(weekday, [Ngày]) IN (1, 7) OR MONTH([Ngày]) = 1 AND DAY([Ngày]) = 1)
Giải pháp này đơn giản và cũng khá hiệu quả, cho phép tôi có thể chuyển giao việc bảo trì lịch nghỉ cho người dùng ko biết gì về CNTT. Tuy nhiên nó có một số vấn đề:
- Độ tin cậy thấp: Khỏi phải giải thích.
- Ko tận dụng được bảng ảo: Do giải pháp này gắn liền với việc lưu trữ một bảng thực chứa tất cả các ngày tháng, nên nó ko thể tận dụng được các giải pháp tạo bảng ngày tháng ảo dựa trên bảng số nguyên (ảo nốt) như ở bài viết trước.
- Hiệu suất thấp: Hiệu suất ở đây là hiệu suất sử dụng dữ liệu (lượng thông tin trên lượng dữ liệu). Điều này là do có rất nhiều bản ghi chứa ngày làm việc trộn lẫn giữa các bản ghi chứa ngày nghỉ, trong khi thông tin tôi cần là ngày nào là ngày nghỉ.
Giải pháp trung cấp
Xuất phát từ mục đích ban đầu là tôi chỉ muốn biết một ngày nào đó có phải ngày nghỉ hay ko, tôi cũng có thể tạo một bảng riêng chỉ chứa các ngày nghỉ. Khi người dùng muốn thêm bớt ngày nghỉ tôi sẽ để họ thêm một bản ghi mới vào bảng hoặc xóa một bản ghi cũ đi.
| Ngày nghỉ |
|---|
| 2015/1/1 |
| ... |
| 2015/4/25 |
| 2015/4/26 |
| ... |
Các ngày nghỉ định kỳ có thể được tạo sẵn bởi một vài câu truy vấn
INSERT dựa trên dữ liệu gốc từ bảng ngày tháng hay bảng số nguyên có sẵn (tham khảo bài viết trước).INSERT INTO [Bảng ngày nghỉ] ([Ngày], [Nghỉ]) VALUES (DATEADD(week, [Số] - 5000, '2015-04-25'), 1)
FROM [Bảng số] WHERE [Số] BETWEEN 0 AND 10000
INSERT INTO [Bảng ngày nghỉ] ([Ngày], [Nghỉ]) VALUES (DATEADD(year, [Số] - 100, '2015-01-01'), 1)
FROM [Bảng số] WHERE [Số] BETWEEN 0 AND 200
Câu truy vấn đầu tiên thêm vào bảng 10.001 ngày thứ Bảy từ 5000 tuần trước đến 5000 tuần sau ngày thứ Bảy chuẩn là 2015/4/25. Câu truy vấn tiếp theo thêm 201 ngày 1/1 của các năm từ 100 năm trước đến 100 năm sau năm chuẩn là 2015.
Khi đã có bảng ngày nghỉ, tôi có thể kết hợp nó với bảng ngày tháng để tạo ra bảng lịch nghỉ ảo giống như bảng thực ở giải pháp sơ cấp nói trên.
SELECT [Ngày], IIF([Ngày nghỉ] IS NULL, 0, 1) AS [Nghỉ]
FROM [Bảng ngày] LEFT OUTER JOIN [Bảng ngày nghỉ] ON [Ngày] = [Ngày nghỉ]
Câu truy vấn trên liệt kê lại tất cả các ngày trong bảng ngày tháng sẵn có, và thiết lập đó là ngày nghỉ nếu tồn tại bản ghi chứa ngày đó trong bảng ngày nghỉ. Việc còn lại chỉ là lưu nó thành một khung nhìn để có thể hiển thị ra cho người dùng.
Giải pháp này đã khá tốt, và nếu kết hợp với bảng ngày tháng và số nguyên ảo thì có thể nói là nó đã đủ "số má" để áp dụng vào hệ thống thực tiễn. Tuy nhiên khi đi vào thực tế nó vẫn có một số vấn đề khá rõ ràng như sau:
- Hiệu suất vẫn ko cao: Tôi vẫn sẽ phải lưu hàng nghìn bản ghi chứa các ngày nghỉ định kỳ như thứ Bảy Chủ Nhật hay 1/1. Tôi cũng có thể xử lý thứ Bảy Chủ Nhật riêng khi tạo báo cáo cụ thể và chỉ lưu các ngày nghỉ khác, nhưng như vậy quá phức tạp và làm nảy sinh nhiều vấn đề hơn là hiệu quả mang lại.
- Khó bảo trì: Việc thay đổi các ngày nghỉ định kỳ, ví dụ như chuyển sang nghỉ thứ Tư Chủ Nhật thay vì thứ Bảy Chủ Nhật, đòi hỏi tôi phải thực hiện một loạt các câu truy vấn tạm bợ (ad-hoc) khá phức tạp để thêm bớt bản ghi. Điều này dãn đến rủi ro nhầm lẫn khá cao và hạn chế khả năng chuyển giao công việc bảo trì lịch nghỉ cho người dùng bình thường.
Giải pháp cao cấp (sang chảnh)
Để ý rằng khi người dùng muốn thiết lập ngày 1/1 làm ngày nghỉ, thông tin mà tôi có được và cần lưu trữ ko phải là "nghỉ ngày 2015/1/1, nghỉ ngày 2016/1/1, nghỉ ngày ..." mà là "nghỉ ngày 1/1 của tất cả các năm". Như vậy thay vì lưu thông tin dưới dạng bảng ngày nghỉ, tôi có thể lưu thông tin dưới dạng bảng quy luật nghỉ như sau:
| Ngày chuẩn | Kiểu lặp | Giải thích |
|---|---|---|
| 2015/4/25 | w | Thứ Bảy |
| 2015/4/26 | w | Chủ Nhật |
| 2015/1/1 | y | Năm mới |
| 2015/1/31 | m | Tổng kết cuối tháng (quán anh Béo) |
| 2015/3/21 | n | Cưới con sếp tổng |
Trong đó, kiểu lặp n, w, m, y lần lượt tương ứng với các ngày nghỉ ko lặp lại, lặp mỗi tuần, lặp mỗi tháng và lặp mỗi năm dựa trên một ngày được lấy làm chuẩn. Bằng cách này tôi có thể hạn chế lượng dữ liệu lưu trong CSDL đến rất gần với lượng thông tin thực tế cần lưu. Nhờ đó, tôi ko cần phải tạo sẵn dữ liệu ngày nghỉ cho vài trăm năm, hay thêm bớt hàng nghìn bản ghi khi cần chuyển ngày nghỉ từ thứ Bảy sang thứ Tư.
Vấn đề là tôi sẽ chuyển đổi quy luật nghỉ trên ra thành bảng (khung nhìn) lịch nghỉ như thế nào?
SELECT [Ngày], MAX(CASE [Kiểu lặp]
WHEN 'n' THEN IIF([Ngày] = [Ngày chuẩn], 1, 0)
WHEN 'w' THEN IIF(DATEPART(weekday, [Ngày]) = DATEPART(weekday, [Ngày chuẩn]), 1, 0)
WHEN 'm' THEN IIF(DAY([Ngày]) = DAY([Ngày chuẩn]), 1, 0)
WHEN 'y' THEN IIF(DAY([Ngày]) = DAY([Ngày chuẩn]) AND MONTH([Ngày]) = MONTH([Ngày chuẩn]), 1, 0)
ELSE 0 END) AS [Nghỉ]
FROM [Bảng ngày], [Bảng quy luật] GROUP BY [Ngày]
Các bước để đi đến câu truy vấn trên như sau:
Trước hết nếu đây ko phải là SQL mà là một ngôn ngữ lập trình khác thì tôi sẽ dùng hai vòng
for lồng vào nhau (nested), vòng ngoài chạy tất cả các ngày, và vòng trọng chạy tất cả các quy luật để đánh giá từng quy luật đối với ngày đang xét, rồi sau đó đi đến kết luận ngày đang xét có phải ngày nghỉ hay ko. Tóm lại là tôi làm một phép nhân giữa dãy ngày và dãy quy luật. Quay trở lại SQL, phép nhân đó tương ứng với phép nhân Đề-các thuần túy bảng ngày và bảng quy luật, còn gọi là CROSS JOIN. (tham khảo)SELECT ... FROM [Bảng ngày], [Bảng quy luật]
Tiếp theo, trong vòng
for thứ hai ở trên tôi sẽ phải xét từng trường hợp cụ thể của kiểu lặp để tính ra một giá trịboolean thể hiện quy luật đang xét có áp dụng được với ngày đang xét hay ko, tức là tôi sẽ phải làm một câu lệnh casehay switch. Điều này trong SQL tương ứng với cú pháp CASE WHEN. (tham khảo)CASE [Kiểu lặp] WHEN 'n' THEN ... WHEN 'w' THEN ... WHEN 'm' THEN ... WHEN 'y' THEN ... ELSE ... END
Đến phần đánh giá cụ thể cho từng kiểu lặp, nếu tôi hiểu đơn giản ngày nghỉ lặp theo tuần là các ngày có cùng số thứ tự trong tuần với ngày chuẩn, ngày nghỉ lặp theo tháng là các ngày có cùng số ngày với ngày chuẩn, và ngày nghỉ lặp theo năm là các ngày có cùng số ngày và số tháng với ngày chuẩn, thì tôi có thể dùng các hàm
DATEPART, DAY,MONTH để đánh giá. (tham khảoCASE [Kiểu lặp]
WHEN 'n' THEN IIF([Ngày] = [Ngày chuẩn] , 1, 0)
WHEN 'w' THEN IIF(DATEPART(weekday, [Ngày]) = DATEPART(weekday, [Ngày chuẩn]), 1, 0)
WHEN 'm' THEN IIF(DAY([Ngày]) = DAY([Ngày chuẩn]), 1, 0)
WHEN 'y' THEN IIF(DAY([Ngày]) = DAY([Ngày chuẩn]) AND MONTH([Ngày]) = MONTH([Ngày chuẩn]), 1, 0)
ELSE 0 END
Cuối cùng, tôi cần tổng hợp kết quả đánh giá của tất cả các quy luật đối với mỗi ngày để đưa ra kết luận ngày đó có phải ngày nghỉ hay ko. Với yêu cầu là chỉ cần có một quy luật áp dụng được thì ngày đó là ngày nghỉ, tôi sẽ phải sử dụng một hàm tổng hợp (aggregate) nào đó và
GROUP theo ngày. Nếu là ngôn ngữ lập trình khác thì tôi sẽ dùng orcho kiểu boolean, nhưng đối với SQL thì tôi phải dùng các hàm MIN MAX.SELECT [Ngày], MAX(CASE ... END) AS [Nghỉ] FROM [Bảng ngày], [Bảng quy luật] GROUP BY [Ngày]
Tổng hợp các bước lại tôi có được câu truy vấn hoàn chỉnh như đã trình bày ở trên.
Giải pháp này có rất nhiều ưu điểm so với các giải pháp lưu thông tin dưới dạng từng ngày nghỉ.
- Độ tin cậy cao: Các ngày nghỉ định kỳ, chiếm trên 90% tổng số ngày nghỉ trong năm, đều được tính toán tự động dựa trên một thuật toán thống nhất nên hạn chế được tối đa lỗi con người. Các quy luật được thể hiện rõ ràng tách khỏi các ngày nghỉ đơn lẻ, với số bản ghi có thể kiểm soát được bằng mắt người. Tôi sẽ ko phải phân vân vì sao ngày 2015/12/12 lại là ngày nghỉ, hay tại sao bỗng dưng 2016/1/1 lại trở thành ngày đi làm.
- Dễ bảo trì: Dù là thêm một ngày nghỉ, thêm một quy luật nghỉ, hay thay đổi một quy định nghỉ có sẵn, tôi đều chỉ cần thêm hoặc sửa đúng một bản ghi. Điều này cho phép một người dùng mù CNTT cũng có thể bảo trì lịch nhanh chóng và dễ dàng với một chút hướng dẫn hoặc một công cụ đơn giản.
- Dễ mở rộng: Chỉ cần sửa chữa một chút bảng quy luật và câu truy vấn, tôi có thể thêm chức năng thiết lập ngày nghỉ theo quý, theo 5 năm, hay thậm chí là ngày thứ x của tuần thứ y trong tháng.
Kết
Trên đây là một số giải pháp để tạo ra một bảng lịch các ngày nghỉ có thể ứng dụng cho doanh nghiệp lẫn cá nhân, trong đó chú trọng vào việc hạn chế tối đa sự phụ thuộc vào dữ liệu thực cũng như lỗi con người, đồng thời tạo nền tảng cho việc mở rộng chức năng về sau.
Hai giải pháp sơ cấp và trung cấp do tôi cóp nhặt từ người đi trước kết hợp với kinh nghiệm cá nhân. Đối với giải pháp cao cấp, tôi lấy ý tưởng về bảng quy luật từ gói phần mềm quản lý tài nguyên cho doanh nghiệp (ERP) Dynamics NAV của Microsoft. Tất nhiên giải pháp cuối cùng mà tôi đưa ra chưa phải là hoàn hảo và vẫn còn nhiều điểm có thể sửa chữa, nâng cấp mà tôi sẽ đề cập đến trong phần 2.
Hy vọng bài viết sẽ có ích cho mọi người và chúc mọi người thành công.
Nguồn: Hawkie
No comments:
Post a Comment