Page MenuHomePhabricator

Use nightly rather than weekly audit file for Adyen
Closed, ResolvedPublic4 Estimated Story Points

Description

We need to get nightly rather than weekly info for Adyen - change the audit download and parse to use the Payment Accounting Report rather than the settlement details report.

Configure the Payment Accounting Report in the Adyen UI to run every weeknight and to include all the columns we expect. Adjust filenames in code to download and parse this report. Ensure that all expected columns are present and update any column headers as necessary in AdyenAudit.php in SmashPig.

Original text below:

We have seen an uptick in cases of manually settled donations in Adyen taking many days to make it into Civi as the audit file is only sent on a weekly basis (per https://phabricator.wikimedia.org/T313826 - one donated on 07/21 and appeared in Civi on 08/05; another donated on 07/19 and is not yet in Civi).

It is confusing for donors to receive their receipts so long after their donation and they are concerned it processed twice. They are also receiving follow up fundraising emails as their recent donation had not made it into Civi, so they were not opted out of subsequent sends, which is not an ideal donor experience.

As we are moving to Adyen as default PSP for 6C, could we increase the cadence of these sends? Ideally we would have them run at the same cadence as Ingenico, which seems to be more frequent (status 600 donations that are settled generally appear in Civi within a couple of days).

Thanks!

Event Timeline

+ 1 @krobinson We need to put Adyen on the same track that Ingenico was to avoid these lags that could have bigger impact when we are operating at scale.

@EMartin we are using the settlement batch report (https://docs.adyen.com/reporting/settlement-reconciliation ) from Adyen as our audit/reconciliation report. The report is generated every time a settlement batch is ready to be paid out. Our payout schedule is currently set to 'weekly Friday'. At https://ca-live.adyen.com/ca/ca/finance/configurepayouts.shtml you can change that to 'Daily (Mon-Fri)'.

Since that will affect actual transferring of money to bank accounts, and since I don't know if there's a fee associated with more frequent payouts, I haven't touched the setting myself.

Hi @Ejegg Thanks. I will check with Finance. I doubt they will want to increase the frequency since Ingenico has been at weekly but it's a good time to ask. Assume the process was different for Ingenico so we don't see the lags? I'll let you know what I find out from Finance.

I guess Ingenico must not link their reporting schedule to their financial payout schedule.

For Adyen there are other reports available but it would take some work to figure out which (if any) would serve our purposes and to write new code to parse them. It looks like maybe the Payment Accounting Report (https://docs.adyen.com/reporting/invoice-reconciliation/payment-accounting-report ) would serve if configured correctly.

@Ejegg. @krobinson As expected, Finance wants to stay with weekly settlement. I did inquire with Adyen and they advise the following as it relates to the manually settled activity. Can we add this report to the mix to get these settled faster? Would this be a big undertaking? Should I put it in a separate Phab request? It seems like something we need:

For a record of these manually settled transaction, as soon as they are booked as 'Settled', they would appear in the Payment Accounting report for that day (which is a daily file).
https://docs.adyen.com/reporting/invoice-reconciliation/payment-accounting-report#page-introduction
Using this report, you wouldn't need to wait for the actual payout to occur (which is when the Settlement Details report is delivered), for confirmation the payment was successfully booked as settled.

Adyen PSP 122781361.1 has still not come to Civi. There is one difference that I noticed is that it says it was "settled externally". The transaction was settled on Jul 22, 2022.

Ejegg renamed this task from Review Cadence of Audit Files for Manually settled Adyen Donations to Use nightly rather than weekly audit file for Adyen.Aug 25 2022, 4:28 PM
Ejegg updated the task description. (Show Details)
Dwisehaupt set the point value for this task to 4.Aug 26 2022, 4:27 PM

Change 831146 had a related patch set uploaded (by Cstone; author: Cstone):

[wikimedia/fundraising/crm@master] WIP: Audit module part of using the payments_detail_report for audit There will be a smashpig patch to go along with this

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

Change 831159 had a related patch set uploaded (by Cstone; author: Cstone):

[wikimedia/fundraising/SmashPig@master] WIP: Adding PaymentAccountingReport to the files the adyen audit looks at

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

Testing the report downloads is complicated. This is a WIP just so I don't forget steps. I'll move these to the wiki once everything has been tested and worked out.

Patches needed:
Smashpig https://gerrit.wikimedia.org/r/c/wikimedia/fundraising/SmashPig/+/831159
Civi https://gerrit.wikimedia.org/r/c/wikimedia/fundraising/crm/+/831146

If you just want to test downloading the report, you don't need the civi patch

There is a local config file that tells it where to download the report called report-location in smashpig's main.yaml in config-private. I set it to a local test folder for now but I need to figure out how to properly get this tied so it goes into the audit's incoming folder.

Setting up the download on Adyen's end

  1. Log into the test console and make sure you are in the WikimediaDonations merchant account and not WikimediaFoundation. You can switch to it at the top left of the screen.
  1. Create a new Report details webhook in Developers -> Webhooks using the blue button that says Webhook at the top right. If you scroll down a bit you will see Report details Choose that then add in your url. You can look at Christine test as an example.
  1. Generate a test report by going to Reports then click on Payment accounting > (it's a link but it doesn't look like one). On this page there is a list of reports that have been generated as well as a blue Generate report button at the top right. Click that and pick a date that some test donations happened. It will queue a download that sometimes shows up right away and sometimes doesn't for ~1 hour .
  1. Eventually (hopefully quickly) you will see an IPN in the smashpig-syslog that looks like:

<?xml version="1.0" encoding="UTF-8"?><soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><soap:Body><sendNotification xmlns="http://notification.services.adyen.com"><notification><live>false</live><notificationItems><NotificationRequestItem><amount><currency xmlns="http://common.services.adyen.com">EUR</currency><value xmlns="http://common.services.adyen.com">0</value></amount><eventCode>REPORT_AVAILABLE</eventCode><eventDate>2022-09-10T00:44:12+02:00</eventDate><merchantAccountCode>WikimediaDonations</merchantAccountCode><merchantReference></merchantReference><pspReference>payments_accounting_report_2022_08_31.csv</pspReference><reason>https://ca-test.adyen.com/reports/download/MerchantAccount/WikimediaDonations/payments_accounting_report_2022_08_31.csv</reason><success>true</success></NotificationRequestItem></notificationItems></notification></sendNotification></soap:Body></soap:Envelope>

If it processes the IPN correctly you will see:
2022-09-09T22:54:01+00:00 2d3459b285c0 SmashPig-Adyen: SPCID-1021397804 | Notification received from test server. | |
2022-09-09T22:54:01+00:00 2d3459b285c0 SmashPig-Adyen: SPCID-1021397804 | Creating Adyen message object from data. | |
2022-09-09T22:54:01+00:00 2d3459b285c0 SmashPig-Adyen: SPCID-1021397804 | Listener message of type SmashPig\PaymentProviders\Adyen\ExpatriatedMessages\ReportAvailable created. | |
2022-09-09T22:54:01+00:00 2d3459b285c0 SmashPig-Adyen: SPCID-1021397804 | Extracted 1 from received message. Beginning processing loop. | |
2022-09-09T22:54:01+00:00 2d3459b285c0 SmashPig-Adyen: SPCID-1021397804 | Received new report from Adyen: payments_accounting_report_2022_08_31.csv. Generated: 2022-09-10T00:44:12+02:00. | "https:\/\/ca-test.adyen.com\/reports\/download\/MerchantAccount\/WikimediaDonations\/payments_accounting_report_2022_08_31.csv" |
2022-09-09T22:54:01+00:00 2d3459b285c0 SmashPig-Adyen: SPCID-1021397804 | Finished processing of IPN message, retuning accepted. | |
2022-09-09T22:54:01+00:00 2d3459b285c0 SmashPig-Adyen: SPCID-1021397804 | Finished processing listener request | |

This will put a message on the jobs-adyen queue. Example:
"{\"gateway\":\"adyen\",\"account\":\"WikimediaDonations\",\"reportUrl\":\"https:\\/\\/ca-test.adyen.com\\/reports\\/download\\/MerchantAccount\\/WikimediaDonations\\/payments_accounting_report_2022_09_08.csv\",\"propertiesExcludedFromExport\":[\"logger\",\"downloadLoc\"],\"php-message-class\":\"SmashPig\\\\PaymentProviders\\\\Adyen\\\\Jobs\\\\DownloadReportJob\",\"source_name\":\"SmashPig\",\"source_type\":\"listener\",\"source_host\":\"2d3459b285c0\",\"source_run_id\":31,\"source_version\":\"unknown\",\"source_enqueued_time\":1662764009}"

  1. To download the report, in the smashpig container run:

php Maintenance/QueueJobRunner.php --queue jobs-adyen --config-node adyen
This will download it to the location specified in main.yaml

Change 831159 merged by jenkins-bot:

[wikimedia/fundraising/SmashPig@master] Adding PaymentsAccountingReport as a source audit file

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

Change 833870 had a related patch set uploaded (by Cstone; author: Cstone):

[wikimedia/fundraising/crm@master] Create wmf-audit

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

Change 833870 merged by jenkins-bot:

[wikimedia/fundraising/crm@master] Create wmf-audit

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

Change 831146 merged by Ejegg:

[wikimedia/fundraising/crm@master] Adding payments_details_report as an option for the Adyen Audit

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

Change 843587 had a related patch set uploaded (by Cstone; author: Cstone):

[wikimedia/fundraising/SmashPig@master] Add missing types for the Payments Accounting Report

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

Change 843587 merged by jenkins-bot:

[wikimedia/fundraising/SmashPig@master] Add missing types for the Payments Accounting Report

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

Change 844541 had a related patch set uploaded (by Cstone; author: Cstone):

[wikimedia/fundraising/SmashPig@master] Use the right column for gross amount (Original Amount)

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

Change 844541 merged by jenkins-bot:

[wikimedia/fundraising/SmashPig@master] Use the right column for gross amount (Original Amount)

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

Change 845124 had a related patch set uploaded (by Cstone; author: Cstone):

[wikimedia/fundraising/SmashPig@master] Add jcbprepaidanonymous and an example of how to add new methods.

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

Change 845124 merged by jenkins-bot:

[wikimedia/fundraising/SmashPig@master] Add jcbprepaidanonymous and an example of how to add new methods.

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

Change 845193 had a related patch set uploaded (by Cstone; author: Cstone):

[wikimedia/fundraising/SmashPig@master] Add types for Amex

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

Change 845193 merged by jenkins-bot:

[wikimedia/fundraising/SmashPig@master] Add types for Amex

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

Change 852226 had a related patch set uploaded (by Cstone; author: Cstone):

[wikimedia/fundraising/SmashPig@master] Add capturefailued to ignored Adyen audit types

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

Change 852226 merged by jenkins-bot:

[wikimedia/fundraising/SmashPig@master] Add capturefailued to ignored Adyen audit types

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