Install Sql-ledger on OSX

Here is a simple install for Mac users to install sql-ledger on Mac OS Mountain Lion. I will use Xcode to get all the core files needed to get it working. Osx already has perl, apache and postgres, we only need to get the extra files needed and Latex if you want to use pdf printing. 

Step 1

First we get all the files to run sql-ledger.

Go to the App Store and Install Xcode, it's free! I know it is a large download but it is the easiest way to get the tools we need.

When Xcode is installed, go to preferences of Xcode and install 'Command Line Tools'

(you can also download 'command line tools' direct from the Apple developers site without installing Xcode, but I follow the Xcode way...).  

Now we have the command line tools we can install the database drivers for Perl.

-Go to terminal and give command: 

sudo cpan DBD:Pg

Follow the prompt messages. (pressing Y a few times...)

To get pdf printing, Install MacTex, http://www.tug.org/mactex/morepackages.html

Choose this file to download and install it: BasicTex.pkg

That's it. Now we need to get sql-ledger on the Mac.

Step 2

Download latest version of sql-ledger, and place the files in directory:

/library/webserver/documents/sql-ledger 

Go to the Terminal app and make the files readable for the web server.

sudo chown -R www:www /library/webserver/documents/sql-ledger 

Now we have to tell apache where to find sql-ledger, to do this add the file sql-ledger.conf in /etc/apache2/users

The contents of this file is:

-------

Alias /sql-ledger/ /Library/WebServer/Documents/sql-ledger/

 <Directory /Library/WebServer/Documents/sql-ledger/>

AllowOverride All

AddHandler cgi-script .pl

Options ExecCGI Includes FollowSymlinks

Order Allow,Deny

Allow from All

</Directory>

<Directory /Library/WebServer/Documents/sql-ledger/users>

Order Deny,Allow

Deny from All

</Directory>

----------

To have apache read the new file, go to Terminal app and restart apache:

sudo apachectl restart

Last step is to alter a line in /Library/WebServer/Documents/sql-ledger/sql-ledger.conf. We want to tell sql-ledger where to find Latex on your Mac. That is located in /usr/texbin

Edit the file sql-ledger.conf in the sql-ledger directory, and edit the line so the line looks like:

# if the server can't find gzip, latex, dvips or pdflatex, add the path

$ENV{PATH} .= ":/usr/texbin";

Step 3

Start sql-ledger with: http://localhost/sql-ledger/admin.pl

Give an admin password and setup a dataset.

Default, sql-ledger uses the user sql-ledger for database access. Change this into postgres, if you have not setup any other users for postgres. The password for user postgres is the root password of your Mac. For host, type, localhost.

I suggest that if everything is working, Google into how to setup a sql-leder user for postgres. 

Your done! Login with user admin and the new password you created at: http://localhost/sql-ledger/login.pl

Under HR, you can setup the new users and their privileges. See install instructions at the sql-ledger website for more details.

Short Demo on YouTube

Short demo of how to use Pentaho Reporting for making reports on Sql-Ledger.

Installing The Pentaho Bi-server

When you have created several reports, it is time to share them with your team. For that we have to install the Pentaho Bi-server. Download Pentaho bi-server , unzip and place the postgres driver postgresql-9.1-901.jdbc3.jar in /biserver-ce/tomcat/lib. 
To tune the bi-server to work also with iPhone, read this and follow the instructions. 
To tune the bi-server, not to give the evaluation login link, see picture below, read this, and follow the instructions

Change biserver-ce/pentaho-solutions/system/publisher_config.xml and add pentaho as publisher password (or what you prefer..)

<publisher-config>

<publisher-password>pentaho</publisher-password>

</publisher-config>

To start the bi-server, on the Mac, first make the *.sh files in the directory biserver-ce-3 and tomcat/bin executable by doing a chmod +x to the files. Goto directory biserver-ce-3 and

sudo chmod +x *.sh

Same as in the directory biserver-ce-3/tomcat/bin Goto this directory and again

sudo chmod +x *.sh

And finally to start it, goto the directory biserver-ce-3:

sudo ./start-pentaho.sh 

To start the administration console go to the directory, administration-console and 

sudo chmod + x *.sh

then start it by 

sudo ./start-pac.sh

The bi-server is now setup for localhost on your Mac. You can find them now on localhost:8080 and the admin console on localhost:8099.


First go to localhost:8099 and login using credentials: admin password and make a user with admin rights.

If you want run it on your server, read this in Dutch (or this in a language you can read, (google translate) But first, keep on reading this blog, below, what are (better) setting....Below is the setup for Debian.

#Pentaho has it's own tomcat installation, but no Java Environment. 
#You have to install this separately. We used Sun version 1.6. 

#Debian:

aptitude install sun-java6-jdk sun-java6-jre
aptitude install sun-java6-plugin sun-java6-bin
aptitude install libcommons-beanutils-java libcommons-collections-java libcommons-collections3-java libcommons-daemon-java
aptitude install libcommons-io-java libcommons-launcher-java libcommons-logging-java libcommons-modeler-java
aptitude install libcommons-pool-java libcommons-validator-java

# Perl also needs a specific CPAN module(s):

perl -MCPAN -e "install Text::Iconv"

# On the postgresql server, add a line in the file /etc/postgresql/*/main/pg_hba.conf 
# to give access to the server where pentaho is running so that pentaho has access to the
# Postgres database it has to use.

host    <pg_username>            <pg_database>         <IP-pentaho-server>/32    md5

# In the weblink they install the software, in the home dir of root. 
# This is a NO! NO!
# Choose a subdir under /var/www/ (standard Apache dir) or a subdir under /home/. 
# In this example /home/pentaho/.

# A comment for the user where pentaho will be running under. 
# In the weblink they use root, but this is also a NO! NO!
# Create a separate user for pentaho:

useradd -u 10000 -d /home/pentaho -m pentaho

# Install pentaho now according to the weblink. 

chown -R pentaho.pentaho /home/pentaho

# Finally, the startup we do, is also different from the weblink:

vi /etc/rc.local
 su - pentaho -c "/home/pentaho/startup.sh start" >/dev/null 2>&1

vi /home/pentaho/startup.sh
#!/bin/bash
#
# Only run as user pentaho

[ $(id -u) -ne 10000 ] && exit 1

# Set the paths according to your distro.

export JAVA_HOME="/usr/lib/jvm/java-6-sun"
export JRE_HOME="/usr/lib/jvm/java-6-sun/jre"
export PATH=$PATH:$JAVA_HOME/bin

case $1 in
       start)
               /home/pentaho/biserver-ce/start-pentaho.sh &
               cd /home/pentaho/administration-console
               /home/pentaho/administration-console/start-pac.sh &
               ;;
       stop)
               /home/pentaho/biserver-ce/stop-pentaho.sh &
               cd /home/pentaho/administration-console
               /home/pentaho/administration-console/stop-pac.sh &
               ;;
       restart)
               $0 stop
               $0 start
               ;;
       *)
               echo "usage: $0 <start|stop|restart>"
               ;;
esac

Tutorial Part III

Most difficult thing, for making the reports in Pentaho Designer, is making the queries to the database.

The good thing is that every user of Sql-ledger has the same database layout, so a good report can be used by everyone! 

To make life a bit easier, here is a trick, so that you can see what the queries are when you use Sql-ledger.

See this blog how to log all the queries from postgres.

Be aware that Sql-ledger is a web application, so it has to build the screens in parts. In the log you will see several queries fired to build one screen.

If you scout your postgres.log you will find the queries

Example Q4 2011 query from sql-ledger to see what we bought:

SELECT a.id, a.invnumber, a.ordnumber, a.transdate,

                a.duedate, (SELECT sum(ac.amount)

 FROM acc_trans ac

 JOIN chart c ON (c.id = ac.chart_id)

 WHERE ac.trans_id = a.id

 AND ac.approved = '1'

 AND c.link LIKE '%_tax%'

              AND ac.transdate <= '31-12-2011') * -1 AS tax,

a.amount, (a.paid) AS paid,

a.invoice, a.datepaid, a.terms, a.notes,

a.shipvia, a.waybill, a.shippingpoint,

e.name AS employee, vc.name, vc.vendornumber,

a.vendor_id, a.till, m.name AS manager, a.curr,

a.exchangerate, d.description AS department,

a.ponumber, a.warehouse_id, w.description AS warehouse,

a.description, a.dcn, pm.description AS paymentmethod,

a.datepaid - a.duedate AS paymentdiff,

ad.address1, ad.address2, ad.city, ad.zipcode, ad.country

 

        FROM ap a

     JOIN vendor vc ON (a.vendor_id = vc.id)

     JOIN address ad ON (ad.trans_id = vc.id)

     LEFT JOIN employee e ON (a.employee_id = e.id)

     LEFT JOIN employee m ON (e.managerid = m.id)

     LEFT JOIN department d ON (a.department_id = d.id)

     LEFT JOIN warehouse w ON (a.warehouse_id = w.id)

     LEFT JOIN paymentmethod pm ON (pm.id = a.paymentmethod_id)

     

     

            WHERE a.approved = '1' AND a.transdate >= '01-10-2011' AND a.transdate <= '31-12-2011'

            ORDER by 4 ASC,2,17

Tutorial Part II

After installing Pentaho Report Designer and creating a sample database we will make our first report.

Fire up the Report Designer and close the welcome screen.

Then choose File - Report Wizard. Select a nice theme and click next.



















Now we have to define the database connection. Select the Sample Query and click on the pencil icon.

Of the left pane, again click the pencil icon.

Define the connection as shown here below. The password for postgres is the admin password of your mac.

After your filled in and selected the connection type, PostgreSQL, test the connection by pressing test.

You will get a dialog stating that the connection was successful. Press OK to close the dialog (if the test was ok...) Now we can create a query. 

On the right pane, Available Queries, press the plus sign. 

It makes a Query 1, you can rename it to a more smarter name.

In the Query Dialog (with the yellow line) insert the Postgres Query below. (copy paste) This query shows the trial balance for Q1 2011. After you have pasted it, click preview to see the query result.

SELECT accno, 

 description,

 (SELECT SUM(amount) FROM acc_trans ac 

 WHERE ac.chart_id = chart.id 

 AND TO_CHAR(transdate, 'YY-MM') = '11-01') AS jan,

 (SELECT SUM(amount) FROM acc_trans ac 

 WHERE ac.chart_id = chart.id 

 AND TO_CHAR(transdate, 'YY-MM') = '11-02') AS feb,

 (SELECT SUM(amount) FROM acc_trans ac 

 WHERE ac.chart_id = chart.id 

 AND TO_CHAR(transdate, 'YY-MM') = '11-03') AS mar

FROM chart

WHERE charttype = 'A'

ORDER BY accno

Click Ok and select the Query 1 to be used.



















In the next screen put the available items in the box below and sort them accordingly




















Press Next and set width accno 7%, description 30% and change the data format for jan, feb and mar to #,###;(#,###) 

Your are done! Finish will give the trial report, you can always press preview to see the result in the wizard.

Tutorial Part 1

For all of you who are working with sql-ledger, this post is about how to integrate Pentaho reporting with sql-ledger.
I have been working with ledger for several years now and I was looking for a reporting tool, so that I can make live reports on the ledger system for management. There are several ways to Rome for reporting and I just bought a book about Pentaho, so that is why.

I assume that you have a little knowledge about sql-ledger and you have some knowledge about databases and setting up software and a not scared in using terminal prompt.

Because I have a Mac, the whole tutorial is who to make it work on a Mac.

Just to see who it works let's start with downloading the report tool.

• Download Pentaho Report Designer, I use 3.8.2 GA for mac, prd-ce-mac-3.8.2-stable.tar.gz
• When downloaded drag the whole thing to the applications folder.
• For Pentaho to work with Sql-Ledger, we have to add a jdbc driver, postgres.
• Download the driver from postgresql-9.1-901.jdbc3.jar
• Open Finder and right click the Pentaho application and select show package contents.
• Drop the driver in the Pentaho Report Designer directory in /jdbc/lib

Before we can do some reporting we have to get a database to play with. Because all mac osx are shipped with Postgres we can do some testing locally on the mac!
Go to your sql-ledger environment and select System -> Backup - > Save to File
When you got the backup file name it to something short, and drop it in /library/postgresql/8.4/bin directory. If you are using OSX Snow leopard it is: /library/postgresql8/bin

Now the terminal part. Open terminal and enter the following:
cd /
cd /library/postgresql/8.4/bin (or where you bin directory is)

Now we are going to make a database in Postgres and restore the backup
sudo ./createdb -U postgres yourdatabasename
./psql -U postgres yourdatabasename < yourbackupname.sql

Whenever you want to remove the database, type:
sudo ./dropdb -U postgres yourdatabasename

OK, now we can make some nice reports. And, yes, they are still local on you machine, but as soon as we made a few reports we will instal the bi-server to share them. Even on your IPhone!