public ActionResult QueryRealTimeSaleReport(Pager page, SearchSaleReport condition)
{
var rows = _saleOrderQuery.QueryRealTimeSaleReport(page, condition);
if (page.toExcel)
{
var data = _excelService.WriteToExcelStream(rows.ToList(), ExcelVersion.Above2007, false, true).ToArray();
var fileName = string.Format("实时销售报表_{0}.xlsx", DateTime.Now.ToString("yyyyMMdd"));
return(File(data, "application/ms-excel", fileName));
}
return(Json(new { success = true, data = rows, total = page.Total, sum = page.SumColumns }));
}
public JsonResult QuerySaleReport(Pager page, SearchSaleReport condition)
{
var rows = _saleOrderQuery.QuerySaleReport(page, condition);
return(Json(new { success = true, data = rows, total = page.Total, sum = page.SumColumns }));
}
private IEnumerable <SaleReportDto> QuerySaleReportGroupByStore(Pager page, SearchSaleReport condition)
{
IEnumerable <SaleReportDto> result = new List <SaleReportDto>();
if ((int)condition.GroupBy == 0)
{
return(result);
}
dynamic param = new ExpandoObject();
string where = "";
if (!string.IsNullOrEmpty(condition.ProductCodeOrBarCode))
{
where += @"and ( p.code=@ProductCodeOrBarCode or p.barcode =@ProductCodeOrBarCode ) ";
param.ProductCodeOrBarCode = condition.ProductCodeOrBarCode;
}
if (condition.StartDate.HasValue)
{
where += "and r.CreatedOn >=@StartDate ";
param.StartDate = condition.StartDate.Value;
}
if (condition.EndDate.HasValue)
{
where += "and r.CreatedOn < @EndDate ";
param.EndDate = condition.EndDate.Value.AddDays(1);
}
if (!string.IsNullOrEmpty(condition.StoreId) && condition.StoreId != "0")
{
where += "and r.StoreId in @StoreId ";
param.StoreId = condition.StoreId.Split(',').ToIntArray();
}
if (!string.IsNullOrEmpty(condition.CategoryId))
{
where += "and c.Id like @CategoryId ";
param.CategoryId = condition.CategoryId + "%";
}
if (condition.BrandId > 0)
{
where += "and b.Id = @BrandId ";
param.BrandId = condition.BrandId;
}
if (condition.OrderLevel > 0)
{
where += "and r.OrderLevel = @OrderLevel ";
param.OrderLevel = condition.OrderLevel;
}
// select s.`name`,OrderCount,SaleQuantity,SaleCostAmount,SaleAmount from (
//select r.storeId,count(DISTINCT r.billCode) as OrderCount,sum(r.changeQuantity) as SaleQuantity,sum(r.Price*r.changeQuantity) as SaleCostAmount,sum(r.Price*r.changeQuantity) as SaleAmount
//from storeinventoryhistory r left join product p on p.id = r.productId
//left join category c on c.Id = p.CategoryId
//left join brand b on b.Id = p.BrandId
//where r.BillType in (1,2)
//GROUP BY r.StoreId ) t
//LEFT JOIN store s on s.id = t.StoreId limit 0,50
//按分组组装sql
var sql = "";
var sqlCount = "";
var sqlSum = "";
switch (condition.GroupBy)
{
case GroupByMethod.Store:
sql = @"select s.`name`,OrderCount,SaleQuantity,SaleCostAmount,SaleAmount from (
select r.storeId,count(DISTINCT r.saleorderId) as OrderCount,sum(r.Quantity) as SaleQuantity,sum(r.CostPrice*r.Quantity) as SaleCostAmount,sum(r.SalePrice*r.Quantity) as SaleAmount
from salereport r left join product p on p.id = r.productId
left join category c on c.Id = p.CategoryId
left join brand b on b.Id = p.BrandId
where 1=1 {0} GROUP BY r.StoreId LIMIT {1},{2} ) t
LEFT JOIN store s on s.id = t.StoreId";
sqlCount = @"select count(*) from (
select r.storeId,count(DISTINCT r.saleorderId) as OrderCount,sum(r.Quantity) as SaleQuantity,sum(r.CostPrice*r.Quantity) as SaleCostAmount,sum(r.SalePrice*r.Quantity) as SaleAmount
from salereport r left join product p on p.id = r.productId
left join category c on c.Id = p.CategoryId
left join brand b on b.Id = p.BrandId
where 1=1 {0} GROUP BY r.StoreId ) t
LEFT JOIN store s on s.id = t.StoreId";
sqlSum = @"select sum(OrderCount) as OrderCount ,sum(SaleQuantity) as SaleQuantity,sum(SaleCostAmount) as SaleCostAmount,sum(SaleAmount) as SaleAmount from (
select r.storeId,count(DISTINCT r.saleorderId) as OrderCount,sum(r.Quantity) as SaleQuantity,sum(r.CostPrice*r.Quantity) as SaleCostAmount,sum(r.SalePrice*r.Quantity) as SaleAmount
from salereport r left join product p on p.id = r.productId
left join category c on c.Id = p.CategoryId
left join brand b on b.Id = p.BrandId
where 1=1 {0} GROUP BY r.StoreId ) t
LEFT JOIN store s on s.id = t.StoreId";
break;
case GroupByMethod.Product:
sql = @"select s.`name`,OrderCount,SaleQuantity,SaleCostAmount,SaleAmount from (
select r.productId,count(DISTINCT r.saleorderId) as OrderCount,sum(r.Quantity) as SaleQuantity,sum(r.CostPrice*r.Quantity) as SaleCostAmount,sum(r.SalePrice*r.Quantity) as SaleAmount
from salereport r left join product p on p.id = r.productId
left join category c on c.Id = p.CategoryId
left join brand b on b.Id = p.BrandId
where 1=1 {0} GROUP BY r.productId LIMIT {1},{2} ) t
LEFT JOIN product s on s.id = t.productId";
sqlCount = @"select count(*) from (
select r.productId,count(DISTINCT r.saleorderId) as OrderCount,sum(r.Quantity) as SaleQuantity,sum(r.CostPrice*r.Quantity) as SaleCostAmount,sum(r.SalePrice*r.Quantity) as SaleAmount
from salereport r left join product p on p.id = r.productId
left join category c on c.Id = p.CategoryId
left join brand b on b.Id = p.BrandId
where 1=1 {0} GROUP BY r.productId ) t
LEFT JOIN product s on s.id = t.productId";
sqlSum = @"select sum(OrderCount) as OrderCount ,sum(SaleQuantity) as SaleQuantity,sum(SaleCostAmount) as SaleCostAmount,sum(SaleAmount) as SaleAmount from (
select r.productId,count(DISTINCT r.saleorderId) as OrderCount,sum(r.Quantity) as SaleQuantity,sum(r.CostPrice*r.Quantity) as SaleCostAmount,sum(r.SalePrice*r.Quantity) as SaleAmount
from salereport r left join product p on p.id = r.productId
left join category c on c.Id = p.CategoryId
left join brand b on b.Id = p.BrandId
where 1=1 {0} GROUP BY r.productId ) t
LEFT JOIN product s on s.id = t.productId";
break;
case GroupByMethod.Category:
param.CategoryLevel = condition.CategoryLevel <= 0 ? 1 : condition.CategoryLevel;
sql = @"select s.`FullName` as Name ,OrderCount,SaleQuantity,SaleCostAmount,SaleAmount from (
select left(c.Id,2*@CategoryLevel) as CategoryId,count(DISTINCT r.saleorderId) as OrderCount,sum(r.Quantity) as SaleQuantity,sum(r.CostPrice*r.Quantity) as SaleCostAmount,sum(r.SalePrice*r.Quantity) as SaleAmount
from salereport r left join product p on p.id = r.productId
left join category c on c.Id = p.CategoryId
left join brand b on b.Id = p.BrandId
where 1=1 {0} GROUP BY left(c.Id,2*@CategoryLevel) LIMIT {1},{2} ) t
LEFT JOIN Category s on s.id = t.CategoryId";
sqlCount = @"select count(*) from (
select c.Id CategoryId,count(DISTINCT r.saleorderId) as OrderCount,sum(r.Quantity) as SaleQuantity,sum(r.CostPrice*r.Quantity) as SaleCostAmount,sum(r.SalePrice*r.Quantity) as SaleAmount
from salereport r left join product p on p.id = r.productId
left join category c on c.Id = p.CategoryId
left join brand b on b.Id = p.BrandId
where 1=1 {0} GROUP BY left(c.Id,2*@CategoryLevel) ) t
LEFT JOIN Category s on s.id = t.CategoryId";
sqlSum = @"select sum(OrderCount) as OrderCount ,sum(SaleQuantity) as SaleQuantity,sum(SaleCostAmount) as SaleCostAmount,sum(SaleAmount) as SaleAmount from (
select left(c.Id,2*@CategoryLevel) as CategoryId,count(DISTINCT r.saleorderId) as OrderCount,sum(r.Quantity) as SaleQuantity,sum(r.CostPrice*r.Quantity) as SaleCostAmount,sum(r.SalePrice*r.Quantity) as SaleAmount
from salereport r left join product p on p.id = r.productId
left join category c on c.Id = p.CategoryId
left join brand b on b.Id = p.BrandId
where 1=1 {0} GROUP BY left(c.Id,2*@CategoryLevel) ) t
LEFT JOIN Category s on s.id = t.CategoryId";
break;
case GroupByMethod.Supplier:
sql = @"select s.`name`,OrderCount,SaleQuantity,SaleCostAmount,SaleAmount from (
select r.SupplierId,count(DISTINCT r.saleorderId) as OrderCount,sum(r.Quantity) as SaleQuantity,sum(r.CostPrice*r.Quantity) as SaleCostAmount,sum(r.SalePrice*r.Quantity) as SaleAmount
from salereport r left join product p on p.id = r.productId
left join category c on c.Id = p.CategoryId
left join brand b on b.Id = p.BrandId
where 1=1 {0} GROUP BY r.SupplierId LIMIT {1},{2} ) t
LEFT JOIN supplier s on s.id = t.SupplierId";
sqlCount = @"select count(*) from (
select r.SupplierId,count(DISTINCT r.saleorderId) as OrderCount,sum(r.Quantity) as SaleQuantity,sum(r.CostPrice*r.Quantity) as SaleCostAmount,sum(r.SalePrice*r.Quantity) as SaleAmount
from salereport r left join product p on p.id = r.productId
left join category c on c.Id = p.CategoryId
left join brand b on b.Id = p.BrandId
where 1=1 {0} GROUP BY r.SupplierId ) t
LEFT JOIN supplier s on s.id = t.SupplierId";
sqlSum = @"select sum(OrderCount) as OrderCount ,sum(SaleQuantity) as SaleQuantity,sum(SaleCostAmount) as SaleCostAmount,sum(SaleAmount) as SaleAmount from (
select r.SupplierId,count(DISTINCT r.saleorderId) as OrderCount,sum(r.Quantity) as SaleQuantity,sum(r.CostPrice*r.Quantity) as SaleCostAmount,sum(r.SalePrice*r.Quantity) as SaleAmount
from salereport r left join product p on p.id = r.productId
left join category c on c.Id = p.CategoryId
left join brand b on b.Id = p.BrandId
where 1=1 {0} GROUP BY r.SupplierId ) t
LEFT JOIN supplier s on s.id = t.SupplierId";
break;
case GroupByMethod.Creator:
sql = @"select CONCAT(s.NickName,'(',s.`UserName`,')') as `Name`,OrderCount,SaleQuantity,SaleCostAmount,SaleAmount from (
select r.createdBy,count(DISTINCT r.saleorderId) as OrderCount,sum(r.Quantity) as SaleQuantity,sum(r.CostPrice*r.Quantity) as SaleCostAmount,sum(r.SalePrice*r.Quantity) as SaleAmount
from salereport r left join product p on p.id = r.productId
left join category c on c.Id = p.CategoryId
left join brand b on b.Id = p.BrandId
where 1=1 {0} GROUP BY r.createdBy LIMIT {1},{2} ) t
LEFT JOIN account s on s.id = t.createdBy";
sqlCount = @"select count(*) from (
select r.createdBy,count(DISTINCT r.saleorderId) as OrderCount,sum(r.Quantity) as SaleQuantity,sum(r.CostPrice*r.Quantity) as SaleCostAmount,sum(r.SalePrice*r.Quantity) as SaleAmount
from salereport r left join product p on p.id = r.productId
left join category c on c.Id = p.CategoryId
left join brand b on b.Id = p.BrandId
where 1=1 {0} GROUP BY r.createdBy ) t
LEFT JOIN account s on s.id = t.createdBy";
sqlSum = @"select sum(OrderCount) as OrderCount ,sum(SaleQuantity) as SaleQuantity,sum(SaleCostAmount) as SaleCostAmount,sum(SaleAmount) as SaleAmount from (
select r.createdBy,count(DISTINCT r.saleorderId) as OrderCount,sum(r.Quantity) as SaleQuantity,sum(r.CostPrice*r.Quantity) as SaleCostAmount,sum(r.SalePrice*r.Quantity) as SaleAmount
from salereport r left join product p on p.id = r.productId
left join category c on c.Id = p.CategoryId
left join brand b on b.Id = p.BrandId
where 1=1 {0} GROUP BY r.createdBy ) t
LEFT JOIN account s on s.id = t.createdBy";
break;
case GroupByMethod.Day:
sql = @"select t.`Name`,OrderCount,SaleQuantity,SaleCostAmount,SaleAmount from (
select DATE_FORMAT(r.createdOn,'%Y-%m-%d') as `Name`,count(DISTINCT r.saleorderId) as OrderCount,sum(r.Quantity) as SaleQuantity,sum(r.CostPrice*r.Quantity) as SaleCostAmount,sum(r.SalePrice*r.Quantity) as SaleAmount
from salereport r left join product p on p.id = r.productId
left join category c on c.Id = p.CategoryId
left join brand b on b.Id = p.BrandId
where 1=1 {0} GROUP BY DATE_FORMAT(r.createdOn,'%Y-%m-%d') LIMIT {1},{2} ) t";
sqlCount = @"select count(*) from (
select DATE_FORMAT(r.createdOn,'%Y-%m-%d') as `Name`,count(DISTINCT r.saleorderId) as OrderCount,sum(r.Quantity) as SaleQuantity,sum(r.CostPrice*r.Quantity) as SaleCostAmount,sum(r.SalePrice*r.Quantity) as SaleAmount
from salereport r left join product p on p.id = r.productId
left join category c on c.Id = p.CategoryId
left join brand b on b.Id = p.BrandId
where 1=1 {0} GROUP BY DATE_FORMAT(r.createdOn,'%Y-%m-%d') ) t";
sqlSum = @"select sum(OrderCount) as OrderCount ,sum(SaleQuantity) as SaleQuantity,sum(SaleCostAmount) as SaleCostAmount,sum(SaleAmount) as SaleAmount from (
select DATE_FORMAT(r.createdOn,'%Y-%m-%d') as `Name`,count(DISTINCT r.saleorderId) as OrderCount,sum(r.Quantity) as SaleQuantity,sum(r.CostPrice*r.Quantity) as SaleCostAmount,sum(r.SalePrice*r.Quantity) as SaleAmount
from salereport r left join product p on p.id = r.productId
left join category c on c.Id = p.CategoryId
left join brand b on b.Id = p.BrandId
where 1=1 {0} GROUP BY DATE_FORMAT(r.createdOn,'%Y-%m-%d') ) t";
break;
default:
break;
}
//rows = this._query.FindPage<ProductDto>(page.PageIndex, page.PageSize).Where<Product>(where, param);
sql = string.Format(sql, where, (page.PageIndex - 1) * page.PageSize, page.PageSize);
var rows = this._query.FindAll <SaleReportDto>(sql, param);
sqlCount = string.Format(sqlCount, where);
page.Total = this._query.Context.ExecuteScalar <int>(sqlCount, param);
//汇总数据
sqlSum = string.Format(sqlSum, where);
var sumStoreInventory = this._query.Find <SumSaleReport>(sqlSum, param) as SumSaleReport;
page.SumColumns.Add(new SumColumn("OrderCount", sumStoreInventory.OrderCount.ToString()));
page.SumColumns.Add(new SumColumn("SaleQuantity", sumStoreInventory.SaleQuantity.ToString()));
page.SumColumns.Add(new SumColumn("SaleCostAmount", sumStoreInventory.SaleCostAmount.ToString("F4")));
page.SumColumns.Add(new SumColumn("SaleAmount", sumStoreInventory.SaleAmount.ToString("F2")));
page.SumColumns.Add(new SumColumn("ProfitAmount", sumStoreInventory.ProfitAmount.ToString("F2")));
page.SumColumns.Add(new SumColumn("ProfitPercent", sumStoreInventory.ProfitPercent.ToString("F2") + "%"));
return(rows);
}
public IEnumerable <RealTimeSaleReportDto> QueryRealTimeSaleReport(Pager page, SearchSaleReport condition)
{
dynamic param = new ExpandoObject();
string where = "";
if (condition.StartDate.HasValue)
{
where += "and h.CreatedOn >=@StartDate ";
param.StartDate = condition.StartDate.Value;
}
if (condition.EndDate.HasValue)
{
where += "and h.CreatedOn < @EndDate ";
param.EndDate = condition.EndDate.Value.AddDays(1);
}
if (!string.IsNullOrEmpty(condition.StoreId) && condition.StoreId != "0")
{
where += "and h.StoreId in @StoreId ";
param.StoreId = condition.StoreId.Split(',').ToIntArray();
}
if (condition.OrderType != 0)
{
where += condition.OrderType == 1 ? "and h.ChangeQuantity<0 " : "and h.ChangeQuantity>0 ";
}
var sql = @"select s.name ,t.* from (
select h.storeid,count(DISTINCT h.billcode) as orderCount, abs(sum(h.ChangeQuantity)) as SaleQuantity,abs(sum(h.ChangeQuantity*h.Price)) as SaleCostAmount,abs(sum(h.ChangeQuantity*h.SalePrice)) as SaleAmount
from StoreInventoryHistory h
where h.BillType in (1,2) {0}
group by h.storeid) t left join store s on t.storeid = s.id ";
if (!page.toExcel)
{
sql += string.Format(" LIMIT {0},{1}", (page.PageIndex - 1) * page.PageSize, page.PageSize);
}
sql = string.Format(sql, where);
var rows = this._query.FindAll <RealTimeSaleReportDto>(sql, param);
var sqlSum = @"select count(*) as totalCount,sum(t.OrderCount) as OrderCount,sum(Quantity) as SaleQuantity, sum(SaleCostAmount) as SaleCostAmount,sum(SaleAmount) as SaleAmount from (
select h.storeid,count(DISTINCT h.billcode) as orderCount, abs(sum(h.ChangeQuantity)) as Quantity,abs(sum(h.ChangeQuantity*h.Price)) as SaleCostAmount,abs(sum(h.ChangeQuantity*h.SalePrice)) as SaleAmount
from StoreInventoryHistory h
where h.BillType in (1,2) {0}
group by h.storeid) t left join store s on t.storeid = s.id";
//汇总数据
sqlSum = string.Format(sqlSum, where);
var sumStoreInventory = this._query.Find <SumSaleReport>(sqlSum, param) as SumSaleReport;
page.Total = sumStoreInventory.TotalCount;
page.SumColumns.Add(new SumColumn("OrderCount", sumStoreInventory.OrderCount.ToString()));
page.SumColumns.Add(new SumColumn("SaleQuantity", sumStoreInventory.SaleQuantity.ToString()));
page.SumColumns.Add(new SumColumn("SaleCostAmount", sumStoreInventory.SaleCostAmount.ToString("F4")));
page.SumColumns.Add(new SumColumn("SaleAmount", sumStoreInventory.SaleAmount.ToString("F2")));
page.SumColumns.Add(new SumColumn("ProfitAmount", sumStoreInventory.ProfitAmount.ToString("F2")));
page.SumColumns.Add(new SumColumn("ProfitPercent", sumStoreInventory.ProfitPercent.ToString("F2") + "%"));
return(rows);
}
public IEnumerable <SaleReportDto> QuerySaleReport(Pager page, SearchSaleReport condition)
{
var result = QuerySaleReportGroupByStore(page, condition);
return(result);
}
