VM is wrongly resetting auto_increment after order deletion giving ref conflicts

[welrachid]

Hi Jens
Great to hear that you will be releasing a full integration extension for VM -> e-conomic. Just beware the "new api" is not done yet.

How does your "hack" take care of multivendor shops? They will not be having incremental order numbers? they will have a global number?

Wish you the best.

[Jens Kirk]

Hi Wel :-)

We are party using the new API from e-conomic and the old one and we are supporting both the old and new API from QuickPay and the old and the new (coming) API from ePay and the API for ClearHouse. It is working great and many of our own clients are saving so much bookkeeping time :-) The solution is also capturing the money according to total amount of the invoice (items could have been removed from the order if they are not on stock). It is automating everything about the bookkeeping. Including registration of the payment when it arrives at the bank account some days leter - using the API from BankConnect - www.bankconnect.dk

BankConnect is beta testing and we are 3'party developers at their solution and know that they soon will release the net-/webbank integration :-)

We have been testing our solution with multi-sites and with multi-payment-gateways (on one site) with one E-conomic agreement. Every order (and invoice) in E-conomic is prefixed with 2 letters from the site (domain) + 2 letters from Joomla system + the webshop's (increasing) order number (eg. virtuemart_order_id). So it becomes like "NL-VM-1234". We are setting this reference into the "other ref." and the title of the invoice to keep track of the reference back to site and the webshop system that gave the order. VirtueMart is the best supported one but RedShop, OSE Membership, AdAgency are also supported and the other webshop systems in Joomla will follow soon. Every step of the automatication has been verified by our accountants  so we know that the integration is doing it right... until we discover that it was not ;-) ... but every client has agreed that the software is still in beta. We fix errrors quickly and apply patches for the other clients.

The system is also suppoting the API from PostDanmark (Post Norden) and API from GLS and it will support the API from iZettle's in the start of the next year (we hope).

[Milbo]

When my customer asked for special incrementing order numbers instead of randomized numbers we bought this plugin:
http://extensions.virtuemart.net/vm-orders/order-number-plugin-detail it has "global counts" which means it has a single counter across all vendors.

Again, VM2.0.0 provided from begin on incrementing numbers. The plugin of Kainhofer just allows to create your own format.

Every paid order is - after a successful callback from the payment provider – automatically converted to an order in E-conomic. And when the order is shipped it is upgraded to an invoice that is booked and the transaction is captured. Everything working perfectly.

Check the Klarna plugin, you can do the same with VM natively.

A customer never sees the VM-order because we have disabled it. He/she only sees the E-conomic order (and after shipment; the E-conomic invoice). If he/she calls us up about the order/invoice, we always ask for the debtor number (with is found in the order / invoice). Therefore, we do not need to have special order numbers for security.

Yeh you have an extra number for that. Even this number is already in VM natively and called order password. It allowed guests to track their orders.

I see that the last past of your order number system is now a counter, but can we be sure that it stays a counter in the future?

It did not change for years. Actually the only thing which is really important is, that any number is unique, the rest is db. Order numbers dont need to be incremental, I think you mix that up with invoice numbers.

In other words; that your references between E-conomic and VirtueMart will not break down in the future. No you cannot give us this guaranty.

Of course I can, because you use VM! Just write your own plugin or use the one of Kainhofer.

In Denmark we are allowed to delete the orders as much as we like but we cannot deleted nor change booked invoices at E-conomic.net.

You clearly mix up order numbers with invoice numbers. You are not allowed to delete it, when it is in your ERP system, because it is then handled as invoice!

Both www.quickpay.net and www.epay.eu are (in their newest versions for their newest platforms) have / are starting using virtuemart_order_id as the counter for the order ref in their transactions (plus a prefix for clients with more sites on one payment gateway agreement). Both QuickPay and ePay are unaware of the danger in deleting the last order in VM. They will become aware of this if we do not fix it before their clients contacts them about a (new) problem with "invalid order number" when people try to pay.

I just can say you should not write workarounds for their wrong written plugins. We wrote a lot payment plugins, please check the ones delivered with the core and it is not a big deal. They dont need to delete the order, they should just set it to cancelled, problem solved.

Your hack is just not needed. If you need that, use a plugin.

[Jens Kirk]

Hi Milbo :-)

The problem is actually bigger than first reported with the re-use of virtuemart_order_id with payments systems.

The problem is also in many other parts of VM - from orderitems to history and any other subsystems that use virtuemart_order_id.

I cannot understand why auto_increment is not set back to the latest used value so all conflicts are avoided :-)

Our clients that use VM need to delete orders when testing on live sites and it is 100% legal in Scandinavian countries to delete orders and perhaps also other EU countries. Whereas booked invovices cannot be deleted in the external invoicing systems that we use (that we have integrated with VM). We only invoice paid orders from VM. We are doing this 100% correct and accountants have confirmed this. Otherwise we could not be doing it for our clients.

Please let the auto_increment be set back to the last used value when deleting the last order and every one will benefit form this. Who will not benefit from avoiding ID conflicts? Otherwise you have to remove the "delete order" button or alert a warning that says deleting the last order with give ID conflicts in the different subsystems in VM and external systems and use virtuemart_order_id.

[Jens Kirk]

The issue was solved by upgrading to the newest MySQL version where they do not set the auto_increment value back when deleting the last record :-)

[Milbo]

Which version did you used before? and which one do you use now? Seems to be the reason, that I never understood your problem. We use mariadb.