public async Task <List <Employee> > GetEmployeesAsync(EmployeeQueryModel employeeQueryModel = null)
{
IQueryable <Employee> employees = _dataContext.Employees
.Include(x => x.Boss);
if (!(employeeQueryModel is null))
{
if (employeeQueryModel.BossId.HasValue)
{
employees = employees.Where(x => x.Boss.Id == employeeQueryModel.BossId.Value);
}
if (!string.IsNullOrEmpty(employeeQueryModel.FirstName))
{
employees = employees.Where(x => x.FirstName.ToLower() == employeeQueryModel.FirstName.ToLower());
}
if (employeeQueryModel.DateFrom.HasValue)
{
employees = employees.Where(x => x.BirthDate >= employeeQueryModel.DateFrom.Value);
}
if (employeeQueryModel.DateTo.HasValue)
{
employees = employees.Where(x => x.BirthDate <= employeeQueryModel.DateTo.Value);
}
}
return(await employees.ToListAsync());
}
public async Task <IList <EmployeeEntity> > Query(EmployeeQueryModel model)
{
var paging = model.Paging();
var query = Db.Find();
query.WhereIf(model.DepartmentId != null && model.DepartmentId.Value.NotEmpty(), m => m.DepartmentId == model.DepartmentId);
query.WhereNotNull(model.Name, m => m.Name.Contains(model.Name));
query.WhereNotNull(model.JobNo, m => m.Id == model.JobNo.Value);
var joinQuery = query.LeftJoin <AccountEntity>((x, y) => x.CreatedBy == y.Id)
.LeftJoin <DepartmentEntity>((t1, t2, t3) => t1.DepartmentId == t3.Id)
.LeftJoin <PostEntity>((t1, t2, t3, t4) => t1.PostId == t4.Id);
if (!paging.OrderBy.Any())
{
joinQuery.OrderByDescending((t1, t2, t3, t4) => t1.Id);
}
joinQuery.Select((t1, t2, t3, t4) => new { t1, Creator = t2.Name, DepartmentPath = t3.FullPath, PostName = t4.Name });
var result = await joinQuery.PaginationAsync(paging);
model.TotalCount = paging.TotalCount;
return(result);
}
public void LoadEmployeeCost(EmployeeModel employee)
{
EmployeeQueryModel queryModel = employee as EmployeeQueryModel;
if (queryModel == null)
{
return;
}
using (TeamDBContext context = new TeamDBContext())
{
DatabaseAccess dba = context.DatabaseAccess();
DbCommand cmd = dba.CreateCommand("SELECT lc.UnitPrice AS PositionUnitPrice, lc.UnitValue AS PositionUnitValue, lc2.UnitPrice AS UserUnitPrice, lc2.UnitValue AS UserUnitValue FROM auth_User u INNER JOIN auth_UserExtend ue ON u.UserId = ue.UserId LEFT JOIN LaborCost lc ON ue.Position = lc.TargetId AND lc.TargetType = 1 LEFT JOIN LaborCost lc2 ON u.UserId = lc2.TargetId AND lc2.TargetType = 2 WHERE ue.UserId = @UserId");
dba.AddInParameter(cmd, "UserId", DbType.Guid, queryModel.UserId);
using (DbDataReader reader = dba.ExecuteReader(cmd))
{
if (reader.Read())
{
queryModel.PositionUnitPrice = reader.GetDecimalOrDefault("PositionUnitPrice");
queryModel.PositionUnitValue = reader.GetDecimalOrDefault("PositionUnitValue");
queryModel.UserUnitPrice = reader.GetDecimalOrDefault("UserUnitPrice");
queryModel.UserUnitValue = reader.GetDecimalOrDefault("UserUnitValue");
}
}
}
}
/// <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);
}
}
public EmployeeModel CreateEmployeeModel(Guid employeeId, string userName)
{
Guard.ArgumentNotNullOrEmpty(userName, "userName");
EmployeeModel employeeInfo = new EmployeeQueryModel
{
UserId = employeeId,
EmployeeId = employeeId,
UserName = userName
};
return(employeeInfo);
}
public PagingResultModel <EmployeeResultModel> GetEmployees(EmployeeQueryModel query)
{
var where = ExpressionExtension.TrueExpression <SystemUser>()
.AndIfHaveValue(query.Name, u => u.Name.Contains(query.Name))
.AndIfHaveValue(query.PhoneNumber, u => u.PhoneNumber.Contains(query.PhoneNumber));
var userQuery = _userRepository.GetUsers().Where(@where);
if (query.DepartmentId != null)
{
userQuery = from u in userQuery
join uc in _userRepository.GetUserClaims() on u.Id equals uc.UserId
where uc.ClaimType == CustomClaimTypes.UserDepartment &&
uc.ClaimValue == query.DepartmentId.ToString()
select u;
}
if (query.PositionId != null)
{
userQuery = from u in userQuery
join uc in _userRepository.GetUserClaims() on u.Id equals uc.UserId
where uc.ClaimType == CustomClaimTypes.UserPosition &&
uc.ClaimValue == query.PositionId.ToString()
select u;
}
var users = from u in userQuery
let pquery = from uc in _userRepository.GetUserClaims()
where u.Id == uc.UserId && uc.ClaimType == CustomClaimTypes.UserPosition
select uc.ClaimValue
let dquery = from uc in _userRepository.GetUserClaims()
where u.Id == uc.UserId && uc.ClaimType == CustomClaimTypes.UserDepartment
select uc.ClaimValue
orderby u.Id descending
select new EmployeeResultModel
{
Id = u.Id,
PhoneNumber = u.PhoneNumber,
Avatar = u.Avatar,
Name = u.Name,
Sex = (int)u.Sex,
DepartmentId = dquery.FirstOrDefault(),
PositionIds = string.Join(',', pquery)
};
var skip = query.Size * (query.Page - 1);
return(new PagingResultModel <EmployeeResultModel>
{
Data = users.Skip(skip).Take(query.Size).ToList(),
Count = users.Count()
});
}
public async Task <IResultModel> Query(EmployeeQueryModel model)
{
if (model.JobNo != null)
{
model.JobNo = model.JobNo - 1000000;
}
var result = new QueryResultModel <EmployeeEntity>
{
Rows = await _repository.Query(model),
Total = model.TotalCount
};
return(ResultModel.Success(result));
}
public async Task <IResultModel> QueryWithSameDepartment(Guid accountId, EmployeeQueryModel model)
{
var result = new QueryResultModel <EmployeeEntity>();
var employee = await _repository.GetByAccountId(accountId);
if (employee == null || employee.DepartmentId.IsEmpty())
{
return(ResultModel.Success(result));
}
model.DepartmentId = employee.DepartmentId;
result.Rows = await _repository.Query(model);
result.Rows.Remove(result.Rows.FirstOrDefault(m => m.Id == employee.Id));
result.Total = model.TotalCount;
return(ResultModel.Success(result));
}
T ReadEmployeeInfo <T>(DbDataReader reader, bool enableReadCost = true)
where T : EmployeeModel, new()
{
EmployeeModel employeeInfo = new T()
{
UserId = Guid.Parse(reader["UserId"].ToString()),
UserName = reader["UserName"].ToString(),
Enabled = Convert.ToBoolean(reader["Enabled"]),
UserCode = reader.GetStringOrNull("UserCode"),
RealName = reader.GetStringOrNull("RealName"),
Organization = reader.GetStringOrNull("Organization"),
OrganizationName = reader.GetStringOrNull("OrganizationName"),
Department = reader.GetStringOrNull("Department"),
DepartmentName = reader.GetStringOrNull("DepartmentName"),
Position = reader.GetStringOrNull("Position"),
PositionName = reader.GetStringOrNull("PositionName"),
Status = reader.GetStringOrNull("Status"),
StatusName = reader.GetStringOrNull("StatusName"),
Gender = reader.GetBooleanOrDefault("Gender", true).Value,
Birthday = reader.GetDateTimeOrDefault("Birthday"),
CellPhoneNumber = reader.GetStringOrNull("CellPhoneNumber"),
LinePhoneNumber = reader.GetStringOrNull("LinePhoneNumber"),
Email = reader.GetStringOrNull("Email"),
Email2 = reader.GetStringOrNull("Email2"),
QQ = reader.GetStringOrNull("QQ"),
Address = reader.GetStringOrNull("Address")
};
if (employeeInfo is EmployeeQueryModel && enableReadCost)
{
EmployeeQueryModel empQueryInfo = employeeInfo as EmployeeQueryModel;
empQueryInfo.PositionUnitPrice = reader.GetDecimalOrDefault("PositionUnitPrice");
empQueryInfo.PositionUnitValue = reader.GetDecimalOrDefault("PositionUnitValue");
empQueryInfo.UserUnitPrice = reader.GetDecimalOrDefault("UserUnitPrice");
empQueryInfo.UserUnitValue = reader.GetDecimalOrDefault("UserUnitValue");
}
employeeInfo.EmployeeId = employeeInfo.UserId;
return(employeeInfo as T);
}
public Task <IResultModel> Query([FromQuery] EmployeeQueryModel model)
{
return(_service.Query(model));
}
public Task <IResultModel> QueryWithSameDepartment([FromQuery] EmployeeQueryModel model)
{
return(_service.QueryWithSameDepartment(_loginInfo.AccountId, model));
}
public IActionResult Get([FromQuery] EmployeeQueryModel employeeQuery)
{
return(Ok(_employeeService.GetEmployees(employeeQuery)));
}
public async Task <IActionResult> GetAll([FromQuery] EmployeeQueryModel employeeQueryModel)
{
_logger.LogInformation("Getting employees");
return(Ok(await _employeeService.GetEmployeesAsync(employeeQueryModel)));
}
