Hi VirtueMart Team :-)
We have encountered a problem - actually 2 problems.
Our payment gateway uses the value from virtuemart_ordre_id as the forward counting unique number (integer) for transaction references. Using virtuemart_order_id as the reference for payments is great for both the shop owners and the bookkeepers because they can figure out the order (from the number) without having to look it up in the order management in VirtueMart.
BUT... VirtueMart is ressetting the auto_increment value for virtuemart_order_id after deleting of orders. It is resetting it back to the lastest existing order_id + 1. This action gives conflicts with our payment gateway which gives the error "not unique order number" because the next buyer is "trying" to reuse a reference for an older transaction.
In other words; because VM is resetting the auto_increment value to the lastest existing order it actually reusing order ID's giving reference conflicts in our payment system.
Can we - in a later version of VirtueMart - get an option not to reset the auto_increment value for virtuemart_order_id after deleting orders?
In our case we will not set it to "Don't not reset after order deletion". Other shop owners that user order_number (random string) as the transaction reference do not need to disable the reset of the auto_increment value after order deletion.
But I think that the bug is even bigger than this because I can see actions from deleted order in the history overview from existing orders (given the old virtuemart_order_id integer). So the history is merged by the old and deleted order and the new and not deleted order.
It seems to me that the resetting of the auto_increment is bugging. How can we disable it at PHP level for now?
Thank you for your great job :-)
You should use the oder_number! not the order_id,.. vm isnt doing anything, it is the db.
the virtuemart_order_id is to use only internally for example, within the db, NOT for other issues.
"It seems to me that the resetting of the auto_increment is bugging. "
Again mysql does that. The autoincremented id is not meant for this purposes.
There was a bug relating to auto increment and MYSQL posted on a forum
http://bugs.mysql.com/bug.php?id=727 (http://bugs.mysql.com/bug.php?id=727)
I agree with Milbo - you should be using the order_number
The virtuemart_order_id is an internal database field
Hi Milbo
Thank you :-)
We like to have a real numbers for our orders. The randomly generated order numbers are not liked by our bookkeepers, the people working with the webshop nor the customers. Everyone much more like to say "please see order 1234" instead of "please see order ..... 3jfd34f4g422".
I think I have a fix.
Before an order or orders are deleted the max(virtuemart_order_id) value could be saved into an integer and this integer could be updated into the auto_increment value for virtuemart_order_id after the deletion of orders. In this case it will not re-use old order ID's.
I will see if I can fix this myself :-)
Again thank you for your answer and keep up the good work :-)
Hi again Milbo :-)
I see that you in your lastest version of VM use the order number structure "R2015081700001" instead of a random one. This is better but can you make and extra order number starting from 1 and going up like the auto increment does but not going back if the highest order number is deleted?
Or can you improve VM so the auto increment is set back to the (former) higest number after the lastest order is deleted?
This improvement is done by doing this after deletion of the last order:
ALTER TABLE #__virtuemart_orders AUTO_INCREMENT=[FORMER HIGHEST ORDER ID + 1]
Both solutions would do the trick :-)
Quote from: Jens Kirk on August 22, 2015, 18:13:21 PM
Hi again Milbo :-)
I see that you in your lastest version of VM use the order number structure "R2015081700001" instead of a random one.
since vm2.0.0! and we do not use the autoincrement of the db and you should never delete an order! Set it on cancelled. If you wanna cancel an order on a real cashdesk you must rebook it. You cannot delete it, same here, you should not delete it.
Quote from: Jens Kirk on August 22, 2015, 18:13:21 PM
This improvement is done by doing this after deletion of the last order:
ALTER TABLE #__virtuemart_orders AUTO_INCREMENT=[FORMER HIGHEST ORDER ID + 1]
Try it, it wont work. We do not use the auto increment of the db!
Why is there an "order delete" button when it gives problems using it?
The order delete button is for people who want to delete an order as it was never created. Usually used by agencies, before the shop is given to shopowners.
In the moment the shop starts, you are in most countries not allowed to delete an invoice by law. We dont want to disable a feature, because it could be illegal. You are responsible for your store yourself.
We use an external invoice system - www.e-conomic.com - for our invoices. So we can delete the orders.
Can you make some kind of warning (JS alert) when deleting the last order via the GUI? Because it is only deleting the last order that is giving problems for your webshop owners.
I could manipulate the virtuemart_order_id counter "up to" the last order ID that was deleted by changing the auto_increment value :-)
In special the last order should not be a problem. Because we use Count (rows), so if you delete the last order, it is like never been there. Only if you get new orders meanwhile you will have missing counts. But the invoicenumber works with the invoice entries, not the order entries. so,.. as long you do not invoices with already generated invoices => you have no problem.
Okay thank you again :-)
The invoice number logic have jumped a lot around in the past years. Can't you make some modes for webshops owners to choose from? At least give a mode where the invoice numbering mode starts from 1 and counts up from there?
If so we could use this counting number system for transaction references and in external invoice references without problems.
I know that we can use the existing invoice number but ... it is very long and our clients and their buyers do not like it. Try saying a very long number over the phone and moreover the bank account overview only shows a few chars (meaking it impossible to recognize an order from the bank account overview). RedShop does not do this. It is starting from 1 and counts up which gives the buyers an easy way of talking about their order and the bookkeeps can easily find a payment. I think that VirtueMart version 1 actually did this but moved away from this in perhaps version 2.
Again thank you for all your work - VM is our primary shop system for our clients :-)
Quote from: Jens Kirk on August 24, 2015, 21:38:56 PM
Okay thank you again :-)
The invoice number logic have jumped a lot around in the past years.
Not really, I changed it in vm2 and since then it is the same.
Quote from: Jens Kirk on August 24, 2015, 21:38:56 PM
Can't you make some modes for webshops owners to choose from?
http://extensions.virtuemart.net/vm-orders/order-number-plugin-detail
Quote from: Jens Kirk on August 24, 2015, 21:38:56 PM
At least give a mode where the invoice numbering mode starts from 1 and counts up from there?
Actually, we have just an offset there.
Quote from: Jens Kirk on August 24, 2015, 21:38:56 PM
If so we could use this counting number system for transaction references and in external invoice references without problems.
Exactly for that reason you should NOT use a simple number without any format.
Quote from: Jens Kirk on August 24, 2015, 21:38:56 PM
I know that we can use the existing invoice number but ... it is very long and our clients and their buyers do not like it.
http://extensions.virtuemart.net/vm-orders/order-number-plugin-detail
Quote from: Jens Kirk on August 24, 2015, 21:38:56 PMTry saying a very long number over the phone
You did not notice the format of the invoice, eh? It is YYYYMMDDXX0Counter. Next version btw comes with an own pool of chars, without I, l, O and 0.
Quote from: Jens Kirk on August 24, 2015, 21:38:56 PMIt is starting from 1 and counts up which gives the buyers an easy way of talking about their order and the bookkeeps can easily find a payment.
Yeah and opens fraud any door. Our order and invoice number is just a lot more sophisticated. You see an order/invoice number just as reference. I see it as key. A customer should not be able to guess an order or invoice number. Furthermore it should not reveal too much information and it should be sortable.
The offset prevents the order with ordernumber = 1 and invoice number = 1. It obscures a bit your sales volume. Additionally to that, the ordernumber and order password are the data to access an invoice, even as guest. So it is again important to have some random chars there. The invoicenumber is for the support almost like a password. When a customer calls you and knows the invoice number, you start to give support. So an unguessable order/invoice number prevents frauds.
Additionally, all pdfs are automatically sorted by date, if you sort for the filenames. So if you know and understand a bit the system and the ideas behind, then the strange numberse makes a lot more sense, or (at least I hope so).
Quote from: Jens Kirk on August 24, 2015, 21:38:56 PM
Again thank you for all your work - VM is our primary shop system for our clients :-)
Thank you for that :-)
Thank you for all yours answers :-)
We do not use the order/invoice part of VirtueMart as you do. We have an external invoicing system – www.e-conomic.net - which is working automatically. And it is working so great. Both ePay and QuickPay have created integration for E-conomic with is the biggest Scandinavian online bookkeeping system – and they are expanding to the whole EU.
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.
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.
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? 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. You should be free to change the structure of the order number anytime in the future and we should be free to have an increasing non-reusing order_id reference logic for E-conomic and for your payment provider. As said everyone is happy about the order_id value. The bookkeeper is. The webshop owner is. The customer is. And therefore I - as their developer - am happy too :-D
I have make a tiny little hack into VM so the virtuemart_order_id is set back to the last order_id (as it was before the last order was deleted). The PHP/MySQL is inserted just after an VM-order is deleted. Everything is working great now and every webshop client is happy now that their customers never experience "invalid (re-used) order number" from payment provider. I will do this for all my VM clients.
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.
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.
But as said my little hack fixes this but it is only for my clients and the VM shopowner that I know.
If I send you the hack will you implement it into the official version of VM?
If you don't like the idea then could you make an option in the backend of VM where the webshop owners can choose between reusing and non-reusing order IDs? Because then all ePay and QuickPay users can fix the problem by choosing "non-reusing order IDs". You can leave the "reusing order IDs" as default.
Thank you again for all your hard work for the community :-)
Hello Jens
I've developed a special integration between e-conomic & VM for "company-customers" only. 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.
i do NOT recommend to make your "own changes" to the database. This is a very big potential risk of losing database integrity
My recommendation is ALWAYS to restrain from making hacks to any core part of the VM itself.
If Epay/quickpay are making mistakes and using virtuemart order id you can help them (or at least epay) by forking their plugin and making the changes needed. https://github.com/ePay/virtuemart3
I've already done this to be able to make templateoverride of of post_payment page. (so that you can actually make your own "thank you for ordering..blah blah page" - find it here: https://github.com/welrachid/virtuemart3 )
With regard to re-using order_ids theres an option here to be able to do this.
http://imgur.com/FgRxHth
BTW also from DK
Hi welrachid
You do not get ePay and QuickPay to change back to old number system that was hard to trace. QuickPay changed to using virtuemart_order_id in the early start of this year. They will properly just advice VM-users NOT to delete the last order. I talked to the main VM-developer at QuickPay and he says that they are NOT going back to the old order number system. I will give him my little VM-hack so they can write a note about for their VM-uses. I have not contacted ePay about the issue. 90% of our clients are using QuickPay.
Both ePay and QuickPay are releasing they new platforms - QuickPay is a step in front of ePay - and both want to have simpel, logic and increasing order number as transaction references.
We have been working 4 years will a full integration extension for VM (and a few other Joomla webshops) and E-conomic. We are releasing it in the start of next year. We are running it with 10 of our clients. This little VM hack that prevents MySQL from reusing old virtuemart_order_id values is working great :-)
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.
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).
Quote from: welrachid on September 05, 2015, 09:44:25 AM
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.
Quote from: Jens Kirk on September 04, 2015, 12:32:42 PM
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.
Quote from: Jens Kirk on September 04, 2015, 12:32:42 PM
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.
Quote from: Jens Kirk on September 04, 2015, 12:32:42 PM
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.
Quote from: Jens Kirk on September 04, 2015, 12:32:42 PM
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.
Quote from: Jens Kirk on September 04, 2015, 12:32:42 PM
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!
Quote from: Jens Kirk on September 04, 2015, 12:32:42 PM
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.
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.
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 :-)
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.