using MySql.Data.MySqlClient;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using WX.CRM.Common;
using WX.CRM.Common.Layui;
using WX.CRM.Model.crmModel;
using WX.CRM.Model.Entity;
using WX.CRM.Model.Ww;

namespace WX.CRM.DAL.Ww
{
    public class Ww_huser_Dal
    {
        public List<Ww_Extuser> GeExtUsertList(string corpid, string name, ref Pager pager)
        {
            List<Ww_Extuser> corp = new List<Ww_Extuser>();
            try
            {

                MySqlDbHelper helper = new MySqlDbHelper("MysqlQWConn");
                List<MySqlParameter> parameters = new List<MySqlParameter>();
                string sql = @"select sql_calc_found_rows userid,corpid,ctime,name,avatar,unionid from ww_extuser where 1=1";
                if (!string.IsNullOrEmpty(corpid))
                {
                    sql += " and corpid in(" + corpid + ")";
                }
                if (!string.IsNullOrEmpty(name))
                {
                    sql += " and ( name like @name or exinfo like @name )";
                    parameters.Add(new MySqlParameter() { DbType = DbType.String, Value = string.Format("%{0}%", name), ParameterName = "name" });
                }
                sql += " order by ctime desc limit @pageIndex,@pageSize;select found_rows() scc; ";
                parameters.Add(new MySqlParameter() { DbType = DbType.Int32, Value = pager.rows * (pager.page - 1), ParameterName = "pageIndex" });
                parameters.Add(new MySqlParameter() { DbType = DbType.Int32, Value = pager.rows, ParameterName = "pageSize" });

                DataSet table = helper.ExecuteDataTable(sql, parameters.ToArray());
                foreach (DataRow item in table.Tables[0].Rows)
                {
                    Ww_Extuser model = new Ww_Extuser();
                    model.avatar = item["avatar"].ToString();
                    model.corpid = item["corpid"].ToString();
                    model.ctime = Convert.ToDateTime(item["ctime"]);
                    model.name = item["name"].ToString();
                    model.userid = item["userid"].ToString();
                    model.unionid = item["unionid"].ToString();
                    corp.Add(model);
                }
                pager.totalRows = Convert.ToInt32(table.Tables[1].Rows[0]["scc"]);
            }
            catch (Exception e)
            {
                throw;
            }
            return corp;
        }
        public List<Ww_Corp> Corp_Get()
        {
            List<Ww_Corp> corp = new List<Ww_Corp>();
            try
            {
                MySqlDbHelper helper = new MySqlDbHelper("MysqlQWConn");
                List<MySqlParameter> parameters = new List<MySqlParameter>();
                DataTable table = helper.ExecuteDataTable("select corpid,corpname,companycode,deptid from ww_corp", parameters.ToArray()).Tables[0];
                foreach (DataRow item in table.Rows)
                {
                    Ww_Corp model = new Ww_Corp();
                    model.corpid = item["corpid"].ToString();
                    model.corpname = item["corpname"].ToString();
                    model.companycode = item["companycode"].ToString();
                    model.deptid = item["deptid"].ToString();
                    corp.Add(model);
                }
            }
            catch (Exception e)
            {
                throw;
            }
            return corp;
        }

        public Ww_hhuser HHuser_Get(string userid)
        {
            Ww_hhuser model = null;
            try
            {
                MySqlDbHelper helper = new MySqlDbHelper("MysqlQWConn");
                List<MySqlParameter> parameters = new List<MySqlParameter>() { new MySqlParameter() { DbType = DbType.String, Value = userid, ParameterName = "userid" } };
                DataTable table = helper.ExecuteDataTable("select userid,corpid,uname,exinfo,lmsgtime,deptid,fmsgtime,lastupdate,alias,mobile,email from ww_hhuser where userid=@userid;", parameters.ToArray()).Tables[0];
                foreach (DataRow item in table.Rows)
                {
                    model = new Ww_hhuser();
                    model.corpid = item["corpid"].ToString();
                    if (item["deptid"] != DBNull.Value)
                    {
                        model.deptid = Convert.ToInt32(item["deptid"]);
                    }
                    model.exinfo = item["exinfo"].ToString();
                    if (item["lmsgtime"] != DBNull.Value)
                        model.lmsgtime = Convert.ToDateTime(item["lmsgtime"]);
                    model.uname = item["uname"].ToString();
                    model.userid = item["userid"].ToString();
                }
            }
            catch (Exception e)
            {
                throw;
            }
            return model;
        }
        public Ww_hhuser HHuser_Get(string userid, string corpid)
        {
            Ww_hhuser model = null;
            try
            {
                MySqlDbHelper helper = new MySqlDbHelper("MysqlQWConn");
                List<MySqlParameter> parameters = new List<MySqlParameter>()
                {
                    new MySqlParameter() { DbType = DbType.String, Value = userid, ParameterName = "userid" } ,
                    new MySqlParameter() { DbType = DbType.String, Value = corpid, ParameterName = "corpid" }
                };
                DataTable table = helper.ExecuteDataTable("select userid,corpid,uname,exinfo,lmsgtime,deptid,fmsgtime,lastupdate,alias,mobile,email from ww_hhuser where userid=@userid and corpid=@corpid;", parameters.ToArray()).Tables[0];
                foreach (DataRow item in table.Rows)
                {
                    model = new Ww_hhuser();
                    model.corpid = item["corpid"].ToString();
                    if (item["deptid"] != DBNull.Value)
                    {
                        model.deptid = Convert.ToInt32(item["deptid"]);
                    }
                    model.exinfo = item["exinfo"].ToString();
                    if (item["lmsgtime"] != DBNull.Value)
                        model.lmsgtime = Convert.ToDateTime(item["lmsgtime"]);
                    model.uname = item["uname"].ToString();
                    model.userid = item["userid"].ToString();
                }
            }
            catch (Exception e)
            {
                throw;
            }
            return model;
        }
        public Ww_Extuser ExtUserGet(string userid)
        {
            Ww_Extuser model = null;
            try
            {
                MySqlDbHelper helper = new MySqlDbHelper("MysqlQWConn");
                List<MySqlParameter> parameters = new List<MySqlParameter>() { new MySqlParameter() { DbType = DbType.String, Value = userid, ParameterName = "userid" } };
                DataTable table = helper.ExecuteDataTable(@"
SELECT userid, corpid, ctime, lastupdate, errnums, name, avatar, remoteid, exinfo, unionid FROM   ww_extuser where userid = @userid", parameters.ToArray()).Tables[0];
                foreach (DataRow item in table.Rows)
                {
                    model = new Ww_Extuser();
                    model.userid = item["userid"].ToString();
                    model.corpid = item["corpid"].ToString();
                    model.name = item["name"].ToString();
                    model.avatar = item["avatar"].ToString();
                    if (item["ctime"] != DBNull.Value)
                        model.ctime = Convert.ToDateTime(item["ctime"]);
                    model.exinfo = item["exinfo"].ToString();
                    model.unionid = item["unionid"].ToString();
                    if (item["lastupdate"] != DBNull.Value)
                        model.lastupdate = Convert.ToDateTime(item["lastupdate"]);
                }
            }
            catch (Exception e)
            {
                throw;
            }
            return model;
        }
        /// <summary>
        /// 获取企业微信成员列表
        /// </summary>
        /// <param name="v_name"></param>
        /// <param name="v_corpid"></param>
        /// <param name="v_deptid"></param>
        /// <returns></returns>
        public List<Ww_hhuserModel> WorList_Get(ref Laypage page, string v_name, string v_corpid, string v_deptid, decimal? txt_companyId, string txt_deptId, string txt_groupIds, decimal? txt_userId, decimal? seid, int assignStatus,
            List<decimal> ALLdeptIDS, List<decimal> ALLgidS, int orderType)
        {
            List<Ww_hhuserModel> corp = new List<Ww_hhuserModel>();
            List<WW_HHUSER_EID> ls = new List<WW_HHUSER_EID>();//企业微信 和  员工绑定关系
            List<decimal> myuserIdList = new List<decimal>();
            try
            {
                bool isShowAll = true;
                using (var db = new crmContext())
                {

                    var queryData = db.WW_HHUSER_EID.AsQueryable();
                    if (assignStatus > -1)
                    {
                        isShowAll = false;
                        queryData = queryData.Where(m => m.ASSIGNSTATUS == assignStatus);
                    }
                    if (txt_userId.HasValue || seid.HasValue)
                    {
                        isShowAll = false;
                        if (txt_userId.HasValue)
                            queryData = queryData.Where(m => m.INNERUSERID == txt_userId.Value);
                        if (seid.HasValue)
                            queryData = queryData.Where(m => m.EID == seid.Value);
                    }

                    if (!string.IsNullOrEmpty(txt_groupIds))
                    {
                        isShowAll = false;
                        var _groupids = OperationUtil.ConvertToDecimal(txt_groupIds.Split(','));
                        queryData = (from a in queryData
                                     join b in db.BAS_INNERUSERGROUP on a.INNERUSERID equals b.INNERUSERID
                                     where _groupids.Contains(b.GID.Value)
                                     select a);
                    }
                    else if (!string.IsNullOrEmpty(txt_deptId))
                    {
                        isShowAll = false;
                        var depts = OperationUtil.ConvertToDecimal(txt_deptId.Split(','));
                        queryData = (from a in queryData
                                     join b in db.BAS_INNERUSERGROUP on a.INNERUSERID equals b.INNERUSERID
                                     //join g in db.BAS_INNERGROUP on b.GID equals g.GID
                                     where ALLdeptIDS.Contains(b.DEPTID.Value) || ALLgidS.Contains(b.GID.Value)
                                     select a);
                    }
                    else if (txt_companyId.HasValue)
                    {
                        isShowAll = false;
                        queryData = (from a in queryData
                                     join b in db.BAS_INNERUSERGROUP on a.INNERUSERID equals b.INNERUSERID
                                     join g in db.BAS_INNERGROUP on b.GID equals g.GID
                                     join m in db.BAS_SALESDEPARTMENT on g.SALEDEPTID equals m.SALEDEPTID
                                     where m.COMPANYID == txt_companyId.Value
                                     select a);
                    }
                    ls = queryData.ToList();
                }
                

                MySqlDbHelper helper = new MySqlDbHelper("MysqlQWConn");
                List<MySqlParameter> parameters = new List<MySqlParameter>();

                var deptSql = @"select deptid,corpid,deptname,parentid from ww_dept where 1=1";
                List<MySqlParameter> deptparameters = new List<MySqlParameter>();
                if (!string.IsNullOrEmpty(v_corpid))
                {
                    deptSql += " and corpid =@corpid";
                    deptparameters.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "corpid", Value = v_corpid });
                }
                var wxDeptList = helper.QueryEntitys<Ww_hhDeptModel>(deptSql, deptparameters.ToArray());
                string sql = @"select a.corpid,a.deptid,b.corpname,''exinfo ,a.uname,a.userid,a.lmsgtime,a.mobile,a.email,c.deptname from ww_hhuser  a
join ww_corp  b on a.corpid = b.corpid 
 left join ww_dept c on a.deptid=c.deptid and a.corpid=c.corpid
where 1=1
";
                string where = "";
                if (!string.IsNullOrEmpty(v_name))
                {
                    where += " and (a.uname like @uname or a.userid like @uname or a.mobile like @uname)";
                    parameters.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "uname", Value = string.Format("%{0}%", v_name) });
                }
                if (!string.IsNullOrEmpty(v_corpid))
                {
                    where += " and a.corpid =@corpid and a.deptid is not null";
                    parameters.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "corpid", Value = v_corpid });
                }
                List<int> deptidsnew = new List<int>();
                if (!string.IsNullOrEmpty(v_deptid))
                {
                    int[] deptids = Newtonsoft.Json.JsonConvert.DeserializeObject<int[]>(v_deptid);
                    var deptArray = GetAllCildDept(helper, deptids, v_corpid);
                    deptidsnew = deptArray.ToList();
                    //where += " and a.deptid in(" + mm + ")";
                }
                else if (!string.IsNullOrEmpty(v_corpid))
                {
                    string alldeptSql = "select deptid from ww_dept where corpid=@corpid ";
                    List<MySqlParameter> alldeptparameters = new List<MySqlParameter>();
                    alldeptparameters.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "corpid", Value = v_corpid });
                    DataSet alldeptset = helper.ExecuteDataTable(alldeptSql, parameters.ToArray());
                    if (alldeptset != null && alldeptset.Tables.Count > 0 && alldeptset.Tables[0].Rows.Count > 0)
                    {
                        foreach (DataRow dp in alldeptset.Tables[0].Rows)
                        {
                            if (dp["deptid"] != DBNull.Value)
                            {
                                int deptid = Convert.ToInt32(dp["deptid"]);
                                deptidsnew.Add(deptid);
                            }
                        }
                    }
                }
                //sql += where + " order by a.lmsgtime desc limit @page,@limit;";//查询列表
                sql += where + " order by a.lmsgtime desc";//查询列表
                //parameters.Add(new MySqlParameter() { DbType = DbType.Int32, ParameterName = "page", Value = page.page });
                //parameters.Add(new MySqlParameter() { DbType = DbType.Int32, ParameterName = "limit", Value = page.limit });

                DataSet set = helper.ExecuteDataTable(sql, parameters.ToArray());
                List<Ww_hhuserModel> orderCorp = new List<Ww_hhuserModel>();
                foreach (DataRow item in set.Tables[0].Rows)
                {
                    Ww_hhuserModel model = new Ww_hhuserModel();
                    model.corpid = item["corpid"].ToString();
                    model.corpname = item["corpname"].ToString();
                    if (item["deptid"] != DBNull.Value)
                        model.deptid = Convert.ToInt32(item["deptid"]);
                    model.exinfo = item["exinfo"].ToString();
                    if (item["lmsgtime"] != DBNull.Value)
                        model.lmsgtime = Convert.ToDateTime(item["lmsgtime"]);
                    model.userid = item["userid"].ToString();
                    model.uname = item["uname"].ToString();
                    model.mobile = item["mobile"].ToString();
                    model.email = item["email"].ToString();
                    model.deptname = item["deptname"].ToString();
                    if (item["deptid"] != DBNull.Value)
                    {
                        model.isMyController = deptidsnew.Contains(model.deptid.Value) ? 1 : 2;
                    }
                    WW_HHUSER_EID huid = ls.FirstOrDefault(m => m.USERID == model.userid && m.CORPID == model.corpid);
                    if (huid != null)
                    {
                        model.eid = huid.EID;
                        //model.assignStatusName = huid.ASSIGNSTATUS.HasValue ? (huid.ASSIGNSTATUS.Value == 1 ? "是" : "否") : string.Empty;
                        model.assignStatus = huid.ASSIGNSTATUS.HasValue ? Convert.ToInt32(huid.ASSIGNSTATUS) : 0;
                        model.errorcode = huid.ERRORCODE.HasValue ? Convert.ToInt32(huid.ERRORCODE) : (int?)null;
                    }
                    if (isShowAll)
                        corp.Add(model);//如果显示全部，就全部显示
                    else if (huid != null)
                    {
                        corp.Add(model);//加了组织结构过滤条件
                    }
                }
                if(orderType == 2)
                {
                    corp = corp.OrderByDescending(n => n.isMyController).ToList();
                }
                else
                {
                    corp = corp.OrderBy(n => n.isMyController).ToList();
                }
                page.count = corp.Count;
                corp = corp.Skip((page.page - 1) * page.limit).Take(page.limit).ToList();//重新分页
                var deptidList = corp.Select(n => n.deptid).ToList();
                var deptDic = BuildDeptName(wxDeptList.Distinct().ToList(), deptidList);
                foreach(var item in corp)
                {
                    item.deptname = deptDic.FirstOrDefault(n => n.Key == item.deptid).Value;
                }
                //匹配员工企业号的客户信息
                //使用分页后的结果查询
                //减少需赛选的数据量
                //20220218 
                var showIds = corp.Select(m => m.userid);
                var relSql = $"select userId,count(1) extUser,sum(case when ctime >= CURDATE() then 1 else 0 end) newAdd from ww_user_extuser where userid in  ('{string.Join("','", showIds)}') group by userId;";
                var userExtuserSet = helper.ExecuteDataTable(relSql);
                // var userExtuserDic = helper.ExecuteDataDictionary(relSql);
                foreach (DataRow item in userExtuserSet.Tables[0].Rows)
                {
                    var userId = item["userId"].ToString();
                    var corpItem = corp.FirstOrDefault(m => m.userid == userId);
                    if (corpItem != null)
                    {
                        //更新 客户总数与 今日新增
                        corpItem.myExtuser = Convert.ToInt32(item["extUser"].ToString());
                        corpItem.newAdd = Convert.ToInt32(item["newAdd"].ToString());
                    }
                }
            }
            catch (Exception e)
            {
                throw;
            }
            return corp;
        }
        private Dictionary<int, string> BuildDeptName(List<Ww_hhDeptModel> deptList,List<int?> listId)
        {
            listId = listId.Distinct().ToList();
            Dictionary<int, string> result = new Dictionary<int, string>();
            foreach (var id in listId)
            {
                var res = BuildDeptName(id.Value, deptList, "");
                result.Add(id.Value, res.Trim('/'));
            }
            return result;
        }
        public string BuildDeptName (int id , List<Ww_hhDeptModel> deptList,string name)
        {
            var dept = deptList.FirstOrDefault(n => n.deptid == id);
            if (dept != null)
            {
                name = $"{dept.deptname}/{name}";
                return BuildDeptName(dept.parentid, deptList, name);
            }
            return name;
        }
        #region 查找所有子部门
        /// <summary>
        /// 找出配置里面所有的部门ID
        /// </summary>
        /// <param name="companycode"></param>
        /// <returns></returns>
        public Dictionary<string, List<int>> GetAllCorpDept(string companycode)
        {
            MySqlDbHelper helper = new MySqlDbHelper("MysqlQWConn");
            List<Ww_Corp> list = Corp_Get();
            Dictionary<string, List<int>> dic = new Dictionary<string, List<int>>();
            if (!string.IsNullOrEmpty(companycode))
            {
                string companycodeFormat = string.Format("[{0}]", companycode);
                list = list.Where(m => m.companycode.Contains(companycodeFormat)).ToList();
                foreach (var item in list)
                {
                    int[] deptid = new int[] { };
                    if (!string.IsNullOrEmpty(item.deptid))
                    {
                        JObject obj = (JObject)Newtonsoft.Json.Linq.JObject.Parse(item.deptid);
                        deptid = JsonConvert.DeserializeObject<int[]>(obj[companycode].ToString());
                    }
                    dic.Add(item.corpid, GetAllCildDept(helper, deptid, item.corpid).ToList());
                }
            }
            return dic;
        }

        public int[] GetAllCildDept(MySqlDbHelper helper, int[] deptids, string corpid)
        {
            if (deptids.Count() == 0 || string.IsNullOrEmpty(corpid))
            {
                return deptids;
            }
            Dictionary<int, int> dicdptlist = new Dictionary<int, int>();
            foreach (var item in deptids)
            {
                if (!dicdptlist.ContainsKey(item))
                    dicdptlist.Add(item, item);
            }
            foreach (var item in deptids)
            {
                GetChildDept(helper, dicdptlist, corpid, item);
            }
            return dicdptlist.Select(m => m.Key).ToArray();
        }
        public void GetChildDept(MySqlDbHelper helper, Dictionary<int, int> dplist, string corpid, int id)
        {
            //List<Ww_Dept> deptlist = _crmContext.Ww_Dept.Where(m => m.parentid == id && m.corpid == corpid).ToList();
            string sql = "select * from ww_dept where parentid=@parentid and corpid=@corpid ";
            List<MySqlParameter> parameters = new List<MySqlParameter>()
            {
                new MySqlParameter() { DbType = DbType.String, ParameterName = "parentid", Value = id },
                new MySqlParameter() { DbType = DbType.String, ParameterName = "corpid", Value = corpid }
            };
            //DataSet set = MySqlDbHelper.ExecuteDataTable(helper.GetConnecionString(MySqlDbHelper.DatabaseType.MysqlQWConn), CommandType.Text, sql, parameters.ToArray());
            DataSet set = helper.ExecuteDataTable(sql, parameters.ToArray());
            if (set != null && set.Tables.Count > 0 && set.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dp in set.Tables[0].Rows)
                {
                    if (dp["deptid"] != DBNull.Value)
                    {
                        int deptid = Convert.ToInt32(dp["deptid"]);
                        if (!dplist.ContainsKey(deptid))
                        {
                            dplist.Add(deptid, deptid);
                            GetChildDept(helper, dplist, corpid, deptid);
                        }

                    }
                }
            }
        }
        #endregion
        public List<Hg_KeyWord> GetKeyWordAll(bool showAll = false)
        {
            List<Hg_KeyWord> list = new List<Hg_KeyWord>();
            try
            {
                MySqlDbHelper helper = new MySqlDbHelper("MysqlQWConn");
                string sql = "select keyword,ctime,status from hg_keyword where status=1 order by ctime desc";
                if (showAll == true)
                    sql = "select keyword,ctime,status from hg_keyword  order by ctime desc";
                DataTable tab = helper.ExecuteDataTable(sql).Tables[0];
                foreach (DataRow item in tab.Rows)
                {
                    Hg_KeyWord model = new Hg_KeyWord();
                    model.keyword = item["keyword"].ToString();
                    model.status = Convert.ToInt32(item["status"]);
                    model.ctime = Convert.ToDateTime(item["ctime"]);
                    list.Add(model);
                }
                return list;
            }
            catch (Exception e)
            {
                LogHelper.Error(e.ToString());
                throw;
            }
        }

        /// <summary>
        /// 修改关键字
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool UpdateKeyWord(Hg_KeyWordAllModel model, ref string message)
        {
            bool result = false;
            try
            {
                model.keywords = model.keywords.Replace("；", ";").Replace("\n\r", "").Replace("\r", "").Replace("\n", "");
                List<Ww_Corp> corp = Corp_Get();//企业微信号列表
                List<Hg_KeyWord> keys = GetKeyWordAll(true);//所有的关键词
                List<Hg_Man_Config> manconfig = GetMainConfig();//当前主线程执行顺序
                List<string> keylist2 = model.keywords.Split(';').ToList();
                List<string> keylist = new List<string>();

                foreach (var item in keylist2)
                {
                    if (!string.IsNullOrEmpty(item))
                        keylist.Add(item);
                }
                MySqlDbHelper helper = new MySqlDbHelper("MysqlQWConn");
                string addmessage = "";
                string delmessage = "";
                string pici = DateTime.Now.ToString("yyyyMMddHHmmssfff");
                foreach (string item1 in keylist)
                {
                    string item = item1.Trim();
                    if (string.IsNullOrEmpty(item))
                        continue;
                    Hg_KeyWord word = keys.FirstOrDefault(m => m.keyword == item);
                    if (word == null)//如果没有数据，则新增
                    {
                        addmessage += string.Format("{0};", item);
                        helper.ExecuteNonQuery("insert into hg_keyword(keyword,status)values(@keyword, 1);", new MySqlParameter() { DbType = DbType.String, ParameterName = "keyword", Value = item });//插入数据
                        foreach (var co in corp)
                        {
                            var en = manconfig.FirstOrDefault(m => m.corpid == co.corpid);
                            if (en == null || en.seq == 0)//如果主线程都没有执行，就不用新增 补充数据指令了
                            {
                                continue;
                            }
                            else
                            {
                                //插入待执行表， 最大值为当前执行的位置
                                helper.ExecuteNonQuery("insert into hg_make_up(corpid,keyword,seq,maxseq,status,pici)values(@corpid,@keyword, 0,@maxseq,90,@pici);",
                                    new MySqlParameter() { DbType = DbType.String, ParameterName = "corpid", Value = en.corpid },
                                    new MySqlParameter() { DbType = DbType.String, ParameterName = "keyword", Value = item },
                                    new MySqlParameter() { DbType = DbType.Int32, ParameterName = "maxseq", Value = en.seq },
                                    new MySqlParameter() { DbType = DbType.String, ParameterName = "pici", Value = pici }
                                    );//有记录则插入补充记录
                            }
                        }
                    }
                    else
                    {
                        if (word.status == 0)//有数据，切已经被删除了，则修改会正常状态
                        {
                            addmessage += string.Format("{0};", item);
                            helper.ExecuteNonQuery("update hg_keyword set status=1 where keyword=@keyword;", new MySqlParameter() { DbType = DbType.String, ParameterName = "keyword", Value = item });//插入数据
                            foreach (var co in corp)
                            {
                                var en = manconfig.FirstOrDefault(m => m.corpid == co.corpid);
                                if (en == null || en.seq == 0)//如果主线程都没有执行，就不用新增 补充数据指令了
                                {
                                    continue;
                                }
                                else
                                {
                                    //插入待执行表， 最大值为当前执行的位置
                                    helper.ExecuteNonQuery("insert into hg_make_up(corpid,keyword,seq,maxseq,status,pici)values(@corpid,@keyword, 0,@maxseq,90,@pici);",
                                        new MySqlParameter() { DbType = DbType.String, ParameterName = "corpid", Value = en.corpid },
                                        new MySqlParameter() { DbType = DbType.String, ParameterName = "keyword", Value = item },
                                        new MySqlParameter() { DbType = DbType.Int32, ParameterName = "maxseq", Value = en.seq },
                                        new MySqlParameter() { DbType = DbType.String, ParameterName = "pici", Value = pici }
                                        );//有记录则插入补充记录
                                }
                            }
                        }
                    }
                }
                var delkeyword = keys.Where(m => !keylist.Contains(m.keyword)).ToList();
                foreach (var item in delkeyword)
                {
                    delmessage += string.Format("{0};", item.keyword);
                    helper.ExecuteNonQuery("update hg_keyword set status=0 where keyword=@keyword;", new MySqlParameter() { DbType = DbType.String, ParameterName = "keyword", Value = item.keyword });//将关键字 修改成
                    helper.ExecuteNonQuery("update hg_make_up set status=120 where  keyword=@keyword and status=90;", new MySqlParameter() { DbType = DbType.String, ParameterName = "keyword", Value = item.keyword });//修改成取消状态
                }

                if (!string.IsNullOrEmpty(addmessage))
                {
                    message += "新增关键字：“" + addmessage + "”\n";
                }
                if (!string.IsNullOrEmpty(delmessage))
                {
                    message += "删除关键字：“" + delmessage + "”\n";
                }
                if (string.IsNullOrEmpty(message))
                {
                    message = "无变动！";
                }
                result = true;
            }
            catch (Exception e)
            {
                message = "系统错误！";
                LogHelper.Error(e.ToString());
            }
            return result;
        }

        public List<Hg_Man_Config> GetMainConfig()
        {
            List<Hg_Man_Config> list = new List<Hg_Man_Config>();
            try
            {
                MySqlDbHelper helper = new MySqlDbHelper("MysqlQWConn");
                DataTable tab = helper.ExecuteDataTable("select corpid,tablename,seq from hg_man_config").Tables[0];
                foreach (DataRow item in tab.Rows)
                {
                    Hg_Man_Config model = new Hg_Man_Config();
                    model.corpid = item["corpid"].ToString();
                    model.tablename = item["tablename"].ToString();
                    model.seq = Convert.ToInt32(item["seq"]);
                    list.Add(model);
                }
                return list;
            }
            catch (Exception e)
            {
                LogHelper.Error(e.ToString());
                throw;
            }
        }
        public bool SetKeyWords(string keywords, ref ValidationErrors erro)
        {
            bool result = false;
            try
            {
                MySqlDbHelper helper = new MySqlDbHelper("MysqlQWConn");
                DataTable tab = helper.ExecuteDataTable("select count(*)count from bas_parameter where parakey='WeiXin_IllegalKewords'").Tables[0];
                if (Convert.ToInt32(tab.Rows[0]["count"]) == 0)
                {
                    helper.ExecuteNonQuery("insert into  bas_parameter (parakey,paravalue,remark) values('WeiXin_IllegalKewords',@paravalue,'关键词配置')", new MySqlParameter() { DbType = DbType.String, ParameterName = "paravalue", Value = keywords });
                }
                else
                {

                    helper.ExecuteNonQuery("update  bas_parameter set paravalue=@paravalue where parakey='WeiXin_IllegalKewords' ", new MySqlParameter() { DbType = DbType.String, ParameterName = "paravalue", Value = keywords });
                }
                result = true;
            }
            catch (Exception e)
            {
                LogHelper.Error(e.ToString());
                erro.Add(e.ToString());
            }
            return result;
        }

        public List<Hg_MessageModel> GetHg_Message(ref Pager pager, string userNames, DateTime? msgstime, DateTime? msgetime, DateTime? stime, DateTime? etime, string keyword, string kefid, string userid, int? status)
        {
            List<Hg_MessageModel> list = new List<Hg_MessageModel>();
            try
            {
                MySqlDbHelper helper = new MySqlDbHelper("MysqlQWConn");
                string sql = "select SQL_CALC_FOUND_ROWS id,seq,msgid,msgtype,action,fromer,tolist,roomid,ext,content,nfile,msgtime,ctime,hgstatus,hgtime,issend,kehuname,cusname,corpid from hg_message x where 1=1 ";
                List<MySqlParameter> para = new List<MySqlParameter>();
                if (!string.IsNullOrEmpty(userNames))//客服ID
                {
                    sql += " and fromer in(" + userNames + ")";
                }
                if (msgstime.HasValue)//消息开始时间
                {
                    sql += " and msgtime>=@msgstime";
                    para.Add(new MySqlParameter() { ParameterName = "@msgstime", MySqlDbType = MySqlDbType.DateTime, Value = msgstime.Value });
                }
                if (msgetime.HasValue)//消息结束时间
                {
                    msgetime = msgetime.Value.AddDays(1);//加一天
                    sql += " and msgtime<@msgetime";
                    para.Add(new MySqlParameter() { ParameterName = "@msgetime", MySqlDbType = MySqlDbType.DateTime, Value = msgetime.Value });
                }

                if (stime.HasValue)//系统操作开始时间
                {
                    sql += " and ctime>=@stime";
                    para.Add(new MySqlParameter() { ParameterName = "@stime", MySqlDbType = MySqlDbType.DateTime, Value = stime.Value });
                }
                if (etime.HasValue)//系统操作结束时间
                {
                    etime = etime.Value.AddDays(1);//加一天
                    sql += " and ctime<@etime";
                    para.Add(new MySqlParameter() { ParameterName = "@etime", MySqlDbType = MySqlDbType.DateTime, Value = etime.Value });
                }
                if (!string.IsNullOrEmpty(kefid))//客服ID
                {
                    sql += " and fromer=@fromer";
                    para.Add(new MySqlParameter() { ParameterName = "@fromer", MySqlDbType = MySqlDbType.String, Value = kefid });
                }
                if (!string.IsNullOrEmpty(userid))//客户ID
                {
                    sql += " and tolist=@userid";
                    para.Add(new MySqlParameter() { ParameterName = "@userid", MySqlDbType = MySqlDbType.String, Value = userid });
                }
                if (status.HasValue)
                {
                    sql += " and hgstatus=@hgstatus";
                    para.Add(new MySqlParameter() { ParameterName = "@hgstatus", MySqlDbType = MySqlDbType.String, Value = status.Value });
                }
                if (!string.IsNullOrEmpty(keyword))//关键词
                {
                    sql += " and exists(select 1 from hg_message_word a where a.msgid=x.msgid and a.keyword in(" + keyword + "))";
                }
                if (!string.IsNullOrEmpty(pager.sort) && !string.IsNullOrEmpty(pager.order) && pager.sort != "null" && pager.order != "null")
                {
                    sql += string.Format(" order by {0} {1}", pager.sort, pager.order);//排序
                }

                sql += " limit @pagestart,@endpage ; ";
                para.Add(new MySqlParameter() { ParameterName = "@pagestart", MySqlDbType = MySqlDbType.Int32, Value = ((pager.page - 1) * pager.rows) });
                para.Add(new MySqlParameter() { ParameterName = "@endpage", MySqlDbType = MySqlDbType.Int32, Value = pager.rows });
                sql += " SELECT FOUND_ROWS() rowcount;";//查询总行数
                DataSet tab = helper.ExecuteDataTable(sql, para.ToArray());
                pager.totalRows = Convert.ToInt32(tab.Tables[1].Rows[0]["rowcount"]);
                //序列化为字符串
                string t = JsonConvert.SerializeObject(tab.Tables[0]);
                //反序列化为对象
                list = JsonConvert.DeserializeObject<List<Hg_MessageModel>>(t);//将数据进行转换
            }
            catch (Exception e)
            {
                LogHelper.Error(e.ToString());
            }
            return list;
        }

        /// <summary>
        /// 将合规信息修改成已读
        /// </summary>
        /// <param name="msgid"></param>
        /// <returns></returns>
        public bool Read(string msgid)
        {
            bool isok = false;
            try
            {
                MySqlDbHelper helper = new MySqlDbHelper("MysqlQWConn");
                string sql = "update hg_message set  hgstatus=200,hgtime=now()  where msgid=@msgid";
                int x = helper.ExecuteNonQuery(sql, new MySqlParameter() { ParameterName = "@msgid", Value = msgid, DbType = DbType.String });
                isok = x > 0;//执行是否成功
            }
            catch (Exception e)
            {
                LogHelper.Error(e.ToString());
            }
            return isok;
        }

        /// <summary>
        /// 已经违规
        /// </summary>
        /// <param name="msgid"></param>
        /// <returns></returns>
        public bool Violations(string msgid)
        {
            bool isok = false;
            try
            {
                MySqlDbHelper helper = new MySqlDbHelper("MysqlQWConn");
                string sql = "update hg_message set  hgstatus=500,hgtime=now()  where msgid=@msgid";
                int x = helper.ExecuteNonQuery(sql, new MySqlParameter() { ParameterName = "@msgid", Value = msgid, DbType = DbType.String });
                isok = x > 0;//执行是否成功
            }
            catch (Exception e)
            {
                LogHelper.Error(e.ToString());
            }
            return isok;
        }

        /// <summary>
        /// 获取单条数据
        /// </summary>
        /// <param name="msgid"></param>
        /// <returns></returns>
        public Hg_MessageModel GetMsgById(string msgid)
        {
            Hg_MessageModel model = null;
            try
            {
                MySqlDbHelper helper = new MySqlDbHelper("MysqlQWConn");
                string sql = "select id,seq,msgid,msgtype,action,fromer,tolist,roomid,ext,content,nfile,msgtime,ctime,hgstatus,hgtime,issend,kehuname,cusname,corpid from hg_message  where msgid=@msgid";
                DataSet tab = helper.ExecuteDataTable(sql, new MySqlParameter() { ParameterName = "@msgid", MySqlDbType = MySqlDbType.String, Value = msgid });
                if (tab.Tables[0].Rows.Count == 0)
                    return model;
                string t = JsonConvert.SerializeObject(tab.Tables[0]);
                model = JsonConvert.DeserializeObject<List<Hg_MessageModel>>(t).FirstOrDefault();//将数据进行转换
            }
            catch (Exception e)
            {
                LogHelper.Error(e.ToString());
            }
            return model;
        }

        public List<Hg_Message_WordModel> GetMsgWordList(params string[] msgids)
        {
            List<Hg_Message_WordModel> list = new List<Hg_Message_WordModel>();
            try
            {
                if (msgids == null || msgids.Length == 0)
                    return list;
                MySqlDbHelper helper = new MySqlDbHelper("MysqlQWConn");
                string msgid = string.Format("'{0}'", string.Join("','", msgids));
                string sql = "select msgid,keyword from hg_message_word  where msgid in(" + msgid + ")";
                DataSet tab = helper.ExecuteDataTable(sql);
                string t = JsonConvert.SerializeObject(tab.Tables[0]);
                list = JsonConvert.DeserializeObject<List<Hg_Message_WordModel>>(t);//将数据进行转换
            }
            catch (Exception e)
            {
                LogHelper.Error(e.ToString());
            }
            return list;
        }

        public Ww_Extuser GetExtUserByUnionId(string unionid)
        {
            var list = new List<Ww_Extuser>();
            try
            {
                var helper = new MySqlDbHelper("MysqlQWConn");
                var parameters = new List<MySqlParameter>();
                parameters.Add(new MySqlParameter() { ParameterName = "@unionid", MySqlDbType = MySqlDbType.String, Value = unionid });
                //DataTable table = helper.ExecuteDataTable("select * from ww_extuser where exinfo like '%" + unionid + "%'", parameters.ToArray()).Tables[0];
                DataTable table = helper.ExecuteDataTable("select * from ww_extuser where unionid = @unionid", parameters.ToArray()).Tables[0];
                foreach (DataRow item in table.Rows)
                {
                    var model = new Ww_Extuser
                    {
                        userid = item["userid"].ToString(),
                        corpid = item["corpid"].ToString(),
                        name = item["name"].ToString(),
                        avatar = item["avatar"].ToString()
                    };
                    list.Add(model);
                }
            }
            catch (Exception e)
            {
                throw;
            }
            return list.FirstOrDefault();
        }

        public List<Ww_User_Extuser> GetUserExtUserByUserId(string userid)
        {
            var list = new List<Ww_User_Extuser>();
            try
            {
                var helper = new MySqlDbHelper("MysqlQWConn");
                var parameters = new List<MySqlParameter>();
                parameters.Add(new MySqlParameter() { ParameterName = "@userid", MySqlDbType = MySqlDbType.String, Value = userid });
                DataTable table = helper.ExecuteDataTable("select * from ww_user_extuser where extuserid = @userid", parameters.ToArray()).Tables[0];
                foreach (DataRow item in table.Rows)
                {
                    var model = new Ww_User_Extuser
                    {
                        userid = item["userid"].ToString(),
                        corpid = item["corpid"].ToString(),
                        extuserid = item["extuserid"].ToString()
                    };
                    list.Add(model);
                }
            }
            catch (Exception e)
            {
                throw;
            }
            return list;
        }
    }
   
}