viw_ai_useful_forms.sql
2.49 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
-- ============================================================
-- 视图：viw_ai_useful_forms —— ERP「有用业务表单」目录
-- 合并 gdsmodule(菜单窗体) + gdsconfigformmaster(表单配置)
-- 过滤原则：保召回优先，只剔除"必然无用"的项，再用字段让调用方自行收窄
-- 剔除：失效 / 系统配置源(gds*/sys*) / BI报表 / 首页 / 外链 / 弹窗 / 系统配置窗体
-- 保留：有业务数据源(表/视图/过程)的表单
-- 收窄开关(作为字段暴露，不写死在过滤里，避免误删"明细/查询"类窗体)：
-- bVisibleMenu = 1 → 主菜单可见的窗体(约370张，精度高)
-- bIsGrid = 1 → 列表/表格型窗体(适合出数据列表)
-- 调接口用法：POST /business/getBusinessDataByFormcustomId/{sFormId}?sModelsId={sModuleId}
-- ============================================================
CREATE OR REPLACE VIEW viw_ai_useful_forms AS
SELECT
m.sId AS sModuleId, -- 菜单窗体id (= 接口 sModelsId / ai_tool.sSrcFormId)
m.sChinese AS sModuleName, -- 窗体/菜单名称
m.sModelType AS sModelType, -- 前端路由/业务域(可能为空)
f.sId AS sFormId, -- 表单id (= 接口路径 gdsconfigformmasterId)
f.sChinese AS sFormTitle, -- 表单标题
f.sTbName AS sDataSource, -- 数据源(表/视图/过程)
f.sType AS sExecType, -- 执行类型 table/view/proc
CAST(f.bGrd AS UNSIGNED) AS bIsGrid, -- 1=列表型窗体, 0=明细型 (收窄开关)
CAST(m.bVisible AS UNSIGNED) AS bVisibleMenu, -- 1=主菜单可见, 0=次级/查询窗体 (收窄开关)
m.iOrder AS iOrder -- 菜单排序
FROM gdsmodule m
JOIN gdsconfigformmaster f ON f.sParentId = m.sId
WHERE IFNULL(m.bInvalid, 0) = 0
AND IFNULL(f.sTbName, '') <> ''
AND f.sType IN ('table', 'view', 'proc')
AND f.sTbName NOT LIKE 'gds%'
AND f.sTbName NOT LIKE 'sys%'
AND IFNULL(m.sModelType, '') NOT LIKE 'commonAuto/Bi%'
AND IFNULL(m.sModelType, '') NOT LIKE '%IndexPage'
AND IFNULL(m.sModelType, '') NOT LIKE 'http%'
AND IFNULL(m.sModelType, '') NOT LIKE '%ipAddress%'
AND IFNULL(m.sModelType, '') NOT LIKE 'modal/%'
AND IFNULL(m.sModelType, '') NOT IN (
'gdsmodule', 'gdsconfigformmaster', 'gdsconfigtbmaster',
'gdsformconst', 'gdsjurisdiction', 'gdsconfigcharmaster', 'gdsinterfacemaster'
);