//AR 7/9/19 Merged from AbsenceTypeDA
// Gets absences for a given team -- Check points for duplication
public static List <IAbsenceDO> GetAbsencesByTeamID(int iTeamID)
{
List <IAbsenceDO> listAbsenceTypesByTeam = new List <IAbsenceDO>();
try
{
using (SqlConnection con = new SqlConnection(_ConnectionString))
{
using (SqlCommand getAbsenceTypesByTeamComm = new SqlCommand("sp_GetAbsencesByTeamID", con))
{
try
{
getAbsenceTypesByTeamComm.CommandType = CommandType.StoredProcedure;
getAbsenceTypesByTeamComm.CommandTimeout = 35;
getAbsenceTypesByTeamComm.Parameters.AddWithValue("@TeamId", iTeamID);
con.Open();
using (SqlDataReader reader = getAbsenceTypesByTeamComm.ExecuteReader())
{
while (reader.Read())
{
IAbsenceDO absence = new AbsenceDO();
absence.PointBankID = (int)reader["PointBankID"];
absence.EmployeeName = reader["EmployeeName"].ToString();
absence.Name = reader["AbsenceType"].ToString();
absence.Point = (decimal)reader["Point"];
var active = (int)reader["Active"];
absence.Active = active != 0;
absence.TeamID_FK = (int)reader["TeamID_FK"];
listAbsenceTypesByTeam.Add(absence);
}
}
}
catch (Exception ex)
{
ErrorLogger.LogError(ex, "GetAbsencesByTeamID", "nothing");
}
}
}
}
catch (Exception ex)
{
ErrorLogger.LogError(ex, "GetAbsencesByTeamID", "nothing");
}
return(listAbsenceTypesByTeam);
}
public static List <IAbsenceDO> ViewAbsencesByUserID(int userID)
{
var listOfAbsenceDOs = new List <IAbsenceDO>();
try
{
using (SqlConnection conn = new SqlConnection(_ConnectionString))
{
using (SqlCommand viewComm = new SqlCommand("sp_GetPointsByUserID", conn))
{
viewComm.CommandType = CommandType.StoredProcedure;
viewComm.CommandTimeout = 35;
viewComm.Parameters.AddWithValue("@UserID", userID);
conn.Open();
using (SqlDataReader reader = viewComm.ExecuteReader())
{
while (reader.Read())
{
IAbsenceDO absence = new AbsenceDO();
absence.AbsenceTypeID = reader.GetInt32(reader.GetOrdinal("AbsenceTypeID"));
absence.Comments = reader["Comment"].ToString();
absence.Point = Convert.ToDecimal(reader["Point"]);
absence.Active = Convert.ToBoolean((int)reader["Active"]);
absence.TeamID_FK = reader.GetInt32(reader.GetOrdinal("TeamID"));
absence.AbsenceDate = Convert.ToDateTime(reader["AbsenceDate"]);
absence.AbsentUserID = reader.GetInt32(reader.GetOrdinal("UserID"));
absence.TeamMgtID = reader.GetInt32(reader.GetOrdinal("TeamMgtID"));
absence.EmployeeName = reader["EmployeeName"].ToString();
//Merged JC Method AR 7/9/19
absence.TeamName = reader["Team"].ToString();
absence.Name = reader["Type"].ToString();
absence.PointBankID = (int)reader["PointBankID"];
//------------
listOfAbsenceDOs.Add(absence);
}
}
}
}
}
catch (Exception ex)
{
ErrorLogger.LogError(ex, "ViewAbsencesByUserID", "nothing");
}
return(listOfAbsenceDOs);
}
// Gets selected absence by PointBankID
public static IAbsenceDO GetAbsenceByID(int pointBankID)
{
var iAbsence = new AbsenceDO();
try
{
using (SqlConnection connection = new SqlConnection(_ConnectionString))
{
using (SqlCommand command = new SqlCommand("sp_GetAbsenceByPointBankID", connection))
{
try
{
command.CommandType = CommandType.StoredProcedure;
command.CommandTimeout = 35;
command.Parameters.AddWithValue("@PointBankID", pointBankID);
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
var absenceDO = new AbsenceDO();
absenceDO.PointBankID = (int)reader["PointBankID"];
absenceDO.EmployeeName = reader["Employee"].ToString();
absenceDO.Name = reader["AbsenceType"].ToString();
absenceDO.Point = (decimal)reader["Point"];
}
}
}
catch (Exception ex)
{
ErrorLogger.LogError(ex, "GetAbsenceByID", "nothing");
}
}
}
}
catch (Exception ex)
{
ErrorLogger.LogError(ex, "GetAbsenceByID", "nothing");
}
return(iAbsence);
}
public static IAbsenceDO MapAbsencePOtoDO(AbsencePO absencePO)
{
IAbsenceDO oAbsence = new AbsenceDO();
oAbsence.AbsenceTypeID = absencePO.AbsenceTypeID;
oAbsence.Name = absencePO.Name;
oAbsence.TeamName = absencePO.TeamName;
oAbsence.Point = absencePO.Point;
oAbsence.Active = absencePO.Active;
oAbsence.TeamID_FK = absencePO.TeamID_FK;
oAbsence.AbsenceDate = absencePO.AbsenceDate;
oAbsence.AbsentUserID = absencePO.AbsentUserID;
oAbsence.Comments = absencePO.Comments;
oAbsence.RunningTotal = absencePO.RunningTotal;
oAbsence.TeamMgtID = absencePO.TeamMgtID;
oAbsence.EmployeeName = absencePO.EmployeeName;
oAbsence.PointBankID = absencePO.PointBankID;
oAbsence.AbsenceType = absencePO.AbsenceType;
return(oAbsence);
}
public static List <IAbsenceDO> CalculateUserPoints(List <IAbsenceDO> iAbsence)
{
// TODO: Need to add a way to pass the ID to this method based off of session
var userPoints = new List <IAbsenceDO>();
decimal runningTotal = 0;
var grouping = from x in iAbsence
group x by x.Point into points
select points;
foreach (IAbsenceDO user in grouping)
{
userPoints.Add(user);
}
var query = userPoints
.OrderBy(i => i.Point)
.Select(i =>
{
runningTotal += i.Point;
return(new
{
RunningTotal = runningTotal
});
});
var calculatedPoints = new List <IAbsenceDO>();
foreach (var item in query)
{
var runningTotalList = new AbsenceDO()
{
RunningTotal = item.RunningTotal
};
calculatedPoints.Add(runningTotalList);
}
return(calculatedPoints);
}
public List <IAbsenceDO> GetAllAbsenceTypes()
{
var listOfAbsenceTypes = new List <IAbsenceDO>();
try
{
using (SqlConnection conn = new SqlConnection(_ConnectionString))
{
using (SqlCommand getComm = new SqlCommand("sp_GetAllAbsenceTypes", conn))
{
getComm.CommandType = CommandType.StoredProcedure;
getComm.CommandTimeout = 35;
conn.Open();
using (SqlDataReader reader = getComm.ExecuteReader())
{
while (reader.Read())
{
IAbsenceDO absenceType = new AbsenceDO();
absenceType.AbsenceTypeID = (int)reader["AbsenceTypeID"];
absenceType.Name = reader["Name"].ToString();
var active = (int)reader["Active"];
absenceType.Active = active != 0;
listOfAbsenceTypes.Add(absenceType);
}
}
}
}
}
catch (Exception ex)
{
ErrorLogger.LogError(ex, "GetAbsenceTypes", "nothing");
}
return(listOfAbsenceTypes);
}