890 lines
43 KiB
C#
890 lines
43 KiB
C#
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;
|
||
}
|
||
}
|
||
|
||
}
|