private void lista_dgv_CellEndEdit(object sender, DataGridViewCellEventArgs e)
{
if (type == 0 && e.ColumnIndex == 2 && e.RowIndex >= 0)
{
if (lista_dgv[2, e.RowIndex].Value.ToString() == string.Empty)
{
MessageBox.Show("Nu poti lasa campuri goale.", "Eroare", MessageBoxButtons.OK, MessageBoxIcon.Information);
lista_dgv[2, e.RowIndex].Value = old_value;
return;
}
if (Convert.ToInt32(lista_dgv[2, e.RowIndex].Value) <= 0 || Convert.ToInt32(lista_dgv[2, e.RowIndex].Value) > 10)
{
MessageBox.Show("Nota nu poate fii mai mica decat 1 sau mai mare de 10.", "Eroare", MessageBoxButtons.OK, MessageBoxIcon.Information);
lista_dgv[2, e.RowIndex].Value = old_value;
return;
}
//after all exceptions save data.
string sql = string.Format("UPDATE Teste_Materii SET Nota={0} WHERE ID_Student={1} AND Materie='{2}' AND ID_Test={3};", lista_dgv[2, e.RowIndex].Value, data[2], box_materii.SelectedItem, lista_dgv[0, e.RowIndex].Value);
MyData.execSql(sql);
//recalculam media
saveTotal();
MessageBox.Show("Nota modificata cu succes.", "Modificare cu succes", MessageBoxButtons.OK, MessageBoxIcon.Information);
getData();
}
}
public void saveTotal()
{
string sql = string.Format("SELECT Nota FROM Teste_Materii WHERE ID_Student={0} AND Materie='{1}';", data[2], box_materii.SelectedItem);
DataTable dt = MyData.readTable(sql);
double total = 0;
for (int i = 0; i < dt.Rows.Count; i++)
{
total += Convert.ToInt32(dt.Rows[i][0]);
}
total = total / dt.Rows.Count;
total = Math.Ceiling(total);
sql = string.Format("UPDATE Teste_Materii SET Nota_finala={0} WHERE ID_Student={1} AND Materie='{2}';", total, data[2], box_materii.SelectedItem);
MyData.execSql(sql);
}
public void CheckPromovation()
{
test_on = false;
rez_panel.Visible = true;
int count = intrebari.Count / 2;
promovat_txt.Text = punctaj_user < count + 1 ? $"{punctaj_user} puncte\nImi pare rau ai picat testul.":$"{punctaj_user} puncte\nFelicitari , ai promovat testul.";
promovat_txt.ForeColor = punctaj_user < count ? System.Drawing.Color.Red:System.Drawing.Color.LimeGreen;
image_p.Image = punctaj_user < count?System.Drawing.Image.FromFile("images/fail.jpg") : System.Drawing.Image.FromFile("images/promoted.png");
//add in database all informations
string addSql = string.Format("UPDATE Teste_Materii SET Nota={0} WHERE ID_Student={1} AND Materie='{2}' AND ID_Test={3};", punctaj_user, Main_form.id_user, prop["Materie", IndexRow].Value.ToString(), prop[0, IndexRow].Value);
MyData.execSql(addSql);
reload.ReloadAll();
prop.Refresh();
}
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);
}
}
private void studenti_dgv_CellEndEdit(object sender, DataGridViewCellEventArgs e)
{
if (tab == 1)
{
return;
}
if (tip == 2 || tip == 3 && e.ColumnIndex == 3)
{
if (e.RowIndex >= 0)
{
if (Convert.ToInt32(studenti_dgv[e.ColumnIndex, e.RowIndex].Value) == stock_data)
{
return;
}
if (Convert.ToInt32(studenti_dgv[e.ColumnIndex, e.RowIndex].Value) <= 0 || Convert.ToInt32(studenti_dgv[e.ColumnIndex, e.RowIndex].Value) > 10)
{
MessageBox.Show("Nu poti pune o nota mai mica de 1 sau mai mare de 10.", "Eroare", MessageBoxButtons.OK, MessageBoxIcon.Error);
studenti_dgv[e.ColumnIndex, e.RowIndex].Value = stock_data;
return;
}
string sql = string.Empty;
//start updating
sql = string.Format("UPDATE Teste_Materii SET Nota={0} WHERE ID_Student={1} AND ID_Prof={2} AND Nume_fisier='{3}';", studenti_dgv[e.ColumnIndex, e.RowIndex].Value, getStudentID(), Main_form.id_user, studenti_dgv[4, e.RowIndex].Value);
MyData.execSql(sql);
bool promotion = Convert.ToInt32(studenti_dgv[e.ColumnIndex, e.RowIndex].Value) >= 5 ? true : false;
sql = string.Format("UPDATE Teste_Materii SET Promovat={0} WHERE ID_Student={1} AND ID_Prof={2} AND Nume_fisier='{3}';", promotion, getStudentID(), Main_form.id_user, studenti_dgv[4, e.RowIndex].Value);
MyData.execSql(sql);
//now update all cuz yeah...
int medie = 0;
for (int i = 0; i < studenti_dgv.Rows.Count; i++)
{
medie += Convert.ToInt32(studenti_dgv["Nota", i].Value);
}
medie = medie / studenti_dgv.Rows.Count;
sql = string.Format("UPDATE Teste_Materii SET Nota_finala={0} WHERE ID_Student={1} AND ID_Prof={2};", medie, getStudentID(), Main_form.id_user);
MyData.execSql(sql);
promotion = medie >= 5 ? true : false;
sql = string.Format("UPDATE Teste_Materii SET Promovat_Anul={0} WHERE ID_Student={1} AND ID_Prof={2};", promotion, getStudentID(), Main_form.id_user);
MyData.execSql(sql);
//reset dgv ==> unbug method
BeginInvoke(new MethodInvoker(LoadDataStudenti));
}
}
}
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 register_btn_Click(object sender, EventArgs e)
{
if (nume_txt.Text == string.Empty || nick_txt.Text == string.Empty || prenume_txt.Text == string.Empty || pass_txt.Text == string.Empty || repass_txt.Text == string.Empty)
{
MessageBox.Show("Toate campurile sunt obligatorii.", "Eroare", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
else if (nick_txt.Text == Main_form.director_acc[0])
{
MessageBox.Show("Nickname deja folosit,alege altul.", "Nick folosit", MessageBoxButtons.OK, MessageBoxIcon.Error);
nick_txt.Text = string.Empty;
return;
}
//verifica daca contul exista in baza de date Studenti si Profesori ( nick -ul mai exact ) dupa verifica daca exista numele si prenumele in baza de date.
if (NickAlreadyExisted(nick_txt.Text))
{
MessageBox.Show("Nickname deja folosit,alege altul.", "Nick folosit", MessageBoxButtons.OK, MessageBoxIcon.Error);
nick_txt.Text = string.Empty;
}
if (NumeAlreadyExisted(nume_txt.Text, prenume_txt.Text))
{
MessageBox.Show("Acel nume si prenume exista deja in baza de date.Poti avea doar un cont cu acealsi nume.", "Nume existent", MessageBoxButtons.OK, MessageBoxIcon.Error);
nume_txt.Text = prenume_txt.Text = string.Empty;
}
//update table
string addsql = string.Format("INSERT INTO Studenti(Nick,Parola)VALUES('{0}','{1}');", nick_txt.Text, pass_txt.Text);
MyData.execSql(addsql);
string sqlID = string.Format("SELECT ID_Student FROM Studenti WHERE Nick='{0}' and Parola='{1}';", nick_txt.Text, pass_txt.Text);
int id_student = MyData.selectData(sqlID, "ID_Student");
string addNume = string.Format("INSERT INTO Grades(ID_Student,Nume,Prenume)VALUES({0},'{1}','{2}');", id_student, nume_txt.Text, prenume_txt.Text);
MyData.execSql(addNume);
MessageBox.Show("Cont creat cu succes, acum te poti loga !", "Succes !", MessageBoxButtons.OK, MessageBoxIcon.Information);
Main_form.CloseMain();
}
public void LoadDateMaterii()
{
mycourse_dgv.Columns.Clear();
if (materii_db.Items.Count > 0)
{
string selectDates = string.Format("SELECT ID_Test,Nume_fisier,Nota,Promovat FROM Teste_Materii WHERE ID_Student={0} AND Materie='{1}' AND Nota<>0;", Main_form.id_user, materii_db.SelectedItem.ToString());
DataTable dt = MyData.readTable(selectDates);
if (dt.Rows.Count > 0)
{
mycourse_dgv.DataSource = dt;
mycourse_dgv.Columns["Nume_fisier"].HeaderText = "Nume test";
mycourse_dgv.Columns.Add("name", "Nume profesor");
mycourse_dgv.Columns.Add("prename", "Prenume profesor");
//select name and prename using profesor ID
string IdProf = string.Format("SELECT Materii_inscris,Id_Profesor FROM Grades WHERE ID_Student={0};", Main_form.id_user);
dt = MyData.readTable(IdProf);
string Id_materie = string.Format("SELECT ID_Materie FROM TabelMaterii WHERE Nume_Materie='{0}';", materii_db.SelectedItem.ToString());
int ID = MyData.selectData(Id_materie, "ID_Materie");
string[] splitData1 = dt.Rows[0][0].ToString().Split(',');
string[] splitData2 = dt.Rows[0][1].ToString().Split(',');
for (int i = 0; i < splitData1.Length; i++)
{
int IDS = Convert.ToInt32(splitData1[i]);
if (ID == IDS)
{
//am gasit date le folosim
selectDates = string.Format("SELECT Nume,Prenume FROM Materii WHERE IDProfesor={0} AND IDMaterie={1};", splitData2[i], splitData1[i]);
data = MyData.getData(selectDates, new string[] { "Nume", "Prenume" });
}
}
//setam daca e sau nu promovat
for (int i = 0; i < mycourse_dgv.Rows.Count; i++)
{
if (Convert.ToInt32(mycourse_dgv["Nota", i].Value) >= 5)
{
mycourse_dgv["Promovat", i].Value = true;
}
else
{
mycourse_dgv["Promovat", i].Value = false;
}
string update = string.Format("UPDATE Teste_Materii SET Promovat={2} WHERE Materie='{0}' AND ID_Test={1};", materii_db.SelectedItem.ToString(), mycourse_dgv["ID_Test", i].Value, mycourse_dgv["Promovat", i].Value);
MyData.execSql(update);
}
//calculam media + verificam daca e sau nu promovat
if (mycourse_dgv.Rows.Count > 0)
{
promovat_chk.Visible = true;
medie_txt.Visible = true;
label3.Visible = true;
float total = 0f, count = 0;
for (int i = 0; i < mycourse_dgv.Rows.Count; i++)
{
total += Convert.ToSingle(mycourse_dgv["Nota", i].Value);
count++;
}
double medie = total / count;
medie_txt.Text = string.Format("{0:0.00}", medie);
if (medie < 4.5)
{
promovat_chk.Checked = false;
medie = Math.Round(medie, 2);
}
else if (medie == 4.5)
{
promovat_chk.Checked = true;
medie = Math.Ceiling(medie);
}
else
{
promovat_chk.Checked = true;
medie = Math.Round(medie, 2);
}
string update = string.Format("UPDATE Teste_Materii SET Promovat_Anul={1} WHERE Materie='{0}';", materii_db.SelectedItem.ToString(), promovat_chk.Checked);
MyData.execSql(update);
update = string.Format("UPDATE Teste_Materii SET Nota_finala={0} WHERE ID_Student={1} AND Materie='{2}';", medie, Main_form.id_user, materii_db.SelectedItem.ToString());
MyData.execSql(update);
}
else
{
promovat_chk.Visible = false;
medie_txt.Visible = false;
label3.Visible = false;
}
}
}
}
private void log_in_Click(object sender, EventArgs e)
{
if (user_txt.Text == string.Empty || pass_txt.Text == string.Empty)
{
MessageBox.Show("Campuri invalide , te rog sa introduci campuri valide.", "Eroare", MessageBoxButtons.OK, MessageBoxIcon.Error);
user_txt.Focus();
return;
}
IsLogged = false;
IsProfessor = false;
if (user_txt.Text == director_acc[0] && user_txt.Text == director_acc[1])
{
//director control panel
IsProfessor = false;
IsLogged = true;
directorpnl = new DirectorPanel();
directorpnl.main = this;
directorpnl.TextMain[0] = user_txt;
directorpnl.TextMain[1] = pass_txt;
this.Hide();
directorpnl.Show();
}
else
{
string sql = string.Format("SELECT Nick,Parola FROM Studenti WHERE Nick='{0}' AND Parola='{1}';", user_txt.Text, pass_txt.Text);
string sql2 = string.Format("SELECT Nick,Pass FROM Profesori WHERE Nick='{0}' AND Pass='******';", user_txt.Text, pass_txt.Text);
short countStudenti = MyData.countData(sql);
short countProfesor = MyData.countData(sql2);
if (countStudenti == 1)
{
IsProfessor = false;
IsLogged = true;
string sqlQ = string.Format("SELECT ID_Student FROM Studenti WHERE Nick='{0}' and Parola='{1}';", user_txt.Text, pass_txt.Text);
id_user = MyData.selectData(sqlQ, "ID_Student");
string[] numestudent = new string[2] {
"Nume", "Prenume"
};
date = MyData.getData("Grades", numestudent, "ID_Student", id_user);
string update = string.Format("UPDATE Studenti SET Ultima_logare='{0}' WHERE ID_Student={1};", DateTime.Now, id_user);
MyData.execSql(update);
MessageBox.Show("Bine ai venit " + date[0] + " " + date[1] + " !", "Bine ai revenit !", MessageBoxButtons.OK, MessageBoxIcon.Information);
//show student form.
studenpnl = new StudentPanel();
studenpnl.main = this;
studenpnl.TextMain[0] = user_txt;
studenpnl.TextMain[1] = pass_txt;
studenpnl.Show();
this.Hide();
}
else if (countProfesor == 1)
{
IsProfessor = true;
IsLogged = true;
string sqlQ = string.Format("SELECT ID_Prof FROM Profesori WHERE Nick='{0}' and Pass='******';", user_txt.Text, pass_txt.Text);
id_user = MyData.selectData(sqlQ, "ID_Prof");
string[] numeprof = new string[2] {
"Nume", "Prenume"
};
string[] date = MyData.getData("Materii", numeprof, "IDProfesor", id_user);
string update = string.Format("UPDATE Profesori SET Ultima_logare='{0}' WHERE ID_Prof={1};", DateTime.Now, id_user);
MyData.execSql(update);
MessageBox.Show("Bine ai venit , profesor " + date[0] + " " + date[1] + " !", "Bine ai revenit !", MessageBoxButtons.OK, MessageBoxIcon.Information);
//show prof form.
profesorpnl = new ProfesorPanel();
//set main
profesorpnl.main = this;
profesorpnl.Info[0] = user_txt;
profesorpnl.Info[1] = pass_txt;
profesorpnl.Show();
this.Hide();
}
else
{
MessageBox.Show("Nume sau parola gresita, te rog sa introduci din nou campurile.", "Eroare", MessageBoxButtons.OK, MessageBoxIcon.Error);
user_txt.Focus();
user_txt.Text = pass_txt.Text = string.Empty;
}
}
}
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 modifica_dgv_CellEndEdit(object sender, DataGridViewCellEventArgs e)
{
//ne asiguram ca nu e goala
if (!(modifica_dgv.Columns[e.ColumnIndex] is DataGridViewComboBoxColumn) || !(modifica_dgv.Columns[e.ColumnIndex] is DataGridViewButtonColumn))
{
if (string.IsNullOrEmpty(modifica_dgv[e.ColumnIndex, e.RowIndex].Value.ToString()))
{
MessageBox.Show("Nu poti avea empty data in nici o celula.", "Eroare", MessageBoxButtons.OK, MessageBoxIcon.Error);
modifica_dgv[e.ColumnIndex, e.RowIndex].Value = old_data;
return;
}
}
//updateaza baza de date cand o celula e editata
string updateSql = string.Empty;
if (lista_modifica.SelectedItem.ToString() == lista_modifica.Items[0].ToString())
{
//pt profesori alt switch alte coloane
switch (Convert.ToInt32(e.ColumnIndex))
{
case 1: //nume
if (CreateAccount.NumeAlreadyExisted(modifica_dgv["Nume", e.RowIndex].Value.ToString(), modifica_dgv["Prenume", e.RowIndex].Value.ToString()))
{
MessageBox.Show("Acel nume si prenume exista deja in baza de date.Poti avea doar un cont cu acealsi nume.", "Nume existent", MessageBoxButtons.OK, MessageBoxIcon.Error);
modifica_dgv[e.ColumnIndex, e.RowIndex].Value = old_data;
return;
}
updateSql = string.Format("UPDATE Materii SET Nume='{0}' WHERE IDProfesor={1};", modifica_dgv[e.ColumnIndex, e.RowIndex].Value.ToString(), modifica_dgv[0, e.RowIndex].Value);
MyData.execSql(updateSql);
break;
case 2: //prenume
if (CreateAccount.NumeAlreadyExisted(modifica_dgv["Nume", e.RowIndex].Value.ToString(), modifica_dgv["Prenume", e.RowIndex].Value.ToString()))
{
MessageBox.Show("Acel nume si prenume exista deja in baza de date.Poti avea doar un cont cu acealsi nume.", "Prenume existent", MessageBoxButtons.OK, MessageBoxIcon.Error);
modifica_dgv[e.ColumnIndex, e.RowIndex].Value = old_data;
return;
}
updateSql = string.Format("UPDATE Materii SET Nume='{0}' WHERE IDProfesor={1};", modifica_dgv[e.ColumnIndex, e.RowIndex].Value.ToString(), modifica_dgv[0, e.RowIndex].Value);
MyData.execSql(updateSql);
break;
case 3: //nick
if (CreateAccount.NickAlreadyExisted(modifica_dgv[e.ColumnIndex, e.RowIndex].Value.ToString()) || modifica_dgv[e.ColumnIndex, e.RowIndex].Equals(Main_form.director_acc[0]))
{
MessageBox.Show("Nickname deja folosit,alege altul.", "Nick folosit", MessageBoxButtons.OK, MessageBoxIcon.Error);
modifica_dgv[e.ColumnIndex, e.RowIndex].Value = old_data;
return;
}
updateSql = string.Format("UPDATE Profesori SET Nick='{0}' WHERE ID_Prof={1};", modifica_dgv[e.ColumnIndex, e.RowIndex].Value.ToString(), modifica_dgv[0, e.RowIndex].Value);
MyData.execSql(updateSql);
break;
case 4: //pass
if (modifica_dgv[e.ColumnIndex, e.RowIndex].Value.ToString().Length < 6)
{
MessageBox.Show("Parola ta trebuie sa aiba cel putin 6 caractere.", "Eroare", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
modifica_dgv[e.ColumnIndex, e.RowIndex].Value = old_data;
return;
}
updateSql = string.Format("UPDATE Profesori SET Pass='******' WHERE ID_Prof={1};", modifica_dgv[e.ColumnIndex, e.RowIndex].Value.ToString(), modifica_dgv[0, e.RowIndex].Value);
MyData.execSql(updateSql);
break;
}
}
else
{
switch (Convert.ToInt32(e.ColumnIndex))
{
case 1: //nume
if (CreateAccount.NumeAlreadyExisted(modifica_dgv["Nume", e.RowIndex].Value.ToString(), modifica_dgv["Prenume", e.RowIndex].Value.ToString()))
{
MessageBox.Show("Acel nume si prenume exista deja in baza de date.Poti avea doar un cont cu acealsi nume.", "Nume existent", MessageBoxButtons.OK, MessageBoxIcon.Error);
modifica_dgv[e.ColumnIndex, e.RowIndex].Value = old_data;
return;
}
updateSql = string.Format("UPDATE Grades SET Nume='{0}' WHERE ID_Student={1};", modifica_dgv[e.ColumnIndex, e.RowIndex].Value.ToString(), modifica_dgv[0, e.RowIndex].Value);
break;
case 2: //prenume
if (CreateAccount.NumeAlreadyExisted(modifica_dgv["Nume", e.RowIndex].Value.ToString(), modifica_dgv["Prenume", e.RowIndex].Value.ToString()))
{
MessageBox.Show("Acel nume si prenume exista deja in baza de date.Poti avea doar un cont cu acealsi nume.", "Prenume existent", MessageBoxButtons.OK, MessageBoxIcon.Error);
modifica_dgv[e.ColumnIndex, e.RowIndex].Value = old_data;
return;
}
updateSql = string.Format("UPDATE Grades SET Prenume='{0}' WHERE ID_Student={1};", modifica_dgv[e.ColumnIndex, e.RowIndex].Value.ToString(), modifica_dgv[0, e.RowIndex].Value);
break;
case 3: //nick
if (CreateAccount.NickAlreadyExisted(modifica_dgv[e.ColumnIndex, e.RowIndex].Value.ToString()) || modifica_dgv[e.ColumnIndex, e.RowIndex].Equals(Main_form.director_acc[0]))
{
MessageBox.Show("Nickname deja folosit,alege altul.", "Nick folosit", MessageBoxButtons.OK, MessageBoxIcon.Error);
modifica_dgv[e.ColumnIndex, e.RowIndex].Value = old_data;
return;
}
updateSql = string.Format("UPDATE Studenti SET Nick='{0}' WHERE ID_Student={1};", modifica_dgv[e.ColumnIndex, e.RowIndex].Value.ToString(), modifica_dgv[0, e.RowIndex].Value);
break;
case 4: //pass
//verificari pt parola
if (modifica_dgv[e.ColumnIndex, e.RowIndex].Value.ToString().Length < 6)
{
MessageBox.Show("Parola ta trebuie sa aiba cel putin 6 caractere.", "Eroare", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
modifica_dgv[e.ColumnIndex, e.RowIndex].Value = old_data;
return;
}
updateSql = string.Format("UPDATE Studenti SET Parola='{0}' WHERE ID_Student={1};", modifica_dgv[e.ColumnIndex, e.RowIndex].Value.ToString(), modifica_dgv[0, e.RowIndex].Value);
break;
case 5: //materii la care e inscris
//updateSql = string.Format("SELECT Materii_inscris FROM Grades WHERE ID_STUDENT='{0}';");
//string materii = MyData.stringSelector(updateSql,"Materii_inscris");
//string[] split_materii = materii.Split(',');
//if (split_materii.Length >= 3)
string[] myS = modifica_dgv[e.ColumnIndex, e.RowIndex].Value.ToString().Split(',');
if (!checkForType(myS))
{
MessageBox.Show("Date introduse gresit.", "Eroare", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
modifica_dgv[e.ColumnIndex, e.RowIndex].Value = old_data;
return;
}
if (myS.Length > 3 || verifyNoDub(myS))
{
MessageBox.Show("Elevul se poate inscrie maximum la 3 materii o singura data.", "Eroare", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
modifica_dgv[e.ColumnIndex, e.RowIndex].Value = old_data;
return;
}
updateSql = string.Format("UPDATE Grades SET Materii_inscris='{0}' WHERE ID_Student={1};", modifica_dgv[e.ColumnIndex, e.RowIndex].Value.ToString(), modifica_dgv[0, e.RowIndex].Value);
break;
}
MyData.execSql(updateSql);
}
}
private void date_dgv_CellClick(object sender, DataGridViewCellEventArgs e)
{
if (date_dgv.Columns[e.ColumnIndex] is DataGridViewButtonColumn && e.RowIndex >= 0)
{
//ask first
string mesaj = string.Empty;
mesaj = string.Format("Esti sigur ca vrei sa stergi {0} {1} {2} ?", lista_modifica.SelectedIndex == 0? "profesorul":"studentul", date_dgv["Nume", e.RowIndex].Value.ToString(), date_dgv["Prenume", e.RowIndex].Value.ToString());
DialogResult result = MessageBox.Show(mesaj, "Stergere", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
switch (result)
{
case DialogResult.Yes:
//delete record
string sql = string.Empty;
if (lista_modifica.SelectedIndex == 0)
{
string materie_sql = string.Format("SELECT IDMaterie FROM Materii WHERE IDProfesor={0};", date_dgv["IDProfesor", e.RowIndex].Value);
int id_materie = MyData.selectData(materie_sql, "IDMaterie");
string[] selectMaterii = MyData.getData("TabelMaterii", new string[] { "ID_Profesori" }, "ID_Materie", id_materie);
sql = string.Format("DELETE ID_Prof,Nick,Pass,IDMaterie,Ultima_Logare FROM Profesori WHERE ID_Prof={0};", date_dgv["IDProfesor", e.RowIndex].Value);
MyData.execSql(sql);
sql = string.Format("DELETE IDMaterie,IDProfesor,Nume,Prenume,IDTesteAdaugate FROM Materii WHERE IDProfesor={0};", date_dgv["IDProfesor", e.RowIndex].Value);
MyData.execSql(sql);
string id_nou = string.Empty;
string[] new_idprofi = selectMaterii[0].Split(',');
for (int i = 0; i < new_idprofi.Length; i++)
{
if (new_idprofi[i] != date_dgv["IDProfesor", e.RowIndex].Value.ToString())
{
//daca nu e egal cu id-ul profului nostru pe care il stergem ii dam add in noul string
if (id_nou == string.Empty)
{
id_nou = new_idprofi[i];
}
else
{
id_nou += "," + new_idprofi[i];
}
}
}
sql = string.Format("UPDATE TabelMaterii SET ID_Profesori='{0}' WHERE ID_Materie={1};", id_nou, id_materie);
MyData.execSql(sql);
sql = 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};", date_dgv["IDProfesor", e.RowIndex].Value);
MyData.execSql(sql);
//TODO:delete index from grades too
sql = string.Format("SELECT Materii_inscris,Id_Profesor,ID_Student FROM Grades WHERE Id_Profesor LIKE '%{0}%';", date_dgv["IDProfesor", e.RowIndex].Value);
DataTable dt = MyData.readTable(sql);
string[] broken, materii_index;
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]) != id_materie)
{
//rewrite it
if (materii_noi == string.Empty)
{
materii_noi = materii_index[j];
}
else
{
materii_noi += "," + materii_index[j];
}
}
if (Convert.ToInt32(broken[j]) != Convert.ToInt32(date_dgv["IDProfesor", e.RowIndex].Value))
{
//rewrite it without current ID
if (id_nou == string.Empty)
{
id_nou = broken[j];
}
else
{
id_nou += "," + broken[j];
}
}
}
sql = string.Format("UPDATE Grades SET Materii_inscris='{0}' , ID_Profesor='{1}' WHERE ID_Profesor LIKE '%{2}%' AND ID_Student={3};", materii_noi, id_nou, date_dgv["IDProfesor", e.RowIndex].Value, dt.Rows[i][2]); //add user ID
MyData.execSql(sql);
string path = string.Format($"teste/{modifica_dgv["Materie", e.RowIndex].Value.ToString()}/{modifica_dgv["Nume", e.RowIndex].Value.ToString()}_{modifica_dgv["Prenume", e.RowIndex].Value.ToString()}");
if (Directory.Exists(path))
{
Directory.Delete(path, true);
}
}
}
else
{
sql = string.Format("DELETE ID_Student,Nick,Parola,Ultima_logare FROM Studenti WHERE ID_Student={0};", date_dgv["ID_Student", e.RowIndex].Value);
MyData.execSql(sql);
sql = string.Format("DELETE ID_Student,Nume,Prenume,Materii_inscris FROM Grades WHERE ID_Student={0};", date_dgv["ID_Student", e.RowIndex].Value);
MyData.execSql(sql);
sql = string.Format("DELETE ID_Student,ID_Prof,Materie,ID_Test,Nume_fisier,Nota,Nota_finala,Promovat,Promovat_Anul FROM Teste_Materii WHERE ID_Student={0};", date_dgv["ID_Student", e.RowIndex].Value);
MyData.execSql(sql);
}
//mesaj
mesaj = string.Format("{0} {1} {2} a fost sters din baza de date !", lista_modifica.SelectedIndex == 0 ? "Profesorul" : "Studentul", date_dgv["Nume", e.RowIndex].Value.ToString(), date_dgv["Prenume", e.RowIndex].Value.ToString());
MessageBox.Show(mesaj, "Camp sters !", MessageBoxButtons.OK, MessageBoxIcon.Information);
//recreate table
CreateTable(1);
break;
case DialogResult.No:
//nu se intampla nimic
break;
}
}
}
private void adauga_btn_Click(object sender, EventArgs e)
{
//same ca la creare cont :)
if (lista_modifica.SelectedItem.ToString() != lista_modifica.Items[0].ToString())
{
if (nume_txt.Text == string.Empty || nick_txt.Text == string.Empty || prenume_txt.Text == string.Empty || pass_txt.Text == string.Empty)
{
MessageBox.Show("Toate campurile sunt obligatorii.", "Eroare", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
else if (nick_txt.Text == Main_form.director_acc[0])
{
MessageBox.Show("Nickname deja folosit,alege altul.", "Nick folosit", MessageBoxButtons.OK, MessageBoxIcon.Error);
nume_txt.Text = string.Empty;
return;
}
}
else
{
if (nume_txt.Text == string.Empty || nick_txt.Text == string.Empty || prenume_txt.Text == string.Empty || pass_txt.Text == string.Empty || materii_combo.SelectedIndex == -1)
{
MessageBox.Show("Toate campurile sunt obligatorii.", "Eroare", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
else if (nick_txt.Text == Main_form.director_acc[0])
{
MessageBox.Show("Nickname deja folosit,alege altul.", "Nick folosit", MessageBoxButtons.OK, MessageBoxIcon.Error);
nume_txt.Text = string.Empty;
return;
}
}
//verifica daca contul exista in baza de date Studenti si Profesori ( nickname-ul mai exact ) dupa verifica daca exista numele si prenumele in baza de date.
string sql = string.Format("SELECT Nick,Parola FROM Studenti WHERE Nick='{0}';", nick_txt.Text);
string sql2 = string.Format("SELECT Nick,Pass FROM Profesori WHERE Nick='{0}';", nick_txt.Text);
short countStudenti = MyData.countData(sql);
short countProfesor = MyData.countData(sql2);
if (countProfesor >= 1 || countStudenti >= 1)
{
MessageBox.Show("Nickname deja folosit,alege altul.", "Nick folosit", MessageBoxButtons.OK, MessageBoxIcon.Error);
nick_txt.Text = string.Empty;
return;
}
string sqlQ = string.Format("SELECT ID_Student FROM Grades WHERE Nume='{0}' AND Prenume='{1}';", nume_txt.Text, prenume_txt.Text);
string sqlprof = string.Format("SELECT IDProfesor FROM Materii WHERE Nume='{0}' AND Prenume='{1}';", nume_txt.Text, prenume_txt.Text);
short countStudentNume = MyData.countData(sqlQ);
short countprofNume = MyData.countData(sqlprof);
if (countStudentNume >= 1 || countprofNume >= 1)
{
MessageBox.Show("Acel nume si prenume exista deja in baza de date.Poti avea doar un cont cu acealsi nume.", "Nume existent", MessageBoxButtons.OK, MessageBoxIcon.Error);
nume_txt.Text = prenume_txt.Text = string.Empty;
return;
}
if (lista_modifica.SelectedItem.ToString() != lista_modifica.Items[0].ToString()) //add student
{
string addsql = string.Format("INSERT INTO Studenti(Nick,Parola)VALUES('{0}','{1}');", nick_txt.Text, pass_txt.Text);
MyData.execSql(addsql);
string sqlID = string.Format("SELECT ID_Student FROM Studenti WHERE Nick='{0}' and Parola='{1}';", nick_txt.Text, pass_txt.Text);
int id_student = MyData.selectData(sqlID, "ID_Student");
string addNume = string.Format("INSERT INTO Grades(ID_Student,Nume,Prenume)VALUES({0},'{1}','{2}');", id_student, nume_txt.Text, prenume_txt.Text);
MyData.execSql(addNume);
MessageBox.Show("Student adaugat !", "Adaugare cu succces", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
nume_txt.Text = pass_txt.Text = nick_txt.Text = prenume_txt.Text = string.Empty;
}
else //else add profesor
{
int id_materie = (Convert.ToInt32(materii_combo.SelectedIndex) + 1);
string addprof = string.Format("INSERT INTO Profesori(Nick,Pass,IDMaterie)VALUES('{0}','{1}',{2});", nick_txt.Text, pass_txt.Text, id_materie);
MyData.execSql(addprof);
string sqlProfID = string.Format("SELECT ID_Prof FROM Profesori WHERE Nick='{0}' AND Pass='******';", nick_txt.Text, pass_txt.Text);
int IdProf = MyData.selectData(sqlProfID, "ID_Prof");
string add = string.Format("INSERT INTO Materii(IDMaterie,IDProfesor,Nume,Prenume)VALUES('{0}','{1}','{2}','{3}');", id_materie, IdProf, nume_txt.Text, prenume_txt.Text);
MyData.execSql(add);
string new_id = string.Empty;
string[] selectMaterii = MyData.getData("TabelMaterii", new string[] { "ID_Profesori" }, "ID_Materie", id_materie);
if (selectMaterii[0].Length > 0)
{
new_id = selectMaterii[0] + "," + IdProf.ToString();
}
else
{
new_id = IdProf.ToString();
}
string addquery = string.Format("UPDATE TabelMaterii SET ID_Profesori='{0}' WHERE ID_Materie={1};", new_id, id_materie);
MyData.execSql(addquery);
MessageBox.Show("Profesor adaugat !", "Adaugare cu succces", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
nume_txt.Text = pass_txt.Text = nick_txt.Text = prenume_txt.Text = string.Empty;
}
}
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);
}
}
}
}