USE $(DATA_BASE_NAME);
GO

/****** Object:  StoredProcedure [dm].[CalcOEFOIFReviewTime]    Script Date: 5/16/2016 12:59:07 PM ******/
DROP PROCEDURE [dm].[CalcOEFOIFReviewTime]
GO

/****** Object:  StoredProcedure [dm].[CalcOEFOIFReviewTime]    Script Date: 5/16/2016 12:59:07 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO









--select reviewtype from dm.ReviewAggregate group by reviewtype
--execute [dm].[CalcOEFOIFReviewTime]


--ALTER procedure [dm].[CalcOEFOIFReviewTime]
CREATE procedure [dm].[CalcOEFOIFReviewTime]
as
with init as (select e.requestId RequestID,Row_Number() over (partition by e.requestId, e.reviewId order by e.CreatedOn asc) RID, 'OEF' as RevType, e.createdOn SD,null EndDate,0 RevCount,0 isApproved,0 isDenied,null as CurrentStatus, e.reviewId  
    from Hib.Event e
    left join dm.ReviewAggregate ra on e.requestId = ra.requestID and ra.reviewId = e.reviewId and ReviewType = 'OEF'
where 
    e.Name like '%Request sent for OEF%'    --should probably use the EventTypeId and GroupId here instead of the name
    and (ra.RequestID is null or (ra.reviewId != e.reviewid ) ) )

Insert into dm.ReviewAggregate(requestID,ReviewType,StartDate,EndDate,RevisionCount,isApproved,isdenied,CurrentStatus,reviewId)
select RequestID, RevType,SD,EndDate,RevCount,isApproved,isdenied,CurrentStatus, reviewId from INIT
where RID = 1

;with base as (select Row_Number() over (partition by requestID, reviewId order by createdOn) RID, ev.*
from hib.[Event] ev inner join hib.[eventgroup] eg on ev.id = eg.eventid
where (name like '%OEF%' or (ev.eventtypeid = 13 and eg.groupid = 9 ) )
and EventTypeID != 4 )  --submitted with changes

, base2 as (select c1.RequestID, c1.reviewid, Case when c1.Name like '%Request sent for OEF%' then 'Reviewer'
                    when c1.Name = 'Change Requested by OEF-OIF' then 'Requestor'
                    when c1.Name like '%OEF-OIF Review Approval%' or c1.Name like '%OEF/OIF Admin Review Approval%' 
                                 or c1.Name like '%OEF-OIF Request Approval%' then 'N/A'
                    when c1.Name = 'OEF-OIF Review Denied' then 'N/A' 
                    when c1.Name like '%Withdrawn%' then 'N/A' end as Actor
                    , dbo.udf_GetCalendarDays(c1.CreatedOn, Coalesce(c2.CreatedOn,getdate())) as CalDayCount
                    , dbo.udf_GetBusinessDays(c1.CreatedOn, Coalesce(c2.CreatedOn,getdate())) as BusDayCount
from Base c1 
    left join Base c2 on c1.RequestID = c2.RequestID and c1.reviewId = C2.reviewId and c1.RID = c2.RID -1)
--Update table 

, base3 as (select requestID, reviewId
                            , max(Case when Actor = 'Requestor' then BusDayCount end) ReqBusDays
                            , Max(Case when Actor = 'Reviewer'  then BusDayCount end) RevBusDays
                            , max(Case when Actor = 'Requestor' then CalDayCount end) ReqCalDays
                            , Max(Case when Actor = 'Reviewer'  then CalDayCount end) RevCalDays
from base2 b
where actor != 'N/A'
group by requestID, reviewId)



Update agg
    set agg.TotalReviewerBusinessDays = coalesce(b.RevBusDays,0)
    , agg.TotalRequestorBusinessDays = coalesce(b.ReqBusDays,0)
    , agg.TotalReviewerCalendarDays = coalesce(b.RevCalDays,0)
    , agg.TotalRequestorCalendarDays = coalesce(b.ReqCalDays,0)
from base3 b
    inner join dm.ReviewAggregate agg on b.requestID = agg.RequestID and b.reviewid = agg.reviewId and reviewType = 'OEF'

/*Update Approved/Denied/Withdrawn recs*/
Update  ra
    set ra.isApproved = Case when e.Name like '%OEF-OIF Review Approval%' or e.Name like '%OEF/OIF Admin Review Approval%' or e.Name like '%OEF-OIF Request Approval%' then 1 else 0 end
        ,ra.isDenied =  Case when e.Name = 'OEF-OIF Review Denied' then 1 else 0 end
        ,ra.EndDate = e.CreatedOn
        ,ra.CurrentStatus = Case when e.Name like '%OEF-OIF Review Approval%' or e.Name like '%OEF/OIF Admin Review Approval%' 
                                    or e.Name like '%OEF-OIF Request Approval%' then 'Approved' 
                            when e.Name like '%OEF-OIF Review Denied%' then 'Denied' end

from dm.ReviewAggregate ra
    inner join hib.[Event] e on ra.RequestID = e.RequestID and ra.reviewId = e.reviewId 
--  inner join view1 v1 on e.requestId = v1.requestId and e.reviewId = v1.reviewId and e.CreatedOn = v1.createdOn
where ra.ReviewType = 'OEF'
and e.Name in ('OEF-OIF Review Approval','OEF/OIF Admin Review Approval','OEF-OIF Review Denied', 'OEF-OIF Request Approval') 

Update  ra
    set ra.isApproved = 0
        ,ra.isDenied =  0
        ,ra.TotalReviewerBusinessDays = 0
        ,ra.TotalRequestorBusinessDays = 0
        ,ra.TotalReviewerCalendarDays = 0
        ,ra.TotalRequestorCalendarDays = 0
        ,ra.EndDate = e.CreatedOn
        ,ra.CurrentStatus = 'Withdrawn' 
  from dm.ReviewAggregate ra
    inner join hib.[Event] e on ra.RequestID = e.RequestID and ra.reviewId = e.reviewId
where ra.ReviewType = 'OEF'
and e.Name like '%Withdrawn%' 


;with mxEvent as (select e.RequestID, e.reviewid, max(createdOn) createdOn
from dm.ReviewAggregate ra
    inner join hib.[Event] e on ra.requestID = e.RequestID and ra.reviewId = e.reviewid
where ra.ReviewType = 'OEF'
and e.Name like '%OEF%'
and ra.EndDate is null
and e.EventTypeID != 4
group by e.RequestID, e.reviewId)


Update  ra
    set ra.isApproved = 0
        ,ra.isDenied =  0
        ,ra.EndDate = null
        ,ra.CurrentStatus = Case when Name like '%Change Requested by OEF-OIF%' then 'Waiting for Requestor' 
                                                when Name like '%Request sent for OEF%' then 'Waiting for Reviewer' end 
from dm.ReviewAggregate ra
inner join mxEvent b on ra.RequestID = b.RequestID and ra.reviewId = b.reviewid 
    inner join hib.[event] e on b.requestID = e.RequestID and b.reviewid = e.reviewid and b.CreatedOn = e.CreatedON and e.Name like '%OEF%' and e.EventTypeID != 4
where ra.reviewType = 'OEF';









GO


