/// <summary>
        /// 事業コード一覧を取得する。
        /// </summary>
        /// <param name="searchText"></param>
        /// <returns>合計件数と一覧の名前付きタプル型を返す。</returns>
        public async Task <(int TotalCount, List <BusinessCodeSearchResult> List)> GetList(int?selectedAccountingCode, string searchDebitBusinessCode, string searchDebitBusinessName, bool showDeleted,
                                                                                           IEnumerable <SortParam> sortParams, IPagingParam pagingParam)
        {
            DynamicParameters parameter = new DynamicParameters(pagingParam);
            string            whereSql1 = CreateSearchTextForBusinessCode(searchDebitBusinessCode, searchDebitBusinessName,
                                                                          selectedAccountingCode, showDeleted, parameter);

            //string whereSql2 = CreateSearchTextForBusinessCode(selectedAccountingCode);
            sortParams = ISortParamsExtensions.CheckSortParams(sortParams, SortColumns); //SQLインジェクションを防ぐ為、画面から送られてきたソート用パラメータが想定した文字列かチェックする。

            string dispOrder = CreateSortOrderStr(sortParams);
            string orderText = string.IsNullOrEmpty(dispOrder) ? " ORDER BY AccountingCodeNo" : dispOrder;

            string sql =
                $@"SELECT * FROM
(
     SELECT 
        ({CreateGeneralNameSubQuery(ConstKubun.AccountingCode, "main.AccountingCodeNo")}) AS AccountingCode
       ,({CreateRemarksSubQuery(ConstKubun.AccountingCode, "main.AccountingCodeNo")}) AS AccountingCodeName
       ,main.BusinessCodeNo
       ,main.AccountingCodeNo
       ,main.DebitBusinessCode
	   ,main.DebitBusinessName
	   ,main.DebitAccountingItemCode
	   ,main.DebitAccountingAssistItemCode
	   ,main.DebitTaxCode
	   ,main.CreditBusinessCode
       ,main.CreditAccountingItemCode
       ,main.CreditAccountingAssistItemCode
       ,main.CreditTaxCode
       ,main.PaymentFlag
	   ,main.DeleteFlag
       ,COUNT(*) OVER() AS TotalCount
  FROM M_BusinessCode main
  WHERE 
  {whereSql1}
)as base
{orderText}
OFFSET @StartRow ROWS FETCH NEXT @RowCount ROWS ONLY
";
            int totalCount = 0;
            //SELECT結果とTotalCountを取得。
            var list = (await Connection.QueryAsync <BusinessCodeSearchResult, int, BusinessCodeSearchResult>(sql,
                                                                                                              (r, _totalCount) =>
            {
                totalCount = _totalCount;
                return(r);
            }, parameter, splitOn: "TotalCount"))
                       .ToList();

            var result = (TotalCount : totalCount, List : list);

            return(result);
        }
Exemple #2
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);
        }