/// <summary>
/// 获取用户收藏帖子列表
/// </summary>
/// <param name="aid"></param>
/// <param name="userId"></param>
/// <param name="totalCount"></param>
/// <param name="pageSize"></param>
/// <param name="pageIndex"></param>
/// <returns></returns>
public List <CityMsg> getListMyFavoriteMsg(int aid, int userId, out int totalCount, int pageSize = 10, int pageIndex = 1)
{
string strWhere = $"f.aid={aid} and f.userId={userId} and f.state=0 and f.actionType=0 and msg.state=1";
string sql = $"select msg.*,f.Id as FavoriteId from CityUserFavoriteMsg f left join CityMsg msg on f.msgId=msg.Id where {strWhere} order by addTime desc LIMIT {(pageIndex - 1) * pageSize},{pageSize}";
totalCount = GetMyFavoriteMsgCount(strWhere);
List <CityMsg> listCity_Msg = new List <CityMsg>();
using (var dr = SqlMySql.ExecuteDataReader(Utility.dbEnum.MINIAPP.ToString(), CommandType.Text, sql))
{
while (dr.Read())
{
CityMsg x = CityMsgBLL.SingleModel.GetModel(dr);
if (x != null && x.Id > 0)
{
//获取用户头像
C_UserInfo c_UserInfo = C_UserInfoBLL.SingleModel.GetModel(x.userId);
if (c_UserInfo != null)
{
x.userName = c_UserInfo.NickName;
x.userHeaderImg = c_UserInfo.HeadImgUrl;
}
//获取帖子类别
CityStoreMsgType city_StoreMsgType = CityStoreMsgTypeBLL.SingleModel.GetModel(x.msgTypeId);
if (city_StoreMsgType != null)
{
x.msgTypeName = city_StoreMsgType.name;
}
//根据帖子ID获取其浏览量-收藏量-分享量数据
CityMsgViewFavoriteShare city_MsgViewFavoriteShare = CityMsgViewFavoriteShareBLL.SingleModel.getModelByMsgId(x.Id);
if (city_MsgViewFavoriteShare != null)
{
x.ViewCount = city_MsgViewFavoriteShare.ViewCount;
x.FavoriteCount = city_MsgViewFavoriteShare.FavoriteCount;
x.ShareCount = city_MsgViewFavoriteShare.ShareCount;
x.DzCount = city_MsgViewFavoriteShare.DzCount;
}
x.msgDetail = HttpUtility.HtmlDecode(x.msgDetail);
x.showTimeStr = CommondHelper.GetTimeSpan(DateTime.Now - x.addTime);
x.FavoriteId = (dr["FavoriteId"] != DBNull.Value?Convert.ToInt32(dr["FavoriteId"]):0);//收藏记录ID
listCity_Msg.Add(x);
}
}
}
return(listCity_Msg);
}
public CityMsg getMsg(int aid, int msgId)
{
CityMsg x = base.GetModel($"aid={aid} and Id={msgId}");
if (x != null)
{
//获取用户头像
C_UserInfo c_UserInfo = C_UserInfoBLL.SingleModel.GetModel(x.userId);
if (c_UserInfo != null)
{
x.userName = c_UserInfo.NickName;
x.userHeaderImg = c_UserInfo.HeadImgUrl;
}
//获取帖子类别
CityStoreMsgType city_StoreMsgType = CityStoreMsgTypeBLL.SingleModel.GetModel(x.msgTypeId);
if (city_StoreMsgType != null)
{
x.msgTypeName = city_StoreMsgType.name;
}
//根据帖子ID获取其浏览量-收藏量-分享量数据
CityMsgViewFavoriteShare city_MsgViewFavoriteShare = CityMsgViewFavoriteShareBLL.SingleModel.getModelByMsgId(x.Id);
if (city_MsgViewFavoriteShare != null)
{
x.ViewCount = city_MsgViewFavoriteShare.ViewCount;
x.FavoriteCount = city_MsgViewFavoriteShare.FavoriteCount;
x.ShareCount = city_MsgViewFavoriteShare.ShareCount;
x.DzCount = city_MsgViewFavoriteShare.DzCount;
}
x.showTimeStr = CommondHelper.GetTimeSpan(DateTime.Now - x.addTime);
x.msgDetail = HttpUtility.HtmlDecode(x.msgDetail);
}
return(x);
}
public List <CityMsg> getListByaid(int aid, out int totalCount, int isTop = 0, int pageSize = 10, int pageIndex = 1, string msgTypeName = "", string userName = "", string userPhone = "", string orderWhere = "addTime desc", string appId = "", int Review = -2)
{
string strWhere = $"aid={aid} and state<>-1";
if (isTop == 0)
{
//表示普通信息 非置顶信息
strWhere += " and topDay=0 and IsDoNotTop<>1";
}
else if (isTop == 1)
{
List <string> listMsgId = new List <string>();
List <CityMorders> listCityMorder = new CityMordersBLL().GetList($"MinisnsId={aid} and payment_status=1 and Status=1");
if (listCityMorder != null && listCityMorder.Count > 0)
{
foreach (CityMorders item in listCityMorder)
{
listMsgId.Add(item.CommentId.ToString());
}
}
else
{
listMsgId.Add("0");
}
strWhere += $" and (topDay>0 or IsDoNotTop=1) and (Id in({string.Join(",",listMsgId)}) or IsDoTop=1) ";//置顶信息 去CityMorders表找出已经付款成功的帖子信息ID
}
if (Review != -2)
{
strWhere += $" and Review={Review} ";
}
#region 根据类别名称查询类别
List <CityStoreMsgType> listMsgType = new List <CityStoreMsgType>();
if (!string.IsNullOrEmpty(msgTypeName))
{
//类别
int count = 0;
string typeIds = string.Empty;
List <int> listIds = new List <int>();
listMsgType = CityStoreMsgTypeBLL.SingleModel.getListByaid(aid, out count, 1000, 1, msgTypeName);
if (listMsgType != null && listMsgType.Count > 0)
{
listIds.AddRange(listMsgType.Select(x => x.Id));
}
else
{
listIds.Add(0);
}
typeIds = string.Join(",", listIds);
if (!string.IsNullOrEmpty(typeIds))
{
strWhere += $" and msgTypeId in ({typeIds})";
}
}
#endregion
#region 根据用户昵称模糊匹配用户列表
List <C_UserInfo> listUserInfo = new List <C_UserInfo>();
if (!string.IsNullOrEmpty(userName))
{
//用户
string userIds = string.Empty;
List <int> listIds = new List <int>();
listUserInfo = C_UserInfoBLL.SingleModel.GetUserListByNickName(userName, appId);
if (listUserInfo != null && listUserInfo.Count > 0)
{
listIds.AddRange(listUserInfo.Select(x => x.Id));
}
else
{
listIds.Add(0);
}
userIds = string.Join(",", listIds);
if (!string.IsNullOrEmpty(userIds))
{
strWhere += $" and userId in ({userIds})";
}
}
#endregion
List <MySqlParameter> parameters = new List <MySqlParameter>();
if (!string.IsNullOrEmpty(userPhone))
{
parameters.Add(new MySqlParameter("@userPhone", $"%{userPhone}%"));
strWhere += " and phone like @userPhone";
}
totalCount = base.GetCount(strWhere, parameters.ToArray());
List <CityMsg> listCity_Msg = base.GetListByParam(strWhere, parameters.ToArray(), pageSize, pageIndex, "*", orderWhere);
if (listCity_Msg != null && listCity_Msg.Count > 0)
{
listCity_Msg.ForEach(x =>
{
if (listUserInfo != null && listUserInfo.Count > 0)
{
x.userName = listUserInfo.FirstOrDefault(u => u.Id == x.userId).NickName;
x.userHeaderImg = listUserInfo.FirstOrDefault(u => u.Id == x.userId).HeadImgUrl;
}
else
{
C_UserInfo c_UserInfo = C_UserInfoBLL.SingleModel.GetModel(x.userId);
if (c_UserInfo != null)
{
x.userName = c_UserInfo.NickName;
x.userHeaderImg = c_UserInfo.HeadImgUrl;
}
}
if (listMsgType != null && listMsgType.Count > 0)
{
x.msgTypeName = listMsgType.FirstOrDefault(t => t.Id == x.msgTypeId).name;
}
else
{
CityStoreMsgType city_StoreMsgType = CityStoreMsgTypeBLL.SingleModel.GetModel(x.msgTypeId);
if (city_StoreMsgType != null)
{
x.msgTypeName = city_StoreMsgType.name;
}
}
x.msgDetail = HttpUtility.HtmlDecode(x.msgDetail);
});
}
return(listCity_Msg);
}
public List <CityMsg> getListCity_Msg(int aid, out int totalCount, int pageSize = 10, int pageIndex = 1, int msgTypeId = 0, string keyMsg = "", int orderType = 0, double ws_lat = 0, double ws_lng = 0)
{
string strWhere = $" aid={aid} and state=1 ";
string strWhereCount = $" aid={aid} and state=1 ";
if (msgTypeId > 0)
{
strWhere += $" and msgTypeId={msgTypeId}";
strWhereCount += $" and msgTypeId={msgTypeId}";
}
List <MySqlParameter> parameters = new List <MySqlParameter>();
if (!string.IsNullOrEmpty(keyMsg))
{
strWhere += $" and msgDetail like @keyMsg ";
strWhereCount += $" and msgDetail like @keyMsg ";
parameters.Add(new MySqlParameter("keyMsg", $"%{keyMsg}%"));
}
//TODO 测试时的时间单位为分钟MINUTE 线上为天DAY
string sql = $@"SELECT * FROM( SELECT * FROM (SELECT *,(DATE_ADD(addtime,INTERVAL topDay DAY)-now()) as tspan from CityMsg where (DATE_ADD(addtime,INTERVAL topDay DAY)-now())>0 and { strWhere} ORDER BY addtime desc LIMIT 100000)t1
UNION
SELECT* FROM(
SELECT*, (DATE_ADD(addtime, INTERVAL topDay DAY)-now()) as tspan from CityMsg where (DATE_ADD(addtime, INTERVAL topDay DAY) - now()) <= 0 and { strWhere} ORDER BY addtime desc LIMIT 100000) t2
) t3 limit {(pageIndex - 1) * pageSize},{pageSize}";
if (orderType > 0)
{//表示附近
sql = $@"SELECT * FROM( SELECT * FROM (SELECT *,(DATE_ADD(addtime,INTERVAL topDay DAY)-now()) as tspan,ROUND(6378.138*2*ASIN(SQRT(POW(SIN(({ws_lat}*PI()/180-lat*PI()/180)/2),2)+COS({ws_lat}*PI()/180)*COS(lat*PI()/180)*POW(SIN(({ws_lng}*PI()/180-lng*PI()/180)/2),2)))*1000) AS distance from CityMsg where (DATE_ADD(addtime,INTERVAL topDay DAY)-now())>0 and { strWhere} ORDER BY distance asc,addtime desc LIMIT 100000)t1
UNION
SELECT* FROM(
SELECT*, (DATE_ADD(addtime, INTERVAL topDay DAY)-now()) as tspan,ROUND(6378.138*2*ASIN(SQRT(POW(SIN(({ws_lat}*PI()/180-lat*PI()/180)/2),2)+COS({ws_lat}*PI()/180)*COS(lat*PI()/180)*POW(SIN(({ws_lng}*PI()/180-lng*PI()/180)/2),2)))*1000) AS distance from CityMsg where (DATE_ADD(addtime, INTERVAL topDay DAY) - now()) <= 0 and { strWhere} ORDER BY distance asc, addtime desc LIMIT 100000) t2
) t3 limit {(pageIndex - 1) * pageSize},{pageSize}";
}
List <CityMsg> listCity_Msg = base.GetListBySql(sql, parameters.ToArray());
if (listCity_Msg != null && listCity_Msg.Count > 0)
{
string userIds = string.Join(",", listCity_Msg.Select(s => s.userId).Distinct());
List <C_UserInfo> userInfoList = C_UserInfoBLL.SingleModel.GetListByIds(userIds);
string cityMsgIds = string.Join(",", listCity_Msg.Select(s => s.msgTypeId));
List <CityStoreMsgType> cityStoreMsgTypeList = CityStoreMsgTypeBLL.SingleModel.GetListByIds(cityMsgIds);
string msgShareIds = string.Join(",", listCity_Msg.Select(s => s.Id));
List <CityMsgViewFavoriteShare> cityMsgViewFavoriteShareList = CityMsgViewFavoriteShareBLL.SingleModel.GetListByMsgIds(msgShareIds);
listCity_Msg.ForEach(x =>
{
//获取用户头像 昵称
C_UserInfo c_UserInfo = userInfoList?.FirstOrDefault(f => f.Id == x.userId);
if (c_UserInfo != null)
{
x.userName = c_UserInfo.NickName;
x.userHeaderImg = c_UserInfo.HeadImgUrl;
}
//获取帖子类别
CityStoreMsgType city_StoreMsgType = cityStoreMsgTypeList?.FirstOrDefault(f => f.Id
== x.msgTypeId);
if (city_StoreMsgType != null)
{
x.msgTypeName = city_StoreMsgType.name;
}
//根据帖子ID获取其浏览量-收藏量-分享量数据
CityMsgViewFavoriteShare city_MsgViewFavoriteShare = cityMsgViewFavoriteShareList?.FirstOrDefault(f => f.Id == x.Id);
if (city_MsgViewFavoriteShare != null)
{
x.ViewCount = city_MsgViewFavoriteShare.ViewCount;
x.FavoriteCount = city_MsgViewFavoriteShare.FavoriteCount;
x.ShareCount = city_MsgViewFavoriteShare.ShareCount;
x.DzCount = city_MsgViewFavoriteShare.DzCount;
}
x.showTimeStr = CommondHelper.GetTimeSpan(DateTime.Now - x.addTime);
x.msgDetail = HttpUtility.HtmlDecode(x.msgDetail);
});
}
totalCount = base.GetCount(strWhereCount, parameters.ToArray());
return(listCity_Msg);
}
/// <summary>
/// 根据用户Id获取该用户的帖子信息
/// </summary>
/// <param name="aid"></param>
/// <param name="totalCount"></param>
/// <param name="userId"></param>
/// <param name="pageSize"></param>
/// <param name="pageIndex"></param>
/// <param name="orderType">帖子排序字段 默认为0表示按照时间降序排列 1表示先按照置顶排序 然后再按照时间</param>
/// <returns></returns>
public List <CityMsg> getListByUserId(int aid, out int totalCount, int userId, int pageSize = 10, int pageIndex = 1, int orderType = 0)
{
List <string> listMsgId = new List <string>();
List <CityMorders> listCityMorder = new CityMordersBLL().GetList($"MinisnsId={aid} and payment_status=1 and Status=1");
if (listCityMorder != null && listCityMorder.Count > 0)
{
foreach (CityMorders item in listCityMorder)
{
listMsgId.Add(item.CommentId.ToString());
}
}
else
{
listMsgId.Add("0");
}
string strWhere = $"aid={aid} and userId={userId} and ( (state<>-1 and topDay=0) or (state<>-1 and Id in({string.Join(",",listMsgId)}))) ";
string orderWhere = " addTime desc";//默认按照时间降序排序
//if (orderType == 1)
//{
// orderWhere = " topDay desc,addTime desc";//先按照置顶排序 然后再按照时间
// strWhere += "";
//}
totalCount = base.GetCount(strWhere);
List <CityMsg> listCity_Msg = base.GetList(strWhere, pageSize, pageIndex, "*", orderWhere);
if (listCity_Msg != null && listCity_Msg.Count > 0)
{
string userIds = string.Join(",", listCity_Msg.Select(s => s.userId).Distinct());
List <C_UserInfo> userInfoList = C_UserInfoBLL.SingleModel.GetListByIds(userIds);
string cityMsgIds = string.Join(",", listCity_Msg.Select(s => s.msgTypeId));
List <CityStoreMsgType> cityStoreMsgTypeList = CityStoreMsgTypeBLL.SingleModel.GetListByIds(cityMsgIds);
string msgShareIds = string.Join(",", listCity_Msg.Select(s => s.Id));
List <CityMsgViewFavoriteShare> cityMsgViewFavoriteShareList = CityMsgViewFavoriteShareBLL.SingleModel.GetListByMsgIds(msgShareIds);
listCity_Msg.ForEach(x =>
{
//获取用户头像
C_UserInfo c_UserInfo = userInfoList?.FirstOrDefault(f => f.Id == x.userId);
if (c_UserInfo != null)
{
x.userName = c_UserInfo.NickName;
x.userHeaderImg = c_UserInfo.HeadImgUrl;
}
//获取帖子类别
CityStoreMsgType city_StoreMsgType = cityStoreMsgTypeList?.FirstOrDefault(f => f.Id == x.msgTypeId);
if (city_StoreMsgType != null)
{
x.msgTypeName = city_StoreMsgType.name;
}
//根据帖子ID获取其浏览量-收藏量-分享量数据
CityMsgViewFavoriteShare city_MsgViewFavoriteShare = cityMsgViewFavoriteShareList?.FirstOrDefault(f => f.Id == x.Id);
if (city_MsgViewFavoriteShare != null)
{
x.ViewCount = city_MsgViewFavoriteShare.ViewCount;
x.FavoriteCount = city_MsgViewFavoriteShare.FavoriteCount;
x.ShareCount = city_MsgViewFavoriteShare.ShareCount;
x.DzCount = city_MsgViewFavoriteShare.DzCount;
}
x.showTimeStr = CommondHelper.GetTimeSpan(DateTime.Now - x.addTime);
x.msgDetail = HttpUtility.HtmlDecode(x.msgDetail);
});
}
return(listCity_Msg);
}
public ActionResult AddMsgViewFavoriteShare()
{
returnObj = new Return_Msg_APP();
string appId = Context.GetRequest("appId", string.Empty);
int msgId = Context.GetRequestInt("msgId", 0);
int userId = Context.GetRequestInt("userId", 0);
int actionType = Context.GetRequestInt("actionType", 0);//默认为0 表示浏览量 1表示收藏 2 表示分享成功 3表示点赞
if (string.IsNullOrEmpty(appId) || msgId <= 0)
{
returnObj.code = "200";
returnObj.Msg = "参数错误";
return(Json(returnObj));
}
XcxAppAccountRelation r = _xcxAppAccountRelationBLL.GetModelByAppid(appId);
if (r == null)
{
returnObj.code = "200";
returnObj.Msg = "小程序未授权";
return(Json(returnObj));
}
C_UserInfo userInfo = new C_UserInfo();
if (actionType == 3 || actionType == 1)
{
if (userId <= 0)
{
returnObj.code = "200";
returnObj.Msg = "请先授权";
return(Json(returnObj));
}
userInfo = C_UserInfoBLL.SingleModel.GetModel(userId);
if (userInfo == null)
{
returnObj.code = "200";
returnObj.Msg = "登录信息过期";
return(Json(returnObj));
}
}
CityMsg cityMsg = CityMsgBLL.SingleModel.GetModel(msgId);
if (cityMsg == null)
{
returnObj.code = "200";
returnObj.Msg = "帖子不存在";
return(Json(returnObj));
}
string msgTypeName = string.Empty;
CityStoreMsgType cityStoreMsgType = CityStoreMsgTypeBLL.SingleModel.GetModel(cityMsg.msgTypeId);
if (cityStoreMsgType != null)
{
msgTypeName = cityStoreMsgType.name;
}
CityMsgViewFavoriteShare mode = CityMsgViewFavoriteShareBLL.SingleModel.getModelByMsgId(msgId);
TransactionModel tranModel = new TransactionModel();
if (mode == null)
{
//新增一条数据
mode = new CityMsgViewFavoriteShare();
switch (actionType)
{
case 1: //表示收藏
mode.FavoriteCount = 1;
if (CityUserFavoriteMsgBLL.SingleModel.getCity_UserFavoriteMsg(r.Id, msgId, userId) == null)
{
tranModel.Add(CityUserFavoriteMsgBLL.SingleModel.BuildAddSql(new CityUserFavoriteMsg()
{ //用户-帖子收藏记录
aid = r.Id,
userId = userId,
msgId = msgId,
addTime = DateTime.Now
}));
}
break;
case 2: //表示分享成功
mode.ShareCount = 1;
break;
case 3: //表示点赞成功
if (CityUserFavoriteMsgBLL.SingleModel.getCity_UserFavoriteMsg(r.Id, msgId, userId, 1) == null)
{
mode.DzCount = 1;
CityUserMsg city_UserMsg = new CityUserMsg();
city_UserMsg.addTime = DateTime.Now;
city_UserMsg.aid = r.Id;
city_UserMsg.fromUserId = userId;
city_UserMsg.toUserId = cityMsg.userId;
city_UserMsg.updateTime = DateTime.Now;
city_UserMsg.msgBody = string.Format(WebSiteConfig.City_UserDzMsgTemplate, userInfo.NickName, $"'#{msgTypeName}#{(cityMsg.msgDetail.Length > 10 ? cityMsg.msgDetail.Substring(0, 10) : cityMsg.msgDetail)}'");
city_UserMsg.msgType = 1;
tranModel.Add(CityUserMsgBLL.SingleModel.BuildAddSql(city_UserMsg));
tranModel.Add(CityUserFavoriteMsgBLL.SingleModel.BuildAddSql(new CityUserFavoriteMsg()
{ //用户-帖子点赞记录
aid = r.Id,
userId = userId,
msgId = msgId,
addTime = DateTime.Now,
actionType = 1
}));
}
break;
default: //默认为0 表示浏览量
mode.ViewCount = 1;
break;
}
mode.msgId = msgId;
mode.addTime = DateTime.Now;
mode.aid = r.Id;
tranModel.Add(CityMsgViewFavoriteShareBLL.SingleModel.BuildAddSql(mode));
if (tranModel.sqlArray == null || tranModel.sqlArray.Length <= 0 || !CityMsgViewFavoriteShareBLL.SingleModel.ExecuteTransactionDataCorect(tranModel.sqlArray))
{
returnObj.code = "200";
returnObj.Msg = "新增失败";
return(Json(returnObj));
}
returnObj.isok = true;
returnObj.code = "200";
returnObj.Msg = "新增成功";
return(Json(returnObj));
}
else
{
string updateFiled = "ViewCount";
//更新
switch (actionType)
{
case 1: //表示收藏
if (CityUserFavoriteMsgBLL.SingleModel.getCity_UserFavoriteMsg(r.Id, msgId, userId) == null)
{
mode.FavoriteCount += 1;
updateFiled = "FavoriteCount";
tranModel.Add(CityUserFavoriteMsgBLL.SingleModel.BuildAddSql(new CityUserFavoriteMsg()
{ //用户-帖子收藏记录
aid = r.Id,
userId = userId,
msgId = msgId,
addTime = DateTime.Now
}));
}
break;
case 2: //表示分享成功
mode.ShareCount += 1;
updateFiled = "ShareCount";
break;
case 3: //表示点赞成功
if (CityUserFavoriteMsgBLL.SingleModel.getCity_UserFavoriteMsg(r.Id, msgId, userId, 1) == null)
{
mode.DzCount += 1;
updateFiled = "DzCount";
//发消息
CityUserMsg city_UserMsg = new CityUserMsg();
city_UserMsg.addTime = DateTime.Now;
city_UserMsg.aid = r.Id;
city_UserMsg.fromUserId = userId;
city_UserMsg.toUserId = cityMsg.userId;
city_UserMsg.updateTime = DateTime.Now;
city_UserMsg.msgBody = string.Format(WebSiteConfig.City_UserDzMsgTemplate, userInfo.NickName, $"'#{msgTypeName}#{(cityMsg.msgDetail.Length > 10 ? cityMsg.msgDetail.Substring(0, 10) : cityMsg.msgDetail)}'");
city_UserMsg.msgType = 1;
tranModel.Add(CityUserMsgBLL.SingleModel.BuildAddSql(city_UserMsg));
tranModel.Add(CityUserFavoriteMsgBLL.SingleModel.BuildAddSql(new CityUserFavoriteMsg()
{ //用户-帖子点赞记录
aid = r.Id,
userId = userId,
msgId = msgId,
addTime = DateTime.Now,
actionType = 1
}));
}
break;
default: //默认为0 表示浏览量
mode.ViewCount += 1;
break;
}
tranModel.Add(CityMsgViewFavoriteShareBLL.SingleModel.BuildUpdateSql(mode, updateFiled));
if (tranModel.sqlArray == null || tranModel.sqlArray.Length <= 0 || !CityMsgViewFavoriteShareBLL.SingleModel.ExecuteTransactionDataCorect(tranModel.sqlArray))
{
returnObj.code = "200";
returnObj.Msg = "更新失败";
return(Json(returnObj));
}
returnObj.isok = true;
returnObj.code = "200";
returnObj.Msg = "更新成功";
return(Json(returnObj));
}
}
