/// <summary>
/// SELECT開始行を取得する。
/// </summary>
/// <param name="cond"></param>
/// <returns></returns>
public static int GetStartRow(this IPagingParam cond)
{
//int result = (cond.CurrentPage.Value - 1) * cond.RowCount.Value; //検索開始位置;
//1ページ目は0で来る想定。
int result = (cond.CurrentPage.Value) * cond.RowCount.Value; //検索開始位置;
return(result);
}
/// <summary>
/// ページ内の表示終了位置を取得する。
/// </summary>
/// <param name="cond"></param>
/// <param name="startCount"></param>
/// <returns></returns>
public static int CulcEndCount(this IPagingParam cond, int startCount)
{
int?endCount = startCount + cond.RowCount - 1;
endCount = endCount > cond.TotalCount ? cond.TotalCount : endCount;
return(endCount ?? 1);
}
/// <summary>
/// 合計ページ数を算出する。
/// </summary>
/// <param name="cond"></param>
/// <returns></returns>
public static int CulcTotalPageCount(this IPagingParam cond)
{
int totalPageCount = cond.TotalCount / cond.RowCount.Value; //総商品数を１ページの表示数で割る。
if ((cond.TotalCount % cond.RowCount.Value) > 0)
{
totalPageCount++; //余りあったらページ数を1増やす。
}
return(totalPageCount);
}
/// <summary>
/// ページング用の値を設定する。
/// 既に値が設定されている場合は何もしない。
/// </summary>
/// <param name="cond"></param>
/// <param name="defaultCurrentPage"></param>
/// <param name="defaultRowCount"></param>
public static void SetDefaultPagingParamIfNull(this IPagingParam cond,
int defaultCurrentPage = 0, int defaultRowCount = DefaultPageCount)
{
cond.CurrentPage = cond.CurrentPage == null || cond.CurrentPage == 0
? defaultCurrentPage : cond.CurrentPage;
cond.RowCount = cond.RowCount ?? defaultRowCount;
cond.StartRow = GetStartRow(cond);
}
/// <summary>
/// SELECT開始～終了行の表示用の文言を取得する。
/// </summary>
/// <param name="cond"></param>
/// <param name="format"></param>
/// <returns></returns>
public static string GetStartEndCountText(this IPagingParam cond, string format = "{0}～{1}件まで表示")
{
if (cond == null)
{
return("");
}
int start = CulcStartCount(cond);
int end = CulcEndCount(cond, start);
string text = string.Format(format, start, end);
return(text);
}
/// <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="TotalCount"></param>
/// <param name="List"></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)
{
pagingParam.SetDefaultPagingParamIfNull();
var result = await repository.GetList(SelectedAccountingCode, SearchDebitBusinessCode, SearchDebitBusinessName, showDeleted, sortParams, pagingParam);
return(result);
}
/// <summary>
/// ページ内の表示開始位置を取得する。
/// </summary>
/// <param name="cond"></param>
/// <returns></returns>
public static int CulcStartCount(this IPagingParam cond)
{
int?startCount = (cond.RowCount * (cond.CurrentPage)) + 1;
return(startCount ?? 1);
}
