On Fri, Aug 30, 2002 at 01:33:32PM -0500, Susan Murray wrote:
> On Fri, 2002-08-30 at 13:08, Dieter Simader wrote:
>
> > Not at all. You must write an import routine which either generates sql or
> > a commandline argument to be passed to the appropriate script.
> Can you import directly to the database? What I mean is we would write
> a script in php to export from our web database which would post to a
> table the last order number exported, last customer number,etc. store
> the values in an array, then connect to the LAN postgres database and
> post to the appropriate tables.
Bear in mind that the SL customer database also assigns numbers.
Having dived into the mess, there's some interesting dependencies
between the tables, but since you're doing it from the oe, it'd matter
less.
Still, since your ecommerce package is the one that has the master
list of customers and parts, it'd be like this:
(1) Find customer from postgres db. In this case, you might have to
alter the db so the customer table maps the customer.id with whatever
primary key your ecommerce package use. Or use a seperate table for
that.
a. if not found, INSERT INTO new customer, get customer.id
b. if found, get customer.id
(2) Insert new order into oe, with customer.id, get new oe.id
(3) For each line item on the order
(a) Find parts.id
- If not found, insert from master list on ecommerce
package, get parts.id
- If found, get the parts.id
(b) Insert all the lines on the invoice into invoice.id, setting
invoice.trans_id with oe.id, invoice.parts_id with parts.id, and all
the rest of the information.
I've found that all the relavent backend code are in the SL/
directory. It is very much possible to just do a
new $form = Form;
set the various variables with
$form->{qty_1} = 4;
(for example), and the run the appropriate module, in this case,
SL/OE.pm
Hey! Now there's an idea for a contrib module. A version for
commandline or XML format.
> We are most interested in a efficient means to process orders and track
> inventory than in accounting with the G/L. We have a firm which does
> our tax accounting. We don't necessarily need to maintain strict
> accounting rules in this application.
Thank you, someone preaching the gospel! :-)
Have you considered this? On this one client, I went and downloaded a
PHP program capable of generating a barcode image. Then I modified the
invoice template so that it would call that PHP code (thinking it is
an image) and then having it barcoded. So now, all the invoices go
with a barcode at the upper-left hand corner. Of course, that meant
having two different versions of invoice, one for print, and one for
email, but that's ok.
I have the relavent code, and it doesn't patch against the core code,
if you're interested in it. If you're going all-out paperless, then
this would be less useful. But who knows?
> There is an autoincrementing id field in table oe. I assume in creating
> a query to update that table, we would use a "" for the id field and the
> database would assign it the next available number. The order numbers
> are assigned by the web database.
> >
> > >
> > > Secondly, we have backordered items. How are the current users handling
> > > backorders? I understand they will be supported in a future version.
> >
> > You keep the sales order and revise it.
> Well yes, of course. But it would be helpful if we had a trigger to
> release backorders when stock arrives. We ship 150+ orders per day and
> have 2 full time staffers who enter orders into a networked Quickbooks.
> We max out the transaciton limit in quickbooks about every quarter.
> It's also exceedingly slow and cumbersome. As far as the backorders go,
> we could probably use the notes field until we can find a solution.
Ahh, gotta love it when small projects starts scaling up. You might
want to write a cronjob to do a vacuum -d <SL-db-name> nightly after
backing it up.
The kludge that I wrote for 1.6.3 (which is really lame, so don't ask
for it, the kludge I mean), I just added a BO field. It does no
automatic check within the inventory, nor does it kick out a new sales
order automatically with the backordered stuff in there. Though, come
to think of it, I think I'll bring it up with the client and see if
he'd like something like that.
-Qaexl-
http://www.next-horizons.com/qaexl/
-------------------------------------------------------
This sf.net email is sponsored by: OSDN - Tired of that same old
cell phone? Get a new here for FREE!
https://www.inphonic.com/r.asp?r=sourceforge1&refcode1=vs3390
-------------------------------------------------------
(un)subscribe: http://lists.sourceforge.net/lists/listinfo/sql-ledger-users
Archive: http://www.mail-archive.com/[email protected]/
