Read Me

README file for KEvMan version 0.3

Authors: 
Sam Crossley [php, SQL, documentation] (samuelcrossley at yahoo.co.uk)
Krishna Kumar [various]
Alex Ridge [javascript, implementation of mailing scripts]
Christian Steinruecken [css]
Andrew Munro [css]
Adam Reid [testing]
Uses GNU public license software by TengYong Ng (datetime picker) and Jim Jagielski et. al (phpmailer class)

23-Aug-2012


KEvMan was originally developed for use by graduate students of King's College, Cambridge University. Webmasters based at Cambridge can also read the file 'README_CAM.txt' for some Cambridge-specific hints on installing KEvMan.


1. What is KEvMan?

1.1 In brief

KEvMan is a ticketing and billing system suitable for organisations such as student unions. Managers create events, users buy tickets. Periodically the treasurer can create a bill which KEvMan will assist in generating and send out to every user with payments outstanding. Users can also submit claims for expenses they have incurred on behalf of the organisation which are then deducted from their bill.

1.2 How it works

1.2.1 Users and administrators

'Users' are anyone authorised to use KEvMan to buy tickets to events. 'Managers' are people authorised to create and edit events. Managers can only edit their own events. 'Supermanagers' are like managers but can also edit the events of other managers. 'Treasurers' keep track of how much everyone owes, and periodically create bills to settle up. 'Accounts' maintain the user list. 'Admins' maintain the administrators list, i.e. who gets to be a manager, treasurer etc.

1.2.2 Ticket sales

When a manager creates or edits a KEvMan event they may specify the maximum number of tickets to be sold, maximum tickets per user, date tickets are released to general sale (date-start) and date tickets are removed from general sale (date-end). Users can then buy tickets between date-start and date-end. The manager must specify a policy for ticket resale - users are either free to cancel purchased tickets or are required to resell unwanted tickets to other users (which KEvMan will facilitate). Managers also have control of a separate 'manual ticket' database for private issuing of tickets to their event.

1.2.3 Confirmation - after the event

When the event is over the manager MUST follow up by 'confirming' the event. When an event is confirmed the manager is presented with a summary of tickets sold etc, and is asked to 'sign off' on the event. Effectively the manager is formally stating to the treasurer that the information for the event is accurate and ready for billing. The manager should also use this as an opportunity to reclaim any expenses incurred by them whilst organising the event.

1.2.4 After confirmation

A confirmed event becomes 'read only' and only accessible in an archive section of the site. KEvMan generates a series of 'transactions' out of the ticketing information provided by the manager during event confirmation. Transactions are only viewable/editable by the treasurer.

1.2.5 Billing

Newly created transactions are attributed to a special category called 'CURRENT_BILL'. Periodically, the treasurer will create a new bill. When this happens all the transactions attributed to 'CURRENT_BILL' are reassigned to this new bill which might have a name like 'Period Feb to Apr 2012'. All users with outstanding transactions are automatically emailed to notify them that they are about to be billed, and requested to check their bill for errors. At some later point (ideally communicated in advance to all users) the treasurer must 'confirm' this new bill. After that the transactions become read-only and may not be edited by anyone. Thus a bill should only be marked as 'confirmed' when it has passed a point of no return in the 'real world', at which point it is appropriate that KEvMan also set the information in concrete (it can be undone at the SQL command line in an emergency).

1.3 What KEvMan isn't

- KEvMan generates bills but it doesn't process payments directly.
- KEvMan relies on Apache user sessions for security. KEvMan is only as secure as the Apache installation it runs on.
- KEvMan does not use any kind of data encryption.

1.4 License

KEvMan is a property of King's College Graduate Society (copyright, 2012). The system is distributed under Affero GPL License Version 3 or later. This program is free software: you can redistribute it and/or modify
it under the terms of the GNU Affero General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version. This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU Affero General Public License for more details.

You should have received a copy of the GNU Affero General Public License along with this program.  If not, see <http://www.gnu.org/licenses/>.

2 System requirements

2.1 Server requirements

- Server running a Linux OS, accessible by all intended users of KEvMan.
- Apache webserver (test version was 2.2.14)
- PHP (test version was 5.3.2)
- MySQL database (test version was 5.1.63)
- User authentication framework for Apache to resolve user names (test version was Cambridge University's mod_ucam_webauth)
- For mailing to work, PHP must be configured appropriately.
- To resolve user details from user names: access to an LDAP server (or equivalent) with that information.

2.2 Client requirements

- Internet browser with javascript and session cookies enabled.
- You could rewrite index.php and buy_ticket.php to make KEvMan javascript-free for general users. Some admin pages use a non-essential javascript datetime picker by TengYong Ng (GNU public license).
 
2.3 Client authentication

As KEvMan is designed to allow users to accumulate real-life credit with an organisation, proper identification of users is very important. KEvMan delegates user authentication to Apache such that the username (SUID hereafter) appears in the PHP superglobal $_SERVER['REMOTE_USER']. Additionally, in order for email features to work every user must have an email address of form SUID@x.y where x.y is a web address common to all users. There are many guides available online for configuring Apache authentication.

All Apache users might not necessarily be KEvMan users. This is the purpose of the 'user list' which is entirely internal to KEvMan. It's basically a list of people who are 'billable', i.e. are known, trusted users who will pay their dues on demand.

In summary, there are three possible scenarios that may greet a client attempting to access KEvMan:

- Client fails to authenticate to Apache. Client will not be able to access any aspect of KEvMan.
- Client successfully authenticates to Apache but is not present on the KEvMan user list. Client should be able to browse events and use the 'My Account' page, but should be unable to purchase tickets.
- Client successfully authenticates to Apache and is present on the KEvMan user list. Client should have full access to KEvMan.

2.4 High-volume usage

To enable high-volume usage (i.e. if you expect large numbers of clients to be simultaneously accessing KEvMan, e.g. to buy tickets to a popular event), reduce the mail time delay to a small value. The mail time delay is a safety feature to prevent a mis-configured or faulty installation of KEvMan from generating large volumes of spurious email. KEvMan is largely untested for high-volume usage, please get in touch with your findings and recommendations. You may be able to improve high-volume performance by stripping down the pages header.php, index.php and buy_tickets.php. To edit the mail time delay, change the value of the sleep() functions in mail.php for both send_mail and send_mail_many. Suggest only do this if your installation is working and well tested.

2.5 Client name resolution

To resolve a client's real name (and other details) from their SUID you will need to write your own function which would e.g. connect to a remote LDAP server with that information. This is optional as the SUID alone should be sufficient to identify all clients. The file to edit is ldap.php, although the mechanism for name resolution needn't be LDAP based.


3 Detailed HowTos for various tasks

Hopefully things should be reasonably well explained by KEvMan itself. However if anyone has time to fill in this chapter, please do!


4 Installation

NB If you don't have shell access to your server you'll need to perform file edits before uploading everything. Troubleshooting problems will be much easier if you have command-line access to the MySQL database (refer to Appendix B).

- Extract and upload everything to the public html part of your Linux/Apache server.
- You need to make sure that the KEvMan files are protected by Apache authentication so that only identifiable people can access it. See previous section 2.3 for ideas of how to achieve this.
- Edit 'sql.php'. Fill in the details of your MySQL database, as instructed.
- Edit 'user_parameters.php'. Update the variables therein with your preferences. Leave debug mode on for now.
- Edit the KEvMan file 'install.php'. Create an installation password as instructed - this will only be used once.
- In your webbrowser, navigate to 'install.php'. Apache should challenge you to authenticate. You will then be further challenged to enter the installation password you created in install.php. install.php will then attempt to connect to the MySQL database and create all the tables that KEvMan will need. It will also add you to the system as the first admin user. You should get a transcript of this process appear in your webbrowser. If you see any error messages, take a look at the database manually at the SQL command line to see if all the tables are there (see Appendix B). If not, double check the information in sql.php. As a last resort you can create the tables manually by copying the commands out of install.php.
- Navigate to index.php. If all has gone to plan you should see a link in the header to access the admin page. When you access the admin page, assign yourself all of the other administrative privileges - supermanager, treasurer and accounts.
- Edit terms_and_conditions.php with the terms and conditions you wish to impose on users (including, and perhaps particularly, expense claims).
- Replace 'images/banner.png' with something appropriate for your organisation. Your image should have the same dimensions in terms of pixels as the default banner.png.
- Access index.php again. Check everything looks OK and your edits have worked.
- Access the accounts page to create the user list for KEvMan. At this stage just add your pals to help test the system. You could also assign administrative privileges to others at this stage.
- Give KEvMan a whirl. Create a few test events and get your pals to buy a few tickets.
- Edit 'ldap.php' to get user names to display correctly in KEvMan. See also section 2.5. If you don't know basic PHP skip this step. Cambridge users can just use 'ldap_cam'.
- When you're satisfied that everything is working, turn off debug mode in user_parameters.php.
- Check is that emails are going out correctly (emails are never sent in debug mode). See troubleshooting if not.
- Look into ways to back up the MySQL database periodically. This can be done manually via an sql dump or such, but better to include it in a cron job. Do an internet search for ideas on how to keep your SQL database safe.
- To make KEvMan look nice, customise css/main.css in your linux server.
- When you're ready, access the accounts page to create the operational user list, and start posting events.
- backup.sh has the shell script to take regular backups everyday at 12:00AM and email it to the computing officer. Modify it to suit your needs. 


5 Troubleshooting

5.1 Mail doesn't work

First check the mail log in admin.php to see if it's anything obvious.
Could be:
- Debug mode is on (change in user_parameters.php).
- The PHP mail() function isn't configured properly. Check php.ini.

NB every username must have an email of form suid@$mail_domain where $mail_domain is set in user_parameters.php. If this isn't the case and you know PHP/MySQL you have two options: add an email to each user in the user table that gets looked up by mail.php (requires minor modification to the database and major modification to mail.php and user_list.php), or disable email (requires drastic but simple modification to mail.php).

5.2 Error messages of form 'undefined variable'.

This is probably a (minor) bug relating to variables of form $_SERVER[variable]. Strictly the 'variable' should be quoted - certain installations of PHP complain about this and others don't, so it could have been missed in testing. Get in touch quoting the error and the page name.

5.3 Very slow performance when lots of people try to access it at once

- Could just be the server.
- Try reducing $mail_sleep in user_parameters.php. 

5.4 Datetime picker annoyingly resets me to the top of the page.

- This is a bug. Sorry. Only seems to occur in certain browsers.

PLEASE EXTEND


Appendix A: Database design

KEvMan's database is broadly divided into two independent halves - events and transactions. Event space is edited by managers and used by users. Transaction space is edited by the treasurer and viewed by users.

Linking the two halves are 'claimsets'. The claimset mechanism allows managers to 'request' the treasurer to 'allow' their event into transaction space for billing. 'Claimsets' rather than 'claims' so that event claims with loads of tickets can be grouped and not need approval for each individual ticket.

The purpose of this design is so that the treasurer has complete control over what goes on in transaction space. This means that transaction space, if well managed, should be more or less free of errors and clutter, a situation that is presumably desirable for the treasurer. A degree of clutter/disorder/creativity in event space is thus permissible, due to its separation from transaction space. This situation is again presumably desirable as it allows the disorder that real-life events tend to attract to be reflected in the database.

A crude ASCII representation of the above follows. You might need a fairly wide screen to view it.


                                              -----
                                              | X |
                                              -----

                                                ^
                                                | treasurer
                                                | reject

----------                               -----------------                             ----------------
| Events |      ------------------>      | Set of claims |       --------------->      | Transactions |
----------      event confirmation       -----------------       treasurer accept      ----------------
editable                                 not editable                                  editable by
by managers                                     ^                                      treasurer only
                                                |
                                                |

                                        --------------------
                                        | Individual claim |
                                        --------------------
                                        editable by users



Appendix B: SQL tables

This is a technical guide to the SQL tables of KEvMan. The tables may be edited directly by advanced users at the SQL command line. It is strongly recommended that you do not try to change anything manually without first carefully reading the relevant table descriptions here: for historical reasons some of the fields have confusing or inconsistent nomenclature and the restrictions and safeguards KEvMan' php scripts impose on data entry are mostly there for a reason. 

Every table's name is preceded by the SQL prefix set in sql.php. E.g. if the SQL prefix is 'KEvMan', the user list will be in a table named 'KEvMan_users'. This feature allows multiple installations of KEvMan to coexist in the same SQL database.

List of tables:

_budget  
_budget_privileges        
_claims                       
_claimsets                    
_college_bills                 
_deposits                     
_event_options                 
_events                        
_expenses                      
_interested                   
_mail_log                     
_privileges                   
_purchases                    
_purchases_manual             
_resell_waitinglist            
_sql_log                        
_users                         


B.1 _budget

This table defines sub-budgets to assist users (authorised in the table _budget_privileges) in monitoring the total value of their expense claims. If this table is empty, only the 'misc' category will be available for expense claims of any kind.

mysql> show fields from _budget;
+--------+-------+------+-----+---------+-------+
| Field  | Type  | Null | Key | Default | Extra |
+--------+-------+------+-----+---------+-------+
| budget | text  | YES  |     | NULL    |       |
| value  | float | YES  |     | NULL    |       |
+--------+-------+------+-----+---------+-------+
2 rows in set (0.00 sec)

budget: this is the name of a budget, such as 'sports' or 'drinks'.
value: this is the balance of the budget. This will automatically decrease when expenses are claimed against the budget.

B.2 _budget_privileges

This table identifies users who are authorised to make claims against particular sub-budgets (if any are defined in _budget).

mysql> show fields from store_budget_privileges;
+-----------+------+------+-----+---------+-------+
| Field     | Type | Null | Key | Default | Extra |
+-----------+------+------+-----+---------+-------+
| suid      | text | YES  |     | NULL    |       |
| privilege | text | YES  |     | NULL    |       |
+-----------+------+------+-----+---------+-------+
2 rows in set (0.00 sec)

suid: name of the user.
privilege: name of the budget they are authorised on. A user may be authorised on more than one budget by having more than one line in this table.

B.3 _claims

This table contains claims for expenses or deposits. When an event is confirmed by the manager, an entry is created here for every ticket that was issued (this is a deposit claim). An entry is also created here for any expense claims made by users. If a claim is accepted by the treasurer, it becomes a transaction, i.e. it ends up in either the _expenses or _deposits table.

mysql> show fields from _claims;
+-------------------+------------+------+-----+---------+----------------+
| Field             | Type       | Null | Key | Default | Extra          |
+-------------------+------------+------+-----+---------+----------------+
| id                | int(11)    | NO   | PRI | NULL    | auto_increment |
| claimset_id       | int(11)    | YES  |     | NULL    |                |
| short_description | text       | YES  |     | NULL    |                |
| long_description  | text       | YES  |     | NULL    |                |
| expense_deposit   | tinyint(4) | YES  |     | NULL    |                |
| preferred_payment | tinyint(4) | YES  |     | NULL    |                |
| value             | float      | YES  |     | NULL    |                |
| suid              | text       | YES  |     | NULL    |                |
+-------------------+------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)

claimset_id: claims (e.g. started life as a ticket) are grouped into claimsets (e.g. started life as an event). This is the id of the claimset the claim belongs to, from the _claimsets table.
short_description: name of the claim
long_description: details of the claim
expense_deposit: if TRUE, the claim is a deposit (e.g. an event ticket, claimee OWES you). if FALSE, the claim is an expense (you OWE the claimee).
preferred_payment: the preferred mode of payment of the claimee. If TRUE, they would prefer cheque. If FALSE, they would prefer bill.
value: value of the claim, in units of currency.
suid: username of the claimee.

B.4 _claimsets

Claims (from the _claims table) are grouped into claimsets so that the treasurer can approve a large event with a single click, and not have to approve every individual ticket. Even stand-alone expense claims are grouped into a claimset, although for this case the claimset will contain only one claim.

mysql> show fields from _claimsets;
+----------+----------+------+-----+---------+----------------+
| Field    | Type     | Null | Key | Default | Extra          |
+----------+----------+------+-----+---------+----------------+
| id       | int(11)  | NO   | PRI | NULL    | auto_increment |
| suid     | text     | YES  |     | NULL    |                |
| category | text     | YES  |     | NULL    |                |
| message  | text     | YES  |     | NULL    |                |
| time     | datetime | YES  |     | NULL    |                |
| name     | text     | YES  |     | NULL    |                |
| value    | float    | YES  |     | NULL    |                |
| status   | int(11)  | YES  |     | NULL    |                |
| origin   | int(11)  | YES  |     | NULL    |                |
+----------+----------+------+-----+---------+----------------+
9 rows in set (0.00 sec)

suid: username of the person submitting the claimset. For a stand-alone expense claim, this will be the claimee. For an event, it will be whoever confirmed the event, usually the manager.
category: a budget category from the _budgets table that the claimset will be set against. E.g. 'sports' or 'drinks'.
message: a personal message for the treasurer. E.g. instructions on how to find the invoices that accompany an expense claim.
time: time the claimset was submitted.
name: name of the claimset.
value: total value of all the claims in the claimset.
status: 0 is unapproved. 1 is accepted. -1 is rejected.
origin: 0 means the claimset originated as an event. 1 means the claimset is a stand-alone expense claim.

B.5 _college_bills

Contains all the bills that have been periodically generated by the treasurer. An erroneously confirmed bill may be unconfirmed by setting the 'confirmed' boolean to FALSE.

mysql> show fields from _college_bills;
+--------------+------------+------+-----+---------+----------------+
| Field        | Type       | Null | Key | Default | Extra          |
+--------------+------------+------+-----+---------+----------------+
| id           | int(11)    | NO   | PRI | NULL    | auto_increment |
| name         | text       | YES  |     | NULL    |                |
| cleared      | tinyint(4) | YES  |     | NULL    |                |
| confirmed    | tinyint(4) | YES  |     | NULL    |                |
| when_cleared | datetime   | YES  |     | NULL    |                |
+--------------+------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

name: name of the bill. E.g. Feb-Mar 2012.
cleared: TRUE means the bill is cleared and the funds are in the bank account. Only matters when using the balance sheet feature of KEvMan.
confirmed: TRUE means the bill is confirmed. All transactions attributed to the bill become read-only (although this can be reversed at the SQL command line if need be, just set this parameter back to FALSE).
when_cleared: datetime the bill cleared. Only matters when using the balance sheet feature of KEvMan.

B.6 _deposits

Contains approved deposits (user OWES you).

mysql> show fields from _deposits;
+-----------------+------------+------+-----+---------+----------------+
| Field           | Type       | Null | Key | Default | Extra          |
+-----------------+------------+------+-----+---------+----------------+
| id              | int(11)    | NO   | PRI | NULL    | auto_increment |
| category        | text       | YES  |     | NULL    |                |
| description     | text       | YES  |     | NULL    |                |
| payment_method  | tinyint(4) | YES  |     | NULL    |                |
| college_bill_id | int(11)    | YES  |     | NULL    |                |
| value           | float      | YES  |     | NULL    |                |
| cleared         | tinyint(4) | YES  |     | NULL    |                |
| time            | datetime   | YES  |     | NULL    |                |
| name            | text       | YES  |     | NULL    |                |
| payment_details | text       | YES  |     | NULL    |                |
| when_cleared    | datetime   | YES  |     | NULL    |                |
| cheque_details  | text       | YES  |     | NULL    |                |
+-----------------+------------+------+-----+---------+----------------+
12 rows in set (0.00 sec)

category: the category of the deposit, e.g. 'sports' or 'drinks'.
description: long description of the deposit. May include id numbers of related deposits/expenses.
payment_method: FALSE means by bill. TRUE means by cheque.
college_bill_id: id number of the bill the deposit is linked to (unless it's a cheque). -1 means CURRENT_BILL, i.e. it hasn't yet been assigned to a bill.
value: value of the deposit in units of currency.
cleared: TRUE means the deposit has cleared, if the deposit is a cheque. If the deposit is a bill then the cleared state is overriden by that of the bill, i.e. the cleared field of the _college_bill table.
time: datetime the deposit was created.
name: name of the deposit.
payment_details: the username of the payee.
when_cleared: datetime the deposit cleared.
cheque_details: details of any cheques associated with the deposit.

B.7 _event_options

A manager may define multiple options for event tickets, each with their own price and description. E.g. 'vegetarian'.

mysql> show fields from _event_options;
+--------------+---------+------+-----+---------+----------------+
| Field        | Type    | Null | Key | Default | Extra          |
+--------------+---------+------+-----+---------+----------------+
| id           | int(11) | NO   | PRI | NULL    | auto_increment |
| event_id     | int(11) | YES  |     | NULL    |                |
| option_name  | text    | YES  |     | NULL    |                |
| option_price | float   | YES  |     | NULL    |                |
+--------------+---------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

event_id: the id of the event which is in the table _events. 
option_name: the name of the option, e.g. 'vegetarian'.
option_price: the price of the option in units of currency.

B.8 _events

The table where current and past events are stored.

mysql> show fields from _events;
+------------------+------------+------+-----+---------+----------------+
| Field            | Type       | Null | Key | Default | Extra          |
+------------------+------------+------+-----+---------+----------------+
| id               | int(11)    | NO   | PRI | NULL    | auto_increment |
| name             | text       | YES  |     | NULL    |                |
| description      | text       | YES  |     | NULL    |                |
| time             | datetime   | YES  |     | NULL    |                |
| ticketed         | tinyint(4) | YES  |     | NULL    |                |
| price            | float      | YES  |     | NULL    |                |
| tickets_per_user | int(11)    | YES  |     | NULL    |                |
| tickets_start    | datetime   | YES  |     | NULL    |                |
| tickets_end      | datetime   | YES  |     | NULL    |                |
| owner            | text       | YES  |     | NULL    |                |
| num_tickets      | int(11)    | YES  |     | NULL    |                |
| status           | int(11)    | YES  |     | NULL    |                |
| user_cancellable | tinyint(4) | YES  |     | NULL    |                |
| hidden           | tinyint(4) | YES  |     | NULL    |                |
| drupal_nid       | int(11)    | YES  |     | NULL    |                |
+------------------+------------+------+-----+---------+----------------+
15 rows in set (0.00 sec)

name: name of the event.
description: description of the event.
time: time the event will begin.
ticketed: TRUE means the event is ticketed. FALSE means an unticketed event - it will simply appear in the main page of KEvMan and will be confirmed automatically.
price: base price of the event, ONLY IF NO OPTIONS ARE DEFINED. If any entries are in the _event_options table for this event, the base price here is overriden and becomes irrelevant.
tickets_per_user: max tickets any single user can purchase.
tickets_start: datetime ticket sales begin.
tickets_end: datetime ticket sales end.
owner: username of the manager who created the event.
num_tickets: maximum total number of tickets that can be issued for this event (excludes manual tickets).
status: 0 means live. 1 means confirmed. -1 means cancelled.
user_cancellable: whether to allow ticket holders to cancel their tickets (only prior to tickets_end). If this is FALSE, users can only sell tickets to each other.
hidden: the event won't be displayed on the main page of KEvMan if this is TRUE.
drupal_nid: relic from an ill-fated attempt to synchronise with drupal events. Not in use.

B.9 _expenses

This table is identical in every way to _deposits, except here the you OWE the payee.

B.10 _interested

This is the 'interested' list that users can subscribe to if an event is fully booked. They will all be emailed if a ticket becomes available.

mysql> show fields from _interested;
+----------+---------+------+-----+---------+-------+
| Field    | Type    | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+-------+
| suid     | text    | YES  |     | NULL    |       |
| event_id | int(11) | YES  |     | NULL    |       |
+----------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

suid: username of the interested user.
event_id: id of the entry in _events corresponding to the event the user is interested in.

B.11 _mail_log

Logs all the mail the system has ever sent. Fields are self-explanatory. This table may safely be purged without affecting the operation of KEvMan.

mysql> show fields from _mail_log;
+----------------+----------+------+-----+---------+----------------+
| Field          | Type     | Null | Key | Default | Extra          |
+----------------+----------+------+-----+---------+----------------+
| id             | int(11)  | NO   | PRI | NULL    | auto_increment |
| header_to      | text     | YES  |     | NULL    |                |
| header_from    | text     | YES  |     | NULL    |                |
| header_bcc     | text     | YES  |     | NULL    |                |
| header_subject | text     | YES  |     | NULL    |                |
| contents       | text     | YES  |     | NULL    |                |
| time           | datetime | YES  |     | NULL    |                |
+----------------+----------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

B.12 _privileges

Defines administrator privileges.

mysql> show fields from _privileges;
+-----------+------+------+-----+---------+-------+
| Field     | Type | Null | Key | Default | Extra |
+-----------+------+------+-----+---------+-------+
| suid      | text | YES  |     | NULL    |       |
| privilege | text | YES  |     | NULL    |       |
+-----------+------+------+-----+---------+-------+
2 rows in set (0.00 sec)

suid: username of the user who is being assigned the privilege.
privilege: one out of 'manager', 'supermanager', 'admin', 'accounts' or 'treasurer', depending on the privilege to be assigned.

B.13 _purchases

Contains the tickets that have been bought.

mysql> show fields from _purchases;
+-----------------+----------+------+-----+---------+----------------+
| Field           | Type     | Null | Key | Default | Extra          |
+-----------------+----------+------+-----+---------+----------------+
| id              | int(11)  | NO   | PRI | NULL    | auto_increment |
| suid            | text     | YES  |     | NULL    |                |
| event_id        | int(11)  | YES  |     | NULL    |                |
| bought_at       | datetime | YES  |     | NULL    |                |
| event_option_id | int(11)  | YES  |     | NULL    |                |
| status          | int(11)  | YES  |     | NULL    |                |
| message         | text     | YES  |     | NULL    |                |
+-----------------+----------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

suid: username of the ticket holder.
event_id: id of the event in the table _events that the ticket was bought for.
bought_at: datetime the ticket was bought.
event_option_id: if the event had multiple options, e.g. 'vegetarian', the id of the option entry in _event_options that this ticketholder selected is given here. If no event options were defined this field should read -1.
status: 0 means live. -1 means a cancelled ticket for a live event. -2 means a cancelled ticket for an old confirmed or cancelled event. 1 means the user has put the ticket up for resale during a live event. 2 means a confirmed ticket for a confirmed event - the ticket should now have a corresponding entry in the _deposits table.
message: personal message from the ticket holder to the event manager.

B.14 _purchases_manual

Contains manual tickets for an event. Manual tickets are issued privately by the event manager.

mysql> show fields from _purchases_manual;
+-------------+---------+------+-----+---------+----------------+
| Field       | Type    | Null | Key | Default | Extra          |
+-------------+---------+------+-----+---------+----------------+
| id          | int(11) | NO   | PRI | NULL    | auto_increment |
| suid        | text    | YES  |     | NULL    |                |
| event_id    | int(11) | YES  |     | NULL    |                |
| description | text    | YES  |     | NULL    |                |
| price       | float   | YES  |     | NULL    |                |
+-------------+---------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

suid: username of the ticket holder.
event_id: id of the event in the table _events that the ticket was bought for.
description: reason why a manual ticket was issued.
price: price of the manual ticket in units of currency. Every manual ticket has its own price, set by the event manager.

B.15 _resell_waitinglist

Contains tickets that have been put up for sale by their owner. The only purpose of the resell waiting list is to ensure that tickets put up for sale are sold in the order they went up for sale.

mysql> show fields from _resell_waitinglist;
+-------------+---------+------+-----+---------+----------------+
| Field       | Type    | Null | Key | Default | Extra          |
+-------------+---------+------+-----+---------+----------------+
| id          | int(11) | NO   | PRI | NULL    | auto_increment |
| purchase_id | int(11) | YES  |     | NULL    |                |
| event_id    | int(11) | YES  |     | NULL    |                |
+-------------+---------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

purchase_id: id number of the ticket in the _purchases table.
event_id: id number of the event in the _events table.

B.16 _sql_log

Contains all the SQL queries every made by KEvMan, along with any errors that resulted. Fields are self-explanatory. This table may safely be purged without affecting the operation of KEvMan. 

mysql> show fields from _sql_log;
+------------+------------+------+-----+---------+----------------+
| Field      | Type       | Null | Key | Default | Extra          |
+------------+------------+------+-----+---------+----------------+
| id         | int(11)    | NO   | PRI | NULL    | auto_increment |
| time       | datetime   | YES  |     | NULL    |                |
| suid       | text       | YES  |     | NULL    |                |
| sql_query  | text       | YES  |     | NULL    |                |
| sql_error  | text       | YES  |     | NULL    |                |
| error_code | tinyint(4) | YES  |     | NULL    |                |
+------------+------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

B.17 _users

This is the user list - it contains the user names of everyone authorised to use KEvMan to purchase tickets.

mysql> show fields from _users;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| suid  | text | YES  |     | NULL    |       |
+-------+------+------+-----+---------+-------+
1 row in set (0.00 sec)