private void button1_Click(object sender, EventArgs e)
{
//ask if he is sure about saving the test and then save in database to be used by all users.
if (studenti_lst.Items.Count > 0)
{
string sql = string.Empty;
sql = string.Format("SELECT Nume_Materie FROM TabelMaterii WHERE ID_Profesori LIKE '%{0}%';", Main_form.id_user);
string numeMaterie = MyData.stringSelector(sql, "Nume_Materie");
if (studenti_lst.SelectedIndex == 0)
{
//adaugam pentru toti elevi in baza de date: loop
for (int i = 1; i < studenti_lst.Items.Count; i++)
{
string[] split_name = studenti_lst.Items[i].ToString().Split(' ');
MessageBox.Show(split_name[0]);
sql = string.Format("SELECT ID_Student FROM Grades WHERE Nume='{1}' AND Prenume='{2}' AND Id_Profesor LIKE '%{0}%';", Main_form.id_user, split_name[0], split_name[1]);//get id
int id = MyData.selectData(sql, "ID_Student");
// adaug testul
sql = string.Format("INSERT INTO Teste_Materii(ID_Student,Materie,ID_Prof,Nume_fisier)VALUES({0},'{1}',{2},'{3}');", id, numeMaterie, Main_form.id_user, nume_fisier.Text);
MyData.execSql(sql);
sql = string.Format("SELECT IDTesteAdaugate FROM Materii WHERE IDProfesor={0};", Main_form.id_user);
string teste = MyData.stringSelector(sql, "IDTesteAdaugate");
sql = string.Format("SELECT ID_Test FROM Teste_Materii WHERE ID_Student={0} AND Materie='{1}' AND ID_Prof={2};", id, numeMaterie, Main_form.id_user);
int ids = MyData.selectData(sql, "ID_Test");
teste = teste.Length > 0 ? string.Format($"{teste},{ids}") : string.Format($"{ids}");
sql = string.Format("UPDATE Materii SET IDTesteAdaugate='{0}' WHERE IDProfesor={1};", teste, Main_form.id_user);
MyData.execSql(sql);
}
}
else
{
sql = string.Format("INSERT INTO Teste_Materii(ID_Student,Materie,ID_Prof,Nume_fisier)VALUES({0},'{1}',{2},'{3}');", getStudentID(), numeMaterie, Main_form.id_user, nume_fisier.Text);
MyData.execSql(sql);
sql = string.Format("SELECT IDTesteAdaugate FROM Materii WHERE IDProfesor={0};", Main_form.id_user);
string teste = MyData.stringSelector(sql, "IDTesteAdaugate");
sql = string.Format("SELECT ID_Test FROM Teste_Materii WHERE ID_Student={0} AND Materie='{1}' AND ID_Prof={2};", getStudentID(), numeMaterie, Main_form.id_user);
int id = MyData.selectData(sql, "ID_Test");
teste = teste.Length > 0 ? string.Format($"{teste},{id}") : string.Format($"{id}");
sql = string.Format("UPDATE Materii SET IDTesteAdaugate='{0}' WHERE IDProfesor={1};", teste, Main_form.id_user);
MyData.execSql(sql);
}
InitCreate();
MessageBox.Show("Test adaugat cu succes !", "Info", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
public void AddTesteInBox(ComboBox box)
{
box.Items.Clear();
string sql_materii = string.Format("SELECT Materii_inscris FROM Grades WHERE ID_Student={0};", Main_form.id_user);
string materii = MyData.stringSelector(sql_materii, "Materii_inscris");
if (!string.IsNullOrEmpty(materii))
{
string[] split_materii = materii.Split(',');
for (int i = 0; i < split_materii.Length; i++)
{
sql_materii = string.Format($"SELECT Nume_Materie FROM TabelMaterii WHERE ID_Materie={split_materii[i]};");
string materie = MyData.stringSelector(sql_materii, "Nume_Materie");
box.Items.Add(materie);
}
box.SelectedIndex = 0;
}
}
public string getName(int id, int type) //1 -> nume | 2 -> prenume
{
string sql = string.Empty, data = string.Empty;
switch (type)
{
case 1:
sql = string.Format("SELECT Nume FROM Materii WHERE IDProfesor={0};", Main_form.id_user);
data = MyData.stringSelector(sql, "Nume");
break;
case 2:
sql = string.Format("SELECT Prenume FROM Materii WHERE IDProfesor={0};", Main_form.id_user);
data = MyData.stringSelector(sql, "Prenume");
break;
}
return(data);
}
private void dgv_allcourse_CellClick(object sender, DataGridViewCellEventArgs e)
{
if (e.ColumnIndex >= 0)
{
if (dgv_allcourse.Columns[e.ColumnIndex] is DataGridViewButtonColumn && e.RowIndex >= 0)
{
string sql = string.Format("SELECT Materii_inscris FROM Grades WHERE ID_Student={0};", Main_form.id_user);
string data = MyData.stringSelector(sql, "Materii_inscris");
//checking all exceptions
if (data.Split(',').Length >= max_materii)
{
MessageBox.Show($"Esti deja inscris la {max_materii} materii , nu poti alege altele decat la incheierea anului.", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
//daca exista
string[] splitData = data.Split(',');
for (int i = 0; i < splitData.Length; i++)
{
if (splitData[i] == dgv_allcourse["ID_Materie", e.RowIndex].Value.ToString())
{
MessageBox.Show("Esti inscris deja la aceasta materie , nu te poti inscrie de 2 ori.", "Atentionare", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
data = data.Length <= 0 ? dgv_allcourse["ID_Materie", e.RowIndex].Value.ToString() : string.Format($"{data},{dgv_allcourse["ID_Materie", e.RowIndex].Value.ToString()}");
sql = string.Format("SELECT Id_Profesor FROM Grades WHERE ID_Student={0};", Main_form.id_user);
string id = MyData.stringSelector(sql, "Id_profesor");
sql = string.Format("SELECT IDProfesor FROM Materii WHERE Nume='{0}' AND Prenume='{1}';", dgv_allcourse["Nume", e.RowIndex].Value.ToString(), dgv_allcourse["Prenume", e.RowIndex].Value.ToString());
string realID = MyData.stringSelector(sql, "IDProfesor");
id = id.Length <= 0 ? realID : string.Format($"{id},{realID}");
sql = string.Format("UPDATE Grades SET Materii_inscris='{0}', Id_Profesor='{2}' WHERE ID_Student={1};", data, Main_form.id_user, id);
MyData.execSql(sql);
dgv_allcourse["count_st", e.RowIndex].Value = Convert.ToInt32(dgv_allcourse["count_st", e.RowIndex].Value) + 1;
sql = string.Format("UPDATE Materii SET Numar_studenti={0} WHERE IDMaterie={1} AND IDProfesor={2};", dgv_allcourse["count_st", e.RowIndex].Value, dgv_allcourse["ID_Materie", e.RowIndex].Value, realID);
MyData.execSql(sql);
AddTesteInBox(materii_db);
AddTesteInBox(materii_cb);
LoadDateMaterii();
LoadDateTeste();
MessageBox.Show($"Te-ai inscris cu succes la cursul de {dgv_allcourse["Nume_Materie", e.RowIndex].Value.ToString()} al dl-ului profesor {dgv_allcourse["Nume", e.RowIndex].Value.ToString()} {dgv_allcourse["Prenume", e.RowIndex].Value.ToString()}.", "Instintare", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
private void create_btn_Click(object sender, EventArgs e)
{
if (nume_fisier.Text == string.Empty)
{
MessageBox.Show("Numele fisierului este gresit.", "Info", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
//materii
string sql = string.Format("SELECT Nume_Materie FROM TabelMaterii WHERE ID_Profesori LIKE '%{0}%';", Main_form.id_user);
string numeMaterie = MyData.stringSelector(sql, "Nume_Materie");
path = string.Format($"teste/{numeMaterie}");
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
//exista folderul cu materie verific daca exista folderul cu numele profului
path += $"/{getName(Main_form.id_user, 1)}_{getName(Main_form.id_user, 2)}";
if (!Directory.Exists(path))
{
//directorul exista , creeam fisierul
Directory.CreateDirectory(path);
}
path += $"/{nume_fisier.Text}.txt";
//verific daca exista fisierul , daca exista eroare...
if (File.Exists(path))
{
MessageBox.Show("Fisierul exista deja , acesta poate fii doar modificat.", "Info", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
File.Create(path).Close();
create_btn.Enabled = false;
nume_fisier.Enabled = false;
studenti_lst.Enabled = false;
MessageBox.Show($"Testul a fost creat cu succes pentru studentul {studenti_lst.SelectedItem.ToString()}.\nAcum poti adauga intrebarile pentru a putea fii rezolvat.", "Info", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
private void m_ComboBoxColumn_SelectedIndexChanged(object sender, EventArgs e)
{
ComboBox comboBox = (ComboBox)sender;
if ((save_comboBox - 1) == comboBox.SelectedIndex)
{
return;
}
if (comboBox.Text != null)
{
int index = comboBox.SelectedIndex + 1;
string updateSql = string.Empty;
//Iau datele din TabelMaterii, verific daca exista userul la id-ul materiei respective folosind like dupa sterg din string id-ul si il adaug la indexul nou creat
//exista id-ul returnam id-urile profesorilor.
updateSql = string.Format("SELECT ID_Profesori FROM TabelMaterii WHERE ID_Materie={0};", save_comboBox);
string myIds = MyData.stringSelector(updateSql, "ID_Profesori");
string new_ids = string.Empty;
if (!(string.IsNullOrEmpty(myIds)))
{
string[] breaked_ids = myIds.Split(',');
if (breaked_ids.Length > 0)
{
for (int i = 0; i < breaked_ids.Length; i++)
{
int id = Convert.ToInt32(breaked_ids[i]);
if (id != save_id)
{
if (new_ids == string.Empty)
{
new_ids = breaked_ids[i] + ",";
}
else
{
new_ids += breaked_ids[i] + ",";
}
}
}
if (new_ids.Length > 0)
{
new_ids = new_ids.Remove(new_ids.Length - 1);
}
}
}
//update tabelul pt a sterge id-ul profesorului de la materia respectiva
updateSql = string.Format("UPDATE TabelMaterii SET ID_Profesori='{0}' WHERE ID_Materie={1};", new_ids, save_comboBox);
MyData.execSql(updateSql);
//adaugam id-ul in materia noua
updateSql = string.Format("SELECT ID_Profesori FROM TabelMaterii WHERE ID_Materie={0};", index);
myIds = MyData.stringSelector(updateSql, "ID_Profesori");
myIds = myIds.Length > 0 ? $"{myIds},{save_id}" : save_id.ToString();
updateSql = string.Format("UPDATE TabelMaterii SET ID_Profesori='{0}' WHERE ID_Materie={1};", myIds, index);
MyData.execSql(updateSql);
//update db
updateSql = string.Format("UPDATE Profesori SET IDMaterie={0} WHERE ID_Prof={1};", index, save_id);
MyData.execSql(updateSql);
//stergem toate testele date inainte pentru a preveni anumite probleme
//TODO:stergem toate testele pe care le-a pus la aceea materie si resetam indexurile testelor
updateSql = string.Format("DELETE ID_Student,ID_Prof,Materie,ID_Test,Nume_fisier,Nota,Nota_finala,Promovat,Promovat_Anul FROM Teste_Materii WHERE ID_Prof={0};", save_id);
MyData.execSql(updateSql);
//materii and grades modifica
updateSql = string.Format("UPDATE Materii SET IDMaterie={0} WHERE IDProfesor={1};", index, save_id);
MyData.execSql(updateSql);
updateSql = string.Format("UPDATE Materii SET IDTesteAdaugate='' AND Numar_studenti='' WHERE IDProfesor={0};", save_id);
MyData.execSql(updateSql);
//grades
updateSql = string.Format("SELECT Materii_inscris,Id_Profesor,ID_Student FROM Grades WHERE Id_Profesor LIKE '%{0}%';", save_id);
DataTable dt = MyData.readTable(updateSql);
string[] broken, materii_index;
string id_nou = string.Empty;
string materii_noi = string.Empty;
for (int i = 0; i < dt.Rows.Count; i++)
{
broken = dt.Rows[i][1].ToString().Split(',');
materii_index = dt.Rows[i][0].ToString().Split(',');
id_nou = materii_noi = string.Empty;
for (int j = 0; j < broken.Length; j++)
{
if (Convert.ToInt32(materii_index[j]) != index)
{
//rewrite it
if (materii_noi == string.Empty)
{
materii_noi = materii_index[j];
}
else
{
materii_noi += "," + materii_index[j];
}
}
if (Convert.ToInt32(broken[j]) != save_id)
{
//rewrite it without current ID
if (id_nou == string.Empty)
{
id_nou = broken[j];
}
else
{
id_nou += "," + broken[j];
}
}
}
updateSql = string.Format("UPDATE Grades SET Materii_inscris='{0}' , ID_Profesor='{1}' WHERE ID_Profesor LIKE '%{2}%' AND ID_Student={3};", materii_noi, id_nou, save_id, dt.Rows[i][2]); //add user ID
MyData.execSql(updateSql);
}
//delete directory file
//teste/<nume materie>/<nume profesor>
string[] date = MyData.getData("Materii", new string[] { "Nume", "Prenume" }, "IDProfesor", save_id);
string path = string.Format($"teste/{comboBox.SelectedItem.ToString()}/{date[0]}_{date[1]}");
if (Directory.Exists(path))
{
Directory.Delete(path, true);
}
}
comboBox.SelectedIndexChanged -= new EventHandler(m_ComboBoxColumn_SelectedIndexChanged);
btn_modifica.Focus();
if (once)
{
btn_modifica.PerformClick();
once = false;
}
modifica_dgv.Refresh();
}
private void studenti_dgv_CellClick(object sender, DataGridViewCellEventArgs e)
{
if (tip == 1)
{
if (e.RowIndex >= 0 && studenti_dgv.Columns[e.ColumnIndex] is DataGridViewButtonColumn)
{
string sql = string.Empty;
sql = string.Format("SELECT Nume_Materie FROM TabelMaterii WHERE ID_Profesori LIKE '%{0}%';", Main_form.id_user);
string numeMaterie = MyData.stringSelector(sql, "Nume_Materie");
string path = string.Format($"teste/{numeMaterie}/{getName(Main_form.id_user, 1)}_{getName(Main_form.id_user, 2)}/{studenti_dgv[1, e.RowIndex].Value}.txt");
if (e.ColumnIndex == 3)
{
//sterge test
DialogResult res = MessageBox.Show($"Esti sigur ca vrei sa stergi acest test pentru elevul {nume_cmb.SelectedItem.ToString()} ?", "Info", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
switch (res)
{
case DialogResult.Yes:
sql = string.Format("SELECT ID_Test FROM Teste_Materii WHERE ID_Student={0} AND ID_Prof={1} AND Nume_fisier='{2}';", getStudentID(), Main_form.id_user, studenti_dgv[1, e.RowIndex].Value);
int id_test = MyData.selectData(sql, "ID_Test");
sql = string.Format("SELECT IDTesteAdaugate FROM Materii WHERE IDProfesor={0};", Main_form.id_user);
string id_teste = MyData.stringSelector(sql, "IDTesteAdaugate");
string[] splitTeste = id_teste.Split(',');
string[] new_data = new string[splitTeste.Length - 1];
int j = 0;
for (int i = 0; i < splitTeste.Length; i++)
{
if (id_test != Convert.ToInt32(splitTeste[i]))
{
new_data[j] = splitTeste[i];
j++;
}
}
id_teste = string.Join(",", new_data);
sql = string.Format("UPDATE Materii SET IDTesteAdaugate='{0}' WHERE IDProfesor={1};", id_teste, Main_form.id_user);
MyData.execSql(sql);
//delete test
sql = string.Format("DELETE * FROM Teste_Materii WHERE ID_Student={0} AND ID_Prof={1} AND Nume_fisier='{2}';", getStudentID(), Main_form.id_user, studenti_dgv[1, e.RowIndex].Value);
MyData.execSql(sql);
if (File.Exists(path))
{
File.Delete(path);
}
MessageBox.Show("Testul a fost sters cu succes din baza de date.", "Informatie", MessageBoxButtons.OK, MessageBoxIcon.Information);
//recreate
ListaTeste();
break;
case DialogResult.No:
break;
}
}
else if (e.ColumnIndex == 4)
{
//modifica test
if (File.Exists(path))
{
editor = new TestEditor(path);
editor.Show();
}
}
else if (e.ColumnIndex == 5)
{
//reset test
sql = string.Format("UPDATE Teste_Materii SET Nota=0 AND Promovat=false WHERE ID_Test={0} AND ID_Student={1} AND ID_Prof={2};", studenti_dgv[0, e.RowIndex].Value.ToString(), getStudentID(), Main_form.id_user);
MyData.execSql(sql);
ListaTeste();
MessageBox.Show("Testul a fost reinitializat cu succes, acum studentul poate relua testul.", "Informatie", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
}