public async Task <ActionResult> DesginCostStatisticsExport(ReportQueryBaseDto reportQuery)
{
var list = (await GetDesginCostStatistic(reportQuery)).ToList();
var titles = new string[] { "序号", "订单", "支付设计成本（元）", "设计费（元）" };
var result = Dickson.Web.Helper.ExcelHelp.Export(titles, "设计费用统计", ws =>
{
var row = 2;
int index = 1;
foreach (var desginCostStatistic in list)
{
ws.Cells[row, 1].Value = index;
ws.Cells[row, 2].Value = string.IsNullOrEmpty(desginCostStatistic.Id)
? "总计："
: desginCostStatistic.Id;
ws.Cells[row, 3].Value = desginCostStatistic.DesginCost;
ws.Cells[row, 4].Value = desginCostStatistic.OutputWaxCost;
row++;
index++;
}
;
});
return(result);
}
public async Task <IEnumerable <OrderMainStoneStatistic> > GetOrderMainStoneStatisticsAsync(
ReportQueryBaseDto reportQuery)
{
var query = DbContext.Set <OrderMainStoneInfo>().AsQueryable();
if (!string.IsNullOrEmpty(reportQuery.CustomerId))
{
query = query.Where(a => a.Order.CustomerId == reportQuery.CustomerId);
}
if (reportQuery.StatisticStartDate.HasValue)
{
query = query.Where(t => t.Created > reportQuery.StatisticStartDate.Value);
}
if (reportQuery.StatisticEndDate.HasValue)
{
var endDate = reportQuery.StatisticEndDate.Value.AddDays(1);
query = query.Where(t => t.Created < endDate);
}
if (!string.IsNullOrEmpty(reportQuery.OrderId))
{
query = query.Where(a => a.OrderId.Contains(reportQuery.OrderId));
}
return((await query.OrderBy(t => t.Created).ToListAsync()).Select(t => new OrderMainStoneStatistic
{
OrderId = t.OrderId,
CustomerName = t.Order.Customer.Name,
Created = t.Created.ToShortDateString(),
MainStoneName = t.MainStone.Name,
Risk = t.MainStone.RiskType.GetDisplayName(),
MainStoneWeight = t.Weight
}));
}
public async Task <ActionResult> DesginCostStatistics(ReportQueryBaseDto reportQuery)
{
if (!Request.IsAjaxRequest())
{
return(View(reportQuery));
}
var list = await GetDesginCostStatistic(reportQuery);
return(Json(true, string.Empty, list));
}
public async Task <ActionResult> AccountStatistics(ReportQueryBaseDto reportQuery)
{
if (!Request.IsAjaxRequest())
{
return(View(reportQuery));
}
var manager = new ReconciliationManager(User);
var list = await manager.GetAccountStatisticsAsync(reportQuery);
return(Json(true, string.Empty, list));
}
public async Task <ActionResult> OrderMainStoneStatistics(ReportQueryBaseDto reportQuery)
{
if (!Request.IsAjaxRequest())
{
return(View(reportQuery));
}
var manager = new OrderMainStoneInfoManager(User);
var list = await manager.GetOrderMainStoneStatisticsAsync(reportQuery);
return(Json(true, string.Empty, list));
}
public async Task <IEnumerable <DesginCostStatistic> > DesginCostStatistics(ReportQueryBaseDto reportQuery)
{
var query = DbContext.Set <Order>().Where(t => t.DesginCost > 0 && t.OutputWaxCost > 0).AsQueryable();
if (reportQuery.StatisticStartDate.HasValue)
{
query = query.Where(t => t.DesginAuditTime > reportQuery.StatisticStartDate.Value);
}
if (reportQuery.StatisticEndDate.HasValue)
{
var endDate = reportQuery.StatisticEndDate.Value.AddDays(1);
query = query.Where(t => t.DesginAuditTime < endDate);
}
return((await query.OrderBy(t => t.DesginAuditTime).ToListAsync()).Select(t => new DesginCostStatistic
{
Id = t.Id,
OutputWaxCost = t.OutputWaxCost,
DesginCost = t.DesginCost
}));
}
public async Task <FileStreamResult> AccountStatisticsExport(ReportQueryBaseDto reportQuery)
{
var manager = new ReconciliationManager(User);
var accountStatistics = await manager.GetAccountStatisticsAsync(reportQuery);
var titles = new string[] { "序号", "客户", "累积消费（元）", "当前欠款（元）" };
var result = Dickson.Web.Helper.ExcelHelp.Export(titles, "账目报表", ws =>
{
var row = 2;
int index = 1;
foreach (var accountStatistic in accountStatistics)
{
ws.Cells[row, 1].Value = index;
ws.Cells[row, 2].Value = accountStatistic.CustomerName;
ws.Cells[row, 3].Value = accountStatistic.PaymentInQuery;
ws.Cells[row, 4].Value = accountStatistic.SurplusArrearage;
row++;
index++;
}
;
});
return(result);
}
public async Task <IEnumerable <OrderSetStoneStatistic> > GetOrderSetStoneStatisticsAsync(ReportQueryBaseDto reportQuery)
{
var query = DbContext.Set <ShipmentOrderInfo>().Where(t => t.Order.ComplayId == User.CompanyId);
if (reportQuery.StatisticStartDate.HasValue)
{
query = query.Where(t => t.ShipmentOrder.Created > reportQuery.StatisticStartDate.Value);
}
if (reportQuery.StatisticEndDate.HasValue)
{
var endDate = reportQuery.StatisticEndDate.Value.AddDays(1);
query = query.Where(t => t.ShipmentOrder.Created < endDate);
}
if (!string.IsNullOrEmpty(reportQuery.CustomerId))
{
query = query.Where(t => t.ShipmentOrder.CustomerId == reportQuery.CustomerId);
}
var orderIds = query.Select(r => r.Order.Id);
if (!orderIds.Any())
{
return(Enumerable.Empty <OrderSetStoneStatistic>());
}
var orderSetStones = await(from a in DbContext.Set <OrderSetStoneInfo>()
join b in DbContext.Set <Order>()
on a.OrderId equals b.Id
join c in DbContext.Set <CustomerDiscountRate>()
on b.CustomerId equals c.CustomerId
where orderIds.Contains(a.OrderId)
group new { a, c } by new { a.MatchStoneId, a.MathchStoneName })
.ToListAsync();
var orderSetStoneStatistics = orderSetStones.Select(g => new OrderSetStoneStatistic
{
SetStoneName = g.Key.MathchStoneName,
Weight = g.Sum(ga => (decimal)ga.a.Weight),
Number = g.Sum(ga => ga.a.Number),
SetStoneAmount = g.Sum(ga => ((decimal)(ga.c.SideStone / 100.0 * ga.a.Price * ga.a.Weight)))
}).ToList();
var totalWeight = orderSetStoneStatistics.Sum(r => r.Weight);
var totalNumber = orderSetStoneStatistics.Sum(r => r.Number);
var setStoneAmount = orderSetStoneStatistics.Sum(r => r.SetStoneAmount);
orderSetStoneStatistics.Add(new OrderSetStoneStatistic {
SetStoneName = "总计", Weight = totalWeight, Number = totalNumber, SetStoneAmount = setStoneAmount
});
return(orderSetStoneStatistics);
}
public async Task <IEnumerable <AccountStatistic> > GetAccountStatisticsAsync(ReportQueryBaseDto reportQuery)
{
var query = DbContext.Set <Reconciliation>().Where(o => o.CompanyId == User.CompanyId);
if (!string.IsNullOrEmpty(reportQuery.CustomerId))
{
query = query.Where(o => o.CustomerId == reportQuery.CustomerId);
}
var AccountStatistics = await query.GroupBy(r => r.CustomerId).Select(a => new
{
CustomerId = a.Key,
SurplusArrearage = a.Sum(r => r.Type == ReconciliationType.Payment ? -r.Amount : r.Amount),
}).ToListAsync();
if (reportQuery.StatisticStartDate.HasValue)
{
query = query.Where(o => o.Created > reportQuery.StatisticStartDate.Value);
}
if (reportQuery.StatisticEndDate.HasValue)
{
var endDate = reportQuery.StatisticEndDate.Value.AddDays(1);
query = query.Where(o => o.Created < endDate);
}
var AccountStatisticsInQuery = await query.Where(r => r.Type == ReconciliationType.Payment).GroupBy(r => r.CustomerId).Select(a => new
{
CustomerId = a.Key,
Payment = a.Sum(r => r.Amount)
}).ToListAsync();
var customers = await new UserManager().GetAllCustomersAsync();
var statistic = (from a in AccountStatistics
join b in AccountStatisticsInQuery
on a.CustomerId equals b.CustomerId
into temp
from ur in temp.DefaultIfEmpty()
join c in customers
on a.CustomerId equals c.Id
select new AccountStatistic
{
CustomerId = a.CustomerId,
CustomerName = c.Name,
PaymentInQuery = Math.Round(ur?.Payment ?? 0, 2),
SurplusArrearage = Math.Round(a.SurplusArrearage, 2)
}).OrderByDescending(c => c.SurplusArrearage).ToList();
var totalPaymentInQuery = Math.Round(statistic.Sum(r => r.PaymentInQuery), 2);
var totalSurplusArrearage = Math.Round(statistic.Sum(r => r.SurplusArrearage), 2);
statistic.Add(new AccountStatistic {
CustomerName = "总计", PaymentInQuery = totalPaymentInQuery, SurplusArrearage = totalSurplusArrearage
});
return(statistic);
}
private async Task <IEnumerable <DesginCostStatistic> > GetDesginCostStatistic(ReportQueryBaseDto reportQuery)
{
var manager = new OrderManager(User);
var list = (await manager.DesginCostStatistics(reportQuery)).ToList();
if (list.Any())
{
var total = new DesginCostStatistic()
{
DesginCost = list.Sum(r => r.DesginCost),
OutputWaxCost = list.Sum(r => r.OutputWaxCost)
};
list.Add(total);
}
return(list);
}