using System;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.Threading.Tasks;
|
using System.Windows.Forms;
|
using Newtonsoft.Json;
|
using Newtonsoft.Json.Linq;
|
|
namespace MesMessage
|
{
|
public partial class MesMessageList : Form
|
{
|
private DBHelper oCN = new DBHelper();
|
|
// 钉钉配置（可根据需要改为从配置文件读取）
|
private string appKey = "dingkdddbhdcssk7jduw"; // 斯莫尔
|
private string appSecret = "iv07c-GLfJPnzfJaNAAOfJDl3Z-eODvDAhlInMZCZhGorkle5Evbaxx3ImylvdjQ";
|
//private string appKey = "dingrsrzhdyn3mlaof95"; // 本地
|
//private string appSecret = "RAqH6YtZnPLCpDbuqfaYQkKkVtVdS0wqfC8I26X6qiS-8eoCJCNrzx3fubGND4Sq";
|
//private string appKey = "ding8hkfpspnpoteenk3"; // 九菱
|
//private string appSecret = "-AwplMi-44dA39rNXUiBvDAQl_oAhhrBRQmMtDvSkN44biLh8C1Gb2MCtyzxJ6uF";
|
|
// 防止同一定时器重入的标志（分别控制两个任务）
|
private bool isFeedbackExecuting = false;
|
private bool isParamAlertExecuting = false;
|
|
public MesMessageList()
|
{
|
InitializeComponent();
|
|
// 绑定定时器事件并启动（Interval 请在设计器中设置：timer1=60000，timer2=300000）
|
timer1.Tick += Timer1_Tick;
|
timer2.Tick += Timer2_Tick;
|
timer1.Start();
|
timer2.Start();
|
}
|
|
/// <summary>
|
/// 异常反馈单处理任务
|
/// </summary>
|
private async Task ExecuteFeedbackTaskAsync()
|
{
|
try
|
{
|
// 获取系统参数（决定使用APP还是钉钉）
|
string rule = GetSystemRule();
|
if (string.IsNullOrEmpty(rule))
|
{
|
DBHelper.CustomWriteLog("未获取到系统参数 OA_AndengFlowRule_XT", DateTime.Now.ToString("yyyy-MM-dd"));
|
return;
|
}
|
|
//查询异常反馈单要发送信息的人
|
string sql = @"select distinct a.HInterID, b.HBillType, a.HReceiveMan 接收人,
|
c.HDingDingUserID 钉钉id, b.HDescription 内容
|
from OA_ErrMsgBackBillSub2 a
|
inner join OA_ErrMsgBackBillMain b on a.HInterID = b.HInterID
|
left join Gy_Czygl c on a.HReceiveMan = c.Czymc
|
where HSendFlag = '0'
|
order by a.HInterID";
|
|
DataSet dsFeedback = oCN.RunProcReturn(sql, "OA_ErrMsgBackBillSub2");
|
if (dsFeedback == null || dsFeedback.Tables.Count == 0 || dsFeedback.Tables[0].Rows.Count == 0)
|
return;
|
|
// 添加系统参数判断是钉钉/APP
|
if (rule == "APP")
|
{
|
for (int i = 0; i < dsFeedback.Tables[0].Rows.Count; i++)
|
{
|
string HName = dsFeedback.Tables[0].Rows[i]["接收人"].ToString();
|
// 原控制台程序中此处使用 dt.Tables[0].Rows[0]["HInterID"]，可能存在逻辑问题，但按原样保留
|
string HInterID = dsFeedback.Tables[0].Rows[0]["HInterID"].ToString();
|
DBHelper.CustomWriteLog($"exec h_p_CreateOAWorkLinkBill_Andon {HInterID},'{HName}'", DateTime.Now.ToString("yyyy-MM-dd"));
|
var ds = oCN.RunProcReturn($"exec h_p_CreateOAWorkLinkBill_Andon {HInterID},'{HName}'", "h_p_CreateOAWorkLinkBill_Andon");
|
if (ds != null && ds.Tables[0].Rows.Count > 0)
|
{
|
// 第一步：先获取字符串并判断是否为"0"，是则直接跳过
|
int newHInterID = int.Parse(ds.Tables[0].Rows[0]["HInterID"].ToString());
|
if (newHInterID != 0)
|
{
|
DBHelper.CustomWriteLog($"执行消息推送...{newHInterID}", DateTime.Now.ToString("yyyy-MM-dd"));
|
GeTuiService.PubishSingle(newHInterID); // 同步方法，若耗时较长可考虑异步优化
|
}
|
}
|
}
|
}
|
else if (rule == "钉钉")
|
{
|
MSG msg = new MSG();
|
// 获取企业的access_token的值
|
string tokenResponse = msg.GetAccessToken(appKey, appSecret);
|
JObject tokenJson = JObject.Parse(tokenResponse);
|
// 获取access_token的值
|
string accessToken = tokenJson["accessToken"].ToString();
|
// 调用方法发送消息
|
// string agentId = "3118119317";//钉钉后台建立的小程序id
|
// 斯莫尔
|
string agentId = "3151454458";//钉钉后台建立的小程序id
|
// 九菱
|
//string agentId = "3910708781";//钉钉后台建立的小程序id
|
|
for (int i = 0; i < dsFeedback.Tables[0].Rows.Count; i++)
|
{
|
string HContext = dsFeedback.Tables[0].Rows[i]["内容"].ToString();
|
string HName = dsFeedback.Tables[0].Rows[i]["钉钉id"].ToString();
|
string Stares = dsFeedback.Tables[0].Rows[i]["接收人"].ToString();
|
|
// 调用方法发送消息
|
string response = await msg.SendTextMessage(accessToken, agentId, HName, "0", "false", HContext);
|
ResponseData responseData = JsonConvert.DeserializeObject<ResponseData>(response);
|
if (responseData.errcode == 0)
|
{
|
// 更新状态
|
string sql1 = $"update OA_ErrMsgBackBillSub2 set HSendFlag='1' where HReceiveMan in ('{Stares}')";
|
oCN.RunProc(sql1);
|
DBHelper.CustomWriteLog($"钉钉返回信息:{response} 更新子表语句:{sql1}", DateTime.Now.ToString("yyyy-MM-dd"));
|
}
|
else
|
{
|
DBHelper.CustomWriteLog($"钉钉返回错误码:{responseData.errcode}", DateTime.Now.ToString("yyyy-MM-dd"));
|
DBHelper.CustomWriteLog($"钉钉返回信息:{response}", DateTime.Now.ToString("yyyy-MM-dd"));
|
}
|
}
|
}
|
}
|
catch (Exception ex)
|
{
|
DBHelper.CustomWriteLog($"ExecuteFeedbackTaskAsync 异常：{ex}", DateTime.Now.ToString("yyyy-MM-dd"));
|
}
|
}
|
|
/// <summary>
|
/// 异常工艺参数预警处理任务
|
/// </summary>
|
private async Task ExecuteParamAlertTaskAsync()
|
{
|
try
|
{
|
// 获取系统参数
|
string rule = GetSystemRule();
|
if (string.IsNullOrEmpty(rule))
|
{
|
DBHelper.CustomWriteLog("未获取到系统参数 OA_AndengFlowRule_XT", DateTime.Now.ToString("yyyy-MM-dd"));
|
return;
|
}
|
|
#region 异常工艺参数预警（每 1 分钟执行一次，但定时器为1分钟，所以内部判断时间差）
|
DBHelper.CustomWriteLog("开始进入执行循环 :", DateTime.Now.ToString("yyyy-MM-dd"));
|
|
#endregion
|
|
#region 异常工艺参数预警处理（每分钟执行）
|
// 查询出要更新的异常参数（最近1分钟内的未发送数据）
|
string Sql = "select HItemID from Sb_EquipMentCollectionTechParam_ERR where HCreateTime>=DATEADD(minute, - 1, GETDATE()) AND HSendFlag = 0";
|
DBHelper.CustomWriteLog("查询出异常的参数:" + Sql, DateTime.Now.ToString("yyyy-MM-dd"));
|
DataSet dsErr = oCN.RunProcReturn(Sql, "Sb_EquipMentCollectionTechParam_ERR");
|
if (dsErr != null && dsErr.Tables[0].Rows.Count > 0)
|
{
|
List<string> itemIds = new List<string>();
|
foreach (DataRow row in dsErr.Tables[0].Rows)
|
{
|
itemIds.Add(row["HItemID"].ToString());
|
}
|
DBHelper.CustomWriteLog("返回的异常参数集合：" + string.Join(", ", itemIds), DateTime.Now.ToString("yyyy-MM-dd"));
|
|
DBHelper.CustomWriteLog("要进入更新的语句", DateTime.Now.ToString("yyyy-MM-dd"));
|
// 更新这些记录的发送标志
|
for (int i = 0; i < dsErr.Tables[0].Rows.Count; i++)
|
{
|
string ycSql = "update Sb_EquipMentCollectionTechParam_ERR set HSendFlag = 1 where HItemID = " + dsErr.Tables[0].Rows[i]["HItemID"].ToString();
|
DBHelper.CustomWriteLog("钉钉异常预警更新语句:" + ycSql, DateTime.Now.ToString("yyyy-MM-dd"));
|
oCN.RunProc(ycSql);
|
}
|
|
// 添加系统参数判断是钉钉/APP
|
if (rule == "钉钉")
|
{
|
// 查询出要发信息的异常参数
|
string newSql = @"SELECT 工序名称, STUFF((
|
SELECT DISTINCT '; ' + 设备编码 + ' - ' + 工艺参数名称 + ' - 数采值: ' + CAST(数采值 AS VARCHAR(10))
|
+ ' - 上限值: ' + CAST(上限值 AS VARCHAR(10)) + ' - 下限值: ' + CAST(下限值 AS VARCHAR(10))
|
FROM h_v_Sb_EquipMentCollectionTechParam_ERRList AS InnerTable
|
WHERE InnerTable.工序名称 = OuterTable.工序名称
|
AND 发现异常时间 >= DATEADD(minute, -1, GETDATE())
|
FOR XML PATH('')), 1, 2, '') AS 内容
|
FROM h_v_Sb_EquipMentCollectionTechParam_ERRList AS OuterTable
|
GROUP BY 工序名称";
|
DBHelper.CustomWriteLog("查询出要发信息的异常参数" + newSql, DateTime.Now.ToString("yyyy-MM-dd"));
|
DataSet dsContent = oCN.RunProcReturn(newSql, "h_v_Sb_EquipMentCollectionTechParam_ERRList");
|
|
for (int j = 0; j < dsContent.Tables[0].Rows.Count; j++)
|
{
|
DBHelper.CustomWriteLog("异常内容数量 " + dsContent.Tables[0].Rows.Count, DateTime.Now.ToString("yyyy-MM-dd"));
|
string HContext = dsContent.Tables[0].Rows[j]["内容"].ToString();
|
string HDeptName = dsContent.Tables[0].Rows[j]["工序名称"].ToString();
|
DBHelper.CustomWriteLog("异常内容值 " + HContext, DateTime.Now.ToString("yyyy-MM-dd"));
|
DBHelper.CustomWriteLog("异常车间值 " + HDeptName, DateTime.Now.ToString("yyyy-MM-dd"));
|
if (!string.IsNullOrEmpty(HContext) && HContext.ToLower() != "null")
|
{
|
DBHelper.CustomWriteLog("查询出要发信息的内容" + HContext, DateTime.Now.ToString("yyyy-MM-dd"));
|
// 查询出要发送的人
|
string newSql1 = $@"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("查询出要发送的人:" + newSql1, DateTime.Now.ToString("yyyy-MM-dd"));
|
DataSet dsUsers = oCN.RunProcReturn(newSql1, "Gy_Czygl");
|
DBHelper.CustomWriteLog("开始进入发信息", DateTime.Now.ToString("yyyy-MM-dd"));
|
if (dsUsers.Tables[0].Rows.Count > 0)
|
{
|
DBHelper.CustomWriteLog("进入发信息", DateTime.Now.ToString("yyyy-MM-dd"));
|
MSG msg = new MSG();
|
// 获取企业的access_token的值
|
string tokenResponse = msg.GetAccessToken(appKey, appSecret);
|
JObject tokenJson = JObject.Parse(tokenResponse);
|
string accessToken = tokenJson["accessToken"].ToString();
|
string agentId = "3151454458"; // 斯莫尔
|
|
for (int i = 0; i < dsUsers.Tables[0].Rows.Count; i++)
|
{
|
string HName = dsUsers.Tables[0].Rows[i]["钉钉id"].ToString();
|
string Stares = dsUsers.Tables[0].Rows[i]["接收人"].ToString();
|
|
// 调用方法发送消息
|
string response = await msg.SendTextMessage(accessToken, agentId, HName, "0", "false", HContext);
|
ResponseData responseData = JsonConvert.DeserializeObject<ResponseData>(response);
|
if (responseData.errcode == 0)
|
{
|
DBHelper.CustomWriteLog("钉钉异常预警发送成功", DateTime.Now.ToString("yyyy-MM-dd"));
|
}
|
else
|
{
|
DBHelper.CustomWriteLog($"钉钉返回错误码:{responseData.errcode}", DateTime.Now.ToString("yyyy-MM-dd"));
|
DBHelper.CustomWriteLog($"钉钉返回信息:{response}", DateTime.Now.ToString("yyyy-MM-dd"));
|
}
|
}
|
}
|
}
|
}
|
}
|
else if (rule == "APP")
|
{
|
// 查询出要发信息的异常参数
|
string newSql = @"SELECT 工序名称, STUFF((
|
SELECT DISTINCT '; ' + 设备编码 + ' - ' + 工艺参数名称 + ' - 数采值: ' + CAST(数采值 AS VARCHAR(10))
|
+ ' - 上限值: ' + CAST(上限值 AS VARCHAR(10)) + ' - 下限值: ' + CAST(下限值 AS VARCHAR(10))
|
FROM h_v_Sb_EquipMentCollectionTechParam_ERRList AS InnerTable
|
WHERE InnerTable.工序名称 = OuterTable.工序名称
|
AND 发现异常时间 >= DATEADD(minute, -1, GETDATE())
|
FOR XML PATH('')), 1, 2, '') AS 内容
|
FROM h_v_Sb_EquipMentCollectionTechParam_ERRList AS OuterTable
|
GROUP BY 工序名称";
|
|
DataSet dsContent = oCN.RunProcReturn(newSql, "h_v_Sb_EquipMentCollectionTechParam_ERRList");
|
|
for (int j = 0; j < dsContent.Tables[0].Rows.Count; j++)
|
{
|
DBHelper.CustomWriteLog("异常内容数量 " + dsContent.Tables[0].Rows.Count, DateTime.Now.ToString("yyyy-MM-dd"));
|
string HContext = dsContent.Tables[0].Rows[j]["内容"].ToString();
|
string HDeptName = dsContent.Tables[0].Rows[j]["工序名称"].ToString();
|
if (!string.IsNullOrEmpty(HContext) && HContext.ToLower() != "null")
|
{
|
// 查询出要发送的人
|
string newSql1 = $@"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}工艺异常预警接收人'";
|
DataSet dsUsers = oCN.RunProcReturn(newSql1, "Gy_Czygl");
|
if (dsUsers.Tables[0].Rows.Count > 0)
|
{
|
|
for (int i = 0; i < dsUsers.Tables[0].Rows.Count; i++)
|
{
|
string HName = dsUsers.Tables[0].Rows[i]["钉钉id"].ToString();
|
|
var ds = oCN.RunProcReturn($"exec h_p_OA_WorkLinkBill_TechParam_ERR_Create '{HContext}','{HName}'", "h_p_OA_WorkLinkBill_TechParam_ERR_Create");
|
if (ds != null && ds.Tables[0].Rows.Count > 0)
|
{
|
// 第一步：先获取字符串并判断是否为"0"，是则直接跳过
|
int newHInterID = int.Parse(ds.Tables[0].Rows[0]["HInterID"].ToString());
|
if (newHInterID != 0)
|
{
|
DBHelper.CustomWriteLog($"执行消息推送...{newHInterID}", DateTime.Now.ToString("yyyy-MM-dd"));
|
GeTuiService.PubishSingle(newHInterID); // 同步方法，若耗时较长可考虑异步优化
|
}
|
}
|
}
|
}
|
}
|
}
|
}
|
}
|
#endregion
|
}
|
catch (Exception ex)
|
{
|
DBHelper.CustomWriteLog($"ExecuteParamAlertTaskAsync 异常：{ex}", DateTime.Now.ToString("yyyy-MM-dd"));
|
}
|
}
|
|
/// <summary>
|
/// 获取系统参数 OA_AndengFlowRule_XT 的值
|
/// </summary>
|
private string GetSystemRule()
|
{
|
string sql = "select HValue from xt_SystemParameter where HKey='OA_AndengFlowRule_XT'";
|
DataSet ds = oCN.RunProcReturn(sql, "xt_SystemParameter");
|
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
|
return ds.Tables[0].Rows[0]["HValue"].ToString();
|
return null;
|
}
|
|
// 定时器1 Tick 事件（异常反馈单）
|
private async void Timer1_Tick(object sender, EventArgs e)
|
{
|
if (isFeedbackExecuting) return;
|
isFeedbackExecuting = true;
|
try
|
{
|
await ExecuteFeedbackTaskAsync();
|
}
|
finally
|
{
|
isFeedbackExecuting = false;
|
}
|
}
|
|
// 定时器2 Tick 事件（异常工艺参数预警）
|
private async void Timer2_Tick(object sender, EventArgs e)
|
{
|
if (isParamAlertExecuting) return;
|
isParamAlertExecuting = true;
|
try
|
{
|
await ExecuteParamAlertTaskAsync();
|
}
|
finally
|
{
|
isParamAlertExecuting = false;
|
}
|
}
|
}
|
|
// 原 Program.cs 中的 ResponseData 类
|
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; }
|
}
|
}
|
