1672 lines
107 KiB
SQL
1672 lines
107 KiB
SQL
-- Kaidi Finance V073 全功能状态演示数据
|
|
-- 兼容：MySQL 8.4.x + Flyway 版本 V073/V074（已在 Kaidi Finance Preview 49 验证）
|
|
-- 用途：在已完成 /setup、但尚无业务数据的数据库中导入展示数据。
|
|
--
|
|
-- 导入示例：
|
|
-- mysql --default-character-set=utf8mb4 -h HOST -P PORT -u USER -p DATABASE \
|
|
-- < demo-data-mysql8-v073.sql
|
|
--
|
|
-- 安全边界：
|
|
-- 1. 本脚本不创建可登录账号，只新建 3 个禁用的演示操作人。
|
|
-- 2. 本脚本不修改已有业务记录，所有演示编号均以 DEMO-V073 开头。
|
|
-- 3. 本脚本不写入真实附件字节；文件表中的记录仅用于展示状态，点击下载可能返回“文件不存在”。
|
|
-- 4. 本数据集只允许导入一次；重复导入会在前置检查阶段停止。
|
|
-- 5. 不要使用 --force 忽略 SQL 错误；本文件使用事务保证导入的原子性。
|
|
|
|
SET NAMES utf8mb4;
|
|
|
|
SET @demo_dataset_code := 'KAIDI_DEMO_V073';
|
|
SET @demo_marker_public_id := CONCAT('D', LPAD(99999, 25, '0'));
|
|
SET @demo_now := UTC_TIMESTAMP(3);
|
|
SET @demo_today := DATE(UTC_TIMESTAMP());
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- 0. 强制前置检查：MySQL 8.4、V073/V074、有效超级管理员、未重复导入
|
|
-- ---------------------------------------------------------------------------
|
|
CREATE TEMPORARY TABLE kaidi_demo_v073_guard (
|
|
check_name VARCHAR(64) NOT NULL,
|
|
ok TINYINT NOT NULL,
|
|
CONSTRAINT ck_kaidi_demo_v073_guard CHECK (ok = 1)
|
|
) ENGINE=InnoDB;
|
|
|
|
INSERT INTO kaidi_demo_v073_guard (check_name, ok)
|
|
SELECT 'MYSQL_8_4', IF(VERSION() REGEXP '^8\\.4\\.', 1, 0);
|
|
|
|
INSERT INTO kaidi_demo_v073_guard (check_name, ok)
|
|
SELECT 'FLYWAY_COMPATIBLE_V073_V074', IF(COALESCE((
|
|
SELECT version
|
|
FROM flyway_schema_history
|
|
WHERE success = 1 AND version IS NOT NULL
|
|
ORDER BY installed_rank DESC
|
|
LIMIT 1
|
|
), '') IN ('73', '073', '74', '074'), 1, 0);
|
|
|
|
INSERT INTO kaidi_demo_v073_guard (check_name, ok)
|
|
SELECT 'SYSTEM_ADMIN_EXISTS', IF(EXISTS (
|
|
SELECT 1
|
|
FROM iam_user user_record
|
|
JOIN iam_user_role user_role ON user_role.user_id = user_record.id
|
|
JOIN iam_role role_record ON role_record.id = user_role.role_id
|
|
WHERE role_record.code = 'SYSTEM_ADMIN'
|
|
AND role_record.enabled = TRUE
|
|
AND user_record.enabled = TRUE
|
|
), 1, 0);
|
|
|
|
INSERT INTO kaidi_demo_v073_guard (check_name, ok)
|
|
SELECT 'DATASET_NOT_IMPORTED', IF(NOT EXISTS (
|
|
SELECT 1
|
|
FROM audit_log
|
|
WHERE action_code = 'DEMO_DATA_IMPORT'
|
|
AND object_type = 'DEMO_DATASET'
|
|
AND object_public_id = @demo_marker_public_id
|
|
), 1, 0);
|
|
|
|
DROP TEMPORARY TABLE kaidi_demo_v073_guard;
|
|
|
|
SET @demo_admin_id := (
|
|
SELECT user_record.id
|
|
FROM iam_user user_record
|
|
JOIN iam_user_role user_role ON user_role.user_id = user_record.id
|
|
JOIN iam_role role_record ON role_record.id = user_role.role_id
|
|
WHERE role_record.code = 'SYSTEM_ADMIN'
|
|
AND role_record.enabled = TRUE
|
|
AND user_record.enabled = TRUE
|
|
ORDER BY user_record.id
|
|
LIMIT 1
|
|
);
|
|
SET @demo_admin_public_id := (SELECT public_id FROM iam_user WHERE id = @demo_admin_id);
|
|
SET @demo_admin_username := (SELECT username FROM iam_user WHERE id = @demo_admin_id);
|
|
|
|
START TRANSACTION;
|
|
|
|
-- 保证安装后新建的 SYSTEM_ADMIN 具有所有已有权限和全局范围。
|
|
-- 使用 NOT EXISTS 而不是 INSERT IGNORE，避免在已完成安装的数据库中制造重复键警告。
|
|
INSERT INTO iam_role_permission (role_id, permission_id)
|
|
SELECT role_record.id, permission_record.id
|
|
FROM iam_role role_record
|
|
CROSS JOIN iam_permission permission_record
|
|
WHERE role_record.code = 'SYSTEM_ADMIN'
|
|
AND role_record.enabled = TRUE
|
|
AND NOT EXISTS (
|
|
SELECT 1
|
|
FROM iam_role_permission existing_permission
|
|
WHERE existing_permission.role_id = role_record.id
|
|
AND existing_permission.permission_id = permission_record.id
|
|
);
|
|
|
|
INSERT INTO iam_scope (
|
|
user_id, role_id, permission_id, scope_type,
|
|
company_public_id, project_public_id, amount_limit, status
|
|
)
|
|
SELECT user_role.user_id, user_role.role_id, role_permission.permission_id,
|
|
'GLOBAL', NULL, NULL, NULL, 'ACTIVE'
|
|
FROM iam_user_role user_role
|
|
JOIN iam_role role_record ON role_record.id = user_role.role_id
|
|
JOIN iam_role_permission role_permission ON role_permission.role_id = role_record.id
|
|
WHERE user_role.user_id = @demo_admin_id
|
|
AND role_record.code = 'SYSTEM_ADMIN'
|
|
AND role_record.enabled = TRUE
|
|
AND NOT EXISTS (
|
|
SELECT 1
|
|
FROM iam_scope existing_scope
|
|
WHERE existing_scope.user_id = user_role.user_id
|
|
AND existing_scope.role_id = user_role.role_id
|
|
AND existing_scope.permission_id = role_permission.permission_id
|
|
AND existing_scope.scope_type = 'GLOBAL'
|
|
AND existing_scope.company_public_id IS NULL
|
|
AND existing_scope.project_public_id IS NULL
|
|
);
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- 1. 禁用的演示操作人：只用于显示发起人、复核人、档案人员
|
|
-- ---------------------------------------------------------------------------
|
|
INSERT INTO iam_user (
|
|
public_id, username, display_name, password_hash, department_name,
|
|
enabled, must_change_password, failed_login_count, version
|
|
)
|
|
VALUES
|
|
(CONCAT('D', LPAD(1, 25, '0')), 'demo_v073_project_actor', '演示项目发起人', '!KAIDI_DEMO_DISABLED!', '项目部', FALSE, TRUE, 0, 0),
|
|
(CONCAT('D', LPAD(2, 25, '0')), 'demo_v073_finance_actor', '演示财务复核人', '!KAIDI_DEMO_DISABLED!', '财务部', FALSE, TRUE, 0, 0),
|
|
(CONCAT('D', LPAD(3, 25, '0')), 'demo_v073_archive_actor', '演示档案管理人', '!KAIDI_DEMO_DISABLED!', '资料档案部', FALSE, TRUE, 0, 0);
|
|
|
|
SET @demo_project_actor_id := (SELECT id FROM iam_user WHERE username = 'demo_v073_project_actor');
|
|
SET @demo_finance_actor_id := (SELECT id FROM iam_user WHERE username = 'demo_v073_finance_actor');
|
|
SET @demo_archive_actor_id := (SELECT id FROM iam_user WHERE username = 'demo_v073_archive_actor');
|
|
|
|
INSERT INTO iam_user_role (user_id, role_id)
|
|
SELECT @demo_project_actor_id, id FROM iam_role WHERE code = 'PROJECT_MANAGER'
|
|
UNION ALL
|
|
SELECT @demo_finance_actor_id, id FROM iam_role WHERE code = 'FINANCE_MANAGER'
|
|
UNION ALL
|
|
SELECT @demo_archive_actor_id, id FROM iam_role WHERE code = 'ARCHIVE_MANAGER';
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- 2. 文件状态（只有元数据，无真实文件字节）
|
|
-- ---------------------------------------------------------------------------
|
|
INSERT INTO file_object (
|
|
public_id, original_name, storage_key, media_type, extension, size_bytes,
|
|
sha256, scan_status, scan_detail, scanned_at, uploaded_by
|
|
)
|
|
SELECT CONCAT('D', LPAD(seed.seq, 25, '0')),
|
|
seed.original_name,
|
|
CONCAT('demo-v073/', seed.storage_name),
|
|
seed.media_type,
|
|
seed.extension,
|
|
seed.size_bytes,
|
|
SHA2(CONCAT('KAIDI-DEMO-V073:', seed.storage_name), 256),
|
|
seed.scan_status,
|
|
seed.scan_detail,
|
|
CASE WHEN seed.scan_status = 'SCANNING' THEN NULL ELSE @demo_now END,
|
|
@demo_admin_public_id
|
|
FROM (
|
|
SELECT 100 AS seq, '01-扫描中附件.pdf' AS original_name, '01-scanning.pdf' AS storage_name,
|
|
'application/pdf' AS media_type, 'pdf' AS extension, 1024 AS size_bytes,
|
|
'SCANNING' AS scan_status, '正在扫描，用于展示检查中状态' AS scan_detail
|
|
UNION ALL SELECT 101, '02-可用演示附件.pdf', '02-available.pdf', 'application/pdf', 'pdf', 2048,
|
|
'AVAILABLE', '演示元数据：SQL 不包含真实文件字节'
|
|
UNION ALL SELECT 102, '03-被拒绝附件.exe', '03-rejected.exe', 'application/octet-stream', 'exe', 4096,
|
|
'REJECTED', '文件类型不允许'
|
|
UNION ALL SELECT 103, '04-隔离中附件.pdf', '04-quarantined.pdf', 'application/pdf', 'pdf', 1536,
|
|
'QUARANTINED', '扫描服务不可用，文件已隔离'
|
|
UNION ALL SELECT 104, '05-已被替代附件.pdf', '05-superseded.pdf', 'application/pdf', 'pdf', 1024,
|
|
'SUPERSEDED', '已由新版本替代'
|
|
UNION ALL SELECT 105, '06-项目导入.xlsx', '06-project-import.xlsx',
|
|
'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet', 'xlsx', 8192,
|
|
'AVAILABLE', '项目导入演示文件'
|
|
UNION ALL SELECT 106, '07-OA导入.xlsx', '07-source-import.xlsx',
|
|
'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet', 'xlsx', 8192,
|
|
'AVAILABLE', 'OA 表单导入演示文件'
|
|
UNION ALL SELECT 107, '08-付款回单.pdf', '08-payment-receipt.pdf', 'application/pdf', 'pdf', 3072,
|
|
'AVAILABLE', '付款结果回单演示元数据'
|
|
UNION ALL SELECT 108, '09-记账导出.csv', '09-accounting-export.csv', 'text/csv', 'csv', 2048,
|
|
'AVAILABLE', '记账凭证导出演示元数据'
|
|
UNION ALL SELECT 109, '10-记账结果.pdf', '10-accounting-result.pdf', 'application/pdf', 'pdf', 4096,
|
|
'AVAILABLE', '记账结果证据演示元数据'
|
|
UNION ALL SELECT 110, '11-档案资料.pdf', '11-archive-proof.pdf', 'application/pdf', 'pdf', 4096,
|
|
'AVAILABLE', '档案和借阅演示元数据'
|
|
UNION ALL SELECT 111, '12-档案资料新版.pdf', '12-archive-proof-v2.pdf', 'application/pdf', 'pdf', 5120,
|
|
'AVAILABLE', '文件版本替代演示元数据'
|
|
UNION ALL SELECT 112, '13-报表导出.csv', '13-report-export.csv', 'text/csv', 'csv', 2048,
|
|
'AVAILABLE', '报表导出演示元数据'
|
|
UNION ALL SELECT 113, '14-借阅资料-A.pdf', '14-borrow-a.pdf', 'application/pdf', 'pdf', 2048,
|
|
'AVAILABLE', '借阅状态演示 A'
|
|
UNION ALL SELECT 114, '15-借阅资料-B.pdf', '15-borrow-b.pdf', 'application/pdf', 'pdf', 2048,
|
|
'AVAILABLE', '借阅状态演示 B'
|
|
UNION ALL SELECT 115, '16-借阅资料-C.pdf', '16-borrow-c.pdf', 'application/pdf', 'pdf', 2048,
|
|
'AVAILABLE', '借阅状态演示 C'
|
|
UNION ALL SELECT 116, '17-借阅资料-D.pdf', '17-borrow-d.pdf', 'application/pdf', 'pdf', 2048,
|
|
'AVAILABLE', '借阅状态演示 D'
|
|
UNION ALL SELECT 117, '18-借阅资料-E.pdf', '18-borrow-e.pdf', 'application/pdf', 'pdf', 2048,
|
|
'AVAILABLE', '借阅状态演示 E'
|
|
UNION ALL SELECT 118, '19-借阅资料-F.pdf', '19-borrow-f.pdf', 'application/pdf', 'pdf', 2048,
|
|
'AVAILABLE', '借阅状态演示 F'
|
|
) seed;
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- 3. 公司、往来单位、部门、成本分类的全状态
|
|
-- ---------------------------------------------------------------------------
|
|
INSERT INTO md_company (
|
|
public_id, business_no, name, status, created_by, updated_by,
|
|
submitted_by, submitted_at, reviewed_by, reviewed_at, review_opinion
|
|
)
|
|
SELECT CONCAT('D', LPAD(seed.seq, 25, '0')), seed.business_no, seed.name, seed.status,
|
|
@demo_project_actor_id, @demo_finance_actor_id,
|
|
CASE WHEN seed.status IN ('REVIEWING', 'RETURNED', 'ACTIVE', 'DISABLED') THEN @demo_project_actor_id END,
|
|
CASE WHEN seed.status IN ('REVIEWING', 'RETURNED', 'ACTIVE', 'DISABLED') THEN DATE_SUB(@demo_now, INTERVAL 4 DAY) END,
|
|
CASE WHEN seed.status IN ('RETURNED', 'ACTIVE', 'DISABLED') THEN @demo_finance_actor_id END,
|
|
CASE WHEN seed.status IN ('RETURNED', 'ACTIVE', 'DISABLED') THEN DATE_SUB(@demo_now, INTERVAL 3 DAY) END,
|
|
CASE
|
|
WHEN seed.status = 'RETURNED' THEN '税务资料需要补充'
|
|
WHEN seed.status = 'DISABLED' THEN '演示停用的历史公司'
|
|
WHEN seed.status = 'ACTIVE' THEN '复核通过'
|
|
END
|
|
FROM (
|
|
SELECT 200 AS seq, 'DEMO-V073-COMPANY-DRAFT' AS business_no, '演示公司（草稿）' AS name, 'DRAFT' AS status
|
|
UNION ALL SELECT 201, 'DEMO-V073-COMPANY-REVIEW', '演示公司（待复核）', 'REVIEWING'
|
|
UNION ALL SELECT 202, 'DEMO-V073-COMPANY-RETURN', '演示公司（已退回）', 'RETURNED'
|
|
UNION ALL SELECT 203, 'DEMO-V073-COMPANY-ACTIVE', '凯迪演示建设有限公司', 'ACTIVE'
|
|
UNION ALL SELECT 204, 'DEMO-V073-COMPANY-DISABLED', '演示公司（已停用）', 'DISABLED'
|
|
) seed;
|
|
|
|
SET @demo_company_id := (SELECT id FROM md_company WHERE business_no = 'DEMO-V073-COMPANY-ACTIVE');
|
|
SET @demo_company_public_id := (SELECT public_id FROM md_company WHERE id = @demo_company_id);
|
|
|
|
INSERT INTO md_counterparty (
|
|
public_id, business_no, counterparty_type, name, status,
|
|
created_by, updated_by, submitted_by, submitted_at,
|
|
reviewed_by, reviewed_at, review_opinion
|
|
)
|
|
SELECT CONCAT('D', LPAD(seed.seq, 25, '0')), seed.business_no, seed.counterparty_type,
|
|
seed.name, seed.status, @demo_project_actor_id, @demo_finance_actor_id,
|
|
CASE WHEN seed.status IN ('REVIEWING', 'RETURNED', 'ACTIVE', 'DISABLED') THEN @demo_project_actor_id END,
|
|
CASE WHEN seed.status IN ('REVIEWING', 'RETURNED', 'ACTIVE', 'DISABLED') THEN DATE_SUB(@demo_now, INTERVAL 4 DAY) END,
|
|
CASE WHEN seed.status IN ('RETURNED', 'ACTIVE', 'DISABLED') THEN @demo_finance_actor_id END,
|
|
CASE WHEN seed.status IN ('RETURNED', 'ACTIVE', 'DISABLED') THEN DATE_SUB(@demo_now, INTERVAL 3 DAY) END,
|
|
CASE WHEN seed.status = 'RETURNED' THEN '资质证明待补充'
|
|
WHEN seed.status = 'DISABLED' THEN '历史单位已停用'
|
|
WHEN seed.status = 'ACTIVE' THEN '复核通过' END
|
|
FROM (
|
|
SELECT 210 AS seq, 'DEMO-V073-CP-DRAFT' AS business_no, 'SUPPLIER' AS counterparty_type,
|
|
'演示供应商（草稿）' AS name, 'DRAFT' AS status
|
|
UNION ALL SELECT 211, 'DEMO-V073-CP-REVIEW', 'CUSTOMER', '演示客户（待复核）', 'REVIEWING'
|
|
UNION ALL SELECT 212, 'DEMO-V073-CP-RETURN', 'SUPPLIER', '演示供应商（已退回）', 'RETURNED'
|
|
UNION ALL SELECT 213, 'DEMO-V073-CP-ACTIVE', 'BOTH', '中城演示工程有限公司', 'ACTIVE'
|
|
UNION ALL SELECT 214, 'DEMO-V073-CP-DISABLED', 'SUPPLIER', '演示供应商（已停用）', 'DISABLED'
|
|
) seed;
|
|
|
|
SET @demo_counterparty_id := (SELECT id FROM md_counterparty WHERE business_no = 'DEMO-V073-CP-ACTIVE');
|
|
SET @demo_counterparty_public_id := (SELECT public_id FROM md_counterparty WHERE id = @demo_counterparty_id);
|
|
|
|
INSERT INTO md_department (
|
|
public_id, code, name, status, created_by, updated_by,
|
|
submitted_by, submitted_at, reviewed_by, reviewed_at, review_opinion
|
|
)
|
|
SELECT CONCAT('D', LPAD(seed.seq, 25, '0')), seed.code, seed.name, seed.status,
|
|
@demo_project_actor_id, @demo_finance_actor_id,
|
|
CASE WHEN seed.status IN ('REVIEWING', 'RETURNED', 'ACTIVE', 'DISABLED') THEN @demo_project_actor_id END,
|
|
CASE WHEN seed.status IN ('REVIEWING', 'RETURNED', 'ACTIVE', 'DISABLED') THEN DATE_SUB(@demo_now, INTERVAL 4 DAY) END,
|
|
CASE WHEN seed.status IN ('RETURNED', 'ACTIVE', 'DISABLED') THEN @demo_finance_actor_id END,
|
|
CASE WHEN seed.status IN ('RETURNED', 'ACTIVE', 'DISABLED') THEN DATE_SUB(@demo_now, INTERVAL 3 DAY) END,
|
|
CASE WHEN seed.status = 'RETURNED' THEN '部门负责人信息待补充'
|
|
WHEN seed.status = 'ACTIVE' THEN '复核通过'
|
|
WHEN seed.status = 'DISABLED' THEN '历史部门已停用' END
|
|
FROM (
|
|
SELECT 220 AS seq, 'DEMO-V073-DEPT-DRAFT' AS code, '演示部门（草稿）' AS name, 'DRAFT' AS status
|
|
UNION ALL SELECT 221, 'DEMO-V073-DEPT-REVIEW', '演示部门（待复核）', 'REVIEWING'
|
|
UNION ALL SELECT 222, 'DEMO-V073-DEPT-RETURN', '演示部门（已退回）', 'RETURNED'
|
|
UNION ALL SELECT 223, 'DEMO-V073-DEPT-ACTIVE', '演示项目管理部', 'ACTIVE'
|
|
UNION ALL SELECT 224, 'DEMO-V073-DEPT-DISABLED', '演示部门（已停用）', 'DISABLED'
|
|
) seed;
|
|
|
|
INSERT INTO md_cost_category (
|
|
public_id, code, name, status, created_by, updated_by,
|
|
submitted_by, submitted_at, reviewed_by, reviewed_at, review_opinion
|
|
)
|
|
SELECT CONCAT('D', LPAD(seed.seq, 25, '0')), seed.code, seed.name, seed.status,
|
|
@demo_project_actor_id, @demo_finance_actor_id,
|
|
CASE WHEN seed.status IN ('REVIEWING', 'RETURNED', 'ACTIVE', 'DISABLED') THEN @demo_project_actor_id END,
|
|
CASE WHEN seed.status IN ('REVIEWING', 'RETURNED', 'ACTIVE', 'DISABLED') THEN DATE_SUB(@demo_now, INTERVAL 4 DAY) END,
|
|
CASE WHEN seed.status IN ('RETURNED', 'ACTIVE', 'DISABLED') THEN @demo_finance_actor_id END,
|
|
CASE WHEN seed.status IN ('RETURNED', 'ACTIVE', 'DISABLED') THEN DATE_SUB(@demo_now, INTERVAL 3 DAY) END,
|
|
CASE WHEN seed.status = 'RETURNED' THEN '成本分类层级待确认'
|
|
WHEN seed.status = 'ACTIVE' THEN '复核通过'
|
|
WHEN seed.status = 'DISABLED' THEN '历史分类已停用' END
|
|
FROM (
|
|
SELECT 230 AS seq, 'DEMO-V073-COST-DRAFT' AS code, '演示成本（草稿）' AS name, 'DRAFT' AS status
|
|
UNION ALL SELECT 231, 'DEMO-V073-COST-REVIEW', '演示成本（待复核）', 'REVIEWING'
|
|
UNION ALL SELECT 232, 'DEMO-V073-COST-RETURN', '演示成本（已退回）', 'RETURNED'
|
|
UNION ALL SELECT 233, 'DEMO-V073-COST-ACTIVE', '演示专项成本', 'ACTIVE'
|
|
UNION ALL SELECT 234, 'DEMO-V073-COST-DISABLED', '演示成本（已停用）', 'DISABLED'
|
|
) seed;
|
|
|
|
SET @demo_cost_category_id := (SELECT id FROM md_cost_category WHERE code = 'MATERIAL' AND status = 'ACTIVE' LIMIT 1);
|
|
SET @demo_cost_category_public_id := (SELECT public_id FROM md_cost_category WHERE id = @demo_cost_category_id);
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- 4. 项目、资金控制、风险、银行账户、合同全状态
|
|
-- ---------------------------------------------------------------------------
|
|
INSERT INTO md_project (
|
|
public_id, company_id, business_no, name, description, customer_id, owner_user_id,
|
|
start_date, end_date, currency, stage, finance_status, archive_status, status,
|
|
created_by, updated_by
|
|
)
|
|
SELECT CONCAT('D', LPAD(seed.seq, 25, '0')), @demo_company_id,
|
|
seed.business_no, seed.name, seed.description, @demo_counterparty_id,
|
|
@demo_project_actor_id, DATE_SUB(@demo_today, INTERVAL seed.start_days DAY),
|
|
DATE_ADD(@demo_today, INTERVAL seed.end_days DAY), 'CNY', seed.stage,
|
|
seed.finance_status, seed.archive_status, seed.status,
|
|
@demo_project_actor_id, @demo_finance_actor_id
|
|
FROM (
|
|
SELECT 300 AS seq, 'DEMO-V073-PROJECT-MAIN' AS business_no, '凯迪财务全链路演示项目' AS name,
|
|
'用于展示 OA、合同、收付款、记账、档案和报表的主项目' AS description,
|
|
180 AS start_days, 180 AS end_days, 'EXECUTION' AS stage, 'IN_PROGRESS' AS finance_status,
|
|
'PREPARING' AS archive_status, 'ACTIVE' AS status
|
|
UNION ALL SELECT 301, 'DEMO-V073-PROJECT-INIT', '演示项目（立项）', '展示立项阶段', 10, 350,
|
|
'INITIATION', 'PENDING', 'NOT_READY', 'ACTIVE'
|
|
UNION ALL SELECT 302, 'DEMO-V073-PROJECT-CONTRACT', '演示项目（签约）', '展示签约阶段', 60, 300,
|
|
'CONTRACTING', 'IN_PROGRESS', 'NOT_READY', 'ACTIVE'
|
|
UNION ALL SELECT 303, 'DEMO-V073-PROJECT-ACCEPT', '演示项目（验收）', '展示验收和财务阻断', 300, 30,
|
|
'ACCEPTANCE', 'BLOCKED', 'CHECKING', 'ACTIVE'
|
|
UNION ALL SELECT 304, 'DEMO-V073-PROJECT-SETTLE', '演示项目（结算）', '展示结算和档案退回', 500, 10,
|
|
'SETTLEMENT', 'COMPLETED', 'RETURNED', 'ACTIVE'
|
|
UNION ALL SELECT 305, 'DEMO-V073-PROJECT-CLOSED', '演示项目（已关闭）', '展示关闭和停用状态', 800, -30,
|
|
'CLOSED', 'COMPLETED', 'ARCHIVED', 'DISABLED'
|
|
) seed;
|
|
|
|
SET @demo_project_id := (SELECT id FROM md_project WHERE business_no = 'DEMO-V073-PROJECT-MAIN');
|
|
SET @demo_project_public_id := (SELECT public_id FROM md_project WHERE id = @demo_project_id);
|
|
|
|
INSERT INTO project_fund_control (
|
|
project_id, currency, confirmed_receipt, confirmed_deduction, approved_adjustment,
|
|
frozen_amount, approved_unpaid, paid_amount, version
|
|
)
|
|
SELECT id, currency,
|
|
CASE WHEN business_no = 'DEMO-V073-PROJECT-MAIN' THEN 1500000.00 ELSE 100000.00 END,
|
|
CASE WHEN business_no = 'DEMO-V073-PROJECT-MAIN' THEN 100000.00 ELSE 0.00 END,
|
|
CASE WHEN business_no = 'DEMO-V073-PROJECT-MAIN' THEN 50000.00 ELSE 0.00 END,
|
|
CASE WHEN business_no = 'DEMO-V073-PROJECT-MAIN' THEN 150000.00 ELSE 0.00 END,
|
|
CASE WHEN business_no = 'DEMO-V073-PROJECT-MAIN' THEN 200000.00 ELSE 0.00 END,
|
|
CASE WHEN business_no = 'DEMO-V073-PROJECT-MAIN' THEN 300000.00 ELSE 0.00 END,
|
|
CASE WHEN business_no = 'DEMO-V073-PROJECT-MAIN' THEN 9 ELSE 1 END
|
|
FROM md_project
|
|
WHERE business_no LIKE 'DEMO-V073-PROJECT-%';
|
|
|
|
INSERT INTO project_risk_flag (
|
|
public_id, project_id, risk_type, reason, effective_from, effective_until,
|
|
blocking, status, resolved_reason, resolved_by, resolved_at, created_by, updated_by
|
|
)
|
|
VALUES
|
|
(CONCAT('D', LPAD(310, 25, '0')), @demo_project_id, 'PAYMENT_RISK', '存在待复核的付款资料',
|
|
DATE_SUB(@demo_now, INTERVAL 2 DAY), DATE_ADD(@demo_now, INTERVAL 30 DAY), TRUE, 'ACTIVE',
|
|
NULL, NULL, NULL, @demo_finance_actor_id, @demo_finance_actor_id),
|
|
(CONCAT('D', LPAD(311, 25, '0')), @demo_project_id, 'CONTRACT_RISK', '合同风险已解除',
|
|
DATE_SUB(@demo_now, INTERVAL 20 DAY), @demo_now, FALSE, 'RESOLVED',
|
|
'已完成合同补充协议复核', @demo_finance_actor_id, @demo_now,
|
|
@demo_finance_actor_id, @demo_finance_actor_id),
|
|
(CONCAT('D', LPAD(312, 25, '0')), @demo_project_id, 'ARCHIVE_RISK', '档案补件提醒已过期',
|
|
DATE_SUB(@demo_now, INTERVAL 60 DAY), DATE_SUB(@demo_now, INTERVAL 1 DAY), FALSE, 'EXPIRED',
|
|
NULL, NULL, NULL, @demo_archive_actor_id, @demo_archive_actor_id);
|
|
|
|
-- 账号密文不是真实银行账号；列表只显示脱敏值。
|
|
INSERT INTO md_bank_account_version (
|
|
public_id, owner_type, counterparty_id, account_category, account_name, bank_name,
|
|
account_no_ciphertext, account_no_hash, masked_account_no, version_no, valid_from,
|
|
status, created_by, updated_by, submitted_by, submitted_at,
|
|
reviewed_by, reviewed_at, review_opinion
|
|
)
|
|
VALUES (
|
|
CONCAT('D', LPAD(320, 25, '0')), 'COUNTERPARTY', @demo_counterparty_id, 'BASIC',
|
|
'中城演示工程有限公司', '演示银行城中支行',
|
|
UNHEX(REPEAT('00', 32)), SHA2('KAIDI-DEMO-V073-ACCOUNT', 256), '**** **** 0730', 1,
|
|
DATE_SUB(@demo_now, INTERVAL 1 YEAR), 'ACTIVE', @demo_project_actor_id, @demo_finance_actor_id,
|
|
@demo_project_actor_id, DATE_SUB(@demo_now, INTERVAL 10 DAY), @demo_finance_actor_id,
|
|
DATE_SUB(@demo_now, INTERVAL 9 DAY), '演示账户复核通过'
|
|
);
|
|
SET @demo_bank_account_id := (SELECT id FROM md_bank_account_version WHERE public_id = CONCAT('D', LPAD(320, 25, '0')));
|
|
|
|
INSERT INTO md_contract (
|
|
public_id, company_id, project_id, counterparty_id, business_no, name,
|
|
original_amount, approved_change_amount, settlement_amount, currency, status,
|
|
created_by, updated_by, submitted_by, submitted_at, reviewed_by, reviewed_at, review_opinion
|
|
)
|
|
SELECT CONCAT('D', LPAD(seed.seq, 25, '0')), @demo_company_id, @demo_project_id,
|
|
@demo_counterparty_id, seed.business_no, seed.name, seed.original_amount,
|
|
seed.change_amount, seed.settlement_amount, 'CNY', seed.status,
|
|
@demo_project_actor_id, @demo_finance_actor_id,
|
|
CASE WHEN seed.status IN ('REVIEWING', 'RETURNED', 'ACTIVE', 'SETTLED', 'DISABLED', 'VOID') THEN @demo_project_actor_id END,
|
|
CASE WHEN seed.status IN ('REVIEWING', 'RETURNED', 'ACTIVE', 'SETTLED', 'DISABLED', 'VOID') THEN DATE_SUB(@demo_now, INTERVAL 8 DAY) END,
|
|
CASE WHEN seed.status IN ('RETURNED', 'ACTIVE', 'SETTLED', 'DISABLED', 'VOID') THEN @demo_finance_actor_id END,
|
|
CASE WHEN seed.status IN ('RETURNED', 'ACTIVE', 'SETTLED', 'DISABLED', 'VOID') THEN DATE_SUB(@demo_now, INTERVAL 7 DAY) END,
|
|
CASE WHEN seed.status = 'RETURNED' THEN '合同附件待补充'
|
|
WHEN seed.status IN ('ACTIVE', 'SETTLED') THEN '复核通过'
|
|
WHEN seed.status IN ('DISABLED', 'VOID') THEN '历史合同终止' END
|
|
FROM (
|
|
SELECT 330 AS seq, 'DEMO-V073-CONTRACT-DRAFT' AS business_no, '演示合同（草稿）' AS name,
|
|
300000.00 AS original_amount, 0.00 AS change_amount, NULL AS settlement_amount, 'DRAFT' AS status
|
|
UNION ALL SELECT 331, 'DEMO-V073-CONTRACT-REVIEW', '演示合同（待复核）', 350000.00, 0.00, NULL, 'REVIEWING'
|
|
UNION ALL SELECT 332, 'DEMO-V073-CONTRACT-RETURN', '演示合同（已退回）', 400000.00, 0.00, NULL, 'RETURNED'
|
|
UNION ALL SELECT 333, 'DEMO-V073-CONTRACT-ACTIVE', '凯迪全链路演示合同', 2000000.00, 200000.00, NULL, 'ACTIVE'
|
|
UNION ALL SELECT 334, 'DEMO-V073-CONTRACT-SETTLED', '演示合同（已结算）', 500000.00, 50000.00, 530000.00, 'SETTLED'
|
|
UNION ALL SELECT 335, 'DEMO-V073-CONTRACT-DISABLED', '演示合同（已停用）', 200000.00, 0.00, NULL, 'DISABLED'
|
|
UNION ALL SELECT 336, 'DEMO-V073-CONTRACT-VOID', '演示合同（已作废）', 100000.00, 0.00, NULL, 'VOID'
|
|
) seed;
|
|
|
|
SET @demo_contract_id := (SELECT id FROM md_contract WHERE business_no = 'DEMO-V073-CONTRACT-ACTIVE');
|
|
SET @demo_contract_public_id := (SELECT public_id FROM md_contract WHERE id = @demo_contract_id);
|
|
|
|
-- 保证记账演示需要的会计科目和税率存在；已有同编码时不改动旧数据。
|
|
INSERT INTO md_account (
|
|
public_id, code, name, account_type, auxiliary_required, status,
|
|
created_by, updated_by, submitted_by, submitted_at, reviewed_by, reviewed_at, review_opinion
|
|
)
|
|
SELECT CONCAT('D', LPAD(seed.seq, 25, '0')), seed.code, seed.name, seed.account_type,
|
|
seed.auxiliary_required, 'ACTIVE', @demo_project_actor_id, @demo_finance_actor_id,
|
|
@demo_project_actor_id, DATE_SUB(@demo_now, INTERVAL 20 DAY),
|
|
@demo_finance_actor_id, DATE_SUB(@demo_now, INTERVAL 19 DAY), '复核通过'
|
|
FROM (
|
|
SELECT 340 AS seq, '1002' AS code, '银行存款' AS name, 'ASSET' AS account_type, FALSE AS auxiliary_required
|
|
UNION ALL SELECT 341, '1122', '应收账款', 'ASSET', TRUE
|
|
UNION ALL SELECT 342, '2202', '应付账款', 'LIABILITY', TRUE
|
|
UNION ALL SELECT 343, '5401', '工程成本', 'COST', TRUE
|
|
UNION ALL SELECT 344, '6001', '主营业务收入', 'INCOME', TRUE
|
|
) seed
|
|
WHERE NOT EXISTS (SELECT 1 FROM md_account existing WHERE existing.code = seed.code);
|
|
|
|
INSERT INTO md_tax_rate (
|
|
public_id, code, name, rate, status, created_by, updated_by,
|
|
submitted_by, submitted_at, reviewed_by, reviewed_at, review_opinion
|
|
)
|
|
SELECT CONCAT('D', LPAD(seed.seq, 25, '0')), seed.code, seed.name, seed.rate, 'ACTIVE',
|
|
@demo_project_actor_id, @demo_finance_actor_id,
|
|
@demo_project_actor_id, DATE_SUB(@demo_now, INTERVAL 20 DAY),
|
|
@demo_finance_actor_id, DATE_SUB(@demo_now, INTERVAL 19 DAY), '复核通过'
|
|
FROM (
|
|
SELECT 350 AS seq, 'VAT-0' AS code, '零税率' AS name, 0.000000 AS rate
|
|
UNION ALL SELECT 351, 'VAT-3', '3% 税率', 0.030000
|
|
UNION ALL SELECT 352, 'VAT-6', '6% 税率', 0.060000
|
|
UNION ALL SELECT 353, 'VAT-9', '9% 税率', 0.090000
|
|
UNION ALL SELECT 354, 'VAT-13', '13% 税率', 0.130000
|
|
) seed
|
|
WHERE NOT EXISTS (SELECT 1 FROM md_tax_rate existing WHERE existing.code = seed.code);
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- 5. 项目导入、OA 导入、参数版本和历史编号
|
|
-- ---------------------------------------------------------------------------
|
|
SET @demo_project_import_file_id := (SELECT id FROM file_object WHERE public_id = CONCAT('D', LPAD(105, 25, '0')));
|
|
SET @demo_source_import_file_id := (SELECT id FROM file_object WHERE public_id = CONCAT('D', LPAD(106, 25, '0')));
|
|
|
|
INSERT INTO project_import_batch (
|
|
public_id, batch_no, source_file_id, file_sha256, total_rows, valid_rows, error_rows,
|
|
validation_json, status, created_by, updated_by
|
|
)
|
|
SELECT CONCAT('D', LPAD(seed.seq, 25, '0')), seed.batch_no,
|
|
(SELECT id FROM file_object WHERE public_id = CONCAT('D', LPAD(105 + seed.seq - 400, 25, '0'))),
|
|
(SELECT sha256 FROM file_object WHERE public_id = CONCAT('D', LPAD(105 + seed.seq - 400, 25, '0'))),
|
|
seed.total_rows, seed.valid_rows, seed.error_rows,
|
|
JSON_OBJECT('message', seed.message), seed.status,
|
|
@demo_project_actor_id, @demo_project_actor_id
|
|
FROM (
|
|
SELECT 400 AS seq, 'DEMO-V073-PROJECT-IMPORT-VALIDATING' AS batch_no, 10 AS total_rows, 0 AS valid_rows, 0 AS error_rows,
|
|
'正在校验' AS message, 'VALIDATING' AS status
|
|
UNION ALL SELECT 401, 'DEMO-V073-PROJECT-IMPORT-INVALID', 10, 7, 3, '存在 3 行错误', 'INVALID'
|
|
UNION ALL SELECT 402, 'DEMO-V073-PROJECT-IMPORT-READY', 10, 10, 0, '已通过校验，待确认', 'READY'
|
|
UNION ALL SELECT 403, 'DEMO-V073-PROJECT-IMPORT-CONFIRMED', 10, 10, 0, '已确认导入', 'CONFIRMED'
|
|
UNION ALL SELECT 404, 'DEMO-V073-PROJECT-IMPORT-CANCELLED', 10, 10, 0, '已取消', 'CANCELLED'
|
|
) seed;
|
|
|
|
INSERT INTO project_import_row (batch_id, row_no, payload_json, validation_json, status)
|
|
SELECT batch.id, 1,
|
|
JSON_OBJECT('businessNo', CONCAT(batch.batch_no, '-ROW-1'), 'name', '演示导入项目'),
|
|
CASE WHEN batch.status = 'INVALID' THEN JSON_OBJECT('errors', JSON_ARRAY('项目编号重复')) ELSE JSON_OBJECT('valid', TRUE) END,
|
|
CASE WHEN batch.status = 'INVALID' THEN 'INVALID'
|
|
WHEN batch.status = 'CONFIRMED' THEN 'CONFIRMED'
|
|
ELSE 'VALID' END
|
|
FROM project_import_batch batch
|
|
WHERE batch.batch_no LIKE 'DEMO-V073-PROJECT-IMPORT-%';
|
|
|
|
INSERT INTO src_import_batch (
|
|
public_id, batch_no, form_type, source_system, source_file_id, file_sha256,
|
|
total_rows, valid_rows, error_rows, validation_json, status, created_by, updated_by
|
|
)
|
|
SELECT CONCAT('D', LPAD(seed.seq, 25, '0')), seed.batch_no, 'OA-01', 'DEMO_SQL',
|
|
@demo_source_import_file_id,
|
|
(SELECT sha256 FROM file_object WHERE id = @demo_source_import_file_id),
|
|
seed.total_rows, seed.valid_rows, seed.error_rows,
|
|
JSON_OBJECT('message', seed.message), seed.status,
|
|
@demo_project_actor_id, @demo_project_actor_id
|
|
FROM (
|
|
SELECT 410 AS seq, 'DEMO-V073-OA-IMPORT-VALIDATING' AS batch_no, 10 AS total_rows, 0 AS valid_rows, 0 AS error_rows,
|
|
'正在校验' AS message, 'VALIDATING' AS status
|
|
UNION ALL SELECT 411, 'DEMO-V073-OA-IMPORT-INVALID', 10, 7, 3, '存在 3 行错误', 'INVALID'
|
|
UNION ALL SELECT 412, 'DEMO-V073-OA-IMPORT-READY', 10, 10, 0, '已通过校验，待确认', 'READY'
|
|
UNION ALL SELECT 413, 'DEMO-V073-OA-IMPORT-CONFIRMED', 10, 10, 0, '已确认导入', 'CONFIRMED'
|
|
UNION ALL SELECT 414, 'DEMO-V073-OA-IMPORT-CANCELLED', 10, 10, 0, '已取消', 'CANCELLED'
|
|
) seed;
|
|
|
|
INSERT INTO src_import_row (
|
|
batch_id, row_no, source_no, source_version, payload_json, validation_json, status
|
|
)
|
|
SELECT batch.id, 1, CONCAT(batch.batch_no, '-ROW-1'), '1',
|
|
JSON_OBJECT('awardDate', DATE_FORMAT(@demo_today, '%Y-%m-%d'), 'constructionUnit', '演示建设单位'),
|
|
CASE WHEN batch.status = 'INVALID' THEN JSON_OBJECT('errors', JSON_ARRAY('中标金额缺失')) ELSE JSON_OBJECT('valid', TRUE) END,
|
|
CASE WHEN batch.status = 'INVALID' THEN 'INVALID'
|
|
WHEN batch.status = 'CONFIRMED' THEN 'CONFIRMED'
|
|
ELSE 'VALID' END
|
|
FROM src_import_batch batch
|
|
WHERE batch.batch_no LIKE 'DEMO-V073-OA-IMPORT-%';
|
|
|
|
INSERT INTO sys_parameter_version (
|
|
public_id, parameter_group, version_no, value_json, status, effective_at,
|
|
created_by, updated_by
|
|
)
|
|
VALUES
|
|
(CONCAT('D', LPAD(420, 25, '0')), 'DEMO_UI_SETTINGS', 1,
|
|
JSON_OBJECT('name', '演示参数草稿', 'enabled', FALSE), 'DRAFT', NULL,
|
|
@demo_admin_public_id, @demo_admin_public_id),
|
|
(CONCAT('D', LPAD(421, 25, '0')), 'DEMO_UI_SETTINGS', 2,
|
|
JSON_OBJECT('name', '演示参数生效版', 'enabled', TRUE), 'ACTIVE', DATE_SUB(@demo_now, INTERVAL 1 DAY),
|
|
@demo_admin_public_id, @demo_admin_public_id),
|
|
(CONCAT('D', LPAD(422, 25, '0')), 'DEMO_UI_SETTINGS', 3,
|
|
JSON_OBJECT('name', '演示参数已退役版', 'enabled', FALSE), 'RETIRED', DATE_SUB(@demo_now, INTERVAL 30 DAY),
|
|
@demo_admin_public_id, @demo_admin_public_id);
|
|
|
|
INSERT INTO md_legacy_identifier_mapping (
|
|
public_id, resource_type, source_system, legacy_code, normalized_legacy_code,
|
|
target_public_id, status, created_by, updated_by
|
|
)
|
|
VALUES
|
|
(CONCAT('D', LPAD(430, 25, '0')), 'PROJECT', 'LEGACY_ERP', 'OLD-PROJECT-001', 'OLD-PROJECT-001',
|
|
@demo_project_public_id, 'ACTIVE', @demo_admin_id, @demo_admin_id),
|
|
(CONCAT('D', LPAD(431, 25, '0')), 'CONTRACT', 'LEGACY_ERP', 'OLD-CONTRACT-001', 'OLD-CONTRACT-001',
|
|
@demo_contract_public_id, 'DISABLED', @demo_admin_id, @demo_admin_id);
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- 6. OA-01 ~ OA-14、来源单据全状态、审批流程全状态
|
|
-- ---------------------------------------------------------------------------
|
|
SET @demo_project_actor_name := (SELECT display_name FROM iam_user WHERE id = @demo_project_actor_id);
|
|
SET @demo_finance_actor_name := (SELECT display_name FROM iam_user WHERE id = @demo_finance_actor_id);
|
|
SET @demo_definition_version_id := (
|
|
SELECT definition_version.id
|
|
FROM wf_definition definition_record
|
|
JOIN wf_definition_version definition_version ON definition_version.definition_id = definition_record.id
|
|
WHERE definition_record.code = 'SOURCE_STANDARD_REVIEW'
|
|
AND definition_record.status = 'ACTIVE'
|
|
AND definition_version.status = 'ACTIVE'
|
|
ORDER BY definition_version.version_no DESC
|
|
LIMIT 1
|
|
);
|
|
|
|
CREATE TEMPORARY TABLE kaidi_demo_v073_forms (
|
|
seq INT NOT NULL PRIMARY KEY,
|
|
form_type VARCHAR(16) NOT NULL,
|
|
document_status VARCHAR(32) NOT NULL,
|
|
payload_json JSON NOT NULL
|
|
) ENGINE=InnoDB;
|
|
|
|
INSERT INTO kaidi_demo_v073_forms (seq, form_type, document_status, payload_json)
|
|
VALUES
|
|
(500, 'OA-01', 'DRAFT', JSON_OBJECT(
|
|
'awardDate', DATE_FORMAT(@demo_today, '%Y-%m-%d'),
|
|
'constructionUnit', '凯迪演示建设有限公司', 'projectAddress', '演示市建设路 73 号',
|
|
'awardAmount', 2200000.00, 'isEpc', TRUE, 'costBudget', 1800000.00,
|
|
'paymentTerms', '按月度进度支付',
|
|
'winningPeople', JSON_ARRAY(JSON_OBJECT('name', '张演示', 'role', '项目负责人')),
|
|
'constructionDetails', JSON_ARRAY(JSON_OBJECT('item', '主体工程', 'amount', 1800000.00))
|
|
)),
|
|
(501, 'OA-02', 'SUBMITTED', JSON_OBJECT(
|
|
'supplierName', '中城演示工程有限公司', 'taxNo', '91330000DEMO000073',
|
|
'contact', '李演示 13800000073', 'supplierType', '工程供应商', 'purchaseAmount', 600000.00,
|
|
'bankAccounts', JSON_ARRAY(JSON_OBJECT('accountName', '中城演示工程有限公司',
|
|
'bankName', '演示银行城中支行', 'accountNo', '6222000000000730', 'accountCategory', 'BASIC')),
|
|
'qualifications', JSON_ARRAY(JSON_OBJECT('name', '建筑业企业资质', 'validUntil', '2030-12-31'))
|
|
)),
|
|
(502, 'OA-03', 'PROJECT_REVIEW', JSON_OBJECT(
|
|
'contractName', '凯迪全链路演示合同', 'contractNo', 'DEMO-V073-CONTRACT-ACTIVE',
|
|
'contractAmount', 2000000.00, 'supplierId', @demo_counterparty_public_id, 'currency', 'CNY',
|
|
'supplierSnapshot', JSON_OBJECT('name', '中城演示工程有限公司'),
|
|
'realtimeCost', 1500000.00,
|
|
'quantityPriceReview', JSON_ARRAY(JSON_OBJECT('item', '主体施工', 'quantity', 1, 'price', 1500000.00))
|
|
)),
|
|
(503, 'OA-04', 'FINANCE_REVIEW', JSON_OBJECT(
|
|
'contractId', @demo_contract_public_id, 'supplierId', @demo_counterparty_public_id,
|
|
'costCategoryId', @demo_cost_category_public_id, 'businessDate', DATE_FORMAT(@demo_today, '%Y-%m-%d'),
|
|
'payableAmount', 300000.00,
|
|
'invoiceDetails', JSON_ARRAY(JSON_OBJECT('invoiceType', 'SPECIAL', 'amount', 300000.00)),
|
|
'supportingFiles', JSON_ARRAY('演示应付附件.pdf')
|
|
)),
|
|
(504, 'OA-05', 'RETURNED', JSON_OBJECT(
|
|
'receiptDate', DATE_FORMAT(@demo_today, '%Y-%m-%d'), 'receiptAmount', 500000.00,
|
|
'payerName', '凯迪演示建设有限公司', 'referenceNo', 'DEMO-RCPT-REF-073',
|
|
'receiptFiles', JSON_ARRAY('演示收款回单.pdf')
|
|
)),
|
|
(505, 'OA-06', 'REJECTED', JSON_OBJECT(
|
|
'contractId', @demo_contract_public_id, 'supplierId', @demo_counterparty_public_id,
|
|
'payableId', 'DEMO-V073-PAYABLE-CONFIRMED', 'requestedAmount', 180000.00,
|
|
'requestedDate', DATE_FORMAT(@demo_today, '%Y-%m-%d'), 'purpose', '演示进度款',
|
|
'paymentDetails', JSON_OBJECT('bankAccount', '**** **** 0730', 'amount', 180000.00)
|
|
)),
|
|
(506, 'OA-07', 'APPROVED', JSON_OBJECT(
|
|
'companyName', '凯迪演示建设有限公司', 'accountName', '凯迪演示建设有限公司',
|
|
'bankName', '演示银行总行', 'accountNo', '6222000000000731', 'accountCategory', 'BASIC',
|
|
'validFrom', DATE_FORMAT(@demo_today, '%Y-%m-%d')
|
|
)),
|
|
(507, 'OA-08', 'RESULT_PENDING', JSON_OBJECT(
|
|
'contractId', @demo_contract_public_id, 'counterpartyId', @demo_counterparty_public_id,
|
|
'invoiceType', 'SPECIAL', 'taxRate', 0.130000, 'invoiceAmount', 260000.00,
|
|
'requestedDate', DATE_FORMAT(@demo_today, '%Y-%m-%d'), 'invoiceTitle', '中城演示工程有限公司'
|
|
)),
|
|
(508, 'OA-09', 'ARCHIVED', JSON_OBJECT(
|
|
'teamName', '演示施工一班', 'teamLeader', '王演示',
|
|
'workers', JSON_ARRAY(JSON_OBJECT('name', '工人甲', 'job', '施工员'), JSON_OBJECT('name', '工人乙', 'job', '安全员')),
|
|
'workerFiles', JSON_ARRAY('实名制人员清单.pdf')
|
|
)),
|
|
(509, 'OA-10', 'VOID', JSON_OBJECT(
|
|
'contractAmount', 2000000.00, 'reportPeriod', DATE_FORMAT(@demo_today, '%Y-%m'),
|
|
'progressMetrics', JSON_OBJECT('planned', 60, 'actual', 55), 'progressSummary', '主体施工完成 55%',
|
|
'taskDetails', JSON_ARRAY(JSON_OBJECT('task', '主体施工', 'progress', 55)),
|
|
'sitePhotos', JSON_ARRAY('现场照片-01.jpg')
|
|
)),
|
|
(510, 'OA-11', 'APPROVED', JSON_OBJECT(
|
|
'costContractNo', 'DEMO-V073-CONTRACT-ACTIVE', 'supplierName', '中城演示工程有限公司',
|
|
'amounts', JSON_OBJECT('contractAmount', 2000000.00, 'settlementAmount', 1980000.00),
|
|
'settlementFiles', JSON_ARRAY('结算确认单.pdf')
|
|
)),
|
|
(511, 'OA-12', 'ARCHIVE_PENDING', JSON_OBJECT(
|
|
'acceptanceDetails', JSON_ARRAY(JSON_OBJECT('item', '工程质量', 'result', '合格')),
|
|
'acceptanceSummary', '项目验收通过，等待归档完整性检查'
|
|
)),
|
|
(512, 'OA-13', 'FINANCE_REVIEW', JSON_OBJECT(
|
|
'chargeDate', DATE_FORMAT(@demo_today, '%Y-%m-%d'),
|
|
'feeDetails', JSON_ARRAY(JSON_OBJECT('person', '赵演示', 'feeType', '证书费', 'amount', 3000.00)),
|
|
'monthlyReceivable', 3000.00
|
|
)),
|
|
(513, 'OA-14', 'APPROVED', JSON_OBJECT(
|
|
'constructionPermitRequired', TRUE,
|
|
'projectLeaders', JSON_ARRAY(JSON_OBJECT('name', '张演示', 'role', '项目经理')),
|
|
'projectDates', JSON_OBJECT('startDate', DATE_FORMAT(@demo_today, '%Y-%m-%d'), 'endDate', DATE_FORMAT(DATE_ADD(@demo_today, INTERVAL 180 DAY), '%Y-%m-%d')),
|
|
'winningPersonnel', JSON_ARRAY(JSON_OBJECT('name', '张演示')),
|
|
'changePersonnel', JSON_ARRAY(), 'residentPersonnel', JSON_ARRAY(JSON_OBJECT('name', '工人甲'))
|
|
));
|
|
|
|
INSERT INTO src_source_document (
|
|
public_id, business_no, form_type, source_system, source_no, normalized_source_no,
|
|
source_version, normalized_source_version, template_id, company_id, project_id,
|
|
applicant_name, business_date, status, created_by, updated_by
|
|
)
|
|
SELECT CONCAT('D', LPAD(form_seed.seq, 25, '0')),
|
|
CONCAT('DEMO-V073-', form_seed.form_type), form_seed.form_type, 'DEMO_SQL',
|
|
CONCAT('DEMO-V073-', form_seed.form_type), CONCAT('DEMO-V073-', form_seed.form_type),
|
|
'1', '1', template_record.id, @demo_company_id, @demo_project_id,
|
|
@demo_project_actor_name, @demo_today, form_seed.document_status,
|
|
@demo_project_actor_id, @demo_finance_actor_id
|
|
FROM kaidi_demo_v073_forms form_seed
|
|
JOIN src_template template_record
|
|
ON template_record.id = (
|
|
SELECT latest_template.id
|
|
FROM src_template latest_template
|
|
WHERE latest_template.form_type = form_seed.form_type
|
|
AND latest_template.status = 'ACTIVE'
|
|
ORDER BY latest_template.template_version DESC
|
|
LIMIT 1
|
|
);
|
|
|
|
INSERT INTO src_document_version (
|
|
public_id, source_document_id, version_no, template_id, snapshot_sha256,
|
|
payload_json, submitted_at, created_by
|
|
)
|
|
SELECT CONCAT('D', LPAD(form_seed.seq + 50, 25, '0')), document_record.id, 1,
|
|
document_record.template_id, SHA2(CAST(form_seed.payload_json AS CHAR), 256),
|
|
form_seed.payload_json,
|
|
CASE WHEN form_seed.document_status = 'DRAFT' THEN NULL ELSE DATE_SUB(@demo_now, INTERVAL 5 DAY) END,
|
|
@demo_project_actor_id
|
|
FROM kaidi_demo_v073_forms form_seed
|
|
JOIN src_source_document document_record
|
|
ON document_record.business_no = CONCAT('DEMO-V073-', form_seed.form_type);
|
|
|
|
UPDATE src_source_document document_record
|
|
JOIN src_document_version version_record ON version_record.source_document_id = document_record.id
|
|
SET document_record.current_version_id = version_record.id
|
|
WHERE document_record.business_no LIKE 'DEMO-V073-OA-%';
|
|
|
|
INSERT INTO src_field_value (
|
|
document_version_id, field_code, row_no, typed_value_json, schema_version
|
|
)
|
|
SELECT version_record.id, field_key.field_code, 0,
|
|
JSON_EXTRACT(version_record.payload_json, CONCAT('$.', field_key.field_code)), 1
|
|
FROM src_document_version version_record
|
|
JOIN src_source_document document_record ON document_record.id = version_record.source_document_id
|
|
JOIN JSON_TABLE(
|
|
JSON_KEYS(version_record.payload_json),
|
|
'$[*]' COLUMNS(field_code VARCHAR(64) PATH '$')
|
|
) AS field_key
|
|
WHERE document_record.business_no LIKE 'DEMO-V073-OA-%';
|
|
|
|
-- 每个非草稿表单保留提交快照；OA-01 的撤回样本也保留提交历史。
|
|
INSERT INTO src_approval_snapshot (
|
|
public_id, document_version_id, node_code, actor_name, actor_id,
|
|
action, opinion, acted_at, source_snapshot_json
|
|
)
|
|
SELECT CONCAT('D', LPAD(800 + form_seed.seq - 500, 25, '0')), version_record.id,
|
|
'APPLICANT', @demo_project_actor_name, @demo_project_actor_id,
|
|
'SUBMIT', '演示数据：提交审批', DATE_SUB(@demo_now, INTERVAL 5 DAY),
|
|
JSON_OBJECT('dataset', @demo_dataset_code, 'formType', form_seed.form_type)
|
|
FROM kaidi_demo_v073_forms form_seed
|
|
JOIN src_source_document document_record
|
|
ON document_record.business_no = CONCAT('DEMO-V073-', form_seed.form_type)
|
|
JOIN src_document_version version_record ON version_record.id = document_record.current_version_id
|
|
WHERE form_seed.document_status <> 'DRAFT'
|
|
OR form_seed.form_type = 'OA-01';
|
|
|
|
-- 所有流程实例状态：RUNNING / RETURNED / APPROVED / REJECTED / WITHDRAWN / VOID。
|
|
INSERT INTO wf_instance (
|
|
public_id, definition_version_id, source_version_id, current_node_code,
|
|
status, created_by, updated_by, created_at, updated_at
|
|
)
|
|
SELECT CONCAT('D', LPAD(instance_seed.seq, 25, '0')), @demo_definition_version_id,
|
|
version_record.id, instance_seed.current_node_code, instance_seed.instance_status,
|
|
@demo_project_actor_id, @demo_finance_actor_id,
|
|
DATE_SUB(@demo_now, INTERVAL 5 DAY), DATE_SUB(@demo_now, INTERVAL 1 DAY)
|
|
FROM (
|
|
SELECT 600 AS seq, 'OA-01' AS form_type, NULL AS current_node_code, 'WITHDRAWN' AS instance_status
|
|
UNION ALL SELECT 601, 'OA-03', 'PROJECT_REVIEW', 'RUNNING'
|
|
UNION ALL SELECT 602, 'OA-04', 'FINANCE_REVIEW', 'RUNNING'
|
|
UNION ALL SELECT 603, 'OA-05', NULL, 'RETURNED'
|
|
UNION ALL SELECT 604, 'OA-06', NULL, 'REJECTED'
|
|
UNION ALL SELECT 605, 'OA-07', NULL, 'APPROVED'
|
|
UNION ALL SELECT 606, 'OA-10', NULL, 'VOID'
|
|
UNION ALL SELECT 607, 'OA-11', NULL, 'APPROVED'
|
|
UNION ALL SELECT 608, 'OA-12', NULL, 'APPROVED'
|
|
UNION ALL SELECT 609, 'OA-13', 'FINANCE_REVIEW', 'RUNNING'
|
|
UNION ALL SELECT 610, 'OA-14', NULL, 'APPROVED'
|
|
) instance_seed
|
|
JOIN src_source_document document_record
|
|
ON document_record.business_no = CONCAT('DEMO-V073-', instance_seed.form_type)
|
|
JOIN src_document_version version_record ON version_record.id = document_record.current_version_id;
|
|
|
|
INSERT INTO wf_task (
|
|
public_id, instance_id, node_code, sequence_no, assignee_id, due_at,
|
|
status, created_at, updated_at
|
|
)
|
|
SELECT CONCAT('D', LPAD(task_seed.seq, 25, '0')), instance_record.id,
|
|
task_seed.node_code, task_seed.sequence_no,
|
|
CASE WHEN task_seed.node_code = 'PROJECT_REVIEW' THEN @demo_project_actor_id ELSE @demo_finance_actor_id END,
|
|
CASE WHEN task_seed.task_status = 'PENDING' THEN DATE_ADD(@demo_now, INTERVAL task_seed.due_days DAY) END,
|
|
task_seed.task_status, DATE_SUB(@demo_now, INTERVAL 4 DAY), DATE_SUB(@demo_now, INTERVAL 1 DAY)
|
|
FROM (
|
|
SELECT 620 AS seq, 600 AS instance_seq, 'PROJECT_REVIEW' AS node_code, 1 AS sequence_no, 'CANCELLED' AS task_status, 0 AS due_days
|
|
UNION ALL SELECT 621, 601, 'PROJECT_REVIEW', 1, 'PENDING', -1
|
|
UNION ALL SELECT 622, 602, 'PROJECT_REVIEW', 1, 'APPROVED', 0
|
|
UNION ALL SELECT 623, 602, 'FINANCE_REVIEW', 2, 'PENDING', 2
|
|
UNION ALL SELECT 624, 603, 'PROJECT_REVIEW', 1, 'RETURNED', 0
|
|
UNION ALL SELECT 625, 604, 'PROJECT_REVIEW', 1, 'REJECTED', 0
|
|
UNION ALL SELECT 626, 605, 'PROJECT_REVIEW', 1, 'APPROVED', 0
|
|
UNION ALL SELECT 627, 605, 'FINANCE_REVIEW', 2, 'APPROVED', 0
|
|
UNION ALL SELECT 628, 606, 'PROJECT_REVIEW', 1, 'CANCELLED', 0
|
|
UNION ALL SELECT 629, 607, 'PROJECT_REVIEW', 1, 'APPROVED', 0
|
|
UNION ALL SELECT 630, 607, 'FINANCE_REVIEW', 2, 'APPROVED', 0
|
|
UNION ALL SELECT 631, 608, 'PROJECT_REVIEW', 1, 'APPROVED', 0
|
|
UNION ALL SELECT 632, 608, 'FINANCE_REVIEW', 2, 'APPROVED', 0
|
|
UNION ALL SELECT 633, 609, 'PROJECT_REVIEW', 1, 'APPROVED', 0
|
|
UNION ALL SELECT 634, 609, 'FINANCE_REVIEW', 2, 'PENDING', 1
|
|
UNION ALL SELECT 635, 610, 'PROJECT_REVIEW', 1, 'APPROVED', 0
|
|
UNION ALL SELECT 636, 610, 'FINANCE_REVIEW', 2, 'APPROVED', 0
|
|
) task_seed
|
|
JOIN wf_instance instance_record
|
|
ON instance_record.public_id = CONCAT('D', LPAD(task_seed.instance_seq, 25, '0'));
|
|
|
|
INSERT INTO wf_action (
|
|
public_id, task_id, sequence_no, action, opinion, return_points_json,
|
|
actor_id, acted_at, request_id
|
|
)
|
|
SELECT CONCAT('D', LPAD(700 + task_seed.seq - 620, 25, '0')), task_record.id, 1,
|
|
task_seed.action_code, task_seed.opinion,
|
|
CASE WHEN task_seed.action_code = 'RETURN' THEN JSON_ARRAY('补充附件', '核对金额') END,
|
|
CASE WHEN task_record.node_code = 'PROJECT_REVIEW' THEN @demo_project_actor_id ELSE @demo_finance_actor_id END,
|
|
DATE_SUB(@demo_now, INTERVAL 2 DAY),
|
|
CONCAT('D', LPAD(1100 + task_seed.seq - 620, 25, '0'))
|
|
FROM (
|
|
SELECT 620 AS seq, 'CANCEL' AS action_code, '申请人撤回，任务取消' AS opinion
|
|
UNION ALL SELECT 621, 'REASSIGN', '演示重新指派后继续待办'
|
|
UNION ALL SELECT 622, 'APPROVE', '项目复核通过'
|
|
UNION ALL SELECT 624, 'RETURN', '资料不完整，退回补充'
|
|
UNION ALL SELECT 625, 'REJECT', '审批驳回'
|
|
UNION ALL SELECT 626, 'APPROVE', '项目复核通过'
|
|
UNION ALL SELECT 627, 'APPROVE', '财务复核通过'
|
|
UNION ALL SELECT 628, 'CANCEL', '单据作废，任务取消'
|
|
UNION ALL SELECT 629, 'APPROVE', '项目复核通过'
|
|
UNION ALL SELECT 630, 'APPROVE', '财务复核通过'
|
|
UNION ALL SELECT 631, 'APPROVE', '项目复核通过'
|
|
UNION ALL SELECT 632, 'APPROVE', '财务复核通过'
|
|
UNION ALL SELECT 633, 'APPROVE', '项目复核通过'
|
|
UNION ALL SELECT 635, 'APPROVE', '项目复核通过'
|
|
UNION ALL SELECT 636, 'APPROVE', '财务复核通过'
|
|
) task_seed
|
|
JOIN wf_task task_record
|
|
ON task_record.public_id = CONCAT('D', LPAD(task_seed.seq, 25, '0'));
|
|
|
|
-- 将实际审批动作投影到来源审批快照。
|
|
INSERT INTO src_approval_snapshot (
|
|
public_id, document_version_id, node_code, actor_name, actor_id,
|
|
action, opinion, acted_at, source_snapshot_json
|
|
)
|
|
SELECT CONCAT('D', LPAD(900 + ROW_NUMBER() OVER (ORDER BY action_record.id), 25, '0')),
|
|
instance_record.source_version_id, task_record.node_code,
|
|
CASE WHEN task_record.node_code = 'PROJECT_REVIEW' THEN @demo_project_actor_name ELSE @demo_finance_actor_name END,
|
|
action_record.actor_id, action_record.action, action_record.opinion,
|
|
action_record.acted_at,
|
|
JSON_OBJECT('dataset', @demo_dataset_code, 'taskPublicId', task_record.public_id)
|
|
FROM wf_action action_record
|
|
JOIN wf_task task_record ON task_record.id = action_record.task_id
|
|
JOIN wf_instance instance_record ON instance_record.id = task_record.instance_id
|
|
JOIN src_document_version version_record ON version_record.id = instance_record.source_version_id
|
|
JOIN src_source_document document_record ON document_record.id = version_record.source_document_id
|
|
WHERE document_record.business_no LIKE 'DEMO-V073-OA-%'
|
|
AND action_record.action IN ('APPROVE', 'RETURN', 'REJECT');
|
|
|
|
INSERT INTO src_approval_snapshot (
|
|
public_id, document_version_id, node_code, actor_name, actor_id,
|
|
action, opinion, acted_at, source_snapshot_json
|
|
)
|
|
SELECT CONCAT('D', LPAD(980, 25, '0')), version_record.id, 'APPLICANT',
|
|
@demo_project_actor_name, @demo_project_actor_id, 'WITHDRAW',
|
|
'申请人撤回演示', DATE_SUB(@demo_now, INTERVAL 2 DAY), JSON_OBJECT('dataset', @demo_dataset_code)
|
|
FROM src_source_document document_record
|
|
JOIN src_document_version version_record ON version_record.id = document_record.current_version_id
|
|
WHERE document_record.business_no = 'DEMO-V073-OA-01';
|
|
|
|
INSERT INTO src_approval_snapshot (
|
|
public_id, document_version_id, node_code, actor_name, actor_id,
|
|
action, opinion, acted_at, source_snapshot_json
|
|
)
|
|
SELECT CONCAT('D', LPAD(981, 25, '0')), version_record.id, 'APPLICANT',
|
|
@demo_project_actor_name, @demo_project_actor_id, 'VOID',
|
|
'演示表单作废', DATE_SUB(@demo_now, INTERVAL 1 DAY), JSON_OBJECT('dataset', @demo_dataset_code)
|
|
FROM src_source_document document_record
|
|
JOIN src_document_version version_record ON version_record.id = document_record.current_version_id
|
|
WHERE document_record.business_no = 'DEMO-V073-OA-10';
|
|
|
|
INSERT INTO src_approval_snapshot (
|
|
public_id, document_version_id, node_code, actor_name, actor_id,
|
|
action, opinion, acted_at, source_snapshot_json
|
|
)
|
|
SELECT CONCAT('D', LPAD(982, 25, '0')), version_record.id, 'APPLICANT',
|
|
@demo_project_actor_name, @demo_project_actor_id, 'IMPORT',
|
|
'历史 OA 数据导入演示', DATE_SUB(@demo_now, INTERVAL 30 DAY), JSON_OBJECT('dataset', @demo_dataset_code)
|
|
FROM src_source_document document_record
|
|
JOIN src_document_version version_record ON version_record.id = document_record.current_version_id
|
|
WHERE document_record.business_no = 'DEMO-V073-OA-09';
|
|
|
|
DROP TEMPORARY TABLE kaidi_demo_v073_forms;
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- 7. 合同成本、收款发票、付款和资金流水的全状态
|
|
-- ---------------------------------------------------------------------------
|
|
INSERT INTO fin_contract_change (
|
|
public_id, contract_id, change_no, change_amount, effective_date,
|
|
reason, status, created_by
|
|
)
|
|
VALUES
|
|
(CONCAT('D', LPAD(1200, 25, '0')), @demo_contract_id, 'DEMO-V073-CHANGE-APPROVED', 200000.00,
|
|
DATE_SUB(@demo_today, INTERVAL 30 DAY), '演示已批准合同变更', 'APPROVED', @demo_finance_actor_id),
|
|
(CONCAT('D', LPAD(1201, 25, '0')), @demo_contract_id, 'DEMO-V073-CHANGE-VOID', -50000.00,
|
|
DATE_SUB(@demo_today, INTERVAL 20 DAY), '演示已作废合同变更', 'VOID', @demo_finance_actor_id);
|
|
|
|
INSERT INTO fin_contract_settlement (
|
|
public_id, contract_id, settlement_no, settlement_amount, settlement_date,
|
|
status, created_by
|
|
)
|
|
VALUES
|
|
(CONCAT('D', LPAD(1210, 25, '0')), @demo_contract_id, 'DEMO-V073-SETTLEMENT-CONFIRMED',
|
|
1980000.00, DATE_SUB(@demo_today, INTERVAL 5 DAY), 'CONFIRMED', @demo_finance_actor_id),
|
|
(CONCAT('D', LPAD(1211, 25, '0')), @demo_contract_id, 'DEMO-V073-SETTLEMENT-VOID',
|
|
1900000.00, DATE_SUB(@demo_today, INTERVAL 10 DAY), 'VOID', @demo_finance_actor_id);
|
|
|
|
INSERT INTO fin_payable (
|
|
public_id, business_no, company_id, project_id, contract_id, counterparty_id,
|
|
cost_category_id, business_date, amount, invoiced_amount, paid_amount, status,
|
|
confirmed_by, confirmed_at, created_by, updated_by
|
|
)
|
|
SELECT CONCAT('D', LPAD(seed.seq, 25, '0')), seed.business_no,
|
|
@demo_company_id, @demo_project_id, @demo_contract_id, @demo_counterparty_id,
|
|
@demo_cost_category_id, DATE_SUB(@demo_today, INTERVAL seed.age_days DAY),
|
|
seed.amount, seed.invoiced_amount, seed.paid_amount, seed.status,
|
|
CASE WHEN seed.status IN ('CONFIRMED', 'PART_PAID', 'PAID') THEN @demo_finance_actor_id END,
|
|
CASE WHEN seed.status IN ('CONFIRMED', 'PART_PAID', 'PAID') THEN DATE_SUB(@demo_now, INTERVAL seed.age_days DAY) END,
|
|
@demo_project_actor_id, @demo_finance_actor_id
|
|
FROM (
|
|
SELECT 1220 AS seq, 'DEMO-V073-PAYABLE-PENDING' AS business_no, 100000.00 AS amount, 0.00 AS invoiced_amount, 0.00 AS paid_amount, 'PENDING' AS status, 1 AS age_days
|
|
UNION ALL SELECT 1221, 'DEMO-V073-PAYABLE-CONFIRMED', 300000.00, 120000.00, 0.00, 'CONFIRMED', 5
|
|
UNION ALL SELECT 1222, 'DEMO-V073-PAYABLE-PART-PAID', 250000.00, 250000.00, 100000.00, 'PART_PAID', 15
|
|
UNION ALL SELECT 1223, 'DEMO-V073-PAYABLE-PAID', 200000.00, 200000.00, 200000.00, 'PAID', 25
|
|
UNION ALL SELECT 1224, 'DEMO-V073-PAYABLE-VOID', 80000.00, 0.00, 0.00, 'VOID', 30
|
|
) seed;
|
|
|
|
SET @demo_payable_id := (SELECT id FROM fin_payable WHERE business_no = 'DEMO-V073-PAYABLE-CONFIRMED');
|
|
SET @demo_payment_result_file_id := (SELECT id FROM file_object WHERE public_id = CONCAT('D', LPAD(107, 25, '0')));
|
|
|
|
INSERT INTO fin_invoice (
|
|
public_id, business_no, company_id, project_id, contract_id, counterparty_id,
|
|
invoice_type, amount, currency, tax_rate, requested_date, invoice_no, issued_date,
|
|
result_file_id, status, submitted_by, submitted_at, reviewed_by, reviewed_at,
|
|
review_opinion, result_recorded_by, result_recorded_at, termination_reason,
|
|
created_by, updated_by
|
|
)
|
|
SELECT CONCAT('D', LPAD(seed.seq, 25, '0')), seed.business_no,
|
|
@demo_company_id, @demo_project_id, @demo_contract_id, @demo_counterparty_id,
|
|
seed.invoice_type, seed.amount, 'CNY', seed.tax_rate,
|
|
DATE_SUB(@demo_today, INTERVAL seed.age_days DAY), seed.invoice_no, seed.issued_date,
|
|
CASE WHEN seed.status IN ('ISSUED', 'RED_LETTERED') THEN @demo_payment_result_file_id END,
|
|
seed.status,
|
|
CASE WHEN seed.status <> 'DRAFT' THEN @demo_project_actor_id END,
|
|
CASE WHEN seed.status <> 'DRAFT' THEN DATE_SUB(@demo_now, INTERVAL seed.age_days DAY) END,
|
|
CASE WHEN seed.status IN ('RETURNED', 'REJECTED', 'APPROVED', 'RESULT_PENDING', 'ISSUED', 'RED_LETTERED') THEN @demo_finance_actor_id END,
|
|
CASE WHEN seed.status IN ('RETURNED', 'REJECTED', 'APPROVED', 'RESULT_PENDING', 'ISSUED', 'RED_LETTERED') THEN DATE_SUB(@demo_now, INTERVAL GREATEST(seed.age_days - 1, 0) DAY) END,
|
|
CASE WHEN seed.status = 'RETURNED' THEN '开票资料不完整'
|
|
WHEN seed.status = 'REJECTED' THEN '开票内容不符合要求'
|
|
WHEN seed.status IN ('APPROVED', 'RESULT_PENDING', 'ISSUED', 'RED_LETTERED') THEN '财务复核通过' END,
|
|
CASE WHEN seed.status IN ('ISSUED', 'RED_LETTERED') THEN @demo_finance_actor_id END,
|
|
CASE WHEN seed.status IN ('ISSUED', 'RED_LETTERED') THEN DATE_SUB(@demo_now, INTERVAL GREATEST(seed.age_days - 2, 0) DAY) END,
|
|
CASE WHEN seed.status = 'VOID' THEN '演示作废：客户信息变更' END,
|
|
@demo_project_actor_id, @demo_finance_actor_id
|
|
FROM (
|
|
SELECT 1240 AS seq, 'DEMO-V073-INVOICE-DRAFT' AS business_no, 'SPECIAL' AS invoice_type, 100000.00 AS amount, 0.130000 AS tax_rate, NULL AS invoice_no, NULL AS issued_date, 'DRAFT' AS status, 1 AS age_days
|
|
UNION ALL SELECT 1241, 'DEMO-V073-INVOICE-REVIEWING', 'NORMAL', 120000.00, 0.090000, NULL, NULL, 'REVIEWING', 2
|
|
UNION ALL SELECT 1242, 'DEMO-V073-INVOICE-RETURNED', 'SPECIAL', 140000.00, 0.130000, NULL, NULL, 'RETURNED', 3
|
|
UNION ALL SELECT 1243, 'DEMO-V073-INVOICE-REJECTED', 'NORMAL', 160000.00, 0.060000, NULL, NULL, 'REJECTED', 4
|
|
UNION ALL SELECT 1244, 'DEMO-V073-INVOICE-APPROVED', 'SPECIAL', 180000.00, 0.130000, NULL, NULL, 'APPROVED', 5
|
|
UNION ALL SELECT 1245, 'DEMO-V073-INVOICE-RESULT-PENDING', 'NORMAL', 200000.00, 0.090000, NULL, NULL, 'RESULT_PENDING', 6
|
|
UNION ALL SELECT 1246, 'DEMO-V073-INVOICE-ISSUED', 'SPECIAL', 220000.00, 0.130000, 'DEMO-INV-ISSUED-073', DATE_SUB(@demo_today, INTERVAL 6 DAY), 'ISSUED', 8
|
|
UNION ALL SELECT 1247, 'DEMO-V073-INVOICE-VOID', 'RECEIPT', 50000.00, 0.000000, NULL, NULL, 'VOID', 9
|
|
UNION ALL SELECT 1248, 'DEMO-V073-INVOICE-RED-LETTERED', 'RED_LETTER', 80000.00, 0.130000, 'DEMO-INV-RED-073', DATE_SUB(@demo_today, INTERVAL 9 DAY), 'RED_LETTERED', 10
|
|
) seed;
|
|
|
|
UPDATE fin_invoice red_invoice
|
|
JOIN fin_invoice issued_invoice ON issued_invoice.business_no = 'DEMO-V073-INVOICE-ISSUED'
|
|
SET red_invoice.original_invoice_id = issued_invoice.id
|
|
WHERE red_invoice.business_no = 'DEMO-V073-INVOICE-RED-LETTERED';
|
|
|
|
INSERT INTO fin_receivable (
|
|
public_id, business_no, company_id, project_id, contract_id, counterparty_id,
|
|
business_date, amount, outstanding_amount, currency, status, created_by, updated_by
|
|
)
|
|
SELECT CONCAT('D', LPAD(seed.seq, 25, '0')), seed.business_no,
|
|
@demo_company_id, @demo_project_id, @demo_contract_id, @demo_counterparty_id,
|
|
DATE_SUB(@demo_today, INTERVAL seed.age_days DAY), seed.amount,
|
|
seed.outstanding_amount, 'CNY', seed.status,
|
|
@demo_finance_actor_id, @demo_finance_actor_id
|
|
FROM (
|
|
SELECT 1260 AS seq, 'DEMO-V073-RECEIVABLE-OPEN' AS business_no, 300000.00 AS amount, 300000.00 AS outstanding_amount, 'OPEN' AS status, 4 AS age_days
|
|
UNION ALL SELECT 1261, 'DEMO-V073-RECEIVABLE-PARTIAL', 250000.00, 100000.00, 'PARTIALLY_SETTLED', 12
|
|
UNION ALL SELECT 1262, 'DEMO-V073-RECEIVABLE-SETTLED', 200000.00, 0.00, 'SETTLED', 20
|
|
UNION ALL SELECT 1263, 'DEMO-V073-RECEIVABLE-VOID', 60000.00, 60000.00, 'VOID', 30
|
|
) seed;
|
|
|
|
SET @demo_receivable_partial_id := (SELECT id FROM fin_receivable WHERE business_no = 'DEMO-V073-RECEIVABLE-PARTIAL');
|
|
SET @demo_receivable_settled_id := (SELECT id FROM fin_receivable WHERE business_no = 'DEMO-V073-RECEIVABLE-SETTLED');
|
|
|
|
INSERT INTO fin_receipt (
|
|
public_id, business_no, company_id, project_id, customer_id, receipt_date,
|
|
amount, allocated_amount, currency, payer_name, reference_no, status,
|
|
confirmed_by, confirmed_at, void_reason, created_by, updated_by
|
|
)
|
|
SELECT CONCAT('D', LPAD(seed.seq, 25, '0')), seed.business_no,
|
|
@demo_company_id, @demo_project_id, @demo_counterparty_id,
|
|
DATE_SUB(@demo_today, INTERVAL seed.age_days DAY), seed.amount, seed.allocated_amount,
|
|
'CNY', '中城演示工程有限公司', seed.reference_no, seed.status,
|
|
CASE WHEN seed.status IN ('CONFIRMED', 'PARTIALLY_ALLOCATED', 'ALLOCATED') THEN @demo_finance_actor_id END,
|
|
CASE WHEN seed.status IN ('CONFIRMED', 'PARTIALLY_ALLOCATED', 'ALLOCATED') THEN DATE_SUB(@demo_now, INTERVAL seed.age_days DAY) END,
|
|
CASE WHEN seed.status = 'VOID' THEN '演示作废：重复登记' END,
|
|
@demo_finance_actor_id, @demo_finance_actor_id
|
|
FROM (
|
|
SELECT 1280 AS seq, 'DEMO-V073-RECEIPT-DRAFT' AS business_no, 50000.00 AS amount, 0.00 AS allocated_amount, 'DEMO-RCPT-DRAFT-073' AS reference_no, 'DRAFT' AS status, 1 AS age_days
|
|
UNION ALL SELECT 1281, 'DEMO-V073-RECEIPT-CONFIRMED', 100000.00, 0.00, 'DEMO-RCPT-CONFIRMED-073', 'CONFIRMED', 3
|
|
UNION ALL SELECT 1282, 'DEMO-V073-RECEIPT-PARTIAL', 150000.00, 50000.00, 'DEMO-RCPT-PARTIAL-073', 'PARTIALLY_ALLOCATED', 8
|
|
UNION ALL SELECT 1283, 'DEMO-V073-RECEIPT-ALLOCATED', 200000.00, 200000.00, 'DEMO-RCPT-ALLOCATED-073', 'ALLOCATED', 15
|
|
UNION ALL SELECT 1284, 'DEMO-V073-RECEIPT-VOID', 30000.00, 0.00, 'DEMO-RCPT-VOID-073', 'VOID', 25
|
|
) seed;
|
|
|
|
INSERT INTO fin_receipt_allocation (
|
|
public_id, receipt_id, receivable_id, sequence_no, project_id, contract_id,
|
|
amount, note, created_by
|
|
)
|
|
SELECT CONCAT('D', LPAD(1290, 25, '0')), receipt_record.id,
|
|
@demo_receivable_partial_id, 1, @demo_project_id, @demo_contract_id,
|
|
50000.00, '演示部分核销', @demo_finance_actor_id
|
|
FROM fin_receipt receipt_record WHERE receipt_record.business_no = 'DEMO-V073-RECEIPT-PARTIAL'
|
|
UNION ALL
|
|
SELECT CONCAT('D', LPAD(1291, 25, '0')), receipt_record.id,
|
|
@demo_receivable_settled_id, 1, @demo_project_id, @demo_contract_id,
|
|
200000.00, '演示全部核销', @demo_finance_actor_id
|
|
FROM fin_receipt receipt_record WHERE receipt_record.business_no = 'DEMO-V073-RECEIPT-ALLOCATED';
|
|
|
|
INSERT INTO fin_payment_request (
|
|
public_id, business_no, company_id, project_id, contract_id, payable_id,
|
|
supplier_id, bank_account_version_id, requested_amount, approved_amount,
|
|
frozen_amount, paid_amount, currency, requested_date, purpose,
|
|
check_snapshot_json, check_rule_version, status,
|
|
submitted_by, submitted_at, checked_by, checked_at, approved_by, approved_at,
|
|
returned_by, returned_at, return_reason, created_by, updated_by
|
|
)
|
|
SELECT CONCAT('D', LPAD(seed.seq, 25, '0')), seed.business_no,
|
|
@demo_company_id, @demo_project_id, @demo_contract_id, @demo_payable_id,
|
|
@demo_counterparty_id, @demo_bank_account_id, seed.requested_amount,
|
|
seed.approved_amount, seed.frozen_amount, seed.paid_amount, 'CNY',
|
|
DATE_SUB(@demo_today, INTERVAL seed.age_days DAY), CONCAT('演示付款：', seed.status),
|
|
JSON_OBJECT('result', seed.check_result, 'dataset', @demo_dataset_code), 'PAYMENT-CHECK-V1',
|
|
seed.status,
|
|
CASE WHEN seed.status <> 'DRAFT' THEN @demo_project_actor_id END,
|
|
CASE WHEN seed.status <> 'DRAFT' THEN DATE_SUB(@demo_now, INTERVAL seed.age_days DAY) END,
|
|
CASE WHEN seed.status IN ('REJECTED', 'APPROVED', 'OFFLINE_PENDING', 'RESULT_RECORDED', 'RESULT_VERIFIED', 'PART_PAID', 'PAID', 'FAILED', 'REFUNDED') THEN @demo_finance_actor_id END,
|
|
CASE WHEN seed.status IN ('REJECTED', 'APPROVED', 'OFFLINE_PENDING', 'RESULT_RECORDED', 'RESULT_VERIFIED', 'PART_PAID', 'PAID', 'FAILED', 'REFUNDED') THEN DATE_SUB(@demo_now, INTERVAL GREATEST(seed.age_days - 1, 0) DAY) END,
|
|
CASE WHEN seed.status IN ('APPROVED', 'OFFLINE_PENDING', 'RESULT_RECORDED', 'RESULT_VERIFIED', 'PART_PAID', 'PAID', 'FAILED', 'REFUNDED') THEN @demo_finance_actor_id END,
|
|
CASE WHEN seed.status IN ('APPROVED', 'OFFLINE_PENDING', 'RESULT_RECORDED', 'RESULT_VERIFIED', 'PART_PAID', 'PAID', 'FAILED', 'REFUNDED') THEN DATE_SUB(@demo_now, INTERVAL GREATEST(seed.age_days - 1, 0) DAY) END,
|
|
CASE WHEN seed.status = 'RETURNED' THEN @demo_finance_actor_id END,
|
|
CASE WHEN seed.status = 'RETURNED' THEN DATE_SUB(@demo_now, INTERVAL 1 DAY) END,
|
|
CASE WHEN seed.status = 'RETURNED' THEN '付款附件不完整' END,
|
|
@demo_project_actor_id, @demo_finance_actor_id
|
|
FROM (
|
|
SELECT 1300 AS seq, 'DEMO-V073-PAYMENT-DRAFT' AS business_no, 10000.00 AS requested_amount, NULL AS approved_amount, 0.00 AS frozen_amount, 0.00 AS paid_amount, 'DRAFT' AS status, 'PASS' AS check_result, 1 AS age_days
|
|
UNION ALL SELECT 1301, 'DEMO-V073-PAYMENT-REVIEWING', 11000.00, NULL, 0.00, 0.00, 'REVIEWING', 'WARNING', 2
|
|
UNION ALL SELECT 1302, 'DEMO-V073-PAYMENT-RETURNED', 12000.00, NULL, 0.00, 0.00, 'RETURNED', 'BLOCK', 3
|
|
UNION ALL SELECT 1303, 'DEMO-V073-PAYMENT-REJECTED', 13000.00, NULL, 0.00, 0.00, 'REJECTED', 'BLOCK', 4
|
|
UNION ALL SELECT 1304, 'DEMO-V073-PAYMENT-APPROVED', 14000.00, 14000.00, 14000.00, 0.00, 'APPROVED', 'PASS', 5
|
|
UNION ALL SELECT 1305, 'DEMO-V073-PAYMENT-OFFLINE', 15000.00, 15000.00, 15000.00, 0.00, 'OFFLINE_PENDING', 'PASS', 6
|
|
UNION ALL SELECT 1306, 'DEMO-V073-PAYMENT-RESULT-RECORDED', 16000.00, 16000.00, 16000.00, 0.00, 'RESULT_RECORDED', 'PASS', 7
|
|
UNION ALL SELECT 1307, 'DEMO-V073-PAYMENT-RESULT-VERIFIED', 17000.00, 17000.00, 8000.00, 9000.00, 'RESULT_VERIFIED', 'PASS', 8
|
|
UNION ALL SELECT 1308, 'DEMO-V073-PAYMENT-PART-PAID', 18000.00, 18000.00, 8000.00, 10000.00, 'PART_PAID', 'PASS', 9
|
|
UNION ALL SELECT 1309, 'DEMO-V073-PAYMENT-PAID', 19000.00, 19000.00, 0.00, 19000.00, 'PAID', 'PASS', 10
|
|
UNION ALL SELECT 1310, 'DEMO-V073-PAYMENT-FAILED', 20000.00, 20000.00, 0.00, 0.00, 'FAILED', 'WARNING', 11
|
|
UNION ALL SELECT 1311, 'DEMO-V073-PAYMENT-REFUNDED', 21000.00, 21000.00, 0.00, 0.00, 'REFUNDED', 'WARNING', 12
|
|
UNION ALL SELECT 1312, 'DEMO-V073-PAYMENT-VOID', 22000.00, NULL, 0.00, 0.00, 'VOID', 'PASS', 13
|
|
) seed;
|
|
|
|
SET @demo_payment_review_id := (SELECT id FROM fin_payment_request WHERE business_no = 'DEMO-V073-PAYMENT-REVIEWING');
|
|
SET @demo_payment_result_recorded_id := (SELECT id FROM fin_payment_request WHERE business_no = 'DEMO-V073-PAYMENT-RESULT-RECORDED');
|
|
SET @demo_payment_paid_id := (SELECT id FROM fin_payment_request WHERE business_no = 'DEMO-V073-PAYMENT-PAID');
|
|
SET @demo_payment_failed_id := (SELECT id FROM fin_payment_request WHERE business_no = 'DEMO-V073-PAYMENT-FAILED');
|
|
SET @demo_payment_refunded_id := (SELECT id FROM fin_payment_request WHERE business_no = 'DEMO-V073-PAYMENT-REFUNDED');
|
|
|
|
INSERT INTO fin_payment_check (
|
|
public_id, payment_id, check_code, result, message, checked_by, checked_at
|
|
)
|
|
VALUES
|
|
(CONCAT('D', LPAD(1330, 25, '0')), @demo_payment_review_id, 'BUDGET_CHECK', 'PASS', '预算额度充足', @demo_finance_actor_id, @demo_now),
|
|
(CONCAT('D', LPAD(1331, 25, '0')), @demo_payment_review_id, 'ATTACHMENT_CHECK', 'WARNING', '有一项附件需要人工确认', @demo_finance_actor_id, @demo_now),
|
|
(CONCAT('D', LPAD(1332, 25, '0')), @demo_payment_review_id, 'RISK_CHECK', 'BLOCK', '项目风险标记阻断付款', @demo_finance_actor_id, @demo_now);
|
|
|
|
INSERT INTO fin_payment_attachment (
|
|
public_id, payment_id, file_id, document_type, active, uploaded_by
|
|
)
|
|
SELECT CONCAT('D', LPAD(1340, 25, '0')), @demo_payment_review_id, file_record.id,
|
|
'PAYMENT_SUPPORT', TRUE, @demo_project_actor_id
|
|
FROM file_object file_record WHERE file_record.public_id = CONCAT('D', LPAD(101, 25, '0'));
|
|
|
|
INSERT INTO fin_payment_result (
|
|
public_id, result_no, payment_id, recorded_from_status, result_type,
|
|
paid_date, amount, transaction_no, receipt_file_id, failure_reason, remark,
|
|
recorded_by, recorded_at, verified_by, verified_at, verification_opinion,
|
|
returned_by, returned_at, return_opinion, status
|
|
)
|
|
VALUES
|
|
(CONCAT('D', LPAD(1350, 25, '0')), 'DEMO-V073-RESULT-PENDING', @demo_payment_result_recorded_id,
|
|
'OFFLINE_PENDING', 'SUCCESS', @demo_today, 16000.00, 'DEMO-TXN-PENDING-073', @demo_payment_result_file_id,
|
|
NULL, '等待复核', @demo_project_actor_id, @demo_now, NULL, NULL, NULL, NULL, NULL, NULL, 'PENDING_VERIFY'),
|
|
(CONCAT('D', LPAD(1351, 25, '0')), 'DEMO-V073-RESULT-VERIFIED', @demo_payment_paid_id,
|
|
'OFFLINE_PENDING', 'SUCCESS', DATE_SUB(@demo_today, INTERVAL 8 DAY), 19000.00, 'DEMO-TXN-VERIFIED-073', @demo_payment_result_file_id,
|
|
NULL, '付款成功', @demo_project_actor_id, DATE_SUB(@demo_now, INTERVAL 8 DAY),
|
|
@demo_finance_actor_id, DATE_SUB(@demo_now, INTERVAL 7 DAY), '回单与流水一致', NULL, NULL, NULL, 'VERIFIED'),
|
|
(CONCAT('D', LPAD(1352, 25, '0')), 'DEMO-V073-RESULT-RETURNED', @demo_payment_failed_id,
|
|
'OFFLINE_PENDING', 'FAILURE', DATE_SUB(@demo_today, INTERVAL 5 DAY), 20000.00, NULL, NULL,
|
|
'收款账户信息错误', '失败结果需要补充银行回单', @demo_project_actor_id, DATE_SUB(@demo_now, INTERVAL 5 DAY),
|
|
NULL, NULL, NULL, @demo_finance_actor_id, DATE_SUB(@demo_now, INTERVAL 4 DAY), '缺少失败回单', 'RETURNED'),
|
|
(CONCAT('D', LPAD(1353, 25, '0')), 'DEMO-V073-RESULT-REFUND', @demo_payment_refunded_id,
|
|
'PAID', 'REFUND', DATE_SUB(@demo_today, INTERVAL 3 DAY), 21000.00, 'DEMO-TXN-REFUND-073', @demo_payment_result_file_id,
|
|
NULL, '全额退款', @demo_project_actor_id, DATE_SUB(@demo_now, INTERVAL 3 DAY),
|
|
@demo_finance_actor_id, DATE_SUB(@demo_now, INTERVAL 2 DAY), '退款流水复核通过', NULL, NULL, NULL, 'VERIFIED');
|
|
|
|
SET @demo_refund_result_id := (SELECT id FROM fin_payment_result WHERE result_no = 'DEMO-V073-RESULT-REFUND');
|
|
|
|
INSERT INTO fin_payment_result_file (public_id, payment_result_id, file_id, created_by)
|
|
SELECT CONCAT('D', LPAD(1360, 25, '0')), result_record.id, @demo_payment_result_file_id, @demo_finance_actor_id
|
|
FROM fin_payment_result result_record WHERE result_record.result_no = 'DEMO-V073-RESULT-VERIFIED';
|
|
|
|
INSERT INTO fin_payment_refund (
|
|
public_id, payment_result_id, sequence_no, amount, reason, status, created_by
|
|
)
|
|
VALUES
|
|
(CONCAT('D', LPAD(1370, 25, '0')), @demo_refund_result_id, 1, 5000.00, '演示退款待复核', 'RECORDED', @demo_project_actor_id),
|
|
(CONCAT('D', LPAD(1371, 25, '0')), @demo_refund_result_id, 2, 8000.00, '演示退款已复核', 'VERIFIED', @demo_finance_actor_id),
|
|
(CONCAT('D', LPAD(1372, 25, '0')), @demo_refund_result_id, 3, 3000.00, '演示退款已作废', 'VOID', @demo_finance_actor_id);
|
|
|
|
INSERT INTO fin_payment_attachment_exception (
|
|
public_id, payment_id, document_type, document_name, version_no, reason, status, active,
|
|
requested_by, requested_at, reviewed_by, reviewed_at, review_opinion,
|
|
consumed_by, consumed_at
|
|
)
|
|
SELECT CONCAT('D', LPAD(seed.seq, 25, '0')), payment_record.id, seed.document_type, seed.document_name,
|
|
1, seed.reason, seed.status, seed.active,
|
|
@demo_project_actor_id, DATE_SUB(@demo_now, INTERVAL 2 DAY),
|
|
CASE WHEN seed.status IN ('APPROVED', 'REJECTED') THEN @demo_finance_actor_id END,
|
|
CASE WHEN seed.status IN ('APPROVED', 'REJECTED') THEN DATE_SUB(@demo_now, INTERVAL 1 DAY) END,
|
|
CASE WHEN seed.status = 'APPROVED' THEN '同意附件例外'
|
|
WHEN seed.status = 'REJECTED' THEN '附件仍需补齐' END,
|
|
CASE WHEN seed.status = 'APPROVED' AND seed.consumed THEN @demo_finance_actor_id END,
|
|
CASE WHEN seed.status = 'APPROVED' AND seed.consumed THEN @demo_now END
|
|
FROM (
|
|
SELECT 1380 AS seq, 'DEMO-V073-PAYMENT-REVIEWING' AS business_no, 'MATERIAL_INVOICE' AS document_type, '材料发票' AS document_name, '发票正在补开' AS reason, 'PENDING' AS status, TRUE AS active, FALSE AS consumed
|
|
UNION ALL SELECT 1381, 'DEMO-V073-PAYMENT-APPROVED', 'DELIVERY_NOTE', '送货单', '送货单原件后补', 'APPROVED', FALSE, TRUE
|
|
UNION ALL SELECT 1382, 'DEMO-V073-PAYMENT-RETURNED', 'SITE_ACCEPTANCE_PHOTO', '现场验收照片', '现场照片缺失', 'REJECTED', FALSE, FALSE
|
|
) seed
|
|
JOIN fin_payment_request payment_record ON payment_record.business_no = seed.business_no;
|
|
|
|
INSERT INTO fin_payment_control_exception (
|
|
public_id, payment_id, control_code, finding_fingerprint, finding_summary,
|
|
finding_snapshot_json, version_no, reason, valid_until, status, active,
|
|
requested_by, requested_at, reviewed_by, reviewed_at, review_opinion,
|
|
consumed_by, consumed_at
|
|
)
|
|
SELECT CONCAT('D', LPAD(seed.seq, 25, '0')), payment_record.id, seed.control_code,
|
|
SHA2(CONCAT(seed.business_no, ':', seed.control_code), 256), seed.summary,
|
|
JSON_OBJECT('dataset', @demo_dataset_code, 'controlCode', seed.control_code),
|
|
1, seed.reason, DATE_ADD(@demo_today, INTERVAL 30 DAY), seed.status, seed.active,
|
|
@demo_project_actor_id, DATE_SUB(@demo_now, INTERVAL 2 DAY),
|
|
CASE WHEN seed.status IN ('APPROVED', 'REJECTED') THEN @demo_finance_actor_id END,
|
|
CASE WHEN seed.status IN ('APPROVED', 'REJECTED') THEN DATE_SUB(@demo_now, INTERVAL 1 DAY) END,
|
|
CASE WHEN seed.status = 'APPROVED' THEN '风险已人工核实'
|
|
WHEN seed.status = 'REJECTED' THEN '风险不可豁免' END,
|
|
CASE WHEN seed.status = 'APPROVED' AND seed.consumed THEN @demo_finance_actor_id END,
|
|
CASE WHEN seed.status = 'APPROVED' AND seed.consumed THEN @demo_now END
|
|
FROM (
|
|
SELECT 1390 AS seq, 'DEMO-V073-PAYMENT-REVIEWING' AS business_no, 'DUPLICATE' AS control_code,
|
|
'存在相似付款申请' AS summary, '申请重复校验例外' AS reason, 'PENDING' AS status, TRUE AS active, FALSE AS consumed
|
|
UNION ALL SELECT 1391, 'DEMO-V073-PAYMENT-APPROVED', 'PROJECT_RISK',
|
|
'项目存在已核实风险标记', '风险已由负责人确认', 'APPROVED', FALSE, TRUE
|
|
UNION ALL SELECT 1392, 'DEMO-V073-PAYMENT-RETURNED', 'PROJECT_RISK',
|
|
'项目风险不可接受', '申请临时放行', 'REJECTED', FALSE, FALSE
|
|
) seed
|
|
JOIN fin_payment_request payment_record ON payment_record.business_no = seed.business_no;
|
|
|
|
-- 资金流水覆盖当前支持的九类事件；快照值与主项目资金控制保持一致。
|
|
INSERT INTO fund_ledger (
|
|
public_id, company_id, project_id, currency, source_event_key, entry_type,
|
|
source_type, source_public_id, source_business_no, description, event_amount,
|
|
delta_amount, confirmed_receipt_delta, confirmed_deduction_delta,
|
|
approved_adjustment_delta, frozen_amount_delta, approved_unpaid_delta, paid_amount_delta,
|
|
confirmed_receipt_after, confirmed_deduction_after, approved_adjustment_after,
|
|
frozen_amount_after, approved_unpaid_after, paid_amount_after, balance_after,
|
|
control_version_after, occurred_at, created_by
|
|
)
|
|
SELECT CONCAT('D', LPAD(seed.seq, 25, '0')), @demo_company_id, @demo_project_id, 'CNY',
|
|
CONCAT('DEMO-V073-FUND:', seed.entry_type), seed.entry_type, seed.source_type,
|
|
@demo_project_public_id, CONCAT('DEMO-V073-', seed.entry_type), seed.description,
|
|
seed.event_amount, seed.delta_amount, seed.receipt_delta, seed.deduction_delta,
|
|
seed.adjustment_delta, seed.frozen_delta, seed.unpaid_delta, seed.paid_delta,
|
|
1500000.00, 100000.00, 50000.00, 150000.00, 200000.00, 300000.00,
|
|
800000.00, seed.seq - 1399, DATE_SUB(@demo_now, INTERVAL (1409 - seed.seq) HOUR),
|
|
@demo_finance_actor_id
|
|
FROM (
|
|
SELECT 1400 AS seq, 'OPENING_BALANCE' AS entry_type, 'PROJECT' AS source_type, '期初余额' AS description, 0.00 AS event_amount, 800000.00 AS delta_amount, 1500000.00 AS receipt_delta, 100000.00 AS deduction_delta, 50000.00 AS adjustment_delta, 150000.00 AS frozen_delta, 200000.00 AS unpaid_delta, 300000.00 AS paid_delta
|
|
UNION ALL SELECT 1401, 'RECEIPT_CONFIRMED', 'RECEIPT', '收款确认', 100000.00, 100000.00, 100000.00, 0, 0, 0, 0, 0
|
|
UNION ALL SELECT 1402, 'RECEIPT_ALLOCATED_TO_PROJECT', 'RECEIPT', '收款分配至项目', 80000.00, 80000.00, 80000.00, 0, 0, 0, 0, 0
|
|
UNION ALL SELECT 1403, 'RECEIPT_VOIDED', 'RECEIPT', '收款作废', 30000.00, -30000.00, -30000.00, 0, 0, 0, 0, 0
|
|
UNION ALL SELECT 1404, 'PAYMENT_FUNDS_RESERVED', 'PAYMENT', '付款资金冻结', 14000.00, -14000.00, 0, 0, 0, 14000.00, 0, 0
|
|
UNION ALL SELECT 1405, 'PAYMENT_PAID', 'PAYMENT', '付款完成', 19000.00, -19000.00, 0, 0, 0, -19000.00, 0, 19000.00
|
|
UNION ALL SELECT 1406, 'PAYMENT_FUNDS_RELEASED_FAILURE', 'PAYMENT', '失败后释放冻结', 20000.00, 20000.00, 0, 0, 0, -20000.00, 0, 0
|
|
UNION ALL SELECT 1407, 'PAYMENT_FUNDS_RELEASED_REFUND', 'PAYMENT_REFUND', '退款释放冻结', 21000.00, 21000.00, 0, 0, 0, -21000.00, 0, 0
|
|
UNION ALL SELECT 1408, 'PAYMENT_REFUNDED', 'PAYMENT_REFUND', '付款退款', 21000.00, 21000.00, 0, 0, 0, 0, 0, -21000.00
|
|
) seed;
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- 8. 记账事件、凭证、导出、结果复核、冲销和重开的全状态
|
|
-- ---------------------------------------------------------------------------
|
|
SET @demo_account_1002_id := (SELECT id FROM md_account WHERE code = '1002' LIMIT 1);
|
|
SET @demo_account_1122_id := (SELECT id FROM md_account WHERE code = '1122' LIMIT 1);
|
|
SET @demo_account_2202_id := (SELECT id FROM md_account WHERE code = '2202' LIMIT 1);
|
|
SET @demo_account_5401_id := (SELECT id FROM md_account WHERE code = '5401' LIMIT 1);
|
|
SET @demo_account_6001_id := (SELECT id FROM md_account WHERE code = '6001' LIMIT 1);
|
|
SET @demo_period := DATE_FORMAT(@demo_today, '%Y-%m');
|
|
SET @demo_accounting_export_file_id := (SELECT id FROM file_object WHERE public_id = CONCAT('D', LPAD(108, 25, '0')));
|
|
SET @demo_accounting_result_file_id := (SELECT id FROM file_object WHERE public_id = CONCAT('D', LPAD(109, 25, '0')));
|
|
|
|
CREATE TEMPORARY TABLE kaidi_demo_v073_accounting (
|
|
seq INT NOT NULL PRIMARY KEY,
|
|
event_status VARCHAR(32) NOT NULL,
|
|
voucher_status VARCHAR(32) NULL,
|
|
event_type VARCHAR(32) NOT NULL,
|
|
amount DECIMAL(20, 2) NOT NULL
|
|
) ENGINE=InnoDB;
|
|
|
|
INSERT INTO kaidi_demo_v073_accounting (seq, event_status, voucher_status, event_type, amount)
|
|
VALUES
|
|
(1500, 'PENDING', NULL, 'RECEIPT', 10000.00),
|
|
(1501, 'VOID', NULL, 'PAYABLE', 11000.00),
|
|
(1502, 'DRAFTED', 'DRAFT', 'RECEIPT', 12000.00),
|
|
(1503, 'DRAFTED', 'REVIEWING', 'INVOICE', 13000.00),
|
|
(1504, 'DRAFTED', 'RETURNED', 'PAYABLE', 14000.00),
|
|
(1505, 'DRAFTED', 'APPROVED', 'PAYMENT', 15000.00),
|
|
(1506, 'DRAFTED', 'EXPORTED', 'PAYMENT', 16000.00),
|
|
(1507, 'DRAFTED', 'RESULT_RECORDED', 'PAYMENT', 17000.00),
|
|
(1508, 'COMPLETED', 'COMPLETED', 'RECEIPT', 18000.00),
|
|
(1509, 'DRAFTED', 'RESULT_REVERSED', 'PAYMENT_REFUND', 19000.00),
|
|
(1510, 'VOID', 'VOID', 'INVOICE', 20000.00);
|
|
|
|
INSERT INTO acc_event (
|
|
public_id, business_no, event_type, source_type, source_public_id,
|
|
source_version, event_sequence, company_id, project_id, counterparty_id,
|
|
business_date, period, amount, currency, status, created_by
|
|
)
|
|
SELECT CONCAT('D', LPAD(seed.seq, 25, '0')), CONCAT('DEMO-V073-EVENT-', seed.seq),
|
|
seed.event_type, seed.event_type, CONCAT('D', LPAD(seed.seq + 10000, 25, '0')),
|
|
'DEMO-V073', seed.seq - 1499, @demo_company_id, @demo_project_id,
|
|
@demo_counterparty_id, DATE_SUB(@demo_today, INTERVAL (seed.seq - 1499) DAY),
|
|
@demo_period, seed.amount, 'CNY', seed.event_status, @demo_finance_actor_id
|
|
FROM kaidi_demo_v073_accounting seed;
|
|
|
|
INSERT INTO acc_voucher (
|
|
public_id, business_no, event_id, company_id, project_id,
|
|
rule_version, rule_snapshot_json, period, business_date, summary,
|
|
debit_total, credit_total, status, submitted_by, reviewed_by,
|
|
exported_at, export_sha256, export_batch_no, external_voucher_no, result_at,
|
|
result_recorded_by, result_recorded_at, result_record_remark,
|
|
result_verified_by, result_verified_at, result_verify_opinion, result_cycle_no,
|
|
void_reason, last_change_reason, created_by, updated_by
|
|
)
|
|
SELECT CONCAT('D', LPAD(seed.seq + 100, 25, '0')), CONCAT('DEMO-V073-VOUCHER-', seed.seq),
|
|
event_record.id, @demo_company_id, @demo_project_id,
|
|
'ACCOUNTING-RULE-V1', JSON_OBJECT(
|
|
'schemaVersion', 1, 'ruleVersion', 'ACCOUNTING-RULE-V1',
|
|
'eventType', seed.event_type, 'dataset', @demo_dataset_code
|
|
), @demo_period, DATE_SUB(@demo_today, INTERVAL (seed.seq - 1499) DAY),
|
|
CONCAT('演示凭证：', seed.voucher_status), seed.amount, seed.amount,
|
|
seed.voucher_status,
|
|
CASE WHEN seed.voucher_status IN ('REVIEWING', 'RETURNED', 'APPROVED', 'EXPORTED', 'RESULT_RECORDED', 'COMPLETED', 'RESULT_REVERSED') THEN @demo_project_actor_id END,
|
|
CASE WHEN seed.voucher_status IN ('RETURNED', 'APPROVED', 'EXPORTED', 'RESULT_RECORDED', 'COMPLETED', 'RESULT_REVERSED') THEN @demo_finance_actor_id END,
|
|
CASE WHEN seed.voucher_status IN ('EXPORTED', 'RESULT_RECORDED', 'COMPLETED', 'RESULT_REVERSED') THEN DATE_SUB(@demo_now, INTERVAL 3 DAY) END,
|
|
CASE WHEN seed.voucher_status IN ('EXPORTED', 'RESULT_RECORDED', 'COMPLETED', 'RESULT_REVERSED') THEN SHA2(CONCAT('DEMO-V073-EXPORT-', seed.seq), 256) END,
|
|
CASE WHEN seed.voucher_status IN ('EXPORTED', 'RESULT_RECORDED', 'COMPLETED', 'RESULT_REVERSED') THEN CONCAT('DEMO-V073-EXPORT-', seed.seq) END,
|
|
CASE WHEN seed.voucher_status IN ('RESULT_RECORDED', 'COMPLETED', 'RESULT_REVERSED') THEN CONCAT('EXT-DEMO-V073-', seed.seq) END,
|
|
CASE WHEN seed.voucher_status IN ('RESULT_RECORDED', 'COMPLETED', 'RESULT_REVERSED') THEN DATE_SUB(@demo_today, INTERVAL 2 DAY) END,
|
|
CASE WHEN seed.voucher_status IN ('RESULT_RECORDED', 'COMPLETED', 'RESULT_REVERSED') THEN @demo_project_actor_id END,
|
|
CASE WHEN seed.voucher_status IN ('RESULT_RECORDED', 'COMPLETED', 'RESULT_REVERSED') THEN DATE_SUB(@demo_now, INTERVAL 2 DAY) END,
|
|
CASE WHEN seed.voucher_status IN ('RESULT_RECORDED', 'COMPLETED', 'RESULT_REVERSED') THEN '演示记账结果已登记' END,
|
|
CASE WHEN seed.voucher_status IN ('COMPLETED', 'RESULT_REVERSED') THEN @demo_finance_actor_id END,
|
|
CASE WHEN seed.voucher_status IN ('COMPLETED', 'RESULT_REVERSED') THEN DATE_SUB(@demo_now, INTERVAL 1 DAY) END,
|
|
CASE WHEN seed.voucher_status IN ('COMPLETED', 'RESULT_REVERSED') THEN '外部凭证号和附件复核通过' END,
|
|
CASE WHEN seed.voucher_status = 'RESULT_REVERSED' THEN 1 ELSE 0 END,
|
|
CASE WHEN seed.voucher_status = 'VOID' THEN '演示凭证作废' END,
|
|
CASE WHEN seed.voucher_status = 'RETURNED' THEN '摘要和辅助核算需要修改' END,
|
|
@demo_finance_actor_id, @demo_finance_actor_id
|
|
FROM kaidi_demo_v073_accounting seed
|
|
JOIN acc_event event_record ON event_record.public_id = CONCAT('D', LPAD(seed.seq, 25, '0'))
|
|
WHERE seed.voucher_status IS NOT NULL;
|
|
|
|
UPDATE acc_event event_record
|
|
JOIN acc_voucher voucher_record ON voucher_record.event_id = event_record.id
|
|
SET event_record.voucher_id = voucher_record.id
|
|
WHERE event_record.business_no LIKE 'DEMO-V073-EVENT-%';
|
|
|
|
INSERT INTO acc_voucher_entry (
|
|
public_id, voucher_id, line_no, direction, account_id, amount, summary, auxiliary_json
|
|
)
|
|
SELECT CONCAT('D', LPAD(1700 + ROW_NUMBER() OVER (ORDER BY voucher_record.id, line_seed.line_no), 25, '0')),
|
|
voucher_record.id, line_seed.line_no, line_seed.direction,
|
|
CASE
|
|
WHEN line_seed.direction = 'DEBIT' AND event_record.event_type = 'RECEIPT' THEN @demo_account_1002_id
|
|
WHEN line_seed.direction = 'CREDIT' AND event_record.event_type = 'RECEIPT' THEN @demo_account_1122_id
|
|
WHEN line_seed.direction = 'DEBIT' AND event_record.event_type = 'INVOICE' THEN @demo_account_1122_id
|
|
WHEN line_seed.direction = 'CREDIT' AND event_record.event_type = 'INVOICE' THEN @demo_account_6001_id
|
|
WHEN line_seed.direction = 'DEBIT' AND event_record.event_type = 'PAYABLE' THEN @demo_account_5401_id
|
|
WHEN line_seed.direction = 'CREDIT' AND event_record.event_type = 'PAYABLE' THEN @demo_account_2202_id
|
|
WHEN line_seed.direction = 'DEBIT' AND event_record.event_type = 'PAYMENT' THEN @demo_account_2202_id
|
|
WHEN line_seed.direction = 'CREDIT' AND event_record.event_type = 'PAYMENT' THEN @demo_account_1002_id
|
|
WHEN line_seed.direction = 'DEBIT' AND event_record.event_type = 'PAYMENT_REFUND' THEN @demo_account_1002_id
|
|
ELSE @demo_account_2202_id
|
|
END,
|
|
event_record.amount, CONCAT('演示', event_record.event_type, '分录'),
|
|
JSON_OBJECT('projectPublicId', @demo_project_public_id, 'dataset', @demo_dataset_code)
|
|
FROM acc_voucher voucher_record
|
|
JOIN acc_event event_record ON event_record.id = voucher_record.event_id
|
|
CROSS JOIN (
|
|
SELECT 1 AS line_no, 'DEBIT' AS direction
|
|
UNION ALL SELECT 2, 'CREDIT'
|
|
) line_seed
|
|
WHERE voucher_record.business_no LIKE 'DEMO-V073-VOUCHER-%';
|
|
|
|
INSERT INTO acc_voucher_change (
|
|
public_id, voucher_id, reason, before_json, after_json, changed_by, changed_at
|
|
)
|
|
SELECT CONCAT('D', LPAD(1750, 25, '0')), voucher_record.id,
|
|
'演示退回后修改凭证', JSON_OBJECT('summary', '原摘要'),
|
|
JSON_OBJECT('summary', voucher_record.summary), @demo_finance_actor_id, @demo_now
|
|
FROM acc_voucher voucher_record WHERE voucher_record.status = 'RETURNED'
|
|
AND voucher_record.business_no LIKE 'DEMO-V073-VOUCHER-%';
|
|
|
|
INSERT INTO acc_export_batch (
|
|
public_id, batch_no, voucher_id, rule_version, range_json,
|
|
file_id, sha256, row_count, exported_by, exported_at
|
|
)
|
|
SELECT CONCAT('D', LPAD(1760 + ROW_NUMBER() OVER (ORDER BY voucher_record.id), 25, '0')),
|
|
CONCAT('DEMO-V073-EXPORT-BATCH-', voucher_record.id), voucher_record.id,
|
|
voucher_record.rule_version, JSON_OBJECT('voucherPublicId', voucher_record.public_id),
|
|
@demo_accounting_export_file_id,
|
|
(SELECT sha256 FROM file_object WHERE id = @demo_accounting_export_file_id),
|
|
2, @demo_finance_actor_id, DATE_SUB(@demo_now, INTERVAL 2 DAY)
|
|
FROM acc_voucher voucher_record
|
|
WHERE voucher_record.business_no LIKE 'DEMO-V073-VOUCHER-%'
|
|
AND voucher_record.status IN ('EXPORTED', 'RESULT_RECORDED', 'COMPLETED', 'RESULT_REVERSED');
|
|
|
|
INSERT INTO acc_voucher_result_file (
|
|
public_id, voucher_id, result_cycle_no, file_id, created_by
|
|
)
|
|
SELECT CONCAT('D', LPAD(1780 + ROW_NUMBER() OVER (ORDER BY voucher_record.id), 25, '0')),
|
|
voucher_record.id, voucher_record.result_cycle_no,
|
|
@demo_accounting_result_file_id, @demo_finance_actor_id
|
|
FROM acc_voucher voucher_record
|
|
WHERE voucher_record.business_no LIKE 'DEMO-V073-VOUCHER-%'
|
|
AND voucher_record.status IN ('RESULT_RECORDED', 'COMPLETED', 'RESULT_REVERSED');
|
|
|
|
SET @demo_reversed_voucher_id := (
|
|
SELECT id FROM acc_voucher
|
|
WHERE business_no = 'DEMO-V073-VOUCHER-1509'
|
|
);
|
|
SET @demo_exported_voucher_id := (
|
|
SELECT id FROM acc_voucher
|
|
WHERE business_no = 'DEMO-V073-VOUCHER-1506'
|
|
);
|
|
|
|
INSERT INTO acc_voucher_result_action (
|
|
public_id, voucher_id, company_id, period, sequence_no, result_cycle_no,
|
|
action_type, reason, historical_external_voucher_no, result_snapshot_json,
|
|
operated_by, operated_at, version_before, version_after
|
|
)
|
|
VALUES
|
|
(CONCAT('D', LPAD(1790, 25, '0')), @demo_reversed_voucher_id, @demo_company_id, @demo_period,
|
|
1, 1, 'REVERSE', '演示冲销外部记账结果', 'EXT-DEMO-V073-1509',
|
|
JSON_OBJECT('state', 'RESULT_REVERSED', 'dataset', @demo_dataset_code),
|
|
@demo_finance_actor_id, DATE_SUB(@demo_now, INTERVAL 1 DAY), 3, 4),
|
|
(CONCAT('D', LPAD(1791, 25, '0')), @demo_exported_voucher_id, @demo_company_id, @demo_period,
|
|
1, 1, 'REOPEN', '演示重开冲销后的记账结果', NULL,
|
|
JSON_OBJECT('state', 'EXPORTED', 'dataset', @demo_dataset_code),
|
|
@demo_finance_actor_id, @demo_now, 4, 5);
|
|
|
|
DROP TEMPORARY TABLE kaidi_demo_v073_accounting;
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- 9. 文件版本、档案包、档案清单、借阅全状态
|
|
-- ---------------------------------------------------------------------------
|
|
SET @demo_project_init_id := (SELECT id FROM md_project WHERE business_no = 'DEMO-V073-PROJECT-INIT');
|
|
SET @demo_project_contract_id := (SELECT id FROM md_project WHERE business_no = 'DEMO-V073-PROJECT-CONTRACT');
|
|
SET @demo_project_accept_id := (SELECT id FROM md_project WHERE business_no = 'DEMO-V073-PROJECT-ACCEPT');
|
|
SET @demo_project_settle_id := (SELECT id FROM md_project WHERE business_no = 'DEMO-V073-PROJECT-SETTLE');
|
|
SET @demo_project_closed_id := (SELECT id FROM md_project WHERE business_no = 'DEMO-V073-PROJECT-CLOSED');
|
|
SET @demo_file_old_id := (SELECT id FROM file_object WHERE public_id = CONCAT('D', LPAD(110, 25, '0')));
|
|
SET @demo_file_new_id := (SELECT id FROM file_object WHERE public_id = CONCAT('D', LPAD(111, 25, '0')));
|
|
|
|
-- 同一资料系列的旧版本 + 当前版本，验证版本替代和归档状态约束。
|
|
INSERT INTO file_link (
|
|
file_id, object_type, object_public_id, company_public_id, project_public_id,
|
|
contract_public_id, counterparty_public_id, form_type, original_type,
|
|
archive_status, supersedes_link_id, series_public_id, document_type,
|
|
version_no, active, linked_by
|
|
)
|
|
VALUES (
|
|
@demo_file_old_id, 'PROJECT', @demo_project_public_id, @demo_company_public_id,
|
|
@demo_project_public_id, @demo_contract_public_id, @demo_counterparty_public_id,
|
|
'OA-12', 'ACCEPTANCE', 'SUPERSEDED', NULL, CONCAT('D', LPAD(1900, 25, '0')),
|
|
'ACCEPTANCE_REPORT', 1, FALSE, @demo_archive_actor_id
|
|
);
|
|
SET @demo_old_file_link_id := LAST_INSERT_ID();
|
|
|
|
INSERT INTO file_link (
|
|
file_id, object_type, object_public_id, company_public_id, project_public_id,
|
|
contract_public_id, counterparty_public_id, form_type, original_type,
|
|
archive_status, supersedes_link_id, series_public_id, document_type,
|
|
version_no, active, linked_by
|
|
)
|
|
VALUES (
|
|
@demo_file_new_id, 'PROJECT', @demo_project_public_id, @demo_company_public_id,
|
|
@demo_project_public_id, @demo_contract_public_id, @demo_counterparty_public_id,
|
|
'OA-12', 'ACCEPTANCE', 'ARCHIVED', @demo_old_file_link_id, CONCAT('D', LPAD(1900, 25, '0')),
|
|
'ACCEPTANCE_REPORT', 2, TRUE, @demo_archive_actor_id
|
|
);
|
|
SET @demo_new_file_link_id := LAST_INSERT_ID();
|
|
|
|
-- 六条独立资料系列分别用于借阅 APPLIED/APPROVED/BORROWED/RETURNED/REJECTED/OVERDUE。
|
|
INSERT INTO file_link (
|
|
file_id, object_type, object_public_id, company_public_id, project_public_id,
|
|
contract_public_id, counterparty_public_id, form_type, original_type,
|
|
archive_status, supersedes_link_id, series_public_id, document_type,
|
|
version_no, active, linked_by
|
|
)
|
|
SELECT file_record.id, 'PROJECT', @demo_project_public_id, @demo_company_public_id,
|
|
@demo_project_public_id, @demo_contract_public_id, @demo_counterparty_public_id,
|
|
'OA-12', 'BORROW_PROOF', 'ARCHIVED', NULL,
|
|
CONCAT('D', LPAD(1900 + file_seed.seq, 25, '0')), CONCAT('BORROW_PROOF_', file_seed.seq),
|
|
1, TRUE, @demo_archive_actor_id
|
|
FROM (
|
|
SELECT 1 AS seq, 113 AS file_seq UNION ALL SELECT 2, 114 UNION ALL SELECT 3, 115
|
|
UNION ALL SELECT 4, 116 UNION ALL SELECT 5, 117 UNION ALL SELECT 6, 118
|
|
) file_seed
|
|
JOIN file_object file_record ON file_record.public_id = CONCAT('D', LPAD(file_seed.file_seq, 25, '0'));
|
|
|
|
INSERT INTO arc_package (
|
|
public_id, business_no, company_id, project_id, root_package_id,
|
|
package_version, rule_version, completeness, missing_count, retention_years,
|
|
physical_location, manifest_json, manifest_sha256, frozen, freeze_reason,
|
|
freeze_status_before, status, submitted_by, submitted_at, reviewed_by,
|
|
review_opinion, archived_at, created_by, updated_by
|
|
)
|
|
SELECT CONCAT('D', LPAD(package_seed.seq, 25, '0')), package_seed.business_no,
|
|
@demo_company_id, project_record.id, NULL, 1, 'ARCHIVE-RULE-V1',
|
|
package_seed.completeness, package_seed.missing_count, 10,
|
|
CONCAT('A-073-', package_seed.seq),
|
|
JSON_OBJECT('dataset', @demo_dataset_code, 'project', project_record.public_id),
|
|
SHA2(CONCAT('DEMO-V073-MANIFEST-', package_seed.seq), 256),
|
|
package_seed.frozen, package_seed.freeze_reason,
|
|
package_seed.freeze_status_before, package_seed.status,
|
|
CASE WHEN package_seed.status <> 'PREPARING' THEN @demo_archive_actor_id END,
|
|
CASE WHEN package_seed.status <> 'PREPARING' THEN DATE_SUB(@demo_now, INTERVAL 4 DAY) END,
|
|
CASE WHEN package_seed.status IN ('ARCHIVED', 'FROZEN', 'RETURNED') THEN @demo_finance_actor_id END,
|
|
CASE WHEN package_seed.status = 'RETURNED' THEN '缺少验收签字页' ELSE '演示档案状态' END,
|
|
CASE WHEN package_seed.status IN ('ARCHIVED', 'FROZEN') THEN DATE_SUB(@demo_now, INTERVAL 2 DAY) END,
|
|
@demo_archive_actor_id, @demo_archive_actor_id
|
|
FROM (
|
|
SELECT 1810 AS seq, 'DEMO-V073-ARCHIVE-PREPARING' AS business_no, 'DEMO-V073-PROJECT-INIT' AS project_no, 20.00 AS completeness, 4 AS missing_count, 'PREPARING' AS status, FALSE AS frozen, NULL AS freeze_reason, NULL AS freeze_status_before
|
|
UNION ALL SELECT 1811, 'DEMO-V073-ARCHIVE-CHECKED', 'DEMO-V073-PROJECT-CONTRACT', 80.00, 1, 'CHECKED', FALSE, NULL, NULL
|
|
UNION ALL SELECT 1812, 'DEMO-V073-ARCHIVE-REVIEWING', 'DEMO-V073-PROJECT-ACCEPT', 100.00, 0, 'REVIEWING', FALSE, NULL, NULL
|
|
UNION ALL SELECT 1813, 'DEMO-V073-ARCHIVE-RETURNED', 'DEMO-V073-PROJECT-SETTLE', 70.00, 2, 'RETURNED', FALSE, NULL, NULL
|
|
UNION ALL SELECT 1814, 'DEMO-V073-ARCHIVE-ARCHIVED', 'DEMO-V073-PROJECT-CLOSED', 100.00, 0, 'ARCHIVED', FALSE, NULL, NULL
|
|
UNION ALL SELECT 1815, 'DEMO-V073-ARCHIVE-FROZEN', 'DEMO-V073-PROJECT-MAIN', 100.00, 0, 'FROZEN', TRUE, '税务稽核期间冻结', 'ARCHIVED'
|
|
) package_seed
|
|
JOIN md_project project_record ON project_record.business_no = package_seed.project_no;
|
|
|
|
UPDATE arc_package package_record
|
|
SET package_record.root_package_id = package_record.id
|
|
WHERE package_record.business_no LIKE 'DEMO-V073-ARCHIVE-%';
|
|
|
|
SET @demo_package_returned_id := (SELECT id FROM arc_package WHERE business_no = 'DEMO-V073-ARCHIVE-RETURNED');
|
|
SET @demo_package_archived_id := (SELECT id FROM arc_package WHERE business_no = 'DEMO-V073-ARCHIVE-ARCHIVED');
|
|
SET @demo_package_frozen_id := (SELECT id FROM arc_package WHERE business_no = 'DEMO-V073-ARCHIVE-FROZEN');
|
|
|
|
INSERT INTO arc_package_item (
|
|
public_id, package_id, item_type, item_name, object_type, object_public_id,
|
|
file_id, file_link_id, file_sha256, required_flag, status, missing_reason,
|
|
not_applicable_reason, not_applicable_requested_by, not_applicable_requested_at,
|
|
not_applicable_reviewed_by, not_applicable_reviewed_at, not_applicable_review_opinion
|
|
)
|
|
SELECT CONCAT('D', LPAD(item_seed.seq, 25, '0')), package_record.id,
|
|
item_seed.item_type, item_seed.item_name, item_seed.object_type, item_seed.object_public_id,
|
|
file_record.id, link_record.id,
|
|
CASE WHEN file_record.id IS NOT NULL THEN file_record.sha256 END,
|
|
item_seed.required_flag, item_seed.item_status, item_seed.missing_reason,
|
|
item_seed.not_applicable_reason,
|
|
CASE WHEN item_seed.item_status IN ('NA_REVIEWING', 'NOT_APPLICABLE', 'NA_REJECTED') THEN @demo_finance_actor_id END,
|
|
CASE WHEN item_seed.item_status IN ('NA_REVIEWING', 'NOT_APPLICABLE', 'NA_REJECTED') THEN DATE_SUB(@demo_now, INTERVAL 2 DAY) END,
|
|
CASE WHEN item_seed.item_status IN ('NOT_APPLICABLE', 'NA_REJECTED') THEN @demo_archive_actor_id END,
|
|
CASE WHEN item_seed.item_status IN ('NOT_APPLICABLE', 'NA_REJECTED') THEN DATE_SUB(@demo_now, INTERVAL 1 DAY) END,
|
|
CASE WHEN item_seed.item_status = 'NOT_APPLICABLE' THEN '经复核确认不适用'
|
|
WHEN item_seed.item_status = 'NA_REJECTED' THEN '该资料仍然适用' END
|
|
FROM (
|
|
SELECT 1830 AS seq, 'OA-12' AS item_type, '验收报告' AS item_name, 'SOURCE_FORM' AS object_type, CONCAT('D', LPAD(511, 25, '0')) AS object_public_id, 111 AS file_seq, 1 AS required_flag, 'PRESENT' AS item_status, NULL AS missing_reason, NULL AS not_applicable_reason, 'DEMO-V073-ARCHIVE-ARCHIVED' AS package_no
|
|
UNION ALL SELECT 1831, 'ACCOUNTING', '记账凭证结果', 'ACCOUNTING_VOUCHER', CONCAT('D', LPAD(1608, 25, '0')), 109, 1, 'PRESENT', NULL, NULL, 'DEMO-V073-ARCHIVE-ARCHIVED'
|
|
UNION ALL SELECT 1832, 'PAYMENT', '付款回单', 'PAYMENT', CONCAT('D', LPAD(1309, 25, '0')), 107, 1, 'MISSING', '缺少银行回单', NULL, 'DEMO-V073-ARCHIVE-RETURNED'
|
|
UNION ALL SELECT 1833, 'INVOICE', '发票原件', 'INVOICE', CONCAT('D', LPAD(1246, 25, '0')), NULL, 1, 'NA_REVIEWING', NULL, '当前业务不涉及该资料', 'DEMO-V073-ARCHIVE-REVIEWING'
|
|
UNION ALL SELECT 1834, 'CONTRACT', '合同扫描件', 'CONTRACT', @demo_contract_public_id, 110, 1, 'SUPERSEDED', NULL, NULL, 'DEMO-V073-ARCHIVE-CHECKED'
|
|
UNION ALL SELECT 1835, 'PROJECT', '项目立项资料', 'PROJECT', @demo_project_public_id, NULL, 1, 'NOT_APPLICABLE', NULL, '本演示项目不涉及该资料', 'DEMO-V073-ARCHIVE-FROZEN'
|
|
UNION ALL SELECT 1836, 'PROJECT', '现场签证附件', 'PROJECT', @demo_project_public_id, NULL, 1, 'NA_REJECTED', NULL, '原申请不适用已被驳回', 'DEMO-V073-ARCHIVE-PREPARING'
|
|
) item_seed
|
|
JOIN arc_package package_record ON package_record.business_no = item_seed.package_no
|
|
LEFT JOIN file_object file_record ON file_record.public_id = CASE WHEN item_seed.file_seq IS NULL THEN NULL ELSE CONCAT('D', LPAD(item_seed.file_seq, 25, '0')) END
|
|
LEFT JOIN file_link link_record ON link_record.file_id = file_record.id AND link_record.active = TRUE;
|
|
|
|
INSERT INTO arc_package_return_item (package_id, item_id, reason, created_by)
|
|
SELECT @demo_package_returned_id, item_record.id, '缺少银行回单，请补充可核验的付款凭证', @demo_archive_actor_id
|
|
FROM arc_package_item item_record
|
|
WHERE item_record.public_id = CONCAT('D', LPAD(1832, 25, '0'));
|
|
|
|
INSERT INTO arc_package_object_snapshot (
|
|
public_id, package_id, item_id, object_type, object_public_id,
|
|
object_business_no, object_name, object_status, object_version,
|
|
source_version_public_id, snapshot_json, snapshot_sha256
|
|
)
|
|
SELECT CONCAT('D', LPAD(1850 + ROW_NUMBER() OVER (ORDER BY item_record.id), 25, '0')),
|
|
item_record.package_id, item_record.id, COALESCE(item_record.object_type, 'DEMO'),
|
|
COALESCE(item_record.object_public_id, item_record.public_id),
|
|
CONCAT('DEMO-V073-SNAPSHOT-', item_record.id), item_record.item_name,
|
|
item_record.status, 1, NULL,
|
|
JSON_OBJECT('dataset', @demo_dataset_code, 'itemPublicId', item_record.public_id,
|
|
'status', item_record.status),
|
|
SHA2(CONCAT('DEMO-V073-SNAPSHOT:', item_record.public_id), 256)
|
|
FROM arc_package_item item_record
|
|
WHERE item_record.public_id LIKE 'D000000000000000000000018%';
|
|
|
|
INSERT INTO arc_package_action (
|
|
public_id, package_id, sequence_no, action_code, from_status, to_status,
|
|
opinion, missing_json, manifest_sha256, actor_id, occurred_at
|
|
)
|
|
SELECT CONCAT('D', LPAD(action_seed.seq, 25, '0')), package_record.id,
|
|
action_seed.sequence_no, action_seed.action_code, action_seed.from_status,
|
|
action_seed.to_status, action_seed.opinion,
|
|
CASE WHEN action_seed.action_code IN ('CHECK', 'RETURN', 'NA_REQUEST', 'NA_REJECT') THEN JSON_ARRAY('演示缺失项') END,
|
|
package_record.manifest_sha256, action_seed.actor_id,
|
|
DATE_SUB(@demo_now, INTERVAL action_seed.age_days DAY)
|
|
FROM (
|
|
SELECT 1860 AS seq, 'DEMO-V073-ARCHIVE-PREPARING' AS package_no, 1 AS sequence_no, 'GENERATE' AS action_code, NULL AS from_status, 'PREPARING' AS to_status, '生成档案包' AS opinion, @demo_archive_actor_id AS actor_id, 5 AS age_days
|
|
UNION ALL SELECT 1861, 'DEMO-V073-ARCHIVE-CHECKED', 1, 'GENERATE', NULL, 'PREPARING', '生成清单', @demo_archive_actor_id, 5
|
|
UNION ALL SELECT 1862, 'DEMO-V073-ARCHIVE-CHECKED', 2, 'CHECK', 'PREPARING', 'CHECKED', '完整性检查完成', @demo_archive_actor_id, 4
|
|
UNION ALL SELECT 1863, 'DEMO-V073-ARCHIVE-REVIEWING', 1, 'GENERATE', NULL, 'PREPARING', '生成清单', @demo_archive_actor_id, 5
|
|
UNION ALL SELECT 1864, 'DEMO-V073-ARCHIVE-REVIEWING', 2, 'CHECK', 'PREPARING', 'CHECKED', '检查通过', @demo_archive_actor_id, 4
|
|
UNION ALL SELECT 1865, 'DEMO-V073-ARCHIVE-REVIEWING', 3, 'SUBMIT', 'CHECKED', 'REVIEWING', '提交归档审核', @demo_archive_actor_id, 3
|
|
UNION ALL SELECT 1866, 'DEMO-V073-ARCHIVE-RETURNED', 1, 'RETURN', 'REVIEWING', 'RETURNED', '退回补件', @demo_finance_actor_id, 2
|
|
UNION ALL SELECT 1867, 'DEMO-V073-ARCHIVE-RETURNED', 2, 'REVISE', 'RETURNED', 'PREPARING', '准备重新补件', @demo_archive_actor_id, 1
|
|
UNION ALL SELECT 1868, 'DEMO-V073-ARCHIVE-ARCHIVED', 1, 'ARCHIVE', 'REVIEWING', 'ARCHIVED', '归档完成', @demo_finance_actor_id, 2
|
|
UNION ALL SELECT 1869, 'DEMO-V073-ARCHIVE-FROZEN', 1, 'FREEZE', 'ARCHIVED', 'FROZEN', '稽核期间冻结', @demo_archive_actor_id, 1
|
|
UNION ALL SELECT 1870, 'DEMO-V073-ARCHIVE-FROZEN', 2, 'UNFREEZE', 'FROZEN', 'ARCHIVED', '演示解冻记录', @demo_archive_actor_id, 0
|
|
UNION ALL SELECT 1871, 'DEMO-V073-ARCHIVE-PREPARING', 2, 'NA_REQUEST', 'PREPARING', 'PREPARING', '申请不适用', @demo_finance_actor_id, 3
|
|
UNION ALL SELECT 1872, 'DEMO-V073-ARCHIVE-FROZEN', 3, 'NA_APPROVE', 'FROZEN', 'FROZEN', '不适用复核通过', @demo_finance_actor_id, 1
|
|
UNION ALL SELECT 1873, 'DEMO-V073-ARCHIVE-RETURNED', 3, 'NA_REJECT', 'RETURNED', 'RETURNED', '不适用申请驳回', @demo_finance_actor_id, 1
|
|
) action_seed
|
|
JOIN arc_package package_record ON package_record.business_no = action_seed.package_no;
|
|
|
|
INSERT INTO arc_borrow (
|
|
public_id, business_no, file_id, file_link_id, series_public_id, package_id,
|
|
applicant_id, purpose, due_at, status, approved_by, approved_at,
|
|
review_opinion, returned_by, returned_at, return_condition, created_at, updated_at
|
|
)
|
|
SELECT CONCAT('D', LPAD(borrow_seed.seq, 25, '0')), borrow_seed.business_no,
|
|
file_record.id, link_record.id, link_record.series_public_id,
|
|
@demo_package_archived_id, @demo_project_actor_id, borrow_seed.purpose,
|
|
CASE WHEN borrow_seed.status = 'OVERDUE' THEN DATE_SUB(@demo_now, INTERVAL 2 DAY)
|
|
ELSE DATE_ADD(@demo_now, INTERVAL borrow_seed.due_days DAY) END,
|
|
borrow_seed.status,
|
|
CASE WHEN borrow_seed.status IN ('APPROVED', 'BORROWED', 'RETURNED', 'REJECTED', 'OVERDUE') THEN @demo_archive_actor_id END,
|
|
CASE WHEN borrow_seed.status IN ('APPROVED', 'BORROWED', 'RETURNED', 'REJECTED', 'OVERDUE') THEN DATE_SUB(@demo_now, INTERVAL 2 DAY) END,
|
|
CASE WHEN borrow_seed.status = 'REJECTED' THEN '借阅用途不符合归档权限' ELSE '演示借阅审核' END,
|
|
CASE WHEN borrow_seed.status = 'RETURNED' THEN @demo_archive_actor_id END,
|
|
CASE WHEN borrow_seed.status = 'RETURNED' THEN DATE_SUB(@demo_now, INTERVAL 1 DAY) END,
|
|
CASE WHEN borrow_seed.status = 'RETURNED' THEN '资料完好归还' END,
|
|
DATE_SUB(@demo_now, INTERVAL 4 DAY), @demo_now
|
|
FROM (
|
|
SELECT 1880 AS seq, 'DEMO-V073-BORROW-APPLIED' AS business_no, 1 AS file_seq, 'APPLIED' AS status, '项目现场查阅' AS purpose, 7 AS due_days
|
|
UNION ALL SELECT 1881, 'DEMO-V073-BORROW-APPROVED', 2, 'APPROVED', '合同复核借阅', 10
|
|
UNION ALL SELECT 1882, 'DEMO-V073-BORROW-BORROWED', 3, 'BORROWED', '审计抽查借阅', 14
|
|
UNION ALL SELECT 1883, 'DEMO-V073-BORROW-RETURNED', 4, 'RETURNED', '历史资料借阅', 7
|
|
UNION ALL SELECT 1884, 'DEMO-V073-BORROW-REJECTED', 5, 'REJECTED', '外部借阅申请', 7
|
|
UNION ALL SELECT 1885, 'DEMO-V073-BORROW-OVERDUE', 6, 'OVERDUE', '项目资料借阅', 0
|
|
) borrow_seed
|
|
JOIN file_object file_record ON file_record.public_id = CONCAT('D', LPAD(112 + borrow_seed.file_seq, 25, '0'))
|
|
JOIN file_link link_record ON link_record.file_id = file_record.id AND link_record.active = TRUE;
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- 10. 查询导出、幂等记录、审计记录和导入完成标记
|
|
-- ---------------------------------------------------------------------------
|
|
INSERT INTO report_export_request (
|
|
public_id, actor_public_id, identity_code, scope_fingerprint,
|
|
view_scope_fingerprint, report_code, definition_version, filter_hash,
|
|
filter_json, columns_json, sort_code, estimated_rows, status,
|
|
export_log_public_id, expires_at, completed_at
|
|
)
|
|
VALUES
|
|
(CONCAT('D', LPAD(2000, 25, '0')), @demo_admin_public_id, 'SYSTEM_ADMIN', SHA2('DEMO-V073-SCOPE-1', 256),
|
|
SHA2('DEMO-V073-VIEW-SCOPE-1', 256), 'PROJECT_OVERVIEW', 'REPORT-V1', SHA2('DEMO-V073-FILTER-1', 256),
|
|
JSON_OBJECT('projectId', @demo_project_public_id), JSON_ARRAY('businessNo', 'name', 'status'), 'updatedAt,desc',
|
|
20, 'PREPARED', NULL, DATE_ADD(@demo_now, INTERVAL 1 HOUR), NULL),
|
|
(CONCAT('D', LPAD(2001, 25, '0')), @demo_admin_public_id, 'SYSTEM_ADMIN', SHA2('DEMO-V073-SCOPE-2', 256),
|
|
SHA2('DEMO-V073-VIEW-SCOPE-2', 256), 'PAYMENT_LEDGER', 'REPORT-V1', SHA2('DEMO-V073-FILTER-2', 256),
|
|
JSON_OBJECT('dateFrom', DATE_FORMAT(DATE_SUB(@demo_today, INTERVAL 30 DAY), '%Y-%m-%d'), 'dateTo', DATE_FORMAT(@demo_today, '%Y-%m-%d')),
|
|
JSON_ARRAY('businessNo', 'amount', 'status'), 'businessDate,desc', 8, 'COMPLETED',
|
|
CONCAT('D', LPAD(2010, 25, '0')), DATE_ADD(@demo_now, INTERVAL 1 HOUR), DATE_SUB(@demo_now, INTERVAL 10 MINUTE)),
|
|
(CONCAT('D', LPAD(2002, 25, '0')), @demo_admin_public_id, 'SYSTEM_ADMIN', SHA2('DEMO-V073-SCOPE-3', 256),
|
|
SHA2('DEMO-V073-VIEW-SCOPE-3', 256), 'ARCHIVE_COMPLETENESS', 'REPORT-V1', SHA2('DEMO-V073-FILTER-3', 256),
|
|
JSON_OBJECT('projectId', @demo_project_public_id), JSON_ARRAY('packageNo', 'completeness', 'missingCount'), 'packageVersion,desc',
|
|
6, 'EXPIRED', NULL, DATE_SUB(@demo_now, INTERVAL 1 DAY), NULL);
|
|
|
|
INSERT INTO export_log (
|
|
public_id, request_id, actor_public_id, identity_code, resource_type,
|
|
filter_json, columns_json, row_count, sha256, created_at
|
|
)
|
|
VALUES (
|
|
CONCAT('D', LPAD(2010, 25, '0')), CONCAT('D', LPAD(2100, 25, '0')), @demo_admin_public_id,
|
|
'SYSTEM_ADMIN', 'PAYMENT_LEDGER',
|
|
JSON_OBJECT('dateFrom', DATE_FORMAT(DATE_SUB(@demo_today, INTERVAL 30 DAY), '%Y-%m-%d'), 'dateTo', DATE_FORMAT(@demo_today, '%Y-%m-%d')),
|
|
JSON_ARRAY('businessNo', 'amount', 'status'), 8, SHA2('DEMO-V073-EXPORT-REPORT', 256), DATE_SUB(@demo_now, INTERVAL 10 MINUTE)
|
|
);
|
|
|
|
UPDATE report_export_request
|
|
SET export_log_public_id = CONCAT('D', LPAD(2010, 25, '0'))
|
|
WHERE public_id = CONCAT('D', LPAD(2001, 25, '0'));
|
|
|
|
INSERT INTO sys_idempotency_record (
|
|
idempotency_key, user_public_id, command_name, request_hash,
|
|
response_status, response_json, state, expires_at
|
|
)
|
|
VALUES
|
|
('DEMO-V073-IDEMPOTENCY-STARTED', @demo_admin_public_id, 'DEMO_COMMAND_STARTED', SHA2('DEMO-V073-STARTED', 256),
|
|
NULL, NULL, 'STARTED', DATE_ADD(@demo_now, INTERVAL 1 HOUR)),
|
|
('DEMO-V073-IDEMPOTENCY-COMPLETED', @demo_admin_public_id, 'DEMO_COMMAND_COMPLETED', SHA2('DEMO-V073-COMPLETED', 256),
|
|
200, JSON_OBJECT('demo', TRUE, 'message', '幂等命令已完成'), 'COMPLETED', DATE_ADD(@demo_now, INTERVAL 1 HOUR)),
|
|
('DEMO-V073-IDEMPOTENCY-FAILED', @demo_admin_public_id, 'DEMO_COMMAND_FAILED', SHA2('DEMO-V073-FAILED', 256),
|
|
409, JSON_OBJECT('demo', TRUE, 'message', '幂等命令失败后可重试'), 'FAILED', DATE_ADD(@demo_now, INTERVAL 1 HOUR));
|
|
|
|
INSERT INTO audit_log (
|
|
public_id, request_id, user_public_id, username, active_role,
|
|
company_public_id, project_public_id, action_code, object_type,
|
|
object_public_id, result_code, reason, before_json, after_json,
|
|
ip_address, user_agent, created_at
|
|
)
|
|
VALUES
|
|
(CONCAT('D', LPAD(2200, 25, '0')), CONCAT('D', LPAD(2210, 25, '0')), @demo_admin_public_id, @demo_admin_username, 'SYSTEM_ADMIN',
|
|
@demo_company_public_id, @demo_project_public_id, 'PROJECT_CREATE', 'PROJECT', @demo_project_public_id, 'SUCCESS', '演示项目创建', NULL,
|
|
JSON_OBJECT('status', 'ACTIVE', 'dataset', @demo_dataset_code), '127.0.0.1', 'kaidi-demo-sql', DATE_SUB(@demo_now, INTERVAL 3 DAY)),
|
|
(CONCAT('D', LPAD(2201, 25, '0')), CONCAT('D', LPAD(2211, 25, '0')), @demo_admin_public_id, @demo_admin_username, 'SYSTEM_ADMIN',
|
|
@demo_company_public_id, @demo_project_public_id, 'PAYMENT_APPROVE', 'PAYMENT', CONCAT('D', LPAD(1304, 25, '0')), 'DENIED', '演示权限不足',
|
|
JSON_OBJECT('status', 'REVIEWING'), NULL, '127.0.0.1', 'kaidi-demo-sql', DATE_SUB(@demo_now, INTERVAL 2 DAY)),
|
|
(CONCAT('D', LPAD(2202, 25, '0')), CONCAT('D', LPAD(2212, 25, '0')), @demo_admin_public_id, @demo_admin_username, 'SYSTEM_ADMIN',
|
|
@demo_company_public_id, @demo_project_public_id, 'ARCHIVE_CHECK', 'ARCHIVE_PACKAGE', CONCAT('D', LPAD(1813, 25, '0')), 'FAILED', '演示档案缺少资料',
|
|
JSON_OBJECT('status', 'REVIEWING'), JSON_OBJECT('status', 'RETURNED', 'missingCount', 2), '127.0.0.1', 'kaidi-demo-sql', DATE_SUB(@demo_now, INTERVAL 1 DAY)),
|
|
(CONCAT('D', LPAD(2203, 25, '0')), CONCAT('D', LPAD(2213, 25, '0')), @demo_admin_public_id, @demo_admin_username, 'SYSTEM_ADMIN',
|
|
NULL, NULL, 'LOGIN', 'SESSION', NULL, 'SUCCESS', '演示登录记录', NULL,
|
|
JSON_OBJECT('identity', 'SYSTEM_ADMIN'), '127.0.0.1', 'kaidi-demo-sql', DATE_SUB(@demo_now, INTERVAL 30 MINUTE));
|
|
|
|
-- 该行是本数据集的幂等标记；前置检查会拒绝第二次导入。
|
|
INSERT INTO audit_log (
|
|
public_id, request_id, user_public_id, username, active_role,
|
|
action_code, object_type, object_public_id, result_code, reason,
|
|
before_json, after_json, ip_address, user_agent, created_at
|
|
)
|
|
VALUES (
|
|
CONCAT('D', LPAD(99998, 25, '0')), CONCAT('D', LPAD(99997, 25, '0')),
|
|
@demo_admin_public_id, @demo_admin_username, 'SYSTEM_ADMIN',
|
|
'DEMO_DATA_IMPORT', 'DEMO_DATASET', @demo_marker_public_id, 'SUCCESS',
|
|
'Kaidi Finance V073 全功能演示数据导入完成', NULL,
|
|
JSON_OBJECT('dataset', @demo_dataset_code, 'version', 'V073',
|
|
'source', 'deploy/demo-data-mysql8-v073.sql'),
|
|
'127.0.0.1', 'kaidi-demo-sql', @demo_now
|
|
);
|
|
|
|
COMMIT;
|
|
|
|
-- 导入结束后输出可核验摘要；这些查询只读，不会修改数据。
|
|
SELECT 'KAIDI_DEMO_V073_IMPORTED' AS dataset,
|
|
@demo_admin_username AS imported_by,
|
|
@demo_now AS imported_at;
|
|
|
|
SELECT 'source_documents' AS object_name, COUNT(*) AS demo_count
|
|
FROM src_source_document WHERE business_no LIKE 'DEMO-V073-%'
|
|
UNION ALL SELECT 'projects', COUNT(*) FROM md_project WHERE business_no LIKE 'DEMO-V073-%'
|
|
UNION ALL SELECT 'contracts', COUNT(*) FROM md_contract WHERE business_no LIKE 'DEMO-V073-%'
|
|
UNION ALL SELECT 'payables', COUNT(*) FROM fin_payable WHERE business_no LIKE 'DEMO-V073-%'
|
|
UNION ALL SELECT 'receipts', COUNT(*) FROM fin_receipt WHERE business_no LIKE 'DEMO-V073-%'
|
|
UNION ALL SELECT 'invoices', COUNT(*) FROM fin_invoice WHERE business_no LIKE 'DEMO-V073-%'
|
|
UNION ALL SELECT 'payments', COUNT(*) FROM fin_payment_request WHERE business_no LIKE 'DEMO-V073-%'
|
|
UNION ALL SELECT 'vouchers', COUNT(*) FROM acc_voucher WHERE business_no LIKE 'DEMO-V073-%'
|
|
UNION ALL SELECT 'archive_packages', COUNT(*) FROM arc_package WHERE business_no LIKE 'DEMO-V073-%'
|
|
UNION ALL SELECT 'borrows', COUNT(*) FROM arc_borrow WHERE business_no LIKE 'DEMO-V073-%';
|
|
|
|
SELECT 'source_status' AS status_group, status, COUNT(*) AS row_count
|
|
FROM src_source_document WHERE business_no LIKE 'DEMO-V073-%' GROUP BY status
|
|
UNION ALL SELECT 'payment_status', status, COUNT(*) FROM fin_payment_request WHERE business_no LIKE 'DEMO-V073-%' GROUP BY status
|
|
UNION ALL SELECT 'voucher_status', status, COUNT(*) FROM acc_voucher WHERE business_no LIKE 'DEMO-V073-%' GROUP BY status
|
|
UNION ALL SELECT 'archive_status', status, COUNT(*) FROM arc_package WHERE business_no LIKE 'DEMO-V073-%' GROUP BY status
|
|
UNION ALL SELECT 'borrow_status', status, COUNT(*) FROM arc_borrow WHERE business_no LIKE 'DEMO-V073-%' GROUP BY status;
|