private async Task <List <ShopReportData> > GetShopReportData(GetShopReportDataInput input)
{
List <ShopReportData> entity = null;
if (input.TimeType == 2)
{
entity = await _retailerRepository.GetShopReportByRangeAsync(input.BeginTime, input.EndTime);
}
else
{
entity = await _retailerRepository.GetShopReportAsync();
}
var statisticsListDto = entity.MapTo <List <ShopReportData> >();
return(statisticsListDto);
}
public async Task <APIResultDto> ExportShopReportDataByOrganizationExcel(GetShopReportDataInput input)
{
try
{
var exportData = await GetShopReportDataByOrganizationExcel(input);
var result = new APIResultDto();
result.Code = 0;
result.Data = SaveShopReportDataByOrganizationExcelExcel($"{input.Organization}报表统计.xlsx", exportData);
return(result);
}
catch (Exception ex)
{
Logger.ErrorFormat("ExportShopExcel errormsg{0} Exception{1}", ex.Message, ex);
return(new APIResultDto()
{
Code = 901, Msg = "网络忙...请待会儿再试！"
});
}
}
/// <summary>
/// 查询店铺消费报表
/// </summary>
/// <param name="input"></param>
/// <returns></returns>
public async Task <PagedResultDto <ShopReportData> > GetDataStatisticsAsync(GetShopReportDataInput input)
{
List <ShopReportData> entity = null;
if (input.TimeType == 2)
{
entity = await _retailerRepository.GetShopReportByRangeAsync(input.BeginTime, input.EndTime);
}
else
{
entity = await _retailerRepository.GetShopReportAsync();
}
var shopReportDataCount = entity.Count();
var statisticsListDto = entity.MapTo <List <ShopReportData> >();
return(new PagedResultDto <ShopReportData>(
shopReportDataCount,
statisticsListDto
));
}
private async Task <List <ShopReportData> > GetShopReportDataByOrganizationExcel(GetShopReportDataInput input)
{
var retailer = _retailerRepository.GetAll();
var shop = _shopRepository.GetAll().Where(v => v.Status == ShopAuditStatus.已审核);
var products = _productRepository.GetAll();
var purchaserecord = _purchaserecordRepository.GetAll();
List <ShopReportData> entity = null;
if (input.OrganizatType == 1)
{
entity = await(from pur in purchaserecord
join p in products on pur.ProductId equals p.Id
group new { pur.Specification, pur.Quantity, pur.Id, p.Price } by new { pur.Specification } into g
select new ShopReportData()
{
Specification = g.Key.Specification,
PriceTotal = g.Sum(v => v.Quantity * v.Price),
ScanQuantity = g.Sum(v => v.Quantity),
ScanFrequency = g.GroupBy(v => v.Id).Count()
}).WhereIf(!string.IsNullOrEmpty(input.Specification), r => r.Specification.Contains(input.Specification)).OrderByDescending(v => v.ScanQuantity).ToListAsync();
}
else if (input.OrganizatType == 2)
{
entity = await(from pur in purchaserecord
join p in products on pur.ProductId equals p.Id
join s in shop on pur.ShopId equals s.Id
join r in retailer on s.RetailerId equals r.Id
where r.BranchCompany == input.Organization
group new { pur.Specification, pur.Quantity, pur.Id, p.Price } by new { pur.Specification } into g
select new ShopReportData()
{
Specification = g.Key.Specification,
PriceTotal = g.Sum(v => v.Quantity * v.Price),
ScanQuantity = g.Sum(v => v.Quantity),
ScanFrequency = g.GroupBy(v => v.Id).Count()
}).WhereIf(!string.IsNullOrEmpty(input.Specification), r => r.Specification.Contains(input.Specification)).OrderByDescending(v => v.ScanQuantity).ToListAsync();
}
else if (input.OrganizatType == 3)
{
entity = await(from pur in purchaserecord
join p in products on pur.ProductId equals p.Id
join s in shop on pur.ShopId equals s.Id
join r in retailer on s.RetailerId equals r.Id
where r.Area == input.Organization
group new { pur.Specification, pur.Quantity, pur.Id, p.Price } by new { pur.Specification } into g
select new ShopReportData()
{
Specification = g.Key.Specification,
PriceTotal = g.Sum(v => v.Quantity * v.Price),
ScanQuantity = g.Sum(v => v.Quantity),
ScanFrequency = g.GroupBy(v => v.Id).Count()
}).WhereIf(!string.IsNullOrEmpty(input.Specification), r => r.Specification.Contains(input.Specification)).OrderByDescending(v => v.ScanQuantity).ToListAsync();
}
else
{
entity = await(from pur in purchaserecord
join p in products on pur.ProductId equals p.Id
join s in shop on pur.ShopId equals s.Id
join r in retailer on s.RetailerId equals r.Id
where r.SlsmanName == input.Organization
group new { pur.Specification, pur.Quantity, pur.Id, p.Price } by new { pur.Specification } into g
select new ShopReportData()
{
Specification = g.Key.Specification,
PriceTotal = g.Sum(v => v.Quantity * v.Price),
ScanQuantity = g.Sum(v => v.Quantity),
ScanFrequency = g.GroupBy(v => v.Id).Count()
}).WhereIf(!string.IsNullOrEmpty(input.Specification), r => r.Specification.Contains(input.Specification)).OrderByDescending(v => v.ScanQuantity).ToListAsync();
}
int shopReportDataCount =
await(from pur in purchaserecord
join p in products on pur.ProductId equals p.Id
group new { pur.Specification, pur.Quantity, pur.Id, p.Price } by new { pur.Specification } into g
select new ShopReportData()
{
Specification = g.Key.Specification,
PriceTotal = g.Sum(v => v.Quantity * v.Price),
ScanQuantity = g.Sum(v => v.Quantity),
ScanFrequency = g.GroupBy(v => v.Id).Count()
}).WhereIf(!string.IsNullOrEmpty(input.Specification), r => r.Specification.Contains(input.Specification)).CountAsync();
var statisticsListDto = entity.MapTo <List <ShopReportData> >();
return(statisticsListDto);
}
/// <summary>
/// 店铺报表详情
/// </summary>
/// <param name="input"></param>
/// <returns></returns>
public async Task <PagedResultDto <ShopReportData> > GetShopReportDataDetailAsync(GetShopReportDataInput input)
{
var retailer = _retailerRepository.GetAll().AsNoTracking();
var shop = _shopRepository.GetAll().Where(v => v.Status == ShopAuditStatus.已审核).AsNoTracking();
var products = _productRepository.GetAll().AsNoTracking();
var purchaserecord = _purchaserecordRepository.GetAll().AsNoTracking();
if (input.OrganizatType == 1)
{
var query = from pur in purchaserecord
join p in products on pur.ProductId equals p.Id
select new
{
pur.Specification,
pur.Quantity,
p.Price,
pur.Id
};
var result = (from q in query
group q by q.Specification into g
select new ShopReportData()
{
Specification = g.Key,
PriceTotal = g.Sum(v => v.Quantity * v.Price),
ScanQuantity = g.Sum(v => v.Quantity),
ScanFrequency = g.GroupBy(v => v.Id).Count()
}).WhereIf(!string.IsNullOrEmpty(input.Specification), r => r.Specification.Contains(input.Specification)).AsNoTracking();
var statisticsListDto = await result.AsNoTracking().OrderByDescending(v => v.ScanQuantity).PageBy(input).ToListAsync();
int shopReportDataCount = await result.CountAsync();
return(new PagedResultDto <ShopReportData>(
shopReportDataCount,
statisticsListDto
));
}
else if (input.OrganizatType == 2)
{
var query = from pur in purchaserecord
join p in products on pur.ProductId equals p.Id
join s in shop on pur.ShopId equals s.Id
join r in retailer on s.RetailerId equals r.Id
where r.BranchCompany == input.Organization
select new
{
pur.Specification,
pur.Quantity,
p.Price,
pur.Id
};
var result = (from q in query
group q by q.Specification into g
select new ShopReportData()
{
Specification = g.Key,
PriceTotal = g.Sum(v => v.Quantity * v.Price),
ScanQuantity = g.Sum(v => v.Quantity),
ScanFrequency = g.GroupBy(v => v.Id).Count()
}).WhereIf(!string.IsNullOrEmpty(input.Specification), r => r.Specification.Contains(input.Specification)).AsNoTracking();
var statisticsListDto = await result.AsNoTracking().OrderByDescending(v => v.ScanQuantity).PageBy(input).ToListAsync();
int shopReportDataCount = await result.CountAsync();
return(new PagedResultDto <ShopReportData>(
shopReportDataCount,
statisticsListDto
));
}
else if (input.OrganizatType == 3)
{
var query = from pur in purchaserecord
join p in products on pur.ProductId equals p.Id
join s in shop on pur.ShopId equals s.Id
join r in retailer on s.RetailerId equals r.Id
where r.Area == input.Organization
select new
{
pur.Specification,
pur.Quantity,
p.Price,
pur.Id
};
var result = (from q in query
group q by q.Specification into g
select new ShopReportData()
{
Specification = g.Key,
PriceTotal = g.Sum(v => v.Quantity * v.Price),
ScanQuantity = g.Sum(v => v.Quantity),
ScanFrequency = g.GroupBy(v => v.Id).Count()
}).WhereIf(!string.IsNullOrEmpty(input.Specification), r => r.Specification.Contains(input.Specification)).AsNoTracking();
var statisticsListDto = await result.AsNoTracking().OrderByDescending(v => v.ScanQuantity).PageBy(input).ToListAsync();
int shopReportDataCount = await result.CountAsync();
return(new PagedResultDto <ShopReportData>(
shopReportDataCount,
statisticsListDto
));
}
else
{
var query = from pur in purchaserecord
join p in products on pur.ProductId equals p.Id
join s in shop on pur.ShopId equals s.Id
join r in retailer on s.RetailerId equals r.Id
where r.SlsmanName == input.Organization
select new
{
pur.Specification,
pur.Quantity,
p.Price,
pur.Id
};
var result = (from q in query
group q by q.Specification into g
select new ShopReportData()
{
Specification = g.Key,
PriceTotal = g.Sum(v => v.Quantity * v.Price),
ScanQuantity = g.Sum(v => v.Quantity),
ScanFrequency = g.GroupBy(v => v.Id).Count()
}).WhereIf(!string.IsNullOrEmpty(input.Specification), r => r.Specification.Contains(input.Specification)).AsNoTracking();
var statisticsListDto = await result.AsNoTracking().OrderByDescending(v => v.ScanQuantity).PageBy(input).ToListAsync();
int shopReportDataCount = await result.CountAsync();
return(new PagedResultDto <ShopReportData>(
shopReportDataCount,
statisticsListDto
));
}
}
