Shipping Method and Transaction Fees removed from Enterprise

[emilyjeanrogers]

Description

Enterprises appear to have had Shipping Method and Payment Method fees removed from their account (noted on 21st July).

Specific Example

Prom Coast Food Collective (2552) had an order cycle that closed on 20th July at 4pm. There are four orders that were placed between 2.38pm and 3.50pm on 20th July that have a $15 credit owing equal to the shipping method fee for the selected shipping method.

R421582413
R221736220
R046212123
R752741780

NOTE: it appears that the $15 'credit owing' status was being updated continually in OFN from some time on 21st July. Investigations earlier in the morning only identified 2 and 3 impacted orders, but by 12pm there were 3 impacted orders.

Prom Coast provided details of Order number R421582413 as follows:

• A customer has placed an order and paid by Stripe.
• The Stripe payment includes a $15 fee.
• The order total does not reference this and specifies a $0 shipping method fee
• The Shipping Method that was selected is supposed to have had a $15 fee charged
• Logging in using Admin shows no Shipping Method or Payment Method fees currently for this Enterprise
• There should be fees for this Enterprise as follows ($10 or $15 for shipping methods, Admin Fee on Each Order, 1.75% Fee on Stripe Payments)
• These fees were charged for other customers in this order cycle

**Other Enterprises also impacted **
These Enterprises also appear to be missing Fees (to be updated here as more come to light):
Fat Beets Food Hub (2070)
New Life Farm (2026)
North Arm Farm (2331) - Confirmed $1 transaction fee missing

Steps to Reproduce

• Review Prom Coast Food Collective Orders before and after July 20th/21st
• See Order numbers above with Credit owing
• See that Prom Coast Food Collective Fees are now all set at zero

Animated Gif/Screenshot

Workaround

Severity

S2

https://github.com/openfoodfoundation/openfoodnetwork/wiki/Bug-severity
-->

Your Environment

• Version used:
• Browser name and version:
• Operating System and version (desktop or mobile):

Possible Fix

[emilyjeanrogers]

The landscape is constantly changing while we investigate.

• The number of transactions with Credit Owing increased through the morning to a total of 4.
• The amount of credit owing has changed for at least one order
• Before midday on July 21st all of the impacted orders had a zero shipping method.
• As at 1.15pm they all have had a shipping method applied. Have checked with customer to see if they are manually editing orders

Screen shot at 10.52am showing an Order total amount of $86.81 and Credit owing of $15

Screen shot at 11.20am shows same Order Total with no shipping method fee charged:

Screen shot at 12.50pm shows a Stripe transaction of $101.81 and a Credit Owing of $5.

Earlier (around 11.20am the Credit Owing was $15)

Status as at 1pm July 21st is that 3 of the Orders have a Credit owing of $15 and one of the Orders has a different credit owing of $5 because of a new shipping method having been applied since this morning.

[emilyjeanrogers]

There appears to be a relationship between this issue and #5774 as a) the same enterprises are impacted, in b) the same order cycle and c) missing/incorrect Shipping Methods are involved.

There is no correlation between the impacted Orders logged in #5774 but the impacted orders in this issue took place since 5774 was logged. In this issue, the orders have a status of Credit Owed, and in 5774 they had a status of Failed.

[chezaorchard]

Have found four other examples of the same issue for Ceres Joes Market Garden, Enterprise 2312

Orders: R164812604, R121757548, R730781748 & R354476000

Expected Behaviour: shipping method fee should be $6.

Actual: shipping method $0 in order, shipping method fee $0 under ENTERPRISE - SHIPPING METHOD list. Enterprise has NOT updated or changed shipping method fee.

R354476000 was completed July 18, 2020 3:55 PM - this is the earliest instance of this issue we have found.

[chezaorchard]

Updated affected enterprises:

Melbourne Farmers Marketplace (2542)

• Enterprise fees (two different fees) and adjustment fees (Tax/GST) are charging at 0
• Order payment is successful, no error message or credit due message displayed in order list
• only affects orders since July 21

Wangaratta Farmers' Market Hub (2630)

• Enterprise fee is charging at 0
• Order payment is successful, no error message or credit due message displayed in order list
• only affects orders since July 21

[mbudm]

I've been trawling through the prod data locally and haven't found much except what we know - the equivalent 'spree_shipping_rates.cost' is 0 for the reported orders.

For example: R354476000

open_food_network_prod_bak=# SELECT number, updated_at, shipment_state  FROM spree_orders WHERE number = 'R354476000';
   number   |         updated_at         | shipment_state 
------------+----------------------------+----------------
 R354476000 | 2020-07-18 05:55:55.841711 | ready
(1 row)

open_food_network_prod_bak=# SELECT * FROM spree_shipping_methods WHERE name = 'Delivery $6';
 id  |    name     |         created_at         |         updated_at         | display_on | deleted_at | require_ship_address |                                description                                 | tracking_url 
-----+-------------+----------------------------+----------------------------+------------+------------+----------------------+----------------------------------------------------------------------------+--------------
 938 | Delivery $6 | 2020-04-20 04:12:58.527686 | 2020-04-21 06:14:27.850062 |            |            | t                    | Delivery to Coburg, Coburg North, Preston, Reservoir, Brunswick, Thornbury | 
(1 row)

open_food_network_prod_bak=# SELECT id, number, updated_at, shipment_state  FROM spree_orders WHERE number = 'R354476000';
   id   |   number   |         updated_at         | shipment_state 
--------+------------+----------------------------+----------------
 893706 | R354476000 | 2020-07-18 05:55:55.841711 | ready
(1 row)

open_food_network_prod_bak=# SELECT id, order_id  FROM spree_shipments WHERE order_id = '893706';
  id   | order_id 
-------+----------
 67089 |   893706
(1 row)

open_food_network_prod_bak=# SELECT id, cost  FROM spree_shipping_rates WHERE shipment_id = '67089';
   id   | cost 
--------+------
 202281 | 0.00
 202282 | 0.00
 202283 | 0.00
(3 rows)

I'm not sure if 3 results for spree_shipping_rates that match a single shipment_id is a clue. Here's the same data with dates:

open_food_network_prod_bak=# SELECT id, cost, shipment_id, created_at, updated_at  FROM spree_shipping_rates WHERE shipment_id = '67089';
   id   | cost | shipment_id |         created_at         |         updated_at         
--------+------+-------------+----------------------------+----------------------------
 202281 | 0.00 |       67089 | 2020-07-21 05:04:37.246532 | 2020-07-21 05:04:37.246532
 202282 | 0.00 |       67089 | 2020-07-21 05:04:37.2485   | 2020-07-21 05:04:37.2485
 202283 | 0.00 |       67089 | 2020-07-21 05:04:37.250165 | 2020-07-21 05:04:37.266055

All created/updated at around the same time, 3 days after this order....

[chezaorchard]

@luisramos0 - example that has not been edited (we have not updated or edited) as requested:

Enterprise: North Arm Farms (2331)
Transaction fee should be $1 for all orders

Order: R564437026
Transaction fee displaying $0

[luisramos0]

This is a bug in this PR: https://github.com/openfoodfoundation/openfoodnetwork/pull/5613/files#diff-6e29c23009b98ed84acfcde3a57a1600R36
The calculator names were not updated in spree_preferences so all payment and shipping methods and enterprise fees calculators were without value and currency.

To be clear, this is a bug introduced in v3.1.1, I'll remove the v3-regression as it is not related to the upgrade itself, it's a bug in a specific PR of the normal release process that is now happening in v3.

I think I have fixed the issue in AUS live by running the follow SQL statement:
openfoodnetwork=> update spree_preferences p set key = replace(p.key, '/spree/calculator', '/calculator') where p.key like '%/spree/calculator%' and not exists (select 1 from spree_preferences pp where pp.key = replace(p.key, '/spree/calculator', '/calculator'));
UPDATE 1970

I'll take some time now to validate the orders in the system now and check if the problem is solved or not.

[emilyjeanrogers]

I've been contacted by Melbourne Farmers Marketplace this morning and a) their admin fees are still not being charged and b) their admin fee is set to zero still in their settings but it should be $5. I think this means that the fix for #5795 has not completely worked. Re-opening the issue and continuing to investigate.

[emilyjeanrogers]

Actually it's still open!

[emilyjeanrogers]

It looks like the only Enterprise that was still impacted today was Melbourne Farmers Marketplace who lost their Admin fee again this morning. Since re-applying it manually there appear to be no new issues. All orders coming through today appear to have all fees associated.

[luisramos0]

ok, that's good news 👍
The only possible explanation I have for that enterprise is the fact that the fix was not executed for the fees that had been changed already since the release, there were 20 fees that were not updated because someone had already updated them manually in the UI since the release.
Maybe this was one of them (no way to tell now). Maybe someone had edited this fee after the release but not changed the amount to the correct value, in this case, maybe... someone opened the fees page and saved it making the amounts updated (not fixable by my fix) but with the incorrect amount.

[emilyjeanrogers]

there were 20 fees that were not updated because someone had already updated them manually in the UI since the release.

Hi @luisramos0 I'm not sure exactly what you mean above when you say 20 fees were not updated in the fix. Do you mean 20 orders that went through with fees missing, or 20 enterprises whose fees were not reinstated in admin.. or something else? Either way, It would be good to get a list to cross-check against our refund list if possible? We are currently going through every order manually to confirm which enterprises will need to be reimbursed for lost fees.

[luisramos0]

Hello Emily, I meant 20 fees (fees can be payment method fee or shipping method fee or enterprise fee). I mean the fees themselves, the config, not the values on the orders. I think this was your doubt, right?
So, 20 enterprises whose fees WERE reinstated manually in the admin.

We can get a list of orders from the point of the release until the fix was executed. I will only be able to do this in about 48 hours.

[emilyjeanrogers]

@luisramos0 Sorry for the slow response on your question above - I've been bogged down in lists of orders and fees owing! We have gone back over all the lists multiple times and my brain is in knots.

My biggest outstanding concern is that there are Enterprises out there whose fees dropped off and are still missing, but they don't know. If I'm understanding correctly, this should only have happened in the 20 cases where a fee was fixed manually - yes? If so, then a list of those enterprises/fees would be good to cross check against... if possible?

[luisramos0]

I dont think I can get that list of 20 now. But these 20 were the ones that were fixed manually, so they will be correct.
Anyway, let's continue the conversation in #5836