using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using System;
using System.Collections.Generic;
using System.Data;
using System.Threading.Tasks;

namespace DingDingMsg
{
    class Program
    {
        //异常反馈单,异常工艺参数预警   发送钉钉消息
        static DBHelper oCN = new DBHelper();
        static DataSet dt = new DataSet();
        static DataSet dt1 = new DataSet();
        static DataSet dt2 = new DataSet();
        static async Task Main(string[] args)
        {
            //本地
            //string appKey = "dingrsrzhdyn3mlaof95";
            //string appSecret = "RAqH6YtZnPLCpDbuqfaYQkKkVtVdS0wqfC8I26X6qiS-8eoCJCNrzx3fubGND4Sq";
            //斯莫尔
            //string appKey = "dingkdddbhdcssk7jduw";
            //string appSecret = "iv07c-GLfJPnzfJaNAAOfJDl3Z-eODvDAhlInMZCZhGorkle5Evbaxx3ImylvdjQ";
            //九菱
            string appKey = "ding8hkfpspnpoteenk3";
            string appSecret = "-AwplMi-44dA39rNXUiBvDAQl_oAhhrBRQmMtDvSkN44biLh8C1Gb2MCtyzxJ6uF";
            Console.WriteLine("安灯程序正在执行,请不要关闭!!!!");
            // 记录上次执行新定时任务的时间
            DateTime lastNewTaskExecution = DateTime.MinValue;
            while (true)
            {
                //查数据
                string sql = "select a.HInterID,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";
                dt = oCN.RunProcReturn(sql, "OA_ErrMsgBackBillSub2");
                //异常反馈单自动发信息
                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();
                        string HName = dt.Tables[0].Rows[i]["钉钉id"].ToString();
                        string Stares = dt.Tables[0].Rows[i]["接收人"].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 agentId = "3910708781";//钉钉后台建立的小程序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)
                        {
                            //更新状态
                            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
                        {
                            Console.WriteLine(responseData.errcode);
                            DBHelper.CustomWriteLog("钉钉返回信息:" + response, DateTime.Now.ToString("yyyy-MM-dd"));
                        }
                    }                                                                        
                }               

                //异常工艺参数预警（每 5 分钟执行一次）
                //DBHelper.CustomWriteLog("开始进入执行循环 :", DateTime.Now.ToString("yyyy-MM-dd"));
                //if ((DateTime.Now - lastNewTaskExecution).TotalMinutes >= 4)
                //{
                //    DBHelper.CustomWriteLog("已进入执行循环:", DateTime.Now.ToString("yyyy-MM-dd"));
                //    //查询出要更新已经发送的异常参数
                //    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"));
                //    dt = oCN.RunProcReturn(Sql, "Sb_EquipMentCollectionTechParam_ERR");                 
                //    if (dt != null && dt.Tables[0].Rows.Count > 0)
                //    {
                //        List<string> itemIds = new List<string>();
                //        foreach (DataRow row in dt.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"));
                //    if (dt.Tables[0].Rows.Count > 0)
                //    {
                //        DBHelper.CustomWriteLog("进入更新的语句", DateTime.Now.ToString("yyyy-MM-dd"));
                //        //更新要发送的单子
                //        if (dt != null && dt.Tables != null && dt.Tables.Count > 0)
                //        {
                //            for (int i = 0; i < dt.Tables[0].Rows.Count; i++)
                //            {
                //                string ycSql = "update Sb_EquipMentCollectionTechParam_ERR set HSendFlag = 1 where HItemID = " + dt.Tables[0].Rows[i]["HItemID"].ToString();
                //                DBHelper.CustomWriteLog("钉钉异常预警更新语句:" + ycSql, DateTime.Now.ToString("yyyy-MM-dd"));
                //                oCN.RunProc(ycSql);
                //            }
                //        }
                //        else
                //        {
                //            // 可以在这里添加适当的处理，比如记录日志表明没有可处理的数据表
                //            DBHelper.CustomWriteLog("没有更新的语句", DateTime.Now.ToString("yyyy-MM-dd"));
                //        }
                //        //查询出要发信息的异常参数
                      
                //        string newSql = @"SELECT 工序名称, STUFF((  SELECT '; ' + 设备编码 + ' - ' + 工艺参数名称 + ' - 数采值: ' + 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"));
                //        dt1 = oCN.RunProcReturn(newSql, "h_v_Sb_EquipMentCollectionTechParam_ERRList");                      
                //        string HContext = ""; string HDeptName = "";
                //        for (int j = 0; j < dt1.Tables[0].Rows.Count; j++)
                //        {
                //            DBHelper.CustomWriteLog("异常内容数量   "+ dt1.Tables[0].Rows.Count, DateTime.Now.ToString("yyyy-MM-dd"));
                //            HContext = dt1.Tables[0].Rows[j]["内容"].ToString();
                //            HDeptName = dt1.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"));
                //                dt = oCN.RunProcReturn(newSql1, "Gy_Czygl");
                //                DBHelper.CustomWriteLog("开始进入发信息", DateTime.Now.ToString("yyyy-MM-dd"));
                //                if (dt.Tables[0].Rows.Count > 0)
                //                {
                //                    DBHelper.CustomWriteLog("进入发信息", DateTime.Now.ToString("yyyy-MM-dd"));
                //                    for (int i = 0; i < dt.Tables[0].Rows.Count; i++)
                //                    {
                //                        string HName = dt.Tables[0].Rows[i]["钉钉id"].ToString();
                //                        string Stares = dt.Tables[0].Rows[i]["接收人"].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"));
                //                        }

                //                    }
                //                }
                //            }
                //        }
                //    }
                //    lastNewTaskExecution = DateTime.Now;
                //}


                // 等待一分钟
                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; }
        }
    }
}