การส่งรายงาน ด้วยอีเมล์อัตโนมัติ บน ฐานข้อมูล SQL SERVER 2005 ตอนที่ 2: การส่งข้อความอีเมล ในรูปแบบ HTML


บทความจาก นิตยสาร WindowsITPro (ฉบับ March – April 2013)
ได้ขออนุญาตทางบก. เรียบร้อยแล้ว

จากบทความ:
การส่งรายงานด้วยอีเมล์อัตโนมัติ บนฐานข้อมูล SQL SERVER 2005 ตอนที่:1
เมื่อคุณ Configure Database Mail เป็นที่เรียบร้อย
SQL Server ของคุณ พร้อมแล้ว สำหรับการส่งเมล์

ซึ่งในบทความตอนที่:2 นี้ เป็นการกล่าวถึง
การสร้างรายงานอีเมล์ในรูปแบบ HTML และ
การตั้งค่า JOB Schedules เพื่อส่งอีเมล์อัตโนมัติ
เราไปดูขั้นตอน กันเลยครับ

ขั้นตอน การสร้างรายงาน

  • ขั้นตอนที่ 1 สร้าง View
  • ขั้นตอนที่ 2 สร้าง สโตร์โพรซีเยอร์ (Stored procedure)
  • ขั้นตอนที่ 3 สร้าง JOB Schedules ให้ส่งเมล์อัตโนมัติ

ขั้นตอนที่ 1 สร้าง View

1. ไปที่หน้าต่าง Object Explorer

2. คลิกชื่อฐานข้อมูล Northwind

3. คลิกขวาบน โฟลเดอร์ View=>
เลือกเมนู New View…

4. ในหน้าต่าง Add Table =>
เลือกตาราง Categories และ ตาราง Products

5. คลิก ปุ่ม Add ดังรูปที่ 1


รูปที่ 1 การออกแบบ View

6. จากรูปที่ 1 เมื่อกดปุ่ม Add=>
คุณจะเข้าสู่ หน้าต่างออกแบบ View

7. ทำการเลือกคอลัมน์ ที่ต้องการ ในที่นี้คือ =>
[ProductID],
[ProductName],
[CategoryName],
[UnitsInStock]


8. จากนั้น ทำการกำหนดเงื่อนไข
ให้ดึงข้อมูลสินค้า เฉพาะ
จำนวนสินค้าในสต็อกคงเหลือน้อยกว่า
หรือเท่ากับ 9 […WHERE UnitsInStock <= 9…]
ดังรูปที่ 2


รูปที่ 2 การเลือกคอลัมน์ และกำหนดเงื่อนไข View

9. ทำการกดรูปไอคอนดิสก์ เพื่อบันทึก View

10. จะมีหน้าต่าง แสดงขึ้นมาให้ตั้งชื่อ =>
จากนั้นระบุชื่อเป็น vw_ProductMinimumInStock

11. กลับไปที่ โฟลเดอร์ View อีกครั้ง=>
คุณจะเห็นชื่อ View vw_ProductMinimumInStock เพิ่มเข้ามา…

12. ทำการทดสอบ View โดยการ =>
คลิกขวาบน View vw_ProductMinimumInStock =>
เลือกเมนู Select Top 1000 Rows

13. คุณจะเห็นผล ดังรูปที่ 3

รูปที่ 3 การบันทึก และทดสอบ View

ขั้นตอนที่ 2 สร้าง สโตร์โพรซีเยอร์ (Stored procedure)

14. เมื่อคุณสร้าง View และกรองข้อมูล เรียบร้อยแล้ว
ต่อไป เป็นการสร้างสโตร์โพรซีเยอร์

15. กลับไปที่หน้า Object Explorer อีกครั้ง

16. เปิดเข้าไปบนโฟลเดอร์ Programmability =>
คลิกขยาย โฟลเดอร์ Stored Procedures

17. คลิกขวาบนโฟลเดอร์ Stored Procedures =>
เลือกเมนู New Stored Procedure…

18. คุณจะเห็นเทมเพลตในการสร้างสร้างสโตร์โพรซีเยอร์
ดังรูป 4


รูปที่4 การสร้างสโตร์โพรซีเยอร์

19. จากนั้นทำการเขียนสคริปต์สโตร์โพรซีเยอร์
สร้างรายงานในรูปแบบ HTML
โดยดึงข้อมูลจาก View ที่สร้างไว้เมื่อครู่
ดังนี้

SP_vw_ProductMinimumInStock

USE [Northwind]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[SP_vw_ProductMinimumInStock]

AS
BEGIN

SET NOCOUNT ON;

DECLARE @tableHTML NVARCHAR(MAX),
@mailSubject NVARCHAR(100),
@ReportDate DateTime,
@MailTo nvarchar(100)

–//…ดึงวันที่ ปัจจุบัน
SET @ReportDate = getdate()
–//…กำหนด หัวเรื่องของอีเมล์ พร้อมวันที่ปัจจุบัน
SET @mailSubject = ‘Product Minimum In Stock : ‘+
Convert(varchar,@ReportDate,111)+’ ‘+
Convert(varchar,@ReportDate,8)

–//…กำหนดผู้รับเมล์เป็น Dear inventory… ที่จริงคุณจะใส่ชื่อบุคคล…
SET @MailTo = ‘Dear inventory…’

–//…สร้างเนื้อหาอีเมล์ ในรูปแบบ HTML เก็บไว้ในตัวแปล @tableHTML
SET @tableHTML =
–//…สร้าง Style Sheet
‘<style type=”text/css”>
table.gridtable {
font-family: Tahoma,verdana,arial,sans-serif;
font-size:11px;
color:#333333;
border-width: 1px;
border-color: #666666;
border-collapse: collapse;
}
table.gridtable th {
border-width: 1px;
padding: 8px;
border-style: solid;
border-color: #666666;
background-color: #dedede;
}
table.gridtable td {
border-width: 1px;
padding: 8px;
border-style: solid;
border-color: #666666;
background-color: #ffffff;
}
</style> ‘+

‘ <H2>’+@MailTo+'</H2> ‘+ –//…ชื่อผู้รับเมล์
‘ <H3>’+@mailSubject+'</H3>’+–//…หัวเรื่องของอีเมล์
‘ <HR>’+

–//…ตารางรายงาน
‘ <table class=”gridtable”>’ +–//…กำหนด Style Sheet
‘ <tr>’+–//…สร้างชื่อคอลัมน์ ของตาราง
‘<th>ID</th>’+
‘<th>Product</th>’+
‘<th>Category</th>’+
‘<th>UnitsInStock</th>’+
‘ </tr>’ +
–//…สร้างเนื้อหา รายงาน ดึงจากฐานข้อมูล #รูปแบบการเขียนสคริปต์ ศึกษาเพิ่มเติมจาก แหล่งข้อมูลอ้างอิง:
CAST((SELECT DISTINCT
–//…กำหนดฟอนท์ตัวอักษรเป็นตัวหนา
[td/@style] = ‘font-weight:bold’,
td = [ProductID],”,
td = [ProductName],”,
td = [CategoryName],”,
–//…กำหนดพื้นตัวอักษร และสีตัวอักษร…ถ้า
–//…จำนวนสินค้าคงคลัง(UnitsInStock)เหลือศูนย์ ให้กำหนด สีพื้นเป็นสีเหลือง ตัวอักษรเป็นสีแดง
[td/@style] = ‘background-color:’+
CASE WHEN ABS(convert(float,[UnitsInStock])) =0 THEN ‘#FFFF00;’ ELSE ‘#FFFFFF;’ END+’color:’+
CASE WHEN ABS(convert(float,[UnitsInStock])) =0 THEN ‘#FF0000;’ ELSE ‘##000000;’ END,
td = [UnitsInStock] ,”
FROM [vw_ProductMinimumInStock]

FOR XML PATH(‘tr’), TYPE ) AS NVARCHAR(MAX)) +

‘ </table>’+
–//…สิ้นสุดการสร้าง ตารางรายงาน

–//…สร้างข้อความสำหรับ ส่วนท้ายของอีเมล์ (ลายเซ็น)
‘ <hr />’ +
‘ <em>’+
‘ ‘+@mailSubject+’ <BR>’ +
‘ is brought to you by: <BR>’+
‘ <a href=”www.janawat.wordpress.com”>www.janawat.wordpress.com</a><BR>’+
‘ Date: February 25, 2013<BR>’+
‘ </em>’;

–//…ส่งเมล์ผ่าน สโตร์โพรซีเยอร์ sp_send_dbmail… #รูปแบบ การใช้งาน sp_send_dbmail ศึกษาเพิ่มเติมจาก แหล่งข้อมูลอ้างอิง:
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘ProductAutoMail’,–//…E-mail Profile ที่คุณสร้างไว้ในตอนที่ 1
@recipients = ‘nuchit@flowco.co.th’, –//…กำหนด อีเมล์ผู้รับ
@copy_recipients =’nuchit@flowco.co.th;’,–//…กำหนด อีเมล์สำเนาผู้รับ

@subject = @mailSubject,–//…กำหนด หัวเรื่องของอีเมล์
@body = @tableHTML,–//…กำหนด เนื้อหาของอีเมล์ ที่คุณได้สร้างรอไว้แล้วด้านบน ในตัวแปร @tableHTML
@body_format = ‘HTML’ ;–//…กำหนดรูปแบบ อีเมล์ เป็น HTML โดยค่าเริ่มต้นมันจะเป็น Text

END

/*…Test Script*/
/*

Exec SP_vw_ProductMinimumInStock

*/

20. เมื่อเขียนสคริปต์ฯ เรียบร้อย
ทำการทดสอบสคริปต์ฯ ที่เขียน =>
โดยการไฮไลท์เลือกสคริปต์ SQL บรรทัด สุดท้าย
[…Exec SP_vw_ProductMinimumInStock…] =>
โดยการไฮไลท์คลุมโค้ดส่วนใด SQL จะรู้ว่า
เราต้องการรัน เฉพาะโค้ดบริเวณนั้น

21. จากนั้นทำการเอ็กซิคิวต์=>
โดยการกดเมนู Execute บนทูลบาร์ของ
โปรแกรม SQL Server Management Studio

22. เมื่อรันสโตร์โพรซีเยอร์
SP_vw_ProductMinimumInStock =>
คุณจะได้รับข้อความ Mail queued.
ในช่อง Message =>
ให้รอสักครู่ =>
แล้วไปเปิด กล่องจดหมายดู

23. ในกล่องจดหมาย =>
คุณจะได้รับอีเมล์
ดังรูปที่ 5

24. เมื่อดับเบิ้ลคลิกเปิดอ่านเมล์ =>
คุณจะเห็นรายงานข้อมูล สินค้าคงคลัง
จำนวนคงเหลือตำกว่ากำหนด
น้อยกว่า หรือเท่ากับ 9
ซึ่งเรากำหนดไว้ใน View […WHERE UnitsInStock <= 9…]
ดังรูปที่ 2


รูปที่5 รายงาน Product Minimum In Stock

ขั้นตอนที่ 3 สร้าง JOB Schedules ทำงานอัตโนมัติ

25. เมื่อสโตร์โพรซีเยอร์ พร้อมใช้งาน
ต่อไปเราจะทำให้มันทำงานอัตโนมัติ =>
โดยการเพิ่ม JOB สั่งให้มันทำงานแทนเรา

26. กลับไปที่หน้าต่าง Object Explorer อีกครั้ง

27. คลิกคลิกไปที่ SQL Server Agent =>
คลิกขวาบนโฟลเดอร์ Jobs => เลือก New Job…

28. ในหน้าต่าง New Job =>
แทบ General =>
ช่อง Name: ให้ตั้งชื่อ Job ในบทความนี้ใช้ชื่อว่า ProductAutoMail
ดังรูปที่6


รูปที่6 การสร้าง Jobs SQL

29. ไปที่แทบ Steps =>
คลิกปุ่ม New…

30. ในหน้าต่าง Job Step Properties – Send mail=>
แทบ General =>
Step Name ระบุชื่อ Send mail

31. ช่อง Type: เลือก Transact-SQL script (T-SQL)

32. ช่อง Database: เลือก Nortwind

33. ช่อง Command:
ใส่โค้ดการเอ็กซิคิวต์ สโตร์โพรซีเยอร์ [Exec SP_vw_ProductMinimumInStock] =>
แล้วกดปุ่ม OK

34. จะปรากฏชื่อ Step ในหน้าต่าง Job Step Properties
ดังรูปที่ 7


รูปที่7 กำหนด Command เอ็กซิคิวต์ สโตร์โพรซีเยอร์ SP_vw_ProductMinimumInStock

35. ตั้งเวลา คลิกไปที่แทบ Schedules=>
คลิก New…

36. ในหน้าต่าง New Job Schedule =>
ช่อง Name: ระบุ #7.00
บอกให้เรารู้ว่า มันส่งเวลา 7.00 น.

37. ให้มันทำงานทุกวัน =>
ให้ระบุ Occurs: เป็น Daly =>
และ Recurs every: เป็น 1

38. ให้ส่งตอน 7.00 น. =>
ให้ติ๊กเลือก Occurs once at: เป็น 7:00:00 =>
จากนั้นคลิกปุ่ม OK
ดังรูปที่ 8


รูปที่8 กำหนดเวลาส่ง(Schedule)

39. จากข้อ 38 เมื่อคลิก OK =>
Job จะถูกสร้างเรียบร้อย รอทำงาน ตอน 7.00 น.
ในวันรุ่งขึ้น

39. ถ้าคุณต้องการทดสอบดูว่า Job ที่คุณสร้างไป
ทำงานได้จริง หรือไม่?
สามารถทำได้โดย

40. ไปที่หน้าต่าง Object Explorer อีกครั้ง=>
คลิก SQL Server Agent =>
คุณจะเห็นรายชื่อ Job ProductAutoMail
ปรากฏเพิ่มเข้ามา

41. ทดสอบ รัน Job โดยการคลิกขวาบน Job =>
เลือกเมนู Start Job as Step…

42. หน้าต่างแสดง การรัน Job จะแสดง การทำงาน ขี้นมา =>
เมื่อรันเสร็จเรียบร้อย (ดูช่อง Status = Success) =>
ให้กดปุ่ม Close

43. ให้กลับไปเปิด กล่องอีเมล์ ดูอีกครั้ง=>
คุณจะเห็นรายงานส่งเข้ามา

44. ถ้าต้องการแก้ไขบางอย่างใน Job ละ
จะทำอย่างไร? เช่น
การเพิ่มเวลาส่ง…=>
สามารถเข้าแก้ไข ได้จากตรงนี้เช่นกัน=>
คลิกขวา บน Job ใหม่อีกครั้ง=>
แต่ครั้งนี้เลือกเมนู Properties =>
เท่านี้ หน้าต่าง Job Properties
จะเปิดขึ้นมา คุณสามารถแก้ไข ปรับแต่ง
ได้ตามใจชอบ
ดังในรูปที่ 9


รูปที่ 9 JOB ProductAutoMail

และจากนี้ ทุกเช้าหลังเวลา 7.00 น.
คุณจะได้รับอีเมล์อัตโนมัติ แจ้งเตือน
รายงาน Product Minimum In Stock

จำนวนสินค้าคงเหลือ น้อยกว่าที่กำหนด
ตามที่คุณระบุไว้ในตอน การสร้าง View
ซึ่งคุณอาจจะย้าย จำนวนที่กำหนดไปไว้ อีกตารางหนึ่ง
เพื่อสามารถปรับปรุง แก้ไขตัวเลข ให้มากน้อย
ได้ตามต้องการ โดยไม่ต้องไปแก้ไข View บ่อย
ระบบจะดูดีขึ้นทันที
=>แล้วพบ กันโอกาสหน้า<=

แหล่งข้อมูลอ้างอิง:

แหล่งข้อมูลดาวน์โหลด:

  • ตัวอย่าง ฐานข้อมูล Northwind: http://goo.gl/92oYH
  • สคริปต์ SQL…สร้างสโตร์โพรซีเยอร์…สร้างView…สร้างJOB: http://sdrv.ms/125mVhk

ขอขอบคุณ บทความจาก นิตยสาร WindowsITPro

เกี่ยวกับ

Nuchit Atjanawat is a Microsoft MVP, GreatFriends Community Leader, WindowsITPro Columnist(Mr.Nano), Silverlight Business Application Instructor, Speaker in various Microsoft events, Author, passionate Blogger and a Software Engineer by profession. He has a very good skill over Silverlight, LightSwitch, C#, XAML & Blend. He shares his technical findings, tutorials in his technical blog. Follow his Blog and Facebook page to be updated on latest articles. - Technical Blog: https://janawat.wordpress.com - WindowsITPro Columnist(Mr.Nano): http://windowsitpro.net - The GreatFriends.Biz Community Discussion Board (nano): http://greatfriends.biz - witter: https://twitter.com/janawat - Facebook Page: https://www.facebook.com/nuchit - Instructor: http://bit.ly/GF250-01, http://bit.ly/GF150-02

เขียนใน MS SQL

ใส่ความเห็น

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / เปลี่ยนแปลง )

Twitter picture

You are commenting using your Twitter account. Log Out / เปลี่ยนแปลง )

Facebook photo

You are commenting using your Facebook account. Log Out / เปลี่ยนแปลง )

Google+ photo

You are commenting using your Google+ account. Log Out / เปลี่ยนแปลง )

Connecting to %s

In Archive
%d bloggers like this: