public JsonResult GetProductSaleStatisticList(ProductStatisticQuery query)
{
query.ShopId = CurrentSellerManager.ShopId;
var model = StatisticApplication.GetProductSales(query);
return(Json(model));
}
/// <summary>
/// 取商品销售分类统计
/// </summary>
/// <param name="startDate"></param>
/// <param name="endDate"></param>
/// <param name="shopId"></param>
/// <returns></returns>
public static IEnumerable <ProductCategoryStatisticModel> GetProductCategorySales(DateTime startDate, DateTime endDate, long shopId = 0)
{
ProductStatisticQuery query = new ProductStatisticQuery()
{
StartDate = startDate,
EndDate = endDate
};
if (shopId > 0)
{//如果不等于0，增加店铺ID条件
query.ShopId = shopId;
}
//销售记录
var productSales = _StatisticsService.GetProductAllVisits(query);
var pids = productSales.Select(e => e.ProductId).Distinct();
//商品信息，分类
var products = ProductManagerApplication.GetAllStatusProductByIds(pids);
var categorys = CategoryApplication.GetMainCategory();
//补充分类
var productCateInfo = productSales.Select(e =>
{
var mainCategoryId = products.FirstOrDefault(item => item.Id == e.ProductId).CategoryTopId;
var mainCategory = categorys.FirstOrDefault(c => c.Id == mainCategoryId);
return(new
{
CategoryId = mainCategoryId,
CategoryName = mainCategory == null?string.Empty:mainCategory.Name,
ProductId = e.ProductId,
ShopId = e.ShopId,
SaleAmounts = e.SaleAmounts,
SaleCounts = e.SaleCounts
});
});
var totalAmount = productCateInfo.Sum(e => e.SaleAmounts);
var totalCount = productCateInfo.Sum(e => e.SaleCounts);
//根据分类统计
var productCateStat = productCateInfo.GroupBy(e => e.CategoryId).Select(e => new ProductCategoryStatisticModel
{
CategoryId = e.Key,
CategoryName = e.FirstOrDefault().CategoryName,
SaleAmounts = e.Sum(item => item.SaleAmounts),
SaleCounts = e.Sum(item => item.SaleCounts),
EndDate = endDate.ToString("yyyy-MM-dd"),
StartDate = startDate.ToString("yyyy-MM-dd"),
AmountRate = 0.0M,
CountRate = 0.0M
});
//计算比率
totalAmount = totalAmount == 0 ? 1 : totalAmount;
totalCount = totalCount == 0 ? 1 : totalCount;
foreach (var cate in productCateStat)
{
cate.AmountRate = (decimal)cate.SaleAmounts / totalAmount;
cate.CountRate = (decimal)cate.SaleCounts / totalCount;
}
return(productCateStat);
}
public ActionResult ExportProductStatistic(ProductStatisticQuery query)
{
DateTime end = query.EndDate;
query.ShopId = CurrentSellerManager.ShopId;
query.PageSize = int.MaxValue;
var model = StatisticApplication.GetProductSales(query);
ViewData.Model = model.Models;
string Title = query.StartDate.ToString("yyyy-MM-dd") + "至" + end.ToString("yyyy-MM-dd") + "商品统计数据";
ViewData.Add("Title", Title);
string viewHtml = RenderPartialViewToString(this, "ExportProductStatistic");
return(File(System.Text.Encoding.UTF8.GetBytes(viewHtml), "application/ms-excel", "商品销售情况.xls"));
}
/// <summary>
/// 取商品统计
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public static QueryPageModel <ProductStatisticModel> GetProductSales(ProductStatisticQuery query)
{
var productVisits = _StatisticsService.GetProductVisits(query);
//补充商品名称
var pids = productVisits.Models.Select(e => e.ProductId);
var products = ProductManagerApplication.GetAllStatusProductByIds(pids);
foreach (var item in productVisits.Models)
{
var product = products.FirstOrDefault(e => e.Id == item.ProductId);
if (product != null)
{
item.ProductName = product.ProductName;
}
}
return(productVisits);
}
public ActionResult ExportProductStatistic(DateTime startDate, DateTime endDate)
{
ProductStatisticQuery query = new ProductStatisticQuery
{
PageSize = int.MaxValue,
PageNo = 1,
StartDate = startDate,
EndDate = endDate
};
var model = StatisticApplication.GetProductSales(query);
ViewData.Model = model.Models;
string Title = startDate.ToString("yyyy-MM-dd") + "至" + endDate.ToString("yyyy-MM-dd") + "商品统计数据";
ViewData.Add("Title", Title);
string viewHtml = RenderPartialViewToString(this, "ExportProductStatistic");
return(File(System.Text.Encoding.UTF8.GetBytes(viewHtml), "application/ms-excel", "商品销售情况.xls"));
}
public JsonResult GetProductSaleStatisticList(int page, int rows, DateTime startDate, DateTime endDate, string Sort = "", bool IsAsc = false)
{
ProductStatisticQuery query = new ProductStatisticQuery
{
PageSize = rows,
PageNo = page,
StartDate = startDate,
EndDate = endDate,
Sort = Sort,
IsAsc = IsAsc
};
var model = StatisticApplication.GetProductSales(query);
DataGridModel <ProductStatisticModel> dataGrid = new DataGridModel <ProductStatisticModel>()
{
rows = model.Models, total = model.Total
};
return(Json(dataGrid));
}
public JsonResult GetProductSaleStatisticList(ProductStatisticQuery query)
{
var model = StatisticApplication.GetProductSales(query);
return(Json(model));
}
public QueryPageModel <ProductStatisticModel> GetProductVisits(ProductStatisticQuery query)
{
string sql = "SELECT V.ProductId,P.ProductName,SUM(V.SaleAmounts)as SaleAmounts,SUM(V.SaleCounts)as SaleCounts,SUM(V.PayUserCounts)as PayUserCounts ";
string countSql = " select count(1) from ( SELECT count(1) FROM himall_productvisti V ";
countSql += " left join himall_product P on P.Id=V.ProductId ";
sql += " ,SUM(V.VisitUserCounts)as VisitUserCounts ";
sql += " ,SUM(V.VistiCounts)as VistiCounts ";
sql += " ,(SUM(V.PayUserCounts)/SUM(V.VisitUserCounts))as Conversion ";
sql += " ,(case when SUM(V.VisitUserCounts)=0 and SUM(V.PayUserCounts)>0 then 100 else SUM(V.PayUserCounts)/SUM(V.VisitUserCounts) end )as _Conversion ";
sql += " FROM himall_productvisti V left join himall_product P on P.Id=V.ProductId ";
sql += string.Format(" where V.Date>='{0}' and V.Date<'{1}' ", query.StartDate, query.EndDate);
countSql += string.Format(" where V.Date>='{0}' and V.Date<'{1}' ", query.StartDate, query.EndDate);
if (query.ShopId.HasValue)
{
sql += string.Format(" and V.ShopId={0} ", query.ShopId.Value);
countSql += string.Format(" and V.ShopId={0} ", query.ShopId.Value);
}
sql += " group by V.ProductId ";
countSql += " group by V.ProductId )T";
switch (query.Sort.ToLower())
{
case "visticounts":
if (query.IsAsc)
{
sql += " order by VistiCounts";
}
else
{
sql += " order by VistiCounts desc ";
}
break;
case "visitusercounts":
if (query.IsAsc)
{
sql += " order by VisitUserCounts";
}
else
{
sql += " order by VisitUserCounts desc ";
}
break;
case "payusercounts":
if (query.IsAsc)
{
sql += " order by PayUserCounts";
}
else
{
sql += " order by PayUserCounts desc ";
}
break;
case "singlepercentconversion":
if (query.IsAsc)
{
sql += " order by _Conversion";
}
else
{
sql += " order by _Conversion desc ";
}
break;
case "salecounts":
if (query.IsAsc)
{
sql += " order by SaleCounts";
}
else
{
sql += " order by SaleCounts desc ";
}
break;
case "saleamounts":
if (query.IsAsc)
{
sql += " order by SaleAmounts";
}
else
{
sql += " order by SaleAmounts desc ";
}
break;
default:
sql += " order by ProductId desc ";
break;
}
sql += GetSearchPage(query);
Core.Log.Info(sql);
Core.Log.Info(countSql);
var model = DbFactory.Default.Query <ProductStatisticModel>(sql).ToList();
return(new QueryPageModel <ProductStatisticModel>
{
Models = model,
Total = DbFactory.Default.ExecuteScalar <int>(countSql)
});
}
private string GetSearchPage(ProductStatisticQuery query)
{
return(string.Format(" LIMIT {0},{1} ", (query.PageNo - 1) * query.PageSize, query.PageSize));
}
