wuzilongandCursor
d22b06dbe7
含易制毒计划安排后进入已安排待确认，确认通过后再占合同并生成链接/地磅；新增购买证/运输证主数据、产品行关联、余量校验与 OCR 配置，并停止跟踪 api_key/app.pid。 Co-authored-by: Cursor <cursoragent@cursor.com>
199 lines
8.6 KiB
SQL
199 lines
8.6 KiB
SQL
-- 易制毒购买证 / 运输证 / 计划产品行关联（P2）
|
||
-- 执行前请确认库为 MySQL 5.7 utf8mb4
|
||
|
||
CREATE TABLE IF NOT EXISTS `pl_purchase_cert` (
|
||
`id` varchar(64) NOT NULL COMMENT '主键UUID',
|
||
`cert_no` varchar(64) NOT NULL COMMENT '购买证编号',
|
||
`issue_date` date DEFAULT NULL COMMENT '核发日期',
|
||
`buyer_name` varchar(200) DEFAULT NULL COMMENT '购买单位或个人',
|
||
`buyer_legal` varchar(100) DEFAULT NULL COMMENT '购买方法人',
|
||
`buyer_tel` varchar(50) DEFAULT NULL COMMENT '购买方电话',
|
||
`buyer_addr` varchar(500) DEFAULT NULL COMMENT '购买方住所',
|
||
`seller_name` varchar(200) DEFAULT NULL COMMENT '销售单位',
|
||
`seller_legal` varchar(100) DEFAULT NULL COMMENT '销售方法人',
|
||
`seller_tel` varchar(50) DEFAULT NULL COMMENT '销售方电话',
|
||
`seller_addr` varchar(500) DEFAULT NULL COMMENT '销售方住所',
|
||
`product_name` varchar(200) NOT NULL COMMENT '购买物品/品名',
|
||
`usage_desc` varchar(200) DEFAULT NULL COMMENT '用途',
|
||
`total_qty` decimal(18,3) NOT NULL DEFAULT 0.000 COMMENT '购买证数量',
|
||
`unit` varchar(20) NOT NULL DEFAULT '吨' COMMENT '单位',
|
||
`valid_times` varchar(50) DEFAULT NULL COMMENT '有效次数（如多次有效）',
|
||
`valid_from` date DEFAULT NULL COMMENT '有效开始',
|
||
`valid_to` date DEFAULT NULL COMMENT '有效截止',
|
||
`police_org` varchar(200) DEFAULT NULL COMMENT '公安机关',
|
||
`handler_name` varchar(100) DEFAULT NULL COMMENT '经办人',
|
||
`police_tel` varchar(50) DEFAULT NULL COMMENT '机关电话',
|
||
`attachment_urls` varchar(2000) DEFAULT NULL COMMENT '附件URL，逗号分隔',
|
||
`ocr_result_json` mediumtext NULL COMMENT 'DeepSeek识别结果JSON',
|
||
`tenant_no` varchar(64) NOT NULL DEFAULT 'shanxia2.0',
|
||
`order_num` int(11) DEFAULT NULL,
|
||
`remark` varchar(512) DEFAULT NULL,
|
||
`create_by` varchar(64) DEFAULT NULL,
|
||
`create_dept_id` varchar(64) NOT NULL DEFAULT '',
|
||
`create_time` datetime NOT NULL,
|
||
`update_by` varchar(64) DEFAULT NULL,
|
||
`update_time` datetime NOT NULL,
|
||
`del_flag` smallint(6) NOT NULL DEFAULT 0,
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_ppc_tenant_no` (`tenant_no`,`cert_no`),
|
||
KEY `idx_ppc_product` (`tenant_no`,`product_name`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='易制毒购买备案证明';
|
||
|
||
CREATE TABLE IF NOT EXISTS `pl_transport_cert` (
|
||
`id` varchar(64) NOT NULL,
|
||
`purchase_cert_id` varchar(64) NOT NULL COMMENT '主挂购买证ID',
|
||
`cert_no` varchar(64) NOT NULL COMMENT '运输证编号',
|
||
`issue_date` date DEFAULT NULL,
|
||
`owner_name` varchar(200) DEFAULT NULL COMMENT '货主',
|
||
`owner_legal` varchar(100) DEFAULT NULL,
|
||
`owner_tel` varchar(50) DEFAULT NULL,
|
||
`owner_addr` varchar(500) DEFAULT NULL,
|
||
`product_name` varchar(200) DEFAULT NULL COMMENT '品名',
|
||
`pack_spec` varchar(100) DEFAULT NULL COMMENT '包装',
|
||
`permit_cert_nos` varchar(500) DEFAULT NULL COMMENT '购买许可/备案/出口许可证号，逗号分隔',
|
||
`carrier_name` varchar(200) DEFAULT NULL COMMENT '运输单位',
|
||
`carrier_contact` varchar(200) DEFAULT NULL COMMENT '住所/电话',
|
||
`transport_mode` varchar(50) DEFAULT NULL COMMENT '运输方式',
|
||
`plate_nos` varchar(500) DEFAULT NULL COMMENT '车号(次)，逗号或顿号分隔',
|
||
`route_desc` varchar(1000) DEFAULT NULL COMMENT '运输路线',
|
||
`consignee_name` varchar(200) DEFAULT NULL COMMENT '收货单位',
|
||
`total_qty` decimal(18,3) NOT NULL DEFAULT 0.000 COMMENT '运输证数量',
|
||
`unit` varchar(20) NOT NULL DEFAULT '吨',
|
||
`valid_times` varchar(50) DEFAULT NULL,
|
||
`valid_from` date DEFAULT NULL,
|
||
`valid_to` date DEFAULT NULL,
|
||
`applicant_unit` varchar(200) DEFAULT NULL,
|
||
`police_org` varchar(200) DEFAULT NULL,
|
||
`handler_name` varchar(100) DEFAULT NULL,
|
||
`police_tel` varchar(50) DEFAULT NULL COMMENT '公安机关联系电话',
|
||
`attachment_urls` varchar(2000) DEFAULT NULL,
|
||
`ocr_result_json` mediumtext NULL COMMENT 'DeepSeek识别结果JSON',
|
||
`tenant_no` varchar(64) NOT NULL DEFAULT 'shanxia2.0',
|
||
`order_num` int(11) DEFAULT NULL,
|
||
`remark` varchar(512) DEFAULT NULL,
|
||
`create_by` varchar(64) DEFAULT NULL,
|
||
`create_dept_id` varchar(64) NOT NULL DEFAULT '',
|
||
`create_time` datetime NOT NULL,
|
||
`update_by` varchar(64) DEFAULT NULL,
|
||
`update_time` datetime NOT NULL,
|
||
`del_flag` smallint(6) NOT NULL DEFAULT 0,
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_ptc_purchase` (`tenant_no`,`purchase_cert_id`),
|
||
KEY `idx_ptc_cert_no` (`tenant_no`,`cert_no`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='易制毒运输备案证明';
|
||
|
||
CREATE TABLE IF NOT EXISTS `pl_plan_product_cert_link` (
|
||
`id` varchar(64) NOT NULL,
|
||
`plan_id` varchar(64) NOT NULL,
|
||
`plan_product_id` varchar(64) NOT NULL,
|
||
`purchase_cert_id` varchar(64) NOT NULL,
|
||
`transport_cert_id` varchar(64) NOT NULL,
|
||
`link_status` varchar(20) NOT NULL DEFAULT 'linked' COMMENT 'linked-已关联 occupied-已占用(齐备) voided-已失效',
|
||
`occupy_plan_qty` decimal(18,3) DEFAULT NULL COMMENT '齐备时按计划量占用',
|
||
`occupy_actual_qty` decimal(18,3) DEFAULT NULL COMMENT '归档实际量(P4填写)',
|
||
`tenant_no` varchar(64) NOT NULL DEFAULT 'shanxia2.0',
|
||
`order_num` int(11) DEFAULT NULL,
|
||
`remark` varchar(512) DEFAULT NULL,
|
||
`create_by` varchar(64) DEFAULT NULL,
|
||
`create_dept_id` varchar(64) NOT NULL DEFAULT '',
|
||
`create_time` datetime NOT NULL,
|
||
`update_by` varchar(64) DEFAULT NULL,
|
||
`update_time` datetime NOT NULL,
|
||
`del_flag` smallint(6) NOT NULL DEFAULT 0,
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_ppcl_plan` (`tenant_no`,`plan_id`),
|
||
KEY `idx_ppcl_product` (`tenant_no`,`plan_product_id`),
|
||
KEY `idx_ppcl_purchase` (`tenant_no`,`purchase_cert_id`,`link_status`),
|
||
KEY `idx_ppcl_transport` (`tenant_no`,`transport_cert_id`,`link_status`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='运输计划易制毒产品行证书关联';
|
||
|
||
-- ---------------------------------------------------------------------------
|
||
-- 菜单：与「危化品证上传」同级挂在 PL_MENU_ROOT 下；按钮权限 edit / confirm
|
||
-- 角色授权：复制已有 pl:plan-cert:upload 的角色
|
||
-- ---------------------------------------------------------------------------
|
||
|
||
-- 仅首次插入时后移后续菜单（幂等：已存在 PL_PURCHASE_CERT 则跳过）
|
||
UPDATE `sys_menu` m
|
||
SET m.`order_num` = m.`order_num` + 1,
|
||
m.`update_by` = 'system',
|
||
m.`update_time` = NOW()
|
||
WHERE m.`parent_id` = 'PL_MENU_ROOT'
|
||
AND m.`del_flag` = 0
|
||
AND m.`order_num` >= 4
|
||
AND m.`id` <> 'PL_PURCHASE_CERT'
|
||
AND NOT EXISTS (
|
||
SELECT 1 FROM (SELECT `id` FROM `sys_menu` WHERE `id` = 'PL_PURCHASE_CERT' AND `del_flag` = 0) t
|
||
);
|
||
|
||
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`
|
||
) VALUES
|
||
(
|
||
'PL_PURCHASE_CERT', '购买证管理', 'PL_MENU_ROOT', 'C', 'purchase-cert',
|
||
'pl/purchase-cert/index', 'pl:purchase-cert:list',
|
||
1, '1', '1', 'shanxia2.0', 4, '易制毒购买证/运输证管理与待确认计划关联',
|
||
'system', '', NOW(), 'system', NOW(), 0
|
||
),
|
||
(
|
||
'PL_PURCHASE_CERT_E', '录入编辑', 'PL_PURCHASE_CERT', 'F', '', '',
|
||
'pl:purchase-cert:edit',
|
||
1, '1', '1', 'shanxia2.0', 1, NULL,
|
||
'system', '', NOW(), 'system', NOW(), 0
|
||
),
|
||
(
|
||
'PL_PURCHASE_CERT_C', '确认生效', 'PL_PURCHASE_CERT', 'F', '', '',
|
||
'pl:purchase-cert:confirm',
|
||
1, '1', '1', 'shanxia2.0', 2, NULL,
|
||
'system', '', NOW(), 'system', NOW(), 0
|
||
)
|
||
ON DUPLICATE KEY UPDATE
|
||
`menu_name` = VALUES(`menu_name`),
|
||
`parent_id` = VALUES(`parent_id`),
|
||
`path` = VALUES(`path`),
|
||
`component` = VALUES(`component`),
|
||
`perms` = VALUES(`perms`),
|
||
`order_num` = VALUES(`order_num`),
|
||
`remark` = VALUES(`remark`),
|
||
`update_by` = 'system',
|
||
`update_time` = NOW(),
|
||
`del_flag` = 0;
|
||
|
||
-- 给已有「危化品证上传」按钮权限的角色同步授权（含祖先 PL_PURCHASE_CERT）
|
||
-- id 用角色+菜单确定性拼接，避免 UUID() 主键碰撞
|
||
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
|
||
CONCAT('RM_PC_', LEFT(REPLACE(rm.role_id, '-', ''), 20), '_', m.suffix),
|
||
rm.role_id,
|
||
m.id,
|
||
'shanxia2.0',
|
||
0,
|
||
NULL,
|
||
'system',
|
||
'',
|
||
NOW(),
|
||
'system',
|
||
NOW(),
|
||
0
|
||
FROM (
|
||
SELECT DISTINCT `role_id`
|
||
FROM `sys_role_menu`
|
||
WHERE `del_flag` = 0
|
||
AND `menu_id` IN ('PL_PLAN_CERT', 'PL_PLAN_CERT_U', 'PL_PLAN_CERT_R')
|
||
) rm
|
||
CROSS JOIN (
|
||
SELECT 'PL_PURCHASE_CERT' AS id, 'L' AS suffix
|
||
UNION ALL SELECT 'PL_PURCHASE_CERT_E', 'E'
|
||
UNION ALL SELECT 'PL_PURCHASE_CERT_C', 'C'
|
||
) m
|
||
WHERE NOT EXISTS (
|
||
SELECT 1 FROM `sys_role_menu` x
|
||
WHERE x.`role_id` = rm.`role_id`
|
||
AND x.`menu_id` = m.id
|
||
AND x.`del_flag` = 0
|
||
);
|