Charting in Sql-Ledger

I was looking for charting software to use with sql-ledger.

I found Chart.js, and it is very easy to use and flexible in the sense that you do not have to download a lot of libraries. There is also a lot info available on youtube, see this very good tutorial!

Sql-ledger uses Perl and a Postgress database to store the data. In the perl routines you figure out what is happens in the program and the I used the income statement (rp.pl) as the script to get the Data for my Financial Dashboard.

See this short video what the Dashboard is all about.

You only need the perl chart.pl script to make it work. No need to get additional Java libraries , these are loaded in the script.

Assuming you have SQL-Ledger version 3.0 or higher and a directory structure
like: /sql-ledger/bin/mozilla/custom/

• Step 1 copy am.pl in /sql-ledger/ to chart.pl in /sql-ledger/
• Step 2 download chart.pl and place it in /sql-ledger/bin/mozilla
• Step 3 download menu.ini and place it in /sql-ledger/bin/mozilla/custom
• Step 4 run perl locales in your local language directory

LaTeX Challenges

If you familiar with sql-ledger, everybody knows that after you installed it, got the accounting scheme in place, made you opening balance etc, etc, you want to make your first invoice or packaging slip.

Yep, then you are stuck with a language called Latex.

I needed a lot of information on the invoice, this because in retailing world they want to see everything. Apart from the usual stuff like name, adres, etc I needed:

-EAN code

-Date delivered

-Shelf life cookie 

-How many pallets

-Type of package

-Price and discount

-Total weight in kg of the order

I have shared the invoice layout here. If you are stuck with a variable, hopefully you find it in the code.

In the Netherlands they use a standard cargo slip, which is pre-printed, the trick is to get the layout right that it fits in all the blocks.

In Latex you can use text block to handle this.
First declare where it origin is:
\textblockorigin{10mm}{10mm} % start everything near the top-left corner

Then enter a block with exact coordinates.

\begin{textblock}{2}(0.5,1.8)
<%name%> \\
<%address1%> \\
<%zipcode%>
<%city%>
\end{textblock}

Above example is 2 modules wide and is placed (0.5,1.8) from the origin.

See this link for more information.

Barcode Scanner Part II

So, you my ask: how does it all work in the factory? Let me explain!

I use numbers for barcodes, so no letters etc. Reason for this is that I setup ranges in the barcode.
Fixed barcodes (one time setup):

• Barcode numbers in the range 1-99 for the Factory pastry carts.
• Barcode numbers in the range 101-950 for the mousetraps and cleaning tables

Free barcodes (new incremental value for new item in factory):

• Barcode numbers above 1000 are for goods that go through the Factory such as flower, butter etc.

When flower comes in the factory a new barcode is printed and scanned. In the first scan you can give the new barcode a general part name (flower) from the populated list. Also the date of entering the factory is added.
When the item is scanned again, the item is known in the system (invokes different menu) and you can assign the item to a pastry cart. When you scan a pastry cart you can assign that the cart goes into production. The barcode of the cart will get a entry in the production table with production time, the amount of degrees that the dough is, and the production sequence code. The production sequence code is also printed on the cookie package. From the production table I can identify how the cookies were made from which pastry card, from what ingredients and when a specific ingredient came into the factory.

To make it even more quality driven, all items that make the cookies are sampled. So to take the flower example, when new flower comes in the factory, it is sampled, the sample will get a new barcode, the same new barcode is issued at the production line where it is scanned when the new flower is added to the pastry cart. And last the same barcode is sticked to the packing slip that you get from the flower supplier. So all in all the factory prints three barcodes per item.

Scanning a mousetrap or a cleaning tabel will give you the option to select if the trap/cleaning was ok, mouse, empty or other. And submits a inspection date.

Because all the screens are different, a lot of code clutter goes into formatting the screens. So when a barcode is scanned, the program first looks at the value and then calls different subs to get the right item and handle the action when a button is pressed.

 

To see the results of the scans, barcode.pl also has a few routines to display the entries. These are the report subs. You can select them in the menu of Sql-ledger, invoked by the custom menu. For more extended reporting I use Pentaho. See the other old blogposts.

Barcode Scanner Part I

To trace items in the Factory, I wanted to use barcodes. Since everybody has a mobile phone I began to search for a good app that can scan a barcode and has a function that you can re-route the barcode to a webpage. Mobiscan did the job. This app has the option to trigger a custom website where it adds the barcode value it scans. Also it triggers the browser from within the app. Saves a few presses on your phone.

To make it work on Sql-ledger side I used the following steps:

• In the directory SL edit file Form.pm. Add the yellow code line. This is to force that the scrollbars are off.

<head>
 <title>$self->{titlebar}</title>
<META NAME="robots" CONTENT="noindex,nofollow" />
<META NAME="viewport" content="width=device-width, initial-scale=1.0, maximum-scale=1.0, user-scalable=no">
$favicon
$stylesheet
$charset
 </head>

• In the directory CSS, add an extra css file, sql-ledger-mobile.css make a color scheme that is different from the normal setup.

• In the root directory, make a copy of gl.pl and rename it to barcode.pl

• In the directory bin/mozilla create a new file barcode.pl (this will contain the perl code).

• In de root directory add the file custom_menu.ini

• Create a new user in sql-ledger with minimal permission on the system.

Now we are ready the begin to make some code in the barcode.pl file in bin/mozilla! The first part of the code states all the routines that are used. You can find the all the files here for download. I know that the code is horrible, but as I said before, I am not a programmer... google was my friend to find routines in Perl. The whole trick is to catch the barcode scan push it to Perl. The link you have to make in your iPhone app is:

http://www.yoursql-ledger.com/barcode.pl?path=bin/mozilla&action=catch&level=Barcode--Setup--Catch&login=Username&password=Password&js=1&catch={barcode}

Replace Username and Password with the new user you made. The link will call the sub routine below. The iPhone app will replace {barcode} with the barcode value you scan.

sub catch {
    ## trim the barcode to avoid errors
       $catching1 = $form->{catch};    
       $catching1 =~ s/^\s+//;
       $catching1 =~ s/\s+$//;
    ## Make sure the barcode is a number 
       $catching = $catching1 * 1;    
    ## perform a query to postgres

   my $dbh = $form->dbconnect(\%myconfig);
   my $query = qq|SELECT productionitem.id, 
  productionitem.barcode, 
  productionitem.date_in,
  productionitem.parts_id
  FROM productionitem
        JOIN parts ON (parts.id = productionitem.parts_id)
        WHERE productionitem.barcode = '$catching'|;
   my $sth = $dbh->prepare($query);
   $sth->execute || $form->dberror($query);
   $ref = $sth->fetchrow_hashref(NAME_lc);
   foreach $key (keys %$ref) {
      $form->{$key} = $ref->{$key};
   }
   $sth->finish;
   $dbh->disconnect;

From the code example above you can see that it queries a table that default does not exists in Sql-ledger. I added two new tables; todo this edit the file: Pg-custom_tables.sql in the sql directory.

In my case:

CREATE TABLE productionitem (
 id               int default nextval('id'),
 barcode          char(13),
 date_in          date,
 parts_id  int
);

CREATE TABLE production (
 id    int default nextval('id'),
 productionitem_id  int,
 production_date         timestamp,
 production_code         text,
 mouse_id  text,
 deeg_date  timestamp,
 deeg_id           text,
 graden   numeric
);

To get the tables in your database, run the command:
psql –U postgres yourdatabase < Pg-custom_tables.sql 

All barcodes are stored in the production item table and the actual parts name comes from the Sql-ledger parts table. I have added 'general' parts in Sql-ledger,  for example Flower, Sugar, Butter, Mouse, Cart1, Cart2, etc. From the queries in the code you can see that these 'general' parts all begin with the letter x and a space . (ie. x Flower) In that way they are all at the bottom of your parts list and not used for making invoices. To match the barcode scan it and select from the general parts-list (populated with all the parts starting with x and a space) what it is.

How to run a Cookie Factory with Sql-Ledger

For five years, I was the proud owner of a Cookie Factory. When I became the owner, the factory was in serious trouble. I restarted the factory, and after five profitable years the old owner bought it back. That was always the intention, get the factory in order, build a stable pipeline with customers, and in time the old owner would buy it back. I should write a book about it, because it was a very interesting succesfull journey!

During that journey, I needed a system to implement a ledger for the company. For quality control I needed an application to track and trace the items to make the cookies. And last I needed an application to trace cleaning and inspect the mouse traps....

I managed to use sql-ledger for all my demands and although I am not really a programmer, I got it working in no time.

Have a look at the factory, where they make the best cookies, allergy free!

In this blog I will share and explain how you can use sql-ledger to track and trace items. Also give a few tips.

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.