private void saveSignIn()
{
diff = 0;
attendance_id = txtId.Text + " " + dayDate + " " + timeIn;
try
{
//create the command
using (command = connection.GetCommand("select p.person_id pid, p.national_id nid " +
"from dbo.person p where p.national_id=@national_id", CommandType.Text))
{
// add the parameter
command.AddParameter("@national_id", txtId.Text, SqlDbType.VarChar);
command.AddParameter("@time_in", timeIn, SqlDbType.DateTime);
command.AddParameter("@dayDate", dayDate, SqlDbType.Date);
command.AddParameter("@action", 1, SqlDbType.Int);
command.AddParameter("@attendance_id", attendance_id, SqlDbType.VarChar);
// initialize the reader and execute the command
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
person_id = Convert.ToString(reader["pid"]);
//national_id = Convert.ToString(reader["nid"]);
}
command.AddParameter("@person_id", person_id, SqlDbType.VarChar);
reader.Close();
command.CommandText = "INSERT INTO dbo.time_attendance (person_id, national_id, time_in, dayDate, action,attendance_id) " +
"VALUES (@person_id, @national_id, @time_in, @dayDate, @action,@attendance_id)";
int i = command.ExecuteNonQuery();
if (i > 0)
{
new OpenForms().openModalForm(new Welcome(txtName.Text));
data.getAttendanceRecords();
clearConrols();
}
else
{
Messaging.information("Log in failed!", title);
}
}
else
{
Messaging.warning("Person not found!", title);
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), title, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public static string getReportHeader()
{
SqlConnection connection = DBHelper.GetConnection();
SqlCommand command = connection.GetCommand(DatabaseManipulate.strOrganization, CommandType.Text);
string header = "";
try
{
using (command)
{
// initialize the reader and execute the command
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
header = Convert.ToString(reader["Name"]);
header += "\nP.O. Box" + Convert.ToString(reader["Addres"]);
header += "\n" + Convert.ToString(reader["City"]) + ", " + Convert.ToString(reader["Country"]);
if (!Convert.ToString(reader["Mobile"]).Equals(""))
{
header += "\nMobile: " + Convert.ToString(reader["Mobile"]);
}
if (!Convert.ToString(reader["Landline"]).Equals(""))
{
header += "\nLandline: " + Convert.ToString(reader["Landline"]);
}
if (!Convert.ToString(reader["Email"]).Equals(""))
{
header += "\nEmail: " + Convert.ToString(reader["Email"]);
}
if (!Convert.ToString(reader["Website"]).Equals(""))
{
header += "\nWebsite: " + Convert.ToString(reader["Website"]);
}
}
reader.Close();
}
else
{
Messaging.information("No header details found!", title);
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), title, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
return(header);
}
public void getAttendanceRecords()
{
command = connection.GetCommand("Select p.national_id,p.surname,p.first_name, p.other_name, " +
"t.time_in,t.time_out, t.minutes from dbo.person p, dbo.time_attendance t where " +
"t.person_id=p.person_id and t.dayDate=@dayDate", CommandType.Text);
DateTime dt = DateTime.Now;
string strDate = dt.Date.ToString("yyyy - MM - dd");
command.AddParameter("@dayDate", strDate, SqlDbType.VarChar);
txtDate.Text = strDate;
using (command)
{
// initialize the reader and execute the command
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
int i = 0;
lstvStaffDetails.Items.Clear();
while (reader.Read())
{
// Create a ListViewItem object for evey item that you wish to add the ListView.
string[] lv = new String[5];
//lv[0] = Convert.ToString(reader["staff_no"]);
lv[0] = Convert.ToString(reader["national_id"]);
lv[1] = Convert.ToString(reader["surname"]) + " " + Convert.ToString(reader["first_name"])
+ " " + Convert.ToString(reader["other_name"]);
lv[2] = Convert.ToString(reader["time_in"]);
lv[3] = Convert.ToString(reader["time_out"]);
lv[4] = Convert.ToString(reader["minutes"]);
lstvStaffDetails.Items.Add(new ListViewItem(lv, i));
i++;
}
reader.Close();
ListViewRowColors.recolorListItems(lstvStaffDetails);
}
else
{
Messaging.information("No attendance details for this day is found found!", title);
}
}
}
}
public void searchPerson(string id)
{
command = connection.GetCommand("SELECT * FROM dbo.person WHERE national_id = @_id", CommandType.Text);
command.AddParameter("@_id", id, SqlDbType.VarChar);
using (command)
{
// initialize the reader and execute the command
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
string[] person = new string[14];
while (reader.Read())
{
person[0] = Convert.ToString(reader["person_id"]);
person[1] = Convert.ToString(reader["national_id"]);
person[2] = Convert.ToString(reader["surname"]);
person[3] = Convert.ToString(reader["first_name"]);
person[4] = Convert.ToString(reader["other_name"]);
person[5] = Convert.ToString(reader["address"]);
person[6] = Convert.ToString(reader["city"]);
person[7] = Convert.ToString(reader["country"]);
person[8] = Convert.ToString(reader["email"]);
person[9] = Convert.ToString(reader["landline"]);
person[10] = Convert.ToString(reader["mobile"]);
person[11] = Convert.ToString(reader["nationality"]);
//txtPostalCode.Text = Convert.ToString(reader["postal_code"]);
person[12] = Convert.ToString(reader["gender"]);
person[13] = Convert.ToString(reader["marital_status"]);
}
reader.Close();
fillFields(person);
//ListViewRowColors.recolorListItems(lstvStaffDetails);
}
else
{
Messaging.information("No staff details found!", title);
}
}
}
}
private void saveSignOut()
{
try
{
//create the command
using (command = connection.GetCommand("select * from dbo.time_attendance p where " +
"p.national_id=@national_id and p.action = 1 and dayDate=@dayDate", CommandType.Text))
{
command.AddParameter("@national_id", txtId.Text, SqlDbType.VarChar);
command.AddParameter("@action", 2, SqlDbType.Int);
command.AddParameter("@time_out", timeOut, SqlDbType.DateTime);
command.AddParameter("@minutes", diff, SqlDbType.Float);
command.AddParameter("@attendance_id", attendance_id, SqlDbType.VarChar);
command.AddParameter("@dayDate", dayDate, SqlDbType.Date);
command.CommandText = "UPDATE dbo.time_attendance SET " +
"time_out = @time_out, " +
"minutes = @minutes, " +
"action = @action " +
"WHERE attendance_id = @attendance_id";
int i = command.ExecuteNonQuery();
if (i > 0)
{
new OpenForms().openModalForm(new GoodBye(txtName.Text));
data.getAttendanceRecords();
clearConrols();
}
else
{
Messaging.information("Log out failed!", title);
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), title, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void delete(string id)
{
try
{
command = connection.GetCommand("SELECT * FROM dbo.person WHERE national_id = @national_id", CommandType.Text);
//create the command
command.AddParameter("@national_id", id, SqlDbType.VarChar);
using (command)
{
// initialize the reader and execute the command
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
reader.Close();
command.CommandText = "DELETE FROM dbo.person WHERE national_id = @national_id";
int i = command.ExecuteNonQuery();
if (i > 0)
{
searchPerson("");
Messaging.information("Deleted successfully!", title);
clearControls();
}
}
else
{
Messaging.warning("No staff details found!", title);
}
}
studentList.searchPerson("");
}
}
catch (Exception ex)
{
Messaging.error("Error! Failed deleting!\n" + ex.ToString(), title);
}
}
private void saveSignIn(string personId, string nationalId, string name)
{
diff = 0;
attendance_id = nationalId + " " + dayDate + " " + timeIn;
try
{
//create the command
using (command = connection.GetCommand("INSERT INTO dbo.time_attendance (person_id, national_id, time_in, dayDate, action,attendance_id) " +
"VALUES (@person_id, @national_id, @time_in, @dayDate, @action,@attendance_id)", CommandType.Text))
{
// add the parameter
command.AddParameter("@national_id", nationalId, SqlDbType.VarChar);
command.AddParameter("@time_in", timeIn, SqlDbType.DateTime);
command.AddParameter("@dayDate", dayDate, SqlDbType.Date);
command.AddParameter("@action", 1, SqlDbType.Int);
command.AddParameter("@attendance_id", attendance_id, SqlDbType.VarChar);
command.AddParameter("@person_id", personId, SqlDbType.VarChar);
int i = command.ExecuteNonQuery();
if (i > 0)
{
new OpenForms().openModalForm(new Welcome(name));
this.getAttendanceRecords();
}
else
{
Messaging.information("Log in failed!", title);
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), title, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public void searchPerson(string id)
{
if (!id.Equals(""))
{
if (optSearchIDname.Checked)
{
command = connection.GetCommand("SELECT * FROM dbo.person WHERE national_id = @_id", CommandType.Text);
command.AddParameter("@_id", id, SqlDbType.VarChar);
}
else if (optSearchStaffNumber.Checked)
{
command = connection.GetCommand("Select * from dbo.person p, dbo.person_job pj " +
"where p.person_id=pj.person_id and pj.staff_no = @_id", CommandType.Text);
command.AddParameter("@_id", id, SqlDbType.VarChar);
}
}
else
{
//create the command
command = connection.GetCommand("SELECT * FROM dbo.person order by surname asc", CommandType.Text);
}
using (command)
{
// initialize the reader and execute the command
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
int i = 0;
lstvStaffDetails.Items.Clear();
idDict.Clear();
person_idDict.Clear();
while (reader.Read())
{
// Create a ListViewItem object for evey item that you wish to add the ListView.
string[] lv = new String[4];
lv[0] = Convert.ToString(i + 1);//Convert.ToString(reader["designation"]);
lv[1] = Convert.ToString(reader["national_id"]);
lv[2] = Convert.ToString(reader["surname"]) + " " + Convert.ToString(reader["first_name"])
+ " " + Convert.ToString(reader["other_name"]);
lv[3] = Convert.ToString(reader["person_id"]);
//lv[3] = Convert.ToString(reader["designation"]);
lstvStaffDetails.Items.Add(new ListViewItem(lv, i));;
idDict.Add(Convert.ToString(reader["national_id"]), lv);
person_idDict.Add(Convert.ToString(reader["national_id"]), Convert.ToString(reader["person_id"]));
i++;
}
reader.Close();
ListViewRowColors.recolorListItems(lstvStaffDetails);
}
else
{
Messaging.information("No staff details found!", title);
}
}
}
}
private void save()
{
string msg = "";
try
{
if (getTextFieldValues() == true)
{
//create the command
using (command = connection.GetCommand("SELECT national_id = @national_id FROM dbo.person WHERE national_id = @national_id", CommandType.Text))
{
// add the parameter
command.AddParameter("@personId", personId, SqlDbType.VarChar);
command.AddParameter("@surname", surname, SqlDbType.VarChar);
command.AddParameter("@first_name", firstName, SqlDbType.VarChar);
command.AddParameter("@other_name", otherName, SqlDbType.VarChar);
command.AddParameter("@national_id", nationalId, SqlDbType.VarChar);
command.AddParameter("@gender", gender, SqlDbType.VarChar);
command.AddParameter("@dob", dob, SqlDbType.Date);
command.AddParameter("@marital_status", maritalStatus, SqlDbType.VarChar);
command.AddParameter("@nationality", nationality, SqlDbType.VarChar);
command.AddParameter("@country", country, SqlDbType.VarChar);
command.AddParameter("@city", city, SqlDbType.VarChar);
command.AddParameter("@address", address, SqlDbType.VarChar);
command.AddParameter("@path", imagePath, SqlDbType.VarChar);
command.AddParameter("@email", email, SqlDbType.VarChar);
command.AddParameter("@mobile", mobile, SqlDbType.VarChar);
command.AddParameter("@landline", landline, SqlDbType.VarChar);
// initialize the reader and execute the command
using (SqlDataReader reader = command.ExecuteReader())
{
if (isNew)
{
if (!reader.HasRows)
{
msg = "Saving";
command.CommandText = "INSERT INTO dbo.person (person_id, surname, first_name, other_name, " +
"national_id, gender, dob, marital_status, nationality, country, city, address, " +
"email,mobile,landline) " +
"VALUES (@personId, @surname, @first_name, @other_name, @national_id, @gender, @dob, " +
"@marital_status, @nationality, @country,@city,@address,@email, @mobile, " +
"@landline)";
reader.Close();
int i = command.ExecuteNonQuery();
if (i > 0)
{
Messaging.information(msg + " successfully!", title);
clearControls();
}
}
}
else
{
msg = "Updating";
reader.Close();
command.CommandText = "UPDATE dbo.person SET " +
"surname= @surname," +
"first_name = @first_name, " +
"other_name= @other_name," +
"national_id= @national_id," +
"gender= @gender," +
"dob= @dob," +
"marital_status= @marital_status," +
"nationality= @nationality," +
"country= @country," +
"city= @city," +
"address= @address," +
"email= @email," +
"mobile= @mobile," +
"landline= @landline " +
"WHERE person_id = @personId";
int i = command.ExecuteNonQuery();
if (i > 0)
{
Messaging.information(msg + " successfully!", title);
}
}
}
savePhoto();
studentList.searchPerson("");
}
}
}
catch (Exception ex)
{
Messaging.error("Error! " + msg + " Failed!\n" + ex.ToString(), title);
}
}