/**
*    
*    A view of the BI Reporting users that incorporates some tables in the hReports schema.
*    This is used by Admin to drive the BIUserInformation admin screen which provides a view
*    for EWS support so they can determine why some users cannot access BI Reports.
*
*    The reason for this SP is so that admin can auto-generate its code without the hReports
*    schema and views existing in the "MoversSuite2_Create" database.

*    This SP must be parameterless.
*    
*/
CREATE PROCEDURE [dbo].[BIUserInformation]
as
set nocount on

if( exists( select top 1 1 from INFORMATION_SCHEMA.VIEWS where TABLE_SCHEMA = N'hReports' and TABLE_NAME = N'BIUserInformation' ) )
begin
    select *
    from hReports.BIUserInformation
end
else
begin
    select
        UserID = UserAccess.UserID,
        UserName = dbo.FormatLastNameFirstName( SysUser.LASTNAME, SysUser.FIRSTNAME ),
        UserStatus = [Status].[Status],
        LoginID = convert( nvarchar(50), null ),
        [Role] = convert( nvarchar(50), null ),
        LastLogin = convert( datetime2(7), null ),
        IsDeletedUser = convert( bit, null ),
        UseMoversSuiteCredentials = ReportingUser.UseMoversSuiteCredentials,
        IsSuperUser = ReportingUser.IsSuperUser
    from ReportingUser
    left outer join SysUser on ReportingUser.SysUserFID = SysUser.SysUserId
    left outer join UserAccess on SysUser.SysUserId = UserAccess.SysUserId
    left outer join [Status] on SysUser.[STATUS] = [Status].PriKey
    where isnull( UserAccess.UserID, '' ) != 'msuite'
end
GO
GRANT EXECUTE ON  [dbo].[BIUserInformation] TO [MssExec]
GO