GO
/****** Object:  StoredProcedure [dbo].[SP_GetStockValue]    Script Date: 13-03-2025 13:17:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SP_GetStockValue]
    @StockListType NVARCHAR(255) = NULL,
    @Category NVARCHAR(255) = NULL,
    @Company NVARCHAR(255) = NULL,
	@DateOn datetime
AS
BEGIN
    -- Base query
    DECLARE @SQL NVARCHAR(MAX);
    SET @SQL = 'SELECT Item.itemCode, item.ItemName, GetStockValue.SUMQTY as Stock, Category.Name, dbo.udf_SelectSTDCOST(@DateOn, item.ItemCode) AS StandardCost,Rate as SalesRate
                FROM Item
                left Join  GetStockValue on GetStockValue.ItemCode=item.ItemCode
                LEFT JOIN Category ON Category.Code = Item.Category
                WHERE ISNULL(item.stkmode, ''Inventory'') = ''Inventory''';

    -- StockListType filter
    IF @StockListType = 1
    BEGIN
        SET @SQL += ' AND item.Stock < 0';  -- StockListType 1: qty < 0
    END
    ELSE IF @StockListType = 2
    BEGIN
        SET @SQL += ' AND item.Stock > 0';  -- StockListType 2: qty > 0
    END
    ELSE IF @StockListType = 3
    BEGIN
        SET @SQL += ' AND item.Stock = 0';  -- StockListType 3: qty = 0
    END

    -- Category filter, if provided
    IF @Category IS NOT NULL AND @Category <> ''
    BEGIN
        SET @SQL += ' AND Item.CategoryName = @Category';  -- Category filter
    END

    -- Company filter, if provided
    IF @Company IS NOT NULL AND @Company <> ''
    BEGIN
        SET @SQL += ' AND Item.CompanyName = @Company';  -- Company filter
    END

    -- Execute the dynamic query
    EXEC sp_executesql @SQL,
        N'@Category NVARCHAR(255), @Company NVARCHAR(255),@DateOn datetime',
        @Category, @Company,@DateOn;
END