| @@ -32,8 +32,14 @@ | ||
| 32 | 32 | class AdminRestController { |
| 33 | 33 | |
| 34 | 34 | const API_NAMESPACE = 'f12-doi/v1'; |
| 35 | 35 | |
| 36 | + /** | |
| 37 | + * SQL form of OptIn::isOptedOut(): not confirmed, withdrawal IP and time | |
| 38 | + * recorded. A re-opt-in clears the time, so the row counts as confirmed again. | |
| 39 | + */ | |
| 40 | + private const REVOKED_SQL = "(doubleoptin = 0 AND ipaddr_optout IS NOT NULL AND ipaddr_optout <> '' AND optouttime IS NOT NULL AND optouttime NOT IN ('', '0'))"; | |
| 41 | + | |
| 36 | 42 | private LoggerInterface $logger; |
| 37 | 43 | private FormSettingsService $formService; |
| 38 | 44 | private FormSettingsValidator $formValidator; |
| 39 | 45 | |
| @@ -617,9 +623,10 @@ | ||
| 617 | 623 | $table = $wpdb->prefix . 'f12_cf7_doubleoptin'; |
| 618 | 624 | |
| 619 | 625 | $total = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$table}" ); |
| 620 | 626 | $confirmed = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$table} WHERE doubleoptin = 1" ); |
| 621 | - $pending = $total - $confirmed; | |
| 627 | + $revoked = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$table} WHERE " . self::REVOKED_SQL ); // phpcs:ignore WordPress.DB.PreparedSQL -- fixed SQL, no input. | |
| 628 | + $pending = max( 0, $total - $confirmed - $revoked ); | |
| 622 | 629 | $rate = $total > 0 ? round( ( $confirmed / $total ) * 100, 1 ) : 0; |
| 623 | 630 | |
| 624 | 631 | // Recent opt-ins (raw activity feed — not analytics). |
| 625 | 632 | // Time-bucketed activity, top-forms breakdown and the big |
| @@ -625,9 +632,9 @@ | ||
| 625 | 632 | // Time-bucketed activity, top-forms breakdown and the big |
| 626 | 633 | // conversion-rate card moved into addon-analytics, which |
| 627 | 634 | // renders them at the `dashboard.widget` mount point. |
| 628 | 635 | $recent = $wpdb->get_results( |
| 629 | - "SELECT id, email, cf_form_id, doubleoptin, createtime FROM {$table} ORDER BY id DESC LIMIT 5", | |
| 636 | + "SELECT id, email, cf_form_id, doubleoptin, createtime, ipaddr_optout, optouttime FROM {$table} ORDER BY id DESC LIMIT 5", | |
| 630 | 637 | ARRAY_A |
| 631 | 638 | ); |
| 632 | 639 | |
| 633 | 640 | foreach ( $recent as &$row ) { |
| @@ -633,14 +640,18 @@ | ||
| 633 | 640 | foreach ( $recent as &$row ) { |
| 634 | 641 | $post = get_post( (int) $row['cf_form_id'] ); |
| 635 | 642 | $row['formName'] = $post ? $post->post_title : sprintf( '#%d', $row['cf_form_id'] ); |
| 636 | 643 | $row['confirmed'] = (int) $row['doubleoptin'] === 1; |
| 644 | + $row['revoked'] = self::isRevokedRow( $row ); | |
| 645 | + unset( $row['ipaddr_optout'], $row['optouttime'] ); | |
| 637 | 646 | } |
| 647 | + unset( $row ); | |
| 638 | 648 | |
| 639 | 649 | $data = array( |
| 640 | 650 | 'totalOptins' => $total, |
| 641 | 651 | 'confirmed' => $confirmed, |
| 642 | 652 | 'pending' => $pending, |
| 653 | + 'revoked' => $revoked, | |
| 643 | 654 | 'conversionRate' => $rate, |
| 644 | 655 | 'recentOptins' => $recent ?: array(), |
| 645 | 656 | ); |
| 646 | 657 | |
| @@ -721,8 +732,11 @@ | ||
| 721 | 732 | if ( $status === 'confirmed' ) { |
| 722 | 733 | $where[] = 'doubleoptin = 1'; |
| 723 | 734 | } elseif ( $status === 'pending' ) { |
| 724 | 735 | $where[] = '(doubleoptin = 0 OR doubleoptin IS NULL)'; |
| 736 | + $where[] = 'NOT ' . self::REVOKED_SQL; | |
| 737 | + } elseif ( $status === 'revoked' ) { | |
| 738 | + $where[] = self::REVOKED_SQL; | |
| 725 | 739 | } |
| 726 | 740 | |
| 727 | 741 | if ( $formId !== null && $formId !== '' ) { |
| 728 | 742 | $where[] = 'cf_form_id = %d'; |
| @@ -2170,8 +2184,21 @@ | ||
| 2170 | 2184 | // HELPERS |
| 2171 | 2185 | // ═══════════════════════════════════════════════════════════════ |
| 2172 | 2186 | |
| 2173 | 2187 | /** |
| 2188 | + * PHP form of REVOKED_SQL for a raw table row. | |
| 2189 | + * | |
| 2190 | + * @param array<string, mixed> $row The database row. | |
| 2191 | + */ | |
| 2192 | + private static function isRevokedRow( array $row ): bool { | |
| 2193 | + $optOutTime = (string) ( $row['optouttime'] ?? '' ); | |
| 2194 | + | |
| 2195 | + return (int) ( $row['doubleoptin'] ?? 0 ) !== 1 | |
| 2196 | + && (string) ( $row['ipaddr_optout'] ?? '' ) !== '' | |
| 2197 | + && $optOutTime !== '' && $optOutTime !== '0'; | |
| 2198 | + } | |
| 2199 | + | |
| 2200 | + /** | |
| 2174 | 2201 | * Format an opt-in database row for the API response. |
| 2175 | 2202 | * |
| 2176 | 2203 | * @param array $row The database row. |
| 2177 | 2204 | * @param bool $detailed Whether to include full detail (content, mail data). |
| @@ -2188,8 +2215,10 @@ | ||
| 2188 | 2215 | 'formId' => (int) $row['cf_form_id'], |
| 2189 | 2216 | 'formName' => $post ? $post->post_title : sprintf( '#%d', $row['cf_form_id'] ), |
| 2190 | 2217 | 'category' => (int) $row['category'], |
| 2191 | 2218 | 'confirmed' => (int) $row['doubleoptin'] === 1, |
| 2219 | + // Consent withdrawn via the opt-out (5.9.0). Not "pending". | |
| 2220 | + 'revoked' => self::isRevokedRow( $row ), | |
| 2192 | 2221 | // Confirmation mail: 'sent' (handed to the mail server), 'failed', |
| 2193 | 2222 | // or '' (recorded before 5.8.0). Since 5.8.0. |
| 2194 | 2223 | 'mailStatus' => (string) ( $row['mail_status'] ?? '' ), |
| 2195 | 2224 | 'createtime' => $this->toSiteLocalTime( $row['createtime'] ), |
