/// <summary>
        /// Gets the user list.
        /// </summary>
        /// <returns></returns>
        /// <remarks>Documented by Dev05, 2009-01-16</remarks>
        public IList <UserStruct> GetUserList()
        {
            IList <UserStruct> users = Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.GetIdentifier(CacheObject.UserList, 0)] as IList <UserStruct>;

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

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

            cmd.CommandText = "SELECT * FROM \"UserProfiles\";";

            SqlCeDataReader reader = MSSQLCEConn.ExecuteReader(cmd);

            users = new List <UserStruct>();
            users.Add(new UserStruct(Resources.CREATE_NEW_USER, UserAuthenticationTyp.ListAuthentication));
            while (reader.Read())
            {
                UserStruct user = new UserStruct(reader["username"].ToString(), reader["local_directory_id"].ToString(), UserAuthenticationTyp.ListAuthentication);

                users.Add(user);
            }
            reader.Close();

            Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.UserList, 0, new TimeSpan(0, 0, 30))] = users;

            return(users);
        }
Esempio n. 2
0
        /// <summary>
        /// Gets the chapter ids.
        /// </summary>
        /// <param name="lmid">The id of the learning module.</param>
        /// <returns></returns>
        /// <remarks>Documented by Dev02, 2008-08-05</remarks>
        /// <remarks>Documented by Dev08, 2009-01-12</remarks>
        public IList <int> GetChapterIds(int lmid)
        {
            IList <int> chaptersCache = parent.CurrentUser.Cache[ObjectLifetimeIdentifier.GetIdentifier(CacheObject.ChaptersList, lmid)] as IList <int>;

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

            IList <int> list = new List <int>();

            using (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(parent.CurrentUser))
            {
                cmd.CommandText = "SELECT id FROM \"Chapters\" WHERE lm_id=@lmid ORDER BY position ASC";
                cmd.Parameters.Add("@lmid", lmid);
                SqlCeDataReader reader = MSSQLCEConn.ExecuteReader(cmd);
                while (reader.Read())
                {
                    list.Add(Convert.ToInt32(reader["id"]));
                }
                reader.Close();
            }

            parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.ChaptersList, lmid)] = list;
            return(list);
        }
Esempio n. 3
0
        /// <summary>
        /// Gets a single property value for a media object.
        /// </summary>
        /// <param name="id">The id.</param>
        /// <param name="property">The property.</param>
        /// <returns></returns>
        /// <remarks>Documented by Dev02, 2008-08-07</remarks>
        /// <remarks>Documented by Dev03, 2009-01-13</remarks>
        public string GetPropertyValue(int id, MLifter.DAL.Interfaces.MediaProperty property)
        {
            Dictionary <MediaProperty, string> properties = Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.GetIdentifier(CacheObject.MediaProperties, id)] as Dictionary <MediaProperty, string>;

            if (properties != null && properties.ContainsKey(property))
            {
                return(properties[property]);
            }

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

            cmd.CommandText = "SELECT property, value FROM MediaProperties WHERE media_id=@id";
            cmd.Parameters.Add("@id", id);
            SqlCeDataReader reader = MSSQLCEConn.ExecuteReader(cmd);

            properties = new Dictionary <MediaProperty, string>();
            while (reader.Read())
            {
                properties[(MediaProperty)Enum.Parse(typeof(MediaProperty), reader["property"].ToString())] = reader["value"].ToString();
            }

            reader.Close();
            Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.MediaProperties, id, new TimeSpan(1, 0, 0))] = properties;

            if (properties.ContainsKey(property))
            {
                return(properties[property]);
            }
            else
            {
                return(null);
            }
        }
Esempio n. 4
0
        /// <summary>
        /// Sets a single property value for a media object.
        /// </summary>
        /// <param name="id">The id.</param>
        /// <param name="property">The property.</param>
        /// <param name="value">The value.</param>
        /// <remarks>Documented by Dev02, 2008-08-07</remarks>
        /// <remarks>Documented by Dev03, 2009-01-13</remarks>
        public void SetPropertyValue(int id, MLifter.DAL.Interfaces.MediaProperty property, string value)
        {
            SqlCeCommand     cmd  = MSSQLCEConn.CreateCommand(Parent.CurrentUser);
            SqlCeTransaction tran = cmd.Connection.BeginTransaction();

            if (GetPropertyValue(id, property) == null)
            {
                cmd.CommandText = "INSERT INTO MediaProperties (media_id, property, value) VALUES (@media_id, @property, @value);";
            }
            else
            {
                cmd.CommandText = "UPDATE MediaProperties SET value=@value WHERE media_id=@media_id AND property=@property;";
            }

            Dictionary <MediaProperty, string> properties = Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.GetIdentifier(CacheObject.MediaProperties, id)] as Dictionary <MediaProperty, string>;

            if (properties == null)
            {
                properties = new Dictionary <MediaProperty, string>();
            }
            properties[property] = value;
            Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.MediaProperties, id, new TimeSpan(1, 0, 0))] = properties;

            cmd.Parameters.Add("@media_id", id);
            cmd.Parameters.Add("@property", property.ToString());
            cmd.Parameters.Add("@value", value);
            MSSQLCEConn.ExecuteNonQuery(cmd);

            tran.Commit();
        }
Esempio n. 5
0
        /// <summary>
        /// Gets the settings.
        /// </summary>
        /// <param name="id">The id.</param>
        /// <returns></returns>
        /// <remarks>Documented by Dev05, 2009-01-15</remarks>
        public ISettings GetSettings(int id)
        {
            int?chapterSettingsId = Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.GetIdentifier(CacheObject.ChapterSetting, id)] as int?;

            if (chapterSettingsId.HasValue)
            {
                return(new DbSettings(chapterSettingsId.Value, false, Parent));
            }

            using (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser))
            {
                cmd.CommandText = "SELECT settings_id FROM \"Chapters\" WHERE id=@id";
                cmd.Parameters.Add("@id", id);

                int?sid = MSSQLCEConn.ExecuteScalar <int>(cmd);

                if (sid.HasValue)
                {
                    Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.ChapterSetting, id)] = sid;
                    return(new DbSettings(sid.Value, false, Parent));
                }
                else
                {
                    return(null);
                }
            }
        }
Esempio n. 6
0
        /// <summary>
        /// Sets the properties for a media object.
        /// </summary>
        /// <param name="id">The id of the media object.</param>
        /// <param name="properties">The properties for the media object.</param>
        /// <remarks>Documented by Dev03, 2008-08-05</remarks>
        /// <remarks>Documented by Dev03, 2009-01-13</remarks>
        public void SetProperties(int id, Dictionary <MLifter.DAL.Interfaces.MediaProperty, string> properties)
        {
            SqlCeCommand     cmd1 = MSSQLCEConn.CreateCommand(Parent.CurrentUser);
            SqlCeTransaction tran = cmd1.Connection.BeginTransaction();

            cmd1.CommandText = "DELETE FROM MediaProperties WHERE media_id=@id;";
            cmd1.Parameters.Add("@id", id);
            MSSQLCEConn.ExecuteNonQuery(cmd1);

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

            cmd.CommandText = "INSERT INTO MediaProperties (media_id, property, value) VALUES (@media_id, @property, @value);";
            cmd.Parameters.Add("@media_id", SqlDbType.Int, 4);
            cmd.Parameters.Add("@property", SqlDbType.NVarChar, 100);
            cmd.Parameters.Add("@value", SqlDbType.NVarChar, 100);
            foreach (KeyValuePair <MediaProperty, string> item in properties)
            {
                cmd.Parameters["@media_id"].Value = id;
                cmd.Parameters["@property"].Value = item.Key.ToString();
                cmd.Parameters["@value"].Value    = item.Value;
                MSSQLCEConn.ExecuteNonQuery(cmd);
            }

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

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

            using (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser))
            {
                sizes = new BoxSizes(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0);          //filled with temp/default values

                cmd.CommandText = "SELECT CS.box, count(*) AS count FROM UserCardState CS INNER JOIN Cards C ON CS.cards_id = C.id WHERE CS.active=1 and CS.user_id=@user_id and C.lm_id=@lm_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.Value.Sizes[Convert.ToInt32(reader["box"])] = Convert.ToInt32(reader["count"]);
                }
                reader.Close();

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

                return(sizes.Value.Sizes[id]);
            }
        }
Esempio n. 8
0
        /// <summary>
        /// Copies the statistics.
        /// </summary>
        /// <param name="lmId">The lm id.</param>
        /// <param name="statistic">The statistic.</param>
        /// <remarks>Documented by Dev08, 2009-02-09</remarks>
        public void CopyStatistics(int lmId, IStatistic statistic)
        {
            if (statistic.StartTimestamp == null || statistic.EndTimestamp == null)     //do not save invalid sessions
            {
                return;
            }

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

            cmd.CommandText = "INSERT INTO LearningSessions(user_id, lm_id, starttime, endtime, 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, @starttime, @endtime, @sumright, @sumwrong, @pool, @b1, @b2, @b3, @b4, @b5, @b6, @b7, @b8, @b9, @b10)";

            cmd.Parameters.Add("@userid", parent.CurrentUser.Id);
            cmd.Parameters.Add("@lmid", lmId);
            cmd.Parameters.Add("@starttime", statistic.StartTimestamp);
            cmd.Parameters.Add("@endtime", statistic.EndTimestamp);
            cmd.Parameters.Add("@sumright", statistic.Right);
            cmd.Parameters.Add("@sumwrong", statistic.Wrong);
            int counter = 0;

            foreach (int box in statistic.Boxes)
            {
                ++counter;
                cmd.Parameters.Add("@b" + counter.ToString(), box);
            }

            cmd.Parameters.Add("@pool", parent.GetParentDictionary().Boxes.Box[0].MaximalSize);     //pool max size => cards in pools

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

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

            using (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser))
            {
                sizes           = new BoxSizes(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0);
                cmd.CommandText = @"SELECT UserCardState.box AS box, count(*) AS count FROM UserCardState, Chapters_Cards
	                                WHERE UserCardState.active=1 and 
		                            Chapters_Cards.cards_id=UserCardState.cards_id and UserCardState.user_id=@user_id and 
		                            Chapters_Cards.chapters_id IN (
			                            SELECT chapters_id FROM SelectedLearnChapters INNER JOIN UserProfilesLearningModulesSettings
					                    ON SelectedLearnChapters.settings_id=UserProfilesLearningModulesSettings.settings_id
				                        WHERE UserProfilesLearningModulesSettings.user_id=@user_id and UserProfilesLearningModulesSettings.lm_id=@lm_id
		                            ) GROUP BY UserCardState.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.Value.Sizes[Convert.ToInt32(reader["box"])] = Convert.ToInt32(reader["count"]);
                }
                reader.Close();

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

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

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

            using (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser))
            {
                cmd.CommandText = "SELECT Boxes.* 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();
                sizes = new BoxSizes(int.MaxValue,
                                     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"]),
                                     int.MaxValue);
                reader.Close();

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

                return(sizes.Value.Sizes[id]);
            }
        }
        public List <ICard> GetCards(int id)
        {
            List <ICard> cardsCache = Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.GetIdentifier(CacheObject.CardsList, id)] as List <ICard>;

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

            using (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser))
            {
                cmd.CommandText = "SELECT cards_id FROM \"LearningModules_Cards\" WHERE lm_id=@id";
                cmd.Parameters.Add("@id", id);
                SqlCeDataReader reader;
                try { reader = MSSQLCEConn.ExecuteReader(cmd); }
                catch { throw new IdAccessException(id); }

                List <ICard> cards = new List <ICard>();
                while (reader.Read())
                {
                    cards.Add(new DbCard(Convert.ToInt32(reader["cards_id"]), false, Parent));
                }
                reader.Close();
                Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.CardsList, id)] = cards;

                return(cards);
            }
        }
Esempio n. 12
0
        public void RecalculateBoxSizes(int sessionId)
        {
            SqlCeCommand cmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser);

            cmd.CommandText = "UPDATE LearningSessions SET pool_content=@pool, box1_content=@b1, box2_content=@b2, box3_content=@b3, " +
                              "box4_content=@b4, box5_content=@b5, box6_content=@b6, box7_content=@b7, box8_content=@b8, box9_content=@b9, box10_content=@b10 WHERE id=@sid AND user_id=@uid AND lm_id=@lmid";
            cmd.Parameters.Add("sid", sessionId);
            cmd.Parameters.Add("@uid", Parent.CurrentUser.Id);
            cmd.Parameters.Add("@lmid", Parent.GetParentDictionary().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;
            }

            MSSQLCEConn.ExecuteNonQuery(cmd);
        }
Esempio n. 13
0
        /// <summary>
        /// Gets the learn sessions.
        /// </summary>
        /// <param name="lmId">The lm id.</param>
        /// <returns></returns>
        /// <remarks>Documented by Dev08, 2009-01-13</remarks>
        public List <int> GetLearnSessions(int lmId)
        {
            object learnSessionsCache   = parent.CurrentUser.Cache[ObjectLifetimeIdentifier.GetIdentifier(CacheObject.StatisticsLearnSessions, lmId)];
            MsSqlCeStatisticConnector c = MsSqlCeStatisticConnector.GetInstance(parent);

            //The data of GetLearnSession can be cached until a new session was created.
            if (runningSessionCopy == c.RunningSession && learnSessionsCache != null)
            {
                return(learnSessionsCache as List <int>);
            }

            //if cache is empty or the RunningSession has changed...
            SqlCeCommand cmd = MSSQLCEConn.CreateCommand(parent.CurrentUser);

            cmd.CommandText = "SELECT id FROM \"LearningSessions\" WHERE lm_id = @lmId AND user_id=@uid ORDER BY endtime ASC";
            cmd.Parameters.Add("@lmId", lmId);
            cmd.Parameters.Add("@uid", parent.CurrentUser.Id);

            List <int>      output = new List <int>();
            SqlCeDataReader reader = MSSQLCEConn.ExecuteReader(cmd);

            while (reader.Read())
            {
                object id           = reader["id"];
                int    id_converted = Convert.ToInt32(id);

                output.Add(id_converted);
            }
            reader.Close();
            runningSessionCopy = c.RunningSession;

            //Save to Cache
            parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.StatisticsLearnSessions, lmId, Cache.DefaultStatisticValidationTime)] = output;
            return(output);
        }
Esempio n. 14
0
        /// <summary>
        /// Deletes the chapter.
        /// </summary>
        /// <param name="lmid">The id of the learning module.</param>
        /// <param name="id">The id.</param>
        /// <remarks>Documented by Dev02, 2008-08-05</remarks>
        /// <remarks>Documented by Dev08, 2009-01-12</remarks>
        public void DeleteChapter(int lmid, int id)
        {
            using (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(parent.CurrentUser))
            {
                cmd.CommandText  = "DELETE FROM LearningModules_Cards WHERE cards_id IN (SELECT cards_id FROM Chapters_Cards WHERE chapters_id=@id); ";
                cmd.CommandText += "DELETE FROM Cards_MediaContent WHERE cards_id IN (SELECT cards_id FROM Chapters_Cards WHERE chapters_id=@id); ";
                cmd.CommandText += "DELETE FROM UserCardState WHERE cards_id IN (SELECT cards_id FROM Chapters_Cards WHERE chapters_id=@id); ";
                cmd.CommandText += "DELETE FROM TextContent WHERE cards_id IN (SELECT cards_id FROM Chapters_Cards WHERE chapters_id=@id); ";
                cmd.CommandText += "DELETE FROM LearnLog WHERE cards_id IN (SELECT cards_id FROM Chapters_Cards WHERE chapters_id=@id); ";
                cmd.CommandText += "DELETE FROM Chapters_Cards WHERE chapters_id=@id; ";
                cmd.CommandText += "DELETE FROM SelectedLearnChapters WHERE chapters_id=@id; ";
                cmd.CommandText += "DELETE FROM Cards_MediaContent WHERE cards_id NOT IN (SELECT cards_id FROM LearningModules_Cards); ";
                cmd.CommandText += "DELETE FROM UserCardState WHERE cards_id NOT IN (SELECT cards_id FROM LearningModules_Cards); ";
                cmd.CommandText += "DELETE FROM TextContent WHERE cards_id NOT IN (SELECT cards_id FROM LearningModules_Cards); ";
                cmd.CommandText += "DELETE FROM LearnLog WHERE cards_id NOT IN (SELECT cards_id FROM LearningModules_Cards); ";
                cmd.CommandText += "DELETE FROM Cards WHERE id NOT IN (SELECT cards_id FROM LearningModules_Cards); ";
                cmd.CommandText += "DELETE FROM Chapters WHERE id=@id";
                cmd.Parameters.Add("@id", id);
                if (MSSQLCEConn.ExecuteNonQuery(cmd) < 1)
                {
                    throw new IdAccessException(id);
                }

                parent.CurrentUser.Cache.Uncache(ObjectLifetimeIdentifier.GetIdentifier(CacheObject.ChaptersList, lmid));
            }
        }
Esempio n. 15
0
        /// <summary>
        /// Gets the description of the chapter.
        /// </summary>
        /// <param name="id">The id.</param>
        /// <returns></returns>
        /// <remarks>Documented by Dev02, 2008-08-05</remarks>
        /// <remarks>Documented by Dev05, 2009-01-15</remarks>
        public string GetDescription(int id)
        {
            string descriptionCache = Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.GetIdentifier(CacheObject.ChapterDescription, id)] as string;

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

            using (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser))
            {
                cmd.CommandText = "SELECT id, description FROM \"Chapters\" WHERE lm_id IN (SELECT lm_id FROM \"Chapters\" WHERE id=@id)";
                cmd.Parameters.Add("@id", id);
                SqlCeDataReader reader = MSSQLCEConn.ExecuteReader(cmd);

                string chapterDescription = string.Empty;
                while (reader.Read())
                {
                    int    chip        = Convert.ToInt32(reader["id"]);
                    string chapterDesc = reader["description"].ToString();
                    if (id == chip)
                    {
                        chapterDescription = chapterDesc;
                    }

                    Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.ChapterDescription, chip)] = chapterDesc;
                }
                reader.Close();

                return(chapterDescription);
            }
        }
        /// <summary>
        /// Creates the new word.
        /// </summary>
        /// <param name="id">The id.</param>
        /// <param name="word">The word.</param>
        /// <param name="side">The side.</param>
        /// <param name="type">The type.</param>
        /// <param name="isDefault">if set to <c>true</c> [is default].</param>
        /// <returns></returns>
        /// <remarks>Documented by Dev03, 2009-01-09</remarks>
        public IWord CreateNewWord(int id, string word, Side side, WordType type, bool isDefault)
        {
            if (word != null)
            {
                SqlCeCommand cmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser);
                cmd.CommandText = "SELECT position FROM TextContent WHERE cards_id=@id AND side=@side AND type=@type ORDER BY position DESC";
                cmd.Parameters.Add("@id", id);
                cmd.Parameters.Add("@side", side.ToString());
                cmd.Parameters.Add("@type", type.ToString());

                int    currentPos = 0;
                object retval     = MSSQLCEConn.ExecuteScalar(cmd);
                if (retval != DBNull.Value)
                {
                    currentPos = Convert.ToInt32(retval);
                }
                cmd.Parameters.Clear();

                cmd.CommandText = @"INSERT INTO TextContent (cards_id, text, side, type, position, is_default) VALUES (@id, @text, @side, @type, @position, @isdefault); SELECT @@IDENTITY;";
                cmd.Parameters.Add("@id", id);
                cmd.Parameters.Add("@text", word);
                cmd.Parameters.Add("@side", side.ToString());
                cmd.Parameters.Add("@type", type.ToString());
                cmd.Parameters.Add("@position", currentPos + 10);
                cmd.Parameters.Add("@isdefault", isDefault);

                Parent.CurrentUser.Cache.Uncache(ObjectLifetimeIdentifier.GetIdentifier(ObjectLifetimeIdentifier.GetCacheObject(side, type), id));

                return(new DbWord(Convert.ToInt32(MSSQLCEConn.ExecuteScalar(cmd)), word, type, isDefault, Parent));
            }
            else
            {
                return(null);
            }
        }
        /// <summary>
        /// Checks if the card exists and throws an IdAccessException if not.
        /// </summary>
        /// <param name="id">The card id.</param>
        /// <remarks>Documented by Dev03, 2008-08-06</remarks>
        /// <remarks>Documented by Dev08, 2009-01-09</remarks>
        public void CheckCardId(int id)
        {
            List <int> cardIdsCache = parent.CurrentUser.Cache[ObjectLifetimeIdentifier.GetIdentifier(CacheObject.CardIdsList, 0)] as List <int>;

            if (cardIdsCache != null && cardIdsCache.Contains(id))
            {
                return;
            }

            using (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(parent.CurrentUser))
            {
                cmd.CommandText = "(SELECT lm_id FROM \"LearningModules_Cards\" WHERE cards_id=@id)";
                cmd.Parameters.Add("@id", id);
                int lmId = Convert.ToInt32(MSSQLCEConn.ExecuteScalar(cmd));
                cmd.Parameters.Clear();

                cmd.CommandText = "SELECT id FROM \"Cards\" WHERE id IN " +
                                  "(SELECT cards_id FROM \"LearningModules_Cards\" WHERE lm_id=@lm_id)";
                cmd.Parameters.Add("@lm_id", lmId);
                SqlCeDataReader reader;
                try { reader = MSSQLCEConn.ExecuteReader(cmd); }
                catch { throw new IdAccessException(id); }

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

                parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.CardIdsList, 0)] = cardIds;
            }
        }
Esempio n. 18
0
        /// <summary>
        /// Gets the title of the chapter.
        /// </summary>
        /// <param name="id">The id.</param>
        /// <returns></returns>
        /// <remarks>Documented by Dev02, 2008-08-05</remarks>
        /// <remarks>Documented by Dev05, 2009-01-15</remarks>
        public string GetTitle(int id)
        {
            string titleCache = Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.GetIdentifier(CacheObject.ChapterTitle, id)] as string;

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

            using (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser))
            {
                cmd.CommandText = "SELECT id, title FROM \"Chapters\" WHERE lm_id IN (SELECT lm_id FROM \"Chapters\" WHERE id=@id);";
                cmd.Parameters.Add("@id", id);
                SqlCeDataReader reader = MSSQLCEConn.ExecuteReader(cmd);

                string title = string.Empty;
                while (reader.Read())
                {
                    int    chapterId    = Convert.ToInt32(reader["id"]);
                    string chapterTitle = Convert.ToString(reader["title"]);
                    if (id == chapterId)
                    {
                        title = chapterTitle;
                    }

                    Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.ChapterTitle, chapterId, new TimeSpan(0, 10, 0))] = chapterTitle;
                }
                reader.Close();

                return(title);
            }
        }
        /// <summary>
        /// Sets the chapter for a card.
        /// </summary>
        /// <param name="id">The card id.</param>
        /// <param name="chapter">The chapter id.</param>
        /// <remarks>Documented by Dev03, 2008-08-06</remarks>
        /// <remarks>Documented by Dev08, 2009-01-09</remarks>
        public void SetChapter(int id, int chapter)
        {
            using (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(parent.CurrentUser))
            {
                SqlCeTransaction transaction = cmd.Connection.BeginTransaction();
                cmd.CommandText = "SELECT count(*) FROM \"Chapters\" WHERE id=@chapterid";
                cmd.Parameters.Add("chapterid", chapter);
                if (Convert.ToInt32(MSSQLCEConn.ExecuteScalar(cmd)) < 1)
                {
                    throw new IdAccessException(chapter);
                }
                Dictionary <int, int> cardChapterCache = parent.CurrentUser.Cache[ObjectLifetimeIdentifier.GetIdentifier(CacheObject.CardChapterList, 0)] as Dictionary <int, int>;
                if (cardChapterCache != null)
                {
                    cardChapterCache[id] = chapter;
                }

                using (SqlCeCommand cmd2 = MSSQLCEConn.CreateCommand(parent.CurrentUser))
                {
                    cmd2.CommandText  = "DELETE FROM \"Chapters_Cards\" WHERE cards_id=@id; ";
                    cmd2.CommandText += "INSERT INTO \"Chapters_Cards\" (chapters_id, cards_id) VALUES (@chapterid, @id);";
                    cmd2.CommandText += "UPDATE Cards SET chapters_id=@chapterid WHERE id=@id;";
                    cmd2.Parameters.Add("@chapterid", chapter);
                    cmd2.Parameters.Add("@id", id);
                    MSSQLCEConn.ExecuteNonQuery(cmd2);
                }
                transaction.Commit();
            }
        }
Esempio n. 20
0
        /// <summary>
        /// Deletes the media object.
        /// </summary>
        /// <param name="id">The id.</param>
        /// <remarks>Documented by Dev03, 2008-08-05</remarks>
        /// <remarks>Documented by Dev03, 2009-01-13</remarks>
        public void DeleteMedia(int id)
        {
            SqlCeCommand deletecmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser);

            deletecmd.CommandText = "DELETE FROM MediaProperties WHERE media_id=@id; DELETE FROM MediaContent WHERE id=@id;";
            deletecmd.Parameters.Add("@id", id);
            MSSQLCEConn.ExecuteNonQuery(deletecmd);
        }
        /// <summary>
        /// Gets the default.
        /// </summary>
        /// <param name="id">The id.</param>
        /// <returns></returns>
        /// <remarks>Documented by Dev03, 2009-01-09</remarks>
        public bool GetDefault(int id)
        {
            SqlCeCommand cmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser);

            cmd.CommandText = "SELECT is_default FROM TextContent WHERE id=@id";
            cmd.Parameters.Add("@id", id);
            return(Convert.ToBoolean(MSSQLCEConn.ExecuteScalar(cmd)));
        }
        /// <summary>
        /// Gets the word.
        /// </summary>
        /// <param name="id">The id.</param>
        /// <returns></returns>
        /// <remarks>Documented by Dev03, 2009-01-09</remarks>
        public string GetWord(int id)
        {
            SqlCeCommand cmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser);

            cmd.CommandText = "SELECT text FROM TextContent WHERE id=@id";
            cmd.Parameters.Add("@id", id);
            return(MSSQLCEConn.ExecuteScalar(cmd).ToString());
        }
        /// <summary>
        /// Gets the type.
        /// </summary>
        /// <param name="id">The id.</param>
        /// <returns></returns>
        /// <remarks>Documented by Dev03, 2009-01-09</remarks>
        public WordType GetType(int id)
        {
            SqlCeCommand cmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser);

            cmd.CommandText = "SELECT type FROM TextContent WHERE id=@id";
            cmd.Parameters.Add("@id", id);
            return((WordType)Enum.Parse(typeof(WordType), MSSQLCEConn.ExecuteScalar(cmd).ToString(), true));
        }
        /// <summary>
        /// Gets the chapter for a card.
        /// </summary>
        /// <param name="id">The card id.</param>
        /// <returns></returns>
        /// <remarks>Documented by Dev03, 2008-08-06</remarks>
        /// <remarks>Documented by Dev08, 2009-01-09</remarks>
        public int GetChapter(int id)
        {
            Dictionary <int, int> cardChaptersCache = parent.CurrentUser.Cache[ObjectLifetimeIdentifier.GetIdentifier(CacheObject.CardChapterList, 0)] as Dictionary <int, int>;

            if (cardChaptersCache != null && cardChaptersCache.ContainsKey(id))
            {
                return(cardChaptersCache[id]);
            }

            using (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(parent.CurrentUser))
            {
                cmd.CommandText = "(SELECT lm_id FROM \"LearningModules_Cards\" WHERE cards_id=@id)";
                cmd.Parameters.Add("@id", id);
                int lmid = Convert.ToInt32(MSSQLCEConn.ExecuteScalar(cmd));

                cmd.Parameters.Clear();

                cmd.CommandText = "SELECT chapters_id, cards_id FROM Chapters_Cards WHERE chapters_id IN " +
                                  "(SELECT chapters_id FROM Chapters WHERE lm_id=@lmid)";
                cmd.Parameters.Add("@lmid", lmid);
                SqlCeDataReader reader = MSSQLCEConn.ExecuteReader(cmd);

                Dictionary <int, int> cardChapters = new Dictionary <int, int>();

                while (reader.Read())
                {
                    object[] chapters = new object[2];
                    reader.GetValues(chapters);
                    int chid = Convert.ToInt32(chapters[0]);
                    int cid  = Convert.ToInt32(chapters[1]);
                    if (!cardChapters.ContainsKey(cid))
                    {
                        cardChapters[cid] = chid;
                    }
                }
                reader.Close();

                // this should fix the bug where for some reason a card didn't have chapter assigned [ML-1708] (and similar)
                int chapterId = 0;
                if (!cardChapters.TryGetValue(id, out chapterId))
                {
                    lock (cardChapters)
                    {
                        foreach (int c in cardChapters.Values)
                        {
                            chapterId = c;
                            break;
                        }
                        SetChapter(id, chapterId);
                        cardChapters.Add(id, chapterId);
                    }
                }

                parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.CardChapterList, 0, new TimeSpan(23, 59, 59))] = cardChapters;

                return(chapterId);
            }
        }
Esempio n. 25
0
 /// <summary>
 /// Gets the GUID.
 /// </summary>
 /// <param name="id">The id.</param>
 /// <returns></returns>
 /// <remarks>Documented by Dev08, 2009-01-12</remarks>
 public string GetGuid(int id)
 {
     using (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(parent.CurrentUser))
     {
         cmd.CommandText = "SELECT guid FROM \"LearningModules\" WHERE id=@id";
         cmd.Parameters.Add("@id", id);
         return(Convert.ToString(MSSQLCEConn.ExecuteScalar(cmd)));
     }
 }
        /// <summary>
        /// Sets the default.
        /// </summary>
        /// <param name="id">The id.</param>
        /// <param name="Default">if set to <c>true</c> [default].</param>
        /// <remarks>Documented by Dev03, 2009-01-09</remarks>
        public void SetDefault(int id, bool Default)
        {
            SqlCeCommand cmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser);

            cmd.CommandText = "UPDATE TextContent SET is_default=@isdefault WHERE id=@id";
            cmd.Parameters.Add("@id", id);
            cmd.Parameters.Add("@isdefault", Default);
            MSSQLCEConn.ExecuteNonQuery(cmd);
        }
Esempio n. 27
0
        /// <summary>
        /// Determines whether this media is available.
        /// </summary>
        /// <param name="id">The id.</param>
        /// <returns>
        ///     <c>true</c> if media is available; otherwise, <c>false</c>.
        /// </returns>
        /// <remarks>Documented by Dev05, 2009-03-30</remarks>
        public bool IsMediaAvailable(int id)
        {
            SqlCeCommand cmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser);

            cmd.CommandText = "SELECT count(*) FROM MediaContent WHERE id=@id AND data IS NOT NULL";
            cmd.Parameters.Add("@id", id);

            return(MSSQLCEConn.ExecuteScalar <int>(cmd).Value > 0);
        }
Esempio n. 28
0
 /// <summary>
 /// Gets the associated lm id of the chapter.
 /// </summary>
 /// <param name="id">The id.</param>
 /// <returns></returns>
 /// <remarks>Documented by Dev02, 2008-08-05</remarks>
 /// <remarks>Documented by Dev05, 2009-01-15</remarks>
 public int GetLmId(int id)
 {
     using (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser))
     {
         cmd.CommandText = "SELECT lm_id FROM \"Chapters\" WHERE id=@id";
         cmd.Parameters.Add("@id", id);
         return(Convert.ToInt32(MSSQLCEConn.ExecuteScalar(cmd)));
     }
 }
 /// <summary>
 /// Clears the media for card style.
 /// </summary>
 /// <param name="Id">The style id.</param>
 /// <remarks>Documented by Dev03, 2009-03-05</remarks>
 /// <remarks>Documented by Dev03, 2009-03-05</remarks>
 public void ClearMediaForCardStyle(int Id)
 {
     using (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(parent.CurrentUser))
     {
         cmd.CommandText = "DELETE FROM \"MediaContent_CardStyles\" WHERE cardstyles_id = @cardstyle_id";
         cmd.Parameters.Add("@cardstyle_id", Id);
         MSSQLCEConn.ExecuteNonQuery(cmd);
     }
 }
        /// <summary>
        /// Sets the word.
        /// </summary>
        /// <param name="id">The id.</param>
        /// <param name="Word">The word.</param>
        /// <remarks>Documented by Dev03, 2009-01-09</remarks>
        public void SetWord(int id, string Word)
        {
            SqlCeCommand cmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser);

            cmd.CommandText = "UPDATE TextContent SET text=@text WHERE id=@id";
            cmd.Parameters.Add("@id", id);
            cmd.Parameters.Add("@text", Word);
            MSSQLCEConn.ExecuteNonQuery(cmd);
        }