public void RequestWithoutColumnsList()
{
List <EmployeeModel> employeeList = new List <EmployeeModel>();
Function function = new Function();
QueryBuilderRequest employeeRequest = new QueryBuilderRequest
{
CompanyId = 6,
UserId = 6,
Payload = new QueryBuilderRequest
{
Fields = employeeList,
EntityName = "Employee",
AppType = Constants.QUERY_BUILDER
}
};
EmployeeModel employeeModel1 = new EmployeeModel
{
Name = Constants.USERID,
Value = "7",
Operator = ">="
};
employeeList.Add(employeeModel1);
EmployeeResponse usersResponse = function.GetEmployeesQueryBuilder(employeeRequest, null);
ErrorResponse userResponse = usersResponse.Error;
Assert.AreEqual(500, userResponse.Status);
Assert.AreEqual(33, userResponse.Code);
StringAssert.Contains(userResponse.Message, TestConstants.SYSTEM_ERROR);
}
public void GetUserDetailsCreatedBeforeToday()
{
List <EmployeeModel> employeeList = new List <EmployeeModel>();
Function function = new Function();
QueryBuilderRequest employeeRequest = new QueryBuilderRequest
{
CompanyId = 6,
UserId = 111,
Payload = new QueryBuilderRequest
{
ColumnList = DefaultColumnsList,
Fields = employeeList,
EntityName = "Employee",
AppType = Constants.QUERY_BUILDER
}
};
EmployeeModel employeeModel1 = new EmployeeModel
{
Name = Constants.USER_CREATED_DATE,
Value = "04/22/2019",
Operator = "<"
};
employeeList.Add(employeeModel1);
EmployeeResponse usersResponse = function.GetEmployeesQueryBuilder(employeeRequest, null);
List <EmployeeQueryModel> userResponse = usersResponse.Employees;
Assert.AreNotEqual(0, userResponse.Count);
}
public void SmartParamCombinationTest()
{
List <EmployeeModel> employeeList = new List <EmployeeModel>();
Function function = new Function();
QueryBuilderRequest employeeRequest = new QueryBuilderRequest
{
CompanyId = 6,
UserId = 111,
Payload = new QueryBuilderRequest
{
ColumnList = DefaultColumnsList,
Fields = employeeList,
EntityName = "Employee",
AppType = Constants.QUERY_BUILDER
}
};
EmployeeModel employeeModel1 = new EmployeeModel
{
Name = Constants.DEPARTMENT,
Value = Constants.YES,
Operator = "="
};
employeeList.Add(employeeModel1);
EmployeeResponse usersResponse = function.GetEmployeesQueryBuilder(employeeRequest, null);
List <EmployeeQueryModel> userResponse = usersResponse.Employees;
Assert.AreNotEqual(0, userResponse.Count);
}
public void RequestWithoutColumnList()
{
List <EmployeeModel> tasksList = new List <EmployeeModel>();
Function function = new Function();
QueryBuilderRequest taskRequest = new QueryBuilderRequest
{
CompanyId = 6,
UserId = 6,
Payload = new QueryBuilderRequest
{
Fields = tasksList,
AppType = Constants.QUERY_BUILDER,
EntityName = "Task"
}
};
EmployeeModel task1 = new EmployeeModel
{
Name = Constants.TASK_ID,
Value = "90",
Operator = "="
};
tasksList.Add(task1);
TaskResponse taskResponse = function.GetTaskQueryBuilder(taskRequest, null);
ErrorResponse tasksResponse = taskResponse.Error;
Assert.AreEqual(500, tasksResponse.Status);
Assert.AreEqual(33, tasksResponse.Code);
StringAssert.Contains(tasksResponse.Message, TestConstants.SYSTEM_ERROR);
}
/// <summary>
/// [QueryBuilder] Function to get list of employee(s) filtered with some condition(s)
/// </summary>
/// <param name="queryBuilderRequest"></param>
/// <param name="context"></param>
/// <returns>EmployeeResponse</returns>
public EmployeeResponse GetEmployeesQueryBuilder(QueryBuilderRequest queryBuilderRequest, ILambdaContext context = null)
{
EmployeeRepository employeeRepository = new EmployeeRepository();
EmployeeResponse employeeResponse = new EmployeeResponse();
Authorizer authorizer = new Authorizer();
try
{
//Validating the student details
string studentDetails = queryBuilderRequest.Payload.Fields.Where(x => x.Name.ToUpper() == Constants.STUDENT_DETAILS).Select(x => x.Value).FirstOrDefault();
if (authorizer.ValidateUser(queryBuilderRequest.UserId, queryBuilderRequest.CompanyId, queryBuilderRequest.Payload.AppType, studentDetails))
{
return(employeeRepository.GetEmployeeDetails(queryBuilderRequest));
}
else
{
employeeResponse.Error = ResponseBuilder.Forbidden();
return(employeeResponse);
}
}
catch (Exception getEmployeesException)
{
LambdaLogger.Log(getEmployeesException.ToString());
employeeResponse.Error = ResponseBuilder.InternalError();
return(employeeResponse);
}
}
/// <summary>
/// Generate the SQL Query based on the entity
/// </summary>
/// <param name="queryBuilderRequest"></param>
/// <returns>string</returns>
private string ProcessSaveQueryRequest(QueryBuilderRequest queryBuilderRequest)
{
string query = string.Empty;
try
{
//Generate the SQL Query based on the entity
switch (queryBuilderRequest.EntityName)
{
case Constants.TASK:
TaskRepository taskRepository = new TaskRepository();
query = taskRepository.CreateTaskQuery(queryBuilderRequest);
break;
case Constants.WORKBOOK:
WorkbookRepository workbookRepository = new WorkbookRepository();
query = workbookRepository.CreateWorkbookQuery(queryBuilderRequest);
break;
case Constants.EMPLOYEE:
EmployeeRepository employeeRepository = new EmployeeRepository();
query = employeeRepository.CreateEmployeeQuery(queryBuilderRequest);
break;
default:
break;
}
return(query);
}
catch (Exception exception)
{
LambdaLogger.Log(exception.ToString());
return(string.Empty);
}
}
public void RequestWithoutColumnList()
{
List <EmployeeModel> wbList = new List <EmployeeModel>();
Function function = new Function();
QueryBuilderRequest wbRequest = new QueryBuilderRequest
{
CompanyId = 6,
UserId = 6,
Payload = new QueryBuilderRequest
{
Fields = wbList,
EntityName = "Workbook",
AppType = Constants.QUERY_BUILDER
}
};
EmployeeModel wbFilter1 = new EmployeeModel
{
Name = Constants.DAYS_TO_COMPLETE,
Value = "20",
Operator = "<="
};
wbList.Add(wbFilter1);
WorkbookResponse wbResponse = function.GetWorkbookQueryBuilder(wbRequest, null);
ErrorResponse workbookResponse = wbResponse.Error;
Assert.AreEqual(500, workbookResponse.Status);
Assert.AreEqual(33, workbookResponse.Code);
StringAssert.Contains(workbookResponse.Message, TestConstants.SYSTEM_ERROR);
}
/// <summary>
/// Get list of user queires
/// </summary>
/// <param name="queryBuilderRequest"></param>
/// <returns>QueryResponse</returns>
public QueryResponse GetUserQuery(QueryBuilderRequest queryBuilderRequest)
{
List <QueryModel> queryList = new List <QueryModel>();
QueryResponse queryResponse = new QueryResponse();
try
{
if (queryBuilderRequest.CompanyId == 0)
{
throw new ArgumentException("CompanyId");
}
if (queryBuilderRequest.UserId == 0)
{
throw new ArgumentException("UserId");
}
if (string.IsNullOrEmpty(queryBuilderRequest.QueryId))
{
throw new ArgumentException("QueryId");
}
if (!string.IsNullOrEmpty(queryBuilderRequest.QueryId))
{
using (IDataReader dataReader = ExecuteReader(Queries.SaveQuery.GetUserQueries(queryBuilderRequest.CompanyId, queryBuilderRequest.QueryId), null))
{
if (dataReader != null)
{
while (dataReader.Read())
{
QueryModel queryModel = new QueryModel
{
QueryJson = Convert.ToString(dataReader["QueryJson"]),
QueryResult = GetResults(queryBuilderRequest.CompanyId, Convert.ToString(dataReader["QuerySQL"]), Convert.ToString(dataReader["QueryJson"]))
};
queryList.Add(queryModel);
}
queryResponse.Queries = queryList;
}
else
{
queryResponse.Error = ResponseBuilder.InternalError();
}
}
}
else
{
queryResponse.Error = ResponseBuilder.BadRequest(DataResource.CHECK_INPUT);
}
return(queryResponse);
}
catch (Exception getUserQueriesException)
{
LambdaLogger.Log(getUserQueriesException.ToString());
queryResponse.Error = new ExceptionHandler(getUserQueriesException).ExceptionResponse();
return(queryResponse);
}
finally
{
CloseConnection();
}
}
internal dynamic ExecuteTests(int companyId, int userId, string[] columns, string appType, List <EmployeeModel> fieldset)
{
Function function = new Function();
QueryBuilderRequest QueryRequest = new QueryBuilderRequest
{
CompanyId = companyId,
UserId = userId,
Payload = new QueryBuilderRequest
{
ColumnList = columns,
Fields = fieldset,
AppType = appType
}
};
dynamic testResponse = null;
switch (appType)
{
case Constants.OQ_DASHBOARD:
case Constants.TRAINING_DASHBOARD:
testResponse = function.GetWorkbookQueryBuilder(QueryRequest, null);
break;
case Constants.WORKBOOK_DASHBOARD:
testResponse = function.GetWorkbookQueryBuilder(QueryRequest, null);
break;
default:
break;
}
return(testResponse);
}
public void GetUsersWithSpecificRole()
{
List <EmployeeModel> employeeList = new List <EmployeeModel>();
Function function = new Function();
QueryBuilderRequest employeeRequest = new QueryBuilderRequest
{
CompanyId = 6,
UserId = 111,
Payload = new QueryBuilderRequest
{
ColumnList = DefaultColumnsList,
Fields = employeeList,
EntityName = "Employee",
AppType = Constants.QUERY_BUILDER
}
};
EmployeeModel employeeModel1 = new EmployeeModel
{
Name = Constants.ROLE,
Value = Constants.SUPERVISOR,
Operator = "="
};
employeeList.Add(employeeModel1);
EmployeeResponse usersResponse = function.GetEmployeesQueryBuilder(employeeRequest, null);
List <EmployeeQueryModel> userResponse = usersResponse.Employees;
Assert.AreNotEqual(0, userResponse.Count);
Assert.IsTrue(userResponse[0].Role.ToUpper() == Constants.SUPERVISOR);
}
/// <summary>
/// Get specific query details and results based on the queryId
/// </summary>
/// <param name="queryBuilderRequest"></param>
/// <param name="context"></param>
/// <returns>QueryResponse</returns>
public QueryResponse GetQuery(QueryBuilderRequest queryBuilderRequest, ILambdaContext context = null)
{
QueryRepository queryRepository = new QueryRepository();
QueryResponse queryResponse = new QueryResponse();
Authorizer authorizer = new Authorizer();
try
{
if (authorizer.ValidateUser(queryBuilderRequest.UserId, queryBuilderRequest.CompanyId))
{
return(queryRepository.GetUserQuery(queryBuilderRequest));
}
else
{
queryResponse.Error = ResponseBuilder.Forbidden();
return(queryResponse);
}
}
catch (Exception getQueryException)
{
LambdaLogger.Log(getQueryException.ToString());
queryResponse.Error = ResponseBuilder.InternalError();
return(queryResponse);
}
}
/// <summary>
/// Get list of user queires
/// </summary>
/// <param name="queryBuilderRequest"></param>
/// <returns>QueryResponse</returns>
public QueryResponse GetUserQueries(QueryBuilderRequest queryBuilderRequest)
{
int userId = 0;
List <QueryModel> queryList = new List <QueryModel>();
QueryResponse queryResponse = new QueryResponse();
try
{
if (queryBuilderRequest.CompanyId == 0)
{
throw new ArgumentException("CompanyId");
}
if (queryBuilderRequest.UserId == 0)
{
throw new ArgumentException("UserId");
}
userId = queryBuilderRequest.UserId;
if (userId > 0)
{
using (IDataReader dataReader = ExecuteReader(Queries.SaveQuery.GetUserQueries(queryBuilderRequest.CompanyId, userId), null))
{
if (dataReader != null)
{
while (dataReader.Read())
{
QueryModel queryModel = new QueryModel
{
QueryId = Convert.ToString(dataReader["QueryId"]),
QueryName = Convert.ToString(dataReader["Name"]),
CreatedDate = dataReader["CreatedDate"] != DBNull.Value ? Convert.ToDateTime(dataReader["CreatedDate"]).ToString("MM/dd/yyyy") : default(DateTime).ToString("MM/dd/yyyy"),
LastModified = dataReader["LastModified"] != DBNull.Value ? Convert.ToDateTime(dataReader["LastModified"]).ToString("MM/dd/yyyy") : default(DateTime).ToString("MM/dd/yyyy"),
CreatedBy = Convert.ToString(dataReader["employeeName"])
};
queryList.Add(queryModel);
}
}
queryResponse.Queries = queryList;
}
return(queryResponse);
}
else
{
queryResponse.Error = ResponseBuilder.BadRequest(DataResource.USER_ID);
return(queryResponse);
}
}
catch (Exception getUserQueriesException)
{
LambdaLogger.Log(getUserQueriesException.ToString());
queryResponse.Error = new ExceptionHandler(getUserQueriesException).ExceptionResponse();
return(queryResponse);
}
finally
{
CloseConnection();
}
}
public void GetEmployeesWithSingleField()
{
List <EmployeeModel> employeeList = new List <EmployeeModel>();
Function function = new Function();
QueryBuilderRequest employeeRequest = new QueryBuilderRequest
{
CompanyId = 6,
UserId = 6,
Payload = new QueryBuilderRequest
{
ColumnList = new string[] { Constants.USERID, Constants.EMPLOYEE_NAME, Constants.ROLE, Constants.USERNAME, Constants.ALTERNATE_USERNAME, Constants.TOTAL_EMPLOYEES, Constants.EMAIL },
Fields = employeeList,
EntityName = "Employee",
AppType = Constants.QUERY_BUILDER
}
};
EmployeeModel employeeModel1 = new EmployeeModel
{
Name = Constants.USERID,
Value = "27",
Operator = "="
};
EmployeeModel employeeModel2 = new EmployeeModel
{
Name = Constants.ROLE,
Value = "contractor",
Operator = "="
};
EmployeeModel employeeModel5 = new EmployeeModel
{
Name = Constants.ROLE,
Value = "Admin",
Operator = "=",
Bitwise = "AND"
};
EmployeeModel employeeModel3 = new EmployeeModel
{
Name = Constants.SUPERVISOR_ID,
Value = "6",
Operator = "="
};
EmployeeModel employeeModel4 = new EmployeeModel
{
Name = Constants.QR_CODE,
Value = "no",
Operator = "="
};
employeeList.Add(employeeModel1);
EmployeeResponse usersResponse = function.GetEmployeesQueryBuilder(employeeRequest, null);
List <EmployeeQueryModel> userResponse = usersResponse.Employees;
Assert.AreEqual(1, userResponse.Count);
Assert.IsTrue(userResponse[0].UserId == 27);
StringAssert.Contains(userResponse[0].Role, "Manager");
}
public void GetCompletedUsersProgress()
{
List <EmployeeModel> tasksList = new List <EmployeeModel>();
Function function = new Function();
QueryBuilderRequest taskRequest = new QueryBuilderRequest
{
CompanyId = 2288,
UserId = 331535,
Payload = new QueryBuilderRequest
{
ColumnList = new string[] { "USER_ID", Constants.COMPANY_ID, Constants.EMPLOYEE_NAME, Constants.ASSIGNED_COMPANY_QUALIFICATION, Constants.COMPLETED_COMPANY_QUALIFICATION, Constants.IN_COMPLETE_COMPANY_QUALIFICATION },
Fields = tasksList,
AppType = Constants.TRAINING_DASHBOARD
}
};
EmployeeModel task4 = new EmployeeModel
{
Name = Constants.CONTRACTOR_COMPANY,
Value = "2128",
Operator = "=",
Bitwise = ""
};
EmployeeModel task1 = new EmployeeModel
{
Name = Constants.ROLE_ID,
Value = "28649",
Operator = "=",
Bitwise = ""
};
EmployeeModel task2 = new EmployeeModel
{
Name = Constants.ADMIN_ID,
Value = "331535",
Operator = "=",
Bitwise = "AND"
};
EmployeeModel task3 = new EmployeeModel
{
Name = Constants.STATUS,
Value = Constants.COMPLETED,
Operator = "=",
Bitwise = "AND"
};
tasksList.Add(task4);
tasksList.Add(task1);
tasksList.Add(task2);
tasksList.Add(task3);
TaskResponse taskResponse = function.GetTaskQueryBuilder(taskRequest, null);
List <TaskModel> tasksResponse = taskResponse.Tasks;
Assert.IsTrue(tasksResponse.Count > 0);
Assert.AreEqual(28649, tasksResponse[0].RoleId);
Assert.AreEqual(2128, tasksResponse[0].CompanyId);
}
public void GetTasksWithAdditionalColumns()
{
List <EmployeeModel> tasksList = new List <EmployeeModel>();
Function function = new Function();
QueryBuilderRequest taskRequest = new QueryBuilderRequest
{
CompanyId = 6,
UserId = 6,
Payload = new QueryBuilderRequest
{
ColumnList = new string[] { Constants.TASK_ID, Constants.TASK_NAME, Constants.STATUS, Constants.EVALUATOR_NAME, Constants.ASSIGNED_TO, Constants.DATE_TAKEN, Constants.NUMBER_OF_ATTEMPTS, Constants.LOCATION },
Fields = tasksList,
AppType = Constants.QUERY_BUILDER,
EntityName = "Task"
}
};
EmployeeModel task1 = new EmployeeModel
{
Name = Constants.STATUS,
Value = "completed",
Operator = "="
};
EmployeeModel task2 = new EmployeeModel
{
Name = Constants.TASK_NAME,
Value = "Mechanical",
Operator = "does not contain",
Bitwise = "AND"
};
EmployeeModel task3 = new EmployeeModel
{
Name = Constants.EVALUATOR_NAME,
Value = "en",
Operator = "contains",
Bitwise = "AND"
};
tasksList.Add(task1);
tasksList.Add(task2);
tasksList.Add(task3);
TaskResponse taskResponse = function.GetTaskQueryBuilder(taskRequest, null);
List <TaskModel> tasksResponse = taskResponse.Tasks;
Assert.AreNotEqual(0, tasksResponse.Count);
Assert.AreNotEqual(null, tasksResponse[0].TaskId);
Assert.AreEqual("Completed", tasksResponse[0].Status);
Assert.AreNotEqual(null, tasksResponse[0].NumberofAttempts);
Assert.AreNotEqual(null, tasksResponse[0].Location);
StringAssert.Matches(tasksResponse[0].EvaluatorName, new Regex(@"(?i)\b(.*?)en(.*?)\b"));
}
public void GetEmployeesWithAdditionalColumns()
{
List <EmployeeModel> employeeList = new List <EmployeeModel>();
Function function = new Function();
QueryBuilderRequest employeeRequest = new QueryBuilderRequest
{
CompanyId = 6,
UserId = 6,
Payload = new QueryBuilderRequest
{
ColumnList = new string[] { Constants.EMPLOYEE_NAME, Constants.ROLE, Constants.USERNAME, Constants.ALTERNATE_USERNAME, Constants.TOTAL_EMPLOYEES, Constants.EMAIL, Constants.USERID, Constants.USER_CREATED_DATE },
Fields = employeeList,
EntityName = "Employee",
AppType = Constants.QUERY_BUILDER
}
};
EmployeeModel employeeModel1 = new EmployeeModel
{
Name = Constants.USERID,
Value = "7",
Operator = ">="
};
EmployeeModel employeeModel2 = new EmployeeModel
{
Name = Constants.USER_CREATED_DATE,
Value = "01/21/1995",
Operator = ">",
Bitwise = "and"
};
EmployeeModel employeeModel3 = new EmployeeModel
{
Name = Constants.ROLE,
Value = "manager",
Operator = "contains",
Bitwise = "and"
};
employeeList.Add(employeeModel1);
employeeList.Add(employeeModel2);
employeeList.Add(employeeModel3);
EmployeeResponse usersResponse = function.GetEmployeesQueryBuilder(employeeRequest, null);
List <EmployeeQueryModel> userResponse = usersResponse.Employees;
Assert.AreNotEqual(0, userResponse.Count);
Assert.AreNotEqual("", userResponse[0].UserName);
Assert.AreNotSame("Completed", userResponse[0].UserId);
//Assert.AreNotEqual(null, userResponse[0].UserCreatedDate);
Assert.AreNotEqual(null, userResponse[0].TotalEmployees);
StringAssert.Matches(userResponse[0].Role, new Regex(@"(?i)\b(.*?)manager(.*?)\b"));
}
public void GetTasksWithMultipleFields()
{
List <EmployeeModel> tasksList = new List <EmployeeModel>();
Function function = new Function();
QueryBuilderRequest taskRequest = new QueryBuilderRequest
{
CompanyId = 6,
UserId = 6,
Payload = new QueryBuilderRequest
{
ColumnList = new string[] { Constants.TASK_ID, Constants.TASK_NAME, Constants.ASSIGNED_TO, Constants.EVALUATOR_NAME, Constants.EXPIRATION_DATE },
Fields = tasksList,
AppType = Constants.QUERY_BUILDER,
EntityName = "Task"
}
};
EmployeeModel task1 = new EmployeeModel
{
Name = Constants.TASK_ID,
Value = "19",
Operator = ">="
};
EmployeeModel task2 = new EmployeeModel
{
Name = Constants.EVALUATOR_NAME,
Value = "nn",
Operator = "contains",
Bitwise = "AND"
};
EmployeeModel task3 = new EmployeeModel
{
Name = Constants.DATE_TAKEN,
Value = "11/21/1991",
Operator = ">=",
Bitwise = "AND"
};
tasksList.Add(task1);
tasksList.Add(task2);
tasksList.Add(task3);
TaskResponse taskResponse = function.GetTaskQueryBuilder(taskRequest, null);
List <TaskModel> tasksResponse = taskResponse.Tasks;
Assert.AreNotEqual(0, tasksResponse.Count);
Assert.AreNotEqual("", tasksResponse[0].TaskId);
Assert.IsTrue(tasksResponse[0].TaskId >= 19);
StringAssert.Matches(tasksResponse[0].EvaluatorName, new Regex(@"(?i)\b(.*?)nn(.*?)\b"));
}
public void GetWorkbooksWithSingleField()
{
List <EmployeeModel> wbList = new List <EmployeeModel>();
Function function = new Function();
QueryBuilderRequest wbRequest = new QueryBuilderRequest
{
CompanyId = 6,
UserId = 6,
Payload = new QueryBuilderRequest
{
ColumnList = new string[] { Constants.WORKBOOK_ID, Constants.WORKBOOK_NAME, Constants.DESCRIPTION, Constants.WORKBOOK_CREATED_BY, Constants.DAYS_TO_COMPLETE },
Fields = wbList,
EntityName = "Workbook",
AppType = Constants.QUERY_BUILDER
}
};
EmployeeModel wbFilter1 = new EmployeeModel
{
Name = Constants.DAYS_TO_COMPLETE,
Value = "20",
Operator = "<="
};
EmployeeModel wbFilter2 = new EmployeeModel
{
Name = Constants.WORKBOOK_CREATED,
Value = "01/01/1787 and 04/03/2019", //"08 /06/1977, 08/08/1975",
Operator = "BETWEEN"
};
EmployeeModel wbFilter3 = new EmployeeModel
{
Name = Constants.WORKBOOK_ID,
Value = "14",
Operator = "<"
};
EmployeeModel wbFilter4 = new EmployeeModel
{
Name = Constants.SUPERVISOR_ID,
Value = "6",
Operator = "!="
};
wbList.Add(wbFilter3);
//wbList.Add(wbFilter4);
WorkbookResponse wbResponse = function.GetWorkbookQueryBuilder(wbRequest, null);
List <WorkbookModel> workbookList = wbResponse.Workbooks;
Assert.AreNotEqual(0, workbookList.Count);
Assert.IsTrue(workbookList[workbookList.Count - 1].WorkBookId < 14);
}
/// <summary>
/// Delete the existing query based on the queryId
/// </summary>
/// <param name="queryBuilderRequest"></param>
/// <returns>QueryResponse</returns>
public QueryResponse DeleteQuery(QueryBuilderRequest queryBuilderRequest)
{
int rowAffected = 0, Id = 0;
QueryResponse queryResponse = new QueryResponse();
try
{
if (string.IsNullOrEmpty(queryBuilderRequest.QueryId))
{
throw new ArgumentException("QueryId");
}
Id = ExecuteScalar(Queries.SaveQuery.ReadQuery(queryBuilderRequest.QueryId));
if (Id != 0)
{
rowAffected = ExecuteQuery(Queries.SaveQuery.DeleteQuery(queryBuilderRequest.QueryId));
if (rowAffected > 0)
{
rowAffected = ExecuteQuery(Queries.SaveQuery.DeleteUserQuery(Id));
if (rowAffected > 0)
{
queryResponse.Message = DataResource.DELETE_SUCCESS_MESSAGE;
}
else
{
queryResponse.Error = ResponseBuilder.InternalError();
}
}
else
{
queryResponse.Error = ResponseBuilder.InternalError();
}
}
else
{
queryResponse.Error = ResponseBuilder.BadRequest(DataResource.CHECK_INPUT);
}
return(queryResponse);
}
catch (Exception deleteQueryException)
{
LambdaLogger.Log(deleteQueryException.ToString());
queryResponse.Error = new ExceptionHandler(deleteQueryException).ExceptionResponse();
return(queryResponse);
}
finally
{
CloseConnection();
}
}
public void GetWorkbooksWithMultipleField()
{
List <EmployeeModel> wbList = new List <EmployeeModel>();
Function function = new Function();
QueryBuilderRequest wbRequest = new QueryBuilderRequest
{
CompanyId = 6,
UserId = 6,
Payload = new QueryBuilderRequest
{
ColumnList = new string[] { Constants.WORKBOOK_ID, Constants.WORKBOOK_NAME, Constants.DESCRIPTION, Constants.WORKBOOK_CREATED_BY, Constants.DAYS_TO_COMPLETE },
Fields = wbList,
EntityName = "Workbook",
AppType = Constants.QUERY_BUILDER
}
};
EmployeeModel wbFilter1 = new EmployeeModel
{
Name = Constants.WORKBOOK_ID,
Value = "40",
Operator = ">="
};
EmployeeModel wbFilter2 = new EmployeeModel
{
Name = Constants.WORKBOOK_CREATED,
Value = "01/21/2001",
Operator = "<=",
Bitwise = "and"
};
EmployeeModel wbFilter3 = new EmployeeModel
{
Name = Constants.WORKBOOK_NAME,
Value = "e",
Operator = "contains",
Bitwise = "and"
};
wbList.Add(wbFilter1);
wbList.Add(wbFilter2);
wbList.Add(wbFilter3);
WorkbookResponse wbResponse = function.GetWorkbookQueryBuilder(wbRequest, null);
List <WorkbookModel> workbookList = wbResponse.Workbooks;
Assert.AreNotEqual(0, workbookList.Count);
Assert.AreNotEqual("", workbookList[0].WorkBookId);
Assert.IsTrue(workbookList[0].WorkBookId >= 40);
StringAssert.Matches(workbookList[0].WorkBookName, new Regex(@"(?i)\b(.*?)e(.*?)\b"));
}
public void GetWorkbooksForInvalidCompany()
{
List <EmployeeModel> wbList = new List <EmployeeModel>();
Function function = new Function();
QueryBuilderRequest wbRequest = new QueryBuilderRequest
{
CompanyId = 8, // Valid one is 6
UserId = 6,
Payload = new QueryBuilderRequest
{
ColumnList = new string[] { Constants.WORKBOOK_ID, Constants.WORKBOOK_NAME, Constants.DESCRIPTION, Constants.WORKBOOK_CREATED_BY, Constants.DAYS_TO_COMPLETE },
Fields = wbList,
EntityName = "Workbook",
AppType = Constants.QUERY_BUILDER
}
};
EmployeeModel wbFilter1 = new EmployeeModel
{
Name = Constants.WORKBOOK_ID,
Value = "40",
Operator = ">="
};
EmployeeModel wbFilter2 = new EmployeeModel
{
Name = Constants.WORKBOOK_CREATED,
Value = "01/21/2001",
Operator = "<=",
Bitwise = "and"
};
EmployeeModel wbFilter3 = new EmployeeModel
{
Name = Constants.WORKBOOK_NAME,
Value = "e",
Operator = "contains",
Bitwise = "and"
};
wbList.Add(wbFilter1);
wbList.Add(wbFilter2);
wbList.Add(wbFilter3);
WorkbookResponse wbResponse = function.GetWorkbookQueryBuilder(wbRequest, null);
ErrorResponse workbookList = wbResponse.Error;
Assert.AreEqual(403, workbookList.Status);
Assert.AreEqual(14, workbookList.Code);
StringAssert.Contains(workbookList.Message, TestConstants.PERMISSION_DENIED);
}
public void GetTasksForUnsubscribedCompany()
{
List <EmployeeModel> tasksList = new List <EmployeeModel>();
Function function = new Function();
QueryBuilderRequest taskRequest = new QueryBuilderRequest
{
CompanyId = 8, // Valid one is 6
UserId = 6,
Payload = new QueryBuilderRequest
{
ColumnList = new string[] { Constants.TASK_ID, Constants.TASK_NAME, "ITSLMS", Constants.EVALUATOR_NAME, Constants.ASSIGNED_TO },
Fields = tasksList,
AppType = Constants.QUERY_BUILDER,
EntityName = "Task"
}
};
EmployeeModel task1 = new EmployeeModel
{
Name = Constants.STATUS,
Value = "failed",
Operator = "="
};
EmployeeModel task2 = new EmployeeModel
{
Name = Constants.TASK_NAME,
Value = "e",
Operator = "does not contain",
Bitwise = "AND"
};
EmployeeModel task3 = new EmployeeModel
{
Name = Constants.EVALUATOR_NAME,
Value = "an",
Operator = "contains",
Bitwise = "AND"
};
tasksList.Add(task1);
tasksList.Add(task2);
tasksList.Add(task3);
TaskResponse taskResponse = function.GetTaskQueryBuilder(taskRequest, null);
ErrorResponse taskList = taskResponse.Error;
Assert.AreEqual(403, taskList.Status);
Assert.AreEqual(14, taskList.Code);
StringAssert.Contains(taskList.Message, TestConstants.PERMISSION_DENIED);
}
public void GetEmployeesWithInvalidCompany()
{
List <EmployeeModel> employeeList = new List <EmployeeModel>();
Function function = new Function();
QueryBuilderRequest employeeRequest = new QueryBuilderRequest
{
CompanyId = 8, // Valid one is 6
UserId = 6,
Payload = new QueryBuilderRequest
{
ColumnList = new string[] { Constants.EMPLOYEE_NAME, Constants.ROLE, Constants.USERNAME, Constants.USERID, Constants.TOTAL_EMPLOYEES, Constants.EMAIL },
Fields = employeeList,
EntityName = "Employee",
AppType = Constants.QUERY_BUILDER
}
};
EmployeeModel employeeModel1 = new EmployeeModel
{
Name = Constants.USERID,
Value = "7",
Operator = ">="
};
EmployeeModel employeeModel2 = new EmployeeModel
{
Name = Constants.USER_CREATED_DATE,
Value = "01/21/1995",
Operator = ">",
Bitwise = "and"
};
EmployeeModel employeeModel3 = new EmployeeModel
{
Name = Constants.ROLE,
Value = "manager",
Operator = "contains",
Bitwise = "and"
};
employeeList.Add(employeeModel1);
employeeList.Add(employeeModel2);
employeeList.Add(employeeModel3);
EmployeeResponse usersResponse = function.GetEmployeesQueryBuilder(employeeRequest, null);
ErrorResponse userResponse = usersResponse.Error;
Assert.AreEqual(403, userResponse.Status);
Assert.AreEqual(14, userResponse.Code);
StringAssert.Contains(userResponse.Message, TestConstants.PERMISSION_DENIED);
}
public void GetIncompleteCompaniesForARole()
{
List <EmployeeModel> tasksList = new List <EmployeeModel>();
Function function = new Function();
QueryBuilderRequest taskRequest = new QueryBuilderRequest
{
CompanyId = 2288,
UserId = 331535,
Payload = new QueryBuilderRequest
{
ColumnList = new string[] { Constants.COMPLETED_COMPANY_USERS, Constants.NOT_COMPLETED_COMPANY_USERS, Constants.TOTAL_COMPLETED_COMPANY_USERS, Constants.COMPANY_NAME, Constants.ROLE_ID, Constants.COMPANY_ID },
Fields = tasksList,
AppType = Constants.TRAINING_DASHBOARD
}
};
EmployeeModel task1 = new EmployeeModel
{
Name = Constants.ROLE_ID,
Value = "28649",
Operator = "=",
Bitwise = ""
};
EmployeeModel task2 = new EmployeeModel
{
Name = Constants.ADMIN_ID,
Value = "331535",
Operator = "=",
Bitwise = "AND"
};
EmployeeModel task3 = new EmployeeModel
{
Name = Constants.STATUS,
Value = Constants.NOT_COMPLETED_COMPANY_USERS,
Operator = "=",
Bitwise = "AND"
};
tasksList.Add(task1);
tasksList.Add(task2);
tasksList.Add(task3);
TaskResponse taskResponse = function.GetTaskQueryBuilder(taskRequest, null);
List <TaskModel> tasksResponse = taskResponse.Tasks;
Assert.IsTrue(tasksResponse.Count > 0);
Assert.AreEqual(28649, tasksResponse[0].RoleId);
}
public void GetTasksWithSingleField()
{
List <EmployeeModel> tasksList = new List <EmployeeModel>();
Function function = new Function();
QueryBuilderRequest taskRequest = new QueryBuilderRequest
{
CompanyId = 6,
UserId = 6,
Payload = new QueryBuilderRequest {
ColumnList = new string[] { Constants.TASK_ID, Constants.TASK_NAME, Constants.ASSIGNED_TO, Constants.EVALUATOR_NAME, Constants.EXPIRATION_DATE },
Fields = tasksList,
AppType = Constants.QUERY_BUILDER,
EntityName = "Task"
}
};
EmployeeModel task1 = new EmployeeModel
{
Name = Constants.TASK_ID,
Value = "90",
Operator = "="
};
tasksList.Add(task1);
//Dictionary<string, string> pathValues = new Dictionary<string, string>
//{
// { "companyId", "6" }
//};
//APIGatewayProxyRequest aPIGatewayProxyRequest = new APIGatewayProxyRequest
//{
// Body = JsonConvert.SerializeObject(taskRequest),
// PathParameters = pathValues
//};
TaskResponse taskResponse = function.GetTaskQueryBuilder(taskRequest, null);
List <TaskModel> tasksResponse = taskResponse.Tasks;
Assert.AreNotEqual(0, tasksResponse.Count);
Assert.AreEqual(90, tasksResponse[0].TaskId);
Assert.AreNotEqual("", tasksResponse[0].TaskName);
Assert.IsTrue(tasksResponse[0].EvaluatorName != "");
// Assert.AreEqual(200, taskResponse.StatusCode);
}
public void GetOnlyLoggedInUserDetails()
{
List <EmployeeModel> employeeList = new List <EmployeeModel>();
Function function = new Function();
QueryBuilderRequest employeeRequest = new QueryBuilderRequest
{
CompanyId = 6,
UserId = 111,
Payload = new QueryBuilderRequest
{
ColumnList = DefaultColumnsList,
Fields = employeeList,
EntityName = "Employee",
AppType = Constants.QUERY_BUILDER
}
};
EmployeeModel employeeModel1 = new EmployeeModel
{
Name = Constants.USERNAME,
Value = Constants.ME,
Operator = "="
};
EmployeeModel employeeModel2 = new EmployeeModel
{
Name = Constants.CURRENT_USER,
Value = "111",
Operator = "=",
Bitwise = "AND"
};
employeeList.Add(employeeModel1);
employeeList.Add(employeeModel2);
EmployeeResponse usersResponse = function.GetEmployeesQueryBuilder(employeeRequest, null);
List <EmployeeQueryModel> userResponse = usersResponse.Employees;
Assert.AreEqual(1, userResponse.Count);
Assert.IsTrue(userResponse[0].UserId == 111);
Assert.AreEqual("supervisor", userResponse[0].Role);
Assert.AreNotEqual("", userResponse[0].AlternateName);
}
public void GetLoggedInUserAndSubordinateDetails()
{
List <EmployeeModel> employeeList = new List <EmployeeModel>();
Function function = new Function();
QueryBuilderRequest employeeRequest = new QueryBuilderRequest
{
CompanyId = 6,
UserId = 111,
Payload = new QueryBuilderRequest
{
ColumnList = DefaultColumnsList,
Fields = employeeList,
EntityName = "Employee",
AppType = Constants.QUERY_BUILDER
}
};
EmployeeModel employeeModel1 = new EmployeeModel
{
Name = Constants.USERNAME,
Value = Constants.ME_AND_DIRECT_SUBORDINATES,
Operator = "="
};
EmployeeModel employeeModel2 = new EmployeeModel
{
Name = Constants.CURRENT_USER,
Value = "111",
Operator = "=",
Bitwise = "AND"
};
employeeList.Add(employeeModel1);
employeeList.Add(employeeModel2);
EmployeeResponse usersResponse = function.GetEmployeesQueryBuilder(employeeRequest, null);
List <EmployeeQueryModel> userResponse = usersResponse.Employees;
int userIndex = userResponse.FindIndex(x => x.UserId == 111);
Assert.IsTrue(userIndex >= 0);
Assert.AreNotEqual(0, userResponse.Count);
}
/// <summary>
/// Get query results based on the entity
/// </summary>
/// <param name="companyId"></param>
/// <param name="query"></param>
/// <param name="queryJson"></param>
/// <returns>QueryModel</returns>
public QueryModel GetResults(int companyId, string query, string queryJson)
{
QueryModel queryModel = new QueryModel();
try
{
//Deserialize the query Json to get the results
QueryBuilderRequest queryBuilderRequest = JsonConvert.DeserializeObject <QueryBuilderRequest>(queryJson);
Dictionary <string, string> parameterList = ParameterHelper.Getparameters(queryBuilderRequest);
switch (queryBuilderRequest.EntityName.ToUpper())
{
case Constants.TASK:
TaskRepository taskRepository = new TaskRepository();
List <TaskModel> taskList = taskRepository.ReadTaskDetails(query, parameterList, queryBuilderRequest);
queryModel.Tasks = taskList;
break;
case Constants.WORKBOOK:
WorkbookRepository workbookRepository = new WorkbookRepository();
List <WorkbookModel> workbookList = workbookRepository.ReadWorkBookDetails(query, parameterList);
queryModel.Workbooks = workbookList;
break;
case Constants.EMPLOYEE:
EmployeeRepository employeeRepository = new EmployeeRepository();
List <EmployeeQueryModel> employeeList = employeeRepository.ReadEmployeeDetails(query, parameterList);
queryModel.Employee = employeeList;
break;
default:
break;
}
return(queryModel);
}
catch (Exception getResultsexception)
{
LambdaLogger.Log(getResultsexception.ToString());
return(queryModel);
}
}
public void GetTasksWithInvalidInputs()
{
List <EmployeeModel> tasksList = new List <EmployeeModel>();
Function function = new Function();
QueryBuilderRequest taskRequest = new QueryBuilderRequest
{
CompanyId = 6,
UserId = 6,
Payload = new QueryBuilderRequest
{
ColumnList = new string[] { Constants.TASK_ID, Constants.TASK_NAME, Constants.ASSIGNED_TO, Constants.EVALUATOR_NAME, Constants.EXPIRATION_DATE },
Fields = tasksList,
AppType = Constants.QUERY_BUILDER,
EntityName = "Task"
}
};
EmployeeModel task1 = new EmployeeModel
{
Name = Constants.TASK_ID,
Value = "LMS",
Operator = ">="
};
EmployeeModel task2 = new EmployeeModel
{
Name = Constants.TASK_NAME,
Value = "1234",
Operator = "=",
Bitwise = "AND"
};
tasksList.Add(task1);
tasksList.Add(task2);
TaskResponse taskResponse = function.GetTaskQueryBuilder(taskRequest, null);
List <TaskModel> tasksResponse = taskResponse.Tasks;
Assert.AreEqual(0, tasksResponse.Count);
Assert.ThrowsException <ArgumentOutOfRangeException>(() => tasksResponse[0]);
}
public void GetWorkbooksForSpecificUserId()
{
List <EmployeeModel> wbList = new List <EmployeeModel>();
Function function = new Function();
QueryBuilderRequest wbRequest = new QueryBuilderRequest
{
CompanyId = 6, //1, //6,
UserId = 6, //335216, //6,
Payload = new QueryBuilderRequest
{
ColumnList = new string[] { Constants.USERID, Constants.WORKBOOK_ID, Constants.WORKBOOK_NAME, Constants.DESCRIPTION, Constants.WORKBOOK_CREATED_BY, Constants.DAYS_TO_COMPLETE },
Fields = wbList,
EntityName = "Workbook",
AppType = Constants.QUERY_BUILDER
}
};
EmployeeModel wbFilter1 = new EmployeeModel
{
Name = Constants.USERID,
Value = "10",
Operator = ">="
};
EmployeeModel wbFilter2 = new EmployeeModel
{
Name = Constants.STUDENT_DETAILS,
Value = "10",
Operator = "=",
Bitwise = "AND"
};
wbList.Add(wbFilter1);
//wbList.Add(wbFilter2);
WorkbookResponse wbResponse = function.GetWorkbookQueryBuilder(wbRequest, null);
List <WorkbookModel> workbookList = wbResponse.Workbooks;
Assert.AreNotEqual(0, workbookList.Count);
Assert.IsTrue(workbookList[workbookList.Count - 1].UserId >= 10);
}
