GO
/****** Object:  StoredProcedure [dbo].[sp_InsertItemHistory]    Script Date: 12-04-2025 15:33:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_InsertItemHistory]
    @fromdate DATE,
    @todate DATE,
    @stockStartDate DATE,
    @itemcode NVARCHAR(50),
	 @BranchName NVARCHAR(50)
AS
BEGIN
    SET NOCOUNT ON;

    -- Insert all data into ItemHistory using UNION ALL
  --  INSERT INTO ItemHistory (
  --      EntryDate, 
  --      RefNo, 
  --      Type, 
  --      Unit, 
  --      DeliveryQty, 
  --      IssueQty, 
  --      IssueRetQty, 
  --      PurQty, 
  --      PurRetQty, 
  --      FinalQty, 
  --      ProductionQty, 
  --      DmgQty, 
  --      AdjQty, 
		--TransOutQty,
		--TransInQty,
  --      qtyperunit,
		--OPStock

  --  )

   SELECT EntryDate, RefNo, Type, Unit, SUM(ISNULL(DeliveryQty,0)) AS DeliveryQty, SUM(ISNULL(IssueQty,0)) AS IssueQty, SUM(ISNULL(IssueRetQty,0)) AS IssueRetQty, SUM(ISNULL(PurQty,0)) AS PurQty, SUM(ISNULL(PurRetQty,0)) AS PurRetQty,SUM(ISNULL(TransOutQty,0)) AS TransOutQty ,
	SUM(ISNULL(TransInQty,0)) AS TransInQty, SUM(ISNULL(FinalQty,0)) AS FinalQty , 
	SUM(ISNULL(ProductionQty,0)) AS ProductionQty, SUM(ISNULL(DmgQty,0)) AS DmgQty , SUM(ISNULL(AdjQty,0)) AS AdjQty, qtyperunit,[dbo].[SP_OpstockNewMethod]  (@fromdate,@Itemcode ,@BRANCHNAME )  AS OPStock
    FROM (

	SELECT 
        @stockStartDate AS ENTRYDATE, 
        ''   AS REFNO, 
        'OPSTOCK' AS TYPE, 
        'Each'  Unit,
        null AS DeliveryQty, 
        NULL AS IssueQty,
        NULL AS IssueRetQty,
        NULL AS PurQty,
        NULL AS PurRetQty,
        NULL AS FinalQty,
        NULL AS ProductionQty,
        NULL AS DmgQty,
        NULL AS AdjQty,
		NULL AS TransInQty,
		NULL AS TransOutQty,
        1 as   qtyperunit,
		0   AS OPStock
    FROM  item  
    WHERE (@itemcode IS NULL OR @itemcode = '' OR ItemCode = @itemcode)
      
	  
    
	UNION ALL
    SELECT 
        dt.EntryDate, 
        dt.DNNo AS REFNO, 
        'Delivery' AS TYPE, 
        dt.unit AS Unit,
        SUM(dt.Qty) AS DeliveryQty, 
        NULL AS IssueQty,
        NULL AS IssueRetQty,
        NULL AS PurQty,
        NULL AS PurRetQty,
        NULL AS FinalQty,
        NULL AS ProductionQty,
        NULL AS DmgQty,
        NULL AS AdjQty,
		NULL AS TransInQty,
		NULL AS TransOutQty,
        ISNULL(dt.qtyperunit, 1) AS qtyperunit,
		ISNULL(gs.SUMQTY, 0) AS StockQty
    FROM DeliveryNoteItem dt
    INNER JOIN item ON dt.itemcode = item.itemcode
	LEFT JOIN GetStock gs ON gs.ItemCode = dt.ItemCode
    WHERE (@itemcode IS NULL OR @itemcode = '' OR dt.ItemCode = @itemcode)
      AND dt.Status = 0
      AND dt.EntryDate BETWEEN @fromdate AND @todate
      AND dt.EntryDate >= @stockStartDate
      AND ISNULL(dt.deleted, 'N') = 'N'
      AND ISNULL(item.stkmode, 'Inventory') = 'Inventory'
	   and (BranchName = @BranchName OR @BranchName IS NULL OR @BranchName = '' OR @BranchName='All')  
	  
    GROUP BY dt.EntryDate, dt.DNNo, dt.unit, dt.qtyperunit, gs.SUMQTY

    UNION ALL

    SELECT 
        it.EntryDate, 
        it.InvNo AS REFNO, 
        'Sales' AS TYPE, 
        it.unit AS Unit,
        NULL AS DeliveryQty,
        SUM(it.Qty) AS IssueQty,
        NULL AS IssueRetQty,
        NULL AS PurQty,
        NULL AS PurRetQty,
        NULL AS FinalQty,
        NULL AS ProductionQty,
        NULL AS DmgQty,
        NULL AS AdjQty,
		NULL AS TransInQty,
		NULL AS TransOutQty,
        ISNULL(it.qtyperunit, 1) AS qtyperunit,
		NULL AS StockQty
    FROM InvoiceItem it
    INNER JOIN item ON it.itemcode = item.itemcode
	LEFT JOIN GetStock gs ON gs.ItemCode = it.ItemCode
    WHERE (@itemcode IS NULL OR @itemcode = '' OR it.ItemCode = @itemcode)
      AND it.SR = 'Sales'
      AND ISNULL(it.Deleted, 'N') = 'N'
      AND it.EntryDate BETWEEN @fromdate AND @todate
      AND it.EntryDate >= @stockStartDate
      AND ISNULL(item.stkmode, 'Inventory') = 'Inventory'
	   and (BranchName = @BranchName OR @BranchName IS NULL OR @BranchName = '' OR @BranchName='All')  
    GROUP BY it.EntryDate, it.InvNo, it.unit, it.qtyperunit, gs.SUMQTY

    UNION ALL

    SELECT 
        it.EntryDate, 
        it.InvoiceRetNo AS REFNO, 
        'Returns' AS TYPE, 
        it.unit AS Unit,
        NULL AS DeliveryQty,
        NULL AS IssueQty,
        SUM(it.Qty) AS IssueRetQty,
        NULL AS PurQty,
        NULL AS PurRetQty,
        NULL AS FinalQty,
        NULL AS ProductionQty,
        NULL AS DmgQty,
        NULL AS AdjQty,
		NULL AS TransInQty,
		NULL AS TransOutQty,
        ISNULL(it.qtyperunit, 1) AS qtyperunit,
		NULL AS StockQty
    FROM InvoiceReturnItem it
    INNER JOIN item ON it.itemcode = item.itemcode
	LEFT JOIN GetStock gs ON gs.ItemCode = it.ItemCode
    WHERE (@itemcode IS NULL OR @itemcode = '' OR it.ItemCode = @itemcode)
      AND it.SR = 'Returns'
      AND ISNULL(it.deleted, 'N') = 'N'
      AND it.EntryDate BETWEEN @fromdate AND @todate
      AND it.EntryDate >= @stockStartDate
      AND ISNULL(item.stkmode, 'Inventory') = 'Inventory'
	   and (BranchName = @BranchName OR @BranchName IS NULL OR @BranchName = '' OR @BranchName='All')  
    GROUP BY it.EntryDate, it.InvoiceRetNo, it.unit, it.qtyperunit, gs.SUMQTY

    UNION ALL

    SELECT 
        pt.EntryDate, 
        pt.DocumentNo AS REFNO, 
        'Purchase' AS TYPE, 
        pt.unit AS Unit,
        NULL AS DeliveryQty,
        NULL AS IssueQty,
        NULL AS IssueRetQty,
        SUM(pt.Qty) AS PurQty,
        NULL AS PurRetQty,
        NULL AS FinalQty,
        NULL AS ProductionQty,
        NULL AS DmgQty,
        NULL AS AdjQty,
		NULL AS TransInQty,
		NULL AS TransOutQty,
        ISNULL(pt.qtyperunit, 1) AS qtyperunit,
		NULL AS StockQty
    FROM PurchaseItem pt
    INNER JOIN item ON pt.itemcode = item.itemcode
	LEFT JOIN GetStock gs ON gs.ItemCode = pt.ItemCode
    WHERE (@itemcode IS NULL OR @itemcode = '' OR pt.ItemCode = @itemcode)
      AND pt.PR = 'Purchase'
      AND ISNULL(pt.deleted, 'N') = 'N'
      AND pt.EntryDate BETWEEN @fromdate AND @todate
      AND pt.EntryDate >= @stockStartDate
      AND ISNULL(item.stkmode, 'Inventory') = 'Inventory'
	   and (BranchName = @BranchName OR @BranchName IS NULL OR @BranchName = '' OR @BranchName='All')  
    GROUP BY pt.EntryDate, pt.DocumentNo, pt.unit, pt.qtyperunit, gs.SUMQTY

    UNION ALL

    SELECT 
        pt.EntryDate, 
        pt.DocumentNo AS REFNO, 
        'Returns' AS TYPE, 
        pt.unit AS Unit,
        NULL AS DeliveryQty,
        NULL AS IssueQty,
        NULL AS IssueRetQty,
        NULL AS PurQty,
        SUM(pt.Qty) AS PurRetQty,
        NULL AS FinalQty,
        NULL AS ProductionQty,
        NULL AS DmgQty,
        NULL AS AdjQty,
		NULL AS TransInQty,
		NULL AS TransOutQty,
        ISNULL(pt.qtyperunit, 1) AS qtyperunit,
		NULL AS StockQty
    FROM PurchaseReturnItem pt
    INNER JOIN item ON pt.itemcode = item.itemcode
	LEFT JOIN GetStock gs ON gs.ItemCode = pt.ItemCode
    WHERE (@itemcode IS NULL OR @itemcode = '' OR pt.ItemCode = @itemcode)
      AND pt.PR = 'Returns'
      AND ISNULL(pt.deleted, 'N') = 'N'
      AND pt.EntryDate BETWEEN @fromdate AND @todate
      AND pt.EntryDate >= @stockStartDate
      AND ISNULL(item.stkmode, 'Inventory') = 'Inventory'
	  and (BranchName = @BranchName OR @BranchName IS NULL OR @BranchName = '' OR @BranchName='All')  
    GROUP BY pt.EntryDate, pt.DocumentNo, pt.unit, pt.qtyperunit, gs.SUMQTY

    UNION ALL

    SELECT 
        ft.EntryDate, 
        ft.DocNo AS REFNO, 
        'FinalProduct' AS TYPE, 
        ISNULL(ft.unit, '') AS Unit,
        NULL AS DeliveryQty,
        NULL AS IssueQty,
        NULL AS IssueRetQty,
        NULL AS PurQty,
        NULL AS PurRetQty,
        SUM(ft.Qty) AS FinalQty,
        NULL AS ProductionQty,
        NULL AS DmgQty,
        NULL AS AdjQty,
		NULL AS TransInQty,
		NULL AS TransOutQty,
        1 AS qtyperunit,
		NULL AS StockQty
    FROM FinalProduct ft
    INNER JOIN item ON ft.itemcode = item.itemcode
	LEFT JOIN GetStock gs ON gs.ItemCode = ft.ItemCode
     WHERE (@itemcode IS NULL OR @itemcode = '' OR ft.ItemCode = @itemcode)
      AND ft.EntryDate BETWEEN @fromdate AND @todate
      AND ft.EntryDate >= @stockStartDate
      AND ISNULL(item.stkmode, 'Inventory') = 'Inventory'
	  and (BranchName = @BranchName OR @BranchName IS NULL OR @BranchName = '' OR @BranchName='All')  
    GROUP BY ft.EntryDate, ft.DocNo, ft.unit, gs.SUMQTY

    UNION ALL

    SELECT 
        fpt.EntryDate, 
        fpt.DocNo AS REFNO, 
        'FinalProductItem' AS TYPE, 
        ISNULL(fpt.unit, '') AS Unit,
        NULL AS DeliveryQty,
        NULL AS IssueQty,
        NULL AS IssueRetQty,
        NULL AS PurQty,
        NULL AS PurRetQty,
        NULL AS FinalQty,
        SUM(fpt.Qty) AS ProductionQty,
        NULL AS DmgQty,
        NULL AS AdjQty,
		NULL AS TransInQty,
		NULL AS TransOutQty,
        1 AS qtyperunit,
		NULL AS StockQty
    FROM FinalProductItem fpt
    INNER JOIN item ON fpt.itemcode = item.itemcode
	LEFT JOIN GetStock gs ON gs.ItemCode = fpt.ItemCode
     WHERE (@itemcode IS NULL OR @itemcode = '' OR fpt.ItemCode = @itemcode)
      AND fpt.EntryDate BETWEEN @fromdate AND @todate
      AND fpt.EntryDate >= @stockStartDate
      AND ISNULL(item.stkmode, 'Inventory') = 'Inventory'
	   and (BranchName = @BranchName OR @BranchName IS NULL OR @BranchName = '' OR @BranchName='All')  
    GROUP BY fpt.EntryDate, fpt.DocNo, fpt.unit, gs.SUMQTY

    UNION ALL

    SELECT 
        dt.Date, 
        dt.DocNO AS REFNO, 
        'Damage' AS TYPE, 
        '' AS Unit,
        NULL AS DeliveryQty,
        NULL AS IssueQty,
        NULL AS IssueRetQty,
        NULL AS PurQty,
        NULL AS PurRetQty,
        NULL AS FinalQty,
        NULL AS ProductionQty,
        SUM(dt.Qty) AS DmgQty,
        NULL AS AdjQty,
		NULL AS TransInQty,
		NULL AS TransOutQty,
        1 AS qtyperunit,
		NULL AS StockQty
    FROM DamageItem dt
    INNER JOIN item ON dt.itemcode = item.itemcode
	LEFT JOIN GetStock gs ON gs.ItemCode = dt.ItemCode
    WHERE (@itemcode IS NULL OR @itemcode = '' OR dt.ItemCode = @itemcode)
      AND dt.Date BETWEEN @fromdate AND @todate
      AND dt.Date >= @stockStartDate
      AND ISNULL(item.stkmode, 'Inventory') = 'Inventory'
	  and (BranchName = @BranchName OR @BranchName IS NULL OR @BranchName = '' OR @BranchName='All')  
    GROUP BY dt.Date, dt.DocNO, gs.SUMQTY






	 UNION ALL

    SELECT 
        qad.EntryDate, 
        qad.DocumentNo AS REFNO, 
        'Transfer In' AS TYPE, 
        qad.Unit AS Unit,
        NULL AS DeliveryQty,
        NULL AS IssueQty,
        NULL AS IssueRetQty,
        NULL AS PurQty,
        NULL AS PurRetQty,
        NULL AS FinalQty,
        NULL AS ProductionQty,
        NULL AS DmgQty,
       NULL AS AdjQty,
		ISNULL(SUM(qad.Qty), 0) AS TransInQty,
		
		NULL AS TransOutQty,
       1 AS qtyperunit,
		NULL AS StockQty
     FROM MaterialTransDetails qad
    INNER JOIN item ON qad.itemcode = item.itemcode
    LEFT JOIN MaterialTrans qa ON qa.voucherNo = qad.DocumentNo
	LEFT JOIN GetStock gs ON gs.ItemCode = qad.ItemCode
    WHERE (@itemcode IS NULL OR @itemcode = '' OR qad.ItemCode = @itemcode)
      AND qad.EntryDate BETWEEN @fromdate AND @todate
      AND qad.EntryDate >= @stockStartDate
      AND ISNULL(item.stkmode, 'Inventory') = 'Inventory'
	  and (qad.BranchName = @BranchName OR @BranchName IS NULL OR @BranchName = '' OR @BranchName='All')  
    GROUP BY qad.EntryDate, qad.DocumentNo, qad.Unit, qad.qtyperunit, gs.SUMQTY


	

	 UNION ALL

    SELECT 
        qad.EntryDate, 
        qad.DocumentNo AS REFNO, 
        'Transfer Out' AS TYPE, 
        qad.Unit AS Unit,
        NULL AS DeliveryQty,
        NULL AS IssueQty,
        NULL AS IssueRetQty,
        NULL AS PurQty,
        NULL AS PurRetQty,
        NULL AS FinalQty,
        NULL AS ProductionQty,
        NULL AS DmgQty,
       NULL AS AdjQty,
		NULL AS TransInQty,
		ISNULL(SUM(qad.Qty), 0) AS TransOutQty,
		
       1 AS qtyperunit,
		ISNULL(gs.SUMQTY, 0) AS StockQty
     FROM MaterialTransOutDetails qad
    INNER JOIN item ON qad.itemcode = item.itemcode
    LEFT JOIN MaterialTransOut qa ON qa.voucherNo = qad.DocumentNo
	LEFT JOIN GetStock gs ON gs.ItemCode = qad.ItemCode
    WHERE (@itemcode IS NULL OR @itemcode = '' OR qad.ItemCode = @itemcode)
      AND qad.EntryDate BETWEEN @fromdate AND @todate
      AND qad.EntryDate >= @stockStartDate
      AND ISNULL(item.stkmode, 'Inventory') = 'Inventory'
	  and (qad.BranchName = @BranchName OR @BranchName IS NULL OR @BranchName = '' OR @BranchName='All')  
    GROUP BY qad.EntryDate, qad.DocumentNo, qad.Unit, qad.QtyPUnit, gs.SUMQTY









    UNION ALL

    SELECT 
        qad.EntryDate, 
        qad.DocNo AS REFNO, 
        'Adjustment' AS TYPE, 
        qad.Unit AS Unit,
        NULL AS DeliveryQty,
        NULL AS IssueQty,
        NULL AS IssueRetQty,
        NULL AS PurQty,
        NULL AS PurRetQty,
        NULL AS FinalQty,
        NULL AS ProductionQty,
        NULL AS DmgQty,
        ISNULL(SUM(qad.Qty), 0) AS AdjQty,
		NULL AS TransInQty,
		NULL AS TransOutQty,
        ISNULL(qad.qtyperunit, 1) AS qtyperunit,
		ISNULL(gs.SUMQTY, 0) AS StockQty
     FROM QtyAdjustmentDetails qad
    INNER JOIN item ON qad.itemcode = item.itemcode
    LEFT JOIN QtyAdjustment qa ON qa.DocNo = qad.DocNo
	LEFT JOIN GetStock gs ON gs.ItemCode = qad.ItemCode
    WHERE (@itemcode IS NULL OR @itemcode = '' OR qad.ItemCode = @itemcode)
      AND qad.EntryDate BETWEEN @fromdate AND @todate
      AND qad.EntryDate >= @stockStartDate
      AND ISNULL(item.stkmode, 'Inventory') = 'Inventory'
	  and (qad.BranchName = @BranchName OR @BranchName IS NULL OR @BranchName = '' OR @BranchName='All')  
    GROUP BY qad.EntryDate, qad.DocNo, qad.Unit, qad.qtyperunit, gs.SUMQTY

	) A

    -- Optional Return the inserted data, or if you need to select from ItemHistory
    GROUP BY ENTRYDATE,REFNO,TYPE,UNIT,QTYPERUNIT
    ORDER BY EntryDate;
END