WP_REST_Server::READABLE,
'callback' => array( __CLASS__, 'live_snapshot' ),
'permission_callback' => static fn(): bool => current_user_can( 'manage_options' ),
'args' => array(
'page' => array(
'default' => 1,
'sanitize_callback' => 'absint',
),
),
)
);
}
public static function live_snapshot( WP_REST_Request $request ): WP_REST_Response {
ob_start();
self::render_dashboard( max( 1, absint( $request->get_param( 'page' ) ) ) );
$html = (string) ob_get_clean();
$response = rest_ensure_response(
array(
'html' => $html,
'updatedAt' => current_time( 'mysql' ),
)
);
$response->header( 'Cache-Control', 'no-store, no-cache, must-revalidate, max-age=0' );
$response->header( 'CDN-Cache-Control', 'no-store' );
$response->header( 'Pragma', 'no-cache' );
$response->header( 'X-Robots-Tag', 'noindex, nofollow' );
return $response;
}
public static function table_name(): string {
global $wpdb;
return $wpdb->prefix . 'sg7g_daily_views';
}
public static function install(): void {
global $wpdb;
require_once ABSPATH . 'wp-admin/includes/upgrade.php';
$table = self::table_name();
$charset = $wpdb->get_charset_collate();
$sql = "CREATE TABLE {$table} (
view_date date NOT NULL,
post_id bigint(20) unsigned NOT NULL,
views bigint(20) unsigned NOT NULL DEFAULT 0,
PRIMARY KEY (view_date, post_id),
KEY post_id (post_id),
KEY views (views)
) {$charset};";
dbDelta( $sql );
update_option( self::SCHEMA_OPTION, self::SCHEMA_VERSION, false );
}
public static function maybe_install(): void {
if ( self::SCHEMA_VERSION === (string) get_option( self::SCHEMA_OPTION, '' ) ) {
return;
}
self::install();
}
public static function record( int $post_id ): void {
if ( $post_id <= 0 ) {
return;
}
global $wpdb;
$table = self::table_name();
$date = current_time( 'Y-m-d' );
// A direct write is required for an atomic aggregate counter in the plugin-owned table.
// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
$result = $wpdb->query(
$wpdb->prepare(
'INSERT INTO %i (view_date, post_id, views)
VALUES (%s, %d, 1)
ON DUPLICATE KEY UPDATE views = views + 1',
$table,
$date,
$post_id
)
);
// Recover gracefully if a database cleanup removed the aggregate table while the schema option remained in place.
if ( false === $result && self::is_missing_table_error() ) {
self::install();
// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
$wpdb->query(
$wpdb->prepare(
'INSERT INTO %i (view_date, post_id, views)
VALUES (%s, %d, 1)
ON DUPLICATE KEY UPDATE views = views + 1',
$table,
$date,
$post_id
)
);
}
}
public static function delete_post_data( int $post_id ): void {
if ( 'sg7g_media' !== get_post_type( $post_id ) ) {
return;
}
global $wpdb;
// A direct delete is required for the plugin-owned aggregate table.
// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
$wpdb->delete( self::table_name(), array( 'post_id' => $post_id ), array( '%d' ) );
}
public static function menu(): void {
add_submenu_page(
'edit.php?post_type=sg7g_media',
__( '7S Video & Music Gallery Statistics', '7sgaleri' ),
__( 'Statistics', '7sgaleri' ),
'manage_options',
'sg7g-statistics',
array( __CLASS__, 'render_page' )
);
}
public static function render_page(): void {
if ( ! current_user_can( 'manage_options' ) ) {
wp_die( esc_html__( 'You do not have permission to view this page.', '7sgaleri' ) );
}
$page = max( 1, absint( $_GET['paged'] ?? 1 ) ); // phpcs:ignore WordPress.Security.NonceVerification.Recommended
$export_url = wp_nonce_url(
admin_url( 'admin-post.php?action=sg7g_export_statistics' ),
'sg7g_export_statistics'
);
$archive_url = get_post_type_archive_link( 'sg7g_media' ) ?: home_url( '/' );
?>
format( 'Y-m-d' );
$last_7_from = $today_date->modify( '-6 days' )->format( 'Y-m-d' );
$last_30_from = $today_date->modify( '-29 days' )->format( 'Y-m-d' );
$view_totals = self::summary( $today, $last_7_from, $last_30_from );
$like_totals = self::like_summary( $today_date );
$daily = self::daily_series( $last_30_from, $today );
$top_video = self::top_content( 'video', 5 );
$top_audio = self::top_content( 'audio', 5 );
$top_liked = self::top_liked_content( 5 );
$total_items = self::published_count();
$total_pages = max( 1, (int) ceil( $total_items / self::PER_PAGE ) );
$page = min( $total_pages, max( 1, $requested_page ) );
$rows = self::content_rows( $page, self::PER_PAGE );
?>
getTimestamp() ), 'dashicons-clock' ); ?>
getTimestamp() ), 'dashicons-heart' ); ?>
$fields Row fields.
*/
private static function csv_line( array $fields ): string {
$cells = array_map(
static function ( mixed $field ): string {
$value = str_replace( '"', '""', (string) $field );
if ( false !== strpbrk( $value, ";\"\r\n" ) ) {
return '"' . $value . '"';
}
return $value;
},
$fields
);
return implode( ';', $cells ) . "\r\n";
}
private static function summary( string $today, string $last_7_from, string $last_30_from ): array {
global $wpdb;
$table = self::table_name();
// Aggregate reporting requires direct read-only queries; results are request-scoped administrator data.
// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
$total = (int) $wpdb->get_var(
$wpdb->prepare(
'SELECT COALESCE(SUM(CAST(pm.meta_value AS UNSIGNED)), 0)
FROM %i pm
INNER JOIN %i p ON p.ID = pm.post_id
WHERE pm.meta_key = %s
AND p.post_type = %s
AND p.post_status = %s
AND p.post_password = %s',
$wpdb->postmeta,
$wpdb->posts,
'_sg7g_views',
'sg7g_media',
'publish',
''
)
);
// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
$daily = $wpdb->get_row(
$wpdb->prepare(
'SELECT
COALESCE(SUM(CASE WHEN view_date = %s THEN views ELSE 0 END), 0) AS today,
COALESCE(SUM(CASE WHEN view_date BETWEEN %s AND %s THEN views ELSE 0 END), 0) AS last7,
COALESCE(SUM(CASE WHEN view_date BETWEEN %s AND %s THEN views ELSE 0 END), 0) AS last30
FROM %i dv
INNER JOIN %i p ON p.ID = dv.post_id
WHERE p.post_type = %s
AND p.post_status = %s
AND p.post_password = %s',
$today,
$last_7_from,
$today,
$last_30_from,
$today,
$table,
$wpdb->posts,
'sg7g_media',
'publish',
''
),
ARRAY_A
);
return array(
'total' => $total,
'today' => (int) ( $daily['today'] ?? 0 ),
'last7' => (int) ( $daily['last7'] ?? 0 ),
'last30' => (int) ( $daily['last30'] ?? 0 ),
);
}
private static function like_summary( DateTimeImmutable $today ): array {
global $wpdb;
$tomorrow = $today->modify( '+1 day' );
$today_from = self::utc_mysql( $today );
$last_7_from = self::utc_mysql( $today->modify( '-6 days' ) );
$last_30_from = self::utc_mysql( $today->modify( '-29 days' ) );
$until = self::utc_mysql( $tomorrow );
// Aggregate reporting requires a direct read-only query against the plugin-owned likes table.
// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
$row = $wpdb->get_row(
$wpdb->prepare(
'SELECT
COUNT(*) AS total,
COALESCE(SUM(CASE WHEN l.created_at >= %s AND l.created_at < %s THEN 1 ELSE 0 END), 0) AS today,
COALESCE(SUM(CASE WHEN l.created_at >= %s AND l.created_at < %s THEN 1 ELSE 0 END), 0) AS last7,
COALESCE(SUM(CASE WHEN l.created_at >= %s AND l.created_at < %s THEN 1 ELSE 0 END), 0) AS last30
FROM %i l
INNER JOIN %i p ON p.ID = l.post_id
WHERE p.post_type = %s
AND p.post_status = %s
AND p.post_password = %s',
$today_from,
$until,
$last_7_from,
$until,
$last_30_from,
$until,
SG7G_Likes::table_name(),
$wpdb->posts,
'sg7g_media',
'publish',
''
),
ARRAY_A
);
return array(
'total' => (int) ( $row['total'] ?? 0 ),
'today' => (int) ( $row['today'] ?? 0 ),
'last7' => (int) ( $row['last7'] ?? 0 ),
'last30' => (int) ( $row['last30'] ?? 0 ),
);
}
private static function utc_mysql( DateTimeImmutable $date ): string {
return $date->setTimezone( new DateTimeZone( 'UTC' ) )->format( 'Y-m-d H:i:s' );
}
private static function daily_series( string $from, string $to ): array {
global $wpdb;
$table = self::table_name();
// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
$rows = $wpdb->get_results(
$wpdb->prepare(
'SELECT dv.view_date, SUM(dv.views) AS views
FROM %i dv
INNER JOIN %i p ON p.ID = dv.post_id
WHERE dv.view_date BETWEEN %s AND %s
AND p.post_type = %s
AND p.post_status = %s
AND p.post_password = %s
GROUP BY dv.view_date
ORDER BY dv.view_date ASC',
$table,
$wpdb->posts,
$from,
$to,
'sg7g_media',
'publish',
''
),
OBJECT_K
);
$series = array();
$current = new DateTimeImmutable( $from, wp_timezone() );
$end = new DateTimeImmutable( $to, wp_timezone() );
while ( $current <= $end ) {
$date = $current->format( 'Y-m-d' );
$series[] = array(
'date' => $date,
'label' => wp_date( 'j M', $current->getTimestamp() ),
'views' => isset( $rows[ $date ] ) ? (int) $rows[ $date ]->views : 0,
);
$current = $current->modify( '+1 day' );
}
return $series;
}
private static function top_content( string $media_type, int $limit ): array {
global $wpdb;
// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
$rows = $wpdb->get_results(
$wpdb->prepare(
'SELECT p.ID, p.post_title, COALESCE(v.total, 0) AS total
FROM %i p
INNER JOIN %i type_meta
ON type_meta.post_id = p.ID
AND type_meta.meta_key = %s
AND type_meta.meta_value = %s
LEFT JOIN (
SELECT post_id, SUM(CAST(meta_value AS UNSIGNED)) AS total
FROM %i
WHERE meta_key = %s
GROUP BY post_id
) v ON v.post_id = p.ID
WHERE p.post_type = %s
AND p.post_status = %s
AND p.post_password = %s
GROUP BY p.ID, p.post_title, v.total
HAVING total > 0
ORDER BY total DESC, p.post_title ASC
LIMIT %d',
$wpdb->posts,
$wpdb->postmeta,
'_sg7g_media_type',
$media_type,
$wpdb->postmeta,
'_sg7g_views',
'sg7g_media',
'publish',
'',
$limit
),
ARRAY_A
);
return self::attach_people( $rows );
}
private static function top_liked_content( int $limit ): array {
global $wpdb;
// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
$rows = $wpdb->get_results(
$wpdb->prepare(
'SELECT p.ID, p.post_title, COUNT(l.id) AS total
FROM %i l
INNER JOIN %i p ON p.ID = l.post_id
WHERE p.post_type = %s
AND p.post_status = %s
AND p.post_password = %s
GROUP BY p.ID, p.post_title
ORDER BY total DESC, MAX(l.created_at) DESC, p.post_title ASC
LIMIT %d',
SG7G_Likes::table_name(),
$wpdb->posts,
'sg7g_media',
'publish',
'',
$limit
),
ARRAY_A
);
return self::attach_people( is_array( $rows ) ? $rows : array() );
}
private static function content_rows( int $page, int $per_page ): array {
global $wpdb;
$today_date = new DateTimeImmutable( 'today', wp_timezone() );
$today = $today_date->format( 'Y-m-d' );
$last_7_from = $today_date->modify( '-6 days' )->format( 'Y-m-d' );
$previous_7_to = $today_date->modify( '-7 days' )->format( 'Y-m-d' );
$previous_7_from = $today_date->modify( '-13 days' )->format( 'Y-m-d' );
$last_30_from = $today_date->modify( '-29 days' )->format( 'Y-m-d' );
$table = self::table_name();
$likes_table = SG7G_Likes::table_name();
if ( $per_page > 0 ) {
$offset = max( 0, ( $page - 1 ) * $per_page );
// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
$rows = $wpdb->get_results(
$wpdb->prepare(
'SELECT p.ID, p.post_title,
COALESCE(v.total, 0) AS total,
COALESCE(l.likes, 0) AS likes,
COALESCE(d.today, 0) AS today,
COALESCE(d.last7, 0) AS last7,
COALESCE(d.previous7, 0) AS previous7,
COALESCE(d.last30, 0) AS last30,
MAX(CASE WHEN pm.meta_key = %s THEN pm.meta_value ELSE %s END) AS media_type,
MAX(CASE WHEN pm.meta_key = %s THEN pm.meta_value ELSE %s END) AS source
FROM %i p
LEFT JOIN (
SELECT post_id, SUM(CAST(meta_value AS UNSIGNED)) AS total
FROM %i
WHERE meta_key = %s
GROUP BY post_id
) v ON v.post_id = p.ID
LEFT JOIN (
SELECT post_id, COUNT(*) AS likes
FROM %i
GROUP BY post_id
) l ON l.post_id = p.ID
LEFT JOIN (
SELECT post_id,
SUM(CASE WHEN view_date = %s THEN views ELSE 0 END) AS today,
SUM(CASE WHEN view_date BETWEEN %s AND %s THEN views ELSE 0 END) AS last7,
SUM(CASE WHEN view_date BETWEEN %s AND %s THEN views ELSE 0 END) AS previous7,
SUM(CASE WHEN view_date BETWEEN %s AND %s THEN views ELSE 0 END) AS last30
FROM %i
GROUP BY post_id
) d ON d.post_id = p.ID
LEFT JOIN %i pm
ON pm.post_id = p.ID
AND pm.meta_key IN (%s, %s)
WHERE p.post_type = %s
AND p.post_status = %s
AND p.post_password = %s
GROUP BY p.ID, p.post_title, v.total, l.likes, d.today, d.last7, d.previous7, d.last30
ORDER BY total DESC, p.post_title ASC
LIMIT %d OFFSET %d',
'_sg7g_media_type',
'',
'_sg7g_source',
'',
$wpdb->posts,
$wpdb->postmeta,
'_sg7g_views',
$likes_table,
$today,
$last_7_from,
$today,
$previous_7_from,
$previous_7_to,
$last_30_from,
$today,
$table,
$wpdb->postmeta,
'_sg7g_media_type',
'_sg7g_source',
'sg7g_media',
'publish',
'',
$per_page,
$offset
),
ARRAY_A
);
} else {
// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
$rows = $wpdb->get_results(
$wpdb->prepare(
'SELECT p.ID, p.post_title,
COALESCE(v.total, 0) AS total,
COALESCE(l.likes, 0) AS likes,
COALESCE(d.today, 0) AS today,
COALESCE(d.last7, 0) AS last7,
COALESCE(d.previous7, 0) AS previous7,
COALESCE(d.last30, 0) AS last30,
MAX(CASE WHEN pm.meta_key = %s THEN pm.meta_value ELSE %s END) AS media_type,
MAX(CASE WHEN pm.meta_key = %s THEN pm.meta_value ELSE %s END) AS source
FROM %i p
LEFT JOIN (
SELECT post_id, SUM(CAST(meta_value AS UNSIGNED)) AS total
FROM %i
WHERE meta_key = %s
GROUP BY post_id
) v ON v.post_id = p.ID
LEFT JOIN (
SELECT post_id, COUNT(*) AS likes
FROM %i
GROUP BY post_id
) l ON l.post_id = p.ID
LEFT JOIN (
SELECT post_id,
SUM(CASE WHEN view_date = %s THEN views ELSE 0 END) AS today,
SUM(CASE WHEN view_date BETWEEN %s AND %s THEN views ELSE 0 END) AS last7,
SUM(CASE WHEN view_date BETWEEN %s AND %s THEN views ELSE 0 END) AS previous7,
SUM(CASE WHEN view_date BETWEEN %s AND %s THEN views ELSE 0 END) AS last30
FROM %i
GROUP BY post_id
) d ON d.post_id = p.ID
LEFT JOIN %i pm
ON pm.post_id = p.ID
AND pm.meta_key IN (%s, %s)
WHERE p.post_type = %s
AND p.post_status = %s
AND p.post_password = %s
GROUP BY p.ID, p.post_title, v.total, l.likes, d.today, d.last7, d.previous7, d.last30
ORDER BY total DESC, p.post_title ASC',
'_sg7g_media_type',
'',
'_sg7g_source',
'',
$wpdb->posts,
$wpdb->postmeta,
'_sg7g_views',
$likes_table,
$today,
$last_7_from,
$today,
$previous_7_from,
$previous_7_to,
$last_30_from,
$today,
$table,
$wpdb->postmeta,
'_sg7g_media_type',
'_sg7g_source',
'sg7g_media',
'publish',
''
),
ARRAY_A
);
}
return self::attach_people( $rows );
}
private static function attach_people( array $rows ): array {
$post_ids = array_values(
array_unique(
array_filter(
array_map(
static fn( array $row ): int => absint( $row['ID'] ?? 0 ),
$rows
)
)
)
);
if ( $post_ids ) {
update_meta_cache( 'post', $post_ids );
}
return $rows;
}
private static function published_count(): int {
global $wpdb;
// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
return (int) $wpdb->get_var(
$wpdb->prepare(
'SELECT COUNT(ID)
FROM %i
WHERE post_type = %s
AND post_status = %s
AND post_password = %s',
$wpdb->posts,
'sg7g_media',
'publish',
''
)
);
}
private static function is_missing_table_error(): bool {
global $wpdb;
$error = strtolower( (string) $wpdb->last_error );
return str_contains( $error, "doesn't exist" ) || str_contains( $error, 'does not exist' ) || str_contains( $error, 'no such table' );
}
private static function summary_card( string $title, int $value, string $caption, string $icon ): void {
?>
$item ) :
$height = $max > 0 ? max( 2, round( ( (int) $item['views'] / $max ) * 100, 2 ) ) : 2;
$show_label = 0 === $index || count( $series ) - 1 === $index || 0 === ( $index + 1 ) % 5;
?>
-
'sg7g-stat-top-list__image', 'loading' => 'lazy' ) ); // phpcs:ignore WordPress.Security.EscapeOutput.OutputNotEscaped ?>
'sg7g_media',
'page' => 'sg7g-statistics',
'paged' => '%#%',
),
admin_url( 'edit.php' )
);
echo '';
echo wp_kses_post(
paginate_links(
array(
'base' => $base,
'format' => '',
'current' => $current,
'total' => $total,
'prev_text' => __( '‹ Previous', '7sgaleri' ),
'next_text' => __( 'Next ›', '7sgaleri' ),
)
)
);
echo '