Building systems that solve your business problems

Project: X

Background

Tony Smith, Managing Director of AMEANA LTD., had worked on several prestige construction projects in the Middle East and South East Asia including Bangkok’s 314 metre high MahaNakhon Tower.

The MahaNakhon Tower, Bangkok

Tony had worked with construction management software before, such as Oracle’s Aconex but due to the high cost of that product, was often forced into using spreadsheets, local filing systems and general purpose email to control his construction projects. He wanted a cloud-based database system that would be a secure, low-cost platform offering up-to-date project data, document version control, event notifications, document authorisation or approvals, with control over access to information dependent upon a number of factors including the specific role of a user or organisation. The collaborators for the system would be the client (developer), project managers, consultant designers, quantity surveyors and construction companies.

Single Page Application

Tony’s mock-up of the ‘look and feel’ of the system had a menu navigation on the left with six main menu items, later expanded to be eight menu items and a total of 38 ‘sub-menu’ items referred to as ‘modules’ by Aventec. Each module when clicked on would launch a different ‘dataset’ in the main central area of the screen and new records could be added or existing records viewed or edited from that dataset.

The original PowerPoint mock-up of the required layout

The top centre of the screen would show the project name, which could be changed to any of the user’s projects by clicking on the ‘hamburger’ bars to the left of the project name.

Steve Smith could see that much of the screen layout would remain the same while different ‘edit forms’ were being opened and closed for the datasets that were being accessed. It was therefore clear that a ‘Single Page Application’ methodology was appropriate for development of the system. While there are many software frameworks that allow such development, Steve decided upon creating his own lightweight methodology that would also allow for fast and efficient operation of the system. This methodology termed ‘triple-q’ proved effective and if you are technically minded, you can read more about it by clicking here.

Later mock-up: Adding a record to the Construction –> Request for Approval dataset

Understanding the system structure

One of the initial challenges in building the system was to get a good understanding and agreement of the structure of the various users and organisations that would be involved. It was clear from the outset that a single project would have many participating organisations and that each of these organisations would have various users as shown in the following screenshots:

Screenshots showing many users per organisation

Roles and Approvers set-up + Payment Certificate approver selection

However, projects themselves would be owned by a customer of Aventec but that customer would usually be a project manager or Client organisation of the project. Furthermore, setting up new projects could only be done by a user who was a Customer Administrator, new organisations within a project by a Project Administrator and setting up new users within an organisation by Organisation Administrators. This complexity was further compounded by the fact that organisations and users should be ‘global’ across the system in order to allow them to be invited to other projects, even by other customers.

The above requirements were met by creating a well defined database structure with combined Project-Organisation couplings and then a further table coupling these records to User or Contact Records.

This solution allowed for an efficient and well-defined database structure together with proper ‘server-side’ control over access to information.

Use of APIs and bespoke work

Tony wanted a dashboard with charts for the main screen that users would see when logging in. The quickest way to provide this was to link into Google’s Charts API. (An API – Application Programming Interface - is just data or services provided programmatically by another organisation’s servers over the Internet)

Google’s Charts API used for column and pie charts with filtering added by Steve Smith Solutions

The filtering was added by using JavaScript code in the browser. The dashboard is really the only area where there was significant use of an API. The Aventec ‘schedule’ modules do incorporate a Gantt Chart for monitoring project progress (and Google Charts does have Gantt Chart options) but the very specific requirements made it more appropriate to build this from scratch.

The Aventec Project Schedule including ‘Gantt’ lines

Another area where third party software or libraries might be used is to build a calendar. However, given that the requirements were quite straight-forward, it was very fast to build a calendar that scrolled one month at a time and allow meetings to be added into it.

The Aventec calendar with the full day’s meeting list opened. (Individual meeting details can be opened from either the day’s list or directly from the calendar.)

Advertising slots

To the right of the main screen is a grid 2 columns x 4 rows for adverts. The requirement was that all 8 combinations of rectangular advert sizes be allowed. Also there would be multiple ‘pages’ of adverts, so each time a new module was accessed, the grid would toggle to the next page. Further, the dashboard would have it’s own set of just two pages while the general modules would share 20 pages that could be toggled through, and adverts would be specific to a project, but packages of advert sales allowed between 5 and 20 projects with the option to choose the same project multiple times for the same advert.

A 2col x 1 row advert with a 2 col x 2 row advert underneath. And a separate grid with a 1 col x 4 row advert. (Note: No two column advert could be added to this page.)

It was required to provide a user interface for Aventec’s Account Managers to allocate new adverts to appropriate slots and be controlled so they could not accidentally cause a slot conflict.

Part of the slot select window being accessed by an Account Manager allowing them only to select free slots for a 1 col x 1 row advert.

And after the advert has been allocated to an acceptable slot

Getting the Adverts module working correctly and efficiently was probably one of the more complex parts of the system but was successful due to proper planning before any coding work was undertaken.

Deployment on AWS

The ‘Basic’ system (Dashboard, 11 user modules and all Admin/Advertising modules) was deployed along with the ‘website’ aventec.net on Amazon Web Services (AWS) in August 2021. This involved setting up an AWS 'Virtual Private Cloud' with appropriate subnets, Internet gateway, Application (EC2) server, Database, SSL/TLS certification, Email service (including configuration of ‘Sendmail’ Mail Transport Agent) for automated emailing from the system, load balancing, AWS S3 storage for document uploads with inbuild commands to ensure these can be accessed freely over the internet only by authorised users (achieved using signed URLs).

In this regard Steve Smith Solutions has provided a full build and deploy service and so can be trusted with any aspect of your IT requirements.

The ‘Standard’ system (a further eight user modules) and ‘Premium’ system (another eight modules) were both deployed in 2022. The timescale for delivery of all parts of the system was achieved in line with, or before, the promised time frame.

Project: X

Background

Mabey Hire are a plant rental company operating from 18 sites around the UK. They would invoice around 1,500 new contracts every month and send out up to 6,000 customer invoices, so their systems needed to be highly automated. Their solution up until the early 2000s was to run bespoke software via character based “green screen” terminals. Until the late 1990s, they had also used an MAI Basic Four minicomputer, which had been old hardware technology for a number of years and was becoming less and less reliable over time.

The Managing Director of Mabey Hire had full responsibility for the IT systems and following a number of repeated hardware failures, decided it was time to replace the ailing equipment with a modern Unix based server.

Steve Smith had recently joined the company as Finance Director and very quickly became involved in the businesses IT strategy, first getting to grips with the new Unix operating system, before managing the successful program changes to ensure “year 2000” compliance, and then taking full responsibility for directing the IT strategy.

Key issues to address

At the time, the main issues with the existing systems were:

• The “front office” plant rental system was totally bespoke and there was only a single consultant programmer who had a good understanding of the code base. Total reliance on this single person left the company in a vulnerable position.


• The “back office” accounting system, CBS III, also had a character-based user interface and management reporting consisted of bulky line-printed reports where information had to be sifted through, laboriously. There was no concept of drill-down!
• While many staff had standalone PCs and these could run a Unix terminal emulator to connect to the MAI systems, no-one had personal email and other office programs were DOS-based Lotus-123 spreadsheets or the WordPerfect word processor, neither of which had any level of integration with any other system.
• Front office network communications with the remote depots ran over expensive leased telecom lines and depended upon outdated multiplexer hardware.

Much of this technology had been “cutting-edge” when it was introduced in the mid-1980s but was long-overdue for replacement when Steve Smith took over the responsibility for IT systems.

The proposed solution

The solution had to be piecemeal and managed without interruption to the business processes, so the plan was multi-stage:

1. Introduce a modern MS Windows Server platform into the head-office site, with MS Exchange Server for email, MS Office applications for all users running on Citrix Application Servers along with all filing on centralised data servers.
2. Replace the CBSIII accounting system, with a modern Windows-based package running on MS SQL Server database platform.
3. Extend the Windows Server/Citrix platform out across all UK sites.
4. Replace the bespoke plant hire system with one running on MS SQL Server and ensure full integration to the accounting.

While many Finance Directors and CFOs have a tendency to over-delegate their company’s IT systems projects and so lose an understanding or even lose control, thereby leading to the many failures that are continuously documented in the press, Steve Smith wanted to have a detailed hands-on involvement in all major parts of the project as is described below.

The core platforms

The main platforms were installed at Mabey Hire’s head office in Dewsbury, UK in the summer of 2000 and as part of the purchase, Steve organised for himself and his staff to be trained on the systems including a basic understanding of networking through the TCP/IP protocols, Windows Active Directory for controlling user logging and MS Exchange Server 5.5 (which did not integrate with Active Directory, until MS Exchange 2000 became available in the UK later that year.)

For the roll-out of the new platforms across all UK sites, Steve recruited an experienced network manager from a major financial institution but maintained close involvement and oversaw proper documentation of all set-up processes and maintenance of disaster recovery plans.

Accounting system

Mabey Hire was part of the Mabey Group, which had 5 other operating companies in the UK and USA. The Group Finance Director was insistent that and new accounting system chosen be applied across group companies.

Steve led the process of choosing the system and secured agreement of all Finance Directors to implement Great Plains Accounting to run on MS SQL Server. Steve was particularly impressed with the high level of integration between Great Plains and Microsoft Office products, which was ahead of all competitors at the time. It was no surprise when a few months later, Microsoft Corporation decided to enter the business accounting software market by acquiring Great Plains and rebranding it as Microsoft Dynamics.

The system went live in early 2002 and proved a great success. Steve automated the integration of budgets and accounting journals from carefully designed Excel spreadsheets, leading to large saving in input and reconciliation time. He also produced many new reports, with full drill-down functionality using the integrated FRx General Ledger reporting platform. This was admired so much at the Mabey Group’s headquarters that they insisted the sister company in the USA send a party to the UK to find out how to do the same with their systems.

Plant rental system

In the early 2000s, most of the plant rental systems in the UK, were still character-based, rather than offering a graphical user Interface (GUI) like all modern ‘Windows’ or cloud-based offerings. One of the key suppliers to one of Mabey Hire’s competitors at the time was developing a GUI front-end to their rental application using Java applets and initial discussions took place with that supplier.

Then by chance, Steve’s team discovered inspHire software (acquired by Kerridge Commercial Systems in 2018), which was a proper windows product and ran on an MS SQL Server database. The only problem was that their system at the time, only delivered about half of the level of functionality and accounting integration required by Mabey Hire.

Nevertheless, after several meetings and demonstrations, contracts were signed and it was agreed that Steve’s Mabey Hire team would prepare detailed specifications for the development work to be carried out by the inspHire team, so that the new system could be put in place.

There followed an 18 month period of development and testing of the growing inspHire system as new functionality was being delivered. Simultaneously, routines for exporting data from the legacy system for import to the new system were also developed and tested. Detailed user manuals were written, some by Steve himself and training was carried out.

One further issue that needed to be addressed was the fact that Mabey Hire’s legacy plant rental system had never fully integrated with accounting. This meant that apart from data transfer from the legacy system, there would have to be a coordinated import of historic data from other less automated systems.

During the whole testing phase, the team became very familiar with running SQL queries on the database, which Steve says helped enormously several years later when learning the language in a more formal way at university. The team also built many new reports using Crystal reporting software, which was integrated into the inspHire system.

During the planned usual company shutdown between Christmas 2004 and the new year, all the data transfers were carried out in accordance with an extensive preprepared plan and the new system went live on 2nd January 2005. Again it was a great success in terms of both user feedback, vastly improved management information and a low, manageable level of faults.

Career change

Steve continued with Mabey Hire taking on the role of CEO of a business created by Mabey Hire’s merger with a sister company before moving into a ‘group’ role exploring international business acquisition opportunities. After he left Mabey in 2012, he was drawn by his love of IT and decided to take a career break and do a full time degree programme in Computer Science. So, rather than just specify and implement business processes, he can now build systems himself!

CLOSE

Project: X

Background

The website syhff.com was an idea had by Steve Smith himself. His objectives were two-fold:

1. To test out a business idea of a fully automated system, free to use and with incentives for users to sign up and market by word of mouth but, more importantly:
2. To test and develop his own skills at actually building a complete system. While Steve had obtained a First Class Honours degree in Computer Science, his experience with web-based “front end” skills, such as HTML, CSS and JavaScript was minimal. In fact, to begin with, he wasn’t even aware of the sophisticated development tools available in modern web browsers and as a result, struggled with his early JavaScript programming.

The idea was to build a website where individuals could register for an account allowing them to log in, amend their details, upload details, photos and location of one or more homes that they had for rent or sale and receive private messages from other logged-in users who might be interested in their property. The website also needed good filtering facilities to allow prospective buyers to find homes that met their specific needs.

Progress

The website as described took around 500 hours to build during the second half of 2019 and early 2020. There were also a number of changes during this time including moving from a Postgres database to MySQL and replacing Google Maps with an Open Street Maps solution. The website was not put live until 2022 as Steve decided to add a monetisation solution that delayed progress due to the fact that the Aventec project (described in the list of projects) took priority. Some features of the syhff website including parts of the monetisation solution are described in more detail below.

Filtering

The user selects the country where they wish to ‘search’ for properties and can then select an individual city or all cities in that country. Each of the select options was required to show the total number of properties available in that city or country.

Once the user has made their selections, they are presented with a list of available filters for isolating the properties that meet their exact or acceptable requirements.

Before filter applied

After filter applied

In the above screenshots, the left image shows how many properties match each criterium. The image on the right is after the user has checked the filter to show only properties with alarms, which automatically changes all the other numbers for the newly applied filter.

Maps

The system was built to allow users to locate their property on a map.

The system opens the map at a suitable place and zoom level based upon the UK postcode, US zipcode or other worldwide city entered by the user as part of the property address details.

Once a marker is added, the location coordinates are written to hidden form input fields for saving to the database.

However, the user can also “drag” the marker to a different location on the map and the coordinates are automatically updated in the hidden fields.

The system was originally built to use Google maps API but due to concerns over possible costs an alternative API was added into the code and parameters added so that it can be switched back to Google in a matter of minutes if required.

Open Street Map is a free resource that provides mapping data and Thunderforest build on top of this to provide the actual imagery (“map tiles”) for each possible zoom level. Thunderforest have a ‘free tier’ level. Leaflet provide the interaction tools for such things as adding markers and retrieving coordinates.

Bringing these three mapping platforms together to replace Google Maps API was initially challenging but Steve Smith Solutions is able to undertake this work if anyone is thinking of doing the same.

Database locks and ‘user’ locks

If you’ve ever booked an airline ticket and reserved a particular seat on your flight, what is to stop someone else booking the same seat before you’ve completed your booking and made your payment?

There was a similar ‘systems’ challenge with syhff in that there is only one advertising slot per city on the search page, as shown in the picture.

If an advertiser selects this slot, then it is ‘reserved’ for them as soon as they proceed to the checkout.

Anyone trying to ‘choose’ the same slot would then be excluded from doing so, unless they had already opened up the page showing available slots before one was selected by another user. In this case they would need to be notified that the slot had been taken before they could proceed to checkout.

Achieving this functionality and providing a good user experience required a combination of different types of ‘locks’ at various points in the codebase.

This can be further complicated by the payments provider’s API as is described below.

Again, Steve Smith Solutions can provide advice on how to build a robust system to deal with this type of requirement.

Stripe API – payments integration

syhff.com uses Stripe to process card payments for advertising.

Integration with payment platforms has become more complicated than it used to be.

For instance, Stripe now needs to access a ‘web hook’ on the server side of your website in order to push information through when a payment has been approved or rejected and code needs to be incorporated in order to process this separately against information that has been passed from the user's web browser.

Again Steve Smith Solutions can help with integrating your website into any payment provider’s API.

Conclusion

syhff.com is a complete database driven website with links to other providers’ APIs. It has many more features not fully described here such as browser-based image compression and auto-adjusting pricing for advertisers dependent upon various dynamic factors.

Whatever your systems and programming requirements are, Steve Smith is able to provide a Solution!