/** * 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; }
/** * Get list of users available for particular staff. * * @global string $table_prefix * @param integer $staff_id If null then it means new staff * @return array */ public function getUsersForStaff($staff_id = null) { /** @var wpdb $wpdb */ global $wpdb; if (!is_multisite()) { $query = sprintf('SELECT ID, user_email, display_name FROM ' . $wpdb->users . ' WHERE ID NOT IN(SELECT DISTINCT IFNULL( wp_user_id, 0 ) FROM ' . AB_Staff::getTableName() . ' %s) ORDER BY display_name', $staff_id !== null ? "WHERE " . AB_Staff::getTableName() . ".id <> {$staff_id}" : ''); $users = $wpdb->get_results($query); } else { // In Multisite show users only for current blog. if ($staff_id == null) { $query = AB_Staff::query('s')->select('DISTINCT wp_user_id')->whereNot('wp_user_id', null); } else { $query = AB_Staff::query('s')->select('wp_user_id')->whereNot('id', $staff_id); } $occupied_wp_users = array(); foreach ($query->fetchArray() as $staff) { $occupied_wp_users[] = $staff['wp_user_id']; } $users = get_users(array('blog_id' => get_current_blog_id(), 'orderby' => 'display_name', 'exclude' => $occupied_wp_users)); } return $users; }
/** * @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; } }
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;'); }
/** * Constructor. */ public function __construct() { global $wpdb; // Select all services (with categories and staff members) // which have at least one staff member assigned. $rows = $wpdb->get_results($wpdb->prepare(' SELECT IFNULL(`c`.`id`,0) AS `category_id`, IFNULL(`c`.`name`,%s) AS `category_name`, `c`.`position` AS `category_position`, `s`.`id` AS `service_id`, `s`.`position` AS `service_position`, `s`.`title` AS `service_name`, `st`.`id` AS `staff_id`, `st`.`position` AS `staff_position`, `st`.`full_name` AS `staff_name`, `ss`.`capacity` AS `capacity`, `ss`.`price` AS `price` FROM `' . AB_Service::getTableName() . '` `s` INNER JOIN `' . AB_StaffService::getTableName() . '` `ss` ON `s`.`id` = `ss`.`service_id` LEFT JOIN `' . AB_Category::getTableName() . '` `c` ON `s`.`category_id` = `c`.`id` LEFT JOIN `' . AB_Staff::getTableName() . '` `st` ON `ss`.`staff_id` = `st`.`id` ORDER BY `service_name` ', __('Uncategorized', 'bookly')), ARRAY_A); foreach ($rows as $row) { if (!isset($this->services[$row['service_id']])) { $this->services[$row['service_id']] = array('id' => $row['service_id'], 'name' => AB_Utils::getTranslatedString('service_' . $row['service_id'], $row['service_name']), 'category_id' => $row['category_id'], 'staff' => array(), 'max_capacity' => $row['capacity'], 'position' => $row['service_position']); } else { if ($this->services[$row['service_id']]['max_capacity'] < $row['capacity']) { // Detect the max capacity for each service //(it is the max capacity from all staff members who provides this service). $this->services[$row['service_id']]['max_capacity'] = $row['capacity']; } } if (!isset($this->staff[$row['staff_id']])) { $this->staff[$row['staff_id']] = array('id' => $row['staff_id'], 'name' => AB_Utils::getTranslatedString('staff_' . $row['staff_id'], $row['staff_name']), 'services' => array(), 'position' => $row['staff_position']); } if ($row['category_id'] != '' && !isset($this->categories[$row['category_id']])) { $this->categories[$row['category_id']] = array('id' => $row['category_id'], 'name' => AB_Utils::getTranslatedString('category_' . $row['category_id'], $row['category_name']), 'services' => array(), 'position' => $row['category_position']); } if (!isset($this->services[$row['service_id']]['staff'][$row['staff_id']])) { $staff_member = $this->staff[$row['staff_id']]; unset($staff_member['services']); if (self::isPaymentDisabled() == false) { $staff_member['name'] .= ' (' . AB_Utils::formatPrice($row['price']) . ')'; } $this->services[$row['service_id']]['staff'][$row['staff_id']] = $staff_member; } if (!isset($this->staff[$row['staff_id']]['services'][$row['service_id']])) { $service = $this->services[$row['service_id']]; unset($service['staff']); $service['max_capacity'] = $row['capacity']; $this->staff[$row['staff_id']]['services'][$row['service_id']] = $service; } if (!isset($this->categories[intval($row['category_id'])]['staff'][$row['staff_id']])) { $staff_member = $this->staff[$row['staff_id']]; unset($staff_member['services']); $this->categories[intval($row['category_id'])]['staff'][$row['staff_id']] = $staff_member; } if (!isset($this->categories[intval($row['category_id'])]['services'][$row['service_id']])) { $service = $this->services[$row['service_id']]; unset($service['staff']); $this->categories[intval($row['category_id'])]['services'][$row['service_id']] = $service; } } }