/// <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); }