173 lines
18 KiB
SQL
173 lines
18 KiB
SQL
-- ============================================================
|
||
-- 福安德智慧报价平台 演示数据
|
||
-- 场景：某制造企业采购电子元器件及工控设备
|
||
-- ============================================================
|
||
|
||
SET NAMES utf8mb4;
|
||
|
||
-- ── 供应商 ──────────────────────────────────────────────────
|
||
INSERT IGNORE INTO biz_supplier(supplier_id,tenant_id,supplier_name,contact,phone,email,address,status,create_by,create_time) VALUES
|
||
(1,1,'深圳市华顺达电子有限公司','王建国','13800138001','wangjg@hsd-elec.com','广东省深圳市宝安区西乡街道华顺工业园3栋','0','admin','2025-12-01 09:00:00'),
|
||
(2,1,'苏州博远精密设备有限公司','李梅','13900139002','limei@boyuan-prc.com','江苏省苏州市吴中区木渎镇博远路88号','0','admin','2025-12-01 09:10:00'),
|
||
(3,1,'上海瑞达工控科技有限公司','张明伟','13700137003','zmw@ruida-ctrl.com','上海市松江区茸北工业区瑞达路12号','0','admin','2025-12-02 10:00:00'),
|
||
(4,1,'广州联盟电气集团股份有限公司','陈晓燕','15900159004','chenxy@lm-elec.com.cn','广东省广州市黄埔区联盟电气工业园A区','0','admin','2025-12-03 14:00:00'),
|
||
(5,1,'武汉华中精工机械有限公司','刘强','13600136005','liuq@hzjg-mach.com','湖北省武汉市东湖新技术开发区华中精工园','0','admin','2025-12-05 09:30:00');
|
||
|
||
-- ── 物料 ────────────────────────────────────────────────────
|
||
INSERT IGNORE INTO biz_material(material_id,tenant_id,category_id,material_code,material_name,spec,unit,brand,status,create_by,create_time) VALUES
|
||
(1,1,0,'M-EL-001','伺服电机','额定功率750W，额定转矩2.4N·m，额定转速3000rpm','台','松下/安川','0','admin','2025-12-01 10:00:00'),
|
||
(2,1,0,'M-EL-002','变频器','三相380V输入，7.5kW，IP20防护','台','ABB/西门子','0','admin','2025-12-01 10:05:00'),
|
||
(3,1,0,'M-EL-003','PLC控制器','CPU模块，数字量I/O 40点，以太网接口','套','西门子S7-1200','0','admin','2025-12-01 10:10:00'),
|
||
(4,1,0,'M-EL-004','工业交换机','8口千兆以太网，导轨安装，宽温型','台','赫斯曼/菲尼克斯','0','admin','2025-12-02 09:00:00'),
|
||
(5,1,0,'M-ME-001','精密导轨','THK型，长度1500mm，宽度35mm','根','THK/上银','0','admin','2025-12-02 09:05:00'),
|
||
(6,1,0,'M-ME-002','滚珠丝杠','直径32mm，导程10mm，长度1200mm','套','NSK/银泰','0','admin','2025-12-02 09:10:00'),
|
||
(7,1,0,'M-ME-003','减速机','行星减速比1:10，输出扭矩150N·m，法兰安装','台','纽卡特/APEX','0','admin','2025-12-03 10:00:00'),
|
||
(8,1,0,'M-EL-005','触摸屏','7寸TFT，分辨率1024×600，RS485/以太网','台','威纶通/昆仑通态','0','admin','2025-12-04 10:00:00'),
|
||
(9,1,0,'M-ME-004','气动电磁阀','5/2通，DC24V，G1/4接口，响应时间≤15ms','只','SMC/费斯托','0','admin','2025-12-05 10:00:00'),
|
||
(10,1,0,'M-EL-006','电源模块','24VDC/10A，输入85-264VAC，效率≥90%','台','明纬/西门子','0','admin','2025-12-05 10:05:00');
|
||
|
||
-- ── RFQ报价请求 ──────────────────────────────────────────────
|
||
-- RFQ-1：自动化生产线控制系统（已完成）
|
||
INSERT IGNORE INTO biz_rfq(rfq_id,tenant_id,rfq_no,rfq_title,deadline,delivery_addr,status,remark,create_by,create_time,update_time) VALUES
|
||
(1,1,'RFQ-2026-001','自动化生产线控制系统采购','2026-01-10 18:00:00','广东省佛山市顺德区福安德工业园仓库','completed','用于新建3号生产线控制系统升级项目，需提供原厂质保证明','admin','2025-12-20 09:00:00','2026-01-15 16:00:00'),
|
||
(2,1,'RFQ-2026-002','精密运动控制组件采购','2026-02-05 18:00:00','广东省佛山市顺德区福安德工业园仓库','completed','5轴联动机床改造项目，要求交货期不超过30天','admin','2026-01-10 10:00:00','2026-02-10 14:00:00'),
|
||
(3,1,'RFQ-2026-003','气动系统元件年度备料采购','2026-03-15 18:00:00','广东省佛山市顺德区福安德工业园仓库','published','年度备件计划，价格参考上年采购单价','admin','2026-02-20 14:00:00','2026-02-20 14:00:00'),
|
||
(4,1,'RFQ-2026-004','HMI及工控网络设备采购','2026-04-01 18:00:00','广东省佛山市顺德区福安德工业园仓库','published','配合MES系统升级项目','admin','2026-03-10 10:00:00','2026-03-10 10:00:00'),
|
||
(5,1,'RFQ-2026-005','Q2伺服系统批量采购','2026-05-10 18:00:00','广东省佛山市顺德区福安德工业园仓库','draft','第二季度计划采购，待审批后发布','admin','2026-04-01 09:00:00','2026-04-01 09:00:00');
|
||
|
||
-- RFQ物料明细
|
||
INSERT IGNORE INTO biz_rfq_item(item_id,rfq_id,material_id,material_name,spec,unit,quantity,expected_price) VALUES
|
||
-- RFQ-1
|
||
(1,1,3,'PLC控制器','CPU模块，数字量I/O 40点，以太网接口','套',5,3200.00),
|
||
(2,1,2,'变频器','三相380V输入，7.5kW，IP20防护','台',8,4500.00),
|
||
(3,1,10,'电源模块','24VDC/10A，输入85-264VAC，效率≥90%','台',20,380.00),
|
||
-- RFQ-2
|
||
(4,2,1,'伺服电机','额定功率750W，额定转矩2.4N·m，额定转速3000rpm','台',10,2800.00),
|
||
(5,2,5,'精密导轨','THK型，长度1500mm，宽度35mm','根',20,650.00),
|
||
(6,2,6,'滚珠丝杠','直径32mm，导程10mm，长度1200mm','套',10,1800.00),
|
||
(7,2,7,'减速机','行星减速比1:10，输出扭矩150N·m，法兰安装','台',5,3500.00),
|
||
-- RFQ-3
|
||
(8,3,9,'气动电磁阀','5/2通，DC24V，G1/4接口，响应时间≤15ms','只',100,85.00),
|
||
-- RFQ-4
|
||
(9,4,8,'触摸屏','7寸TFT，分辨率1024×600，RS485/以太网','台',6,1200.00),
|
||
(10,4,4,'工业交换机','8口千兆以太网，导轨安装，宽温型','台',4,1800.00),
|
||
-- RFQ-5
|
||
(11,5,1,'伺服电机','额定功率750W，额定转矩2.4N·m，额定转速3000rpm','台',30,2600.00),
|
||
(12,5,2,'变频器','三相380V输入，7.5kW，IP20防护','台',15,4200.00);
|
||
|
||
-- RFQ邀请供应商
|
||
INSERT IGNORE INTO biz_rfq_supplier(rfq_id,supplier_id,invited_time,quoted_time,status) VALUES
|
||
(1,1,'2025-12-20 09:30:00','2026-01-05 15:00:00','quoted'),
|
||
(1,3,'2025-12-20 09:30:00','2026-01-06 10:00:00','quoted'),
|
||
(1,4,'2025-12-20 09:30:00','2026-01-07 14:00:00','quoted'),
|
||
(2,2,'2026-01-10 10:30:00','2026-01-22 16:00:00','quoted'),
|
||
(2,5,'2026-01-10 10:30:00','2026-01-23 11:00:00','quoted'),
|
||
(2,1,'2026-01-10 10:30:00','2026-01-25 09:00:00','quoted'),
|
||
(3,1,'2026-02-20 14:30:00',NULL,'pending'),
|
||
(3,3,'2026-02-20 14:30:00',NULL,'pending'),
|
||
(4,3,'2026-03-10 10:30:00',NULL,'pending'),
|
||
(4,4,'2026-03-10 10:30:00',NULL,'pending');
|
||
|
||
-- ── 供应商报价单 ─────────────────────────────────────────────
|
||
-- RFQ-1 三家报价
|
||
INSERT IGNORE INTO biz_quotation(quotation_id,tenant_id,rfq_id,supplier_id,quote_no,valid_days,delivery_days,total_amount,currency,status,note,submit_time,create_by,create_time) VALUES
|
||
(1,1,1,1,'QT-2026-001-HSD',30,25,57320.00,'CNY','accepted','原厂授权经销商，提供原厂质保2年，可开增值税专票','2026-01-05 15:00:00','admin','2026-01-04 14:00:00'),
|
||
(2,1,1,3,'QT-2026-001-RD',30,20,61800.00,'CNY','rejected','含免费现场调试服务一次','2026-01-06 10:00:00','admin','2026-01-05 09:00:00'),
|
||
(3,1,1,4,'QT-2026-001-LM',30,30,55100.00,'CNY','rejected','批量可优惠，货期稍长','2026-01-07 14:00:00','admin','2026-01-06 10:00:00'),
|
||
-- RFQ-2 三家报价
|
||
(4,1,2,2,'QT-2026-002-BY',30,28,108500.00,'CNY','accepted','精密机床专业供应商，提供安装调试支持','2026-01-22 16:00:00','admin','2026-01-21 10:00:00'),
|
||
(5,1,2,5,'QT-2026-002-HZ',30,35,112800.00,'CNY','rejected','含运费及安装，质保18个月','2026-01-23 11:00:00','admin','2026-01-22 09:00:00'),
|
||
(6,1,2,1,'QT-2026-002-HSD',30,25,116200.00,'CNY','rejected','原厂品牌，价格含送货到厂','2026-01-25 09:00:00','admin','2026-01-24 14:00:00'),
|
||
-- RFQ-3 待报价（草稿）
|
||
(7,1,3,1,'QT-2026-003-HSD',30,15,0,'CNY','draft',NULL,NULL,'admin','2026-03-01 09:00:00'),
|
||
-- RFQ-4 已提交待审核
|
||
(8,1,4,3,'QT-2026-004-RD',30,20,14880.00,'CNY','submitted','含调试配置服务','2026-03-20 14:00:00','admin','2026-03-18 10:00:00'),
|
||
(9,1,4,4,'QT-2026-004-LM',30,25,16200.00,'CNY','submitted','标准交货，验收后付款','2026-03-22 16:00:00','admin','2026-03-20 10:00:00');
|
||
|
||
-- 报价明细
|
||
INSERT IGNORE INTO biz_quotation_item(item_id,quotation_id,rfq_item_id,material_name,spec,unit,quantity,unit_price,total_price,delivery_days,remark) VALUES
|
||
-- QT-1 (RFQ-1, 供应商1, 被采纳)
|
||
(1,1,1,'PLC控制器','CPU模块，数字量I/O 40点，以太网接口','套',5,3180.00,15900.00,25,'西门子原厂'),
|
||
(2,1,2,'变频器','三相380V输入，7.5kW，IP20防护','台',8,4390.00,35120.00,25,'ABB原厂'),
|
||
(3,1,3,'电源模块','24VDC/10A，输入85-264VAC，效率≥90%','台',20,315.00,6300.00,20,'明纬品牌'),
|
||
-- QT-2 (RFQ-1, 供应商3, 被拒绝)
|
||
(4,2,1,'PLC控制器','CPU模块，数字量I/O 40点，以太网接口','套',5,3450.00,17250.00,20,'含调试服务'),
|
||
(5,2,2,'变频器','三相380V输入，7.5kW，IP20防护','台',8,4780.00,38240.00,20,'西门子品牌'),
|
||
(6,2,3,'电源模块','24VDC/10A，输入85-264VAC，效率≥90%','台',20,315.50,6310.00,15,'明纬品牌'),
|
||
-- QT-3 (RFQ-1, 供应商4, 被拒绝)
|
||
(7,3,1,'PLC控制器','CPU模块，数字量I/O 40点，以太网接口','套',5,3020.00,15100.00,30,'国产品牌'),
|
||
(8,3,2,'变频器','三相380V输入，7.5kW，IP20防护','台',8,4250.00,34000.00,30,'ABB'),
|
||
(9,3,3,'电源模块','24VDC/10A，输入85-264VAC，效率≥90%','台',20,300.00,6000.00,25,'台湾品牌'),
|
||
-- QT-4 (RFQ-2, 供应商2, 被采纳)
|
||
(10,4,4,'伺服电机','额定功率750W，额定转矩2.4N·m，额定转速3000rpm','台',10,2650.00,26500.00,28,'松下原装'),
|
||
(11,4,5,'精密导轨','THK型，长度1500mm，宽度35mm','根',20,620.00,12400.00,28,'THK日本原产'),
|
||
(12,4,6,'滚珠丝杠','直径32mm，导程10mm，长度1200mm','套',10,1750.00,17500.00,28,'NSK品牌'),
|
||
(13,4,7,'减速机','行星减速比1:10，输出扭矩150N·m，法兰安装','台',5,2420.00,12100.00,28,'纽卡特'),
|
||
-- QT-5 (RFQ-2, 供应商5)
|
||
(14,5,4,'伺服电机','额定功率750W，额定转矩2.4N·m，额定转速3000rpm','台',10,2750.00,27500.00,35,'安川品牌'),
|
||
(15,5,5,'精密导轨','THK型，长度1500mm，宽度35mm','根',20,660.00,13200.00,35,'THK'),
|
||
(16,5,6,'滚珠丝杠','直径32mm，导程10mm，长度1200mm','套',10,1850.00,18500.00,35,'NSK'),
|
||
(17,5,7,'减速机','行星减速比1:10，输出扭矩150N·m，法兰安装','台',5,2520.00,12600.00,35,'APEX'),
|
||
-- QT-8 (RFQ-4, 供应商3, 已提交)
|
||
(18,8,9,'触摸屏','7寸TFT，分辨率1024×600，RS485/以太网','台',6,1180.00,7080.00,20,'威纶通'),
|
||
(19,8,10,'工业交换机','8口千兆以太网，导轨安装，宽温型','台',4,1950.00,7800.00,20,'赫斯曼'),
|
||
-- QT-9 (RFQ-4, 供应商4, 已提交)
|
||
(20,9,9,'触摸屏','7寸TFT，分辨率1024×600，RS485/以太网','台',6,1250.00,7500.00,25,'昆仑通态'),
|
||
(21,9,10,'工业交换机','8口千兆以太网，导轨安装，宽温型','台',4,2175.00,8700.00,25,'菲尼克斯');
|
||
|
||
-- ── 采购单 ───────────────────────────────────────────────────
|
||
INSERT IGNORE INTO biz_purchase_order(po_id,tenant_id,po_no,rfq_id,supplier_id,total_amount,currency,delivery_addr,delivery_date,status,remark,create_by,create_time,update_time) VALUES
|
||
(1,1,'PO-2026-001',1,1,57320.00,'CNY','广东省佛山市顺德区福安德工业园仓库（收货人：张仓管 18899998001）','2026-01-30','closed','对应RFQ-2026-001，已验收入库','admin','2026-01-12 10:00:00','2026-02-05 16:00:00'),
|
||
(2,1,'PO-2026-002',2,2,108500.00,'CNY','广东省佛山市顺德区福安德工业园仓库（收货人：张仓管 18899998001）','2026-02-20','confirmed','对应RFQ-2026-002，已确认发货','admin','2026-01-28 14:00:00','2026-02-01 09:00:00'),
|
||
(3,1,'PO-2026-003',NULL,3,42000.00,'CNY','广东省佛山市顺德区福安德工业园仓库','2026-03-25','draft','备品备件采购，待财务审批','admin','2026-03-05 10:00:00','2026-03-05 10:00:00');
|
||
|
||
-- 采购单明细
|
||
INSERT IGNORE INTO biz_purchase_order_item(po_id,material_id,material_name,spec,unit,quantity,unit_price,total_price) VALUES
|
||
-- PO-1
|
||
(1,3,'PLC控制器','CPU模块，数字量I/O 40点，以太网接口','套',5,3180.00,15900.00),
|
||
(1,2,'变频器','三相380V输入，7.5kW，IP20防护','台',8,4390.00,35120.00),
|
||
(1,10,'电源模块','24VDC/10A，输入85-264VAC，效率≥90%','台',20,315.00,6300.00),
|
||
-- PO-2
|
||
(2,1,'伺服电机','额定功率750W，额定转矩2.4N·m，额定转速3000rpm','台',10,2650.00,26500.00),
|
||
(2,5,'精密导轨','THK型，长度1500mm，宽度35mm','根',20,620.00,12400.00),
|
||
(2,6,'滚珠丝杠','直径32mm，导程10mm，长度1200mm','套',10,1750.00,17500.00),
|
||
(2,7,'减速机','行星减速比1:10，输出扭矩150N·m，法兰安装','台',5,2420.00,12100.00),
|
||
-- PO-3
|
||
(3,9,'气动电磁阀','5/2通，DC24V，G1/4接口，响应时间≤15ms','只',100,75.00,7500.00),
|
||
(3,4,'工业交换机','8口千兆以太网，导轨安装，宽温型','台',8,1800.00,14400.00),
|
||
(3,10,'电源模块','24VDC/10A，输入85-264VAC，效率≥90%','台',40,310.00,12400.00),
|
||
(3,8,'触摸屏','7寸TFT，分辨率1024×600，RS485/以太网','台',4,1175.00,4700.00),
|
||
(3,9,'气动电磁阀','5/2通，DC24V，G1/4接口，响应时间≤15ms','只',40,75.00,3000.00);
|
||
|
||
-- ── 供应商评价 ───────────────────────────────────────────────
|
||
INSERT IGNORE INTO biz_supplier_evaluation(eval_id,tenant_id,po_id,supplier_id,quality_score,delivery_score,service_score,price_score,total_score,comment,evaluator,eval_time) VALUES
|
||
(1,1,1,1,5,4,5,4,4.5,'产品质量优秀，均通过我司IQC检验，送货及时，销售响应迅速，价格略高于市场均价。综合评价优秀，列为A类合格供应商。','admin','2026-02-05 16:30:00'),
|
||
(2,1,2,2,5,3,5,5,4.5,'精密件质量达到图纸要求，部分导轨延期3天到货（供应商提前告知），安装调试支持专业，价格具有竞争力。推荐继续合作。','admin','2026-03-10 10:00:00');
|
||
|
||
-- ── 订单异议 ────────────────────────────────────────────────
|
||
INSERT IGNORE INTO biz_order_objection(objection_id,tenant_id,po_id,supplier_id,reason,status,resolution,create_by,create_time,resolve_time) VALUES
|
||
(1,1,2,2,'PO-2026-002第3项滚珠丝杠（NSK品牌）10套中有2套导程误差超出图纸要求±0.05mm/300mm的规定，实测最大误差达0.09mm，要求退换货或索赔。','resolved','经双方确认，供应商同意退换该2套滚珠丝杠，并承担往返运费。补货已于2026-02-28送达，复检合格。本次异议结案。','采购部-李工','2026-02-22 09:00:00','2026-03-02 16:00:00'),
|
||
(2,1,1,1,'PO-2026-001电源模块（批次号：MW-2025-1128）中发现3台开机自检报警，检测为输出纹波超标，不符合采购规格书要求，要求供应商提供原厂检测报告并换货。','resolved','供应商已提供原厂检测报告，确认为该批次出厂检验遗漏问题。已更换3台合格品并附出厂测试记录，我司复验通过。','采购部-王工','2026-02-10 14:00:00','2026-02-18 11:00:00');
|
||
|
||
-- ── 交易记录 ────────────────────────────────────────────────
|
||
INSERT IGNORE INTO biz_transaction(tx_id,tenant_id,po_id,supplier_id,tx_type,tx_no,amount,currency,description,operator,tx_time,create_time) VALUES
|
||
(1,1,1,1,'PO','PO-2026-001',57320.00,'CNY','创建采购单 PO-2026-001（控制系统采购）','admin','2026-01-12 10:00:00','2026-01-12 10:00:00'),
|
||
(2,1,1,1,'QUOTE','QT-2026-001-HSD',57320.00,'CNY','采纳供应商 深圳市华顺达电子有限公司 报价单 QT-2026-001-HSD','admin','2026-01-11 14:00:00','2026-01-11 14:00:00'),
|
||
(3,1,2,2,'PO','PO-2026-002',108500.00,'CNY','创建采购单 PO-2026-002（精密运动控制组件）','admin','2026-01-28 14:00:00','2026-01-28 14:00:00'),
|
||
(4,1,2,2,'QUOTE','QT-2026-002-BY',108500.00,'CNY','采纳供应商 苏州博远精密设备有限公司 报价单 QT-2026-002-BY','admin','2026-01-27 10:00:00','2026-01-27 10:00:00'),
|
||
(5,1,1,1,'EVAL','EVAL-2026-001',NULL,'CNY','对供应商 深圳市华顺达电子有限公司 完成采购评价，综合得分 4.5分','admin','2026-02-05 16:30:00','2026-02-05 16:30:00'),
|
||
(6,1,2,2,'EVAL','EVAL-2026-002',NULL,'CNY','对供应商 苏州博远精密设备有限公司 完成采购评价，综合得分 4.5分','admin','2026-03-10 10:00:00','2026-03-10 10:00:00'),
|
||
(7,1,NULL,NULL,'RFQ','RFQ-2026-003',NULL,'CNY','发布报价请求 RFQ-2026-003（气动系统元件年度备料采购）','admin','2026-02-20 14:30:00','2026-02-20 14:30:00'),
|
||
(8,1,NULL,NULL,'RFQ','RFQ-2026-004',NULL,'CNY','发布报价请求 RFQ-2026-004（HMI及工控网络设备采购）','admin','2026-03-10 10:30:00','2026-03-10 10:30:00'),
|
||
(9,1,3,3,'PO','PO-2026-003',42000.00,'CNY','创建采购单草稿 PO-2026-003（备品备件采购）','admin','2026-03-05 10:00:00','2026-03-05 10:00:00');
|
||
|
||
SELECT '演示数据导入完成' AS result;
|
||
SELECT CONCAT('供应商: ', COUNT(*), ' 家') AS info FROM biz_supplier WHERE tenant_id=1
|
||
UNION ALL SELECT CONCAT('物料: ', COUNT(*), ' 种') FROM biz_material WHERE tenant_id=1
|
||
UNION ALL SELECT CONCAT('报价请求: ', COUNT(*), ' 个') FROM biz_rfq WHERE tenant_id=1
|
||
UNION ALL SELECT CONCAT('报价单: ', COUNT(*), ' 份') FROM biz_quotation WHERE tenant_id=1
|
||
UNION ALL SELECT CONCAT('采购单: ', COUNT(*), ' 张') FROM biz_purchase_order WHERE tenant_id=1
|
||
UNION ALL SELECT CONCAT('供应商评价: ', COUNT(*), ' 条') FROM biz_supplier_evaluation WHERE tenant_id=1
|
||
UNION ALL SELECT CONCAT('订单异议: ', COUNT(*), ' 件') FROM biz_order_objection WHERE tenant_id=1
|
||
UNION ALL SELECT CONCAT('交易记录: ', COUNT(*), ' 条') FROM biz_transaction WHERE tenant_id=1;
|