Jump to content

Order reference in 1.5 - uppercase replace with number


Stanislav Novák

Recommended Posts

To replace uppercase in the Order reference and replace with numbers (id_order as in older versions) I made this simple code override in /override/classes/order/Order.php:

<?php

class Order extends OrderCore
{
public function getUniqReference()
{
	$query = new DbQuery();
	$query->select('MIN(id_order) as min, MAX(id_order) as max');
	$query->from('orders');
	$query->where('id_cart = '.(int)$this->id_cart);
	$query->orderBy('id_order');

	$order = Db::getInstance()->getRow($query);

	/*if ($order['min'] == $order['max'])
		return $this->reference;
	else
		return $this->reference.'#'.($this->id + 1 - $order['min']);*/

	return sprintf('%06d', $this->id);
}

public static function getUniqReferenceOf($id_order)
{
	$order = new Order($id_order);
	return $order->getUniqReference();
}
}  

 

To return to the original letters designation, you must remove comment from 'if' and add comment to

//return sprintf('%06d', $this->id);

 

.

Edited by Stano Novák (see edit history)
  • Like 2
Link to comment
Share on other sites

Thank you,

 

I did try it but it does not let me guest-track the order with the order reference 000007 and it gave me invalid error warning. Did I understand it correctly that this modification replaces all the instances of the 9-letter order reference with 6-digit order ID?

Link to comment
Share on other sites

  • 2 weeks later...
  • 2 weeks later...

In my database there is no file in my order folder after going through override/classes/order. Is there another way to access the order.php file?

 

Thank you for your help.

To replace uppercase in the Order reference and replace with numbers (id_order as in older versions) I made this simple code override in /override/classes/order/Order.php:

 

.

Link to comment
Share on other sites

The code above fixed a lot of the spots the reference ID was used and replaced it with the order #. However, there are a few places that I have noticed so far that it did not replace. Can we figure out how to fix this?

 

1. Guest Order Tracking Page. Still need to enter the reference ID here. The order # did not work.

 

2. Order confirmation page when paying by check (this is the only payment method I currently have setup on my test store so not sure if this is happening for other payment methods as well).

 

3. In the BO under Orders > Orders it still shows the reference ID instead of the order # in the list of orders.

 

These are the only one's I could find that I couldn't change in Localizations > Translations or that I at least couldn't find there (since the word reference is also used alot for product reference).

 

I'm also trying to replace order reference from GXOOQKBBV to normal order number (same as orderID, eg. 000000001) as this is very annoying. Can't find the solution..

 

Solution is in the first post. Works perfectly except for the few places I could find listed above.

Link to comment
Share on other sites

I've just been browsing the forge for bugs etc and came across this suggestion from "Pawel T"

 

Hi there,

I think it will be useful to make {id_order} available in mail templates. These are only 3 changes:

 

PaymentModule.php line ~455 change: '{id_order}' => (int)$order->id,

mailalerts.php line ~370 add: '{id_order}' => (int)$order->id,

PaymentModule.php line ~595 add: '{id_order}' => (int)$order->id

Best regards

 

Would that help sort the above issue out?

Link to comment
Share on other sites

I've just been browsing the forge for bugs etc and came across this suggestion from "Pawel T"

 

 

 

Would that help sort the above issue out?

 

Thanks for the reply but that would only change the reference ID to the order # in the email templates This was already fixed with the original code in the first post.

Link to comment
Share on other sites

Hi All,

 

I try to change the following code in /pdf/invoice.tpl:

 

 

<!-- CUSTOMER INFORMATION -->

<b>{l s='Bestelnummer:' pdf='true'}</b><br />

{$order->getUniqReference()}<br />

<br />

 

in to:

 

 

<!-- CUSTOMER INFORMATION -->

<b>{l s='Bestelnummer:' pdf='true'}</b><br />

{$order->getUniqid_order()}<br />

<br />

 

But I get a fatal error. I am a beginner, i hope some can help me to get the order id in the place instead of reference.

Thank you.

Link to comment
Share on other sites

The code above fixed a lot of the spots the reference ID was used and replaced it with the order #. However, there are a few places that I have noticed so far that it did not replace. Can we figure out how to fix this?

 

1. Guest Order Tracking Page. Still need to enter the reference ID here. The order # did not work.

 

2. Order confirmation page when paying by check (this is the only payment method I currently have setup on my test store so not sure if this is happening for other payment methods as well).

 

3. In the BO under Orders > Orders it still shows the reference ID instead of the order # in the list of orders.

 

These are the only one's I could find that I couldn't change in Localizations > Translations or that I at least couldn't find there (since the word reference is also used alot for product reference).

 

 

 

Solution is in the first post. Works perfectly except for the few places I could find listed above.

 

Anyone have a solution to the 3 spots above that still show the Reference # and not the Order #?

Link to comment
Share on other sites

  • 1 month later...
  • 4 weeks later...
  • 2 weeks later...

The code above fixed a lot of the spots the reference ID was used and replaced it with the order #. However, there are a few places that I have noticed so far that it did not replace. Can we figure out how to fix this?

 

1. Guest Order Tracking Page. Still need to enter the reference ID here. The order # did not work.

 

2. Order confirmation page when paying by check (this is the only payment method I currently have setup on my test store so not sure if this is happening for other payment methods as well).

 

3. In the BO under Orders > Orders it still shows the reference ID instead of the order # in the list of orders.

 

These are the only one's I could find that I couldn't change in Localizations > Translations or that I at least couldn't find there (since the word reference is also used alot for product reference).

 

 

 

Solution is in the first post. Works perfectly except for the few places I could find listed above.

 

To resolve 2. Order Confirmation page you need to open a payment_return.tpl located in /modules/bankwire/views/templates/hook and replace the following code in line 37 from:

 

<br /><br />- {l s='Do not forget to insert your order reference %s in the subject of your bank wire.' sprintf=$reference mod='bankwire'}

 

to:

 

<br /><br />- {l s='Do not forget to insert your order reference %06d in the subject of your bank wire.' sprintf=$id_order mod='bankwire'}

 

The same can be applied to other modules with this issue.

  • Like 1
Link to comment
Share on other sites

 

I was unable to find anything that resolves this issue. It explains how to generate random numbers instead of letters as order reference, but not how to replace random letters with 6 digit sequential order IDs. Post #1 and #14 already explains how it can be done with exception of guest tracking and back office(you can track orders in back office by order ID ignoring zeros in front as a workaround). Guest tracking fetches letter order reference directly from database, therefore, Stanislavs fix does not address it. Files GuestTrackingController.php and guest-tracking.tpl process guest tracking. I tried replacing code to fetch order ID instead, but my PHP skills are too limited at this point.

 

I think there are two ways it can be done, either fiddle with Order.php so the code writes 6 digit number ID instead of letter order reference in the SQL database field "order-reference" or patch it by using #1 and #14 and make guest tracking to fetch by field "id_order" not "order_reference". Hopefully someone with more skills can look at it.

  • Like 1
Link to comment
Share on other sites

Hello

 

Thank you to Stanislav Novák for sharing the code.

 

I tried to use it but I must be doing something wrong as nothing change.

 

What I did:

1)Created a file named Order.php in the \override\classes\order folder (I am runnng my tests with wamp on a local machine)

2)I pasted the code in the file and saved.

3)I refreshed my web pages of the e-shop and the backoffice and made a new test order.

Unfortunatly, the order still have the letters as reference number when looking in the backoffice.

 

Can someone help and tell me what I did wrong ?

 

Thank you,

Link to comment
Share on other sites

Hello

 

Thank you to Stanislav Novák for sharing the code.

 

I tried to use it but I must be doing something wrong as nothing change.

 

What I did:

1)Created a file named Order.php in the \override\classes\order folder (I am runnng my tests with wamp on a local machine)

2)I pasted the code in the file and saved.

3)I refreshed my web pages of the e-shop and the backoffice and made a new test order.

Unfortunatly, the order still have the letters as reference number when looking in the backoffice.

 

Can someone help and tell me what I did wrong ?

 

Thank you,

 

You did nothing wrong except perhaps read this thread carefully. Current Order.php override does not addresss the back office because it does not change what is being written to database, it only returns 6 digit id instead of reference.

Link to comment
Share on other sites

Hello Matti,

 

Thank you for your answer.

I am sorry, but I don't understand (may be because english is not my native language)

Where should I see the change ?

 

What I would need is to replace the actual order reference containing lettre by a reference with number (exactly what we had in previous PS version) wich is (to my mind) far easier to use when having customers calling for inquiry about the orders.

Link to comment
Share on other sites

Ok, here is the solution. I improved Stanislavs override. Back office is working now. Place this code in /override/classes/order/Order.php:

 

<?php
class Order extends OrderCore
{
public function getUniqReference()
	{
			$query = new DbQuery();
			$query->select('MIN(id_order) as min, MAX(id_order) as max');
			$query->from('orders');
			$query->where('id_cart = '.(int)$this->id_cart);
			$query->orderBy('id_order');

			$order = Db::getInstance()->getRow($query);

			/*if ($order['min'] == $order['max'])
					return $this->reference;
			else
					return $this->reference.'#'.($this->id + 1 - $order['min']);*/

			return sprintf('%06d', $this->id);
	}
	public static function getUniqReferenceOf($id_order)
	{
			$order = new Order($id_order);
			return $order->getUniqReference();
	}
public static function generateReference()
{

  $order_results = Db::getInstance()->getValue('SELECT MAX(id_order) FROM '._DB_PREFIX_.'orders WHERE id_order');

  $id_setreturn = ($order_results + 1);
  $formated = sprintf("%06d", $id_setreturn);
  return $formated;
	}
}

 

To make guest tracking compatible change line 103 in /themes/default/guest-tracking.tpl

 

from:

<i>{l s='For example: QIIXJXNUI or QIIXJXNUI#1'}</i>

 

to:

<i>{l s='For example: 000123'}</i>

  • Like 1
Link to comment
Share on other sites

To make guest tracking compatible change line 103 in /themes/default/guest-tracking.tpl

 

from:

<i>{l s='For example: QIIXJXNUI or QIIXJXNUI#1'}</i>

 

to:

<i>{l s='For example: 000123'}</i>

 

This will only change the words next to the input box (which you should be doing under Translations on the backend anyways), it won't make it accept the order #. I have had mine fixed but it was part of some custom work I had a developer do for me and I'm not sure how he fixed it.

Link to comment
Share on other sites

This will only change the words next to the input box (which you should be doing under Translations on the backend anyways), it won't make it accept the order #. I have had mine fixed but it was part of some custom work I had a developer do for me and I'm not sure how he fixed it.

 

This is not to be used on its own, but with the new order.php override. Guest tracking and back office works fine to me (it will only work for new orders of course). Don't knock it till you try it. Well you don't have to now, but others may still need the fix.

 

@Badolina make sure you create the file Order.php in /override/classes/order/ and paste the override code there. Do not change anything in /classes/order/Order.php and it will work fine.

 

Alternatively, you can paste this one to /override/classes/order/Order.php

 

and this one to /classes/order/ Order.php

 

I use 1.5.3.1 as well. Make sure to test only with the new orders as only new orders after code has been overriden will have sequencial 6 digit order reference in database.

Edited by Matti (see edit history)
Link to comment
Share on other sites

Well, I did not follow all of the topic, but it takes me some time to make an override class, where the goal is:

 

1. reference is number leaded with zeros

2. it is saved to DB

3. it is available at the moment of creating the order

4. it is complied with high-load servers - so we are sure, no one else can get the same reference if the orders are processed in the same time

5. nothing is changed, only overrided

6. it is version independent (till the method is used)

 

So the solution is to upload following file to /override/classes/order/

 

<?php
/* Override the Order class
*
*/
class Order extends OrderCore{

/* Generate 1.4.x style order reference (order id with zeros)
 * Q&C to Jan Drda <[email protected]>
 */
public static function generateReference()
{
 /* start REFERENCE AS ORDER NUMBER FILLED WITH 0 TILL 9 CHARS [varchar(9)] */
 // lock table for reading and writing for all
 // = so no other order can get the same reference
 Db::getInstance()->Execute('LOCK TABLES `ps_order` WRITE');
 // get last order id
 $custom_last_order_id = Db::getInstance()->getValue('SELECT MAX(id_order) AS last_order_id FROM `ps_orders`');
 // make reference as order id filled with zeros if necessary
 return str_pad(($custom_last_order_id + 1), 9, "0", STR_PAD_LEFT);
 /* end REFERENCE AS ORDER NUMBER FILLED WITH 0 TILL 9 CHARS [varchar(9)] */

 /* start REFERENCE AS LETTERS - ORIGINAL */

 //return strtoupper(Tools::passwdGen(9, 'NO_NUMERIC'));

 /* end REFERENCE AS LETTERS - ORIGINAL */
}
}
?>

 

Also attaching the file

 

I apologize if the solution is the same as above, but I though, it could help to someone.

Order.php

  • Like 1
Link to comment
Share on other sites

  • 2 weeks later...

Both the solution of Stanislav Novák and jdrda do have (big) risks and even PS1.5.3.1 default solution do have big risks!!!

 

[1] The initial solution provided by Stanislav Novák is to 'show' the order_id to the customer but in the database there is still the reference with upper case letters and so still code / (payment)modules can use this.

Also with PS1.5 you can have multiple orders for 1 reference although I cannot foresee if this can cause effects.

 

[2] The solution of jdrda sound great however if I analyse the code the it already tries to 'forecast' the id_order based on previous order. Still it can be that just in between another order is taking this number. If you have a 40 multishop which all uses same ordering and are generating a lot of orders then this risk is getting higher. And again PS1.5 can have multiple orders out of one Cart.

 

[3] The default PS1.5 stored the order (and possible multiple orders out of the same cart) with this reference:

 

public static function generateReference()

{

return strtoupper(Tools::passwdGen(9, 'NO_NUMERIC'));

}

However with 200000 orders the change is there to have the same reference for multiple different Carts. As the code is not checking if the reference is already used.

 

 

 

Nevertheless due to these valuable contribution it gave me a new idea. Like PS1.3 which is using the Cart ID as a reference for the order. Then no risk of not being unique. And also the reference should be the same for a cart which generate multiple orders. And with the Cart ID which you will then see on bank acounts or via payment gateways like ogone you will be able to lookup the cart and navigate from the cart to the order. At least for me it's 100 times better than the crazy random text reference which I cannot ask client to spell!!!!

 

I refused to edit core files because then it's simple to edit public function validateOrder in classes/PaymentModule.php as here $reference = Order::generateReference(); should change to $reference = $this->context->cart; BUT for example the cheap COD with fee module is duplicating this validateOrder() method and thus would not use the reference.

 

So I found a way to store the solution in the override/classes/order/order.php:

 

 

class Order extends OrderCore
{
public function add($autodate = true, $null_values = true)
{
 $this->reference=sprintf("%08d", $this->id_cart);
 return parent::add($autodate, $null_values);
 }
}

 

 

It will on adding the order ensure that the reference is forced to the formatted id_cart. As simple as that.

 

For me this is working great now.

  • Like 1
Link to comment
Share on other sites

Hi guys,

 

there is this moderator in our German forum who holds the view that in multistore mode this modification will affect in a dangerous way the database stability of PrestaShop and uniqueness of IDs by generating double reference IDs.

 

What do you think about this claim?

Edited by eleazar (see edit history)
Link to comment
Share on other sites

@ eleazar - please do not prevaricate my statements, which are based on the statements of user shopimport.nl. You as initial poster of the code (copied from here) on German Forum didn't make any reference or advise to the problem multishop and multicarts, so I simply added this part to your incomplete initial post !

The objections are coming from shopimport.nl and not from me ! Also there is no statement coming from my part with reference to "it will affect the stability of the Database". I only repeated what was written here: problem of cart duplicates and possibly problems.

 

Both the solution of Stanislav Novák and jdrda do have (big) risks and even PS1.5.3.1 default solution do have big risks!!!

 

[1] The initial solution provided by Stanislav Novák is to 'show' the order_id to the customer but in the database there is still the reference with upper case letters and so still code / (payment)modules can use this.

Also with PS1.5 you can have multiple orders for 1 reference although I cannot foresee if this can cause effects.

 

[2] The solution of jdrda sound great however if I analyse the code the it already tries to 'forecast' the id_order based on previous order. Still it can be that just in between another order is taking this number. If you have a 40 multishop which all uses same ordering and are generating a lot of orders then this risk is getting higher. And again PS1.5 can have multiple orders out of one Cart.

 

 

And

 

I refused to edit core files because then it's simple to edit public function validateOrder in classes/PaymentModule.php as here $reference = Order::generateReference(); should change to $reference = $this->context->cart; BUT for example the cheap COD with fee module is duplicating this validateOrder() method and thus would not use the reference.

Link to comment
Share on other sites

@CD2500

I ask you for fair comments and stopping personal attacks. So please calm down. Thx a lot.

And I advise you to read shopimport.nl's full post without pitching on separate parts.

 

@shopimport.nl

 

Maybe I got it wrong but from my point of view jdrda did neither edit the core directly - he used the override which is something different - nor did he 'forecast' the id_order based on previous order.

 

The solution of jdrda sound great however if I analyse the code the it already tries to 'forecast' the id_order based on previous order. Still it can be that just in between another order is taking this number.

 

In contrast to matti's solution I don't think this will happen here, because jdrda is playing it safe, which is due to a very important new step you find here.

 

Step #1:

 

 Db::getInstance()->Execute('LOCK TABLES `ps_order` WRITE');

 

The database table is now locked for writing for all other users, so that the reference_id stays unique.

 

In step #2 the current ID is read-out ...

 

$custom_last_order_id = Db::getInstance()->getValue('SELECT MAX(id_order) AS last_order_id FROM `ps_orders`');

 

... and will be incremented and returned in step #3:

 

return str_pad(($custom_last_order_id + 1), 9, "0", STR_PAD_LEFT);

 

... by which the table will be unlocked again.

 

I agree with you that under certain conditions the default version of function generateReference (line 1368-1380 in classes/order/Order.php) may bear "big risks".

Anyway, the above solution is even safer than the original function where step #1 is missing.

Link to comment
Share on other sites

I agree that the solution from jdrda is not using core files and nicely uses the override. What I ment with "I refused to edit core files" is just for myself that I try never to touch core prestashop files. Especially when it's not a real bug, but more a different approach. However if I could edit the core files the I would not solve it in the function add() but then I would solve it in validateOrder() however this one is too big to handle in override and do edits because on next release it might contain improvements from prestashop core and then I would use the copy of an old version. I hope this explains.

 

Then about the LOCK database i have to review more in detail. Am I right that it finds the max id_order and increases with 1 just because the new order record is not yet made and when it's made in the $order->add() (called from validateOrder) then it will define id_order and most probably with the same id as the one 'predicted'. However it seems not to be a hard assignment. So it's still free for the database to calculate another id. E.g. when you delete the latest order then the database will skip the ID while the max order_id will not. (I sometimes delete test orders). But indeed the LOCK will then prevent that another order will take the number. I would like to know when the table is unlocked? you mention its unlocked on the return but this would be too early as the new order is not yet added. Will it unlock when the connection is closed?

 

And maybe I'm wrong, but a cart can generate multiple (back)orders and these should have the same reference!! That's why I proposed to use cart id. I'm pretty sure that this can be the case however I didn't experience in practise yet and are just (trying) to reverse engineer.

 

Anyway, nice technical discussing! And I would like to know why core team decided to use the random letters as reference. Is there another (payment) risk we didn't think of here?

Link to comment
Share on other sites

Ha ha ha, you mean you traced back my posts. Unbelievable! This is really funny!

 

Anyhow, scorpionsworld will include my German translation to the module's next release. You have to live with that lol

 

Well, seriously, there are some minor bugs left which I guess scorpionsworld will be able to eliminate. He did a really good job.

Edited by eleazar (see edit history)
Link to comment
Share on other sites

@shopimport.nl

Then about the LOCK database i have to review more in detail. Am I right that it finds the max id_order and increases with 1 just because the new order record is not yet made and when it's made in the $order->add() (called from validateOrder) then it will define id_order and most probably with the same id as the one 'predicted'. However it seems not to be a hard assignment. So it's still free for the database to calculate another id.

 

I guess you're right, but I really don't think that this remark matches the problem we discuss here. The referential integrity seems to be untouched because this function doesn't change any id_order which as primary key is the reference.

I understand that you fear possible risks, because from version 1.4.9 to 1.5 PrestaShop changed the database engine and now uses innoDB instead of MyISAM. Principally innoDB supports foreign keys for better data integrity which is the main difference to MyISAM.

 

But if you have a look into the database you recognize that PrestaShop doesn't use the field 'reference' as a foreign key. It's just a varchar field with the maximum length of 100 chars including zero (which means in comparison to char format saved as is - including blanks and case sensitivity). There's no evidence that these random letters are used as a reference.

 

So as far as I see changing the value of this field named 'reference' won't cause any trouble. Even if the primary key field id_order would be hard assigned as e.g. '13544' and the field 'reference' would have the value '000013546' instead of 'DFKRRFNXR'.

 

Maybe the Presta team just loved the IBAN-like look. ^_^

Edited by eleazar (see edit history)
Link to comment
Share on other sites

You mention that id_order is primary key. Indeed this is true, however different functions do query the orders based on reference field! So one cart resulting in multiple (back)orders should have the same reference in order to work properly! You will see the issue when you have multi line orders . And most people will not have it I think, still it needs to be covered.

 

See in clases/order/order.php

 

getOrdersTotalPaid() , getBrother(), getByReference()

 

 

This for me indicated that reference IS used!

Link to comment
Share on other sites

Correct, but I still don't think that this is the crucial issue. Maybe you skipped the definition of $reference as a string variable in line 154 of classes/order/Order.php with the remark

 

/**
* @var string Order reference, this reference is not unique, but unique for a payment
*/

 

For PrestaShop a unique reference would be this string value completed with hash key followed by a number, like in public function getUniqReference(), e.g. GWJTHMZUN#2.

 

Once again: What makes you think that a unique 9-chars string variable looking like a number is less unique than the primary key id_order containing the same or slightly different number as numerical value?

 

I have no idea, unless you possibly hold the view that random capital letters indicate a better data integrity than numbers.

Link to comment
Share on other sites

@eleazar

In function validateOrder() in PaymentModule class it generates the reference ONCE for all sub orders!

 

You see

$reference = Order::generateReference();

 

And later a foreach in which $reference is used. So it's not the same as id_order which will be different for the sub orders.

Link to comment
Share on other sites

  • 2 months later...

That's exactly why I supported this solution. So you have the chance to ask your customers to skip the zeros. ;)

 

Anyhow, since some weeks some of the English mails in 1.5.4.1 were modified and use id_order as viariable though it should be order_name. Don't know who did this and why, but you should modify those mails otherwise this solution won't work.

 

I checked that this applies to the Swedish translations too. You may identify those mails by the subline "Powered with PrestaShop" (mistranslated from 'réalise par PrestaShop') instead of "Powered by PrestaShop".

Edited by eleazar (see edit history)
Link to comment
Share on other sites

  • 1 month later...
  • 3 months later...

Hey guys, I traced back my solution, as I am preparing new shops. Only a notice - LOCK is released at the end of the session, so no other session can read the table. As the process of generating order is very short, other concurent session should wait until the LOCK is released, so the number has to be unique.

Reference: http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html

Edited by jdrda (see edit history)
Link to comment
Share on other sites

  • 4 weeks later...

@PhPMadman

 

thanks alot for the contribution.

 

Since I never wrote an override and am not going to it myself :) I 'd kindly like to ask you for it.

Or anyone else is invited to feel free to make public one here.

 

Thanks again.

Link to comment
Share on other sites

  • 1 month later...

@PhpMadman: Since you are the expert, I've a short question: 9 out of 10 of our customers (shop-owners) use only the basic functions of order processing in back office, means: an order has always 1 invoce and 1 delivery slip. How hard is it to change PrestaShop so that it uses the same (numeric) reference for order-no, invoice-no. and delivery-slip-no.?

Link to comment
Share on other sites

  • 1 month later...

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...