private void metroButtonGetValues_Click(object sender, EventArgs e)
{
//check if records exist and if not create a record for each student and load for edit
AttendanceRecordsTBLTableAdapter ada = new AttendanceRecordsTBLTableAdapter();
DataTable dt = ada.GetDataBy((int)metroComboBox1.SelectedValue, dateTimePicker1.Text);
if (dt.Rows.Count > 0)
{
//we have records, so we can edit
DataTable dt_new = ada.GetDataBy((int)metroComboBox1.SelectedValue, dateTimePicker1.Text);
dataGridView1.DataSource = dt_new;
}
else
{
//create a record for each student
//Get the class students list
StudentsTBLTableAdapter students_adapter = new StudentsTBLTableAdapter();
DataTable dt_Students = students_adapter.GetDataByClassID((int)metroComboBox1.SelectedValue);
foreach (DataRow row in dt_Students.Rows)
{
//Insert a new Record for this student
ada.InsertQuery((int)row[0], (int)metroComboBox1.SelectedValue, dateTimePicker1.Text, row[1].ToString(), metroComboBox1.Text, "");
}
DataTable dt_new = ada.GetDataBy((int)metroComboBox1.SelectedValue, dateTimePicker1.Text);
dataGridView1.DataSource = dt_new;
}
}
private void BtnGetValues_Click(object sender, EventArgs e)
{
AttendanceRecordsTBLTableAdapter ada = new AttendanceRecordsTBLTableAdapter();
DataTable dt = ada.GetDataBy((int)SelectClassComboBox.SelectedValue,
SelectDateForClassDatePicker.Text);
if (dt.Rows.Count > 0)
{
DataTable dt_new = ada.GetDataBy((int)SelectClassComboBox.SelectedValue,
SelectDateForClassDatePicker.Text);
dataGridView1.DataSource = dt_new;
}
else
{
StudentsTBLTableAdapter students = new StudentsTBLTableAdapter();
DataTable dt_Students = students.GetDataByClassId((int)SelectClassComboBox.SelectedValue);
foreach (DataRow row in dt_Students.Rows)
{
ada.InsertQuery((int)row[0], (int)SelectClassComboBox.SelectedValue, SelectDateForClassDatePicker.Text
, "", row[1].ToString(), SelectClassComboBox.SelectedValue.ToString());
}
DataTable dt_new = ada.GetDataBy((int)SelectClassComboBox.SelectedValue,
SelectDateForClassDatePicker.Text);
dataGridView1.DataSource = dt_new;
}
}
private void metroButtonSave_Click(object sender, EventArgs e)
{
AttendanceRecordsTBLTableAdapter ada = new AttendanceRecordsTBLTableAdapter();
foreach (DataGridViewRow row in dataGridView1.Rows)
{
if (row.Cells[1].Value != null)
{
ada.UpdateQuery(row.Cells[1].Value.ToString(), row.Cells[0].Value.ToString(), (int)metroComboBox1.SelectedValue, dateTimePicker1.Text);
}
}
}
private void BtnGetValuesInReportSection_Click(object sender, EventArgs e)
{
try
{
StudentsTBLTableAdapter students = new StudentsTBLTableAdapter();
DataTable dt_Students = students.GetDataByClassId((int)SelectClassComboBoxInReportSection.SelectedValue);
AttendanceRecordsTBLTableAdapter ada = new AttendanceRecordsTBLTableAdapter();
int presentStudent = 0;
int absentStudent = 0;
int lateStudent = 0;
int execuseStudent = 0;
foreach (DataRow row in dt_Students.Rows)
{
//present count
presentStudent = (int)ada.GetDataByReport(SelectDateForClassDatePicker.Value.Month,
row[1].ToString(), "present").Rows[0][6];
//absent count
absentStudent = (int)ada.GetDataByReport(SelectDateForClassDatePicker.Value.Month,
row[1].ToString(), "present").Rows[0][6];
//late count
lateStudent = (int)ada.GetDataByReport(SelectDateForClassDatePicker.Value.Month,
row[1].ToString(), "present").Rows[0][6];
//Execuse count
execuseStudent = (int)ada.GetDataByReport(SelectDateForClassDatePicker.Value.Month,
row[1].ToString(), "present").Rows[0][6];
ListViewItem listItem = new ListViewItem();
listItem.Text = row[1].ToString();
listItem.SubItems.Add(presentStudent.ToString());
listItem.SubItems.Add(absentStudent.ToString());
listItem.SubItems.Add(lateStudent.ToString());
listItem.SubItems.Add(execuseStudent.ToString());
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message + " " + ex.StackTrace);
}
}
private void BtnClear_Click(object sender, EventArgs e)
{
AttendanceRecordsTBLTableAdapter ada = new AttendanceRecordsTBLTableAdapter();
foreach (DataGridViewRow row in dataGridView1.Rows)
{
if (row.Cells[1].Value != null)
{
ada.UpdateQuery("", row.Cells[0].Value.ToString(),
(int)SelectClassComboBox.SelectedValue, Convert.ToDateTime(SelectDateForClassDatePicker.Text));
}
}
DataTable dt_new = ada.GetDataBy((int)SelectClassComboBox.SelectedValue,
SelectDateForClassDatePicker.Text);
dataGridView1.DataSource = dt_new;
}
private void metroButton1_Click(object sender, EventArgs e)
{
// get students
StudentsTBLTableAdapter students_adapter = new StudentsTBLTableAdapter();
DataTable dt_Students = students_adapter.GetDataByClassID((int)metroComboBox2.SelectedValue);
AttendanceRecordsTBLTableAdapter ada = new AttendanceRecordsTBLTableAdapter();
int p = 0;
int A = 0;
int L = 0;
int E = 0;
//loop through students and get the values
foreach (DataRow row in dt_Students.Rows)
{
//Presence count
p = (int)ada.GetDataByReport(dateTimePicker2.Value.Month, row[1].ToString(), "present").Rows[0][6];
// Absence
A = (int)ada.GetDataByReport(dateTimePicker2.Value.Month, row[1].ToString(), "absent").Rows[0][6];
// late
L = (int)ada.GetDataByReport(dateTimePicker2.Value.Month, row[1].ToString(), "late").Rows[0][6];
//Execuse
E = (int)ada.GetDataByReport(dateTimePicker2.Value.Month, row[1].ToString(), "execused").Rows[0][6];
ListViewItem litem = new ListViewItem();
litem.Text = row[1].ToString();
litem.SubItems.Add(p.ToString());
litem.SubItems.Add(A.ToString());
litem.SubItems.Add(L.ToString());
litem.SubItems.Add(E.ToString());
listView1.Items.Add(litem);
}
}
private void BtnSave_Click(object sender, EventArgs e)
{
try
{
AttendanceRecordsTBLTableAdapter ada = new AttendanceRecordsTBLTableAdapter();
foreach (DataGridViewRow row in dataGridView1.Rows)
{
if (row.Cells[1].Value != null)
{
ada.UpdateQuery(row.Cells[1].Value.ToString(), row.Cells[0].Value.ToString(),
(int)SelectClassComboBox.SelectedValue, Convert.ToDateTime(SelectDateForClassDatePicker.Text));
}
}
DataTable dt_new = ada.GetDataBy((int)SelectClassComboBox.SelectedValue,
SelectDateForClassDatePicker.Text);
dataGridView1.DataSource = dt_new;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message + "\n" + ex.StackTrace);
}
}
private void metroButtonGet_Click(object sender, EventArgs e)
{
AttendanceRecordsTBLTableAdapter ada = new AttendanceRecordsTBLTableAdapter();
DataTable dt = ada.GetDataBy((int)metroComboBox1.SelectedValue, dateTimePicker1.Text);
if (dt.Rows.Count > 0)
{
DataTable dt_new = ada.GetDataBy((int)metroComboBox1.SelectedValue, dateTimePicker1.Text);
dataGridView1.DataSource = dt_new;
}
else
{
StudentsTBLTableAdapter students_adapter = new StudentsTBLTableAdapter();
DataTable dt_Students = students_adapter.GetDataByClassID((int)metroComboBox1.SelectedValue);
foreach (DataRow row in dt_Students.Rows)
{
ada.InsertQuery((int)row[0], (int)metroComboBox1.SelectedValue, dateTimePicker1.Text, "", row[1].ToString(), metroComboBox1.Text);
}
DataTable dt_new = ada.GetDataBy((int)metroComboBox1.SelectedValue, dateTimePicker1.Text);
dataGridView1.DataSource = dt_new;
}
}
private void metroButton1_Click(object sender, EventArgs e)
{
//get students
StudentsTBLTableAdapter students_adapter = new StudentsTBLTableAdapter();
DataTable dt_Students = students_adapter.GetDataByClassID((int)metroComboBox2.SelectedValue);
AttendanceRecordsTBLTableAdapter ada = new AttendanceRecordsTBLTableAdapter();
int p = 0;
int a = 0;
int l = 0;
int ex = 0;
string connectionString = @"Data Source=LAPTOP-1QRG78IU; Initial Catalog=AttendanceDB;integrated security=SSPI";
conn = new SqlConnection(connectionString);
//loop through students and get the values
foreach (DataRow row in dt_Students.Rows)
{
string query = string.Format("SELECT COUNT(Status) FROM AttendanceRecordsTBL WHERE MONTH(DateAttendance) = {0} AND StudentName = '{1}' AND Status = 'p'", dateTimePicker2.Value.Month, row[1].ToString());
cmd = new SqlCommand(query, conn);
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
p = Convert.ToInt32(dr[0].ToString());
}
conn.Close();
query = string.Format("SELECT COUNT(Status) FROM AttendanceRecordsTBL WHERE MONTH(DateAttendance) = {0} AND StudentName = '{1}' AND Status = 'a'", dateTimePicker2.Value.Month, row[1].ToString());
cmd = new SqlCommand(query, conn);
conn.Open();
dr = cmd.ExecuteReader();
if (dr.Read())
{
a = Convert.ToInt32(dr[0].ToString());
}
conn.Close();
query = string.Format("SELECT COUNT(Status) FROM AttendanceRecordsTBL WHERE MONTH(DateAttendance) = {0} AND StudentName = '{1}' AND Status = 'l'", dateTimePicker2.Value.Month, row[1].ToString());
cmd = new SqlCommand(query, conn);
conn.Open();
dr = cmd.ExecuteReader();
if (dr.Read())
{
l = Convert.ToInt32(dr[0].ToString());
}
conn.Close();
query = string.Format("SELECT COUNT(Status) FROM AttendanceRecordsTBL WHERE MONTH(DateAttendance) = {0} AND StudentName = '{1}' AND Status = 'e'", dateTimePicker2.Value.Month, row[1].ToString());
cmd = new SqlCommand(query, conn);
conn.Open();
dr = cmd.ExecuteReader();
if (dr.Read())
{
ex = Convert.ToInt32(dr[0].ToString());
}
conn.Close();
ListViewItem litem = new ListViewItem();
litem.Text = row[1].ToString();
litem.SubItems.Add(p.ToString());
litem.SubItems.Add(a.ToString());
litem.SubItems.Add(l.ToString());
litem.SubItems.Add(ex.ToString());
listView1.Items.Add(litem);
}
}
