1.6 Change the Order Reference to Number Reference

[draganv]

Hi,

 

I`m using Prestashop 1.6.1.4. I would like to change the Order reference ( alphabets ) to alphanumeric value.

 

Thanks.

[presta4you.com]

Check function 'getUniqReference' inside Order class

[shokinro]

you can ignore the reference #, and use order ID as format it into the digits you wanted. most places you can directly use it in many places.

It depends what kind of data is ready in the smarty or PHP context, you can change either one of following

 

$order->reference ==> $order->id

 

or

 

$order['reference'] ==> $order['id_order'] 

[draganv]

I find solution.

 

In classes/order/Order.php  replace

 

public static function generateReference()

{

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

}

 

to

 

public static function generateReference()

{

    $last_id = Db::getInstance()->getValue('

        SELECT MAX(id_order)

        FROM '._DB_PREFIX_.'orders');

    return str_pad((int)$last_id + 1, 9, '000000000', STR_PAD_LEFT);

 

}

[obewanz]

You can use the following code to change the behavior without disturbing the prestashop core code:

<?php
/*
*  RETURN ORDER REFERENCE TO SEQUENTIAL NUMERIC VALUE
*
*  2016 PrestaShop v1.6.1.x
*  Override by Obewanz
*/

Class Order extends OrderCore
{
  public static function generateReference()
  {
    $last_id = Db::getInstance()->getValue('
        SELECT MAX(id_order)
        FROM '._DB_PREFIX_.'orders');
    return str_pad((int)$last_id + 1, 9, '000000000', STR_PAD_LEFT);
  }
}

You should create a file called order.php in the folder /override/classes/order and paste the above code.

 

I looked into doing more complicated types of reference numbers, but in the end, I felt that a plain numeric string that goes to over 999 million was the best, most simple solution. If you must change to something else, next best idea is to replace the first three characters of the 000000000 string to NR-000000 or something similar. The field in the database is set to a 9 character maximum length so the best solution to adding an alpha would be to put it in a configuration record and append it to all requests for the number - just my opinion. (which is also beyond the scope of where I want to go at this point in time.)

 

Anyway, the alternate to implementing this solution (for non-programmers) is to upload the attached file to the /override/classes/order folder.

 

Note: after you have uploaded the file - don't forget to delete the class_index.php file in the /cache folder.

 

Hope this helps!

order.php

Edited June 9, 2016 by obewanz (see edit history)

[Pedramd7]

thanks

it's correctly very well!

[PrestaMake]

On 9/6/2016 at 10:00 AM, obewanz said:

You can use the following code to change the behavior without disturbing the prestashop core code:

<?php
/*
*  RETURN ORDER REFERENCE TO SEQUENTIAL NUMERIC VALUE
*
*  2016 PrestaShop v1.6.1.x
*  Override by Obewanz
*/

Class Order extends OrderCore
{
  public static function generateReference()
  {
    $last_id = Db::getInstance()->getValue('
        SELECT MAX(id_order)
        FROM '._DB_PREFIX_.'orders');
    return str_pad((int)$last_id + 1, 9, '000000000', STR_PAD_LEFT);
  }
}

You should create a file called order.php in the folder /override/classes/order and paste the above code.

 

I looked into doing more complicated types of reference numbers, but in the end, I felt that a plain numeric string that goes to over 999 million was the best, most simple solution. If you must change to something else, next best idea is to replace the first three characters of the 000000000 string to NR-000000 or something similar. The field in the database is set to a 9 character maximum length so the best solution to adding an alpha would be to put it in a configuration record and append it to all requests for the number - just my opinion. (which is also beyond the scope of where I want to go at this point in time.)

 

Anyway, the alternate to implementing this solution (for non-programmers) is to upload the attached file to the /override/classes/order folder.

 

Note: after you have uploaded the file - don't forget to delete the class_index.php file in the /cache folder.

 

Hope this helps!

order.php

 

I can confirm it is working for 1.7.4.2 Thank you.

Edited September 24, 2018 by Daniuz (see edit history)

[Fertechpl]

Working in v. 1.7.3.4

Cache folder changed to: var/cache/dev & var/cache/prod
So after overriding order.php, remember to delete class_index.php from above folders.

Code with little modifications,
conditions:
- generate random NUMERIC reference number
- check if new reference number is UNIQUE
- if not, after 10 tries, change first number to RANDOM letter

Should be enought for many orders.

 

Quote

<?php
/**
 * Author: FER-TECH
 * Based on: SEQUENTIAL NUMERIC ORDER REFERENCE override by Obewanz
 * Date: 17.11.2018
 * Generating random numeric reference
 * Prestashop v. 1.7.4.3
 */

Class Order extends OrderCore
{
    public static function generateReference()
    {
        $regenerate = 1;
        $retries = 0;

        while($regenerate > 0){
            // Generate new randomised reference number
            $newReference = strtoupper(Tools::passwdGen(9, 'NUMERIC'));

            // After 10 tries, change first numer to random letter
            if($retries > 10){
                $newReference = substr_replace($newReference, Tools::passwdGen(1, 'NO_NUMERIC') ,0,1);
            }

            //Check if new reference number is unique
            $regenerate = self::checkIfReferenceNumberExist($newReference);

            $retries++;
        }

        return $newReference;
    }

    private static function checkIfReferenceNumberExist($newReferenceNumber)
    {
        $quantityOfReferences =
            Db::getInstance()->getValue('
              SELECT COUNT(id_order)
              FROM '._DB_PREFIX_.'orders
              WHERE reference = "'. $newReferenceNumber .'"
            ');

        return $quantityOfReferences;
    }
}

 

Edited November 27, 2018 by Fertechpl
Asked to change comment (see edit history)

[folkifoon]

I also uses the code to create a referencenumber bases on the order id. I have a problem with my payment service provider. When to customers order at the same time, sometimes the same reference number is sent by prestahop to that serviceprovider. The first one that pays succeeds, the second will get error. And there is no second order in shop.

How can i solve this? 

[Fertechpl]

When You're first checking what's max. order ID in database and then You're creating new reference number based on that, You will always have problem when 2 clients will use that function in same time.

To solve this problem, You must first create order, return ID of that new order and then create new reference number based on that returned ID.

That's why, I'm checking database for same reference number in my example above. It's not perfect, but almost no chance to generate RANDOM reference by 2 clients in same time.

Edited January 22, 2019 by Fertechpl (see edit history)

[obewanz]

22 hours ago, folkifoon said:

I also uses the code to create a referencenumber bases on the order id. I have a problem with my payment service provider. When to customers order at the same time, sometimes the same reference number is sent by prestahop to that serviceprovider. The first one that pays succeeds, the second will get error. And there is no second order in shop.

How can i solve this? 

9

Are you talking about the SEQUENTIAL override or the RANDOM override?

What other modifications have you made to the code?

What payment service provider do you use?
How many orders per minute do you receive?

[folkifoon]

The first one, SEQUENTIAL. It is so much easier having a reference that correspondents with the order id.

But i need the sequential code combined with the code that checks if a reference exist and the change it to something diffferent.

[folkifoon]

On ‎1‎/‎23‎/‎2019 at 11:37 AM, obewanz said:

Are you talking about the SEQUENTIAL override or the RANDOM override?

What other modifications have you made to the code?

What payment service provider do you use?
How many orders per minute do you receive?

Hi, i use Mollie (Netherlands). I have around 20 orders a day, but already 3 times at the same time giving me problems.

I use your code, that is perfect for me, but it needs to check if the reference number does exist. So i think i need a part of the code that is posted by Fertechpl.

So my reference is
ML121500  (order id is 500)

The next one will be ML121501. Now  a second customers comes on the the site, and is paying before the one that started payment first. This will give a problem.
So for that i need the reference to be changed to QML12150Q or something

ML121500  (order id 500)
ML121501  (order id 501)
ML12150Q (order id 502)
ML121503 (order id 503)

Can you help me with that? 

[obewanz]

On 2/9/2019 at 3:09 AM, folkifoon said:

Hi, i use Mollie (Netherlands). I have around 20 orders a day, but already 3 times at the same time giving me problems.

I use your code, that is perfect for me, but it needs to check if the reference number does exist. So i think i need a part of the code that is posted by Fertechpl.

So my reference is
ML121500  (order id is 500)

The next one will be ML121501. Now  a second customers comes on the the site, and is paying before the one that started payment first. This will give a problem.
So for that i need the reference to be changed to QML12150Q or something

ML121500  (order id 500)
ML121501  (order id 501)
ML12150Q (order id 502)
ML121503 (order id 503)

Can you help me with that? 

So I made an assumption that I should not have made and did not ask... What version of Prestashop are you running?
 

The Technical Concepts:
The Reference Number in the ps_orders table is NOT unique (see screencap below) and so duplicates would only stop an order from processing if the selected payment provider is being fed the Reference Number from the 'ps_orders' table and said provider requires that value to be UNIQUE. (This could possibly be addressed in the payment module.) Having said that, in order to replicate your issue, I believe one would have to have two browsers open and submit both orders at EXACTLY the same time. The solution requested to append a letter and disturb the natural sequential interval would not be acceptable to me, nor would I expect it to be for most other larger store owners.

So at first glance, to fix the level of problem you explain, one would likely have to write a substantially more complex override that would actually store the order, grab the value in the 'order_id' field of the ps_orders table (since that IS a UNIQUE value), then return a prepended string of that value storing it in the 'reference' field and THEN allowing the process to continue within the Prestashop order flow. On second thought, THIS could (possibly) be accomplished with an override to the PaymentModule.php class, but the public function validateOrder is complex enough that I'm a little concerned with this approach given Prestashop's history with code patching, etc. With that in mind, I'll work to add a new override in a followup post with the changes needed to insure the reference number is as unique as the order_id.

So the short answer is - off the top of my head - there is no simple solution to your dilemma other than to address the apparent latency issues or write a different override.

Sorry!

Edited February 25, 2019 by obewanz
updated with possible option (see edit history)

[zod]

Thank you obewanz, instead of the Order ID, I changed SQL query to slightly improve your code, now it is counting the total orders of the current year. So it is a string like RO-2019/000001 and so on.

 

<?php
class Order extends OrderCore
{
  public static function generateReference()
  {
    $last_id = Db::getInstance()->getValue("SELECT count(`id_order`) FROM `ps_orders` WHERE `date_add` > '" . date('Y') . "-01-01'");
    $next_id = (int)$last_id + 1;

    return "RO-".date('Y')."/".str_pad($next_id, 6, '0', STR_PAD_LEFT);
  }

}

 

[obewanz]

16 hours ago, zod said:

Thank you obewanz, instead of the Order ID, I changed SQL query to slightly improve your code, now it is counting the total orders of the current year. So it is a string like RO-2019/000001 and so on.

 

<?php
class Order extends OrderCore
{
  public static function generateReference()
  {
    $last_id = Db::getInstance()->getValue("SELECT count(`id_order`) FROM `ps_orders` WHERE `date_add` > '" . date('Y') . "-01-01'");
    $next_id = (int)$last_id + 1;

    return "RO-".date('Y')."/".str_pad($next_id, 6, '0', STR_PAD_LEFT);
  }

}

Interesting take, I had thought of doing something similar, especially using count() instead of max(), but haven't really revisted the override since its original writing, until lately anyway. Did you by chance change the field length of the "reference" column in the table as well?

I have (finally) provided a new override to folkifoon to test to see if it fixes his problem. It modifies the paymentModuleCore so the value gets created and inserted during the validation part of the process, this is the only place I could find to make certain the reference number would be unique no matter what since it relies on the autonumber value created in the database for the sequential value.

4

5

3

 

Edited March 6, 2019 by obewanz (see edit history)

[zod]

Yes, i forgot to tell you i had to change the reference field lenght in database (from 9 to 16). 
The field is "reference" in table "orders" and "order_reference" in table "order_payment".

In addition it is required another file: override/classes/order/OrdperPayment.php
with following code

<?php
class OrderPayment extends OrderPaymentCore
{

  public function __construct($id = null, $id_lang = null, $id_shop = null)
  {
      self::$definition['fields']['order_reference']['size'] = 16;
      parent::__construct($id, $id_lang, $id_shop);
  }

}

 

Edited April 5, 2019 by zod
More details and additional code (see edit history)

[noufejt]

Hallo, I don't find more relevat topic, so I hope that it belongs here

I use prestashop 1.7.5.2 - multistore (now 5 shops, maybe next later)

I need format order reference number to:

• exactly 9 digits
• actual year (two digits) (can be changed every year automatically?)
• shop ID (can by ID form DB, but i will be better I can set "ID 1 from db = ID 5" ), one digit
• incremental number, 6 digits (000001, 000002) (can be for whole group of shops, not for only one shop)

For example: 194000001

I found only solution for change from "NON_numeric" to "Numeric" and this topic.
I´m not core programator, but a "power user" Can some help?

Thanks

Edited October 23, 2019 by noufejt
update parameters of rownumber (see edit history)

[noufejt]

On 10/21/2019 at 5:04 PM, noufejt said:

Hallo, I don't find more relevat topic, so I hope that it belongs here

I use prestashop 1.7.5.2 - multistore (now 5 shops, maybe next later)

I need format order reference number to:

• exactly 9 digits
• actual year (two digits) (can be changed every year automatically?)
• shop ID (can by ID form DB, but i will be better I can set "ID 1 from db = ID 5" ), one digit
• incremental number, 6 digits (000001, 000002) (can be for whole group of shops, not for only one shop)

For example: 194000001

I found only solution for change from "NON_numeric" to "Numeric" and this topic.
I´m not core programator, but a "power user" Can some help?

Thanks

I use code from you guys, thanks
This "override" works for me (same row number for all shops)


 

<?php
class Order extends OrderCore
{
  public static function generateReference()
  {
    $last_id = Db::getInstance()->getValue("SELECT count(`id_order`) FROM `ps_orders` WHERE `date_add` > '" . date('y') . "-01-01'");
    $next_id = (int)$last_id + 1;
    $id_shop = (int)Shop::getContextShopID();

    return date('y').$id_shop.str_pad($next_id, 6, '0', STR_PAD_LEFT);
  }

}

[muynck]

@draganv I have published a module for this on Github yesterday, checkout the latest release:  https://github.com/blauwfruit/orderreference/releases/tag/1.1.2

 

Tell me what you think.

Edited June 2, 2020 by muynck
Link didn't work. (see edit history)

[Martin Mi]

I can confirm this is working well in PrestaShop 1.7.6.4 as visible in the the picture below. Order Code gets changed during generation and displayed as a random number fine everywhere (email templates, orders, website confirmations, customer account, etc..)

 

Best Regards,

Martin Mi

E-shop & E-commerce Agency

www.dataquo.eu

[muynck]

7 hours ago, Martin Mi said:

I can confirm this is working well in PrestaShop 1.7.6.4 as visible in the the picture below. Order Code gets changed during generation and displayed as a random number fine everywhere (email templates, orders, website confirmations, customer account, etc..)

 

Best Regards,

Martin Mi

E-shop & E-commerce Agency

www.dataquo.eu

Looks good! Thanks for the feedback!

 

Do you have recommendations for features?

[Krsna18]

On 4/9/2020 at 3:59 PM, muynck said:

@draganv I have published a module for this on Github yesterday, checkout the latest release:  https://github.com/blauwfruit/orderreference/releases/tag/1.1.2

 

Tell me what you think.

Thank you for the module. It works for customer e-mail and order listing in the backoffice but not the admin email template (from themes/classic/modules/ps_emailalerts/mails/en/new_order.html). The admin email is still showing the random characters. I am using Prestashop 1.7.6.4.

[muynck]

On 6/10/2020 at 1:30 PM, Krsna18 said:

Thank you for the module. It works for customer e-mail and order listing in the backoffice but not the admin email template (from themes/classic/modules/ps_emailalerts/mails/en/new_order.html). The admin email is still showing the random characters. I am using Prestashop 1.7.6.4.

HI @Krsna18,

 

See the latest release here: https://github.com/blauwfruit/orderreference/releases/tag/1.1.3

Go the the config page and follow the instructions.

[luisleitaoaudio]

 

[Pat13160]

Hello, I installed the suggested version. The order numbers are not displayed !!
In Admin:
Format: do you have an example?
Preview -> no information
My Prestashop version: 1.6.1.27
Big thanks

[muynck]

@Pat13160

The field should be filled with variables, not absolute numbers.

{$cart->id:%d} would, for example, put the cart id there.

Can you give an example reference outcome, and explain what you want? Thanks!

[vjnunes]

Hi,

Easy as this. Search for Order.php in classes/order and where you find:

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

Just change 'NO_NUMERIC' to 'NUMERIC'

And if you don't need to generate such an extensive number, you can change number 9 to 6 for example.

[pg_dev]

Hi, I'm trying to modify order reference with merchant_ref_number value(which is passed into request url to payment page). So far I have tried using hookActionValidateOrder, tried overriding order.php and writing code from this thread but all in vain. Nothing seems to work and change the order reference. Can anyone please help me ? I would be very grateful. I'm stuck with this for over almost a week now.

Below are codes from respective files for understanding: 

path -  modules/latpayredirect/classes/Order.php  :  

<?php

class Order extends OrderCore
{
    public static function generateReference()
    {
        $merchantRefNumber = Tools::getValue('merchant_ref_number');
        if ($merchantRefNumber) {
            return $merchantRefNumber;
        }

        $lastId = Db::getInstance()->getValue('SELECT MAX(id_order) FROM '._DB_PREFIX_.'orders');
        return str_pad((int)$lastId + 1, 9, '000000000', STR_PAD_LEFT);
    }
}

hookActionValidateOrder - 

 public function hookActionValidateOrder($params)
    {
      $orderId = $params['order']->id;
      $newReference = $this->generateMerchantRefNumber(); // Generate your custom reference here

      // Update the order reference
      Db::getInstance()->update(
          'orders',
          array('reference' => pSQL($newReference)),
          'id_order = ' . (int) $orderId
      );

    } 
 
<!--------------------------------------------------------------------------------------------------->
 private function generateMerchantRefNumber()
   {
      $length = 5; // Length of the reference
      $characters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'; // Allowed characters for the reference
      $reference = '';

      for ($i = 0; $i < $length; $i++) {
          $randomIndex = mt_rand(0, strlen($characters) - 1);
          $reference .= $characters[$randomIndex];
      }

      return $reference;
   }
  

and passing merchant_ref_number in PaymentOptions as  : 

$merchant_ref_number = $this->generateMerchantRefNumber();

I have also opened a thread for this here https://www.prestashop.com/forums/topic/1077420-pass-order-reference-to-the-payment-gateway/



Please help !!!

 

[alexandr0s]

On 4/7/2016 at 3:30 PM, draganv said:

$last_id = Db::getInstance()->getValue('

        SELECT MAX(id_order)

        FROM '._DB_PREFIX_.'orders');

    return str_pad((int)$last_id + 1, 9, '000000000', STR_PAD_LEFT);

 

8 years later, still very helpful.

Thanks a lot