public static FullWord GetFullWord(int id) { Word word = GetWord(id); if (word == null) { return(null); } FullWord fullWord = new FullWord(); fullWord.BaseWordID = word.BaseWordID; fullWord.ID = word.ID; fullWord._Word = word._Word; int defID = word.BaseWordID == null ? word.ID : (int)word.BaseWordID; fullWord.Definitions = GetDefinitions(defID); fullWord.Relationship = GetRelationship(fullWord.ID); fullWord.Related = GetRelatedWords(fullWord._Word); return(fullWord); }
public FullWord GetFullWord(String word) { FullWord fullWord = (FullWord)GetWord(word); fullWord.Definitions = GetDefinitions(fullWord.ID); fullWord.Relationship = GetRelationship(fullWord.ID); return(fullWord); }
public static List <FullWord> GetFullWordList(int[] ids) { List <FullWord> fwList = new List <FullWord>(); for (int i = 0; i < ids.Length; i++) { int wordID = ids[i]; FullWord fw = GetFullWord(wordID); fwList.Add(fw); } return(fwList); }
public static List <FullWord> GetFullWordList(String wordStr) { List <FullWord> fwList = new List <FullWord>(); List <int> ids = db.Fetch <int>(@"SELECT ID FROM Words WHERE Word=@0", wordStr); for (int i = 0; i < ids.Count; i++) { int wordID = ids[i]; FullWord fw = GetFullWord(wordID); fwList.Add(fw); } return(fwList); }
public static List <FullWord> GetWordsByExplore(ExploreVM explore) { IEnumerable <int> resultIDs = GetWordsToReturn(explore); List <WordwDefinition> resultWords = GetWordInformation(explore, resultIDs); Dictionary <string, FullWord> resultDict = new Dictionary <string, FullWord>(); foreach (WordwDefinition entry in resultWords) { FullWord fw; // "Search Distance" is a measure of the difference between the search term and the returned term. // However, if the search is of definitions, the "Search Distance" is instead the // index of the first appearance of the search term in a definition. if (resultDict.ContainsKey(entry._Word)) { fw = resultDict[entry._Word]; } else { fw = new FullWord(); fw._Word = entry._Word; fw.ID = entry.ID; fw.Definitions = new List <Definition>(); fw.Popularity = entry.Popularity; fw.Related = new List <RelatedWord>(); if (explore.isDefinitionSearch) { fw.SearchDistance = int.MaxValue; // this is measured later. } else { fw.SearchDistance = LevenshteinDistance.Compute(fw._Word, explore.search); } resultDict[entry._Word] = fw; } var isDef = entry.Definition != null; if (isDef) { Definition def = new Definition() { _Definition = entry.Definition, LanguageID = entry.LanguageID }; fw.Definitions.Add(def); if (explore.isDefinitionSearch) { // compute search distance int defSearchDistance = -1; if (!String.IsNullOrEmpty(explore.search)) { def._Definition.IndexOf(explore.search); } if (defSearchDistance == -1) { defSearchDistance = int.MaxValue; } fw.SearchDistance = Math.Min(fw.SearchDistance, defSearchDistance); } } else { RelatedWord rel = new RelatedWord() { ID = entry.BaseWordID ?? default(int), Relationship = entry.Relationship, Word = entry.BaseWord }; fw.Related.Add(rel); } } if (resultDict.Count > 0) { return(resultDict.Values.OrderBy(e => e.SearchDistance).ToList <FullWord>()); } else { return(new List <FullWord>()); // empty list. } }
public static List <FullWord> GetWordsByExploreOLD(ExploreVM explore) { PropertyInfo[] properties = typeof(ExploreVM).GetProperties(); List <string> concepts = new List <string>(); foreach (PropertyInfo property in properties) { bool isPropBool = property.PropertyType == Type.GetType("System.Boolean"); if (!isPropBool) { continue; } if (property.Name.StartsWith("is")) { continue; } bool isChecked = (bool)property.GetValue(explore); if (isChecked) { concepts.Add(property.Name); } } for (int i = 0; i < concepts.Count; i++) { string concept = concepts[i]; if (!concept.Contains("_")) { continue; } string[] elems = concept.Split('_'); concept = elems[1] + "-" + elems[0]; if (elems.Length > 2) { concept += "-long"; } concepts[i] = concept; } bool hasSearchTerm = !string.IsNullOrWhiteSpace(explore.search); Sql sql = new Sql(@"SELECT Words.ID, Words.Word, Words.Popularity, Definition"); if (explore.isDefinitionSearch) { sql.Append(@"FROM Definitions INNER JOIN Word_Definition ON Definitions.ID = Word_Definition.DefinitionID INNER JOIN Words ON Word_Definition.WordID = Words.ID"); } else { sql.Append(@", Relationship, BaseWords.Word as BaseWord FROM Words LEFT OUTER JOIN Word_Definition ON Words.ID = Word_Definition.WordID LEFT OUTER JOIN Definitions ON Word_Definition.DefinitionID = Definitions.ID LEFT OUTER JOIN WordRelationships ON Words.ID = WordRelationships.WordA LEFT OUTER JOIN Relationships ON WordRelationships.RelationshipID = Relationships.ID LEFT OUTER JOIN Words AS BaseWords ON WordRelationships.WordB = BaseWords.ID"); } if (hasSearchTerm) { if (explore.originalSearch == null) { explore.originalSearch = explore.search.Replace("*", ""); } string searchTerm = explore.search.Replace('*', '%'); if (explore.isDefinitionSearch) { if (!searchTerm.Contains('%')) { searchTerm = string.Format("%{0}%", searchTerm); } sql.Append(@"WHERE Definition LIKE (@0)", searchTerm); } else { searchTerm = searchTerm.Replace("a:", "ä").Replace("o:", "ö"); // perhaps this should be on the client side searchTerm = searchTerm.Trim().ToLower(); sql.Append(@"WHERE Words.Word LIKE (@0)", searchTerm); } } // sql.Append(@""); if (concepts.Count > 0) { if (hasSearchTerm) { sql.Append("AND"); } else { sql.Append("WHERE"); } sql.Append(@"Words.ID IN ( SELECT Words.ID FROM Words INNER JOIN Word_Concept ON Words.ID = Word_Concept.WordID INNER JOIN Concepts ON Word_Concept.ConceptID = Concepts.ID WHERE Concept IN (@0) AND Popularity IS NOT NULL GROUP BY Words.ID HAVING COUNT(*) = @1)", concepts.ToArray(), concepts.Count); } if (explore.isDefinitionSearch) { sql.Append("ORDER BY Words.Popularity DESC, Words.Word"); } else { sql.Append("ORDER BY Words.Popularity DESC, Words.ID, RelationshipID"); } Debug.WriteLine(sql); List <WordwDefinition> wordsDefs = db.Fetch <WordwDefinition>(sql); List <FullWord> resultSet = new List <FullWord>(); List <string> relList = new List <string>(); // TODO: There is a way to do this with LINQ but, for now.... int wordID = 0; FullWord word = null; WordwDefinition wDef = null; for (int i = 0; i < wordsDefs.Count; i++) { wDef = wordsDefs[i]; bool isNew = wordID != wDef.ID; if (isNew) { if (word != null && relList.Count > 0) { // finish up the previous word // adding definitions derived from relationships at the end of the list of definitions. if (word.Definitions == null) { word.Definitions = new List <Definition>(); } for (int j = 0; j < relList.Count; j++) { Definition def = new Definition() { _Definition = relList[j] }; word.Definitions.Add(def); } } word = new FullWord(); relList = new List <string>(); word.ID = wDef.ID; word._Word = wDef._Word; word.Popularity = wDef.Popularity; resultSet.Add(word); wordID = wDef.ID; word.SearchDistance = LevenshteinDistance.Compute(word._Word, explore.originalSearch); } if (!string.IsNullOrEmpty(wDef.Definition)) { if (word.Definitions == null) { word.Definitions = new List <Definition>(); } bool hasDef = word.Definitions.Any <Definition>(t => t._Definition == wDef.Definition); if (!hasDef) { Definition def = new Definition() { _Definition = wDef.Definition }; if (!word.Definitions.Contains(def)) { word.Definitions.Add(def); } } } if (!string.IsNullOrEmpty(wDef.Relationship)) { string relDefStr = String.Format("{0}: <a href={1}>{1}</a>", wDef.Relationship, wDef.BaseWord); if (!relList.Contains(relDefStr)) { relList.Add(relDefStr); } } } if (resultSet.Count == 1) { Sql relatedSql = new Sql(@"SELECT RelatedWords.Word, Relationship FROM WordRelationships INNER JOIN Words AS RelatedWords ON WordRelationships.WordA = RelatedWords.ID INNER JOIN Words ON WordRelationships.WordB = Words.ID INNER JOIN Relationships ON WordRelationships.RelationshipID = Relationships.ID WHERE Words.Word = @0 ORDER BY RelationshipID", explore.search); List <WordwDefinition> relWordList = db.Fetch <WordwDefinition>(relatedSql); if (relWordList.Count > 0) { relList = new List <string>(); } for (int k = 0; k < relWordList.Count; k++) { WordwDefinition relW = relWordList[k]; if (!string.IsNullOrEmpty(relW.Relationship)) { string relDefStr = String.Format("{0}: <a href={1}>{1}</a>", relW.Relationship, relW._Word); if (!relList.Contains(relDefStr)) { relList.Add(relDefStr); } } } } if (word != null && wDef != null && relList.Count > 0) { // finish up the previous word // adding definitions derived from relationships at the end of the list of definitions. if (word.Definitions == null) { word.Definitions = new List <Definition>(); } for (int j = 0; j < relList.Count; j++) { Definition def = new Definition() { _Definition = relList[j] }; word.Definitions.Add(def); } } if (resultSet.Count == 0 && explore.search.Length > 1) { string broaderTerm = explore.search; bool hasEndWildcard = (broaderTerm[broaderTerm.Length - 1] == '*'); if (hasEndWildcard) { broaderTerm = broaderTerm.Substring(0, broaderTerm.Length - 2); } broaderTerm += '*'; explore.search = broaderTerm; resultSet = GetWordsByExplore(explore); } if (!explore.isDefinitionSearch && hasSearchTerm && resultSet.Count > 0 && resultSet.First().SearchDistance > 0) { resultSet.Sort((a, b) => a.SearchDistance.CompareTo(b.SearchDistance)); } return(resultSet); }