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 (NpgsqlConnection con = PostgreSQLConn.CreateConnection(Parent.CurrentUser))
            {
                using (NpgsqlCommand cmd = con.CreateCommand())
                {
                    sizes = new BoxSizes(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0);
                    cmd.CommandText = @"SELECT box, count(*) AS count FROM ""UserCardState"" WHERE active=true and user_id=:user_id and cards_id IN (SELECT cards_id FROM ""LearningModules_Cards"" WHERE lm_id=:lm_id) GROUP BY box";
                    cmd.Parameters.Add("user_id", Parent.CurrentUser.Id);
                    cmd.Parameters.Add("lm_id", Parent.CurrentUser.ConnectionString.LmId);
                    NpgsqlDataReader reader = PostgreSQLConn.ExecuteReader(cmd, Parent.CurrentUser, false);
                    while (reader.Read())
                        sizes.Value.Sizes[Convert.ToInt32(reader["box"])] = Convert.ToInt32(reader["count"]);

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

                    return sizes.Value.Sizes[id];
                }
            }
        }
        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 (NpgsqlConnection con = PostgreSQLConn.CreateConnection(Parent.CurrentUser))
            {
                using (NpgsqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = string.Format("SELECT * FROM \"Boxes\"" +
                        "WHERE id=(SELECT boxes FROM \"Settings\" WHERE id=(SELECT default_settings_id FROM \"LearningModules\" WHERE id=:lm_id))", id);
                    cmd.Parameters.Add("lm_id", Parent.CurrentUser.ConnectionString.LmId);
                    NpgsqlDataReader reader = PostgreSQLConn.ExecuteReader(cmd, Parent.CurrentUser);
                    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);

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

                    return sizes.Value.Sizes[id];
                }
            }
        }
        /// <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];
            }
        }
        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 (NpgsqlConnection con = PostgreSQLConn.CreateConnection(Parent.CurrentUser))
            {
                using (NpgsqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = string.Format("SELECT * FROM \"GetBoxSizes\"(:uid, :lm_id)", id);
                    cmd.Parameters.Add("uid", Parent.CurrentUser.Id);
                    cmd.Parameters.Add("lm_id", Parent.CurrentUser.ConnectionString.LmId);
                    NpgsqlDataReader reader = PostgreSQLConn.ExecuteReader(cmd, Parent.CurrentUser);
                    reader.Read();
                    sizes = new BoxSizes(Convert.ToInt32(reader["box0"]),
                        Convert.ToInt32(reader["box1"]),
                        Convert.ToInt32(reader["box2"]),
                        Convert.ToInt32(reader["box3"]),
                        Convert.ToInt32(reader["box4"]),
                        Convert.ToInt32(reader["box5"]),
                        Convert.ToInt32(reader["box6"]),
                        Convert.ToInt32(reader["box7"]),
                        Convert.ToInt32(reader["box8"]),
                        Convert.ToInt32(reader["box9"]),
                        Convert.ToInt32(reader["box10"]));

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

                    return sizes.Value.Sizes[id];
                }
            }
        }
        /// <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 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 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];
            }
        }
        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;
            }
        }
        private BoxSizes GetCurrentBoxContent()
        {
            BoxSizes sizes;
            using (NpgsqlConnection con = PostgreSQLConn.CreateConnection(Parent.CurrentUser))
            {
                using (NpgsqlCommand cmd = con.CreateCommand())
                {
                    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 = TRUE 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);

                    NpgsqlDataReader reader = PostgreSQLConn.ExecuteReader(cmd, Parent.CurrentUser);
                    while (reader.Read())
                        sizes.Sizes[Convert.ToInt32(reader["box"])] = Convert.ToInt32(reader["count"]);
                }
            }
            return sizes;
        }