public void InsertJobType(JobType jobType) { lock (DbContext.locker) { var rowsAffected = Db.Update(jobType); if (rowsAffected == 0) { // The item does not exists in the database so safe to insert Db.Insert(jobType); } } }
/// <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; }
/// <summary> /// Deseriliazes a singular Jobs with childrem. /// This method is not fully completed and should be used with caution. /// </summary> /// <param name="jsonString">Serialized data contain information about job and its children</param> /// <returns>A deserialized Jobs object</returns> private Job Deserialize(string jsonString) { DbJob db = new DbJob(); Dictionary<string, object> dict = JsonConvert.DeserializeObject<Dictionary<string, object>>(jsonString); System.Diagnostics.Debug.WriteLine("DeserializeApiData. Printing Key Value:"); string[] keys = dict.Keys.ToArray(); Job j = new Job(); j.companies = new List<Company>(); j.jobTypes = new List<JobType>(); j.locations = new List<Location>(); j.studyGroups = new List<StudyGroup>(); CompaniesController cp = new CompaniesController(); 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")) { j.uuid = dict[key].ToString(); } if (key.Equals("title")) { j.title = dict[key].ToString(); } if (key.Equals("description")) { j.description = dict[key].ToString(); } if (key.Equals("webpage")) { j.webpage = dict[key].ToString(); } if (key.Equals("expiryDate")) { DateTime dateTime = (DateTime)dict[key]; j.expiryDate = long.Parse(dateTime.ToString("yyyyMMddHHmmss")); } if (key.Equals("modified")) { DateTime dateTime = (DateTime)dict[key]; j.modified = long.Parse(dateTime.ToString("yyyyMMddHHmmss")); } if (key.Equals("published")) { DateTime dateTime = (DateTime)dict[key]; j.published = long.Parse(dateTime.ToString("yyyyMMddHHmmss")); } if (key.Equals("companies")) { 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("DeserializeOneJobs: company.id: " + company.id); j.companies.Add(company); dbCompany.UpdateCompany(company); System.Diagnostics.Debug.WriteLine("DeserializeOneJobs: After j.companies.Add(company)"); string jobUuid = dict["uuid"].ToString(); dbCompany.InsertCompanyJob(company.id, jobUuid); } } if (key.Equals("studyGroups")) { IEnumerable studyGroups = (IEnumerable)dict[key]; //Newtonsoft.Json.Linq.JArray' System.Diagnostics.Debug.WriteLine("studyGroups created"); DbStudyGroup dbStudyGroup = new DbStudyGroup(); 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(); } j.studyGroups.Add(sg); string jobUuid = dict["uuid"].ToString(); dbStudyGroup.InsertStudyGroupJob(sg.id, jobUuid); } } if (key.Equals("locations")) { IEnumerable locations = (IEnumerable)dict[key]; //Newtonsoft.Json.Linq.JArray' DbLocation dbLocation = new DbLocation(); System.Diagnostics.Debug.WriteLine("location created"); foreach (var location in locations) { System.Diagnostics.Debug.WriteLine("foreach initiated"); Dictionary<string, object> locationDict = JsonConvert.DeserializeObject<Dictionary<string, object>>(location.ToString()); Location loc = new Location(); if (locationDict.ContainsKey("id")) { loc.id = locationDict["id"].ToString(); System.Diagnostics.Debug.WriteLine("location id: " + loc.id); } if (locationDict.ContainsKey("name")) { loc.name = locationDict["name"].ToString(); } dbLocation.InsertLocation(loc); j.locations.Add(loc); string jobUuid = dict["uuid"].ToString(); dbLocation.InsertLocationJob(loc.id, jobUuid); } } if (key.Equals("jobTypes")) { IEnumerable jobTypes = (IEnumerable)dict[key]; //Newtonsoft.Json.Linq.JArray' DbJobType dbJobType = new DbJobType(); 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 j.jobTypes.Add(jt);"); j.jobTypes.Add(jt); string jobUuid = dict["uuid"].ToString(); dbJobType.InsertJobTypeJob(jt.id, jobUuid); } } } db.UpdateJob(j); return j; }
private async void TestInsertProject(object sender, EventArgs e) { DbContext DbContext = DbContext.GetDbContext; SQLiteConnection Db = DbContext.Db; System.Diagnostics.Debug.WriteLine("Before insert Project.Count: " + Db.Query<Project>("Select * from Project").Count()); ProjectsController jc = new ProjectsController(); string testUuid = "colemak"; Project project = new Project() { uuid = testUuid, }; string companyId = "Ikea"; Company comp = new Company() { id = companyId }; string courseId = "sverige"; Course course = new Course() { id = courseId }; string sgId = "dykking"; StudyGroup sg = new StudyGroup() { id = sgId }; StudyGroupProject sgj = new StudyGroupProject() { StudyGroupId = sgId, ProjectUuid = testUuid }; CourseProject lj = new CourseProject() { CourseId = courseId, ProjectUuid = testUuid }; CompanyProject cp = new CompanyProject() { CompanyId = companyId, ProjectUuid = testUuid }; string jtId = "10aarErfaringEcma6"; JobType jt = new JobType() { id = jtId }; JobTypeProject jtp = new JobTypeProject() { ProjectUuid = testUuid, JobTypeId = jtId }; // try catch on tables that will not be affected by a job delete try { Db.Insert(comp); } catch { } Db.Insert(project); try { Db.Insert(course); } catch { } try { Db.Insert(sg); } catch { } Db.Insert(sgj); Db.Insert(lj); Db.Insert(cp); try { Db.Insert(jt); } catch { } Db.Insert(jtp); System.Diagnostics.Debug.WriteLine("After insert: Project.Count: " + Db.Query<Project>("Select * from Project").Count()); }
private async void TestInsertJob(object sender, EventArgs e) { DbContext DbContext = DbContext.GetDbContext; SQLiteConnection Db = DbContext.Db; System.Diagnostics.Debug.WriteLine("Before insert Job.Count: " + Db.Query<Job>("Select * from Job").Count()); JobsController jc = new JobsController(); DateTime yesterday = DateTime.Now.AddDays(-1); long n = long.Parse(yesterday.ToString("yyyyMMddHHmmss")); string testUuid = "colemak"; Job job = new Job() { uuid = testUuid, expiryDate = n }; string companyId = "Ikea"; Company comp = new Company() { id = companyId }; string locationId = "sverige"; Location loc = new Location() { id = locationId }; string sgId = "dykking"; StudyGroup sg = new StudyGroup() { id = sgId }; StudyGroupJob sgj = new StudyGroupJob() { StudyGroupId = sgId, JobUuid = testUuid }; LocationJob lj = new LocationJob() { LocationId = locationId, JobUuid = testUuid }; CompanyJob cj = new CompanyJob() { CompanyId = companyId, JobUuid = testUuid }; string jtId = "10aarErfaringEcma6"; JobType jt = new JobType() { id = jtId }; JobTypeJob jtj = new JobTypeJob() { JobUuid = testUuid, JobTypeId = jtId }; // try catch on tables that will not be affected by a job delete try { Db.Insert(comp); } catch { } Db.Insert(job); try { Db.Insert(loc); } catch { } try { Db.Insert(sg); } catch { } Db.Insert(sgj); Db.Insert(lj); Db.Insert(cj); try { Db.Insert(jt); } catch { } Db.Insert(jtj); System.Diagnostics.Debug.WriteLine("After insert: Job.Count: " + Db.Query<Job>("Select * from Job").Count()); }
/// <summary> /// This test method require that you have not logged in and got no authorization /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private async void TestDeleteExpiredJobs(object sender, EventArgs e) { DbJob jc = new DbJob(); DbContext DbContext = DbContext.GetDbContext; SQLiteConnection Db = DbContext.Db; DateTime yesterday = DateTime.Now.AddDays(-1); long n = long.Parse(yesterday.ToString("yyyyMMddHHmmss")); string testUuid = "colemak"; Job job = new Job() { uuid = testUuid, expiryDate = n }; string companyId = "Ikea"; Company comp = new Company() { id = companyId }; string locationId = "sverige"; Location loc = new Location() { id = locationId }; string sgId = "dykking"; StudyGroup sg = new StudyGroup() { id = sgId }; StudyGroupJob sgj = new StudyGroupJob() { StudyGroupId = sgId, JobUuid = testUuid }; LocationJob lj = new LocationJob() { LocationId = locationId, JobUuid = testUuid }; CompanyJob cj = new CompanyJob() { CompanyId = companyId, JobUuid = testUuid }; string jtId = "10aarErfaringEcma6"; JobType jt = new JobType() { id = jtId }; JobTypeJob jtj = new JobTypeJob() { JobUuid = testUuid, JobTypeId = jtId }; Db.Insert(comp); Db.Insert(job); Db.Insert(loc); Db.Insert(sg); Db.Insert(sgj); Db.Insert(lj); Db.Insert(cj); Db.Insert(jt); Db.Insert(jtj); Job j = Db.Get<Job>(testUuid); System.Diagnostics.Debug.WriteLine("j.expiryDate: " + j.expiryDate); System.Diagnostics.Debug.WriteLine("StudyGroup.Count: " + Db.Query<StudyGroup>("Select * from StudyGroup").Count()); System.Diagnostics.Debug.WriteLine("Job.Count: " + Db.Query<Job>("Select * from Job").Count()); System.Diagnostics.Debug.WriteLine("JobType.Count: " + Db.Query<JobType>("Select * from JobType").Count()); System.Diagnostics.Debug.WriteLine("Location.Count: " + Db.Query<Location>("Select * from Location").Count()); System.Diagnostics.Debug.WriteLine("Company.Count: " + Db.Query<Company>("Select * from Company").Count()); System.Diagnostics.Debug.WriteLine("CompanyJob.Count: " + Db.Query<CompanyJob>("Select * from CompanyJob").Count()); System.Diagnostics.Debug.WriteLine("JobTypeJob.Count: " + Db.Query<JobTypeJob>("Select * from JobTypeJob").Count()); System.Diagnostics.Debug.WriteLine("LocationJob.Count: " + Db.Query<LocationJob>("Select * from LocationJob").Count()); System.Diagnostics.Debug.WriteLine("StudyGroupJob.Count: " + Db.Query<StudyGroupJob>("Select * from StudyGroupJob").Count()); System.Diagnostics.Debug.WriteLine("Time for delete"); jc.DeleteAllExpiredJobs(); System.Diagnostics.Debug.WriteLine("Job.Count: " + Db.Query<Job>("Select * from Job").Count()); System.Diagnostics.Debug.WriteLine("CompanyJob.Count: " + Db.Query<CompanyJob>("Select * from CompanyJob").Count()); System.Diagnostics.Debug.WriteLine("JobTypeJob.Count: " + Db.Query<JobTypeJob>("Select * from JobTypeJob").Count()); System.Diagnostics.Debug.WriteLine("LocationJob.Count: " + Db.Query<LocationJob>("Select * from LocationJob").Count()); System.Diagnostics.Debug.WriteLine("StudyGroupJob.Count: " + Db.Query<StudyGroupJob>("Select * from StudyGroupJob").Count()); //CompanyJobs, StudyGroupJob, LocationJob og JobTypeJob. }
// fix later public void UpdateJobType(JobType jobType) { try { lock (DbContext.locker) { Db.Update(jobType); } } catch { } }