/// <summary>
/// 报表类型：Sales by Product
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public ReportByProducttWithStatisModel GetReportByProductList(ReportsModel model)
{
using (PermaisuriCMSEntities db = new PermaisuriCMSEntities())
{
var adapter = (IObjectContextAdapter)db;
var objectContext = adapter.ObjectContext;
objectContext.CommandTimeout = 3 * 60; // value in seconds
ReportByProducttWithStatisModel reportModel = new ReportByProducttWithStatisModel();
List <ReportByProductModel> list = new List <ReportByProductModel>();
// ToList() resovle:The result of a query cannot be enumerated more than once problems
String OrderType = "asc";
String OrderBy = "UnitsSold";
if (model.OrderType > 0)
{
OrderType = "desc";
}
if (model.OrderBy > 0)
{
switch (model.OrderBy)
{
case 1: //order by SKUOrder
OrderBy = "SKUOrder";
break;
case 2: //order by SKUName
OrderBy = "ProductName";
break;
case 3: // SalesTotal
OrderBy = "SalesTotal";
break;
case 4: // UnitesSold
OrderBy = "UnitsSold";
break;
case 5: //AvgAmount
OrderBy = "AvgAmount";
break;
default:
OrderBy = "UnitsSold";
break;
}
}
//DbType.Int32
var countParam = new ObjectParameter("count", typeof(int));//注意，第一个参数count名称必须与Store Procedure里面的输出参数的名称一一对应！不区分大小写
var SumSalesTotalParam = new ObjectParameter("SumSalesTotal", typeof(int));
var SumUnitsSoldParam = new ObjectParameter("SumUnitsSold", typeof(int));
var SumAvgAmontParam = new ObjectParameter("SumAvgAmont", typeof(int));
var res = db.Ecom_ReportByProduct_SP(model.page, model.rows, model.SKUOrder, model.ProductName, model.Channel, model.Brand,
model.StartTime, model.EndTime, OrderBy, OrderType, countParam, SumSalesTotalParam, SumUnitsSoldParam, SumAvgAmontParam).ToList().AsEnumerable();
reportModel.Count = Convert.ToInt32(countParam.Value);
reportModel.SumSalesTotal = Convert.ToInt32(SumSalesTotalParam.Value).ToString("C", new CultureInfo("en-US"));
reportModel.SumUnitsSold = Convert.ToInt32(SumUnitsSoldParam.Value);
reportModel.SumAvgAmont = Convert.ToInt32(SumAvgAmontParam.Value).ToString("C", new CultureInfo("en-US"));
//count = Convert.ToInt32(countParam.Value);
foreach (Ecom_ReportByProduct_SP_Result r in res)
{
list.Add(new ReportByProductModel
{
SKUOrder = r.SKUOrder,
ProductName = r.ProductName,
SalesTotal = r.SalesTotal.ConvertToNotNull().ToString("C", new CultureInfo("en-US")),
UnitsSold = r.UnitsSold.ConvertToNotNull(),
AvgAmont = r.AvgAmont.ConvertToNotNull().ToString("C", new CultureInfo("en-US"))
});
}
reportModel.ReportList = list;
return(reportModel);
}
}