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;
        }
Beispiel #2
0
        /// <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);
            }
        }
Beispiel #3
0
        /// <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;
            }
        }
Beispiel #5
0
        /// <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));
        }
Beispiel #6
0
        /// <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()));
            }
        }
Beispiel #7
0
        /// <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();
                }
            }
        }
Beispiel #8
0
        /// <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));
        }