public static void AddMeanings(KanjiListModel kl)
{
//Set up Sqlite
SQLiteConnection sql = new SQLiteConnection("Data Source=Kanji.db");
SQLiteCommand cmd;
sql.Open();
for (int i = 0; i < kl.Kanji.Count; i++)
{
cmd = new SQLiteCommand("SELECT rowid FROM Kanji WHERE character = '" + kl.Kanji[i].Character + "';", sql);
int rowid = Int32.Parse(cmd.ExecuteScalar().ToString());
for (int j = 0; j < kl.Kanji[i].Meanings.Count; j++)
{
cmd = new SQLiteCommand("INSERT INTO KanjiMeaning (meaning,kanjiId)" +
"VALUES(" +
"@Meaning," +
"@Rowid" +
")", sql);
cmd.Parameters.AddWithValue("@Meaning", kl.Kanji[i].Meanings[j]);
cmd.Parameters.AddWithValue("@Rowid", rowid);
cmd.ExecuteNonQuery();
}
}
sql.Close();
}
public static KanjiModel SelectTargetKanji(KanjiListModel shortList)
{
Random r = new Random();
KanjiModel target = shortList.Kanji[r.Next(shortList.Kanji.Count)];
return(target);
}
public static void AddKanji(KanjiListModel list)
{
//Set up Sqlite
SQLiteConnection sql = new SQLiteConnection("Data Source=Kanji.db");
SQLiteCommand cmd;
sql.Open();
for (int i = 0; i < list.Kanji.Count; i++)
{
//Insert new kanji
cmd = new SQLiteCommand(@"INSERT INTO Kanji (character,grade,jlpt,strokeCount,unicode,heisigEnglish)" +
"VALUES(" +
"@Character," +
"@Grade," +
"@JLPT," +
"@StrokeCount," +
"@Unicode," +
"@Heisig" +
")", sql);
cmd.Parameters.AddWithValue("@Character", list.Kanji[i].Character);
cmd.Parameters.AddWithValue("@Grade", list.Kanji[i].Grade);
cmd.Parameters.AddWithValue("@JLPT", list.Kanji[i].JLPT);
cmd.Parameters.AddWithValue("@StrokeCount", list.Kanji[i].StrokeCount);
cmd.Parameters.AddWithValue("@Unicode", list.Kanji[i].Unicode);
cmd.Parameters.AddWithValue("@Heisig", list.Kanji[i].HeisigEnglish);
cmd.ExecuteNonQuery();
}
sql.Close();
}
public static void UpdateKanjiListModelFile(KanjiListModel list, string category)
{
XmlSerializer serializer = new XmlSerializer(typeof(KanjiListModel));
var file = System.IO.File.Create("XML/" + category + ".xml");
serializer.Serialize(file, list);
file.Close();
}
public static KanjiListModel GetKanjiListModelFromXML(string category)
{
XmlReader x = XmlReader.Create("XML/" + category + ".xml");
XmlSerializer serializer = new XmlSerializer(typeof(KanjiListModel));
string text = File.ReadAllText("XML/" + category + ".xml");
KanjiListModel list = (KanjiListModel)serializer.Deserialize(x);
x.Close();
return(list);
}
public static KanjiListModel CreateKanjiShortList(KanjiListModel fullList, int numberSelected)
{
KanjiListModel shortList = new KanjiListModel();
int max = fullList.Kanji.Count;
Random r = new Random();
for (int i = 0; i < numberSelected; i++)
{
shortList.Kanji.Add(fullList.Kanji[r.Next(0, max)]);
}
return(shortList);
}
public static KanjiListModel LoadKanjiFromSet(string name)
{
KanjiListModel kl = new KanjiListModel();
SQLiteConnection connection = new SQLiteConnection("Data Source=Kanji.db", true);
SQLiteCommand command;
connection.Open();
command = new SQLiteCommand(
"SELECT * " +
"FROM Kanji " +
"WHERE rowid in " +
"( SELECT kanjiId FROM KanjiInSet WHERE kanjiSetId in" +
"( SELECT rowid FROM KanjiSet WHERE kanjiSetName = @KanjiSetName))", connection);
command.Parameters.AddWithValue("@KanjiSetName", name);
var r = command.ExecuteReader();
while (r.Read())
{
string c = r.GetString(0);
int g = r.GetInt32(1);
int s = r.GetInt32(2);
int j = r.GetInt32(3);
string u = r.GetString(4);
string h = r.GetString(5);
kl.Kanji.Add(new KanjiModel(c, g, s, j, u, h));
}
for (int i = 0; i < kl.Kanji.Count; i++)
{
command = new SQLiteCommand(
"SELECT meaning " +
"FROM KanjiMeaning " +
"WHERE kanjiId = " +
"(" +
"SELECT rowid " +
"FROM Kanji " +
"WHERE character = @Character" +
")", connection);
command.Parameters.AddWithValue("@Character", kl.Kanji[i].Character);
r = command.ExecuteReader();
while (r.Read())
{
kl.Kanji[i].Meanings.Add(r.GetString(0));
}
}
connection.Close();
return(kl);
}
public static void AddKanjiToGradeSet(KanjiListModel list, int grade)
{
SQLiteConnection connection = new SQLiteConnection("Data Source=Kanji.db");
SQLiteCommand command;
connection.Open();
string category = "grade-" + grade;
command = new SQLiteCommand("SELECT rowid FROM KanjiSet WHERE kanjiSetName=@Grade", connection);
command.Parameters.AddWithValue("@Grade", category);
int setRowid = Int32.Parse(command.ExecuteScalar().ToString());
command = new SQLiteCommand("INSERT INTO KanjiInSet(kanjiSetId,kanjiId) " +
"SELECT @SetRowId,rowid " +
"FROM Kanji " +
"WHERE grade = @SetRowId", connection);
command.Parameters.AddWithValue("@SetRowId", setRowid);
command.ExecuteNonQuery();
connection.Close();
}
public static void BuildDatabase()
{
//Create Database File
FileUtility.CreateDataFile("Kanji");
//Create Tables
DatabaseBuilder.AddTables("Kanji");
for (int i = 1; i < 7; i++)
{
KanjiListModel list = FileUtility.GetKanjiListModelFromXML("grade-" + i);
//Set up grade 1 set
DatabaseBuilder.CreateSet("grade-" + i, "Kanji");
//Add those kanji to the database
DatabaseBuilder.AddKanji(list);
//Add the meanings to the database
DatabaseBuilder.AddMeanings(list);
//Add the readings to the database
DatabaseBuilder.AddReadings(list);
//Add those Kanji to the correct grade set
DatabaseBuilder.AddKanjiToGradeSet(list, i);
}
}
public static void DisperseKanji(ObservableCollection <ObservableCollection <string> > cellValues, KanjiListModel shortList)
{
Random r = new Random();
int xLimit = cellValues.Count;
int yLimit = cellValues[0].Count;
int x = r.Next(0, xLimit);
int y = r.Next(0, yLimit);
for (int i = 0; i < shortList.Kanji.Count; i++)
{
while ((cellValues[x][y] != String.Empty))
{
x = r.Next(0, xLimit);
y = r.Next(0, yLimit);
}
cellValues[x][y] = shortList.Kanji[i].Character;
}
}
