opaolilo/sqlite-data-viz-delight
0
1const expectedStatus = [2 'PENDENTE',3 'ERRO',4 'ENVIADO',5 'PROCESSANDO',6 'AGUARDANDO_RETRY',7 'ERRO_DETECTOR_INFO',8 'PROCESSANDO_DETECTOR_INFO',9 'VALE_ERROR'10];11let SQL;12let dbFileUint8 = null; // Arquivo carregado em Uint8Array13let barChart, pieChart, lineChart;14let selectedFileName = '';15let refreshInterval = null;16let activeRefreshInterval = 0;17// Inicializa SQL.js18async function initSqlJsLibrary() {19 SQL = await initSqlJs({20 locateFile: filename => `https://cdnjs.cloudflare.com/ajax/libs/sql.js/1.8.0/${filename}`21 });22}23function loadDatabaseFromUint8Array(uint8Array) {24 try {25 const db = new SQL.Database(uint8Array);26 27 // Query for status counts28 const statusQuery = `29 SELECT status_api, COUNT(*) as total30 FROM eventos31 WHERE status_api IN (${expectedStatus.map(s => `'${s}'`).join(', ')})32 GROUP BY status_api33 `;34 35 // Query for status counts per date36 const dateStatusQuery = `37 SELECT 38 strftime('%m-%d', created_at) AS month_day,39 status_api,40 COUNT(*) AS total41 FROM eventos42 GROUP BY month_day, status_api43 `;44 const sourceQuery = `45 SELECT 46 strftime('%m-%d', created_at) AS month_day,47 source_id,48 COUNT(*) AS total49 FROM eventos50 GROUP BY month_day, source_id51 `;52 53 const sourceCountQuery = `54 SELECT source_id, COUNT(*) as total55 FROM eventos56 GROUP BY source_id57 `;58 59 const res = db.exec(statusQuery);60 const dateStatusRes = db.exec(dateStatusQuery);61 const sourceRes = db.exec(sourceQuery);62 const sourceCountRes = db.exec(sourceCountQuery);63// Inicializa com zero para todos os status esperados64 const statusCount = {};65 expectedStatus.forEach(s => statusCount[s] = 0);66 67 if (res.length > 0) {68 res[0].values.forEach(([status, total]) => {69 statusCount[status] = total;70 });71 }72 73 const labels = expectedStatus;74 const data = labels.map(status => statusCount[status]);75 renderCharts(labels, data);76 updateStats(statusCount);77 renderDateStatusChart(dateStatusRes);78 renderSourceTrendChart(sourceRes);79 renderSourceCountChart(sourceCountRes);80document.getElementById('filename').textContent = `Loaded: ${selectedFileName}`;81} catch (error) {82 console.error('Error processing database:', error);83 document.getElementById('filename').textContent = `Error: ${error.message}`;84 }85}86 87function renderCharts(labels, data) {88 // Tooltip styling89 const tooltip = {90 enabled: true,91 backgroundColor: 'rgb(30, 41, 59)',92 titleFont: { size: 14, weight: 'bold' },93 bodyFont: { size: 12 },94 padding: 10,95 cornerRadius: 6,96 displayColors: false,97 callbacks: {98 label: function(context) {99 return `${context.label}: ${context.raw}`;100 }101 }102 };103 104 // Se já existir algum chart, destrói antes de criar novos para evitar sobreposição105 if (barChart) barChart.destroy();106 if (pieChart) pieChart.destroy();107 if (lineChart) lineChart.destroy();108 109 // Bar Chart110 barChart = new Chart(document.getElementById('barChart'), {111 type: 'bar',112 data: {113 labels: labels,114 datasets: [{115 label: 'Count',116 data: data,117 backgroundColor: '#6366f1',118 borderColor: '#4f46e5',119 borderWidth: 1,120 borderRadius: 6,121 hoverBackgroundColor: '#4f46e5'122 }]123 },124 options: {125 responsive: true,126 maintainAspectRatio: false,127 plugins: {128 legend: { display: false },129 tooltip: tooltip130 },131 scales: {132 y: { 133 beginAtZero: true,134 grid: { color: 'rgba(255, 255, 255, 0.1)' },135 ticks: { color: 'rgba(255, 255, 255, 0.7)' }136 },137 x: { 138 grid: { display: false },139 ticks: { color: 'rgba(255, 255, 255, 0.7)' }140 }141 }142 }143 });144 145 // Pie Chart146 pieChart = new Chart(document.getElementById('pieChart'), {147 type: 'doughnut',148 data: {149 labels: labels,150 datasets: [{151 data: data,152 backgroundColor: [153 '#6366f1',154 '#ec4899',155 '#f59e0b',156 '#10b981',157 '#3b82f6',158 '#f97316',159 '#8b5cf6',160 '#ef4444'161 ],162 borderColor: 'rgba(17, 24, 39, 0.5)',163 borderWidth: 1,164 hoverOffset: 8165 }]166 },167 options: {168 responsive: true,169 maintainAspectRatio: false,170 plugins: {171 legend: {172 position: 'right',173 labels: { color: 'rgba(255, 255, 255, 0.7)' }174 },175 tooltip: tooltip176 },177 cutout: '65%'178 }179 });180 181 // Line Chart182 lineChart = new Chart(document.getElementById('lineChart'), {183 type: 'line',184 data: {185 labels: labels,186 datasets: [{187 label: 'Trend',188 data: data,189 fill: false,190 borderColor: '#f59e0b',191 backgroundColor: '#f59e0b',192 tension: 0.4,193 pointBackgroundColor: '#fff',194 pointBorderColor: '#f59e0b',195 pointBorderWidth: 2,196 pointRadius: 5,197 pointHoverRadius: 7198 }]199 },200 options: {201 responsive: true,202 maintainAspectRatio: false,203 plugins: {204 legend: { display: false },205 tooltip: tooltip206 },207 scales: {208 y: { 209 beginAtZero: true,210 grid: { color: 'rgba(255, 255, 255, 0.1)' },211 ticks: { color: 'rgba(255, 255, 255, 0.7)' }212 },213 x: { 214 grid: { display: false },215 ticks: { color: 'rgba(255, 255, 255, 0.7)' }216 }217 }218 }219 });220}221 222function updateStats(statusCount) {223 const statsContainer = document.getElementById('statsContainer');224 const statsGrid = statsContainer.querySelector('.grid');225 226 // Clear previous stats227 statsGrid.innerHTML = '';228 229 // Calculate total230 const total = Object.values(statusCount).reduce((sum, count) => sum + count, 0);231 232 // Add total stat233 statsGrid.appendChild(createStatCard('Total Events', total, 'database', 'bg-indigo-500'));234 235 // Add stats for each status236 Object.entries(statusCount).forEach(([status, count]) => {237 const icon = getIconForStatus(status);238 const color = getColorForStatus(status);239 statsGrid.appendChild(createStatCard(status, count, icon, color));240 });241 242 // Show container243 statsContainer.classList.remove('hidden');244 statsContainer.classList.add('fade-in');245}246 247function createStatCard(title, value, icon, bgColor) {248 const card = document.createElement('div');249 card.className = 'stat-card bg-gray-800 rounded-xl p-6 shadow-lg flex items-center gap-4';250 251 const iconDiv = document.createElement('div');252 iconDiv.className = `p-3 rounded-lg ${bgColor} text-white`;253 iconDiv.innerHTML = `<i data-feather="${icon}" class="w-5 h-5"></i>`;254 255 const contentDiv = document.createElement('div');256 contentDiv.className = 'flex-1';257 258 const titleP = document.createElement('p');259 titleP.className = 'text-sm text-gray-400';260 titleP.textContent = title;261 262 const valueP = document.createElement('p');263 valueP.className = 'text-2xl font-bold text-white';264 valueP.textContent = value;265 266 contentDiv.appendChild(titleP);267 contentDiv.appendChild(valueP);268 269 card.appendChild(iconDiv);270 card.appendChild(contentDiv);271 272 feather.replace({ width: 20, height: 20 });273 274 return card;275}276 277function getIconForStatus(status) {278 const icons = {279 'PENDENTE': 'clock',280 'ERRO': 'alert-circle',281 'ENVIADO': 'send',282 'PROCESSANDO': 'loader',283 'AGUARDANDO_RETRY': 'rotate-cw',284 'ERRO_DETECTOR_INFO': 'alert-triangle',285 'PROCESSANDO_DETECTOR_INFO': 'cpu',286 'VALE_ERROR': 'x-octagon'287 };288 return icons[status] || 'help-circle';289}290function renderSourceTrendChart(queryResult) {291 if (queryResult.length === 0 || !queryResult[0].values.length) return;292 293 // Create container if it doesn't exist294 let chartContainer = document.getElementById('sourceTrendChartContainer');295 if (!chartContainer) {296 chartContainer = document.createElement('div');297 chartContainer.id = 'sourceTrendChartContainer';298 chartContainer.className = 'bg-gray-800 rounded-xl p-6 shadow-lg mt-8';299 document.querySelector('.container').appendChild(chartContainer);300 }301 302 // Clear previous content303 chartContainer.innerHTML = `304 <div class="flex items-center justify-between mb-4">305 <h3 class="text-lg font-semibold text-gray-200">Events by Source ID</h3>306 </div>307 <div class="h-80">308 <canvas id="sourceTrendChart"></canvas>309 </div>310 `;311 312 const rows = queryResult[0].values;313 const dates = [...new Set(rows.map(row => row[0]))].sort();314 const sources = [...new Set(rows.map(row => row[1]))];315 316 // Group data by source317 const datasets = sources.map(source => {318 const sourceData = dates.map(date => {319 const match = rows.find(r => r[0] === date && r[1] === source);320 return match ? match[2] : 0;321 });322 323 return {324 label: `Source ${source}`,325 data: sourceData,326 borderColor: getRandomColor(),327 backgroundColor: getRandomColor(),328 tension: 0.4,329 borderWidth: 2,330 pointRadius: 3,331 pointHoverRadius: 5332 };333 });334 335 // Create chart336 new Chart(document.getElementById('sourceTrendChart'), {337 type: 'line',338 data: {339 labels: dates,340 datasets: datasets341 },342 options: {343 responsive: true,344 maintainAspectRatio: false,345 plugins: {346 legend: {347 position: 'right',348 labels: { color: 'rgba(255, 255, 255, 0.7)' }349 },350 tooltip: {351 enabled: true,352 backgroundColor: 'rgb(30, 41, 59)',353 titleFont: { size: 14, weight: 'bold' },354 bodyFont: { size: 12 },355 padding: 10,356 cornerRadius: 6,357 callbacks: {358 label: function(context) {359 return `${context.dataset.label}: ${context.raw}`;360 }361 }362 }363 },364 scales: {365 y: { 366 beginAtZero: true,367 grid: { color: 'rgba(255, 255, 255, 0.1)' },368 ticks: { color: 'rgba(255, 255, 255, 0.7)' }369 },370 x: { 371 grid: { display: false },372 ticks: { color: 'rgba(255, 255, 255, 0.7)' }373 }374 }375 }376 });377}378 379function renderDateStatusChart(queryResult) {380if (queryResult.length === 0 || !queryResult[0].values.length) return;381 382 // Create container if it doesn't exist383 let chartContainer = document.getElementById('dateStatusChartContainer');384 if (!chartContainer) {385 chartContainer = document.createElement('div');386 chartContainer.id = 'dateStatusChartContainer';387 chartContainer.className = 'bg-gray-800 rounded-xl p-6 shadow-lg mt-8';388 document.querySelector('.container').appendChild(chartContainer);389 }390 391 // Clear previous content392 chartContainer.innerHTML = `393 <div class="flex items-center justify-between mb-4">394 <h3 class="text-lg font-semibold text-gray-200">Status Count by Date</h3>395 <div class="flex items-center gap-2"></div>396</div>397 <div class="h-80">398 <canvas id="dateStatusChart"></canvas>399 </div>400 `;401 402 const rows = queryResult[0].values;403 const dates = [...new Set(rows.map(row => row[0]))].sort();404 const statuses = [...new Set(rows.map(row => row[1]))];405 406 // Group data by status407 const datasets = expectedStatus.map(status => {408 const statusData = dates.map(date => {409 const match = rows.find(r => r[0] === date && r[1] === status);410 return match ? match[2] : 0;411 });412 413 return {414 label: status,415 data: statusData,416 borderColor: getChartColor(status),417 backgroundColor: getChartColor(status),418 tension: 0.4,419 borderWidth: 2,420 pointRadius: 3,421 pointHoverRadius: 5422 };423 });424 425 // Tooltip styling426 const tooltip = {427 enabled: true,428 backgroundColor: 'rgb(30, 41, 59)',429 titleFont: { size: 14, weight: 'bold' },430 bodyFont: { size: 12 },431 padding: 10,432 cornerRadius: 6,433 callbacks: {434 label: function(context) {435 return `${context.dataset.label}: ${context.raw}`;436 }437 }438 };439 440 // Create chart441 new Chart(document.getElementById('dateStatusChart'), {442 type: 'line',443 data: {444 labels: dates,445 datasets: datasets446 },447 options: {448 responsive: true,449 maintainAspectRatio: false,450 plugins: {451 legend: {452 position: 'right',453 labels: { color: 'rgba(255, 255, 255, 0.7)' }454 },455 tooltip: tooltip456 },457 scales: {458 y: { 459 beginAtZero: true,460 grid: { color: 'rgba(255, 255, 255, 0.1)' },461 ticks: { color: 'rgba(255, 255, 255, 0.7)' }462 },463 x: { 464 grid: { display: false },465 ticks: { color: 'rgba(255, 255, 255, 0.7)' }466 }467 }468 }469 });470}471 472function getChartColor(status) {473 const colors = {474 'PENDENTE': '#6366f1',475 'ERRO': '#ec4899',476 'ENVIADO': '#10b981',477 'PROCESSANDO': '#f59e0b',478 'AGUARDANDO_RETRY': '#f97316',479 'ERRO_DETECTOR_INFO': '#8b5cf6',480 'PROCESSANDO_DETECTOR_INFO': '#06b6d4',481 'VALE_ERROR': '#ef4444'482 };483 return colors[status] || '#9ca3af';484}485function getRandomColor() {486 const colors = [487 '#6366f1', '#ec4899', '#f59e0b', '#10b981', '#3b82f6',488 '#f97316', '#8b5cf6', '#ef4444', '#84cc16', '#06b6d4'489 ];490 return colors[Math.floor(Math.random() * colors.length)];491}492function renderSourceCountChart(queryResult) {493 if (queryResult.length === 0 || !queryResult[0].values.length) return;494 495 // Create container if it doesn't exist496 let chartContainer = document.getElementById('sourceCountChartContainer');497 if (!chartContainer) {498 chartContainer = document.createElement('div');499 chartContainer.id = 'sourceCountChartContainer';500 chartContainer.className = 'bg-gray-800 rounded-xl p-6 shadow-lg mt-8';501 document.querySelector('.container').appendChild(chartContainer);502 }503 504 // Clear previous content505 chartContainer.innerHTML = `506 <div class="flex items-center justify-between mb-4">507 <h3 class="text-lg font-semibold text-gray-200">Events Count by Source ID</h3>508 </div>509 <div class="h-80">510 <canvas id="sourceCountChart"></canvas>511 </div>512 `;513 514 const rows = queryResult[0].values;515 const labels = rows.map(row => `Source ${row[0]}`);516 const data = rows.map(row => row[1]);517 const backgroundColors = rows.map(() => getRandomColor());518 519 // Create chart520 new Chart(document.getElementById('sourceCountChart'), {521 type: 'bar',522 data: {523 labels: labels,524 datasets: [{525 label: 'Events Count',526 data: data,527 backgroundColor: backgroundColors,528 borderColor: backgroundColors.map(color => color.replace('0.7', '1')),529 borderWidth: 1,530 borderRadius: 6531 }]532 },533 options: {534 responsive: true,535 maintainAspectRatio: false,536 plugins: {537 legend: { display: false },538 tooltip: {539 enabled: true,540 backgroundColor: 'rgb(30, 41, 59)',541 titleFont: { size: 14, weight: 'bold' },542 bodyFont: { size: 12 },543 padding: 10,544 cornerRadius: 6,545 displayColors: false,546 callbacks: {547 label: function(context) {548 return `Events: ${context.raw}`;549 }550 }551 }552 },553 scales: {554 y: { 555 beginAtZero: true,556 grid: { color: 'rgba(255, 255, 255, 0.1)' },557 ticks: { color: 'rgba(255, 255, 255, 0.7)' }558 },559 x: { 560 grid: { display: false },561 ticks: { 562 color: 'rgba(255, 255, 255, 0.7)',563 callback: function(value) {564 return this.getLabelForValue(value).replace('Source ', '');565 }566 }567 }568 }569 }570 });571}572 573function getColorForStatus(status) {574const colors = {575 'PENDENTE': 'bg-blue-500',576 'ERRO': 'bg-red-500',577 'ENVIADO': 'bg-green-500',578 'PROCESSANDO': 'bg-yellow-500',579 'AGUARDANDO_RETRY': 'bg-orange-500',580 'ERRO_DETECTOR_INFO': 'bg-purple-500',581 'PROCESSANDO_DETECTOR_INFO': 'bg-cyan-500',582 'VALE_ERROR': 'bg-pink-500'583 };584 return colors[status] || 'bg-gray-500';585}586function updateActiveNavLink() {587 const currentPath = window.location.pathname.split('/').pop() || 'index.html';588 const header = document.querySelector('custom-header');589 if (header) {590 const shadowRoot = header.shadowRoot;591 shadowRoot.querySelectorAll('.nav-link').forEach(link => {592 link.classList.remove('active');593 const linkPath = link.getAttribute('href').split('/').pop();594 if ((currentPath === 'index.html' && linkPath === '') || 595 (currentPath === linkPath)) {596 link.classList.add('active');597 }598 });599 }600}601 602window.onload = async () => {603 updateActiveNavLink();604await initSqlJsLibrary();605 606 const fileInput = document.getElementById('fileInput');607 const btnReload = document.getElementById('btnReload');608 609 fileInput.addEventListener('change', e => {610 const file = e.target.files[0];611 if (!file) {612 document.getElementById('filename').textContent = 'No file selected';613 btnReload.disabled = true;614 dbFileUint8 = null;615 return;616 }617 selectedFileName = file.name;618 const reader = new FileReader();619 reader.onload = () => {620 dbFileUint8 = new Uint8Array(reader.result);621 document.getElementById('filename').textContent = `Selected: ${selectedFileName}`;622 btnReload.disabled = false;623 loadDatabaseFromUint8Array(dbFileUint8);624 };625 reader.onerror = () => {626 document.getElementById('filename').textContent = 'Error reading file';627 btnReload.disabled = true;628 dbFileUint8 = null;629 };630 reader.readAsArrayBuffer(file);631 });632 btnReload.addEventListener('click', () => {633 if (dbFileUint8) {634 loadDatabaseFromUint8Array(dbFileUint8);635 }636 });637 638 // Auto refresh functionality639 const autoRefreshBtn = document.getElementById('autoRefreshBtn');640 const autoRefreshDropdown = document.getElementById('autoRefreshDropdown');641 642 autoRefreshBtn.addEventListener('click', () => {643 autoRefreshDropdown.classList.toggle('hidden');644 });645 646 // Close dropdown when clicking outside647 document.addEventListener('click', (e) => {648 if (!autoRefreshBtn.contains(e.target) && !autoRefreshDropdown.contains(e.target)) {649 autoRefreshDropdown.classList.add('hidden');650 }651 });652 653 // Handle interval selection654 autoRefreshDropdown.querySelectorAll('button').forEach(btn => {655 btn.addEventListener('click', (e) => {656 const interval = parseInt(e.target.dataset.interval);657 658 // Clear any existing interval659 if (refreshInterval) {660 clearInterval(refreshInterval);661 refreshInterval = null;662 }663 664 // Set new interval if not "Off"665 if (interval > 0 && dbFileUint8) {666 refreshInterval = setInterval(() => {667 loadDatabaseFromUint8Array(dbFileUint8);668 }, interval);669 activeRefreshInterval = interval;670 671 // Update button text672 autoRefreshBtn.innerHTML = `673 <i data-feather="clock" class="w-4 h-4"></i>674 <span>${interval === 5000 ? '5s' : interval === 15000 ? '15s' : '1m'}</span>675 <i data-feather="chevron-down" class="w-4 h-4"></i>676 `;677 feather.replace();678 } else {679 activeRefreshInterval = 0;680 autoRefreshBtn.innerHTML = `681 <i data-feather="clock" class="w-4 h-4"></i>682 <span>Auto Refresh</span>683 <i data-feather="chevron-down" class="w-4 h-4"></i>684 `;685 feather.replace();686 }687 688 autoRefreshDropdown.classList.add('hidden');689 });690 });691 692 // Start auto-refresh if previously set and file is loaded693 if (activeRefreshInterval > 0 && dbFileUint8) {694 refreshInterval = setInterval(() => {695 loadDatabaseFromUint8Array(dbFileUint8);696 }, activeRefreshInterval);697 }698};