Key handling application

I need an application to allow us to control the keys which are used around our business. The application will likely be operated on an Android device, but would be useful to be a HTML5 based application and a local SQLite database, but written in such a way that if we ever decided to add a second terminal we can link the two together and use an online MySQL server.

Back end DB

Table for users: userID, UserName,UserCardNumber,UserValidFrom,UserValidTo
Table for keys: keyID, KeyNumber, KeyDescription,KeyRestricted, KeyOut, KeyOutDate,KeyLastReturned,KeyLastUser
Table for authorisations: authID, AuthUserID, AuthKeyID, AuthValidFrom, AuthValidTo
Table for audittrail, ID, AuditDateStamp, AuditDirection (in or out), AuditUserName,AuditUserCard, AuditKey

Core features:

1. a front end with fields for Staff Name, Key Number, and buttons for Scan ID, Scan Key, Take Key, Return Key, Clear, and a label to verify the name of the key that is being issued.

When someone approaches the tablet, they press a button for either Take Key or Return Key.

If take key is pressed, they can either enter their name, or press Scan ID. Scan ID will allow the user to scan a staff card using the in-built camera (2D barcode) and will populate their name if its' in the user database. (The users will not always be in the database - we sometimes have contractors borrowing keys on a one-off basis, so a not-found condition should not throw an error unless the key is restricted)

Key number can be typed in, or the ScanKey button pressed which will use the devices' camera to scan the QR code on a key's tag. Once a key number is entered or scanned, a caption should display the name of the key that has been scanned.

If the key's keyrestricted field is set to true, then a check should be carried out to ensure that the selected user has a record in the authorisations table for the respective key.

If the key is not restricted, or if the check above shows the user can take the key, the key's KeyOut field should be set to true, KeyOutDate set to a timestamp, KeyLastUser should be set to the current user. An audit trail record should be created with all of the relevant fields filled in.

If the user presses the "Return Key" button, a field to enter a key number and a scan Key button should be presented, along with a button "Check my keys"

Typing a key number should carry out a search of the key number and if the key is out should populate a caption with the key name and asking to confirm the key's return. Confirming the keys return should set KeyOut to false, KeyOutDate to null, KeyLastReturned to a timestamp, and KeyLastUser should remain the same. An audit trail record should be created of the transaction

Pressing the Scan key button should allow the scanning of a QR code with the device's camera. Scanning a recognised barcode should replicate the process above.

Pressing the "Check my keys" button should prompt for the user to type in their name or give the option to scan a staff ID card (2D barcode), and doing a search with either should present a table with all of the keys held by a user. Each key should feature a "return" checkbox, the key number, and the key description. The user can then tick each key they wish to mark as returned, and the system will carry out the process above for each key in turn to mark the keys as returned.


Back End:

The software will require a password protected admin page which allows:
Add, Edit, Delete of keys (including an option to view the audit trail for the selected key and a manual reset button to mark the key as returned)
Add, Edit, Delete of users, including a sub page to create key authorisations. (including the option to see an audit trail for the current user and any keys currently held by the user.

Ability to download the audit trail as a CSV file or PDF file
Ability to upload a CSV key list (to perform an edit/add routine)
Ability to upload a CSV user list (to perform an edit/add routine, editing existing users where they have a staff number or creating new users if they don't)

I hope this gives a good outline of what we need. Drop me a message if you've got any questions.