public PaginatedList <UserItemView> Search(UserSearchFilter filter, PaginationInfoView paginationInfo)
{
var query = _repository.Query <User>()
.Where(x => x.Status == Core.UserStatus.Active);
if (!string.IsNullOrWhiteSpace(filter.UserName))
{
query = query.Where(x => x.UserName == filter.UserName);
}
if (!string.IsNullOrWhiteSpace(filter.FirstName))
{
query = query.Where(x => x.FirstName == filter.FirstName);
}
if (!string.IsNullOrWhiteSpace(filter.LastName))
{
query = query.Where(x => x.LastName == filter.LastName);
}
if (filter.Status != Core.UserStatus.None)
{
query = query.Where(x => x.Status == filter.Status);
}
var newQuery = query.Select(
x => new UserItemView
{
FirstName = x.FirstName,
LastName = x.LastName,
UserName = x.UserName,
Status = x.Status,
Id = x.Id
});
return(new PaginatedList <UserItemView>(paginationInfo, newQuery));
}
public void FilterUsersTable()
{
UserSearchFilter filter = new UserSearchFilter
{
Name = name_filter.Text.ToString(),
Surname = surname_filter.Text.ToString(),
Email = email_filter.Text.ToString(),
Username = surname_filter.Text.ToString(),
Roles = new string[] { rol_filter.Text.ToString() },
IdDocument = document_filter.Text.ToString(),
IdDocumentType = document_type_filter.Text.ToString()
};
UserWSClient ws = new UserWSClient();
for (int indx = (userTable.RowCount * userTable.ColumnCount) - 1; indx >= userTable.ColumnCount; indx--)
{
userTable.Controls.RemoveAt(indx);
}
userTable.RowCount = 1;
UserWS.editUserData[] users = ws.listUsersByFilter(new UserWS.Security {
BinarySecurityToken = authToken
}, new listUsersByFilter {
arg1 = filter
});
FillUsersTable(users);
}
public User[] FindUsersByFilter(UserSearchFilter filter)
{
using (var ds = new DataService())
{
return(ds.getUserDAO().FindUsersByFilter(filter));
}
}
/// <summary>
/// Returns all <see cref="IEnumerable<UserDocument>">users</see> matching <paramref name="filter"/>.
/// </summary>
/// <remarks>
/// <see cref="string.StartsWith"/> is used for matching.
/// </remarks>
public IEnumerable <UserSummary> Find(UserSearchFilter filter, UserSearchOrderBy orderBy)
{
Guard.ArgumentNotNull(filter, nameof(filter));
Guard.ArgumentNotNull(orderBy, nameof(orderBy));
var queryable = AsQueryable();
// Assuming filter conditions are or'd as most reasonable.
// Any other filtering would have to be defined at the requirements level.
var results = queryable.Where(u =>
(!string.IsNullOrEmpty(filter.FirstName) && u.FirstName.StartsWith(filter.FirstName)) ||
(!string.IsNullOrEmpty(filter.LastName) && u.LastName.StartsWith(filter.LastName)) ||
(filter.HasUserEverConnected && u.LastConnectionDate != null) ||
(!filter.HasUserEverConnected && u.LastConnectionDate == null)
);
// Assuming the orderings are mutually exclusive.
// Any other logic would have to be defined at the requirements level.
if (orderBy.LastConnectionDate)
{
results.OrderBy(u => u.LastConnectionDate);
}
else if (orderBy.CreationDate)
{
results.OrderBy(u => u.Audit.CreationDate);
}
else if (orderBy.LastName)
{
results.OrderBy(u => u.LastName);
}
var summary = results.ToList().Select(r => mapper.Map <UserSummary>(r));
return(summary);
}
public ActionResult List()
{
ListViewModel model = new ListViewModel();
model.Filter = new ListFilterViewModel();
model.CurrentPage = 1;
model.PageSize = 10;
UserSearchFilter searchFilter = new UserSearchFilter();
searchFilter.CurrentPage = model.CurrentPage.HasValue ? model.CurrentPage.Value : 1;
searchFilter.PageSize = model.PageSize.HasValue ? model.PageSize.Value : 10;
searchFilter.SortOn = model.SortOn;
searchFilter.SortDirection = model.SortDirection;
searchFilter.Filter_Name = model.Filter.Filter_Name;
searchFilter.Filter_LastName = model.Filter.Filter_LastName;
searchFilter.Filter_BloodGroupId = model.Filter.Filter_BloodGroupId;
searchFilter.Filter_CityCode = model.Filter.Filter_CityCode;
model.FilterCitySelectList = GetCitySelectList();
model.FilterBloodGroupSelectList = GetBloodGroupSelectList();
model.FilterSexSelectList = GetSexSelectList();
try
{
model.DataList = _userService.GetAllPaginatedWithDetailBySearchFilter(searchFilter);
}
catch
{
model.DataList = new PaginatedList <UserWithDetail>(new List <UserWithDetail>(), 0, searchFilter.CurrentPage, searchFilter.PageSize, searchFilter.SortOn, searchFilter.SortDirection);
ViewBag.ErrorMessage = "Error";
}
return(View(model));
}
/// <summary>
/// Method to get list of users
/// </summary>
/// <param name="searchFilter">User Search Filter</param>
/// <returns>list of users</returns>
public async Task <IEnumerable <User> > GetUsers(UserSearchFilter searchFilter)
{
var userList = new List <User>();
try
{
var dbUsers = _context.Users.AsQueryable();
if (!string.IsNullOrEmpty(searchFilter.Name))
{
dbUsers = dbUsers.Where(i => i.FirstName.Contains(searchFilter.Name) ||
i.LastName.Contains(searchFilter.Name));
}
if (searchFilter.DisplayRecordCount > 0)
{
dbUsers = dbUsers.Take((int)searchFilter.DisplayRecordCount);
}
userList = await dbUsers.ToListAsync();
}
catch (Exception ex)
{
throw ex;
}
return(userList);
}
public virtual ActionResult Manage(UserSearchFilter filter)
{
if (!Request.IsAjaxRequest())
{
return(View(_userSrv.Get(filter)));
}
else
{
return(PartialView("Partials/_List", _userSrv.Get(filter)));
}
}
public UserListModel Get(UserSearchFilter searchFilter)
{
UserListFormData formData = new UserListFormData();
SearchResult <UserDTO> searchResult = userSearch.GetDataByFilter(searchFilter);
return(new UserListModel()
{
FormData = formData,
SearchResult = searchResult
});
}
/// <summary>
/// Gets a user by their email address, but do not throw any errors.
/// </summary>
/// <param name="email">The email address of the user to search for.</param>
/// <param name="filter">The search filter to use (the email will override the search string)</param>
/// <returns>An optional response containing the user (if it was found), and any error (if there was one).</returns>
public ApiOptionalResponse<User> GetByEmailAddressOptional(string email, UserSearchFilter filter = null)
{
filter = filter ?? new UserSearchFilter();
filter.Search = email;
var results = FindAllOptional(filter);
if(!results.HasValue)
return new ApiOptionalResponse<User> { Exception = results.Exception };
foreach(var user in results.Value)
if(user.EmailAddress == email)
return new ApiOptionalResponse<User> { Value = user };
return new ApiOptionalResponse<User> { Exception = new ApiErrorException($"No user was found by the email address \"{email}\".") };
}
/// <summary>
/// Gets a user by their username, but do not throw any errors.
/// </summary>
/// <param name="username">The username of the user to search for.</param>
/// <param name="filter">The search filter to use (the username will override the search string)</param>
/// <returns>An optional response containing the user (if it was found), and any error (if there was one).</returns>
public ApiOptionalResponse<User> GetByUserNameOptional(string username, UserSearchFilter filter = null)
{
filter = filter ?? new UserSearchFilter();
filter.Search = username;
var results = FindAllOptional(filter);
if(!results.HasValue)
return new ApiOptionalResponse<User> { Exception = results.Exception };
foreach(var user in results.Value)
if(user.UserName == username)
return new ApiOptionalResponse<User> { Value = user };
return new ApiOptionalResponse<User> { Exception = new ApiErrorException($"No user was found by the username \"{username}\".") };
}
public IEnumerable <User> GetListByFilter(UserSearchFilter filter)
{
List <User> contactList = new List <User>();
using (ClientManagementContext db = new ClientManagementContext())
{
var query = db.Users
.Include(f => f.Contact)
.Include(f => f.UserRole)
.Where(f => (filter.SearchTerm == string.Empty || f.Username.Contains(filter.SearchTerm.Trim()) ||
f.Contact.Email.Contains(filter.SearchTerm.Trim()) ||
f.Contact.FirstName.Contains(filter.SearchTerm.Trim()) ||
f.Contact.CellPhone.Contains(filter.SearchTerm.Trim())));
filter.TotalCount = query.Count();
//sorting
Func <User, object> OrderByStringField = null;
switch (filter.SortColumn)
{
case "Username":
OrderByStringField = p => p.Username;
break;
case "Contact.FirstName":
OrderByStringField = p => p.Contact.FirstName;
break;
case "Contact.Email":
OrderByStringField = p => p.Contact.Email;
break;
case "CellPhone":
OrderByStringField = p => p.Contact.CellPhone;
break;
default:
OrderByStringField = p => p.Contact.FirstName;
break;
}
//end sorting
var finalQuery = filter.SortDirection == "ASC" ? query.OrderBy(OrderByStringField) : query.OrderByDescending(OrderByStringField);
contactList = finalQuery.Skip((filter.PageNumber - 1) * filter.PageSize)
.Take(filter.PageSize)
.AsParallel()
.ToList();
}
return(new StaticPagedList <User>(contactList, filter.PageNumber, filter.PageSize, filter.TotalCount));
}
private static List <IFilter> getUserFilter(string filter)
{
if (string.IsNullOrEmpty(filter))
{
return(new List <IFilter>());
}
UserSearchFilter searchCriteria = new UserSearchFilter(filter);
return(new List <IFilter>()
{
searchCriteria
});
}
public IHttpActionResult Search([FromUri] UserSearchFilter filter)
{
ThrowIfUserHasNoRole(readRole);
if (filter == null)
{
throw new KairosException("Missing search filter parameter");
}
using (var userSearch = new UserSearch(Db))
{
var data = userSearch.GetDataByFilter(filter);
return(Ok(new SuccessResponse(data)));
}
}
public virtual IActionResult Excel(UserSearchFilter filter)
{
try
{
string fileName = $"Customers_{PersianDateTime.Now.ToString(PersianDateTimeFormat.Date).Replace("/", "-")}.xlsx";
var contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
var fileContent = _userSrv.ExportExcel(filter);
return(File(fileContent, contentType, fileName));
}
catch
{
return(null);
}
}
public IEnumerable <UserSummary> Find(
[FromBody] UserSearchFilter filter,
[FromQuery(Name = "orderByLastName")] bool orderByLastName,
[FromQuery(Name = "orderByCreationDate")] bool orderByCreationDate,
[FromQuery(Name = "orderByLastConnectionDate")] bool orderByLastConnectionDate)
{
var orderBy = new UserSearchOrderBy
{
LastName = orderByLastName,
CreationDate = orderByCreationDate,
LastConnectionDate = orderByLastConnectionDate
};
return(service.Find(filter, orderBy));
}
public IHttpActionResult Search([FromUri] UserSearchFilter filter)
{
string accessType = "User_ViewAll";
ThrowIfUserCannotAccess(accessType);
if (filter == null)
{
throw new KairosException("Missing search filter parameter");
}
using (var userSearch = new UserSearch(Db))
{
var data = userSearch.GetDataByFilter(filter);
return(Ok(new SuccessResponse(data)));
}
}
/// <summary>
///
/// </summary>
/// <param name="searchFilter"></param>
/// <returns></returns>
public async Task <IEnumerable <UserModel> > GetUsers(UserSearchFilter searchFilter)
{
var userList = new List <UserModel>();
try
{
if (searchFilter is null)
{
throw new ArgumentNullException(nameof(UserSearchFilter));
}
var dbSearchFilter = new Repository.Filters.UserSearchFilter()
{
Name = searchFilter.Name,
DisplayRecordCount = searchFilter.DisplayRecordCount
};
var dbUserList = await _userRepository.GetUsers(dbSearchFilter);
dbUserList.ToList().ForEach(item =>
{
userList.Add(new UserModel()
{
ID = item.ID,
Email = item.Email,
DateOfBirth = item.DateOfBirth,
PhoneNumber = item.PhoneNumber,
Name = new Name()
{
FirstName = item.FirstName,
LastName = item.LastName,
Title = item.Title
},
Picture = new Picture()
{
ProfileThumbnailURL = item.ProfileThumbnailURL
}
});
});
}
catch (Exception ex)
{
throw ex;
}
return(userList);
}
public HttpResponseMessage Export([FromBody] UserSearchFilter filter)
{
ThrowIfUserHasNoRole(exportRole);
if (filter != null)
{
filter.PageSize = 1000000;
}
string accessType = "User_ViewAll";
ThrowIfUserHasNoRole(accessType);
//if (filter == null)
// throw new KairosException("Missing search filter parameter");
UserExport userExport = new UserExport();
return(userExport.Export(Db, "UserUpload", filter));
}
public ActionResult List(ListViewModel model)
{
if (model.Filter == null)
{
model.Filter = new ListFilterViewModel();
}
if (!model.CurrentPage.HasValue)
{
model.CurrentPage = 1;
}
if (!model.PageSize.HasValue)
{
model.PageSize = 10;
}
model.ProfileSelectList = GetProfileSelectList("");
UserSearchFilter searchFilter = new UserSearchFilter();
searchFilter.CurrentPage = model.CurrentPage.HasValue ? model.CurrentPage.Value : 1;
searchFilter.PageSize = model.PageSize.HasValue ? model.PageSize.Value : 10;
searchFilter.SortOn = model.SortOn;
searchFilter.SortDirection = model.SortDirection;
searchFilter.Filter_Name = model.Filter.Filter_Name;
searchFilter.Filter_Surname = model.Filter.Filter_Surname;
searchFilter.Filter_ProfileId = model.Filter.Filter_ProfileId;
var apiResponseModel = _userService.GetAllPaginatedWithDetailBySearchFilter(SessionHelper.CurrentUser.UserToken, searchFilter);
if (apiResponseModel.ResultStatusCode == ResultStatusCodeStatic.Success)
{
model.DataList = apiResponseModel.Data;
}
else
{
ViewBag.ErrorMessage = apiResponseModel.ResultStatusMessage;
model.ProfileSelectList = GetProfileSelectList("");
model.DataList = new Business.Models.PaginatedList <UserWithDetail>(new List <UserWithDetail>(), 0, model.CurrentPage.Value, model.PageSize.Value, model.SortOn, model.SortDirection);
return(View(model));
}
// select lists
return(View(model));
}
public ActionResult List(ListViewModel model)
{
// filter bilgilerinin default boş değerlerle doldurulması sağlanıyor
if (model.Filter == null)
{
model.Filter = new ListFilterViewModel();
}
if (!model.CurrentPage.HasValue)
{
model.CurrentPage = 1;
}
if (!model.PageSize.HasValue)
{
model.PageSize = 10;
}
UserSearchFilter searchFilter = new UserSearchFilter();
searchFilter.CurrentPage = model.CurrentPage.HasValue ? model.CurrentPage.Value : 1;
searchFilter.PageSize = model.PageSize.HasValue ? model.PageSize.Value : 10;
searchFilter.SortOn = model.SortOn;
searchFilter.SortDirection = model.SortDirection;
searchFilter.Filter_Name = model.Filter.Filter_Name;
searchFilter.Filter_LastName = model.Filter.Filter_LastName;
searchFilter.Filter_BloodGroupId = model.Filter.Filter_BloodGroupId;
searchFilter.Filter_CityCode = model.Filter.Filter_CityCode;
model.FilterCitySelectList = GetCitySelectList();
model.FilterBloodGroupSelectList = GetBloodGroupSelectList();
model.FilterSexSelectList = GetSexSelectList();
try
{
model.DataList = _userService.GetAllPaginatedWithDetailBySearchFilter(searchFilter);
}
catch
{
model.DataList = new PaginatedList <UserWithDetail>(new List <UserWithDetail>(), 0, searchFilter.CurrentPage, searchFilter.PageSize, searchFilter.SortOn, searchFilter.SortDirection);
ViewBag.ErrorMessage = "Error";
}
return(View(model));
}
public ActionResult List(UserSearchFilter filter)
{
filter.SearchTerm = filter.SearchTerm ?? string.Empty;
filter.SortColumn = "Username";
filter.SortDirection = "ASC";
var userList = _userService.GetListByFilter(filter);
var model = new UserListViewModel
{
Users = userList,
SearchFilter = filter
};
//QueryStringHelper.Add("pageNumber", filter.PageNumber);
//QueryStringHelper.Add("searchTerm", filter.SearchTerm.ToString());
//QueryStringHelper.Add("status", filter.Status.ToString());
return(View(model));
}
public ActionResult Changeuserpassword(UserSearchFilter model)
{
ModelState.Remove("RoleId");
ModelState.Remove("SearchFilter");
if (ModelState.IsValid)
{
model.UserName = model.UserNamePassword.DecryptParameter();
model.ActionUser = Session["UserName"].ToString();
model.IpAddress = ApplicationUtilities.GetIP();
//model.BrowserInfo = ApplicationUtilities.GetBrowserInfo();
var common = model.MapObject <shared.Models.User.ChangePasswordCommon>();
CommonDbResponse resp = buss.ChangeUserPassword(common);//.SetMessageInTempData(this);
if (resp.Code == ResponseCode.Success)
{
this.ShowPopup(0, "Password Changed Successfully!!");
//resp.SetMessageInTempData(this);
return(RedirectToAction("Index"));
}
}
this.ShowPopup(1, "Something went wrong!!");
return(RedirectToAction("SearchUser", model));
}
public ActionResult List(int?pageNumber, string searchTerm, string sort = "Username", string sortdir = "ASC")
{
pageNumber = pageNumber ?? 1;
var filter = new UserSearchFilter
{
PageNumber = (int)pageNumber,
SearchTerm = searchTerm ?? string.Empty,
SortColumn = sort,
SortDirection = sortdir
};
var userList = _userService.GetListByFilter(filter);
var model = new UserListViewModel
{
Users = userList,
SearchFilter = filter
};
return(Request.IsAjaxRequest() ? (ActionResult)PartialView("_UserList", model) : View(model));
}
public PagingListDetails <User> Get(UserSearchFilter filter)
{
Expression <Func <User, bool> > conditions = x => true;
if (filter != null)
{
if (!string.IsNullOrWhiteSpace(filter.FullNameF))
{
conditions = conditions.And(x => x.FullName.Contains(filter.FullNameF));
}
if (!string.IsNullOrWhiteSpace(filter.EmailF))
{
conditions = x => x.Email.Contains(filter.EmailF);
}
if (!string.IsNullOrWhiteSpace(filter.MobileNumberF))
{
conditions = x => x.MobileNumber.ToString().Contains(filter.MobileNumberF);
}
}
var items = _userRepo.Get(conditions, filter, x => x.OrderByDescending(u => u.InsertDateMi));
return(items);
}
public ActionResult SearchUser(UserSearchFilter USF)
{
ViewBag.SearchFilter = ApplicationUtilities.SetDDLValue(LoadDropdownList("searchfilter") as Dictionary <string, string>, USF.SearchFilter, "--Select--");
if (!string.IsNullOrEmpty(USF.SearchField) && !string.IsNullOrEmpty(USF.SearchFilter))
{
string username = Session["username"].ToString();
var lst = buss.GetSearchUserList(USF.SearchField, USF.SearchFilter, username);
foreach (var item in lst)
{
item.Action = StaticData.GetActions("SearchUser", item.UserID.EncryptParameter(), this, "", "", item.IsActive, Session["UserName"].ToString(), item.AgentUserId.EncryptParameter(), item.IsPrimary);
item.Status = "<span class='badge badge-" + (item.IsActive.Trim().ToUpper() == "Y" ? "success" : "danger") + "'>" + (item.IsActive.Trim().ToUpper() == "Y" ? "Active" : "Blocked") + "</span>";
}
IDictionary <string, string> param = new Dictionary <string, string>();
//param.Add("AgentUserId", "Agent Id");
param.Add("FullName", "Full Name");
param.Add("UserName", "User Name");
param.Add("Email", "Email");
param.Add("Status", "Status");
param.Add("PhoneNo", "Mobile Number");
param.Add("CreatedBy", "Created By");
param.Add("CreateDate", "Created On");
param.Add("Action", "Action");
ProjectGrid.column = param;
//Ends
var grid = ProjectGrid.MakeGrid(lst, "hidebreadcrumb", "", 10, false, "", "", "", "", "", "");
ViewData["grid"] = grid;
}
else
{
this.ShowPopup(1, "Please Fill the fields");
}
return(View(USF));
}
public PaginatedList <UserWithDetail> GetAllPaginatedWithDetailBySearchFilter(UserSearchFilter searchFilter)
{
PaginatedList <UserWithDetail> resultList = new PaginatedList <UserWithDetail>(new List <UserWithDetail>(), 0, searchFilter.CurrentPage, searchFilter.PageSize, searchFilter.SortOn, searchFilter.SortDirection);
using (AppDBContext dbContext = new AppDBContext(_config))
{
var query = from u in dbContext.User
from b in dbContext.BloodGroup.Where(x => x.Id == u.BloodGroupId).DefaultIfEmpty()
from c in dbContext.City.Where(x => x.Code == u.CityCode).DefaultIfEmpty()
select new UserWithDetail()
{
Id = u.Id,
Age = u.Age,
BloodGroupId = u.BloodGroupId,
CityCode = u.CityCode,
Name = u.Name,
LastName = u.LastName,
Email = u.Email,
Phone = u.Phone,
UserName = u.UserName,
SexId = u.SexId,
BloodGroup_Name = b == null ? string.Empty : b.Name,
City_Name = c == null ? string.Empty : c.Name,
};
// filtering
if (!string.IsNullOrEmpty(searchFilter.Filter_Name))
{
query = query.Where(r => r.Name.Contains(searchFilter.Filter_Name));
}
if (!string.IsNullOrEmpty(searchFilter.Filter_LastName))
{
query = query.Where(r => r.LastName.Contains(searchFilter.Filter_LastName));
}
if (searchFilter.Filter_BloodGroupId.HasValue)
{
query = query.Where(r => r.BloodGroupId == searchFilter.Filter_BloodGroupId.Value);
}
if (searchFilter.Filter_CityCode.HasValue)
{
query = query.Where(r => r.CityCode == searchFilter.Filter_CityCode.Value);
}
// asnotracking
query = query.AsNoTracking();
//total count
var totalCount = query.Count();
//sorting
if (!string.IsNullOrEmpty(searchFilter.SortOn))
{
// using System.Linq.Dynamic.Core; nuget paketi ve namespace eklenmelidir, dynamic order by yapmak icindir
query = query.OrderBy(searchFilter.SortOn + " " + searchFilter.SortDirection.ToUpper());
}
else
{
// deefault sıralama vermek gerekiyor yoksa skip metodu hata veriyor ef 6'da -- 28.10.2019 15:40
// https://stackoverflow.com/questions/3437178/the-method-skip-is-only-supported-for-sorted-input-in-linq-to-entities
query = query.OrderBy(r => r.Id);
}
//paging
query = query.Skip((searchFilter.CurrentPage - 1) * searchFilter.PageSize).Take(searchFilter.PageSize);
resultList = new PaginatedList <UserWithDetail>(
query.ToList(),
totalCount,
searchFilter.CurrentPage,
searchFilter.PageSize,
searchFilter.SortOn,
searchFilter.SortDirection
);
}
return(resultList);
}
public async Task <ActionResult <IEnumerable <UserModel> > > GetUsers([FromQuery] UserSearchFilter searchFilter)
{
return(Ok(await _userManager.GetUsers(searchFilter)));
}
public PaginatedList <UserWithDetail> GetAllPaginatedWithDetailBySearchFilter(UserSearchFilter searchFilter)
{
PaginatedList <UserWithDetail> resultList = new PaginatedList <UserWithDetail>(new List <UserWithDetail>(), 0, searchFilter.CurrentPage, searchFilter.PageSize, searchFilter.SortOn, searchFilter.SortDirection);
using (AppDbContext dbContext = new AppDbContext())
{
var query = from u in dbContext.User
from p in dbContext.Profile.Where(p => p.Id == u.ProfileId && p.IsDeleted == false).DefaultIfEmpty()
where u.IsDeleted == false
select new UserWithDetail()
{
Id = u.Id,
UserName = u.UserName,
Password = u.Password,
Name = u.Name,
Surname = u.Surname,
Mail = u.Mail,
ProfileId = u.ProfileId,
Profile_Name = p == null ? String.Empty : p.Name,
};
// filtering
if (searchFilter.Filter_ProfileId.HasValue)
{
query = query.Where(r => r.ProfileId == searchFilter.Filter_ProfileId.Value);
}
if (!string.IsNullOrEmpty(searchFilter.Filter_Name))
{
query = query.Where(r => r.Name.Contains(searchFilter.Filter_Name));
}
if (!string.IsNullOrEmpty(searchFilter.Filter_Surname))
{
query = query.Where(r => r.Surname.Contains(searchFilter.Filter_Surname));
}
// asnotracking
query = query.AsNoTracking();
//total count
var totalCount = query.Count();
//sorting
if (!string.IsNullOrEmpty(searchFilter.SortOn))
{
// System.Linq.Dynamic nuget paketi ve namespace eklenmelidir, dynamic order by yapmak icindir
query = query.OrderBy(searchFilter.SortOn + " " + searchFilter.SortDirection.ToUpper());
}
else
{
// deefault sıralama vermek gerekiyor yoksa skip metodu hata veriyor ef 6'da
// https://stackoverflow.com/questions/3437178/the-method-skip-is-only-supported-for-sorted-input-in-linq-to-entities
query = query.OrderBy(r => r.Id);
}
//paging
query = query.Skip((searchFilter.CurrentPage - 1) * searchFilter.PageSize).Take(searchFilter.PageSize);
resultList = new PaginatedList <UserWithDetail>(
query.ToList(),
totalCount,
searchFilter.CurrentPage,
searchFilter.PageSize,
searchFilter.SortOn,
searchFilter.SortDirection
);
}
return(resultList);
}
public override Task <IList <User> > Search(UserSearchFilter name, int offset, int limit, string sortColumn)
{
return(connection.SelectAllFrom <User>().OrderBy(x => x.UserName).Limit(offset, limit).ToListAsync());
}
public HttpResponseMessage Export(GlobalSolusindoDb Db, string fileName, UserSearchFilter filter)
{
_fileName = fileName;
_workbook = new XSSFWorkbook(); //Creating New Excel object
var workbook = new XLWorkbook();
using (var userSearch = new UserSearch(Db))
{
var data = userSearch.GetDataByFilter(filter);
var dataExport = data.Records.Select(c => new UserExportDTO
{
Nomor = c.User_PK,
JoinDate = c.JoinDate,
UserName = c.Username,
KategoriJabatanTitle = c.KategoriJabatanTitle,
Name = c.Name,
TglLahir = c.TglLahir,
NoKTP = c.NoKTP,
ReligionName = c.Religion == null || c.Religion == 0 ? "" : religionQuery.GetByPrimaryKey((int)c.Religion).Name, //?
CategoryContract = c.CategoryContract == null || c.CategoryContract == 0 ? "" : categoryContractQuery.GetByPrimaryKey((int)c.CategoryContract).Name,
Project = c.Project == null || c.Project == 0 ? "" : projectQuery.GetByPrimaryKey((int)c.Project).Project_PK + "-" + projectQuery.GetByPrimaryKey((int)c.Project).OperatorTitle + "-" + projectQuery.GetByPrimaryKey((int)c.Project).VendorTitle + "-" + projectQuery.GetByPrimaryKey((int)c.Project).DeliveryAreaTitle, //?
Gender = c.Gender == null || c.Gender == 0 ? "" : genderQuery.GetByPrimaryKey((int)c.Gender).Name,
MartialStatus = c.MaritalStatus == null || c.MaritalStatus == 0 ? "" : maritalStatusQuery.GetByPrimaryKey((int)c.MaritalStatus).Name,
NPWP = c.NPWP, //?
BPJS = c.BPJS, //?
ContactNumber = c.NoHP,
G1EmailID = c.Email,
PersonalEmail = c.PersonalEmail,
Address = c.Address,
NamaBank = c.BankName, //?
NoRekening = c.AccountNumber, //?
Salary = c.Salary, //?
Remark = c.Description, //?
Status = null //?
}).ToList();
projectQuery.Dispose();
DataTable user = new DataTable("UserUpload");
UserExportDTO obj = new UserExportDTO();
foreach (var item in obj.GetType().GetProperties())
{
user.Columns.Add(item.Name);
}
DataRow dr;
foreach (var item in dataExport)
{
dr = user.NewRow();
dr["Nomor"] = item.Nomor;
dr["UserName"] = item.UserName;
dr["KategoriJabatanTitle"] = item.KategoriJabatanTitle;
dr["Name"] = item.Name;
dr["TglLahir"] = item.TglLahir;
dr["NoKTP"] = item.NoKTP;
dr["ReligionName"] = item.ReligionName;
dr["CategoryContract"] = item.CategoryContract;
dr["Project"] = item.Project;
dr["Gender"] = item.Gender;
dr["MartialStatus"] = item.MartialStatus;
dr["NPWP"] = item.NPWP;
dr["BPJS"] = item.BPJS;
dr["JoinDate"] = item.JoinDate;
dr["ContactNumber"] = item.ContactNumber;
dr["G1EmailID"] = item.G1EmailID;
dr["PersonalEmail"] = item.PersonalEmail;
dr["Address"] = item.Address;
dr["NamaBank"] = item.NamaBank;
dr["NoRekening"] = item.NoRekening;
dr["Salary"] = item.Salary;
dr["Remark"] = item.Remark;
dr["Status"] = item.Status;
user.Rows.Add(dr);
}
workbook.AddWorksheet(user);
var worksheet = workbook.Worksheet(1);
worksheet.Columns().Width = 15;
using (var jabatanQry = new KategoriJabatanQuery())
{
//SETUP TABLE
DataTable validationTable = new DataTable();
validationTable.TableName = "Kategori Jabatan";
//SETUP COLUMN
LOVDTO objkatja = new LOVDTO();
foreach (var item in objkatja.GetType().GetProperties())
{
validationTable.Columns.Add(item.Name);
}
//DATA ROW
var dataval = jabatanQry.GetQuery().Select(c => new LOVDTO {
Id = c.KategoriJabatan_PK, Name = c.Title
}).ToList();
DataRow dr2;
int startcell = 2, endcell = 2;
foreach (var item in dataval)
{
dr2 = validationTable.NewRow();
dr2["Id"] = item.Id;
dr2["Name"] = item.Name;
validationTable.Rows.Add(dr2);
endcell++;
}
var worksheet2 = workbook.AddWorksheet(validationTable);
worksheet.Column(3).SetDataValidation().List(worksheet2.Range("B" + startcell.ToString() + ":B" + endcell.ToString()), true);
//GENDER
using (var genderQuery = new GenderQuery())
{
//SETUP TABLE
DataTable validationTablegender = new DataTable();
validationTablegender.TableName = "Gender";
//SETUP COLUMN
GenderDTO objgender = new GenderDTO();
foreach (var item in objkatja.GetType().GetProperties())
{
validationTablegender.Columns.Add(item.Name);
}
var datagender = genderQuery.GetQuery().Select(c => new GenderDTO {
Gender_PK = c.Gender_PK, Name = c.Name
}).ToList();
DataRow drgender;
startcell = 2; endcell = 2;
foreach (var item in datagender)
{
drgender = validationTablegender.NewRow();
drgender["Id"] = item.Gender_PK;
drgender["Name"] = item.Name;
validationTablegender.Rows.Add(drgender);
endcell++;
}
var worksheetgender = workbook.AddWorksheet(validationTablegender);
worksheet.Column(10).SetDataValidation().List(worksheetgender.Range("B" + startcell.ToString() + ":B" + endcell.ToString()), true);
//RELIGION
using (var ReligionQuery = new ReligionQuery())
{
//SETUP TABLE
DataTable validationTableReligion = new DataTable();
validationTableReligion.TableName = "Religion";
//SETUP COLUMN
ReligionDTO objReligion = new ReligionDTO();
foreach (var item in objkatja.GetType().GetProperties())
{
validationTableReligion.Columns.Add(item.Name);
}
var dataReligion = ReligionQuery.GetQuery().Select(c => new ReligionDTO {
Religion_PK = c.Religion_PK, Name = c.Name
}).ToList();
DataRow drReligion;
startcell = 2; endcell = 2;
foreach (var item in dataReligion)
{
drReligion = validationTableReligion.NewRow();
drReligion["Id"] = item.Religion_PK;
drReligion["Name"] = item.Name;
validationTableReligion.Rows.Add(drReligion);
endcell++;
}
var worksheetReligion = workbook.AddWorksheet(validationTableReligion);
worksheet.Column(7).SetDataValidation().List(worksheetReligion.Range("B" + startcell.ToString() + ":B" + endcell.ToString()), true);
//MaritalStatus
using (var MaritalStatusQuery = new MaritalStatusQuery())
{
//SETUP TABLE
DataTable validationTableMaritalStatus = new DataTable();
validationTableMaritalStatus.TableName = "MaritalStatus";
//SETUP COLUMN
MaritalStatusDTO objMaritalStatus = new MaritalStatusDTO();
foreach (var item in objkatja.GetType().GetProperties())
{
validationTableMaritalStatus.Columns.Add(item.Name);
}
var dataMaritalStatus = MaritalStatusQuery.GetQuery().Select(c => new MaritalStatusDTO {
MaritalStatus_PK = c.MaritalStatus_PK, Name = c.Name
}).ToList();
DataRow drMaritalStatus;
startcell = 2; endcell = 2;
foreach (var item in dataMaritalStatus)
{
drMaritalStatus = validationTableMaritalStatus.NewRow();
drMaritalStatus["Id"] = item.MaritalStatus_PK;
drMaritalStatus["Name"] = item.Name;
validationTableMaritalStatus.Rows.Add(drMaritalStatus);
endcell++;
}
var worksheetMaritalStatus = workbook.AddWorksheet(validationTableMaritalStatus);
worksheet.Column(11).SetDataValidation().List(worksheetMaritalStatus.Range("B" + startcell.ToString() + ":B" + endcell.ToString()), true);
//CategoryContract
using (var CategoryContractQuery = new CategoryContractQuery())
{
//SETUP TABLE
DataTable validationTableCategoryContract = new DataTable();
validationTableCategoryContract.TableName = "CategoryContract";
//SETUP COLUMN
CategoryContractDTO objCategoryContract = new CategoryContractDTO();
foreach (var item in objkatja.GetType().GetProperties())
{
validationTableCategoryContract.Columns.Add(item.Name);
}
var dataCategoryContract = CategoryContractQuery.GetQuery().Select(c => new CategoryContractDTO {
CategoryContract_PK = c.CategoryContract_PK, Name = c.Name
}).ToList();
DataRow drCategoryContract;
startcell = 2; endcell = 2;
foreach (var item in dataCategoryContract)
{
drCategoryContract = validationTableCategoryContract.NewRow();
drCategoryContract["Id"] = item.CategoryContract_PK;
drCategoryContract["Name"] = item.Name;
validationTableCategoryContract.Rows.Add(drCategoryContract);
endcell++;
}
var worksheetCategoryContract = workbook.AddWorksheet(validationTableCategoryContract);
worksheet.Column(8).SetDataValidation().List(worksheetCategoryContract.Range("B" + startcell.ToString() + ":B" + endcell.ToString()), true);
//Project
using (var ProjectQuery2 = new ProjectQuery())
{
//SETUP TABLE
DataTable validationTableProject = new DataTable();
validationTableProject.TableName = "Project";
//SETUP COLUMN
ProjectDTO objProject = new ProjectDTO();
foreach (var item in objkatja.GetType().GetProperties())
{
validationTableProject.Columns.Add(item.Name);
}
var dataProject = ProjectQuery2.GetQuery();
DataRow drProject;
startcell = 2; endcell = 2;
foreach (var item in dataProject)
{
drProject = validationTableProject.NewRow();
drProject["Id"] = item.Project_PK;
drProject["Name"] = item.Project_PK + "-" + item.OperatorTitle + "-" + item.VendorTitle + "-" + item.DeliveryAreaTitle;
validationTableProject.Rows.Add(drProject);
endcell++;
}
var worksheetProject = workbook.AddWorksheet(validationTableProject);
worksheet.Column(9).SetDataValidation().List(worksheetProject.Range("B" + startcell.ToString() + ":B" + endcell.ToString()), true);
}
}
}
}
}
}
worksheet.Protect("kairosg1")
.SetFormatCells() // Cell Formatting
.SetInsertColumns() // Inserting Columns
.SetDeleteColumns() // Deleting Columns
.SetDeleteRows(); // Deleting Rows;
worksheet.Columns(1, 2).Style.Fill.BackgroundColor = XLColor.DarkGray;
worksheet.Columns(1, 2).Style.Font.FontColor = XLColor.Black;
worksheet.Columns(3, 20).Style.Protection.SetLocked(false);
}
MemoryStream memoryStream = GetStream(workbook);
var response = new HttpResponseMessage(HttpStatusCode.OK)
{
Content = new ByteArrayContent(memoryStream.ToArray())
};
response.Content.Headers.ContentType = new MediaTypeHeaderValue
("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.Content.Headers.ContentDisposition =
new ContentDispositionHeaderValue("attachment")
{
FileName = $"{_fileName}_{DateTime.Now.ToString("yyyyMMddHHmmss")}.xlsx"
};
return(response);
}
