461 lines
28 KiB
SQL
461 lines
28 KiB
SQL
-- =============================================
|
||
-- DCP 平台内置接口和流程初始化数据
|
||
-- 使用方法：
|
||
-- 1. 先在平台管理页面新增 DCP 平台实例（code='dcp'）
|
||
-- 2. 执行本脚本前，将下方 @dcp_platform_id 替换为实际的平台 ID
|
||
-- 或直接执行：SET @dcp_platform_id = (SELECT id FROM platform_instance WHERE code='dcp' LIMIT 1);
|
||
-- =============================================
|
||
|
||
SET @dcp_platform_id = (SELECT id FROM platform_instance WHERE code = 'dcp' LIMIT 1);
|
||
|
||
-- 重新执行时先清理本脚本维护的流程和接口，避免旧配置被 LIMIT 1 命中。
|
||
DELETE s FROM platform_flow_step s
|
||
INNER JOIN platform_flow f ON f.id = s.flow_id
|
||
WHERE f.platform_id = @dcp_platform_id
|
||
AND f.code IN (
|
||
'dcp_init', 'dcp_token_refresh', 'dcp_onboard_inclino',
|
||
'dcp_onboard_stress', 'dcp_onboard_hyeto',
|
||
'dcp_onboard_static_level', 'dcp_onboard_deep_inclino'
|
||
);
|
||
|
||
DELETE FROM platform_flow
|
||
WHERE platform_id = @dcp_platform_id
|
||
AND code IN (
|
||
'dcp_init', 'dcp_token_refresh', 'dcp_onboard_inclino',
|
||
'dcp_onboard_stress', 'dcp_onboard_hyeto',
|
||
'dcp_onboard_static_level', 'dcp_onboard_deep_inclino'
|
||
);
|
||
|
||
DELETE FROM platform_interface
|
||
WHERE platform_id = @dcp_platform_id
|
||
AND code IN (
|
||
'dcp_auth', 'dcp_get_user', 'dcp_get_projects', 'dcp_create_point',
|
||
'dcp_bind_inclino', 'dcp_bind_stress', 'dcp_bind_hyeto',
|
||
'dcp_bind_static_level', 'dcp_bind_deep_inclino',
|
||
'dcp_push_inclino', 'dcp_push_stress', 'dcp_push_hyeto',
|
||
'dcp_push_static_level'
|
||
);
|
||
|
||
-- =============================================
|
||
-- 一、接口定义（13个）
|
||
-- =============================================
|
||
|
||
-- 1. 认证接口
|
||
INSERT INTO `platform_interface` (`platform_id`,`name`,`code`,`method`,`path`,`content_type`,`request_schema`,`response_schema`,`sort_order`,`remark`,`create_by`,`create_time`)
|
||
VALUES (@dcp_platform_id, '获取Token', 'dcp_auth', 'POST', '/connect/token', 'application/x-www-form-urlencoded',
|
||
'[
|
||
{"name":"grant_type","type":"string","source":"fixed","value":"password","required":true,"desc":"授权类型"},
|
||
{"name":"scope", "type":"string","source":"auth", "field":"scope", "required":true,"desc":"scope"},
|
||
{"name":"client_id", "type":"string","source":"auth", "field":"clientId", "required":true,"desc":"客户端ID"},
|
||
{"name":"userName", "type":"string","source":"auth", "field":"userName", "required":true,"desc":"用户名"},
|
||
{"name":"password", "type":"string","source":"auth", "field":"password", "required":true,"desc":"密码"}
|
||
]',
|
||
'[
|
||
{"name":"access_token","type":"string","path":"access_token","desc":"访问Token"},
|
||
{"name":"expires_in", "type":"number","path":"expires_in", "desc":"过期秒数"},
|
||
{"name":"token_type", "type":"string","path":"token_type", "desc":"Token类型"}
|
||
]',
|
||
1, 'DCP平台认证，获取access_token', 'system', NOW());
|
||
|
||
-- 2. 获取用户ID
|
||
INSERT INTO `platform_interface` (`platform_id`,`name`,`code`,`method`,`path`,`content_type`,`request_schema`,`response_schema`,`sort_order`,`remark`,`create_by`,`create_time`)
|
||
VALUES (@dcp_platform_id, '获取用户ID', 'dcp_get_user', 'GET', '/api/app/user/user-id', 'application/json',
|
||
'[]',
|
||
'[
|
||
{"name":"id", "type":"string","path":"id", "desc":"用户ID"},
|
||
{"name":"userName", "type":"string","path":"userName", "desc":"用户名"},
|
||
{"name":"phoneNumber", "type":"string","path":"phoneNumber", "desc":"手机号"}
|
||
]',
|
||
2, '获取当前登录用户信息', 'system', NOW());
|
||
|
||
-- 3. 获取项目列表
|
||
INSERT INTO `platform_interface` (`platform_id`,`name`,`code`,`method`,`path`,`content_type`,`request_schema`,`response_schema`,`sort_order`,`remark`,`create_by`,`create_time`)
|
||
VALUES (@dcp_platform_id, '获取项目列表', 'dcp_get_projects', 'GET', '/api/app/project/by-user-id/{userId}', 'application/json',
|
||
'[
|
||
{"name":"userId","type":"string","source":"context","contextKey":"step2.id","required":true,"desc":"用户ID，来自上一步"}
|
||
]',
|
||
'[
|
||
{"name":"id", "type":"string","path":"id", "desc":"项目ID"},
|
||
{"name":"name", "type":"string","path":"name", "desc":"项目名称"}
|
||
]',
|
||
3, '获取用户下的完整项目列表', 'system', NOW());
|
||
|
||
-- 4. 创建测点
|
||
INSERT INTO `platform_interface` (`platform_id`,`name`,`code`,`method`,`path`,`content_type`,`request_schema`,`response_schema`,`sort_order`,`remark`,`create_by`,`create_time`)
|
||
VALUES (@dcp_platform_id, '创建测点', 'dcp_create_point', 'POST', '/api/app/point', 'application/json',
|
||
'[
|
||
{"name":"projectId","type":"string","source":"device","field":"platform_project_id","required":true,"desc":"项目ID"},
|
||
{"name":"name", "type":"string","source":"device","field":"serial_number","required":true,"desc":"测点名称，用设备序列号"},
|
||
{"name":"type", "type":"number","source":"fixed","value":3,"required":true,"desc":"测点类型，固定为3"}
|
||
]',
|
||
'[
|
||
{"name":"id","type":"string","path":"id","desc":"测点ID（pointId）"}
|
||
]',
|
||
4, '在DCP平台创建监测测点', 'system', NOW());
|
||
|
||
-- 5. 绑定倾角传感器
|
||
INSERT INTO `platform_interface` (`platform_id`,`name`,`code`,`method`,`path`,`content_type`,`request_schema`,`response_schema`,`sort_order`,`remark`,`create_by`,`create_time`)
|
||
VALUES (@dcp_platform_id, '绑定倾角传感器', 'dcp_bind_inclino', 'POST', '/api/app/inclino-sensor', 'application/json',
|
||
'[
|
||
{"name":"projectId", "type":"string","source":"device", "field":"platform_project_id", "required":true,"desc":"项目ID"},
|
||
{"name":"pointId", "type":"string","source":"context","contextKey":"step1.id","required":true,"desc":"测点ID，来自创建测点步骤"},
|
||
{"name":"deviceType", "type":"number","source":"fixed", "value":5, "required":true,"desc":"设备类型，倾角=5"},
|
||
{"name":"name", "type":"string","source":"device", "field":"serial_number","required":true,"desc":"传感器名称"},
|
||
{"name":"moduleId", "type":"string","source":"uuid", "required":true,"desc":"模块ID，系统自动生成UUID"},
|
||
{"name":"virtualId", "type":"string","source":"uuid", "required":true,"desc":"虚拟ID，系统自动生成UUID"},
|
||
{"name":"dH", "type":"number","source":"fixed", "value":0, "required":false,"desc":"高程"}
|
||
]',
|
||
'[
|
||
{"name":"id","type":"string","path":"id","desc":"DCP设备ID（deviceId）"}
|
||
]',
|
||
5, '将倾角传感器绑定到测点', 'system', NOW());
|
||
|
||
-- 6. 绑定轴力计
|
||
INSERT INTO `platform_interface` (`platform_id`,`name`,`code`,`method`,`path`,`content_type`,`request_schema`,`response_schema`,`sort_order`,`remark`,`create_by`,`create_time`)
|
||
VALUES (@dcp_platform_id, '绑定轴力计', 'dcp_bind_stress', 'POST', '/api/app/stress-sensor', 'application/json',
|
||
'[
|
||
{"name":"projectId", "type":"string","source":"device", "field":"platform_project_id", "required":true,"desc":"项目ID"},
|
||
{"name":"pointId", "type":"string","source":"context","contextKey":"step1.id","required":true,"desc":"测点ID"},
|
||
{"name":"deviceType", "type":"number","source":"fixed", "value":10, "required":true,"desc":"设备类型，轴力计=10"},
|
||
{"name":"name", "type":"string","source":"device", "field":"serial_number","required":true,"desc":"传感器名称"},
|
||
{"name":"factor", "type":"number","source":"fixed", "value":1, "required":false,"desc":"系数"},
|
||
{"name":"moduleId", "type":"string","source":"uuid", "required":true},
|
||
{"name":"virtualId", "type":"string","source":"uuid", "required":true}
|
||
]',
|
||
'[{"name":"id","type":"string","path":"id","desc":"DCP设备ID"}]',
|
||
6, '将轴力计绑定到测点', 'system', NOW());
|
||
|
||
-- 7. 绑定雨量计
|
||
INSERT INTO `platform_interface` (`platform_id`,`name`,`code`,`method`,`path`,`content_type`,`request_schema`,`response_schema`,`sort_order`,`remark`,`create_by`,`create_time`)
|
||
VALUES (@dcp_platform_id, '绑定雨量计', 'dcp_bind_hyeto', 'POST', '/api/app/hyeto-sensor', 'application/json',
|
||
'[
|
||
{"name":"projectId", "type":"string","source":"device", "field":"platform_project_id", "required":true},
|
||
{"name":"pointId", "type":"string","source":"context","contextKey":"step1.id","required":true},
|
||
{"name":"deviceType", "type":"number","source":"fixed", "value":3, "required":true,"desc":"雨量计=3"},
|
||
{"name":"name", "type":"string","source":"device", "field":"serial_number","required":true},
|
||
{"name":"moduleId", "type":"string","source":"uuid", "required":true},
|
||
{"name":"virtualId", "type":"string","source":"uuid", "required":true}
|
||
]',
|
||
'[{"name":"id","type":"string","path":"id","desc":"DCP设备ID"}]',
|
||
7, '将雨量计绑定到测点', 'system', NOW());
|
||
|
||
-- 8. 绑定静力水准
|
||
INSERT INTO `platform_interface` (`platform_id`,`name`,`code`,`method`,`path`,`content_type`,`request_schema`,`response_schema`,`sort_order`,`remark`,`create_by`,`create_time`)
|
||
VALUES (@dcp_platform_id, '绑定静力水准', 'dcp_bind_static_level', 'POST', '/api/app/static-level-device', 'application/json',
|
||
'[
|
||
{"name":"projectId", "type":"string","source":"device", "field":"platform_project_id", "required":true},
|
||
{"name":"pointId", "type":"string","source":"context","contextKey":"step1.id","required":true},
|
||
{"name":"deviceType", "type":"number","source":"fixed", "value":1002, "required":true,"desc":"静力水准=1002"},
|
||
{"name":"name", "type":"string","source":"device", "field":"serial_number","required":true},
|
||
{"name":"moduleId", "type":"string","source":"uuid", "required":true},
|
||
{"name":"virtualId", "type":"string","source":"uuid", "required":true},
|
||
{"name":"sensorIds", "type":"array", "source":"referenceSubDeviceSensorIdPairs","required":true,"desc":"基准点与每个监测点成对绑定"}
|
||
]',
|
||
'[{"name":"id","type":"string","path":"id","desc":"DCP设备ID"}]',
|
||
8, '将静力水准传感器绑定到测点', 'system', NOW());
|
||
|
||
-- 9. 绑定深部位移
|
||
INSERT INTO `platform_interface` (`platform_id`,`name`,`code`,`method`,`path`,`content_type`,`request_schema`,`response_schema`,`sort_order`,`remark`,`create_by`,`create_time`)
|
||
VALUES (@dcp_platform_id, '绑定深部位移', 'dcp_bind_deep_inclino', 'POST', '/api/app/deep-inclino-device', 'application/json',
|
||
'[
|
||
{"name":"projectId", "type":"string","source":"device", "field":"platform_project_id","required":true},
|
||
{"name":"pointId", "type":"string","source":"context", "contextKey":"step1.id", "required":true},
|
||
{"name":"deviceType", "type":"number","source":"fixed", "value":1001, "required":true},
|
||
{"name":"name", "type":"string","source":"device", "field":"serial_number", "required":true},
|
||
{"name":"moduleId", "type":"string","source":"uuid", "required":true},
|
||
{"name":"virtualId", "type":"string","source":"uuid", "required":true},
|
||
{"name":"sensorIds", "type":"array", "source":"cascadedSubDeviceSensorIds", "required":true},
|
||
{"name":"sensorDepths", "type":"array", "source":"cascadedSubDeviceDepths", "required":true},
|
||
{"name":"holeDepth", "type":"number","source":"onboard_config", "field":"holeDepth", "required":true},
|
||
{"name":"holeElevation","type":"number","source":"onboard_config", "field":"holeElevation", "required":true}
|
||
]',
|
||
'[{"name":"id","type":"string","path":"id","desc":"DCP设备ID"}]',
|
||
9, '将深部位移设备绑定到测点', 'system', NOW());
|
||
|
||
|
||
-- =============================================
|
||
-- 1.5 推送数据接口（4个，设备绑定时选择推送接口用）
|
||
-- =============================================
|
||
|
||
-- 9. 推送倾角数据
|
||
INSERT INTO `platform_interface` (`platform_id`,`name`,`code`,`method`,`path`,`content_type`,`request_schema`,`response_schema`,`sort_order`,`remark`,`create_by`,`create_time`)
|
||
VALUES (@dcp_platform_id, '推送倾角数据', 'dcp_push_inclino', 'POST', '/api/app/inclino-data/many', 'application/json',
|
||
'[
|
||
{"name":"deviceId", "type":"string","source":"device","field":"platform_device_id","required":true,"desc":"DCP设备ID"},
|
||
{"name":"epoch", "type":"string","source":"ts","required":true,"desc":"采集时间，ISO8601格式"},
|
||
{"name":"xDeg", "type":"number","source":"thingsModel","required":true,"desc":"X轴倾角(度)"},
|
||
{"name":"yDeg", "type":"number","source":"thingsModel","required":true,"desc":"Y轴倾角(度)"},
|
||
{"name":"temperature","type":"number","source":"thingsModel","required":false,"desc":"温度"}
|
||
]',
|
||
'[]',
|
||
9, '推送倾角传感器实时数据到DCP', 'system', NOW());
|
||
|
||
-- 10. 推送轴力计数据
|
||
INSERT INTO `platform_interface` (`platform_id`,`name`,`code`,`method`,`path`,`content_type`,`request_schema`,`response_schema`,`sort_order`,`remark`,`create_by`,`create_time`)
|
||
VALUES (@dcp_platform_id, '推送轴力计数据', 'dcp_push_stress', 'POST', '/api/app/stress-data/many', 'application/json',
|
||
'[
|
||
{"name":"deviceId","type":"string","source":"device","field":"platform_device_id","required":true,"desc":"DCP设备ID"},
|
||
{"name":"epoch", "type":"string","source":"ts","required":true,"desc":"采集时间，ISO8601格式"},
|
||
{"name":"e", "type":"number","source":"thingsModel","required":true,"desc":"累计值（必须递增）"}
|
||
]',
|
||
'[]',
|
||
10, '推送轴力计实时数据到DCP', 'system', NOW());
|
||
|
||
-- 11. 推送雨量计数据
|
||
INSERT INTO `platform_interface` (`platform_id`,`name`,`code`,`method`,`path`,`content_type`,`request_schema`,`response_schema`,`sort_order`,`remark`,`create_by`,`create_time`)
|
||
VALUES (@dcp_platform_id, '推送雨量计数据', 'dcp_push_hyeto', 'POST', '/api/app/hyeto-data/many', 'application/json',
|
||
'[
|
||
{"name":"deviceId","type":"string","source":"device","field":"platform_device_id","required":true,"desc":"DCP设备ID"},
|
||
{"name":"epoch", "type":"string","source":"ts","required":true,"desc":"采集时间，ISO8601格式"},
|
||
{"name":"value", "type":"number","source":"thingsModel","required":true,"desc":"累计雨量（必须递增）"}
|
||
]',
|
||
'[]',
|
||
11, '推送雨量计实时数据到DCP', 'system', NOW());
|
||
|
||
-- 12. 推送静力水准数据
|
||
INSERT INTO `platform_interface` (`platform_id`,`name`,`code`,`method`,`path`,`content_type`,`request_schema`,`response_schema`,`sort_order`,`remark`,`create_by`,`create_time`)
|
||
VALUES (@dcp_platform_id, '推送静力水准数据', 'dcp_push_static_level', 'POST', '/api/app/static-level-data/many', 'application/json',
|
||
'[
|
||
{"name":"deviceId", "type":"string","source":"device","field":"platform_device_id","required":true,"desc":"DCP设备ID"},
|
||
{"name":"sensorData","type":"array","source":"context","contextKey":"sensorData","required":true,"desc":"静力水准数组","itemSchema":[
|
||
{"name":"epoch","type":"string","source":"ts","desc":"采集时间"},
|
||
{"name":"height","type":"number","source":"thingsModel","desc":"高程"},
|
||
{"name":"temperature","type":"number","source":"thingsModel","desc":"温度"},
|
||
{"name":"sensorId","type":"string","source":"subDeviceId","desc":"DCP侧sensorId"}
|
||
]},
|
||
{"name":"ct","type":"number","source":"fixed","value":1,"required":true,"desc":"采集类型"}
|
||
]',
|
||
'[]',
|
||
12, '推送静力水准实时数据到DCP', 'system', NOW());
|
||
|
||
|
||
-- =============================================
|
||
-- 二、流程定义（7个）
|
||
-- =============================================
|
||
|
||
-- 流程1：平台初始化（flow_type=1）
|
||
INSERT INTO `platform_flow` (`platform_id`,`name`,`code`,`flow_type`,`sensor_type`,`enable`,`remark`,`create_by`,`create_time`)
|
||
VALUES (@dcp_platform_id, 'DCP平台初始化', 'dcp_init', 1, NULL, '1', '获取Token→userId，一次性执行', 'system', NOW());
|
||
|
||
SET @flow_init_id = LAST_INSERT_ID();
|
||
|
||
-- 初始化流程步骤
|
||
INSERT INTO `platform_flow_step` (`flow_id`,`step_order`,`interface_id`,`result_key`,`result_save`,`remark`)
|
||
VALUES (@flow_init_id, 1,
|
||
(SELECT id FROM platform_interface WHERE platform_id=@dcp_platform_id AND code='dcp_auth' LIMIT 1),
|
||
'step1',
|
||
'[{"field":"access_token","saveTo":"platform_instance.access_token"},{"field":"expires_in","saveTo":"platform_instance.token_expires_at"}]',
|
||
'获取access_token并持久化');
|
||
|
||
INSERT INTO `platform_flow_step` (`flow_id`,`step_order`,`interface_id`,`result_key`,`result_save`,`remark`)
|
||
VALUES (@flow_init_id, 2,
|
||
(SELECT id FROM platform_interface WHERE platform_id=@dcp_platform_id AND code='dcp_get_user' LIMIT 1),
|
||
'step2',
|
||
'[{"field":"id","saveTo":"extra_config.userId"}]',
|
||
'获取userId并保存');
|
||
|
||
|
||
-- 流程2：Token刷新（flow_type=3）
|
||
INSERT INTO `platform_flow` (`platform_id`,`name`,`code`,`flow_type`,`sensor_type`,`enable`,`remark`,`create_by`,`create_time`)
|
||
VALUES (@dcp_platform_id, 'DCP Token刷新', 'dcp_token_refresh', 3, NULL, '1', 'Token过期时自动触发', 'system', NOW());
|
||
|
||
SET @flow_token_id = LAST_INSERT_ID();
|
||
|
||
INSERT INTO `platform_flow_step` (`flow_id`,`step_order`,`interface_id`,`result_key`,`result_save`,`remark`)
|
||
VALUES (@flow_token_id, 1,
|
||
(SELECT id FROM platform_interface WHERE platform_id=@dcp_platform_id AND code='dcp_auth' LIMIT 1),
|
||
'tokenStep',
|
||
'[{"field":"access_token","saveTo":"platform_instance.access_token"},{"field":"expires_in","saveTo":"platform_instance.token_expires_at"}]',
|
||
'获取新Token并持久化');
|
||
|
||
|
||
-- 流程3：倾角设备入网（flow_type=2）
|
||
INSERT INTO `platform_flow` (`platform_id`,`name`,`code`,`flow_type`,`sensor_type`,`enable`,`remark`,`create_by`,`create_time`)
|
||
VALUES (@dcp_platform_id, 'DCP倾角入网', 'dcp_onboard_inclino', 2, 'inclino', '1', '创建测点→绑定倾角传感器', 'system', NOW());
|
||
|
||
SET @flow_inclino_id = LAST_INSERT_ID();
|
||
|
||
INSERT INTO `platform_flow_step` (`flow_id`,`step_order`,`interface_id`,`result_key`,`result_save`,`remark`)
|
||
VALUES (@flow_inclino_id, 1,
|
||
(SELECT id FROM platform_interface WHERE platform_id=@dcp_platform_id AND code='dcp_create_point' LIMIT 1),
|
||
'step1',
|
||
'[{"field":"id","saveTo":"device_mapping.platform_point_id"}]',
|
||
'创建测点，获取pointId并回填');
|
||
|
||
INSERT INTO `platform_flow_step` (`flow_id`,`step_order`,`interface_id`,`result_key`,`result_save`,`remark`)
|
||
VALUES (@flow_inclino_id, 2,
|
||
(SELECT id FROM platform_interface WHERE platform_id=@dcp_platform_id AND code='dcp_bind_inclino' LIMIT 1),
|
||
'step2',
|
||
'[{"field":"id","saveTo":"device_mapping.platform_device_id"}]',
|
||
'绑定传感器，获取DCP deviceId');
|
||
|
||
|
||
-- 流程4：轴力计设备入网（flow_type=2）
|
||
INSERT INTO `platform_flow` (`platform_id`,`name`,`code`,`flow_type`,`sensor_type`,`enable`,`remark`,`create_by`,`create_time`)
|
||
VALUES (@dcp_platform_id, 'DCP轴力计入网', 'dcp_onboard_stress', 2, 'stress', '1', '创建测点→绑定轴力计', 'system', NOW());
|
||
|
||
SET @flow_stress_id = LAST_INSERT_ID();
|
||
|
||
INSERT INTO `platform_flow_step` (`flow_id`,`step_order`,`interface_id`,`result_key`,`result_save`,`remark`)
|
||
VALUES (@flow_stress_id, 1,
|
||
(SELECT id FROM platform_interface WHERE platform_id=@dcp_platform_id AND code='dcp_create_point' LIMIT 1),
|
||
'step1',
|
||
'[{"field":"id","saveTo":"device_mapping.platform_point_id"}]',
|
||
'创建测点');
|
||
|
||
INSERT INTO `platform_flow_step` (`flow_id`,`step_order`,`interface_id`,`result_key`,`result_save`,`remark`)
|
||
VALUES (@flow_stress_id, 2,
|
||
(SELECT id FROM platform_interface WHERE platform_id=@dcp_platform_id AND code='dcp_bind_stress' LIMIT 1),
|
||
'step2',
|
||
'[{"field":"id","saveTo":"device_mapping.platform_device_id"}]',
|
||
'绑定轴力计');
|
||
|
||
|
||
-- 流程5：雨量计设备入网（flow_type=2）
|
||
INSERT INTO `platform_flow` (`platform_id`,`name`,`code`,`flow_type`,`sensor_type`,`enable`,`remark`,`create_by`,`create_time`)
|
||
VALUES (@dcp_platform_id, 'DCP雨量计入网', 'dcp_onboard_hyeto', 2, 'hyeto', '1', '创建测点→绑定雨量计', 'system', NOW());
|
||
|
||
SET @flow_hyeto_id = LAST_INSERT_ID();
|
||
|
||
INSERT INTO `platform_flow_step` (`flow_id`,`step_order`,`interface_id`,`result_key`,`result_save`,`remark`)
|
||
VALUES (@flow_hyeto_id, 1,
|
||
(SELECT id FROM platform_interface WHERE platform_id=@dcp_platform_id AND code='dcp_create_point' LIMIT 1),
|
||
'step1',
|
||
'[{"field":"id","saveTo":"device_mapping.platform_point_id"}]',
|
||
'创建测点');
|
||
|
||
INSERT INTO `platform_flow_step` (`flow_id`,`step_order`,`interface_id`,`result_key`,`result_save`,`remark`)
|
||
VALUES (@flow_hyeto_id, 2,
|
||
(SELECT id FROM platform_interface WHERE platform_id=@dcp_platform_id AND code='dcp_bind_hyeto' LIMIT 1),
|
||
'step2',
|
||
'[{"field":"id","saveTo":"device_mapping.platform_device_id"}]',
|
||
'绑定雨量计');
|
||
|
||
|
||
-- 流程6：静力水准设备入网（flow_type=2）
|
||
INSERT INTO `platform_flow` (`platform_id`,`name`,`code`,`flow_type`,`sensor_type`,`enable`,`remark`,`create_by`,`create_time`)
|
||
VALUES (@dcp_platform_id, 'DCP静力水准入网', 'dcp_onboard_static_level', 2, 'static_level', '1', '创建测点→绑定静力水准', 'system', NOW());
|
||
|
||
SET @flow_static_level_id = LAST_INSERT_ID();
|
||
|
||
INSERT INTO `platform_flow_step` (`flow_id`,`step_order`,`interface_id`,`result_key`,`result_save`,`remark`)
|
||
VALUES (@flow_static_level_id, 1,
|
||
(SELECT id FROM platform_interface WHERE platform_id=@dcp_platform_id AND code='dcp_create_point' LIMIT 1),
|
||
'step1',
|
||
'[{"field":"id","saveTo":"device_mapping.platform_point_id"}]',
|
||
'创建测点');
|
||
|
||
INSERT INTO `platform_flow_step` (`flow_id`,`step_order`,`interface_id`,`result_key`,`result_save`,`remark`)
|
||
VALUES (@flow_static_level_id, 2,
|
||
(SELECT id FROM platform_interface WHERE platform_id=@dcp_platform_id AND code='dcp_bind_static_level' LIMIT 1),
|
||
'step2',
|
||
'[{"field":"id","saveTo":"device_mapping.platform_device_id"}]',
|
||
'绑定静力水准');
|
||
|
||
-- 流程7：深部位移设备入网（flow_type=2）
|
||
INSERT INTO `platform_flow` (`platform_id`,`name`,`code`,`flow_type`,`sensor_type`,`enable`,`remark`,`create_by`,`create_time`)
|
||
VALUES (@dcp_platform_id, 'DCP深部位移入网', 'dcp_onboard_deep_inclino', 2, 'deep_inclino', '1', '创建测点→绑定深部位移', 'system', NOW());
|
||
|
||
SET @flow_deep_inclino_id = LAST_INSERT_ID();
|
||
|
||
INSERT INTO `platform_flow_step` (`flow_id`,`step_order`,`interface_id`,`result_key`,`result_save`,`remark`)
|
||
VALUES (@flow_deep_inclino_id, 1,
|
||
(SELECT id FROM platform_interface WHERE platform_id=@dcp_platform_id AND code='dcp_create_point' LIMIT 1),
|
||
'step1',
|
||
'[{"field":"id","saveTo":"device_mapping.platform_point_id"}]',
|
||
'创建测点');
|
||
|
||
INSERT INTO `platform_flow_step` (`flow_id`,`step_order`,`interface_id`,`result_key`,`result_save`,`remark`)
|
||
VALUES (@flow_deep_inclino_id, 2,
|
||
(SELECT id FROM platform_interface WHERE platform_id=@dcp_platform_id AND code='dcp_bind_deep_inclino' LIMIT 1),
|
||
'step2',
|
||
'[{"field":"id","saveTo":"device_mapping.platform_device_id"}]',
|
||
'绑定深部位移');
|
||
|
||
|
||
|
||
-- 更新已落库的平台接口配置，去掉 fixed:auto 和静力水准扁平 sensorIds 配置。
|
||
-- 执行前确认平台实例 code，如不是 dcp，请手动替换 @platform_id。
|
||
|
||
SET @platform_id = (SELECT id FROM platform_instance WHERE code = 'dcp' LIMIT 1);
|
||
|
||
UPDATE platform_interface
|
||
SET request_schema = '[
|
||
{"name":"projectId","type":"string","source":"device","field":"platform_project_id","required":true,"desc":"项目ID"},
|
||
{"name":"name", "type":"string","source":"device","field":"serial_number","required":true,"desc":"测点名称，用设备序列号"},
|
||
{"name":"type", "type":"number","source":"fixed","value":3,"required":true,"desc":"测点类型，固定为3"}
|
||
]', update_by = 'system', update_time = NOW()
|
||
WHERE platform_id = @platform_id AND code = 'dcp_create_point';
|
||
|
||
DELETE s FROM platform_flow_step s
|
||
INNER JOIN platform_flow f ON f.id = s.flow_id
|
||
WHERE f.platform_id = @platform_id AND f.code = 'dcp_init' AND s.step_order > 2;
|
||
|
||
UPDATE platform_flow
|
||
SET remark = '获取Token→userId，一次性执行', update_by = 'system', update_time = NOW()
|
||
WHERE platform_id = @platform_id AND code = 'dcp_init';
|
||
|
||
UPDATE platform_interface
|
||
SET request_schema = '[
|
||
{"name":"projectId", "type":"string","source":"device", "field":"platform_project_id", "required":true,"desc":"项目ID"},
|
||
{"name":"pointId", "type":"string","source":"context","contextKey":"step1.id","required":true,"desc":"测点ID，来自创建测点步骤"},
|
||
{"name":"deviceType", "type":"number","source":"fixed", "value":5, "required":true,"desc":"设备类型，倾角=5"},
|
||
{"name":"name", "type":"string","source":"device", "field":"serial_number","required":true,"desc":"传感器名称"},
|
||
{"name":"moduleId", "type":"string","source":"uuid", "required":true,"desc":"模块ID，系统自动生成UUID"},
|
||
{"name":"virtualId", "type":"string","source":"uuid", "required":true,"desc":"虚拟ID，系统自动生成UUID"},
|
||
{"name":"dH", "type":"number","source":"fixed", "value":0, "required":false,"desc":"高程"}
|
||
]',
|
||
update_by = 'system',
|
||
update_time = NOW()
|
||
WHERE platform_id = @platform_id
|
||
AND code = 'dcp_bind_inclino';
|
||
|
||
UPDATE platform_interface
|
||
SET request_schema = '[
|
||
{"name":"projectId", "type":"string","source":"device", "field":"platform_project_id", "required":true,"desc":"项目ID"},
|
||
{"name":"pointId", "type":"string","source":"context","contextKey":"step1.id","required":true,"desc":"测点ID"},
|
||
{"name":"deviceType", "type":"number","source":"fixed", "value":10, "required":true,"desc":"设备类型，轴力计=10"},
|
||
{"name":"name", "type":"string","source":"device", "field":"serial_number","required":true,"desc":"传感器名称"},
|
||
{"name":"factor", "type":"number","source":"fixed", "value":1, "required":false,"desc":"系数"},
|
||
{"name":"moduleId", "type":"string","source":"uuid", "required":true},
|
||
{"name":"virtualId", "type":"string","source":"uuid", "required":true}
|
||
]',
|
||
update_by = 'system',
|
||
update_time = NOW()
|
||
WHERE platform_id = @platform_id
|
||
AND code = 'dcp_bind_stress';
|
||
|
||
UPDATE platform_interface
|
||
SET request_schema = '[
|
||
{"name":"projectId", "type":"string","source":"device", "field":"platform_project_id", "required":true},
|
||
{"name":"pointId", "type":"string","source":"context","contextKey":"step1.id","required":true},
|
||
{"name":"deviceType", "type":"number","source":"fixed", "value":3, "required":true,"desc":"雨量计=3"},
|
||
{"name":"name", "type":"string","source":"device", "field":"serial_number","required":true},
|
||
{"name":"moduleId", "type":"string","source":"uuid", "required":true},
|
||
{"name":"virtualId", "type":"string","source":"uuid", "required":true}
|
||
]',
|
||
update_by = 'system',
|
||
update_time = NOW()
|
||
WHERE platform_id = @platform_id
|
||
AND code = 'dcp_bind_hyeto';
|
||
|
||
UPDATE platform_interface
|
||
SET request_schema = '[
|
||
{"name":"projectId", "type":"string","source":"device", "field":"platform_project_id", "required":true},
|
||
{"name":"pointId", "type":"string","source":"context","contextKey":"step1.id","required":true},
|
||
{"name":"deviceType", "type":"number","source":"fixed", "value":1002, "required":true,"desc":"静力水准=1002"},
|
||
{"name":"name", "type":"string","source":"device", "field":"serial_number","required":true},
|
||
{"name":"moduleId", "type":"string","source":"uuid", "required":true},
|
||
{"name":"virtualId", "type":"string","source":"uuid", "required":true},
|
||
{"name":"sensorIds", "type":"array", "source":"referenceSubDeviceSensorIdPairs","required":true,"desc":"基准点与每个监测点成对绑定"}
|
||
]',
|
||
update_by = 'system',
|
||
update_time = NOW()
|
||
WHERE platform_id = @platform_id
|
||
AND code = 'dcp_bind_static_level';
|
||
|
||
SELECT code, request_schema
|
||
FROM platform_interface
|
||
WHERE platform_id = @platform_id
|
||
AND code IN ('dcp_bind_inclino', 'dcp_bind_stress', 'dcp_bind_hyeto', 'dcp_bind_static_level');
|