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