Page MenuHomePhabricator

Civi is not updated with actual Dlocal volume/$$
Closed, ResolvedPublic2 Story Points

Description

Noting an issue from August / September under thread: "Re: Fail Mail (civi1001) run-job: AstroPay nightly audit download failed with code 1"

Elliott and I had a call with Dlocal to troubleshoot the problem discussed in this thread. Elliott realized during the call that the fact that we couldn't download the file had to do with some system setting related to the DDOS attack. Once he made a change, the file download worked. The call ended with Dlocal and Elliott was going to gather all pending files not downloaded to update our civi records. This does not appear to have happened as our recon is grossly off for August and September noted here:
herehttps://docs.google.com/spreadsheets/d/1_3wqCttHtwlBgGnpZvlfL1l2n2Ba5lJRhl-vYVTIJvs/edit#gid=1202991057

It seems we need to update our civi info with all of the files we were unable to grab while experiencing this issue so we can reconcile and have record of all transactions processed via dlocal. Trending to a low amount usually, but we ran some India tests during this period so we are missing 10's of thousands in the recon.

Details

Related Gerrit Patches:
wikimedia/fundraising/crm : masterFix payment method update fn to add account relationship
wikimedia/fundraising/crm : masterFix account relationships for new methods

Event Timeline

EMartin created this task.Oct 10 2019, 8:10 PM
Restricted Application added subscribers: Sadads, Aklapper. · View Herald TranscriptOct 10 2019, 8:10 PM
Ejegg added a subscriber: Ejegg.Oct 17 2019, 3:06 PM

Hi @EMartin, how are you getting the numbers for the google spreadsheet? If I do this search in Civi:

Date Received - greater than or equal to "August 1st, 2019 12:00 AM" AND less than or equal to "August 31st, 2019 11:59 PM" ...AND...
Contribution Status In Completed ...AND...
Gateway = astropay

I end up with 6,440 results. It's still less than the 6,654 you have for Astro Console / August, but far more than the 2,258 you have for Civi / August.

Once we fixed our firewall (looks like Sep 23), we were able to successfully download all of the report files for the period when the download was failing (looks like Sep 7 - 23), and we processed them the same day.

Hi Elliott,

I just reran the recon report out of civi, which I use for all recons and
came up with the same result (give or take a few $) for August. The report
name is:
Gateway Reconciliation MINIMAL

As you can see, Astropay doesn't align to your numbers, however, Amazon and
Adyen match to the respective provider portals. Astropay/Dlocal seems to
be the only one grossly off to the report.

Row Labels Sum of Total Amount (USD) Sum of Number of Contributions
adyen 3741.19 316
amazon 202917.38 14921
*astropay* *16855.8* *2263*
benevity 43858.45 2985
bitpay 2196.35 8
citibank 5286.79 62
engage 46215.28 564
globalcollect 265902.63 42421
ingenico 1228835.12 91795
paypal 197046.11 31356
paypal_ec 1317299.86 135446
(blank) 1152878 632
Grand Total 4483032.96 322769

Ejegg claimed this task.Oct 17 2019, 3:56 PM
Ejegg moved this task from Backlog to Doing on the Fundraising Sprint Usual Subscripts board.
Ejegg added a comment.Oct 17 2019, 4:12 PM

OK, digging in I see there's a filter in that report on the 'to_financial_account_id' of the civicrm_financial_trxn table, and that for AstroPay / DLocal, many of those rows have a NULL value in 'to_financial_account_id'. I'll try to see if those correspond with the way the contribution was recorded.

Ejegg added a comment.Tue, Nov 5, 5:00 PM

Aha, found it. The ones with NULL are all using newer payment methods. Something's broken in the way we add new ones, and we're not getting the rows we need in civicrm_entity_financial_account which say which accounts should map to which payment methods.

Excellent. Do you need anything from Dlocal to troubleshoot resolution here?

Ejegg added a comment.Tue, Nov 5, 5:12 PM

Nope @EMartin, this is purely CiviCRM internals. I'll fix the existing records first, then see what we need to do to make sure that any new payment methods we add in the future end up with the right configuration.

Change 548809 had a related patch set uploaded (by Ejegg; owner: Ejegg):
[wikimedia/fundraising/crm@master] Fix account relationships for new methods

https://gerrit.wikimedia.org/r/548809

Change 548823 had a related patch set uploaded (by Ejegg; owner: Ejegg):
[wikimedia/fundraising/crm@master] Fix payment method update fn to add account relationship

https://gerrit.wikimedia.org/r/548823

Change 548809 merged by jenkins-bot:
[wikimedia/fundraising/crm@master] Fix account relationships for new methods

https://gerrit.wikimedia.org/r/548809

OK, the missing account references have been added for all the contributions made with new payment methods, and the code has been fixed so the next one we add gets the right setup.

@EMartin can you verify that the report gives you some plausible numbers now?

@Ejegg Thanks Elliott, the numbers are definitely within the realm now. Things definitely moved from 60% off to 4% off. With the changes Dlocal is proposing in a separate task, all should come into alignment now. Thanks. We can close this one!

Ejegg added a comment.Wed, Nov 6, 8:36 PM

@EMartin out of curiosity, are Civi's numbers higher than those from their console, or lower? I'd like to know if Civi is missing contributions or fees.

Civi is lower in count but higher in amount, as follows:

Astro Console CIVI Finance
Trnx # Trxns amount Trnx # Trxns amount
1,056 $677.91 713 $1,282.96 $678.00
6,654 $36,712.69 6540 $38,077.08 $36,713.00
4,506 $29,766.81 4,462 $30,898.59 $29,767.00

Change 548823 merged by Eileen:
[wikimedia/fundraising/crm@master] Fix payment method update fn to add account relationship

https://gerrit.wikimedia.org/r/548823

Ejegg closed this task as Resolved.Sat, Nov 9, 1:21 AM
Ejegg triaged this task as Normal priority.
Ejegg set the point value for this task to 2.