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