db->get_row( $this->db->prepare( "SELECT * FROM {$this->table} WHERE id = %d", $id ), ARRAY_A ); return $row ? self::hydrate( $row ) : null; } public function find_by_email( string $email ): ?array { $email = strtolower( trim( $email ) ); if ( '' === $email ) { return null; } // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery $row = $this->db->get_row( $this->db->prepare( "SELECT * FROM {$this->table} WHERE email = %s ORDER BY id LIMIT 1", $email ), ARRAY_A ); return $row ? self::hydrate( $row ) : null; } public function find_by_user( int $user_id ): ?array { if ( $user_id <= 0 ) { return null; } // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery $row = $this->db->get_row( $this->db->prepare( "SELECT * FROM {$this->table} WHERE user_id = %d ORDER BY id LIMIT 1", $user_id ), ARRAY_A ); return $row ? self::hydrate( $row ) : null; } /** * Customers by ID. * * @param int[] $ids IDs. * @return array> Keyed by ID. */ public function many( array $ids ): array { $ids = array_values( array_unique( array_filter( array_map( 'intval', $ids ) ) ) ); if ( array() === $ids ) { return array(); } // 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 id IN (" . self::in( $ids ) . ')', $ids ), ARRAY_A ); $out = array(); foreach ( $rows as $row ) { $out[ (int) $row['id'] ] = self::hydrate( $row ); } return $out; } /** * Find the customer with this email, or make one; fill in details they did not have. * * @param array $data email, first_name, last_name, phone, timezone, locale, user_id, consent. * @return int Customer ID. */ public function upsert( array $data ): int { $email = strtolower( trim( (string) ( $data['email'] ?? '' ) ) ); $existing = '' === $email ? null : $this->find_by_email( $email ); if ( null === $existing && ! empty( $data['user_id'] ) ) { $existing = $this->find_by_user( (int) $data['user_id'] ); } if ( null !== $existing ) { $changes = array(); foreach ( array( 'first_name', 'last_name', 'phone', 'timezone', 'locale' ) as $field ) { if ( isset( $data[ $field ] ) && '' !== (string) $data[ $field ] ) { $changes[ $field ] = (string) $data[ $field ]; } } if ( ! empty( $data['user_id'] ) && 0 === $existing['user_id'] ) { $changes['user_id'] = (int) $data['user_id']; } if ( isset( $data['consent'] ) ) { $changes['consent'] = array_merge( $existing['consent'], (array) $data['consent'] ); } $this->update( $existing['id'], $changes ); return $existing['id']; } $row = array_intersect_key( $data, array_flip( self::FIELDS ) ); $row['email'] = $email; $row['consent'] = wp_json_encode( (object) ( $data['consent'] ?? array() ) ); $row['created_at'] = self::now(); $row['updated_at'] = self::now(); $this->db->insert( $this->table, $row ); return (int) $this->db->insert_id; } /** * Change fields. * * @param int $id Customer. * @param array $data Fields; tags as a list; consent as an object. */ public function update( int $id, array $data ): void { $row = array_intersect_key( $data, array_flip( self::FIELDS ) ); if ( isset( $row['tags'] ) && is_array( $row['tags'] ) ) { $row['tags'] = implode( ',', self::clean_tags( $row['tags'] ) ); } if ( isset( $row['email'] ) ) { $row['email'] = strtolower( trim( (string) $row['email'] ) ); } if ( array_key_exists( 'consent', $data ) ) { $row['consent'] = wp_json_encode( (object) $data['consent'] ); } if ( array() === $row ) { return; } $row['updated_at'] = self::now(); $this->db->update( $this->table, $row, array( 'id' => $id ) ); } /** * Search customers. * * @param string $search Name, email or phone. * @param string $tag Only with this tag. * @param int $page Page. * @param int $per_page Per page. * @return array{items: array>, total: int} */ public function search( string $search, string $tag, int $page, int $per_page ): array { $where = array( "email NOT LIKE 'erased-%'" ); $args = array(); if ( '' !== $search ) { $like = '%' . $this->db->esc_like( $search ) . '%'; $where[] = '(email LIKE %s OR first_name LIKE %s OR last_name LIKE %s OR phone LIKE %s OR CONCAT(first_name, " ", last_name) LIKE %s)'; array_push( $args, $like, $like, $like, $like, $like ); } if ( '' !== $tag ) { $where[] = 'FIND_IN_SET(%s, tags)'; $args[] = $tag; } $sql = implode( ' AND ', $where ); $per_page = max( 1, min( 500, $per_page ) ); // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.NotPrepared $count = "SELECT COUNT(*) FROM {$this->table} WHERE {$sql}"; $total = (int) $this->db->get_var( $args ? $this->db->prepare( $count, $args ) : $count ); $rows = (array) $this->db->get_results( $this->db->prepare( "SELECT * FROM {$this->table} WHERE {$sql} ORDER BY updated_at DESC, id DESC LIMIT %d OFFSET %d", array_merge( $args, array( $per_page, max( 0, $page - 1 ) * $per_page ) ) ), ARRAY_A ); // phpcs:enable return array( 'items' => array_map( array( self::class, 'hydrate' ), $rows ), 'total' => $total, ); } /** * Remove personal details, keeping the row so bookings still add up. * * @param int $id Customer. */ public function anonymize( int $id ): void { $this->db->update( $this->table, array( 'user_id' => 0, 'email' => 'erased-' . $id . '@invalid', 'first_name' => '', 'last_name' => '', 'phone' => '', 'tags' => '', 'notes' => '', 'consent' => '{}', 'updated_at' => self::now(), ), array( 'id' => $id ) ); } public function delete( int $id ): void { $this->db->delete( $this->table, array( 'id' => $id ) ); } /** * Customers whose last booking ended before a time and who have nothing upcoming. * * @param int $before Unix time. * @param int $limit Most to return. * @return int[] */ public function inactive_since( int $before, int $limit = 200 ): array { $bookings = $this->db->prefix . 'schedulereed_bookings'; // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery return array_map( 'intval', (array) $this->db->get_col( $this->db->prepare( "SELECT c.id FROM {$this->table} c LEFT JOIN {$bookings} b ON b.customer_id = c.id WHERE c.email NOT LIKE 'erased-%%' AND c.updated_at < %s GROUP BY c.id HAVING COALESCE(MAX(b.ends_at), '1000-01-01') < %s LIMIT %d", self::dt( $before ), self::dt( $before ), $limit ) ) ); } /** * Tags in use, for filters. * * @return string[] */ public function tags(): array { // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery $lists = (array) $this->db->get_col( "SELECT DISTINCT tags FROM {$this->table} WHERE tags <> '' LIMIT 1000" ); $tags = array(); foreach ( $lists as $list ) { foreach ( explode( ',', (string) $list ) as $tag ) { $tags[ $tag ] = true; } } $tags = array_keys( $tags ); sort( $tags ); return $tags; } /** * Tidy a list of tags. * * @param array $tags Tags. * @return string[] */ public static function clean_tags( array $tags ): array { $out = array(); foreach ( $tags as $tag ) { $tag = trim( str_replace( ',', ' ', sanitize_text_field( (string) $tag ) ) ); if ( '' !== $tag ) { $out[ mb_strtolower( $tag ) ] = mb_substr( $tag, 0, 40 ); } } return array_slice( array_values( $out ), 0, 20 ); } /** * Cast a row. * * @param array $row Row. * @return array */ private static function hydrate( array $row ): array { return array( 'id' => (int) $row['id'], 'user_id' => (int) $row['user_id'], 'email' => (string) $row['email'], 'first_name' => (string) $row['first_name'], 'last_name' => (string) $row['last_name'], 'name' => trim( $row['first_name'] . ' ' . $row['last_name'] ), 'phone' => (string) $row['phone'], 'timezone' => (string) $row['timezone'], 'locale' => (string) $row['locale'], 'tags' => '' === $row['tags'] ? array() : explode( ',', (string) $row['tags'] ), 'notes' => (string) $row['notes'], 'consent' => (array) self::json( $row['consent'] ), 'created_at' => self::ts( $row['created_at'] ), ); } }