public ISettings GetUserSettings(int id)
{
DbSettings settingsCache = Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.GetIdentifier(CacheObject.UserLearningModuleSettings, id)] as DbSettings;
if (settingsCache != null)
{
return(settingsCache);
}
using (NpgsqlConnection con = PostgreSQLConn.CreateConnection(Parent.CurrentUser))
{
using (NpgsqlCommand cmd = con.CreateCommand())
{
cmd.CommandText = "SELECT \"GetUserSettings\"(:uid, :lm_id);";
cmd.Parameters.Add("uid", Parent.CurrentUser.Id);
cmd.Parameters.Add("lm_id", id);
int?settingsid = PostgreSQLConn.ExecuteScalar <int>(cmd, Parent.CurrentUser);
if (!settingsid.HasValue)
{
return(null);
}
DbSettings settings = new DbSettings(settingsid.Value, false, Parent);
Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.UserLearningModuleSettings, id, Cache.DefaultSettingsValidationTime)] = settings;
return(settings);
}
}
}
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 (NpgsqlConnection con = PostgreSQLConn.CreateConnection(Parent.CurrentUser))
{
using (NpgsqlCommand cmd = con.CreateCommand())
{
cmd.CommandText = "SELECT id FROM \"Cards\" WHERE id IN (SELECT cards_id FROM \"LearningModules_Cards\" WHERE lm_id=:id)";
cmd.Parameters.Add("id", id);
NpgsqlDataReader reader;
try { reader = PostgreSQLConn.ExecuteReader(cmd, Parent.CurrentUser); }
catch { throw new IdAccessException(id); }
List <ICard> cards = new List <ICard>();
while (reader.Read())
{
cards.Add(new DbCard(Convert.ToInt32(reader["id"]), false, Parent));
}
Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.CardsList, id)] = cards;
return(cards);
}
}
}
public IWord CreateNewWord(int id, string word, Side side, WordType type, bool isDefault)
{
if (word != null)
{
using (NpgsqlConnection con = PostgreSQLConn.CreateConnection(Parent.CurrentUser))
{
using (NpgsqlCommand cmd = con.CreateCommand())
{
cmd.CommandText = "INSERT INTO \"TextContent\" (cards_id, text, side, type, position, is_default) VALUES (:id, :text, :side, :type, " +
"(COALESCE((SELECT position FROM \"TextContent\" WHERE cards_id=:id AND side=:side AND type=:type ORDER BY position DESC LIMIT 1), 0) + 10), " +
":isdefault) RETURNING id";
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("isdefault", isDefault);
Parent.CurrentUser.Cache.Uncache(ObjectLifetimeIdentifier.GetIdentifier(ObjectLifetimeIdentifier.GetCacheObject(side, type), id));
return(new DbWord(Convert.ToInt32(PostgreSQLConn.ExecuteScalar(cmd, Parent.CurrentUser)), word, type, isDefault, Parent));
}
}
}
else
{
return(null);
}
}
/// <summary>
/// Gets the extension stream.
/// </summary>
/// <param name="guid">The GUID.</param>
/// <returns></returns>
public Stream GetExtensionStream(Guid guid)
{
MemoryStream stream = null;
using (NpgsqlConnection con = PostgreSQLConn.CreateConnection(Parent.CurrentUser))
{
int noid = 0;
using (NpgsqlCommand cmd = con.CreateCommand())
{
cmd.CommandText = "SELECT data FROM \"Extensions\" WHERE guid=:guid;";
cmd.Parameters.Add("guid", guid.ToString());
object obj = PostgreSQLConn.ExecuteScalar(cmd, Parent.CurrentUser);
if (obj == null || obj == DBNull.Value || !(obj as long?).HasValue)
{
return(stream);
}
noid = Convert.ToInt32((obj as long?).Value);
}
NpgsqlTransaction tran = con.BeginTransaction();
try
{
LargeObjectManager lbm = new LargeObjectManager(con);
LargeObject largeObject = lbm.Open(noid, LargeObjectManager.READWRITE);
byte[] buffer = LargeObjectToBuffer(largeObject);
stream = new MemoryStream(buffer);
largeObject.Close();
}
catch { }
finally { tran.Commit(); }
}
return(stream);
}
/// <summary>
/// Sets the extension actions.
/// </summary>
/// <param name="guid">The GUID.</param>
/// <param name="extensionActions">The extension actions.</param>
public void SetExtensionActions(Guid guid, IList <ExtensionAction> extensionActions)
{
using (NpgsqlConnection con = PostgreSQLConn.CreateConnection(Parent.CurrentUser))
{
NpgsqlTransaction tran = con.BeginTransaction();
using (NpgsqlCommand cmd = con.CreateCommand())
{
cmd.CommandText = "DELETE FROM \"ExtensionActions\" WHERE guid=:guid";
cmd.Parameters.Add("guid", guid.ToString());
PostgreSQLConn.ExecuteNonQuery(cmd, Parent.CurrentUser);
}
foreach (ExtensionAction action in extensionActions)
{
using (NpgsqlCommand cmd = con.CreateCommand())
{
cmd.CommandText = "INSERT INTO \"ExtensionActions\" (guid, action, execution) VALUES (:guid, :action, :execution)";
cmd.Parameters.Add("guid", guid.ToString());
cmd.Parameters.Add("action", action.Kind.ToString());
cmd.Parameters.Add("execution", action.Execution.ToString());
PostgreSQLConn.ExecuteNonQuery(cmd, Parent.CurrentUser);
}
}
tran.Commit();
}
}
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);
}
public void RecalculateBoxSizes(int sessionId)
{
using (NpgsqlConnection con = PostgreSQLConn.CreateConnection(Parent.CurrentUser))
{
using (NpgsqlCommand cmd = con.CreateCommand())
{
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;
}
PostgreSQLConn.ExecuteNonQuery(cmd, Parent.CurrentUser);
}
}
}
/// <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;
}
using (NpgsqlConnection con = PostgreSQLConn.CreateConnection(parent.CurrentUser))
{
using (NpgsqlCommand cmd = con.CreateCommand())
{
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
PostgreSQLConn.ExecuteNonQuery(cmd, parent.CurrentUser);
}
}
}
/// <summary>
/// Gets the number of all Media Objects/Files in this LearningModules.
/// </summary>
/// <param name="id">The id.</param>
/// <returns></returns>
/// <remarks>Documented by Dev08, 2008-10-02</remarks>
public int GetDictionaryMediaObjectsCount(int id)
{
using (NpgsqlConnection con = PostgreSQLConn.CreateConnection(Parent.CurrentUser))
{
using (NpgsqlCommand cmd = con.CreateCommand())
{
int?mediaSizeObjectsCount;
cmd.CommandText = "SELECT COUNT(*) AS LearningModuleMediaObjectsCount" +
" FROM \"MediaContent\", \"Cards_MediaContent\", \"MediaProperties\"" +
" WHERE \"Cards_MediaContent\".media_id = \"MediaContent\".id AND \"Cards_MediaContent\".cards_id IN" +
" (SELECT id FROM \"Cards\", \"LearningModules_Cards\" WHERE \"LearningModules_Cards\".cards_id = \"Cards\".id AND" +
" \"LearningModules_Cards\".lm_id = :id) AND \"MediaProperties\".media_id = \"MediaContent\".id AND" +
"\"MediaProperties\".property = 'MediaSize'";
cmd.Parameters.Add("id", id);
mediaSizeObjectsCount = PostgreSQLConn.ExecuteScalar <int>(cmd, Parent.CurrentUser);
if (!mediaSizeObjectsCount.HasValue)
{
return(0);
}
return(mediaSizeObjectsCount.Value);
}
}
}
public IList <int> GetLMIds()
{
using (NpgsqlConnection con = PostgreSQLConn.CreateConnection(Parent.CurrentUser))
{
using (NpgsqlCommand cmd = con.CreateCommand())
{
IList <int> ids = new List <int>();
cmd.CommandText = "SELECT * FROM \"LearningModules\"";
NpgsqlDataReader reader = PostgreSQLConn.ExecuteReader(cmd, Parent.CurrentUser);
int id;
while (reader.Read())
{
id = Convert.ToInt32(reader["id"]);
Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.DefaultLearningModuleSettings, id, new TimeSpan(0, 10, 0))] =
new DbSettings(Convert.ToInt32(reader["default_settings_id"]), false, Parent);
Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.LearningModuleAuthor, id)] = Convert.ToString(reader["author"]);
Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.LearningModuleTitle, id)] = Convert.ToString(reader["title"]);
ids.Add(id);
}
return(ids);
}
}
}
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 (NpgsqlConnection con = PostgreSQLConn.CreateConnection(Parent.CurrentUser))
{
using (NpgsqlCommand cmd = con.CreateCommand())
{
cmd.CommandText = "SELECT id FROM \"Chapters\" WHERE lm_id=:lmid ORDER BY position ASC";
cmd.Parameters.Add("lmid", lmid);
NpgsqlDataReader reader = PostgreSQLConn.ExecuteReader(cmd, Parent.CurrentUser);
while (reader.Read())
{
list.Add(Convert.ToInt32(reader["id"]));
}
}
}
Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.ChaptersList, lmid)] = list;
return(list);
}
public int AddNewLM(string guid, int categoryId, string title, string licenceKey, bool contentProtected, int calCount)
{
using (NpgsqlConnection con = PostgreSQLConn.CreateConnection(Parent.CurrentUser))
{
int?lmId;
using (NpgsqlCommand cmd = con.CreateCommand())
{
cmd.CommandText = "SELECT \"CreateNewLearningModule\"(:guid, (SELECT id FROM \"Categories\" WHERE global_id=:categoryid), :title)";
cmd.Parameters.Add("guid", guid);
cmd.Parameters.Add("categoryid", categoryId);
cmd.Parameters.Add("title", title);
lmId = PostgreSQLConn.ExecuteScalar <int>(cmd, Parent.CurrentUser);
}
using (NpgsqlCommand cmd = con.CreateCommand())
{
cmd.CommandText = "UPDATE \"LearningModules\" SET licence_key=:lk, content_protected=:cp, cal_count=:cals WHERE id=:id";
cmd.Parameters.Add("id", lmId);
cmd.Parameters.Add("lk", licenceKey);
cmd.Parameters.Add("cp", contentProtected);
cmd.Parameters.Add("cals", calCount);
PostgreSQLConn.ExecuteNonQuery(cmd, Parent.CurrentUser);
}
return(lmId.Value);
}
}
public string GetDescription(int id)
{
string descriptionCache = Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.GetIdentifier(CacheObject.ChapterDescription, id)] as string;
if (descriptionCache != null)
{
return(descriptionCache);
}
using (NpgsqlConnection con = PostgreSQLConn.CreateConnection(Parent.CurrentUser))
{
using (NpgsqlCommand cmd = con.CreateCommand())
{
cmd.CommandText = "SELECT id, description FROM \"Chapters\" WHERE lm_id=(SELECT lm_id FROM \"Chapters\" WHERE id=:id)";
cmd.Parameters.Add("id", id);
NpgsqlDataReader reader = PostgreSQLConn.ExecuteReader(cmd, Parent.CurrentUser);
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;
}
return(chapterDescription);
}
}
}
/// <summary>
/// Gets the start time stamp.
/// </summary>
/// <param name="sessionId">The session id.</param>
/// <returns></returns>
/// <remarks>Documented by Dev08, 2009-01-28</remarks>
public DateTime?GetStartTimeStamp(int sessionId)
{
object startTimeStamp = parent.CurrentUser.Cache[ObjectLifetimeIdentifier.GetIdentifier(CacheObject.StatisticStartTime, sessionId)];
if (startTimeStamp == null || RunningSession == sessionId)
{
using (NpgsqlConnection con = PostgreSQLConn.CreateConnection(parent.CurrentUser))
{
using (NpgsqlCommand cmd = con.CreateCommand())
{
cmd.CommandText = "SELECT starttime FROM \"LearningSessions\" WHERE \"LearningSessions\".id = :sessId AND user_id=:uid AND lm_id=:lmid";
cmd.Parameters.Add("sessId", sessionId);
cmd.Parameters.Add("uid", parent.CurrentUser.Id);
cmd.Parameters.Add("lmid", parent.GetParentDictionary().Id);
DateTime?dt = PostgreSQLConn.ExecuteScalar <DateTime>(cmd, parent.CurrentUser);
//Save to Cache
parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.StatisticStartTime, sessionId, Cache.DefaultStatisticValidationTime)] = dt;
return(dt);
}
}
}
return(startTimeStamp as DateTime?);
}
/// <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, MediaProperty property, string value)
{
using (NpgsqlConnection con = PostgreSQLConn.CreateConnection(Parent.CurrentUser))
{
NpgsqlTransaction tran = con.BeginTransaction();
using (NpgsqlCommand cmd = con.CreateCommand())
{
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;";
}
cmd.Parameters.Add("media_id", id);
cmd.Parameters.Add("property", property.ToString());
cmd.Parameters.Add("value", value);
PostgreSQLConn.ExecuteNonQuery(cmd, Parent.CurrentUser);
}
tran.Commit();
}
}
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]);
}
}
}
/// <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)
{
using (NpgsqlConnection conn = PostgreSQLConn.CreateConnection(Parent.CurrentUser))
{
int noid = 0;
using (NpgsqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT data FROM \"MediaContent\" WHERE id=:id;";
cmd.Parameters.Add("id", id);
noid = Convert.ToInt32(PostgreSQLConn.ExecuteScalar(cmd, Parent.CurrentUser));
}
NpgsqlTransaction tran = conn.BeginTransaction();
LargeObjectManager lbm = new LargeObjectManager(conn);
lbm.Delete(noid);
using (NpgsqlCommand deletecmd = conn.CreateCommand())
{
deletecmd.CommandText = "DELETE FROM \"MediaContent\" WHERE id=:id;";
deletecmd.Parameters.Add("id", id);
PostgreSQLConn.ExecuteNonQuery(deletecmd, Parent.CurrentUser);
}
tran.Commit();
}
}
/// <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 <MediaProperty, string> properties)
{
using (NpgsqlConnection conn = PostgreSQLConn.CreateConnection(Parent.CurrentUser))
{
NpgsqlTransaction tran = conn.BeginTransaction();
using (NpgsqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "DELETE FROM \"MediaProperties\" WHERE media_id=:id;";
cmd.Parameters.Add("id", id);
PostgreSQLConn.ExecuteNonQuery(cmd, Parent.CurrentUser);
}
foreach (KeyValuePair <MediaProperty, string> item in properties)
{
using (NpgsqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "INSERT INTO \"MediaProperties\" (media_id, property, \"value\") VALUES (:media_id, :property, :value);";
cmd.Parameters.Add("media_id", id);
cmd.Parameters.Add("property", item.Key.ToString());
cmd.Parameters.Add("value", item.Value);
PostgreSQLConn.ExecuteNonQuery(cmd, Parent.CurrentUser);
}
}
tran.Commit();
}
}
/// <summary>
/// Creates a new media object.
/// </summary>
/// <param name="media">The memory stream containing the media.</param>
/// <param name="type">The media type.</param>
/// <param name="rpu">A delegate of type <see cref="StatusMessageReportProgress"/> used to send messages back to the calling object.</param>
/// <param name="caller">The calling object.</param>
/// <returns>The id for the new media object.</returns>
/// <remarks>Documented by Dev03, 2008-08-05</remarks>
/// <remarks>Documented by Dev03, 2009-01-13</remarks>
public int CreateMedia(Stream media, EMedia type, StatusMessageReportProgress rpu, object caller)
{
using (NpgsqlConnection conn = PostgreSQLConn.CreateConnection(Parent.CurrentUser))
{
NpgsqlTransaction tran = conn.BeginTransaction();
LargeObjectManager lbm = new LargeObjectManager(conn);
int noid = lbm.Create(LargeObjectManager.READWRITE);
LargeObject largeObject = lbm.Open(noid, LargeObjectManager.READWRITE);
byte[] buffer = new byte[media.Length];
media.Read(buffer, 0, (int)media.Length);
BufferToLargeObject(buffer, largeObject, rpu, caller);
largeObject.Close();
int newId = 0;
using (NpgsqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "INSERT INTO \"MediaContent\" (data, media_type) VALUES (:data, :type) RETURNING id;";
cmd.Parameters.Add("data", noid);
cmd.Parameters.Add("type", type.ToString());
newId = Convert.ToInt32(PostgreSQLConn.ExecuteScalar(cmd, Parent.CurrentUser));
}
tran.Commit();
return(newId);
}
}
/// <summary>
/// Updates the media.
/// </summary>
/// <param name="id">The id.</param>
/// <param name="media">The media.</param>
/// <remarks>Documented by Dev02, 2008-08-06</remarks>
/// <remarks>Documented by Dev03, 2009-01-13</remarks>
public void UpdateMedia(int id, Stream media)
{
using (NpgsqlConnection con = PostgreSQLConn.CreateConnection(Parent.CurrentUser))
{
NpgsqlTransaction tran = con.BeginTransaction();
int noid;
using (NpgsqlCommand cmd = con.CreateCommand())
{
cmd.CommandText = "SELECT data FROM \"MediaContent\" WHERE id=:id";
cmd.Parameters.Add("id", id);
noid = Convert.ToInt32(PostgreSQLConn.ExecuteScalar(cmd, Parent.CurrentUser));
}
LargeObjectManager lbm = new LargeObjectManager(con);
lbm.Delete(noid);
noid = lbm.Create(LargeObjectManager.READWRITE);
LargeObject largeObject = lbm.Open(noid, LargeObjectManager.READWRITE);
byte[] buffer = new byte[media.Length];
media.Read(buffer, 0, (int)media.Length);
BufferToLargeObject(buffer, largeObject);
largeObject.Close();
using (NpgsqlCommand cmd = con.CreateCommand())
{
cmd.CommandText = "UPDATE \"MediaContent\" SET data=:data WHERE id=:id";
cmd.Parameters.Add("id", id);
cmd.Parameters.Add("data", noid);
PostgreSQLConn.ExecuteNonQuery(cmd, Parent.CurrentUser);
}
tran.Commit();
}
}
/// <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 Dev03, 2009-01-13</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 (NpgsqlConnection con = PostgreSQLConn.CreateConnection(Parent.CurrentUser))
{
using (NpgsqlCommand cmd = con.CreateCommand())
{
cmd.CommandText = "SELECT id FROM \"Cards\" WHERE id IN " +
"(SELECT cards_id FROM \"LearningModules_Cards\" WHERE lm_id=(SELECT lm_id FROM \"LearningModules_Cards\" WHERE cards_id=:id))";
cmd.Parameters.Add("id", id);
NpgsqlDataReader reader;
try { reader = PostgreSQLConn.ExecuteReader(cmd, Parent.CurrentUser); }
catch { throw new IdAccessException(id); }
List <int> cardIds = new List <int>();
while (reader.Read())
{
cardIds.Add(Convert.ToInt32(reader["id"]));
}
Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.CardIdsList, 0)] = cardIds;
}
}
}
public string GetTitle(int id)
{
string titleCache = Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.GetIdentifier(CacheObject.ChapterTitle, id)] as string;
if (titleCache != null)
{
return(titleCache);
}
using (NpgsqlConnection con = PostgreSQLConn.CreateConnection(Parent.CurrentUser))
{
using (NpgsqlCommand cmd = con.CreateCommand())
{
cmd.CommandText = "SELECT id, title FROM \"Chapters\" WHERE lm_id=(SELECT lm_id FROM \"Chapters\" WHERE id=:id)";
cmd.Parameters.Add("id", id);
NpgsqlDataReader reader = PostgreSQLConn.ExecuteReader(cmd, Parent.CurrentUser);
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;
}
return(title);
}
}
}
public IList <UserStruct> GetUserList()
{
IList <UserStruct> users = Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.GetIdentifier(CacheObject.UserList, 0)] as IList <UserStruct>;
if (users != null)
{
return(users);
}
using (NpgsqlConnection con = PostgreSQLConn.CreateConnection(Parent.CurrentUser))
{
using (NpgsqlCommand cmd = con.CreateCommand())
{
cmd.CommandText = "SELECT * FROM \"GetUserList\"()";
NpgsqlDataReader reader = PostgreSQLConn.ExecuteReader(cmd, Parent.CurrentUser, false);
users = new List <UserStruct>();
while (reader.Read())
{
UserStruct user = new UserStruct(reader["username"].ToString(),
(UserAuthenticationTyp)Enum.Parse(typeof(UserAuthenticationTyp), reader["typ"].ToString()));
users.Add(user);
}
reader.Close();
Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.UserList, 0, new TimeSpan(0, 0, 30))] = users;
return(users);
}
}
}
/// <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 Dev03, 2009-01-13</remarks>
public void SetChapter(int id, int chapter)
{
using (NpgsqlConnection con = PostgreSQLConn.CreateConnection(Parent.CurrentUser))
{
NpgsqlTransaction transaction = con.BeginTransaction();
using (NpgsqlCommand cmd = con.CreateCommand())
{
cmd.CommandText = "SELECT count(*) FROM \"Chapters\" WHERE id=:chapterid";
cmd.Parameters.Add("chapterid", chapter);
if (Convert.ToInt32(PostgreSQLConn.ExecuteScalar(cmd, Parent.CurrentUser)) < 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 (NpgsqlCommand cmd = con.CreateCommand())
{
cmd.CommandText = "DELETE FROM \"Chapters_Cards\" WHERE cards_id=:id; ";
cmd.CommandText += "INSERT INTO \"Chapters_Cards\" (chapters_id, cards_id) VALUES (:chapterid, :id);";
cmd.CommandText += "UPDATE \"Cards\" SET chapters_id=:chapterid WHERE id=:id;";
cmd.Parameters.Add("chapterid", chapter);
cmd.Parameters.Add("id", id);
PostgreSQLConn.ExecuteNonQuery(cmd, Parent.CurrentUser);
}
transaction.Commit();
}
}
/// <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 Dev03, 2009-01-13</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 (NpgsqlConnection con = PostgreSQLConn.CreateConnection(Parent.CurrentUser))
{
using (NpgsqlCommand cmd = con.CreateCommand())
{
cmd.CommandText = "SELECT * FROM \"Chapters_Cards\" WHERE chapters_id IN " +
"(SELECT chapters_id FROM \"Chapters\" WHERE lm_id=(SELECT lm_id FROM \"LearningModules_Cards\" WHERE cards_id=:id))";
cmd.Parameters.Add("id", id);
NpgsqlDataReader reader = PostgreSQLConn.ExecuteReader(cmd, Parent.CurrentUser);
Dictionary <int, int> cardChapters = new Dictionary <int, int>();
while (reader.Read())
{
int chid = Convert.ToInt32(reader["chapters_id"]);
int cid = Convert.ToInt32(reader["cards_id"]);
if (!cardChapters.ContainsKey(cid))
{
cardChapters.Add(cid, chid);
}
}
Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.CardChapterList, 0)] = cardChapters;
return(cardChapters[id]);
}
}
}
/// <summary>
/// Gets the wrong cards.
/// </summary>
/// <param name="sessionId">The session id.</param>
/// <returns></returns>
/// <remarks>Documented by Dev08, 2009-01-28</remarks>
public int?GetWrongCards(int sessionId)
{
object wrongCardsCache = parent.CurrentUser.Cache[ObjectLifetimeIdentifier.GetIdentifier(CacheObject.StatisticWrongCards, sessionId)];
if (wrongCardsCache == null || RunningSession == sessionId)
{
using (NpgsqlConnection con = PostgreSQLConn.CreateConnection(parent.CurrentUser))
{
using (NpgsqlCommand cmd = con.CreateCommand())
{
cmd.CommandText = "SELECT sum_wrong FROM \"LearningSessions\" WHERE \"LearningSessions\".id=:sessId AND user_id=:uid AND lm_id=:lmid";
cmd.Parameters.Add("sessId", sessionId);
cmd.Parameters.Add("uid", parent.CurrentUser.Id);
cmd.Parameters.Add("lmid", parent.GetParentDictionary().Id);
int?wrongCards = PostgreSQLConn.ExecuteScalar <int>(cmd, parent.CurrentUser);
//Save to Cache
parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.StatisticWrongCards, sessionId, Cache.DefaultStatisticValidationTime)] = wrongCards;
return(wrongCards);
}
}
}
return(wrongCardsCache as int?);
}
public bool CheckUserSession()
{
using (NpgsqlConnection con = PostgreSQLConn.CreateConnection(Parent.CurrentUser))
{
using (NpgsqlCommand cmd = con.CreateCommand())
{
return(PostgreSQLConn.CheckSession(cmd, Parent.CurrentUser));
}
}
}
public int GetLMCount()
{
using (NpgsqlConnection con = PostgreSQLConn.CreateConnection(Parent.CurrentUser))
{
using (NpgsqlCommand cmd = con.CreateCommand())
{
cmd.CommandText = "SELECT count(*) as count FROM \"LearningModules\"";
return(Convert.ToInt32(PostgreSQLConn.ExecuteScalar(cmd, Parent.CurrentUser)));
}
}
}
public int GetLmId(int id)
{
using (NpgsqlConnection con = PostgreSQLConn.CreateConnection(Parent.CurrentUser))
{
using (NpgsqlCommand cmd = con.CreateCommand())
{
cmd.CommandText = "SELECT lm_id FROM \"Chapters\" WHERE id=:id";
cmd.Parameters.Add("id", id);
return(Convert.ToInt32(PostgreSQLConn.ExecuteScalar(cmd, Parent.CurrentUser)));
}
}
}
/// <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 (NpgsqlConnection con = PostgreSQLConn.CreateConnection(Parent.CurrentUser))
{
using (NpgsqlCommand cmd = con.CreateCommand())
{
cmd.CommandText = "DELETE FROM \"MediaContent_CardStyles\" WHERE cardstyles_id = :cardstyles_id";
cmd.Parameters.Add("cardstyles_id", Id);
PostgreSQLConn.ExecuteNonQuery(cmd, Parent.CurrentUser);
}
}
}
