Chapter 9. Program overview

Table of Contents

The program
PHP code
Java script code
External software used
The database

Spot contains several parts and subsystems. This chapter gives an overview of those from a semi technical standpoint.

The program

The program consist mostly of PHP code, with some java script to handle the interface.

PHP code

The PHP source code is divided into three parts; share - common library code. twospot - all the basic functions and the web user interface, and local - adaptations containing installation specific code.

Methods used to write adaptable/configurable code

A lot of configuration is done through the database, setup tables exist for almost everything. In a few cases the database will also include a filename and a function name. For these the general framework and engine of the function is written in code, and when the data has to be sent to another company or some calculations done, the correct file is included and the actual work done there, the database only controls what library is used. Usually the files included contains fairly simple code.

For lookup tables when the choice matters, there's usually a separate field in the data that's an enum or similar. For example in the eaddress_type table that defines what type of phone/email/fax/IM and whatever codes can be entered on a customer there's a field that says if this type of electronic address is a phone number or an email.


All records can have linked notes, they are stored in the same table to make it easy to show all notes on all records connected to a company to the company itself with only one query. Table name and key value are used to link the data to the actual record that they referee to.


The code is setup to make it easy to add new tables, this extends to making it easy to add new forms, one form for one record is the general rule. The normal presentation of data is done through the inputLayout function that will create the correct HTML input tag for the data that's supplied. There's a slew of other functions to show connected information in different ways depending on how it should be presented.

Meta data

The database structure is entered into a file called tables.struct. It's a simple XML format that describes how to generate the "create table" for new tables and "alter table" when there are changes to the existing structure. It also contains some extra information on how to use the data, like a display name for the column and search criteria to use when searching the data. When used the definition in the file is compared to the actual structure live in the DB and the appropriate commands are issued to make it the same in a non-destructive manner.

The menu system

Menus are defined in the menu.xml file, each menu has an id and a name. It can also have a file that will be loaded when the menu is picked. Then there's a list of items that link to other menus. If the menu doesn't have a list of other menus the menu itself will not be updated when it's clicked from somewhere else.

Connecting to the database

Access to the database is though the $GLOBALS["conn"] variable for selecting data. There are a few handy functions to select data from the database or display data, but nothing in the API to loop over and process data. Mostly because the mysql_query and mysql_fetch_assoc are easy enough to use on their own. The only select functions added have been to make it easier to write the code.

For insert, delete, and update the doInsert, doUpdate and delete_rec functions are used. delete_rec uses a different naming standard to make it a bit harder to write the wrong command by mistake. When these are use, the call in a bunch of classes. The first being SqlHandler that are inherited and used by SqlInserter, SqlUpdater and SqlDeleter. These are not used directly but always trough the doInsert and friends. The SqlHandler objects will in turn require and use (if they exists) two more classes, validate and postprocess. Validate is used to check the integrity of the data and to set some useful defaults. It also has the power to refuse an update if there is information missing or if for some other reason the action should not be executed. Postprocess is used as a PHP replacement for triggers and does things that would normally be done using that.

Data integrity

The SqlHandler classes described above will check for some integrity, and will also make sure that any changes to the database is logged and can be viewed later. Logging is done to the cust_log table that has the user doing the action, what type of update, the IP address, what table and the datetime the action was performed. It also has a difference as a text in the form +field: 1 -field: 2 for a change where field changed it's value from 2 to 1. Text and blob fields are only logged with the first 60 characters. The cust_log table can be accessed from each record and you can see a list of all changes to that record, it's also possible in the software to include changes in tables that link back to this table if needed. Deletes are also logged, so in theory it's possible to recreate deleted records using this. In practice since long fields are cropped it's not an option right Now. Changes to blob and text fields should be logged in a separate table with difference data to allow for that.

Java script code

The twospot.js file controls the interface. It's responsible for the menu and to call in the right new pages to show.

External software used

Spot can use a few pieces of external software, for example htmldoc is used to generate PDF files from HTML files.