public async Task <IEnumerable <StudentGroup> > Get(StudentGroupGetOptions options)
{
try
{
StringBuilder sql = new StringBuilder();
_logger.LogInformation("Try to create get student groups sql query");
sql.AppendLine(@"
select
distinct
sg.Id,
sg.StudyDirectionId,
sg.Name,
sg.StudentsCount,
sg.StartYear,
sg.CurrentCourse
from StudentGroup sg
");
if (options.DepartmentLoadsIds != null && options.DepartmentLoadsIds.Count > 0)
{
sql.AppendLine($"join GroupDisciplineLoad gdl on gdl.StudentGroupId = sg.Id and gdl.DepartmentLoadId in @DepartmentLoadsIds");
}
if (options.DepartmentId.HasValue)
{
sql.AppendLine("join [StudyDirection] sd on sd.Id = sg.StudyDirectionId and sd.DepartmentId = @DepartmentId");
}
if (options.DepartmentIds != null && options.DepartmentIds.Count > 0)
{
sql.AppendLine($"join [StudyDirection] sd on sd.Id = sg.StudyDirectionId and sd.DepartmentId in @DepartmentIds");
}
int conditionIndex = 0;
if (options.Id.HasValue)
{
sql.AppendLine($"{(conditionIndex++ == 0 ? "where" : "and")} sg.Id = @id");
}
if (options.Ids != null && options.Ids.Count > 0)
{
sql.AppendLine($"{(conditionIndex++ == 0 ? "where" : "and")} sg.Id in @Ids");
}
if (options.Names != null && options.Names.Count > 0)
{
sql.AppendLine($@"{(conditionIndex++ == 0 ? "where" : "and")} sg.Name in @Names");
}
if (!string.IsNullOrEmpty(options.Search))
{
sql.AppendLine($@"{(conditionIndex++ == 0 ? "where" : "and")} lower(sg.Name) like '%lower(@search)%'");
}
_logger.LogInformation($"Sql query successfully created:\n{sql.ToString()}");
_logger.LogInformation("Try to execute sql get student groups query");
var result = await QueryAsync <StudentGroup>(sql.ToString(), options);
_logger.LogInformation("Sql get student groups query successfully executed");
return(result);
}
catch (Exception exception)
{
_logger.LogError(exception.Message);
throw exception;
}
}
public async Task <IEnumerable <StudentGroup> > Get(StudentGroupGetOptions options)
{
return(await _dao.Get(options));
}
