public Word[] GetAllReportedWords(int limit, string like, string FROMTO, int version)
{
string where = "";
if (version == 1) where = " version = 1 AND ";
else if (version == 2) where = " version = 2 AND ";
string query = "SELECT ";
if(limit > 0) query += " TOP " + limit.ToString() ;
query += " words.ID, words.word, words.meaning, words.sentences, words.pron, report.correct, report.incorrect, report.author_id, words.version " +
" FROM report, words " +
" WHERE " + where + " words.word = report.word AND (" + like + ") ";
if (FROMTO != "") query += " AND " + FROMTO + " ";
query += " ORDER BY (correct / (correct + incorrect + 1)), incorrect DESC, correct + incorrect ";
DataSet ds = ExecuteQuery(query);
if (ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0) return null;
Word[] w = new Word[ds.Tables[0].Rows.Count];
int i = 0;
if (limit < 0) limit = ds.Tables[0].Rows.Count;
foreach (DataRow dr in ds.Tables[0].Rows)
{
if (i == limit) break;
w[i] = new Word();
w[i] = GetWordFromDataRow(dr);
w[i].correct = Convert.ToInt32(dr["correct"].ToString());
w[i].incorrect = Convert.ToInt32(dr["incorrect"].ToString());
++i;
}
return w;
}
public void AddWord(Word w)
{
string query = "SELECT MAX(id) FROM words";
DataSet ds = ExecuteQuery(query);
int id = Convert.ToInt32(ds.Tables[0].Rows[0][0].ToString()) + 1;
w.id = id;
InsertWord(w);
}
public frmToLearn(Word[] w)
{
InitializeComponent();
//txtDump.Text = msg;
this.words = w;
}
public Word[] GetAllWords(int limit, string where, string FROMTO )
{
string query = "SELECT " ;
if(limit > 0) query += " TOP " + limit.ToString();
query += " id, word, meaning, pron FROM words " + where ;
if (FROMTO != "") query += " AND " + FROMTO + " ";
query += " ORDER BY word asc";
DataSet ds = ExecuteQuery(query);
if (ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0) return null;
Word[] words = new Word[ds.Tables[0].Rows.Count];
if (limit < 0) limit = ds.Tables[0].Rows.Count;
for(int i = 0; i < ds.Tables[0].Rows.Count && i < limit; ++i)
{
Word toReturn = new Word();
DataRow dr = ds.Tables[0].Rows[i];
toReturn.id = Convert.ToInt32(dr["id"].ToString());
toReturn.word = dr["word"].ToString();
toReturn.meaning = dr["meaning"].ToString();
//toReturn.sentence = ds["sentences"].ToString();
toReturn.pron = dr["pron"].ToString();
revert(toReturn.meaning);
//revert(toReturn.sentence);
words[i] = toReturn;
//GetWordFromDataRow(ds.Tables[0].Rows[i]);
}
return words;
}
public void UpdateWord(Word w, string key)
{
w.meaning = w.meaning.Replace("'", "''");
w.sentence = w.sentence.Replace("'", "''");
//string query = string.Format("SELECT id FROM words WHERE word = '{0}'", w.word);
//string id = ExecuteQuery(query).Tables[0].Rows[0][0].ToString();
string query = string.Format("UPDATE words " +
"SET meaning = '{0}', sentences = '{1}', pron = '{2}', word = '{3}' " +
"WHERE word = '{4}'", w.meaning, w.sentence, w.pron, w.word, key);
ExecuteQuery(query);
query = "UPDATE report SET word = '" + w.word + "' WHERE word = '" + key + "'";
}
public void InsertWord(Word w)
{
//adjust(w.sentence);
//adjust(w.meaning);
if (ifWordExists(w.word)) return;
w.meaning = w.meaning.Replace("'", "''");
w.sentence = w.sentence.Replace("'", "''");
string query = string.Format("INSERT INTO words VALUES ({0}, '{1}', '{2}', '{3}', '{4}', 0)",
w.id, w.word, w.meaning, w.sentence, w.pron);
ExecuteQuery(query);
if (ifWordReportExists(w.word)) return;
query = string.Format("INSERT INTO report VALUES ('{0}', 0, 0, 0)", w.word);
ExecuteQuery(query);
}
public Word GetWordFromDataRow(DataRow ds)
{
Word toReturn = new Word();
toReturn.id = Convert.ToInt32(ds["id"].ToString());
toReturn.word = ds["word"].ToString();
toReturn.meaning = ds["meaning"].ToString();
toReturn.sentence = ds["sentences"].ToString();
toReturn.pron = ds["pron"].ToString();
revert(toReturn.meaning);
revert(toReturn.sentence);
return toReturn;
}
private void frmHint_Load(object sender, EventArgs e)
{
thisword = c.GetWord(word);
}