public System.Collections.Generic.List<SWN.TTR.Repository.Dto.Employee> GetActiveEmployees()
{
using (SqlConnection cn = new SqlConnection(SqlHelper.OdsConnectionString))
{
using (SqlCommand cm = cn.CreateCommand())
{
cm.CommandType = CommandType.Text;
cm.CommandText = @"SELECT ISNULL(Preferred_FirstName,''), ISNULL(Last_Name,''), ISNULL(Work_Email_Address,''), ISNULL(Full_Network_Id,'')
FROM dbo.v_Entity_Employee_Active
WHERE LEN(Work_Email_Address) > 0
AND Work_Email_Address <> 'None';";
cm.Connection.Open();
using (SqlDataReader rdr = cm.ExecuteReader(CommandBehavior.CloseConnection))
{
List<Employee> employess = new List<Employee>();
while (rdr.Read())
{
Employee result = new Employee();
result.FirstName = rdr.GetString(0);
result.LastName = rdr.GetString(1);
result.EmailAddress = rdr.GetString(2);
result.NetworkId = rdr.GetString(3);
employess.Add(result);
}
return employess;
}
}
}
}
public Employee GetEmployeeByFullName(string fullname)
{
using (SqlConnection cn = new SqlConnection(SqlHelper.OdsConnectionString))
{
using (SqlCommand cm = cn.CreateCommand())
{
cm.CommandText = @"SELECT Preferred_FirstName, Last_Name, Work_Email_Address, Network_Id
FROM dbo.v_Entity_Employee_Active
WHERE LEN(Work_Email_Address) > 0
AND Work_Email_Address <> 'None'
AND Preferred_FirstName + ' ' + Last_Name = @Full_Name;";
cm.CommandType = CommandType.Text;
cm.Parameters.AddWithValue("@Full_Name", fullname);
cm.Connection.Open();
using (SqlDataReader rdr = cm.ExecuteReader(CommandBehavior.CloseConnection))
{
if (rdr.Read())
{
Employee employee = new Employee();
employee.FirstName = rdr.GetString(0);
employee.LastName = rdr.GetString(1);
employee.EmailAddress = rdr.GetString(2);
employee.NetworkId = rdr.GetString(3);
return employee;
}
else
return null;
}
}
}
}
public SWN.TTR.Repository.Dto.Employee GetEmployeeByNetworkID(string networkId)
{
using (SqlConnection cn = new SqlConnection(SqlHelper.OdsConnectionString))
{
using (SqlCommand cm = cn.CreateCommand())
{
cm.CommandText = "SELECT ISNULL(Preferred_FirstName,''), ISNULL(Last_Name,''), ISNULL(Work_Email_Address,''), ISNULL(Full_Network_Id,'') FROM dbo.v_Entity_Employee_By_HR_Alias WHERE Full_Network_ID = @Id;";
cm.CommandType = CommandType.Text;
cm.Parameters.AddWithValue("@Id", networkId);
cm.Connection.Open();
using (SqlDataReader rdr = cm.ExecuteReader(CommandBehavior.CloseConnection))
{
Employee result = new Employee();
// in case duplicate values come back, just pull the last one
while (rdr.Read())
{
result.FirstName = rdr.GetString(0);
result.LastName = rdr.GetString(1);
result.EmailAddress = rdr.GetString(2);
result.NetworkId = rdr.GetString(3);
}
return result;
}
}
}
}
public System.Collections.Generic.List<SWN.TTR.Repository.Dto.Employee> GetEmployeesByRole(string system, string subsystem, string role)
{
using (SqlConnection cn = new SqlConnection(SqlHelper.OdsConnectionString))
{
using (SqlCommand cm = cn.CreateCommand())
{
cm.CommandText = @"SELECT EA.[Preferred_FirstName], EA.[Last_Name], EA.[Work_Email_Address], EA.[Network_Id]
FROM dbo.Menu_User MU,
dbo.Menu_Option MO,
dbo.Menu_Option_Security MS,
dbo.v_Entity_Employee_Active EA
WHERE MS.[System_Id] = MO.[System_Id]
AND MS.[Option_Number] = MO.[Option_Number]
AND MS.[Sub_System_Id] = MO.[Sub_System_Id]
AND MS.[Capability_Group] = MU.[Capability_Group]
AND MO.[Sub_System_Id] = MU.[Sub_System_Id]
AND MO.[System_Id] = MU.[System_Id]
AND MU.[User_Id] = EA.[Network_Id]
AND MU.[System_Id] = @System_Id
AND MU.[Sub_System_Id] = @Sub_System_Id
AND MU.Capability_Group = @Role
ORDER BY EA.[Last_Name];";
cm.CommandType = CommandType.Text;
cm.Parameters.AddWithValue("@System_Id", system);
cm.Parameters.AddWithValue("@Sub_System_Id", subsystem);
cm.Parameters.AddWithValue("@Role", role);
cm.Connection.Open();
using (SqlDataReader rdr = cm.ExecuteReader(CommandBehavior.CloseConnection))
{
List<Employee> result = new List<Employee>();
while (rdr.Read())
{
Employee employee = new Employee();
employee.FirstName = rdr.GetString(0);
employee.LastName = rdr.GetString(1);
employee.EmailAddress = rdr.GetString(2);
employee.NetworkId = rdr.GetString(3);
result.Add(employee);
}
return result;
}
}
}
}