/// <summary> /// 商品列表 /// </summary> /// <param name="model"></param> /// <returns></returns> public JsonResult List(SearchSP_ProductCommentModel model) { //SearchAC_OperateLogModel logmodel = new SearchAC_OperateLogModel(); model.PagedIndex = model.PagedIndex == 0 ? 0 : model.PagedIndex; model.PagedSize = model.PagedSize == 0 ? 10 : model.PagedSize; model.LanguageID = ACultureHelper.GetLanguageID; int total = 0; //查询列表 var result = this._sp_ProductCommentService.GetSP_ProductCommentList(model); List <SP_ProductCommentModel> ds = result.Data; var data = new { rows = ds, total = result.Data.TotalCount, }; return(Json(data, JsonRequestBehavior.AllowGet)); }
/// <summary> /// 商品加载数据 /// </summary> /// <param name="id"></param> /// <returns></returns> public ActionResult Create(long?id) { SP_ProductCommentModel model = new SP_ProductCommentModel(); if (id.HasValue) { SearchSP_ProductCommentModel smodel = new SearchSP_ProductCommentModel(); smodel.ProductCommentId = id.Value; smodel.PagedIndex = 0; smodel.PagedSize = 100; //查询列表 List <SP_ProductCommentModel> List = this._sp_ProductCommentService.GetSP_ProductCommentList(smodel).Data; if (List != null && List.Count > 0) { model = List[0]; } } return(PartialView(model)); }
/// <summary> /// 获取商品评论列表 /// </summary> /// <param name="parentId"></param> /// <returns>商品评论列表</returns> public ResultModel GetSP_ProductCommentList(SearchSP_ProductCommentModel model) { var tb = base._database.Db.SP_ProductComment; var where = new SimpleExpression(1, 1, SimpleExpressionType.Equal); if (model.OrderId != 0) { //订单ID where = new SimpleExpression(where, tb.OrderId == model.OrderId, SimpleExpressionType.And); } if (model.UserID != 0) { //用户ID where = new SimpleExpression(where, tb.UserID == model.UserID, SimpleExpressionType.And); } if (model.ProductId != null && model.ProductId != 0) { //商品id where = new SimpleExpression(where, tb.ProductId == model.ProductId, SimpleExpressionType.And); } if (model.CheckBy != null && !string.IsNullOrEmpty(model.CheckBy.Trim())) { //审核人名称 where = new SimpleExpression(where, tb.CheckBy.Like("%" + model.CheckBy + "%"), SimpleExpressionType.And); } if (model.Phone != null && !string.IsNullOrEmpty(model.Phone)) { //手机 where = new SimpleExpression(where, _database.Db.YH_User.Phone.Like("%" + model.Phone + "%"), SimpleExpressionType.And); } if (model.Email != null && !string.IsNullOrEmpty(model.Email.Trim())) { //Email where = new SimpleExpression(where, _database.Db.YH_User.Email.Like("%" + model.Email + "%"), SimpleExpressionType.And); } if (model.BeginCommentDT != null) { //评论开始时间 where = new SimpleExpression(where, tb.CommentDT >= model.BeginCommentDT, SimpleExpressionType.And); } if (model.EndCommentDT != null) { //评论结束 时间 where = new SimpleExpression(where, tb.CommentDT < model.EndCommentDT, SimpleExpressionType.And); } if (model.BeginCheckDT != null) { //开始审核时间 where = new SimpleExpression(where, tb.CheckDT >= model.BeginCheckDT, SimpleExpressionType.And); } if (model.EndCheckDT != null) { //结束审核时间 where = new SimpleExpression(where, tb.CheckDT < model.EndCheckDT, SimpleExpressionType.And); } if (model.CheckStatus != 0) { //审核状态 where = new SimpleExpression(where, tb.CheckStatus == model.CheckStatus, SimpleExpressionType.And); } if (model.LanguageID > 0) { //语言 where = new SimpleExpression(where, _database.Db.Product_Lang.LanguageID == model.LanguageID, SimpleExpressionType.And); } if (model.ProductCommentId > 0) { //评论ID where = new SimpleExpression(where, tb.ProductCommentId == model.ProductCommentId, SimpleExpressionType.And); } // 1:好评;2:中评;3:差评 if (model.typeLevel != null && model.typeLevel.HasValue) { switch (model.typeLevel.Value) { case 1: where = new SimpleExpression(where, tb.CommentLevel >= 4, SimpleExpressionType.And); break; case 2: where = new SimpleExpression(where, tb.CommentLevel >= 2 && tb.CommentLevel < 4, SimpleExpressionType.And); break; case 3: where = new SimpleExpression(where, tb.CommentLevel >= 0 && tb.CommentLevel < 2, SimpleExpressionType.And); break; default: break; } } dynamic cl; dynamic pc; var query = tb .Query() .LeftJoin(_database.Db.Product_Lang, out cl) .On(_database.Db.Product_Lang.ProductId == tb.ProductId) .LeftJoin(_database.Db.YH_User, out pc) .On(_database.Db.YH_User.UserID == tb.UserID) .Select( tb.ProductCommentId, tb.OrderId, tb.UserID, tb.ProductId, tb.CommentLevel, tb.CommentContent, tb.CommentDT, tb.IsAnonymous, tb.ReplyBy, tb.ReplyDT, tb.ReplyContent, tb.CheckBy, tb.CheckStatus, tb.CheckDT, cl.ProductName, pc.Account, pc.NickName, pc.Phone, //商品评价人显示手机号 pc.Email, pc.HeadImageUrl ) .Where(where) .OrderByCommentDTDescending(); var result = new ResultModel() { Data = new SimpleDataPagedList <SP_ProductCommentModel>(query, model.PagedIndex, model.PagedSize) }; return(result); }
/// <summary> /// 获取商品评论列表 /// </summary> /// <param name="parentId"></param> /// <returns>商品评论列表</returns> public ResultModel GetProductCommentList(SearchSP_ProductCommentModel model) { StringBuilder builder = new StringBuilder(); builder.Append(" where 1=1"); if (model.OrderId != 0) { builder.Append(" and tb.OrderId=" + model.OrderId.ToString()); } if (model.UserID != 0) { builder.Append(" and tb.UserID=" + model.UserID.ToString()); } if (model.ProductId != null && model.ProductId != 0) { builder.Append(" and tb.ProductId=" + model.ProductId.ToString()); } if (!string.IsNullOrEmpty(model.CheckBy)) { builder.Append(" and tb.CheckBy like '%" + model.CheckBy.Replace("'", "''") + "%'"); } if (model.BeginCommentDT != null) { builder.Append(" and tb.CommentDT>='" + model.BeginCommentDT.Value.ToString("yyyy-MM-dd") + "'"); } if (model.EndCommentDT != null) { builder.Append(" and tb.CommentDT<'" + model.EndCommentDT.Value.ToString("yyyy-MM-dd") + "'"); } if (model.BeginCheckDT != null) { builder.Append(" and tb.CheckDT>='" + model.BeginCheckDT.Value.ToString("yyyy-MM-dd") + "'"); } if (model.EndCheckDT != null) { builder.Append(" and tb.CheckDT<'" + model.EndCheckDT.Value.ToString("yyyy-MM-dd") + "'"); } if (model.CheckStatus != 0) { builder.Append(" and tb.CheckStatus=" + model.CheckStatus.ToString()); } if (model.LanguageID > 0) { builder.Append(" and c1.LanguageID=" + model.LanguageID.ToString()); } if (model.ProductCommentId > 0) { builder.Append(" and tb.ProductCommentId=" + model.ProductCommentId.ToString()); } if (model.CommentLevel > 0) { if (model.CommentLevel == 1) { builder.Append(" and tb.CommentLevel > 3"); } if (model.CommentLevel == 2) { builder.Append(" and tb.CommentLevel <= 3 and tb.CommentLevel >= 2"); } if (model.CommentLevel == 3) { builder.Append(" and tb.CommentLevel <= 1"); } } string startindex = (model.PagedIndex * model.PagedSize + 1).ToString(); string endindex = ((model.PagedIndex + 1) * model.PagedSize).ToString(); string sql = @"select ProductCommentId,OrderId,UserID,ProductId,CommentLevel,CommentContent,IsAnonymous,ReplyBy,ReplyDT,ReplyContent,CheckBy,Email,HeadImageUrl, CheckStatus,CheckDT,CommentDT,ProductName,Account,Phone,PicUrl,(case when row between " + startindex + " and " + endindex + " then " + @"stuff((select ','+convert(varchar(10),Labels) from SP_ProductComment_Labels where ProductCommentId = x.ProductCommentId for xml path('')),1,1,'') else 0 end) as LablesStr from (select row_number() over(order by tb.CommentDT desc) as row,tb.*,c1.ProductName,pc.Account,pc.Phone,pp.PicUrl,pc.Email,pc.HeadImageUrl from SP_ProductComment tb left join Product_Lang c1 on c1.ProductId = tb.ProductId left join ProductPic pp on pp.ProductId = tb.ProductId and pp.Flag=1 left join YH_User pc on pc.UserID = tb.UserID " + builder.ToString() + ") x"; List <dynamic> res = this._database.RunSqlQuery(x => x.ToResultSets(sql))[0]; List <ProductCommentModel> list = res.Skip(model.PagedIndex * model.PagedSize).Take(model.PagedSize).ToEntity <ProductCommentModel>(); return(new ResultModel() { Data = list }); #region del by liujc /* * var query = tb * .Query() * .LeftJoin(cl) * .On(cl.ProductId == tb.ProductId) * .LeftJoin(pp) * .On(pp.ProductId == tb.ProductId) * .LeftJoin(pc) * .On(pc.UserID == tb.UserID) * .Select( * tb.ProductCommentId,tb.OrderId,tb.UserID,tb.ProductId,tb.CommentLevel,tb.CommentContent,tb.IsAnonymous,tb.ReplyBy,tb.ReplyDT,tb.ReplyContent,tb.CheckBy, * tb.CheckStatus,tb.CheckDT,tb.CommentDT,cl.ProductName,pc.Account,pc.Phone, //商品评价人显示手机号 * pp.PicUrl * ). * With(base._database.Db.SP_ProductComment.SP_ProductComment_Labels). * Where(base._database.Db.SP_ProductComment.SP_ProductComment_Labels.ProductCommentId == base._database.Db.SP_ProductComment.ProductId) * .Where(where) * .OrderByCommentDTDescending(); * * * var tb = base._database.Db.SP_ProductComment; * var cl = base._database.Db.Product_Lang; * var pp = base._database.Db.ProductPic; * var pc = base._database.Db.YH_User; * //var where = new SimpleExpression(1, 1, SimpleExpressionType.Equal); * * //图片 * var where = new SimpleExpression(pp.Flag, 1, SimpleExpressionType.Equal); * //dynamic cl; * //dynamic pc; * //dynamic pp; * * if (model.OrderId != 0) * { * //订单ID * where = new SimpleExpression(where, tb.OrderId == model.OrderId, SimpleExpressionType.And); * } * if (model.UserID != 0) * { * //用户ID * where = new SimpleExpression(where, tb.UserID == model.UserID, SimpleExpressionType.And); * } * if (model.ProductId != null && model.ProductId != 0) * { * //商品id * where = new SimpleExpression(where, tb.ProductId == model.ProductId, SimpleExpressionType.And); * } * if (model.CheckBy != null && string.IsNullOrEmpty(model.CheckBy)) * { * //审核人名称 * where = new SimpleExpression(where, tb.CheckBy.Like("%" + model.CheckBy + "%"), SimpleExpressionType.And); * } * * if (model.BeginCommentDT != null) * { * //评论开始时间 * where = new SimpleExpression(where, tb.CommentDT >= model.BeginCommentDT, SimpleExpressionType.And); * } * * if (model.EndCommentDT != null) * { * //评论结束 时间 * where = new SimpleExpression(where, tb.CommentDT < model.EndCommentDT, SimpleExpressionType.And); * } * * if (model.BeginCheckDT != null) * { * //开始审核时间 * where = new SimpleExpression(where, tb.CheckDT >= model.BeginCheckDT, SimpleExpressionType.And); * } * * if (model.EndCheckDT != null) * { * //结束审核时间 * where = new SimpleExpression(where, tb.CheckDT < model.EndCheckDT, SimpleExpressionType.And); * } * * if (model.CheckStatus != 0) * { * //审核状态 * where = new SimpleExpression(where, tb.CheckStatus == model.CheckStatus, SimpleExpressionType.And); * } * * if (model.LanguageID > 0) * { * //语言 * where = new SimpleExpression(where, cl.LanguageID == model.LanguageID, SimpleExpressionType.And); * } * * * * if (model.ProductCommentId > 0) * { * //评论ID * where = new SimpleExpression(where, tb.ProductCommentId == model.ProductCommentId, SimpleExpressionType.And); * } * * if (model.CommentLevel > 0) * { * //评价等级(0全部,1好评,2中评,3差评) * if (model.CommentLevel == 1) * { * where = new SimpleExpression(where, tb.CommentLevel > 3, SimpleExpressionType.And); * } * if (model.CommentLevel == 2) * { * where = new SimpleExpression(where, tb.CommentLevel <= 3, SimpleExpressionType.And); * where = new SimpleExpression(where, tb.CommentLevel >= 2, SimpleExpressionType.And); * } * if (model.CommentLevel == 3) * { * where = new SimpleExpression(where, tb.CommentLevel <= 1, SimpleExpressionType.And); * } * } * * * * var query = tb * .Query() * .LeftJoin(cl) * .On(cl.ProductId == tb.ProductId) * .LeftJoin(pp) * .On(pp.ProductId == tb.ProductId) * .LeftJoin(pc) * .On(pc.UserID == tb.UserID) * .Select( * tb.ProductCommentId,tb.OrderId,tb.UserID,tb.ProductId,tb.CommentLevel,tb.CommentContent,tb.IsAnonymous,tb.ReplyBy,tb.ReplyDT,tb.ReplyContent,tb.CheckBy, * tb.CheckStatus,tb.CheckDT,tb.CommentDT,cl.ProductName,pc.Account,pc.Phone, //商品评价人显示手机号 * pp.PicUrl * ). * .Where(where) * .OrderByCommentDTDescending(); * * var result = new ResultModel() * { * Data = new SimpleDataPagedList<ProductCommentModel>(query, model.PagedIndex, model.PagedSize) * * }; * * return result; * */ #endregion }
/// <summary> /// 获取商品评论列表 /// </summary> /// <param name="parentId"></param> /// <returns>商品评论列表</returns> public ResultModel GetProductCommentList(SearchSP_ProductCommentModel model, out int TotalCnt) { StringBuilder builder = new StringBuilder(); builder.Append(" where 1=1"); if (model.OrderId != 0) { builder.Append(" and tb.OrderId=" + model.OrderId.ToString()); } if (model.UserID != 0) { builder.Append(" and tb.UserID=" + model.UserID.ToString()); } if (model.ProductId != null && model.ProductId != 0) { builder.Append(" and tb.ProductId=" + model.ProductId.ToString()); } if (!string.IsNullOrEmpty(model.CheckBy)) { builder.Append(" and tb.CheckBy like '%" + model.CheckBy.Replace("'", "''") + "%'"); } if (model.BeginCommentDT != null) { builder.Append(" and tb.CommentDT>='" + model.BeginCommentDT.Value.ToString("yyyy-MM-dd") + "'"); } if (model.EndCommentDT != null) { builder.Append(" and tb.CommentDT<'" + model.EndCommentDT.Value.ToString("yyyy-MM-dd") + "'"); } if (model.BeginCheckDT != null) { builder.Append(" and tb.CheckDT>='" + model.BeginCheckDT.Value.ToString("yyyy-MM-dd") + "'"); } if (model.EndCheckDT != null) { builder.Append(" and tb.CheckDT<'" + model.EndCheckDT.Value.ToString("yyyy-MM-dd") + "'"); } if (model.CheckStatus != 0) { builder.Append(" and tb.CheckStatus=" + model.CheckStatus.ToString()); } if (model.LanguageID > 0) { builder.Append(" and c1.LanguageID=" + model.LanguageID.ToString()); } if (model.ProductCommentId > 0) { builder.Append(" and tb.ProductCommentId=" + model.ProductCommentId.ToString()); } if (model.CommentLevel > 0) { if (model.CommentLevel == 1) { builder.Append(" and tb.CommentLevel > 3"); } if (model.CommentLevel == 2) { builder.Append(" and tb.CommentLevel <= 3 and tb.CommentLevel >= 2"); } if (model.CommentLevel == 3) { builder.Append(" and tb.CommentLevel <= 1"); } } string startindex = (model.PagedIndex * model.PagedSize + 1).ToString(); string endindex = ((model.PagedIndex + 1) * model.PagedSize).ToString(); string sql = @"select ProductCommentId,OrderId,UserID,ProductId,CommentLevel,CommentContent,IsAnonymous,ReplyBy,ReplyDT,ReplyContent,CheckBy,Email,HeadImageUrl, CheckStatus,CheckDT,CommentDT,ProductName,Account,Phone,PicUrl,(case when row between " + startindex + " and " + endindex + " then " + @"stuff((select ','+convert(varchar(10),Labels) from SP_ProductComment_Labels where ProductCommentId = x.ProductCommentId for xml path('')),1,1,'') else null end) as LablesStr from (select row_number() over(order by tb.CommentDT desc) as row,tb.*,c1.ProductName,pc.Account,pc.Phone,pp.PicUrl,pc.Email,pc.HeadImageUrl from SP_ProductComment tb left join Product_Lang c1 on c1.ProductId = tb.ProductId left join ProductPic pp on pp.ProductId = tb.ProductId and pp.Flag=1 left join YH_User pc on pc.UserID = tb.UserID " + builder.ToString() + ") x"; List <dynamic> res = this._database.RunSqlQuery(x => x.ToResultSets(sql))[0]; List <ProductCommentModel> list = res.Skip(model.PagedIndex * model.PagedSize).Take(model.PagedSize).ToEntity <ProductCommentModel>(); TotalCnt = res.Count; return(new ResultModel() { Data = list }); }