[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: wxPOS almost there

Hello there,
After being playing with this, I found that was easy giving variables to database functions than write a complete query. It works fantastic for small things like search a part for a specified client (I would like to use barcode scanner to search by partnumber) but when we talk about an invoice, there are many variables and a big array of parts with its own variables. I have the idea to have a temporal table to store step by a step the invoice and then a final function to post it. I'm looking for lesser interaction as possible between application client and database in order to save bandwidth and simplify client written.

Lets say on pseudo code:
- When user establish a connection gets unique session id or recovers its own.

- Starting a new sales invoice -> invoice_new($customer)
--- check session id by user
--- recover invoice not finished? YES -> return w_t_s reference
--- NO -> create table/space/object/whatever_to_save drop if exists sesion_id
--- save customer_acc_ids, discount_id on w_t_s
--- return w_t_s reference

- Add parts -> invoice_add($reference, $part_id int, $partnumber text)
--- check session and reference
--- select part id/number
--- select join part and customer taxes
--- apply taxes
--- apply discount
--- save what is needed to update into accounts.
--- return price_to_show

- Del Parts -> invoice_del($reference, $reference_id)
--- check session and reference
--- ...

- Post -> invoice_post($reference, $amount_to_charge)
--- check session and reference
--- check what is recorder on db against amount to charge
--- start store procedure
---- get all my stored data
---- save invoice
---- update accounts
---- drop temporal table
--- end sp
--- return new_invoice_id

- Show -> invoice_show($id int)
- return view_without_taxes

- Show -> invoice_show_detailed($id int)
- return view_with_taxes

Well, this is just an idea that I would like to realize but I have not the experience on PGsql or LedgerSMB to know if it is a good one or better drop it. Not an idea on how it would impact  database performance, what is better to use as temporal record or worse if it is not following LedgerSMB route.

I'm having a good time learning plpgsql but it would be better if it helps the project.
Any feedback would be appreciate it!

On Sun, Apr 15, 2012 at 10:36 PM, Chris Travers <..hidden..> wrote:
Sorry about the delay here.

On Thu, Apr 12, 2012 at 6:59 AM, Andres Basile <..hidden..> wrote:
> Hello Havard,
> I would like to finish this, at least a basic invoice but I'm having issues
> understanding how to record the invoioce on the database, so far:
> 1. New Acc Transaction
> -Id, table_name
> 2. New AR
> -Date, invoice true, total with taxes, total without taxes, total paid
> 3. New Invoice
> -Foreach part { trans_id, sell price, qty }
> 4. Inventory
> -Foreach part {Parts_id, qty, shipping_date, trans_id}
> 5. Taxes
> How should I record them? I'm summing total amount for each tax account and
> then a transaction

These get recorded in acc_trans.
> 6. Cost Account
> From where I have to get the part cost? Maybe an old transaction when I
> bought it?

The current approach is to look at the invoice table and check qty
against allocated and build up COGS that way.

I would like to move this into a stored procedure, as that would make
it easier to make the logic consistent.  If you can give me a few
days, I can try to do this.

Best Wishes,
Chris Travers

For Developers, A Lot Can Happen In A Second.
Boundary is the first to Know...and Tell You.
Monitor Your Applications in Ultra-Fine Resolution. Try it FREE!
Ledger-smb-devel mailing list