namespace AccountsDAL.Query.AccountReports
{
    // GB4 GetAccountBooks (AccountsReportsDAL.cs) consolidated migration — Ledger/Statement of Account.
    // Reuses AccountReportsQB's child-detail queries (bill allocation/cost center/instrument/addon/TDS)
    // as-is; adds the running-balance-per-account calculation that distinguishes a ledger from a flat
    // register, a multi-axis currency basis selector, and an account-count pre-check for the
    // unbounded "general ledger" case.
    //
    // IMPORTANT — no leading CTE (`;WITH ...`) in any query passed to IQueryExecutor.QueryPagedAsync:
    // that method has only one overload in this codebase (no separate countSql parameter, despite the
    // CLAUDE.md example showing one) and auto-derives the row count by wrapping the caller's SQL as
    // `SELECT COUNT(*) FROM (<sql>) AS Total`. A query starting with a CTE cannot be nested inside a
    // derived table that way — SQL Server throws "Incorrect syntax near ';'"/"near ')'". Found live via
    // HTTP testing (0 rows returned, HTTP 200, with the real syntax error buried in the response body's
    // GeneralErrors — not a thrown exception) after an initial CTE-based draft of these queries. Fixed by
    // replacing the opening-balance CTE with a correlated OUTER APPLY per row (detail) / scalar subquery
    // per group (summary) — both are valid inside a derived table, so QueryPagedAsync's wrap works.
    //
    // {DynamicFilter} (main WHERE, aliases V/VD/ACC/BTT) and {OpeningDynamicFilter} (inside the
    // opening-balance OUTER APPLY/subquery, aliases V2/VD2/ACC2/BTT2) are TWO DISTINCT placeholders,
    // not the same text reused — a first draft used one shared placeholder for both, and
    // AccountReportFilterBuilder.Build()'s IncludeInformational fragment hardcodes the alias `BTT`,
    // which — when substituted verbatim inside the opening-balance subquery — silently correlated to
    // the OUTER query's grouped `BTT` row instead of the subquery's own local `BTT2`, causing SQL
    // Server to reject the query ("Column ... invalid in the select list because it is not contained in
    // either an aggregate function or the GROUP BY clause"), caught live via HTTP testing. Fixed: the
    // DAL builds a second copy of the SAME filter text with aliases remapped (VD->VD2, V->V2, ACC->ACC2,
    // BTT->BTT2, in that length-descending order to avoid one substring clobbering another) for
    // {OpeningDynamicFilter}. BuildDetailOnly()'s BillType/dimension filters are intentionally excluded
    // from the opening-balance calculation entirely (in either alias form) — those are per-voucher
    // narrative filters, not account-scoping ones, so they don't change which accounts' opening
    // balances matter.
    public static class AccountLedgerReportsQB
    {
        // Selects the amount column matching the caller's requested currency basis. 0 = OU/base
        // (VOUCHERAMOUNT), 1 = Transaction (VOUCHERAMOUNTFC), 2 = Customer/party (VOUCHERAMOUNTAC,
        // confirmed live-populated), 3 = Group (VOUCHERAMOUNTGC, confirmed present but unpopulated on
        // GB5DEMO today — surfaced as-is, not assumed meaningful).
        private const string BASE_AMOUNT_EXPR = @"
            CASE @CurrencyBasis
                WHEN 1 THEN VD.VOUCHERAMOUNTFC
                WHEN 2 THEN VD.VOUCHERAMOUNTAC
                WHEN 3 THEN VD.VOUCHERAMOUNTGC
                ELSE VD.VOUCHERAMOUNT
            END";

        // Same expression, aliased to VD2 for use inside the opening-balance OUTER APPLY/subquery
        // (which self-joins TVOUCHERDETAIL under a different alias to look back before PeriodFromDate).
        private const string BASE_AMOUNT_EXPR_VD2 = @"
            CASE @CurrencyBasis
                WHEN 1 THEN VD2.VOUCHERAMOUNTFC
                WHEN 2 THEN VD2.VOUCHERAMOUNTAC
                WHEN 3 THEN VD2.VOUCHERAMOUNTGC
                ELSE VD2.VOUCHERAMOUNT
            END";

        // Report-currency conversion (VEXCHANGEDATEWISE) is only wired up for the two bases that carry
        // their own explicit currency id (Transaction/CURRENCYID, Customer/ACCURRENCYID) — OU/base and
        // Group would need the OU's own base-currency id (MORGANIZATIONUNIT), not verified this session,
        // so those two bases are left unconverted rather than guessing at an unconfirmed join.
        private const string FROM_CURRENCY_EXPR = @"
            CASE @CurrencyBasis
                WHEN 1 THEN VD.CURRENCYID
                WHEN 2 THEN VD.ACCURRENCYID
                ELSE NULL
            END";

        // Correlated per-row opening balance — sums this account's transactions strictly before
        // PeriodFromDate. Used via OUTER APPLY in the detail/stream queries (correlated to VD.VOUCHERACCOUNTID).
        private const string OPENING_BALANCE_APPLY = @"
OUTER APPLY (
    SELECT SUM(CASE WHEN VD2.DETAILTYPE = 0 THEN " + BASE_AMOUNT_EXPR_VD2 + @" ELSE -(" + BASE_AMOUNT_EXPR_VD2 + @") END) AS OpeningBalance
    FROM TVOUCHER V2
    INNER JOIN TVOUCHERDETAIL VD2 ON V2.VOUCHERID = VD2.VOUCHERID
    INNER JOIN MACCOUNT ACC2 ON VD2.VOUCHERACCOUNTID = ACC2.ACCOUNTID
    INNER JOIN MBIZTRANSACTIONTYPE BTT2 ON V2.BIZTRANSACTIONTYPEID = BTT2.BIZTRANSACTIONTYPEID
    WHERE VD2.VOUCHERACCOUNTID = VD.VOUCHERACCOUNTID
      AND V2.VOUCHERDATE < @PeriodFromDate
      AND V2.OUID IN (
            SELECT UAR.OUID FROM MUSERACCESSRIGHTS UAR WHERE UAR.USERID = @UserId AND UAR.OUID <> -1
            UNION
            SELECT OGD.OUID FROM MUSERACCESSRIGHTS UAR2
            INNER JOIN MORGANIZATIONGROUPDETAIL OGD ON OGD.ORGANIZATIONGROUPID = UAR2.OUGROUPID
            WHERE UAR2.USERID = @UserId AND UAR2.OUGROUPID <> -1
      )
      {OpeningDynamicFilter}
) OC";

        public const string GET_ACCOUNT_LEDGER_DETAIL_REPORT = @"
SELECT
    V.VOUCHERID AS VoucherId,
    V.STATUS AS Status,
    V.VOUCHERDATE AS VoucherDate,
    V.VOUCHERAMOUNT AS VoucherAmount,
    V.VOUCHERFCAMOUNT AS VoucherFCAmount,
    V.VOUCHERNUMBER AS VoucherNumber,
    V.VOUCHERREFERENCENUMBER AS VoucherReferenceNumber,
    V.VOUCHERREFERENCEDATE AS VoucherReferenceDate,
    V.VOUCHERNARRATION AS VoucherNarration,
    V.ISREVERSE AS IsReverse,
    V.VALIDTILLDATE AS ValidTillDate,
    V.BIZTRANSACTIONTYPEID AS BizTransactionTypeId,
    BTT.BIZTRANSACTIONTYPECODE AS BizTransactionTypeCode,
    BTT.BIZTRANSACTIONTYPENAME AS BizTransactionTypeName,
    VD.VOUCHERDETAILID AS VoucherDetailId,
    VD.SLNO AS Slno,
    VD.VOUCHERAMOUNTFC AS VoucherDetailVoucherAmountFC,
    VD.VOUCHERAMOUNT AS VoucherDetailVoucherAmount,
    VD.CURRENCYID AS CurrencyId,
    CUR.CURRENCYCODE AS CurrencyCode,
    CUR.CURRENCYNAME AS CurrencyName,
    VD.CURRENCYCONVERSION AS ConversionFactor,
    CASE WHEN VD.DETAILTYPE = 0 THEN BA.BaseAmount ELSE 0 END AS Debit,
    CASE WHEN VD.DETAILTYPE = 1 THEN BA.BaseAmount ELSE 0 END AS Credit,
    VD.VOUCHERLINENARRATION AS VoucherLineNarration,
    VD.DETAILTYPE AS DetailType,
    VD.VOUCHERACCOUNTID AS VoucherAccountId,
    ACC.ACCOUNTCODE AS VoucherAccountCode,
    ACC.ACCOUNTNAME AS VoucherAccountName,
    CASE V.STATUS
        WHEN 0 THEN 'Pending' WHEN 1 THEN 'Active' WHEN 2 THEN 'Deleted'
        WHEN 3 THEN 'Amended/Rejected' WHEN 4 THEN 'Inactive' WHEN 5 THEN 'Archived'
        ELSE '' END AS VoucherStatus,
    ISNULL(OC.OpeningBalance, 0) AS AccountOpeningBalance,
    ISNULL(OC.OpeningBalance, 0)
        + SUM(CASE WHEN VD.DETAILTYPE = 0 THEN BA.BaseAmount ELSE -BA.BaseAmount END)
          OVER (PARTITION BY VD.VOUCHERACCOUNTID ORDER BY V.VOUCHERDATE, VD.SLNO
                ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningBalance,
    @CurrencyBasis AS CurrencyBasis,
    @ReportCurrencyId AS ReportCurrencyId,
    RC.CONVERSIONRATE AS ReportConversionRate,
    CASE WHEN @ReportCurrencyId <> 0 AND RC.CONVERSIONRATE IS NOT NULL
         THEN BA.BaseAmount * RC.CONVERSIONRATE ELSE NULL END AS ReportConvertedAmount,
    0 AS TotalDebit,
    0 AS TotalCredit
FROM TVOUCHER V
INNER JOIN TVOUCHERDETAIL VD ON V.VOUCHERID = VD.VOUCHERID
INNER JOIN MACCOUNT ACC ON VD.VOUCHERACCOUNTID = ACC.ACCOUNTID
INNER JOIN MBIZTRANSACTIONTYPE BTT ON V.BIZTRANSACTIONTYPEID = BTT.BIZTRANSACTIONTYPEID
LEFT JOIN MCURRENCY CUR ON VD.CURRENCYID = CUR.CURRENCYID
CROSS APPLY (VALUES (" + BASE_AMOUNT_EXPR + @")) AS BA(BaseAmount)
CROSS APPLY (VALUES (" + FROM_CURRENCY_EXPR + @")) AS FC(FromCurrencyId)
" + OPENING_BALANCE_APPLY + @"
OUTER APPLY (
    SELECT TOP 1 EX.CONVERSIONRATE
    FROM VEXCHANGEDATEWISE EX
    WHERE EX.D <= V.VOUCHERDATE AND EX.FROMCURRENCYID = FC.FromCurrencyId AND EX.TOCURRENCYID = @ReportCurrencyId
    ORDER BY EX.D DESC
) RC
WHERE V.VOUCHERDATE >= @PeriodFromDate AND V.VOUCHERDATE <= @PeriodToDate
  AND V.OUID IN (
        SELECT UAR.OUID FROM MUSERACCESSRIGHTS UAR WHERE UAR.USERID = @UserId AND UAR.OUID <> -1
        UNION
        SELECT OGD.OUID FROM MUSERACCESSRIGHTS UAR2
        INNER JOIN MORGANIZATIONGROUPDETAIL OGD ON OGD.ORGANIZATIONGROUPID = UAR2.OUGROUPID
        WHERE UAR2.USERID = @UserId AND UAR2.OUGROUPID <> -1
  )
  {DynamicFilter}
ORDER BY {OrderBy}
OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";

        public const string GET_ACCOUNT_LEDGER_DETAIL_TOTALS = @"
SELECT
    SUM(CASE WHEN VD.DETAILTYPE = 0 THEN " + BASE_AMOUNT_EXPR + @" ELSE 0 END) AS TotalDebit,
    SUM(CASE WHEN VD.DETAILTYPE = 1 THEN " + BASE_AMOUNT_EXPR + @" ELSE 0 END) AS TotalCredit
FROM TVOUCHER V
INNER JOIN TVOUCHERDETAIL VD ON V.VOUCHERID = VD.VOUCHERID
INNER JOIN MACCOUNT ACC ON VD.VOUCHERACCOUNTID = ACC.ACCOUNTID
INNER JOIN MBIZTRANSACTIONTYPE BTT ON V.BIZTRANSACTIONTYPEID = BTT.BIZTRANSACTIONTYPEID
WHERE V.VOUCHERDATE >= @PeriodFromDate AND V.VOUCHERDATE <= @PeriodToDate
  AND V.OUID IN (
        SELECT UAR.OUID FROM MUSERACCESSRIGHTS UAR WHERE UAR.USERID = @UserId AND UAR.OUID <> -1
        UNION
        SELECT OGD.OUID FROM MUSERACCESSRIGHTS UAR2
        INNER JOIN MORGANIZATIONGROUPDETAIL OGD ON OGD.ORGANIZATIONGROUPID = UAR2.OUGROUPID
        WHERE UAR2.USERID = @UserId AND UAR2.OUGROUPID <> -1
  )
  {DynamicFilter};";

        // Streaming export variant — same shape as the paged query, minus OFFSET/FETCH (the DAL applies
        // its own IAsyncEnumerable-based skip/take, matching DayBookReportDAL.GetDayBooksStream). No CTE
        // here either, for consistency with the paged query above (QueryStreamAsync doesn't require
        // this, but keeping one query shape avoids maintaining two different opening-balance strategies).
        public const string GET_ACCOUNT_LEDGER_DETAIL_STREAM = @"
SELECT
    V.VOUCHERID AS VoucherId,
    V.STATUS AS Status,
    V.VOUCHERDATE AS VoucherDate,
    V.VOUCHERAMOUNT AS VoucherAmount,
    V.VOUCHERFCAMOUNT AS VoucherFCAmount,
    V.VOUCHERNUMBER AS VoucherNumber,
    V.VOUCHERREFERENCENUMBER AS VoucherReferenceNumber,
    V.VOUCHERREFERENCEDATE AS VoucherReferenceDate,
    V.VOUCHERNARRATION AS VoucherNarration,
    V.ISREVERSE AS IsReverse,
    V.VALIDTILLDATE AS ValidTillDate,
    V.BIZTRANSACTIONTYPEID AS BizTransactionTypeId,
    BTT.BIZTRANSACTIONTYPECODE AS BizTransactionTypeCode,
    BTT.BIZTRANSACTIONTYPENAME AS BizTransactionTypeName,
    VD.VOUCHERDETAILID AS VoucherDetailId,
    VD.SLNO AS Slno,
    VD.VOUCHERAMOUNTFC AS VoucherDetailVoucherAmountFC,
    VD.VOUCHERAMOUNT AS VoucherDetailVoucherAmount,
    VD.CURRENCYID AS CurrencyId,
    CUR.CURRENCYCODE AS CurrencyCode,
    CUR.CURRENCYNAME AS CurrencyName,
    VD.CURRENCYCONVERSION AS ConversionFactor,
    CASE WHEN VD.DETAILTYPE = 0 THEN BA.BaseAmount ELSE 0 END AS Debit,
    CASE WHEN VD.DETAILTYPE = 1 THEN BA.BaseAmount ELSE 0 END AS Credit,
    VD.VOUCHERLINENARRATION AS VoucherLineNarration,
    VD.DETAILTYPE AS DetailType,
    VD.VOUCHERACCOUNTID AS VoucherAccountId,
    ACC.ACCOUNTCODE AS VoucherAccountCode,
    ACC.ACCOUNTNAME AS VoucherAccountName,
    CASE V.STATUS
        WHEN 0 THEN 'Pending' WHEN 1 THEN 'Active' WHEN 2 THEN 'Deleted'
        WHEN 3 THEN 'Amended/Rejected' WHEN 4 THEN 'Inactive' WHEN 5 THEN 'Archived'
        ELSE '' END AS VoucherStatus,
    ISNULL(OC.OpeningBalance, 0) AS AccountOpeningBalance,
    ISNULL(OC.OpeningBalance, 0)
        + SUM(CASE WHEN VD.DETAILTYPE = 0 THEN BA.BaseAmount ELSE -BA.BaseAmount END)
          OVER (PARTITION BY VD.VOUCHERACCOUNTID ORDER BY V.VOUCHERDATE, VD.SLNO
                ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningBalance,
    @CurrencyBasis AS CurrencyBasis,
    @ReportCurrencyId AS ReportCurrencyId,
    RC.CONVERSIONRATE AS ReportConversionRate,
    CASE WHEN @ReportCurrencyId <> 0 AND RC.CONVERSIONRATE IS NOT NULL
         THEN BA.BaseAmount * RC.CONVERSIONRATE ELSE NULL END AS ReportConvertedAmount,
    0 AS TotalDebit,
    0 AS TotalCredit
FROM TVOUCHER V
INNER JOIN TVOUCHERDETAIL VD ON V.VOUCHERID = VD.VOUCHERID
INNER JOIN MACCOUNT ACC ON VD.VOUCHERACCOUNTID = ACC.ACCOUNTID
INNER JOIN MBIZTRANSACTIONTYPE BTT ON V.BIZTRANSACTIONTYPEID = BTT.BIZTRANSACTIONTYPEID
LEFT JOIN MCURRENCY CUR ON VD.CURRENCYID = CUR.CURRENCYID
CROSS APPLY (VALUES (" + BASE_AMOUNT_EXPR + @")) AS BA(BaseAmount)
CROSS APPLY (VALUES (" + FROM_CURRENCY_EXPR + @")) AS FC(FromCurrencyId)
" + OPENING_BALANCE_APPLY + @"
OUTER APPLY (
    SELECT TOP 1 EX.CONVERSIONRATE
    FROM VEXCHANGEDATEWISE EX
    WHERE EX.D <= V.VOUCHERDATE AND EX.FROMCURRENCYID = FC.FromCurrencyId AND EX.TOCURRENCYID = @ReportCurrencyId
    ORDER BY EX.D DESC
) RC
WHERE V.VOUCHERDATE >= @PeriodFromDate AND V.VOUCHERDATE <= @PeriodToDate
  AND V.OUID IN (
        SELECT UAR.OUID FROM MUSERACCESSRIGHTS UAR WHERE UAR.USERID = @UserId AND UAR.OUID <> -1
        UNION
        SELECT OGD.OUID FROM MUSERACCESSRIGHTS UAR2
        INNER JOIN MORGANIZATIONGROUPDETAIL OGD ON OGD.ORGANIZATIONGROUPID = UAR2.OUGROUPID
        WHERE UAR2.USERID = @UserId AND UAR2.OUGROUPID <> -1
  )
  {DynamicFilter}
ORDER BY {OrderBy}";

        // Cheap pre-check run before the main query when no account/group/control-account filter narrows
        // the request — if this exceeds AccountLedgerReportBLL's configured cap, the request is rejected
        // rather than running an unbounded "general ledger" pull.
        public const string GET_DISTINCT_ACCOUNT_COUNT = @"
SELECT COUNT(DISTINCT VD.VOUCHERACCOUNTID) AS DistinctAccountCount
FROM TVOUCHER V
INNER JOIN TVOUCHERDETAIL VD ON V.VOUCHERID = VD.VOUCHERID
INNER JOIN MACCOUNT ACC ON VD.VOUCHERACCOUNTID = ACC.ACCOUNTID
INNER JOIN MBIZTRANSACTIONTYPE BTT ON V.BIZTRANSACTIONTYPEID = BTT.BIZTRANSACTIONTYPEID
WHERE V.VOUCHERDATE >= @PeriodFromDate AND V.VOUCHERDATE <= @PeriodToDate
  AND V.OUID IN (
        SELECT UAR.OUID FROM MUSERACCESSRIGHTS UAR WHERE UAR.USERID = @UserId AND UAR.OUID <> -1
        UNION
        SELECT OGD.OUID FROM MUSERACCESSRIGHTS UAR2
        INNER JOIN MORGANIZATIONGROUPDETAIL OGD ON OGD.ORGANIZATIONGROUPID = UAR2.OUGROUPID
        WHERE UAR2.USERID = @UserId AND UAR2.OUGROUPID <> -1
  )
  {DynamicFilter};";

        // ── Ledger Summary — grouped Opening/Debit/Credit/Closing by account (own query/data source;
        //    genuinely different from the Normal/Detail rows above, not just a different column layout —
        //    same reasoning as AccountRegisterSummary vs AccountRegister). Opening balance is a scalar
        //    correlated subquery per group (correlated on VD.VOUCHERACCOUNTID, a GROUP BY key) — same
        //    no-CTE constraint as the detail query above, since this also runs through QueryPagedAsync. ──

        private const string SUMMARY_OPENING_BALANCE_SUBQUERY = @"
(
    SELECT SUM(CASE WHEN VD2.DETAILTYPE = 0 THEN " + BASE_AMOUNT_EXPR_VD2 + @" ELSE -(" + BASE_AMOUNT_EXPR_VD2 + @") END)
    FROM TVOUCHER V2
    INNER JOIN TVOUCHERDETAIL VD2 ON V2.VOUCHERID = VD2.VOUCHERID
    INNER JOIN MACCOUNT ACC2 ON VD2.VOUCHERACCOUNTID = ACC2.ACCOUNTID
    INNER JOIN MBIZTRANSACTIONTYPE BTT2 ON V2.BIZTRANSACTIONTYPEID = BTT2.BIZTRANSACTIONTYPEID
    WHERE VD2.VOUCHERACCOUNTID = VD.VOUCHERACCOUNTID
      AND V2.VOUCHERDATE < @PeriodFromDate
      AND V2.OUID IN (
            SELECT UAR.OUID FROM MUSERACCESSRIGHTS UAR WHERE UAR.USERID = @UserId AND UAR.OUID <> -1
            UNION
            SELECT OGD.OUID FROM MUSERACCESSRIGHTS UAR2
            INNER JOIN MORGANIZATIONGROUPDETAIL OGD ON OGD.ORGANIZATIONGROUPID = UAR2.OUGROUPID
            WHERE UAR2.USERID = @UserId AND UAR2.OUGROUPID <> -1
      )
      {OpeningDynamicFilter}
)";

        public const string GET_ACCOUNT_LEDGER_SUMMARY = @"
SELECT
    ACC.ACCOUNTGROUPID AS AccountGroupId,
    AG.ACCOUNTGROUPCODE AS AccountGroupCode,
    AG.ACCOUNTGROUPNAME AS AccountGroupName,
    ACC.CONTROLACCOUNTID AS ControlAccountId,
    CTRL.ACCOUNTCODE AS ControlAccountCode,
    CTRL.ACCOUNTNAME AS ControlAccountName,
    VD.VOUCHERACCOUNTID AS AccountId,
    ACC.ACCOUNTCODE AS AccountCode,
    ACC.ACCOUNTNAME AS AccountName,
    ISNULL(" + SUMMARY_OPENING_BALANCE_SUBQUERY + @", 0) AS OpeningBalance,
    SUM(CASE WHEN VD.DETAILTYPE = 0 THEN " + BASE_AMOUNT_EXPR + @" ELSE 0 END) AS Debit,
    SUM(CASE WHEN VD.DETAILTYPE = 1 THEN " + BASE_AMOUNT_EXPR + @" ELSE 0 END) AS Credit,
    ISNULL(" + SUMMARY_OPENING_BALANCE_SUBQUERY + @", 0)
        + SUM(CASE WHEN VD.DETAILTYPE = 0 THEN " + BASE_AMOUNT_EXPR + @" ELSE -(" + BASE_AMOUNT_EXPR + @") END) AS ClosingBalance
FROM TVOUCHER V
INNER JOIN TVOUCHERDETAIL VD ON V.VOUCHERID = VD.VOUCHERID
INNER JOIN MACCOUNT ACC ON VD.VOUCHERACCOUNTID = ACC.ACCOUNTID
LEFT JOIN MACCOUNTGROUP AG ON ACC.ACCOUNTGROUPID = AG.ACCOUNTGROUPID
LEFT JOIN MACCOUNT CTRL ON ACC.CONTROLACCOUNTID = CTRL.ACCOUNTID
INNER JOIN MBIZTRANSACTIONTYPE BTT ON V.BIZTRANSACTIONTYPEID = BTT.BIZTRANSACTIONTYPEID
WHERE V.VOUCHERDATE >= @PeriodFromDate AND V.VOUCHERDATE <= @PeriodToDate
  AND V.OUID IN (
        SELECT UAR.OUID FROM MUSERACCESSRIGHTS UAR WHERE UAR.USERID = @UserId AND UAR.OUID <> -1
        UNION
        SELECT OGD.OUID FROM MUSERACCESSRIGHTS UAR2
        INNER JOIN MORGANIZATIONGROUPDETAIL OGD ON OGD.ORGANIZATIONGROUPID = UAR2.OUGROUPID
        WHERE UAR2.USERID = @UserId AND UAR2.OUGROUPID <> -1
  )
  {DynamicFilter}
GROUP BY ACC.ACCOUNTGROUPID, AG.ACCOUNTGROUPCODE, AG.ACCOUNTGROUPNAME,
         ACC.CONTROLACCOUNTID, CTRL.ACCOUNTCODE, CTRL.ACCOUNTNAME,
         VD.VOUCHERACCOUNTID, ACC.ACCOUNTCODE, ACC.ACCOUNTNAME
ORDER BY {OrderBy}
OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";

        public const string GET_ACCOUNT_LEDGER_SUMMARY_TOTALS = @"
SELECT
    SUM(CASE WHEN VD.DETAILTYPE = 0 THEN " + BASE_AMOUNT_EXPR + @" ELSE 0 END) AS TotalDebit,
    SUM(CASE WHEN VD.DETAILTYPE = 1 THEN " + BASE_AMOUNT_EXPR + @" ELSE 0 END) AS TotalCredit
FROM TVOUCHER V
INNER JOIN TVOUCHERDETAIL VD ON V.VOUCHERID = VD.VOUCHERID
INNER JOIN MACCOUNT ACC ON VD.VOUCHERACCOUNTID = ACC.ACCOUNTID
INNER JOIN MBIZTRANSACTIONTYPE BTT ON V.BIZTRANSACTIONTYPEID = BTT.BIZTRANSACTIONTYPEID
WHERE V.VOUCHERDATE >= @PeriodFromDate AND V.VOUCHERDATE <= @PeriodToDate
  AND V.OUID IN (
        SELECT UAR.OUID FROM MUSERACCESSRIGHTS UAR WHERE UAR.USERID = @UserId AND UAR.OUID <> -1
        UNION
        SELECT OGD.OUID FROM MUSERACCESSRIGHTS UAR2
        INNER JOIN MORGANIZATIONGROUPDETAIL OGD ON OGD.ORGANIZATIONGROUPID = UAR2.OUGROUPID
        WHERE UAR2.USERID = @UserId AND UAR2.OUGROUPID <> -1
  )
  {DynamicFilter};";
    }
}