/// <summary> /// 検索処理を行う。 /// </summary> /// <param name="TotalCount"></param> /// <param name="List"></param> /// <returns></returns> public async Task <(int TotalCount, List <UserSearchResult> List)> GetList(UserSearchParam model) { model.PagingParam.SetDefaultPagingParamIfNull(); var result = await repository.GetList(model); return(result); }
public Object SearchUsers(UserSearchParam pSearchParam) { try { var result = DataService.SearchUsers(pSearchParam); return(new { data = new { Count = result.ResultCount, UserList = result.Result }, success = true, error = "" }); } catch (Exception ex) { return(new { success = false, error = "Some Error has occurred" }); } }
public ResponseResult SearchUsers(UserSearchParam pSearchParam) { try { var result = DataService.SearchUsers(pSearchParam); return(ResponseResult.GetSuccessObject(new { Count = result.ResultCount, UserList = result.Result })); //return (new //{ // data = new // { // Count = result.ResultCount, // UserList = result.Result // }, // success = true, // error = "" //}); } catch (Exception ex) { CustomUtility.HandleException(ex); return(ResponseResult.GetErrorObject()); } }
/// <summary> /// 获取用户列表 /// </summary> /// <param name="entity">用户搜索实体</param> /// <returns>PageDataModel<UserEntity></returns> public Task <BasePagedListModel <UserEntity> > GetListAsync(UserSearchParam param) { if (param == null) { throw new ArgumentNullException(nameof(param)); } return(userRepository.GetListAsync(param)); }
/// <summary> /// 詳細情報の取得処理を行う。 /// </summary> /// <param name="model"></param> /// <returns></returns> public async Task <UserSearchResult> GetDetail(UserSearchParam model) { model.PagingParam = new PagingParam(); model.PagingParam.SetDefaultPagingParamIfNull(); var result = (await repository.GetList(model)).List.FirstOrDefault(); return(result); }
public async Task <IActionResult> GetGridJson(UserSearchParam userSearchEntity) { BasePagedListModel <UserEntity> pageDataModel = await userService.GetListAsync(userSearchEntity); var data = new { total = pageDataModel.Total, rows = pageDataModel.Data }; return(Json(data)); }
/// <summary> /// 获取用户列表 /// </summary> /// <param name="param">用户搜索实体</param> /// <returns>PageDataModel<UserEntity></returns> public async Task <BasePagedListModel <UserEntity> > GetListAsync(UserSearchParam param) { BasePagedListModel <UserEntity> list = new BasePagedListModel <UserEntity>(); try { #region 条件与排序 StringBuilder condition = new StringBuilder("1=1"); if (param.Name.IsNotNullOrEmpty()) { condition.AppendFormat(" AND U_NAME='{0}'", param.Name.FilterSql()); } if (param.RealName.IsNotNullOrEmpty()) { condition.AppendFormat(" AND U_REALNAME='{0}'", param.RealName.FilterSql()); } if (param.Mobile.IsNotNullOrEmpty()) { condition.AppendFormat(" AND U_MOBILE='{0}'", param.Mobile.FilterSql()); } #endregion PageCriteriaModel criteria = new PageCriteriaModel(); criteria.Condition = condition.ToString(); criteria.PageIndex = param.PageIndex; criteria.Fields = "*"; criteria.PageSize = param.PageSize; criteria.TableName = "S_USER"; criteria.PrimaryKey = "U_ID"; if (param.SortName.IsNotNullOrEmpty() && param.SortOrder.IsNotNullOrEmpty()) { criteria.Sort = $"{param.SortName.FilterSql()} {param.SortOrder.FilterSql()}"; } list = await pageListRepository.GetPageDataAsync <UserEntity>(connectionProvider, criteria); } catch (ArgumentNullException ex) { logger.LogError("调用方法GetListAsync(UserSearchParam param)发生ArgumentNullException,异常信息:{0}", ex); } catch (SqlException ex) { logger.LogError("调用方法GetListAsync(UserSearchParam param)发生SqlException,异常信息:{0}", ex); } catch (Exception ex) { logger.LogError("调用方法GetListAsync(UserSearchParam param)发生Exception,异常信息:{0}", ex); } return(list); }
public async Task ユーザーマスタ_検索_条件全指定() { var searchModel = new UserSearchParam(); searchModel.SearchUserName = "******"; searchModel.SearchLoginId = "user4"; searchModel.SearchRole = 4; searchModel.SearchDivisionNo = 5; searchModel.ShowDelete = true; searchModel.SortParams = new List <SortParam>() { new SortParam("", "", "asc") }; searchModel.PagingParam = new PagingParam() { CurrentPage = 0, RowCount = 3 }; var result = await service.GetList(searchModel); var totalCount = result.TotalCount; var list = result.List; // 条件に合致する件が検索される Assert.AreEqual(1, totalCount); Assert.AreEqual(1, list.Count); var user = list[0]; Assert.AreEqual(4, user.UserNo); Assert.AreEqual("ユーザー4", user.UserName); Assert.AreEqual("user4", user.LoginId); Assert.AreEqual(null, user.Password); Assert.AreEqual(4, user.Role); Assert.AreEqual("管理者", user.RoleName); Assert.AreEqual(1, user.DeleteFlag); Assert.AreEqual(true, user.IsDeleted); var divisionList = user.DivisionNoList; Assert.AreEqual(1, divisionList.Count); Assert.AreEqual(true, divisionList.Any(d => d == 5)); var divisionNameList = user.DivisionNameList; Assert.AreEqual(1, divisionNameList.Count); Assert.AreEqual(true, divisionNameList.Any(d => d == "課5")); }
/// <summary> /// ユーザー検索のWhere句を作成する。 /// </summary> /// <param name="searchText"></param> /// <param name="paramater"></param> /// <returns></returns> private string CreateSearchText(UserSearchParam model, DynamicParameters paramater) { List <string> whereSql = new List <string>(); //ユーザ名等 if (!String.IsNullOrEmpty(model.SearchUserName)) { whereSql.Add(base.CreateSearchText(CommonUtil.SplitSearchText(model.SearchUserName), new String[] { "UserName" }, paramater, "main")); } //ユーザーID if (!String.IsNullOrEmpty(model.SearchLoginId)) { whereSql.Add(base.CreateSearchText(CommonUtil.SplitSearchText(model.SearchLoginId), new String[] { "LoginId" }, paramater, "main")); } //担当 if (model.SearchDivisionNo != null) { paramater.Add("@DivisionNo", model.SearchDivisionNo, dbType: DbType.Int32); whereSql.Add(@" EXISTS (SELECT * FROM M_UserDivision WHERE UserNo = main.UserNo AND DivisionNo = @DivisionNo) "); } //役割 if (model.SearchRole != null) { paramater.Add("@Role", model.SearchRole, dbType: DbType.Int32); whereSql.Add(@" main.Role = @Role "); } //主キー指定する場合 if (model.SearchUserNo != null) { paramater.Add("@UserNo", model.SearchUserNo); whereSql.Add(@" main.UserNo = @UserNo "); } //直接主キーが指定されている場合はDeleteFlagはチェックしない。 if (model.SearchUserNo == null) { //削除フラグ paramater.Add("@DeleteFlag", model.ShowDelete.AsFlag(), dbType: DbType.Int32); whereSql.Add(@" main.DeleteFlag = @DeleteFlag "); } return(whereSql.IsNullOrEmpty() ? "" : string.Join(" AND ", whereSql)); }
public async Task <string> QueryPagedAsync([FromQuery] UserSearchParam param) { var filter = param.SearchLambda <TUser, UserSearchParam>(); var result = await _iTUserRepository.QueryPagedAsync(param.Page, param.Limit, c => c.CreateTime, filter); var pageData = new LayuiTableDto <TUser> { Code = "0", Msg = "获取数据成功", Count = result.TotalResults, Data = result.Items.ToList() }; var json = JsonConvert.SerializeObject(pageData); return(json); }
public IQueryable <User> LoadSearchedUsers(UserSearchParam param, short delFlag) { var temp = CurrentDbSession.UserDal.LoadEntities(u => u.DelFlag == delFlag); if (!string.IsNullOrEmpty(param.SearchEmail)) { temp = temp.Where(u => u.Email.Contains(param.SearchEmail)); } if (!string.IsNullOrEmpty(param.SearchName)) { temp = temp.Where(u => u.UserName.Contains(param.SearchName)); } param.Total = temp.Count(); return(temp.OrderBy(u => u.Id) .Skip(param.PageSize * (param.PageIndex - 1)) .Take(param.PageSize)); }
public UserSearchResult SearchUsers(UserSearchParam entity) { using (var ctx = new PRMDataContext()) { UserSearchResult result = new Entities.DBEntities.UserSearchResult(); string query = "execute sec.SearchUsers @0, @1, @2,@3"; var cmd = ctx.Database.Connection.CreateCommand(); cmd.CommandText = query; cmd.Parameters.Add(new SqlParameter { ParameterName = "@0", Value = entity.TextToSearch }); cmd.Parameters.Add(new SqlParameter { ParameterName = "@1", Value = entity.IsActive }); cmd.Parameters.Add(new SqlParameter { ParameterName = "@2", Value = entity.PageSize }); cmd.Parameters.Add(new SqlParameter { ParameterName = "@3", Value = entity.PageIndex }); ctx.Database.Connection.Open(); var reader = cmd.ExecuteReader(); result.ResultCount = ((IObjectContextAdapter)ctx) .ObjectContext .Translate <int>(reader).FirstOrDefault(); reader.NextResult(); result.Result = ((IObjectContextAdapter)ctx) .ObjectContext .Translate <UserSearchResultObj>(reader).ToList(); return(result); } }
public object this[UserSearchParam p] { get { return m_parameters.ContainsKey(p) ? m_parameters[p] : null; } set { SetParameter(p, value); } }
public async Task ユーザーマスタ_検索_条件指定なし() { var searchModel = new UserSearchParam(); searchModel.SearchUserName = ""; searchModel.SearchLoginId = ""; searchModel.SearchRole = null; searchModel.SearchDivisionNo = null; searchModel.ShowDelete = false; searchModel.SortParams = new List <SortParam>() { new SortParam("UserName", "", "desc") }; searchModel.PagingParam = new PagingParam() { CurrentPage = 0, RowCount = 3 }; var result = await service.GetList(searchModel); var totalCount = result.TotalCount; var list = result.List; // 全4件の内、DeleteFlagが1のデータを除いて3件が検索される Assert.AreEqual(4, totalCount); Assert.AreEqual(3, list.Count); // データの中身確認 //list = list.OrderBy(m => m.UserNo).ToList(); var user = list[0]; Assert.AreEqual(6, user.UserNo); Assert.AreEqual("ユーザー6", user.UserName); Assert.AreEqual("user6", user.LoginId); Assert.AreEqual(null, user.Password); Assert.AreEqual(2, user.Role); Assert.AreEqual("代理", user.RoleName); Assert.AreEqual(0, user.DeleteFlag); Assert.AreEqual(false, user.IsDeleted); //削除フラグが立った課を除いたものが取得される。 var divisionList = user.DivisionNoList; Assert.AreEqual(2, divisionList.Count); Assert.AreEqual(true, divisionList.Any(d => d == 3)); Assert.AreEqual(true, divisionList.Any(d => d == 5)); var divisionNameList = user.DivisionNameList; Assert.AreEqual(2, divisionNameList.Count); Assert.AreEqual(true, divisionNameList.Any(d => d == "課3")); Assert.AreEqual(true, divisionNameList.Any(d => d == "課5")); // データの順番確認 (UserName DESC) Assert.AreEqual(6, list[0].UserNo); Assert.AreEqual(5, list[1].UserNo); Assert.AreEqual(2, list[2].UserNo); // 削除済みが含まれていない Assert.IsTrue(list.Where(p => p.IsDeleted).ToList().Count == 0); }
public ActionResult Get(UserSearchParam param) { var users = _service.Fetch(param); return(Json(ResultUtil.Success(users))); }
public Object SearchUsers(UserSearchParam u) { return(Repository.SearchUsers(u)); }
public ResponseResult SearchUsers(UserSearchParam u) { return(Repository.SearchUsers(u)); }
public async Task ユーザーマスタ_更新_検索_更新結果確認() { var registerModel = new UserRegisterModel(); registerModel.UserNo = 3; registerModel.UserName = "******"; registerModel.LoginId = "tarou3"; registerModel.Role = 3; registerModel.DivisionNoList = new List <int>() { 1000, 1001 }; registerModel.PasswordStr = null; //パスワード変更なし registerModel.IsDeleted = false; registerModel.CreateBy = registerModel.UpdateBy = 1; var updateResult = await service.Update(registerModel); var isSuccess = updateResult.Result; var message = updateResult.Message; Assert.AreEqual(true, isSuccess); Assert.AreEqual("", message); // DB登録結果確認(更新後データの存在確認) var searchModel = new UserSearchParam(); searchModel.SearchUserName = "******"; searchModel.SearchLoginId = ""; searchModel.SearchRole = 3; searchModel.SearchDivisionNo = null; searchModel.ShowDelete = false; searchModel.SortParams = new List <SortParam>() { new SortParam("", "", "asc") }; searchModel.PagingParam = new PagingParam() { CurrentPage = 0, RowCount = 20 }; var result = await service.GetList(searchModel); var totalCount = result.TotalCount; var list = result.List; Assert.AreEqual(1, totalCount); Assert.AreEqual(1, list.Count); // データの中身確認 var user = list[0]; Assert.AreEqual(registerModel.UserNo, user.UserNo); Assert.AreEqual(registerModel.UserName, user.UserName); Assert.AreEqual(registerModel.LoginId, user.LoginId); Assert.AreEqual(registerModel.Role, user.Role); var divisionNoList = user.DivisionNoList; Assert.AreEqual(2, divisionNoList.Count); Assert.AreEqual(true, divisionNoList.Any(d => d == 1000)); Assert.AreEqual(true, divisionNoList.Any(d => d == 1001)); Assert.AreEqual(registerModel.IsDeleted.AsFlag(), user.DeleteFlag); Assert.AreEqual(registerModel.IsDeleted, user.IsDeleted); //ログイン出来るか確認 var loginInfo = await loginService.GetUserByLoginInfo(registerModel.LoginId, "password"); Assert.IsNotNull(loginInfo); //パスワード変更あり registerModel.UserNo = 3; registerModel.PasswordStr = "password123"; updateResult = await service.Update(registerModel); isSuccess = updateResult.Result; message = updateResult.Message; Assert.AreEqual(true, isSuccess); Assert.AreEqual("", message); //ログイン出来るか確認 loginInfo = await loginService.GetUserByLoginInfo(registerModel.LoginId, registerModel.PasswordStr); Assert.IsNotNull(loginInfo); }
public async Task ユーザーマスタ_新規登録_検索_登録結果確認() { // ユーザーを新規登録 var registerModel = new UserRegisterModel(); registerModel.UserNo = null; registerModel.UserName = "******"; registerModel.LoginId = "tarou1"; registerModel.Role = 1; registerModel.DivisionNoList = new List <int>() { 1, 2 }; registerModel.PasswordStr = "password2"; registerModel.IsDeleted = false; registerModel.CreateBy = registerModel.UpdateBy = 1; var insertResult = await service.Create(registerModel); var isSuccess = insertResult.Result; var message = insertResult.Message; // DB登録結果確認 Assert.IsTrue(isSuccess); Assert.AreEqual("", message); // 登録内容で再取得し、取得できることを確認 var searchModel = new UserSearchParam(); searchModel.SearchUserName = ""; searchModel.SearchLoginId = "tarou"; searchModel.SearchRole = 1; searchModel.SearchDivisionNo = 2; searchModel.ShowDelete = false; searchModel.SortParams = new List <SortParam>() { new SortParam("", "", "asc") }; searchModel.PagingParam = new PagingParam() { CurrentPage = 0, RowCount = 20 }; var result = await service.GetList(searchModel); var totalCount = result.TotalCount; var list = result.List; Assert.AreEqual(1, totalCount); Assert.AreEqual(1, list.Count); // データの中身確認 var user = list[0]; Assert.AreEqual(7, user.UserNo); Assert.AreEqual(registerModel.UserName, user.UserName); Assert.AreEqual(registerModel.LoginId, user.LoginId); Assert.AreEqual(registerModel.Role, user.Role); var divisionNoList = user.DivisionNoList; Assert.AreEqual(2, divisionNoList.Count); Assert.AreEqual(true, divisionNoList.Any(d => d == 1)); Assert.AreEqual(true, divisionNoList.Any(d => d == 2)); Assert.AreEqual(registerModel.IsDeleted.AsFlag(), user.DeleteFlag); Assert.AreEqual(registerModel.IsDeleted, user.IsDeleted); //ログイン出来るか確認 var loginInfo = await loginService.GetUserByLoginInfo(registerModel.LoginId, registerModel.PasswordStr); Assert.IsNotNull(loginInfo); }
/// <summary> /// ユーザー一覧を取得する。 /// </summary> /// <param name="searchText"></param> /// <returns>合計件数と一覧の名前付きタプル型を返す。</returns> public async Task <(int TotalCount, List <UserSearchResult> List)> GetList(UserSearchParam model) { DynamicParameters parameter = new DynamicParameters(model.PagingParam); string whereSql = CreateSearchText(model, parameter); var sortParams = ISortParamsExtensions.CheckSortParams(model.SortParams, SortColumns); //SQLインジェクションを防ぐ為、画面から送られてきたソート用パラメータが想定した文字列かチェックする。 string dispOrder = CreateSortOrderStr(sortParams); string orderText = string.IsNullOrEmpty(dispOrder) ? " ORDER BY UserNo" : dispOrder; //SQL内にコメント書くとDockerではSQLが変になる(多分改行のせい) string sql = $@"SELECT * FROM ( SELECT main.UserNo ,main.UserName ,main.LoginId ,main.Role ,({CreateGeneralNameSubQuery(ConstKubun.Role, "main.Role")}) AS RoleName ,main.DeleteFlag ,COUNT(*) OVER() AS TotalCount ,( SELECT CONVERT(varchar, udiv.DivisionNo) + '{EachFieldDelimiter}' + ISNULL(div.DivisionName, '') + '{EachRowDelimiter}' FROM M_UserDivision udiv INNER JOIN M_Division div ON udiv.DivisionNo = div.DivisionNo AND div.DeleteFlag = {ConstFlag.FalseValue} WHERE udiv.UserNo = main.UserNo FOR XML PATH('') ) AS DivisionNoStr FROM M_User main WHERE {whereSql} )as base {orderText} OFFSET @StartRow ROWS FETCH NEXT @RowCount ROWS ONLY "; int totalCount = 0; //SELECT結果とTotalCountを取得。 var list = (await Connection.QueryAsync <UserSearchResult, int, string, UserSearchResult>(sql, (user, _totalCount, divisionNoStr) => { totalCount = _totalCount; user.DivisionNoList = new List <int>(); var _ = GetResultFromSqlXmlText(divisionNoStr, (fields) => { if (int.TryParse(fields.ElementAtOrDefault(0, null), out var d)) { user.DivisionNoList.Add(d); user.DivisionNameList.Add(fields.ElementAtOrDefault(1, "")); } return((int?)null); //適当に返す。 }); return(user); }, parameter, splitOn: "TotalCount,DivisionNoStr")) //TotalCountはSelect句の末尾に書くこと。 .ToList(); var result = (TotalCount : totalCount, List : list); return(result); }
public void SetParameter(UserSearchParam p, object value) { if (m_parameters.ContainsKey(p)) { m_parameters[p] = value; } else { m_parameters.Add(p, value); } }