Generating Usage Reports

Scheduler reports =

To get total reservations on all machines in minutes for certain period:

SELECT 
sum((reservations.end_date   + (reservations.endTime * 60))-(reservations.start_date + (reservations.startTime * 60)))/60 duration
FROM reservations
WHERE unix_timestamp('2000-01-01') <=(reservations.start_date + (reservations.startTime * 60))  AND
unix_timestamp('2016-03-31') >= (reservations.end_date   + (reservations.endTime * 60))
AND is_pending = 0;

To get total user reservations in minutes for certain period per machine:

SELECT machines.name resource_name,
sum((reservations.end_date   + (reservations.endTime * 60))-(reservations.start_date + (reservations.startTime * 60)))/60 duration
FROM reservations
LEFT JOIN reservation_users ON reservation_users.resid = reservations.resid
LEFT JOIN machines         ON reservations.machid = machines.machid
WHERE unix_timestamp('2000-01-01') <=(reservations.start_date + (reservations.startTime * 60))  AND
unix_timestamp('2016-03-31') >= (reservations.end_date   + (reservations.endTime * 60))
AND is_pending = 0
GROUP BY resource_name
ORDER BY duration desc

To get total resource reservations in minutes for certain period per machine. This is slightly different than above because user can invite other user(s) to his reservation.

SELECT machines.name resource_name,
sum((reservations.end_date   + (reservations.endTime * 60))-(reservations.start_date + (reservations.startTime * 60)))/60 duration
FROM reservations
LEFT JOIN machines         ON reservations.machid = machines.machid
WHERE unix_timestamp('2016-03-01') <=(reservations.start_date + (reservations.startTime * 60))  AND
unix_timestamp('2016-03-31') >= (reservations.end_date   + (reservations.endTime * 60))
AND is_pending = 0
GROUP BY resource_name
ORDER BY resource_name;