/// <summary>
/// 获取单个订单的商品评论
/// </summary>
/// <param name="model">查询模型</param>
/// <returns>单个订单的商品评论</returns>
public ResultModel GetOrderProductComments(SearchOrderProductCommentView model)
{
var result = new ResultModel()
{
IsValid = false
};
string sql = string.Format(@"SELECT a.SKU_ProducId,a.Iscomment,a.OrderID,a.ProductId,a.SkuName,c.PicUrl,d.ProductName,e.CommentContent,e.CommentDT,e.CommentLevel,e.ProductCommentId,
stuff((select ','+convert(varchar(10),Labels) from SP_ProductComment_Labels where ProductCommentId = e.ProductCommentId for xml path('')),1,1,'') as LablesStr
FROM OrderDetails AS a
INNER JOIN dbo.[Order] AS f ON a.OrderID=f.OrderID
INNER JOIN dbo.Product AS b ON a.ProductId =b.ProductId
LEFT JOIN(SELECT * FROM dbo.ProductPic WHERE Flag=1) AS c
ON b.ProductId=c.ProductID
LEFT JOIN(SELECT * FROM dbo.Product_Lang WHERE LanguageID={0}) AS d
ON b.ProductId=d.ProductId
LEFT JOIN dbo.SP_ProductComment AS e ON a.SKU_ProducId=e.SKU_ProducId AND a.OrderID=e.OrderId
WHERE a.OrderID='{1}' AND f.UserID={2}", model.LanguageID.Value, SqlFilterUtil.ReplaceSqlChar(model.OrderID), model.UserID);
if (model.Iscomment.HasValue)
{
sql += string.Format(" AND a.Iscomment={0}", model.Iscomment.Value);
}
List <dynamic> sources = _database.RunSqlQuery(x => x.ToResultSets(sql))[0];
result.Data = sources.ToEntity <OrderProductCommentView>();
result.IsValid = true;
return(result);
}
public void GetPaingCommentsIntoWeb_Test()
{
SearchOrderProductCommentView searchModel = new SearchOrderProductCommentView()
{
UserID = 973840311,
page = 1,
pageSize = 2,
LanguageID = 1
};
var result = _sp_productCommentService.GetPaingCommentsIntoWeb(searchModel);
Assert.IsTrue(result.IsValid);
Assert.NotNull(result.Data);
Assert.IsTrue(result.Data.TotalCount);
}
/// <summary>
/// 分页获取用户商品评论
/// </summary>
/// <param name="model">查询模型</param>
/// <returns>分页获取用户商品评论</returns>
public ResultModel GetPaingCommentsIntoWeb(SearchOrderProductCommentView model)
{
var result = new ResultModel()
{
IsValid = false
};
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.AppendLine(" SELECT COUNT(*) AS [Count] ");
sqlBuilder.AppendLine(" FROM OrderDetails AS a ");
sqlBuilder.AppendLine(" INNER JOIN dbo.[Order] AS f ON a.OrderID=f.OrderID ");
sqlBuilder.AppendLine(" INNER JOIN dbo.Product AS b ON a.ProductId =b.ProductId ");
sqlBuilder.AppendLine(" LEFT JOIN(SELECT * FROM dbo.ProductPic WHERE Flag=1) AS c ON b.ProductId=c.ProductID ");
sqlBuilder.AppendLine(" LEFT JOIN(SELECT * FROM dbo.Product_Lang WHERE LanguageID=1) AS d ON b.ProductId=d.ProductId ");
sqlBuilder.AppendLine(" INNER JOIN dbo.YH_MerchantInfo AS g ON f.MerchantID=g.MerchantID ");
sqlBuilder.AppendLine(" INNER JOIN dbo.SP_ProductComment AS e ON a.ProductId=e.ProductId AND a.OrderID=e.OrderId ");
sqlBuilder.AppendFormat(" WHERE f.UserID={0}; ", model.UserID);
sqlBuilder.AppendLine(" WITH tempTable AS ( ");
sqlBuilder.AppendLine(" SELECT a.SKU_ProducId,a.Iscomment,a.OrderID,a.ProductId,a.SkuName,c.PicUrl,d.ProductName,e.CommentContent,e.CommentDT,e.CommentLevel,e.ProductCommentId,f.MerchantID,g.ShopName ");
sqlBuilder.AppendLine(" FROM OrderDetails AS a ");
sqlBuilder.AppendLine(" INNER JOIN dbo.[Order] AS f ON a.OrderID=f.OrderID ");
sqlBuilder.AppendLine(" INNER JOIN dbo.Product AS b ON a.ProductId =b.ProductId ");
sqlBuilder.AppendLine(" LEFT JOIN(SELECT * FROM dbo.ProductPic WHERE Flag=1) AS c ON b.ProductId=c.ProductID ");
sqlBuilder.AppendFormat(" LEFT JOIN(SELECT * FROM dbo.Product_Lang WHERE LanguageID={0}) AS d ON b.ProductId=d.ProductId ", model.LanguageID);
sqlBuilder.AppendLine(" INNER JOIN dbo.YH_MerchantInfo AS g ON f.MerchantID=g.MerchantID ");
sqlBuilder.AppendLine(" INNER JOIN dbo.SP_ProductComment AS e ON a.SKU_ProducId=e.SKU_ProducId AND a.OrderID=e.OrderId ");
sqlBuilder.AppendFormat(" WHERE f.UserID={0}) ", model.UserID);
sqlBuilder.AppendLine(" SELECT Iscomment,OrderID,ProductId,SkuName,PicUrl,ProductName,CommentContent,CommentDT,CommentLevel,ProductCommentId ,MerchantID,ShopName FROM ( SELECT ");
sqlBuilder.AppendFormat(" *,ROW_NUMBER() OVER(ORDER BY CommentDT DESC) AS rid FROM tempTable)AS a WHERE a.rid BETWEEN {0} AND {1} ", (model.page - 1) * model.pageSize + 1, model.page * model.pageSize);
var queryResult = _database.RunSqlQuery(x => x.ToResultSets(sqlBuilder.ToString()));
dynamic source = queryResult[0][0];
List <dynamic> sources = queryResult[1];
result.Data = new SimpleDataPagedList <OrderProductCommentView>(sources.ToEntity <OrderProductCommentView>(), model.page, model.pageSize, source.Count);
result.IsValid = true;
return(result);
}