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); }