'string', 'path_id' => 'int', 'visitors' => 'int', 'pageviews' => 'int', ); /** * Holds all the default values for the columns. * * @var array * * @since 5.1.0 */ protected static $fields_values = array( 'date' => '', 'path_id' => 0, 'visitors' => 0, 'pageviews' => 0, ); /** * Creates table functionality. * * @param \wpdb $connection - \wpdb connection to be used for name extraction. * * @return bool * * @since 5.1.0 */ public static function create_table( $connection = null ): bool { if ( null !== $connection ) { if ( $connection instanceof \wpdb ) { $collate = $connection->get_charset_collate(); } } else { $collate = self::get_connection()->get_charset_collate(); } $table_name = self::get_table_name( $connection ); if ( ! is_string( $table_name ) || ! preg_match( '/^[a-zA-Z0-9_]+$/', $table_name ) ) { return false; } $wp_entity_sql = ' CREATE TABLE `' . $table_name . '` ( date DATE NOT NULL, path_id INT UNSIGNED NOT NULL, visitors INT UNSIGNED NOT NULL DEFAULT 0, pageviews INT UNSIGNED NOT NULL DEFAULT 0, PRIMARY KEY (date, path_id), KEY `path_id` (`path_id`) ) ENGINE=InnoDB ' . $collate . ';'; return self::maybe_create_table( $table_name, $wp_entity_sql, $connection ); } /** * Upsert page stats for a given date and path. * * @param string $date Date in Y-m-d format. * @param int $path_id Path ID from Traffic_Paths_Entity. * @param int $visitors Number of new visitors to add. * @param int $pageviews Number of new pageviews to add. * * @return void * * @since 5.1.0 */ public static function upsert( string $date, int $path_id, int $visitors, int $pageviews ): void { $_wpdb = static::get_connection(); $table = self::get_table_name( $_wpdb ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared self::query_run( $_wpdb->prepare( "INSERT INTO `{$table}` (date, path_id, visitors, pageviews) VALUES (%s, %d, %d, %d) ON DUPLICATE KEY UPDATE visitors = visitors + VALUES(visitors), pageviews = pageviews + VALUES(pageviews)", $date, $path_id, $visitors, $pageviews ) ); } /** * Get top pages for a date range. * * @param string $start_date Start date (Y-m-d). * @param string $end_date End date (Y-m-d). * @param int $limit Number of results. * * @return array * * @since 5.1.0 */ public static function get_top_pages( string $start_date, string $end_date, int $limit = 10 ): array { $_wpdb = static::get_connection(); $stats_table = self::get_table_name( $_wpdb ); $paths_table = Traffic_Paths_Entity::get_table_name( $_wpdb ); // Ensure both tables exist before running the JOIN. if ( ! Common_Table::check_table_exists( $stats_table, $_wpdb ) ) { self::create_table( $_wpdb ); } if ( ! Common_Table::check_table_exists( $paths_table, $_wpdb ) ) { Traffic_Paths_Entity::create_table( $_wpdb ); } // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared $sql = $_wpdb->prepare( "SELECT p.path, SUM(s.visitors) AS visitors, SUM(s.pageviews) AS pageviews FROM `{$stats_table}` s INNER JOIN `{$paths_table}` p ON s.path_id = p.id WHERE s.date BETWEEN %s AND %s GROUP BY s.path_id ORDER BY pageviews DESC LIMIT %d", $start_date, $end_date, $limit ); $_wpdb->suppress_errors( true ); $results = $_wpdb->get_results( $sql, ARRAY_A ); if ( '' !== $_wpdb->last_error ) { if ( 1146 === self::get_last_sql_error( $_wpdb ) ) { self::create_table( $_wpdb ); Traffic_Paths_Entity::create_table( $_wpdb ); self::log_table_recreated( $stats_table ); $results = array(); } } $_wpdb->suppress_errors( false ); return $results ? $results : array(); } /** * Get top pages grouped by base path (without query parameters). * * Returns an array of base paths with aggregated stats and a list of * individual path+query variations under each. * * @param string $start_date Start date (Y-m-d). * @param string $end_date End date (Y-m-d). * @param int $limit Number of top base paths to return. * * @return array Array of [ base_path => string, visitors => int, pageviews => int, urls => [ [ path, visitors, pageviews ] ] ]. * * @since 5.1.0 */ public static function get_top_pages_grouped( string $start_date, string $end_date, int $limit = 10 ): array { $all = self::get_top_pages( $start_date, $end_date, 200 ); // Group by base path (strip query string). $groups = array(); foreach ( $all as $row ) { $base_path = $row['path']; $qpos = strpos( $base_path, '?' ); if ( false !== $qpos ) { $base_path = substr( $base_path, 0, $qpos ); } if ( ! isset( $groups[ $base_path ] ) ) { $groups[ $base_path ] = array( 'base_path' => $base_path, 'visitors' => 0, 'pageviews' => 0, 'urls' => array(), ); } $groups[ $base_path ]['visitors'] += (int) $row['visitors']; $groups[ $base_path ]['pageviews'] += (int) $row['pageviews']; $groups[ $base_path ]['urls'][] = array( 'path' => $row['path'], 'visitors' => (int) $row['visitors'], 'pageviews' => (int) $row['pageviews'], ); } // Sort by pageviews descending. usort( $groups, function ( $a, $b ) { return $b['pageviews'] - $a['pageviews']; } ); return array_slice( $groups, 0, $limit ); } } }