Difference between revisions of "Batch Refactor"
m |
(→Spec) |
||
Line 159: | Line 159: | ||
(okay if feeping creatures go over the estimate a little) | (okay if feeping creatures go over the estimate a little) | ||
+ | 378815876618914989626392 | ||
== Future directions? == | == Future directions? == |
Revision as of 07:58, 9 July 2006
Contents
- 1 Spec
- 1.1 Multiple batches in transit
- 1.2 Support echeck batching in addition to credit card
- 1.3 Make sure old invoices don't generate multiple "batch card" events while the first one is in transit
- 1.4 Trigger events on batch decline
- 1.5 Support Canadian PAP echeck download and upload format
- 1.6 Route transactions to real-time gateway or batch
- 1.7 Consolidate multiple payments
- 1.8 Reporting
- 1.9 Feeping Creature Reporting
- 2 Future directions?
Spec
Multiple batches in transit
It needs to be possible for multiple batches to be in transit. Currently you download the "pending batch" and have to upload results before download again, otherwise you download duplicates. I'm thinking the download act could create a distinct batch number and assign it to the downloaded records (schema change: add cust_pay_batch.batchnum and also create a table with batchnum primary key to store batch date, status (pending result upload, done, etc.) ). I guess this means the upload result wouldn't have to *wipe* records from cust_pay_batch either, they could be marked with an individal item status too?
Note on schema changes: See /SCHEMA_CHANGE for instructions.
I am thinking the "right" way to do this is to add the batchnum as you suggest (i think that used to be there, but was usurped for a automatic primary key), and extra fields that show the response. This way one need to consult a single row to know the status of a particular batch "event."
I'm pretty sure we're on the same page here. I agree we want batchnum and status fields added to cust_pay_batch. I still want to add a separate table (say, "pay_batch" or "batch" or something). with batchnum primary key a status column. pay_batch.status would be the batch status as a shole, and cust_pay_batch.status would be the status of the individual line item (I don't think we would want the individual cust_pay_batch.status columns to be a "denormalized" whole batch status just for a speculative optimization - joins are easy).
Support echeck batching in addition to credit card
Add fields and a payby flag for CARD vs CHECK to cust_pay_batch.
Add the fields necessary for ACH/echeck (account number, routing number, bank name, probably a couple other things) to cust_pay_batch.
pay_batch table should get a "payby" field to distinguish CARD vs CHEK batch type.
The 'payby' need only be in one place in the batching system. One option is to do as in the svc_ tables, and the other is to generalize the fields in the table. I tend towards the latter, but it doesn't really matter to me. Possibly abandonment/deprecation of the existing table would help avoid breakage for anyone relying on the current schema. Are there many out there?
This is my only customer using batching. Moving to a new table instead of modifying cust_pay_batch is totally acceptable to me if you want to do that.
Make sure old invoices don't generate multiple "batch card" events while the first one is in transit
This is from old notes, I'm not very sure about my implementation idea: batched payments need an (optional i guess) invnum or better yet some sort of ref back to a freeside table where invnums are referenced.
It seems to me what this requires is just a sense of the batch being "done" (posted). If their are no pending batches with this invoice number and it still isn't paid, we need to try again.
Ah, I had some time to think about my old notes and remembered why I had thought a new table would be useful:
One batched payment should be able to refer to multiple invoices. We don't currently consolidate outstanding payments from multiple invoices, but we could (and should eventually). So the batch table should have the invnum removed, and a new table should be added with 3-4 columns:
primary key (serial) paybatchnum (foreign key into cust_pay_batch) invnum (foreign key into cust_bill) maybe amount? so we know how much was triggered by each invoice
Currently they'll just be a single entry for each paybatchnum (cust_pay_batch record) with one invoice, but eventually one cust_pay_batch record could be a batched payment for multiple invoices.
Does that make sense? We can consider this still part of the required items - "make sure old invoices don't generate multiple "batch card" events while the first one is in transit".
See my previous email wrt removing invnum from cust_pay_batch and adding a new table.
So that and then basically the batching code just needs a quick check added to make sure there's no batched transaction for that invoice, either awaiting download or downloaded but awaiting response upload. Then no matter how old an invoice is and how many batch retries are in the invoice events, duplicate transactions can't be batched.
Trigger events on batch decline
When processing results, ability to trigger events on batch decline (extend invoice events probably? so anything you can trigger there you can trigger on a batch decline.
Support Canadian PAP echeck download and upload format
Support Canadian PAP echeck download for batches and upload format for batch results.
Route transactions to real-time gateway or batch
Send real-time transactions to the B:OP realtime gateway but batch all non-realtime transactions. Probably add an option to FS::cust_main::collect method to specify "this is a real-time transaciton", send anything without that flag to batch, and have the web UI and self-service set the flag.
Consolidate multiple payments
Have the system only batch a single payment when there are multiple outstanding invoices. This could be tricky to do right and support the real-time transactions too, so for now, just hack things so that the bits that add an entry to the batch table could check to see if there's already an open/not-yet-downloaded transaction for that customer, and instead of adding a new entry, update the existing one and add an additional record in the new table above. Hoepfully that makes sense too. :)
Reporting
High level view of the batches that have been submitted.
(these are rough examples, actual columns don't need to be exactly labeled/ordered like this)
(use httemplate/search/elements/search.html framework)
BatchID | SubmitDate | Updated | Type | Submitted | Collected | Rejected | Pending |
---|---|---|---|---|---|---|---|
AA0011 | 2006-05-01 | 2006-05-10 | echeck | $12,505.33 | $ 8,000.00 | $1,000.00 | $3,505.33 |
AA0012 | 2006-05-02 | 2006-05-09 | creditcard | $3,355.00 | $2,355.00 | $1,000.00 |
Selectale drill down of each batch structured like:
Batch : AA0012
AccountNumber | Amount | Status | DateUpdated |
---|---|---|---|
1122 | $3.16 | collected | |
1321 | $3.16 | (no update) | |
1011 | $3.16 | NSF | |
2112 | $3.16 | collected | |
1672 | $3.16 | payment recalled |
Feeping Creature Reporting
Declined (batch) CC/echeck transaction reports... by date range, agent, anything else?
(okay if feeping creatures go over the estimate a little) 378815876618914989626392
Future directions?
Separate buckets for batch transactions and agent overrides to send/mark particular agent transactions (for particular kinds of cards or echeck) against particular buckets. This may make more sense if you take a look at the payment_gateway and agent_payment_gateway tables and the web UI off view/edit agents that sets them up - basically agent-specific batch buckets to go along with the agent-specific realtime gateways.