/// <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);
}
public static GeneralWord RandomGeneralWord()
{
string tableName = "AllWordsData";
MySQLiteHelper sql = MySQLiteHelper.Instance;
// 连接数据库
sql.GetConnectionWith(CommonData.dataBaseName);
int wordsCount = sql.GetItemCountOfTable(tableName, null, true);
int wordId = Random.Range(0, wordsCount);
string[] conditions = new string[] { string.Format("wordId={0}", wordId) };
IDataReader reader = sql.ReadSpecificRowsOfTable(tableName, null, conditions, true);
reader.Read();
string spell = reader.GetString(1);
string explaination = reader.GetString(2);
// bool valid = reader.GetBoolean (4);
return(new GeneralWord(wordId, spell, explaination));
}
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);
}
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));
}
public List <LearnWord> GetAllUngraspedWords()
{
List <LearnWord> ungraspWords = new List <LearnWord>();
string tableName = GetCurrentLearningWordsTabelName();
MySQLiteHelper sql = MySQLiteHelper.Instance;
// 连接数据库
sql.GetConnectionWith(CommonData.dataBaseName);
string[] ungraspedCondition = new string[] { "ungraspTimes>0" };
// 读取器
IDataReader reader = sql.ReadSpecificRowsOfTable(tableName, null, ungraspedCondition, true);
// 从表中读取数据
while (reader.Read())
{
if (reader == null)
{
return(null);
}
int wordId = reader.GetInt32(0);
string spell = reader.GetString(1);
string explaination = reader.GetString(2);
string phoneticSymble = reader.GetString(3);
string example = reader.GetString(4);
int learnedTimes = reader.GetInt16(5);
int ungraspTimes = reader.GetInt16(6);
LearnWord w = new LearnWord(wordId, spell, explaination, phoneticSymble, example, learnedTimes, ungraspTimes);
ungraspWords.Add(w);
}
sql.CloseAllConnections();
return(ungraspWords);
}
// 从指定文件（txt／csv等文本文件）中读取数据 csv为从excel中导出的文本文件，导入unity之后需要选择结尾格式（mono里是这样的，在mono中打开csv文件后会有提示），否则在读取数据库时会报字段名不同的错误
// private static void LoadWordsData(string dataPaths){
//
// string itemsString = DataHandler.LoadDataString (dataPaths);
//
// string[] stringsByLine = itemsString.Split (new string[]{ "\n" }, System.StringSplitOptions.RemoveEmptyEntries);
//
// fieldNames = stringsByLine [0].Split (new char[]{ ',' });
//
// for (int i = 1; i < stringsByLine.Length; i++) {
// itemsProperties.Add(stringsByLine [i].Split (new char[]{ ',' }));
// }
//
// }
// private void ToLower(){
// MySQLiteHelper sql = MySQLiteHelper.Instance;
// sql.GetConnectionWith (CommonData.dataBaseName);
//
// string tableName = "AllWordsTable";
//
// int wordsCount = sql.GetItemCountOfTable (tableName,null,true);
//
// for (int i = 0; i < 37336; i++) {
//
// IDataReader reader = sql.ReadSpecificRowsOfTable (
// "AllWordsData",
// "Spell",
// new string[]{ string.Format ("Id={0}", i) },
// true);
// reader.Read ();
//
// string spell = reader.GetString (0);
//
// string lowerSpell = spell.ToLower ();
//
// if (lowerSpell == spell) {
// continue;
// }
//
// lowerSpell = lowerSpell.Replace("'","''");
//
// sql.UpdateValues ("AllWordsData",
// new string[]{ "Spell" },
// new string[]{ string.Format("'{0}'",lowerSpell) },
// new string[]{string.Format("Id = {0}",i)},
// true);
//
// reader.Close ();
//
// }
//
//
//
// sql.CloseConnection (CommonData.dataBaseName);
// }
private void MoveData()
{
MySQLiteHelper sql = MySQLiteHelper.Instance;
sql.GetConnectionWith(CommonData.dataBaseName);
sql.CreateTable("AllWordsData",
new string[] { "wordId", "Spell", "Explaination", "Valid" },
new string[] { "PRIMARY KEY NOT NULL", "UNIQUE NOT NULL", "NOT NULL", "NOT NULL", "NOT NULL" },
new string[] { "INTEGER", "TEXT", "TEXT", "INTEGER DEFAULT 1" });
sql.DeleteAllDataFromTable("AllWordsData");
IDataReader reader = null;
int pad = 0;
for (int i = 0; i < 39286; i++)
{
if (i == 34250)
{
pad++;
continue;
}
reader = sql.ReadSpecificRowsOfTable("AllWords", "*",
new string[] { string.Format("ID={0}", i) },
true);
reader.Read();
int id = i - pad;
string spell = reader.GetString(1);
string explaination = reader.GetString(2);
int type = 0;
int valid = 1;
if (spell == string.Empty || explaination == string.Empty || spell == null || explaination == null)
{
pad++;
continue;
}
spell = spell.Replace("'", "''");
explaination = explaination.Replace("'", "''");
sql.InsertValues("AllWordsData",
new string[] { id.ToString(),
"'" + spell + "'",
"'" + explaination + "'",
type.ToString(),
valid.ToString() });
reader.Close();
}
Debug.Log("Finished");
sql.CloseConnection(CommonData.dataBaseName);
}
public static LearnWord RandomWord()
{
LearningInfo learnInfo = LearningInfo.Instance;
int wordId = 0;
if (learnInfo.learnedWordCount != 0)
{
wordId = Random.Range(0, learnInfo.learnedWordCount);
List <LearnWord> learnedWords = learnInfo.GetAllLearnedWords();
return(learnedWords [wordId]);
}
string tableName = string.Empty;
WordType wt = GameManager.Instance.gameDataCenter.gameSettings.wordType;
switch (wt)
{
case WordType.CET4:
tableName = CommonData.CET4Table;
break;
case WordType.CET6:
tableName = "CET6";
break;
case WordType.Daily:
tableName = "Daily";
break;
case WordType.Bussiness:
tableName = "Bussiness";
break;
}
MySQLiteHelper sql = MySQLiteHelper.Instance;
// 连接数据库
sql.GetConnectionWith(CommonData.dataBaseName);
int wordsCount = sql.GetItemCountOfTable(tableName, null, true);
wordId = Random.Range(0, wordsCount);
string[] conditions = new string[] { string.Format("wordId={0}", wordId) };
IDataReader reader = sql.ReadSpecificRowsOfTable(tableName, null, conditions, true);
reader.Read();
string spell = reader.GetString(1);
string explaination = reader.GetString(2);
string phoneticSymbol = reader.GetString(3);
string example = reader.GetString(4);
int learnedTimes = reader.GetInt16(5);
int ungraspTimes = reader.GetInt16(6);
return(new LearnWord(wordId, spell, explaination, phoneticSymbol, example, learnedTimes, ungraspTimes));
}
/// <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;
}