using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;

namespace MMDAL.Query.Allocation
{
    /// <summary>
    /// Query Builder — Allocation module.
    /// Contains all SQL constants required by <see cref="AllocationDAL"/>.
    ///
    /// GB4 Source : Unisoft.MMDAL.Query.MM.AllocationQueryBuilder
    ///
    /// GB5 SQL conventions (matching the existing GET_ALLOCATION pattern):
    ///   • @param     Dapper-style bound parameters (not :param).
    ///   • LEFT JOIN  instead of LEFT OUTER JOIN.
    ///   • JOIN       (implicit INNER JOIN) instead of comma-list implicit joins.
    ///   • UPPERCASE  SQL keywords throughout.
    ///   • Aligned    AS aliases for readability.
    ///
    /// String-replace tokens — NOT Dapper params.
    /// AllocationDAL calls string.Replace() on these before handing SQL to the executor:
    ///   :replacestring          item-wise SELECT expansion or empty string
    ///   :loadfrombiztype        "JOIN MBIZTRANSACTIONTYPE Loadtotype ON ..." or empty
    ///   :showonlyavailbletowork literal 0 or 1
    ///   :HeaderReplacePart      SELECT column list injected at runtime
    ///   :HeaderTablePart        additional JOIN/FROM tables injected at runtime
    ///   :objectheadertypeid     literal int replaced via ReplaceNamedParams helper
    ///   :objecttypeid           literal int replaced via ReplaceNamedParams helper
    /// </summary>
    public static class AllocationQB
    {
        // ─────────────────────────────────────────────────────────────────────────
        // GET_ALLOCATION
        // Fetches a single allocation record with all related lookup data.
        // Used by : AllocationDAL.GetAllocation
        // Param   : @allocationid  (Dapper)
        // GB4     : New in GB5 — no direct equivalent existed in GB4 QB.
        // ─────────────────────────────────────────────────────────────────────────

        public const string GET_ALLOCATION =
            @"SELECT
    A.ALLOCATIONID                          AS AllocationId,
    A.BIZTRANSACTIONTYPEID                  AS BizTransactionTypeId,
    BTT.BIZTRANSACTIONTYPECODE              AS BizTransactionTypeCode,
    BTT.BIZTRANSACTIONTYPENAME              AS BizTransactionTypeName,

    A.OBJECTHEADERTYPEID                    AS ObjectHeaderTypeId,
    OHT.ENTITYCODE                          AS ObjectHeaderTypeCode,
    OHT.ENTITYNAME                          AS ObjectHeaderTypeName,

    A.OBJECTHEADERID                        AS AllocationObjectHeaderId,

    A.OBJECTTYPEID                          AS ObjectTypeId,
    OT.ENTITYCODE                           AS ObjectTypeCode,
    OT.ENTITYNAME                           AS ObjectTypeName,

    A.OBJECTID                              AS AllocationObjectId,
    A.ALLOCATIONTYPE                        AS AllocationType,
    A.ALLOCATIONNATURE                      AS AllocationNature,

    AA.BIZTRANSACTIONTYPEID                 AS AllotedBizTransactionTypeId,
    ABTT.BIZTRANSACTIONTYPECODE             AS AllotedBizTransactionTypeCode,
    ABTT.BIZTRANSACTIONTYPENAME             AS AllotedBizTransactionTypeName,

    AA.OBJECTHEADERTYPEID                   AS AllotedHeaderTypeId,
    AHT.ENTITYCODE                          AS AllotedHeaderTypeCode,
    AHT.ENTITYNAME                          AS AllotedHeaderTypeName,

    AA.OBJECTHEADERID                       AS AllocationAllotedHeaderId,

    AA.OBJECTTYPEID                         AS AllotedObjectTypeId,
    AOT.ENTITYCODE                          AS AllotedObjectTypeCode,
    AOT.ENTITYNAME                          AS AllotedObjectTypeName,

    AA.OBJECTID                             AS AllocationAllotedObjectId,

    A.QUANTITY                              AS AllocationQuantity,
    A.REJECTEDQUANTITY                      AS AllocationRejectedQuantity,
    A.REWORKQUANTITY                        AS AllocationReworkQuantity,
    A.OTHERQUANTITY                         AS AllocationOtherQuantity,
    A.EXCESSQUANTITY                        AS AllocationExcessQuantity,
    A.FREEQUANTITY                          AS AllocationFreeQuantity,

    A.PROCESSID                             AS ProcessId,
    P.PROCESSCODE                           AS ProcessCode,
    P.PROCESSNAME                           AS ProcessName,

    A.ALLOTEDALLOCATIONID                   AS AllotedAllocationId,
    MAL.ALLOCATIONNAME                      AS AllotedAllocationName,

    A.MODIFIEDON                            AS ModifiedOn

FROM TALLOCATION A
    LEFT JOIN MBIZTRANSACTIONTYPE  BTT  ON A.BIZTRANSACTIONTYPEID  = BTT.BIZTRANSACTIONTYPEID
    LEFT JOIN MENTITY              OHT  ON A.OBJECTHEADERTYPEID    = OHT.ENTITYID
    LEFT JOIN MENTITY              OT   ON A.OBJECTTYPEID          = OT.ENTITYID
    LEFT JOIN TALLOCATION          AA   ON A.ALLOTEDALLOCATIONID   = AA.ALLOCATIONID
    LEFT JOIN MBIZTRANSACTIONTYPE  ABTT ON AA.BIZTRANSACTIONTYPEID = ABTT.BIZTRANSACTIONTYPEID
    LEFT JOIN MENTITY              AHT  ON AA.OBJECTHEADERTYPEID   = AHT.ENTITYID
    LEFT JOIN MENTITY              AOT  ON AA.OBJECTTYPEID         = AOT.ENTITYID
    LEFT JOIN MPROCESS             P    ON A.PROCESSID             = P.PROCESSID
    LEFT JOIN MALLOCATION          MAL  ON A.ALLOTEDALLOCATIONID   = MAL.ALLOCATIONID
WHERE
    A.ALLOCATIONID = @allocationid;";


        // ─────────────────────────────────────────────────────────────────────────
        // GET_NEWALLOCATION_BASED_DOCNO
        // Paginated SQL Server picklist inner query.
        // The DAL closes this with ") x" and appends a GROUPBY constant.
        // Used by : AllocationDAL.LoadFromAllocationPicklist  (SQL Server path)
        //
        // String-replace tokens (applied by DAL before execution):
        //   :replacestring          item columns when ItemWiseTag=0, else empty
        //   :loadfrombiztype        "JOIN MBIZTRANSACTIONTYPE Loadtotype ON ..." or empty
        //   :showonlyavailbletowork literal 0 or 1
        //
        // GB4 Source : AllocationQueryBuilder.GET_NEWALLOCATION_BASED_DOCNO
        // ─────────────────────────────────────────────────────────────────────────

        public const string GET_NEWALLOCATION_BASED_DOCNO =
            @"SELECT
    HeaderName,
    AllocationObjectHeaderId,
    HeaderDate,
    DocumentAllocationId,
    DocumentAllocationName,
    ISNULL(RefNumber, '')                   AS RefNumber,
    RefDate,
    SUM(AllocationTotalQuantity)            AS AllocationTotalQuantity,
    PartyId,
    PartyName,
    PartyBranchId,
    PartyBranchName,
    ModifiedBy :replacestring
FROM
(
    SELECT
        A.DOCUMENTNUMBER                    AS HeaderName,
        A.OBJECTHEADERID                    AS AllocationObjectHeaderId,
        A.DOCUMENTDATE                      AS HeaderDate,
        A.HEADERALLOCATIONID                AS DocumentAllocationId,
        MAL.ALLOCATIONNAME                  AS DocumentAllocationName,
        A.PARTYREFERENCENUMBER              AS RefNumber,
        A.PARTYREFERENCEDATE                AS RefDate,
        A.QUANTITY                          AS AllocationTotalQuantity,
        PARTY.PARTYID                       AS PartyId,
        PARTY.PARTYNAME                     AS PartyName,
        PARTYBRANCH.PARTYBRANCHID           AS PartyBranchId,
        PARTYBRANCH.PARTYBRANCHNAME         AS PartyBranchName,
        MOD.USERNAME                        AS ModifiedBy,
        A.ITEMID                            AS ItemId,
        ITEM.ITEMCODE                       AS ItemCode,
        ITEM.ITEMNAME                       AS ItemName,
        A.SKUID                             AS SKUId,
        SKU.SKUCODE                         AS SKUCode,
        SKU.SKUNAME                         AS SKUName
    FROM TPENDINGALLOCATION A

        -- Revised by Vikash (26-Aug-2023, Bug #35796):
        -- Resolved via IndentId instead of ObjectId because PendingAllocation rows
        -- may exist before output is posted to TPOSTEDINDENTDETAIL.
        OUTER APPLY (
            SELECT TOP (1) PDTL.*
            FROM  TPOSTEDINDENTDETAIL PDTL
            JOIN  TINDENTDETAIL       INDDTL ON PDTL.INDENTID = INDDTL.INDENTID
            WHERE PDTL.INDENTID           = A.OBJECTHEADERID
              AND A.OBJECTHEADERTYPEID    = -1899997569
              AND INDDTL.RELEASESTATUS   <> 5
        ) PDTL

        OUTER APPLY (
            SELECT MIN(PDTLMIN.ROUTINGDETAILSLNO) AS ROUTINGDETAILSLNO
            FROM  TPOSTEDINDENTDETAIL PDTLMIN
            WHERE PDTLMIN.COMBINEINDENTID = PDTL.COMBINEINDENTID
        ) PDTLMIN

        JOIN MBIZTRANSACTIONTYPE  C           ON A.BIZTRANSACTIONTYPEID  = C.BIZTRANSACTIONTYPEID
        JOIN MBIZTRANSACTIONCLASS D           ON C.BIZTRANSACTIONCLASSID = D.BIZTRANSACTIONCLASSID
        JOIN MBIZTRANSACTION      BIZ         ON C.BIZTRANSACTIONID      = BIZ.BIZTRANSACTIONID
        JOIN MPARTYBRANCH         PARTYBRANCH ON A.PARTYBRANCHID         = PARTYBRANCH.PARTYBRANCHID
        JOIN MPARTY               PARTY       ON PARTYBRANCH.PARTYID     = PARTY.PARTYID
        JOIN MITEM                ITEM        ON A.ITEMID                = ITEM.ITEMID
        JOIN MSKU                 SKU         ON A.SKUID                 = SKU.SKUID
        JOIN MALLOCATION          MAL         ON A.HEADERALLOCATIONID    = MAL.ALLOCATIONID
        JOIN MUSER                MOD         ON A.CREATEDBYID           = MOD.USERID
        :loadfrombiztype
    WHERE 1 = 1
        -- Available-to-work filter (Bug #35796).
        -- :showonlyavailbletowork = 1 bypasses the check entirely.
        AND (
            (D.BIZTRANSACTIONCLASSID <> -1399999908)
            OR (
                D.BIZTRANSACTIONCLASSID = -1399999908
                AND (
                    (PDTL.INDENTID = PDTL.COMBINEINDENTID)
                    OR (ISNULL(PDTL.AVAILABLETOWORK, 0) > 0)
                    OR :showonlyavailbletowork = 1
                    OR ISNULL(PDTLMIN.ROUTINGDETAILSLNO, 1) = PDTL.ROUTINGDETAILSLNO
                )
            )
            OR (PDTL.AVAILABLETOWORK IS NULL)
        )
        -- Bug #37274: exclude closed work-orders.
        AND -1 <> CASE
            WHEN D.BIZTRANSACTIONCLASSID <> -1399999908 THEN -2100000000
            ELSE ISNULL(PDTL.INDENTID, -1)
        END
";


        // ─────────────────────────────────────────────────────────────────────────
        // GROUPBY_NEWALLOCATION_BASED_DOCNO
        // Appended after ") x" closing of GET_NEWALLOCATION_BASED_DOCNO.
        // Used when ItemWiseTag = 1  →  document-level grouping, no item columns.
        // Used by : AllocationDAL.LoadFromAllocationPicklist
        // GB4 Source : AllocationQueryBuilder.GROUPBY_NEWALLOCATION_BASED_DOCNO
        // ─────────────────────────────────────────────────────────────────────────

        public const string GROUPBY_NEWALLOCATION_BASED_DOCNO =
            @"GROUP BY
    HeaderName, AllocationObjectHeaderId, HeaderDate,
    DocumentAllocationId, DocumentAllocationName, RefNumber, RefDate,
    PartyId, PartyName, PartyBranchId, PartyBranchName, ModifiedBy
ORDER BY x.HeaderName";


        // ─────────────────────────────────────────────────────────────────────────
        // GROUPBY_NEWALLOCATION_BASED_DOCNO_ITEM
        // Appended after ") x" closing of GET_NEWALLOCATION_BASED_DOCNO.
        // Used when ItemWiseTag = 0  →  item-level grouping, includes item/SKU columns.
        // Used by : AllocationDAL.LoadFromAllocationPicklist
        // GB4 Source : AllocationQueryBuilder.GROUPBY_NEWALLOCATION_BASED_DOCNO_ITEM
        // ─────────────────────────────────────────────────────────────────────────

        public const string GROUPBY_NEWALLOCATION_BASED_DOCNO_ITEM =
            @"GROUP BY
    HeaderName, AllocationObjectHeaderId, HeaderDate,
    DocumentAllocationId, DocumentAllocationName, RefNumber, RefDate,
    PartyId, PartyName, PartyBranchId, PartyBranchName, ModifiedBy,
    ItemId, ItemCode, ItemName, SKUId, SKUCode, SKUName, AllocationTotalQuantity
ORDER BY x.HeaderName";


        // ─────────────────────────────────────────────────────────────────────────
        // GET_PENDING_ALLOCATION_DOCUMENT_NEW
        // Oracle path — MM Document head-type, compact mode (NeedAllTag = 0).
        // Used by : AllocationDAL.BuildMMHeadQuery  (NeedAllTag = 0 branch)
        //
        // String-replace tokens (applied by DAL before execution):
        //   :HeaderReplacePart  SELECT column list (compact or item-wise variant)
        //   :loadfrombiztype    "JOIN MBIZTRANSACTIONTYPE Loadtotype ON ..." or empty
        //
        // Changes vs GB4 GET_PENDING_ALLOCATION_DOCUMENT (legacy):
        //   • Added LEFT JOIN MPARTY, MITEM, MUOM (needed for item-wise select).
        //   • Added OUTER APPLY TPACKDETAIL for pack quantity (Bug #38762, Apr-2023).
        //   • :loadfrombiztype token replaces the hardcoded join in the old version.
        //
        // GB4 Source : AllocationQueryBuilder.GET_PENDING_ALLOCATION_DOCUMENT_NEW
        // ─────────────────────────────────────────────────────────────────────────

        public const string GET_PENDING_ALLOCATION_DOCUMENT_NEW =
            @"SELECT
    :HeaderReplacePart
FROM TPENDINGALLOCATION A
    LEFT JOIN TMMHEAD B
           ON A.OBJECTHEADERID     = B.DOCUMENTID
          AND A.OBJECTHEADERTYPEID = -1899997952
          AND A.OBJECTTYPEID       = -1899997854
    LEFT JOIN TMMDETAIL  DTL      ON B.DOCUMENTID   = DTL.DOCUMENTID
    LEFT JOIN MPARTY     PARTY    ON B.PARTYID       = PARTY.PARTYID
    LEFT JOIN MITEM      ITEM     ON DTL.ITEMID      = ITEM.ITEMID
    LEFT JOIN MUOM       UOM      ON ITEM.STOCKUOMID = UOM.UOMID

    -- Pack quantity sub-total per detail line (Bug #38762, 28-Apr-2023).
    OUTER APPLY (
        SELECT SUM(PACKDTL.GOODQUANTITY) AS GOODQUANTITY
        FROM   TPACKDETAIL PACKDTL
        WHERE  PACKDTL.DOCUMENTDETAILID = DTL.DOCUMENTDETAILID
    ) PACKDTL

    LEFT JOIN TINDENT IND
           ON A.OBJECTHEADERID     = IND.INDENTID
          AND A.OBJECTHEADERTYPEID = -1899997952
          AND A.OBJECTTYPEID       = -1899997854

    JOIN MBIZTRANSACTIONTYPE  C           ON A.BIZTRANSACTIONTYPEID  = C.BIZTRANSACTIONTYPEID
    JOIN MBIZTRANSACTIONCLASS D           ON C.BIZTRANSACTIONCLASSID = D.BIZTRANSACTIONCLASSID
    JOIN MBIZTRANSACTION      BIZ         ON C.BIZTRANSACTIONID      = BIZ.BIZTRANSACTIONID
    JOIN MPARTYBRANCH         PARTYBRANCH ON A.PARTYBRANCHID         = PARTYBRANCH.PARTYBRANCHID
    JOIN MALLOCATION          MAL         ON B.ALLOCATIONID          = MAL.ALLOCATIONID
    JOIN MUSER                MOD         ON B.CREATEDBYID           = MOD.USERID
    :loadfrombiztype
WHERE 1 = 1
    AND A.BIZTRANSACTIONTYPEID    = C.BIZTRANSACTIONTYPEID
    AND C.BIZTRANSACTIONCLASSID   = D.BIZTRANSACTIONCLASSID
    AND C.BIZTRANSACTIONID        = BIZ.BIZTRANSACTIONID
    AND PARTYBRANCH.PARTYBRANCHID = A.PARTYBRANCHID
    AND MAL.ALLOCATIONID          = B.ALLOCATIONID
    AND MOD.USERID                = B.CREATEDBYID
    AND A.ALLOCATIONNATURE       IN (1)
    AND A.OBJECTID                = DTL.DOCUMENTDETAILID
";


        // ─────────────────────────────────────────────────────────────────────────
        // GET_PENDING_ALLOCATION_FORALL
        // Oracle path — MM Document full mode (NeedAllTag = 1) and base template
        // for all Indent head-type branches.
        // Used by : AllocationDAL.BuildMMHeadQuery  (NeedAllTag = 1 branch)
        //           AllocationDAL.BuildIndentQuery   (all IsMultiple modes)
        //
        // String-replace tokens (applied by DAL before execution):
        //   :HeaderReplacePart  SELECT column list built at runtime
        //   :HeaderTablePart    additional JOIN tables built at runtime
        //   :objectheadertypeid literal int via ReplaceNamedParams
        //   :objecttypeid       literal int via ReplaceNamedParams
        //
        // Notable details:
        //   • Uses TPENDINGALLOCATION (Oracle trigger table, not the view).
        //   • PRE sub-query    MM Head source traceability (AllocationType 0 -> 1).
        //   • INDPRE sub-query Indent source traceability.
        //   • ISNULL(B.AMENDMENTSLNO, 0) for amendment tracking.
        //   • CASE on SELECTIONCLASSID collapses three manufacturing classes into
        //     one expression (GB4 used nested CASE WHEN ... ELSE CASE WHEN ...).
        //
        // GB4 Source : AllocationQueryBuilder.GET_PENDING_ALLOCATION_FORALL
        // ─────────────────────────────────────────────────────────────────────────

        public const string GET_PENDING_ALLOCATION_FORALL =
            @"SELECT DISTINCT
    A.ALLOCATIONID                          AS AllocationId,
    A.ALLOCATIONNATURE                      AS AllocationNature,
    A.BIZTRANSACTIONTYPEID                  AS BizTransactionTypeId,
    A.OBJECTHEADERID                        AS AllocationObjectHeaderId,
    A.OBJECTHEADERTYPEID                    AS ObjectHeaderTypeId,
    A.OBJECTID                              AS AllocationObjectId,
    A.OBJECTTYPEID                          AS ObjectTypeId,
    A.ALLOTEDALLOCATIONID                   AS AllotedAllocationId,
    A.OTHERQUANTITY                         AS AllocationOtherQuantity,
    A.PROCESSID                             AS ProcessId,
    E.PENDINGQUANTITY                       AS AllocationQuantity,
    E.PENDINGREJECTEDQUANTITY               AS AllocationRejectedQuantity,
    E.PENDINGREWORKQUANTITY                 AS AllocationReworkQuantity,
    E.PENDINGOTHERQUANTITY                  AS AllocationOtherQuantity,
    A.ALLOCATIONTYPE                        AS AllocationType,
    ISNULL(B.AMENDMENTSLNO, 0)              AS AmendmentSlNo,
    -- Pre-document number: manufacturing biz classes resolve from INDPRE (Indent),
    -- all other classes resolve from PRE (MM Head source).
    CASE
        WHEN C.SELECTIONCLASSID IN (-1399999916, -1399999908, -1499999995)
            THEN ISNULL(INDPRE.DOCUMENTNUMBER,     '')
        ELSE     ISNULL(PRE.DOCUMENTNUMBER,        '')
    END                                     AS PreDocumentNumber,
    CASE
        WHEN C.SELECTIONCLASSID IN (-1399999916, -1399999908, -1499999995)
            THEN ISNULL(INDPRE.DOCUMENTMODIFIEDBY, '')
        ELSE     ISNULL(PRE.DOCUMENTMODIFIEDBY,    '')
    END                                     AS PreDocumentModifiedBy
    :HeaderReplacePart
FROM TALLOCATION A
    LEFT JOIN TMMHEAD B
           ON A.OBJECTHEADERID     = B.DOCUMENTID
          AND A.OBJECTHEADERTYPEID = :objectheadertypeid
          AND A.OBJECTTYPEID       = :objecttypeid
    LEFT JOIN TMMDETAIL DTL ON B.DOCUMENTID = DTL.DOCUMENTID

    -- Pre-document traceability: MM Head source (AllocationType 0 allotted to 1).
    LEFT JOIN (
        SELECT
            MMHEAD.DOCUMENTID               AS DocumentId,
            MMHEAD.DOCUMENTNUMBER           AS DocumentNumber,
            MMHEAD.DOCUMENTDATE             AS DocumentDate,
            MMHEAD.REFERENCENUMBER          AS DocumentReferenceNumber,
            MMHEAD.REFERENCEDATE            AS DocumentReferenceDate,
            MMHEAD.PARTYREFERENCENUMBER     AS DocumentPartyReferenceNumber,
            MMHEAD.PARTYREFERENCEDATE       AS DocumentPartyReferenceDate,
            ALLOC1.OBJECTHEADERID           AS ObjectHeaderId,
            ALLOC1.OBJECTID                 AS ObjectId,
            USR.USERNAME                    AS DocumentModifiedBy
        FROM TMMHEAD MMHEAD
            JOIN TALLOCATION ALLOC
                ON  MMHEAD.BIZTRANSACTIONTYPEID = ALLOC.BIZTRANSACTIONTYPEID
                AND MMHEAD.DOCUMENTID           = ALLOC.OBJECTHEADERID
                AND ALLOC.ALLOCATIONTYPE        = 0
            JOIN TMMDETAIL TMMD
                ON  MMHEAD.DOCUMENTID     = TMMD.DOCUMENTID
                AND TMMD.DOCUMENTDETAILID = ALLOC.OBJECTID
            JOIN TALLOCATION ALLOC1
                ON  ALLOC.ALLOCATIONID         = ALLOC1.ALLOTEDALLOCATIONID
                AND ALLOC1.OBJECTHEADERTYPEID  = -1899997952
                AND ALLOC1.OBJECTTYPEID        = -1899997854
                AND ALLOC1.ALLOCATIONTYPE      = 1
            JOIN MUSER USR ON MMHEAD.MODIFIEDBYID = USR.USERID
    ) PRE
        ON PRE.OBJECTHEADERID = DTL.DOCUMENTID
       AND PRE.OBJECTID       = DTL.DOCUMENTDETAILID

    -- Pre-document traceability: Indent source.
    LEFT JOIN (
        SELECT
            IND.INDENTID                    AS DocumentId,
            IND.INDENTNUMBER                AS DocumentNumber,
            IND.INDENTDATE                  AS DocumentDate,
            IND.REFERENCENUMBER             AS DocumentReferenceNumber,
            IND.REFERENCEDATE               AS DocumentReferenceDate,
            ALLOC1.OBJECTHEADERID           AS ObjectHeaderId,
            ALLOC1.OBJECTID                 AS ObjectId,
            USR.USERNAME                    AS DocumentModifiedBy
        FROM TINDENT IND
            JOIN TALLOCATION ALLOC
                ON  IND.BIZTRANSACTIONTYPEID = ALLOC.BIZTRANSACTIONTYPEID
                AND IND.INDENTID             = ALLOC.OBJECTHEADERID
                AND ALLOC.ALLOCATIONTYPE     = 0
            JOIN TALLOCATION ALLOC1
                ON  ALLOC.ALLOCATIONID        = ALLOC1.ALLOTEDALLOCATIONID
                AND ALLOC1.OBJECTHEADERTYPEID = -1899997952
                AND ALLOC1.OBJECTTYPEID       = -1899997854
                AND ALLOC1.ALLOCATIONTYPE     = 1
            JOIN MUSER USR ON IND.MODIFIEDBYID = USR.USERID
    ) INDPRE
        ON INDPRE.OBJECTHEADERID = DTL.DOCUMENTID
       AND INDPRE.OBJECTID       = DTL.DOCUMENTDETAILID

    LEFT JOIN TINDENT         IND    ON A.OBJECTHEADERID     = IND.INDENTID
                                   AND A.OBJECTHEADERTYPEID  = :objectheadertypeid
                                   AND A.OBJECTTYPEID        = :objecttypeid
    LEFT JOIN TINDENTDETAIL   INDDTL ON IND.INDENTID          = INDDTL.INDENTID
    LEFT JOIN TINDENTMATERIAL INDMTL ON INDDTL.INDENTDETAILID = INDMTL.INDENTDETAILID

    JOIN MBIZTRANSACTIONTYPE  C   ON A.BIZTRANSACTIONTYPEID  = C.BIZTRANSACTIONTYPEID
    JOIN MBIZTRANSACTIONCLASS D   ON C.BIZTRANSACTIONCLASSID = D.BIZTRANSACTIONCLASSID
    JOIN MBIZTRANSACTION      BIZ ON C.BIZTRANSACTIONID      = BIZ.BIZTRANSACTIONID
    JOIN TPENDINGALLOCATION   E   ON E.ALLOTEDALLOCATIONID   = A.ALLOCATIONID
    -- :HeaderTablePart is replaced at runtime with additional JOINs such as:
    -- MPARTY, MPARTYBRANCH, MSKU, MITEM, MALLOCATION, MUSER, MUOM,
    -- and optionally MBIZTRANSACTIONTYPE Loadtotype.
    :HeaderTablePart
WHERE 1 = 1
    AND E.PENDINGQUANTITY > 0
";


        // ─────────────────────────────────────────────────────────────────────────
        // GET_PENDING_ALLOCATION_FORINDENT
        // Oracle path — Indent head-type, single-line mode (IsMultiple = 0).
        // Used by : AllocationDAL.BuildIndentQuery  (IsMultiple = 0 branch)
        //
        // String-replace tokens:
        //   :HeaderReplacePart  SELECT column list
        //   :HeaderTablePart    additional JOIN tables (must include MPARTYBRANCH)
        //   :objectheadertypeid literal int via ReplaceNamedParams
        //
        // GB4 Source : AllocationQueryBuilder.GET_PENDING_ALLOCATION_FORINDENT
        // ─────────────────────────────────────────────────────────────────────────

        public const string GET_PENDING_ALLOCATION_FORINDENT =
            @"SELECT DISTINCT
    A.ALLOCATIONID                          AS AllocationId,
    A.ALLOCATIONNATURE                      AS AllocationNature,
    A.BIZTRANSACTIONTYPEID                  AS BizTransactionTypeId,
    A.OBJECTHEADERID                        AS AllocationObjectHeaderId,
    A.OBJECTHEADERTYPEID                    AS ObjectHeaderTypeId,
    A.OBJECTID                              AS AllocationObjectId,
    A.OBJECTTYPEID                          AS ObjectTypeId,
    A.ALLOTEDALLOCATIONID                   AS AllotedAllocationId,
    A.OTHERQUANTITY                         AS AllocationOtherQuantity,
    A.PROCESSID                             AS ProcessId,
    E.PENDINGQUANTITY                       AS AllocationQuantity,
    A.REJECTEDQUANTITY                      AS AllocationRejectedQuantity,
    A.REWORKQUANTITY                        AS AllocationReworkQuantity,
    A.ALLOCATIONTYPE                        AS AllocationType
    :HeaderReplacePart
FROM TALLOCATION A
    LEFT JOIN TINDENT IND
           ON A.OBJECTHEADERID     = IND.INDENTID
          AND A.OBJECTHEADERTYPEID = :objectheadertypeid
          AND IND.STATUS           = 1
    LEFT JOIN TINDENTDETAIL   INDDTL ON IND.INDENTID           = INDDTL.INDENTID
    LEFT JOIN TINDENTMATERIAL INDMTL ON INDDTL.INDENTDETAILID  = INDMTL.INDENTDETAILID
    JOIN MBIZTRANSACTIONTYPE  C   ON A.BIZTRANSACTIONTYPEID  = C.BIZTRANSACTIONTYPEID
    JOIN MBIZTRANSACTIONCLASS D   ON C.BIZTRANSACTIONCLASSID = D.BIZTRANSACTIONCLASSID
    JOIN MBIZTRANSACTION      BIZ ON C.BIZTRANSACTIONID      = BIZ.BIZTRANSACTIONID
    JOIN VPENDINGALLOCATION   E   ON E.ALLOTEDALLOCATIONID   = A.ALLOCATIONID
    :HeaderTablePart
WHERE 1 = 1
    AND E.PENDINGQUANTITY > 0
";


        // ─────────────────────────────────────────────────────────────────────────
        // GET_PENDING_ALLOCATION_FORINDENT_NEW
        // Oracle path — Indent head-type, multi-BOM mode (IsMultiple = 1).
        // Used by : AllocationDAL.BuildIndentQuery  (IsMultiple = 1 branch)
        //
        // Extends GET_PENDING_ALLOCATION_FORINDENT with:
        //   • OUTER APPLY BOMITEMSECTION — BOM item count per routing slot
        //     (Redmine CR #31009, 22-Jan-2021). DAL appends an extra AND clause
        //     for BizClass -1399999916 after selecting this constant.
        //   • LEFT JOIN TPOSTEDINDENTDETAIL POSTEDDTL — routing slot data.
        //   • CROSS APPLY VV — produced good quantity from TPRODUCTIONDETAIL.
        //   • Release status filter : Planned(0) / Released(3) / Processing(6).
        //   • Remaining planned quantity check (planned - produced > 0).
        //
        // String-replace tokens:
        //   :HeaderReplacePart  SELECT column list
        //   :HeaderTablePart    additional JOIN tables
        //   :objectheadertypeid literal int via ReplaceNamedParams
        //
        // GB4 Source : AllocationQueryBuilder.GET_PENDING_ALLOCATION_FORINDENT_NEW
        // ─────────────────────────────────────────────────────────────────────────

        public const string GET_PENDING_ALLOCATION_FORINDENT_NEW =
            @"SELECT DISTINCT
    A.BIZTRANSACTIONTYPEID                  AS BizTransactionTypeId,
    A.OBJECTHEADERID                        AS AllocationObjectHeaderId,
    A.OBJECTHEADERTYPEID                    AS ObjectHeaderTypeId
    :HeaderReplacePart
FROM TALLOCATION A
    LEFT JOIN TINDENT IND
           ON A.OBJECTHEADERID     = IND.INDENTID
          AND A.OBJECTHEADERTYPEID = :objectheadertypeid
          AND IND.STATUS           = 1

    -- BOM item count per routing slot (Redmine CR #31009, 22-Jan-2021).
    OUTER APPLY (
        SELECT COUNT(INDMAT.INDENTDETAILID) AS Indmatcount
        FROM  TPOSTEDINDENTDETAIL PDTL
        JOIN  TINDENTMATERIAL     INDMAT ON PDTL.INDENTDETAILID = INDMAT.INDENTDETAILID
        WHERE INDMAT.TYPEOFDATA = 0
          AND PDTL.INDENTID     = IND.INDENTID
    ) BOMITEMSECTION

    LEFT JOIN TPOSTEDINDENTDETAIL POSTEDDTL ON IND.INDENTID            = POSTEDDTL.INDENTID
    LEFT JOIN TINDENTDETAIL       INDDTL    ON IND.INDENTID            = INDDTL.INDENTID
    LEFT JOIN TINDENTMATERIAL     INDMTL    ON INDDTL.INDENTDETAILID   = INDMTL.INDENTDETAILID

    -- Produced good quantity per indent detail line.
    CROSS APPLY (
        SELECT SUM(GOODQUANTITY) AS Goodquantity
        FROM   TPRODUCTIONDETAIL
        WHERE  INDENTDETAILID = INDDTL.INDENTDETAILID
    ) VV

    JOIN MBIZTRANSACTIONTYPE  C   ON A.BIZTRANSACTIONTYPEID  = C.BIZTRANSACTIONTYPEID
    JOIN MBIZTRANSACTIONCLASS D   ON C.BIZTRANSACTIONCLASSID = D.BIZTRANSACTIONCLASSID
    JOIN MBIZTRANSACTION      BIZ ON C.BIZTRANSACTIONID      = BIZ.BIZTRANSACTIONID
    JOIN VPENDINGALLOCATION   E   ON E.ALLOTEDALLOCATIONID   = A.ALLOCATIONID
    :HeaderTablePart
WHERE 1 = 1
    AND E.PENDINGQUANTITY > 0
    -- Only active line statuses: Planned(0), Released(3), Processing(6).
    AND INDDTL.RELEASESTATUS IN (0, 3, 6)
    -- Exclude lines where production is already complete.
    AND ISNULL(INDDTL.PLANNEDQUANTITY, 0) - ISNULL(VV.GOODQUANTITY, 0) > 0
";


        // ─────────────────────────────────────────────────────────────────────────
        // GET_PENDING_ALLOCATION_FORINDENT_BASED_ON_COMBINE_INDENT_BASED
        // Oracle path — Indent head-type, combine-indent mode (CombineIndent = 0).
        // Groups picklist rows by the parent combine-indent (COMBIND) rather than
        // the child indent, surfacing the combine indent's number, date, and party.
        // Used by : AllocationDAL.BuildIndentQuery  (CombineIndent = 0 branch)
        //
        // String-replace tokens:
        //   :HeaderTablePart    additional JOIN tables (must include MPARTYBRANCH)
        //   :objectheadertypeid literal int via ReplaceNamedParams
        //
        // GB4 Source : AllocationQueryBuilder.GET_PENDING_ALLOCATION_FORINDENT_BASED_ON_COMBINE_INDENT_BASED
        // ─────────────────────────────────────────────────────────────────────────

        public const string GET_PENDING_ALLOCATION_FORINDENT_BASED_ON_COMBINE_INDENT_BASED =
            @"SELECT DISTINCT
    COMBIND.INDENTID                        AS AllocationObjectHeaderId,
    COMBIND.INDENTNUMBER                    AS HeaderName,
    COMBIND.INDENTDATE                      AS HeaderDate,
    COMBIND.REFERENCENUMBER                 AS RefNumber,
    COMBIND.REFERENCEDATE                   AS RefDate,
    COMBIND.PARTYBRANCHID                   AS PartyBranchId,
    PARTYBRANCH.PARTYBRANCHNAME             AS PartyBranchName
FROM TALLOCATION A
    LEFT JOIN TINDENT IND
           ON A.OBJECTHEADERID     = IND.INDENTID
          AND A.OBJECTHEADERTYPEID = :objectheadertypeid
          AND IND.STATUS           = 1
    -- Resolve each child indent to its parent combine-indent.
    LEFT JOIN TINDENT         COMBIND ON IND.COMBINEINDENTID      = COMBIND.INDENTID
    LEFT JOIN TINDENTDETAIL   INDDTL  ON IND.INDENTID             = INDDTL.INDENTID
    LEFT JOIN TINDENTMATERIAL INDMTL  ON INDDTL.INDENTDETAILID    = INDMTL.INDENTDETAILID
    JOIN MBIZTRANSACTIONTYPE  C   ON A.BIZTRANSACTIONTYPEID  = C.BIZTRANSACTIONTYPEID
    JOIN MBIZTRANSACTIONCLASS D   ON C.BIZTRANSACTIONCLASSID = D.BIZTRANSACTIONCLASSID
    JOIN MBIZTRANSACTION      BIZ ON C.BIZTRANSACTIONID      = BIZ.BIZTRANSACTIONID
    JOIN VPENDINGALLOCATION   E   ON E.ALLOTEDALLOCATIONID   = A.ALLOCATIONID
    :HeaderTablePart
WHERE 1 = 1
    AND E.PENDINGQUANTITY > 0
";


        // ─────────────────────────────────────────────────────────────────────────
        // GET_PENDING_ALLOCATION_DOCUMENT_PRODUCTIONPLAN
        // MM Document head-type, compact mode (NeedAllTag = 0), production-plan
        // variant — adds warp-type/label (fabric design) columns that
        // GET_PENDING_ALLOCATION_DOCUMENT_NEW does not carry.
        // Used by : AllocationDAL.GetPendingNewListForProductionPlan
        //           (HeadTypeId == OBJECTMMHEAD && NeedAllTag == 0 branch)
        //
        // String-replace tokens (applied by DAL before execution):
        //   :HeaderReplacePart  SELECT column list
        //   :HeaderTablePart    additional JOIN tables (party/partybranch/address/city)
        //   :objectheadertypeid literal int via ReplaceNamedParams
        //   :objecttypeid       literal int via ReplaceNamedParams
        //
        // Preserved GB4 quirk: LABEL.LABELID = ITEM.ITEMID (not LABEL.ITEMID).
        // This looks like a mismatched join key, but it is GB4's actual deployed
        // behaviour (AllocationQueryBuilder.GET_PENDING_ALLOCATION_DOCUMENT_PRODUCTIONPLAN) —
        // kept exactly as-is rather than "fixed" during migration.
        //
        // GB4 Source : AllocationQueryBuilder.GET_PENDING_ALLOCATION_DOCUMENT_PRODUCTIONPLAN
        // ─────────────────────────────────────────────────────────────────────────

        public const string GET_PENDING_ALLOCATION_DOCUMENT_PRODUCTIONPLAN =
            @"SELECT
    :HeaderReplacePart
FROM TALLOCATION A
    LEFT JOIN TMMHEAD B
           ON A.OBJECTHEADERID     = B.DOCUMENTID
          AND A.OBJECTHEADERTYPEID = :objectheadertypeid
          AND A.OBJECTTYPEID       = :objecttypeid
    LEFT JOIN TMMDETAIL DTL ON B.DOCUMENTID = DTL.DOCUMENTID

    LEFT JOIN TINDENT IND
           ON A.OBJECTHEADERID     = IND.INDENTID
          AND A.OBJECTHEADERTYPEID = :objectheadertypeid
          AND A.OBJECTTYPEID       = :objecttypeid

    JOIN MBIZTRANSACTIONTYPE  C     ON A.BIZTRANSACTIONTYPEID  = C.BIZTRANSACTIONTYPEID
    JOIN MBIZTRANSACTIONCLASS D     ON C.BIZTRANSACTIONCLASSID = D.BIZTRANSACTIONCLASSID
    JOIN MBIZTRANSACTION      BIZ   ON C.BIZTRANSACTIONID      = BIZ.BIZTRANSACTIONID
    JOIN MITEM                ITEM  ON DTL.ITEMID              = ITEM.ITEMID
    JOIN MSKU                 MSKU  ON DTL.SKUID               = MSKU.SKUID
    JOIN MLABEL               LABEL ON LABEL.LABELID           = ITEM.ITEMID
    JOIN MGCM                 GCM   ON LABEL.WARPTYPEID         = GCM.GCMID
    JOIN VPENDINGALLOCATION   E     ON E.ALLOTEDALLOCATIONID    = A.ALLOCATIONID
    :HeaderTablePart
WHERE 1 = 1
    AND E.PENDINGQUANTITY > 0
";


        // ─────────────────────────────────────────────────────────────────────────
        // ApplyCriteria
        // Maps UI CriteriaDTO fields to SQL WHERE clauses via the provided
        // field-to-column dictionary.
        //
        // GB4 equivalent : CommonFunctionFrameDAL.ApplyCriteria  (used string[,] map)
        // GB5 change      : Map is now Dictionary<string,string> for type safety.
        //
        // TODO: Replace the throw with the actual GB5 shared criteria engine call.
        //       Inject ICommonQueryHelper or the equivalent from GB5Shared.
        // ─────────────────────────────────────────────────────────────────────────

        public static string ApplyCriteria(
            string Sql,
            Dictionary<string, string> CriteriaFieldMap,
            CriteriaDTO CriteriaDTO,
            LoginDTO LoginDTO)
        {
            throw new NotImplementedException(
                "ApplyCriteria must be wired to the GB5 shared criteria engine. " +
                "Inject ICommonQueryHelper or the equivalent from GB5Shared.");
        }

        // GET_SELECTLIST_TPENDINGALLOCATION moved to PendingAllocationQB.BuildPendingAllocationSelectList
        // — it now runs through the typed List-Query pipeline (IQueryBuilder + QueryContext +
        // SqlClauses) instead of a raw CriteriaBuilder-driven string constant here.
    }
}