Monday, April 3, 2017

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.

No comments:

Post a Comment