74 lines
2.6 KiB
MySQL
74 lines
2.6 KiB
MySQL
|
DELIMITER $$
|
|||
|
|
|
|||
|
|
CREATE PROCEDURE `sp_get_user_status_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
|
|||
|
|
)
|
|||
|
|
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;
|
|||
|
|
|
|||
|
|
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 'email' THEN CONCAT('ORDER BY u.email_address ', p_sort_direction)
|
|||
|
|
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.user_name LIKE @p_search_like OR u.email_address 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;
|
|||
|
|
|
|||
|
|
SET v_sql = CONCAT(
|
|||
|
|
'SELECT SQL_CALC_FOUND_ROWS ',
|
|||
|
|
' u.user_id, ',
|
|||
|
|
' CASE WHEN u.user_name IS NOT NULL AND u.email_address IS NOT NULL THEN CONCAT(u.user_name, \' [\', LOWER(SUBSTRING_INDEX(u.email_address, \'@\', 1)), \']\') WHEN u.user_name IS NOT NULL THEN u.user_name ELSE u.email_address END AS user_display_name, ',
|
|||
|
|
' u.email_address, ',
|
|||
|
|
' 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.user_name, u.email_address, g.group_name ',
|
|||
|
|
v_order_by_clause,
|
|||
|
|
' LIMIT ? OFFSET ?'
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
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 ;
|