private List<Person> GetPersonsWithTestToDo(List<Person> team) { List<Person> personsWithUndoneTests = new List<Person>(); List<Person> temp = new List<Person>(); var queryResult = from p in team orderby p.TestDate group p by p.UserName; foreach (IGrouping<string, Person> pair in queryResult) { Person toDoTest = new Person(); var ordered = pair.OrderByDescending(x => x.TestDate); //we want the last test, regardless how many there are in total Person lastTest = ordered.ElementAt(0); if (lastTest.TestDate == String.Empty) { toDoTest = lastTest; toDoTest.TestWaiting = "LST"; personsWithUndoneTests.Add(toDoTest); } else if (lastTest.TestType == "LST" && lastTest.TestGrade == "Underkänd") { toDoTest = lastTest; toDoTest.TestWaiting = "LST"; personsWithUndoneTests.Add(toDoTest); } else if (lastTest.TestType == "ÅKU" && lastTest.TestGrade == "Underkänd") { toDoTest = lastTest; toDoTest.TestWaiting = "ÅKU"; personsWithUndoneTests.Add(toDoTest); } else //is date due for ÅKU { DateTime passedTestDate = DateTime.Parse(lastTest.TestDate); if (passedTestDate.AddYears(1) < DateTime.Now) { toDoTest = lastTest; toDoTest.TestWaiting = "ÅKU"; personsWithUndoneTests.Add(toDoTest); } } } return personsWithUndoneTests; }
public List<Person> GetNoTestPersons() { //deffinerar koppling mot postgres List<Person> listOfPersons = new List<Person>(); bool isPassed; try { using (NpgsqlConnection conn = new NpgsqlConnection(myConnection)) { //öpnar koppling mot db conn.Open(); //kod för vad man vill göra mot databasen string query = "SELECT firstname, lastname, testtype, date, score, passed, username " + "FROM testoccasion t " + "RIGHT JOIN person p ON t.id_user = p.id " + "WHERE id_user IS NULL OR (testtype ='LST' AND passed = false OR testtype ='ÅKU' AND passed = false) " + "ORDER BY lastname"; using (NpgsqlCommand command = new NpgsqlCommand(query, conn)) { //command.Parameters.AddWithValue("name", userName); using (NpgsqlDataReader dr = command.ExecuteReader()) { while (dr.Read()) { Person p = new Person(); p.FirstName = dr[0].ToString(); p.LastName = dr[1].ToString(); p.TestType =dr[2].ToString(); p.TestDate = dr[3].ToString(); if (p.TestDate != "") { p.TestDate = p.TestDate.Substring(0, 10); } p.TestScore = dr[4].ToString(); if(bool.TryParse(dr[5].ToString(), out isPassed )) { if (isPassed) p.TestGrade = "Godkänd"; else p.TestGrade = "Underkänd"; } p.UserName = dr[6].ToString(); listOfPersons.Add(p); } } } } } catch (NpgsqlException ex) { NpgsqlException = ex.Message; } //returnerar listan med personer som inte skrivit LST och de som inte blivit godkännda return listOfPersons; }
public List<Person> GetTeamMembers(string query, string userName) { List<Person> listOfPersons = new List<Person>(); bool isPassed; try { using (NpgsqlConnection conn = new NpgsqlConnection(myConnection)) { conn.Open(); using (NpgsqlCommand command = new NpgsqlCommand(query, conn)) { command.Parameters.AddWithValue("userName", userName); using (NpgsqlDataReader dr = command.ExecuteReader()) { while (dr.Read()) { Person p = new Person(); p.FirstName = dr[0].ToString(); p.LastName = dr[1].ToString(); p.TestType =dr[2].ToString(); p.TestDate = dr[3].ToString(); if (p.TestDate != "") { p.TestDate = p.TestDate.Substring(0, 10); } p.TestScore = dr[4].ToString(); if(bool.TryParse(dr[5].ToString(), out isPassed )) { if (isPassed) p.TestGrade = "Godkänd"; else p.TestGrade = "Underkänd"; } p.UserName = dr[6].ToString(); listOfPersons.Add(p); } } } } } catch (NpgsqlException ex) { NpgsqlException = ex.Message; } return listOfPersons; }