using Newtonsoft.Json;
|
using Newtonsoft.Json.Linq;
|
using System;
|
using System.Data;
|
using System.Threading.Tasks;
|
|
namespace DingDingMsg_EarlyWarning
|
{
|
class Program
|
{
|
//异常反馈单,异常工艺参数预警 发送钉钉消息
|
static DBHelper oCN = new DBHelper();
|
static DataSet dt = new DataSet();
|
static DataSet dts = new DataSet();
|
static async Task Main(string[] args)
|
{
|
//本地
|
//string appKey = "dingrsrzhdyn3mlaof95";
|
//string appSecret = "RAqH6YtZnPLCpDbuqfaYQkKkVtVdS0wqfC8I26X6qiS-8eoCJCNrzx3fubGND4Sq";
|
//斯莫尔
|
string appKey = "dingkdddbhdcssk7jduw";
|
string appSecret = "iv07c-GLfJPnzfJaNAAOfJDl3Z-eODvDAhlInMZCZhGorkle5Evbaxx3ImylvdjQ";
|
Console.WriteLine("程序2正在执行,请不要关闭!!!!");
|
string HDeptName = "";
|
string HWarningDate = "10";
|
string HRecordDate = DateTime.MinValue.ToString();
|
string HRecordDate_spc = "";
|
// 记录上次执行新定时任务的时间
|
while (true)
|
{
|
//查找设备保养预警
|
string sql = "SELECT a.HInterID,b.HEntryID,c.HEquipFileNo 设备编码, c.HName 设备名称,d.HName 设备部门 FROM Sb_EquipMaintainPlanBillMain a " +
|
"INNER JOIN Sb_EquipMaintainPlanBillSub_Plan b ON a.HInterID = b.HInterID " +
|
"LEFT JOIN Gy_EquipFileBillMain c ON a.HEquipID = c.HInterID " +
|
"LEFT JOIN Gy_Department d ON a.HDeptID = d.HItemID " +
|
" WHERE CONVERT(date, b.HErrBeginTime, 120) = CONVERT(date, GETDATE(), 120) and HSendFlag = '0'";
|
DBHelper.CustomWriteLog("查询设备保养计划:" + sql, DateTime.Now.ToString("yyyy-MM-dd"));
|
dt = oCN.RunProcReturn(sql, "Sb_EquipMaintainPlanBillMain");
|
//设备保养预警自动发信息
|
if (dt.Tables[0].Rows.Count > 0)
|
{
|
string HContext = "";
|
|
for (int i = 0; i < dt.Tables[0].Rows.Count; i++)
|
{
|
HContext = "设备编码为:" + dt.Tables[0].Rows[i]["设备编码"].ToString() + ",设备名称为:" + dt.Tables[0].Rows[i]["设备名称"].ToString() + ",已到保养预警日期,需尽快进行保养";
|
//更新设备保养计划预警 子表
|
string sql1 = "update Sb_EquipMaintainPlanBillSub_Plan set HSendFlag='1' where HInterID=" + dt.Tables[0].Rows[i]["HInterID"].ToString() + " and HEntryID=" + dt.Tables[0].Rows[i]["HEntryID"].ToString() + ""; ;
|
oCN.RunProc(sql1);
|
DBHelper.CustomWriteLog("设备保养计划预警:" + sql1 + " 更新子表语句:" + sql1, DateTime.Now.ToString("yyyy-MM-dd"));
|
HDeptName = dt.Tables[0].Rows[i]["设备部门"].ToString();
|
string sql2 = "select a.HDingDingUserID 钉钉id,a.Czymc 接收人 from Gy_Czygl a left join System_UserGroupInfo b on a.Czybm = b.UserId" +
|
" left join System_UserGroup c on b.GroupId = c.GroupID where c.GroupName = '" + HDeptName + "设备保养计划预警接收人'";
|
DBHelper.CustomWriteLog("设备保养计划预警接收人:" + sql2, DateTime.Now.ToString("yyyy-MM-dd"));
|
dts = oCN.RunProcReturn(sql2, "Gy_Czygl");
|
for (int j = 0; j < dts.Tables[0].Rows.Count; j++)
|
{
|
string HName = dts.Tables[0].Rows[j]["钉钉id"].ToString();
|
string Stares = dts.Tables[0].Rows[j]["接收人"].ToString();
|
MSG msg = new MSG();
|
//获取企业的access_token的值
|
string response = msg.GetAccessToken(appKey, appSecret);
|
JObject responseJson = JObject.Parse(response);
|
// 获取access_token的值
|
string accessToken = responseJson["accessToken"].ToString();
|
|
//调用方法发送消息
|
//string agentId = "3118119317";//钉钉后台建立的小程序id
|
//斯莫尔
|
string agentId = "3151454458";//钉钉后台建立的小程序id
|
string userIdList = HName;//钉钉人员的id
|
string deptIdList = "0"; // 空字符串表示不指定部门
|
string toAllUser = "false";
|
string message = HContext;
|
|
// 调用方法发送消息
|
response = await msg.SendTextMessage(accessToken, agentId, userIdList, deptIdList, toAllUser, message);
|
ResponseData responseData = JsonConvert.DeserializeObject<ResponseData>(response);
|
if (responseData.errcode == 0)
|
{
|
DBHelper.CustomWriteLog("钉钉发送信息成功!", DateTime.Now.ToString("yyyy-MM-dd"));
|
}
|
else
|
{
|
Console.WriteLine(responseData.errcode);
|
DBHelper.CustomWriteLog("钉钉返回信息:" + response, DateTime.Now.ToString("yyyy-MM-dd"));
|
}
|
}
|
}
|
}
|
|
//查找模具保养预警
|
string newsql = "SELECT a.HInterID,b.HEntryID,c.HMouldNo 模具编码, c.HName 模具名称,B.HErrBeginQty,HSendFlag,d.HName 模具部门 FROM Sc_MouldMaintainPlanBillMain a " +
|
"INNER JOIN Sc_MouldMaintainPlanBillSub_Plan b ON a.HInterID = b.HInterID " +
|
"LEFT JOIN Gy_MouldFileMain c ON a.HMouldID = c.HInterID " +
|
"LEFT JOIN Gy_Department d ON a.HDeptID = d.HItemID " +
|
" WHERE b.HErrBeginQty <= (select b.HUseNowQty+sum(HUseLife) from Sc_MouldLifeUseBillSub WHERE HMaterID = a.HMouldID) and b.HSendFlag = '0'";
|
DBHelper.CustomWriteLog("查询模具保养计划:" + newsql, DateTime.Now.ToString("yyyy-MM-dd"));
|
dt = oCN.RunProcReturn(newsql, "Sb_EquipMaintainPlanBillMain");
|
//模具保养预警自动发信息
|
if (dt.Tables[0].Rows.Count > 0)
|
{
|
string HContext = "";
|
|
for (int i = 0; i < dt.Tables[0].Rows.Count; i++)
|
{
|
HContext = "模具编码为:" + dt.Tables[0].Rows[i]["模具编码"].ToString() + ",模具名称为:" + dt.Tables[0].Rows[i]["模具名称"].ToString() + ",已到保养预警次数,需尽快进行保养";
|
//更新模具保养计划预警 子表
|
string sql1 = "update Sc_MouldMaintainPlanBillSub_Plan set HSendFlag='1' where HInterID=" + dt.Tables[0].Rows[i]["HInterID"].ToString() + " and HEntryID=" + dt.Tables[0].Rows[i]["HEntryID"].ToString() + ""; ;
|
oCN.RunProc(sql1);
|
DBHelper.CustomWriteLog("模具保养计划预警:" + sql1 + " 更新子表语句:" + sql1, DateTime.Now.ToString("yyyy-MM-dd"));
|
HDeptName = dt.Tables[0].Rows[i]["模具部门"].ToString();
|
string sql2 = "select a.HDingDingUserID 钉钉id,a.Czymc 接收人 from Gy_Czygl a left join System_UserGroupInfo b on a.Czybm = b.UserId" +
|
" left join System_UserGroup c on b.GroupId = c.GroupID where c.GroupName = '模具保养计划预警接收人'";
|
DBHelper.CustomWriteLog("模具保养计划预警接收人:" + sql2, DateTime.Now.ToString("yyyy-MM-dd"));
|
dts = oCN.RunProcReturn(sql2, "Gy_Czygl");
|
for (int j = 0; j < dts.Tables[0].Rows.Count; j++)
|
{
|
string HName = dts.Tables[0].Rows[j]["钉钉id"].ToString();
|
string Stares = dts.Tables[0].Rows[j]["接收人"].ToString();
|
MSG msg = new MSG();
|
//获取企业的access_token的值
|
string response = msg.GetAccessToken(appKey, appSecret);
|
JObject responseJson = JObject.Parse(response);
|
// 获取access_token的值
|
string accessToken = responseJson["accessToken"].ToString();
|
|
//调用方法发送消息
|
//string agentId = "3118119317";//钉钉后台建立的小程序id
|
//斯莫尔
|
string agentId = "3151454458";//钉钉后台建立的小程序id
|
string userIdList = HName;//钉钉人员的id
|
string deptIdList = "0"; // 空字符串表示不指定部门
|
string toAllUser = "false";
|
string message = HContext;
|
|
// 调用方法发送消息
|
response = await msg.SendTextMessage(accessToken, agentId, userIdList, deptIdList, toAllUser, message);
|
ResponseData responseData = JsonConvert.DeserializeObject<ResponseData>(response);
|
if (responseData.errcode == 0)
|
{
|
DBHelper.CustomWriteLog("钉钉发送信息成功!", DateTime.Now.ToString("yyyy-MM-dd"));
|
}
|
else
|
{
|
Console.WriteLine(responseData.errcode);
|
DBHelper.CustomWriteLog("钉钉返回信息:" + response, DateTime.Now.ToString("yyyy-MM-dd"));
|
}
|
}
|
}
|
}
|
|
|
DBHelper.CustomWriteLog("设备采集异常开始!", DateTime.Now.ToString("yyyy-MM-dd"));
|
//设备采集异常时（开工后，设备持续10分钟未采集），进行消息预警
|
//获取 为预警 并且状态为开工的 设备 和工艺参数
|
dt = oCN.RunProcReturn("exec h_p_Sb_EquiMessageWarn", "h_p_Sb_EquiMessageWarn");
|
|
|
if (dt.Tables[0].Rows.Count > 0 && (DateTime.Now - DateTime.Parse(HRecordDate)).TotalMinutes >= int.Parse(HWarningDate))
|
{
|
string HEquipFileName = "";
|
string HRemark = "";
|
|
//为预警的设备 和 工艺参数 进行循环
|
for (int i = 0; i < dt.Tables[0].Rows.Count; i++)
|
{
|
HRecordDate = dt.Tables[0].Rows[i]["HRecordDate"].ToString();//记录时间
|
HWarningDate = dt.Tables[0].Rows[i]["HWarningDate"].ToString();//预警时间
|
string HCollectionDate = dt.Tables[0].Rows[i]["HCollectionDate"].ToString();//采集时间
|
string HEquipFileNo = dt.Tables[0].Rows[i]["HEquipFileNo"].ToString();//设备编号
|
string HParameterName = dt.Tables[0].Rows[i]["HName"].ToString();//工艺参数
|
|
if ((DateTime.Now - DateTime.Parse(HRecordDate)).TotalMinutes >= int.Parse(HWarningDate))
|
{
|
//根据 设备+工艺参数+采集时间 查询工艺参数临时表的数据
|
DataSet ds = oCN.RunProcReturn("exec h_p_Sb_EquipMentTechParam_Temp '" + HEquipFileNo + "', '" + HParameterName + "', '" + HCollectionDate + "'", "h_p_Sb_EquipMentTechParam_Temp");
|
if (ds.Tables[0].Rows.Count > 0)
|
{
|
string HMaxCreateTime = ds.Tables[0].Rows[0]["HCreateTime"].ToString();
|
// 判断 当前时间-参数的创建时间 是否大于预警时间 如果 大于预警时间则需要钉钉发消息
|
if ((DateTime.Now - DateTime.Parse(HMaxCreateTime)).TotalMinutes >= int.Parse(HWarningDate))
|
{
|
if (HEquipFileName != HEquipFileNo)
|
{
|
HRemark += "设备编码:" + HEquipFileNo + "---";
|
HEquipFileName = HEquipFileNo;
|
}
|
|
DBHelper.CustomWriteLog("最大时间:" + HMaxCreateTime + "," + i, DateTime.Now.ToString("yyyy-MM-dd"));
|
//发送消息
|
HRemark += HParameterName + ",";
|
DBHelper.CustomWriteLog("更新时间:" + HEquipFileNo, DateTime.Now.ToString("yyyy-MM-dd"));
|
//更新记录时间
|
oCN.RunProc("update Gy_EquipFileBillMain set HRecordDate=getdate() where HEquipFileNo='" + HEquipFileNo + "'");
|
}
|
DBHelper.CustomWriteLog("设备采集异常开始---不需要预警!", DateTime.Now.ToString("yyyy-MM-dd"));
|
}
|
else
|
{
|
if (HEquipFileName != HEquipFileNo)
|
{
|
HRemark += "设备编码:" + HEquipFileNo + "---";
|
HEquipFileName = HEquipFileNo;
|
}
|
//发送消息
|
HRemark += HParameterName + ",";
|
//更新记录时间
|
oCN.RunProc("update Gy_EquipFileBillMain set HRecordDate=getdate() where HEquipFileNo='" + HEquipFileNo + "'");
|
}
|
}
|
}
|
|
HRecordDate = DateTime.Now.ToString();
|
|
if (HRemark.Length > 0)
|
{
|
HRemark += "没有采集到数据!";
|
|
// 根据 角色 去查询需要发消息的钉钉号
|
string sql2 = "select a.HDingDingUserID 钉钉id,a.Czymc 接收人 from Gy_Czygl a left join System_UserGroupInfo b on a.Czybm = b.UserId" +
|
" left join System_UserGroup c on b.GroupId = c.GroupID where c.GroupName = '参数预警'";
|
dts = oCN.RunProcReturn(sql2, "Gy_Czygl");
|
for (int j = 0; j < dts.Tables[0].Rows.Count; j++)
|
{
|
string HName = dts.Tables[0].Rows[j]["钉钉id"].ToString();
|
string Stares = dts.Tables[0].Rows[j]["接收人"].ToString();
|
MSG msg = new MSG();
|
//获取企业的access_token的值
|
string response = msg.GetAccessToken(appKey, appSecret);
|
JObject responseJson = JObject.Parse(response);
|
// 获取access_token的值
|
string accessToken = responseJson["accessToken"].ToString();
|
|
//调用方法发送消息
|
//string agentId = "3118119317";//钉钉后台建立的小程序id
|
//斯莫尔
|
string agentId = "3151454458";//钉钉后台建立的小程序id
|
string userIdList = HName;//钉钉人员的id
|
string deptIdList = "0"; // 空字符串表示不指定部门
|
string toAllUser = "false";
|
string message = HRemark;
|
|
// 调用方法发送消息
|
response = await msg.SendTextMessage(accessToken, agentId, userIdList, deptIdList, toAllUser, message);
|
ResponseData responseData = JsonConvert.DeserializeObject<ResponseData>(response);
|
if (responseData.errcode == 0)
|
{
|
DBHelper.CustomWriteLog("钉钉发送信息成功!", DateTime.Now.ToString("yyyy-MM-dd"));
|
}
|
else
|
{
|
Console.WriteLine(responseData.errcode);
|
DBHelper.CustomWriteLog("钉钉返回信息:" + response, DateTime.Now.ToString("yyyy-MM-dd"));
|
}
|
}
|
}
|
|
}
|
DBHelper.CustomWriteLog("设备采集异常结束!", DateTime.Now.ToString("yyyy-MM-dd"));
|
|
|
|
DBHelper.CustomWriteLog("spc异常发消息!", DateTime.Now.ToString("yyyy-MM-dd"));
|
//spc 异常表有未发消息的数据时 一小时 发送一次消息
|
//获取 异常表 有未发消息的数据
|
dt = oCN.RunProcReturn("select * from h_v_QC_SendMessageList", "h_v_QC_SendMessageList");
|
|
|
if (HRecordDate_spc == "")
|
{
|
HRecordDate_spc = DateTime.Now.ToString();
|
}
|
//根据30分钟时间 去发送消息
|
if (dt.Tables[0].Rows.Count > 0 && (DateTime.Now - DateTime.Parse(HRecordDate_spc)).TotalMinutes >= 30)
|
{
|
|
//为预警的设备 和 工艺参数 进行循环
|
for (int i = 0; i < dt.Tables[0].Rows.Count; i++)
|
{
|
string HMaterID = dt.Tables[0].Rows[i]["HMaterID"].ToString();
|
string HQCCheckItemID = dt.Tables[0].Rows[i]["HQCCheckItemID"].ToString();
|
//string HMaterNumber = dt.Tables[0].Rows[i]["物料代码"].ToString();
|
//string HQCCheckItemNumber = dt.Tables[0].Rows[i]["项目代码"].ToString();
|
string HMouldNum = dt.Tables[0].Rows[i]["穴号"].ToString();
|
string HRemark = dt.Tables[0].Rows[i]["错误数据"].ToString();
|
|
if (HRemark.Length > 0)
|
{
|
// 根据 角色 去查询需要发消息的钉钉号
|
string sql2 = "select a.HDingDingUserID 钉钉id,a.Czymc 接收人 from Gy_Czygl a left join System_UserGroupInfo b on a.Czybm = b.UserId" +
|
" left join System_UserGroup c on b.GroupId = c.GroupID where c.GroupName = 'SPC数据异常预警'";
|
dts = oCN.RunProcReturn(sql2, "Gy_Czygl");
|
for (int j = 0; j < dts.Tables[0].Rows.Count; j++)
|
{
|
string HName = dts.Tables[0].Rows[j]["钉钉id"].ToString();
|
string Stares = dts.Tables[0].Rows[j]["接收人"].ToString();
|
MSG msg = new MSG();
|
//获取企业的access_token的值
|
string response = msg.GetAccessToken(appKey, appSecret);
|
JObject responseJson = JObject.Parse(response);
|
// 获取access_token的值
|
string accessToken = responseJson["accessToken"].ToString();
|
|
//调用方法发送消息
|
//string agentId = "3118119317";//钉钉后台建立的小程序id
|
//斯莫尔
|
string agentId = "3151454458";//钉钉后台建立的小程序id
|
string userIdList = HName;//钉钉人员的id
|
string deptIdList = "0"; // 空字符串表示不指定部门
|
string toAllUser = "false";
|
string message = HRemark;
|
DBHelper.CustomWriteLog("spc异常 当前时间:"+ HRecordDate_spc, DateTime.Now.ToString("yyyy-MM-dd"));
|
|
// 调用方法发送消息
|
response = await msg.SendTextMessage(accessToken, agentId, userIdList, deptIdList, toAllUser, message);
|
ResponseData responseData = JsonConvert.DeserializeObject<ResponseData>(response);
|
if (responseData.errcode == 0)
|
{
|
DBHelper.CustomWriteLog("spc异常发息成功!", DateTime.Now.ToString("yyyy-MM-dd"));
|
string sql3 = "update QC_SendMessage set HMessageIsSend=1 where HMaterID=" + HMaterID + " and HQCCheckItemID=" + HQCCheckItemID + " and HMouldNum='" + HMouldNum + "'";
|
oCN.RunProc(sql3);
|
}
|
else
|
{
|
Console.WriteLine(responseData.errcode);
|
DBHelper.CustomWriteLog("spc异常返回信息:" + response, DateTime.Now.ToString("yyyy-MM-dd"));
|
}
|
}
|
|
HRecordDate_spc = DateTime.Now.ToString();
|
}
|
}
|
}
|
DBHelper.CustomWriteLog("spc异常发息结束!", DateTime.Now.ToString("yyyy-MM-dd"));
|
|
// 等待一分钟
|
await Task.Delay(TimeSpan.FromMinutes(1));
|
}
|
|
}
|
public class ResponseData
|
{
|
public int errcode { get; set; }
|
public string errmsg { get; set; }
|
public long task_id { get; set; }
|
public string request_id { get; set; }
|
}
|
}
|
}
|
