USE $(DATA_BASE_NAME);
GO

DROP VIEW [drtrpt].[DartRequestDocument]
GO

/****** Object:  View [drtrpt].[DartRequestDocument]    Script Date: 2/24/2016 1:58:21 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE view [drtrpt].[DartRequestDocument]
as

select req.ActivityID as ResearchStudyID
              , req.ID as RequestID
              , req.TrackingNumber
              , doc.createdOn
              , doc.CreatedBy
              , doc.UpdatedOn
              , doc.UpdatedBy
              , doc.Description as DocumentDescription
              , case when doc.ContentID is not null then 'Yes' else 'No' end as isDocumentUploaded
              , case when rald.ID is not null then 'Admin Location Document'
                              when rapd.ID is not null then 'Admin Participant Document'
                              when rld.ID is not null then 'Location Document'
                              when rpd.id is not null then 'Participant Document' end as DocumentType
              , Coalesce(raldG.Name,'N/A') as AdminGroupName
              , Coalesce(raldLoc.Name,'N/A') as AdminLocation
              , Coalesce(rapdG.Name,'N/A') as AdminPartGroupName
              , Coalesce(rapdPer.FullName,'N/A') as AdminParticipantName
              , Coalesce(rldLoc.Name,'N/A') as LocationName
              , Coalesce(rpdPers.FullName,'N/A') as ParticipantName
              , case when  doc.ContentID is not null then doc.Name else 'Not yet uploaded' end as DocumentName
              
from hib.Document doc
       inner join Hib.Request req on doc.RequestID = req.ID
       left join [hib].[requestadminlocationdocument] rald on doc.ID = rald.DocumentID
              left join [hib].[group] raldG on rald.GroupID = raldG.ID
              left join [hib].[Location] raldLoc on raldLoc.ID = rald.LocationID
       left join [hib].[requestadminparticipantdocument] rapd on doc.ID = rapd.DocumentID
              left join [hib].[group] rapdG on rapd.GroupID = rapdG.ID
              left join hib.Participant rapdP on rapd.ParticipantID = rapdP.ID
              left join hib.Person rapdPer on rapdP.PersonID = rapdPer.ID
       left join [hib].[requestlocationdocument] rld on doc.ID = rld.DocumentID
              left join hib.Location rldLoc on rld.LocationID = rldLoc.ID
       left join [hib].[requestparticipantdocument] rpd on doc.ID = rpd.DocumentID
              left join hib.Participant rpdPart on rpd.participantid = rpdPart.ID
              left join hib.Person rpdPers on rpdPart.personid = rpdPers.ID
--order by req.ActivityID, req.TrackingNumber
GO