\u306e\u3069\u3061\u3089\u304b\u304c\u898b\u3064\u304b\u308c\u3070\u3088\u3044\u306e\u3067\u3059\u3002<\/p>\n\n\n\n
\u524d\u8005\u3067\u3042\u308c\u3070\u3001\u3053\u306e\u624b\u306e\u8907\u96d1\u306a\u30c7\u30fc\u30bf\u69cb\u9020\u306e\u5834\u5408\u306b\u306f\u300c\u8ad6\u7406\u524a\u9664\u3092\u7981\u6b62\uff01\u300d\u3067\u304d\u307e\u3059\u3002\u9006\u306b\u3001\u5f8c\u8005\u306e\u5834\u5408\u3067\u306f\u300c\u7269\u7406\u524a\u9664\u3060\u3051\u3067\u306f\u554f\u984c\u304c\u89e3\u6c7a\u3067\u304d\u306a\u3044\uff01\u300d\u3053\u3068\u3068\u306a\u308a\u8ad6\u7406\u524a\u9664\u3092\u63a1\u7528\u3059\u308b\u7406\u7531\u304c\u898b\u3064\u304b\u308b\u306f\u305a\u3067\u3059\u3002<\/p>\n\n\n\n
\u4eee\u8aac\u3092\u8a3c\u660e\u3059\u308b\u305f\u3081\u306b<\/strong><\/h2>\n\n\n\n1. \u5de5\u5834\u306e\u90e8\u54c1\u53d7\u6ce8\u30b7\u30b9\u30c6\u30e0\u306e\u30c7\u30fc\u30bf\u69cb\u9020\u3092\u8003\u3048\u308b\uff08\u8ad6\u7406\u524a\u9664\u578b\u3068\u7269\u7406\u524a\u9664\u578b\u306e\u4e21\u65b9\uff09
2. \u8907\u96d1\u306a\u30af\u30a8\u30ea\u3042\u308b\u3044\u306f\u624b\u9806\u306e\u30e6\u30fc\u30b9\u30b1\u30fc\u30b9\u3092\u8003\u3048\u308b
3. \u305d\u308c\u305e\u308c\u306e\u30b1\u30fc\u30b9\u3067\u554f\u984c\u3067\u30af\u30a8\u30ea\u3084\u30b3\u30fc\u30c9\u3092\u66f8\u3044\u3066\u307f\u308b
4. \u554f\u984c\u304c\u8d77\u3053\u308b\u304b\u3069\u3046\u304b\u3092\u78ba\u8a8d\u3059\u308b<\/p>\n\n\n\n
\u3068\u3044\u3046\u3053\u3068\u306b\u306a\u308a\u307e\u3059\u3002<\/p>\n\n\n\n
\u3044\u307e\u307e\u3067\u306f\u3001\u3053\u306e\u624b\u306e\u30e6\u30fc\u30b9\u30b1\u30fc\u30b9\u3092\u8003\u3048\u305f\u308a\u3001\u5b9f\u969b\u306b\u30af\u30a8\u30ea\u3084\u30b3\u30fc\u30c9\u3092\u66f8\u3044\u305f\u308a\u3059\u308b\u306e\u304c\u5927\u5909\u3060\u3063\u305f\u306e\u3067\u3059\u304c\u3001\u4eca\u306f AI \u30a8\u30fc\u30b8\u30a7\u30f3\u30c8\u304c\u3042\u308b\u306e\u3067\u7c21\u5358\u3067\u3059\u3002AI \u30a8\u30fc\u30b8\u30a7\u30f3\u30c8\u306b\u300c\u5de5\u5834\u306e\u90e8\u54c1\u53d7\u6ce8\u30b7\u30b9\u30c6\u30e0\u306e\u30c7\u30fc\u30bf\u69cb\u9020\u300d\u3092\u7a81\u3063\u8fbc\u3093\u3067\u304a\u3044\u3066\u3001SQL \u3084\u30b3\u30fc\u30c9\u3092\u66f8\u3044\u3066\u8cb0\u3048\u3070\u3044\u3044\u306e\u3067\u3059\u3002<\/p>\n\n\n\n
\u3067\u3001\u305d\u306e\u30b3\u30fc\u30c9\u304c\u3001\u4eba\u306b\u3068\u3063\u3066\u8aad\u307f\u3084\u3059\u3044\u304b\u2252\u4eba\u306b\u3068\u3063\u3066\u66f8\u304d\u3084\u3059\u3044\u304b\u3001\u3068\u3044\u3046\u3053\u3068\u304c\u308f\u304b\u308c\u3070\u3044\u3044\u3067\u3059\u3088\u306d\u3002
\u9006\u306b\u5c06\u6765\u7684\u306b AI \u30a8\u30fc\u30b8\u30a7\u30f3\u30c8\u306b\u30b3\u30fc\u30c7\u30a3\u30f3\u30b0\u3092\u4f9d\u5b58\u3057\u305f\u5834\u5408\u306b\u3001\u7834\u7dbb\u3059\u308b\u3088\u3046\u306a\u30c7\u30fc\u30bf\u69cb\u9020\u3092\u8a2d\u8a08\u3059\u308b\u306e\u3092\u907f\u3051\u308b\u3068\u3044\u3046\u610f\u5473\u3082\u3042\u308a\u307e\u3059\u3002\u3067\u304d\u308b\u9650\u308a\u3001AI \u304c\u8aa4\u89e3\u3057\u306a\u3044\u3088\u3046\u306a\u30c7\u30fc\u30bf\u69cb\u9020\u304c\u671b\u307e\u308c\u308b\u3067\u3057\u3087\u3046\u3002<\/p>\n\n\n\n
\u30c7\u30fc\u30bf\u69cb\u9020<\/strong><\/h2>\n\n\n\n\n- – \u90e8\u54c1\u30de\u30b9\u30bf\u30fc<\/li>\n\n\n\n
- – \u90e8\u54c1\u88dc\u52a9\u60c5\u5831<\/li>\n\n\n\n
- – \u90e8\u54c1\u306e\u8abf\u9054\u5148<\/li>\n\n\n\n
- – \u53d7\u6ce8\u60c5\u5831<\/li>\n\n\n\n
- – \u90e8\u54c1\u751f\u7523\u7ba1\u7406<\/li>\n\n\n\n
- – \u90e8\u54c1\u306e\u7d44\u307f\u5408\u308f\u305b\u30c6\u30fc\u30d6\u30eb<\/li>\n\n\n\n
- – \u53d7\u6ce8\u7d44\u307f\u3042\u308f\u305b\u756a\u53f7\u7ba1\u7406<\/li>\n\n\n\n
- – \u9867\u5ba2\u767a\u6ce8\u5c65\u6b74<\/li>\n<\/ul>\n\n\n\n
\u8ad6\u7406\u524a\u9664\u578b\u30c7\u30fc\u30bf\u69cb\u9020<\/strong><\/h2>\n\n\n\n-- \u90e8\u54c1\u30de\u30b9\u30bf\u30fc\nCREATE TABLE parts (\n id BIGINT PRIMARY KEY AUTO_INCREMENT,\n part_number VARCHAR(64) UNIQUE NOT NULL,\n part_name VARCHAR(255) NOT NULL,\n description TEXT NULL,\n unit_price DECIMAL(10, 2) NOT NULL,\n lead_time_days INT DEFAULT 0,\n created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n deleted_at TIMESTAMP NULL\n);\n \n-- \u90e8\u54c1\u88dc\u52a9\u60c5\u5831\nCREATE TABLE part_details (\n id BIGINT PRIMARY KEY AUTO_INCREMENT,\n part_id BIGINT NOT NULL,\n weight DECIMAL(10, 4) NULL,\n dimensions VARCHAR(255) NULL,\n material VARCHAR(128) NULL,\n storage_location VARCHAR(128) NULL,\n created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n FOREIGN KEY (part_id) REFERENCES parts(id)\n);\n \n-- \u90e8\u54c1\u306e\u8abf\u9054\u5148\nCREATE TABLE part_suppliers (\n id BIGINT PRIMARY KEY AUTO_INCREMENT,\n part_id BIGINT NOT NULL,\n supplier_id BIGINT NOT NULL,\n supplier_part_number VARCHAR(64) NULL,\n supplier_price DECIMAL(10, 2) NOT NULL,\n lead_time_days INT DEFAULT 0,\n created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n FOREIGN KEY (part_id) REFERENCES parts(id),\n FOREIGN KEY (supplier_id) REFERENCES suppliers(id)\n);\n \n-- \u53d7\u6ce8\u60c5\u5831\nCREATE TABLE purchase_orders (\n id BIGINT PRIMARY KEY AUTO_INCREMENT,\n po_number VARCHAR(64) UNIQUE NOT NULL,\n supplier_id BIGINT NOT NULL,\n order_date DATE NOT NULL,\n expected_delivery_date DATE NOT NULL,\n actual_delivery_date DATE NULL,\n status ENUM('pending', 'ordered', 'received', 'cancelled') DEFAULT 'pending',\n created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n FOREIGN KEY (supplier_id) REFERENCES suppliers(id)\n);\n \n-- \u90e8\u54c1\u751f\u7523\u7ba1\u7406\nCREATE TABLE part_production (\n id BIGINT PRIMARY KEY AUTO_INCREMENT,\n part_id BIGINT NOT NULL,\n production_date DATE NOT NULL,\n quantity_produced INT NOT NULL,\n quality_status ENUM('pass', 'fail', 'rework') DEFAULT 'pass',\n notes TEXT NULL,\n created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n FOREIGN KEY (part_id) REFERENCES parts(id)\n);\n \n-- \u90e8\u54c1\u306e\u7d44\u307f\u5408\u308f\u305b\u30c6\u30fc\u30d6\u30eb\uff08BOM\uff09\nCREATE TABLE bom_items (\n id BIGINT PRIMARY KEY AUTO_INCREMENT,\n bom_id BIGINT NOT NULL,\n part_id BIGINT NOT NULL,\n quantity INT NOT NULL,\n sequence INT NOT NULL,\n created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n FOREIGN KEY (bom_id) REFERENCES bom(id),\n FOREIGN KEY (part_id) REFERENCES parts(id),\n UNIQUE KEY unique_bom_part (bom_id, part_id)\n);\n \n-- BOM \u30de\u30b9\u30bf\u30fc\nCREATE TABLE bom (\n id BIGINT PRIMARY KEY AUTO_INCREMENT,\n product_number VARCHAR(64) UNIQUE NOT NULL,\n product_name VARCHAR(255) NOT NULL,\n version INT DEFAULT 1,\n effective_date DATE NOT NULL,\n status ENUM('active', 'obsolete') DEFAULT 'active',\n created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n deleted_at TIMESTAMP NULL\n);\n \n-- \u53d7\u6ce8\u7d44\u307f\u5408\u308f\u305b\u756a\u53f7\u7ba1\u7406\nCREATE TABLE customer_orders (\n id BIGINT PRIMARY KEY AUTO_INCREMENT,\n order_number VARCHAR(64) UNIQUE NOT NULL,\n customer_id BIGINT NOT NULL,\n bom_id BIGINT NOT NULL,\n order_date DATE NOT NULL,\n delivery_date DATE NOT NULL,\n quantity INT NOT NULL,\n status ENUM('pending', 'in_production', 'completed', 'cancelled') DEFAULT 'pending',\n created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n FOREIGN KEY (customer_id) REFERENCES customers(id),\n FOREIGN KEY (bom_id) REFERENCES bom(id)\n);\n \n-- \u9867\u5ba2\u767a\u6ce8\u5c65\u6b74\nCREATE TABLE order_history (\n id BIGINT PRIMARY KEY AUTO_INCREMENT,\n customer_order_id BIGINT NOT NULL,\n status_changed_to VARCHAR(64) NOT NULL,\n changed_at TIMESTAMP NOT NULL,\n notes TEXT NULL,\n created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n FOREIGN KEY (customer_order_id) REFERENCES customer_orders(id)\n);\n \n-- \u9867\u5ba2\u30de\u30b9\u30bf\u30fc\nCREATE TABLE customers (\n id BIGINT PRIMARY KEY AUTO_INCREMENT,\n customer_code VARCHAR(64) UNIQUE NOT NULL,\n customer_name VARCHAR(255) NOT NULL,\n contact_person VARCHAR(128) NULL,\n phone VARCHAR(20) NULL,\n email VARCHAR(255) NULL,\n created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP\n);\n \n-- \u30b5\u30d7\u30e9\u30a4\u30e4\u30fc\u30de\u30b9\u30bf\u30fc\nCREATE TABLE suppliers (\n id BIGINT PRIMARY KEY AUTO_INCREMENT,\n supplier_code VARCHAR(64) UNIQUE NOT NULL,\n supplier_name VARCHAR(255) NOT NULL,\n contact_person VARCHAR(128) NULL,\n phone VARCHAR(20) NULL,\n email VARCHAR(255) NULL,\n created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP\n<\/pre><\/div>\n\n\n![\"\"](\"https:\/\/www.moonmile.net\/blog\/wp-content\/uploads\/2025\/12\/image-57-1024x527.png\")
<\/a><\/figure>\n\n\n\n\u524d\u56de Copilot \u306b\u66f8\u3044\u3066\u8cb0\u3063\u305f\u30c7\u30fc\u30bf\u69cb\u9020\u306f\u3001deleted_at \u30ab\u30e9\u30e0\u304c\u3042\u308b\u8ad6\u7406\u524a\u9664\u578b\u306e\u3082\u306e\u3067\u3057\u305f\u3002\u3053\u308c\u306f\u3001AI \u304c\u5410\u304d\u51fa\u3057\u305f\u3082\u306e\u3067\u3059\u304b\u3089\u3001\u591a\u5c11\u306e\u4fee\u6b63\u304c\u5fc5\u8981\u304b\u3082\u3057\u308c\u307e\u305b\u3093\u304c\u3001AI \u304c\u30e9\u30a4\u30d6\u30b3\u30fc\u30c7\u30a3\u30f3\u30b0\u3092\u3057\u3084\u3059\u3044\u30c7\u30fc\u30bf\u69cb\u9020\u306b\u306a\u3063\u3066\u3044\u308b\u7b48\u3067\u3059\u3002<\/p>\n\n\n\n
\u3053\u308c\u306b\u547d\u984c\u3092\u4e0e\u3048\u3066\u3044\u304d\u307e\u3059\u3002<\/p>\n\n\n\n
\u7269\u7406\u524a\u9664\u578b\u30c7\u30fc\u30bf\u69cb\u9020<\/strong><\/h2>\n\n\n\nparts, bom \u30c6\u30fc\u30d6\u30eb\u306b deleted_at \u30ab\u30e9\u30e0\u304c\u5165\u3063\u3066\u3044\u308b\u306e\u3067\u3001\u3053\u308c\u3092\u53d6\u308a\u9664\u304d\u307e\u3059\u3002
\u3055\u3089\u306b\u3001parts \u3084 bom \u30c6\u30fc\u30d6\u30eb\u306e\u904e\u53bb\u306e\u3082\u306e\u304c\u53c2\u7167\u3067\u304d\u308b\u3088\u3046\u306b\u3001\u5c65\u6b74\u7528\u306e\u30c6\u30fc\u30d6\u30eb\u3092\u4f5c\u3063\u3066\u8cb0\u3044\u307e\u3059\u3002<\/p>\n\n\n
\n-- \u90e8\u54c1\u30de\u30b9\u30bf\u30fc\uff08\u7269\u7406\u524a\u9664\uff09\nCREATE TABLE parts (\n id BIGINT PRIMARY KEY AUTO_INCREMENT,\n part_number VARCHAR(64) UNIQUE NOT NULL,\n part_name VARCHAR(255) NOT NULL,\n description TEXT NULL,\n unit_price DECIMAL(10, 2) NOT NULL,\n lead_time_days INT DEFAULT 0,\n created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP\n);\n\n-- \u90e8\u54c1\u30de\u30b9\u30bf\u30fc\u5c65\u6b74\nCREATE TABLE parts_history (\n history_id BIGINT PRIMARY KEY AUTO_INCREMENT,\n part_id BIGINT NOT NULL,\n part_number VARCHAR(64) NOT NULL,\n part_name VARCHAR(255) NOT NULL,\n description TEXT NULL,\n unit_price DECIMAL(10, 2) NOT NULL,\n lead_time_days INT DEFAULT 0,\n version INT NOT NULL,\n effective_from TIMESTAMP NOT NULL,\n effective_to TIMESTAMP NULL,\n created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n FOREIGN KEY (part_id) REFERENCES parts(id)\n);\n\n-- BOM \u30de\u30b9\u30bf\u30fc\uff08\u7269\u7406\u524a\u9664\uff09\nCREATE TABLE bom (\n id BIGINT PRIMARY KEY AUTO_INCREMENT,\n product_number VARCHAR(64) UNIQUE NOT NULL,\n product_name VARCHAR(255) NOT NULL,\n version INT DEFAULT 1,\n effective_date DATE NOT NULL,\n status ENUM('active', 'obsolete') DEFAULT 'active',\n created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP\n);\n\n-- BOM \u30de\u30b9\u30bf\u30fc\u5c65\u6b74\nCREATE TABLE bom_history (\n history_id BIGINT PRIMARY KEY AUTO_INCREMENT,\n bom_id BIGINT NOT NULL,\n product_number VARCHAR(64) NOT NULL,\n product_name VARCHAR(255) NOT NULL,\n version INT NOT NULL,\n effective_date DATE NOT NULL,\n status ENUM('active', 'obsolete') DEFAULT 'active',\n effective_from TIMESTAMP NOT NULL,\n effective_to TIMESTAMP NULL,\n created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n FOREIGN KEY (bom_id) REFERENCES bom(id)\n);\n\n-- \u90e8\u54c1\u88dc\u52a9\u60c5\u5831\uff08\u7269\u7406\u524a\u9664\uff09\nCREATE TABLE part_details (\n id BIGINT PRIMARY KEY AUTO_INCREMENT,\n part_id BIGINT NOT NULL,\n weight DECIMAL(10, 4) NULL,\n dimensions VARCHAR(255) NULL,\n material VARCHAR(128) NULL,\n storage_location VARCHAR(128) NULL,\n created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n FOREIGN KEY (part_id) REFERENCES parts(id)\n);\n\n-- \u90e8\u54c1\u306e\u8abf\u9054\u5148\uff08\u7269\u7406\u524a\u9664\uff09\nCREATE TABLE part_suppliers (\n id BIGINT PRIMARY KEY AUTO_INCREMENT,\n part_id BIGINT NOT NULL,\n supplier_id BIGINT NOT NULL,\n supplier_part_number VARCHAR(64) NULL,\n supplier_price DECIMAL(10, 2) NOT NULL,\n lead_time_days INT DEFAULT 0,\n created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n FOREIGN KEY (part_id) REFERENCES parts(id),\n FOREIGN KEY (supplier_id) REFERENCES suppliers(id)\n);\n\n-- \u53d7\u6ce8\u60c5\u5831\uff08\u7269\u7406\u524a\u9664\uff09\nCREATE TABLE purchase_orders (\n id BIGINT PRIMARY KEY AUTO_INCREMENT,\n po_number VARCHAR(64) UNIQUE NOT NULL,\n supplier_id BIGINT NOT NULL,\n order_date DATE NOT NULL,\n expected_delivery_date DATE NOT NULL,\n actual_delivery_date DATE NULL,\n status ENUM('pending', 'ordered', 'received', 'cancelled') DEFAULT 'pending',\n created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n FOREIGN KEY (supplier_id) REFERENCES suppliers(id)\n);\n\n-- \u90e8\u54c1\u751f\u7523\u7ba1\u7406\uff08\u7269\u7406\u524a\u9664\uff09\nCREATE TABLE part_production (\n id BIGINT PRIMARY KEY AUTO_INCREMENT,\n part_id BIGINT NOT NULL,\n production_date DATE NOT NULL,\n quantity_produced INT NOT NULL,\n quality_status ENUM('pass', 'fail', 'rework') DEFAULT 'pass',\n notes TEXT NULL,\n created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n FOREIGN KEY (part_id) REFERENCES parts(id)\n);\n\n-- \u90e8\u54c1\u306e\u7d44\u307f\u5408\u308f\u305b\u30c6\u30fc\u30d6\u30eb\uff08BOM\uff09\uff08\u7269\u7406\u524a\u9664\uff09\nCREATE TABLE bom_items (\n id BIGINT PRIMARY KEY AUTO_INCREMENT,\n bom_id BIGINT NOT NULL,\n part_id BIGINT NOT NULL,\n quantity INT NOT NULL,\n sequence INT NOT NULL,\n created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n FOREIGN KEY (bom_id) REFERENCES bom(id),\n FOREIGN KEY (part_id) REFERENCES parts(id),\n UNIQUE KEY unique_bom_part (bom_id, part_id)\n);\n\n-- \u53d7\u6ce8\u7d44\u307f\u5408\u308f\u305b\u756a\u53f7\u7ba1\u7406\uff08\u7269\u7406\u524a\u9664\uff09\nCREATE TABLE customer_orders (\n id BIGINT PRIMARY KEY AUTO_INCREMENT,\n order_number VARCHAR(64) UNIQUE NOT NULL,\n customer_id BIGINT NOT NULL,\n bom_id BIGINT NOT NULL,\n order_date DATE NOT NULL,\n delivery_date DATE NOT NULL,\n quantity INT NOT NULL,\n status ENUM('pending', 'in_production', 'completed', 'cancelled') DEFAULT 'pending',\n created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n FOREIGN KEY (customer_id) REFERENCES customers(id),\n FOREIGN KEY (bom_id) REFERENCES bom(id)\n);\n\n-- \u9867\u5ba2\u767a\u6ce8\u5c65\u6b74\uff08\u7269\u7406\u524a\u9664\uff09\nCREATE TABLE order_history (\n id BIGINT PRIMARY KEY AUTO_INCREMENT,\n customer_order_id BIGINT NOT NULL,\n status_changed_to VARCHAR(64) NOT NULL,\n changed_at TIMESTAMP NOT NULL,\n notes TEXT NULL,\n created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n FOREIGN KEY (customer_order_id) REFERENCES customer_orders(id)\n);\n\n-- \u9867\u5ba2\u30de\u30b9\u30bf\u30fc\uff08\u7269\u7406\u524a\u9664\uff09\nCREATE TABLE customers (\n id BIGINT PRIMARY KEY AUTO_INCREMENT,\n customer_code VARCHAR(64) UNIQUE NOT NULL,\n customer_name VARCHAR(255) NOT NULL,\n contact_person VARCHAR(128) NULL,\n phone VARCHAR(20) NULL,\n email VARCHAR(255) NULL,\n created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP\n);\n\n-- \u30b5\u30d7\u30e9\u30a4\u30e4\u30fc\u30de\u30b9\u30bf\u30fc\uff08\u7269\u7406\u524a\u9664\uff09\nCREATE TABLE suppliers (\n id BIGINT PRIMARY KEY AUTO_INCREMENT,\n supplier_code VARCHAR(64) UNIQUE NOT NULL,\n supplier_name VARCHAR(255) NOT NULL,\n contact_person VARCHAR(128) NULL,\n phone VARCHAR(20) NULL,\n email VARCHAR(255) NULL,\n created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP\n);\n\n<\/pre><\/div>\n\n\n![\"\"](\"https:\/\/www.moonmile.net\/blog\/wp-content\/uploads\/2025\/12\/image-58-1024x408.png\")
<\/a><\/figure>\n\n\n\nER \u56f3\u3092\u898b\u6bd4\u3079\u308b\u3068<\/p>\n\n\n\n
\n- PARTS_HISTORY<\/li>\n\n\n\n
- BOM_HISTORY<\/li>\n<\/ul>\n\n\n\n
\u3068\u3044\u3046\u5c65\u6b74\u30c6\u30fc\u30d6\u30eb\u304c\u8ffd\u52a0\u3055\u308c\u3066\u3044\u308b\u3053\u3068\u304c\u5206\u304b\u308a\u307e\u3059\u3002<\/p>\n\n\n\n
\u30e6\u30fc\u30b9\u30b1\u30fc\u30b9\u306e\u691c\u8a0e<\/strong><\/h2>\n\n\n\n\u5177\u4f53\u7684\u306b\u30e6\u30fc\u30b9\u30b1\u30fc\u30b9\u3092\u8003\u3048\u307e\u3057\u3087\u3046\u3002
\u5c65\u6b74\u304c\u5fc5\u8981\u306a\u306e\u306f\u3001\u90e8\u54c1\u30c6\u30fc\u30d6\u30eb\uff08parts\uff09\u3068\u90e8\u54c1\u306e\u7d44\u307f\u5408\u308f\u305b\u30c6\u30fc\u30d6\u30eb\uff08bom\uff09\u3067\u3059\u3002
\u3053\u306e2\u3064\u306e\u30c6\u30fc\u30d6\u30eb\u3067\u3001\u904e\u53bb\u306b\u3055\u304b\u306e\u307c\u3063\u3066\u30c7\u30fc\u30bf\u3092\u53c2\u7167\u3059\u308b\u30d1\u30bf\u30fc\u30f3\u3092\u8003\u3048\u307e\u3059\u3002<\/p>\n\n\n\n
\u30b1\u30fc\u30b91<\/strong><\/h2>\n\n\n\n\u9867\u5ba2\u304b\u3089\u53e4\u3044\u767a\u6ce8\u66f8\u306b\u57fa\u3065\u3044\u3066\u90e8\u54c1\u306e\u767a\u6ce8\u3092\u53d7\u3051\u305f\u3002\u6b8b\u5ff5\u306a\u304c\u3089\u904e\u53bb\u306e\u767a\u6ce8\u306e\u305f\u3081\u306b\u3001\u767a\u6ce8\u66f8\u306e\u4e2d\u306b\u3042\u308b\u3001\u90e8\u54c1\u756a\u53f7\u306f\u73fe\u5728\u6271\u3063\u3066\u3044\u306a\u3044\u3002\u3053\u306e\u90e8\u54c1\u756a\u53f7\u3092\u65b0\u3057\u304f\u3057\u3066\u898b\u7a4d\u3082\u308a\u66f8\u3092\u4f5c\u6210\u3057\u305f\u3044\u3002
\u3053\u306e\u3068\u304d\u306b\u3001\u904e\u53bb\u306e\u767a\u6ce8\u66f8\u3068\u5408\u8a08\u91d1\u984d\u3092\u7167\u5408\u3057\u3066\u3001\u5dee\u5206\u3092\u8a08\u7b97\u3057\u3066\u304a\u304d\u305f\u3044\u3002
\u90e8\u54c1A,B,C \u304c\u904e\u53bb\u306e\u767a\u6ce8\u66f8\u306b\u3042\u308a\u3001\u90e8\u54c1B \u304c\u5ec3\u76e4\u306b\u306a\u3063\u3066\u3044\u308b\u305f\u3081\u3001\u90e8\u54c1D \u306b\u7f6e\u304d\u5909\u3048\u305f\u3044\u3002<\/p>\n\n\n\n
\u3053\u306e\u3068\u304d\u306b\u3001\u8ad6\u7406\u524a\u9664\u578b\u30c7\u30fc\u30bf\u69cb\u9020\u3092\u4f7f\u3063\u305f\u3068\u304d\u306e SQL \u30af\u30a8\u30ea\u3001\u3042\u308b\u3044\u306f\u3001PHP\/Laravel \u306b\u3088\u308b\u691c\u7d22\u7d50\u679c\u3092\u793a\u3057\u3066\u307b\u3057\u3044\u3002<\/p>\n\n\n\n
\u8ad6\u7406\u524a\u9664\u306e\u30c7\u30fc\u30bf\u69cb\u9020\u306e\u5834\u5408<\/strong><\/h3>\n\n\n\nLaravel\/Eloquent \u306e\u30b3\u30fc\u30c9\u306e\u5834\u5408\u306f\u3001\u8ad6\u7406\u524a\u9664\u3092 withTrashed \u30e1\u30bd\u30c3\u30c9\u3092\u4f7f\u3063\u3066\u524a\u9664\u6e08\u307f\u30c7\u30fc\u30bf\u3082\u53d6\u5f97\u3067\u304d\u308b\u3088\u3046\u306b\u3057\u307e\u3059\u3002\u666e\u6bb5\u306e\u691c\u7d22\u306e\u3068\u304d\u306f\u3001where \u3092\u4f7f\u3063\u3066 deleted_at is not null \u306e\u6761\u4ef6\u304c\u81ea\u52d5\u7684\u306b\u8ffd\u52a0\u306b\u306a\u308b\u306e\u3067\u3001\u3053\u3053\u3067\u6df7\u4e71\u3059\u308b\u3053\u3068\u306f\u3042\u308a\u307e\u305b\u3093\u3002<\/p>\n\n\n
\nnamespace App\\Services;\n\nuse App\\Models\\CustomerOrder;\nuse App\\Models\\Bom;\nuse App\\Models\\BomItem;\nuse App\\Models\\Part;\nuse Illuminate\\Support\\Collection;\n\nclass QuoteService\n{\n \/**\n * @param string $orderNumber \u904e\u53bb\u306e\u767a\u6ce8\u756a\u53f7\n * @param array<string,string> $replacements ['old_part_number' => 'new_part_number']\n *\/\n public function buildQuote(string $orderNumber, array $replacements): array\n {\n \/\/ \u904e\u53bb\u306e\u767a\u6ce8\u3092\u53d6\u5f97\uff08\u524a\u9664\u6e08\u307f BOM \u3082\u62fe\u3046\u305f\u3081 withTrashed\uff09\n $order = CustomerOrder::where('order_number', $orderNumber)->firstOrFail();\n $bom = Bom::withTrashed()->findOrFail($order->bom_id);\n\n \/\/ BOM \u660e\u7d30\uff08\u524a\u9664\u6e08\u307f\u30d1\u30fc\u30c4\u3082\u62fe\u3046\u305f\u3081 withTrashed\uff09\n $items = BomItem::where('bom_id', $bom->id)->get()->map(function (BomItem $item) {\n $item->part = Part::withTrashed()->findOrFail($item->part_id);\n return $item;\n });\n\n $lines = [];\n $oldTotal = 0;\n $newTotal = 0;\n\n foreach ($items as $item) {\n $oldPart = $item->part;\n $qty = $item->quantity;\n $newPart = $oldPart;\n\n \/\/ \u5ec3\u76e4\u54c1\u3092\u4ee3\u66ff\u54c1\u306b\u5dee\u3057\u66ff\u3048\n if (isset($replacements[$oldPart->part_number])) {\n $newPart = Part::where('part_number', $replacements[$oldPart->part_number])->firstOrFail();\n }\n\n $lines[] = [\n 'old_part_number' => $oldPart->part_number,\n 'new_part_number' => $newPart->part_number,\n 'quantity' => $qty,\n 'old_price' => $oldPart->unit_price,\n 'new_price' => $newPart->unit_price,\n 'old_subtotal' => $oldPart->unit_price * $qty,\n 'new_subtotal' => $newPart->unit_price * $qty,\n 'replaced' => $oldPart->id !== $newPart->id,\n ];\n\n $oldTotal += $oldPart->unit_price * $qty;\n $newTotal += $newPart->unit_price * $qty;\n }\n\n return [\n 'order_number' => $order->order_number,\n 'bom_id' => $bom->id,\n 'lines' => $lines,\n 'old_total' => $oldTotal,\n 'new_total' => $newTotal,\n 'diff' => $newTotal - $oldTotal,\n ];\n }\n}\n<\/pre><\/div>\n\n\n\u307e\u3042\u3001\u3053\u308c\u306f softDelete \u306e\u6a5f\u80fd\u304c Laravel\/Eloquent \u306b\u3042\u308b\u306e\u3067\u7c21\u5358\u306b\u66f8\u3051\u308b\u3068\u3044\u3046\u88cf\u6280\u307f\u305f\u3044\u306a\u3082\u306e\u306a\u306e\u3067\u3001\u540c\u3058\u30d1\u30bf\u30fc\u30f3\u3092 C#\/LINQ \u3067\u3084\u3063\u3066\u307f\u307e\u3057\u3087\u3046\u3002<\/p>\n\n\n\n
\u3053\u306e\u30d1\u30bf\u30fc\u30f3\u306f C# \u306e EF Core \u3092\u66ab\u304f\u3084\u3063\u3066\u3044\u306a\u304b\u3063\u305f\u306e\u3067\u3001\u6c17\u304c\u4ed8\u304b\u306a\u304b\u3063\u305f\u306e\u3067\u3059\u304c\u3001IgnoreQueryFilters \u30e1\u30bd\u30c3\u30c9\u304c\u3042\u308a\u307e\u3059\u306d\u3002
deleted_at \u30ab\u30e9\u30e0\u3092\u9664\u5916\u3059\u308b\u305f\u3081\u306b\u3042\u3089\u304b\u3058\u3081\u30b0\u30ed\u30fc\u30d0\u30eb\u30af\u30a8\u30ea\u30d5\u30a3\u30eb\u30bf\u30fc\u3092\u4ed8\u3051\u3066\u304a\u3051\u3070\u3088\u3044\u305d\u3046\u3067\u3059\u3002<\/p>\n\n\n
\nusing Microsoft.EntityFrameworkCore;\n\npublic class QuoteService\n{\n private readonly AppDbContext _db;\n\n public QuoteService(AppDbContext db) => _db = db;\n\n \/\/ replacements: \u53e4\u3044\u54c1\u756a\u2192\u65b0\u3057\u3044\u54c1\u756a\u306e\u30de\u30c3\u30d7\n public async Task<QuoteResult> BuildQuoteAsync(string orderNumber, Dictionary<string, string> replacements)\n {\n var order = await _db.CustomerOrders\n .AsNoTracking()\n .FirstOrDefaultAsync(o => o.OrderNumber == orderNumber)\n ?? throw new InvalidOperationException("order not found");\n\n var bom = await _db.Boms\n .IgnoreQueryFilters() \/\/ deleted_at \u3092\u7121\u8996\u3057\u3066\u53d6\u5f97\n .AsNoTracking()\n .FirstOrDefaultAsync(b => b.Id == order.BomId)\n ?? throw new InvalidOperationException("bom not found");\n\n var items = await _db.BomItems\n .Where(i => i.BomId == bom.Id)\n .AsNoTracking()\n .ToListAsync();\n\n var lines = new List<QuoteLine>();\n decimal oldTotal = 0;\n decimal newTotal = 0;\n\n foreach (var item in items)\n {\n var oldPart = await _db.Parts\n .IgnoreQueryFilters()\n .AsNoTracking()\n .FirstOrDefaultAsync(p => p.Id == item.PartId)\n ?? throw new InvalidOperationException("part not found");\n\n var newPart = oldPart;\n if (replacements.TryGetValue(oldPart.PartNumber, out var newPn))\n {\n newPart = await _db.Parts\n .AsNoTracking()\n .FirstOrDefaultAsync(p => p.PartNumber == newPn)\n ?? throw new InvalidOperationException("replacement not found");\n }\n\n var qty = item.Quantity;\n var oldSub = oldPart.UnitPrice * qty;\n var newSub = newPart.UnitPrice * qty;\n\n lines.Add(new QuoteLine\n {\n OldPartNumber = oldPart.PartNumber,\n NewPartNumber = newPart.PartNumber,\n Quantity = qty,\n OldPrice = oldPart.UnitPrice,\n NewPrice = newPart.UnitPrice,\n OldSubtotal = oldSub,\n NewSubtotal = newSub,\n Replaced = oldPart.Id != newPart.Id\n });\n\n oldTotal += oldSub;\n newTotal += newSub;\n }\n\n return new QuoteResult\n {\n OrderNumber = order.OrderNumber,\n BomId = bom.Id,\n Lines = lines,\n OldTotal = oldTotal,\n NewTotal = newTotal,\n Diff = newTotal - oldTotal\n };\n }\n}\n\npublic record QuoteResult\n{\n public string OrderNumber { get; init; }\n public long BomId { get; init; }\n public List<QuoteLine> Lines { get; init; } = new();\n public decimal OldTotal { get; init; }\n public decimal NewTotal { get; init; }\n public decimal Diff { get; init; }\n}\n\npublic record QuoteLine\n{\n public string OldPartNumber { get; init; }\n public string NewPartNumber { get; init; }\n public int Quantity { get; init; }\n public decimal OldPrice { get; init; }\n public decimal NewPrice { get; init; }\n public decimal OldSubtotal { get; init; }\n public decimal NewSubtotal { get; init; }\n public bool Replaced { get; init; }\n}\n<\/pre><\/div>\n\n\n\u30b3\u30fc\u30c9\u30d5\u30a1\u30fc\u30b9\u30c8\u306e\u3068\u304d\u306b\u3001OnModelCreating \u30e1\u30bd\u30c3\u30c9\u3067\u3001HasQueryFilter \u3092\u4f7f\u3063\u3066\u30b0\u30ed\u30fc\u30d0\u30eb\u30af\u30a8\u30ea\u30d5\u30a3\u30eb\u30bf\u30fc\u3092\u8a2d\u5b9a\u3057\u3066\u304a\u304d\u307e\u3059\u3002<\/p>\n\n\n
\nprotected override void OnModelCreating(ModelBuilder modelBuilder)\n{\n base.OnModelCreating(modelBuilder);\n\n \/\/ Soft-delete \u7528\u30d5\u30a3\u30eb\u30bf\u30fc\uff08\u901a\u5e38\u306f deleted_at \u304c NULL \u306e\u307f\u53d6\u5f97\uff09\n modelBuilder.Entity<Bom>()\n .HasQueryFilter(b => b.DeletedAt == null);\n\n \/\/ \u4ed6\u306e\u30a8\u30f3\u30c6\u30a3\u30c6\u30a3\u306b\u3082\u5fc5\u8981\u306a\u3089\u540c\u69d8\u306b\n \/\/ modelBuilder.Entity<Part>().HasQueryFilter(p => p.DeletedAt == null);\n}\n\n<\/pre><\/div>\n\n\n\u7269\u7406\u524a\u9664\u306e\u30c7\u30fc\u30bf\u69cb\u9020\u306e\u5834\u5408<\/strong><\/h3>\n\n\n\n\u4eca\u5ea6\u306f\u3001\u7269\u7406\u524a\u9664\u306e\u30d1\u30bf\u30fc\u30f3\u3067\u3059\u3002<\/p>\n\n\n\n
\u53e4\u3044 parts, bom \u30c6\u30fc\u30d6\u30eb\u306e\u30c7\u30fc\u30bf\u306f\u524a\u9664\u3055\u308c\u3066\u3044\u308b\u53ef\u80fd\u6027\u304c\u3042\u308b\u306e\u3067\u3001\u5c65\u6b74\u30c6\u30fc\u30d6\u30eb\u304b\u3089\u6700\u65b0\u306e\u3082\u306e\u3092\u53d6\u5f97\u3059\u308b\u5fc5\u8981\u304c\u3042\u308a\u307e\u3059\u3002findPartWithHistory \u3084 findPartByNumberWithHistory \u3068\u3044\u3063\u305f\u30d8\u30eb\u30d1\u30fc\u30e1\u30bd\u30c3\u30c9\u3092\u7528\u610f\u3057\u3066\u3001\u5c65\u6b74\u30c6\u30fc\u30d6\u30eb\u304b\u3089\u6700\u65b0\u306e\u30c7\u30fc\u30bf\u3092\u53d6\u5f97\u3059\u308b\u3088\u3046\u306b\u3057\u307e\u3059\u3002<\/p>\n\n\n
\n<?php\n\nnamespace App\\Services;\n\nuse App\\Models\\CustomerOrder;\nuse App\\Models\\Bom;\nuse App\\Models\\BomItem;\nuse App\\Models\\Part;\nuse App\\Models\\PartsHistory;\nuse App\\Models\\BomHistory;\nuse Illuminate\\Support\\Facades\\DB;\n\nclass QuoteService\n{\n \/**\n * @param string $orderNumber \u904e\u53bb\u306e\u767a\u6ce8\u756a\u53f7\n * @param array<string,string> $replacements ['old_part_number' => 'new_part_number']\n *\/\n public function buildQuote(string $orderNumber, array $replacements): array\n {\n return DB::transaction(function () use ($orderNumber, $replacements) {\n $order = CustomerOrder::where('order_number', $orderNumber)->firstOrFail();\n\n \/\/ BOM \u3092\u73fe\u884c\u30c6\u30fc\u30d6\u30eb\u512a\u5148\u3067\u53d6\u5f97\u3001\u306a\u3051\u308c\u3070\u5c65\u6b74\u304b\u3089\u6700\u65b0\u3092\u53d6\u5f97\n $bom = Bom::find($order->bom_id)\n ?? BomHistory::where('bom_id', $order->bom_id)\n ->orderByDesc('effective_from')\n ->firstOrFail();\n\n \/\/ BOM\u660e\u7d30\u306f\u73fe\u884c\u3092\u60f3\u5b9a\uff08\u5c65\u6b74\u304c\u5fc5\u8981\u306a\u3089\u5225\u9014 bom_items_history \u3092\u7528\u610f\u3057\u3066\u540c\u69d8\u306b\u53d6\u5f97\uff09\n $items = BomItem::where('bom_id', $order->bom_id)->get();\n\n $lines = [];\n $oldTotal = 0;\n $newTotal = 0;\n\n foreach ($items as $item) {\n $oldPart = $this->findPartWithHistory($item->part_id);\n $qty = $item->quantity;\n\n $newPart = $oldPart;\n if (isset($replacements[$oldPart['part_number']])) {\n $newPart = $this->findPartByNumberWithHistory($replacements[$oldPart['part_number']]);\n }\n\n $oldSub = $oldPart['unit_price'] * $qty;\n $newSub = $newPart['unit_price'] * $qty;\n\n $lines[] = [\n 'old_part_number' => $oldPart['part_number'],\n 'new_part_number' => $newPart['part_number'],\n 'quantity' => $qty,\n 'old_price' => $oldPart['unit_price'],\n 'new_price' => $newPart['unit_price'],\n 'old_subtotal' => $oldSub,\n 'new_subtotal' => $newSub,\n 'replaced' => $oldPart['id'] !== $newPart['id'] || $oldPart['part_number'] !== $newPart['part_number'],\n ];\n\n $oldTotal += $oldSub;\n $newTotal += $newSub;\n }\n\n return [\n 'order_number' => $order->order_number,\n 'bom_id' => $order->bom_id,\n 'lines' => $lines,\n 'old_total' => $oldTotal,\n 'new_total' => $newTotal,\n 'diff' => $newTotal - $oldTotal,\n ];\n });\n }\n\n private function findPartWithHistory(int $partId): array\n {\n $part = Part::find($partId);\n if ($part) {\n return $part->toArray();\n }\n\n $hist = PartsHistory::where('part_id', $partId)\n ->orderByDesc('effective_from')\n ->firstOrFail();\n\n return [\n 'id' => $hist->part_id,\n 'part_number' => $hist->part_number,\n 'unit_price' => $hist->unit_price,\n ];\n }\n\n private function findPartByNumberWithHistory(string $partNumber): array\n {\n $part = Part::where('part_number', $partNumber)->first();\n if ($part) {\n return $part->toArray();\n }\n\n $hist = PartsHistory::where('part_number', $partNumber)\n ->orderByDesc('effective_from')\n ->firstOrFail();\n\n return [\n 'id' => $hist->part_id,\n 'part_number' => $hist->part_number,\n 'unit_price' => $hist->unit_price,\n ];\n }\n}\n<\/pre><\/div>\n\n\n\u666e\u6bb5\u306f where \u30e1\u30bd\u30c3\u30c9\u3092\u4f7f\u3063\u3066 parts, bom \u30c6\u30fc\u30d6\u30eb\u304b\u3089\u691c\u7d22\u3059\u308b\u306e\u3067\u3001\u7279\u306b\u554f\u984c\u306f\u3067\u307e\u305b\u3093\u3002\u904e\u53bb\u90e8\u54c1\u3060\u3063\u305f\u3068\u304d\u306b\u3001findPartWithHistory \u3084 findPartByNumberWithHistory \u30e1\u30bd\u30c3\u30c9\u3092\u4f7f\u3063\u3066\u5c65\u6b74\u30c6\u30fc\u30d6\u30eb\u304b\u3089\u6700\u65b0\u306e\u30c7\u30fc\u30bf\u3092\u53d6\u5f97\u3059\u308b\u3088\u3046\u306b\u3057\u3066\u3044\u307e\u3059\u3002
\u3053\u306e\u30b1\u30fc\u30b9\u306e\u5834\u5408\u306f\u300c\u904e\u53bb\u304b\u3089\u8aad\u307f\u53d6\u308b\u53ef\u80fd\u6027\u304c\u3042\u308b\u300d\u3053\u3068\u304c\u524d\u63d0\u306b\u306a\u3063\u3066\u3044\u308b\u306e\u3067\u3001withTrashed \u3092\u4f7f\u3046\u304b findPartWithHistory \u30e1\u30bd\u30c3\u30c9\u306e\u3088\u3046\u306a\u30d8\u30eb\u30d1\u30fc\u30e1\u30bd\u30c3\u30c9\u3092\u4f7f\u3046\u304b\u3068\u3044\u3046\u5224\u65ad\u306f\u30b3\u30fc\u30c7\u30a3\u30f3\u30b0\u6642\u306b\u5224\u660e\u3057\u305d\u3046\u3067\u3059\u3002<\/p>\n\n\n\n
\u540c\u3058\u30d1\u30bf\u30fc\u30f3\u3092 C#\/LINQ \u3067\u3084\u3063\u3066\u307f\u307e\u3057\u3087\u3046\u3002
LINQ \u306e\u5834\u5408\u306f\u3001IgnoreQueryFilters \u30e1\u30bd\u30c3\u30c9\u306f\u4f7f\u308f\u305a\u306b\u3001\u5c65\u6b74\u30c6\u30fc\u30d6\u30eb\uff08BomHistory\u3001PartsHistory\uff09\u3078\u306e\u30a2\u30af\u30bb\u30b9\u3067\u904e\u53bb\u30c7\u30fc\u30bf\u306e\u7167\u5408\u3092\u884c\u3044\u307e\u3059\u3002<\/p>\n\n\n
\n\/\/ \u7269\u7406\u524a\u9664\uff0b\u5c65\u6b74\u30c6\u30fc\u30d6\u30eb\u3067\u30b1\u30fc\u30b91\u3092\u51e6\u7406\u3059\u308b C#\/EF Core \u30b5\u30fc\u30d3\u30b9\u4f8b\nusing Microsoft.EntityFrameworkCore;\n\npublic class QuoteService\n{\n private readonly AppDbContext _db;\n public QuoteService(AppDbContext db) => _db = db;\n\n \/\/ replacements: \u53e4\u3044\u54c1\u756a\u2192\u65b0\u3057\u3044\u54c1\u756a\n public async Task<QuoteResult> BuildQuoteAsync(string orderNumber, Dictionary<string, string> replacements)\n {\n var order = await _db.CustomerOrders\n .AsNoTracking()\n .FirstOrDefaultAsync(o => o.OrderNumber == orderNumber)\n ?? throw new InvalidOperationException("order not found");\n\n \/\/ BOM \u306f\u73fe\u884c\u512a\u5148\u3001\u7121\u3051\u308c\u3070\u5c65\u6b74\u304b\u3089\u6700\u65b0\u3092\u53d6\u5f97\n var bom = await _db.Boms.AsNoTracking()\n .FirstOrDefaultAsync(b => b.Id == order.BomId)\n ?? await _db.BomHistory.AsNoTracking()\n .Where(h => h.BomId == order.BomId)\n .OrderByDescending(h => h.EffectiveFrom)\n .Select(h => new Bom { Id = h.BomId, ProductNumber = h.ProductNumber, ProductName = h.ProductName })\n .FirstOrDefaultAsync()\n ?? throw new InvalidOperationException("bom not found");\n\n \/\/ BOM \u660e\u7d30\u306f\u73fe\u884c\u3092\u60f3\u5b9a\uff08\u5c65\u6b74\u304c\u5fc5\u8981\u306a\u3089 bom_items_history \u3082\u540c\u69d8\u306b\u30d5\u30a9\u30fc\u30eb\u30d0\u30c3\u30af\uff09\n var items = await _db.BomItems\n .Where(i => i.BomId == order.BomId)\n .AsNoTracking()\n .ToListAsync();\n\n var lines = new List<QuoteLine>();\n decimal oldTotal = 0, newTotal = 0;\n\n foreach (var item in items)\n {\n var oldPart = await FindPartWithHistoryAsync(item.PartId);\n var qty = item.Quantity;\n\n var newPart = oldPart;\n if (replacements.TryGetValue(oldPart.PartNumber, out var newPn))\n {\n newPart = await FindPartByNumberWithHistoryAsync(newPn);\n }\n\n var oldSub = oldPart.UnitPrice * qty;\n var newSub = newPart.UnitPrice * qty;\n\n lines.Add(new QuoteLine\n {\n OldPartNumber = oldPart.PartNumber,\n NewPartNumber = newPart.PartNumber,\n Quantity = qty,\n OldPrice = oldPart.UnitPrice,\n NewPrice = newPart.UnitPrice,\n OldSubtotal = oldSub,\n NewSubtotal = newSub,\n Replaced = oldPart.Id != newPart.Id || oldPart.PartNumber != newPart.PartNumber\n });\n\n oldTotal += oldSub;\n newTotal += newSub;\n }\n\n return new QuoteResult\n {\n OrderNumber = order.OrderNumber,\n BomId = bom.Id,\n Lines = lines,\n OldTotal = oldTotal,\n NewTotal = newTotal,\n Diff = newTotal - oldTotal\n };\n }\n\n private async Task<PartDto> FindPartWithHistoryAsync(long partId)\n {\n var part = await _db.Parts.AsNoTracking().FirstOrDefaultAsync(p => p.Id == partId);\n if (part != null) return new PartDto(part);\n\n var hist = await _db.PartsHistory.AsNoTracking()\n .Where(h => h.PartId == partId)\n .OrderByDescending(h => h.EffectiveFrom)\n .FirstOrDefaultAsync()\n ?? throw new InvalidOperationException("part not found");\n\n return new PartDto(hist.PartId, hist.PartNumber, hist.UnitPrice);\n }\n\n private async Task<PartDto> FindPartByNumberWithHistoryAsync(string partNumber)\n {\n var part = await _db.Parts.AsNoTracking().FirstOrDefaultAsync(p => p.PartNumber == partNumber);\n if (part != null) return new PartDto(part);\n\n var hist = await _db.PartsHistory.AsNoTracking()\n .Where(h => h.PartNumber == partNumber)\n .OrderByDescending(h => h.EffectiveFrom)\n .FirstOrDefaultAsync()\n ?? throw new InvalidOperationException("replacement not found");\n\n return new PartDto(hist.PartId, hist.PartNumber, hist.UnitPrice);\n }\n}\n\npublic record QuoteResult\n{\n public string OrderNumber { get; init; }\n public long BomId { get; init; }\n public List<QuoteLine> Lines { get; init; } = new();\n public decimal OldTotal { get; init; }\n public decimal NewTotal { get; init; }\n public decimal Diff { get; init; }\n}\n\npublic record QuoteLine\n{\n public string OldPartNumber { get; init; }\n public string NewPartNumber { get; init; }\n public int Quantity { get; init; }\n public decimal OldPrice { get; init; }\n public decimal NewPrice { get; init; }\n public decimal OldSubtotal { get; init; }\n public decimal NewSubtotal { get; init; }\n public bool Replaced { get; init; }\n}\n\npublic record PartDto\n{\n public PartDto(Part p) : this(p.Id, p.PartNumber, p.UnitPrice) { }\n public PartDto(long id, string partNumber, decimal unitPrice)\n {\n Id = id; PartNumber = partNumber; UnitPrice = unitPrice;\n }\n public long Id { get; }\n public string PartNumber { get; }\n public decimal UnitPrice { get; }\n}\n<\/pre><\/div>\n\n\n\u5c65\u6b74\u30c6\u30fc\u30d6\u30eb\u3092\u53c2\u7167\u3059\u308b\u5206\u3060\u3051\u3001\u7269\u7406\u524a\u9664\u306e C#\/LINQ \u306e\u30b3\u30fc\u30c9\u306e\u65b9\u304c\u9577\u304f\u306f\u306a\u308a\u307e\u3059\u304c\u3001\u660e\u793a\u7684\u306b BomHistory \u3084 PartsHistory \u3092\u53c2\u7167\u3057\u3066\u3044\u308b\u306e\u3067\u3001\u30b3\u30fc\u30c9\u3092\u8aad\u3093\u3060\u3068\u304d\u306b\u300c\u904e\u53bb\u30c7\u30fc\u30bf\u3092\u53c2\u7167\u3057\u3066\u3044\u308b\u300d\u3053\u3068\u304c\u5206\u304b\u308a\u3084\u3059\u3044\u3068\u3044\u3046\u5229\u70b9\u304c\u3042\u308a\u307e\u3059\u3002<\/p>\n\n\n\n
\u30b1\u30fc\u30b92<\/strong><\/h2>\n\n\n\n\u3082\u3046\u3072\u3068\u3064\u30b1\u30fc\u30b9\u3092\u8003\u3048\u3066\u307f\u307e\u3057\u3087\u3046\u3002<\/p>\n\n\n\n
\u5ba2\u5148\u304b\u3089\u767a\u6ce8\u66f8\u3092\u53d7\u3051\u305f\u304c\u3001\u767a\u6ce8\u66f8\u306b\u8a18\u8f09\u3055\u308c\u3066\u3044\u308b\u90e8\u54c1\u756a\u53f7\u3092\u30b7\u30b9\u30c6\u30e0\u3067\u691c\u7d22\u3059\u308b\u3068\u51fa\u3066\u6765\u306a\u3044\u3002\u3069\u3046\u3084\u3089\u3001\u4ee5\u524d\u306e\u898b\u7a4d\u3082\u308a\u3092\u51fa\u3057\u305f\u6642\u306b\u306f\u3001\u8abf\u9054\u53ef\u80fd\u3060\u3063\u305f\u3082\u306e\u3067\u7d44\u307f\u5408\u308f\u305b\u756a\u53f7\u3092\u4f5c\u6210\u3057\u3066\u3044\u305f\u306e\u3060\u304c\u3001\u305d\u306e\u5f8c\u306b\u5ec3\u76e4\u306b\u306a\u3063\u3066\u3057\u307e\u3063\u305f\u3089\u3057\u3044\u3002\u7d44\u307f\u5408\u308f\u305b\u756a\u53f7\u306e\u6574\u5099\u304c\u8ffd\u3044\u4ed8\u304b\u305a\u3001\u90e8\u54c1\u306e\u7d44\u307f\u5408\u308f\u305b\u306e\u307b\u3046\u304c\u6709\u52b9\u306b\u306a\u3063\u3066\u3044\u308b\u304c\u3001\u90e8\u54c1\u756a\u53f7\u3092\u8abf\u3079\u308b\u3068\u753b\u9762\u306b\u51fa\u3066\u6765\u306a\u3044\u3068\u3044\u3046\u72b6\u6cc1\u306b\u306a\u3063\u3066\u3057\u307e\u3063\u3066\u3044\u308b\u3002<\/p>\n\n\n\n
\u767a\u6ce8\u66f8\u306b\u7d44\u307f\u5408\u308f\u305b\u756a\u53f7\u304c\u66f8\u3044\u3066\u3042\u3063\u305f\u3068\u304d\u306b\u3001\u305d\u306e\u7d44\u307f\u5408\u308f\u305b\u306b\u542b\u307e\u308c\u308b\u90e8\u54c1\u756a\u53f7\u304c\u6709\u52b9\u3067\u3042\u308b\u304b\u3069\u3046\u304b\u306e\u30c1\u30a7\u30c3\u30af\u3092\u884c\u3044\u305f\u3044\u30021\u56de\u76ee\u306e\u30c1\u30a7\u30c3\u30af\u3067\u306f\u3001\u73fe\u72b6\u8abf\u9054\u3067\u304d\u308b\u90e8\u54c1\u3060\u3051\u3067\u30c1\u30a7\u30c3\u30af\u3092\u3059\u308b\u304c\u30012\u56de\u76ee\u306e\u30c1\u30a7\u30c3\u30af\u3067\u306f\u3001\u5c65\u6b74\u30c6\u30fc\u30d6\u30eb\u3082\u53c2\u7167\u3057\u3066\u3001\u904e\u53bb\u306b\u5b58\u5728\u3057\u305f\u90e8\u54c1\u756a\u53f7\u3082\u542b\u3081\u3066\u30c1\u30a7\u30c3\u30af\u3092\u884c\u3044\u305f\u3044\u3002
1\u56de\u76ee\u30682\u56de\u76ee\u306e\u30c1\u30a7\u30c3\u30af\u306e\u30e1\u30bd\u30c3\u30c9\u3092\u4f5c\u6210\u3057\u3066\u6b32\u3057\u3044\u3002
\u5165\u529b\u306f customer_orders.order_number \u3068\u3057\u305f\u3044\u3002<\/p>\n\n\n\n
\u8ad6\u7406\u524a\u9664\u306e\u30c7\u30fc\u30bf\u69cb\u9020\u306e\u5834\u5408<\/strong><\/h3>\n\n\n\n\u3053\u306e\u5834\u5408\u306f\u30011\u56de\u76ee\u304c\u73fe\u884c\u306e\u30c6\u30fc\u30d6\u30eb\u3067\u78ba\u8a8d\u3002\u3053\u3053\u3067\u5b58\u5728\u3057\u306a\u3044\u306e\u3067\u3001\u78ba\u8a8d\u306e\u305f\u3081\u306b2\u56de\u76ee\u3067\u5c65\u6b74\u3092\u63a2\u3059\u3068\u3044\u3046\u4e8c\u6bb5\u968e\u306e\u6d41\u308c\u306b\u306a\u308a\u307e\u3059\u3002<\/p>\n\n\n
\nnamespace App\\Services;\n\nuse App\\Models\\CustomerOrder;\nuse App\\Models\\Bom;\nuse App\\Models\\BomItem;\nuse App\\Models\\Part;\n\nclass BomCheckService\n{\n \/\/ \u73fe\u884c\u306e\u307f\uff08deleted_at IS NULL\uff09\n public function checkCurrentByOrder(string $orderNumber): array\n {\n $order = CustomerOrder::where('order_number', $orderNumber)->firstOrFail();\n $bom = Bom::findOrFail($order->bom_id);\n\n $partIds = BomItem::where('bom_id', $bom->id)->pluck('part_id')->all();\n $existing = Part::whereIn('id', $partIds)->pluck('id')->all(); \/\/ \u73fe\u884c\u306e\u307f\n $missingId = array_values(array_diff($partIds, $existing));\n $missingPn = Part::withTrashed()->whereIn('id', $missingId)->pluck('part_number')->all();\n\n return [\n 'bom_id' => $bom->id,\n 'missing' => $missingPn,\n ];\n }\n\n \/\/ \u5c65\u6b74\u3082\u542b\u3081\u308b\uff08withTrashed \u3067\u8ad6\u7406\u524a\u9664\u3092\u8a31\u5bb9\uff09\n public function checkWithHistoryByOrder(string $orderNumber): array\n {\n $order = CustomerOrder::where('order_number', $orderNumber)->firstOrFail();\n $bom = Bom::withTrashed()->findOrFail($order->bom_id);\n\n $partIds = BomItem::where('bom_id', $bom->id)->pluck('part_id')->all();\n $allParts = Part::withTrashed()->whereIn('id', $partIds)->get(['id','part_number','deleted_at']);\n $foundIds = $allParts->pluck('id')->all();\n $missingId = array_values(array_diff($partIds, $foundIds));\n\n $foundInHistory = $allParts->filter(fn($p) => $p->deleted_at !== null)\n ->pluck('part_number')->all();\n $unknownPn = Part::withTrashed()->whereIn('id', $missingId)->pluck('part_number')->all();\n\n return [\n 'bom_id' => $bom->id,\n 'missing' => $unknownPn, \/\/ \u73fe\u884c\u306b\u3082\u5c65\u6b74\u306b\u3082\u7121\u3044\n 'found_in_history' => $foundInHistory, \/\/ \u8ad6\u7406\u524a\u9664\u6e08\u307f\uff08\u904e\u53bb\u5b58\u5728\uff09\n ];\n }\n}\n\n<\/pre><\/div>\n\n\n\u7269\u7406\u524a\u9664\u306e\u30c7\u30fc\u30bf\u69cb\u9020\u306e\u5834\u5408<\/strong><\/h3>\n\n\n\n\u3053\u3063\u3061\u306f\u3001BomHistory, PartsHistory \u3092\u53c2\u7167\u3057\u3066\u5c65\u6b74\u3092\u78ba\u8a8d\u3059\u308b\u5fc5\u8981\u304c\u3042\u308a\u307e\u3059\u3002
\u4e21\u65b9\u306e\u69cb\u9020\u3092\u898b\u308b\u3068\u3001\u8ad6\u7406\u524a\u9664\u3067\u3082\u7269\u7406\u524a\u9664\u3067\u3082\u3042\u307e\u308a\u5909\u308f\u3089\u306a\u3044\u30b3\u30fc\u30c9\u306b\u306a\u3063\u3066\u3044\u308b\u306e\u3067\u3001\u30b3\u30fc\u30c9\u306e\u8a18\u8ff0\u30ec\u30d9\u30eb\u3067\u306f\u3001\u3069\u3061\u3089\u3092\u9078\u3093\u3067\u3082\u3088\u3055\u305d\u3046\u306a\u6c17\u304c\u3057\u307e\u3059\u3002
\u305f\u3060\u3001\u3053\u306e\u30c6\u30fc\u30d6\u30eb\u69cb\u9020\u306e\u5834\u5408\u306f\u3001customer_orders \u3068 parts \u30c6\u30fc\u30d6\u30eb\u304c\u3084\u3084\u8fd1\u3044\u3068\u3053\u308d\u306b\u3042\u308b\u306e\u3067\u3001\u305d\u3053\u3060\u3051\u898b\u308c\u3070\u69cb\u9020\u304c\u5206\u304b\u308a\u3084\u3059\u3044\u305f\u3081\u3068\u3082\u8a00\u3048\u307e\u3059\u306d\u3002<\/p>\n\n\n
\nnamespace App\\Services;\n\nuse App\\Models\\CustomerOrder;\nuse App\\Models\\Bom;\nuse App\\Models\\BomHistory;\nuse App\\Models\\BomItem;\nuse App\\Models\\Part;\nuse App\\Models\\PartsHistory;\n\nclass BomCheckService\n{\n \/\/ 1\u56de\u76ee: \u73fe\u884c\u306e\u307f\uff08\u7269\u7406\u524a\u9664\u306a\u306e\u3067\u5c65\u6b74\u306f\u898b\u306a\u3044\uff09\n public function checkCurrentByOrder(string $orderNumber): array\n {\n $order = CustomerOrder::where('order_number', $orderNumber)->firstOrFail();\n $bom = Bom::findOrFail($order->bom_id);\n\n $partIds = BomItem::where('bom_id', $bom->id)->pluck('part_id')->all();\n $existing = Part::whereIn('id', $partIds)->pluck('id')->all();\n $missingId = array_values(array_diff($partIds, $existing));\n $missingPn = PartsHistory::whereIn('part_id', $missingId)\n ->orderBy('effective_from', 'desc')\n ->pluck('part_number')\n ->unique()\n ->values()\n ->all();\n\n return [\n 'bom_id' => $bom->id,\n 'missing' => $missingPn, \/\/ \u73fe\u884c\u30c6\u30fc\u30d6\u30eb\u306b\u7121\u3044\u90e8\u54c1\u756a\u53f7\n ];\n }\n\n \/\/ 2\u56de\u76ee: \u5c65\u6b74\u3082\u542b\u3081\u3066\u5b58\u5728\u78ba\u8a8d\uff08parts_history \/ bom_history \u3092\u53c2\u7167\uff09\n public function checkWithHistoryByOrder(string $orderNumber): array\n {\n $order = CustomerOrder::where('order_number', $orderNumber)->firstOrFail();\n\n \/\/ BOM: \u73fe\u884c\u512a\u5148\u3001\u7121\u3051\u308c\u3070\u5c65\u6b74\u306e\u6700\u65b0\u3092\u63a1\u7528\n $bom = Bom::find($order->bom_id)\n ?? BomHistory::where('bom_id', $order->bom_id)\n ->orderByDesc('effective_from')\n ->firstOrFail();\n\n $partIds = BomItem::where('bom_id', $order->bom_id)->pluck('part_id')->all();\n\n \/\/ \u73fe\u884c + \u5c65\u6b74\n $current = Part::whereIn('id', $partIds)->get(['id','part_number']);\n $hist = PartsHistory::whereIn('part_id', $partIds)\n ->orderByDesc('effective_from')\n ->get(['part_id','part_number']);\n\n $foundIds = $current->pluck('id')->all();\n $histIds = $hist->pluck('part_id')->all();\n $missingId = array_values(array_diff($partIds, array_unique(array_merge($foundIds, $histIds))));\n\n $foundInHistory = $hist->pluck('part_number')->unique()->values()->all();\n $unknownPn = PartsHistory::whereIn('part_id', $missingId)\n ->orderByDesc('effective_from')\n ->pluck('part_number')\n ->unique()\n ->values()\n ->all();\n\n return [\n 'bom_id' => $bom->id,\n 'missing' => $unknownPn, \/\/ \u73fe\u884c\u306b\u3082\u5c65\u6b74\u306b\u3082\u7121\u3044\n 'found_in_history' => $foundInHistory, \/\/ \u5c65\u6b74\u3067\u5b58\u5728\u3057\u305f\u90e8\u54c1\u756a\u53f7\n ];\n }\n}\n\n<\/pre><\/div>\n\n\n\u3053\u308c\u3060\u3051\u3060\u3068\u3001\u53cc\u65b9\u3067\u5dee\u7570\u304c\u307f\u3089\u308c\u306a\u3044\u306e\u3067\u3001\u5f15\u304d\u7d9a\u304d\u3002
<\/p>\n","protected":false},"excerpt":{"rendered":"
\u8ad6\u7406\u524a\u9664\u3068\u7269\u7406\u524a\u9664\u306e\u6bd4\u8f03\u306e\u305f\u3081\u306e\u30c6\u30fc\u30d6\u30eb\u8a2d\u8a08\u304c\u3072\u3068\u901a\u308a\u3067\u304d\u305f\u306e\u3067\u3001\u5177\u4f53\u7684\u306b\u3069\u306e\u3088\u3046\u306b\u4f7f\u308f\u308c\u308b\u306e\u304b\u3092\u8003\u3048\u3066\u307f\u307e\u3057\u3087\u3046\u3002 \u3053\u306e\u624b\u306e\u3084\u3064\u306f\u3001\u3044\u3061\u3070\u3093\u8907\u96d1\u3060\u3068\u601d\u308f\u308c\u308b\u300c\u5de5\u5834\u306e\u90e8\u54c1\u53d7\u6ce8\u30b7\u30b9\u30c6\u30e0\u300d\u3092\u4f8b\u306b\u3068\u308b\u3068\u3088\u3044\u3067\u3059\u3002\u4e0b\u624b\u306b\u3001\u3084\u3055\u3057 … \u7d9a\u304d\u3092\u8aad\u3080
2. \u8907\u96d1\u306a\u30af\u30a8\u30ea\u3042\u308b\u3044\u306f\u624b\u9806\u306e\u30e6\u30fc\u30b9\u30b1\u30fc\u30b9\u3092\u8003\u3048\u308b
3. \u305d\u308c\u305e\u308c\u306e\u30b1\u30fc\u30b9\u3067\u554f\u984c\u3067\u30af\u30a8\u30ea\u3084\u30b3\u30fc\u30c9\u3092\u66f8\u3044\u3066\u307f\u308b
4. \u554f\u984c\u304c\u8d77\u3053\u308b\u304b\u3069\u3046\u304b\u3092\u78ba\u8a8d\u3059\u308b<\/p>\n\n\n\n
\u9006\u306b\u5c06\u6765\u7684\u306b AI \u30a8\u30fc\u30b8\u30a7\u30f3\u30c8\u306b\u30b3\u30fc\u30c7\u30a3\u30f3\u30b0\u3092\u4f9d\u5b58\u3057\u305f\u5834\u5408\u306b\u3001\u7834\u7dbb\u3059\u308b\u3088\u3046\u306a\u30c7\u30fc\u30bf\u69cb\u9020\u3092\u8a2d\u8a08\u3059\u308b\u306e\u3092\u907f\u3051\u308b\u3068\u3044\u3046\u610f\u5473\u3082\u3042\u308a\u307e\u3059\u3002\u3067\u304d\u308b\u9650\u308a\u3001AI \u304c\u8aa4\u89e3\u3057\u306a\u3044\u3088\u3046\u306a\u30c7\u30fc\u30bf\u69cb\u9020\u304c\u671b\u307e\u308c\u308b\u3067\u3057\u3087\u3046\u3002<\/p>\n\n\n\n
- \n
- – \u90e8\u54c1\u30de\u30b9\u30bf\u30fc<\/li>\n\n\n\n
- – \u90e8\u54c1\u88dc\u52a9\u60c5\u5831<\/li>\n\n\n\n
- – \u90e8\u54c1\u306e\u8abf\u9054\u5148<\/li>\n\n\n\n
- – \u53d7\u6ce8\u60c5\u5831<\/li>\n\n\n\n
- – \u90e8\u54c1\u751f\u7523\u7ba1\u7406<\/li>\n\n\n\n
- – \u90e8\u54c1\u306e\u7d44\u307f\u5408\u308f\u305b\u30c6\u30fc\u30d6\u30eb<\/li>\n\n\n\n
- – \u53d7\u6ce8\u7d44\u307f\u3042\u308f\u305b\u756a\u53f7\u7ba1\u7406<\/li>\n\n\n\n
- – \u9867\u5ba2\u767a\u6ce8\u5c65\u6b74<\/li>\n<\/ul>\n\n\n\n
\u8ad6\u7406\u524a\u9664\u578b\u30c7\u30fc\u30bf\u69cb\u9020<\/strong><\/h2>\n\n\n
\n-- \u90e8\u54c1\u30de\u30b9\u30bf\u30fc\nCREATE TABLE parts (\n id BIGINT PRIMARY KEY AUTO_INCREMENT,\n part_number VARCHAR(64) UNIQUE NOT NULL,\n part_name VARCHAR(255) NOT NULL,\n description TEXT NULL,\n unit_price DECIMAL(10, 2) NOT NULL,\n lead_time_days INT DEFAULT 0,\n created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n deleted_at TIMESTAMP NULL\n);\n \n-- \u90e8\u54c1\u88dc\u52a9\u60c5\u5831\nCREATE TABLE part_details (\n id BIGINT PRIMARY KEY AUTO_INCREMENT,\n part_id BIGINT NOT NULL,\n weight DECIMAL(10, 4) NULL,\n dimensions VARCHAR(255) NULL,\n material VARCHAR(128) NULL,\n storage_location VARCHAR(128) NULL,\n created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n FOREIGN KEY (part_id) REFERENCES parts(id)\n);\n \n-- \u90e8\u54c1\u306e\u8abf\u9054\u5148\nCREATE TABLE part_suppliers (\n id BIGINT PRIMARY KEY AUTO_INCREMENT,\n part_id BIGINT NOT NULL,\n supplier_id BIGINT NOT NULL,\n supplier_part_number VARCHAR(64) NULL,\n supplier_price DECIMAL(10, 2) NOT NULL,\n lead_time_days INT DEFAULT 0,\n created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n FOREIGN KEY (part_id) REFERENCES parts(id),\n FOREIGN KEY (supplier_id) REFERENCES suppliers(id)\n);\n \n-- \u53d7\u6ce8\u60c5\u5831\nCREATE TABLE purchase_orders (\n id BIGINT PRIMARY KEY AUTO_INCREMENT,\n po_number VARCHAR(64) UNIQUE NOT NULL,\n supplier_id BIGINT NOT NULL,\n order_date DATE NOT NULL,\n expected_delivery_date DATE NOT NULL,\n actual_delivery_date DATE NULL,\n status ENUM('pending', 'ordered', 'received', 'cancelled') DEFAULT 'pending',\n created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n FOREIGN KEY (supplier_id) REFERENCES suppliers(id)\n);\n \n-- \u90e8\u54c1\u751f\u7523\u7ba1\u7406\nCREATE TABLE part_production (\n id BIGINT PRIMARY KEY AUTO_INCREMENT,\n part_id BIGINT NOT NULL,\n production_date DATE NOT NULL,\n quantity_produced INT NOT NULL,\n quality_status ENUM('pass', 'fail', 'rework') DEFAULT 'pass',\n notes TEXT NULL,\n created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n FOREIGN KEY (part_id) REFERENCES parts(id)\n);\n \n-- \u90e8\u54c1\u306e\u7d44\u307f\u5408\u308f\u305b\u30c6\u30fc\u30d6\u30eb\uff08BOM\uff09\nCREATE TABLE bom_items (\n id BIGINT PRIMARY KEY AUTO_INCREMENT,\n bom_id BIGINT NOT NULL,\n part_id BIGINT NOT NULL,\n quantity INT NOT NULL,\n sequence INT NOT NULL,\n created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n FOREIGN KEY (bom_id) REFERENCES bom(id),\n FOREIGN KEY (part_id) REFERENCES parts(id),\n UNIQUE KEY unique_bom_part (bom_id, part_id)\n);\n \n-- BOM \u30de\u30b9\u30bf\u30fc\nCREATE TABLE bom (\n id BIGINT PRIMARY KEY AUTO_INCREMENT,\n product_number VARCHAR(64) UNIQUE NOT NULL,\n product_name VARCHAR(255) NOT NULL,\n version INT DEFAULT 1,\n effective_date DATE NOT NULL,\n status ENUM('active', 'obsolete') DEFAULT 'active',\n created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n deleted_at TIMESTAMP NULL\n);\n \n-- \u53d7\u6ce8\u7d44\u307f\u5408\u308f\u305b\u756a\u53f7\u7ba1\u7406\nCREATE TABLE customer_orders (\n id BIGINT PRIMARY KEY AUTO_INCREMENT,\n order_number VARCHAR(64) UNIQUE NOT NULL,\n customer_id BIGINT NOT NULL,\n bom_id BIGINT NOT NULL,\n order_date DATE NOT NULL,\n delivery_date DATE NOT NULL,\n quantity INT NOT NULL,\n status ENUM('pending', 'in_production', 'completed', 'cancelled') DEFAULT 'pending',\n created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n FOREIGN KEY (customer_id) REFERENCES customers(id),\n FOREIGN KEY (bom_id) REFERENCES bom(id)\n);\n \n-- \u9867\u5ba2\u767a\u6ce8\u5c65\u6b74\nCREATE TABLE order_history (\n id BIGINT PRIMARY KEY AUTO_INCREMENT,\n customer_order_id BIGINT NOT NULL,\n status_changed_to VARCHAR(64) NOT NULL,\n changed_at TIMESTAMP NOT NULL,\n notes TEXT NULL,\n created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n FOREIGN KEY (customer_order_id) REFERENCES customer_orders(id)\n);\n \n-- \u9867\u5ba2\u30de\u30b9\u30bf\u30fc\nCREATE TABLE customers (\n id BIGINT PRIMARY KEY AUTO_INCREMENT,\n customer_code VARCHAR(64) UNIQUE NOT NULL,\n customer_name VARCHAR(255) NOT NULL,\n contact_person VARCHAR(128) NULL,\n phone VARCHAR(20) NULL,\n email VARCHAR(255) NULL,\n created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP\n);\n \n-- \u30b5\u30d7\u30e9\u30a4\u30e4\u30fc\u30de\u30b9\u30bf\u30fc\nCREATE TABLE suppliers (\n id BIGINT PRIMARY KEY AUTO_INCREMENT,\n supplier_code VARCHAR(64) UNIQUE NOT NULL,\n supplier_name VARCHAR(255) NOT NULL,\n contact_person VARCHAR(128) NULL,\n phone VARCHAR(20) NULL,\n email VARCHAR(255) NULL,\n created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP\n<\/pre><\/div>\n\n\n<\/a><\/figure>\n\n\n\n\u524d\u56de Copilot \u306b\u66f8\u3044\u3066\u8cb0\u3063\u305f\u30c7\u30fc\u30bf\u69cb\u9020\u306f\u3001deleted_at \u30ab\u30e9\u30e0\u304c\u3042\u308b\u8ad6\u7406\u524a\u9664\u578b\u306e\u3082\u306e\u3067\u3057\u305f\u3002\u3053\u308c\u306f\u3001AI \u304c\u5410\u304d\u51fa\u3057\u305f\u3082\u306e\u3067\u3059\u304b\u3089\u3001\u591a\u5c11\u306e\u4fee\u6b63\u304c\u5fc5\u8981\u304b\u3082\u3057\u308c\u307e\u305b\u3093\u304c\u3001AI \u304c\u30e9\u30a4\u30d6\u30b3\u30fc\u30c7\u30a3\u30f3\u30b0\u3092\u3057\u3084\u3059\u3044\u30c7\u30fc\u30bf\u69cb\u9020\u306b\u306a\u3063\u3066\u3044\u308b\u7b48\u3067\u3059\u3002<\/p>\n\n\n\n
\u3053\u308c\u306b\u547d\u984c\u3092\u4e0e\u3048\u3066\u3044\u304d\u307e\u3059\u3002<\/p>\n\n\n\n
\u7269\u7406\u524a\u9664\u578b\u30c7\u30fc\u30bf\u69cb\u9020<\/strong><\/h2>\n\n\n\n
parts, bom \u30c6\u30fc\u30d6\u30eb\u306b deleted_at \u30ab\u30e9\u30e0\u304c\u5165\u3063\u3066\u3044\u308b\u306e\u3067\u3001\u3053\u308c\u3092\u53d6\u308a\u9664\u304d\u307e\u3059\u3002
\u3055\u3089\u306b\u3001parts \u3084 bom \u30c6\u30fc\u30d6\u30eb\u306e\u904e\u53bb\u306e\u3082\u306e\u304c\u53c2\u7167\u3067\u304d\u308b\u3088\u3046\u306b\u3001\u5c65\u6b74\u7528\u306e\u30c6\u30fc\u30d6\u30eb\u3092\u4f5c\u3063\u3066\u8cb0\u3044\u307e\u3059\u3002<\/p>\n\n\n\n-- \u90e8\u54c1\u30de\u30b9\u30bf\u30fc\uff08\u7269\u7406\u524a\u9664\uff09\nCREATE TABLE parts (\n id BIGINT PRIMARY KEY AUTO_INCREMENT,\n part_number VARCHAR(64) UNIQUE NOT NULL,\n part_name VARCHAR(255) NOT NULL,\n description TEXT NULL,\n unit_price DECIMAL(10, 2) NOT NULL,\n lead_time_days INT DEFAULT 0,\n created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP\n);\n\n-- \u90e8\u54c1\u30de\u30b9\u30bf\u30fc\u5c65\u6b74\nCREATE TABLE parts_history (\n history_id BIGINT PRIMARY KEY AUTO_INCREMENT,\n part_id BIGINT NOT NULL,\n part_number VARCHAR(64) NOT NULL,\n part_name VARCHAR(255) NOT NULL,\n description TEXT NULL,\n unit_price DECIMAL(10, 2) NOT NULL,\n lead_time_days INT DEFAULT 0,\n version INT NOT NULL,\n effective_from TIMESTAMP NOT NULL,\n effective_to TIMESTAMP NULL,\n created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n FOREIGN KEY (part_id) REFERENCES parts(id)\n);\n\n-- BOM \u30de\u30b9\u30bf\u30fc\uff08\u7269\u7406\u524a\u9664\uff09\nCREATE TABLE bom (\n id BIGINT PRIMARY KEY AUTO_INCREMENT,\n product_number VARCHAR(64) UNIQUE NOT NULL,\n product_name VARCHAR(255) NOT NULL,\n version INT DEFAULT 1,\n effective_date DATE NOT NULL,\n status ENUM('active', 'obsolete') DEFAULT 'active',\n created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP\n);\n\n-- BOM \u30de\u30b9\u30bf\u30fc\u5c65\u6b74\nCREATE TABLE bom_history (\n history_id BIGINT PRIMARY KEY AUTO_INCREMENT,\n bom_id BIGINT NOT NULL,\n product_number VARCHAR(64) NOT NULL,\n product_name VARCHAR(255) NOT NULL,\n version INT NOT NULL,\n effective_date DATE NOT NULL,\n status ENUM('active', 'obsolete') DEFAULT 'active',\n effective_from TIMESTAMP NOT NULL,\n effective_to TIMESTAMP NULL,\n created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n FOREIGN KEY (bom_id) REFERENCES bom(id)\n);\n\n-- \u90e8\u54c1\u88dc\u52a9\u60c5\u5831\uff08\u7269\u7406\u524a\u9664\uff09\nCREATE TABLE part_details (\n id BIGINT PRIMARY KEY AUTO_INCREMENT,\n part_id BIGINT NOT NULL,\n weight DECIMAL(10, 4) NULL,\n dimensions VARCHAR(255) NULL,\n material VARCHAR(128) NULL,\n storage_location VARCHAR(128) NULL,\n created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n FOREIGN KEY (part_id) REFERENCES parts(id)\n);\n\n-- \u90e8\u54c1\u306e\u8abf\u9054\u5148\uff08\u7269\u7406\u524a\u9664\uff09\nCREATE TABLE part_suppliers (\n id BIGINT PRIMARY KEY AUTO_INCREMENT,\n part_id BIGINT NOT NULL,\n supplier_id BIGINT NOT NULL,\n supplier_part_number VARCHAR(64) NULL,\n supplier_price DECIMAL(10, 2) NOT NULL,\n lead_time_days INT DEFAULT 0,\n created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n FOREIGN KEY (part_id) REFERENCES parts(id),\n FOREIGN KEY (supplier_id) REFERENCES suppliers(id)\n);\n\n-- \u53d7\u6ce8\u60c5\u5831\uff08\u7269\u7406\u524a\u9664\uff09\nCREATE TABLE purchase_orders (\n id BIGINT PRIMARY KEY AUTO_INCREMENT,\n po_number VARCHAR(64) UNIQUE NOT NULL,\n supplier_id BIGINT NOT NULL,\n order_date DATE NOT NULL,\n expected_delivery_date DATE NOT NULL,\n actual_delivery_date DATE NULL,\n status ENUM('pending', 'ordered', 'received', 'cancelled') DEFAULT 'pending',\n created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n FOREIGN KEY (supplier_id) REFERENCES suppliers(id)\n);\n\n-- \u90e8\u54c1\u751f\u7523\u7ba1\u7406\uff08\u7269\u7406\u524a\u9664\uff09\nCREATE TABLE part_production (\n id BIGINT PRIMARY KEY AUTO_INCREMENT,\n part_id BIGINT NOT NULL,\n production_date DATE NOT NULL,\n quantity_produced INT NOT NULL,\n quality_status ENUM('pass', 'fail', 'rework') DEFAULT 'pass',\n notes TEXT NULL,\n created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n FOREIGN KEY (part_id) REFERENCES parts(id)\n);\n\n-- \u90e8\u54c1\u306e\u7d44\u307f\u5408\u308f\u305b\u30c6\u30fc\u30d6\u30eb\uff08BOM\uff09\uff08\u7269\u7406\u524a\u9664\uff09\nCREATE TABLE bom_items (\n id BIGINT PRIMARY KEY AUTO_INCREMENT,\n bom_id BIGINT NOT NULL,\n part_id BIGINT NOT NULL,\n quantity INT NOT NULL,\n sequence INT NOT NULL,\n created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n FOREIGN KEY (bom_id) REFERENCES bom(id),\n FOREIGN KEY (part_id) REFERENCES parts(id),\n UNIQUE KEY unique_bom_part (bom_id, part_id)\n);\n\n-- \u53d7\u6ce8\u7d44\u307f\u5408\u308f\u305b\u756a\u53f7\u7ba1\u7406\uff08\u7269\u7406\u524a\u9664\uff09\nCREATE TABLE customer_orders (\n id BIGINT PRIMARY KEY AUTO_INCREMENT,\n order_number VARCHAR(64) UNIQUE NOT NULL,\n customer_id BIGINT NOT NULL,\n bom_id BIGINT NOT NULL,\n order_date DATE NOT NULL,\n delivery_date DATE NOT NULL,\n quantity INT NOT NULL,\n status ENUM('pending', 'in_production', 'completed', 'cancelled') DEFAULT 'pending',\n created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n FOREIGN KEY (customer_id) REFERENCES customers(id),\n FOREIGN KEY (bom_id) REFERENCES bom(id)\n);\n\n-- \u9867\u5ba2\u767a\u6ce8\u5c65\u6b74\uff08\u7269\u7406\u524a\u9664\uff09\nCREATE TABLE order_history (\n id BIGINT PRIMARY KEY AUTO_INCREMENT,\n customer_order_id BIGINT NOT NULL,\n status_changed_to VARCHAR(64) NOT NULL,\n changed_at TIMESTAMP NOT NULL,\n notes TEXT NULL,\n created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n FOREIGN KEY (customer_order_id) REFERENCES customer_orders(id)\n);\n\n-- \u9867\u5ba2\u30de\u30b9\u30bf\u30fc\uff08\u7269\u7406\u524a\u9664\uff09\nCREATE TABLE customers (\n id BIGINT PRIMARY KEY AUTO_INCREMENT,\n customer_code VARCHAR(64) UNIQUE NOT NULL,\n customer_name VARCHAR(255) NOT NULL,\n contact_person VARCHAR(128) NULL,\n phone VARCHAR(20) NULL,\n email VARCHAR(255) NULL,\n created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP\n);\n\n-- \u30b5\u30d7\u30e9\u30a4\u30e4\u30fc\u30de\u30b9\u30bf\u30fc\uff08\u7269\u7406\u524a\u9664\uff09\nCREATE TABLE suppliers (\n id BIGINT PRIMARY KEY AUTO_INCREMENT,\n supplier_code VARCHAR(64) UNIQUE NOT NULL,\n supplier_name VARCHAR(255) NOT NULL,\n contact_person VARCHAR(128) NULL,\n phone VARCHAR(20) NULL,\n email VARCHAR(255) NULL,\n created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP\n);\n\n<\/pre><\/div>\n\n\n<\/a><\/figure>\n\n\n\nER \u56f3\u3092\u898b\u6bd4\u3079\u308b\u3068<\/p>\n\n\n\n
- \n
- PARTS_HISTORY<\/li>\n\n\n\n
- BOM_HISTORY<\/li>\n<\/ul>\n\n\n\n
\u3068\u3044\u3046\u5c65\u6b74\u30c6\u30fc\u30d6\u30eb\u304c\u8ffd\u52a0\u3055\u308c\u3066\u3044\u308b\u3053\u3068\u304c\u5206\u304b\u308a\u307e\u3059\u3002<\/p>\n\n\n\n
\u30e6\u30fc\u30b9\u30b1\u30fc\u30b9\u306e\u691c\u8a0e<\/strong><\/h2>\n\n\n\n
\u5177\u4f53\u7684\u306b\u30e6\u30fc\u30b9\u30b1\u30fc\u30b9\u3092\u8003\u3048\u307e\u3057\u3087\u3046\u3002
\u5c65\u6b74\u304c\u5fc5\u8981\u306a\u306e\u306f\u3001\u90e8\u54c1\u30c6\u30fc\u30d6\u30eb\uff08parts\uff09\u3068\u90e8\u54c1\u306e\u7d44\u307f\u5408\u308f\u305b\u30c6\u30fc\u30d6\u30eb\uff08bom\uff09\u3067\u3059\u3002
\u3053\u306e2\u3064\u306e\u30c6\u30fc\u30d6\u30eb\u3067\u3001\u904e\u53bb\u306b\u3055\u304b\u306e\u307c\u3063\u3066\u30c7\u30fc\u30bf\u3092\u53c2\u7167\u3059\u308b\u30d1\u30bf\u30fc\u30f3\u3092\u8003\u3048\u307e\u3059\u3002<\/p>\n\n\n\n\u30b1\u30fc\u30b91<\/strong><\/h2>\n\n\n\n
\u9867\u5ba2\u304b\u3089\u53e4\u3044\u767a\u6ce8\u66f8\u306b\u57fa\u3065\u3044\u3066\u90e8\u54c1\u306e\u767a\u6ce8\u3092\u53d7\u3051\u305f\u3002\u6b8b\u5ff5\u306a\u304c\u3089\u904e\u53bb\u306e\u767a\u6ce8\u306e\u305f\u3081\u306b\u3001\u767a\u6ce8\u66f8\u306e\u4e2d\u306b\u3042\u308b\u3001\u90e8\u54c1\u756a\u53f7\u306f\u73fe\u5728\u6271\u3063\u3066\u3044\u306a\u3044\u3002\u3053\u306e\u90e8\u54c1\u756a\u53f7\u3092\u65b0\u3057\u304f\u3057\u3066\u898b\u7a4d\u3082\u308a\u66f8\u3092\u4f5c\u6210\u3057\u305f\u3044\u3002
\u3053\u306e\u3068\u304d\u306b\u3001\u904e\u53bb\u306e\u767a\u6ce8\u66f8\u3068\u5408\u8a08\u91d1\u984d\u3092\u7167\u5408\u3057\u3066\u3001\u5dee\u5206\u3092\u8a08\u7b97\u3057\u3066\u304a\u304d\u305f\u3044\u3002
\u90e8\u54c1A,B,C \u304c\u904e\u53bb\u306e\u767a\u6ce8\u66f8\u306b\u3042\u308a\u3001\u90e8\u54c1B \u304c\u5ec3\u76e4\u306b\u306a\u3063\u3066\u3044\u308b\u305f\u3081\u3001\u90e8\u54c1D \u306b\u7f6e\u304d\u5909\u3048\u305f\u3044\u3002<\/p>\n\n\n\n\u3053\u306e\u3068\u304d\u306b\u3001\u8ad6\u7406\u524a\u9664\u578b\u30c7\u30fc\u30bf\u69cb\u9020\u3092\u4f7f\u3063\u305f\u3068\u304d\u306e SQL \u30af\u30a8\u30ea\u3001\u3042\u308b\u3044\u306f\u3001PHP\/Laravel \u306b\u3088\u308b\u691c\u7d22\u7d50\u679c\u3092\u793a\u3057\u3066\u307b\u3057\u3044\u3002<\/p>\n\n\n\n
\u8ad6\u7406\u524a\u9664\u306e\u30c7\u30fc\u30bf\u69cb\u9020\u306e\u5834\u5408<\/strong><\/h3>\n\n\n\n
Laravel\/Eloquent \u306e\u30b3\u30fc\u30c9\u306e\u5834\u5408\u306f\u3001\u8ad6\u7406\u524a\u9664\u3092 withTrashed \u30e1\u30bd\u30c3\u30c9\u3092\u4f7f\u3063\u3066\u524a\u9664\u6e08\u307f\u30c7\u30fc\u30bf\u3082\u53d6\u5f97\u3067\u304d\u308b\u3088\u3046\u306b\u3057\u307e\u3059\u3002\u666e\u6bb5\u306e\u691c\u7d22\u306e\u3068\u304d\u306f\u3001where \u3092\u4f7f\u3063\u3066 deleted_at is not null \u306e\u6761\u4ef6\u304c\u81ea\u52d5\u7684\u306b\u8ffd\u52a0\u306b\u306a\u308b\u306e\u3067\u3001\u3053\u3053\u3067\u6df7\u4e71\u3059\u308b\u3053\u3068\u306f\u3042\u308a\u307e\u305b\u3093\u3002<\/p>\n\n\n
\nnamespace App\\Services;\n\nuse App\\Models\\CustomerOrder;\nuse App\\Models\\Bom;\nuse App\\Models\\BomItem;\nuse App\\Models\\Part;\nuse Illuminate\\Support\\Collection;\n\nclass QuoteService\n{\n \/**\n * @param string $orderNumber \u904e\u53bb\u306e\u767a\u6ce8\u756a\u53f7\n * @param array<string,string> $replacements ['old_part_number' => 'new_part_number']\n *\/\n public function buildQuote(string $orderNumber, array $replacements): array\n {\n \/\/ \u904e\u53bb\u306e\u767a\u6ce8\u3092\u53d6\u5f97\uff08\u524a\u9664\u6e08\u307f BOM \u3082\u62fe\u3046\u305f\u3081 withTrashed\uff09\n $order = CustomerOrder::where('order_number', $orderNumber)->firstOrFail();\n $bom = Bom::withTrashed()->findOrFail($order->bom_id);\n\n \/\/ BOM \u660e\u7d30\uff08\u524a\u9664\u6e08\u307f\u30d1\u30fc\u30c4\u3082\u62fe\u3046\u305f\u3081 withTrashed\uff09\n $items = BomItem::where('bom_id', $bom->id)->get()->map(function (BomItem $item) {\n $item->part = Part::withTrashed()->findOrFail($item->part_id);\n return $item;\n });\n\n $lines = [];\n $oldTotal = 0;\n $newTotal = 0;\n\n foreach ($items as $item) {\n $oldPart = $item->part;\n $qty = $item->quantity;\n $newPart = $oldPart;\n\n \/\/ \u5ec3\u76e4\u54c1\u3092\u4ee3\u66ff\u54c1\u306b\u5dee\u3057\u66ff\u3048\n if (isset($replacements[$oldPart->part_number])) {\n $newPart = Part::where('part_number', $replacements[$oldPart->part_number])->firstOrFail();\n }\n\n $lines[] = [\n 'old_part_number' => $oldPart->part_number,\n 'new_part_number' => $newPart->part_number,\n 'quantity' => $qty,\n 'old_price' => $oldPart->unit_price,\n 'new_price' => $newPart->unit_price,\n 'old_subtotal' => $oldPart->unit_price * $qty,\n 'new_subtotal' => $newPart->unit_price * $qty,\n 'replaced' => $oldPart->id !== $newPart->id,\n ];\n\n $oldTotal += $oldPart->unit_price * $qty;\n $newTotal += $newPart->unit_price * $qty;\n }\n\n return [\n 'order_number' => $order->order_number,\n 'bom_id' => $bom->id,\n 'lines' => $lines,\n 'old_total' => $oldTotal,\n 'new_total' => $newTotal,\n 'diff' => $newTotal - $oldTotal,\n ];\n }\n}\n<\/pre><\/div>\n\n\n\u307e\u3042\u3001\u3053\u308c\u306f softDelete \u306e\u6a5f\u80fd\u304c Laravel\/Eloquent \u306b\u3042\u308b\u306e\u3067\u7c21\u5358\u306b\u66f8\u3051\u308b\u3068\u3044\u3046\u88cf\u6280\u307f\u305f\u3044\u306a\u3082\u306e\u306a\u306e\u3067\u3001\u540c\u3058\u30d1\u30bf\u30fc\u30f3\u3092 C#\/LINQ \u3067\u3084\u3063\u3066\u307f\u307e\u3057\u3087\u3046\u3002<\/p>\n\n\n\n
\u3053\u306e\u30d1\u30bf\u30fc\u30f3\u306f C# \u306e EF Core \u3092\u66ab\u304f\u3084\u3063\u3066\u3044\u306a\u304b\u3063\u305f\u306e\u3067\u3001\u6c17\u304c\u4ed8\u304b\u306a\u304b\u3063\u305f\u306e\u3067\u3059\u304c\u3001IgnoreQueryFilters \u30e1\u30bd\u30c3\u30c9\u304c\u3042\u308a\u307e\u3059\u306d\u3002
deleted_at \u30ab\u30e9\u30e0\u3092\u9664\u5916\u3059\u308b\u305f\u3081\u306b\u3042\u3089\u304b\u3058\u3081\u30b0\u30ed\u30fc\u30d0\u30eb\u30af\u30a8\u30ea\u30d5\u30a3\u30eb\u30bf\u30fc\u3092\u4ed8\u3051\u3066\u304a\u3051\u3070\u3088\u3044\u305d\u3046\u3067\u3059\u3002<\/p>\n\n\n\nusing Microsoft.EntityFrameworkCore;\n\npublic class QuoteService\n{\n private readonly AppDbContext _db;\n\n public QuoteService(AppDbContext db) => _db = db;\n\n \/\/ replacements: \u53e4\u3044\u54c1\u756a\u2192\u65b0\u3057\u3044\u54c1\u756a\u306e\u30de\u30c3\u30d7\n public async Task<QuoteResult> BuildQuoteAsync(string orderNumber, Dictionary<string, string> replacements)\n {\n var order = await _db.CustomerOrders\n .AsNoTracking()\n .FirstOrDefaultAsync(o => o.OrderNumber == orderNumber)\n ?? throw new InvalidOperationException("order not found");\n\n var bom = await _db.Boms\n .IgnoreQueryFilters() \/\/ deleted_at \u3092\u7121\u8996\u3057\u3066\u53d6\u5f97\n .AsNoTracking()\n .FirstOrDefaultAsync(b => b.Id == order.BomId)\n ?? throw new InvalidOperationException("bom not found");\n\n var items = await _db.BomItems\n .Where(i => i.BomId == bom.Id)\n .AsNoTracking()\n .ToListAsync();\n\n var lines = new List<QuoteLine>();\n decimal oldTotal = 0;\n decimal newTotal = 0;\n\n foreach (var item in items)\n {\n var oldPart = await _db.Parts\n .IgnoreQueryFilters()\n .AsNoTracking()\n .FirstOrDefaultAsync(p => p.Id == item.PartId)\n ?? throw new InvalidOperationException("part not found");\n\n var newPart = oldPart;\n if (replacements.TryGetValue(oldPart.PartNumber, out var newPn))\n {\n newPart = await _db.Parts\n .AsNoTracking()\n .FirstOrDefaultAsync(p => p.PartNumber == newPn)\n ?? throw new InvalidOperationException("replacement not found");\n }\n\n var qty = item.Quantity;\n var oldSub = oldPart.UnitPrice * qty;\n var newSub = newPart.UnitPrice * qty;\n\n lines.Add(new QuoteLine\n {\n OldPartNumber = oldPart.PartNumber,\n NewPartNumber = newPart.PartNumber,\n Quantity = qty,\n OldPrice = oldPart.UnitPrice,\n NewPrice = newPart.UnitPrice,\n OldSubtotal = oldSub,\n NewSubtotal = newSub,\n Replaced = oldPart.Id != newPart.Id\n });\n\n oldTotal += oldSub;\n newTotal += newSub;\n }\n\n return new QuoteResult\n {\n OrderNumber = order.OrderNumber,\n BomId = bom.Id,\n Lines = lines,\n OldTotal = oldTotal,\n NewTotal = newTotal,\n Diff = newTotal - oldTotal\n };\n }\n}\n\npublic record QuoteResult\n{\n public string OrderNumber { get; init; }\n public long BomId { get; init; }\n public List<QuoteLine> Lines { get; init; } = new();\n public decimal OldTotal { get; init; }\n public decimal NewTotal { get; init; }\n public decimal Diff { get; init; }\n}\n\npublic record QuoteLine\n{\n public string OldPartNumber { get; init; }\n public string NewPartNumber { get; init; }\n public int Quantity { get; init; }\n public decimal OldPrice { get; init; }\n public decimal NewPrice { get; init; }\n public decimal OldSubtotal { get; init; }\n public decimal NewSubtotal { get; init; }\n public bool Replaced { get; init; }\n}\n<\/pre><\/div>\n\n\n\u30b3\u30fc\u30c9\u30d5\u30a1\u30fc\u30b9\u30c8\u306e\u3068\u304d\u306b\u3001OnModelCreating \u30e1\u30bd\u30c3\u30c9\u3067\u3001HasQueryFilter \u3092\u4f7f\u3063\u3066\u30b0\u30ed\u30fc\u30d0\u30eb\u30af\u30a8\u30ea\u30d5\u30a3\u30eb\u30bf\u30fc\u3092\u8a2d\u5b9a\u3057\u3066\u304a\u304d\u307e\u3059\u3002<\/p>\n\n\n
\nprotected override void OnModelCreating(ModelBuilder modelBuilder)\n{\n base.OnModelCreating(modelBuilder);\n\n \/\/ Soft-delete \u7528\u30d5\u30a3\u30eb\u30bf\u30fc\uff08\u901a\u5e38\u306f deleted_at \u304c NULL \u306e\u307f\u53d6\u5f97\uff09\n modelBuilder.Entity<Bom>()\n .HasQueryFilter(b => b.DeletedAt == null);\n\n \/\/ \u4ed6\u306e\u30a8\u30f3\u30c6\u30a3\u30c6\u30a3\u306b\u3082\u5fc5\u8981\u306a\u3089\u540c\u69d8\u306b\n \/\/ modelBuilder.Entity<Part>().HasQueryFilter(p => p.DeletedAt == null);\n}\n\n<\/pre><\/div>\n\n\n\u7269\u7406\u524a\u9664\u306e\u30c7\u30fc\u30bf\u69cb\u9020\u306e\u5834\u5408<\/strong><\/h3>\n\n\n\n
\u4eca\u5ea6\u306f\u3001\u7269\u7406\u524a\u9664\u306e\u30d1\u30bf\u30fc\u30f3\u3067\u3059\u3002<\/p>\n\n\n\n
\u53e4\u3044 parts, bom \u30c6\u30fc\u30d6\u30eb\u306e\u30c7\u30fc\u30bf\u306f\u524a\u9664\u3055\u308c\u3066\u3044\u308b\u53ef\u80fd\u6027\u304c\u3042\u308b\u306e\u3067\u3001\u5c65\u6b74\u30c6\u30fc\u30d6\u30eb\u304b\u3089\u6700\u65b0\u306e\u3082\u306e\u3092\u53d6\u5f97\u3059\u308b\u5fc5\u8981\u304c\u3042\u308a\u307e\u3059\u3002findPartWithHistory \u3084 findPartByNumberWithHistory \u3068\u3044\u3063\u305f\u30d8\u30eb\u30d1\u30fc\u30e1\u30bd\u30c3\u30c9\u3092\u7528\u610f\u3057\u3066\u3001\u5c65\u6b74\u30c6\u30fc\u30d6\u30eb\u304b\u3089\u6700\u65b0\u306e\u30c7\u30fc\u30bf\u3092\u53d6\u5f97\u3059\u308b\u3088\u3046\u306b\u3057\u307e\u3059\u3002<\/p>\n\n\n
\n<?php\n\nnamespace App\\Services;\n\nuse App\\Models\\CustomerOrder;\nuse App\\Models\\Bom;\nuse App\\Models\\BomItem;\nuse App\\Models\\Part;\nuse App\\Models\\PartsHistory;\nuse App\\Models\\BomHistory;\nuse Illuminate\\Support\\Facades\\DB;\n\nclass QuoteService\n{\n \/**\n * @param string $orderNumber \u904e\u53bb\u306e\u767a\u6ce8\u756a\u53f7\n * @param array<string,string> $replacements ['old_part_number' => 'new_part_number']\n *\/\n public function buildQuote(string $orderNumber, array $replacements): array\n {\n return DB::transaction(function () use ($orderNumber, $replacements) {\n $order = CustomerOrder::where('order_number', $orderNumber)->firstOrFail();\n\n \/\/ BOM \u3092\u73fe\u884c\u30c6\u30fc\u30d6\u30eb\u512a\u5148\u3067\u53d6\u5f97\u3001\u306a\u3051\u308c\u3070\u5c65\u6b74\u304b\u3089\u6700\u65b0\u3092\u53d6\u5f97\n $bom = Bom::find($order->bom_id)\n ?? BomHistory::where('bom_id', $order->bom_id)\n ->orderByDesc('effective_from')\n ->firstOrFail();\n\n \/\/ BOM\u660e\u7d30\u306f\u73fe\u884c\u3092\u60f3\u5b9a\uff08\u5c65\u6b74\u304c\u5fc5\u8981\u306a\u3089\u5225\u9014 bom_items_history \u3092\u7528\u610f\u3057\u3066\u540c\u69d8\u306b\u53d6\u5f97\uff09\n $items = BomItem::where('bom_id', $order->bom_id)->get();\n\n $lines = [];\n $oldTotal = 0;\n $newTotal = 0;\n\n foreach ($items as $item) {\n $oldPart = $this->findPartWithHistory($item->part_id);\n $qty = $item->quantity;\n\n $newPart = $oldPart;\n if (isset($replacements[$oldPart['part_number']])) {\n $newPart = $this->findPartByNumberWithHistory($replacements[$oldPart['part_number']]);\n }\n\n $oldSub = $oldPart['unit_price'] * $qty;\n $newSub = $newPart['unit_price'] * $qty;\n\n $lines[] = [\n 'old_part_number' => $oldPart['part_number'],\n 'new_part_number' => $newPart['part_number'],\n 'quantity' => $qty,\n 'old_price' => $oldPart['unit_price'],\n 'new_price' => $newPart['unit_price'],\n 'old_subtotal' => $oldSub,\n 'new_subtotal' => $newSub,\n 'replaced' => $oldPart['id'] !== $newPart['id'] || $oldPart['part_number'] !== $newPart['part_number'],\n ];\n\n $oldTotal += $oldSub;\n $newTotal += $newSub;\n }\n\n return [\n 'order_number' => $order->order_number,\n 'bom_id' => $order->bom_id,\n 'lines' => $lines,\n 'old_total' => $oldTotal,\n 'new_total' => $newTotal,\n 'diff' => $newTotal - $oldTotal,\n ];\n });\n }\n\n private function findPartWithHistory(int $partId): array\n {\n $part = Part::find($partId);\n if ($part) {\n return $part->toArray();\n }\n\n $hist = PartsHistory::where('part_id', $partId)\n ->orderByDesc('effective_from')\n ->firstOrFail();\n\n return [\n 'id' => $hist->part_id,\n 'part_number' => $hist->part_number,\n 'unit_price' => $hist->unit_price,\n ];\n }\n\n private function findPartByNumberWithHistory(string $partNumber): array\n {\n $part = Part::where('part_number', $partNumber)->first();\n if ($part) {\n return $part->toArray();\n }\n\n $hist = PartsHistory::where('part_number', $partNumber)\n ->orderByDesc('effective_from')\n ->firstOrFail();\n\n return [\n 'id' => $hist->part_id,\n 'part_number' => $hist->part_number,\n 'unit_price' => $hist->unit_price,\n ];\n }\n}\n<\/pre><\/div>\n\n\n\u666e\u6bb5\u306f where \u30e1\u30bd\u30c3\u30c9\u3092\u4f7f\u3063\u3066 parts, bom \u30c6\u30fc\u30d6\u30eb\u304b\u3089\u691c\u7d22\u3059\u308b\u306e\u3067\u3001\u7279\u306b\u554f\u984c\u306f\u3067\u307e\u305b\u3093\u3002\u904e\u53bb\u90e8\u54c1\u3060\u3063\u305f\u3068\u304d\u306b\u3001findPartWithHistory \u3084 findPartByNumberWithHistory \u30e1\u30bd\u30c3\u30c9\u3092\u4f7f\u3063\u3066\u5c65\u6b74\u30c6\u30fc\u30d6\u30eb\u304b\u3089\u6700\u65b0\u306e\u30c7\u30fc\u30bf\u3092\u53d6\u5f97\u3059\u308b\u3088\u3046\u306b\u3057\u3066\u3044\u307e\u3059\u3002
\u3053\u306e\u30b1\u30fc\u30b9\u306e\u5834\u5408\u306f\u300c\u904e\u53bb\u304b\u3089\u8aad\u307f\u53d6\u308b\u53ef\u80fd\u6027\u304c\u3042\u308b\u300d\u3053\u3068\u304c\u524d\u63d0\u306b\u306a\u3063\u3066\u3044\u308b\u306e\u3067\u3001withTrashed \u3092\u4f7f\u3046\u304b findPartWithHistory \u30e1\u30bd\u30c3\u30c9\u306e\u3088\u3046\u306a\u30d8\u30eb\u30d1\u30fc\u30e1\u30bd\u30c3\u30c9\u3092\u4f7f\u3046\u304b\u3068\u3044\u3046\u5224\u65ad\u306f\u30b3\u30fc\u30c7\u30a3\u30f3\u30b0\u6642\u306b\u5224\u660e\u3057\u305d\u3046\u3067\u3059\u3002<\/p>\n\n\n\n\u540c\u3058\u30d1\u30bf\u30fc\u30f3\u3092 C#\/LINQ \u3067\u3084\u3063\u3066\u307f\u307e\u3057\u3087\u3046\u3002
LINQ \u306e\u5834\u5408\u306f\u3001IgnoreQueryFilters \u30e1\u30bd\u30c3\u30c9\u306f\u4f7f\u308f\u305a\u306b\u3001\u5c65\u6b74\u30c6\u30fc\u30d6\u30eb\uff08BomHistory\u3001PartsHistory\uff09\u3078\u306e\u30a2\u30af\u30bb\u30b9\u3067\u904e\u53bb\u30c7\u30fc\u30bf\u306e\u7167\u5408\u3092\u884c\u3044\u307e\u3059\u3002<\/p>\n\n\n\n\/\/ \u7269\u7406\u524a\u9664\uff0b\u5c65\u6b74\u30c6\u30fc\u30d6\u30eb\u3067\u30b1\u30fc\u30b91\u3092\u51e6\u7406\u3059\u308b C#\/EF Core \u30b5\u30fc\u30d3\u30b9\u4f8b\nusing Microsoft.EntityFrameworkCore;\n\npublic class QuoteService\n{\n private readonly AppDbContext _db;\n public QuoteService(AppDbContext db) => _db = db;\n\n \/\/ replacements: \u53e4\u3044\u54c1\u756a\u2192\u65b0\u3057\u3044\u54c1\u756a\n public async Task<QuoteResult> BuildQuoteAsync(string orderNumber, Dictionary<string, string> replacements)\n {\n var order = await _db.CustomerOrders\n .AsNoTracking()\n .FirstOrDefaultAsync(o => o.OrderNumber == orderNumber)\n ?? throw new InvalidOperationException("order not found");\n\n \/\/ BOM \u306f\u73fe\u884c\u512a\u5148\u3001\u7121\u3051\u308c\u3070\u5c65\u6b74\u304b\u3089\u6700\u65b0\u3092\u53d6\u5f97\n var bom = await _db.Boms.AsNoTracking()\n .FirstOrDefaultAsync(b => b.Id == order.BomId)\n ?? await _db.BomHistory.AsNoTracking()\n .Where(h => h.BomId == order.BomId)\n .OrderByDescending(h => h.EffectiveFrom)\n .Select(h => new Bom { Id = h.BomId, ProductNumber = h.ProductNumber, ProductName = h.ProductName })\n .FirstOrDefaultAsync()\n ?? throw new InvalidOperationException("bom not found");\n\n \/\/ BOM \u660e\u7d30\u306f\u73fe\u884c\u3092\u60f3\u5b9a\uff08\u5c65\u6b74\u304c\u5fc5\u8981\u306a\u3089 bom_items_history \u3082\u540c\u69d8\u306b\u30d5\u30a9\u30fc\u30eb\u30d0\u30c3\u30af\uff09\n var items = await _db.BomItems\n .Where(i => i.BomId == order.BomId)\n .AsNoTracking()\n .ToListAsync();\n\n var lines = new List<QuoteLine>();\n decimal oldTotal = 0, newTotal = 0;\n\n foreach (var item in items)\n {\n var oldPart = await FindPartWithHistoryAsync(item.PartId);\n var qty = item.Quantity;\n\n var newPart = oldPart;\n if (replacements.TryGetValue(oldPart.PartNumber, out var newPn))\n {\n newPart = await FindPartByNumberWithHistoryAsync(newPn);\n }\n\n var oldSub = oldPart.UnitPrice * qty;\n var newSub = newPart.UnitPrice * qty;\n\n lines.Add(new QuoteLine\n {\n OldPartNumber = oldPart.PartNumber,\n NewPartNumber = newPart.PartNumber,\n Quantity = qty,\n OldPrice = oldPart.UnitPrice,\n NewPrice = newPart.UnitPrice,\n OldSubtotal = oldSub,\n NewSubtotal = newSub,\n Replaced = oldPart.Id != newPart.Id || oldPart.PartNumber != newPart.PartNumber\n });\n\n oldTotal += oldSub;\n newTotal += newSub;\n }\n\n return new QuoteResult\n {\n OrderNumber = order.OrderNumber,\n BomId = bom.Id,\n Lines = lines,\n OldTotal = oldTotal,\n NewTotal = newTotal,\n Diff = newTotal - oldTotal\n };\n }\n\n private async Task<PartDto> FindPartWithHistoryAsync(long partId)\n {\n var part = await _db.Parts.AsNoTracking().FirstOrDefaultAsync(p => p.Id == partId);\n if (part != null) return new PartDto(part);\n\n var hist = await _db.PartsHistory.AsNoTracking()\n .Where(h => h.PartId == partId)\n .OrderByDescending(h => h.EffectiveFrom)\n .FirstOrDefaultAsync()\n ?? throw new InvalidOperationException("part not found");\n\n return new PartDto(hist.PartId, hist.PartNumber, hist.UnitPrice);\n }\n\n private async Task<PartDto> FindPartByNumberWithHistoryAsync(string partNumber)\n {\n var part = await _db.Parts.AsNoTracking().FirstOrDefaultAsync(p => p.PartNumber == partNumber);\n if (part != null) return new PartDto(part);\n\n var hist = await _db.PartsHistory.AsNoTracking()\n .Where(h => h.PartNumber == partNumber)\n .OrderByDescending(h => h.EffectiveFrom)\n .FirstOrDefaultAsync()\n ?? throw new InvalidOperationException("replacement not found");\n\n return new PartDto(hist.PartId, hist.PartNumber, hist.UnitPrice);\n }\n}\n\npublic record QuoteResult\n{\n public string OrderNumber { get; init; }\n public long BomId { get; init; }\n public List<QuoteLine> Lines { get; init; } = new();\n public decimal OldTotal { get; init; }\n public decimal NewTotal { get; init; }\n public decimal Diff { get; init; }\n}\n\npublic record QuoteLine\n{\n public string OldPartNumber { get; init; }\n public string NewPartNumber { get; init; }\n public int Quantity { get; init; }\n public decimal OldPrice { get; init; }\n public decimal NewPrice { get; init; }\n public decimal OldSubtotal { get; init; }\n public decimal NewSubtotal { get; init; }\n public bool Replaced { get; init; }\n}\n\npublic record PartDto\n{\n public PartDto(Part p) : this(p.Id, p.PartNumber, p.UnitPrice) { }\n public PartDto(long id, string partNumber, decimal unitPrice)\n {\n Id = id; PartNumber = partNumber; UnitPrice = unitPrice;\n }\n public long Id { get; }\n public string PartNumber { get; }\n public decimal UnitPrice { get; }\n}\n<\/pre><\/div>\n\n\n\u5c65\u6b74\u30c6\u30fc\u30d6\u30eb\u3092\u53c2\u7167\u3059\u308b\u5206\u3060\u3051\u3001\u7269\u7406\u524a\u9664\u306e C#\/LINQ \u306e\u30b3\u30fc\u30c9\u306e\u65b9\u304c\u9577\u304f\u306f\u306a\u308a\u307e\u3059\u304c\u3001\u660e\u793a\u7684\u306b BomHistory \u3084 PartsHistory \u3092\u53c2\u7167\u3057\u3066\u3044\u308b\u306e\u3067\u3001\u30b3\u30fc\u30c9\u3092\u8aad\u3093\u3060\u3068\u304d\u306b\u300c\u904e\u53bb\u30c7\u30fc\u30bf\u3092\u53c2\u7167\u3057\u3066\u3044\u308b\u300d\u3053\u3068\u304c\u5206\u304b\u308a\u3084\u3059\u3044\u3068\u3044\u3046\u5229\u70b9\u304c\u3042\u308a\u307e\u3059\u3002<\/p>\n\n\n\n
\u30b1\u30fc\u30b92<\/strong><\/h2>\n\n\n\n
\u3082\u3046\u3072\u3068\u3064\u30b1\u30fc\u30b9\u3092\u8003\u3048\u3066\u307f\u307e\u3057\u3087\u3046\u3002<\/p>\n\n\n\n
\u5ba2\u5148\u304b\u3089\u767a\u6ce8\u66f8\u3092\u53d7\u3051\u305f\u304c\u3001\u767a\u6ce8\u66f8\u306b\u8a18\u8f09\u3055\u308c\u3066\u3044\u308b\u90e8\u54c1\u756a\u53f7\u3092\u30b7\u30b9\u30c6\u30e0\u3067\u691c\u7d22\u3059\u308b\u3068\u51fa\u3066\u6765\u306a\u3044\u3002\u3069\u3046\u3084\u3089\u3001\u4ee5\u524d\u306e\u898b\u7a4d\u3082\u308a\u3092\u51fa\u3057\u305f\u6642\u306b\u306f\u3001\u8abf\u9054\u53ef\u80fd\u3060\u3063\u305f\u3082\u306e\u3067\u7d44\u307f\u5408\u308f\u305b\u756a\u53f7\u3092\u4f5c\u6210\u3057\u3066\u3044\u305f\u306e\u3060\u304c\u3001\u305d\u306e\u5f8c\u306b\u5ec3\u76e4\u306b\u306a\u3063\u3066\u3057\u307e\u3063\u305f\u3089\u3057\u3044\u3002\u7d44\u307f\u5408\u308f\u305b\u756a\u53f7\u306e\u6574\u5099\u304c\u8ffd\u3044\u4ed8\u304b\u305a\u3001\u90e8\u54c1\u306e\u7d44\u307f\u5408\u308f\u305b\u306e\u307b\u3046\u304c\u6709\u52b9\u306b\u306a\u3063\u3066\u3044\u308b\u304c\u3001\u90e8\u54c1\u756a\u53f7\u3092\u8abf\u3079\u308b\u3068\u753b\u9762\u306b\u51fa\u3066\u6765\u306a\u3044\u3068\u3044\u3046\u72b6\u6cc1\u306b\u306a\u3063\u3066\u3057\u307e\u3063\u3066\u3044\u308b\u3002<\/p>\n\n\n\n
\u767a\u6ce8\u66f8\u306b\u7d44\u307f\u5408\u308f\u305b\u756a\u53f7\u304c\u66f8\u3044\u3066\u3042\u3063\u305f\u3068\u304d\u306b\u3001\u305d\u306e\u7d44\u307f\u5408\u308f\u305b\u306b\u542b\u307e\u308c\u308b\u90e8\u54c1\u756a\u53f7\u304c\u6709\u52b9\u3067\u3042\u308b\u304b\u3069\u3046\u304b\u306e\u30c1\u30a7\u30c3\u30af\u3092\u884c\u3044\u305f\u3044\u30021\u56de\u76ee\u306e\u30c1\u30a7\u30c3\u30af\u3067\u306f\u3001\u73fe\u72b6\u8abf\u9054\u3067\u304d\u308b\u90e8\u54c1\u3060\u3051\u3067\u30c1\u30a7\u30c3\u30af\u3092\u3059\u308b\u304c\u30012\u56de\u76ee\u306e\u30c1\u30a7\u30c3\u30af\u3067\u306f\u3001\u5c65\u6b74\u30c6\u30fc\u30d6\u30eb\u3082\u53c2\u7167\u3057\u3066\u3001\u904e\u53bb\u306b\u5b58\u5728\u3057\u305f\u90e8\u54c1\u756a\u53f7\u3082\u542b\u3081\u3066\u30c1\u30a7\u30c3\u30af\u3092\u884c\u3044\u305f\u3044\u3002
1\u56de\u76ee\u30682\u56de\u76ee\u306e\u30c1\u30a7\u30c3\u30af\u306e\u30e1\u30bd\u30c3\u30c9\u3092\u4f5c\u6210\u3057\u3066\u6b32\u3057\u3044\u3002
\u5165\u529b\u306f customer_orders.order_number \u3068\u3057\u305f\u3044\u3002<\/p>\n\n\n\n\u8ad6\u7406\u524a\u9664\u306e\u30c7\u30fc\u30bf\u69cb\u9020\u306e\u5834\u5408<\/strong><\/h3>\n\n\n\n
\u3053\u306e\u5834\u5408\u306f\u30011\u56de\u76ee\u304c\u73fe\u884c\u306e\u30c6\u30fc\u30d6\u30eb\u3067\u78ba\u8a8d\u3002\u3053\u3053\u3067\u5b58\u5728\u3057\u306a\u3044\u306e\u3067\u3001\u78ba\u8a8d\u306e\u305f\u3081\u306b2\u56de\u76ee\u3067\u5c65\u6b74\u3092\u63a2\u3059\u3068\u3044\u3046\u4e8c\u6bb5\u968e\u306e\u6d41\u308c\u306b\u306a\u308a\u307e\u3059\u3002<\/p>\n\n\n
\nnamespace App\\Services;\n\nuse App\\Models\\CustomerOrder;\nuse App\\Models\\Bom;\nuse App\\Models\\BomItem;\nuse App\\Models\\Part;\n\nclass BomCheckService\n{\n \/\/ \u73fe\u884c\u306e\u307f\uff08deleted_at IS NULL\uff09\n public function checkCurrentByOrder(string $orderNumber): array\n {\n $order = CustomerOrder::where('order_number', $orderNumber)->firstOrFail();\n $bom = Bom::findOrFail($order->bom_id);\n\n $partIds = BomItem::where('bom_id', $bom->id)->pluck('part_id')->all();\n $existing = Part::whereIn('id', $partIds)->pluck('id')->all(); \/\/ \u73fe\u884c\u306e\u307f\n $missingId = array_values(array_diff($partIds, $existing));\n $missingPn = Part::withTrashed()->whereIn('id', $missingId)->pluck('part_number')->all();\n\n return [\n 'bom_id' => $bom->id,\n 'missing' => $missingPn,\n ];\n }\n\n \/\/ \u5c65\u6b74\u3082\u542b\u3081\u308b\uff08withTrashed \u3067\u8ad6\u7406\u524a\u9664\u3092\u8a31\u5bb9\uff09\n public function checkWithHistoryByOrder(string $orderNumber): array\n {\n $order = CustomerOrder::where('order_number', $orderNumber)->firstOrFail();\n $bom = Bom::withTrashed()->findOrFail($order->bom_id);\n\n $partIds = BomItem::where('bom_id', $bom->id)->pluck('part_id')->all();\n $allParts = Part::withTrashed()->whereIn('id', $partIds)->get(['id','part_number','deleted_at']);\n $foundIds = $allParts->pluck('id')->all();\n $missingId = array_values(array_diff($partIds, $foundIds));\n\n $foundInHistory = $allParts->filter(fn($p) => $p->deleted_at !== null)\n ->pluck('part_number')->all();\n $unknownPn = Part::withTrashed()->whereIn('id', $missingId)->pluck('part_number')->all();\n\n return [\n 'bom_id' => $bom->id,\n 'missing' => $unknownPn, \/\/ \u73fe\u884c\u306b\u3082\u5c65\u6b74\u306b\u3082\u7121\u3044\n 'found_in_history' => $foundInHistory, \/\/ \u8ad6\u7406\u524a\u9664\u6e08\u307f\uff08\u904e\u53bb\u5b58\u5728\uff09\n ];\n }\n}\n\n<\/pre><\/div>\n\n\n\u7269\u7406\u524a\u9664\u306e\u30c7\u30fc\u30bf\u69cb\u9020\u306e\u5834\u5408<\/strong><\/h3>\n\n\n\n
\u3053\u3063\u3061\u306f\u3001BomHistory, PartsHistory \u3092\u53c2\u7167\u3057\u3066\u5c65\u6b74\u3092\u78ba\u8a8d\u3059\u308b\u5fc5\u8981\u304c\u3042\u308a\u307e\u3059\u3002
\u4e21\u65b9\u306e\u69cb\u9020\u3092\u898b\u308b\u3068\u3001\u8ad6\u7406\u524a\u9664\u3067\u3082\u7269\u7406\u524a\u9664\u3067\u3082\u3042\u307e\u308a\u5909\u308f\u3089\u306a\u3044\u30b3\u30fc\u30c9\u306b\u306a\u3063\u3066\u3044\u308b\u306e\u3067\u3001\u30b3\u30fc\u30c9\u306e\u8a18\u8ff0\u30ec\u30d9\u30eb\u3067\u306f\u3001\u3069\u3061\u3089\u3092\u9078\u3093\u3067\u3082\u3088\u3055\u305d\u3046\u306a\u6c17\u304c\u3057\u307e\u3059\u3002
\u305f\u3060\u3001\u3053\u306e\u30c6\u30fc\u30d6\u30eb\u69cb\u9020\u306e\u5834\u5408\u306f\u3001customer_orders \u3068 parts \u30c6\u30fc\u30d6\u30eb\u304c\u3084\u3084\u8fd1\u3044\u3068\u3053\u308d\u306b\u3042\u308b\u306e\u3067\u3001\u305d\u3053\u3060\u3051\u898b\u308c\u3070\u69cb\u9020\u304c\u5206\u304b\u308a\u3084\u3059\u3044\u305f\u3081\u3068\u3082\u8a00\u3048\u307e\u3059\u306d\u3002<\/p>\n\n\n\nnamespace App\\Services;\n\nuse App\\Models\\CustomerOrder;\nuse App\\Models\\Bom;\nuse App\\Models\\BomHistory;\nuse App\\Models\\BomItem;\nuse App\\Models\\Part;\nuse App\\Models\\PartsHistory;\n\nclass BomCheckService\n{\n \/\/ 1\u56de\u76ee: \u73fe\u884c\u306e\u307f\uff08\u7269\u7406\u524a\u9664\u306a\u306e\u3067\u5c65\u6b74\u306f\u898b\u306a\u3044\uff09\n public function checkCurrentByOrder(string $orderNumber): array\n {\n $order = CustomerOrder::where('order_number', $orderNumber)->firstOrFail();\n $bom = Bom::findOrFail($order->bom_id);\n\n $partIds = BomItem::where('bom_id', $bom->id)->pluck('part_id')->all();\n $existing = Part::whereIn('id', $partIds)->pluck('id')->all();\n $missingId = array_values(array_diff($partIds, $existing));\n $missingPn = PartsHistory::whereIn('part_id', $missingId)\n ->orderBy('effective_from', 'desc')\n ->pluck('part_number')\n ->unique()\n ->values()\n ->all();\n\n return [\n 'bom_id' => $bom->id,\n 'missing' => $missingPn, \/\/ \u73fe\u884c\u30c6\u30fc\u30d6\u30eb\u306b\u7121\u3044\u90e8\u54c1\u756a\u53f7\n ];\n }\n\n \/\/ 2\u56de\u76ee: \u5c65\u6b74\u3082\u542b\u3081\u3066\u5b58\u5728\u78ba\u8a8d\uff08parts_history \/ bom_history \u3092\u53c2\u7167\uff09\n public function checkWithHistoryByOrder(string $orderNumber): array\n {\n $order = CustomerOrder::where('order_number', $orderNumber)->firstOrFail();\n\n \/\/ BOM: \u73fe\u884c\u512a\u5148\u3001\u7121\u3051\u308c\u3070\u5c65\u6b74\u306e\u6700\u65b0\u3092\u63a1\u7528\n $bom = Bom::find($order->bom_id)\n ?? BomHistory::where('bom_id', $order->bom_id)\n ->orderByDesc('effective_from')\n ->firstOrFail();\n\n $partIds = BomItem::where('bom_id', $order->bom_id)->pluck('part_id')->all();\n\n \/\/ \u73fe\u884c + \u5c65\u6b74\n $current = Part::whereIn('id', $partIds)->get(['id','part_number']);\n $hist = PartsHistory::whereIn('part_id', $partIds)\n ->orderByDesc('effective_from')\n ->get(['part_id','part_number']);\n\n $foundIds = $current->pluck('id')->all();\n $histIds = $hist->pluck('part_id')->all();\n $missingId = array_values(array_diff($partIds, array_unique(array_merge($foundIds, $histIds))));\n\n $foundInHistory = $hist->pluck('part_number')->unique()->values()->all();\n $unknownPn = PartsHistory::whereIn('part_id', $missingId)\n ->orderByDesc('effective_from')\n ->pluck('part_number')\n ->unique()\n ->values()\n ->all();\n\n return [\n 'bom_id' => $bom->id,\n 'missing' => $unknownPn, \/\/ \u73fe\u884c\u306b\u3082\u5c65\u6b74\u306b\u3082\u7121\u3044\n 'found_in_history' => $foundInHistory, \/\/ \u5c65\u6b74\u3067\u5b58\u5728\u3057\u305f\u90e8\u54c1\u756a\u53f7\n ];\n }\n}\n\n<\/pre><\/div>\n\n\n\u3053\u308c\u3060\u3051\u3060\u3068\u3001\u53cc\u65b9\u3067\u5dee\u7570\u304c\u307f\u3089\u308c\u306a\u3044\u306e\u3067\u3001\u5f15\u304d\u7d9a\u304d\u3002
<\/p>\n","protected":false},"excerpt":{"rendered":"\u8ad6\u7406\u524a\u9664\u3068\u7269\u7406\u524a\u9664\u306e\u6bd4\u8f03\u306e\u305f\u3081\u306e\u30c6\u30fc\u30d6\u30eb\u8a2d\u8a08\u304c\u3072\u3068\u901a\u308a\u3067\u304d\u305f\u306e\u3067\u3001\u5177\u4f53\u7684\u306b\u3069\u306e\u3088\u3046\u306b\u4f7f\u308f\u308c\u308b\u306e\u304b\u3092\u8003\u3048\u3066\u307f\u307e\u3057\u3087\u3046\u3002 \u3053\u306e\u624b\u306e\u3084\u3064\u306f\u3001\u3044\u3061\u3070\u3093\u8907\u96d1\u3060\u3068\u601d\u308f\u308c\u308b\u300c\u5de5\u5834\u306e\u90e8\u54c1\u53d7\u6ce8\u30b7\u30b9\u30c6\u30e0\u300d\u3092\u4f8b\u306b\u3068\u308b\u3068\u3088\u3044\u3067\u3059\u3002\u4e0b\u624b\u306b\u3001\u3084\u3055\u3057 … \u7d9a\u304d\u3092\u8aad\u3080
![\"\"](\"https:\/\/www.moonmile.net\/blog\/wp-content\/uploads\/2025\/12\/image-57.png\")
<\/a><\/figure>\n\n\n\n