I just thought of a reasonably easy way to track both the payment made by a borrower, and the payment as applied to charges on a borrowers account. Let's start with the three $5 fine scenario again, which the borrower is paying in full.
1. A payment of $15 is made, it is inserted with a new accounttype ( say BORPMNT ). 2. This payment is broken down into 3 $5 payments, each of which reference one of the existing charges. 3. Each of these payments also reference the $15 payment through a new column, say accountno_pmnt The BORPMNT could either live in the existing accountlines table, or could be stored in yet another table. The real question is: is this necessary? Do any libraries using Koha need to track total payments against individual fine payments? Kyle http://www.kylehall.info Information Technology Crawford County Federated Library System ( http://www.ccfls.org ) On Wed, Sep 30, 2009 at 4:03 PM, Michael Hafen <mdha...@tech.washk12.org> wrote: > On Wed, 2009-09-30 at 15:37 -0400, Kyle Hall wrote: >> > I think the simplest way to manage fines would be to have one table for >> > invoices, and a separate table for credits. Figuring the amount >> > outstanding would be a (relatively) simple sum of a union query. And >> > nether table would ever be touched for updates. >> >> That could easily be step 2, after this is complete. >> > > I'm thinking about this a little more, and there wouldn't have to be > separate tables. The point, as Joe said, is that the table(s) aren't > updated; only inserted into. > >> > The trick to getting this to work is having a third table just for >> > tracking the association of credits to invoices. If a payment is made >> > that is more than a single invoice, more rows would be inserted to >> > reflect that. Similarly if many payments are made for one invoice, more >> > rows would be inserted. >> >> Is there a reason that the payments table couldn't just have a foreign >> key to the invoices table? I assume it's required so a single payment >> can reference multiple invoices. That is a limitation of what I >> proposed. To make a single payment against multiple invoices in my >> proposal, it requires breaking that single payment into multiple >> smaller payments. Not hard to code, but I don't know what impact this >> has on accounting practices. > > Foreign keys are always good, and would be applicable in any case I > think. > > >> > Moving to this sort of structure is the real hitch. It wouldn't be easy. >> > I imagine a couple queries, one to copy credit lines to the credits >> > table and one to copy fine lines to the fines table. The >> > amountoutstanding column could effectively be ignored since it's status >> > should be reflected well by the tracking table. The accountoffsets >> > table could be used to form the tracking table with minimal data loss. >> > If the borrowernumber,accountno combination isn't used for the key, then >> > a temporary column could be added to the accountlines table and filled >> > via an SQL variable to reflect what the key in the new table will be. >> > Ideally there would be no data loss because the accountoffsets table is >> > being used properly in all code. I'm not an idealist. >> > >> >> What is the point of the accountoffsets table? With all the work I've >> done, I've never once had to work with it. > > The accountoffsets table is the tracking table. I realize now that what > I've described isn't that different from what we have now. Drop the > amountoutstanding column and add a key column, and you have it > basically. > > The other difference is how to handle odd payments, which is where we > began. > > The question of splitting the payment to better match the fines payed is > a good one. Not splitting the payment represents what the patron did; > they brought in money and gave it to the library. Splitting the payment > represents what the library did; they took the money and applied it to > fines the patron had. > > The difference is a matter of accounting practices, which I don't know > that well. For all I know an accountant would want both done. Record > receipt of the whole sum, and have a separate table where the credit is > split and applied against fines. > >> >> Kyle > -- > Michael Hafen > Systems Analyst and Programmer > Washington County School District > Utah, USA > > for Koha checkout > http://development.washk12.org/gitweb/ > or > git://development.washk12.org/koha > > > _______________________________________________ Koha-devel mailing list Koha-devel@lists.koha.org http://lists.koha.org/mailman/listinfo/koha-devel
