383 lines
12 KiB
PHP
383 lines
12 KiB
PHP
<?php
|
|
if ( ! defined( 'ABSPATH' ) ) {
|
|
exit;
|
|
}
|
|
|
|
class RR_Data {
|
|
|
|
private $meta_table = 'of47_wp_rosins_restaurants_meta';
|
|
private $post_type = 'rosins-restaurants';
|
|
private $taxonomy = 'region';
|
|
private $cache_ttl = 3600; // 1 Stunde
|
|
private $table_format = null; // 'jet' oder 'wp'
|
|
|
|
/**
|
|
* Erkennt das Tabellenformat:
|
|
* - JetEngine custom tables haben eigene Spalten pro Feld (staffel, status, etc.)
|
|
* - WP-Style hat meta_key / meta_value Spalten
|
|
*/
|
|
private function detect_table_format() {
|
|
if ( null !== $this->table_format ) {
|
|
return $this->table_format;
|
|
}
|
|
|
|
global $wpdb;
|
|
$columns = $wpdb->get_results( "SHOW COLUMNS FROM {$this->meta_table}" );
|
|
|
|
if ( empty( $columns ) ) {
|
|
$this->table_format = 'wp';
|
|
return $this->table_format;
|
|
}
|
|
|
|
$col_names = array_map( function( $c ) { return $c->Field; }, $columns );
|
|
|
|
// JetEngine custom tables haben die Feldnamen direkt als Spalten
|
|
if ( in_array( 'status', $col_names, true ) ) {
|
|
$this->table_format = 'jet';
|
|
} else {
|
|
$this->table_format = 'wp';
|
|
}
|
|
|
|
return $this->table_format;
|
|
}
|
|
|
|
/**
|
|
* Build a lookup: post_id => [ field_name => value ]
|
|
*/
|
|
private function build_meta_map() {
|
|
$cached = get_transient( 'rr_meta_map' );
|
|
if ( false !== $cached ) {
|
|
return $cached;
|
|
}
|
|
|
|
global $wpdb;
|
|
$posts_table = $wpdb->posts;
|
|
$format = $this->detect_table_format();
|
|
$map = [];
|
|
|
|
if ( 'jet' === $format ) {
|
|
// JetEngine: Spalten sind direkt die Feldnamen
|
|
$results = $wpdb->get_results( $wpdb->prepare(
|
|
"SELECT p.ID as post_id, p.post_title, m.*
|
|
FROM {$posts_table} p
|
|
INNER JOIN {$this->meta_table} m ON p.ID = m.object_ID
|
|
WHERE p.post_type = %s AND p.post_status = 'publish'",
|
|
$this->post_type
|
|
) );
|
|
|
|
foreach ( $results as $row ) {
|
|
$row_arr = (array) $row;
|
|
$pid = $row_arr['post_id'];
|
|
unset( $row_arr['post_id'], $row_arr['post_title'], $row_arr['meta_ID'], $row_arr['object_ID'] );
|
|
$map[ $pid ] = $row_arr;
|
|
$map[ $pid ]['_title'] = $row->post_title;
|
|
}
|
|
} else {
|
|
// WP-Style: meta_key / meta_value
|
|
$results = $wpdb->get_results( $wpdb->prepare(
|
|
"SELECT p.ID as post_id, p.post_title, m.meta_key, m.meta_value
|
|
FROM {$posts_table} p
|
|
INNER JOIN {$this->meta_table} m ON p.ID = m.post_id
|
|
WHERE p.post_type = %s AND p.post_status = 'publish'",
|
|
$this->post_type
|
|
) );
|
|
|
|
foreach ( $results as $row ) {
|
|
$map[ $row->post_id ][ $row->meta_key ] = $row->meta_value;
|
|
$map[ $row->post_id ]['_title'] = $row->post_title;
|
|
}
|
|
}
|
|
|
|
set_transient( 'rr_meta_map', $map, $this->cache_ttl );
|
|
return $map;
|
|
}
|
|
|
|
/**
|
|
* Debug: Rohdaten für Diagnose
|
|
*/
|
|
public function get_debug_info() {
|
|
global $wpdb;
|
|
|
|
$info = [];
|
|
|
|
// Tabellenformat
|
|
$info['table_format'] = $this->detect_table_format();
|
|
|
|
// Spalten der Meta-Tabelle
|
|
$columns = $wpdb->get_results( "SHOW COLUMNS FROM {$this->meta_table}" );
|
|
$info['columns'] = $columns ? array_map( function( $c ) { return $c->Field; }, $columns ) : 'TABELLE NICHT GEFUNDEN';
|
|
|
|
// Anzahl Zeilen
|
|
$info['meta_row_count'] = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$this->meta_table}" );
|
|
|
|
// Anzahl publizierte Posts
|
|
$info['published_posts'] = (int) $wpdb->get_var( $wpdb->prepare(
|
|
"SELECT COUNT(*) FROM {$wpdb->posts} WHERE post_type = %s AND post_status = 'publish'",
|
|
$this->post_type
|
|
) );
|
|
|
|
// Beispielzeile
|
|
$info['sample_row'] = $wpdb->get_row( "SELECT * FROM {$this->meta_table} LIMIT 1", ARRAY_A );
|
|
|
|
// Meta-Map Ergebnis
|
|
// Transients löschen für frische Daten
|
|
$this->clear_cache();
|
|
$map = $this->build_meta_map();
|
|
$info['meta_map_count'] = count( $map );
|
|
$info['meta_map_sample'] = array_slice( $map, 0, 2, true );
|
|
|
|
// Status-Werte die gefunden werden
|
|
$info['status_counts'] = $this->get_status_counts();
|
|
|
|
return $info;
|
|
}
|
|
|
|
/**
|
|
* Cache löschen
|
|
*/
|
|
public function clear_cache() {
|
|
delete_transient( 'rr_meta_map' );
|
|
delete_transient( 'rr_status_counts' );
|
|
delete_transient( 'rr_staffel_status' );
|
|
delete_transient( 'rr_testessen_verb' );
|
|
delete_transient( 'rr_region_counts' );
|
|
delete_transient( 'rr_region_status' );
|
|
delete_transient( 'rr_staffel_avg' );
|
|
delete_transient( 'rr_all_meta' );
|
|
}
|
|
|
|
/**
|
|
* Normalisiert den Status-Wert für Vergleiche
|
|
*/
|
|
private function normalize_status( $raw ) {
|
|
$s = mb_strtolower( trim( $raw ) );
|
|
// Verschiedene mögliche Schreibweisen abfangen
|
|
if ( in_array( $s, [ 'geöffnet', 'geoeffnet', 'offen', 'geoffnet' ], true ) ) {
|
|
return 'geöffnet';
|
|
}
|
|
if ( in_array( $s, [ 'geschlossen', 'zu', 'closed' ], true ) ) {
|
|
return 'geschlossen';
|
|
}
|
|
if ( false !== strpos( $s, 'neuer' ) || false !== strpos( $s, 'betreiber' ) || false !== strpos( $s, 'new' ) ) {
|
|
return 'neuer Betreiber';
|
|
}
|
|
return $s;
|
|
}
|
|
|
|
/**
|
|
* 1. Status-Verteilung
|
|
*/
|
|
public function get_status_counts() {
|
|
$cached = get_transient( 'rr_status_counts' );
|
|
if ( false !== $cached ) {
|
|
return $cached;
|
|
}
|
|
|
|
$map = $this->build_meta_map();
|
|
$counts = [
|
|
'geöffnet' => 0,
|
|
'geschlossen' => 0,
|
|
'neuer Betreiber' => 0,
|
|
];
|
|
|
|
foreach ( $map as $meta ) {
|
|
$status = $this->normalize_status( $meta['status'] ?? '' );
|
|
if ( isset( $counts[ $status ] ) ) {
|
|
$counts[ $status ]++;
|
|
}
|
|
}
|
|
|
|
set_transient( 'rr_status_counts', $counts, $this->cache_ttl );
|
|
return $counts;
|
|
}
|
|
|
|
/**
|
|
* 2. Status pro Staffel
|
|
*/
|
|
public function get_staffel_status() {
|
|
$cached = get_transient( 'rr_staffel_status' );
|
|
if ( false !== $cached ) {
|
|
return $cached;
|
|
}
|
|
|
|
$map = $this->build_meta_map();
|
|
$result = [];
|
|
|
|
foreach ( $map as $meta ) {
|
|
$staffel = trim( $meta['staffel'] ?? '' );
|
|
$status = $this->normalize_status( $meta['status'] ?? '' );
|
|
if ( '' === $staffel || '' === $status ) {
|
|
continue;
|
|
}
|
|
if ( ! isset( $result[ $staffel ] ) ) {
|
|
$result[ $staffel ] = [
|
|
'geöffnet' => 0,
|
|
'geschlossen' => 0,
|
|
'neuer Betreiber' => 0,
|
|
];
|
|
}
|
|
if ( isset( $result[ $staffel ][ $status ] ) ) {
|
|
$result[ $staffel ][ $status ]++;
|
|
}
|
|
}
|
|
|
|
ksort( $result, SORT_NUMERIC );
|
|
set_transient( 'rr_staffel_status', $result, $this->cache_ttl );
|
|
return $result;
|
|
}
|
|
|
|
/**
|
|
* 3. Testessen-Verbesserung
|
|
*/
|
|
public function get_testessen_verbesserung() {
|
|
$cached = get_transient( 'rr_testessen_verb' );
|
|
if ( false !== $cached ) {
|
|
return $cached;
|
|
}
|
|
|
|
$map = $this->build_meta_map();
|
|
$result = [];
|
|
|
|
foreach ( $map as $post_id => $meta ) {
|
|
$e1 = floatval( $meta['ergebnis_1_testessen_normalisiert'] ?? 0 );
|
|
$e2 = floatval( $meta['ergebnis_2_testessen_normalisiert'] ?? 0 );
|
|
if ( $e1 <= 0 && $e2 <= 0 ) {
|
|
continue;
|
|
}
|
|
$staffel = trim( $meta['staffel'] ?? '?' );
|
|
$result[] = [
|
|
'post_id' => $post_id,
|
|
'staffel' => $staffel,
|
|
'test1' => $e1,
|
|
'test2' => $e2,
|
|
];
|
|
}
|
|
|
|
set_transient( 'rr_testessen_verb', $result, $this->cache_ttl );
|
|
return $result;
|
|
}
|
|
|
|
/**
|
|
* 4. Restaurants pro Region
|
|
*/
|
|
public function get_region_counts() {
|
|
$cached = get_transient( 'rr_region_counts' );
|
|
if ( false !== $cached ) {
|
|
return $cached;
|
|
}
|
|
|
|
$terms = get_terms( [
|
|
'taxonomy' => $this->taxonomy,
|
|
'hide_empty' => true,
|
|
] );
|
|
|
|
if ( is_wp_error( $terms ) ) {
|
|
return [];
|
|
}
|
|
|
|
$result = [];
|
|
foreach ( $terms as $term ) {
|
|
$result[ $term->name ] = (int) $term->count;
|
|
}
|
|
|
|
arsort( $result );
|
|
set_transient( 'rr_region_counts', $result, $this->cache_ttl );
|
|
return $result;
|
|
}
|
|
|
|
/**
|
|
* 5. Status pro Region
|
|
*/
|
|
public function get_region_status() {
|
|
$cached = get_transient( 'rr_region_status' );
|
|
if ( false !== $cached ) {
|
|
return $cached;
|
|
}
|
|
|
|
$terms = get_terms( [
|
|
'taxonomy' => $this->taxonomy,
|
|
'hide_empty' => true,
|
|
] );
|
|
|
|
if ( is_wp_error( $terms ) ) {
|
|
return [];
|
|
}
|
|
|
|
$meta_map = $this->build_meta_map();
|
|
$result = [];
|
|
|
|
foreach ( $terms as $term ) {
|
|
$post_ids = get_objects_in_term( $term->term_id, $this->taxonomy );
|
|
if ( is_wp_error( $post_ids ) ) {
|
|
continue;
|
|
}
|
|
|
|
$counts = [
|
|
'geöffnet' => 0,
|
|
'geschlossen' => 0,
|
|
'neuer Betreiber' => 0,
|
|
];
|
|
|
|
foreach ( $post_ids as $pid ) {
|
|
$status = $this->normalize_status( $meta_map[ $pid ]['status'] ?? '' );
|
|
if ( isset( $counts[ $status ] ) ) {
|
|
$counts[ $status ]++;
|
|
}
|
|
}
|
|
|
|
$result[ $term->name ] = $counts;
|
|
}
|
|
|
|
set_transient( 'rr_region_status', $result, $this->cache_ttl );
|
|
return $result;
|
|
}
|
|
|
|
/**
|
|
* 6. Durchschnittliche Testessen-Ergebnisse pro Staffel
|
|
*/
|
|
public function get_staffel_ergebnis_avg() {
|
|
$cached = get_transient( 'rr_staffel_avg' );
|
|
if ( false !== $cached ) {
|
|
return $cached;
|
|
}
|
|
|
|
$map = $this->build_meta_map();
|
|
$sums = [];
|
|
|
|
foreach ( $map as $meta ) {
|
|
$staffel = trim( $meta['staffel'] ?? '' );
|
|
if ( '' === $staffel ) {
|
|
continue;
|
|
}
|
|
|
|
$e1 = floatval( $meta['ergebnis_1_testessen_normalisiert'] ?? 0 );
|
|
$e2 = floatval( $meta['ergebnis_2_testessen_normalisiert'] ?? 0 );
|
|
|
|
if ( ! isset( $sums[ $staffel ] ) ) {
|
|
$sums[ $staffel ] = [ 'sum1' => 0, 'sum2' => 0, 'count1' => 0, 'count2' => 0 ];
|
|
}
|
|
|
|
if ( $e1 > 0 ) {
|
|
$sums[ $staffel ]['sum1'] += $e1;
|
|
$sums[ $staffel ]['count1']++;
|
|
}
|
|
if ( $e2 > 0 ) {
|
|
$sums[ $staffel ]['sum2'] += $e2;
|
|
$sums[ $staffel ]['count2']++;
|
|
}
|
|
}
|
|
|
|
$result = [];
|
|
ksort( $sums, SORT_NUMERIC );
|
|
foreach ( $sums as $staffel => $s ) {
|
|
$result[ $staffel ] = [
|
|
'avg1' => $s['count1'] > 0 ? round( $s['sum1'] / $s['count1'], 2 ) : 0,
|
|
'avg2' => $s['count2'] > 0 ? round( $s['sum2'] / $s['count2'], 2 ) : 0,
|
|
];
|
|
}
|
|
|
|
set_transient( 'rr_staffel_avg', $result, $this->cache_ttl );
|
|
return $result;
|
|
}
|
|
}
|