/**
* Export Appointment to CSV
*/
public function executeExportToCSV()
{
$start_date = new DateTime($this->getParameter('date_start'));
$start_date = $start_date->format('Y-m-d H:i:s');
$end_date = new DateTime($this->getParameter('date_end'));
$end_date = $end_date->modify('+1 day')->format('Y-m-d H:i:s');
$delimiter = $this->getParameter('delimiter', ',');
header('Content-Type: text/csv; charset=utf-8');
header('Content-Disposition: attachment; filename=Appointments.csv');
$header = array(__('Booking Time', 'bookly'), __('Staff Member', 'bookly'), __('Service', 'bookly'), __('Duration', 'bookly'), __('Price', 'bookly'), __('Customer', 'bookly'), __('Phone', 'bookly'), __('Email', 'bookly'));
$custom_fields = array();
$fields_data = json_decode(get_option('ab_custom_fields'));
foreach ($fields_data as $field_data) {
$custom_fields[$field_data->id] = '';
$header[] = $field_data->label;
}
$output = fopen('php://output', 'w');
fwrite($output, pack("CCC", 0xef, 0xbb, 0xbf));
fputcsv($output, $header, $delimiter);
$rows = AB_CustomerAppointment::query()->select('r.id,
r.number_of_persons,
r.coupon_discount,
r.coupon_deduction,
st.full_name AS staff_name,
s.title AS service_title,
s.duration AS service_duration,
c.name AS customer_name,
c.phone AS customer_phone,
c.email AS customer_email,
ss.price,
a.start_date')->leftJoin('AB_Appointment', 'a', 'a.id = r.appointment_id')->leftJoin('AB_Service', 's', 's.id = a.service_id')->leftJoin('AB_Staff', 'st', 'st.id = a.staff_id')->leftJoin('AB_Customer', 'c', 'c.id = r.customer_id')->leftJoin('AB_StaffService', 'ss', 'ss.staff_id = st.id AND ss.service_id = s.id')->whereBetween('a.start_date', $start_date, $end_date)->sortBy('a.start_date')->order(AB_Query::ORDER_DESCENDING)->fetchArray();
foreach ($rows as $row) {
if ($row['coupon_discount'] or $row['coupon_deduction']) {
$coupon = new AB_Coupon();
$coupon->set('discount', $row['coupon_discount']);
$coupon->set('deduction', $row['coupon_deduction']);
$row['price'] = $coupon->apply($row['price']);
}
$row['price'] *= $row['number_of_persons'];
$row_data = array($row['start_date'], $row['staff_name'], $row['service_title'], AB_Service::durationToString($row['service_duration']), AB_Utils::formatPrice($row['price']), $row['customer_name'], $row['customer_phone'], $row['customer_email']);
$customer_appointment = new AB_CustomerAppointment();
$customer_appointment->load($row['id']);
foreach ($customer_appointment->getCustomFields() as $custom_field) {
$custom_fields[$custom_field['id']] = $custom_field['value'];
}
fputcsv($output, array_merge($row_data, $custom_fields), $delimiter);
$custom_fields = array_map(function () {
return '';
}, $custom_fields);
}
fclose($output);
exit;
}
/**
* @param AB_Notification $notification
*/
public function processNotification(AB_Notification $notification)
{
/** @var $wpdb wpdb */
global $wpdb;
$date = new DateTime();
switch ($notification->get('type')) {
case 'staff_agenda':
if ($date->format('H') >= 18) {
$rows = $wpdb->get_results('SELECT
`a`.*,
`c`.`name` AS `customer_name`,
`s`.`title` AS `service_title`,
`st`.`email` AS `staff_email`,
`st`.`phone` AS `staff_phone`,
`st`.`full_name` AS `staff_name`
FROM `' . AB_CustomerAppointment::getTableName() . '` `ca`
LEFT JOIN `' . AB_Appointment::getTableName() . '` `a` ON `a`.`id` = `ca`.`appointment_id`
LEFT JOIN `' . AB_Customer::getTableName() . '` `c` ON `c`.`id` = `ca`.`customer_id`
LEFT JOIN `' . AB_Service::getTableName() . '` `s` ON `s`.`id` = `a`.`service_id`
LEFT JOIN `' . AB_Staff::getTableName() . '` `st` ON `st`.`id` = `a`.`staff_id`
LEFT JOIN `' . AB_StaffService::getTableName() . '` `ss` ON `ss`.`staff_id` = `a`.`staff_id` AND `ss`.`service_id` = `a`.`service_id`
WHERE DATE(DATE_ADD("' . $this->mysql_now . '", INTERVAL 1 DAY)) = DATE(`a`.`start_date`) AND NOT EXISTS (
SELECT * FROM `' . AB_SentNotification::getTableName() . '` `sn` WHERE
DATE(`sn`.`created`) = DATE("' . $this->mysql_now . '") AND
`sn`.`gateway` = "' . $notification->get('gateway') . '" AND
`sn`.`type` = "staff_agenda" AND
`sn`.`staff_id` = `a`.`staff_id`
)');
if ($rows) {
$appointments = array();
foreach ($rows as $row) {
$appointments[$row->staff_id][] = $row;
}
foreach ($appointments as $staff_id => $collection) {
$sent = false;
$staff_email = null;
$staff_phone = null;
$table = $notification->get('gateway') == 'email' ? '<table>%s</table>' : '%s';
$tr = $notification->get('gateway') == 'email' ? '<tr><td>%s</td><td>%s</td><td>%s</td></tr>' : "%s %s %s\n";
$agenda = '';
foreach ($collection as $appointment) {
$startDate = new DateTime($appointment->start_date);
$endDate = new DateTime($appointment->end_date);
$agenda .= sprintf($tr, $startDate->format('H:i') . '-' . $endDate->format('H:i'), $appointment->service_title, $appointment->customer_name);
$staff_email = $appointment->staff_email;
$staff_phone = $appointment->staff_phone;
}
$agenda = sprintf($table, $agenda);
if ($staff_email || $staff_phone) {
$replacement = new AB_NotificationCodes();
$replacement->set('next_day_agenda', $agenda);
$replacement->set('appointment_datetime', $appointment->start_date);
$replacement->set('staff_name', $appointment->staff_name);
if ($notification->get('gateway') == 'email' && $staff_email) {
$message = $replacement->replace($notification->get('message'));
$subject = $replacement->replace($notification->get('subject'));
// Send email.
$sent = wp_mail($staff_email, $subject, wpautop($message), AB_Utils::getEmailHeaders());
} else {
if ($notification->get('gateway') == 'sms' && $staff_phone) {
$message = $replacement->replace($notification->get('message'), $notification->get('gateway'));
// Send sms.
$sent = $this->sms->sendSms($staff_phone, $message);
}
}
}
if ($sent) {
$sent_notification = new AB_SentNotification();
$sent_notification->set('staff_id', $staff_id);
$sent_notification->set('gateway', $notification->get('gateway'));
$sent_notification->set('type', 'staff_agenda');
$sent_notification->set('created', $date->format('Y-m-d H:i:s'));
$sent_notification->save();
}
}
}
}
break;
case 'client_follow_up':
if ($date->format('H') >= 21) {
$rows = $wpdb->get_results('SELECT
`a`.*,
`ca`.*
FROM `' . AB_CustomerAppointment::getTableName() . '` `ca`
LEFT JOIN `' . AB_Appointment::getTableName() . '` `a` ON `a`.`id` = `ca`.`appointment_id`
WHERE DATE("' . $this->mysql_now . '") = DATE(`a`.`start_date`) AND NOT EXISTS (
SELECT * FROM `' . AB_SentNotification::getTableName() . '` `sn` WHERE
DATE(`sn`.`created`) = DATE("' . $this->mysql_now . '") AND
`sn`.`gateway` = "' . $notification->get('gateway') . '" AND
`sn`.`type` = "client_follow_up" AND
`sn`.`customer_appointment_id` = `ca`.`id`
)', ARRAY_A);
if ($rows) {
foreach ($rows as $row) {
$customer_appointment = new AB_CustomerAppointment();
$customer_appointment->load($row['id']);
if (AB_NotificationSender::sendFromCron(AB_NotificationSender::CRON_FOLLOW_UP_EMAIL, $notification, $customer_appointment)) {
$sent_notification = new AB_SentNotification();
$sent_notification->set('customer_appointment_id', $customer_appointment->get('id'));
$sent_notification->set('gateway', $notification->get('gateway'));
$sent_notification->set('type', 'client_follow_up');
$sent_notification->set('created', $date->format('Y-m-d H:i:s'));
$sent_notification->save();
}
}
}
}
break;
case 'client_reminder':
if ($date->format('H') >= 18) {
$rows = $wpdb->get_results('SELECT
`ca`.`id`
FROM `' . AB_CustomerAppointment::getTableName() . '` `ca`
LEFT JOIN `' . AB_Appointment::getTableName() . '` `a` ON `a`.`id` = `ca`.`appointment_id`
WHERE DATE(DATE_ADD("' . $this->mysql_now . '", INTERVAL 1 DAY)) = DATE(`a`.`start_date`) AND NOT EXISTS (
SELECT * FROM `' . AB_SentNotification::getTableName() . '` `sn` WHERE
DATE(`sn`.`created`) = DATE("' . $this->mysql_now . '") AND
`sn`.`gateway` = "' . $notification->get('gateway') . '" AND
`sn`.`type` = "client_reminder" AND
`sn`.`customer_appointment_id` = `ca`.`id`
)', ARRAY_A);
if ($rows) {
foreach ($rows as $row) {
$customer_appointment = new AB_CustomerAppointment();
$customer_appointment->load($row['id']);
if (AB_NotificationSender::sendFromCron(AB_NotificationSender::CRON_NEXT_DAY_APPOINTMENT, $notification, $customer_appointment)) {
$sent_notification = new AB_SentNotification();
$sent_notification->set('customer_appointment_id', $customer_appointment->get('id'));
$sent_notification->set('gateway', $notification->get('gateway'));
$sent_notification->set('type', 'client_reminder');
$sent_notification->set('created', $date->format('Y-m-d H:i:s'));
$sent_notification->save();
}
}
}
}
break;
}
}
