> */ public function between( array $bookable_ids, int $from, int $to, bool $with_cancelled = false ): array { $where = array( 'starts_at >= %s', 'starts_at < %s' ); $args = array( self::dt( $from ), self::dt( $to ) ); if ( array() !== $bookable_ids ) { $bookable_ids = array_map( 'intval', $bookable_ids ); $where[] = 'bookable_id IN (' . self::in( $bookable_ids ) . ')'; $args = array_merge( $args, $bookable_ids ); } if ( ! $with_cancelled ) { $where[] = "status = 'scheduled'"; } // 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->table} WHERE " . implode( ' AND ', $where ) . ' ORDER BY starts_at, id LIMIT 2000', $args ), 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; } /** * Sessions a staff member teaches in a window, as busy time. * * @param int[] $staff_ids Staff. * @param int $from Start. * @param int $to End. * @return array>> */ public function busy_staff( array $staff_ids, int $from, int $to ): array { $staff_ids = array_values( array_filter( array_map( 'intval', $staff_ids ) ) ); if ( array() === $staff_ids ) { return array(); } $args = array_merge( $staff_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->table} WHERE staff_id IN (" . self::in( $staff_ids ) . ") AND status = 'scheduled' AND starts_at < %s AND ends_at > %s", $args ), ARRAY_A ); $out = array_fill_keys( $staff_ids, array() ); foreach ( $rows as $row ) { $session = self::hydrate( $row ); $out[ $session['staff_id'] ][] = array( 'start' => $session['start'], 'end' => $session['end'], 'qty' => 1, 'kind' => 'session', 'bookable_id' => $session['bookable_id'], 'slot_start' => -1, ); } return $out; } /** * Insert a session. * * @param array $session bookable_id, start, end, capacity, staff_id, location_id, tickets, series, note. */ public function create( array $session ): int { $this->db->insert( $this->table, $this->row( $session ) ); return (int) $this->db->insert_id; } /** * Change a session. * * @param int $id Session. * @param array $session Fields to change. */ public function update( int $id, array $session ): void { $row = $this->row( $session ); if ( array() !== $row ) { $this->db->update( $this->table, $row, array( 'id' => $id ) ); } } /** * Delete future sessions of a series that nobody has booked, before regenerating them. * * @param int $bookable_id Bookable. * @param string $series Series key. * @param int $from Only sessions starting after this. * @param int[] $keep Session IDs to keep (booked ones). */ public function delete_unbooked( int $bookable_id, string $series, int $from, array $keep ): void { $keep = array() !== $keep ? array_map( 'intval', $keep ) : array( 0 ); $args = array_merge( array( $bookable_id, $series, self::dt( $from ) ), $keep ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.DirectDatabaseQuery $this->db->query( $this->db->prepare( "DELETE FROM {$this->table} WHERE bookable_id = %d AND series = %s AND starts_at > %s AND id NOT IN (" . self::in( $keep ) . ')', $args ) ); } /** * Map fields to columns. * * @param array $s Session fields. * @return array */ private function row( array $s ): array { $row = array(); if ( isset( $s['bookable_id'] ) ) { $row['bookable_id'] = (int) $s['bookable_id']; } if ( isset( $s['start'] ) ) { $row['starts_at'] = self::dt( (int) $s['start'] ); } if ( isset( $s['end'] ) ) { $row['ends_at'] = self::dt( (int) $s['end'] ); } foreach ( array( 'capacity', 'staff_id', 'location_id' ) as $int ) { if ( isset( $s[ $int ] ) ) { $row[ $int ] = max( 0, (int) $s[ $int ] ); } } foreach ( array( 'status', 'series', 'note' ) as $text ) { if ( isset( $s[ $text ] ) ) { $row[ $text ] = (string) $s[ $text ]; } } if ( array_key_exists( 'tickets', $s ) ) { $row['tickets'] = wp_json_encode( $s['tickets'] ?? array() ); } return $row; } /** * Cast a row. * * @param array $row Row. * @return array */ private static function hydrate( array $row ): array { return array( 'id' => (int) $row['id'], 'bookable_id' => (int) $row['bookable_id'], 'start' => self::ts( $row['starts_at'] ), 'end' => self::ts( $row['ends_at'] ), 'capacity' => (int) $row['capacity'], 'staff_id' => (int) $row['staff_id'], 'location_id' => (int) $row['location_id'], 'status' => (string) $row['status'], 'tickets' => (array) self::json( $row['tickets'] ), 'series' => (string) $row['series'], 'note' => (string) $row['note'], ); } }