xuewuerduo
f468d532b1
包含以下模块： - antdv-next-admin: Vue 3 + TypeScript + Ant Design Vue 管理后台 - 设备/许可证/配件/耗材 CRUD 管理页面 - 基础数据管理 (分类/位置/制造商/型号/供应商) - 业务管理 (故障报修/盘点/资产分配/资产申请/交易记录) - 下拉选项改造 (ID输入框 → 搜索下拉选择) - 资产状态字典化 (接入sys_dict系统) - 界面文案优化 (设备→资产, 在库/在用/维修中/已报废) - 修复 console 警告 (popupClassName, 重复组件注册) - our-itam: Java Spring Boot + magic-api 后端服务 - fantastic-admin: 前端底层框架 (pnpm monorepo) - ciyo-itasset: CIYO 资产模块 - magic-script-skill: Claude Code skill 定义 - .claude: 对话历史记录 Co-Authored-By: Claude Code <noreply@anthropic.com>
256 lines
15 KiB
SQL
256 lines
15 KiB
SQL
-- ============================================================
|
||
-- UUID v7 迁移脚本
|
||
-- 将所有 bigint AUTO_INCREMENT 主键和外键转为 char(36)
|
||
-- ============================================================
|
||
|
||
SET NAMES utf8mb4;
|
||
SET FOREIGN_KEY_CHECKS = 0;
|
||
|
||
-- ============================================================
|
||
-- UUID v7 种子数据（预生成，时间有序）
|
||
-- ============================================================
|
||
SET @uid_admin = '019e3035-d8be-72ea-9599-7c8d9a68c4ca';
|
||
SET @did_companies = '019e3035-d8bf-706e-bd16-522c70829c79'; -- 总公司
|
||
SET @did_it = '019e3035-d8bf-706e-bd17-224931a5bb89'; -- IT部
|
||
SET @did_finance = '019e3035-d8bf-706e-bd18-aa5d97f9e80e'; -- 财务部
|
||
SET @rid_admin = '019e3035-d8bf-706e-bd19-dd4dfc2632cf'; -- 超级管理员
|
||
SET @rid_itadmin = '019e3035-d8bf-706e-bd1a-3a74593c97a4'; -- IT管理员
|
||
SET @rid_user = '019e3035-d8c0-7e72-bd27-e8bd3cf6788e'; -- 普通用户
|
||
|
||
SET @cid_1 = '019e3035-d8c0-7e72-bd28-fd7262aa0431'; -- IT设备
|
||
SET @cid_2 = '019e3035-d8c0-7e72-bd29-ad5352d98cff'; -- 服务器
|
||
SET @cid_3 = '019e3035-d8c0-7e72-bd2a-9e07f1f23da5'; -- PC/笔记本
|
||
SET @cid_4 = '019e3035-d8c0-7e72-bd2b-555dd8501aba'; -- 网络设备
|
||
SET @cid_5 = '019e3035-d8c0-7e72-bd2c-42e63da87ea5'; -- 路由器
|
||
SET @cid_6 = '019e3035-d8c1-76cf-9c75-050fad3450e6'; -- 交换机
|
||
SET @cid_7 = '019e3035-d8c1-76cf-9c76-11b87304fe61'; -- IT许可
|
||
SET @cid_8 = '019e3035-d8c1-76cf-9c77-8a12cb4ec0b9'; -- 软件许可
|
||
SET @cid_9 = '019e3035-d8c1-76cf-9c78-77d0601448b5'; -- SaaS许可
|
||
SET @cid_10 = '019e3035-d8ce-74ee-9816-98ad2f2d6681'; -- IT配件
|
||
SET @cid_11 = '019e3035-d8ce-74ee-9817-64b60562c76c'; -- 电源配件
|
||
SET @cid_12 = '019e3035-d8cf-759c-9fa9-a68c1ec47f34'; -- 网络配件
|
||
SET @cid_13 = '019e3035-d8cf-759c-9faa-723872d1e191'; -- 办公耗材
|
||
SET @cid_14 = '019e3036-e597-7938-be1f-067966358dec'; -- 打印耗材
|
||
SET @cid_15 = '019e3036-e598-7269-a9cc-85204d4a5630'; -- IT服务
|
||
SET @cid_16 = '019e3036-e598-7269-a9cd-4c55c37f6619'; -- 运维服务
|
||
|
||
-- ============================================================
|
||
-- 清除所有现有数据（除系统表外）
|
||
-- ============================================================
|
||
DELETE FROM `sys_user_role`;
|
||
DELETE FROM `sys_role_menu`;
|
||
DELETE FROM `sys_file`;
|
||
DELETE FROM `sys_user`;
|
||
DELETE FROM `sys_role`;
|
||
DELETE FROM `sys_dept`;
|
||
DELETE FROM `sys_menu`;
|
||
DELETE FROM `itam_consumable_transactions`;
|
||
DELETE FROM `itam_allocations`;
|
||
DELETE FROM `itam_stocktake_items`;
|
||
DELETE FROM `itam_stocktakes`;
|
||
DELETE FROM `itam_failures`;
|
||
DELETE FROM `itam_asset_requests`;
|
||
DELETE FROM `itam_device`;
|
||
DELETE FROM `itam_licenses`;
|
||
DELETE FROM `itam_accessories`;
|
||
DELETE FROM `itam_consumables`;
|
||
DELETE FROM `itam_offering`;
|
||
DELETE FROM `itam_depreciations`;
|
||
DELETE FROM `itam_models`;
|
||
DELETE FROM `itam_manufacturers`;
|
||
DELETE FROM `itam_suppliers`;
|
||
DELETE FROM `itam_locations`;
|
||
DELETE FROM `itam_categories`;
|
||
|
||
-- ============================================================
|
||
-- 系统表 ALTER
|
||
-- ============================================================
|
||
ALTER TABLE `sys_dept` MODIFY COLUMN `id` char(36) NOT NULL;
|
||
ALTER TABLE `sys_dept` MODIFY COLUMN `parent_id` char(36) DEFAULT NULL;
|
||
ALTER TABLE `sys_dept` MODIFY COLUMN `create_by` char(36) DEFAULT NULL;
|
||
ALTER TABLE `sys_dept` MODIFY COLUMN `update_by` char(36) DEFAULT NULL;
|
||
|
||
ALTER TABLE `sys_user` MODIFY COLUMN `id` char(36) NOT NULL;
|
||
ALTER TABLE `sys_user` MODIFY COLUMN `dept_id` char(36) DEFAULT NULL;
|
||
ALTER TABLE `sys_user` MODIFY COLUMN `create_by` char(36) DEFAULT NULL;
|
||
ALTER TABLE `sys_user` MODIFY COLUMN `update_by` char(36) DEFAULT NULL;
|
||
|
||
ALTER TABLE `sys_role` MODIFY COLUMN `id` char(36) NOT NULL;
|
||
ALTER TABLE `sys_role` MODIFY COLUMN `create_by` char(36) DEFAULT NULL;
|
||
ALTER TABLE `sys_role` MODIFY COLUMN `update_by` char(36) DEFAULT NULL;
|
||
|
||
ALTER TABLE `sys_menu` MODIFY COLUMN `id` char(36) NOT NULL;
|
||
ALTER TABLE `sys_menu` MODIFY COLUMN `parent_id` char(36) DEFAULT '0';
|
||
ALTER TABLE `sys_menu` MODIFY COLUMN `create_by` char(36) DEFAULT NULL;
|
||
ALTER TABLE `sys_menu` MODIFY COLUMN `update_by` char(36) DEFAULT NULL;
|
||
|
||
ALTER TABLE `sys_user_role` MODIFY COLUMN `user_id` char(36) NOT NULL;
|
||
ALTER TABLE `sys_user_role` MODIFY COLUMN `role_id` char(36) NOT NULL;
|
||
|
||
ALTER TABLE `sys_role_menu` MODIFY COLUMN `role_id` char(36) NOT NULL;
|
||
ALTER TABLE `sys_role_menu` MODIFY COLUMN `menu_id` char(36) NOT NULL;
|
||
|
||
ALTER TABLE `sys_file` MODIFY COLUMN `id` char(36) NOT NULL;
|
||
ALTER TABLE `sys_file` MODIFY COLUMN `biz_id` char(36) DEFAULT NULL;
|
||
ALTER TABLE `sys_file` MODIFY COLUMN `create_by` char(36) DEFAULT NULL;
|
||
|
||
-- ============================================================
|
||
-- 资产核心表 ALTER
|
||
-- ============================================================
|
||
ALTER TABLE `itam_categories` MODIFY COLUMN `id` char(36) NOT NULL;
|
||
ALTER TABLE `itam_categories` MODIFY COLUMN `parent_id` char(36) DEFAULT NULL;
|
||
ALTER TABLE `itam_categories` MODIFY COLUMN `create_by` char(36) DEFAULT NULL;
|
||
ALTER TABLE `itam_categories` MODIFY COLUMN `update_by` char(36) DEFAULT NULL;
|
||
|
||
ALTER TABLE `itam_manufacturers` MODIFY COLUMN `id` char(36) NOT NULL;
|
||
ALTER TABLE `itam_manufacturers` MODIFY COLUMN `create_by` char(36) DEFAULT NULL;
|
||
ALTER TABLE `itam_manufacturers` MODIFY COLUMN `update_by` char(36) DEFAULT NULL;
|
||
|
||
ALTER TABLE `itam_models` MODIFY COLUMN `id` char(36) NOT NULL;
|
||
ALTER TABLE `itam_models` MODIFY COLUMN `manufacturer_id` char(36) DEFAULT NULL;
|
||
ALTER TABLE `itam_models` MODIFY COLUMN `category_id` char(36) DEFAULT NULL;
|
||
ALTER TABLE `itam_models` MODIFY COLUMN `create_by` char(36) DEFAULT NULL;
|
||
ALTER TABLE `itam_models` MODIFY COLUMN `update_by` char(36) DEFAULT NULL;
|
||
|
||
ALTER TABLE `itam_locations` MODIFY COLUMN `id` char(36) NOT NULL;
|
||
ALTER TABLE `itam_locations` MODIFY COLUMN `create_by` char(36) DEFAULT NULL;
|
||
ALTER TABLE `itam_locations` MODIFY COLUMN `update_by` char(36) DEFAULT NULL;
|
||
|
||
ALTER TABLE `itam_suppliers` MODIFY COLUMN `id` char(36) NOT NULL;
|
||
ALTER TABLE `itam_suppliers` MODIFY COLUMN `create_by` char(36) DEFAULT NULL;
|
||
ALTER TABLE `itam_suppliers` MODIFY COLUMN `update_by` char(36) DEFAULT NULL;
|
||
|
||
ALTER TABLE `itam_depreciations` MODIFY COLUMN `id` char(36) NOT NULL;
|
||
ALTER TABLE `itam_depreciations` MODIFY COLUMN `create_by` char(36) DEFAULT NULL;
|
||
ALTER TABLE `itam_depreciations` MODIFY COLUMN `update_by` char(36) DEFAULT NULL;
|
||
|
||
-- ============================================================
|
||
-- 资产实体表 ALTER
|
||
-- ============================================================
|
||
ALTER TABLE `itam_device` MODIFY COLUMN `id` char(36) NOT NULL;
|
||
ALTER TABLE `itam_device` MODIFY COLUMN `model_id` char(36) NOT NULL;
|
||
ALTER TABLE `itam_device` MODIFY COLUMN `category_id` char(36) DEFAULT NULL;
|
||
ALTER TABLE `itam_device` MODIFY COLUMN `assigned_to` char(36) DEFAULT NULL;
|
||
ALTER TABLE `itam_device` MODIFY COLUMN `location_id` char(36) DEFAULT NULL;
|
||
ALTER TABLE `itam_device` MODIFY COLUMN `supplier_id` char(36) DEFAULT NULL;
|
||
ALTER TABLE `itam_device` MODIFY COLUMN `depreciation_id` char(36) DEFAULT NULL;
|
||
ALTER TABLE `itam_device` MODIFY COLUMN `create_by` char(36) DEFAULT NULL;
|
||
ALTER TABLE `itam_device` MODIFY COLUMN `update_by` char(36) DEFAULT NULL;
|
||
|
||
ALTER TABLE `itam_licenses` MODIFY COLUMN `id` char(36) NOT NULL;
|
||
ALTER TABLE `itam_licenses` MODIFY COLUMN `manufacturer_id` char(36) DEFAULT NULL;
|
||
ALTER TABLE `itam_licenses` MODIFY COLUMN `category_id` char(36) DEFAULT NULL;
|
||
ALTER TABLE `itam_licenses` MODIFY COLUMN `supplier_id` char(36) DEFAULT NULL;
|
||
ALTER TABLE `itam_licenses` MODIFY COLUMN `create_by` char(36) DEFAULT NULL;
|
||
ALTER TABLE `itam_licenses` MODIFY COLUMN `update_by` char(36) DEFAULT NULL;
|
||
|
||
ALTER TABLE `itam_accessories` MODIFY COLUMN `id` char(36) NOT NULL;
|
||
ALTER TABLE `itam_accessories` MODIFY COLUMN `category_id` char(36) NOT NULL;
|
||
ALTER TABLE `itam_accessories` MODIFY COLUMN `supplier_id` char(36) DEFAULT NULL;
|
||
ALTER TABLE `itam_accessories` MODIFY COLUMN `manufacturer_id` char(36) DEFAULT NULL;
|
||
ALTER TABLE `itam_accessories` MODIFY COLUMN `location_id` char(36) DEFAULT NULL;
|
||
ALTER TABLE `itam_accessories` MODIFY COLUMN `depreciation_id` char(36) DEFAULT NULL;
|
||
ALTER TABLE `itam_accessories` MODIFY COLUMN `create_by` char(36) DEFAULT NULL;
|
||
ALTER TABLE `itam_accessories` MODIFY COLUMN `update_by` char(36) DEFAULT NULL;
|
||
|
||
ALTER TABLE `itam_consumables` MODIFY COLUMN `id` char(36) NOT NULL;
|
||
ALTER TABLE `itam_consumables` MODIFY COLUMN `category_id` char(36) NOT NULL;
|
||
ALTER TABLE `itam_consumables` MODIFY COLUMN `manufacturer_id` char(36) DEFAULT NULL;
|
||
ALTER TABLE `itam_consumables` MODIFY COLUMN `location_id` char(36) DEFAULT NULL;
|
||
ALTER TABLE `itam_consumables` MODIFY COLUMN `create_by` char(36) DEFAULT NULL;
|
||
ALTER TABLE `itam_consumables` MODIFY COLUMN `update_by` char(36) DEFAULT NULL;
|
||
|
||
ALTER TABLE `itam_offering` MODIFY COLUMN `id` char(36) NOT NULL;
|
||
ALTER TABLE `itam_offering` MODIFY COLUMN `supplier_id` char(36) DEFAULT NULL;
|
||
ALTER TABLE `itam_offering` MODIFY COLUMN `create_by` char(36) DEFAULT NULL;
|
||
ALTER TABLE `itam_offering` MODIFY COLUMN `update_by` char(36) DEFAULT NULL;
|
||
|
||
-- ============================================================
|
||
-- 业务表 ALTER
|
||
-- ============================================================
|
||
ALTER TABLE `itam_allocations` MODIFY COLUMN `id` char(36) NOT NULL;
|
||
ALTER TABLE `itam_allocations` MODIFY COLUMN `item_id` char(36) NOT NULL;
|
||
ALTER TABLE `itam_allocations` MODIFY COLUMN `owner_id` char(36) NOT NULL;
|
||
ALTER TABLE `itam_allocations` MODIFY COLUMN `create_by` char(36) DEFAULT NULL;
|
||
ALTER TABLE `itam_allocations` MODIFY COLUMN `update_by` char(36) DEFAULT NULL;
|
||
|
||
ALTER TABLE `itam_failures` MODIFY COLUMN `id` char(36) NOT NULL;
|
||
ALTER TABLE `itam_failures` MODIFY COLUMN `target_id` char(36) NOT NULL;
|
||
ALTER TABLE `itam_failures` MODIFY COLUMN `reported_by` char(36) DEFAULT NULL;
|
||
ALTER TABLE `itam_failures` MODIFY COLUMN `resolved_by` char(36) DEFAULT NULL;
|
||
ALTER TABLE `itam_failures` MODIFY COLUMN `create_by` char(36) DEFAULT NULL;
|
||
ALTER TABLE `itam_failures` MODIFY COLUMN `update_by` char(36) DEFAULT NULL;
|
||
|
||
ALTER TABLE `itam_stocktakes` MODIFY COLUMN `id` char(36) NOT NULL;
|
||
ALTER TABLE `itam_stocktakes` MODIFY COLUMN `location_id` char(36) DEFAULT NULL;
|
||
ALTER TABLE `itam_stocktakes` MODIFY COLUMN `category_id` char(36) DEFAULT NULL;
|
||
ALTER TABLE `itam_stocktakes` MODIFY COLUMN `manager_id` char(36) NOT NULL;
|
||
ALTER TABLE `itam_stocktakes` MODIFY COLUMN `create_by` char(36) DEFAULT NULL;
|
||
ALTER TABLE `itam_stocktakes` MODIFY COLUMN `update_by` char(36) DEFAULT NULL;
|
||
|
||
ALTER TABLE `itam_stocktake_items` MODIFY COLUMN `id` char(36) NOT NULL;
|
||
ALTER TABLE `itam_stocktake_items` MODIFY COLUMN `stocktake_id` char(36) NOT NULL;
|
||
ALTER TABLE `itam_stocktake_items` MODIFY COLUMN `item_id` char(36) NOT NULL;
|
||
ALTER TABLE `itam_stocktake_items` MODIFY COLUMN `checked_by` char(36) DEFAULT NULL;
|
||
ALTER TABLE `itam_stocktake_items` MODIFY COLUMN `create_by` char(36) DEFAULT NULL;
|
||
ALTER TABLE `itam_stocktake_items` MODIFY COLUMN `update_by` char(36) DEFAULT NULL;
|
||
|
||
ALTER TABLE `itam_asset_requests` MODIFY COLUMN `id` char(36) NOT NULL;
|
||
ALTER TABLE `itam_asset_requests` MODIFY COLUMN `user_id` char(36) NOT NULL;
|
||
ALTER TABLE `itam_asset_requests` MODIFY COLUMN `category_id` char(36) DEFAULT NULL;
|
||
ALTER TABLE `itam_asset_requests` MODIFY COLUMN `item_id` char(36) DEFAULT NULL;
|
||
ALTER TABLE `itam_asset_requests` MODIFY COLUMN `approver_id` char(36) DEFAULT NULL;
|
||
ALTER TABLE `itam_asset_requests` MODIFY COLUMN `create_by` char(36) DEFAULT NULL;
|
||
ALTER TABLE `itam_asset_requests` MODIFY COLUMN `update_by` char(36) DEFAULT NULL;
|
||
|
||
ALTER TABLE `itam_consumable_transactions` MODIFY COLUMN `id` char(36) NOT NULL;
|
||
ALTER TABLE `itam_consumable_transactions` MODIFY COLUMN `consumable_id` char(36) NOT NULL;
|
||
ALTER TABLE `itam_consumable_transactions` MODIFY COLUMN `operator_id` char(36) NOT NULL;
|
||
ALTER TABLE `itam_consumable_transactions` MODIFY COLUMN `target_id` char(36) DEFAULT NULL;
|
||
ALTER TABLE `itam_consumable_transactions` MODIFY COLUMN `create_by` char(36) DEFAULT NULL;
|
||
ALTER TABLE `itam_consumable_transactions` MODIFY COLUMN `update_by` char(36) DEFAULT NULL;
|
||
|
||
-- ============================================================
|
||
-- 重新插入种子数据（UUID v7）
|
||
-- ============================================================
|
||
|
||
-- 用户
|
||
INSERT INTO `sys_user` (`id`, `user_name`, `nick_name`, `password`, `status`) VALUES
|
||
(@uid_admin, 'admin', '超级管理员', '$2a$10$N.zmdr9k7uOCQb376NoUnuTJ8iAt6Z5EHsM8lE9lBOsl7iKTVKIUi', '0');
|
||
|
||
-- 部门
|
||
INSERT INTO `sys_dept` (`id`, `dept_name`, `order_num`, `status`) VALUES
|
||
(@did_companies, '总公司', 0, '0'),
|
||
(@did_it, 'IT部', 1, '0'),
|
||
(@did_finance, '财务部', 2, '0');
|
||
|
||
-- 角色
|
||
INSERT INTO `sys_role` (`id`, `role_name`, `role_key`, `role_sort`, `status`) VALUES
|
||
(@rid_admin, '超级管理员', 'admin', 1, '0'),
|
||
(@rid_itadmin, 'IT管理员', 'it_admin', 2, '0'),
|
||
(@rid_user, '普通用户', 'user', 3, '0');
|
||
|
||
-- 用户角色关联
|
||
INSERT INTO `sys_user_role` (`user_id`, `role_id`) VALUES (@uid_admin, @rid_admin);
|
||
|
||
-- 分类
|
||
INSERT INTO `itam_categories` (`id`, `name`, `code`, `parent_id`, `category_type`) VALUES
|
||
(@cid_1, 'IT设备', 'IT_DEVICE', NULL, 'device'),
|
||
(@cid_2, '服务器', 'SERVER', @cid_1, 'device'),
|
||
(@cid_3, 'PC/笔记本', 'PC_LAPTOP', @cid_1, 'device'),
|
||
(@cid_4, '网络设备', 'NETWORK_DEVICE', NULL, 'device'),
|
||
(@cid_5, '路由器', 'ROUTER', @cid_4, 'device'),
|
||
(@cid_6, '交换机', 'SWITCH', @cid_4, 'device'),
|
||
(@cid_7, 'IT许可', 'IT_LICENSE', NULL, 'license'),
|
||
(@cid_8, '软件许可', 'SOFTWARE_LICENSE', @cid_7, 'license'),
|
||
(@cid_9, 'SaaS许可', 'SAAS_LICENSE', @cid_7, 'license'),
|
||
(@cid_10, 'IT配件', 'IT_ACCESSORY', NULL, 'accessory'),
|
||
(@cid_11, '电源配件', 'POWER_ACCESSORY', @cid_10, 'accessory'),
|
||
(@cid_12, '网络配件', 'NETWORK_ACCESSORY',@cid_10, 'accessory'),
|
||
(@cid_13, '办公耗材', 'OFFICE_CONSUMABLE',NULL, 'consumable'),
|
||
(@cid_14, '打印耗材', 'PRINT_CONSUMABLE', NULL, 'consumable'),
|
||
(@cid_15, 'IT服务', 'IT_SERVICE', NULL, 'service'),
|
||
(@cid_16, '运维服务', 'OPS_SERVICE', @cid_15, 'service');
|
||
|
||
SET FOREIGN_KEY_CHECKS = 1;
|