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)); }