ok, here we go....
first, after I wrote this process, I found a public php class for dd, which would have been helpful:
https://github.com/digitick/php-sepa-xml second... code is provided as is, no guarantee expressed or implied, yada yada yada... no specific licence, just use it
third.... it is based on a2billing 1.4, with one change; I've added two columns on the table cc_invoice, total_amount and open_amount, which make life easier for reporting. they are updated with triggers. no perf issues as the base tables are not updated that often.
alter `cc_invoice` add (
`total_amount` decimal(15,5) DEFAULT '0.00000',
`open_amount` decimal(15,5) DEFAULT '0.00000',
) ;
the triggers are in the attached file
http://pastebin.com/17xZTwSgthen I've created two tables that hold the customer's bank detail
http://pastebin.com/hdujZvKDNo gui is provided, just insert the data in direct_debit - it is only needed once - in addition to the obvious fields, rum is the Unique Mandate Reference, isactive is just a switch that says if dd is activated (you may want to suspend/disable), and isfirst should be set to 1 for the first direct debit. My undestanding of SEPA is that the very first debit for a customer should be in a separate payment info block, so that's what it's used for.
the script consists of two files:
- holidays.php :
http://pastebin.com/GijvX0nJ- sepa.php :
http://pastebin.com/bK3Dfi6wobviously, the meat is in the sepa.php - you'll need to customize the first variables, and probably the $detailqry & $postqry so they select only the invoices you require.
the script first generates the file, then updates the database. error processing is minimal....
note that before doing any db updates, you are prompted, so you can review the file before screwing your db !
So far, I've successfully issued 2 files to my french bank, the sepa file also passes various sepa validators online.
Feedback is appreciated !
Enjoy !!!
J.