ALTER TABLE Invoice
ADD 
    PlateNo VARCHAR(500),
    Category VARCHAR(500),
    ModelNo VARCHAR(500),
    ChassisNo VARCHAR(500),
    Brand VARCHAR(500),
    Colour VARCHAR(500),
    Kilometer VARCHAR(500),
    TimeIn VARCHAR(500),
    TimeOut VARCHAR(500),
    VehicleRemarks VARCHAR(500),
    Comments VARCHAR(500),
    DeliveredBy VARCHAR(500),
 joborderno VARCHAR(500)
;





insert into setup(code,svalue) values('Replacement','1000');







	GO
/****** Object:  StoredProcedure [dbo].[ProfitandLossSummary]    Script Date: 07-12-2024 10:30:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[ProfitandLossSummary]
@fromdt datetime,
@todt datetime,
@Branchcode nvarchar(500)  
as
begin

DECLARE @closingstock  FLOAT
DECLARE @OpeningStock  FLOAT
DECLARE @sales  FLOAT
DECLARE @Expenses  FLOAT
DECLARE @Purchases  FLOAT
DECLARE @Income FLOAT
--clstock
select @closingstock= ISNULL([dbo].udf_SelectStockValue(@todt,@Branchcode),0) 
--opstock
select @OpeningStock=ISNULL([dbo].udf_SelectStockValue(DATEADD(dAY, -1, @fromdt),@Branchcode),0) 
--sales
select @sales=-isnull(sum(isnull(Amount,0)),0)  from tblaccounting  where entryDate between @fromdt and @todt and accode ='95100'  AND (@BranchCode IS NULL OR @BranchCode = '' OR ISNULL(TBLACCOUNTING.BranchCode, '') = @BranchCode) 
--cashentrysummary
SELECT @Expenses=isnull(SUM(isnull(S.Amount,0)),0)  FROM Accounts C INNER JOIN tblaccounting S ON C.Code = S.AcCode where S.EntryDate between @fromdt and @todt  and C.Type='Expense' AND C.CODE<>'95200'  AND (@BranchCode IS NULL OR @BranchCode = '' OR ISNULL(s.BranchCode, '') = @BranchCode) 
--purchase
select  @Purchases=isnull(sum(Amount),0) from tblaccounting  where entryDate between @fromdt and @todt and AcCode='95200' AND (@BranchCode IS NULL OR @BranchCode = '' OR ISNULL(TBLACCOUNTING.BranchCode, '') = @BranchCode) 
--income summary
SELECT @Income=isnull(SUM(isnull(S.Amount,0)),0) FROM Accounts C INNER JOIN tblaccounting S ON C.Code = S.AcCode where S.EntryDate between @fromdt and @todt  and C.Type='Income' and c.code<>'95100'  AND (@BranchCode IS NULL OR @BranchCode = '' OR ISNULL(s.BranchCode, '') = @BranchCode) 



select a.* from (

--expense detail
SELECT   @closingstock AS closingstock ,@OpeningStock AS OpeningStock,@Sales AS Sales,@Expenses AS Expenses,@Purchases AS Purchases,C.Account as ExpAcName,SUM(S.Amount) as ExpAmount,@Income AS Income,'' AS IncomeAcName,'' AS IncomeAmount,'Exp/Inc' as ExpIncflag
FROM Accounts C INNER JOIN tblaccounting S ON C.Code = S.AcCode where S.EntryDate between @fromdt and @todt  and C.Type in ('Expense','Income')  and c.code<>'95100' and c.code<>'95200'  AND (@BranchCode IS NULL OR @BranchCode = '' OR ISNULL(s.BranchCode, '') = @BranchCode)  GROUP BY C.Code, C.Account,c.type
union all
SELECT   @closingstock AS closingstock ,@OpeningStock AS OpeningStock,@Sales AS Sales,@Expenses AS Expenses,@Purchases AS Purchases,C.Account as ExpAcName,SUM(S.Amount) as ExpAmount,@Income AS Income,'' AS IncomeAcName,'' AS IncomeAmount,'Exp/Inc' as ExpIncflag
FROM Accounts C INNER JOIN tblaccounting S ON C.Code = S.AcCode where S.EntryDate between @fromdt and @todt  and C.Type in ('Expense','Income')    AND (@BranchCode IS NULL OR @BranchCode = '' OR ISNULL(s.BranchCode, '') = @BranchCode)  GROUP BY C.Code, C.Account,c.type

union all


--income detail
SELECT    @closingstock AS closingstock ,@OpeningStock AS OpeningStock,@Sales AS Sales,@Expenses AS Expenses,@Purchases AS Purchases,''AS ExpAcName,'' AS ExpAmount,@Income AS Income,C.Account as IncomeAcName,SUM(S.Amount) as IncomeAmount,'Exp/Inc' as ExpIncflag
FROM Accounts C INNER JOIN tblaccounting S ON C.Code = S.AcCode where S.EntryDate between @fromdt and @todt  and C.Type='Income'   and c.code<>'95100'  AND (@BranchCode IS NULL OR @BranchCode = '' OR ISNULL(s.BranchCode, '') = @BranchCode)  GROUP BY C.Code, C.Account
union all
SELECT    @closingstock AS closingstock ,@OpeningStock AS OpeningStock,@Sales AS Sales,@Expenses AS Expenses,@Purchases AS Purchases,''AS ExpAcName,'' AS ExpAmount,@Income AS Income,C.Account as IncomeAcName,SUM(S.Amount) as IncomeAmount,'Exp/Inc' as ExpIncflag
FROM Accounts C INNER JOIN tblaccounting S ON C.Code = S.AcCode where S.EntryDate between @fromdt and @todt  and C.Type='Income'    AND (@BranchCode IS NULL OR @BranchCode = '' OR ISNULL(s.BranchCode, '') = @BranchCode)  GROUP BY C.Code, C.Account
)a 


end
go


/****** Object:  StoredProcedure [dbo].[sp_InvoiceWiseProfit]    Script Date: 07-12-2024 10:28:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[sp_InvoiceWiseProfit]        
@FromDate datetime,@ToDate datetime        
AS        
begin      
SELECT A.* FROM (  
SELECT InvoiceItem.ItemCode,InvoiceItem.ItemName,InvoiceItem.InvNo,        
InvoiceItem.EntryDate,(InvoiceItem.Rate-(((((isnull(Invoice.LessAmount,0)+isnull(Invoice.AddLess,0)+Invoice.Commision)*100)/Invoice.GrossAmount)/100)*InvoiceItem.Rate)) as Rate,         
ISNULL(InvoiceItem.Qty,0)AS QTY ,InvoiceItem.SR, InvoiceItem.Amount,invoice.Commision,
(CASE WHEN isnull(InvoiceItem.stdcost,0)=0 THEN ISNULL(ITEM.StandardCost,0) ELSE isnull(InvoiceItem.stdcost,0) END)
--isnull(InvoiceItem.stdcost,ITEM.StandardCost) 
as StandardCost,0 as BOXQTY,0 as PKTQTY      
,InvoiceItem.UNIT, ISNULL(InvoiceItem.QTYPERUNIT,1)AS QTYPERUNIT,
ISNULL(InvoiceItem.VATPER,0)as VATPER,ISNULL(InvoiceItem.VATAMT,0)as VATAMTT,ISNULL((SELECT NAME FROM CATEGORY WHERE CODE =Item.Category),'') AS Category 
FROM   InvoiceItem INNER JOIN ITEM ON InvoiceItem.ItemCode = ITEM.itemcode         
INNER JOIN Invoice ON Invoice.Invno = Invoiceitem.Invno and InvoiceItem.SR='Sales' and Invoice.SR='Sales'       
where isnull(Invoice.deleted,'N')='N' and Invoice.GrossAmount <> 0 and Invoiceitem.qty<>0 and InvoiceItem.SR='Sales'  
--and  InvoiceItem.EntryDate         between @FromDate  and @ToDate   
and Invoice.INVOICEDate between @FromDate and @ToDate 
union    ALL  
SELECT InvoiceItem.ItemCode,InvoiceItem.ItemName,InvoiceItem.InvNo,        
InvoiceItem.EntryDate,(InvoiceItem.Rate-(((((isnull(Invoice.LessAmount,0)+isnull(Invoice.AddLess,0)+isnull(Invoice.Commision,0))*100)/Invoice.GrossAmount)/100)*InvoiceItem.Rate)) as Rate,         
ISNULL(InvoiceItem.Qty,0)AS QTY ,InvoiceItem.SR, InvoiceItem.Amount,invoice.Commision,
(CASE WHEN isnull(InvoiceItem.stdcost,0)=0 THEN ISNULL(ITEM.StandardCost,0) ELSE isnull(InvoiceItem.stdcost,0) END)
--isnull(InvoiceItem.stdcost,ITEM.StandardCost)
 as StandardCost,0 as BOXQTY,0 as PKTQTY      
,InvoiceItem.UNIT, ISNULL(InvoiceItem.QTYPERUNIT,0)AS QTYPERUNIT,
ISNULL(InvoiceItem.VATPER,0)as VATPER,ISNULL(InvoiceItem.VATAMT,0)as VATAMTT,ISNULL((SELECT NAME FROM CATEGORY WHERE CODE =Item.Category),'') AS Category 
FROM   InvoiceItem INNER JOIN ITEM ON InvoiceItem.ItemCode = ITEM.itemcode         
INNER JOIN Invoice ON Invoice.InvoiceRetNo = Invoiceitem.InvoiceRetNo and InvoiceItem.SR='RETURNS'  and Invoice.SR='RETURNS'       
where isnull(Invoice.deleted,'N')='N' and Invoice.GrossAmount <> 0 and Invoiceitem.qty<>0 and InvoiceItem.SR='RETURNS' and  InvoiceItem.EntryDate         
between @FromDate  and @ToDate   
)A
ORDER BY A.ENTRYDATE,A.INVNO

end




GO
/****** Object:  StoredProcedure [dbo].[sp_InvoiceWiseProfit]    Script Date: 19-12-2024 20:40:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[sp_InvoiceWiseProfit]        
@FromDate datetime,@ToDate datetime ,@Custcode varchar(200), @SalesMan varchar(200), @InvoiceType varchar(200), @SalesType varchar(200),@BranchCode varchar(200)       
AS        
begin      
SELECT A.* FROM (  
SELECT InvoiceItem.ItemCode,InvoiceItem.ItemName,InvoiceItem.InvNo,        
InvoiceItem.EntryDate,(InvoiceItem.Rate-(((((isnull(Invoice.LessAmount,0)+isnull(Invoice.AddLess,0)+Invoice.Commision)*100)/Invoice.GrossAmount)/100)*InvoiceItem.Rate)) as Rate,         
ISNULL(InvoiceItem.Qty,0)AS QTY ,InvoiceItem.SR, InvoiceItem.Amount,invoice.Commision,
(CASE WHEN isnull(InvoiceItem.stdcost,0)=0 THEN ISNULL(ITEM.StandardCost,0) ELSE isnull(InvoiceItem.stdcost,0) END)
--isnull(InvoiceItem.stdcost,ITEM.StandardCost) 
as StandardCost,0 as BOXQTY,0 as PKTQTY      
,InvoiceItem.UNIT, ISNULL(InvoiceItem.QTYPERUNIT,1)AS QTYPERUNIT,
ISNULL(InvoiceItem.VATPER,0)as VATPER,ISNULL(InvoiceItem.VATAMT,0)as VATAMTT,ISNULL((SELECT NAME FROM CATEGORY WHERE CODE =Item.Category),'') AS Category 
FROM   InvoiceItem INNER JOIN ITEM ON InvoiceItem.ItemCode = ITEM.itemcode         
INNER JOIN Invoice ON Invoice.Invno = Invoiceitem.Invno and InvoiceItem.SR='Sales' and Invoice.SR='Sales'       
where isnull(Invoice.deleted,'N')='N' and Invoice.GrossAmount <> 0 and Invoiceitem.qty<>0 and InvoiceItem.SR='Sales'  
--and  InvoiceItem.EntryDate         between @FromDate  and @ToDate   
and Invoice.INVOICEDate between @FromDate and @ToDate 
   
			
	 and(Invoice.CustomerCode = @Custcode OR @Custcode IS NULL OR @Custcode = '' OR @Custcode = '1') 
	 and (Invoice.Attended = @SalesMan OR @SalesMan IS NULL OR @SalesMan = '')  
	 and(Invoice.BranchCode = @BranchCode OR @BranchCode IS NULL OR @BranchCode = '' OR @BranchCode = '1')          
           
union    ALL  
SELECT InvoiceReturnItem.ItemCode,InvoiceReturnItem.ItemName,InvoiceReturnItem.InvNo,        
InvoiceReturnItem.EntryDate,(InvoiceReturnItem.Rate-(((((isnull(InvoiceReturn.LessAmount,0)+isnull(InvoiceReturn.AddLess,0)+isnull(InvoiceReturn.Commision,0))*100)/InvoiceReturn.GrossAmount)/100)*InvoiceReturnItem.Rate)) as Rate,         
ISNULL(InvoiceReturnItem.Qty,0)AS QTY ,InvoiceReturnItem.SR, InvoiceReturnItem.Amount,InvoiceReturn.Commision,
(CASE WHEN isnull(InvoiceReturnItem.stdcost,0)=0 THEN ISNULL(ITEM.StandardCost,0) ELSE isnull(InvoiceReturnItem.stdcost,0) END)
--isnull(InvoiceItem.stdcost,ITEM.StandardCost)
 as StandardCost,0 as BOXQTY,0 as PKTQTY      
,InvoiceReturnItem.UNIT, ISNULL(InvoiceReturnItem.QTYPERUNIT,0)AS QTYPERUNIT,
ISNULL(InvoiceReturnItem.VATPER,0)as VATPER,ISNULL(InvoiceReturnItem.VATAMT,0)as VATAMTT,ISNULL((SELECT NAME FROM CATEGORY WHERE CODE =Item.Category),'') AS Category 
FROM   InvoiceReturnItem INNER JOIN ITEM ON InvoiceReturnItem.ItemCode = ITEM.itemcode         
INNER JOIN InvoiceReturn ON InvoiceReturn.InvoiceRetNo = InvoiceReturnItem.InvoiceRetNo and InvoiceReturnItem.SR='RETURNS'  and InvoiceReturn.SR='RETURNS'       
where isnull(InvoiceReturn.deleted,'N')='N' and InvoiceReturn.GrossAmount <> 0 and InvoiceReturnItem.qty<>0 and InvoiceReturnItem.SR='RETURNS' and  InvoiceReturnItem.EntryDate         
between @FromDate  and @ToDate   AND (@InvoiceType = 'all' OR InvoiceReturn.InvType = @InvoiceType)
			 and isnull(InvoiceReturn.CustomerCode,'')=isnull(@Custcode,isnull(InvoiceReturn.CustomerCode,''))
	
	 and (InvoiceReturn.Attended = @SalesMan OR @SalesMan IS NULL OR @SalesMan = '')  
	 and(InvoiceReturn.BranchCode = @BranchCode OR @BranchCode IS NULL OR @BranchCode = '' OR @BranchCode = '1')  
)A
ORDER BY A.ENTRYDATE,A.INVNO

end



GO
create PROCEDURE [dbo].[SP_stockupdateDateWiseStockList]           
    @todate DATETIME,
	@BranchName varchar(100)
	
AS        
BEGIN       
    DECLARE @stockstartdate AS DATETIME;

    
    SET @stockstartdate = (SELECT ISNULL(stockdate, '01/01/1900') AS 'date' FROM stockstartdate);

    -- Insert the aggregated quantity and item code into GetStock table
    INSERT INTO GetStock (SUMQTY, ItemCode)
    SELECT SUM(qty) AS SUMQTY, ITEMCODE 
    FROM (
        -- Summing the quantities by ItemCode
        SELECT ISNULL(SUM(ISNULL(opstock, 0)), 0) AS qty, ITEMCODE 
        FROM ITEM 
        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'  
		 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.INVNO = MaterialTrans.INVNO  
        WHERE ITEMCODE NOT IN ('SER', 'DLC')   
          AND MaterialTransDetails.entrydate >= @stockstartdate 
          AND MaterialTransDetails.entrydate <= @todate    
		  AND (@BranchName = 'All' OR MaterialTransDetails.branchname = @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.INVNO = MaterialTransOut.INVNO 
        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.branchname = @BranchName)
        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  
		   AND (@BranchName = 'All' OR branchname = @BranchName)
        GROUP BY ITEMCODE
    ) t 
    GROUP BY t.ITEMCODE;

END



GO
create PROCEDURE [dbo].[SP_stockupdateDateWiseStockValueNew]           
    @todate DATETIME   ,
	@BranchName varchar(250)
AS        
BEGIN       
    DECLARE @stockstartdate AS DATETIME;

    
    SET @stockstartdate = (SELECT ISNULL(stockdate, '01/01/1900') AS 'date' FROM stockstartdate);

    -- Insert the aggregated quantity and item code into GetStock table
    INSERT INTO GetStockValue (SUMQTY, ItemCode)
    SELECT SUM(qty) AS SUMQTY, ITEMCODE 
    FROM (
        -- Summing the quantities by ItemCode
        SELECT ISNULL(SUM(ISNULL(opstock, 0)), 0) AS qty, ITEMCODE 
        FROM ITEM 
        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 (@BranchName = 'All' OR branchname = @BranchName)
          AND ISNULL(deleted, 'N') = 'N'  
        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.INVNO = MaterialTrans.INVNO  
        WHERE ITEMCODE NOT IN ('SER', 'DLC')   
          AND MaterialTransDetails.entrydate >= @stockstartdate 
          AND MaterialTransDetails.entrydate <= @todate    
		   AND (@BranchName = 'All' OR MaterialTransDetails.branchname = @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.INVNO = MaterialTransOut.INVNO 
        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.branchname = @BranchName)
        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   
		   AND (@BranchName = 'All' OR branchname = @BranchName)
        GROUP BY ITEMCODE
    ) t 
    GROUP BY t.ITEMCODE;

END


drop table PermanentSettings


GO

CREATE TABLE PermanentSettings(
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[SalesRate] [varchar](255) NOT NULL,
	[DeliveryStock] [varchar](255) NULL,
	[DeliveryTbl] [varchar](255) NULL,
PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO


GO

INSERT INTO [dbo].[PermanentSettings]
           ([SalesRate]
           ,[DeliveryStock]
           ,[DeliveryTbl])
     VALUES
           (1
           ,1
           ,2)
GO


GO
/****** Object:  StoredProcedure [dbo].[sp_SelectPendingPurchaseDetails]    Script Date: 26-12-2024 12:28:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_SelectPendingPurchaseDetails]        
@Flag integer,          
@SupplierCode as varchar(50),       
@FromDate datetime,        
@ToDate datetime,
@BranchCode as varchar(50)
       
AS            
begin          
if @Flag=1      
begin          
select DocumentNo,InvoiceNo,SupplierCode,SupplierName,EntryDate,NetAmount,
([dbo].udf_SelectReceivedAmountOfSupplier(DocumentNo,@BranchCode)) as ReceivedAmount ,--+[dbo].udf_RetAmtForSupplierPayment(SupplierCode,DocumentNo)
([dbo].udf_SelectDiscountAmountOfSupplier(DocumentNo,@BranchCode)) as DiscountAmount ,
(isnull(NetAmount,0)-([dbo].udf_SelectReceivedAmountOfSupplier(DocumentNo,@BranchCode)+([dbo].udf_SelectDiscountAmountOfSupplier(DocumentNo,@BranchCode)) +[dbo].udf_RetAmtForSupplierPayment(SupplierCode,DocumentNo,@BranchCode)))as BalAmt ,
isnull([dbo].udf_RetAmtForSupplierPayment(SupplierCode,DocumentNo,@BranchCode),0) AS RetAmt,'P' as ttype ,isnull(([dbo].udf_SelectDiscountAmountOfSupplier(DocumentNo,@BranchCode)),0) as DiscountAmt from Purchase         
where  Billtype='Credit' and PR='Purchase'  
and (NetAmount-([dbo].udf_SelectReceivedAmountOfSupplier(DocumentNo,@BranchCode)+([dbo].udf_SelectDiscountAmountOfSupplier(DocumentNo,@BranchCode)) +[dbo].udf_RetAmtForSupplierPayment(SupplierCode,DocumentNo,@BranchCode)))>0           
and CONVERT(VARCHAR(25),Purchase.EntryDate,112)>= @FromDate and CONVERT(VARCHAR(25),Purchase.EntryDate,112)<=@ToDate     
union all
select purexpno as DocumentNo,purexpno as InvoiceNo,suppcode as SupplierCode,suppname SupplierName,EXPDATE as EntryDate,rate as NetAmount,
([dbo].udf_SelectReceivedAmountOfSupplier(purexpno,@BranchCode)) as ReceivedAmount ,--+[dbo].udf_RetAmtForSupplierPayment(SupplierCode,DocumentNo)
([dbo].udf_SelectDiscountAmountOfSupplier(purexpno,@BranchCode)) as DiscountAmount ,
(isnull(RATE,0)-([dbo].udf_SelectReceivedAmountOfSupplier(purexpno,@BranchCode)+([dbo].udf_SelectDiscountAmountOfSupplier(purexpno,@BranchCode)) +[dbo].udf_RetAmtForSupplierPayment(suppcode,purexpno,@BranchCode)))as BalAmt ,
isnull([dbo].udf_RetAmtForSupplierPayment(suppcode,purexpno,@BranchCode),0) AS RetAmt,'E' as ttype ,isnull(([dbo].udf_SelectDiscountAmountOfSupplier(purexpno,@BranchCode)),0) as DiscountAmt from PurchaseExpenseDetails         
where  EXTYPE='Credit' 
and (RATE-([dbo].udf_SelectReceivedAmountOfSupplier(purexpno,@BranchCode)+([dbo].udf_SelectDiscountAmountOfSupplier(purexpno,@BranchCode)) +[dbo].udf_RetAmtForSupplierPayment(suppcode,purexpno,@BranchCode)))>0           
and CONVERT(VARCHAR(25),PurchaseExpenseDetails.EXPDATE,112)>= @FromDate and CONVERT(VARCHAR(25),PurchaseExpenseDetails.EXPDATE,112)<=@ToDate  
union all 
select  isnull(DocNo,'')'DocumentNo',isnull(PurchaseNo,'') as InvoiceNo,isnull(SupplierOPEntryUpdated.SupplierCode,'')as SupplierCode,isnull(SupplierOPEntryUpdated.SupplierName,'') COLLATE SQL_Latin1_General_CP1_CS_AS as SupplierName, SupplierOPEntryUpdated.PurchaseDate as Entrydate,
 (isnull(PurchaseAmt,0)) as NetAmount,isnull(ReceivedAmt,0) + isnull(([dbo].udf_SelectReceivedAmountOfSupplier(DocNo,@BranchCode)),0) as ReceivedAmount, isnull(SupplierOPEntryUpdated.Discount,0) as   DiscountAmount,
(isnull(Balance,0)-([dbo].udf_SelectReceivedAmountOfSupplier(DocNo,@BranchCode)+isnull(Discount,0) +[dbo].udf_RetAmtForSupplierPayment(SupplierCode,DocNo,@BranchCode)))as BalAmt,isnull(ReturnedAmt,0) + isnull([dbo].udf_RetAmtForSupplierPayment(SupplierCode,DocNo,@BranchCode),0) as  RetAmt,'E' as ttype ,isnull(SupplierOPEntryUpdated.Discount,0) as DiscountAmt
from SupplierOPEntryUpdated left join Supplier on Supplier.Code=SupplierOPEntryUpdated.SupplierCode where  
(isnull(Balance,0)-([dbo].udf_SelectReceivedAmountOfSupplier(DocNo,@BranchCode)+isnull(Discount,0) +[dbo].udf_RetAmtForSupplierPayment(SupplierCode,DocNo,@BranchCode)))>0  
and CONVERT(VARCHAR(25),SupplierOPEntryUpdated.EntryDate,112)>= @FromDate and CONVERT(VARCHAR(25),SupplierOPEntryUpdated.EntryDate,112)<=@ToDate   
 AND (@BranchCode IS NULL OR @BranchCode = '' OR @BranchCode = '1' OR ISNULL(SupplierOPEntryUpdated.BranchCode, '') = @BranchCode) 
ORDER BY ENTRYDATE--DocumentNo      
end      
if @Flag=2      
begin      
select DocumentNo,InvoiceNo,SupplierCode,SupplierName,EntryDate,NetAmount,
([dbo].udf_SelectReceivedAmountOfSupplier(DocumentNo,@BranchCode)) as ReceivedAmount ,--+[dbo].udf_RetAmtForSupplierPayment(SupplierCode,DocumentNo)
([dbo].udf_SelectDiscountAmountOfSupplier(DocumentNo,@BranchCode)) as DiscountAmount,
(NetAmount-([dbo].udf_SelectReceivedAmountOfSupplier(DocumentNo,@BranchCode)+([dbo].udf_SelectDiscountAmountOfSupplier(DocumentNo,@BranchCode))+[dbo].udf_RetAmtForSupplierPayment(SupplierCode,DocumentNo,@BranchCode)))as BalAmt  ,
isnull([dbo].udf_RetAmtForSupplierPayment(SupplierCode,DocumentNo,@BranchCode),0) AS RetAmt,'P' as ttype,isnull(([dbo].udf_SelectDiscountAmountOfSupplier(DocumentNo,@BranchCode)),0) as DiscountAmt from Purchase         
where  SupplierCode=@SupplierCode and Billtype='Credit' and PR='Purchase'   
and (NetAmount-([dbo].udf_SelectReceivedAmountOfSupplier(DocumentNo,@BranchCode)+([dbo].udf_SelectDiscountAmountOfSupplier(DocumentNo,@BranchCode))+[dbo].udf_RetAmtForSupplierPayment(SupplierCode,DocumentNo,@BranchCode)))>0            
and CONVERT(VARCHAR(25),Purchase.EntryDate,112)>= @FromDate and CONVERT(VARCHAR(25),Purchase.EntryDate,112)<=@ToDate         
union all 
select purexpno as DocumentNo,purexpno as InvoiceNo,suppcode as SupplierCode,suppname as SupplierName,expdate as EntryDate,rate as NetAmount,
([dbo].udf_SelectReceivedAmountOfSupplier(purexpno,@BranchCode)) as ReceivedAmount ,--+[dbo].udf_RetAmtForSupplierPayment(SupplierCode,DocumentNo)
([dbo].udf_SelectDiscountAmountOfSupplier(purexpno,@BranchCode)) as DiscountAmount,
(rate-([dbo].udf_SelectReceivedAmountOfSupplier(purexpno,@BranchCode)+([dbo].udf_SelectDiscountAmountOfSupplier(purexpno,@BranchCode))+[dbo].udf_RetAmtForSupplierPayment(suppcode,purexpno,@BranchCode)))as BalAmt  ,
isnull([dbo].udf_RetAmtForSupplierPayment(suppcode,purexpno,@BranchCode),0) AS RetAmt,'P' as ttype,isnull(([dbo].udf_SelectDiscountAmountOfSupplier(purexpno,@BranchCode)),0) as DiscountAmt from PurchaseExpenseDetails         
where  suppcode=@SupplierCode and EXTYPE='Credit'   
and (rate-([dbo].udf_SelectReceivedAmountOfSupplier(purexpno,@BranchCode)+([dbo].udf_SelectDiscountAmountOfSupplier(purexpno,@BranchCode))+[dbo].udf_RetAmtForSupplierPayment(suppcode,purexpno,@BranchCode)))>0            
and CONVERT(VARCHAR(25),PurchaseExpenseDetails.EXPDATE,112)>= @FromDate and CONVERT(VARCHAR(25),PurchaseExpenseDetails.EXPDATE,112)<=@ToDate  
union all
select  isnull(DocNo,'')'DocumentNo',isnull(PurchaseNo,'') as InvoiceNo,isnull(SupplierOPEntryUpdated.SupplierCode,'')as SupplierCode,isnull(SupplierOPEntryUpdated.SupplierName,'') COLLATE SQL_Latin1_General_CP1_CS_AS as SupplierName, SupplierOPEntryUpdated.PurchaseDate as Entrydate,
 (isnull(PurchaseAmt,0)) as NetAmount,isnull(ReceivedAmt,0) + isnull(([dbo].udf_SelectReceivedAmountOfSupplier(DocNo,@BranchCode)),0) as ReceivedAmount, isnull(SupplierOPEntryUpdated.Discount,0) as   DiscountAmount,
(isnull(Balance,0)-([dbo].udf_SelectReceivedAmountOfSupplier(DocNo,@BranchCode)+isnull(Discount,0) +[dbo].udf_RetAmtForSupplierPayment(SupplierCode,DocNo,@BranchCode)))as BalAmt,isnull(ReturnedAmt,0) + isnull([dbo].udf_RetAmtForSupplierPayment(SupplierCode,DocNo,@BranchCode),0) as  RetAmt,'E' as ttype ,isnull(SupplierOPEntryUpdated.Discount,0) as DiscountAmt
from SupplierOPEntryUpdated left join Supplier on Supplier.Code=SupplierOPEntryUpdated.SupplierCode where  
(isnull(Balance,0)-([dbo].udf_SelectReceivedAmountOfSupplier(DocNo,@BranchCode)+isnull(Discount,0) +[dbo].udf_RetAmtForSupplierPayment(SupplierCode,DocNo,@BranchCode)))>0  
and CONVERT(VARCHAR(25),SupplierOPEntryUpdated.EntryDate,112)>= @FromDate and CONVERT(VARCHAR(25),SupplierOPEntryUpdated.EntryDate,112)<=@ToDate   
 AND (@BranchCode IS NULL OR @BranchCode = '' OR @BranchCode = '1' OR ISNULL(SupplierOPEntryUpdated.BranchCode, '') = @BranchCode) 
ORDER BY ENTRYDATE--DocumentNo     
end          
end  


GO

/****** Object:  Table [dbo].[JobOrder]    Script Date: 26-12-2024 20:40:35 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[JobOrder](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[JobOrderNo] [nvarchar](50) NOT NULL,
	[EntryDate] [datetime] NOT NULL,
	[JobOrderDate] [datetime] NOT NULL,
	[PromisedDate] [datetime] NULL,
	[ChassisNo] [nvarchar](500) NULL,
	[PlateNo] [nvarchar](200) NULL,
	[VehicleCode] [nvarchar](500) NULL,
	[VehicleName] [nvarchar](500) NULL,
	[TypeCode] [nvarchar](500) NULL,
	[TypeName] [nvarchar](500) NULL,
	[EngineCode] [nvarchar](500) NULL,
	[EngineName] [nvarchar](500) NULL,
	[TransmissionCode] [nvarchar](500) NULL,
	[TransmissionName] [nvarchar](500) NULL,
	[Model] [nvarchar](500) NULL,
	[Colour] [nvarchar](500) NULL,
	[Kilometer] [nvarchar](500) NULL,
	[CustomerCode] [nvarchar](500) NULL,
	[CustomerName] [nvarchar](500) NULL,
	[DeliveredBy] [nvarchar](500) NULL,
	[ContactNo] [nvarchar](100) NULL,
	[TimeIn] [nvarchar](100) NULL,
	[TimeOut] [nvarchar](100) NULL,
	[GuaranteePeriod] [nvarchar](500) NULL,
	[BillType] [nvarchar](50) NULL,
	[Reference] [nvarchar](500) NULL,
	[Status] [nvarchar](50) NULL,
	[Salesman] [nvarchar](500) NULL,
	[VehicleInfo] [nvarchar](max) NULL,
	[Comments] [nvarchar](max) NULL,
	[SubTotal] [decimal](18, 2) NULL,
	[DiscPer] [decimal](5, 2) NULL,
	[DiscAmt] [decimal](18, 2) NULL,
	[TaxAmt] [decimal](18, 2) NULL,
	[NetAmt] [decimal](18, 2) NULL,
	[BranchName] [varchar](100) NULL,
	[BranchCode] [varchar](100) NULL,
	[Brand] [varchar](500) NULL,
	[Category] [varchar](500) NULL,
PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO



GO

/****** Object:  Table [dbo].[JobOrderDetails]    Script Date: 26-12-2024 20:41:01 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[JobOrderDetails](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[EntryDate] [datetime] NOT NULL,
	[JobOrderNo] [nvarchar](50) NOT NULL,
	[RepairCode] [nvarchar](500) NULL,
	[RepairName] [nvarchar](500) NULL,
	[TechnicianCode] [nvarchar](500) NULL,
	[TechnicianName] [nvarchar](500) NULL,
	[Qty] [int] NULL,
	[Rate] [decimal](18, 2) NULL,
	[Total] [decimal](18, 2) NULL,
	[VatPer] [decimal](5, 2) NULL,
	[VatAmt] [decimal](18, 2) NULL,
	[LineTotal] [decimal](18, 2) NULL,
	[BranchName] [varchar](100) NULL,
	[BranchCode] [varchar](100) NULL,
	[SubId] [int] NULL,
PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO


alter table permanentsettings add AutoCustCode varchar(50),AutoSuppCode varchar(50),AutoItemCode varchar(50);

  alter table CustomerOPEntryUpdated add PONo varchar(250);
    alter table CustomerOPEntryDetails add PONo varchar(250);


	SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SP_GetStockList]
    @StockListType NVARCHAR(255) = NULL,
    @Category NVARCHAR(255) = NULL,
    @Company NVARCHAR(255) = NULL,
	@CostRateval NVARCHAR(255) = NULL
AS
BEGIN
    -- Base query
    DECLARE @SQL NVARCHAR(MAX);
   SET @SQL = '
        SELECT GetStock.ItemCode, 
               Item.ItemName, 
               GetStock.SUMQTY AS Stock, 
               Category.Name, 
               CASE 
                   WHEN @CostRateval = ''Rate'' THEN Item.Rate
                   WHEN @CostRateval = ''Cost'' THEN Item.StandardCost
                   WHEN @CostRateval = ''VatRate'' THEN Item.VatRate
                   ELSE NULL
               END AS CostRatevalue,
			    @CostRateval AS CostRateval
        FROM GetStock
        LEFT JOIN Item ON Item.ItemCode = GetStock.ItemCode
        LEFT JOIN Category ON Category.Code = Item.Category
        WHERE ISNULL(Item.StkMode, ''Inventory'') = ''Inventory''';
    -- StockListType filter
    IF @StockListType = 1
    BEGIN
        SET @SQL += ' AND GetStock.SUMQTY < 0';  -- StockListType 1: qty < 0
    END
    ELSE IF @StockListType = 2
    BEGIN
        SET @SQL += ' AND GetStock.SUMQTY > 0';  -- StockListType 2: qty > 0
    END
    ELSE IF @StockListType = 3
    BEGIN
        SET @SQL += ' AND GetStock.SUMQTY = 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),@CostRateval NVARCHAR(255)',
        @Category, @Company,@CostRateval;
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SP_stockupdateDateWiseStockList]           
    @todate DATETIME,
	@BranchName varchar(100)
	
AS        
BEGIN       
    DECLARE @stockstartdate AS DATETIME;

    
    SET @stockstartdate = (SELECT ISNULL(stockdate, '01/01/1900') AS 'date' FROM stockstartdate);

    -- Insert the aggregated quantity and item code into GetStock table
    INSERT INTO GetStock (SUMQTY, ItemCode)
    SELECT SUM(qty) AS SUMQTY, ITEMCODE 
    FROM (
        -- Summing the quantities by ItemCode
        SELECT ISNULL(SUM(ISNULL(opstock, 0)), 0) AS qty, ITEMCODE 
        FROM ITEM 
        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'  
		 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(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.TOSTORE = @BranchName)
  --      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  
		   AND (@BranchName = 'All' OR branchname = @BranchName)
        GROUP BY ITEMCODE
    ) t 
    GROUP BY t.ITEMCODE;

END


UPDATE supplier
SET dbcr = CASE 
              WHEN dbcr = '1' THEN 'Credit'
              WHEN dbcr = '2' THEN 'Debit'
           END
WHERE dbcr IN ('1','2');

UPDATE Customer
SET dbcr = CASE 
              WHEN dbcr = '1' THEN 'Debit'
              WHEN dbcr = '2' THEN 'Credit'
           END
WHERE dbcr IN ('1','2');




alter table permanentsettings add Replacement varchar(50);

alter table permanentsettings add Billtype varchar(50);

GO
/****** Object:  StoredProcedure [dbo].[sp_MatTransferDetailsReport]    Script Date: 05-01-2025 12:15:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_MatTransferDetailsReport]
@FromDate datetime,
@ToDate varchar(50),
@BranchFrom varchar(50),
@BranchTo varchar(50) 
AS  
BEGIN 
select  MaterialTrans.voucherNo as InvNo,MaterialTrans.EntryDate,MaterialTrans.PONo,MaterialTrans.RefNo,MaterialTrans.fromstore as StoreName,MaterialTrans.tostore as StoreName1,MaterialTrans.Attended,MaterialTrans.NetAmount,
MaterialTrans.Remarks,MaterialTransDetails.SlNo,MaterialTransDetails.ItemName,MaterialTransDetails.Qty,MaterialTransDetails.Rate,MaterialTransDetails.Unit,MaterialTransDetails.Amount,MaterialTransDetails.ItemCode,
MaterialTransDetails.ItemName,MaterialTransDetails.Qty,MaterialTransDetails.Rate,
MaterialTransDetails.SalesRate
from  MaterialTrans inner join MaterialTransDetails on MaterialTrans.voucherNo=MaterialTransDetails.DocumentNo  
 where  MaterialTrans.EntryDate between @FromDate and @ToDate and (MaterialTrans.fromstore = @BranchFrom OR @BranchFrom IS NULL OR @BranchFrom = '' OR @BranchFrom='All')   and(MaterialTrans.tostore = @BranchTo OR @BranchTo IS NULL OR @BranchTo = '' OR @BranchTo='All')  
END  



GO
/****** Object:  StoredProcedure [dbo].[sp_MatTransferDetailsOutReport]    Script Date: 05-01-2025 12:15:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_MatTransferDetailsOutReport]
@FromDate datetime,
@ToDate varchar(50),
@BranchFrom varchar(50),
@BranchTo varchar(50) 
AS  
BEGIN 
select  MaterialTransOut.voucherNo as InvNo,MaterialTransOut.EntryDate,MaterialTransOut.PONo,MaterialTransOut.RefNo,MaterialTransOut.fromstore as StoreName,MaterialTransOut.tostore as StoreName1,MaterialTransOut.Attended,MaterialTransOut.NetAmount,
MaterialTransOut.Remarks,MaterialTransOutDetails.SlNo,MaterialTransOutDetails.ItemName,MaterialTransOutDetails.Qty,MaterialTransOutDetails.Rate,MaterialTransOutDetails.Unit,MaterialTransOutDetails.Amount,MaterialTransOutDetails.ItemCode,
MaterialTransOutDetails.ItemName,MaterialTransOutDetails.Qty,MaterialTransOutDetails.Rate

from  MaterialTransOut inner join MaterialTransOutDetails on MaterialTransOut.voucherNo=MaterialTransOutDetails.DocumentNo  
 where  MaterialTransOut.EntryDate between @FromDate and @ToDate and (MaterialTransOut.fromstore = @BranchFrom OR @BranchFrom IS NULL OR @BranchFrom = '' OR @BranchFrom='All')   and(MaterialTransOut.tostore = @BranchTo OR @BranchTo IS NULL OR @BranchTo = '' OR @BranchTo='All')  
END  


alter table permanentsettings add CashAccount varchar(250);

alter table permanentsettings add BankAccount varchar(250);

alter table permanentsettings add CashAccountCode varchar(250);

alter table permanentsettings add BankAccountCode varchar(250);


GO

/****** Object:  Table [dbo].[Repair]    Script Date: 09-01-2025 17:42:14 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Repair](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Code] [nvarchar](50) NOT NULL,
	[Name] [nvarchar](500) NOT NULL,
	[Remarks] [nvarchar](max) NULL,
PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO



GO

/****** Object:  Table [dbo].[RepairDetails]    Script Date: 09-01-2025 17:43:13 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[RepairDetails](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[EntryDate] [datetime] NOT NULL,
	[JobOrderNo] [nvarchar](50) NOT NULL,
	[RepairCode] [nvarchar](500) NULL,
	[RepairName] [nvarchar](500) NULL,
	[TechnicianCode] [nvarchar](500) NULL,
	[TechnicianName] [nvarchar](500) NULL,
	[Qty] [int] NULL,
	[Rate] [decimal](18, 2) NULL,
	[Total] [decimal](18, 2) NULL,
	[VatPer] [decimal](5, 2) NULL,
	[VatAmt] [decimal](18, 2) NULL,
	[LineTotal] [decimal](18, 2) NULL,
PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO



GO

/****** Object:  Table [dbo].[Technician]    Script Date: 09-01-2025 17:44:07 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Technician](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Code] [nvarchar](50) NOT NULL,
	[Name] [nvarchar](500) NOT NULL,
	[Position] [nvarchar](500) NULL,
	[Mobile] [nvarchar](150) NULL,
	[Remarks] [nvarchar](max) NULL,
PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO


GO

/****** Object:  Table [dbo].[VehicleType]    Script Date: 09-01-2025 17:44:33 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[VehicleType](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Code] [nvarchar](50) NOT NULL,
	[Name] [nvarchar](500) NOT NULL,
	[Remarks] [nvarchar](max) NULL,
PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

insert into setup(code,svalue) values('JobOrderNo','1000');





GO
/****** Object:  StoredProcedure [dbo].[SP_GetSupplierPaymentDetailEdit]    Script Date: 09-01-2025 16:22:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [dbo].[SP_GetSupplierPaymentDetailEdit]
    @id NVARCHAR(50)
AS
BEGIN
    SET NOCOUNT ON;

    -- Query to fetch payment details for purchases
    SELECT  
        PurchaseNo,
        SupplierPaymentDetails.ReceivedAmount,
        ISNULL(SupplierPaymentDetails.Narration, '') AS Narration,
        Purchase.NetAmount AS PurchaseAmount,
        ISNULL((SELECT SUM(Discount) 
                FROM SupplierPaymentDetails  
                WHERE SupplierPaymentDetails.PurchaseNo = Purchase.DocumentNo), 0) AS DiscountAmt,
        ISNULL((SELECT SUM(ReceivedAmount) 
                FROM SupplierPaymentDetails  
                WHERE SupplierPaymentDetails.PurchaseNo = Purchase.DocumentNo), 0) AS ReceivedAmt,
        FORMAT(SupplierPaymentDetails.PurchaseDate, 'dd-MM-yyyy') AS EntryDatefrmtd,
        CASE 
            WHEN (SELECT COUNT(*)   
                  FROM SupplierPaymentDetails AS CRD2   
                  WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo   
                  GROUP BY PurchaseNo) = 1  
            THEN (Purchase.NetAmount)
            ELSE (SELECT (Purchase.NetAmount -  
                          COALESCE(SUM(ReceivedAmount), 0) - 
                          COALESCE(SUM(Discount), 0)) 
                  FROM SupplierPaymentDetails AS CRD2 
                  WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo 
                    AND CRD2.VoucherNo != @id 
                  GROUP BY PurchaseNo) 
        END AS TotalReceivedAmount,
        CASE  
            WHEN EXISTS (SELECT 1  
                         FROM SupplierPaymentDetails AS CRD2  
                         WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo  
                           AND CRD2.EntryDate > SupplierPaymentDetails.EntryDate) 
            THEN 'True' 
            ELSE 'False' 
        END AS EntryAfterPaidDate,
        CASE 
            WHEN (SELECT COUNT(*) 
                  FROM SupplierPaymentDetails AS CRD2 
                  WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo 
                  GROUP BY PurchaseNo) = 1
            THEN '0'
            ELSE (SELECT COALESCE(SUM(ReceivedAmount), 0) 
                  FROM SupplierPaymentDetails AS CRD2 
                  WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo 
                    AND CRD2.VoucherNo != @id 
                  GROUP BY PurchaseNo) 
        END AS ReceivedWithoutInv,
        CASE 
            WHEN (SELECT COUNT(*) 
                  FROM SupplierPaymentDetails AS CRD2 
                  WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo 
                  GROUP BY PurchaseNo) = 1
            THEN '0'
            ELSE (SELECT COALESCE(SUM(Discount), 0) 
                  FROM SupplierPaymentDetails AS CRD2 
                  WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo 
                    AND CRD2.VoucherNo != @id 
                  GROUP BY PurchaseNo) 
        END AS DiscountWithoutInv,
        'P' AS Type
    FROM SupplierPaymentDetails 
    LEFT JOIN Purchase ON SupplierPaymentDetails.PurchaseNo = Purchase.DocumentNo 
    WHERE SupplierPaymentDetails.VoucherNo = @id AND Type = 'P'

    UNION ALL

    -- Query to fetch payment details for purchase expenses
    SELECT  
        PurchaseNo,
        ReceivedAmount,
        ISNULL(SupplierPaymentDetails.Narration, '') AS Narration,
        PurchaseExpenseDetails.RATE AS PurchaseAmount,
        ISNULL((SELECT SUM(Discount) 
                FROM SupplierPaymentDetails  
                WHERE SupplierPaymentDetails.PurchaseNo = PurchaseExpenseDetails.DOCNO), 0) AS DiscountAmt,
        ISNULL((SELECT SUM(ReceivedAmount) 
                FROM SupplierPaymentDetails  
                WHERE SupplierPaymentDetails.PurchaseNo = PurchaseExpenseDetails.DOCNO), 0) AS ReceivedAmt,
        FORMAT(SupplierPaymentDetails.PurchaseDate, 'dd-MM-yyyy') AS EntryDatefrmtd,
        CASE 
            WHEN (SELECT COUNT(*)   
                  FROM SupplierPaymentDetails AS CRD2   
                  WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo   
                  GROUP BY PurchaseNo) = 1  
            THEN (PurchaseExpenseDetails.RATE)
            ELSE (SELECT (PurchaseExpenseDetails.RATE -  
                          COALESCE(SUM(ReceivedAmount), 0) - 
                          COALESCE(SUM(Discount), 0)) 
                  FROM SupplierPaymentDetails AS CRD2 
                  WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo 
                    AND CRD2.VoucherNo != @id 
                  GROUP BY PurchaseNo) 
        END AS TotalReceivedAmount,
        CASE  
            WHEN EXISTS (SELECT 1  
                         FROM SupplierPaymentDetails AS CRD2  
                         WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo  
                           AND CRD2.EntryDate > SupplierPaymentDetails.EntryDate) 
            THEN 'True' 
            ELSE 'False' 
        END AS EntryAfterPaidDate,
        CASE 
            WHEN (SELECT COUNT(*) 
                  FROM SupplierPaymentDetails AS CRD2 
                  WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo 
                  GROUP BY PurchaseNo) = 1
            THEN '0'
            ELSE (SELECT COALESCE(SUM(ReceivedAmount), 0) 
                  FROM SupplierPaymentDetails AS CRD2 
                  WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo 
                    AND CRD2.VoucherNo != @id 
                  GROUP BY PurchaseNo) 
        END AS ReceivedWithoutInv,
        CASE 
            WHEN (SELECT COUNT(*) 
                  FROM SupplierPaymentDetails AS CRD2 
                  WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo 
                  GROUP BY PurchaseNo) = 1
            THEN '0'
            ELSE (SELECT COALESCE(SUM(Discount), 0) 
                  FROM SupplierPaymentDetails AS CRD2 
                  WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo 
                    AND CRD2.VoucherNo != @id 
                  GROUP BY PurchaseNo) 
        END AS DiscountWithoutInv,
        'E' AS Type
    FROM SupplierPaymentDetails 
    LEFT JOIN PurchaseExpenseDetails ON SupplierPaymentDetails.PurchaseNo = PurchaseExpenseDetails.purexpno 
    WHERE SupplierPaymentDetails.VoucherNo = @id AND Type = 'E'

    UNION ALL

    -- Query to fetch supplier opening balance payment details
    SELECT  
        SupplierOPEntryUpdated.PurchaseNo,
        SupplierPaymentDetails.ReceivedAmount,
        ISNULL(SupplierPaymentDetails.Narration, '') AS Narration,
        SupplierOPEntryUpdated.PurchaseAmt AS PurchaseAmount,
        ISNULL((SELECT SUM(Discount) 
                FROM SupplierPaymentDetails  
                WHERE SupplierPaymentDetails.PurchaseNo = SupplierOPEntryUpdated.PurchaseNo), 0) AS DiscountAmt,
        ISNULL((SELECT SUM(ReceivedAmount) 
                FROM SupplierPaymentDetails  
                WHERE SupplierPaymentDetails.PurchaseNo = SupplierOPEntryUpdated.PurchaseNo), 0) AS ReceivedAmt,
        FORMAT(SupplierPaymentDetails.PurchaseDate, 'dd-MM-yyyy') AS EntryDatefrmtd,
        CASE 
            WHEN (SELECT COUNT(*)   
                  FROM SupplierPaymentDetails AS CRD2   
                  WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo   
                  GROUP BY PurchaseNo) = 1  
            THEN (SupplierOPEntryUpdated.PurchaseAmt)
            ELSE (SELECT (SupplierOPEntryUpdated.PurchaseAmt -  
                          COALESCE(SUM(ReceivedAmt), 0) - 
                          COALESCE(SUM(Discount), 0)) 
                  FROM SupplierPaymentDetails AS CRD2 
                  WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo 
                    AND CRD2.VoucherNo != @id 
                  GROUP BY PurchaseNo) 
        END AS TotalReceivedAmount,
        CASE  
            WHEN EXISTS (SELECT 1  
                         FROM SupplierPaymentDetails AS CRD2  
                         WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo  
                           AND CRD2.EntryDate > SupplierPaymentDetails.EntryDate) 
            THEN 'True' 
            ELSE 'False' 
        END AS EntryAfterPaidDate,
        CASE 
            WHEN (SELECT COUNT(*) 
                  FROM SupplierPaymentDetails AS CRD2 
                  WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo 
                  GROUP BY PurchaseNo) = 1
            THEN '0'
            ELSE (SELECT COALESCE(SUM(ReceivedAmount), 0) 
                  FROM SupplierPaymentDetails AS CRD2 
                  WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo 
                    AND CRD2.VoucherNo != @id 
                  GROUP BY PurchaseNo) 
        END AS ReceivedWithoutInv,
        CASE 
            WHEN (SELECT COUNT(*) 
                  FROM SupplierPaymentDetails AS CRD2 
                  WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo 
                  GROUP BY PurchaseNo) = 1
            THEN '0'
            ELSE (SELECT COALESCE(SUM(Discount), 0) 
                  FROM SupplierPaymentDetails AS CRD2 
                  WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo 
                    AND CRD2.VoucherNo != @id 
                  GROUP BY PurchaseNo) 
        END AS DiscountWithoutInv,
        '' AS Type
    FROM SupplierPaymentDetails 
    LEFT JOIN SupplierOPEntryUpdated ON SupplierPaymentDetails.PurchaseNo = SupplierOPEntryUpdated.PurchaseNo 
    WHERE SupplierPaymentDetails.VoucherNo = @id AND Type IS NULL group by SupplierOPEntryUpdated.PurchaseNo,SupplierOPEntryUpdated.ReceivedAmt,SupplierPaymentDetails.Narration,SupplierOPEntryUpdated.PurchaseAmt,SupplierPaymentDetails.PurchaseDate,
SupplierPaymentDetails.PurchaseNo,SupplierPaymentDetails.EntryDate,SupplierPaymentDetails.ReceivedAmount;
END







GO
/****** Object:  StoredProcedure [dbo].[SP_GetPurchaseDetailsForSuppPayment]    Script Date: 09-01-2025 16:13:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [dbo].[SP_GetPurchaseDetailsForSuppPayment]
    @id NVARCHAR(50)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT 
        Purchase.DocumentNo AS PurchaseNo, 
        (Purchase.NetAmount 
         - COALESCE(PurchaseReturn.NetAmount, 0) 
         - ISNULL(SUM(SupplierPaymentDetails.ReceivedAmount), 0) 
         - ISNULL(SUM(SupplierPaymentDetails.Discount), 0)) AS BalanceAmount,
        ISNULL(SUM(SupplierPaymentDetails.Discount), 0) AS DiscountAmt,
        ISNULL(SUM(SupplierPaymentDetails.ReceivedAmount), 0) AS ReceivedAmt,
        Purchase.NetAmount AS PurchaseAmount, 
        FORMAT(Purchase.EntryDate, 'dd-MM-yyyy') AS EntryDatefrmtd,
        'P' AS type
    FROM Purchase
    LEFT JOIN SupplierPaymentDetails
        ON SupplierPaymentDetails.PurchaseNo = Purchase.DocumentNo AND type = 'P'
    LEFT JOIN PurchaseReturn
        ON PurchaseReturn.PurchaseNo = Purchase.DocumentNo AND PurchaseReturn.BillType = 'Credit'
    WHERE Purchase.BillType = 'Credit' AND Purchase.SupplierCode = @id
    GROUP BY Purchase.DocumentNo, Purchase.NetAmount, Purchase.EntryDate, PurchaseReturn.NetAmount, PurchaseReturn.BillType
    HAVING (Purchase.NetAmount 
            - COALESCE(PurchaseReturn.NetAmount, 0) 
            - ISNULL(SUM(SupplierPaymentDetails.ReceivedAmount), 0) 
            - ISNULL(SUM(SupplierPaymentDetails.Discount), 0)) > 0

    UNION ALL

    SELECT 
        PurchaseExpenseDetails.purexpno AS PurchaseNo, 
        (PurchaseExpenseDetails.RATE 
         - ISNULL(SUM(SupplierPaymentDetails.ReceivedAmount), 0) 
         - ISNULL(SUM(SupplierPaymentDetails.Discount), 0)) AS BalanceAmount,
        ISNULL(SUM(SupplierPaymentDetails.Discount), 0) AS DiscountAmt,
        ISNULL(SUM(SupplierPaymentDetails.ReceivedAmount), 0) AS ReceivedAmt,
        PurchaseExpenseDetails.RATE AS PurchaseAmount,  
        FORMAT(PurchaseExpenseDetails.EXPDATE, 'dd-MM-yyyy') AS EntryDatefrmtd,
        'E' AS type
    FROM PurchaseExpenseDetails
    LEFT JOIN SupplierPaymentDetails
        ON SupplierPaymentDetails.PurchaseNo = PurchaseExpenseDetails.purexpno AND type = 'E'

    WHERE PurchaseExpenseDetails.EXTYPE = 'Credit' AND PurchaseExpenseDetails.suppcode = @id
    GROUP BY PurchaseExpenseDetails.purexpno, PurchaseExpenseDetails.RATE, PurchaseExpenseDetails.EXPDATE, PurchaseExpenseDetails.EXTYPE
    HAVING (PurchaseExpenseDetails.RATE 
            - ISNULL(SUM(SupplierPaymentDetails.ReceivedAmount), 0)) > 0

    UNION ALL

    SELECT 
        SupplierOPEntryUpdated.PurchaseNo AS PurchaseNo, 
		
        (ISNULL(SupplierOPEntryUpdated.Balance, 0)-ISNULL(SUM(SupplierPaymentDetails.ReceivedAmount), 0) - ISNULL(SUM(SupplierPaymentDetails.Discount), 0)) AS BalanceAmount,
        ISNULL(SupplierOPEntryUpdated.Discount, 0) AS DiscountAmt,
        ISNULL(SupplierOPEntryUpdated.ReceivedAmt, 0) AS ReceivedAmt,
        SupplierOPEntryUpdated.PurchaseAmt AS PurchaseAmount, 
        FORMAT(SupplierOPEntryUpdated.EntryDate, 'dd-MM-yyyy') AS EntryDatefrmtd,
        '' AS type
    FROM SupplierOPEntryUpdated
	 LEFT JOIN SupplierPaymentDetails
        ON SupplierPaymentDetails.PurchaseNo = SupplierOPEntryUpdated.PurchaseNo 
    WHERE SupplierOPEntryUpdated.SupplierCode = @id
	group by
	SupplierOPEntryUpdated.PurchaseNo, 
        SupplierOPEntryUpdated.PurchaseAmt, 
        SupplierOPEntryUpdated.EntryDate,
        SupplierOPEntryUpdated.Balance,
		SupplierOPEntryUpdated.Discount,
		SupplierOPEntryUpdated.ReceivedAmt
    ORDER BY EntryDatefrmtd ASC;
END




GO
/****** Object:  StoredProcedure [dbo].[Sp_GetInvoiceDetailForReceipt]    Script Date: 09-01-2025 15:51:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [dbo].[Sp_GetInvoiceDetailForReceipt]
    @CustomerId NVARCHAR(50)
AS
BEGIN
    SET NOCOUNT ON;

    -- First SELECT statement
    SELECT 
        Invoice.InvNo AS InvoiceNo, 
        (Invoice.NetAmount - COALESCE(InvoiceReturn.NetAmount, 0) - ISNULL(SUM(CustomerReceiptDetail.ReceivedAmount), 0) - ISNULL(SUM(CustomerReceiptDetail.Discount), 0)) AS BalanceAmount,
        ISNULL(SUM(CustomerReceiptDetail.Discount), 0) AS DiscountAmt, 
        ISNULL(SUM(CustomerReceiptDetail.ReceivedAmount), 0) AS ReceivedAmt,
        Invoice.NetAmount AS InvoiceAmount, 
        FORMAT(Invoice.InvoiceDate, 'dd-MM-yyyy') AS EntryDatefrmtd
    FROM 
        Invoice
        LEFT JOIN CustomerReceiptDetail 
            ON CustomerReceiptDetail.InvoiceNo = Invoice.InvNo
        LEFT JOIN InvoiceReturn 
            ON InvoiceReturn.InvNo = Invoice.InvNo 
            AND InvoiceReturn.InvType = 'Credit'
    WHERE 
        Invoice.InvType = 'Credit' 
        AND Invoice.CustomerCode = @CustomerId
    GROUP BY 
        Invoice.InvNo, 
        Invoice.NetAmount, 
        Invoice.InvoiceDate, 
        InvoiceReturn.NetAmount,
        InvoiceReturn.InvType
    HAVING 
        (Invoice.NetAmount - COALESCE(InvoiceReturn.NetAmount, 0) - ISNULL(SUM(CustomerReceiptDetail.ReceivedAmount), 0) - ISNULL(SUM(CustomerReceiptDetail.Discount), 0)) > 0
    
    UNION ALL

    -- Second SELECT statement
    SELECT 
        CustomerOPEntryUpdated.InvoiceNo AS InvoiceNo,
        (Balance-ISNULL(SUM(CustomerReceiptDetail.ReceivedAmount), 0) - ISNULL(SUM(CustomerReceiptDetail.Discount), 0)) AS BalanceAmount,
        CustomerOPEntryUpdated.Discount AS DiscountAmt,
        ReceivedAmt AS ReceivedAmt,
        InvoiceAmt AS InvoiceAmount,
        FORMAT(InvoiceDate, 'dd-MM-yyyy') AS EntryDatefrmtd
    FROM 
        CustomerOPEntryUpdated
		LEFT JOIN CustomerReceiptDetail 
            ON CustomerReceiptDetail.InvoiceNo = CustomerOPEntryUpdated.InvoiceNo
    WHERE 
        CustomerCode = @CustomerId
		GROUP BY 
        CustomerOPEntryUpdated.InvoiceNo, 
        CustomerOPEntryUpdated.InvoiceAmt, 
        CustomerOPEntryUpdated.InvoiceDate,
        CustomerOPEntryUpdated.Balance,
		CustomerOPEntryUpdated.Discount,
		CustomerOPEntryUpdated.ReceivedAmt
      
    ORDER BY EntryDatefrmtd ASC;
END





GO
/****** Object:  StoredProcedure [dbo].[Sp_GetCustomerReceiptDetailEdit]    Script Date: 09-01-2025 15:37:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [dbo].[Sp_GetCustomerReceiptDetailEdit]
    '1018' NVARCHAR(50)
AS
BEGIN
    SET NOCOUNT ON;

    -- First SELECT statement
    SELECT  
        CustomerReceiptDetail.InvoiceNo,
        CustomerReceiptDetail.Discount,
        CustomerReceiptDetail.ReceivedAmount,
        ISNULL(CustomerReceiptDetail.Narration, '') AS Narration,
        Invoice.NetAmount AS InvoiceAmount,
        ISNULL((SELECT SUM(ReceivedAmount) FROM CustomerReceiptDetail WHERE CustomerReceiptDetail.InvoiceNo = Invoice.InvNo), 0) AS ReceivedAmt,
        FORMAT(InvDate, 'dd-MM-yyyy') AS EntryDatefrmtd,
        ISNULL((SELECT SUM(Discount) FROM CustomerReceiptDetail WHERE CustomerReceiptDetail.InvoiceNo = Invoice.InvNo), 0) AS DiscountAmt,
        CASE 
            WHEN (SELECT COUNT(*) FROM CustomerReceiptDetail AS CRD2 WHERE CRD2.InvoiceNo = CustomerReceiptDetail.InvoiceNo GROUP BY InvoiceNo) = 1
            THEN Invoice.NetAmount
            ELSE (SELECT (Invoice.NetAmount - COALESCE(SUM(ReceivedAmount), 0) - COALESCE(SUM(Discount), 0)) 
                  FROM CustomerReceiptDetail AS CRD2 
                  WHERE CRD2.InvoiceNo = CustomerReceiptDetail.InvoiceNo 
                    AND CRD2.VoucherNo != '1018' 
                  GROUP BY InvoiceNo)
        END AS TotalReceivedAmount,
        CASE 
            WHEN EXISTS (SELECT 1 FROM CustomerReceiptDetail AS CRD2 WHERE CRD2.InvoiceNo = CustomerReceiptDetail.InvoiceNo AND CRD2.PaidDate > CustomerReceiptDetail.PaidDate) 
            THEN 'True' 
            ELSE 'False' 
        END AS EntryAfterPaidDate,
        CASE 
            WHEN (SELECT COUNT(*) FROM CustomerReceiptDetail AS CRD2 WHERE CRD2.InvoiceNo = CustomerReceiptDetail.InvoiceNo GROUP BY InvoiceNo) = 1
            THEN 0
            ELSE (SELECT COALESCE(SUM(ReceivedAmount), 0) 
                  FROM CustomerReceiptDetail AS CRD2 
                  WHERE CRD2.InvoiceNo = CustomerReceiptDetail.InvoiceNo 
                    AND CRD2.VoucherNo != '1018' 
                  GROUP BY InvoiceNo)
        END AS ReceivedWithoutInv,
        CASE 
            WHEN (SELECT COUNT(*) FROM CustomerReceiptDetail AS CRD2 WHERE CRD2.InvoiceNo = CustomerReceiptDetail.InvoiceNo GROUP BY InvoiceNo) = 1
            THEN 0
            ELSE (SELECT COALESCE(SUM(Discount), 0) 
                  FROM CustomerReceiptDetail AS CRD2 
                  WHERE CRD2.InvoiceNo = CustomerReceiptDetail.InvoiceNo 
                    AND CRD2.VoucherNo != '1018' 
                  GROUP BY InvoiceNo)
        END AS DiscountWithoutInv
    FROM   
        CustomerReceiptDetail 
    LEFT JOIN  
        Invoice ON CustomerReceiptDetail.InvoiceNo = Invoice.InvNo 
    WHERE  
        CustomerReceiptDetail.VoucherNo = '1018' AND Invoice.NetAmount IS NOT NULL

    UNION ALL

    -- Second SELECT statement
    SELECT  
        CustomerReceiptDetail.InvoiceNo,
        CustomerReceiptDetail.Discount,
        CustomerReceiptDetail.ReceivedAmount,
        ISNULL(CustomerReceiptDetail.Narration, '') AS Narration,
        CustomerOPEntryUpdated.InvoiceAmt AS InvoiceAmount,
        ISNULL((SELECT SUM(ReceivedAmount) FROM CustomerReceiptDetail WHERE CustomerReceiptDetail.InvoiceNo = CustomerOPEntryUpdated.InvoiceNo), 0) AS ReceivedAmt,
        FORMAT(InvDate, 'dd-MM-yyyy') AS EntryDatefrmtd,
        ISNULL((SELECT SUM(Discount) FROM CustomerReceiptDetail WHERE CustomerReceiptDetail.InvoiceNo = CustomerOPEntryUpdated.InvoiceNo), 0) AS DiscountAmt,
        CASE 
            WHEN (SELECT COUNT(*) FROM CustomerReceiptDetail AS CRD2 WHERE CRD2.InvoiceNo = CustomerReceiptDetail.InvoiceNo GROUP BY InvoiceNo) = 1
            THEN CustomerOPEntryUpdated.InvoiceAmt
            ELSE (SELECT (CustomerOPEntryUpdated.InvoiceAmt - COALESCE(SUM(ReceivedAmount), 0) - COALESCE(SUM(Discount), 0)) 
                  FROM CustomerReceiptDetail AS CRD2 
                  WHERE CRD2.InvoiceNo = CustomerReceiptDetail.InvoiceNo 
                    AND CRD2.VoucherNo != '1018' 
                  GROUP BY InvoiceNo)
        END AS TotalReceivedAmount,
        CASE 
            WHEN EXISTS (SELECT 1 FROM CustomerReceiptDetail AS CRD2 WHERE CRD2.InvoiceNo = CustomerReceiptDetail.InvoiceNo AND CRD2.PaidDate > CustomerReceiptDetail.PaidDate) 
            THEN 'True' 
            ELSE 'False' 
        END AS EntryAfterPaidDate,
        CASE 
            WHEN (SELECT COUNT(*) FROM CustomerReceiptDetail AS CRD2 WHERE CRD2.InvoiceNo = CustomerReceiptDetail.InvoiceNo GROUP BY InvoiceNo) = 1
            THEN 0
            ELSE (SELECT COALESCE(SUM(ReceivedAmount), 0) 
                  FROM CustomerReceiptDetail AS CRD2 
                  WHERE CRD2.InvoiceNo = CustomerReceiptDetail.InvoiceNo 
                    AND CRD2.VoucherNo != '1018' 
                  GROUP BY InvoiceNo)
        END AS ReceivedWithoutInv,
        CASE 
            WHEN (SELECT COUNT(*) FROM CustomerReceiptDetail AS CRD2 WHERE CRD2.InvoiceNo = CustomerReceiptDetail.InvoiceNo GROUP BY InvoiceNo) = 1
            THEN 0
            ELSE (SELECT COALESCE(SUM(Discount), 0) 
                  FROM CustomerReceiptDetail AS CRD2 
                  WHERE CRD2.InvoiceNo = CustomerReceiptDetail.InvoiceNo 
                    AND CRD2.VoucherNo != '1018' 
                  GROUP BY InvoiceNo)
        END AS DiscountWithoutInv
    FROM   
        CustomerReceiptDetail 
    LEFT JOIN  
        CustomerOPEntryUpdated ON CustomerReceiptDetail.InvoiceNo = CustomerOPEntryUpdated.InvoiceNo 
    WHERE  
        CustomerReceiptDetail.VoucherNo = '1018' AND CustomerOPEntryUpdated.InvoiceAmt IS NOT NULL
END


insert into setup(code,svalue) values('MatTransReqNo',1000);

alter table MaterialTransDetails add qtyperunit varchar(250);



GO
/****** Object:  StoredProcedure [dbo].[sp_InsertItemHistory]    Script Date: 09-01-2025 17:22:17 ******/
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 
        dt.EntryDate, 
        dt.DNNo, 
        'Delivery', 
        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, 
        'Sales', 
        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,
		ISNULL(gs.SUMQTY, 0) 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, 
        'Returns', 
        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,
		ISNULL(gs.SUMQTY, 0) 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, 
        'Purchase', 
        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,
		ISNULL(gs.SUMQTY, 0) 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, 
        'Returns', 
        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,
		ISNULL(gs.SUMQTY, 0) 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, 
        'FinalProduct', 
        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,
		ISNULL(gs.SUMQTY, 0) 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, 
        'FinalProductItem', 
        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,
		ISNULL(gs.SUMQTY, 0) 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, 
        'Damage', 
        '' 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,
		ISNULL(gs.SUMQTY, 0) 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, 
        'Transfer In', 
        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,
		ISNULL(gs.SUMQTY, 0) 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, 
        'Transfer Out', 
        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, 
        'Adjustment', 
        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;

    -- Optional Return the inserted data, or if you need to select from ItemHistory
    SELECT EntryDate, RefNo, Type, Unit, DeliveryQty, IssueQty, IssueRetQty, PurQty, PurRetQty,TransOutQty,TransInQty, FinalQty, ProductionQty, DmgQty, AdjQty, qtyperunit,OPStock
    FROM ItemHistory
    ORDER BY EntryDate;
END;