/// <summary>
/// Creating response object after reading Employee(s) details from database
/// </summary>
/// <param name="query"></param>
/// <param name="parameters"></param>
/// <returns>List<EmployeeQueryModel></returns>
public List <EmployeeQueryModel> ReadEmployeeDetails(string query, Dictionary <string, string> parameters)
{
List <EmployeeQueryModel> employeeList = new List <EmployeeQueryModel>();
try
{
//Read the employee details from the DB
using (IDataReader dataReader = ExecuteDataReader(query, ParameterHelper.CreateSqlParameter(parameters)))
{
if (dataReader != null)
{
while (dataReader.Read())
{
DataTable dataTable = dataReader.GetSchemaTable();
EmployeeQueryModel employeeResponse = new EmployeeQueryModel
{
UserId = (dataTable.Select("ColumnName = 'userId'").Count() == 1) ? (int?)dataReader["userId"] : null,
SupervisorId = (dataTable.Select("ColumnName = 'supervisorId'").Count() == 1) ? (int?)dataReader["supervisorId"] : null,
TotalEmployees = (dataTable.Select("ColumnName = 'employeeCount'").Count() == 1) ? (int?)dataReader["employeeCount"] : null,
EmployeeName = (dataTable.Select("ColumnName = 'employeeName'").Count() == 1) ? Convert.ToString(dataReader["employeeName"]) : null,
Role = (dataTable.Select("ColumnName = 'Role'").Count() == 1) ? Convert.ToString(dataReader["Role"]) : null,
UserName = (dataTable.Select("ColumnName = 'UserName'").Count() == 1) ? Convert.ToString(dataReader["UserName"]) : null,
AlternateName = (dataTable.Select("ColumnName = 'UserName2'").Count() == 1) ? Convert.ToString(dataReader["UserName2"]) : null,
Email = (dataTable.Select("ColumnName = 'email'").Count() == 1) ? Convert.ToString(dataReader["email"]) : null,
Address = (dataTable.Select("ColumnName = 'Address'").Count() == 1) ? Convert.ToString(dataReader["Address"]) : null,
Phone = (dataTable.Select("ColumnName = 'Phone'").Count() == 1) ? Convert.ToString(dataReader["Phone"]) : null,
SupervisorName = (dataTable.Select("ColumnName = 'SupervisorName'").Count() == 1) ? Convert.ToString(dataReader["SupervisorName"]) : null,
UserCreatedDate = (dataTable.Select("ColumnName = 'DateCreated'").Count() == 1) ? Convert.ToString(dataReader["DateCreated"]) : null,
Userpermission = (dataTable.Select("ColumnName = 'UserPerms'").Count() == 1) ? (bool?)(dataReader["UserPerms"]) : null,
SettingsPermission = (dataTable.Select("ColumnName = 'settingsperms'").Count() == 1) ? (bool?)dataReader["settingsperms"] : null,
CoursePermission = (dataTable.Select("ColumnName = 'courseperms'").Count() == 1) ? (bool?)dataReader["courseperms"] : null,
TranscriptPermission = (dataTable.Select("ColumnName = 'Transcriptperms'").Count() == 1) ? (bool?)dataReader["Transcriptperms"] : null,
CompanyPermission = (dataTable.Select("ColumnName = 'companyperms'").Count() == 1) ? (bool?)dataReader["companyperms"] : null,
ForumPermission = (dataTable.Select("ColumnName = 'forumperms'").Count() == 1) ? (bool?)dataReader["forumperms"] : null,
ComPermission = (dataTable.Select("ColumnName = 'comperms'").Count() == 1) ? (bool?)dataReader["comperms"] : null,
ReportsPermission = (dataTable.Select("ColumnName = 'reportsperms'").Count() == 1) ? (bool?)dataReader["reportsperms"] : null,
AnnouncementPermission = (dataTable.Select("ColumnName = 'announcementperms'").Count() == 1) ? (bool?)dataReader["announcementperms"] : null,
SystemPermission = (dataTable.Select("ColumnName = 'systemperms'").Count() == 1) ? (bool?)dataReader["systemperms"] : null
};
// Adding each employee details in array list
employeeList.Add(employeeResponse);
}
}
else
{
return(null);
}
}
return(employeeList);
}
catch (Exception readEmployeeDetailsException)
{
LambdaLogger.Log(readEmployeeDetailsException.ToString());
return(null);
}
}
/// <summary>
/// Creating response object after reading workbook(s) details from database
/// </summary>
/// <param name="query"></param>
/// <param name="parameters"></param>
/// <returns>WorkbookModel</returns>
public List <WorkbookModel> ReadWorkBookDetails(string query, Dictionary <string, string> parameters)
{
List <WorkbookModel> workbookList = new List <WorkbookModel>();
try
{
//Read the data from the database
using (IDataReader dataReader = ExecuteDataReader(query, ParameterHelper.CreateSqlParameter(parameters)))
{
if (dataReader != null)
{
while (dataReader.Read())
{
DataTable dataTable = dataReader.GetSchemaTable();
//Get the workbook details from the database
TaskModel taskComment = (dataTable.Select("ColumnName = 'Attempt_Comment'").Count() == 1) ? JsonConvert.DeserializeObject <TaskModel>(Convert.ToString(dataReader["Attempt_Comment"])) : null;
WorkbookModel workbookResponse = new WorkbookModel
{
EmployeeName = (dataTable.Select("ColumnName = 'employeeName'").Count() == 1) ? Convert.ToString(dataReader["employeeName"]) : (dataTable.Select("ColumnName = 'Employee_Full_Name'").Count() == 1) ? Convert.ToString(dataReader["Employee_Full_Name"]) : null,
WorkBookName = (dataTable.Select("ColumnName = 'workbookName'").Count() == 1) ? Convert.ToString(dataReader["workbookName"]) : (dataTable.Select("ColumnName = 'OJT_Name'").Count() == 1) ? Convert.ToString(dataReader["OJT_Name"]) : null,
Description = (dataTable.Select("ColumnName = 'Description'").Count() == 1) ? Convert.ToString(dataReader["Description"]) : null,
WorkbookCreated = (dataTable.Select("ColumnName = 'datecreated'").Count() == 1) ? Convert.ToString(dataReader["datecreated"]) : null,
WorkbookEnabled = (dataTable.Select("ColumnName = 'isEnabled'").Count() == 1) ? (bool?)(dataReader["isEnabled"]) : null,
WorkBookId = (dataTable.Select("ColumnName = 'Id'").Count() == 1) ? (dataReader["Id"] != DBNull.Value ? (int?)dataReader["Id"] : 0) : (dataTable.Select("ColumnName = 'OJT_Id'").Count() == 1) ? (dataReader["OJT_Id"] != DBNull.Value ? (int?)dataReader["OJT_Id"] : 0) : null,
RepsRequired = (dataTable.Select("ColumnName = 'OJT_Reps_Required_Count'").Count() == 1) ? (dataReader["OJT_Reps_Required_Count"] != DBNull.Value ? (int?)dataReader["OJT_Reps_Required_Count"] : 0) : null,
RepsCompleted = (dataTable.Select("ColumnName = 'OJT_Reps_Completed_Count'").Count() == 1) ? (dataReader["OJT_Reps_Completed_Count"] != DBNull.Value ? (int?)dataReader["OJT_Reps_Completed_Count"] : 0) : null,
LastSignoffBy = (dataTable.Select("ColumnName = 'LastSignOffBy'").Count() == 1) ? Convert.ToString((dataReader["LastSignOffBy"])) : null,
WorkbookAssignedDate = (dataTable.Select("ColumnName = 'DateAdded'").Count() == 1) ? !string.IsNullOrEmpty(Convert.ToString(dataReader["DateAdded"])) ? Convert.ToDateTime(dataReader["DateAdded"]).ToString("MM/dd/yyyy") : default(DateTime).ToString("MM/dd/yyyy") : null,
Repetitions = (dataTable.Select("ColumnName = 'Repetitions'").Count() == 1) ? Convert.ToString((dataReader["Repetitions"])) : null,
FirstAttemptDate = (dataTable.Select("ColumnName = 'FirstAttemptDate'").Count() == 1) ? !string.IsNullOrEmpty(Convert.ToString(dataReader["FirstAttemptDate"])) ? Convert.ToDateTime(dataReader["FirstAttemptDate"]).ToString("MM/dd/yyyy") : default(DateTime).ToString("MM/dd/yyyy") : null,
LastAttemptDate = (dataTable.Select("ColumnName = 'LastAttemptDate'").Count() == 1) ? !string.IsNullOrEmpty(Convert.ToString(dataReader["LastAttemptDate"])) ? Convert.ToDateTime(dataReader["LastAttemptDate"]).ToString("MM/dd/yyyy") : default(DateTime).ToString("MM/dd/yyyy") : null,
NumberCompleted = (dataTable.Select("ColumnName = 'NumberCompleted'").Count() == 1) ? (int?)(dataReader["NumberCompleted"]) : null,
Role = (dataTable.Select("ColumnName = 'Role'").Count() == 1) ? Convert.ToString(dataReader["Role"]) : (dataTable.Select("ColumnName = 'Employee_Role'").Count() == 1) ? Convert.ToString(dataReader["Employee_Role"]) : null,
CompletedWorkbook = (dataTable.Select("ColumnName = 'OJT_Completed'").Count() == 1) ? (int?)(dataReader["OJT_Completed"]) : null,
TotalTasks = (dataTable.Select("ColumnName = 'OJT_Task_Count'").Count() == 1) ? (int?)(dataReader["OJT_Task_Count"]) : null,
TotalWorkbook = (dataTable.Select("ColumnName = 'TotalWorkbooks'").Count() == 1) ? (int?)(dataReader["TotalWorkbooks"]) : null,
PastDueWorkBook = (dataTable.Select("ColumnName = 'OJT_Past_Due_Count'").Count() == 1) ? (int?)(dataReader["OJT_Past_Due_Count"]) : null,
InDueWorkBook = (dataTable.Select("ColumnName = 'OJT_Due_Count'").Count() == 1) ? (int?)(dataReader["OJT_Due_Count"]) : null,
AssignedWorkBook = (dataTable.Select("ColumnName = 'OJT_Assigned_Count'").Count() == 1) ? (int?)(dataReader["OJT_Assigned_Count"]) : null,
UserCount = (dataTable.Select("ColumnName = 'userCount'").Count() == 1) ? (int?)(dataReader["userCount"]) : null,
EntityCount = (dataTable.Select("ColumnName = 'entityCount'").Count() == 1) ? (int?)(dataReader["entityCount"]) : null,
UserName = (dataTable.Select("ColumnName = 'UserName'").Count() == 1) ? Convert.ToString(dataReader["UserName"]) : (dataTable.Select("ColumnName = 'Employee_User_Name'").Count() == 1) ? Convert.ToString(dataReader["Employee_User_Name"]) : null,
Status = (dataTable.Select("ColumnName = 'status'").Count() == 1) ? Convert.ToString(dataReader["status"]) : null,
DaysToComplete = (dataTable.Select("ColumnName = 'daystocomplete'").Count() == 1) ? Convert.ToString(dataReader["daystocomplete"]) : null,
AlternateName = (dataTable.Select("ColumnName = 'UserName2'").Count() == 1) ? Convert.ToString(dataReader["UserName2"]) : null,
Email = (dataTable.Select("ColumnName = 'email'").Count() == 1) ? Convert.ToString(dataReader["email"]) : null,
CreatedBy = (dataTable.Select("ColumnName = 'CreatedBy'").Count() == 1) ? Convert.ToString(dataReader["CreatedBy"]) : null,
Address = (dataTable.Select("ColumnName = 'Address'").Count() == 1) ? Convert.ToString(dataReader["Address"]) : null,
Phone = (dataTable.Select("ColumnName = 'Phone'").Count() == 1) ? Convert.ToString(dataReader["Phone"]) : null,
TotalEmployees = (dataTable.Select("ColumnName = 'Subordinate_Count'").Count() == 1) ? (int?)(dataReader["Subordinate_Count"]) : null,
InCompleteWorkBook = (dataTable.Select("ColumnName = 'InCompletedWorkbooks'").Count() == 1) ? (int?)(dataReader["InCompletedWorkbooks"]) : null,
UserId = (dataTable.Select("ColumnName = 'UserId'").Count() == 1) ? (dataReader["UserId"] != DBNull.Value ? (int?)dataReader["UserId"] : 0) : (dataTable.Select("ColumnName = 'Employee_Id'").Count() == 1) ? (dataReader["Employee_Id"] != DBNull.Value ? (int?)dataReader["Employee_Id"] : 0) : null,
DueDate = (dataTable.Select("ColumnName = 'OJT_Due_Date'").Count() == 1) ? !string.IsNullOrEmpty(Convert.ToString(dataReader["OJT_Due_Date"])) ? Convert.ToDateTime(dataReader["OJT_Due_Date"]).ToString("MM/dd/yyyy") : null : null,
TaskId = (dataTable.Select("ColumnName = 'taskId'").Count() == 1) ? (dataReader["taskId"] != DBNull.Value ? (int?)dataReader["taskId"] : 0) : (dataTable.Select("ColumnName = 'Task_Id'").Count() == 1) ? (dataReader["Task_Id"] != DBNull.Value ? (int?)dataReader["Task_Id"] : 0) : null,
TaskName = (dataTable.Select("ColumnName = 'taskName'").Count() == 1) ? Convert.ToString(dataReader["taskName"]) : (dataTable.Select("ColumnName = 'Task_Name'").Count() == 1) ? Convert.ToString(dataReader["Task_Name"]) : null,
TaskCode = (dataTable.Select("ColumnName = 'Code'").Count() == 1) ? Convert.ToString(dataReader["Code"]) : (dataTable.Select("ColumnName = 'Task_Code'").Count() == 1) ? Convert.ToString(dataReader["Task_Code"]) : null,
NumberofAttempts = (dataTable.Select("ColumnName = 'Attempt'").Count() == 1) ? Convert.ToString(dataReader["Attempt"]) : null,
LastAttemptDate_tasks = (dataTable.Select("ColumnName = 'Date_Attempted'").Count() == 1) ? !string.IsNullOrEmpty(Convert.ToString(dataReader["Date_Attempted"])) ? Convert.ToDateTime(dataReader["Date_Attempted"]).ToString("MM/dd/yyyy") : default(DateTime).ToString("MM/dd/yyyy") : null,
Location = (dataTable.Select("ColumnName = 'Attempt_Location'").Count() == 1) ? Convert.ToString(dataReader["Attempt_Location"]) : null,
Comments = taskComment?.Comment,
EvaluatorName = (dataTable.Select("ColumnName = 'Submitted_By_User_Id'").Count() == 1) ? Convert.ToString(dataReader["Submitted_By_User_Id"]) : null,
CompletedTasks = (dataTable.Select("ColumnName = 'OJT_Task_Completed_Count'").Count() == 1) ? Convert.ToString((dataReader["OJT_Task_Completed_Count"])) : null
};
// Adding each workbook details in array list
workbookList.Add(workbookResponse);
}
}
else
{
return(null);
}
}
return(workbookList);
}
catch (Exception readWorkbookDetailsException)
{
LambdaLogger.Log(readWorkbookDetailsException.ToString());
return(null);
}
}