public Models.PageOf <Models.OrderListInfoModel> GetOrderList(Models.OrderQueryInfo queryInfo, Models.PageDTO page, LanguageEnum languageVersion, int receiptCountry)
{
var db = DbSFO2OMain;
var parameters = db.CreateParameterCollection();
var sql = @"( select o.OrderCode,s.MainDicValue,s.SubDicValue,s.MainValue,s.SubValue,o.CreateTime,p.Name,op.UnitPrice,op.PayUnitPrice,op.Quantity,o.TotalAmount,
o.OrderStatus,o.ExchangeRate,p.Spu,pImg.ImagePath as ProductImagePath,op.Sku,op.RefundQuantity,
(select top 1 expresslist from OrderExpress where OrderCode = o.OrderCode) as expresslist,
(select top 1 ExpressCompany from OrderExpress where OrderCode = o.OrderCode) as ExpressCompany,pr.ProvinceName+' | '+ci.CityName+' | '+ ar.AreaName+' | '+ o.ReceiptAddress AS ReceiptAddress,o.Phone,o.Receiver
from OrderInfo o(NOLOCK)
inner join OrderProducts op(NOLOCK) on o.OrderCode= op.OrderCode
inner join SkuInfo s(NOLOCK) on s.Sku= op.Sku
inner join ProductInfo p(NOLOCK) on p.Id=s.SpuId and p.LanguageVersion=@LanguageVersion
left join ProductImage pImg(NOLOCK) ON pImg.Spu = p.Spu AND pImg.SortValue = 1 AND pImg.ImageType = 1
LEFT JOIN city(NOLOCK) AS ci ON ci.CityId=o.ReceiptCity AND ci.LanguageVersion = 1
LEFT JOIN Province(NOLOCK) AS pr ON pr.ProvinceId = o.ReceiptProvince AND pr.LanguageVersion = 1
LEFT JOIN Area AS ar ON ar.areaid=o.ReceiptRegion AND ar.LanguageVersion = 1
where p.SupplierId= @SupplierId ";
if (receiptCountry != 0)
{
sql = sql + " and o.ReceiptCountry=@ReceiptCountry";
}
var query = BindQuery(queryInfo, parameters);
sql = sql + query;
sql += ") pp ";
string SQL = string.Format(@" select * from (select ROW_NUMBER() OVER(order by pp.CreateTime desc) AS RowNum,* from {0}
) as A where A.RowNum BETWEEN (@PageIndex-1)* @PageSize+1 AND @PageIndex*@PageSize ORDER BY RowNum;" ,
sql);
SQL += string.Format(@" SELECT COUNT(1),COUNT(distinct pp.OrderCode) FROM {0};", sql);
parameters.Append("LanguageVersion", (int)languageVersion);
parameters.Append("SupplierId", queryInfo.SupplierId);
parameters.Append("ReceiptCountry", receiptCountry);
parameters.Append("PageIndex", page.PageIndex);
parameters.Append("PageSize", page.PageSize);
DataSet ds = db.ExecuteSqlDataSet(SQL, parameters);
return(new PageOf <OrderListInfoModel>()
{
PageIndex = page.PageIndex,
PageSize = page.PageSize,
Total = Convert.ToInt32(ds.Tables[1].Rows[0][0]),
RowCount = Convert.ToInt32(ds.Tables[1].Rows[0][1]),
Items = DataMapHelper.DataSetToList <OrderListInfoModel>(ds)
});
}
public PageOf <OrderListInfoModel> GetOrderList(Models.OrderQueryInfo queryInfo, Models.PageDTO page, Models.LanguageEnum languageVersion, int receiptCountry)
{
return(dal.GetOrderList(queryInfo, page, languageVersion, receiptCountry));
}
