-- Mapping AbpUserRoles -> TL_SYS_ROLE_MAPPING
ALTER PROCEDURE dbo.TL_SYS_ROLE_MAPPING_Sync
@p_TLNAME varchar(20) = NULL,
@p_USER_LOGIN varchar(20) = NULL
AS
BEGIN TRANSACTION
 
    DECLARE @USER_ID BIGINT,@ROLENAME VARCHAR(50)
    SET @USER_ID =(SELECT ID FROM TL_USER WHERE TLNANME =@p_TLNAME)
    
    IF((SELECT count(*) FROM AbpUserRoles aur WHERE aur.UserId =@USER_ID)=1)
    BEGIN  
        SET @ROLENAME =(SELECT ar.DisplayName FROM (SELECT aur.RoleId FROM AbpUserRoles aur WHERE aur.UserId =@USER_ID) tmp JOIN AbpRoles ar ON tmp.RoleId =ar.Id)
        IF(EXISTS(SELECT 1 FROM TL_USER WHERE TLNANME = @p_TLNAME AND RoleName <>@ROLENAME))
        BEGIN
            UPDATE TL_USER SET RoleName = @ROLENAME WHERE TLNANME = @p_TLNAME
            IF @@Error <> 0 GOTO ABORT
        END
    END


    
   DELETE TL_SYS_ROLE_MAPPING WHERE TLNAME = @p_TLNAME AND IS_MAIN = 0
   INSERT INTO TL_SYS_ROLE_MAPPING (ROLE_OLD, ROLE_NEW, TLNAME, DEP_ID, BRANCH_ID, EFF_DATE, EXP_DATE, NOTES, AUTH_STATUS, RECORD_STATUS, MAKER_ID, CHECKER_ID, IS_MAIN)
   SELECT A.RoleName,C.DisplayName,@p_TLNAME AS TLNAME,A.DEP_ID,A.TLSUBBRID, NULL,NULL,'' AS NOTES,'A' AS AUTH_STATUS, 1 AS RECORD_STATUS,@p_USER_LOGIN AS MAKER_ID,@p_USER_LOGIN AS CHECKER_ID,0 AS IS_MAIN
   FROM (SELECT ID,A.RoleName,A.TLSUBBRID,A.DEP_ID FROM TL_USER A  WHERE TLNANME =@p_TLNAME) A
   JOIN AbpUserRoles B ON B.UserId = A.ID 
   JOIN AbpRoles C ON C.Id=B.RoleId 
--   WHERE C.DisplayName NOT IN (SELECT tsrm.ROLE_NEW FROM TL_SYS_ROLE_MAPPING tsrm WHERE tsrm.IS_MAIN=0 AND tsrm.TLNAME=@p_TLNAME)
   IF @@Error <> 0 GOTO ABORT
    
COMMIT TRANSACTION
SELECT '0' as Result, '' ErrorDesc
RETURN '0'
ABORT:
BEGIN
		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' ErrorDesc
		RETURN '-1'
End