A Bookly WordPress Plugin Birthdays Report

One of our clients uses the Bookly WordPress Plugin, to allow their clients to make on-line bookings. This plugin records a small amount of client information, including the client’s birth date. Our client asked for a way to extract upcoming birthdays from the system, so that they could send a Happy Birthday greeting to their client.

We delved into the depths of the database, and created a query which returns the information for clients having a birthday in the next 14 days.

We teamed this query together with the Exports and Reports WordPress Plugin, which creates a report on screen for their system Administrators, along with exportable reports in CSV, TSV, XML, JSON, and custom delimiter separated formats.

For the technically inclined, the database query follows:

select cus.full_name as cus_name, cus.phone,cus.email,
DATE_FORMAT(cus.birthday, "%d/%m/%Y") as birthday,
IFNULL(vw.type,'-') as type,
IFNULL(vw.gateway,'-') as gateway,
IFNULL(vw.created,'-') as created
from `xxxx_ab_customers` `cus` 
left join 
(
 select sn.id,sn.ref_id,no.type,no.gateway,IFNULL(sn.created,'---') as created 
 from `xxxx_ab_sent_notifications` `sn` 
 inner join `xxxx_ab_notifications` `no` on ( `sn`.`notification_id` = `no`.`id` )
 where `no`.`type` = 'client_birthday_greeting'
) vw on `cus`.`id` = `vw`.`ref_id`
where `cus`.`birthday` IS NOT NULL 
AND DATE_FORMAT(`cus`.`birthday`, "%m-%d") <= DATE_FORMAT(date_add(curdate(),INTERVAL 14 DAY), "%m-%d")
order by DATE_FORMAT(`cus`.`birthday`, "%m-%d") DESC