namespace AccountsDAL.Query.AccountReports
{
    // Receivable Dashboard (Finance Dashboard Migration Phase 3) -- a 3-piece composite (KPI +
    // incharge-wise + ageing-wise), same "multiple internal queries -> one composite DTO" pattern as
    // AccountCashFlowReportDAL.GetCashFlowIndirectReport. Two of the three pieces reuse EXISTING,
    // already-verified queries verbatim -- KPI reuses AccountOutstandingReportsQB.GET_OUTSTANDING_
    // DETAIL_TOTALS, incharge-wise reuses AccountOutstandingReportsQB.GET_OUTSTANDING_SUMMARY with
    // AccountOutstandingGroupByBuilder.Build(3) (InCharge). Only the ageing-wise piece needs a new
    // query here: GET_AGEING_OVERALL is GET_AGEING_SUMMARY with no grouping dimension at all (a
    // single overall bucket-total row for the whole accessible book, not broken out by
    // account/group/incharge/etc.) -- {AgeBucketColumns} still comes from the existing, already-
    // verified AccountAgeingBucketBuilder (its NumberOfAge/AgeBoundaries are already fully
    // caller-configurable, so no hardcoded bucket scheme to re-verify here).
    public static class AccountReceivableDashboardQB
    {
        public const string GET_AGEING_OVERALL = @"
SELECT
    {AgeBucketColumns}
    SUM(CASE WHEN PB.BALANCE > 0 THEN PB.BALANCE ELSE 0 END) AS RecAmt,
    SUM(CASE WHEN PB.BALANCE < 0 THEN -PB.BALANCE ELSE 0 END) AS PayAmt,
    SUM(PB.BALANCE) AS NetAmt,
    SUM(PB.BALANCE) AS Total
FROM FNPENDINGBILL(@AsOnDate, '', @InfoRequired) PB
INNER JOIN TBILLALLOCATION VBILL ON PB.ALLOCATIONLINEID = VBILL.BILLALLOCATIONLINEID AND VBILL.BILLALLOCATIONLINEID <> -1
INNER JOIN TVOUCHERDETAIL VD ON VBILL.VOUCHERDETAILID = VD.VOUCHERDETAILID
INNER JOIN TVOUCHER V ON VD.VOUCHERID = V.VOUCHERID
INNER JOIN MACCOUNT ACC ON VD.VOUCHERACCOUNTID = ACC.ACCOUNTID
INNER JOIN MBIZTRANSACTIONTYPE BTT ON V.BIZTRANSACTIONTYPEID = BTT.BIZTRANSACTIONTYPEID
WHERE 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}";
    }
}