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);
}
}
/// <summary>
/// Gets the words.
/// </summary>
/// <param name="cardid">The cardid.</param>
/// <param name="side">The side.</param>
/// <param name="type">The type.</param>
/// <returns></returns>
/// <remarks>Documented by Dev03, 2009-01-09</remarks>
private IList <IWord> GetWords(int cardid, Side side, WordType type)
{
CacheObject obj = ObjectLifetimeIdentifier.GetCacheObject(side, type);
IList <IWord> wordsCache = Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.GetIdentifier(obj, cardid)] as IList <IWord>;
if (wordsCache != null)
{
return(wordsCache);
}
IList <IWord> question = new List <IWord>();
IList <IWord> questionExample = new List <IWord>();
IList <IWord> questionDistractor = new List <IWord>();
IList <IWord> answer = new List <IWord>();
IList <IWord> answerExample = new List <IWord>();
IList <IWord> answerDistractor = new List <IWord>();
SqlCeCommand cmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser);
cmd.CommandText = "SELECT id, side, type, text, is_default FROM TextContent WHERE cards_id=@id ORDER BY position ASC;";
cmd.Parameters.Add("@id", cardid);
SqlCeDataReader reader = MSSQLCEConn.ExecuteReader(cmd);
while (reader.Read())
{
object[] word = new object[5];
reader.GetValues(word);
int id = Convert.ToInt32(word[0]);
Side wside = (Side)Enum.Parse(typeof(Side), word[1].ToString(), true);
WordType wtype = (WordType)Enum.Parse(typeof(WordType), word[2].ToString(), false);
string text = word[3].ToString();
bool isDefault = Convert.ToBoolean(word[4]);
switch (wside)
{
case Side.Question:
switch (wtype)
{
case WordType.Word:
question.Add(new DbWord(id, text, wtype, isDefault, Parent));
break;
case WordType.Sentence:
questionExample.Add(new DbWord(id, text, wtype, isDefault, Parent));
break;
case WordType.Distractor:
questionDistractor.Add(new DbWord(id, text, wtype, isDefault, Parent));
break;
}
break;
case Side.Answer:
switch (wtype)
{
case WordType.Word:
answer.Add(new DbWord(id, text, wtype, isDefault, Parent));
break;
case WordType.Sentence:
answerExample.Add(new DbWord(id, text, wtype, isDefault, Parent));
break;
case WordType.Distractor:
answerDistractor.Add(new DbWord(id, text, wtype, isDefault, Parent));
break;
}
break;
}
}
reader.Close();
Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.QuestionWords, cardid)] = question;
Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.QuestionExampleWords, cardid)] = questionExample;
Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.QuestionDistractorWords, cardid)] = questionDistractor;
Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.AnswerWords, cardid)] = answer;
Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.AnswerExampleWords, cardid)] = answerExample;
Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.AnswerDistractorWords, cardid)] = answerDistractor;
switch (side)
{
case Side.Question:
switch (type)
{
case WordType.Word:
return(question);
case WordType.Sentence:
return(questionExample);
case WordType.Distractor:
return(questionDistractor);
}
break;
case Side.Answer:
switch (type)
{
case WordType.Word:
return(answer);
case WordType.Sentence:
return(answerExample);
case WordType.Distractor:
return(answerDistractor);
}
break;
}
return(new List <IWord>());
}
/// <summary>
/// Adds the words.
/// </summary>
/// <param name="id">The id.</param>
/// <param name="side">The side.</param>
/// <param name="type">The type.</param>
/// <param name="words">The words.</param>
/// <remarks>Documented by Dev03, 2009-01-09</remarks>
/// <remarks>Documented by Dev08, 2009-01-19</remarks>
public void AddWords(int id, Side side, WordType type, List <IWord> words)
{
if (words.Count > 0)
{
//SqlCeCommand cmd1 = MSSQLCEConn.CreateCommand(Parent.CurrentUser);
//SqlCeCommand cmd2 = MSSQLCEConn.CreateCommand(Parent.CurrentUser);
//SqlCeCommand cmd3 = MSSQLCEConn.CreateCommand(Parent.CurrentUser);
//SqlCeParameter paramWordId = new SqlCeParameter("@id", SqlDbType.Int);
//SqlCeParameter paramCardId = new SqlCeParameter("@cardid", SqlDbType.Int);
//SqlCeParameter paramType = new SqlCeParameter("@type", SqlDbType.NVarChar);
//SqlCeParameter paramIsDefault = new SqlCeParameter("@isdefault", SqlDbType.Bit);
//cmd1.CommandText = "SELECT count(*) FROM TextContent WHERE id = @wordid AND text = @word AND type = @type AND is_default = @isdefault;";
//cmd1.Parameters.Add(paramWordId);
//cmd1.Parameters.Add(paramCardId);
//cmd1.Parameters.Add(paramType);
//cmd1.Parameters.Add(paramIsDefault);
//SqlCeParameter paramSide = new SqlCeParameter("@side", SqlDbType.NVarChar);
//cmd2.CommandText = "SELECT position FROM TextContent WHERE cards_id=@id AND side=@side AND type=@type ORDER BY position DESC";
//cmd2.Parameters.Add(paramWordId);
//cmd2.Parameters.Add(paramSide);
//cmd2.Parameters.Add(paramType);
//SqlCeParameter paramText = new SqlCeParameter("@text", SqlDbType.NText);
//SqlCeParameter paramPosition = new SqlCeParameter("@position", SqlDbType.Int);
//cmd3.CommandText = @"INSERT INTO TextContent (cards_id, text, side, type, position, is_default) VALUES (@id, @text, @:side, @type, @position, @isdefault); SELECT @@IDENTITY;";
//cmd3.Parameters.Add(paramCardId);
//cmd3.Parameters.Add(paramText);
//cmd3.Parameters.Add(paramSide);
//cmd3.Parameters.Add(paramType);
//cmd3.Parameters.Add(paramPosition);
//cmd3.Parameters.Add(paramIsDefault);
foreach (IWord word in words)
{
if (word != null && word.Word.Length > 0)
{
//paramCardId.Value = id;
//paramWordId.Value = word.Id;
//paramText.Value = word.Word;
//paramSide.Value = side.ToString();
//paramType.Value = type.ToString();
//paramIsDefault.Value = word.Default;
SqlCeCommand cmd1 = MSSQLCEConn.CreateCommand(Parent.CurrentUser);
cmd1.CommandText = "SELECT count(*) FROM TextContent WHERE id = @wordid AND text = @word AND type = @type AND is_default = @isdefault;";
cmd1.Parameters.Add("@wordid", word.Id);
cmd1.Parameters.Add("@word", word.Word);
cmd1.Parameters.Add("@type", type.ToString());
cmd1.Parameters.Add("@isdefault", word.Default);
bool wordExists = (Convert.ToInt32(MSSQLCEConn.ExecuteScalar(cmd1)) > 0);
if (!wordExists)
{
int currentPos = 0;
SqlCeCommand cmd2 = MSSQLCEConn.CreateCommand(Parent.CurrentUser);
cmd2.CommandText = "SELECT position FROM TextContent WHERE cards_id=@id AND side=@side AND type=@type ORDER BY position DESC";
cmd2.Parameters.Add("@id", id);
cmd2.Parameters.Add("@side", side.ToString());
cmd2.Parameters.Add("@type", type.ToString());
object retval = MSSQLCEConn.ExecuteScalar(cmd2);
if (retval != DBNull.Value)
{
currentPos = Convert.ToInt32(retval);
}
//paramPosition.Value = currentPos + 10;
SqlCeCommand cmd3 = MSSQLCEConn.CreateCommand(Parent.CurrentUser);
cmd3.CommandText = "INSERT INTO TextContent (cards_id, text, side, type, position, is_default) VALUES (@id, @word, @side, @type, @position, @isdefault); SELECT @@IDENTITY;";
cmd3.Parameters.Add("@id", id);
cmd3.Parameters.Add("@word", word.Word);
cmd3.Parameters.Add("@side", side.ToString());
cmd3.Parameters.Add("@type", type.ToString());
cmd3.Parameters.Add("@position", currentPos + 10);
cmd3.Parameters.Add("@isdefault", word.Default);
MSSQLCEConn.ExecuteNonQuery(cmd3);
}
}
}
Parent.CurrentUser.Cache.Uncache(ObjectLifetimeIdentifier.GetIdentifier(ObjectLifetimeIdentifier.GetCacheObject(side, type), id));
}
}
public void PreloadCardCache(int id)
{
if (parent.CurrentUser.Cache[ObjectLifetimeIdentifier.GetIdentifier(CacheObject.CardCacheInitialized, id)] != null)
{
return;
}
using (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(parent.CurrentUser))
using (SqlCeCommand cmd2 = MSSQLCEConn.CreateCommand(parent.CurrentUser))
using (SqlCeCommand cmd3 = MSSQLCEConn.CreateCommand(parent.CurrentUser))
{
cmd.CommandText = "SELECT C.id, C.chapters_id, CS.box, CS.active, CS.[timestamp] FROM Cards C INNER JOIN UserCardState CS ON C.id = CS.cards_id WHERE C.lm_id = @lm_id AND CS.user_id = @user_id ORDER BY C.id";
cmd.Parameters.Add("@user_id", parent.CurrentUser.Id);
cmd.Parameters.Add("@lm_id", id);
cmd2.CommandText = "SELECT id, title FROM Chapters WHERE id IN (SELECT chapters_id FROM Cards WHERE lm_id = @lm_id)";
cmd2.Parameters.Add("@lm_id", id);
cmd3.CommandText = "SELECT C.id, T.text, T.side FROM Cards C INNER JOIN TextContent T ON C.id = T.cards_id WHERE C.lm_id = @lm_id AND T.type = 'Word' ORDER BY C.id, T.side, T.position;";
cmd3.Parameters.Add("@lm_id", id);
List <object[]> cards = new List <object[]>();
using (SqlCeDataReader cardReader = MSSQLCEConn.ExecuteReader(cmd))
{
while (cardReader.Read())
{
object[] row = new object[5];
cardReader.GetValues(row);
cards.Add(row);
}
}
List <object[]> chapters = new List <object[]>();
using (SqlCeDataReader chapterReader = MSSQLCEConn.ExecuteReader(cmd2))
{
while (chapterReader.Read())
{
object[] row = new object[2];
chapterReader.GetValues(row);
chapters.Add(row);
}
}
List <object[]> allWords = new List <object[]>();
using (SqlCeDataReader textReader = MSSQLCEConn.ExecuteReader(cmd3))
{
while (textReader.Read())
{
object[] row = new object[3];
textReader.GetValues(row);
allWords.Add(row);
}
}
int pos = 0;
foreach (object[] card in cards)
{
int cardId = Convert.ToInt32(card[0]);
int chapterId = Convert.ToInt32(card[1]);
object[] chapter = chapters.Find(c => Convert.ToInt32(c[0]) == chapterId);
if (chapter != null)
{
chapters.Remove(chapter);
}
List <object[]> words = new List <object[]>();
if (allWords.Count > pos && Convert.ToInt32(allWords[pos][0]) == cardId)
{
while (allWords.Count > pos && Convert.ToInt32(allWords[pos][0]) == cardId)
{
words.Add(allWords[pos]);
pos++;
}
}
IList <IWord> question = new List <IWord>();
IList <IWord> answer = new List <IWord>();
foreach (object[] word in words)
{
// Pull the question word or answer word from this row
String side = word[2].ToString();
if (side == Side.Question.ToString())
{
question.Add(new DbWord(cardId, word[1].ToString(), WordType.Word, true, parent));
}
else
{
answer.Add(new DbWord(cardId, word[1].ToString(), WordType.Word, true, parent));
}
}
parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.QuestionWords, cardId)] = question;
parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.AnswerWords, cardId)] = answer;
parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.CardState, cardId)] = new CardState(Convert.ToInt32(card[2]),
Convert.ToBoolean(card[3]), (card[4] is DBNull) ? new DateTime(1901, 1, 1) : Convert.ToDateTime(card[4])); // box, active, timestamp
if (chapter != null)
{
parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.ChapterTitle, Convert.ToInt32(chapter[0]))] = chapter[1].ToString();
}
}
}
parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.CardCacheInitialized, id)] = true;
}
/// <summary>
/// Gets the score.
/// </summary>
/// <param name="id">The id.</param>
/// <returns></returns>
/// <remarks>Documented by Dev08, 2009-01-13</remarks>
/// <remarks>Documented by Dev04, 2009-04-10</remarks>
/// <remarks>Documented by Dev08, 2009-04-20</remarks>
public double GetScore(int id)
{
//double? score = parent.CurrentUser.Cache[ObjectLifetimeIdentifier.GetIdentifier(CacheObject.Score, id)] as double?;
//if (score != null && score.HasValue)
// return score.Value;
using (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(parent.CurrentUser))
{
//cmd.CommandText = "SELECT SUM(box) FROM \"UserCardState\" WHERE user_id=@param_user_id and cards_id IN (SELECT cards_id FROM \"LearningModules_Cards\" WHERE lm_id=@param_lm_id)";
double[] factor = new double[11];
factor[0] = 0;
for (int i = 1; i < factor.Length; i++)
{
factor[i] = factor[i - 1] + 1.0 / i;
}
cmd.CommandText = "SELECT TOP(1) (";
for (int i = 1; i < factor.Length; i++)
{
cmd.CommandText = cmd.CommandText + "box" + i + "_content * " + factor[i - 1].ToString().Replace(",", ".");
if (i != factor.Length - 1)
{
cmd.CommandText += " + ";
}
}
cmd.CommandText += ") AS sum, ((pool_content + ";
for (int i = 1; i < factor.Length; i++)
{
cmd.CommandText += "box" + i + "_content ";
if (i != factor.Length - 1)
{
cmd.CommandText += "+";
}
else
{
cmd.CommandText += ")*" + factor[factor.Length - 2].ToString().Replace(",", ".");
}
}
cmd.CommandText += ") AS total FROM \"LearningSessions\" WHERE (user_id=@param_user_id AND lm_id=@param_lm_id) ORDER BY id DESC";
cmd.Parameters.Add("@param_user_id", parent.CurrentUser.Id);
cmd.Parameters.Add("@param_lm_id", id);
SqlCeDataReader reader = MSSQLCEConn.ExecuteReader(cmd);
reader.Read();
double sum, total;
double?result;
try
{
sum = System.Convert.ToDouble(reader["sum"]);
total = System.Convert.ToDouble(reader["total"]);
result = (double)(sum / total * 100.00);
}
catch
{
result = null;
}
//parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.Score, id, new TimeSpan(0, 0, 1))] = result;
return(result.HasValue ? result.Value : 0);
}
}
/// <summary>
/// Gets the user settings.
/// </summary>
/// <param name="id">The id.</param>
/// <returns></returns>
/// <remarks>Documented by Dev08, 2009-01-12</remarks>
public ISettings GetUserSettings(int id)
{
DbSettings settingsCache = parent.CurrentUser.Cache[ObjectLifetimeIdentifier.GetIdentifier(CacheObject.UserLearningModuleSettings, id)] as DbSettings;
if (settingsCache != null)
{
return(settingsCache);
}
int count;
using (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(parent.CurrentUser))
{
cmd.CommandText = "SELECT count(*) FROM \"UserProfilesLearningModulesSettings\" WHERE user_id=@param_user_id and lm_id=@param_lm_id;";
cmd.Parameters.Add("@param_user_id", parent.CurrentUser.Id);
cmd.Parameters.Add("@param_lm_id", id);
count = Convert.ToInt32(MSSQLCEConn.ExecuteScalar(cmd));
}
int result;
if (count < 1) //Create new settings
{
using (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(parent.CurrentUser))
{
cmd.CommandText = "INSERT INTO \"UserProfilesLearningModulesSettings\" (user_id, lm_id, settings_id) VALUES (@param_user_id, @param_lm_id, @param_new_settings);";
cmd.Parameters.Add("@param_user_id", parent.CurrentUser.Id);
cmd.Parameters.Add("@param_lm_id", id);
cmd.Parameters.Add("@param_new_settings", MsSqlCeSettingsConnector.CreateNewSettings(parent));
MSSQLCEConn.ExecuteNonQuery(cmd);
}
using (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(parent.CurrentUser))
{
cmd.CommandText = "SELECT settings_id FROM \"UserProfilesLearningModulesSettings\" WHERE user_id=@param_user_id and lm_id=@param_lm_id;";
cmd.Parameters.Add("@param_user_id", parent.CurrentUser.Id);
cmd.Parameters.Add("@param_lm_id", id);
result = Convert.ToInt32(MSSQLCEConn.ExecuteScalar(cmd));
}
//Selected Learn Chapters
using (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(parent.CurrentUser))
{
cmd.CommandText = "SELECT id FROM \"Chapters\" WHERE lm_id=@param_lm_id";
cmd.Parameters.Add("@param_lm_id", id);
SqlCeDataReader reader = MSSQLCEConn.ExecuteReader(cmd);
while (reader.Read())
{
using (SqlCeCommand cmd2 = MSSQLCEConn.CreateCommand(parent.CurrentUser))
{
cmd2.CommandText = "INSERT INTO \"SelectedLearnChapters\" VALUES (@cid, @result);";
cmd2.Parameters.Add("@cid", reader["id"]);
cmd2.Parameters.Add("@result", result);
MSSQLCEConn.ExecuteNonQuery(cmd2);
}
}
reader.Close();
}
DbSettings settings = new DbSettings(result, false, parent);
parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.UserLearningModuleSettings, id, Cache.DefaultSettingsValidationTime)] = settings;
return(settings);
}
else
{
using (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(parent.CurrentUser))
{
cmd.CommandText = "SELECT settings_id FROM \"UserProfilesLearningModulesSettings\" WHERE user_id=@param_user_id and lm_id=@param_lm_id;";
cmd.Parameters.Add("@param_user_id", parent.CurrentUser.Id);
cmd.Parameters.Add("@param_lm_id", id);
result = Convert.ToInt32(MSSQLCEConn.ExecuteScalar(cmd));
}
DbSettings settings = new DbSettings(result, false, parent);
parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.UserLearningModuleSettings, id, Cache.DefaultSettingsValidationTime)] = settings;
return(settings);
}
}
/// <summary>
/// Gets the database values.
/// </summary>
/// <param name="parent">The parent.</param>
/// <remarks>Documented by Dev08, 2009-01-12</remarks>
public static void GetDatabaseValues(ParentClass parent)
{
using (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(parent.CurrentUser))
{
cmd.CommandText = "SELECT * FROM \"DatabaseInformation\"";
SqlCeDataReader reader = MSSQLCEConn.ExecuteReader(cmd);
while (reader.Read())
{
switch ((DataBaseInformation)Enum.Parse(typeof(DataBaseInformation), reader["property"].ToString()))
{
case DataBaseInformation.Version:
parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.DataBaseVersion, 0, new TimeSpan(1, 0, 0))] = reader["value"].ToString();
break;
case DataBaseInformation.SupportedDataLayerVersions:
string vrs = reader["value"].ToString();
if (vrs.Length > 0)
{
List <string> versions = new List <string>();
versions.AddRange(vrs.Split(new char[] { ',' }));
parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.SupportedDataLayerVersions, 0, new TimeSpan(1, 0, 0))] = versions;
}
break;
case DataBaseInformation.ListAuthentication:
parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.ListAuthentication, 0, new TimeSpan(1, 0, 0))] = (bool?)Convert.ToBoolean(reader["value"]);
break;
case DataBaseInformation.FormsAuthentication:
parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.FormsAuthentication, 0, new TimeSpan(1, 0, 0))] = (bool?)Convert.ToBoolean(reader["value"]);
break;
case DataBaseInformation.LocalDirectoryAuthentication:
parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.LocalDirectoryAuthentication, 0, new TimeSpan(1, 0, 0))] = (bool?)Convert.ToBoolean(reader["value"]);
break;
case DataBaseInformation.LocalDirectoryType:
try
{
parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.LocalDirectoryType, 0, new TimeSpan(1, 0, 0))] =
(LocalDirectoryType)Enum.Parse(typeof(LocalDirectoryType), reader["value"].ToString());
}
catch (Exception)
{
parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.LocalDirectoryType, 0, new TimeSpan(1, 0, 0))] =
LocalDirectoryType.ActiveDirectory;
}
break;
case DataBaseInformation.LdapServer:
parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.LdapServer, 0, new TimeSpan(1, 0, 0))] = reader["value"].ToString();
break;
case DataBaseInformation.LdapPort:
try
{
parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.LdapPort, 0, new TimeSpan(1, 0, 0))] = Convert.ToInt32(reader["value"]);
}
catch
{
parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.LdapPort, 0, new TimeSpan(1, 0, 0))] = 0;
}
break;
case DataBaseInformation.LdapUser:
parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.LdapUser, 0, new TimeSpan(1, 0, 0))] = reader["value"].ToString();
break;
case DataBaseInformation.LdapPassword:
parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.LdapPassword, 0, new TimeSpan(1, 0, 0))] = reader["value"].ToString();
break;
case DataBaseInformation.LdapContext:
parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.LdapContext, 0, new TimeSpan(1, 0, 0))] = reader["value"].ToString();
break;
case DataBaseInformation.LdapUseSsl:
parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.LdapUseSsl, 0, new TimeSpan(1, 0, 0))] = (bool?)Convert.ToBoolean(reader["value"]);
break;
default:
break;
}
}
reader.Close();
}
}
/// <summary>
/// Restarts the learning success.
/// </summary>
/// <param name="lm_id">The lm_id.</param>
/// <remarks>Documented by Dev10, 2009-01-11</remarks>
public IDictionary RestartLearningSuccess(int lm_id)
{
if (Parent.CurrentUser.ConnectionString.SyncType != SyncType.NotSynchronized)
{
object firstLearningSessionId = null, lastLearningSessionId = null;
//Get all LearningSessions.Ids
SqlCeCommand cmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser);
cmd.CommandText = "SELECT id FROM LearningSessions WHERE lm_id = @lmId AND user_id = @userId ORDER BY id ASC;";
cmd.Parameters.Add("@lmId", lm_id);
cmd.Parameters.Add("@userId", Parent.CurrentUser.Id);
SqlCeDataReader reader = MSSQLCEConn.ExecuteReader(cmd);
int counter = 0;
while (reader.Read())
{
++counter;
if (firstLearningSessionId == null)
{
firstLearningSessionId = reader["id"];
}
else
{
lastLearningSessionId = reader["id"];
}
}
reader.Close();
if (lastLearningSessionId == null)
{
lastLearningSessionId = firstLearningSessionId;
}
int firstLearningSession = Convert.ToInt32(firstLearningSessionId);
int lastLearningSession = Convert.ToInt32(lastLearningSessionId);
if (counter == 0)
{
return(Parent.GetParentDictionary());
}
cmd.CommandText = "DELETE FROM LearnLog WHERE session_id BETWEEN @sessionStart AND @sessionStop; DELETE FROM LearningSessions WHERE lm_id = @lmId AND user_id = @userId;";
cmd.Parameters.Add("@sessionStart", firstLearningSession);
cmd.Parameters.Add("@sessionStop", lastLearningSession);
MSSQLCEConn.ExecuteNonQuery(cmd);
return(Parent.GetParentDictionary());
}
else
{
SqlCeCommand cmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser);
cmd.CommandText = "UPDATE UserProfiles SET local_directory_id=NULL, username=@user WHERE id=@id";
cmd.Parameters.Add("@id", Parent.CurrentUser.Id);
cmd.Parameters.Add("@user", "BACKUP_" + Parent.CurrentUser.AuthenticationStruct.UserName);
MSSQLCEConn.ExecuteNonQuery(cmd);
SqlCeCommand cmd2 = MSSQLCEConn.CreateCommand(Parent.CurrentUser);
cmd2.CommandText = "INSERT INTO UserProfiles(username, local_directory_id, user_type) VALUES(@user, @id, 'ListAuthentication'); SELECT @@IDENTITY";
cmd2.Parameters.Add("@user", User.CurrentWindowsUserName);
cmd2.Parameters.Add("@id", User.CurrentWindowsUserId);
int uid = MSSQLCEConn.ExecuteScalar <int>(cmd2).Value;
((Parent.CurrentUser as User).BaseUser as DbUser).SetId(uid);
return(((Parent.CurrentUser as User).BaseUser as DbUser).Open());
}
}
public int AddNewLM(string guid, int categoryId, string title, string licenceKey, bool contentProtected, int calCount)
{
if (!Parent.CurrentUser.ConnectionString.ConnectionString.EndsWith(Helper.EmbeddedDbExtension))
{
ConnectionStringStruct css = Parent.CurrentUser.ConnectionString;
css.ConnectionString += @"\" + title.Replace(@"\", "_") + Helper.EmbeddedDbExtension;
css.Typ = DatabaseType.MsSqlCe;
Parent.CurrentUser.ConnectionString = css;
}
{
//replace invalid filename characters
ConnectionStringStruct css = Parent.CurrentUser.ConnectionString;
css.ConnectionString = Helper.FilterInvalidFilenameCharacters(css.ConnectionString);
Parent.CurrentUser.ConnectionString = css;
}
if (File.Exists(Parent.CurrentUser.ConnectionString.ConnectionString))
{
int i = 1;
while (File.Exists(Parent.CurrentUser.ConnectionString.ConnectionString.Replace(Helper.EmbeddedDbExtension, "_" + i + Helper.EmbeddedDbExtension)))
{
i++;
}
ConnectionStringStruct css = Parent.CurrentUser.ConnectionString;
css.ConnectionString = css.ConnectionString.Replace(Helper.EmbeddedDbExtension, "_" + i + Helper.EmbeddedDbExtension);
Parent.CurrentUser.ConnectionString = css;
}
if (!Directory.Exists(Path.GetDirectoryName(Parent.CurrentUser.ConnectionString.ConnectionString)))
{
Directory.CreateDirectory(Path.GetDirectoryName(Parent.CurrentUser.ConnectionString.ConnectionString));
}
using (SqlCeEngine clientEngine = new SqlCeEngine("Data Source=" + Parent.CurrentUser.ConnectionString.ConnectionString))
{
clientEngine.CreateDatabase();
clientEngine.Dispose();
}
using (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser))
{
cmd.CommandText = Resources.MsSqlCeDbCreateScript;
MSSQLCEConn.ExecuteNonQuery(cmd);
}
MSSQLCEConn.ApplyIndicesToDatabase(MSSQLCEConn.GetConnection(Parent.CurrentUser));
int cat_id;
using (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser))
{
cmd.CommandText = "SELECT id FROM Categories WHERE global_id=@cat_id;";
cmd.Parameters.Add("@cat_id", categoryId);
cat_id = MSSQLCEConn.ExecuteScalar <int>(cmd).Value;
}
using (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser))
{
cmd.CommandText = "INSERT INTO LearningModules (guid, title, categories_id, default_settings_id, allowed_settings_id, licence_key, content_protected, cal_count) " +
"VALUES (@guid, @title, @cat_id, @dset, @aset, @lk, @cp, @cals); SELECT @@IDENTITY;";
cmd.Parameters.Add("@guid", guid);
cmd.Parameters.Add("@title", title);
cmd.Parameters.Add("@cat_id", cat_id);
cmd.Parameters.Add("@lk", licenceKey);
cmd.Parameters.Add("@cp", contentProtected);
cmd.Parameters.Add("@cals", calCount);
cmd.Parameters.Add("@dset", MsSqlCeSettingsConnector.CreateNewDefaultSettings(Parent));
cmd.Parameters.Add("@aset", MsSqlCeSettingsConnector.CreateNewAllowedSettings(Parent));
return(MSSQLCEConn.ExecuteScalar <int>(cmd).Value);
}
}
private IList <int> GetCardMedia(int cardid, Side side, WordType?type)
{
IList <int> ids = new List <int>();
IList <IMedia> mediaList = Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.GetIdentifier(CacheObject.CardMedia, cardid)] as IList <IMedia>;
if (mediaList != null)
{
foreach (DbMedia cms in mediaList)
{
if (cms.Side == side && (!type.HasValue || cms.Type == type))
{
ids.Add(cms.Id);
}
}
return(ids);
}
using (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser))
{
cmd.CommandText = "SELECT id, media_type, side, type, is_default FROM \"Cards_MediaContent\" JOIN \"MediaContent\" ON media_id=id WHERE cards_id=@cardid;";
cmd.Parameters.Add("@cardid", cardid);
SqlCeDataReader reader = MsSqlCe.MSSQLCEConn.ExecuteReader(cmd);
mediaList = new List <IMedia>();
while (reader.Read())
{
IMedia newMedia = null;
int id = Convert.ToInt32(reader["id"]);
EMedia mtype = (EMedia)Enum.Parse(typeof(EMedia), Convert.ToString(reader["media_type"]));
Side cside = (Side)Enum.Parse(typeof(Side), Convert.ToString(reader["side"]));
WordType wordtype = (WordType)Enum.Parse(typeof(WordType), Convert.ToString(reader["type"]));
bool isDefault = Convert.ToBoolean(reader["is_default"]);
switch (mtype)
{
case EMedia.Audio:
newMedia = new DbAudio(id, cardid, false, cside, wordtype, isDefault, (wordtype == WordType.Sentence), Parent);
break;
case EMedia.Image:
newMedia = new DbImage(id, cardid, false, cside, wordtype, isDefault, (wordtype == WordType.Sentence), Parent);
break;
case EMedia.Video:
newMedia = new DbVideo(id, cardid, false, cside, wordtype, isDefault, (wordtype == WordType.Sentence), Parent);
break;
}
mediaList.Add(newMedia);
if ((newMedia as DbMedia).Side == side && (!type.HasValue || (newMedia as DbMedia).Type == type))
{
ids.Add((newMedia as DbMedia).Id);
}
}
reader.Close();
Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.CardMedia, cardid)] = mediaList;
return(ids);
}
}
/// <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 cards by query.
/// </summary>
/// <param name="id">The id.</param>
/// <param name="query">The query.</param>
/// <param name="orderBy">The order by.</param>
/// <param name="orderDir">The order dir.</param>
/// <param name="number">The number.</param>
/// <returns></returns>
/// <remarks>Documented by Dev08, 2009-01-19</remarks>
public List <ICard> GetCardsByQuery(int id, QueryStruct[] query, QueryOrder orderBy, QueryOrderDir orderDir, int number)
{
List <int> ids = new List <int>();
if (cardStateCount < 0)
{
int cardCount;
using (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser))
{
//create entries in UserCardState for all cards in lm
cmd.CommandText = "SELECT count(*) FROM \"LearningModules_Cards\" WHERE lm_id=@lmid";
cmd.Parameters.Add("@lmid", id);
cardCount = MSSQLCEConn.ExecuteScalar <int>(cmd).Value;
}
using (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser))
{
//create entries in UserCardState for all cards in lm
cmd.CommandText = "SELECT count(*) FROM UserCardState CS INNER JOIN Cards C ON C.ID = CS.cards_id WHERE CS.user_id=@userid AND C.lm_id=@lmid";
cmd.Parameters.Add("@userid", Parent.CurrentUser.Id);
cmd.Parameters.Add("@lmid", id);
cardStateCount = MSSQLCEConn.ExecuteScalar <int>(cmd).Value;
}
if (cardCount != cardStateCount)
{
using (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser))
{
cmd.CommandText = "INSERT INTO UserCardState (user_id, cards_id, box, active) " +
String.Format("SELECT {0}, C.id, 0, 1 FROM Cards C LEFT OUTER JOIN (SELECT * FROM UserCardState WHERE user_id = {0}) CS ON C.ID = CS.cards_id WHERE CS.cards_id IS NULL AND C.lm_id=@lmid", Parent.CurrentUser.Id);
cmd.Parameters.Add("@lmid", id);
MSSQLCEConn.ExecuteNonQuery(cmd);
}
}
}
using (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser))
{
cmd.CommandText = string.Format("SELECT {0}C.id as cid FROM Cards C INNER JOIN UserCardState CS ON C.id=CS.cards_id", number > 0 ? "TOP (" + number + ") " : string.Empty);
List <string> conditions = new List <string>();
foreach (QueryStruct q in query)
{
string cond = "CS.user_id=@uid";
if (q.ChapterId != -1)
{
cond += string.Format(" AND C.chapters_id={0}", q.ChapterId);
}
if (q.BoxId != -1)
{
cond += string.Format(" AND CS.box={0}", q.BoxId);
}
switch (q.CardState)
{
case QueryCardState.Active:
cond += " AND CS.active=1";
break;
case QueryCardState.Inactive:
cond += " AND CS.active=0";
break;
default:
break;
}
conditions.Add(cond);
}
cmd.CommandText += " WHERE C.lm_id=@lmid AND (";
if (conditions.Count > 0)
{
cmd.CommandText += " " + conditions[0];
conditions.RemoveAt(0);
foreach (string cond in conditions)
{
cmd.CommandText += " OR " + cond;
}
}
else
{
cmd.CommandText += " 1=1";
}
cmd.CommandText += ")";
switch (orderBy)
{
case QueryOrder.Id:
cmd.CommandText += " ORDER BY C.id";
break;
case QueryOrder.Chapter:
cmd.CommandText += " ORDER BY C.chapters_id";
break;
case QueryOrder.Random:
cmd.CommandText += " ORDER BY newid()";
break;
case QueryOrder.Timestamp:
cmd.CommandText += " ORDER BY CS.timestamp";
break;
default:
break;
}
if (orderBy == QueryOrder.Chapter || orderBy == QueryOrder.Id)
{
switch (orderDir)
{
case QueryOrderDir.Ascending:
cmd.CommandText += " ASC";
break;
case QueryOrderDir.Descending:
cmd.CommandText += " DESC";
break;
}
}
if (orderBy == QueryOrder.Timestamp)
{
cmd.CommandText += ", C.id " + (orderDir == QueryOrderDir.Ascending ? "ASC" : "DESC");
}
cmd.Parameters.Add("@lmid", id);
cmd.Parameters.Add("@uid", Parent.CurrentUser.Id);
SqlCeDataReader reader = MSSQLCEConn.ExecuteReader(cmd);
List <ICard> cards = new List <ICard>();
//Performance: 180ms!!!
while (reader.Read())
{
cards.Add(new DbCard(Convert.ToInt32(reader["cid"]), false, Parent));
}
reader.Close();
return(cards);
}
}
/// <summary>
/// Gets the settings value.
/// </summary>
/// <param name="snoozeOptionsId">The snooze options id.</param>
/// <param name="cacheObjectType">Type of the cache object.</param>
/// <param name="cacheValue">The cache value.</param>
/// <remarks>Documented by Dev08, 2009-01-13</remarks>
private void GetSettingsValue(int snoozeOptionsId, CacheObject cacheObjectType, out object cacheValue)
{
SqlCeCommand cmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser);
cmd.CommandText = "SELECT * FROM \"SnoozeOptions\" WHERE id=@id";
cmd.Parameters.Add("@id", snoozeOptionsId);
SqlCeDataReader reader = MSSQLCEConn.ExecuteReader(cmd);
reader.Read();
int? sid = DbValueConverter.Convert <int>(reader["id"]);
bool? cardsEnabled = DbValueConverter.Convert <bool>(reader["cards_enabled"]);
bool? rightsEnabled = DbValueConverter.Convert <bool>(reader["rights_enabled"]);
bool? timeEnabled = DbValueConverter.Convert <bool>(reader["time_enabled"]);
int? snoozeCards = DbValueConverter.Convert <int>(reader["snooze_cards"]);
int? snoozeHigh = DbValueConverter.Convert <int>(reader["snooze_high"]);
int? snoozeLow = DbValueConverter.Convert <int>(reader["snooze_low"]);
ESnoozeMode?snoozeMode = DbValueConverter.Convert <ESnoozeMode>(reader["snooze_mode"]);
int? snoozeRights = DbValueConverter.Convert <int>(reader["snooze_rights"]);
int? snoozeTime = DbValueConverter.Convert <int>(reader["snooze_time"]);
reader.Close();
//cache values
DateTime expires = DateTime.Now.Add(Cache.DefaultSettingsValidationTime);
Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.SettingsSnoozeCardsEnabled, snoozeOptionsId, expires)] = cardsEnabled;
Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.SettingsSnoozeRightsEnabled, snoozeOptionsId, expires)] = rightsEnabled;
Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.SettingsSnoozeTimeEnabled, snoozeOptionsId, expires)] = timeEnabled;
Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.SettingsSnoozeCards, snoozeOptionsId, expires)] = snoozeCards;
Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.SettingsSnoozeHigh, snoozeOptionsId, expires)] = snoozeHigh;
Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.SettingsSnoozeLow, snoozeOptionsId, expires)] = snoozeLow;
Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.SettingsSnoozeMode, snoozeOptionsId, expires)] = snoozeMode;
Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.SettingsSnoozeRights, snoozeOptionsId, expires)] = snoozeRights;
Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.SettingsSnoozeTime, snoozeOptionsId, expires)] = snoozeTime;
Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.SettingsSnoozeOptionsId, snoozeOptionsId, expires)] = sid;
//set output value
switch (cacheObjectType)
{
case CacheObject.SettingsSnoozeCardsEnabled: cacheValue = cardsEnabled; break;
case CacheObject.SettingsSnoozeRightsEnabled: cacheValue = rightsEnabled; break;
case CacheObject.SettingsSnoozeTimeEnabled: cacheValue = timeEnabled; break;
case CacheObject.SettingsSnoozeCards: cacheValue = snoozeCards; break;
case CacheObject.SettingsSnoozeHigh: cacheValue = snoozeHigh; break;
case CacheObject.SettingsSnoozeLow: cacheValue = snoozeLow; break;
case CacheObject.SettingsSnoozeMode: cacheValue = snoozeMode; break;
case CacheObject.SettingsSnoozeRights: cacheValue = snoozeRights; break;
case CacheObject.SettingsSnoozeTime: cacheValue = snoozeTime; break;
case CacheObject.SettingsSnoozeOptionsId: cacheValue = sid; break;
default: cacheValue = null; break;
}
}
/// <summary>
/// Creates a new user session.
/// The old one will be automatically closed, in case of inconsistance data.
/// </summary>
/// <param name="lm_id">The lm_id.</param>
/// <returns></returns>
/// <remarks>Documented by Dev10, 2009-01-11</remarks>
/// <remarks>Documented by Dev08, 2009-04-28</remarks>
public int OpenUserSession(int lm_id)
{
//1. Check if the old session is closed
bool previousSessionExisting = false;
DateTime?endtime = null;
int latestSessionId = 0;
SqlCeCommand cmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser);
cmd.CommandText = "SELECT * FROM LearningSessions WHERE lm_id=@lmid AND user_id=@userid ORDER BY starttime DESC";
cmd.Parameters.Add("@lmid", lm_id);
cmd.Parameters.Add("@userid", Parent.CurrentUser.Id);
SqlCeDataReader reader = MSSQLCEConn.ExecuteReader(cmd);
if (reader.Read())
{
previousSessionExisting = true;
try
{
latestSessionId = Convert.ToInt32(reader["id"]);
endtime = Convert.ToDateTime(reader["endtime"]);
}
catch
{
endtime = null;
}
}
//2. Close the previous session, if it hasn't closed before (maybe cause of crash of ML)
if (previousSessionExisting && !endtime.HasValue)
{
cmd.Parameters.Clear();
cmd.CommandText = "UPDATE LearningSessions SET endtime=GETDATE() WHERE id=@id";
cmd.Parameters.Add("@id", latestSessionId);
MSSQLCEConn.ExecuteNonQuery(cmd);
}
//3. Add new session entry to DB
cmd.Parameters.Clear();
cmd.CommandText = "INSERT INTO LearningSessions (user_id, lm_id, starttime, 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, GETDATE(), 0, 0, @pool, @b1, @b2, @b3, @b4, @b5, @b6, @b7, @b8, @b9, @b10); SELECT @@IDENTITY;";
cmd.Parameters.Add("@userid", 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 = MSSQLCEConn.ExecuteScalar <int>(cmd).Value;
//Following Statement does add the "RunningSession" = true to the current Statistic.
MsSqlCeStatisticConnector connector = MsSqlCeStatisticConnector.GetInstance(Parent);
connector.RunningSession = newSessionId;
return(newSessionId);
}
/// <summary>
/// Moves the chapter.
/// How does this function work (by FabThe, who always forgot how it works):
/// If the "FirstChapter.Position" is smaller than the "SecondChapter.Position" the new position of "FirstChapter" will be "SecondChapter.Position" + 5
/// If the "FirstChapter.Position" is greater than the "SecondChapter.Position" the new position of "FirstChapter" will be "SecondChapter.Position" - 5
/// Summary: If the first chapter is on the top and the second chapter in the middle, the new position of the first chapter will be after the second chapter.
/// If the first chapter is in the middle and the second chapter on the top, the new position of the first chapter will be before the second chapter.
/// </summary>
/// <param name="lmid">The id of the learning module.</param>
/// <param name="first_id">The id of the first chapter.</param>
/// <param name="second_id">The id of the second chapter..</param>
/// <remarks>Documented by Dev02, 2008-08-05</remarks>
/// <remarks>Documented by Dev08, 2009-01-12</remarks>
public void MoveChapter(int lmid, int first_id, int second_id)
{
int position1, position2;
using (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(parent.CurrentUser))
{
SqlCeTransaction transaction = cmd.Connection.BeginTransaction();
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 = MSSQLCEConn.ExecuteScalar(cmd);
if (result == null)
{
throw new IdAccessException(first_id);
}
position1 = Convert.ToInt32(result);
using (SqlCeCommand cmd2 = MSSQLCEConn.CreateCommand(parent.CurrentUser))
{
cmd2.CommandText = "SELECT position FROM \"Chapters\" WHERE id=@id AND lm_id=@lmid";
cmd2.Parameters.Add("@id", second_id);
cmd2.Parameters.Add("@lmid", lmid);
object result2 = MSSQLCEConn.ExecuteScalar(cmd2);
if (result2 == null)
{
throw new IdAccessException(second_id);
}
position2 = Convert.ToInt32(result2);
}
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;
}
int serialchapters = 10, increment = 10;
using (SqlCeCommand cmd3 = MSSQLCEConn.CreateCommand(parent.CurrentUser))
{
cmd3.CommandText = "UPDATE \"Chapters\" SET position=@position WHERE id=@id; ";
cmd3.CommandText += "SELECT * FROM \"Chapters\" WHERE lm_id=@lmid ORDER BY position ASC";
cmd3.Parameters.Add("@position", newfirstposition);
cmd3.Parameters.Add("@id", first_id);
cmd3.Parameters.Add("@lmid", lmid);
SqlCeDataReader reader = MSSQLCEConn.ExecuteReader(cmd3);
while (reader.Read())
{
using (SqlCeCommand cmd4 = MSSQLCEConn.CreateCommand(parent.CurrentUser))
{
cmd4.CommandText = "UPDATE \"Chapters\" SET position=@position WHERE \"Chapters\".id=@chapterId";
cmd4.Parameters.Add("@position", serialchapters);
cmd4.Parameters.Add("@chapterId", reader["id"]);
MSSQLCEConn.ExecuteNonQuery(cmd4);
serialchapters += increment;
}
}
reader.Close();
}
transaction.Commit();
}
parent.CurrentUser.Cache.Uncache(ObjectLifetimeIdentifier.GetIdentifier(CacheObject.ChaptersList, lmid));
}
/// <summary>
/// Creates the learn log entry.
/// </summary>
/// <param name="learnLog">The learn log.</param>
/// <remarks>Documented by Dev10, 2009-01-11</remarks>
public void CreateLearnLogEntry(LearnLogStruct learnLog)
{
bool answeredCardCorrect = false;
if (learnLog.MoveType != MoveType.Manual)
{
if (learnLog.NewBox > 1)
{
answeredCardCorrect = true;
}
}
using (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser))
{
cmd.CommandText = "INSERT INTO LearnLog (session_id, user_id, lm_id, cards_id, old_box, new_box, timestamp, duration, learn_mode, move_type, answer, direction, case_sensitive, correct_on_the_fly, percentage_known, percentage_required) ";
cmd.CommandText += "VALUES(@sid, @uid, @lmid, @cid, @obox, @nbox, @ts, @dur, @lmode, @mtype, @answ, @dir, @csen, @cotf, @pknown, @preq); ";
if (learnLog.MoveType != MoveType.Manual && answeredCardCorrect)
{
cmd.CommandText += "UPDATE LearningSessions SET sum_right=sum_right + 1 WHERE id=@sid AND user_id=@uid AND lm_id=@lmid; ";
}
else if (learnLog.MoveType != MoveType.Manual && !answeredCardCorrect)
{
cmd.CommandText += "UPDATE LearningSessions SET sum_wrong=sum_wrong + 1 WHERE id=@sid AND user_id=@uid AND lm_id=@lmid; ";
}
string newBoxContent = string.Empty;
string oldBoxContent = string.Empty;
if (learnLog.NewBox.Value > 0)
{
newBoxContent = "box" + learnLog.NewBox.Value.ToString() + "_content";
}
else
{
newBoxContent = "pool_content";
}
if (learnLog.OldBox.Value > 0)
{
oldBoxContent = "box" + learnLog.OldBox.Value.ToString() + "_content";
}
else
{
oldBoxContent = "pool_content";
}
if (learnLog.NewBox.Value != learnLog.OldBox.Value)
{
cmd.CommandText += "UPDATE LearningSessions SET " + newBoxContent + "=" + newBoxContent + " + 1, " + oldBoxContent + "=" + oldBoxContent + " - 1 WHERE id=@sid AND user_id=@uid AND lm_id=@lmid; ";
}
cmd.Parameters.Add("@sid", learnLog.SessionID.Value);
cmd.Parameters.Add("@uid", Parent.CurrentUser.Id);
cmd.Parameters.Add("@lmid", Parent.CurrentUser.ConnectionString.LmId);
cmd.Parameters.Add("@cid", learnLog.CardsID.Value);
cmd.Parameters.Add("@obox", learnLog.OldBox.Value);
cmd.Parameters.Add("@nbox", learnLog.NewBox.Value);
cmd.Parameters.Add("@ts", learnLog.TimeStamp.Value);
cmd.Parameters.Add("@dur", learnLog.Duration.Value);
cmd.Parameters.Add("@lmode", learnLog.LearnMode.Value.ToString());
cmd.Parameters.Add("@mtype", learnLog.MoveType.Value.ToString());
cmd.Parameters.Add("@answ", learnLog.Answer);
cmd.Parameters.Add("@dir", learnLog.Direction.Value.ToString());
cmd.Parameters.Add("@csen", learnLog.CaseSensitive);
cmd.Parameters.Add("@cotf", learnLog.CorrectOnTheFly);
cmd.Parameters.Add("@pknown", learnLog.PercentageKnown);
cmd.Parameters.Add("@preq", learnLog.PercentageRequired);
MSSQLCEConn.ExecuteNonQuery(cmd);
}
//delete caches
Parent.CurrentUser.Cache.Uncache(ObjectLifetimeIdentifier.GetIdentifier(CacheObject.StatisticWrongCards, learnLog.SessionID.Value));
Parent.CurrentUser.Cache.Uncache(ObjectLifetimeIdentifier.GetIdentifier(CacheObject.StatisticCorrectCards, learnLog.SessionID.Value));
Parent.CurrentUser.Cache.Uncache(ObjectLifetimeIdentifier.GetIdentifier(CacheObject.StatisticContentOfBoxes, learnLog.SessionID.Value));
}
/// <summary>
/// Gets the user learning module settings.
/// </summary>
/// <param name="id">The id.</param>
/// <returns></returns>
/// <remarks>Documented by Dev05, 2009-01-16</remarks>
public int GetUserLearningModuleSettings(int id)
{
int cnt;
SqlCeCommand cmd_main = MSSQLCEConn.CreateCommand(Parent.CurrentUser);
cmd_main.CommandText = "SELECT count(*) FROM \"UserProfilesLearningModulesSettings\" " +
"WHERE user_id=@uid and lm_id=@lm_id;";
cmd_main.Parameters.Add("@uid", Parent.CurrentUser.Id);
cmd_main.Parameters.Add("@lm_id", id);
cnt = MSSQLCEConn.ExecuteScalar <int>(cmd_main).Value;
if (cnt > 0)
{
//Workaround for Issue: ML-2458 If System Date is older than Timestamps only one card is asked
SqlCeCommand timeCmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser);
timeCmd.CommandText = "SELECT cards_id FROM \"UserCardState\" WHERE user_id=@uid AND timestamp > @now";
timeCmd.Parameters.Add("@uid", Parent.CurrentUser.Id);
timeCmd.Parameters.Add("@now", DateTime.Now);
SqlCeDataReader reader = MSSQLCEConn.ExecuteReader(timeCmd);
List <int> wrongCards = new List <int>();
while (reader.Read())
{
wrongCards.Add(Convert.ToInt32(reader["cards_id"]));
}
reader.Close();
foreach (int cid in wrongCards)
{
SqlCeCommand updateCmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser);
updateCmd.CommandText = "UPDATE \"UserCardState\" SET timestamp = @time WHERE user_id=@uid AND cards_id=@cid";
updateCmd.Parameters.Add("@uid", Parent.CurrentUser.Id);
updateCmd.Parameters.Add("@cid", cid);
updateCmd.Parameters.Add("@time", DateTime.Now.AddSeconds(-Math.PI / 2));
MSSQLCEConn.ExecuteNonQuery(updateCmd);
}
//End of Workaround for Issue: ML-2458
SqlCeCommand cmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser);
cmd.CommandText = "SELECT settings_id FROM \"UserProfilesLearningModulesSettings\" " +
"WHERE user_id=@uid and lm_id=@lm_id;";
cmd.Parameters.Add("@uid", Parent.CurrentUser.Id);
cmd.Parameters.Add("@lm_id", id);
return(MSSQLCEConn.ExecuteScalar <int>(cmd).Value);
}
else
{
int newSettings = MsSqlCeSettingsConnector.CreateNewSettings(Parent);
SqlCeCommand cmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser);
cmd.CommandText = "INSERT INTO \"UserProfilesLearningModulesSettings\" (user_id, lm_id, settings_id) " +
"VALUES (@uid, @lm_id, @settings_id)";
cmd.Parameters.Add("@uid", Parent.CurrentUser.Id);
cmd.Parameters.Add("@lm_id", id);
cmd.Parameters.Add("@settings_id", newSettings);
MSSQLCEConn.ExecuteNonQuery(cmd);
cmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser);
cmd.CommandText = "SELECT id FROM \"Chapters\" " +
"WHERE lm_id=@lm_id;";
cmd.Parameters.Add("@lm_id", id);
SqlCeDataReader reader = MSSQLCEConn.ExecuteReader(cmd);
List <int> chapterIds = new List <int>();
while (reader.Read())
{
chapterIds.Add(Convert.ToInt32(reader["id"]));
}
reader.Close();
foreach (int chapterId in chapterIds)
{
SqlCeCommand cmd2 = MSSQLCEConn.CreateCommand(Parent.CurrentUser);
cmd2.CommandText = "INSERT INTO \"SelectedLearnChapters\" (chapters_id, settings_id) VALUES (@cid, @sid);";
cmd2.Parameters.Add("@cid", chapterId);
cmd2.Parameters.Add("@sid", newSettings);
MSSQLCEConn.ExecuteNonQuery(cmd2);
}
SqlCeCommand cmd3 = MSSQLCEConn.CreateCommand(Parent.CurrentUser);
cmd3.CommandText = "SELECT id FROM \"Cards\" WHERE id IN " +
"(SELECT cards_id FROM \"LearningModules_Cards\" WHERE lm_id=@lm_id)";
cmd3.Parameters.Add("@lm_id", id);
SqlCeDataReader reader2 = MSSQLCEConn.ExecuteReader(cmd3);
List <int> cardIds = new List <int>();
while (reader2.Read())
{
cardIds.Add(Convert.ToInt32(reader2["id"]));
}
reader2.Close();
foreach (int cid in cardIds)
{
SqlCeCommand cmd4 = MSSQLCEConn.CreateCommand(Parent.CurrentUser);
cmd4.CommandText = "INSERT INTO \"UserCardState\" (user_id, cards_id, box, active) VALUES (@param_user_id, @param_cards_id, 0, 1);";
cmd4.Parameters.Add("@param_user_id", Parent.CurrentUser.Id);
cmd4.Parameters.Add("@param_cards_id", cid);
MSSQLCEConn.ExecuteNonQuery(cmd4);
}
Parent.CurrentUser.Cache.Clear();
return(newSettings);
}
}
