using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using MMDAL.DTO.Allocation;
using MMDAL.DTO.BOM;
using MMDAL.Query.Allocation;
using MMDAL.Query.BOM;
using Newtonsoft.Json;
using static GB5Shared.GB5Constant.Constant;

namespace MMDAL.CustomCode.Allocation
{
    /// <summary>
    /// Data Access Layer for Allocation operations.
    /// Executes raw SQL via <see cref="IQueryExecutor"/> and returns JSON-serialized results.
    /// </summary>
    public class AllocationDAL : IAllocationDAL
    {
        private readonly IQueryExecutor _QueryExecutor;

        public AllocationDAL(IQueryExecutor QueryExecutor)
        {
            _QueryExecutor = QueryExecutor;
        }

        // ─────────────────────────────────────────────────────────────────────
        // GetAllocation
        // ─────────────────────────────────────────────────────────────────────

        /// <inheritdoc/>
        public async Task<string> GetAllocation(int AllocationId, LoginDTO LoginDTO)
        {
            try
            {
                string Sql = AllocationQB.GET_ALLOCATION;
                var Parameters = new { allocationid = AllocationId };
                AllocationDTO Result = await _QueryExecutor.QuerySingleAsync<AllocationDTO>(LoginDTO, Sql, Parameters);
                return JsonConvert.SerializeObject(Result);
            }
            catch (Exception)
            {
                throw;
            }
        }

        // ─────────────────────────────────────────────────────────────────────
        // LoadFromAllocationPicklist  (SQL Server path)
        // ─────────────────────────────────────────────────────────────────────

        /// <inheritdoc/>
        /// <remarks>
        /// GB4 Migration Notes:
        /// • Replaced nested for-loop criteria extraction with a single LINQ pass via
        ///   <see cref="ExtractCriteriaValue"/> helper — avoids the repeated if/else chain.
        /// • String concatenation SQL building is preserved as-is from GB4 because the
        ///   underlying query builder (<see cref="AllocationQB"/>) relies on placeholder
        ///   replacement tokens (:replacestring, :showonlyavailbletowork, etc.).
        ///   Parameterised binding for these dynamic fragments is not yet supported by
        ///   the query builder; migrate when the QB supports it.
        /// • SQL injection risk on HeaderName / ItemSearch: both values come from UI input
        ///   and are embedded with LIKE '% … %'. TODO: switch to parameterised LIKE once
        ///   the query executor supports it.
        /// </remarks>
        public async Task<string> LoadFromAllocationPicklist(
            int FirstNumber,
            int MaxResult,
            CriteriaDTO CriteriaDTO,
            LoginDTO LoginDTO)
        {
            try
            {
                // ── 1. Extract filter values from criteria ──────────────────
                int OUId = ExtractInt(CriteriaDTO, "ouid", -1);
                int ItemWiseTag = ExtractInt(CriteriaDTO, "itemwisetag", 1);
                int ShowOnlyAvailableToWork = ExtractInt(CriteriaDTO, "showonlyavailbletowork", 0);
                int LoadForBizTypeId = ExtractInt(CriteriaDTO, "loadforbiztypeid", 0);
                string HeaderName = ExtractString(CriteriaDTO, "headername");

                // ItemCode and ItemName both map to the same search string; last one wins (GB4 behaviour preserved).
                string ItemSearch = ExtractString(CriteriaDTO, "itemcode");
                if (string.IsNullOrEmpty(ItemSearch))
                    ItemSearch = ExtractString(CriteriaDTO, "itemname");

                // ── 2. Build item-wise SELECT fragment ──────────────────────
                // When itemwisetag == 0, expand the SELECT to include item/SKU columns.
                string ItemWiseSelectFragment = ItemWiseTag == 0
                    ? ",ItemId,ItemCode,ItemName,SKUId,SKUCode,SKUName,AllocationTotalQuantity as AllocationQuantity"
                    : string.Empty;

                // ── 3. Build base SQL from query builder ────────────────────
                string Sql = AllocationQB.GET_NEWALLOCATION_BASED_DOCNO;
                Sql = Sql.Replace(":replacestring", ItemWiseSelectFragment);
                Sql = Sql.Replace(":showonlyavailbletowork", ShowOnlyAvailableToWork.ToString());

                // ── 4. Apply biz-transaction-type pending quantity filter ────
                // When LoadForBizTypeId is provided, filter pending qty across all
                // quantity types (Good / Rejected / Rework / Other) based on the
                // MBIZTRANSACTIONTYPE configuration flags.
                if (LoadForBizTypeId != 0)
                {
                    Sql = Sql.Replace(":loadfrombiztype", ",MBIZTRANSACTIONTYPE Loadtotype");
                    Sql += "\r\n" +
                           "and((loadtotype.isgoodquantity     = 0 and a.pendingquantity         > 0)\r\n" +
                           "or (loadtotype.isrejectedquantity  = 0 and a.pendingrejectedquantity  > 0)\r\n" +
                           "or (loadtotype.isreworkquantity    = 0 and a.pendingreworkquantity    > 0)\r\n" +
                           "or (loadtotype.isotherquantity     = 0 and a.pendingotherquantity     > 0))\r\n";
                }
                else
                {
                    Sql = Sql.Replace(":loadfrombiztype", string.Empty);
                    Sql += "\r\n and a.pendingquantity > 0\r\n";
                }

                // ── 5. Optional OU filter ───────────────────────────────────
                if (OUId != -1)
                    Sql += $" and (a.OUID = {OUId})";

                // ── 6. Apply common picklist criteria (date range, biz class, etc.) ──
                Sql = ApplyAllocationPicklistCriteria(Sql, CriteriaDTO, LoginDTO);

                // ── 7. Optional document header / party reference search ────
                if (!string.IsNullOrWhiteSpace(HeaderName))
                    Sql += $" and (a.DOCUMENTNUMBER like '%{HeaderName}%' or a.PARTYREFERENCENUMBER like '%{HeaderName}%')";

                // ── 8. Optional item code / name search ────────────────────
                if (!string.IsNullOrWhiteSpace(ItemSearch))
                    Sql += $" and (item.ItemCode like '%{ItemSearch}%' or item.ItemName like '%{ItemSearch}%')";

                // ── 9. Close inner query and append GROUP BY + OFFSET/FETCH paging ─
                // GB4 paging was handled by the NHibernate lazy-loader (FirstNumber, MaxResult).
                // GB5 IQueryExecutor.QueryAsync has no skip/take overload, so pagination is
                // embedded directly into SQL using OFFSET/FETCH NEXT — SQL Server 2012+ syntax.
                Sql += " ) x ";
                Sql += ItemWiseTag == 1
                    ? AllocationQB.GROUPBY_NEWALLOCATION_BASED_DOCNO
                    : AllocationQB.GROUPBY_NEWALLOCATION_BASED_DOCNO_ITEM;

                // Append OFFSET/FETCH after ORDER BY (already included in GROUPBY constants).
                Sql += $"\r\nOFFSET {FirstNumber} ROWS FETCH NEXT {MaxResult} ROWS ONLY";

                // ── 10. Execute paged query ─────────────────────────────────
                // QueryAsync returns IEnumerable<T>; materialise with ToList() for serialisation.
                IEnumerable<AllocationNewDTO> Results = await _QueryExecutor
                    .QueryAsync<AllocationNewDTO>(LoginDTO, Sql);

                return JsonConvert.SerializeObject(Results.ToList());
            }
            catch (Exception)
            {
                throw;
            }
        }

        // ─────────────────────────────────────────────────────────────────────
        // GetPendingAllocationForOracle  (Oracle path — was GetPendingNew in GB4)
        // ─────────────────────────────────────────────────────────────────────

        /// <inheritdoc/>
        /// <remarks>
        /// GB4 Migration Notes:
        /// • Renamed from GetPendingNew → GetPendingAllocationForOracle to clearly
        ///   communicate its purpose and prevent accidental use on SQL Server.
        /// • Head-type branching (ObjectMMHead vs ObjectIndent) is preserved.
        /// • The nested for-loop criteria extraction is replaced with helper methods.
        /// • FilterOuId overriding OUId logic is preserved exactly as in GB4.
        /// • NeedAllTag == 1 (full item detail expand) is preserved under ObjectMMHead.
        /// </remarks>
        public async Task<string> GetPendingAllocationForOracle(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            try
            {
                // ── 1. Extract all filter values ────────────────────────────
                int BizClassId = ExtractInt(CriteriaDTO, "biztransactionclassid", 0);
                int HeadTypeId = ExtractInt(CriteriaDTO, "objectheadertypeid", 0);
                int ObjTypeId = ExtractInt(CriteriaDTO, "objecttypeid", 0);
                int ItemWiseTag = ExtractInt(CriteriaDTO, "itemwisetag", -1);
                int ProcessId = ExtractInt(CriteriaDTO, "headerprocessid", 0);
                int OUId = ExtractInt(CriteriaDTO, "ouid", 0);
                int FilterOUId = ExtractInt(CriteriaDTO, "filterouid", -1);
                int PartyBranchId = ExtractInt(CriteriaDTO, "partybranchid", 0);
                int OrderId = ExtractInt(CriteriaDTO, "orderid", 0);
                int LoadForBizTypeId = ExtractInt(CriteriaDTO, "loadforbiztypeid", 0);
                int IsMultiple = ExtractInt(CriteriaDTO, "ismultiple", 0);
                int CombineIndent = ExtractInt(CriteriaDTO, "combineindent", -1);
                int ItemPartyTag = ExtractInt(CriteriaDTO, "itempartytag", -1);
                int NeedAllTag = ExtractInt(CriteriaDTO, "needall", 0);
                int Priority = ExtractInt(CriteriaDTO, "priority", 0);
                int PriorityTag = Priority != 0 ? 1 : 0;
                int BeamType = ExtractInt(CriteriaDTO, "beamtypetag", 0);
                int OnlyRemainingPackQty = ExtractInt(CriteriaDTO, "onlyremainingpackqty", 1);
                int AllocationId = ExtractInt(CriteriaDTO, "allocationid", -1);
                int IndentNature = ExtractInt(CriteriaDTO, "indentnature", 0);
                int Incharge = ExtractInt(CriteriaDTO, "incharge", 0);
                int WorkCenterId = ExtractInt(CriteriaDTO, "workcenterid", 0);
                int FabricId = ExtractInt(CriteriaDTO, "fabricid", 0);
                int MerchId = ExtractInt(CriteriaDTO, "merchandiserid", 0);
                int MachineTypeId = ExtractInt(CriteriaDTO, "machinetypeid", 0);
                int MachineId = ExtractInt(CriteriaDTO, "machineid", 0);
                int StoreId = ExtractInt(CriteriaDTO, "storeid", 0);

                string PartyId = ExtractString(CriteriaDTO, "partyid");
                if (string.IsNullOrEmpty(PartyId))
                    PartyId = ExtractString(CriteriaDTO, "partybranchparty.id");
                string ItemIds = ExtractString(CriteriaDTO, "itemids");
                string ItemCode = ExtractString(CriteriaDTO, "itemcode");
                string HeaderName = ExtractString(CriteriaDTO, "headername");
                string IndentObjectTypeId = ExtractString(CriteriaDTO, "indentobjecttypeid");

                // FilterOuId overrides OUId when explicitly provided (GB4 behaviour preserved).
                if (FilterOUId != -1)
                    OUId = FilterOUId;

                string Sql = string.Empty;

                // ── 2. MM Document head type branch ─────────────────────────
                if (HeadTypeId == EntityConstant.OBJECTMMHEAD)
                {
                    Sql = BuildMMHeadQuery(
                        BizClassId, ItemWiseTag, NeedAllTag, LoadForBizTypeId,
                        OnlyRemainingPackQty, AllocationId, OUId, PartyId,
                        ItemCode, HeaderName, ItemPartyTag, CriteriaDTO, LoginDTO);
                }
                // ── 3. Indent head type branch ───────────────────────────────
                else if (HeadTypeId == EntityConstant.OBJECTINDENT)
                {
                    Sql = BuildIndentQuery(
                        BizClassId, IsMultiple, CombineIndent, OUId,
                        PartyBranchId, ItemCode, CriteriaDTO, LoginDTO);
                }

                // ── 4. Replace named parameters for head/object types ────────
                Sql = ReplaceNamedParams(Sql,
                    new[] { "objectheadertypeid", "objecttypeid" },
                    new object[] { HeadTypeId, ObjTypeId });

                // ── 5. Execute and return ────────────────────────────────────
                // QueryAsync returns IEnumerable<T>; materialise with ToList() for serialisation.
                IEnumerable<AllocationNewDTO> Results = await _QueryExecutor
                    .QueryAsync<AllocationNewDTO>(LoginDTO, Sql);

                return JsonConvert.SerializeObject(Results.ToList());
            }
            catch (Exception)
            {
                throw;
            }
        }

        /// <inheritdoc/>
        /// <remarks>
        /// GB4 Source: AllocationBLL.GetPendingNewListForProductionPlan ->
        /// AllocationDAL.GetPendingNewListForProductionPlan ("production plan For Hilife").
        /// GB4 route: /Allocation/PendingAllocationSelectList — the closest textual match
        /// to GB5's route name "GetSelectListTPendingAllocation", which is why this is the
        /// method actually wired to that endpoint (not GetPendingSelectList, whose real
        /// GB4 route is /Allocation/SelectList and returns the simpler AllocationDTO shape).
        ///
        /// GB4 Migration Notes:
        /// • Only PartyId and OUId are read from criteria and actually used in the query —
        ///   GB4 extracts many more fields (WorkCenterId, FabricId, MerchId, Priority, etc.)
        ///   but never references them in this specific method body; that dead extraction
        ///   is not reproduced here.
        /// • HeadTypeId/NeedAllTag branching is preserved exactly:
        ///     - OBJECTMMHEAD && NeedAllTag == 0 → GET_PENDING_ALLOCATION_DOCUMENT_PRODUCTIONPLAN
        ///       (warp-type/label columns, the "Hilife" production-plan variant)
        ///     - OBJECTMMHEAD && NeedAllTag == 1 → GET_PENDING_ALLOCATION_FORALL (shared with
        ///       GetPendingAllocationForOracle's full-mode branch)
        ///     - OBJECTINDENT                    → GET_PENDING_ALLOCATION_FORINDENT (shared)
        /// • ApplyGetPendingNewSources (GB4's generic criteria-field mapper) is not called
        ///   here, matching GetPendingAllocationForOracle's existing precedent — GB5's
        ///   AllocationQB.ApplyCriteria is not yet wired to the shared criteria engine.
        /// </remarks>
        public async Task<string> GetPendingNewListForProductionPlan(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            try
            {
                int HeadTypeId = ExtractInt(CriteriaDTO, "objectheadertypeid", 0);
                int ObjTypeId = ExtractInt(CriteriaDTO, "objecttypeid", 0);
                int NeedAllTag = ExtractInt(CriteriaDTO, "needall", 0);
                int PartyId = ExtractInt(CriteriaDTO, "partyid", 0);
                int PartyBranchId = ExtractInt(CriteriaDTO, "partybranchid", 0);
                int OUId = ExtractInt(CriteriaDTO, "ouid", 0);

                string Sql;

                if (HeadTypeId == EntityConstant.OBJECTMMHEAD)
                {
                    if (NeedAllTag == 0)
                    {
                        Sql = AllocationQB.GET_PENDING_ALLOCATION_DOCUMENT_PRODUCTIONPLAN;
                        Sql = Sql.Replace(":HeaderTablePart", ",MPARTY PARTY,MPARTYBRANCH PARTYBRANCH,MADDRESS ADDRESS,MCITY CITY");
                        Sql += " AND B.PARTYID = PARTY.PARTYID" +
                               " AND PARTYBRANCH.PARTYBRANCHID = B.PARTYBRANCHID" +
                               " AND PARTYBRANCH.DEFAULTADDRESSID = ADDRESS.ADDRESSID" +
                               " AND ADDRESS.CITYID = CITY.CITYID";

                        if (PartyId != 0)
                            Sql += $" AND B.PARTYID = {PartyId}";
                        if (OUId != 0)
                            Sql += $" AND B.OUID = {OUId}";

                        string SelectPart =
                            "DISTINCT B.DOCUMENTNUMBER AS HeaderName," +
                            "DTL.REQUIREDDELIVERYDATE AS DeliveryDate," +
                            "DTL.ACTUALQUANTITY AS ActualQuantity," +
                            "DTL.DOCUMENTDETAILID AS MMDetailId," +
                            "A.OBJECTHEADERID AS AllocationObjectHeaderId," +
                            "B.DOCUMENTDATE AS HeaderDate," +
                            "B.REFERENCENUMBER AS RefNumber," +
                            "B.REFERENCEDATE AS RefDate," +
                            "PARTY.PARTYID AS PartyId," +
                            "PARTY.PARTYNAME AS PartyName," +
                            "E.PENDINGQUANTITY AS AllocationQuantity," +
                            "LABEL.DESIGNNO AS DesignNo," +
                            "ITEM.ITEMID AS ItemId," +
                            "ITEM.ITEMNAME AS ItemName," +
                            "ITEM.ITEMCODE AS ItemCode," +
                            "MSKU.SKUID AS SKUId," +
                            "MSKU.SKUCODE AS SKUCode," +
                            "MSKU.SKUNAME AS SKUName," +
                            "PARTYBRANCH.PARTYBRANCHID AS PartyBranchId," +
                            "PARTYBRANCH.PARTYBRANCHNAME AS PartyBranchName," +
                            "LABEL.WARPTYPEID AS WarpTypeId," +
                            "GCM.GCMCODE AS WarpTypeCode," +
                            "GCM.GCMNAME AS WarpTypeName," +
                            "LABEL.LABELID AS LabelId," +
                            "LABEL.LABELCODE AS LabelCode," +
                            "LABEL.LABELNAME AS LabelName," +
                            "LABEL.NOOFREPEATS AS Repeats";
                        Sql = Sql.Replace(":HeaderReplacePart", SelectPart);
                    }
                    else
                    {
                        Sql = AllocationQB.GET_PENDING_ALLOCATION_FORALL;
                        Sql = Sql.Replace(":HeaderTablePart", ",MPARTY PARTY,MPARTYBRANCH PARTYBRANCH,MSKU SKU,MITEM ITEM");
                        Sql += " AND B.PARTYID = PARTY.PARTYID" +
                               " AND PARTYBRANCH.PARTYBRANCHID = B.PARTYBRANCHID" +
                               " AND DTL.ITEMID = ITEM.ITEMID" +
                               " AND DTL.SKUID = SKU.SKUID";

                        if (PartyId != 0)
                            Sql += $" AND B.PARTYID = {PartyId}";
                        if (OUId != 0)
                            Sql += $" AND B.OUID = {OUId}";

                        string SelectPart =
                            ",B.DOCUMENTNUMBER AS HeaderName," +
                            "B.DOCUMENTDATE AS HeaderDate," +
                            "B.REFERENCENUMBER AS RefNumber," +
                            "B.REFERENCEDATE AS RefDate," +
                            "PARTY.PARTYID AS PartyId," +
                            "PARTY.PARTYNAME AS PartyName," +
                            "PARTYBRANCH.PARTYBRANCHID AS PartyBranchId," +
                            "PARTYBRANCH.PARTYBRANCHNAME AS PartyBranchName," +
                            "ITEM.ITEMID AS ItemId," +
                            "ITEM.ITEMCODE AS ItemCode," +
                            "ITEM.ITEMNAME AS ItemName," +
                            "SKU.SKUID AS SKUId," +
                            "SKU.SKUCODE AS SKUCode," +
                            "SKU.SKUNAME AS SKUName," +
                            "DTL.TRANSACTIONACTUALQUANTITY AS ActualQuantity";
                        Sql = Sql.Replace(":HeaderReplacePart", SelectPart);
                    }

                    Sql += " AND A.OBJECTID = DTL.DOCUMENTDETAILID";
                    Sql += " ORDER BY B.DOCUMENTNUMBER";
                }
                else if (HeadTypeId == EntityConstant.OBJECTINDENT)
                {
                    Sql = AllocationQB.GET_PENDING_ALLOCATION_FORINDENT;
                    Sql = Sql.Replace(":HeaderTablePart", ",MPARTYBRANCH PARTYBRANCH,MSKU SKU,MITEM ITEM");
                    Sql += " AND PARTYBRANCH.PARTYBRANCHID = IND.PARTYBRANCHID" +
                           " AND INDDTL.ITEMID = ITEM.ITEMID" +
                           " AND INDDTL.SKUID = SKU.SKUID";

                    if (PartyBranchId != 0)
                        Sql += $" AND IND.PARTYBRANCHID = {PartyBranchId}";
                    if (OUId != 0)
                        Sql += $" AND IND.OUID = {OUId}";

                    string SelectPart =
                        ",IND.INDENTNUMBER AS HeaderName," +
                        "IND.INDENTDATE AS HeaderDate," +
                        "IND.REFERENCENUMBER AS RefNumber," +
                        "IND.REFERENCEDATE AS RefDate," +
                        "IND.PARTYBRANCHID AS PartyBranchId," +
                        "PARTYBRANCH.PARTYBRANCHNAME AS PartyBranchName";
                    Sql = Sql.Replace(":HeaderReplacePart", SelectPart);

                    Sql += " AND A.OBJECTID = INDMTL.INDENTMATERIALID AND A.ALLOCATIONNATURE = 1";
                }
                else
                {
                    return JsonConvert.SerializeObject(new List<AllocationNewDTO>());
                }

                Sql = ReplaceNamedParams(Sql,
                    new[] { "objectheadertypeid", "objecttypeid" },
                    new object[] { HeadTypeId, ObjTypeId });

                IEnumerable<AllocationNewDTO> Results = await _QueryExecutor
                    .QueryAsync<AllocationNewDTO>(LoginDTO, Sql);

                return JsonConvert.SerializeObject(Results.ToList());
            }
            catch (Exception)
            {
                throw;
            }
        }

        // ─────────────────────────────────────────────────────────────────────
        // Private helpers
        // ─────────────────────────────────────────────────────────────────────

        /// <summary>
        /// Applies the standard picklist criteria mapping (biz class, date range, party, item, etc.)
        /// to the SQL string using the common criteria engine.
        /// Mirrors GB4's <c>ApplyLoadFromAllocationPicklist</c>.
        /// </summary>
        private string ApplyAllocationPicklistCriteria(string Sql, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            if (CriteriaDTO == null) return Sql;

            // Maps UI criteria field names → SQL column aliases used in the query.
            var CriteriaFieldMap = new Dictionary<string, string>
            {
                { "BizTransactionClassId",    "d.BIZTRANSACTIONCLASSID"       },
                { "BizTransactionSubClassId", "c.BIZTRANSACTIONSUBCLASSID"     },
                { "HeaderIndentName",         "a.DOCUMENTNUMBER"               },
                { "Nature",                   "a.ALLOCATIONNATURE"             },
                { "BizTransactionTypeId",     "a.BizTransactionTypeId"         },
                { "ObjectHeaderTypeId",       "a.ObjectHeaderTypeId"           },
                { "ObjectHeaderId",           "a.ObjectHeaderId"               },
                { "ObjectTypeId",             "a.ObjectTypeId"                 },
                { "indentobjecttypeid",       "a.ObjectTypeId"                 },
                { "ObjectId",                 "a.ObjectId"                     },
                { "ProcessId",                "a.ProcessId"                    },
                { "ItemId",                   "dtl.ItemId"                     },
                { "PartyId",                  "party.PARTYID"                  },
                { "DocumentId",               "a.OBJECTHEADERID"               },
                { "BizTransactionId",         "biz.BIZTRANSACTIONID"           },
                { "loadforbiztypeid",         "loadtotype.BIZTRANSACTIONTYPEID"},
                { "IndentId",                 "ind.INDENTID"                   },
                { "PeriodFromDate",           "a.DOCUMENTDATE"                 },
                { "PeriodToDate",             "a.DOCUMENTDATE"                 },
            };

            return AllocationQB.ApplyCriteria(Sql, CriteriaFieldMap, CriteriaDTO, LoginDTO);
        }

        /// <summary>
        /// Builds the SQL fragment for MM Document head-type pending allocation queries.
        /// Handles both compact (NeedAllTag == 0) and full item-detail (NeedAllTag == 1) modes.
        /// </summary>
        private string BuildMMHeadQuery(
            int BizClassId, int ItemWiseTag, int NeedAllTag, int LoadForBizTypeId,
            int OnlyRemainingPackQty, int AllocationId, int OUId, string PartyId,
            string ItemCode, string HeaderName, int ItemPartyTag,
            CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            string Sql;

            if (NeedAllTag == 0)
            {
                // ── Compact mode: document-level grouping ──────────────────
                Sql = AllocationQB.GET_PENDING_ALLOCATION_DOCUMENT_NEW;

                // Filter to only rows where remaining pack quantity > 0 (Added Apr 2023, Redmine #38762).
                if (OnlyRemainingPackQty == 0)
                    Sql += "\r\n  and Isnull(dtl.GOODQUANTITY,0) - isnull(packdtl.GOODQUANTITY,0) > 0\r\n";

                // Purchase Requisitions carry PARTYID = -1 so we must include that
                // to avoid filtering them out when IsItemFromParty is enabled.
                if (!string.IsNullOrEmpty(PartyId))
                    Sql += $"  and (b.PARTYID in ({PartyId}) or b.PARTYID = -1)";

                if (OUId != 0)
                    Sql += $"  and (b.OUID = {OUId} or dtl.INTEROUID = {OUId})";

                if (AllocationId != -1)
                    Sql += $"  and (b.AllocationId = {AllocationId} or dtl.AllocationId = {AllocationId})";

                // Build SELECT replacement part based on item-wise grouping flag.
                string SelectPart = BuildMMHeadSelectPart(ItemWiseTag);
                Sql = Sql.Replace(":HeaderReplacePart", SelectPart);
                Sql = Sql.Replace(":loadfrombiztype",
                    LoadForBizTypeId != 0 ? ",MBIZTRANSACTIONTYPE Loadtotype" : string.Empty);

                // Pending quantity filter: biz-type-aware or simple > 0.
                Sql = AppendPendingQtyFilter(Sql, LoadForBizTypeId, LoadForBizTypeId != 0,
                    UseAlias: "a", RemoveExisting: "and a.PENDINGQUANTITY>0");

                Sql += " AND B.STATUS IN (1)\r\n";

                if (ItemPartyTag == 0)
                    Sql += " and itp.ITEMPARTYID=itpd.ITEMPARTYID and itp.PARTYID=b.PARTYID and itp.ITEMID=dtl.ITEMID";

                if (!string.IsNullOrWhiteSpace(HeaderName))
                    Sql += $" AND (b.DOCUMENTNUMBER like '%{HeaderName}%' or b.partyreferencenumber like '%{HeaderName}%')";

                Sql += " order by b.DOCUMENTNUMBER";
            }
            else
            {
                // ── Full mode: item-level detail including SKU, UOM, actual qty ──
                Sql = AllocationQB.GET_PENDING_ALLOCATION_FORALL;

                string FromPart = ",MPARTY party,MPARTYBRANCH partybranch,MSKU sku,MITEM item,MALLOCATION mal,MUSER mod,MUOM stkuom,MUOM puruom";
                if (LoadForBizTypeId != 0)
                    FromPart += ",MBIZTRANSACTIONTYPE Loadtotype";

                Sql = Sql.Replace(":HeaderTablePart", FromPart);

                Sql += " and b.PARTYID=party.PARTYID" +
                       " and partybranch.PARTYBRANCHID=b.PARTYBRANCHID" +
                       " and dtl.ITEMID=item.ITEMID" +
                       " and dtl.SKUID=sku.SKUID" +
                       " and mal.ALLOCATIONID=b.ALLOCATIONID" +
                       " and item.STOCKUOMID=stkuom.UOMID" +
                       " and item.PURCHASEUOMID=puruom.UOMID";

                if (!string.IsNullOrEmpty(PartyId))
                    Sql += $"  and b.PARTYID in ({PartyId})";

                if (OUId != 0)
                    Sql += $"  and (b.OUID = {OUId} or dtl.INTEROUID = {OUId})";

                Sql += " and mod.USERID=b.CREATEDBYID";

                string SelectPart = BuildMMHeadFullSelectPart();
                Sql = Sql.Replace(":HeaderReplacePart", SelectPart);

                Sql = AppendPendingQtyFilter(Sql, LoadForBizTypeId, LoadForBizTypeId != 0,
                    UseAlias: "e", RemoveExisting: "and e.PENDINGQUANTITY>0");

                Sql += " AND B.STATUS IN (1)";

                if (!string.IsNullOrWhiteSpace(ItemCode))
                    Sql += $" AND (item.ItemCode LIKE ('%{ItemCode}%') or item.ItemName LIKE ('%{ItemCode}%'))";
            }

            Sql += " and a.OBJECTID=dtl.DOCUMENTDETAILID";
            return Sql;
        }

        /// <summary>
        /// Builds the compact MM-head SELECT replacement clause.
        /// Item-wise grouping (ItemWiseTag == -1) omits pending quantity columns.
        /// </summary>
        private static string BuildMMHeadSelectPart(int ItemWiseTag)
        {
            string Base =
                "distinct b.DOCUMENTNUMBER as HeaderName," +
                "a.OBJECTHEADERID as AllocationObjectHeaderId," +
                "b.DOCUMENTDATE as HeaderDate," +
                "b.ALLOCATIONID as DocumentAllocationId," +
                "mal.ALLOCATIONNAME as DocumentAllocationName," +
                "ISNULL(b.AMENDMENTSLNO,0) as AmendmentSlNo," +
                "b.PARTYREFERENCENUMBER as RefNumber," +
                "b.PARTYREFERENCEDATE as RefDate," +
                "b.TOTALQUANTITY as AllocationTotalQuantity," +
                "b.REFERENCENUMBER as HeadReferenceNumber,";

            Base += ItemWiseTag == -1
                ? "ISNULL(party.PARTYID,-1) as PartyId,ISNULL(party.PARTYNAME,'') as PartyName,"
                : "ISNULL(party.PARTYID,-1) as PartyId,ISNULL(party.PARTYNAME,'NONE') as PartyName," +
                  "e.PENDINGQUANTITY as AllocationQuantity," +
                  "e.PENDINGREJECTEDQUANTITY as AllocationRejectedQuantity," +
                  "e.PENDINGREWORKQUANTITY as AllocationReworkQuantity," +
                  "e.PENDINGOTHERQUANTITY as AllocationOtherQuantity," +
                  "ISNULL(UOM.UOMNAME,'') as StockUOMName";

            Base += " partybranch.PARTYBRANCHID as PartyBranchId," +
                    "partybranch.PARTYBRANCHNAME as PartyBranchName," +
                    "mod.USERNAME as ModifiedBy";

            return Base;
        }

        /// <summary>
        /// Builds the full item-detail SELECT replacement clause used when NeedAllTag == 1.
        /// Includes SKU, UOM (stock and purchase), actual quantity, and modifier fields.
        /// Added STOCKUOMID and following fields for Redmine #37788 (Jun 2023).
        /// </summary>
        private static string BuildMMHeadFullSelectPart()
        {
            return ",b.DOCUMENTNUMBER as HeaderName," +
                   "b.DOCUMENTDATE as HeaderDate," +
                   "b.ALLOCATIONID as DocumentAllocationId," +
                   "mal.ALLOCATIONNAME as DocumentAllocationName," +
                   "b.PARTYREFERENCENUMBER as RefNumber," +
                   "b.PARTYREFERENCEDATE as RefDate," +
                   "party.PARTYID as PartyId," +
                   "party.PARTYNAME as PartyName," +
                   "partybranch.PARTYBRANCHID as PartyBranchId," +
                   "partybranch.PARTYBRANCHNAME as PartyBranchName," +
                   "item.ITEMID as ItemId," +
                   "item.ITEMCODE as ItemCode," +
                   "item.ITEMNAME as ItemName," +
                   "sku.SKUID as SKUId," +
                   "sku.SKUCODE as SKUCode," +
                   "sku.SKUNAME as SKUName," +
                   "dtl.SLNO as SlNo," +
                   "dtl.TRANSACTIONACTUALQUANTITY as ActualQuantity," +
                   "mod.USERNAME as ModifiedBy," +
                   "item.STOCKUOMID as StockUOMId," +
                   "stkuom.UOMCODE as StockUOMCode," +
                   "stkuom.UOMNAME as StockUOMName," +
                   "item.PURCHASEUOMID as PurchaseUOMId," +
                   "puruom.UOMCODE as PurchaseUOMCode," +
                   "puruom.UOMNAME as PurchaseUOMName";
        }

        /// <summary>
        /// Builds the SQL fragment for Indent head-type pending allocation queries.
        /// Supports single-line, multi-BOM, and combine-indent query variants.
        /// </summary>
        private string BuildIndentQuery(
            int BizClassId, int IsMultiple, int CombineIndent, int OUId,
            int PartyBranchId, string ItemCode, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            string Sql;

            if (IsMultiple == 1)
            {
                Sql = AllocationQB.GET_PENDING_ALLOCATION_FORINDENT_NEW;

                // BIZ Class -1399999916 indicates a multi-routing/BOM scenario (Redmine CR #31009, Jan 2021).
                if (BizClassId == -1399999916)
                    Sql += "\r\n and (posteddtl.ROUTINGDETAILSLNO=1 or ISNULL(bomitemsection.Indmatcount,0)>1)\r\n";
            }
            else if (IsMultiple == 0)
            {
                Sql = AllocationQB.GET_PENDING_ALLOCATION_FORINDENT;
            }
            else
            {
                throw new InvalidOperationException("IsMultiple value must be 0 or 1 for Indent head type.");
            }

            // Combine-indent overrides the normal indent query when requested.
            if (CombineIndent == 0)
                Sql = AllocationQB.GET_PENDING_ALLOCATION_FORINDENT_BASED_ON_COMBINE_INDENT_BASED;

            string FromPart = ",MPARTYBRANCH partybranch,MSKU sku,MITEM item";
            Sql = Sql.Replace(":HeaderTablePart", FromPart);

            Sql += " and partybranch.PARTYBRANCHID=ind.PARTYBRANCHID" +
                   " and inddtl.ITEMID=item.ITEMID" +
                   " and inddtl.SKUID=sku.SKUID";

            if (PartyBranchId != 0)
                Sql += $"  and ind.PARTYBRANCHID = {PartyBranchId}";

            if (OUId != 0)
                Sql += $"  and ind.OUID = {OUId}";

            string SelectPart =
                ",ind.IndentNumber as HeaderName," +
                "ind.IndentDate as HeaderDate," +
                "ind.ReferenceNumber as RefNumber," +
                "ind.ReferenceDate as RefDate," +
                "ind.PartyBranchId as PartyBranchId," +
                "partybranch.PartyBranchName as PartyBranchName," +
                "item.ITEMID as ItemId," +
                "item.ITEMCODE as ItemCode," +
                "item.ITEMNAME as ItemName";

            Sql = Sql.Replace(":HeaderReplacePart", SelectPart);

            // Indent queries use INDENTID instead of DOCUMENTID in criteria.
            Sql = Sql.Replace("and b.DOCUMENTID", "and ind.INDENTID");

            Sql += " and (a.OBJECTID=inddtl.INDENTDETAILID or a.OBJECTID=indmtl.INDENTMATERIALID)";

            if (!string.IsNullOrWhiteSpace(ItemCode))
                Sql += $" AND (item.ItemCode LIKE ('%{ItemCode}%') or item.ItemName LIKE ('%{ItemCode}%'))";

            return Sql;
        }

        /// <summary>
        /// Appends the pending quantity WHERE clause.
        /// When <paramref name="UseBizTypeFilter"/> is true, removes the simple
        /// <c>pendingquantity &gt; 0</c> placeholder and adds per-quantity-type conditions
        /// based on MBIZTRANSACTIONTYPE flags (Good / Rejected / Rework / Other).
        /// </summary>
        private static string AppendPendingQtyFilter(
            string Sql,
            int LoadForBizTypeId,
            bool UseBizTypeFilter,
            string UseAlias,
            string RemoveExisting)
        {
            if (UseBizTypeFilter)
            {
                Sql = Sql.Replace(RemoveExisting, string.Empty);
                Sql += $" and Loadtotype.BIZTRANSACTIONTYPEID = {LoadForBizTypeId}\r\n";
                Sql += $"and (\r\n" +
                       $"  (loadtotype.isgoodquantity    = 0 and {UseAlias}.pendingquantity         > 0) or\r\n" +
                       $"  (loadtotype.isrejectedquantity = 0 and {UseAlias}.pendingrejectedquantity > 0) or\r\n" +
                       $"  (loadtotype.isreworkquantity  = 0 and {UseAlias}.pendingreworkquantity   > 0) or\r\n" +
                       $"  (loadtotype.isotherquantity   = 0 and {UseAlias}.pendingotherquantity    > 0)\r\n" +
                       $")";
            }
            else
            {
                Sql += $" and {UseAlias}.PENDINGQUANTITY > 0\r\n";
            }

            return Sql;
        }

        /// <summary>
        /// Replaces named SQL parameters (e.g. :objectheadertypeid) with their runtime values.
        /// Mirrors GB4's <c>CommonFunctionFrameDAL.ChangedByNamedParam</c>.
        /// </summary>
        private static string ReplaceNamedParams(string Sql, string[] Names, object[] Values)
        {
            for (int i = 0; i < Names.Length; i++)
                Sql = Sql.Replace($":{Names[i]}", Values[i]?.ToString() ?? string.Empty);

            return Sql;
        }

        // ─────────────────────────────────────────────────────────────────────
        // Criteria extraction helpers
        // ─────────────────────────────────────────────────────────────────────

        /// <summary>
        /// Finds the first matching criteria attribute by field name (case-insensitive)
        /// and returns its integer value, or <paramref name="DefaultValue"/> if not found.
        /// </summary>
        private static int ExtractInt(CriteriaDTO CriteriaDTO, string FieldName, int DefaultValue)
        {
            string? Raw = FindCriteriaValue(CriteriaDTO, FieldName);
            return Raw != null && int.TryParse(Raw, out int Parsed) ? Parsed : DefaultValue;
        }

        /// <summary>
        /// Finds the first matching criteria attribute by field name (case-insensitive)
        /// and returns its string value, or <see cref="string.Empty"/> if not found.
        /// </summary>
        private static string ExtractString(CriteriaDTO CriteriaDTO, string FieldName)
        {
            return FindCriteriaValue(CriteriaDTO, FieldName) ?? string.Empty;
        }

        /// <summary>
        /// Iterates the criteria sections and attributes to find a field value by name.
        /// Returns null when the field is absent.
        /// </summary>
        private static string? FindCriteriaValue(CriteriaDTO CriteriaDTO, string FieldName)
        {
            if (CriteriaDTO == null) return null;

            string Key = FieldName.ToLower();
            foreach (var Section in CriteriaDTO.SectionCriteriaList)
            {
                foreach (var Attribute in Section.AttributesCriteriaList)
                {
                    if (Attribute.FieldName.ToLower() == Key)
                        return Attribute.FieldValue?.ToString();
                }
            }

            return null;
        }


        // GetSelectListTPendingAllocation moved to MMBLL.Allocation.PendingAllocationBLL /
        // MMDAL.Query.Allocation.PendingAllocationSelectListQB — it now runs through the
        // typed List-Query pipeline (IQueryBuilder + GenericListHandler) instead of raw
        // CriteriaDTO-driven SQL here, so it gets typed criteria binding and no longer
        // depends on the generic CriteriaBuilder blindly turning every client field into
        // a literal SQL column reference.
    }
}