public static TribalCourse ExtractCourseFromDbReader(SqlDataReader reader)
{
TribalCourse tribalCourse = new TribalCourse();
tribalCourse.Ukprn = (int)CheckForDbNull(reader["Ukprn"], 0);
tribalCourse.CourseId = (int)CheckForDbNull(reader["CourseId"], 0);
tribalCourse.CourseTitle = (string)CheckForDbNull(reader["CourseTitle"], string.Empty);
tribalCourse.LearningAimRefId = (string)CheckForDbNull(reader["LearningAimRefId"], string.Empty);
tribalCourse.QualificationLevelId = (int)CheckForDbNull(reader["QualificationLevelId"], 0);
tribalCourse.LearningAimAwardOrgCode = (string)CheckForDbNull(reader["LearningAimAwardOrgCode"], string.Empty);
tribalCourse.Qualification = (string)CheckForDbNull(reader["Qualification"], string.Empty);
tribalCourse.CourseSummary = (string)CheckForDbNull(reader["CourseSummary"], string.Empty);
tribalCourse.EntryRequirements = (string)CheckForDbNull(reader["EntryRequirements"], string.Empty);
tribalCourse.EquipmentRequired = (string)CheckForDbNull(reader["EquipmentRequired"], string.Empty);
tribalCourse.AssessmentMethod = (string)CheckForDbNull(reader["AssessmentMethod"], string.Empty);
////
//tribalCourse.AdvancedLearnerLoan = // TODO:
return(tribalCourse);
}
public static List <TribalCourse> GetCoursesByProviderUKPRN(int ProviderUKPRN, string connectionString, out string ProviderName, out bool AdvancedLearnerLoan, out string errorMessageGetCourses)
{
var tribalCourses = new List <TribalCourse>();
ProviderName = string.Empty;
AdvancedLearnerLoan = false;
errorMessageGetCourses = string.Empty;
using (var sqlConnection = new SqlConnection(connectionString))
{
using (var command = sqlConnection.CreateCommand())
{
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "dfc_GetCoursesByProviderUKPRN";
command.Parameters.Add(new SqlParameter("@ProviderUKPRN", SqlDbType.Int));
command.Parameters["@ProviderUKPRN"].Value = ProviderUKPRN;
command.Parameters.Add(new SqlParameter("@ProviderName", SqlDbType.NVarChar, 200));
command.Parameters["@ProviderName"].Direction = ParameterDirection.Output;
command.Parameters.Add(new SqlParameter("@AdvancedLearnerLoan", SqlDbType.Bit));
command.Parameters["@AdvancedLearnerLoan"].Direction = ParameterDirection.Output;
try
{
//Open connection.
sqlConnection.Open();
using (SqlDataReader dataReader = command.ExecuteReader())
{
while (dataReader.Read())
{
TribalCourse tribalCourse = ExtractCourseFromDbReader(dataReader);
if (tribalCourse != null)
{
tribalCourses.Add(tribalCourse);
}
}
// Close the SqlDataReader.
dataReader.Close();
}
// Get the Provider Name
ProviderName = (string)CheckForDbNull(command.Parameters["@ProviderName"].Value, string.Empty);
// Get the AdvancedLearnerLoan
AdvancedLearnerLoan = (bool)CheckForDbNull(command.Parameters["@AdvancedLearnerLoan"].Value, false);
}
catch (Exception ex)
{
errorMessageGetCourses = string.Format("Error Message: {0}" + Environment.NewLine + "Stack Trace: {1}", ex.Message, ex.StackTrace);
}
finally
{
sqlConnection.Close();
}
}
}
return(tribalCourses);
}
public static Course MapTribalCourseToCourse(TribalCourse tribalCourse, int numberOfMonthsAgo, bool dummyMode, out List <string> mappingMessages, out bool courseNOTtoBeMigrated)
{
var course = new Course();
var courseRuns = new List <CourseRun>();
mappingMessages = new List <string>();
courseNOTtoBeMigrated = false;
//var courseRunsToBeRemovedAsTooOld = new List<CourseRun>();
foreach (var tribalCourseRun in tribalCourse.TribalCourseRuns)
{
var courseRun = new CourseRun();
// JUST FOR TESTING - DO NOT UNCOMMENT
//tribalCourseRun.AttendanceType = AttendanceType.DistanceWithAttendance;
//tribalCourseRun.DurationUnit = TribalDurationUnit.Terms;
//tribalCourseRun.StudyMode = TribalStudyMode.PartOfAFulltimeProgram;
//tribalCourseRun.AttendancePattern = TribalAttendancePattern.Customised;
courseRun.id = Guid.NewGuid();
courseRun.CourseInstanceId = tribalCourseRun.CourseInstanceId;
courseRun.VenueId = tribalCourseRun.VenueGuidId;
courseRun.CourseName = tribalCourseRun.CourseName;
courseRun.ProviderCourseID = tribalCourseRun.ProviderOwnCourseInstanceRef;
// AttendanceType <=> DeliveryMode,
switch (tribalCourseRun.AttendanceType)
{
case AttendanceType.Location:
courseRun.DeliveryMode = DeliveryMode.ClassroomBased;
break;
case AttendanceType.WorkBased:
case AttendanceType.FaceToFaceNonCampus:
if (null != courseRun.VenueId)
{
courseRun.DeliveryMode = DeliveryMode.ClassroomBased;
}
else
{
courseRun.DeliveryMode = DeliveryMode.WorkBased;
}
break;
case AttendanceType.OnlineWithoutAttendance:
case AttendanceType.OnlineWithAttendance:
courseRun.DeliveryMode = DeliveryMode.Online;
break;
case AttendanceType.MixedMode:
case AttendanceType.DistanceWithAttendance:
case AttendanceType.DistanceWithoutAttendance:
case AttendanceType.NotKnown:
case AttendanceType.Undefined:
default:
courseRun.DeliveryMode = DeliveryMode.Undefined;
mappingMessages.Add($"ATTENTION - CourseRun { tribalCourseRun.CourseInstanceId } with Ref: '{ tribalCourseRun.ProviderOwnCourseInstanceRef }' is set to PENDING " +
$"because your AttendanceType is set to { tribalCourseRun.AttendanceType } and we don't have it" + Environment.NewLine);
break;
}
// StartDate & FlexibleStartDate
// Uncomment for testing only
//tribalCourseRun.StartDate = DateTime.Now.AddMonths(-5);
if (tribalCourseRun.StartDate != null && tribalCourseRun.StartDate > DateTime.MinValue)
{
courseRun.StartDate = tribalCourseRun.StartDate;
courseRun.FlexibleStartDate = false;
//if (tribalCourseRun.StartDate >= (DateTime.Now.AddMonths(-numberOfMonthsAgo)))
//{
// courseRun.StartDate = tribalCourseRun.StartDate;
// courseRun.FlexibleStartDate = false;
//}
//else
//{
// //courseRunsIsTooOld.Add(true);
// courseRunsToBeRemovedAsTooOld.Add(courseRun);
// mappingMessages.Add($"ATTENTION - CourseRun { tribalCourseRun.CourseInstanceId } with Ref: '{ tribalCourseRun.ProviderOwnCourseInstanceRef }' was REMOVED " +
// $"because the CourseRun StartDate ( { tribalCourseRun.StartDate.Value.ToShortDateString() } ) was more than 3 months ago and we didn't migrate it" + Environment.NewLine);
//}
}
else
{
// latest decision Imran & Mark C.
courseRun.StartDate = null;
courseRun.FlexibleStartDate = false;
mappingMessages.Add($"ATTENTION - CourseRun { tribalCourseRun.CourseInstanceId } with Ref: '{ tribalCourseRun.ProviderOwnCourseInstanceRef }' was set to Pending, because it didn't have StartDate " + Environment.NewLine);
}
courseRun.CourseURL = tribalCourseRun.Url;
courseRun.Cost = tribalCourseRun.Price;
courseRun.CostDescription = tribalCourseRun.PriceAsText;
// DurationUnit & DurationValue
switch (tribalCourseRun.DurationUnit)
{
case TribalDurationUnit.Hours:
courseRun.DurationValue = tribalCourseRun.DurationValue;
courseRun.DurationUnit = DurationUnit.Hours;
break;
case TribalDurationUnit.Days:
courseRun.DurationValue = tribalCourseRun.DurationValue;
courseRun.DurationUnit = DurationUnit.Days;
break;
case TribalDurationUnit.Weeks:
courseRun.DurationValue = tribalCourseRun.DurationValue;
courseRun.DurationUnit = DurationUnit.Weeks;
break;
case TribalDurationUnit.Months:
courseRun.DurationValue = tribalCourseRun.DurationValue;
courseRun.DurationUnit = DurationUnit.Months;
break;
case TribalDurationUnit.Terms:
if (tribalCourseRun.DurationValue == null)
{
courseRun.DurationValue = null;
}
else
{
courseRun.DurationValue = (tribalCourseRun.DurationValue ?? 0) * 3;
}
courseRun.DurationUnit = DurationUnit.Months;
mappingMessages.Add($"ATTENTION - CourseRun { tribalCourseRun.CourseInstanceId } with Ref: '{ tribalCourseRun.ProviderOwnCourseInstanceRef }' was set to DurationUnit = { tribalCourseRun.DurationUnit } " +
$"and DurationValue = { tribalCourseRun.DurationValue }. We needed to convert it to DurationUnit = { courseRun.DurationUnit } and DurationValue = { courseRun.DurationValue }." + Environment.NewLine);
//courseRun.DurationValue = tribalCourseRun.DurationValue;
//courseRun.DurationUnit = DurationUnit.Undefined;
//// Alternativly 3 x Months or X x Weeks // TODO
//courseRun.RecordStatus = RecordStatus.MigrationPending;
//mappingMessages.Add($"ATTENTION - CourseRun { tribalCourseRun.CourseInstanceId } with Ref: '{ tribalCourseRun.ProviderOwnCourseInstanceRef }' is set to PENDING " +
// $"because your DurationUnit is set to { tribalCourseRun.DurationUnit } and we don't have it" +
// $"We preserved the DurationValue, but you have to set appropriate DurationUnit and change the DurationValue accordingly" + Environment.NewLine);
break;
case TribalDurationUnit.Semesters:
courseRun.DurationValue = null; // New requirement - null
courseRun.DurationUnit = DurationUnit.Undefined;
mappingMessages.Add($"ATTENTION - CourseRun { tribalCourseRun.CourseInstanceId } with Ref: '{ tribalCourseRun.ProviderOwnCourseInstanceRef }' was set to DurationUnit = Semesters " +
$"and DurationValue = { tribalCourseRun.DurationValue }. 'Semester' DurationUnit is not supported." + Environment.NewLine);
// $"and DurationValue = { tribalCourseRun.DurationValue }. We preserved the DurationValue = { courseRun.DurationValue }, but you need to select available DurationUnit and change the DurationValue accordingly." + Environment.NewLine);
//$"and DurationValue = { tribalCourseRun.DurationValue }. 'Semester' DurationUnit is not supported." + Environment.NewLine);
break;
/*
* case TribalDurationUnit.Semesters:
* if (tribalCourseRun.DurationValue == null)
* courseRun.DurationValue = null;
* else
* courseRun.DurationValue = (tribalCourseRun.DurationValue ?? 0) * 3;
* courseRun.DurationUnit = DurationUnit.Months;
* mappingMessages.Add($"ATTENTION - CourseRun { tribalCourseRun.CourseInstanceId } with Ref: '{ tribalCourseRun.ProviderOwnCourseInstanceRef }' was set to DurationUnit = { tribalCourseRun.DurationUnit } " +
* $"and DurationValue = { tribalCourseRun.DurationValue }. We needed to convert it to DurationUnit = { courseRun.DurationUnit } and DurationValue = { courseRun.DurationValue }." + Environment.NewLine);
*
*
* //courseRun.DurationValue = tribalCourseRun.DurationValue;
* //courseRun.DurationUnit = DurationUnit.Undefined;
* //// Alternativly 3 x Months or X x Weeks // TODO
* //courseRun.RecordStatus = RecordStatus.MigrationPending;
* //mappingMessages.Add($"ATTENTION - CourseRun { tribalCourseRun.CourseInstanceId } with Ref: '{ tribalCourseRun.ProviderOwnCourseInstanceRef }' is set to PENDING " +
* // $"because your DurationUnit is set to { tribalCourseRun.DurationUnit } and we don't have it" +
* // $"We preserved the DurationValue, but you have to set appropriate DurationUnit and change the DurationValue accordingly" + Environment.NewLine);
* break;
*/
case TribalDurationUnit.Years:
courseRun.DurationValue = tribalCourseRun.DurationValue;
courseRun.DurationUnit = DurationUnit.Years;
break;
case TribalDurationUnit.Undefined:
default:
courseRun.DurationUnit = DurationUnit.Undefined;
mappingMessages.Add($"ATTENTION - CourseRun { tribalCourseRun.CourseInstanceId } with Ref: '{ tribalCourseRun.ProviderOwnCourseInstanceRef }' is set to PENDING " +
$"because your DurationUnit is set to { tribalCourseRun.DurationUnit } and we don't have it" + Environment.NewLine);
break;
}
// StudyMode
switch (tribalCourseRun.StudyMode)
{
case TribalStudyMode.FullTime:
courseRun.StudyMode = StudyMode.FullTime;
break;
case TribalStudyMode.PartTime:
courseRun.StudyMode = StudyMode.PartTime;
break;
case TribalStudyMode.Flexible:
courseRun.StudyMode = StudyMode.Flexible; // Here it looks identical but value changes from 4 to 3
break;
case TribalStudyMode.PartOfAFulltimeProgram:
case TribalStudyMode.NotKnown:
case TribalStudyMode.Undefined:
default:
courseRun.StudyMode = StudyMode.Undefined;
mappingMessages.Add($"ATTENTION - CourseRun { tribalCourseRun.CourseInstanceId } with Ref: '{ tribalCourseRun.ProviderOwnCourseInstanceRef }' is set to PENDING " +
$"because your StudyMode is set to { tribalCourseRun.StudyMode } and we don't have it" + Environment.NewLine);
break;
}
// AttendancePattern
switch (tribalCourseRun.AttendancePattern)
{
case TribalAttendancePattern.DaytimeWorkingHours:
courseRun.AttendancePattern = AttendancePattern.Daytime;
break;
case TribalAttendancePattern.DayBlockRelease:
courseRun.AttendancePattern = AttendancePattern.DayOrBlockRelease; // Here it looks identical but value changes from 2 to 4
break;
case TribalAttendancePattern.Evening:
case TribalAttendancePattern.Twilight:
courseRun.AttendancePattern = AttendancePattern.Evening;
break;
case TribalAttendancePattern.Weekend:
courseRun.AttendancePattern = AttendancePattern.Weekend; // Here it looks identical but value changes from 5 to 3
break;
case TribalAttendancePattern.Customised:
case TribalAttendancePattern.NotKnown:
case TribalAttendancePattern.NotApplicable:
case TribalAttendancePattern.Undefined:
default:
courseRun.AttendancePattern = AttendancePattern.Undefined;
mappingMessages.Add($"ATTENTION - CourseRun { tribalCourseRun.CourseInstanceId } with Ref: '{ tribalCourseRun.ProviderOwnCourseInstanceRef }' is set to PENDING, " +
$"because your AttendancePattern is set to { tribalCourseRun.AttendancePattern } and we don't have it" + Environment.NewLine);
break;
}
courseRun.CreatedDate = DateTime.Now;
courseRun.CreatedBy = "DFC – Course Migration Tool";
courseRuns.Add(courseRun);
}
course.id = Guid.NewGuid();
course.CourseId = tribalCourse.CourseId;
course.QualificationCourseTitle = tribalCourse.CourseTitle;
course.LearnAimRef = tribalCourse.LearningAimRefId;
course.NotionalNVQLevelv2 = tribalCourse.QualificationLevelIdString;
course.AwardOrgCode = tribalCourse.LearningAimAwardOrgCode;
course.QualificationType = tribalCourse.Qualification;
course.ProviderUKPRN = tribalCourse.Ukprn;
course.CourseDescription = tribalCourse.CourseSummary;
course.EntryRequirements = tribalCourse.EntryRequirements;
course.WhatYoullLearn = tribalCourse.WhatYoullLearn;
course.HowYoullLearn = tribalCourse.HowYoullLearn;
course.WhatYoullNeed = tribalCourse.EquipmentRequired;
course.HowYoullBeAssessed = tribalCourse.AssessmentMethod;
course.WhereNext = tribalCourse.WhereNext;
course.AdvancedLearnerLoan = tribalCourse.AdvancedLearnerLoan;
course.AdultEducationBudget = false; // WE don't have the data/or rule for it.
course.CreatedDate = DateTime.Now;
course.CreatedBy = "DFC – Course Migration Tool";
// Removing CourseRuns, which are older than 3 (configurable) months
//foreach(var courseRunToBeRemovedAsTooOld in courseRunsToBeRemovedAsTooOld)
//{
// courseRuns.Remove(courseRunToBeRemovedAsTooOld);
//}
if (courseRuns != null && courseRuns.Count > 0)
{
course.CourseRuns = courseRuns;
}
else
{
// We don't migrate courses without CourseRuns
courseNOTtoBeMigrated = true;
}
return(course);
}
