public object Convert(object value, Type targetType, object parameter, System.Globalization.CultureInfo culture) { if (value is IEnumerable<VocabCategory>) { IEnumerable<VocabCategory> categories = (IEnumerable<VocabCategory>)value; StringBuilder fullString = new StringBuilder(); foreach (VocabCategory category in categories) { if (CategoryDictionary.ContainsKey(category.ID)) { fullString.Append(CategoryDictionary[category.ID].Label).Append(" ; "); } } return fullString.ToString().Trim(new char[] { ' ', ';' }); } else if (value is VocabCategory) { VocabCategory category = (VocabCategory)value; if (CategoryDictionary.ContainsKey(category.ID)) { return CategoryDictionary[category.ID].Label; } } return null; }
/// <summary> /// Includes the categories of the given meaning in the entity. /// </summary> private void IncludeMeaningCategories(DaoConnection connection, VocabMeaning meaning) { IEnumerable <NameValueCollection> categories = connection.Query( string.Format("SELECT vc.* FROM {0} vmvc JOIN {1} vc ON (vmvc.{2}=vc.{3}) WHERE vmvc.{4}=@mid", SqlHelper.Table_VocabMeaning_VocabCategory, SqlHelper.Table_VocabCategory, SqlHelper.Field_VocabMeaning_VocabCategory_VocabCategoryId, SqlHelper.Field_VocabCategory_Id, SqlHelper.Field_VocabMeaning_VocabCategory_VocabMeaningId), new DaoParameter("@mid", meaning.ID)); VocabCategoryBuilder categoryBuilder = new VocabCategoryBuilder(); foreach (NameValueCollection nvcCategory in categories) { VocabCategory category = categoryBuilder.BuildEntity(nvcCategory, null); meaning.Categories.Add(category); } }
/// <summary> /// Parses a kanji element node. /// Updates the list with the available info. /// </summary> /// <param name="xkanjiElement">Element to parse.</param> /// <param name="vocabList">Vocab list to be updated.</param> /// <param name="seq">Sequence number of corresponding vocab item</param> private void ParseKanji(XElement xkanjiElement, List <VocabEntity> vocabList, long seq) { // Create a new vocab with the associated writing. VocabEntity vocab = new VocabEntity(); vocab.Seq = seq; vocab.KanjiWriting = xkanjiElement.Element(XmlNode_KanjiReading).Value; vocab.IsCommon = IsCommonWord(xkanjiElement, XmlNode_KanjiVocabReference); // For each kanji info node foreach (XElement xkanjiInf in xkanjiElement.Elements(XmlNode_KanjiInfo)) { // Associate the vocab with the category referred by the info. VocabCategory category = GetCategoryByLabel(xkanjiInf.Value); if (category != null && !vocab.Categories.Contains(category)) { vocab.Categories.Add(category); } } // Add the created vocab to the list. vocabList.Add(vocab); }
private static VocabCategoryInfo GetInfo(VocabCategory c) { switch (c.ShortName) { case "MA": return new VocabCategoryInfo(R.VocabCat_MartialArts, Colors.Black); case "X": return new VocabCategoryInfo(R.VocabCat_X, Colors.Black); case "abbr": return new VocabCategoryInfo(R.VocabCat_Abbreviation, Colors.Black); case "adj-i": return new VocabCategoryInfo(R.VocabCat_IAdjective, Colors.Black); case "adj-na": return new VocabCategoryInfo(R.VocabCat_NaAdjective, Colors.Black); case "adj-no": return new VocabCategoryInfo(R.VocabCat_NoAdjective, Colors.Black); case "adj-pn": return new VocabCategoryInfo(R.VocabCat_PrenominalAdjective, Colors.Black); case "adj-t": return new VocabCategoryInfo(R.VocabCat_TaruAdjective, Colors.Black); case "adj-f": return new VocabCategoryInfo(R.VocabCat_PrenominalNounVerb, Colors.Black); case "adj": return new VocabCategoryInfo(R.VocabCat_Adjective, Colors.Black); case "adv": return new VocabCategoryInfo(R.VocabCat_Adverb, Colors.Black); case "adv-to": return new VocabCategoryInfo(R.VocabCat_ToAdverb, Colors.Black); case "arch": return new VocabCategoryInfo(R.VocabCat_Archaism, Colors.Black); case "ateji": return new VocabCategoryInfo(R.VocabCat_Ateji, Colors.Black); case "aux": return new VocabCategoryInfo(R.VocabCat_Auxiliary, Colors.Black); case "aux-v": return new VocabCategoryInfo(R.VocabCat_AuxiliaryVerb, Colors.Black); case "aux-adj": return new VocabCategoryInfo(R.VocabCat_AuxiliaryAdjective, Colors.Black); case "Buddh": return new VocabCategoryInfo(R.VocabCat_BuddhistTerm, Colors.Black); case "chem": return new VocabCategoryInfo(R.VocabCat_Chemistry, Colors.Black); case "chn": return new VocabCategoryInfo(R.VocabCat_Children, Colors.Black); case "col": return new VocabCategoryInfo(R.VocabCat_Colloquialism, Colors.Black); case "comp": return new VocabCategoryInfo(R.VocabCat_ComputerTerminology, Colors.Black); case "conj": return new VocabCategoryInfo(R.VocabCat_Conjunction, Colors.Black); case "ctr": return new VocabCategoryInfo(R.VocabCat_Counter, Colors.Black); case "derog": return new VocabCategoryInfo(R.VocabCat_Derogatory, Colors.Black); case "eK": return new VocabCategoryInfo(R.VocabCat_ExclusivelyKanji, Colors.Black); case "ek": return new VocabCategoryInfo(R.VocabCat_ExclusivelyKana, Colors.Black); case "exp": return new VocabCategoryInfo(R.VocabCat_Expression, Colors.Black); case "fam": return new VocabCategoryInfo(R.VocabCat_Familiar, Colors.Black); case "fem": return new VocabCategoryInfo(R.VocabCat_Female, Colors.Black); case "food": return new VocabCategoryInfo(R.VocabCat_Food, Colors.Black); case "geom": return new VocabCategoryInfo(R.VocabCat_Geometry, Colors.Black); case "gikun": return new VocabCategoryInfo(R.VocabCat_Gikun, Colors.Black); case "hon": return new VocabCategoryInfo(R.VocabCat_Honorific, Colors.Black); case "hum": return new VocabCategoryInfo(R.VocabCat_Humble, Colors.Black); case "iK": return new VocabCategoryInfo(R.VocabCat_IrregularKanjiUsage, Colors.Black); case "id": return new VocabCategoryInfo(R.VocabCat_Idiomatic, Colors.Black); case "ik": return new VocabCategoryInfo(R.VocabCat_IrregularKanaUsage, Colors.Black); case "int": return new VocabCategoryInfo(R.VocabCat_Interjection, Colors.Black); case "io": return new VocabCategoryInfo(R.VocabCat_IrregularOkuriganaUsage, Colors.Black); case "iv": return new VocabCategoryInfo(R.VocabCat_IrregularVerb, Colors.Black); case "ling": return new VocabCategoryInfo(R.VocabCat_Linguistics, Colors.Black); case "m-sl": return new VocabCategoryInfo(R.VocabCat_MangaSlang, Colors.Black); case "male": return new VocabCategoryInfo(R.VocabCat_Male, Colors.Black); case "male-sl": return new VocabCategoryInfo(R.VocabCat_MaleSlang, Colors.Black); case "math": return new VocabCategoryInfo(R.VocabCat_Mathematics, Colors.Black); case "mil": return new VocabCategoryInfo(R.VocabCat_Military, Colors.Black); case "n": return new VocabCategoryInfo(R.VocabCat_Noun, Colors.Black); case "n-adv": return new VocabCategoryInfo(R.VocabCat_AdverbialNoun, Colors.Black); case "n-suf": return new VocabCategoryInfo(R.VocabCat_SuffixNoun, Colors.Black); case "n-pref": return new VocabCategoryInfo(R.VocabCat_PrefixNoun, Colors.Black); case "n-t": return new VocabCategoryInfo(R.VocabCat_TemporalNoun, Colors.Black); case "num": return new VocabCategoryInfo(R.VocabCat_Numeric, Colors.Black); case "oK": return new VocabCategoryInfo(R.VocabCat_OutdatedKanji, Colors.Black); case "obs": return new VocabCategoryInfo(R.VocabCat_Obsolete, Colors.Black); case "obsc": return new VocabCategoryInfo(R.VocabCat_Obscure, Colors.Black); case "ok": return new VocabCategoryInfo(R.VocabCat_OutdatedKana, Colors.Black); case "oik": return new VocabCategoryInfo(R.VocabCat_OldOrIrregularKana, Colors.Black); case "on-mim": return new VocabCategoryInfo(R.VocabCat_Onomatopoeic, Colors.Black); case "pn": return new VocabCategoryInfo(R.VocabCat_Pronoun, Colors.Black); case "poet": return new VocabCategoryInfo(R.VocabCat_Poetical, Colors.Black); case "pol": return new VocabCategoryInfo(R.VocabCat_Polite, Colors.Black); case "pref": return new VocabCategoryInfo(R.VocabCat_Prefix, Colors.Black); case "proverb": return new VocabCategoryInfo(R.VocabCat_Proverb, Colors.Black); case "prt": return new VocabCategoryInfo(R.VocabCat_Particle, Colors.Black); case "physics": return new VocabCategoryInfo(R.VocabCat_Physics, Colors.Black); case "rare": return new VocabCategoryInfo(R.VocabCat_Rare, Colors.Black); case "sens": return new VocabCategoryInfo(R.VocabCat_Sensitive, Colors.Black); case "sl": return new VocabCategoryInfo(R.VocabCat_Slang, Colors.Black); case "suf": return new VocabCategoryInfo(R.VocabCat_Suffix, Colors.Black); case "uK": return new VocabCategoryInfo(R.VocabCat_UsuallyKanji, Colors.Black); case "uk": return new VocabCategoryInfo(R.VocabCat_UsuallyKana, Colors.Black); case "v1": return new VocabCategoryInfo(R.VocabCat_IchidanVerb, Colors.Black); case "v2a-s": return new VocabCategoryInfo(R.VocabCat_NidanVerbU, Colors.Black); case "v4h": return new VocabCategoryInfo(R.VocabCat_YodanVerbFu, Colors.Black); case "v4r": return new VocabCategoryInfo(R.VocabCat_YodanVerbRu, Colors.Black); case "v5": return new VocabCategoryInfo(R.VocabCat_GodanVerb, Colors.Black); case "v5aru": return new VocabCategoryInfo(R.VocabCat_GodanVerbAru, Colors.Black); case "v5b": return new VocabCategoryInfo(R.VocabCat_GodanVerbBu, Colors.Black); case "v5g": return new VocabCategoryInfo(R.VocabCat_GodanVerbGu, Colors.Black); case "v5k": return new VocabCategoryInfo(R.VocabCat_GodanVerbKu, Colors.Black); case "v5k-s": return new VocabCategoryInfo(R.VocabCat_GodanVerbIku, Colors.Black); case "v5m": return new VocabCategoryInfo(R.VocabCat_GodanVerbMu, Colors.Black); case "v5n": return new VocabCategoryInfo(R.VocabCat_GodanVerbNu, Colors.Black); case "v5r": return new VocabCategoryInfo(R.VocabCat_GodanVerbRu, Colors.Black); case "v5r-i": return new VocabCategoryInfo(R.VocabCat_GodanVerbRuIrregular, Colors.Black); case "v5s": return new VocabCategoryInfo(R.VocabCat_GodanVerbSu, Colors.Black); case "v5t": return new VocabCategoryInfo(R.VocabCat_GodanVerbTsu, Colors.Black); case "v5u": return new VocabCategoryInfo(R.VocabCat_GodanVerbU, Colors.Black); case "v5u-s": return new VocabCategoryInfo(R.VocabCat_GodanVerbUIrregular, Colors.Black); case "v5uru": return new VocabCategoryInfo(R.VocabCat_GodanVerbUru, Colors.Black); case "vz": return new VocabCategoryInfo(R.VocabCat_IchidanVerbZuru, Colors.Black); case "vi": return new VocabCategoryInfo(R.VocabCat_IntransitiveVerb, Colors.Black); case "vk": return new VocabCategoryInfo(R.VocabCat_KuruVerb, Colors.Black); case "vn": return new VocabCategoryInfo(R.VocabCat_NuVerb, Colors.Black); case "vr": return new VocabCategoryInfo(R.VocabCat_RuVerb, Colors.Black); case "vs": return new VocabCategoryInfo(R.VocabCat_SuruVerbNoun, Colors.Black); case "vs-c": return new VocabCategoryInfo(R.VocabCat_SuVerb, Colors.Black); case "vs-s": return new VocabCategoryInfo(R.VocabCat_SuruVerb, Colors.Black); case "vs-i": return new VocabCategoryInfo(R.VocabCat_SuruVerbIrregular, Colors.Black); case "kyb": return new VocabCategoryInfo(R.VocabCat_KyotoBen, Colors.Black); case "osb": return new VocabCategoryInfo(R.VocabCat_OsakaBen, Colors.Black); case "ksb": return new VocabCategoryInfo(R.VocabCat_KansaiBen, Colors.Black); case "ktb": return new VocabCategoryInfo(R.VocabCat_KantouBen, Colors.Black); case "tsb": return new VocabCategoryInfo(R.VocabCat_TosaBen, Colors.Black); case "thb": return new VocabCategoryInfo(R.VocabCat_TouhokuBen, Colors.Black); case "tsug": return new VocabCategoryInfo(R.VocabCat_TsugaruBen, Colors.Black); case "kyu": return new VocabCategoryInfo(R.VocabCat_KyuushuuBen, Colors.Black); case "rkb": return new VocabCategoryInfo(R.VocabCat_RyuukyuuBen, Colors.Black); case "nab": return new VocabCategoryInfo(R.VocabCat_NaganoBen, Colors.Black); case "hob": return new VocabCategoryInfo(R.VocabCat_HokkaidoBen, Colors.Black); case "vt": return new VocabCategoryInfo(R.VocabCat_TransitiveVerb, Colors.Black); case "vulg": return new VocabCategoryInfo(R.VocabCat_Vulgar, Colors.Black); case "adj-kari": return new VocabCategoryInfo(R.VocabCat_KariAdjective, Colors.Black); case "adj-ku": return new VocabCategoryInfo(R.VocabCat_KuAdjective, Colors.Black); case "adj-shiku": return new VocabCategoryInfo(R.VocabCat_ShikuAdjective, Colors.Black); case "adj-nari": return new VocabCategoryInfo(R.VocabCat_NariAdjective, Colors.Black); case "n-pr": return new VocabCategoryInfo(R.VocabCat_ProperNoun, Colors.Black); case "v-unspec": return new VocabCategoryInfo(R.VocabCat_Verb, Colors.Black); case "v4k": return new VocabCategoryInfo(R.VocabCat_ArchaicVerb, Colors.Black); case "v4g": return new VocabCategoryInfo(R.VocabCat_ArchaicVerb, Colors.Black); case "v4s": return new VocabCategoryInfo(R.VocabCat_ArchaicVerb, Colors.Black); case "v4t": return new VocabCategoryInfo(R.VocabCat_ArchaicVerb, Colors.Black); case "v4n": return new VocabCategoryInfo(R.VocabCat_ArchaicVerb, Colors.Black); case "v4b": return new VocabCategoryInfo(R.VocabCat_ArchaicVerb, Colors.Black); case "v4m": return new VocabCategoryInfo(R.VocabCat_ArchaicVerb, Colors.Black); case "v2k-k": return new VocabCategoryInfo(R.VocabCat_ArchaicVerb, Colors.Black); case "v2g-k": return new VocabCategoryInfo(R.VocabCat_ArchaicVerb, Colors.Black); case "v2t-k": return new VocabCategoryInfo(R.VocabCat_ArchaicVerb, Colors.Black); case "v2d-k": return new VocabCategoryInfo(R.VocabCat_ArchaicVerb, Colors.Black); case "v2h-k": return new VocabCategoryInfo(R.VocabCat_ArchaicVerb, Colors.Black); case "v2b-k": return new VocabCategoryInfo(R.VocabCat_ArchaicVerb, Colors.Black); case "v2m-k": return new VocabCategoryInfo(R.VocabCat_ArchaicVerb, Colors.Black); case "v2y-k": return new VocabCategoryInfo(R.VocabCat_ArchaicVerb, Colors.Black); case "v2r-k": return new VocabCategoryInfo(R.VocabCat_ArchaicVerb, Colors.Black); case "v2k-s": return new VocabCategoryInfo(R.VocabCat_ArchaicVerb, Colors.Black); case "v2g-s": return new VocabCategoryInfo(R.VocabCat_ArchaicVerb, Colors.Black); case "v2s-s": return new VocabCategoryInfo(R.VocabCat_ArchaicVerb, Colors.Black); case "v2z-s": return new VocabCategoryInfo(R.VocabCat_ArchaicVerb, Colors.Black); case "v2t-s": return new VocabCategoryInfo(R.VocabCat_ArchaicVerb, Colors.Black); case "v2d-s": return new VocabCategoryInfo(R.VocabCat_ArchaicVerb, Colors.Black); case "v2n-s": return new VocabCategoryInfo(R.VocabCat_ArchaicVerb, Colors.Black); case "v2h-s": return new VocabCategoryInfo(R.VocabCat_ArchaicVerb, Colors.Black); case "v2b-s": return new VocabCategoryInfo(R.VocabCat_ArchaicVerb, Colors.Black); case "v2m-s": return new VocabCategoryInfo(R.VocabCat_ArchaicVerb, Colors.Black); case "v2y-s": return new VocabCategoryInfo(R.VocabCat_ArchaicVerb, Colors.Black); case "v2r-s": return new VocabCategoryInfo(R.VocabCat_ArchaicVerb, Colors.Black); case "v2w-s": return new VocabCategoryInfo(R.VocabCat_ArchaicVerb, Colors.Black); case "archit": return new VocabCategoryInfo(R.VocabCat_Architecture, Colors.Black); case "anat": return new VocabCategoryInfo(R.VocabCat_Anatomy, Colors.Black); case "astron": return new VocabCategoryInfo(R.VocabCat_Astronomy, Colors.Black); case "baseb": return new VocabCategoryInfo(R.VocabCat_Baseball, Colors.Black); case "biol": return new VocabCategoryInfo(R.VocabCat_Biology, Colors.Black); case "bot": return new VocabCategoryInfo(R.VocabCat_Botany, Colors.Black); case "bus": return new VocabCategoryInfo(R.VocabCat_Business, Colors.Black); case "econ": return new VocabCategoryInfo(R.VocabCat_Economy, Colors.Black); case "engr": return new VocabCategoryInfo(R.VocabCat_Engineering, Colors.Black); case "finc": return new VocabCategoryInfo(R.VocabCat_Finance, Colors.Black); case "geol": return new VocabCategoryInfo(R.VocabCat_Geology, Colors.Black); case "law": return new VocabCategoryInfo(R.VocabCat_Law, Colors.Black); case "med": return new VocabCategoryInfo(R.VocabCat_Medicine, Colors.Black); case "music": return new VocabCategoryInfo(R.VocabCat_Music, Colors.Black); case "Shinto": return new VocabCategoryInfo(R.VocabCat_Shinto, Colors.Black); case "sports": return new VocabCategoryInfo(R.VocabCat_Sports, Colors.Black); case "sumo": return new VocabCategoryInfo(R.VocabCat_Sumo, Colors.Black); case "zool": return new VocabCategoryInfo(R.VocabCat_Zoology, Colors.Black); case "joc": return new VocabCategoryInfo(R.VocabCat_Jocular, Colors.Black); default: return null; } }
/// <summary> /// Builds and returns the vocab filter SQL clauses from the given /// filters. /// </summary> internal string BuildVocabFilterClauses(List <DaoParameter> parameters, KanjiEntity kanji, string readingFilter, string meaningFilter, VocabCategory categoryFilter, int jlptLevel, int wkLevel) { const int minJlptLevel = Levels.MinJlptLevel; const int maxJlptLevel = Levels.MaxJlptLevel; const int minWkLevel = Levels.MinWkLevel; const int maxWkLevel = Levels.MaxWkLevel; string sqlJlptFilter = string.Empty; if (jlptLevel >= minJlptLevel && jlptLevel <= maxJlptLevel) { sqlJlptFilter = string.Format("v.{0}=@jlpt ", SqlHelper.Field_Vocab_JlptLevel); parameters.Add(new DaoParameter("@jlpt", jlptLevel)); } else if (jlptLevel < minJlptLevel) { sqlJlptFilter = string.Format("v.{0} IS NULL ", SqlHelper.Field_Vocab_JlptLevel); } string sqlWkFilter = string.Empty; if (wkLevel >= minWkLevel && wkLevel <= maxWkLevel) { sqlWkFilter = string.Format("v.{0}=@wk ", SqlHelper.Field_Vocab_WaniKaniLevel); parameters.Add(new DaoParameter("@wk", wkLevel)); } else if (wkLevel > maxWkLevel) { sqlWkFilter = string.Format("v.{0} IS NULL ", SqlHelper.Field_Vocab_WaniKaniLevel); } string sqlKanjiFilter = string.Empty; if (kanji != null) { // Build the sql kanji filter clause. // Example with the kanji '達' : // // WHERE v.KanjiWriting LIKE '%達%' sqlKanjiFilter = string.Format("v.{0} LIKE @kanji ", SqlHelper.Field_Vocab_KanjiWriting); parameters.Add(new DaoParameter("@kanji", "%" + kanji.Character + "%")); } string sqlReadingFilter = string.Empty; if (!string.IsNullOrWhiteSpace(readingFilter)) { // Build the sql reading filter clause. // Example with readingFilter="かな" : // // WHERE v.KanaWriting LIKE '%かな%' OR // v.KanjiWriting LIKE '%かな%' sqlReadingFilter = string.Format("(v.{0} LIKE @reading OR v.{1} LIKE @reading) ", SqlHelper.Field_Vocab_KanaWriting, SqlHelper.Field_Vocab_KanjiWriting); parameters.Add(new DaoParameter("@reading", "%" + readingFilter + "%")); } string sqlSharedJoins = string.Empty; string sqlMeaningFilterJoins = string.Empty; string sqlMeaningFilter = string.Empty; if (!string.IsNullOrWhiteSpace(meaningFilter)) { // Build the sql meaning filter clause and join clauses. // Example of filter clause with meaningFilter="test" : // // WHERE vm.Meaning LIKE '%test%' // First, build the join clause if it does not already exist. This will be included before the filters. if (string.IsNullOrEmpty(sqlSharedJoins)) { sqlSharedJoins = joinString_VocabEntity_VocabMeaning; } sqlMeaningFilterJoins = joinString_VocabMeaningSet; // Once the join clauses are done, build the filter itself. sqlMeaningFilter = string.Format("vm.{0} LIKE @meaning ", SqlHelper.Field_VocabMeaning_Meaning); parameters.Add(new DaoParameter("@meaning", "%" + meaningFilter + "%")); } string sqlCategoryFilterJoins = string.Empty; string sqlCategoryFilter = string.Empty; if (categoryFilter != null) { // Build the filter clause for the vocab category. // Note that the category is actually associated either with the vocab itself or with a MEANING, // so we need to grab any vocab which itself has said category, or of which ONE OF THE MEANINGS // is of said category. // Example of filter clause with category.ID=42 : // // WHERE vc.Categories_ID=42 OR mc.Categories_ID=42 if (string.IsNullOrEmpty(sqlSharedJoins)) { sqlSharedJoins = joinString_VocabEntity_VocabMeaning; } /* TODO: Currently, only the meanings are checked for category matches, not vocab items themselves. * This is because of several reasons: * * 1) In the current database, not a single vocab has a category attached. * Only meanings do. * 2) Saving some performance by not doing a check that does not do anything. * * This does mean that this query must be changed if vocab items ever * get a category attached to them. * * Actually, scratch that. Ateji DO have a category attached to the vocab themselves. */ string subQuery = string.Format("(SELECT m.{0} FROM {1} m WHERE m.{2}=@cat)", SqlHelper.Field_VocabMeaning_VocabCategory_VocabMeaningId, SqlHelper.Table_VocabMeaning_VocabCategory, SqlHelper.Field_VocabMeaning_VocabCategory_VocabCategoryId); // First, build the join clause if it does not already exist. This will be included before the filters. sqlCategoryFilterJoins = string.IsNullOrEmpty(sqlMeaningFilterJoins) ? joinString_VocabMeaningSet : null; // Once the join clauses are done, build the filter itself. sqlCategoryFilter = string.Format("vm.{0} IN {1} ", SqlHelper.Field_VocabMeaning_Id, subQuery); parameters.Add(new DaoParameter("@cat", categoryFilter.ID)); } string[] sqlArgs = { sqlSharedJoins, sqlMeaningFilterJoins, sqlCategoryFilterJoins, sqlJlptFilter, sqlWkFilter, sqlKanjiFilter, sqlReadingFilter, sqlMeaningFilter, sqlCategoryFilter }; bool isFiltered = false; for (int i = 0; i < sqlArgs.Length; i++) { string arg = sqlArgs[i]; if (string.IsNullOrEmpty(arg) || arg.StartsWith("JOIN")) { continue; } sqlArgs[i] = (isFiltered ? "AND " : "WHERE ") + arg; isFiltered = true; } return(string.Concat(sqlArgs)); }
/// <summary> /// See <see cref="Kanji.Database.Dao.VocabDao.GetFilteredVocab"/>. /// Returns the results count. /// </summary> public long GetFilteredVocabCount(KanjiEntity kanji, string readingFilter, string meaningFilter, VocabCategory categoryFilter, int jlptLevel, int wkLevel) { List <DaoParameter> parameters = new List <DaoParameter>(); string sqlFilterClauses = BuildVocabFilterClauses(parameters, kanji, readingFilter, meaningFilter, categoryFilter, jlptLevel, wkLevel); using (DaoConnection connection = DaoConnection.Open(DaoConnectionEnum.KanjiDatabase)) { return((long)connection.QueryScalar( string.Format("SELECT count(1) FROM {0} v {1}", SqlHelper.Table_Vocab, sqlFilterClauses), parameters.ToArray())); } }
/// <summary> /// Retrieves and returns the collection of vocab matching the /// given filters. /// </summary> /// <param name="kanji">Kanji filter. Only vocab containing this /// kanji will be filtered in.</param> /// <param name="readingFilter">Reading filter. Only vocab containing /// this string in their kana or kanji reading will be filtered in.</param> /// <param name="meaningFilter">Meaning filter. Only vocab containing /// this string as part of at least one of their meaning entries will /// be filtered in.</param> /// <param name="categoryFilter">If not null, this category is used as the filter.</param> /// <param name="jlptLevel">The JLPT level to filter /// (1-5, where a lower value means it is not covered on the JLPT /// and a higher value means that this filter will be ignored).</param> /// <param name="wkLevel">The WaniKani level to filter /// (1-60, where a higher value means it is not taught by WaniKani /// and a lower value means that this filter will be ignored).</param> /// <param name="isCommonFirst">Indicates if common vocab should be /// presented first. If false, results are sorted only by the length /// of their writing (asc or desc depending on the parameter)</param> /// <param name="isShortWritingFirst">Indicates if results should /// be sorted by ascending or descending writing length. /// If True, short readings come first. If False, long readings /// come first.</param> /// <returns>Vocab entities matching the filters.</returns> public IEnumerable <VocabEntity> GetFilteredVocab(KanjiEntity kanji, string readingFilter, string meaningFilter, VocabCategory categoryFilter, int jlptLevel, int wkLevel, bool isCommonFirst, bool isShortWritingFirst) { List <DaoParameter> parameters = new List <DaoParameter>(); string sqlFilterClauses = BuildVocabFilterClauses(parameters, kanji, readingFilter, meaningFilter, categoryFilter, jlptLevel, wkLevel); string sortClause = "ORDER BY "; if (isCommonFirst) { sortClause += string.Format("v.{0} DESC,", SqlHelper.Field_Vocab_IsCommon); } sortClause += string.Format("length(v.{0}) {1}", SqlHelper.Field_Vocab_KanaWriting, (isShortWritingFirst ? "ASC" : "DESC")); DaoConnection connection = null; DaoConnection srsConnection = null; try { connection = DaoConnection.Open(DaoConnectionEnum.KanjiDatabase); srsConnection = new DaoConnection(DaoConnectionEnum.SrsDatabase); srsConnection.OpenAsync(); IEnumerable <NameValueCollection> vocabs = connection.Query( string.Format("SELECT DISTINCT v.* FROM {0} v {1}{2}", SqlHelper.Table_Vocab, sqlFilterClauses, sortClause), parameters.ToArray()); VocabBuilder vocabBuilder = new VocabBuilder(); foreach (NameValueCollection nvcVocab in vocabs) { VocabEntity vocab = vocabBuilder.BuildEntity(nvcVocab, null); IncludeCategories(connection, vocab); IncludeMeanings(connection, vocab); IncludeKanji(connection, srsConnection, vocab); IncludeSrsEntries(srsConnection, vocab); IncludeVariants(connection, vocab); yield return(vocab); } } finally { if (connection != null) { connection.Dispose(); } } }
/// <summary> /// Builds and returns the vocab filter SQL clauses from the given /// filters. /// </summary> internal string BuildVocabFilterClauses(List <DaoParameter> parameters, KanjiEntity kanji, string readingFilter, string meaningFilter, VocabCategory categoryFilter, int jlptLevel, int wkLevel) { const int minJlptLevel = Levels.MinJlptLevel; const int maxJlptLevel = Levels.MaxJlptLevel; const int minWkLevel = Levels.MinWkLevel; const int maxWkLevel = Levels.MaxWkLevel; string sqlJlptFilter = string.Empty; if (jlptLevel >= minJlptLevel && jlptLevel <= maxJlptLevel) { sqlJlptFilter = string.Format("v.{0}=@jlpt ", SqlHelper.Field_Vocab_JlptLevel); parameters.Add(new DaoParameter("@jlpt", jlptLevel)); } else if (jlptLevel < minJlptLevel) { sqlJlptFilter = string.Format("v.{0} IS NULL ", SqlHelper.Field_Vocab_JlptLevel); } string sqlWkFilter = string.Empty; if (wkLevel >= minWkLevel && wkLevel <= maxWkLevel) { sqlWkFilter = string.Format("v.{0}=@wk ", SqlHelper.Field_Vocab_WaniKaniLevel); parameters.Add(new DaoParameter("@wk", wkLevel)); } else if (wkLevel > maxWkLevel) { sqlWkFilter = string.Format("v.{0} IS NULL ", SqlHelper.Field_Vocab_WaniKaniLevel); } string sqlKanjiFilter = string.Empty; if (kanji != null) { // Build the sql kanji filter clause. // Example with the kanji '達' : // // WHERE v.KanjiWriting LIKE '%達%' sqlKanjiFilter = string.Format("v.{0} LIKE @kanji ", SqlHelper.Field_Vocab_KanjiWriting); parameters.Add(new DaoParameter("@kanji", "%" + kanji.Character + "%")); } string sqlReadingFilter = string.Empty; if (!string.IsNullOrWhiteSpace(readingFilter)) { // Build the sql reading filter clause. // Example with readingFilter="かな" : // // WHERE v.KanaWriting LIKE '%かな%' OR // v.KanjiWriting LIKE '%かな%' sqlReadingFilter = string.Format("(v.{0} LIKE @reading OR v.{1} LIKE @reading) ", SqlHelper.Field_Vocab_KanaWriting, SqlHelper.Field_Vocab_KanjiWriting); parameters.Add(new DaoParameter("@reading", "%" + readingFilter + "%")); } string sqlSharedJoins = string.Empty; string sqlMeaningFilterJoins = string.Empty; string sqlMeaningFilter = string.Empty; if (!string.IsNullOrWhiteSpace(meaningFilter)) { // Build the sql meaning filter clause and join clauses. // Example of filter clause with meaningFilter="test" : // // WHERE vm.Meaning LIKE '%test%' // First, build the join clause. This will be included before the filters. if (string.IsNullOrEmpty(sqlSharedJoins)) { sqlSharedJoins = string.Format("JOIN {0} vvm ON (vvm.{1}=v.{2}) ", SqlHelper.Table_Vocab_VocabMeaning, SqlHelper.Field_Vocab_VocabMeaning_VocabId, SqlHelper.Field_Vocab_Id); } sqlMeaningFilterJoins = string.Format("JOIN {0} vm ON (vm.{1}=vvm.{2}) ", SqlHelper.Table_VocabMeaning, SqlHelper.Field_VocabMeaning_Id, SqlHelper.Field_Vocab_VocabMeaning_VocabMeaningId); // Ouch... it looks kinda like an obfuscated string... Sorry. // Basically, you just join the vocab to its meaning entries. // Once the join clauses are done, build the filter itself. sqlMeaningFilter = string.Format("vm.{0} LIKE @meaning ", SqlHelper.Field_VocabMeaning_Meaning); parameters.Add(new DaoParameter("@meaning", "%" + meaningFilter + "%")); } string sqlCategoryFilterJoins = string.Empty; string sqlCategoryFilter = string.Empty; if (categoryFilter != null) { // Build the filter clause for the vocab category. // Note that the category is actually associated either with the vocab itself or with a MEANING, // so we need to grab any vocab which itself has said category, or of which ONE OF THE MEANINGS // is of said category. // Example of filter clause with category.ID=42 : // // WHERE vc.Categories_ID=42 OR mc.Categories_ID=42 if (string.IsNullOrEmpty(sqlSharedJoins)) { sqlSharedJoins = string.Format("JOIN {0} vvm ON (vvm.{1}=v.{2}) ", SqlHelper.Table_Vocab_VocabMeaning, SqlHelper.Field_Vocab_VocabMeaning_VocabId, SqlHelper.Field_Vocab_Id); } sqlCategoryFilterJoins = string.Format( "JOIN {0} vc ON (vc.{1}=v.{2}) JOIN {3} mc ON (mc.{4}=vvm.{5}) ", SqlHelper.Table_VocabCategory_Vocab, SqlHelper.Field_VocabCategory_Vocab_VocabId, SqlHelper.Field_VocabCategory_Id, SqlHelper.Table_VocabMeaning_VocabCategory, SqlHelper.Field_VocabMeaning_VocabCategory_VocabMeaningId, SqlHelper.Field_Vocab_VocabMeaning_VocabMeaningId); sqlCategoryFilter = string.Format("(vc.{0}=@cat OR mc.{1}=@cat) ", SqlHelper.Field_VocabCategory_Vocab_VocabCategoryId, SqlHelper.Field_VocabMeaning_VocabCategory_VocabCategoryId); parameters.Add(new DaoParameter("@cat", categoryFilter.ID)); } string[] sqlArgs = { sqlSharedJoins, sqlMeaningFilterJoins, sqlCategoryFilterJoins, sqlJlptFilter, sqlWkFilter, sqlKanjiFilter, sqlReadingFilter, sqlMeaningFilter, sqlCategoryFilter }; bool isFiltered = false; for (int i = 0; i < sqlArgs.Length; i++) { string arg = sqlArgs[i]; if (string.IsNullOrEmpty(arg) || arg.StartsWith("JOIN")) { continue; } sqlArgs[i] = (isFiltered ? "AND " : "WHERE ") + arg; isFiltered = true; } return(string.Concat(sqlArgs)); }