SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[SP_OpstockNewMethod]  (@todate datetime,@Itemcode nVarchar(500),@BRANCHNAME nvarchar(500))            
RETURNS float      
AS        
BEGIN       
DECLARE @stockstartdate AS datetime

DECLARE @STK AS DECIMAL
set @stockstartdate=(select isnull(stockdate,'01/01/1900')'date' from stockstartdate)  
 
     
	  BEGIN
select @STK= sum(a.QTY)
FROM 
(select sum(qty)as qty from (
select  sum(OPSTOCK) as qty from OPStockUpdatedDetails WHERE  isnull(itemcode,'')=isnull(@Itemcode,isnull(itemcode,''))   and (BranchName = @BranchName OR @BranchName IS NULL OR @BranchName = '' OR @BranchName='All')  
UNION all
  select isnull(sum(isnull(qty,0)*isnull(qtyperunit,1)),0) as qty from PurchaseItem where PurchaseItem.entrydate>= @stockstartdate and  entryDate < @todate and PR='Purchase'  and isnull(deleted,'N')='N'   
  AND (@itemcode IS NULL OR @itemcode = '' OR PurchaseItem.ItemCode = @itemcode)  
  and (BranchName = @BranchName OR @BranchName IS NULL OR @BranchName = '' OR @BranchName='All')  
  UNION all
   select -1*isnull(sum(isnull(qty,0)*isnull(qtyperunit,1)),0) as qty from InvoiceItem where InvoiceItem.entrydate>= @stockstartdate and  entryDate < @todate and SR='sales'  and isnull(deleted,'N')='N'  AND (@itemcode IS NULL OR @itemcode = '' OR InvoiceItem.ItemCode = @itemcode)  and (BranchName = @BranchName OR @BranchName IS NULL OR @BranchName = '' OR @BranchName='All')  
    UNION all
	select isnull(sum(qty),0) as qty from MaterialTransDetails qad  
	 LEFT JOIN MaterialTrans qa ON qa.voucherNo = qad.DocumentNo
	  WHERE (@itemcode IS NULL OR @itemcode = '' OR qad.ItemCode = @itemcode)
      AND qad.EntryDate < @todate
      AND qad.EntryDate >= @stockStartDate
       
	  and (qad.BranchName = @BranchName OR @BranchName IS NULL OR @BranchName = '' OR @BranchName='All')  
	
   UNION all
  select sum(qty) from FINALPRODUCT  WHERE EntryDate>=@stockstartdate AND  ITEMCODE=@Itemcode  and  entryDate < @todate   and (BranchName = @BranchName OR @BranchName IS NULL OR @BranchName = '' OR @BranchName='All')  
union all
select -sum(qty) from FINALPRODUCTITEM  WHERE  Entrydate>=@stockstartdate AND  ITEMCODE=@Itemcode   and  entryDate < @todate   and (BranchName = @BranchName OR @BranchName IS NULL OR @BranchName = '' OR @BranchName='All')  
union all
  select -1* isnull(sum(isnull(qty,0)*isnull(qtyperunit,1)),0) as qty from PurchaseRETURNItem where PurchaseRETURNItem.entrydate>= @stockstartdate and  entryDate < @todate and PR='Returns'  and isnull(deleted,'N')='N' 
  AND (@itemcode IS NULL OR @itemcode = '' OR PurchaseRETURNItem.ItemCode = @itemcode)  and (BranchName = @BranchName OR @BranchName IS NULL OR @BranchName = '' OR @BranchName='All')  
  UNION all
    select  isnull(sum(isnull(qty,0)*isnull(qtyperunit,1)),0) as qty from InvoiceRETURNItem where InvoiceRETURNItem.entrydate>= @stockstartdate and  entryDate < @todate and SR='Returns' 
	and isnull(deleted,'N')='N'   AND (@itemcode IS NULL OR @itemcode = '' OR InvoiceRETURNItem.ItemCode = @itemcode)  and (BranchName = @BranchName OR @BranchName IS NULL OR @BranchName = '' OR @BranchName='All')  
  --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'  Group by ItemCode 
   UNION all
  select -1* isnull(sum(isnull(qty,0)*isnull(qtyperunit,1)),0) as qty from DeliveryNoteItem where DeliveryNoteItem.entrydate>= @stockstartdate and  entryDate < @todate   AND DeliveryNoteItem.STATUS=0
  and isnull(deleted,'N')='N'  AND (@itemcode IS NULL OR @itemcode = '' OR DeliveryNoteItem.ItemCode = @itemcode)  and (BranchName = @BranchName OR @BranchName IS NULL OR @BranchName = '' OR @BranchName='All')  
 
 UNION all
  select -1* isnull(sum(qty),0) as qty from damageItem
  where damageItem.Date>= @stockstartdate and  Date < @todate   AND (@itemcode IS NULL OR @itemcode = '' OR damageItem.ItemCode = @itemcode)  and (BranchName = @BranchName OR @BranchName IS NULL OR @BranchName = '' OR @BranchName='All')  
  UNION all
  select -1* isnull(sum(qty),0)as qty from  MaterialTransOutDetails qad
        LEFT JOIN MaterialTransOut qa ON qa.voucherNo = qad.DocumentNo
  WHERE   qad.entrydate>= @stockstartdate  and qad.entrydate< @todate   
  AND (@itemcode IS NULL OR @itemcode = '' OR qad.ItemCode = @itemcode)
	  and (qad.BranchName = @BranchName OR @BranchName IS NULL OR @BranchName = '' OR @BranchName='All')  
    Group by ItemCode
   UNION all
   select  isnull(sum(isnull(qty,0)*isnull(qtyperunit,1)),0) as Qty from QtyAdjustmentDetails qad WHERE (@itemcode IS NULL OR @itemcode = '' OR qad.ItemCode = @itemcode)
      AND qad.EntryDate < @todate
      AND qad.EntryDate >= @stockStartDate
      	  and (qad.BranchName = @BranchName OR @BranchName IS NULL OR @BranchName = '' OR @BranchName='All')  


   ) t 

 )a
  END
  
  RETURN @STK
  END