/// <summary>
/// Verifies a person's login information and retrieves full name and role
/// </summary>
/// <param name="username">As a string</param>
/// <param name="password">As a string</param>
/// <returns></returns>
public DataTable GetLogin(string username, string password)
{
DataTable dt = new DataTable();
string selectStatement =
"SELECT l.personID, l.userName, l.password, (p.first_name + ' ' + p.last_name) AS 'name', p.role" +
"FROM login l JOIN person p ON p.id = l.personID" +
"WHERE username = @username AND password = @password";
using (SqlConnection connection = ScheduleManager_DB_Connection.GetConnection())
{
connection.Open();
SqlCommand sqlCommand = new SqlCommand(selectStatement, connection);
sqlCommand.Parameters.AddWithValue("@username", username);
sqlCommand.Parameters.AddWithValue("@password", password);
SqlDataReader reader = sqlCommand.ExecuteReader();
dt.Columns.Add("personID", typeof(int));
dt.Columns.Add("userName", typeof(string));
dt.Columns.Add("password", typeof(string));
dt.Columns.Add("name", typeof(string));
dt.Columns.Add("role", typeof(string));
dt.Load(reader);
}
return(dt);
}
public List <Shift> GetShifts()
{
SqlConnection connection = ScheduleManager_DB_Connection.GetConnection();
List <Shift> shiftList = new List <Shift>();
string selectStatement = "SELECT s.id, s.scheduleShiftId, s.personId, s.positionId, sh.scheduledStartTime, sh.scheduledEndTime, " +
"sh.scheduledLunchBreakStartTime, sh.scheduledLunchBreakEndTime, sh.actualStartTime, sh.actualEndTime, sh.actualLunchBreakStart, " +
"sh.acutalLunchBreakEnd, p.first_name, p.last_name, ps.position_title " +
"FROM shift AS s " +
"JOIN shiftHours AS sh ON s.scheduleShiftId = sh.id " +
"JOIN person AS p ON s.personId = p.id " +
"JOIN position AS ps ON s.positionId = ps.id";
using (connection)
{
connection.Open();
using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection))
{
using (SqlDataReader reader = selectCommand.ExecuteReader())
{
while (reader.Read())
{
Shift shift = new Shift();
shift.personLastName = reader["lastName"].ToString();
shift.personFirstName = reader["firstName"].ToString();
shiftList.Add(shift);
}
}
}
}
return(shiftList);
}
/// <summary>
/// this method returns all employees
/// </summary>
/// <returns></returns>
public List <Person> GetDesiredPersons(string whereClause)
{
List <Person> persons = new List <Person>();
string desiredEmployees = this.selectedPersons + whereClause;
using (SqlConnection connection = ScheduleManager_DB_Connection.GetConnection())
{
connection.Open();
using (SqlCommand command = new SqlCommand(desiredEmployees, connection))
{
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Person person = new Person();
person.Id = (int)reader["id"];
person.LastName = reader["last_name"].ToString();
person.FirstName = reader["first_name"].ToString();
person.DateOfBirth = (DateTime)reader["date_of_birth"];
person.Ssn = (char)reader["ssn"];
person.Gender = reader["gender"].ToString();
person.StreetAddress = reader["street_address"].ToString();
person.Phone = reader["phone"].ToString();
person.Zipcode = reader["zipcode"].ToString();
person.Username = reader["username"].ToString();
person.Password = (byte[])reader["password"];
person.RoleId = (int)reader["roleId"];
person.StatusId = (int)reader["statusId"];
persons.Add(person);
}
}
return(persons);
}
}
}
/// <summary>
/// This method adds an accepted person to the database
///
///
/// Need to set in the View an appropiate auto generated-- password - roleId - StatusId -
///
/// </summary>
/// <param name="addPerson"></param>
public void AddPerson(Person addPerson)
{
int addedPersonId = -1;
try
{
using (SqlConnection connection = ScheduleManager_DB_Connection.GetConnection())
{
connection.Open();
using (SqlTransaction transaction = connection.BeginTransaction())
{
string insertPerson = "INSERT person(" +
"[last_name]" +
" ,[first_name]" +
" ,[date_of_birth]" +
" ,[ssn]" +
" ,[gender]" +
" ,[street_address]" +
" ,[phone]" +
" ,[zipcode]" +
" ,[username]" +
" ,[password]" +
" ,[roleId]" +
" ,[statusId])" +
" VALUES(" +
" @last_name" +
", @first_name" +
", @date_of_birth" +
", @ssn" +
", @gender" +
", @street_address" +
", @phone" +
", @zipcode" +
", @username" +
", HASHBYTES('SHA2_256', @password)" +
", @roleId" +
", @statusId)";
using (SqlCommand command = new SqlCommand(insertPerson, connection))
{
command.Parameters.Add(new SqlParameter("@last_name", addPerson.LastName));
command.Parameters.Add(new SqlParameter("@first_name", addPerson.FirstName));
command.Parameters.Add(new SqlParameter("@date_of_birth", addPerson.DateOfBirth));
command.Parameters.Add(new SqlParameter("@ssn", addPerson.Ssn));
command.Parameters.Add(new SqlParameter("@gender", addPerson.Gender));
command.Parameters.Add(new SqlParameter("@street_address", addPerson.StreetAddress));
command.Parameters.Add(new SqlParameter("@phone", addPerson.Phone));
command.Parameters.Add(new SqlParameter("@zipcode", addPerson.Zipcode));
command.Parameters.Add(new SqlParameter("@username", addPerson.Username));
command.Parameters.Add(new SqlParameter("@password", addPerson.Password));
command.Parameters.Add(new SqlParameter("@roleId", addPerson.Password));
command.Parameters.Add(new SqlParameter("@statusID", addPerson.Password));
string selectStatement = "SELECT IDENT_CURRENT('Person') FROM Person";
using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection))
{
selectCommand.Transaction = transaction;
addedPersonId = Convert.ToInt32(selectCommand.ExecuteScalar());
}
transaction.Commit();
}
}
}
}
catch (Exception ex)
{
//Message Box won't work - thats wierd
//MessageBox.Show(ex.Message, "Error");
}
}