using AutoCheckSMS.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace AutoCheckSMS
{
    /// <summary>
    /// 数据库操作类
    /// </summary>
    public class SQLHelper
    {
        Database da = DatabaseFactory.CreateDatabase("ConnectionString");
        private static SQLHelper _instance;
        public static SQLHelper Instance
        {
            get
            {
                if (_instance == null)
                {
                    _instance = new SQLHelper();
                }
                return _instance;
            }
        }

        #region  审核操作
        /// <summary>
        /// 获取待审核内容
        /// </summary>
        /// <returns></returns>
        public List<tbl_sms_task> GetSmsConent()
        {
            List<tbl_sms_task> list = new List<tbl_sms_task>();
           string[] Out = System.Configuration.ConfigurationManager.AppSettings["OutUserid"].Trim().Split(',');

            string U_sql = "select userid,parentuserid from tbl_user  where isuse=1 and isdelete=0";
            DataSet U_ds1 = da.ExecuteDataSet(CommandType.Text, U_sql);
            DataTable U_dt = U_ds1.Tables[0];
            List<GetUserID> IGetID = new List<GetUserID>();
            foreach (DataRow item in U_dt.Rows)
            {
                IGetID.Add(new GetUserID{
                    UserID = item[0].ToString(),
                    ParentuserID = item[1].ToString()
                });
            }
            List<GetUserID> GetID = GetSonUsserID(Out, IGetID);

            string[] I_Out = new string[GetID.Count];
            int i = 0;
            foreach (var item in GetID)
            {
                I_Out[i] = item.UserID;
                i++;
            }
            Log4netService.Debug($"过滤ID个数:{GetID.Count}");
            try
            {
                string time = DateTime.Now.AddHours(-1).ToString("yyyy-MM-dd HH:mm");
                string sql = $"select TaskID,UserID,MsgContent,ReadError,IsAudit from tbl_sms_task where CreateTime>= '{time}'and IsAudit=0 and ReadError like '%分包完成%' and msgContent not like '%退'  and MsgContent not like '%回_退%' and MsgContent not like '%退订%' and MsgContent not like '%退_T%' and MsgContent not like '%.c%'  and MsgContent not like '%点对点%'";
                DataSet ds = da.ExecuteDataSet(CommandType.Text, sql);
                DataTable dt = ds.Tables[0];
                if (dt.Rows.Count > 0)
                {
                    foreach (DataRow dr in dt.Rows)
                    {
                        if (!I_Out.Contains(dr["UserID"].ToString().Trim()))
                        {
                            int taskid = int.Parse(dr["TaskID"].ToString());
                            int userid = int.Parse(dr["UserID"].ToString());
                            string content = dr["MsgContent"].ToString();
                            int isaudit = int.Parse(dr["IsAudit"].ToString());
                            list.Add(new tbl_sms_task() { TaskID = taskid, UserID = userid, MsgContent = content, IsAudit = isaudit });
                        }
                        else
                        {
                            Console.WriteLine($"【审核操作】存在过滤ID：{dr["UserID"].ToString().Trim()},跳过该ID内容");
                            Log4netService.Debug($"【审核操作】存在过滤ID：{dr["UserID"].ToString().Trim()},跳过该ID内容：{dr["MsgContent"]}");
                        }

                    }
                }
            }
            catch (Exception ex)
            {
                Log4netService.Error($"获取待审核内容异常：{ex.Message}");
            }
            return list;
        }

        /// <summary>
        /// 递归获取获取子ID
        /// </summary>
        /// <param name="UserID">传入一级ID</param>
        /// <param name="GetID">传入账号表</param>
        /// <returns></returns>
        public static List<GetUserID> GetSonUsserID(string[] UserID, List<GetUserID> GetID)
        {
            List<GetUserID> LGetID = new List<GetUserID>();
            List<GetUserID> LID = new List<GetUserID>();
            for (int i = 0; i < UserID.Length; i++)
            {
                LID.AddRange(GetID.Where(a => a.ParentuserID == UserID[i]).ToList());//将GetID中符合一级ID的所有子ID传入LID

            }
            if (LID.Count != 0)//递归条件
            {
                string[] SonID = new string[LID.Count];
                int i = 0;
                foreach (var item in LID)
                {
                    //Console.WriteLine($"递归 ID：{item.ParentuserID}   子ID：{item.UserID}");
                    SonID[i] = item.UserID;
                    i++;
                }//将子ID转成数组
                //for (int l = 0; l < SonID.Length; l++)
                //{
                //    Console.WriteLine($"需要递归的ID：{SonID[l]}");
                //}

                for (int h = 0; h < UserID.Length; h++)
                {
                    LGetID.Add(new GetUserID
                    {
                        UserID = UserID[h].ToString(),
                        ParentuserID = 0.ToString()
                    });
                }
                LGetID.AddRange(GetSonUsserID(SonID, GetID));
            }
            else
            {
                for (int i = 0; i < UserID.Length; i++)
                {
                    LGetID.Add(new GetUserID
                    {
                        UserID = UserID[i].ToString(),
                        ParentuserID = 0.ToString()
                    });
                }
                return LGetID;
            }
            return LGetID;
        }

        public class GetUserID
        {
            public string UserID { get; set; }
            public string ParentuserID { get; set; }
        }
           

        /// <summary>
        /// 更新审核状态
        /// </summary>
        /// <param name="audit"></param>
        /// <returns></returns>
        public int UpdateSmsTask(int audit,int taskids)
        {
            try
            {
                string sql = "update tbl_sms_task set IsAudit=@isAudit where taskid in (@taskids)";
                SqlCommand cmd = new SqlCommand(sql);
                cmd.Parameters.Add(new SqlParameter() { ParameterName = "@isAudit", Value = audit });
                cmd.Parameters.Add(new SqlParameter() { ParameterName = "@taskids", Value = taskids });
                return da.ExecuteNonQuery(cmd);
            }
            catch (Exception ex)
            {
                Log4netService.Error($"更新状态异常：{ex.Message}");
            }
            return 0;
        }


        #endregion


        #region 模板操作

        /// <summary>
        /// 获取手动审核通过的内容
        /// </summary>
        /// <returns></returns>
        public List<tbl_sms_task> GetSmsLogContent()
        {
            List<tbl_sms_task> list = new List<tbl_sms_task>();
            try
            {
                string[] OutUserid = System.Configuration.ConfigurationManager.AppSettings["OutUserid"].Trim().Split(',');
                string time = DateTime.Now.AddHours(-1).ToString("yyyy-MM-dd HH:mm");
                string sql = $"select TaskID,UserID,MsgContent,ReadError,IsAudit from tbl_sms_task_log where CreateTime>= '{time}' and issend=2  and (MsgContent not like '%退订%' and MsgContent not like '%.c%'  and MsgContent not like '%点对点%') and IsAudit=2";
                DataSet ds = da.ExecuteDataSet(CommandType.Text, sql);
                DataTable dt = ds.Tables[0];
                if (dt.Rows.Count > 0)
                {
                    foreach (DataRow dr in dt.Rows)
                    {
                        foreach (var item in OutUserid)
                        {
                            if (dr["UserID"].ToString() != item)
                            {
                                int taskid = int.Parse(dr["TaskID"].ToString());
                                int userid = int.Parse(dr["UserID"].ToString());
                                string content = dr["MsgContent"].ToString();
                                int isaudit = int.Parse(dr["IsAudit"].ToString());
                                list.Add(new tbl_sms_task() { TaskID = taskid, UserID = userid, MsgContent = content, IsAudit = isaudit });
                            }
                            else
                            {
                                Console.WriteLine($"存在过滤ID：{item},跳过该ID内容");
                                Log4netService.Debug($"【模板操作】存在过滤ID：{item},跳过该ID内容：{dr["MsgContent"]}");
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Log4netService.Error($"获取已审核的内容异常：{ex.Message}");
            }

            return list;
        }


        /// <summary>
        /// 获取模板
        /// </summary>
        /// <returns></returns>
        public List<A_template> GetTemplates()
        {
            List<A_template> list = new List<A_template>();
            string sql = $"select id,template,matchType from A_template ";
            DataSet ds = da.ExecuteDataSet(CommandType.Text, sql);
            DataTable dt = ds.Tables[0];
            if (dt.Rows.Count > 0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    int id = int.Parse(dr["id"].ToString());
                    string template = dr["template"].ToString();
                    int matchType = int.Parse(dr["matchType"].ToString());
                    list.Add(new A_template() { Id=id,Template=template,MatchType= matchType });
                }
            }
            return list;
        }

        /// <summary>
        /// 添加模板
        /// </summary>
        /// <param name="template"></param>
        /// <param name="matchType"></param>
        /// <returns></returns>
        public int AddTemplate(string template,int matchType)
        {
            string sql = "insert into A_template (template,matchType) values (@template,@matchType)";
            SqlCommand cmd = new SqlCommand(sql);
            cmd.Parameters.Add(new SqlParameter() {ParameterName="@template",Value=template });
            cmd.Parameters.Add(new SqlParameter() { ParameterName= "@matchType", Value=matchType});
            return da.ExecuteNonQuery(cmd);
        }

        #endregion

    }
}