[freeside] Sending and [re]sending statements

[ivan at 420.am]

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 
> >>does
> >>not exist?
> >
> >It seems that you are the interested party.
> >http://pouncequick.420.am/rt/Ticket/Display.html?id=545
> >
> 
> 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.

-- 
_ivan


> Using a view, e.g.
> 
> [warning: schematic, nonusable]
> 
> 	CREATE VIEW combined_view AS
> 	SELECT custnum, date, amount, 'Invoice' as type, invnum as id FROM 
> invoice
> 	UNION
> 	SELECT custnum, date, -amount, 'Payment' as type, pmtnum as id FROM 
> payment
> 	UNION
> 	SELECT custnum, date, amount 'Refund' as type, refnum as id FROM 
> 	refund
> 	UNION
> 	SELECT custnum, date, -amount 'Credit' as type, crednum as id FROM 
> credit
> 
> would make the SQL query many times simpler, e.g.
> 
> 	SELECT sum(amount) FROM combined_view WHERE custnum = ? AND date <= 
> somedate
> 
> 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...
> 
> Randall
>