using Newtonsoft.Json.Linq;
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;

namespace DingDingServices_SMR
{
    public class DingDingServeices
    {
        public DataSet ds = new DataSet();
        public DataSet ds1 = new DataSet();
        public DBHelper oCN = new DBHelper();       

        /// <summary>
        /// 获取异常反馈单未关闭的单子
        /// </summary>
        public object Get_ErrMsgBackBillMain()
        {
            //获取未关闭单子
            string sql = "select a.HInterID,a.HContext,a.HDescription,a.HReceiveMan,a.HCopyMan,a.HMakeDate,DATEDIFF(MINUTE, a.HMakeDate, GETDATE()) AS MinutesPassed from OA_ErrMsgBackBillMain a where HResult='未完成'";
            ds = oCN.RunProcReturn(sql, "OA_ErrMsgBackBillMain");
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                //获取已发送人,未关闭的单子 主内码 内容
                string Superiors = ds.Tables[0].Rows[i]["HReceiveMan"].ToString();
                string HInterID = ds.Tables[0].Rows[i]["HInterID"].ToString();
                string HDescription = ds.Tables[0].Rows[i]["HDescription"].ToString();
                //获取制单日期到现在过了多长时间
                int TimeInterval = Convert.ToInt32(ds.Tables[0].Rows[i]["MinutesPassed"]);
                //根据发送人去找对应的上级
                string sql1 = "select a.HGroupID,c.GroupName,* 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 Czymc = '" + Superiors + "'";
                ds = oCN.RunProcReturn(sql1, "Gy_Czygl");
                //获取职务
                string Job = ds.Tables[0].Rows[0]["GroupName"].ToString();
                //根据职务去找当前人上级
                string sql2 = "select * from Gy_Duty where HParentID=(select HParentID from Gy_Duty where HItemID=(select HParentID from Gy_Duty where HName='" + Job + "' ))";
                ds = oCN.RunProcReturn(sql2, "Gy_Duty");
                //根据安灯规则去找到匹配规则的职位,然后获取到时间,看是否大于1
                if (ds.Tables[0].Rows.Count>1)
                {
                    for (int j = 0; j < ds.Tables[0].Rows.Count; j++)
                    {
                        //获取上级人的职务
                        string Jobs = ds.Tables[0].Rows[j]["HName"].ToString();
                        //根据安灯去匹配职务和触发时间
                        string sql3 = "select b.HTimes,c.HName from OA_AndengFlowRuleBillMain a inner join OA_AndengFlowRuleBillSub b on a.HInterID = b.HInterID left join Gy_Duty c on b.HDutyID = c.HItemID where c.HName = '" + Jobs + "'";
                        ds = oCN.RunProcReturn(sql3, "OA_AndengFlowRuleBillMain");
                        //根据规则获取触发时间  职务
                        int HTimes = Convert.ToInt32(ds.Tables[0].Rows[0]["HTimes"]);
                        string JobS = ds.Tables[0].Rows[0]["HName"].ToString();
                        //如果时间间隔大于触发时间
                        if (TimeInterval > HTimes)
                        {
                            //根据职务去匹配相对应的人 用于插入数据库
                            string sql4 = "select a.HGroupID,c.GroupName,* 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 = '" + JobS + "'";
                            ds = oCN.RunProcReturn(sql4, "Gy_Czygl");
                            string HName = ds.Tables[0].Rows[0]["Czymc"].ToString();
                            //获取当前异常反馈单子表的ID是多大,然后插入子表信息
                            string sql5 = "select max(HEntryID) HEntryID from OA_ErrMsgBackBillSub2 where HInterID='" + HInterID + "'";
                            ds1 = oCN.RunProcReturn(sql5, "OA_ErrMsgBackBillSub2");
                            int HEntryID = Convert.ToInt32(ds1.Tables[0].Rows[0]["HName"]);
                            int newHEntryID = HEntryID + 1; //根据当前ID最大的子ID去加1
                            string sql6 = "insert OA_ErrMsgBackBillSub2 values('" + HInterID + "','" + newHEntryID + "','" + HName + "','0','1')";
                            ds1 = oCN.RunProcReturn(sql6, "OA_ErrMsgBackBillSub2");
                            //传入插入子表的人    异常反馈单的内容
                            DingDingMesAsync(HName, HDescription);

                        }
                    }
                }
                else
                {
                    //获取上级人的职务
                    string Jobs = ds.Tables[0].Rows[0]["HName"].ToString();
                    //根据安灯去匹配职务和触发时间
                    string sql3 = "select b.HTimes,c.HName from OA_AndengFlowRuleBillMain a inner join OA_AndengFlowRuleBillSub b on a.HInterID = b.HInterID left join Gy_Duty c on b.HDutyID = c.HItemID where c.HName = '"+ Jobs + "'";
                    ds = oCN.RunProcReturn(sql3, "OA_AndengFlowRuleBillMain");
                    //根据规则获取触发时间  职务
                    int HTimes = Convert.ToInt32(ds.Tables[0].Rows[0]["HTimes"]);
                    string JobS = ds.Tables[0].Rows[0]["HName"].ToString();
                    //如果时间间隔大于触发时间
                    if (TimeInterval > HTimes)
                    {
                        //根据职务去匹配相对应的人 用于插入数据库
                        string sql4 = "select a.HGroupID,c.GroupName,* 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 = '"+ JobS + "'";
                        ds = oCN.RunProcReturn(sql4, "Gy_Czygl");
                        string HName= ds.Tables[0].Rows[0]["Czymc"].ToString();
                        //获取当前异常反馈单子表的ID是多大,然后插入子表信息
                        string sql5 = "select max(HEntryID) HEntryID from OA_ErrMsgBackBillSub2 where HInterID='" + HInterID + "'";
                        ds1 = oCN.RunProcReturn(sql5, "OA_ErrMsgBackBillSub2");
                        int HEntryID = Convert.ToInt32(ds1.Tables[0].Rows[0]["HName"]);
                        int newHEntryID = HEntryID + 1; //根据当前ID最大的子ID去加1
                        string sql6 = "insert OA_ErrMsgBackBillSub2 values('"+HInterID+"','"+newHEntryID+"','"+HName+"','0','1')";
                        ds1 = oCN.RunProcReturn(sql6, "OA_ErrMsgBackBillSub2");
                        //传入插入子表的人    异常反馈单的内容
                        DingDingMesAsync(HName, HDescription);

                    }
                }

            }
            return 1;
        }


        /// <summary>
        /// 钉钉接口发送消息
        /// </summary>
        /// <param name="HReceiveMan">要发信息的人</param>
        /// <param name="HDescription">异常反馈单内容</param>
        public async void DingDingMesAsync(string HReceiveMan, string HDescription)
        {

            ds = oCN.RunProcReturn("select * from Gy_Czygl where Czymc='" + HReceiveMan + "'", "Gy_Czygl");
            string DDUserID = ds.Tables[0].Rows[0]["DDUserID"].ToString();  //钉钉用户的id
            MSG msg = new MSG();
            string appKey = "dingrsrzhdyn3mlaof95";
            string appSecret = "RAqH6YtZnPLCpDbuqfaYQkKkVtVdS0wqfC8I26X6qiS-8eoCJCNrzx3fubGND4Sq";
            //获取企业的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 deptIdList = "0"; // 空字符串表示不指定部门
            string toAllUser = "false";
            string message = HDescription;//异常反馈单的内容
            response = await msg.SendTextMessage(accessToken, agentId, DDUserID, deptIdList, toAllUser, message);
            Console.WriteLine($"初始消息发送结果: {response}");
        }
    }
}