80 lines
3.2 KiB
SQL
80 lines
3.2 KiB
SQL
DELIMITER $$
|
|
|
|
SET @saved_sql_mode = @@sql_mode
|
|
$$
|
|
SET @@sql_mode = 'NO_AUTO_VALUE_ON_ZERO'
|
|
$$
|
|
CREATE PROCEDURE `sp_get_user_list`(
|
|
IN p_search_term VARCHAR(100),
|
|
IN p_page_num INT,
|
|
IN p_page_size INT,
|
|
IN p_sort_column VARCHAR(50),
|
|
IN p_sort_direction VARCHAR(4),
|
|
IN p_group_id BIGINT
|
|
)
|
|
COMMENT '사용자 목록 조회 (검색, 페이징, 정렬, 필터링, 자산 수 포함)'
|
|
BEGIN
|
|
DECLARE v_offset INT;
|
|
DECLARE v_order_by_clause VARCHAR(255);
|
|
DECLARE v_where_clause TEXT;
|
|
DECLARE v_sql TEXT;
|
|
SET @p_search_like = NULL; -- 세션 변수 사용 유지 (Prepared Statement 제약)
|
|
|
|
-- 입력값 기본 처리
|
|
IF p_search_term IS NOT NULL AND p_search_term != '' THEN SET @p_search_like = CONCAT('%', p_search_term, '%'); END IF;
|
|
IF UPPER(p_sort_direction) NOT IN ('ASC', 'DESC') THEN SET p_sort_direction = 'ASC'; END IF;
|
|
IF p_page_num IS NULL OR p_page_num < 1 THEN SET p_page_num = 1; END IF;
|
|
IF p_page_size IS NULL OR p_page_size < 1 THEN SET p_page_size = 20; END IF;
|
|
SET v_offset = (p_page_num - 1) * p_page_size;
|
|
|
|
-- 정렬 기준 설정
|
|
SET v_order_by_clause = CASE p_sort_column
|
|
WHEN 'name' THEN CONCAT('ORDER BY user_display_name ', p_sort_direction)
|
|
WHEN 'account' THEN CONCAT('ORDER BY u.account_name ', p_sort_direction) -- 정렬 기준 변경: email -> account
|
|
WHEN 'group' THEN CONCAT('ORDER BY g.group_name ', p_sort_direction)
|
|
WHEN 'assets' THEN CONCAT('ORDER BY assigned_asset_count ', p_sort_direction)
|
|
ELSE 'ORDER BY user_display_name ASC'
|
|
END;
|
|
IF p_sort_column != 'name' THEN SET v_order_by_clause = CONCAT(v_order_by_clause, ', u.user_id ASC'); END IF;
|
|
|
|
-- 검색 조건 설정
|
|
SET v_where_clause = 'WHERE 1=1 ';
|
|
IF @p_search_like IS NOT NULL THEN
|
|
SET v_where_clause = CONCAT(v_where_clause, 'AND (u.display_name LIKE @p_search_like OR u.account_name LIKE @p_search_like OR g.group_name LIKE @p_search_like) '); -- 검색 컬럼 변경
|
|
END IF;
|
|
IF p_group_id IS NOT NULL THEN
|
|
SET v_where_clause = CONCAT(v_where_clause, 'AND u.group_id = ? ');
|
|
END IF;
|
|
|
|
-- 동적 SQL 생성
|
|
SET v_sql = CONCAT(
|
|
'SELECT SQL_CALC_FOUND_ROWS ',
|
|
' u.user_id, ',
|
|
' CASE WHEN u.display_name IS NOT NULL AND u.display_name != \'\' AND u.account_name IS NOT NULL THEN CONCAT(u.display_name, \' [\', u.account_name, \']\') WHEN u.display_name IS NOT NULL AND u.display_name != \'\' THEN u.display_name ELSE u.account_name END AS user_display_name, ', -- 표시 이름 로직 변경
|
|
' u.account_name, ', -- 컬럼명 변경
|
|
' g.group_name, ',
|
|
' COUNT(r.resource_id) AS assigned_asset_count ',
|
|
'FROM user_info u ',
|
|
' LEFT JOIN group_info g ON u.group_id = g.group_id ',
|
|
' LEFT JOIN resource_info r ON u.user_id = r.user_id ',
|
|
v_where_clause,
|
|
'GROUP BY u.user_id, u.display_name, u.account_name, g.group_name ', -- GROUP BY 컬럼 변경
|
|
v_order_by_clause,
|
|
' LIMIT ? OFFSET ?'
|
|
);
|
|
|
|
-- SQL 실행
|
|
PREPARE stmt FROM v_sql;
|
|
IF p_group_id IS NOT NULL THEN
|
|
EXECUTE stmt USING p_group_id, p_page_size, v_offset;
|
|
ELSE
|
|
EXECUTE stmt USING p_page_size, v_offset;
|
|
END IF;
|
|
|
|
-- 전체 개수 반환
|
|
SELECT FOUND_ROWS() AS total_count;
|
|
DEALLOCATE PREPARE stmt;
|
|
END
|
|
$$
|
|
|
|
DELIMITER ; |