This repository has been archived on 2025-02-19. You can view files and clone it, but cannot push or open issues or pull requests.
Amen/db/Helpful - Insert - UserRole.sql
2022-09-20 08:40:13 -07:00

67 lines
1.8 KiB
Transact-SQL

/*
select * from ASPNetusers
select * from ASPNetUserRoles
select * from ASPNetRoles
select * from AffiliateAdmin
select u.Id, u.FullName, u.Email, u.AffiliateId, r.Name as RoleName, a.[Key]
from ASPNetUsers u
LEFT JOIN ASPNetUserRoles ur on ur.UserId = u.Id
LEFT JOIN ASPNetRoles r on r.Id = ur.RoleId
LEFT JOIN AffiliateAdmin aa on u.Id = aa.ApplicationUserId
LEFT JOIN Affiliate a on aa.AffiliateId = a.Id
order by u.fullname
update aspnetuserroles set userid = lower(userid), roleid = lower(roleid)
update affiliateadmin set applicationuserid = lower(applicationuserid)
*/
DECLARE @UserEmail varchar(max), @AffiliateKey varchar(max), @RoleName varchar(max), @AffiliateId int, @RoleId uniqueidentifier, @UserId uniqueidentifier
SET @UserEmail = 'smcpeters@brb.org'
SET @AffiliateKey = 'wfgw'
--SET @RoleName = 'Administrator'
SET @RoleName = 'Affiliate Administrator'
SELECT @UserId = Id from ASPNetUsers where Email = @UserEmail
SELECT @RoleId = Id from ASPNetRoles where Name = @RoleName
SELECT @AffiliateId = Id from Affiliate where [Key] = @AffiliateKey
--SELECT LOWER(@UserId), LOWER(@RoleId), @AffiliateId
--RETURN
IF (@UserId is null OR @RoleId is null OR @AffiliateId is null)
BEGIN
PRINT 'Exiting: missing or invalid info'
RETURN
END
IF(@RoleName = 'Affiliate Administrator')
BEGIN
IF NOT EXISTS( SELECT 1
FROM AffiliateAdmin
WHERE AffiliateId = @AffiliateId AND ApplicationUserId = @UserId)
BEGIN
PRINT 'Adding AffiliateAdmin record'
INSERT INTO AffiliateAdmin VALUES(LOWER(@UserId), @AffiliateId, GETUTCDATE())
END
ELSE BEGIN
PRINT 'AffiliateAdmin record exists'
END
END
IF NOT EXISTS( SELECT 1
FROM ASPNetUserRoles
WHERE UserId = @UserId AND RoleId = @RoleId)
BEGIN
PRINT 'Adding UserRole'
INSERT INTO ASPNetUserRoles VALUES(LOWER(@UserId), LOWER(@RoleId))
END
ELSE
BEGIN
PRINT 'UserRole already exists'
END