[The Knotty Celt]

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:

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.

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?