Get Users

avatar
unknown
sqlserver
a year ago
14 kB
4
Indexable
declare @__ef_filter__IsTenantGlobalOverride_2 bit = 'False', -- New query
@__ef_filter__TenantId_3 int ='98762',
@__ef_filter__IsTenantGlobalOverride_4 bit ='False',
@__ef_filter__TenantId_5 int ='98762',
@__ef_filter__IsTenantGlobalOverride_6 bit ='False',
@__ef_filter__TenantId_7 int ='98762',
@__ef_filter__IsTenantGlobalOverride_8 bit ='False',
@__ef_filter__TenantId_9 int ='98762',
@__ef_filter__IsTenantGlobalOverride_0 bit ='False',
@__ef_filter__TenantId_1 int ='98762',
@__p_1 int ='0',
@__p_2 int ='1000',
@__ef_filter__IsTenantGlobalOverride_10 bit ='False',
@__ef_filter__TenantId_11 int ='98762',
@__ef_filter__IsTenantGlobalOverride_12 bit ='False',
@__ef_filter__TenantId_13 int ='98762',
@__ef_filter__IsTenantGlobalOverride_14 bit ='False',
@__ef_filter__TenantId_15 int ='98762'

SELECT [t4].[ID] AS [Id], [w3].[Type] AS [UserType], [t4].[ID2] AS [ApplicationId], [m0].[MembershipType] AS [ApplicationType], [m1].[DisplayText] AS [ApplicationStatus], [m2].[DisplayText] AS [AccreditationStatus], [m3].[DisplayText] AS [AccreditationVerificationStatus], [t4].[Firstname] AS [FirstName], [t4].[Lastname] AS [LastName], (COALESCE([t4].[Firstname], N'') + N' ') + COALESCE([t4].[Lastname], N'') AS [FullName], [t4].[Username], [t4].[IsEmailVerified], [t4].[Created2] AS [LastLoginDate], [t4].[PasswordResetRequired], [t4].[SubmittedDate] AS [Submitted], [t5].[CellPhone] AS [PrimaryPhone], [t7].[Name] AS [Institution], CASE
    WHEN [t4].[ID2] IS NOT NULL THEN [t4].[AccountId0]
    ELSE [t4].[ID1]
END AS [PrimaryAccountId], [t4].[ReferenceNumber] AS [ReferenceId], [t4].[SuitabilityIsReviewed], [t4].[IsActive], [t4].[IsLockedOut], CASE
    WHEN [t4].[ID2] IS NOT NULL THEN [a1].[DisplayText]
    ELSE [a2].[DisplayText]
END AS [AccountType], [a3].[Status] AS [AccountStatus]
FROM (
    SELECT [w].[ID], [w].[AddressID], [w].[Firstname], [w].[IsActive], [w].[IsEmailVerified], [w].[IsLockedOut], [w].[Lastname], [w].[PasswordResetRequired], [w].[UserType], [w].[Username], [t0].[ID] AS [ID1], [t0].[AccountTypeId], [t0].[StatusID], [t1].[ID] AS [ID2], [t1].[AccountId] AS [AccountId0], [t1].[AccountTypeId] AS [AccountTypeId0], [t1].[AccreditationStatusID], [t1].[AccreditationVerificationStatusID], [t1].[ReferenceNumber], [t1].[StatusID] AS [StatusID0], [t1].[SubmittedDate], [t1].[SuitabilityIsReviewed], [t1].[TypeID], [t2].[Created] AS [Created2]
    FROM [Webuser] AS [w]
    LEFT JOIN (
        SELECT [w0].[AccountID], [w0].[WebuserID]
        FROM [WebuserLink] AS [w0]
        WHERE (@__ef_filter__IsTenantGlobalOverride_2 = CAST(1 AS bit)) OR ([w0].[TenantID] = @__ef_filter__TenantId_3)
    ) AS [t] ON [w].[ID] = [t].[WebuserID]
    LEFT JOIN (
        SELECT [a].[ID], [a].[AccountTypeId], [a].[StatusID]
        FROM [Account] AS [a]
        WHERE (@__ef_filter__IsTenantGlobalOverride_4 = CAST(1 AS bit)) OR ([a].[TenantID] = @__ef_filter__TenantId_5)
    ) AS [t0] ON [t].[AccountID] = [t0].[ID]
    LEFT JOIN (
        SELECT [m].[ID], [m].[AccountId], [m].[AccountTypeId], [m].[AccreditationStatusID], [m].[AccreditationVerificationStatusID], [m].[ReferenceNumber], [m].[StatusID], [m].[SubmittedDate], [m].[SuitabilityIsReviewed], [m].[TypeID], [m].[WebuserID]
        FROM [MembershipApplication] AS [m]
        WHERE (@__ef_filter__IsTenantGlobalOverride_6 = CAST(1 AS bit)) OR ([m].[TenantID] = @__ef_filter__TenantId_7)
    ) AS [t1] ON [w].[ID] = [t1].[WebuserID]
    LEFT JOIN (
        SELECT [w1].[Created], [w1].[WebuserID]
        FROM [WebuserSignInSession] AS [w1]
        INNER JOIN (
            SELECT MAX([w2].[ID]) AS [Id]
            FROM [WebuserSignInSession] AS [w2]
            WHERE (@__ef_filter__IsTenantGlobalOverride_8 = CAST(1 AS bit)) OR ([w2].[TenantID] = @__ef_filter__TenantId_9)
            GROUP BY [w2].[WebuserID]
        ) AS [t3] ON [w1].[ID] = [t3].[Id]
        WHERE (@__ef_filter__IsTenantGlobalOverride_8 = CAST(1 AS bit)) OR ([w1].[TenantID] = @__ef_filter__TenantId_9)
    ) AS [t2] ON [w].[ID] = [t2].[WebuserID]
    WHERE ((@__ef_filter__IsTenantGlobalOverride_0 = CAST(1 AS bit)) OR ([w].[TenantID] = @__ef_filter__TenantId_1)) AND [w].[UserType] IN (2, 3)
    ORDER BY (SELECT 1)
    OFFSET @__p_1 ROWS FETCH NEXT @__p_2 ROWS ONLY
) AS [t4]
LEFT JOIN [Webuser_Type] AS [w3] ON [t4].[UserType] = [w3].[Id]
LEFT JOIN [MembershipApplicationType] AS [m0] ON [t4].[TypeID] = [m0].[ID]
LEFT JOIN [MembershipApplication_Status] AS [m1] ON [t4].[StatusID0] = [m1].[ID]
LEFT JOIN [MembershipApplication_Accreditation_Status] AS [m2] ON [t4].[AccreditationStatusID] = [m2].[ID]
LEFT JOIN [MembershipApplication_Accreditation_Verification_Status] AS [m3] ON [t4].[AccreditationVerificationStatusID] = [m3].[ID]
LEFT JOIN (
    SELECT [a0].[ID], [a0].[CellPhone]
    FROM [Address] AS [a0]
    WHERE (@__ef_filter__IsTenantGlobalOverride_10 = CAST(1 AS bit)) OR ([a0].[TenantID] = @__ef_filter__TenantId_11)
) AS [t5] ON [t4].[AddressID] = [t5].[ID]
LEFT JOIN (
    SELECT [m4].[InstitutionID], [m4].[MembershipApplicationID]
    FROM [MembershipApplication_InstitutionDetails] AS [m4]
    WHERE (@__ef_filter__IsTenantGlobalOverride_12 = CAST(1 AS bit)) OR ([m4].[TenantID] = @__ef_filter__TenantId_13)
) AS [t6] ON [t4].[ID2] = [t6].[MembershipApplicationID]
LEFT JOIN (
    SELECT [i].[ID], [i].[Name]
    FROM [Institution] AS [i]
    WHERE (@__ef_filter__IsTenantGlobalOverride_14 = CAST(1 AS bit)) OR ([i].[TenantID] = @__ef_filter__TenantId_15)
) AS [t7] ON [t6].[InstitutionID] = [t7].[ID]
LEFT JOIN [AccountType] AS [a1] ON [t4].[AccountTypeId0] = [a1].[ID]
LEFT JOIN [AccountType] AS [a2] ON [t4].[AccountTypeId] = [a2].[ID]
LEFT JOIN [AccountStatus] AS [a3] ON [t4].[StatusID] = [a3].[ID]


declare @__ef_filter__IsTenantGlobalOverride_2 bit = 'False', -- existing query
@__ef_filter__TenantId_3 int ='98762',
@__ef_filter__IsTenantGlobalOverride_4 bit ='False',
@__ef_filter__TenantId_5 int ='98762',
@__ef_filter__IsTenantGlobalOverride_6 bit ='False',
@__ef_filter__TenantId_7 int ='98762',
@__ef_filter__IsTenantGlobalOverride_8 bit ='False',
@__ef_filter__TenantId_9 int ='98762',
@__ef_filter__IsTenantGlobalOverride_0 bit ='False',
@__ef_filter__TenantId_1 int ='98762',
@__p_1 int ='0',
@__p_2 int ='1000',
@__ef_filter__IsTenantGlobalOverride_10 bit ='False',
@__ef_filter__TenantId_11 int ='98762',
@__ef_filter__IsTenantGlobalOverride_12 bit ='False',
@__ef_filter__TenantId_13 int ='98762',
@__ef_filter__IsTenantGlobalOverride_14 bit ='False',
@__ef_filter__TenantId_15 int ='98762'


SELECT [t4].[ID] AS [Id], [w3].[Type] AS [UserType], CASE
    WHEN [t4].[ID1] IS NOT NULL THEN [t4].[ID1]
    ELSE [t4].[ID0]
END AS [ApplicationId], COALESCE([m1].[MembershipType], [m2].[MembershipType]) AS [ApplicationType], COALESCE([m3].[DisplayText], [m4].[DisplayText]) AS [ApplicationStatus], COALESCE([m5].[DisplayText], [m6].[DisplayText]) AS [AccreditationStatus], COALESCE([m7].[DisplayText], [m8].[DisplayText]) AS [AccreditationVerificationStatus], [t4].[Firstname] AS [FirstName], [t4].[Lastname] AS [LastName], (COALESCE([t4].[Firstname], N'') + N' ') + COALESCE([t4].[Lastname], N'') AS [FullName], [t4].[Username], [t4].[IsEmailVerified], [t4].[Created2] AS [LastLoginDate], [t4].[PasswordResetRequired], COALESCE([t4].[SubmittedDate0], [t4].[SubmittedDate]) AS [Submitted], [t5].[CellPhone] AS [PrimaryPhone], COALESCE([t7].[Name], [t9].[Name]) AS [Institution], [t4].[ID1] AS [PrimaryAccountApplicationId], [t4].[AccountId0] AS [PrimaryAccountId], COALESCE([t4].[ReferenceNumber0], [t4].[ReferenceNumber]) AS [ReferenceId], [t4].[SuitabilityIsReviewed0] AS [SuitabilityIsReviewed], [t4].[IsActive], [t4].[IsLockedOut], COALESCE([a1].[DisplayText], [a2].[DisplayText]) AS [AccountType]
FROM (
    SELECT [w].[ID], [w].[AddressID], [w].[Firstname], [w].[IsActive], [w].[IsEmailVerified], [w].[IsLockedOut], [w].[Lastname], [w].[PasswordResetRequired], [w].[UserType], [w].[Username], [t].[ID] AS [ID0], [t].[AccountTypeId], [t].[AccreditationStatusID], [t].[AccreditationVerificationStatusID], [t].[ReferenceNumber], [t].[StatusID], [t].[SubmittedDate], [t].[TypeID], [t0].[ID] AS [ID1], [t0].[AccountId] AS [AccountId0], [t0].[AccountTypeId] AS [AccountTypeId0], [t0].[AccreditationStatusID] AS [AccreditationStatusID0], [t0].[AccreditationVerificationStatusID] AS [AccreditationVerificationStatusID0], [t0].[ReferenceNumber] AS [ReferenceNumber0], [t0].[StatusID] AS [StatusID0], [t0].[SubmittedDate] AS [SubmittedDate0], [t0].[SuitabilityIsReviewed] AS [SuitabilityIsReviewed0], [t0].[TypeID] AS [TypeID0], [t2].[Created] AS [Created2]
    FROM [Webuser] AS [w]
    LEFT JOIN (
        SELECT [m].[ID], [m].[AccountTypeId], [m].[AccreditationStatusID], [m].[AccreditationVerificationStatusID], [m].[ReferenceNumber], [m].[StatusID], [m].[SubmittedDate], [m].[TypeID], [m].[WebuserID]
        FROM [MembershipApplication] AS [m]
        WHERE (@__ef_filter__IsTenantGlobalOverride_2 = CAST(1 AS bit)) OR ([m].[TenantID] = @__ef_filter__TenantId_3)
    ) AS [t] ON [w].[ID] = [t].[WebuserID]
    LEFT JOIN (
        SELECT [m0].[ID], [m0].[AccountId], [m0].[AccountTypeId], [m0].[AccreditationStatusID], [m0].[AccreditationVerificationStatusID], [m0].[ReferenceNumber], [m0].[StatusID], [m0].[SubmittedDate], [m0].[SuitabilityIsReviewed], [m0].[TypeID]
        FROM [MembershipApplication] AS [m0]
        WHERE (@__ef_filter__IsTenantGlobalOverride_2 = CAST(1 AS bit)) OR ([m0].[TenantID] = @__ef_filter__TenantId_3)
    ) AS [t0] ON (
        SELECT TOP(1) COALESCE([t1].[ParentAccountID], [t1].[ID])
        FROM [WebuserLink] AS [w0]
        INNER JOIN (
            SELECT [a].[ID], [a].[AccountName], [a].[AccountTypeId], [a].[Created], [a].[CreatedByWebuserID], [a].[Modified], [a].[ModifiedByID], [a].[ParentAccountID], [a].[StatusID], [a].[TenantID]
            FROM [Account] AS [a]
            WHERE (@__ef_filter__IsTenantGlobalOverride_6 = CAST(1 AS bit)) OR ([a].[TenantID] = @__ef_filter__TenantId_7)
        ) AS [t1] ON [w0].[AccountID] = [t1].[ID]
        WHERE ((@__ef_filter__IsTenantGlobalOverride_4 = CAST(1 AS bit)) OR ([w0].[TenantID] = @__ef_filter__TenantId_5)) AND ([w0].[WebuserID] = [w].[ID])) = [t0].[AccountId]
    LEFT JOIN (
        SELECT [w1].[Created], [w1].[WebuserID]
        FROM [WebuserSignInSession] AS [w1]
        INNER JOIN (
            SELECT MAX([w2].[ID]) AS [Id]
            FROM [WebuserSignInSession] AS [w2]
            WHERE (@__ef_filter__IsTenantGlobalOverride_8 = CAST(1 AS bit)) OR ([w2].[TenantID] = @__ef_filter__TenantId_9)
            GROUP BY [w2].[WebuserID]
        ) AS [t3] ON [w1].[ID] = [t3].[Id]
        WHERE (@__ef_filter__IsTenantGlobalOverride_8 = CAST(1 AS bit)) OR ([w1].[TenantID] = @__ef_filter__TenantId_9)
    ) AS [t2] ON [w].[ID] = [t2].[WebuserID]
    WHERE ((@__ef_filter__IsTenantGlobalOverride_0 = CAST(1 AS bit)) OR ([w].[TenantID] = @__ef_filter__TenantId_1)) AND [w].[UserType] IN (2, 3)
    ORDER BY (SELECT 1)
    OFFSET @__p_1 ROWS FETCH NEXT @__p_2 ROWS ONLY
) AS [t4]
LEFT JOIN [Webuser_Type] AS [w3] ON [t4].[UserType] = [w3].[Id]
LEFT JOIN [MembershipApplicationType] AS [m1] ON [t4].[TypeID0] = [m1].[ID]
LEFT JOIN [MembershipApplicationType] AS [m2] ON [t4].[TypeID] = [m2].[ID]
LEFT JOIN [MembershipApplication_Status] AS [m3] ON [t4].[StatusID0] = [m3].[ID]
LEFT JOIN [MembershipApplication_Status] AS [m4] ON [t4].[StatusID] = [m4].[ID]
LEFT JOIN [MembershipApplication_Accreditation_Status] AS [m5] ON [t4].[AccreditationStatusID0] = [m5].[ID]
LEFT JOIN [MembershipApplication_Accreditation_Status] AS [m6] ON [t4].[AccreditationStatusID] = [m6].[ID]
LEFT JOIN [MembershipApplication_Accreditation_Verification_Status] AS [m7] ON [t4].[AccreditationVerificationStatusID0] = [m7].[ID]
LEFT JOIN [MembershipApplication_Accreditation_Verification_Status] AS [m8] ON [t4].[AccreditationVerificationStatusID] = [m8].[ID]
LEFT JOIN (
    SELECT [a0].[ID], [a0].[CellPhone]
    FROM [Address] AS [a0]
    WHERE (@__ef_filter__IsTenantGlobalOverride_10 = CAST(1 AS bit)) OR ([a0].[TenantID] = @__ef_filter__TenantId_11)
) AS [t5] ON [t4].[AddressID] = [t5].[ID]
LEFT JOIN (
    SELECT [m9].[InstitutionID], [m9].[MembershipApplicationID]
    FROM [MembershipApplication_InstitutionDetails] AS [m9]
    WHERE (@__ef_filter__IsTenantGlobalOverride_12 = CAST(1 AS bit)) OR ([m9].[TenantID] = @__ef_filter__TenantId_13)
) AS [t6] ON [t4].[ID1] = [t6].[MembershipApplicationID]
LEFT JOIN (
    SELECT [i].[ID], [i].[Name]
    FROM [Institution] AS [i]
    WHERE (@__ef_filter__IsTenantGlobalOverride_14 = CAST(1 AS bit)) OR ([i].[TenantID] = @__ef_filter__TenantId_15)
) AS [t7] ON [t6].[InstitutionID] = [t7].[ID]
LEFT JOIN (
    SELECT [m10].[InstitutionID], [m10].[MembershipApplicationID]
    FROM [MembershipApplication_InstitutionDetails] AS [m10]
    WHERE (@__ef_filter__IsTenantGlobalOverride_12 = CAST(1 AS bit)) OR ([m10].[TenantID] = @__ef_filter__TenantId_13)
) AS [t8] ON [t4].[ID0] = [t8].[MembershipApplicationID]
LEFT JOIN (
    SELECT [i0].[ID], [i0].[Name]
    FROM [Institution] AS [i0]
    WHERE (@__ef_filter__IsTenantGlobalOverride_14 = CAST(1 AS bit)) OR ([i0].[TenantID] = @__ef_filter__TenantId_15)
) AS [t9] ON [t8].[InstitutionID] = [t9].[ID]
LEFT JOIN [AccountType] AS [a1] ON [t4].[AccountTypeId0] = [a1].[ID]
LEFT JOIN [AccountType] AS [a2] ON [t4].[AccountTypeId] = [a2].[ID]
Editor is loading...
Leave a Comment