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 InitAllCourses()
{
dgv_allcourse.Columns.Clear();
string sql = string.Format("SELECT TabelMaterii.ID_Materie,TabelMaterii.Nume_Materie,Materii.Nume,Materii.Prenume,Materii.IDTesteAdaugate FROM TabelMaterii,Materii WHERE TabelMaterii.ID_Materie=Materii.IDMaterie;");
DataTable dt = MyData.readTable(sql);
if (dt.Rows.Count > 0)
{
dgv_allcourse.DataSource = dt;
dgv_allcourse.Columns["Nume"].HeaderText = "Nume profesor";
dgv_allcourse.Columns["ID_Materie"].HeaderText = "ID Materie";
dgv_allcourse.Columns["Nume_Materie"].HeaderText = "Nume Materie";
DataGridViewTextBoxColumn nr_teste = new DataGridViewTextBoxColumn();
nr_teste.Name = "nr_teste";
nr_teste.HeaderText = "Numar teste";
nr_teste.ValueType = typeof(string);
dgv_allcourse.Columns.Add(nr_teste);
int count = 0;
for (int i = 0; i < dgv_allcourse.Rows.Count; i++)
{
if (dt.Rows[i]["IDTesteAdaugate"].ToString().Contains(","))
{
count = Convert.ToInt32(dt.Rows[i]["IDTesteAdaugate"].ToString().Split(',').Length);
}
else
{
if (dt.Rows[i]["IDTesteAdaugate"].ToString().Length != 0)
{
count = 1;
}
else
{
count = 0;
}
}
dgv_allcourse["nr_teste", i].Value = count.ToString();
}
dgv_allcourse.Columns.Remove("IDTesteAdaugate");
dgv_allcourse.Columns.Add("count_st", "Studenti inscrisi");
//numara cati elevi are fiecare profesor | start counting
DataTable tb = new DataTable();
sql = string.Format("SELECT Numar_studenti FROM Materii;");
tb = MyData.readTable(sql);
for (int i = 0; i < tb.Rows.Count; i++)
{
if (!string.IsNullOrEmpty(tb.Rows[i][0].ToString()))
{
dgv_allcourse["count_st", i].Value = tb.Rows[i][0];
}
else
{
dgv_allcourse["count_st", i].Value = 0;
}
}
//button
DataGridViewButtonColumn apply = new DataGridViewButtonColumn();
apply.Name = "apply_btn";
apply.HeaderText = "Alege materiile";
apply.Text = "Aplica";
apply.UseColumnTextForButtonValue = true;
dgv_allcourse.Columns.Add(apply);
}
}
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;
}
}
public void CreateTable(int type)
{
table = new DataTable();
switch (type)
{
case 1:
string sqlselect = string.Empty;
if (lista_modifica.SelectedItem.ToString() == lista_modifica.Items[0].ToString())
{
sqlselect = string.Format("SELECT IDProfesor,Nume,Prenume FROM Materii;");
table = MyData.readTable(sqlselect);
string sql = string.Format("SELECT ID_Materie,Nume_Materie FROM TabelMaterii;");
table_materii = MyData.readTable(sql);
table.Columns.Add("Materie");
for (int i = 0; i < table.Rows.Count; i++)
{
string materie_sql = string.Format("SELECT IDMaterie FROM Materii WHERE IDProfesor={0};", table.Rows[i][0]);
int materie_id = MyData.selectData(materie_sql, "IDMaterie");
for (int j = 0; j < table_materii.Rows.Count; j++)
{
if (Convert.ToInt32(table_materii.Rows[j][0]) == materie_id)
{
table.Rows[i]["Materie"] = table_materii.Rows[j][1];
}
}
}
}
else
{
sqlselect = string.Format("SELECT ID_Student,Nume,Prenume FROM Grades;");
table = MyData.readTable(sqlselect);
}
date_dgv.Columns.Clear();
date_dgv.DataSource = table;
DataGridViewButtonColumn btn = new DataGridViewButtonColumn();
btn.HeaderText = "Sterge " + lista_modifica.SelectedItem.ToString();
btn.Name = "delete_btn";
btn.Text = "Sterge";
btn.ValueType = typeof(Button);
btn.UseColumnTextForButtonValue = true;
date_dgv.Columns.Add(btn);
break;
case 2:
string sqlmodifica = string.Empty;
if (lista_modifica.SelectedItem.ToString() == lista_modifica.Items[0].ToString())
{
sqlmodifica = string.Format("SELECT Materii.IDProfesor,Materii.Nume,Materii.Prenume,TabelMaterii.Nume_Materie,Profesori.Nick,Profesori.Pass FROM Materii,TabelMaterii,Profesori WHERE Profesori.IDMaterie=TabelMaterii.ID_Materie AND Profesori.ID_Prof=Materii.IDProfesor;");
table = MyData.readTable(sqlmodifica);
modifica_dgv.Columns.Clear();
modifica_dgv.DataSource = table;
//Remodelate coloana 4 intr-o lista dropdown
DataGridViewComboBoxColumn list_materii = new DataGridViewComboBoxColumn();
list_materii.HeaderText = "Materie";
list_materii.Name = "lst_materii";
DataTable dt = getMaterii();
object[] obj = new object[dt.Rows.Count];
for (int i = 0; i < dt.Rows.Count; i++)
{
obj[i] = dt.Rows[i]["Nume_Materie"];
}
list_materii.Items.AddRange(obj);
modifica_dgv.Columns.RemoveAt(3);
modifica_dgv.Columns.Add(list_materii);
for (int i = 0; i < modifica_dgv.RowCount; i++)
{
modifica_dgv["lst_materii", i].Value = table.Rows[i]["Nume_Materie"];
}
//buton cu ce teste a adaugat
DataGridViewButtonColumn ts = new DataGridViewButtonColumn();
ts.Name = "teste_btn";
ts.HeaderText = "Teste adaugate";
ts.Text = "Editeaza";
ts.UseColumnTextForButtonValue = true;
ts.ValueType = typeof(Button);
modifica_dgv.Columns.Add(ts);
modifica_dgv.Columns["IDProfesor"].ReadOnly = true;
}
else
{
sqlmodifica = string.Format("SELECT Grades.ID_Student,Grades.Nume,Grades.Prenume,Studenti.Nick,Studenti.Parola,Grades.Materii_inscris FROM Studenti,Grades WHERE Grades.ID_Student=Studenti.ID_Student;");
table = MyData.readTable(sqlmodifica);
modifica_dgv.Columns.Clear();
modifica_dgv.DataSource = table;
modifica_dgv.Columns["Materii_inscris"].Name = "ID_Materii";
//buton teste windows
DataGridViewButtonColumn button = new DataGridViewButtonColumn();
button.Name = "teste_btn";
button.HeaderText = "Teste";
button.Text = "Vezi teste";
button.ValueType = typeof(Button);
button.UseColumnTextForButtonValue = true;
modifica_dgv.Columns.Add(button);
modifica_dgv.Columns["ID_Student"].ReadOnly = true;
}
break;
}
}
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);
}
}
}
}
