18 replies [Last post]
Wonder95's picture
Offline
Joined: 02/20/2008
Juice: 283
Was this information Helpful?

Currently, Ubercart only supports single-number order numbers with digits and no other values or characters. However, there are times when it would be nice to have some sort of prefix (the year is commonly used as an option, i.e. 08-50 being the 50th order of 2008). My suggestion is to add some functionality that allows the user to specify if a prefix is to be used with the order number, and if so, what it is. This could be combined with the token module (like pathauto does) where the user could specify any number of values to use as a prefix. Since the uc_order module seems to be just using the next highest number stored in uc_orders_order_id in the sequences table, the prefix would just have to be prepended to it. However, this might (would) also require another option to allow the uc_orders_order_id value to be reset if necessary (such as at the beginning of the next year if the year was used as the prefix, for example).

torgosPizza's picture
Offline
Bug FinderEarly adopter... addicted to alphas.Getting busy with the Ubercode.
Joined: 08/14/2007
Juice: 4111
Re: Enhance order number format options

I had talked about this way in the beginning when UC was still in Alpha. Unfortunately the issue right now is that all order_id numbers are integers. I was able to, with some mucking around, use just a timestamp as an order_id... but incremented by the system itself 1 at a time, as opposed to an actual timestamp (because we found some orders getting lost or overwritten .. although that issue may be moot now).

One idea I had, and this is something I haven't mentioned yet, would be an "alias" for order numbers. In other words you would leave the UC system as is, but using some hooks and fancy custom module work, you could append a "customer invoice id" or something to that effect that could be tokenized, and in a separate database table, you just keep track of the relationships between order_id and invoice_id.

At least, it was a fleeting thought, and I'm not sure what it would take or what the specific benefits would be or how exactly you'd want it integrated. But it's probably the easiest way to do that sort of thing without overhauling the entire database schema, and not break anything.

--
Help directly fund development: Donate via PayPal!

Wonder95's picture
Offline
Joined: 02/20/2008
Juice: 283
Re: Re: Enhance order number format options

I was thinking along the lines of adding a prefix field (varchar, char, something like that) to the uc_orders table, and then just combining the two fields to create the order number when the order is displayed (doing something like converting the mediumint to a string so they could be concatenated). Then, you could do something like an admin setting to specify what the value is for the prefix field, such as the last two digits of the current year, or some other token value.

One thing I don't understand is why the value in the sequences table is used instead of an auto_increment value. With MySQL you can alter the value of an auto_increment field, either as the table is created or after it is created (however, this is with MyISAM tables, not InnoDB). It seems inefficient to have to write code to manage the sequence number when you can get the database to do it for you automatically.

Ryan's picture
Offline
Joined: 08/07/2007
Juice: 15459
Re: Re: Re: Enhance order number format options

Good ideas here... I remember your case from a while ago, tP. We should try to change this during D6 methinks.

Re: why the sequences table and not an auto_increment, that was actually just the "Drupal way" in D5. This has since changed, so those fields should be converted to auto_increment in D6. Granted... if we make it so, we can't switch to arbitrary unique or alphanumeric order IDs. In that case you would have to go with some alternate column or something... but perhaps we can just write a function to get an order number that you could hook into or override.

Wonder95's picture
Offline
Joined: 02/20/2008
Juice: 283
Use additional prefix field
Quote:

those fields should be converted to auto_increment in D6. Granted... if we make it so, we can't switch to arbitrary unique or alphanumeric order IDs. In that case you would have to go with some alternate column or something... but perhaps we can just write a function to get an order number that you could hook into or override.

That's what I'm trying to suggest; that we use the auto_increment field in conjunction with a separate field to create an order number. Let's say, for instance, that you have a varchar field called 'prefix', and the auto_increment 'order_id' field. There could be an admin field where the user could specify the value to be used for the prefix field using either a token or a text value. There could be another field to reset the value in the auto_increment field (using the ALTER TABLE statement, which is available in MySQL.) There could maybe even be a field to specify when the value should be reset (i.e. annually, etc.) which could be run using cron.

Or, if that's too complex, just add an additional varchar field ('prefix') as I mentioned above, and just keep the order_id field and the existing code that increments it as is. You could still have the admin field that resets the value at a specific interval, and you would just use UPDATE TABLE instead of ALTER TABLE.

Either way, with an additional prefix field, you could then have arbitrary unique or alphanumeric order IDs.

STSarah's picture
Offline
Joined: 08/27/2008
Juice: 14
yes please

I am interested in knowing how this turns out. my company currently uses four (yes...I wish they wouldn't insist) numbers to identify a job and most of them do not step up by single integers.

mrmeech's picture
Offline
Joined: 03/11/2009
Juice: 66
Ryan wrote:Good ideas
Ryan wrote:

Good ideas here... I remember your case from a while ago, tP. We should try to change this during D6 methinks.

Soooo, by any chance did this make it to the D6 Version? I'm cruisin' around on D6 UC-2 beta 5... and haven't seen anything to do this.

ACTUALLY, an idea:

For us, Ubercart is going to be the second shopping cart hooking into one account at authorize.net. We are using ubercart only for the recurring subscriptions. The other shopping cart posts the "invoice #" to Auth.net in integers also. And we need to be able to search on authorize.net the transaction records by "invoice" so that we can see which are recurring, and those that are not. (Currently authorize.net cannot provide this type of separation of records with ALSO the ability to download the search results.)

SO, as a stop-gag, is there a way i can hack the code that posts the transaction to Auth.net to include a prefix to the invoice #?

Ryan's picture
Offline
Joined: 08/07/2007
Juice: 15459
Re: Ryan wrote:Good ideas

Yeah, you can do that, you just have to hack the core Auth.Net module at this point. Not so elegant, but just remember to maintain the hack as you update your UC in the future. You should start by lookin' at _uc_authorizenet_charge().

allanmayberry's picture
Offline
Joined: 03/09/2009
Juice: 116
Re: Re: Ryan wrote:Good ideas

Was there ever any further development (ideally a module??) of this? I need to be able to prefix the orders before they are sent to SagePay as the account that this will be paying into also receives payment from a number of other online stores...

adamo's picture
Offline
Getting busy with the Ubercode.
Joined: 02/17/2009
Juice: 229
Re: Re: Re: Ryan wrote:Good ideas

I do not think this can be done in a module. At least, not completely. Maybe someone can prove me wrong but it looks like the uc_orders module will need some modification for this to be possible.

I have uploaded a contrib module that does not solve the problem completely, but may be useful depending on your needs:
Order Number Prefix

I hope this discussion will continue. I would really like to have this feature. I would be happy to do the work to create a patch but I don't want it to be all for nothing. I'd really like to hear back from the core devs if they would be willing to add this feature to core if a patch is submitted.

PS: I don't see any need to change the order_id primary key that the uc_orders table uses now. Just create an alternate_key field that can be set by other modules when the order is created, and make some modifications to the order edit/view/list functions to display the alternate key instead of or along with the order id.

allanmayberry's picture
Offline
Joined: 03/09/2009
Juice: 116
Re: Re: Re: Re: Ryan wrote:Good ideas

I'm glad to see I'm not the only one still chasing this functionality Smiling I'd be more than happy to help to contribute to development of this, however my PHP skills are very limited at the moment so I'm not entirely sure how much help I'd be...

As for what Adamo has said above, I agree with you about not having to change the order_id PK - it would be much less intrusive (and easier i imagine???) to create an "alternate_key"/prefix that could used in conjunction with the order_id when sending to the payment gateway, but wouldn't have to affect anything within Ubercart itself?

Is this feasible or am I just rambling?

Allan

adamo's picture
Offline
Getting busy with the Ubercode.
Joined: 02/17/2009
Juice: 229
Re: Re: Re: Re: Re: Ryan wrote:Good ideas

That's kind of what I'm thinking. The alternate_key should be expected to be a whole key though, not just the prefix. It would have a unique index, and you would be able to reference specific orders using only the alternate_key. The uc_orders module could default the alternate_key to the order_id, but other modules could modify the alternate key in the 'new' op of hook_order. The uc_orders module would load and save the alternate_key with the rest of the order. There would be new tokens added for use in invoice templates and checkout messages, and there could be an admin setting to choose whether you want order list/edit/view pages to show the order_id or alternate_key (or both?).

This would allow 3rd party modules to set the alternate_key to whatever they want, as long as it is unique. My prefix module would have an admin setting where you can specify the prefix to be used in the alternate_key. It would implement hook_order and in the 'new' op it would set the alternate_key to the specified prefix concatenated with the order_id. So if the order_id is 500 and your prefix is set to "WUSA", your alternate_key would be set to "WUSA500". Other modules could do different things like pad the alternate_key with leading zero's, or whatever other crazy stuff people need to do.

Payment gateway modules would need to be modified to allow you to choose whether you want to send the order_id or the alternate_key to the payment gateway. This would be a very simple change though, and it is much more likely to happen if there is a standard alternate_key field in core.

allanmayberry's picture
Offline
Joined: 03/09/2009
Juice: 116
Re: Re: Re: Re: Re: Re: Ryan wrote:Good ideas

+1 for that method then adamo! Thinking about it the alternate key should be a whole key, will be much easier to deal with in the long term as it wouldn't mean concatenation between the two keys down every time you wanted to use them!

Agree with it being added to core as it wouldn't be a massive change, could we make it optional to use the prefix and create the alternate key, as it may not be necessary for all users, who could instead continue to use order_id?

adamo's picture
Offline
Getting busy with the Ubercode.
Joined: 02/17/2009
Juice: 229
Re: Re: Re: Re: Re: Re: Re: Ryan wrote:Good ideas

I don't necessarily think the prefixing thing should be in core, just the alternate_key part. This would allow other modules to set whatever they want for the alternate_key (which could be something entirely unrelated to the order_id). I would update the prefixing module I posted in contrib to use the alternate_key field.

The alternate_key field would need to be populated with something if it has a unique index (which it should if it is going to be used to uniquely identify records), which I why I suggested that it be set to the order_id by default. Whether it is displayed in order list/view/edit pages or sent to payment gateways would be optional though. The order_id would still be displayed by default, and would still be used by all the core UC functions.

The order_id would still be the primary key, there would just be another key that can be used for certain things like changing the order number that is displayed to customers or submitted to payment gateways, or integration with legacy ERP systems.

longwave's picture
Offline
Joined: 09/20/2008
Juice: 630
allanmayberry wrote: Was
allanmayberry wrote:

Was there ever any further development (ideally a module??) of this? I need to be able to prefix the orders before they are sent to SagePay as the account that this will be paying into also receives payment from a number of other online stores...

The SagePay module currently generates a random ID per order, rather than using just the numeric order ID. See http://drupal.org/node/469920 for more discussion on this.

--
These forums are for general support questions about Ubercart.
Bug reports and feature requests should be posted at http://drupal.org/project/issues/ubercart
Latest API documentation can be found at http://api.ubercart.me/

salientknight's picture
Offline
Joined: 10/28/2011
Juice: 5
Authorize.net

I found 'invoiceNumber' => $order->order_id, on line 491 of ubercart/payment/uc_authorizenet.module

It looks like you can modify this 'x_invoiceNumber' => 'PREFIX'.$order->order_id ... the number will be saved in the database, but the order_id in auth.net will have the prefix.

longwave's picture
Offline
Joined: 09/20/2008
Juice: 630
Re: Enhance order number format options

I am also interested in this. The current approach I am using is to re-theme the site where needed (invoices etc) to add my prefix to the order numbers. A common theme function for formatting order IDs would be useful - a simple theme function could just add a prefix and/or leading zeroes, a more complex theme function could do some database lookups or similar to map the internal order_id to a different string for display (and generate new strings as needed for previously unseen order_ids).

Alternatively, holding two identifiers in the database per order may be useful - keep the integer order ID for Ubercart's internal use, and add a separate "order reference" column that defaults to the order ID, but that can be modified at order creation time by a module if necessary.

--
These forums are for general support questions about Ubercart.
Bug reports and feature requests should be posted at http://drupal.org/project/issues/ubercart
Latest API documentation can be found at http://api.ubercart.me/

tnf
tnf's picture
Offline
Joined: 04/27/2010
Juice: 21
Re: Enhance order number format options

Any progress on this? I used the suggestions here to make an intermediate solution, but the order number issue should be addressed in next version.

docans's picture
Offline
Joined: 11/05/2012
Juice: 28
Any solution for Drupal 7

I need to Generate a custom order number with a prefix. My order number is made up of:

=> mmddyy-0001

Where mm = month i.e. 05 for may
dd = day i.e. 25 for 25th
yy = year i.e. 14 for 2014.