224 lines
39 KiB
SQL
224 lines
39 KiB
SQL
-- WMMS fresh schema for MySQL 8.0. Executed once for an empty Docker volume.
|
||
-- SET NAMES must come first: the mysql client that runs this file may default to
|
||
-- latin1, which would mangle every Chinese literal below into mojibake.
|
||
SET NAMES utf8mb4;
|
||
CREATE DATABASE IF NOT EXISTS wmms CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
||
USE wmms;
|
||
|
||
CREATE TABLE users (id BIGINT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50) NOT NULL UNIQUE, password VARCHAR(255) NOT NULL, description VARCHAR(500) NOT NULL DEFAULT '', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
CREATE TABLE organizations (id BIGINT AUTO_INCREMENT PRIMARY KEY, code VARCHAR(50) NOT NULL UNIQUE, name VARCHAR(200) NOT NULL, status VARCHAR(20) NOT NULL DEFAULT 'active', default_timezone VARCHAR(64) NOT NULL DEFAULT 'Asia/Shanghai', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
CREATE TABLE roles (id BIGINT AUTO_INCREMENT PRIMARY KEY, code VARCHAR(80) NOT NULL UNIQUE, name VARCHAR(100) NOT NULL, enabled BOOLEAN NOT NULL DEFAULT TRUE, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
CREATE TABLE permissions (id BIGINT AUTO_INCREMENT PRIMARY KEY, code VARCHAR(120) NOT NULL UNIQUE, name VARCHAR(150) NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
CREATE TABLE role_permissions (role_id BIGINT NOT NULL, permission_id BIGINT NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY(role_id,permission_id), FOREIGN KEY(role_id) REFERENCES roles(id) ON DELETE CASCADE, FOREIGN KEY(permission_id) REFERENCES permissions(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
CREATE TABLE user_roles (user_id BIGINT NOT NULL, role_id BIGINT NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY(user_id,role_id), FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE, FOREIGN KEY(role_id) REFERENCES roles(id) ON DELETE RESTRICT) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
CREATE TABLE user_organizations (user_id BIGINT NOT NULL, organization_id BIGINT NOT NULL, is_default BOOLEAN NOT NULL DEFAULT FALSE, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY(user_id,organization_id), FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE, FOREIGN KEY(organization_id) REFERENCES organizations(id) ON DELETE RESTRICT) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
CREATE TABLE revoked_tokens (jti VARCHAR(64) PRIMARY KEY, user_id BIGINT NOT NULL, expires_at DATETIME NOT NULL, revoked_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_revoked_tokens_expires_at(expires_at), FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
CREATE TABLE material_types (id BIGINT AUTO_INCREMENT PRIMARY KEY, organization_id BIGINT NOT NULL, code VARCHAR(50) NOT NULL, name VARCHAR(100) NOT NULL, status VARCHAR(20) NOT NULL DEFAULT 'active', UNIQUE KEY uk_material_types_org_code(organization_id,code), FOREIGN KEY(organization_id) REFERENCES organizations(id) ON DELETE RESTRICT) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
CREATE TABLE material_categories (id BIGINT AUTO_INCREMENT PRIMARY KEY, organization_id BIGINT NOT NULL, code VARCHAR(50) NOT NULL, name VARCHAR(100) NOT NULL, description VARCHAR(1000) NOT NULL DEFAULT '', parent_id BIGINT NULL, status VARCHAR(20) NOT NULL DEFAULT 'active', UNIQUE KEY uk_material_categories_org_code(organization_id,code), FOREIGN KEY(organization_id) REFERENCES organizations(id) ON DELETE RESTRICT, FOREIGN KEY(parent_id) REFERENCES material_categories(id) ON DELETE RESTRICT) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
CREATE TABLE units (id BIGINT AUTO_INCREMENT PRIMARY KEY, organization_id BIGINT NOT NULL, code VARCHAR(50) NOT NULL, name VARCHAR(100) NOT NULL, precision_scale TINYINT NOT NULL DEFAULT 0, status VARCHAR(20) NOT NULL DEFAULT 'active', UNIQUE KEY uk_units_org_code(organization_id,code), FOREIGN KEY(organization_id) REFERENCES organizations(id) ON DELETE RESTRICT) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
CREATE TABLE materials (id BIGINT AUTO_INCREMENT PRIMARY KEY, organization_id BIGINT NOT NULL, material_code VARCHAR(50) NOT NULL, name VARCHAR(200) NOT NULL, material_image VARCHAR(500), spec VARCHAR(200), manufacturer_model VARCHAR(200), material_type_id BIGINT, category_id BIGINT NOT NULL, base_unit_id BIGINT, unit VARCHAR(20), status VARCHAR(20) NOT NULL DEFAULT 'active', version INT NOT NULL DEFAULT 1, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uk_materials_org_code(organization_id,material_code), KEY idx_materials_category(category_id), FOREIGN KEY(organization_id) REFERENCES organizations(id) ON DELETE RESTRICT, FOREIGN KEY(material_type_id) REFERENCES material_types(id) ON DELETE RESTRICT, FOREIGN KEY(category_id) REFERENCES material_categories(id) ON DELETE RESTRICT, FOREIGN KEY(base_unit_id) REFERENCES units(id) ON DELETE RESTRICT) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
CREATE TABLE code_rules (id BIGINT AUTO_INCREMENT PRIMARY KEY, organization_id BIGINT NOT NULL, category_id BIGINT NOT NULL, prefix VARCHAR(30) NOT NULL, sequence_length TINYINT NOT NULL, next_sequence BIGINT NOT NULL DEFAULT 1, status VARCHAR(20) NOT NULL DEFAULT 'active', UNIQUE KEY uk_code_rules_org_category(organization_id,category_id), FOREIGN KEY(organization_id) REFERENCES organizations(id), FOREIGN KEY(category_id) REFERENCES material_categories(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
CREATE TABLE material_unit_conversions (id BIGINT AUTO_INCREMENT PRIMARY KEY, organization_id BIGINT NOT NULL, material_id BIGINT NOT NULL, from_unit_id BIGINT NOT NULL, to_unit_id BIGINT NOT NULL, conversion_rate DECIMAL(20,6) NOT NULL, effective_from DATE NOT NULL, effective_to DATE NULL, KEY idx_material_unit_conversions_material(organization_id,material_id), FOREIGN KEY(organization_id) REFERENCES organizations(id), FOREIGN KEY(material_id) REFERENCES materials(id), FOREIGN KEY(from_unit_id) REFERENCES units(id), FOREIGN KEY(to_unit_id) REFERENCES units(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
CREATE TABLE tags (id BIGINT AUTO_INCREMENT PRIMARY KEY, material_id BIGINT NOT NULL, tag_code VARCHAR(100) NOT NULL UNIQUE, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_tags_material(material_id), FOREIGN KEY(material_id) REFERENCES materials(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
CREATE TABLE business_partners (id BIGINT AUTO_INCREMENT PRIMARY KEY, legal_name VARCHAR(200) NOT NULL, unified_credit_code VARCHAR(50) UNIQUE, contact_name VARCHAR(100), contact_phone VARCHAR(50), address VARCHAR(500), created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
-- Partner categories support selecting material and finished_product together;
|
||
-- an empty set means unclassified. Both fields are created with the table.
|
||
CREATE TABLE organization_partners (id BIGINT AUTO_INCREMENT PRIMARY KEY, organization_id BIGINT NOT NULL, business_partner_id BIGINT NOT NULL, partner_code VARCHAR(50) NOT NULL, is_supplier BOOLEAN NOT NULL DEFAULT FALSE, is_customer BOOLEAN NOT NULL DEFAULT FALSE, supplier_status VARCHAR(20), supplier_categories SET('material','finished_product') NOT NULL DEFAULT '', customer_status VARCHAR(20), customer_categories SET('material','finished_product') NOT NULL DEFAULT '', settlement_currency CHAR(3), payment_terms VARCHAR(200), created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uk_organization_partners_org_partner(organization_id,business_partner_id), UNIQUE KEY uk_organization_partners_org_code(organization_id,partner_code), FOREIGN KEY(organization_id) REFERENCES organizations(id), FOREIGN KEY(business_partner_id) REFERENCES business_partners(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
CREATE TABLE material_suppliers (id BIGINT AUTO_INCREMENT PRIMARY KEY, organization_id BIGINT NOT NULL, material_id BIGINT NOT NULL, organization_partner_id BIGINT NOT NULL, price DECIMAL(20,6) NOT NULL, currency CHAR(3) NOT NULL, minimum_order_quantity DECIMAL(20,6) NOT NULL DEFAULT 0, lead_time_days INT NOT NULL DEFAULT 0, is_preferred BOOLEAN NOT NULL DEFAULT FALSE, effective_from DATE NOT NULL, effective_to DATE, status VARCHAR(20) NOT NULL DEFAULT 'active', KEY idx_material_suppliers_material(organization_id,material_id), FOREIGN KEY(organization_id) REFERENCES organizations(id), FOREIGN KEY(material_id) REFERENCES materials(id), FOREIGN KEY(organization_partner_id) REFERENCES organization_partners(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
CREATE TABLE warehouses (id BIGINT AUTO_INCREMENT PRIMARY KEY, organization_id BIGINT NOT NULL, code VARCHAR(50) NOT NULL, name VARCHAR(150) NOT NULL, status VARCHAR(20) NOT NULL DEFAULT 'active', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uk_warehouses_org_code(organization_id,code), FOREIGN KEY(organization_id) REFERENCES organizations(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
CREATE TABLE warehouse_locations (id BIGINT AUTO_INCREMENT PRIMARY KEY, warehouse_id BIGINT NOT NULL, code VARCHAR(50) NOT NULL, name VARCHAR(150) NOT NULL, status VARCHAR(20) NOT NULL DEFAULT 'active', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uk_warehouse_locations_warehouse_code(warehouse_id,code), FOREIGN KEY(warehouse_id) REFERENCES warehouses(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
CREATE TABLE user_warehouses (user_id BIGINT NOT NULL, warehouse_id BIGINT NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY(user_id,warehouse_id), FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE, FOREIGN KEY(warehouse_id) REFERENCES warehouses(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
CREATE TABLE inventory_balances (id BIGINT AUTO_INCREMENT PRIMARY KEY, organization_id BIGINT NOT NULL, warehouse_id BIGINT NOT NULL, location_id BIGINT NOT NULL, material_id BIGINT NOT NULL, on_hand DECIMAL(20,6) NOT NULL DEFAULT 0, reserved DECIMAL(20,6) NOT NULL DEFAULT 0, locked DECIMAL(20,6) NOT NULL DEFAULT 0, in_transit DECIMAL(20,6) NOT NULL DEFAULT 0, version INT NOT NULL DEFAULT 1, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uk_inventory_balance_dimension(organization_id,warehouse_id,location_id,material_id), KEY idx_inventory_balances_material(organization_id,material_id), FOREIGN KEY(organization_id) REFERENCES organizations(id), FOREIGN KEY(warehouse_id) REFERENCES warehouses(id), FOREIGN KEY(location_id) REFERENCES warehouse_locations(id), FOREIGN KEY(material_id) REFERENCES materials(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
CREATE TABLE stock_warning_rules (id BIGINT AUTO_INCREMENT PRIMARY KEY, organization_id BIGINT NOT NULL, warehouse_id BIGINT NOT NULL, material_id BIGINT NOT NULL, minimum_available DECIMAL(20,6) NOT NULL DEFAULT 0, enabled BOOLEAN NOT NULL DEFAULT TRUE, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uk_stock_warning_rules_dimension(organization_id,warehouse_id,material_id), FOREIGN KEY(organization_id) REFERENCES organizations(id), FOREIGN KEY(warehouse_id) REFERENCES warehouses(id), FOREIGN KEY(material_id) REFERENCES materials(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
CREATE TABLE inbound_orders (id BIGINT AUTO_INCREMENT PRIMARY KEY, organization_id BIGINT NOT NULL DEFAULT 1, warehouse_id BIGINT NOT NULL DEFAULT 1, location_id BIGINT NOT NULL DEFAULT 1, inbound_no VARCHAR(32) UNIQUE, operator VARCHAR(100), inbound_date DATETIME NOT NULL, handler VARCHAR(100) NOT NULL, supplier VARCHAR(200), remark TEXT, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY idx_inbound_orders_date(inbound_date), FOREIGN KEY(organization_id) REFERENCES organizations(id), FOREIGN KEY(warehouse_id) REFERENCES warehouses(id), FOREIGN KEY(location_id) REFERENCES warehouse_locations(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
CREATE TABLE inbound_items (id BIGINT AUTO_INCREMENT PRIMARY KEY, inbound_order_id BIGINT NOT NULL, material_id BIGINT NOT NULL, quantity INT NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_inbound_items_order(inbound_order_id), FOREIGN KEY(inbound_order_id) REFERENCES inbound_orders(id) ON DELETE CASCADE, FOREIGN KEY(material_id) REFERENCES materials(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
CREATE TABLE outbound_orders (id BIGINT AUTO_INCREMENT PRIMARY KEY, organization_id BIGINT NOT NULL DEFAULT 1, warehouse_id BIGINT NOT NULL DEFAULT 1, location_id BIGINT NOT NULL DEFAULT 1, outbound_no VARCHAR(32) UNIQUE, operator VARCHAR(100), outbound_date DATETIME NOT NULL, handler VARCHAR(100) NOT NULL, receiver VARCHAR(100) NOT NULL, remark TEXT, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY idx_outbound_orders_date(outbound_date), FOREIGN KEY(organization_id) REFERENCES organizations(id), FOREIGN KEY(warehouse_id) REFERENCES warehouses(id), FOREIGN KEY(location_id) REFERENCES warehouse_locations(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
CREATE TABLE outbound_items (id BIGINT AUTO_INCREMENT PRIMARY KEY, outbound_order_id BIGINT NOT NULL, material_id BIGINT NOT NULL, quantity INT NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_outbound_items_order(outbound_order_id), FOREIGN KEY(outbound_order_id) REFERENCES outbound_orders(id) ON DELETE CASCADE, FOREIGN KEY(material_id) REFERENCES materials(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
CREATE TABLE material_logs (id BIGINT AUTO_INCREMENT PRIMARY KEY, operation_type VARCHAR(50) NOT NULL, material_id BIGINT NOT NULL, old_value TEXT, new_value TEXT, operator VARCHAR(100), description TEXT, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_material_logs_material(material_id), FOREIGN KEY(material_id) REFERENCES materials(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
CREATE TABLE stock_logs (id BIGINT AUTO_INCREMENT PRIMARY KEY, material_id BIGINT NOT NULL, type ENUM('in','out') NOT NULL, quantity INT NOT NULL, related_order_type VARCHAR(50) NOT NULL, related_order_id BIGINT NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_stock_logs_material(material_id), KEY idx_stock_logs_related_order(related_order_type,related_order_id), FOREIGN KEY(material_id) REFERENCES materials(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
CREATE TABLE material_applications (id BIGINT AUTO_INCREMENT PRIMARY KEY, user_id BIGINT NOT NULL, purpose VARCHAR(500) NOT NULL DEFAULT '', status ENUM('pending','approved','rejected','cancelled') NOT NULL DEFAULT 'pending', rejection_reason VARCHAR(500) NOT NULL DEFAULT '', approved_by BIGINT NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY(user_id) REFERENCES users(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
CREATE TABLE application_items (id BIGINT AUTO_INCREMENT PRIMARY KEY, application_id BIGINT NOT NULL, material_id BIGINT NOT NULL, quantity INT NOT NULL, FOREIGN KEY(application_id) REFERENCES material_applications(id) ON DELETE CASCADE, FOREIGN KEY(material_id) REFERENCES materials(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
INSERT INTO organizations (id,code,name,status) VALUES (1,'DEFAULT','默认公司组织','active');
|
||
INSERT INTO roles (code,name) VALUES ('super_admin','超级管理员'),('warehouse_admin','仓库管理员'),('purchaser','采购人员'),('technician','技术人员'),('production','生产人员'),('sales','销售人员');
|
||
INSERT INTO permissions (code,name) VALUES ('master.material.read','查看物料'),('master.material.write','维护物料'),('master.config.write','维护物料基础配置'),('organization.read','查看公司组织'),('organization.write','维护公司组织'),('organization.user.write','维护用户'),('organization.role.write','维护角色与权限'),('partner.supplier.read','查看供应商'),('partner.supplier.write','维护供应商'),('partner.customer.read','查看客户'),('partner.customer.write','维护客户'),('inventory.stock.read','查看库存'),('inventory.warning.write','维护库存预警'),('inventory.inbound.write','维护入库'),('inventory.outbound.write','维护出库');
|
||
INSERT INTO role_permissions (role_id,permission_id) SELECT r.id,p.id FROM roles r CROSS JOIN permissions p WHERE r.code='super_admin';
|
||
INSERT INTO material_types (organization_id,code,name) VALUES (1,'raw_material','原材料'),(1,'semi_finished','半成品'),(1,'finished_good','成品'),(1,'consumable','耗材'),(1,'service','服务');
|
||
-- Category tree: 6 majors (code A-F) + 28 subcategories (code A01-F05).
|
||
INSERT INTO material_categories (organization_id,code,name,description) VALUES
|
||
(1,'A','机械结构类','遵循通用分级规则：零加工外购标准件 → 零加工外购成品件 → 外购原料简易加工 → 深度定制加工件 → 本类兜底。纯机械传动件纳入本类管理。'),
|
||
(1,'B','电气元器件类','遵循通用分级规则：零加工外购标准成品 → 外购原料简易加工 → 单项工艺定制加工 → 多工艺集成组装件 → 本类兜底。带电气接口、电控功能的电机、电动部件统一归入本类；外购卷材/长条电气件，现场裁剪使用的纳入简易加工子类。'),
|
||
(1,'C','产品工艺耗材类','按使用场景划分，仅收录装配在设备内部、随产品出厂的专用耗材，全部为外购成品；车间通用清洁、劳保、运维耗材不纳入本类。'),
|
||
(1,'D','软件&固件&授权类','收录设备内置运行、随产品交付的程序、授权介质、使用许可，属于无形资产及配套实体载体，无物理加工环节。'),
|
||
(1,'E','半成品&成品类','按产品装配阶段划分，为多类零部件组合而成的装配体，是BOM顶层节点；仅区分半成品、成品两类。'),
|
||
(1,'F','包材类','本类统一收纳不参与设备核心功能运行、不属于机身核心的物料，仅作为产品配套、合规、交付附属物件。包含附着机身的标识、随机资料、整套包装物料、交付工具等配套物资，全部随整机出厂交付，不影响设备核心功能实现。');
|
||
INSERT INTO material_categories (organization_id,code,name,description) VALUES
|
||
(1,'A01','标准件','1.核心定义：国家/行业通用标准化零件，型号固定，无需绘图、无需任何二次加工。 2.包含说明：设备通用紧固、锁紧、防松类标准配件，采用全行业通用规格，无非标改造。 3.现场举例：内六角螺钉、沉头螺钉、组合螺钉、六角螺母、平垫圈、弹簧垫圈、防松垫片、六角铜螺柱。 4.边界区分：国标通用紧固配件统一归此类；非标定制紧固结构件归入 A04。'),
|
||
(1,'A02','成品件','1.核心定义：工业标准成品件，整件采购到货，无需裁切、折弯、切削、焊接等二次加工，可直接装配使用。 2.包含说明：设备通用机械辅助成品、纯机械传动成品件，自带标准型号，无需图纸定制。 3.现场举例：脚轮、磁铁、设备防护罩、标准导轨、丝杆、轴承、同步轮、同步带。 4.边界区分：完整成品无需加工归A02；原材料需裁切加工归A03。'),
|
||
(1,'A03','简易加工件','1.核心定义：采购标准定尺长条原料，仅做断料、裁切、简单打孔，无车铣、折弯、焊接、异形精密深加工。 2.包含说明：机架、框架类基础原料件，仅做简单下料、简易开孔处理。 3.现场举例：工业铝型材、方管、圆管、槽钢、角钢、实心棒材裁切下料件。 4.边界区分：长条原料简易裁切归A03；成品整件直接使用=A02；异形精密切削成型=A04。'),
|
||
(1,'A04','精密加工件','1.核心定义：金属厚料基材，通过CNC车、铣、钻、镗、削精密成型的非标结构件，包含金属研发手板、非标传动结构件。 2.包含说明：异形结构、精密配合、运动连接、承重支撑类金属定制件。 3.现场举例：设备金属板、安装转接板、支撑柱、旋转支架、非标机械连杆、金属CNC手板。 4.边界区分：厚料、切削工艺归A04；金属薄板折弯、焊接成型归A05。'),
|
||
(1,'A05','钣金件','1.核心定义：金属薄板基材，通过折弯、冲压、焊接、开孔成型的板类结构件，包含钣金研发手板。 2.包含说明：设备外壳基材、层板、机架、屏蔽防护类钣金件。 3.现场举例：金属层板、屏蔽罩、钢架、侧板、钣金支架、薄板折弯手板。 4.边界区分：薄板基材、折弯/焊接/冲压为主工艺归A05；厚料异形精密切削工艺归A04。'),
|
||
(1,'A06','塑胶件','1.核心定义：纯塑胶材质结构件、外观件，涵盖注塑量产件、3D打印/CNC/复模研发手板。 2.包含说明：塑胶防护、外观、支撑、壳体类零件，不区分生产工艺与阶段。 3.现场举例：灯罩、灯带支座、设备外壳、扶手蒙皮、塑胶3D打印手板。 4.边界区分：所有纯塑胶结构件统一归此类；手板与量产件通过系统属性标注区分。'),
|
||
(1,'A07','其他','1.核心定义：以上子类无法精准归类的小众机械零件。 2.包含说明：冷门机械辅件、非标小连接件。 3.现场举例：碟形弹簧、小型非标结构件。 4.边界区分：A01-A06无法匹配的机械类物料，全部归入此类。'),
|
||
(1,'B01','成品件','1.核心定义：市面标准电气成品、模块、电动部件，整件采购，无需出图、加工、组装，可直接装配。 2.包含说明：开关、电源、端子、标准线材、工控模块、各类电机及电动执行件。 3.现场举例：急停按钮、船型开关、温控开关、磁环、接线端子、冷压端子、开关电源、UPS、触摸显示器、工控机、IO扩展模块、标准网线、串口线、伺服电机、步进电机。 4.边界区分：无定制、无加工的标准电气成品、电动件全部归此类；需现场裁剪的长条电气件归入B02。'),
|
||
(1,'B02','简易加工件','1.核心定义：采购长条、卷材类标准电气原料，到货后仅做裁剪、分段等简单加工，无焊接、改板、接线等深度作业。 2.包含说明：通用长条灯条、连续式灯带、长距离线缆等，按装配尺寸现场裁切使用。 3.现场举例：整机外购标准灯条、长条形灯带，根据安装长度裁剪分段。 4.边界区分：仅做裁剪、分段的长条/卷材电气原料归B02；完整成品电气件归B01；需制板、压接定制的物料归入后续子类。'),
|
||
(1,'B03','PCB硬板','1.核心定义：依据我方PCB图纸，外协加工的硬质电路板，含贴片、插件成品板。 2.包含说明：自研主控板、驱动板、接口板等硬质功能板卡。 3.现场举例：主控灯带控制板、从控灯带控制板、各类自研PCB板卡。 4.边界区分：仅单纯PCB板加工，无外部其他器件装配。'),
|
||
(1,'B04','FPC软板','1.核心定义：依据图纸，外协定制的FPC软板、柔性排线。 2.包含说明：设备活动连接、弯折部位专用柔性电路件。 3.现场举例：设备关节连接软板、显示模组柔性排线。 4.边界区分：柔性线路板归B04；硬质PCB板归入B03。'),
|
||
(1,'B05','线束','1.核心定义：依据线表、线序、长度要求，外协裁切、压接、编组的专用非标线束。 2.包含说明：设备专用成套线缆、定制线组。 3.现场举例：定制线缆、LM系列专用线束。 4.边界区分：纯线材外协压接编组加工，无板卡、器件组装。'),
|
||
(1,'B06','组装组件','1.核心定义：我方提供图纸、电路图、BOM、装配工艺，外协完成板卡、线束、电气件整体组装、接线、调试后整件来料的集成组件。 2.包含说明：PCB、软板、线材、电动件组合而成的集成电气装配体。 3.现场举例：PCB板+柔性线路板+电机+配套线材集成组件。 4.边界区分：多品类电气件集成装配体归此类；单一板卡、单一线束分别归入对应子类。'),
|
||
(1,'B07','其他','1.核心定义：以上子类无法归类的小众电气配件、特殊电子元件。 2.包含说明：冷门电气辅件、非主流功能元件。 3.现场举例：B01-B06无法匹配的电气类物料，统一归入此类。'),
|
||
(1,'C01','连接耗材','1.核心定义：设备接线、装配过程使用，最终留存于产品内部的辅材。 2.包含说明：线路防护、绝缘、粘接类耗材。 3.现场举例：热缩管、梅花管、绝缘护套、设备专用粘接剂。 4.边界区分：仅产品内置装配耗材归此类。'),
|
||
(1,'C02','密封耗材','1.核心定义：用于设备密封、防尘、防水，装配后随产品交付的耗材。 2.包含说明：各类密封垫圈、专用密封油脂、防护填充材料。 3.现场举例：设备密封胶圈、腔体密封脂。 4.边界区分：设备本体密封耗材归此类；生产设备运维耗材不纳入。'),
|
||
(1,'C03','其他','1.核心定义：以上子类无法归类、随产品出厂的小众工艺耗材。 2.包含说明：本类仅收纳产品内置耗材，通用车间耗材一律排除。'),
|
||
(1,'D01','自研程序固件','1.核心定义：企业自主开发，嵌入设备硬件、保障设备核心运行的嵌入式固件、上位机控制程序。 2.包含说明：设备下位机固件、定制控制程序、专用驱动程序。 3.现场举例：设备主控固件、人机界面程序、运动控制程序。 4.边界区分：自主研发、设备必备的程序归此类；办公、设计类软件不纳入。'),
|
||
(1,'D02','外购软件授权','1.核心定义：外部采购，设备运行必需的第三方商用软件、正版使用许可。 2.包含说明：设备配套第三方控制软件、功能组件、永久/阶段性使用授权。 3.现场举例：第三方算法软件、配套组态软件、软件使用授权证书。 4.边界区分：参与设备功能实现的外购软件及授权归此类。'),
|
||
(1,'D03','授权介质','1.核心定义：承载软件、设备授权的实物硬件，随整机配套交付。 2.包含说明：软件加密锁、设备身份密钥等硬件载体。 3.现场举例：软件加密狗、专用授权密钥硬件。 4.边界区分：仅产品配套的授权硬件归此类。'),
|
||
(1,'D04','其他','1.核心定义：以上子类无法归类的配套程序组件、临时授权、辅助文档。 2.包含说明：软件、授权相关杂项统一归入此类。'),
|
||
(1,'E01','成品','1.核心定义：完成全部装配、调试、检验，可直接对外交付的有源医疗器械整机。 2.包含说明：公司主营各类完整诊疗设备。 3.现场举例：眼科手术控制系统、主控制台、从控制台整机。 4.边界区分：最终交付整机产品归此类。'),
|
||
(1,'E02','半成品','1.核心定义：由多类零部件装配而成，未完成最终整机装配，可流转至下道工序继续加工的中间组件。 2.包含说明：工序级装配体、模块级组合组件。 3.现场举例：控制台外壳组件、底座组件、运动机构组件、电子集成组件。 4.边界区分：非最终整机的组合装配体归此类。'),
|
||
(1,'F01','资料文档','1.核心定义：随整机配套交付的纸质/合规类技术文档、资质文件。 2.包含说明：产品使用、安装、维修、合规备案所需配套资料。 3.现场举例：产品说明书、操作手册、安装手册、维修手册、出厂合格证、检测报告。 4.边界区分：仅产品随货交付的正式资料文件归入此类；内部研发工艺资料不纳入BOM。'),
|
||
(1,'F02','标识标牌','1.核心定义：附着、固定在设备机身表面，用于合规标示、参数说明、警示提示的标识类物件。 2.包含说明：设备铭牌、参数标签、警示标贴、功能标识、规格标牌。 3.现场举例：机身金属铭牌、设备参数贴纸、安全警示标签、接口标识贴、合规标识牌。 4.边界区分：所有贴附/固定在产品本体的标识类物件统一归入此类，不再归入机械类。'),
|
||
(1,'F03','包装物料','1.核心定义：产品整机交付使用的外包装箱体、外壳及缓冲防护、填充类配套物料。 2.包含说明：定制航空箱、外包装箱体、防护外箱、内衬框架、缓冲泡棉、填充棉、防护内衬、减震包装辅材。 3.现场举例：设备定制航空箱、整机外包装箱、成型泡棉内衬、定位缓冲泡棉、防震填充辅料。 4.边界区分：所有产品专属包装、防护、减震填充物料全部归入此类。'),
|
||
(1,'F04','随机工具配件','1.核心定义：随产品整机交付给客户，供客户安装、调试、日常运维、拆装使用的小型工具、配套配件。 2.包含说明：设备专属交付工具、配套小配件，非设备本体结构件。 3.现场举例：随机内六角扳手、专用拆装工具、配套备用小配件。 4.边界区分：随货交付客户的工具归入此类；车间生产/自检自用工具、工装治具不纳入本BOM体系。'),
|
||
(1,'F05','其他','1.核心定义：以上子类无法归类、随产品交付的其余附属配套物料。 2.包含说明：本大类无对应子类的产品配套杂项，统一兜底归入。');
|
||
-- Subcategory codes are 3 chars (A01); their first letter is the major's code.
|
||
UPDATE material_categories AS c
|
||
JOIN material_categories AS p ON p.organization_id = c.organization_id AND p.code = LEFT(c.code, 1)
|
||
SET c.parent_id = p.id
|
||
WHERE c.organization_id = 1 AND CHAR_LENGTH(c.code) = 3 AND c.parent_id IS NULL;
|
||
-- Material number = subcategory code + 4-digit sequence; next_sequence is
|
||
-- "current max sequence + 1", so the next codes are A010201 / A020202 / B010203.
|
||
INSERT INTO code_rules (organization_id,category_id,prefix,sequence_length,next_sequence,status)
|
||
SELECT 1,c.id,c.code,4,CASE c.code WHEN 'A02' THEN 202 WHEN 'B01' THEN 203 ELSE 201 END,'active'
|
||
FROM material_categories c WHERE c.organization_id = 1 AND c.parent_id IS NOT NULL;
|
||
INSERT INTO warehouses (id,organization_id,code,name,status) VALUES (1,1,'DEFAULT','默认仓库','active');
|
||
INSERT INTO warehouse_locations (id,warehouse_id,code,name,status) VALUES (1,1,'MAIN','主库位','active');
|
||
|
||
-- Seed encoding is handled by SET NAMES utf8mb4 at the top of this file. The
|
||
-- previous latin1/BINARY round-trip repair is gone: it only produced correct
|
||
-- text when the client was latin1, and it corrupts (or destroys non-latin1
|
||
-- characters such as "→") when the client is already utf8mb4.
|
||
|
||
-- ============================================================================
|
||
-- 示例数据：客户、供应商。以下企业、联系人、电话及地址均为虚构，仅用于演示。
|
||
-- 共 20 家企业：8 家仅供应商、8 家仅客户、4 家同时具有两种身份。
|
||
-- 默认归属组织 ID 为 1；信用代码留空，伙伴编码以 DEMO- 开头。
|
||
-- 本段示例数据会跳过已有记录；init.sql 整体仍仅用于空库首次初始化。
|
||
-- ============================================================================
|
||
SET @demo_organization_id = 1;
|
||
|
||
DROP TEMPORARY TABLE IF EXISTS tmp_demo_partners_20261008;
|
||
CREATE TEMPORARY TABLE tmp_demo_partners_20261008 (
|
||
partner_code VARCHAR(50) NOT NULL PRIMARY KEY,
|
||
legal_name VARCHAR(200) NOT NULL,
|
||
contact_name VARCHAR(100) NOT NULL,
|
||
contact_phone VARCHAR(50) NOT NULL,
|
||
address VARCHAR(500) NOT NULL,
|
||
is_supplier BOOLEAN NOT NULL,
|
||
is_customer BOOLEAN NOT NULL,
|
||
supplier_status VARCHAR(20) NULL,
|
||
customer_status VARCHAR(20) NULL,
|
||
supplier_categories SET('material', 'finished_product') NOT NULL DEFAULT '',
|
||
customer_categories SET('material', 'finished_product') NOT NULL DEFAULT '',
|
||
payment_terms VARCHAR(200) NOT NULL
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
INSERT INTO tmp_demo_partners_20261008
|
||
(partner_code, legal_name, contact_name, contact_phone, address,
|
||
is_supplier, is_customer, supplier_status, customer_status,
|
||
supplier_categories, customer_categories, payment_terms)
|
||
VALUES
|
||
('DEMO-SUP-001', '示例·北京澄岳电子材料有限公司', '周明远', '010-0000-1001', '北京市海淀区示例路 18 号',
|
||
TRUE, FALSE, 'active', NULL, 'material', '', '验收后 30 天付款'),
|
||
('DEMO-SUP-002', '示例·武汉青岚精密部件有限公司', '林晓晴', '027-0000-1002', '武汉市洪山区示例大道 36 号',
|
||
TRUE, FALSE, 'active', NULL, 'material', '', '验收后 15 天付款'),
|
||
('DEMO-SUP-003', '示例·苏州星禾仪器贸易有限公司', '郑柏川', '0512-0000-1003', '苏州市工业园区示例街 52 号',
|
||
TRUE, FALSE, 'active', NULL, 'finished_product', '', '款到发货'),
|
||
('DEMO-SUP-004', '示例·深圳沐辰机电有限公司', '许知夏', '0755-0000-1004', '深圳市南山区示例路 9 号',
|
||
TRUE, FALSE, 'active', NULL, 'material,finished_product', '', '月结 30 天'),
|
||
('DEMO-SUP-005', '示例·成都锦栖包装材料有限公司', '唐思齐', '028-0000-1005', '成都市高新区示例街 27 号',
|
||
TRUE, FALSE, 'suspended', NULL, 'material', '', '预付 30%，验收后付尾款'),
|
||
('DEMO-SUP-006', '示例·南京云朔自动化设备有限公司', '宋景行', '025-0000-1006', '南京市江宁区示例大道 61 号',
|
||
TRUE, FALSE, 'pending', NULL, 'finished_product', '', '验收后 45 天付款'),
|
||
('DEMO-SUP-007', '示例·杭州珩远工业配套有限公司', '韩若溪', '0571-0000-1007', '杭州市滨江区示例路 43 号',
|
||
TRUE, FALSE, 'active', NULL, 'material,finished_product', '', '月结 60 天'),
|
||
('DEMO-SUP-008', '示例·天津启沅物资有限公司', '顾承宇', '022-0000-1008', '天津市西青区示例街 15 号',
|
||
TRUE, FALSE, 'eliminated', NULL, '', '', '款到发货'),
|
||
|
||
('DEMO-CUS-001', '示例·北京云帆研发技术有限公司', '沈沐阳', '010-0000-2001', '北京市昌平区示例大道 29 号',
|
||
FALSE, TRUE, NULL, 'active', '', 'material', '发货后 30 天收款'),
|
||
('DEMO-CUS-002', '示例·武汉卓溪智能装备有限公司', '蒋书宁', '027-0000-2002', '武汉市江夏区示例路 48 号',
|
||
FALSE, TRUE, NULL, 'active', '', 'finished_product', '预收 50%，交付后收尾款'),
|
||
('DEMO-CUS-003', '示例·上海禾川工业贸易有限公司', '何清越', '021-0000-2003', '上海市闵行区示例街 11 号',
|
||
FALSE, TRUE, NULL, 'active', '', 'material,finished_product', '月结 30 天'),
|
||
('DEMO-CUS-004', '示例·西安辰澜实验科技有限公司', '邵文轩', '029-0000-2004', '西安市高新区示例路 67 号',
|
||
FALSE, TRUE, NULL, 'active', '', 'material', '款到发货'),
|
||
('DEMO-CUS-005', '示例·长沙栩航设备经营有限公司', '叶星遥', '0731-0000-2005', '长沙市岳麓区示例大道 23 号',
|
||
FALSE, TRUE, NULL, 'suspended', '', 'finished_product', '验收后 30 天收款'),
|
||
('DEMO-CUS-006', '示例·济南清屿制造有限公司', '杜泽安', '0531-0000-2006', '济南市历城区示例街 39 号',
|
||
FALSE, TRUE, NULL, 'active', '', 'material,finished_product', '月结 45 天'),
|
||
('DEMO-CUS-007', '示例·郑州景禾商贸有限公司', '梁初瑶', '0371-0000-2007', '郑州市中原区示例路 56 号',
|
||
FALSE, TRUE, NULL, 'pending', '', 'finished_product', '预付全款'),
|
||
('DEMO-CUS-008', '示例·青岛若川技术服务有限公司', '陆嘉树', '0532-0000-2008', '青岛市崂山区示例大道 14 号',
|
||
FALSE, TRUE, NULL, 'inactive', '', '', '交付后 15 天收款'),
|
||
|
||
('DEMO-BOTH-001', '示例·北京知衡产业有限公司', '方予安', '010-0000-3001', '北京市大兴区示例路 72 号',
|
||
TRUE, TRUE, 'active', 'active', 'material', 'finished_product', '采购月结 30 天；销售预收 30%'),
|
||
('DEMO-BOTH-002', '示例·武汉观澜机电贸易有限公司', '程舒雅', '027-0000-3002', '武汉市东西湖区示例街 31 号',
|
||
TRUE, TRUE, 'active', 'active', 'material,finished_product', 'material,finished_product', '采购、销售均月结 30 天'),
|
||
('DEMO-BOTH-003', '示例·合肥映川智能科技有限公司', '贺景辰', '0551-0000-3003', '合肥市蜀山区示例大道 46 号',
|
||
TRUE, TRUE, 'suspended', 'active', 'finished_product', 'material', '采购款到发货；销售月结 45 天'),
|
||
('DEMO-BOTH-004', '示例·厦门沅禾工业服务有限公司', '白念慈', '0592-0000-3004', '厦门市集美区示例路 8 号',
|
||
TRUE, TRUE, 'active', 'pending', 'material', '', '采购验收后 15 天付款；销售预付全款');
|
||
|
||
START TRANSACTION;
|
||
|
||
-- 仅在目标组织存在时插入企业，避免错误组织 ID 产生无关联数据。
|
||
INSERT INTO business_partners
|
||
(legal_name, unified_credit_code, contact_name, contact_phone, address)
|
||
SELECT demo.legal_name, NULL, demo.contact_name, demo.contact_phone, demo.address
|
||
FROM tmp_demo_partners_20261008 demo
|
||
JOIN organizations org ON org.id = @demo_organization_id
|
||
WHERE NOT EXISTS (
|
||
SELECT 1 FROM business_partners existing WHERE existing.legal_name = demo.legal_name
|
||
);
|
||
|
||
-- 不硬编码自增 ID。重复运行时保留原关系及已编辑的分类、状态等数据。
|
||
INSERT INTO organization_partners
|
||
(organization_id, business_partner_id, partner_code, is_supplier, is_customer,
|
||
supplier_status, customer_status, supplier_categories, customer_categories,
|
||
settlement_currency, payment_terms)
|
||
SELECT org.id, partner.id, demo.partner_code, demo.is_supplier, demo.is_customer,
|
||
demo.supplier_status, demo.customer_status, demo.supplier_categories, demo.customer_categories,
|
||
'CNY', demo.payment_terms
|
||
FROM tmp_demo_partners_20261008 demo
|
||
JOIN organizations org ON org.id = @demo_organization_id
|
||
JOIN (
|
||
SELECT legal_name, MIN(id) AS id FROM business_partners GROUP BY legal_name
|
||
) partner ON partner.legal_name = demo.legal_name
|
||
WHERE NOT EXISTS (
|
||
SELECT 1 FROM organization_partners existing
|
||
WHERE existing.organization_id = org.id
|
||
AND (existing.business_partner_id = partner.id OR existing.partner_code = demo.partner_code)
|
||
);
|
||
|
||
COMMIT;
|
||
|
||
-- 检查导入结果；目标组织不存在时明确提示，并且不会插入任何数据。
|
||
SELECT @demo_organization_id AS organization_id,
|
||
CASE WHEN EXISTS (SELECT 1 FROM organizations WHERE id = @demo_organization_id)
|
||
THEN '目标组织存在，请核对下面的导入结果'
|
||
ELSE '目标组织不存在：未导入，请修改 @demo_organization_id 后重新执行'
|
||
END AS import_message;
|
||
|
||
SELECT op.partner_code, bp.legal_name, op.is_supplier, op.is_customer,
|
||
op.supplier_categories, op.customer_categories, op.supplier_status, op.customer_status
|
||
FROM organization_partners op
|
||
JOIN business_partners bp ON bp.id = op.business_partner_id
|
||
JOIN tmp_demo_partners_20261008 demo
|
||
ON demo.partner_code = op.partner_code AND demo.legal_name = bp.legal_name
|
||
WHERE op.organization_id = @demo_organization_id
|
||
ORDER BY op.partner_code;
|
||
|
||
DROP TEMPORARY TABLE tmp_demo_partners_20261008;
|