public async Task <PageResultModel <CourseModel> > GetList(CourseFilter filter)
{
var query = _db.Courses.AsQueryable();
if (filter.Status != null)
{
query = query.Where(x => x.Status == filter.Status);
}
if (filter.Level != null)
{
query = query.Where(x => x.Level == filter.Level);
}
query = query.OrderByDescending(x => x.Id)
.Skip((filter.Page - 1) * filter.Size)
.Take(filter.Size);
var count = await query.CountAsync();
var list = await MapQuery(query).ToListAsync();
return(new PageResultModel <CourseModel>
{
Size = filter.Size,
Page = filter.Page,
TotalCount = count,
List = list
});
}
/// <summary>
/// filters Courses for a certain status (planned, active, completed)
/// </summary>
/// <param name="courses"></param>
/// <param name="filter"></param>
/// <returns>a list of Courses</returns>
public List <Course> FilterStatus(List <Course> courses, CourseFilter filter)
{
if (filter.status != null && filter.status.Count > 0)
{
if (!filter.status.Contains("active"))
{
var courseRemoveActive = courses.Where(x => x.Start <DateTime.Now && x.End> DateTime.Now).ToList();
foreach (var course in courseRemoveActive)
{
courses.Remove(course);
}
}
if (!filter.status.Contains("planned"))
{
var courseRemovePlanned = courses.Where(x => x.Start > DateTime.Now).ToList();
foreach (var course in courseRemovePlanned)
{
courses.Remove(course);
}
}
if (!filter.status.Contains("completed"))
{
var courseRemoveCompleted = courses.Where(x => x.Start < DateTime.Now && x.End < DateTime.Now).ToList();
foreach (var course in courseRemoveCompleted)
{
courses.Remove(course);
}
}
}
return(courses);
}
private IReadOnlyCollection <DataSetValuePair> UpdateDatasetSource()
{
var sources = new List <DataSetValuePair>();
var sessions = ViewModelLocatorStatic.Locator.SessionModule.Sessions.Select(c => c.Model);
if (CodeFilter != null)
{
var sessionsfiltered =
sessions.Where(
c => c.SessionId.ToLowerInvariant().Contains(CodeFilter.ToLowerInvariant()));
sessions = sessionsfiltered;
}
if (CourseFilter != null)
{
var cf = CourseFilter.ToLowerInvariant();
var sessionsfiltered =
sessions.Where(
c => c.Course.CourseId.ToLowerInvariant().Contains(cf) || c.Course.CourseName.ToLowerInvariant().Contains(cf));
sessions = sessionsfiltered;
}
sources.Add(new DataSetValuePair("SessionDataset", sessions));
return(sources);
}
/// <summary>
/// filters Courses for a certain course category (e.g. Coding Campus)
/// </summary>
/// <param name="courses"></param>
/// <param name="filter"></param>
/// <returns>a list of Courses</returns>
public List <Course> FilterCategory(List <Course> courses, CourseFilter filter)
{
if (filter.category != null && filter.category.Length > 0)
{
courses = courses.Where(x => x.Category == filter.category).ToList();
}
return(courses);
}
public CourseFilter ToCourseFilter()
{
var filter = new CourseFilter();
filter.Code = Code;
filter.Name = Keyword;
return(filter);
}
public async Task <IEnumerable <CourseModel> > GetAllCoursesAsync(CourseFilter courseFilter)
{
courseFilter.CurrentPage = (courseFilter.CurrentPage - 1) * courseFilter.PageSize;
if (courseFilter.CurrentPage <= 0)
{
courseFilter.CurrentPage = 0;
}
return(await(_courseRepository.GetAllCoursesAsync(courseFilter)));
}
/// <summary>
/// filters Courses for a certain Content
/// </summary>
/// <param name="courses"></param>
/// <param name="filter"></param>
/// <returns>a list of Courses</returns>
public List <Course> FilterContent(List <Course> courses, CourseFilter filter)
{
if (filter.content_id != null && filter.content_id != 0)
{
// get all course-content relations where a certain content exists
var relations = entities.RelCourseContents.Where(x => x.ContentId == filter.content_id).ToList();
// filter courses for existing course-content relations
courses = courses.Where(x => relations.Any(z => x.Id == z.CourseId)).ToList();
}
return(courses);
}
/// <summary>
/// filters Courses for assignment to a certain trainer
/// </summary>
/// <param name="courses"></param>
/// <param name="filter"></param>
/// <returns>a list of Courses</returns>
public List <Course> FilterTrainer(List <Course> courses, CourseFilter filter)
{
if (filter.trainer_id != null && filter.trainer_id != 0)
{
// get all course-trainer relations where a certain trainer exists
var relations = entities.RelCourseTrainers.Where(x => x.TrainerId == filter.trainer_id).ToList();
// filter courses for existing course-trainer relations
courses = courses.Where(x => relations.Any(z => x.Id == z.CourseId)).ToList();
}
return(courses);
}
public IActionResult Index()
{
var currentUserId = HttpContext.Session.GetInt32("_Id").Value;
Services.Courses.DTOs.CourseFilter filter = new CourseFilter()
{
UserId = currentUserId
};
var result = _courseService.GetListDTOPaged(1, 50, null, filter).ToViewModel();
return(View(result));
}
public async Task <IActionResult> GetCoursesFiltered([FromBody] CourseFilter courseFilter)
{
if (courseFilter != null && ModelState.IsValid)
{
var courses = await _courseManager.GetAllCoursesAsync(courseFilter);
if (courses.Count() > 0)
{
return(Ok(courses));
}
return(NoContent());
}
return(BadRequest("Invalid Parameters"));
}
public PagedResult <Course> GetList(CourseFilter filter, Paging paging)
{
const string func = "GetList";
_logger.LogTrace("{}: Entering {}.", func, func);
try
{
return(_courseRepo.GetList(filter, paging));
}
catch (Exception ex)
{
_logger.LogInformation("{}: Exception caught.", func, ex);
throw ex;
}
}
public List <JSONCourseSend> Get([FromBody] CourseFilter filter)
{
List <JSONCourseSend> returnList = null;
try
{
returnList = courseController.GetFilteredCourses(filter);
Response.StatusCode = 200;
}
catch (Exception ex)
{
Response.StatusCode = 500;
Response.WriteAsync(ex.Message);
}
return(returnList);
}
public void LoadCourses()
{
try
{
var bl = Service.Get <ICourseBusinessLogic>();
var filter = new CourseFilter();
var griddata = bl.GetCourses(filter);
View.SetGridData(griddata);
}
catch (Exception err)
{
View.ShowError(err);
}
}
public IHttpActionResult GetCourses([FromUri] CourseFilter courseFilter)
{
var queryable = Course.FilterCourses(db.Courses, courseFilter);
if (courseFilter.Favourites)
{
int userId = JwtAuthManager.GetUserIdFromRequest(Request);
var favouriteIds = db.Users.Find(userId).Favorites.Select(course => course.Id).ToList();
queryable = queryable.Where(c => favouriteIds.Contains(c.Id));
}
var coursesPage = Pagination.CreateMappedPage <Course, CourseCard>(
queryable, courseFilter.Page, courseFilter.PageSize, "Title", true
);
return(Ok(coursesPage));
}
/// <summary>
/// filters Courses for four filter options
/// </summary>
/// <param name="filter"></param>
/// <returns>a list of JSONCourseSends</returns>
public List <JSONCourseSend> GetFilteredCourses(CourseFilter filter)
{
var courses = GetAll();
courses = FilterStatus(courses, filter);
courses = FilterTrainer(courses, filter);
courses = FilterCategory(courses, filter);
courses = FilterSearch(courses, filter);
courses = FilterContent(courses, filter);
var jsonCourses = new List <JSONCourseSend>();
foreach (var course in courses)
{
jsonCourses.Add(jsonConverter.ConvertCourseToJSON(course));
}
return(jsonCourses);
}
public IActionResult GetCoursesForPagination(PaginationData data)
{
int page = data.RequestPage ?? 0;
_pageSize = data.PageLimit ?? 4;
var itemsToSkip =
page == 0 ? 0 : (page - 1) * _pageSize;
var container = new ApiContainer();
var filter = new CourseFilter()
{
Category = data.Category,
UserSearchInput = data.UserSearchInput
};
var courses = _data.GetCourses(filter).OrderBy(t => t.Id).Skip(itemsToSkip).Take(_pageSize);
if (!courses.Any())
{
return(NotFound());
}
var dataRez = new List <CourseDto>();
var model = courses.Select(c => _mapper.Map <Course, CourseDto>(c));
foreach (var course in courses)
{
var dtoModel = _mapper.Map <Course, CourseDto>(course);
dtoModel.StudentCount = course.Users.Count();
dtoModel.UserRaiting = 5;
dtoModel.Author =
string.IsNullOrEmpty(course.Author.Name) || string.IsNullOrEmpty(course.Author.Surname)
? course.Author.UserName
: $"{course.Author.Name} {course.Author.Surname}";
dataRez.Add(dtoModel);
}
container.Data = dataRez;
container.CurrentPage = page;
container.TotalRecord = _data.GetCourses(filter).Count();
container.PageLimit = _pageSize;
container.Fetched = courses.Count();
return(Ok(container));
}
private List <KeyValuePair <string, object> > GetFilterParam(CourseFilter filter)
{
List <KeyValuePair <string, object> > parameters = new List <KeyValuePair <string, object> >();
if (filter == null)
{
return(parameters);
}
if (filter.Id.HasValue)
{
parameters.Add(new KeyValuePair <string, object>("@Id", filter.Id));
}
if (filter.Name != null && filter.Name != "")
{
parameters.Add(new KeyValuePair <string, object>("@Name", filter.Name));
}
return(parameters);
}
public IEnumerable <ListCourseGridViewModel> GetCourses(CourseFilter filter)
{
IEnumerable <ListCourseGridViewModel> result = null;
using (var ctx = new ModelContext())
{
var query = ctx.Courses.AsQueryable();
result = query
.Select(x => new ListCourseGridViewModel
{
Id = x.Id,
Title = x.course_title,
Level = x.course_level
})
.ToArray();
}
return(result);
}
public async Task <CourseVO[]> BbCourses(CourseFilter filter)
{
_tryCount += 1;
if (_tryCount == 3)
{
await ResetConnection();
_crseClient = null;
}
if (_crseClient == null)
{
_crseClient = await GetCourseClient();
}
var crseVo = await _crseClient.getCourseAsync(filter);
_tryCount = 0;
return(crseVo.@return);
}
public IEnumerable <Course> GetCourses(CourseFilter filter)
{
var query = _context.Courses.AsQueryable();
if (filter.Category != null)
{
query = _context.CoursesToCategories
.Where(c => c.Category.Name == filter.Category)
.Select(c => c.Course);
}
if (filter.UserSearchInput != null)
{
var t = filter.UserSearchInput.ToLower();
query = query.Where(c => c.Name.ToLower().Contains(t));
}
return(query.Include(u => u.Users).Include(u => u.Author).ToList());
}
public IEnumerable <Course> Select(CourseFilter filter)
{
string procedureName = "Course_Select";
using (SqlConnection connection = new SqlConnection(_connectionString))
{
connection.Open();
SqlCommand command = new SqlCommand(procedureName, connection)
{
CommandType = System.Data.CommandType.StoredProcedure
};
var paramList = GetFilterParam(filter);
foreach (var param in paramList)
{
SqlParameter Param = new SqlParameter
{
ParameterName = param.Key,
Value = param.Value
};
command.Parameters.Add(Param);
}
var result = command.ExecuteReader();
List <Course> resultList = new List <Course>();
if (result.HasRows)
{
while (result.Read())
{
Course entity = new Course
{
Id = result.GetInt32(0),
Name = result.GetString(1)
};
resultList.Add(entity);
}
}
result.Close();
return(resultList);
}
}
private async Task FilterCourses(CourseFilter filter, IEnumerable<Course> unfilteredCourses)
{
Courses = new ObservableCollection<Course>();
Courses = await Task<ObservableCollection<Course>>.Run(() =>
{
IEnumerable<Course> filteredCourses = null;
switch (filter)
{
case CourseFilter.Code:
filteredCourses = unfilteredCourses.OrderBy(course => course.Id);
break;
case CourseFilter.Name:
filteredCourses = unfilteredCourses.OrderBy(course => course.Name);
break;
case CourseFilter.Department:
filteredCourses = unfilteredCourses.OrderBy(course => course.DepartmentId);
break;
}
return new ObservableCollection<Course>(filteredCourses);
});
}
/// <summary>
/// filters Course titles for an entry in the search field (at frontend)
/// </summary>
/// <param name="courses"></param>
/// <param name="filter"></param>
/// <returns>a list of Courses</returns>
public List <Course> FilterSearch(List <Course> courses, CourseFilter filter)
{
// not the best looking code but working...
if (filter.search != null && filter.search.Length > 0)
{
var filteredCourses = new List <Course>();
foreach (var course in courses)
{
var title = course.Title.ToLower();
var search = filter.search.ToLower();
if (title.Contains(search))
{
filteredCourses.Add(course);
}
}
return(filteredCourses);
}
else
{
return(courses);
}
}
public PagedResult <Course> GetList(CourseFilter filter, Paging paging)
{
const string func = "GetList";
_logger.LogTrace("{}: Entering {}.", func, func);
try
{
var courses = from c in _context.Course
select c;
if (!string.IsNullOrEmpty(filter.Code))
{
courses = courses.Where(c => c.Code.ToLower().Contains(filter.Code.ToLower()));
}
if (!string.IsNullOrEmpty(filter.Name))
{
courses = courses.Where(c => c.Name.ToLower().Contains(filter.Name.ToLower()));
}
// Not paging
if (paging == null)
{
paging.CurrentPage = 0;
paging.PageSize = int.MaxValue;
}
var result = courses.GetPaged(paging.CurrentPage, paging.PageSize);
return(result);
}
catch (Exception ex)
{
_logger.LogInformation("{}: Exception caught.", func, ex);
throw ex;
}
}
public List <CourseVO> LoadCourses(CourseFilter pFilter, int pId = -1, int[] pIds = null)
{
List <CourseVO> result = null;
try
{
AuthenticationService.CheckIfUSerIsAuthenticated();
LogService.Log("CourseService", "LoadCourses", String.Format("Filter:{0}; ID:{1}; IDs: {2}", pFilter, pId, pIds));
CourseTableAdapter adapter = new CourseTableAdapter();
ONyRDataSet.CourseDataTable table;
result = new List <CourseVO>();
if (pFilter == CourseFilter.All)
{
table = adapter.GetData();
}
else if (pFilter == CourseFilter.ById)
{
table = adapter.GetDataByID(pId);
}
else if (pFilter == CourseFilter.ByIds)
{
table = adapter.GetData();
}
else
{
throw new ONyRException(ErrorCode.UnknownError);
}
for (int i = 0; i < table.Count; ++i)
{
if (pFilter != CourseFilter.ByIds || pIds.Contains(table[i].ID))
{
// TODO: Fill properties here
// TODO: try-catch block for every Nullable field!
result.Add(new CourseVO(table[i].ID, table[i].Name));
}
}
adapter.Dispose();
}
catch (ONyRException e)
{
LogService.LogError(e.ErrorCode, e.StackTrace);
throw new FaultException <ONyRFaultException>(new ONyRFaultException((int)e.ErrorCode));
}
catch (SqlException ex)
{
string s = "Class: " + ex.Class + "\n";
for (int i = 0; i < ex.Errors.Count; i++)
{
s += "Index #" + i + "\n" +
"Message: " + ex.Errors[i].Message + "\n" +
"LineNumber: " + ex.Errors[i].LineNumber + "\n" +
"Source: " + ex.Errors[i].Source + "\n" +
"Procedure: " + ex.Errors[i].Procedure + "\n" +
"Class: " + ex.Errors[i].Class + "\n";
}
LogService.LogError(ErrorCode.UnknownError, null, s);
throw new FaultException <ONyRFaultException>(new ONyRFaultException((int)ErrorCode.DatabaseError));
}
catch (Exception e)
{
LogService.LogError(ErrorCode.UnknownError, e.StackTrace, e.Message);
throw new FaultException <ONyRFaultException>(new ONyRFaultException((int)ErrorCode.UnknownError));
}
return(result);
}
public async Task <CourseReconResult> ReconcileCourses()
{
//await GetProfile();
var courseFilter = new CourseFilter
{
filterTypeSpecified = true,
filterType = (int)CourseFilterType.LoadByCatId
};
if (Faculty != null)
{
var academy = StaticAcademy.AcadLookupById[Faculty.AcademyId];
courseFilter.categoryIds = new[] { academy.BbCategoryId };
}
else
{
var ids = StaticAcademy.AcadLookupById.Select(acad => acad.Value.BbCategoryId).ToArray();
courseFilter.categoryIds = ids;
}
var autoRetry = new Retrier <CourseVO[]>();
var bbCoursesResult = await autoRetry.Try(() => _bbWs.BbCourses(courseFilter), 3);
if (bbCoursesResult == null)
{
throw new InvalidDataException("No Bb Responses received");
}
var queryKnownCourses = ctxManager.Context.Courses.AsQueryable();
queryKnownCourses = Faculty == null
? queryKnownCourses
: queryKnownCourses.Where(crse => crse.AcademyId == Faculty.AcademyId);
var knownCoursesIds = queryKnownCourses.Select(crse => crse.BbCourseId).ToList();
var reconResult = new CourseReconResult
{
Id = Guid.NewGuid(),
AcademyId = Faculty?.AcademyId,
Courses = new List <Course>()
};
foreach (var nc in bbCoursesResult
.Where(bbc => !knownCoursesIds.Contains(bbc.id))
.Select(bbc => new Course
{
BbCourseId = bbc.id,
AcademyId = Faculty.AcademyId,
Name = bbc.name,
StartDate = DateTime.Now,
GradDate = DateTime.Now.AddDays(25)
}))
{
reconResult.NumAdded += 1;
reconResult.Courses.Add(nc);
ctxManager.Context.Courses.Add(nc);
}
await ctxManager.Context.SaveChangesAsync();
foreach (var course in reconResult.Courses)
{
course.ReconResultId = reconResult.Id;
}
return(reconResult);
}
public async Task <IEnumerable <CourseModel> > GetAllCoursesAsync(CourseFilter courseFilter)
{
try
{
StringBuilder dynamicQuery = new StringBuilder();
dynamicQuery.Append(SqlQueries.GetAllCourses);
if (courseFilter.Keyword != null && courseFilter.Keyword.Length > 0)
{
StringBuilder subKeywordSearchConditions = new StringBuilder();
string keyword = $"'%{courseFilter.Keyword.Trim()}%'";
subKeywordSearchConditions.Append($"[C].[BatchType] LIKE {keyword} OR");
subKeywordSearchConditions.Append($"[C].[CourseType] LIKE {keyword} OR");
subKeywordSearchConditions.Append($"[C].[Academy] LIKE {keyword} OR");
subKeywordSearchConditions.Append($"[C].[Name] LIKE {keyword} OR");
subKeywordSearchConditions.Append($"[C].[YorbitCourseId] LIKE {keyword}");
dynamicQuery.Append(" ");
dynamicQuery.Append($"AND ({subKeywordSearchConditions.ToString()})");
}
string order;
switch (courseFilter.Sort)
{
case E_COURSE_SORT.ACADEMY_NAME_ASC:
order = "[C].[Academy] ASC";
break;
case E_COURSE_SORT.ACADEMY_NAME_DESC:
order = "[C].[Academy] DESC";
break;
case E_COURSE_SORT.COURSE_NAME_DESC:
order = "[C].[Name] DESC";
break;
case E_COURSE_SORT.BATCH_TYPE_ASC:
order = "[C].[BatchType] ASC";
break;
case E_COURSE_SORT.BATCH_TYPE_DESC:
order = "[C].[BatchType] DESC";
break;
case E_COURSE_SORT.COURSE_TYPE_ASC:
order = "[C].[CourseType] ASC";
break;
case E_COURSE_SORT.COURSE_TYPE_DESC:
order = "[C].[CourseType] DESC";
break;
case E_COURSE_SORT.YORBIT_COURSE_ID_ASC:
order = "[C].[YorbitCourseId] ASC";
break;
case E_COURSE_SORT.YORBIT_COURSE_ID_DESC:
order = "[C].[YorbitCourseId] DESC";
break;
default:
order = "[C].[Name] ASC";
break;
}
order = "ORDER BY " + order;
dynamicQuery.Append(" ");
dynamicQuery.Append(order);
//Pagination
dynamicQuery.Append($" offset {courseFilter.CurrentPage} rows fetch next {courseFilter.PageSize} rows only;");
return(await _dataRepository.QueryAsync <CourseModel>(dynamicQuery.ToString()));
}
catch (Exception ex)
{
return(null);
}
}
public IEnumerable <Course> GetCoursesWithAuthor(CourseFilter filter)
{
return(_context.Courses.Include(c => c.Author));
}
public LoadCoursesNotifier(CourseFilter pFilter, int pId = -1, int[] pIds = null)
{
mFilter = pFilter;
mId = pId;
mIds = pIds;
}
static String[,] getCourseList(String courseuser)
{
// Create a two dimensional array to house the course and membership data
String[,] courseList = new String[10000, 4];
// Add headers as the first element of the array
courseList[0, 0] = "COURSE ID";
courseList[0, 1] = "COURSE NAME";
courseList[0, 2] = "MEMBERSHIP ID";
courseList[0, 3] = "COURSE ROLE";
if (DEBUG)
{
Console.WriteLine(courseList[0, 0] + "," + courseList[0, 1] + "," + courseList[0, 2] + "," + courseList[0, 3]);
}
// Get the list of courseIds associated with the user requested.
CourseIdVO [] courses = ctx.getMemberships(courseuser);
if (courses == null)
{
return(courseList);
}
// Initialize a user filter, set to get user by username and availability.
UserFilter uf = new UserFilter();
uf.filterType = 6;
uf.filterTypeSpecified = true;
uf.name = new string[] { courseuser };
// Get the user object
UserVO [] user = usr.getUser(uf);
if (user == null)
{
return(courseList);
}
int i = 1;
// Iterate through the course list returned from contextWS.getMemberships()
foreach (CourseIdVO courseIdVO in courses)
{
try
{
// Grab the course Id
String courseId = courseIdVO.externalId;
// Initialize a new Course Filter and Membership Filter
CourseFilter cf = new CourseFilter();
MembershipFilter mf = new MembershipFilter();
// Set course filter to search for courses by user Id. Yes, this is redundant, however
// ContextWS.getMemberships returns both courses and organizations. This only retrieves courses
cf.filterType = 3;
cf.filterTypeSpecified = true;
cf.ids = new string[] { courseId };
// Set the membership filter to get memberships by course Id and UserId.
// Getting by user Id only (filter type 5) always fails. The course Id will be added
// during the foreach loop below.
mf.filterType = 6;
mf.filterTypeSpecified = true;
mf.userIds = new string[] { user[0].id };
// Load courses according to the course filter above
CourseVO[] course = crs.loadCourses(cf);
// if course is null, either the call failed, or the courseId actually refers to an organization.
// Check for course == null so we don't throw an exception when attempting to access the array.
if (course == null || course[0] == null)
{
continue;
}
else
{
// Add the current course Id to the membership filter
mf.courseIds = new string[] { courseId };
// Get Course memberships for the given user and course, as specified in the membership filter
CourseMembershipVO[] memberships = crm.loadCourseMembership(courseId, mf);
// If memberships were returned...
if (memberships != null)
{
// add the course id, the course name, the membership pk1, and the role id
courseList[i, 0] = course[0].courseId;
courseList[i, 1] = course[0].name;
courseList[i, 2] = memberships[0].id;
courseList[i, 3] = memberships[0].roleId;
if (DEBUG)
{
Console.WriteLine(i + ": " + courseList[i, 0] + "," + courseList[i, 1] + "," + courseList[i, 2] + "," + courseList[i, 3]);
}
i++;
}
}
}
catch (Exception e)
{
// This error handling is not very useful, but essentially it is likely failing because of a
// NullReferenceException, so the current courselist is returned.
return(courseList);
}
}
// Finished processing, return courseLIst.
return(courseList);
}
