public void CheckId(int id)
{
using (NpgsqlConnection con = PostgreSQLConn.CreateConnection(Parent.CurrentUser))
{
using (NpgsqlCommand cmd = con.CreateCommand())
{
cmd.CommandText = "SELECT count(*) FROM \"MultipleChoiceOptions\" WHERE id=:id";
cmd.Parameters.Add("id", id);
if (Convert.ToInt32(PostgreSQLConn.ExecuteScalar(cmd, Parent.CurrentUser)) < 1)
{
throw new IdAccessException(id);
}
}
}
}
/// <summary>
/// Checks the id.
/// </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 CheckId(int Id)
{
using (NpgsqlConnection con = PostgreSQLConn.CreateConnection(Parent.CurrentUser))
{
using (NpgsqlCommand cmd = con.CreateCommand())
{
cmd.CommandText = "SELECT count(*) FROM \"CardStyles\" WHERE id=:id";
cmd.Parameters.Add("id", Id);
int?count = PostgreSQLConn.ExecuteScalar <int>(cmd, Parent.CurrentUser);
if (!count.HasValue || count.Value < 1)
{
throw new IdAccessException(Id);
}
}
}
}
public int FindChapter(int lmid, string title)
{
using (NpgsqlConnection con = PostgreSQLConn.CreateConnection(Parent.CurrentUser))
{
using (NpgsqlCommand cmd = con.CreateCommand())
{
cmd.CommandText = "SELECT id FROM \"Chapters\" WHERE title=:title AND lm_id=:lmid";
cmd.Parameters.Add("title", title);
cmd.Parameters.Add("lmid", lmid);
object result = PostgreSQLConn.ExecuteScalar(cmd, Parent.CurrentUser);
if (result == null)
{
return(-1);
}
return(Convert.ToInt32(result));
}
}
}
/// <summary>
/// Gets the category Id.
/// </summary>
/// <param name="id">The id.</param>
/// <returns>the global category ID (0-5)</returns>
/// <remarks>Documented by Dev08, 2008-10-02</remarks>
public Category GetCategoryId(int id)
{
using (NpgsqlConnection con = PostgreSQLConn.CreateConnection(Parent.CurrentUser))
{
using (NpgsqlCommand cmd = con.CreateCommand())
{
cmd.CommandText = "SELECT global_id FROM \"LearningModules\", \"Categories\" WHERE \"LearningModules\".categories_id = \"Categories\".id AND \"LearningModules\".id = :id";
cmd.Parameters.Add("id", id);
int?globalId = PostgreSQLConn.ExecuteScalar <int>(cmd, Parent.CurrentUser);
if (!globalId.HasValue)
{
return(new Category(0));
}
return(new Category(globalId.Value));
}
}
}
/// <summary>
/// Gets the type of the media.
/// </summary>
/// <param name="id">The id.</param>
/// <returns></returns>
/// <remarks>Documented by Dev02, 2009-06-25</remarks>
/// <remarks>Documented by Dev02, 2009-06-25</remarks>
public EMedia GetMediaType(int id)
{
try
{
using (NpgsqlConnection conn = PostgreSQLConn.CreateConnection(Parent.CurrentUser))
{
using (NpgsqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT media_type FROM \"MediaContent\" WHERE id=:id;";
cmd.Parameters.Add("id", id);
return((EMedia)Enum.Parse(typeof(EMedia), Convert.ToString(PostgreSQLConn.ExecuteScalar(cmd, Parent.CurrentUser))));
}
}
}
catch (ArgumentException)
{
return(EMedia.Unknown);
}
}
public int GetCardsCount(int id)
{
List <ICard> cardsCache = Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.GetIdentifier(CacheObject.CardsList, id)] as List <ICard>;
if (cardsCache != null)
{
return(cardsCache.Count);
}
using (NpgsqlConnection con = PostgreSQLConn.CreateConnection(Parent.CurrentUser))
{
using (NpgsqlCommand cmd = con.CreateCommand())
{
cmd.CommandText = "SELECT count(*) FROM \"LearningModules_Cards\" WHERE lm_id=:id";
cmd.Parameters.Add("id", id);
return(Convert.ToInt32(PostgreSQLConn.ExecuteScalar(cmd, Parent.CurrentUser)));
}
}
}
public ICard GetNewCard(int id)
{
using (NpgsqlConnection con = PostgreSQLConn.CreateConnection(Parent.CurrentUser))
{
using (NpgsqlCommand cmd = con.CreateCommand())
{
cmd.CommandText = "INSERT INTO \"Cards\" VALUES (default) RETURNING id;";
ICard card = new DbCard(Convert.ToInt32(PostgreSQLConn.ExecuteScalar(cmd, Parent.CurrentUser)), false, Parent);
List <ICard> cardsCache = Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.GetIdentifier(CacheObject.CardsList, id)] as List <ICard>;
if (cardsCache != null)
{
cardsCache.Add(card);
}
return(card);
}
}
}
/// <summary>
/// Gets the type of the extension.
/// </summary>
/// <param name="guid">The GUID.</param>
/// <returns></returns>
public ExtensionType GetExtensionType(Guid guid)
{
using (NpgsqlConnection con = PostgreSQLConn.CreateConnection(Parent.CurrentUser))
{
using (NpgsqlCommand cmd = con.CreateCommand())
{
cmd.CommandText = "SELECT type FROM \"Extensions\" WHERE guid=:guid";
cmd.Parameters.Add("guid", guid.ToString());
try
{
return((ExtensionType)Enum.Parse(typeof(ExtensionType), PostgreSQLConn.ExecuteScalar(cmd, Parent.CurrentUser).ToString()));
}
catch (ArgumentException)
{
return(ExtensionType.Unknown);
}
}
}
}
/// <summary>
/// Creates the new card style.
/// </summary>
/// <returns>The style id.</returns>
/// <remarks>Documented by Dev03, 2009-03-05</remarks>
/// <remarks>Documented by Dev03, 2009-03-05</remarks>
public int CreateNewCardStyle()
{
using (NpgsqlConnection con = PostgreSQLConn.CreateConnection(Parent.CurrentUser))
{
using (NpgsqlCommand cmd = con.CreateCommand())
{
cmd.CommandText = "INSERT INTO \"CardStyles\" (id) VALUES (DEFAULT) RETURNING id";
int?newId = PostgreSQLConn.ExecuteScalar <int>(cmd, Parent.CurrentUser);
if (newId.HasValue)
{
return(newId.Value);
}
else
{
return(-1);
}
}
}
}
public int GetChapterCount(int lmid)
{
IList <int> chaptersCache = Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.GetIdentifier(CacheObject.ChaptersList, lmid)] as IList <int>;
if (chaptersCache != null)
{
return(chaptersCache.Count);
}
using (NpgsqlConnection con = PostgreSQLConn.CreateConnection(Parent.CurrentUser))
{
using (NpgsqlCommand cmd = con.CreateCommand())
{
cmd.CommandText = "SELECT count(*) FROM \"Chapters\" WHERE lm_id=:lmid";
cmd.Parameters.Add("lmid", lmid);
return(Convert.ToInt32(PostgreSQLConn.ExecuteScalar(cmd, Parent.CurrentUser)));
}
}
}
/// <summary>
/// Gets the end time stamp.
/// </summary>
/// <param name="sessionId">The session id.</param>
/// <returns></returns>
/// <remarks>Documented by Dev08, 2009-01-28</remarks>
public DateTime?GetEndTimeStamp(int sessionId)
{
if (RunningSession == sessionId)
{
return(DateTime.Now);
}
object endTimeStamp = parent.CurrentUser.Cache[ObjectLifetimeIdentifier.GetIdentifier(CacheObject.StatisticEndTime, sessionId)];
if (endTimeStamp == null || RunningSession == sessionId)
{
using (NpgsqlConnection con = PostgreSQLConn.CreateConnection(parent.CurrentUser))
{
using (NpgsqlCommand cmd = con.CreateCommand())
{
cmd.CommandText = "SELECT endtime 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);
object dt = PostgreSQLConn.ExecuteScalar <DateTime>(cmd, parent.CurrentUser);
DateTime?dateTime;
if (dt == null || !(dt is DateTime)) //either null or not DateTime
{
dateTime = DateTime.Now;
}
else
{
dateTime = (DateTime)dt;
}
//Save to Cache
parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.StatisticEndTime, sessionId, Cache.DefaultStatisticValidationTime)] = dateTime;
return(dateTime);
}
}
}
return(endTimeStamp as DateTime?);
}
public int LoginListUser(string username, Guid sid, bool closeOpenSessions, bool standAlone)
{
using (NpgsqlConnection con = PostgreSQLConn.CreateConnection(Parent.CurrentUser))
{
using (NpgsqlCommand cmd = con.CreateCommand())
{
cmd.CommandText = "SELECT * FROM \"LoginListUser\"(:user, :sessionid, :close_open_sessions, :standalone)";
cmd.Parameters.Add("user", username.ToLower());
cmd.Parameters.Add("sessionid", sid.ToString());
cmd.Parameters.Add("close_open_sessions", closeOpenSessions);
cmd.Parameters.Add("standalone", standAlone);
int value = (int)PostgreSQLConn.ExecuteScalar(cmd, Parent.CurrentUser, false);
Methods.CheckUserId(value);
Parent.CurrentUser.Cache.Uncache(ObjectLifetimeIdentifier.GetIdentifier(CacheObject.UserList, 0));
return(value);
}
}
}
/// <summary>
/// Gets the size of the dictionary.
/// </summary>
/// <param name="id">The LearningModule id.</param>
/// <param name="defaultCardSizeValue">The default size of a card without media (e.g. 1024 bytes).</param>
/// <returns></returns>
/// <remarks>Documented by Dev08, 2008-10-02</remarks>
public long GetDictionarySize(int id, int defaultCardSizeValue)
{
using (NpgsqlConnection con = PostgreSQLConn.CreateConnection(Parent.CurrentUser))
{
using (NpgsqlCommand cmd = con.CreateCommand())
{
long?mediaSize;
long?cardsCount;
//1. Get the FileSize sum of all MediaFiles in this LearningModule
cmd.CommandText = "SELECT SUM(CAST(\"MediaProperties\".value AS INT)) AS LearningModuleSize" +
" 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);
mediaSize = PostgreSQLConn.ExecuteScalar <long>(cmd, Parent.CurrentUser);
//2. Get the number of cards in this LearningModule (to calculate the approximately size of all Cards without Media)
using (NpgsqlCommand cmd2 = con.CreateCommand())
{
cmd2.CommandText = "SELECT COUNT(*) FROM \"Cards\", \"LearningModules_Cards\" WHERE \"LearningModules_Cards\".cards_id = \"Cards\".id AND \"LearningModules_Cards\".lm_id = :id";
cmd2.Parameters.Add("id", id);
cardsCount = PostgreSQLConn.ExecuteScalar <long>(cmd2, Parent.CurrentUser);
}
long size = 0;
if (mediaSize.HasValue)
{
size += mediaSize.Value;
}
if (cardsCount.HasValue)
{
size += cardsCount.Value * defaultCardSizeValue;
}
return(size);
}
}
}
public string GetAuthor(int id)
{
string authorCache = Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.GetIdentifier(CacheObject.LearningModuleAuthor, id)] as string;
if (authorCache != null)
{
return(authorCache);
}
using (NpgsqlConnection con = PostgreSQLConn.CreateConnection(Parent.CurrentUser))
{
using (NpgsqlCommand cmd = con.CreateCommand())
{
cmd.CommandText = "SELECT author FROM \"LearningModules\" WHERE id=:id";
cmd.Parameters.Add("id", id);
string author = Convert.ToString(PostgreSQLConn.ExecuteScalar(cmd, Parent.CurrentUser));
Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.LearningModuleAuthor, id)] = author;
return(author);
}
}
}
/// <summary>
/// Adds the media for the card style.
/// </summary>
/// <param name="Id">The style id.</param>
/// <param name="mediaId">The media id.</param>
/// <returns></returns>
/// <remarks>Documented by Dev03, 2009-03-05</remarks>
/// <remarks>Documented by Dev03, 2009-03-05</remarks>
public int AddMediaForCardStyle(int Id, int mediaId)
{
using (NpgsqlConnection con = PostgreSQLConn.CreateConnection(Parent.CurrentUser))
{
using (NpgsqlCommand cmd = con.CreateCommand())
{
cmd.CommandText = "INSERT INTO \"MediaContent_CardStyles\" (media_id, cardstyles_id) VALUES (:media_id, :cardstyles_id)";
cmd.Parameters.Add("media_id", mediaId);
cmd.Parameters.Add("cardstyles_id", Id);
int?newId = PostgreSQLConn.ExecuteScalar <int>(cmd, Parent.CurrentUser);
if (newId.HasValue)
{
return(newId.Value);
}
else
{
return(-1);
}
}
}
}
public MLifter.DAL.Interfaces.ISettings GetSettings(int id)
{
using (NpgsqlConnection con = PostgreSQLConn.CreateConnection(Parent.CurrentUser))
{
using (NpgsqlCommand cmd = con.CreateCommand())
{
cmd.CommandText = "SELECT settings_id FROM \"Chapters\" WHERE id=:id";
cmd.Parameters.Add("id", id);
int?sid = PostgreSQLConn.ExecuteScalar <int>(cmd, Parent.CurrentUser);
if (sid.HasValue)
{
return(new DbSettings(sid.Value, false, Parent));
}
else
{
return(null);
}
}
}
}
public string GetDbVersion()
{
string version = Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.GetIdentifier(CacheObject.DataBaseVersion, 0)] as string;
if (version != null && version.Length > 0)
{
return(version);
}
using (NpgsqlConnection con = PostgreSQLConn.CreateConnection(Parent.CurrentUser))
{
using (NpgsqlCommand cmd = con.CreateCommand())
{
cmd.CommandText = "SELECT value FROM \"DatabaseInformation\" WHERE property=:prop";
cmd.Parameters.Add("prop", DataBaseInformation.Version.ToString());
version = Convert.ToString(PostgreSQLConn.ExecuteScalar(cmd, Parent.CurrentUser));
Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.DataBaseVersion, 0, new TimeSpan(1, 0, 0))] = version;
return(version);
}
}
}
/// <summary>
/// Opens the user learning session.
/// </summary>
/// <param name="lm_id">The lm_id.</param>
/// <remarks>Documented by Dev08, 2008-09-05</remarks>
public int OpenUserSession(int lm_id)
{
using (NpgsqlConnection con = PostgreSQLConn.CreateConnection(Parent.CurrentUser))
{
using (NpgsqlCommand cmd = con.CreateCommand())
{
cmd.CommandText = "SELECT \"StartLearningSession\"(:usrid, :lmid, :pool, :b1, :b2, :b3, :b4, :b5, :b6, :b7, :b8, :b9, :b10)";
cmd.Parameters.Add("usrid", Parent.CurrentUser.Id);
cmd.Parameters.Add("lmid", lm_id);
int counter = 0;
int cardsInBoxes = 0;
BoxSizes boxContent = GetCurrentBoxContent();
foreach (int box in boxContent.Sizes)
{
if (counter == 0)
{
cmd.Parameters.Add("pool", box);
++counter;
continue;
}
cmd.Parameters.Add("b" + Convert.ToString(counter++), box);
cardsInBoxes += box;
}
int newSessionId = PostgreSQLConn.ExecuteScalar <int>(cmd, Parent.CurrentUser).Value;
//Following Statement does add the "RunningSession" = true to the current Statistic.
PgSqlStatisticConnector connector = PgSqlStatisticConnector.GetInstance(Parent);
connector.RunningSession = newSessionId;
return(newSessionId);
}
}
}
/// <summary>
/// Gets the media.
/// </summary>
/// <param name="id">The id.</param>
/// <param name="cacheConnector">The cache connector.</param>
/// <returns>A memory stream for the media object.</returns>
/// <remarks>Documented by Dev03, 2008-08-05</remarks>
/// <remarks>Documented by Dev03, 2009-01-13</remarks>
public Stream GetMediaStream(int id, IDbMediaConnector cacheConnector)
{
CachingStream stream = null;
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);
LargeObject largeObject = lbm.Open(noid, LargeObjectManager.READWRITE);
byte[] buffer = LargeObjectToBuffer(largeObject);
stream = new CachingStream(buffer, id, cacheConnector);
largeObject.Close();
tran.Commit();
}
return(stream);
}
public void MoveChapter(int lmid, int first_id, int second_id)
{
using (NpgsqlConnection con = PostgreSQLConn.CreateConnection(Parent.CurrentUser))
{
NpgsqlTransaction transaction = con.BeginTransaction();
int position1, position2;
using (NpgsqlCommand cmd = con.CreateCommand())
{
cmd.CommandText = "SELECT position FROM \"Chapters\" WHERE id=:id AND lm_id=:lmid";
cmd.Parameters.Add("id", first_id);
cmd.Parameters.Add("lmid", lmid);
object result = PostgreSQLConn.ExecuteScalar(cmd, Parent.CurrentUser);
if (result == null)
{
throw new IdAccessException(first_id);
}
position1 = Convert.ToInt32(result);
}
using (NpgsqlCommand cmd = con.CreateCommand())
{
cmd.CommandText = "SELECT position FROM \"Chapters\" WHERE id=:id AND lm_id=:lmid";
cmd.Parameters.Add("id", second_id);
cmd.Parameters.Add("lmid", lmid);
object result = PostgreSQLConn.ExecuteScalar(cmd, Parent.CurrentUser);
if (result == null)
{
throw new IdAccessException(second_id);
}
position2 = Convert.ToInt32(result);
}
int newfirstposition;
if (position1 < position2)
{
newfirstposition = position2 + 5; //insert first after second
}
else if (position1 > position2)
{
newfirstposition = position2 - 5; //insert first before second
}
else
{
//positions were equal => no reordering possible
return;
}
using (NpgsqlCommand cmd = con.CreateCommand())
{
cmd.CommandText = "UPDATE \"Chapters\" SET position=:position WHERE id=:id; ";
cmd.CommandText += "SELECT \"RedistributeChapterPositions\"(:lmid)";
cmd.Parameters.Add("position", newfirstposition);
cmd.Parameters.Add("id", first_id);
cmd.Parameters.Add("lmid", lmid);
PostgreSQLConn.ExecuteNonQuery(cmd, Parent.CurrentUser);
}
transaction.Commit();
Parent.CurrentUser.Cache.Uncache(ObjectLifetimeIdentifier.GetIdentifier(CacheObject.ChaptersList, lmid));
}
}