54 lines
3.7 KiB
SQL
54 lines
3.7 KiB
SQL
DROP TABLE IF EXISTS `smart_cabinet`;
|
||
|
||
CREATE TABLE `smart_cabinet` (
|
||
`cabinet_id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '柜机唯一ID',
|
||
`cabinet_name` VARCHAR(100) NOT NULL COMMENT '柜机名称',
|
||
`cabinet_type` TINYINT NOT NULL DEFAULT 0 COMMENT '柜机类型（0主柜 1副柜）',
|
||
`template_no` VARCHAR(50) NOT NULL COMMENT '柜机模版编号',
|
||
`lock_control_no` INT NOT NULL COMMENT '锁控板序号',
|
||
`location` INT NOT NULL COMMENT '柜机位置',
|
||
`creator_id` BIGINT NOT NULL DEFAULT 0 COMMENT '创建者ID',
|
||
`create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`updater_id` BIGINT NOT NULL DEFAULT 0 COMMENT '更新者ID',
|
||
`update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`deleted` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '删除标志（0存在 1删除）',
|
||
PRIMARY KEY (`cabinet_id`),
|
||
KEY `idx_template_no` (`template_no`),
|
||
KEY `idx_location` (`location`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='智能柜信息表';
|
||
|
||
DROP TABLE IF EXISTS `cabinet_cell`;
|
||
|
||
CREATE TABLE `cabinet_cell` (
|
||
`cell_id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '格口唯一ID',
|
||
`cabinet_id` BIGINT NOT NULL COMMENT '关联柜机ID',
|
||
`goods_id` BIGINT DEFAULT NULL COMMENT '关联商品ID',
|
||
`cell_no` INT NOT NULL COMMENT '格口号',
|
||
`pin_no` INT NOT NULL COMMENT '针脚序号',
|
||
`stock` INT NOT NULL DEFAULT 0 COMMENT '库存数量',
|
||
`cell_type` TINYINT NOT NULL DEFAULT 1 COMMENT '格口类型（1小格 2中格 3大格 4超大格）',
|
||
`usage_status` TINYINT NOT NULL DEFAULT 1 COMMENT '使用状态（1空闲 2已占用）',
|
||
`available_status` TINYINT NOT NULL DEFAULT 1 COMMENT '可用状态（1正常 2故障）',
|
||
`creator_id` BIGINT NOT NULL DEFAULT 0 COMMENT '创建者ID',
|
||
`create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`updater_id` BIGINT NOT NULL DEFAULT 0 COMMENT '更新者ID',
|
||
`update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`deleted` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '删除标志（0存在 1删除）',
|
||
PRIMARY KEY (`cell_id`),
|
||
KEY `idx_cabinet` (`cabinet_id`),
|
||
KEY `idx_usage_status` (`usage_status`),
|
||
KEY `idx_available_status` (`available_status`),
|
||
KEY `idx_goods` (`goods_id`),
|
||
CONSTRAINT `fk_cell_cabinet` FOREIGN KEY (`cabinet_id`) REFERENCES `smart_cabinet` (`cabinet_id`),
|
||
CONSTRAINT `fk_cell_goods` FOREIGN KEY (`goods_id`) REFERENCES `shop_goods` (`goods_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='柜机格口信息表';
|
||
|
||
INSERT INTO sys_menu
|
||
(menu_name, menu_type, router_name, parent_id, `path`, is_button, permission, meta_info, status, remark, creator_id, create_time, updater_id, update_time, deleted)
|
||
VALUES('柜机管理', 2, '', 0, '/cabinet', 0, '', '{"title":"柜机管理","icon":"fa-solid:server","showLink":true,"showParent":true,"rank":2}', 1, '', 1, '2025-03-17 17:55:52', NULL, NULL, 0);
|
||
INSERT INTO sys_menu
|
||
(menu_name, menu_type, router_name, parent_id, `path`, is_button, permission, meta_info, status, remark, creator_id, create_time, updater_id, update_time, deleted)
|
||
VALUES('柜机列表', 1, 'SmartCabinet', 69, '/cabinet/smart-cabinet/index', 0, '', '{"title":"柜机列表","showLink":true,"showParent":true,"rank":1}', 1, '', 1, '2025-03-17 17:58:10', 1, '2025-03-17 17:58:21', 0);
|
||
INSERT INTO sys_menu
|
||
(menu_name, menu_type, router_name, parent_id, `path`, is_button, permission, meta_info, status, remark, creator_id, create_time, updater_id, update_time, deleted)
|
||
VALUES('格口管理', 1, 'CabinetCell', 69, '/cabinet/cabinet-cell/index', 0, '', '{"title":"格口管理","showParent":true,"rank":2}', 1, '', 1, '2025-03-18 17:23:47', NULL, NULL, 0); |