/// <summary>
/// Gets the number of reviews due for the current date.
/// </summary>
/// <returns>Number of reviews due for the current date.</returns>
public long GetReviewsCount()
{
long result = -1;
DaoConnection connection = null;
try
{
connection = DaoConnection.Open(DaoConnectionEnum.SrsDatabase);
result = (long)connection.QueryScalar(
"SELECT COUNT(1) FROM " + SqlHelper.Table_SrsEntry + " se WHERE se."
+ SqlHelper.Field_SrsEntry_SuspensionDate + " IS NULL AND se."
+ SqlHelper.Field_SrsEntry_NextAnswerDate + " <= @date",
new DaoParameter("@date", DateTime.UtcNow.Ticks));
}
finally
{
if (connection != null)
{
connection.Dispose();
}
}
return(result);
}
/// <summary>
/// See <see cref="Kanji.Database.Dao.VocabDao.GetFilteredVocab"/>.
/// Returns the results count.
/// </summary>
public long GetFilteredVocabCount(KanjiEntity kanji,
string readingFilter, string meaningFilter, VocabCategory categoryFilter, int jlptLevel, int wkLevel)
{
List <DaoParameter> parameters = new List <DaoParameter>();
string sqlFilterClauses = BuildVocabFilterClauses(parameters, kanji,
readingFilter, meaningFilter, categoryFilter, jlptLevel, wkLevel);
using (DaoConnection connection
= DaoConnection.Open(DaoConnectionEnum.KanjiDatabase))
{
return((long)connection.QueryScalar(
string.Format("SELECT count(1) FROM {0} v {1}",
SqlHelper.Table_Vocab,
sqlFilterClauses),
parameters.ToArray()));
}
}
/// <summary>
/// See <see cref="Kanji.Database.Dao.KanjiDao.GetFilteredKanji"/>.
/// Returns the result count.
/// </summary>
public long GetFilteredKanjiCount(RadicalGroup[] radicals, string textFilter,
string meaningFilter, string anyReadingFilter, string onYomiFilter, string kunYomiFilter,
string nanoriFilter, int jlptLevel, int wkLevel)
{
List <DaoParameter> parameters = new List <DaoParameter>();
string sqlFilter = BuildKanjiFilterClauses(parameters, radicals, textFilter,
meaningFilter, anyReadingFilter, onYomiFilter, kunYomiFilter, nanoriFilter,
jlptLevel, wkLevel);
using (DaoConnection connection =
DaoConnection.Open(DaoConnectionEnum.KanjiDatabase))
{
return((long)connection.QueryScalar(
string.Format("SELECT COUNT(1) FROM {0} k {1}",
SqlHelper.Table_Kanji,
sqlFilter),
parameters.ToArray()));
}
}
/// <summary>
/// Gets the number of items matching the given filter clauses.
/// </summary>
/// <param name="filterClauses">Filter clauses to match.</param>
/// <returns>Number of items matching the filter clauses.</returns>
public long GetFilteredItemsCount(FilterClause[] filterClauses)
{
List <DaoParameter> parameters = new List <DaoParameter>();
string whereClause = string.Empty;
bool isFiltered = false;
foreach (FilterClause clause in filterClauses)
{
if (clause != null)
{
string sqlClause = clause.GetSqlWhereClause(!isFiltered, parameters);
if (!string.IsNullOrEmpty(sqlClause))
{
whereClause += sqlClause + " ";
isFiltered = true;
}
}
}
DaoConnection connection = null;
long result = -1;
try
{
connection = DaoConnection.Open(DaoConnectionEnum.SrsDatabase);
result = (long)connection.QueryScalar(
"SELECT COUNT(1) FROM " + SqlHelper.Table_SrsEntry + " se "
+ whereClause,
parameters.ToArray());
}
finally
{
if (connection != null)
{
connection.Dispose();
}
}
return(result);
}
public VocabEntity GetSingleVocabByKanaReading(string kanaReading)
{
DaoConnection connection = null;
try
{
//connection = DaoConnection.Open(DaoConnectionEnum.KanjiDatabase);
connection = _connection;
long count = (long)connection.QueryScalar(
string.Format("SELECT COUNT(1) FROM {0} WHERE {1}=@kanaWriting",
SqlHelper.Table_Vocab,
SqlHelper.Field_Vocab_KanaWriting),
new DaoParameter("@kanaWriting", kanaReading));
if (count == 1)
{
IEnumerable <NameValueCollection> vocabs = connection.Query(
string.Format("SELECT * FROM {0} WHERE {1}=@kanaWriting",
SqlHelper.Table_Vocab,
SqlHelper.Field_Vocab_KanaWriting),
new DaoParameter("@kanaWriting", kanaReading));
VocabBuilder builder = new VocabBuilder();
return(builder.BuildEntity(vocabs.First(), null));
}
}
finally
{
//if (connection != null)
//{
// connection.Dispose();
//}
}
return(null);
}
public bool UpdateFrequencyRankOnSingleKanaMatch(string kanaReading, int rank)
{
DaoConnection connection = null;
try
{
//connection = DaoConnection.Open(DaoConnectionEnum.KanjiDatabase);
connection = _connection;
long count = (long)connection.QueryScalar(
string.Format("SELECT COUNT(1) FROM {0} WHERE {1}=@kanaWriting",
SqlHelper.Table_Vocab,
SqlHelper.Field_Vocab_KanaWriting),
new DaoParameter("@kanaWriting", kanaReading));
if (count == 1)
{
return(connection.ExecuteNonQuery(
string.Format("UPDATE {0} SET {1}={1}+@rank WHERE {2}=@kanaWriting",
SqlHelper.Table_Vocab,
SqlHelper.Field_Vocab_FrequencyRank,
SqlHelper.Field_Vocab_KanaWriting),
new DaoParameter("@rank", rank),
new DaoParameter("@kanaWriting", kanaReading)) == 1);
}
}
finally
{
//if (connection != null)
//{
// connection.Dispose();
//}
}
return(false);
}
/// <summary>
/// Gets all review information for the current date.
/// </summary>
/// <returns>Review info for the current date.</returns>
public ReviewInfo GetReviewInfo()
{
ReviewInfo info = new ReviewInfo();
DaoConnection connection = null;
try
{
connection = DaoConnection.Open(DaoConnectionEnum.SrsDatabase);
// Query the review count for this date.
info.AvailableReviewsCount = (long)connection.QueryScalar(
"SELECT COUNT(1) FROM " + SqlHelper.Table_SrsEntry + " se WHERE se."
+ SqlHelper.Field_SrsEntry_SuspensionDate + " IS NULL AND se."
+ SqlHelper.Field_SrsEntry_NextAnswerDate + " <= @date",
new DaoParameter("@date", DateTime.UtcNow.Ticks));
// Query the review count for today.
DateTime endOfToday = DateTime.Now.Date.AddDays(1).ToUniversalTime();
info.TodayReviewsCount = (long)connection.QueryScalar(
"SELECT COUNT(1) FROM " + SqlHelper.Table_SrsEntry + " se WHERE se."
+ SqlHelper.Field_SrsEntry_SuspensionDate + " IS NULL AND se."
+ SqlHelper.Field_SrsEntry_NextAnswerDate + " <= @date",
new DaoParameter("@date", endOfToday.Ticks));
// Query the first review date.
object nextAnswerDate = connection.QueryScalar(
"SELECT MIN(" + SqlHelper.Field_SrsEntry_NextAnswerDate + ") FROM "
+ SqlHelper.Table_SrsEntry + " WHERE "
+ SqlHelper.Field_SrsEntry_SuspensionDate + " IS NULL AND "
+ SqlHelper.Field_SrsEntry_NextAnswerDate + " NOT NULL");
if (nextAnswerDate != null && nextAnswerDate is long)
{
info.FirstReviewDate = new DateTime((long)nextAnswerDate,
DateTimeKind.Utc);
}
// Query all counts/total info.
IEnumerable <NameValueCollection> results = connection.Query(
"SELECT COUNT(" + SqlHelper.Field_SrsEntry_AssociatedKanji + ") "
+ SqlKey_KanjiCount + ",COUNT("
+ SqlHelper.Field_SrsEntry_AssociatedVocab + ") " + SqlKey_VocabCount
+ ",SUM(" + SqlHelper.Field_SrsEntry_SuccessCount + ") "
+ SqlKey_SuccessCount + ",SUM(" + SqlHelper.Field_SrsEntry_FailureCount
+ ") " + SqlKey_FailureCount + " FROM " + SqlHelper.Table_SrsEntry);
if (results.Any())
{
NameValueCollection nvcInfo = results.First();
info.KanjiItemsCount = nvcInfo.ReadLong(SqlKey_KanjiCount) ?? 0;
info.VocabItemsCount = nvcInfo.ReadLong(SqlKey_VocabCount) ?? 0;
info.TotalSuccessCount = nvcInfo.ReadLong(SqlKey_SuccessCount) ?? 0;
info.TotalFailureCount = nvcInfo.ReadLong(SqlKey_FailureCount) ?? 0;
}
// Query item count by level.
results = connection.Query("SELECT " + SqlHelper.Field_SrsEntry_CurrentGrade
+ " " + SqlKey_Grade + ", SUM(1) " + SqlKey_ItemCount + " FROM "
+ SqlHelper.Table_SrsEntry + " GROUP BY "
+ SqlHelper.Field_SrsEntry_CurrentGrade);
foreach (NameValueCollection nvcGroup in results)
{
short grade = nvcGroup.ReadShort(SqlKey_Grade) ?? 0;
long itemCount = nvcGroup.ReadLong(SqlKey_ItemCount) ?? 0;
info.ReviewsPerLevel.Add(grade, itemCount);
}
}
finally
{
if (connection != null)
{
connection.Dispose();
}
}
return(info);
}
