// CONSTRUCTOR
/// <summary>
/// constructor for adress
/// </summary>
/// <param name="propertyName">the property name</param>
/// <param name="propertyNumber">the property number</param>
/// <param name="postCode">the postcode</param>
public Address(string propertyName, int propertyNumber,
PostCode postCode)
{
this.propertyName = propertyName;
this.propertyNumber = propertyNumber;
this.postCode = postCode;
}
/// <summary>
/// gets all hourly paid employees
/// with associated address, postcode and supervisor objects
/// </summary>
/// <returns>list of employees</returns>
public List<Employee> GetAllHourlyPaid()
{
List<Employee> result = new List<Employee>();
StringBuilder sqlb = new StringBuilder(
"SELECT employeeid,name,username,supervisor, phonenumber, propertyname, propertynumber, postcode ");
sqlb.Append("FROM EMPLOYEES e, ADDRESSES a ");
sqlb.Append("WHERE e.addressID = a.addressID ");
sqlb.Append(@"AND discriminator = 'h'");
string sql = sqlb.ToString();
SqlCeCommand comm = new SqlCeCommand(sql, conn);
if (conn.State == ConnectionState.Closed) conn.Open();
SqlCeDataReader reader = comm.ExecuteReader(CommandBehavior.CloseConnection);
while(reader.Read())
{
Employee emp = null;
int employeeID = reader.GetInt32(0);
string name = reader.GetString(1);
string username = reader.GetString(2);
int supervisor = -1;
if(!reader.IsDBNull(3))
supervisor = reader.GetInt32(3);
string phonenumber = reader.GetString(4);
string propertyname = reader.GetString(5);
int propertynumber = reader.GetInt32(6);
string postcode = reader.GetString(7);
if (identityMap.ContainsKey(employeeID))
{
emp = identityMap[employeeID];
}
else
{
PostCode pc = new PostCode(postcode);
Address ad = new Address(propertyname, propertynumber, pc);
emp = new HourlyPaidEmployee(employeeID, name, username, ad, phonenumber);
emp.Supervisor = GetById(supervisor);
identityMap.Add(employeeID, emp);
}
result.Add(emp);
}
//conn.Close();
return result;
}
/// <summary>
/// gets specified employee with associated address and postcode objects
/// </summary>
/// <param name="id">the employee id</param>
/// <returns>an employee object</returns>
public Employee GetById(int id)
{
Employee result = null;
// check whether target object is already loaded
if (identityMap.ContainsKey(id))
{
result = identityMap[id];
}
else
{
StringBuilder sqlb = new StringBuilder(
"SELECT employeeid,name,username,phonenumber, discriminator, paygrade, propertyname, propertynumber, postcode ");
sqlb.Append("FROM EMPLOYEES e, ADDRESSES a ");
sqlb.Append("WHERE e.addressID = a.addressID ");
sqlb.Append("AND employeeID = ");
sqlb.Append(id.ToString());
string sql = sqlb.ToString();
SqlCeCommand comm = new SqlCeCommand(sql, conn);
if(conn.State==ConnectionState.Closed) conn.Open();
SqlCeDataReader reader = comm.ExecuteReader(CommandBehavior.CloseConnection);
if (reader.Read())
{
int employeeID = reader.GetInt32(0);
string name = reader.GetString(1);
string username = reader.GetString(2);
string phonenumber = reader.GetString(3);
string discriminator = reader.GetString(4);
int paygrade = 0;
if(!reader.IsDBNull(5))
paygrade = reader.GetInt32(5);
string propertyname = reader.GetString(6);
int propertynumber = reader.GetInt32(7);
string postcode = reader.GetString(8);
PostCode pc = new PostCode(postcode);
Address ad = new Address(propertyname, propertynumber, pc);
if (discriminator.Equals("S"))
{
// need to include pay grade in database schema and adjust query
result = new SalariedEmployee(employeeID, name, username, ad, phonenumber, paygrade);
}
else if (discriminator.Equals("H"))
{
result = new HourlyPaidEmployee(employeeID, name, username, ad, phonenumber);
}
else
{
throw new Exception("Invalid employee type");
}
}
//conn.Close();
identityMap.Add(id, result);
}
return result;
}
