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 search_btn_Click(object sender, EventArgs e)
{
string id = id_combx.Text.ToString();
if (id_combx.Text != "")
{
DBConnection con = new DBConnection();
query = $"SELECT " +
$"room.room_id AS 'Room NO', " +
$"GROUP_CONCAT(CONCAT(student.name, ' ',student.surname) SEPARATOR ', ') AS Students, " +
$"count(student.name) AS 'Number of Student', " +
$"room.capacity AS Capacity,(room.capacity - count(student.name)) AS 'Left Places'," +
$"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 room.room_id HAVING room.room_id = {id} " +
$"ORDER BY room.room_id ";
this.last_page = (con.Counter(query) / limit) + 1;
this.page_query = query + $" LIMIT {(current_page - 1) * limit}, {limit}";
pages_lbl.Text = $"{current_page} / {last_page}";
data_dgv.DataSource = con.SetDS(page_query).Tables[0];
//to set last column to fill all width
try
{
this.data_dgv.Columns[data_dgv.Columns.Count - 1].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
}
catch (Exception ex) { }
}
}
public void paging(string query)
{
DBConnection con = new DBConnection();
limit = Int32.Parse(results_combx.Text);
if ((float)con.Counter(query) / limit == con.Counter(query) / limit)
{
this.last_page = (con.Counter(query) / limit);
}
else
{
this.last_page = (con.Counter(query) / limit) + 1;
}
this.page_query = query + $" LIMIT {(current_page - 1) * limit}, {limit}";
pages_lbl.Text = $"{current_page} / {last_page}";
}
private void search_btn_Click(object sender, EventArgs e)
{
string search = search_txtbx.Text.ToString();
string by = by_combx.Text.ToString();
if (search != "" && by_combx.Text != "")
{
DBConnection con = new DBConnection();
query = $"SELECT states.id AS ID, CONCAT(personal.name, ' ', personal.surname) AS 'Full Name', UPPER(states.state) AS 'IN / OUT', DATE_FORMAT(date, {date_format}) AS 'Time' FROM states, personal WHERE states.personal = '1' AND states.id = personal.personal_id AND CONCAT(personal.name, ' ', personal.surname) REGEXP '{search}' ORDER BY date DESC ";
this.last_page = (con.Counter(query) / limit) + 1;
this.page_query = query + $" LIMIT {(current_page - 1) * limit}, {limit}";
pages_lbl.Text = $"{current_page} / {last_page}";
data_dgv.DataSource = con.SetDS(page_query).Tables[0];
//to set last column to fill all width
try
{
this.data_dgv.Columns[data_dgv.Columns.Count - 1].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
}
catch (Exception ex) { }
}
}
private void search_btn_Click(object sender, EventArgs e)
{
string search = search_txtbx.Text.ToString();
if (search != "")
{
DBConnection con = new DBConnection();
query = $"SELECT student.student_id As ID ,CONCAT(student.name,' ',student.surname) AS 'Full Name' ,SUM(fees.paid) AS PAID, (fees.amount - SUM(fees.paid)) AS 'LEFT' FROM fees INNER JOIN student on student.student_id = fees.student_id GROUP BY fees.student_id HAVING student.student_id REGEXP '{search}'";
//query = $"SELECT student_id AS ID, CONCAT(name, ' ', surname) AS Name, tc, reg_date AS 'Reg. Date' FROM student WHERE student_id REGEXP '{search}'";
this.last_page = (con.Counter(query) / limit) + 1;
this.page_query = query + $" LIMIT {(current_page - 1) * limit}, {limit}";
pages_lbl.Text = $"{current_page} / {last_page}";
data_dgv.DataSource = con.SetDS(page_query).Tables[0];
//to set last column to fill all width
try
{
this.data_dgv.Columns[data_dgv.Columns.Count - 1].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
id = this.data_dgv.Rows[0].Cells[0].Value.ToString();
}
catch (Exception ex) { }
}
}
private void search_btn_Click(object sender, EventArgs e)
{
string search = search_txtbx.Text.ToString();
string by = by_combx.Text.ToString();
if (search != "" && by_combx.Text != "")
{
DBConnection con = new DBConnection();
query = $"SELECT personal.personal_id AS ID, CONCAT(personal.name,' ', personal.surname) AS 'Full Name', tc AS TC, job.position AS Jop, personal.admin AS Admin ,salary.amount/12 AS 'Monthly Salary' FROM personal INNER JOIN job ON personal.job_id = job.job_id INNER JOIN salary on (job.job_id= salary.job_id AND personal.personal_id = salary.personal_id) WHERE {by} REGEXP '{search}' GROUP BY personal.personal_id";
this.last_page = (con.Counter(query) / limit) + 1;
this.page_query = query + $" LIMIT {(current_page - 1) * limit}, {limit}";
pages_lbl.Text = $"{current_page} / {last_page}";
data_dgv.DataSource = con.SetDS(page_query).Tables[0];
//to set last column to fill all width
try
{
this.data_dgv.Columns[data_dgv.Columns.Count - 1].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
id = this.data_dgv.Rows[0].Cells[0].Value.ToString();
}
catch (Exception ex) { }
}
}
private void search_btn_Click(object sender, EventArgs e)
{
string search = search_txtbx.Text.ToString();
string by = by_combx.Text.ToString();
if (search != "" && by_combx.Text != "")
{
DBConnection con = new DBConnection();
query = $"SELECT student.student_id AS ID, CONCAT(student.name,' ',student.surname)AS 'Full Name', student.tc AS TC,student.reg_date AS Date, room.room_id AS 'Room NO' ,room.capacity AS Capacity, department.name AS Department, student.class AS Class FROM student INNER JOIN room ON student.room_id = room.room_id INNER JOIN department ON student.dep_id = department.dep_id WHERE student.{by} REGEXP '{search}'";
//query = $"SELECT student_id AS ID, CONCAT(name, ' ', surname) AS Name, tc, reg_date AS 'Reg. Date' FROM student WHERE {by} REGEXP '{search}'";
this.last_page = (con.Counter(query) / limit) + 1;
this.page_query = query + $" LIMIT {(current_page - 1) * limit}, {limit}";
pages_lbl.Text = $"{current_page} / {last_page}";
data_dgv.DataSource = con.SetDS(page_query).Tables[0];
//to set last column to fill all width
try
{
this.data_dgv.Columns[data_dgv.Columns.Count - 1].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
id = this.data_dgv.Rows[0].Cells[0].Value.ToString();
}
catch (Exception ex) { }
}
}
private void ok_btn_Click(object sender, EventArgs e)
{
DBConnection con = new DBConnection();
string department = dep_txtbx.Text.ToString();
string query = $"INSERT INTO department(name) VALUES('{department}')";
if (dep_txtbx.Text != "")
{
try
{
if (con.Counter($"SELECT * FROM department WHERE name='{department}'") <= 0)
{
con.Insert(query);
this.Close();
}
else
{
MessageBox.Show("Already Available !");
}
}
catch (Exception ex)
{
MessageBox.Show("Error: " + ex);
}
finally
{
dep_txtbx.Text = "";
}
}
else
{
MessageBox.Show("Missing Data", "Critical Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}