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:
<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>
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.
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.
<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.
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.
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.
