USE [VINCI]
GO

/****** Object:  StoredProcedure [WS].[GetDARTDashboardRequestsByUser]    Script Date: 8/12/2013 9:34:06 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--ALTER procedure [WS].[GetDARTDashboardRequestsByUser]
CREATE procedure [WS].[GetDARTDashboardRequestsByUser]
@Participant varchar(64)

as

with res as (select  
   r.[state] as s1,  
   r.activityid as s2,  
   r.id as s3,  
   r.trackingnumber as s4, 
   a.name as s5,  
   a.officialname as s6,  
   cast(r.submittedon as Datetime) as s7,  
   cast(submitted.initSubmittedOn as Datetime) as initSubmittedOn,  
   cast(r.updatedon as Datetime) as s8,  
   r.requesttype as s9,  
   (select count(*) from hib.review v where v.requestid=r.id) as cnt1, 
   (select count(*) from hib.review v where v.requestid=r.id and v.updatedon is not null) as cnt2,  
   inv.Fullname, 

     Case when r.State = 3 then 'Change Requested' 
						 when r.State = 4 then 'Approved' 
						 when r.State = 5 then 'Denied' 
						 when r.State = 6 then 'Closed'
						 when r.RequestType = 4 and r.State = 1 then 'Initiated' 
						 when r.RequestType = 4 and r.State = 2 and r.WorkflowState = 2 then '0%' 
						 when r.RequestType = 4 and r.State = 2 and r.WorkFlowState = 3 then '50%' 
						 when r.RequestType = 4 and r.State = 16 then 'Approved'
						 when r.State = 1 then 'Initiated' 
						 when r.State = 2 then Cast(Case when totCount = 0 then 0 
													  when initNDS.initRev = 0 then 0
													  when totCount != 0 then ((apprejCount+ 1) * 100) / (totcount+2) end as Varchar(100))+'%' 
						 end as RequestStatus,
	 Case   
						 when r.RequestType = 4 and  r.WorkflowState = 1 then 1 
						 when r.State = 1 then 1 
						 when r.State = 3 then 1  
						 else 0 end as [editable], 
	 ammend.Ammendable as Ammendable, 
	 TD.TimeDiff as FinalEventTimeDiffInDays, 
	  Case when r.RequestType = 2 then 'Research Data Access'  
					when r.RequestType = 4 then 'Operations Data Access' end as RequestType, 
	  Case when r.RequestType = 2 then 1 else 0 end as isRes, 
	  Case when r.RequestType = 4 then 1 else 0 end as isOp
from hib.request r   
	   inner join hib.activity a on r.activityid=a.id  

	   --left join  (select part.RequestID, person.fullname, row_Number() over (partition by part.RequestID, Part.LocationID order by person.Name desc) RID from hib.participant part inner join hib.person person on part.PersonID = person.ID inner join hib.request r on part.RequestID = r.ID and part.LocationID = r.PrimarySiteID where part.principalInvestigator=1 )  
	 --inv on r.ID = inv.RequestID and inv.RID = 1  
	   left join hib.vwPrimarycontact inv on r.ID = inv.ID

	   left join (select r.ID, SUm(case when Coalesce(rev.approved,rev.rejected) is not null then 1 else 0 end) apprejCount, Count(rev.RequestID) as totcount 
										 from hib.Request R 
										 left join hib.Review  rev on r.ID = rev.RequestID  
										 where r.state = 2 
												group by r.ID) aggres on r.ID = aggRes.ID 

	  left join (select requestID, Max(Case when event.Name = 'Initial NDS Review Complete' then 1 else 0 end) as initRev	--TODO: might want to use the eventtypeid and the groupid instead of the event name here
																 from hib.event
																group by requestID) initNDS on r.ID = initNDS.RequestID

	  left join (select requestID, min(createdon) as initSubmittedOn from hib.[event] where eventtypeid=2 group by requestID) submitted on r.id=submitted.requestID

	  left join (select r.ID,r.activityID, Case when mxHead.mxH = 0 and r.State in (4,5,6,7) then 1
				  when r.CreatedOn = mxCr.mxAM  and r.state in (4,5,6,7) then 1 
				  else 0 end as ammendable 
				  from hib.request r  
				  inner join (select ActivityID, Max(HeadID) mxH from hib.Request 
						  group by ActivityID) mxHead on r.ActivityID = mxHead.ActivityID 
				  inner join (select ActivityID, Max(CreatedOn) mxAm from hib.Request  
						  group by ActivityID) mxCr on r.ActivityID = mxCr.ActivityID 
	   ) ammend on r.ID = ammend.ID      
 
	   left join ( select r.ID
				  , Cast(Round(Cast(DateDiff(minute,r.submittedon,Coalesce(mxE.mxcr,getdate())) / 60.0 / 24.0 as Numeric(9,2)),0) as int) as TimeDiff   
			   from hib.Request r  
					left join (select requestID, Max(e.createdON) mxcr from hib.[event] e
									inner join hib.Request r on e.RequestID = r.ID
										where r.State in (4,5,6,7)
									 group by requestID) mxE on r.ID = mxE.RequestID ) TD on r.ID = TD.ID 
	  left join hib.Participant p on r.ID = p.RequestID
	  left join hib.Person pers on p.PersonID = pers.ID
	  left join hib.Person pers2 on r.RequestorID = pers2.ID
where (pers.Name = @Participant or pers2.Name = @Participant)

)

--select Distinct * from res
select distinct res.*,
	   Case when w.id is null then 0 else w.id end as WorkflowID, 
	   Case when w.id is null then res.s1 else w.requeststate end as WorkflowState,
	   Case when w.WorkflowTemplateID is null then null else w.WorkflowTemplateID end as WorkflowTemplateID,
	   Case		 when w.id is null then res.RequestStatus 
				 when w.RequestState = 3 then 'Change Requested' 
				 when w.RequestState = 4 then 'Approved' 
				 when w.RequestState = 5 then 'Denied' 
				 when w.RequestState = 6 then 'Closed'
				 when w.RequestState = 1 then 'Initiated' 
				 when w.RequestState = 2 and (w.WorkflowTemplateID is null or w.WorkflowTemplateID = 1) then			--NDS workflow												
															Cast(Case when totCount = 0 then 0 
															when initNDS.initRev = 0 then 0
															when totCount != 0 then ((apprejCount+ 1) * 100) / (totcount+2) end as Varchar(100))+'%' 
				 when w.RequestState = 2 then Cast(Case when totCount = 0 then 0										--Independent Approver workflow
														when apprejCount = 0 then 0
														else 1 end as Varchar(100))+'%' 
				 end as RequestStatus,
	   Case when w.WorkflowTemplateID is null then 1 else t.groupid end as WorkflowGroupID
--	   ,totcount,
--	   apprejCount
from res
	inner join (select activityID, ID, row_number() over (partition by ActivityID order by createdOn desc) as rid
						from hib.Request) req on res.s2 = req.ActivityID and res.s3 = req.ID
	
	left join hib.requestworkflow w on w.requestid=res.s3
	left join hib.workflowtemplate t on w.workflowtemplateid=t.id

	left join (select r.ID, wid.WorkflowID, SUm(case when Coalesce(rev.approved,rev.rejected) is not null then 1 else 0 end) apprejCount, Count(rev.RequestID) as totcount 
			   from hib.request r
	  		  			left join	(select rev2.requestid as requestid, 
											Case when rev2.workflowid is null then 0 else rev2.workflowid end as WorkflowID
									from hib.review rev2
									group by rev2.requestid,rev2.workflowid) wid on wid.requestid=r.id
						left join hib.Review  rev on (r.ID = rev.RequestID and (wid.WorkflowID is not null) and ((rev.WorkflowID is null and wid.WorkflowID=0) or wid.WorkflowID=rev.workflowid))
--			   where r.state = 2  --only care about the %completed if the request is in progress
			   group by r.ID, wid.WorkflowID) aggres on (res.s3 = aggRes.ID and ((w.id is null) or w.id = aggres.WorkflowID) and (res.s1=2 or w.requeststate=2))	--only care about the %completed if the request is in progress

    left join (select requestID, Max(Case when event.Name = 'Initial NDS Review Complete' then 1 else 0 end) as initRev
			   from hib.event
			   group by requestID) initNDS on res.s3 = initNDS.RequestID

--where req.RID = 1
where req.RID = 1 and (w.id is null or w.RequestState != 6)	--keep the workflows that haven't been closed (Data Sources removed)
order by res.s4, res.s3	--trackingnumber,id


GO

