将更新的产品价格推送到Opencart 2.x中的过去订单

Question

Attempting to push new product prices to past orders. Essentially, when I edit the product price it needs to update in all orders containing that price Here's the code:

public function editProduct($product_id, $data) {
    $this->event->trigger('pre.admin.product.edit', $data);

    $this->db->query("UPDATE " . DB_PREFIX . "product SET model = '" . $this->db->escape($data['model']) . "', sku = '" . $this->db->escape($data['sku']) . "', upc = '" . $this->db->escape($data['upc']) . "', ean = '" . $this->db->escape($data['ean']) . "', jan = '" . $this->db->escape($data['jan']) . "', isbn = '" . $this->db->escape($data['isbn']) . "', mpn = '" . $this->db->escape($data['mpn']) . "', location = '" . $this->db->escape($data['location']) . "', quantity = '" . (int)$data['quantity'] . "', minimum = '" . (int)$data['minimum'] . "', subtract = '" . (int)$data['subtract'] . "', stock_status_id = '" . (int)$data['stock_status_id'] . "', date_available = '" . $this->db->escape($data['date_available']) . "', manufacturer_id = '" . (int)$data['manufacturer_id'] . "', shipping = '" . (int)$data['shipping'] . "', price = '" . (float)$data['price'] . "', points = '" . (int)$data['points'] . "', weight = '" . (float)$data['weight'] . "', weight_class_id = '" . (int)$data['weight_class_id'] . "', length = '" . (float)$data['length'] . "', width = '" . (float)$data['width'] . "', height = '" . (float)$data['height'] . "', length_class_id = '" . (int)$data['length_class_id'] . "', status = '" . (int)$data['status'] . "', tax_class_id = '" . (int)$data['tax_class_id'] . "', sort_order = '" . (int)$data['sort_order'] . "', date_modified = NOW() WHERE product_id = '" . (int)$product_id . "'");

//Push Product Edits //
    $product_qry = $this->db->query("SELECT price FROM " . DB_PREFIX . "product WHERE product_id = '" . (int)$product_id . "'");
    $new_price = $product_qry->row['price'];

    $order_product_id_qry = $this->db->query("SELECT order_product_id FROM " . DB_PREFIX . "order_product");
    $order_product_id = $order_product_id_qry->row['order_product_id'];

    $order_qry = $this->db->query("SELECT quantity FROM " . DB_PREFIX . "order_product WHERE order_product_id = '" . (int)$order_product_id . "'");
    $product_quantity = $order_qry->row['quantity'];

    $new_total = $product_quantity * $new_price;

    $this->db->query("UPDATE " . DB_PREFIX . "order_product SET price = '" . $new_price . "' WHERE product_id = '" . (int)$product_id ."'");
    $this->db->query("UPDATE " . DB_PREFIX . "order_product SET total = '" . $new_total . "' WHERE order_product_id = '" . (int)$order_product_id . "'");

Updating the price is working great. I am no longer receiving an errors, but the order total is not updating. Any idea why?

Answer 1

I added a MYSQL Trigger onto the DB

CREATE TRIGGER `after_product_update` AFTER UPDATE ON `oc_product` FOR EACH ROW
BEGIN
    UPDATE `order_product`
    SET `price` = NEW.`price`, `total` = `quantity` * NEW.`price` WHERE `product_id` = NEW.`product_id`;
END

Do note other triggers have to be added to the tables oc_order_total in order to update the Subtotal and Total fields there.