public static ReportGraph Create(Report report, ReportGraphType type = ReportGraphType.Sale) { var graph = new ReportGraph(type, report.Filter.From.Value, report.Filter.To.Value); graph.Current = graph.From; graph.Warehouses = report.Warehouses; var count = 0; while (true) { var to = graph.Current.AddHours(graph.Step); var total = 0m; foreach (var wh in report.Warehouses) { var paidOrders = report.PaidOrders.Where(i => i.WarehouseID == wh.ID && i.SubmitDate >= graph.Current && i.SubmitDate <= to).Sum(i => i.Paid); var incomes = report.Incomes.Where(i => i.WarehouseID == wh.ID && i.SubmitDate >= graph.Current && i.SubmitDate <= to).Sum(i => i.Amount); var outcomes = report.Outcomes.Where(i => i.WarehouseID == wh.ID && i.SubmitDate >= graph.Current && i.SubmitDate <= to).Sum(i => i.Amount); var employees = report.Employees.Where(i => i.WarehouseID == wh.ID); var salaries = report.Salaries.Where(i => employees.FirstOrDefault(e => e.ID == i.EmployeeID) != null && i.Month.HasValue && i.Month.Value >= graph.Current && i.Month.Value < to).Sum(i => i.CalculatedTotal); var revenue = 0m; switch (type) { case ReportGraphType.Revenue: revenue = (paidOrders + incomes) * 0.65m - outcomes - salaries; break; case ReportGraphType.Sale: revenue = paidOrders; break; case ReportGraphType.SaleRelative: var store = report.Stores.FirstOrDefault(i => i.WarehouseID == wh.ID); revenue = store != null && store.SalePoint > 0 ? Math.Round(paidOrders / store.SalePoint * 100, 2) : 0m; break; default: break; } graph.Revenues.Add(new GraphSalePoint(graph.Current, revenue, wh.ID)); total += revenue; } if (!report.Filter.WarehouseID.HasValue) { graph.Revenues.Add(new GraphSalePoint(graph.Current, total)); } graph.Current = graph.Current.AddHours(graph.Step); count++; if (graph.Current >= report.Filter.To.Value || count == 31) { break; } } graph.Count = count; graph.Max = graph.Revenues.Max(i => i.Value); return(graph); }
public static Report Get(int userID, int employeeID, int bussinessID, ReportFilter filter = null, bool justOrders = false) { var result = new Report(); result.Filter = RenderFilter(filter); result.Filter.Max = 10; try { result.MainEmployeeID = null; using (var con = Repo.DB.SKtimeManagement) { var login = Login.Get(userID); if (login.Username != "admin" && login.Type == LoginType.Sale) { result.MainEmployeeID = employeeID; } result.PaidOrders = con.Query <OrderRecord>(PaidOrdersQuery(userID, employeeID, bussinessID, result.Filter)).ToList(); if (!justOrders) { result.Transactions = con.Query <Transaction>(OrderTransactions(userID, employeeID, bussinessID, result.Filter)).ToList(); result.Warehouses.AddRange(result.PaidOrders.GroupBy(i => i.WarehouseID).Select(i => new WarehouseInfo() { ID = i.Key, Name = i.FirstOrDefault().WarehouseName })); result.Incomes = con.Query <IncomeInfo>(IncomesQuery(userID, employeeID, bussinessID, result.Filter)).ToList(); result.Warehouses.AddRange(result.Incomes.Where(i => result.Warehouses.FirstOrDefault(w => w.ID == i.WarehouseID) == null).GroupBy(i => i.WarehouseID).Select(i => new WarehouseInfo() { ID = i.Key, Name = i.FirstOrDefault().WarehouseName })); result.Outcomes = con.Query <OutcomeInfo>(OutcomesQuery(userID, employeeID, bussinessID, result.Filter)).ToList(); result.Warehouses.AddRange(result.Outcomes.Where(i => result.Warehouses.FirstOrDefault(w => w.ID == i.WarehouseID) == null).GroupBy(i => i.WarehouseID).Select(i => new WarehouseInfo() { ID = i.Key, Name = i.FirstOrDefault().WarehouseName })); if (result.Warehouses.Count > 0) { result.Stores = con.Query <StoreInfo>(StoreQuery(bussinessID, result.Warehouses)).ToList(); } result.ProductsByQuantity = con.Query <ProductSaleReport>(PopularProductQuery(bussinessID, result.Filter, ProductSaleOrder.ByQuantityTotal)).ToList(); result.ProductsByRevenue = con.Query <ProductSaleReport>(PopularProductQuery(bussinessID, result.Filter, ProductSaleOrder.ByRevenueTotal)).ToList(); result.Salaries = con.Query <SalaryInfo>(SalariesQuery(bussinessID, result.Filter)).ToList(); if (result.Filter.To.Value.Year == result.Filter.From.Value.Year) { result.LastYearFrom = filter.From.Value.AddYears(-1); result.LastYearTo = filter.To.Value.AddYears(-1); result.LastYearRevenue = con.Query <decimal>(RevenueQuery(userID, employeeID, bussinessID, result.Filter, result.LastYearFrom, result.LastYearTo)).FirstOrDefault(); } if ((result.Filter.To.Value - result.Filter.From.Value).TotalHours <= 24) { result.LastPeriodFrom = filter.From.Value.AddDays(-1); result.LastPeriodTo = filter.To.Value.AddDays(-1); } else if ((result.Filter.To.Value - result.Filter.From.Value).TotalHours <= 24 * 31) { result.LastPeriodFrom = filter.From.Value.AddMonths(-1); result.LastPeriodTo = filter.To.Value.AddMonths(-1); } else { result.LastPeriodFrom = filter.From.Value.AddHours(-(filter.To.Value - filter.From.Value).TotalHours); result.LastPeriodTo = filter.From.Value; } result.LastPeriodRevenue = con.Query <decimal>(RevenueQuery(userID, employeeID, bussinessID, result.Filter, result.LastPeriodFrom, result.LastPeriodTo)).FirstOrDefault(); } } result.Employees.AddRange(result.PaidOrders.Where(i => !result.MainEmployeeID.HasValue || (result.MainEmployeeID.HasValue && i.EmployeeID == result.MainEmployeeID)).GroupBy(i => i.EmployeeID).Select(i => new EmployeeSale() { ID = i.FirstOrDefault().EmployeeID, Name = i.FirstOrDefault().EmployeeName, WarehouseID = i.FirstOrDefault().WarehouseID, WarehouseName = i.FirstOrDefault().WarehouseName, OrderCount = i.Count(), ReturnedCount = i.Where(o => o.Status == OrderStatus.Refunded).Count(), Total = i.Sum(o => o.Paid), Returned = i.Where(o => o.Status == OrderStatus.Refunded).Sum(o => o.Total) })); result.Graph = ReportGraph.Create(result); result.RevenueGraph = ReportGraph.Create(result, ReportGraphType.Revenue); result.RelativeGraph = ReportGraph.Create(result, ReportGraphType.SaleRelative); } catch (Exception e) { } return(result); }