public Application GetApplication(ApplicationQuery query)
{
query.Count = 1;
var q = BuildGetApplicationsQuery(query);
var res = Read(q, r => { r.Read();
return(ReadApplication(r)); });
LoadApplicationData(res);
return(res);
}
public PaginatedList <Application> GetPaginatedApplications(ApplicationQuery query)
{
var q = BuildGetApplicationsQuery(query);
q = Orm.PaginationSelect(q, query.OrderBy, query.OrderDesc ? Orm.OrderType.Desc : Orm.OrderType.Asc, query.Start, query.Count);
var paginatedApps = ReadPaginatedResult(q, query.Start, query.Count, r =>
{
var res = new List <Application>();
while (r.Read())
{
res.Add(ReadApplication(r));
}
return(res);
});
return(PreparePicturesData(paginatedApps) as PaginatedList <Application>);
}
private DbQuery BuildSelectApplicationQuery(ApplicationQuery query)
{
var q = new DbQuery();
var banColumn = $@"Case When @schoolId is not null
Then IsNull({nameof(ApplicationSchoolOption.Banned)}, 0) Else null End";
var mainSelect = $@"Select
[{nameof(Application)}].*,
({banColumn}) As Ban
From
[{nameof(Application)}]
left join {nameof(ApplicationSchoolOption)}
On [{nameof(Application.Id)}] = {nameof(ApplicationSchoolOption.ApplicationRef)}
And {nameof(ApplicationSchoolOption.SchoolRef)} = @schoolId" ;
q.Sql.Append(mainSelect);
q.Parameters.Add("schoolId", query.SchoolId);
return(q);
}
private DbQuery BuildGetApplicationsQuery(ApplicationQuery query)
{
var res = BuildSelectApplicationQuery(query);
AddSimpleParamsToApplicationQuery(res, query);
//Adding complex params to query
if (query.SchoolId.HasValue && query.Ban.HasValue)
{
res.Sql.Append($" And IsNull({nameof(ApplicationSchoolOption.Banned)},0) = @banned");
res.Parameters.Add("banned", query.Ban);
}
if (query.GradeLevels != null && query.GradeLevels.Count > 0)
{
var gradeLevelsPredicate = $@"Exists(
Select *
From {nameof(ApplicationGradeLevel)}
Where
{nameof(ApplicationGradeLevel.ApplicationRef)} = {nameof(Application)}.Id
And {nameof(ApplicationGradeLevel.GradeLevel)} in (Select * From @gradeLevels))";
res.Sql.Append($" And {gradeLevelsPredicate}");
res.Parameters.Add("gradeLevels", query.GradeLevels);
}
if (query.CategoryIds != null && query.CategoryIds.Count > 0)
{
var categoryPredicate = $@"Exists(
Select *
From {nameof(ApplicationCategory)}
Where
{nameof(ApplicationCategory.ApplicationRef)} = Application.Id
And {nameof(ApplicationCategory.CategoryRef)} in (Select * from @categoryIds))";
res.Sql.Append($"And {categoryPredicate}");
res.Parameters.Add("categoryIds", query.CategoryIds);
}
if (string.IsNullOrEmpty(query.Filter))
{
return(res);
}
var keyWords = query.Filter.Split(new [] { ' ', '.', ',', ';' }, StringSplitOptions.RemoveEmptyEntries);
var keyWordsParams = new List <string>();
for (var i = 0; i < keyWords.Length; ++i)
{
if (string.IsNullOrEmpty(keyWords[i]))
{
continue;
}
var keyWordParam = "@keyWord_" + i;
res.Parameters.Add(keyWordParam, $"%{keyWords[i]}%");
keyWordsParams.Add(" like " + keyWordParam);
}
if (keyWordsParams.Count <= 0)
{
return(res);
}
var appCategory = $@"Select {nameof(ApplicationCategory.Id)}
From {nameof(ApplicationCategory)} join {nameof(Category)}
On {nameof(ApplicationCategory)}.CategoryRef = {nameof(Category)}.Id
Where {nameof(ApplicationCategory.ApplicationRef)} = [Application].Id
And (Lower({nameof(Category)}.Name) {keyWordsParams.JoinString(" or LOWER(Category.Name) ")} )";
var devQuery = $@"Select * From {nameof(Developer)}
Where {nameof(Developer.Id)} = {nameof(Application.DeveloperRef)}
And ( Lower({nameof(Developer.Name)}) {keyWordsParams.JoinString(" or Lower(Name) ")}
or Lower({nameof(Developer.WebSite)}) {keyWordsParams.JoinString(" or Lower(WebSite) ")} )";
var searchQuery =
$@" And ( Lower({nameof(Application.Name)}) {keyWordsParams.JoinString(" or Lower (Name) ")}
or Lower({nameof(Application.Description)}) {keyWordsParams.JoinString(" or Lower (Description) ")}
or Exists({appCategory})
or Exists({devQuery}) )";
res.Sql.Append(searchQuery);
return(res);
}
private void AddSimpleParamsToApplicationQuery(DbQuery result, ApplicationQuery query)
{
var conditions = new AndQueryCondition();
if (query.Role != CoreRoles.SUPER_ADMIN_ROLE.Id && query.Role != CoreRoles.APP_TESTER_ROLE.Id)
{
if (!query.IncludeInternal)
{
conditions.Add(nameof(Application.IsInternal), 1, ConditionRelation.NotEqual);
}
if (query.MyApps.HasValue)
{
if (query.Role == CoreRoles.STUDENT_ROLE.Id)
{
conditions.Add(nameof(Application.HasStudentMyApps), query.MyApps, ConditionRelation.Equal);
}
if (query.Role == CoreRoles.TEACHER_ROLE.Id)
{
conditions.Add(nameof(Application.HasTeacherMyApps), query.MyApps, ConditionRelation.Equal);
}
if (query.Role == CoreRoles.DISTRICT_ADMIN_ROLE.Id)
{
conditions.Add(nameof(Application.HasAdminMyApps), query.MyApps, ConditionRelation.Equal);
}
}
}
if (query.CanAttach.HasValue)
{
conditions.Add(nameof(Application.CanAttach), query.CanAttach, ConditionRelation.Equal);
}
if (query.DeveloperId.HasValue)
{
conditions.Add(nameof(Application.DeveloperRef), query.DeveloperId, ConditionRelation.Equal);
}
if (query.Id.HasValue)
{
conditions.Add(nameof(Application.Id), query.Id, ConditionRelation.Equal);
}
if (query.Live.HasValue || query.State.HasValue)
{
var relation = ConditionRelation.Equal;
var state = (int?)query.State;
if (query.Live.HasValue)
{
if (!query.Live.Value)
{
relation = ConditionRelation.NotEqual;
}
state = (int?)ApplicationStateEnum.Live;
}
conditions.Add(nameof(Application.State), state, relation);
}
conditions.BuildSqlWhere(result, nameof(Application));
}
