} */ public static function totals( string $from, string $to ): array { global $wpdb; $daily = Ailens_Schema::daily_table(); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared $rows = $wpdb->get_results( $wpdb->prepare( "SELECT category, SUM(hits) AS hits FROM {$daily} WHERE summary_date BETWEEN %s AND %s GROUP BY category", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared $from, $to ) ); $by_category = array( 'training' => 0, 'search_index' => 0, 'user_fetch' => 0, ); $total = 0; foreach ( (array) $rows as $row ) { $by_category[ $row->category ] = (int) $row->hits; $total += (int) $row->hits; } return array( 'total' => $total, 'by_category' => $by_category, ); } /** * 日別×クローラー別の時系列(日次サマリから)。 * * @param string $from Y-m-d。 * @param string $to Y-m-d。 * @return array> date => (crawler_id => hits) */ public static function series( string $from, string $to ): array { global $wpdb; $daily = Ailens_Schema::daily_table(); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared $rows = $wpdb->get_results( $wpdb->prepare( "SELECT summary_date, crawler_id, hits FROM {$daily} WHERE summary_date BETWEEN %s AND %s ORDER BY summary_date", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared $from, $to ) ); $series = array(); foreach ( (array) $rows as $row ) { $series[ $row->summary_date ][ $row->crawler_id ] = (int) $row->hits; } return $series; } /** * クローラー別テーブル: 件数・verified率(サマリ)+最終訪問(hits範囲検索)。 * * @param string $from Y-m-d。 * @param string $to Y-m-d。 * @return array */ public static function crawler_rows( string $from, string $to ): array { global $wpdb; $daily = Ailens_Schema::daily_table(); $hits = Ailens_Schema::hits_table(); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared $rows = $wpdb->get_results( $wpdb->prepare( "SELECT crawler_id, SUM(hits) AS hits, SUM(verified_hits) AS verified_hits FROM {$daily} WHERE summary_date BETWEEN %s AND %s GROUP BY crawler_id ORDER BY hits DESC", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared $from, $to ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared $last_visits = $wpdb->get_results( $wpdb->prepare( "SELECT crawler_id, MAX(hit_at) AS last_visit FROM {$hits} WHERE hit_at >= %s GROUP BY crawler_id", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared $from . ' 00:00:00' ), OBJECT_K ); $result = array(); foreach ( (array) $rows as $row ) { $result[] = (object) array( 'crawler_id' => $row->crawler_id, 'hits' => (int) $row->hits, 'verified_hits' => (int) $row->verified_hits, 'last_visit' => isset( $last_visits[ $row->crawler_id ] ) ? $last_visits[ $row->crawler_id ]->last_visit : null, ); } return $result; } /** * 読まれたページTop10(hits範囲検索+上位パスのみ最多クローラーを追加取得)。 * * 「Top pages read by AI」の名のとおり、本文ページ(is_content_pathの単一定義) * のみを対象とする(2026-07-30: robots.txt/sitemap.xml等が混入していたのを修正。 * タイトルの主張と中身を一致させる)。 * * @param string $from Y-m-d。 * @return array */ public static function top_pages( string $from ): array { global $wpdb; $hits = Ailens_Schema::hits_table(); require_once AILENS_PLUGIN_DIR . 'includes/class-ailens-weekly-report.php'; // 本文判定はPHP側の単一定義のため、パス別集計を取得してからフィルタして // 上位10件に絞る(hitsはAIクローラーのみのテーブルでパス種類は限定的)。 // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared $all_pages = $wpdb->get_results( $wpdb->prepare( "SELECT url_path, COUNT(*) AS hits FROM {$hits} WHERE hit_at >= %s GROUP BY url_path ORDER BY hits DESC, url_path ASC", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared $from . ' 00:00:00' ) ); $pages = array(); foreach ( (array) $all_pages as $page ) { if ( Ailens_Weekly_Report::is_content_path( (string) $page->url_path ) ) { $pages[] = $page; } if ( count( $pages ) >= 10 ) { break; } } $result = array(); foreach ( (array) $pages as $page ) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared $top = $wpdb->get_var( $wpdb->prepare( "SELECT crawler_id FROM {$hits} WHERE hit_at >= %s AND url_path = %s GROUP BY crawler_id ORDER BY COUNT(*) DESC LIMIT 1", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared $from . ' 00:00:00', $page->url_path ) ); $result[] = (object) array( 'url_path' => $page->url_path, 'hits' => (int) $page->hits, 'top_crawler' => (string) $top, ); } return $result; } }