protected void AlterDatabase(sdv7DataContext ctx)
{
// 构建索引,以提速查询过期许可证
string sqlCmd = "IF NOT EXISTS(SELECT * FROM sys.sysindexes WHERE name = 'idx_Gsp_shouying_qyshb__youxiao_rq_xk')\n" +
"\tCREATE INDEX idx_Gsp_shouying_qyshb__youxiao_rq_xk ON Gsp_shouying_qyshb(youxiao_rq_xk)";
ctx.ExecuteCommand(sqlCmd);
// 创建winphone schema,隔离本应用专用数据
sqlCmd = "IF(SCHEMA_ID(N'winphone') IS NULL) EXEC sp_executesql N'CREATE SCHEMA winphone'";
ctx.ExecuteCommand(sqlCmd);
// 创建winphone.wx_notify表,记录成功发送的微信消息
sqlCmd = "IF OBJECT_ID(N'winphone.wx_notify', N'U') IS NULL\n" +
"CREATE TABLE winphone.wx_notify(\n" +
"md5 CHAR(32) NOT NULL,\n" +
"openid CHAR(28) NOT NULL,\n" +
"tm SMALLDATETIME DEFAULT GetDate(),\n" +
"CONSTRAINT PK_WX_NOTIFY primary key (md5, openid))";
ctx.ExecuteCommand(sqlCmd);
// 删除一年以前的旧记录
DateTime tmOneYearBefore = DateTime.Today - TimeSpan.FromDays(365.0);
sqlCmd = "DELETE FROM winphone.wx_notify WHERE tm < {0}";
ctx.ExecuteCommand(sqlCmd, tmOneYearBefore);
}
public void Check()
{
// 当前时间
this.DateExpired = DateTime.Today;
// 读取配置参数,提前N天预警
try{
string strPreAlarmDays = ConfigurationManager.AppSettings["preAlarmDays"];
this.PreAlarmDays = Int32.Parse(strPreAlarmDays);
}
catch (Exception ex) {
EventLog.WriteEntry(MainVM.Name, "读取配置项preAlarmDays失败\r\n" + ex.ToString(), EventLogEntryType.Warning);
}
this.DateAlarm = this.DateExpired + TimeSpan.FromDays(this.PreAlarmDays);
using (var ctx = new sdv7DataContext(ConfigurationManager.ConnectionStrings["sdv7"].ConnectionString))
{
// index
this.AlterDatabase(ctx);
// query
var query = from c in ctx.GetTable <Gsp_shouying_qyshb>()
where c.youxiao_rq_xk <= this.DateAlarm
orderby c.youxiao_rq_xk
select c;
this.AlarmedList = query.ToList();
// top 10 not expired or alarmed
var queryNext10 = from c in ctx.GetTable <Gsp_shouying_qyshb>()
where c.youxiao_rq_xk >= this.DateAlarm
orderby c.youxiao_rq_xk
select c;
var listNext10 = queryNext10.Take(10).ToList();
this.AlarmedList = this.AlarmedList.Concat(listNext10);
}
}
public void Notify(IEnumerable <Gsp_shouying_qyshb> listAlarms)
{
// 免打扰时间不发送消息
if (WechatConfigSection.Current.DoNotDisturb())
{
return;
}
if (listAlarms != null && listAlarms.Count() > 0)
{
string strTitle = "许可证即将到期";
var sbContent = new StringBuilder();
var sbRemark = new StringBuilder();
foreach (var x in listAlarms)
{
if (x.IsExpired)
{
sbContent.AppendFormat("{0}[{1:yyyy-MM-dd}]\n", x.mingcheng, x.youxiao_rq_xk);
strTitle = "许可证已到期";
}
else if (x.IsAlarmed)
{
if (sbRemark.Length == 0)
{
sbRemark.AppendLine("\n以下许可证也即将到期:");
}
sbRemark.AppendFormat("{0}[{1:yyyy-MM-dd}]\n", x.mingcheng, x.youxiao_rq_xk);
}
}
try
{
var client = new HttpClient();
// 1. Get the AccessToken
var uriToken = new Uri("http://dev.incardata.com.cn/srv/s/1/AccessToken");
client.GetStringAsync(uriToken).ContinueWith((taskToken) =>
{
var token = JsonConvert.DeserializeObject(taskToken.Result) as JObject;
var uriMsg = String.Format("https://api.weixin.qq.com/cgi-bin/message/template/send?access_token={0}", token.GetValue("token"));
var wechatCfg = WechatConfigSection.Current;
var templateId = wechatCfg.NotifyTemplateId;
using (var ctx = new sdv7DataContext(ConfigurationManager.ConnectionStrings["sdv7"].ConnectionString))
{
foreach (WechatUser usr in wechatCfg.Users)
{
// 2. Prepare message
var dataMsg = JsonConvert.SerializeObject(new
{
touser = usr.OpenId, // XGH
template_id = templateId,
url = "",
topcolor = "#FF7700",
data = new
{
first = new { value = strTitle, color = "#FF3333" },
content = new { value = sbContent.ToString(), color = "#FF3333" },
occurtime = new { value = DateTime.Today.ToString("yyyy年M月d日"), color = "#FF3333" },
remark = new { value = sbRemark.ToString(), color = "#FF7700" },
}
});
// 3. Check if send already
var md5 = MakeMD5(dataMsg);
var querySendAlready = from log in ctx.GetTable <wx_notify>()
where log.md5 == md5 && log.openid == usr.OpenId
select log;
if (querySendAlready.Any())
{
continue;
}
// 4. Send out
client.PostAsync(uriMsg, new StringContent(dataMsg)).ContinueWith((taskMsg) =>
{
taskMsg.Result.Content.ReadAsStringAsync().ContinueWith((taskSendResult) =>
{
if (taskSendResult.Result != null)
{
var sent = JsonConvert.DeserializeObject(taskSendResult.Result) as JObject;
if (sent.Value <int>("errcode") == 0)
{
// 5. Log it
using (var ctx2 = new sdv7DataContext(ConfigurationManager.ConnectionStrings["sdv7"].ConnectionString))
{
var log = new wx_notify()
{
md5 = md5,
openid = usr.OpenId,
tm = DateTime.Today
};
ctx2.GetTable <wx_notify>().InsertOnSubmit(log);
ctx2.SubmitChanges();
}
}
}
});
});
}
}
});
}
catch (Exception ex)
{
System.Diagnostics.Trace.WriteLine(ex.ToString());
}
}
}