[freeside] Sending and [re]sending statements
ivan at 420.am
ivan at 420.am
Mon Jun 30 14:02:40 PDT 2003
On Mon, Jun 30, 2003 at 04:34:49PM -0400, Randall Lucas wrote:
> (let me know if this is best moved to -devel)
Probably. Follow up there if you have further questions.
> >>2. Again, does there exist anywhere a canonical method for producing a
> >>statement, that is, a list of all bill, pay, credit, and refunds,
> >>within a given period, with a balance-forward and end of period
> >>balance, for a given time frame?
> >>Would there be any interest in putting such a thing together if it
> >>not exist?
> >It seems that you are the interested party.
> Architectually, how would you feel about using a view in Postgres?
I'd weigh the benefit of introducing a dependancy on a new database
feature versus the benefit over implementing with the database features
we use now. I believe DBIx::DBSchema would need to be udpated to handle
views as well.
In this case, there's already a "balance_date" method for cust_main
objects, so your first query would be providing a (hypothetical)
perforamnce optimization rather than any new functionality.
The statement functionality also seems trivial to construct without the
use of a view, perhaps generalizing the code used in
httemplate/view/cust_main.cgi into methods useful in other contexts.
> Using a view, e.g.
> [warning: schematic, nonusable]
> CREATE VIEW combined_view AS
> SELECT custnum, date, amount, 'Invoice' as type, invnum as id FROM
> SELECT custnum, date, -amount, 'Payment' as type, pmtnum as id FROM
> SELECT custnum, date, amount 'Refund' as type, refnum as id FROM
> SELECT custnum, date, -amount 'Credit' as type, crednum as id FROM
> would make the SQL query many times simpler, e.g.
> SELECT sum(amount) FROM combined_view WHERE custnum = ? AND date <=
> would give you the customer's balance as of somedate. Likewise, a
> statement for a time period is as simple as
> SELECT type, id, date, amount FROM combined_view WHERE custnum = ?
> AND date >= begindate AND date <= enddate
> Unfortunately, MySQL isn't going to even try for views until 5.1. (in
> the meantime, if someone is porting to MySQL 4, it would be possible to
> encapsulate the statement-retrieval code so that it could be extended
> by a MySQLer) It would be possible to implement the statement
> functionality in Perl, but it would be significantly less efficient,
> and this is really something for which views were born...
More information about the freeside-users