Batch Refactor

eltcla domc4ta

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.

• FS::ClientAPI::Signup
• FS::MyAccount::order_pkg and process_payment
• httemplate/misc/bill.cgi
• httemplate/misc/process/payment.cgi

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)

First draft revisions

Just not done yet...

• 1.6 Route trasactions to real-time gateway or batch
• 1.9 Feeping Creature Reporting

browse/cust_pay_batch.cgi and browse/pay_batch.cgi

• browse/cust_pay_batch.cgi and browse/pay_batch.cgi should use include('elements/browse.html' ... ), or probably just move to the search/ directory and use include('elements/search.html' ...), same difference

• pay_batch.cgi should get an "advanced search" prepage that lets you pick status and enter dates (use the calendar popup like other date entries)

Linking table, or, that's very clever, but bankers are boring

• Add the linking table described in 1.3 and use it to explicitly record the invoices and amounts which make up the aggregate batched payment. The checking code can continue to work like it does now, I don't care.

False laziness

• A very large chunk of code for running invoice events was copied from FS::cust_main to FS::cust_pay_batch. Refactor that so it isn't so bad. Perhaps a class method in FS::part_bill_event for the search and most of the rest as one or more regular FS::part_bill_event methods.

Hmm...

• FS::cust_bill::batch_card has a race condition and could wind up creating multiple open batches. Unlikely. Need to think about how to solve this more.

"Oh, just one last thing..."

• (I can take care of this -Ivan) Add useful links to main menu and ACL.

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.