How to reset invoice number in 1.6.1.0

[jkr]

I have problem to change the invoice number via Back Office.
It is still saved the old number and I need to change it.

Where can I find it in a database table to set it to the start number?

Thank you

[NemoPS]

It should be PS_INVOICE_START_NUMBER, in ps_configuration

[jkr]

It should be PS_INVOICE_START_NUMBER, in ps_configuration

Thank you, I have found it.

 

But it does not solve my problem. The back office shows me that the next invoice number will be 2016179 and the current PS_INVOICE_START_NUMBER is now manually set in DB to 8766.

How to force Presta to have the next invoice number 8767 ?

If I try to change the invoice number in the back office, then I get the error message that the number cannot be changed.

"Wrong invoice number( the number must be >2016178)"

Thank you

 

[jkr]

Hm, nobody knows.… how to solve it? 

[rocky]

You can't decrease the invoice number, you can only increase it, otherwise you could cause a conflict with old invoices. I'm going to guess that until you've removed all invoices with a number greater than 8767, you will continue to get that error message.

 

If you've done that and you're still getting an error message, you may need to change the AUTO_INCREMENT value.

[Rebel Tech]

I am not sure if this helps - but I use this module

 

Order reference change module

 

https://www.prestashop.com/forums/topic/433999-free-module-order-reference-change-module/

[jkr]

You can't decrease the invoice number, you can only increase it, otherwise you could cause a conflict with old invoices. I'm going to guess that until you've removed all invoices with a number greater than 8767, you will continue to get that error message.

 

If you've done that and you're still getting an error message, you may need to change the AUTO_INCREMENT value.

Thank you

The saving with the higher number has worked ☺ but I have still the same increased original invoice number

Please where to find the AUTO_INCREMENT ?

Thank you a lot

[rocky]

You can check your current AUTO_INCREMENT value by executing the following SQL query in phpMyAdmin:

SELECT `AUTO_INCREMENT`
FROM  INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'database_name'
AND   TABLE_NAME   = 'ps_order_invoice';

Change database_name to the name of the database where PrestaShop is installed. If your database prefix isn't ps_ then you'll need to change that too.

 

You can change the AUTO_INCREMENT value using the following SQL query:

ALTER TABLE `ps_order_invoice` AUTO_INCREMENT = 8767

Change the database prefix if necessary and 8767 to the ID of the next invoice.

[jkr]

You can check your current AUTO_INCREMENT value by executing the following SQL query in phpMyAdmin:

SELECT `AUTO_INCREMENT`
FROM  INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'database_name'
AND   TABLE_NAME   = 'ps_order_invoice';

Change database_name to the name of the database where PrestaShop is installed. If your database prefix isn't ps_ then you'll need to change that too.

 

You can change the AUTO_INCREMENT value using the following SQL query:

ALTER TABLE `ps_order_invoice` AUTO_INCREMENT = 8767

Change the database prefix if necessary and 8767 to the ID of the next invoice.

 

 

AUTO_INCREMENT shows the value 8768 but if I create the new invoice, then I have still the same increased original invoice number.

 

 

Edited October 25, 2016 by jkr (see edit history)

[rocky]

I checked the code and found the following:

            if ($id = (int)$this->getOrderInvoiceIdIfHasDelivery()) {
                $order_invoice = new OrderInvoice($id);
            } else {
                $order_invoice = new OrderInvoice();
            }
            $order_invoice->id_order = $this->id;
            if (!$id) {
                $order_invoice->number = 0;
            }

            // Save Order invoice

            $this->setInvoiceDetails($order_invoice);

            if (Configuration::get('PS_INVOICE')) {
                $this->setLastInvoiceNumber($order_invoice->id, $this->id_shop);
            }

It seems if the order has a delivery slip, it will use the existing invoice number. Otherwise, it uses the autoincrement value. If an invoice number is configured on the Orders > Invoices tab, then it will get the highest integer from the number column of the ps_order_invoice table.

[jkr]

I checked the code and found the following:

            if ($id = (int)$this->getOrderInvoiceIdIfHasDelivery()) {
                $order_invoice = new OrderInvoice($id);
            } else {
                $order_invoice = new OrderInvoice();
            }
            $order_invoice->id_order = $this->id;
            if (!$id) {
                $order_invoice->number = 0;
            }

            // Save Order invoice

            $this->setInvoiceDetails($order_invoice);

            if (Configuration::get('PS_INVOICE')) {
                $this->setLastInvoiceNumber($order_invoice->id, $this->id_shop);
            }

It seems if the order has a delivery slip, it will use the existing invoice number. Otherwise, it uses the autoincrement value. If an invoice number is configured on the Orders > Invoices tab, then it will get the highest integer from the number column of the ps_order_invoice table.

Exists any solution for it? Its really strange behaviour.

I deleted all the data from the table ps_order_invoice.

If I create a new invoice it still creates the wrong number in the settings in Orders>Invoices it rewrites new input for the desired invoice number and still increments the old one value 2016188, 2016189 and so on   I am so unhappy

[jkr]

Can somebody help me how to reset the invoice number?
Now I have No. 201623  (without prefix) and I need to start for instance from  the No. 790.

// I do not want to use delivery notes.
Thank you

[manfield]

Hi, have you tried to clear cache?

Advanced Parameters > Performance > Cache Clear button

Manfredi

Edited November 28, 2016 by manfield (see edit history)

[Ocius]

Hi, i've changed in ps 1.6.0.9 in the file order.php the function getLastInvoiceNumber() adding this WHERE date_add LIKE "2017%"  and work fine for me. Well i know that every year i will have to change, by the way i don't know the ps function to call for have the current year...

[Impe-M]

Hi guys,

I have to reset the invoice number, the current value is 106, anyone found a clearer and less intrusive solution?

 

Right now, I have deleted all the records of ps_order_invoice table, and I've set the AUTO_INCREMENT value to 0. Also, I've deleted all records in ps_order_invoice_payment table and in ps_order_invoice_tax table.

 

In addition, i've set the PS_INVOICE_START_NUMBER to 0 from the ps_configuration table.

 

I hope not to find other bugs on this topic.

Edited May 2, 2017 by Marcella*M* (see edit history)

[softhightech]

How to generate invoice based on the number of invoice for customers?

[phliippeduverger]

Hello 

 

For the info (if it help someone)

I had to reset the invoice number in the middle of the year (from 2017-08-16)

 

I changed classes/order/Order.php

I updated  the getLastInvoiceNumber

 

(note that in my case, I have PS_INVOICE_RESET set to reset invoice number everyyear. 

  public static function getLastInvoiceNumber()
    {
        $sql = 'SELECT MAX(`number`) FROM `'._DB_PREFIX_.'order_invoice`';
        if (Configuration::get('PS_INVOICE_RESET')) {
            $sql .= ' WHERE DATE_FORMAT(`date_add`, "%Y") = '.(int)date('Y').'  AND `date_add` > "2017-08-16 21:00:00"';
        }


        return Db::getInstance()->getValue($sql);
    }

I was able with this change to change/reset it  in the backoffice.

[Scully]

I would like to point out one more time that resetting the invoice number to 1 or to a lower value than the last value may result in data inconsistencies if you still hold old invoices with higher numbers in your database. We are frequently fixing database problems caused by incorrect resets of invoice and / or order numbers.

Edited August 29, 2017 by Scully (see edit history)

[Serhii]

On 8/29/2017 at 5:04 PM, phliippeduverger said:

Hello 

 

For the info (if it help someone)

I had to reset the invoice number in the middle of the year (from 2017-08-16)

 

I changed classes/order/Order.php

I updated  the getLastInvoiceNumber

 

(note that in my case, I have PS_INVOICE_RESET set to reset invoice number everyyear. 

  public static function getLastInvoiceNumber()
    {
        $sql = 'SELECT MAX(`number`) FROM `'._DB_PREFIX_.'order_invoice`';
        if (Configuration::get('PS_INVOICE_RESET')) {
            $sql .= ' WHERE DATE_FORMAT(`date_add`, "%Y") = '.(int)date('Y').'  AND `date_add` > "2017-08-16 21:00:00"';
        }


        return Db::getInstance()->getValue($sql);
    }

I was able with this change to change/reset it  in the backoffice.

Hi guys.
Finally the managed.
You can restart invoice numbers with this change in the code only if you make these changes:
Your code

    public static function setLastInvoiceNumber($order_invoice_id, $id_shop)
    {
        if (!$order_invoice_id) {
            return false;
        }

        $number = Configuration::get('PS_INVOICE_START_NUMBER', null, null, $id_shop);
        // If invoice start number has been set, you clean the value of this configuration
        if ($number) {
            Configuration::updateValue('PS_INVOICE_START_NUMBER', false, false, null, $id_shop);
        }

        $sql = 'UPDATE `'._DB_PREFIX_.'order_invoice` SET number =';

        if ($number) {
            $sql .= (int)$number;
        } else {
            // Find the next number
            $new_number_sql = 'SELECT (MAX(`number`) + 1) AS new_number
                FROM `'._DB_PREFIX_.'order_invoice`'.(Configuration::get('PS_INVOICE_RESET') ?
                ' WHERE DATE_FORMAT(`date_add`, "%Y") = '.(int)date('Y') : '');
            $new_number = DB::getInstance()->getValue($new_number_sql);
            
            $sql .= (int)$new_number;
        }

        $sql .= ' WHERE `id_order_invoice` = '.(int)$order_invoice_id;

        return Db::getInstance()->execute($sql);
    }

change this:  ' WHERE DATE_FORMAT(`date_add`, "%Y") = '.(int)date('Y') : '');  on ' WHERE DATE_FORMAT(`date_add`, "%Y") = '.(int)date('Y') : '').' AND `date_add` > "2019-02-05 11:06:00"';

my code

    public static function setLastInvoiceNumber($order_invoice_id, $id_shop)
    {
        if (!$order_invoice_id) {
            return false;
        }

        $number = Configuration::get('PS_INVOICE_START_NUMBER', null, null, $id_shop);
        // If invoice start number has been set, you clean the value of this configuration
        if ($number) {
            Configuration::updateValue('PS_INVOICE_START_NUMBER', false, false, null, $id_shop);
        }

        $sql = 'UPDATE `'._DB_PREFIX_.'order_invoice` SET number =';

        if ($number) {
            $sql .= (int)$number;
        } else {
            // Find the next number
            $new_number_sql = 'SELECT (MAX(`number`) + 1) AS new_number
                FROM `'._DB_PREFIX_.'order_invoice`'.(Configuration::get('PS_INVOICE_RESET') ?
                ' WHERE DATE_FORMAT(`date_add`, "%Y") = '.(int)date('Y') : '').' AND `date_add` > "2019-02-05 11:06:00"';
            $new_number = DB::getInstance()->getValue($new_number_sql);
            
            $sql .= (int)$new_number;
        }

        $sql .= ' WHERE `id_order_invoice` = '.(int)$order_invoice_id;

        return Db::getInstance()->execute($sql);
    }

after these changes I have reset the number invoice, tested on version 1.6.1.18

Edited February 5, 2019 by Serhii (see edit history)