-- =============================================
-- Author: Graham Rook
-- Create date: 2016-02-24
-- Description:	Return Debtors Assisted Report Result Table
-- =============================================
-- Author: Charlene van Heerden
-- Create date: 2016-07-08
-- Description:	Need to include Assistant / Specialist name. Result must contain 1 line per patient 
-- =============================================
-- Author: Charlene van Heerden
-- Create date: 2016-09-05 
-- Description: Add VAT columns
-- =============================================
-- Author: Charlene van Heerden
-- Create date: 2019-05-27  
-- Description: TSP-31 - Assistants Report (Add Date Selection)
-- =============================================
CREATE PROCEDURE [dbo].[sp_DebtorsAssistedReportResult]
	-- Add the parameters for the stored procedure here
	@ServiceDate DateTime
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	SELECT t.PatientNum, t.MainMember, t.Due, SUM(t.DueAssistantIncl) DueToAssistantIncl, 
	SUM(t.DueAssistantIncl) - SUM(t.DueAssistantExcl) AS VAT, 
	SUM(t.DueAssistantExcl) DueToAssistantExcl,	SUM(t.PayAssistant) PayableToAssistant, 
	t.Assistant AS Assistant
	FROM (
	--get all assistant data to aggregate
		
		SELECT ac.dateOfService, ac.allocatedAssistReference, ac.patientNumber AS PatientNum, ac.name + ' ' + ac.surname AS [MainMember],
			ISNULL(ag.[dTotal],0) [Due], 
			CASE WHEN ac.modifier1 IN ('0008','0009') THEN ISNULL(ac.amount,0) 
						- dbo.GetAssistantAccountAllocatedAmountForDate(allocatedAssistReference,@ServiceDate) ELSE 0 END AS DueAssistantIncl, 

			CASE WHEN ac.modifier1 IN ('0008','0009') THEN CONVERT(DECIMAL(18,2), (ISNULL(ac.amount,0) - dbo.GetAssistantAccountAllocatedAmountForDate(allocatedAssistReference,@ServiceDate)) 
					* (100 / (100 + ac.vatRate))) ELSE 0 END AS DueAssistantExcl, 

			CASE WHEN ac.modifier1 IN ('0008','0009') AND ISNULL(ag.[dTotal],0) <= 0 THEN CONVERT(DECIMAL(18,2), (ISNULL(ac.amount,0) - dbo.GetAssistantAccountAllocatedAmountForDate(allocatedAssistReference,@ServiceDate)) 
					* (100 / (100 + ac.vatRate))) ELSE 0 END AS PayAssistant,

			CASE WHEN LTRIM(RTRIM(ac.modifier2)) = '' THEN LTRIM(RTRIM(ac.modifier3)) ELSE 
					CASE WHEN LTRIM(RTRIM(ac.modifier3)) = '' THEN LTRIM(RTRIM(ac.modifier2)) 
						ELSE LTRIM(RTRIM(ac.modifier3)) + ' - ' + LTRIM(RTRIM(ac.modifier2)) END END AS Assistant

		FROM pal_Account ac 
		LEFT OUTER JOIN pal_Ageing ag ON ag.patientNum = ac.patientNumber 
		WHERE ac.isVisible = 1 
		AND ac.modifier1 IN ('0008', '0009') 
		AND convert(DATE, ac.dateOfService) <= @ServiceDate
	) t
	GROUP BY t.PatientNum, t.MainMember, t.Due, t.Assistant
	HAVING SUM(t.DueAssistantIncl) > 0 

END