/// <summary>
/// Gets a list of cards specified by a query.
/// If the query array is null or the lenght is 0 then all cards are returned.
/// </summary>
/// <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 of cards to return (0 for all).</param>
/// <returns></returns>
/// <remarks>Documented by Dev03, 2007-09-04</remarks>
/// <remarks>Documented by Dev03, 2009-01-13</remarks>
public List <ICard> GetCards(QueryStruct[] query, QueryOrder orderBy, QueryOrderDir orderDir, int number)
{
List <ICard> cards = connector.GetCardsByQuery(LearningModuleId, query, orderBy, orderDir, number);
List <ICard> visibleCards = new List <ICard>();
foreach (ICard card in cards)
{
if (this.HasPermission(PermissionTypes.Visible))
{
visibleCards.Add(card);
}
}
return(visibleCards);
}
/// <summary>
/// Gets the selection of the form controls.
/// </summary>
/// <param name="boxes">The boxes.</param>
/// <param name="cardorder">The cardorder.</param>
/// <param name="cardorderdir">The cardorderdir.</param>
/// <param name="cardstate">The cardstate.</param>
/// <remarks>Documented by Dev02, 2007-11-26</remarks>
private void GetGUISelection(out List <int> boxes, out QueryOrder cardorder, out QueryOrderDir cardorderdir, out QueryCardState cardstate)
{
boxes = new System.Collections.Generic.List <int>();
if (checkBoxAllBoxes.Checked)
{
for (int index = 0; index <= Dictionary.Boxes.Count; index++)
{
boxes.Add(index);
}
}
else
{
foreach (int index in listViewBoxesSelection.CheckedIndices)
{
boxes.Add(index);
}
}
cardorder = QueryOrdersEnum[comboBoxCardOrder.SelectedIndex < 0 ? 0 : comboBoxCardOrder.SelectedIndex];
cardorderdir = cbReverseOrder.Checked ? QueryOrderDir.Descending : QueryOrderDir.Ascending;
cardstate = QueryCardState.Active;
}
/// <summary>
/// Gets the selection of the form controls.
/// </summary>
/// <param name="boxes">The boxes.</param>
/// <param name="cardorder">The cardorder.</param>
/// <param name="cardorderdir">The cardorderdir.</param>
/// <param name="cardstate">The cardstate.</param>
/// <remarks>Documented by Dev02, 2007-11-26</remarks>
private void GetGUISelection(out List<int> boxes, out QueryOrder cardorder, out QueryOrderDir cardorderdir, out QueryCardState cardstate)
{
boxes = new System.Collections.Generic.List<int>();
if (checkBoxAllBoxes.Checked)
{
for (int index = 0; index <= Dictionary.Boxes.Count; index++)
boxes.Add(index);
}
else
{
foreach (int index in listViewBoxesSelection.CheckedIndices)
boxes.Add(index);
}
cardorder = QueryOrdersEnum[comboBoxCardOrder.SelectedIndex < 0 ? 0 : comboBoxCardOrder.SelectedIndex];
cardorderdir = cbReverseOrder.Checked ? QueryOrderDir.Descending : QueryOrderDir.Ascending;
cardstate = QueryCardState.Active;
}
/// <summary>
/// Gets the cards.
/// </summary>
/// <param name="query">The query.</param>
/// <param name="orderBy">The order type.</param>
/// <param name="orderDir">The order direction. The orderDir has no effect if <see cref="T:MLifter.DAL.QueryOrder">orderBy</see> is Random or None.</param>
/// <param name="number">The number cards to return (0 for all).</param>
/// <returns></returns>
/// <remarks>Documented by Dev03, 2007-08-30</remarks>
public List <ICard> GetCards(QueryStruct[] query, QueryOrder orderBy, QueryOrderDir orderDir, int number)
{
//filter the cards
List <ICard> cards = new List <ICard>();
if ((query != null) && (query.Length != 0))
{
foreach (QueryStruct qry in query)
{
List <ICard> foundCards = m_cards.FindAll(
delegate(ICard card)
{
bool isMatch = true;
if (qry.BoxId >= 0)
{
isMatch = isMatch && (qry.BoxId == card.Box);
}
if (qry.ChapterId >= 0)
{
isMatch = isMatch && (qry.ChapterId == card.Chapter);
}
switch (qry.CardState)
{
case QueryCardState.Active:
isMatch = isMatch && (card.Box >= 0);
break;
case QueryCardState.Inactive:
isMatch = isMatch && (card.Box < 0);
break;
case QueryCardState.All:
default:
break;
}
return(isMatch);
});
cards.AddRange(foundCards);
}
}
else
{
cards.AddRange(m_cards); //add all cards
}
//sort the cards
if (orderBy != QueryOrder.None)
{
if (orderBy == QueryOrder.Random)
{
foreach (ICard card in cards)
{
((XmlCard)card).Random = m_oDictionary.GetRandomNumber();
}
}
cards.Sort(
delegate(ICard left, ICard right)
{
int result;
switch (orderBy)
{
case QueryOrder.Id:
result = Comparer <int> .Default.Compare(left.Id, right.Id);
break;
case QueryOrder.Chapter:
result = Comparer <int> .Default.Compare(left.Chapter, right.Chapter);
break;
case QueryOrder.Box:
result = Comparer <int> .Default.Compare(left.Box, right.Box);
break;
case QueryOrder.Answer:
result = Comparer <string> .Default.Compare(left.Answer.ToString(), right.Answer.ToString());
break;
case QueryOrder.Question:
result = Comparer <string> .Default.Compare(left.Question.ToString(), right.Question.ToString());
break;
case QueryOrder.Timestamp:
result = Comparer <DateTime> .Default.Compare(left.Timestamp, right.Timestamp);
break;
case QueryOrder.Random:
result = Comparer <int> .Default.Compare(((XmlCard)left).Random, ((XmlCard)right).Random);
break;
default:
result = 0;
break;
}
return(result);
});
}
if ((orderBy != QueryOrder.Random) && (orderBy != QueryOrder.None))
{
if (orderDir == QueryOrderDir.Descending)
{
cards.Reverse();
}
}
if ((number > 0) && (number < cards.Count))
{
return(cards.GetRange(0, number));;
}
else
{
return(cards);
}
}
/// <summary>
/// Gets the cards.
/// </summary>
/// <param name="query">The query.</param>
/// <param name="orderBy">The order type.</param>
/// <param name="orderDir">The order direction. The orderDir has no effect if <see cref="T:MLifter.DAL.QueryOrder">orderBy</see> is Random or None.</param>
/// <param name="number">The number cards to return (0 for all).</param>
/// <returns></returns>
/// <remarks>Documented by Dev03, 2007-08-30</remarks>
public List<ICard> GetCards(QueryStruct[] query, QueryOrder orderBy, QueryOrderDir orderDir, int number)
{
//filter the cards
List<ICard> cards = new List<ICard>();
if ((query != null) && (query.Length != 0))
{
foreach (QueryStruct qry in query)
{
List<ICard> foundCards = m_cards.FindAll(
delegate(ICard card)
{
bool isMatch = true;
if (qry.BoxId >= 0) isMatch = isMatch && (qry.BoxId == card.Box);
if (qry.ChapterId >= 0) isMatch = isMatch && (qry.ChapterId == card.Chapter);
switch (qry.CardState)
{
case QueryCardState.Active:
isMatch = isMatch && (card.Box >= 0);
break;
case QueryCardState.Inactive:
isMatch = isMatch && (card.Box < 0);
break;
case QueryCardState.All:
default:
break;
}
return isMatch;
});
cards.AddRange(foundCards);
}
}
else
{
cards.AddRange(m_cards); //add all cards
}
//sort the cards
if (orderBy != QueryOrder.None)
{
if (orderBy == QueryOrder.Random)
{
foreach(ICard card in cards)
((XmlCard)card).Random = m_oDictionary.GetRandomNumber();
}
cards.Sort(
delegate(ICard left, ICard right)
{
int result;
switch (orderBy)
{
case QueryOrder.Id:
result = Comparer<int>.Default.Compare(left.Id, right.Id);
break;
case QueryOrder.Chapter:
result = Comparer<int>.Default.Compare(left.Chapter, right.Chapter);
break;
case QueryOrder.Box:
result = Comparer<int>.Default.Compare(left.Box, right.Box);
break;
case QueryOrder.Answer:
result = Comparer<string>.Default.Compare(left.Answer.ToString(), right.Answer.ToString());
break;
case QueryOrder.Question:
result = Comparer<string>.Default.Compare(left.Question.ToString(), right.Question.ToString());
break;
case QueryOrder.Timestamp:
result = Comparer<DateTime>.Default.Compare(left.Timestamp, right.Timestamp);
break;
case QueryOrder.Random:
result = Comparer<int>.Default.Compare(((XmlCard)left).Random, ((XmlCard)right).Random);
break;
default:
result = 0;
break;
}
return result;
});
}
if ((orderBy != QueryOrder.Random) && (orderBy != QueryOrder.None))
{
if (orderDir == QueryOrderDir.Descending)
{
cards.Reverse();
}
}
if ((number > 0) && (number < cards.Count))
{
return cards.GetRange(0, number); ;
}
else
{
return cards;
}
}
/// <summary>
/// Gets the selection of the controls on the wizard page
/// </summary>
/// <param name="cardorder">The cardorder.</param>
/// <param name="cardorderdir">The cardorderdir.</param>
/// <remarks>Documented by Dev02, 2008-01-03</remarks>
private void GetSelection(out QueryOrder cardorder, out QueryOrderDir cardorderdir)
{
cardorder = QueryOrdersEnum[comboBoxSorting.SelectedIndex < 0 ? 0 : comboBoxSorting.SelectedIndex];
cardorderdir = checkBoxReverseOrder.Checked ? QueryOrderDir.Descending : QueryOrderDir.Ascending;
}
/// <summary>
/// Gets a list of cards specified by a query.
/// If the query array is null or the lenght is 0 then all cards are returned.
/// </summary>
/// <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 of cards to return (0 for all).</param>
/// <returns></returns>
/// <remarks>Documented by Dev03, 2007-09-04</remarks>
/// <remarks>Documented by Dev03, 2009-03-23</remarks>
public List<ICard> GetCards(QueryStruct[] query, QueryOrder orderBy, QueryOrderDir orderDir, int number)
{
throw new NotImplementedException();
}
public List <ICard> GetCardsByQuery(int id, QueryStruct[] query, QueryOrder orderBy, QueryOrderDir orderDir, int number)
{
using (NpgsqlConnection con = PostgreSQLConn.CreateConnection(Parent.CurrentUser))
{
using (NpgsqlCommand cmd = con.CreateCommand())
{
//create entries in UserCardState for all cards in lm
cmd.CommandText = "SELECT \"FillUpUserCardState\"(:userid, :lmid)";
cmd.Parameters.Add("userid", Parent.CurrentUser.Id);
cmd.Parameters.Add("lmid", id);
PostgreSQLConn.ExecuteNonQuery(cmd, Parent.CurrentUser);
}
using (NpgsqlCommand cmd = con.CreateCommand())
{
cmd.CommandText = "SELECT \"Cards\".id as cid FROM \"Cards\", \"UserCardState\"";
List <string> conditions = new List <string>();
foreach (QueryStruct q in query)
{
string cond = " \"Cards\".lm_id=:lmid AND \"Cards\".id=\"UserCardState\".cards_id AND \"UserCardState\".user_id=:uid";
if (q.ChapterId != -1)
{
cond += string.Format(" AND \"Cards\".chapters_id={0}", q.ChapterId);
}
if (q.BoxId != -1)
{
cond += string.Format(" AND \"UserCardState\".box={0}", q.BoxId);
}
switch (q.CardState)
{
case QueryCardState.Active:
cond += " AND \"UserCardState\".active=true";
break;
case QueryCardState.Inactive:
cond += " AND \"UserCardState\".active=false";
break;
default:
break;
}
conditions.Add(cond);
}
if (conditions.Count > 0)
{
cmd.CommandText += " WHERE " + conditions[0];
conditions.RemoveAt(0);
foreach (string cond in conditions)
{
cmd.CommandText += " OR " + cond;
}
}
else
{
cmd.CommandText += " WHERE \"Cards\".id=\"UserCardState\".cards_id AND \"UserCardState\".user_id=:uid AND \"Cards\".lm_id=:lmid";
}
switch (orderBy)
{
case QueryOrder.Id:
cmd.CommandText += " ORDER BY \"Cards\".id";
break;
case QueryOrder.Chapter:
cmd.CommandText += " ORDER BY \"Cards\".chapter_id";
break;
case QueryOrder.Random:
cmd.CommandText += " ORDER BY random()";
break;
case QueryOrder.Timestamp:
cmd.CommandText += " ORDER BY \"UserCardState\".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 += ", \"Cards\".id " + (orderDir == QueryOrderDir.Ascending ? "ASC" : "DESC");
}
cmd.CommandText = string.Format("SELECT cid FROM ({0}) as cards {1}",
cmd.CommandText, number > 0 ? "LIMIT " + number : string.Empty);
cmd.Parameters.Add("lmid", id);
cmd.Parameters.Add("uid", Parent.CurrentUser.Id);
NpgsqlDataReader reader = PostgreSQLConn.ExecuteReader(cmd, Parent.CurrentUser);
List <ICard> cards = new List <ICard>();
while (reader.Read())
{
cards.Add(new DbCard(Convert.ToInt32(reader["cid"]), false, Parent));
}
return(cards);
}
}
}
/// <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 a list of cards specified by a query.
/// If the query array is null or the lenght is 0 then all cards are returned.
/// </summary>
/// <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 of cards to return (0 for all).</param>
/// <returns></returns>
/// <remarks>Documented by Dev03, 2007-09-04</remarks>
/// <remarks>Documented by Dev03, 2009-03-23</remarks>
public List <ICard> GetCards(QueryStruct[] query, QueryOrder orderBy, QueryOrderDir orderDir, int number)
{
throw new NotImplementedException();
}
public List<ICard> GetCardsByQuery(int id, QueryStruct[] query, QueryOrder orderBy, QueryOrderDir orderDir, int number)
{
using (NpgsqlConnection con = PostgreSQLConn.CreateConnection(Parent.CurrentUser))
{
using (NpgsqlCommand cmd = con.CreateCommand())
{
//create entries in UserCardState for all cards in lm
cmd.CommandText = "SELECT \"FillUpUserCardState\"(:userid, :lmid)";
cmd.Parameters.Add("userid", Parent.CurrentUser.Id);
cmd.Parameters.Add("lmid", id);
PostgreSQLConn.ExecuteNonQuery(cmd, Parent.CurrentUser);
}
using (NpgsqlCommand cmd = con.CreateCommand())
{
cmd.CommandText = "SELECT \"Cards\".id as cid FROM \"Cards\", \"UserCardState\"";
List<string> conditions = new List<string>();
foreach (QueryStruct q in query)
{
string cond = " \"Cards\".lm_id=:lmid AND \"Cards\".id=\"UserCardState\".cards_id AND \"UserCardState\".user_id=:uid";
if (q.ChapterId != -1)
cond += string.Format(" AND \"Cards\".chapters_id={0}", q.ChapterId);
if (q.BoxId != -1)
cond += string.Format(" AND \"UserCardState\".box={0}", q.BoxId);
switch (q.CardState)
{
case QueryCardState.Active:
cond += " AND \"UserCardState\".active=true";
break;
case QueryCardState.Inactive:
cond += " AND \"UserCardState\".active=false";
break;
default:
break;
}
conditions.Add(cond);
}
if (conditions.Count > 0)
{
cmd.CommandText += " WHERE " + conditions[0];
conditions.RemoveAt(0);
foreach (string cond in conditions)
cmd.CommandText += " OR " + cond;
}
else
cmd.CommandText += " WHERE \"Cards\".id=\"UserCardState\".cards_id AND \"UserCardState\".user_id=:uid AND \"Cards\".lm_id=:lmid";
switch (orderBy)
{
case QueryOrder.Id:
cmd.CommandText += " ORDER BY \"Cards\".id";
break;
case QueryOrder.Chapter:
cmd.CommandText += " ORDER BY \"Cards\".chapter_id";
break;
case QueryOrder.Random:
cmd.CommandText += " ORDER BY random()";
break;
case QueryOrder.Timestamp:
cmd.CommandText += " ORDER BY \"UserCardState\".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 += ", \"Cards\".id " + (orderDir == QueryOrderDir.Ascending ? "ASC" : "DESC");
cmd.CommandText = string.Format("SELECT cid FROM ({0}) as cards {1}",
cmd.CommandText, number > 0 ? "LIMIT " + number : string.Empty);
cmd.Parameters.Add("lmid", id);
cmd.Parameters.Add("uid", Parent.CurrentUser.Id);
NpgsqlDataReader reader = PostgreSQLConn.ExecuteReader(cmd, Parent.CurrentUser);
List<ICard> cards = new List<ICard>();
while (reader.Read())
cards.Add(new DbCard(Convert.ToInt32(reader["cid"]), false, Parent));
return cards;
}
}
}
/// <summary>
/// Gets a list of cards specified by a query.
/// If the query array is null or the lenght is 0 then all cards are returned.
/// </summary>
/// <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 of cards to return (0 for all).</param>
/// <returns></returns>
/// <remarks>Documented by Dev03, 2007-09-04</remarks>
/// <remarks>Documented by Dev03, 2009-01-13</remarks>
public List<ICard> GetCards(QueryStruct[] query, QueryOrder orderBy, QueryOrderDir orderDir, int number)
{
List<ICard> cards = connector.GetCardsByQuery(LearningModuleId, query, orderBy, orderDir, number);
List<ICard> visibleCards = new List<ICard>();
foreach (ICard card in cards)
if (this.HasPermission(PermissionTypes.Visible))
visibleCards.Add(card);
return visibleCards;
}
/// <summary>
/// Gets the selection of the controls on the wizard page
/// </summary>
/// <param name="cardorder">The cardorder.</param>
/// <param name="cardorderdir">The cardorderdir.</param>
/// <remarks>Documented by Dev02, 2008-01-03</remarks>
private void GetSelection(out QueryOrder cardorder, out QueryOrderDir cardorderdir)
{
cardorder = QueryOrdersEnum[comboBoxSorting.SelectedIndex < 0 ? 0 : comboBoxSorting.SelectedIndex];
cardorderdir = checkBoxReverseOrder.Checked ? QueryOrderDir.Descending : QueryOrderDir.Ascending;
}
/// <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);
}
}