get_results("SELECT * FROM {$table_name} ORDER BY click_date DESC, id DESC"); $filename = 'ailm_click_stats_' . gmdate('Y-m-d_H-i-s') . '.csv'; header('Content-Type: text/csv; charset=utf-8'); header('Content-Disposition: attachment; filename="' . $filename . '"'); $output = fopen('php://output', 'w'); // BOM for Excel UTF-8 compatibility fprintf($output, "\xEF\xBB\xBF"); fputcsv($output, [ __('Date', 'ailinkmaster-internal-auto-manager'), __('Type', 'ailinkmaster-internal-auto-manager'), __('Source Article (Donor)', 'ailinkmaster-internal-auto-manager'), __('Target URL', 'ailinkmaster-internal-auto-manager'), __('Anchor Text', 'ailinkmaster-internal-auto-manager'), __('Clicks', 'ailinkmaster-internal-auto-manager'), ]); foreach ($all_rows as $row) { $post_title = get_the_title($row->post_id); $type_label = $row->link_type; if ($row->link_type === 'sponsored') { $type_label = __('Sponsored', 'ailinkmaster-internal-auto-manager'); } elseif ($row->link_type === 'external') { $type_label = __('Outdoor', 'ailinkmaster-internal-auto-manager'); } elseif ($row->link_type === 'internal') { $type_label = __('Internal', 'ailinkmaster-internal-auto-manager'); } fputcsv($output, [ $row->click_date, $type_label, $post_title ? $post_title : sprintf( /* translators: %d: post ID */ __('(ID: %d)', 'ailinkmaster-internal-auto-manager'), $row->post_id ), $row->target_url, $row->anchor, $row->click_count, ]); } // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fclose fclose($output); exit; } private function get_sort_link($orderby_val, $current_orderby, $current_order) { $order = 'desc'; if ($orderby_val === $current_orderby) { $order = (strtolower($current_order) === 'desc') ? 'asc' : 'desc'; } return add_query_arg(['orderby' => $orderby_val, 'order' => $order]); } public function render_tab() { global $wpdb; // Include DB class if it is not already included (just in case) if (!class_exists('AILM_Clicks_DB')) { require_once plugin_dir_path(__FILE__) . 'class-ailm-clicks-db.php'; } $table_name = esc_sql(AILM_Clicks_DB::get_table_name()); // DB Auto-migration: ensure link_type column exists and unique_stat includes anchor. // Table name is safely built via esc_sql() from $wpdb->prefix. // DDL statements cannot use $wpdb->prepare() for table names. // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared $has_col = $wpdb->get_results("SHOW COLUMNS FROM {$table_name} LIKE 'link_type'"); if (empty($has_col)) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.SchemaChange $wpdb->query("ALTER TABLE {$table_name} ADD COLUMN link_type varchar(20) NOT NULL DEFAULT 'internal'"); } // Ensure unique_stat includes anchor for per-anchor tracking. // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared $has_anchor_in_index = $wpdb->get_results("SHOW INDEX FROM {$table_name} WHERE Key_name = 'unique_stat' AND Column_name = 'anchor'"); if (empty($has_anchor_in_index)) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.SchemaChange $wpdb->query("ALTER TABLE {$table_name} DROP INDEX unique_stat"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.SchemaChange $wpdb->query("ALTER TABLE {$table_name} ADD UNIQUE KEY unique_stat (post_id, target_url(190), anchor(190), click_date, link_type)"); } // --- Clear Statistics --- if (isset($_POST['ailm_clear_clicks'])) { check_admin_referer('ailm_clicks_action'); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching $wpdb->query("TRUNCATE TABLE {$table_name}"); echo '

' . esc_html__('Statistics cleared successfully!', 'ailinkmaster-internal-auto-manager') . '

'; } // --- Pagination and Sorting --- $allowed_per_page = [20, 50, 100]; // phpcs:ignore WordPress.Security.NonceVerification.Recommended $per_page = isset($_GET['per_page']) ? intval($_GET['per_page']) : 20; if (!in_array($per_page, $allowed_per_page, true)) { $per_page = 20; } // phpcs:ignore WordPress.Security.NonceVerification.Recommended $paged = isset($_GET['paged']) ? max(1, intval($_GET['paged'])) : 1; $offset = ($paged - 1) * $per_page; // phpcs:ignore WordPress.Security.NonceVerification.Recommended $orderby = isset($_GET['orderby']) ? sanitize_text_field(wp_unslash($_GET['orderby'])) : 'click_date'; // phpcs:ignore WordPress.Security.NonceVerification.Recommended $order = isset($_GET['order']) ? sanitize_text_field(wp_unslash($_GET['order'])) : 'DESC'; // Whitelist for orderby $allowed_orderby = ['click_date', 'post_id', 'target_url', 'anchor', 'click_count', 'link_type']; if (!in_array($orderby, $allowed_orderby, true)) { $orderby = 'click_date'; } $order = strtoupper($order); if ('ASC' !== $order && 'DESC' !== $order) { $order = 'DESC'; } // --- Queries --- // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching $total_items = $wpdb->get_var("SELECT COUNT(*) FROM {$table_name}"); $total_pages = ceil($total_items / $per_page); // Safe query construction $orderby_sql = esc_sql($orderby); $order_sql = esc_sql($order); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter $items = $wpdb->get_results($wpdb->prepare("SELECT * FROM {$table_name} ORDER BY {$orderby_sql} {$order_sql}, id DESC LIMIT %d OFFSET %d", $per_page, $offset)); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching $total_clicks = (int) $wpdb->get_var("SELECT SUM(click_count) FROM {$table_name}"); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching $internal_clicks = (int) $wpdb->get_var("SELECT SUM(click_count) FROM {$table_name} WHERE link_type = 'internal'"); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching $external_clicks = (int) $wpdb->get_var("SELECT SUM(click_count) FROM {$table_name} WHERE link_type = 'external'"); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching $sponsored_clicks = (int) $wpdb->get_var("SELECT SUM(click_count) FROM {$table_name} WHERE link_type = 'sponsored'"); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching $unique_links = (int) $wpdb->get_var("SELECT COUNT(*) FROM {$table_name}"); // --- HTML --- ?>

$value) { if ($key === 'per_page') { continue; } echo ''; } ?>
1): ?>
post_id); $post_link = get_edit_post_link($item->post_id); $post_view = get_permalink($item->post_id); ?>
click_date); ?> link_type === 'sponsored'): ?> link_type === 'external'): ?> post_id )); ?>
|
target_url); echo esc_html($display_url === '' ? '/' : $display_url); ?> anchor); ?> click_count)); ?>
1): ?>
add_query_arg(['paged' => '%#%', 'per_page' => $per_page]), 'format' => '', 'prev_text' => '« ' . __('Prev', 'ailinkmaster-internal-auto-manager'), 'next_text' => __('Next', 'ailinkmaster-internal-auto-manager') . ' »', 'total' => $total_pages, 'current' => $paged, 'type' => 'plain' ])); ?>