'string', 'tech_id' => 'int', 'pageviews' => 'int', 'visitors' => 'int', ); /** * Holds all the default values for the columns. * * @var array * * @since 5.1.0 */ protected static $fields_values = array( 'date' => '', 'tech_id' => 0, 'pageviews' => 0, 'visitors' => 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, tech_id INT UNSIGNED NOT NULL, pageviews INT UNSIGNED NOT NULL DEFAULT 0, visitors INT UNSIGNED NOT NULL DEFAULT 0, PRIMARY KEY (date, tech_id), KEY `tech_id` (`tech_id`) ) ENGINE=InnoDB ' . $collate . ';'; return self::maybe_create_table( $table_name, $wp_entity_sql, $connection ); } /** * Upsert tech stats for a given date and tech combo. * * @param string $date Date in Y-m-d format. * @param int $tech_id Tech ID from Traffic_Tech_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 $tech_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, tech_id, pageviews, visitors) VALUES (%s, %d, %d, %d) ON DUPLICATE KEY UPDATE pageviews = pageviews + VALUES(pageviews), visitors = visitors + VALUES(visitors)", $date, $tech_id, $pageviews, $visitors ) ); } /** * Get top browsers 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_browsers( string $start_date, string $end_date, int $limit = 10 ): array { $_wpdb = static::get_connection(); $stats_table = self::get_table_name( $_wpdb ); $tech_table = Traffic_Tech_Entity::get_table_name( $_wpdb ); if ( ! Common_Table::check_table_exists( $stats_table, $_wpdb ) ) { self::create_table( $_wpdb ); } if ( ! Common_Table::check_table_exists( $tech_table, $_wpdb ) ) { Traffic_Tech_Entity::create_table( $_wpdb ); } // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared $sql = $_wpdb->prepare( "SELECT t.browser AS name, SUM(s.visitors) AS visitors, SUM(s.pageviews) AS pageviews FROM `{$stats_table}` s INNER JOIN `{$tech_table}` t ON s.tech_id = t.id WHERE s.date BETWEEN %s AND %s GROUP BY t.browser 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_Tech_Entity::create_table( $_wpdb ); self::log_table_recreated( $stats_table ); $results = array(); } } $_wpdb->suppress_errors( false ); return $results ? $results : array(); } /** * Get top operating systems 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_os( string $start_date, string $end_date, int $limit = 10 ): array { $_wpdb = static::get_connection(); $stats_table = self::get_table_name( $_wpdb ); $tech_table = Traffic_Tech_Entity::get_table_name( $_wpdb ); if ( ! Common_Table::check_table_exists( $stats_table, $_wpdb ) ) { self::create_table( $_wpdb ); } if ( ! Common_Table::check_table_exists( $tech_table, $_wpdb ) ) { Traffic_Tech_Entity::create_table( $_wpdb ); } // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared $sql = $_wpdb->prepare( "SELECT t.os AS name, SUM(s.visitors) AS visitors, SUM(s.pageviews) AS pageviews FROM `{$stats_table}` s INNER JOIN `{$tech_table}` t ON s.tech_id = t.id WHERE s.date BETWEEN %s AND %s GROUP BY t.os 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_Tech_Entity::create_table( $_wpdb ); self::log_table_recreated( $stats_table ); $results = array(); } } $_wpdb->suppress_errors( false ); return $results ? $results : array(); } /** * Get device type breakdown for a date range. * * @param string $start_date Start date (Y-m-d). * @param string $end_date End date (Y-m-d). * * @return array * * @since 5.1.0 */ public static function get_device_breakdown( string $start_date, string $end_date ): array { $_wpdb = static::get_connection(); $stats_table = self::get_table_name( $_wpdb ); $tech_table = Traffic_Tech_Entity::get_table_name( $_wpdb ); if ( ! Common_Table::check_table_exists( $stats_table, $_wpdb ) ) { self::create_table( $_wpdb ); } if ( ! Common_Table::check_table_exists( $tech_table, $_wpdb ) ) { Traffic_Tech_Entity::create_table( $_wpdb ); } // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared $sql = $_wpdb->prepare( "SELECT t.device_type AS name, SUM(s.visitors) AS visitors, SUM(s.pageviews) AS pageviews FROM `{$stats_table}` s INNER JOIN `{$tech_table}` t ON s.tech_id = t.id WHERE s.date BETWEEN %s AND %s GROUP BY t.device_type ORDER BY pageviews DESC", $start_date, $end_date ); $_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_Tech_Entity::create_table( $_wpdb ); self::log_table_recreated( $stats_table ); $results = array(); } } $_wpdb->suppress_errors( false ); return $results ? $results : array(); } } }