CREATE PROC [dbo].[sp_ReceiptListingByDate]
(
	@DateFrom DATETIME
	,@DateTo DATETIME
)
AS



CREATE TABLE #TmpInvoicePayments
(
recNo INT,
invNo VARCHAR(50)
)

;WITH tmp(invNo, invDate, allocatedPayment, allocatedReference) AS
(
    SELECT
        invoiceNo, dateOfService,
        CONVERT(VARCHAR(50),LEFT(allocatedReference, CHARINDEX(',', allocatedReference + ',') - 1)),
        STUFF(allocatedReference, 1, CHARINDEX(',', allocatedReference + ','), '')
    FROM pal_Account
	WHERE isVisible = 1 AND procedureType = 'TI' and allocatedReference <> '' 

    UNION all

    SELECT
        invNo, invDate,
        CONVERT(VARCHAR(50),LEFT(allocatedReference, CHARINDEX(',', allocatedReference + ',') - 1)),
        STUFF(allocatedReference, 1, CHARINDEX(',', allocatedReference + ','), '')
    FROM tmp
    WHERE
        allocatedReference > ''
)
INSERT INTO #TmpInvoicePayments
SELECT
    r.receiptNo,
	'INV'+CONVERT(VARCHAR(2),RIGHT(dbo.GetFiscalYear(invDate),2))+REPLACE(STR(invNo, 6), SPACE(1), '0') as invoices 
FROM tmp 
LEFT OUTER JOIN pal_Account r ON r.recId = CASE WHEN CHARINDEX(':', allocatedPayment) > 0 
		THEN SUBSTRING(allocatedPayment,0,CHARINDEX(':',allocatedPayment)) 
		ELSE allocatedPayment 
	END
WHERE ISNULL(CASE WHEN CHARINDEX(':', allocatedPayment) > 0 
		THEN  CONVERT(DECIMAL(18,2), REPLACE(allocatedPayment, SUBSTRING(allocatedPayment,0,CHARINDEX(':',allocatedPayment) + 1), ''))
		ELSE NULL
	END, 1) > 0
GROUP BY CASE WHEN CHARINDEX(':', allocatedPayment) > 0 
		THEN SUBSTRING(allocatedPayment,0,CHARINDEX(':',allocatedPayment)) 
		ELSE allocatedPayment 
	END, r.receiptNo, 'INV'+CONVERT(VARCHAR(2),RIGHT(dbo.GetFiscalYear(invDate),2))+REPLACE(STR(invNo, 6), SPACE(1), '0')
OPTION (maxrecursion 0)

SELECT MAX(CONVERT(VARCHAR(10),A.dateOfService, 120)) [PaymentDateTime]
              ,CASE WHEN U.name IS NULL THEN '' ELSE dbo.GetInitials(U.name+' '+U.surname) END AS [PaymentReceiptingAuthorizedBy]          
              ,'REC'+CONVERT(VARCHAR(2),RIGHT(dbo.GetFiscalYear(A.dateOfService),2))+REPLACE(STR(A.receiptNo, 6), SPACE(1), '0') AS [ReceiptNumber]
              ,A.patientNumber AS [PatientNumber]
              ,A.patientName+' '+A.patientSurname AS [Patient]
              ,A.medAidName AS [MedicalAid]
              ,A.procedureCode AS [PaymentMethod]
              ,SUM(A.amount)*-1 AS [TotalReceiptedAmount]
              ,ISNULL(dbo.GetAmountPaidToAssistantFee(A.allocatedReference),0) AS [AmountPaidToAssistantFee]
              ,STUFF( (select ',' + invNo   
                           from #TmpInvoicePayments  
                           where recNo = A.receiptNo
                           FOR XML PATH(''), TYPE
                     ).value('.', 'VARCHAR(MAX)') 
                      ,1,1,'') AS Invoices
FROM   pal_Account A
              LEFT OUTER JOIN [Framework_EVOL-1].dbo.pal_User U
                     ON U.recId = A.userId
WHERE  A.isVisible = 1
              AND A.procedureType IN ('PM') 
AND CAST(A.dateOfService as DATE)  
			BETWEEN LEFT(CONVERT(VARCHAR(50), @DateFrom, 120) , 10) AND LEFT(CONVERT(VARCHAR(50), @DateTo, 120) , 10)
GROUP BY CASE WHEN U.name IS NULL THEN '' ELSE dbo.GetInitials(U.name+' '+U.surname) END        
              ,'REC'+CONVERT(VARCHAR(2),RIGHT(dbo.GetFiscalYear(A.dateOfService),2))+REPLACE(STR(A.receiptNo, 6), SPACE(1), '0')
			  ,A.receiptNo
              ,A.patientNumber
              ,A.patientName+' '+A.patientSurname
              ,A.medAidName
              ,A.procedureCode
              ,dbo.GetAmountPaidToAssistantFee(A.allocatedReference)
ORDER BY PaymentDateTime, ReceiptNumber 

DROP TABLE #TmpInvoicePayments

