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[] OutUserid = System.Configuration.ConfigurationManager.AppSettings["OutUserid"].Trim().Split(',');
            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 '%.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)
                    {
                        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>
        /// <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

    }
}