178 lines
9.4 KiB
SQL
178 lines
9.4 KiB
SQL
-- ============================================
|
|
-- Supplier Service 数据库初始化脚本
|
|
-- ============================================
|
|
|
|
-- 供应商表
|
|
CREATE TABLE IF NOT EXISTS `suppliers` (
|
|
`id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
|
`supplier_code` VARCHAR(50) NOT NULL COMMENT '供应商编码' unique,
|
|
`supplier_name` VARCHAR(200) NOT NULL COMMENT '供应商名称',
|
|
`supplier_type` VARCHAR(50) COMMENT '供应商类型: manufacturer/trader/agent/individual',
|
|
`industry` VARCHAR(100) COMMENT '所属行业',
|
|
`business_license` VARCHAR(100) COMMENT '统一社会信用代码',
|
|
`registered_capital` DECIMAL(15,2) COMMENT '注册资本',
|
|
`establishment_date` VARCHAR(20) COMMENT '成立日期',
|
|
`legal_person` VARCHAR(100) COMMENT '法定代表人',
|
|
`phone` VARCHAR(50) COMMENT '联系电话',
|
|
`email` VARCHAR(100) COMMENT '电子邮箱',
|
|
`website` VARCHAR(200) COMMENT '官方网站',
|
|
`province` VARCHAR(50) COMMENT '省份',
|
|
`city` VARCHAR(50) COMMENT '城市',
|
|
`district` VARCHAR(50) COMMENT '区县',
|
|
`address` VARCHAR(500) COMMENT '详细地址',
|
|
`main_business` TEXT COMMENT '主营业务',
|
|
`status` VARCHAR(50) DEFAULT 'pending' COMMENT '合作状态: pending/active/inactive/blacklist',
|
|
`grade` VARCHAR(10) DEFAULT 'C' COMMENT '供应商等级: A/B/C/D',
|
|
`credit_score` INT DEFAULT 60 COMMENT '信用评分 (0-100)',
|
|
`is_internal` INT DEFAULT 0 COMMENT '是否为内供: 0-外部 1-内部',
|
|
`cooperation_start_date` VARCHAR(20) COMMENT '合作开始日期',
|
|
`cooperation_end_date` VARCHAR(20) COMMENT '合作结束日期',
|
|
`settlement_method` VARCHAR(50) COMMENT '结算方式: prepaid/postpaid/monthly',
|
|
`payment_days` INT COMMENT '付款周期(天)',
|
|
`minimum_order_quantity` DECIMAL(15,3) COMMENT '最低起订量',
|
|
`remark` TEXT COMMENT '备注',
|
|
`attachments` TEXT COMMENT '附件(JSON数组)',
|
|
`audit_status` VARCHAR(50) DEFAULT 'pending' COMMENT '审核状态: pending/approved/rejected',
|
|
`audit_comment` TEXT COMMENT '审核备注',
|
|
`auditor_id` BIGINT COMMENT '审核人ID',
|
|
`audit_time` DATETIME COMMENT '审核时间',
|
|
`version` INT DEFAULT 0 COMMENT '乐观锁版本号',
|
|
`created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
|
`updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
|
`deleted` INT DEFAULT 0 COMMENT '逻辑删除标记',
|
|
PRIMARY KEY (`id`),
|
|
INDEX `idx_supplier_code` (`supplier_code`),
|
|
INDEX `idx_supplier_name` (`supplier_name`),
|
|
INDEX `idx_status` (`status`),
|
|
INDEX `idx_grade` (`grade`),
|
|
INDEX `idx_audit_status` (`audit_status`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='供应商主表';
|
|
|
|
-- 供应商联系人表
|
|
CREATE TABLE IF NOT EXISTS `supplier_contacts` (
|
|
`id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
|
`supplier_id` BIGINT NOT NULL COMMENT '供应商ID',
|
|
`contact_name` VARCHAR(100) NOT NULL COMMENT '联系人姓名',
|
|
`gender` VARCHAR(10) COMMENT '性别: male/female',
|
|
`department` VARCHAR(100) COMMENT '部门',
|
|
`position` VARCHAR(100) COMMENT '职务',
|
|
`mobile` VARCHAR(50) COMMENT '手机号码',
|
|
`phone` VARCHAR(50) COMMENT '固定电话',
|
|
`email` VARCHAR(100) COMMENT '电子邮箱',
|
|
`qq` VARCHAR(50) COMMENT 'QQ号码',
|
|
`wechat` VARCHAR(100) COMMENT '微信',
|
|
`is_primary` INT DEFAULT 0 COMMENT '是否为主要联系人: 0-否 1-是',
|
|
`is_emergency` INT DEFAULT 0 COMMENT '是否为紧急联系人: 0-否 1-是',
|
|
`remark` TEXT COMMENT '备注',
|
|
`created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
|
`updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
|
`deleted` INT DEFAULT 0 COMMENT '逻辑删除标记',
|
|
PRIMARY KEY (`id`),
|
|
INDEX `idx_supplier_id` (`supplier_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='供应商联系人表';
|
|
|
|
-- 供应商银行账户表
|
|
CREATE TABLE IF NOT EXISTS `supplier_bank_accounts` (
|
|
`id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
|
`supplier_id` BIGINT NOT NULL COMMENT '供应商ID',
|
|
`account_name` VARCHAR(200) NOT NULL COMMENT '开户名称',
|
|
`bank_name` VARCHAR(200) NOT NULL COMMENT '开户银行',
|
|
`bank_account` VARCHAR(100) NOT NULL COMMENT '银行账号',
|
|
`branch_name` VARCHAR(200) COMMENT '开户支行',
|
|
`branch_code` VARCHAR(50) COMMENT '银行联行号',
|
|
`province` VARCHAR(50) COMMENT '开户省份',
|
|
`city` VARCHAR(50) COMMENT '开户城市',
|
|
`account_type` VARCHAR(50) COMMENT '账户类型: basic/general/reserve',
|
|
`is_default` INT DEFAULT 0 COMMENT '是否为默认账户: 0-否 1-是',
|
|
`balance` DECIMAL(15,2) DEFAULT 0 COMMENT '账户余额',
|
|
`reserved_phone` VARCHAR(50) COMMENT '银行预留手机号',
|
|
`remark` TEXT COMMENT '备注',
|
|
`created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
|
`updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
|
`deleted` INT DEFAULT 0 COMMENT '逻辑删除标记',
|
|
PRIMARY KEY (`id`),
|
|
INDEX `idx_supplier_id` (`supplier_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='供应商银行账户表';
|
|
|
|
-- 供应商评级表
|
|
CREATE TABLE IF NOT EXISTS `supplier_ratings` (
|
|
`id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
|
`supplier_id` BIGINT NOT NULL COMMENT '供应商ID',
|
|
`rating_period` VARCHAR(50) COMMENT '评价周期: monthly/quarterly/annual',
|
|
`rating_year` INT COMMENT '评价年份',
|
|
`rating_month` INT COMMENT '评价月份',
|
|
`quality_score` DECIMAL(5,2) COMMENT '质量评分(0-100)',
|
|
`delivery_score` DECIMAL(5,2) COMMENT '交期评分(0-100)',
|
|
`price_score` DECIMAL(5,2) COMMENT '价格评分(0-100)',
|
|
`service_score` DECIMAL(5,2) COMMENT '服务评分(0-100)',
|
|
`overall_score` DECIMAL(5,2) COMMENT '综合评分(0-100)',
|
|
`grade` VARCHAR(10) COMMENT '供应商等级: A/B/C/D',
|
|
`evaluator_id` BIGINT COMMENT '评价人ID',
|
|
`evaluator_name` VARCHAR(100) COMMENT '评价人姓名',
|
|
`evaluation_time` DATETIME COMMENT '评价时间',
|
|
`strengths` TEXT COMMENT '优点',
|
|
`weaknesses` TEXT COMMENT '不足',
|
|
`suggestions` TEXT COMMENT '改进建议',
|
|
`remark` TEXT COMMENT '备注',
|
|
`created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
|
`updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
|
`deleted` INT DEFAULT 0 COMMENT '逻辑删除标记',
|
|
PRIMARY KEY (`id`),
|
|
INDEX `idx_supplier_id` (`supplier_id`),
|
|
INDEX `idx_rating_year_month` (`rating_year`, `rating_month`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='供应商评级表';
|
|
|
|
-- 采购询价表
|
|
CREATE TABLE IF NOT EXISTS `procurement_inquiries` (
|
|
`id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
|
`inquiry_no` VARCHAR(50) NOT NULL COMMENT '询价单号' unique,
|
|
`supplier_id` BIGINT NOT NULL COMMENT '供应商ID',
|
|
`supplier_name` VARCHAR(200) COMMENT '供应商名称',
|
|
`inquiry_type` VARCHAR(50) COMMENT '询价类型: product/material/service',
|
|
`product_id` BIGINT COMMENT '商品ID',
|
|
`product_name` VARCHAR(200) COMMENT '商品名称',
|
|
`product_spec` VARCHAR(200) COMMENT '商品规格',
|
|
`unit` VARCHAR(20) COMMENT '商品单位',
|
|
`quantity` DECIMAL(15,3) COMMENT '询价数量',
|
|
`expected_delivery_date` VARCHAR(20) COMMENT '期望交货日期',
|
|
`inquiry_status` VARCHAR(50) DEFAULT 'draft' COMMENT '询价状态: draft/sent/replied/compared/confirmed/cancelled',
|
|
`inquiry_date` DATETIME COMMENT '询价日期',
|
|
`quote_deadline` VARCHAR(20) COMMENT '报价截止日期',
|
|
`quoted_price` DECIMAL(15,4) COMMENT '报价金额',
|
|
`currency` VARCHAR(10) DEFAULT 'CNY' COMMENT '报价币种',
|
|
`quote_valid_until` VARCHAR(20) COMMENT '报价有效期',
|
|
`delivery_cycle` INT COMMENT '交货周期(天)',
|
|
`quality_standard` VARCHAR(500) COMMENT '质量标准',
|
|
`payment_terms` VARCHAR(200) COMMENT '付款方式',
|
|
`remark` TEXT COMMENT '备注',
|
|
`is_selected` INT DEFAULT 0 COMMENT '是否已选择: 0-否 1-是',
|
|
`selection_reason` TEXT COMMENT '选择原因',
|
|
`creator_id` BIGINT COMMENT '创建人ID',
|
|
`creator_name` VARCHAR(100) COMMENT '创建人姓名',
|
|
`created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
|
`updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
|
`deleted` INT DEFAULT 0 COMMENT '逻辑删除标记',
|
|
PRIMARY KEY (`id`),
|
|
INDEX `idx_inquiry_no` (`inquiry_no`),
|
|
INDEX `idx_supplier_id` (`supplier_id`),
|
|
INDEX `idx_inquiry_status` (`inquiry_status`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='采购询价表';
|
|
|
|
-- 供应商操作日志表
|
|
CREATE TABLE IF NOT EXISTS `supplier_operation_logs` (
|
|
`id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
|
`supplier_id` BIGINT NOT NULL COMMENT '供应商ID',
|
|
`operator_id` BIGINT COMMENT '操作人ID',
|
|
`operator_name` VARCHAR(100) COMMENT '操作人姓名',
|
|
`operation_type` VARCHAR(100) COMMENT '操作类型',
|
|
`operation_desc` VARCHAR(500) COMMENT '操作描述',
|
|
`before_data` TEXT COMMENT '操作前数据(JSON)',
|
|
`after_data` TEXT COMMENT '操作后数据(JSON)',
|
|
`ip` VARCHAR(50) COMMENT 'IP地址',
|
|
`user_agent` VARCHAR(500) COMMENT 'User-Agent',
|
|
`created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
|
PRIMARY KEY (`id`),
|
|
INDEX `idx_supplier_id` (`supplier_id`),
|
|
INDEX `idx_created_at` (`created_at`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='供应商操作日志表';
|