get_row( $wpdb->prepare( 'SELECT * FROM %i WHERE id = %d', $table, absint( $id ) ) ); } /** * Find a contact by email address. * * @param string $email Email address. * @return object|null Row object or null. */ public static function find_by_email( $email ) { global $wpdb; $table = Mailpuff_Schema::table( Mailpuff_Schema::SUBSCRIBERS ); return $wpdb->get_row( $wpdb->prepare( 'SELECT * FROM %i WHERE email = %s', $table, sanitize_email( $email ) ) ); } /** * Find subscribed contacts. * * @param int $limit Number of rows to return. * @param int $offset Offset for pagination. * @return array Array of row objects. */ public static function find_subscribed( $limit = 20, $offset = 0 ) { global $wpdb; $table = Mailpuff_Schema::table( Mailpuff_Schema::SUBSCRIBERS ); return $wpdb->get_results( $wpdb->prepare( "SELECT * FROM %i WHERE status = 'subscribed' ORDER BY created_at DESC LIMIT %d OFFSET %d", $table, absint( $limit ), absint( $offset ) ) ); } /** * Count contacts, optionally filtered by status. * * @param string $status Contact status. Empty string for all. * @return int */ public static function count_by_status( $status = '' ) { global $wpdb; $table = Mailpuff_Schema::table( Mailpuff_Schema::SUBSCRIBERS ); if ( '' !== $status ) { return (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i WHERE status = %s', $table, sanitize_text_field( $status ) ) ); } return (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i', $table ) ); } /** * Create a new contact. * * Auto-generates an unsubscribe token. * * @param array $data Column => value pairs. * @return int Insert ID. */ public static function create( $data ) { global $wpdb; $table = Mailpuff_Schema::table( Mailpuff_Schema::SUBSCRIBERS ); if ( empty( $data['unsubscribe_token'] ) ) { $data['unsubscribe_token'] = wp_generate_password( 32, false ); } $data['created_at'] = current_time( 'mysql' ); $data['updated_at'] = current_time( 'mysql' ); if ( isset( $data['email'] ) ) { $data['email_hash'] = Mailpuff_Schema::email_hash( $data['email'] ); } $wpdb->insert( $table, $data ); return (int) $wpdb->insert_id; } /** * Update an existing contact. * * @param int $id Contact ID. * @param array $data Column => value pairs. * @return int|false Number of rows updated or false on error. */ public static function update( $id, $data ) { global $wpdb; $table = Mailpuff_Schema::table( Mailpuff_Schema::SUBSCRIBERS ); $data['updated_at'] = current_time( 'mysql' ); if ( isset( $data['email'] ) ) { $data['email_hash'] = Mailpuff_Schema::email_hash( $data['email'] ); } return $wpdb->update( $table, $data, array( 'id' => absint( $id ) ), null, array( '%d' ) ); } /** * Change a contact's status. The one place a status is written outside a * create, so `mailpuff_contact_status_changed( $id, $from, $to )` fires * once per real change and never for a no-op. * * @param int $id Contact ID. * @param string $status One of Mailpuff_Schema::statuses(). * @param string $source Optional: what made the change (e.g. 'inactive_cleanup'), * passed on to `mailpuff_contact_status_changed`. * @return bool True when the status changed. */ public static function set_status( $id, $status, $source = '' ) { global $wpdb; $id = absint( $id ); if ( ! $id || ! in_array( $status, Mailpuff_Schema::statuses(), true ) ) { return false; } $contact = self::find( $id ); if ( ! $contact || $contact->status === $status ) { return false; } $wpdb->update( Mailpuff_Schema::table( Mailpuff_Schema::SUBSCRIBERS ), array( 'status' => $status, 'updated_at' => current_time( 'mysql' ), ), array( 'id' => $id ), array( '%s', '%s' ), array( '%d' ) ); self::status_changed( $id, (string) $contact->status, $status, $source ); return true; } /** * Announce a status change. Call this when a status was written some other * way (a bulk update, a form field) and the caller knows the old value. * * @param int $id Contact ID. * @param string $from Old status. * @param string $to New status. * @param string $source Optional: what made the change ('' when not said). */ public static function status_changed( $id, $from, $to, $source = '' ) { if ( $from !== $to ) { /** * A contact's status changed. * * @param int $id Contact ID. * @param string $from Old status. * @param string $to New status. * @param string $source What made the change, such as 'inactive_cleanup'; '' when not said. */ do_action( 'mailpuff_contact_status_changed', (int) $id, (string) $from, (string) $to, sanitize_key( (string) $source ) ); } } /** * Delete a contact by ID. * * Removes everything tied to the contact; see {@see self::purge()}. * * @param int $id Contact ID. * @return int|false Number of rows deleted or false on error. */ public static function delete( $id ) { return self::purge( $id ) ? 1 : 0; } /** * Remove a contact and everything that hangs off it: group links, custom * field values, tracking rows and send-queue rows. The one place that does * this, so the privacy eraser, delete-on-user-delete and the REST delete * agree. It does not suppress the address; callers that must do that. * * @param int $id Contact ID. * @return bool True when a contact row was removed. */ public static function purge( $id ) { global $wpdb; $id = absint( $id ); if ( ! $id ) { return false; } $wpdb->delete( Mailpuff_Schema::table( Mailpuff_Schema::SUBSCRIBER_LISTS ), array( 'contact_id' => $id ), array( '%d' ) ); Mailpuff_Schema::delete_meta( $id ); $wpdb->delete( Mailpuff_Schema::table( Mailpuff_Schema::TRACKING ), array( 'contact_id' => $id ), array( '%d' ) ); $wpdb->delete( Mailpuff_Schema::table( Mailpuff_Schema::LINK_CLICKS ), array( 'contact_id' => $id ), array( '%d' ) ); $wpdb->delete( Mailpuff_Schema::table( Mailpuff_Schema::SEND_QUEUE ), array( 'contact_id' => $id ), array( '%d' ) ); return (bool) $wpdb->delete( Mailpuff_Schema::table( Mailpuff_Schema::SUBSCRIBERS ), array( 'id' => $id ), array( '%d' ) ); } /** * Find a contact by unsubscribe token. * * @param string $token Unsubscribe token. * @return object|null Row object or null. */ public static function find_by_token( $token ) { global $wpdb; $table = Mailpuff_Schema::table( Mailpuff_Schema::SUBSCRIBERS ); return $wpdb->get_row( $wpdb->prepare( 'SELECT * FROM %i WHERE unsubscribe_token = %s', $table, sanitize_text_field( $token ) ) ); } }