Calling Lotus Notes Agent From .NET (Domino 7)

今天參考 http://ithelp.ithome.com.tw/question/10109131 的做法來寫一支Winform去啟動 Notes的Agent(因為Scheduled Agent經常會罷工只好手動).

做完之後, 發現大部份Agent都無法執行RunOnServer.(報錯)

找不到原因之下,最後只好在其中一個看板寫一個簡單的啟動其他Agent之轉接Agent程式.再由C#來啟動這支轉接Agent就都OK啦!

這支轉接Agent的Code 如下
(Declarations)
Dim session As NotesSession
Dim db As NotesDatabase
Dim doc As NotesDocument
Dim view As NotesView

Dim agent As NotesAgent
Dim profile As NotesDocument
Dim currentLog As NotesLog

Sub Initialize
Dim m_item As NotesItem
Dim m_db As NotesDatabase
Dim m_agent As NotesAgent
'Dim m_col As NotesDocumentCollection
Dim m_mail As NotesDocument
Set session=New NotesSession
Set db=session.CurrentDatabase
Set agent = session.CurrentAgent
Set currentLog = New NotesLog( db.Title+" - Agent - "+agent.Name+" on "+db.Server )
Set profile=db.GetProfileDocument("SystemProfile")
Call currentLog.OpenNotesLog( db.Server, profile.LogPath(0) )
Call currentLog.LogAction("代理程式執行檢查 : "+agent.Name)
Set m_db=session.GetDatabase(db.Server,"Flow\CheckFlow.nsf",False)
If Not m_db Is Nothing Then
Call currentLog.LogAction("取得資料庫 : "+m_db.Title)
Set m_agent =m_db.GetAgent("GetSapDataAgent")
If Not m_agent Is Nothing Then
m_find=True
Print "Run Agent : "+m_agent.Name
Call currentLog.LogAction("開始執行代理程式 : "+m_agent.Name)
If m_agent.RunOnServer=0 Then
m_runOk=True
Call currentLog.LogAction("完成執行代理程式 : "+m_agent.Name)
Else
Call currentLog.LogError(1,"代理程式執行錯誤!!")
End If
Else
Call currentLog.LogError(1,"無法取得代理程式，代理程式執行錯誤!!")
End If
Else
Call currentLog.LogError(1,"無法取得資料庫，代理程式執行錯誤!!")
End If
currentLog.LogAction("代理程式結束作業完成!!")
Call currentLog.Close
End Sub

Notes Domino R7 自動抓取國衛院網頁上的公告招標資訊並匯入看板

老大說希望有新的標案就自動通知相關人員, 所以就先做了自動匯入來玩玩看

網址如下,

http://po.nhri.org.tw/po_board/po_query.jsp

代理程式如下 : (使用Java)

import lotus.domino.*;
import java.net.*;
import java.io.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.Date;
import java.util.Vector;
import java.text.*;

public class JavaAgent extends AgentBase {

private Session session;
private AgentContext agentContext;
private Database db;
private Log log;
private Document doc;

public void NotesMain() {

try {
Session session = getSession();
AgentContext agentContext = session.getAgentContext();
Agent agent = agentContext.getCurrentAgent();

// (您的程式碼移至此處)
String web1 = "http://po.nhri.org.tw/po_board/po_query.jsp" ;//UTF-8 國衛院
read1(session,agentContext,web1);


} catch(Exception e) {
e.printStackTrace();
}
}

public void read1( Session session,AgentContext agentContext,String strURL ) throws NotesException{
         int chunksize = 4096;
        byte[] chunk = new byte[chunksize];
        int count,idx;
        try  {
db=agentContext.getCurrentDatabase();
            URL pageUrl = new URL(strURL );
            // 讀入網頁(位元串流)
            //http://po.nhri.org.tw/po_board/docdownload.jsp?ticket_id=1020432&file_id=201312021140130.pdf
            BufferedInputStream bis = new BufferedInputStream(pageUrl.openStream());
            //BufferedOutputStream bos = new BufferedOutputStream(new FileOutputStream("URL1.txt", false));
            System.out.println("國衛院資料讀取中" );
            String cname;
            while ((count = bis.read(chunk, 0, chunksize)) != -1) {
               // bos.write(chunk, 0, count); // 寫入檔案
                cname=new String(chunk,"UTF-8");
                int nn=0;
View viewR = db.getView ("ViewByNoA");
Pattern pattern = Pattern.compile("<(td|a href)[^>]*>(.*?)</(td|a)>");
Matcher matcher = pattern.matcher(cname);
while(matcher.find()) {
if (matcher.group().substring(0, 2).compareTo("<a")==0 & nn>0) {
nn=nn+1;
doc.replaceItemValue ("Name",matcher.group().replaceAll("</?[a-z][a-z0-9]*[^<>]*>", ""));    
System.out.println(Integer.toString(nn)+":"+matcher.group().replaceAll("</?[a-z][a-z0-9]*[^<>]*>", ""));
Pattern pattern1 = Pattern.compile("(\"d)(.*?)\"");
Matcher matcher1 = pattern1.matcher(matcher.group());
while(matcher1.find()) {
nn=nn+1;
doc.replaceItemValue ("Hyper","http://po.nhri.org.tw/po_board/"+matcher1.group().replaceAll("\"",""));
System.out.println(Integer.toString(nn)+":"+"檔案網址:http://po.nhri.org.tw/po_board/"+matcher1.group().replaceAll("\"",""));
}
}
else {    
if (matcher.group().replaceAll("</?[a-z][a-z0-9]*[^<>]*>", "").replaceAll("&nbsp;","").compareTo("標的案號")==0 | nn>0) {
nn= nn+1;
cname=matcher.group().replaceAll("</?[a-z][a-z0-9]*[^<>]*>", "").replaceAll("&nbsp;","");
System.out.println(Integer.toString(nn)+":"+cname);
switch(nn) {
  case 1:
      break;
  case 2:
      boolean NotFound = true;
   DocumentCollection dc = db.getAllDocuments();
   Document docR = dc.getFirstDocument ();
   while (docR != null){          
        if (docR.getItemValueString("No").equals(cname) & docR.getItemValueString("Customer").equals("國衛院") ){
                NotFound = false;
            System.out.println("Find:"+cname);
break;
};
       docR = dc.getNextDocument();
    };
   if ( NotFound ) {  
      Date dt = new Date();
   SimpleDateFormat ft =  new SimpleDateFormat (" yyyy.MM.dd E");
   doc = db.createDocument ();
      doc.replaceItemValue ("Form","Form1");
      doc.replaceItemValue ("Customer","國衛院");
      doc.replaceItemValue ("IDay",ft.format(dt));
      doc.replaceItemValue ("No",cname);
      }
      else {      
      nn=0;
      }
      break;
  case 4:
      doc.replaceItemValue ("Item",cname);
      break;
  case 8:
      doc.replaceItemValue ("Type",cname);
      break;
   case 16:
      doc.replaceItemValue ("DDay",cname);
      break;
  case 18:
      doc.replaceItemValue ("Aday",cname);
      if (doc.save ()) {
System.out.println ("Document created and saved");
} else {
System.out.println ("Something went wrong");
}
nn=0;
      break;
  default:      
      break;
}      
}
    }  
};
// System.out.println(cname);
            }
            //bos.close();
            bis.close();
           System.out.println("國衛院資料讀取完成");
         }catch (IOException e) {
             e.printStackTrace();
             try{
session.setEnvironmentVar("AgentResult","國衛院資料讀取時，發生錯誤 !!");
}catch ( Exception ex){}
         }
      }
}

用主控台應用程式(C#)來執行Web API(OData)

老李在一次去 Study4.TW 的場合上課中，聽到 KKBruce 講解有關Web API的內容，這裡面提到了用主控台應用程式來執行Web API(不需要用到IIS喔)，感覺還蠻有趣的。老李開始找一下相關的資料，發現 Self-Host a Web API (C#)這篇文章, 我用winXP+vs2010+.net Framework4試做成功了喔.當中只有add the Web API Self-Host package這個地方因為在擴充管理員內找不到,所以要改用http://nuget.org/packages/Microsoft.AspNet.WebApi.SelfHost/ 所提到的方法來安裝就可以了.

至於OData的部分, 請參考貓大的文章來做, 記得先用http://www.nuget.org/packages/Microsoft.AspNet.WebApi.OData所提到的方法來安裝OData喔.

全部Source Code如下(C#)_測試用所以沒刪除一些用不到的東東

兩個類別範例
Product.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace SelfHost
{
    public class Product
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string Category { get; set; }
        public decimal Price { get; set; }
    }
}

EmployeesInfo.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace SelfHost
{
    public class EmployeesInfo
    {
        String m_WorkNo;
        string m_Name;

        public string WorkNo
      {
          get
           {
               return m_WorkNo;
           }
           set
           {
              m_WorkNo = value;
           }
       }

        public string Name
        {
            get
            {
                return m_Name;
            }
            set
            {
                m_Name = value;
            }
        }
    }
}

啟動程式
Program.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web.Http;
using System.Web.Http.SelfHost;

namespace SelfHost
{
    class Program
    {
        static void Main(string[] args)
        {
            var config = new HttpSelfHostConfiguration("http://localhost:8080");

            config.Routes.MapHttpRoute(
                "API Default", "api/{controller}/{id}",
                new { id = RouteParameter.Optional });

            using (HttpSelfHostServer server = new HttpSelfHostServer(config))
            {
                server.OpenAsync().Wait();
                Console.WriteLine("Press Enter to quit.");
                Console.ReadLine();
            }
        }
    }
}

Controller
ProductsController.cs

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Net;
using System.Web.Http;

namespace SelfHost
{
    public class ProductsController : ApiController
    {
        Product[] products = new Product[]
        {
            new Product { Id = 1, Name = "Tomato Soup", Category = "Groceries", Price = 1 },
            new Product { Id = 2, Name = "Yo-yo", Category = "Toys", Price = 3.75M },
            new Product { Id = 3, Name = "Hammer", Category = "Hardware", Price = 16.99M }
        };

        [Queryable]
        public IQueryable<EmployeesInfo> GetAllProducts()
        {
            List<EmployeesInfo> EmpLists;
            EmpLists=ReadEmplyeesData();
            return EmpLists.AsQueryable();
        }

        public Product GetProductById(int id)
        {
            var product = products.FirstOrDefault((p) => p.Id == id);
            if (product == null)
            {
                throw new HttpResponseException(HttpStatusCode.NotFound);
            }
            return product;
        }

        public IEnumerable<Product> GetProductsByCategory(string category)
        {
            return products.Where(p => string.Equals(p.Category, category,
                    StringComparison.OrdinalIgnoreCase));
        }

        static private string GetConnectionString()
        {
            // To avoid storing the connection string in your code,
            // you can retrieve it from a configuration file, using the
            // System.Configuration.ConfigurationSettings.AppSettings property
            return "Data Source = EXExxxx;Initial catalog = Gxxxx;" +
            "User id = sa; Password = Gxxxx";
        }

        private static List<EmployeesInfo> ReadEmplyeesData()
        {
            List<EmployeesInfo> EmpLists = new List<EmployeesInfo>();
            EmployeesInfo tEMPL;
            string connectionString = GetConnectionString();
            string queryString =
                "SELECT * FROM dbo.Employees;";
            using (SqlConnection connection = new SqlConnection(
                       connectionString))
            {
                SqlCommand command = new SqlCommand(
                    queryString, connection);
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();
                try
                {
                    while (reader.Read())
                    {
                        tEMPL = new EmployeesInfo();
                        tEMPL.WorkNo = reader[1].ToString();
                        tEMPL.Name = reader[16].ToString();
                        EmpLists.Add(tEMPL);
                    }
                }
                finally
                {
                    // Always call Close when done reading.
                    reader.Close();
                }
                return EmpLists;
            }
        }
    }

}

P.S.撈資料庫的範例請自行改寫,否則無法執行喔!

執行成功後, 建議按照貓大的方法用Chrome Postman來測試比較方便!

ASP截取氣象局V7網頁, 示範抓取台中市的最新預報氣候及溫度

我們的需求是截取這部分訊息Include到EIP的首頁上

程式範例如下(改編[蔡玉貴老師]自http://wa.sjps.ntpc.edu.tw/blog/View.asp?cid=955)

        <%
'--取得<td></td>間的文字內容
Function ReplaceAll(str1, patrn, replStr)
Dim regEx
Set regEx = Server.CreateObject("VBScript.RegExp") ' Create regular expression.
regEx.Pattern = patrn   ' Set pattern.
regEx.IgnoreCase = True ' Make case insensitive.
regEx.Global = True
ReplaceAll = regEx.Replace(str1, replStr)  ' Make replacement.
Set regEx = Nothing
End Function

'--判斷是否已經截取過，如果已經存在就直接秀出氣象結果，為了增加效能，
'不必每次使用者上實踐網站就前往中央氣象局，只要伺服器在特定時間前往取回，
'寫入伺服器中，每天會更新四次。爾後，一般使用者瀏覽實踐網站時，
'就會直接讀取已經事先擷取的氣象資訊，這種作法比較有效率。
Set FSO=Server.CreateObject("Scripting.FileSystemObject")
cktime = "17"
if hour(now)> 16 then '--每天上午0:00 5:00, 11:00與下午5:00 前往氣象局抓取氣象資料
  getfile=year(date)&month(date)&day(date)&"17.txt"
  cktime = "17"
ElseIf hour(now)> 10 then
  getfile=year(date)&month(date)&day(date)&"11.txt"
  cktime = "11"
ElseIf hour(now)> 4 then
  getfile=year(date)&month(date)&day(date)&"5.txt"
  cktime = "05"
else
  getfile=year(date)&month(date)&day(date)&".txt"
end if

whichfile=server.MapPath("\")&"\GetTodayWeather\"&getfile '--以當天日期作為紀錄檔

If FSO.FileExists(whichfile) then
Set txt = FSO.OpenTextFile(whichfile,1)
rline = txt.ReadAll
txt.Close
response.write rline
else
'--擷取外部網站html原始碼
dim objXMLHTTP
Dim Pos,Pos1,BodyG,Gettdtext

'--擷取中央氣象局台中地區氣象資料
URL = Request.form("URL")
if ( URL = "" ) then
 URL ="http://www.cwb.gov.tw/V7/forecast/taiwan/Taichung_City.htm"
end if

Set objXMLHTTP = Server.CreateObject("Microsoft.XMLHTTP")
objXMLHTTP.Open "GET", URL, false
objXMLHTTP.Send
strTmp = bytesToBSTR(objXMLHTTP.responseBody,"utf-8")
TimeStamp = MID(strTmp,InStr(strTmp,"發布時間")+18,2)
strTmp=MID(strTmp,InStr(strTmp,"今明預報"),550)
strTmp=MID(strTmp,InStr(strTmp,"<td>")+4,200)
degree=MID(strTmp,1,InStr(strTmp,"</td>")-1)&"℃"
strTmp=MID(strTmp,InStr(strTmp,"alt=")+5,50)
weather=MID(strTmp,1,InStr(strTmp,"title")-3)
response.write (weather&"<br>("&degree&")")
if cktime = TimeStamp then '確定已更新才存檔
Set NewFile=FSO.CreateTextFile(whichfile,True)
NewFile.Write (weather&"<br>("&degree&")")
NewFile.Close
Set NewFile=Nothing
end if
Set objXMLHTTP = Nothing
end if

'--中文轉換

Function BytesToBstr(body,Cset)
dim objstream
 set objstream = Server.CreateObject("adodb.stream")
 objstream.Type = 1
 objstream.Mode =3
 objstream.Open
 objstream.Write body
 objstream.Position = 0
 objstream.Type = 2
 objstream.Charset = Cset
 BytesToBstr = objstream.ReadText
 objstream.Close
 set objstream = nothing
End Function
%>

Database Mail的範例

此範例式搭配我的EMS, 建立在SQL Server agent上的作業定期執行

重點:

1.只能發UTF-8格式

2.千萬要處理好Null欄位的問題啦!

USE [GJ_Test]

GO

DECLARE @msg varchar(8000) 

DECLARE @msg_subject varchar(200)

DECLARE @mailaddr varchar(500)

DECLARE @MaxRownum int

DECLARE @Iter int

--發通知信

SET @mailaddr = 'a@goldjoint.com.tw;w.you@goldjoint.com.tw;fa@goldjoint.com.tw;ni.l@goldjoint.com.tw'

--SET @mailaddr = 'ni.l@goldjoint.com.tw'

SET @msg_subject = '請安排信中所列儀器的相關校驗事宜'

if object_id('#EMails') is not null

begin

  drop table #EMails

end

SELECT

    RowNum = ROW_NUMBER() OVER(ORDER BY a.F_ID)

    ,a.*,b.F_Equip_Name,b.F_filename

INTO #EMails

FROM T_EMS_Equip_History a left join T_EMS_Equipment b on a.F_ID=b.F_ID

WHERE a.F_Notice_Email = 1 and a.F_Notice_Date <= CONVERT(VARCHAR(8) , GETDATE(), 112 ) and a.F_Sent_Date is null

SET @MaxRownum = (SELECT MAX(RowNum) FROM #EMails)

IF @MaxRownum > 0 

Begin

SET @Iter = (SELECT MIN(RowNum) FROM #EMails)

SET @msg = '清單所列儀器請先安排相關校驗事宜' + char(13) + char(10)+ char(13) + char(10)

set @msg='<B><font size=4 color="Blue">'+@msg+'</B></font><br /><br />'

WHILE @Iter <= @MaxRownum

BEGIN

    SET @msg = @msg + (SELECT '開始準備日期:'+F_Prepare_Date+'  應校驗日期:'+F_Cali_Date+'  編號:'+F_ID+'_'+(CASE WHEN F_Equip_Name IS NULL THEN '沒有名稱' ELSE F_Equip_Name END)+ '<img src="file://192.168.2.231/GJERP$/品管部/儀器/'+(CASE WHEN F_filename IS NULL THEN 'NONE.png' ELSE F_filename END) +'" height="100" width="100">'  as mg

    FROM #EMails

    WHERE RowNum = @Iter) + char(13) + char(10)+ '<br />'

    SET @Iter = @Iter + 1

END

SET @msg = @msg + char(13) + char(10) + '<br />'+ ' 共' + cast(@MaxRownum as varchar) + '項' 

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'NickAutoMail',

@recipients = @mailaddr, 

@subject = @msg_subject,

@body_format=HTML,

@body = @msg;  

UPDATE T_EMS_Equip_History SET F_Notice_Email = 0

WHERE F_Notice_Email = 1 and F_Notice_Date <= CONVERT(VARCHAR(8) , GETDATE(), 112 ) and F_Sent_Date is null     

END

DROP TABLE #EMails

--發準備信

SET @mailaddr = 'nick.l@goldjoint.com.tw'

--SET @mailaddr = 'nick.l@goldjoint.com.tw'

SET @msg_subject = '請開始準備校驗信中所列儀器'

if object_id('#EMails2') is not null

begin

  drop table #EMails2

end

SELECT

    RowNum = ROW_NUMBER() OVER(ORDER BY a.F_ID)

    ,a.*,b.F_Equip_Name,b.F_filename

INTO #EMails2

FROM T_EMS_Equip_History a left join T_EMS_Equipment b on a.F_ID=b.F_ID

WHERE a.F_Prepare_Email = 1 and a.F_Prepare_Date <= CONVERT(VARCHAR(8) , GETDATE(), 112 ) and a.F_Sent_Date is null

SET @MaxRownum = (SELECT MAX(RowNum) FROM #EMails2)

IF @MaxRownum > 0 

Begin

SET @Iter = (SELECT MIN(RowNum) FROM #EMails2)

SET @msg = '清單所列儀器請開始準備校驗工作' + char(13) + char(10)+ char(13) + char(10)

set @msg='<B><font size=5 color="Blue">'+@msg+'</B></font><br /><br />'

WHILE @Iter <= @MaxRownum

BEGIN

    SET @msg = @msg + (SELECT '開始準備日期:'+F_Prepare_Date+'  應校驗日期:'+F_Cali_Date+'  編號:'+F_ID+'_'+(CASE WHEN F_Equip_Name IS NULL THEN '沒有名稱' ELSE F_Equip_Name END)+ '<img src="file://192.168.2.231/GJERP$/品管部/儀器/'+(CASE WHEN F_filename IS NULL THEN 'NONE.png' ELSE F_filename END) +'" height="100" width="100">'  as mg

    FROM #EMails2

    WHERE RowNum = @Iter) + char(13) + char(10) + '<br />'

    SET @Iter = @Iter + 1

END

SET @msg = @msg + char(13) + char(10) + '<br />' + ' 共' + cast(@MaxRownum as varchar) + '項' 

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'NickAutoMail',

@recipients = @mailaddr, 

@subject = @msg_subject,

@body_format=HTML,

@body = @msg;  

UPDATE T_EMS_Equip_History SET F_Prepare_Email = 0

WHERE F_Prepare_Email = 1 and F_Prepare_Date <= CONVERT(VARCHAR(8) , GETDATE(), 112 ) and F_Sent_Date is null

END       

DROP TABLE #EMails2    

--發到期信

SET @mailaddr = 'nick.l@goldjoint.com.tw'

--SET @mailaddr = 'nick.l@goldjoint.com.tw'

SET @msg_subject = '[重要]信中所列儀器今天已經到期請校驗'

if object_id('#EMails3') is not null

begin

  drop table #EMails3

end

SELECT

    RowNum = ROW_NUMBER() OVER(ORDER BY a.F_ID)

    ,a.*,b.F_Equip_Name,b.F_filename

INTO #EMails3

FROM T_EMS_Equip_History a left join T_EMS_Equipment b on a.F_ID=b.F_ID

WHERE a.F_Cali_Date = CONVERT(VARCHAR(8) , GETDATE(), 112 ) and a.F_Sent_Date is null

SET @MaxRownum = (SELECT MAX(RowNum) FROM #EMails3)

IF @MaxRownum > 0 

Begin

SET @Iter = (SELECT MIN(RowNum) FROM #EMails3)

SET @msg = '清單所列儀器今天到期請校驗' + char(13) + char(10)+ char(13) + char(10)

set @msg='<B><font size=6 color="red">'+@msg+'</B></font><br /><br />'

WHILE @Iter <= @MaxRownum

BEGIN

    SET @msg = @msg + (SELECT '應校驗日期:'+F_Cali_Date+'  編號:'+F_ID+'_'+(CASE WHEN F_Equip_Name IS NULL THEN '沒有名稱' ELSE F_Equip_Name END)+ '<img src="file://192.168.2.231/GJERP$/品管部/儀器/'+(CASE WHEN F_filename IS NULL THEN 'NONE.png' ELSE F_filename END) +'" height="100" width="100">'  as mg

    FROM #EMails3

    WHERE RowNum = @Iter) + char(13) + char(10)+ '<br />'

    SET @Iter = @Iter + 1

END

SET @msg = @msg + char(13) + char(10)+ '<br />' + ' 共' + cast(@MaxRownum as varchar) + '項' 

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'NickAutoMail',

@recipients = @mailaddr, 

@subject = @msg_subject,

@body_format=HTML,

@body = @msg;  

END       

DROP TABLE #EMails3  

--發逾期信

SET @mailaddr = 'a@goldjoint.com.tw;nick.l@goldjoint.com.tw'

--SET @mailaddr = 'nick.l@goldjoint.com.tw'

SET @msg_subject = '[緊急]信中所列儀器已經逾期請速校驗'

if object_id('#EMails4') is not null

begin

  drop table #EMails4

end

SELECT

    RowNum = ROW_NUMBER() OVER(ORDER BY a.F_Cali_Date)

    ,a.*,b.F_Equip_Name,b.F_filename,DateDiff(d, cast(a.F_Cali_Date as datetime),GETDATE()) as Delay_Date

INTO #EMails4

FROM T_EMS_Equip_History a left join T_EMS_Equipment b on a.F_ID=b.F_ID

WHERE a.F_Cali_Date < CONVERT(VARCHAR(8) , GETDATE(), 112 ) and a.F_Sent_Date is null

SET @MaxRownum = (SELECT MAX(RowNum) FROM #EMails4)

IF @MaxRownum > 0 

Begin

SET @Iter = (SELECT MIN(RowNum) FROM #EMails4)

SET @msg = '清單所列儀器已經逾期請速校驗' + char(13) + char(10)+ char(13) + char(10)

set @msg='<font size=7 color="red"><B>'+@msg+'</B></font><br /><br />'

WHILE @Iter <= @MaxRownum

BEGIN

    SET @msg = @msg + (SELECT '應校驗日期:'+F_Cali_Date+'  逾期天數:<font color="red"><B>'+cast(Delay_Date as varchar)+'</B></font>  編號:'+F_ID+'_'+(CASE WHEN F_Equip_Name IS NULL THEN '沒有名稱' ELSE F_Equip_Name END)+ '<img src="file://192.168.2.231/GJERP$/品管部/儀器/'+ (CASE WHEN F_filename IS NULL THEN 'NONE.png' ELSE F_filename END) +'" height="100" width="100">'  as mg

    FROM #EMails4

    WHERE RowNum = @Iter) + char(13) + char(10) +'<br />'

    SET @Iter = @Iter + 1

END

SET @msg = @msg + char(13) + char(10) + '<br />' + ' 共' + cast(@MaxRownum as varchar) + '項' 

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'NickAutoMail',

@recipients = @mailaddr, 

@subject = @msg_subject,

@body_format=HTML,

@body = @msg;  

END       

DROP TABLE #EMails4

asp 使用三竹簡訊群發的範例

<%@LANGUAGE="VBSCRIPT" CODEPAGE="950"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=big5">
<title>無標題文件</title>
</head>

<body>
<%
'讀取檔案內容
Set fs = CreateObject("Scripting.FileSystemObject")
FileName=server.MapPath("testpost.txt")
Set fw = fs.OpenTextFile(FileName,1,true)
SendBody=fw.ReadAll
fw.close
'正式HTTPS
'sendURL="https://smexpress.mitake.com.tw:9601/SmSendPost.asp?username=XXXXX&password=@@@@@@&encoding=UTF8"
'正式HTTP
'sendURL="http://smexpress.mitake.com.tw:9600/SmSendPost.asp?username=SITTest&password=1234&encoding=UTF8"
'測試HTTP
sendURL="http://163.29.39.31:8000/SmSendPost.asp?username=SITTest&password=1234&encoding=UTF8"
Set objWinHttp = Server.CreateObject("WinHttp.WinHttpRequest.5.1")
objWinHttp.Open "POST", sendURL
objWinHttp.SetRequestHeader "Content-Type","application/x-www-form-urlencoded"
'objWinHTTP.Option(2) = 950 '用來忽略SSL憑證的的錯誤
objWinHTTP.Option(4) = &H3300 '用來忽略SSL憑證的的錯誤
objWinHttp.Send SendBody

'處理從Server端送過來的資料
varBinData=objWinHttp.ResponseBody()
varStrData=Bin2Str(varBinData)

response.write varStrData

'Binary資料轉為String
Function Bin2Str(varBinData)
    Dim varlen,clow,cstrc,skipflag
         skipflag=0
    cstrc = ""
    If Not IsNull(varBinData) Then
      varlen=LenB(varBinData)
      For i=1 To varlen
          If skipflag=0 Then
             clow = MidB(varBinData,i,1)
             '判斷是否中文
             If AscB(clow) > 127 Then
                cstrc =cstrc & Chr(AscW(MidB(varBinData,i+1,1) & clow))
                skipflag=1
             Else
                cstrc = cstrc & Chr(AscB(clow))
             End If
          Else
             skipflag=0
          End If
       Next
    End If
    bin2str = cstrc
End Function
%>
<input name="Submit2" type="button" onClick="javascript:window.opener=null;window.open('','_self');window.close();" value="關閉視窗">
</body>
</html>

asp的同目錄下請放testpost.txt如下


[101]
DestName=經理
dstaddr=0938444585
smbody=我是測試預約15:45發送
dlvtime=20121129154500
response=http://192.168.1.200/smreply.asp
[102]
DestName=二寶
dstaddr=0983444555
dlvtime=20121129155000
smbody=我是測試預約15:50發送
[103]
DestName=小明
dstaddr=0983444114
dlvtime=20121129154000
smbody=我是測試預約15:40發送

c# 使用三竹簡訊群發的範例

http://tw.myblog.yahoo.com/wululu-blog/article?mid=170&next=169&l=f&fid=16

把上面這個例子再簡化說明清楚一點

VS新增一個控制台專案 :


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections.Specialized;
using System.Net;
using System.IO;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
        string tRequestFilePath = @"D:\\a.ini";
        string tUrl = "http://smexpress.mitake.com.tw:9600/SmSendPost.asp?username=XXXXXXXA&password=11111111&encoding=Big5";
        HttpWebRequest request = (HttpWebRequest)WebRequest.Create(tUrl);
        request.Timeout = 1000 * 5;
        request.Method = "POST";
        request.ContentType = "application/x-www-form-urlencoded";
        //撈出發送資料放到tMSG存成格式為ANSI(必須存成INI格式喔)這裡我直接用一個ini檔來簡化這個例子
        // StreamWriter sw = new StreamWriter(tRequestFilePath, true, System.Text.Encoding.Default);
        // sw.Write(tMSG.ToString());
        // sw.Close();
        //再讀出來
        StreamReader sr = new StreamReader(tRequestFilePath, System.Text.Encoding.Default);
        string strTest = sr.ReadToEnd();
        sr.Close();
        //轉成byte格式
        Byte[] B = System.Text.Encoding.Default.GetBytes(strTest);
        request.ContentLength = B.Length;
        System.IO.Stream ioStream = request.GetRequestStream(); //開始提交數據
        ioStream.Write(B, 0, B.Length);
        ioStream.Close();
        //取得回來訊息
        HttpWebResponse response = request.GetResponse() as HttpWebResponse;
        StreamReader reader = new StreamReader(response.GetResponseStream(), Encoding.Default);
        string tReturn = reader.ReadToEnd();
        //可以輸出 或存檔tReturn

        Console.WriteLine(tReturn);
        Console.ReadKey();

        reader.Close();        }
    }
}

D:\\a.ini (ansi格式)內容如下


[101]
DestName=經理
dstaddr=0980944485
smbody=我是測試1
response=http://192.168.1.200/smreply.asp
[102]
DestName=二寶
dstaddr=0982944445
smbody=我是測試2
[103]
DestName=小明
dstaddr=0981444444
smbody=我是測試3