210 lines
8.1 KiB
Plaintext
210 lines
8.1 KiB
Plaintext
DELIMITER //
|
|
|
|
CREATE OR REPLACE PROCEDURE zen_create_json_comps_formula(
|
|
IN p_uuid VARCHAR(100),
|
|
IN p_FieldObject VARCHAR(100),
|
|
IN p_FieldName VARCHAR(100),
|
|
IN p_FieldCN NVARCHAR(100),
|
|
IN p_picker_tag VARCHAR(100),
|
|
IN p_rowid INT
|
|
)
|
|
/*
|
|
function : amis Wizard 產生 json UI 的核心程式(每個 Component 有各自的 json UI 定義)
|
|
description: 本段 sp 主要是設定 formula 的 json 定義
|
|
Build Date : 2023/12/20
|
|
Modified : 2025/08/09 (轉換為 MariaDB 版本)
|
|
|
|
Modify History
|
|
Item Who Date Modify Docs
|
|
==== ========== ========== ================================================
|
|
1 Michael 2023/12/20 根據 v1.0 重構
|
|
2 Assistant 2025/08/09 轉換為 MariaDB 版本
|
|
*/
|
|
BEGIN
|
|
DECLARE v_comps TEXT;
|
|
DECLARE v_json_insert TEXT;
|
|
DECLARE v_json_update TEXT;
|
|
DECLARE v_json_quickedit TEXT;
|
|
DECLARE v_json_gridview TEXT;
|
|
DECLARE v_json_view TEXT;
|
|
DECLARE v_cnt INT;
|
|
|
|
DECLARE v_blob_data TEXT;
|
|
DECLARE v_field_df TEXT;
|
|
DECLARE v_required TEXT;
|
|
DECLARE v_crlf VARCHAR(2);
|
|
|
|
DECLARE v_tmp1 TEXT;
|
|
DECLARE v_comps_edit TEXT;
|
|
DECLARE v_gridType VARCHAR(100);
|
|
|
|
-- 通用寫法
|
|
SET v_crlf = CONCAT(CHAR(13), CHAR(10));
|
|
SET v_blob_data = '';
|
|
SET v_field_df = '';
|
|
SET v_required = '';
|
|
|
|
IF SUBSTRING(p_picker_tag, 1, 1) = '^' THEN
|
|
-- 此處先 hard code, 因為要將 date + time 變成 datetime Michael 20230902
|
|
-- 新版似乎已不使用, 但程式仍暫時保留(原本是專給行程管理及週期性會議使用)
|
|
SET v_tmp1 = SUBSTRING(p_picker_tag, 2, 100);
|
|
SET v_tmp1 = REPLACE(v_tmp1, '+', CONCAT('+', CHAR(39), ' ', CHAR(39), '+'));
|
|
|
|
SET v_comps = CONCAT(
|
|
' {', v_crlf,
|
|
' "type": "', p_FieldObject, '",', v_crlf,
|
|
' "name": "', p_FieldName, '",', v_crlf,
|
|
' "formula": "', v_tmp1, '"', v_crlf,
|
|
' }'
|
|
);
|
|
|
|
SET v_comps_edit = v_comps;
|
|
ELSE
|
|
-- 專 for Add
|
|
SET v_comps = CONCAT(
|
|
' {', v_crlf,
|
|
' "type": "static",', v_crlf,
|
|
' "name": "', p_FieldName, '",', v_crlf,
|
|
' "label": "', p_FieldCN, '"', v_crlf,
|
|
' },', v_crlf,
|
|
' {', v_crlf,
|
|
' "type": "', p_FieldObject, '",', v_crlf,
|
|
' "name": "', p_FieldName, '",', v_crlf,
|
|
' "value": 0,', v_crlf,
|
|
' "formula": "${', p_picker_tag, '}"', v_crlf,
|
|
' }'
|
|
);
|
|
|
|
-- 專 for update
|
|
IF LOCATE('|', p_picker_tag) > 0 AND LOCATE('pick', p_picker_tag) > 0 THEN
|
|
SET v_comps_edit = CONCAT(
|
|
' {', v_crlf,
|
|
' "type": "static",', v_crlf,
|
|
' "id": "_', p_FieldName, '",', v_crlf,
|
|
' "name": "', p_FieldName, '",', v_crlf,
|
|
' "label": "', p_FieldCN, '"', v_crlf,
|
|
' }'
|
|
);
|
|
ELSE
|
|
SET v_comps_edit = CONCAT(
|
|
' {', v_crlf,
|
|
' "type": "static",', v_crlf,
|
|
' "id": "_', p_FieldName, '",', v_crlf,
|
|
' "name": "', p_FieldName, '",', v_crlf,
|
|
' "label": "', p_FieldCN, '"', v_crlf,
|
|
' },', v_crlf,
|
|
' {', v_crlf,
|
|
' "type": "', p_FieldObject, '",', v_crlf,
|
|
' "name": "', p_FieldName, '",', v_crlf,
|
|
' "value": 0,', v_crlf,
|
|
' "formula": "${', p_picker_tag, '}"', v_crlf,
|
|
' }'
|
|
);
|
|
END IF;
|
|
|
|
-- 專 for View (m-d 的 details sum)
|
|
IF LOCATE('ARRAYMAP', p_picker_tag) > 0 THEN
|
|
-- 先 hard code, 再想辦法設定
|
|
IF LOCATE('bill_', p_FieldName) > 0 AND LOCATE('amount', p_FieldName) > 0 THEN
|
|
-- 總計(totalamount + taxamount)
|
|
SET v_json_view = CONCAT(
|
|
' {', v_crlf,
|
|
' "type": "static",', v_crlf,
|
|
' "id": "_', p_FieldName, '",', v_crlf,
|
|
' "name": "', p_FieldName, '",', v_crlf,
|
|
' "value": "${totalamount + taxamount|round: 0}",', v_crlf,
|
|
' "label": "', p_FieldCN, '"', v_crlf,
|
|
' }'
|
|
);
|
|
|
|
SET v_comps_edit = v_json_view;
|
|
ELSEIF LOCATE('bill_', p_FieldName) > 0 AND LOCATE('quantity', p_FieldName) > 0 THEN
|
|
-- 數量(不顯示)
|
|
SET v_json_view = ' ';
|
|
|
|
SET v_comps_edit = CONCAT(
|
|
' {', v_crlf,
|
|
' "type": "static",', v_crlf,
|
|
' "id": "_', p_FieldName, '",', v_crlf,
|
|
' "name": "', p_FieldName, '",', v_crlf,
|
|
' "label": "', p_FieldCN, '"', v_crlf,
|
|
' }'
|
|
);
|
|
ELSE
|
|
-- 有資料庫欄位, 直接顯示
|
|
SET v_json_view = CONCAT(
|
|
' {', v_crlf,
|
|
' "type": "static",', v_crlf,
|
|
' "id": "_', p_FieldName, '",', v_crlf,
|
|
' "name": "', p_FieldName, '",', v_crlf,
|
|
' "label": "', p_FieldCN, '"', v_crlf,
|
|
' }'
|
|
);
|
|
|
|
SET v_comps_edit = v_json_view;
|
|
END IF;
|
|
END IF;
|
|
END IF;
|
|
|
|
-- insert/edit 要改為 items (原設定為 details)
|
|
SET v_comps = REPLACE(v_comps, 'details|', 'items|');
|
|
SET v_comps_edit = REPLACE(v_comps_edit, 'details|', 'items|');
|
|
|
|
-- insert/update: 加入判斷, 是否為 pk 欄位
|
|
SET v_json_insert = v_comps;
|
|
SET v_json_update = v_comps_edit;
|
|
|
|
/*
|
|
GridView 欄位, formula 應該 "visible": false,
|
|
*/
|
|
-- Grid view
|
|
SET v_comps_edit = '';
|
|
SET v_gridType = 'text'; -- Static
|
|
IF p_FieldObject = 'formula' AND (SUBSTRING(p_picker_tag, 1, 1) <> '^') THEN
|
|
-- 此處先 hard code, 非資料庫欄位, 欄位前固定為 bill_xxxx
|
|
IF LOCATE('|', p_picker_tag) > 0 AND LOCATE('pick', p_picker_tag) > 0 THEN
|
|
SET v_comps_edit = CONCAT(
|
|
' {', v_crlf,
|
|
' "type": "', v_gridType, '",', v_crlf,
|
|
' "name": "', p_FieldName, '",', v_crlf,
|
|
' "label": "', p_FieldCN, '"', v_crlf,
|
|
' }'
|
|
);
|
|
ELSE
|
|
SET v_comps_edit = CONCAT(
|
|
' {', v_crlf,
|
|
' "type": "', v_gridType, '",', v_crlf,
|
|
' "name": "', p_FieldName, '",', v_crlf,
|
|
' "label": "', p_FieldCN, '"', v_crlf,
|
|
' },', v_crlf,
|
|
' {', v_crlf,
|
|
' "type": "', p_FieldObject, '",', v_crlf,
|
|
' "name": "', p_FieldName, '",', v_crlf,
|
|
' "visible": false,', v_crlf,
|
|
' "value": 0,', v_crlf,
|
|
' "formula": "${', p_picker_tag, '}"', v_crlf,
|
|
' }'
|
|
);
|
|
END IF;
|
|
|
|
SET v_comps_edit = REPLACE(v_comps_edit, 'details|', 'items|');
|
|
END IF;
|
|
|
|
-- Grid View
|
|
SET v_json_gridview = v_comps_edit;
|
|
|
|
-- view
|
|
IF IFNULL(v_json_view, '') = '' THEN
|
|
-- SET v_json_view = v_comps_edit;
|
|
SET v_json_view = v_json_update;
|
|
END IF;
|
|
|
|
-- 寫入暫存 Table (zen_wizard_jsondata)
|
|
UPDATE zen_wizard_jsondata SET json_insert = v_json_insert WHERE uuid = p_uuid AND rowid = p_rowid;
|
|
UPDATE zen_wizard_jsondata SET json_update = v_json_update WHERE uuid = p_uuid AND rowid = p_rowid;
|
|
UPDATE zen_wizard_jsondata SET json_quickedit = v_json_quickedit WHERE uuid = p_uuid AND rowid = p_rowid;
|
|
UPDATE zen_wizard_jsondata SET json_gridview = v_json_gridview WHERE uuid = p_uuid AND rowid = p_rowid;
|
|
UPDATE zen_wizard_jsondata SET json_view = v_json_view WHERE uuid = p_uuid AND rowid = p_rowid;
|
|
END //
|
|
|
|
DELIMITER ; |