/// <summary>
/// 获取所有已学习过的单词
/// </summary>
/// <returns>The all learned words.</returns>
public List <HLHWord> GetAllLearnedWords()
{
List <HLHWord> learnedWords = new List <HLHWord>();
string tableName = GetCurrentLearningWordsTabelName();
MySQLiteHelper sql = MySQLiteHelper.Instance;
// 连接数据库
sql.GetConnectionWith(CommonData.dataBaseName);
string[] ungraspedCondition = { "learnedTimes>0" };
// 读取器
IDataReader reader = sql.ReadSpecificRowsOfTable(tableName, null, ungraspedCondition, true);
// 从表中读取数据
while (reader.Read())
{
if (reader == null)
{
sql.CloseAllConnections();
return(null);
}
HLHWord word = HLHWord.GetWordFromReader(reader);
learnedWords.Add(word);
}
sql.CloseAllConnections();
return(learnedWords);
}
/// <summary>
/// 获取所有不熟悉的单词
/// </summary>
/// <returns>The all unfamiliar word.</returns>
public List <HLHWord> GetAllUnfamiliarWord()
{
List <HLHWord> unfamiliarWords = new List <HLHWord>();
string tableName = GetCurrentLearningWordsTabelName();
MySQLiteHelper sql = MySQLiteHelper.Instance;
sql.GetConnectionWith(CommonData.dataBaseName);
string[] graspedCondition = { "learnedTimes>0", "isFamiliar=0" };
IDataReader reader = sql.ReadSpecificRowsOfTable(tableName, null, graspedCondition, true);
while (reader.Read())
{
if (reader == null)
{
sql.CloseConnection(CommonData.dataBaseName);
return(null);
}
HLHWord word = HLHWord.GetWordFromReader(reader);
unfamiliarWords.Add(word);
}
sql.CloseAllConnections();
return(unfamiliarWords);
}
private HLHWord GetAValidWord()
{
MySQLiteHelper mySql = MySQLiteHelper.Instance;
mySql.GetConnectionWith(CommonData.dataBaseName);
string currentWordsTableName = LearningInfo.Instance.GetCurrentLearningWordsTabelName();
string[] condition = new string[] { "wordLength <= 7 ORDER BY RANDOM() LIMIT 1" };
IDataReader reader = mySql.ReadSpecificRowsOfTable(currentWordsTableName, null, condition, true);
reader.Read();
return(HLHWord.GetWordFromReader(reader));
}
private HLHWord GetWordOfLength(int length)
{
string currentTableName = LearningInfo.Instance.GetCurrentLearningWordsTabelName();
string query = string.Format("SELECT * FROM {0} WHERE wordLength={1} ORDER BY RANDOM() LIMIT 1", currentTableName, length);
MySQLiteHelper sql = MySQLiteHelper.Instance;
sql.GetConnectionWith(CommonData.dataBaseName);
IDataReader reader = sql.ExecuteQuery(query);
reader.Read();
HLHWord word = HLHWord.GetWordFromReader(reader);
GameManager.Instance.pronounceManager.DownloadPronounceCache(word);
//sql.CloseConnection(CommonData.dataBaseName);
return(word);
}
/// <summary>
/// 初始化指定数量的未学习单词
/// 使用该方法需注意
/// </summary>
/// <returns>The learn words in map.</returns>
public HLHWord[] GetUnlearnedWordsOfCount(int count)
{
MySQLiteHelper mySql = MySQLiteHelper.Instance;
mySql.GetConnectionWith(CommonData.dataBaseName);
HLHWord[] words = new HLHWord[count];
string currentWordsTableName = LearningInfo.Instance.GetCurrentLearningWordsTabelName();
string query = string.Format("SELECT learnedTimes FROM {0} ORDER BY learnedTimes ASC", currentWordsTableName);
IDataReader reader = mySql.ExecuteQuery(query);
reader.Read();
int wholeLearnTime = reader.GetInt16(0);
query = string.Format("SELECT COUNT(*) FROM {0} WHERE learnedTimes=ungraspTimes AND learnedTimes>0", currentWordsTableName);
reader = mySql.ExecuteQuery(query);
reader.Read();
int wrongWordCount = reader.GetInt32(0);
if (wrongWordCount >= count)
{
query = string.Format("SELECT * FROM {0} WHERE learnedTimes=ungraspTimes AND learnedTimes>0 LIMIT {1}", currentWordsTableName, count);
int index = 0;
reader = mySql.ExecuteQuery(query);
for (int i = 0; i < count; i++)
{
reader.Read();
HLHWord word = HLHWord.GetWordFromReader(reader);
words[index] = word;
index++;
}
}
else
{
query = string.Format("SELECT COUNT(*) FROM {0} WHERE learnedTimes={1}", currentWordsTableName, wholeLearnTime);
reader = mySql.ExecuteQuery(query);
reader.Read();
int validWordCount = reader.GetInt32(0);
if (validWordCount < count - wrongWordCount)
{
string[] colFields = { "learnedTimes" };
string[] values = { (wholeLearnTime + 1).ToString() };
string[] conditions = { "learnedTimes=" + wholeLearnTime.ToString() };
mySql.UpdateValues(currentWordsTableName, colFields, values, conditions, true);
wholeLearnTime++;
}
int index = 0;
//Debug.Log(wrongWordCount);
query = string.Format("SELECT * FROM {0} WHERE learnedTimes=ungraspTimes AND learnedTimes>0 LIMIT {1}", currentWordsTableName, wrongWordCount);
reader = mySql.ExecuteQuery(query);
for (int i = 0; i < wrongWordCount; i++)
{
reader.Read();
HLHWord word = HLHWord.GetWordFromReader(reader);
words[index] = word;
index++;
}
// 边界条件
string[] condition = { string.Format("learnedTimes={0} ORDER BY RANDOM() LIMIT {1}", wholeLearnTime, count - wrongWordCount) };
reader = mySql.ReadSpecificRowsOfTable(currentWordsTableName, null, condition, true);
while (reader.Read())
{
HLHWord word = HLHWord.GetWordFromReader(reader);
words[index] = word;
index++;
}
}
mySql.CloseConnection(CommonData.dataBaseName);
return(words);
}