[freeside] Sending and [re]sending statements

[Randall Lucas]

(let me know if this is best moved to -devel)

>> 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?  
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