/home/edulekha/crm.edulekha.com/modules/appointly/models/Appointly_model.php
<?php
defined('BASEPATH') or exit('No direct script access allowed');
class Appointly_model extends App_Model
{
public function __construct()
{
parent::__construct();
$this->load->model('appointly/appointly_attendees_model', 'atm');
$this->load->model('appointly/service_model', 'service_model');
}
/**
* Insert new appointment
*
* @param array $data
*
* @return bool
* @throws Exception
*/
public function create_appointment($data)
{
$attendees = [];
$relation = $data['rel_type'];
$external_cid = null;
unset($data['rel_type']);
// Ensure rel_lead_type is unset to avoid SQL errors
if (isset($data['rel_lead_type'])) {
unset($data['rel_lead_type']);
}
// Ensure notification flags are properly set
$data['by_sms'] = $data['by_sms'] ?? 0;
$data['by_email'] = $data['by_email'] ?? 0;
// Check if this is a staff-only appointment (internal_staff)
if ($relation == 'internal_staff') {
// Skip service validation for staff-only appointments
// Set default duration for staff-only
$data['duration'] = $data['duration'] ?? 60;
// Set source for staff-only
$data['source'] = 'internal_staff';
// Remove client/contact/lead info for staff-only
unset($data['contact_id'], $data['email'], $data['phone'], $data['name'], $data['rel_id']);
// Ensure address is set for Google Calendar
$data['address'] = $data['address'] ?? '';
} elseif (isset($data['service_id'])) {
// Get service details and validate for non-staff-only appointments
$service = $this->service_model->get($data['service_id']);
if (! $service) {
return false;
}
// Set duration from service
$data['duration'] = $service->duration;
}
if ($relation == 'lead_related') {
$this->load->model('leads_model');
$lead = $this->leads_model->get($data['rel_id']);
if ($lead) {
$data['contact_id'] = $data['rel_id'];
$data['name'] = $lead->name;
if ($lead->phonenumber != '') {
$data['phone'] = $lead->phonenumber;
}
if ($lead->address != '') {
$data['address'] = $lead->address;
}
if ($lead->email != '') {
$data['email'] = $lead->email;
}
}
$data['source'] = 'lead_related';
// Unset rel_id here now that we've used it
if (isset($data['rel_id'])) {
unset($data['rel_id']);
}
} elseif ($relation == 'internal') {
$contact_id = $data['contact_id'] ?? null;
if ($contact_id) {
$this->load->model('clients_model');
$data['contact_id'] = $contact_id;
$data['source'] = 'internal';
}
// Unset rel_id if it exists, but we don't need it
if (isset($data['rel_id'])) {
unset($data['rel_id']);
}
} elseif ($relation == 'external') {
if (! $data['email']) {
return false;
}
$data['source'] = 'external';
// Unset rel_id if it exists, but we don't need it
if (isset($data['rel_id'])) {
unset($data['rel_id']);
}
}
// Process recurring data using the validateRecurringData method
if (isset($data['repeat_appointment'])) {
$data = $this->validateRecurringData($data);
}
// Process reminder fields first
$data = handleDataReminderFields($data);
// Remove recurring fields if recurring isn't enabled
if (!isset($data['recurring']) || $data['recurring'] != 1) {
unset($data['repeat_every']);
unset($data['repeat_every_custom']);
unset($data['repeat_type_custom']);
}
// Only process reminder settings if notifications are enabled
if ((!isset($data['by_email']) || $data['by_email'] != 1) &&
(!isset($data['by_sms']) || $data['by_sms'] != 1)
) {
unset($data['reminder_before']);
unset($data['reminder_before_type']);
}
// Remove repeat_appointment field as it's only used for UI
unset($data['repeat_appointment']);
// Ensure address is set for all appointment types
$data['address'] = $data['address'] ?? '';
// Format date properly (DATE only, no time - we store time separately in start_hour/end_hour)
$data['date'] = to_sql_date($data['date']);
// Description can be empty
$data['description'] = $data['description'] ?? '';
// Ensure start_hour and end_hour are properly set
if (! empty($data['start_hour'])) {
// Format start_hour for consistent storage (HH:MM format without seconds)
$data['start_hour'] = date('H:i', strtotime($data['start_hour']));
// Calculate end_hour from start_hour and duration if not already set
if ((empty($data['end_hour'])) && ! empty($data['duration'])) {
$end_time = strtotime($data['start_hour']) + ($data['duration'] * 60);
$data['end_hour'] = date('H:i', $end_time);
}
} elseif (! empty($data['end_hour']) && ! empty($data['duration'])) {
// If only end_hour is set, calculate start_hour by subtracting duration
$data['end_hour'] = date('H:i', strtotime($data['end_hour']));
$start_time = strtotime($data['end_hour']) - ($data['duration'] * 60);
$data['start_hour'] = date('H:i', $start_time);
}
// Ensure end_hour is formatted properly if it was set directly
if (! empty($data['end_hour'])) {
$data['end_hour'] = date('H:i', strtotime($data['end_hour']));
}
// Set created by
$data['created_by'] = $data['created_by'] ?? get_staff_user_id();
// Initialize links
$data['google_calendar_link'] = '';
$data['google_added_by_id'] = null;
$data['outlook_calendar_link'] = '';
$data['outlook_event_id'] = '';
$data['feedback_comment'] = '';
// Format phone number
if (! empty($data['phone'])) {
$data['phone'] = trim($data['phone']);
}
// Get contact data for internal appointments
if (
isset($data['source']) && $data['source'] == 'internal'
&& empty($data['email'])
) {
$contact_data = get_appointment_contact_details($data['contact_id']);
$data['email'] = $contact_data['email'];
$data['name'] = $contact_data['full_name'];
$data['phone'] = $contact_data['phone'];
}
// Handle status - default to 'in-progress' if not set
$data['status'] = isset($data['status']) && in_array($data['status'], ['pending', 'cancelled', 'completed', 'no-show', 'in-progress'])
? $data['status']
: 'in-progress';
// Handle timezone
$data['timezone'] = $data['timezone'] ?? get_option('default_timezone');
// Extract attendees
if (isset($data['attendees'])) {
$attendees = $data['attendees'];
unset($data['attendees']);
}
// provider to attendees if not already included
$provider_id = $data['provider_id'] ?? null;
if ($provider_id && ! in_array((int)$provider_id, $attendees, true)) {
$attendees[] = (int)$provider_id;
}
// Staff-only appointment validation
if ($relation == 'internal_staff' && empty($attendees)) {
// Return false instead of throwing exception
return false;
}
// Google Calendar integration
if ((isset($data['google']) && $data['google'] && appointlyGoogleAuth()) || (get_option('appointly_auto_enable_google_meet') == '1' && appointlyGoogleAuth())) {
// For staff-only, ensure each attendee has a valid email before adding to Google
if ($relation == 'internal_staff' && ! empty($attendees)) {
// Validate attendee emails before trying to add to Google Calendar
$valid_attendees_emails = [];
foreach ($attendees as $attendee_id) {
$staff = $this->staff_model->get($attendee_id);
if ($staff && filter_var($staff->email, FILTER_VALIDATE_EMAIL)) {
$valid_attendees_emails[] = $staff->email;
}
}
// Only proceed with Google Calendar if we have valid attendees
if (! empty($valid_attendees_emails)) {
$data['external_contact_id'] = $external_cid;
// Add timeout protection for Google Calendar API
try {
$googleEvent = insertAppointmentToGoogleCalendar($data, $valid_attendees_emails);
if ($googleEvent) {
$data['google_event_id'] = $googleEvent['google_event_id'];
$data['google_calendar_link'] = $googleEvent['htmlLink'];
if (isset($googleEvent['hangoutLink'])) {
$data['google_meet_link'] = $googleEvent['hangoutLink'];
}
$data['google_added_by_id'] = get_staff_user_id();
}
} catch (Exception $e) {
log_message('error', 'Google Calendar integration failed during appointment creation: ' . $e->getMessage());
// Continue with appointment creation even if Google Calendar fails
}
}
} else {
$data['external_contact_id'] = $external_cid;
// Add timeout protection for Google Calendar API
try {
$googleEvent = insertAppointmentToGoogleCalendar($data, $attendees);
if ($googleEvent) {
$data['google_event_id'] = $googleEvent['google_event_id'];
$data['google_calendar_link'] = $googleEvent['htmlLink'];
if (isset($googleEvent['hangoutLink'])) {
$data['google_meet_link'] = $googleEvent['hangoutLink'];
}
$data['google_added_by_id'] = get_staff_user_id();
}
} catch (Exception $e) {
log_message('error', 'Google Calendar integration failed during appointment creation: ' . $e->getMessage());
// Continue with appointment creation even if Google Calendar fails
}
}
unset($data['google'], $data['external_contact_id']);
}
// Final safety check before database insert
if (isset($data['rel_id'])) {
unset($data['rel_id']);
}
if (isset($data['custom_fields'])) {
$custom_fields = $data['custom_fields'];
unset($data['custom_fields']);
}
// We need this for external appointments form view public url ...
$data['hash'] = app_generate_hash();
// Remove CSRF token and other non-database fields before insert
$fieldsToRemove = ['ci_csrf_token', 'csrf_token_name', 'current_language'];
foreach ($fieldsToRemove as $field) {
if (isset($data[$field])) {
unset($data[$field]);
}
}
// Insert the appointment
$this->db->insert(db_prefix() . 'appointly_appointments', $data);
$insert_id = $this->db->insert_id();
if ($insert_id) {
$data['appointment_id'] = $insert_id;
$data['id'] = $insert_id;
$data['feedbacks'] = null;
if (isset($custom_fields)) {
handle_custom_fields_post($insert_id, $custom_fields);
}
// Create attendees
if (! empty($attendees)) {
$this->atm->create($insert_id, $attendees);
}
// Link appointment to service if service_id is provided
if (! empty($data['service_id'])) {
$this->link_appointment_service($insert_id, $data['service_id']);
}
// Handle custom fields and notifications
$this->prepareAndNotifyUsers($data, $insert_id);
// Auto-create invoice if setting is enabled (for internal appointments with contact_id)
$this->auto_create_invoice_on_booking($insert_id, $data);
return $insert_id;
}
return false;
}
/**
* Normalize and validate recurring appointment data (for insert or update)
*
* @param array $data
* @param array|null $original Optional original data for update comparison
*
* @return array
*/
private function validateRecurringData(array $data, ?array $original = null)
{
if (
isset($original['repeat_every'], $data['repeat_every']) && $original && $original['repeat_every'] !== '' && $data['repeat_every'] === ''
) {
$data['cycles'] = 0;
$data['total_cycles'] = 0;
$data['last_recurring_date'] = null;
}
if (! empty($data['repeat_every'])) {
$data['recurring'] = 1;
if ($data['repeat_every'] === 'custom') {
if (isset($data['repeat_every_custom'])) {
$data['repeat_every'] = $data['repeat_every_custom'];
$data['recurring_type'] = $data['repeat_type_custom'] ??
null;
$data['custom_recurring'] = 1;
}
} else {
$_temp = explode('-', $data['repeat_every']);
if (count($_temp) > 1) {
$data['recurring_type'] = $_temp[1];
$data['repeat_every'] = $_temp[0];
$data['custom_recurring'] = 0;
}
}
} else {
$data['recurring'] = 0;
}
// Normalize cycles if not set or recurrence is off
$data['cycles'] = (! isset($data['cycles']) || $data['recurring'] == 0)
? 0
: $data['cycles'];
// Always clean temporary fields
unset($data['repeat_type_custom'], $data['repeat_every_custom'], $data['repeat_appointment']);
return $data;
}
public function link_appointment_service($appointment_id, $service_id)
{
$this->db->insert(db_prefix() . 'appointly_appointment_services', [
'appointment_id' => $appointment_id,
'service_id' => $service_id,
]);
return $this->db->insert_id();
}
/**
* Helper function for create appointment
*w
*
* @param $data
* @param $insert_id
*
* @return bool
*/
public function prepareAndNotifyUsers($data, $insert_id)
{
$data = array_merge($data, convertDateForDatabase($data['date']));
// Remove unnecessary fields
$fieldsToRemove = ['custom_fields', 'rel_id', 'staff_id'];
foreach ($fieldsToRemove as $field) {
if (isset($data[$field])) {
unset($data[$field]);
}
}
if (isset($data['rel_id'])) {
unset($data['rel_id']);
}
// Ensure reminders are set we have the by_sms and by_email flags
$by_sms = $data['by_sms'] ?? 0;
$by_email = $data['by_email'] ?? 0;
// Update appointment with notification flags if they weren't already set
$this->db->where('id', $insert_id);
$this->db->update(db_prefix() . 'appointly_appointments', ['by_sms' => $by_sms, 'by_email' => $by_email]);
// Ensure appointment has email before sending notifications
$this->appointment_approve_notification_and_sms_triggers($insert_id);
return $insert_id;
}
/**
* Get raw appointment data without permission filtering
*
* This method provides direct database access without staff permission checks.
* Use for client/public access where caller handles security validation.
* For staff operations with permission filtering, use get_appointment() instead.
*
* @param string $appointment_id
*
* @return array|bool
*/
public function get_appointment_data($appointment_id)
{
$this->db->where('id', $appointment_id);
$appointment = $this->db->get(db_prefix() . 'appointly_appointments')->row_array();
if ($this->db->affected_rows() > 0) {
$appointment['attendees'] = $this->atm->get($appointment_id);
return $appointment;
}
return false;
}
/**
* Send email and SMS notifications
*
* @param string $appointment_id
*
* @return void
*/
public function appointment_approve_notification_and_sms_triggers($appointment_id): void
{
try {
// Get appointment data
$appointment = $this->get_appointment_data($appointment_id);
// Fetch attendees (array of staffid)
$attendees = $this->get_appointment_attendees($appointment_id);
$recipient_ids = array_unique(array_column($attendees, 'staffid'));
// remove creator from recipient_ids
if ($appointment && isset($appointment['created_by'])) {
$recipient_ids = array_diff($recipient_ids, [$appointment['created_by']]);
}
foreach ($recipient_ids as $staff_id) {
if ($staff_id == $appointment['created_by']) {
continue;
}
add_notification([
'description' => 'appointment_notification',
'touserid' => $staff_id,
'fromcompany' => true,
'link' => 'appointly/appointments/view?appointment_id=' . $appointment_id,
]);
}
// Collect all recipients for pusher notifications (attendees + provider if not in attendees)
$pusher_recipients = $recipient_ids;
// Add provider to pusher notifications if they're not already in attendees
if (!empty($appointment['provider_id']) && $appointment['provider_id'] != $appointment['created_by']) {
$provider_in_attendees = false;
foreach ($attendees as $attendee) {
if ($attendee['staffid'] == $appointment['provider_id']) {
$provider_in_attendees = true;
break;
}
}
// Add provider to pusher notifications if not already included
if (!$provider_in_attendees) {
$pusher_recipients[] = $appointment['provider_id'];
}
}
// Send pusher notifications to all relevant staff for real-time updates
if (!empty($pusher_recipients)) {
$unique_recipients = array_unique($pusher_recipients);
log_message('debug', 'APPOINTMENT CREATION Pusher recipients: ' . json_encode($unique_recipients));
pusher_trigger_notification($unique_recipients);
}
$template = null;
// Send email to customer if by_email is enabled
if (! empty($appointment['email'])) {
try {
$template = mail_template(
'appointly_appointment_approved_to_contact',
'appointly',
array_to_object($appointment)
);
// Send the email
$template->send();
} catch (Exception $e) {
// Log the error but continue
log_message('error', 'Failed to send appointment email: ' . $e->getMessage());
}
}
// Send SMS to customer if by_sms is enabled
if (! empty($appointment['phone'])) {
try {
// Trigger SMS
$this->app_sms->trigger(
APPOINTLY_SMS_APPOINTMENT_APPROVED_TO_CLIENT,
$appointment['phone'],
isset($template) ? $template->get_merge_fields() : []
);
} catch (Exception $e) {
// Log the error but continue
log_message('error', 'Failed to send appointment SMS: ' . $e->getMessage());
}
}
// Send email to attendees
if (!empty($attendees)) {
foreach ($attendees as $staff) {
if (isset($staff['email']) && !empty($staff['email'])) {
send_mail_template(
'appointly_appointment_approved_to_staff_attendees',
'appointly',
array_to_object($appointment),
array_to_object($staff)
);
}
}
}
// Send email to provider if different from creator and not in attendees
if (!empty($appointment['provider_id']) && $appointment['provider_id'] != $appointment['created_by']) {
// Check if provider is already in attendees list
$provider_in_attendees = false;
foreach ($attendees as $attendee) {
if ($attendee['staffid'] == $appointment['provider_id']) {
$provider_in_attendees = true;
break;
}
}
// Only send if provider is not already in attendees
if (!$provider_in_attendees) {
$provider_staff = appointly_get_staff($appointment['provider_id']);
if (!empty($provider_staff)) {
send_mail_template(
'appointly_appointment_approved_to_staff_attendees',
'appointly',
array_to_object($appointment),
array_to_object($provider_staff)
);
}
}
}
} catch (Exception $e) {
// Log the error but don't interrupt the appointment status change
log_message('error', 'Error in appointment notifications: ' . $e->getMessage());
}
}
public function get_appointment_attendees($appointment_id)
{
$this->db->select('staff.*, appointly_attendees.appointment_id');
$this->db->from(db_prefix() . 'staff staff');
$this->db->join(
db_prefix() . 'appointly_attendees appointly_attendees',
'appointly_attendees.staff_id = staff.staffid',
'inner'
);
$this->db->where('appointly_attendees.appointment_id', $appointment_id);
return $this->db->get()->result_array();
}
public function recurringAddGoogleNewEvent($data, $attendees)
{
$googleInsertData = [];
$googleEvent = insertAppointmentToGoogleCalendar($data, $attendees);
$googleInsertData['google_event_id'] = $googleEvent['google_event_id'];
$googleInsertData['google_calendar_link'] = $googleEvent['htmlLink'];
if (isset($googleEvent['hangoutLink'])) {
$googleInsertData['google_meet_link'] = $googleEvent['hangoutLink'];
}
return $googleInsertData;
}
/**
* Add appointment to google calendar
*
* @param array $data
*
* @return array
* @throws Exception
*/
public function add_event_to_google_calendar($data)
{
$result = [
'result' => 'error',
'message' => _l('Oops, something went wrong, please try again...'),
];
if (appointlyGoogleAuth()) {
// info log to see what data we're receiving
if (isset($data['appointment_id'])) {
// Get the full appointment data - this ensures we have all necessary information
$appointment = $this->get_appointment_data($data['appointment_id']);
if (! $appointment) {
return [
'result' => 'error',
'message' => 'Appointment not found',
];
}
// Check if appointment already has a Google Calendar event
if (!empty($appointment['google_event_id'])) {
return [
'result' => 'success',
'message' => _l('appointment_already_in_google_calendar'),
'google_event_id' => $appointment['google_event_id'],
'google_calendar_link' => $appointment['google_calendar_link'] ?? '',
'google_meet_link' => $appointment['google_meet_link'] ?? null,
];
}
// Ensure start_hour and end_hour are properly formatted for Google Calendar
if (isset($appointment['start_hour']) && ! strpos($appointment['start_hour'], ':')) {
$appointment['start_hour'] .= ':00';
}
if (isset($appointment['end_hour']) && ! strpos($appointment['end_hour'], ':')) {
$appointment['end_hour'] .= ':00';
}
// Prepare attendees
$attendees = $data['attendees'] ?? [];
if (empty($attendees) && isset($appointment['attendees'])) {
$attendees = array_column(
$appointment['attendees'],
'staffid'
);
}
// Create the Google Calendar event using the same function as when creating appointments
$googleEvent = insertAppointmentToGoogleCalendar($appointment, $attendees);
if (! $googleEvent || ! isset($googleEvent['google_event_id'])) {
log_message('error', 'Google Calendar Add Event - Failed to create event');
return [
'result' => 'error',
'message' => 'Failed to create Google Calendar event',
];
}
// Update the appointment with Google Calendar data
$googleUpdateData = [
'google_event_id' => $googleEvent['google_event_id'],
'google_calendar_link' => $googleEvent['htmlLink'],
'google_added_by_id' => get_staff_user_id(),
];
// Add Google Meet link if available
if (isset($googleEvent['hangoutLink'])) {
$googleUpdateData['google_meet_link'] = $googleEvent['hangoutLink'];
}
$this->db->where('id', $data['appointment_id']);
$this->db->update(db_prefix() . 'appointly_appointments', $googleUpdateData);
if ($this->db->affected_rows() > 0) {
return [
'result' => 'success',
'message' => _l('appointments_added_to_google_calendar'),
'google_event_id' => $googleEvent['google_event_id'],
'google_calendar_link' => $googleEvent['htmlLink'],
'google_meet_link' => $googleEvent['hangoutLink'] ?? null,
];
}
}
}
return $result;
}
/**
* Inserts appointment submitted from external clients form
*
* @param array $data
*
* @return bool
*/
public function insert_external_appointment($data)
{
// Basic data preparation
$data['hash'] = app_generate_hash();
$data['source'] = 'external';
// Clean phone number
if (isset($data['phone']) && $data['phone']) {
$data['phone'] = trim($data['phone']);
}
// Get service details and set duration
if (! empty($data['service_id'])) {
$this->load->model('appointly/service_model');
$service = $this->service_model->get($data['service_id']);
if ($service) {
$data['duration'] = $service->duration;
// Store service id for later use
$appointment_service_id = $data['service_id'];
}
}
// Add provider_id from staff_id
if (! empty($data['staff_id'])) {
$data['provider_id'] = $data['staff_id'];
unset($data['staff_id']);
} elseif (! empty($data['hidden_staff_id'])) {
$data['provider_id'] = $data['hidden_staff_id'];
}
// Remove duplicate provider_id value if it exists
unset($data['hidden_staff_id']);
// Convert date for database and ensure start_hour is properly set
if (isset($data['date']) && !empty($data['date'])) {
$date_data = convertDateForDatabase($data['date']);
$data['date'] = $date_data['date'];
// Make sure start_hour is set and has the correct format
if (!isset($data['start_hour']) || empty($data['start_hour'])) {
if (!empty($date_data['start_hour'])) {
$data['start_hour'] = $date_data['start_hour'];
}
}
}
// Process appointment times with proper date context
if (isset($data['start_hour']) && !empty($data['start_hour']) && isset($data['duration'])) {
// Use appointment date to avoid timezone issues
$appointment_date = $data['date'] ?? date('Y-m-d');
$start_timestamp = strtotime($appointment_date . ' ' . $data['start_hour']);
$end_timestamp = $start_timestamp + ($data['duration'] * 60);
// Format end hour
$data['end_hour'] = date('H:i', $end_timestamp);
}
$data['status'] = 'pending';
// Handle custom fields if present
if (isset($data['custom_fields'])) {
$custom_fields = $data['custom_fields'];
}
// Remove unnecessary fields
unset($data['rel_type'], $data['terms_accepted'], $data['custom_fields'], $data['current_language']);
// Insert appointment
$this->db->insert(db_prefix() . 'appointly_appointments', $data);
$appointment_id = $this->db->insert_id();
if ($appointment_id) {
// If we have a service, link it to the appointment
if (! empty($appointment_service_id)) {
$this->link_appointment_service(
$appointment_id,
$appointment_service_id
);
}
// Handle custom fields
if (isset($custom_fields)) {
handle_custom_fields_post($appointment_id, $custom_fields);
}
// Send notifications if approved by default
$data['id'] = $appointment_id;
// For external appointments, notify all relevant staff so they can approve
if ($data['source'] === 'external') {
$this->notifyAdminsAndProvidersForExternalAppointment($appointment_id);
} else {
$this->notify_appointment_staff($appointment_id, $data);
}
// Send confirmation email to client
$this->send_client_submission_confirmation($appointment_id, $data);
// Auto-create invoice if setting is enabled and user is logged in
$this->auto_create_invoice_on_booking($appointment_id, $data);
}
return $appointment_id;
}
/**
* Auto-create invoice on booking if setting is enabled
*
* @param int $appointment_id
* @param array $data
* @return void
*/
private function auto_create_invoice_on_booking($appointment_id, $data)
{
// Check if invoice creation is enabled
if (get_option('appointly_show_invoice_option') != '1') {
return;
}
// Only create invoice if user is logged in (has contact_id)
if (empty($data['contact_id'])) {
return;
}
// Load the invoice model
$this->load->model('appointly/appointlyinvoices_model', 'appointly_invoices');
// Create the invoice
$invoice_id = $this->appointly_invoices->create_invoice_from_appointment($appointment_id);
if ($invoice_id) {
// Update appointment with invoice_id
$this->db->where('id', $appointment_id);
$this->db->update(db_prefix() . 'appointly_appointments', [
'invoice_id' => $invoice_id,
'invoice_date' => date('Y-m-d H:i:s')
]);
}
}
/**
* Notify all staff
*
* @param int $appointment_id
* @param array $data
*
* @return void
*/
private function notifyAdminsAndProvidersForExternalAppointment($appointment_id)
{
// Fetch all staff with permissions to view appointments or admin access
$this->db->select('staffid');
$this->db->where('admin', 1);
$this->db->where('active', 1);
$staff = $this->db->get(db_prefix() . 'staff')->result_array();
$notified_users = [];
// Get appointment data for email
$appointment = $this->get_appointment_data($appointment_id);
// Also notify the assigned provider if exists and not already an admin
if (!empty($appointment['provider_id'])) {
// Check if provider is already an admin
$provider_is_admin = false;
foreach ($staff as $admin) {
if ($admin['staffid'] == $appointment['provider_id']) {
$provider_is_admin = true;
break;
}
}
// Add provider to staff list if not already an admin
if (!$provider_is_admin) {
$provider_staff = $this->db->get_where(db_prefix() . 'staff', [
'staffid' => $appointment['provider_id'],
'active' => 1
])->row_array();
if ($provider_staff) {
$staff[] = $provider_staff;
}
}
}
// Send notifications to each eligible staff member
foreach ($staff as $member) {
// Use different notification message for provider vs admin
$notification_key = 'new_appointment_notification'; // Default for admins
if (!empty($appointment['provider_id']) && $member['staffid'] == $appointment['provider_id']) {
$notification_key = 'external_appointment_provider_notification'; // Specific for provider
}
add_notification([
'description' => $notification_key,
'touserid' => $member['staffid'],
'fromcompany' => 1,
'link' => 'appointly/appointments/view?appointment_id=' . $appointment_id,
]);
// Send email notification to admin staff about new appointment submission
$staff_member = $this->db->get_where(db_prefix() . 'staff', ['staffid' => $member['staffid']])->row();
if ($staff_member && $appointment) {
send_mail_template(
'appointly_appointment_new_appointment_submitted',
'appointly',
array_to_object($staff_member),
array_to_object($appointment)
);
}
$notified_users[] = $member['staffid'];
}
// Trigger real-time notifications
if (!empty($notified_users)) {
log_message('debug', 'EXTERNAL BOOKING Pusher recipients (admins + provider): ' . json_encode($notified_users));
pusher_trigger_notification($notified_users);
}
}
/**
* Notify staff involved in the appointment
*
* @param int $appointment_id
* @param array $data
*
* @return void
*/
private function notify_appointment_staff($appointment_id, $data)
{
$notified_users = [];
$staff = [];
// Check if this is a staff-only appointment
$is_staff_only = isset($data['source']) && $data['source'] === 'internal_staff';
if ($is_staff_only) {
// For staff-only appointments, get all attendees from the attendees table
$this->db->select('staff.staffid, staff.firstname, staff.lastname, staff.email');
$this->db->from(db_prefix() . 'staff as staff');
$this->db->join(
db_prefix() . 'appointly_attendees as attendees',
'attendees.staff_id = staff.staffid',
'inner'
);
$this->db->where('attendees.appointment_id', $appointment_id);
$this->db->where('staff.active', 1);
$staff = $this->db->get()->result_array();
} else {
// For regular appointments, notify the provider
if (!empty($data['provider_id'])) {
$this->db->select('staffid');
$this->db->where('staffid', $data['provider_id']);
$this->db->where('active', 1);
$staff = $this->db->get(db_prefix() . 'staff')->result_array();
}
}
// Get appointment data for email
$appointment = $this->get_appointment_data($appointment_id);
// Send notifications to all relevant staff
foreach ($staff as $member) {
// Don't notify the creator (they already know they created it)
if (isset($data['created_by']) && $member['staffid'] == $data['created_by']) {
continue;
}
add_notification([
'description' => 'new_appointment_notification',
'touserid' => $member['staffid'],
'fromcompany' => 1,
'link' => 'appointly/appointments/view?appointment_id=' . $appointment_id,
]);
// Send email notification to staff about new appointment
$staff_member = $this->db->get_where(db_prefix() . 'staff', ['staffid' => $member['staffid']])->row();
if ($staff_member && $appointment) {
send_mail_template(
'appointly_appointment_new_appointment_submitted',
'appointly',
array_to_object($staff_member),
array_to_object($appointment)
);
}
$notified_users[] = $member['staffid'];
}
// Trigger real-time notifications
if (!empty($notified_users)) {
pusher_trigger_notification($notified_users);
}
}
/**
* Send confirmation email to client after external appointment submission
*
* @param int $appointment_id
* @param array $data
*
* @return void
*/
private function send_client_submission_confirmation($appointment_id, $data)
{
try {
// Only send if we have a valid email
if (empty($data['email']) || !filter_var($data['email'], FILTER_VALIDATE_EMAIL)) {
return;
}
// Get full appointment data for email template
$appointment = $this->get_appointment_data($appointment_id);
if (!$appointment) {
log_message('error', 'Failed to get appointment data for client confirmation email: ' . $appointment_id);
return;
}
// Send confirmation email to client
$template = mail_template(
'appointly_appointment_submitted_to_contact',
'appointly',
array_to_object($appointment)
);
if ($template) {
$template->send();
}
} catch (Exception $e) {
// Log the error but don't interrupt the appointment creation
log_message('error', 'Failed to send client confirmation email for appointment ID ' . $appointment_id . ': ' . $e->getMessage());
}
}
public function update_appointment_private_notes($appointment_id, $notes)
{
$this->db->where('id', $appointment_id);
$this->db->update(db_prefix() . 'appointly_appointments', [
'notes' => $notes,
]);
return json_encode([
'success' => $this->db->affected_rows() !== 0,
'message' => _l('appointment_notes_updated'),
]);
}
/**
* Update existing appointment
*
* @param array $data
*
* @return bool
* @throws Exception
*/
public function update_appointment($data)
{
$originalAppointment = $this->get_appointment_data($data['appointment_id']);
$current_attendees = $this->atm->attendees($data['appointment_id']);
// Remove white spaces from phone number
if (isset($data['phone'])) {
$data['phone'] = trim($data['phone']);
}
// Handle external or lead appointment correctly
if (isset($data['contact_id']) && $data['contact_id'] == 0) {
unset($data['contact_id']);
}
// Handle reminder fields
$data = handleDataReminderFields($data);
// Format the date in SQL format
if (isset($data['date'])) {
$data['date'] = to_sql_date($data['date']);
}
if (! empty($data['description'])) {
$cleanContent = strip_tags($data['description']);
$data['description'] = $cleanContent;
}
// Format the update data
$updateData = [
'subject' => $data['subject'] ?? $originalAppointment['subject'],
'description' => $data['description'] ?? $originalAppointment['description'],
'notes' => $data['notes'] ?? $originalAppointment['notes'],
'date' => $data['date'] ?? $originalAppointment['date'],
'address' => $data['address'] ?? $originalAppointment['address'],
'status' => $data['status'] ?? $originalAppointment['status'],
'by_sms' => isset($data['by_sms']) ? '1' : '0',
'by_email' => isset($data['by_email']) ? '1' : '0',
'timezone' => $data['timezone'] ?? $originalAppointment['timezone'],
];
if (isset($data['status']) && $data['status'] !== 'cancelled') {
$updateData['cancel_notes'] = null;
}
// Handle relationship fields based on rel_type
if (isset($data['rel_type'])) {
if ($data['rel_type'] === 'lead_related') {
$updateData['source'] = 'lead_related';
// For lead-related appointments, we use contact_id to store the lead ID
if (! empty($data['rel_id'])) {
$updateData['contact_id'] = $data['rel_id'];
// Get lead details to populate name, email, phone
$this->load->model('leads_model');
$lead = $this->leads_model->get($data['rel_id']);
if ($lead) {
$updateData['name'] = $lead->name;
$updateData['email'] = $lead->email;
$updateData['phone'] = $lead->phonenumber;
}
} else {
// If no lead ID provided but type is lead_related, keep existing contact_id
$updateData['contact_id'] = $originalAppointment['contact_id'];
}
} elseif ($data['rel_type'] === 'internal' && isset($data['contact_id'])) {
$updateData['source'] = 'internal';
$updateData['contact_id'] = $data['contact_id'];
// Get contact details for internal appointments
$contact = $this->clients_model->get_contact($data['contact_id']);
if ($contact) {
$updateData['name'] = $contact->firstname . ' ' . $contact->lastname;
$updateData['email'] = $contact->email;
$updateData['phone'] = $contact->phonenumber;
}
} elseif ($data['rel_type'] === 'external') {
$updateData['source'] = 'external';
$updateData['contact_id'] = null;
// Add external contact information
if (isset($data['name'])) {
$updateData['name'] = $data['name'];
}
if (isset($data['email'])) {
$updateData['email'] = $data['email'];
}
if (isset($data['phone'])) {
$updateData['phone'] = $data['phone'];
}
} elseif ($data['rel_type'] === 'internal_staff') {
$updateData['source'] = 'internal_staff';
$updateData['contact_id'] = null;
}
}
// Handle service and provider
if (isset($data['service_id'])) {
$updateData['service_id'] = $data['service_id'];
// Get service duration for non-internal_staff appointments
if ($updateData['source'] !== 'internal_staff' && ! empty($updateData['service_id'])) {
$service = $this->get_service($updateData['service_id']);
if ($service) {
$updateData['duration'] = $service->duration;
}
}
}
// Track provider changes for notifications
$provider_changed = false;
$old_provider_id = null;
$new_provider_id = null;
if (isset($data['provider_id'])) {
$old_provider_id = $originalAppointment['provider_id'];
$new_provider_id = $data['provider_id'];
// Check if provider actually changed
if ($old_provider_id != $new_provider_id) {
$provider_changed = true;
}
$updateData['provider_id'] = $data['provider_id'];
}
// Track status changes for notifications
$status_changed = false;
$old_status = $originalAppointment['status'];
$new_status = isset($data['status']) ? $data['status'] : $old_status;
if (isset($data['status']) && $old_status != $new_status) {
$status_changed = true;
}
// Track date/time changes for client notifications
$datetime_changed = false;
$old_date = $originalAppointment['date'];
$old_start_hour = $originalAppointment['start_hour'];
$new_date = $updateData['date'];
$new_start_hour = $updateData['start_hour'] ?? $old_start_hour;
if ($old_date != $new_date || $old_start_hour != $new_start_hour) {
$datetime_changed = true;
log_message('debug', 'APPOINTMENT UPDATE: DateTime changed detected - Old: ' . $old_date . ' ' . $old_start_hour . ' → New: ' . $new_date . ' ' . $new_start_hour);
}
// Handle time fields
if (! empty($data['start_hour'])) {
$updateData['start_hour'] = $data['start_hour'];
}
if (! empty($data['end_hour'])) {
$updateData['end_hour'] = $data['end_hour'];
}
// For internal_staff, use provided duration
if (
isset($updateData['source'], $data['duration'])
&& $updateData['source'] === 'internal_staff'
) {
$updateData['duration'] = $data['duration'];
}
// Handle reminder settings
if (isset($data['reminder_before'])) {
$updateData['reminder_before'] = $data['reminder_before'];
}
if (isset($data['reminder_before_type'])) {
$updateData['reminder_before_type'] = $data['reminder_before_type'];
}
// Handle notification checkboxes (unchecked checkboxes don't send any value)
$updateData['by_sms'] = isset($data['by_sms']) ? 1 : 0;
$updateData['by_email'] = isset($data['by_email']) ? 1 : 0;
// If both notifications are disabled, clear reminder settings
if ($updateData['by_sms'] == 0 && $updateData['by_email'] == 0) {
$updateData['reminder_before'] = null;
$updateData['reminder_before_type'] = null;
}
// Process recurring data
if (isset($data['repeat_appointment'])) {
// transform the data for database
$data = $this->validateRecurringData($data, $originalAppointment);
// Add processed recurring fields to updateData
$recurringFields = ['recurring', 'recurring_type', 'repeat_every', 'custom_recurring', 'cycles', 'total_cycles', 'last_recurring_date'];
foreach ($recurringFields as $field) {
if (isset($data[$field])) {
$updateData[$field] = $data[$field];
}
}
} else {
// If repeat_appointment checkbox is not checked, clear all recurring settings
$updateData['recurring'] = 0;
$updateData['recurring_type'] = null;
$updateData['repeat_every'] = null;
$updateData['custom_recurring'] = null;
$updateData['cycles'] = null;
$updateData['total_cycles'] = null;
$updateData['last_recurring_date'] = null;
}
// Remove CSRF token and other non-database fields before update
$fieldsToRemove = ['ci_csrf_token', 'csrf_token_name', 'current_language'];
foreach ($fieldsToRemove as $field) {
if (isset($updateData[$field])) {
unset($updateData[$field]);
}
}
// Update the appointment
$this->db->where('id', $data['appointment_id']);
$this->db->update(db_prefix() . 'appointly_appointments', $updateData);
// Initialize attendee diff variables for use throughout the method
$new_attendees_diff = [];
$removed_attendees_diff = [];
// Handle attendees - for internal_staff appointments, attendees are required
if (isset($data['attendees']) && is_array($data['attendees'])) {
$attendees = $data['attendees'];
$new_attendees_diff = array_diff($attendees, $current_attendees);
$removed_attendees_diff = array_diff($current_attendees, $attendees);
// Update the attendees
$this->atm->update($data['appointment_id'], $attendees);
// Send notifications to new attendees
if (! empty($new_attendees_diff)) {
$new_attendees = [];
foreach ($new_attendees_diff as $new_attendee) {
$new_attendees[] = appointly_get_staff($new_attendee);
}
// Prepare data for notification
$notification_data = array_merge(
$data,
['id' => $data['appointment_id']]
);
$this->atm->send_notifications_to_new_attendees($new_attendees, $notification_data);
}
// Send notifications to removed attendees
if (! empty($removed_attendees_diff)) {
$appointment_data = $this->get_appointment_data($data['appointment_id']);
foreach ($removed_attendees_diff as $removed_attendee_id) {
$removed_attendee = appointly_get_staff($removed_attendee_id);
if (!empty($removed_attendee) && $removed_attendee_id != $appointment_data['created_by']) {
// Add in-app notification for removed attendee
add_notification([
'description' => 'appointment_attendee_removed',
'touserid' => $removed_attendee_id,
'fromcompany' => true,
'link' => 'appointly/appointments/view?appointment_id=' . $data['appointment_id'],
]);
// Send email notification to removed attendee
send_mail_template(
'appointly_appointment_attendee_removed',
'appointly',
array_to_object($removed_attendee),
array_to_object($appointment_data)
);
}
}
}
// Send notifications for update
$updateAppointment = $this->get_appointment_data($data['appointment_id']);
// Only send notifications if the appointment is approved and not cancelled or completed
if (
isset($updateAppointment['status'])
&& $updateAppointment['status'] != 'cancelled'
&& $updateAppointment['status'] != 'completed'
) {
// Send notifications to all attendees (staff)
foreach ($attendees as $staff_id) {
$staff = appointly_get_staff($staff_id);
if (! empty($staff)) {
// do not notify the creator
if ($staff_id == $updateAppointment['created_by']) {
continue;
}
// Skip attendee notifications if datetime changed (handled separately below)
// Also skip if this is specifically an attendee change - we'll handle that separately with targeted notifications
if (!$datetime_changed && empty($new_attendees_diff) && empty($removed_attendees_diff)) {
// This is a general appointment details change, notify all attendees
add_notification([
'description' => 'appointment_details_changed',
'touserid' => $staff_id,
'fromcompany' => true,
'link' => 'appointly/appointments/view?appointment_id=' . $data['appointment_id'],
]);
}
send_mail_template(
'appointly_appointment_updated_to_staff',
'appointly',
array_to_object($staff),
array_to_object($updateAppointment)
);
}
}
// Include removed attendees in pusher notifications
$all_notification_recipients = $attendees;
if (!empty($removed_attendees_diff)) {
$all_notification_recipients = array_merge($all_notification_recipients, $removed_attendees_diff);
$all_notification_recipients = array_unique($all_notification_recipients);
}
// Store notification recipients for consolidated pusher notification
$consolidated_pusher_recipients = $all_notification_recipients;
}
} else {
$this->atm->update($data['appointment_id'], []);
}
// Handle targeted attendee change notifications (only notify affected attendees + creator)
if (!$datetime_changed && (!empty($new_attendees_diff) || !empty($removed_attendees_diff))) {
$attendee_notification_recipients = [];
// Notify added attendees (with safety checks)
if (is_array($new_attendees_diff)) {
foreach ($new_attendees_diff as $added_staff_id) {
if (!empty($added_staff_id) && $added_staff_id != $updateAppointment['created_by']) {
add_notification([
'description' => 'appointment_attendees_changed',
'touserid' => $added_staff_id,
'fromcompany' => true,
'link' => 'appointly/appointments/view?appointment_id=' . $data['appointment_id'],
]);
$attendee_notification_recipients[] = $added_staff_id;
}
}
}
// Notify removed attendees (with safety checks)
if (is_array($removed_attendees_diff)) {
foreach ($removed_attendees_diff as $removed_staff_id) {
if (!empty($removed_staff_id) && $removed_staff_id != $updateAppointment['created_by']) {
add_notification([
'description' => 'appointment_attendees_changed',
'touserid' => $removed_staff_id,
'fromcompany' => true,
'link' => 'appointly/appointments/view?appointment_id=' . $data['appointment_id'],
]);
$attendee_notification_recipients[] = $removed_staff_id;
}
}
}
// Notify creator (if not external appointment and creator is staff)
if ($updateAppointment['source'] !== 'external' && !empty($updateAppointment['created_by'])) {
add_notification([
'description' => 'appointment_attendees_changed',
'touserid' => $updateAppointment['created_by'],
'fromcompany' => true,
'link' => 'appointly/appointments/view?appointment_id=' . $data['appointment_id'],
]);
$attendee_notification_recipients[] = $updateAppointment['created_by'];
}
// Store attendee notification recipients for consolidated pusher notification
if (!empty($attendee_notification_recipients)) {
log_message('debug', 'ATTENDEE CHANGE Pusher recipients: ' . json_encode(array_unique($attendee_notification_recipients)));
$consolidated_pusher_recipients = array_merge(
$consolidated_pusher_recipients ?? [],
$attendee_notification_recipients
);
}
}
// Handle provider change notifications (outside attendees block to work for all appointment types)
if ($provider_changed) {
$provider_notification_list = [];
// Only send notifications if the appointment is not cancelled or completed
if (isset($updateAppointment['status']) && $updateAppointment['status'] != 'cancelled' && $updateAppointment['status'] != 'completed') {
// Notify the old provider about being removed from the appointment
if ($old_provider_id && $old_provider_id != $updateAppointment['created_by']) {
$old_provider = appointly_get_staff($old_provider_id);
if (!empty($old_provider)) {
// Add notification for old provider
add_notification([
'description' => 'appointment_provider_removed',
'touserid' => $old_provider_id,
'fromcompany' => true,
'link' => 'appointly/appointments/view?appointment_id=' . $data['appointment_id'],
]);
// Send email notification to old provider
send_mail_template(
'appointly_appointment_provider_removed',
'appointly',
array_to_object($old_provider),
array_to_object($updateAppointment)
);
$provider_notification_list[] = $old_provider_id;
}
}
// Notify the new provider if provider was changed
if ($new_provider_id && $new_provider_id != $updateAppointment['created_by']) {
$new_provider = appointly_get_staff($new_provider_id);
if (!empty($new_provider)) {
// Add notification for new provider
add_notification([
'description' => 'appointment_provider_assigned',
'touserid' => $new_provider_id,
'fromcompany' => true,
'link' => 'appointly/appointments/view?appointment_id=' . $data['appointment_id'],
]);
// Send email notification to new provider
send_mail_template(
'appointly_appointment_provider_assigned',
'appointly',
array_to_object($new_provider),
array_to_object($updateAppointment)
);
$provider_notification_list[] = $new_provider_id;
}
}
// Store provider notification recipients for consolidated pusher notification
if (!empty($provider_notification_list)) {
$consolidated_pusher_recipients = array_merge(
$consolidated_pusher_recipients ?? [],
$provider_notification_list
);
}
}
}
// Send client notification for appointment updates that affect them
// This ensures external clients and clients without attendees also get notified
$client_affecting_changes = $provider_changed || $status_changed ||
(isset($data['date']) && $data['date'] != $originalAppointment['date']) ||
(isset($data['start_hour']) && $data['start_hour'] != $originalAppointment['start_hour']) ||
(isset($data['end_hour']) && $data['end_hour'] != $originalAppointment['end_hour']) ||
(isset($data['subject']) && $data['subject'] != $originalAppointment['subject']) ||
(isset($data['description']) && $data['description'] != $originalAppointment['description']);
// Send client email notification (consolidated to avoid duplicates)
$client_email_sent = false;
if ($client_affecting_changes && $updateAppointment && $updateAppointment['status'] != 'cancelled' && $updateAppointment['status'] != 'completed') {
// Send email notification to contact if they have an email (only if not already sent for datetime changes)
if (!empty($updateAppointment['email']) && !$datetime_changed) {
$template = mail_template('appointly_appointment_updated_to_contact', 'appointly', array_to_object($updateAppointment));
if ($template) {
$template->send();
$client_email_sent = true;
// Send SMS notification if by_sms is enabled
if (!empty($updateAppointment['phone']) && $updateAppointment['by_sms'] == 1) {
$merge_fields = $template->get_merge_fields();
$this->app_sms->trigger(
APPOINTLY_SMS_APPOINTMENT_UPDATED_TO_CLIENT,
$updateAppointment['phone'],
$merge_fields
);
}
}
}
}
// Update service link if service_id is provided
if (! empty($data['service_id'])) {
// First delete existing service links for this appointment
$this->db->where('appointment_id', $data['appointment_id']);
$this->db->delete(db_prefix() . 'appointly_appointment_services');
// Then create a new link
$this->link_appointment_service($data['appointment_id'], $data['service_id']);
}
// Handle custom fields
if (isset($data['custom_fields'])) {
$custom_fields = $data['custom_fields'];
handle_custom_fields_post($data['appointment_id'], $custom_fields);
}
// Handle Google Calendar integration (skip for cancelled appointments to prevent Pusher conflicts)
if ($new_status !== 'cancelled' && isset($data['google_event_id']) && appointlyGoogleAuth()) {
// If appointment is in Google Calendar, update it
updateAppointmentToGoogleCalendar(array_merge($data, [
'id' => $data['appointment_id'],
'date' => $updateData['date'],
'start_hour' => $updateData['start_hour'],
'end_hour' => $updateData['end_hour'],
]));
}
// Handle special status changes that need extra processing
if ($status_changed) {
if ($new_status === 'in-progress') {
// Trigger approval notifications for in-progress status
$this->appointment_approve_notification_and_sms_triggers($data['appointment_id']);
} elseif ($new_status === 'cancelled') {
// Use the existing cancellation system
$this->send_cancellation_notifications($data['appointment_id']);
} elseif (in_array($new_status, ['completed', 'no-show'])) {
// Use my new status change system for these
$this->send_status_change_notifications($data['appointment_id'], $new_status, $old_status);
}
}
// Handle date/time changes - notify ALL relevant staff and client
if ($datetime_changed) {
// Get all staff to notify (attendees + provider)
$staff_to_notify = [];
// Add all attendees (ensure $attendees is array)
if (is_array($attendees)) {
foreach ($attendees as $staff_id) {
if (!empty($staff_id)) {
$staff_to_notify[] = $staff_id;
}
}
}
// Add provider if not already in attendees
if (!empty($updateAppointment['provider_id'])) {
if (!in_array($updateAppointment['provider_id'], $staff_to_notify)) {
$staff_to_notify[] = $updateAppointment['provider_id'];
}
}
$staff_to_notify = array_unique($staff_to_notify);
// Send in-app notifications to ALL relevant staff
foreach ($staff_to_notify as $staff_id) {
if ($staff_id != $updateAppointment['created_by']) { // Don't notify creator
add_notification([
'description' => 'appointment_datetime_changed',
'touserid' => $staff_id,
'fromcompany' => true,
'link' => 'appointly/appointments/view?appointment_id=' . $data['appointment_id'],
]);
}
}
// Store datetime change recipients for consolidated pusher notification
if (!empty($staff_to_notify)) {
log_message('debug', 'DATETIME CHANGE Pusher recipients: ' . json_encode($staff_to_notify));
$consolidated_pusher_recipients = array_merge(
$consolidated_pusher_recipients ?? [],
$staff_to_notify
);
}
// Send email notification to client (only once for datetime changes)
if (!empty($updateAppointment['email']) && !$client_email_sent) {
$template = mail_template('appointly_appointment_updated_to_contact', 'appointly', array_to_object($updateAppointment));
if ($template) {
$template->send();
$client_email_sent = true;
}
}
}
// CONSOLIDATED PUSHER NOTIFICATION - Send only once at the end
if (!empty($consolidated_pusher_recipients)) {
$unique_recipients = array_unique($consolidated_pusher_recipients);
log_message('debug', 'CONSOLIDATED PUSHER notification recipients: ' . json_encode($unique_recipients));
pusher_trigger_notification($unique_recipients);
}
// LOG NOTIFICATION SUMMARY for debugging
$notification_summary = [
'appointment_id' => $data['appointment_id'],
'changes' => [
'datetime' => $datetime_changed,
'attendees' => (!empty($new_attendees_diff) || !empty($removed_attendees_diff)),
'provider' => $provider_changed,
'status' => $status_changed,
'client_affecting' => $client_affecting_changes ?? false
],
'notifications_sent' => [
'pusher_recipients' => $unique_recipients ?? [],
'client_email_sent' => $client_email_sent ?? false,
'staff_email_count' => isset($attendees) ? count($attendees) : 0
]
];
log_message('info', 'APPOINTMENT UPDATE SUMMARY: ' . json_encode($notification_summary));
return true;
}
/**
* Get single service by ID
*
* @param int $service_id Service ID
*
* @return object|null Service object or null if not found
*/
public function get_service($service_id)
{
if (! $service_id) {
return null;
}
$this->db->select('*');
$this->db->from(db_prefix() . 'appointly_services');
$this->db->where('id', $service_id);
return $this->db->get()->row();
}
/**
* Delete appointment
*
* @param $id
*
* @return array
*/
public function delete_appointment($id)
{
// For Google two-way sync appointments that are not in database (direct Google event ID)
if (! is_numeric($id)) {
if (appointlyGoogleAuth()) {
$this->load->model('googlecalendar');
$this->googlecalendar->deleteEvent($id);
// Check if there's a matching event in the database to delete
$this->db->where('google_event_id', $id);
$appointment = $this->db->get(db_prefix() . 'appointly_appointments')->row_array();
if ($appointment) {
// Now delete the database record with the actual appointment ID
return $this->delete_appointment($appointment['id']);
}
return [
'success' => true,
'message' => _l('appointment_deleted'),
];
}
return [
'success' => false,
'message' => _l('appointment_delete_failed'),
];
}
// Regular appointment deletion logic
$this->db->where('id', $id);
$appointment = $this->db->get(db_prefix() . 'appointly_appointments')->row_array();
if (! $appointment) {
return [
'success' => false,
'message' => _l('appointment_not_found'),
];
}
// Delete from Google Calendar if enabled and we have a Google event ID
if (! empty($appointment['google_event_id']) && get_option('appointly_also_delete_in_google_calendar') && appointlyGoogleAuth()) {
// Only delete from Google if current user added it or is admin
if ($appointment['google_added_by_id'] == get_staff_user_id() || is_admin()) {
$this->load->model('googlecalendar');
$this->googlecalendar->deleteEvent($appointment['google_event_id']);
}
}
// Delete related records from appointly_appointment_services table
$this->db->where('appointment_id', $id);
$this->db->delete(db_prefix() . 'appointly_appointment_services');
// Delete attendees
$this->atm->deleteAll($id);
// Delete the appointment - apply correct permission logic
$this->db->where('id', $id);
// If not admin and has deleted permission, restrict to only appointments created by this user
if (! is_admin() && staff_can('delete', 'appointments')) {
$this->db->where('created_by', get_staff_user_id());
} elseif (! staff_can('delete', 'appointments')) {
// No delete permission at all
return ['success' => false, 'message' => _l('access_denied')];
}
$this->db->delete(db_prefix() . 'appointly_appointments');
if ($this->db->affected_rows() !== 0) {
return [
'success' => true,
'message' => _l('appointment_deleted'),
];
}
return [
'success' => false,
'message' => _l('appointment_delete_failed'),
];
}
/**
* Get today's appointments
*
* @return array
*/
public function fetch_todays_appointments()
{
$date = new DateTime();
$today = $date->format('Y-m-d');
// Check if user has any appointments permissions at all
if (!staff_can('view', 'appointments')) {
return []; // No permissions = no appointments
}
if (! is_admin()) {
// All non-admin staff can only see appointments they're connected to:
// 1. Created by them, 2. Assigned as provider, 3. Listed as attendee
$this->db->where('(created_by=' . get_staff_user_id()
. ' OR provider_id=' . get_staff_user_id()
. ' OR id IN (SELECT appointment_id FROM ' . db_prefix() . 'appointly_attendees WHERE staff_id=' . get_staff_user_id() . '))');
}
$this->db->where('date', $today);
if ($_SERVER['REQUEST_URI'] != '/admin/appointly/appointments') {
$this->db->where('source !=', 'internal_staff');
}
$dbAppointments = $this->db->get(db_prefix() . 'appointly_appointments')->result_array();
// Extract all Google event IDs from database appointments to avoid duplicates
$googleEventIds = [];
foreach ($dbAppointments as $appointment) {
if (! empty($appointment['google_event_id'])) {
$googleEventIds[] = $appointment['google_event_id'];
}
}
appointlyGoogleAuth();
$googleCalendarAppointments = [];
$isGoogleAuthenticatedAndShowInTable = appointlyGoogleAuth() && get_option('appointments_googlesync_show_in_table');
if ($isGoogleAuthenticatedAndShowInTable) {
$googleCalendarAppointments = appointlyGetGoogleCalendarData();
// Filter Google calendar appointments to only include today's appointments
$googleCalendarAppointments = array_filter(
$googleCalendarAppointments,
static function ($appointment) use ($today, $googleEventIds) {
// Check if this is today's appointment
$appointmentDate = $appointment['date'] ?? '';
$isToday = strpos($appointmentDate, $today) !== false || strpos($appointmentDate, (string) ($today)) !== false;
// Check if this appointment is not already in our database
$isNotInDatabase = empty($appointment['id']) && (! isset($appointment['google_event_id'])
|| ! in_array($appointment['google_event_id'], $googleEventIds));
return $isToday && $isNotInDatabase;
}
);
// Ensure each Google calendar appointment has source='google'
foreach ($googleCalendarAppointments as &$appointment) {
$appointment['source'] = 'google';
}
}
return array_merge($dbAppointments, $googleCalendarAppointments);
}
/**
* Get all appointment data for calendar event
*
* @param string $start
* @param string $end
* @param array $data
*
* @return array
*/
public function get_calendar_data($start, $end, $data)
{
// Get appointments from database with left join to services table
$this->db->select('a.*, s.name as service_name, s.color as service_color, s.duration as service_duration, a.status, st.firstname as provider_firstname, st.lastname as provider_lastname');
$this->db->from(db_prefix() . 'appointly_appointments a');
$this->db->join(
db_prefix() . 'appointly_services s',
's.id = a.service_id',
'left'
);
$this->db->join(
db_prefix() . 'staff st',
'st.staffid = a.provider_id',
'left'
);
$this->db->where('a.status !=', 'cancelled');
$this->db->where('a.status !=', 'completed');
if (! is_client_logged_in()) {
// Check if user has any appointments permissions at all
if (!staff_can('view', 'appointments')) {
$this->db->where('1=0'); // No permissions = no results
} elseif (! is_admin()) {
// Non-admin staff can only see appointments they're connected to:
// 1. Created by them, 2. Assigned as provider, 3. Listed as attendee
$this->db->where('(a.created_by=' . get_staff_user_id()
. ' OR a.provider_id=' . get_staff_user_id()
. ' OR a.id IN (SELECT appointment_id FROM '
. db_prefix()
. 'appointly_attendees WHERE staff_id='
. get_staff_user_id() . '))');
}
} else {
// Client filtering: show only appointments for the logged-in contact
$contact_id = get_contact_user_id();
$contact = $this->db->select('email')
->where('id', $contact_id)
->get(db_prefix() . 'contacts')
->row();
$this->db->group_start();
$this->db->where('a.contact_id', $contact_id);
if ($contact && !empty($contact->email)) {
$this->db->or_where('a.email', $contact->email);
}
$this->db->group_end();
}
$this->db->where('(CONCAT(a.date, " ", a.start_hour) BETWEEN "' . $start . '" AND "' . $end . '")');
$appointments = $this->db->get()->result_array();
// Decode subject html entities before sending to calendar
foreach ($appointments as $key => $appointment) {
$appointments[$key]['subject'] = html_entity_decode($appointment['subject']);
}
// Clear any lingering references
unset($appointment);
// Collect Google event IDs from database to avoid duplicates
$googleEventIds = [];
foreach ($appointments as $appointment) {
if (! empty($appointment['google_event_id'])) {
$googleEventIds[] = $appointment['google_event_id'];
}
}
// Get Google Calendar appointments if enabled
$googleCalendarAppointments = [];
$isGoogleAuthenticatedAndShowInTable = appointlyGoogleAuth() && get_option('appointments_googlesync_show_in_table');
if ($isGoogleAuthenticatedAndShowInTable) {
$googleData = appointlyGetGoogleCalendarData();
// Filter out Google events that already exist in our database
// and ensure they fall within the calendar date range
foreach ($googleData as $googleEvent) {
$isDuplicate = false;
// Check if this Google event already exists in database
foreach ($appointments as $dbAppointment) {
if (
// Match by Google event ID
(isset($googleEvent['google_event_id']) && $googleEvent['google_event_id'] === $dbAppointment['google_event_id']) ||
// Match by subject and date/time (fallback for events without google_event_id)
(isset($googleEvent['subject']) && isset($googleEvent['date']) && isset($googleEvent['start_hour']) &&
$googleEvent['subject'] === $dbAppointment['subject'] &&
$googleEvent['date'] === $dbAppointment['date'] &&
$googleEvent['start_hour'] === $dbAppointment['start_hour'])
) {
$isDuplicate = true;
break;
}
}
// Only add Google event if it's not a duplicate and within date range
if (
!$isDuplicate &&
isset($googleEvent['date']) &&
strtotime($googleEvent['date']) >= strtotime($start) &&
strtotime($googleEvent['date']) <= strtotime($end)
) {
$googleEvent['source'] = 'google';
$googleCalendarAppointments[] = $googleEvent;
}
}
}
// Merge database appointments with Google Calendar appointments
$allAppointments = array_merge(
$appointments,
$googleCalendarAppointments
);
foreach ($allAppointments as $appointment) {
$calendarEvent = [
'id' => 'appointly_' . ($appointment['id'] ?? md5($appointment['google_event_id'] ?? uniqid('', true))),
'title' => $appointment['subject'] ?? $appointment['title'] ?? 'Untitled',
'start' => $appointment['date'] . ' ' . ($appointment['start_hour'] ?? '00:00:00'),
'end' => $appointment['date'] . ' ' . ($appointment['end_hour'] ?? '00:00:00'),
'allDay' => false,
];
// Base URL for viewing appointment
if (is_client_logged_in()) {
$calendarEvent['url'] = isset($appointment['hash'])
? appointly_get_appointment_url($appointment['hash'])
: $appointment['google_calendar_link'] ?? '';
} else {
$calendarEvent['url'] = isset($appointment['id'])
? admin_url('appointly/appointments/view?appointment_id=' . $appointment['id'])
: $appointment['google_calendar_link'] ?? '';
}
// Format appointment time - respect system time format setting
$time_format = get_option('time_format') == 24 ? 'H:i' : 'g:i A';
$appointmentTime = isset($appointment['start_hour']) ?
date($time_format, strtotime($appointment['start_hour'])) : '';
if (! empty($appointment['end_hour'])) {
$appointmentTime .= ' - ' . date($time_format, strtotime($appointment['end_hour']));
}
// Build enhanced tooltip content with beautiful HTML structure
$tooltipContent = $this->buildEnhancedTooltip($appointment, $appointmentTime);
// Determine color based on service or source
if (! empty($appointment['service_color'])) {
// Use service color from join
$calendarEvent['color'] = $appointment['service_color'];
} elseif (! empty($appointment['service_id'])) {
// Fallback to service lookup
$service = $this->get_service($appointment['service_id']);
if ($service && !empty($service->color)) {
$calendarEvent['color'] = $service->color;
}
} else {
// Default colors based on source
if (isset($appointment['source'])) {
switch ($appointment['source']) {
case 'google':
$calendarEvent['color'] = '#4285F4';
break;
case 'lead_related':
$calendarEvent['color'] = '#F4B400';
break;
case 'internal_staff':
$calendarEvent['color'] = '#34A853';
break;
default:
$calendarEvent['color'] = '#28B8DA';
}
} else {
$calendarEvent['color'] = '#28B8DA';
}
}
$calendarEvent['_tooltip'] = $tooltipContent;
$calendarEvent['appointlyData'] = $appointment;
// Prevent duplicate appointments when hook is called multiple times
$appointmentExists = false;
$currentAppointmentId = $calendarEvent['id'];
foreach ($data as $existingEvent) {
if (isset($existingEvent['id']) && $existingEvent['id'] == $currentAppointmentId) {
$appointmentExists = true;
break;
}
}
if (!$appointmentExists) {
$data[] = $calendarEvent;
}
}
return $data;
}
/**
* Build enhanced tooltip content with beautiful HTML structure
*/
private function buildEnhancedTooltip($appointment, $appointmentTime)
{
$title = $appointment['subject'] ?? $appointment['title'] ?? 'Untitled';
// Build a simple text-only tooltip for the title attribute
$tooltip = $title;
// Add service info
if (!empty($appointment['service_name'])) {
$tooltip .= "\n" . $appointment['service_name'];
}
// Add time info
if (!empty($appointmentTime)) {
$tooltip .= "\n" . $appointmentTime;
if (!empty($appointment['duration'])) {
$tooltip .= ' (' . $appointment['duration'] . ' min)';
}
}
// Add provider info
if (!empty($appointment['provider_firstname']) && !empty($appointment['provider_lastname'])) {
$tooltip .= "\nProvider: " . $appointment['provider_firstname'] . ' ' . $appointment['provider_lastname'];
}
// Add status
if (!empty($appointment['status'])) {
$tooltip .= "\nStatus: " . ucfirst(str_replace('-', ' ', $appointment['status']));
}
// Add contact info
if (!empty($appointment['email'])) {
$tooltip .= "\nEmail: " . $appointment['email'];
}
if (!empty($appointment['phone'])) {
$tooltip .= "\nPhone: " . $appointment['phone'];
}
// Add client name
if (!empty($appointment['name'])) {
$tooltip .= "\nClient: " . $appointment['name'];
}
return $tooltip;
}
/**
* Get Bootstrap badge class for appointment status
*/
private function getBootstrapStatusClass($status)
{
switch ($status) {
case 'completed':
return 'success';
case 'pending':
return 'warning';
case 'cancelled':
case 'no-show':
return 'danger';
case 'in-progress':
return 'primary';
default:
return 'secondary';
}
}
/**
* Fetch contact data and apply to fields in modal
*
* @param string $contact_id
*
* @param $is_lead
*
* @return mixed
*/
public function apply_contact_data($contact_id, $is_lead)
{
if ($is_lead == 'false' || ! $is_lead) {
return $this->clients_model->get_contact($contact_id);
}
$this->load->model('leads_model');
return $this->leads_model->get($contact_id);
}
/**
* Cancel appointment
*
* @param string $appointment_id
*
* @return void
*/
public function cancel_appointment($appointment_id)
{
$success = $this->change_appointment_status($appointment_id, 'cancelled');
if ($success) {
$appointment = $this->get_appointment_data($appointment_id);
$notified_users = [];
$attendees = $appointment['attendees'];
foreach ($attendees as $staff) {
if ($staff['staffid'] === get_staff_user_id()) {
continue;
}
add_notification([
'description' => 'appointment_is_cancelled',
'touserid' => $staff['staffid'],
'fromcompany' => true,
'link' => 'appointly/appointments/view?appointment_id=' . $appointment['id'],
]);
$notified_users[] = $staff['staffid'];
send_mail_template(
'appointly_appointment_notification_cancelled_to_staff',
'appointly',
array_to_object($appointment),
array_to_object($staff)
);
}
pusher_trigger_notification(array_unique($notified_users));
$template = mail_template(
'appointly_appointment_notification_cancelled_to_contact',
'appointly',
array_to_object($appointment)
);
if (! empty($appointment['phone'])) {
$merge_fields = $template->get_merge_fields();
$this->app_sms->trigger(
APPOINTLY_SMS_APPOINTMENT_CANCELLED_TO_CLIENT,
$appointment['phone'],
$merge_fields
);
}
@$template->send();
}
header('Content-Type: application/json');
echo json_encode(['success' => $success]);
}
/**
* Generate available time slots for a provider on a specific date with a specific service
*
* @param $appointment_id
* @param $status
*
* @return bool Array of available time slots
*/
public function change_appointment_status($appointment_id, $status)
{
// First, get the current appointment data
$this->db->where('id', $appointment_id);
$current = $this->db->get(db_prefix() . 'appointly_appointments')->row_array();
if (!$current) {
return false;
}
$update_data = [
'status' => $status,
'cancel_notes' => null,
];
if ($status !== 'cancelled') {
$update_data['cancel_notes'] = null;
}
$this->db->where('id', $appointment_id);
$this->db->update(db_prefix() . 'appointly_appointments', $update_data);
$affected = $this->db->affected_rows();
// Send notifications for status changes
if ($affected > 0) {
if ($status === 'cancelled') {
// Handle cancellation notifications
$this->send_cancellation_notifications($appointment_id);
} elseif (in_array($status, ['completed', 'no-show'])) {
// Handle completed and no-show notifications
$this->send_status_change_notifications($appointment_id, $status, $current['status']);
}
// Note: 'in-progress' is handled in the controller before calling this method
}
return $affected > 0;
}
/**
* Send cancellation notifications (extracted from cancel_appointment method)
*
* @param int $appointment_id
* @return void
*/
private function send_cancellation_notifications($appointment_id)
{
$appointment = $this->get_appointment_data($appointment_id);
$notified_users = [];
// Get notification recipients (attendees + provider)
$notification_recipients = [];
// Add attendees
if (!empty($appointment['attendees'])) {
foreach ($appointment['attendees'] as $attendee) {
if ($attendee['staffid'] && $attendee['staffid'] != $appointment['created_by']) {
$notification_recipients[] = $attendee['staffid'];
}
}
}
// Add provider if different from creator and not already in attendees
if ($appointment['provider_id'] && $appointment['provider_id'] != $appointment['created_by']) {
if (!in_array($appointment['provider_id'], $notification_recipients)) {
$notification_recipients[] = $appointment['provider_id'];
}
}
// Send notifications to all recipients
foreach ($notification_recipients as $staff_id) {
if ($staff_id === get_staff_user_id()) {
continue;
}
add_notification([
'description' => 'appointment_is_cancelled',
'touserid' => $staff_id,
'fromcompany' => true,
'link' => 'appointly/appointments/view?appointment_id=' . $appointment['id'],
]);
$notified_users[] = $staff_id;
$staff = appointly_get_staff($staff_id);
if (!empty($staff)) {
send_mail_template(
'appointly_appointment_notification_cancelled_to_staff',
'appointly',
array_to_object($appointment),
array_to_object($staff)
);
} else {
log_message('error', 'Could not get staff data for ID: ' . $staff_id);
}
}
pusher_trigger_notification(array_unique($notified_users));
$template = mail_template(
'appointly_appointment_notification_cancelled_to_contact',
'appointly',
array_to_object($appointment)
);
if (! empty($appointment['phone'])) {
$merge_fields = $template->get_merge_fields();
$this->app_sms->trigger(
APPOINTLY_SMS_APPOINTMENT_CANCELLED_TO_CLIENT,
$appointment['phone'],
$merge_fields
);
}
@$template->send();
}
/**
* Send notifications when appointment status changes
*
* @param int $appointment_id
* @param string $new_status
* @param string $old_status
* @return void
*/
private function send_status_change_notifications($appointment_id, $new_status, $old_status)
{
// Get full appointment data
$appointment = $this->get_appointment_data($appointment_id);
if (!$appointment) {
return;
}
// Don't send notifications if status didn't actually change
if ($new_status === $old_status) {
return;
}
// Get notification recipients (attendees + provider)
$notification_recipients = [];
// Add attendees
if (!empty($appointment['attendees'])) {
foreach ($appointment['attendees'] as $attendee) {
if ($attendee['staffid'] && $attendee['staffid'] != $appointment['created_by']) {
$notification_recipients[] = $attendee['staffid'];
}
}
}
// Add provider if different from creator and not already in attendees
if ($appointment['provider_id'] && $appointment['provider_id'] != $appointment['created_by']) {
if (!in_array($appointment['provider_id'], $notification_recipients)) {
$notification_recipients[] = $appointment['provider_id'];
}
}
// Determine notification description based on status
$notification_descriptions = [
'completed' => 'appointment_marked_as_completed',
'cancelled' => 'appointment_marked_as_cancelled',
'no-show' => 'appointment_marked_as_no_show'
];
$notification_description = $notification_descriptions[$new_status] ?? 'appointment_status_changed';
// Send in-app notifications to staff
foreach ($notification_recipients as $staff_id) {
add_notification([
'description' => $notification_description,
'touserid' => $staff_id,
'fromcompany' => true,
'link' => 'appointly/appointments/view?appointment_id=' . $appointment_id,
]);
}
// Send email notifications based on status
$this->send_status_change_emails($appointment, $new_status, $notification_recipients);
// Send pusher notifications
if (!empty($notification_recipients)) {
log_message('debug', 'STATUS CHANGE (' . $new_status . ') Pusher recipients: ' . json_encode($notification_recipients));
pusher_trigger_notification($notification_recipients);
}
}
/**
* Send email notifications for status changes
*
* @param array $appointment
* @param string $status
* @param array $staff_recipients
* @return void
*/
private function send_status_change_emails($appointment, $status, $staff_recipients)
{
// Email template mapping - use existing templates with correct naming
$email_templates = [
'completed' => 'appointly_appointment_completed_to_staff',
'cancelled' => 'appointly_appointment_notification_cancelled_to_staff',
'no-show' => 'appointly_appointment_no_show_to_staff'
];
$template_name = $email_templates[$status] ?? null;
if (!$template_name) {
return;
}
// Send emails to staff recipients
foreach ($staff_recipients as $staff_id) {
$staff = appointly_get_staff($staff_id);
if (!empty($staff)) {
// Debug log to see who's getting staff emails
send_mail_template(
$template_name,
'appointly',
array_to_object($staff),
array_to_object($appointment)
);
}
}
// Send email to client if they have email and notifications are enabled
if (!empty($appointment['email'])) {
$client_templates = [
'completed' => 'appointly_appointment_completed_to_contact',
'cancelled' => 'appointly_appointment_notification_cancelled_to_contact',
'no-show' => 'appointly_appointment_no_show_to_contact'
];
$client_template_name = $client_templates[$status] ?? null;
if (!$client_template_name) {
return;
}
// Debug log to see client email
$template = mail_template($client_template_name, 'appointly', array_to_object($appointment));
if ($template) {
$template->send();
// Send SMS if enabled and phone exists
if (!empty($appointment['phone']) && $appointment['by_sms'] == 1) {
$sms_triggers = [
'completed' => APPOINTLY_SMS_APPOINTMENT_UPDATED_TO_CLIENT,
'cancelled' => APPOINTLY_SMS_APPOINTMENT_CANCELLED_TO_CLIENT,
'no-show' => APPOINTLY_SMS_APPOINTMENT_UPDATED_TO_CLIENT
];
$sms_trigger = $sms_triggers[$status] ?? APPOINTLY_SMS_APPOINTMENT_UPDATED_TO_CLIENT;
$this->app_sms->trigger(
$sms_trigger,
$appointment['phone'],
$template->get_merge_fields()
);
}
}
}
}
/**
* Approve appointment
*
* @param string $appointment_id
*
* @return bool
*/
public function approve_appointment($appointment_id)
{
$this->appointment_approve_notification_and_sms_triggers($appointment_id);
$success = $this->change_appointment_status($appointment_id, 'in-progress');
// Update external_notification_date for tracking purposes
if ($success) {
$this->db->where('id', $appointment_id);
$this->db->update(db_prefix() . 'appointly_appointments', ['external_notification_date' => date('Y-m-d'),]);
// Auto-add to Google Calendar if setting is enabled
if (get_option('appointly_auto_add_to_google_on_approval') == '1' && appointlyGoogleAuth()) {
$this->auto_add_appointment_to_google_calendar($appointment_id);
}
}
return $success;
}
/**
* Automatically add appointment to Google Calendar when approving
*
* @param string $appointment_id
*
* @return void
*/
public function auto_add_appointment_to_google_calendar($appointment_id)
{
try {
// Get appointment data
$appointment = $this->get_appointment_data($appointment_id);
if (!$appointment) {
log_message('error', 'Auto Google Calendar: Appointment not found - ID: ' . $appointment_id);
return;
}
// Skip if already added to Google Calendar
if (!empty($appointment['google_event_id'])) {
log_message('info', 'Auto Google Calendar: Appointment already has Google event - ID: ' . $appointment_id);
return;
}
// Get attendees (staff members assigned to this appointment)
$attendees = $this->atm->get($appointment_id);
if (empty($attendees)) {
log_message('info', 'Auto Google Calendar: No attendees found for appointment - ID: ' . $appointment_id);
return;
}
// Prepare attendee emails
$attendee_emails = [];
foreach ($attendees as $attendee) {
if (!empty($attendee['staff_id'])) {
$staff = $this->staff_model->get($attendee['staff_id']);
if ($staff && filter_var($staff->email, FILTER_VALIDATE_EMAIL)) {
$attendee_emails[] = $staff->email;
}
}
}
// Add appointment to Google Calendar
if (!empty($attendee_emails)) {
$googleEvent = insertAppointmentToGoogleCalendar($appointment, $attendee_emails);
if ($googleEvent && !empty($googleEvent['google_event_id'])) {
// Update appointment with Google Calendar data
$update_data = [
'google_event_id' => $googleEvent['google_event_id'],
'google_calendar_link' => $googleEvent['htmlLink'],
'google_added_by_id' => get_staff_user_id()
];
if (isset($googleEvent['hangoutLink'])) {
$update_data['google_meet_link'] = $googleEvent['hangoutLink'];
}
$this->db->where('id', $appointment_id);
$this->db->update(db_prefix() . 'appointly_appointments', $update_data);
log_message('info', 'Auto Google Calendar: Successfully added appointment to Google Calendar - ID: ' . $appointment_id . ', Event ID: ' . $googleEvent['google_event_id']);
}
}
} catch (Exception $e) {
log_message('error', 'Auto Google Calendar: Exception occurred - ID: ' . $appointment_id . ', Error: ' . $e->getMessage());
}
}
/**
* Check for external client hash token
*
* @param string $hash
*
* @return bool|void
*/
public function get_public_appointment_by_hash($hash)
{
$this->db->where('hash', $hash);
$appointment = $this->db->get(db_prefix() . 'appointly_appointments')
->row_array();
if ($appointment) {
$appointment['feedbacks']
= json_decode(get_option('appointly_default_feedbacks'));
$appointment['selected_contact'] = $appointment['contact_id'];
// Handle different appointment sources properly
if (! empty($appointment['selected_contact'])) {
if ($appointment['source'] == 'lead_related') {
// For lead-related appointments, contact_id actually contains the lead ID
$this->load->model('leads_model');
$lead = $this->leads_model->get($appointment['selected_contact']);
if ($lead) {
$appointment['details'] = [
'email' => $lead->email ?? '',
'phone' => $lead->phonenumber ?? '',
'full_name' => $lead->name ?? '',
'company_name' => $lead->company ?? '',
'userid' => null // Leads don't have userid like contacts
];
}
} elseif ($appointment['source'] == 'internal') {
// For internal appointments, use the contact details function
$appointment['details']
= get_appointment_contact_details($appointment['selected_contact']);
}
// For external, internal_staff, google, and other sources,
// we don't need to fetch additional details as they're already in the appointment data
}
$appointment['attendees'] = $this->atm->get($appointment['id']);
return $appointment;
}
return false;
}
/**
* Marks appointment as finished
*
* @param $id
*
* @return void
*/
public function mark_as_finished($id)
{
$success = $this->change_appointment_status($id, 'completed');
header('Content-Type: application/json');
echo json_encode(['success' => $success]);
}
/**
* Marks appointment as ongoing
*
* @param $id
*
* @return boolean
*/
public function mark_as_ongoing($id)
{
try {
// First, notify users about the status change
$this->appointment_approve_notification_and_sms_triggers($id);
} catch (Exception $e) {
// Log the error but continue with status change
log_message('error', 'Error in appointment notifications: ' . $e->getMessage());
}
// Update the appointment status
$success = $this->change_appointment_status($id, 'in-progress');
// Explicitly log the operation result
log_message(
'info',
'Appointment status change attempted - ID: ' . $id . ', Success: '
. ($success ? 'true' : 'false')
);
return $success;
}
/**
* Mark appointment as no-show
*
* @param int $id
*
* @return bool
*/
public function mark_as_no_show($id)
{
$success = $this->change_appointment_status($id, 'no-show');
// Explicitly log the operation result
log_message(
'info',
'Appointment marked as no-show - ID: ' . $id . ', Success: ' . ($success
? 'true' : 'false')
);
return $success;
}
/**
* Handles appointment cancellation and updates status
*
* @param string $hash
* @param string $notes
*
* @return array
*/
public function appointment_cancellation_handler($hash, $notes)
{
// Get appointment data first for notifications
$this->db->where('hash', $hash);
$appointment = $this->db->get(db_prefix() . 'appointly_appointments')->row_array();
if (!$appointment) {
return [
'response' => [
'message' => _l('appointment_not_found'),
'success' => false,
],
];
}
// Only set cancel_notes - do NOT change status to cancelled
// This creates a "pending cancellation" that staff must approve
$this->db->where('hash', $hash);
$this->db->update(db_prefix() . 'appointly_appointments', [
'cancel_notes' => $notes,
// Status remains unchanged - staff will approve/deny the cancellation
]);
if ($this->db->affected_rows() !== 0) {
// Send notifications to staff about the cancellation request
$this->send_client_cancellation_request_notifications($appointment, $notes);
return [
'response' => [
'message' => _l('appointments_thank_you_cancel_request'),
'success' => true,
],
];
}
return [
'response' => [
'message' => _l('appointment_error_occurred'),
'success' => false,
],
];
}
/**
* Send notifications when client requests cancellation
* @param array $appointment
* @param string $reason
*/
private function send_client_cancellation_request_notifications($appointment, $reason)
{
try {
// Load the appointly helper
$CI = &get_instance();
$CI->load->helper(['appointly/appointly']);
// Get appointment full data with attendees
$appointment_data = $this->get_appointment_data($appointment['id']);
if (!$appointment_data) {
return;
}
$client_name = !empty($appointment['name']) ? $appointment['name'] : 'Client';
$appointment_subject = !empty($appointment['subject']) ? $appointment['subject'] : 'Appointment';
// Get all relevant staff to notify
$staff_to_notify = [];
$notifications_to_send = [];
// 1. Appointment creator
if (!empty($appointment['created_by'])) {
$creator = appointly_get_staff($appointment['created_by']);
if ($creator) {
$staff_to_notify[$creator['staffid']] = $creator;
}
}
// 2. Provider
if (!empty($appointment['provider_id']) && $appointment['provider_id'] != $appointment['created_by']) {
$provider = appointly_get_staff($appointment['provider_id']);
if ($provider) {
$staff_to_notify[$provider['staffid']] = $provider;
}
}
// 3. All attendees
if (!empty($appointment_data['attendees'])) {
foreach ($appointment_data['attendees'] as $attendee) {
if (!isset($staff_to_notify[$attendee['staffid']])) {
$staff_to_notify[$attendee['staffid']] = $attendee;
}
}
}
// Send notifications to all relevant staff
foreach ($staff_to_notify as $staff) {
// Add notification
add_notification([
'description' => _l('appointment_cancellation_requested') . ' - ' . $appointment_subject . ' (' . $client_name . ')',
'touserid' => $staff['staffid'],
'fromcompany' => true,
'link' => 'appointly/appointments/view?appointment_id=' . $appointment['id'],
]);
$notifications_to_send[] = $staff['staffid'];
// Send email notification to staff
$template = mail_template(
'appointly_appointment_cancellation_request_to_staff',
'appointly',
array_to_object($appointment),
array_to_object($staff)
);
if ($template) {
// Set the cancellation reason in merge fields
$merge_fields = $template->get_merge_fields();
$merge_fields['{appointment_cancellation_reason}'] = $reason;
$template->set_merge_fields($merge_fields);
$template->send();
}
}
// Send confirmation email to client
$template = mail_template(
'appointly_appointment_cancellation_request_confirmation_to_client',
'appointly',
array_to_object($appointment)
);
if ($template) {
// Set the cancellation reason in merge fields
$merge_fields = $template->get_merge_fields();
$merge_fields['{appointment_cancellation_reason}'] = $reason;
$template->set_merge_fields($merge_fields);
$template->send();
}
// Send live notifications
if (!empty($notifications_to_send)) {
pusher_trigger_notification($notifications_to_send);
}
// Log the notification with details
log_activity('Appointment Cancellation Requested [AppointmentID: ' . $appointment['id'] . ', Subject: ' . $appointment_subject . ', Client: ' . $client_name . ', Reason: ' . $reason . ']');
} catch (Exception $e) {
log_message('error', 'Error sending client cancellation request notifications: ' . $e->getMessage());
}
}
/**
* Check if cancellation is in progress already
*
* @param [appointment hash] $hash
*
* @return array
*/
public function check_if_user_requested_appointment_cancellation($hash)
{
$this->db->select('cancel_notes');
$this->db->where('hash', $hash);
return $this->db->get(db_prefix() . 'appointly_appointments')
->row_array()
;
}
/**
* Send appointment early reminders
*
* @param string|int $appointment_id
*
* @return bool
*/
public function send_appointment_early_reminders($appointment_id)
{
$appointment = $this->get_appointment_data($appointment_id);
// Early validation
if (! $appointment) {
return false;
}
// Don't send reminders for cancelled or completed appointments
if ($appointment['status'] == 'cancelled' || $appointment['status'] == 'completed') {
return false;
}
// Ensure appointment data doesn't have null values that could cause str_replace errors
$appointment = array_map(function ($value) {
return $value ?? '';
}, $appointment);
$staff_to_notify = [];
// Send notifications to staff attendees
foreach ($appointment['attendees'] as $staff) {
if (! $staff['staffid']) {
continue;
}
// do not notify the creator
if ($staff['staffid'] == $appointment['created_by']) {
continue;
}
$staff_to_notify[] = $staff['staffid'];
// notification
add_notification([
'description' => 'appointment_you_have_new_appointment',
'touserid' => $staff['staffid'],
'fromcompany' => true,
'link' => 'appointly/appointments/view?appointment_id=' . $appointment_id,
]);
// Only send email if staff has valid email
if (! filter_var($staff['email'], FILTER_VALIDATE_EMAIL)) {
continue;
}
// Ensure all staff fields are not null to prevent str_replace errors
$staff_safe = array_map(function ($value) {
return $value ?? '';
}, $staff);
send_mail_template(
'appointly_appointment_cron_reminder_to_staff',
'appointly',
array_to_object($appointment),
array_to_object($staff_safe)
);
}
// Send notification to contact if by_email is enabled
if (
! empty($appointment['email']) && isset($appointment['by_email'])
&& $appointment['by_email'] == 1
) {
$template = mail_template(
'appointly_appointment_cron_reminder_to_contact',
'appointly',
array_to_object($appointment)
);
$template->send();
}
// Send SMS to contact if by_sms is enabled
if (
isset($appointment['by_sms']) && $appointment['by_sms'] == 1
&& ! empty($appointment['phone'])
) {
// Trigger SMS
$this->app_sms->trigger(
APPOINTLY_SMS_APPOINTMENT_APPOINTMENT_REMINDER_TO_CLIENT,
$appointment['phone'],
$template->get_merge_fields()
);
}
// Trigger notifications for staff
if (! empty($staff_to_notify)) {
pusher_trigger_notification($staff_to_notify);
}
return true;
}
/**
* Handles the request for new appointment feedback
*
* @param string $appointment_id
*
* @return void
*/
public function request_appointment_feedback($appointment_id)
{
$appointment = $this->get_appointment_data($appointment_id);
$success = false;
if (is_array($appointment) && ! empty($appointment)) {
send_mail_template(
'appointly_appointment_request_feedback',
'appointly',
array_to_object($appointment)
);
$success = true;
}
echo json_encode(['success' => $success]);
}
/**
* Handles new feedback
*
* @param string $id
* @param string $feedback
* @param string $comment
*
* @return bool
*/
public function handle_feedback_post($id, $feedback, ?string $comment = null)
{
$data = ['feedback' => $feedback];
$notified_users = [];
$appointment = $this->apm->get_appointment_data($id);
if (! $appointment || ! is_array($appointment)) {
return false;
}
// Ensure all required fields exist
$appointment['subject'] = $appointment['subject'] ?? _l('appointment_feedback_label');
$appointment['email'] = $appointment['email'] ?? '';
$appointment['name'] = $appointment['name'] ?? '';
$tmp_name = 'appointly_appointment_feedback_received';
$tmp_lang = 'appointment_new_feedback_added';
if ($appointment['feedback'] !== null) {
$tmp_name = 'appointly_appointment_feedback_updated';
$tmp_lang = 'appointly_feedback_updated';
}
// Notify organizer (created_by)
if (!empty($appointment['created_by'])) {
$admin_staff = appointly_get_staff($appointment['created_by']);
if ($admin_staff && is_array($admin_staff)) {
send_mail_template(
$tmp_name,
'appointly',
array_to_object($appointment),
array_to_object($admin_staff)
);
add_notification([
'description' => $tmp_lang,
'touserid' => $admin_staff['staffid'],
'fromcompany' => true,
'link' => 'appointly/appointments/view?appointment_id=' . $id,
]);
$notified_users[] = $admin_staff['staffid'];
}
}
// Notify provider (if different from organizer)
if (!empty($appointment['provider_id']) && $appointment['provider_id'] != $appointment['created_by']) {
$provider_staff = appointly_get_staff($appointment['provider_id']);
if ($provider_staff && is_array($provider_staff)) {
mail_template(
$tmp_name,
'appointly',
array_to_object($appointment),
array_to_object($provider_staff)
);
add_notification([
'description' => $tmp_lang,
'touserid' => $provider_staff['staffid'],
'fromcompany' => true,
'link' => 'appointly/appointments/view?appointment_id=' . $id,
]);
$notified_users[] = $provider_staff['staffid'];
}
}
// Send pusher notifications to all notified users
if (!empty($notified_users)) {
pusher_trigger_notification($notified_users);
}
if ($comment !== null) {
$data['feedback_comment'] = $comment;
}
$this->db->where('id', $id);
$this->db->update(db_prefix() . 'appointly_appointments', $data);
return $this->db->affected_rows() !== 0;
}
/**
* Inserts new event to Outlook calendar in database
*
* @param array $data
*
* @return bool
*/
public function insert_new_outlook_event($data)
{
$last_appointment_id = $this->db->get(db_prefix()
. 'appointly_appointments')
->last_row()->id;
$this->db->where('id', $last_appointment_id);
$this->db->update(
db_prefix() . 'appointly_appointments',
[
'outlook_event_id' => $data['outlook_event_id'],
'outlook_calendar_link' => $data['outlook_calendar_link'],
'outlook_added_by_id' => get_staff_user_id(),
]
);
return true;
}
/**
* Inserts new event to outlook calendar
*
* @param array $data
*
* @return bool
*/
public function update_outlook_event($data)
{
$this->db->where('id', $data['appointment_id']);
$this->db->update(
db_prefix() . 'appointly_appointments',
[
'outlook_event_id' => $data['outlook_event_id'],
'outlook_calendar_link' => $data['outlook_calendar_link'],
'outlook_added_by_id' => get_staff_user_id(),
]
);
return $this->db->affected_rows() !== 0;
}
/**
* Handles sending custom email to client
*
* @param array $data
*
* @return bool
*/
public function send_google_meet_request_email($data)
{
$this->load->model('emails_model');
$attendees = isset($data['attendees']) && !empty($data['attendees'])
? (is_array($data['attendees']) ? $data['attendees'] : json_decode($data['attendees'], true))
: [];
$message = $data['message'];
$subject = _l('appointment_connect_via_google_meet');
$sent_emails = [];
$failed_emails = [];
// Always send to the primary recipient (client/lead/external contact)
$primary_sent = false;
if (!empty($data['to'])) {
$primary_sent = $this->emails_model->send_simple_email(
$data['to'],
$subject,
$message
);
if ($primary_sent) {
$sent_emails[] = $data['to'];
// Log activity for primary recipient
log_activity('Google Meet Invitation Sent [' . $subject . '] to primary recipient: ' . $data['to']);
} else {
$failed_emails[] = $data['to'];
log_activity('Failed to send Google Meet Invitation [' . $subject . '] to primary recipient: ' . $data['to']);
}
}
// Send to attendees only if requested and available
$attendees_sent = 0;
if (is_array($attendees) && count($attendees) > 0) {
$current_staff = appointly_get_staff(get_staff_user_id());
$current_staff_email = $current_staff ? $current_staff['email'] : '';
foreach ($attendees as $attendee_email) {
// Don't send to current staff member (they're sending the email)
if ($attendee_email !== $current_staff_email && !empty($attendee_email)) {
$attendee_sent = $this->emails_model->send_simple_email(
$attendee_email,
$subject,
$message
);
if ($attendee_sent) {
$sent_emails[] = $attendee_email;
$attendees_sent++;
// Log activity for each attendee
log_activity('Google Meet Invitation Sent [' . $subject . '] to attendee: ' . $attendee_email);
} else {
$failed_emails[] = $attendee_email;
log_activity('Failed to send Google Meet Invitation [' . $subject . '] to attendee: ' . $attendee_email);
}
}
}
}
// Log summary activity
$total_sent = count($sent_emails);
$total_failed = count($failed_emails);
if ($total_sent > 0) {
log_activity('Google Meet Invitation Campaign: ' . $total_sent . ' emails sent successfully' .
($total_failed > 0 ? ', ' . $total_failed . ' failed' : ''));
}
// Return detailed results
return [
'success' => $primary_sent || $attendees_sent > 0,
'primary_sent' => $primary_sent,
'attendees_sent' => $attendees_sent,
'total_sent' => $total_sent,
'sent_emails' => $sent_emails,
'failed_emails' => $failed_emails,
'message' => $total_sent > 0 ?
'Google Meet invitation sent to ' . $total_sent . ' recipient(s)' :
'Failed to send Google Meet invitation'
];
}
/**
* Get all services
*/
public function get_services()
{
$this->db->select('s.*, COUNT(DISTINCT ss.staff_id) as provider_count');
$this->db->from(db_prefix() . 'appointly_services s');
$this->db->where('s.active', 1);
$this->db->join(db_prefix() . 'appointly_service_staff ss', 's.id = ss.service_id AND ss.is_provider = 1');
$this->db->group_by('s.id');
return $this->db->get()->result_array();
}
/**
* Get appointment with staff permission filtering
*
* This method enforces staff permissions and applies filtering based on user roles.
* For client/public access, use get_appointment_data() instead.
*
* @param int $id Appointment ID
* @return array|null Appointment data or null if not found/no access
*/
public function get_appointment($id)
{
if (! $id) {
return null;
}
// Check permissions first
if (!staff_can('view', 'appointments')) {
return null; // No permissions = no access
}
// First get the basic appointment data without the join that's causing errors
$this->db->select('a.*, s.name as service_name, s.duration as service_duration, s.color as service_color');
$this->db->from(db_prefix() . 'appointly_appointments a');
$this->db->join(
db_prefix() . 'appointly_services s',
's.id = a.service_id',
'left'
);
$this->db->where('a.id', $id);
// Apply permission filtering for non-admin staff
if (!is_admin()) {
$this->db->where('(a.created_by=' . get_staff_user_id()
. ' OR a.provider_id=' . get_staff_user_id()
. ' OR a.id IN (SELECT appointment_id FROM ' . db_prefix() . 'appointly_attendees WHERE staff_id=' . get_staff_user_id() . '))');
}
$appointment = $this->db->get()->row_array();
if ($appointment) {
// Get service providers separately
$service_providers = [];
if (
isset($appointment['service_id'])
&& ! empty($appointment['service_id'])
) {
$this->db->select('staff_id');
$this->db->from(db_prefix() . 'appointly_service_staff');
$this->db->where('service_id', $appointment['service_id']);
$providers_query = $this->db->get();
if ($providers_query && $providers_query->num_rows() > 0) {
foreach ($providers_query->result_array() as $provider) {
$service_providers[] = $provider['staff_id'];
}
}
}
// Get attendees (staff assigned to this appointment)
$this->db->select('staff_id');
$this->db->from(db_prefix() . 'appointly_attendees');
$this->db->where('appointment_id', $id);
$assigned_providers = $this->db->get()->result_array();
// Get assigned provider IDs as a simple array
$assigned_provider_ids = array_column(
$assigned_providers,
'staff_id'
);
// Include the creator in assigned providers if not already included
if (! in_array(
$appointment['created_by'],
$assigned_provider_ids
)) {
$assigned_provider_ids[] = $appointment['created_by'];
}
// Merge everything into the appointment array
$appointment = array_merge($appointment, [
'appointment_id' => $appointment['id'],
'service_providers' => $service_providers,
// Available providers for the service
'selected_staff' => $assigned_provider_ids,
// Currently assigned providers
'created_by' => $appointment['created_by'],
// Keep original creator
]);
return $appointment;
}
return null;
}
public function getBusyTimes()
{
$this->db->select('date, start_hour, service_id');
$this->db->from(db_prefix() . 'appointly_appointments');
$this->db->where('status !=', 'cancelled');
$this->db->where('status !=', 'completed');
$this->db->where('date >=', date('Y-m-d'));
$appointments = $this->db->get()->result_array();
$busy_times = [];
foreach ($appointments as $appointment) {
$service = $this->service_model->get($appointment['service_id']);
$duration = $service ? $service->duration : 60;
$start_time = strtotime($appointment['date'] . ' '
. $appointment['start_hour']);
$end_time = strtotime("+{$duration} minutes", $start_time);
$busy_times[] = [
'date' => $appointment['date'],
'start_hour' => $appointment['start_hour'],
'end_hour' => date('H:i', $end_time),
];
}
return $busy_times;
}
/**
* Get appointments by date with filters
*
* @param array $params Filter parameters
*
* @return array
*/
public function get_appointments_by_date($params = [])
{
// Check permissions first
if (!staff_can('view', 'appointments')) {
return []; // No permissions = no appointments
}
$this->db->select('a.*, s.name as service_name, s.duration as service_duration');
$this->db->from(db_prefix() . 'appointly_appointments a');
$this->db->join(
db_prefix() . 'appointly_services s',
's.id = a.service_id',
'left'
);
// Apply permission filtering for non-admin staff
if (!is_admin()) {
$this->db->where('(a.created_by=' . get_staff_user_id()
. ' OR a.provider_id=' . get_staff_user_id()
. ' OR a.id IN (SELECT appointment_id FROM ' . db_prefix() . 'appointly_attendees WHERE staff_id=' . get_staff_user_id() . '))');
}
// Apply filters
if (isset($params['where'])) {
foreach ($params['where'] as $key => $value) {
if (is_array($value)) {
$this->db->where_in('a.' . $key, $value);
} else {
$this->db->where('a.' . $key, $value);
}
}
}
// Date range filter
if (isset($params['start_date'])) {
$this->db->where('a.date >=', $params['start_date']);
}
if (isset($params['end_date'])) {
$this->db->where('a.date <=', $params['end_date']);
}
// Staff filter
if (isset($params['staff_id'])) {
$this->db->where('a.created_by', $params['staff_id']);
}
// Service filter
if (isset($params['service_id'])) {
$this->db->where('a.service_id', $params['service_id']);
}
// Order by date and start hour
$this->db->order_by('a.date', 'ASC');
$this->db->order_by('a.start_hour', 'ASC');
return $this->db->get()->result_array();
}
/**
* Get appointment timezone info
*
* @param array $appointment
*
* @return array
*/
public function get_appointment_timezone_info($appointment)
{
if (empty($appointment['timezone'])) {
return get_option('default_timezone');
}
try {
$timezone = new DateTimeZone($appointment['timezone']);
$date = new DateTime('now', $timezone);
return [
'timezone' => $appointment['timezone'],
'offset' => $date->format('P'),
'abbr' => $date->format('T'),
];
} catch (Exception $e) {
log_message(
'error',
'Timezone info fetch failed: ' . $e->getMessage()
);
return [
'timezone' => get_option('default_timezone'),
'offset' => '+00:00',
'abbr' => 'UTC',
];
}
}
/**
* Save appointment description
*
* @param integer $appointment_id
* @param string $description
*
* @return boolean
*/
public function save_appointment_description($appointment_id, $description)
{
$this->db->where('id', $appointment_id);
return $this->db->update(
db_prefix() . 'appointly_appointments',
['description' => $description]
);
}
public function save_outlook_event_id(
$appointment_id,
$outlook_event_id,
$outlook_calendar_link,
$staff_id
) {
return $this->db->update(db_prefix() . 'appointly_appointments', [
'outlook_event_id' => $outlook_event_id,
'outlook_calendar_link' => $outlook_calendar_link,
'outlook_added_by_id' => $staff_id,
], ['id' => $appointment_id]);
}
/**
* Get pending cancellation requests
*
* @return array
*/
public function get_pending_cancellations()
{
$this->db->select('*');
$this->db->from(db_prefix() . 'appointly_appointments');
$this->db->where('cancel_notes IS NOT NULL');
$this->db->where('status !=', 'cancelled');
$this->db->where('status !=', 'completed');
$this->db->order_by('date', 'desc');
$appointments = $this->db->get()->result_array();
foreach ($appointments as &$appointment) {
// related contact/staff info
if (! empty($appointment['contact_id'])) {
$this->load->model('clients_model');
$contact
= $this->clients_model->get_contact($appointment['contact_id']);
if ($contact) {
$appointment['contact_name'] = $contact->firstname . ' '
. $contact->lastname;
$appointment['contact_email'] = $contact->email;
}
}
// service info if exists
if (! empty($appointment['service_id'])) {
$service = $this->get_service($appointment['service_id']);
if ($service) {
$appointment['service_name'] = $service->name;
}
}
}
return $appointments;
}
/**
* Get pending reschedule requests
*
* @return array
*/
public function get_pending_reschedules()
{
$this->db->select('rr.*, a.subject, a.date, a.start_hour, a.end_hour, a.contact_id, a.email, a.name, a.service_id');
$this->db->from(db_prefix() . 'appointly_reschedule_requests rr');
$this->db->join(db_prefix() . 'appointly_appointments a', 'a.id = rr.appointment_id');
$this->db->where('rr.status', 'pending');
$this->db->order_by('rr.requested_at', 'DESC');
$reschedules = $this->db->get()->result_array();
foreach ($reschedules as &$reschedule) {
// Add related contact/staff info
if (!empty($reschedule['contact_id'])) {
$this->load->model('clients_model');
$contact = $this->clients_model->get_contact($reschedule['contact_id']);
if ($contact) {
$reschedule['contact_name'] = $contact->firstname . ' ' . $contact->lastname;
$reschedule['contact_email'] = $contact->email;
}
}
// Add service info if exists
if (!empty($reschedule['service_id'])) {
$service = $this->get_service($reschedule['service_id']);
if ($service) {
$reschedule['service_name'] = $service->name;
}
}
}
return $reschedules;
}
/**
* Create a reschedule request
*
* @param int $appointment_id The appointment ID
* @param string $requested_date The requested date
* @param string $requested_time The requested time
* @param string $reason The reason for the reschedule request
*
* @return int The ID of the created reschedule request
*/
public function create_reschedule_request($appointment_id, $requested_date, $requested_time, ?string $reason = null)
{
$data = [
'appointment_id' => $appointment_id,
'requested_date' => $requested_date,
'requested_time' => $requested_time,
'reason' => $reason,
'status' => 'pending',
'requested_at' => date('Y-m-d H:i:s')
];
$this->db->insert(db_prefix() . 'appointly_reschedule_requests', $data);
return $this->db->insert_id();
}
/**
* Approve a reschedule request
*
* @param int $reschedule_id The reschedule request ID
* @param int $staff_id The staff ID approving the request
*
* @return boolean
*/
public function approve_reschedule_request($reschedule_id, $staff_id)
{
// Get the reschedule request details
$reschedule = $this->db->get_where(db_prefix() . 'appointly_reschedule_requests', ['id' => $reschedule_id])->row_array();
if (!$reschedule) {
return false;
}
// Update the original appointment with new date/time
$this->db->where('id', $reschedule['appointment_id']);
$this->db->update(db_prefix() . 'appointly_appointments', [
'date' => $reschedule['requested_date'],
'start_hour' => $reschedule['requested_time']
]);
// Mark reschedule request as approved
$this->db->where('id', $reschedule_id);
$this->db->update(db_prefix() . 'appointly_reschedule_requests', [
'status' => 'approved',
'processed_by' => $staff_id,
'processed_at' => date('Y-m-d H:i:s')
]);
return true;
}
/**
* Deny a reschedule request
*
* @param int $reschedule_id The reschedule request ID
* @param int $staff_id The staff ID denying the request
* @param string $denial_reason The reason for denial
*
* @return boolean
*/
public function deny_reschedule_request($reschedule_id, $staff_id, ?string $denial_reason = null)
{
$this->db->where('id', $reschedule_id);
$this->db->update(db_prefix() . 'appointly_reschedule_requests', [
'status' => 'denied',
'processed_by' => $staff_id,
'processed_at' => date('Y-m-d H:i:s'),
'denial_reason' => $denial_reason
]);
return $this->db->affected_rows() > 0;
}
/**
* Get a reschedule request by ID
*
* @param int $reschedule_id The reschedule request ID
*
* @return array|null
*/
public function get_reschedule_request($reschedule_id)
{
$this->db->select('rr.*, a.subject, a.date, a.start_hour, a.end_hour, a.contact_id, a.email, a.name, a.service_id');
$this->db->from(db_prefix() . 'appointly_reschedule_requests rr');
$this->db->join(db_prefix() . 'appointly_appointments a', 'a.id = rr.appointment_id');
$this->db->where('rr.id', $reschedule_id);
return $this->db->get()->row_array();
}
/**
* Check if an appointment has a pending reschedule request
*
* @param int $appointment_id The appointment ID to check
*
* @return boolean
*/
public function has_pending_reschedule($appointment_id)
{
$this->db->where('appointment_id', $appointment_id);
$this->db->where('status', 'pending');
return $this->db->count_all_results(db_prefix() . 'appointly_reschedule_requests') > 0;
}
/**
* Check if a staff member is busy on a specific date and time
*
* @param int $staff_id The staff ID to check
* @param string $date The date to check (Y-m-d format)
* @param string $start_hour The start time (H:i format)
* @param string $end_hour The end time (H:i format)
*
* @return boolean
*/
public function staff_is_busy($staff_id, $date, $start_hour, $end_hour)
{
$this->db->select('a.id, a.status, a.date, a.start_hour, a.end_hour');
$this->db->from(db_prefix() . 'appointly_appointments a');
$this->db->join(
db_prefix() . 'appointly_attendees att',
'att.appointment_id = a.id'
);
$this->db->where('att.staff_id', $staff_id);
$this->db->where('a.date', $date);
$this->db->where('a.status !=', 'cancelled');
$this->db->where('a.status !=', 'completed');
$this->db->where("(
(a.start_hour <= '$start_hour' AND a.end_hour > '$start_hour') OR
(a.start_hour < '$end_hour' AND a.end_hour >= '$end_hour') OR
(a.start_hour >= '$start_hour' AND a.end_hour <= '$end_hour')
)");
return $this->db->count_all_results() > 0;
}
/**
* Get busy times for a specific service
*
* @param int $service_id The service ID to get busy times for
*
* @return array
*/
public function get_busy_times_by_service($service_id)
{
// Clean and validate service_id
$service_id = $this->db->escape_str($service_id);
$this->db->select('a.id AS appointment_id, a.date, a.start_hour, a.end_hour, a.provider_id, a.duration, a.status');
$this->db->from(db_prefix() . 'appointly_appointments a');
$this->db->where('a.service_id', $service_id);
$this->db->where('a.status !=', 'cancelled');
$this->db->where('a.status !=', 'completed');
// Only include dates from today onwards
$today = date('Y-m-d');
$this->db->where('a.date >=', $today);
$appointments = $this->db->get()->result_array();
$busy_times = [];
foreach ($appointments as $appointment) {
$busy_times[] = [
'appointment_id' => $appointment['appointment_id'],
'date' => $appointment['date'],
'start_hour' => $appointment['start_hour'],
'end_hour' => $appointment['end_hour'],
'provider_id' => $appointment['provider_id'],
'duration' => $appointment['duration'],
];
}
return $busy_times;
}
/**
* Get appointment feedback
*
* @param int $appointment_id
*
* @return array
*/
public function get_appointment_feedback($appointment_id)
{
$this->db->select('feedback, comment');
$this->db->from(db_prefix() . 'appointly_appointments');
$this->db->where('id', $appointment_id);
$this->db->where('feedback IS NOT NULL');
$this->db->where('comment IS NOT NULL');
$result = $this->db->get()->row_array();
return $result;
}
/**
* Get company schedule for each day of the week
*
* @return array Company schedule by weekday
*/
public function get_company_schedule()
{
$this->db->order_by('FIELD(weekday, "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday")');
$result = $this->db->get(db_prefix() . 'appointly_company_schedule')
->result_array();
// Convert to associative array with weekday as key
$schedule = [];
foreach ($result as $day) {
$schedule[$day['weekday']] = [
'start_time' => $day['start_time'],
'end_time' => $day['end_time'],
'is_enabled' => (bool) $day['is_enabled'],
];
}
return $schedule;
}
/**
* Get staff working hours for a specific staff member
*
* @param int $staff_id Staff ID
*
* @return array Working hours by day
*/
public function get_staff_working_hours($staff_id)
{
$this->db->where('staff_id', $staff_id);
$query = $this->db->get(db_prefix()
. 'appointly_staff_working_hours');
$working_hours = [];
// Return empty array if no hours set
if ($query->num_rows() === 0) {
return $working_hours;
}
foreach ($query->result_array() as $day) {
$working_hours[$day['weekday']] = [
'weekday' => $day['weekday'],
'start_time' => $day['start_time'],
'end_time' => $day['end_time'],
'enabled' => (bool) $day['is_available'],
'is_available' => (bool) $day['is_available'],
'use_company_schedule' => isset($day['use_company_schedule'])
? (bool) $day['use_company_schedule'] : false,
];
}
return $working_hours;
}
/**
* Get available time slots for a provider on a specific date for a service
*
* @param int $provider_id Staff ID
* @param string $date Date in Y-m-d format
* @param int $service_id Service ID
* @param string $timezone User's timezone (optional)
* @param int $exclude_appointment_id Optional ID of appointment being edited
*
* @return array Array of available time slots
*/
public function get_available_time_slots(
$provider_id,
$date,
$service_id,
$timezone = null,
$exclude_appointment_id = null
) {
// Basic validation
if (empty($date)) {
return [];
}
// Ensure date is in Y-m-d format
$formatted_date = $date;
if (! preg_match('/^\d{4}-\d{2}-\d{2}$/', $date)) {
// Try various date formats
if (preg_match('/^\d{2}-\d{2}-\d{4}$/', $date)) {
// d-m-Y format
$parts = explode('-', $date);
$formatted_date = $parts[2] . '-' . $parts[1] . '-' . $parts[0];
} elseif (preg_match('/^\d{2}\/\d{2}\/\d{4}$/', $date)) {
// mm/dd/yyyy format
$parts = explode('/', $date);
$formatted_date = $parts[2] . '-' . $parts[0] . '-' . $parts[1];
} elseif (preg_match('/^\d{2}\.\d{2}\.\d{4}$/', $date)) {
// dd.mm.yyyy format
$parts = explode('.', $date);
$formatted_date = $parts[2] . '-' . $parts[1] . '-' . $parts[0];
} else {
// Last resort - try general parsing
$timestamp = strtotime($date);
if ($timestamp === false) {
return [];
}
$formatted_date = date('Y-m-d', $timestamp);
}
// Validate the new format
if (! preg_match('/^\d{4}-\d{2}-\d{2}$/', $formatted_date)) {
return [];
}
}
// Get day of week
$day_of_week = date('l', strtotime($formatted_date));
// Get service details
$service = $this->get_service($service_id);
if (! $service) {
log_message('error', "Service not found: {$service_id}");
return [];
}
$duration = $service->duration;
$buffer_before = property_exists($service, 'buffer_before')
? $service->buffer_before : 0;
$buffer_after = property_exists($service, 'buffer_after')
? $service->buffer_after : 0;
// Get company schedule first as we might need it for fallback
$this->db->where('weekday', $day_of_week);
$company_schedule = $this->db->get(db_prefix()
. 'appointly_company_schedule')
->row();
// If company doesn't have schedule for this day at all, then no availability
if (! $company_schedule) {
return [];
}
// If company schedule is disabled for this day, no availability
if (! $company_schedule->is_enabled) {
return [];
}
// Get staff working hours - if they exist
$this->db->where('staff_id', $provider_id);
$this->db->where('weekday', $day_of_week);
$staff_hours = $this->db->get(db_prefix()
. 'appointly_staff_working_hours')->row();
// Determine which schedule to use:
// 1. If staff has hours and use_company_schedule=1 - use company schedule regardless of availability
// 2. If staff has hours, use_company_schedule=0 and is_available=0 - no availability
// 3. If staff has hours, use_company_schedule=0 and is_available=1 - use staff hours
// 4. If staff has no hours set for this day - fall back to company schedule
$use_company_schedule = false;
if (! $staff_hours) {
// No staff hours set for this day - use company schedule
$use_company_schedule = true;
} else {
// Check if using company schedule is enabled - this takes priority over availability
if (
isset($staff_hours->use_company_schedule)
&& $staff_hours->use_company_schedule == 1
) {
$use_company_schedule = true;
} else {
// Staff has hours set and isn't using company schedule - check if available
if ($staff_hours->is_available == 0) {
// Staff explicitly marked as not available and not using company schedule
return [];
} else {
// Staff is available and using their own hours
$use_company_schedule = false;
}
}
}
// Set start and end times based on which schedule to use
if ($use_company_schedule) {
$start_time = $company_schedule->start_time;
$end_time = $company_schedule->end_time;
} else {
// When staff has custom hours, ensure we're using them correctly
if ($staff_hours) {
$start_time = $staff_hours->start_time;
$end_time = $staff_hours->end_time;
} else {
// This shouldn't happen based on our logic above, but as a fallback
$start_time = $company_schedule->start_time;
$end_time = $company_schedule->end_time;
}
}
// Get existing appointments for this provider on this date, excluding the current appointment if provided
$busy_times = $this->get_busy_times_by_staff(
$provider_id,
$formatted_date,
$exclude_appointment_id
);
// Generate available slots
$slots = [];
$slot_interval = 15; // 15-minute intervals
// Convert start and end times to timestamps
$start_timestamp = strtotime($formatted_date . ' ' . $start_time);
$end_timestamp = strtotime($formatted_date . ' ' . $end_time);
if (! $start_timestamp || ! $end_timestamp) {
return [];
}
$current_time = time();
// Handle timezone adjustment if needed
if ($timezone && $timezone != get_option('default_timezone')) {
$system_tz = new DateTimeZone(get_option('default_timezone'));
$user_tz = new DateTimeZone($timezone);
// Calculate timezone offset for today
$system_dt = new DateTime('now', $system_tz);
$user_dt = new DateTime('now', $user_tz);
$offset_seconds = $system_tz->getOffset($system_dt)
- $user_tz->getOffset($user_dt);
// Adjust timestamps for timezone differences
$start_timestamp -= $offset_seconds;
$end_timestamp -= $offset_seconds;
$current_time -= $offset_seconds;
}
// Loop through the time slots
$slot_time = $start_timestamp;
while ($slot_time + ($duration * 60) <= $end_timestamp) {
$slot_end_time = $slot_time + ($duration * 60);
// Add buffer times
$slot_start_with_buffer = $slot_time - ($buffer_before * 60);
$slot_end_with_buffer = $slot_end_time + ($buffer_after * 60);
// Check if slot with buffers conflicts with any busy times
$available = true;
foreach ($busy_times as $busy) {
$busy_start = strtotime($formatted_date . ' '
. $busy['start_hour']);
$busy_end = strtotime($formatted_date . ' ' . $busy['end_hour']);
// Add buffer times to busy slot if it has them
if (isset($busy['buffer_before']) && $busy['buffer_before']) {
$busy_start -= ($busy['buffer_before'] * 60);
}
if (isset($busy['buffer_after']) && $busy['buffer_after']) {
$busy_end += ($busy['buffer_after'] * 60);
}
// Check if there's an overlap
if (
($slot_start_with_buffer >= $busy_start
&& $slot_start_with_buffer < $busy_end)
|| // Start time is within busy period
($slot_end_with_buffer > $busy_start
&& $slot_end_with_buffer <= $busy_end)
|| // End time is within busy period
($slot_start_with_buffer <= $busy_start
&& $slot_end_with_buffer
>= $busy_end) // Slot completely encompasses busy period
) {
$available = false;
break;
}
}
// Check if this time slot is in the past for today's date
if ($formatted_date === date('Y-m-d')) {
$current_datetime = time();
if ($slot_time <= $current_datetime) {
$available = false;
}
}
if ($available) {
// Format the time values for display
$start_formatted = date('H:i', $slot_time);
$end_formatted = date('H:i', $slot_end_time);
$display_formatted = date('H:i', $slot_time) . ' - ' . date('H:i', $slot_end_time);
// Include all required keys to satisfy both frontend and controller expectations
$slots[] = [
// Keys expected by the controller
'start' => $start_formatted,
'end' => $end_formatted,
'formatted_time' => $display_formatted,
'available' => true,
// Keys for frontend compatibility
'value' => $start_formatted,
'text' => $display_formatted,
'end_time' => $end_formatted,
];
}
// Move to next slot
$slot_time += ($slot_interval * 60);
}
return $slots;
}
/**
* Get busy times for a staff member for a specific date
*
* @param int $staff_id
* @param string $date Date in Y-m-d format (optional, if null will get all future dates)
* @param int $exclude_appointment_id Exclude this appointment when finding busy times (for editing)
*
* @return array Array of busy times
*/
public function get_busy_times_by_staff(
$staff_id,
$date = null,
$exclude_appointment_id = null
) {
if (! $staff_id) {
return [];
}
// Start with the basic query
$this->db->select('a.id, a.service_id, a.date, a.start_hour, a.end_hour, s.buffer_before, s.buffer_after')
->from(db_prefix() . 'appointly_appointments a')
->join(
db_prefix() . 'appointly_services s',
'a.service_id = s.id',
'left'
)
->where('a.provider_id', $staff_id)
->where('a.status !=', 'cancelled')
->order_by('a.date', 'ASC')
->order_by('a.start_hour', 'ASC')
;
// Add date constraint if provided
if ($date) {
$this->db->where('a.date', $date);
} else {
$this->db->where('a.date >=', date('Y-m-d'));
}
// Exclude the appointment being edited
if ($exclude_appointment_id) {
$this->db->where('a.id !=', $exclude_appointment_id);
}
// Execute the query
$busy_times = $this->db->get()->result_array();
return $busy_times;
}
/**
* Save staff working hours
*
* @param int $staff_id Staff ID
* @param array $working_hours Array of working hours by day
*
* @return bool
*/
public function save_staff_working_hours($staff_id, $working_hours)
{
// Validate staff_id
$staff_id = (int) $staff_id;
if (! $staff_id) {
log_message('error', 'Appointly - Invalid staff_id: ' . $staff_id);
return false;
}
// Start transaction
$this->db->trans_begin();
try {
// First, delete existing working hours for this staff
$this->db->where('staff_id', $staff_id);
$this->db->delete(db_prefix() . 'appointly_staff_working_hours');
// Format and validate working hours data
$valid_days = [
'Monday',
'Tuesday',
'Wednesday',
'Thursday',
'Friday',
'Saturday',
'Sunday',
];
$insert_data = [];
foreach ($valid_days as $day) {
// Initialize default values
$day_data = [
'staff_id' => $staff_id,
'weekday' => $day,
'start_time' => '09:00:00',
'end_time' => '17:00:00',
'is_available' => 0, // Default to unavailable
'use_company_schedule' => 0,
// Default to not using company schedule
];
// Update with posted values if available
if (isset($working_hours[$day])) {
$hours = $working_hours[$day];
// Check if is_available was explicitly set to 1
// This is critical - checkbox values need special handling
if (
isset($hours['is_available'])
&& ($hours['is_available'] == 1
|| $hours['is_available'] === true
|| $hours['is_available'] === 'on')
) {
$day_data['is_available'] = 1;
}
// Check if use_company_schedule was explicitly set
if (
isset($hours['use_company_schedule'])
&& ($hours['use_company_schedule'] == 1
|| $hours['use_company_schedule'] === true
|| $hours['use_company_schedule'] === 'on')
) {
$day_data['use_company_schedule'] = 1;
}
// Update times if provided
if (! empty($hours['start_time'])) {
// Format time properly with seconds
$start_time
= $this->format_time_with_seconds($hours['start_time']);
if ($start_time) {
$day_data['start_time'] = $start_time;
}
}
if (! empty($hours['end_time'])) {
// Format time properly with seconds
$end_time
= $this->format_time_with_seconds($hours['end_time']);
if ($end_time) {
$day_data['end_time'] = $end_time;
}
}
}
$insert_data[] = $day_data;
}
if (! empty($insert_data)) {
$result = $this->db->insert_batch(
db_prefix()
. 'appointly_staff_working_hours',
$insert_data
);
if (! $result) {
log_message('error', 'Appointly - Batch insert failed: '
. $this->db->error()['message']);
$this->db->trans_rollback();
return false;
}
}
$this->db->trans_commit();
// Verify the insert worked by counting rows
$this->db->where('staff_id', $staff_id);
$count = $this->db->count_all_results(db_prefix()
. 'appointly_staff_working_hours');
return $count === 7; // We should have exactly 7 days
} catch (Exception $e) {
$this->db->trans_rollback();
return false;
}
}
/**
* Format time with seconds
* Ensures time strings always include seconds (HH:MM:SS)
*
* @param string $time Time string (HH:MM or HH:MM:SS)
*
* @return string Time with seconds (HH:MM:SS)
*/
private function format_time_with_seconds($time)
{
if (empty($time)) {
return '00:00:00';
}
// If time already has seconds, return as is
if (substr_count($time, ':') == 2) {
return $time;
}
// Otherwise add seconds
return $time . ':00';
}
/**
* Save company schedule
*
* @param array $schedule Day => hour settings
*
* @return bool
*/
public function save_company_schedule($schedule)
{
// First, clear the existing schedule
$this->db->truncate(db_prefix() . 'appointly_company_schedule');
// Format and validate schedule data
$valid_days = [
'Monday',
'Tuesday',
'Wednesday',
'Thursday',
'Friday',
'Saturday',
'Sunday',
];
$insert_data = [];
foreach ($valid_days as $day) {
if (isset($schedule[$day])) {
$day_data = $schedule[$day];
// Check for value "1" for is_enabled (checkbox checked)
$enabled = isset($day_data['is_enabled'])
&& $day_data['is_enabled'] == 1;
// Default values
$start_time = '09:00';
$end_time = '17:00';
// Only use submitted times if they are set
if (! empty($day_data['start_time'])) {
// Convert to 15-minute interval if needed
$start_time
= $this->round_time_to_15_min($day_data['start_time']);
}
if (! empty($day_data['end_time'])) {
// Convert to 15-minute interval if needed
$end_time
= $this->round_time_to_15_min($day_data['end_time']);
}
$insert_data[] = [
'weekday' => $day,
'start_time' => $start_time,
'end_time' => $end_time,
'is_enabled' => $enabled ? 1 : 0,
];
} else {
// If day not provided in schedule, add with default values
$insert_data[] = [
'weekday' => $day,
'start_time' => '09:00',
'end_time' => '17:00',
'is_enabled' => 0,
];
}
}
// Insert the schedule
return $this->db->insert_batch(
db_prefix() . 'appointly_company_schedule',
$insert_data
);
}
/**
* Round a time value to the nearest 15-minute interval
*
* @param string $time Time in HH:MM or HH:MM:SS format
*
* @return string Time in HH:MM format rounded to nearest 15 minutes
*/
private function round_time_to_15_min($time)
{
// Extract hours and minutes
$parts = explode(':', $time);
$hours = (int) $parts[0];
$minutes = isset($parts[1]) ? (int) $parts[1] : 0;
// Round minutes to nearest 15
$rounded_minutes = round($minutes / 15) * 15;
// Handle case where minutes round to 60
if ($rounded_minutes == 60) {
$hours++;
$rounded_minutes = 0;
}
// Format and return
return sprintf('%02d:%02d', $hours, $rounded_minutes);
}
/**
* Update service providers
*
* @param int $service_id
* @param array $provider_ids
* @param int $primary_provider_id Primary provider ID (optional)
*
* @return bool
*/
public function update_service_providers(
$service_id,
$provider_ids,
$primary_provider_id = null
) {
// Delete existing provider relationships
$this->db->where('service_id', $service_id);
$this->db->delete(db_prefix() . 'appointly_service_staff');
// Ensure we have an array of providers
if (! is_array($provider_ids)) {
$provider_ids = [$provider_ids];
}
// If no primary provider is set but we have providers, set the first one as primary
if (! $primary_provider_id && ! empty($provider_ids)) {
$primary_provider_id = $provider_ids[0];
}
// Insert provider relationships
foreach ($provider_ids as $staff_id) {
$this->db->insert(db_prefix() . 'appointly_service_staff', [
'service_id' => $service_id,
'staff_id' => $staff_id,
'is_provider' => 1,
'is_primary' => ($staff_id == $primary_provider_id) ? 1 : 0,
]);
}
return true;
}
/**
* Get busy times for a specific service and staff combination
*
* @param int $service_id Service ID
* @param int $staff_id Staff ID
* @param string $date Optional specific date (Y-m-d format)
*
* @return array Array of busy times with buffer considerations
*/
public function get_busy_times_by_service_and_staff(
$service_id,
$staff_id,
$date = null
) {
// Clean and validate inputs
$service_id = $this->db->escape_str($service_id);
$staff_id = $this->db->escape_str($staff_id);
// Get service details for buffer times
$service = $this->get_service($service_id);
if (! $service) {
return [];
}
$default_buffer_before = property_exists($service, 'buffer_before')
? $service->buffer_before : 0;
$default_buffer_after = property_exists($service, 'buffer_after')
? $service->buffer_after : 0;
$busy_times = [];
$today = date('Y-m-d');
// 1. Get appointments where the staff is a provider
$this->db->select('a.id AS appointment_id, a.date, a.start_hour, a.end_hour, a.duration, a.buffer_before, a.buffer_after, a.service_id');
$this->db->from(db_prefix() . 'appointly_appointments a');
$this->db->where('a.provider_id', $staff_id);
$this->db->where('a.status !=', 'cancelled');
// Filter by date if provided
if ($date) {
$this->db->where('a.date', $date);
} else {
// Only include dates from today onwards
$this->db->where('a.date >=', $today);
}
$provider_appointments = $this->db->get()->result_array();
// 2. Get appointments where the staff is an attendee
$this->db->select('a.id AS appointment_id, a.date, a.start_hour, a.end_hour, a.duration, a.buffer_before, a.buffer_after, a.service_id');
$this->db->from(db_prefix() . 'appointly_appointments a');
$this->db->join(
db_prefix() . 'appointly_attendees att',
'a.id = att.appointment_id',
'inner'
);
$this->db->where('att.staff_id', $staff_id);
$this->db->where('a.status !=', 'cancelled');
if ($date) {
$this->db->where('a.date', $date);
} else {
$this->db->where('a.date >=', $today);
}
$attendee_appointments = $this->db->get()->result_array();
// Combine both sets of appointments (avoiding duplicates)
$all_appointments = array_merge(
$provider_appointments,
$attendee_appointments
);
$processed_ids = [];
foreach ($all_appointments as $appointment) {
// Skip if we've already processed this appointment
if (in_array((int)$appointment['appointment_id'], $processed_ids, true)) {
continue;
}
$processed_ids[] = (int)$appointment['appointment_id'];
// Ensure we have end_hour (calculate if needed)
$end_hour = $appointment['end_hour'];
if (empty($end_hour) && ! empty($appointment['start_hour'])) {
// Try to get duration from appointment
$duration = $appointment['duration'];
// If no duration but service_id exists, try to get from service
if (! $duration && ! empty($appointment['service_id'])) {
$appt_service
= $this->get_service($appointment['service_id']);
if ($appt_service) {
$duration = $appt_service->duration;
}
}
// Default to 60 minutes if no duration found
if (! $duration) {
$duration = 60;
}
$start_time = strtotime($appointment['start_hour']);
$end_time = $start_time + ($duration * 60);
$end_hour = date('H:i:s', $end_time);
}
// Get buffer times (either from appointment or defaults from service)
$buffer_before = $appointment['buffer_before'] ?? $default_buffer_before;
$buffer_after = $appointment['buffer_after'] ?? $default_buffer_after;
$busy_times[] = [
'appointment_id' => $appointment['appointment_id'],
'date' => $appointment['date'],
'start_hour' => $appointment['start_hour'],
'end_hour' => $end_hour,
'buffer_before' => $buffer_before,
'buffer_after' => $buffer_after,
];
}
return $busy_times;
}
/**
* Get busy times for a specific provider on a specific date
*
* @param int $provider_id The provider ID
* @param string $date The date in YYYY-MM-DD format
*
* @return array Array of busy time slots
*/
public function get_busy_times_by_date($provider_id, $date)
{
if (! $provider_id || ! $date) {
return [];
}
// Sanitize inputs
$provider_id = $this->db->escape_str($provider_id);
$date = $this->db->escape_str($date);
// Check if buffer columns exist in services table
$buffer_columns_exist = $this->db->field_exists('buffer_before', db_prefix() . 'appointly_services') &&
$this->db->field_exists('buffer_after', db_prefix() . 'appointly_services');
// Build select clause based on available columns
if ($buffer_columns_exist) {
$select_clause = 'a.id, a.date, a.start_hour, a.end_hour, a.service_id, s.buffer_before, s.buffer_after';
} else {
$select_clause = 'a.id, a.date, a.start_hour, a.end_hour, a.service_id, 0 as buffer_before, 0 as buffer_after';
}
// Query appointments where the staff is the provider
$this->db->select($select_clause)
->from(db_prefix() . 'appointly_appointments a')
->join(
db_prefix() . 'appointly_services s',
'a.service_id = s.id',
'left'
)
->where('a.provider_id', $provider_id)
->where('a.date', $date)
->where('a.status !=', 'cancelled')
->where('a.status !=', 'completed')
;
$provider_appointments = $this->db->get()->result_array();
// Also get appointments where the staff is an attendee
$this->db->select($select_clause)
->from(db_prefix() . 'appointly_appointments a')
->join(
db_prefix() . 'appointly_attendees att',
'a.id = att.appointment_id',
'inner'
)
->join(
db_prefix() . 'appointly_services s',
'a.service_id = s.id',
'left'
)
->where('att.staff_id', $provider_id)
->where('a.date', $date)
->where('a.status !=', 'cancelled')
->where('a.status !=', 'completed')
;
$attendee_appointments = $this->db->get()->result_array();
// Merge both sets of appointments (avoiding duplicates)
$busy_times = [];
$processed_ids = [];
foreach (
array_merge($provider_appointments, $attendee_appointments) as
$appointment
) {
// Skip if we've already processed this appointment
if (in_array((int)$appointment['id'], $processed_ids, true)) {
continue;
}
$processed_ids[] = (int)$appointment['id'];
$busy_times[] = $appointment;
}
log_message(
'debug',
'Get busy times by date - Provider: ' . $provider_id . ', Date: ' . $date
. ', Found: ' . count($busy_times) . ' appointments'
);
return $busy_times;
}
/**
* Get appointments for a specific contact (used by client-side dashboard)
*
* @param int $contact_id
* @return array
*/
public function get_client_appointments($contact_id)
{
// Get the contact's email for external appointment matching
$contact = $this->db->select('email')
->where('id', $contact_id)
->get(db_prefix() . 'contacts')
->row();
if (!$contact) {
return [];
}
// Get appointments for specific contact - include both internal and external appointments
$this->db->select(db_prefix() . 'appointly_appointments.*');
$this->db->from(db_prefix() . 'appointly_appointments');
$this->db->where_in(db_prefix() . 'appointly_appointments.source', ['internal', 'external']);
// Match appointments either by:
// 1. contact_id (for internal appointments with existing contacts)
// 2. email address (for external appointments that match this contact's email)
$this->db->group_start();
$this->db->where(db_prefix() . 'appointly_appointments.contact_id', $contact_id);
if (!empty($contact->email)) {
$this->db->or_where(db_prefix() . 'appointly_appointments.email', $contact->email);
}
$this->db->group_end();
$this->db->order_by(db_prefix() . 'appointly_appointments.date', 'desc');
return $this->db->get()->result_array();
}
/**
* Get all appointments for a client/company (used by admin-side customer profile)
*
* @param int $client_id
* @return array
*/
public function get_client_company_appointments($client_id)
{
// Get client contact emails first to avoid collation issues
$contact_emails = $this->db->select('email')
->where('userid', $client_id)
->get(db_prefix() . 'contacts')
->result_array();
$emails = array_column($contact_emails, 'email');
$emails = array_filter($emails); // Remove empty emails
// Get appointments for entire client company - include both internal and external appointments
$this->db->select(db_prefix() . 'appointly_appointments.*');
$this->db->from(db_prefix() . 'appointly_appointments');
$this->db->where_in(db_prefix() . 'appointly_appointments.source', ['internal', 'external']);
// Match appointments either by:
// 1. contact_id (for internal appointments with existing contacts)
// 2. email address (for external appointments)
$this->db->group_start();
$this->db->where(db_prefix() . 'appointly_appointments.contact_id IN (SELECT id FROM ' . db_prefix() . 'contacts WHERE userid = ' . $this->db->escape($client_id) . ')');
if (!empty($emails)) {
$this->db->or_where_in(db_prefix() . 'appointly_appointments.email', $emails);
}
$this->db->group_end();
// Apply staff permission filtering (same logic as other appointment views)
if (!is_admin()) {
// All staff (regardless of permissions) can only see appointments they're connected to:
// 1. Created by them, 2. Assigned as provider, 3. Listed as attendee
$this->db->where('(' . db_prefix() . 'appointly_appointments.created_by=' . get_staff_user_id()
. ' OR ' . db_prefix() . 'appointly_appointments.provider_id=' . get_staff_user_id()
. ' OR ' . db_prefix() . 'appointly_appointments.id IN (SELECT appointment_id FROM '
. db_prefix() . 'appointly_attendees WHERE staff_id=' . get_staff_user_id() . '))');
}
$this->db->order_by(db_prefix() . 'appointly_appointments.date', 'desc');
return $this->db->get()->result_array();
}
/**
* Get appointments for a lead
*
* @param int $lead_id
* @return array
*/
public function get_lead_appointments($lead_id)
{
$this->db->select(db_prefix() . 'appointly_appointments.*');
$this->db->from(db_prefix() . 'appointly_appointments');
$this->db->where(db_prefix() . 'appointly_appointments.source', 'lead_related');
$this->db->where(db_prefix() . 'appointly_appointments.contact_id', $lead_id);
$this->db->order_by(db_prefix() . 'appointly_appointments.date', 'desc');
return $this->db->get()->result_array();
}
/**
* Get upcoming appointments with configurable date range
*
* @param string $range - '7_days', '14_days', '30_days', '4_weeks'
* @return array
*/
public function fetch_upcoming_appointments($range = '7_days')
{
$startDate = date('Y-m-d');
$endDate = '';
// Calculate end date based on range
switch ($range) {
case '7_days':
$endDate = date('Y-m-d', strtotime('+7 days'));
break;
case '14_days':
$endDate = date('Y-m-d', strtotime('+14 days'));
break;
case '30_days':
$endDate = date('Y-m-d', strtotime('+30 days'));
break;
case '4_weeks':
$endDate = date('Y-m-d', strtotime('+4 weeks'));
break;
default:
$endDate = date('Y-m-d', strtotime('+7 days'));
}
// Check if user has any appointments permissions at all
if (!staff_can('view', 'appointments')) {
return []; // No permissions = no appointments
}
$this->db->select('*');
$this->db->from(db_prefix() . 'appointly_appointments');
if (!is_admin()) {
// All non-admin staff can only see appointments they're connected to:
// 1. Created by them, 2. Assigned as provider, 3. Listed as attendee
$this->db->where('(created_by=' . get_staff_user_id()
. ' OR provider_id=' . get_staff_user_id()
. ' OR id IN (SELECT appointment_id FROM ' . db_prefix() . 'appointly_attendees WHERE staff_id=' . get_staff_user_id() . '))');
}
$this->db->where('date >=', $startDate);
$this->db->where('date <=', $endDate);
$this->db->where('status !=', 'cancelled');
$this->db->where('status !=', 'completed');
// Exclude internal staff appointments from widget
if ($_SERVER['REQUEST_URI'] != '/admin/appointly/appointments') {
$this->db->where('source !=', 'internal_staff');
}
$this->db->order_by('date', 'ASC');
$this->db->order_by('start_hour', 'ASC');
$this->db->limit(20); // Limit to 20 appointments to avoid overwhelming the widget
$dbAppointments = $this->db->get()->result_array();
// Extract all Google event IDs from database appointments to avoid duplicates
$googleEventIds = [];
foreach ($dbAppointments as $appointment) {
if (!empty($appointment['google_event_id'])) {
$googleEventIds[] = $appointment['google_event_id'];
}
}
$googleCalendarAppointments = [];
$isGoogleAuthenticatedAndShowInTable = appointlyGoogleAuth() && get_option('appointments_googlesync_show_in_table');
if ($isGoogleAuthenticatedAndShowInTable) {
$googleCalendarAppointments = appointlyGetGoogleCalendarData();
// Filter Google calendar appointments to only include upcoming appointments in range
$googleCalendarAppointments = array_filter(
$googleCalendarAppointments,
static function ($appointment) use ($startDate, $endDate, $googleEventIds) {
$appointmentDate = $appointment['date'] ?? '';
$isInRange = $appointmentDate >= $startDate && $appointmentDate <= $endDate;
// Check if this appointment is not already in our database
$isNotInDatabase = empty($appointment['id']) && (!isset($appointment['google_event_id'])
|| !in_array($appointment['google_event_id'], $googleEventIds));
return $isInRange && $isNotInDatabase;
}
);
// Ensure each Google calendar appointment has source='google'
foreach ($googleCalendarAppointments as &$appointment) {
$appointment['source'] = 'google';
}
}
return array_merge($dbAppointments, $googleCalendarAppointments);
}
/**
* Submit client cancellation request (used by logged-in clients)
*
* @param int $appointment_id
* @param string $reason
* @return bool
*/
public function submit_client_cancellation_request($appointment_id, $reason)
{
// Get appointment data (using get_appointment_data for client access)
$appointment = $this->get_appointment_data($appointment_id);
if (!$appointment) {
return false;
}
// Update appointment with cancellation notes
$this->db->where('id', $appointment_id);
$this->db->update(db_prefix() . 'appointly_appointments', [
'cancel_notes' => $reason,
]);
if ($this->db->affected_rows() > 0) {
// Send notifications to staff about the cancellation request
$this->send_client_cancellation_request_notifications($appointment, $reason);
return true;
}
return false;
}
/**
* Submit client reschedule request (used by logged-in clients)
*
* @param int $appointment_id
* @param string $new_date
* @param string $new_time
* @param string $reason
* @param int $contact_id
* @return bool
*/
public function submit_client_reschedule_request($appointment_id, $new_date, $new_time, $reason, $contact_id)
{
// Get appointment data (using get_appointment_data for client access)
$appointment = $this->get_appointment_data($appointment_id);
if (!$appointment) {
return false;
}
// Create reschedule request
$reschedule_id = $this->create_reschedule_request(
$appointment_id,
$new_date,
$new_time,
$reason
);
if ($reschedule_id) {
// Send notification to staff (reuse existing method)
$this->_send_reschedule_notification_to_staff($appointment, $new_date, $new_time, $reason);
// Send confirmation email to client
$template = mail_template(
'appointly_appointment_reschedule_request_confirmation_to_client',
'appointly',
array_to_object($appointment)
);
if ($template) {
// Set the reschedule details in merge fields
$merge_fields = $template->get_merge_fields();
$merge_fields['{reschedule_requested_date}'] = _dt($new_date);
$merge_fields['{reschedule_requested_time}'] = $new_time;
$merge_fields['{reschedule_reason}'] = $reason;
$template->set_merge_fields($merge_fields);
$template->send();
}
return true;
}
return false;
}
/**
* Send reschedule notification to staff (shared method)
*
* @param array $appointment
* @param string $new_date
* @param string $new_time
* @param string $reason
*/
public function _send_reschedule_notification_to_staff($appointment, $new_date, $new_time, $reason)
{
$notified_users = [];
// Notify provider if exists and not current user
if (!empty($appointment['provider_id']) && (!function_exists('get_staff_user_id') || $appointment['provider_id'] !== get_staff_user_id())) {
$provider_staff = appointly_get_staff($appointment['provider_id']);
if (!empty($provider_staff)) {
// Add notification
add_notification([
'description' => 'appointment_reschedule_requested',
'touserid' => $appointment['provider_id'],
'fromcompany' => true,
'link' => 'appointly/appointments/view?appointment_id=' . $appointment['id'],
]);
$notified_users[] = $appointment['provider_id'];
// Send email using the correct appointly pattern
send_mail_template(
'appointly_appointment_reschedule_request_to_staff',
'appointly',
array_to_object($appointment),
array_to_object($provider_staff)
);
}
}
// Notify creator if different from provider and not current user
if (!empty($appointment['created_by']) && $appointment['created_by'] != $appointment['provider_id'] && (!function_exists('get_staff_user_id') || $appointment['created_by'] !== get_staff_user_id())) {
$creator_staff = appointly_get_staff($appointment['created_by']);
if (!empty($creator_staff)) {
// Add notification
add_notification([
'description' => 'appointment_reschedule_requested',
'touserid' => $appointment['created_by'],
'fromcompany' => true,
'link' => 'appointly/appointments/view?appointment_id=' . $appointment['id'],
]);
$notified_users[] = $appointment['created_by'];
// Send email using the correct appointly pattern
send_mail_template(
'appointly_appointment_reschedule_request_to_staff',
'appointly',
array_to_object($appointment),
array_to_object($creator_staff)
);
}
}
pusher_trigger_notification(array_unique($notified_users));
log_activity('Appointment Reschedule Requested [AppointmentID: ' . $appointment['id'] . ', Requested Date: ' . $new_date . ' ' . $new_time . ', Reason: ' . $reason . ']');
}
}