Changing order numbers [#2651200]

I would like to inquire if it is possible to change order numbers on already existing orders in our store. For example, say we have order numbers 1 through 507 in our store, if I want to change existing order #280 and make it #352 instead, how is that done? Is there any way to do this in the store admin area? Or is this something that I have to do in the site database? If you could walk me through the process of doing this for one order, I'd appreciate it. I'm assuming it is doable in some way, but the order # we want to change it to must be available without another order already having that number. We plan to delete all the orders occupying the order numbers we want to change certain orders to use. Thanks in advance for any assistance you can provide.

Comments

Comment #1

15 January 2016 at 22:21

wildlife created an issue. See original summary.

Comment #2

wildlife CreditAttribution: wildlife commented 16 January 2016 at 00:14

Issue summary:

View changes

Comment #3

TR CreditAttribution: TR commented 16 January 2016 at 02:18

The order_id is the primary key of the uc_orders table, and many other tables use that as an index to join their data to the order data. In theory you could write a little piece of standalone code to order_load, clone the order, change the order_id of the cloned order, save the clone, then delete the original. All of this preferably in a transaction. Maybe 15 lines of code total, which includes bootstrapping Drupal. In practice, I'm not sure than any contributed modules you have installed will have implemented the correct order delete and order save hooks, so some order data might not get modified. So what I would do is to set up a test site and make a test order so you have one and only one order in the database. Try writing and running the above code and see if all the order data gets carried over. You could also look at your database and see which tables have order_id stored in them, and make sure the order_id gets properly changed.

Or you could just manually change your tables one-by-one. Tedious but do-able if you only have to move one order. But if you do end up writing a standalone script then I'd appreciate it if you'd post it here for future reference.

Renumbering orders is not something I want to encourage for general use, because it's bad from an accounting point of view if nothing else, so it's not a feature I would want to put into core Ubercart, but it's useful to have the code posted in this queue for people who need it. (Note there are also module like node_renumber and node_clone, I don't know if they work for general entities like order but I think that since you're only doing one of these it's probably more effort than it's worth to test them / try to get them working for orders.)

Comment #4

TR CreditAttribution: TR commented 16 January 2016 at 07:15

Status:

Active

» Postponed (maintainer needs more info)

Comment #5

wildlife CreditAttribution: wildlife commented 17 January 2016 at 16:44

I'm not at a skill level of being able to write my own standalone script for this. I have received what you've sent me via e-mail to possibly accomplish this. I could test it on a separate test site, but I don't think I'd be able to do much in the way of modifying it to perfect it if anything goes wrong.

In our current situation, we have to change the order numbers on 25 different orders.

Do I understand it correctly that merely changing that number in whatever way to the number it needs to be may have unintended consequences with how other Ubercart contributed modules handle the orders? And that the script you sent me does things in a way where the contributed modules would handle the new orders properly?

Where you give the option that I could manually change the order tables one by one, can you explore that approach more for me? I'm willing to do the tedious work if I know exactly what it is that I need to do. That may be an easier option for me than setting up a test site and working with scripts. Is it a matter of finding all MySQL database tables containing Order ID # and changing the value in there for each order?

Our situation is resulting from a Drupal 6 to Drupal 7 upgrade. I advised that we shut down the client's store during the transition period to avoid this problem so the orders database would remain frozen. But the client wanted to keep it open, so we did, figuring we could manually add the orders afterwards. The Drupal 6 site on the real domain was kept fully active through the development of the Drupal 7 version. The site database for the Drupal 7 version of the site only had orders through December 14th in it. But the Drupal 6 version of the store has received orders after that date. Some additional orders were added to the Drupal 7 version during development for testing that now makes it to where the order numbers sequence will be totally off if we manually recreate the missing recent orders in the Drupal 7 system. Ideally, we would just manually add the orders needed, and then change their order numbers after that to match the order numbers present in the Drupal 6 site where the orders were actually placed. Any additional thoughts to this now that you know the full back-story? I realize things were not done in an ideal way with this.

Comment #6

TR CreditAttribution: TR commented 22 January 2016 at 20:08

Where you give the option that I could manually change the order tables one by one, can you explore that approach more for me? I'm willing to do the tedious work if I know exactly what it is that I need to do. That may be an easier option for me than setting up a test site and working with scripts. Is it a matter of finding all MySQL database tables containing Order ID # and changing the value in there for each order?

You can do this all within phpMyAdmin. First identify all the tables that have a column named order_id. All these order ids will have to be changed the same way at the same time. I suggest writing a set of SQL queries that you can execute all at once from within phpMyAdmin. Try it on a copy of your database first. Make a backup of your database before you try it on your real site. Put your site into maintenance mode before you run the queries so that you don't get new orders while you're trying to renumber the old ones. You can also edit all the order ids manually, but that would take a long time (you probably don't want your site offline for an entire day) and is prone to error - you might miss one table or type the wrong number at some point. Fixing errors with the renumbering could be more time-consuming than the renumbering itself, so it's a good idea to write out the SQL queries ahead of time so you can examine them, fix them, debug them, and test them before doing it for real.