老大說希望有新的標案就自動通知相關人員, 所以就先做了自動匯入來玩玩看
網址如下,
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(" ","").compareTo("標的案號")==0 | nn>0) {
nn= nn+1;
cname=matcher.group().replaceAll("</?[a-z][a-z0-9]*[^<>]*>", "").replaceAll(" ","");
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){}
}
}
}
2013年12月6日 星期五
2013年5月26日 星期日
用主控台應用程式(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來測試比較方便!
至於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來測試比較方便!
2013年5月16日 星期四
ASP截取氣象局V7網頁, 示範抓取台中市的最新預報氣候及溫度
我們的需求是截取這部分訊息Include到EIP的首頁上
<%
'--取得<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>("°ree&")")
if cktime = TimeStamp then '確定已更新才存檔
Set NewFile=FSO.CreateTextFile(whichfile,True)
NewFile.Write (weather&"<br>("°ree&")")
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
%>
'--取得<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>("°ree&")")
if cktime = TimeStamp then '確定已更新才存檔
Set NewFile=FSO.CreateTextFile(whichfile,True)
NewFile.Write (weather&"<br>("°ree&")")
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
%>
2013年5月6日 星期一
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
2012年11月29日 星期四
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發送
2012年11月28日 星期三
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
把上面這個例子再簡化說明清楚一點
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
2012年11月25日 星期日
ASP表單中關於Enter鍵的控管
處理方式的其中兩種 :
1. 將所有input的Enter鍵模擬成預設按鈕觸發(適用於查詢頁面)
1. 將所有input的Enter鍵模擬成預設按鈕觸發(適用於查詢頁面)
<script>//menphis 2012.11.26 setting default click button(參考自訊光公司網頁)$(document).ready(function() {$("input").bind("keydown", function(event) {// track enter keyvar keycode = (event.keyCode ? event.keyCode : (event.which ? event.which : event.charCode));if (keycode == 13) { // keycode for enter keydocument.getElementById('Mybutton').click();return false;} else {return true;}}); // end of function}); // end of document ready</script>
2.將input的Enter鍵模擬成Tab鍵(適用於表單內容維護)
所有Input的td中增加
onkeydown="MyKeyDown();"
並增加以下Function
function
MyKeyDown(){
if(event.keyCode==13)
event.keyCode=9;
}
訂閱：
文章 (Atom)