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.
