GO
/****** Object:  StoredProcedure [dbo].[sp_SelectBalanceSheet]    Script Date: 06-04-2025 17:36:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[sp_SelectBalanceSheet]                 
(        
@FromDate datetime,                
@dtpDateAsOn datetime,
@Branchcode NVarchar(500)
--@AccountsStartDate  datetime               
)                  
as                  
begin                  
----SELECT C.Account, convert(varchar,C.Code) as Code,                
----([dbo].[udf_SelectAccountOpeningBalance](C.Code)+[dbo].[udf_SelectLedgerClosingBalance](Code,@FromDate,@dtpDateAsOn)) as Amount,C.Type FROM Accounts C                 
----left JOIN CBLedger S ON C.Code = S.AcCode                 
------where S.EntryDate between '2000-01-01 00:00:00.000'                  
------and @dtpDateAsOn         
----where   (C.Type='Asset' or C.Type='Liability')                
----GROUP BY C.Code, C.Account,C.Type order by C.Code 





--SELECT * FROM (
--SELECT C.Account, convert(varchar,C.Code) as Code, C.ACGROUP AS ACGROUP , '' as subgrp,
--(--[dbo].[udf_SelectAccountOpeningBalance](C.Code)+
----[dbo].[udf_SelectDateWiseOpeningBalance](C.Code,@FromDate,@AccountsStartDate)+ 
--[dbo].[udf_SelectDateWiseOpeningBalance](C.Code,@FromDate,@Branchcode)+ 
--[dbo].[udf_SelectLedgerClosingBalance](Code,@FromDate,@dtpDateAsOn,@Branchcode)) as Amount,C.Type,isnull(c.flag,'Acc') as Flag,ISNULL(C.DbCr,'') AS DbCr
--,@Branchcode AS BRANCHNAME
--FROM Accounts C                 
--left JOIN tblaccounting S ON C.Code = S.AcCode                 
--where   (C.Type='Asset' or C.Type='Liability')  and c.code<>'8888' AND C.Code<>'55001'     
--AND 
--([dbo].[udf_SelectDateWiseOpeningBalance](C.Code,@FromDate,@Branchcode)+ [dbo].[udf_SelectLedgerClosingBalance](Code,@FromDate,@dtpDateAsOn,@Branchcode))  <>0    
----AND  ISNULL(BRANCHNAME,'')=ISNULL(@Branchcode,ISNULL(BRANCHNAME,''))
--GROUP BY C.Code, C.Account,C.Type,isnull(c.flag,'Acc'),ISNULL(C.DbCr,''),C.ACGROUP 
--)A
--WHERE   ISNULL(BRANCHNAME,'')=ISNULL(@Branchcode,ISNULL(BRANCHNAME,''))
--order by A.Code  

DECLARE @CLSTOCKStock  FLOAT
DECLARE @OpeningStock  FLOAT
DECLARE @sales  FLOAT
DECLARE @Expenses  FLOAT
DECLARE @Purchases  FLOAT
DECLARE @Income FLOAT
DECLARE @PROFIT FLOAT

--clstock
select @OpeningStock= ISNULL([dbo].udf_SelectStockValue(DATEADD(dAY, -1, @FromDate),@Branchcode),0) 
--opstock
select @CLSTOCKStock=ISNULL([dbo].udf_GetTotalStockValue(@dtpDateAsOn),0) 
--sales
select @sales=-isnull(sum(isnull(Amount,0)),0)  from tblaccounting  where entryDate between @FromDate and @dtpDateAsOn and accode ='95100'  AND ISNULL(BRANCHNAME,'')=ISNULL(@Branchcode,ISNULL(BRANCHNAME,''))
--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 @FromDate and @dtpDateAsOn  and C.Type='Expense' AND C.CODE<>'95200'  AND ISNULL(S.BRANCHNAME,'')=ISNULL(@Branchcode,ISNULL(S.BRANCHNAME,''))
--purchase
select  @Purchases=isnull(sum(Amount),0) from tblaccounting  where entryDate between @FromDate and @dtpDateAsOn and AcCode='95200' AND ISNULL(BRANCHNAME,'')=ISNULL(@Branchcode,ISNULL(BRANCHNAME,''))
--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 @FromDate and @dtpDateAsOn  and C.Type='Income' and c.code<>'95100'  AND ISNULL(S.BRANCHNAME,'')=ISNULL(@Branchcode,ISNULL(S.BRANCHNAME,'')) 

SET @PROFIT=(ISNULL(@CLSTOCKStock,0)+ISNULL(@sales,0)+ISNULL(@Income,0))-(ISNULL(@OpeningStock,0)+ISNULL(@Purchases,0)+ISNULL(@Expenses,0))

END
BEGIN

    WITH AssetsCTE AS (
    SELECT 
        C.Account AS AssetAccount,
        convert(varchar, C.Code) AS AssetCode,
        C.ACGROUP AS AssetACGROUP,
        '' AS AssetSubGrp,
        -- Calculate the Amount for Assets
        (
           ISNULL([dbo].[udf_SelectDateWiseOpeningBalance](C.Code, @FromDate, @Branchcode),0) + 
            ISNULL([dbo].[udf_SelectLedgerClosingBalance](C.Code, @FromDate, @dtpDateAsOn, @Branchcode),0)
        ) AS AssetAmount,
        ROW_NUMBER() OVER (ORDER BY C.Code) AS RowNum
    FROM Accounts C
    LEFT JOIN tblaccounting S ON C.Code = S.AcCode
    WHERE C.Type = 'Asset'
    AND C.Code <> '8888'
    AND C.Code <> '55001'
    AND (
        ISNULL([dbo].[udf_SelectDateWiseOpeningBalance](C.Code, @FromDate, @Branchcode),0) + 
        ISNULL([dbo].[udf_SelectLedgerClosingBalance](C.Code, @FromDate, @dtpDateAsOn, @Branchcode),0)
    ) <> 0
    -- Remove group by as it’s unnecessary
	GROUP BY C.Code, C.Account,C.Type,isnull(c.flag,'Acc'),ISNULL(C.DbCr,''),C.ACGROUP 
),

LiabilitiesCTE AS (
    SELECT 
        C.Account AS LiabilityAccount,
        convert(varchar, C.Code) AS LiabilityCode,
        C.ACGROUP AS LiabilityACGROUP,
        '' AS LiabilitySubGrp,
        -- Calculate the Amount for Liabilities
        (
            ISNULL([dbo].[udf_SelectDateWiseOpeningBalance](C.Code, @FromDate, @Branchcode),0) + 
            ISNULL([dbo].[udf_SelectLedgerClosingBalance](C.Code, @FromDate, @dtpDateAsOn, @Branchcode),0)
        ) AS LiabilityAmount,
        ROW_NUMBER() OVER (ORDER BY C.Code) AS RowNum
    FROM Accounts C
    LEFT JOIN tblaccounting S ON C.Code = S.AcCode
    WHERE C.Type = 'Liability'
    AND C.Code <> '8888'
    AND C.Code <> '55001'
    AND (
        ISNULL([dbo].[udf_SelectDateWiseOpeningBalance](C.Code, @FromDate, @Branchcode),0) + 
        ISNULL([dbo].[udf_SelectLedgerClosingBalance](C.Code, @FromDate, @dtpDateAsOn, @Branchcode),0)
    ) <> 0
	GROUP BY C.Code, C.Account,C.Type,isnull(c.flag,'Acc'),ISNULL(C.DbCr,''),C.ACGROUP 
    -- Remove group by as it’s unnecessary
)

-- Full Outer Join to align Assets and Liabilities side by side
SELECT 

 isnull(A.AssetCode,'') as AssetCode, 
    isnull(A.AssetAccount,'') as AssetAccount, 
     isnull(A.AssetACGROUP,'') asAssetACGROUP, 
   isnull(A.AssetAmount,0) as AssetAmount,
      isnull(L.LiabilityCode,'') as LiabilityCode, 
    isnull(L.LiabilityAccount,'') as LiabilityAccount, 
	  isnull(L.LiabilityACGROUP,'') as LiabilityACGROUP, 
   isnull(L.LiabilityAmount,0) as LiabilityAmount
FROM 
    AssetsCTE A
FULL OUTER JOIN 
    LiabilitiesCTE L
ON 
    A.RowNum = L.RowNum
-- Optionally, filter by branch code
WHERE 
    ISNULL(@Branchcode, '') = ISNULL(@Branchcode, '')

	UNION ALL

	SELECT 
    'ClosingStock' AS AssetAccount, 
   'ClosingStock' AS  AssetCode, 
    'Asset' AS AssetACGROUP, 
    ISNULL(@CLSTOCKStock,0) as AssetAmount,
    'Profit And Loss' AS LiabilityAccount, 
    'Profit And Loss' AS LiabilityCode, 
    'Liability' AS LiabilityACGROUP, 
    -1 * ISNULL(@PROFIT,0) as LiabilityAmount
ORDER BY 

 AssetCode, 
    LiabilityCode;       

end