prefix . 'tracking_campaigns'; $charset_collate = $wpdb->get_charset_collate(); // Estrutura: ID, Source, Campaign, Timestamp, Registo. // Usamos dbDelta para garantir que a tabela existe sem apagar dados. $sql = "CREATE TABLE $table_name ( id BIGINT(20) NOT NULL AUTO_INCREMENT, utm_source VARCHAR(100) DEFAULT '', utm_campaign VARCHAR(100) DEFAULT '', post_timestamp BIGINT(20) DEFAULT 0, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id) ) $charset_collate;"; require_once(ABSPATH . 'wp-admin/includes/upgrade.php'); dbDelta($sql); } register_activation_hook(__FILE__, 'tc_criar_tabela'); // 4. CAPTURA DE DADOS (UTMs) function tc_guardar_utm_campaign() { if (!is_admin()) { global $wpdb; // Sanitização rigorosa $utm_source = isset($_GET['utm_source']) ? sanitize_text_field($_GET['utm_source']) : ''; $utm_campaign = isset($_GET['utm_campaign']) ? sanitize_text_field($_GET['utm_campaign']) : ''; $post_timestamp = isset($_GET['post_ts']) ? intval($_GET['post_ts']) : 0; // Se o timestamp for inválido, usa o atual if ($post_timestamp < 946684800 || $post_timestamp > 4102444800) { $post_timestamp = current_time('timestamp'); } if (!empty($utm_source) || !empty($utm_campaign)) { $table_name = $wpdb->prefix . 'tracking_campaigns'; $wpdb->insert( $table_name, [ 'utm_source' => $utm_source, 'utm_campaign' => $utm_campaign, 'post_timestamp' => $post_timestamp, 'created_at' => current_time('mysql'), ] ); } } } add_action('template_redirect', 'tc_guardar_utm_campaign'); // 5. CARREGAMENTO DE SCRIPTS (CHART.JS) function tc_enqueue_admin_scripts($hook) { // Apenas carrega na página do plugin if ($hook != 'toplevel_page_tracking-campaigns') { return; } wp_enqueue_script('chartjs', 'https://cdn.jsdelivr.net/npm/chart.js', array(), '4.4.0', true); } add_action('admin_enqueue_scripts', 'tc_enqueue_admin_scripts'); // 6. ADICIONAR AO MENU LATERAL function tc_tracking_menu() { add_menu_page( 'Tracking Campanhas', 'Tracking Campanhas', 'manage_options', 'tracking-campaigns', 'tc_pagina_dashboard_html', 'dashicons-chart-bar', 20 ); } add_action('admin_menu', 'tc_tracking_menu'); // 7. EXPORTAÇÃO PARA EXCEL (CSV) function tc_exportar_csv() { if (isset($_GET['page']) && $_GET['page'] == 'tracking-campaigns' && isset($_POST['exportar_excel'])) { global $wpdb; $table_name = $wpdb->prefix . 'tracking_campaigns'; $resultados = $wpdb->get_results("SELECT * FROM $table_name ORDER BY id DESC"); if ($resultados) { ob_clean(); header('Content-Type: text/csv; charset=utf-8'); header('Content-Disposition: attachment; filename=tracking_campaigns_WP_' . date('Y-m-d') . '.csv'); $saida = fopen('php://output', 'w'); fprintf($saida, chr(0xEF) . chr(0xBB) . chr(0xBF)); // BOM para Excel fputcsv($saida, array('ID', 'Source', 'Campaign', 'Post Date', 'Data Registo'), ';'); foreach ($resultados as $row) { $post_date = (!empty($row->post_timestamp)) ? date('Y-m-d H:i:s', $row->post_timestamp) : '-'; fputcsv($saida, array( $row->id, (!empty($row->utm_source) ? $row->utm_source : '-'), (!empty($row->utm_campaign) ? $row->utm_campaign : '-'), $post_date, $row->created_at ), ';'); } fclose($saida); exit; } } } add_action('admin_init', 'tc_exportar_csv'); // 8. CONTEÚDO DO DASHBOARD if (!function_exists('tc_pagina_dashboard_html')) { function tc_pagina_dashboard_html() { global $wpdb; $table_name = $wpdb->prefix . 'tracking_campaigns'; // 1. Processar Eliminação Total if (isset($_POST['tc_delete_all_records'])) { check_admin_referer('tc_delete_all_nonce_action', 'tc_delete_all_nonce_field'); $wpdb->query("TRUNCATE TABLE $table_name"); echo '

Todos os registos foram eliminados!

'; } // 2. Processar Eliminação Individual if (isset($_GET['delete_id'])) { $wpdb->delete($table_name, array('id' => intval($_GET['delete_id']))); echo '

Registo eliminado com sucesso.

'; } // 3. Obter Dados $resultados = $wpdb->get_results("SELECT * FROM $table_name ORDER BY id DESC LIMIT 200"); $unique_sources = $wpdb->get_col("SELECT DISTINCT utm_source FROM $table_name WHERE utm_source != ''"); $unique_campaigns = $wpdb->get_col("SELECT DISTINCT utm_campaign FROM $table_name WHERE utm_campaign != ''"); ?>

🚀 Tracking de Campanhas

até

Origens

Campanhas

Listagem de Registos

ID Origem Campanha Data Post Data Registo Ações
#id; ?> utm_source ?: 'Direto'); ?> utm_campaign ?: '-'); ?> post_timestamp ? date('Y-m-d H:i', $row->post_timestamp) : '-'; ?> created_at; ?>
Sem registos.
prefix . 'tracking_campaigns'; $start_date = isset($_POST['start_date']) ? sanitize_text_field($_POST['start_date']) : ''; $end_date = isset($_POST['end_date']) ? sanitize_text_field($_POST['end_date']) : ''; // Decodificar arrays JSON $sources = isset($_POST['sources']) ? json_decode(stripslashes($_POST['sources'])) : []; $campaigns = isset($_POST['campaigns']) ? json_decode(stripslashes($_POST['campaigns'])) : []; $where = ["1=1"]; if ($start_date) $where[] = $wpdb->prepare("DATE(created_at) >= %s", $start_date); if ($end_date) $where[] = $wpdb->prepare("DATE(created_at) <= %s", $end_date); if (!empty($sources)) { $source_placeholders = implode(',', array_fill(0, count($sources), '%s')); $where[] = $wpdb->prepare("utm_source IN ($source_placeholders)", $sources); } if (!empty($campaigns)) { $campaign_placeholders = implode(',', array_fill(0, count($campaigns), '%s')); $where[] = $wpdb->prepare("utm_campaign IN ($campaign_placeholders)", $campaigns); } $where_clause = implode(' AND ', $where); // Dados para Gráficos $sources_stats = $wpdb->get_results("SELECT utm_source, COUNT(*) as count FROM $table_name WHERE $where_clause GROUP BY utm_source"); $campaigns_stats = $wpdb->get_results("SELECT utm_campaign, COUNT(*) as count FROM $table_name WHERE $where_clause GROUP BY utm_campaign"); $resp_sources = ['labels' => [], 'data' => []]; foreach ($sources_stats as $s) { $resp_sources['labels'][] = empty($s->utm_source) ? 'Direto/Sem UTM' : $s->utm_source; $resp_sources['data'][] = (int)$s->count; } $resp_campaigns = ['labels' => [], 'data' => []]; foreach ($campaigns_stats as $c) { $resp_campaigns['labels'][] = empty($c->utm_campaign) ? 'Nenhuma' : $c->utm_campaign; $resp_campaigns['data'][] = (int)$c->count; } // Dados para Tabela (HTML) $resultados = $wpdb->get_results("SELECT * FROM $table_name WHERE $where_clause ORDER BY id DESC LIMIT 100"); $table_html = ''; if ($resultados) { foreach ($resultados as $row) { $post_date = (!empty($row->post_timestamp)) ? date('Y-m-d H:i:s', $row->post_timestamp) : '-'; $utm_source = !empty($row->utm_source) ? $row->utm_source : 'Direto'; $table_html .= ''; $table_html .= '#' . esc_html($row->id) . ''; $table_html .= '' . esc_html($utm_source) . ''; $table_html .= '' . esc_html(!empty($row->utm_campaign) ? $row->utm_campaign : '-') . ''; $table_html .= '' . esc_html($post_date) . ''; $table_html .= '' . esc_html($row->created_at) . ''; $table_html .= ''; $table_html .= ''; } } else { $table_html = 'Sem dados para os filtros selecionados.'; } wp_send_json_success([ 'sources' => $resp_sources, 'campaigns' => $resp_campaigns, 'table_html' => $table_html ]); } add_action('wp_ajax_tc_filter_data', 'tc_filter_data_ajax_handler');