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