SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SP_stockupdateDateWiseNEWMETHOD]           
@todate datetime        
AS        
BEGIN       
DECLARE @stockstartdate AS datetime
DECLARE @DeliveryEffect AS integer
set @stockstartdate=(select isnull(stockdate,'01/01/1900')'date' from stockstartdate)  
set @DeliveryEffect=(select isnull(DeliveryEffect,1)'DeliveryEffect' from PermanentSettings) 
update item set stock=0
   if @DeliveryEffect=1
begin
    Update ITEM 
SET Item.STOCK = a.QTY
FROM 
(select sum(qty)as qty,itemcode from (

select  isnull(sum(Quantity*isnull(qtyperunit,1)),0) as qty,ItemCode from OpeningStockEntryItemForupdate where entrydate>= @stockstartdate and  entryDate <= @todate  Group by ItemCode

UNION all
  select isnull(sum(isnull(qty,0)*isnull(qtyperunit,1)),0) as qty,ITEMCODE from PurchaseItem where PurchaseItem.entrydate>= @stockstartdate and  entryDate <= @todate and PR='Purchase'  and isnull(deleted,'N')='N'  Group by ItemCode
  UNION all
   select isnull(sum(isnull(qty,0)*isnull(qtyperunit,1)),0) as qty,ITEMCODE from InvoiceItem where InvoiceItem.entrydate>= @stockstartdate and  entryDate <= @todate and SR='Returns'  and isnull(deleted,'N')='N'   Group by ItemCode
    UNION all
	select isnull(sum(qty),0) as qty,ITEMCODE from MaterialTransDetails   INNER JOIN MaterialTrans ON MaterialTransDetails.INVNO=MaterialTrans.INVNO  WHERE ItemCode NOT IN ('SER','DLC')   and MaterialTransDetails.entrydate>=@stockstartdate and MaterialTransDetails.entrydate<=@todate    Group by ItemCode
   
   UNION all
   select -1* isnull(sum(isnull(qty,0)*isnull(qtyperunit,1)),0) as qty,ITEMCODE from PurchaseItem where PurchaseItem.entrydate>= @stockstartdate and  entryDate <= @todate and PR='Returns'  and isnull(deleted,'N')='N'  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'   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'  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 isnull(deleted,'N')='N'and Status=0    Group by ItemCode 
 
  UNION all
 select sum(qty),ItemCode from ProductionMasterItemFP  inner join ProductionEntryMaster on ProductionEntryMaster.docno=ProductionMasterItemFP.docno WHERE ItemCode NOT IN ('SER','DLC') and EntryDate>=@stockstartdate and  entryDate <= @todate  Group by ItemCode
union all
select -sum(qty),ItemCode from ProductionMasterItem  inner join ProductionEntryMaster on ProductionEntryMaster.docno=ProductionMasterItem.docno WHERE ItemCode NOT IN ('SER','DLC')  and Entrydate>=@stockstartdate and  entryDate <= @todate  Group by ItemCode
union all
  select -1* isnull(sum(qty),0) as qty,ITEMCODE from damageItem where damageItem.Date>= @stockstartdate and  Date <= @todate   Group by ItemCode
  UNION all
  select -1* isnull(sum(qty),0)as qty,ITEMCODE from MaterialTransOutDetails  INNER JOIN MaterialTransOut ON MaterialTransOutDetails.INVNO=MaterialTransOut.INVNO 
  WHERE ItemCode NOT IN ('SER','DLC') and MaterialTransOutDetails.entrydate>= @stockstartdate  and MaterialTransOutDetails.entrydate<= @todate  AND FROMSTORE IN (SELECT NAME FROM BRANCHMASTER ) 
    Group by ItemCode
   UNION all
   select  isnull(sum(isnull(qty,0)*isnull(qtyperunit,1)),0) as Qty,ITEMCODE from QtyAdjustmentDetails Where entryDate <= @todate and entryDate>=@stockstartdate   Group by ItemCode) t 

 group by  t.itemcode )a



   WHERE item.itemcode = a.itemcode  AND ITEM.StkMode='Inventory'


END
ELSE
BEGIN
 Update ITEM 
SET Item.STOCK = a.QTY
FROM 
(select sum(qty)as qty,itemcode from (

select  isnull(sum(Quantity*isnull(qtyperunit,1)),0) as qty,ItemCode from OpeningStockEntryItemForupdate where entrydate>= @stockstartdate and  entryDate <= @todate  Group by ItemCode

UNION all
  select isnull(sum(isnull(qty,0)*isnull(qtyperunit,1)),0) as qty,ITEMCODE from PurchaseItem where PurchaseItem.entrydate>= @stockstartdate and  entryDate <= @todate and PR='Purchase'  and isnull(deleted,'N')='N'  Group by ItemCode
  UNION all
   select isnull(sum(isnull(qty,0)*isnull(qtyperunit,1)),0) as qty,ITEMCODE from InvoiceItem where InvoiceItem.entrydate>= @stockstartdate and  entryDate <= @todate and SR='Returns'  and isnull(deleted,'N')='N'   Group by ItemCode
    UNION all
	select isnull(sum(qty),0) as qty,ITEMCODE from MaterialTransDetails   INNER JOIN MaterialTrans ON MaterialTransDetails.INVNO=MaterialTrans.INVNO  WHERE ItemCode NOT IN ('SER','DLC')   and MaterialTransDetails.entrydate>=@stockstartdate and MaterialTransDetails.entrydate<=@todate    Group by ItemCode
   
   UNION all
   select -1* isnull(sum(isnull(qty,0)*isnull(qtyperunit,1)),0) as qty,ITEMCODE from PurchaseItem where PurchaseItem.entrydate>= @stockstartdate and  entryDate <= @todate and PR='Returns'  and isnull(deleted,'N')='N'  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'   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 isnull(deleted,'N')='N'  Group by ItemCode 
 
  UNION all
 select sum(qty),ItemCode from ProductionMasterItemFP  inner join ProductionEntryMaster on ProductionEntryMaster.docno=ProductionMasterItemFP.docno WHERE ItemCode NOT IN ('SER','DLC') and EntryDate>=@stockstartdate and  entryDate <= @todate  Group by ItemCode
union all
select -sum(qty),ItemCode from ProductionMasterItem  inner join ProductionEntryMaster on ProductionEntryMaster.docno=ProductionMasterItem.docno WHERE ItemCode NOT IN ('SER','DLC')  and Entrydate>=@stockstartdate and  entryDate <= @todate  Group by ItemCode
union all
  select -1* isnull(sum(qty),0) as qty,ITEMCODE from damageItem where damageItem.Date>= @stockstartdate and  Date <= @todate   Group by ItemCode
  UNION all
  select -1* isnull(sum(qty),0)as qty,ITEMCODE from MaterialTransOutDetails  INNER JOIN MaterialTransOut ON MaterialTransOutDetails.INVNO=MaterialTransOut.INVNO 
  WHERE ItemCode NOT IN ('SER','DLC') and MaterialTransOutDetails.entrydate>= @stockstartdate  and MaterialTransOutDetails.entrydate<= @todate  AND FROMSTORE IN (SELECT NAME FROM BRANCHMASTER ) 
    Group by ItemCode
   UNION all
   select  isnull(sum(isnull(qty,0)*isnull(qtyperunit,1)),0) as Qty,ITEMCODE from QtyAdjustmentDetails Where entryDate <= @todate and entryDate>=@stockstartdate   Group by ItemCode) t 

 group by  t.itemcode )a



   WHERE item.itemcode = a.itemcode   AND ITEM.StkMode='Inventory'
   END
   END