Batch Refactor

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 a "payby" field and 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 too then, to distinguish CARD vs CHEK batch type.

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)

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

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.