using Dapper;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.ListQuery;
using MMDAL.DTO.Allocation;
using System.Collections.Generic;

namespace MMDAL.Query.Allocation
{
    // ─────────────────────────────────────────────────────────────────────
    //  PendingAllocationQB  – SQL builders for all pending-allocation variants
    //
    //  Rules:
    //    • Each Build* method returns (string sql, DynamicParameters p)
    //    • QueryContext provides alias-qualified column references and
    //      schema-qualified table names
    //    • SqlClauses provides reusable, composable WHERE filters
    //    • ISqlDialect handles paging + dialect-specific syntax
    //    • No user-supplied values are ever concatenated into SQL strings
    //
    //  Indexes required:
    //    TPENDINGALLOCATION: (OUID, BIZTRANSACTIONTYPEID, ALLOCATIONNATURE, PENDINGQUANTITY)
    //    mbiztransactiontype: (BIZTRANSACTIONTYPEID, ISSELECTIONREQUIRED)
    //    MALLOCATION:        (ALLOCATIONID)
    // ─────────────────────────────────────────────────────────────────────
    public static class PendingAllocationQB
    {
        // ── Shared GB5 structural constants ───────────────────────────────
        // ObjectHeaderTypeId (from GB5Shared.GB5Constant.Constant.EntityConstant)
        private const int OBJECTINDENT = -1899997569;

        // AllocationType: 1 = From; AllocationNature: 1 = Normal pending
        private const int ALLOCATIONTYPE_FROM     = 1;
        private const int ALLOCATIONNATURE_NORMAL = 1;

        // ── Shared fbt subquery ───────────────────────────────────────────
        // Resolves the set of valid (biztransactiontypeid, selectiontype) pairs
        // for the requested ForBizTransactionTypeId via the selection rules in
        // mbiztransactiontype. ISSELECTIONREQUIRED = 0 means auto-selection only.
        private static string FbtSubquery(ISqlDialect d, string paramName = "forBizTypeId") =>
            $@"(
           SELECT DISTINCT fbt.biztransactiontypeid, fbt.selectiontype
           FROM   mbiztransactiontype sbt
           JOIN   mbiztransactiontype fbt ON (
                      (sbt.selectionclassid          = fbt.biztransactionclassid    OR sbt.selectionclassid          = -1)
                  AND (sbt.selectionsubclassid       = fbt.biztransactionsubclassid OR sbt.selectionsubclassid       = -1)
                  AND (sbt.SELECTIONBIZTRANSACTIONID = fbt.biztransactionid         OR sbt.SELECTIONBIZTRANSACTIONID = -1)
                  )
           WHERE  sbt.BIZTRANSACTIONTYPEID = {d.Param(paramName)}
           AND    sbt.ISSELECTIONREQUIRED  = 0
       ) fbt";

        // ── Pending MM Documents ──────────────────────────────────────────

        /// <summary>
        /// Header-level list: one row per pending document with aggregate pending quantity.
        /// Source: TPENDINGALLOCATION filtered through mbiztransactiontype selection rules.
        /// </summary>
        public static (string Sql, DynamicParameters Params) BuildPendingMMDocuments(
            PendingMMDocumentCriteria c, ISqlDialect d,
            string orderBy = "a.DOCUMENTDATE")
        {
            var ctx = new QueryContext(d).Register("pending", "a");

            var p = new DynamicParameters();
            p.Add("forBizTypeId", c.BizTypeId);
            p.Add("ouId",         c.OUId);

            var where = new System.Collections.Generic.List<string>
            {
                $"a.OUID           = {d.Param("ouId")}",
                "a.PENDINGQUANTITY > 0",
            };

            if (!string.IsNullOrWhiteSpace(c.SearchText))
            {
                p.Add("search", $"%{c.SearchText}%");
                where.Add($"a.DOCUMENTNUMBER LIKE {d.Param("search")}");
            }

            if (c.PartyIds is { Length: > 0 })
            {
                p.Add("partyIds", c.PartyIds);
                where.Add($"pt.PARTYID IN {d.Param("partyIds")}");
            }

            if (c.PartyBranchId.HasValue)
            {
                p.Add("partyBranchId", c.PartyBranchId.Value);
                where.Add($"a.PARTYBRANCHID = {d.Param("partyBranchId")}");
            }

            // Legacy MMHead.svc/MMHeadAllocation parity filters — scope to one specific
            // document and narrow by biz class / nature / store when supplied.
            if (c.DocumentId.HasValue)
            {
                p.Add("documentId", c.DocumentId.Value);
                where.Add($"a.OBJECTHEADERID = {d.Param("documentId")}");
            }

            if (c.Nature.HasValue)
            {
                p.Add("nature", c.Nature.Value);
                where.Add($"a.ALLOCATIONNATURE = {d.Param("nature")}");
            }

            if (c.BizTransactionClassId.HasValue)
            {
                p.Add("bizTransactionClassId", c.BizTransactionClassId.Value);
                where.Add($"abt.BIZTRANSACTIONCLASSID = {d.Param("bizTransactionClassId")}");
            }

            if (c.StoreId.HasValue)
            {
                p.Add("storeId", c.StoreId.Value);
                where.Add($"dtl.STOREID = {d.Param("storeId")}");
            }

            var whereSql = "WHERE " + string.Join("\nAND   ", where);
            var (pagingSql, pagingParams) = SqlClauses.Paging(ctx, c, orderBy: orderBy);

            var sql = $@"
SELECT {d.TotalCountExpr()},
       a.objectheadertypeid      AS ObjectHeaderTypeId,
       a.objectheaderid          AS AllocationId,
       a.documentnumber          AS HeaderName,
       a.documentdate            AS HeaderDate,
       a.REFERENCENUMBER         AS RefNumber,
       a.referencedate           AS RefDate,
       a.partyreferencenumber    AS PartyReferenceNumber,
       a.partyreferencedate      AS PartyReferenceDate,
       pt.PARTYID                AS PartyId,
       pt.PARTYNAME              AS PartyName,
       a.PARTYBRANCHID           AS PartyBranchId,
       pb.PARTYBRANCHNAME        AS PartyBranchName,
       SUM(a.PENDINGQUANTITY)    AS PendingQuantity,
       m.ALLOCATIONNAME          AS AllocationName,
       MAX(a.modifiedon)         AS LastModifiedOn,
       u.USERCODE                AS UserCode,
       u.USERNAME                AS UserName
FROM   {FbtSubquery(d)}
JOIN   TPENDINGALLOCATION a  ON  a.biztransactiontypeid = fbt.biztransactiontypeid
                             AND a.allocationnature     = fbt.selectiontype
JOIN   MALLOCATION        m  ON  m.ALLOCATIONID         = a.HEADERALLOCATIONID
JOIN   muser              u  ON  u.USERID               = a.CREATEDBYID
JOIN   Mpartybranch       pb ON  pb.PARTYBRANCHID       = a.PARTYBRANCHID
JOIN   Mparty             pt ON  pt.PARTYID             = pb.PARTYID
LEFT   JOIN mbiztransactiontype abt ON abt.BIZTRANSACTIONTYPEID = a.biztransactiontypeid
LEFT   JOIN TMMDETAIL     dtl ON  dtl.DOCUMENTDETAILID         = a.OBJECTID
{whereSql}
GROUP  BY
       a.objectheadertypeid,    a.objectheaderid,       a.documentnumber,       a.documentdate,
       a.REFERENCENUMBER,       a.referencedate,        a.partyreferencenumber, a.partyreferencedate,
       pt.PARTYID,              pt.PARTYNAME,           a.PARTYBRANCHID,        pb.PARTYBRANCHNAME,
       m.ALLOCATIONNAME,        u.USERCODE,             u.USERNAME
{pagingSql}";

            return (sql, SqlClause.Merge(p, pagingParams));
        }

        // ── Pending MM All Items ──────────────────────────────────────────

        /// <summary>
        /// Detail-level list: one row per pending line with item and SKU information.
        /// Source: TPENDINGALLOCATION filtered through mbiztransactiontype selection rules.
        /// </summary>
        public static (string Sql, DynamicParameters Params) BuildPendingMMAllItems(
            PendingMMAllItemsCriteria c, ISqlDialect d,
            string orderBy = "a.DOCUMENTDATE")
        {
            var ctx = new QueryContext(d).Register("pending", "a");

            var p = new DynamicParameters();
            p.Add("forBizTypeId", c.BizTypeId);
            p.Add("ouId",         c.OUId);

            var where = new System.Collections.Generic.List<string>
            {
                $"a.OUID           = {d.Param("ouId")}",
                "a.PENDINGQUANTITY > 0",
            };

            if (!string.IsNullOrWhiteSpace(c.SearchText))
            {
                p.Add("search", $"%{c.SearchText}%");
                where.Add($"a.DOCUMENTNUMBER LIKE {d.Param("search")}");
            }

            if (c.PartyIds is { Length: > 0 })
            {
                p.Add("partyIds", c.PartyIds);
                where.Add($"pt.PARTYID IN {d.Param("partyIds")}");
            }

            if (c.ItemId.HasValue)
            {
                p.Add("itemId", c.ItemId.Value);
                where.Add($"a.ITEMID = {d.Param("itemId")}");
            }

            var whereSql = "WHERE " + string.Join("\nAND   ", where);
            var (pagingSql, pagingParams) = SqlClauses.Paging(ctx, c, orderBy: orderBy);

            var sql = $@"
SELECT {d.TotalCountExpr()},
       a.objectheadertypeid      AS ObjectHeaderTypeId,
       a.objectheaderid          AS AllocationId,
       a.documentnumber          AS HeaderName,
       a.documentdate            AS HeaderDate,
       a.REFERENCENUMBER         AS RefNumber,
       a.referencedate           AS RefDate,
       a.partyreferencenumber    AS PartyReferenceNumber,
       a.partyreferencedate      AS PartyReferenceDate,
       pt.PARTYID                AS PartyId,
       pt.PARTYNAME              AS PartyName,
       a.PARTYBRANCHID           AS PartyBranchId,
       pb.PARTYBRANCHNAME        AS PartyBranchName,
       a.ITEMID                  AS ItemId,
       i.ITEMCODE                AS ItemCode,
       i.ITEMNAME                AS ItemName,
       a.SKUID                   AS SKUId,
       s.SKUCODE                 AS SKUCode,
       s.SKUNAME                 AS SKUName,
       a.PENDINGQUANTITY         AS PendingQuantity,
       m.ALLOCATIONNAME          AS AllocationName,
       a.modifiedon              AS LastModifiedOn,
       u.USERCODE                AS UserCode,
       u.USERNAME                AS UserName
FROM   {FbtSubquery(d)}
JOIN   TPENDINGALLOCATION a  ON  a.biztransactiontypeid = fbt.biztransactiontypeid
                             AND a.allocationnature     = fbt.selectiontype
JOIN   mitem              i  ON  i.ITEMID               = a.ITEMID
JOIN   msku               s  ON  s.SKUID                = a.SKUID
JOIN   MALLOCATION        m  ON  m.ALLOCATIONID         = a.HEADERALLOCATIONID
JOIN   muser              u  ON  u.USERID               = a.CREATEDBYID
JOIN   Mpartybranch       pb ON  pb.PARTYBRANCHID       = a.PARTYBRANCHID
JOIN   Mparty             pt ON  pt.PARTYID             = pb.PARTYID
{whereSql}
{pagingSql}";

            return (sql, SqlClause.Merge(p, pagingParams));
        }

        // ── Pending Indents ───────────────────────────────────────────────

        /// <summary>
        /// Indent-level pending list (OBJECTINDENT).
        /// Used when ObjectHeaderTypeId == OBJECTINDENT.
        /// </summary>
        public static (string Sql, DynamicParameters Params) BuildPendingIndents(
            PendingIndentCriteria c, ISqlDialect d,
            string orderBy = "ih.INDENTID DESC")
        {
            var ctx = new QueryContext(d)
                .Register("allocation", "a")
                .Register("header",     "ih")   // tindent (header)
                .Register("detail",     "id")   // tindentdetail
                .Register("pending",    "id")   // same alias — pending cols on detail
                .Register("document",   "ih")   // for OUFilter (OUID on indent header)
                .Register("item",       "it")
                .Register("sku",        "sk")
                .Register("party",      "pt");

            var (whereSql, whereParams) = SqlClause.AsWhere(new[]
            {
                SqlClauses.HeaderType(ctx, OBJECTINDENT),
                SqlClauses.AllocationType(ctx, ALLOCATIONTYPE_FROM),
                SqlClauses.AllocationNature(ctx, ALLOCATIONNATURE_NORMAL),
                SqlClauses.BizType(ctx, c.BizTypeId),
                SqlClauses.OUFilterSingle(ctx, c.OUId, "document"),
                SqlClauses.PartyFilter(ctx, c.PartyIds),
                SqlClauses.IndentSearch(ctx, c.SearchText),
                SqlClauses.IndentStatus(ctx, status: 1),
                SqlClauses.PendingOnly(ctx),
            });

            var (pagingSql, pagingParams) = SqlClauses.Paging(ctx, c, orderBy: orderBy);

            var sql = $@"
SELECT {d.TotalCountExpr()},
       a.ALLOCATIONID              AS AllocationId,
       ih.INDENTNUMBER             AS HeaderName,
       ih.INDENTDATE               AS HeaderDate,
       ih.REFERENCENUMBER          AS RefNumber,
       ih.PARTYID                  AS PartyId,
       pt.PARTYNAME                AS PartyName,
       it.ITEMID                   AS ItemId,
       it.ITEMCODE                 AS ItemCode,
       it.ITEMNAME                 AS ItemName,
       sk.SKUID                    AS SKUId,
       sk.SKUCODE                  AS SKUCode,
       sk.SKUNAME                  AS SKUName,
       id.INDENTDETAILID           AS MMDetailId,
       id.PENDINGQUANTITY          AS PendingQuantity
FROM   tallocation      a
JOIN   tindent          ih ON ih.INDENTID         = a.ALLOCATIONALLOTEDHEADERID
JOIN   tindentdetail    id ON id.INDENTDETAILID   = a.ALLOCATIONALLOTEDOBJECTID
LEFT   JOIN titem       it ON it.ITEMID           = id.ITEMID
LEFT   JOIN tsku        sk ON sk.SKUID            = id.SKUID
LEFT   JOIN tparty      pt ON pt.PARTYID          = ih.PARTYID
{whereSql}
{pagingSql}";

            return (sql, SqlClause.Merge(whereParams, pagingParams));
        }

        // ── Pending Indents Multi-Process ─────────────────────────────────

        /// <summary>
        /// Pending indents filtered across multiple processes simultaneously.
        /// Used when ObjectHeaderTypeId == OBJECTINDENT AND multiprocess discriminator is set.
        /// </summary>
        public static (string Sql, DynamicParameters Params) BuildPendingIndentsMultiProcess(
            PendingIndentMultiProcessCriteria c, ISqlDialect d,
            string orderBy = "ih.INDENTID DESC, id.INDENTDETAILID")
        {
            var ctx = new QueryContext(d)
                .Register("allocation", "a")
                .Register("header",     "ih")
                .Register("detail",     "id")
                .Register("pending",    "id")
                .Register("document",   "ih")
                .Register("item",       "it")
                .Register("sku",        "sk");

            // Process IN filter — built inline since SqlClauses doesn't have a generic IN helper
            SqlClause processFilter = SqlClause.Empty;
            if (c.ProcessIds is { Length: > 0 })
            {
                var p = new DynamicParameters();
                p.Add("processIds", c.ProcessIds);
                processFilter = new SqlClause(
                    $"{ctx.Col("detail", "PROCESSID")} IN {d.Param("processIds")}",
                    p);
            }

            var (whereSql, whereParams) = SqlClause.AsWhere(new[]
            {
                SqlClauses.HeaderType(ctx, OBJECTINDENT),
                SqlClauses.AllocationType(ctx, ALLOCATIONTYPE_FROM),
                SqlClauses.AllocationNature(ctx, ALLOCATIONNATURE_NORMAL),
                SqlClauses.BizType(ctx, c.BizTypeId),
                SqlClauses.OUFilterSingle(ctx, c.OUId, "document"),
                SqlClauses.IndentSearch(ctx, c.SearchText),
                SqlClauses.IndentStatus(ctx, status: 1),
                SqlClauses.PendingOnly(ctx),
                processFilter,
            });

            var (pagingSql, pagingParams) = SqlClauses.Paging(ctx, c, orderBy: orderBy);

            var sql = $@"
SELECT {d.TotalCountExpr()},
       a.ALLOCATIONID              AS AllocationId,
       ih.INDENTNUMBER             AS HeaderName,
       ih.INDENTDATE               AS HeaderDate,
       ih.REFERENCENUMBER          AS RefNumber,
       it.ITEMID                   AS ItemId,
       it.ITEMCODE                 AS ItemCode,
       it.ITEMNAME                 AS ItemName,
       sk.SKUID                    AS SKUId,
       sk.SKUCODE                  AS SKUCode,
       sk.SKUNAME                  AS SKUName,
       id.PROCESSID                AS ProcessId,
       id.INDENTDETAILID           AS MMDetailId,
       id.PENDINGQUANTITY          AS PendingQuantity
FROM   tallocation      a
JOIN   tindent          ih ON ih.INDENTID         = a.ALLOCATIONALLOTEDHEADERID
JOIN   tindentdetail    id ON id.INDENTDETAILID   = a.ALLOCATIONALLOTEDOBJECTID
LEFT   JOIN titem       it ON it.ITEMID           = id.ITEMID
LEFT   JOIN tsku        sk ON sk.SKUID            = id.SKUID
{whereSql}
{pagingSql}";

            return (sql, SqlClause.Merge(whereParams, pagingParams));
        }

        // ── Pending Allocation Select List (dedup pick-list) ──────────────

        /// <summary>
        /// Distinct pick-list of pending allocations for the Allocation-matching
        /// screen's dropdown.
        ///
        /// GB4 Source: AllocationBLL.GetPendingSelectList — fetched the full
        /// GetPending list, then de-duplicated in memory via two DataTables keyed
        /// on (BizTransactionTypeId, ObjectHeaderTypeId, AllocationObjectHeaderId,
        /// HeaderName, ItemId, ItemName). GB5 does the same dedup with
        /// SELECT DISTINCT, wrapped in a derived table so paging/COUNT(*) OVER()
        /// apply to the already-distinct row set rather than the raw join.
        ///
        /// TENANTID: GB4 was single-tenant; GB5 scopes every row to
        /// login.ClientId since TPENDINGALLOCATION carries a real TENANTID
        /// column (added per the pending-allocation engine migration).
        /// </summary>
        public static (string Sql, DynamicParameters Params) BuildPendingAllocationSelectList(
            PendingAllocationSelectListCriteria c, LoginDTO login, ISqlDialect d,
            string orderBy = "HeaderName")
        {
            var ctx = new QueryContext(d)
                .Register("pending", "PA")
                .Register("biztype", "MB")
                .Register("item",    "MI");

            var (whereSql, whereParams) = SqlClause.AsWhere(new[]
            {
                SqlClauses.ExactInt(ctx, login.ClientId, "pending", "TENANTID"),
                SqlClauses.ExactInt(ctx, c.BizTransactionClassId, "biztype", "BIZTRANSACTIONCLASSID"),
                SqlClauses.ExactInt(ctx, c.ObjectHeaderTypeId,    "pending", "OBJECTHEADERTYPEID"),
                SqlClauses.ExactInt(ctx, c.ObjectTypeId,          "pending", "OBJECTTYPEID"),
                SqlClauses.ExactInt(ctx, c.ItemId,                "pending", "ITEMID"),
                SqlClauses.OUFilterSingle(ctx, c.OUId, "pending"),
                SqlClauses.ExactString(ctx, c.SearchText, "pending", "DOCUMENTNUMBER"),
            });

            var (pagingSql, pagingParams) = SqlClauses.Paging(ctx, c, orderBy: orderBy);

            var sql = $@"
SELECT {d.TotalCountExpr()}, *
FROM (
    SELECT DISTINCT
        PA.BIZTRANSACTIONTYPEID  AS BizTransactionTypeId,
        MB.BIZTRANSACTIONCLASSID AS BizTransactionClassId,
        PA.OBJECTHEADERTYPEID    AS ObjectHeaderTypeId,
        PA.OBJECTHEADERID        AS AllocationObjectHeaderId,
        PA.OBJECTTYPEID          AS ObjectTypeId,
        PA.OUID                  AS OUId,
        PA.DOCUMENTNUMBER        AS HeaderName,
        PA.ITEMID                AS ItemId,
        MI.ITEMNAME              AS ItemName
    FROM TPENDINGALLOCATION PA
    JOIN MBIZTRANSACTIONTYPE MB ON MB.BIZTRANSACTIONTYPEID = PA.BIZTRANSACTIONTYPEID
    LEFT JOIN MITEM MI          ON MI.ITEMID = PA.ITEMID
    {whereSql}
) D
{pagingSql}";

            return (sql, SqlClause.Merge(whereParams, pagingParams));
        }
    }

    // ─────────────────────────────────────────────────────────────────────
    //  IQueryBuilder wrapper classes  – one per query variant
    //
    //  These are the ONLY files a module developer writes for list queries.
    //  AddListInfrastructure() scans MMDAL, finds these classes, and auto-
    //  registers GenericListHandler<TQuery, PendingAllocationListDTO> for each.
    //
    //  PendingAllocationHandlers.cs is deleted — these replace it entirely.
    //
    //  Sort safety: each class declares a private _sort allowlist.
    //  SqlClauses.OrderBy() validates client-supplied SortBy against it.
    //  Unknown columns fall back to the compile-time defaultCol — never
    //  concatenated directly into SQL.
    // ─────────────────────────────────────────────────────────────────────

    public sealed class PendingMMDocumentsQB
    : IQueryBuilder<PendingMMDocumentsQuery, PendingAllocationListDTO>
    {
        private static readonly IReadOnlyDictionary<string, string> _sort =
            new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase)
            {
                ["headerdate"] = "a.documentdate",
                ["headernumber"] = "a.documentnumber",
                ["partyname"] = "pt.PARTYNAME",
                ["pendingqty"] = "SUM(a.PENDINGQUANTITY)",
                ["lastmodified"] = "MAX(a.modifiedon)",
            };

        public (string Sql, DynamicParameters Params) Build(
            PendingMMDocumentsQuery query,
            LoginDTO login,
            ISqlDialect dialect)
        {
            _ = login;

            var orderBy = SqlClauses.OrderBy(
                query.Criteria.SortBy,
                query.Criteria.SortDesc,
                _sort,
                defaultCol: "a.DOCUMENTDATE");

            return PendingAllocationQB.BuildPendingMMDocuments(
                query.Criteria,
                dialect,
                orderBy);
        }
    }

    public sealed class PendingMMAllItemsQB
        : IQueryBuilder<PendingMMAllItemsQuery, PendingAllocationListDTO>
    {
        private static readonly IReadOnlyDictionary<string, string> _sort =
            new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase)
            {
                ["headerdate"] = "a.documentdate",
                ["headernumber"] = "a.documentnumber",
                ["partyname"] = "pt.PARTYNAME",
                ["itemcode"] = "i.ITEMCODE",
                ["itemname"] = "i.ITEMNAME",
                ["skucode"] = "s.SKUCODE",
                ["pendingqty"] = "a.PENDINGQUANTITY",
                ["lastmodified"] = "a.modifiedon",
            };

        public (string Sql, DynamicParameters Params) Build(
            PendingMMAllItemsQuery query,
            LoginDTO login,
            ISqlDialect dialect)
        {
            _ = login;

            var orderBy = SqlClauses.OrderBy(
                query.Criteria.SortBy,
                query.Criteria.SortDesc,
                _sort,
                defaultCol: "a.DOCUMENTDATE");

            return PendingAllocationQB.BuildPendingMMAllItems(
                query.Criteria,
                dialect,
                orderBy);
        }
    }

    public sealed class PendingIndentsQB
        : IQueryBuilder<PendingIndentsQuery, PendingAllocationListDTO>
    {
        private static readonly IReadOnlyDictionary<string, string> _sort =
            new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase)
            {
                ["headerdate"] = "ih.INDENTDATE",
                ["headernumber"] = "ih.INDENTNUMBER",
                ["partyname"] = "pt.PARTYNAME",
                ["itemcode"] = "it.ITEMCODE",
                ["itemname"] = "it.ITEMNAME",
                ["pendingqty"] = "id.PENDINGQUANTITY",
            };

        public (string Sql, DynamicParameters Params) Build(
            PendingIndentsQuery query,
            LoginDTO login,
            ISqlDialect dialect)
        {
            _ = login;

            var orderBy = SqlClauses.OrderBy(
                query.Criteria.SortBy,
                query.Criteria.SortDesc,
                _sort,
                defaultCol: "ih.INDENTID DESC");

            return PendingAllocationQB.BuildPendingIndents(
                query.Criteria,
                dialect,
                orderBy);
        }
    }

    public sealed class PendingIndentMultiProcessQB
    : IQueryBuilder<PendingIndentMultiProcessQuery, PendingAllocationListDTO>
    {
        private static readonly IReadOnlyDictionary<string, string> _sort =
            new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase)
            {
                ["headerdate"] = "ih.INDENTDATE",
                ["headernumber"] = "ih.INDENTNUMBER",
                ["itemcode"] = "it.ITEMCODE",
                ["itemname"] = "it.ITEMNAME",
                ["processid"] = "id.PROCESSID",
                ["pendingqty"] = "id.PENDINGQUANTITY",
            };

        public (string Sql, DynamicParameters Params) Build(
            PendingIndentMultiProcessQuery query,
            LoginDTO login,
            ISqlDialect dialect)
        {
            var orderBy = SqlClauses.OrderBy(
                query.Criteria.SortBy,
                query.Criteria.SortDesc,
                _sort,
                defaultCol: "ih.INDENTID DESC, id.INDENTDETAILID");

            return PendingAllocationQB.BuildPendingIndentsMultiProcess(
                query.Criteria,
                dialect,
                orderBy);
        }
    }

    public sealed class PendingAllocationSelectListQB
        : IQueryBuilder<PendingAllocationSelectListQuery, PendingAllocationSelectListDTO>
    {
        private static readonly IReadOnlyDictionary<string, string> _sort =
            new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase)
            {
                ["headername"] = "HeaderName",
                ["itemname"] = "ItemName",
                ["itemid"] = "ItemId",
                ["biztransactiontypeid"] = "BizTransactionTypeId",
            };

        public (string Sql, DynamicParameters Params) Build(
            PendingAllocationSelectListQuery query,
            LoginDTO login,
            ISqlDialect dialect)
        {
            var orderBy = SqlClauses.OrderBy(
                query.Criteria.SortBy,
                query.Criteria.SortDesc,
                _sort,
                defaultCol: "HeaderName");

            return PendingAllocationQB.BuildPendingAllocationSelectList(
                query.Criteria,
                login,
                dialect,
                orderBy);
        }
    }
}