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));
        }
Example #6
0
        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);
        }