67 lines
5.2 KiB
MySQL
67 lines
5.2 KiB
MySQL
|
DROP TABLE IF EXISTS `mes_collection_data`;
|
|||
|
|
CREATE TABLE IF NOT EXISTS `mes_collection_data` (
|
|||
|
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '收集数据ID',
|
|||
|
|
`device_code` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '设备编号',
|
|||
|
|
`collect_time` datetime NOT NULL COMMENT '收集时间',
|
|||
|
|
`weight` decimal(24, 6) NOT NULL COMMENT '重量',
|
|||
|
|
`product_id` bigint NOT NULL COMMENT '物品ID',
|
|||
|
|
`product_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '物品名称',
|
|||
|
|
`product_code` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '物品编码',
|
|||
|
|
`tare_weight` decimal(24, 6) NULL DEFAULT 0.000000 COMMENT '去皮重量',
|
|||
|
|
`net_weight` decimal(24, 6) NULL DEFAULT NULL COMMENT '净重（重量-去皮）',
|
|||
|
|
`cumulative_weight` decimal(24, 6) NOT NULL DEFAULT 0.000000 COMMENT '累计重量',
|
|||
|
|
`cumulative_count` bigint NOT NULL DEFAULT 0 COMMENT '累计次数',
|
|||
|
|
`remark` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '备注',
|
|||
|
|
`order_no` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '订单号',
|
|||
|
|
`create_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT '' COMMENT '创建者',
|
|||
|
|
`create_time` datetime NULL DEFAULT NULL COMMENT '创建时间',
|
|||
|
|
`update_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT '' COMMENT '更新者',
|
|||
|
|
`update_time` datetime NULL DEFAULT NULL COMMENT '更新时间',
|
|||
|
|
PRIMARY KEY (`id`) USING BTREE,
|
|||
|
|
INDEX `idx_device_code`(`device_code`) USING BTREE COMMENT '设备编号索引',
|
|||
|
|
INDEX `idx_product_id`(`product_id`) USING BTREE COMMENT '物品ID索引',
|
|||
|
|
INDEX `idx_collect_time`(`collect_time`) USING BTREE COMMENT '收集时间索引'
|
|||
|
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '收集数据表' ROW_FORMAT = DYNAMIC;
|
|||
|
|
|
|||
|
|
-- 2. 插入一级菜单：数据采集（如果不存在）
|
|||
|
|
-- 注意：请根据实际情况调整 menu_id，确保不与现有菜单ID冲突
|
|||
|
|
-- 查询当前最大菜单ID: SELECT MAX(menu_id) FROM sys_menu;
|
|||
|
|
|
|||
|
|
-- 假设一级菜单ID为 3000（请根据实际情况调整）
|
|||
|
|
INSERT INTO `sys_menu` ( `menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`)
|
|||
|
|
VALUES ( '磅秤采集', 0, 10, 'collection', NULL, NULL, 1, 0, 'M', '0', '0', '', 'monitor', 'admin', NOW(), '', NULL, '数据采集目录');
|
|||
|
|
|
|||
|
|
|
|||
|
|
-- 获取刚插入的菜单ID
|
|||
|
|
SET @menu_id = LAST_INSERT_ID();
|
|||
|
|
|
|||
|
|
-- 3. 插入二级菜单：收集数据
|
|||
|
|
INSERT INTO `sys_menu` (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`)
|
|||
|
|
VALUES ( '磅秤采集', @menu_id, 1, 'data', 'mes/collection/data/index', NULL, 1, 0, 'C', '0', '0', 'collection:data:list', 'list', 'admin', NOW(), '', NULL, '收集数据菜单');
|
|||
|
|
|
|||
|
|
-- 4. 插入按钮权限
|
|||
|
|
-- 收集数据查询
|
|||
|
|
|
|||
|
|
-- 获取刚插入的菜单ID
|
|||
|
|
SET @menu_id = LAST_INSERT_ID();
|
|||
|
|
|
|||
|
|
|
|||
|
|
INSERT INTO `sys_menu` (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`)
|
|||
|
|
VALUES ( '采集数据查询', @menu_id, 1, '#', '', NULL, 1, 0, 'F', '0', '0', 'collection:data:query', '#', 'admin', NOW(), '', NULL, '');
|
|||
|
|
|
|||
|
|
-- 收集数据新增
|
|||
|
|
INSERT INTO `sys_menu` (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`)
|
|||
|
|
VALUES ( '采集数据新增', @menu_id, 2, '#', '', NULL, 1, 0, 'F', '0', '0', 'collection:data:add', '#', 'admin', NOW(), '', NULL, '');
|
|||
|
|
|
|||
|
|
-- 收集数据修改
|
|||
|
|
INSERT INTO `sys_menu` (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`)
|
|||
|
|
VALUES ('采集数据修改', @menu_id, 3, '#', '', NULL, 1, 0, 'F', '0', '0', 'collection:data:edit', '#', 'admin', NOW(), '', NULL, '');
|
|||
|
|
|
|||
|
|
-- 收集数据删除
|
|||
|
|
INSERT INTO `sys_menu` ( `menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`)
|
|||
|
|
VALUES ( '采集数据删除', @menu_id, 4, '#', '', NULL, 1, 0, 'F', '0', '0', 'collection:data:remove', '#', 'admin', NOW(), '', NULL, '');
|
|||
|
|
|
|||
|
|
-- 收集数据导出
|
|||
|
|
INSERT INTO `sys_menu` ( `menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`)
|
|||
|
|
VALUES ('采集数据导出', @menu_id, 5, '#', '', NULL, 1, 0, 'F', '0', '0', 'collection:data:export', '#', 'admin', NOW(), '', NULL, '');
|