public async Task <CourseDetailViewModel> GetCourseAsync(int id)
{
/*Per registrare i Log dell'applicazione*/
logger.LogInformation($"Course {id} richiesto");
FormattableString query = $@"
SELECT * FROM Courses WHERE Id={id};
SELECT * FROM Lessons WHERE IdCourse={id}";
DataSet dataSet = await db.QueryAsync(query);
var courseTable = dataSet.Tables[0];
if (courseTable.Rows.Count != 1)
{
logger.LogWarning($"Corso {id} non trovato!");
throw new CourseNotFoundException(id);
}
var courseRow = courseTable.Rows[0];
var courseDetailViewModel = CourseDetailViewModel.FromDataRow(courseRow);
var lessonDataTable = dataSet.Tables[1];
foreach (DataRow lessonRow in lessonDataTable.Rows)
{
LessonViewModel lessonViewModel = LessonViewModel.FromDataRow(lessonRow);
courseDetailViewModel.Lezioni.Add(lessonViewModel);
}
return(courseDetailViewModel);
}
public async Task <CourseDetailViewModel> GetCourseAsync(int id)
{
logger.LogInformation("Course {id} requested", id);
FormattableString query = $@"SELECT Id, Title, Description, ImagePath, Author, Rating, FullPrice_Amount, FullPrice_Currency, CurrentPrice_Amount, CurrentPrice_Currency FROM Courses WHERE Id={id}
; SELECT Id, Title, Description, Duration FROM Lessons WHERE CourseId={id}";
DataSet dataSet = await db.QueryAsync(query);
//Course
var courseTable = dataSet.Tables[0];
if (courseTable.Rows.Count != 1)
{
logger.LogWarning("Course {id} not found", id);
throw new CourseNotFoundException(id);
}
var courseRow = courseTable.Rows[0];
var courseDetailViewModel = CourseDetailViewModel.FromDataRow(courseRow);
//Course lessons
var lessonDataTable = dataSet.Tables[1];
foreach (DataRow lessonRow in lessonDataTable.Rows)
{
LessonViewModel lessonViewModel = LessonViewModel.FromDataRow(lessonRow);
courseDetailViewModel.Lessons.Add(lessonViewModel);
}
return(courseDetailViewModel);
}
public async Task <PastryDetailViewModel> GetPastryAsync(int id)
{
logger.LogInformation("Pastry {id} requested", id);
FormattableString query = $@"SELECT Id, Name, Description, Price, Currency, ImagePath, date(InsertDateTime) as InsertDateTime
FROM Pastries
WHERE Id={id};
SELECT i.Id, i.Name, c.Quantity, c.UoM
FROM Ingredients i
INNER JOIN Compositions c ON i.Id = c.IdI
WHERE c.IdP = {id}";
DataSet dataSet = await db.QueryAsync(query);
var pastryTable = dataSet.Tables[0];
if (pastryTable.Rows.Count != 1)
{
logger.LogWarning("Pastry {id} not found", id);
throw new PastryNotFoundException(id);
}
var pastryRow = pastryTable.Rows[0];
var pastryDetailViewModel = PastryDetailViewModel.FromDataRow(pastryRow);
var ingredientsDataTable = dataSet.Tables[1];
foreach (DataRow ingredientRow in ingredientsDataTable.Rows)
{
IngredientViewModel ingredientViewModel = IngredientViewModel.FromDataRow(ingredientRow);
pastryDetailViewModel.Ingredients.Add(ingredientViewModel);
}
return(pastryDetailViewModel);
}
public async Task <List <CourseViewModel> > GetCoursesAsync()
{
FormattableString query = $"SELECT Id, Title, Author, ImagePath, Rating, FullPrice_Amount, FullPrice_Currency, CurrentPrice_Amount, CurrentPrice_Currency FROM Courses";
DataSet dataSet = await db.QueryAsync(query);
var dataTable = dataSet.Tables[0];
var courseList = mapper.Map <List <CourseViewModel> >(dataTable.Rows);
//var courseList = new List<CourseViewModel>();
// foreach(DataRow courseRow in dataTable.Rows) {
// CourseViewModel course = CourseViewModel.FromDataRow(courseRow);
// courseList.Add(course);
// }
return(courseList);
}
public async Task <ProvinciaViewModel> GetProvinciaAsync(int id)
{
FormattableString query = $"SELECT * FROM province WHERE codiceProvincia={id}";
DataSet dataSet = await db.QueryAsync(query);
var provinciaTable = dataSet.Tables[0];
if (provinciaTable.Rows.Count != 1)
{
throw new InvalidOperationException($"Mi aspettavo che venisse restituita solo una riga della tabella {id}");
}
var provinciaRow = provinciaTable.Rows[0];
var provinciaViewModel = ProvinciaViewModel.FromDataRow(provinciaRow);
return(provinciaViewModel);
}
public async Task <PasswordDetailViewModel> GetPasswordAsync(string id)
{
log.LogInformation("password {id} requested", id);
FormattableString query = $"SELECT * FROM Passwords WHERE Id = {id}";
DataSet dset = await db.QueryAsync(query);
var dtable = dset.Tables[0];
if (dtable.Rows.Count != 1)
{
log.LogWarning("password {id} not found", id);
throw new PasswordNotFoundException(Convert.ToInt32(id));
}
var PassRow = dtable.Rows[0];
PasswordDetailViewModel PassDetailViewModel = PasswordDetailViewModel.FromDataRow(PassRow);
return(PassDetailViewModel);
}
public async Task <LessonDetailViewModel> GetLessonAsync(int id)
{
FormattableString query = $@"SELECT Id, CourseId, Title, Description, Duration FROM Lessons WHERE ID={id}";
DataSet dataSet = await db.QueryAsync(query);
//Course
var lessonTable = dataSet.Tables[0];
if (lessonTable.Rows.Count != 1)
{
logger.LogWarning("Lesson {id} not found", id);
throw new LessonNotFoundException(id);
}
var lessonRow = lessonTable.Rows[0];
var lessonDetailViewModel = LessonDetailViewModel.FromDataRow(lessonRow);
return(lessonDetailViewModel);
}
public async Task <ListViewModel <SpeseViewModel> > GetSpeseAsync(SpeseListInputModel model)
{
string mese = DateTime.Now.ToString("MM");
string anno = DateTime.Now.ToString("yyyy");
string mesePrec1 = DateTime.Now.AddMonths(-1).ToString("MM");
string annoPrec1 = DateTime.Now.AddMonths(-1).ToString("yyyy");
string mesePrec2 = DateTime.Now.AddMonths(-2).ToString("MM");
string annoPrec2 = DateTime.Now.AddMonths(-2).ToString("yyyy");
FormattableString query = $@"SELECT IdSpesa, Descrizione, Importo, Valuta, Mese, Anno FROM Spese WHERE Mese LIKE {mese} AND Anno LIKE {anno} ORDER BY IdSpesa DESC LIMIT {model.Limit} OFFSET {model.Offset};
SELECT COUNT(*) FROM Spese WHERE Mese LIKE {mese} AND Anno LIKE {anno};
SELECT COUNT(*) FROM Spese WHERE Mese LIKE {mesePrec1} AND Anno LIKE {annoPrec1};
SELECT COUNT(*) FROM Spese WHERE Mese LIKE {mesePrec2} AND Anno LIKE {annoPrec2};
SELECT SUM(Importo) FROM Spese WHERE Mese LIKE {mese} AND Anno LIKE {anno};";
DataSet dataSet = await db.QueryAsync(query);
var dataTable = dataSet.Tables[0];
var speseList = new List <SpeseViewModel>();
foreach (DataRow speseRow in dataTable.Rows)
{
SpeseViewModel speseViewModel = SpeseViewModel.FromDataRow(speseRow);
speseList.Add(speseViewModel);
}
ListViewModel <SpeseViewModel> result = new ListViewModel <SpeseViewModel>
{
Results = speseList,
TotalCount = Convert.ToInt32(dataSet.Tables[1].Rows[0][0]),
TotalMese = Convert.ToInt32(dataSet.Tables[1].Rows[0][0]),
Total1MesePrec = Convert.ToInt32(dataSet.Tables[2].Rows[0][0]),
Total2MesePrec = Convert.ToInt32(dataSet.Tables[3].Rows[0][0]),
TotaleSpese = Convert.ToString(dataSet.Tables[4].Rows[0][0])
};
return(result);
}
public async Task <ApplicationUser> FindByIdAsync(string userId, CancellationToken token)
{
DataSet dataSet = await db.QueryAsync($"SELECT * FROM AspNetUsers WHERE Id={userId}", token);
if (dataSet.Tables[0].Rows.Count == 0)
{
return(null);
}
return(ApplicationUser.FromDataRow(dataSet.Tables[0].Rows[0]));
}
public async Task <RegioneViewModel> GetRegioneAsync(int id)
{
FormattableString query = $"SELECT * FROM regioni WHERE codiceRegione={id}";
DataSet dataSet = await db.QueryAsync(query);
var regioneTable = dataSet.Tables[0];
if (regioneTable.Rows.Count != 1)
{
throw new InvalidOperationException($"Mi aspettavo che venisse restituita solo una riga della tabella {id}");
}
var regioneRow = regioneTable.Rows[0];
var regioneViewModel = RegioneViewModel.FromDataRow(regioneRow);
// Questo codice dovrebbe essere interamente sostituito con la funzione GetRegioneAsync -- INIZIO
FormattableString queryCom = $"SELECT * FROM comuni WHERE codiceCatastale={regioneRow["codiceCapoluogo"]}";
DataSet dataSetCom = await db.QueryAsync(queryCom);
var comuneTable = dataSetCom.Tables[0];
if (comuneTable.Rows.Count != 1)
{
throw new InvalidOperationException($"Mi aspettavo che venisse restituita solo una riga della tabella {regioneRow["codiceCapoluogo"]}");
}
var comuneRow = comuneTable.Rows[0];
// Questo codice dovrebbe essere interamente sostituito con la funzione GetRegioneAsync -- FINE
var comuneViewModel = ComuneViewModel.FromDataRow(comuneRow);
regioneViewModel.codiceCapoluogo = (ComuneViewModel)comuneViewModel;
return(regioneViewModel);
}
public async Task <List <CourseViewModel> > GetCoursesAsync()
{
// Otteniamo la tabella risultante, dal interfaccia IDatabaseAccessor che implementa il metodo Query.
FormattableString query = $"SELECT Id, Title, ImagePath, Author, Rating, FullPrice_Amount, CurrentPrice_Amount, FullPrice_Currency, CurrentPrice_Currency FROM COURSES;";
DataSet dataSet = await db.QueryAsync(query);
// Ottiene il registro del corso [0]
var dataTable = dataSet.Tables[0];
// Lista dei corsi dove aggiungerli
var courseList = new List <CourseViewModel>();
// Percorriamo tutte le rows del corso
foreach (DataRow courseRow in dataTable.Rows)
{
// Logica di mapping inserita in FromDataRow(courseRow)
CourseViewModel course = CourseViewModel.FromDataRow(courseRow);
courseList.Add(course);
}
return(courseList);
}
public async Task <CourseDetailViewModel> GetCourseAsync(int id)
{
FormattableString query = $@"SELECT Id, Title, Description, ImagePath, Author, Rating, FullPrice_Amount, FullPrice_Currency, CurrentPrice_Amount, CurrentPrice_Currency FROM Courses WHERE Id={id}
; SELECT Id, Title, Description, Duration FROM Lessons WHERE CourseId={id}";
DataSet dataSet = await db.QueryAsync(query);
//Course
var courseTable = dataSet.Tables[0];
if (courseTable.Rows.Count != 1)
{
throw new InvalidOperationException($"Did not return exactly 1 row for Course {id}");
}
var courseRow = courseTable.Rows[0];
var courseDetailViewModel = mapper.Map <CourseDetailViewModel>(courseRow);
//Course lessons
var lessonDataTable = dataSet.Tables[1];
courseDetailViewModel.Lessons = mapper.Map <List <LessonViewModel> >(lessonDataTable.Rows);
return(courseDetailViewModel);
}
public async Task <ComuneViewModel> GetComuneAsync(string id)
{
FormattableString query = $"SELECT * FROM comuni WHERE codiceCatastale={id}";
DataSet dataSet = await db.QueryAsync(query);
var comuneTable = dataSet.Tables[0];
if (comuneTable.Rows.Count != 1)
{
throw new InvalidOperationException($"Mi aspettavo che venisse restituita solo una riga della tabella {id}");
}
var comuneRow = comuneTable.Rows[0];
var comuneViewModel = ComuneViewModel.FromDataRow(comuneRow);
FormattableString queryReg = $"SELECT * FROM regioni WHERE codiceRegione={comuneRow["codiceRegione"]}";
DataSet dataSetReg = await db.QueryAsync(queryReg);
var regioneTable = dataSetReg.Tables[0];
if (regioneTable.Rows.Count != 1)
{
throw new InvalidOperationException($"Mi aspettavo che venisse restituita solo una riga della tabella {comuneRow["regione"]}");
}
var regioneRow = regioneTable.Rows[0];
var regioneViewModel = RegioneViewModel.FromDataRow(regioneRow);
comuneViewModel.regione = (RegioneViewModel)regioneViewModel;
FormattableString queryPro = $"SELECT * FROM province WHERE codiceProvincia={comuneRow["codiceProvincia"]}";
DataSet dataSetPro = await db.QueryAsync(queryPro);
var provinciaTable = dataSetPro.Tables[0];
if (provinciaTable.Rows.Count != 1)
{
throw new InvalidOperationException($"Mi aspettavo che venisse restituita solo una riga della tabella {comuneRow["provincia"]}");
}
var provinciaRow = provinciaTable.Rows[0];
var provinciaViewModel = ProvinciaViewModel.FromDataRow(provinciaRow);
comuneViewModel.provincia = (ProvinciaViewModel)provinciaViewModel;
return(comuneViewModel);
}