perl查詢到期帳號並發送電子郵件通知

要執行下面程式,請先建立好unixODBC和FreeTDS的設定

查詢到期帳號發送通知的程式碼如下:

use strict;
use DBI;
#連線資料庫
my $dbh = DBI->connect("dbi:ODBC:ERP","sa","password") or die "$DBI::errstr\n"; 
#取得今天日期作為查詢條件字串
my($today)=`date +%Y%m%d`;
#資料庫查詢字串,查詢今天之前密碼到期的帳號但不包含工號為9開頭的帳號
my $statement =<<end;
select rtrim(MV001) MV001,rtrim(NAME) NAME,MV002,MV003,MV098,NEXTTIME_CHANGEPASSWD from YCMSMV
where NEXTTIME_CHANGEPASSWD < '$today' and MV001 not like '9%';
end
#儲存信件內容字串變數
my $content  =<<end;
<style>
td {width:100px;border:1px solid purple;}
</style>
<p>以下列出密碼過期 %s 組帳號</p>
<table>
<thead><tr><th>員工姓名</th><th>員工編號</th><th>密碼過期日</th></tr></thead>
<tbody>
end
#由資料庫取得查詢的資料
my $emps = $dbh->selectall_arrayref($statement,{ Slice => {} });
my $count=0;
#將取得的資料一一寫入信件內容中
foreach my $emp ( @$emps ) {
	$count+=1;
	$content .= "<tr><td>$emp->{NAME}</td><td>$emp->{MV001}</td><td>$emp->{NEXTTIME_CHANGEPASSWD}</td></tr>";
}                 
$content.="</tbody></table>";
$content=sprintf($content,$count);
my $rc = $dbh->disconnect  or warn $dbh->errstr;

#信件主旨
my $subject  = "網頁帳號密碼過期通知";
#使用ubuntu的mail指令執行郵件寄送功能,使用html格式、填入主旨、指定收信人
my $sendmail = "/usr/bin/mail -a 'Content-type: text/html;' -s $subject MIS\@toyoink-ct.com.tw\n";
#開啟檔案
open(SENDMAIL, "|$sendmail") or die "Cannot open $sendmail: $!";
#將郵件內容寫入
print SENDMAIL $content;
#關閉檔案即開始寄送郵件
close(SENDMAIL);

發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

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照片

You are commenting using your Facebook account. Log Out / 變更 )

Google+ photo

You are commenting using your Google+ account. Log Out / 變更 )

連結到 %s