public PagedList <SalesReport> GetSalesReport(QueryParams2 queryParams) { var data = DbContext.SalesData.AsQueryable(); if (queryParams.STime.HasValue && queryParams.ETime.HasValue) { if (queryParams.STime.Value != queryParams.ETime.Value) { data = data.Where(x => x.Yyyymmdd >= queryParams.STime && x.Yyyymmdd <= queryParams.ETime); } else { data = data.Where(x => x.Yyyymmdd == queryParams.STime); } } if (!string.IsNullOrEmpty(queryParams.M_id)) { data = data.Where(x => x.M_id == queryParams.M_id); } if (!string.IsNullOrEmpty(queryParams.WebSite)) { data = data.Where(x => x.Affiliate_id == queryParams.WebSite); } var mydataGrouped = data.GroupBy(queryParams.Sort, "it").Select("new (it.Key as Sort,Count() as OrderCount,SUM(Sales) as SalesTotal, SUM(Comm) as CommTotal,it as Report )"); List <SalesReport> reports = new List <SalesReport>(); foreach (dynamic group in mydataGrouped) { int _validOrderCount = 0; decimal _validSales = 0; decimal _validComm = 0; foreach (dynamic ii in group.Report) { if (ii.Stat_code == 200) { _validOrderCount += 1; _validSales += ii.Sales; _validComm += ii.Commission; } } reports.Add(new SalesReport { Sort = group.Sort, OrderCount = group.OrderCount, ValidOrderCount = _validOrderCount, SalesTotal = group.SalesTotal, ValidSalesTotal = _validSales, CommTotal = group.CommTotal, ValidCommTotal = _validComm }); } return(reports.AsQueryable().OrderByDescending(x => x.Sort).ToPagedList(queryParams.Offset / queryParams.Limit + 1, queryParams.Limit)); }
public IHttpActionResult GetSalesReport([FromUri] QueryParams2 queryParams) { var data = _salesDataService.GetSalesReport(queryParams); return(Json(new RespResult <SalesReport> { IsOk = 1, Count = data.TotalCount, Result = data })); }