CREATE PROCEDURE [dbo].[SP_GetStockList_Temp]
    @todate DATETIME,
    @BranchName VARCHAR(100),
    @StockListType NVARCHAR(10) = NULL,
    @Category NVARCHAR(255) = NULL,
    @Company NVARCHAR(255) = NULL,
    @CostRateval NVARCHAR(255) = NULL
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @stockstartdate AS DATETIME;
    SET @stockstartdate = (SELECT ISNULL(stockdate, '1900-01-01') FROM stockstartdate);

    -- Create temp table for stock
    CREATE TABLE #TempStockList (
        SUMQTY DECIMAL(18,2),
        ItemCode VARCHAR(100)
    );

    -- Insert stock quantities into temp table
    INSERT INTO #TempStockList (SUMQTY, ItemCode)
    SELECT SUM(qty) AS SUMQTY, ITEMCODE
    FROM (
        SELECT ISNULL(SUM(ISNULL(opstock,0)),0) as qty, ITEMCODE
        FROM OPStockUpdatedDetails 
        WHERE BranchName = @BranchName
        GROUP BY ITEMCODE

        UNION ALL

        SELECT ISNULL(SUM(ISNULL(qty, 0) * ISNULL(qtyperunit, 1)), 0), ITEMCODE
        FROM PurchaseItem
        WHERE entrydate >= @stockstartdate AND entrydate <= @todate
          AND PR = 'Purchase' AND ISNULL(deleted, 'N') = 'N'
          AND (@BranchName = 'All' OR branchname = @BranchName)
        GROUP BY ITEMCODE

       UNION ALL

        SELECT ISNULL(SUM(ISNULL(qty, 0) * ISNULL(qtyperunit, 1)), 0) AS qty, ITEMCODE 
        FROM InvoiceReturnItem 
        WHERE InvoiceReturnItem.entrydate >= @stockstartdate 
          AND entryDate <= @todate 
          AND SR = 'Returns' 
          AND ISNULL(deleted, 'N') = 'N'   
		 AND (@BranchName = 'All' OR branchname = @BranchName)
        GROUP BY ITEMCODE

        UNION ALL

        SELECT ISNULL(SUM(ISNULL(qty, 0) * ISNULL(qtyperunit, 1)), 0) AS qty, ITEMCODE 
        FROM GoodsReceiptItem 
        WHERE GoodsReceiptItem.entrydate >= @stockstartdate 
          AND entryDate <= @todate 
          AND ISNULL(deleted, 'N') = 'N'   
		  AND (@BranchName = 'All' OR branchname = @BranchName)
        GROUP BY ITEMCODE

        UNION ALL

        SELECT ISNULL(SUM(qty), 0) AS qty, ITEMCODE 
        FROM MaterialTransDetails   
        INNER JOIN MaterialTrans ON MaterialTransDetails.DOCUMENTNO = MaterialTrans.VOUCHERNO  
        WHERE ITEMCODE NOT IN ('SER', 'DLC')   
          AND MaterialTransDetails.entrydate >= @stockstartdate 
          AND MaterialTransDetails.entrydate <= @todate    
		  AND (@BranchName = 'All' OR MaterialTransDetails.TOSTORE = @BranchName)
        GROUP BY ITEMCODE

   --     UNION ALL
		 --SELECT -1*ISNULL(SUM(qty), 0) AS qty, ITEMCODE 
   --     FROM MaterialTransDetails   
   --     INNER JOIN MaterialTrans ON MaterialTransDetails.DOCUMENTNO = MaterialTrans.VOUCHERNO  
   --     WHERE ITEMCODE NOT IN ('SER', 'DLC')   
   --       AND MaterialTransDetails.entrydate >= @stockstartdate 
   --       AND MaterialTransDetails.entrydate <= @todate    
		 -- AND (@BranchName = 'All' OR MaterialTransDetails.fromstore = @BranchName)
   --     GROUP BY ITEMCODE

        UNION ALL

        SELECT ISNULL(SUM(qty), 0) AS qty, ITEMCODE 
        FROM FinalProduct 
        WHERE FinalProduct.entrydate >= @stockstartdate 
          AND entryDate <= @todate   
		  AND (@BranchName = 'All' OR branchname = @BranchName)
        GROUP BY ITEMCODE

        UNION ALL

        SELECT -1 * ISNULL(SUM(ISNULL(qty, 0) * ISNULL(qtyperunit, 1)), 0) AS qty, ITEMCODE 
        FROM PurchaseReturnItem 
        WHERE PurchaseReturnItem.entrydate >= @stockstartdate 
          AND entryDate <= @todate 
          AND PR = 'Returns' 
          AND ISNULL(deleted, 'N') = 'N'  
		 AND (@BranchName = 'All' OR branchname = @BranchName)
        GROUP BY ITEMCODE 

        UNION ALL

        SELECT -1 * ISNULL(SUM(ISNULL(qty, 0) * ISNULL(qtyperunit, 1)), 0) AS qty, ITEMCODE 
        FROM InvoiceItem 
        WHERE InvoiceItem.entrydate >= @stockstartdate 
          AND entryDate <= @todate 
          AND SR = 'Sales'  
          AND ISNULL(deleted, 'N') = 'N'   
		 AND (@BranchName = 'All' OR branchname = @BranchName)
        GROUP BY ITEMCODE

        UNION ALL

        SELECT -1 * ISNULL(SUM(ISNULL(qty, 0) * ISNULL(qtyperunit, 1)), 0) AS qty, ITEMCODE 
        FROM DeliveryNoteItem 
        WHERE DeliveryNoteItem.entrydate >= @stockstartdate 
          AND entryDate <= @todate 
          AND Status = 0  
          AND ISNULL(deleted, 'N') = 'N'  
		  AND (@BranchName = 'All' OR branchname = @BranchName)
        GROUP BY ITEMCODE 

        UNION ALL

        SELECT -1 * ISNULL(SUM(qty), 0) AS qty, ITEMCODE 
        FROM FinalProductItem 
        WHERE FinalProductItem.entrydate >= @stockstartdate 
          AND entryDate <= @todate  
		  AND (@BranchName = 'All' OR branchname = @BranchName)
        GROUP BY ITEMCODE  

        UNION ALL

        SELECT -1 * ISNULL(SUM(qty), 0) AS qty, ITEMCODE 
        FROM damageItem 
        WHERE damageItem.Date >= @stockstartdate 
          AND Date <= @todate   
		 AND (@BranchName = 'All' OR branchname = @BranchName)
        GROUP BY ITEMCODE

        UNION ALL

        SELECT -1 * ISNULL(SUM(qty), 0) AS qty, ITEMCODE 
        FROM MaterialTransOutDetails  
        INNER JOIN MaterialTransOut ON MaterialTransOutDetails.DOCUMENTNO = MaterialTransOut.VOUCHERNO 
        WHERE ITEMCODE NOT IN ('SER', 'DLC') 
          AND MaterialTransOutDetails.entrydate >= @stockstartdate  
          AND MaterialTransOutDetails.entrydate <= @todate  
          AND MaterialTransOut.FROMSTORE IN (SELECT NAME FROM BRANCHMASTER)  
		 AND (@BranchName = 'All' OR MaterialTransOut.FROMSTORE = @BranchName)
        GROUP BY ITEMCODE

        UNION ALL

        SELECT ISNULL(SUM(ISNULL(qty, 0) * ISNULL(qtyperunit, 1)), 0), ITEMCODE
        FROM QtyAdjustmentDetails
        WHERE entryDate <= @todate AND entryDate >= @stockstartdate
          AND (@BranchName = 'All' OR branchname = @BranchName)
        GROUP BY ITEMCODE
    ) t
    GROUP BY t.ITEMCODE;

    -- Final select with joins and filters
    SELECT 
        ts.ItemCode,
        i.ItemName,
        ts.SUMQTY AS Stock,
        c.Name AS CategoryName,
        CASE 
            WHEN @CostRateval = 'Rate' THEN i.Rate
            WHEN @CostRateval = 'Cost' THEN i.StandardCost
            WHEN @CostRateval = 'VatRate' THEN i.VatRate
            ELSE NULL
        END AS CostRatevalue,
        @CostRateval AS CostRateval
    FROM #TempStockList ts
    LEFT JOIN Item i ON i.ItemCode = ts.ItemCode
    LEFT JOIN Category c ON c.Code = i.Category
    WHERE ISNULL(i.StkMode, 'Inventory') = 'Inventory'
        AND (
            (@StockListType = '1' AND ts.SUMQTY < 0) OR
            (@StockListType = '2' AND ts.SUMQTY > 0) OR
            (@StockListType = '3' AND ts.SUMQTY = 0) OR
            (@StockListType IS NULL)
        )
        AND (
            @Category IS NULL OR @Category = '' OR i.CategoryName = @Category
        )
        AND (
            @Company IS NULL OR @Company = '' OR i.CompanyName = @Company
        );

    DROP TABLE #TempStockList;
END