Printing QR codes out of database

Getting your data onto paper - or the web - Discussing the reports features of Base

Printing QR codes out of database

Postby saarenmaa » Thu Dec 17, 2020 4:40 pm

I need to print a very large number of small size QR codes out of database. There exists an OO Extension "QR Code Generator JS". It works in Calc and Writer. Any suggestions how to do this in Base? I think it is best to use Base since there are Reports and page layout can be controlled.

Thanks for any advise, Hannu
OpenOffice 4.1.8 on Windows 10
saarenmaa
 
Posts: 2
Joined: Thu Dec 17, 2020 4:22 pm

Re: Printing QR codes out of database

Postby Villeroy » Thu Dec 17, 2020 6:30 pm

The extension converts text to QR codes. I would think that it could be possible to convert text that is stored in a database into QR codes during report generation. Are you a programmer?
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
 
Posts: 30079
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Printing QR codes out of database

Postby saarenmaa » Thu Dec 17, 2020 7:51 pm

I was a programmer long time ago. :cry: Not up to date with JavaScript.
OpenOffice 4.1.8 on Windows 10
saarenmaa
 
Posts: 2
Joined: Thu Dec 17, 2020 4:22 pm

Re: Printing QR codes out of database

Postby Villeroy » Thu Dec 17, 2020 8:55 pm

The extension author uses a JS library with a little bit of wrapper code in StarBasic.
Function QrCode(sText, sPath, sColor, nSize) is the routine which takes 4 arguments and prepares the appropriate call to the JavaScript code.

A simple solution would read the database field in question, call that routine and store the resulting pictures permanently in a dedicated directory. The original Basic code deletes the picture after it has been embedded in your document.
The directory of pictures can be reported either with the LibreOffice suite or with OpenOffice plus report builder extension. OpenOffice's 20 year old report generator can not have any pictures.

Having a table with a unique identifier and some URL to be QRed:
Code: Select all   Expand viewCollapse view
ID   URL
123   https://foo.com/

it would be possible to write all the pictures as 123.svg and fetch them with a relatively simple SQL statement:
Code: Select all   Expand viewCollapse view
SELECT *, 'file:///path/to/pics/' || "ID" || '.svg' AS "QR" FROM "My Table"

and build a report based on this statement with a picture control linked to the concatenated "QR" field.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
 
Posts: 30079
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Printing QR codes out of database

Postby Villeroy » Sun Dec 20, 2020 1:12 am

2021-02-09: Fixed some irritating bug in the form design, added button to save record
You still have that QR/JavaScript extension installed.
If you are still using OpenOffice, install the report builder extension. I'd prefer https://libreoffice.org/ where this thing is built-in.
menu:Tools>Options... Security, [Macro Security...], set highest level and add a trusted directory for documents with embedded macro code. Do NOT use your Downloads folder. Your Documents folder is a better place.
Save the attached document to that directory or any subdirectory therein.
Create a subdirectory "qr" in the directory where you saved the document.
Open the document and the form therein.
Push the big button. It triggers an embedded macro which itself calls the extension macro which itself calls the JavaScript that produces QR codes.
Navigate the form records and try the codes with your smart phone.
Open the report.
A report is something printable, a form is for interactive data input, filtering, sorting, viewing.
--------------------------------------------------------------------------------------------------------------------------
How it works:
The form, the report and the macro rely on a query named "QR". The query must have a text column "URL" to generate the pictures from and a column "FN" of unique file names.
Query "QR" in the attached document goes like this:
Code: Select all   Expand viewCollapse view
SELECT "TXT" AS "URL",
"PK" AS "ID",
'qr/' || RIGHT( '000000' || "PK", 7 ) || '.svg' AS "FN"
FROM "Table1"

I have a table "Table1" from where I select the column "TXT" renamed to "URL", a unique primary key "PK" renamed to "ID" and a concatenation of subdirectory 'qr/' with an expression that creates a 7-digit number from the unique primary key plus the '.svg' file name suffix. The concatenation of the unique file names in subdirectory "qr" is named "FN".
The order of columns does not matter, the order of rows does not matter. All that matters is that some query "QR" has a column "URL" and a column "FN".
The macro loops through all records of that query and writes the QR code for "URL" into the file specified by "FN" by simply calling the code you already have installed.
The image controls in both elements, the form and the report, are linked to the "URL" column of the "QR" query which point to a relative file URL. The database document has no images stored.
P.S. any filtering applied to the form will affect the count of generated QR codes. Only visible form records get a new QR.
P.P.S. menu:Tools>Macros>Organize>Basic... tab "Libraries" allows to import the QR library from my document into yours if both documents are loaded. Create your own "QR" query in your database and that's it.
Attachments
QR.odb
Write QR codes.
(35.42 KiB) Downloaded 120 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
 
Posts: 30079
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Reporting

Who is online

Users browsing this forum: No registered users and 0 guests