public function load($staff_id)
{
$data = $this->wpdb->get_results('
SELECT c.name AS category_name, s.*
FROM ' . AB_Category::getTableName() . ' c
INNER JOIN ' . AB_Service::getTableName() . ' s ON c.id = s.category_id
', ARRAY_A);
if (!$data) {
$data = array();
}
$this->uncategorized_services = AB_Service::query('s')->where('s.category_id', null)->fetchArray();
$staff_services = AB_StaffService::query('ss')->select('ss.service_id, ss.price, ss.capacity')->where('ss.staff_id', $staff_id)->fetchArray();
if ($staff_services) {
foreach ($staff_services as $staff_service) {
$this->selected[$staff_service['service_id']] = array('price' => $staff_service['price'], 'capacity' => $staff_service['capacity']);
}
}
foreach ($data as $row) {
if (!isset($this->collection[$row['category_id']])) {
$abCategory = new AB_Category();
$abCategory->set('id', $row['category_id']);
$abCategory->set('name', $row['category_name']);
$this->collection[$row['category_id']] = $abCategory;
}
unset($row['category_name']);
$abService = new AB_Service($row);
$this->category_services[$row['category_id']][] = $abService->get('id');
$this->collection[$row['category_id']]->addService($abService);
}
}
/**
* Get category name.
*
* @return string
*/
public function getCategoryName()
{
if ($this->get('category_id')) {
$category = new AB_Category();
$category->load($this->get('category_id'));
return $category->get('name');
}
return __('Uncategorized', 'bookly');
}
public function load($staff_id)
{
$data = $this->wpdb->get_results('
SELECT c.name AS category_name, s.*
FROM ab_category c
INNER JOIN ab_service s ON c.id = s.category_id
', ARRAY_A);
if (!$data) {
$data = array();
}
$uncategorized_services = $this->wpdb->get_results('SELECT * FROM ab_service WHERE category_id IS NULL');
foreach ($uncategorized_services as $uncategorized_service) {
$abService = new AB_Service();
$abService->setData($uncategorized_service);
$this->uncategorized_services[] = $abService;
}
$rows = $this->wpdb->get_results($this->wpdb->prepare('
SELECT s.service_id, s.price, s.capacity
FROM ab_staff_service s
WHERE s.staff_id = %d
', $staff_id));
if ($rows) {
foreach ($rows as $row) {
$this->selected[$row->service_id] = array('price' => $row->price, 'capacity' => $row->capacity);
}
}
foreach ($data as $row) {
if (!isset($this->collection[$row['category_id']])) {
$abCategory = new AB_Category();
$abCategory->set('id', $row['category_id']);
$abCategory->set('name', $row['category_name']);
$this->collection[$row['category_id']] = $abCategory;
}
unset($row['category_name']);
$abService = new AB_Service();
$abService->setData($row);
$this->category_services[$row['category_id']][] = $abService->get('id');
$this->collection[$row['category_id']]->addService($abService);
}
}
/**
* 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;
}
/**
* @return mixed
*/
private function getCategoryCollection()
{
return AB_Category::query()->sortBy('position')->fetchArray();
}
/**
* Delete category.
*/
public function executeDeleteCategory()
{
$category = new AB_Category();
$category->set('id', $this->getParameter('id', 0));
$category->delete();
}
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;');
}
/**
* @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;
}
/**
* 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;
}
}
}
