Batch Refactor

2008-12-19T09:56:14Z

OudarAlget: varlid

ouricalolo
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)

{| border=1 cellspacing=0
! 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
{| border=1 cellspacing=0
! 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 -[[User:Ivan|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.

Freeside - User contributions [en]

Batch Refactor