public OrderListTotalModel GetOrderCount(OrderListQueryModel query)
{
const string sql = @"
SELECT COUNT(OrderCode) AS OrderCount,SUM(Quantity) AS SkuCount
,SUM(PaidAmount) AS PaidAmount
,SUM(CustomsDuties) AS CustomsDuties
,MIN(ExchangeRate) ExchangeRate
FROM
(
SELECT o.OrderCode,o.CustomsDuties,o.ExchangeRate,SUM(op.Quantity) AS Quantity
,SUM(op.UnitPrice*op.Quantity) AS PaidAmount
FROM OrderInfo o(NOLOCK)
INNER JOIN OrderProducts op(NOLOCK) on o.OrderCode=op.OrderCode
INNER JOIN customer c(NOLOCK) on c.ID=o.UserId
INNER JOIN SkuInfo s(NOLOCK) on s.Sku= op.Sku
INNER JOIN ProductInfo p(NOLOCK) on p.Id=s.SpuId and p.LanguageVersion=1
WHERE 1=1 {0} {1}
GROUP BY o.OrderCode,o.CustomsDuties,o.ExchangeRate
) t";
var db = DbSFO2ORead;
var parameters = db.CreateParameterCollection();
var recCountry = "";
if (query.CountryCode > 0)
{
parameters.Append("ReceiptCountry", query.CountryCode);
recCountry = "AND ReceiptCountry=@ReceiptCountry";
}
var conditions = OrderListConditions(query, ref parameters);
return(db.ExecuteSqlFirst <OrderListTotalModel>(String.Format(sql, recCountry, conditions), parameters));
}
public ActionResult ExportOrderList(DateTime startTime, DateTime endTime, string sku, int orderStatus, string buyerAccount, int supplierId, string orderCode, int isExcludeCloseOrder)
{
OrderListQueryModel query = new OrderListQueryModel()
{
BuyerAccount = buyerAccount,
CountryCode = 0,
CreateTimeEnd = endTime,
CreateTimeStart = startTime,
IsExcludeCloseOrder = isExcludeCloseOrder,
OrderCode = orderCode,
OrderStatus = orderStatus,
SellerId = supplierId,
SKU = sku
};
try
{
var list = orderBll.GetOrderList(query, Int32.MaxValue, 1);
Response.Clear();
Response.Buffer = true;
Response.Charset = "GB2312";
Response.AppendHeader("Content-Disposition", "attachment;filename=order_list" + System.DateTime.Now.ToString("yyyyMMdd") + ".xls");
Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312"); //设置输出流为简体中文
Response.ContentType = "application/ms-excel"; //设置输出文件类型为excel文件。
OrderListToExcel(list.Items);
Response.End();
}
catch (Exception ext)
{
LogHelper.Error(ext);
}
return(View());
}
public ActionResult OrderList()
{
ViewBag.SallerNames = new SupplierBLL().GetSupplierNames();
OrderListQueryModel query = new OrderListQueryModel();
query.CreateTimeEnd = DateTime.Now;
query.CreateTimeStart = query.CreateTimeEnd.AddMonths(-3);
query.IsExcludeCloseOrder = 1;
query.OrderStatus = -2;
query.OrderCode = Request["OrderCode"] == null ? "" : Request["OrderCode"].ToString();
return(View(query));
}
private string OrderListConditions(OrderListQueryModel query, ref ParameterCollection paras)
{
StringBuilder sb = new StringBuilder(1000);
if (!String.IsNullOrWhiteSpace(query.OrderCode))
{
sb.Append(" AND o.OrderCode=@OrderCode");
paras.Append("OrderCode", query.OrderCode);
}
else
{
sb.Append(" AND o.CreateTime>=@StartTime AND o.CreateTime<=@EndTime");
paras.Append("StartTime", new DateTime(query.CreateTimeStart.Year, query.CreateTimeStart.Month, query.CreateTimeStart.Day, 0, 0, 0));
paras.Append("EndTime", new DateTime(query.CreateTimeEnd.Year, query.CreateTimeEnd.Month, query.CreateTimeEnd.Day, 23, 59, 59, 999));
if (query.OrderStatus != -2)
{
sb.Append(" AND o.OrderStatus=@Status");
paras.Append("Status", query.OrderStatus);
}
if (!String.IsNullOrWhiteSpace(query.SKU))
{
sb.Append(" AND op.Sku=@SKU");
paras.Append("SKU", query.SKU);
}
if (!String.IsNullOrWhiteSpace(query.BuyerAccount))
{
sb.Append(" AND c.UserName=@UserName");
paras.Append("UserName", query.BuyerAccount);
}
if (query.SellerId > 0)
{
sb.Append(" AND p.SupplierId=@SupplierId");
paras.Append("SupplierId", query.SellerId);
}
if (query.IsExcludeCloseOrder == 1)
{
sb.Append(" AND o.OrderStatus NOT IN (5)");
}
}
return(sb.ToString());
}
public ActionResult GetOrderList(OrderListQueryModel query, int pageSize, int pageIndex, int isPaging)
{
OrderListAndCountModel result = new OrderListAndCountModel();
query.CountryCode = 1;
try
{
result.Total = orderBll.GetOrderCount(query);
result.OrderList = orderBll.GetOrderList(query, pageSize, pageIndex);
}
catch (Exception ext)
{
LogHelper.Error(ext);
}
return(View(result));
}
public PageOf <OrderListModel> GetOrderList(OrderListQueryModel query, int pageSize, int pageIndex)
{
const string sql = @"
WITH IMGS AS
(
SELECT spu,ImagePath
FROM (
SELECT rownum = ROW_NUMBER() OVER (PARTITION BY spu ORDER BY sortValue), spu,ImagePath
FROM ProductImage
WHERE ImageType = 1
)b
WHERE rownum<2
),
orderMain AS
(
SELECT * FROM (
SELECT ROW_NUMBER() OVER (ORDER BY a.Createtime desc) AS RowNumber,* FROM (
SELECT distinct o.OrderCode,o.CreateTime,c.UserName,o.OrderStatus,o.ExchangeRate,SUM(op.RefundQuantity) AS RefundQuantity,CustomsDuties
FROM OrderInfo o(NOLOCK)
INNER JOIN OrderProducts op(NOLOCK) on o.OrderCode=op.OrderCode
INNER JOIN customer c(NOLOCK) on c.ID=o.UserId
INNER JOIN SkuInfo s(NOLOCK) on s.Sku= op.Sku
INNER JOIN ProductInfo p(NOLOCK) on p.Id=s.SpuId and p.LanguageVersion=1
WHERE 1=1 {0} {1}
GROUP BY o.OrderCode,o.CreateTime,c.UserName,o.OrderStatus,o.ExchangeRate,o.CustomsDuties
) a)b WHERE b.RowNumber>(@PageIndex-1)*@PageSize AND b.RowNumber <= @PageIndex*@PageSize
)
SELECT om.OrderCode,om.CreateTime,om.UserName,p.Name,si.MainDicValue,si.MainValue,si.SubDicValue,si.SubValue,om.OrderStatus,op.Sku,op.Spu,op.PayUnitPrice,op.Quantity,op.IsBearDuty,s.CompanyName,op.TaxAmount,op.IsBearDuty,om.CustomsDuties
,SUM(op.PayUnitPrice*op.Quantity) AS TotalAmount,SUM(op.UnitPrice*op.Quantity) AS TotalAmount1,om.ExchangeRate,IMGS.ImagePath
FROM OrderProducts(NOLOCK) AS op
INNER JOIN orderMain AS om ON op.OrderCode = om.OrderCode
INNER JOIN SkuInfo si(NOLOCK) ON si.Sku= op.Sku
INNER JOIN ProductInfo p(NOLOCK) ON p.Id=si.SpuId and p.LanguageVersion=1
INNER JOIN Supplier(NOLOCK) AS s ON s.SupplierID = p.SupplierId
LEFT JOIN IMGS ON IMGS.Spu=op.Spu
WHERE 1=1 {2}
GROUP BY om.OrderCode,om.CreateTime,om.UserName,op.Sku,op.Spu,op.PayUnitPrice,op.Quantity,s.CompanyName,p.Name,si.MainDicValue,si.MainValue,si.SubDicValue,si.SubValue,om.OrderStatus,om.ExchangeRate,IMGS.ImagePath,op.TaxAmount,op.IsBearDuty,om.CustomsDuties
ORDER BY om.CreateTime DESC
SELECT COUNT(DISTINCT o.ordercode)
FROM OrderInfo o(NOLOCK)
INNER JOIN OrderProducts op(NOLOCK) on o.OrderCode=op.OrderCode
INNER JOIN customer c(NOLOCK) on c.ID=o.UserId
INNER JOIN SkuInfo s(NOLOCK) on s.Sku= op.Sku
INNER JOIN ProductInfo p(NOLOCK) on p.Id=s.SpuId and p.LanguageVersion=1
WHERE 1=1 {0} {1}
";
var db = DbSFO2ORead;
var parameters = db.CreateParameterCollection();
parameters.Append("PageSize", pageSize);
parameters.Append("PageIndex", pageIndex);
var recCountry = "";
if (query.CountryCode > 0)
{
parameters.Append("ReceiptCountry", query.CountryCode);
recCountry = "AND ReceiptCountry=@ReceiptCountry";
}
var conditions = OrderListConditions(query, ref parameters);
var conditions1 = "";
if (query.SellerId > 0)
{
conditions1 = " AND p.SupplierId=@SupplierId1";
parameters.Append("SupplierId1", query.SellerId);
}
if (!String.IsNullOrWhiteSpace(query.SKU))
{
conditions1 += " AND op.Sku=@SKU1";
parameters.Append("SKU1", query.SKU);
}
var finalSql = String.Format(sql, recCountry, conditions, conditions1);
var ds = db.ExecuteSqlDataSet(finalSql, parameters);
return(new PageOf <OrderListModel>()
{
PageIndex = pageIndex,
PageSize = pageSize,
RowCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]),
Items = DataMapHelper.DataSetToList <OrderListModel>(ds)
});
}
public PageOf <OrderListModel> GetOrderList(OrderListQueryModel query, int pageSize, int pageIndex)
{
return(orderDao.GetOrderList(query, pageSize, pageIndex));
}
public OrderListTotalModel GetOrderCount(OrderListQueryModel query)
{
return(orderDao.GetOrderCount(query));
}
