Follow us on Twitter X-Cart on Facebook Wiki
Shopping cart software Solutions for online shops and malls

Custom Order Numbering

 
Reply
   X-Cart forums > X-Cart 5 > Dev Questions (X-Cart 5)
 
Thread Tools Search this Thread
  #1  
Old 03-15-2020, 01:00 PM
  The Knotty Celt's Avatar 
The Knotty Celt The Knotty Celt is offline
 

Advanced Member
  
Join Date: Jan 2020
Posts: 32
 

Default Custom Order Numbering

PREMISE

My current databases generates order numbers using a prefix, the current year, and a four-digit sequence, like 'SO/2020/0194'. X-Cart, natively simply uses a sequential number. In order for compatibility among my various systems, platforms and databases, I am working on an add-on which would override the current numbering system with one that matches my other platforms.


CODE

While some of my systems already have settings which allow me to easily perform this action, some do not. This is how I have overridden the native numbering format of one such platform:
Code:
public static function generateReference() { $last_id = Db::getInstance()->getValue(" SELECT CAST(RIGHT(MAX(`reference`),4) AS UNSIGNED) FROM `"._DB_PREFIX_."orders` WHERE `date_add` LIKE '".date(Y)."%'"); return 'SO/'.date(Y).'/'.str_pad($last_id+1, 4, '0', STR_PAD_LEFT); }
It's important to note that that particular system uses PostgreSQL. It would be easy enough to adapt the function to the MySQL syntax for use in X-Cart.


WHAT TO OVERRIDE?

I assume I would need to override the findNextOrderNumber() function of the Oder model.

Code:
public function findNextOrderNumber() { if (!\XLite\Core\Config::getInstance()->General->order_number_counter) { $this->initializeNextOrderNumber(); } $em = \XLite\Core\Database::getEM(); $conn = $em->getConnection(); $conn->beginTransaction(); try { $orderNumber = $em->createQueryBuilder() ->select(['c.config_id', 'c.value']) ->from('XLite\Model\Config', 'c') ->where('c.name = :name') ->andWhere('c.category = :category') ->setParameter('name', 'order_number_counter') ->setParameter('category', 'General') ->getQuery() ->setLockMode(\Doctrine\DBAL\LockMode::PESSIMISTIC_WRITE) ->getSingleResult(); $value = max($orderNumber['value'], $this->getMaxOrderNumber() + 1); $qb = $em->createQueryBuilder(); $qb ->update('XLite\Model\Config', 'c') ->set('c.value', $qb->expr()->literal($value + 1)) ->where('c.config_id = :config_id') ->setParameter('config_id', $orderNumber['config_id']) ->getQuery() ->execute(); $conn->commit(); } catch (\Exception $e) { $conn->rollback(); throw $e; } return $value; }
I imagine adapting the $orderNumber and $value definitions to find and define the next order number in sequence based on the query used by my other system is the key, however; I am unfamiliar with the proper use of the createQueryBuilder() method.

QUESTIONS
  1. Where can I find clear information on the createQueryBuilder() method?
  2. Do I need to use the createQueryBuilder() method to run the query, or is there some other method?
  3. How do I structure my module directories/files to properly override this function?
__________________
X-Cart version 5.4.1.46
PHP version 7.4.33
MySQL version 15.1
Apache version 2.4.56
cURL version 7.74.0
Reply With Quote
  #2  
Old 03-15-2020, 11:24 PM
 
Ed B. Ed B. is offline
 

X-Adept
  
Join Date: Apr 2016
Posts: 446
 

Default Re: Custom Order Numbering

Quote:
Originally Posted by The Knotty Celt
PREMISE


QUESTIONS
  1. Where can I find clear information on the createQueryBuilder() method?
  2. Do I need to use the createQueryBuilder() method to run the query, or is there some other method?
  3. How do I structure my module directories/files to properly override this function?
For the question 1 the best I can give is

https://devs.x-cart.com/getting_started/working-with-database.html#query-builder


For the question 2, normally you can just use the appropriate getter and setter, but I am not sure.


3 It suffices to create an abstract class that extends the Order class that implements iDecorator.
__________________
X-cart 5.2.12, php 5.6
Ed from Grenoble, France
Reply With Quote
  #3  
Old 03-16-2020, 03:27 AM
  cflsystems's Avatar 
cflsystems cflsystems is offline
 

Veteran
  
Join Date: Apr 2007
Posts: 14,190
 

Default Re: Custom Order Numbering

There is ready one - https://market.x-cart.com/addons/order-prefix-suffix-by-cfl-systems.html
__________________
Steve Stoyanov
CFLSystems.com
Web Development
Reply With Quote
  #4  
Old 03-30-2020, 10:08 AM
  The Knotty Celt's Avatar 
The Knotty Celt The Knotty Celt is offline
 

Advanced Member
  
Join Date: Jan 2020
Posts: 32
 

Default Re: Custom Order Numbering

Quote:
Originally Posted by cflsystems

Thank you. That, unfortunately, does not suit my needs. It applies a prefix and suffix to the order number, but the order number itself continues to increment. I am looking to have intuitive order numbers where the sequence resets each year, and the current year is pre-appended.

Since the orderNumber column of the orders model class is already a VARCHAR of length 255, so it already prepared to accept the form of order number required for my business logic. I just need to find the precise business logic which needs to be overridden by my module.

It seems, though, that in my OP, I looked at the Order Repo model, rather than the actual Order Model itself. I am continuing my research to find exactly where in the business logic is found the code that sets the value of the orderNumber column.
__________________
X-Cart version 5.4.1.46
PHP version 7.4.33
MySQL version 15.1
Apache version 2.4.56
cURL version 7.74.0
Reply With Quote
  #5  
Old 03-30-2020, 11:20 AM
  cflsystems's Avatar 
cflsystems cflsystems is offline
 

Veteran
  
Join Date: Apr 2007
Posts: 14,190
 

Default Re: Custom Order Numbering

Quote:
Originally Posted by The Knotty Celt
Thank you. That, unfortunately, does not suit my needs. It applies a prefix and suffix to the order number, but the order number itself continues to increment. I am looking to have intuitive order numbers where the sequence resets each year, and the current year is pre-appended.




You will need to make a lot of changes to avoid issues with orderNumber.
While the type in the database for this field is varchar this is the only place where orderNumber is treated as string. Everywhere else orderNumber is treated as integer. This was pointed out to XC veeery long time ago and I simply do not see them willing to change this behavior.


If you start recording orderNumber as string, meaning the field having non-numeric characters, you will break orders search (both admin and customer), next/prev order, eventually payments and everything else that depends on orderNumber.
Not to mention orderNumber is cast to (int) numerous times within the code, both for PHP and MySQL.


If you keep it as number but start resetting it every so often you will end up with multiple orders having the same orderNumber which will again screw up lots of functionality.


Unfortunately XC is coded within its core to work with orderNumber instead of orderId for lots of functions and behavior.


For what you want your best bet is to have it say 202000001, then next year reset to 202100001 and so on. This will not cause any issues with current code. And you can easily set this at the beginning of every year through admin cart settings - next order number.
Then you can use the module I mentioned to just add the SO/ prefix.
And in this case you cannot have 2020/00001 as orderNumber as the / will mess things up.
__________________
Steve Stoyanov
CFLSystems.com
Web Development
Reply With Quote
  #6  
Old 03-30-2020, 11:40 AM
  The Knotty Celt's Avatar 
The Knotty Celt The Knotty Celt is offline
 

Advanced Member
  
Join Date: Jan 2020
Posts: 32
 

Default Re: Custom Order Numbering

Quote:
Originally Posted by cflsystems
You will need to make a lot of changes to avoid issues with orderNumber.
...
If you start recording orderNumber as string, meaning the field having non-numeric characters, you will break orders search (both admin and customer), next/prev order, eventually payments and everything else that depends on orderNumber.
Not to mention orderNumber is cast to (int) numerous times within the code, both for PHP and MySQL.
...
Unfortunately XC is coded within its core to work with orderNumber instead of orderId for lots of functions and behavior.

Lots of good information here. Thank you, again. Instead of relying on the core orderNumber field, then; perhaps it might be better to add a column to the Order Model Class with behaviour defined by my module which would also include the necessary templates to display this value in both the FO and BO. This makes the module design a bit less of a challenge as I do not have to be as concerned with messing up the behaviours which access the orderNumber field itself. My next step was going to be to add a separate column for an invoice number, anyway, so it's just a matter of defining the columns and procedures associated with each. This is not so bad, since some of my other systems have separate numbers for the system and for the user/customer.

Thanks again. Now I have a better idea of what direction to take.
__________________
X-Cart version 5.4.1.46
PHP version 7.4.33
MySQL version 15.1
Apache version 2.4.56
cURL version 7.74.0
Reply With Quote
  #7  
Old 06-05-2020, 12:49 PM
  The Knotty Celt's Avatar 
The Knotty Celt The Knotty Celt is offline
 

Advanced Member
  
Join Date: Jan 2020
Posts: 32
 

Default Re: Custom Order Numbering

I am working on a custom module which adds a column to the Order model for handling custom order reference numbers. Here is my .\Model\Order.php file:
Code:
<?php { /** * * @var string * * @Column (type="string", length=12) */ prodtected $reference = ''; /** * Get Order reference number * * @return string */ public function getReference() { return $this->reference; } /** * Set Order reference number * * @param string $reference * @return void */ public function setReference($reference) { $this->reference = $reference; } /** * Set orderNumber * * @param string $orderNumber * @return Order * / public function setOrderNumber($OrderNumber) { $this->orderNumber = $orderNumber; // Check if a reference number has already been assigned. // If not, generate and assign a new one. if ($this->reference == '') { $reference = $this->generateReference(); $this->setReference($reference); } return $this; } /** * Generates new Order Reference Number in the form SO/2020/0001 * * @return string */ public function generateReference() { $dateMin = new DateTime(date("Y")."-01-01"); $dateMax = new DateTime(date("Y")."-12-31"); $result = \XLite\Core\Database::getRepo('\XLite\Model\Order')->createQueryBuilder('o') ->select('o.reference') ->where('o.date BETWEEN :dateMin AND :dateMax') ->setParameter('dateMin', $dateMin->getTimestamp()) ->setParameter('dateMax', $dateMax->getTimestamp()) ->getResult(); $references = []; foreach ($result as $order) { array_push($references, max($order)); } $reference = "SO/" . date("Y") . "/" . substr(str_repeat(0,4).((int)substr(max($references),-4)+1), -4); return $reference; } }


Here is the workflow logic of the generateReference function:
  1. Grab a list of all Order reference numbers;
  2. Place them in an array;
  3. Find the maximum value;
  4. Extract and increment its 4-digit counter portion;
  5. Format the new order reference; and
  6. Return the newly formatted reference number.
When I log in to place an order as a customer, the checkout process works up until after payment, at which point the site just hangs with the "thinking" cursor.


It does create records in the Payment\Transaction and Order models, but xc_orders.orderNumber is Null and xc_orders.reference is blank.


Is there a way to properly debug what is causing it to hang?
__________________
X-Cart version 5.4.1.46
PHP version 7.4.33
MySQL version 15.1
Apache version 2.4.56
cURL version 7.74.0
Reply With Quote
  #8  
Old 06-05-2020, 03:01 PM
  cflsystems's Avatar 
cflsystems cflsystems is offline
 

Veteran
  
Join Date: Apr 2007
Posts: 14,190
 

Default Re: Custom Order Numbering

One thing I see, although it may not be the issue you have is


Code:
... new DateTime(date("Y")."-01-01");


The way you have it PHP will assume \XLite\Model\Order\DateTime. Change it to


Code:
... new \DateTime(date("Y")."-01-01");


Also use $this->getReference() == '' instead of $this->reference == '' - that's why you have the get method. However the reference in this case will always be empty. setOrderNumber() is called only when order is placed and turned into an order.


Also you can make $reference nullable and assign default value of NULL instead of empty string. Then if you still want to check you can do


Code:
if (is_null($this->getReference())) { $this->setReference($this->generateReference()); }


or


Code:
if ($this->getReference() === null) { $this->setReference($this->generateReference()); }


keeping in mind === will evaluate exact value of null while == will evaluate 0, '', false being null as well.


While \ DateTime(date("Y")."-01-01") will give you what you want - Jan 1st @ 00:00:00am same is true for \DateTime(date("Y")."-12-31") - @ the very beginning of the day. You need to add 23 hrs 59 min and 59 seconds to end date to get to the end of year.


Change your query to select max reference directly and get single result instead of looping through all. And since I assume you will always want to get the max number which will be current year anyway there is no need of min and max timestamp.
I suggest you get the reference for the latest order that has one (meaning reference is not null, order number is not null and is_order is true) and then manipulate reference to increase by 1 for the next order.
__________________
Steve Stoyanov
CFLSystems.com
Web Development
Reply With Quote
  #9  
Old 06-06-2020, 08:02 AM
  The Knotty Celt's Avatar 
The Knotty Celt The Knotty Celt is offline
 

Advanced Member
  
Join Date: Jan 2020
Posts: 32
 

Default Re: Custom Order Numbering

Thank you for your assistance and tips.

Quote:
Originally Posted by cflsystems
And since I assume you will always want to get the max number which will be current year anyway there is no need of min and max timestamp.


The reason I filter the year of the orders is to reset the 4-digit sequence. When the query returns an empty result, the integer value of Null becomes 0, to which 1 is added, thus having an automatic reset of the numbering. I recognize what you mean by my range missing the last 23 hours, 59 minutes and 59 seconds of the year, so I added a year column to streamline the query.


Code:
/** * * @var string * @Column (type="string", length=4) */ protected $year = ''; /** * Set Date * * @param integer $date * @return Order */ public function setDate($date) { $this->date = $date; $this->setYear(); return $this; } /** * Set year * * @return void */ public function setYear() { $this->year = date("Y"); } /** * Get year * * @return string */ public function getYear() { return $this->year; } /** * Generate new Order Reference Number in the form SO/2020/0001 * * @return string */ public function generateReference() { $reference = "SO/" .date("Y") ."/" .substr(str_repeat(0,4) .((int)substr(\XLite\Core\Database::getRepo('\XLite\Model\Order') ->createQueryBuilder('o') ->select('MAX(o.reference)') ->where('o.year = :year') ->setParameter('year', date("Y")) ->getSingleResult()[1],-4)+1), -4); return $result; }


After these alterations based of your feedback, the reference numbers are being generated, and the site no longer hangs after payment is processed.
__________________
X-Cart version 5.4.1.46
PHP version 7.4.33
MySQL version 15.1
Apache version 2.4.56
cURL version 7.74.0
Reply With Quote
  #10  
Old 06-06-2020, 08:20 AM
  cflsystems's Avatar 
cflsystems cflsystems is offline
 

Veteran
  
Join Date: Apr 2007
Posts: 14,190
 

Default Re: Custom Order Numbering

Good to know you got it working
__________________
Steve Stoyanov
CFLSystems.com
Web Development
Reply With Quote
Reply
   X-Cart forums > X-Cart 5 > Dev Questions (X-Cart 5)


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump


All times are GMT -8. The time now is 02:01 PM.

   

 
X-Cart forums © 2001-2020