/*
Fields required for the PHAIR Widget with Risk

*/

DECLARE @StartEventDate DATETIME = '01-Jan-2019' -- Enter the Start and the End Event Dates to filter the data
DECLARE @EndEventDate DATETIME = '01-July-2019'

select 
  de.EventNumber as [EventID]                --EventID
 ,do.OrgUnitName as [BusinessUnit]                --Business Unit
 ,dent.EntityName as [Project]                 --Project
 ,substring(( select  ', ' + dety.EntityTypeName
     from  DimEntityType dety   
     left join   BridgeEntityEntityType beety on beety.DimEntityTypeId = dety.DimEntityTypeId
     where  beety.DimEntityId = de.DimEntityId
     group by dety.EntityTypeName
     for xml path(''),TYPE).value('.','NVARCHAR(MAX)'),3,4000) as [Entity]      --Entity (This is Entity Type but MCD use ""Entity"" as terminology)
 ,de.EventName as [Event Title]                 --Event Title
 ,devt.EventTemplateName as [Form]                 --Form
 ,FORMAT(de.EventDateTime,'HH:mm dd MMM yyyy') as [Event Date]   --Event Date 
 ,substring(( select  ', ' + devcat.EventTemplateCategoryName
     from  BridgeEventClassCategorySeverity devc   
     left join   DimEventTemplateCategory devcat on devcat.DimEventTemplateCategoryId = devc.DimEventTemplateCategoryId
     where  devc.DimEventId = de.DimEventId
     group by devcat.EventTemplateCategoryName
     for xml path(''),TYPE).value('.','NVARCHAR(MAX)'),3,4000) as [Event Category] --Event Category
 ,substring(( select  ', ' + devtcl.EventTemplateClassName
     from  BridgeEventClassCategorySeverity devcl 
     left join   DimEventTemplateClass devtcl on devcl.DimEventTemplateClassId = devtcl.DimEventTemplateClassId
     where  devcl.DimEventID = de.DimEventId
  and devcl.DimEventTemplateClassId in (41,48,49)
     group by devtcl.EventTemplateClassName
     for xml path(''),TYPE).value('.','NVARCHAR(MAX)'),3,4000) as [Event Type]  --Event Type
, de.EventRiskName as [Event Risk]
 
from DimEvent de
inner join FactEventSectionSegmentField fev on fev.DimEventId = de.DimEventId
inner join DimOrgUnit do on do.DimOrgUnitId = de.DimOrgUnitId
inner join DimEntity dent on dent.DimEntityId = de.DimEntityId
inner join DimEventTemplate devt on devt.DimEventTemplateId = de.DimEventTemplateId
inner join DimUser dusr on dusr.DimUserId = de.DimAuditorUserId
inner join DimCompanyUser dcusr on dusr.DimUserId = dcusr.DimCompanyUserId
inner join DimEventStatus dest on dest.DimEventStatusId = de.DimEventStatusId

left join CMO.dbo.AuditAnswer aans on aans.Auditid = de.OriginalEventId 
             and aans.AnswerTypeCode = 65 
             and isnumeric(substring(aans.AnswerValue,16,1)) = 1 
             and isnumeric(replace(right(aans.answervalue,2),'}','')) = 1
left join CMO.dbo.consequence c on c.code = cast(substring(aans.AnswerValue,16,1) as int) and c.CompanyID = 1179
left join CMO.dbo.Likelihood l on l.code = cast(replace(right(aans.answervalue,2),'}','') as int) and l.CompanyID = 1179
left join CMO.dbo.RiskScore rs on rs.LikelihoodID = l.LikelihoodID and rs.ConsequenceID = c.ConsequenceID

where de.EventDateTime > @StartEventDate and de.EventDateTime < @EndEventDate
--and de.eventnumber = '20171212107.0'

group by 
  de.DimEventId
 ,de.EventNumber     --EventID
 ,do.OrgUnitName     --Business Unit
 ,dent.EntityName    --Project
 ,de.EventName     --Event Title
 ,devt.EventTemplateName   --Form
 ,dcusr.FirstName + ' ' + dcusr.LastName 
 ,de.EventDateTime 
 ,dest.EventStatusName
 ,de.DimEntityId,
 de.EventRiskName