row( "SELECT * FROM {$table} WHERE id = %d" . ( $for_update ? ' FOR UPDATE' : '' ), array( $id ) ); } /** * A page of sessions, newest date first. * * @since 0.1.0 * * @param array $args page, per_page, search, status. * @return array{items: object[], total: int} */ public function query( array $args ): array { list( , $per_page, $offset ) = $this->paging( $args ); $table = Tables::name( 'sessions' ); $where = array( '1=1' ); $params = array(); if ( ! empty( $args['search'] ) ) { $where[] = 'title LIKE %s'; $params[] = '%' . $this->wpdb->esc_like( (string) $args['search'] ) . '%'; } if ( ! empty( $args['status'] ) ) { $where[] = 'status = %s'; $params[] = (string) $args['status']; } $clause = implode( ' AND ', $where ); $total = (int) $this->value( "SELECT COUNT(*) FROM {$table} WHERE {$clause}", $params ); $items = $this->rows( "SELECT * FROM {$table} WHERE {$clause} ORDER BY session_date DESC, start_time ASC, id DESC LIMIT %d OFFSET %d", array_merge( $params, array( $per_page, $offset ) ) ); return array( 'items' => $items, 'total' => $total, ); } /** * Sessions for a picker: recent first, capped. * * @since 0.1.0 * * @param int $limit Maximum rows. * @return object[] */ public function recent( int $limit = 300 ): array { $table = Tables::name( 'sessions' ); return $this->rows( "SELECT * FROM {$table} ORDER BY session_date DESC, start_time ASC, id DESC LIMIT %d", array( max( 1, min( 500, $limit ) ) ) ); } /** * Whether the sessions table exists under the current table prefix. * * @since 0.1.0 * * @return bool */ public function table_exists(): bool { $table = Tables::name( 'sessions' ); return $table === $this->value( 'SHOW TABLES LIKE %s', array( $this->wpdb->esc_like( $table ) ) ); } /** * Every session id (settings changes reschedule reminders for all of them). * * @since 0.1.0 * * @return int[] */ public function all_ids(): array { $table = Tables::name( 'sessions' ); return array_map( 'intval', $this->column( "SELECT id FROM {$table} ORDER BY id ASC" ) ); } /** * The session the public page should show when none is named: a live one, otherwise the next one that has not ended. * * @since 0.1.0 * * @param string $today Site-local date, Y-m-d. * @param string $now_time Site-local time, H:i:s. * @return object|null */ public function current_or_next( string $today, string $now_time ): ?object { $table = Tables::name( 'sessions' ); $live = $this->row( "SELECT * FROM {$table} WHERE status IN ('active','paused') ORDER BY session_date ASC, start_time ASC LIMIT 1" ); if ( $live ) { return $live; } return $this->row( "SELECT * FROM {$table} WHERE status = 'scheduled' AND ( session_date > %s OR ( session_date = %s AND end_time >= %s ) ) ORDER BY session_date ASC, start_time ASC LIMIT 1", array( $today, $today, $now_time ) ); } /** * Number of unfinished sessions whose window overlaps the given one on the same date. * * @since 0.1.0 * * @param string $date Y-m-d. * @param string $start H:i:s. * @param string $end H:i:s. * @param int $exclude_id Session being edited. * @return int */ public function count_overlapping( string $date, string $start, string $end, int $exclude_id ): int { $table = Tables::name( 'sessions' ); return (int) $this->value( "SELECT COUNT(*) FROM {$table} WHERE session_date = %s AND start_time < %s AND end_time > %s AND id <> %d AND status <> %s", array( $date, $end, $start, $exclude_id, 'completed' ) ); } /** * Inserts a session. * * @since 0.1.0 * * @param array $data Columns. * @return int New id, 0 on failure. */ public function insert( array $data ): int { $data['created_at'] = gmdate( 'Y-m-d H:i:s' ); $data['updated_at'] = $data['created_at']; return $this->insert_row( Tables::name( 'sessions' ), $data ); } /** * Updates a session. * * @since 0.1.0 * * @param int $id Session id. * @param array $data Columns. * @return bool */ public function update( int $id, array $data ): bool { $data['updated_at'] = gmdate( 'Y-m-d H:i:s' ); return $this->update_row( Tables::name( 'sessions' ), $id, $data ); } /** * Deletes a session with its candidates and logs, all or nothing. * * @since 0.1.0 * * @param int $id Session id. * @return bool */ public function delete( int $id ): bool { $this->wpdb->query( 'START TRANSACTION' ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching foreach ( array( 'candidates', 'events', 'emails' ) as $key ) { if ( ! $this->delete_where( Tables::name( $key ), array( 'session_id' => $id ) ) ) { $this->wpdb->query( 'ROLLBACK' ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching return false; } } if ( ! $this->delete_where( Tables::name( 'sessions' ), array( 'id' => $id ) ) ) { $this->wpdb->query( 'ROLLBACK' ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching return false; } return false !== $this->wpdb->query( 'COMMIT' ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching } }