using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace MMDAL.Query.ItemStock
{
    public static class ItemStockQB
    {
        public const string GET_ITEMSTOCK = @"
SELECT
    ITS.ITEMSTOCKID                AS ItemStockId,
    ITS.STOREID                    AS StoreId,
    S.STORECODE                    AS StoreCode,
    S.STORENAME                    AS StoreName,
    ITS.ITEMID                     AS ItemId,
    I.ITEMCODE                     AS ItemCode,
    I.ITEMNAME                     AS ItemName,
    ITS.SKUID                      AS SKUId,
    SKU.SKUCODE                    AS SKUCode,
    SKU.SKUNAME                    AS SKUName,
    ITS.ISCRITICAL                 AS ItemStockIsCritical,
    ITS.STOCKONHAND                AS ItemStockStockOnHand,
    ITS.ROL                        AS ItemStockRol,
    ITS.BINNUMBER                  AS ItemStockBinNumber,
    ITS.UOMID                      AS UOMId,
    U.UOMCODE                      AS UOMCode,
    U.UOMNAME                      AS UOMName,
    ITS.MODIFIEDON                 AS ItemStockModifiedOn,
    ITS.CYCLECLASSID               AS CycleClassId,
    CC.CYCLECLASS                  AS CycleClassCycleClasso,   
    CC.DESCRIPTION                 AS CycleClassDescription,
    ITS.BINID                      AS BinId,
    B.BINNUMBER                    AS BinNumber
FROM MITEMSTOCK ITS
LEFT JOIN MSTORE S ON ITS.STOREID = S.STOREID
LEFT JOIN MITEM I ON ITS.ITEMID = I.ITEMID
LEFT JOIN MSKU SKU ON ITS.SKUID = SKU.SKUID
LEFT JOIN MUOM U ON ITS.UOMID = U.UOMID
LEFT JOIN MCYCLECLASS CC ON ITS.CYCLECLASSID = CC.CYCLECLASSID
LEFT JOIN MBIN B ON ITS.BINID = B.BINID
WHERE ITS.ITEMSTOCKID = @ItemStockId;";




        public const string GET_SELECTLIST_ITEMSTOCK = @"
                                                    WITH PagedItemStock AS
                                                    (
                                                        SELECT
                                                            ITEMSTOCKID AS Id,
                                                            BINNUMBER AS BinNumber,
                                                            ROW_NUMBER() OVER (ORDER BY ITEMSTOCKID) AS RowNum
                                                        FROM MITEMSTOCK
                                                    )
                                                    SELECT
                                                        Id,
                                                        BinNumber
                                                    FROM PagedItemStock
                                                    WHERE
                                                        (@firstnumber = -1 AND @maxresult = -1)
                                                        OR
                                                        (RowNum BETWEEN @firstnumber AND @maxresult);
                                                    ";

        //  public const string INSERT_ITEMSTOCK = @"INSERT INTO MITEMSTOCK
        //(
        //    ITEMSTOCKID,
        //    STOREID,
        //    ITEMID,
        //    ISCRITICAL,
        //    STOCKONHAND,
        //    ROL,
        //    UOMID,
        //    SKUID,
        //    BINNUMBER,
        //    MODIFIEDON,
        //    CYCLECLASSID,
        //    BINID
        //)
        //VALUES
        //(
        //    @ItemStockId,
        //    @StoreId,
        //    @ItemId,
        //    @ItemStockIsCritical,
        //    @ItemStockStockOnHand,
        //    @ItemStockRol,
        //    @UOMId,
        //    @SKUId,
        //    @ItemStockBinNumber,
        //    @ItemStockModifiedOn,
        //    @CycleClassId,
        //    @BinId
        //);";


        public const string SAVE_ITEMSTOCK = @"
                                    INSERT INTO MITEMSTOCK
                                    (
                                        ITEMSTOCKID,
                                        STOREID,
                                        ITEMID,
                                        ISCRITICAL,
                                        STOCKONHAND,
                                        ROL,
                                        UOMID,
                                        SKUID,
                                        BINNUMBER,
                                        MODIFIEDON,
                                        CYCLECLASSID,
                                        BINID
                                    )
                                    VALUES
                                    (
                                        @ItemStockId,
                                        @StoreId,
                                        @ItemId,
                                        @ItemStockIsCritical,
                                        @ItemStockStockOnHand,
                                        @ItemStockRol,
                                        @UOMId,
                                        @SKUId,
                                        @ItemStockBinNumber,
                                        @ItemStockModifiedOn,
                                        @CycleClassId,
                                        @BinId
                                    );";


        public const string UPDATE_ITEMSTOCK = @"
                                            UPDATE MITEMSTOCK
                                            SET
                                                STOREID        = @StoreId,
                                                ITEMID         = @ItemId,
                                                ISCRITICAL     = @ItemStockIsCritical,
                                                STOCKONHAND    = @ItemStockStockOnHand,
                                                ROL            = @ItemStockRol,
                                                UOMID          = @UOMId,
                                                SKUID          = @SKUId,
                                                BINNUMBER      = @ItemStockBinNumber,
                                                MODIFIEDON     = @ItemStockModifiedOn,
                                                CYCLECLASSID   = @CycleClassId,
                                                BINID          = @BinId
                                            WHERE
                                                ITEMSTOCKID = @ItemStockId;";


        public const string DELETE_ITEMSTOCK = @"
                                        DELETE FROM MITEMSTOCK
                                        WHERE ITEMSTOCKID = @ItemStockId;";


    }
    
    }