yctb-SpringBoot/init_test_data（ai生成测试数据）.sql

-- 初始化测试数据
-- 目标数据库用户: ZJRS_YWXT

-- 清理旧数据，防止主键冲突
-- 删除 FW_OPERATOR 时，同时根据 OPERID 和 LOGINID 删除，防止 UK_FW_OPERATOR_LOGINID 冲突
DELETE FROM ZJRS_YWXT.FW_OPERATOR WHERE OPERID IN ('admin', 'test', 'admin_123456') OR LOGINID IN ('admin', 'test', 'admin_123456');
-- 删除关联权限时，需要先找出可能存在的 OPERID（如果是通过 LOGINID 查找到的旧数据）
-- 这里简单起见，还是根据 OPERID 删除，但在生产环境中应该更严谨
DELETE FROM ZJRS_YWXT.FW_OPERATOR2RIGHT WHERE OPERID IN ('admin', 'test', 'admin_123456');
-- 同时删除即将插入的 ID 1, 2, 3，防止主键冲突
DELETE FROM ZJRS_YWXT.FW_OPERATOR2RIGHT WHERE ID IN (1, 2, 3);
DELETE FROM ZJRS_YWXT.FW_RIGHT WHERE RIGHTID IN ('01', '0101', '0102');
-- 删除旧的字典数据，避免违反唯一约束 UK_AAA100_ITEMCODE
-- 同时删除即将插入的主键 ID，防止 PK_AA10 冲突
DELETE FROM ZJRS_YWXT.AA10 WHERE AAZ093 IN (1001, 1002, 1003, 2001, 2002, 2003, 2004);
DELETE FROM ZJRS_YWXT.AA10 WHERE AAA100 = 'OPERTYPE' AND ITEMCODE IN ('1', '2', '3');
DELETE FROM ZJRS_YWXT.AA10 WHERE AAA100 = 'RIGHTTYPE' AND ITEMCODE IN ('1', '2');
DELETE FROM ZJRS_YWXT.AA10 WHERE AAA100 = 'AUTHTYPE' AND ITEMCODE IN ('1', '2');

DELETE FROM ZJRS_YWXT.FW_OPERATEUNIT WHERE OPERUNITID = 1001;
DELETE FROM ZJRS_YWXT.FW_SYSORG WHERE BAE001 = '440800';

-- 清理异常日志表，防止 ID 冲突 (ORA-00001: PK_FW_LOG4EXCEPTION_ID)
DELETE FROM ZJRS_YWXT.FW_LOG4EXCEPTION;

-- 清理登录日志表 (可选，保持环境干净)
DELETE FROM ZJRS_YWXT.FW_LOG4LOGIN;

-- 解决 UNSAFE_VALIDATEFORM 配置问题
-- 如果数据库中有导致空指针的配置，将其更新为安全值
UPDATE AA01 SET AAA005 = 'NEVER_MATCH_THIS_TOKEN' WHERE AAA001 = 'UNSAFE_VALIDATEFORM' AND (AAA005 IS NULL OR AAA005 = '');

COMMIT;

-- 1. 插入经办单位 (Organization Unit)
-- OPERUNITID: 1001
-- OPERUNITNAME: 湛江市人社局
INSERT INTO ZJRS_YWXT.FW_OPERATEUNIT (BAE001, OPERUNITID, OPERUNITNAME, OPERUNITTYPE, AAE100, BAE006, BAE003)
VALUES ('440800', 1001, '湛江市人社局', '1', '1', 'system', 20231027000000);

-- 2. 插入字典数据 (AA10) - 操作员类型
-- OPERTYPE: 1-超级管理员, 2-系统管理员, 3-普通操作员
INSERT INTO ZJRS_YWXT.AA10 (AAZ093, ITEMCODE, AAA102, AAA100, AAE100, SORTNO)
VALUES (1001, '1', '超级管理员', 'OPERTYPE', '1', '1');
INSERT INTO ZJRS_YWXT.AA10 (AAZ093, ITEMCODE, AAA102, AAA100, AAE100, SORTNO)
VALUES (1002, '2', '系统管理员', 'OPERTYPE', '1', '2');
INSERT INTO ZJRS_YWXT.AA10 (AAZ093, ITEMCODE, AAA102, AAA100, AAE100, SORTNO)
VALUES (1003, '3', '普通操作员', 'OPERTYPE', '1', '3');

-- 3. 插入操作员 (User)
-- 用户1: admin / 密码: admin (明文)
-- PWENCRYPT: 0 (表示未加密)
-- OPERTYPE: 2 (系统管理员)
INSERT INTO ZJRS_YWXT.FW_OPERATOR (
  BAE001, OPERUNITID, OPERID, OPERNAME, OPERTYPE, LOGINID, 
  PASSWORD, PWENCRYPT, AAE100, BAE006, BAE003, OPERTYPE2
) VALUES (
  '440800', 1001, 'admin', '系统管理员', '2', 'admin', 
  'admin', '0', '1', 'system', 20231027000000, '0'
);

-- 用户2: test / 密码: test (明文)
-- PWENCRYPT: 0 (表示未加密，如果系统支持明文登录)
INSERT INTO ZJRS_YWXT.FW_OPERATOR (
  BAE001, OPERUNITID, OPERID, OPERNAME, OPERTYPE, LOGINID, 
  PASSWORD, PWENCRYPT, AAE100, BAE006, BAE003, OPERTYPE2
) VALUES (
  '440800', 1001, 'test', '测试用户', '3', 'test', 
  'test', '0', '1', 'system', 20231027000000, '0'
);

-- 用户3: admin_123456 / 密码: 123456 (明文)
INSERT INTO ZJRS_YWXT.FW_OPERATOR (
  BAE001, OPERUNITID, OPERID, OPERNAME, OPERTYPE, LOGINID, 
  PASSWORD, PWENCRYPT, AAE100, BAE006, BAE003, OPERTYPE2
) VALUES (
  '440800', 1001, 'admin_123456', '管理员123456', '2', 'admin_123456', 
  '123456', '0', '1', 'system', 20231027000000, '0'
);

COMMIT;

-- =================================================================
-- 修复 ORA-00904: "LOCATE" / "F_LENGTH" 无效标识符问题
-- 原因：代码中使用了 MySQL/自定义函数，Oracle 中不存在
-- 解决方案：在数据库中创建兼容函数
-- =================================================================

-- 1. 创建 LOCATE 兼容函数 (模拟 MySQL LOCATE)
CREATE OR REPLACE FUNCTION LOCATE( substr IN VARCHAR2, str IN VARCHAR2 ) RETURN NUMBER IS
BEGIN
  -- 调用 Oracle 内置的 INSTR，注意参数互换位置
  RETURN INSTR(str, substr);
END LOCATE;
/

-- 2. 创建 f_length 兼容函数 (模拟 LENGTH)
CREATE OR REPLACE FUNCTION f_length( str IN VARCHAR2 ) RETURN NUMBER IS
BEGIN
  RETURN LENGTH(str);
END f_length;
/

/*
  ================================================================================
  需从正式环境提取数据的表清单 (System Configuration & Framework Tables)
  请使用数据导出工具 (如 PL/SQL Developer, DataPump) 导出以下表的数据并在测试环境导入。
  ================================================================================

  1. 系统参数与字典 (System Parameters & Dictionaries)
  ----------------------------------------------------
  - AA01                 (系统参数表 - System Parameters)
  - AA10                 (数据字典表 - Data Dictionary)
  - FW_SYSMESSAGE        (系统消息提示信息 - System Messages)
  - FW_HELP              (系统帮助信息 - System Help)
  - FW_BIGDICT_MAINTAIN  (大字典维护表 - Big Dictionary)

  2. 权限与组织架构 (Rights, Roles & Organization)
  ----------------------------------------------------
  - FW_SYSORG            (系统机构表 - System Organization)
  - FW_OPERATEUNIT       (经办单位表 - Operating Units)
  - FW_OPERATOR          (操作员表 - Operators)
  - FW_RIGHT             (菜单与权限表 - Menus & Rights)
  - FW_RIGHT2RESOURCE    (权限资源关联表 - Rights to Resources)
  - FW_ROLE              (角色表 - Roles)
  - FW_ROLE2RIGHT        (角色权限关联表 - Roles to Rights)
  - FW_OPERATOR2RIGHT    (用户权限关联表 - Users to Rights)
  - FW_OPERATOR2ROLE     (用户角色关联表 - Users to Roles)

  3. 界面配置 (UI Configuration)
  ----------------------------------------------------
  - FW_GENLIST_CONF      (通用列表配置表 - General List Config)
  - FW_GENTBL_CONF       (通用表单配置表 - General Table Config)
  - FW_QUERYSQL          (通用查询SQL表 - General Query SQL)

  4. 报表打印 (Reporting & Printing)
  ----------------------------------------------------
  - FW_PRINT_CONF        (打印配置表 - Print Configuration)
  - FW_PRINT_CLIENTCONFIG (客户端打印配置 - Client Print Config)
  - FW_REPORT_CONF       (报表配置表 - Report Configuration)

  5. 工作流 (Workflow - If applicable)
  ----------------------------------------------------
  - WF_ACTION_DEF        (工作流动作定义 - Workflow Actions)
  - WF_ACTION_DEF2TASK   (工作流任务关联 - Workflow Tasks)
  - FW_WF_DESIGNER       (工作流设计器 - Workflow Designer)

  6. 其他 (Others)
  ----------------------------------------------------
  - FW_YXZLINFO_CONF     (影像资料配置 - Image Config)
  - FW_LOG4BUSS          (业务日志 - Business Logs, optional structure)

  导出命令参考 (Oracle Exp):
  exp userid=ZJRS_YWXT/PASSWORD tables=(AA01,AA10,FW_SYSMESSAGE,FW_HELP,FW_BIGDICT_MAINTAIN,FW_SYSORG,FW_OPERATEUNIT,FW_OPERATOR,FW_RIGHT,FW_RIGHT2RESOURCE,FW_ROLE,FW_ROLE2RIGHT,FW_OPERATOR2RIGHT,FW_OPERATOR2ROLE,FW_GENLIST_CONF,FW_GENTBL_CONF,FW_QUERYSQL,FW_PRINT_CONF,FW_PRINT_CLIENTCONFIG,FW_REPORT_CONF,WF_ACTION_DEF,WF_ACTION_DEF2TASK,FW_WF_DESIGNER,FW_YXZLINFO_CONF) file=init_data.dmp
*/