Hi guys,
I made a mistake of adding some additional order statuses as Processing Order Status in settings. Now every order I have changed the status has also changed the date added. So when I run a report some of these orders are now showing in the wrong month.
I think I can change it back with an SQL command in the database but I'm not sure how I can achieve this if anyone can help?
It would go a little like this (but in SQL terms):
Look at oc_order_history, if order status =5 then change date_added in corresponding order in oc_order to same date_added as in oc_order_history
The order status 5 would have been the status when it was originally created.
Hope that made sense and if anyone can help?
UPDATE oc_order AS o SET o.date_added = (SELECT date_added FROM oc_order_history WHERE order_id = o.order_id AND order_status_id = 5)
Professional OpenCart extensions, support and custom work.
Contact me via email or Skype by support@thekrotek.com
It should be
Date created and date modified.
Date created should never be changed.
Statuses should only change date modified.
Date created and date modified.
Date created should never be changed.
Statuses should only change date modified.
An e.g from this post: viewtopic.php?f=202&t=225821&p=830101#p830088 , with a little modification:Syztemlord wrote: ↑Wed Oct 27, 2021 5:17 amJust tried it but I get the following error:
#1242 - Subquery returns more than 1 row
Code: Select all
require_once(DIR_SYSTEM . 'helper/db_schema.php');
$tables = db_schema();
foreach ($tables as $table) {
if ($table['name'] == 'order_history') {
$table_query = $this->db->query("SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = '" . DB_DATABASE . "' AND TABLE_NAME = '" . DB_PREFIX . $table['name'] . "'");
if ($table_query->num_rows) {
for ($i = 0; $i < count($table['field']); $i++) {
if (($table['field'][$i]['type'] == 'date' || $table['field'][$i]['type'] == 'datetime') && $table['field'][$i]['name'] == 'date_added') {
$query = $this->db->query("SELECT oh.`date_added` FROM `" . DB_PREFIX . "order_history` oh INNER JOIN `" . DB_PREFIX . "order` o ON (oh.`order_id` = o.`order_id`) WHERE o.`order_status_id` = '5'");
if ($query->num_rows) {
foreach ($query->rows as $result) {
$this->db->query("UPDATE `order` SET `date_added` = DATE('" . $result['date_added'] . "') WHERE `order_status_id` = '5'");
}
}
}
}
}
}
}
Dedication and passion goes to those who are able to push and merge a project.
Regards,
Straightlight
Programmer / Opencart Tester