/// <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 Dev08, 2009-01-09</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 (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(parent.CurrentUser))
{
cmd.CommandText = "(SELECT lm_id FROM \"LearningModules_Cards\" WHERE cards_id=@id)";
cmd.Parameters.Add("@id", id);
int lmid = Convert.ToInt32(MSSQLCEConn.ExecuteScalar(cmd));
cmd.Parameters.Clear();
cmd.CommandText = "SELECT chapters_id, cards_id FROM Chapters_Cards WHERE chapters_id IN " +
"(SELECT chapters_id FROM Chapters WHERE lm_id=@lmid)";
cmd.Parameters.Add("@lmid", lmid);
SqlCeDataReader reader = MSSQLCEConn.ExecuteReader(cmd);
Dictionary <int, int> cardChapters = new Dictionary <int, int>();
while (reader.Read())
{
object[] chapters = new object[2];
reader.GetValues(chapters);
int chid = Convert.ToInt32(chapters[0]);
int cid = Convert.ToInt32(chapters[1]);
if (!cardChapters.ContainsKey(cid))
{
cardChapters[cid] = chid;
}
}
reader.Close();
// this should fix the bug where for some reason a card didn't have chapter assigned [ML-1708] (and similar)
int chapterId = 0;
if (!cardChapters.TryGetValue(id, out chapterId))
{
lock (cardChapters)
{
foreach (int c in cardChapters.Values)
{
chapterId = c;
break;
}
SetChapter(id, chapterId);
cardChapters.Add(id, chapterId);
}
}
parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.CardChapterList, 0, new TimeSpan(23, 59, 59))] = cardChapters;
return(chapterId);
}
}
public object[] Read()
{
if (reader.Read())
{
object[] values = new object[reader.FieldCount];
reader.GetValues(values);
return(values);
}
return(null);
}
public DataTable ExecuteQuery(String queryString)
{
DataTable result_L = new DataTable();
SqlCeCommand command_L = _sqlCeConnection.CreateCommand();
command_L.CommandText = queryString;
command_L.CommandType = System.Data.CommandType.Text;
SqlCeTransaction sqlCeTransaction = _sqlCeConnection.BeginTransaction(System.Data.IsolationLevel.Serializable);
SqlCeDataReader dataReader = command_L.ExecuteReader();
for (int i = 0; i < dataReader.FieldCount; ++i)
{
result_L.Columns.Add(dataReader.GetName(i));
}
while (dataReader.Read())
{
Object[] rowData_L = new object[dataReader.FieldCount];
dataReader.GetValues(rowData_L);
result_L.Rows.Add(rowData_L);
}
sqlCeTransaction.Commit(CommitMode.Immediate);
return(result_L);
}
/// <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>());
}
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;
}
