/**
* Get appointments for FullCalendar.
*
* @param DateTime $start_date
* @param DateTime $end_date
*
* @return array
*/
public function getAppointmentsForFC(DateTime $start_date, DateTime $end_date)
{
if (!$this->isLoaded()) {
return array();
}
$appointments = AB_Appointment::query('a')->select('a.id, a.start_date, a.end_date,
s.title AS service_title, s.color AS service_color,
ss.capacity AS max_capacity,
SUM(ca.number_of_persons) AS total_number_of_persons, ca.custom_fields,
c.name AS customer_name, c.phone AS customer_phone, c.email AS customer_email')->leftJoin('AB_CustomerAppointment', 'ca', 'ca.appointment_id = a.id')->leftJoin('AB_Customer', 'c', 'c.id = ca.customer_id')->leftJoin('AB_Service', 's', 's.id = a.service_id')->leftJoin('AB_Staff', 'st', 'st.id = a.staff_id')->leftJoin('AB_StaffService', 'ss', 'ss.staff_id = a.staff_id AND ss.service_id = a.service_id')->where('st.id', $this->get('id'))->whereBetween('DATE(a.start_date)', $start_date->format('Y-m-d'), $end_date->format('Y-m-d'))->groupBy('a.start_date')->fetchArray();
foreach ($appointments as $key => $appointment) {
$desc = array();
if ($appointment['max_capacity'] == 1) {
foreach (array('customer_name', 'customer_phone', 'customer_email') as $data_entry) {
if ($appointment[$data_entry]) {
$desc[] = '<div class="fc-employee">' . esc_html($appointment[$data_entry]) . '</div>';
}
}
$ca = new AB_CustomerAppointment();
$ca->set('custom_fields', $appointment['custom_fields']);
foreach ($ca->getCustomFields() as $custom_field) {
$desc[] = sprintf('<div class="fc-notes">%s : %s</div>', wp_strip_all_tags($custom_field['label']), esc_html($custom_field['value']));
}
} else {
$desc[] = sprintf('<div class="fc-notes">%s %s</div>', __('Signed up', 'bookly'), $appointment['total_number_of_persons']);
$desc[] = sprintf('<div class="fc-notes">%s %s</div>', __('Capacity', 'bookly'), $appointment['max_capacity']);
}
$appointments[$key] = array('id' => $appointment['id'], 'start' => $appointment['start_date'], 'end' => $appointment['end_date'], 'title' => $appointment['service_title'] ? esc_html($appointment['service_title']) : __('Untitled', 'bookly'), 'desc' => implode('', $desc), 'color' => $appointment['service_color'], 'staffId' => $this->get('id'));
}
return $appointments;
}
/**
* Delete customer appointment.
*/
public function executeDeleteCustomerAppointment()
{
$customer_appointment = new AB_CustomerAppointment();
$customer_appointment->load($this->getParameter('id'));
$appointment = new AB_Appointment();
$appointment->load($customer_appointment->get('appointment_id'));
$customer_appointment->delete();
// Delete appointment, if there aren't customers.
$count = AB_CustomerAppointment::query()->where('appointment_id', $customer_appointment->get('appointment_id'))->count();
if (!$count) {
$appointment->delete();
} else {
$appointment->handleGoogleCalendar();
}
wp_send_json_success();
}
/**
* Get array with appointments data for customer profile.
*
* @return array
*/
public function getAppointmentsForProfile()
{
$records = array();
if ($this->get('id')) {
$result = $this->wpdb->get_results($this->wpdb->prepare('SELECT `c`.`name` `category`,
`sv`.`title` `service`,
`s`.`full_name` `staff`,
`a`.`start_date` `start_date`,
`ss`.`price` `price`,
`ca`.`number_of_persons` `number_of_persons`,
`ca`.`coupon_discount` `coupon_discount`,
`ca`.`coupon_deduction` `coupon_deduction`,
`ca`.`time_zone_offset` `time_zone_offset`,
`ca`.`token` `token`
FROM `' . AB_Appointment::getTableName() . '` `a`
LEFT JOIN `' . AB_Staff::getTableName() . '` `s` ON `s`.`id` = `a`.`staff_id`
LEFT JOIN `' . AB_Service::getTableName() . '` `sv` ON `sv`.`id` = `a`.`service_id`
LEFT JOIN `' . AB_Category::getTableName() . '` `c` ON `c`.`id` = `sv`.`category_id`
LEFT JOIN `' . AB_StaffService::getTableName() . '` `ss` ON `ss`.`staff_id` = `a`.`staff_id` AND `ss`.`service_id` = `a`.`service_id`
INNER JOIN `' . AB_CustomerAppointment::getTableName() . '` `ca` ON `ca`.`appointment_id` = `a`.`id` AND `ca`.`customer_id` = %d', $this->get('id')), ARRAY_A);
if ($result) {
foreach ($result as $row) {
if ($row['time_zone_offset'] !== null) {
$row['start_date'] = AB_DateTimeUtils::applyTimeZoneOffset($row['start_date'], $row['time_zone_offset']);
}
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'];
unset($row['time_zone_offset'], $row['coupon_discount'], $row['coupon_deduction'], $row['number_of_persons']);
$records[] = $row;
}
}
}
return $records;
}
/**
* @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;
}
}
/**
* Save all data and create appointment.
*
* @return AB_Appointment
*/
public function save()
{
$user_id = get_current_user_id();
$customer = new AB_Customer();
if ($user_id) {
// Try to find customer by WP user ID.
$customer->loadBy(array('wp_user_id' => $user_id));
}
if (!$customer->isLoaded()) {
// If customer with such name & e-mail exists, append new booking to him, otherwise - create new customer
$customer->loadBy(array('name' => $this->get('name'), 'email' => $this->get('email')));
}
$customer->set('name', $this->get('name'));
$customer->set('email', $this->get('email'));
$customer->set('phone', $this->get('phone'));
if (get_option('ab_settings_create_account', 0) && !$customer->get('wp_user_id')) {
// Create WP user and link it to customer.
$customer->setWPUser($user_id ?: null);
}
$customer->save();
$this->customer_id = $customer->get('id');
$service = $this->getService();
/**
* Get appointment, with same params.
* If it is -> create connection to this appointment,
* otherwise create appointment and connect customer to new appointment
*/
$appointment = new AB_Appointment();
$appointment->loadBy(array('staff_id' => $this->getStaffId(), 'service_id' => $this->get('service_id'), 'start_date' => $this->get('appointment_datetime')));
if ($appointment->isLoaded() == false) {
$appointment->set('staff_id', $this->getStaffId());
$appointment->set('service_id', $this->get('service_id'));
$appointment->set('start_date', $this->get('appointment_datetime'));
$endDate = new DateTime($this->get('appointment_datetime'));
$di = "+ {$service->get('duration')} sec";
$endDate->modify($di);
$appointment->set('end_date', $endDate->format('Y-m-d H:i:s'));
$appointment->save();
}
$customer_appointment = new AB_CustomerAppointment();
$customer_appointment->loadBy(array('customer_id' => $customer->get('id'), 'appointment_id' => $appointment->get('id')));
if ($customer_appointment->isLoaded()) {
// Add number of persons to existing booking.
$customer_appointment->set('number_of_persons', $customer_appointment->get('number_of_persons') + $this->get('number_of_persons'));
} else {
$customer_appointment->set('customer_id', $customer->get('id'));
$customer_appointment->set('appointment_id', $appointment->get('id'));
$customer_appointment->set('number_of_persons', $this->get('number_of_persons'));
}
$customer_appointment->set('custom_fields', $this->get('custom_fields'));
$customer_appointment->set('time_zone_offset', $this->get('time_zone_offset'));
$coupon = $this->getCoupon();
if ($coupon) {
$customer_appointment->set('coupon_code', $coupon->get('code'));
$customer_appointment->set('coupon_discount', $coupon->get('discount'));
$customer_appointment->set('coupon_deduction', $coupon->get('deduction'));
$coupon->claim();
$coupon->save();
}
$customer_appointment->save();
// Create fake payment record for 100% discount coupons.
if ($coupon && $coupon->get('discount') == '100') {
$payment = new AB_Payment();
$payment->set('total', '0.00');
$payment->set('type', 'coupon');
$payment->set('created', current_time('mysql'));
$payment->set('customer_appointment_id', $customer_appointment->get('id'));
$payment->save();
}
// Google Calendar.
$appointment->handleGoogleCalendar();
// Send email notifications.
AB_NotificationSender::send(AB_NotificationSender::INSTANT_NEW_APPOINTMENT, $customer_appointment);
return $appointment;
}
/**
* Cancel Appointment using token.
*/
public function executeCancelAppointment()
{
$customer_appointment = new AB_CustomerAppointment();
if ($customer_appointment->loadBy(array('token' => $this->getParameter('token')))) {
// Send email.
AB_NotificationSender::send(AB_NotificationSender::INSTANT_CANCELLED_APPOINTMENT, $customer_appointment);
$customer_appointment->delete();
$appointment = new AB_Appointment();
$appointment->load($customer_appointment->get('appointment_id'));
// Delete appointment, if there aren't customers.
$count = AB_CustomerAppointment::query('ca')->where('ca.appointment_id', $customer_appointment->get('appointment_id'))->count();
if (!$count) {
$appointment->delete();
} else {
$appointment->handleGoogleCalendar();
}
if ($this->url = get_option('ab_settings_cancel_page_url')) {
wp_redirect($this->url);
$this->render('cancel_appointment');
exit(0);
}
}
$this->url = home_url();
if (isset($_SERVER['HTTP_REFERER'])) {
if (parse_url($_SERVER['HTTP_REFERER'], PHP_URL_HOST) == parse_url($this->url, PHP_URL_HOST)) {
// Redirect back if user came from our site.
$this->url = $_SERVER['HTTP_REFERER'];
}
}
wp_redirect($this->url);
$this->render('cancel_appointment');
exit(0);
}
/**
* Prepare data for email.
*
* @param AB_CustomerAppointment $ca
* @return array
*/
private static function _prepareData(AB_CustomerAppointment $ca)
{
$appointment = new AB_Appointment();
$appointment->load($ca->get('appointment_id'));
$customer = new AB_Customer();
$customer->load($ca->get('customer_id'));
$staff = new AB_Staff();
$staff->load($appointment->get('staff_id'));
$service = new AB_Service();
$service->load($appointment->get('service_id'));
$staff_service = new AB_StaffService();
$staff_service->loadBy(array('staff_id' => $staff->get('id'), 'service_id' => $service->get('id')));
$price = $staff_service->get('price');
if ($ca->get('coupon_discount') or $ca->get('coupon_deduction')) {
$coupon = new AB_Coupon();
$coupon->set('discount', $ca->get('coupon_discount'));
$coupon->set('deduction', $ca->get('coupon_deduction'));
$price = $coupon->apply($price);
}
$codes = new AB_NotificationCodes();
$codes->set('appointment_datetime', $appointment->get('start_date'));
$codes->set('appointment_token', $ca->get('token'));
$codes->set('category_name', $service->getCategoryName());
$codes->set('client_name', $customer->get('name'));
$codes->set('client_phone', $customer->get('phone'));
$codes->set('client_email', $customer->get('email'));
$codes->set('custom_fields', $ca->getFormattedCustomFields('text'));
$codes->set('custom_fields_2c', $ca->getFormattedCustomFields('html'));
$codes->set('number_of_persons', $ca->get('number_of_persons'));
$codes->set('service_name', $service->getTitle());
$codes->set('service_price', $price);
$codes->set('staff_name', $staff->get('full_name'));
$codes->set('staff_email', $staff->get('email'));
$codes->set('staff_phone', $staff->get('phone'));
$codes->set('staff_photo', $staff->get('avatar_url'));
return array($codes, $staff, $appointment, $customer);
}
function update_7_0()
{
global $wpdb;
$wpdb->query('ALTER TABLE `ab_customer_appointment` ADD `coupon_deduction` DECIMAL(10,2) DEFAULT NULL AFTER `coupon_discount`');
$wpdb->query('ALTER TABLE `ab_coupons` CHANGE COLUMN `used` `used` INT UNSIGNED NOT NULL DEFAULT 0,
ADD COLUMN `deduction` DECIMAL(10,2) NOT NULL DEFAULT 0 AFTER `discount`,
ADD COLUMN `usage_limit` INT UNSIGNED NOT NULL DEFAULT 1');
$wpdb->query('ALTER TABLE `ab_notifications` CHANGE `slug` `type` VARCHAR(255) NOT NULL DEFAULT ""');
// SMS.
$wpdb->query('ALTER TABLE `ab_notifications` ADD `gateway` ENUM("email","sms") NOT NULL DEFAULT "email"');
$wpdb->query('UPDATE `ab_notifications` SET `gateway` = "email"');
$sms_notifies = array(array('type' => 'client_new_appointment', 'message' => __("Dear [[CLIENT_NAME]].\nThis is confirmation that you have booked [[SERVICE_NAME]].\nWe are waiting you at [[COMPANY_ADDRESS]] on [[APPOINTMENT_DATE]] at [[APPOINTMENT_TIME]].\nThank you for choosing our company.\n[[COMPANY_NAME]]\n[[COMPANY_PHONE]]\n[[COMPANY_WEBSITE]]", 'bookly'), 'active' => 1), array('type' => 'staff_new_appointment', 'message' => __("Hello.\nYou have new booking.\nService: [[SERVICE_NAME]]\nDate: [[APPOINTMENT_DATE]]\nTime: [[APPOINTMENT_TIME]]\nClient name: [[CLIENT_NAME]]\nClient phone: [[CLIENT_PHONE]]\nClient email: [[CLIENT_EMAIL]]", 'bookly'), 'active' => 0), array('type' => 'client_reminder', 'message' => __("Dear [[CLIENT_NAME]].\nWe would like to remind you that you have booked [[SERVICE_NAME]] tomorrow on [[APPOINTMENT_TIME]]. We are waiting you at [[COMPANY_ADDRESS]].\nThank you for choosing our company.\n[[COMPANY_NAME]]\n[[COMPANY_PHONE]]\n[[COMPANY_WEBSITE]]", 'bookly'), 'active' => 0), array('type' => 'client_follow_up', 'message' => __("Dear [[CLIENT_NAME]].\nThank you for choosing [[COMPANY_NAME]]. We hope you were satisfied with your [[SERVICE_NAME]].\nThank you and we look forward to seeing you again soon.\n[[COMPANY_NAME]]\n[[COMPANY_PHONE]]\n[[COMPANY_WEBSITE]]", 'bookly'), 'active' => 0), array('type' => 'staff_agenda', 'message' => __("Hello.\nYour agenda for tomorrow is:\n[[NEXT_DAY_AGENDA]]", 'bookly'), 'active' => 0), array('type' => 'staff_cancelled_appointment', 'message' => __("Hello.\nThe following booking has been cancelled.\nService: [[SERVICE_NAME]]\nDate: [[APPOINTMENT_DATE]]\nTime: [[APPOINTMENT_TIME]]\nClient name: [[CLIENT_NAME]]\nClient phone: [[CLIENT_PHONE]]\nClient email: [[CLIENT_EMAIL]]", 'bookly'), 'active' => 0), array('type' => 'client_new_wp_user', 'message' => __("Hello.\nAn account was created for you at [[SITE_ADDRESS]]\nYour user details:\nuser: [[NEW_USERNAME]]\npassword: [[NEW_PASSWORD]]\n\nThanks.", 'bookly'), 'active' => 1));
// Insert notifications.
foreach ($sms_notifies as $data) {
$wpdb->insert('ab_notifications', array('gateway' => 'sms', 'type' => $data['type'], 'subject' => '', 'message' => $data['message'], 'active' => $data['active']));
}
// Rename notifications.
$notifications = array('client_info' => 'client_new_appointment', 'provider_info' => 'staff_new_appointment', 'evening_next_day' => 'client_reminder', 'evening_after' => 'client_follow_up', 'event_next_day' => 'staff_agenda', 'cancel_appointment' => 'staff_cancelled_appointment', 'new_wp_user' => 'client_new_wp_user');
foreach ($notifications as $from => $to) {
$wpdb->query("UPDATE `ab_notifications` SET `type` = '{$to}' WHERE `type` = '{$from}'");
}
$this->drop('ab_email_notification');
// Rename tables.
$ab_tables = array('ab_appointment' => AB_Appointment::getTableName(), 'ab_category' => AB_Category::getTableName(), 'ab_coupons' => AB_Coupon::getTableName(), 'ab_customer' => AB_Customer::getTableName(), 'ab_customer_appointment' => AB_CustomerAppointment::getTableName(), 'ab_holiday' => AB_Holiday::getTableName(), 'ab_notifications' => AB_Notification::getTableName(), 'ab_payment' => AB_Payment::getTableName(), 'ab_schedule_item_break' => AB_ScheduleItemBreak::getTableName(), 'ab_service' => AB_Service::getTableName(), 'ab_staff' => AB_Staff::getTableName(), 'ab_staff_schedule_item' => AB_StaffScheduleItem::getTableName(), 'ab_staff_service' => AB_StaffService::getTableName());
foreach ($ab_tables as $from => $to) {
$wpdb->query("ALTER TABLE `{$from}` RENAME TO `{$to}`");
}
$wpdb->query("CREATE TABLE IF NOT EXISTS `" . AB_SentNotification::getTableName() . "` (\n `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,\n `customer_appointment_id` INT UNSIGNED,\n `staff_id` INT UNSIGNED,\n `gateway` ENUM('email','sms') NOT NULL DEFAULT 'email',\n `type` VARCHAR(60) NOT NULL,\n `created` DATETIME NOT NULL,\n CONSTRAINT fk_" . AB_SentNotification::getTableName() . "_" . AB_CustomerAppointment::getTableName() . "_id\n FOREIGN KEY (customer_appointment_id)\n REFERENCES " . AB_CustomerAppointment::getTableName() . "(id)\n ON DELETE CASCADE\n ON UPDATE CASCADE,\n CONSTRAINT fk_" . AB_SentNotification::getTableName() . "_" . AB_Staff::getTableName() . "_id\n FOREIGN KEY (staff_id)\n REFERENCES " . AB_Staff::getTableName() . "(id)\n ON DELETE CASCADE\n ON UPDATE CASCADE\n ) ENGINE = INNODB\n DEFAULT CHARACTER SET = utf8\n COLLATE = utf8_general_ci");
// Google Calendar.
add_option('ab_settings_google_event_title', '[[SERVICE_NAME]]');
// Link assets.
add_option('ab_settings_link_assets_method', 'enqueue');
// SMS.
add_option('ab_sms_default_country_code', '');
}
private function _drop_tables()
{
/** @var wpdb $wpdb */
global $wpdb;
$ab_tables = array(AB_Appointment::getTableName(), AB_Category::getTableName(), AB_Coupon::getTableName(), AB_Customer::getTableName(), AB_CustomerAppointment::getTableName(), AB_Holiday::getTableName(), AB_Notification::getTableName(), AB_Payment::getTableName(), AB_ScheduleItemBreak::getTableName(), AB_SentNotification::getTableName(), AB_Service::getTableName(), AB_Staff::getTableName(), AB_StaffScheduleItem::getTableName(), AB_StaffService::getTableName());
$this->_drop_fk($ab_tables);
$wpdb->query('DROP TABLE IF EXISTS `' . implode('`, `', $ab_tables) . '` CASCADE;');
}
/**
* Cancel Appointment using token.
*/
public function executeCancelAppointment()
{
$customer_appointment = new AB_Customer_Appointment();
if ($customer_appointment->loadByToken($this->getParameter('token'))) {
$customer_appointment->delete();
// Delete appointment, if there aren't customers
$current_capacity = $this->getWpdb()->get_var($this->getWpdb()->prepare('SELECT count(*) from `ab_customer_appointment` WHERE appointment_id = %d', $customer_appointment->get('appointment_id')));
if (!$current_capacity) {
$appointment = new AB_Appointment();
$appointment->load($customer_appointment->get('appointment_id'));
$appointment->delete();
}
if (get_option('ab_settings_cancel_page_url')) {
exit(wp_redirect(get_option('ab_settings_cancel_page_url')));
}
}
exit(wp_redirect(home_url()));
}
/**
* Check if booking time is still available
* Return TRUE if time is available
*
* @return bool
*/
public function checkBookingTime()
{
/** @var WPDB $wpdb */
global $wpdb;
$booked_datetime = $this->userData->get('appointment_datetime');
$endDate = new DateTime($booked_datetime);
$endDate->modify("+ {$this->userData->getService()->get('duration')} sec");
$query = $wpdb->prepare("SELECT `a`.*, `ss`.`capacity`, SUM(`ca`.`number_of_persons`) AS `total_number_of_persons`\n FROM `" . AB_CustomerAppointment::getTableName() . "` `ca`\n LEFT JOIN `" . AB_Appointment::getTableName() . "` `a` ON `a`.`id` = `ca`.`appointment_id`\n LEFT JOIN `" . AB_StaffService::getTableName() . "` `ss` ON `ss`.`staff_id` = `a`.`staff_id` AND `ss`.`service_id` = `a`.`service_id`\n WHERE `a`.`staff_id` = %d\n GROUP BY `a`.`start_date` , `a`.`staff_id` , `a`.`service_id`\n HAVING\n (`a`.`start_date` = %s AND `service_id` = %d AND `total_number_of_persons` >= `capacity`) OR\n (`a`.`start_date` = %s AND `service_id` <> %d) OR\n (`a`.`start_date` > %s AND `a`.`end_date` <= %s) OR\n (`a`.`start_date` < %s AND `a`.`end_date` > %s) OR\n (`a`.`start_date` < %s AND `a`.`end_date` > %s)\n LIMIT 1", $this->userData->getStaffId(), $booked_datetime, $this->userData->get('service_id'), $booked_datetime, $this->userData->get('service_id'), $booked_datetime, $endDate->format('Y-m-d H:i:s'), $endDate->format('Y-m-d H:i:s'), $endDate->format('Y-m-d H:i:s'), $booked_datetime, $booked_datetime);
return !(bool) $wpdb->get_row($query);
}
public function executeDeleteAppointment()
{
$appointment = new AB_Appointment();
$appointment->load($this->getParameter('appointment_id'));
$appointment->delete();
exit;
}
/**
* @return AB_Appointment
*/
public function save()
{
/** @var wpdb $wpdb */
global $wpdb;
// #11094: if customer with such name & e-mail exists, append new booking to him, otherwise - create new customer
$customer_exists = $wpdb->get_row($wpdb->prepare('SELECT * FROM ab_customer WHERE name = %s AND email = %s', $this->name, $this->email));
$customer = new AB_Customer();
if ($customer_exists) {
$customer->set('id', $customer_exists->id);
$customer->set('name', $customer_exists->name);
$customer->set('email', $customer_exists->email);
$customer->set('phone', $customer_exists->phone);
} else {
$customer->set('name', $this->name);
$customer->set('email', $this->email);
$customer->set('phone', $this->phone);
$customer->save();
}
$this->customer_id = $customer->get('id');
$service = new AB_Service();
$service->load($this->service_id);
$category = new AB_Category();
$category->load($service->get('category_id'));
/**
* Get appointment, with same params.
* If it is -> create connection to this appointment,
* otherwise create appointment and connect customer to new appointment
*/
$booking = $wpdb->get_row($wpdb->prepare("SELECT * from ab_appointment a WHERE a.staff_id = %d and a.service_id = %d and a.start_date = %s LIMIT 1;", $this->getStaffId(), $this->service_id, $this->booked_datetime));
$appointment = new AB_Appointment();
if ($booking) {
$appointment->load($booking->id);
} else {
$appointment->set('staff_id', $this->getStaffId());
$appointment->set('service_id', $this->service_id);
$appointment->set('start_date', date('Y-m-d H:i:s', strtotime($this->booked_datetime)));
$endDate = new DateTime($this->booked_datetime);
$di = "+ {$service->get('duration')} sec";
$endDate->modify($di);
$appointment->set('end_date', $endDate->format('Y-m-d H:i:s'));
$appointment->save();
}
$customer_appointment = new AB_Customer_Appointment();
$customer_appointment->set('appointment_id', $appointment->get('id'));
$customer_appointment->set('customer_id', $customer->get('id'));
$customer_appointment->set('token', md5($this->form_id));
$customer_appointment->set('notes', $this->notes);
$customer_appointment->save();
$staff = new AB_Staff();
$staff->load($this->getStaffId());
return $appointment;
}
/**
* @param AB_Appointment $appointment
*/
private function handleEventData(AB_Appointment $appointment)
{
$start_datetime = new Google_Service_Calendar_EventDateTime();
$start_datetime->setDateTime(DateTime::createFromFormat('Y-m-d H:i:s', $appointment->get('start_date'), new DateTimeZone(AB_Utils::getTimezoneString()))->format(DateTime::RFC3339));
$end_datetime = new Google_Service_Calendar_EventDateTime();
$end_datetime->setDateTime(DateTime::createFromFormat('Y-m-d H:i:s', $appointment->get('end_date'), new DateTimeZone(AB_Utils::getTimezoneString()))->format(DateTime::RFC3339));
$service = new AB_Service();
$service->load($appointment->get('service_id'));
$description = __('Service', 'bookly') . ": " . $service->get('title') . PHP_EOL;
$client_names = array();
foreach ($appointment->getCustomerAppointments() as $ca) {
$description .= sprintf("%s: %s\n%s: %s\n%s: %s\n", __('Name', 'bookly'), $ca->customer->get('name'), __('Email', 'bookly'), $ca->customer->get('email'), __('Phone', 'bookly'), $ca->customer->get('phone'));
$description .= $ca->getFormattedCustomFields('text');
$description .= PHP_EOL;
$client_names[] = $ca->customer->get('name');
}
$staff = new AB_Staff();
$staff->load($appointment->get('staff_id'));
$title = strtr(get_option('ab_settings_google_event_title', '[[SERVICE_NAME]]'), array('[[SERVICE_NAME]]' => $service->get('title'), '[[CLIENT_NAMES]]' => implode(', ', $client_names), '[[STAFF_NAME]]' => $staff->get('full_name')));
$this->event->setStart($start_datetime);
$this->event->setEnd($end_datetime);
$this->event->setSummary($title);
$this->event->setDescription($description);
$extended_property = new Google_Service_Calendar_EventExtendedProperties();
$extended_property->setPrivate(array('customers' => json_encode(array_map(function ($ca) {
return $ca->customer->get('id');
}, $appointment->getCustomerAppointments())), 'service_id' => $service->get('id'), 'appointment_id' => $appointment->get('id')));
$this->event->setExtendedProperties($extended_property);
}
/**
* @param $start_date
* @param $end_date
* @param $staff_id
* @param $appointment_id
* @return bool
*/
private function dateIntervalIsAvailableForAppointment($start_date, $end_date, $staff_id, $appointment_id)
{
return AB_Appointment::query()->whereNot('id', $appointment_id)->where('staff_id', $staff_id)->whereRaw('(start_date > %s AND start_date < %s OR (end_date > %s AND end_date < %s) OR (start_date < %s AND end_date > %s) OR (start_date = %s OR end_date = %s) )', array($start_date, $end_date, $start_date, $end_date, $start_date, $end_date, $start_date, $end_date))->count() == 0;
}
