A Developer’s Day – Wrestling with Filters

0 comments on this article Posted by Graham in A Developer's Day on April 22nd, 2012 , tagged with .

I Need to Make Some Space in my Head – What can I Throw Out?

This is the second article in an informal series of shock exposés in which I lift the lid on the white-knuckle ride which constitutes the daily rough and tumble of the E-Quip gerontocracy. Over the years I have written a lot of code, a fair proportion of which seems to work, and somehow or other I seem to remain in control of all the 0′s and 1′s floating around in my head. There are, however, two exceptions – bits of code which, while not exceptionally complex,  I seem to have to re-learn every time I look at them.

The first example is Transformation Matrices, which are central to our 3D graphics and CAD applications. In case you don’t know, if you represent a point in space as a 4-element column vector (called homogeneous coordinates), there are several matrices that you can multiply this vector by to do things like rotation, scaling, transformation etc. This is how CAD systems manipulate graphical objects. It’s not rocket science and doesn’t really involve anything more complex than matrix multiplication. Twenty or more years ago I wrote a graphics library to handle all this good stuff and, for the most part, it just works. It doesn’t need changing very often: once to add support for Windows (that will give you an idea how long this library has been around), again to add support for 32-bit processors (did I mention that this code has been around for a while?), once to convert it from C to C++, once to add support for 3D, and again to convert it to C#. The trouble is, every time I look at this code, it’s like it’s the first time I’ve ever seen it! I don’t just have to “brush up” on my theory, I have to dive back into Foley & Van Dam or reach for my old university lecture notes (yes, I do have all of my old lecture notes). These algorithms (or, perhaps more accurately, how I turned them into code over 20 years ago) just won’t stay in my head.

The second example is expression evaluation, which lies at the heart of filtering in all of our products. There is absolutely nothing difficult about parsing an expression from a given grammar; it’s a pretty common 1st-year undergraduate assignment which comes somewhere between the matriculation dinner and finding your coat-hook and locker.

A grammar defines a set of rules which are used to build an expression tree. Grammars are often described using BNF ( Backus – Naur Form), and they specify things like how factors are combined to form terms, and how terms combine to form expressions. A very simple example is shown below. Of course, being software developers we have to be different, and in the interests of bio-diversity, our trees have their roots at the top and their leaves at the bottom. I have yet to work out where worms and/or birds appear in these trees, yet alone which way up the nests need to go.

Each node of this tree has three key features:

1. An operator (+, – etc)

2. Two operands, Op1 & Op2

A really simple expression, like “1 + 2″ would be represented by a single node tree where the operator is +, Op1 is 1 and Op2 is 2. In more complicated trees like the example above, both Op1 & Op2 can be other tree nodes.

Filter Expressions

A filter is also an expression, albeit a Boolean rather than an arithmetic one, and so they are handled in a very similar way. The tree is slightly different to a simple arithmetic expression because the operands are predicates, i.e. statements which can evaluate to true or false. The tree below is taken from the ICL Reference Documentation (ICL is a set of class libraries used for all Integra products): it is the parse tree for the filter: A = B OR ( A = 5 AND B = 3)

Note that brackets can be used to change the way that the tree is built, but do not actually appear in the tree itself. Turning this parse tree into SQL (that’s the language used to get data out of the database) is simply a case of doing a recursive, left-right tree walk of our upside-down tree, starting from the root (i.e. the bit nearest the sky) translating each node into SQL syntax as we go.

On the off-chance that you’re interested, compilers translate entire software programs into parse trees in exactly the same way as this, the only difference is that the grammar is more complex, with each programming language having its own grammar. Compilers often have a front end which builds the parse tree, and then a back end which does the tree-walk to turn the tree into assembler instructions.

Now, every time I have to make any changes to the code which implements this, I run straight into the old mental block and have to go right back to basics. Fortunately this isn’t code that changes very often, but, like transformation matrices, there just doesn’t seem to be any room for this in my head on a long-term basis.

What’s the Problem?

The thing that makes this complicated isn’t creating the parse tree itself; that really is pretty simple stuff. There are three things about filters that make them complicated-ish:

1. They use public names for data, rather than the names that are actually in the database. The predicate [Status = 'Active'] must be translated into “EquipmentStatusShortName = ‘Active’“. This means that when we do the left-right tree walk (keep up!) to translate the filter into SQL, each public name has to be looked up in the database to find its equivalent database name.

2. They are defined in client-side code and have to be sent to the server. E-Quip uses a type of formatted text called XML to communicate between the client and the server, and unfortunately there are characters (<, > and ‘ are the most common) that have special meaning in XML but which are commonly used in filters. An expression like [Unit Price > 5000] can’t be sent directly to the server because XML is using the > character for its own purposes.

3. The filter processing code itself has many reserved words and characters which users also might want to include in filters. For example, the filter [Status = 'Scrapped - Not in use'] actually includes the words “Not In” which have special meaning to the filter mechanism. We also use the [ ] characters to delimit predicates which makes building the parse tree much easier, but which cause problems if users actually want to include those characters in filter text. To make things even trickier, [ & ] are actually SQL wildcards: “Model LIKE 3[1-5]00” means all models where the name has a 3 followed by any number between 1 and 5, followed by 00, i.e 3100, 3200, 3300 etc.

Problems with BETWEEN

In fact, it is exactly this last point that has prompted me to write this article. A filter expression is a series of terms separated by OR’s, each of which can be a series of factors separated by AND’s, just like an arithmetic expression is a series of terms separated by + and -. You might not have noticed, but in version 2.0 support for the BETWEEN operation was added to E-Quip filters. Now, most predicates are made up of three bits: a field name to filter on (e.g. Status), an operator (e.g. =) and a comparison value (e.g. Active). Put these three together and you get the filter [Status = 'Active'].

BETWEEN is unusual in that it doesn’t have three components, it has four, and the last two are separated by the word AND, e.g. [Purchase Date BETWEEN 1-JAN-2010 AND 31-DEC-2010]. This is particularly inconvenient, not only because of the extra operand, but we have already seen how the word AND is used to separate the factors in a filter expression. Although this doesn’t cause any problems when evaluating the filter, it does confuse the Filter Wizard when you open a saved Query Builder filter which contains BETWEEN. This relatively simple type of filter is always made up of a number predicates ANDed together, and the Filter Wizard uses the word “AND” to split a filter expression up into its constituent elements. It then writes each factor to a separate line in the Query Builder. This extra AND convinces the Query Builder that  the expression has two factors:

1. Purchase Date BETWEEN 1-JAN-2010

2. 31-DEC-2010

Neither of these terms is valid according to our grammar, and if you applied this filter you would get an error. Note that the problem only appeared in the Query Builder and not in any of the other Filter Wizard options, because only the Query Builder attempts to split an expression into individual terms.

The solution to the problem is pretty straightforward: because we are in charge of the translation we can use whatever words we like. So, instead of using BETWEEN … AND, I just changed the code to use FROM … TO. Hey presto – the extra AND has disappeared and sanity has been restored. We do a very similar thing for the LIKE predicate. Strictly speaking the opposite of LIKE is NOT LIKE but that extra word is problematic and makes the creation of the parse tree a tad more complex, so we use the word UNLIKE instead (try it – create a filter using the DOES NOT CONTAIN comparison and look at the filter that is produced).

So, it took me 5 minutes to come up with a solution to the problem, and then the rest of the day to dig through the documentation (yes, we do have documentation) to remind myself how this stuff all hangs together and to check to see if my simple fix would introduce other problems.

So that’s it for filters – until the next time. By the time I have clicked on Publish to put this article on the blog, everything that I have just re-taught myself about about filter expressions will have been flushed from my brain yet again. Perhaps I should be glad that there are only two things that seem not to fit in there!

 

 

 

 

 

 

 

Spare Part Locations – When is a Bin not a Bin?

0 comments on this article Posted by Graham in Spare Parts on April 21st, 2012 , tagged with .

What is a Spare Part Location

A spare part bin is a repository for spare parts. You might think that all  asset-management databases support the concept of  spare part bins, but surprisingly they don’t, and those that do all do it in different ways. Many of you will remember the old SEMS system developed by IBS in Bristol many years ago. SEMS used to provide full support for spare part bins: there was a screen where you could define the bins and give them names etc. The spare part catalogue would then allow you to assign spare parts to bins. A nice feature of this approach is that you can’t accidentally assign parts to non-existent bins, but the downside is that it introduces a significant data entry overhead: you have to create the bins before you can use them. This is even more cumbersome for smaller hospitals which may only have a single bin for each spare part. An interesting side-effect of this strategy is that bins can exist which have no parts assigned to them (which is not the same thing as an empty bin, which is a bin which has parts assigned to but but where the number of parts in the bin is zero).

At the other extreme, several systems completely ignore bins and just have a spare part catalogue with a field to represent the quantity in stock, which makes an implicit assumption that a spare part is held in only one bin. This is surprisingly common. For E-Quip, we chose a compromise approach which allowed a spare part to be held in multiple locations but which did not impose the overhead of a separate spare part bin entity. How does this work?

For the next couple of paragraphs  I will use the term bin to refer to spare part locations, since this avoids confusion with the term location, which specifies where the bin is. e.g. “Bin 36 in the Anaesthetics Workshop” is, strictly speaking, a spare part location named “Bin 36″ in the location “Anaesthetics Workshop”, but this overuse of the word location can obscure the explanation. Later on in the article I will switch back to using the terms bin and spare part location interchangeably.

In E-Quip a bin is simply “a named link between  a spare part and a location“. What makes these useful is that the name is not constrained to be unique, and neither is the spare part or the location.

This is quite an important definition – it implies that a bin cannot exist in the absence of a spare part. There is no such thing as a bin which has no spare parts assigned to it – there is no place in E-Quip where a bin can be created except on the spare part screen, which means that the example used above (“Bin 36 in the Anaesthetics Workshop“) is only meaningful in the context of a specific spare part.

Suppose we wish to record that Part No 123-456 is (or can be) held in  Bin 36 which is located in the Anaesthetics Workshop. We simply create a link between Part No 123-456 and the Anaesthetics Workshop location, and then name the link “Bin 36″.

This is done on the spare part property page, using the Locations tab. You simply type a code and/or name for the bin, then choose the location. This is no different to editing any list within E-Quip. The example below shows two bins being created for a spare part.

Note that in a system which actually included bins in the data model, you would first need to create the bins before you could make use of them, so in the example above, bins 3 & 23 would be selected from a list, not entered as free text.  Our approach has several advantages, chiefly in the reduction in the amount of data entry when creating spare part bins, but it does have some disadvantages. Because the concept of the bin is implemented textually, care is needed when entering bin codes and names. However, this disadvantage is far outweighed by the saving in data entry effort.

How do we model a situation where multiple different parts all share the same bin? Simple, just create another link between some other spare part and the Anaesthetics Workshop location, and give it the same name as before, i.e. “Bin 36″. Now, when looking through the list of links (i.e bins), “Bin 36″ will appear twice, with a different spare part in each case.

The example below appears to all intents and purposes to be a list of the spare parts held in Bin 52 in the Technical Services spare parts store. In fact, these are just 5 separate links between spare parts (each different in this example) and the location Technical Services. It is the textual name of this link which implements the concept of a spare part bin.

Naturally there is more to a spare part location than just a named link between between a spare part and a location. The system must be able to record the number of parts in the bin, minimum stock levels etc.

Spare Part Location Attributes

In addition to a spare part and a location, bins can also include the following information:

1. Code & Name: The name of a spare part location is factored into two separate elements simply to reflect common usage. For some types of store, locations can be referred to in a coded way. A common example is a two-dimensional array of plastic drawers each containing multiple partitions. An individual drawer might be identified by a  number followed by a letter, with the number representing the column and the letter indicating the row. The particular partition within the drawer could be represented by a 2nd number. Thus 10D5 would uniquely identify a drawer and the location of the part within it.

In other situations, particularly for larger items, locations may be referred to by names, such as the “Graseby Shelf” or “Mattress Parts”.

2. Stock: The current number of spare parts in this bin. Note that when spare parts are used on jobs this value is not decremented until the job is finished.

3. In-Use: The number of spare parts in the bin which are currently assigned to non-completed jobs. The stock minus the in-use count should yield the number of parts currently  in the bin.

4. Minimum Stock Level: If the stock level falls below this value then the bin should be replenished.

5. Target Stock Level: This is used by auto-ordering, and specifies the desired stock level for replenishment.

5. Expiry Date: Sometimes spare parts have a limited storage life and must be consumed in a particular sequence. Batteries and O2 cells are common examples. See the paragraph on shelf-life below for fuller details.

6. Team: If the same spare part exists in multiple locations, then assigning teams to locations can be a great help in ensuring that the system takes parts from the correct locations when they are used in jobs.

There are several ways in which multiple stores can be managed, but at a simple level suppose that you have AAA batteries stored in both Technical Services (Bin 525) and in the Anaesthetics Workshop (Bin 4). By assigning Bin 525 to the team “EBME” and Bin 4 to the team “Aesthetics”, if an anaesthetics engineer uses a AAA battery then the system can easily determine which bin it should be taken from.

7. Inactive: Making a spare part location inactive prevents it from being selected in future.

Expiry Dates and Spare Part Shelf-Life

We have seen that a spare part location is simply a named link between a spare part and a location. How can we model a situation where there are several identical spare parts in a bin each with a different “use by” dates. Clearly in this situation, when using a spare part on a job
it is not enough to say for example that an O2 cell from Bin 52 was used – we need to select the O2 cell based on its remaining life, and record which particular O2 cell was used.

Suppose that Bin 52 contains 5 O2 cells each with different expiry dates. Apart from their expiry dates these cells are all identical and all have the same Part No. In the case of bins containing several different parts we have already seen that several spare part locations can be created for the same location and each given the same name. We can also create multiple spare part locations for the same spare part. This is shown below.

Note that when uniquely identifying parts in this way, it is common for the stock level to be 1. i.e. We have 5 rows each with a quantity of 1, rather than 1 row with a quantity of 5.

When one of these O2 cells is used in a job, how can we specify which one was used? The Spare Part Location lookup includes an Expiry Date column, allowing you to select the particular part used.

 

Spare Part Location Management

0 comments on this article Posted by Graham in Spare Parts on April 21st, 2012 , tagged with .

Spare Part Location Bulk Update

From time to time you may want to re-organise your spare part store, and this will probably involve renaming your spare part bins. How can you do this?

For a single spare part you can do this by editing the spare part, going to the Locations tab and then simply change the location code and name by typing directly in the grid.

For example, if you wanted to rename Bin 1 (whose code is 00001) to have a code “A52″ and the name “Bin 52″, then you can just type this directly into the grid shown above, replacing “00001″ with “A52″, and “Bin 1″ with “Bin 52″.

This is fine for a small number of spare parts, but if you were reorganising your entire store this could be very time consuming. Things also get more complex when a spare part location contains many different spare parts, which is extremely common. If Bin 1 contained 5 different parts, then each part in this location would have to be found, and the location renamed.

The screen-capture below (of the Spare Part Bin summary screen) shows the contents of Bin 1. We can see that it contains 5 different small parts.

It would be very time-consuming to open Part No 0126-0025-K and rename its part location, and then to repeat this for each part in the bin. That’s where spare part location Bulk Update comes in.

Simple select all of the bins (Ctrl+A), then right-click the mouse and choose Bulk Update. This will display the following screen.

Just enter the new bin code and name, then click Update.

 

Version 2.1.0 Released

0 comments on this article Posted by Graham in New Features, New Releases on April 21st, 2012 , tagged with , .

Version 2.1.0 was released on 17th April, 2012. Coming hot on the heels of 2.0.0 the changes are relatively minor, but have all been requested by our users. The changes include:

All Screens: Document Quick Link is now available on all summary screens.

Jobs: Decontamination Certificate No has been added to the job screen.

Contract Renewal: The Contract Renewal Wizard now auto-populates the contract code field and the way that suggested dates for the new contract are calculated has been improved.

Invoicing: A simplified invoice Bulk Update facility has been added, along with some new Look For filters: Today’s Invoices, Yesterday’s Invoices & This Week’s Invoices. These all make it simpler to find invoices that have been auto-created. The invoice total amount is now displayed in the invoice lookup, which also makes it easier to find invoices.

Equipment & Job Leading Zeroes: There is now a role-level option which allows the format of equipment & job numbers to automatically include leading zeroes. i.e. 000123 rather than 123. This is mainly for users who have come to E-Quip from older systems which used leading zeroes as a way of simplifying numeric sort orders.

Domains: In previous versions the domain was always called “(Local)” and most users would only ever see it as part of the E-Quip window title which is made up of the user’s login name, the domain name followed by the screen name. e.g. “John (Local) – Asset List”

Users who routinely connect to multiple databases (e.g. a training database, the detached Client etc) could sometimes forget which database they had connected to which could cause confusion. To overcome this, the domain name is now made up of the server name followed by the database name.

In the example below the client is connected to server VirtualXP-52772 and the E-Quip database name is TRAINING.

 

 

Rigel 288 CSV Imports – Choosing the Output File Type

0 comments on this article Posted by Graham in Electrical Safety Tests, Test Equipment on April 21st, 2012 , tagged with , .

Creating Individual Test Files from the Rigel 288

When you import a CSV file containing multiple test results from the Rigel 288, E-Quip creates an individual file for each test within the CSV file and attaches it to the asset being tested. Previously these individual files were named using a combination of the equipment number and the data/time that the import was run:

ESTImport_EquipmentNo_YYYYMMDD_hhmm_fff.txt

(where fff represents the number of milliseconds).

The reason that the number of milliseconds is included in the file name is because it is possible for the same device to be tested with the Rigel several times in rapid succession. If the same device was tested twice within 1 minute this would result in 2 files being created with exactly the same name, which Windows will not allow.

The file name extension of “.txt” was chosen because every computer running Windows is able to open this type of file simply by double-clicking on it.

However, some users have requested that these files should be saved with a “.csv” extension. This is reasonable as it not only reflects the internal format of the file, but on computers with Microsoft Office installed, double-clicking a CSV file automatically runs Microsoft Excel, which some users prefer.

As from version 2.1.1 you can now choose which file name extension will be used when creating individual test files.

Note that this has no effect on the actual contents of the file, only the file name extension. The contents are exactly as they were in the original Rigel CSV file

Contract Renewal

0 comments on this article Posted by Graham in Contract Management on April 21st, 2012 , tagged with .

How to Renew a Contract 

Suppose that you have a contract which runs from 1/1/2011 to 31/12/2011, how should you renew it for 2012? Perhaps it might be better to emphasise how not to renew it.  The simplest thing to do is simply to edit the contract and change the start date to 1/1/2012 and the end date to 31/12/2012.

This is an extremely bad idea! If you do this you immediately lose all historic information about the contract. Quite apart from that, all of the jobs that were linked to last year’s contract will now be linked to this year’s, as will all the jobs from the year before that, etc.

Never renew a contract by editing an existing contract

Always renew a contract by copying an existing contract

This means that if you have had a contract for 10 years and renewed it every year, then you should have 10 distinct versions of that contract. Each will have different dates, will  probably have different financial details and the equipment schedule for each will most likely be slightly different.

The basic steps in contract renewal are:

1. Make a copy of the contract to be renewed, along with its equipment schedule

2. Edit the new contract to reflect the new dates, reference numbers, financial details etc.

3. Remove any devices that are no longer covered from the new contract schedule. For example, some of the devices  covered by the old contract may have been decommissioned and cover is no longer required

4. Add new devices to the new contract schedule as necessary

As you would expect, E-Quip contains tools to automate this process.

Auto-Renewal

On the contract summary screen, select the contract to be renewed, right-click the mouse and choose Renew from the menu. This will display the Contract Renewal Wizard, shown below.

The information requested by the wizard is fairly self-explanatory. The only information which is mandatory is the contract code, which is auto-generated exactly as if you were adding the new contract manually.

How can E-Quip determine the start & end dates for the new contract? Basically, it can’t, and neither can any other piece of software. The only people who know these dates with absolute certainty are you and the contract supplier. They will be included in your contract documentation. E-Quip can try to suggest dates based on the contract being renewed, but only your documentation will tell you if these dates are correct.

The New Contract Start Date

The wizard will assume that the new contract is to run sequentially with the contract being renewed. i.e. If the last contract expired on 31/12/2011 then the new contract will commence on 1/1/2012. This is a reasonable assumption, but might not always be the case, so always remember to check this date.

The New Contract End Date

Suppose that the contract being renewed ran from 1/1/2011 to 31/12/2011, which is a span of 365 days. Does that mean that the new contract should run for 365 days? If this were the case we would lose a day’s cover as 2012 is a leap year. It is probably more common for a contract period to be 1 year than for a specified number of days (i.e. 1 year rather than 365 days), so it is reasonable for the wizard to suggest  dates of 1/1/2012 – 31/12/2012 for the new contract dates, but there is no guarantee that this is correct. Remember, always check the contract dates.

What if you are renewing a contract that ran from 1/12/2011 to 31/12/2012. This is a 31 day contract, so the new contract should run from 1/1/2012 to 31/1/2012, but what should happen the next time this is renewed? The December to January renewal was simple as both months have 31 days, but January to February is more problematic. The new start  date will obviously be 1/2/2012, but should the end date be 29/2/2012 (remember that 2012 is a leap year) or 2/2/2012? Is this a monthly contract or a 31-day contract?

While you might accept that the concept of a monthly contract is more common than a 31 day contract, this is not necessarily the case when contracts are for services which are charged daily. For example, rolling 28-day contracts for  mobile phones are quite common.

The important point is that the dates suggested by the wizard are exactly that, suggestions, whose purpose is to save you some typing if they are correct. They should always be checked.

In the example above of the (presumed) monthly contract, the dates suggested by the wizard for each renewal will be:

What does Auto-Renewal Do?

1. It creates a copy of the contract being renewed with a new contract code, auxiliary code, supplier reference, order number and start/end dates. All other values are copied from the original contract.

2. It copies all active equipment from the previous contract equipment schedule to the new equipment schedule. In this context, active equipment means equipment with a status class of active, where the equipment record has not been deactivated.

3. The Renewed flag of the old contract is set.

What Should you do Next?

Once you have created the new version of the contract you should:

1. Edit the new contract to change any data which is no longer correct. For example, new contract financial details may need to be altered.

2. Remove any devices which are no longer covered from the equipment schedule.

3. Add any new devices to the equipment schedule as necessary.

 

 

A Developer’s Day – A Bad Day at Stansted!

0 comments on this article Posted by Graham in A Developer's Day on April 20th, 2012 , tagged with .

The Lost Passport & Driving Licence Saga

I thought I would write a series of occasional articles about the kind of nonsense that fills my day, either at work or otherwise, and to kick this off I shall share with you how a software developer goes about replacing a lost driving licence. Think of the old “how many … does it take to change a light bulb” jokes, and you might see why we developers are not often allowed out to meet other people! In my defence, I had plenty of spare time because I was confined to bed for a few days, and Maria insisted that I was not allowed to work, otherwise I would have been hiding under the covers with my torch dashing off E-Quip code – honest!

On the way back from Dublin a couple of weeks ago I left my wallet (containing £100 and all my plastic) and passport (which had my driving licence in the same holder) on the long-stay parking bus at Stansted airport. This would be a pain at the best of times, but I’m  off to Italy shortly, for which the passport and driving licence would come in very handy.

So, first things first – all plastic cancelled. Next, how do I get a new passport and driving licence in 2 weeks. I can fast-track my passport application and get that within a week, so no problem there, but what about the driving licence? I can apply on-line, for which I need my current driving licence and passport numbers, but of all the important numbers that I can remember (the wife’s birthday, wedding anniversary, my old RAF service number, my first ever phone number, the number of yards in a mile and the coronation date of George III), I have no record of either of these two numbers. Incidentally, George III was crowned in 1760, which is the number of yards in a mile, so that should perhaps only count as one number.

The only way that I can get a driving licence without knowing my passport number is to apply by post, but unfortunately DVLA want all the same documents as the Passport Office, and neither of these will accept copies. This means that unless I want to wait 6 weeks I have no choice but to find these two numbers.

Getting my driving  licence number wasn’t too bad. When I changed my car a few months ago the nice people at Peugeot photocopied my licence, so all I had to do was call them, convince them that I was who I said I was, and they sent me a copy. So far, so good. Now for the passport number. I’d just come back from Dublin, and I still had a bit of my boarding pass – surely your passport  number is printed on that?

Diversion 1 – Boarding Passes

Had all this happened a year ago that would have been the end of the story, but Ryanair no longer print passport numbers on boarding passes.

However, just because the passport number doesn’t appear on the printed boarding doesn’t mean it’s not there! When your aircraft eventually arrives and you finally get to the end of the queue at the gate, there must be some way for the keen, efficient, polite & courteous Ryanair staff to check your passport number against your boarding pass. No prizes for guessing that it must be embedded in the data matrix bar code.

Unfortunately, I don’t happen to own a data matrix scanner, but I do own a mobile phone, and a little bit of searching found a couple of free “apps” for my phone which claim to be able to read bar codes. A after a little bit of experimentation with a packet of Paracetamol and a jar of anchovies I’m ready to move up from bar codes to data matrix. Unfortunately, while my phone could happily read bar codes, even though the software had an option to read data matrix labels, it failed at every attempt, so I’m back to square one.

By now I should have spotted all the characteristic signs of a project that was set to waste a huge amount of time, but, undaunted, I stumbled on. A bit more Googling told me that the standard symbology adopted by IATA for boarding passes is called PDF-417, and its use is defined by a standard called BCBP (Bar Coded Boarding Pass). Still more Googling found some web sites that can decode PDF-417 labels, and having far more spare time than common sense I persevered and headed to http://www.datasymbol.com/barcode-recognition-sdk/barcode-reader/online-barcode-decoder.html. Success!

This is what is hidden in the boarding pass:

M1STANBURY/GRAHAM LIKWFK STNDUBFR 225 088Y000 104 10C DEMO

4D315354414E425552592F47524148414D2020202020204C494B57464B2053544E445542465220

203232352030383859303030203130342020313043202020202020202044454D4F

0 10 146 10 0 603 0 603 146

Surely my passport number is buried in that little lot somewhere – perhaps I should have stayed on the IATA BCBP web site a little longer! Any code is easier to crack if you have a crib, just ask Alan Turing, and I will admit that knowing that my name is Graham Stanbury and that the boarding bass was for flight FR 225 from Stansted to Dublin did contribute significantly to simplifying the decryption. But I still couldn’t see anything that looked like a passport number, so back the IATA BCBP web site to dig into the standard.

The first character, “M” means that this is a multi-leg flight, and the next number indicates how many legs, so “M1″ means that this is a multi-leg flight consisting of 1 leg. Hmmm, I can’t say  I’m entirely sure about a multi-leg journey which consists of a single leg as a philosophical concept, but I’ll leave that one for the philosophers.

After the flight number FR 225 there is 088, which, according to the standard is the date of the flight, i.e. day 88 which is 29th March. The “Y” refers to the compartment number. Now I’ve never found a Ryanair flight which has an upstairs bar area, or even a little blue curtain between the front and the back, so I guess this is always “Y” for Ryanair. Then we have the seat number – for any Ryainair frequent flyers I’ll just allow a few minutes for the laughter to die down.

Then we’ve got the check-in sequence number, 104. I guess that this means that I was the 104th passenger to check in for this flight. The next “1″ is the passenger status, which I think means “adult”.

After that we have OC, which as we all know is 12 in hexadecimal. Which means that the next chunk of information is 12 characters long. All the programmers among you will have immediately spotted the sequence of 20′s in:

202020202020202044454D4F0 10 146 10 0 603 0 603 146

Now all programmers know instinctively that 20 is hexadecimal for 32, which is the ASCII space character, which means there are then 8 spaces, followed by “DEMO”. i.e. 44 is 68 in hexadecimal, which is the ASCII code for D, and 45 represents E, etc. Should we be concerned that the word DEMO appears in here, almost like this is part of a test system which has been released by mistake? So that takes care of the 12 characters,  now it looks like we’re getting close to a passport number! Is this it?

0 10 146 10 0 603 0 603 146

And that’s round about where the wheel fell off!  The last block of boarding pass data is the security section, and it is airline specific – well, it bloody would be, wouldn’t it! There is a group of 9 numbers, and a passport number is 9 digits long, but these aren’t ASCII characters representing numbers, because those only range from 48 to 57 (0 – 9), which means the passport number is encrypted.

Still, if anyone fancies a challenge (believe it or not there is a limit to how much time I will waste on this kind of adventure), it can’t be very complex because it has to be readable by any scanner at the airport, and it can’t use 1-way encryption for the same reason.

Diversion 2 – Internet Browser Caches

Ok, so I have to give up on the boarding pass strategy, but this is where we software developers show that we can waste time on an industrial scale – did I give up? Not likely!

In order to get the boarding pass in the first place I had to check-in on-line. Surely the details of that would still be in my web browser cache? Can’t I just open up the history window of my browser and go back and look at the web page where I originally entered the passport information?

I use Google Chrome, and this is what my cache looked like for the day in question:

I clicked on each of these and got nothing but error messages. So, give up or dig deeper? No contest!

Did you know that you could type chrome:cache as a URL into Google Chrome? This shows you the browser cache in a lot more detail, including HTTP headers and the binary file contents, but still failed to yield a passport number.

Yet more Googling uncovered a tool called the Chrome Cache Viewer, which shows more information about your cache than you could imagine, but did it show my passport number? That was a rhetorical question, by the way –  no, it didn’t!

The Solution

There must be some way that equipped with all of this technology I can find my blasted passport number. Well yes, there is. Peter reminded me that back in 2010  our accountant had photocopied both of our passports. One email – problem solved – Italy here we come :-)

So was all of this a waste of time? Far from it. I’ve learned some new stuff, I can now use my mobile phone to check bar codes on jars of anchovies, and it’s even looking like I might end up with a driving licence and a passport. Result!

Active Directory User Validation

0 comments on this article Posted by Graham in New Features on April 3rd, 2012 , tagged with .

Log on to E-Quip using your Windows Password

As from 2.0.4, E-Quip integrates with Active Directory to allow you to use your Windows password to log on to E-Quip, as long as your E-Quip login name matches your Windows login name.

Of course, in previous versions you could create an E-Quip account with the same name and password that you used for Windows, but if you ever changed your Windows password you would have needed to also change it in E-Quip. Now, password validation is handled by Active Directory.

So, for example, if you log on to Windows as user myname with a password of topsecret, then when you log on to E-Quip as user myname, you can use your windows password (i.e. topsecret). If you change your password in Windows to mynewpassword, then you must use this password when logging on to E-Quip.

Coming Soon

One of the things which gets in the way of allowing clinical users to update their own device competence records is the massive task of user account management to allocate user names and passwords all staff in a hospital. We plan to add a mechanism whereby anyone can log in to E-Quip using their Windows security details. These users will only see screens related to device competences, and they will only be able to see their own personnel record.

 

 

Version 2.0.1 Released

0 comments on this article Posted by Graham in New Features, New Releases on March 19th, 2012 , tagged with , .

Version 2.0.1 of E-Quip AM was Released on 18/3/2012

The next major version version of the E-Quip AM software has now been released. If you would like to arrange a date for an upgrade then give me call or drop me an email.

There are some really nice  features in the new version, several of which I’ve already posted articles about. Some of the changes involve major new features, such as Maintenance Events, while others are small user-interface improvements to make using E-Quip easier, like a button to restore your default grid layout.

The full documentation is available on the E-Quip web site, but here is a summary of the most important changes and additions:

1. Maintenance Events

These are work instructions with a defined frequency that can snap to any job which is opened when the event is due. For example you can define a 6-monthly event called  ”Battery Change” which will attach itself automatically to any job (PPM or Repair) when it becomes due. if the event is completed then it is added to the device event history, otherwise it will snap to the next job that is created.

2. A New Column Chooser

Using the  previous Column Chooser it could be quite time-consuming  to define grid layouts. Now you can select blocks of fields, calculated fields, and even use Excel filtering to search for fields. Defining layouts is now really fast and simple.

3. The PDF Designer

You can now easily design PDF list reports, either from scratch or using grid layouts that you have defined with the new Column Chooser. You can specify titles, fonts, colours etc for the various sections of the reports. Reports can be saved for easy re-use.

4. Rigel 288 Safety Analyser Import

If you have a CSV file downloaded from the Rigel 288 you can now import this, which will automatically create a separate file for each test within the CSV, and link this file to the appropriate device. Optionally the import will also create a job for each test.

 

5. Contractor Spreadsheet Import

If a contractor gives you a spreadsheet with a list of the devices that have been serviced during a contract visit, this can be imported and E-Quip will create a job for each.

6. Model-Based PPM Scheduling

For simple PPM scheduling (i.e. not using PPM Schedules) you can now specify the default service interval for a model. Whenever a PPM job is completed for a device with an interval set up the equipment Next PPM Date field is updated to the job end date + the service interval.

7. Back / Forwards Navigation

As you work with E-Quip and set various filters, Back and Forwards let you navigate through your filter history.

8. Field-Level Visibility & Security for Equipment

If you give access to E-Quip to the Finance Department  so they can update financial information, how do you stop them from accidentally overwriting other important information, and how do you prevent technical users from overwriting financial information? It is now possible not only to specify which equipment fields a user can edit, but you can also hide the contents of certain fields.

9. Information Governance

A new tab on the equipment screen lets you record data-related information for a device, such as the ports that it has, what security measures it supports, etc. When a device is sent to a contractor for repair, or when it is decommissioned, the new Information Governance Event screen allows you to record where it has moved from, where it has gone to, and how you safeguarded any data on the device.

10. Attached Document Warnings

Whenever you open a property page, if there are any documents attached to the item the Linked Document Manager icon changes to an open book (rather than a closed book), so you no longer need to click on the Linked Document Manager to see if there are any documents.

11. Asset Location Reconciliation

While your engineers are working in a ward, give them a PDA with a bar-code scanner and let them scan every device into a spreadsheet. When they get back to the workshop the spreadsheet can be imported to update the last seen location of each device.

You can search for devices which  have moved and use the inventory reconciliation tool to change device assigned locations, either individually or in bulk.

12. QBE (Query-by-Example) has been added to the Equipment Screen

This makes it simple to search for devices based on date ranges, serial number ranges etc.

13. Device Warnings

When an asset is opened, in addition to the text warnings, the equipment property page appearance changes to make it immediately obvious if there is a Medical Device Alert or open job for the device, or if the device is a parent.

14. Configurable Equipment & Job Property Page Tabs

You can now choose how you prefer the equipment and job property page tabs to be displayed.

15. Document Quick Link Added to all Screens

It is now possible to use Quick Link on all property pages

16. F8 Toggles Grid Text Wrap

Pressing F8 now toggles between single and multi-line grids.

Single-line:

Multi-line:

17. Restore Default Grid Layout

If you’ve changed the grid layout it’s now really easy to restore your default. Just click the grid icon next to the right of Show all Records.

These aren’t all of the new features, but they should give you a pretty good idea of the benefits of upgrading.

As always, if you have any questions, feel free to get in touch.

 

Maintenance Events

0 comments on this article Posted by Graham in Maintenance Events, Planned Maintenance on February 14th, 2012 , tagged with .

Part 1 – An Introduction to Device Maintenance Events

This is the first of a 2-part article about the maintenance event feature of E-Quip. This article introduces the concept of events and shows a simple automated event mechanism. The 2nd article will go into this subject in much greater depth.

A maintenance event is any activity that must take place at some fixed interval on a device. Examples might be “Battery Change”, “Replace Filter” etc.

Suppose that the battery for a device must be changed every year, and the device is on 6-monthly, annual and 5-yearly PPM Schedules. If you understand how PPM Schedules work then you will know that there will always be 3 non-completed jobs for this device, one for each schedule. (If you didn’t know that, check out some of the articles on this blog on PPM management).

It is easy to envisage situations where the annual battery change might coincide with any of these jobs. Indeed, if the device develops a fault between scheduled maintenance the  battery might be replaced as part of a repair job. How is the engineer to know (without searching through the service history) whether or not the job that he is currently doing should include the battery change or not?

E-Quip now includes a simple to use maintenance event mechanism. It works like this:

1. On the model screen define which events the device needs, and their interval.

2. If you choose to make these events automatic, that’s all you need to do! Every time you open a device, if any events are due (or overdue) then you will see a list of the events. If you want to you can complete the events directly from the equipment property page.

3. Similarly, whenever you create or open a job for a device which has one or more events due, then you will see a list of the events, and those events will snap to that job. If you complete the event it will be added to the device’s event history, but you won’t hear from it again until it is next due. If you complete the job but don’t complete the event(s), then they will just snap to the next job.

For example:

Step 1: Define an event for a model.

Note that in this example both events are defined with the Auto-Link option.

Step 2: Create a job for a device of that type:

As soon as you select the Equipment No. for the job you will be warned about due/overdue events.

Them when the job opens, the events will automatically snap to the job.

It is possible to manually specify events on the job property page, such as when you wish to carry out an event before it is actually due. Events which have snapped to a job automatically will be coloured light blue. They are only temporarily attached to the job. If they are completed (by entering the completion date) then they will be permanently linked to job and will appear in the device event history.

If an auto-snapped event is not completed and the job is closed, the event(s) will simply snap to the next appropriate job.

This is the simplest scenario but events can be used much more flexibly than this. The next article on this topic will explore this in more detail.

 

Previous entries