codade

Hallo,
ich hatte dasselbe Problem und habe den Verdacht, dass die Filter der beiden Plugins vom Timing her nicht stimmen; es wurde bei mir kein einziger Zählpixel erkannt, obwohl alle Beiträge einen haben.
Ich habe mir unten stehendes php-Skript geschrieben, das eine Datenbank-seitige Migration der Zählpixel vornimmt; jetzt funktioniert die Pixel-Auslieferung auch mit diesem Plugin:

Kleine Hinweise zur Verwendung auf eigene Gefahr

• unbedingt ein Datenbank-Backup erstellen.
• Die PHP-Datei in den Ordner des METIS-Plugins legen.
• In der Datei MIGRATION_SECRET auf einen eigenen Wert ändern.
• In vgw-metis.php folgende Zeile einfügen:
  require_once plugin_dir_path( __FILE__ ) . 'migrate-from-prosodia.php';
• Als WordPress-Administrator folgende URL aufrufen:
  https://deine-domain.de/?run_prosodia_migration=1&migration_secret=DEIN_SECRET
• Die Migration läuft automatisch in Batches weiter. Warten, bis MIGRATION COMPLETE erscheint.
• Für die bisher noch keinem Post zugeordnete Pixel zusätzlich &unassigned_only=1 an die URL anhängen.
• Optional kann mit &batch_size=20 die Batch-Größe angepasst werden.
• Nach erfolgreicher Migration die PHP-Datei wieder aus dem Plugin-Ordner löschen.

Keine Garantie: Die Migration erfolgt auf eigene Verantwortung. Nach der Migration bitte
selbst prüfen, ob Pixel, Beiträge und Zuordnungen korrekt übernommen wurden.

Viel Erfolg damit!

Hier das Skript, als migrate-from-prosodia.php speichern:

<?php
/**
 * Migration script: Prosodia VGW OS → VG WORT METIS
 *
 * Reads pixel assignments from the Prosodia table (wpvgw_markers) and writes
 * them directly into the METIS tables (metis_pixels, metis_pixel_posts).
 * Runs in batches to avoid PHP timeouts; progress is stored in wp_options.
 *
 * USAGE: Add ?run_prosodia_migration=1&migration_secret=CHANGE_ME to any
 * WordPress page URL while logged in as admin, e.g.:
 *   https://your-site.com/?run_prosodia_migration=1&migration_secret=CHANGE_ME&unassigned_only=1
 *
 * Keep reloading the URL until you see "Migration complete".
 * Add &reset=1 to start over from the beginning.
 * Add &batch_size=50 to override the default batch size.
 *
 * IMPORTANT: Change MIGRATION_SECRET before deploying. Delete this file after migration.
 */

if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

define( 'MIGRATION_SECRET',    'CHANGE_ME' ); // ← change before use!
define( 'MIGRATION_BATCH_SIZE', 50 );          // rows per request
define( 'MIGRATION_OPTION_KEY', 'prosodia_migration_offset' );
define( 'MIGRATION_UNASSIGNED_OPTION_KEY', 'prosodia_migration_unassigned_offset' );
add_action( 'init', function () {
	if (
		! isset( $_GET['run_prosodia_migration'] ) ||
		! isset( $_GET['migration_secret'] ) ||
		$_GET['migration_secret'] !== MIGRATION_SECRET
	) {
		return;
	}

	if ( ! current_user_can( 'manage_options' ) ) {
		wp_die( 'Unauthorized.' );
	}

	if ( isset( $_GET['reset'] ) && $_GET['reset'] === '1' ) {
		delete_option( MIGRATION_OPTION_KEY );
	}

	$unassigned_only = isset( $_GET['unassigned_only'] )
	&& $_GET['unassigned_only'] === '1';

	// separate offset
	$offset_option_key = $unassigned_only
	? MIGRATION_UNASSIGNED_OPTION_KEY
	: MIGRATION_OPTION_KEY;

	$batch_size = isset( $_GET['batch_size'] ) ? max( 1, (int) $_GET['batch_size'] ) : MIGRATION_BATCH_SIZE;

	[ $output, $done, $next_offset, $total ] = migrate_prosodia_batch( $batch_size, $unassigned_only, $offset_option_key );

	$current_url = ( is_ssl() ? 'https' : 'http' ) . '://' . $_SERVER['HTTP_HOST'] . $_SERVER['REQUEST_URI'];
	// strip existing auto_continue param so we can set it cleanly
	$current_url = remove_query_arg( 'auto_continue', $current_url );

	echo '<!DOCTYPE html><html><head><meta charset="utf-8">';
	if ( ! $done ) {
		// Auto-reload to next batch after 1 second
		echo '<meta http-equiv="refresh" content="1;url=' . esc_attr( $current_url ) . '">';
	}
	echo '</head><body><pre>';
	echo esc_html( $output );

	if ( ! $done ) {
		echo "\n⏳ Batch done. Loading next batch automatically…\n";
		echo '   <a href="' . esc_attr( $current_url ) . '">Click here if it does not reload</a>';
	} else {
		echo "\n✅ All batches complete. You can delete this file now.\n";
	}

	echo '</pre></body></html>';
	exit;
} );

/**
 * Process one batch of rows.
 *
 * @return array [ string $log, bool $done, int $next_offset, int $total ]
 */
function migrate_prosodia_batch(
	int $batch_size,
	bool $unassigned_only = false,
	string $offset_option_key = MIGRATION_OPTION_KEY
): array {

	global $wpdb;

	$prosodia_table = $wpdb->prefix . 'wpvgw_markers';
	$metis_pixels   = $wpdb->prefix . 'metis_pixels';
	$metis_pp       = $wpdb->prefix . 'metis_pixel_posts';
	#$pixel_domain   = 'met.vgwort.de';

	// ── 1. Check Prosodia table exists ────────────────────────────────────────
	if ( $wpdb->get_var( "SHOW TABLES LIKE '$prosodia_table'" ) !== $prosodia_table ) {
		return [ "ERROR: Prosodia table '$prosodia_table' not found.", true, 0, 0 ];
	}

	// ── 2. Total eligible rows (for progress display) ─────────────────────────
	# Build where statement
	$where = "
		is_marker_disabled = 0
		AND public_marker != ''
		AND private_marker != ''
	";

	if ( $unassigned_only ) {
		$where .= "
			AND post_id IS NULL
		";
	} else {
		$where .= "
			AND post_id IS NOT NULL
			AND post_id > 0
		";
	}

	$total = (int) $wpdb->get_var( "
		SELECT COUNT(*) FROM $prosodia_table
		WHERE $where " );

	if ( $total === 0 ) {
		delete_option( MIGRATION_OPTION_KEY );
		return [ 'No eligible rows found in Prosodia table.', true, 0, 0 ];
	}

	// ── 3. Get current offset from wp_options, depending on search mode ───────
	$offset = (int) get_option( $offset_option_key, 0 );

	// ── 4. Load batch ─────────────────────────────────────────────────────────
	$rows = $wpdb->get_results(
		$wpdb->prepare(
			"
			SELECT post_id, public_marker, private_marker, order_date, server
			FROM $prosodia_table
			WHERE $where
			ORDER BY id ASC
			LIMIT %d OFFSET %d
			",
			$batch_size,
			$offset
		)
	);

	$log  = "── Batch: rows $offset–" . ( $offset + count( $rows ) - 1 ) . " of $total ──\n\n";
	$inserted_px    = 0;
	$skipped_px     = 0;
	$assigned       = 0;
	$skipped_assign = 0;
	$errors         = 0;


	// Prepare validation of existing pixels with API against TOM DB
	$public_ids = [];

	foreach ( $rows as $row ) {
	    $public_ids[] = trim( $row->public_marker );
	}

	$api_pixels = \WP_VGWORT\Tom_Pixels::check_pixel_state( $public_ids );

	if ( $api_pixels === false ) {
	    return [
	        "ERROR: METIS API request failed.",
	        true,
	        $offset,
	        $total
	    ];
	}

	$api_by_public_id = [];

	foreach ( $api_pixels as $api_pixel ) {
	    $api_by_public_id[ $api_pixel->publicUID ] = $api_pixel;
	}


	foreach ( $rows as $row ) {
		$post_id = $row->post_id !== null
			? (int) $row->post_id
			: 0;
		$public_id  = trim( $row->public_marker );
		$private_id = trim( $row->private_marker );
		$ordered_at = $row->order_date ?: current_time( 'mysql', true );
		$pixel_domain = trim( explode( '/na', $row->server, 2 )[0] );


		// Verify Pixel was found in TOM DB and ownership is clear
		$api_pixel = $api_by_public_id[ $public_id ] ?? null;

		if ( ! $api_pixel ) {
		    $log .= "  SKIP pixel $public_id – not returned by METIS API.\n";
		    $errors++;
		    continue;
		}

		if ( $api_pixel->state === 'NOT_OWNER' ) {
		    $log .= "  SKIP pixel $public_id – NOT_OWNER.\n";
		    $errors++;
		    continue;
		}

		if ( $api_pixel->state !== 'VALID' ) {
		    $log .= "  SKIP pixel $public_id – API state: {$api_pixel->state}.\n";
		    $errors++;
		    continue;
		}


		// ── metis_pixels ──────────────────────────────────────────────────────
		$px_exists = (int) $wpdb->get_var( $wpdb->prepare(
			"SELECT COUNT(*) FROM $metis_pixels WHERE public_identification_id = %s",
			$public_id
		) );

		if ( ! $px_exists ) {


			// Inset pixel into DB
			$result = $wpdb->insert( $metis_pixels, [
			    'public_identification_id'  => $public_id,
			    'private_identification_id' => $api_pixel->privateUID,
			    'count_started'             => 0,
			    'domain'                    => $pixel_domain,
			    'ordered_at'                => $ordered_at,
			    'min_hits'                  => '',
			    'source'                    => 'dbmigration',
			    'ownership'                 => 1,
			    'disabled'                  => 0,
			] );


			if ( $result === false ) {
				$log .= "  ERROR inserting pixel $public_id: " . $wpdb->last_error . "\n";
				$errors++;
				continue;
			}
			$inserted_px++;
			$log .= "  INSERT pixel $public_id\n";
		} else {
			$skipped_px++;
			$log .= "  SKIP   pixel $public_id (already in metis_pixels)\n";
		}

		// In unassigned_only mode: only create the pixel.
		// No post assignment is created because post_id IS NULL.
		if ( $unassigned_only ) {
			$log .= "  INFO  pixel $public_id – intentionally unassigned\n";
			continue;
		}
		// ── Post prüfen ───────────────────────────────────────────────────────
		$post_status = get_post_status( $post_id );

		if ( ! $post_status ) {
			$log .= "  SKIP  post #$post_id – post does not exist.\n";
			continue;
		}

		if ( $post_status === 'trash' ) {
			$log .= "  SKIP  post #$post_id – post is in trash.\n";
			continue;
		}


		// ── metis_pixel_posts ─────────────────────────────────────────────────
		$pp_exists = (int) $wpdb->get_var( $wpdb->prepare(
			"SELECT COUNT(*) FROM $metis_pp WHERE post_id = %d",
			$post_id
		) );

		if ( ! $pp_exists ) {
			$result = $wpdb->insert( $metis_pp, [
				'public_identification_id' => $public_id,
				'post_id'                  => $post_id,
				'active'                   => 1,
			] );

			if ( $result === false ) {
				$log .= "  ERROR assigning pixel $public_id to post #$post_id: " . $wpdb->last_error . "\n";
				$errors++;
				continue;
			}
			$assigned++;
			$log .= "  ASSIGN pixel $public_id → post #$post_id\n";
		} else {
			$skipped_assign++;
			$log .= "  SKIP   assignment for post #$post_id (already assigned)\n";
		}
	}

	$log .= "\n  Inserted: $inserted_px  |  Skipped: $skipped_px  |  Assigned: $assigned  |  Skipped assignments: $skipped_assign  |  Errors: $errors\n";

	// ── 5. Advance or finish ──────────────────────────────────────────────────
	$next_offset = $offset + count( $rows );
	$done        = ( $next_offset >= $total || count( $rows ) < $batch_size );

	if ( $done ) {
		delete_option( $offset_option_key );
		$log .= "\n════════════════════════════════\n";
		$log .= "MIGRATION COMPLETE ($total rows processed).\n";
		$log .= "════════════════════════════════\n";
	} else {
		update_option( $offset_option_key, $next_offset, false );
		$log .= "\n  Progress: $next_offset / $total (" . round( $next_offset / $total * 100 ) . "%)\n";
	}

	return [ $log, $done, $next_offset, $total ];
}

• This reply was modified 1 month, 3 weeks ago by codade.
• This reply was modified 1 month, 3 weeks ago by codade.
• This reply was modified 1 month, 3 weeks ago by codade.