You have to install CLI version of PHP, and you can start. Cron can run any command line program, also php interpreter.

Janusz

We have used a variety of windows/unix/web accounting programs over the last 14 years and frontaccounting is by far the best we have used for our purposes. I keep thinking that gnue will go somewhere, but it been 14 years and counting since I started following that. Whatever we do, it has to work with frontaccounting.

Here is what we are trying to do...

We are running two business under 1 set of books. We have two DBA names because the businesses are so different and unrelated (Bookbinding and Wireless High Speed Internet).

Frontaccounting is set up under the Bookbinding business because that is the main one. The Wireless is a side effort but uses a different name because, lets face it, who would by Internet access from a old book guy :-)

Anyway, we have the bandwidth and threshold usage stored in an autogenerated rrd database. We also have a list of customers stored in a mysql database with what services they get billed for very month.

What I wanted was a script that would access the autobill information from mysql, then access the rrd database and determine if any additional bandwidth should be billed to the customer, generate an email invoice/statement combination and then insert the invoice information into frontaccounting as if I had manually inserted a direct invoice. This script would run just after midnight on the first of every month.

I wrote a first pass prototype of the script in bash (what an ugly kludge of a script!) It's 500 lines of database calls, and string manipulation. It was relatively easy to figure out what frontaccounting was doing by looking at the log, but I really would hate to do that again. If nothing else, the process was very informative of what frontaccounting was doing. (rather more complicated than some of the other accounting programs have been)

I wouldn't even bother posting the kludge. It's obviously not the way to go.

I've never really done much php scripting, but I guess now is as good a time as any. Can you run a php script from a cron job?

By using database triggers, SymmetricDS guarantees that data changes are captured and atomicity is preserved. Support for database vendors is provided through a Database Dialect layer, with implementations for MySQL, Oracle, SQL Server, PostgreSQL, DB2, Firebird, HSQLDB, H2, and Apache Derby included.

For a POS I think this is the way to go...

AM

Sorry it is taking me a while to get back to you on my thoughts.
We are working on it and we have 3 programmers hired full time that are working on our system with Frontaccounting at the center of things.

We are almost done with our POS system and when it is finished and ready to work with Front Accounting, I will let you know.

Thanks for your patience, I will be in touch as soon as I have something ready to show you.
My last comment was mainly just to point out that until there are custom fields and workflows in frontaccounting, that our POS integration is mainly happening through vtiger's custom workflows and custom fields interacting directly with the frontaccounting database.

Thanks,

Dave

Regarding custom fields/workflows you know my point of view, but seems that for some reason you are not really determined to turn this idea into reality as before .
Janusz

I agree that this is not the best way to integrate systems, but until front accounting has the ability to create custom fields and custom workflows in a similar fashion as vtiger, we don't see any other way of getting the type of integration we need.

It's a pity to say your code dealing directly with FA database content will not work with next FA major release (within month or so). This is _really_ not right way of integration unless you want to do the same work every couple of months on new releases,  or you prefer using current FA version forever.

Janusz

We would love it if you shared your Insert and Update queries with us when you are done so we don't have to repeat what you have already done.
We also need to go directly to the database and want to make sure we are inserting and updating everything correctly.

Thanks,
Dave

However, the program I have to integrate is not php. what I have to do is write a glue script that will move data from it (stored in an rrd database and a MySQL database) to the frontaccounting database and make it look like I created a direct invoice from within frontaccounting.

It turns out the easiest way to see what was going on was to log the MySQL calls that frontaccounting made during the generation of a direct invoice. By manipulating the log it's pretty clear what is happening. There are lots of SELECT queries (which I expected to find) and three distinct transactions. One for the order, one for the delivery and one for the invoice. You really only have to pay attention to the UPDATE and INSERT statements to clearly understand it.

Given this information, I think I can write that glue script to run under a cron job. I still have a bit more digging to do before I try though. Frontaccounting uses a quite a few numerical symbols (such as "type" for transaction, etc..) that I need to get a handle on.

Simply include this file and create an object of this class. And then fill this object with your invoice details and item lines. You can see ,in /sales/sales_order_entry.php, how to fill the items and invoice details and finally how to save the object with the write method.
This way you are using the internal core of FA to get everything updated, including (invoice, debtor trans, delivery, sales order, GL transactions etc.). So by studying this file, you will see how to do it.

This is much better than trying to explain how everything is connected in DB.
If you are curious about this, however, there is an ER-diagram and Table scheme for download on the download documentation page.

/Joe

Thanks,
Dave

Thanks

Alvin

Janusz

I want to integrate a separate program that will auto-generate invoices based on some dynamic information contained in another database, then insert the appropriate data in frontaccounting as if frontaccounting had done it itself.

I hav no problme with database manipulation, but cannot find documentation on what tables are changed in frontaccounting with a direct invoice.

Thanks