TableRow addSearchResultRow(SysAidTicketSearchResult result)
{
TableRow returnMe = new TableRow();
returnMe.Cells.Add(new TableCell()
{
Text = "<div style=\"\">" +
"<div style=\"font-size: 12pt; font-weight: bold;margin-bottom: 2px;\"><a href=\"Ticket.aspx?id=" + result.Ticket.ID + "\">" + result.Ticket.Title + "</a></div>" +
"<div><b>Created: </b>" + result.Ticket.WhenCreated.ToLongDateString() + " " + result.Ticket.WhenCreated.ToShortTimeString() + " by <b>" + result.Ticket.Reporter + "</b></div>" +
"<div><b>Type: </b>" + result.Ticket.ProblemType + " / " + result.Ticket.ProblemSubType + "</div>" +
"<div><b>Search Score: </b>" + result.SearchScore + ", <b>Search Hits:</b> " + result.FieldHits.ToCommaSeparatedString() + "</div>" +
"<br/><div style=\"font-size: 8pt;\">" + result.Ticket.Description + "</div>" +
"</div><br/>"
});
return(returnMe);
}
public List <SysAidTicketSearchResult> Find(string searchTerms)
{
string searchQuery = SQLQuery + " WHERE title LIKE @STERMS " +
"OR description LIKE @STERMS " +
"OR notes LIKE @STERMS " +
"OR resolution LIKE @STERMS " +
"OR solution LIKE @STERMS " +
"OR computer_id LIKE @STERMS " +
"OR request_user LIKE @STERMS";
List <SysAidTicketSearchResult> returnMe = new List <SysAidTicketSearchResult>();
// Split search terms
List <string> terms = new List <string>();
terms.Add(searchTerms.ToLower().Trim());
foreach (string term in searchTerms.Split(new char[] { ',', ';' }))
{
if (!string.IsNullOrEmpty(term))
{
terms.Add(term.ToLower().Trim());
}
}
List <int> foundTicketIDs = new List <int>();
List <SysAidTicket> foundTickets = new List <SysAidTicket>();
foreach (string term in terms)
{
using (SqlConnection connection = new SqlConnection(Settings.dbConnectionString))
{
using (SqlCommand sqlCommand = new SqlCommand())
{
sqlCommand.Connection = connection;
sqlCommand.CommandType = CommandType.Text;
sqlCommand.CommandText = searchQuery;
sqlCommand.Parameters.AddWithValue("STERMS", "%" + term + "%");
sqlCommand.Connection.Open();
SqlDataReader dataReader = sqlCommand.ExecuteReader();
if (dataReader.HasRows)
{
while (dataReader.Read())
{
int id = Parsers.ParseInt(dataReader["id"].ToString().Trim());
if (!foundTicketIDs.Contains(id))
{
foundTickets.Add(sqlDataReaderToTicket(dataReader));
foundTicketIDs.Add(id);
}
}
}
sqlCommand.Connection.Close();
}
}
}
// Go through each search result and attribute a score to it
foreach (SysAidTicket result in foundTickets)
{
SysAidTicketSearchResult searchResult = new SysAidTicketSearchResult(result);
int score = 0;
foreach (string term in terms)
{
// Determine which fields the terms were found in
if (result.Title.ToLower().Contains(term))
{
if (!searchResult.FieldHits.Contains("title"))
{
searchResult.FieldHits.Add("title");
}
score += 100;
}
if (result.Description.ToLower().Contains(term))
{
if (!searchResult.FieldHits.Contains("description"))
{
searchResult.FieldHits.Add("description");
}
score += 10;
}
if (result.Notes.ToLower().Contains(term))
{
if (!searchResult.FieldHits.Contains("notes"))
{
searchResult.FieldHits.Add("notes");
}
score += 50;
}
if (result.Resolution.ToLower().Contains(term))
{
if (!searchResult.FieldHits.Contains("resolution"))
{
searchResult.FieldHits.Add("resolution");
}
score += 50;
}
if (result.Solution.ToLower().Contains(term))
{
if (!searchResult.FieldHits.Contains("solution"))
{
searchResult.FieldHits.Add("solution");
}
score += 50;
}
if (term.Length > 5)
{
if (result.ComputerID.ToLower().Contains(term))
{
if (!searchResult.FieldHits.Contains("computer_id"))
{
searchResult.FieldHits.Add("computer_id");
}
score += 1000;
}
}
if (result.Reporter.ToLower().Contains(term))
{
if (!searchResult.FieldHits.Contains("reporter"))
{
searchResult.FieldHits.Add("reporter");
}
score += 10;
}
}
searchResult.SearchScore = score;
// We don't care about things that have a zero score
if (score > 0)
{
returnMe.Add(searchResult);
}
}
return(returnMe.OrderByDescending(t => t.SearchScore).ThenByDescending(t => t.Ticket.WhenCreated).ToList());
}
