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

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.

Thực hiện

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:
lesssecureapps
Bật tích xanh

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.

SQL Server Agent
Vào Start > Run > services.msc > Enter để kiểm tra

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:

Configure Database Mail
Database Mail > Configure Database Mail
Database Mail Configuration
Next
Set up Database Mail
Next > Yes
Profile name 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
Sql server mail profile
Next
Manage Profile Security
Chọn Public, Default Profile: Yes > Next
Configure System Parameters Database Mail
Next
Database Mail Configuration Wizard
Finish
Configuring Database Mail
Close

Đế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:

Send Test E-Mail
Database Mail > Send Test E-Mail…
Database Mail Test E-Mail
Nhập Email người nhận, Subject, Body > Send Test E-Mail > OK
Database Mail Test GMail
Kiểm tra Email người nhận. Trường hợp chưa nhận được Email bạn cần rà soát lại các bước cấu hình bên trên để chắc chắn đã thiết lập đúng và đủ, sau đó Send Test E-Mail để thử lại.

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
Jobs > New Job…
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 Steps Database Mail
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
New Job Schedules Database Mail
OK
Start Job at Step
Khởi động Job: SQL_Alerts > Start Job at Step…
Success Database Mail
Close
Database Mail Test G-Mail
Và đây là kết quả

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.

You may also like...

Leave a Reply

Your email address will not be published.