public AttendanceLogSearchResultObject GetListAttendanceLog(AttendanceLogSearchCondition model)
{
model.DateFrom = DateTime.ParseExact(model.DateFrom.ToString("dd/MM/yyyy") + " " + model.TimeFrom + ":00.000", "dd/MM/yyyy HH:mm:ss.fff", CultureInfo.InvariantCulture);
model.DateTo = DateTime.ParseExact(model.DateTo.ToString("dd/MM/yyyy") + " " + model.TimeTo + ":59.999", "dd/MM/yyyy HH:mm:ss.fff", CultureInfo.InvariantCulture);
try
{
var listAttendanceLog = (from a in db.AttendanceLog.AsNoTracking()
join e in db.Employee.AsNoTracking() on a.EmployeeId equals e.EmployeeId into g
from ae in g.DefaultIfEmpty()
join d in db.Department.AsNoTracking() on ae.DepartmentId equals d.DepartmentId into gr
from aed in gr.DefaultIfEmpty()
join f in db.JobTitle.AsNoTracking() on ae.JobTitleId equals f.JobTitleId into af
from af1 in af.DefaultIfEmpty()
where (string.IsNullOrEmpty(model.EmployeeCode) || ae.Code.Contains(model.EmployeeCode)) &&
(string.IsNullOrEmpty(model.EmployeeName) || ae.Name.Contains(model.EmployeeName)) &&
(string.IsNullOrEmpty(model.CameraIPAddress) || a.CameraIPAdress.Contains(model.CameraIPAddress)) &&
(string.IsNullOrEmpty(model.DepartmentId) || ae.DepartmentId.Equals(model.DepartmentId)) &&
(model.FaceCount == null || a.FaceCount == model.FaceCount)
orderby a.Date descending
select new AttendanceLogSearchResult()
{
AttendanceLogId = a.AttendanceLogId,
EmployeeName = ae.Name,
EmployeeId = a.EmployeeId,
EmployeeCode = ae.Code,
DepartmentName = aed.Name,
JobTitleName = af1.Name,
Date = (DateTime)a.Date,
Confidence = a.Confidence,
CameraIPAddress = a.CameraIPAdress,
ImageLink = a.ImageLink,
ObjSelect = false,
FaceCount = a.FaceCount,
ImageFace = a.ImageFace,
}).AsQueryable();
listAttendanceLog = listAttendanceLog.Where(r => r.Date <= model.DateTo);
listAttendanceLog = listAttendanceLog.Where(r => r.Date >= model.DateFrom);
listAttendanceLog = listAttendanceLog.Where(r => r.Confidence >= model.ConfidenceFrom);
listAttendanceLog = listAttendanceLog.Where(r => r.Confidence <= model.ConfidenceTo);
var count = listAttendanceLog.Select(u => u.AttendanceLogId).ToList().Count;
var listResult = SQLHelpper.OrderBy(listAttendanceLog, model.OrderBy, model.OrderType).Skip((model.PageNumber - 1) * model.PageSize).Take(model.PageSize).ToList();
AttendanceLogSearchResultObject result = new AttendanceLogSearchResultObject()
{
ListResult = listResult,
TotalItem = count
};
return(result);
}
catch (Exception ex)
{
throw new ErrorException(ErrorMessage.ERR001, ex.InnerException);
}
}
public SearchResultObject <GroupSearchResult> SearchGroupUser(GroupUserSearchCondition searchCondition)
{
db = new TimeAttendanceEntities();
SearchResultObject <GroupSearchResult> searchResult = new SearchResultObject <GroupSearchResult>();
try
{
List <string> userDelete = db.User.AsNoTracking().Where(r => r.DeleteFlg == Constants.DeleteTrue).Select(p => p.UserId).ToList();
var listmodel = (from a in db.Group.AsNoTracking()
join b in db.UserGroup.AsNoTracking() on a.GroupId equals b.GroupId into ab
select new GroupSearchResult()
{
GroupId = a.GroupId,
Name = a.Name,
HomePage = a.HomePage,
Status = a.Status,
Description = a.Description,
CreateBy = a.CreateBy,
CreateDate = a.CreateDate,
UpdateBy = a.UpdateBy,
UpdateDate = a.UpdateDate,
CountUser = ab.Where(r => !userDelete.Any(w => w.Equals(r.UserId))).Count(),
}).AsQueryable();
if (!string.IsNullOrEmpty(searchCondition.Name))
{
listmodel = listmodel.Where(r => r.Name.ToLower().Contains(searchCondition.Name.ToLower()));
}
if (!string.IsNullOrEmpty(searchCondition.Description))
{
listmodel = listmodel.Where(r => r.Description.ToLower().Contains(searchCondition.Description.ToLower()));
}
if (searchCondition.Status.HasValue)
{
listmodel = listmodel.Where(r => r.Status.HasValue && r.Status.Value == searchCondition.Status.Value);
}
searchResult.TotalItem = listmodel.Select(r => r.GroupId).Count();
searchResult.ListResult = SQLHelpper.OrderBy(listmodel, searchCondition.OrderBy, searchCondition.OrderType).Skip((searchCondition.PageNumber - 1) * searchCondition.PageSize)
.Take(searchCondition.PageSize)
.ToList();
return(searchResult);
}
catch (Exception ex)
{
throw new ErrorException(ErrorMessage.ERR001, ex.InnerException);
}
}
public AttendanceLogSearchResultObject GetListNoAttendanceLog(NoAttendanceLogSearchCondition model)
{
try
{
List <string> listEmployeeId = new List <string>();
listEmployeeId = (from t in db.TimeAttendanceLog.AsNoTracking()
where model.DateFrom <= t.Date && t.Date <= model.DateTo
select t.EmployeeId).ToList();
var listEmployee = (from e in db.Employee.AsNoTracking()
join d in db.Department.AsNoTracking() on e.DepartmentId equals d.DepartmentId
join j in db.JobTitle.AsNoTracking() on e.JobTitleId equals j.JobTitleId
where (string.IsNullOrEmpty(model.DepartmentId) || e.DepartmentId.Equals(model.DepartmentId)) &&
(string.IsNullOrEmpty(model.JobTitleId) || e.JobTitleId.Equals(model.JobTitleId)) &&
(string.IsNullOrEmpty(model.EmployeeCode) || e.Code.Contains(model.EmployeeCode)) &&
(string.IsNullOrEmpty(model.EmployeeName) || e.Name.Contains(model.EmployeeName)) &&
!listEmployeeId.Contains(e.EmployeeId)
select new AttendanceLogSearchResult()
{
EmployeeId = e.EmployeeId,
EmployeeName = e.Name,
EmployeeCode = e.Code,
DepartmentName = d.Name,
JobTitleName = j.Name,
}).AsQueryable();
var count = listEmployee.ToList().Count;
var listResult = SQLHelpper.OrderBy(listEmployee, model.OrderBy, model.OrderType).Skip((model.PageNumber - 1) * model.PageSize).Take(model.PageSize).ToList();
AttendanceLogSearchResultObject result = new AttendanceLogSearchResultObject()
{
ListResult = listResult,
TotalItem = count
};
return(result);
}
catch (Exception ex)
{
throw new ErrorException(ErrorMessage.ERR001, ex.InnerException);
}
}
public SearchResultObject <UserSearchResult> SearchUserCustomer(UserSearchCondition searchCondition)
{
db = new TimeAttendanceEntities();
SearchResultObject <UserSearchResult> searchResult = new SearchResultObject <UserSearchResult>();
try
{
var listmodel = (from a in db.User.AsNoTracking()
where a.DeleteFlg == Constants.DeleteFalse && (string.IsNullOrEmpty(searchCondition.Type) || a.Type.Equals(searchCondition.Type))
join c in db.UserGroup.AsNoTracking() on a.UserId equals c.UserId into ac
from acv in ac.DefaultIfEmpty()
join d in db.Group.AsNoTracking() on acv.GroupId equals d.GroupId into cd
from cdv in cd.DefaultIfEmpty()
select new UserSearchResult
{
UserId = a.UserId,
UnitId = a.UnitId,
Name = a.Name,
FullName = a.FullName,
BirthDay = a.BirthDay,
Agency = a.Agency,
Email = a.Email,
Role = a.Role,
PhoneNumber = a.PhoneNumber,
Status = a.Status,
Description = a.Description,
ImageLink = a.ImageLink,
Address = a.Address,
CreateBy = a.CreateBy,
CreateDate = a.CreateDate,
UpdateBy = a.UpdateBy,
UpdateDate = a.UpdateDate,
GroupId = acv.GroupId,
GroupName = cdv != null ? cdv.Name : string.Empty,
Type = a.Type,
}).AsQueryable();
if (!string.IsNullOrEmpty(searchCondition.GroupId))
{
listmodel = listmodel.Where(r => r.GroupId.Equals(searchCondition.GroupId));
}
if (!string.IsNullOrEmpty(searchCondition.Name))
{
listmodel = listmodel.Where(r => r.Name.ToUpper().Contains(searchCondition.Name.ToUpper()));
}
if (!string.IsNullOrEmpty(searchCondition.FullName))
{
listmodel = listmodel.Where(r => r.FullName.ToUpper().Contains(searchCondition.FullName.ToUpper()));
}
if (searchCondition.Status.HasValue)
{
listmodel = listmodel.Where(r => r.Status.HasValue && r.Status.Value == searchCondition.Status.Value);
}
if (!string.IsNullOrEmpty(searchCondition.UnitId))
{
listmodel = listmodel.Where(r => r.UnitId.Equals(searchCondition.UnitId));
}
if (!string.IsNullOrEmpty(searchCondition.PhoneNumber))
{
listmodel = listmodel.Where(r => r.PhoneNumber.ToUpper().Contains(searchCondition.PhoneNumber.ToUpper()));
}
if (searchCondition.Status.HasValue)
{
listmodel = listmodel.Where(r => r.Status.HasValue && r.Status.Value == searchCondition.Status.Value);
}
searchResult.TotalItem = listmodel.Select(r => r.UserId).Count();
var listResult = SQLHelpper.OrderBy(listmodel, searchCondition.OrderBy, searchCondition.OrderType).Skip((searchCondition.PageNumber - 1) * searchCondition.PageSize)
.Take(searchCondition.PageSize)
.ToList();
searchResult.ListResult = listResult;
}
catch (Exception ex)
{
throw new ErrorException(ErrorMessage.ERR001, ex.InnerException);
}
return(searchResult);
}
public SearchResultObject <UserEventLogSearchResult> SearchUserEventLog(UserEventLogSearchCondition searchCondition, string saveOption)
{
db = new TimeAttendanceEntities();
SearchResultObject <UserEventLogSearchResult> searchResult = new SearchResultObject <UserEventLogSearchResult>();
try
{
var listmodel = (from a in db.UserEventLog.AsNoTracking()
join b in db.User.AsNoTracking() on a.UserId equals b.UserId into ab
from abv in ab.DefaultIfEmpty()
select new UserEventLogSearchResult()
{
UserEventLogId = a.UserEventLogId,
UserId = a.UserId,
Description = a.Description,
LogType = a.LogType,
LogTypeName = a.LogType == 0 ? "Truy cập hệ thống" : "Khai thác dữ liệu",
Type = abv.Type,
CreateDate = a.CreateDate,
UserName = abv.Name,
FullName = abv.FullName
}).AsQueryable();
if (!string.IsNullOrEmpty(searchCondition.UserName))
{
listmodel = listmodel.Where(r => r.UserName != null && r.UserName.ToUpper().Contains(searchCondition.UserName.ToUpper()));
}
if (!string.IsNullOrEmpty(searchCondition.UserType))
{
listmodel = listmodel.Where(r => r.Type != null && r.Type.Equals(searchCondition.UserType));
}
if (!string.IsNullOrEmpty(searchCondition.UserIdSearch))
{
listmodel = listmodel.Where(r => r.UserId != null && r.UserId.Equals(searchCondition.UserIdSearch));
}
if (!string.IsNullOrEmpty(searchCondition.FullName))
{
listmodel = listmodel.Where(r => r.FullName != null && r.FullName.ToUpper().Contains(searchCondition.FullName.ToUpper()));
}
if (!string.IsNullOrEmpty(searchCondition.Description))
{
listmodel = listmodel.Where(r => r.Description.ToUpper().Contains(searchCondition.Description.ToUpper()));
}
if (searchCondition.LogType.HasValue)
{
listmodel = listmodel.Where(r => r.LogType.HasValue && r.LogType.Value == searchCondition.LogType.Value);
}
if (searchCondition.LogDateFrom.HasValue)
{
searchCondition.LogDateFrom = DateTimeUtils.ConvertDateFrom(searchCondition.LogDateFrom);
listmodel = listmodel.Where(r => r.CreateDate.HasValue && r.CreateDate >= searchCondition.LogDateFrom);
}
if (searchCondition.LogDateTo.HasValue)
{
searchCondition.LogDateTo = DateTimeUtils.ConvertDateTo(searchCondition.LogDateTo);
listmodel = listmodel.Where(r => r.CreateDate.HasValue && r.CreateDate <= searchCondition.LogDateTo);
}
searchResult.TotalItem = listmodel.Select(r => r.UserEventLogId).Count();
var listResult = SQLHelpper.OrderBy(listmodel, searchCondition.OrderBy, searchCondition.OrderType).Skip((searchCondition.PageNumber - 1) * searchCondition.PageSize)
.Take(searchCondition.PageSize)
.ToList();
string pathFile = string.Empty;
if ((saveOption.Equals("PDF") || saveOption.Equals("XLSX")) && searchResult.TotalItem > 0)
{
if (searchResult.TotalItem > Constants.MAX_RETURN_DATA_ROW)
{
throw new BusinessException(ErrorMessage.ERR007);
}
else
{
pathFile = this.Export(saveOption, listmodel.ToList(), searchCondition);
}
}
searchResult.ListResult = listResult;
searchResult.PathFile = pathFile;
}
catch (Exception ex)
{
throw new ErrorException(ErrorMessage.ERR001, ex.InnerException);
}
return(searchResult);
}