public IActionResult Search(string term)
{
var results = new List <TrackingRow>();
SqlConnection cnn;
cnn = new SqlConnection(connectionString);
SqlCommand command;
SqlDataAdapter adapter = new SqlDataAdapter();
cnn.Open();
string q1 = "SELECT clientLast, clientFirst, dbo.ccr.ccrStatus, dbo.client.clientCode, phoneNumber FROM dbo.client INNER JOIN dbo.ccr ON dbo.client.clientCode = dbo.ccr.clientCode WHERE clientLast = '" + term + "';";
string q2 = "SELECT clientLast, clientFirst, dbo.ccr.ccrStatus, dbo.client.clientCode, phoneNumber FROM dbo.client INNER JOIN dbo.ccr ON dbo.client.clientCode = dbo.ccr.clientCode WHERE clientFirst = '" + term + "';";
command = new SqlCommand(q1, cnn);
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
string statusString = "";
switch (reader.GetInt32(2))
{
case 1:
statusString = "Open";
break;
case 0:
statusString = "Closed";
break;
case 2:
statusString = "Closed due to non-engagement";
break;
}
//We push information from the query into a row and onto the list of rows
TrackingRow row = new TrackingRow {
lname = reader.GetString(0), fname = reader.GetString(1), status = statusString, clientCode = reader.GetGuid(3), phoneNumber = reader.GetString(4)
};
results.Add(row);
}
reader.Close();
command = new SqlCommand(q2, cnn);
SqlDataReader reader2 = command.ExecuteReader();
while (reader2.Read())
{
string statusString = "";
switch (reader2.GetInt32(2))
{
case 1:
statusString = "Open";
break;
case 0:
statusString = "Closed";
break;
case 2:
statusString = "Closed due to non-engagement";
break;
}
//We push information from the query into a row and onto the list of rows
TrackingRow row = new TrackingRow {
lname = reader2.GetString(0), fname = reader2.GetString(1), status = statusString, clientCode = reader2.GetGuid(3), phoneNumber = reader2.GetString(4)
};
results.Add(row);
}
reader2.Close();
cnn.Close();
return(View("TrackingList", results));
}