GO
/****** Object:  StoredProcedure [dbo].[Sp_GetCustomerReceiptDetailEdit]    Script Date: 01-02-2025 12:48:53 ******/
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,
        ISNULL(Invoice.NetAmount,0) AS InvoiceAmount,
        ISNULL((SELECT SUM(ReceivedAmount) FROM CustomerReceiptDetail WHERE CustomerReceiptDetail.InvoiceNo = Invoice.InvNo), 0) AS ReceivedAmt,
		
        FORMAT(invoice.InvoiceDate, '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 COALESCE(Invoice.NetAmount, 0)
            ELSE COALESCE((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),0)
        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,
		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
    FROM   
        CustomerReceiptDetail 
		inner join CustomerReceipt  on CustomerReceipt.voucherno=CustomerReceiptDetail.VoucherNo
    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(CustomerOPEntryUpdated.InvoiceDate, '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

		union all 

		 SELECT  
        CustomerReceiptDetail.InvoiceNo,
        CustomerReceiptDetail.Discount,
        CustomerReceiptDetail.ReceivedAmount,
        ISNULL(CustomerReceiptDetail.Narration, '') AS Narration,
        ISNULL(Invoice.NetAmount,0) AS InvoiceAmount,
        ISNULL((SELECT SUM(ReceivedAmount) FROM CustomerReceiptDetail WHERE CustomerReceiptDetail.InvoiceNo = Invoice.InvNo), 0) AS ReceivedAmt,
		
        FORMAT(invoice.InvoiceDate, '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 COALESCE(Invoice.NetAmount, 0)
            ELSE COALESCE((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),0)
        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,
		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
    FROM   
        CustomerReceiptDetail 
		inner join CustomerReceipt  on CustomerReceipt.voucherno=CustomerReceiptDetail.VoucherNo

    LEFT JOIN  
        Invoice ON CustomerReceiptDetail.InvoiceNo = Invoice.InvNo 
		LEFT JOIN  
        CustomerOPEntryUpdated ON CustomerReceiptDetail.InvoiceNo = CustomerOPEntryUpdated.InvoiceNo  and CustomerOPEntryUpdated.CustomerCode=CustomerReceipt.CustomerCode
    WHERE  
        CustomerReceiptDetail.VoucherNo = @VoucherNo AND Invoice.NetAmount IS NULL AND CustomerOPEntryUpdated.InvoiceAmt IS NULL and CustomerReceipt.Advance in(1,3)
END