USE $(DATA_BASE_NAME);
GO

/****** Object:  StoredProcedure [WS].[GetDARTDashboardRequestsByIrma]    Script Date: 2/17/2016 2:34:19 PM ******/
DROP PROCEDURE [WS].[GetDARTDashboardRequestsByIrma]
GO

/****** Object:  StoredProcedure [WS].[GetDARTDashboardRequestsByIrma]    Script Date: 2/17/2016 2:34:19 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE procedure [WS].[GetDARTDashboardRequestsByIrma]
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,   
			   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,   
--			   totCountW,
--			   apprejCountW,

			   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 then Cast(Case when totCountW = 0 then 0  
										       when initNDS.initRev = 0 then 0 
										       when totCountW != 0 then ((apprejCountW+ 1) * 100) / (totcountW+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,
			   w.WorkflowID,
			   w.WorkflowTemplateID,
			   w.WorkflowState,
			   Case when w.WorkflowTemplateID is null then 1 else t.groupid end as WorkflowGroupID,
			   apprejCountW,
			   totCountW

	   from hib.request r    
			   inner join hib.activity a on r.activityid=a.id   
			   
			   left join hib.vwPrimarycontact inv on r.ID = inv.ID 

			   left join	(select r.id as requestid, 
									Case when (r.workflowid != 3 and r.workflowid != 5) then 0	--Operations or NDS at the top-level
										when (select count(w.id) from hib.requestworkflow w left join hib.workflowtemplate wt on w.workflowtemplateid=wt.id where w.requestid=r.id and wt.workflowtypeid=2)=0 then null	--no NDS child workflows yet
										else (select w.id from hib.requestworkflow w left join hib.workflowtemplate wt on w.workflowtemplateid=wt.id where w.requestid=r.id and wt.workflowtypeid=2) end as WorkflowID, --multi-workflow (NDS)
									Case when  (r.workflowid != 3 and r.workflowid != 5) then r.[state] 	--Operations or NDS at the top-level
										when (select count(w.id) from hib.requestworkflow w left join hib.workflowtemplate wt on w.workflowtemplateid=wt.id where w.requestid=r.id and wt.workflowtypeid=2)=0 then r.[state]	--no NDS child workflows yet
										else (select w.requeststate from hib.requestworkflow w left join hib.workflowtemplate wt on w.workflowtemplateid=wt.id where w.requestid=r.id and wt.workflowtypeid=2) end as WorkflowState,  --multi-workflow (NDS)
									Case when  (r.workflowid != 3 and r.workflowid != 5) then null	--Operations or NDS at the top-level
										when (select count(w.id) from hib.requestworkflow w left join hib.workflowtemplate wt on w.workflowtemplateid=wt.id where w.requestid=r.id and wt.workflowtypeid=2)=0 then null	--no NDS child workflows yet
										else (select w.workflowtemplateid from hib.requestworkflow w left join hib.workflowtemplate wt on w.workflowtemplateid=wt.id where w.requestid=r.id and wt.workflowtypeid=2) end as WorkflowTemplateID --multi-workflow (NDS)
							from hib.request r) w
			   on w.requestid=r.id
			   left join hib.workflowtemplate t on w.workflowtemplateid=t.id

			   left join (select r.ID, SUm(case when Coalesce(rev.approved,rev.rejected) is not null then 1 else 0 end) apprejCountW, Count(rev.RequestID) as totcountW  
						  from hib.Request R  
  		  			      left join	(select r.id as requestid, 
											Case 
												when (r.workflowid != 3 and r.workflowid != 5) then 0	--Operations or NDS at the top-level
												when (select count(w.id) from hib.requestworkflow w left join hib.workflowtemplate wt on w.workflowtemplateid=wt.id where w.requestid=r.id and wt.workflowtypeid=2)=0 then null	--no child workflows yet
												else (select w.id from hib.requestworkflow w left join hib.workflowtemplate wt on w.workflowtemplateid=wt.id where w.requestid=r.id and wt.workflowtypeid=2) end as WorkflowID, --multi-workflow (NDS)
											Case when (r.workflowid != 3 and r.workflowid != 5) then r.[state]
												when (select count(w.id) from hib.requestworkflow w left join hib.workflowtemplate wt on w.workflowtemplateid=wt.id where w.requestid=r.id and wt.workflowtypeid=2)=0 then r.[state]	--no child workflows yet
												else (select w.requeststate from hib.requestworkflow w left join hib.workflowtemplate wt on w.workflowtemplateid=wt.id where w.requestid=r.id and wt.workflowtypeid=2) end as WorkflowState --multi-workflow (NDS)
											from hib.request r) 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) aggresW on (r.ID = aggResW.ID and (r.state=2 or w.WorkflowState=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 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 r.State != 1
)

select Distinct * 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 (res.s7 is not null) and (WorkflowID = 0 or WorkflowState != 6)	--keep the workflows that have been submitted and haven't been closed (Data Sources removed) -> can't use the submitted timestamp because of dirty data from the old SharePoint application
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



GO