Sales orders missed from imports

[Guewen Baconnier @ Camptocamp]

Original report by Allison Miller:

> We have recently discovered an issue when running scheduled batch imports
> of sale orders, after seeing that a couple of orders never made it into
> OpenERP. The batch performs a search for Magento orders using the
> created_at time. This has the potential to skip orders by starting and
> finishing the search in between the time an order is placed and that
order
> is committed to the database. We see this specifically with credit card
> orders; the authorization call can take multiple seconds to respond and
is
> made in the same PHP transaction as the order creation, so though the
> created_at date reflects when PHP ran, the order is not searchable by
> OpenERP until the full transaction has processed.

[Guewen Baconnier @ Camptocamp (gbaconnier-c2c)]

Allison Miller proposed:

"""
After some discussion, the most thorough solution we have come up with is
adding 'exported' flags to Magento objects. This eliminates the risk of
entirely missing records, as they can always be caught on the next import.
 For objects that get updated (products, for example) this flag would have
to be reset when updates occur. This will make upgrading existing
instances of the connector a bit tricky, because flags would have to be
properly set initially in Magento after the column is created.

Some other solutions we discussed but did not like are:
- Adding 'committed_create_at' and 'committed_update_at' columns to Magento
objects in the database that are set by MySQL so they reflect times more
accurately. We would still run into a possible millisecond misalignment
here.
- Adding a buffer to import times (for example, if the last job finished
searching at 09:15:00, the next would start at 09:14:30). This would not
fix the problem but it would eliminate virtually all skipped records. With
this approach, duplicate orders would have to be handled differently so
failed jobs would not need to be cleaned up regularly.
"""

[Guewen Baconnier @ Camptocamp (gbaconnier-c2c)]

> After some discussion, the most thorough solution we have come up with is
> adding 'exported' flags to Magento objects. This eliminates the risk of
> entirely missing records, as they can always be caught on the next import.
> For objects that get updated (products, for example) this flag would have
> to be reset when updates occur. This will make upgrading existing
> instances of the connector a bit tricky, because flags would have to be
> properly set initially in Magento after the column is created.

A good solution, but we have to deal with the initialization of the
flag. It adds a write operation on Magento when we only usually only
read. We need to add the field on all the object, and the PHP connector
is actually the part who needs the more love in this project, because
the community has virtually no PHP developer (are you?), so this could
be a problem. It could be the preferred *long-term* solution though.

An other point is that it makes testing more painful when we want to
import a record again and again.

>
> Some other solutions we discussed but did not like are:
> - Adding 'committed_create_at' and 'committed_update_at' columns to Magento
> objects in the database that are set by MySQL so they reflect times more
> accurately. We would still run into a possible millisecond misalignment
> here.

If we have to add fields and search filters on Magento, we better have
to use the imported flag.

> - Adding a buffer to import times (for example, if the last job finished
> searching at 09:15:00, the next would start at 09:14:30). This would not
> fix the problem but it would eliminate virtually all skipped records. With
> this approach, duplicate orders would have to be handled differently so
> failed jobs would not need to be cleaned up regularly.

Seems a bit hacky but this is actually my preferred one for a short-term
solution: this is the less invasive change, a quick and backward
compatible change and does not need changes on Magento. In fact, some
jobs will be duplicated, but most of the duplicated jobs won't do
anything: as the sync_date will be the same, they will be skipped when
executed. Indeed we have to change the import of orders to skip already
imported orders: this is a 2 lines changes.