Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE OR ALTER FUNCTION [dbo].[FilterByDivision]
- (@UserId INT, @PermissionFrom DATETIME, @PermissionTo DATETIME, @Permission NVARCHAR(20))
- RETURNS TABLE
- AS
- RETURN
- SELECT
- div.DivisionID FROM dbo.SCH_Division div
- JOIN dbo.SCH_Standard std ON std.StandardID = div.StandardID
- JOIN dbo.SCH_Section sec ON sec.SectionID = std.SectionID
- JOIN dbo.SCH_School sch ON sch.SchoolID = sec.SchoolID
- JOIN dbo.SCH_Campus cam ON cam.CampusID = sch.CampusID
- JOIN dbo.SCH_Trust tr ON tr.TrustID = cam.TrustID
- JOIN SEC_UserRole sur ON sur.UserId = @UserId
- AND (sur.PermissionFrom >= @PermissionFrom
- OR ((sur.PermissionTo IS NULL)
- OR (sur.PermissionTo IS NOT NULL
- AND @PermissionTo <= sur.PermissionTo)))
- AND (((sur.TrustId = tr.TrustID AND sur.CampusId IS NULL AND sur.SchoolId IS NULL AND sur.SectionId IS NULL AND sur.StandardId IS NULL AND sur.DivisionId IS NULL)
- OR (sur.CampusId = cam.CampusID AND sur.SchoolId IS NULL AND sur.SectionId IS NULL AND sur.StandardId IS NULL AND sur.DivisionId IS NULL)
- OR (sur.SchoolId = sch.SchoolID AND sur.SectionId IS NULL AND sur.StandardId IS NULL AND sur.DivisionId IS NULL)
- OR (sur.SectionId = sec.SectionID AND sur.StandardId IS NULL AND sur.DivisionId IS NULL)
- OR (sur.StandardId = std.StandardID AND sur.DivisionId IS NULL)
- OR (sur.DivisionId = div.DivisionID)))
- JOIN SEC_RoleClaim src ON src.ClaimValue = @Permission AND src.RoleId = sur.RoleId
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
