Using PHP to develop an enterprise resource planning (ERP) system that implements order tracking functions

-- 创建订单表
CREATE TABLE orders (
  id INT PRIMARY KEY AUTO_INCREMENT,
  order_number VARCHAR(50) NOT NULL,
  customer_name VARCHAR(100) NOT NULL,
  status ENUM('待处理', '处理中', '已完成') NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建订单历史记录表
CREATE TABLE order_history (
  id INT PRIMARY KEY AUTO_INCREMENT,
  order_id INT NOT NULL,
  status ENUM('待处理', '处理中', '已完成') NOT NULL,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (order_id) REFERENCES orders(id)
);

class Order {
  private $conn;

  public function __construct($conn) {
    $this->conn = $conn;
  }

  // 创建订单
  public function createOrder($orderNumber, $customerName) {
    $status = '待处理';

    $sql = "INSERT INTO orders (order_number, customer_name, status) VALUES (?, ?, ?)";
    $stmt = $this->conn->prepare($sql);
    $stmt->bind_param("sss", $orderNumber, $customerName, $status);
    $stmt->execute();
    $stmt->close();
  }

  // 更新订单状态
  public function updateOrderStatus($orderId, $status) {
    $sql = "UPDATE orders SET status = ? WHERE id = ?";
    $stmt = $this->conn->prepare($sql);
    $stmt->bind_param("si", $status, $orderId);
    $stmt->execute();
    $stmt->close();

    // 记录订单状态变更历史
    $this->recordOrderHistory($orderId, $status);
  }

  // 记录订单状态变更历史
  private function recordOrderHistory($orderId, $status) {
    $sql = "INSERT INTO order_history (order_id, status) VALUES (?, ?)";
    $stmt = $this->conn->prepare($sql);
    $stmt->bind_param("is", $orderId, $status);
    $stmt->execute();
    $stmt->close();
  }

  // 获取订单详情
  public function getOrderDetail($orderId) {
    $sql = "SELECT o.*, oh.status AS history_status, oh.updated_at AS history_updated_at
            FROM orders o
            LEFT JOIN order_history oh ON o.id = oh.order_id
            WHERE o.id = ?";
    $stmt = $this->conn->prepare($sql);
    $stmt->bind_param("i", $orderId);
    $stmt->execute();
    $result = $stmt->get_result();
    $orderDetail = $result->fetch_assoc();
    $stmt->close();

    return $orderDetail;
  }
}

// 初始化数据库连接
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "erp";

$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
  die("连接数据库失败：" . $conn->connect_error);
}

$order = new Order($conn);

// 创建订单
$order->createOrder("202101010001", "张三");

// 更新订单状态为处理中
$order->updateOrderStatus(1, '处理中');

// 更新订单状态为已完成
$order->updateOrderStatus(1, '已完成');

// 获取订单详情
$orderDetail = $order->getOrderDetail(1);