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;
}
}
//Student records Function, Crashes Program right now, needs to be fixed
private void metroButton5_Click(object sender, EventArgs e)
{
StudentsTBLTableAdapter students_Adapter = new StudentsTBLTableAdapter();
DataTable dt_Students = students_Adapter.GetDataByClassID((int)metroComboBox2.SelectedValue);
AttendanceTBLTableAdapter ada = new AttendanceTBLTableAdapter();
/*
* int P = 0;
* int A = 0;
* int L = 0;
* int E = 0;
*/
//loop databse and get values for PALE
//foreach (DataRow row in dt_Students.Rows)
// {
//present
//P = (int)ada.GetDataByReport(dateTimePicker2.Value.Month, row[1].ToString(), "present").Rows[0][6];
/*/absent
* 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];
* //excused
* E = (int)ada.GetDataByReport(dateTimePicker2.Value.Month,
* row[1].ToString(),
* "Excused").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 metroButtonGet_Click(object sender, EventArgs e)
{
//check if records exixts load em for edit if not create a record for each student and load for edit
AttendanceRecordTBLTableAdapter ada = new AttendanceRecordTBLTableAdapter();
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
{
//creatr a record for each student
//Get the class students list
StudentsTBLTableAdapter students_adapter = new StudentsTBLTableAdapter();
DataTable dt_Student = students_adapter.GetDataByClassID((int)metroComboBox1.SelectedValue);
foreach (DataRow row in dt_Student.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;
}
// TODO: This line of code loads data into the 'dataSet1.AttendanceRecordTBL' table. You can move, or remove it, as needed.
this.attendanceRecordTBLTableAdapter.Fill(this.dataSet1.AttendanceRecordTBL);
}
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);
}
}
//'Get Values' Button
private void metroButtonGet_Click(object sender, EventArgs e)
{
//Check if records exist, load for editing or create a record for each student if none exists
AttendanceTBLTableAdapter ada = new AttendanceTBLTableAdapter();
DataTable dt = ada.GetDataBy((int)metroComboBox1.SelectedValue, dateTimePicker1.Text);
if (dt.Rows.Count > 0)
{
//records Exist
DataTable dt_new = ada.GetDataBy((int)metroComboBox1.SelectedValue, dateTimePicker1.Text);
dataGridView1.DataSource = dt_new;
}
else
{
//create record for each
//get 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 new record
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;
}
/*TODO Delete, Probably not needed:
* this.attendanceTBLTableAdapter.Fill(this.dataSet1.AttendanceTBL); */
}
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 metroButton6_Click(object sender, EventArgs e)
{
//get students
StudentsTBLTableAdapter students_adapter = new StudentsTBLTableAdapter();
DataTable dt_Student = students_adapter.GetDataByClassID((int)metroComboBox2.SelectedValue);
AttendanceRecordTBLTableAdapter ada = new AttendanceRecordTBLTableAdapter();
int P = 0, A = 0, L = 0, E = 0;
//loop through students and get the values
foreach (DataRow row in dt_Student.Rows)
{
int x = dateTimePicker2.Value.Month;
string y = row[1].ToString();
/*
* P = (int)ada.GetDataByReport(dateTimePicker2.Value.Month, row[2].ToString(), "present").Rows[0][6];
*
* // Absence
* A = (int)ada.GetDataByReport(dateTimePicker2.Value.Month, row[2].ToString(), "absent").Rows[0][6];
*
* // late
* L = (int)ada.GetDataByReport(dateTimePicker2.Value.Month, row[2].ToString(), "late").Rows[0][6];
*
*
* //Execuse
* E = (int)ada.GetDataByReport(dateTimePicker2.Value.Month, row[2].ToString(), "execused").Rows[0][6];
*
*
*/
//Presence count
P = (int)ada.ScalarQuery(dateTimePicker2.Value.Month, row[1].ToString(), "present");
// Absence
A = (int)ada.ScalarQuery(dateTimePicker2.Value.Month, row[1].ToString(), "absence");
// late
A = (int)ada.ScalarQuery(dateTimePicker2.Value.Month, row[1].ToString(), "late");
//Execuse
A = (int)ada.ScalarQuery(dateTimePicker2.Value.Month, row[1].ToString(), "execuse");
/* //Presence count
* p = 1;
* dateTimePicker2.Value.Month, row[1].ToString(), "late"
* // Absence
* A = 1;
*
* // late
* L = 1;
*
*
* //Execuse
* E = 1;
*
*/
ListViewItem listitem = new ListViewItem();
listitem.Text = row[1].ToString();
listitem.SubItems.Add(P.ToString());
listitem.SubItems.Add(A.ToString());
listitem.SubItems.Add(L.ToString());
listitem.SubItems.Add(E.ToString());
listViewreport.Items.Add(listitem);
}
}
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);
}
}