Re: [SQL-Ledger-users] Partial payments

[Antonio G. <ag...@ib...>]

I think the critic is well addressed. We must work together if we want to=
=20
have a very nice SQL-Ledger. Really, the app seems very promised, but we =
need=20
to boost the development and for this is very important the team working.

We are still waiting for the 1.4 version.

I learned Perl, because SQL-Ledger exists! ... Well I am a newbie on Perl=
=2E=20
but I try to write Perl code for SQL-Ledger ;-)

What is new in SQL-Ledger 1.4?

We can put some devel-info in the home page to help new developers to go =
into.=20
For example when I start looking into SQL-Leger, I draw a Schema of the D=
B on=20
Pg-Access. This schema is very useful for the developer. I will send the=20
schema later.

Well, I know SQL language better than Perl ! I wrote some Queries in Pg-A=
ccess=20
that helps to maintain the data consistency:


Query name:=09BadTransac
Descriptions:=09Display transactions where the balance is out of the rang=
e
=09=09<-0.005; 0.005>. This transacctions are not correct, and must be
=09=09corrected, because some times (debits-credits) is not 0.
Code:=09=09SELECT t0."trans_id", SUM(t0."amount") FROM "acc_trans" t0 GRO=
UP BY
=09=09t0."trans_id"  HAVING ABS(sum(t0."amount"))>0.005 ORDER BY t0."tran=
s_id"
=09=09ASC;
Use:=09=09When you find some transaction with this query I try to correct=
 the rows
=09=09of the transaction to get (debits-credits=3D0).


Queryname:=09LostSons
Description:=09in early versions of SQL-Ledger, sometimes happens that in=
 the
=09=09DB was account details without header in any of the tables ar, ap, =
or  gl.
=09=09Then I wrote this Query to find this kind of "Lost Sons" or "Detail=
s
=09=09without Headers".
=09=09Displays the rows of the table acc_trans, that have not header in a=
ny of
=09=09the tables ar,ap or gl.
Code:=09=09SELECT * FROM acc_trans WHERE issell(trans_id) =3D NULL AND
=09=09isgl(trans_id) =3D NULL AND isbuy(trans_id) =3D NULL

Note:=09=09The query use some custom functions: "issell", "isinvoice" and=
 "isbuy":
=09=09Function name:=09issell
=09=09Parameters:=09int4
=09=09Returns:=09=09bool
=09=09Language:=09sql
=09=09Code:=09=09SELECT TRUE FROM ar WHERE id=3D$1

=09=09Function name:=09isgl
=09=09Parameters:=09int4
=09=09Returns:=09=09bool
=09=09Language:=09sql
=09=09Code:=09=09SELECT TRUE FROM gl WHERE id=3D$1

=09=09Function name:=09isbuy
=09=09Parameters:=09int4
=09=09Returns:=09=09bool
=09=09Language:=09sql
=09=09Code:=09=09SELECT TRUE FROM ap WHERE id=3D$1
Use:=09=09When you find any row on the table acc_trans that meets this cr=
iteria,
=09=09please delete the row. I know that we can deletes the row on a SQL =
queries,
=09=09but still intrigate me what and when appears the "Lost Sons". I thi=
nk that
=09=09the "problem of Lost Sons" does not exist anymore since version 1.2=
=2E10. But
=09=09I still search some times for "Lost Sons" and this query helps to.


Query name:=09BadBalances
Description:=09Display the account numbers and more info about when the b=
alance=20
=09=09in the table chart is not equal to the balance of the account in th=
e table
=09=09acc_trans. I called the balance based on the table acc_trans "real =
balance".
=09=09I use here again the interval <-0.005; 0.005>.
Code:=09=09SELECT accno,balance, realbalance(accno) FROM chart WHERE
=09=09ABS(balance - realbalance(accno)) > 0.005 ORDER BY accno
Note:=09=09The query use one custom functions:
=09=09Function name:=09realbalance
=09=09Parameters:=09int4
=09=09Returns:=09=09float8
=09=09Language:=09sql
=09=09Code:=09=09SELECT SUM(amount) FROM acc_trans accno=3D$1
Use:=09=09When you find any row when run this query, change the field "ba=
lance" of
=09=09table "chart" to the value of the field "realbalance" of the query.

Also, I wrote two subs to help to format the amounts into a format three=20
digit comma separated. Example: 1000.00 will be 1,000.00.

I put this code at the end of HTML.pm, because like DB subs like=20
"update_balance", I use it in almost every page. Note, that this code ins=
erts=20
a comma every three digits and for this, we cannot sum the output like a=20
"normal number". But, Sid has the last word about.



### Start of code ###

# Formats an amount into a number with two digits containing commas every
# third number.
sub to_money($amount)
{
=09my($self, $amount) =3D @_;
=09return (&commify(sprintf("%.2f", $amount)));
}

# Performs the comma step
sub commify
{
=09local $_ =3D shift;
=091 while s/^(-?\d+)(\d{3})/$1,$2/;
=09return "$_";
}

### End of code ###


Use of the code. For example: on ca.cgi of the version: 1.2.10 I wrote:

$ca->{credit} =3D $form->to_money($ca->{credit});

Please see the attached file ca.cgi

Also, I sent a new report that work with a template. I called this report=
=20
"Comprobante", because I don't know what is the correct name in English.


That's is all by now. I hope this will help us to make a better SQL-Ledge=
r=20
accounting experience.

Regards,

Ing. Antonio Gallardo
AG Software, S. A.
Managua, NICARAGUA.