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 > Modifying the design and features (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
 

Member
  
Join Date: Jan 2020
Posts: 10
 

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.0.9
Reply With Quote
  #2  
Old 03-15-2020, 11:24 PM
 
Ed B. Ed B. is offline
 

eXpert
  
Join Date: Apr 2016
Posts: 293
 

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: 13,811
 

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
 

Member
  
Join Date: Jan 2020
Posts: 10
 

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.0.9
Reply With Quote
  #5  
Old 03-30-2020, 11:20 AM
  cflsystems's Avatar 
cflsystems cflsystems is offline
 

Veteran
  
Join Date: Apr 2007
Posts: 13,811
 

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
 

Member
  
Join Date: Jan 2020
Posts: 10
 

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.0.9
Reply With Quote
Reply
   X-Cart forums > X-Cart 5 > Modifying the design and features (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 06:30 AM.

   

 
X-Cart forums © 2001-2020