/// <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)); } }