07-05-2011, 02:55 PM
Attendance .cs
Code:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Collections;
using System.Data.OleDb;
namespace SchoolManagementSystem
{
public partial class Attendance1 : Form
{
OleDbConnection cn = new OleDbConnection(ConnectionStringForSMS.MyConnectionString());
int classid, sectionid;
string AttandanceTime = null,day=null,med=null,cc=null,ss=null;
public Attendance1()
{
InitializeComponent();
}
private void Attendance1_Load(object sender, EventArgs e)
{
OleDbDataAdapter da23 = new OleDbDataAdapter("select schooltitle,Address from schooltitle", cn);
DataSet ds23 = new DataSet();
da23.Fill(ds23);
string schooltitle = ds23.Tables[0].Rows[0].ItemArray[0].ToString();
string address = ds23.Tables[0].Rows[0].ItemArray[1].ToString();
this.Text = schooltitle + "- Day Attendance";
label7.Text = "";
label7.Text = schooltitle;
panel1.Visible = false;
btnGetDetails.Visible = false;
btnSubmit.Visible = false;
AttandanceTime = "AM";
day = "ToDay";
dateTimePicker1.Visible = false;
cmbMedium.Focus();
med = "medium";
cmbMedium.SelectedIndex = 0;
}
private void cmbSection_SelectedIndexChanged1(object sender, EventArgs e)
{
btnGetDetails.Visible = false;
panel1.Visible = false;
unvisiable();
if (ss == "select")
{
ss = "";
}
else
{
if (cmbSection.SelectedItem.ToString() == "-- Select --")
{
MessageBox.Show("Select Section ", "School Management System", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
OleDbDataAdapter da1 = new OleDbDataAdapter("SELECT [SectionID] FROM [Section] WHERE ClassId = " + classid + " and Medium='" + cmbMedium.SelectedItem.ToString() + "' and [Section]='" + cmbSection.SelectedItem.ToString() + "'", cn);
DataSet ds1 = new DataSet();
da1.Fill(ds1);
if (ds1.Tables[0].Rows.Count != 0)
{
sectionid = Convert.ToInt32(ds1.Tables[0].Rows[0][0].ToString());
btnGetDetails.Visible = true;
}
}
}
}
private void cmbClass_SelectedIndexChanged(object sender, EventArgs e)
{
cmbSection.Text = "";
btnGetDetails.Visible = false;
panel1.Visible = false;
unvisiable();
cmbSection.Items.Clear();
if (cc == "select")
{
cc = "";
}
else
{
if (cmbClass.SelectedItem.ToString() == "-- Select --")
{
MessageBox.Show("Select Class ", "School Management System", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
OleDbDataAdapter da1 = new OleDbDataAdapter("SELECT ClassID FROM Class WHERE (ClassName = '" + cmbClass.SelectedItem.ToString() + "') and Medium='" + cmbMedium.SelectedItem.ToString() + "'", cn);
DataSet ds1 = new DataSet();
da1.Fill(ds1);
if (ds1.Tables[0].Rows.Count != 0)
{
classid = Convert.ToInt32(ds1.Tables[0].Rows[0][0].ToString());
}
OleDbDataAdapter da = new OleDbDataAdapter("SELECT [Section] FROM [Section] WHERE (ClassId = " + classid + ") and Medium='" + cmbMedium.SelectedItem.ToString() + "'", cn);
DataSet ds = new DataSet();
da.Fill(ds);
cmbSection.DisplayMember = "Section";
if (ds.Tables[0].Rows.Count != 0)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
cmbSection.Items.Add(ds.Tables[0].Rows[i].ItemArray[0].ToString());
}
cmbSection.Items.Insert(0, "-- Select --");
ss = "";
ss = "select";
cmbSection.SelectedIndex = 0;
}
}
}
}
private void cmbMedium_SelectedIndexChanged(object sender, EventArgs e)
{
cmbClass.Items.Clear();
cmbSection.Items.Clear();
cmbClass.Text = "";
cmbSection.Text = "";
btnGetDetails.Visible = false;
panel1.Visible = false;
unvisiable();
if (med == "medium")
{
}
else
{
if (cmbMedium.SelectedItem.ToString() == "-- Select --")
{
MessageBox.Show("Select Medium ", "School Management System", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
OleDbDataAdapter da = new OleDbDataAdapter("SELECT ClassName FROM Class WHERE (Medium = '" + cmbMedium.SelectedItem.ToString() + "')", cn);
DataSet ds = new DataSet();
da.Fill(ds);
cmbClass.DisplayMember = "ClassName";
if (ds.Tables[0].Rows.Count != 0)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
cmbClass.Items.Add(ds.Tables[0].Rows[i].ItemArray[0].ToString());
}
cmbClass.Items.Insert(0, "-- Select --");
cc = "";
cc = "select";
cmbClass.SelectedIndex = 0;
}
else
{
cmbClass.Text = "";
MessageBox.Show("No Class[es] Available For " + cmbMedium.SelectedItem.ToString() + " Medium ", "School Management System", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
med = "";
}
private void btnGetDetails_Click_1(object sender, EventArgs e)
{
if (cmbMedium.SelectedIndex == 0)
{
MessageBox.Show("Select Medium", "School Management System", MessageBoxButtons.OK, MessageBoxIcon.Information);
cmbMedium.Focus();
}
else if (cmbClass.SelectedIndex == -1)
{
MessageBox.Show("Select Class", "School Management System", MessageBoxButtons.OK, MessageBoxIcon.Information);
cmbClass.Focus();
}
else if (cmbSection.SelectedIndex == -1)
{
MessageBox.Show("Select Section", "School Management System", MessageBoxButtons.OK, MessageBoxIcon.Information);
cmbSection.Focus();
}
else
{
OleDbDataAdapter adpt1 = new OleDbDataAdapter("SELECT AdmissionNo,StudentName from Admission where Medium='" + cmbMedium.SelectedItem.ToString() + "' and ClassID=" + classid + " and SectionID=" + sectionid + "", cn);
DataSet dsranks1 = new DataSet();
adpt1.Fill(dsranks1);
DataTable dt = new DataTable();
dt.Columns.Add("AdmissionNo", typeof(string));
dt.Columns.Add("StudentName", typeof(string));
int sno = 0;
for (int i = 0; i < dsranks1.Tables[0].Rows.Count; i++)
{
sno = sno + 1;
dt.Rows.Add(dsranks1.Tables[0].Rows[i].ItemArray[0].ToString(), dsranks1.Tables[0].Rows[i].ItemArray[1].ToString());
}
dsranks1.Tables.Add(dt);
if (dsranks1.Tables[0].Rows.Count > 0)
{
dataGridView1.DataSource = dsranks1.Tables[1];
dataGridView1.Columns[0].DisplayIndex = 2;
dataGridView1.Columns[1].DisplayIndex = 3;
dataGridView1.Columns[2].DisplayIndex = 4;
dataGridView1.Columns[3].DisplayIndex = 0;
dataGridView1.Columns[4].DisplayIndex = 1;
dataGridView1.Columns[3].ReadOnly = true;
dataGridView1.Columns[4].ReadOnly = true;
dataGridView1.Columns[0].Visible = true;
dataGridView1.Columns[1].Visible = true;
foreach (DataGridViewRow row in dataGridView1.Rows)
{
row.Cells[0].Value = true;
row.Cells[1].Value = true;
}
if (rbAM.Checked)
{
dataGridView1.Columns[1].Visible = false;
}
else if (rbPM.Checked)
{
dataGridView1.Columns[0].Visible = false;
}
else
{
}
btnSubmit.Visible = true;
panel1.Visible = true;
}
else
{
MessageBox.Show("No Records Found", "School Management System", MessageBoxButtons.OK, MessageBoxIcon.Information);
btnSubmit.Visible = false;
panel1.Visible = false;
}
}
}
public void unvisiable()
{
rbToDay.Checked=true ;
rbAM.Checked=true;
btnSubmit.Visible = false;
}
private void btnSubmit_Click(object sender, EventArgs e)
{
DialogResult result;
DataTable dt_temp = new DataTable();
try
{
for (int i = 0; i < dataGridView1.ColumnCount; i++)
{
dt_temp.Columns.Add(dataGridView1.Columns[i].Name);
}
for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
{
DataRow row = dt_temp.NewRow();
for (int j = 0; j < dataGridView1.Columns.Count; j++)
{
row[j] = dataGridView1[j, i].Value;
}
dt_temp.Rows.Add(row);
}
DataSet dsDT = new DataSet();
dsDT.Tables.Add(dt_temp);
DateTime Cdate;
if (day == "ToDay")
{
Cdate = Convert.ToDateTime(System.DateTime.Now.ToShortDateString());
}
else
{
Cdate = Convert.ToDateTime(dateTimePicker1.Value.ToShortDateString());
}
if (Cdate > System.DateTime.Now.Date)
{
MessageBox.Show("Selected Date For Attendance Should Not Be Greater Than Current Date", "School Management System", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
string date = "#" + Cdate.ToShortDateString() + "#";
string pp = "Present";
string aa = "Absent";
string admisionNo = null;
admisionNo = dsDT.Tables[0].Rows[0].ItemArray[3].ToString();
if (AttandanceTime == "AM")
{
OleDbDataAdapter adpt10 = new OleDbDataAdapter("SELECT * from Attendance where AdmissionNo='" + admisionNo + "' and AttDate=" + date + " and (AM ='" + pp + "' or AM ='" + aa + "')", cn);
DataSet dsranks10 = new DataSet();
adpt10.Fill(dsranks10);
if (dsranks10.Tables[0].Rows.Count > 0)
{
MessageBox.Show("Morning Session Attendance Already Submitted", "School Management System", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
for (int a = 0; a < dsDT.Tables[0].Rows.Count; a++)
{
string am = dsDT.Tables[0].Rows[a].ItemArray[0].ToString();
string pm = dsDT.Tables[0].Rows[a].ItemArray[1].ToString();
string remarks = dsDT.Tables[0].Rows[a].ItemArray[2].ToString();
string admNo = dsDT.Tables[0].Rows[a].ItemArray[3].ToString();
string sname = dsDT.Tables[0].Rows[a].ItemArray[4].ToString();
string amP = null;
if (am == "True")
{
amP = "Present";
}
else
{
amP = "Absent";
}
OleDbCommand cmd = null;
cmd = new OleDbCommand("insert into Attendance(AdmissionNo,AttDate,AM,Remarks)values('" + admNo + "'," + date + ",'" + amP + "','" + remarks + "')", cn);
cn.Open();
cmd.ExecuteNonQuery();
cn.Close();
}
MessageBox.Show("Morning Session Attendance Submited Successfully", "School Management System", MessageBoxButtons.OK, MessageBoxIcon.Information);
panel1.Visible = false;
btnSubmit.Visible = false;
}
}
else if (AttandanceTime == "PM")
{
OleDbDataAdapter adpt08 = new OleDbDataAdapter("SELECT * from Attendance where AdmissionNo='" + admisionNo + "' and AttDate=" + date + " and (AM='" + pp + "' or AM='" + aa + "') and (PM='" + pp + "' or PM='" + aa + "')", cn);
DataSet dsranks08 = new DataSet();
adpt08.Fill(dsranks08);
if (dsranks08.Tables[0].Rows.Count > 0)
{
MessageBox.Show("Sessions Attendance Already Submited", "School Management System", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
OleDbDataAdapter adpt09 = new OleDbDataAdapter("SELECT * from Attendance where AdmissionNo='" + admisionNo + "' and AttDate=" + date + " and (AM='" + pp + "' or AM='" + aa + "') and PM Is Null", cn);
DataSet dsranks09 = new DataSet();
adpt09.Fill(dsranks09);
if (dsranks09.Tables[0].Rows.Count > 0)
{
for (int a = 0; a < dsDT.Tables[0].Rows.Count; a++)
{
string am = dsDT.Tables[0].Rows[a].ItemArray[0].ToString();
string pm = dsDT.Tables[0].Rows[a].ItemArray[1].ToString();
string remarks = dsDT.Tables[0].Rows[a].ItemArray[2].ToString();
string admNo = dsDT.Tables[0].Rows[a].ItemArray[3].ToString();
string sname = dsDT.Tables[0].Rows[a].ItemArray[4].ToString();
string pmP = null;
if (pm == "True")
{
pmP = "Present";
}
else
{
pmP = "Absent";
}
OleDbCommand cmd = null;
cmd = new OleDbCommand("Update Attendance set PM='" + pmP + "',Remarks='" + remarks + "' where AdmissionNo='" + admNo + "' and AttDate=" + date + "", cn);
cn.Open();
cmd.ExecuteNonQuery();
cn.Close();
}
MessageBox.Show("Afternoon Session Attendance Submitted Successfully", "School Management System", MessageBoxButtons.OK, MessageBoxIcon.Information);
panel1.Visible = false;
btnSubmit.Visible = false;
}
else
{
OleDbDataAdapter adpt10 = new OleDbDataAdapter("SELECT * from Attendance where AdmissionNo='" + admisionNo + "' and AttDate=" + date + " and AM Is Null and PM Is Null", cn);
DataSet dsranks10 = new DataSet();
adpt10.Fill(dsranks10);
if (dsranks10.Tables[0].Rows.Count == 0)
{
result = MessageBox.Show("Morning Session Attendance Not Taken, Do You Want To Take Now", "School Management System", MessageBoxButtons.YesNo, MessageBoxIcon.Information);
if (result == System.Windows.Forms.DialogResult.Yes)
{
rbAM.Checked = true;
}
if (result == System.Windows.Forms.DialogResult.No)
{
OleDbDataAdapter adpt = new OleDbDataAdapter("SELECT * from Attendance where AdmissionNo='" + admisionNo + "' and AttDate=" + date + " and (PM ='" + pp + "' or PM ='" + aa + "')", cn);
DataSet dsranks = new DataSet();
adpt.Fill(dsranks);
if (dsranks.Tables[0].Rows.Count > 0)
{
MessageBox.Show("Afternoon Session Attendance Already Submitted", "School Management System", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
for (int a = 0; a < dsDT.Tables[0].Rows.Count; a++)
{
string am = dsDT.Tables[0].Rows[a].ItemArray[0].ToString();
string pm = dsDT.Tables[0].Rows[a].ItemArray[1].ToString();
string remarks = dsDT.Tables[0].Rows[a].ItemArray[2].ToString();
string admNo = dsDT.Tables[0].Rows[a].ItemArray[3].ToString();
string sname = dsDT.Tables[0].Rows[a].ItemArray[4].ToString();
string pmP = null;
if (pm == "True")
{
pmP = "Present";
}
else
{
pmP = "Absent";
}
OleDbCommand cmd = null;
cmd = new OleDbCommand("insert into Attendance(AdmissionNo,AttDate,PM,Remarks)values('" + admNo + "'," + date + ",'" + pmP + "','" + remarks + "')", cn);
cn.Open();
cmd.ExecuteNonQuery();
cn.Close();
}
MessageBox.Show("Afternoon Session Attendance Submited Successfully", "School Management System", MessageBoxButtons.OK, MessageBoxIcon.Information);
panel1.Visible = false;
btnSubmit.Visible = false;
}
}
}
}
}
}
else
{
OleDbDataAdapter adpt10 = new OleDbDataAdapter("SELECT * from Attendance where AdmissionNo='" + admisionNo + "' and AttDate=" + date + " and (AM ='" + pp + "' or AM ='" + aa + "') and (PM ='" + pp + "' or PM ='" + aa + "')", cn);
DataSet dsranks10 = new DataSet();
adpt10.Fill(dsranks10);
if (dsranks10.Tables[0].Rows.Count > 0)
{
MessageBox.Show("Sessions Attendance Already Submitted", "School Management System", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
OleDbDataAdapter adpt78 = new OleDbDataAdapter("SELECT * from Attendance where AdmissionNo='" + admisionNo + "' and AttDate=" + date + " and (AM ='" + pp + "' or AM ='" + aa + "') and PM Is Null", cn);
DataSet dsranks78 = new DataSet();
adpt78.Fill(dsranks78);
if (dsranks78.Tables[0].Rows.Count > 0)
{
result = MessageBox.Show("Afternoon Session Attendance Not Taken, Do You Want To Take Now", "School Management System", MessageBoxButtons.YesNo, MessageBoxIcon.Information);
if (result == System.Windows.Forms.DialogResult.No)
{
rbAM.Checked = true;
}
if (result == System.Windows.Forms.DialogResult.Yes)
{
for (int a = 0; a < dsDT.Tables[0].Rows.Count; a++)
{
string am = dsDT.Tables[0].Rows[a].ItemArray[0].ToString();
string pm = dsDT.Tables[0].Rows[a].ItemArray[1].ToString();
string remarks = dsDT.Tables[0].Rows[a].ItemArray[2].ToString();
string admNo = dsDT.Tables[0].Rows[a].ItemArray[3].ToString();
string sname = dsDT.Tables[0].Rows[a].ItemArray[4].ToString();
string pmP = null;
if (pm == "True")
{
pmP = "Present";
}
else
{
pmP = "Absent";
}
OleDbCommand cmd = null;
cmd = new OleDbCommand("Update Attendance set PM='" + pmP + "',Remarks='" + remarks + "' where AdmissionNo='" + admNo + "' and AttDate=" + date + "", cn);
cn.Open();
cmd.ExecuteNonQuery();
cn.Close();
}
MessageBox.Show("Afternoon Session Attendance Submitted Successfully", "School Management System", MessageBoxButtons.OK, MessageBoxIcon.Information);
panel1.Visible = false;
btnSubmit.Visible = false;
}
}
else
{
OleDbDataAdapter adpt79 = new OleDbDataAdapter("SELECT * from Attendance where AdmissionNo='" + admisionNo + "' and AttDate=" + date + " and (PM ='" + pp + "' or PM ='" + aa + "') and AM Is Null", cn);
DataSet dsranks79 = new DataSet();
adpt79.Fill(dsranks79);
if (dsranks79.Tables[0].Rows.Count > 0)
{
result = MessageBox.Show("Morning Session Attendance Not Taken, Do You Want To Take Now", "School Management System", MessageBoxButtons.YesNo, MessageBoxIcon.Information);
if (result == System.Windows.Forms.DialogResult.No)
{
rbAM.Checked = true;
}
if (result == System.Windows.Forms.DialogResult.Yes)
{
for (int a = 0; a < dsDT.Tables[0].Rows.Count; a++)
{
string am = dsDT.Tables[0].Rows[a].ItemArray[0].ToString();
string pm = dsDT.Tables[0].Rows[a].ItemArray[1].ToString();
string remarks = dsDT.Tables[0].Rows[a].ItemArray[2].ToString();
string admNo = dsDT.Tables[0].Rows[a].ItemArray[3].ToString();
string sname = dsDT.Tables[0].Rows[a].ItemArray[4].ToString();
string amP = null;
if (am == "True")
{
amP = "Present";
}
else
{
amP = "Absent";
}
OleDbCommand cmd = null;
cmd = new OleDbCommand(" Update Attendance set AM='" + amP + "',Remarks='" + remarks + "' where AdmissionNo='" + admNo + "' and AttDate=" + date + "", cn);
cn.Open();
cmd.ExecuteNonQuery();
cn.Close();
}
MessageBox.Show("Morning Session Attendance Submitted Successfully", "School Management System", MessageBoxButtons.OK, MessageBoxIcon.Information);
panel1.Visible = false;
btnSubmit.Visible = false;
}
}
else
{
for (int a = 0; a < dsDT.Tables[0].Rows.Count; a++)
{
string am = dsDT.Tables[0].Rows[a].ItemArray[0].ToString();
string pm = dsDT.Tables[0].Rows[a].ItemArray[1].ToString();
string remarks = dsDT.Tables[0].Rows[a].ItemArray[2].ToString();
string admNo = dsDT.Tables[0].Rows[a].ItemArray[3].ToString();
string sname = dsDT.Tables[0].Rows[a].ItemArray[4].ToString();
string amP = null, pmP = null;
if (am == "True")
{
amP = "Present";
}
else
{
amP = "Absent";
}
if (pm == "True")
{
pmP = "Present";
}
else
{
pmP = "Absent";
}
OleDbCommand cmd = null;
cmd = new OleDbCommand("insert into Attendance(AdmissionNo,AttDate,AM,PM,Remarks)values('" + admNo + "'," + date + ",'" + amP + "','" + pmP + "','" + remarks + "')", cn);
cn.Open();
cmd.ExecuteNonQuery();
cn.Close();
}
MessageBox.Show("Sessions Attendance Submitted Successfully", "School Management System", MessageBoxButtons.OK, MessageBoxIcon.Information);
panel1.Visible = false;
btnSubmit.Visible = false;
}
}
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Exception", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void rbAM_CheckedChanged(object sender, EventArgs e)
{
AttandanceTime = "AM";
panel1.Visible = false;
btnSubmit.Visible = false;
}
private void rbPM_CheckedChanged(object sender, EventArgs e)
{
AttandanceTime = "PM";
panel1.Visible = false;
btnSubmit.Visible = false;
}
private void rmBoth_CheckedChanged(object sender, EventArgs e)
{
AttandanceTime = "Both";
panel1.Visible = false;
btnSubmit.Visible = false;
}
private void rbToDay_CheckedChanged_1(object sender, EventArgs e)
{
day = "ToDay";
dateTimePicker1.Visible = false;
}
private void rbOthers_CheckedChanged_1(object sender, EventArgs e)
{
day = "Others";
dateTimePicker1.Visible = true;
}
private void pictureBox1_Click(object sender, EventArgs e)
{
this.Close();
EntryForm e1 = new EntryForm();
e1.Show();
}
private void pictureBox2_Click(object sender, EventArgs e)
{
this.Close();
SMSMainForm a=new SMSMainForm();
a.Show();
}
}
}