USE [VINCI_Dev]
GO

/****** Object:  StoredProcedure [dm].[CalcFinalNDSReviewTime]    Script Date: 4/28/2016 4:01:22 PM ******/
DROP PROCEDURE [dm].[CalcFinalNDSReviewTime]
GO

/****** Object:  StoredProcedure [dm].[CalcFinalNDSReviewTime]    Script Date: 4/28/2016 4:01:22 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE procedure [dm].[CalcFinalNDSReviewTime]
as
with init as (select r.ID RequestID,Row_Number() over (partition by r.ID order by e.CreatedOn asc) RID, 'Final NDS' as RevType, e.createdOn SD,null EndDate,0 RevCount,0 isApproved,0 isDenied,null as CurrentStatus,null TotalReviewerTime,null TotalRequestorTime from Hib.Request r
    inner join Hib.Event e on r.ID = e.RequestID
    left join dm.ReviewAggregate ra on r.ID = ra.requestID and ra.ReviewType = 'Final NDS' 
where 
    e.Name = 'Request Sent for Final NDS Review'
    and ra.RequestID is null)

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

;with base as (select Row_Number() over (partition by requestID order by createdOn) RID, *
from hib.[Event]
where name like '%Final NDS%'
and EventTypeID != 4)

, base2 as (select c1.RequestID, Case when c1.Name = 'Request Sent for Final NDS Review' then 'Reviewer'
                    when c1.Name = 'Change Requested by Final NDS Review' then 'Requestor'
                    when c1.Name = 'Final NDS Approval Complete' then 'N/A'
                    when c1.Name = 'Final NDS Review Complete' 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.RID = c2.RID -1)
    
, base3 as (select requestID, 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)


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 reviewType = 'Final NDS'

/*Update Approved/Denied recs*/
Update  ra
    set ra.isApproved = Case when e.Name in('final NDS Review Complete','Final NDS Approval Complete') then 1 else 0 end
        ,ra.isDenied =  Case when e.Name = 'Final NDS Review Denied' then 1 else 0 end
        ,ra.EndDate = e.CreatedOn
        ,ra.CurrentStatus = Case when e.Name in('final NDS Review Complete','Final NDS Approval Complete') then 'Approved' else 'Denied' end
from dm.ReviewAggregate ra
    inner join hib.[Event] e on ra.RequestID = e.RequestID 
where ra.ReviewType = 'Final NDS'
and e.Name in('final NDS Review Complete','Final NDS Approval Complete','Final NDS Review Denied')

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


Update  ra
    set ra.isApproved = 0
        ,ra.isDenied =  0
        ,ra.EndDate = null
        ,ra.CurrentStatus = Case when Name = 'Change Requested by Final NDS Review' then 'Waiting for Requestor' 
                                                when Name = 'Request Sent for Final NDS Review' then 'Waiting for Reviewer' end 
from dm.ReviewAggregate ra
inner join mxEvent b on ra.RequestID = b.RequestID 
    inner join hib.[event] e on b.requestID = e.RequestID and b.CreatedOn = e.CreatedON and e.Name like '%Final NDS%' and e.EventTypeiD != 4
where ra.ReviewType = 'Final NDS'




GO


