/home/edulekha/crm.edulekha.com/modules/appointly/migrations/127_version_127.php
<?php
defined('BASEPATH') or exit('No direct script access allowed');
class Migration_Version_127 extends App_module_migration
{
public function up()
{
$CI = &get_instance();
$appointments_show_past_dates = get_option('appointments_show_past_dates');
if (!$appointments_show_past_dates) {
update_option('appointments_show_past_dates', 0);
}
// Add new option for booking services availability
$appointments_enable_terms_conditions = get_option('appointments_enable_terms_conditions');
if (!$appointments_enable_terms_conditions) {
update_option('appointments_enable_terms_conditions', 0);
}
$appointments_booking_services_availability = get_option('appointments_booking_services_availability');
if (!$appointments_booking_services_availability) {
update_option('appointments_booking_services_availability', json_encode([1, 2]));
}
// Create company schedule table
$CI->db->query("CREATE TABLE IF NOT EXISTS `" . db_prefix() . "appointly_company_schedule` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`weekday` ENUM('Monday','Tuesday','Wednesday','Thursday','Friday','Saturday','Sunday') NOT NULL,
`start_time` TIME NOT NULL DEFAULT '09:00:00',
`end_time` TIME NOT NULL DEFAULT '17:00:00',
`is_enabled` TINYINT(1) NOT NULL DEFAULT 1,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;");
// Populate company schedule with default values
$weekdays = ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday'];
$insertData = [];
foreach ($weekdays as $index => $day) {
$isWeekend = ($index > 4); // Saturday and Sunday
$insertData[] = [
'weekday' => $day,
'start_time' => '09:00:00',
'end_time' => '17:00:00',
'is_enabled' => $isWeekend ? 0 : 1
];
}
$CI->db->insert_batch(db_prefix() . 'appointly_company_schedule', $insertData);
// Create staff working hours table
$CI->db->query("CREATE TABLE IF NOT EXISTS `" . db_prefix() . "appointly_staff_working_hours` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`staff_id` int(11) NOT NULL,
`weekday` ENUM('Monday','Tuesday','Wednesday','Thursday','Friday','Saturday','Sunday') NOT NULL,
`start_time` TIME NOT NULL,
`end_time` TIME NOT NULL,
`is_available` TINYINT(1) NOT NULL DEFAULT 1,
`use_company_schedule` TINYINT(1) NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
KEY `staff_id` (`staff_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;");
// Update services table - remove working_hours, staff_members fields
if ($CI->db->field_exists('working_hours', db_prefix() . 'appointly_services')) {
$CI->db->query("ALTER TABLE `" . db_prefix() . "appointly_services` DROP COLUMN `working_hours`;");
}
if ($CI->db->field_exists('staff_members', db_prefix() . 'appointly_services')) {
$CI->db->query("ALTER TABLE `" . db_prefix() . "appointly_services` DROP COLUMN `staff_members`;");
}
// Add buffer times to services
if (!$CI->db->field_exists('buffer_before', db_prefix() . 'appointly_services')) {
$CI->db->query("ALTER TABLE `" . db_prefix() . "appointly_services`
ADD COLUMN `buffer_before` int(11) DEFAULT 0 AFTER `duration`,
ADD COLUMN `buffer_after` int(11) DEFAULT 0 AFTER `buffer_before`;");
}
// Update service_staff table - add is_primary flag, remove working_hours
if (!$CI->db->field_exists('is_primary', db_prefix() . 'appointly_service_staff')) {
$CI->db->query("ALTER TABLE `" . db_prefix() . "appointly_service_staff`
ADD COLUMN `is_primary` TINYINT(1) DEFAULT 0 AFTER `is_provider`;");
// Mark the first provider for each service as primary
$CI->db->query("
UPDATE " . db_prefix() . "appointly_service_staff ss1
JOIN (
SELECT service_id, MIN(id) as min_id
FROM " . db_prefix() . "appointly_service_staff
WHERE is_provider = 1
GROUP BY service_id
) ss2 ON ss1.service_id = ss2.service_id AND ss1.id = ss2.min_id
SET ss1.is_primary = 1
");
}
if ($CI->db->field_exists('working_hours', db_prefix() . 'appointly_service_staff')) {
$CI->db->query("ALTER TABLE `" . db_prefix() . "appointly_service_staff` DROP COLUMN `working_hours`;");
}
// Check if the columns exist before trying to remove them
if ($CI->db->field_exists('availability_days', db_prefix() . 'appointly_services')) {
$CI->db->query('ALTER TABLE `' . db_prefix() . 'appointly_services` DROP COLUMN `availability_days`');
}
if ($CI->db->field_exists('availability_hours_start', db_prefix() . 'appointly_services')) {
$CI->db->query('ALTER TABLE `' . db_prefix() . 'appointly_services` DROP COLUMN `availability_hours_start`');
}
if ($CI->db->field_exists('availability_hours_end', db_prefix() . 'appointly_services')) {
$CI->db->query('ALTER TABLE `' . db_prefix() . 'appointly_services` DROP COLUMN `availability_hours_end`');
}
// Update appointments table - add status field, buffer fields
if (!$CI->db->field_exists('status', db_prefix() . 'appointly_appointments')) {
$CI->db->query("ALTER TABLE `" . db_prefix() . "appointly_appointments`
ADD COLUMN `status` ENUM('pending', 'cancelled', 'completed', 'no-show', 'in-progress')
NOT NULL DEFAULT 'in-progress' AFTER `hash`");
// Drop legacy status fields only if we can safely remove them
$legacy_fields = ['approved', 'cancelled', 'finished'];
// First verify all appointments have a valid status value
$statuses = $CI->db->query("SELECT DISTINCT status FROM " . db_prefix() . "appointly_appointments")->result_array();
$status_values = array_column($statuses, 'status');
$valid_statuses = ['pending', 'cancelled', 'completed', 'no-show', 'in-progress'];
$has_invalid = false;
foreach ($status_values as $status) {
if (!in_array($status, $valid_statuses)) {
$has_invalid = true;
log_message('error', "Appointly Migration 127: Found invalid status value: $status");
break;
}
}
if (!$has_invalid) {
// Safe to drop the legacy fields
foreach ($legacy_fields as $field) {
if ($CI->db->field_exists($field, db_prefix() . 'appointly_appointments')) {
$CI->db->query("ALTER TABLE `" . db_prefix() . "appointly_appointments` DROP COLUMN `$field`");
log_message('info', "Appointly Migration 127: Dropped legacy status field: $field");
}
}
}
}
// Ensure we have appointment-service connections for all appointments
$CI->db->query("INSERT IGNORE INTO `" . db_prefix() . "appointly_appointment_services`
(appointment_id, service_id)
SELECT id, service_id
FROM `" . db_prefix() . "appointly_appointments`
WHERE service_id IS NOT NULL
AND id NOT IN (SELECT appointment_id FROM `" . db_prefix() . "appointly_appointment_services`)");
// Email templates
create_email_template(
'Your appointment has been updated!',
'<span style=\"font-size: 12pt;\"> Hello {appointment_client_name}.</span><br /><br /><span style=\"font-size: 12pt;\"> Your appointment has been updated.</span><br /><br /><span style=\"font-size: 12pt;\"><strong>Updated Appointment Details:</strong></span><br /><span style=\"font-size: 12pt;\"><strong>Appointment Subject:</strong> {appointment_subject}</span><br /><span style=\"font-size: 12pt;\"><strong>Appointment Description:</strong> {appointment_description}</span><br /><span style=\"font-size: 12pt;\"><strong>Appointment scheduled date:</strong> {appointment_date}</span><br /><span style=\"font-size: 12pt;\"><strong>You can view this appointment at the following link:</strong> <a href="{appointment_public_url}">Your appointment URL</a></span><br /><span style=\"font-size: 12pt;\"><br />Kind Regards</span><br /><br /><span style=\"font-size: 12pt;\">{email_signature}</span>',
'appointly',
'Appointment updated (Sent to Contact)',
'appointment-updated-to-contact'
);
create_email_template(
'Your appointment has been updated!',
'<span style=\"font-size: 12pt;\"> Hello {staff_firstname} {staff_lastname}.</span><br /><br /><span style=\"font-size: 12pt;\"> An appointment that you are attending has been updated.</span><br /><br /><span style=\"font-size: 12pt;\"><strong>Updated Appointment Details:</strong></span><br /><span style=\"font-size: 12pt;\"><strong>Appointment Subject:</strong> {appointment_subject}</span><br /><span style=\"font-size: 12pt;\"><strong>Appointment Description:</strong> {appointment_description}</span><br /><span style=\"font-size: 12pt;\"><strong>Appointment scheduled date:</strong> {appointment_date}</span><br /><span style=\"font-size: 12pt;\"><strong>Client:</strong> {appointment_client_name}</span><br /><span style=\"font-size: 12pt;\"><strong>You can view this appointment at the following link:</strong> <a href="{appointment_admin_url}">Your appointment URL</a></span><br /><span style=\"font-size: 12pt;\"><br />Kind Regards</span><br /><br /><span style=\"font-size: 12pt;\">{email_signature}</span>',
'appointly',
'Appointment updated (Sent to Staff)',
'appointment-updated-to-staff'
);
}
}