/*
It just needs to be updated to 9/30/2015 (ie up to the end of FY15) and the following items added…

1.	New Tab for “NDS Approved Research Requests”:  research requests that have been submitted for review and approved by NDS

2.	For each site:

-	DART Tracking Number for each approved request
-	Official Name of each approved request
-	PI name for each approved request
-	Names of the site’s participants for each approved request
-	Data Sources selected for each approved request
-	VINCI or Local Storage or Both 

*/

select	l.name as 'Site',
		r.trackingnumber as 'DARTTrackingNumber',
		replace(replace(replace(a.officialname,char(13)+char(10),' '),char(13),' '),char(10),' ') as 'OfficialName',
		pc.FullName as 'PI Name',
		p.FullName as 'Participant Name',
		ds.name as 'Data Source Name',

--		case when r.datamart=0 then 'false' else 'true' end as 'VINCI',
--		case when r.localserver=0 then 'false' else 'true' end as 'Local Storage',
		case when r.datamart=0 then (case when r.localserver=1 then 'Local Storage' else 'None' end) 
			 else (case when r.localserver=1 then 'Both' else 'VINCI' end)
		end as 'StorageLocation',

		cast(e.EventDate as date) as 'NDSApprovalDate'

from hib.request r 
	inner join drtrpt.dartrequestEvent e on (r.id=e.requestid and e.EventDescription='Final NDS Approval Complete')

	left join hib.activity a on r.activityid=a.id

	left join hib.vwPrimaryContact pc on pc.id=r.id

	left join hib.participant part on part.requestid=r.id
	inner join hib.person p on part.personid=p.id
	
	left join hib.researchstudydatasource rsds on rsds.researchstudyid=r.id
	left join hib.datasource ds on rsds.datasourceid=ds.id

	left join hib.requestlocation rl on rl.requestid=r.id
	inner join hib.location l on (rl.locationid=l.id and part.locationid=rl.locationid)

where r.[state] in (4,7) and (cast(e.EventDate as date) >= '2014-10-01' and cast(e.EventDate as date) <= '2015-09-30')
order by l.name,e.EventDate,r.trackingnumber,ds.name,p.fullname