Báo cáo tự động qua Email (HTML) trong SQL Server

Nội dung

1. Yêu cầu
2. Thực hiện
- 2.1 Trên Gmail
- 2.2 Trên SQL Server
  - 2.2.1 Cấu hình SMTP Server
  - 2.2.2 Tạo Job truy vấn dữ liệu và chỉ định thời gian gửi Email

1. Yêu cầu

Vào mỗi ngày phòng Nhân sư nhập vào CSDL thông tin (Mã, Tên, Email, Ngày vào, Chức vụ, Phòng ban…) các nhân viên mới gia nhập Cty/ Tổ chức. Yêu cầu: vào lúc 5h chiều mỗi ngày hệ thống tự động gửi thông tin các nhân viên này qua Email (bằng định dạng HTML như hình bên dưới) cho các quản lý.

Database Mail Test G-Mail — Mẫu báo cáo tự động qua Email

Với yêu cầu trên trường hợp phần mềm tại Cty/ Tổ chức của bạn đang sử dụng chưa có chức năng này bạn cần liên hệ với Cty cung cấp phần mềm để phát triển thêm, tuy nhiên phải tốn thêm một khoản chi phí, mất nhiều thời gian và tiền ẩn lỗi phát sinh trên các chức năng khác. Nội dung bên dưới sẽ hướng dẫn các bạn thực hiện nhanh theo cách miễn phí. Dữ liệu sẽ được chuyển trực tiếp từ CSDL qua Email mà không thông qua lớp ứng dụng (giao diện người dùng – UI của phần mềm).

Để gửi được báo cáo tự động qua Email bạn cần thiết lập một SMTP Server trên SQL Server theo hướng dẫn bên dưới.

Chuẩn bị một tài khoản Gmail để thực hiện. Áp dụng tương tự cho trường hợp sử dụng Office 365 hoặc các hệ thống Email khác.

2. Thực hiện

2.1 Trên Gmail

Đăng nhập vào Gmail và bỏ xác minh đăng nhập 2 bước (bỏ phần xác thực qua SMS khi đăng nhập).
Truy cập vào liên kết: https://myaccount.google.com/lesssecureapps > Bật “Cho phép ứng dụng kém an toàn” như hình bên dưới:

2.2 Trên SQL Server

Trước khi bắt đầu hãy đảm bảo rằng SQL Server Agent đang chạy ở chế đội Automatic.

2.2.1 Cấu hình SMTP Server

Sử dụng công cụ SSMS để vào phần quản trị CSDL, thực hiện lần lượt từng bước theo các hình bên dưới:

**Database Mail > Configure Database Mail**

Đặt tên cho Profile name > Add

New Database Mail Account — Lưu ý khai báo chính xác các thông tin: **Email, mật khẩu, Server name, Port, SSL > Next**
Nếu dùng **Office 365, Server name: smtp.office365.com, Port: 587**

Next

Chọn Public, Default Profile: Yes > Next

Configure System Parameters Database Mail — **Next**

Database Mail Configuration Wizard — **Finish**

Đến đây bạn đã cấu hình xong SMTP trên SQL Server, tiếp theo bạn cần kiểm tra quá trình gửi Email đã thành công chưa:

Database Mail Test E-Mail — Nhập **Email người nhận, Subject, Body > Send Test E-Mail > OK**

2.2.2 Tạo Job truy vấn dữ liệu và chỉ định thời gian gửi Email

Tải về và sử dụng dữ liệu từ CSDL HumanResource.sql. Báo cáo sẽ lấy dữ liệu từ các cột trong bảng EMPLOYEES như hình bên dưới:

HumanResource Email HTML — Bảng **EMPLOYEES** trong CSDL **HumanResource**

Tiếp theo tạo Job để khai báo câu truy vấn và thời gian thực hiện gửi Email:

New Job General — Tại Tab **General** > Đặt tên Job tại ô **Name > OK**

New Job Steps — Tại Tab **Steps > New…**

Khai báo lệnh sau vào ô Command (Lưu ý: thay Email người nhận và cách nhau bởi dấu chấm phẩy):

DECLARE @body nvarchar(max)= '
<html>
<head> <style>
 #g {color: green;}
 #r {color: red;}
 #odd {background-color: lightgrey}
</style> </head>';

DECLARE @i int = 0, @pos int, @s nvarchar(max), @ts nvarchar(max), @xml nvarchar(MAX);
DECLARE @recipients nvarchar(256) = 'sql.edu.vn@gmail.com;sql1.alerts@gmail.com' --Thay đổi email người nhận tại đây
, @subject nvarchar(128) = N'Tiêu đề';

SET @xml = CAST(( SELECT EMPLOYEE_ID AS 'td','',FIRST_NAME AS 'td',''
, EMAIL AS 'td',''
, FORMAT(HIRE_DATE,'dd-MM-yyyy') AS 'td', ''
, JOB_ID AS 'td',''
, DEPARTMENT_ID AS 'td'
FROM EMPLOYEES
WHERE FORMAT(HIRE_DATE,'dd-MM-yyyy') = FORMAT(GETDATE(),'dd-MM-yyyy')
ORDER BY EMPLOYEE_ID
FOR XML PATH('tr'), ELEMENTS ) AS nvarchar(MAX));

SET @xml=REPLACE(@xml, '<td>zg', '<td id="g">');
SET @xml=REPLACE(@xml, '<td>zr', '<td id="r">');

SELECT  @s = '', @pos = CHARINDEX('<tr>', @xml, 4);

WHILE(@pos > 0)
BEGIN
   SET @i += 1;
   SET @ts = SUBSTRING(@xml, 1, @pos-1)
   IF(@i % 2 = 1)
      SET @ts = REPLACE(@ts, '<tr>', '<tr id="odd">');
   SET @s += @ts;
   SET @xml = SUBSTRING(@xml, @pos, LEN(@xml));
   SET @pos =  CHARINDEX('<tr>', @xml, 4);
END

SET @i +=1;
SET @ts = @xml;
IF(@i % 2 = 1)
   SET @ts = REPLACE(@ts, '<tr>', '<tr id="odd">');
SET @s += @ts;
SET @body +=N'<body> <H3>Nội dung</H3> <table border=1>
      <tr><th>Mã</th><th>Tên</th><th>Email</th><th>Ngày vào</th><th>Chức vụ</th><th>Phòng ban</th>'
+ @s+'</table> </body> </html>';

EXEC msdb.dbo.sp_send_dbmail
@recipients = @recipients,
@subject = @subject,
@body = @body,
@body_format ='HTML';

Job Step Properties — Khai báo **Step name** > Chọn **Database HumanResource**, dán câu truy vấn vào ô **Command >** OK

New Job Schedules — Tại Tab **Schedules > New…**

New Job Schedules Database Mail SQL Server — Khai báo các thông tin như trên > OK

Khởi động Job: **SQL_Alerts > Start Job at Step…**

Và như vậy vào lúc 5h chiều mỗi ngày hệ thống sẽ tự động gửi báo cáo cho những Email mà bạn đã khai báo trong câu truy vấn. (Lưu ý: bạn sửa lại vài dòng dữ liệu tại cột HIRE_DATE (Ngày vào) cho giống ngày hiện hành để thấy được kết quả).

Tùy theo yêu cầu thực tế tại Cty/ Tổ chức của bạn mà có thể mở rộng các yêu cầu cao hơn. Vd nâng cao hơn bạn có thể sử dụng kèm với Trigger để bắt các sự kiện cụ thể, chẳng hạn khi vừa thêm mới một nhân viên thì tự động gửi báo cáo qua Email ngay tại thời điểm đó.

Tương tự như trên bạn cũng có thể tạo tự động các Báo cáo doanh thu, Báo cáo sổ quỹ tiền (hoặc các trường hợp âm tiền), Báo cáo tồn kho (hoặc các trường hợp âm kho)… vào cuối mỗi ngày (hoặc các thời điểm được chỉ định) từ các CSDL liên quan.

Báo cáo tự động qua Email (HTML) trong SQL Server

1. Yêu cầu