SET QUOTED_IDENTIFIER OFF
GO
/**
*    
*    Description: This view calculates the status of Sysusers based on Safety System dates
*
*/

CREATE view [dbo].[SafetySystemPersonnelStatus]
(
    SysuserFID,
    ColorCode
)
as

select
    SysuserFID = Sysuser.SysUserID,
    ColorCode =
        case
            when SafetySystemPersonnel.AdministrativeHoldFlag = 1 then 'Black'
            when
            (
                SafetySystemPersonnel.NextPhysicalDate < GetDate() or
                SafetySystemPersonnel.LicenseExpiresDate < GetDate() or                
                SafetySystemPersonnel.NextMVRDate < GetDate() or
                SafetySystemPersonnel.NextBackgroundCheckDate < GetDate()                
            ) then 'Red'
            when
            (
                SafetySystemPersonnel.NextPhysicalDate - 30 < GetDate() or
                SafetySystemPersonnel.LicenseExpiresDate - 30 < GetDate() or            
                SafetySystemPersonnel.NextMVRDate - 30 < GetDate() or
                SafetySystemPersonnel.NextBackgroundCheckDate - 30 < GetDate()                
            ) then 'Yellow'
            else 'Green'
        end            
from Sysuser
inner join SafetySystemPersonnel on Sysuser.SysUserID = SafetySystemPersonnel.SysuserFID
GO
GRANT SELECT ON  [dbo].[SafetySystemPersonnelStatus] TO [MssExec]
GRANT INSERT ON  [dbo].[SafetySystemPersonnelStatus] TO [MssExec]
GRANT DELETE ON  [dbo].[SafetySystemPersonnelStatus] TO [MssExec]
GRANT UPDATE ON  [dbo].[SafetySystemPersonnelStatus] TO [MssExec]
GO