project60 / org.project60.sepa Goto Github PK

If you want to enable the mandate via API (i.e. set is_enabled=1), you run into a constraint violation:

{
    "error_code":"constraint violation",
    "sql":"INSERT INTO civicrm_contribution (receive_date , contribution_recur_id ) VALUES ( 20131008 ,  5 )  [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`migration_civicrm`.`civicrm_contribution`, CONSTRAINT `FK_civicrm_contribution_contact_id` FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE)]",
    "debug_info":"INSERT INTO civicrm_contribution (receive_date , contribution_recur_id ) VALUES ( 20131008 ,  5 )  [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`migration_civicrm`.`civicrm_contribution`, CONSTRAINT `FK_civicrm_contribution_contact_id` FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE)]",
    "trace":"#0 [internal function]: CRM_Core_Error::exceptionHandler(Object(DB_Error))\n#1 \/Users\/didonai\/Documents\/mamp_root\/migration\/sites\/all\/modules\/civicrm\/packages\/PEAR.php(931): call_user_func(Array, Object(DB_Error))\n#2 \/Users\/didonai\/Documents\/mamp_root\/migration\/sites\/all\/modules\/civicrm\/packages\/DB.php(969): PEAR_Error->PEAR_Error('DB Error: const...', -3, 16, Array, 'INSERT INTO civ...')\n#3 \/Users\/didonai\/Documents\/mamp_root\/migration\/sites\/all\/modules\/civicrm\/packages\/PEAR.php(564): DB_Error->DB_Error(-3, 16, Array, 'INSERT INTO civ...')\n#4 \/Users\/didonai\/Documents\/mamp_root\/migration\/sites\/all\/modules\/civicrm\/packages\/DB\/common.php(1905): PEAR->raiseError(NULL, -3, NULL, NULL, 'INSERT INTO civ...', 'DB_Error', true)\n#5 \/Users\/didonai\/Documents\/mamp_root\/migration\/sites\/all\/modules\/civicrm\/packages\/DB\/mysql.php(898): DB_common->raiseError(-3, NULL, NULL, NULL, '1452 ** Cannot ...')\n#6 \/Users\/didonai\/Documents\/mamp_root\/migration\/sites\/all\/modules\/civicrm\/packages\/DB\/mysql.php(327): DB_mysql->mysqlRaiseError()\n#7 \/Users\/didonai\/Documents\/mamp_root\/migration\/sites\/all\/modules\/civicrm\/packages\/DB\/common.php(1216): DB_mysql->simpleQuery('INSERT INTO civ...')\n#8 \/Users\/didonai\/Documents\/mamp_root\/migration\/sites\/all\/modules\/civicrm\/packages\/DB\/DataObject.php(2421): DB_common->query('INSERT INTO civ...')\n#9 \/Users\/didonai\/Documents\/mamp_root\/migration\/sites\/all\/modules\/civicrm\/packages\/DB\/DataObject.php(1055): DB_DataObject->_query('INSERT INTO civ...')\n#10 \/Users\/didonai\/Documents\/mamp_root\/migration\/sites\/all\/modules\/civicrm\/CRM\/Core\/DAO.php(287): DB_DataObject->insert()\n#11 \/Users\/didonai\/Documents\/workspace\/CiviSEPA\/CRM\/Sepa\/Logic\/Mandates.php(74): CRM_Core_DAO->save()\n#12 \/Users\/didonai\/Documents\/workspace\/CiviSEPA\/CRM\/Sepa\/BAO\/SEPAMandate.php(35): CRM_Sepa_Logic_Mandates::fix_recurring_contribution(Array)\n#13 [internal function]: CRM_Sepa_BAO_SEPAMandate::add(Array)\n#14 \/Users\/didonai\/Documents\/mamp_root\/migration\/sites\/all\/modules\/civicrm\/api\/v3\/utils.php(973): call_user_func_array(Array, Array)\n#15 \/Users\/didonai\/Documents\/workspace\/CiviSEPA\/api\/v3\/SepaMandate.php(48): _civicrm_api3_basic_create('CRM_Sepa_BAO_SE...', Array)\n#16 \/Users\/didonai\/Documents\/mamp_root\/migration\/sites\/all\/modules\/civicrm\/api\/api.php(75): civicrm_api3_sepa_mandate_create(Array)\n#17 \/Users\/didonai\/Documents\/mamp_root\/migration\/sites\/all\/modules\/civicrm\/CRM\/Utils\/REST.php(358): civicrm_api('SepaMandate', 'create', Array)\n#18 \/Users\/didonai\/Documents\/mamp_root\/migration\/sites\/all\/modules\/civicrm\/CRM\/Utils\/REST.php(580): CRM_Utils_REST::process(Array, false)\n#19 [internal function]: CRM_Utils_REST::ajax(Array)\n#20 \/Users\/didonai\/Documents\/mamp_root\/migration\/sites\/all\/modules\/civicrm\/CRM\/Core\/Invoke.php(258): call_user_func(Array, Array)\n#21 \/Users\/didonai\/Documents\/mamp_root\/migration\/sites\/all\/modules\/civicrm\/CRM\/Core\/Invoke.php(70): CRM_Core_Invoke::runItem(Array)\n#22 \/Users\/didonai\/Documents\/mamp_root\/migration\/sites\/all\/modules\/civicrm\/CRM\/Core\/Invoke.php(52): CRM_Core_Invoke::_invoke(Array)\n#23 \/Users\/didonai\/Documents\/mamp_root\/migration\/sites\/all\/modules\/civicrm\/drupal\/civicrm.module(436): CRM_Core_Invoke::invoke(Array)\n#24 [internal function]: civicrm_invoke('ajax', 'rest')\n#25 \/Users\/didonai\/Documents\/mamp_root\/migration\/includes\/menu.inc(517): call_user_func_array('civicrm_invoke', Array)\n#26 \/Users\/didonai\/Documents\/mamp_root\/migration\/index.php(21): menu_execute_active_handler()\n#27 {main}",
    "is_error":1,
    "error_message":"DB Error: constraint violation"
}

If you deinstalled your sepa_dd extension and then try to re-install it, it fails. The reason is, that the tables are not dropped in the correct order, which leads to a "foreign key constraint violation".

Workaround is:

drop table if exists civicrm_sdd_contribution_txgroup, civicrm_sdd_txgroup, civicrm_sdd_file;

and then install it again.

To fix this, one would probably have to provide a proper upgrader implementation.

Currently, SEPA dd specific fields (such as the IBAN of a mandate, or the mandate reference no.) can only be accessed via smarty code.

It would be helpful though, to make these accessible also via tokens.

This is not a "Priority A" thing, but should not fe forgotten when the SEPA DD extensions gets finalized.

(more a braindump of the stuff that ought to be generic)

1. the MessageTemplates entity api
2. the improvements in the contribution templates (link to the recurring contrib if it exists)
3. fix the hardcoded stuff in the payment processor classes (eg if dd, it's possible NOT to have a sortcode and stuff)

Rationale

1. it doesn't work in v1 with one shot sepa contrib anyway
2. and offering the same list of amount to donate as one shot, monthly or other frequencies doesn't quite make sense.

For wikimedia, we are going to have two separate contribution pages (single and recurring) and put a link from one to the other. The recurring contribution page will not have the checkbox offering to pay monthly, it will be the default

Creating a mandate gives a FK violation error in the APOI, because the PP passes the creditor reference to the mandate (a string containing the bank-issued creditor ID) and not the id of the civicrm_sdd_creditor record. I believe there is an error in the data model XML because there does not need to be a FK to contact at all from creditor_id ...

I admin the naming is somewhat confusing, but the creditor ID is an official term. We should use creditorId as the name for the field, and drop the creditor_id.

I'm concerned what this will do to Xavier's running site at WikiMedia ... do you have the creditor_id rigged in those mandates ?

Clean install on 4.4. Created a PP, entered creditor info, upon edit the fields were empty. Similar to the previously mentioned issue. Will check into where this originates.

http://issues.civicrm.org/jira/browse/CRM-12793

We'll need it to assign the contrib to the entities.

The PP does not yet use the creditor record.

Recurring contributions are not always monthly. They can be yearly, or sometimes even quarterly as well.

Also, some people prefer the collection to happen on a particular day of month.

According to my testing, the batching logic can handle all these things just fine -- so we only lack the UI to set these things.

Hi,

the logic to calculate date is broken. (when to batch and so on)

Can you check for
adjustBankDays()
(right now, got a return near the start, I'm the guilty one)

and add some unit test, because that's a hit and miss.

X+

(code sprint with Paul), it's needed for the batching

the pi options are no longer correctly set now that the pi is not fixed but variant by creditor

We need screens to manage the creditor(s) and their settings. There might also be a link from a SEPA dashboard (currently under civicrm/sepa) but I feel a menu, possible under Administer, would be appropriate

for iban, the validation is complete (checksum + country +...)
for bic, just a regexp

The library provides two tools to format the iban (add/remove the spaces at the right space)

I'm tempted to normalise them before storing them. Should be go humane (with space) and add the to_machine formatting on the export xml or store machine friendly and add formatting everywhere it's displayed?

The Creditor ID in the table civicrm_sdd_mandate contains value NULL which is incorrect becaue it gives an error on exporting the Pain.008 file. Setting the creditor id manually fix this error but this should not be the case. The creditor ID should be set automaticly when generating the mandate.

1. upon creation, set mandate status to (dropdown)

Hi,

either pb in config or error in the code but when you validate a mandate, it puts it into a RCUR group instead of the FRST one.

It might/seems to be another issue with the payment type that isn't correct. More digging needed.

X+

Error given when saving :

SEPA Creditor
'9000' is not a valid option for field payment_instrument_id

I (strongly) suspect we are pushing the value instead of the option id into the record.

Hi,

We got one customer that called and asked why she wasn't debited (on the day of the debit). there isn't an easy way to view is the contribution has been batched to this month and she will get the bank debit soonish, or if it was past the cut off date and it was pushed to the next month batch.

it might be an edge case where we couldn't trust 100% the date of contrib, but I think it make sense to be able to see quickly and clearly where a sepa contribution has been processed (batch group and xml file).

I'm going to change the contribution view to add the relevant sdd batch info.

Ok, after 3 months of prod

1. is_enabled on the mandate is not good enough. is_enabled=0 can mean pretty much anything, from it's not validated yet, to it has been validated but then the client invalidated it, to it's on hold, to it was a test, to...

In short: ain't complete enough.
Some information is/can be found on the recurring contrib, but the ui in the core isn't complete enough.

Might be something @antrik fix in the core when working on the UI for recurring contrib?

Anyway, right now knowing if it's a first or a recur is way too complicated (ie. if you look at it the wrong way, it will do something wrong).

I'm pretty sure that we need a status that at least say

1. not validated
2. validated but never used (FRST)
3. validated (RCUR)
4. invalidated/canceled

The recurring contrib has a status (same list as the individual contribs). I think it's good enough and we should use it for the logic of what is the contrib type. I think it will work to cover all cases:

• online mandate with paper validation (!is_enabled->FRST->RCUR)
• online mandate without paper validation (FRST->RCUR)
• import of existing mandates. (RCUR)

Around that FRST/RCUR, there is another issue: it's way more important than though to distinguish between the first and rcur than we though. We discussed about having two payments. I'm more and more convinced that sepa should be 3 payment_instrument_id (FRST,RCUR, single), because the users seems to see them differently as well.

and because it's a mess otherwise. I've spent the afternoon joining half of the tables existing to be able to understand what's going on on the db ;)

X+

Hi @pdelbar ,

Testing/code reviewing. might be I don't understand something. bug or different assumptions:

1. so the batching is triggered on creation, it should be on validation
2. the batch seems to be created at the nearest date available (ie. date + 5 + 1), should be a fixed date (ie. one transaction on the 8th of the month)

So you thought that with the SEPA file format being an European standard, it would work across Europe?

Well, guess again. German banks decided the standard format ain't good enough for them, and came up with an own subset. This would be fine if it was a recommendation of what is most likely to work reliably -- but it's not. According to our initial testing, at least some German banks actually refuse to accept standard SEPA files, and will only take those using the German variant. Hurray for standards...

In other words, we will need some conditionals in the SDD extension, so it can also generate German SEPA files instead of standard SEPA files. Or we could perhaps introduce some hook(s) so an extra "German SEPA" extension could modify the behaviour of the standard SEPA extension as needed... Though the latter would bear a distinct tinge of over-engineering in my opinion :-)

I believe the installer for sepa_dd might be missing some code causing the
SEPA creditor to not remember the entries after a clean install.
civicrm_sdd_creditor does not get updated in my install.

As an initial approach, let's assume there is only one sepa file per month:

two new batch types:
sepa_initial
sepa_recurring

batch statues (we need separate statues than the other batch exports to be sure):

• sepa_open
• sepa_sealed
• sepa_generated
  plus probably a bit more to handle the rest of the workflow

When a new sepa contrib is created (sepa DD) via a contribution page:

1. check if there is one for the creditor that is already sepa_open (and create if not)
2. add a batchEntity record to link the contrib and the batch

so it shouldn't have any sepa contrib that don't belong to a batch

We probably need to create a new class for sepa file to wrap that (and more)

Thought?

So remember that utf8 thing? turns out that the french banks don't like it, they want ascii.

So if you have émeric that wants to contributes, too bad, he needs to change his name (or move to a country that have technologies that dare being less than 20 years obsolete)

Anyway, it means that we need iconv to make sepa dd working.

@pdelbar, you have another suggestion?

Uninstall cannot remove the payment processor type because it is being referenced. The order of removal needs to be changed. Related to #26

When should the validation date be set? when the mandate is set active?

(in that case, we need to change the code)

if not, we need to carify the what is this date (in xml schema)

When creating a membership (backoffice), we need the ability to select SDD as a payment method which would trigger the display of a Creditor/IBAN/BIC selection screen, and generate the mandate as the membership is created.
This is a use case required by two of my customers, and relatively urgent.

If contributions are entered through multiple SDD PPs with different creditor data, they are correctly assigned to different batches; but the batches are assigned to the same file; and when trying to download that file, it blows up with "mixed creditors in the group".

Note that for creditors using a different creditor bank, the batches should never end up in the same file in the first place. For multiple creditors using the same bank, it might depend on the use case a bit -- but I guess in most cases it's also preferable to keep them apart...

Should only be visible when the type 'SDD' is selected.

we need to be able to configure whether we an mix FRST and RCUR in one file,whether multiple creditors can appear in one file ... perhaps the easiest is to have parameters in the creditor to describe
[X] place OOFF, FRST and RCUR transactions in separate XML files (default ON)
[X] keep this creditor's transaction is a separate XML file (default ON).

Function CRM_Sepa_Logic_Batching::batchTxGroup should be altered to respond to these. Currently, it only groups on latest submission date.

Need to make some order and put them. I'm likely to adopt the convention of one method per couple action+entity

I'm not super sure if Paul's (1 class to rules them all) is the best or if we should have a class per entity and each method is an hook.

@pdelbar, opinion?

I validated (is_enabled) a mandate, the recurring and initial contributions have the right date and is batched in a new group (with the right date)

However, the type is RCUR, it should be FRST.
@pdelbar is this a config issue?
"reference":"TXG - RCUR - 2013-08-08",
"type":"RCUR",
"collection_date":"2013-08-08 00:00:00",
"latest_submission_date":"2013-08-08 00:00:00",
"created_date":"2013-07-04 01:40:11",

Moreover, shouldn't the latest_submission_date be the 2013-08-02 (08-5-1)?

We should probably have a few basic scripts ready to import mandates. One use case if new mandates (like from external fundraising), but my current use case is one-time importing of already running mandates. Probably not worth doing a UI for, but a decent script would be nice.

As suggested and described there:

http://forum.civicrm.org/index.php/topic,29999.0.html

One bank in france that shall remain unamed for now seems to have decided that

1. it would be too useful to provide meaningful error messages that gives info like the line number or tag in the xml

2. seems to have decided that you can't have more than one group per file

So to deal with their mess, we need to create two files per month (one for FRST one for RECR)

@pdelbar I'm tempted to keep the logic as of today (several group per file) but have a generator that creates one file per txgroup in the file. Either way, seems a bit hackish, what do you suggest as less sucking?

X+

Saw with Paul, this will be way useful to debug and make the workflow easier to implement.

X+

When following the "Delete" link on civicrm/admin/paymentProcessor , the confirm page has the creditor data appended; and when hitting the Delete button, it fails with form validation errors on the mandatory creditor fields.

Instead of manually receiving the pdf by email (a sizeable % does return scanned mandates instead of by post), having to find the mandate and update it, might be easier to let the user upload directly to a form.

We probably need a validation step anyway (to check it's genuinely the scanned mandate with the signature)

Not sure, that mandate thing makes a lot of overhead without increasing a lot the security. It stands as well as my general appreciation of the sepa process ;)

Hi,

So looking at @pdelbar doc and this one:
https://www.rabobank.com/en/images/SEPA_Credit_Transfer_format_description.pdf

They are a few differences as it seems there are quite a few options to do things differently with the sepa standard.

Anyway, From the doc, I seem to be missing a few information that we need to add to the creditor: bic and iban (that I didn't find in the rabo doc).

Can someone download and test civicrm/sepa/xml?id={idofthefile} and report?

We got a "clarification letter regarding electronic mandates under the SDD Core Scheme and SDD B2B Scheme"

http://www.europeanpaymentscouncil.eu/content.cfm?page=news&news_id=508

I don't think clarification has the same meaning for them than for me.
For what I understand: bla bla bla, each bank decides what is or isn't a valid e-mandate/e-signature bla bla may god be with you and good luck to ya all.

Can someone translate into "english for humans"? is this something we can take into account?

While existing SDD contributions can be (somewhat) managed through the backoffice UI, new ones presently can be added only through the frontend contribution pages. This is very awkward for the office staff, and makes the whole thing look extremely unprofessional...

In the Netherlands single direct debit payments are very, very common. They are called 'eenmalige machtiging' (single payment mandate) and they are (next to the Ideal-payment platform) by far the most used way of paying.

For our summercamps we would like to offer both sepa single payment and iDeal-payments. We have no budget to sponsor, but have some (limited) resources to help coding/bugfixing :-)

Assuming we want to support the membership create backoffice screen to create memberships, it might be useful to enable a link between a membership type and a creditor to use for it.

the mgd is no longer correct as it sets a pp

ok, I don't like the entity names anymore. To be clear: it's my fault, I didn't choose them right enough

$dao['SepaMandate'] = 'CRM_Sepa_DAO_SEPAMandate';
$dao['SepaCreditor'] = 'CRM_Sepa_DAO_SEPACreditor';
$dao['SepaSddFile'] = 'CRM_Sepa_DAO_SEPASddFile';

Why is DAO_SEPA instead of DAO? Sepa is already there in the name

$dao['SepaTransactionGroup'] = 'CRM_Sepa_DAO_SEPATransactionGroup';
$dao['SepaContributionGroup'] = 'CRM_Sepa_DAO_SEPAContributionGroup';

It confuses the hell out of me, TransactionGroup is a "group", ContributionGroup is a join table between contribution and group

what about

SepaTxGroup
SepaContribution

we don't change the structure, just the name so my wee brain won't get all twisted.

@pdelbar @systopia, comments before I break everything in the sprint?