Harris Retail Services Ltd

29 March 2024
Home    Contact    Webmail Service    Forums    Remote Support

search for  

1 New Messages


Home

Details








Manual


Program Releases






Doctors Out of Hours Booking System


SQL Commands


Sample Pages






Contact us
Email & Telephone

Demonstration System
Run the Demonstration

Get Firefox!

 

Some Useful SQL Commands

 
 

Introduction

The SQL language is used to interrogate the OOH system database directly. This allows administrators to retrieve any information required. The following information provides various useful examples of using SQL to retrieve information from the OOH system.

Find out what fields are available in a table

describe userdetail

This returns a list of the field names and details about the fields.

Back to top

Get the login ID for a particular user

select id, sname, fname from userdetail
where sname =
'Smith'

This will return the login id, firstname and surname of any user with the surname “Smith”.

Back to top

List log records for a particular user either logging in or logging out

select * from log where user = 'clareb' 
and (event = 'Login_OK'
or event = 'Logout')

The brackets “(” and “)” are important in this query as we want records which have either [user = ‘clareb’ AND event = “Login_OK” ] or [user = ‘clareb’ AND event = ‘Logout’].

The same result could be achieved with the query:

select * from log where user = 'clareb' 
and event in
('Login_OK','Logout')

Back to top

Find out who authorised a particular user

select * from log where event = 'Authorise' 
and details like
'%louiseb%'

In the above query, we are looking for “Authorise” events, and we are also looking for the UserID – but the UserID is held in the text of the details. Using the word “like” allows a partial match and the “%” matches any sequence of characters; in this case before or after the id we are looking for.

The query:

 

select * from log where event = 'Authorise' 
and
details like '%alanwatt%' order by logid

returns a number of lines as the authority for this user was changed a number of times – the output is sorted into the order the events occurred and therefore each change to the authority can be tracked and identified.

Back to top

Create a mail list file for merging with a word processor

The following query will produce a mailmerge list of nurses on the system – by using the CSV download option, the file can be saved on your local PC and used to merge with Word or another word processor or used in a program such as Excel or Access:
select users.id, title, fname, sname, add1, add2, town,
county, postcode
from users, userdetail
where users.id =
userdetail.id
and users.permissions = 'S'

The above is a multi-line query (although it could be typed into a single line).

If, instead of nurses, a list of doctors is required, then the following query could be used:

select users.id, title, fname, sname, add1, add2,
town, county, postcode
from users, userdetail
where users.id = userdetail.id
and users.permissions in ('U', 'O', 'G', 'D')

The permissions used on the system are as follows:

 

N	-	None
U - Doctor
O - Restricted Doctor
G - Semi-restricted Doctor
D - Director Doctor
E - Training Doctor
H - Registrar Doctor
S - Nurse
W - Nurse Practitioner
J - Nurse Director
A - Administrator
T - Administrative assistant
V - View Only
B - Bulk Doctor
R - Bulk Nurse
C - Call Handler
I - Driver
X - Disabled

Back to top

Create a Telephone List (with email addresses)

The following query uses CONCAT to join several fields together in producing the output:

 

select concat(title, " ", fname, " ",
sname) as name, telephone,
mobile, email
from userdetail
order
by sname

Back to top

List all bookings made by a particular user on a particular day

The bookings table lists details of all bookings on the system. In addition to the booking itself, the table holds information about who made the booking and when it was made. So:

 

select * from bookings, users
where
bookings.booked_by = users.id
and (users.permissions = 'T' or
users.permissions = 'A')
and users.id = 'steevh')

will return all bookings made by an administrator on behalf of the user “steevh”. The “created” field holds the date and the time, so the query:

 

select * from bookings 
where created between
'2004-12-29' and '2004-12-30'

lists bookings made from 00:00:00 on 29th December to 00:00:00 on the 30th December or effectively all bookings actually made on the 29th December.

Back to top

When did a particular administrator log into the system on a given day

The query:

 

select * from log 
where event = 'Login_OK'
and
user = 'clareb'
and etime between '2004-12-20' and '2004-12-21'

Shows all logins made to the system by ‘cleareb’ on the 20th December. In this case it is not possible to say:

 

select * from log 
where event = 'Login_OK'
and user = 'clareb'
and etime = '2004-12-20'

because the "etime" field specified a date and a time (it is actually the number of seconds since 1st January 1970), and "etime = '2004-12-20'" is the same as saying "etime = '2004-12-20 00:00:00'" which would only match log recors created at exactly midnight. To circumvent this problem, a date is given instead.

Back to top

List new users awaiting authorisation

select concat(title, " ", fname, " ", sname) as
name, created
from userdetail, users
where userdetail.id = users.id
and users.permissions = 'N'
order by created

The above query lists users who have not yet been given permission to use the system. It is sorted into the order of the date that the user registered on the system.

Back to top

List all shift sales

When a shift that is for sale is taken by another user, that event is logged to the Event Log. The following query will list all events when a "For Sale" shift is acquired by anothe user:

 

select * from log where event = 'Swapped'

Back to top

User Messages

Finding out when users confirmed acceptance of messages can be done using the SQL command. When sending a message, the system assigns the message and ID (mid), which you can later use to find out when the message has been read.

For instance, to see when each user has read message ID 10, you could use:

 

select * from user_messages where mid = '10'

If you wish to see the texts of past messages (perhaps to copy the text to use again), and to retrieve the message ID's, use:

 

select * from messages
However, if you have sent alot of message on the system, it may be appropriate to limit the number of messages returned by restricting the results to a range of messages, using something like:

 

select * from messages where mid between 10 and 20

Back to top

GMC or Professional Insurance Expiry

MySQL allows calculations  on dates to be performed within the SQL command, and certain functions can also be called. For instance, now() will return the date and time. Using UNIX_TIMESTAMP converts the date and time to seconds since midnight on the 01/01/1970, which enables calculations on dates.

The following will list all GMC registrations which have either already expired or that will expire in the next 60 days:


SELECT concat(title, " ", fname, " ", sname) as "Name", 
    gmc_no  AS "GMC number", 
    DATE_FORMAT(gmc_expiry, "%d/%m/%Y") as "GMC Expiry" 
    FROM userdetail, users 
    WHERE users.id = userdetail.id 
    AND Permissions IN ('D', 'E', 'H', 'U','O') 
    AND UNIX_TIMESTAMP(gmc_expiry) < 
                         (UNIX_TIMESTAMP(now()) + 60 * 60 * 60 * 24) 
    ORDER BY gmc_expiry 

In the above, several functions have been used to either format the date or to perform date calculations. 60 days is calculated as "60 * 60 * 60 * 24" seconds.

A similar SQL command can be used to select Professional Insurance Expiry:


SELECT concat(title, " ", fname, " ", sname) as "Name", 
    pi_name  AS "Insurance Provider", 
    DATE_FORMAT(pi_expiry, "%d/%m/%Y") as "PI Expiry" 
    FROM userdetail, users 
    WHERE users.id = userdetail.id 
    AND Permissions IN ('D', 'E', 'H', 'U','O') 
    AND UNIX_TIMESTAMP(pi_expiry) <  
                         (UNIX_TIMESTAMP(now()) + 60 * 60 * 60 * 24) 
    ORDER BY pi_expiry 

More about Log Records

To display a list of event types, use:

 

select distinct event from log

Back to top

Further Reading

More information is available at:
MySQL Manual
Introduction to SQL
 
 

 

Website Copyright © 2004-2012 Harris Retail Services Ltd. Parts © iStockPhotos
All rights reserved.
Connect from: 44.223.42.120
Last updated: 28 May 2009