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 async Task <decimal> GetSum(IncomeGetSumBinding binding)
{
using (var context = GetMainContext())
{
int targetCurrencyId = context.GetCurrencyId(binding.TargetCurrencyId, UserId);
var incomeIds = context.Incomes.WhereUser(UserId)
.Where(binding, context)
.Select(x => x.Id)
.ToList();
if (incomeIds.Count == 0)
{
return(0);
}
using (var sql = GetSqlConnection())
{
var parameters = new GetIncomeSumQuery()
{
IncomeIds = incomeIds,
TargetCurrencyId = targetCurrencyId,
UserId = UserId
};
return(Math.Round(await sql.ExecuteScalarAsync <decimal>(SqlLoader.Load(SqlScripts.GetIncomeSum), parameters), 2));
}
}
}
public IEnumerable <KeyValuePair <int, int> > SumVolumeByDayOfWeek(ConsumationGetBinding binding)
{
using (var sqlConnection = GetSqlConnection())
{
return(sqlConnection.Query <KeyValuePair <int, int> >(SqlLoader.Load(SqlScripts.GetConsumationSumByDayOfWeek),
new {
binding.From,
binding.To,
UserId = UserId
})
.Select(x => new KeyValuePair <int, int>(x.Key == 1 ? 6 : x.Key - 2, x.Value))
.OrderBy(x => x.Key));
}
}
public int GetTimeSum(WebTimeGetBinding binding)
{
using (var db = GetSqlConnection())
{
var parameters = new
{
DeviceValueId = binding.DeviceId,
DomainValueId = binding.DomainId,
WebValueId = binding.WebId,
IsSecured = binding.IsSecured,
From = binding.From,
To = binding.To,
UserId = UserId
};
return(db.ExecuteScalar <int>(SqlLoader.Load(SqlScripts.GetWebTimeTotal), parameters));
}
}
public IEnumerable <WebTime> GetTimeSummed(WebTimeGetPagedBinding binding)
{
using (var db = GetSqlConnection())
{
var parameters = new
{
DeviceValueId = binding.DeviceId,
From = binding.From,
Page = binding.Page,
PageSize = binding.PageSize,
To = binding.To,
UserId = UserId
};
var command = new CommandDefinition(SqlLoader.Load(SqlScripts.GetWebTimeSum), parameters);
return(db.Query <WebTime>(command));
}
}
public IEnumerable <TimeByDay> GetTimeTotalByDay(WebTimeGetBinding binding)
{
using (var db = GetSqlConnection())
{
var parameters = new
{
DeviceValueId = binding.DeviceId,
DomainValueId = binding.DomainId,
WebValueId = binding.WebId,
IsSecured = binding.IsSecured,
From = binding.From,
To = binding.To,
UserId = UserId
};
var command = new CommandDefinition(SqlLoader.Load(SqlScripts.GetWebTimeTotalByDay), parameters);
return(db.Query <TimeByDay>(command));
}
}
public IEnumerable <GroupedByMonth <int> > GetTimeTotalByMonth(WebTimeGetBinding binding)
{
using (var db = GetSqlConnection())
{
var parameters = new
{
DeviceValueId = binding.DeviceId,
DomainValueId = binding.DomainId,
WebValueId = binding.WebId,
IsSecured = binding.IsSecured,
From = binding.From,
To = binding.To,
UserId = UserId
};
var command = new CommandDefinition(SqlLoader.Load(SqlScripts.GetWebTimeTotalByMonth), parameters);
return(db.Query <GetWebTimeTotalByMonth>(command)
.Select(x => new GroupedByMonth <int>(x.Seconds, x.Year, x.Month)));
}
}
public IEnumerable <KeyValuePair <int, int> > GetTimeTotalByYear(WebTimeGetBinding binding)
{
using (var db = GetSqlConnection())
{
var parameters = new
{
DeviceValueId = binding.DeviceId,
DomainValueId = binding.DomainId,
WebValueId = binding.WebId,
IsSecured = binding.IsSecured,
From = binding.From,
To = binding.To,
UserId = UserId
};
var command = new CommandDefinition(SqlLoader.Load(SqlScripts.GetWebTimeTotalByYear), parameters);
return(db.Query <GetWebTimeTotalByYear>(command)
.OrderByDescending(x => x.Year)
.Select(x => new KeyValuePair <int, int>(x.Year, x.Seconds)));
}
}
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);
}
}