public OrderTotalInfo GetOrderTotal(OrderQueryInfo queryInfo, LanguageEnum languageEnum, int receiptCountry)
{
var db = DbSFO2OMain;
var parameters = db.CreateParameterCollection();
var sql = @" select COUNT(distinct o.UserId) as BuyerCount,COUNT(distinct o.OrderCode) OrderCount,COUNT(op.Quantity) ProductCount,SUM(op.UnitPrice * op.Quantity) OrderAmountTotal,sum(o.Freight) as FreightTotal
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
where p.SupplierId= @SupplierId";
if (receiptCountry != 0)
{
sql = sql + " and o.ReceiptCountry=@ReceiptCountry";
parameters.Append("ReceiptCountry", receiptCountry);
}
var query = BindQuery(queryInfo, parameters);
sql = sql + query;
parameters.Append("LanguageVersion", (int)languageEnum);
parameters.Append("SupplierId", queryInfo.SupplierId);
return(db.ExecuteSqlFirst <OrderTotalInfo>(sql, parameters));
}
/// <summary>
/// 构造查询条件
/// </summary>
private string BindQuery(OrderQueryInfo queryInfo, ParameterCollection dbParameters)
{
var stringBuilder = new StringBuilder();
if (!string.IsNullOrEmpty(queryInfo.orderCode))
{
stringBuilder.Append(" and o.OrderCode=@OrderCode");
dbParameters.Append("OrderCode", queryInfo.orderCode);
}
else
{
if (queryInfo.orderSatus != -1)
{
stringBuilder.Append(" and o.OrderStatus=@OrderStatus");
dbParameters.Append("OrderStatus", queryInfo.orderSatus);
}
if (queryInfo.startTime != null)
{
stringBuilder.Append(" and o.CreateTime> @StartTime");
dbParameters.Append("StartTime", queryInfo.startTime.ToString("yyyy-MM-dd 00:00:00"));
}
if (queryInfo.endTime != null)
{
stringBuilder.Append(" and o.CreateTime< @EndTime");
dbParameters.Append("EndTime", queryInfo.endTime.AddDays(1).ToString("yyyy-MM-dd 00:00:00"));
}
}
return(stringBuilder.ToString());
}
public OrderTotalInfo GetOrderTotal(OrderQueryInfo queryInfo, LanguageEnum languageEnum, int receiptCountry)
{
return(dal.GetOrderTotal(queryInfo, languageEnum, receiptCountry));
}
public ActionResult ExportOrderList(string startTime, string endTime, string orderCode, int orderStatus = -1)
{
OrderQueryInfo queryInfo = new OrderQueryInfo();
if (string.IsNullOrEmpty(startTime) || string.IsNullOrEmpty(endTime))
{
queryInfo.startTime = DateTime.Now.AddMonths(-3);
queryInfo.endTime = DateTime.Now;
}
else
{
queryInfo.startTime = DateTime.Parse(startTime);
queryInfo.endTime = DateTime.Parse(endTime);
}
queryInfo.orderCode = orderCode;
queryInfo.orderSatus = orderStatus;
queryInfo.SupplierId = CurrentUser.SupplierID;
try
{
PageOf <OrderListInfoModel> list = bll.GetOrderList(queryInfo, new PageDTO()
{
PageIndex = PageNo, PageSize = int.MaxValue
}, LanguageEnum.SimplifiedChinese, 0);
var dt = new DataTable();
dt.Columns.Add("订单号");
dt.Columns.Add("生成时间");
dt.Columns.Add("商品名称");
dt.Columns.Add("单价");
dt.Columns.Add("数量");
dt.Columns.Add("小计");
dt.Columns.Add("状态");
dt.Columns.Add("收货地址");
dt.Columns.Add("收货人");
dt.Columns.Add("联系电话");
List <OrderListInfoModel> result = list.Items.OrderBy(o => o.Phone).ToList <OrderListInfoModel>();
result.ForEach(l =>
{
dt.Rows.Add(new object[] {
l.OrderCode,
l.CreateTime.ToString("yyyy-MM-dd hh:mm:ss"),
l.Name,
"￥" + l.UnitPrice,
l.Quantity,
"￥" + l.UnitPrice * l.Quantity,
EnumHelper.GetDescription(l.OrderStatus, typeof(OrderStatus)),
l.ReceiptAddress,
l.Receiver,
l.Phone,
});
});
ExcelHelper.ExportByWeb(dt, "订单列表", "orderlist" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls");
}
catch (Exception ext)
{
LogHelper.Error(ext);
}
return(View());
}
public ActionResult OrderList(string startTime, string endTime, string orderCode, int receiptCountry = 0, int orderStatus = 1, int page = 1)
{
OrderQueryInfo queryInfo = new OrderQueryInfo();
if (string.IsNullOrEmpty(startTime) || string.IsNullOrEmpty(endTime))
{
queryInfo.startTime = DateTime.Now.AddMonths(-3);
queryInfo.endTime = DateTime.Now;
}
else
{
queryInfo.startTime = DateTime.Parse(startTime);
queryInfo.endTime = DateTime.Parse(endTime);
}
queryInfo.orderCode = orderCode;
queryInfo.orderSatus = orderStatus;
queryInfo.SupplierId = CurrentUser.SupplierID;
ViewBag.QueryInfo = queryInfo;
ViewBag.ReceiptCountry = receiptCountry;
ViewBag.Page = page;
try
{
if (receiptCountry == 1)
{
ViewBag.ChinaOrder = bll.GetOrderList(queryInfo, new PageDTO()
{
PageIndex = PageNo, PageSize = 20
}, LanguageEnum.SimplifiedChinese, 1);
// ViewBag.HKOrder = bll.GetOrderList(queryInfo, new PageDTO() { PageIndex = page, PageSize = 20 }, LanguageEnum.TraditionalChinese, 2);
}
else if (receiptCountry == 2)
{
ViewBag.ChinaOrder = bll.GetOrderList(queryInfo, new PageDTO()
{
PageIndex = page, PageSize = 20
}, LanguageEnum.SimplifiedChinese, 1);
//ViewBag.HKOrder = bll.GetOrderList(queryInfo, new PageDTO() { PageIndex = PageNo, PageSize = 20 }, LanguageEnum.TraditionalChinese, 2);
}
else
{
ViewBag.ChinaOrder = bll.GetOrderList(queryInfo, new PageDTO()
{
PageIndex = 1, PageSize = 20
}, LanguageEnum.SimplifiedChinese, 1);
//ViewBag.HKOrder = bll.GetOrderList(queryInfo, new PageDTO() { PageIndex = 1, PageSize = 20 }, LanguageEnum.TraditionalChinese, 2);
}
var totalCN = bll.GetOrderTotal(queryInfo, LanguageEnum.SimplifiedChinese, 1);
//var totalHK = bll.GetOrderTotal(queryInfo, LanguageEnum.TraditionalChinese, 2);
ViewBag.OrderTotalCN = totalCN;
//ViewBag.OrderTotalHK = totalHK;
}
catch (Exception ex)
{
LogHelper.Error(ex);
}
return(View());
}
