go
CREATE FUNCTION dbo.udf_GetCustomerOpeningBalance
(
    @CustomerCode NVARCHAR(50),
    @BranchCode NVARCHAR(50)
)
RETURNS DECIMAL(18,2)
AS
BEGIN
    DECLARE @TotalBalance DECIMAL(18,2);

    SELECT @TotalBalance = ISNULL(SUM(ISNULL(Balance, 0)), 0)
    FROM CustomerOPEntryUpdated
    WHERE CustomerCode = @CustomerCode
      AND (@BranchCode IS NULL OR @BranchCode = '' OR @BranchCode = '1' 
           OR ISNULL(BranchCode, '') = @BranchCode);

    RETURN @TotalBalance;
END;


go

CREATE FUNCTION dbo.udf_GetSupplierOpeningBalance
(
    @SupplierCode NVARCHAR(50),
    @BranchCode NVARCHAR(50)
)
RETURNS DECIMAL(18,2)
AS
BEGIN
    DECLARE @TotalBalance DECIMAL(18,2);

    SELECT @TotalBalance = -1 * ISNULL(SUM(ISNULL(Balance, 0)), 0)
    FROM SupplierOPEntryUpdated
    WHERE SupplierCode = @SupplierCode
      AND (@BranchCode IS NULL OR @BranchCode = '' OR @BranchCode = '1' 
           OR ISNULL(BranchCode, '') = @BranchCode);

    RETURN @TotalBalance;
END;


go

UPDATE MENUS SET ACCESS=1 WHERE FORM='Combined Ledger'


go

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [dbo].[SP_CombinedLedger]
@CustCode NVARCHAR(500),
@SupCode NVARCHAR(500),
@BranchCode NVARCHAR(500),
@FROM DATETIME,
@TO DATETIME
AS
BEGIN
select 1 AS GP,sum(opening) as opening,voucherno,amount,type,narration,entrydate,@FROM as fromdate,@TO as todate,@CustCode as CustomerCode ,@SupCode as suppliercode,
(select name from supplier where code=@supcode) as Supname,(select name from Customer where code=@Custcode) as CustName
from (
SELECT   ((CASE WHEN DbCr='Debit' THEN ISNULL(Opening,0) WHEN DbCr='credit' THEN (-1)*ISNULL(Opening,0) END)
+isnull(dbo.udf_GenOpening(@FROM,Customer.Code,@BranchCode),0)  + dbo.udf_GetCustomerOpeningBalance(Customer.Code, @BranchCode))
AS opening
,'' AS VoucherNo,0 AS Amount,'OPENING' AS Type,'' AS Narration,'01/01/2000' AS ENTRYDATE 
--,'' AS PONO
FROM dbo.customer
 WHERE  ISNULL(dbo.Customer.Code,'')=@CustCode
 
 UNION ALL
SELECT  0 AS opening
,dbo.TBLACCOUNTING.VoucherNo,Amount,dbo.TBLACCOUNTING.Type,dbo.TBLACCOUNTING.Narration,TBLACCOUNTING.ENTRYDATE
--,(CASE WHEN TYPE='Sales' then (SELECT ISNULL(PONO,'') FROM INVOICE WHERE INVNO=TBLACCOUNTING.VOUCHERNO) else '' end)  AS PONO
FROM dbo.CUSTOMER
LEFT  JOIN TBLACCOUNTING ON dbo.Customer.Code=dbo.TBLACCOUNTING.AcCode 
 WHERE  ISNULL(dbo.Customer.Code,'')=@CustCode
 
and TBLACCOUNTING.EntryDate between @FROM and @TO AND (@BranchCode IS NULL OR @BranchCode = '' OR ISNULL(TBLACCOUNTING.BranchCode, '') = @BranchCode) 
union all
SELECT   ((CASE WHEN DbCr='Debit' THEN ISNULL(Opening,0) WHEN DbCr='credit' THEN (-1)*ISNULL(Opening,0) END)
+isnull(dbo.udf_GenOpening(@FROM,Supplier.Code,@BranchCode),0) + dbo.udf_GetSupplierOpeningBalance(Supplier.Code, @BranchCode)) AS opening
,'' AS VoucherNo,0 AS Amount,'OPENING' AS Type,'' AS Narration,'01/01/2000' AS ENTRYDATE
  -- ,'' AS SVNO 
FROM dbo.Supplier
 WHERE ISNULL(dbo.Supplier.Code,0)=@SupCode

 UNION ALL
SELECT 0 AS opening
,dbo.TBLACCOUNTING.VoucherNo,Amount,dbo.TBLACCOUNTING.Type,dbo.TBLACCOUNTING.Narration,TBLACCOUNTING.ENTRYDATE 
  -- ,ISNULL((SELECT ISNULL(I.rVNO,'') AS SVNO FROM SupplierPayment I WHERE I.VOUCHERNO=tblaccounting.VOUCHERNO AND tblaccounting.TYPE='SupplierPayment'),'') AS SVNO 
FROM dbo.Supplier
LEFT  JOIN TBLACCOUNTING ON dbo.Supplier.Code=dbo.TBLACCOUNTING.AcCode 
 WHERE  ISNULL(dbo.Supplier.Code,0)=@SupCode
 
and TBLACCOUNTING.EntryDate between @FROM and @TO AND (@BranchCode IS NULL OR @BranchCode = '' OR ISNULL(TBLACCOUNTING.BranchCode, '') = @BranchCode) 
)a group by voucherno,amount,type,narration,entrydate
END;