public int Count(ExpenseGetBinding binding)
{
using (var context = GetMainContext())
{
var result = context.Expenses.WhereUser(UserId)
.Where(binding, context);
return(result.Count());
}
}
public int CountTypes(ExpenseGetBinding binding)
{
using (var context = GetMainContext())
{
return(context.Expenses.WhereUser(UserId)
.Include(x => x.Vendor)
.Where(binding, context)
.GroupBy(x => x.ExpenseTypeId)
.Count());
}
}
public PagedView <View.Expense> Get(ExpenseGetBinding binding)
{
using (var context = GetMainContext())
{
return(context.Expenses.WhereUser(UserId)
.IncludeAll()
.Where(binding, context)
.OrderBy(binding)
.ThenByDescending(x => x.Created)
.Select(x => new View.Expense(x))
.ToPagedView(binding));
}
}
public IEnumerable <KeyValuePair <int, int> > CountByYear(ExpenseGetBinding binding)
{
using (var context = GetMainContext())
{
var to = binding.To ?? DateTime.Now;
return(context.Expenses.WhereUser(UserId)
.Where(binding, context)
.GroupBy(x => x.Date.Year)
.Select(x => new KeyValuePair <int, int>(x.Key, x.Count()))
.ToList()
.FillMissingYears(year => new KeyValuePair <int, int>(0, year), binding.From?.Year, to.Year));
}
}
public IEnumerable <KeyValuePair <int, int> > CountByMonth(ExpenseGetBinding binding)
{
using (var context = GetMainContext())
{
var to = binding.To ?? DateTime.Now;
return(context.Expenses.WhereUser(UserId)
.Where(binding, context)
.GroupBy(x => x.Date.Month)
.OrderBy(x => x.Key)
.Select(x => new KeyValuePair <int, int>(x.Key, x.Count()))
.ToList());
}
}
public IEnumerable <KeyValuePair <string, int> > CountByMonthOfYear(ExpenseGetBinding binding)
{
using (var context = GetMainContext())
{
var to = binding.To ?? DateTime.Now;
return(context.Expenses.WhereUser(UserId)
.Where(binding, context)
.GroupBy(x => new { x.Date.Year, x.Date.Month })
.Select(x => new GroupedByMonth <int>(x.Count(), x.Key.Year, x.Key.Month))
.ToList()
.FillMissingMonths(datetime => new GroupedByMonth <int>(0, datetime.Year, datetime.Month), binding.From, to)
.Select(x => new KeyValuePair <string, int>($"{x.Year}-{x.Month}", x.Data)));
}
}
public IEnumerable <KeyValuePair <int, int> > CountByDayOfWeek(ExpenseGetBinding binding)
{
using (var context = GetMainContext())
{
DateTime FirstSunday = new DateTime(2000, 1, 2);
var to = binding.To ?? DateTime.Now;
return(context.Expenses
.WhereUser(UserId)
.Where(binding, context)
.GroupBy(x => ((int)EF.Functions.DateDiffDay((DateTime?)FirstSunday, (DateTime?)x.Date) - 1) % 7)
.OrderBy(x => x.Key)
.Select(x => new KeyValuePair <int, int>(x.Key, x.Count()))
.ToList());
}
}
public IEnumerable <KeyValuePair <string, int> > CountByDay(ExpenseGetBinding binding)
{
using (var context = GetMainContext())
{
var to = binding.To ?? DateTime.Now;
return(context.Expenses.WhereUser(UserId)
.Where(binding, context)
.GroupBy(x => x.Date)
.OrderByDescending(x => x.Key)
.Select(x => new KeyValuePair <DateTime, int>(x.Key, x.Count()))
.ToList()
.FillMissingDates(x => x.Key, x => new KeyValuePair <DateTime, int>(x, 0), binding.From, to)
.Select(x => new KeyValuePair <string, int>(x.Key.ToString("yyyy-MM-dd"), x.Value)));
}
}
public async Task <IEnumerable <string> > GetTopDescriptions(ExpenseGetBinding binding)
{
using (var context = GetMainContext())
{
return(await context.Expenses
.WhereUser(UserId)
.Where(binding, context)
.Where(x => !string.IsNullOrEmpty(x.Comment))
.GroupBy(x => x.Comment)
.Select(x => new { x.Key, Count = x.Count() })
.OrderByDescending(x => x.Count)
.Take(5)
.Select(x => x.Key)
.ToListAsync());
}
}
public PagedView <KeyValuePair <ExpenseType, int> > CountByType(ExpenseGetBinding binding)
{
using (var context = GetMainContext())
{
return(context.Expenses.WhereUser(UserId)
.Where(binding, context)
.Include(x => x.ExpenseType)
.GroupBy(x => new
{
x.ExpenseType.Name,
x.ExpenseType.ValueId
})
.OrderByDescending(x => x.Count())
.Select(x => new KeyValuePair <ExpenseType, int>(new ExpenseType()
{
Id = x.Key.ValueId,
Name = x.Key.Name,
}, x.Count()))
.ToPagedView(binding));
}
}
public static IQueryable <Expense> Where(this IQueryable <Expense> query, ExpenseGetBinding b, MainContext context)
{
var cardIds = context.Cards.GetIds(b.CardId);
var currencyIds = context.Currencies.GetIds(b.CurrencyId);
var expenseTypeIds = context.ExpenseTypes.GetIds(b.TypeId);
var paymentTypeIds = context.PaymentTypes.GetIds(b.PaymentTypeId);
var vendorIds = context.Vendors.GetIds(b.VendorId);
IEnumerable <ExpenseType> expenseTypes = null;
IEnumerable <int> childTypeIds = null;
if (expenseTypeIds?.Any() != null)
{
expenseTypes = context.ExpenseTypes.GetAll().ToList();
childTypeIds = expenseTypes.ToChildTypeIds(expenseTypeIds);
}
var days = b.Day?.Select(x => (int)x).ToList();
return(query.WhereIf(b.From.HasValue, x => x.Date >= b.From)
.WhereIf(b.To.HasValue, x => x.Date <= b.To)
.WhereIf(cardIds, x => x.CardId.HasValue && cardIds.Contains(x.CardId.Value))
.WhereIf(paymentTypeIds, x => x.PaymentTypeId.HasValue && paymentTypeIds.Contains(x.PaymentTypeId.Value))
.WhereIf(expenseTypeIds, x => expenseTypeIds.Contains(x.ExpenseTypeId) || childTypeIds.Contains(x.ExpenseTypeId))
.WhereIf(vendorIds, x => x.VendorId.HasValue && vendorIds.Contains(x.VendorId.Value))
.WhereIf(currencyIds, x => currencyIds.Contains(x.CurrencyId))
.WhereIf(days != null, x => days.Contains(((int)EF.Functions.DateDiffDay((DateTime?)FirstSunday, (DateTime?)x.Date)) % 7))
.WhereIf(b.Month, x => b.Month.Contains(x.Date.Month))
.WhereIf(b.HasLinkedFiles.HasValue, x => !(b.HasLinkedFiles.Value ^ x.ExpenseFiles.Any()))
.WhereIf(b.HasPoi.HasValue, x => !(b.HasPoi.Value ^ x.PoiId.HasValue))
.WhereIf(b.NeedsReview.HasValue, x => !(b.NeedsReview.Value ^ x.NeedsReview))
.WhereIf(!string.IsNullOrWhiteSpace(b.Description), x => x.Comment.Contains(b.Description))
.WhereIf(b.AmountFrom.HasValue, x => x.Amount >= b.AmountFrom)
.WhereIf(b.ExcludeId != null, x => !b.ExcludeId.Contains(x.ValueId))
.WhereIf(b.AmountTo.HasValue, x => x.Amount <= b.AmountTo));
}
public PagedView <View.Expense> Get(ExpenseGetBinding binding) => _expenseHandler.Get(binding);
public IActionResult GetCountByDayOfWekk([FromQuery] ExpenseGetBinding binding) => Ok(_expenseHandler.CountByDayOfWeek(binding));
public IEnumerable <KeyValuePair <string, int> > GetCountByMonthOfYear([FromQuery] ExpenseGetBinding binding) => _expenseHandler.CountByMonthOfYear(binding);
public PagedView <KeyValuePair <Model.View.Vendor.Vendor, int> > CountByVendor(ExpenseGetBinding binding)
{
using (var context = GetMainContext())
{
return(context.Expenses.WhereUser(UserId)
.Where(binding, context)
.Include(x => x.Vendor)
.GroupBy(x => new
{
x.Vendor.ValueId,
x.Vendor.Name
})
.OrderByDescending(x => x.Count())
.Select(x => new KeyValuePair <Model.View.Vendor.Vendor, int>(new() { Id = x.Key.ValueId, Name = x.Key.Name }, x.Count()))
.ToPagedView(binding));
}
}
public static IOrderedQueryable <Expense> OrderBy(this IQueryable <Expense> query, ExpenseGetBinding binding)
{
switch (binding.OrderBy)
{
case ExpenseSort.Date:
return(query.OrderBy(binding.OrderAscending, x => x.Date));
case ExpenseSort.Created:
return(query.OrderBy(binding.OrderAscending, x => x.Created));
case ExpenseSort.Modified:
return(query.OrderBy(binding.OrderAscending, x => x.Modified));
case ExpenseSort.Amount:
return(query.OrderBy(binding.OrderAscending, x => x.Amount));
default:
return(query.OrderBy(binding.OrderAscending, x => x.Date));
}
}
public int GetVendorsCount([FromQuery] ExpenseGetBinding binding) => _expenseHandler.CountVendors(binding);
public async Task <IActionResult> GetTopDescriptions([FromQuery] ExpenseGetBinding binding) => Ok(await _expenseHandler.GetTopDescriptions(binding));
public int GetTypesCount([FromQuery] ExpenseGetBinding binding) => _expenseHandler.CountTypes(binding);
public IActionResult GetCountByVendor([FromQuery] ExpenseGetBinding binding) => Ok(_expenseHandler.CountByVendor(binding));
