private void in_btn_Click(object sender, EventArgs e)
{
DBConnection con = new DBConnection();
int personal = con.Counter($"SELECT user FROM personal WHERE user='******'");
int student = con.Counter($"SELECT student_id FROM student WHERE student_id='{txtbx.Text}'");
if (txtbx.Text == "")
{
MessageBox.Show("Missing info.");
}
else
{
if (personal_rb.Checked == true)
{
if (personal > 0)
{
try
{
string date = DateTime.Now.ToString("yyyy/MM/dd H:mm:ss");
string id = con.Reader($"SELECT personal_id FROM personal WHERE user = '******'", "personal_id");
string query = $"INSERT INTO states(personal, id, state, date)" +
$"VALUES('1', '{id}', 'in', '{date}')";
con.Insert(query);
this.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
else
{
MessageBox.Show("INCORRECT ENTRY");
}
}
else
{
if (student > 0)
{
try
{
string date = DateTime.Now.ToString("yyyy/MM/dd H:mm:ss");
string id = con.Reader($"SELECT student_id FROM student WHERE student_id = '{txtbx.Text}'", "student_id");
string query = $"INSERT INTO states(personal, id, state, date)" +
$"VALUES('0', '{id}', 'in', '{date}')";
con.Insert(query);
this.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
else
{
MessageBox.Show("INCORRECT ENTRY");
}
}
}
}
private void add_room_btn_Click(object sender, EventArgs e)
{
this.Hide();
room_mod addNewRoom = new room_mod();
addNewRoom.ShowDialog();
this.Show();
DBConnection con = new DBConnection();
string myQuery = $"SELECT room_id FROM room";
con.Reader(myQuery, "room_id", room_combx);
myQuery = $"SELECT room_id FROM room ORDER BY room_id DESC LIMIT 1";
room_combx.SelectedItem = con.Reader(myQuery, "room_id");
}
private void add_dep_btn_Click(object sender, EventArgs e)
{
this.Hide();
dep_mod addNewDep = new dep_mod();
addNewDep.ShowDialog();
this.Show();
DBConnection con = new DBConnection();
string myQuery = $"SELECT name FROM department";
con.Reader(myQuery, "name", dep_combx);
myQuery = $"SELECT name FROM department ORDER BY dep_id DESC LIMIT 1";
dep_combx.SelectedItem = con.Reader(myQuery, "name");
}
private void add_job_btn_Click(object sender, EventArgs e)
{
this.Hide();
job_mod addNewJob = new job_mod();
addNewJob.ShowDialog();
this.Show();
DBConnection con = new DBConnection();
string myQuery = $"SELECT position FROM job";
con.Reader(myQuery, "position", job_combx);
myQuery = $"SELECT position FROM job ORDER BY job_id DESC LIMIT 1";
job_combx.SelectedItem = con.Reader(myQuery, "position");
salary_txtbx.Text = "";
}
private void room_form_Load(object sender, EventArgs e)
{
DBConnection con = new DBConnection();
con.Reader("SELECT room_id FROM room", "room_id", id_combx);
refresh();
current_page = 1;
if (current_page == last_page)
{
prev_btn.Enabled = true;
next_btn.Enabled = false;
}
if (current_page == 1)
{
prev_btn.Enabled = false;
}
}
private void add_btn_Click(object sender, EventArgs e)
{
DBConnection con = new DBConnection();
string name = name_txtbx.Text.ToString();
string surname = surname_txtbx.Text.ToString();
string tc = tc_txtbx.Text.ToString();
string address = address_txtbx.Text.ToString();
string email = email_txtbx.Text.ToString();
string tel = tel_txtbx.Text.ToString();
string reg_date = reg_date_dtp.Text.ToString();
string sclass = sclass_combx.Text.ToString();
string room_id = room_combx.Text.ToString();
string department = dep_combx.Text.ToString();
string dep_id = con.Reader($"SELECT dep_id FROM department WHERE name = '{department}'", "dep_id");
if (id != "-1")
{
string query = $"UPDATE student SET " +
$"name='{name}', " +
$"surname='{surname}', " +
$"tc='{tc}', " +
$"address='{address}', " +
$"email='{email}', " +
$"tel='{tel}', " +
$"reg_date='{reg_date}', " +
$"class='{sclass}', " +
$"room_id='{room_id}', " +
$"dep_id='{dep_id}' " +
$"WHERE student_id = '{id_txtbx.Text.ToString()}'";
if (name_txtbx.Text != "" && surname_txtbx.Text != "" && tc_txtbx.Text != "" &&
reg_date_dtp.Text != "" && sclass_combx.SelectedIndex > -1 && room_combx.SelectedIndex > -1 &&
dep_combx.SelectedIndex > -1)
{
try
{
con.Update(query);
this.Close();
}
catch (Exception ex)
{
MessageBox.Show("Error: " + ex);
}
finally
{
id_txtbx.Text = "";
name_txtbx.Text = "";
surname_txtbx.Text = "";
tc_txtbx.Text = "";
address_txtbx.Text = "";
email_txtbx.Text = "";
tel_txtbx.Text = "";
sclass_combx.SelectedIndex = -1;
room_combx.SelectedIndex = -1;
dep_combx.SelectedIndex = -1;
}
}
}
else
{
string query = $"INSERT INTO student(name, surname, tc, address, email, tel, reg_date, class, room_id, dep_id) " +
$"VALUES('{name}', '{surname}', '{tc}', '{address}', '{email}', '{tel}', '{reg_date}', '{sclass}', '{room_id}', '{dep_id}')";
if (name_txtbx.Text != "" && surname_txtbx.Text != "" && tc_txtbx.Text != "" &&
reg_date_dtp.Text != "" && sclass_combx.SelectedIndex > -1 && room_combx.SelectedIndex > -1 &&
dep_combx.SelectedIndex > -1)
{
try
{
con.Insert(query);
this.Close();
}
catch (Exception ex)
{
MessageBox.Show("Error: " + ex);
}
finally
{
id_txtbx.Text = "";
name_txtbx.Text = "";
surname_txtbx.Text = "";
tc_txtbx.Text = "";
address_txtbx.Text = "";
email_txtbx.Text = "";
tel_txtbx.Text = "";
sclass_combx.SelectedIndex = -1;
room_combx.SelectedIndex = -1;
dep_combx.SelectedIndex = -1;
}
}
else
{
MessageBox.Show("Missing Data", "Critical Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
}
private void student_mod_form_Load(object sender, EventArgs e)
{
DBConnection con = new DBConnection();
string query = "SELECT room.room_id, IF((room.capacity - count(student.name) = 0) , (\"FULL\"),(\"Not FULL\")) AS 'Room State' FROM student RIGHT OUTER JOIN room ON room.room_id = student.room_id GROUP BY student.room_id";
con.roomCheck(query, "room_id", room_combx);
/*
* for (int i = 0; i < room_combx.Items.Count; i++)
* {
* if (con.Counter($"SELECT student_id FROM student WHERE room_id ='{room_combx.Items[i]}'")
* .ToString() == con.Reader($"SELECT * FROM room WHERE room_id =" +
* $"'{room_combx.Items[i]}'", "capacity"))
* {
* room_combx.Items.RemoveAt(i);
* if (i != room_combx.Items.Count)
* {
* i--;
* }
* }
* }*/
query = "SELECT * FROM department";
con.Reader(query, "name", dep_combx);
id_txtbx.Text = id;
if (id != "-1")
{
id_txtbx.Text = id;
string myQuery = $"SELECT * FROM student WHERE student_id = '{id}'";
name_txtbx.Text = con.Reader(myQuery, "name");
surname_txtbx.Text = con.Reader(myQuery, "surname");
tc_txtbx.Text = con.Reader(myQuery, "tc");
address_txtbx.Text = con.Reader(myQuery, "address");
tel_txtbx.Text = con.Reader(myQuery, "tel");
sclass_combx.SelectedItem = con.Reader(myQuery, "class");
email_txtbx.Text = con.Reader(myQuery, "email");
if (room_combx.Items.Contains(con.Reader(myQuery, "room_id")))
{
//do nothing
}
else
{
room_combx.Items.Add(con.Reader(myQuery, "room_id"));
}
room_combx.SelectedItem = con.Reader(myQuery, "room_id");
string dep_id = con.Reader(myQuery, "dep_id");
//myQuery = $"SELECT name FROM department WHERE dep_id = (SELECT dep_id FROM student WHERE student_id = {id})";
myQuery = $"SELECT name FROM department WHERE dep_id = '{dep_id}'";
dep_combx.SelectedItem = con.Reader(myQuery, "name");
myQuery = $"SELECT DATE_FORMAT(reg_date, '%Y/%m/%d') AS reg_date FROM student WHERE student_id = {id}";
string myDate = con.Reader(myQuery, "reg_date");
DateTime Date = Convert.ToDateTime(myDate);
this.reg_date_dtp.Value = Date;
}
else
{
id_txtbx.Text = "";
this.reg_date_dtp.Value = DateTime.Now;
}
}
private void add_btn_Click(object sender, EventArgs e)
{
DBConnection con = new DBConnection();
string name = name_txtbx.Text.ToString();
string surname = surname_txtbx.Text.ToString();
string tc = tc_txtbx.Text.ToString();
string address = address_txtbx.Text.ToString();
string email = email_txtbx.Text.ToString();
string tel = tel_txtbx.Text.ToString();
string user = user_txtbx.Text.ToString();
string pass = pass_txtbx.Text.ToString();
string job = job_combx.Text.ToString();
string job_id = con.Reader($"SELECT job_id FROM job WHERE position = '{job}'", "job_id");
string salary = salary_txtbx.Text.ToString();
string sdate = sdate_dtp.Text.ToString();
string fdate;
if (fdate_dtp.Checked == false)
{
fdate = "NULL";
}
else
{
fdate = "'" + fdate_dtp.Text.ToString() + "'";
}
string admin;
if (admin_chkbx.Checked == true)
{
admin = "1";
}
else
{
admin = "0";
}
if (id != "-1") // so thats an edit proccess
{
string query = $"UPDATE personal SET " +
$"name='{name}', " +
$"surname='{surname}', " +
$"tc='{tc}', " +
$"address='{address}', " +
$"email='{email}', " +
$"tel='{tel}', " +
$"pass='******', " +
$"start_date='{sdate}', " +
$"finish_date={fdate}, " +
$"job_id='{job_id}', " +
$"admin='{admin}' " +
$"WHERE personal_id = '{id_txtbx.Text.ToString()}'";
if (name_txtbx.Text != "" && surname_txtbx.Text != "" && tc_txtbx.Text != "" &&
user_txtbx.Text != "" && pass_txtbx.Text != "" && job_combx.SelectedIndex > -1 &&
sdate_dtp.Text != "" && salary_txtbx.Text != "")
{
try
{
con.Update(query);
try
{
con.Delete("delete_salary", id);
}
catch (Exception ex) { };
//con.Update($"UPDATE salary SET personal_id = '{id}', job_id = '{job_id}', amount = '{salary}'");
con.Insert($"INSERT INTO salary(personal_id, job_id, amount) VALUES('{id}', '{job_id}', '{salary}')");
this.Close();
}
catch (Exception ex)
{
MessageBox.Show("Error: " + ex);
}
finally
{
id_txtbx.Text = "";
name_txtbx.Text = "";
surname_txtbx.Text = "";
tc_txtbx.Text = "";
address_txtbx.Text = "";
email_txtbx.Text = "";
tel_txtbx.Text = "";
user_txtbx.Text = "";
pass_txtbx.Text = "";
job_combx.SelectedIndex = -1;
}
}
}
else
{
string query = $"INSERT INTO personal(name, surname, tc, address, email, tel, user, pass, start_date, finish_date, job_id, admin) " +
$"VALUES('{name}', '{surname}', '{tc}', '{address}', '{email}', '{tel}', '{user}', '{pass}', '{sdate}', {fdate}, '{job_id}', '{admin}')";
if (name_txtbx.Text != "" && surname_txtbx.Text != "" && tc_txtbx.Text != "" &&
user_txtbx.Text != "" && pass_txtbx.Text != "" && job_combx.SelectedIndex > -1 &&
sdate_dtp.Text != "" && salary_txtbx.Text != "")
{
try
{
con.Insert(query);
string personal_id = con.Reader($"SELECT personal_id FROM personal WHERE user = '******'", "personal_id");
con.Insert($"INSERT INTO salary(personal_id, job_id, amount) VALUES('{personal_id}', '{job_id}', '{salary}')");
this.Close();
}
catch (Exception ex)
{
MessageBox.Show("Error: " + ex);
}
finally
{
id_txtbx.Text = "";
name_txtbx.Text = "";
surname_txtbx.Text = "";
tc_txtbx.Text = "";
address_txtbx.Text = "";
email_txtbx.Text = "";
tel_txtbx.Text = "";
user_txtbx.Text = "";
pass_txtbx.Text = "";
job_combx.SelectedIndex = -1;
}
}
else
{
MessageBox.Show("Missing Data", "Critical Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
}
private void add_form_Load(object sender, EventArgs e)
{
DBConnection con = new DBConnection();
string query = "SELECT * FROM job";
con.Reader(query, "position", job_combx);
id_txtbx.Text = id;
if (id != "-1")
{
id_txtbx.Text = id;
string myQuery = $"SELECT * FROM personal WHERE personal_id = '{id}'";
name_txtbx.Text = con.Reader(myQuery, "name");
surname_txtbx.Text = con.Reader(myQuery, "surname");
tc_txtbx.Text = con.Reader(myQuery, "tc");
address_txtbx.Text = con.Reader(myQuery, "address");
tel_txtbx.Text = con.Reader(myQuery, "tel");
user_txtbx.Text = con.Reader(myQuery, "user");
pass_txtbx.Text = con.Reader(myQuery, "pass");
email_txtbx.Text = con.Reader(myQuery, "email");
string admin;
if (con.Reader(myQuery, "admin") != "True")
{
admin_chkbx.Checked = false;
admin = "0";
}
else
{
admin_chkbx.Checked = true;
admin = "1";
}
string job_id = con.Reader(myQuery, "job_id");
myQuery = $"SELECT position FROM job WHERE job_id = '{job_id}'";
job_combx.SelectedItem = con.Reader(myQuery, "position");
myQuery = $"SELECT amount FROM salary WHERE job_id = '{job_id}' AND personal_id = '{id}'";
salary_txtbx.Text = con.Reader(myQuery, "amount");
myQuery = $"SELECT DATE_FORMAT(start_date, '%Y/%m/%d') AS sdate, DATE_FORMAT(finish_date, '%Y-%m-%d') AS fdate FROM personal WHERE personal_id = {id}";
string myDate = con.Reader(myQuery, "sdate");
DateTime Date = Convert.ToDateTime(myDate);
this.sdate_dtp.Value = Date;
myDate = con.Reader(myQuery, "fdate");
if (myDate == "")
{
Date = DateTime.Now;
this.fdate_dtp.Value = Date;
this.fdate_dtp.Checked = false;
}
else
{
Date = Convert.ToDateTime(myDate);
this.fdate_dtp.Value = Date;
}
}
else
{
id_txtbx.Text = "";
this.sdate_dtp.Value = DateTime.Now;
this.fdate_dtp.Value = DateTime.Now;
this.fdate_dtp.Checked = false;
}
}