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