380 lines
17 KiB
C#
380 lines
17 KiB
C#
using Mini.Model;
|
|
using Mini.Model.CrmModel;
|
|
using Mini.Model.ViewModel;
|
|
using System;
|
|
using System.Collections.Generic;
|
|
using System.Text;
|
|
using System.Linq.Expressions;
|
|
using System.Linq;
|
|
using Mini.Common;
|
|
using System.Data;
|
|
using MySql.Data.MySqlClient;
|
|
using Mini.Model.WxEntity;
|
|
|
|
namespace Mini.Services.wx
|
|
{
|
|
/// <summary>
|
|
/// 工作微信监控service
|
|
/// </summary>
|
|
public class wx_workwechat_Service : Iwx_workwechat_Service
|
|
{
|
|
|
|
private readonly wxContext _crmContext;
|
|
public wx_workwechat_Service(wxContext crmContext)
|
|
{
|
|
_crmContext = crmContext;
|
|
}
|
|
public Wx_Rcontact GetRcontact(string workusername, string username)
|
|
{
|
|
return _crmContext.Wx_Rcontact.FirstOrDefault(m => m.username == username && m.jobwxusername == workusername);
|
|
}
|
|
public Wx_WorkAccount GetWorAccount(string worusername)
|
|
{
|
|
return _crmContext.Wx_WorkAccount.FirstOrDefault(m => m.username == worusername);
|
|
}
|
|
|
|
public DataTable GetGroupNumber(string v_roomname)
|
|
{
|
|
DataTable tab = null;
|
|
try
|
|
{
|
|
string connectStr = Utility.GetSettingByKey("ConnectionStrings:wxContext");
|
|
var p = new List<MySqlParameter>()
|
|
{
|
|
new MySqlParameter() { ParameterName = "v_roomname", DbType = DbType.String, Value =v_roomname }
|
|
|
|
};
|
|
tab = MySqlDbHelper.DataQueray(connectStr, CommandType.StoredProcedure, "PACK_WX_GROUP_GetGroupNumberByWorkAccount2", p.ToArray()).Tables[0];
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
LogHelper.Error("PACK_WX_GROUP.GetGroupNumberByWorkAccount:" + ex.ToString());
|
|
}
|
|
return tab;
|
|
}
|
|
/// <summary>
|
|
/// 获取企业微信成员列表
|
|
/// </summary>
|
|
/// <param name="v_name">ID</param>
|
|
/// <returns></returns>
|
|
public List<Wx_WorkAccountModel> WorList_Get(ref Laypage pager, string name)
|
|
{
|
|
var db = from a in _crmContext.Wx_WorkAccount
|
|
join b in _crmContext.Wx_Alive on a.username equals b.username into jiontemp
|
|
from ba in jiontemp.DefaultIfEmpty()//左关联（用户组/部门 关系表）
|
|
join c in _crmContext.Wx_JobuserLastFriend on a.username equals c.jobusername into jiontemp2
|
|
from ca in jiontemp2.DefaultIfEmpty()//左关联（用户组/部门 关系表）
|
|
join d in _crmContext.Wx_LastMsgTime on a.username equals d.username into jiontemp4
|
|
from da in jiontemp4.DefaultIfEmpty()//左关联（用户组/部门 关系表）
|
|
select new Wx_WorkAccountModel()
|
|
{
|
|
alias = a.alias,
|
|
conremark = a.conremark,
|
|
ctime = a.ctime,
|
|
fenghaotime = a.fenghaotime,
|
|
isfenghao = a.isfenghao,
|
|
nickname = a.nickname,
|
|
pkid = a.pkid,
|
|
pyinitial = a.pyinitial,
|
|
type = a.type,
|
|
username = a.username,
|
|
forever_uin = ba.forever_uin,
|
|
lasttime = ba.lasttime,
|
|
uin = ba.uin,
|
|
version = ba.version,
|
|
lastfriendtime = ca.createtime,
|
|
lastmsgtime = da.lasttime
|
|
|
|
};
|
|
if (!string.IsNullOrEmpty(name))
|
|
{
|
|
db = db.Where(m => m.nickname.Contains(name) || m.alias.Contains(name) || m.username.Contains(name));
|
|
}
|
|
|
|
if (!string.IsNullOrEmpty(pager.sort) && !string.IsNullOrEmpty(pager.order))
|
|
{
|
|
if (pager.order == "asc")
|
|
{
|
|
switch (pager.sort)
|
|
{
|
|
case "version": db = db.OrderBy(m => m.version); break;
|
|
case "status": db = db.OrderBy(m => m.lasttime); break;
|
|
case "lasttimestr": db = db.OrderBy(m => m.lasttime); break;
|
|
case "lastmsgtimestr": db = db.OrderBy(m => m.lastmsgtime); break;
|
|
case "lastfriendtimestr": db = db.OrderBy(m => m.lastfriendtime); break;
|
|
default: db = db.OrderBy(m => m.lasttime); break;
|
|
}
|
|
}
|
|
else
|
|
{
|
|
switch (pager.sort)
|
|
{
|
|
case "version": db = db.OrderByDescending(m => m.version); break;
|
|
case "status": db = db.OrderByDescending(m => m.lasttime); break;
|
|
case "lasttimestr": db = db.OrderByDescending(m => m.lasttime); break;
|
|
case "lastmsgtimestr": db = db.OrderByDescending(m => m.lastmsgtime); break;
|
|
case "lastfriendtimestr": db = db.OrderByDescending(m => m.lastfriendtime); break;
|
|
default: db = db.OrderByDescending(m => m.lasttime); break;
|
|
}
|
|
}
|
|
}
|
|
else
|
|
{
|
|
db = db.OrderByDescending(m => m.lasttime);
|
|
}
|
|
Pager pager1 = new Pager() { page = pager.page, rows = pager.limit };
|
|
PagerUtil.SetPager<Wx_WorkAccountModel>(ref db, ref pager1);
|
|
pager.count = pager1.totalRows;
|
|
List<Wx_WorkAccountModel> list = db.ToList();
|
|
DateTime time = DateTime.Now;
|
|
foreach (var item in list)
|
|
{
|
|
if (item.lastfriendtime.HasValue)
|
|
{
|
|
item.lastfriendtimestr = item.lastfriendtime.Value.ToString("yyyy-MM-dd HH:mm:ss");
|
|
}
|
|
if (item.lastmsgtime.HasValue)
|
|
{
|
|
item.lastmsgtimestr = item.lastmsgtime.Value.ToString("yyyy-MM-dd HH:mm:ss");
|
|
}
|
|
|
|
if (item.lasttime.HasValue)
|
|
{
|
|
item.lasttimestr = item.lasttime.Value.ToString("yyyy-MM-dd HH:mm:ss");
|
|
if ((time - item.lasttime.Value).TotalMinutes > 15)//超过15分钟就是离线
|
|
{
|
|
item.isOnlie = 0;
|
|
item.status = "离线";
|
|
}
|
|
else
|
|
{
|
|
|
|
item.isOnlie = 1;
|
|
item.status = "在线";
|
|
}
|
|
}
|
|
}
|
|
return list;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 获取 工作微信好友列表
|
|
/// </summary>
|
|
/// <param name="workUsername">工作微信号</param>
|
|
/// <param name="column">排序</param>
|
|
/// <param name="sort"></param>
|
|
/// <returns></returns>
|
|
public List<WX_WorkRCONTACT> GetRcontactByWorkAccount(string workUsername, string column, string sort)
|
|
{
|
|
List<WX_WorkRCONTACT> list = new List<WX_WorkRCONTACT>();
|
|
try
|
|
{
|
|
string connectStr = Utility.GetSettingByKey("ConnectionStrings:wxContext");
|
|
var param1 = new List<MySqlParameter>()
|
|
{
|
|
new MySqlParameter() { ParameterName = "v_workaccount", DbType = DbType.String, Value =workUsername },
|
|
new MySqlParameter() { ParameterName = "p_column", DbType = DbType.String, Value = column } ,
|
|
new MySqlParameter() { ParameterName = "p_sort", DbType = DbType.String, Value = sort }
|
|
//new MySqlParameter() {ParameterName="v_data",DbType=DbType.Cursor,Direction = ParameterDirection.Output }
|
|
};
|
|
DataTable tab = MySqlDbHelper.DataQueray(connectStr, CommandType.StoredProcedure, "PACK_WX_RCONTACT_GetRcontactByWorkAccount", param1.ToArray()).Tables[0];
|
|
foreach (DataRow item in tab.Rows)
|
|
{
|
|
WX_WorkRCONTACT model = new WX_WorkRCONTACT();
|
|
model.ALIAS = item["ALIAS"].ToString();
|
|
model.CONREMARK = item["CONREMARK"].ToString();
|
|
model.FUSERNAME = item["FUSERNAME"].ToString();
|
|
model.JOBWXUSERNAME = item["JOBWXUSERNAME"].ToString();
|
|
model.LASTCHARTIME = Convert.ToDecimal(item["LASTCHARTIME"]);
|
|
model.NICKNAME = item["NICKNAME"].ToString();
|
|
model.NUSERNAME = item["NUSERNAME"].ToString();
|
|
model.PKID = Convert.ToDecimal(item["PKID"]);
|
|
model.USERNAME = item["USERNAME"].ToString();
|
|
model.WUSERNAME = item["WUSERNAME"].ToString();
|
|
model.isblacklist = Convert.ToDecimal(item["isblacklist"]);
|
|
model.isunservedlist = Convert.ToDecimal(item["isunservedlist"]);
|
|
list.Add(model);
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
LogHelper.Error(ex.ToString());
|
|
}
|
|
return list;
|
|
|
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
/// 获取 工作微信群列表
|
|
/// </summary>
|
|
/// <param name="workUsername"></param>
|
|
/// <param name="column"></param>
|
|
/// <param name="sort"></param>
|
|
/// <returns></returns>
|
|
public List<Wx_WorkGroupRcontact> GetGroupsByWorkAccount(string workUsername, string column, string sort)
|
|
{
|
|
List<Wx_WorkGroupRcontact> list = new List<Wx_WorkGroupRcontact>();
|
|
try
|
|
{
|
|
string connectStr = Utility.GetSettingByKey("ConnectionStrings:wxContext");
|
|
var param1 = new List<MySqlParameter>()
|
|
{
|
|
new MySqlParameter() { ParameterName = "v_workaccount", DbType = DbType.String, Value =workUsername },
|
|
new MySqlParameter() { ParameterName = "p_column", DbType = DbType.String, Value = column } ,
|
|
new MySqlParameter() { ParameterName = "p_sort", DbType = DbType.String, Value = sort }
|
|
//new MySqlParameter() {ParameterName="v_data",DbType=DbType.Cursor,Direction = ParameterDirection.Output }
|
|
};
|
|
DataTable tab = MySqlDbHelper.DataQueray(connectStr, CommandType.StoredProcedure, "PACK_WX_RCONTACT_GetGroupRcontactByWorAccount", param1.ToArray()).Tables[0];
|
|
foreach (DataRow item in tab.Rows)
|
|
{
|
|
Wx_WorkGroupRcontact model = new Wx_WorkGroupRcontact();
|
|
model.chatRoomName = item["chatroomname"].ToString();
|
|
model.createTime = Convert.ToDateTime(item["createtime"]);
|
|
model.groupName = item["groupName"].ToString();
|
|
model.roomowner = item["roomowner"].ToString();
|
|
model.LASTCHARTIME = Convert.ToDecimal(item["LASTCHARTIME"]);
|
|
model.shortName = item["shortname"].ToString();
|
|
list.Add(model);
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
LogHelper.Error(ex.ToString());
|
|
}
|
|
return list;
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
/// 获取好友列表
|
|
/// </summary>
|
|
/// <param name="page"></param>
|
|
/// <param name="jobWxUserName"></param>
|
|
/// <param name="userName"></param>
|
|
/// <param name="alias"></param>
|
|
/// <param name="nickName"></param>
|
|
/// <param name="conRemark"></param>
|
|
/// <param name="stime"></param>
|
|
/// <param name="etime"></param>
|
|
/// <returns></returns>
|
|
public List<Wx_Rcontact_List> GetRcontactList(ref Laypage page, string jobWxUserName, string keyword, string stime, string etime)
|
|
{
|
|
var queryData = from a in _crmContext.Wx_Rcontact
|
|
join b in _crmContext.Wx_WorkAccount on a.jobwxusername equals b.username
|
|
select new Wx_Rcontact_List()
|
|
{
|
|
username = a.username,
|
|
jobwxusername = a.jobwxusername,
|
|
alias = a.alias,
|
|
conremark = a.conremark,
|
|
ctime = a.ctime,
|
|
jobalias = b.alias,
|
|
jobnickname = b.nickname,
|
|
nickname = a.nickname,
|
|
lastchattime = a.lastchattime,
|
|
pkid = a.pkid,
|
|
rctime = a.rctime,
|
|
type = a.type
|
|
};
|
|
|
|
if (!string.IsNullOrWhiteSpace(jobWxUserName))
|
|
{
|
|
jobWxUserName = jobWxUserName.Trim();
|
|
queryData = queryData.Where(m => m.jobwxusername == jobWxUserName);
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(keyword))
|
|
{
|
|
keyword = keyword.Trim();
|
|
queryData = queryData.Where(m => m.username.Contains(keyword) || m.conremark.Contains(keyword) || m.alias.Contains(keyword) || m.nickname.Contains(keyword));
|
|
}
|
|
|
|
if (!string.IsNullOrWhiteSpace(stime))
|
|
{
|
|
DateTime time1 = Convert.ToDateTime(stime);
|
|
queryData = queryData.Where(m => m.ctime >= time1);
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(etime))
|
|
{
|
|
DateTime time2 = Convert.ToDateTime(etime).AddDays(1);
|
|
queryData = queryData.Where(m => m.ctime < time2);
|
|
}
|
|
|
|
queryData = queryData.OrderByDescending(m => m.ctime);
|
|
Pager pager1 = new Pager() { page = page.page, rows = page.limit };
|
|
PagerUtil.SetPager<Wx_Rcontact_List>(ref queryData, ref pager1);
|
|
page.count = pager1.totalRows;
|
|
List<Wx_Rcontact_List> list = queryData.ToList();
|
|
foreach (Wx_Rcontact_List item in list)
|
|
{
|
|
if (item.ctime.HasValue)
|
|
item.ctimestr = item.ctime.Value.ToString("yyyy-MM-dd HH:mm:ss");
|
|
}
|
|
return list;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 显示红包
|
|
/// </summary>
|
|
/// <param name="v_username"></param>
|
|
/// <returns></returns>
|
|
|
|
public List<Wx_HongBaoModel> ShowWxHongBao(ref Laypage page,string v_id, string v_username, DateTime? stime, DateTime? etime)
|
|
{
|
|
List<Wx_HongBaoModel> list = new List<Wx_HongBaoModel>();
|
|
try
|
|
{
|
|
string connectStr = Utility.GetSettingByKey("ConnectionStrings:wxContext");
|
|
var param = new List<MySqlParameter>();
|
|
if (!string.IsNullOrEmpty(v_id))
|
|
param.Add(new MySqlParameter() { ParameterName = "v_id", DbType = DbType.String, Value = v_id });
|
|
else
|
|
param.Add(new MySqlParameter() { ParameterName = "v_id", DbType = DbType.String, Value = DBNull.Value });
|
|
param.Add(new MySqlParameter() { ParameterName = "v_username", DbType = DbType.String, Value = v_username });
|
|
if (stime.HasValue)
|
|
param.Add(new MySqlParameter() { ParameterName = "v_sdate", DbType = DbType.DateTime, Value = stime.Value });
|
|
else
|
|
param.Add(new MySqlParameter() { ParameterName = "v_sdate", DbType = DbType.DateTime, Value = DBNull.Value });
|
|
if (etime.HasValue)
|
|
param.Add(new MySqlParameter() { ParameterName = "v_edate", DbType = DbType.DateTime, Value = etime.Value });
|
|
else
|
|
param.Add(new MySqlParameter() { ParameterName = "v_edate", DbType = DbType.DateTime, Value = DBNull.Value });
|
|
param.Add(new MySqlParameter() { ParameterName = "v_pageindex", DbType = DbType.Int32, Value = page.page });
|
|
param.Add(new MySqlParameter() { ParameterName = "v_pageSize", DbType = DbType.Int32, Value = page.limit });
|
|
DataSet tab = MySqlDbHelper.DataQueray(connectStr, CommandType.StoredProcedure, "PACK_WX_HONGBAO_ShowHongBao", param.ToArray());
|
|
foreach (DataRow item in tab.Tables[0].Rows)
|
|
{
|
|
Wx_HongBaoModel m = new Wx_HongBaoModel();
|
|
m.sendid = item["sendid"].ToString();
|
|
m.sendusername = item["sendusername"].ToString();
|
|
m.sendNickName = item["sendNickName"].ToString();
|
|
m.sendconremark = item["sendconremark"].ToString();
|
|
m.mntype = item["mntype"].ToString();
|
|
m.title = item["title"].ToString();
|
|
m.username = item["username"].ToString();
|
|
m.getNickName = item["getNickName"].ToString();
|
|
m.getconremark = item["getconremark"].ToString();
|
|
if (item["createtime"] != DBNull.Value)
|
|
m.createtime = Convert.ToDateTime(item["createtime"]);
|
|
|
|
if (item["sendTime"] != DBNull.Value)
|
|
m.sendTime = Convert.ToDateTime(item["sendTime"]);
|
|
|
|
list.Add(m);
|
|
}
|
|
|
|
page.count = Convert.ToInt32(tab.Tables[1].Rows[0]["rowcount"]);
|
|
return list;
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
LogHelper.Error(ex.ToString());
|
|
throw;
|
|
}
|
|
}
|
|
|
|
|
|
}
|
|
}
|