DELIMITER //

CREATE OR REPLACE FUNCTION zen_picker(
  p_picker_tag VARCHAR(100),  -- 傳入 popup 的資料來源 functiontag
  p_fieldname NVARCHAR(100),  -- 呼叫端的欄位
  p_fieldcname NVARCHAR(100), -- 呼叫端的欄位名稱
  p_actiontype CHAR(2),       -- (R)ead/(E)dit/(Q)uery
  p_ip VARCHAR(100),         -- 'https://api.gex.com.tw:5443'
  p_db VARCHAR(100),         -- 'zenBusiness'
  p_source_tag VARCHAR(100)  -- 原呼叫功能(常需要參考原 tag 的設定)
)
RETURNS TEXT
/*
  function   : amis Wizard 中負責產生 picker 相關程式片段的 udf
  description: 
  build date : 2021/10/10
  
  modify history
    item who        date       modify docs
    ==== ========== ========== ================================================
    1    michael    2021/10/10 新增
    2    Michael    2023-02-01 為了更加彈性, @ip 及 @db 這2個欄位改傳入 ${ls:xxxx}, 測試可行(程式要調整前端呼叫的程式)
    3    Michael    2023-09-01 新增 picker 可以傳回多欄位的設定
    4    Michael    2023-09-25 依 amis 建議 keywords 的變數不要用 -
    5    Michael    2024-01-23 @visibleOn 新增欄位, 配合週期性行事曆, 會設定條件顯示/不顯示本元件
    6    Michael    2024-03-03 修改 labelField 的顯示判斷
*/
BEGIN
  DECLARE v_ret TEXT;
  DECLARE v_dd_table VARCHAR(100);
  DECLARE v_dd_pk VARCHAR(100);
  DECLARE v_dd_functionname VARCHAR(100);
  DECLARE v_dd_name VARCHAR(100);
  DECLARE v_labelField VARCHAR(100);
  DECLARE v_fieldchecknull VARCHAR(10);  -- New
  DECLARE v_dd_fieldname VARCHAR(100);
  DECLARE v_dd_Fieldcn NVARCHAR(100);
  DECLARE v_dd_FieldWidth VARCHAR(10);
  DECLARE v_dd_field_object VARCHAR(100);
  DECLARE v_dd_field_display_name CHAR(1);
  DECLARE v_query_filter VARCHAR(200);
  DECLARE v_query_grid TEXT;
  DECLARE v_date_fmt VARCHAR(100);
  DECLARE v_obj VARCHAR(100);
  DECLARE v_FieldWidth VARCHAR(10);
  DECLARE v_dvalue VARCHAR(100);
  DECLARE v_q_filter VARCHAR(100);
  DECLARE v_q_inputClassName VARCHAR(100); 
  DECLARE v_visibleOn VARCHAR(100);
  DECLARE v_vKey VARCHAR(100);
  DECLARE v_vVal VARCHAR(100);
  DECLARE v_crlf VARCHAR(2);
  DECLARE v_imax INT;
  DECLARE v_inow INT;
  DECLARE v_ipos INT;
  
  -- 與原呼叫端相關(視情況新增)
  DECLARE v_fielddefault NVARCHAR(100);   -- 新增的預設值(若無則空) 
  DECLARE v_iscolumnmatch CHAR(1);        -- 是否傳回多欄位
  DECLARE v_dd_fieldblock INT;
  
  -- 臨時表儲存 JSON 數據
  DROP TEMPORARY TABLE IF EXISTS temp_jsondata_picker;
  CREATE TEMPORARY TABLE temp_jsondata_picker (
    rowid INT AUTO_INCREMENT PRIMARY KEY,
    fieldname VARCHAR(100),
    Fieldcn NVARCHAR(100),
    fieldobject VARCHAR(100),
    field_display_name CHAR(1),
    FieldWidth VARCHAR(10)
  );
  
  -- 取得欄位設定, 多取是否必填
  -- M-D 表單惡裡可能會取到 2筆資料, 要防錯 20250906 (取表頭)
  SELECT fielddefault, iscolumnmatch, fieldblock, FieldWidth, visibleOn, fieldchecknull
  INTO v_fielddefault, v_iscolumnmatch, v_dd_fieldblock, v_FieldWidth, v_visibleOn, v_fieldchecknull
  FROM zen_fields 
  WHERE functiontag = p_source_tag AND fieldname = p_fieldname LIMIT 1;
  -- and fieldblock < 90;
  
  SET v_crlf = CONCAT(CHAR(13), CHAR(10));
  
  -- 防錯(Query)
  IF p_ip = '' THEN SET p_ip = '${ls:base_ip}'; END IF;
  IF p_db = '' THEN SET p_db = '${ls:base_db}'; END IF;
  
  -- 取得資料
  SELECT maintable, mainkey, functionname 
  INTO v_dd_table, v_dd_pk, v_dd_functionname 
  FROM zen_function 
  WHERE functiontag = p_picker_tag;
  
  -- 插入資料到臨時表
  INSERT INTO temp_jsondata_picker (fieldname, Fieldcn, fieldobject, field_display_name, FieldWidth)
  SELECT fieldname, Fieldcn, fieldobject, field_display_name, CAST(FieldWidth AS CHAR) 
  FROM zen_fields 
  WHERE functiontag = p_picker_tag AND fieldds = 'Y' 
  ORDER BY celly, cellx;
  
  -- 計算 _group (body-group) 排列
  SELECT MAX(rowid) INTO v_imax FROM temp_jsondata_picker;
  SELECT MIN(rowid) INTO v_inow FROM temp_jsondata_picker;
  -- SET v_inow = 1;
  SET v_query_filter = '';
  SET v_query_grid = '';
  
  WHILE v_inow <= v_imax DO
    SELECT fieldname, Fieldcn, fieldobject, field_display_name, FieldWidth 
    INTO v_dd_fieldname, v_dd_Fieldcn, v_dd_field_object, v_dd_field_display_name, v_dd_FieldWidth
    FROM temp_jsondata_picker 
    WHERE rowid = v_inow;
    
    IF v_dd_field_display_name = 'Y' THEN 
      SET v_labelField = v_dd_fieldname;
    END IF;
    
    IF v_dd_field_object = 'input-text' THEN
      SET v_query_filter = CONCAT(v_query_filter, v_dd_fieldname, ',');
    END IF;
    
    SET v_obj = 'text';
    SET v_date_fmt = '';  
    
    IF v_dd_field_object = 'input-date' THEN
      SET v_obj = 'static-date';
      SET v_date_fmt = CONCAT('        "format": "YYYY-MM-DD",', v_crlf);
    END IF;
    
    SET v_query_grid = CONCAT(v_query_grid,
                '      {', v_crlf, 
                '        "type": "', v_obj, '",', v_crlf, 
                '        "name": "', v_dd_fieldname, '",', v_crlf, 
                '        "label": "', v_dd_Fieldcn, '",', v_crlf,
                v_date_fmt,
                '        "toggled": true', v_crlf, 
                '      },');
    
    SET v_inow = v_inow + 1;
  END WHILE;
  
  IF v_query_filter <> '' THEN 
    SET v_query_filter = SUBSTRING(v_query_filter, 1, CHAR_LENGTH(v_query_filter) - 1);
  END IF;
  
  IF v_query_grid <> '' THEN 
    SET v_query_grid = CONCAT(SUBSTRING(v_query_grid, 1, CHAR_LENGTH(v_query_grid) - 1), v_crlf);
  END IF;
  
  -- Query 有2處要修改  
  IF p_actiontype = 'Q' THEN
    SET v_dvalue = '*';
    SET v_q_filter = '';
    SET v_q_inputClassName = '  "inputClassName": "w-60",';
  ELSE
    -- 新增也可以給預設值 2023/1/14 Michael
    IF v_fielddefault <> '' THEN
      IF SUBSTRING(v_fielddefault, 1, 3) = 'ls:' THEN 
        SET v_fielddefault = CONCAT('${', v_fielddefault, '}');
      END IF;
    END IF;
    
    SET v_dvalue = v_fielddefault;
    SET v_q_filter = '';
    SET v_q_inputClassName = '';
  END IF;
  
  -- 動態顯式否
  IF IFNULL(v_visibleOn, '') <> '' THEN
    SET v_ipos = LOCATE(',', v_visibleOn);
    IF v_ipos > 0 THEN
      SET v_vKey = SUBSTRING(v_visibleOn, 1, v_ipos - 1);
      SET v_vVal = SUBSTRING(v_visibleOn, v_ipos + 1, 100);
      SET v_q_inputClassName = CONCAT('  "visibleOn": "this.', v_vKey, ' == \\"', v_vVal, '\\"",');
    END IF;
  END IF;
  
  -- 加入是否必填
  IF (IFNULL(v_fieldchecknull, '') = 'Y' or IFNULL(v_fieldchecknull, '') = 'A') THEN
    SET v_q_inputClassName = CONCAT('  "required": true,', v_crlf, v_q_inputClassName);
  END IF;
  
  IF SUBSTRING(p_actiontype, 1, 1) = 'E' OR p_actiontype = 'Q' THEN
    SET v_ret = CONCAT('{', v_crlf, 
                '  "type": "picker",', v_crlf, 
                '  "name": "', p_fieldname, '",', v_crlf,
                '  "joinValues": ', IF(v_iscolumnmatch = 'Y', 'false', 'true'), ',', v_crlf,
                '  "valueField": "', v_dd_pk, '",', v_crlf,
                '  "labelField": "', 
                IF((v_dd_fieldblock >= 90 AND v_iscolumnmatch = 'Y') OR IFNULL(v_labelField, '') = '', 
                   v_dd_pk, v_labelField), '",', v_crlf,
                IF(v_dd_fieldblock >= 90 OR IFNULL(v_labelField, '') = '', 
                   CONCAT('        "width": ', v_FieldWidth, ',', v_crlf), ''),
                '  "label": "', p_fieldcname, '",', v_crlf,
                v_q_inputClassName, v_crlf,
                '  "source": {', v_crlf,
                '    "method": "post",', v_crlf,
                '    "url": "', p_ip, '/xapi/v2/', p_db, '/orm_api/3/",', v_crlf,
                '    "data": {', v_crlf,
                '      "token": "${ls:token}",', v_crlf,
                '      "page": "${page}",', v_crlf,
                '      "perPage": "${perPage}",', v_crlf,
                '      "keywords": "${', p_fieldname, '_keywords}",', v_crlf,
                '      "_$_tableName": "', v_dd_table, '",', v_crlf,
                '      "_$_pk": "', v_dd_pk, '",', v_crlf,
                '      "_$_action": "P",', v_crlf,
                '      "_$_query_filter": "1^10^', v_dd_pk, '^*^^^', v_query_filter, v_q_filter, '"', v_crlf,
                '    },', v_crlf,
                '    "dataType": "form"', v_crlf,
                '  },', v_crlf,
                '  "size": "', IF(v_imax > 6, 'lg', 'md'), '",', v_crlf,
                '  "value": "', v_dvalue, '",', v_crlf,
                '  "multiple": false,', v_crlf,
                '  "pickerSchema": {', v_crlf,
                '    "mode": "table",', v_crlf,
                '    "tableClassName": "myGridHeader_a",', v_crlf,
                '    "title": "', v_dd_functionname, '",', v_crlf,
                '    "name": "', v_dd_table, '_', p_fieldname, '",', v_crlf,
                '    "headerToolbar": {', v_crlf,
                '      "wrapWithPanel": false,', v_crlf,
                '      "type": "form",', v_crlf,
                '      "className": "text-right",', v_crlf,
                '      "target": "', v_dd_table, '_', p_fieldname, '",', v_crlf,
                '      "mode": "inline",', v_crlf,
                '      "body": [', v_crlf,
                '        {', v_crlf,
                '          "type": "input-text",', v_crlf,
                '          "name": "', p_fieldname, '_keywords",', v_crlf,
                '          "addOn": {', v_crlf,
                '            "type": "submit",', v_crlf,
                '            "label": "查詢",', v_crlf,
                '            "tooltip": "輸入 * 可以查詢全部資料",', v_crlf,
                '            "level": "primary",', v_crlf,
                '            "icon": "fa fa-search pull-left"', v_crlf,
                '          }', v_crlf,
                '        }', v_crlf,
                '      ]', v_crlf,
                '    },', v_crlf,
                '    "columns": [', v_crlf,
                v_query_grid,
                '    ]', v_crlf,
                '  }', v_crlf,
                '}', v_crlf);
  ELSE -- for View
    SET v_ret = CONCAT('{', v_crlf,
                '  "type": "picker",', v_crlf,
                '  "name": "', p_fieldname, '",', v_crlf,
                '  "disabled": true,', v_crlf,
                '  "joinValues": true,', v_crlf,
                '  "valueField": "', v_dd_pk, '",', v_crlf,
                '  "labelField": "', IFNULL(v_labelField, ''), '",', v_crlf,
                '  "label": "', p_fieldcname, '",', v_crlf,
                '  "source": {', v_crlf,
                '    "method": "post",', v_crlf,
                '    "url": "', p_ip, '/xapi/v2/', p_db, '/orm_api/3/",', v_crlf,
                '    "data": {', v_crlf,
                '      "token": "${ls:token}",', v_crlf,
                '      "_$_tableName": "', v_dd_table, '",', v_crlf,
                '      "_$_pk": "', v_dd_pk, '",', v_crlf,
                '      "_$_action": "P",', v_crlf,
                '      "_$_query_filter": "1^10^', v_dd_pk, '^*^^^', v_query_filter, '^${', p_fieldname, '}"', v_crlf,
                '    },', v_crlf,
                '    "dataType": "form"', v_crlf,
                '  }', v_crlf,
                '}', v_crlf);
  END IF;
  
  DROP TEMPORARY TABLE IF EXISTS temp_jsondata_picker;
  
  RETURN v_ret;
END //

DELIMITER ;