get_row( $wpdb->prepare( "SELECT * FROM %i WHERE id = %d", $t, $id ) ); // phpcs:ignore WordPress.DB } public static function by_phone( $phone ) { global $wpdb; $t = AFPP_DB::subscribers(); return $wpdb->get_row( $wpdb->prepare( "SELECT * FROM %i WHERE phone = %s", $t, $phone ) ); // phpcs:ignore WordPress.DB } public static function by_token( $token ) { global $wpdb; $t = AFPP_DB::subscribers(); return $wpdb->get_row( $wpdb->prepare( "SELECT * FROM %i WHERE token = %s", $t, $token ) ); // phpcs:ignore WordPress.DB } /** * Add a number, or bring back one that had unsubscribed. * * @return int Subscriber id. */ public static function upsert( $phone, array $data ) { global $wpdb; $t = AFPP_DB::subscribers(); $now = current_time( 'mysql', true ); $row = self::by_phone( $phone ); $data = wp_parse_args( $data, array( 'name' => '', 'status' => 'active', 'source' => '', 'ip' => '', ) ); if ( $row ) { $update = array( 'status' => $data['status'], 'updated_at' => $now, 'consent_at' => $now, ); if ( '' !== $data['name'] ) { $update['name'] = $data['name']; } $wpdb->update( $t, $update, array( 'id' => $row->id ) ); // phpcs:ignore WordPress.DB return (int) $row->id; } $wpdb->insert( // phpcs:ignore WordPress.DB $t, array( 'phone' => $phone, 'name' => $data['name'], 'status' => $data['status'], 'token' => md5( wp_generate_password( 32, true, true ) . $phone ), 'source' => substr( $data['source'], 0, 255 ), 'ip' => $data['ip'], 'consent_at' => $now, 'created_at' => $now, 'updated_at' => $now, ) ); return (int) $wpdb->insert_id; } public static function set_status( $id, $status ) { global $wpdb; $wpdb->update( // phpcs:ignore WordPress.DB AFPP_DB::subscribers(), array( 'status' => $status, 'updated_at' => current_time( 'mysql', true ), ), array( 'id' => (int) $id ) ); do_action( 'afpp_subscriber_status', (int) $id, $status ); } public static function delete( $id ) { global $wpdb; $wpdb->delete( AFPP_DB::subscribers(), array( 'id' => (int) $id ) ); // phpcs:ignore WordPress.DB $wpdb->delete( AFPP_DB::queue(), array( 'subscriber_id' => (int) $id ) ); // phpcs:ignore WordPress.DB do_action( 'afpp_subscriber_deleted', (int) $id ); } public static function count( $status = 'active' ) { global $wpdb; $t = AFPP_DB::subscribers(); if ( 'all' === $status ) { return (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i', $t ) ); // phpcs:ignore WordPress.DB } return (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM %i WHERE status = %s", $t, $status ) ); // phpcs:ignore WordPress.DB } /** * @return array{rows: array, total: int} */ public static function query( $args ) { global $wpdb; $t = AFPP_DB::subscribers(); $args = wp_parse_args( $args, array( 'status' => '', 'search' => '', 'page' => 1, 'per_page' => 25, ) ); $where = array( '1=1' ); $vals = array(); if ( $args['status'] ) { $where[] = 'status = %s'; $vals[] = $args['status']; } if ( $args['search'] ) { $like = '%' . $wpdb->esc_like( $args['search'] ) . '%'; $where[] = '(phone LIKE %s OR name LIKE %s)'; $vals[] = $like; $vals[] = $like; } $where_sql = implode( ' AND ', $where ); $offset = ( max( 1, (int) $args['page'] ) - 1 ) * (int) $args['per_page']; // $where_sql only contains fixed placeholders (%s); every value is passed to prepare(). // phpcs:disable WordPress.DB, PluginCheck.Security.DirectDB.UnescapedDBParameter $total = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM %i WHERE {$where_sql}", array_merge( array( $t ), $vals ) ) ); $rows = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM %i WHERE {$where_sql} ORDER BY id DESC LIMIT %d OFFSET %d", array_merge( array( $t ), $vals, array( (int) $args['per_page'], $offset ) ) ) ); // phpcs:enable return array( 'rows' => $rows, 'total' => $total, ); } public static function unsubscribe_url( $subscriber ) { return add_query_arg( 'afpp_unsub', $subscriber->token, home_url( '/' ) ); } }