prefix . 'ab_veh_take_bookings'; } public static function table_availability() { global $wpdb; return $wpdb->prefix . 'ab_veh_take_availability'; } public static function create_table() { global $wpdb; $table_name = $wpdb->prefix . 'ab_veh_take_bookings'; $avail_table = $wpdb->prefix . 'ab_veh_take_availability'; $charset_collate = $wpdb->get_charset_collate(); $sql = "CREATE TABLE {$table_name} ( id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, journey_type VARCHAR(20) NOT NULL DEFAULT 'oneway', pickup_address TEXT NULL, destination_address TEXT NULL, stops LONGTEXT NULL, booking_date DATE NULL, booking_time TIME NULL, full_name VARCHAR(191) NOT NULL, email VARCHAR(191) NOT NULL, phone VARCHAR(60) NULL, num_passengers SMALLINT UNSIGNED NULL, vehicle_type VARCHAR(100) NULL, status VARCHAR(20) NOT NULL DEFAULT 'pending', created_at DATETIME NOT NULL, PRIMARY KEY (id), KEY status (status) ) {$charset_collate};"; $sql_avail = "CREATE TABLE {$avail_table} ( id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, avail_date DATE NOT NULL, status VARCHAR(20) NOT NULL DEFAULT 'wide', PRIMARY KEY (id), UNIQUE KEY avail_date (avail_date) ) {$charset_collate};"; require_once ABSPATH . 'wp-admin/includes/upgrade.php'; dbDelta( $sql ); dbDelta( $sql_avail ); } public static function insert_booking( $data ) { global $wpdb; $defaults = array( 'journey_type' => 'oneway', 'pickup_address' => '', 'destination_address' => '', 'stops' => '', 'booking_date' => null, 'booking_time' => null, 'full_name' => '', 'email' => '', 'phone' => '', 'num_passengers' => null, 'vehicle_type' => '', 'status' => 'pending', 'created_at' => current_time( 'mysql' ), ); $data = wp_parse_args( $data, $defaults ); $inserted = $wpdb->insert( self::table(), $data ); if ( false === $inserted ) { return false; } return $wpdb->insert_id; } public static function get_booking( $id ) { global $wpdb; $table = esc_sql( self::table() ); return $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$table} WHERE id = %d", absint( $id ) ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared } public static function get_bookings( $args = array() ) { global $wpdb; $table = esc_sql( self::table() ); $defaults = array( 'status' => '', 'orderby' => 'created_at', 'order' => 'DESC', 'per_page' => 20, 'paged' => 1, 'search' => '', ); $args = wp_parse_args( $args, $defaults ); $where = array( '1=1' ); $params = array(); if ( ! empty( $args['status'] ) && 'all' !== $args['status'] ) { $where[] = 'status = %s'; $params[] = $args['status']; } if ( ! empty( $args['search'] ) ) { $like = '%' . $wpdb->esc_like( $args['search'] ) . '%'; $where[] = '(full_name LIKE %s OR email LIKE %s OR phone LIKE %s OR pickup_address LIKE %s OR destination_address LIKE %s)'; $params[] = $like; $params[] = $like; $params[] = $like; $params[] = $like; $params[] = $like; } $where_sql = implode( ' AND ', $where ); $orderby = in_array( $args['orderby'], array( 'id', 'created_at', 'booking_date', 'full_name', 'status' ), true ) ? $args['orderby'] : 'created_at'; $order = strtoupper( $args['order'] ) === 'ASC' ? 'ASC' : 'DESC'; $per_page = max( 1, intval( $args['per_page'] ) ); $paged = max( 1, intval( $args['paged'] ) ); $offset = ( $paged - 1 ) * $per_page; $sql = "SELECT * FROM {$table} WHERE {$where_sql} ORDER BY {$orderby} {$order} LIMIT %d OFFSET %d"; $params[] = $per_page; $params[] = $offset; return $wpdb->get_results( $wpdb->prepare( $sql, $params ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared } public static function count_bookings( $args = array() ) { global $wpdb; $table = esc_sql( self::table() ); $defaults = array( 'status' => '', 'search' => '', ); $args = wp_parse_args( $args, $defaults ); $where = array( '1=1' ); $params = array(); if ( ! empty( $args['status'] ) && 'all' !== $args['status'] ) { $where[] = 'status = %s'; $params[] = $args['status']; } if ( ! empty( $args['search'] ) ) { $like = '%' . $wpdb->esc_like( $args['search'] ) . '%'; $where[] = '(full_name LIKE %s OR email LIKE %s OR phone LIKE %s OR pickup_address LIKE %s OR destination_address LIKE %s)'; $params[] = $like; $params[] = $like; $params[] = $like; $params[] = $like; $params[] = $like; } $where_sql = implode( ' AND ', $where ); $sql = "SELECT COUNT(*) FROM {$table} WHERE {$where_sql}"; if ( ! empty( $params ) ) { $sql = $wpdb->prepare( $sql, $params ); } return (int) $wpdb->get_var( $sql ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared } public static function count_by_status( $status ) { global $wpdb; $table = esc_sql( self::table() ); return (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM {$table} WHERE status = %s", sanitize_key( $status ) ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared } public static function update_status( $id, $status ) { global $wpdb; return $wpdb->update( self::table(), array( 'status' => $status ), array( 'id' => intval( $id ) ) ); } public static function delete_booking( $id ) { global $wpdb; return $wpdb->delete( self::table(), array( 'id' => intval( $id ) ) ); } /* --------------------------------------------------------------- * Availability overrides * ------------------------------------------------------------- */ private static $valid_statuses = array( 'wide', 'limited', 'scarce', 'unavailable' ); public static function get_availability_range( $start_date, $end_date ) { global $wpdb; $table = esc_sql( self::table_availability() ); $rows = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching $wpdb->prepare( "SELECT avail_date, status FROM {$table} WHERE avail_date BETWEEN %s AND %s", $start_date, $end_date ) ); $map = array(); foreach ( (array) $rows as $row ) { $map[ $row->avail_date ] = $row->status; } return $map; } public static function set_availability( $date, $status ) { global $wpdb; if ( ! in_array( $status, self::$valid_statuses, true ) ) { return false; } $table = esc_sql( self::table_availability() ); $existing_id = $wpdb->get_var( $wpdb->prepare( "SELECT id FROM {$table} WHERE avail_date = %s", $date ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared if ( $existing_id ) { return $wpdb->update( $table, array( 'status' => $status ), array( 'id' => $existing_id ) ); } return $wpdb->insert( $table, array( 'avail_date' => $date, 'status' => $status ) ); } public static function clear_availability( $date ) { global $wpdb; return $wpdb->delete( self::table_availability(), array( 'avail_date' => $date ) ); } }