namespace MMDAL.Query.Allocation;

public static class AllocationPostingQB
{
    public const string SP_SAVE_ALLOCATIONS   = "SP_SaveAllocations";
    public const string SP_CANCEL_ALLOCATIONS = "SP_CancelDocumentAllocations";

    // Required indexes: IX_TALLOCATION_OBJHEAD (OBJECTHEADERID, OBJECTHEADERTYPEID, TENANTID, ROWSTATUS)
    public const string GET_ALLOCATIONS_FOR_CANCEL = @"
        SELECT
            a.ALLOCATIONID              AS AllocationId,
            a.ALLOTEDALLOCATIONID       AS AllotedAllocationId,
            a.ALLOCATIONTYPE            AS AllocationType,
            a.ALLOCATIONNATURE          AS AllocationNature,
            a.OBJECTHEADERTYPEID        AS ObjectHeaderTypeId,
            a.OBJECTHEADERID            AS ObjectHeaderId,
            a.OBJECTTYPEID              AS ObjectTypeId,
            a.OBJECTID                  AS ObjectId,
            a.BIZTRANSACTIONTYPEID      AS BizTransactionTypeId,
            a.OUID                      AS OuId,
            ISNULL(a.QUANTITY, 0)       AS Quantity,
            ISNULL(a.REJECTEDQUANTITY, 0) AS RejectedQuantity,
            ISNULL(a.REWORKQUANTITY, 0) AS ReworkQuantity,
            ISNULL(a.OTHERQUANTITY, 0)  AS OtherQuantity,
            ISNULL(a.EXCESSQUANTITY, 0) AS ExcessQuantity,
            ISNULL(a.ISCONSOLIDATED, 0) AS IsConsolidated,
            a.TENANTID                  AS TenantId
        FROM  TALLOCATION a
        WHERE a.OBJECTHEADERID     = @DocumentId
        AND   a.OBJECTHEADERTYPEID = @DocumentTypeId
        AND   a.ROWSTATUS          = 0
        AND   a.TENANTID           = @TenantId";

    // Required indexes: PK on ALLOCATIONID (or IX_TALLOCATION_TENANTID)
    public const string GET_ALLOCATION_NATURES = @"
        SELECT
            a.ALLOCATIONID     AS AllocationId,
            a.ALLOCATIONNATURE AS AllocationNature
        FROM  TALLOCATION a
        WHERE a.ALLOCATIONID IN @AllocationIds
        AND   a.TENANTID     = @TenantId";
}