/// <summary>
/// 根据状态获取订单列表
/// </summary>
/// <param name="userId"></param>
/// <param name="country"></param>
/// <param name="language"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="status"></param>
/// <returns></returns>
public IList <MyOrderSkuInfoEntity> GetMyOrderProductInfos(int userId, int country, int language, int pageIndex, int pageSize, Model.Enum.OrderStatusEnum status)
{
string sql = @"
--DECLARE @PageSize INT
--DECLARE @PageIndex INT
--SET @PageSize=100
--SET @PageIndex=1
;WITH tbAll([OrderCode],[UserId],[OrderStatus],[TotalAmount],[Freight],[ProductTotalAmount],[CustomsDuties],[CreateTime],[TeamCode],[Huoli],RowNum,Coupon) AS
(
SELECT [OrderCode]
,[UserId]
,[OrderStatus]
,[TotalAmount]
,[Freight]
,[ProductTotalAmount]
,[CustomsDuties]
,[CreateTime]
,[TeamCode]
,[Huoli]
,ROW_NUMBER() OVER(ORDER BY [CreateTime] DESC) AS RowNum
,IsNull(Coupon,0) as Coupon
FROM OrderInfo AS oi
WHERE oi.UserId=@UserId
AND oi.ReceiptCountry=@ReceiptCountry
{0}
)
SELECT ta.[OrderCode],
ta.[UserId],
[OrderStatus],
[TotalAmount],
[Freight],
[ProductTotalAmount],
[CustomsDuties],
ta.[CreateTime],
rowsCount= (select max(rownum) from tbAll),
op.RefundQuantity,
op.Spu,
op.Sku,
p.Name,
s.MainDicValue,
s.MainValue,
s.SubDicValue,
s.SubValue,
op.Quantity,
op.PayUnitPrice,
ta.TeamCode as TeamCode,ta.Huoli as HuoLi,ta.Coupon as Coupon,
pim.ImagePath,p.NetWeightUnit,p.NetContentUnit,
ti.UserID AS TeamUserId,
ti.StartTime AS TeamStartTime,
ti.EndTime AS TeamEndTime,
ti.TeamStatus AS TeamStatus,
oe.ExpressList AS ExpressList,
oe.ExpressCompany AS ExpressCompany
from tbAll as ta
INNER JOIN OrderProducts AS op ON op.OrderCode=ta.OrderCode
INNER JOIN ProductInfo AS p ON op.Spu=p.Spu AND p.LanguageVersion=@LanguageVersion
INNER JOIN SkuInfo AS s ON s.Sku=op.Sku AND s.SpuId=p.Id
INNER JOIN ProductImage AS pim ON pim.Spu=op.Spu AND pim.SortValue=1
LEFT JOIN TeamInfo AS ti ON ti.TeamCode=ta.TeamCode
LEFT JOIN OrderExpress as oe ON oe.OrderCode=ta.OrderCode
where rownum>(@PageSize*(@PageIndex-1)) and rownum <=(@PageSize*@PageIndex) ORDER BY ta.[CreateTime] DESC
";
try
{
var parameters = DbSFO2OMain.CreateParameterCollection();
sql = string.Format(sql, status != OrderStatusEnum.Default ? " and oi.OrderStatus=@OrderStatus " : " AND 1=1 ");
parameters.Append("@OrderStatus", status.ToInt());
parameters.Append("@UserId", userId);
parameters.Append("@ReceiptCountry", country);
parameters.Append("@LanguageVersion", language);
parameters.Append("@PageSize", pageSize);
parameters.Append("@PageIndex", pageIndex);
return(DbSFO2OMain.ExecuteSqlList <MyOrderSkuInfoEntity>(sql, parameters));
}
catch (Exception ex)
{
LogHelper.Error(ex);
return(new List <MyOrderSkuInfoEntity>());
}
}
/// <summary>
/// 根据订单状态获取分页列表数据
/// </summary>
/// <param name="userId"></param>
/// <param name="country"></param>
/// <param name="language"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="status"></param>
/// <returns></returns>
public IList <MyOrderInfoDto> GetMyOrderListByStatus(int userId, int country, int language, int pageIndex, int pageSize, Model.Enum.OrderStatusEnum status)
{
return(dal.GetMyOrderProductInfos(userId, country, language, pageIndex, pageSize, status).AsDtos().OrderByDescending(n => n.CreateTime).ToList());
}
