/// <summary>
/// 更新数据库中变更单词掌握状态的单词信息
/// </summary>
private void UpdateChangeStatusWords()
{
MySQLiteHelper sql = MySQLiteHelper.Instance;
sql.GetConnectionWith(CommonData.dataBaseName);
string currentWordsTableName = LearningInfo.Instance.GetCurrentLearningWordsTabelName();
string[] colFields = { "isFamiliar" };
HLHWord word = null;
// 更新数据库中的单词数据
// 更新所有待转移到熟悉列表的单词
for (int i = 0; i < changeToFamiliarWords.Count; i++)
{
word = changeToFamiliarWords[i];
word.learnedTimes++;
string[] conditions = { "wordId=" + word.wordId };
string[] values = { "1" };
sql.UpdateValues(currentWordsTableName, colFields, values, conditions, true);
}
// 更新所有待转移到不熟悉列表的单词
for (int i = 0; i < changeToUnfamiliarWords.Count; i++)
{
word = changeToUnfamiliarWords[i];
word.learnedTimes++;
word.ungraspTimes++;
string[] conditions = { "wordId=" + word.wordId };
string[] values = { "0" };
sql.UpdateValues(currentWordsTableName, colFields, values, conditions, true);
}
sql.CloseConnection(CommonData.dataBaseName);
}
/// <summary>
/// 更新单词数据库
/// </summary>
public void UpdateWordDataBase()
{
// 连接到数据库
MySQLiteHelper sql = MySQLiteHelper.Instance;
sql.GetConnectionWith(CommonData.dataBaseName);
// 开启事务
sql.BeginTransaction();
// 获取当前单词的表单
string currentWordsTableName = LearningInfo.Instance.GetCurrentLearningWordsTabelName();
// 更新项
string[] colFields = { "learnedTimes", "ungraspTimes", "isFamiliar" };
HLHWord word = null;
// 正确单词列表中所有单词数据更新进数据库
for (int i = 0; i < correctWordList.Count; i++)
{
word = correctWordList[i];
string familiarStr = word.isFamiliar ? "1" : "0";
string[] conditions = { "wordId=" + word.wordId };
string[] values = { word.learnedTimes.ToString(), word.ungraspTimes.ToString(), familiarStr };
sql.UpdateValues(currentWordsTableName, colFields, values, conditions, true);
}
// 错误单词列表中所有单词数据更新进数据库
for (int i = 0; i < wrongWordList.Count; i++)
{
word = wrongWordList[i];
string familiarStr = word.isFamiliar ? "1" : "0";
string[] conditions = { "wordId=" + word.wordId };
string[] values = { word.learnedTimes.ToString(), word.ungraspTimes.ToString(), familiarStr };
sql.UpdateValues(currentWordsTableName, colFields, values, conditions, true);
}
// 清理工作
correctWordList.Clear();
wrongWordList.Clear();
sql.EndTransaction();
sql.CloseConnection(CommonData.dataBaseName);
}
private void UpdateDataBase()
{
mySql.GetConnectionWith(CommonData.dataBaseName);
mySql.BeginTransaction();
for (int i = 0; i < singleLearnWordsCount; i++)
{
LearnWord word = wordsToLearnArray [i];
string condition = string.Format("wordId={0}", word.wordId);
string newLearnedTime = (word.learnedTimes).ToString();
string newUngraspTime = (word.ungraspTimes).ToString();
// 更新数据库中当前背诵单词的背诵次数和背错次数
mySql.UpdateValues(currentWordsTableName, new string[] { "learnedTimes", "ungraspTimes" }, new string[] {
newLearnedTime,
newUngraspTime
}, new string[] {
condition
}, true);
}
mySql.EndTransaction();
mySql.CloseConnection(CommonData.dataBaseName);
}
public static FuseStone CreateFuseStoneIfExist(string spell)
{
/************从单词数据库中获取物品名称**************/
MySQLiteHelper sql = MySQLiteHelper.Instance;
sql.GetConnectionWith(CommonData.dataBaseName);
IDataReader reader = sql.ReadSpecificRowsOfTable("AllWordsData", "*",
new string[] { string.Format("Spell='{0}'", spell) },
true);
if (!reader.Read())
{
Debug.Log("不存在");
return(null);
}
bool valid = reader.GetBoolean(4);
if (!valid)
{
Debug.Log("已使用");
return(null);
}
int id = reader.GetInt32(0);
string explaination = reader.GetString(2);
string firstExplaination = explaination.Split(new string[] { "；" }, System.StringSplitOptions.RemoveEmptyEntries)[0];
string[] strings = firstExplaination.Split(new string[] { ".", "，" }, System.StringSplitOptions.RemoveEmptyEntries);
string fuseStoneName = strings [strings.Length - 1];
// string fuseStoneName = explaination.Split (new string[]{ ".", "，" ,"；"},
// System.StringSplitOptions.RemoveEmptyEntries)[1];
fuseStoneName = fuseStoneName.Replace(" ", string.Empty);
fuseStoneName = fuseStoneName.Replace("的", string.Empty);
fuseStoneName = string.Format("{0}之石", fuseStoneName);
sql.UpdateValues("AllWordsData",
new string[] { "Valid" },
new string[] { "0" },
new string[] { string.Format("Id={0}", id) },
true);
sql.CloseConnection(CommonData.dataBaseName);
FuseStone fuseStone = new FuseStone(fuseStoneName, spell);
Debug.Log(fuseStone);
return(fuseStone);
}
/// <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);
}
/// <summary>
/// 初始化本次要学习的单词数组
/// </summary>
private void InitWordsToLearn()
{
ungraspedWordsList.Clear();
mySql = MySQLiteHelper.Instance;
mySql.GetConnectionWith(CommonData.dataBaseName);
// int totalLearnTimeCount = GameManager.Instance.gameDataCenter.learnInfo.totalLearnTimeCount;
//
// int totalWordsCount = mySql.GetItemCountOfTable (CommonData.CET4Table,null,true);
//
// // 大循环的次数
// int bigCycleCount = totalLearnTimeCount * singleLearnWordsCount / (totalWordsCount * recycleLearnTimeBase);
//
// currentWordsLearnedTime = totalLearnTimeCount % (recycleLearnTimeBase * recycleGroupBase) / recycleGroupBase + recycleLearnTimeBase * bigCycleCount;
mySql.BeginTransaction();
// 边界条件
string[] condition = new string[] { "learnedTimes=0" };
IDataReader reader = mySql.ReadSpecificRowsOfTable(currentWordsTableName, null, condition, true);
// 从数据库中读取当前要学习的单词
for (int i = 0; i < singleLearnWordsCount; i++)
{
reader.Read();
if (reader == null)
{
string[] colFields = new string[] { "learnedTimes" };
string[] values = new string[] { "0" };
string[] conditions = new string[] { "learnedTimes=1" };
mySql.UpdateValues(currentWordsTableName, colFields, values, conditions, true);
mySql.EndTransaction();
mySql.CloseAllConnections();
InitWordsToLearn();
return;
}
int wordId = reader.GetInt32(0);
string spell = reader.GetString(1);
string phoneticSymble = reader.GetString(2);
string explaination = reader.GetString(3);
string example = reader.GetString(4);
int learnedTimes = reader.GetInt16(5);
int ungraspTimes = reader.GetInt16(6);
LearnWord word = new LearnWord(wordId, spell, phoneticSymble, explaination, example, learnedTimes, ungraspTimes);
Debug.LogFormat("{0}---{1}次", word, learnedTimes);
wordsToLearnArray [i] = word;
}
mySql.EndTransaction();
mySql.CloseAllConnections();
// 当前要学习的单词全部加入到未掌握单词列表中，用户选择掌握或者学习过该单词后从未掌握单词列表中移除
for (int i = 0; i < wordsToLearnArray.Length; i++)
{
ungraspedWordsList.Add(wordsToLearnArray [i]);
}
// firstIdOfCurrentLearningWords = wordsToLearnArray [0].wordId;
}