Hi,
Thanks for the excellent resource of Ledger-Smb, I hope to contribute is some
small way as the opportunity arises. I have minimal skills in Perl, Sql and
documentation.
This post is somewhat lengthy, but I think it's interesting, my apologies in
advance to anyone who reads on.
I'm still using Sql-Ledger-2.6.19 as I haven't had the time to move over to
Ledger-Smb, but will have a go soon. I think the Database and table layouts are
similar enough, at least for what I want to do, after examining the tables of
both smb and sql-ledger.
I've searched the (smb user) mailing list without success regarding a problem
I'm having, namely I'm attempting to obtain a total of item weights after
creation of a quotation, in order to automatically calculate shipping costs to
add back into the quotation before emailing to the customer.
I'm running the query via a separate application, and will make a small
Gtk2-Perl Gui to run the query based on entering a quote number *when* I have a
query designed that actually does what I want. For me this is easier than
attempting to modify the Perl code for the Quotation form, and it's a separate
app that won't need updating with each new version of Smb-Ledger.
The application (when complete) will automatically total the quotation weights
and query the shippers on-line freight costings in conjunction with the
customers postcode. This will provide a fast and hopefully error free (at least
free of *my* errors) amount for insertion into the final quote.
The ability to query the database separately is one thing I love about Ledger.
I have produced a working query, which *almost*does what I want but only works
after the quote becomes an invoice, due to the fact I haven't been able to link
the parts table to the invoice table prior to ar.quonumber being allocated a
value, and the trigger for this doesn't seem
to occur until the quote *becomes* an invoice.
Here is the query I'm using, it works fine *after* the query becomes a quote :-
select ar.quonumber AS QUOTE, customer.name AS CUST, parts.partnumber AS PART,
parts.weight AS WEIGHT, invoice.qty AS QTY, (parts.weight * invoice.qty) AS TOT
from acc_trans,ar,invoice,customer,parts
where acc_trans.trans_id = ar.id and acc_trans.invoice_id =
invoice.id and ar.customer_id = customer.id and invoice.parts_id = parts.id and
(parts.weight * invoice.qty > 0) and ar.quonumber ='q895'
Anyone know if I am totally off the track (it wouldn't be the first time it's
happened), or just confused ?
I admit I'm a bit lost in the database schema, but it is fantastic to have the
smb-ledger documentation to refer to, my thanks again.
Any words of wisdom, criticism, or pointers to URL's etc gratefully received,
and of course my code will be available (GPLd) to anyone that wants it when
it's working (Linux only).
Thanks for your time reading this far.
--
Best Regards
Terry
-------------------------------------------------------------------------
This SF.net email is sponsored by the 2008 JavaOne(SM) Conference
Don't miss this year's exciting event. There's still time to save $100.
Use priority code J8TL2D2.
http://ad.doubleclick.net/clk;198757673;13503038;p?http://java.sun.com/javaone
_______________________________________________
Ledger-smb-users mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/ledger-smb-users
