events = $db->prefix . 'schedulereed_external_events'; } /** * Links, optionally of one owner or provider. * * @param string $owner_type Owner type or ''. * @param int $owner_id Owner or 0. * @param string $provider ical, google, outlook or ''. * @return array> */ public function links( string $owner_type = '', int $owner_id = 0, string $provider = '' ): array { $where = array( '1=1' ); $args = array(); if ( '' !== $owner_type ) { $where[] = 'owner_type = %s AND owner_id = %d'; array_push( $args, $owner_type, $owner_id ); } if ( '' !== $provider ) { $where[] = 'provider = %s'; $args[] = $provider; } $sql = "SELECT * FROM {$this->table} WHERE " . implode( ' AND ', $where ) . ' ORDER BY id'; // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.NotPrepared $rows = (array) $this->db->get_results( $args ? $this->db->prepare( $sql, $args ) : $sql, ARRAY_A ); return array_map( array( self::class, 'hydrate' ), $rows ); } public function find( int $id ): ?array { // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery $row = $this->db->get_row( $this->db->prepare( "SELECT * FROM {$this->table} WHERE id = %d", $id ), ARRAY_A ); return $row ? self::hydrate( $row ) : null; } /** * Add a link. * * @param array $link owner_type, owner_id, provider, name, url, calendar_id, credentials, push. */ public function create( array $link ): int { $this->db->insert( $this->table, array( 'owner_type' => (string) $link['owner_type'], 'owner_id' => (int) $link['owner_id'], 'provider' => (string) $link['provider'], 'name' => (string) ( $link['name'] ?? '' ), 'url' => (string) ( $link['url'] ?? '' ), 'calendar_id' => (string) ( $link['calendar_id'] ?? '' ), 'credentials' => wp_json_encode( (object) ( $link['credentials'] ?? array() ) ), 'push' => empty( $link['push'] ) ? 0 : 1, 'created_at' => self::now(), ) ); return (int) $this->db->insert_id; } /** * Change a link. * * @param int $id Link. * @param array $data Fields. */ public function update( int $id, array $data ): void { $row = array_intersect_key( $data, array_flip( array( 'name', 'url', 'calendar_id', 'sync_token', 'last_error' ) ) ); if ( array_key_exists( 'credentials', $data ) ) { $row['credentials'] = wp_json_encode( (object) $data['credentials'] ); } if ( array_key_exists( 'push', $data ) ) { $row['push'] = $data['push'] ? 1 : 0; } if ( array_key_exists( 'last_sync', $data ) ) { $row['last_sync'] = $data['last_sync'] ? self::dt( (int) $data['last_sync'] ) : null; } if ( array() !== $row ) { $this->db->update( $this->table, $row, array( 'id' => $id ) ); } } public function delete( int $id ): void { $this->db->delete( $this->table, array( 'id' => $id ) ); $this->db->delete( $this->events, array( 'link_id' => $id ) ); } /** * Replace a link's imported events. * * @param array $link Link. * @param array> $events Events {uid, start, end, summary}. * @param int $from Only replace events from this time (earlier ones are kept as history). */ public function replace_events( array $link, array $events, int $from ): int { // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery $this->db->query( $this->db->prepare( "DELETE FROM {$this->events} WHERE link_id = %d AND end_at >= %s", $link['id'], self::dt( $from ) ) ); $count = 0; foreach ( $events as $event ) { if ( (int) $event['end'] < $from ) { continue; } // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery $this->db->query( $this->db->prepare( "INSERT INTO {$this->events} (link_id, owner_type, owner_id, uid, start_at, end_at, summary, booking_id) VALUES (%d, %s, %d, %s, %s, %s, %s, %d) ON DUPLICATE KEY UPDATE start_at = VALUES(start_at), end_at = VALUES(end_at), summary = VALUES(summary)", $link['id'], $link['owner_type'], $link['owner_id'], mb_substr( (string) $event['uid'], 0, 191 ), self::dt( (int) $event['start'] ), self::dt( (int) $event['end'] ), mb_substr( (string) ( $event['summary'] ?? '' ), 0, 191 ), (int) ( $event['booking_id'] ?? 0 ) ) ); ++$count; } return $count; } /** * UIDs of a link's events that end after a time. * * @param int $link_id Link. * @param int $from Time. * @return string[] */ public function uids( int $link_id, int $from ): array { // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery return array_map( 'strval', (array) $this->db->get_col( $this->db->prepare( "SELECT uid FROM {$this->events} WHERE link_id = %d AND end_at >= %s", $link_id, self::dt( $from ) ) ) ); } /** * Remove one imported event, e.g. one deleted in Google. * * @param int $link_id Link. * @param string $uid Event UID. */ public function delete_event( int $link_id, string $uid ): void { $this->db->delete( $this->events, array( 'link_id' => $link_id, 'uid' => $uid, ) ); } /** * Busy time from imported calendars. * * @param string $owner_type staff, resource or bookable. * @param int[] $owner_ids Owners. * @param int $from Start. * @param int $to End. * @return array>> */ public function busy( string $owner_type, array $owner_ids, int $from, int $to ): array { $owner_ids = array_values( array_filter( array_map( 'intval', $owner_ids ) ) ); if ( array() === $owner_ids ) { return array(); } $args = array_merge( array( $owner_type ), $owner_ids, array( self::dt( $to ), self::dt( $from ) ) ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.DirectDatabaseQuery $rows = (array) $this->db->get_results( $this->db->prepare( "SELECT * FROM {$this->events} WHERE owner_type = %s AND owner_id IN (" . self::in( $owner_ids ) . ') AND start_at < %s AND end_at > %s AND booking_id = 0', $args ), ARRAY_A ); $out = array_fill_keys( $owner_ids, array() ); foreach ( $rows as $row ) { $out[ (int) $row['owner_id'] ][] = array( 'start' => self::ts( $row['start_at'] ), 'end' => self::ts( $row['end_at'] ), 'qty' => 1000000, 'kind' => 'external', 'label' => (string) $row['summary'], ); } return $out; } /** * Imported events for the admin calendar. * * @param int $from Start. * @param int $to End. * @return array> */ public function events_between( int $from, int $to ): array { // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery $rows = (array) $this->db->get_results( $this->db->prepare( "SELECT * FROM {$this->events} WHERE start_at < %s AND end_at > %s AND booking_id = 0 ORDER BY start_at LIMIT 2000", self::dt( $to ), self::dt( $from ) ), ARRAY_A ); return array_map( static fn( array $row ): array => array( 'id' => (int) $row['id'], 'link_id' => (int) $row['link_id'], 'owner_type' => (string) $row['owner_type'], 'owner_id' => (int) $row['owner_id'], 'start' => self::ts( $row['start_at'] ), 'end' => self::ts( $row['end_at'] ), 'summary' => (string) $row['summary'], ), $rows ); } /** * Cast a link row; credentials stay server side. * * @param array $row Row. * @return array */ private static function hydrate( array $row ): array { return array( 'id' => (int) $row['id'], 'owner_type' => (string) $row['owner_type'], 'owner_id' => (int) $row['owner_id'], 'provider' => (string) $row['provider'], 'name' => (string) $row['name'], 'url' => (string) $row['url'], 'calendar_id' => (string) $row['calendar_id'], 'credentials' => (array) self::json( $row['credentials'] ), 'sync_token' => (string) $row['sync_token'], 'push' => (bool) $row['push'], 'last_sync' => self::ts( $row['last_sync'] ), 'last_error' => (string) $row['last_error'], ); } }