Notice: ABPro's Stripe payment processor uses the Legacy Checkout and is not SCA compatible. read more..

  • Home
  • How-To
  • Use MySQL triggers to track staff activity

How-To

Use MySQL triggers to track staff activity

You can add a level of auditing to ABPro by using MySQL UPDATE triggers to store before/after data of updates to bookings. The idea here is a non-Joomla approach, strictly using MySQL and offers no screens in ABPro to view audit data. You could use phpMyAdmin to look at the data or one of the many SQL query/report components available for Joomla.

 

In the scripts below you will need to change the table prefixes from jos_ to whatever table prefix your Joomla is using.

 

Step 1

Create a table to store audit/history data.

Execute this command in phpMyAdmin's SQL console screen..

DROP TABLE IF EXISTS `jos_sv_apptpro2_requests_history`;
CREATE TABLE `jos_sv_apptpro2_requests_history` (
`id_requests_history` int(11) NOT NULL AUTO_INCREMENT,
`id_requests` int(11) NOT NULL,
`update_date_time` datetime DEFAULT NULL,
`user_id` int(11) DEFAULT NULL,

`operator_id` int(11) DEFAULT NULL,
`name` varchar(50) NOT NULL DEFAULT '',
`phone` varchar(20) DEFAULT NULL,
`email` varchar(80) DEFAULT NULL,
`resource` varchar(50) NOT NULL DEFAULT '',
`category` varchar(50) DEFAULT NULL,
`startdate` date DEFAULT NULL,
`starttime` time DEFAULT NULL,
`enddate` date DEFAULT NULL,
`endtime` time DEFAULT NULL,
`admin_comment` varchar(255) DEFAULT NULL,
`request_status` varchar(20) DEFAULT 'new',
`payment_status` varchar(20) DEFAULT 'pending',
`calendar_comment` varchar(200) NOT NULL DEFAULT '',
`cancellation_id` varchar(255) DEFAULT NULL,
`service` varchar(50) DEFAULT NULL,
`sms_reminders` varchar(10) DEFAULT 'No',
`sms_phone` varchar(50) DEFAULT NULL,
`sms_dial_code` varchar(4) DEFAULT '1',
`booking_total` decimal(10,2) DEFAULT '0.00',
`booking_deposit` decimal(10,2) DEFAULT NULL,
`booking_due` decimal(10,2) DEFAULT '0.00',
`coupon_code` varchar(255) DEFAULT NULL,
`booked_seats` int(11) DEFAULT '1',
`credit_used` float(10,2) DEFAULT '0.00',
`payment_processor_used` varchar(30) DEFAULT 'None',
`manual_payment_collected` float(10,2) DEFAULT '0.00',
`last_change_operator` int(11) DEFAULT NULL,
PRIMARY KEY (`id_requests_history`),
KEY `startdate` (`startdate`)

Step 2

Create a TRIGGER to store data on update.

Because Joomla updates the table with check-in/check-out every time it is opened, the trigger looks at only changes to pertinent data to decide if an entry needs to be added to the history table.

DROP TRIGGER IF EXISTS `booking_history`;
CREATE
TRIGGER `booking_history` BEFORE UPDATE
ON `jos_sv_apptpro2_requests`
FOR EACH ROW
BEGIN
IF (NEW.name <> OLD.name)
OR (NEW.email <> OLD.email)
OR (NEW.phone <> OLD.phone)
OR (NEW.resource <> OLD.resource)
OR (NEW.startdate <> OLD.startdate)
OR (NEW.starttime <> OLD.starttime)
OR (NEW.endtime <> OLD.endtime)
OR (NEW.request_status <> OLD.request_status)
OR (NEW.payment_status <> OLD.payment_status)
OR (NEW.service <> OLD.service)
OR (NEW.booking_total <> OLD.booking_total)
OR (NEW.booking_deposit <> OLD.booking_deposit)
OR (NEW.booking_due <> OLD.booking_due)
OR (NEW.manual_payment_collected <> OLD.manual_payment_collected)
OR (NEW.admin_comment <> OLD.admin_comment)
OR (NEW.booked_seats <> OLD.booked_seats)
THEN
INSERT jos_sv_apptpro2_requests_history (
id_requests,
update_date_time,
user_id,
operator_id,
name,
phone,
email,
resource,
category,
startdate,
starttime,
enddate,
endtime,
admin_comment,
request_status,
payment_status,
calendar_comment,
cancellation_id,
service,
sms_reminders,
sms_phone,
sms_dial_code,
booking_total,
booking_deposit,
booking_due,
coupon_code,
booked_seats,
credit_used,
payment_processor_used,
manual_payment_collected,
last_change_operator
)
VALUES(
OLD.id_requests,
NOW(),
OLD.user_id,
OLD.operator_id,
OLD.name,
OLD.phone,
OLD.email,
OLD.resource,
OLD.category,
OLD.startdate,
OLD.starttime,
OLD.enddate,
OLD.endtime,
OLD.admin_comment,
OLD.request_status,
OLD.payment_status,
OLD.calendar_comment,
OLD.cancellation_id,
OLD.service,
OLD.sms_reminders,
OLD.sms_phone,
OLD.sms_dial_code,
OLD.booking_total,
OLD.booking_deposit,
OLD.booking_due,
OLD.coupon_code,
OLD.booked_seats,
OLD.credit_used,
OLD.payment_processor_used,
OLD.manual_payment_collected,
OLD.last_change_operator
);
END IF;
END;

 

Now when staff make a change to a booking, a new row will be added to the jos_sv_apptpro2_requests_history table showing the values before the changes.

Notes:

  1. This only stores changes to core booking data. Changes to UDFs, Extras and Seats would need similar triggers added for those tables.
  2. This does not store before/after, it stores before update so you can compare history data to current values.