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