shanxia2.0/docs/sql/migrations/2026-09-26-pl-vehicle-recharge-workflow.sql

-- 车辆 ETC/加油充值：可配置审批与财务发放登记
-- Spec: docs/superpowers/specs/2026-09-26-pl-vehicle-recharge-workflow-design.md
-- Plan Task 1: 结构 + 字典 + 菜单 + 流程种子 + 存量 pending 迁移
-- 可重复执行（列/菜单/字典/角色授权均做存在性判断）

SET NAMES utf8mb4;

-- =============================================================================
-- 1) bx_workflow_node.action_type
-- =============================================================================
SET @db := DATABASE();
SET @col_exists := (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @db
    AND TABLE_NAME = 'bx_workflow_node'
    AND COLUMN_NAME = 'action_type'
);
SET @sql := IF(
  @col_exists = 0,
  'ALTER TABLE `bx_workflow_node`
     ADD COLUMN `action_type` varchar(20) NOT NULL DEFAULT ''approve''
     COMMENT ''approve-审批 disburse-发放登记'' AFTER `approver_name`',
  'SELECT ''bx_workflow_node.action_type already exists'' AS info'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- =============================================================================
-- 2) pl_vehicle_recharge_apply：wf_id / current_node / approval_round + status 注释
-- =============================================================================
SET @col_exists := (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @db
    AND TABLE_NAME = 'pl_vehicle_recharge_apply'
    AND COLUMN_NAME = 'wf_id'
);
SET @sql := IF(
  @col_exists = 0,
  'ALTER TABLE `pl_vehicle_recharge_apply`
     ADD COLUMN `wf_id` varchar(64) DEFAULT NULL COMMENT ''工作流定义ID'' AFTER `status`',
  'SELECT ''pl_vehicle_recharge_apply.wf_id already exists'' AS info'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists := (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @db
    AND TABLE_NAME = 'pl_vehicle_recharge_apply'
    AND COLUMN_NAME = 'current_node'
);
SET @sql := IF(
  @col_exists = 0,
  'ALTER TABLE `pl_vehicle_recharge_apply`
     ADD COLUMN `current_node` varchar(64) DEFAULT NULL COMMENT ''当前节点ID'' AFTER `wf_id`',
  'SELECT ''pl_vehicle_recharge_apply.current_node already exists'' AS info'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists := (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @db
    AND TABLE_NAME = 'pl_vehicle_recharge_apply'
    AND COLUMN_NAME = 'approval_round'
);
SET @sql := IF(
  @col_exists = 0,
  'ALTER TABLE `pl_vehicle_recharge_apply`
     ADD COLUMN `approval_round` int(11) NOT NULL DEFAULT 0 COMMENT ''当前审批轮次'' AFTER `current_node`',
  'SELECT ''pl_vehicle_recharge_apply.approval_round already exists'' AS info'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

ALTER TABLE `pl_vehicle_recharge_apply`
  MODIFY COLUMN `status` varchar(20) NOT NULL DEFAULT 'draft'
  COMMENT 'draft-草稿 in_approval-审批中 rejected-已驳回 disbursed-已发放 cancelled-已作废';

-- =============================================================================
-- 3) 审批历史表
-- =============================================================================
CREATE TABLE IF NOT EXISTS `pl_vehicle_recharge_approval` (
  `id` varchar(64) NOT NULL COMMENT '主键ID',
  `apply_id` varchar(64) NOT NULL COMMENT '申请单ID',
  `approval_round` int(11) NOT NULL DEFAULT 1 COMMENT '审批轮次',
  `node_id` varchar(64) DEFAULT NULL COMMENT '节点ID快照',
  `node_name` varchar(128) DEFAULT NULL COMMENT '节点名称快照',
  `approver_id` varchar(64) NOT NULL COMMENT '审批人用户ID',
  `approver_name` varchar(64) NOT NULL COMMENT '审批人姓名',
  `approval_status` varchar(32) NOT NULL COMMENT 'approved/rejected/disbursed',
  `approval_opinion` varchar(1024) DEFAULT NULL COMMENT '审批意见',
  `approval_time` datetime NOT NULL COMMENT '审批时间',
  `tenant_no` varchar(64) NOT NULL DEFAULT 'shanxia2.0' COMMENT '租户编号',
  `order_num` int(11) DEFAULT NULL COMMENT '排序号',
  `remark` varchar(512) DEFAULT NULL COMMENT '备注',
  `create_by` varchar(64) DEFAULT NULL COMMENT '创建人id',
  `create_dept_id` varchar(64) NOT NULL DEFAULT '' COMMENT '创建人部门ID',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT NULL COMMENT '更新人id',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `del_flag` smallint(6) NOT NULL DEFAULT 0 COMMENT '删除标记：0-正常 1-已删除',
  PRIMARY KEY (`id`),
  KEY `idx_apply_id` (`apply_id`),
  KEY `idx_tenant` (`tenant_no`),
  KEY `idx_approver` (`approver_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='车辆充值申请审批历史';

-- =============================================================================
-- 4) 流程定义 + 节点种子
-- =============================================================================
INSERT INTO `bx_workflow_def` (
  `id`, `biz_type`, `wf_name`, `status`, `tenant_no`, `order_num`, `remark`,
  `create_by`, `create_dept_id`, `create_time`, `del_flag`
)
VALUES (
  'PL_WF_VEHICLE_RECHARGE', 'vehicle_recharge', 'ETC/加油充值审批流程', 1,
  'shanxia2.0', 0, '充值申请默认审批流程', 'system', '', NOW(), 0
)
ON DUPLICATE KEY UPDATE
  `wf_name` = VALUES(`wf_name`),
  `biz_type` = VALUES(`biz_type`),
  `status` = VALUES(`status`),
  `del_flag` = 0,
  `remark` = VALUES(`remark`);

INSERT INTO `bx_workflow_node` (
  `id`, `wf_id`, `node_name`, `node_order`, `approver_id`, `approver_name`,
  `action_type`, `tenant_no`, `order_num`, `remark`,
  `create_by`, `create_dept_id`, `create_time`, `del_flag`
)
VALUES
(
  'PL_WR_NODE_GM', 'PL_WF_VEHICLE_RECHARGE', '总经理审批', 1,
  '56D1C9DA5F6747DDAD25C248A156A524', '吴九华', 'approve',
  'shanxia2.0', 0, NULL, 'system', '', NOW(), 0
),
(
  'PL_WR_NODE_FIN', 'PL_WF_VEHICLE_RECHARGE', '财务发放', 2,
  'CC2EED9A86F44D11AFFD0AD381893093', '汪璇', 'disburse',
  'shanxia2.0', 0, NULL, 'system', '', NOW(), 0
)
ON DUPLICATE KEY UPDATE
  `wf_id` = VALUES(`wf_id`),
  `node_name` = VALUES(`node_name`),
  `node_order` = VALUES(`node_order`),
  `approver_id` = VALUES(`approver_id`),
  `approver_name` = VALUES(`approver_name`),
  `action_type` = VALUES(`action_type`),
  `del_flag` = 0;

-- =============================================================================
-- 5) 字典 pl_recharge_apply_status
--    软停用旧 pending；补齐 draft/in_approval/rejected；保留 disbursed/cancelled
-- =============================================================================
UPDATE `sys_dict_data`
SET `del_flag` = 1,
    `remark` = '充值审批改造：pending(待发放)已废弃，存量已迁移为 in_approval',
    `update_time` = NOW()
WHERE `id` = 'PL_RECHARGE_APPLY_PENDING'
  AND `dict_type` = 'pl_recharge_apply_status'
  AND `del_flag` = 0;

INSERT INTO `sys_dict_data` (
  `id`, `dict_label`, `dict_value`, `tag_type`, `dict_type`, `tenant_no`,
  `order_num`, `remark`, `create_by`, `create_dept_id`, `create_time`, `del_flag`
)
SELECT
  'PL_RECHARGE_APPLY_DRAFT', '草稿', 'draft', 'info', 'pl_recharge_apply_status',
  'shanxia2.0', 1, NULL, 'system', '', NOW(), 0
FROM DUAL
WHERE NOT EXISTS (
  SELECT 1 FROM `sys_dict_data` WHERE `id` = 'PL_RECHARGE_APPLY_DRAFT'
);

INSERT INTO `sys_dict_data` (
  `id`, `dict_label`, `dict_value`, `tag_type`, `dict_type`, `tenant_no`,
  `order_num`, `remark`, `create_by`, `create_dept_id`, `create_time`, `del_flag`
)
SELECT
  'PL_RECHARGE_APPLY_IN_APPROVAL', '审批中', 'in_approval', 'warning', 'pl_recharge_apply_status',
  'shanxia2.0', 2, NULL, 'system', '', NOW(), 0
FROM DUAL
WHERE NOT EXISTS (
  SELECT 1 FROM `sys_dict_data` WHERE `id` = 'PL_RECHARGE_APPLY_IN_APPROVAL'
);

INSERT INTO `sys_dict_data` (
  `id`, `dict_label`, `dict_value`, `tag_type`, `dict_type`, `tenant_no`,
  `order_num`, `remark`, `create_by`, `create_dept_id`, `create_time`, `del_flag`
)
SELECT
  'PL_RECHARGE_APPLY_REJECTED', '已驳回', 'rejected', 'danger', 'pl_recharge_apply_status',
  'shanxia2.0', 3, NULL, 'system', '', NOW(), 0
FROM DUAL
WHERE NOT EXISTS (
  SELECT 1 FROM `sys_dict_data` WHERE `id` = 'PL_RECHARGE_APPLY_REJECTED'
);

UPDATE `sys_dict_data`
SET `dict_label` = '已发放',
    `tag_type` = 'success',
    `order_num` = 4,
    `del_flag` = 0,
    `update_time` = NOW()
WHERE `id` = 'PL_RECHARGE_APPLY_DISBURSED'
  AND `dict_type` = 'pl_recharge_apply_status';

UPDATE `sys_dict_data`
SET `dict_label` = '已作废',
    `tag_type` = 'info',
    `order_num` = 5,
    `del_flag` = 0,
    `update_time` = NOW()
WHERE `id` = 'PL_RECHARGE_APPLY_CANCELLED'
  AND `dict_type` = 'pl_recharge_apply_status';

-- 幂等：若上述 INSERT 因已存在跳过，仍校正文案/排序/启用态
UPDATE `sys_dict_data`
SET `dict_label` = '草稿', `tag_type` = 'info', `order_num` = 1, `del_flag` = 0, `update_time` = NOW()
WHERE `id` = 'PL_RECHARGE_APPLY_DRAFT';

UPDATE `sys_dict_data`
SET `dict_label` = '审批中', `tag_type` = 'warning', `order_num` = 2, `del_flag` = 0, `update_time` = NOW()
WHERE `id` = 'PL_RECHARGE_APPLY_IN_APPROVAL';

UPDATE `sys_dict_data`
SET `dict_label` = '已驳回', `tag_type` = 'danger', `order_num` = 3, `del_flag` = 0, `update_time` = NOW()
WHERE `id` = 'PL_RECHARGE_APPLY_REJECTED';

-- =============================================================================
-- 6) 菜单：充值审批 / 充值审批流程（挂 PL_GROUP_VEHICLE）
--    排序：申请(1) → 审批(2) → 发放登记(3) → 流程配置(4) → 其余顺延
--    仅当发放登记仍为 order_num=2（首次）时整体后移，避免重复执行叠位
-- =============================================================================
SET @record_ord := (
  SELECT `order_num` FROM `sys_menu`
  WHERE `id` = 'PL_VEH_RECHARGE_RECORD' AND `del_flag` = 0
  LIMIT 1
);
UPDATE `sys_menu`
SET `order_num` = `order_num` + 2,
    `update_time` = NOW()
WHERE @record_ord = 2
  AND `parent_id` = 'PL_GROUP_VEHICLE'
  AND `order_num` >= 2
  AND `del_flag` = 0
  AND `id` NOT IN ('PL_VEH_RECHARGE_APPROVAL', 'PL_VEH_RECHARGE_WF');

UPDATE `sys_menu`
SET `order_num` = 3, `update_time` = NOW()
WHERE `id` = 'PL_VEH_RECHARGE_RECORD' AND `del_flag` = 0;

-- 充值审批（待办）
INSERT INTO `sys_menu` (
  `id`, `menu_name`, `parent_id`, `menu_type`, `path`, `component`, `perms`,
  `is_frame`, `visible`, `status`, `tenant_no`, `order_num`, `remark`,
  `create_by`, `create_dept_id`, `create_time`, `update_by`, `update_time`, `del_flag`
)
SELECT
  'PL_VEH_RECHARGE_APPROVAL', '充值审批', 'PL_GROUP_VEHICLE', 'C',
  'vehicle-recharge-approval', 'pl/vehicle-recharge-approval/index',
  'pl:vehicle-recharge-approval:list', 1, '1', '1', 'shanxia2.0', 2, NULL,
  'system', '', NOW(), NULL, NOW(), 0
FROM DUAL
WHERE NOT EXISTS (
  SELECT 1 FROM `sys_menu` WHERE `id` = 'PL_VEH_RECHARGE_APPROVAL' AND `del_flag` = 0
);

UPDATE `sys_menu`
SET `menu_name` = '充值审批',
    `path` = 'vehicle-recharge-approval',
    `component` = 'pl/vehicle-recharge-approval/index',
    `perms` = 'pl:vehicle-recharge-approval:list',
    `order_num` = 2,
    `visible` = '1',
    `status` = '1',
    `update_time` = NOW()
WHERE `id` = 'PL_VEH_RECHARGE_APPROVAL' AND `del_flag` = 0;

INSERT INTO `sys_menu` (
  `id`, `menu_name`, `parent_id`, `menu_type`, `path`, `component`, `perms`,
  `is_frame`, `visible`, `status`, `tenant_no`, `order_num`, `remark`,
  `create_by`, `create_dept_id`, `create_time`, `update_by`, `update_time`, `del_flag`
)
SELECT 'PL_VRAP_Q', '查询', 'PL_VEH_RECHARGE_APPROVAL', 'F', '', '',
  'pl:vehicle-recharge-approval:query', 1, '1', '1', 'shanxia2.0', 1, NULL,
  'system', '', NOW(), NULL, NOW(), 0
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `id` = 'PL_VRAP_Q' AND `del_flag` = 0);

INSERT INTO `sys_menu` (
  `id`, `menu_name`, `parent_id`, `menu_type`, `path`, `component`, `perms`,
  `is_frame`, `visible`, `status`, `tenant_no`, `order_num`, `remark`,
  `create_by`, `create_dept_id`, `create_time`, `update_by`, `update_time`, `del_flag`
)
SELECT 'PL_VRAP_A', '通过', 'PL_VEH_RECHARGE_APPROVAL', 'F', '', '',
  'pl:vehicle-recharge-approval:approve', 1, '1', '1', 'shanxia2.0', 2, NULL,
  'system', '', NOW(), NULL, NOW(), 0
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `id` = 'PL_VRAP_A' AND `del_flag` = 0);

INSERT INTO `sys_menu` (
  `id`, `menu_name`, `parent_id`, `menu_type`, `path`, `component`, `perms`,
  `is_frame`, `visible`, `status`, `tenant_no`, `order_num`, `remark`,
  `create_by`, `create_dept_id`, `create_time`, `update_by`, `update_time`, `del_flag`
)
SELECT 'PL_VRAP_J', '驳回', 'PL_VEH_RECHARGE_APPROVAL', 'F', '', '',
  'pl:vehicle-recharge-approval:reject', 1, '1', '1', 'shanxia2.0', 3, NULL,
  'system', '', NOW(), NULL, NOW(), 0
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `id` = 'PL_VRAP_J' AND `del_flag` = 0);

INSERT INTO `sys_menu` (
  `id`, `menu_name`, `parent_id`, `menu_type`, `path`, `component`, `perms`,
  `is_frame`, `visible`, `status`, `tenant_no`, `order_num`, `remark`,
  `create_by`, `create_dept_id`, `create_time`, `update_by`, `update_time`, `del_flag`
)
SELECT 'PL_VRAP_D', '发放', 'PL_VEH_RECHARGE_APPROVAL', 'F', '', '',
  'pl:vehicle-recharge-approval:disburse', 1, '1', '1', 'shanxia2.0', 4, NULL,
  'system', '', NOW(), NULL, NOW(), 0
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `id` = 'PL_VRAP_D' AND `del_flag` = 0);

-- 充值审批流程（配置）
INSERT INTO `sys_menu` (
  `id`, `menu_name`, `parent_id`, `menu_type`, `path`, `component`, `perms`,
  `is_frame`, `visible`, `status`, `tenant_no`, `order_num`, `remark`,
  `create_by`, `create_dept_id`, `create_time`, `update_by`, `update_time`, `del_flag`
)
SELECT
  'PL_VEH_RECHARGE_WF', '充值审批流程', 'PL_GROUP_VEHICLE', 'C',
  'vehicle-recharge-workflow', 'pl/vehicle-recharge-workflow/index',
  'pl:vehicle-recharge-workflow:list', 1, '1', '1', 'shanxia2.0', 4, NULL,
  'system', '', NOW(), NULL, NOW(), 0
FROM DUAL
WHERE NOT EXISTS (
  SELECT 1 FROM `sys_menu` WHERE `id` = 'PL_VEH_RECHARGE_WF' AND `del_flag` = 0
);

UPDATE `sys_menu`
SET `menu_name` = '充值审批流程',
    `path` = 'vehicle-recharge-workflow',
    `component` = 'pl/vehicle-recharge-workflow/index',
    `perms` = 'pl:vehicle-recharge-workflow:list',
    `order_num` = 4,
    `visible` = '1',
    `status` = '1',
    `update_time` = NOW()
WHERE `id` = 'PL_VEH_RECHARGE_WF' AND `del_flag` = 0;

INSERT INTO `sys_menu` (
  `id`, `menu_name`, `parent_id`, `menu_type`, `path`, `component`, `perms`,
  `is_frame`, `visible`, `status`, `tenant_no`, `order_num`, `remark`,
  `create_by`, `create_dept_id`, `create_time`, `update_by`, `update_time`, `del_flag`
)
SELECT 'PL_VRWF_Q', '查询', 'PL_VEH_RECHARGE_WF', 'F', '', '',
  'pl:vehicle-recharge-workflow:query', 1, '1', '1', 'shanxia2.0', 1, NULL,
  'system', '', NOW(), NULL, NOW(), 0
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `id` = 'PL_VRWF_Q' AND `del_flag` = 0);

INSERT INTO `sys_menu` (
  `id`, `menu_name`, `parent_id`, `menu_type`, `path`, `component`, `perms`,
  `is_frame`, `visible`, `status`, `tenant_no`, `order_num`, `remark`,
  `create_by`, `create_dept_id`, `create_time`, `update_by`, `update_time`, `del_flag`
)
SELECT 'PL_VRWF_E', '编辑', 'PL_VEH_RECHARGE_WF', 'F', '', '',
  'pl:vehicle-recharge-workflow:edit', 1, '1', '1', 'shanxia2.0', 2, NULL,
  'system', '', NOW(), NULL, NOW(), 0
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `id` = 'PL_VRWF_E' AND `del_flag` = 0);

INSERT INTO `sys_menu` (
  `id`, `menu_name`, `parent_id`, `menu_type`, `path`, `component`, `perms`,
  `is_frame`, `visible`, `status`, `tenant_no`, `order_num`, `remark`,
  `create_by`, `create_dept_id`, `create_time`, `update_by`, `update_time`, `del_flag`
)
SELECT 'PL_VRWF_S', '保存', 'PL_VEH_RECHARGE_WF', 'F', '', '',
  'pl:vehicle-recharge-workflow:save', 1, '1', '1', 'shanxia2.0', 3, NULL,
  'system', '', NOW(), NULL, NOW(), 0
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `id` = 'PL_VRWF_S' AND `del_flag` = 0);

-- 角色授权：已拥有充值申请（C 菜单或查询按钮）的角色同步获得新菜单
INSERT INTO `sys_role_menu` (
  `id`, `role_id`, `menu_id`, `tenant_no`, `order_num`, `remark`,
  `create_by`, `create_dept_id`, `create_time`, `update_by`, `update_time`, `del_flag`
)
SELECT
  MD5(CONCAT(rm.role_id, '|', m.menu_id, '|pl-veh-recharge-wf|v1')),
  rm.role_id,
  m.menu_id,
  IFNULL(NULLIF(rm.tenant_no, ''), 'shanxia2.0'),
  0,
  '充值审批改造：同步授权',
  'system',
  '',
  NOW(),
  NULL,
  NOW(),
  0
FROM (
  SELECT DISTINCT `role_id`, `tenant_no`
  FROM `sys_role_menu`
  WHERE `menu_id` IN ('PL_VEH_RECHARGE_APPLY', 'PL_VRA_Q')
    AND `del_flag` = 0
) rm
JOIN (
  SELECT 'PL_VEH_RECHARGE_APPROVAL' AS menu_id UNION ALL
  SELECT 'PL_VRAP_Q' UNION ALL
  SELECT 'PL_VRAP_A' UNION ALL
  SELECT 'PL_VRAP_J' UNION ALL
  SELECT 'PL_VRAP_D' UNION ALL
  SELECT 'PL_VEH_RECHARGE_WF' UNION ALL
  SELECT 'PL_VRWF_Q' UNION ALL
  SELECT 'PL_VRWF_E' UNION ALL
  SELECT 'PL_VRWF_S'
) m
WHERE NOT EXISTS (
  SELECT 1 FROM `sys_role_menu` x
  WHERE x.role_id = rm.role_id AND x.menu_id = m.menu_id AND x.del_flag = 0
);

-- =============================================================================
-- 7) 存量：pending 且无发放记录 → 审批中（落在财务发放节点）
-- =============================================================================
UPDATE `pl_vehicle_recharge_apply` a
LEFT JOIN `pl_vehicle_recharge_record` r
  ON r.apply_id = a.id AND r.del_flag = 0
SET a.status = 'in_approval',
    a.wf_id = 'PL_WF_VEHICLE_RECHARGE',
    a.current_node = 'PL_WR_NODE_FIN',
    a.approval_round = 1,
    a.update_time = NOW()
WHERE a.del_flag = 0
  AND a.status = 'pending'
  AND r.id IS NULL;