USE $(DATA_BASE_NAME);
GO

/****** Object:  StoredProcedure [WS].[GetDARTDashboardRequestsByGroup]    Script Date: 1/26/2016 4:22:21 PM ******/
DROP PROCEDURE [WS].[GetDARTDashboardRequestsByGroup]
GO

/****** Object:  StoredProcedure [WS].[GetDARTDashboardRequestsByGroup]    Script Date: 1/26/2016 4:22:21 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE procedure [WS].[GetDARTDashboardRequestsByGroup]
@GroupName varchar(64)
as

With Res as (select  
	   r.[state] as s1,  
	   r.activityid as s2,  
	   r.id as s3,  
	   r.trackingnumber as s4, 
	   rev.ID as RevID,
	   a.name as s5,  
	   a.officialname as s6,  
	   r.submittedon as s7, 
	   cast(submitted.initSubmittedOn as Datetime) as initSubmittedOn, 
	   r.updatedon 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,  
	   totcount,
	   apprejCount,

	   inv.Fullname, 

		  Case when w.WorkflowState = 3 then 'Change Requested' 
							 when w.WorkflowState = 4 then 'Approved' 
							 when w.WorkflowState = 5 then 'Denied' 
							 when w.WorkflowState = 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 w.WorkflowState = 1 then 'Initiated' 
							 when w.WorkflowState = 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.WorkflowState = 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 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' 
			   when r.RequestType = 5 then 'Preparatory to Research 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,

--		  Case when rev.workflowid is null then 0 else rev.workflowid end as WorkflowID, 
--		  Case 
--			when rev.workflowid is null then r.[state] 	--top-level workflow only
--			else (select w.requeststate from hib.requestworkflow w where w.id=rev.workflowid) end as WorkflowState, --multi-workflow
		 w.WorkflowID,
  		 w.WorkflowTemplateID,
		 w.WorkflowState,
		 Case when w.WorkflowTemplateID is null then 1 else t.groupid end as WorkflowGroupID
--		 ,apprejCount,
--		 totcount

   from hib.request r   
	   inner join hib.activity a on r.activityid=a.id  
	   inner join hib.Review rev on r.ID = rev.RequestID
	   inner join hib.[Group] g on rev.GroupID = g.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  

	   --get the workflow metadata for this review
	   left join	(select rev.id as reviewid, 
									Case when rev.workflowid is null then 0 else rev.workflowid end as WorkflowID, 
									Case when rev.workflowid is null then r.[state] 	--top-level workflow only
										 else (select w.requeststate from hib.requestworkflow w where w.id=rev.workflowid) end as WorkflowState, --multi-workflow
									Case when rev.workflowid is null then null 	--top-level workflow only
										 else (select w.workflowtemplateid from hib.requestworkflow w where w.id=rev.workflowid) end as WorkflowTemplateID --multi-workflow
					 from hib.review rev left join hib.request r on rev.requestid=r.id) w
	   on w.reviewid=rev.id
	   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 (r.ID = aggRes.ID and ((rev.workflowid is null) or rev.workflowid = aggres.WorkflowID) and (r.state=2 or w.WorkflowState=2))	--only care about the %completed if the request is in progress

--	   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
																 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 
  where g.Name = @GroupName
  									 
)

/* 
select Distinct res.*,maxtrackingnumber,rid from res
	inner join (select activityID, ID, row_number() over (partition by ActivityID order by createdOn desc) as rid, max(trackingnumber) over (partition by ActivityID order by trackingnumber desc) as maxtrackingnumber
						from hib.Request) req on res.s2 = req.ActivityID and res.s3 = req.ID
where req.RID = 1 and (WorkflowID is not null) and (WorkflowID = 0 or WorkflowState != 6)	--keep the workflows that haven't been closed (Data Sources removed)
--where res.s4=req.maxtrackingnumber and (WorkflowID is not null) and (WorkflowID = 0 or WorkflowState != 6)	--keep the workflows that haven't been closed (Data Sources removed)
order by res.s4, res.s3
*/

/* original:  keep the most recent request in the chain
select Distinct res.* 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
where req.RID = 1 and (WorkflowID is not null) and (WorkflowID = 0 or WorkflowState != 6)	--keep the workflows that haven't been closed (Data Sources removed)
order by res.s4, res.s3
end original:  keep the most recent request in the chain */

--keep the most recent review for this activity
select Distinct res.* from res
	inner join (select activityID, r.ID as requestid,rev.id as reviewid, rev.groupid, row_number() over (partition by ActivityID,groupid order by r.createdOn desc) as revrowid
				from hib.Review rev
				left join hib.request r on rev.requestid=r.id) rev on res.s2 = rev.ActivityID and res.s3 = rev.requestid and res.RevID = rev.reviewid
--where rev.revrowid = 1 and (WorkflowID is not null) and (WorkflowID = 0 or WorkflowState != 6)	--keep the workflows that haven't been closed (Data Sources removed)
where rev.revrowid = 1 and (WorkflowID is not null) --and (WorkflowID = 0 or WorkflowState != 6)	
order by res.s4, res.s3



GO
