Omni_wizard.db/zen_create_json_prepare.txt

DELIMITER //

CREATE OR REPLACE PROCEDURE zen_create_json_prepare(
  IN p_uuid VARCHAR(100),
  IN p_FunctionTag VARCHAR(100),
  IN p_code_id VARCHAR(100),  -- Wizard Template Id
  IN p_is_extend VARCHAR(1),
  IN p_query_type VARCHAR(1),
  IN p_funcName VARCHAR(100),
  IN p_tblName VARCHAR(100),
  IN p_pk VARCHAR(100),
  IN p_database VARCHAR(100),
  IN p_op_width INT
)
/*
  功能   : amis Wizard 產生 json UI 的欲處理核心程式(通常是非 Fields 的相關獨立設定)
  描述: 本段 sp 主要是
        1.修正原 Wizard 資料表中的預設值
        2.設定一些 button, picker 及預設值的 json 定義
  建立日期 : 2023/12/22
  
  修改歷史
    項目 誰         日期       修改說明
    ==== ========== ========== ================================================
    1    Michael    2023/12/22 根據 v1.0 重構
*/
BEGIN
  DECLARE v_ip VARCHAR(100);
  DECLARE v_db VARCHAR(100);
  DECLARE v_ext_button_U TEXT;
  DECLARE v_ext_button_I TEXT;
  DECLARE v_frequency VARCHAR(100);
  DECLARE v_menuid VARCHAR(100);
  DECLARE v_imax INT;
  DECLARE v_y INT;
  DECLARE v_cnt INT;
  DECLARE v_imin INT;
  DECLARE v_picker_datas TEXT;
  DECLARE v_fieldnames VARCHAR(600);
  DECLARE v_FieldName VARCHAR(100);
  DECLARE v_iscolumnmatch VARCHAR(1);
  DECLARE v_crlf VARCHAR(2);
  DECLARE v_lastsep VARCHAR(1);
  DECLARE v_fielddefault VARCHAR(200);
  DECLARE v_insert_default VARCHAR(800);
  DECLARE v_update_default VARCHAR(800);
  DECLARE v_detail_fields VARCHAR(800);
  DECLARE v_formsize VARCHAR(100);
  DECLARE v_formsize_drawer VARCHAR(100);  -- 專門給 Drawer 用的 Form Size(要配合修改 Template), 但是舊資料就有點麻煩 20230521
    
  -- 通用寫法
  SET v_crlf = CONCAT(CHAR(13), CHAR(10));
  SET v_ip = '${ls:base_ip}';
  SET v_db = '${ls:base_db}';
  
  /*
    待處理, 持續整理中
  */
  INSERT INTO zen_wizard_tmp (uuid,zen_key,zen_value,create_date) VALUES (p_uuid,'is_query_header',CASE WHEN p_query_type = 'C' THEN 'true' ELSE 'false' END,NOW());
  -- 簡單的固定變數也統一寫入暫存
  INSERT INTO zen_wizard_tmp (uuid,zen_key,zen_value,create_date) VALUES (p_uuid,'functionName',p_funcName,NOW());
  INSERT INTO zen_wizard_tmp (uuid,zen_key,zen_value,create_date) VALUES (p_uuid,'TableName',p_tblName,NOW());
  INSERT INTO zen_wizard_tmp (uuid,zen_key,zen_value,create_date) VALUES (p_uuid,'pk',p_pk,NOW());
  INSERT INTO zen_wizard_tmp (uuid,zen_key,zen_value,create_date) VALUES (p_uuid,'database',p_database,NOW());
  INSERT INTO zen_wizard_tmp (uuid,zen_key,zen_value,create_date) VALUES (p_uuid,'ip-port',v_ip,NOW());
  INSERT INTO zen_wizard_tmp (uuid,zen_key,zen_value,create_date) VALUES (p_uuid,'op-width',CAST(p_op_width AS CHAR),NOW()); 
  -- transfer 的 initApi(在 trans 中累加即可)
  INSERT INTO zen_wizard_tmp (uuid,zen_key,zen_value,create_date) VALUES (p_uuid,'init_api_org','',NOW());
  -- 配合 md uuid 的 template 使用   
  INSERT INTO zen_wizard_tmp (uuid,zen_key,zen_value,create_date) VALUES (p_uuid,'TableName:4_6',SUBSTRING(p_tblName,4,6),NOW());
  
  /*
    擴充 button 處理 2022-2-5(可能有多個 button), 分為2個位置
    get data from zen_function_button
  */
  SELECT zen_get_ext_button(p_FunctionTag,'U',v_ip,v_db) INTO v_ext_button_U;
  SELECT zen_get_ext_button(p_FunctionTag,'I',v_ip,v_db) INTO v_ext_button_I;
  
  -- 取得週期性的欄位名稱
  SELECT FieldName INTO v_frequency
  FROM zen_fields WHERE FunctionTag = p_FunctionTag AND fieldObject = 'cycle-frequency';
  
  -- 如果是擴充表單, 權限同主 Form (要加判斷)
  IF p_is_extend = 'Y' THEN
    IF LOCATE('_',p_FunctionTag) > 0 THEN
      SET v_menuid = SUBSTRING(p_FunctionTag,1,LOCATE('_',p_FunctionTag)-1);
    ELSE
      SET v_menuid = p_FunctionTag;
    END IF;
  ELSE
    SET v_menuid = p_FunctionTag;
  END IF;
  
  -- 將傳入的 json 寫入暫存表方便處理
  INSERT INTO zen_wizard_tmp (uuid,zen_key,zen_value,create_date) VALUES (p_uuid,'ext_button_U',v_ext_button_U,NOW());
  INSERT INTO zen_wizard_tmp (uuid,zen_key,zen_value,create_date) VALUES (p_uuid,'ext_button_I',v_ext_button_I,NOW());
  INSERT INTO zen_wizard_tmp (uuid,zen_key,zen_value,create_date) VALUES (p_uuid,'frequency',IFNULL(v_frequency,''),NOW());
  INSERT INTO zen_wizard_tmp (uuid,zen_key,zen_value,create_date) VALUES (p_uuid,'menuid',v_menuid,NOW());
  
  /*
    picker 的 data
  */
  SET v_y = 0;
  SET v_imax = 0;
  SET v_picker_datas = '';
  SET v_lastsep = '';
  SET v_cnt = 0;
  
  SELECT MIN(rowid) INTO v_y FROM zen_wizard_jsondata WHERE uuid = p_uuid;
  SELECT MAX(rowid) INTO v_imax FROM zen_wizard_jsondata WHERE uuid = p_uuid;
  
  WHILE v_y <= v_imax DO
    SELECT FieldName,iscolumnmatch INTO v_FieldName,v_iscolumnmatch 
    FROM zen_wizard_jsondata WHERE uuid = p_uuid AND rowid = v_y;
  
    IF v_y = v_imax THEN 
      SET v_lastsep = ''; 
    ELSE 
      SET v_lastsep = ','; 
    END IF;
    
    -- 組出 @picker_datas@
    IF v_iscolumnmatch = 'Y' THEN
      SET v_picker_datas = CONCAT(v_picker_datas, '                "', v_FieldName, '": "${', v_FieldName, '.', v_FieldName, ' === null | isTrue: ', v_FieldName, ' : ', v_FieldName, '.', v_FieldName, '}"', v_lastsep, v_crlf);
      SET v_cnt = v_cnt + 1;
    ELSE 
      SET v_picker_datas = CONCAT(v_picker_datas, '                "', v_FieldName, '": "${', v_FieldName, '}"', v_lastsep, v_crlf);
    END IF;
    
    SET v_y = v_y + 1;
  END WHILE;
  
  IF v_cnt > 0 THEN
    SET v_picker_datas = CONCAT('            "data": {', v_crlf, 
                      CASE WHEN LOCATE('MasterDetail',p_code_id) > 0 THEN '                "details": "${details}",' ELSE '' END, v_crlf,
                      v_picker_datas, 
                      '            },', v_crlf);
  END IF;
  
  INSERT INTO zen_wizard_tmp (uuid,zen_key,zen_value,create_date) VALUES (p_uuid,'picker_datas',v_picker_datas,NOW());
  
  /*
    處理 default 的 data domain (@insert_default@, @update_default@)
    
    預設值第一碼 Michael 20230909
    @ 目前無任何設定, 我已經忘了原先是要定義甚麼
    $ 表示設定值是  ${ls:userid}
    # 新增, 專門配合 picker 多回傳欄位用, 也要加 ${ls:userid} Michael 20230901
  */
  SET v_insert_default = '';
  SET v_update_default = '';
  SET v_lastsep = '';
  
  -- 處理 $ 開頭的預設值
  SELECT MAX(rowid) INTO v_imax FROM zen_wizard_jsondata 
  WHERE uuid = p_uuid AND IFNULL(fielddefault,'') <> '' AND SUBSTRING(fielddefault,1,1) <> '@' AND LOCATE('$',fielddefault) > 0;
  
  SELECT MIN(rowid) INTO v_imin FROM zen_wizard_jsondata 
  WHERE uuid = p_uuid AND IFNULL(fielddefault,'') <> '' AND SUBSTRING(fielddefault,1,1) <> '@' AND LOCATE('$',fielddefault) > 0;
  
  WHILE v_imin <= v_imax DO
    SELECT fieldname,fielddefault INTO v_FieldName,v_fielddefault 
    FROM zen_wizard_jsondata 
    WHERE uuid = p_uuid AND IFNULL(fielddefault,'') <> '' AND SUBSTRING(fielddefault,1,1) <> '@' AND LOCATE('$',fielddefault) > 0 AND rowid = v_imin;
    
    IF v_imin = v_imax THEN 
      SET v_lastsep = ''; 
    ELSE 
      SET v_lastsep = ','; 
    END IF;
    
    SET v_insert_default = CONCAT(v_insert_default, '"', v_FieldName, '": "${', SUBSTRING(v_fielddefault,2,100), '}"', v_lastsep, v_crlf);
    
    SELECT MIN(rowid) INTO v_imin FROM zen_wizard_jsondata 
    WHERE uuid = p_uuid AND IFNULL(fielddefault,'') <> '' AND SUBSTRING(fielddefault,1,1) <> '@' AND LOCATE('$',fielddefault) > 0 AND rowid > v_imin;
  END WHILE;
  
  IF v_insert_default <> '' THEN
    SET v_insert_default = CONCAT('"data": { ', v_crlf, v_insert_default, ' },');
  END IF;
    
  -- 處理 @ 開頭的預設值
  SELECT MAX(rowid) INTO v_imax FROM zen_wizard_jsondata 
  WHERE uuid = p_uuid AND IFNULL(fielddefault,'') <> '' AND SUBSTRING(fielddefault,1,1) = '@';
  
  SELECT MIN(rowid) INTO v_imin FROM zen_wizard_jsondata 
  WHERE uuid = p_uuid AND IFNULL(fielddefault,'') <> '' AND SUBSTRING(fielddefault,1,1) = '@';
  
  WHILE v_imin <= v_imax DO
    SELECT fieldname,fielddefault INTO v_FieldName,v_fielddefault 
    FROM zen_wizard_jsondata 
    WHERE uuid = p_uuid AND IFNULL(fielddefault,'') <> '' AND SUBSTRING(fielddefault,1,1) = '@' AND rowid = v_imin;
    
    IF v_imin = v_imax THEN 
      SET v_lastsep = ''; 
    ELSE 
      SET v_lastsep = ','; 
    END IF;
    
    SET v_update_default = CONCAT(v_update_default, '"', v_FieldName, '": "${', SUBSTRING(v_fielddefault,3,100), '}"', v_lastsep, v_crlf);
    
    SELECT MIN(rowid) INTO v_imin FROM zen_wizard_jsondata 
    WHERE uuid = p_uuid AND IFNULL(fielddefault,'') <> '' AND SUBSTRING(fielddefault,1,1) = '@' AND rowid > v_imin;
  END WHILE;
  
  IF v_update_default <> '' THEN
    SET v_update_default = CONCAT('"data": { ', v_crlf, v_update_default, ' },');
  END IF;
  
  INSERT INTO zen_wizard_tmp (uuid,zen_key,zen_value,create_date) VALUES (p_uuid,'insert_default',v_insert_default,NOW());
  INSERT INTO zen_wizard_tmp (uuid,zen_key,zen_value,create_date) VALUES (p_uuid,'update_default',v_update_default,NOW());
  
  /*
    以下是幾個一般設定, 就不獨立開 proc 寫 code 了
  */    
  SELECT COUNT(*) INTO v_imax FROM zen_wizard_jsondata WHERE uuid = p_uuid;
  -- form size (xs,sm,md,lg,xl,full)  Dialog 才有 full
  -- drawer 抽屉尺寸((xs,sm,md,lg,xl)
  IF v_imax <= 6 THEN
    SET v_formsize = 'md';
    SET v_formsize_drawer = 'md';
  ELSEIF v_imax < 25 THEN
    SET v_formsize = 'lg';
    SET v_formsize_drawer = 'lg';
  ELSE
    SET v_formsize = 'full';
    SET v_formsize_drawer = 'xl';
  END IF;
  
  INSERT INTO zen_wizard_tmp (uuid,zen_key,zen_value,create_date) VALUES (p_uuid,'formsize',v_formsize,NOW());
  INSERT INTO zen_wizard_tmp (uuid,zen_key,zen_value,create_date) VALUES (p_uuid,'formsize_drawer',v_formsize_drawer,NOW());
END //

DELIMITER ;