public async Task <decimal> SumAmount(ExpenseSumGetBinding binding)
{
using (var context = GetMainContext())
{
int targetCurrencyId = context.GetCurrencyId(binding.TargetCurrencyId, UserId);
var expenseIds = context.Expenses.WhereUser(UserId)
.Where(binding, context)
.Select(x => x.Id)
.ToList();
if (!expenseIds.Any())
{
return(0);
}
var query = new GetExpenseSumQuery()
{
ExpenseIds = expenseIds,
TargetCurrencyId = targetCurrencyId,
UserId = UserId
};
return(await SumAmount(query));
}
}
private async Task <decimal> SumAmount(GetExpenseSumQuery query)
{
using (var sql = GetSqlConnection())
{
return(Math.Round(await sql.ExecuteScalarAsync <decimal>(SqlLoader.Load(SqlScripts.GetExpenseSumInDefaultCurrency), query), 2));
}
}
public View.Trip.Trip GetSingle(string valueId)
{
using (var context = GetMainContext())
{
var trip = context.Trips.WhereUser(UserId)
.Include(x => x.Cities)
.ThenInclude(x => x.Country)
.Include(x => x.Files)
.Include($"{nameof(Database.Travel.Trip.Pois)}.{nameof(TripPoi.Poi)}")
.SingleOrDefault(x => x.ValueId == valueId);
var excludedExpenseIds = context.TripExpensesExcluded.Where(x => x.TripId == trip.Id)
.Select(x => x.ExpenseId)
.ToList();
var includedExpenseIds = context.TripExpensesIncluded.Where(x => x.TripId == trip.Id)
.Select(x => x.ExpenseId)
.ToList();
var userExpenses = context.Expenses.WhereUser(UserId);
var expensesWithExcluded = userExpenses.Where(x => trip.TimestampStart.Date <= x.Date && trip.TimestampEnd.Date >= x.Date)
.Where(x => !excludedExpenseIds.Contains(x.Id));
var expensesIncluded = userExpenses.Where(x => includedExpenseIds.Contains(x.Id));
var expenseIds = expensesWithExcluded.Union(expensesIncluded)
.OrderBy(x => x.Date)
.Select(x => x.Id)
.ToList();
var expenses = context.Expenses.Where(x => expenseIds.Contains(x.Id))
.IncludeAll()
.OrderBy(x => x.Date)
.ToList();
decimal totalSpent = 0;
if (expenseIds.Any())
{
using (var db = GetSqlConnection())
{
int targetCurrencyId = context.GetCurrencyId(null, UserId);
string sql = SqlLoader.Load(SqlScripts.GetExpenseSumInDefaultCurrency);
var query = new GetExpenseSumQuery()
{
ExpenseIds = expenseIds,
TargetCurrencyId = targetCurrencyId,
UserId = UserId
};
totalSpent = db.ExecuteScalar <decimal>(sql, query);
}
}
var tripView = new View.Trip.Trip(trip)
{
Expenses = expenses.Select(x => new View.Expense.Expense(x)),
Distance = _trackingHandler.GetDistance(new Model.Binding.FilteredBinding(trip.TimestampStart, trip.TimestampEnd)),
TotalSpent = totalSpent
};
return(tripView);
}
}
