public async Task <Weekplanning> GetForWeekAndYear(int year, int week, int groupId)
{
using (var connection = new SqlConnection(this.connectionString))
{
try
{
await connection.OpenAsync();
var query = @"select id, week, year, Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday
from WeekPlans
where year=@year and week=@week and groupId=groupId";
var result = (await connection.QueryAsync(query, new { year, week, groupId })).Select(x =>
{
var wk = new Weekplanning
{
Id = x.id,
Week = x.week,
Year = x.year,
Days = new List <Day>()
{
new Day("maandag", x.Monday),
new Day("dinsdag", x.Tuesday),
new Day("woensdag", x.Wednesday),
new Day("donderdag", x.Thursday),
new Day("vrijdag", x.Friday),
new Day("zaterdag", x.Saturday),
new Day("zondag", x.Sunday),
}
};
return(wk);
}).FirstOrDefault();
if (result != null)
{
await this.MealRepository.PairMealsToDay(result.Days, groupId);
}
return(result);
}
catch (Exception exc)
{
throw exc;
}
}
}
public void savePlanning(Card card)
{
Weekplanning weekplanning = new Weekplanning {
Year = card.PlanningForWeek.Year, Week = (card.PlanningForWeek.DayOfYear / 7) + 1
};
foreach (DayOfTheWeek day in card.DayOfTheWeek)
{
WeekplanningDay weekplanningDay = new WeekplanningDay();
weekplanningDay.Weekplanning = weekplanning;
weekplanning.WeekplanningDays.Add(weekplanningDay);
Day dayToAdd = new Day {
DayOfTheWeek = day.dayNr
};
weekplanningDay.Day = dayToAdd;
foreach (Dish dish in day.Dishes)
{
DayDish dayDish = new DayDish();
dayDish.Day = dayToAdd;
dayDish.Day.WeekplanningDays.Add(weekplanningDay);
Dish dishToInsert = _context.Dishes.FirstOrDefault(d => d.DishId == dish.DishId);
dayDish.Dish = dishToInsert;
dayToAdd.DayDishes.Add(dayDish);
}
}
_context.Add(weekplanning);
_context.SaveChanges();
}
public async Task <Weekplanning> Save(Weekplanning item, int groupId)
{
using (var connection = new SqlConnection(this.connectionString))
{
await connection.OpenAsync();
if (!item.Id.HasValue)
{
var queryInsert = @"insert into WeekPlans(year, week, groupId) values (@year, @week, @groupId);
select SCOPE_IDENTITY();";
var result = await connection.QueryAsync <int>(queryInsert, new { year = item.Year, week = item.Week, groupId });
if (result.Any())
{
item.Id = result.FirstOrDefault();
}
}
var queryUpdate = $"update WeekPlans set Sunday=@meal1, Monday=@meal2, Tuesday=@meal3, Wednesday=@meal4,Thursday=@meal5,Friday=@meal6, Saturday=@meal7 " +
$"where year=@year and week=@week and groupId = @groupId";
await connection.ExecuteAsync(queryUpdate, new
{
year = item.Year,
week = item.Week,
meal1 = item.Days[6].Meal?.Id,
meal2 = item.Days[0].Meal?.Id,
meal3 = item.Days[1].Meal?.Id,
meal4 = item.Days[2].Meal?.Id,
meal5 = item.Days[3].Meal?.Id,
meal6 = item.Days[4].Meal?.Id,
meal7 = item.Days[5].Meal?.Id,
groupId
});
}
return(item);
}
public async Task <IActionResult> Save([FromBody] Weekplanning plan)
{
var item = await this.weekplanning.Save(plan, this.GroupId().Value);
return(Ok(item));
}
