SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[udf_SelectReturnAmountWITHOUTDATE] ( @CustomerCode Varchar(50),@InvNo varchar(50)  )
RETURNS float
AS
BEGIN

RETURN (select ISNULL(Sum(NetAmount) ,0) AS NetAmt from InvoiceReturn
where Billtype='Credit' and SR='Returns'
and Customercode=@CustomerCode AND InvNo=@InvNo
  )

END
GO
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
		 Invoice.InvNo AS InvoiceNo, 
       
		ROUND( (Invoice.NetAmount-(isnull(dbo.udf_CustomerReceiptDtls(Invoice.CustomerCode,Invoice.InvNo,NULL),0)+ isnull(dbo.udf_CustomerReceiptDiscountDtls(Invoice.CustomerCode,Invoice.InvNo,NULL),0)+ISNULL([dbo].udf_SelectReturnAmountWITHOUTDATE(Invoice.CustomerCode,Invoice.InvNo),0))),2)  AS BalanceAmount
       , isnull(dbo.udf_CustomerReceiptDiscountDtls(Invoice.CustomerCode,Invoice.InvNo,NULL),0) AS DiscountAmt, 
        isnull(dbo.udf_CustomerReceiptDtls(Invoice.CustomerCode,Invoice.InvNo,NULL),0) AS ReceivedAmt,
        Invoice.NetAmount AS InvoiceAmount, 
        FORMAT(Invoice.InvoiceDate, 'dd-MM-yyyy') AS EntryDatefrmtd
    FROM 
        Invoice
         
    WHERE 
        Invoice.InvType = 'Credit' 
        AND Invoice.CustomerCode = @CustomerId
    GROUP BY 
        Invoice.InvNo, 
        Invoice.NetAmount, 
        Invoice.InvoiceDate, 
        		Invoice.CustomerCode
       
    HAVING 
        --(Invoice.NetAmount - COALESCE(InvoiceReturn.NetAmount, 0) - ISNULL(SUM(CustomerReceiptDetail.ReceivedAmount), 0) - ISNULL(SUM(CustomerReceiptDetail.Discount), 0)) > 0
		(Invoice.NetAmount-ROUND((isnull(dbo.udf_CustomerReceiptDtls(Invoice.CustomerCode,Invoice.InvNo,NULL),0)+ isnull(dbo.udf_CustomerReceiptDiscountDtls(Invoice.CustomerCode,Invoice.InvNo,NULL),0)+ISNULL([dbo].udf_SelectReturnAmountWITHOUTDATE(Invoice.CustomerCode,Invoice.InvNo),0)),2)) >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