/// <summary>
/// Gets a list of courses matching the search criteria in CourseListRequestStructure
/// and logs the duration of the search.
/// </summary>
/// <param name="courseListInput">Search criteria.</param>
/// <returns>Populated CourseLisOutput containing matching courses.</returns>
public CourseListOutput GetCourseList(CourseListInput courseListInput)
{
CourseListOutput courseListOutput = new CourseListOutput(new CourseListResponseStructure());
CourseListRequest request = BuildCourseListRequest(courseListInput.CourseListRequest);
CourseListResponse response = _courseQuery.GetCourseList(request);
courseListOutput.CourseListResponse =
BuildCourseListResponseStructure(response, courseListInput.CourseListRequest);
// Record search time
_courseQuery.RecordSearchTime(Constants.SEARCH_TIME_COLUMN_FLAG, response.SearchHeaderId);
return(courseListOutput);
}
/// <summary>
/// Get matching courses based on request
/// </summary>
/// <param name="request">search parameters</param>
/// <returns>Matching course reponse object</returns>
public CourseListResponse GetCourseList(CourseListRequest request)
{
try
{
Boolean isPublicAPI = ConfigurationManager.AppSettings["IncludeUCASData"].ToLower() != "true";
Boolean v3SearchEnhancements = ConfigurationManager.AppSettings["UseV3SearchEnhancements"].ToLower() == "true";
Int32 cutoffPercentage;
Int32.TryParse(ConfigurationManager.AppSettings["RemoveLowestRankedResultPercentage"], out cutoffPercentage);
Boolean providerFreeTextMatch = ConfigurationManager.AppSettings["ProviderFreeTextMatch"].ToLower() == "true";
Boolean courseFreeTextMatch = ConfigurationManager.AppSettings["CourseFreeTextMatch"].ToLower() == "true";
Boolean searchCourseSummary = ConfigurationManager.AppSettings["SearchCourseSummary"].ToLower() == "true";
Boolean searchQualificationTitle = ConfigurationManager.AppSettings["SearchQualificationTitle"].ToLower() == "true";
if (isPublicAPI && String.IsNullOrEmpty(request.APIKey.Trim()))
{
return(new CourseListResponse());
}
Stopwatch stopwatch = Stopwatch.StartNew();
string courseCacheKey = CreateCacheKey(request, v3SearchEnhancements, providerFreeTextMatch, courseFreeTextMatch, searchCourseSummary, searchQualificationTitle);
DateTime earlierStartDate;
DateTime.TryParse(request.EarliestStartDate, out earlierStartDate);
DateTime applyUntil;
DateTime.TryParse(request.AppClosedFlag, out applyUntil);
CourseListResponse courseResult = CacheHelper.GetCourseListResponse(courseCacheKey);
if (courseResult == null)
{
courseResult = LoadGetCourseList(request, earlierStartDate, applyUntil, isPublicAPI, v3SearchEnhancements, cutoffPercentage, providerFreeTextMatch, courseFreeTextMatch, searchCourseSummary, searchQualificationTitle);
CacheHelper.SaveCourseListResponse(courseCacheKey, courseResult);
}
new DBHelper().LogProviderRequestResponseLog(DBHelper.ServiceMethodName.GetCourseList, stopwatch.ElapsedMilliseconds, DBHelper.Serialize(request), DBHelper.Serialize(courseResult), isPublicAPI, request.APIKey, courseResult.NumberOfRecords);
return(courseResult);
}
catch (Exception ex)
{
LogException(ex, "GetCourseList");
throw;
}
}
private static Response ListCourseResponse(CourseListRequest courseListRequest)
{
DatabaseManager database = new DatabaseManager();
(MySqlDataReader reader, var Connection) = database.RunQuery($"SELECT course_details.courseid, course_details.coursename, course_details.authorid, account_data.fullname,course_details.rating,course_details.createdon,course_details.videocount " +
$"FROM course_details INNER JOIN account_data" +
$" WHERE account_data.username = course_details.authorid AND" +
$" account_data.username = '******'");
if (reader != null)
{
CourseListResponse res = new CourseListResponse()
{
Status = "OK", Reason = "Success"
};
res.CourseList = new List <CourseDetails>();
while (reader.Read())
{
res.CourseList.Add(new CourseDetails()
{
CourseID = reader.GetString(0),
CourseName = reader.GetString(1),
AuthorID = reader.GetString(2),
AuthorName = reader.GetString(3),
Rating = float.Parse(reader.GetString(4)),
CreatedOn = DateTime.Parse(reader.GetString(5)),
VideoCount = int.Parse(reader.GetString(6))
});
}
Connection.Close();
return(new Response()
{
Type = ResponseType.CourseList, Content = res, Status = "OK"
});
}
Connection.Close();
return(new Response()
{
Content = null, Type = ResponseType.CourseList, Status = "FAIL"
});
}
public static void SaveCourseListResponse(string courseStringCacheKey, CourseListResponse response)
{
var searchKey = string.Concat(CourseListResponseKey, "_", courseStringCacheKey);
CacheManagement.CacheHandler.Add(searchKey, response, CacheExpiryDate);
}
private CourseListResponse LoadGetCourseList(CourseListRequest request, DateTime earlierStartDate, DateTime applyUntil, bool isPublicAPI, bool v3SearchEnhancements, int?cutoffPercentage, bool providerFreeTextMatch, bool courseFreeTextMatch, bool searchCourseSummary, bool searchQualificationTitle)
{
using (SFA_SearchAPIEntities db = new SFA_SearchAPIEntities())
{
List <API_CourseList_Get_v2_Result> courseListItems;
if (!v3SearchEnhancements)
{
courseListItems =
db.API_CourseList_Get_v2(GetNullableString(request.SubjectKeyword),
GetNullableString(request.ProviderKeyword),
GetNullableString(request.QualificationTypes),
GetNullableString(request.QualificationLevels),
GetNullableString(request.StudyModes),
GetNullableString(request.AttendanceModes),
GetNullableString(request.AttendancePatterns),
!earlierStartDate.Equals(DateTime.MinValue) ? earlierStartDate : (DateTime?)null,
GetNullableString(request.LdcsCategoryCode),
!string.IsNullOrEmpty(request.ERTtgStatus) &&
request.ERTtgStatus.Equals("Y", StringComparison.CurrentCultureIgnoreCase),
GetNullableString(request.A10Codes),
!string.IsNullOrEmpty(request.IlsFlag) &&
request.IlsFlag.Equals("Y", StringComparison.CurrentCultureIgnoreCase),
GetNullableString(request.SflFlag),
GetNullableString(request.ERTtgStatus),
GetNullableString(request.ERAppStatus),
GetNullableString(request.AdultLRStatus),
GetNullableString(request.OtherFundingStatus),
!applyUntil.Equals(DateTime.MinValue) ? applyUntil : (DateTime?)null,
!string.IsNullOrEmpty(request.FlexStartFlag) &&
request.FlexStartFlag.Equals("Y", StringComparison.CurrentCultureIgnoreCase),
GetNullableString(request.Location),
request.Distance > 0 ? request.Distance : (float?)null,
request.PageNumber > 0 ? (int)request.PageNumber : 0,
request.RecordsPerPage > 0 ? request.RecordsPerPage : (int?)null,
request.SortBy,
request.ProviderId > 0 ? request.ProviderId : (Int32?)null,
isPublicAPI ? 1 : 0,
String.IsNullOrEmpty(request.DFE1619Funded)
? (Boolean?)null
: request.DFE1619Funded.Equals("Y", StringComparison.CurrentCultureIgnoreCase),
request.APIKey)
.ToList();
}
else
{
SearchQuery query = new SearchQuery(GetNullableString(request.SubjectKeyword));
courseListItems =
db.API_CourseList_Get_v3(String.Join(" ", query.Include),
String.Join(" ", query.Exclude),
String.Join("|", query.IncludeExact),
String.Join("|", query.ExcludeExact),
GetNullableString(request.ProviderKeyword),
GetNullableString(request.QualificationTypes),
GetNullableString(request.QualificationLevels),
GetNullableString(request.StudyModes),
GetNullableString(request.AttendanceModes),
GetNullableString(request.AttendancePatterns),
!earlierStartDate.Equals(DateTime.MinValue) ? earlierStartDate : (DateTime?)null,
GetNullableString(request.LdcsCategoryCode),
!string.IsNullOrEmpty(request.ERTtgStatus) &&
request.ERTtgStatus.Equals("Y", StringComparison.CurrentCultureIgnoreCase),
GetNullableString(request.A10Codes),
!string.IsNullOrEmpty(request.IlsFlag) &&
request.IlsFlag.Equals("Y", StringComparison.CurrentCultureIgnoreCase),
GetNullableString(request.SflFlag),
GetNullableString(request.ERTtgStatus),
GetNullableString(request.ERAppStatus),
GetNullableString(request.AdultLRStatus),
GetNullableString(request.OtherFundingStatus),
!applyUntil.Equals(DateTime.MinValue) ? applyUntil : (DateTime?)null,
!string.IsNullOrEmpty(request.FlexStartFlag) &&
request.FlexStartFlag.Equals("Y", StringComparison.CurrentCultureIgnoreCase),
GetNullableString(request.Location),
request.Distance > 0 ? request.Distance : (float?)null,
request.PageNumber > 0 ? (int)request.PageNumber : 0,
request.RecordsPerPage > 0 ? request.RecordsPerPage : (int?)null,
request.SortBy,
request.ProviderId > 0 ? request.ProviderId : (Int32?)null,
isPublicAPI ? 1 : 0,
String.IsNullOrEmpty(request.DFE1619Funded)
? (Boolean?)null
: request.DFE1619Funded.Equals("Y", StringComparison.CurrentCultureIgnoreCase),
request.APIKey,
cutoffPercentage == 0 ? null : cutoffPercentage,
providerFreeTextMatch ? 1 : 0,
courseFreeTextMatch ? 1 : 0,
searchCourseSummary ? 1 : 0,
searchQualificationTitle ? 1 : 0)
.ToList();
}
var response = new CourseListResponse
{
NumberOfRecords = courseListItems.Count > 0 ? courseListItems.FirstOrDefault().RecordCount.Value : 0,
Courses = new List <Course>()
};
var categoryCodeList = new DBHelper().LoadCategoryCodes(isPublicAPI ? 1 : 0, request.APIKey);
foreach (var courseListItem in courseListItems)
{
var course = new Course
{
Provider = new Provider
{
ProviderName = courseListItem.ProviderName,
TFPlusLoans = courseListItem.Loans24Plus,
DFE1619Funded = courseListItem.ProviderDfEFunded,
FEChoices_LearnerDestination = courseListItem.FEChoices_LearnerDestination,
FEChoices_LearnerSatisfaction = courseListItem.FEChoices_LearnerSatisfaction,
FEChoices_EmployerSatisfaction = courseListItem.FEChoices_EmployerSatisfaction
},
CourseId = courseListItem.CourseId,
CourseTitle = courseListItem.CourseTitle,
QualificationType = courseListItem.QualificationTypeRef,
QualificationLevel = courseListItem.QualificationBulkUploadRef,
LdcsCode1 = courseListItem.LDCS1,
LdcsCode2 = courseListItem.LDCS2,
LdcsCode3 = courseListItem.LDCS3,
LdcsCode4 = courseListItem.LDCS4,
LdcsCode5 = courseListItem.LDCS5,
LdcsDesc1 = GetLdscDescription(courseListItem.LDCS1, categoryCodeList),
LdcsDesc2 = GetLdscDescription(courseListItem.LDCS2, categoryCodeList),
LdcsDesc3 = GetLdscDescription(courseListItem.LDCS3, categoryCodeList),
LdcsDesc4 = GetLdscDescription(courseListItem.LDCS4, categoryCodeList),
LdcsDesc5 = GetLdscDescription(courseListItem.LDCS5, categoryCodeList),
NumberOfOpportunities = 1,
CourseSummary = courseListItem.CourseSummary,
Opportunities = new List <Opportunity>()
};
Opportunity opportunity = new Opportunity
{
OpportunityId = courseListItem.CourseInstanceId.ToString(),
StudyMode = courseListItem.StudyModeBulkUploadRef,
AttendanceMode = courseListItem.AttendanceModeBulkUploadRef,
AttendancePattern = courseListItem.AttendancePatternBulkUploadRef,
StartDate = courseListItem.StartDate.HasValue ? courseListItem.StartDate.Value.ToString("dd MMMM yyyy") : String.Empty,
StartDateDescription = courseListItem.StartDateDescription,
EndDate = courseListItem.EndDate.HasValue ? courseListItem.EndDate.Value.ToString("dd MMMM yyyy") : string.Empty,
RegionName = courseListItem.RegionName,
Distance = courseListItem.Distance.ToString(),
DurationUnit = courseListItem.DurationUnitBulkUploadRef,
DurationDescription = courseListItem.DurationAsText,
DurationValue = courseListItem.DurationUnitId.HasValue ? courseListItem.DurationUnitId.Value : 0,
DfE1619Funded = courseListItem.CourseDfEFunded ?? false,
Venue = new Venue
{
VenueName = courseListItem.VenueName,
AddressLine1 = courseListItem.AddressLine1,
AddressLine2 = courseListItem.AddressLine2,
Town = courseListItem.Town,
County = courseListItem.County,
Postcode = courseListItem.Postcode,
Latitude = courseListItem.Latitude.HasValue ? courseListItem.Latitude.Value.ToString() : string.Empty,
Longitude = courseListItem.Longitude.HasValue ? courseListItem.Longitude.Value.ToString() : string.Empty
},
A10 = new List <String>()
};
opportunity.A10.Add(courseListItem.A10FundingCode);
course.Opportunities.Add(opportunity);
response.Courses.Add(course);
}
return(response);
}
}
/// <summary>
/// Build CourseListResponseStructure object from CourseListResponse data and original CourseListRequestStructure.
/// </summary>
/// <param name="response">CourseListResponse data.</param>
/// <param name="request">Original CourseListRequestStructure.</param>
/// <returns>Populated CourseListResponseStructure object.</returns>
private static CourseListResponseStructure BuildCourseListResponseStructure(CourseListResponse response, CourseListRequestStructure request)
{
CourseListResponseStructure courseListResponse = new CourseListResponseStructure();
// Create matching Ldcs collection
List <CourseListResponseStructureMatchingLDCS> matchingLdcss =
new List <CourseListResponseStructureMatchingLDCS>();
foreach (LdcsCode ldcsCode in response.LdcsCodes)
{
CourseListResponseStructureMatchingLDCS matchingLdcs = new CourseListResponseStructureMatchingLDCS();
matchingLdcs.LDCS = BuildLdcsInfoType(ldcsCode.LdcsCodeValue, ldcsCode.LdcsCodeDescription);
matchingLdcs.Counts = ldcsCode.CourseCount.ToString();
matchingLdcss.Add(matchingLdcs);
}
courseListResponse.MatchingLDCS = matchingLdcss.ToArray();
// Create CourseStructure collection
List <CourseStructure> courseStructures = new List <CourseStructure>();
foreach (Course course in response.Courses)
{
// Get Course information
CourseStructure courseStructure = new CourseStructure();
courseStructure.Course = new CourseInfo();
courseStructure.Course.CourseID = course.CourseId.ToString();
courseStructure.Course.CourseSummary = course.CourseSummary;
courseStructure.Course.CourseTitle = course.CourseTitle;
courseStructure.Course.LDCS = new LDCSOutputType();
courseStructure.Course.LDCS.CatCode1 = BuildLdcsInfoType(course.LdcsCode1, course.LdcsDesc1);
courseStructure.Course.LDCS.CatCode2 = BuildLdcsInfoType(course.LdcsCode2, course.LdcsDesc2);
courseStructure.Course.LDCS.CatCode3 = BuildLdcsInfoType(course.LdcsCode3, course.LdcsDesc3);
courseStructure.Course.LDCS.CatCode4 = BuildLdcsInfoType(course.LdcsCode4, course.LdcsDesc4);
courseStructure.Course.LDCS.CatCode5 = BuildLdcsInfoType(course.LdcsCode5, course.LdcsDesc5);
courseStructure.Course.NoOfOpps = course.NumberOfOpportunities.ToString();
courseStructure.Course.QualificationLevel = course.QualificationLevel;
courseStructure.Course.QualificationType = course.QualificationType;
// Get Opportunity information
List <OpportunityInfo> opportunityInfos = new List <OpportunityInfo>();
foreach (Opportunity opportunity in course.Opportunities)
{
OpportunityInfo opportunityInfo = new OpportunityInfo();
opportunityInfo.AttendanceMode = opportunity.AttendanceMode;
opportunityInfo.AttendancePattern = opportunity.AttendancePattern;
opportunityInfo.OpportunityId = opportunity.OpportunityId;
StartDateType startDateType = BuildStartDateType(opportunity);
//StartDateType startDateType = new StartDateType();
//startDateType.ItemElementName = ItemChoiceType.Date;
//startDateType.Item = opportunity.StartDate;
// TODO: how do we add these in? I suspect we need to change the contract.
//StartDateType startDateDescType = new StartDateType();
//startDateDescType.ItemElementName = ItemChoiceType.DateDesc;
//startDateDescType.Item = opportunity.StartDateDescription;
opportunityInfo.StartDate = startDateType;
opportunityInfo.Duration = new DurationType();
opportunityInfo.Duration.DurationDescription = opportunity.DurationDescription;
opportunityInfo.Duration.DurationUnit = opportunity.DurationUnit;
if (opportunity.DurationValue != 0)
{
opportunityInfo.Duration.DurationValue = opportunity.DurationValue.ToString();
}
opportunityInfo.EndDate = opportunity.EndDate;
opportunityInfo.StudyMode = opportunity.StudyMode;
opportunityInfo.DFE1619Funded = opportunity.DfE1619Funded;
opportunityInfo.DFE1619FundedSpecified = true;
if (opportunity.Venue != null && !string.IsNullOrEmpty(opportunity.Venue.VenueName))
{
VenueInfo venueInfo = new VenueInfo();
if (!string.IsNullOrEmpty(opportunity.Distance))
{
venueInfo.DistanceSpecified = true;
venueInfo.Distance = float.Parse(opportunity.Distance);
}
venueInfo.VenueName = opportunity.Venue.VenueName;
venueInfo.VenueAddress =
BuildAddressType(opportunity.Venue.AddressLine1, opportunity.Venue.AddressLine2,
opportunity.Venue.Town, opportunity.Venue.County, opportunity.Venue.Postcode, opportunity.Venue.Latitude, opportunity.Venue.Longitude);
opportunityInfo.Item = venueInfo;
}
else
{
opportunityInfo.Item = opportunity.RegionName;
}
opportunityInfos.Add(opportunityInfo);
}
// pick out the first opportunity (expecting there to be only one looking at the java web service's design).
// if there is more than one here - then current site cannot deal with it.
courseStructure.Opportunity = opportunityInfos.ElementAt(0);
courseStructure.Provider = new ProviderInfo();
courseStructure.Provider.ProviderName = course.Provider.ProviderName;
courseStructure.Provider.TFPlusLoans = course.Provider.TFPlusLoans;
courseStructure.Provider.TFPlusLoansSpecified = true;
courseStructure.Provider.DFE1619Funded = course.Provider.DFE1619Funded;
courseStructure.Provider.DFE1619FundedSpecified = true;
if (course.Provider.FEChoices_EmployerSatisfaction.HasValue)
{
courseStructure.Provider.FEChoices_EmployerSatisfaction = course.Provider.FEChoices_EmployerSatisfaction.Value;
}
courseStructure.Provider.FEChoices_EmployerSatisfactionSpecified = course.Provider.FEChoices_EmployerSatisfaction.HasValue;
if (course.Provider.FEChoices_LearnerSatisfaction.HasValue)
{
courseStructure.Provider.FEChoices_LearnerSatisfaction = course.Provider.FEChoices_LearnerSatisfaction.Value;
}
courseStructure.Provider.FEChoices_LearnerSatisfactionSpecified = course.Provider.FEChoices_LearnerSatisfaction.HasValue;
if (course.Provider.FEChoices_LearnerDestination.HasValue)
{
courseStructure.Provider.FEChoices_LearnerDestination = course.Provider.FEChoices_LearnerDestination.Value;
}
courseStructure.Provider.FEChoices_LearnerDestinationSpecified = course.Provider.FEChoices_LearnerDestination.HasValue;
courseStructures.Add(courseStructure);
}
courseListResponse.CourseDetails = courseStructures.ToArray();
// Get Result information, i.e. page number etc.
courseListResponse.ResultInfo = new ResultInfoType();
int requestRecordsPerPage = 0;
if (!string.IsNullOrEmpty(request.RecordsPerPage))
{
requestRecordsPerPage = Int32.Parse(request.RecordsPerPage);
}
int responseRecordsPerPage = (requestRecordsPerPage > 0) ? requestRecordsPerPage : 50;
int totalRecords = response.NumberOfRecords;
int numberOfPages =
(totalRecords / responseRecordsPerPage) + ((totalRecords % responseRecordsPerPage == 0) ? 0 : 1);
int currentPage = 1;
if (!string.IsNullOrEmpty(request.PageNo))
{
currentPage = Int32.Parse(request.PageNo);
}
courseListResponse.ResultInfo.NoOfRecords = response.NumberOfRecords.ToString();
courseListResponse.ResultInfo.PageNo = (currentPage > 0) ? currentPage.ToString() : "1";
courseListResponse.ResultInfo.NoOfPages = numberOfPages.ToString();
// Get original Request details
courseListResponse.RequestDetails = request;
return(courseListResponse);
}
