using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace AccountsDAL.Query.Voucher
{
    public static class VoucherQB
    {
        // ── Save/Update Voucher — full-parity port of GB4 VoucherBLL.SaveVoucher (Voucher.svc's
        // single POST / upsert operation, keyed by VoucherId: 0 = insert, non-zero = update).
        //
        // Replaces the previous TVP/stored-procedure scaffold (POST_VOUCHER_DOCUMENT — a SP that
        // never existed in GB4 and was never deployed here either; its call was commented out, so
        // nothing ever actually persisted). GB4 itself doesn't use a stored procedure for this at
        // all — it builds the whole object graph in memory with pre-assigned ids and does one
        // NHibernate Session.Save/SaveOrUpdate cascade (confirmed via Voucher.hbm.xml's
        // cascade="all" + <generator class="assigned"> on every id). Ported here as explicit
        // parameterized INSERTs per row inside one transaction instead, matching every other write
        // path built this session (SaveUpdateOpeningVoucher, TDSUndoPosting) — per explicit
        // direction, not as a new stored procedure.
        //
        // Schema confirmed live via INFORMATION_SCHEMA.COLUMNS (2026-07-27) for TVOUCHER,
        // TVOUCHERDETAIL, TVOUCHERADDON, TBILLALLOCATION, TVOUCHERCOSTCENTER, TINSTRUMENT,
        // TPARAMETERDETAIL (same tables GetVoucher already SELECTs from — no new schema risk).
        // Every DTO property name below already matches its column alias exactly (case-
        // insensitive), so the DTO objects are passed directly as Dapper parameters — no
        // anonymous-object remapping needed.
        public const string SAVE_VOUCHER_HEADER = @"
            INSERT INTO TVOUCHER (
                VOUCHERID, OUID, PERIODID, BIZTRANSACTIONTYPEID, VOUCHERNUMBER, VOUCHERDATE, VOUCHERFCAMOUNT,
                CURRENCYID, CONVERSIONFACTOR, VOUCHERAMOUNT, VOUCHERREFERENCENUMBER, VOUCHERREFERENCEDATE,
                MAINACCOUNTID, SUBACCOUNTID, VOUCHERNARRATION, ISREVERSE, VALIDTILLDATE, COUNTEROPERATIONID,
                ISACCOUNTPOST, PARAMETERSETID, VERSION, STATUS, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON,
                MAXCREDITACCOUNTID, MAXDEBITACCOUNTID, POSTEDFROMTYPE, POSTEDFROMID, POSTEDFROMBIZTRANSACTIONTYPEID,
                NOOFORIGINALCOPY, NOOFCOPYTAKEN, FINANCEBOOKID, INSTRUMENTPOSTEDTYPE, ALLOCATIONID
            ) VALUES (
                @VoucherId, @OrganizationUnitId, @PeriodId, @BizTransactionTypeId, @VoucherVoucherNumber, @VoucherVoucherDate, @VoucherVoucherFCAmount,
                @CurrencyId, @VoucherConversionFactor, @VoucherVoucherAmount, @VoucherReferenceNumber, @VoucherReferenceDate,
                @MainAccountId, @SubAccountId, @VoucherNarration, @VoucherIsReverse, @VoucherValidTillDate, @VoucherCounterOperationId,
                @VoucherIsAccountPost, @ParameterSetId, @VoucherVersion, @VoucherStatus, @VoucherCreatedById, @VoucherCreatedOn, @VoucherModifiedById, @VoucherModifiedOn,
                @VoucherMaxCreditAccountId, @VoucherMaxDebitAccountId, @VoucherPostedFromType, @VoucherPostedFromId, @PostedBizTransactionTypeId,
                @VoucherNoOfOrginalCopy, @VoucherNoOfCopyTaken, @FinanceBookId, @VoucherInstrumentPostedType, @AllocationId
            );";

        // CREATEDBYID/CREATEDON intentionally excluded from the SET list — original creation audit
        // fields are preserved across updates, only MODIFIEDBYID/MODIFIEDON change.
        // VOUCHERNUMBER intentionally excluded from the SET list, same reasoning as CREATEDBYID/
        // CREATEDON below — once assigned (by VoucherNumberService on insert, or by the caller for
        // Manual-numbering transaction types), a voucher's number is immutable. This also prevents
        // a client sending the wrong JSON field name (or omitting it) from silently NULLing out an
        // existing valid number on update.
        public const string UPDATE_VOUCHER_HEADER = @"
            UPDATE TVOUCHER SET
                OUID = @OrganizationUnitId, PERIODID = @PeriodId, BIZTRANSACTIONTYPEID = @BizTransactionTypeId,
                VOUCHERDATE = @VoucherVoucherDate, VOUCHERFCAMOUNT = @VoucherVoucherFCAmount,
                CURRENCYID = @CurrencyId, CONVERSIONFACTOR = @VoucherConversionFactor, VOUCHERAMOUNT = @VoucherVoucherAmount,
                VOUCHERREFERENCENUMBER = @VoucherReferenceNumber, VOUCHERREFERENCEDATE = @VoucherReferenceDate,
                MAINACCOUNTID = @MainAccountId, SUBACCOUNTID = @SubAccountId, VOUCHERNARRATION = @VoucherNarration,
                ISREVERSE = @VoucherIsReverse, VALIDTILLDATE = @VoucherValidTillDate, COUNTEROPERATIONID = @VoucherCounterOperationId,
                ISACCOUNTPOST = @VoucherIsAccountPost, PARAMETERSETID = @ParameterSetId, VERSION = @VoucherVersion, STATUS = @VoucherStatus,
                MODIFIEDBYID = @VoucherModifiedById, MODIFIEDON = @VoucherModifiedOn,
                MAXCREDITACCOUNTID = @VoucherMaxCreditAccountId, MAXDEBITACCOUNTID = @VoucherMaxDebitAccountId,
                POSTEDFROMTYPE = @VoucherPostedFromType, POSTEDFROMID = @VoucherPostedFromId, POSTEDFROMBIZTRANSACTIONTYPEID = @PostedBizTransactionTypeId,
                NOOFORIGINALCOPY = @VoucherNoOfOrginalCopy, NOOFCOPYTAKEN = @VoucherNoOfCopyTaken, FINANCEBOOKID = @FinanceBookId,
                INSTRUMENTPOSTEDTYPE = @VoucherInstrumentPostedType, ALLOCATIONID = @AllocationId
            WHERE VOUCHERID = @VoucherId;";

        // VOUCHERDATE/VALIDTILLDATE on TVOUCHERDETAIL are NOT NULL denormalized copies of the
        // header's own date fields (GB4's SaveVoucher never references them directly — no SQL/HQL
        // in the source sets or reads them independently of the header) — populated here from the
        // header's own VoucherVoucherDate/VoucherValidTillDate rather than left null, since the
        // column doesn't allow it. GCCURRENCYCONVERSION/VOUCHERAMOUNTGC (group-currency) have no
        // GB4 equivalent at all (no reference anywhere in the ported source) — defaulted to 1/0
        // matching the same convention already used for CURRENCYCONVERSION defaults elsewhere.
        public const string SAVE_VOUCHER_DETAIL = @"
            INSERT INTO TVOUCHERDETAIL (
                VOUCHERDETAILID, VOUCHERID, SLNO, VOUCHERACCOUNTID, SUBACCOUNTID, VOUCHERAMOUNTFC, CURRENCYID,
                CURRENCYCONVERSION, VOUCHERAMOUNT, INTEROUID, INTERACCOUNTID, INTERSUBACCOUNTID, INTERVOUCHERID,
                DETAILPARAMETERSETID, VOUCHERLINENARRATION, DETAILTYPE, VOUCHERDATE, VALIDTILLDATE,
                ACCURRENCYID, ACCURRENCYCONVERSION, VOUCHERAMOUNTAC, GCCURRENCYCONVERSION, VOUCHERAMOUNTGC,
                COSTCENTERID, ALLOCATIONID
            ) VALUES (
                @VoucherDetailId, @VoucherId, @VoucherDetailSlno, @VoucherAccountId, @SubAccountId, @VoucherDetailAmountFC, @CurrencyId,
                @VoucherDetailCurrencyConversion, @VoucherDetailAmount, @InterOUId, @InterAccountId, @InterSubAccountId, @InterVoucherId,
                @ParameterSetId, @VoucherDetailVoucherLineNarration, @VoucherDetailDetailType, @HeaderVoucherDate, @HeaderValidTillDate,
                @AcCurrencyId, @VoucherDetailAcCurrencyConversion, @VoucherDetailVoucherAmountAc, 1, 0,
                @CostCenterId, @AllocationId
            );";

        public const string SAVE_VOUCHER_ADDON = @"
            INSERT INTO TVOUCHERADDON (VOUCHERADDONID, VOUCHERID, VOUCHERDETAILID, SLNO, PARAMETERID, INPUTDATE, INPUTID, INPUTVALUE, INPUTTEXT)
            VALUES (@VoucherAddonId, @VoucherId, @VoucherLineId, @VoucherAddonSlno, @ParameterId, @VoucherAddonInputDate, @VoucherAddonInputId, @VoucherAddonInputValue, @VoucherAddonInputText);";

        public const string SAVE_BILL_ALLOCATION = @"
            INSERT INTO TBILLALLOCATION (
                BILLALLOCATIONLINEID, VOUCHERDETAILID, SLNO, ALLOCATIONTYPE, PRICECATEGORYID, REFERENCENUMBER,
                REFERENCEDATE, DUEDATE, PLANDATE, INCHARGEID, STATUS, FULLAMOUNT, CURRENCYID, ALLOCATIONLINEID,
                ALLOCATEDAMOUNT, DETAILNUMBER, DETAILDATE, DUEDAYS, DUETYPE, ROUTEID, CURRENCYCONVERSION,
                FULLAMOUNTFC, ALLOCATEDAMOUNTFC
            ) VALUES (
                @VoucherBillAllocationId, @VoucherLineId, @VoucherBillAllocationSlno, @VoucherBillAllocationType, @PriceCategoryId, @VoucherBillAllocationReferenceNumber,
                @VoucherBillAllocationReferenceDate, @VoucherBillAllocationDueDate, @VoucherBillAllocationPlanDate, @InChargeId, @VoucherBillAllocationStatus, @VoucherBillAllocationFullAmount, @CurrencyId, @AllocationLineId,
                @VoucherBillAllocationAllocatedAmount, @VoucherBillAllocationDetailNumber, @VoucherBillAllocationDetailDate, @VoucherBillAllocationDueDays, @VoucherBillAllocationDueType, @RouteId, @VoucherBillAllocationCurrencyConversion,
                @VoucherBillAllocationFullAmountFC, @VoucherBillAllocationAllocatedAmountFC
            );";

        public const string SAVE_COST_CENTER = @"
            INSERT INTO TVOUCHERCOSTCENTER (VOUCHERCOSTCENTERID, VOUCHERDETAILID, COSTCATEGORYSLNO, COSTCATEGORYID, SLNO, COSTCENTERID, ALLOCATEDAMOUNT)
            VALUES (@VoucherCostCenterId, @VoucherLineId, @VoucherCostCenterCostCategorySlno, @CostCategoryId, @VoucherCostCenterSlno, @CostCenterId, @VoucherCostCenterAllocatedAmount);";

        // DEPOSITNUMBER has no GB4-side equivalent (GB4's own ACCOUNT_VOUCHER_INSTRUMENT_GET never
        // selects it either) — defaulted to '' since the column disallows null.
        public const string SAVE_INSTRUMENT = @"
            INSERT INTO TINSTRUMENT (
                INSTRUMENTLINEID, VOUCHERDETAILID, SLNO, INSTRUMENTID, INSTRUMENTNUMBER, INSTRUMENTDATE,
                DRAWNONBANK, DRAWNONBRANCH, INSTRUMENTFCAMOUNT, CURRENCYID, CURRENCYCONVERSION, INSTRUMENTAMOUNT,
                PARTICULARS, INSTRUMENTSTATUS, DEPOSITID, DEPOSITDATE, REALISEDDATE, BOUNCEDDATE, CANCELDATE,
                SUBACCOUNTID, BANKACCOUNTID, ISACCOUNTPOSTED, ACCOUNTVOUCHERID, NOOFORIGINALCOPY, NOOFCOPYTAKEN,
                ACCOUNTID, DEPOSITNUMBER
            ) VALUES (
                @VoucherInstrumentId, @VoucherLineId, @VoucherInstrumentSlno, @InstrumentId, @VoucherInstrumentNumber, @VoucherInstrumentDate,
                @VoucherInstrumentDrawnOnBank, @VoucherInstrumentDrawnOnBranch, @VoucherInstrumentFCAmount, @CurrencyId, @VoucherInstrumentCurrencyConversion, @VoucherInstrumentAmount,
                @VoucherInstrumentParticulars, @VoucherInstrumentStatus, @VoucherInstrumentDepositId, @VoucherInstrumentDepositDate, @VoucherInstrumentRealisedDate, @VoucherInstrumentBouncedDate, @VoucherInstrumentCancelDate,
                @SubAccountId, @BankAccountId, @VoucherInstrumentIsAccountPosted, @VoucherInstrumentAccountVoucherId, @VoucherInstrumentNoOfOriginalCopy, @VoucherInstrumentNoOfCopyTaken,
                @AccountId, ''
            );";

        public const string SAVE_VOUCHER_TDS = @"
            INSERT INTO TVOUCHERTDS (
                VOUCHERTDSID, VOUCHERLINEID, TDSSLNO, TDSNATURE, TDSTYPE, ACCOUNTID, REFERENCENUMBER, REFERENCEDATE,
                CREDITDATE, PARTICULARS, AMOUNT, TDSCATEGORYID, SECTION, REFERENCESECTION, TDSPERCENT, TDSAMOUNT,
                SCPERCENT, SCAMOUNT, CESSPERCENT, CESSAMOUNT, INTERESTAMOUNT, PENALTYAMOUNT, OTHERAMOUNT, TOTALAMOUNT,
                ROUNDEDAMOUNT, TYPEOFDEDUCTION, LESSDEDUCTIONTYPE, LESSDEDUCTIONREASON, TDSSTATUS, ALLOTEDVOUCHERLINEID,
                PAIDTDSAMOUNT, PAIDSCAMOUNT, PAIDCESSAMOUNT, CERTIFICATENUMBER, CERTIFICATEDATE, REMARKS, MODIFIEDON
            ) VALUES (
                @VoucherTDSId, @VoucherLineId, @VoucherTDSTdsSlNo, @VoucherTDSTDSNature, @VoucherTDSTDSType, @AccountId, @VoucherTDSReferenceNumber, @VoucherTDSReferenceDate,
                @VoucherTDSCreditDate, @VoucherTDSParticulars, @VoucherTDSAmount, @TDSCategoryId, @VoucherTDSSection, @VoucherTDSReferenceSection, @VoucherTDSTDSPercent, @VoucherTDSTDSAmount,
                @VoucherTDSScPercent, @VoucherTDSScAmount, @VoucherTDSCessPercent, @VoucherTDSCessAmount, @VoucherTDSInterestAmount, @VoucherTDSPenaltyAmount, @VoucherTDSOtherAmount, @VoucherTDSTotalAmount,
                @VoucherTDSRoundedAmount, @VoucherTDSTypeOfDeduction, @VoucherTDSLessDeductionType, @VoucherTDSLessDeductionReason, @VoucherTDSTDSStatus, @AllotedVoucherLineId,
                @VoucherTDSPaidTDSAmount, @VoucherTDSPaidScAmount, @VoucherTDSPaidCessAmount, @VoucherTDSCertificateNumber, @VoucherTDSCertificateDate, @VoucherTDSRemarks, @VoucherTDSModifiedOn
            );";

        // ── Update path — delete-then-recreate (matches GB4's own update strategy exactly, see
        // VoucherBLL.SaveVoucher's update branch — it is NOT a diff). Order respects FK depth,
        // deepest children first, TVOUCHERDETAIL last. Parameterized translations of GB4's HQL
        // constants (DELETE_VOUCHER_DETAIL_TPARAMETERDETAIL / DELETE_VOUCHERHEADERADDON /
        // DELETE_VOUCHERCHILDADDON / DELETE_BILLALLOCATION / DELETE_COSTCENTER / DELETE_INSTRUMENT /
        // DELETE_VOUCHERTDS / DELETE_VOUCHERDETAIL).
        //
        // Deliberate fix over GB4's DELETE_VOUCHERCHILDADDON: GB4's own HQL was
        // "...voucherdetailid in (...) and voucherid = -1" — but line-level addons are inserted
        // with their REAL VoucherId (see AssignIds: da.VoucherId = v.VoucherId), never -1. That
        // condition can never match, so GB4 never actually deletes line-level addons on update —
        // old rows are orphaned and new ones pile up alongside them on every re-save. Fixed here by
        // dropping the impossible "voucherid = -1" clause; the VOUCHERDETAILID-in-subquery
        // condition alone already correctly scopes to this voucher's line addons only (head addons
        // have VOUCHERDETAILID = -1, which the subquery — real detail ids only — never returns).
        public const string DELETE_VOUCHER_DETAIL_PARAMETERS_FOR_VOUCHER = @"
            DELETE FROM TPARAMETERDETAIL
            WHERE OBJECTTYPEID = -1899999138
              AND OBJECTID IN (SELECT VOUCHERDETAILID FROM TVOUCHERDETAIL WHERE VOUCHERID = @VoucherId);";

        public const string DELETE_VOUCHER_HEAD_ADDONS_FOR_VOUCHER = @"
            DELETE FROM TVOUCHERADDON WHERE VOUCHERDETAILID = -1 AND VOUCHERID = @VoucherId;";

        public const string DELETE_VOUCHER_LINE_ADDONS_FOR_VOUCHER = @"
            DELETE FROM TVOUCHERADDON
            WHERE VOUCHERDETAILID IN (SELECT VOUCHERDETAILID FROM TVOUCHERDETAIL WHERE VOUCHERID = @VoucherId);";

        public const string DELETE_BILL_ALLOCATIONS_FOR_VOUCHER = @"
            DELETE FROM TBILLALLOCATION
            WHERE VOUCHERDETAILID IN (SELECT VOUCHERDETAILID FROM TVOUCHERDETAIL WHERE VOUCHERID = @VoucherId);";

        public const string DELETE_COST_CENTERS_FOR_VOUCHER = @"
            DELETE FROM TVOUCHERCOSTCENTER
            WHERE VOUCHERDETAILID IN (SELECT VOUCHERDETAILID FROM TVOUCHERDETAIL WHERE VOUCHERID = @VoucherId);";

        // GB4 parity: Voucher.svc/Instrument/SelectList (GetInstrumentNumberSelectList) — a
        // picklist of already-recorded voucher instrument/cheque LINES (TINSTRUMENT), not the
        // MINSTRUMENT master list (that's the separate, pre-existing /Instrument/GetSelectListInstrument
        // endpoint). GB4 branched on a "ChequeType" criteria flag to switch between a filtered
        // HQL query (Instrument.Type=1) and an unfiltered generic-reflection query; both are
        // replaced here by one parameterized query with an explicit InstrumentType filter,
        // matching the InstrumentTypes filter convention already used by VoucherQB's own
        // GET_BRS_LOAD/GET_BRS_LOAD_COUNT queries against these same two tables.
        public const string GET_SELECTLIST_VOUCHER_INSTRUMENT = @"
            SELECT DISTINCT
                A.INSTRUMENTLINEID AS Id,
                A.INSTRUMENTNUMBER AS Number,
                A.INSTRUMENTDATE   AS Date
            FROM TINSTRUMENT A
            LEFT JOIN MINSTRUMENT B ON A.INSTRUMENTID = B.INSTRUMENTID
            WHERE (@InstrumentType = -1 OR B.INSTRUMENTTYPE = @InstrumentType)
            AND (@Number IS NULL OR @Number = '' OR A.INSTRUMENTNUMBER LIKE '%' + @Number + '%')
            AND (@DrawnOnBank IS NULL OR @DrawnOnBank = '' OR A.DRAWNONBANK LIKE '%' + @DrawnOnBank + '%')
            AND (@BankAccountId = -1 OR A.BANKACCOUNTID = @BankAccountId)
            ORDER BY A.INSTRUMENTDATE DESC;
            ";

        public const string DELETE_INSTRUMENTS_FOR_VOUCHER = @"
            DELETE FROM TINSTRUMENT
            WHERE VOUCHERDETAILID IN (SELECT VOUCHERDETAILID FROM TVOUCHERDETAIL WHERE VOUCHERID = @VoucherId);";

        public const string DELETE_TDS_FOR_VOUCHER = @"
            DELETE FROM TVOUCHERTDS
            WHERE VOUCHERLINEID IN (SELECT VOUCHERDETAILID FROM TVOUCHERDETAIL WHERE VOUCHERID = @VoucherId);";

        public const string DELETE_VOUCHER_DETAILS_FOR_VOUCHER = @"
            DELETE FROM TVOUCHERDETAIL WHERE VOUCHERID = @VoucherId;";

        // ── Delete/Cancel Voucher — full-parity port of GB4 VoucherBLL.DeleteVoucher (Voucher.svc's
        // DELETE /?VoucherId=&CancelApplicable= operation). CancelApplicable: 0 = hard delete
        // (removes header + all children), 1 = soft "cancel" (STATUS=2 flip, header kept, most
        // children removed, TVOUCHERDETAIL deleted row-by-row per GB4's own comment — "for Trigger
        // to affect" — rather than a bulk DELETE).
        //
        // Detaches any OTHER voucher's bill-allocation rows that were allotted "against" one of
        // this voucher's own bill-allocation lines, resetting them to standalone/self-referencing
        // (ALLOCATIONTYPE=0, FULLAMOUNT=ALLOCATEDAMOUNT, ALLOCATIONLINEID=BILLALLOCATIONLINEID) so
        // deleting this voucher doesn't leave a dangling cross-reference. Runs unconditionally
        // before either delete mode, matching GB4.
        public const string DETACH_BILL_ALLOCATION_AGAINST_REFERENCES_FOR_VOUCHER = @"
            UPDATE TBILLALLOCATION
            SET ALLOCATIONTYPE = 0, FULLAMOUNT = ALLOCATEDAMOUNT, ALLOCATIONLINEID = BILLALLOCATIONLINEID
            WHERE BILLALLOCATIONLINEID IN (
                SELECT BILLALLOCATIONLINEID FROM TBILLALLOCATION WHERE ALLOCATIONLINEID IN (
                    SELECT BILLALLOCATIONLINEID FROM TBILLALLOCATION
                    WHERE VOUCHERDETAILID IN (SELECT VOUCHERDETAILID FROM TVOUCHERDETAIL WHERE VOUCHERID = @VoucherId)
                ) AND BILLALLOCATIONLINEID <> ALLOCATIONLINEID
            );";

        public const string DELETE_BIZDIMENSION_FOR_VOUCHER = @"
            DELETE FROM TBIZDIMENSION
            WHERE OBJECTHEADERTYPEID = -1899999161 AND OBJECTTYPEID = -1899999138 AND OBJECTHEADERID = @VoucherId;";

        public const string DELETE_VOUCHER_HEADER = @"
            DELETE FROM TVOUCHER WHERE VOUCHERID = @VoucherId;";

        public const string SOFT_CANCEL_VOUCHER_HEADER = @"
            UPDATE TVOUCHER SET STATUS = 2, MODIFIEDON = @ModifiedOn, MODIFIEDBYID = @ModifiedById
            WHERE VOUCHERID = @VoucherId;";

        // GB4 deletes TVOUCHERDETAIL rows one at a time (not a bulk DELETE) specifically so a
        // per-row DB trigger fires for each — preserved here for the soft-cancel path only (the
        // hard-delete path's DeleteAllChildrenForVoucher still uses the bulk delete, matching GB4's
        // own hard-delete path which never bulk-deletes TVOUCHERDETAIL explicitly at all — it relies
        // on the NHibernate cascade, which is a single implicit per-row DELETE internally anyway).
        public const string DELETE_VOUCHER_DETAIL_BY_ID = @"
            DELETE FROM TVOUCHERDETAIL WHERE VOUCHERDETAILID = @VoucherDetailId;";

        public const string GET_VOUCHER_DETAIL_IDS_FOR_VOUCHER = @"
            SELECT VOUCHERDETAILID FROM TVOUCHERDETAIL WHERE VOUCHERID = @VoucherId;";

        // ── Validation support ────────────────────────────────────────────────
        // BizTransactionType flags needed by ValidateVoucher: OU-restriction (OUTYPE=0 means
        // restricted to OUID, OUTYPE=1 means any OU), BizTransactionClassId (Opening class =
        // -1399999983 skips several rules; batch-allocation class = -1399999872 requires one
        // account across all lines), BillAccountType (bill-allocation requirement), and the
        // cost-center flags already used by GetVoucher's GET_VOUCHER_BIZ_TYPE_COST_CENTER_REQUIRED_COUNT.
        // Columns confirmed real via existing GB5 usage (Admin/AdminDAL/Query/BIZTransactionType/BIZTransactionTypeQB.cs).
        public const string GET_BIZ_TRANSACTION_TYPE_FOR_SAVE = @"
            SELECT
                BIZTRANSACTIONTYPEID AS BizTransactionTypeId,
                OUID AS OrganizationUnitId,
                OUTYPE AS OUType,
                BIZTRANSACTIONCLASSID AS BizTransactionClassId,
                BILLACCOUNTTYPE AS BillAccountType,
                ISCOSTCENTER AS IsCostCenter,
                ISMULTIPLECOSTCATEGORY AS IsMultipleCostCategory,
                ISSPLITCOSTCENTER AS IsSplitCostCenter
            FROM MBIZTRANSACTIONTYPE
            WHERE BIZTRANSACTIONTYPEID = @BizTransactionTypeId;";

        public const string GET_PERIOD_DATE_RANGE = @"
            SELECT FROMDATE AS FromDate, TODATE AS ToDate
            FROM MPERIOD
            WHERE PERIODID = @PeriodId;";

        public const string GET_ACCOUNT_TYPE_INFO = @"
            SELECT ACCOUNTID AS AccountId, ACCOUNTTYPE AS AccountType, BILLALLOCATIONTYPE AS BillAllocationType
            FROM MACCOUNT
            WHERE ACCOUNTID = @AccountId;";

        // ── Post-write DB objects (confirmed present on the GB5 target DB, 2026-07-27) ──────────
        // GB4 runs these unconditionally after every save/update, inside the same transaction, in
        // this exact order. POST2PENDINGBILL/GROUPCURRENCYVOUCHERPOST/FNVOUCHERVALIDATE are
        // pre-existing DB objects (no source in either repo — confirmed to exist on the DB via
        // sys.objects/sys.triggers, not authored by this port). The forced no-op UPDATE exists
        // solely to fire TRG_VOUCHER_POST (also confirmed present) — GB4's own comment: "Since
        // Trigger is not working without this line."
        public const string EXEC_POST2PENDINGBILL = "EXEC POST2PENDINGBILL @VoucherId;";

        public const string FORCE_VOUCHER_POST_TRIGGER = "UPDATE TVOUCHER SET STATUS = STATUS WHERE VOUCHERID = @VoucherId;";

        public const string EXEC_GROUPCURRENCYVOUCHERPOST =
            "EXEC GROUPCURRENCYVOUCHERPOST 1, '01-Jan-1899', '31-Dec-9999', @VoucherId, @ClientId;";

        public const string GET_VOUCHER_END_VALIDATION = @"
            SELECT VALIDATIONCODE, VALIDATIONMESSAGE, VALIDATIONVALUE
            FROM dbo.FNVOUCHERVALIDATE(@VoucherId, @IsValidate);";

        // ── Get Voucher — full-parity port of GB4 VoucherBLL.GetVoucher / VoucherDAL.GetSelectedVoucher*
        // (Voucher.svc's bare GET /?VoucherId= operation). Hydrates the full voucher edit/view
        // payload: header + line details, each line's bill allocations/cost centers/instruments/TDS
        // rows/detail parameters, head-level addons, cross-OU bill-allocation stitching, and
        // TDS/cost-center applicability recomputed at read time (see VoucherBLL.GetVoucher). No
        // status/soft-delete filter anywhere, matching GB4 exactly — returns the voucher regardless
        // of VoucherStatus.
        //
        // Schema confirmed live via INFORMATION_SCHEMA.COLUMNS (2026-07-27): TVOUCHER,
        // TVOUCHERDETAIL, TVOUCHERADDON, TVOUCHERCOSTCENTER, TPARAMETERDETAIL, TBILLALLOCATION,
        // TBIZDIMENSION, TINSTRUMENT — none have a TENANTID column (same per-tenant-database
        // pattern already established this session), matching GB4's own lack of a tenant filter.
        // The previous GET_VOUCHER/GET_VOUCHER_DETAILS below referenced ORGANIZATIONUNITID/
        // TENANTID/ACCOUNTID/AMOUNTFC/AMOUNT — columns that don't exist on these tables — a
        // pre-existing bug now fixed using the confirmed real column names (OUID, VOUCHERACCOUNTID,
        // VOUCHERAMOUNTFC, VOUCHERAMOUNT, no TENANTID).
        //
        // Deliberate deviation from GB4's literal comma-join (=INNER JOIN) semantics: every
        // master-data lookup below (OU/Period/BizType/Account/ParameterSet/Currency/Allocation/
        // CostCenter/etc.) uses LEFT JOIN instead. GB4's INNER JOIN would silently return zero rows
        // whenever a referenced master row is missing/sentinel (e.g. SubAccountId/AllocationId left
        // at 0/-1 for vouchers that don't use those optional features) — a real risk given how many
        // optional FKs this header/detail carry. This is a join-style fix, not a business-rule
        // change: every selected column is still exactly what GB4 selected, just tolerant of an
        // absent lookup row instead of losing the whole voucher/line.
        public const string GET_VOUCHER = @"
            SELECT
                V.VOUCHERID AS VoucherId,
                V.OUID AS OrganizationUnitId,
                OU.ORGANIZATIONUNITCODE AS OrganizationUnitCode,
                OU.ORGANIZATIONUNITNAME AS OrganizationUnitName,
                V.PERIODID AS PeriodId,
                P.PERIODCODE AS PeriodCode,
                P.PERIODNAME AS PeriodName,
                V.BIZTRANSACTIONTYPEID AS BizTransactionTypeId,
                BTT.BIZTRANSACTIONTYPECODE AS BizTransactionTypeCode,
                BTT.BIZTRANSACTIONTYPENAME AS BizTransactionTypeName,
                V.VOUCHERDATE AS VoucherVoucherDate,
                V.VOUCHERAMOUNT AS VoucherVoucherAmount,
                V.VOUCHERFCAMOUNT AS VoucherVoucherFCAmount,
                V.VOUCHERNUMBER AS VoucherVoucherNumber,
                V.VOUCHERREFERENCENUMBER AS VoucherReferenceNumber,
                V.VOUCHERREFERENCEDATE AS VoucherReferenceDate,
                V.MAINACCOUNTID AS MainAccountId,
                MA.ACCOUNTCODE AS MainAccountCode,
                MA.ACCOUNTNAME AS MainAccountName,
                V.VOUCHERNARRATION AS VoucherNarration,
                V.ISREVERSE AS VoucherIsReverse,
                V.VALIDTILLDATE AS VoucherValidTillDate,
                V.COUNTEROPERATIONID AS VoucherCounterOperationId,
                V.CONVERSIONFACTOR AS VoucherConversionFactor,
                V.SUBACCOUNTID AS SubAccountId,
                SA.ACCOUNTCODE AS SubAccountCode,
                SA.ACCOUNTNAME AS SubAccountName,
                V.ISACCOUNTPOST AS VoucherIsAccountPost,
                V.PARAMETERSETID AS ParameterSetId,
                PS.PARAMETERSETCODE AS ParameterSetCode,
                PS.PARAMETERSETNAME AS ParameterSetName,
                V.POSTEDFROMTYPE AS VoucherPostedFromType,
                V.POSTEDFROMID AS VoucherPostedFromId,
                V.POSTEDFROMBIZTRANSACTIONTYPEID AS PostedBizTransactionTypeId,
                PBTT.BIZTRANSACTIONTYPECODE AS PostedBizTransactionTypeCode,
                PBTT.BIZTRANSACTIONTYPENAME AS PostedBizTransactionTypeName,
                V.CREATEDBYID AS VoucherCreatedById,
                V.CREATEDON AS VoucherCreatedOn,
                V.MODIFIEDBYID AS VoucherModifiedById,
                V.MODIFIEDON AS VoucherModifiedOn,
                V.STATUS AS VoucherStatus,
                V.VERSION AS VoucherVersion,
                V.CURRENCYID AS CurrencyId,
                C.CURRENCYCODE AS CurrencyCode,
                C.CURRENCYNAME AS CurrencyName,
                V.MAXCREDITACCOUNTID AS VoucherMaxCreditAccountId,
                V.MAXDEBITACCOUNTID AS VoucherMaxDebitAccountId,
                V.NOOFORIGINALCOPY AS VoucherNoOfOrginalCopy,
                V.NOOFCOPYTAKEN AS VoucherNoOfCopyTaken,
                V.FINANCEBOOKID AS FinanceBookId,
                V.INSTRUMENTPOSTEDTYPE AS VoucherInstrumentPostedType,
                V.ALLOCATIONID AS AllocationId,
                ALLOC.ALLOCATIONNAME AS AllocationName,
                ALLOC.DESCRIPTION AS AllocationDescription,
                ALLOC.ALLOCATIONSHORTNAME AS AllocationShortName,
                ALLOCTYPE.ALLOCATIONTYPEID AS AllocationTypeId,
                ALLOCTYPE.ALLOCATIONTYPECODE AS AllocationTypeCode,
                ALLOCTYPE.ALLOCATIONTYPENAME AS AllocationTypeName
            FROM TVOUCHER V
            LEFT JOIN MORGANIZATIONUNIT OU ON V.OUID = OU.OUID
            LEFT JOIN MPERIOD P ON V.PERIODID = P.PERIODID
            LEFT JOIN MBIZTRANSACTIONTYPE BTT ON V.BIZTRANSACTIONTYPEID = BTT.BIZTRANSACTIONTYPEID
            LEFT JOIN MACCOUNT MA ON V.MAINACCOUNTID = MA.ACCOUNTID
            LEFT JOIN MACCOUNT SA ON V.SUBACCOUNTID = SA.ACCOUNTID
            LEFT JOIN MPARAMETERSET PS ON V.PARAMETERSETID = PS.PARAMETERSETID
            LEFT JOIN MBIZTRANSACTIONTYPE PBTT ON V.POSTEDFROMBIZTRANSACTIONTYPEID = PBTT.BIZTRANSACTIONTYPEID
            LEFT JOIN MCURRENCY C ON V.CURRENCYID = C.CURRENCYID
            LEFT JOIN MALLOCATION ALLOC ON V.ALLOCATIONID = ALLOC.ALLOCATIONID
            LEFT JOIN MALLOCATIONTYPE ALLOCTYPE ON ALLOC.ALLOCATIONTYPEID = ALLOCTYPE.ALLOCATIONTYPEID
            WHERE V.VOUCHERID = @VoucherId;";

        public const string GET_VOUCHER_DETAILS = @"
            SELECT
                A.VOUCHERDETAILID AS VoucherDetailId,
                A.VOUCHERID AS VoucherId,
                B.VOUCHERNUMBER AS VoucherVoucherNumber,
                A.SLNO AS VoucherDetailSlno,
                A.VOUCHERACCOUNTID AS VoucherAccountId,
                C.ACCOUNTCODE AS VoucherAccountCode,
                C.ACCOUNTNAME AS VoucherAccountName,
                C.ACCOUNTTYPE AS VoucherAccountType,
                C.BILLALLOCATIONTYPE AS VoucherAccountBillAllocationType,
                C.ACCOUNTGROUPID AS VoucherAccountAccountGroupId,
                E.ACCOUNTSCHEDULENATURE AS VoucherAccountAccountGroupAccountGroupAccountScheduleNature,
                C.ISTDSAPPLICABLE AS VoucherAccountIsTDSApplicable,
                C.TDSCATEGORYID AS VoucherDeducationAccountId,
                F.SUFFEREDACCOUNTID AS VoucherSufferedAccountId,
                F.PAIDACCOUNTID AS VoucherPaidAccountId,
                F.CLAIMACCOUNTID AS VoucherClaimedAccountId,
                A.SUBACCOUNTID AS SubAccountId,
                G.ACCOUNTCODE AS SubAccountCode,
                G.ACCOUNTNAME AS SubAccountName,
                A.VOUCHERAMOUNTFC AS VoucherDetailAmountFC,
                A.CURRENCYCONVERSION AS VoucherDetailCurrencyConversion,
                A.VOUCHERAMOUNT AS VoucherDetailAmount,
                A.INTEROUID AS InterOUId,
                H.ORGANIZATIONUNITCODE AS InterOUCode,
                H.ORGANIZATIONUNITNAME AS InterOUName,
                A.INTERACCOUNTID AS InterAccountId,
                I.ACCOUNTCODE AS InterAccountCode,
                I.ACCOUNTNAME AS InterAccountName,
                A.INTERSUBACCOUNTID AS InterSubAccountId,
                J.ACCOUNTCODE AS InterSubAccountCode,
                J.ACCOUNTNAME AS InterSubAccountName,
                A.INTERVOUCHERID AS InterVoucherId,
                K.VOUCHERNUMBER AS InterVoucherVoucherNumber,
                A.DETAILPARAMETERSETID AS ParameterSetId,
                L.PARAMETERSETCODE AS ParameterSetCode,
                L.PARAMETERSETNAME AS ParameterSetName,
                A.VOUCHERLINENARRATION AS VoucherDetailVoucherLineNarration,
                A.DETAILTYPE AS VoucherDetailDetailType,
                A.CURRENCYID AS CurrencyId,
                M.CURRENCYCODE AS CurrencyCode,
                M.CURRENCYNAME AS CurrencyName,
                A.ACCURRENCYCONVERSION AS VoucherDetailAcCurrencyConversion,
                A.VOUCHERAMOUNTAC AS VoucherDetailVoucherAmountAc,
                A.ACCURRENCYID AS AcCurrencyId,
                ACCUR.CURRENCYCODE AS AcCurrencyCode,
                ACCUR.CURRENCYNAME AS AcCurrencyName,
                ISNULL(SUBDIM.L1Id,-1) AS L1Id,
                ISNULL(SUBDIM.L1Code,'') AS L1Code,
                ISNULL(SUBDIM.L1Name,'') AS L1Name,
                ISNULL(SUBDIM.L2Id,-1) AS L2Id,
                ISNULL(SUBDIM.L2Code,'') AS L2Code,
                ISNULL(SUBDIM.L2Name,'') AS L2Name,
                ISNULL(SUBDIM.L3Id,-1) AS L3Id,
                ISNULL(SUBDIM.L3Code,'') AS L3Code,
                ISNULL(SUBDIM.L3Name,'') AS L3Name,
                ISNULL(SUBDIM.L4Id,-1) AS L4Id,
                ISNULL(SUBDIM.L4Code,'') AS L4Code,
                ISNULL(SUBDIM.L4Name,'') AS L4Name,
                ISNULL(SUBDIM.L5Id,-1) AS L5Id,
                ISNULL(SUBDIM.L5Code,'') AS L5Code,
                ISNULL(SUBDIM.L5Name,'') AS L5Name,
                A.COSTCENTERID AS CostCenterId,
                COSTCENTER.COSTCENTERCODE AS CostCenterCode,
                COSTCENTER.COSTCENTERNAME AS CostCenterName,
                A.ALLOCATIONID AS AllocationId,
                ALLOC.ALLOCATIONNAME AS AllocationName,
                ALLOC.DESCRIPTION AS AllocationDescription,
                ALLOC.ALLOCATIONSHORTNAME AS AllocationShortName,
                ALLOCTYPE.ALLOCATIONTYPEID AS AllocationTypeId,
                ALLOCTYPE.ALLOCATIONTYPECODE AS AllocationTypeCode,
                ALLOCTYPE.ALLOCATIONTYPENAME AS AllocationTypeName
            FROM TVOUCHERDETAIL A
            LEFT JOIN TVOUCHER B ON A.VOUCHERID = B.VOUCHERID
            LEFT JOIN MACCOUNT C ON A.VOUCHERACCOUNTID = C.ACCOUNTID
            LEFT JOIN MACCOUNTGROUP D ON C.ACCOUNTGROUPID = D.ACCOUNTGROUPID
            LEFT JOIN MACCOUNTSCHEDULE E ON D.ACCOUNTSCHEDULEID = E.ACCOUNTSCHEDULEID
            LEFT JOIN MTDSCATEGORY F ON C.TDSCATEGORYID = F.TDSCATEGORYID
            LEFT JOIN MACCOUNT G ON A.SUBACCOUNTID = G.ACCOUNTID
            LEFT JOIN MORGANIZATIONUNIT H ON A.INTEROUID = H.OUID
            LEFT JOIN MACCOUNT I ON A.INTERACCOUNTID = I.ACCOUNTID
            LEFT JOIN MACCOUNT J ON A.INTERSUBACCOUNTID = J.ACCOUNTID
            LEFT JOIN TVOUCHER K ON A.INTERVOUCHERID = K.VOUCHERID
            LEFT JOIN MPARAMETERSET L ON A.DETAILPARAMETERSETID = L.PARAMETERSETID
            LEFT JOIN MCURRENCY M ON A.CURRENCYID = M.CURRENCYID
            LEFT JOIN MCURRENCY ACCUR ON A.ACCURRENCYID = ACCUR.CURRENCYID
            LEFT JOIN MCOSTCENTER COSTCENTER ON A.COSTCENTERID = COSTCENTER.COSTCENTERID
            LEFT JOIN MALLOCATION ALLOC ON A.ALLOCATIONID = ALLOC.ALLOCATIONID
            LEFT JOIN MALLOCATIONTYPE ALLOCTYPE ON ALLOC.ALLOCATIONTYPEID = ALLOCTYPE.ALLOCATIONTYPEID
            OUTER APPLY (
                SELECT
                    V1.BIZDIMENSIONVALUEID AS L1Id, V1.BIZDIMENSIONVALUECODE AS L1Code, V1.BIZDIMENSIONVALUENAME AS L1Name,
                    V2.BIZDIMENSIONVALUEID AS L2Id, V2.BIZDIMENSIONVALUECODE AS L2Code, V2.BIZDIMENSIONVALUENAME AS L2Name,
                    V3.BIZDIMENSIONVALUEID AS L3Id, V3.BIZDIMENSIONVALUECODE AS L3Code, V3.BIZDIMENSIONVALUENAME AS L3Name,
                    V4.BIZDIMENSIONVALUEID AS L4Id, V4.BIZDIMENSIONVALUECODE AS L4Code, V4.BIZDIMENSIONVALUENAME AS L4Name,
                    V5.BIZDIMENSIONVALUEID AS L5Id, V5.BIZDIMENSIONVALUECODE AS L5Code, V5.BIZDIMENSIONVALUENAME AS L5Name
                FROM TBIZDIMENSION TD
                INNER JOIN MBIZDIMENSIONVALUESET VS ON TD.BIZDIMENSIONVALUESETID = VS.BIZDIMENSIONVALUESETID
                LEFT JOIN MBIZDIMENSIONVALUE V1 ON VS.V1 = V1.BIZDIMENSIONVALUEID
                LEFT JOIN MBIZDIMENSIONVALUE V2 ON VS.V2 = V2.BIZDIMENSIONVALUEID
                LEFT JOIN MBIZDIMENSIONVALUE V3 ON VS.V3 = V3.BIZDIMENSIONVALUEID
                LEFT JOIN MBIZDIMENSIONVALUE V4 ON VS.V4 = V4.BIZDIMENSIONVALUEID
                LEFT JOIN MBIZDIMENSIONVALUE V5 ON VS.V5 = V5.BIZDIMENSIONVALUEID
                WHERE TD.OBJECTHEADERTYPEID = -1899999161
                  AND TD.OBJECTHEADERID = A.VOUCHERID
                  AND TD.OBJECTTYPEID = -1899999138
                  AND TD.OBJECTID = A.VOUCHERDETAILID
            ) SUBDIM
            WHERE A.VOUCHERID = @VoucherId
            ORDER BY A.SLNO;";

        // Head-level addons are TVOUCHERADDON rows with VOUCHERDETAILID = -1 (sentinel, matching
        // GB4's "AND A.VOUCHERDETAILID In (-1)" filter for its head-only call); line-level addons
        // are every other row, attached per-line by VoucherLineId. Fetched once per voucher and
        // partitioned in the BLL rather than queried twice, unlike GB4's two separate HQL calls.
        public const string GET_VOUCHER_ADDON = @"
            SELECT
                A.VOUCHERADDONID AS VoucherAddonId,
                A.VOUCHERID AS VoucherId,
                B.VOUCHERNUMBER AS VoucherVoucherNumber,
                A.VOUCHERDETAILID AS VoucherLineId,
                A.SLNO AS VoucherAddonSlno,
                A.PARAMETERID AS ParameterId,
                C.PARAMETERCODE AS ParameterCode,
                C.PARAMETERNAME AS ParameterName,
                A.INPUTDATE AS VoucherAddonInputDate,
                A.INPUTID AS VoucherAddonInputId,
                A.INPUTVALUE AS VoucherAddonInputValue,
                A.INPUTTEXT AS VoucherAddonInputText
            FROM TVOUCHERADDON A
            LEFT JOIN TVOUCHER B ON A.VOUCHERID = B.VOUCHERID
            LEFT JOIN MPARAMETER C ON A.PARAMETERID = C.PARAMETERID
            WHERE A.VOUCHERID = @VoucherId;";

        // Full-parity port of GB4 ACCOUNT_VOUCHER_BILL_ALLOCATION_GET, scoped to a whole voucher
        // (every line's bill allocations in one call). D (self-join for "the allocation line this
        // row was allotted against") is LEFT JOIN — GB4's comma-join would silently drop every
        // not-yet-allotted row (the common case) since ALLOCATIONLINEID is a real FK only once an
        // allotment exists.
        public const string GET_VOUCHER_BILL_ALLOCATION_FOR_VOUCHER = @"
            SELECT
                A.BILLALLOCATIONLINEID AS VoucherBillAllocationId,
                A.VOUCHERDETAILID AS VoucherLineId,
                A.PRICECATEGORYID AS PriceCategoryId,
                B.PRICECATEGORYCODE AS PriceCategoryCode,
                B.PRICECATEGORYNAME AS PriceCategoryName,
                A.SLNO AS VoucherBillAllocationSlno,
                A.ALLOCATIONTYPE AS VoucherBillAllocationType,
                A.REFERENCENUMBER AS VoucherBillAllocationReferenceNumber,
                A.REFERENCEDATE AS VoucherBillAllocationReferenceDate,
                A.DUEDATE AS VoucherBillAllocationDueDate,
                A.PLANDATE AS VoucherBillAllocationPlanDate,
                A.INCHARGEID AS InChargeId,
                C.EMPLOYEECODE AS InChargeCode,
                C.EMPLOYEENAME AS InChargeName,
                A.STATUS AS VoucherBillAllocationStatus,
                A.FULLAMOUNT AS VoucherBillAllocationFullAmount,
                A.ALLOCATIONLINEID AS AllocationLineId,
                D.DETAILNUMBER AS AllocationLineDetailNumber,
                A.ALLOCATEDAMOUNT AS VoucherBillAllocationAllocatedAmount,
                A.CURRENCYID AS CurrencyId,
                E.CURRENCYCODE AS CurrencyCode,
                E.CURRENCYNAME AS CurrencyName,
                A.DETAILNUMBER AS VoucherBillAllocationDetailNumber,
                A.DETAILDATE AS VoucherBillAllocationDetailDate,
                A.DUEDAYS AS VoucherBillAllocationDueDays,
                A.DUETYPE AS VoucherBillAllocationDueType,
                A.ROUTEID AS RouteId,
                RT.ROUTENAME AS RouteName,
                A.FULLAMOUNTFC AS VoucherBillAllocationFullAmountFC,
                A.ALLOCATEDAMOUNTFC AS VoucherBillAllocationAllocatedAmountFC,
                A.CURRENCYCONVERSION AS VoucherBillAllocationCurrencyConversion
            FROM TBILLALLOCATION A
            INNER JOIN TVOUCHERDETAIL F ON A.VOUCHERDETAILID = F.VOUCHERDETAILID
            INNER JOIN TVOUCHER G ON F.VOUCHERID = G.VOUCHERID
            LEFT JOIN MPRICECATEGORY B ON A.PRICECATEGORYID = B.PRICECATEGORYID
            LEFT JOIN MEMPLOYEE C ON A.INCHARGEID = C.EMPLOYEEID
            LEFT JOIN TBILLALLOCATION D ON A.ALLOCATIONLINEID = D.BILLALLOCATIONLINEID
            LEFT JOIN MCURRENCY E ON A.CURRENCYID = E.CURRENCYID
            LEFT JOIN MROUTE RT ON A.ROUTEID = RT.ROUTEID
            WHERE G.VOUCHERID = @VoucherId;";

        // Same shape as GET_VOUCHER_BILL_ALLOCATION_FOR_VOUCHER above, but scoped directly by
        // VoucherDetailId instead of a whole voucher — used only for the cross-OU/inter-voucher
        // stitching case (GetVoucher's InterVoucherId handling), which needs the OTHER voucher's
        // single line's bill allocations, not the current voucher's.
        public const string GET_VOUCHER_BILL_ALLOCATION_FOR_DETAIL = @"
            SELECT
                A.BILLALLOCATIONLINEID AS VoucherBillAllocationId,
                A.VOUCHERDETAILID AS VoucherLineId,
                A.PRICECATEGORYID AS PriceCategoryId,
                B.PRICECATEGORYCODE AS PriceCategoryCode,
                B.PRICECATEGORYNAME AS PriceCategoryName,
                A.SLNO AS VoucherBillAllocationSlno,
                A.ALLOCATIONTYPE AS VoucherBillAllocationType,
                A.REFERENCENUMBER AS VoucherBillAllocationReferenceNumber,
                A.REFERENCEDATE AS VoucherBillAllocationReferenceDate,
                A.DUEDATE AS VoucherBillAllocationDueDate,
                A.PLANDATE AS VoucherBillAllocationPlanDate,
                A.INCHARGEID AS InChargeId,
                C.EMPLOYEECODE AS InChargeCode,
                C.EMPLOYEENAME AS InChargeName,
                A.STATUS AS VoucherBillAllocationStatus,
                A.FULLAMOUNT AS VoucherBillAllocationFullAmount,
                A.ALLOCATIONLINEID AS AllocationLineId,
                D.DETAILNUMBER AS AllocationLineDetailNumber,
                A.ALLOCATEDAMOUNT AS VoucherBillAllocationAllocatedAmount,
                A.CURRENCYID AS CurrencyId,
                E.CURRENCYCODE AS CurrencyCode,
                E.CURRENCYNAME AS CurrencyName,
                A.DETAILNUMBER AS VoucherBillAllocationDetailNumber,
                A.DETAILDATE AS VoucherBillAllocationDetailDate,
                A.DUEDAYS AS VoucherBillAllocationDueDays,
                A.DUETYPE AS VoucherBillAllocationDueType,
                A.ROUTEID AS RouteId,
                RT.ROUTENAME AS RouteName,
                A.FULLAMOUNTFC AS VoucherBillAllocationFullAmountFC,
                A.ALLOCATEDAMOUNTFC AS VoucherBillAllocationAllocatedAmountFC,
                A.CURRENCYCONVERSION AS VoucherBillAllocationCurrencyConversion
            FROM TBILLALLOCATION A
            LEFT JOIN MPRICECATEGORY B ON A.PRICECATEGORYID = B.PRICECATEGORYID
            LEFT JOIN MEMPLOYEE C ON A.INCHARGEID = C.EMPLOYEEID
            LEFT JOIN TBILLALLOCATION D ON A.ALLOCATIONLINEID = D.BILLALLOCATIONLINEID
            LEFT JOIN MCURRENCY E ON A.CURRENCYID = E.CURRENCYID
            LEFT JOIN MROUTE RT ON A.ROUTEID = RT.ROUTEID
            WHERE A.VOUCHERDETAILID = @VoucherDetailId;";

        public const string GET_VOUCHER_COST_CENTER_FOR_VOUCHER = @"
            SELECT
                A.VOUCHERCOSTCENTERID AS VoucherCostCenterId,
                A.VOUCHERDETAILID AS VoucherLineId,
                A.COSTCATEGORYSLNO AS VoucherCostCenterCostCategorySlno,
                A.COSTCATEGORYID AS CostCategoryId,
                B.COSTCATEGORYCODE AS CostCategoryCode,
                B.COSTCATEGORYNAME AS CostCategoryName,
                A.SLNO AS VoucherCostCenterSlno,
                A.COSTCENTERID AS CostCenterId,
                C.COSTCENTERCODE AS CostCenterCode,
                C.COSTCENTERNAME AS CostCenterName,
                A.ALLOCATEDAMOUNT AS VoucherCostCenterAllocatedAmount
            FROM TVOUCHERCOSTCENTER A
            INNER JOIN TVOUCHERDETAIL D ON A.VOUCHERDETAILID = D.VOUCHERDETAILID
            INNER JOIN TVOUCHER E ON D.VOUCHERID = E.VOUCHERID
            LEFT JOIN MCOSTCATEGORY B ON A.COSTCATEGORYID = B.COSTCATEGORYID
            LEFT JOIN MCOSTCENTER C ON A.COSTCENTERID = C.COSTCENTERID
            WHERE E.VOUCHERID = @VoucherId;";

        public const string GET_VOUCHER_INSTRUMENT_FOR_VOUCHER = @"
            SELECT
                A.VOUCHERDETAILID AS VoucherLineId,
                A.SLNO AS VoucherInstrumentSlno,
                A.INSTRUMENTID AS InstrumentId,
                B.INSTRUMENTNAME AS InstrumentName,
                A.INSTRUMENTNUMBER AS VoucherInstrumentNumber,
                A.INSTRUMENTDATE AS VoucherInstrumentDate,
                A.DRAWNONBANK AS VoucherInstrumentDrawnOnBank,
                A.DRAWNONBRANCH AS VoucherInstrumentDrawnOnBranch,
                A.INSTRUMENTFCAMOUNT AS VoucherInstrumentFCAmount,
                A.CURRENCYCONVERSION AS VoucherInstrumentCurrencyConversion,
                A.INSTRUMENTAMOUNT AS VoucherInstrumentAmount,
                A.PARTICULARS AS VoucherInstrumentParticulars,
                A.INSTRUMENTSTATUS AS VoucherInstrumentStatus,
                A.DEPOSITID AS VoucherInstrumentDepositId,
                A.DEPOSITDATE AS VoucherInstrumentDepositDate,
                A.REALISEDDATE AS VoucherInstrumentRealisedDate,
                A.BOUNCEDDATE AS VoucherInstrumentBouncedDate,
                A.CANCELDATE AS VoucherInstrumentCancelDate,
                A.SUBACCOUNTID AS SubAccountId,
                C.ACCOUNTCODE AS SubAccountCode,
                C.ACCOUNTNAME AS SubAccountName,
                A.BANKACCOUNTID AS BankAccountId,
                D.ACCOUNTCODE AS BankAccountCode,
                D.ACCOUNTNAME AS BankAccountName,
                A.ACCOUNTID AS AccountId,
                E.ACCOUNTCODE AS AccountCode,
                E.ACCOUNTNAME AS AccountName,
                A.ISACCOUNTPOSTED AS VoucherInstrumentIsAccountPosted,
                A.ACCOUNTVOUCHERID AS VoucherInstrumentAccountVoucherId,
                A.NOOFORIGINALCOPY AS VoucherInstrumentNoOfOriginalCopy,
                A.NOOFCOPYTAKEN AS VoucherInstrumentNoOfCopyTaken,
                A.CURRENCYID AS CurrencyId,
                F.CURRENCYCODE AS CurrencyCode,
                F.CURRENCYNAME AS CurrencyName
            FROM TINSTRUMENT A
            INNER JOIN TVOUCHERDETAIL G ON A.VOUCHERDETAILID = G.VOUCHERDETAILID
            INNER JOIN TVOUCHER H ON G.VOUCHERID = H.VOUCHERID
            LEFT JOIN MINSTRUMENT B ON A.INSTRUMENTID = B.INSTRUMENTID
            LEFT JOIN MACCOUNT C ON A.SUBACCOUNTID = C.ACCOUNTID
            LEFT JOIN MACCOUNT D ON A.BANKACCOUNTID = D.ACCOUNTID
            LEFT JOIN MACCOUNT E ON A.ACCOUNTID = E.ACCOUNTID
            LEFT JOIN MCURRENCY F ON A.CURRENCYID = F.CURRENCYID
            WHERE G.VOUCHERID = @VoucherId;";

        // Distinct from GET_VOUCHER_TDS above (that one is the unrelated GetVoucherTDS picklist
        // operation, filtered by AccountId, never by VoucherId). This is GB4's ACCOUNT_TDSVOUCHER_GET
        // — every TDS row belonging to this voucher's lines, matched into VoucherDetailDTO.VoucherTDSArray
        // by VoucherLineId in the BLL.
        public const string GET_VOUCHER_TDS_FOR_VOUCHER = @"
            SELECT
                A.VOUCHERTDSID AS VoucherTDSId,
                A.VOUCHERLINEID AS VoucherLineId,
                A.TDSSLNO AS VoucherTDSTdsSlNo,
                A.TDSNATURE AS VoucherTDSTDSNature,
                A.TDSTYPE AS VoucherTDSTDSType,
                A.ACCOUNTID AS AccountId,
                B.ACCOUNTCODE AS AccountCode,
                B.ACCOUNTNAME AS AccountName,
                A.REFERENCENUMBER AS VoucherTDSReferenceNumber,
                A.REFERENCEDATE AS VoucherTDSReferenceDate,
                A.CREDITDATE AS VoucherTDSCreditDate,
                A.PARTICULARS AS VoucherTDSParticulars,
                A.AMOUNT AS VoucherTDSAmount,
                A.TDSCATEGORYID AS TDSCategoryId,
                C.TDSCATEGORYCODE AS TDSCategoryCode,
                C.TDSCATEGORYNAME AS TDSCategoryName,
                A.SECTION AS VoucherTDSSection,
                A.REFERENCESECTION AS VoucherTDSReferenceSection,
                A.TDSPERCENT AS VoucherTDSTDSPercent,
                A.TDSAMOUNT AS VoucherTDSTDSAmount,
                A.SCPERCENT AS VoucherTDSScPercent,
                A.SCAMOUNT AS VoucherTDSScAmount,
                A.CESSPERCENT AS VoucherTDSCessPercent,
                A.CESSAMOUNT AS VoucherTDSCessAmount,
                A.INTERESTAMOUNT AS VoucherTDSInterestAmount,
                A.PENALTYAMOUNT AS VoucherTDSPenaltyAmount,
                A.OTHERAMOUNT AS VoucherTDSOtherAmount,
                A.TOTALAMOUNT AS VoucherTDSTotalAmount,
                A.ROUNDEDAMOUNT AS VoucherTDSRoundedAmount,
                A.TYPEOFDEDUCTION AS VoucherTDSTypeOfDeduction,
                A.LESSDEDUCTIONTYPE AS VoucherTDSLessDeductionType,
                A.LESSDEDUCTIONREASON AS VoucherTDSLessDeductionReason,
                A.TDSSTATUS AS VoucherTDSTDSStatus,
                A.ALLOTEDVOUCHERLINEID AS AllotedVoucherLineId,
                A.PAIDTDSAMOUNT AS VoucherTDSPaidTDSAmount,
                A.PAIDSCAMOUNT AS VoucherTDSPaidScAmount,
                A.PAIDCESSAMOUNT AS VoucherTDSPaidCessAmount,
                A.CERTIFICATENUMBER AS VoucherTDSCertificateNumber,
                A.CERTIFICATEDATE AS VoucherTDSCertificateDate,
                A.REMARKS AS VoucherTDSRemarks,
                A.MODIFIEDON AS VoucherTDSModifiedOn
            FROM TVOUCHERTDS A
            INNER JOIN TVOUCHERDETAIL D ON A.VOUCHERLINEID = D.VOUCHERDETAILID
            INNER JOIN TVOUCHER E ON D.VOUCHERID = E.VOUCHERID
            LEFT JOIN MACCOUNT B ON A.ACCOUNTID = B.ACCOUNTID
            LEFT JOIN MTDSCATEGORY C ON A.TDSCATEGORYID = C.TDSCATEGORYID
            WHERE D.VOUCHERID = @VoucherId;";

        // Ported from GB4's GET_VOUCHER_DETAIL_PARAMETER (raw HQL against TPARAMETERDETAIL, object
        // type sentinel -1899999138 = VoucherDetail). Matched into VoucherDetailDTO.VoucherDetailParameterArray
        // by ObjectId == VoucherDetailId in the BLL.
        public const string GET_VOUCHER_DETAIL_PARAMETER = @"
            SELECT
                A.PARAMETERDETAILID AS ParamterSetId,
                B.BIZTRANSACTIONTYPEID AS BizTransactionTypeId,
                B.BIZTRANSACTIONTYPECODE AS BizTransactionTypeCode,
                B.BIZTRANSACTIONTYPENAME AS BizTransactionTypeName,
                A.OBJECTTYPEID AS ObjectTypeId,
                A.OBJECTID AS ObjectId,
                C.PARAMETERSETID AS ParameterSetId,
                C.PARAMETERSETCODE AS ParameterSetCode,
                C.PARAMETERSETNAME AS ParameterSetName,
                A.SLNO AS SlNo,
                D.PARAMETERID AS ParameterId,
                D.PARAMETERCODE AS ParameterCode,
                D.PARAMETERNAME AS ParameterName,
                A.INPUTVALUE AS InputValue,
                A.INPUTTEXT AS InputText,
                A.FINALVALUE AS FinalValue,
                A.INPUTID AS InputId,
                D.MINVALUE AS MinValue,
                D.MAXVALUE AS MaxValue
            FROM TPARAMETERDETAIL A
            INNER JOIN MBIZTRANSACTIONTYPE B ON A.BIZTRANSACTIONTYPEID = B.BIZTRANSACTIONTYPEID
            INNER JOIN MPARAMETERSET C ON A.PARAMETERSETID = C.PARAMETERSETID
            INNER JOIN MPARAMETER D ON A.PARAMETERID = D.PARAMETERID
            WHERE A.OBJECTTYPEID = -1899999138
              AND A.OBJECTID IN (SELECT VOUCHERDETAILID FROM TVOUCHERDETAIL WHERE VOUCHERID = @VoucherId);";

        // Ported from GB4's GET_VOUCHER_BIZ_TYPE_COST_CENTER_REQUIRED — a per-voucher (not
        // per-line) count: 1 when the voucher's BizTransactionType explicitly disables split-cost-
        // center/multiple-cost-category/cost-center all at once, 0 otherwise. Feeds the
        // cost-center-applicability rule in VoucherBLL.GetVoucher.
        public const string GET_VOUCHER_BIZ_TYPE_COST_CENTER_REQUIRED_COUNT = @"
            SELECT COUNT(A.VOUCHERID)
            FROM TVOUCHER A
            INNER JOIN MBIZTRANSACTIONTYPE B ON A.BIZTRANSACTIONTYPEID = B.BIZTRANSACTIONTYPEID
            WHERE A.VOUCHERID = @VoucherId
              AND B.ISSPLITCOSTCENTER = 0
              AND B.ISMULTIPLECOSTCATEGORY = 0
              AND B.ISCOSTCENTER = 0;";

        // Deliberate perf improvement over GB4's per-detail-line CostCategoryCondition lookup
        // (GenericLazy.GetCondition inside the per-line loop, i.e. N+1): fetch every AccountGroupId
        // that has at least one MCOSTCATEGORYCONDITION row ONCE per GetVoucher call, then check
        // membership in-memory per line. Same result as GB4's per-line query, batched.
        public const string GET_COST_CATEGORY_CONDITION_ACCOUNT_GROUP_IDS = @"
            SELECT DISTINCT ACCOUNTGROUPID FROM MCOSTCATEGORYCONDITION;";

        // All TDS categories' 4 linked account ids, for the read-time TDS-applicability recompute
        // in VoucherBLL.GetVoucher (GB4 fetches this list once via TDSCategoryDAL.GetTDSCategory
        // and matches in-memory per line — same shape here).
        public const string GET_ALL_TDS_CATEGORY_ACCOUNTS = @"
            SELECT
                TDSCATEGORYID AS TDSCategoryId,
                DEDUCTIONACCOUNTID AS DeducationAccountId,
                PAIDACCOUNTID AS PaidAccountId,
                SUFFEREDACCOUNTID AS SufferedAccountId,
                CLAIMACCOUNTID AS ClaimAccountId
            FROM MTDSCATEGORY;";

        // Parameterized replacement for GB4's ad-hoc string-concatenated HQL
        // ("select VOUCHERDETAILID ... where VOUCHERACCOUNTID=... and VOUCHERID=... and
        // VOUCHERAMOUNT=...") used only for the cross-OU/inter-voucher bill-allocation stitching
        // case in VoucherBLL.GetVoucher.
        public const string GET_INTER_VOUCHER_DETAIL_ID = @"
            SELECT VOUCHERDETAILID
            FROM TVOUCHERDETAIL
            WHERE VOUCHERACCOUNTID = @InterAccountId
              AND VOUCHERID = @InterVoucherId
              AND VOUCHERAMOUNT = @VoucherDetailAmount;";

        // Account lookup for the cross-OU stitching swap (GB4's GenericLazy.Get<Account> call) —
        // needs Code/Name/Type/BillAllocationType/AccountGroupId/AccountScheduleNature/IsTDSApplicable,
        // the same fields VoucherBLL.GetVoucher copies onto the swapped detail line.
        public const string GET_ACCOUNT_FOR_INTER_SWAP = @"
            SELECT
                A.ACCOUNTID AS AccountId,
                A.ACCOUNTCODE AS AccountCode,
                A.ACCOUNTNAME AS AccountName,
                A.ACCOUNTTYPE AS AccountType,
                A.BILLALLOCATIONTYPE AS BillAllocationType,
                A.ACCOUNTGROUPID AS AccountGroupId,
                C.ACCOUNTSCHEDULENATURE AS AccountScheduleNature,
                A.ISTDSAPPLICABLE AS IsTDSApplicable
            FROM MACCOUNT A
            LEFT JOIN MACCOUNTGROUP B ON A.ACCOUNTGROUPID = B.ACCOUNTGROUPID
            LEFT JOIN MACCOUNTSCHEDULE C ON B.ACCOUNTSCHEDULEID = C.ACCOUNTSCHEDULEID
            WHERE A.ACCOUNTID = @AccountId;";

        // Matches legacy GB4 GetVoucherTDS's dynamic-criteria HQL (filters only by AccountId/
        // VoucherTDSId — never by VoucherId), plus its always-on exclusion of already-allotted TDS
        // entries (AllotedVoucherLine.Id = -1). Confirmed live schema (TVOUCHERTDS has no TENANTID
        // column; the true FK to TVOUCHERDETAIL is VOUCHERLINEID, not VOUCHERDETAILID).
        public const string GET_VOUCHER_TDS = @"
            SELECT
                T.VOUCHERTDSID               AS VoucherTDSId,
                T.VOUCHERLINEID              AS VoucherLineId,
                T.TDSSLNO                    AS VoucherTDSTdsSlNo,
                T.TDSNATURE                  AS VoucherTDSTDSNature,
                T.TDSTYPE                    AS VoucherTDSTDSType,
                T.ACCOUNTID                  AS AccountId,
                ISNULL(A.ACCOUNTCODE,'NONE') AS AccountCode,
                ISNULL(A.ACCOUNTNAME,'NONE') AS AccountName,
                T.REFERENCENUMBER            AS VoucherTDSReferenceNumber,
                T.REFERENCEDATE              AS VoucherTDSReferenceDate,
                T.CREDITDATE                 AS VoucherTDSCreditDate,
                T.PARTICULARS                AS VoucherTDSParticulars,
                T.AMOUNT                     AS VoucherTDSAmount,
                T.TDSCATEGORYID              AS TDSCategoryId,
                ISNULL(C.TDSCATEGORYCODE,'NONE') AS TDSCategoryCode,
                ISNULL(C.TDSCATEGORYNAME,'NONE') AS TDSCategoryName,
                T.SECTION                    AS VoucherTDSSection,
                T.REFERENCESECTION           AS VoucherTDSReferenceSection,
                T.TDSPERCENT                 AS VoucherTDSTDSPercent,
                T.TDSAMOUNT                  AS VoucherTDSTDSAmount,
                T.SCPERCENT                  AS VoucherTDSScPercent,
                T.SCAMOUNT                   AS VoucherTDSScAmount,
                T.CESSPERCENT                AS VoucherTDSCessPercent,
                T.CESSAMOUNT                 AS VoucherTDSCessAmount,
                T.INTERESTAMOUNT             AS VoucherTDSInterestAmount,
                T.PENALTYAMOUNT              AS VoucherTDSPenaltyAmount,
                T.OTHERAMOUNT                AS VoucherTDSOtherAmount,
                T.TOTALAMOUNT                AS VoucherTDSTotalAmount,
                T.ROUNDEDAMOUNT              AS VoucherTDSRoundedAmount,
                T.TYPEOFDEDUCTION            AS VoucherTDSTypeOfDeduction,
                T.LESSDEDUCTIONTYPE          AS VoucherTDSLessDeductionType,
                T.LESSDEDUCTIONREASON        AS VoucherTDSLessDeductionReason,
                T.TDSSTATUS                  AS VoucherTDSTDSStatus,
                T.ALLOTEDVOUCHERLINEID       AS AllotedVoucherLineId,
                T.PAIDTDSAMOUNT              AS VoucherTDSPaidTDSAmount,
                T.PAIDSCAMOUNT               AS VoucherTDSPaidScAmount,
                T.PAIDCESSAMOUNT             AS VoucherTDSPaidCessAmount,
                T.CERTIFICATENUMBER          AS VoucherTDSCertificateNumber,
                T.CERTIFICATEDATE            AS VoucherTDSCertificateDate,
                T.REMARKS                    AS VoucherTDSRemarks,
                T.MODIFIEDON                 AS VoucherTDSModifiedOn
            FROM TVOUCHERTDS T
            LEFT JOIN MACCOUNT A ON A.ACCOUNTID = T.ACCOUNTID
            LEFT JOIN MTDSCATEGORY C ON C.TDSCATEGORYID = T.TDSCATEGORYID
            WHERE T.ALLOTEDVOUCHERLINEID = -1
            AND (@AccountId = 0 OR T.ACCOUNTID = @AccountId)
            AND (@VoucherTDSId = 0 OR T.VOUCHERTDSID = @VoucherTDSId)";

        // Matches legacy GB4 GetSelectListVoucherTDS's raw-SQL query exactly (VoucherQueryBuilder.cs,
        // GET_SELECT_LIST_VOUCHERTDS) — a lightweight pick-list (Id/ReferenceNumber/ReferenceDate),
        // filtered by AccountId/Number(ReferenceNumber). Its exclusion logic differs from GET_VOUCHER_TDS's
        // simple "ALLOTEDVOUCHERLINEID = -1": it hides BOTH sides of any TDS-to-TDS allotment pairing
        // (i.e. a row that allotted against another TDS entry, AND the entry it was allotted against),
        // not just rows that have themselves been consumed. GB4's DTO also declares Amount/PaidAmount
        // fields that its own live query never populates (dead leftovers from an earlier discarded
        // version) — GB5 populates them here since the source columns (AMOUNT/PAIDTDSAMOUNT) are real
        // and already selected by GET_VOUCHER_TDS, so leaving them dead would be a pointless regression.
        public const string GET_SELECT_LIST_VOUCHERTDS = @"
            SELECT DISTINCT
                T.VOUCHERTDSID               AS VoucherTDSId,
                T.REFERENCENUMBER            AS VoucherTDSReferenceNumber,
                T.REFERENCEDATE              AS VoucherTDSReferenceDate,
                T.AMOUNT                     AS VoucherTDSAmount,
                T.PAIDTDSAMOUNT              AS VoucherTDSPaidAmount
            FROM TVOUCHERTDS T
            WHERE T.VOUCHERTDSID NOT IN (
                SELECT X.VOUCHERTDSID
                FROM TVOUCHERTDS X
                JOIN (
                    SELECT B.VOUCHERTDSID, B.ALLOTEDVOUCHERLINEID
                    FROM TVOUCHERTDS A
                    JOIN TVOUCHERTDS B ON A.VOUCHERTDSID = B.ALLOTEDVOUCHERLINEID
                    WHERE A.VOUCHERTDSID <> -1
                ) SUB ON X.VOUCHERTDSID = SUB.VOUCHERTDSID OR X.VOUCHERTDSID = SUB.ALLOTEDVOUCHERLINEID
            )
            AND (@AccountId = 0 OR T.ACCOUNTID = @AccountId)
            AND (@Number = '' OR T.REFERENCENUMBER LIKE '%' + @Number + '%')";

        public const string GET_BILL_ALLOCATION = @"
            SELECT
                B.BILLALLOCATIONLINEID       AS VoucherBillAllocationId,
                B.VOUCHERDETAILID            AS VoucherLineId,
                B.SLNO                       AS VoucherBillAllocationSlNo,
                B.ALLOCATIONTYPE             AS VoucherBillAllocationType,
                B.REFERENCENUMBER            AS VoucherBillAllocationReferenceNumber,
                B.REFERENCEDATE              AS VoucherBillAllocationReferenceDate,
                B.DUEDATE                    AS VoucherBillAllocationDueDate,
                B.FULLAMOUNT                 AS VoucherBillAllocationFullAmount,
                B.ALLOCATEDAMOUNT            AS VoucherBillAllocationAllocatedAmount,
                B.STATUS                     AS VoucherBillAllocationStatus,
                B.TENANTID                   AS TenantId
            FROM TBILLALLOCATION B
            WHERE B.VOUCHERDETAILID IN (
                SELECT D.VOUCHERDETAILID FROM TVOUCHERDETAIL D
                WHERE D.VOUCHERID = @VoucherId AND D.TENANTID = @TenantId
            )
            AND B.TENANTID = @TenantId";

        // Manual bill-allocation listing screen — feeds VoucherPendingBillAllocationDTO, mirroring
        // legacy GB4's own GET_PENDING_BILL_ALLOCATION HQL field-for-field:
        //  - FullAmount comes from FULLAMOUNTFC (foreign-currency amount), not the base FULLAMOUNT.
        //  - AllocatedAmount is deliberately PB.BALANCE (the pending amount), not B.ALLOCATEDAMOUNT —
        //    GB4's own HQL does the exact same repurposing here, same as the Auto endpoint.
        //  - CurrencyConversion/DetailType/RouteId/RouteName are flat (no "VoucherBillAllocation"
        //    prefix), matching the legacy DTO's own naming.
        // Filter set, VPENDINGBILLDETAIL join, and D.VOUCHERACCOUNTID column mirror the
        // confirmed-working GET_SELECTLIST_BILL_ALLOCATION query further below — TENANTID
        // intentionally omitted since TBILLALLOCATION/TVOUCHERDETAIL don't have that column
        // (confirmed at runtime).
        public const string GET_PENDING_BILL_ALLOCATION = @"
            SELECT
                B.REFERENCENUMBER            AS VoucherBillAllocationReferenceNumber,
                B.REFERENCEDATE              AS VoucherBillAllocationReferenceDate,
                B.DUEDATE                    AS VoucherBillAllocationDueDate,
                B.PLANDATE                   AS VoucherBillAllocationPlanDate,
                B.PRICECATEGORYID            AS PriceCategoryId,
                PC.PRICECATEGORYCODE         AS PriceCategoryCode,
                PC.PRICECATEGORYNAME         AS PriceCategoryName,
                B.INCHARGEID                 AS InChargeId,
                E.EMPLOYEECODE               AS InChargeCode,
                E.EMPLOYEENAME               AS InChargeName,
                B.CURRENCYID                 AS CurrencyId,
                C.CURRENCYCODE               AS CurrencyCode,
                C.CURRENCYNAME               AS CurrencyName,
                B.FULLAMOUNTFC               AS VoucherBillAllocationFullAmount,
                PB.BALANCE                   AS VoucherBillAllocationAllocatedAmount,
                B.DETAILNUMBER               AS VoucherBillAllocationDetailNumber,
                B.DETAILDATE                 AS VoucherBillAllocationDetailDate,
                B.DUEDAYS                    AS VoucherBillAllocationDueDays,
                B.DUETYPE                    AS VoucherBillAllocationDueType,
                D.DETAILTYPE                 AS DetailType,
                B.ROUTEID                    AS RouteId,
                R.ROUTENAME                  AS RouteName,
                B.CURRENCYCONVERSION         AS CurrencyConversion
            FROM TBILLALLOCATION B
            INNER JOIN TVOUCHERDETAIL D ON B.VOUCHERDETAILID = D.VOUCHERDETAILID
            INNER JOIN TVOUCHER V ON D.VOUCHERID = V.VOUCHERID
            INNER JOIN VPENDINGBILLDETAIL PB ON PB.ALLOCATIONLINEID = B.BILLALLOCATIONLINEID
            LEFT JOIN MPRICECATEGORY PC ON B.PRICECATEGORYID = PC.PRICECATEGORYID
            LEFT JOIN MEMPLOYEE E ON B.INCHARGEID = E.EMPLOYEEID
            LEFT JOIN MCURRENCY C ON B.CURRENCYID = C.CURRENCYID
            LEFT JOIN MROUTE R ON B.ROUTEID = R.ROUTEID
            WHERE 1=1
              AND (@AccountId = 0 OR D.VOUCHERACCOUNTID = @AccountId)
              AND (@SubAccountId = 0 OR D.SUBACCOUNTID = @SubAccountId)
              AND (@OuId = 0 OR V.OUID = @OuId)
              AND (@VoucherDate IS NULL OR CAST(V.VOUCHERDATE AS DATE) = @VoucherDate)
              AND (@VoucherBillAllocationId = 0 OR B.BILLALLOCATIONLINEID = @VoucherBillAllocationId)
              AND (@DetailType = 99 OR D.DETAILTYPE = @DetailType)
            ORDER BY B.REFERENCEDATE ASC";

        // Separate from GET_PENDING_BILL_ALLOCATION (used by the manual bill-allocation screen,
        // which needs VoucherBillAllocationAllocatedAmount to mean the true already-allocated
        // amount) — this one feeds VoucherBLL's auto-allocation loop, which needs
        // VoucherBillAllocationAllocatedAmount to carry the bill's PENDING (unallocated) amount
        // instead, exactly like legacy GB4's own GET_AUTO_PENDING_BILL_ALLOCATION HQL does
        // ("abs(pendingbillview.Balance) as VoucherBillAllocationAllocatedAmount").
        // Filter set, VPENDINGBILLDETAIL join, and D.VOUCHERACCOUNTID column all mirror the
        // already-working GET_SELECTLIST_BILL_ALLOCATION query above — confirmed ground truth for
        // this table's real column names and BillAllocationFilterDTO's filter fields.
        // ORDER BY ReferenceDate ASC is the FIFO ordering the auto-allocation loop depends on
        // (oldest bill paid off first), matching GB4 exactly.
        public const string GET_AUTO_PENDING_BILL_ALLOCATION = @"
            SELECT
                B.BILLALLOCATIONLINEID       AS VoucherBillAllocationId,
                B.VOUCHERDETAILID            AS VoucherLineId,
                B.ALLOCATIONTYPE             AS VoucherBillAllocationType,
                B.SLNO                       AS VoucherBillAllocationSlno,
                B.REFERENCENUMBER            AS VoucherBillAllocationReferenceNumber,
                B.REFERENCEDATE              AS VoucherBillAllocationReferenceDate,
                B.DUEDATE                    AS VoucherBillAllocationDueDate,
                B.DETAILNUMBER               AS VoucherBillAllocationDetailNumber,
                B.FULLAMOUNT                 AS VoucherBillAllocationFullAmount,
                ABS(PB.BALANCE)              AS VoucherBillAllocationAllocatedAmount,
                B.STATUS                     AS VoucherBillAllocationStatus,
                B.PRICECATEGORYID            AS PriceCategoryId,
                PC.PRICECATEGORYCODE         AS PriceCategoryCode,
                PC.PRICECATEGORYNAME         AS PriceCategoryName,
                B.INCHARGEID                 AS InChargeId,
                E.EMPLOYEECODE               AS InChargeCode,
                E.EMPLOYEENAME               AS InChargeName,
                B.CURRENCYID                 AS CurrencyId,
                C.CURRENCYCODE               AS CurrencyCode,
                C.CURRENCYNAME               AS CurrencyName,
                V.VOUCHERNARRATION           AS VoucherNarration
            FROM TBILLALLOCATION B
            INNER JOIN TVOUCHERDETAIL D ON B.VOUCHERDETAILID = D.VOUCHERDETAILID
            INNER JOIN TVOUCHER V ON D.VOUCHERID = V.VOUCHERID
            INNER JOIN VPENDINGBILLDETAIL PB ON PB.ALLOCATIONLINEID = B.BILLALLOCATIONLINEID
            LEFT JOIN MPRICECATEGORY PC ON B.PRICECATEGORYID = PC.PRICECATEGORYID
            LEFT JOIN MEMPLOYEE E ON B.INCHARGEID = E.EMPLOYEEID
            LEFT JOIN MCURRENCY C ON B.CURRENCYID = C.CURRENCYID
            WHERE 1=1
              AND (@AccountId = 0 OR D.VOUCHERACCOUNTID = @AccountId)
              AND (@SubAccountId = 0 OR D.SUBACCOUNTID = @SubAccountId)
              AND (@OuId = 0 OR V.OUID = @OuId)
              AND (@VoucherDate IS NULL OR CAST(V.VOUCHERDATE AS DATE) = @VoucherDate)
              AND (@VoucherBillAllocationId = 0 OR B.BILLALLOCATIONLINEID = @VoucherBillAllocationId)
              AND (@DetailType = 99 OR D.DETAILTYPE = @DetailType)
            ORDER BY B.REFERENCEDATE ASC";

        public const string UPDATE_BILL_ALLOCATION = @"
            UPDATE TBILLALLOCATION
            SET ALLOCATEDAMOUNT = @AllocatedAmount,
                STATUS          = @Status,
                MODIFIEDBYID    = @ModifiedById,
                MODIFIEDON      = @ModifiedOn
            WHERE BILLALLOCATIONLINEID = @BillAllocationLineId
              AND TENANTID             = @TenantId";

        // Legacy GB4 VoucherBLL/DAL.BRSLoad, ported faithfully: joins TINSTRUMENT (voucherinstrument)
        // to MINSTRUMENT (instrumnet, for InstrumentType), TVOUCHERDETAIL/TVOUCHER (for
        // DetailType/VoucherNumber/Narration/CreatedBy), MBIZTRANSACTIONTYPE, and MACCOUNT (twice —
        // once for the bank account, once for the party account) plus MBANKBRANCH for the party's
        // bank branch name. Table/column names confirmed against GB5's own existing usage
        // (AccountQB.cs, InstrumentQB.cs, BIZTransactionTypeQB.cs) and a live schema check of
        // TINSTRUMENT. Comma-list filters (BankId/AccountId/DepositId/InstrumentId/InstrumentType)
        // use comma-padded LIKE instead of STRING_SPLIT — some environments (QC) run a SQL Server
        // compat level where STRING_SPLIT is unavailable (see AccountQB/PartyBranchQB fix). Pagination
        // matches GB5's established ROW_NUMBER/BETWEEN convention; GB4's own two-call
        // (data + separate Count=true) pattern is preserved via GET_BRS_LOAD_COUNT below rather than
        // folding a windowed COUNT into this query, to keep the WHERE clause a single source of truth
        // shared by both (same approach GB4 itself uses — a second HQL run with the same filter).
        //
        // NOT fully replicated (flagged, not guessed): GB4's InstrumnetAmountInWords (per-deposit
        // amount-in-words) has no equivalent utility in GB5 yet — left as empty string. The more
        // obscure DayType/BRSStatus sub-branches and the full set of GB4's alternate sort-field
        // names are approximated via the 3 main Number/Date/Amount ascending/descending flags rather
        // than every individual GB4 sort variant.
        public const string GET_BRS_LOAD = @"
            WITH BRSBase AS
            (
                SELECT
                    I.INSTRUMENTLINEID          AS InstrumentId,
                    MI.INSTRUMENTTYPE           AS InstrumentType,
                    I.INSTRUMENTNUMBER          AS InstrumnetNumber,
                    I.INSTRUMENTDATE            AS InstrumentDate,
                    I.INSTRUMENTAMOUNT          AS InstrumnetAmount,
                    ABS(I.INSTRUMENTAMOUNT)     AS InstrumnetAbsAmount,
                    I.BANKACCOUNTID             AS BankId,
                    I.ACCOUNTID                 AS PartyId,
                    V.VOUCHERDATE               AS VoucherDate,
                    I.DRAWNONBANK               AS InstrumentDrawnonBank,
                    I.DRAWNONBRANCH             AS InstrumentDrawnonBranch,
                    BTT.NOGENERATIONTYPE        AS NumberGenerationType,
                    BTT.BIZTRANSACTIONTYPECODE  AS VoucherCode,
                    BTT.BIZTRANSACTIONTYPENAME  AS VoucherName,
                    V.VOUCHERNUMBER             AS VoucherNumber,
                    I.DEPOSITDATE               AS DepositDate,
                    I.INSTRUMENTSTATUS          AS InstrumentStatus,
                    I.REALISEDDATE              AS RealisedDate,
                    I.BOUNCEDDATE               AS BouncedDate,
                    I.CANCELDATE                AS CancelDate,
                    I.DEPOSITID                 AS DepositId,
                    I.DEPOSITNUMBER             AS DepositNumber,
                    I.ISACCOUNTPOSTED           AS IsAccountPosted,
                    MI.INSTRUMENTID             AS TempInstrumentId,
                    V.VOUCHERID                 AS TempVoucherId,
                    V.VOUCHERNARRATION          AS VoucherNarration,
                    V.CREATEDBYID               AS CreatedById,
                    V.STATUS                    AS VoucherStatusRaw,
                    D.DETAILTYPE                AS DetailTypeRaw
                FROM TINSTRUMENT I
                INNER JOIN MINSTRUMENT     MI  ON I.INSTRUMENTID = MI.INSTRUMENTID
                INNER JOIN TVOUCHERDETAIL  D   ON I.VOUCHERDETAILID = D.VOUCHERDETAILID
                INNER JOIN TVOUCHER        V   ON D.VOUCHERID = V.VOUCHERID
                INNER JOIN MBIZTRANSACTIONTYPE BTT ON V.BIZTRANSACTIONTYPEID = BTT.BIZTRANSACTIONTYPEID
                WHERE MI.INSTRUMENTTYPE <> 0
                  AND (@BankIds = '' OR ',' + @BankIds + ',' LIKE '%,' + CAST(I.BANKACCOUNTID AS VARCHAR(20)) + ',%')
                  AND (@AccountIds = '' OR ',' + @AccountIds + ',' LIKE '%,' + CAST(I.ACCOUNTID AS VARCHAR(20)) + ',%')
                  AND (@DepositIds = '' OR ',' + @DepositIds + ',' LIKE '%,' + CAST(ISNULL(I.DEPOSITID,-1) AS VARCHAR(20)) + ',%')
                  AND (@InstrumentLineIds = '' OR ',' + @InstrumentLineIds + ',' LIKE '%,' + CAST(I.INSTRUMENTLINEID AS VARCHAR(20)) + ',%')
                  AND (@InstrumentTypes = '' OR ',' + @InstrumentTypes + ',' LIKE '%,' + CAST(MI.INSTRUMENTTYPE AS VARCHAR(20)) + ',%')
                  AND (@ChequeType = -1 OR D.DETAILTYPE = (CASE WHEN @ChequeType = 0 THEN 1 WHEN @ChequeType = 1 THEN 0 END))
                  AND (@FromDate IS NULL OR I.INSTRUMENTDATE >= @FromDate)
                  AND (@ToDate IS NULL OR I.INSTRUMENTDATE <= @ToDate)
                  AND (@InstrumentStatus = -1 OR I.INSTRUMENTSTATUS = @InstrumentStatus)
                  AND (@VoucherNarration = '' OR V.VOUCHERNARRATION LIKE '%' + @VoucherNarration + '%')
                  AND (@OuId = 0 OR V.OUID = @OuId)
            ),
            BRSNumbered AS
            (
            SELECT
                B.InstrumentId, B.InstrumentType, B.InstrumnetNumber, B.InstrumentDate,
                B.InstrumnetAmount, B.InstrumnetAbsAmount,
                B.BankId, ISNULL(BA.ACCOUNTCODE,'NONE') AS BankCode, ISNULL(BA.ACCOUNTNAME,'NONE') AS BankName,
                B.PartyId, ISNULL(PA.ACCOUNTCODE,'NONE') AS PartyCode, ISNULL(PA.ACCOUNTNAME,'NONE') AS PartyName,
                B.VoucherDate, B.InstrumentDrawnonBank, B.InstrumentDrawnonBranch, B.NumberGenerationType,
                ISNULL(B.VoucherCode,'NONE') AS VoucherCode,
                ISNULL(B.VoucherName,'NONE') AS VoucherName,
                B.VoucherNumber, B.DepositDate, B.InstrumentStatus, B.RealisedDate, B.BouncedDate, B.CancelDate,
                ISNULL(PA.BANKACCOUNTNUMBER,'NONE') AS BankAccountNumber,
                ISNULL(PABB.BANKBRANCHNAME,'NONE') AS BankBranch,
                B.DepositId, B.DepositNumber, B.IsAccountPosted, B.TempInstrumentId, B.TempVoucherId,
                B.VoucherNarration, B.CreatedById,
                ISNULL(U.USERNAME,'NONE') AS EmployeeCode,
                ISNULL(U.USERNAME,'NONE') AS EmployeeName,
                '' AS InstrumnetAmountInWords,
                CASE B.VoucherStatusRaw WHEN 0 THEN 'Pending' WHEN 1 THEN 'Active' WHEN 2 THEN 'Deleted' WHEN 3 THEN 'Amended' WHEN 4 THEN 'InActive' WHEN 5 THEN 'Archived' ELSE 'Unknown' END AS VoucherStatus,
                CASE B.DetailTypeRaw WHEN 0 THEN 'Received' WHEN 1 THEN 'Issued' ELSE '' END AS DetailType,
                CASE WHEN B.DetailTypeRaw = 0 THEN B.InstrumnetAmount ELSE 0 END AS Debit,
                CASE WHEN B.DetailTypeRaw = 1 THEN B.InstrumnetAmount ELSE 0 END AS Credit,
                CASE B.InstrumentStatus WHEN 0 THEN 'Pending' WHEN 1 THEN 'Deposit' WHEN 2 THEN 'Realised' WHEN 3 THEN 'Bounced' WHEN 4 THEN 'Hold' WHEN 5 THEN 'Cancelled' ELSE 'Unknown' END AS InstrumentStatusName,
                SUM(B.InstrumnetAmount) OVER() AS TotalInstrumnetAmount,
                ROW_NUMBER() OVER (
                    ORDER BY
                        CASE WHEN @InstrumentNumberSortAscDesc = 1 THEN B.InstrumnetNumber END DESC,
                        CASE WHEN @InstrumentNumberSortAscDesc = 0 THEN B.InstrumnetNumber END ASC,
                        CASE WHEN @InstrumentDateSortAscDesc = 1 THEN B.InstrumentDate END DESC,
                        CASE WHEN @InstrumentDateSortAscDesc = 0 THEN B.InstrumentDate END ASC,
                        CASE WHEN @InstrumentAmountSortAscDesc = 1 THEN B.InstrumnetAmount END DESC,
                        CASE WHEN @InstrumentAmountSortAscDesc = 0 THEN B.InstrumnetAmount END ASC,
                        B.InstrumentStatus, BA.ACCOUNTNAME
                ) AS RowNum
            FROM BRSBase B
            LEFT JOIN MACCOUNT BA ON B.BankId = BA.ACCOUNTID
            LEFT JOIN MACCOUNT PA ON B.PartyId = PA.ACCOUNTID
            LEFT JOIN MBANKBRANCH PABB ON PA.ACCOUNTBANKBRANCHID = PABB.BANKBRANCHID
            LEFT JOIN MUSER U ON B.CreatedById = U.USERID
            )
            SELECT *
            FROM BRSNumbered
            WHERE RowNum BETWEEN @FirstNumber + 1 AND @FirstNumber + @MaxResult
            ORDER BY RowNum";

        // Same filter WHERE clause as GET_BRS_LOAD's BRSBase CTE (kept as a separate query rather
        // than a windowed COUNT so the two mirror GB4's own two-call Count=true/false design exactly).
        public const string GET_BRS_LOAD_COUNT = @"
            SELECT COUNT(1)
            FROM TINSTRUMENT I
            INNER JOIN MINSTRUMENT     MI  ON I.INSTRUMENTID = MI.INSTRUMENTID
            INNER JOIN TVOUCHERDETAIL  D   ON I.VOUCHERDETAILID = D.VOUCHERDETAILID
            INNER JOIN TVOUCHER        V   ON D.VOUCHERID = V.VOUCHERID
            INNER JOIN MBIZTRANSACTIONTYPE BTT ON V.BIZTRANSACTIONTYPEID = BTT.BIZTRANSACTIONTYPEID
            WHERE MI.INSTRUMENTTYPE <> 0
              AND (@BankIds = '' OR ',' + @BankIds + ',' LIKE '%,' + CAST(I.BANKACCOUNTID AS VARCHAR(20)) + ',%')
              AND (@AccountIds = '' OR ',' + @AccountIds + ',' LIKE '%,' + CAST(I.ACCOUNTID AS VARCHAR(20)) + ',%')
              AND (@DepositIds = '' OR ',' + @DepositIds + ',' LIKE '%,' + CAST(ISNULL(I.DEPOSITID,-1) AS VARCHAR(20)) + ',%')
              AND (@InstrumentLineIds = '' OR ',' + @InstrumentLineIds + ',' LIKE '%,' + CAST(I.INSTRUMENTLINEID AS VARCHAR(20)) + ',%')
              AND (@InstrumentTypes = '' OR ',' + @InstrumentTypes + ',' LIKE '%,' + CAST(MI.INSTRUMENTTYPE AS VARCHAR(20)) + ',%')
              AND (@ChequeType = -1 OR D.DETAILTYPE = (CASE WHEN @ChequeType = 0 THEN 1 WHEN @ChequeType = 1 THEN 0 END))
              AND (@FromDate IS NULL OR I.INSTRUMENTDATE >= @FromDate)
              AND (@ToDate IS NULL OR I.INSTRUMENTDATE <= @ToDate)
              AND (@InstrumentStatus = -1 OR I.INSTRUMENTSTATUS = @InstrumentStatus)
              AND (@VoucherNarration = '' OR V.VOUCHERNARRATION LIKE '%' + @VoucherNarration + '%')
              AND (@OuId = 0 OR V.OUID = @OuId)";

        // Legacy GB4 VoucherBLL.UpdateBRS "Loop A": flips TINSTRUMENT.INSTRUMENTSTATUS and the
        // matching date column per status (0=Pending/4=Hold set no date column; 1/2/3/5 set
        // Deposit/Realised/Bounce/Cancel dates respectively). Keyed by INSTRUMENTLINEID (confirmed
        // real column — NOT INSTRUMENTID, which is TINSTRUMENT's FK to MINSTRUMENT). GB4 builds this
        // via raw string concatenation of a literal date string; ported here as proper bound
        // parameters instead. MODIFIEDBYID/MODIFIEDON/TENANTID dropped — none of those columns exist
        // on TINSTRUMENT per the confirmed schema (the pre-existing GB5 query referencing them would
        // have thrown "Invalid column name" if ever actually run).
        // Loop B (bounce-voucher generation via SaveVoucher) is NOT ported yet — deferred per
        // explicit scope decision; this only covers the status/date column flip.
        public const string UPDATE_INSTRUMENT_STATUS_PENDING = @"
            UPDATE TINSTRUMENT SET INSTRUMENTSTATUS = 0
            WHERE INSTRUMENTLINEID = @InstrumentLineId";

        public const string UPDATE_INSTRUMENT_STATUS_DEPOSITED = @"
            UPDATE TINSTRUMENT SET INSTRUMENTSTATUS = 1, DEPOSITDATE = @Date
            WHERE INSTRUMENTLINEID = @InstrumentLineId";

        public const string UPDATE_INSTRUMENT_STATUS_REALISED = @"
            UPDATE TINSTRUMENT SET INSTRUMENTSTATUS = 2, REALISEDDATE = @Date
            WHERE INSTRUMENTLINEID = @InstrumentLineId";

        public const string UPDATE_INSTRUMENT_STATUS_BOUNCE = @"
            UPDATE TINSTRUMENT SET INSTRUMENTSTATUS = 3, BOUNCEDDATE = @Date
            WHERE INSTRUMENTLINEID = @InstrumentLineId";

        public const string UPDATE_INSTRUMENT_STATUS_HOLD = @"
            UPDATE TINSTRUMENT SET INSTRUMENTSTATUS = 4
            WHERE INSTRUMENTLINEID = @InstrumentLineId";

        public const string UPDATE_INSTRUMENT_STATUS_CANCELLED = @"
            UPDATE TINSTRUMENT SET INSTRUMENTSTATUS = 5, CANCELDATE = @Date
            WHERE INSTRUMENTLINEID = @InstrumentLineId";

        // SP name for re-posting voucher account entries
        public const string RE_ACCOUNT_POST = "RE_ACCOUNT_POST";

        // SP name for auto bill-allocation computation
        public const string AUTO_BILL_ALLOCATION = "AUTO_BILL_ALLOCATION";

        // Legacy GB4 VoucherBLL.PaymentEntryValidation: counts whether the referenced payment
        // document (TMMHEAD.DOCUMENTNUMBER) is linked, via an allotted allocation, to an MM document
        // detail with a rejected quantity. GB4 itself parameterizes this via raw string.Replace
        // (SQL-injection risk) — fixed here to use a bound @ReferenceNumber parameter instead, with
        // no other behavior change. Table/column names (TMMHEAD, TALLOCATION, TMMDETAIL,
        // OBJECTHEADERTYPEID = -1899997952) confirmed against MM.MMDAL's own existing queries
        // (AllocationQB.cs, MMheadQB.cs).
        public const string PAYMENT_ENTRY_VALIDATION_COUNT = @"
            SELECT COUNT(A.DOCUMENTNUMBER)
            FROM TMMHEAD A
            INNER JOIN TALLOCATION B ON A.DOCUMENTID = B.OBJECTHEADERID
            LEFT JOIN TALLOCATION C ON B.ALLOTEDALLOCATIONID = C.ALLOCATIONID AND C.OBJECTHEADERTYPEID = -1899997952
            LEFT JOIN TMMHEAD D ON C.OBJECTHEADERID = D.DOCUMENTID
            LEFT JOIN TMMDETAIL E ON D.DOCUMENTID = E.DOCUMENTID
            WHERE A.DOCUMENTNUMBER = @ReferenceNumber
              AND B.OBJECTHEADERTYPEID = -1899997952
              AND B.ALLOCATIONTYPE = 1
              AND E.REJECTEDQUANTITY > 0";

        // ── Existing read select lists ────────────────────────────────────────
        public const string GET_SELECTLIST_BILL_ALLOCATION = @"

                        SELECT

                        B.BILLALLOCATIONLINEID AS VoucherBillAllocationPicklistId,

                        B.DETAILNUMBER AS VoucherBillAllocationPicklistDetailNumber,

                        B.DETAILDATE AS VoucherBillAllocationPicklistDetailDate,

                        V.OUID AS OUId,
                        OU.ORGANIZATIONUNITCODE AS OUCode,
                        OU.ORGANIZATIONUNITNAME AS OUName,

                        B.PRICECATEGORYID AS PriceCategoryId,
                        PC.PRICECATEGORYCODE AS PriceCategoryCode,
                        PC.PRICECATEGORYNAME AS PriceCategoryName,

                        B.INCHARGEID AS InChargeId,
                        E.EMPLOYEECODE AS InChargeCode,
                        E.EMPLOYEENAME AS InChargeName,

                        B.CURRENCYID AS CurrencyId,
                        C.CURRENCYCODE AS CurrencyCode,
                        C.CURRENCYNAME AS CurrencyName,

                        B.ROUTEID AS RouteId,
                        R.ROUTENAME AS RouteName,

                        B.REFERENCENUMBER AS VoucherReferenceNumber,

                        B.REFERENCEDATE AS VoucherReferenceDate,

                        B.FULLAMOUNTFC AS VoucherFullAmount,

                        PB.BALANCE AS VoucherPendingAmount,

                        B.DUEDATE AS VoucherBillAllocationDueDate,

                        D.DETAILTYPE AS DetailType,

                        -- DetailType: 0 = DR, 1 = CR (matches billallocationlightbox.component.ts's
                        -- own AutoFill() convention: 'const detailType = ...; // 0 = DR , 1 = CR').
                        -- Legacy GB4 never populated this field (always null) — the picklist's own
                        -- 'Type' column asked for it but nothing ever filled it in.
                        CASE D.DETAILTYPE WHEN 0 THEN 'DR' WHEN 1 THEN 'CR' ELSE NULL END AS VoucherBillAllocationType,

                        -- Days past DueDate as of today, floored at 0 when not yet due (never
                        -- negative). Legacy GB4 never populated this either (always 0 regardless of
                        -- how overdue a bill actually was) — the picklist's own 'OverDueDays' column
                        -- asked for it but nothing ever computed it.
                        CASE WHEN B.DUEDATE < CAST(GETDATE() AS DATE)
                             THEN DATEDIFF(DAY, B.DUEDATE, CAST(GETDATE() AS DATE))
                             ELSE 0 END AS VoucherBillAllocationOverduedays,

                        V.VOUCHERNUMBER AS VoucherDocumentNumber,

                        V.VOUCHERNARRATION AS VoucherNarration

                        FROM TBILLALLOCATION B

                        INNER JOIN TVOUCHERDETAIL D
                        ON B.VOUCHERDETAILID = D.VOUCHERDETAILID

                        INNER JOIN TVOUCHER V
                        ON D.VOUCHERID = V.VOUCHERID

                        INNER JOIN VPENDINGBILLDETAIL PB
                        ON PB.ALLOCATIONLINEID = B.BILLALLOCATIONLINEID

                        -- OUCode/OUName were previously unselected (always null) — joined the same
                        -- way AccountBalanceQB.cs/InsuranceQB.cs already do (ORGANIZATIONUNITCODE/
                        -- ORGANIZATIONUNITNAME are the real MORGANIZATIONUNIT column names).
                        LEFT JOIN MORGANIZATIONUNIT OU
                        ON V.OUID = OU.OUID

                        LEFT JOIN MPRICECATEGORY PC
                        ON B.PRICECATEGORYID = PC.PRICECATEGORYID

                        LEFT JOIN MEMPLOYEE E
                        ON B.INCHARGEID = E.EMPLOYEEID

                        LEFT JOIN MCURRENCY C
                        ON B.CURRENCYID = C.CURRENCYID

                        LEFT JOIN MROUTE R
                        ON B.ROUTEID = R.ROUTEID

                        WHERE 1=1

                        AND (@AccountId = 0 OR D.VOUCHERACCOUNTID = @AccountId)

                        AND (@SubAccountId = 0 OR D.SUBACCOUNTID = @SubAccountId)

                        AND (@OuId = 0 OR V.OUID = @OuId)

                        AND (
                            @VoucherDate IS NULL
                            OR CAST(V.VOUCHERDATE AS DATE) = @VoucherDate
                        )

                        AND (
                            @VoucherBillAllocationId = 0
                            OR B.BILLALLOCATIONLINEID = @VoucherBillAllocationId
                        )

                        AND (
                            @DetailType = 99
                            OR D.DETAILTYPE = @DetailType
                        )

                        AND (
                            @Number = ''
                            OR B.DETAILNUMBER LIKE '%' + @Number + '%'
                            OR B.REFERENCENUMBER LIKE '%' + @Number + '%'
                            OR V.VOUCHERNUMBER LIKE '%' + @Number + '%'
                        )

                        ORDER BY B.DETAILDATE ASC";
        // Fixed: CreatedByName/UpdatedByName/MaxCredit/MaxDebit were previously aliased directly
        // from raw id columns (CREATEDBYID/MODIFIEDBYID/MAXCREDITACCOUNTID/MAXDEBITACCOUNTID) with
        // no join at all — the picklist was returning ids where names were expected. Joined to
        // MUSER (USERID/USERNAME) and MACCOUNT (ACCOUNTID/ACCOUNTNAME, both already confirmed real
        // this session) instead. Also added TotalAmount (VOUCHERAMOUNT), which VoucherPicklistDTO
        // already declares but the query never selected.
        // @Number filter lives INSIDE the CTE, before ROW_NUMBER() is computed — filtering
        // in the outer SELECT instead would leave RowNum numbered against the whole unfiltered
        // table, so a matching voucher outside the first page would still get paged away.
        // @OuId/@PeriodId mirror legacy GB4's own Voucher.svc/SelectList criteria (which scopes to
        // the current OrganizationUnit.Id/Period.Id) — TVOUCHER.OUID/PERIODID are both direct
        // columns (confirmed live). Without these two, a matching BizTransactionType would still
        // pull in vouchers from every OU and every period instead of just the current one.
        public const string GET_SELECTLIST_VOUCHER = @"WITH PagedVouchers AS (
                                                        SELECT
                                                            V.VOUCHERID AS Id,
                                                            V.VOUCHERNUMBER AS VoucherNumber,
                                                            V.VOUCHERDATE AS VoucherDate,
                                                            CU.USERNAME AS CreatedByName,
                                                            MU.USERNAME AS UpdatedByName,
                                                            V.VOUCHERREFERENCENUMBER AS VoucherReferenceNumber,
                                                            MCA.ACCOUNTNAME AS MaxCredit,
                                                            MDA.ACCOUNTNAME AS MaxDebit,
                                                            V.VOUCHERAMOUNT AS TotalAmount,
                                                            CASE V.STATUS WHEN 0 THEN 'Pending' WHEN 1 THEN 'Active' WHEN 2 THEN 'Deleted' WHEN 3 THEN 'Amended' WHEN 4 THEN 'InActive' WHEN 5 THEN 'Archived' ELSE 'Unknown' END AS Status,
                                                            ROW_NUMBER() OVER (ORDER BY V.VOUCHERID) AS RowNum
                                                        FROM TVOUCHER V
                                                        LEFT JOIN MUSER CU ON V.CREATEDBYID = CU.USERID
                                                        LEFT JOIN MUSER MU ON V.MODIFIEDBYID = MU.USERID
                                                        LEFT JOIN MACCOUNT MCA ON V.MAXCREDITACCOUNTID = MCA.ACCOUNTID
                                                        LEFT JOIN MACCOUNT MDA ON V.MAXDEBITACCOUNTID = MDA.ACCOUNTID
                                                        WHERE
                                                            (@Number = ''
                                                            OR V.VOUCHERNUMBER LIKE '%' + @Number + '%')
                                                            AND (@BizTransactionTypeId = -1
                                                            OR V.BIZTRANSACTIONTYPEID = @BizTransactionTypeId)
                                                            AND (@OuId = -1
                                                            OR V.OUID = @OuId)
                                                            AND (@PeriodId = -1
                                                            OR V.PERIODID = @PeriodId)
                                                    )
                                                    SELECT
                                                        Id,
                                                        VoucherNumber,
                                                        VoucherDate,
                                                        CreatedByName,
                                                        UpdatedByName,
                                                        VoucherReferenceNumber,
                                                        MaxCredit,
                                                        MaxDebit,
                                                        TotalAmount,
                                                        Status
                                                    FROM PagedVouchers
                                                   WHERE
                                             (@firstnumber = -1 AND @maxresult = -1)
                                             OR (RowNum BETWEEN @firstnumber AND @maxresult);";

        // ── Opening Balance Save/Update ─────────────────────────────────────────
        // Ported from GB4 VoucherBLL.SaveUpdateOpeningVoucher: it never creates/updates the
        // Voucher header itself — it requires one to already exist for the given
        // BizTransactionTypeId+OUId, then inserts/updates VoucherDetail (+ BillAllocation) rows
        // one at a time.
        //
        // Schema confirmed live via INFORMATION_SCHEMA.COLUMNS (2026-07-25):
        //  - TVOUCHER.OUID is real; TVOUCHER has NO "ORGANIZATIONUNITID" and NO "TENANTID" column
        //    at all (despite GET_VOUCHER above / the TVP column mapping in VoucherBLL.cs
        //    referencing both — pre-existing bug in this file, not introduced by this port).
        //  - TVOUCHERDETAIL's account column is VOUCHERACCOUNTID (NOT "ACCOUNTID"), amount columns
        //    are VOUCHERAMOUNTFC/VOUCHERAMOUNT (NOT "AMOUNTFC"/"AMOUNT"), and it likewise has NO
        //    "TENANTID" column. This was originally ported using the wrong names/an extra
        //    nonexistent filter — fixed here after live schema confirmation.
        //  - TBILLALLOCATION was not part of the confirmed batch, but given TVOUCHER/
        //    TVOUCHERDETAIL/TACCOUNTPOST all turned out to have no TENANTID column (this looks
        //    like a per-tenant-database model, not a shared multi-tenant table set), the TenantId
        //    filter below is removed proactively rather than left as a likely-same bug — flag for
        //    a follow-up schema check if TBILLALLOCATION turns out to still need tenant scoping.
        public const string GET_FIRST_PERIOD_ID = @"
            SELECT TOP 1 PERIODID
            FROM MPERIOD
            WHERE PERIODID <> -1 AND TENANTID = @TenantId
            ORDER BY FROMDATE ASC;";

        public const string GET_VOUCHER_ON_OU = @"
            SELECT TOP 1
                V.VOUCHERID AS VoucherId,
                V.VOUCHERDATE AS VoucherVoucherDate
            FROM TVOUCHER V
            WHERE V.BIZTRANSACTIONTYPEID = @BizTransactionTypeId
              AND V.OUID = @OUId;";

        public const string INSERT_VOUCHER_DETAIL_OPENING = @"
            INSERT INTO TVOUCHERDETAIL
            (
                VOUCHERDETAILID, VOUCHERID, SLNO, VOUCHERACCOUNTID, SUBACCOUNTID,
                VOUCHERAMOUNTFC, CURRENCYCONVERSION, VOUCHERAMOUNT,
                INTEROUID, INTERACCOUNTID, INTERSUBACCOUNTID,
                VOUCHERLINENARRATION, DETAILTYPE, CURRENCYID
            )
            VALUES
            (
                @VoucherDetailId, @VoucherId, @Slno, @AccountId, @SubAccountId,
                @AmountFC, @CurrencyConversion, @Amount,
                @InterOUId, @InterAccountId, @InterSubAccountId,
                @VoucherLineNarration, @DetailType, @CurrencyId
            );";

        public const string UPDATE_VOUCHER_DETAIL_OPENING = @"
            UPDATE TVOUCHERDETAIL SET
                SLNO = @Slno,
                VOUCHERACCOUNTID = @AccountId,
                SUBACCOUNTID = @SubAccountId,
                VOUCHERAMOUNTFC = @AmountFC,
                CURRENCYCONVERSION = @CurrencyConversion,
                VOUCHERAMOUNT = @Amount,
                INTEROUID = @InterOUId,
                INTERACCOUNTID = @InterAccountId,
                INTERSUBACCOUNTID = @InterSubAccountId,
                VOUCHERLINENARRATION = @VoucherLineNarration,
                DETAILTYPE = @DetailType,
                CURRENCYID = @CurrencyId
            WHERE VOUCHERDETAILID = @VoucherDetailId;";

        public const string INSERT_BILL_ALLOCATION_OPENING = @"
            INSERT INTO TBILLALLOCATION
            (
                BILLALLOCATIONLINEID, VOUCHERDETAILID, SLNO, ALLOCATIONTYPE,
                REFERENCENUMBER, REFERENCEDATE, DUEDATE,
                FULLAMOUNT, ALLOCATEDAMOUNT, STATUS
            )
            VALUES
            (
                @BillAllocationLineId, @VoucherDetailId, @Slno, @AllocationType,
                @ReferenceNumber, @ReferenceDate, @DueDate,
                @FullAmount, @AllocatedAmount, @Status
            );";

        public const string UPDATE_BILL_ALLOCATION_OPENING = @"
            UPDATE TBILLALLOCATION SET
                SLNO = @Slno,
                ALLOCATIONTYPE = @AllocationType,
                REFERENCENUMBER = @ReferenceNumber,
                REFERENCEDATE = @ReferenceDate,
                DUEDATE = @DueDate,
                FULLAMOUNT = @FullAmount,
                ALLOCATEDAMOUNT = @AllocatedAmount,
                STATUS = @Status
            WHERE BILLALLOCATIONLINEID = @BillAllocationLineId;";

        // Ported from GB4 VoucherBLL/VoucherDAL.GetVoucherDetailForOpeningBalance (Voucher.svc's
        // /VoucherDetail/?BizTransactionTypeId=&OUId=&VoucherAccountId= operation) — the read-side
        // sibling of Opening Balance Save/Update above: fetches the existing detail lines for a
        // given BizTransactionType+OU+Account so the caller can edit and re-submit them. GB4 ran
        // this as HQL selecting whole VoucherDetail entities, then reflection-copied every
        // matching-named field onto VoucherDetailDTO; ported here as a direct column-to-property
        // SELECT using the same confirmed-live TVOUCHERDETAIL/TVOUCHER schema as the rest of this
        // section (VOUCHERACCOUNTID/VOUCHERAMOUNTFC/VOUCHERAMOUNT, no TENANTID column).
        public const string GET_VOUCHER_DETAIL_FOR_OPENING_BALANCE = @"
            SELECT
                D.VOUCHERDETAILID AS VoucherDetailId,
                D.VOUCHERID AS VoucherId,
                D.SLNO AS VoucherDetailSlno,
                D.VOUCHERACCOUNTID AS VoucherAccountId,
                D.SUBACCOUNTID AS SubAccountId,
                D.VOUCHERAMOUNTFC AS VoucherDetailAmountFC,
                D.CURRENCYCONVERSION AS VoucherDetailCurrencyConversion,
                D.VOUCHERAMOUNT AS VoucherDetailAmount,
                D.INTEROUID AS InterOUId,
                D.INTERACCOUNTID AS InterAccountId,
                D.INTERSUBACCOUNTID AS InterSubAccountId,
                D.VOUCHERLINENARRATION AS VoucherDetailVoucherLineNarration,
                D.DETAILTYPE AS VoucherDetailDetailType,
                D.CURRENCYID AS CurrencyId
            FROM TVOUCHERDETAIL D
            INNER JOIN TVOUCHER V ON D.VOUCHERID = V.VOUCHERID
            WHERE V.BIZTRANSACTIONTYPEID = @BizTransactionTypeId
              AND V.OUID = @OUId
              AND D.VOUCHERACCOUNTID = @VoucherAccountId;";

        // Ported from GB4 VoucherBLL.CheckReferenceNumberDuplication (Voucher.svc's
        // /Check/Reference/Number/Duplication/?OuId=&PeriodId=&BiztransactiontypeId=&AccountId=&
        // ReferenceNumber= operation). Two deliberate fixes over GB4's version, both flagged since
        // they change behavior slightly from a literal port:
        //  - GB4 built this SQL via raw string.Replace(":placeholder", ...) directly into the query
        //    text (a SQL-injection risk on ReferenceNumber) — parameterized here instead.
        //  - GB4 used "SELECT max(a.vouchernumber)" with no GROUP BY, which always returns exactly
        //    one row (NULL when there's no match) and depended on undocumented behavior of the old
        //    NHibernate result materialization to treat that as "no duplicate". Replaced with a
        //    straightforward TOP 1 lookup that returns zero rows when there's no match.
        // Duplicate check matches ReferenceNumber against TBILLALLOCATION.DETAILNUMBER (NOT
        // TVOUCHER.VOUCHERREFERENCENUMBER), scoped by OU+Period+BizTransactionType+Account (via the
        // voucher detail's VOUCHERACCOUNTID) — same as GB4. Also matching GB4: no AccountId=0 "any
        // account" special-casing, and no exclusion of the voucher currently being edited (an
        // existing voucher's own reference number would flag itself as a "duplicate" if
        // re-validated) — both are pre-existing GB4 limitations, not something this port fixes.
        public const string CHECK_REFERENCE_NUMBER_DUPLICATION = @"
            SELECT TOP 1 V.VOUCHERNUMBER
            FROM TVOUCHER V
            INNER JOIN TVOUCHERDETAIL D ON V.VOUCHERID = D.VOUCHERID
            INNER JOIN TBILLALLOCATION B ON D.VOUCHERDETAILID = B.VOUCHERDETAILID
            WHERE B.DETAILNUMBER = @ReferenceNumber
              AND V.OUID = @OUId
              AND V.PERIODID = @PeriodId
              AND V.BIZTRANSACTIONTYPEID = @BizTransactionTypeId
              AND D.VOUCHERACCOUNTID = @AccountId;";

        // Ported from GB4 VoucherBLL.GetUsedBillAllocationForVoucher (Voucher.svc's
        // /GetUsedVoucherInBillAllocation/?VoucherId= operation). Given a voucher (typically a
        // bill/invoice), finds the OTHER TBILLALLOCATION rows (payment/against-type allocations,
        // ALLOCATIONTYPE = 2) whose self-referencing ALLOCATIONLINEID points back at one of this
        // voucher's own bill-allocation lines — i.e. "which payment entries have already been
        // allocated against this voucher's bill." TBILLALLOCATION.ALLOCATIONLINEID confirmed live
        // via INFORMATION_SCHEMA.COLUMNS (2026-07-25) as a distinct column from
        // BILLALLOCATIONLINEID. GB4's version returned only DETAILNUMBER per row (every other
        // VoucherBillAllocationPicklistDTO field left at its default) — same minimal projection
        // kept here; DISTINCT added since GB4's un-deduplicated version could repeat the same
        // DetailNumber once per matching allocation-line row.
        public const string GET_USED_VOUCHER_IN_BILL_ALLOCATION = @"
            SELECT DISTINCT BA.DETAILNUMBER AS VoucherBillAllocationPicklistDetailNumber
            FROM TBILLALLOCATION BA
            WHERE BA.ALLOCATIONTYPE = 2
              AND BA.ALLOCATIONLINEID IN (
                  SELECT B.BILLALLOCATIONLINEID
                  FROM TBILLALLOCATION B
                  WHERE B.VOUCHERDETAILID IN (
                      SELECT D.VOUCHERDETAILID FROM TVOUCHERDETAIL D WHERE D.VOUCHERID = @VoucherId
                  )
              );";

        // Ported from GB4 VoucherBLL.GetVoucherSummaryInfo (Voucher.svc's
        // /Voucher/Summary/Info/?VoucherId= operation). Despite the "Summary" name, this is a
        // plain header lookup — no aggregation, no detail/bill-allocation rollup: just the
        // voucher header joined to its main account's code/name. GB4 built this via naive
        // string.Replace(":voucherid", ...) rather than a parameterized query — parameterized
        // here instead. TVOUCHER.MAINACCOUNTID and MACCOUNT.ACCOUNTID/ACCOUNTCODE/ACCOUNTNAME
        // already confirmed live earlier this session.
        public const string GET_VOUCHER_SUMMARY_INFO = @"
            SELECT
                V.VOUCHERID AS VoucherId,
                V.VOUCHERNUMBER AS VoucherNumber,
                V.VOUCHERDATE AS VoucherDate,
                V.VOUCHERREFERENCENUMBER AS VoucherReferenceNumber,
                V.VOUCHERREFERENCEDATE AS VoucherReferenceDate,
                A.ACCOUNTCODE AS AccountCode,
                A.ACCOUNTNAME AS AccountName,
                V.VOUCHERAMOUNT AS VoucherAmount
            FROM TVOUCHER V
            INNER JOIN MACCOUNT A ON V.MAINACCOUNTID = A.ACCOUNTID
            WHERE V.VOUCHERID = @VoucherId;";
    }
}