Monday, April 3, 2017

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.

<%name%> \\
<%address1%> \\

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, 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 Add the yellow code line. This is to force that the scrollbars are off.
<META NAME="robots" CONTENT="noindex,nofollow" />
<META NAME="viewport" content="width=device-width, initial-scale=1.0, maximum-scale=1.0, user-scalable=no">
  • 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 and rename it to
  • In the directory bin/mozilla create a new file (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 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:{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, 
  FROM productionitem
        JOIN parts ON ( = 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};

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.

Sunday, April 2, 2017

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.