shanxia2.0/docs/sql/migrations/2026-10-26-manual-dispatch-calendar.sql

-- 手工派车日历；独立记录，不生成或修改运输计划。MySQL 5.7 / utf8mb4。
-- 同车同日允许多条，一条记录计一车次；不设置车辆+日期唯一约束。
SET NAMES utf8mb4;
CREATE TABLE IF NOT EXISTS `pl_dispatch_calendar` (
  `id` varchar(64) NOT NULL COMMENT '主键UUID',
  `dispatch_date` date NOT NULL COMMENT '派车日期',
  `vehicle_id` varchar(64) NOT NULL COMMENT '车辆ID',
  `license_plate` varchar(20) NOT NULL COMMENT '车牌快照',
  `product_id` varchar(64) NOT NULL COMMENT '产品ID',
  `product_name` varchar(200) NOT NULL COMMENT '产品名称快照',
  `origin` varchar(255) NOT NULL COMMENT '始发地',
  `destination` varchar(255) NOT NULL COMMENT '目的地',
  `quantity` varchar(16) DEFAULT NULL COMMENT '数量文本（吨），最多两位小数，不参与汇总',
  `calendar_kind` varchar(16) NOT NULL DEFAULT 'acid' COMMENT '日历类型：acid酸派车 alkali碱派车 other其他',
  `tenant_no` varchar(64) NOT NULL COMMENT '租户编号',
  `order_num` int DEFAULT NULL COMMENT '排序号',
  `remark` varchar(500) 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 '删除标记',
  PRIMARY KEY (`id`),
  KEY `idx_dispatch_calendar_month` (`tenant_no`,`del_flag`,`dispatch_date`),
  KEY `idx_dispatch_calendar_vehicle` (`tenant_no`,`vehicle_id`,`dispatch_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='手工派车日历';

-- 维护与查看分权；仅向已有日历入口且具有运输计划维护能力的角色授予维护按钮。
-- Web 现有 pl:plan:list 是运输计划编辑入口，没有 add/edit 粒度；
-- 小程序使用明确的 mini:pl:plan:manage。单独日历只读角色不会获得写权限。
-- F 类型为操作权限，不生成路由；visible=1 保证现有权限解析器能读取此权限。
-- 可重复执行，不恢复软删除菜单/授权；不改变已有日历入口及运输计划菜单。
START TRANSACTION;

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_time,del_flag
)
SELECT UUID(),'维护派车日历',leaf.id,'F',NULL,NULL,'pl:dispatch-calendar:manage',
 1,'1','1',leaf.tenant_no,1,'独立手工派车新增修改删除权限','system','',NOW(),NOW(),0
FROM sys_menu leaf
JOIN sys_menu folder ON folder.id=leaf.parent_id AND folder.tenant_no=leaf.tenant_no AND folder.del_flag=0 AND folder.status='1' AND folder.visible='1'
JOIN sys_menu parent ON parent.id=folder.parent_id AND parent.tenant_no=leaf.tenant_no AND parent.del_flag=0 AND parent.status='1' AND parent.visible='1'
JOIN sys_menu root ON root.id=parent.parent_id AND root.tenant_no=leaf.tenant_no AND root.del_flag=0 AND root.status='1' AND root.visible='1'
WHERE leaf.perms='pl:dispatch-calendar:list' AND leaf.del_flag=0 AND leaf.status='1' AND leaf.visible='1'
 AND root.parent_id='0' AND root.path='/web' AND parent.path='/pl'
 AND NOT EXISTS (SELECT 1 FROM sys_menu existing
   WHERE existing.tenant_no=leaf.tenant_no AND existing.perms='pl:dispatch-calendar:manage');

INSERT INTO sys_role_menu (
 id,role_id,menu_id,tenant_no,order_num,remark,create_by,create_dept_id,create_time,update_time,del_flag
)
SELECT UUID(),r.id,target.id,r.tenant_no,0,'日历入口与运输计划维护角色交集授权','system','',NOW(),NOW(),0
FROM sys_role r
JOIN sys_menu leaf ON leaf.perms='pl:dispatch-calendar:list' AND leaf.tenant_no=r.tenant_no AND leaf.del_flag=0 AND leaf.status='1' AND leaf.visible='1'
JOIN sys_menu folder ON folder.id=leaf.parent_id AND folder.tenant_no=leaf.tenant_no AND folder.del_flag=0 AND folder.status='1' AND folder.visible='1'
JOIN sys_menu parent ON parent.id=folder.parent_id AND parent.tenant_no=leaf.tenant_no AND parent.del_flag=0 AND parent.status='1' AND parent.visible='1'
JOIN sys_menu root ON root.id=parent.parent_id AND root.tenant_no=leaf.tenant_no AND root.del_flag=0 AND root.status='1' AND root.visible='1'
JOIN sys_menu target ON target.parent_id=leaf.id AND target.tenant_no=r.tenant_no
 AND target.perms='pl:dispatch-calendar:manage' AND target.del_flag=0 AND target.status='1' AND target.visible='1'
WHERE r.del_flag=0 AND r.status='1' AND root.parent_id='0' AND root.path='/web' AND parent.path='/pl'
 AND EXISTS (SELECT 1 FROM sys_role_menu calendar_access
   WHERE calendar_access.role_id=r.id AND calendar_access.menu_id=leaf.id
    AND calendar_access.tenant_no=r.tenant_no AND calendar_access.del_flag=0)
 AND EXISTS (
   SELECT 1 FROM sys_role_menu source_access JOIN sys_menu source ON source.id=source_access.menu_id
   WHERE source_access.role_id=r.id AND source_access.tenant_no=r.tenant_no
    AND source.tenant_no=r.tenant_no AND source_access.del_flag=0 AND source.del_flag=0 AND source.status='1' AND source.visible='1'
    AND source.perms IN ('pl:plan:list','mini:pl:plan:manage')
 )
 AND NOT EXISTS (SELECT 1 FROM sys_role_menu existing
   WHERE existing.role_id=r.id AND existing.menu_id=target.id AND existing.tenant_no=r.tenant_no);

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_time,del_flag
)
SELECT UUID(),'维护派车日历',leaf.id,'F',NULL,NULL,'mini:pl:dispatch-calendar:manage',
 1,'1','1',leaf.tenant_no,1,'独立手工派车新增修改删除权限','system','',NOW(),NOW(),0
FROM sys_menu leaf
JOIN sys_menu parent ON parent.id=leaf.parent_id AND parent.tenant_no=leaf.tenant_no AND parent.del_flag=0 AND parent.status='1' AND parent.visible='1'
JOIN sys_menu root ON root.id=parent.parent_id AND root.tenant_no=leaf.tenant_no AND root.del_flag=0 AND root.status='1' AND root.visible='1'
WHERE leaf.perms='mini:pl:dispatch-calendar:list' AND leaf.del_flag=0 AND leaf.status='1' AND leaf.visible='1'
 AND root.parent_id='0' AND root.path='/mini' AND parent.path='/mini/pl'
 AND NOT EXISTS (SELECT 1 FROM sys_menu existing
   WHERE existing.tenant_no=leaf.tenant_no AND existing.perms='mini:pl:dispatch-calendar:manage');

INSERT INTO sys_role_menu (
 id,role_id,menu_id,tenant_no,order_num,remark,create_by,create_dept_id,create_time,update_time,del_flag
)
SELECT UUID(),r.id,target.id,r.tenant_no,0,'日历入口与运输计划维护角色交集授权','system','',NOW(),NOW(),0
FROM sys_role r
JOIN sys_menu leaf ON leaf.perms='mini:pl:dispatch-calendar:list' AND leaf.tenant_no=r.tenant_no AND leaf.del_flag=0 AND leaf.status='1' AND leaf.visible='1'
JOIN sys_menu parent ON parent.id=leaf.parent_id AND parent.tenant_no=leaf.tenant_no AND parent.del_flag=0 AND parent.status='1' AND parent.visible='1'
JOIN sys_menu root ON root.id=parent.parent_id AND root.tenant_no=leaf.tenant_no AND root.del_flag=0 AND root.status='1' AND root.visible='1'
JOIN sys_menu target ON target.parent_id=leaf.id AND target.tenant_no=r.tenant_no
 AND target.perms='mini:pl:dispatch-calendar:manage' AND target.del_flag=0 AND target.status='1' AND target.visible='1'
WHERE r.del_flag=0 AND r.status='1' AND root.parent_id='0' AND root.path='/mini' AND parent.path='/mini/pl'
 AND EXISTS (SELECT 1 FROM sys_role_menu calendar_access
   WHERE calendar_access.role_id=r.id AND calendar_access.menu_id=leaf.id
    AND calendar_access.tenant_no=r.tenant_no AND calendar_access.del_flag=0)
 AND EXISTS (
   SELECT 1 FROM sys_role_menu source_access JOIN sys_menu source ON source.id=source_access.menu_id
   WHERE source_access.role_id=r.id AND source_access.tenant_no=r.tenant_no
    AND source.tenant_no=r.tenant_no AND source_access.del_flag=0 AND source.del_flag=0 AND source.status='1' AND source.visible='1'
    AND source.perms IN ('pl:plan:list','mini:pl:plan:manage')
 )
 AND NOT EXISTS (SELECT 1 FROM sys_role_menu existing
   WHERE existing.role_id=r.id AND existing.menu_id=target.id AND existing.tenant_no=r.tenant_no);

COMMIT;