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