[freeside] Intended use of 'closed' field in cust_bill and friends. (1.4.0pre13)

[ivan]

On Sat, May 25, 2002 at 05:58:39AM -0700, baloo at gimpgirl.com wrote:
> The API doc and schema both state it's set to Y when the books are closed.

Correct.

> http://www.sisd.com/freeside/list-archive/msg02165.html states that an
> invoice is is open when not paid in full, or a payment when not applied in
> full, or the equivalent for credits and refunds.

This message is somewhat out of date with the new financials, but still
basically correct.  "open invoices" are not the opposite of "closed
books".  You can have "closed invoices" and still have your books open.

> Looking at the source (*.cgi, FS::*.pm, and scripts, I can see the flag's
> tested in a number of places, but I don't see it being set anywhere. Also,
> search/cust_bill.cgi uses owed to select 'open' invoices.
> 
> Turning to the SQL interface in the webdemo (both are very useful for
> exploring, BTW. Thanks for putting them in.), I get the following:
> 
>   select count(*) from cust_bill -> 86
>   select count(*) from cust_bill where closed isnull -> 86
>   select count(*) from cust_bill where closed notnull -> 0
> 
> which confirm my suspicion that the 'closed' field is unused, and that
> 'open' in that message means "owed/unapplied/credited > 0"(*), not 'closed
> isnull or closed != "Y"'.

Correct.

> What am I missing? If 'closed' was meant to be used for "end of financial
> year"-related stuff,

It is.

> or if it has no current or planned use and could
> conceivably be used for that, what would be the missing pieces? Something
> along the lines of the following SQL (untested, possibly ungrammatical)
> comes to mind:
>
> update cust_bill set closed = "Y"
>   where date <= (timestamp for end of financial year)
>     and charged = coalesce(sum(select amount from cust_credit_bill
>                                  where cust_credit_bill.invnum =
>                                        = cust_bill.invnum), 0)
>                 + coalesce(sum(select amount from cust_bill_pay
>                                  where cust_bill_pay.invnum
>                                        = cust_bill.invnum), 0)
> 
> and ditto with suitable changes for payments, credits, and refunds.

Other than the fact that modifying the database directly bypasses the
history mechanism, that's the basic idea, yes.  Use the perl API.

(Yes, the ideal way to keep history would be with triggers in the
database.  I'm still under the delusion that we might support MySQL again 
someday, and it doesn't have triggers yet.)

[wrt cust_bill.owed]
> 'open' in that message means "owed/unapplied/credited > 0"(*)
>
> (*) BTW, there's no mention of a corresponding method for refunds in the
> documentation, or anything in the source that looks like it. Oversight? Or
> complete trust that although customers sometimes pay more than they owe,
> accounts clerks never make that mistake? :-)

Sorry, don't understand your question here.

-- 
_ivan