ALTER PROCEDURE [dbo].[TL_USER_Sync]
@p_ACTION	varchar(15)  = NULL,
@p_USERNAME	varchar(50)  = NULL,
@p_FULLNAME	nvarchar(500)  = NULL,
@p_BRANCH_CODE	varchar(15)  = NULL,
@p_DEP_CODE	varchar(15)  = NULL,
@p_EMAIL	varchar(150)  = NULL,
@p_ROLE	varchar(50)  = NULL
AS
--Validation is here
DECLARE @ERRORSYS NVARCHAR(15) = '' 
DECLARE @p_BRANCH_NAME NVARCHAR(500) =''
DECLARE @p_BRANCH_TYPE NVARCHAR(50) =''
DECLARE @p_BRANCH_ID NVARCHAR(50) =''
DECLARE @p_DEP_ID NVARCHAR(50) =''
DECLARE @L_TLID VARCHAR(15)
SET @p_BRANCH_NAME = (SELECT TOP 1 A.BRANCH_NAME FROM CM_BRANCH A WHERE A.BRANCH_CODE = @p_BRANCH_CODE)
SET @p_BRANCH_TYPE = (SELECT TOP 1 A.BRANCH_TYPE FROM CM_BRANCH A WHERE A.BRANCH_CODE = @p_BRANCH_CODE)
SET @p_BRANCH_ID =   (SELECT TOP 1 A.BRANCH_ID FROM CM_BRANCH A WHERE A.BRANCH_CODE = @p_BRANCH_CODE)
SET @p_DEP_ID      = (SELECT TOP 1 A.DEP_ID FROM CM_DEPARTMENT A WHERE A.DEP_CODE = @p_DEP_CODE AND BRANCH_ID =@p_BRANCH_ID)
BEGIN TRANSACTION
		IF(@p_ACTION ='I' OR @p_ACTION ='U')
		BEGIN
			--KIEM TRA MA NHAN VIEN KHONG DUOC BO TRONG
			IF (@p_USERNAME='' OR @p_USERNAME IS NULL)
			BEGIN
				ROLLBACK TRANSACTION
				SELECT '-1' Result, ''  EMP_ID, N'User Name không được bỏ trống' ErrorDesc
				RETURN '-1'
			END
			--KIEM TRA MA NHAN VIEN KHONG DUOC BO TRONG
			IF (@p_FULLNAME='' OR @p_FULLNAME IS NULL)
			BEGIN
				ROLLBACK TRANSACTION
				SELECT '-1' Result, ''  EMP_ID, N'Tên nhân viên không được bỏ trống' ErrorDesc
				RETURN '-1'
			END
			--KIEM TRA DON VI NAY CO TON TAI HAY KHONG
			IF (@p_BRANCH_CODE='' OR @p_BRANCH_CODE IS NULL)
			BEGIN
				ROLLBACK TRANSACTION
				SELECT '-1' Result, ''  EMP_ID, N'Mã đơn vị không được bỏ trống' ErrorDesc
				RETURN '-1'
			END
			---------------------------------------------------------------------------------
			--KIEM TRA PHONG BAN NAY CO TON TAI HAY KHONG
			IF (@p_EMAIL='' OR @p_EMAIL IS NULL)
			BEGIN
				ROLLBACK TRANSACTION
				SELECT '-1' Result, ''  EMP_ID, N'Email không được bỏ trống' ErrorDesc
				RETURN '-1'
			END
			--KIEM TRA DON VI NAY CO TON TAI HAY KHONG
			IF (@p_ROLE='' OR @p_ROLE IS NULL)
			BEGIN
				ROLLBACK TRANSACTION
				SELECT '-1' Result, ''  EMP_ID, N'Role không được bỏ trống' ErrorDesc
				RETURN '-1'
			END
			---------------------------------------------------------------------------------
			--KIEM TRA PHONG BAN NAY CO TON TAI HAY KHONG
			IF (@p_DEP_CODE='' OR @p_DEP_CODE IS NULL)
			BEGIN
				ROLLBACK TRANSACTION
				SELECT '-1' Result, ''  EMP_ID, N'Mã phòng ban không được bỏ trống' ErrorDesc
				RETURN '-1'
			END
			---------------------------------------------------------------------------------
		END
		IF(@p_ACTION='I')
		BEGIN
			--KIEM TRA XEM USER CO BI TRUNG HAY CHUA
			IF ( EXISTS ( SELECT * FROM TL_USER WHERE [TLNANME] = @p_USERNAME))
			BEGIN
			SET @ERRORSYS = 'USER-0001'
				ROLLBACK TRANSACTION
				SELECT ErrorCode Result, '' TLID, ErrorDesc ErrorDesc FROM SYS_ERROR WHERE ErrorCode = @ERRORSYS
				RETURN '-1'
			END
			--KIEM TRA DON VI NAY CO TON TAI HAY KHONG
			IF (NOT EXISTS ( SELECT * FROM CM_BRANCH WHERE BRANCH_CODE =@p_BRANCH_CODE))
			BEGIN
				ROLLBACK TRANSACTION
				SELECT '-1' Result, ''  EMP_ID, N'Mã đơn vị này không tồn tại' ErrorDesc
				RETURN '-1'
			END
			 --KIEM TRA PHONG BAN NAY CO TON TAI HAY KHONG
			IF (NOT EXISTS ( SELECT * FROM CM_DEPARTMENT WHERE DEP_CODE =@p_DEP_CODE))
			BEGIN
				ROLLBACK TRANSACTION
				SELECT '-1' Result, ''  EMP_ID, N'Mã phòng ban này không tồn tại' ErrorDesc
				RETURN '-1'
			END
			--KIEM TRA PHONG BAN CO THUOC VE DUNG DON VI CUA NO HAY KHONG
			IF (NOT EXISTS ( SELECT * FROM CM_DEPARTMENT WHERE BRANCH_ID =@p_BRANCH_ID AND DEP_CODE=@p_DEP_CODE))
			BEGIN
				ROLLBACK TRANSACTION
				SELECT '-1' Result, ''  EMP_ID, N'Phòng ban có mã '+@p_DEP_CODE+ N' không thuộc về đơn vị '+@p_BRANCH_CODE ErrorDesc
				RETURN '-1'
			END
			--KIEM TRA ROLE CO HOP LE HAY KHONG
			IF (NOT EXISTS ( SELECT * FROM TL_SYSROLE WHERE ROLE_ID =@p_ROLE))
			BEGIN
				ROLLBACK TRANSACTION
				SELECT '-1' Result, ''  EMP_ID, N'Role không tồn tại'+@p_ROLE ErrorDesc
				RETURN '-1'
			END
			---------------------------------------------------------------------------------
			EXEC SYS_CodeMasters_Gen 'TL_USER', @l_TLID out
			IF @l_TLID='' OR @l_TLID IS NULL GOTO ABORT
			INSERT INTO TL_USER([TLID],[TLNANME],[Password],[TLFullName],[TLSUBBRID],[BRANCH_NAME],[BRANCH_TYPE],[RoleName],[EMAIL],[ADDRESS],[PHONE],[AUTH_STATUS],[MARKER_ID],[AUTH_ID],[APPROVE_DT],[ISAPPROVE],[Birthday],[ISFIRSTTIME],[SECUR_CODE])
			VALUES(@L_TLID ,@p_USERNAME ,NULL ,@p_FULLNAME ,@p_BRANCH_ID ,@p_BRANCH_NAME ,@p_BRANCH_TYPE ,@p_ROLE ,@p_EMAIL ,NULL ,NULL ,'A' ,NULL ,NULL ,CONVERT(DATETIME, GETDATE(), 103) ,NULL ,NULL ,NULL ,@p_DEP_ID )
		END
		ELSE IF(@p_ACTION='U')
		BEGIN
			SET @L_TLID = (SELECT TLID FROM TL_USER WHERE TLNANME=@p_USERNAME)
			--KIEM TRA USER NAY CO TON TAI HAY CHUA
			IF (NOT EXISTS ( SELECT * FROM TL_USER WHERE TLID = @L_TLID))
				SET @ERRORSYS = 'USER-0002'
			--KIEM TRA XEM CO TON TAI VIEC CUNG USER_NAME NHUNG LAI CO 2 USER_ID KHAC NHAU
			IF ( EXISTS ( SELECT * FROM TL_USER WHERE [TLNANME] = @p_USERNAME AND TLID <> @L_TLID))
			  BEGIN
				 SET @ERRORSYS = 'USER-0001'
				 SELECT ErrorCode Result, '' TLID, ErrorDesc ErrorDesc FROM SYS_ERROR WHERE ErrorCode = @ERRORSYS
				 RETURN '-1'
			  END
			IF @ERRORSYS <> '' 
			BEGIN
				ROLLBACK TRANSACTION
				SELECT ErrorCode Result, ''  TLID, ErrorDesc ErrorDesc FROM SYS_ERROR WHERE ErrorCode = @ERRORSYS
				RETURN '-1'
			END
			--KIEM TRA DON VI NAY CO TON TAI HAY KHONG
			IF (NOT EXISTS ( SELECT * FROM CM_BRANCH WHERE BRANCH_CODE =@p_BRANCH_CODE))
			BEGIN
				ROLLBACK TRANSACTION
				SELECT '-1' Result, ''  EMP_ID, N'Mã đơn vị này không tồn tại' ErrorDesc
				RETURN '-1'
			END
			--KIEM TRA PHONG BAN NAY CO TON TAI HAY KHONG
			IF (NOT EXISTS ( SELECT * FROM CM_DEPARTMENT WHERE DEP_CODE =@p_DEP_CODE))
			BEGIN
				ROLLBACK TRANSACTION
				SELECT '-1' Result, ''  EMP_ID, N'Mã phòng ban này không tồn tại' ErrorDesc
				RETURN '-1'
			END
			--KIEM TRA PHONG BAN CO THUOC VE DUNG DON VI CUA NO HAY KHONG
			IF (NOT EXISTS ( SELECT * FROM CM_DEPARTMENT WHERE BRANCH_ID =@p_BRANCH_ID AND DEP_CODE=@p_DEP_CODE))
			BEGIN
				ROLLBACK TRANSACTION
				SELECT '-1' Result, ''  EMP_ID, N'Phòng ban có mã '+@p_DEP_CODE+ N' không thuộc về đơn vị '+@p_BRANCH_CODE ErrorDesc
				RETURN '-1'
			END
			--KIEM TRA ROLE CO HOP LE HAY KHONG
			IF (NOT EXISTS ( SELECT * FROM TL_SYSROLE WHERE ROLE_ID =@p_ROLE))
			BEGIN
				ROLLBACK TRANSACTION
				SELECT '-1' Result, ''  EMP_ID, N'Role không tồn tại'+@p_BRANCH_CODE ErrorDesc
				RETURN '-1'
			END
			---------------------------------------------------------------------------------
				SET @p_BRANCH_NAME = (SELECT BRANCH_NAME FROM CM_BRANCH WHERE BRANCH_ID = @p_BRANCH_CODE)
				UPDATE TL_USER SET 
				[TLSUBBRID] = @p_BRANCH_ID,
				[BRANCH_NAME] = @p_BRANCH_NAME,
				[BRANCH_TYPE] = @p_BRANCH_TYPE,
				TLNANME = @p_USERNAME,
				RoleName=@p_ROLE,
				EMAIL=@p_EMAIL,
				TLFullName =@p_FULLNAME,
				SECUR_CODE=@p_DEP_ID
				WHERE  TLID= @L_TLID
		END
		ELSE IF(@p_ACTION='D')
		BEGIN
				IF ( NOT EXISTS ( SELECT * FROM TL_USER WHERE  TLNANME= @p_USERNAME))
				SET @ERRORSYS = 'USER-0002'
				IF @ERRORSYS <> ''
				BEGIN
					ROLLBACK TRANSACTION
					SELECT ErrorCode Result, ErrorDesc ErrorDesc FROM SYS_ERROR WHERE ErrorCode = @ERRORSYS 
					RETURN '-1'
				END
				IF( (SELECT AUTH_STATUS FROM TL_USER WHERE  TLNANME= @p_USERNAME) = 'A')
				BEGIN
					ROLLBACK TRANSACTION
					SET @ERRORSYS = 'USER-0003'
					SELECT ErrorCode Result, ErrorDesc ErrorDesc FROM SYS_ERROR WHERE ErrorCode = @ERRORSYS 
					RETURN '-1'
				END
				ELSE
				BEGIN
					Delete FROM TL_USER WHERE  TLNANME= @p_USERNAME
				END
		END
		IF @@Error <> 0 GOTO ABORT
COMMIT TRANSACTION
SELECT '0' as Result, @L_TLID  TLID, '' ErrorDesc
RETURN '0'
ABORT:
BEGIN
		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' TLID, '' ErrorDesc
		RETURN '-1'
End