EOFFICE - GD4

ALTER INDEX ix_CM_REQUEST_TEMPLATECIndexBef
ON dbo.CM_REQUEST_TEMPLATE
REBUILD WITH (FILLFACTOR = 80, STATISTICS_NORECOMPUTE = ON);
ALTER INDEX PK_CM_REQUEST_TEMPLATE
ON dbo.CM_REQUEST_TEMPLATE
REBUILD WITH (FILLFACTOR = 80, STATISTICS_NORECOMPUTE = ON);

SELECT
i.name AS index_name,
i.type_desc AS index_type,
i.is_unique,
i.is_primary_key,
c.name AS column_name
FROM
sys.indexes i
JOIN
sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
JOIN
sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
JOIN
sys.tables t ON i.object_id = t.object_id
WHERE
t.name = 'CM_REQUEST_TEMPLATE';

ALTER PROCEDURE dbo.TL_USER_KSV_XL_Search
@p_TLID varchar(15) = NULL,
@p_TLNANME varchar(15) = NULL,
@p_Password varchar(50) = NULL,
@p_TLFullName nvarchar(200) = NULL,
@p_TLSUBBRID varchar(15) = NULL,
@p_BRANCH_LOGIN varchar(15) = NULL,

@p_BRANCH_ID varchar(15) = NULL,
@p_BRANCH_NAME nvarchar(200) = NULL,
@p_BRANCH_TYPE varchar(5) = NULL,
@p_RoleName varchar(20) = NULL,
@p_EMAIL varchar(50) = NULL,
@p_ADDRESS varchar(100) = NULL,
@p_PHONE varchar(15) = NULL,
@p_AUTH_STATUS varchar(1) = NULL,
@p_MARKER_ID varchar(12) = NULL,
@p_AUTH_ID varchar(12) = NULL,
@p_APPROVE_DT VARCHAR = NULL,
@p_ISAPPROVE varchar(1) = NULL,
@p_Birthday VARCHAR = NULL,
@p_ISFIRSTTIME varchar(1) = NULL,
@p_SECUR_CODE varchar(50) = NULL,
@p_DEP_ID varchar(15) = NULL,
@p_TYPE VARCHAR = NULL,
@p_TOP INT = 10,
@P_LEVEL varchar(10) = NULL
AS

BEGIN -- PAGING

DECLARE @MENU_PERMISSION NVARCHAR(500) = 'Pages.Administration.HubTransfer' --permission của chức năng call store
    -- LẤY RA USER MẶC ĐỊNH

IF
-- PAGING BEGIN
SELECT A.ID, A.TLNANME, A.TLFullName, A.SECUR_CODE, A.TLSUBBRID
FROM TL_USER A
WHERE
(EXISTS (
SELECT 1
FROM FN_GET_USER_BY_ROLE('NVTT,TBP,GDDV,QLK',NULL,NULL, @MENU_PERMISSION) R
WHERE A.TLNANME=R.TLNANME
AND (
-- USER ĐĂNG NHẬP ĐANG LÀ MẶC ĐỊNH
(((R.BRANCH_ID=@p_BRANCH_ID AND R.DEP_ID = @p_DEP_ID) AND R.BRANCH_ID = 'DV0001')
OR
((R.BRANCH_ID=@p_BRANCH_ID OR R.DEP_ID = @p_DEP_ID) AND R.BRANCH_ID <> 'DV0001'))
-- USER ĐĂNG NHẬP ĐANG LÀ ỦY QUYỀN
OR EXISTS (
SELECT 1 FROM TL_USER_GET_ROLES(@p_TLNANME, @MENU_PERMISSION) R1
WHERE ((R.BRANCH_ID=R1.BRANCH_ID AND R.DEP_ID=R1.DEPT_ID AND R.BRANCH_ID = 'DV0001')
OR
(((R.BRANCH_ID=R1.BRANCH_ID OR R.DEP_ID=R1.DEPT_ID) AND R.BRANCH_ID <> 'DV0001'))
)
)
)
AND (
(@p_TYPE='XL' AND R.ROLE_NEW IN ('NVTT','QLK'))
OR (@p_TYPE='KS' AND R.ROLE_NEW IN ('GDDV', 'TBP'))
)
)) AND A.RoleName IS NOT NULL AND A.IsActive = '1' AND A.AUTH_STATUS = 'A' AND A.RoleName <> 'DISABLE'
-- PAGING END
ELSE

-- PAGING BEGIN
SELECT TOP) A.ID, A.TLNANME, A.TLFullName, A.SECUR_CODE, A.TLSUBBRID
FROM TL_USER A
WHERE
(EXISTS (
SELECT 1
FROM FN_GET_USER_BY_ROLE('NVTT,TBP,GDDV,QLK',NULL,NULL, @MENU_PERMISSION) R
WHERE A.TLNANME=R.TLNANME
AND (
-- USER ĐĂNG NHẬP ĐANG LÀ MẶC ĐỊNH
(((R.BRANCH_ID=@p_BRANCH_ID AND R.DEP_ID = @p_DEP_ID) AND R.BRANCH_ID = 'DV0001')
OR
((R.BRANCH_ID=@p_BRANCH_ID OR R.DEP_ID = @p_DEP_ID) AND R.BRANCH_ID <> 'DV0001'))
-- USER ĐĂNG NHẬP ĐANG LÀ ỦY QUYỀN
OR EXISTS (
SELECT 1 FROM TL_USER_GET_ROLES(@p_TLNANME, @MENU_PERMISSION) R1
WHERE ((R.BRANCH_ID=R1.BRANCH_ID AND R.DEP_ID=R1.DEPT_ID AND R.BRANCH_ID = 'DV0001')
OR
(((R.BRANCH_ID=R1.BRANCH_ID OR R.DEP_ID=R1.DEPT_ID) AND R.BRANCH_ID <> 'DV0001'))
)
)
)
AND (
(@p_TYPE='XL' AND R.ROLE_NEW IN ('NVTT','QLK'))
OR (@p_TYPE='KS' AND R.ROLE_NEW IN ('GDDV', 'TBP'))
)
)) AND A.RoleName IS NOT NULL AND A.IsActive = '1' AND A.AUTH_STATUS = 'A' AND A.RoleName <> 'DISABLE'
-- PAGING END

END -- PAGING

DECLARE @REQ_ID VARCHAR
SELECT @REQ_ID = REQ_ID FROM TR_rEQUEST_DOC WHERE REq_CODE = 'PUR/2024/000688'
UPDATE TR_REQUEST_DOC SET PROCESS_ID = 'APPROVE' WHERE REQ_ID = @REQ_ID
UPDATE PL_rEQUEST_PROCESS SET STATUS = 'p' WHERE REQ_ID = @REQ_ID AND PROCESS_ID = 'DMMS'
UPDATE PL_rEQUEST_PROCESS SET STATUS = 'C' WHERE REQ_ID = @REQ_ID AND PROCESS_ID = 'APPROVE'
UPDATE PL_REQUEST_PROCESS_CHILD SET STATUS_JOB = 'P' WHERE REQ_ID = @REQ_ID AND TYPE_JOB = 'XL'
--11102024_secretkey