GO
/****** Object:  StoredProcedure [dbo].[Sp_GetCustomerReceiptDetailEdit]    Script Date: 13-01-2025 20:11:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Sp_GetCustomerReceiptDetailEdit]
    @VoucherNo 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 != @VoucherNo 
                  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 != @VoucherNo 
                  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 != @VoucherNo 
                  GROUP BY InvoiceNo)
        END AS DiscountWithoutInv
    FROM   
        CustomerReceiptDetail 
    LEFT JOIN  
        Invoice ON CustomerReceiptDetail.InvoiceNo = Invoice.InvNo 
    WHERE  
        CustomerReceiptDetail.VoucherNo = @VoucherNo 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([dbo].[udf_SelectReceivedAmountOfCustomerforinvoice](CustomerReceiptDetail.invoiceno,customerreceipt.customercode),0)AS ReceivedAmt,
	   ISNULL((SELECT SUM(ReceivedAmount) FROM CustomerReceiptDetail WHERE CustomerReceiptDetail.InvoiceNo = CustomerOPEntryUpdated.InvoiceNo and CustomerOPEntryUpdated.CustomerCode=CustomerReceipt.CustomerCode), 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   CustomerOPEntryUpdated.CustomerCode=CustomerReceipt.CustomerCode
        --            AND CRD2.VoucherNo != @VoucherNo 
        --          GROUP BY InvoiceNo)
        --END AS TotalReceivedAmount,
		 isnull([dbo].[udf_GetTotalReceivedAmountforcustrecpt](CustomerReceiptDetail.invoiceno,customerreceipt.customercode,customerreceipt.voucherno,CustomerOPEntryUpdated.InvoiceAmt),0)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 != @VoucherNo 
        --          GROUP BY InvoiceNo)
        --END AS ReceivedWithoutInv,
		  isnull([dbo].[udf_SelectReceivedAmountOfCustomerforinvoice](CustomerReceiptDetail.invoiceno,customerreceipt.customercode,customerreceipt.voucherno),0)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 != @VoucherNo 
        --          GROUP BY InvoiceNo)
        --END AS DiscountWithoutInv
		  isnull([dbo].[udf_SelectDiscountAmountOfCustomerforinvoice](CustomerReceiptDetail.invoiceno,customerreceipt.customercode,customerreceipt.voucherno),0)AS DiscountWithoutInv
    FROM   
        CustomerReceiptDetail 
		inner join CustomerReceipt  on CustomerReceipt.voucherno=CustomerReceiptDetail.VoucherNo
    LEFT JOIN  
        CustomerOPEntryUpdated ON CustomerReceiptDetail.InvoiceNo = CustomerOPEntryUpdated.InvoiceNo  and CustomerOPEntryUpdated.CustomerCode=CustomerReceipt.CustomerCode
    WHERE  
        CustomerReceiptDetail.VoucherNo = @VoucherNo AND CustomerOPEntryUpdated.InvoiceAmt IS NOT NULL
END








GO
/****** Object:  StoredProcedure [dbo].[Sp_GetInvoiceDetailForReceipt]    Script Date: 13-01-2025 20:11:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER 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([dbo].[udf_GetReceivedAmountCustRecpt](CustomerReceiptDetail.invoiceno,@CustomerId), 0) - ISNULL([dbo].[udf_GetDiscountAmountCustRecpt](CustomerReceiptDetail.invoiceno,@CustomerId), 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  
			LEFT JOIN 
        CustomerReceipt 
        ON CustomerReceiptDetail.voucherno = CustomerReceipt.voucherno    AND (CustomerReceipt.CustomerCode = @CustomerId OR CustomerReceipt.CustomerCode IS NULL)
    WHERE 
        CustomerOPEntryUpdated.CustomerCode = @CustomerId
		-- AND CustomerReceipt.CustomerCode = @CustomerId
		
		GROUP BY 
        CustomerOPEntryUpdated.InvoiceNo, 
        CustomerOPEntryUpdated.InvoiceAmt, 
        CustomerOPEntryUpdated.InvoiceDate,
        CustomerOPEntryUpdated.Balance,
		CustomerOPEntryUpdated.Discount,
		CustomerOPEntryUpdated.ReceivedAmt,
		CustomerReceiptDetail.invoiceno
      
    ORDER BY EntryDatefrmtd ASC;
END
  







GO
/****** Object:  UserDefinedFunction [dbo].[udf_GetReceivedAmountCustRecpt]    Script Date: 13-01-2025 20:13:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create FUNCTION [dbo].[udf_GetReceivedAmountCustRecpt] (
    @InvoiceNo NVARCHAR(50),   -- Invoice number for which the received amount is needed
    @CustomerCode NVARCHAR(50) -- Customer code to filter the data
)
RETURNS DECIMAL(18, 2) -- Returns the total received amount as a decimal value
AS
BEGIN
    DECLARE @TotalReceivedAmount DECIMAL(18, 2);

    SELECT 
        @TotalReceivedAmount = ISNULL(SUM(ReceivedAmount), 0)
    FROM 
        CustomerReceiptDetail
    INNER JOIN 
        CustomerReceipt 
        ON CustomerReceiptDetail.voucherno = CustomerReceipt.voucherno   
    INNER JOIN 
        CustomerOPEntryUpdated 
        ON CustomerReceiptDetail.InvoiceNo = CustomerOPEntryUpdated.InvoiceNo   
    WHERE 
        CustomerReceiptDetail.InvoiceNo = @InvoiceNo
        AND CustomerOPEntryUpdated.CustomerCode = @CustomerCode
        AND CustomerReceipt.CustomerCode = @CustomerCode;

    RETURN @TotalReceivedAmount;
END;




GO
/****** Object:  UserDefinedFunction [dbo].[udf_GetDiscountAmountCustRecpt]    Script Date: 13-01-2025 20:13:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create FUNCTION [dbo].[udf_GetDiscountAmountCustRecpt] (
    @InvoiceNo NVARCHAR(50),   -- Invoice number for which the received amount is needed
    @CustomerCode NVARCHAR(50) -- Customer code to filter the data
)
RETURNS DECIMAL(18, 2) -- Returns the total received amount as a decimal value
AS
BEGIN
    DECLARE @TotalReceivedAmount DECIMAL(18, 2);

    SELECT 
        @TotalReceivedAmount = ISNULL(SUM(CustomerReceiptDetail.Discount), 0)
    FROM 
        CustomerReceiptDetail
    INNER JOIN 
        CustomerReceipt 
        ON CustomerReceiptDetail.voucherno = CustomerReceipt.voucherno   
    INNER JOIN 
        CustomerOPEntryUpdated 
        ON CustomerReceiptDetail.InvoiceNo = CustomerOPEntryUpdated.InvoiceNo   
    WHERE 
        CustomerReceiptDetail.InvoiceNo = @InvoiceNo
        AND CustomerOPEntryUpdated.CustomerCode = @CustomerCode
        AND CustomerReceipt.CustomerCode = @CustomerCode;

    RETURN @TotalReceivedAmount;
END;





GO
/****** Object:  UserDefinedFunction [dbo].[udf_GetTotalReceivedAmountforcustrecpt]    Script Date: 13-01-2025 20:15:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create FUNCTION [dbo].[udf_GetTotalReceivedAmountforcustrecpt]
(
    @InvNo NVARCHAR(500),
    @custcode NVARCHAR(500),
    @voucherno NVARCHAR(500),
    @InvoiceAmt FLOAT
)
RETURNS FLOAT
AS
BEGIN
    DECLARE @TotalReceivedAmount FLOAT;

   IF (
        SELECT COUNT(*)
        FROM CustomerReceiptDetail AS CRD2
        INNER JOIN CustomerReceipt AS CR ON CR.VoucherNo = CRD2.VoucherNo
        WHERE CRD2.InvoiceNo = @InvNo
          AND CR.CustomerCode = @custcode
        GROUP BY CRD2.InvoiceNo
    ) = 1
    BEGIN
        -- If there is only one receipt for the invoice
        SET @TotalReceivedAmount = @InvoiceAmt;
    END
    ELSE
    BEGIN
        -- Calculate the remaining amount for multiple receipts
        SELECT 
            @TotalReceivedAmount = @InvoiceAmt 
                                    - COALESCE(SUM(CRD2.ReceivedAmount), 0) 
                                    - COALESCE(SUM(CRD2.Discount), 0)
        FROM 
            CustomerReceiptDetail AS CRD2
        INNER JOIN 
            CustomerReceipt AS CR ON CR.VoucherNo = CRD2.VoucherNo
        WHERE 
            CRD2.InvoiceNo = @InvNo
            AND CR.CustomerCode = @custcode
            AND CRD2.VoucherNo <> @voucherno
        GROUP BY 
            CRD2.InvoiceNo;
    END

    RETURN ISNULL(@TotalReceivedAmount, 0);
END



GO
/****** Object:  UserDefinedFunction [dbo].[udf_SelectReceivedAmountOfCustomerforinvoice]    Script Date: 13-01-2025 20:16:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create FUNCTION [dbo].[udf_SelectReceivedAmountOfCustomerforinvoice] ( @InvNo nvarchar(500),@custcode nvarchar(500),@voucherno nvarchar(500))
RETURNS float
AS
BEGIN

RETURN (select isnull(sum(ReceivedAmount),0) as ReceivedAmount FROM CustomerReceiptDetail inner join CustomerReceipt 
 on CustomerReceipt.voucherno=CustomerReceiptDetail.VoucherNo 
where CustomerReceiptDetail.InvoiceNo=@InvNo  and CustomerReceipt.CustomerCode =@custcode and CustomerReceipt.voucherno<>@voucherno )

END



GO
/****** Object:  UserDefinedFunction [dbo].[udf_SelectDiscountAmountOfCustomerforinvoice]    Script Date: 13-01-2025 20:17:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create FUNCTION [dbo].[udf_SelectDiscountAmountOfCustomerforinvoice] ( @InvNo nvarchar(500),@custcode nvarchar(500),@voucherno nvarchar(500))
RETURNS float
AS
BEGIN

RETURN (select isnull(sum(Discount),0) as Discount FROM CustomerReceiptDetail inner join CustomerReceipt 
 on CustomerReceipt.voucherno=CustomerReceiptDetail.VoucherNo 
where CustomerReceiptDetail.InvoiceNo=@InvNo  and CustomerReceipt.CustomerCode =@custcode and CustomerReceipt.voucherno<>@voucherno )

END




GO
/****** Object:  StoredProcedure [dbo].[SP_GetPurchaseDetailsForSuppPayment]    Script Date: 13-01-2025 20:19:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER 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([dbo].[udf_GetReceivedAmountSuppPaymnt](SupplierPaymentDetails.PurchaseNo,@id), 0) - ISNULL([dbo].[udf_GetDiscountAmountSuppPaymnt](SupplierPaymentDetails.PurchaseNo,@id), 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 
		left JOIN 
        SupplierPayment 
        ON SupplierPaymentDetails.voucherno = SupplierPayment.voucherno   AND (SupplierPayment.SupplierCode = @id OR SupplierPayment.SupplierCode IS NULL)
    WHERE SupplierOPEntryUpdated.SupplierCode = @id  
	group by
	SupplierOPEntryUpdated.PurchaseNo, 
        SupplierOPEntryUpdated.PurchaseAmt, 
        SupplierOPEntryUpdated.EntryDate,
        SupplierOPEntryUpdated.Balance,
		SupplierOPEntryUpdated.Discount,
		SupplierOPEntryUpdated.ReceivedAmt,
		SupplierPaymentDetails.PurchaseNo
    ORDER BY EntryDatefrmtd ASC;
END




GO
/****** Object:  UserDefinedFunction [dbo].[udf_GetReceivedAmountSuppPaymnt]    Script Date: 13-01-2025 20:19:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create FUNCTION [dbo].[udf_GetReceivedAmountSuppPaymnt] (
    @PurchaseNo NVARCHAR(50),   -- Invoice number for which the received amount is needed
    @SupplierCode NVARCHAR(50) -- Customer code to filter the data
)
RETURNS DECIMAL(18, 2) -- Returns the total received amount as a decimal value
AS
BEGIN
    DECLARE @TotalReceivedAmount DECIMAL(18, 2);

    SELECT 
        @TotalReceivedAmount = ISNULL(SUM(ReceivedAmount), 0)
    FROM 
        SupplierPaymentDetails
    INNER JOIN 
        SupplierPayment 
        ON SupplierPaymentDetails.voucherno = SupplierPayment.voucherno   
    INNER JOIN 
        SupplierOPEntryUpdated 
        ON SupplierPaymentDetails.PurchaseNo = SupplierOPEntryUpdated.PurchaseNo   
    WHERE 
        SupplierPaymentDetails.PurchaseNo = @PurchaseNo
        AND SupplierOPEntryUpdated.SupplierCode = @SupplierCode
        AND SupplierPayment.SupplierCode = @SupplierCode;

    RETURN @TotalReceivedAmount;
END;



GO
/****** Object:  UserDefinedFunction [dbo].[udf_GetDiscountAmountSuppPaymnt]    Script Date: 13-01-2025 20:20:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create FUNCTION [dbo].[udf_GetDiscountAmountSuppPaymnt] (
    @PurchaseNo NVARCHAR(50),   -- Invoice number for which the received amount is needed
    @SupplierCode NVARCHAR(50) -- Customer code to filter the data
)
RETURNS DECIMAL(18, 2) -- Returns the total received amount as a decimal value
AS
BEGIN
    DECLARE @TotalReceivedAmount DECIMAL(18, 2);

    SELECT 
        @TotalReceivedAmount = ISNULL(SUM(SupplierPaymentDetails.Discount), 0)
    FROM 
        SupplierPaymentDetails
    INNER JOIN 
        SupplierPayment 
        ON SupplierPaymentDetails.voucherno = SupplierPayment.voucherno   
    INNER JOIN 
        SupplierOPEntryUpdated 
        ON SupplierPaymentDetails.PurchaseNo = SupplierOPEntryUpdated.PurchaseNo   
    WHERE 
        SupplierPaymentDetails.PurchaseNo = @PurchaseNo
        AND SupplierOPEntryUpdated.SupplierCode = @SupplierCode
        AND SupplierPayment.SupplierCode = @SupplierCode;

    RETURN @TotalReceivedAmount;
END;




GO
/****** Object:  StoredProcedure [dbo].[SP_GetSupplierPaymentDetailEdit]    Script Date: 13-01-2025 20:21:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER 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  
        SupplierPaymentDetails.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([dbo].[udf_SelectReceivedAmountOfCustomerforinvoice](SupplierPaymentDetails.invoiceno,customerreceipt.customercode),0)AS ReceivedAmt,
	   ISNULL((SELECT SUM(ReceivedAmount) FROM SupplierPaymentDetails WHERE SupplierPaymentDetails.PurchaseNo = SupplierOPEntryUpdated.PurchaseNo and SupplierOPEntryUpdated.SupplierCode=SupplierPayment.SupplierCode), 0) AS ReceivedAmt,
        
		FORMAT(SupplierPaymentDetails.EntryDate, 'dd-MM-yyyy') AS EntryDatefrmtd,
       
        --CASE 
        --    WHEN (SELECT COUNT(*) FROM SupplierPaymentDetails AS CRD2 WHERE CRD2.InvoiceNo = SupplierPaymentDetails.InvoiceNo GROUP BY InvoiceNo) = 1
        --    THEN SupplierOPEntryUpdated.InvoiceAmt
        --    ELSE (SELECT (SupplierOPEntryUpdated.InvoiceAmt - COALESCE(SUM(ReceivedAmount), 0) - COALESCE(SUM(Discount), 0)) 
        --          FROM SupplierPaymentDetails AS CRD2 
        --          WHERE CRD2.InvoiceNo = SupplierPaymentDetails.InvoiceNo  and   SupplierOPEntryUpdated.CustomerCode=CustomerReceipt.CustomerCode
        --            AND CRD2.VoucherNo != @VoucherNo 
        --          GROUP BY InvoiceNo)
        --END AS TotalReceivedAmount,
		 isnull([dbo].[udf_GetTotalReceivedAmountforSupppayment](SupplierPaymentDetails.purchaseno,supplierpayment.suppliercode,supplierpayment.voucherno,SupplierOPEntryUpdated.PurchaseAmt),0)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.InvoiceNo = SupplierPaymentDetails.InvoiceNo GROUP BY InvoiceNo) = 1
        --    THEN 0
        --    ELSE (SELECT COALESCE(SUM(ReceivedAmount), 0) 
        --          FROM SupplierPaymentDetails AS CRD2 
        --          WHERE CRD2.InvoiceNo = SupplierPaymentDetails.InvoiceNo 
        --            AND CRD2.VoucherNo != @VoucherNo 
        --          GROUP BY InvoiceNo)
        --END AS ReceivedWithoutInv,
		  isnull([dbo].[udf_SelectReceivedAmountOfSupplierforPurchase](SupplierPaymentDetails.purchaseno,supplierpayment.suppliercode,supplierpayment.voucherno),0)AS ReceivedWithoutInv,
        --CASE 
        --    WHEN (SELECT COUNT(*) FROM SupplierPaymentDetails AS CRD2 WHERE CRD2.InvoiceNo = SupplierPaymentDetails.InvoiceNo GROUP BY InvoiceNo) = 1
        --    THEN 0
        --    ELSE (SELECT COALESCE(SUM(Discount), 0) 
        --          FROM SupplierPaymentDetails AS CRD2 
        --          WHERE CRD2.InvoiceNo = SupplierPaymentDetails.InvoiceNo 
        --            AND CRD2.VoucherNo != @VoucherNo 
        --          GROUP BY InvoiceNo)
        --END AS DiscountWithoutInv
		 isnull([dbo].[udf_SelectDiscountAmountOfSupplierforPurchase](SupplierPaymentDetails.purchaseno,supplierpayment.suppliercode,supplierpayment.voucherno),0)AS DiscountWithoutInv,'' as Type 
    FROM   
        SupplierPaymentDetails 
		inner join SupplierPayment  on SupplierPayment.voucherno=SupplierPaymentDetails.VoucherNo
    LEFT JOIN  
        SupplierOPEntryUpdated ON SupplierPaymentDetails.PurchaseNo = SupplierOPEntryUpdated.PurchaseNo  and SupplierOPEntryUpdated.SupplierCode=SupplierPayment.SupplierCode
    WHERE  
        SupplierPaymentDetails.VoucherNo = @id AND SupplierOPEntryUpdated.PurchaseAmt IS NOT NULL
END




GO
/****** Object:  UserDefinedFunction [dbo].[udf_GetTotalReceivedAmountforSupppayment]    Script Date: 13-01-2025 20:22:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create FUNCTION [dbo].[udf_GetTotalReceivedAmountforSupppayment]
(
    @PurchaseNo NVARCHAR(500),
    @suppcode NVARCHAR(500),
    @voucherno NVARCHAR(500),
    @PurchaseAmt FLOAT
)
RETURNS FLOAT
AS
BEGIN
    DECLARE @TotalReceivedAmount FLOAT;

   IF (
        SELECT COUNT(*)
        FROM SupplierPaymentDetails AS CRD2
        INNER JOIN SupplierPayment AS CR ON CR.VoucherNo = CRD2.VoucherNo
        WHERE CRD2.PurchaseNo = @PurchaseNo
          AND CR.SupplierCode = @suppcode
        GROUP BY CRD2.PurchaseNo
    ) = 1
    BEGIN
        -- If there is only one receipt for the invoice
        SET @TotalReceivedAmount = @PurchaseAmt;
    END
    ELSE
    BEGIN
        -- Calculate the remaining amount for multiple receipts
        SELECT 
            @TotalReceivedAmount = @PurchaseAmt 
                                    - COALESCE(SUM(CRD2.ReceivedAmount), 0) 
                                    - COALESCE(SUM(CRD2.Discount), 0)
        FROM 
            SupplierPaymentDetails AS CRD2
        INNER JOIN 
            SupplierPayment AS CR ON CR.VoucherNo = CRD2.VoucherNo
        WHERE 
            CRD2.PurchaseNo = @PurchaseNo
            AND CR.SupplierCode = @suppcode
            AND CRD2.VoucherNo <> @voucherno
        GROUP BY 
            CRD2.PurchaseNo;
    END

    RETURN ISNULL(@TotalReceivedAmount, 0);
END



GO
/****** Object:  UserDefinedFunction [dbo].[udf_SelectReceivedAmountOfSupplierforPurchase]    Script Date: 13-01-2025 20:23:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create FUNCTION [dbo].[udf_SelectReceivedAmountOfSupplierforPurchase] ( @purchaseNo nvarchar(500),@suppcode nvarchar(500),@voucherno nvarchar(500))
RETURNS float
AS
BEGIN

RETURN (select isnull(sum(ReceivedAmount),0) as Discount FROM SupplierPaymentDetails inner join SupplierPayment 
 on SupplierPayment.voucherno=SupplierPaymentDetails.VoucherNo 
where SupplierPaymentDetails.PurchaseNo=@purchaseNo  and SupplierPayment.SupplierCode =@suppcode and SupplierPayment.voucherno<>@voucherno )

END


GO
/****** Object:  UserDefinedFunction [dbo].[udf_SelectDiscountAmountOfSupplierforPurchase]    Script Date: 13-01-2025 20:24:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create FUNCTION [dbo].[udf_SelectDiscountAmountOfSupplierforPurchase] ( @purchaseNo nvarchar(500),@suppcode nvarchar(500),@voucherno nvarchar(500))
RETURNS float
AS
BEGIN

RETURN (select isnull(sum(Discount),0) as Discount FROM SupplierPaymentDetails inner join SupplierPayment 
 on SupplierPayment.voucherno=SupplierPaymentDetails.VoucherNo 
where SupplierPaymentDetails.PurchaseNo=@purchaseNo  and SupplierPayment.SupplierCode =@suppcode and SupplierPayment.voucherno=@voucherno )

END