private void tabControl1_SelectedIndexChanged(object sender, EventArgs e)
{
if (tabControl1.SelectedIndex == 2 && teste_dgv.Columns.Count <= 4 && teste_dgv.Rows.Count > 0)
{
int value = Convert.ToInt32(teste_dgv["ID_Prof", 0].Value);
string sql = string.Format("SELECT IDMaterie FROM Profesori WHERE ID_Prof={0};", value);
int ID = MyData.selectData(sql, "IDMaterie");
sql = string.Format("SELECT Nume,Prenume FROM Materii WHERE IDProfesor={0} AND IDMaterie={1};", value, ID);
teste_dgv.Columns.Add("name", "Nume Profesor");
teste_dgv.Columns.Add("prename", "Prenume Profesor");
string[] data = MyData.getData(sql, new string[] { "Nume", "Prenume" });
for (int i = 0; i < teste_dgv.Rows.Count; i++)
{
teste_dgv["name", i].Value = data[0];
teste_dgv["prename", i].Value = data[1];
}
DataGridViewButtonColumn button = new DataGridViewButtonColumn();
button.HeaderText = "Test";
button.ValueType = typeof(Button);
button.Text = "Start";
button.UseColumnTextForButtonValue = true;
teste_dgv.Columns.Add(button);
teste_dgv.Columns.RemoveAt(3);
}
else if (tabControl1.SelectedIndex == 2 && teste_dgv.Rows.Count <= 0 || tabControl1.SelectedIndex == 1 && mycourse_dgv.Rows.Count <= 0 || tabControl1.SelectedIndex == 3 && materii_cb.Items.Count <= 0)
{
MessageBox.Show("Nu ai nici un test disponibil sau nu esti inscris la nici un curs.", "Instintare", MessageBoxButtons.OK, MessageBoxIcon.Information);
tabControl1.SelectTab(0);
}
KeepTrakingName();
}
public int getStudentID()
{
if (nume_cmb.Items.Count > 0)
{
string[] sr = nume_cmb.SelectedItem.ToString().Split(' ');
string sql = string.Format("SELECT ID_Student FROM Grades WHERE ID_Profesor LIKE '%{0}%' AND Nume='{1}' AND Prenume='{2}';", Main_form.id_user, sr[0], sr[1]);
int data = MyData.selectData(sql, "ID_Student");
return(data);
}
return(0);
}
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 getData()
{
DataTable dt;
string sql = string.Empty;
switch (type)
{
case 0:
sql = string.Format("SELECT ID_Test,Nume_fisier,Nota,Nota_finala FROM Teste_Materii WHERE ID_Student={0} AND Materie='{1}';", data[2], box_materii.SelectedItem.ToString());
test_lbl.Text = $"Notele elevului {data[0]} {data[1]}";
dt = MyData.readTable(sql);
lista_dgv.DataSource = dt;
lista_dgv.Columns["Nota_finala"].ReadOnly = true;
test_lbl.TextAlign = ContentAlignment.TopCenter;
break;
case 1:
lista_dgv.Columns.Clear();
string[] correct_name = box_materii.SelectedItem.ToString().Split(' ');
string sql_materii = string.Format("SELECT ID_Student FROM Grades WHERE ID_Profesor LIKE '%{0}%' AND Nume='{1}' AND Prenume='{2}';", data[3], correct_name[0], correct_name[1]);
int id_student = MyData.selectData(sql_materii, "ID_Student");
sql = string.Format("SELECT ID_Test,Nume_fisier FROM Teste_Materii WHERE ID_Student={0} AND Materie='{1}';", id_student, data[2]);
test_lbl.Text = $"Modifica testele profesorului {data[0]} {data[1]}";
dt = MyData.readTable(sql);
lista_dgv.DataSource = dt;
DataGridViewButtonColumn btn = new DataGridViewButtonColumn();
btn.Name = "change";
btn.HeaderText = "Modifica testul";
btn.ValueType = typeof(Button);
btn.Text = "Modifica";
btn.UseColumnTextForButtonValue = true;
lista_dgv.Columns.Add(btn);
test_lbl.TextAlign = ContentAlignment.TopLeft;
break;
}
lista_dgv.Columns["ID_Test"].ReadOnly = true;
lista_dgv.Columns["Nume_fisier"].ReadOnly = true;
}
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 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);
}
}
}
}