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;
}
