/// <summary>
/// Save a new Vocab into db.
/// </summary>
/// <param name="NewVocab"></param>
/// <param name="groupid"></param>
/// <param name="picture_path"></param>
/// <param name="sound_path"></param>
public void SaveNewVocab(Vocab NewVocab, int groupid, string picture_path, string sound_path)
{
using (SQLiteCommand command = new SQLiteCommand(connection))
{
command.CommandText = "INSERT INTO vocabulary (group_id, english, german, english_accept, german_accept, example, picture_path, sound_path) " +
"VALUES (@group_id, @english, @german, @english_accept, @german_accept, @example, @picture_path, @sound_path)";
command.Prepare();
command.Parameters.AddWithValue("group_id", groupid);
command.Parameters.AddWithValue("english", NewVocab.english);
command.Parameters.AddWithValue("german", NewVocab.german);
command.Parameters.AddWithValue("english_accept", NewVocab.english_accept);
command.Parameters.AddWithValue("german_accept", NewVocab.german_accept);
command.Parameters.AddWithValue("example", NewVocab.example);
command.Parameters.AddWithValue("picture_path", picture_path);
command.Parameters.AddWithValue("sound_path", sound_path);
try
{
command.ExecuteNonQuery();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
}
/// <summary>
/// read knwon vocabs from db into list by groupid and userId
/// </summary>
/// <param name="groupId"></param>
/// <param name="userId"></param>
/// <returns></returns>
public List <Vocab> GetVocabs(int groupId, int userId)
{
var vocabs = new List <Vocab>();
using (SQLiteCommand command = new SQLiteCommand(connection))
{
command.CommandText = "SELECT id, english, german, picture_path, sound_path FROM vocabulary " +
"WHERE group_id=@group_id";
command.Parameters.AddWithValue("group_id", groupId);
try
{
using (SQLiteDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
var vocab = new Vocab();
vocab.id = reader.GetInt32(0);
vocab.english = reader.GetString(1);
vocab.german = reader.GetString(2);
vocab.picturepath = reader.GetValue(3).ToString();
vocab.soundpath = reader.GetValue(4).ToString();
vocab.level = GetVocabLevel(vocab.id, userId);
vocabs.Add(vocab);
}
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
return(vocabs);
}
/// <summary>
/// /// if vocab index changed reinit affected data (vocab,pic,sound,btns)
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void VocabSelectBox_SelectedIndexChanged(object sender, EventArgs e)
{
if (VocabSelectBox.Text == "Add...")
{
QuestionTextBox.Clear();
AnswerTextBox.Clear();
PicBox.Image = null;
AddChangePictureBtn.Enabled = true;
AddChangeSoundBtn.Enabled = true;
DeleteVocabBtn.Enabled = false;
NextSaveButton.Text = "Save";
}
else
{
Vocab = (db.GetVocabs((GroupSelectBox.SelectedIndex + 1),
db.getuserid("admin")))[VocabSelectBox.SelectedIndex]; //load vocab
QuestionTextBox.Text = Vocab.german;
AnswerTextBox.Text = Vocab.english;
if (Vocab.picturepath != "") //load picture
{
PicBox.Image = Image.FromFile(Vocab.picturepath); //picbox load image
PicBox.SizeMode = PictureBoxSizeMode.Zoom; //autosize
AddChangePictureBtn.Text = "Change";
DeletePictureBtn.Enabled = true;
}
else
{
PicBox.Image = null;
AddChangePictureBtn.Text = "Add";
DeletePictureBtn.Enabled = false;
}
if (Vocab.soundpath != "") //load sound
{
WMPLib.IWMPMedia media = WMPbox.newMedia(Vocab.soundpath);
WMPbox.currentPlaylist.appendItem(media);
AddChangeSoundBtn.Text = "Change";
DeleteSoundBtn.Enabled = true;
}
else
{
WMPbox.close(); //clear soundbox
AddChangeSoundBtn.Text = "Add";
DeleteSoundBtn.Enabled = false; //set btns
}
NextSaveButton.Text = "Next";
AddChangePictureBtn.Enabled = true;
AddChangeSoundBtn.Enabled = true;
DeleteVocabBtn.Enabled = true;
}
if (VocabSelectBox.SelectedIndex == 0) //set to previous btn
{
PrevBtn.Enabled = false;
}
else
{
PrevBtn.Enabled = true;
}
toolStripStatusLabel1.Text = ""; //refresh tooltip
}
/// <summary>
/// /// if unit index changed reinit affected data (vocab)
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void GroupSelectBox_SelectedIndexChanged(object sender, EventArgs e)
{
VocabSelectBox.Items.Clear(); //relaod Vocab, clear
foreach (Vocab German in db.GetVocabs((GroupSelectBox.SelectedIndex + 1), db.getuserid("admin")))
{
VocabSelectBox.Items.Add(German.german + "...");
}
VocabSelectBox.SelectedIndex = 0; //set index
VocabSelectBox.Items.Add("Add..."); //add last entry
Vocab = (db.GetVocabs((GroupSelectBox.SelectedIndex + 1), db.getuserid("admin")))[VocabSelectBox.SelectedIndex]; //Textbox
QuestionTextBox.Text = Vocab.german;
AnswerTextBox.Text = Vocab.english;
}
/// <summary>
/// Get next vocab or save new generated vocab to db (toogle "next"/"save" btn)
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void NextSaveButton_Click(object sender, EventArgs e)
{
if (NextSaveButton.Text == "Save")
{
//get filepaths if allrdy set
string picturepath = Vocab.picturepath;
string soundpath = Vocab.soundpath;
Vocab = new Vocab(); //generate new Vocab
Vocab.english = AnswerTextBox.Text; //read text box, set Vocab propertys
Vocab.german = QuestionTextBox.Text;
db.SaveNewVocab(Vocab, GroupSelectBox.SelectedIndex + 1, picturepath, soundpath); //save to db
toolStripStatusLabel1.Text = "Q: " + QuestionTextBox.Text + "A: " + AnswerTextBox.Text + "saved"; //settooltip
VocabSelectBox.Items.Clear(); //clear vocablist
foreach (Vocab German in db.GetVocabs((GroupSelectBox.SelectedIndex + 1), db.getuserid("admin"))) //reload vocablist
{
VocabSelectBox.Items.Add(German.german);
}
VocabSelectBox.Items.Add("Add..."); //add last entry
VocabSelectBox.SelectedIndex = VocabSelectBox.Items.Count - 2; //set index to last vocab
NextSaveButton.Text = "Next";
AddChangePictureBtn.Enabled = true;
AddChangeSoundBtn.Enabled = true;
}
else
{
if (VocabSelectBox.SelectedIndex + 1 < VocabSelectBox.Items.Count) //check if we have a next vocab
{
Vocab = (db.GetVocabs((GroupSelectBox.SelectedIndex + 1),
db.getuserid("admin")))[VocabSelectBox.SelectedIndex]; //load next vocab in Textbox
VocabSelectBox.SelectedIndex++; //set VocabSelectBox
}
else //no next vocab
{
//clear text box
QuestionTextBox.Clear();
AnswerTextBox.Clear();
//clear picturebox
PicBox.Image = null;
//set btns
DeletePictureBtn.Enabled = false;
AddChangePictureBtn.Text = "Add";
NextSaveButton.Text = "Save";
}
}
}
/// <summary>
/// delete a known vocab
/// </summary>
/// <param name="vocab"></param>
public void DeletePicture(Vocab vocab)
{
using (SQLiteCommand command = new SQLiteCommand(connection))
{
command.CommandText = "UPDATE vocabulary SET picture_path = @picture_path WHERE id = @id";
command.Prepare();
command.Parameters.AddWithValue("picture_path", null);
command.Parameters.AddWithValue("id", vocab.id);
try
{
command.ExecuteNonQuery();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
}
/// <summary>
/// if unit index changed reinit affected data (vocab)
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void UnitComBox_SelectedIndexChanged(object sender, EventArgs e)
{
//Groups
GroupSelectBox.Items.Clear(); //clear
foreach (string group in db.getgroups(UnitSelectBox.SelectedIndex + 1))
{
GroupSelectBox.Items.Add(group); //add from db
}
GroupSelectBox.SelectedIndex = 0; // first entry
//Vocab
VocabSelectBox.Items.Clear(); //clear
foreach (Vocab German in db.GetVocabs((GroupSelectBox.SelectedIndex + 1), db.getuserid("admin")))
{
VocabSelectBox.Items.Add(German.german + "...");
}
//set index
VocabSelectBox.SelectedIndex = 0;
//add last entry
VocabSelectBox.Items.Add("Add...");
//Textbox
Vocab = (db.GetVocabs((GroupSelectBox.SelectedIndex + 1), db.getuserid("admin")))[VocabSelectBox.SelectedIndex];
QuestionTextBox.Text = Vocab.german;
AnswerTextBox.Text = Vocab.english;
}
/// <summary>
/// fill window with data from db
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void TeacherForm_Load(object sender, EventArgs e)
{
Utility = new Util(); //io helper, filecopy function from Util.cs --> this maybe go online on server fileupload, for sync
Vocab = new Vocab(); //Vocab obj
try
{
db = new Database(); //database init
}
catch
{
MessageBox.Show("Unable to load database.",
"Vocabulator", MessageBoxButtons.OK, MessageBoxIcon.Error);
Application.Exit();
}
UnitSelectBox.Items.Clear(); //setup design specific, Lessons
foreach (string unit in db.getunits())
{
UnitSelectBox.Items.Add(unit); //add from db
}
UnitSelectBox.Items.Add("Add..."); //add last entry
UnitSelectBox.SelectedIndex = 0; // set first entry
GroupSelectBox.Items.Clear(); //Groups
foreach (string group in db.getgroups(UnitSelectBox.SelectedIndex + 1))
{
GroupSelectBox.Items.Add(group); //add from db
}
VocabSelectBox.Items.Add("Add..."); //add last entry
GroupSelectBox.SelectedIndex = 0; // set to first entry
VocabSelectBox.Items.Clear(); //Vocab
foreach (Vocab German in db.GetVocabs((GroupSelectBox.SelectedIndex + 1), db.getuserid("admin")))
{
VocabSelectBox.Items.Add(German.german); //add from db
}
VocabSelectBox.Items.Add("Add..."); //add last entry
VocabSelectBox.SelectedIndex = 0; //set to first entry
Vocab = (db.GetVocabs((GroupSelectBox.SelectedIndex + 1), //load Vocab
db.getuserid("admin")))[VocabSelectBox.SelectedIndex];
QuestionTextBox.Text = Vocab.german; //set Vocab atttib
AnswerTextBox.Text = Vocab.english;
if (Vocab.picturepath != "") //Picturebox init
{
PicBox.Image = Image.FromFile(Vocab.picturepath); //picbox load image
PicBox.SizeMode = PictureBoxSizeMode.Zoom; //autosize
AddChangePictureBtn.Text = "Change";
DeletePictureBtn.Enabled = true;
}
else
{
PicBox.Image = null;
AddChangePictureBtn.Text = "Add";
DeletePictureBtn.Enabled = false;
}
if (Vocab.soundpath != "") //Soundbox init, load sound
{
WMPLib.IWMPMedia media = WMPbox.newMedia(Vocab.soundpath);
WMPbox.currentPlaylist.appendItem(media);
AddChangeSoundBtn.Text = "Change";
DeleteSoundBtn.Enabled = true;
}
else
{
WMPbox.close(); //clear soundbox
AddChangeSoundBtn.Text = "Add"; //set btns
DeleteSoundBtn.Enabled = false;
}
}