/// <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"
                });
            }
        }
Beispiel #3
0
 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());
     }
 }
Beispiel #4
0
        /// <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);
        }
Beispiel #6
0
        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));
        }
Beispiel #7
0
        /// <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"));
        }
Beispiel #9
0
        /// <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));
        }
Beispiel #12
0
        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)));
        }
Beispiel #16
0
 public Object SearchUsers(UserSearchParam u)
 {
     return(Repository.SearchUsers(u));
 }
Beispiel #17
0
 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);
        }
Beispiel #20
0
        /// <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);
     }
 }