Post by Syztemlord » Wed Oct 27, 2021 1:38 am

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?

Newbie

Posts

Joined
Sun Nov 26, 2017 6:12 pm

Post by thekrotek » Wed Oct 27, 2021 2:21 am

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


User avatar
Expert Member

Posts

Joined
Sun Jul 03, 2016 12:24 am


Post by mikeinterserv » Wed Oct 27, 2021 2:55 am

It should be
Date created and date modified.
Date created should never be changed.
Statuses should only change date modified.

Active Member

Posts

Joined
Thu May 28, 2020 6:55 am
Location - Wales

Post by Syztemlord » Wed Oct 27, 2021 5:01 am

thekrotek wrote:
Wed Oct 27, 2021 2:21 am
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)
Excellent, thank you. Will give this a go.

Newbie

Posts

Joined
Sun Nov 26, 2017 6:12 pm

Post by Syztemlord » Wed Oct 27, 2021 5:17 am

thekrotek wrote:
Wed Oct 27, 2021 2:21 am
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)
Just tried it but I get the following error:
#1242 - Subquery returns more than 1 row

Newbie

Posts

Joined
Sun Nov 26, 2017 6:12 pm

Post by straightlight » Wed Oct 27, 2021 9:30 pm

Syztemlord wrote:
Wed Oct 27, 2021 5:17 am
thekrotek wrote:
Wed Oct 27, 2021 2:21 am
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)
Just tried it but I get the following error:
#1242 - Subquery returns more than 1 row
An e.g from this post: viewtopic.php?f=202&t=225821&p=830101#p830088 , with a little modification:

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


Legendary Member

Posts

Joined
Mon Nov 14, 2011 11:38 pm
Location - Canada, ON
Who is online

Users browsing this forum: Adminas99999 and 137 guests