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;
}
