/// <summary>
/// This method shows the information of the course and the related department.
/// </summary>
/// <param name="course">show the information in the course</param>
private static void ShowDbCourse(this DbCourse course)
{
if (course != null)
{
Console.WriteLine("CourseID:{0,-5} CourseName:{1,-15} DepartmentName:{2}", course.CourseID, course.Title, course.Department.Name);
}
}
public static async Task Run()
{
var repo = new PostgresCourseRepository();
repo.SetConnection(new ConnectionInfo
{
Host = "127.0.0.1",
Database = "pp3",
Port = 5432,
User = "******",
Password = "******"
});
var path = @"C:\git\ProgramPlanning\data\RawSpreadsheet.xlsx";
using (var package = new ExcelPackage(new FileInfo(path)))
{
var sheet = package.Workbook.Worksheets[0];
var prefixCol = 2;
var numCol = 3;
var outcomeColStart = 10;
for (int row = 4; row <= 48; row++)
{
var prefix = (string)sheet.Cells[row, prefixCol].Value;
var num = (int)(Double)sheet.Cells[row, numCol].Value;
Console.WriteLine($"Doing {prefix} {num}");
DbCourse dbCourse = repo.GetDbCourse(prefix, num);
for (int outcomeCol = outcomeColStart; outcomeCol < outcomeColStart + 12; outcomeCol++)
{
var outcomeText = (string)sheet.Cells[row, outcomeCol].Value;
if (outcomeText == null)
{
break;
}
var learningOutcome = new DbLearningOutcome
{
Description = outcomeText
};
learningOutcome = await repo.AddOutcomeAsync(learningOutcome);
var outcomeCourseLink = new DbCourseLearningOutcome
{
LearningOutcome_Id = learningOutcome.Id,
Course_Id = dbCourse.Id
};
await repo.AddCourseOutcomeLinkAsync(outcomeCourseLink);
Console.WriteLine($"Added learning outcome {outcomeText.Substring(0, Math.Min(outcomeText.Length, 25))}...");
}
}
Console.WriteLine("All done.");
}
Console.ReadLine();
}
/// <summary>
/// This method demonstrates how to undo the changes in Property level using DbContext.
/// </summary>
public static void UndoChangesInProperty()
{
using (DbMySchool school = new DbMySchool())
{
DbCourse course = school.DbCourses.FirstOrDefault();
DbDepartment department = school.DbDepartments.FirstOrDefault();
if (course != null)
{
Console.WriteLine("Before changes:");
course.ShowDbCourse();
Console.WriteLine("After changes:");
// Change the course Properties.
course.Title += "-Modified";
course.Department = department;
course.ShowDbCourse();
Console.WriteLine("After Undo Course Entity's Title Property:");
// Undo the change in the Entity Property level. UndoDbEntityProperty
// method will undo the Title property of the course, but the change of the
// Department Property will not be undone.
school.UndoDbEntityProperty(course, "Title");
course.ShowDbCourse();
}
}
}
/// <summary>
/// 向数据库中添加课程，如果课程已存在则自动增加时间
/// </summary>
/// <param name="TempCourse">要添加的课程</param>
public void AddCourse(Course TempCourse)
{
// 从本地数据库获取数据
var Database = new DataContext();
var TempDbCourseRes = Database.DbCourses
.Include(DbCourse => DbCourse.DbLocTimes)
.SingleOrDefault(a => a.Name.Equals(TempCourse.Name));
if (TempDbCourseRes == null)
{
// 新建课程
DbCourse TempDbCourse = new DbCourse();
TempDbCourse.Name = TempCourse.Name;
TempDbCourse.Teacher = TempCourse.Teacher;
TempDbCourse.Notes = TempCourse.Notes;
TempDbCourse.DbLocTimes = new List <DbLocTime>();
foreach (LocTime TempLocTime in TempCourse.LocTimes)
{
TempDbCourse.DbLocTimes.Add(new DbLocTime
{
Location = TempLocTime.Location,
Week = TempLocTime.Week,
WeekDay = TempLocTime.WeekDay,
BeginTime = TempLocTime.BeginTime,
EndTime = TempLocTime.EndTime
});
}
Database.DbCourses.Add(TempDbCourse);
}
else
{
// 为已有课程添加时间
foreach (LocTime TempLocTime in TempCourse.LocTimes)
{
if (TempDbCourseRes.DbLocTimes == null)
{
TempDbCourseRes.DbLocTimes = new List <DbLocTime>();
}
if (!TempDbCourseRes.DbLocTimes.Exists(t =>
t.Location.Equals(TempLocTime.Location) &&
t.Week.Equals(TempLocTime.Week) &&
t.WeekDay.Equals(TempLocTime.WeekDay) &&
t.BeginTime.Equals(TempLocTime.BeginTime) &&
t.EndTime.Equals(TempLocTime.EndTime)
))
{
TempDbCourseRes.DbLocTimes.Add(new DbLocTime
{
Location = TempLocTime.Location,
Week = TempLocTime.Week,
WeekDay = TempLocTime.WeekDay,
BeginTime = TempLocTime.BeginTime,
EndTime = TempLocTime.EndTime
});
}
}
}
Database.SaveChanges();
}
public async Task DeleteCourseAsync(long id)
{
var r = new DbCourse {
Id = id
};
Db.Courses.Attach(r);
Db.Courses.Remove(r);
await Db.SaveChangesAsync();
}
private string CreateLocalHash()
{
DbCourse db = new DbCourse();
List <Course> courses = db.GetAllCourses();
StringBuilder sb = new StringBuilder();
foreach (var c in courses)
{
sb.Append(Hasher.Base64Decode(c.id));
}
return(Hasher.CalculateMd5Hash(sb.ToString()));
}
/// <summary>
/// Gets all Courses from the servers REST Api.
/// </summary>
public async Task UpdateCoursesFromServer()
{
DbCourse db = new DbCourse();
System.Diagnostics.Debug.WriteLine("CoursesController - UpdateCoursesFromServer: initiated");
DbStudent dbStudent = new DbStudent();
string accessToken = dbStudent.GetStudentAccessToken();
if (accessToken == null)
{
Authenticater.Authorized = false;
return;
}
Uri url = new Uri(Adress);
System.Diagnostics.Debug.WriteLine("CoursesController - url " + url.ToString());
var client = new HttpClient();
client.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("bearer", accessToken);
try
{
var response = await client.GetAsync(url);
if (response.StatusCode == HttpStatusCode.OK)
{
System.Diagnostics.Debug.WriteLine("UpdateCoursesFromServer response " + response.StatusCode.ToString());
var newCourses = await response.Content.ReadAsAsync <IEnumerable <Course> >();
db.DeleteAllCourses();
db.InsertCourses(newCourses);
}
if (response.StatusCode == HttpStatusCode.Unauthorized)
{
Authenticater.Authorized = false;
}
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine("CoursesController - UpdateCoursesFromServer: await client.GetAsync(\"url\") Failed");
System.Diagnostics.Debug.WriteLine("CoursesController - UpdateCoursesFromServer: Exception msg: " + e.Message);
System.Diagnostics.Debug.WriteLine("CoursesController - UpdateCoursesFromServer: Stack Trace: \n" + e.StackTrace);
System.Diagnostics.Debug.WriteLine("CoursesController - UpdateCoursesFromServer: End Of Stack Trace");
}
}
public async Task <Course> MapDbCourse(DbCourse dbCourse)
{
var teachingLanguage = await _languageRepository
.GetLanguageByCodeAsync(dbCourse.TeachingLanguage);
var teachingLanguageTerritory = await _languageRepository
.GetTerritoryByCodeAsync(dbCourse.TeachingLanguageTerritory);
var learnigLanguage = await _languageRepository
.GetLanguageByCodeAsync(dbCourse.LearningLanguage);
var learnigLanguageTerritory = await _languageRepository
.GetTerritoryByCodeAsync(dbCourse.LearningLanguageTerritory);
var course = _mapper.Map <Course>(dbCourse);
course.LearningLanguage = new LanguageDto
{
Language = new Language
{
Code = learnigLanguage.Code,
Name = learnigLanguage.Name
},
Territory = new Territory
{
Code = learnigLanguageTerritory.Code,
Name = learnigLanguageTerritory.Name
}
};
course.TeachingLanguage = new LanguageDto
{
Language = new Language
{
Code = teachingLanguage.Code,
Name = teachingLanguage.Name
},
Territory = new Territory
{
Code = teachingLanguageTerritory.Code,
Name = teachingLanguageTerritory.Name
}
};
course.Level = _mapper.Map <CourseLevelDto>(dbCourse.Level);
return(course);
}
private async void GetAllFilters(object sender, EventArgs e)
{
DbLocation lc = new DbLocation();
DbCourse cc = new DbCourse();
DbStudyGroup sgc = new DbStudyGroup();
DbJobType jtc = new DbJobType();
List <Location> locationsFilter = lc.GetAllLocations();
List <Course> coursesFilter = cc.GetAllCourses();
List <StudyGroup> studyGroupsFilter = sgc.GetAllStudyGroups();
List <JobType> jobTypesJobFilter = jtc.GetJobTypeFilterJob();
List <JobType> jobTypesProjectFilter = jtc.GetJobTypeFilterProject();
System.Diagnostics.Debug.WriteLine("GetAllFilters: locationsFilter.Count: " + locationsFilter.Count);
System.Diagnostics.Debug.WriteLine("GetAllFilters: coursesFilter.Count: " + coursesFilter.Count);
System.Diagnostics.Debug.WriteLine("GetAllFilters: studyGroupsFilter.Count: " + studyGroupsFilter.Count);
System.Diagnostics.Debug.WriteLine("GetAllFilters: jobTypesJobFilter.Count: " + jobTypesJobFilter.Count);
System.Diagnostics.Debug.WriteLine("GetAllFilters: jobTypesProjectFilter.Count: " +
jobTypesProjectFilter.Count);
}
/// <summary>
/// 删除数据库内所有课程，并使用提供的新课程集合覆盖（性能差，尽量不要使用）（未测试使用）
/// </summary>
/// <param name="AllCourses">新的课程集合</param>
public async void OverrideAllCourses(Courses AllCourses)
{
// 从本地数据库获取数据
var Database = new DataContext();
// 清空数据库课程和时间
foreach (DbCourse TempDbCourse in Database.DbCourses)
{
Database.DbCourses.Remove(TempDbCourse);
}
foreach (DbLocTime TempDbLocTime in Database.DbLocTimes)
{
Database.DbLocTimes.Remove(TempDbLocTime);
}
// 添加新的课程
foreach (Course TempCourse in AllCourses.CourseCollection)
{
DbCourse TempDbCourse = new DbCourse();
TempDbCourse.Name = TempCourse.Name;
TempDbCourse.Teacher = TempCourse.Teacher;
TempDbCourse.Notes = TempCourse.Notes;
TempDbCourse.DbLocTimes = new List <DbLocTime>();
foreach (LocTime TempLocTime in TempCourse.LocTimes)
{
TempDbCourse.DbLocTimes.Add(new DbLocTime
{
Location = TempLocTime.Location,
Week = TempLocTime.Week,
WeekDay = TempLocTime.WeekDay,
BeginTime = TempLocTime.BeginTime,
EndTime = TempLocTime.EndTime
});
}
Database.DbCourses.Add(TempDbCourse);
}
await Database.SaveChangesAsync();
}
/// <summary>
/// This method demonstrates how to undo the changes in Context level using DbContext.
/// </summary>
public static void UndoChangesInContext()
{
using (DbMySchool school = new DbMySchool())
{
DbCourse course = school.DbCourses.FirstOrDefault();
if (course != null)
{
Console.WriteLine("Before changes:");
course.ShowDbCourse();
Console.WriteLine("After changes:");
// Change the course and the related department.
course.Title += "-Modified";
course.Department.Name += "-Modified";
course.ShowDbCourse();
Console.WriteLine("After Undo DbContext:");
// Undo the whole Context.
school.UndoDbContext();
course.ShowDbCourse();
}
}
}
public async ValueTask <ElementSummary?> AddCourseAsync(DescribedElementSummary des)
{
var u = await AccountManager.GetCurrentUserAsync();
if (u is null)
{
return(null);
}
var ir = new DbCourse
{
Director = u,
Description = des.Description,
Name = des.Name
};
await Db.Courses.AddAsync(ir);
await Db.SaveChangesAsync();
return(new ElementSummary
{
Id = ir.Id,
Name = ir.Name
});
}
/// <summary>
/// Deserializes a singular Project with children objects.
/// </summary>
/// <param name="jsonString">Serialized data contain information about project and its children</param>
/// <returns>A deserialized Project object</returns>
private Project Deserialize(string jsonString)
{
DbProject db = new DbProject();
Dictionary <string, object> dict = JsonConvert.DeserializeObject <Dictionary <string, object> >(jsonString);
System.Diagnostics.Debug.WriteLine("DeserializeApiData. Printing Key Value:");
string[] keys = dict.Keys.ToArray();
Project p = new Project();
p.companies = new List <Company>();
p.courses = new List <Course>();
p.studyGroups = new List <StudyGroup>();
p.jobTypes = new List <JobType>();
foreach (var key in keys)
{
System.Diagnostics.Debug.WriteLine("key: " + key);
System.Diagnostics.Debug.WriteLine("value: " + dict[key].ToString());
/*
* if (!key.Equals("companies") || !key.Equals("courses") || !key.Equals("degrees")
|| !key.Equals("jobTypes") || !key.Equals("studyGroup")) {} */
if (key.Equals("uuid"))
{
p.uuid = dict[key].ToString();
}
if (key.Equals("title"))
{
p.title = dict[key].ToString();
}
/*
* if (key.Equals("description"))
* {
* p.description = dict[key].ToString();
* }
*/
if (key.Equals("webpage"))
{
p.webpage = dict[key].ToString();
}
if (key.Equals("published"))
{
DateTime dateTime = (DateTime)dict[key];
p.published = long.Parse(dateTime.ToString("yyyyMMddHHmmss"));
}
if (key.Equals("modified"))
{
DateTime dateTime = (DateTime)dict[key];
p.modified = long.Parse(dateTime.ToString("yyyyMMddHHmmss"));
}
if (key.Equals("companies"))
{
// if not true then company already exist and needs to be updated.
CompaniesController cc = new CompaniesController();
DbCompany dbCompany = new DbCompany();
IEnumerable companies = (IEnumerable)dict[key];
//`Newtonsoft.Json.Linq.JArray'
System.Diagnostics.Debug.WriteLine("companies created");
foreach (var comp in companies)
{
System.Diagnostics.Debug.WriteLine("foreach initiated");
Dictionary <string, object> companyDict = JsonConvert.DeserializeObject <Dictionary <string, object> >(comp.ToString());
Company company = cc.DeserializeCompany(companyDict);
System.Diagnostics.Debug.WriteLine("Deserialize: company.id: " + company.id);
p.companies.Add(company);
dbCompany.UpdateCompany(company);
System.Diagnostics.Debug.WriteLine("Deserialize: After j.companies.Add(company)");
string projectUuid = dict["uuid"].ToString();
dbCompany.InsertCompanyProject(company.id, projectUuid);
}
}
if (key.Equals("courses"))
{
DbCourse dbCourse = new DbCourse();
IEnumerable courses = (IEnumerable)dict[key];
//Newtonsoft.Json.Linq.JArray'
System.Diagnostics.Debug.WriteLine("location created");
foreach (var course in courses)
{
System.Diagnostics.Debug.WriteLine("foreach initiated");
Dictionary <string, object> courseDict =
JsonConvert.DeserializeObject <Dictionary <string, object> >(course.ToString());
Course co = new Course();
if (courseDict.ContainsKey("id"))
{
co.id = courseDict["id"].ToString();
System.Diagnostics.Debug.WriteLine("Course id: " + co.id);
}
if (courseDict.ContainsKey("name"))
{
co.name = courseDict["name"].ToString();
}
dbCourse.InsertCourse(co);
p.courses.Add(co);
string projectUuid = dict["uuid"].ToString();
dbCourse.InsertCourseProject(co.id, projectUuid);
}
}
if (key.Equals("studyGroups"))
{
DbStudyGroup dbStudyGroup = new DbStudyGroup();
IEnumerable studyGroups = (IEnumerable)dict[key];
//Newtonsoft.Json.Linq.JArray'
System.Diagnostics.Debug.WriteLine("studyGroups created");
foreach (var studyGroup in studyGroups)
{
System.Diagnostics.Debug.WriteLine("foreach initiated");
Dictionary <string, object> studyGroupDict = JsonConvert.DeserializeObject <Dictionary <string, object> >(studyGroup.ToString());
StudyGroup sg = new StudyGroup();
if (studyGroupDict.ContainsKey("id"))
{
sg.id = studyGroupDict["id"].ToString();
}
if (studyGroupDict.ContainsKey("name"))
{
sg.name = studyGroupDict["name"].ToString();
}
p.studyGroups.Add(sg);
string projectUuid = dict["uuid"].ToString();
dbStudyGroup.InsertStudyGroupProject(sg.id, projectUuid);
}
}
/*
* if (key.Equals("approvedCourses"))
* {
*
* Same as companies implementation
*
* }
*
* if (key.Equals("degrees"))
* {
*
* Same as companies implementation
*
* }
*/
if (key.Equals("jobTypes"))
{
DbJobType dbJobType = new DbJobType();
IEnumerable jobTypes = (IEnumerable)dict[key];
//Newtonsoft.Json.Linq.JArray'
System.Diagnostics.Debug.WriteLine("jobTypes created");
foreach (var jobType in jobTypes)
{
System.Diagnostics.Debug.WriteLine("foreach initiated");
Dictionary <string, object> jtDict = JsonConvert.DeserializeObject <Dictionary <string, object> >(jobType.ToString());
JobType jt = new JobType();
if (jtDict.ContainsKey("id"))
{
jt.id = jtDict["id"].ToString();
}
if (jtDict.ContainsKey("name"))
{
jt.name = jtDict["name"].ToString();
}
dbJobType.InsertJobType(jt);
System.Diagnostics.Debug.WriteLine("before p.jobTypes.Add(jt);");
p.jobTypes.Add(jt);
string projectUuid = dict["uuid"].ToString();
dbJobType.InsertJobTypeProject(jt.id, projectUuid);
}
}
}
db.UpdateProject(p);
return(p);
}
