function wpdev_booking_activate()
{
update_bk_option('booking_version_num', WP_BK_VERSION_NUM);
$version = get_bk_version();
$is_demo = wpdev_bk_is_this_demo();
load_bk_Translation();
// set execution time to 15 minutes, its not worked if we have SAFE MODE ON at PHP
if (function_exists('set_time_limit')) {
if (!in_array(ini_get('safe_mode'), array('1', 'On'))) {
set_time_limit(900);
}
}
add_bk_option('booking_admin_cal_count', $is_demo ? '3' : '2');
add_bk_option('booking_skin', '/css/skins/traditional.css');
add_bk_option('bookings_num_per_page', '10');
add_bk_option('booking_sort_order', '');
add_bk_option('booking_default_toolbar_tab', 'filter');
add_bk_option('bookings_listing_default_view_mode', 'vm_calendar');
//,'vm_listing');
if ($version == 'free') {
add_bk_option('booking_view_days_num', '90');
} else {
add_bk_option('booking_view_days_num', '30');
}
// Month view for several resources
//add_bk_option( 'booking_sort_order_direction', 'ASC');
add_bk_option('booking_max_monthes_in_calendar', '1y');
add_bk_option('booking_client_cal_count', '1');
add_bk_option('booking_start_day_weeek', '0');
add_bk_option('booking_title_after_reservation', sprintf(__('Thank you for your online booking. %s We will send confirmation of your booking as soon as possible.', 'wpdev-booking'), ''));
add_bk_option('booking_title_after_reservation_time', '7000');
add_bk_option('booking_type_of_thank_you_message', 'message');
add_bk_option('booking_thank_you_page_URL', '/thank-you');
add_bk_option('booking_is_use_autofill_4_logged_user', $is_demo ? 'On' : 'Off');
add_bk_option('booking_date_format', get_option('date_format'));
add_bk_option('booking_date_view_type', 'short');
// short / wide
add_bk_option('booking_is_delete_if_deactive', $is_demo ? 'On' : 'Off');
// check
add_bk_option('booking_dif_colors_approval_pending', 'On');
add_bk_option('booking_is_use_hints_at_admin_panel', 'On');
add_bk_option('booking_is_not_load_bs_script_in_client', 'Off');
add_bk_option('booking_is_not_load_bs_script_in_admin', 'Off');
// Set the type of days selections based on the previous saved data ....
$booking_type_of_day_selections = 'multiple';
//'single';
if (get_bk_option('booking_multiple_day_selections') == 'On') {
$booking_type_of_day_selections = 'multiple';
}
if (get_bk_option('booking_range_selection_is_active') == 'On') {
$booking_type_of_day_selections = 'range';
}
if ($is_demo) {
$booking_type_of_day_selections = 'multiple';
}
add_bk_option('booking_type_of_day_selections', $booking_type_of_day_selections);
add_bk_option('booking_form_is_using_bs_css', 'On');
add_bk_option('booking_form_format_type', 'vertical');
add_bk_option('booking_form_field_active1', 'On');
add_bk_option('booking_form_field_required1', 'On');
add_bk_option('booking_form_field_label1', 'First Name');
add_bk_option('booking_form_field_active2', 'On');
add_bk_option('booking_form_field_required2', 'On');
add_bk_option('booking_form_field_label2', 'Last Name');
add_bk_option('booking_form_field_active3', 'On');
add_bk_option('booking_form_field_required3', 'On');
add_bk_option('booking_form_field_label3', 'Email');
add_bk_option('booking_form_field_active4', 'On');
add_bk_option('booking_form_field_required4', 'Off');
add_bk_option('booking_form_field_label4', 'Phone');
add_bk_option('booking_form_field_active5', 'On');
add_bk_option('booking_form_field_required5', 'Off');
add_bk_option('booking_form_field_label5', 'Details');
add_bk_option('booking_unavailable_days_num_from_today', '0');
add_bk_option('booking_unavailable_day0', 'Off');
add_bk_option('booking_unavailable_day1', 'Off');
add_bk_option('booking_unavailable_day2', 'Off');
add_bk_option('booking_unavailable_day3', 'Off');
add_bk_option('booking_unavailable_day4', 'Off');
add_bk_option('booking_unavailable_day5', 'Off');
add_bk_option('booking_unavailable_day6', 'Off');
if ($is_demo) {
add_bk_option('booking_user_role_booking', 'subscriber');
add_bk_option('booking_user_role_addbooking', 'subscriber');
add_bk_option('booking_user_role_resources', 'subscriber');
add_bk_option('booking_user_role_settings', 'subscriber');
} else {
add_bk_option('booking_user_role_booking', 'editor');
add_bk_option('booking_user_role_addbooking', 'editor');
add_bk_option('booking_user_role_resources', 'editor');
add_bk_option('booking_user_role_settings', 'administrator');
}
$blg_title = get_option('blogname');
$blg_title = str_replace('"', '', $blg_title);
$blg_title = str_replace("'", '', $blg_title);
add_bk_option('booking_email_reservation_adress', htmlspecialchars('"Booking system" <' . get_option('admin_email') . '>'));
add_bk_option('booking_email_reservation_from_adress', '[visitoremail]');
//htmlspecialchars('"Booking system" <' .get_option('admin_email').'>'));
add_bk_option('booking_email_reservation_subject', __('New booking', 'wpdev-booking'));
add_bk_option('booking_email_reservation_content', htmlspecialchars(sprintf(__('You need to approve a new booking %s for: %s Person detail information:%s Currently a new booking is waiting for approval. Please visit the moderation panel%sThank you, %s', 'wpdev-booking'), '[bookingtype]', '[dates]<br/><br/>', '<br/> [content]<br/><br/>', ' [moderatelink]<br/><br/>', $blg_title . '<br/>[siteurl]')));
add_bk_option('booking_email_approval_adress', htmlspecialchars('"Booking system" <' . get_option('admin_email') . '>'));
add_bk_option('booking_email_approval_subject', __('Your booking has been approved', 'wpdev-booking'));
if ($this->wpdev_bk_personal !== false) {
add_bk_option('booking_email_approval_content', htmlspecialchars(sprintf(__('Your reservation %s for: %s has been approved.%sYou can edit the booking on this page: %s Thank you, %s', 'wpdev-booking'), '[bookingtype]', '[dates]', '<br/><br/>[content]<br/><br/>', '[visitorbookingediturl]<br/><br/>', $blg_title . '<br/>[siteurl]')));
} else {
add_bk_option('booking_email_approval_content', htmlspecialchars(sprintf(__('Your booking %s for: %s has been approved.%sThank you, %s', 'wpdev-booking'), '[bookingtype]', '[dates]', '<br/><br/>[content]<br/><br/>', $blg_title . '<br/>[siteurl]')));
}
add_bk_option('booking_email_deny_adress', htmlspecialchars('"Booking system" <' . get_option('admin_email') . '>'));
add_bk_option('booking_email_deny_subject', __('Your booking has been declined', 'wpdev-booking'));
add_bk_option('booking_email_deny_content', htmlspecialchars(sprintf(__('Your booking %s for: %s has been canceled. %sThank you, %s', 'wpdev-booking'), '[bookingtype]', '[dates]', '<br/><br/>[denyreason]<br/><br/>[content]<br/><br/>', $blg_title . '<br/>[siteurl]')));
add_bk_option('booking_is_email_reservation_adress', 'On');
add_bk_option('booking_is_email_approval_adress', 'On');
add_bk_option('booking_is_email_deny_adress', 'On');
add_bk_option('booking_widget_title', __('Booking form', 'wpdev-booking'));
add_bk_option('booking_widget_show', 'booking_form');
add_bk_option('booking_widget_type', '1');
add_bk_option('booking_widget_calendar_count', '1');
add_bk_option('booking_widget_last_field', '');
add_bk_option('booking_wpdev_copyright_adminpanel', 'On');
add_bk_option('booking_is_show_powered_by_notice', 'On');
add_bk_option('booking_is_use_captcha', 'Off');
add_bk_option('booking_is_show_legend', 'Off');
add_bk_option('booking_legend_is_show_item_available', 'On');
add_bk_option('booking_legend_text_for_item_available', __('Available', 'wpdev-booking'));
add_bk_option('booking_legend_is_show_item_pending', 'On');
add_bk_option('booking_legend_text_for_item_pending', __('Pending', 'wpdev-booking'));
add_bk_option('booking_legend_is_show_item_approved', 'On');
add_bk_option('booking_legend_text_for_item_approved', __('Booked', 'wpdev-booking'));
if (class_exists('wpdev_bk_biz_s')) {
add_bk_option('booking_legend_is_show_item_partially', 'On');
add_bk_option('booking_legend_text_for_item_partially', __('Partially booked', 'wpdev-booking'));
}
// Create here tables which is needed for using plugin
global $wpdb;
$charset_collate = '';
//if ( $wpdb->has_cap( 'collation' ) ) {
if (!empty($wpdb->charset)) {
$charset_collate = "DEFAULT CHARACTER SET {$wpdb->charset}";
}
if (!empty($wpdb->collate)) {
$charset_collate .= " COLLATE {$wpdb->collate}";
}
//}
$wp_queries = array();
if (!$this->is_table_exists('booking')) {
// Cehck if tables not exist yet
$simple_sql = "CREATE TABLE {$wpdb->prefix}booking (\n booking_id bigint(20) unsigned NOT NULL auto_increment,\n form text ,\n booking_type bigint(10) NOT NULL default 1,\n PRIMARY KEY (booking_id)\n ) {$charset_collate};";
$wpdb->query($simple_sql);
} elseif ($this->is_field_in_table_exists('booking', 'form') == 0) {
$wp_queries[] = "ALTER TABLE {$wpdb->prefix}booking ADD form TEXT AFTER booking_id";
}
if ($this->is_field_in_table_exists('booking', 'modification_date') == 0) {
$wp_queries[] = "ALTER TABLE {$wpdb->prefix}booking ADD modification_date datetime AFTER booking_id";
}
if ($this->is_field_in_table_exists('booking', 'sort_date') == 0) {
$wp_queries[] = "ALTER TABLE {$wpdb->prefix}booking ADD sort_date datetime AFTER booking_id";
}
if ($this->is_field_in_table_exists('booking', 'status') == 0) {
$wp_queries[] = "ALTER TABLE {$wpdb->prefix}booking ADD status varchar(200) NOT NULL default '' AFTER booking_id";
}
if ($this->is_field_in_table_exists('booking', 'is_new') == 0) {
$wp_queries[] = "ALTER TABLE {$wpdb->prefix}booking ADD is_new bigint(10) NOT NULL default 1 AFTER booking_id";
}
if (!$this->is_table_exists('bookingdates')) {
// Cehck if tables not exist yet
$simple_sql = "CREATE TABLE {$wpdb->prefix}bookingdates (\n booking_id bigint(20) unsigned NOT NULL,\n booking_date datetime NOT NULL default '0000-00-00 00:00:00',\n approved bigint(20) unsigned NOT NULL default 0\n ) {$charset_collate}";
$wpdb->query($simple_sql);
if ($this->wpdev_bk_personal == false) {
$wp_queries[] = "INSERT INTO {$wpdb->prefix}booking ( form, modification_date ) VALUES (\n 'text^name1^Jony~text^secondname1^Smith~text^email1^example-free@wpbookingcalendar.com~text^phone1^8(038)458-77-77~textarea^details1^Reserve a room with sea view', NOW() );";
}
}
if (!class_exists('wpdev_bk_biz_l')) {
if ($this->is_index_in_table_exists('bookingdates', 'booking_id_dates') == 0) {
$simple_sql = "CREATE UNIQUE INDEX booking_id_dates ON {$wpdb->prefix}bookingdates (booking_id, booking_date);";
$wpdb->query($simple_sql);
}
} else {
if ($this->is_index_in_table_exists('bookingdates', 'booking_id_dates') != 0) {
$simple_sql = "DROP INDEX booking_id_dates ON {$wpdb->prefix}bookingdates ;";
$wpdb->query($simple_sql);
}
}
if (count($wp_queries) > 0) {
foreach ($wp_queries as $wp_q) {
$wpdb->query($wp_q);
}
if ($this->wpdev_bk_personal == false) {
$temp_id = $wpdb->insert_id;
$wp_queries_sub = "INSERT INTO {$wpdb->prefix}bookingdates (\n booking_id,\n booking_date\n ) VALUES\n ( " . $temp_id . ", CURDATE()+ INTERVAL 2 day ),\n ( " . $temp_id . ", CURDATE()+ INTERVAL 3 day ),\n ( " . $temp_id . ", CURDATE()+ INTERVAL 4 day );";
$wpdb->query($wp_queries_sub);
}
}
// if( $this->wpdev_bk_personal !== false ) $this->wpdev_bk_personal->pro_activate();
make_bk_action('wpdev_booking_activation');
// Examples in demos
if ($is_demo) {
$this->createExamples4Demo();
}
/*
// Fill Development server by initial bookings
if ( $_SERVER['HTTP_HOST'] === 'dev' ) {
for ($i = 0; $i < 5; $i++) {
//if (!class_exists('wpdev_bk_personal'))
$this->createExamples4Demo( array(1,2,3,4,5,6,7,8,9,10,11,12) );
}
}
$this->setDefaultInitialValues();/**/
$this->reindex_booking_db();
}
function wpdev_booking_activate()
{
load_bk_Translation();
// set execution time to 15 minutes, its not worked if we have SAFE MODE ON at PHP
if (function_exists('set_time_limit')) {
if (!in_array(ini_get('safe_mode'), array('1', 'On'))) {
set_time_limit(900);
}
}
if (wpdev_bk_is_this_demo()) {
add_bk_option('booking_admin_cal_count', '3');
} else {
add_bk_option('booking_admin_cal_count', '2');
}
add_bk_option('booking_skin', WPDEV_BK_PLUGIN_URL . '/css/skins/traditional.css');
add_bk_option('bookings_num_per_page', '10');
add_bk_option('booking_sort_order', '');
add_bk_option('booking_default_toolbar_tab', 'filter');
//add_bk_option( 'booking_sort_order_direction', 'ASC');
add_bk_option('booking_max_monthes_in_calendar', '1y');
add_bk_option('booking_client_cal_count', '1');
add_bk_option('booking_start_day_weeek', '0');
add_bk_option('booking_title_after_reservation', sprintf(__('Thank you for your online booking. %s We will send confirmation of your booking as soon as possible.', 'wpdev-booking'), ''));
add_bk_option('booking_title_after_reservation_time', '7000');
add_bk_option('booking_type_of_thank_you_message', 'message');
add_bk_option('booking_thank_you_page_URL', site_url());
add_bk_option('booking_is_use_autofill_4_logged_user', 'On');
add_bk_option('booking_date_format', get_option('date_format'));
add_bk_option('booking_date_view_type', 'short');
// short / wide
if (wpdev_bk_is_this_demo()) {
add_bk_option('booking_is_delete_if_deactive', 'On');
} else {
add_bk_option('booking_is_delete_if_deactive', 'Off');
}
// check
add_bk_option('booking_dif_colors_approval_pending', 'On');
add_bk_option('booking_is_use_hints_at_admin_panel', 'On');
add_bk_option('booking_is_not_load_bs_script_in_client', 'Off');
add_bk_option('booking_is_not_load_bs_script_in_admin', 'Off');
add_bk_option('booking_multiple_day_selections', 'On');
add_bk_option('booking_unavailable_days_num_from_today', '0');
add_bk_option('booking_unavailable_day0', 'Off');
add_bk_option('booking_unavailable_day1', 'Off');
add_bk_option('booking_unavailable_day2', 'Off');
add_bk_option('booking_unavailable_day3', 'Off');
add_bk_option('booking_unavailable_day4', 'Off');
add_bk_option('booking_unavailable_day5', 'Off');
add_bk_option('booking_unavailable_day6', 'Off');
if (wpdev_bk_is_this_demo()) {
add_bk_option('booking_user_role_booking', 'subscriber');
add_bk_option('booking_user_role_addbooking', 'subscriber');
add_bk_option('booking_user_role_resources', 'subscriber');
add_bk_option('booking_user_role_settings', 'subscriber');
} else {
add_bk_option('booking_user_role_booking', 'editor');
add_bk_option('booking_user_role_addbooking', 'editor');
add_bk_option('booking_user_role_resources', 'editor');
add_bk_option('booking_user_role_settings', 'administrator');
}
$blg_title = get_option('blogname');
$blg_title = str_replace('"', '', $blg_title);
$blg_title = str_replace("'", '', $blg_title);
add_bk_option('booking_email_reservation_adress', htmlspecialchars('"Booking system" <' . get_option('admin_email') . '>'));
add_bk_option('booking_email_reservation_from_adress', htmlspecialchars('"Booking system" <' . get_option('admin_email') . '>'));
add_bk_option('booking_email_reservation_subject', __('New booking', 'wpdev-booking'));
add_bk_option('booking_email_reservation_content', htmlspecialchars(sprintf(__('You need to approve new booking %s for: %s Person detail information:%s Currently new booking is waiting for approval. Please visit the moderation panel%sThank you, %s', 'wpdev-booking'), '[bookingtype]', '[dates]<br/><br/>', '<br/> [content]<br/><br/>', ' [moderatelink]<br/><br/>', $blg_title . '<br/>[siteurl]')));
add_bk_option('booking_email_approval_adress', htmlspecialchars('"Booking system" <' . get_option('admin_email') . '>'));
add_bk_option('booking_email_approval_subject', __('Your booking has been approved', 'wpdev-booking'));
if ($this->wpdev_bk_personal !== false) {
add_bk_option('booking_email_approval_content', htmlspecialchars(sprintf(__('Your booking %s for: %s has been approved.%sYou can edit this booking at this page: %s Thank you, %s', 'wpdev-booking'), '[bookingtype]', '[dates]', '<br/><br/>[content]<br/><br/>', '[visitorbookingediturl]<br/><br/>', $blg_title . '<br/>[siteurl]')));
} else {
add_bk_option('booking_email_approval_content', htmlspecialchars(sprintf(__('Your booking %s for: %s has been approved.%sThank you, %s', 'wpdev-booking'), '[bookingtype]', '[dates]', '<br/><br/>[content]<br/><br/>', $blg_title . '<br/>[siteurl]')));
}
add_bk_option('booking_email_deny_adress', htmlspecialchars('"Booking system" <' . get_option('admin_email') . '>'));
add_bk_option('booking_email_deny_subject', __('Your booking has been declined', 'wpdev-booking'));
add_bk_option('booking_email_deny_content', htmlspecialchars(sprintf(__('Your booking %s for: %s has been canceled. %sThank you, %s', 'wpdev-booking'), '[bookingtype]', '[dates]', '<br/><br/>[denyreason]<br/><br/>[content]<br/><br/>', $blg_title . '<br/>[siteurl]')));
add_bk_option('booking_is_email_reservation_adress', 'On');
add_bk_option('booking_is_email_approval_adress', 'On');
add_bk_option('booking_is_email_deny_adress', 'On');
add_bk_option('booking_widget_title', __('Booking form', 'wpdev-booking'));
add_bk_option('booking_widget_show', 'booking_form');
add_bk_option('booking_widget_type', '1');
add_bk_option('booking_widget_calendar_count', '1');
add_bk_option('booking_widget_last_field', '');
add_bk_option('booking_wpdev_copyright', 'Off');
add_bk_option('booking_is_show_powered_by_notice', 'On');
add_bk_option('booking_is_use_captcha', 'Off');
add_bk_option('booking_is_show_legend', 'Off');
// Create here tables which is needed for using plugin
global $wpdb;
$charset_collate = '';
//if ( $wpdb->has_cap( 'collation' ) ) {
if (!empty($wpdb->charset)) {
$charset_collate = "DEFAULT CHARACTER SET {$wpdb->charset}";
}
if (!empty($wpdb->collate)) {
$charset_collate .= " COLLATE {$wpdb->collate}";
}
//}
if (!$this->is_table_exists('booking')) {
$simple_sql = "CREATE TABLE " . $wpdb->prefix . "booking (\r\n booking_id bigint(20) unsigned NOT NULL auto_increment,\r\n firstname varchar(50) NOT NULL,\r\n lastname varchar(50),\r\n referrer varchar(50),\r\n created_by varchar(20) NOT NULL,\r\n created_date datetime NOT NULL,\r\n last_updated_by varchar(20) NOT NULL,\r\n last_updated_date datetime NOT NULL,\r\n PRIMARY KEY (booking_id)\r\n ) {$charset_collate};";
$wpdb->query($wpdb->prepare($simple_sql));
}
if (!$this->is_table_exists('bookingresources')) {
$simple_sql = "CREATE TABLE " . $wpdb->prefix . "bookingresources (\r\n resource_id bigint(20) unsigned NOT NULL auto_increment,\r\n name varchar(50) NOT NULL,\r\n parent_resource_id bigint(20) unsigned,\r\n resource_type varchar(10) NOT NULL,\r\n room_type varchar(2),\r\n created_by varchar(20) NOT NULL,\r\n created_date datetime NOT NULL,\r\n last_updated_by varchar(20) NOT NULL,\r\n last_updated_date datetime NOT NULL,\r\n PRIMARY KEY (resource_id),\r\n FOREIGN KEY (parent_resource_id) REFERENCES " . $wpdb->prefix . "bookingresources(resource_id)\r\n ) {$charset_collate};";
$wpdb->query($wpdb->prepare($simple_sql));
}
if (!$this->is_table_exists('mv_resources_by_path')) {
$simple_sql = "CREATE TABLE " . $wpdb->prefix . "mv_resources_by_path (\r\n resource_id bigint(20) unsigned NOT NULL,\r\n name varchar(50) NOT NULL,\r\n parent_resource_id bigint(20) unsigned,\r\n path varchar(100) NOT NULL,\r\n resource_type varchar(10) NOT NULL,\r\n room_type varchar(2),\r\n level int(10) unsigned NOT NULL,\r\n number_children int(10) unsigned NOT NULL,\r\n capacity int(10) unsigned NOT NULL,\r\n PRIMARY KEY (resource_id)\r\n ) {$charset_collate};";
$wpdb->query($wpdb->prepare($simple_sql));
}
if (!$this->is_table_exists('resource_properties')) {
$simple_sql = "CREATE TABLE " . $wpdb->prefix . "resource_properties (\r\n property_id bigint(20) unsigned NOT NULL,\r\n description varchar(20) NOT NULL,\r\n PRIMARY KEY (property_id)\r\n ) {$charset_collate};";
$wpdb->query($wpdb->prepare($simple_sql));
$simple_sql = "INSERT INTO " . $wpdb->prefix . "resource_properties (property_id, description)\r\n VALUES(%d, %s)";
$wpdb->query($wpdb->prepare($simple_sql, 1, '4-Bed Dorm'));
$wpdb->query($wpdb->prepare($simple_sql, 2, '8-Bed Dorm'));
$wpdb->query($wpdb->prepare($simple_sql, 3, '10-Bed Dorm'));
$wpdb->query($wpdb->prepare($simple_sql, 4, '12-Bed Dorm'));
$wpdb->query($wpdb->prepare($simple_sql, 5, '14-Bed Dorm'));
$wpdb->query($wpdb->prepare($simple_sql, 6, '16-Bed Dorm'));
$wpdb->query($wpdb->prepare($simple_sql, 7, 'Twin Room'));
$wpdb->query($wpdb->prepare($simple_sql, 8, 'Double Room'));
$wpdb->query($wpdb->prepare($simple_sql, 9, 'Double Deluxe Room'));
}
if (!$this->is_table_exists('resource_properties_map')) {
$simple_sql = "CREATE TABLE " . $wpdb->prefix . "resource_properties_map (\r\n resource_id bigint(20) unsigned NOT NULL,\r\n property_id bigint(20) unsigned NOT NULL,\r\n FOREIGN KEY (resource_id) REFERENCES " . $wpdb->prefix . "bookingresources(resource_id),\r\n FOREIGN KEY (property_id) REFERENCES " . $wpdb->prefix . "resource_properties(property_id)\r\n ) {$charset_collate};";
$wpdb->query($wpdb->prepare($simple_sql));
}
if (!$this->is_table_exists('bookingcomment')) {
$simple_sql = "CREATE TABLE " . $wpdb->prefix . "bookingcomment (\r\n comment_id bigint(20) unsigned NOT NULL auto_increment,\r\n booking_id bigint(20) unsigned NOT NULL,\r\n comment TEXT NOT NULL,\r\n comment_type varchar(10) NOT NULL,\r\n created_by varchar(20) NOT NULL,\r\n created_date datetime NOT NULL,\r\n PRIMARY KEY (comment_id),\r\n FOREIGN KEY (booking_id) REFERENCES " . $wpdb->prefix . "booking(booking_id)\r\n ) {$charset_collate};";
$wpdb->query($wpdb->prepare($simple_sql));
}
if (!$this->is_table_exists('allocation')) {
$simple_sql = "CREATE TABLE " . $wpdb->prefix . "allocation (\r\n allocation_id bigint(20) unsigned NOT NULL auto_increment,\r\n booking_id bigint(20) unsigned NOT NULL,\r\n resource_id bigint(20) unsigned NOT NULL,\r\n guest_name varchar(50) NOT NULL,\r\n gender varchar(1) NOT NULL,\r\n created_by varchar(20) NOT NULL,\r\n created_date datetime NOT NULL,\r\n last_updated_by varchar(20) NOT NULL,\r\n last_updated_date datetime NOT NULL,\r\n PRIMARY KEY (allocation_id),\r\n FOREIGN KEY (booking_id) REFERENCES " . $wpdb->prefix . "booking(booking_id),\r\n FOREIGN KEY (resource_id) REFERENCES " . $wpdb->prefix . "bookingresources(resource_id) \r\n ) {$charset_collate};";
$wpdb->query($wpdb->prepare($simple_sql));
}
if (!$this->is_table_exists('bookingdates')) {
$simple_sql = "CREATE TABLE " . $wpdb->prefix . "bookingdates (\r\n allocation_id bigint(20) unsigned NOT NULL,\r\n booking_date date NOT NULL,\r\n status varchar(10) NOT NULL,\r\n checked_out varchar(1) NULL,\r\n created_by varchar(20) NOT NULL,\r\n created_date datetime NOT NULL,\r\n last_updated_by varchar(20) NOT NULL,\r\n last_updated_date datetime NOT NULL,\r\n FOREIGN KEY (allocation_id) REFERENCES " . $wpdb->prefix . "allocation(allocation_id)\r\n ) {$charset_collate};";
$wpdb->query($wpdb->prepare($simple_sql));
}
if (!$this->is_routine_exists('walk_tree_path')) {
$simple_sql = "CREATE FUNCTION walk_tree_path(p_resource_id BIGINT(20) UNSIGNED) RETURNS VARCHAR(255)\r\n -- walks the wp_bookingresources table from the given resource_id down to the root\r\n -- returns the path walked delimited with /\r\n BEGIN\r\n DECLARE last_id BIGINT(20) UNSIGNED;\r\n DECLARE parent_id BIGINT(20) UNSIGNED;\r\n DECLARE return_val VARCHAR(255);\r\n \r\n SET return_val = p_resource_id;\r\n SET parent_id = p_resource_id;\r\n \r\n WHILE parent_id IS NOT NULL DO\r\n \r\n SELECT parent_resource_id INTO parent_id\r\n FROM wp_bookingresources\r\n WHERE resource_id = parent_id;\r\n \r\n SET return_val = CONCAT(IFNULL(parent_id, ''), '/', return_val);\r\n \r\n END WHILE;\r\n \r\n RETURN return_val;\r\n \r\n END;";
$wpdb->query($wpdb->prepare($simple_sql));
}
if (!$this->is_table_exists('v_resources_sub1')) {
$simple_sql = "CREATE OR REPLACE VIEW " . $wpdb->prefix . "v_resources_sub1 AS\r\n SELECT resource_id, name, parent_resource_id, walk_tree_path(resource_id) AS path, resource_type, room_type\r\n FROM " . $wpdb->prefix . "bookingresources";
$wpdb->query($wpdb->prepare($simple_sql));
}
if (!$this->is_table_exists('v_resources_by_path')) {
$simple_sql = "CREATE OR REPLACE VIEW " . $wpdb->prefix . "v_resources_by_path AS\r\n SELECT resource_id, name, parent_resource_id, path, resource_type, room_type,\r\n LENGTH(path) - LENGTH(REPLACE(path, '/', '')) AS level,\r\n (SELECT COUNT(*) FROM wp_v_resources_sub1 s1 WHERE s1.path LIKE CAST(CONCAT(s.path, '/%') AS CHAR) AND resource_type = 'bed') AS number_children,\r\n (SELECT COUNT(*) FROM wp_v_resources_sub1 s1 WHERE (s1.path LIKE CAST(CONCAT(s.path, '/%') AS CHAR) OR s1.path = s.path) AND resource_type = 'bed') AS capacity\r\n FROM " . $wpdb->prefix . "v_resources_sub1 s\r\n ORDER BY path";
// FIXME: remove number_children, rename capacity ==> number_beds
$wpdb->query($wpdb->prepare($simple_sql));
}
if (!$this->is_table_exists('v_booked_capacity')) {
$simple_sql = "CREATE OR REPLACE VIEW " . $wpdb->prefix . "v_booked_capacity (booking_date, resource_id, used_capacity) AS\r\n SELECT bd.booking_date, alloc.resource_id, COUNT(*) used_capacity\r\n FROM " . $wpdb->prefix . "bookingdates bd\r\n JOIN " . $wpdb->prefix . "allocation alloc ON bd.allocation_id = alloc.allocation_id\r\n GROUP BY bd.booking_date, alloc.resource_id";
$wpdb->query($wpdb->prepare($simple_sql));
}
if (!$this->is_table_exists('v_resource_availability')) {
$simple_sql = "CREATE OR REPLACE VIEW " . $wpdb->prefix . "v_resource_availability (booking_date, resource_id, resource_name, path, capacity, used_capacity, avail_capacity) AS\r\n SELECT bc.booking_date, \r\n rp.resource_id, \r\n rp.name AS resource_name,\r\n rp.path, \r\n rp.capacity, \r\n bc.used_capacity,\r\n CAST(rp.capacity - IFNULL(bc.used_capacity, 0) AS SIGNED) AS avail_capacity \r\n FROM " . $wpdb->prefix . "mv_resources_by_path rp\r\n LEFT OUTER JOIN " . $wpdb->prefix . "v_booked_capacity bc ON rp.resource_id = bc.resource_id\r\n WHERE rp.number_children = 0\r\n ORDER BY bc.booking_date, rp.path";
$wpdb->query($wpdb->prepare($simple_sql));
}
if (!$this->is_trigger_exists('trg_enforce_availability')) {
$simple_sql = "CREATE TRIGGER " . $wpdb->prefix . "trg_enforce_availability\r\n -- this will raise an error by selecting from a non-existent table\r\n -- in order to enforce availability for a particular resource/date\r\n BEFORE INSERT ON " . $wpdb->prefix . "bookingdates FOR EACH ROW\r\n BEGIN\r\n DECLARE p_avail_capacity INT;\r\n SELECT avail_capacity INTO p_avail_capacity\r\n FROM " . $wpdb->prefix . "v_resource_availability ra\r\n JOIN " . $wpdb->prefix . "allocation alloc ON ra.resource_id = alloc.resource_id\r\n WHERE ra.booking_date = NEW.booking_date\r\n AND alloc.allocation_id = NEW.allocation_id;\r\n \r\n IF p_avail_capacity <= 0 THEN\r\n SELECT 'Reservation conflicts with an existing reservation' INTO p_avail_capacity \r\n FROM SANITY_CHECK_RESERVATION_CONFLICT_FOUND\r\n WHERE SANITY_CHECK_RESERVATION_CONFLICT_FOUND.id = NEW.allocation_id;\r\n END IF;\r\n END";
$wpdb->query($wpdb->prepare($simple_sql));
}
if (!$this->is_trigger_exists('trg_mv_resources_by_path_ins')) {
$simple_sql = "CREATE TRIGGER " . $wpdb->prefix . "trg_mv_resources_by_path_ins\r\n -- this will update the materialized view\r\n -- whenever an insert is made on the underlying table\r\n AFTER INSERT ON " . $wpdb->prefix . "bookingresources FOR EACH ROW \r\n BEGIN\r\n INSERT INTO " . $wpdb->prefix . "mv_resources_by_path\r\n SELECT * FROM " . $wpdb->prefix . "v_resources_by_path\r\n WHERE resource_id = NEW.resource_id;\r\n END";
$wpdb->query($wpdb->prepare($simple_sql));
}
if (!$this->is_trigger_exists('trg_mv_resources_by_path_upd')) {
$simple_sql = "CREATE TRIGGER " . $wpdb->prefix . "trg_mv_resources_by_path_upd\r\n -- this will update the materialized view\r\n -- whenever an update is made on the underlying table\r\n AFTER UPDATE ON " . $wpdb->prefix . "bookingresources FOR EACH ROW \r\n BEGIN\r\n DELETE FROM " . $wpdb->prefix . "mv_resources_by_path\r\n WHERE resource_id = OLD.resource_id;\r\n\r\n INSERT INTO " . $wpdb->prefix . "mv_resources_by_path\r\n SELECT * FROM " . $wpdb->prefix . "v_resources_by_path\r\n WHERE resource_id = NEW.resource_id;\r\n END";
$wpdb->query($wpdb->prepare($simple_sql));
}
if (!$this->is_trigger_exists('trg_mv_resources_by_path_del')) {
$simple_sql = "CREATE TRIGGER " . $wpdb->prefix . "trg_mv_resources_by_path_del\r\n -- this will update the materialized view\r\n -- whenever a delete is made on the underlying table\r\n AFTER DELETE ON " . $wpdb->prefix . "bookingresources FOR EACH ROW \r\n BEGIN\r\n DELETE FROM " . $wpdb->prefix . "mv_resources_by_path\r\n WHERE resource_id = OLD.resource_id;\r\n END";
$wpdb->query($wpdb->prepare($simple_sql));
}
// if( $this->wpdev_bk_personal !== false ) $this->wpdev_bk_personal->pro_activate();
make_bk_action('wpdev_booking_activation');
//$this->setDefaultInitialValues();
}
