shanxia2.0/docs/sql/migrations/2026-10-25-pl-vehicle-fuel-card.sql

-- 车油卡信息：P01 独立台账，9 条经用户确认的绑定。
-- MySQL 5.7 / utf8mb4。DDL 自动提交；其后菜单及初始数据使用一个事务。
SET NAMES utf8mb4;
CREATE TABLE IF NOT EXISTS `pl_vehicle_fuel_card` (
  `id` varchar(64) NOT NULL COMMENT '主键UUID',
  `license_plate` varchar(20) NOT NULL COMMENT '车牌号',
  `card_no` varchar(64) NOT NULL COMMENT '油卡卡号，字符串保留全部数字',
  `tenant_no` varchar(64) NOT NULL COMMENT '租户编号',
  `order_num` int DEFAULT NULL COMMENT '排序号',
  `remark` varchar(512) DEFAULT NULL COMMENT '备注',
  `create_by` varchar(64) DEFAULT NULL COMMENT '创建人',
  `create_dept_id` varchar(64) NOT NULL DEFAULT '' COMMENT '创建部门',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT NULL COMMENT '更新人',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `del_flag` smallint NOT NULL DEFAULT 0 COMMENT '删除标记',
  `active_marker` int GENERATED ALWAYS AS (CASE WHEN `del_flag` = 0 THEN 1 ELSE NULL END) STORED COMMENT '有效绑定唯一索引辅助列',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_fuel_card_plate` (`tenant_no`,`license_plate`,`active_marker`),
  UNIQUE KEY `uk_fuel_card_number` (`tenant_no`,`card_no`,`active_marker`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='车油卡信息';

START TRANSACTION;
-- 固定 UUID 防止重复导入，也不恢复已软删除的种子或覆盖人工编辑。
INSERT INTO `pl_vehicle_fuel_card`
  (`id`,`license_plate`,`card_no`,`tenant_no`,`remark`,`create_by`,`create_dept_id`)
SELECT '760c34c6-8119-52dc-bebe-25ba37e63c9b','皖HA3779','1000113400007229003','shanxia2.0','','system',''
FROM DUAL WHERE NOT EXISTS (
  SELECT 1 FROM `pl_vehicle_fuel_card`
  WHERE `id`='760c34c6-8119-52dc-bebe-25ba37e63c9b' OR (`tenant_no`='shanxia2.0' AND `del_flag`=0
    AND (`license_plate`='皖HA3779' OR `card_no`='1000113400007229003'))
);
INSERT INTO `pl_vehicle_fuel_card`
  (`id`,`license_plate`,`card_no`,`tenant_no`,`remark`,`create_by`,`create_dept_id`)
SELECT '6e4d07ef-7e5b-5455-8ecc-2b62ca3b2980','皖H21522','1000113400008366476','shanxia2.0','','system',''
FROM DUAL WHERE NOT EXISTS (
  SELECT 1 FROM `pl_vehicle_fuel_card`
  WHERE `id`='6e4d07ef-7e5b-5455-8ecc-2b62ca3b2980' OR (`tenant_no`='shanxia2.0' AND `del_flag`=0
    AND (`license_plate`='皖H21522' OR `card_no`='1000113400008366476'))
);
INSERT INTO `pl_vehicle_fuel_card`
  (`id`,`license_plate`,`card_no`,`tenant_no`,`remark`,`create_by`,`create_dept_id`)
SELECT '45edbfe7-eaa6-5896-803b-8b7c3f7000fa','皖H09177','1000113400008366597','shanxia2.0','','system',''
FROM DUAL WHERE NOT EXISTS (
  SELECT 1 FROM `pl_vehicle_fuel_card`
  WHERE `id`='45edbfe7-eaa6-5896-803b-8b7c3f7000fa' OR (`tenant_no`='shanxia2.0' AND `del_flag`=0
    AND (`license_plate`='皖H09177' OR `card_no`='1000113400008366597'))
);
INSERT INTO `pl_vehicle_fuel_card`
  (`id`,`license_plate`,`card_no`,`tenant_no`,`remark`,`create_by`,`create_dept_id`)
SELECT '9687e3e7-3c8e-57a3-9598-26d9b8f7f965','皖H57837','1000113400008449206','shanxia2.0','','system',''
FROM DUAL WHERE NOT EXISTS (
  SELECT 1 FROM `pl_vehicle_fuel_card`
  WHERE `id`='9687e3e7-3c8e-57a3-9598-26d9b8f7f965' OR (`tenant_no`='shanxia2.0' AND `del_flag`=0
    AND (`license_plate`='皖H57837' OR `card_no`='1000113400008449206'))
);
INSERT INTO `pl_vehicle_fuel_card`
  (`id`,`license_plate`,`card_no`,`tenant_no`,`remark`,`create_by`,`create_dept_id`)
SELECT '1188cc8c-3627-532e-8fe5-366e5b5dd2a4','皖H10291','1000113400009181778','shanxia2.0','','system',''
FROM DUAL WHERE NOT EXISTS (
  SELECT 1 FROM `pl_vehicle_fuel_card`
  WHERE `id`='1188cc8c-3627-532e-8fe5-366e5b5dd2a4' OR (`tenant_no`='shanxia2.0' AND `del_flag`=0
    AND (`license_plate`='皖H10291' OR `card_no`='1000113400009181778'))
);
INSERT INTO `pl_vehicle_fuel_card`
  (`id`,`license_plate`,`card_no`,`tenant_no`,`remark`,`create_by`,`create_dept_id`)
SELECT 'ddda5a4d-0c1e-5c85-b58b-dd39eef2b763','皖H31656','1000113400009181813','shanxia2.0','','system',''
FROM DUAL WHERE NOT EXISTS (
  SELECT 1 FROM `pl_vehicle_fuel_card`
  WHERE `id`='ddda5a4d-0c1e-5c85-b58b-dd39eef2b763' OR (`tenant_no`='shanxia2.0' AND `del_flag`=0
    AND (`license_plate`='皖H31656' OR `card_no`='1000113400009181813'))
);
INSERT INTO `pl_vehicle_fuel_card`
  (`id`,`license_plate`,`card_no`,`tenant_no`,`remark`,`create_by`,`create_dept_id`)
SELECT '760d01df-0ca5-5ea7-974a-ac15004210e8','皖H13532','1000113400009181814','shanxia2.0','','system',''
FROM DUAL WHERE NOT EXISTS (
  SELECT 1 FROM `pl_vehicle_fuel_card`
  WHERE `id`='760d01df-0ca5-5ea7-974a-ac15004210e8' OR (`tenant_no`='shanxia2.0' AND `del_flag`=0
    AND (`license_plate`='皖H13532' OR `card_no`='1000113400009181814'))
);
INSERT INTO `pl_vehicle_fuel_card`
  (`id`,`license_plate`,`card_no`,`tenant_no`,`remark`,`create_by`,`create_dept_id`)
SELECT '19300c64-1079-5610-8a2f-b7eba248a3e0','皖HA3190','1000113400009181874','shanxia2.0','','system',''
FROM DUAL WHERE NOT EXISTS (
  SELECT 1 FROM `pl_vehicle_fuel_card`
  WHERE `id`='19300c64-1079-5610-8a2f-b7eba248a3e0' OR (`tenant_no`='shanxia2.0' AND `del_flag`=0
    AND (`license_plate`='皖HA3190' OR `card_no`='1000113400009181874'))
);
INSERT INTO `pl_vehicle_fuel_card`
  (`id`,`license_plate`,`card_no`,`tenant_no`,`remark`,`create_by`,`create_dept_id`)
SELECT '2d0f7405-7c38-5dd6-9491-9f4381bfd66f','皖LB1283','1000113400009309765','shanxia2.0','旧卡号：1000113400009253456','system',''
FROM DUAL WHERE NOT EXISTS (
  SELECT 1 FROM `pl_vehicle_fuel_card`
  WHERE `id`='2d0f7405-7c38-5dd6-9491-9f4381bfd66f' OR (`tenant_no`='shanxia2.0' AND `del_flag`=0
    AND (`license_plate`='皖LB1283' OR `card_no`='1000113400009309765'))
);

-- 菜单插在审批人设置之后，只写 Web 所属目录。
INSERT INTO `sys_menu`
  (`id`,`menu_name`,`parent_id`,`menu_type`,`path`,`component`,`perms`,
   `is_frame`,`visible`,`status`,`tenant_no`,`order_num`,`create_by`,`create_dept_id`)
SELECT 'PL_VEH_FUEL_CARD','车油卡信息',wf.parent_id,'C','vehicle-fuel-card',
       'pl/vehicle-fuel-card/index','pl:vehicle-fuel-card:list',1,'1','1',
       wf.tenant_no,COALESCE(wf.order_num,4)+1,'system',''
FROM `sys_menu` wf
WHERE wf.id='PL_VEH_RECHARGE_WF' AND wf.tenant_no='shanxia2.0' AND wf.del_flag=0
  AND NOT EXISTS (SELECT 1 FROM `sys_menu` existing WHERE existing.id='PL_VEH_FUEL_CARD');

-- 沿用审批人设置已有角色，补齐新菜单及全部祖先（当前层级最多4层）。
-- 不恢复管理员主动软删除的授权。
INSERT INTO `sys_role_menu`
  (`id`,`role_id`,`menu_id`,`tenant_no`,`create_by`,`create_dept_id`)
SELECT UUID(), eligible.role_id, target.id, eligible.tenant_no, 'system', ''
FROM (
  SELECT DISTINCT rm.role_id,rm.tenant_no FROM `sys_role_menu` rm
  JOIN `sys_role` r ON r.id=rm.role_id AND r.tenant_no=rm.tenant_no AND r.del_flag=0 AND r.status='1'
  WHERE rm.menu_id='PL_VEH_RECHARGE_WF' AND rm.tenant_no='shanxia2.0' AND rm.del_flag=0
) eligible
JOIN `sys_menu` child ON child.id='PL_VEH_FUEL_CARD' AND child.tenant_no=eligible.tenant_no AND child.del_flag=0
LEFT JOIN `sys_menu` p1 ON p1.id=child.parent_id AND p1.tenant_no=eligible.tenant_no AND p1.del_flag=0
LEFT JOIN `sys_menu` p2 ON p2.id=p1.parent_id AND p2.tenant_no=eligible.tenant_no AND p2.del_flag=0
LEFT JOIN `sys_menu` p3 ON p3.id=p2.parent_id AND p3.tenant_no=eligible.tenant_no AND p3.del_flag=0
JOIN `sys_menu` target ON target.id IN (child.id,p1.id,p2.id,p3.id)
  AND target.tenant_no=eligible.tenant_no AND target.del_flag=0
WHERE NOT EXISTS (
  SELECT 1 FROM `sys_role_menu` existing WHERE existing.role_id=eligible.role_id
    AND existing.menu_id=target.id AND existing.tenant_no=eligible.tenant_no
);
COMMIT;