67 lines
3.2 KiB
SQL
67 lines
3.2 KiB
SQL
DELIMITER $$
|
|
|
|
CREATE PROCEDURE `sp_get_all_resources`(
|
|
IN p_page_num INT,
|
|
IN p_page_size INT,
|
|
IN p_sort_column VARCHAR(50),
|
|
IN p_sort_direction VARCHAR(4),
|
|
IN p_category_id TINYINT UNSIGNED,
|
|
IN p_group_id BIGINT,
|
|
IN p_user_id BIGINT
|
|
)
|
|
COMMENT '전체 자산 목록 조회 (페이징, 정렬, 필터링)'
|
|
BEGIN
|
|
DECLARE v_offset INT;
|
|
DECLARE v_order_by_clause VARCHAR(255);
|
|
DECLARE v_sql TEXT;
|
|
|
|
-- 입력값 기본 처리
|
|
IF UPPER(p_sort_direction) != 'ASC' THEN SET p_sort_direction = 'DESC'; 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 'id' THEN CONCAT('ORDER BY r.resource_id ', p_sort_direction)
|
|
WHEN 'name' THEN CONCAT('ORDER BY r.resource_name ', p_sort_direction)
|
|
WHEN 'category' THEN CONCAT('ORDER BY rc.category_name ', p_sort_direction)
|
|
WHEN 'code' THEN CONCAT('ORDER BY r.resource_code ', p_sort_direction)
|
|
WHEN 'user' THEN CONCAT('ORDER BY user_display_name ', p_sort_direction)
|
|
WHEN 'group' THEN CONCAT('ORDER BY g.group_name ', p_sort_direction)
|
|
WHEN 'serial' THEN CONCAT('ORDER BY r.serial_num ', p_sort_direction)
|
|
WHEN 'purchased' THEN CONCAT('ORDER BY r.purchase_date ', p_sort_direction) -- 정렬 기준 변경: created -> purchased
|
|
WHEN 'registered' THEN CONCAT('ORDER BY r.register_date ', p_sort_direction) -- 정렬 기준 추가: registered
|
|
WHEN 'updated' THEN CONCAT('ORDER BY r.update_date ', p_sort_direction)
|
|
WHEN 'is_locked' THEN CONCAT('ORDER BY r.is_locked ', p_sort_direction)
|
|
ELSE 'ORDER BY r.resource_id DESC'
|
|
END;
|
|
IF p_sort_column != 'id' THEN SET v_order_by_clause = CONCAT(v_order_by_clause, ', r.resource_id DESC'); END IF;
|
|
|
|
-- 동적 SQL 생성
|
|
SET v_sql = CONCAT(
|
|
'SELECT SQL_CALC_FOUND_ROWS ',
|
|
' r.resource_id, r.category_id, rc.category_name, r.resource_code, r.manufacturer, ',
|
|
' r.resource_name, r.serial_num, r.spec_value, r.spec_unit, r.user_id, ',
|
|
' CASE WHEN u.user_id IS NOT NULL AND 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, ', -- 사용자 표시 이름 로직 변경
|
|
' g.group_name, r.comments, r.purchase_date, r.register_date, r.update_date, r.is_locked ', -- 컬럼명 변경 반영
|
|
'FROM resource_info r ',
|
|
' LEFT JOIN resource_category rc ON r.category_id = rc.category_id ',
|
|
' LEFT JOIN user_info u ON r.user_id = u.user_id ',
|
|
' LEFT JOIN group_info g ON u.group_id = g.group_id ',
|
|
'WHERE (? IS NULL OR r.category_id = ?) ',
|
|
' AND (? IS NULL OR g.group_id = ?) ',
|
|
' AND (? IS NULL OR r.user_id = ?) ',
|
|
v_order_by_clause,
|
|
' LIMIT ? OFFSET ?'
|
|
);
|
|
|
|
-- SQL 실행
|
|
PREPARE stmt FROM v_sql;
|
|
EXECUTE stmt USING p_category_id, p_category_id, p_group_id, p_group_id, p_user_id, p_user_id, p_page_size, v_offset;
|
|
SELECT FOUND_ROWS() AS total_count;
|
|
DEALLOCATE PREPARE stmt;
|
|
END
|
|
$$
|
|
|
|
DELIMITER ; |