namespace AccountsDAL.Query.AccountReports
{
    /// <summary>
    /// SQL constants for the DayBook report.
    /// All queries are fully parameterised — no string concatenation for values.
    /// Dynamic filter conditions ({DynamicFilter}) are built in the DAL using hardcoded SQL
    /// fragments with @param references; user-supplied values are always bound via DynamicParameters.
    /// </summary>
    public static class DayBookReportsQB
    {
        // ── Opening balance temp table (WithAccount variants only) ────────────
        // Creates #TempLedgerTotal for the current session.
        // Executed first; main WithAccount queries then JOIN this temp table.
        public const string CREATE_OPENING_BALANCE_TEMP = @"
            SELECT b.VoucherAccountId AS VoucherAccountId,
                   g.D               AS AccountPostDate,
                   SUM(CASE WHEN a.VoucherDate < g.D THEN
                           CASE b.DetailType WHEN 0 THEN b.VoucherAmount ELSE b.VoucherAmount * -1 END
                       ELSE 0 END) AS Opening,
                   SUM(CASE WHEN a.VoucherDate = g.D THEN
                           CASE b.DetailType WHEN 0 THEN b.VoucherAmount ELSE 0 END
                       ELSE 0 END) AS Debit,
                   SUM(CASE WHEN a.VoucherDate = g.D THEN
                           CASE b.DetailType WHEN 1 THEN b.VoucherAmount ELSE 0 END
                       ELSE 0 END) AS Credit,
                   SUM(CASE b.DetailType WHEN 0 THEN b.VoucherAmount ELSE b.VoucherAmount * -1 END) AS Closing
            INTO #TempLedgerTotal
            FROM TVoucher a
            JOIN TVoucherDetail b    ON b.VoucherId       = a.VoucherId
            JOIN MAccount c          ON c.AccountId       = b.VoucherAccountId
            JOIN MAccountGroup d     ON d.AccountGroupId  = c.AccountGroupId
            JOIN MAccountSchedule e  ON e.AccountScheduleId = d.AccountScheduleId
            JOIN MBizTransactionType f ON f.BizTransactionTypeId = a.BizTransactionTypeId
            JOIN DateTally g         ON a.VoucherDate <= g.D
            WHERE a.Status IN (0, 1)
              AND a.OUId              = @ouid
              AND b.VoucherAccountId IN @voucherDetailAccountIds
              AND g.D >= @periodFromDate
              AND g.D <= @periodToDate
              {DynamicFilter}
            GROUP BY b.VoucherAccountId, g.D";

        // ── Non-consolidated, without account filter ──────────────────────────

        public const string GET_DAYBOOK_NOCON_NOACC = @"
            SELECT voucher.VoucherId              AS VoucherId,
                   voucher.Status                 AS Status,
                   voucher.VoucherNumber          AS VoucherNumber,
                   voucher.VoucherDate            AS VoucherDate,
                   voucher.VoucherReferenceNumber AS ReferenceNumber,
                   voucher.VoucherReferenceDate   AS ReferenceDate,
                   voucher.BizTransactionTypeId   AS BizTransactionTypeId,
                   btt.BizTransactionTypeCode     AS BizTransactionTypeCode,
                   btt.BizTransactionTypeName     AS BizTransactionTypeName,
                   voucher.VoucherNarration       AS VoucherNarration,
                   vd.SlNo                        AS SlNo,
                   acc.AccountId                  AS VoucherDetailAccountId,
                   acc.AccountCode                AS VoucherDetailAccountCode,
                   acc.AccountName                AS VoucherDetailAccountName,
                   CASE WHEN vd.DetailType = 0 THEN vd.VoucherAmount ELSE 0 END AS Debit,
                   CASE WHEN vd.DetailType = 1 THEN vd.VoucherAmount ELSE 0 END AS Credit,
                   vd.VoucherLineNarration        AS VoucherDetailNarration,
                   'DAYBOOK'                      AS AccountBooksType,
                   voucher.CreatedById            AS CreatedById,
                   voucher.ModifiedById           AS ModifiedById,
                   cd.UserCode                    AS CreatedByCode,
                   cd.UserName                    AS CreatedByName,
                   md.UserCode                    AS ModifiedByCode,
                   md.UserName                    AS ModifiedByName
            FROM TVoucher voucher
            JOIN TVoucherDetail vd  ON vd.VoucherId  = voucher.VoucherId
            JOIN MBizTransactionType btt ON btt.BizTransactionTypeId = voucher.BizTransactionTypeId
            JOIN MAccount acc       ON acc.AccountId = vd.VoucherAccountId
            JOIN MUser cd           ON cd.UserId     = voucher.CreatedById
            JOIN MUser md           ON md.UserId     = voucher.ModifiedById
            WHERE voucher.VoucherId <> -1
              AND voucher.VoucherDate >= @periodFromDate
              AND voucher.VoucherDate <= @periodToDate
              AND voucher.OUId = @ouid
              AND voucher.FinanceBookId IN (
                  SELECT fb.FinanceBookId FROM MFinanceBook fb WHERE fb.FinanceBookId = @financeBookId
                  UNION
                  SELECT fb.BaseBookId   FROM MFinanceBook fb WHERE fb.FinanceBookId = @financeBookId AND fb.BookType = 3
              )
              {DynamicFilter}
            ORDER BY voucher.VoucherDate, btt.BizTransactionTypeCode, voucher.VoucherNumber, vd.SlNo";

        public const string GET_DAYBOOK_NOCON_NOACC_TOTAL = @"
            SELECT SUM(CASE WHEN vd.DetailType = 0 THEN vd.VoucherAmount ELSE 0 END) AS TotalDebit,
                   SUM(CASE WHEN vd.DetailType = 1 THEN vd.VoucherAmount ELSE 0 END) AS TotalCredit
            FROM TVoucher voucher
            JOIN TVoucherDetail vd  ON vd.VoucherId  = voucher.VoucherId
            JOIN MBizTransactionType btt ON btt.BizTransactionTypeId = voucher.BizTransactionTypeId
            JOIN MAccount acc       ON acc.AccountId = vd.VoucherAccountId
            JOIN MUser cd           ON cd.UserId     = voucher.CreatedById
            JOIN MUser md           ON md.UserId     = voucher.ModifiedById
            WHERE voucher.VoucherId <> -1
              AND voucher.VoucherDate >= @periodFromDate
              AND voucher.VoucherDate <= @periodToDate
              AND voucher.OUId = @ouid
              AND voucher.FinanceBookId IN (
                  SELECT fb.FinanceBookId FROM MFinanceBook fb WHERE fb.FinanceBookId = @financeBookId
                  UNION
                  SELECT fb.BaseBookId   FROM MFinanceBook fb WHERE fb.FinanceBookId = @financeBookId AND fb.BookType = 3
              )
              {DynamicFilter}";

        // ── Consolidated (sales grouped), without account filter ──────────────

        public const string GET_DAYBOOK_CON_NOACC = @"
            -- Non-sales: individual voucher detail rows
            SELECT voucher.VoucherId              AS VoucherId,
                   voucher.Status                 AS Status,
                   voucher.VoucherNumber          AS VoucherNumber,
                   voucher.VoucherDate            AS VoucherDate,
                   voucher.VoucherReferenceNumber AS ReferenceNumber,
                   voucher.VoucherReferenceDate   AS ReferenceDate,
                   voucher.BizTransactionTypeId   AS BizTransactionTypeId,
                   btt.BizTransactionTypeCode     AS BizTransactionTypeCode,
                   btt.BizTransactionTypeName     AS BizTransactionTypeName,
                   voucher.VoucherNarration       AS VoucherNarration,
                   vd.SlNo                        AS SlNo,
                   acc.AccountId                  AS VoucherDetailAccountId,
                   acc.AccountCode                AS VoucherDetailAccountCode,
                   acc.AccountName                AS VoucherDetailAccountName,
                   CASE WHEN vd.DetailType = 0 THEN vd.VoucherAmount ELSE 0 END AS Debit,
                   CASE WHEN vd.DetailType = 1 THEN vd.VoucherAmount ELSE 0 END AS Credit,
                   vd.VoucherLineNarration        AS VoucherDetailNarration,
                   'DAYBOOK'                      AS AccountBooksType,
                   voucher.CreatedById            AS CreatedById,
                   voucher.ModifiedById           AS ModifiedById,
                   cd.UserCode                    AS CreatedByCode,
                   cd.UserName                    AS CreatedByName,
                   md.UserCode                    AS ModifiedByCode,
                   md.UserName                    AS ModifiedByName
            FROM TVoucher voucher
            JOIN TVoucherDetail vd  ON vd.VoucherId  = voucher.VoucherId
            JOIN MBizTransactionType btt ON btt.BizTransactionTypeId = voucher.BizTransactionTypeId
            JOIN MAccount acc       ON acc.AccountId = vd.VoucherAccountId
            JOIN MUser cd           ON cd.UserId     = voucher.CreatedById
            JOIN MUser md           ON md.UserId     = voucher.ModifiedById
            WHERE voucher.VoucherId <> -1
              AND btt.BizTransactionClassId <> -1799999904
              AND voucher.VoucherDate >= @periodFromDate
              AND voucher.VoucherDate <= @periodToDate
              AND voucher.OUId = @ouid
              AND voucher.FinanceBookId IN (
                  SELECT fb.FinanceBookId FROM MFinanceBook fb WHERE fb.FinanceBookId = @financeBookId
                  UNION
                  SELECT fb.BaseBookId   FROM MFinanceBook fb WHERE fb.FinanceBookId = @financeBookId AND fb.BookType = 3
              )
              {DynamicFilter}
            UNION ALL
            -- Sales class: consolidated (grouped) rows
            SELECT -1                             AS VoucherId,
                   -1                             AS Status,
                   'Consolidated'                 AS VoucherNumber,
                   voucher.VoucherDate            AS VoucherDate,
                   'Consolidated'                 AS ReferenceNumber,
                   voucher.VoucherDate            AS ReferenceDate,
                   voucher.BizTransactionTypeId   AS BizTransactionTypeId,
                   btt.BizTransactionTypeCode     AS BizTransactionTypeCode,
                   btt.BizTransactionTypeName     AS BizTransactionTypeName,
                   'Consolidated'                 AS VoucherNarration,
                   1                              AS SlNo,
                   acc.AccountId                  AS VoucherDetailAccountId,
                   acc.AccountCode                AS VoucherDetailAccountCode,
                   acc.AccountName                AS VoucherDetailAccountName,
                   SUM(CASE WHEN vd.DetailType = 0 THEN vd.VoucherAmount ELSE 0 END) AS Debit,
                   SUM(CASE WHEN vd.DetailType = 1 THEN vd.VoucherAmount ELSE 0 END) AS Credit,
                   'Consolidated'                 AS VoucherDetailNarration,
                   'DAYBOOK'                      AS AccountBooksType,
                   voucher.CreatedById            AS CreatedById,
                   voucher.ModifiedById           AS ModifiedById,
                   'Consolidated'                 AS CreatedByCode,
                   'Consolidated'                 AS CreatedByName,
                   'Consolidated'                 AS ModifiedByCode,
                   'Consolidated'                 AS ModifiedByName
            FROM TVoucher voucher
            JOIN TVoucherDetail vd  ON vd.VoucherId  = voucher.VoucherId
            JOIN MBizTransactionType btt ON btt.BizTransactionTypeId = voucher.BizTransactionTypeId
            JOIN MAccount acc       ON acc.AccountId = vd.VoucherAccountId
            WHERE voucher.VoucherId <> -1
              AND btt.BizTransactionClassId = -1799999904
              AND voucher.VoucherDate >= @periodFromDate
              AND voucher.VoucherDate <= @periodToDate
              AND voucher.OUId = @ouid
              AND voucher.FinanceBookId IN (
                  SELECT fb.FinanceBookId FROM MFinanceBook fb WHERE fb.FinanceBookId = @financeBookId
                  UNION
                  SELECT fb.BaseBookId   FROM MFinanceBook fb WHERE fb.FinanceBookId = @financeBookId AND fb.BookType = 3
              )
              {DynamicFilter}
            GROUP BY voucher.VoucherDate, voucher.BizTransactionTypeId,
                     btt.BizTransactionTypeCode, btt.BizTransactionTypeName,
                     acc.AccountId, acc.AccountCode, acc.AccountName,
                     voucher.CreatedById, voucher.ModifiedById";

        public const string GET_DAYBOOK_CON_NOACC_TOTAL = @"
            SELECT SUM(a.Debit) AS TotalDebit, SUM(a.Credit) AS TotalCredit
            FROM (
                SELECT CASE WHEN vd.DetailType = 0 THEN vd.VoucherAmount ELSE 0 END AS Debit,
                       CASE WHEN vd.DetailType = 1 THEN vd.VoucherAmount ELSE 0 END AS Credit
                FROM TVoucher voucher
                JOIN TVoucherDetail vd  ON vd.VoucherId  = voucher.VoucherId
                JOIN MBizTransactionType btt ON btt.BizTransactionTypeId = voucher.BizTransactionTypeId
                JOIN MAccount acc       ON acc.AccountId = vd.VoucherAccountId
                JOIN MUser cd           ON cd.UserId     = voucher.CreatedById
                JOIN MUser md           ON md.UserId     = voucher.ModifiedById
                WHERE voucher.VoucherId <> -1
                  AND btt.BizTransactionClassId <> -1799999904
                  AND voucher.VoucherDate >= @periodFromDate
                  AND voucher.VoucherDate <= @periodToDate
                  AND voucher.OUId = @ouid
                  AND voucher.FinanceBookId IN (
                      SELECT fb.FinanceBookId FROM MFinanceBook fb WHERE fb.FinanceBookId = @financeBookId
                      UNION
                      SELECT fb.BaseBookId   FROM MFinanceBook fb WHERE fb.FinanceBookId = @financeBookId AND fb.BookType = 3
                  )
                  {DynamicFilter}
                UNION ALL
                SELECT SUM(CASE WHEN vd.DetailType = 0 THEN vd.VoucherAmount ELSE 0 END) AS Debit,
                       SUM(CASE WHEN vd.DetailType = 1 THEN vd.VoucherAmount ELSE 0 END) AS Credit
                FROM TVoucher voucher
                JOIN TVoucherDetail vd  ON vd.VoucherId  = voucher.VoucherId
                JOIN MBizTransactionType btt ON btt.BizTransactionTypeId = voucher.BizTransactionTypeId
                JOIN MAccount acc       ON acc.AccountId = vd.VoucherAccountId
                WHERE voucher.VoucherId <> -1
                  AND btt.BizTransactionClassId = -1799999904
                  AND voucher.VoucherDate >= @periodFromDate
                  AND voucher.VoucherDate <= @periodToDate
                  AND voucher.OUId = @ouid
                  AND voucher.FinanceBookId IN (
                      SELECT fb.FinanceBookId FROM MFinanceBook fb WHERE fb.FinanceBookId = @financeBookId
                      UNION
                      SELECT fb.BaseBookId   FROM MFinanceBook fb WHERE fb.FinanceBookId = @financeBookId AND fb.BookType = 3
                  )
                  {DynamicFilter}
            ) a";

        // ── Non-consolidated, with account filter (uses #TempLedgerTotal) ─────

        public const string GET_DAYBOOK_NOCON_WITHACC = @"
            SELECT ap.Opening                     AS OpeningAmount,
                   voucher.Status                 AS Status,
                   voucher.VoucherId              AS VoucherId,
                   voucher.VoucherNumber          AS VoucherNumber,
                   voucher.VoucherDate            AS VoucherDate,
                   voucher.VoucherReferenceNumber AS ReferenceNumber,
                   voucher.VoucherReferenceDate   AS ReferenceDate,
                   voucher.BizTransactionTypeId   AS BizTransactionTypeId,
                   btt.BizTransactionTypeCode     AS BizTransactionTypeCode,
                   btt.BizTransactionTypeName     AS BizTransactionTypeName,
                   voucher.VoucherNarration       AS VoucherNarration,
                   acc.AccountId                  AS VoucherDetailAccountId,
                   acc.AccountCode                AS VoucherDetailAccountCode,
                   acc.AccountName                AS VoucherDetailAccountName,
                   CASE WHEN sq.VoucherId IS NOT NULL
                       THEN CASE WHEN vd.DetailType = 1 THEN vd.VoucherAmount ELSE 0 END
                       ELSE CASE WHEN vd.DetailType = 0 THEN vd.VoucherAmount ELSE 0 END
                   END AS Debit,
                   CASE WHEN sq.VoucherId IS NOT NULL
                       THEN CASE WHEN vd.DetailType = 0 THEN vd.VoucherAmount ELSE 0 END
                       ELSE CASE WHEN vd.DetailType = 1 THEN vd.VoucherAmount ELSE 0 END
                   END AS Credit,
                   vd.VoucherLineNarration        AS VoucherDetailNarration,
                   'DAYBOOK'                      AS AccountBooksType,
                   voucher.CreatedById            AS CreatedById,
                   voucher.ModifiedById           AS ModifiedById,
                   cd.UserCode                    AS CreatedByCode,
                   cd.UserName                    AS CreatedByName,
                   md.UserCode                    AS ModifiedByCode,
                   md.UserName                    AS ModifiedByName
            FROM TVoucher voucher
            LEFT JOIN TVoucherDetail vd ON vd.VoucherId = voucher.VoucherId
            LEFT JOIN (
                SELECT v.VoucherId, vd2.VoucherDetailId, vd2.VoucherAmount, vd2.VoucherLineNarration
                FROM TVoucher v
                JOIN TVoucherDetail vd2 ON vd2.VoucherId = v.VoucherId
                WHERE v.VoucherDate >= @periodFromDate
                  AND v.VoucherDate <= @periodToDate
                  AND v.OUId = @ouid
                  AND vd2.VoucherAccountId IN @voucherDetailAccountIds
            ) sq ON sq.VoucherDetailId = vd.VoucherDetailId
            LEFT JOIN #TempLedgerTotal ap
                ON ap.VoucherAccountId IN @voucherDetailAccountIds
               AND ap.AccountPostDate = voucher.VoucherDate
            JOIN MBizTransactionType btt ON btt.BizTransactionTypeId = voucher.BizTransactionTypeId
            JOIN MAccount acc       ON acc.AccountId = vd.VoucherAccountId
            JOIN MUser cd           ON cd.UserId     = voucher.CreatedById
            JOIN MUser md           ON md.UserId     = voucher.ModifiedById
            WHERE voucher.VoucherId <> -1
              AND acc.AccountId NOT IN @voucherDetailAccountIds
              AND voucher.VoucherDate >= @periodFromDate
              AND voucher.VoucherDate <= @periodToDate
              AND voucher.OUId = @ouid
              AND voucher.FinanceBookId IN (
                  SELECT fb.FinanceBookId FROM MFinanceBook fb WHERE fb.FinanceBookId = @financeBookId
                  UNION
                  SELECT fb.BaseBookId   FROM MFinanceBook fb WHERE fb.FinanceBookId = @financeBookId AND fb.BookType = 3
              )
              {DynamicFilter}
            ORDER BY voucher.VoucherDate, voucher.VoucherNumber";

        public const string GET_DAYBOOK_NOCON_WITHACC_TOTAL = @"
            SELECT SUM(CASE WHEN sq.VoucherId IS NOT NULL
                           THEN CASE WHEN vd.DetailType = 1 THEN vd.VoucherAmount ELSE 0 END
                           ELSE CASE WHEN vd.DetailType = 0 THEN vd.VoucherAmount ELSE 0 END END) AS TotalDebit,
                   SUM(CASE WHEN sq.VoucherId IS NOT NULL
                           THEN CASE WHEN vd.DetailType = 0 THEN vd.VoucherAmount ELSE 0 END
                           ELSE CASE WHEN vd.DetailType = 1 THEN vd.VoucherAmount ELSE 0 END END) AS TotalCredit
            FROM TVoucher voucher
            LEFT JOIN TVoucherDetail vd ON vd.VoucherId = voucher.VoucherId
            LEFT JOIN (
                SELECT v.VoucherId, vd2.VoucherDetailId
                FROM TVoucher v
                JOIN TVoucherDetail vd2 ON vd2.VoucherId = v.VoucherId
                WHERE v.VoucherDate >= @periodFromDate
                  AND v.VoucherDate <= @periodToDate
                  AND v.OUId = @ouid
                  AND vd2.VoucherAccountId IN @voucherDetailAccountIds
            ) sq ON sq.VoucherDetailId = vd.VoucherDetailId
            JOIN MBizTransactionType btt ON btt.BizTransactionTypeId = voucher.BizTransactionTypeId
            JOIN MAccount acc       ON acc.AccountId = vd.VoucherAccountId
            JOIN MUser cd           ON cd.UserId     = voucher.CreatedById
            JOIN MUser md           ON md.UserId     = voucher.ModifiedById
            WHERE voucher.VoucherId <> -1
              AND acc.AccountId NOT IN @voucherDetailAccountIds
              AND voucher.VoucherDate >= @periodFromDate
              AND voucher.VoucherDate <= @periodToDate
              AND voucher.OUId = @ouid
              AND voucher.FinanceBookId IN (
                  SELECT fb.FinanceBookId FROM MFinanceBook fb WHERE fb.FinanceBookId = @financeBookId
                  UNION
                  SELECT fb.BaseBookId   FROM MFinanceBook fb WHERE fb.FinanceBookId = @financeBookId AND fb.BookType = 3
              )
              {DynamicFilter}";

        // ── Consolidated, with account filter (uses #TempLedgerTotal) ─────────

        public const string GET_DAYBOOK_CON_WITHACC = @"
            -- Non-sales: reversed debit/credit for selected account's counterparts
            SELECT ap.Opening                     AS OpeningAmount,
                   voucher.Status                 AS Status,
                   voucher.VoucherId              AS VoucherId,
                   voucher.VoucherNumber          AS VoucherNumber,
                   voucher.VoucherDate            AS VoucherDate,
                   voucher.VoucherReferenceNumber AS ReferenceNumber,
                   voucher.VoucherReferenceDate   AS ReferenceDate,
                   voucher.BizTransactionTypeId   AS BizTransactionTypeId,
                   btt.BizTransactionTypeCode     AS BizTransactionTypeCode,
                   btt.BizTransactionTypeName     AS BizTransactionTypeName,
                   voucher.VoucherNarration       AS VoucherNarration,
                   acc.AccountId                  AS VoucherDetailAccountId,
                   acc.AccountCode                AS VoucherDetailAccountCode,
                   acc.AccountName                AS VoucherDetailAccountName,
                   CASE WHEN sq.VoucherId IS NOT NULL
                       THEN CASE WHEN vd.DetailType = 1 THEN vd.VoucherAmount ELSE 0 END
                       ELSE CASE WHEN vd.DetailType = 0 THEN vd.VoucherAmount ELSE 0 END
                   END AS Debit,
                   CASE WHEN sq.VoucherId IS NOT NULL
                       THEN CASE WHEN vd.DetailType = 0 THEN vd.VoucherAmount ELSE 0 END
                       ELSE CASE WHEN vd.DetailType = 1 THEN vd.VoucherAmount ELSE 0 END
                   END AS Credit,
                   vd.VoucherLineNarration        AS VoucherDetailNarration,
                   'DAYBOOK'                      AS AccountBooksType,
                   voucher.CreatedById            AS CreatedById,
                   voucher.ModifiedById           AS ModifiedById,
                   cd.UserCode                    AS CreatedByCode,
                   cd.UserName                    AS CreatedByName,
                   md.UserCode                    AS ModifiedByCode,
                   md.UserName                    AS ModifiedByName
            FROM TVoucher voucher
            LEFT JOIN TVoucherDetail vd ON vd.VoucherId = voucher.VoucherId
            LEFT JOIN (
                SELECT v.VoucherId, vd2.VoucherDetailId, vd2.VoucherAmount, vd2.VoucherLineNarration
                FROM TVoucher v
                JOIN TVoucherDetail vd2 ON vd2.VoucherId = v.VoucherId
                WHERE v.VoucherDate >= @periodFromDate
                  AND v.VoucherDate <= @periodToDate
                  AND v.OUId = @ouid
                  AND vd2.VoucherAccountId IN @voucherDetailAccountIds
            ) sq ON sq.VoucherDetailId = vd.VoucherDetailId
            LEFT JOIN #TempLedgerTotal ap
                ON ap.VoucherAccountId IN @voucherDetailAccountIds
               AND ap.AccountPostDate = voucher.VoucherDate
            JOIN MBizTransactionType btt ON btt.BizTransactionTypeId = voucher.BizTransactionTypeId
            JOIN MAccount acc       ON acc.AccountId = vd.VoucherAccountId
            JOIN MUser cd           ON cd.UserId     = voucher.CreatedById
            JOIN MUser md           ON md.UserId     = voucher.ModifiedById
            WHERE voucher.VoucherId <> -1
              AND btt.BizTransactionClassId <> -1799999904
              AND acc.AccountId NOT IN @voucherDetailAccountIds
              AND voucher.VoucherDate >= @periodFromDate
              AND voucher.VoucherDate <= @periodToDate
              AND voucher.OUId = @ouid
              AND voucher.FinanceBookId IN (
                  SELECT fb.FinanceBookId FROM MFinanceBook fb WHERE fb.FinanceBookId = @financeBookId
                  UNION
                  SELECT fb.BaseBookId   FROM MFinanceBook fb WHERE fb.FinanceBookId = @financeBookId AND fb.BookType = 3
              )
              {DynamicFilter}
            UNION ALL
            -- Sales class: consolidated rows
            SELECT NULL                           AS OpeningAmount,
                   -1                             AS Status,
                   'Consolidated'                 AS VoucherNumber,
                   voucher.VoucherDate            AS VoucherDate,
                   'Consolidated'                 AS ReferenceNumber,
                   voucher.VoucherDate            AS ReferenceDate,
                   voucher.BizTransactionTypeId   AS BizTransactionTypeId,
                   btt.BizTransactionTypeCode     AS BizTransactionTypeCode,
                   btt.BizTransactionTypeName     AS BizTransactionTypeName,
                   'Consolidated'                 AS VoucherNarration,
                   acc.AccountId                  AS VoucherDetailAccountId,
                   acc.AccountCode                AS VoucherDetailAccountCode,
                   acc.AccountName                AS VoucherDetailAccountName,
                   SUM(CASE WHEN vd.DetailType = 0 THEN vd.VoucherAmount ELSE 0 END) AS Debit,
                   SUM(CASE WHEN vd.DetailType = 1 THEN vd.VoucherAmount ELSE 0 END) AS Credit,
                   'Consolidated'                 AS VoucherDetailNarration,
                   'DAYBOOK'                      AS AccountBooksType,
                   voucher.CreatedById            AS CreatedById,
                   voucher.ModifiedById           AS ModifiedById,
                   'Consolidated'                 AS CreatedByCode,
                   'Consolidated'                 AS CreatedByName,
                   'Consolidated'                 AS ModifiedByCode,
                   'Consolidated'                 AS ModifiedByName
            FROM TVoucher voucher
            JOIN TVoucherDetail vd  ON vd.VoucherId  = voucher.VoucherId
            JOIN MBizTransactionType btt ON btt.BizTransactionTypeId = voucher.BizTransactionTypeId
            JOIN MAccount acc       ON acc.AccountId = vd.VoucherAccountId
            WHERE voucher.VoucherId <> -1
              AND btt.BizTransactionClassId = -1799999904
              AND acc.AccountId NOT IN @voucherDetailAccountIds
              AND voucher.VoucherDate >= @periodFromDate
              AND voucher.VoucherDate <= @periodToDate
              AND voucher.OUId = @ouid
              AND voucher.FinanceBookId IN (
                  SELECT fb.FinanceBookId FROM MFinanceBook fb WHERE fb.FinanceBookId = @financeBookId
                  UNION
                  SELECT fb.BaseBookId   FROM MFinanceBook fb WHERE fb.FinanceBookId = @financeBookId AND fb.BookType = 3
              )
              {DynamicFilter}
            GROUP BY voucher.VoucherDate, voucher.BizTransactionTypeId,
                     btt.BizTransactionTypeCode, btt.BizTransactionTypeName,
                     acc.AccountId, acc.AccountCode, acc.AccountName,
                     voucher.CreatedById, voucher.ModifiedById";

        public const string GET_DAYBOOK_CON_WITHACC_TOTAL = @"
            SELECT SUM(a.Debit) AS TotalDebit, SUM(a.Credit) AS TotalCredit
            FROM (
                SELECT SUM(CASE WHEN sq.VoucherId IS NOT NULL
                               THEN CASE WHEN vd.DetailType = 1 THEN vd.VoucherAmount ELSE 0 END
                               ELSE CASE WHEN vd.DetailType = 0 THEN vd.VoucherAmount ELSE 0 END END) AS Debit,
                       SUM(CASE WHEN sq.VoucherId IS NOT NULL
                               THEN CASE WHEN vd.DetailType = 0 THEN vd.VoucherAmount ELSE 0 END
                               ELSE CASE WHEN vd.DetailType = 1 THEN vd.VoucherAmount ELSE 0 END END) AS Credit
                FROM TVoucher voucher
                LEFT JOIN TVoucherDetail vd ON vd.VoucherId = voucher.VoucherId
                LEFT JOIN (
                    SELECT v.VoucherId, vd2.VoucherDetailId
                    FROM TVoucher v
                    JOIN TVoucherDetail vd2 ON vd2.VoucherId = v.VoucherId
                    WHERE v.VoucherDate >= @periodFromDate
                      AND v.VoucherDate <= @periodToDate
                      AND v.OUId = @ouid
                      AND vd2.VoucherAccountId IN @voucherDetailAccountIds
                ) sq ON sq.VoucherDetailId = vd.VoucherDetailId
                JOIN MBizTransactionType btt ON btt.BizTransactionTypeId = voucher.BizTransactionTypeId
                JOIN MAccount acc       ON acc.AccountId = vd.VoucherAccountId
                JOIN MUser cd           ON cd.UserId     = voucher.CreatedById
                JOIN MUser md           ON md.UserId     = voucher.ModifiedById
                WHERE voucher.VoucherId <> -1
                  AND btt.BizTransactionClassId <> -1799999904
                  AND acc.AccountId NOT IN @voucherDetailAccountIds
                  AND voucher.VoucherDate >= @periodFromDate
                  AND voucher.VoucherDate <= @periodToDate
                  AND voucher.OUId = @ouid
                  AND voucher.FinanceBookId IN (
                      SELECT fb.FinanceBookId FROM MFinanceBook fb WHERE fb.FinanceBookId = @financeBookId
                      UNION
                      SELECT fb.BaseBookId   FROM MFinanceBook fb WHERE fb.FinanceBookId = @financeBookId AND fb.BookType = 3
                  )
                  {DynamicFilter}
                UNION ALL
                SELECT SUM(CASE WHEN vd.DetailType = 0 THEN vd.VoucherAmount ELSE 0 END) AS Debit,
                       SUM(CASE WHEN vd.DetailType = 1 THEN vd.VoucherAmount ELSE 0 END) AS Credit
                FROM TVoucher voucher
                JOIN TVoucherDetail vd  ON vd.VoucherId  = voucher.VoucherId
                JOIN MBizTransactionType btt ON btt.BizTransactionTypeId = voucher.BizTransactionTypeId
                JOIN MAccount acc       ON acc.AccountId = vd.VoucherAccountId
                WHERE voucher.VoucherId <> -1
                  AND btt.BizTransactionClassId = -1799999904
                  AND acc.AccountId NOT IN @voucherDetailAccountIds
                  AND voucher.VoucherDate >= @periodFromDate
                  AND voucher.VoucherDate <= @periodToDate
                  AND voucher.OUId = @ouid
                  AND voucher.FinanceBookId IN (
                      SELECT fb.FinanceBookId FROM MFinanceBook fb WHERE fb.FinanceBookId = @financeBookId
                      UNION
                      SELECT fb.BaseBookId   FROM MFinanceBook fb WHERE fb.FinanceBookId = @financeBookId AND fb.BookType = 3
                  )
                  {DynamicFilter}
            ) a";
    }
}