Пример #1
0
        private BoxSizes GetCurrentBoxContent()
        {
            BoxSizes sizes;

            using (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser))
            {
                sizes           = new BoxSizes(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0);
                cmd.CommandText = @"SELECT CS.box AS box, count(*) AS count FROM UserCardState CS
                                        INNER JOIN Cards C ON C.id = CS.cards_id AND C.lm_id=@lm_id
                                        WHERE CS.active = 1 AND CS.user_id = @user_id  
                                      GROUP BY CS.box";
                cmd.Parameters.Add("@user_id", Parent.CurrentUser.Id);
                cmd.Parameters.Add("@lm_id", Parent.CurrentUser.ConnectionString.LmId);

                SqlCeDataReader reader = MSSQLCEConn.ExecuteReader(cmd);
                while (reader.Read())
                {
                    sizes.Sizes[Convert.ToInt32(reader["box"])] = Convert.ToInt32(reader["count"]);
                }
                reader.Close();

                return(sizes);
            }
        }
        /// <summary>
        /// Gets the words.
        /// </summary>
        /// <param name="cardid">The cardid.</param>
        /// <param name="side">The side.</param>
        /// <param name="type">The type.</param>
        /// <returns></returns>
        /// <remarks>Documented by Dev03, 2009-01-09</remarks>
        private IList <IWord> GetWords(int cardid, Side side, WordType type)
        {
            CacheObject obj = ObjectLifetimeIdentifier.GetCacheObject(side, type);

            IList <IWord> wordsCache = Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.GetIdentifier(obj, cardid)] as IList <IWord>;

            if (wordsCache != null)
            {
                return(wordsCache);
            }

            IList <IWord> question           = new List <IWord>();
            IList <IWord> questionExample    = new List <IWord>();
            IList <IWord> questionDistractor = new List <IWord>();
            IList <IWord> answer             = new List <IWord>();
            IList <IWord> answerExample      = new List <IWord>();
            IList <IWord> answerDistractor   = new List <IWord>();

            SqlCeCommand cmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser);

            cmd.CommandText = "SELECT id, side, type, text, is_default FROM TextContent WHERE cards_id=@id ORDER BY position ASC;";
            cmd.Parameters.Add("@id", cardid);
            SqlCeDataReader reader = MSSQLCEConn.ExecuteReader(cmd);

            while (reader.Read())
            {
                object[] word = new object[5];
                reader.GetValues(word);
                int      id        = Convert.ToInt32(word[0]);
                Side     wside     = (Side)Enum.Parse(typeof(Side), word[1].ToString(), true);
                WordType wtype     = (WordType)Enum.Parse(typeof(WordType), word[2].ToString(), false);
                string   text      = word[3].ToString();
                bool     isDefault = Convert.ToBoolean(word[4]);

                switch (wside)
                {
                case Side.Question:
                    switch (wtype)
                    {
                    case WordType.Word:
                        question.Add(new DbWord(id, text, wtype, isDefault, Parent));
                        break;

                    case WordType.Sentence:
                        questionExample.Add(new DbWord(id, text, wtype, isDefault, Parent));
                        break;

                    case WordType.Distractor:
                        questionDistractor.Add(new DbWord(id, text, wtype, isDefault, Parent));
                        break;
                    }
                    break;

                case Side.Answer:
                    switch (wtype)
                    {
                    case WordType.Word:
                        answer.Add(new DbWord(id, text, wtype, isDefault, Parent));
                        break;

                    case WordType.Sentence:
                        answerExample.Add(new DbWord(id, text, wtype, isDefault, Parent));
                        break;

                    case WordType.Distractor:
                        answerDistractor.Add(new DbWord(id, text, wtype, isDefault, Parent));
                        break;
                    }
                    break;
                }
            }
            reader.Close();

            Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.QuestionWords, cardid)]           = question;
            Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.QuestionExampleWords, cardid)]    = questionExample;
            Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.QuestionDistractorWords, cardid)] = questionDistractor;
            Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.AnswerWords, cardid)]             = answer;
            Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.AnswerExampleWords, cardid)]      = answerExample;
            Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.AnswerDistractorWords, cardid)]   = answerDistractor;

            switch (side)
            {
            case Side.Question:
                switch (type)
                {
                case WordType.Word:
                    return(question);

                case WordType.Sentence:
                    return(questionExample);

                case WordType.Distractor:
                    return(questionDistractor);
                }
                break;

            case Side.Answer:
                switch (type)
                {
                case WordType.Word:
                    return(answer);

                case WordType.Sentence:
                    return(answerExample);

                case WordType.Distractor:
                    return(answerDistractor);
                }
                break;
            }

            return(new List <IWord>());
        }
        /// <summary>
        /// Adds the words.
        /// </summary>
        /// <param name="id">The id.</param>
        /// <param name="side">The side.</param>
        /// <param name="type">The type.</param>
        /// <param name="words">The words.</param>
        /// <remarks>Documented by Dev03, 2009-01-09</remarks>
        /// <remarks>Documented by Dev08, 2009-01-19</remarks>
        public void AddWords(int id, Side side, WordType type, List <IWord> words)
        {
            if (words.Count > 0)
            {
                //SqlCeCommand cmd1 = MSSQLCEConn.CreateCommand(Parent.CurrentUser);
                //SqlCeCommand cmd2 = MSSQLCEConn.CreateCommand(Parent.CurrentUser);
                //SqlCeCommand cmd3 = MSSQLCEConn.CreateCommand(Parent.CurrentUser);

                //SqlCeParameter paramWordId = new SqlCeParameter("@id", SqlDbType.Int);
                //SqlCeParameter paramCardId = new SqlCeParameter("@cardid", SqlDbType.Int);
                //SqlCeParameter paramType = new SqlCeParameter("@type", SqlDbType.NVarChar);
                //SqlCeParameter paramIsDefault = new SqlCeParameter("@isdefault", SqlDbType.Bit);
                //cmd1.CommandText = "SELECT count(*) FROM TextContent WHERE id = @wordid AND text = @word AND type = @type AND is_default = @isdefault;";
                //cmd1.Parameters.Add(paramWordId);
                //cmd1.Parameters.Add(paramCardId);
                //cmd1.Parameters.Add(paramType);
                //cmd1.Parameters.Add(paramIsDefault);

                //SqlCeParameter paramSide = new SqlCeParameter("@side", SqlDbType.NVarChar);
                //cmd2.CommandText = "SELECT position FROM TextContent WHERE cards_id=@id AND side=@side AND type=@type ORDER BY position DESC";
                //cmd2.Parameters.Add(paramWordId);
                //cmd2.Parameters.Add(paramSide);
                //cmd2.Parameters.Add(paramType);

                //SqlCeParameter paramText = new SqlCeParameter("@text", SqlDbType.NText);
                //SqlCeParameter paramPosition = new SqlCeParameter("@position", SqlDbType.Int);
                //cmd3.CommandText = @"INSERT INTO TextContent (cards_id, text, side, type, position, is_default) VALUES (@id, @text, @:side, @type, @position, @isdefault); SELECT @@IDENTITY;";
                //cmd3.Parameters.Add(paramCardId);
                //cmd3.Parameters.Add(paramText);
                //cmd3.Parameters.Add(paramSide);
                //cmd3.Parameters.Add(paramType);
                //cmd3.Parameters.Add(paramPosition);
                //cmd3.Parameters.Add(paramIsDefault);

                foreach (IWord word in words)
                {
                    if (word != null && word.Word.Length > 0)
                    {
                        //paramCardId.Value = id;
                        //paramWordId.Value = word.Id;
                        //paramText.Value = word.Word;
                        //paramSide.Value = side.ToString();
                        //paramType.Value = type.ToString();
                        //paramIsDefault.Value = word.Default;

                        SqlCeCommand cmd1 = MSSQLCEConn.CreateCommand(Parent.CurrentUser);
                        cmd1.CommandText = "SELECT count(*) FROM TextContent WHERE id = @wordid AND text = @word AND type = @type AND is_default = @isdefault;";
                        cmd1.Parameters.Add("@wordid", word.Id);
                        cmd1.Parameters.Add("@word", word.Word);
                        cmd1.Parameters.Add("@type", type.ToString());
                        cmd1.Parameters.Add("@isdefault", word.Default);

                        bool wordExists = (Convert.ToInt32(MSSQLCEConn.ExecuteScalar(cmd1)) > 0);
                        if (!wordExists)
                        {
                            int          currentPos = 0;
                            SqlCeCommand cmd2       = MSSQLCEConn.CreateCommand(Parent.CurrentUser);
                            cmd2.CommandText = "SELECT position FROM TextContent WHERE cards_id=@id AND side=@side AND type=@type ORDER BY position DESC";
                            cmd2.Parameters.Add("@id", id);
                            cmd2.Parameters.Add("@side", side.ToString());
                            cmd2.Parameters.Add("@type", type.ToString());

                            object retval = MSSQLCEConn.ExecuteScalar(cmd2);
                            if (retval != DBNull.Value)
                            {
                                currentPos = Convert.ToInt32(retval);
                            }

                            //paramPosition.Value = currentPos + 10;
                            SqlCeCommand cmd3 = MSSQLCEConn.CreateCommand(Parent.CurrentUser);
                            cmd3.CommandText = "INSERT INTO TextContent (cards_id, text, side, type, position, is_default) VALUES (@id, @word, @side, @type, @position, @isdefault); SELECT @@IDENTITY;";
                            cmd3.Parameters.Add("@id", id);
                            cmd3.Parameters.Add("@word", word.Word);
                            cmd3.Parameters.Add("@side", side.ToString());
                            cmd3.Parameters.Add("@type", type.ToString());
                            cmd3.Parameters.Add("@position", currentPos + 10);
                            cmd3.Parameters.Add("@isdefault", word.Default);
                            MSSQLCEConn.ExecuteNonQuery(cmd3);
                        }
                    }
                }

                Parent.CurrentUser.Cache.Uncache(ObjectLifetimeIdentifier.GetIdentifier(ObjectLifetimeIdentifier.GetCacheObject(side, type), id));
            }
        }
Пример #4
0
        public void PreloadCardCache(int id)
        {
            if (parent.CurrentUser.Cache[ObjectLifetimeIdentifier.GetIdentifier(CacheObject.CardCacheInitialized, id)] != null)
            {
                return;
            }

            using (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(parent.CurrentUser))
                using (SqlCeCommand cmd2 = MSSQLCEConn.CreateCommand(parent.CurrentUser))
                    using (SqlCeCommand cmd3 = MSSQLCEConn.CreateCommand(parent.CurrentUser))
                    {
                        cmd.CommandText = "SELECT C.id, C.chapters_id, CS.box, CS.active, CS.[timestamp] FROM Cards C INNER JOIN UserCardState CS ON C.id = CS.cards_id WHERE C.lm_id = @lm_id AND  CS.user_id = @user_id ORDER BY C.id";
                        cmd.Parameters.Add("@user_id", parent.CurrentUser.Id);
                        cmd.Parameters.Add("@lm_id", id);
                        cmd2.CommandText = "SELECT id, title FROM Chapters WHERE id IN (SELECT chapters_id FROM Cards WHERE lm_id = @lm_id)";
                        cmd2.Parameters.Add("@lm_id", id);
                        cmd3.CommandText = "SELECT C.id, T.text, T.side FROM Cards C INNER JOIN TextContent T ON C.id = T.cards_id WHERE C.lm_id = @lm_id AND T.type = 'Word' ORDER BY C.id, T.side, T.position;";
                        cmd3.Parameters.Add("@lm_id", id);

                        List <object[]> cards = new List <object[]>();
                        using (SqlCeDataReader cardReader = MSSQLCEConn.ExecuteReader(cmd))
                        {
                            while (cardReader.Read())
                            {
                                object[] row = new object[5];
                                cardReader.GetValues(row);
                                cards.Add(row);
                            }
                        }
                        List <object[]> chapters = new List <object[]>();
                        using (SqlCeDataReader chapterReader = MSSQLCEConn.ExecuteReader(cmd2))
                        {
                            while (chapterReader.Read())
                            {
                                object[] row = new object[2];
                                chapterReader.GetValues(row);
                                chapters.Add(row);
                            }
                        }
                        List <object[]> allWords = new List <object[]>();
                        using (SqlCeDataReader textReader = MSSQLCEConn.ExecuteReader(cmd3))
                        {
                            while (textReader.Read())
                            {
                                object[] row = new object[3];
                                textReader.GetValues(row);
                                allWords.Add(row);
                            }
                        }

                        int pos = 0;
                        foreach (object[] card in cards)
                        {
                            int      cardId    = Convert.ToInt32(card[0]);
                            int      chapterId = Convert.ToInt32(card[1]);
                            object[] chapter   = chapters.Find(c => Convert.ToInt32(c[0]) == chapterId);
                            if (chapter != null)
                            {
                                chapters.Remove(chapter);
                            }
                            List <object[]> words = new List <object[]>();
                            if (allWords.Count > pos && Convert.ToInt32(allWords[pos][0]) == cardId)
                            {
                                while (allWords.Count > pos && Convert.ToInt32(allWords[pos][0]) == cardId)
                                {
                                    words.Add(allWords[pos]);
                                    pos++;
                                }
                            }
                            IList <IWord> question = new List <IWord>();
                            IList <IWord> answer   = new List <IWord>();
                            foreach (object[] word in words)
                            {
                                // Pull the question word or answer word from this row
                                String side = word[2].ToString();
                                if (side == Side.Question.ToString())
                                {
                                    question.Add(new DbWord(cardId, word[1].ToString(), WordType.Word, true, parent));
                                }
                                else
                                {
                                    answer.Add(new DbWord(cardId, word[1].ToString(), WordType.Word, true, parent));
                                }
                            }
                            parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.QuestionWords, cardId)] = question;
                            parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.AnswerWords, cardId)]   = answer;
                            parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.CardState, cardId)]     = new CardState(Convert.ToInt32(card[2]),
                                                                                                                                         Convert.ToBoolean(card[3]), (card[4] is DBNull) ? new DateTime(1901, 1, 1) : Convert.ToDateTime(card[4])); // box, active, timestamp
                            if (chapter != null)
                            {
                                parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.ChapterTitle, Convert.ToInt32(chapter[0]))] = chapter[1].ToString();
                            }
                        }
                    }
            parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.CardCacheInitialized, id)] = true;
        }
Пример #5
0
        /// <summary>
        /// Gets the score.
        /// </summary>
        /// <param name="id">The id.</param>
        /// <returns></returns>
        /// <remarks>Documented by Dev08, 2009-01-13</remarks>
        /// <remarks>Documented by Dev04, 2009-04-10</remarks>
        /// <remarks>Documented by Dev08, 2009-04-20</remarks>
        public double GetScore(int id)
        {
            //double? score = parent.CurrentUser.Cache[ObjectLifetimeIdentifier.GetIdentifier(CacheObject.Score, id)] as double?;
            //if (score != null && score.HasValue)
            //    return score.Value;

            using (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(parent.CurrentUser))
            {
                //cmd.CommandText = "SELECT SUM(box) FROM \"UserCardState\" WHERE user_id=@param_user_id and cards_id IN (SELECT cards_id FROM \"LearningModules_Cards\" WHERE lm_id=@param_lm_id)";

                double[] factor = new double[11];
                factor[0] = 0;
                for (int i = 1; i < factor.Length; i++)
                {
                    factor[i] = factor[i - 1] + 1.0 / i;
                }

                cmd.CommandText = "SELECT TOP(1) (";
                for (int i = 1; i < factor.Length; i++)
                {
                    cmd.CommandText = cmd.CommandText + "box" + i + "_content * " + factor[i - 1].ToString().Replace(",", ".");
                    if (i != factor.Length - 1)
                    {
                        cmd.CommandText += " + ";
                    }
                }
                cmd.CommandText += ") AS sum, ((pool_content + ";

                for (int i = 1; i < factor.Length; i++)
                {
                    cmd.CommandText += "box" + i + "_content ";
                    if (i != factor.Length - 1)
                    {
                        cmd.CommandText += "+";
                    }
                    else
                    {
                        cmd.CommandText += ")*" + factor[factor.Length - 2].ToString().Replace(",", ".");
                    }
                }

                cmd.CommandText += ") AS total FROM \"LearningSessions\" WHERE (user_id=@param_user_id AND lm_id=@param_lm_id) ORDER BY id DESC";

                cmd.Parameters.Add("@param_user_id", parent.CurrentUser.Id);
                cmd.Parameters.Add("@param_lm_id", id);

                SqlCeDataReader reader = MSSQLCEConn.ExecuteReader(cmd);
                reader.Read();

                double sum, total;
                double?result;
                try
                {
                    sum   = System.Convert.ToDouble(reader["sum"]);
                    total = System.Convert.ToDouble(reader["total"]);

                    result = (double)(sum / total * 100.00);
                }
                catch
                {
                    result = null;
                }

                //parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.Score, id, new TimeSpan(0, 0, 1))] = result;
                return(result.HasValue ? result.Value : 0);
            }
        }
Пример #6
0
        /// <summary>
        /// Gets the user settings.
        /// </summary>
        /// <param name="id">The id.</param>
        /// <returns></returns>
        /// <remarks>Documented by Dev08, 2009-01-12</remarks>
        public ISettings GetUserSettings(int id)
        {
            DbSettings settingsCache = parent.CurrentUser.Cache[ObjectLifetimeIdentifier.GetIdentifier(CacheObject.UserLearningModuleSettings, id)] as DbSettings;

            if (settingsCache != null)
            {
                return(settingsCache);
            }

            int count;

            using (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(parent.CurrentUser))
            {
                cmd.CommandText = "SELECT count(*) FROM \"UserProfilesLearningModulesSettings\" WHERE user_id=@param_user_id and lm_id=@param_lm_id;";
                cmd.Parameters.Add("@param_user_id", parent.CurrentUser.Id);
                cmd.Parameters.Add("@param_lm_id", id);

                count = Convert.ToInt32(MSSQLCEConn.ExecuteScalar(cmd));
            }

            int result;

            if (count < 1)      //Create new settings
            {
                using (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(parent.CurrentUser))
                {
                    cmd.CommandText = "INSERT INTO \"UserProfilesLearningModulesSettings\" (user_id, lm_id, settings_id) VALUES (@param_user_id, @param_lm_id, @param_new_settings);";
                    cmd.Parameters.Add("@param_user_id", parent.CurrentUser.Id);
                    cmd.Parameters.Add("@param_lm_id", id);
                    cmd.Parameters.Add("@param_new_settings", MsSqlCeSettingsConnector.CreateNewSettings(parent));

                    MSSQLCEConn.ExecuteNonQuery(cmd);
                }

                using (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(parent.CurrentUser))
                {
                    cmd.CommandText = "SELECT settings_id FROM \"UserProfilesLearningModulesSettings\" WHERE user_id=@param_user_id and lm_id=@param_lm_id;";
                    cmd.Parameters.Add("@param_user_id", parent.CurrentUser.Id);
                    cmd.Parameters.Add("@param_lm_id", id);

                    result = Convert.ToInt32(MSSQLCEConn.ExecuteScalar(cmd));
                }

                //Selected Learn Chapters
                using (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(parent.CurrentUser))
                {
                    cmd.CommandText = "SELECT id FROM \"Chapters\" WHERE lm_id=@param_lm_id";
                    cmd.Parameters.Add("@param_lm_id", id);

                    SqlCeDataReader reader = MSSQLCEConn.ExecuteReader(cmd);
                    while (reader.Read())
                    {
                        using (SqlCeCommand cmd2 = MSSQLCEConn.CreateCommand(parent.CurrentUser))
                        {
                            cmd2.CommandText = "INSERT INTO \"SelectedLearnChapters\" VALUES (@cid, @result);";
                            cmd2.Parameters.Add("@cid", reader["id"]);
                            cmd2.Parameters.Add("@result", result);

                            MSSQLCEConn.ExecuteNonQuery(cmd2);
                        }
                    }
                    reader.Close();
                }

                DbSettings settings = new DbSettings(result, false, parent);
                parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.UserLearningModuleSettings, id, Cache.DefaultSettingsValidationTime)] = settings;
                return(settings);
            }
            else
            {
                using (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(parent.CurrentUser))
                {
                    cmd.CommandText = "SELECT settings_id FROM \"UserProfilesLearningModulesSettings\" WHERE user_id=@param_user_id and lm_id=@param_lm_id;";
                    cmd.Parameters.Add("@param_user_id", parent.CurrentUser.Id);
                    cmd.Parameters.Add("@param_lm_id", id);

                    result = Convert.ToInt32(MSSQLCEConn.ExecuteScalar(cmd));
                }

                DbSettings settings = new DbSettings(result, false, parent);
                parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.UserLearningModuleSettings, id, Cache.DefaultSettingsValidationTime)] = settings;
                return(settings);
            }
        }
Пример #7
0
        /// <summary>
        /// Gets the database values.
        /// </summary>
        /// <param name="parent">The parent.</param>
        /// <remarks>Documented by Dev08, 2009-01-12</remarks>
        public static void GetDatabaseValues(ParentClass parent)
        {
            using (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(parent.CurrentUser))
            {
                cmd.CommandText = "SELECT * FROM \"DatabaseInformation\"";
                SqlCeDataReader reader = MSSQLCEConn.ExecuteReader(cmd);

                while (reader.Read())
                {
                    switch ((DataBaseInformation)Enum.Parse(typeof(DataBaseInformation), reader["property"].ToString()))
                    {
                    case DataBaseInformation.Version:
                        parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.DataBaseVersion, 0, new TimeSpan(1, 0, 0))] = reader["value"].ToString();
                        break;

                    case DataBaseInformation.SupportedDataLayerVersions:
                        string vrs = reader["value"].ToString();
                        if (vrs.Length > 0)
                        {
                            List <string> versions = new List <string>();
                            versions.AddRange(vrs.Split(new char[] { ',' }));
                            parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.SupportedDataLayerVersions, 0, new TimeSpan(1, 0, 0))] = versions;
                        }
                        break;

                    case DataBaseInformation.ListAuthentication:
                        parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.ListAuthentication, 0, new TimeSpan(1, 0, 0))] = (bool?)Convert.ToBoolean(reader["value"]);
                        break;

                    case DataBaseInformation.FormsAuthentication:
                        parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.FormsAuthentication, 0, new TimeSpan(1, 0, 0))] = (bool?)Convert.ToBoolean(reader["value"]);
                        break;

                    case DataBaseInformation.LocalDirectoryAuthentication:
                        parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.LocalDirectoryAuthentication, 0, new TimeSpan(1, 0, 0))] = (bool?)Convert.ToBoolean(reader["value"]);
                        break;

                    case DataBaseInformation.LocalDirectoryType:
                        try
                        {
                            parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.LocalDirectoryType, 0, new TimeSpan(1, 0, 0))] =
                                (LocalDirectoryType)Enum.Parse(typeof(LocalDirectoryType), reader["value"].ToString());
                        }
                        catch (Exception)
                        {
                            parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.LocalDirectoryType, 0, new TimeSpan(1, 0, 0))] =
                                LocalDirectoryType.ActiveDirectory;
                        }
                        break;

                    case DataBaseInformation.LdapServer:
                        parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.LdapServer, 0, new TimeSpan(1, 0, 0))] = reader["value"].ToString();
                        break;

                    case DataBaseInformation.LdapPort:
                        try
                        {
                            parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.LdapPort, 0, new TimeSpan(1, 0, 0))] = Convert.ToInt32(reader["value"]);
                        }
                        catch
                        {
                            parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.LdapPort, 0, new TimeSpan(1, 0, 0))] = 0;
                        }
                        break;

                    case DataBaseInformation.LdapUser:
                        parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.LdapUser, 0, new TimeSpan(1, 0, 0))] = reader["value"].ToString();
                        break;

                    case DataBaseInformation.LdapPassword:
                        parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.LdapPassword, 0, new TimeSpan(1, 0, 0))] = reader["value"].ToString();
                        break;

                    case DataBaseInformation.LdapContext:
                        parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.LdapContext, 0, new TimeSpan(1, 0, 0))] = reader["value"].ToString();
                        break;

                    case DataBaseInformation.LdapUseSsl:
                        parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.LdapUseSsl, 0, new TimeSpan(1, 0, 0))] = (bool?)Convert.ToBoolean(reader["value"]);
                        break;

                    default:
                        break;
                    }
                }
                reader.Close();
            }
        }
Пример #8
0
        /// <summary>
        /// Restarts the learning success.
        /// </summary>
        /// <param name="lm_id">The lm_id.</param>
        /// <remarks>Documented by Dev10, 2009-01-11</remarks>
        public IDictionary RestartLearningSuccess(int lm_id)
        {
            if (Parent.CurrentUser.ConnectionString.SyncType != SyncType.NotSynchronized)
            {
                object firstLearningSessionId = null, lastLearningSessionId = null;

                //Get all LearningSessions.Ids
                SqlCeCommand cmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser);
                cmd.CommandText = "SELECT id FROM LearningSessions WHERE lm_id = @lmId AND user_id = @userId ORDER BY id ASC;";
                cmd.Parameters.Add("@lmId", lm_id);
                cmd.Parameters.Add("@userId", Parent.CurrentUser.Id);

                SqlCeDataReader reader  = MSSQLCEConn.ExecuteReader(cmd);
                int             counter = 0;
                while (reader.Read())
                {
                    ++counter;
                    if (firstLearningSessionId == null)
                    {
                        firstLearningSessionId = reader["id"];
                    }
                    else
                    {
                        lastLearningSessionId = reader["id"];
                    }
                }
                reader.Close();

                if (lastLearningSessionId == null)
                {
                    lastLearningSessionId = firstLearningSessionId;
                }

                int firstLearningSession = Convert.ToInt32(firstLearningSessionId);
                int lastLearningSession  = Convert.ToInt32(lastLearningSessionId);

                if (counter == 0)
                {
                    return(Parent.GetParentDictionary());
                }

                cmd.CommandText = "DELETE FROM LearnLog WHERE session_id BETWEEN @sessionStart AND @sessionStop; DELETE FROM LearningSessions WHERE lm_id = @lmId AND user_id = @userId;";
                cmd.Parameters.Add("@sessionStart", firstLearningSession);
                cmd.Parameters.Add("@sessionStop", lastLearningSession);

                MSSQLCEConn.ExecuteNonQuery(cmd);

                return(Parent.GetParentDictionary());
            }
            else
            {
                SqlCeCommand cmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser);
                cmd.CommandText = "UPDATE UserProfiles SET local_directory_id=NULL, username=@user WHERE id=@id";
                cmd.Parameters.Add("@id", Parent.CurrentUser.Id);
                cmd.Parameters.Add("@user", "BACKUP_" + Parent.CurrentUser.AuthenticationStruct.UserName);
                MSSQLCEConn.ExecuteNonQuery(cmd);

                SqlCeCommand cmd2 = MSSQLCEConn.CreateCommand(Parent.CurrentUser);
                cmd2.CommandText = "INSERT INTO UserProfiles(username, local_directory_id, user_type) VALUES(@user, @id, 'ListAuthentication'); SELECT @@IDENTITY";
                cmd2.Parameters.Add("@user", User.CurrentWindowsUserName);
                cmd2.Parameters.Add("@id", User.CurrentWindowsUserId);
                int uid = MSSQLCEConn.ExecuteScalar <int>(cmd2).Value;

                ((Parent.CurrentUser as User).BaseUser as DbUser).SetId(uid);
                return(((Parent.CurrentUser as User).BaseUser as DbUser).Open());
            }
        }
Пример #9
0
        public int AddNewLM(string guid, int categoryId, string title, string licenceKey, bool contentProtected, int calCount)
        {
            if (!Parent.CurrentUser.ConnectionString.ConnectionString.EndsWith(Helper.EmbeddedDbExtension))
            {
                ConnectionStringStruct css = Parent.CurrentUser.ConnectionString;
                css.ConnectionString += @"\" + title.Replace(@"\", "_") + Helper.EmbeddedDbExtension;
                css.Typ = DatabaseType.MsSqlCe;
                Parent.CurrentUser.ConnectionString = css;
            }
            {
                //replace invalid filename characters
                ConnectionStringStruct css = Parent.CurrentUser.ConnectionString;
                css.ConnectionString = Helper.FilterInvalidFilenameCharacters(css.ConnectionString);
                Parent.CurrentUser.ConnectionString = css;
            }
            if (File.Exists(Parent.CurrentUser.ConnectionString.ConnectionString))
            {
                int i = 1;
                while (File.Exists(Parent.CurrentUser.ConnectionString.ConnectionString.Replace(Helper.EmbeddedDbExtension, "_" + i + Helper.EmbeddedDbExtension)))
                {
                    i++;
                }

                ConnectionStringStruct css = Parent.CurrentUser.ConnectionString;
                css.ConnectionString = css.ConnectionString.Replace(Helper.EmbeddedDbExtension, "_" + i + Helper.EmbeddedDbExtension);
                Parent.CurrentUser.ConnectionString = css;
            }
            if (!Directory.Exists(Path.GetDirectoryName(Parent.CurrentUser.ConnectionString.ConnectionString)))
            {
                Directory.CreateDirectory(Path.GetDirectoryName(Parent.CurrentUser.ConnectionString.ConnectionString));
            }
            using (SqlCeEngine clientEngine = new SqlCeEngine("Data Source=" + Parent.CurrentUser.ConnectionString.ConnectionString))
            {
                clientEngine.CreateDatabase();
                clientEngine.Dispose();
            }
            using (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser))
            {
                cmd.CommandText = Resources.MsSqlCeDbCreateScript;
                MSSQLCEConn.ExecuteNonQuery(cmd);
            }
            MSSQLCEConn.ApplyIndicesToDatabase(MSSQLCEConn.GetConnection(Parent.CurrentUser));
            int cat_id;

            using (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser))
            {
                cmd.CommandText = "SELECT id FROM Categories WHERE global_id=@cat_id;";
                cmd.Parameters.Add("@cat_id", categoryId);
                cat_id = MSSQLCEConn.ExecuteScalar <int>(cmd).Value;
            }
            using (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser))
            {
                cmd.CommandText = "INSERT INTO LearningModules (guid, title, categories_id, default_settings_id, allowed_settings_id, licence_key, content_protected, cal_count) " +
                                  "VALUES (@guid, @title, @cat_id, @dset, @aset, @lk, @cp, @cals); SELECT @@IDENTITY;";
                cmd.Parameters.Add("@guid", guid);
                cmd.Parameters.Add("@title", title);
                cmd.Parameters.Add("@cat_id", cat_id);
                cmd.Parameters.Add("@lk", licenceKey);
                cmd.Parameters.Add("@cp", contentProtected);
                cmd.Parameters.Add("@cals", calCount);
                cmd.Parameters.Add("@dset", MsSqlCeSettingsConnector.CreateNewDefaultSettings(Parent));
                cmd.Parameters.Add("@aset", MsSqlCeSettingsConnector.CreateNewAllowedSettings(Parent));

                return(MSSQLCEConn.ExecuteScalar <int>(cmd).Value);
            }
        }
Пример #10
0
        private IList <int> GetCardMedia(int cardid, Side side, WordType?type)
        {
            IList <int>    ids       = new List <int>();
            IList <IMedia> mediaList = Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.GetIdentifier(CacheObject.CardMedia, cardid)] as IList <IMedia>;

            if (mediaList != null)
            {
                foreach (DbMedia cms in mediaList)
                {
                    if (cms.Side == side && (!type.HasValue || cms.Type == type))
                    {
                        ids.Add(cms.Id);
                    }
                }

                return(ids);
            }

            using (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser))
            {
                cmd.CommandText = "SELECT id, media_type, side, type, is_default FROM \"Cards_MediaContent\" JOIN \"MediaContent\" ON media_id=id WHERE cards_id=@cardid;";
                cmd.Parameters.Add("@cardid", cardid);
                SqlCeDataReader reader = MsSqlCe.MSSQLCEConn.ExecuteReader(cmd);

                mediaList = new List <IMedia>();
                while (reader.Read())
                {
                    IMedia   newMedia  = null;
                    int      id        = Convert.ToInt32(reader["id"]);
                    EMedia   mtype     = (EMedia)Enum.Parse(typeof(EMedia), Convert.ToString(reader["media_type"]));
                    Side     cside     = (Side)Enum.Parse(typeof(Side), Convert.ToString(reader["side"]));
                    WordType wordtype  = (WordType)Enum.Parse(typeof(WordType), Convert.ToString(reader["type"]));
                    bool     isDefault = Convert.ToBoolean(reader["is_default"]);
                    switch (mtype)
                    {
                    case EMedia.Audio:
                        newMedia = new DbAudio(id, cardid, false, cside, wordtype, isDefault, (wordtype == WordType.Sentence), Parent);
                        break;

                    case EMedia.Image:
                        newMedia = new DbImage(id, cardid, false, cside, wordtype, isDefault, (wordtype == WordType.Sentence), Parent);
                        break;

                    case EMedia.Video:
                        newMedia = new DbVideo(id, cardid, false, cside, wordtype, isDefault, (wordtype == WordType.Sentence), Parent);
                        break;
                    }

                    mediaList.Add(newMedia);

                    if ((newMedia as DbMedia).Side == side && (!type.HasValue || (newMedia as DbMedia).Type == type))
                    {
                        ids.Add((newMedia as DbMedia).Id);
                    }
                }
                reader.Close();

                Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.CardMedia, cardid)] = mediaList;

                return(ids);
            }
        }
        /// <summary>
        /// Gets the size of the maximal.
        /// </summary>
        /// <param name="id">The id.</param>
        /// <returns></returns>
        /// <remarks>Documented by Dev03, 2009-01-09</remarks>
        public int GetMaximalSize(int id)
        {
            BoxSizes?sizes = Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.GetIdentifier(CacheObject.MaximalBoxSizes, Parent.CurrentUser.ConnectionString.LmId)] as BoxSizes?;

            if (sizes.HasValue)
            {
                return(sizes.Value.Sizes[id]);
            }

            using (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser))
            {
                cmd.CommandText = "SELECT count(*) FROM UserProfilesLearningModulesSettings WHERE user_id=@uid and lm_id=@lm_id";
                cmd.Parameters.Add("uid", Parent.CurrentUser.Id);
                cmd.Parameters.Add("lm_id", Parent.CurrentUser.ConnectionString.LmId);
                int settingsCount = Convert.ToInt32(cmd.ExecuteScalar());
                cmd.Parameters.Clear();

                cmd.CommandText = "SELECT count(*) FROM Cards WHERE id IN (SELECT cards_id FROM LearningModules_Cards WHERE lm_id=@lm_id)";
                cmd.Parameters.Add("lm_id", Parent.CurrentUser.ConnectionString.LmId);
                int cardsCount = Convert.ToInt32(cmd.ExecuteScalar());
                cmd.Parameters.Clear();

                // get the default sizes
                cmd.CommandText = @"SELECT box1_size, box2_size, box3_size, box4_size, box5_size, box6_size, box7_size, box8_size, box9_size
	FROM Boxes INNER JOIN Settings ON Boxes.id = Settings.boxes
		INNER JOIN LearningModules ON Settings.id=LearningModules.default_settings_id
	WHERE LearningModules.id=@lm_id"    ;
                cmd.Parameters.Add("@lm_id", Parent.CurrentUser.ConnectionString.LmId);
                SqlCeDataReader reader = MSSQLCEConn.ExecuteReader(cmd);
                reader.Read();
                BoxSizes defaultSizes = new BoxSizes(cardsCount,
                                                     Convert.ToInt32(reader["box1_size"]),
                                                     Convert.ToInt32(reader["box2_size"]),
                                                     Convert.ToInt32(reader["box3_size"]),
                                                     Convert.ToInt32(reader["box4_size"]),
                                                     Convert.ToInt32(reader["box5_size"]),
                                                     Convert.ToInt32(reader["box6_size"]),
                                                     Convert.ToInt32(reader["box7_size"]),
                                                     Convert.ToInt32(reader["box8_size"]),
                                                     Convert.ToInt32(reader["box9_size"]),
                                                     cardsCount);
                reader.Close();

                if (settingsCount > 0)
                {
                    // get the user sizes
                    cmd.Parameters.Clear();
                    cmd.CommandText = "SELECT boxes FROM Settings INNER JOIN UserProfilesLearningModulesSettings ON Settings.id=UserProfilesLearningModulesSettings.settings_id WHERE user_id=@uid and lm_id=@lm_id";
                    cmd.Parameters.Add("@uid", Parent.CurrentUser.Id);
                    cmd.Parameters.Add("@lm_id", Parent.CurrentUser.ConnectionString.LmId);
                    int boxesId = Convert.ToInt32(cmd.ExecuteScalar());
                    cmd.Parameters.Clear();

                    cmd.CommandText = @"SELECT box1_size, box2_size, box3_size, box4_size, box5_size, box6_size, box7_size, box8_size, box9_size FROM Boxes WHERE id=@bid";
                    cmd.Parameters.Add("@bid", boxesId);
                    reader = MSSQLCEConn.ExecuteReader(cmd);
                    if (reader.Read())
                    {
                        sizes = new BoxSizes(cardsCount,
                                             GetUserOrDefaultSize(reader["box1_size"], defaultSizes.Sizes[1]),
                                             GetUserOrDefaultSize(reader["box2_size"], defaultSizes.Sizes[2]),
                                             GetUserOrDefaultSize(reader["box3_size"], defaultSizes.Sizes[3]),
                                             GetUserOrDefaultSize(reader["box4_size"], defaultSizes.Sizes[4]),
                                             GetUserOrDefaultSize(reader["box5_size"], defaultSizes.Sizes[5]),
                                             GetUserOrDefaultSize(reader["box6_size"], defaultSizes.Sizes[6]),
                                             GetUserOrDefaultSize(reader["box7_size"], defaultSizes.Sizes[7]),
                                             GetUserOrDefaultSize(reader["box8_size"], defaultSizes.Sizes[8]),
                                             GetUserOrDefaultSize(reader["box9_size"], defaultSizes.Sizes[9]),
                                             cardsCount);
                    }
                    else
                    {
                        sizes = defaultSizes;
                    }
                    reader.Close();
                }
                else
                {
                    sizes = defaultSizes;
                }

                Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.MaximalBoxSizes, Parent.CurrentUser.ConnectionString.LmId)] = sizes.Value;

                return(sizes.Value.Sizes[id]);
            }
        }
        /// <summary>
        /// Gets the cards by query.
        /// </summary>
        /// <param name="id">The id.</param>
        /// <param name="query">The query.</param>
        /// <param name="orderBy">The order by.</param>
        /// <param name="orderDir">The order dir.</param>
        /// <param name="number">The number.</param>
        /// <returns></returns>
        /// <remarks>Documented by Dev08, 2009-01-19</remarks>
        public List <ICard> GetCardsByQuery(int id, QueryStruct[] query, QueryOrder orderBy, QueryOrderDir orderDir, int number)
        {
            List <int> ids = new List <int>();

            if (cardStateCount < 0)
            {
                int cardCount;
                using (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser))
                {
                    //create entries in UserCardState for all cards in lm
                    cmd.CommandText = "SELECT count(*) FROM \"LearningModules_Cards\" WHERE lm_id=@lmid";
                    cmd.Parameters.Add("@lmid", id);
                    cardCount = MSSQLCEConn.ExecuteScalar <int>(cmd).Value;
                }
                using (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser))
                {
                    //create entries in UserCardState for all cards in lm
                    cmd.CommandText = "SELECT count(*) FROM UserCardState CS INNER JOIN Cards C ON C.ID = CS.cards_id WHERE CS.user_id=@userid AND C.lm_id=@lmid";
                    cmd.Parameters.Add("@userid", Parent.CurrentUser.Id);
                    cmd.Parameters.Add("@lmid", id);
                    cardStateCount = MSSQLCEConn.ExecuteScalar <int>(cmd).Value;
                }
                if (cardCount != cardStateCount)
                {
                    using (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser))
                    {
                        cmd.CommandText = "INSERT INTO UserCardState (user_id, cards_id, box, active) " +
                                          String.Format("SELECT {0}, C.id, 0, 1 FROM Cards C LEFT OUTER JOIN (SELECT * FROM UserCardState WHERE user_id = {0}) CS ON C.ID = CS.cards_id WHERE CS.cards_id IS NULL AND C.lm_id=@lmid", Parent.CurrentUser.Id);
                        cmd.Parameters.Add("@lmid", id);
                        MSSQLCEConn.ExecuteNonQuery(cmd);
                    }
                }
            }

            using (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser))
            {
                cmd.CommandText = string.Format("SELECT {0}C.id as cid FROM Cards C INNER JOIN UserCardState CS ON C.id=CS.cards_id", number > 0 ? "TOP (" + number + ") " : string.Empty);

                List <string> conditions = new List <string>();
                foreach (QueryStruct q in query)
                {
                    string cond = "CS.user_id=@uid";
                    if (q.ChapterId != -1)
                    {
                        cond += string.Format(" AND C.chapters_id={0}", q.ChapterId);
                    }
                    if (q.BoxId != -1)
                    {
                        cond += string.Format(" AND CS.box={0}", q.BoxId);
                    }
                    switch (q.CardState)
                    {
                    case QueryCardState.Active:
                        cond += " AND CS.active=1";
                        break;

                    case QueryCardState.Inactive:
                        cond += " AND CS.active=0";
                        break;

                    default:
                        break;
                    }

                    conditions.Add(cond);
                }
                cmd.CommandText += " WHERE C.lm_id=@lmid AND (";
                if (conditions.Count > 0)
                {
                    cmd.CommandText += " " + conditions[0];
                    conditions.RemoveAt(0);
                    foreach (string cond in conditions)
                    {
                        cmd.CommandText += " OR " + cond;
                    }
                }
                else
                {
                    cmd.CommandText += " 1=1";
                }
                cmd.CommandText += ")";

                switch (orderBy)
                {
                case QueryOrder.Id:
                    cmd.CommandText += " ORDER BY C.id";
                    break;

                case QueryOrder.Chapter:
                    cmd.CommandText += " ORDER BY C.chapters_id";
                    break;

                case QueryOrder.Random:
                    cmd.CommandText += " ORDER BY newid()";
                    break;

                case QueryOrder.Timestamp:
                    cmd.CommandText += " ORDER BY CS.timestamp";
                    break;

                default:
                    break;
                }
                if (orderBy == QueryOrder.Chapter || orderBy == QueryOrder.Id)
                {
                    switch (orderDir)
                    {
                    case QueryOrderDir.Ascending:
                        cmd.CommandText += " ASC";
                        break;

                    case QueryOrderDir.Descending:
                        cmd.CommandText += " DESC";
                        break;
                    }
                }
                if (orderBy == QueryOrder.Timestamp)
                {
                    cmd.CommandText += ", C.id " + (orderDir == QueryOrderDir.Ascending ? "ASC" : "DESC");
                }

                cmd.Parameters.Add("@lmid", id);
                cmd.Parameters.Add("@uid", Parent.CurrentUser.Id);

                SqlCeDataReader reader = MSSQLCEConn.ExecuteReader(cmd);
                List <ICard>    cards  = new List <ICard>();
                //Performance: 180ms!!!
                while (reader.Read())
                {
                    cards.Add(new DbCard(Convert.ToInt32(reader["cid"]), false, Parent));
                }
                reader.Close();

                return(cards);
            }
        }
Пример #13
0
        /// <summary>
        /// Gets the settings value.
        /// </summary>
        /// <param name="snoozeOptionsId">The snooze options id.</param>
        /// <param name="cacheObjectType">Type of the cache object.</param>
        /// <param name="cacheValue">The cache value.</param>
        /// <remarks>Documented by Dev08, 2009-01-13</remarks>
        private void GetSettingsValue(int snoozeOptionsId, CacheObject cacheObjectType, out object cacheValue)
        {
            SqlCeCommand cmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser);

            cmd.CommandText = "SELECT * FROM \"SnoozeOptions\" WHERE id=@id";
            cmd.Parameters.Add("@id", snoozeOptionsId);

            SqlCeDataReader reader = MSSQLCEConn.ExecuteReader(cmd);

            reader.Read();

            int?        sid           = DbValueConverter.Convert <int>(reader["id"]);
            bool?       cardsEnabled  = DbValueConverter.Convert <bool>(reader["cards_enabled"]);
            bool?       rightsEnabled = DbValueConverter.Convert <bool>(reader["rights_enabled"]);
            bool?       timeEnabled   = DbValueConverter.Convert <bool>(reader["time_enabled"]);
            int?        snoozeCards   = DbValueConverter.Convert <int>(reader["snooze_cards"]);
            int?        snoozeHigh    = DbValueConverter.Convert <int>(reader["snooze_high"]);
            int?        snoozeLow     = DbValueConverter.Convert <int>(reader["snooze_low"]);
            ESnoozeMode?snoozeMode    = DbValueConverter.Convert <ESnoozeMode>(reader["snooze_mode"]);
            int?        snoozeRights  = DbValueConverter.Convert <int>(reader["snooze_rights"]);
            int?        snoozeTime    = DbValueConverter.Convert <int>(reader["snooze_time"]);

            reader.Close();

            //cache values
            DateTime expires = DateTime.Now.Add(Cache.DefaultSettingsValidationTime);

            Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.SettingsSnoozeCardsEnabled, snoozeOptionsId, expires)]  = cardsEnabled;
            Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.SettingsSnoozeRightsEnabled, snoozeOptionsId, expires)] = rightsEnabled;
            Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.SettingsSnoozeTimeEnabled, snoozeOptionsId, expires)]   = timeEnabled;
            Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.SettingsSnoozeCards, snoozeOptionsId, expires)]         = snoozeCards;
            Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.SettingsSnoozeHigh, snoozeOptionsId, expires)]          = snoozeHigh;
            Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.SettingsSnoozeLow, snoozeOptionsId, expires)]           = snoozeLow;
            Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.SettingsSnoozeMode, snoozeOptionsId, expires)]          = snoozeMode;
            Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.SettingsSnoozeRights, snoozeOptionsId, expires)]        = snoozeRights;
            Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.SettingsSnoozeTime, snoozeOptionsId, expires)]          = snoozeTime;
            Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.SettingsSnoozeOptionsId, snoozeOptionsId, expires)]     = sid;

            //set output value
            switch (cacheObjectType)
            {
            case CacheObject.SettingsSnoozeCardsEnabled: cacheValue = cardsEnabled; break;

            case CacheObject.SettingsSnoozeRightsEnabled: cacheValue = rightsEnabled; break;

            case CacheObject.SettingsSnoozeTimeEnabled: cacheValue = timeEnabled; break;

            case CacheObject.SettingsSnoozeCards: cacheValue = snoozeCards; break;

            case CacheObject.SettingsSnoozeHigh: cacheValue = snoozeHigh; break;

            case CacheObject.SettingsSnoozeLow: cacheValue = snoozeLow; break;

            case CacheObject.SettingsSnoozeMode: cacheValue = snoozeMode; break;

            case CacheObject.SettingsSnoozeRights: cacheValue = snoozeRights; break;

            case CacheObject.SettingsSnoozeTime: cacheValue = snoozeTime; break;

            case CacheObject.SettingsSnoozeOptionsId: cacheValue = sid; break;

            default: cacheValue = null; break;
            }
        }
Пример #14
0
        /// <summary>
        /// Creates a new user session.
        /// The old one will be automatically closed, in case of inconsistance data.
        /// </summary>
        /// <param name="lm_id">The lm_id.</param>
        /// <returns></returns>
        /// <remarks>Documented by Dev10, 2009-01-11</remarks>
        /// <remarks>Documented by Dev08, 2009-04-28</remarks>
        public int OpenUserSession(int lm_id)
        {
            //1. Check if the old session is closed
            bool     previousSessionExisting = false;
            DateTime?endtime         = null;
            int      latestSessionId = 0;

            SqlCeCommand cmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser);

            cmd.CommandText = "SELECT * FROM LearningSessions WHERE lm_id=@lmid AND user_id=@userid ORDER BY starttime DESC";
            cmd.Parameters.Add("@lmid", lm_id);
            cmd.Parameters.Add("@userid", Parent.CurrentUser.Id);
            SqlCeDataReader reader = MSSQLCEConn.ExecuteReader(cmd);

            if (reader.Read())
            {
                previousSessionExisting = true;
                try
                {
                    latestSessionId = Convert.ToInt32(reader["id"]);
                    endtime         = Convert.ToDateTime(reader["endtime"]);
                }
                catch
                {
                    endtime = null;
                }
            }

            //2. Close the previous session, if it hasn't closed before (maybe cause of crash of ML)
            if (previousSessionExisting && !endtime.HasValue)
            {
                cmd.Parameters.Clear();
                cmd.CommandText = "UPDATE LearningSessions SET endtime=GETDATE() WHERE id=@id";
                cmd.Parameters.Add("@id", latestSessionId);
                MSSQLCEConn.ExecuteNonQuery(cmd);
            }

            //3. Add new session entry to DB
            cmd.Parameters.Clear();
            cmd.CommandText = "INSERT INTO LearningSessions (user_id, lm_id, starttime, sum_right, sum_wrong, pool_content, box1_content, box2_content, box3_content, " +
                              "box4_content, box5_content, box6_content, box7_content, box8_content, box9_content, box10_content)" +
                              "VALUES(@userid, @lmid, GETDATE(),  0, 0, @pool, @b1, @b2, @b3, @b4, @b5, @b6, @b7, @b8, @b9, @b10); SELECT @@IDENTITY;";
            cmd.Parameters.Add("@userid", Parent.CurrentUser.Id);
            cmd.Parameters.Add("@lmid", lm_id);
            int      counter      = 0;
            int      cardsInBoxes = 0;
            BoxSizes boxContent   = GetCurrentBoxContent();

            foreach (int box in boxContent.Sizes)
            {
                if (counter == 0)
                {
                    cmd.Parameters.Add("@pool", box);
                    ++counter;
                    continue;
                }

                cmd.Parameters.Add("@b" + Convert.ToString(counter++), box);
                cardsInBoxes += box;
            }

            int newSessionId = MSSQLCEConn.ExecuteScalar <int>(cmd).Value;

            //Following Statement does add the "RunningSession" = true to the current Statistic.
            MsSqlCeStatisticConnector connector = MsSqlCeStatisticConnector.GetInstance(Parent);

            connector.RunningSession = newSessionId;

            return(newSessionId);
        }
Пример #15
0
        /// <summary>
        /// Moves the chapter.
        /// How does this function work (by FabThe, who always forgot how it works):
        /// If the "FirstChapter.Position" is smaller than the "SecondChapter.Position" the new position of "FirstChapter" will be "SecondChapter.Position" + 5
        /// If the "FirstChapter.Position" is greater than the "SecondChapter.Position" the new position of "FirstChapter" will be "SecondChapter.Position" - 5
        /// Summary: If the first chapter is on the top and the second chapter in the middle, the new position of the first chapter will be after the second chapter.
        /// If the first chapter is in the middle and the second chapter on the top, the new position of the first chapter will be before the second chapter.
        /// </summary>
        /// <param name="lmid">The id of the learning module.</param>
        /// <param name="first_id">The id of the first chapter.</param>
        /// <param name="second_id">The id of the second chapter..</param>
        /// <remarks>Documented by Dev02, 2008-08-05</remarks>
        /// <remarks>Documented by Dev08, 2009-01-12</remarks>
        public void MoveChapter(int lmid, int first_id, int second_id)
        {
            int position1, position2;

            using (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(parent.CurrentUser))
            {
                SqlCeTransaction transaction = cmd.Connection.BeginTransaction();

                cmd.CommandText = "SELECT position FROM \"Chapters\" WHERE id=@id AND lm_id=@lmid";
                cmd.Parameters.Add("@id", first_id);
                cmd.Parameters.Add("@lmid", lmid);
                object result = MSSQLCEConn.ExecuteScalar(cmd);
                if (result == null)
                {
                    throw new IdAccessException(first_id);
                }
                position1 = Convert.ToInt32(result);

                using (SqlCeCommand cmd2 = MSSQLCEConn.CreateCommand(parent.CurrentUser))
                {
                    cmd2.CommandText = "SELECT position FROM \"Chapters\" WHERE id=@id AND lm_id=@lmid";
                    cmd2.Parameters.Add("@id", second_id);
                    cmd2.Parameters.Add("@lmid", lmid);
                    object result2 = MSSQLCEConn.ExecuteScalar(cmd2);
                    if (result2 == null)
                    {
                        throw new IdAccessException(second_id);
                    }
                    position2 = Convert.ToInt32(result2);
                }

                int newfirstposition;

                if (position1 < position2)
                {
                    newfirstposition = position2 + 5; //insert first after second
                }
                else if (position1 > position2)
                {
                    newfirstposition = position2 - 5; //insert first before second
                }
                else
                {
                    //positions were equal => no reordering possible
                    return;
                }

                int serialchapters = 10, increment = 10;
                using (SqlCeCommand cmd3 = MSSQLCEConn.CreateCommand(parent.CurrentUser))
                {
                    cmd3.CommandText  = "UPDATE \"Chapters\" SET position=@position WHERE id=@id; ";
                    cmd3.CommandText += "SELECT * FROM \"Chapters\" WHERE lm_id=@lmid ORDER BY position ASC";
                    cmd3.Parameters.Add("@position", newfirstposition);
                    cmd3.Parameters.Add("@id", first_id);
                    cmd3.Parameters.Add("@lmid", lmid);
                    SqlCeDataReader reader = MSSQLCEConn.ExecuteReader(cmd3);
                    while (reader.Read())
                    {
                        using (SqlCeCommand cmd4 = MSSQLCEConn.CreateCommand(parent.CurrentUser))
                        {
                            cmd4.CommandText = "UPDATE \"Chapters\" SET position=@position WHERE \"Chapters\".id=@chapterId";
                            cmd4.Parameters.Add("@position", serialchapters);
                            cmd4.Parameters.Add("@chapterId", reader["id"]);
                            MSSQLCEConn.ExecuteNonQuery(cmd4);

                            serialchapters += increment;
                        }
                    }
                    reader.Close();
                }

                transaction.Commit();
            }

            parent.CurrentUser.Cache.Uncache(ObjectLifetimeIdentifier.GetIdentifier(CacheObject.ChaptersList, lmid));
        }
        /// <summary>
        /// Creates the learn log entry.
        /// </summary>
        /// <param name="learnLog">The learn log.</param>
        /// <remarks>Documented by Dev10, 2009-01-11</remarks>
        public void CreateLearnLogEntry(LearnLogStruct learnLog)
        {
            bool answeredCardCorrect = false;

            if (learnLog.MoveType != MoveType.Manual)
            {
                if (learnLog.NewBox > 1)
                {
                    answeredCardCorrect = true;
                }
            }

            using (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser))
            {
                cmd.CommandText  = "INSERT INTO LearnLog (session_id, user_id, lm_id, cards_id, old_box, new_box, timestamp, duration, learn_mode, move_type, answer, direction, case_sensitive, correct_on_the_fly, percentage_known, percentage_required) ";
                cmd.CommandText += "VALUES(@sid, @uid, @lmid, @cid, @obox, @nbox, @ts, @dur, @lmode, @mtype, @answ, @dir, @csen, @cotf, @pknown, @preq); ";
                if (learnLog.MoveType != MoveType.Manual && answeredCardCorrect)
                {
                    cmd.CommandText += "UPDATE LearningSessions SET sum_right=sum_right + 1 WHERE id=@sid AND user_id=@uid AND lm_id=@lmid; ";
                }
                else if (learnLog.MoveType != MoveType.Manual && !answeredCardCorrect)
                {
                    cmd.CommandText += "UPDATE LearningSessions SET sum_wrong=sum_wrong + 1 WHERE id=@sid AND user_id=@uid AND lm_id=@lmid; ";
                }

                string newBoxContent = string.Empty;
                string oldBoxContent = string.Empty;
                if (learnLog.NewBox.Value > 0)
                {
                    newBoxContent = "box" + learnLog.NewBox.Value.ToString() + "_content";
                }
                else
                {
                    newBoxContent = "pool_content";
                }

                if (learnLog.OldBox.Value > 0)
                {
                    oldBoxContent = "box" + learnLog.OldBox.Value.ToString() + "_content";
                }
                else
                {
                    oldBoxContent = "pool_content";
                }

                if (learnLog.NewBox.Value != learnLog.OldBox.Value)
                {
                    cmd.CommandText += "UPDATE LearningSessions SET " + newBoxContent + "=" + newBoxContent + " + 1, " + oldBoxContent + "=" + oldBoxContent + " - 1 WHERE id=@sid AND user_id=@uid AND lm_id=@lmid; ";
                }

                cmd.Parameters.Add("@sid", learnLog.SessionID.Value);
                cmd.Parameters.Add("@uid", Parent.CurrentUser.Id);
                cmd.Parameters.Add("@lmid", Parent.CurrentUser.ConnectionString.LmId);
                cmd.Parameters.Add("@cid", learnLog.CardsID.Value);
                cmd.Parameters.Add("@obox", learnLog.OldBox.Value);
                cmd.Parameters.Add("@nbox", learnLog.NewBox.Value);
                cmd.Parameters.Add("@ts", learnLog.TimeStamp.Value);
                cmd.Parameters.Add("@dur", learnLog.Duration.Value);
                cmd.Parameters.Add("@lmode", learnLog.LearnMode.Value.ToString());
                cmd.Parameters.Add("@mtype", learnLog.MoveType.Value.ToString());
                cmd.Parameters.Add("@answ", learnLog.Answer);
                cmd.Parameters.Add("@dir", learnLog.Direction.Value.ToString());
                cmd.Parameters.Add("@csen", learnLog.CaseSensitive);
                cmd.Parameters.Add("@cotf", learnLog.CorrectOnTheFly);
                cmd.Parameters.Add("@pknown", learnLog.PercentageKnown);
                cmd.Parameters.Add("@preq", learnLog.PercentageRequired);

                MSSQLCEConn.ExecuteNonQuery(cmd);
            }

            //delete caches
            Parent.CurrentUser.Cache.Uncache(ObjectLifetimeIdentifier.GetIdentifier(CacheObject.StatisticWrongCards, learnLog.SessionID.Value));
            Parent.CurrentUser.Cache.Uncache(ObjectLifetimeIdentifier.GetIdentifier(CacheObject.StatisticCorrectCards, learnLog.SessionID.Value));
            Parent.CurrentUser.Cache.Uncache(ObjectLifetimeIdentifier.GetIdentifier(CacheObject.StatisticContentOfBoxes, learnLog.SessionID.Value));
        }
        /// <summary>
        /// Gets the user learning module settings.
        /// </summary>
        /// <param name="id">The id.</param>
        /// <returns></returns>
        /// <remarks>Documented by Dev05, 2009-01-16</remarks>
        public int GetUserLearningModuleSettings(int id)
        {
            int          cnt;
            SqlCeCommand cmd_main = MSSQLCEConn.CreateCommand(Parent.CurrentUser);

            cmd_main.CommandText = "SELECT count(*) FROM \"UserProfilesLearningModulesSettings\" " +
                                   "WHERE user_id=@uid and lm_id=@lm_id;";
            cmd_main.Parameters.Add("@uid", Parent.CurrentUser.Id);
            cmd_main.Parameters.Add("@lm_id", id);

            cnt = MSSQLCEConn.ExecuteScalar <int>(cmd_main).Value;
            if (cnt > 0)
            {
                //Workaround for Issue: ML-2458 If System Date is older than Timestamps only one card is asked
                SqlCeCommand timeCmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser);
                timeCmd.CommandText = "SELECT cards_id FROM \"UserCardState\" WHERE user_id=@uid AND timestamp > @now";
                timeCmd.Parameters.Add("@uid", Parent.CurrentUser.Id);
                timeCmd.Parameters.Add("@now", DateTime.Now);
                SqlCeDataReader reader = MSSQLCEConn.ExecuteReader(timeCmd);

                List <int> wrongCards = new List <int>();
                while (reader.Read())
                {
                    wrongCards.Add(Convert.ToInt32(reader["cards_id"]));
                }
                reader.Close();

                foreach (int cid in wrongCards)
                {
                    SqlCeCommand updateCmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser);
                    updateCmd.CommandText = "UPDATE \"UserCardState\" SET timestamp = @time WHERE user_id=@uid AND cards_id=@cid";
                    updateCmd.Parameters.Add("@uid", Parent.CurrentUser.Id);
                    updateCmd.Parameters.Add("@cid", cid);
                    updateCmd.Parameters.Add("@time", DateTime.Now.AddSeconds(-Math.PI / 2));
                    MSSQLCEConn.ExecuteNonQuery(updateCmd);
                }
                //End of Workaround for Issue: ML-2458

                SqlCeCommand cmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser);
                cmd.CommandText = "SELECT settings_id FROM \"UserProfilesLearningModulesSettings\" " +
                                  "WHERE user_id=@uid and lm_id=@lm_id;";
                cmd.Parameters.Add("@uid", Parent.CurrentUser.Id);
                cmd.Parameters.Add("@lm_id", id);

                return(MSSQLCEConn.ExecuteScalar <int>(cmd).Value);
            }
            else
            {
                int          newSettings = MsSqlCeSettingsConnector.CreateNewSettings(Parent);
                SqlCeCommand cmd         = MSSQLCEConn.CreateCommand(Parent.CurrentUser);
                cmd.CommandText = "INSERT INTO \"UserProfilesLearningModulesSettings\" (user_id, lm_id, settings_id) " +
                                  "VALUES (@uid, @lm_id, @settings_id)";
                cmd.Parameters.Add("@uid", Parent.CurrentUser.Id);
                cmd.Parameters.Add("@lm_id", id);
                cmd.Parameters.Add("@settings_id", newSettings);
                MSSQLCEConn.ExecuteNonQuery(cmd);

                cmd             = MSSQLCEConn.CreateCommand(Parent.CurrentUser);
                cmd.CommandText = "SELECT id FROM \"Chapters\" " +
                                  "WHERE lm_id=@lm_id;";
                cmd.Parameters.Add("@lm_id", id);

                SqlCeDataReader reader     = MSSQLCEConn.ExecuteReader(cmd);
                List <int>      chapterIds = new List <int>();
                while (reader.Read())
                {
                    chapterIds.Add(Convert.ToInt32(reader["id"]));
                }
                reader.Close();

                foreach (int chapterId in chapterIds)
                {
                    SqlCeCommand cmd2 = MSSQLCEConn.CreateCommand(Parent.CurrentUser);
                    cmd2.CommandText = "INSERT INTO \"SelectedLearnChapters\" (chapters_id, settings_id) VALUES (@cid, @sid);";
                    cmd2.Parameters.Add("@cid", chapterId);
                    cmd2.Parameters.Add("@sid", newSettings);

                    MSSQLCEConn.ExecuteNonQuery(cmd2);
                }

                SqlCeCommand cmd3 = MSSQLCEConn.CreateCommand(Parent.CurrentUser);
                cmd3.CommandText = "SELECT id FROM \"Cards\" WHERE id IN " +
                                   "(SELECT cards_id FROM \"LearningModules_Cards\" WHERE lm_id=@lm_id)";
                cmd3.Parameters.Add("@lm_id", id);

                SqlCeDataReader reader2 = MSSQLCEConn.ExecuteReader(cmd3);
                List <int>      cardIds = new List <int>();
                while (reader2.Read())
                {
                    cardIds.Add(Convert.ToInt32(reader2["id"]));
                }
                reader2.Close();

                foreach (int cid in cardIds)
                {
                    SqlCeCommand cmd4 = MSSQLCEConn.CreateCommand(Parent.CurrentUser);
                    cmd4.CommandText = "INSERT INTO \"UserCardState\" (user_id, cards_id, box, active) VALUES (@param_user_id, @param_cards_id, 0, 1);";
                    cmd4.Parameters.Add("@param_user_id", Parent.CurrentUser.Id);
                    cmd4.Parameters.Add("@param_cards_id", cid);

                    MSSQLCEConn.ExecuteNonQuery(cmd4);
                }

                Parent.CurrentUser.Cache.Clear();

                return(newSettings);
            }
        }