Published Jul 27. 2016 - 5 hours ago

The technology behind LeaderCalc

This is the story about the adaptation of the old LeaderCalc spreadsheet to a Drupal module that drives a web based system

This is NOT about fly fishing or fly tying, but about site development and nerdy stuff

A very short intro to what LeaderCalc does for the non-flyfisher. Leaders are tapered lines that form the transition from a thick and heavy fly line to the thin tippet and the fly/hook. Some leaders are a simple tapered piece of nylon while others consist of a series of nylon lines of varying lengths and thicknesses tied together with knots.
The way this taper is made is critical to the function of the leader and there are lots of formulas describing the construction. These formulas take length and other overall dimensions as parameters and then describe the specifics for each piece of line needed to tie together to make a whole leader.

Leadre presentation
Martin Joergensen

A spreadsheet

LeaderCalc helps do these calculations. It was originally an Excel spreadsheet consisting of a series of sheets containing data, formulas and automation. As spreadsheets come, it was very complex, but all the complexity was hidden and locked for the end user, who just selected the criteria for a leader and let the spreadsheet do its magic.
But the spreadsheet requires a piece of software like Microsoft Excel or Open Office (or one of the derivatives) and a file download to work, which is cumbersome and somewhat altmodisch. It was popular enough, but still a bother to use compared to more modern web based calculators. It's still there if you want to try it.
I wanted to transform it into an online based tool.

JS or jQuery or...?

One option was to make a JavaScript based system with a structure function like the spreadsheet: a few large arrays with all formula data, dynamically calculating the numbers using a bunch of JavaScript or jQuery code. I disliked this solution for two reasons: its immense complexity and the fact that all data and formulas had to be included with the page. That would mean a large page and all data and formulas freely available to users.
I opted for a classic web based solution: a simple form that leads to a page with the results. All algorithms would be in PHP and all data in the database – and everything server side.

Dissecting

So the first job was to dissect the spreadsheet and reverse engineer the functions. I had the unlocked spreadsheet, which contained leader names each with a machine name and a set of data describing the length and line weight and thickness ranges. The core in the spreadsheet was a table with the length percentage and thickness of each piece of line in a given leader.
The user selected a set of criteria: fly rod line class and desired length and tippet, and the system would consult the percentage table and deliver back the actual measures of each piece, actually doing so by mirroring the percentages onto the front page in a very large table.

Transferring

First I needed the data transferred to database tables.
Since they were in a spreadsheet and well organized, I opted for simple CSV files. I copied the data from the original spreadsheet, pasted into another, cleaned it up and saved the data as a comma separated file, ready to import into the prepared tables in Drupal's database using PHPMyAdmin.
The percent data was a huge matrix in the spreadsheet, and I decided to compress that into smaller arrays of data and save them with each their formula rather than having a separate table for these data.
The result was a simple set of tables:
The base table lc_leader, with the human readable name, machine name, description, the length range and a reference to a category.
The percentage table lc_percent contains a number of formulas for each leader, referring to the leader table using the machine name. It has columns for the fly line weight and line thickness.
The categories are in a separate table, lc_category and the possible line thicknesses are in a table called lc_thickess, needed because I needed both labels, inches and millimeters as floats as well as millimeters in the standardized thicknesses available in the market.

The system can then select from these tables based on the different criteria: category, leader name, fly line weight, length and desired line thickness.

A module

Next step was to make a Drupal module to handle all the logic and Drupal integration.
You know the drill:
gff_leadercalc.info – information about the module
gff_leadercalc.module – hook_permission, hook_menu, forms, submit handlers and all the calculation and presentation logic.
gff_leadercalc.js – some JavaScript code for some snazzy functions I wanted.
gff_leadercalc.css – styling the results specific for this module. Quite a bit of it, it would show.

Since I was working from scratch, I could expand the original form a bit, and on top of having length, tippet and line, I also added category as an optional parameter.

Selecting

When a user submits the form, the system finds all leaders that meet the selected criteria and shows them.
The leader display function is part of a PHP class called gff_leadercalc_leader whose constructor takes a leader formula machine name a length and a tippet and then loads the appropriate data into the object. The object has one method gff_leadercalc_leader->row(), which will create the representation of that leader object in the form of a table with data and a schematic drawing of the leader. The drawing is simply made with div's colored and dimensioned with CSS.
Since many leader formulas can be made into leaders of several lengths and tippet thicknesses, the basic selection finds all formula combinations that meet the user's criteria and then loops through the variables: formula, length and tippet.
For each the system renders a table with data and a drawing plus some links using the gff_leadercalc_leader->row() function.
Different criteria results in vastly different numbers of leader formulas. Some return just a couple, some return several hundred. Currently the system can generate more than 2,700 different leader formulas.

Other functions

On top of the basic formula with the criteria, I needed the option to switch between inches and millimeters since a large part of the users are from the US and tend to be metric-shy. This is done in a small dropdown with autosubmit, and on submission this will save the user's choice in a cookie, which is the used to select the units until it's changed.
The user can also save selected leaders in a cookie or – if logged in – in a small table called lc_saved. Either will be read when revisiting the formula and can be shown on a page called My Leaders.
Saving leaders and removing them from the My Leaders list is done using AJAX and jQuery, so that no page updates or form submissions are necessary. The relevant elements simply change on the page – number of leaders and save/remove links – to reflect the user's actions, and for logged in users the data is also saved in the database using AJAX.

Labels ready to print
Martin Joergensen
Admin interface and label form
Martin Joergensen

Printing labels

The old spreadsheet had a label function, which would allow the user to generated inlay sheets or labes for printing. These inlays could then be put in small plastic bags together with the finished leader.
The label function is now a small separate include file – gff_leadercalc.labels.inc - used by the main module. This file contains the formula and submit handling of the label system, which will generate the labels as individual tables with the user's text and data from the system in each cell. The form makes use of Drupal's AJAX form handling, and generally updates everything dynamically when the entered data changes.
Since the labels need to fit into a bag, I'm using physical measurements to set the size of each table/label. Using inline CSS makes it possible to dynamically specify the physical size of HTML elements, and if the browser-system-printer chain is set up properly, this works all the way to the physical world, and labels should be printed in the user selectable physical size.
I simply float each label left on the printed page, and again the browser-printer chain will ensure that the labels are arranged in rows that fill the paper optimally.

Administration

The first leader data was inherent in the system and database from the spreadsheet data import, but in order to be able to add more leaders and edit the existing, I needed a native administration interface in order to avoid having to edit data directly in the database – which was possible, but quite a bit of work and coordination.
So I added an admin interface too, allowing me to edit each set of leader data in a more structured manner.
Another inc file – this time gff_leadercalc.admin.inc – contains the forms and submission handlers for the editing, and the main module handles permissions and menu routing.
This interface allows an editor to edit or add leader formulas through the web interface, and makes maintaining the data much easier.

Not mobile

The old articles and files were kept for those wanting to use the spreadsheet, but all LeaderCalc references now point to the new system, which has essentially taken over most of the quite large traffic that used to go to the old system.
Even though the site has quite a lot of mobile traffic, I have not made the fairly complex LeaderCalc forms or the presentation of the leaders mobile friendly. People will most likely use computers or tablets with sufficiently large screens for this task, which is generally performed at home or in a workshop.

Add new comment

Log in or register to pre-fill name on comments, add videos, user pictures and more.
Read more about why you should register.