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