DELIMITER //

CREATE OR REPLACE PROCEDURE get_treedata(
  IN p_type VARCHAR(20),
  IN p_para01 VARCHAR(100)
)
/*
  Function   : 配合 amis 的 treeview, 取得 year-sprint 類的2階資料
               目前的寫法只適用於固定2階, 如果階數不固定, 要找時間修改(bom 展開), 暫時不適用
               擴充 @para, 目前是電子書專用, 如果有 @, @後的就是內文的全文檢索內容
  Description: 可以用在 pbi, 會計科目等左方有 tree-view 的 UX, api 回傳用 6
               新增 @type 參數
               sprint   取得 year-sprint 資料(給 menu 使用)
               basdepartment
               acctitle 取得 類別-科目 資料(大部分都是用這個)
               eipmxx.idno 避免 key 重複導致的錯誤
  Build Date : 2021/10/19
  
  Modify History
    Item Who        Date       Modify Docs
    ==== ========== ========== ================================================
    1    Michael    2021/11/26
    2    Michael    2021/12/31 新增 on lone books
    3    Michael    2022/04/14 新增 sys_aside_setup, aside 改為依設定即可產生 aside 相關 json 設定
    4    Michael    2022/06/19 @type 加上 tag.type 以防止重複, tag 僅供識別用
    5    Michael    2022/07/18 key-value 擴充能支援2層式的 tree(開發中)
    6    Michael    2022/07/27 擴充 @para01 內容, 支援內文查詢過濾
    7    Michael    2023/01/13 將 @para01 第一碼的 bookid 改為傳入 functag
	8    Michael    2023/07/20 改寫為 MariaDB 並最佳化(ChatGPT+DeepSeek)
*/
BEGIN
    DECLARE v_max INT;
    DECLARE v_currid INT;
    DECLARE v_groupid INT;
    DECLARE v_next_groupid INT;
    DECLARE v_datas LONGTEXT;
    DECLARE v_footer VARCHAR(600);
    DECLARE v_deptname VARCHAR(100);
    DECLARE v_empname VARCHAR(100);
    DECLARE v_personid VARCHAR(100);
    DECLARE v_crlf VARCHAR(2);
    DECLARE v_comm VARCHAR(1);
    
    DECLARE v_aside_id VARCHAR(100);
    DECLARE v_group_id VARCHAR(100);
    DECLARE v_group_name VARCHAR(100);
    DECLARE v_row_id VARCHAR(100);
    DECLARE v_row_name VARCHAR(100);
    DECLARE v_group_partitionby VARCHAR(100);
    DECLARE v_group_orderby VARCHAR(100);
    DECLARE v_row_orderby VARCHAR(100);
    DECLARE v_filter_field VARCHAR(100);
    DECLARE v_tablename VARCHAR(100);
    DECLARE v_rel_tablename VARCHAR(100);
    DECLARE v_g_partition1 VARCHAR(100);
    DECLARE v_g_partition2 VARCHAR(100);
    DECLARE v_filter_value VARCHAR(100);
    
    DECLARE v_para01, v_para02 VARCHAR(100);
    
    DECLARE v_pos INT;
    DECLARE v_cnt INT;
    DECLARE v_sql_cmd LONGTEXT;
    
    DECLARE v_guid VARCHAR(100);
    DECLARE v_where_keyvalue VARCHAR(100);
    
    SET v_crlf = CONCAT(CHAR(10), CHAR(13));
    
    -- 創建臨時表來存儲數據
    DROP TEMPORARY TABLE IF EXISTS temp_data;
    CREATE TEMPORARY TABLE temp_data (
      xr_id VARCHAR(100),
      x_id VARCHAR(100),
      xr_name VARCHAR(100),
      x_name VARCHAR(100),
      groupid INT,
      rowid INT
    ) ENGINE=MEMORY;
    
    -- 取得相關設定參數
    SELECT 
      aside_id,
      group_id,
      group_name,
      row_id,
      row_name,
      group_partitionby,
      group_orderby,
      row_orderby,
      filter_field,
      tablename,
      IFNULL(rel_tablename,''),
      filter_value
    INTO
      v_aside_id,
      v_group_id,
      v_group_name,
      v_row_id,
      v_row_name,
      v_group_partitionby,
      v_group_orderby,
      v_row_orderby,
      v_filter_field,
      v_tablename,
      v_rel_tablename,
      v_filter_value
    FROM sys_aside_setup WHERE aside_id = p_type;
    
    -- 防錯
    IF IFNULL(v_group_orderby,'') = '' THEN
        SET v_group_orderby = v_row_id;
    END IF;
    
    IF IFNULL(v_row_orderby,'') = '' THEN
        SET v_row_orderby = v_row_id;
    END IF;
    
    -- 去除 p_type 中的 tag
    SET v_pos = LOCATE('.', p_type);
    IF v_pos > 0 THEN
        SET p_type = SUBSTRING(p_type, v_pos+1);
    END IF;
    
    -- 生成 GUID
    SET v_guid = REPLACE(UUID(), '-', '');
    SET v_where_keyvalue = '';
    
    -- 專門客製 for key-value
    IF v_tablename = 'zen_keyvalue' THEN
        -- 取得是否有 desc, 若有, 記錄起來
        SELECT COUNT(*) INTO v_cnt FROM zen_keyvalue WHERE fieldname = p_type AND IFNULL(keydesc,'') <> '';
        
        SET v_where_keyvalue = CONCAT(' AND fieldname = ', QUOTE(p_type));
    END IF;
    
    -- 行銷類的專案過濾(test ...)
    IF IFNULL(v_filter_field,'') <> '' AND IFNULL(v_filter_value,'') <> '' THEN
        SET v_where_keyvalue = CONCAT(' AND ', v_filter_field, ' = ', QUOTE(v_filter_value));
    END IF;
    
    SET v_sql_cmd = '';
    
    -- 電子書有特殊語法, 其他都統一用法
    IF p_type <> 'classid' THEN
        -- 目前支持 p_type = sprintid 及 departmentid, 日後會隨 sys_aside_setup 的資料內容增加
        
        SET v_g_partition1 = '';
        SET v_g_partition2 = '';
        IF IFNULL(v_group_partitionby,'') <> '' THEN
            SET v_g_partition1 = CONCAT('PARTITION BY ', IF(v_rel_tablename = '', '', 'R.'), v_group_partitionby);
            SET v_g_partition2 = CONCAT(IF(v_rel_tablename = '', '', CONCAT('R.', v_group_partitionby, ',')), '');
        END IF;
        
        IF v_rel_tablename = '' THEN
            -- 如果 key-value 有設定 keydesc, 就展開成2層的 tree
            IF v_cnt > 0 THEN
                SET v_g_partition1 = 'PARTITION BY keydesc';
                SET v_g_partition2 = 'keydesc,';
                
                SET v_sql_cmd = CONCAT(
                    'INSERT INTO sys_aside_temp (aside_guid, xr_id, x_id, xr_name, x_name, groupid, rowid) ',
                    'SELECT ', QUOTE(v_guid), ', ',
                    IF(v_group_id = '*', QUOTE(v_group_id), v_group_id), ', ', v_row_id, ', ',
                    IF(v_group_id = '*', QUOTE(v_group_name), v_group_name), ', ', v_row_name, ', ',
                    'ROW_NUMBER() OVER(', v_g_partition1, ' ORDER BY ', v_group_orderby, ') AS groupid, ',
                    'ROW_NUMBER() OVER(ORDER BY ', v_g_partition2, v_row_orderby, ') AS rowid ',
                    'FROM ', v_tablename, ' M ',
                    'WHERE IFNULL(', v_row_id, ', '''' ) <> ''''', v_where_keyvalue
                );
            ELSE
                SET v_sql_cmd = CONCAT(
                    'INSERT INTO sys_aside_temp (aside_guid, xr_id, x_id, xr_name, x_name, groupid, rowid) ',
                    'SELECT ', QUOTE(v_guid), ', ',
                    IF(v_group_id = '*', QUOTE(v_group_id), v_group_id), ', ', v_row_id, ', ',
                    IF(v_group_id = '*', QUOTE(v_group_name), v_group_name), ', ', v_row_name, ', ',
                    'ROW_NUMBER() OVER(', v_g_partition1, ' ORDER BY ', v_group_orderby, ') AS groupid, ',
                    'ROW_NUMBER() OVER(ORDER BY ', v_g_partition2, v_row_orderby, ') AS rowid ',
                    'FROM ', v_tablename, ' M ',
                    'WHERE IFNULL(', v_row_id, ', '''' ) <> ''''', v_where_keyvalue
                );
            END IF;
        ELSE
            -- 如果有 rel_tablename, 就一定要設定 group_partitionby
            SET v_sql_cmd = CONCAT(
                'INSERT INTO sys_aside_temp (aside_guid, xr_id, x_id, xr_name, x_name, groupid, rowid) ',
                'SELECT ', QUOTE(v_guid), ', ',
                IF(v_group_id = '*', QUOTE(CONCAT('R.', v_group_id)), CONCAT('R.', v_group_id)), ', ', CONCAT('M.', v_row_id), ', ',
                IF(v_group_id = '*', QUOTE(CONCAT('R.', v_group_name)), CONCAT('R.', v_group_name)), ', ', CONCAT('M.', v_row_name), ', ',
                'ROW_NUMBER() OVER(', v_g_partition1, ' ORDER BY ', CONCAT('M.', v_group_orderby), ') AS groupid, ',
                'ROW_NUMBER() OVER(ORDER BY ', v_g_partition2, CONCAT('M.', v_row_orderby), ') AS rowid ',
                'FROM ', v_tablename, ' M ',
                'LEFT JOIN ', v_rel_tablename, ' R ON M.', v_group_partitionby, ' = R.', v_group_partitionby, ' ',
                'WHERE IFNULL(', CONCAT('M.', v_row_id), ', '''' ) <> ''''', v_where_keyvalue
            );
        END IF;
    ELSE
        -- 處理 p_para01/02
        SET v_pos = LOCATE('@', p_para01);
        SET v_para02 = '';
        IF v_pos > 0 THEN
            SET v_para02 = SUBSTRING(p_para01, v_pos+1, 100);
            SET p_para01 = SUBSTRING(p_para01, 1, v_pos-1);
            
            -- 將 p_para01 由 functag 改為 bookid
            SELECT keyid INTO v_para01 FROM zen_keyvalue WHERE fieldname = 'book_mapping_id' AND keyvalue = p_para01;
            IF IFNULL(v_para01, '') = '' THEN
                SET v_para01 = '1';
            END IF;
        END IF;
        
        -- 目前是專 for 電子書(p_type = 'classid')
        IF IFNULL(v_group_partitionby, '') = '' THEN
            SET v_sql_cmd = CONCAT(
                'INSERT INTO sys_aside_temp (aside_guid, xr_id, x_id, xr_name, x_name, groupid, rowid) ',
                'SELECT ', QUOTE(v_guid), ', ',
                'x.', v_group_id, ', x.', v_row_id, ', b.', v_group_name, ', x.', v_group_name, ', groupid, rowid', v_crlf,
                'FROM (', v_crlf,
                '  SELECT ', v_group_id, ', ', v_row_id, ', ', v_group_name, ', ',
                IF(v_group_name <> v_row_name, CONCAT(v_row_name, ', '), ''),
                v_group_id, ' AS p_classid,', v_crlf,
                '    ROW_NUMBER() OVER(ORDER BY ', v_group_orderby, ') AS groupid,', v_crlf,
                '    ROW_NUMBER() OVER(ORDER BY ', v_row_orderby, ') AS rowid', v_crlf,
                '  FROM ', v_tablename, v_crlf,
                '  WHERE IFNULL(', v_group_id, ', '''' ) <> '''' AND ', v_filter_field, ' = ', v_para01, v_crlf,
                IF(v_para02 <> '', CONCAT('  AND book_desc LIKE ', QUOTE(CONCAT('%', v_para02, '%')), v_crlf), ''),
                ') x', v_crlf,
                'LEFT JOIN ', v_tablename, ' b ON x.p_classid = b.', v_row_id, ' AND ', v_filter_field, ' = ', v_para01
            );
        ELSE
            SET v_sql_cmd = CONCAT(
                'INSERT INTO sys_aside_temp (aside_guid, xr_id, x_id, xr_name, x_name, groupid, rowid) ',
                'SELECT ', QUOTE(v_guid), ', ',
                'x.', v_group_id, ', x.', v_row_id, ', b.', v_group_name, ', x.', v_group_name, ', groupid, rowid', v_crlf,
                'FROM (', v_crlf,
                '  SELECT ', v_group_id, ', ', v_row_id, ', ', v_group_name, ', ',
                IF(v_group_name <> v_row_name, CONCAT(v_row_name, ', '), ''),
                v_group_id, ' AS p_classid,', v_crlf,
                '    ROW_NUMBER() OVER(PARTITION BY ', v_group_partitionby, ' ORDER BY ', v_group_orderby, ') AS groupid,', v_crlf,
                '    ROW_NUMBER() OVER(ORDER BY ', v_row_orderby, ') AS rowid', v_crlf,
                '  FROM ', v_tablename, v_crlf,
                '  WHERE IFNULL(', v_group_id, ', '''' ) <> '''' AND ', v_filter_field, ' = ', v_para01, v_crlf,
                IF(v_para02 <> '', CONCAT('  AND book_desc LIKE ', QUOTE(CONCAT('%', v_para02, '%')), v_crlf), ''),
                ') x', v_crlf,
                'LEFT JOIN ', v_tablename, ' b ON x.p_classid = b.', v_row_id, ' AND ', v_filter_field, ' = ', v_para01
            );
        END IF;
    END IF;
    
    -- 執行動態SQL
    SET @sql = v_sql_cmd;
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    
    -- 將資料寫入臨時表
    INSERT INTO temp_data 
    SELECT xr_id, x_id, xr_name, x_name, groupid, rowid 
    FROM sys_aside_temp 
    WHERE aside_guid = v_guid;
    
    DELETE FROM sys_aside_temp WHERE aside_guid = v_guid;
    
    /* 開始 main loop */
    SELECT COUNT(*) INTO v_max FROM temp_data;
    SELECT MIN(rowid) INTO v_currid FROM temp_data;
    
    SET v_datas = '';
    SET v_comm = '';
    
    WHILE v_currid <= v_max DO
        -- get data
        SELECT 
            groupid, xr_name, x_id, x_name 
        INTO 
            v_groupid, v_deptname, v_personid, v_empname
        FROM temp_data 
        WHERE rowid = v_currid;
        
        IF v_currid = v_max THEN
            SET v_comm = '';
        ELSE
            SELECT groupid INTO v_next_groupid FROM temp_data WHERE rowid = v_currid + 1;
            IF v_next_groupid = 1 THEN
                SET v_comm = '';
            ELSE
                SET v_comm = ',';
            END IF;
        END IF;
        
        IF v_groupid = 1 THEN
            IF v_currid > 1 THEN
                SET v_footer = CONCAT(
                    '            ]', v_crlf,
                    '          },', v_crlf
                );
            ELSE
                SET v_footer = '';
            END IF;
            
            SET v_datas = CONCAT(
                v_datas, v_footer,
                '        {', v_crlf,
                '            "label": "', v_deptname, '",', v_crlf,
                '            "children": [', v_crlf,
                '               {', v_crlf,
                '                "label": "', v_empname, '",', v_crlf,
                '                "value": "', v_personid, '"', v_crlf,
                '              }', v_comm, v_crlf
            );
        ELSE
            SET v_datas = CONCAT(
                v_datas,
                '               {', v_crlf,
                '                "label": "', v_empname, '",', v_crlf,
                '                "value": "', v_personid, '"', v_crlf,
                '              }', v_comm, v_crlf
            );
        END IF;
        
        SET v_currid = v_currid + 1;
    END WHILE;
    
    SET v_datas = CONCAT(
        '    [', v_crlf,
        v_datas,
        '            ]', v_crlf,
        '        }', v_crlf,
        '    ]'
    );
    
    -- 返回結果
    SELECT 0 AS code, 'ok' AS msg;
    SELECT v_datas AS jsoncode;
    
    -- 清理臨時表
    DROP TEMPORARY TABLE IF EXISTS temp_data;
END //

DELIMITER ;