namespace MMDAL.Query.StockValuation;

public static class StockPostingQB
{
    public const string SP_APPLY_STOCK_BATCH = "sp_ApplyStockBatch";

    // Required indexes: IX_TSTOCKLEDGER_OBJDETAIL (OBJECTDETAILID, OBJECTDETAILTYPEID, TENANTID)
    //                   IX_TMMDETAIL_DOCID        (DOCUMENTID, TENANTID)
    public const string GET_STOCK_LEDGER_FOR_DOCUMENT = @"
        SELECT
            sl.STOCKLEDGERID                                    AS StockLedgerId,
            sl.STOCKLEDGERNUMBER                                AS StockLedgerNumber,
            sl.REFERENCENUMBER                                  AS ReferenceNumber,
            sl.OUID                                              AS OuId,
            sl.OBJECTTYPEID                                     AS ObjectTypeId,
            sl.OBJECTID                                          AS ObjectId,
            sl.BIZTRANSACTIONTYPEID                             AS BizTransactionTypeId,
            sl.LOCATIONTYPE                                     AS LocationType,
            ISNULL(sl.ALLOCATIONID, -1)                        AS AllocationId,
            ISNULL(sl.STOREID, -1)                             AS StoreId,
            ISNULL(sl.WORKCENTERID, -1)                        AS WorkCenterId,
            ISNULL(sl.PARTYBRANCHID, -1)                       AS PartyBranchId,
            sl.ITEMID                                           AS ItemId,
            ISNULL(sl.SKUID, -1)                               AS SkuId,
            sl.STOCKPOSTTYPE                                    AS StockPostType,
            sl.MATERIALOWNERSHIPTYPE                            AS MaterialOwnershipType,
            sl.TRANSACTIONQUANTITY                              AS TransactionQuantity,
            sl.UOMID                                            AS UomId,
            sl.CONVERSIONFACTOR                                 AS ConversionFactor,
            sl.QUANTITY                                         AS Quantity,
            ISNULL(sl.PACKQUANTITY, 0)                         AS PackQuantity,
            ISNULL(sl.PACKID, -1)                              AS PackId,
            ISNULL(sl.WEIGHT, 0)                               AS Weight,
            ISNULL(sl.CURRENCYID, -1)                          AS CurrencyId,
            ISNULL(sl.RATECONVERSIONFACTOR, 1)                 AS RateConversionFactor,
            ISNULL(sl.RATE, 0)                                 AS Rate,
            ISNULL(sl.RATEPERUOMID, -1)                        AS RatePerUomId,
            ISNULL(sl.COST, 0)                                 AS Cost,
            ISNULL(sl.POSTEDCOST, 0)                           AS PostedCost,
            ISNULL(sl.POSTEDVALUE, 0)                          AS PostedValue,
            ISNULL(sl.GOODQUANTITY, 0)                         AS GoodQuantity,
            ISNULL(sl.REJECTEDQUANTITY, 0)                     AS RejectedQuantity,
            ISNULL(sl.REWORKQUANTITY, 0)                       AS ReworkQuantity,
            ISNULL(sl.OTHERQUANTITY, 0)                        AS OtherQuantity,
            ISNULL(sl.TRANSACTIONACTUALQUANTITY, 0)            AS TransactionActualQuantity,
            ISNULL(sl.ACTUALQUANTITY, 0)                       AS ActualQuantity,
            ISNULL(sl.TRANSACTIONUOMID, -1)                    AS TransactionUomId,
            sl.OBJECTDETAILTYPEID                              AS ObjectDetailTypeId,
            sl.OBJECTDETAILID                                  AS ObjectDetailId,
            sl.DELIVERYTYPE                                    AS DeliveryType,
            sl.MATERIALCOST                                    AS MaterialCost,
            sl.PROCESSCOST                                     AS ProcessCost,
            sl.CHARGESCOST                                     AS ChargesCost,
            sl.REVENUE                                         AS Revenue,
            ISNULL(sl.INDENTDETAILID, -1)                      AS IndentDetailId,
            ISNULL(sl.GCCURRENCYCONVERSION, 0)                 AS GcCurrencyConversion,
            ISNULL(sl.POSTEDCOSTGC, 0)                         AS PostedCostGc,
            ISNULL(sl.POSTEDVALUEGC, 0)                        AS PostedValueGc,
            sl.TENANTID                                        AS TenantId
        FROM  TSTOCKLEDGER sl
        WHERE sl.OBJECTDETAILID IN (
            SELECT d.DOCUMENTDETAILID
            FROM   TMMDETAIL d
            WHERE  d.DOCUMENTID = @DocumentId
            AND    d.TENANTID   = @TenantId
        )
        AND sl.OBJECTDETAILTYPEID = @ObjectDetailTypeId
        AND sl.TENANTID           = @TenantId";
}