119 lines
4.5 KiB
Plaintext
119 lines
4.5 KiB
Plaintext
DELIMITER //
|
|
|
|
CREATE OR REPLACE PROCEDURE zen_create_json_prepare_gridquery(
|
|
IN p_uuid VARCHAR(100),
|
|
IN p_query_type VARCHAR(1)
|
|
)
|
|
/*
|
|
function : amis Wizard 產生 json UI 的核心程式(每個 Component 有各自的 json UI 定義)
|
|
description: 本段 sp 主要是設定 master-detail 中 detail 相關的 json 定義
|
|
Build Date : 2023/12/20
|
|
|
|
Modify History
|
|
Item Who Date Modify Docs
|
|
==== ========== ========== ================================================
|
|
1 Michael 2023/12/20 根據 v1.0 重構
|
|
*/
|
|
BEGIN
|
|
DECLARE v_QueryFlds VARCHAR(1000);
|
|
DECLARE v_QueryData VARCHAR(2000);
|
|
DECLARE v_QueryType VARCHAR(600);
|
|
DECLARE v_keyword_search VARCHAR(600);
|
|
DECLARE v_FieldName VARCHAR(100);
|
|
DECLARE v_FieldDS VARCHAR(1);
|
|
DECLARE v_FieldCN NVARCHAR(100);
|
|
DECLARE v_query_type_data TEXT;
|
|
DECLARE v_FieldObject VARCHAR(100);
|
|
DECLARE v_Filters TEXT;
|
|
DECLARE v_pos INT;
|
|
DECLARE v_imax INT;
|
|
DECLARE v_imin INT;
|
|
DECLARE v_inow INT;
|
|
-- DECLARE v_y INT;
|
|
DECLARE v_crlf VARCHAR(2);
|
|
DECLARE v_count INT;
|
|
|
|
SET v_crlf = CONCAT(CHAR(13), CHAR(10));
|
|
SET v_QueryFlds = '';
|
|
SET v_QueryData = '';
|
|
SET v_QueryType = '';
|
|
SET v_Filters = '';
|
|
|
|
/*
|
|
取得 detail 的欄位名稱(要包含 pk 欄位)
|
|
新增 grid footer sum, 含 crud 及 input-table
|
|
*/
|
|
-- SELECT COUNT(*) INTO v_imax FROM zen_wizard_jsondata WHERE uuid = p_uuid;
|
|
SELECT min(rowid) INTO v_inow FROM zen_wizard_jsondata WHERE uuid = p_uuid;
|
|
SELECT max(rowid) INTO v_imax FROM zen_wizard_jsondata WHERE uuid = p_uuid;
|
|
|
|
WHILE v_inow <= v_imax DO
|
|
SELECT
|
|
FieldName, IFNULL(FieldCN, FieldName), FieldObject, FieldDS
|
|
INTO
|
|
v_FieldName, v_FieldCN, v_FieldObject, v_FieldDS
|
|
FROM zen_wizard_jsondata WHERE uuid = p_uuid AND rowid = v_inow;
|
|
|
|
IF v_FieldDS = 'Y' AND (v_FieldObject <> 'hidden') THEN
|
|
-- Query Header Define
|
|
SET v_QueryFlds = CONCAT(v_QueryFlds, v_FieldName, '^');
|
|
SET v_QueryData = CONCAT(v_QueryData, '${search_', v_FieldName, '}^');
|
|
|
|
IF v_FieldObject LIKE '%int%' OR v_FieldObject LIKE '%decimal%' THEN
|
|
SET v_QueryType = CONCAT(v_QueryType, 'I^');
|
|
ELSE -- 20230425 C 的預設改為 Clike, 因為大部分的情況應該都是用 like 查詢比較正常
|
|
SET v_QueryType = CONCAT(v_QueryType, 'Clike^');
|
|
END IF;
|
|
END IF;
|
|
|
|
-- 此處要根據顯示元件的定義, 目前還不全(20231103 再加上 select)
|
|
IF (v_FieldDS = 'Y' AND (v_FieldObject LIKE '%text%' OR v_FieldObject LIKE '%picker%' OR v_FieldObject LIKE '%select%')) THEN
|
|
SET v_Filters = CONCAT(v_Filters, v_FieldName, ',');
|
|
END IF;
|
|
|
|
SET v_inow = v_inow + 1;
|
|
END WHILE;
|
|
|
|
IF v_Filters <> '' THEN
|
|
SET v_Filters = SUBSTRING(v_Filters, 1, LENGTH(v_Filters)-1);
|
|
END IF;
|
|
|
|
INSERT INTO zen_wizard_tmp (uuid,zen_key,zen_value,create_date) VALUES (p_uuid, 'Filters', v_Filters, NOW());
|
|
|
|
IF v_QueryFlds <> '' THEN
|
|
SET v_QueryFlds = SUBSTRING(v_QueryFlds, 1, LENGTH(v_QueryFlds)-1);
|
|
END IF;
|
|
|
|
IF v_QueryData <> '' THEN
|
|
SET v_QueryData = SUBSTRING(v_QueryData, 1, LENGTH(v_QueryData)-1);
|
|
END IF;
|
|
|
|
IF v_QueryType <> '' THEN
|
|
SET v_QueryType = SUBSTRING(v_QueryType, 1, LENGTH(v_QueryType)-1);
|
|
END IF;
|
|
|
|
-- Query Header
|
|
SET v_query_type_data = '';
|
|
SET v_keyword_search = '';
|
|
|
|
IF p_query_type = 'C' THEN
|
|
SET v_query_type_data = CONCAT(
|
|
'"_$_frmQueryFlds_": "', v_QueryFlds, '",', v_crlf,
|
|
' "_$_frmQueryData_": "', v_QueryData, '",', v_crlf,
|
|
' "_$_frmQueryType_": "', v_QueryType, '",', v_crlf
|
|
);
|
|
ELSE
|
|
SET v_keyword_search = CONCAT(
|
|
'{', v_crlf,
|
|
' "type": "search-box",', v_crlf,
|
|
' "name": "keywords",', v_crlf,
|
|
' "align": "left",', v_crlf,
|
|
' "placeholder": "全文檢索"', v_crlf,
|
|
' },'
|
|
);
|
|
END IF;
|
|
|
|
INSERT INTO zen_wizard_tmp (uuid,zen_key,zen_value,create_date) VALUES (p_uuid, 'query_type_data', v_query_type_data, NOW());
|
|
INSERT INTO zen_wizard_tmp (uuid,zen_key,zen_value,create_date) VALUES (p_uuid, 'keyword_search', v_keyword_search, NOW());
|
|
INSERT INTO zen_wizard_tmp (uuid,zen_key,zen_value,create_date) VALUES (p_uuid, 'is_query_header', IF(p_query_type = 'C', 'true', 'false'), NOW());
|
|
END // |