SET NOCOUNT ON

DECLARE @DUTY_START DATETIME,
    @DUTY_END DATETIME,
    @GROUPBY1 VARCHAR(60),
    @GROUPBY2 VARCHAR(60),
    @GROUPBY3 VARCHAR(60)  

SELECT  @DUTY_START = ISNULL(NULL, '1/1/1900'),
        @DUTY_END = ISNULL(NULL, '1/1/3000'),
        @GROUPBY1 = CONVERT(VARCHAR(60), 'Agency'),
        @GROUPBY2 = CONVERT(VARCHAR(60), 'Case Type'),
        @GROUPBY3 = CONVERT(VARCHAR(60), 'None')

SELECT
  	g.GuarantorID,
  	g.First AS GuarantorFirst,
  	g.Middle AS GuarantorMiddle,
  	g.Last AS GuarantorLast,
  	dbo.FormatName(g.Prefix , g.First , g.Middle , g.Last , g.Suffix) AS GuarantorName,
  	pp.PatientProfileId,
  	pp.First AS PatientFirst,
  	pp.Middle AS PatientMiddle,
  	pp.Last AS PatientLast,
  	dbo.FormatName(pp.Prefix , pp.First , pp.Middle , pp.Last , pp.Suffix) AS PatientName,
	pp.Birthdate,
	ISNULL(SUBSTRING(pp.SSN , 1 , 3) + '-' + SUBSTRING(pp.SSN , 4 , 2) + '-' + SUBSTRING(pp.SSN , 6 , 4) , '') AS PatientSSN,
  	CASE WHEN pfc.CaseType = 1 THEN 'Medical'
  	     WHEN pfc.CaseType = 2 THEN 'BHS'
  	     ELSE ''
  	END AS CaseType,
  	diag.Code AS DiagnosisCodeID,
  	pfc.StartDate,
  	pfc.EndDate,
	1 + DATEDIFF (day ,pfc.startdate ,isnull(pfc.enddate,getdate()) ) AS NumberOfDays,
  	CASE WHEN pfc.POD = '-1' THEN ''
  	     WHEN pfc.POD = '0' THEN 'No'
  	     WHEN pfc.POD = '1' THEN 'Yes'
  	END AS POD,
  	pfc.PODHours AS PODHours,
  	pfc.PD44,
  	pfc.PD444,
  	pfc.PD445FinalDate,
  	cml.Description AS Status,
  	pfc.DeterminationNum,
	ISNULL(cccd.cCaption , '') AS Agency,
	pfc.PFCDutyStatusID,
	pfc.InjuryDate, 
	pfc.DisabilityDate,
	pfc.MedicalHistory,
	pfc.CaseNotes, 
	( 
	SELECT TOP 1   
		a.ApptStart
	FROM     
		Appointments a 
		INNER JOIN PatientProfile pp1 ON a.OwnerId = pp1.PatientProfileId
	WHERE    
		a.ApptKind = 1 AND ( a.Canceled = 0 or a.Canceled IS NULL)
		AND pp1.PatientProfileId = pp.PatientProfileId
		AND a.ApptStart > GETDATE()
	ORDER BY  
		a.ApptStart DESC
    ) AS [Return to Clinic], 
	CASE @GROUPBY1 
		WHEN 'Agency' THEN ISNULL(cccd.cCaption , '')
		WHEN 'Guarantor Name' THEN dbo.FormatName(g.Prefix , g.First , g.Middle , g.Last , g.Suffix)
		WHEN 'Patient Name' THEN dbo.FormatName(pp.Prefix , pp.First , pp.Middle , pp.Last , pp.Suffix) 
		WHEN 'Diagnosis' THEN diag.Code
		WHEN 'Status' THEN cml.Description
		WHEN 'POD' THEN CASE WHEN pfc.POD = '-1' THEN 'Not Set' WHEN pfc.POD = '0' THEN 'No' WHEN pfc.POD = '1' THEN 'Yes' END
		WHEN 'Case Type' THEN CASE WHEN pfc.CaseType = 1 THEN 'Medical' WHEN pfc.CaseType = 2 THEN 'BHS' ELSE '' END
		ELSE 'None'
	END AS GroupBy1 ,
	CASE @GROUPBY2
		WHEN 'Agency' THEN ISNULL(cccd.cCaption , '')
		WHEN 'Guarantor Name' THEN dbo.FormatName(g.Prefix , g.First , g.Middle , g.Last , g.Suffix)
		WHEN 'Patient Name' THEN dbo.FormatName(pp.Prefix , pp.First , pp.Middle , pp.Last , pp.Suffix) 
		WHEN 'Diagnosis' THEN diag.Code
		WHEN 'Status' THEN cml.Description
		WHEN 'POD' THEN CASE WHEN pfc.POD = '-1' THEN 'Not Set' WHEN pfc.POD = '0' THEN 'No' WHEN pfc.POD = '1' THEN 'Yes' END
		WHEN 'Case Type' THEN CASE WHEN pfc.CaseType = 1 THEN 'Medical' WHEN pfc.CaseType = 2 THEN 'BHS' ELSE '' END
		ELSE 'None'
	END AS GroupBy2 ,
	CASE @GROUPBY3 
		WHEN 'Agency' THEN ISNULL(cccd.cCaption , '')
		WHEN 'Guarantor Name' THEN dbo.FormatName(g.Prefix , g.First , g.Middle , g.Last , g.Suffix)
		WHEN 'Patient Name' THEN dbo.FormatName(pp.Prefix , pp.First , pp.Middle , pp.Last , pp.Suffix) 
		WHEN 'Diagnosis' THEN diag.Code
		WHEN 'Status' THEN cml.Description
		WHEN 'POD' THEN CASE WHEN pfc.POD = '-1' THEN 'Not Set' WHEN pfc.POD = '0' THEN 'No' WHEN pfc.POD = '1' THEN 'Yes' END
		WHEN 'Case Type' THEN CASE WHEN pfc.CaseType = 1 THEN 'Medical' WHEN pfc.CaseType = 2 THEN 'BHS' ELSE '' END
		ELSE 'None'
	END AS GroupBy3
   
FROM 
	PatientProfile pp
	LEFT JOIN Guarantor g ON pp.GuarantorId = g.GuarantorId
	INNER JOIN cus_PFCDutyStatus pfc ON pfc.GuarantorId = g.GuarantorId
	LEFT JOIN Diagnosis diag ON pfc.DiagnosisCodeID = diag.DiagnosisId 
   	LEFT JOIN cusCRIMedLists cml ON pfc.StatusMlD = cml.MedlistsId
   	LEFT JOIN cusCustomControlPatientData cccpd ON cccpd.PatientProfileID = pp.PatientProfileId and cccpd.CustomControlMasterID = 1
   	LEFT JOIN cusCustomControlDetail cccd  ON cccpd.CustomControlDetailID = cccd.CustomControlDetailID 

WHERE
	((pfc.StartDate >= @DUTY_START AND pfc.EndDate < @DUTY_END + 1) or pfc.StartDate IS NULL or pfc.EndDate IS NULL)
	AND --Filter on Guarantor
	(
	(NULL IS NOT NULL AND pp.GuarantorID IN (NULL)) OR
	(NULL IS NULL)
	)	
	AND --Filter on Patient
	(
	(NULL IS NOT NULL AND  pp.PatientProfileID IN (NULL)) OR
	(NULL IS NULL)
	)
	AND  --Filter on Diagnosis
	(
	(NULL IS NOT NULL AND pfc.DiagnosisCodeID IN (NULL)) OR 
	(NULL IS NULL)
	)
	AND  --Filter on Duty Status
	(
	(1 = 2 AND pfc.StatusMlD IN (NULL)) OR
	(1 = 1)
	)
	AND  --Filter on Agency
	(
	(1 = 2 AND cccd.CustomControlDetailID IN (NULL)) OR
	(1 = 1)
	)
	AND  --Filter on Case Type
	(
	(1 = 2 AND pfc.CaseType IN (NULL)) OR
	(1 = 1)
	)
	AND  --Filter on POD
	(
	(1 = 2 AND pfc.POD IN (NULL)) OR
	(1 = 1)
	)