private void lblForgot_Click(object sender, EventArgs e)
{
SqlConnection conn = DBUtils.GetDBConnection();
conn.Open();
string userID = "";
string question = "";
string answer = "";
using (SqlCommand cmd = new SqlCommand("Select * from Accounts where userID = @userID", conn))
{
cmd.Parameters.AddWithValue("@userID", txtUser.Text);
SqlDataReader reader = cmd.ExecuteReader();
int ordinal = 0;
if (reader.Read())
{
ordinal = reader.GetOrdinal("userID");
userID = reader.GetString(ordinal);
ordinal = reader.GetOrdinal("securityQuestion");
question = reader.GetString(ordinal);
ordinal = reader.GetOrdinal("securityAnswer");
answer = reader.GetString(ordinal);
}
else
{
MessageBox.Show("Account does not exist.");
return;
}
reader.Close();
}
ForgotPassword fg = new ForgotPassword();
fg.Activated += new EventHandler(mn_Activated);
fg.FormClosed += new FormClosedEventHandler(mn_FormClosed);
fg.userID = userID;
fg.question = question;
fg.answer = answer;
fg.Show();
}
private void Report_Load(object sender, EventArgs e)
{
txtStudNo.Text = studNo.ToString();
txtResidence.Text = residence;
txtFullName.Text = fullName;
lvReport.Columns.Add("Record No.", 150);
lvReport.Columns.Add("Date Committed", 150);
lvReport.Columns.Add("Semester", 130);
lvReport.Columns.Add("School Year", 130);
lvReport.Columns.Add("Violation Code", 130);
lvReport.Columns.Add("Violation Type", 130);
lvReport.Columns.Add("Violation Name", 120);
lvReport.Columns.Add("Remarks", 120);
txtStudNo.Text = studNo.ToString();
SqlConnection conn = DBUtils.GetDBConnection();
conn.Open();
using (SqlCommand cmd = new SqlCommand("SELECT * FROM RecordDetails INNER JOIN ViolationDetails ON ViolationDetails.ViolationCode = RecordDetails.ViolationCode WHERE RecordDetails.StudentNo = @studNo", conn))
{
lvReport.Items.Clear();
cmd.Parameters.AddWithValue("@studNo", studNo);
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
int recordNo = Convert.ToInt32(dr.GetValue(0));
int dateIndex = dr.GetOrdinal("DateCommitted");
DateTime myDate = dr.GetDateTime(dateIndex);
string date = myDate.ToString("MM/dd/yyyy");
int periodIndex = dr.GetOrdinal("Period");
string period = Convert.ToString(dr.GetValue(periodIndex));
int syIndex = dr.GetOrdinal("SY");
string sy = Convert.ToString(dr.GetValue(syIndex));
int violationCode = Convert.ToInt32(dr.GetValue(2));
int violationTypeIndex = dr.GetOrdinal("ViolationType");
string violationType = Convert.ToString(dr.GetValue(violationTypeIndex));
if (violationType.Equals("Institutional"))
{
institutionalCount += 1;
}
else if (violationType.Equals("Departmental"))
{
departmentalCount += 1;
}
int violationNameIndex = dr.GetOrdinal("ViolationName");
string violationName = Convert.ToString(dr.GetValue(violationNameIndex));
int remarksIndex = dr.GetOrdinal("Remarks");
string remarks = Convert.ToString(dr.GetValue(remarksIndex));
records[0] = recordNo.ToString();
records[1] = date;
records[2] = period;
records[3] = sy;
records[4] = violationCode.ToString();
records[5] = violationType;
records[6] = violationName;
records[7] = remarks;
ListViewItem record = new ListViewItem(records);
lvReport.Items.Add(record);
txtDepartViolation.Text = departmentalCount.ToString();
txtInstiViolation.Text = institutionalCount.ToString();
}
}
}
}
private void updateListView()
{
SqlConnection conn = DBUtils.GetDBConnection();
conn.Open();
if (period == "ALL" && violationType == "ALL" && residence == "ALL")
{
using (SqlCommand cmd = new SqlCommand("SELECT rd.RecordNo as RecordNo, rd.StudentNo as StudentNo, rd.ViolationCode as ViolationCode, rd.DateCommitted as DateCommitted, si.LastName + ', ' + si.GivenName + ' ' + si.MiddleName AS 'Full Name', si.ResidenceStatus as ResidenceStatus, rd.Period as Period, rd.SY as SY, rd.Remarks as Remarks, vd.ViolationType as ViolationType, vd.ViolationName FROM RecordDetails AS rd INNER JOIN StudentInfo AS si ON rd.StudentNo = si.StudentNo INNER JOIN ViolationDetails AS vd ON rd.ViolationCode = vd.ViolationCode WHERE (year(DateCommitted) between " + syFrom + " and " + syTo + ")", conn))
{
lvGenReport.Items.Clear();
using (DbDataReader reader = cmd.ExecuteReader())
{
int i = 1;
if (reader.HasRows)
{
while (reader.Read())
{
//1
int recordNo = Convert.ToInt32(reader.GetValue(0));
//2
int studNoIndex = reader.GetOrdinal("StudentNo");
int studNo = Convert.ToInt32(reader.GetValue(studNoIndex));
//3
int fullNameIndex = reader.GetOrdinal("Full Name");
string fullName = Convert.ToString(reader.GetValue(fullNameIndex));
//4
int residenceIndex = reader.GetOrdinal("ResidenceStatus");
string residence = Convert.ToString(reader.GetValue(residenceIndex));
//5
int dateIndex = reader.GetOrdinal("dateCommitted");
DateTime myDate = Convert.ToDateTime(reader.GetValue(dateIndex));
string date = myDate.ToString("MM/dd/yyyy");
//6
int violationCodeIndex = reader.GetOrdinal("ViolationCode");
int violationCode = Convert.ToInt32(reader.GetValue(violationCodeIndex));
//7
int violationTypeIndex = reader.GetOrdinal("ViolationType");
string violationType = Convert.ToString(reader.GetValue(violationTypeIndex));
//8
int violationNameIndex = reader.GetOrdinal("ViolationName");
string violationName = Convert.ToString(reader.GetValue(violationNameIndex));
//9
int remarksIndex = reader.GetOrdinal("remarks");
string remarks = Convert.ToString(reader.GetValue(remarksIndex));
genRep[0] = i.ToString();
genRep[1] = recordNo.ToString();
genRep[2] = studNo.ToString();
genRep[3] = fullName;
genRep[4] = residence;
genRep[5] = date;
genRep[6] = violationCode.ToString();
genRep[7] = violationType;
genRep[8] = violationName;
genRep[9] = remarks;
ListViewItem lvGen = new ListViewItem(genRep);
lvGenReport.Items.Add(lvGen);
i++;
}
}
}
}
}
else if (period == "ALL" && violationType == "ALL")
{
using (SqlCommand cmd = new SqlCommand("SELECT rd.RecordNo as RecordNo, rd.StudentNo as StudentNo, rd.ViolationCode as ViolationCode, rd.DateCommitted as DateCommitted, si.LastName + ', ' + si.GivenName + ' ' + si.MiddleName AS 'Full Name', si.ResidenceStatus as ResidenceStatus, rd.Period as Period, rd.SY as SY, rd.Remarks as Remarks, vd.ViolationType as ViolationType, vd.ViolationName FROM RecordDetails AS rd INNER JOIN StudentInfo AS si ON rd.StudentNo = si.StudentNo INNER JOIN ViolationDetails AS vd ON rd.ViolationCode = vd.ViolationCode WHERE (year(DateCommitted) between " + syFrom + " and " + syTo + ") AND (si.ResidenceStatus = '" + residence + "')", conn))
{
lvGenReport.Items.Clear();
using (DbDataReader reader = cmd.ExecuteReader())
{
int i = 1;
if (reader.HasRows)
{
while (reader.Read())
{
//1
int recordNo = Convert.ToInt32(reader.GetValue(0));
//2
int studNoIndex = reader.GetOrdinal("StudentNo");
int studNo = Convert.ToInt32(reader.GetValue(studNoIndex));
//3
int fullNameIndex = reader.GetOrdinal("Full Name");
string fullName = Convert.ToString(reader.GetValue(fullNameIndex));
//4
int residenceIndex = reader.GetOrdinal("ResidenceStatus");
string residence = Convert.ToString(reader.GetValue(residenceIndex));
//5
int dateIndex = reader.GetOrdinal("dateCommitted");
DateTime myDate = Convert.ToDateTime(reader.GetValue(dateIndex));
string date = myDate.ToString("MM/dd/yyyy");
//6
int violationCodeIndex = reader.GetOrdinal("ViolationCode");
int violationCode = Convert.ToInt32(reader.GetValue(violationCodeIndex));
//7
int violationTypeIndex = reader.GetOrdinal("ViolationType");
string violationType = Convert.ToString(reader.GetValue(violationTypeIndex));
//8
int violationNameIndex = reader.GetOrdinal("ViolationName");
string violationName = Convert.ToString(reader.GetValue(violationNameIndex));
//9
int remarksIndex = reader.GetOrdinal("remarks");
string remarks = Convert.ToString(reader.GetValue(remarksIndex));
genRep[0] = i.ToString();
genRep[1] = recordNo.ToString();
genRep[2] = studNo.ToString();
genRep[3] = fullName;
genRep[4] = residence;
genRep[5] = date;
genRep[6] = violationCode.ToString();
genRep[7] = violationType;
genRep[8] = violationName;
genRep[9] = remarks;
ListViewItem lvGen = new ListViewItem(genRep);
lvGenReport.Items.Add(lvGen);
i++;
}
}
}
}
}
else if (period == "ALL" && residence == "ALL")
{
using (SqlCommand cmd = new SqlCommand("SELECT rd.RecordNo as RecordNo, rd.StudentNo as StudentNo, rd.ViolationCode as ViolationCode, rd.DateCommitted as DateCommitted, si.LastName + ', ' + si.GivenName + ' ' + si.MiddleName AS 'Full Name', si.ResidenceStatus as ResidenceStatus, rd.Period as Period, rd.SY as SY, rd.Remarks as Remarks, vd.ViolationType as ViolationType, vd.ViolationName FROM RecordDetails AS rd INNER JOIN StudentInfo AS si ON rd.StudentNo = si.StudentNo INNER JOIN ViolationDetails AS vd ON rd.ViolationCode = vd.ViolationCode WHERE (vd.ViolationType = '" + violationType + "') AND (year(DateCommitted) between " + syFrom + " and " + syTo + ")", conn))
{
lvGenReport.Items.Clear();
using (DbDataReader reader = cmd.ExecuteReader())
{
int i = 1;
if (reader.HasRows)
{
while (reader.Read())
{
//1
int recordNo = Convert.ToInt32(reader.GetValue(0));
//2
int studNoIndex = reader.GetOrdinal("StudentNo");
int studNo = Convert.ToInt32(reader.GetValue(studNoIndex));
//3
int fullNameIndex = reader.GetOrdinal("Full Name");
string fullName = Convert.ToString(reader.GetValue(fullNameIndex));
//4
int residenceIndex = reader.GetOrdinal("ResidenceStatus");
string residence = Convert.ToString(reader.GetValue(residenceIndex));
//5
int dateIndex = reader.GetOrdinal("dateCommitted");
DateTime myDate = Convert.ToDateTime(reader.GetValue(dateIndex));
string date = myDate.ToString("MM/dd/yyyy");
//6
int violationCodeIndex = reader.GetOrdinal("ViolationCode");
int violationCode = Convert.ToInt32(reader.GetValue(violationCodeIndex));
//7
int violationTypeIndex = reader.GetOrdinal("ViolationType");
string violationType = Convert.ToString(reader.GetValue(violationTypeIndex));
//8
int violationNameIndex = reader.GetOrdinal("ViolationName");
string violationName = Convert.ToString(reader.GetValue(violationNameIndex));
//9
int remarksIndex = reader.GetOrdinal("remarks");
string remarks = Convert.ToString(reader.GetValue(remarksIndex));
genRep[0] = i.ToString();
genRep[1] = recordNo.ToString();
genRep[2] = studNo.ToString();
genRep[3] = fullName;
genRep[4] = residence;
genRep[5] = date;
genRep[6] = violationCode.ToString();
genRep[7] = violationType;
genRep[8] = violationName;
genRep[9] = remarks;
ListViewItem lvGen = new ListViewItem(genRep);
lvGenReport.Items.Add(lvGen);
i++;
}
}
}
}
}
else if (violationType == "ALL" && residence == "ALL")
{
using (SqlCommand cmd = new SqlCommand("SELECT rd.RecordNo as RecordNo, rd.StudentNo as StudentNo, rd.ViolationCode as ViolationCode, rd.DateCommitted as DateCommitted, si.LastName + ', ' + si.GivenName + ' ' + si.MiddleName AS 'Full Name', si.ResidenceStatus as ResidenceStatus, rd.Period as Period, rd.SY as SY, rd.Remarks as Remarks, vd.ViolationType as ViolationType, vd.ViolationName FROM RecordDetails AS rd INNER JOIN StudentInfo AS si ON rd.StudentNo = si.StudentNo INNER JOIN ViolationDetails AS vd ON rd.ViolationCode = vd.ViolationCode WHERE (rd.Period = '" + period + "') AND (year(DateCommitted) between " + syFrom + " and " + syTo + ")", conn))
{
lvGenReport.Items.Clear();
using (DbDataReader reader = cmd.ExecuteReader())
{
int i = 1;
if (reader.HasRows)
{
while (reader.Read())
{
//1
int recordNo = Convert.ToInt32(reader.GetValue(0));
//2
int studNoIndex = reader.GetOrdinal("StudentNo");
int studNo = Convert.ToInt32(reader.GetValue(studNoIndex));
//3
int fullNameIndex = reader.GetOrdinal("Full Name");
string fullName = Convert.ToString(reader.GetValue(fullNameIndex));
//4
int residenceIndex = reader.GetOrdinal("ResidenceStatus");
string residence = Convert.ToString(reader.GetValue(residenceIndex));
//5
int dateIndex = reader.GetOrdinal("dateCommitted");
DateTime myDate = Convert.ToDateTime(reader.GetValue(dateIndex));
string date = myDate.ToString("MM/dd/yyyy");
//6
int violationCodeIndex = reader.GetOrdinal("ViolationCode");
int violationCode = Convert.ToInt32(reader.GetValue(violationCodeIndex));
//7
int violationTypeIndex = reader.GetOrdinal("ViolationType");
string violationType = Convert.ToString(reader.GetValue(violationTypeIndex));
//8
int violationNameIndex = reader.GetOrdinal("ViolationName");
string violationName = Convert.ToString(reader.GetValue(violationNameIndex));
//9
int remarksIndex = reader.GetOrdinal("remarks");
string remarks = Convert.ToString(reader.GetValue(remarksIndex));
genRep[0] = i.ToString();
genRep[1] = recordNo.ToString();
genRep[2] = studNo.ToString();
genRep[3] = fullName;
genRep[4] = residence;
genRep[5] = date;
genRep[6] = violationCode.ToString();
genRep[7] = violationType;
genRep[8] = violationName;
genRep[9] = remarks;
ListViewItem lvGen = new ListViewItem(genRep);
lvGenReport.Items.Add(lvGen);
i++;
}
}
}
}
}
else if (violationType == "ALL")
{
using (SqlCommand cmd = new SqlCommand("SELECT rd.RecordNo as RecordNo, rd.StudentNo as StudentNo, rd.ViolationCode as ViolationCode, rd.DateCommitted as DateCommitted, si.LastName + ', ' + si.GivenName + ' ' + si.MiddleName AS 'Full Name', si.ResidenceStatus as ResidenceStatus, rd.Period as Period, rd.SY as SY, rd.Remarks as Remarks, vd.ViolationType as ViolationType, vd.ViolationName FROM RecordDetails AS rd INNER JOIN StudentInfo AS si ON rd.StudentNo = si.StudentNo INNER JOIN ViolationDetails AS vd ON rd.ViolationCode = vd.ViolationCode WHERE (rd.Period = '" + period + "') AND (year(DateCommitted) between " + syFrom + " and " + syTo + ") AND (si.ResidenceStatus = '" + residence + "')", conn))
{
lvGenReport.Items.Clear();
using (DbDataReader reader = cmd.ExecuteReader())
{
int i = 1;
if (reader.HasRows)
{
while (reader.Read())
{
//1
int recordNo = Convert.ToInt32(reader.GetValue(0));
//2
int studNoIndex = reader.GetOrdinal("StudentNo");
int studNo = Convert.ToInt32(reader.GetValue(studNoIndex));
//3
int fullNameIndex = reader.GetOrdinal("Full Name");
string fullName = Convert.ToString(reader.GetValue(fullNameIndex));
//4
int residenceIndex = reader.GetOrdinal("ResidenceStatus");
string residence = Convert.ToString(reader.GetValue(residenceIndex));
//5
int dateIndex = reader.GetOrdinal("dateCommitted");
DateTime myDate = Convert.ToDateTime(reader.GetValue(dateIndex));
string date = myDate.ToString("MM/dd/yyyy");
//6
int violationCodeIndex = reader.GetOrdinal("ViolationCode");
int violationCode = Convert.ToInt32(reader.GetValue(violationCodeIndex));
//7
int violationTypeIndex = reader.GetOrdinal("ViolationType");
string violationType = Convert.ToString(reader.GetValue(violationTypeIndex));
//8
int violationNameIndex = reader.GetOrdinal("ViolationName");
string violationName = Convert.ToString(reader.GetValue(violationNameIndex));
//9
int remarksIndex = reader.GetOrdinal("remarks");
string remarks = Convert.ToString(reader.GetValue(remarksIndex));
genRep[0] = i.ToString();
genRep[1] = recordNo.ToString();
genRep[2] = studNo.ToString();
genRep[3] = fullName;
genRep[4] = residence;
genRep[5] = date;
genRep[6] = violationCode.ToString();
genRep[7] = violationType;
genRep[8] = violationName;
genRep[9] = remarks;
ListViewItem lvGen = new ListViewItem(genRep);
lvGenReport.Items.Add(lvGen);
i++;
}
}
}
}
}
else if (period == "ALL")
{
using (SqlCommand cmd = new SqlCommand("SELECT rd.RecordNo as RecordNo, rd.StudentNo as StudentNo, rd.ViolationCode as ViolationCode, rd.DateCommitted as DateCommitted, si.LastName + ', ' + si.GivenName + ' ' + si.MiddleName AS 'Full Name', si.ResidenceStatus as ResidenceStatus, rd.Period as Period, rd.SY as SY, rd.Remarks as Remarks, vd.ViolationType as ViolationType, vd.ViolationName FROM RecordDetails AS rd INNER JOIN StudentInfo AS si ON rd.StudentNo = si.StudentNo INNER JOIN ViolationDetails AS vd ON rd.ViolationCode = vd.ViolationCode WHERE (vd.ViolationType = '" + violationType + "') AND (year(DateCommitted) between " + syFrom + " and " + syTo + ") AND (si.ResidenceStatus = '" + residence + "')", conn))
{
lvGenReport.Items.Clear();
using (DbDataReader reader = cmd.ExecuteReader())
{
int i = 1;
if (reader.HasRows)
{
while (reader.Read())
{
//1
int recordNo = Convert.ToInt32(reader.GetValue(0));
//2
int studNoIndex = reader.GetOrdinal("StudentNo");
int studNo = Convert.ToInt32(reader.GetValue(studNoIndex));
//3
int fullNameIndex = reader.GetOrdinal("Full Name");
string fullName = Convert.ToString(reader.GetValue(fullNameIndex));
//4
int residenceIndex = reader.GetOrdinal("ResidenceStatus");
string residence = Convert.ToString(reader.GetValue(residenceIndex));
//5
int dateIndex = reader.GetOrdinal("dateCommitted");
DateTime myDate = Convert.ToDateTime(reader.GetValue(dateIndex));
string date = myDate.ToString("MM/dd/yyyy");
//6
int violationCodeIndex = reader.GetOrdinal("ViolationCode");
int violationCode = Convert.ToInt32(reader.GetValue(violationCodeIndex));
//7
int violationTypeIndex = reader.GetOrdinal("ViolationType");
string violationType = Convert.ToString(reader.GetValue(violationTypeIndex));
//8
int violationNameIndex = reader.GetOrdinal("ViolationName");
string violationName = Convert.ToString(reader.GetValue(violationNameIndex));
//9
int remarksIndex = reader.GetOrdinal("remarks");
string remarks = Convert.ToString(reader.GetValue(remarksIndex));
genRep[0] = i.ToString();
genRep[1] = recordNo.ToString();
genRep[2] = studNo.ToString();
genRep[3] = fullName;
genRep[4] = residence;
genRep[5] = date;
genRep[6] = violationCode.ToString();
genRep[7] = violationType;
genRep[8] = violationName;
genRep[9] = remarks;
ListViewItem lvGen = new ListViewItem(genRep);
lvGenReport.Items.Add(lvGen);
i++;
}
}
}
}
}
else if (residence == "ALL")
{
using (SqlCommand cmd = new SqlCommand("SELECT rd.RecordNo as RecordNo, rd.StudentNo as StudentNo, rd.ViolationCode as ViolationCode, rd.DateCommitted as DateCommitted, si.LastName + ', ' + si.GivenName + ' ' + si.MiddleName AS 'Full Name', si.ResidenceStatus as ResidenceStatus, rd.Period as Period, rd.SY as SY, rd.Remarks as Remarks, vd.ViolationType as ViolationType, vd.ViolationName FROM RecordDetails AS rd INNER JOIN StudentInfo AS si ON rd.StudentNo = si.StudentNo INNER JOIN ViolationDetails AS vd ON rd.ViolationCode = vd.ViolationCode WHERE (vd.ViolationType = '" + violationType + "') AND (rd.Period = '" + period + "') AND (year(DateCommitted) between " + syFrom + " and " + syTo + ")", conn))
{
lvGenReport.Items.Clear();
using (DbDataReader reader = cmd.ExecuteReader())
{
int i = 1;
if (reader.HasRows)
{
while (reader.Read())
{
//1
int recordNo = Convert.ToInt32(reader.GetValue(0));
//2
int studNoIndex = reader.GetOrdinal("StudentNo");
int studNo = Convert.ToInt32(reader.GetValue(studNoIndex));
//3
int fullNameIndex = reader.GetOrdinal("Full Name");
string fullName = Convert.ToString(reader.GetValue(fullNameIndex));
//4
int residenceIndex = reader.GetOrdinal("ResidenceStatus");
string residence = Convert.ToString(reader.GetValue(residenceIndex));
//5
int dateIndex = reader.GetOrdinal("dateCommitted");
DateTime myDate = Convert.ToDateTime(reader.GetValue(dateIndex));
string date = myDate.ToString("MM/dd/yyyy");
//6
int violationCodeIndex = reader.GetOrdinal("ViolationCode");
int violationCode = Convert.ToInt32(reader.GetValue(violationCodeIndex));
//7
int violationTypeIndex = reader.GetOrdinal("ViolationType");
string violationType = Convert.ToString(reader.GetValue(violationTypeIndex));
//8
int violationNameIndex = reader.GetOrdinal("ViolationName");
string violationName = Convert.ToString(reader.GetValue(violationNameIndex));
//9
int remarksIndex = reader.GetOrdinal("remarks");
string remarks = Convert.ToString(reader.GetValue(remarksIndex));
genRep[0] = i.ToString();
genRep[1] = recordNo.ToString();
genRep[2] = studNo.ToString();
genRep[3] = fullName;
genRep[4] = residence;
genRep[5] = date;
genRep[6] = violationCode.ToString();
genRep[7] = violationType;
genRep[8] = violationName;
genRep[9] = remarks;
ListViewItem lvGen = new ListViewItem(genRep);
lvGenReport.Items.Add(lvGen);
i++;
}
}
}
}
}
else
{
using (SqlCommand cmd = new SqlCommand("SELECT rd.RecordNo as RecordNo, rd.StudentNo as StudentNo, rd.ViolationCode as ViolationCode, rd.DateCommitted as DateCommitted, si.LastName + ', ' + si.GivenName + ' ' + si.MiddleName AS 'Full Name', si.ResidenceStatus as ResidenceStatus, rd.Period as Period, rd.SY as SY, rd.Remarks as Remarks, vd.ViolationType as ViolationType, vd.ViolationName FROM RecordDetails AS rd INNER JOIN StudentInfo AS si ON rd.StudentNo = si.StudentNo INNER JOIN ViolationDetails AS vd ON rd.ViolationCode = vd.ViolationCode WHERE (vd.ViolationType = '" + violationType + "') AND (rd.Period = '" + period + "') AND (year(DateCommitted) between " + syFrom + " and " + syTo + ") AND (si.ResidenceStatus = '" + residence + "')", conn))
{
lvGenReport.Items.Clear();
using (DbDataReader reader = cmd.ExecuteReader())
{
int i = 1;
if (reader.HasRows)
{
while (reader.Read())
{
//1
int recordNo = Convert.ToInt32(reader.GetValue(0));
//2
int studNoIndex = reader.GetOrdinal("StudentNo");
int studNo = Convert.ToInt32(reader.GetValue(studNoIndex));
//3
int fullNameIndex = reader.GetOrdinal("Full Name");
string fullName = Convert.ToString(reader.GetValue(fullNameIndex));
//4
int residenceIndex = reader.GetOrdinal("ResidenceStatus");
string residence = Convert.ToString(reader.GetValue(residenceIndex));
//5
int dateIndex = reader.GetOrdinal("dateCommitted");
DateTime myDate = Convert.ToDateTime(reader.GetValue(dateIndex));
string date = myDate.ToString("MM/dd/yyyy");
//6
int violationCodeIndex = reader.GetOrdinal("ViolationCode");
int violationCode = Convert.ToInt32(reader.GetValue(violationCodeIndex));
//7
int violationTypeIndex = reader.GetOrdinal("ViolationType");
string violationType = Convert.ToString(reader.GetValue(violationTypeIndex));
//8
int violationNameIndex = reader.GetOrdinal("ViolationName");
string violationName = Convert.ToString(reader.GetValue(violationNameIndex));
//9
int remarksIndex = reader.GetOrdinal("remarks");
string remarks = Convert.ToString(reader.GetValue(remarksIndex));
genRep[0] = i.ToString();
genRep[1] = recordNo.ToString();
genRep[2] = studNo.ToString();
genRep[3] = fullName;
genRep[4] = residence;
genRep[5] = date;
genRep[6] = violationCode.ToString();
genRep[7] = violationType;
genRep[8] = violationName;
genRep[9] = remarks;
ListViewItem lvGen = new ListViewItem(genRep);
lvGenReport.Items.Add(lvGen);
i++;
}
}
}
}
}
conn.Close();
}
private void btnLogin_Click(object sender, EventArgs e)
{
Main mn = new Main();
string user, pass;
user = txtUser.Text;
pass = txtPass.Text;
if (txtUser.Text == "" && txtPass.Text == "")
{
txtUser.Focus();
MessageBox.Show("No input!");
}
else if (txtPass.Text == "")
{
MessageBox.Show("No Password input");
txtPass.Focus();
}
else if (txtUser.Text == "")
{
MessageBox.Show("No Username input!");
txtUser.Focus();
}
else
{
SqlConnection conn = DBUtils.GetDBConnection();
conn.Open();
Nullable <int> loginAttempts;
int userLevel;
using (SqlCommand cmd = new SqlCommand("Select loginAttempts FROM Accounts WHERE userID = @userID", conn))
{
cmd.Parameters.AddWithValue("@userID", user);
loginAttempts = Convert.ToInt32(cmd.ExecuteScalar());
}
if (loginAttempts < 5)
{
string un = txtUser.Text;
string pw = txtPass.Text;
using (SqlCommand cmd = new SqlCommand("Select * from Accounts where userID = @userID AND Password = @password", conn))
{
cmd.Parameters.AddWithValue("@userID", un);
cmd.Parameters.AddWithValue("@password", pw);
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
string lName, fName, mName;
dr.Read();
lName = dr.GetString(2);
fName = dr.GetString(3);
mName = dr.GetString(4);
SqlCommand cmd2 = new SqlCommand("UPDATE Accounts SET loginAttempts = 0", conn);
int ordinal = 0;
//dr.Read();
ordinal = dr.GetOrdinal("userLevel");
userLevel = dr.GetInt32(ordinal);
dr.Close();
dr.Dispose();
cmd2.ExecuteNonQuery();
MessageBox.Show("Login Successful");
using (System.IO.StreamWriter file = new System.IO.StreamWriter(@"C:\Logs\ActivityLogs.txt", true))
{
string time = DateTime.Now.ToString();
file.WriteLine(time + " Account Name: " + lName + ", " + fName + " " + mName + " has logged in.");
}
}
else
{
using (SqlCommand cmd2 = new SqlCommand("Select userID from Accounts where userID = @userID", conn))
{
cmd2.Parameters.AddWithValue("@userID", un);
dr.Close();
dr.Dispose();
dr = cmd2.ExecuteReader();
int ordinal = 0;
string value = "";
if (dr.Read())
{
ordinal = dr.GetOrdinal("userID");
value = dr.GetString(ordinal);
if (value.Equals(un))
{
SqlCommand cmd3 = new SqlCommand("UPDATE Accounts SET loginAttempts += 1 WHERE userID = @un", conn);
cmd3.Parameters.AddWithValue("@un", un);
dr.Close();
dr.Dispose();
cmd3.ExecuteNonQuery();
cmd3.Dispose();
}
}
}
MessageBox.Show("User ID or Password is invalid");
return;
}
}
txtPass.Text = "";
txtUser.Text = "";
mn.Activated += new EventHandler(mn_Activated);
mn.FormClosed += new FormClosedEventHandler(mn_FormClosed);
if (userLevel == 2)
{
mn.permission = false;
}
else
{
mn.permission = true;
}
mn.Show();
}
else
{
DialogResult dr = MessageBox.Show("Due to multiple login attempts, your account has been locked. \nWould you like to unlock it?", "Account Recovery", MessageBoxButtons.YesNo);
switch (dr)
{
case DialogResult.Yes:
SqlConnection cnn = DBUtils.GetDBConnection();
cnn.Open();
string question = "", answer = "";
int ordinal = 0;
using (SqlCommand cmd = new SqlCommand("Select * from Accounts where userID = @userID", cnn))
{
cmd.Parameters.AddWithValue("@userID", txtUser.Text);
SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
ordinal = reader.GetOrdinal("securityQuestion");
question = reader.GetString(ordinal);
ordinal = reader.GetOrdinal("securityAnswer");
answer = reader.GetString(ordinal);
}
reader.Close();
}
string input = Interaction.InputBox(question, "Account Recovery", "");
if (input.Equals(answer))
{
SqlCommand cmd2 = new SqlCommand("UPDATE Accounts SET loginAttempts = 0 WHERE userID = @un", cnn);
cmd2.Parameters.AddWithValue("@un", txtUser.Text);
cmd2.ExecuteNonQuery();
cmd2.Dispose();
MessageBox.Show("Account has been unlocked");
}
else
{
MessageBox.Show("Your answer is wrong");
}
break;
case DialogResult.No: break;
}
}
//Validation codes END
}
}