1250 lines
43 KiB
PHP
1250 lines
43 KiB
PHP
<?php
|
|
// filepath: v:\controls\test\HMI\index.php
|
|
include "../../session.php";
|
|
include "../../userAccess.php";
|
|
|
|
// Database connection
|
|
$servername = "192.168.0.13";
|
|
$username = "opce";
|
|
$password = "opcelasuca";
|
|
$dbname = "archive";
|
|
|
|
try {
|
|
$pdo = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
|
|
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
|
|
} catch (PDOException $e) {
|
|
if (isset($_POST['action'])) {
|
|
header('Content-Type: application/json');
|
|
echo json_encode(['success' => false, 'error' => 'Database connection failed: ' . $e->getMessage()]);
|
|
exit;
|
|
}
|
|
die("Connection failed: " . $e->getMessage());
|
|
}
|
|
|
|
// Handle AJAX requests
|
|
if (isset($_POST['action'])) {
|
|
// Prevent any output before JSON response
|
|
ob_clean();
|
|
header('Content-Type: application/json');
|
|
|
|
try {
|
|
switch ($_POST['action']) {
|
|
case 'get_tags':
|
|
$stmt = $pdo->prepare("SELECT DISTINCT name FROM id_names WHERE name IS NOT NULL AND name != '' ORDER BY name");
|
|
$stmt->execute();
|
|
$tags = $stmt->fetchAll(PDO::FETCH_COLUMN);
|
|
echo json_encode(['success' => true, 'tags' => $tags]);
|
|
break;
|
|
|
|
case 'get_pivot_data':
|
|
// Decode JSON tags parameter properly
|
|
$selectedTagsJson = $_POST['tags'] ?? '[]';
|
|
|
|
// Check if it's already an array or needs to be decoded
|
|
if (is_string($selectedTagsJson)) {
|
|
$selectedTags = json_decode($selectedTagsJson, true);
|
|
if (json_last_error() !== JSON_ERROR_NONE) {
|
|
throw new Exception('Invalid tags JSON: ' . json_last_error_msg());
|
|
}
|
|
} else {
|
|
$selectedTags = $selectedTagsJson;
|
|
}
|
|
|
|
$startDate = $_POST['start_date'] ?? '';
|
|
$endDate = $_POST['end_date'] ?? '';
|
|
$groupBy = $_POST['group_by'] ?? 'hour';
|
|
$aggregation = $_POST['aggregation'] ?? 'avg';
|
|
$limit = (int)($_POST['limit'] ?? 1000);
|
|
|
|
// Validate selectedTags
|
|
if (empty($selectedTags) || !is_array($selectedTags)) {
|
|
throw new Exception('No tags selected or invalid tags format');
|
|
}
|
|
|
|
// Validate limit - ensure it's a positive integer
|
|
if ($limit <= 0 || $limit > 50000) {
|
|
$limit = 1000;
|
|
}
|
|
|
|
// Build dynamic SQL based on grouping
|
|
$dateFormat = '';
|
|
switch ($groupBy) {
|
|
case 'minute':
|
|
$dateFormat = "DATE_FORMAT(h.TimeStamp, '%Y-%m-%d %H:%i:00')";
|
|
break;
|
|
case 'hour':
|
|
$dateFormat = "DATE_FORMAT(h.TimeStamp, '%Y-%m-%d %H:00:00')";
|
|
break;
|
|
case 'day':
|
|
$dateFormat = "DATE_FORMAT(h.TimeStamp, '%Y-%m-%d 00:00:00')";
|
|
break;
|
|
case 'month':
|
|
$dateFormat = "DATE_FORMAT(h.TimeStamp, '%Y-%m-01 00:00:00')";
|
|
break;
|
|
default:
|
|
$dateFormat = "DATE_FORMAT(h.TimeStamp, '%Y-%m-%d %H:00:00')";
|
|
}
|
|
|
|
// Build aggregation function
|
|
$aggFunction = strtoupper($aggregation);
|
|
if (!in_array($aggFunction, ['AVG', 'SUM', 'MIN', 'MAX', 'COUNT'])) {
|
|
$aggFunction = 'AVG';
|
|
}
|
|
|
|
// Create placeholders for IN clause
|
|
$placeholders = str_repeat('?,', count($selectedTags) - 1) . '?';
|
|
|
|
// Build the SQL query - LIMIT added directly to SQL string
|
|
$sql = "SELECT
|
|
$dateFormat as time_group,
|
|
n.name as tag_name,
|
|
$aggFunction(h.Value) as value,
|
|
COUNT(*) as data_points
|
|
FROM historicaldata h
|
|
INNER JOIN id_names n ON h.ID = n.idnumber
|
|
WHERE n.name IN ($placeholders)";
|
|
|
|
$params = $selectedTags;
|
|
|
|
if (!empty($startDate)) {
|
|
$sql .= " AND h.TimeStamp >= ?";
|
|
$params[] = $startDate;
|
|
}
|
|
|
|
if (!empty($endDate)) {
|
|
$sql .= " AND h.TimeStamp <= ?";
|
|
$params[] = $endDate;
|
|
}
|
|
|
|
$sql .= " GROUP BY time_group, n.name
|
|
ORDER BY time_group ASC, n.name ASC";
|
|
|
|
// Add LIMIT directly to the SQL string (not as a parameter)
|
|
$sql .= " LIMIT " . $limit;
|
|
|
|
// Debug logging
|
|
error_log("Selected tags: " . print_r($selectedTags, true));
|
|
error_log("Final SQL: " . $sql);
|
|
error_log("Parameters: " . print_r($params, true));
|
|
|
|
$stmt = $pdo->prepare($sql);
|
|
$stmt->execute($params);
|
|
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
|
|
|
|
// Transform data for pivot table
|
|
$pivotData = [];
|
|
$timeGroups = [];
|
|
$actualTagNames = []; // Track actual tag names from results
|
|
|
|
foreach ($results as $row) {
|
|
$timeGroup = $row['time_group'];
|
|
$tagName = $row['tag_name'];
|
|
$value = (float)$row['value'];
|
|
|
|
if (!in_array($timeGroup, $timeGroups)) {
|
|
$timeGroups[] = $timeGroup;
|
|
}
|
|
|
|
if (!in_array($tagName, $actualTagNames)) {
|
|
$actualTagNames[] = $tagName;
|
|
}
|
|
|
|
if (!isset($pivotData[$timeGroup])) {
|
|
$pivotData[$timeGroup] = ['time_group' => $timeGroup];
|
|
}
|
|
|
|
$pivotData[$timeGroup][$tagName] = $value;
|
|
}
|
|
|
|
// Fill missing values with null for actual tag names that returned data
|
|
foreach ($pivotData as &$row) {
|
|
foreach ($actualTagNames as $tagName) {
|
|
if (!isset($row[$tagName])) {
|
|
$row[$tagName] = null;
|
|
}
|
|
}
|
|
}
|
|
|
|
echo json_encode([
|
|
'success' => true,
|
|
'data' => array_values($pivotData),
|
|
'timeGroups' => $timeGroups,
|
|
'tagNames' => $actualTagNames, // Use actual tag names that returned data
|
|
'totalRows' => count($pivotData),
|
|
'rawResultCount' => count($results),
|
|
'query_info' => [
|
|
'group_by' => $groupBy,
|
|
'aggregation' => $aggregation,
|
|
'date_range' => [$startDate, $endDate],
|
|
'selected_tags' => $selectedTags,
|
|
'actual_tags' => $actualTagNames,
|
|
'limit_applied' => $limit,
|
|
'sql_query' => $sql
|
|
]
|
|
]);
|
|
break;
|
|
|
|
default:
|
|
throw new Exception('Invalid action specified');
|
|
}
|
|
|
|
} catch (Exception $e) {
|
|
echo json_encode(['success' => false, 'error' => $e->getMessage()]);
|
|
}
|
|
|
|
exit; // Always exit after AJAX response
|
|
}
|
|
?>
|
|
<!DOCTYPE html>
|
|
<html lang="en">
|
|
<head>
|
|
<meta charset="UTF-8">
|
|
<meta name="viewport" content="width=device-width, initial-scale=1.0">
|
|
<title>LASUCA Controls - Pivot Chart Analysis</title>
|
|
<script src="https://cdn.jsdelivr.net/npm/chart.js@4.4.0/dist/chart.umd.js"></script>
|
|
<script src="https://cdn.jsdelivr.net/npm/moment@2.29.4/moment.min.js"></script>
|
|
<script src="https://cdn.jsdelivr.net/npm/chartjs-adapter-moment@1.0.1/dist/chartjs-adapter-moment.min.js"></script>
|
|
|
|
<style>
|
|
* {
|
|
margin: 0;
|
|
padding: 0;
|
|
box-sizing: border-box;
|
|
}
|
|
|
|
body {
|
|
font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif;
|
|
background: #2c3e50;
|
|
color: #fff;
|
|
min-height: 100vh;
|
|
}
|
|
|
|
.dashboard-header {
|
|
background: #34495e;
|
|
padding: 20px;
|
|
text-align: center;
|
|
border-bottom: 3px solid #3498db;
|
|
box-shadow: 0 2px 10px rgba(0,0,0,0.3);
|
|
}
|
|
|
|
.dashboard-header h1 {
|
|
color: #3498db;
|
|
font-size: 2rem;
|
|
margin: 0;
|
|
text-shadow: 2px 2px 4px rgba(0,0,0,0.5);
|
|
}
|
|
|
|
.container {
|
|
max-width: 1400px;
|
|
margin: 0 auto;
|
|
padding: 30px;
|
|
}
|
|
|
|
.control-panel {
|
|
background: #ecf0f1;
|
|
color: #2c3e50;
|
|
border-radius: 15px;
|
|
box-shadow: 0 8px 25px rgba(0,0,0,0.3);
|
|
margin-bottom: 30px;
|
|
overflow: hidden;
|
|
}
|
|
|
|
.panel-header {
|
|
background: #3498db;
|
|
color: white;
|
|
padding: 20px;
|
|
font-weight: bold;
|
|
font-size: 1.2rem;
|
|
}
|
|
|
|
.control-content {
|
|
padding: 25px;
|
|
}
|
|
|
|
.control-grid {
|
|
display: grid;
|
|
grid-template-columns: repeat(auto-fit, minmax(300px, 1fr));
|
|
gap: 20px;
|
|
margin-bottom: 25px;
|
|
}
|
|
|
|
.control-group {
|
|
background: #f8f9fa;
|
|
padding: 20px;
|
|
border-radius: 10px;
|
|
border: 2px solid #bdc3c7;
|
|
}
|
|
|
|
.control-group h3 {
|
|
color: #2c3e50;
|
|
margin-bottom: 15px;
|
|
font-size: 1.1rem;
|
|
border-bottom: 2px solid #3498db;
|
|
padding-bottom: 8px;
|
|
}
|
|
|
|
.form-row {
|
|
margin-bottom: 15px;
|
|
}
|
|
|
|
.form-row label {
|
|
display: block;
|
|
margin-bottom: 5px;
|
|
font-weight: bold;
|
|
color: #2c3e50;
|
|
}
|
|
|
|
.form-row input,
|
|
.form-row select {
|
|
width: 100%;
|
|
padding: 10px 15px;
|
|
border: 2px solid #bdc3c7;
|
|
border-radius: 6px;
|
|
font-size: 0.9rem;
|
|
transition: all 0.3s ease;
|
|
}
|
|
|
|
.form-row input:focus,
|
|
.form-row select:focus {
|
|
outline: none;
|
|
border-color: #3498db;
|
|
box-shadow: 0 0 0 3px rgba(52, 152, 219, 0.1);
|
|
}
|
|
|
|
.tag-selector {
|
|
max-height: 200px;
|
|
overflow-y: auto;
|
|
border: 2px solid #bdc3c7;
|
|
border-radius: 6px;
|
|
padding: 10px;
|
|
background: white;
|
|
}
|
|
|
|
.tag-item {
|
|
display: flex;
|
|
align-items: center;
|
|
padding: 5px 0;
|
|
border-bottom: 1px solid #ecf0f1;
|
|
}
|
|
|
|
.tag-item:last-child {
|
|
border-bottom: none;
|
|
}
|
|
|
|
.tag-item input[type="checkbox"] {
|
|
width: auto;
|
|
margin-right: 10px;
|
|
}
|
|
|
|
.tag-item label {
|
|
margin: 0;
|
|
cursor: pointer;
|
|
flex: 1;
|
|
}
|
|
|
|
.action-buttons {
|
|
display: flex;
|
|
gap: 15px;
|
|
justify-content: center;
|
|
flex-wrap: wrap;
|
|
}
|
|
|
|
button {
|
|
background: #3498db;
|
|
color: white;
|
|
border: none;
|
|
padding: 12px 25px;
|
|
border-radius: 6px;
|
|
font-size: 1rem;
|
|
font-weight: bold;
|
|
cursor: pointer;
|
|
transition: all 0.3s ease;
|
|
}
|
|
|
|
button:hover:not(:disabled) {
|
|
background: #2980b9;
|
|
transform: translateY(-2px);
|
|
box-shadow: 0 6px 20px rgba(52, 152, 219, 0.3);
|
|
}
|
|
|
|
button:disabled {
|
|
background: #95a5a6;
|
|
cursor: not-allowed;
|
|
transform: none;
|
|
}
|
|
|
|
button.export-btn {
|
|
background: #27ae60;
|
|
}
|
|
|
|
button.export-btn:hover {
|
|
background: #219a52;
|
|
}
|
|
|
|
button.clear-btn {
|
|
background: #e74c3c;
|
|
}
|
|
|
|
button.clear-btn:hover {
|
|
background: #c0392b;
|
|
}
|
|
|
|
.results-container {
|
|
display: grid;
|
|
grid-template-columns: 1fr;
|
|
gap: 20px;
|
|
}
|
|
|
|
.chart-container {
|
|
background: #ecf0f1;
|
|
border-radius: 15px;
|
|
box-shadow: 0 8px 25px rgba(0,0,0,0.3);
|
|
overflow: hidden;
|
|
}
|
|
|
|
.chart-header {
|
|
background: #3498db;
|
|
color: white;
|
|
padding: 20px;
|
|
display: flex;
|
|
justify-content: space-between;
|
|
align-items: center;
|
|
}
|
|
|
|
.chart-title {
|
|
font-size: 1.3rem;
|
|
font-weight: bold;
|
|
}
|
|
|
|
.chart-controls {
|
|
display: flex;
|
|
gap: 10px;
|
|
}
|
|
|
|
.chart-controls select {
|
|
padding: 6px 12px;
|
|
border: none;
|
|
border-radius: 4px;
|
|
background: white;
|
|
color: #2c3e50;
|
|
}
|
|
|
|
.chart-wrapper {
|
|
position: relative;
|
|
height: 500px;
|
|
padding: 20px;
|
|
background: white;
|
|
}
|
|
|
|
.pivot-table-container {
|
|
background: #ecf0f1;
|
|
border-radius: 15px;
|
|
box-shadow: 0 8px 25px rgba(0,0,0,0.3);
|
|
overflow: hidden;
|
|
}
|
|
|
|
.table-header {
|
|
background: #34495e;
|
|
color: white;
|
|
padding: 20px;
|
|
font-weight: bold;
|
|
font-size: 1.2rem;
|
|
}
|
|
|
|
.table-wrapper {
|
|
overflow: auto;
|
|
max-height: 500px;
|
|
background: white;
|
|
}
|
|
|
|
.pivot-table {
|
|
width: 100%;
|
|
border-collapse: collapse;
|
|
font-size: 0.9rem;
|
|
}
|
|
|
|
.pivot-table th {
|
|
background: #34495e;
|
|
color: white;
|
|
padding: 12px 10px;
|
|
text-align: left;
|
|
position: sticky;
|
|
top: 0;
|
|
z-index: 10;
|
|
border-right: 1px solid #2c3e50;
|
|
}
|
|
|
|
.pivot-table td {
|
|
padding: 10px;
|
|
border-bottom: 1px solid #ecf0f1;
|
|
border-right: 1px solid #ecf0f1;
|
|
color: #2c3e50;
|
|
}
|
|
|
|
.pivot-table tr:nth-child(even) {
|
|
background: #f8f9fa;
|
|
}
|
|
|
|
.pivot-table tr:hover {
|
|
background: rgba(52, 152, 219, 0.1);
|
|
}
|
|
|
|
.numeric-cell {
|
|
text-align: right;
|
|
font-family: 'Courier New', monospace;
|
|
font-weight: bold;
|
|
}
|
|
|
|
.null-cell {
|
|
color: #95a5a6;
|
|
font-style: italic;
|
|
text-align: center;
|
|
}
|
|
|
|
.loading-overlay {
|
|
position: fixed;
|
|
top: 0;
|
|
left: 0;
|
|
width: 100%;
|
|
height: 100%;
|
|
background: rgba(44, 62, 80, 0.95);
|
|
display: none;
|
|
justify-content: center;
|
|
align-items: center;
|
|
z-index: 9999;
|
|
}
|
|
|
|
.loading-spinner {
|
|
width: 60px;
|
|
height: 60px;
|
|
border: 4px solid #34495e;
|
|
border-top: 4px solid #3498db;
|
|
border-radius: 50%;
|
|
animation: spin 1s linear infinite;
|
|
}
|
|
|
|
@keyframes spin {
|
|
0% { transform: rotate(0deg); }
|
|
100% { transform: rotate(360deg); }
|
|
}
|
|
|
|
.status-message {
|
|
padding: 15px 20px;
|
|
margin-bottom: 20px;
|
|
border-radius: 8px;
|
|
font-weight: bold;
|
|
}
|
|
|
|
.status-message.success {
|
|
background: rgba(39, 174, 96, 0.1);
|
|
border: 2px solid #27ae60;
|
|
color: #27ae60;
|
|
}
|
|
|
|
.status-message.error {
|
|
background: rgba(231, 76, 60, 0.1);
|
|
border: 2px solid #e74c3c;
|
|
color: #e74c3c;
|
|
}
|
|
|
|
.status-message.info {
|
|
background: rgba(52, 152, 219, 0.1);
|
|
border: 2px solid #3498db;
|
|
color: #3498db;
|
|
}
|
|
|
|
/* Debug information */
|
|
.debug-info {
|
|
background: #34495e;
|
|
color: #ecf0f1;
|
|
padding: 15px;
|
|
border-radius: 8px;
|
|
margin-bottom: 20px;
|
|
font-family: 'Courier New', monospace;
|
|
font-size: 0.8rem;
|
|
border: 2px solid #3498db;
|
|
}
|
|
|
|
.debug-info h4 {
|
|
color: #3498db;
|
|
margin-bottom: 10px;
|
|
}
|
|
|
|
/* Responsive Design */
|
|
@media (max-width: 768px) {
|
|
.control-grid {
|
|
grid-template-columns: 1fr;
|
|
}
|
|
|
|
.action-buttons {
|
|
flex-direction: column;
|
|
}
|
|
|
|
.chart-wrapper {
|
|
height: 400px;
|
|
}
|
|
|
|
.chart-header {
|
|
flex-direction: column;
|
|
gap: 15px;
|
|
text-align: center;
|
|
}
|
|
}
|
|
</style>
|
|
</head>
|
|
|
|
<body>
|
|
<!-- Loading Overlay -->
|
|
<div class="loading-overlay" id="loadingOverlay">
|
|
<div class="loading-spinner"></div>
|
|
</div>
|
|
|
|
<!-- Header -->
|
|
<div class="dashboard-header">
|
|
<h1>LASUCA Controls - Pivot Chart Analysis</h1>
|
|
<p style="margin-top: 10px; color: #bdc3c7;">Dynamic Data Analysis & Visualization</p>
|
|
</div>
|
|
|
|
<div class="container">
|
|
<!-- Control Panel -->
|
|
<div class="control-panel">
|
|
<div class="panel-header">
|
|
Analysis Configuration
|
|
</div>
|
|
<div class="control-content">
|
|
<div class="control-grid">
|
|
<!-- Tag Selection -->
|
|
<div class="control-group">
|
|
<h3>📊 Tag Selection</h3>
|
|
<div class="form-row">
|
|
<label>Select Tags to Analyze:</label>
|
|
<div class="tag-selector" id="tagSelector">
|
|
<div style="text-align: center; padding: 20px; color: #7f8c8d;">
|
|
Loading tags...
|
|
</div>
|
|
</div>
|
|
</div>
|
|
<div class="form-row">
|
|
<button onclick="selectAllTags()">Select All</button>
|
|
<button onclick="clearAllTags()" class="clear-btn">Clear All</button>
|
|
</div>
|
|
</div>
|
|
|
|
<!-- Time Range -->
|
|
<div class="control-group">
|
|
<h3>📅 Time Range</h3>
|
|
<div class="form-row">
|
|
<label for="startDate">Start Date & Time:</label>
|
|
<input type="datetime-local" id="startDate">
|
|
</div>
|
|
<div class="form-row">
|
|
<label for="endDate">End Date & Time:</label>
|
|
<input type="datetime-local" id="endDate">
|
|
</div>
|
|
<div class="form-row">
|
|
<button onclick="setQuickRange('1h')">Last Hour</button>
|
|
<button onclick="setQuickRange('24h')">Last 24h</button>
|
|
<button onclick="setQuickRange('7d')">Last Week</button>
|
|
</div>
|
|
</div>
|
|
|
|
<!-- Aggregation Settings -->
|
|
<div class="control-group">
|
|
<h3>⚙️ Analysis Settings</h3>
|
|
<div class="form-row">
|
|
<label for="groupBy">Group Data By:</label>
|
|
<select id="groupBy">
|
|
<option value="minute">Every Minute</option>
|
|
<option value="hour" selected>Every Hour</option>
|
|
<option value="day">Every Day</option>
|
|
<option value="month">Every Month</option>
|
|
</select>
|
|
</div>
|
|
<div class="form-row">
|
|
<label for="aggregation">Aggregation Function:</label>
|
|
<select id="aggregation">
|
|
<option value="avg" selected>Average (AVG)</option>
|
|
<option value="sum">Sum (SUM)</option>
|
|
<option value="min">Minimum (MIN)</option>
|
|
<option value="max">Maximum (MAX)</option>
|
|
<option value="count">Count (COUNT)</option>
|
|
</select>
|
|
</div>
|
|
<div class="form-row">
|
|
<label for="dataLimit">Data Limit:</label>
|
|
<select id="dataLimit">
|
|
<option value="500">500 records</option>
|
|
<option value="1000" selected>1000 records</option>
|
|
<option value="5000">5000 records</option>
|
|
<option value="10000">10000 records</option>
|
|
</select>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
|
|
<!-- Action Buttons -->
|
|
<div class="action-buttons">
|
|
<button onclick="generatePivotChart()" id="generateBtn">
|
|
🔄 Generate Analysis
|
|
</button>
|
|
<button onclick="exportData('csv')" class="export-btn" id="exportCsvBtn" disabled>
|
|
📊 Export CSV
|
|
</button>
|
|
<button onclick="exportData('json')" class="export-btn" id="exportJsonBtn" disabled>
|
|
📋 Export JSON
|
|
</button>
|
|
<button onclick="clearResults()" class="clear-btn">
|
|
🗑️ Clear Results
|
|
</button>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
|
|
<!-- Status Messages -->
|
|
<div id="statusMessages"></div>
|
|
|
|
<!-- Debug Info (will be populated if needed) -->
|
|
<div id="debugInfo" style="display: none;"></div>
|
|
|
|
<!-- Results Container -->
|
|
<div class="results-container" id="resultsContainer" style="display: none;">
|
|
<!-- Chart Container -->
|
|
<div class="chart-container">
|
|
<div class="chart-header">
|
|
<div class="chart-title">Pivot Chart Analysis</div>
|
|
<div class="chart-controls">
|
|
<select id="chartType" onchange="updateChartType()">
|
|
<option value="line">Line Chart</option>
|
|
<option value="bar">Bar Chart</option>
|
|
<option value="scatter">Scatter Plot</option>
|
|
<option value="area">Area Chart</option>
|
|
</select>
|
|
</div>
|
|
</div>
|
|
<div class="chart-wrapper">
|
|
<canvas id="pivotChart"></canvas>
|
|
</div>
|
|
</div>
|
|
|
|
<!-- Pivot Table -->
|
|
<div class="pivot-table-container">
|
|
<div class="table-header">
|
|
Pivot Data Table
|
|
</div>
|
|
<div class="table-wrapper">
|
|
<table class="pivot-table" id="pivotTable">
|
|
<thead id="pivotTableHead"></thead>
|
|
<tbody id="pivotTableBody"></tbody>
|
|
</table>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
|
|
<script>
|
|
// Global variables
|
|
let pivotChart = null;
|
|
let currentData = null;
|
|
let availableTags = [];
|
|
|
|
// Initialize the application
|
|
document.addEventListener('DOMContentLoaded', function() {
|
|
loadAvailableTags();
|
|
setDefaultDateRange();
|
|
});
|
|
|
|
// Load available tags from database
|
|
async function loadAvailableTags() {
|
|
try {
|
|
showLoading(true);
|
|
|
|
const formData = new FormData();
|
|
formData.append('action', 'get_tags');
|
|
|
|
const response = await fetch(window.location.href, {
|
|
method: 'POST',
|
|
body: formData
|
|
});
|
|
|
|
const responseText = await response.text();
|
|
console.log('Tags response:', responseText);
|
|
|
|
let data;
|
|
try {
|
|
data = JSON.parse(responseText);
|
|
} catch (parseError) {
|
|
console.error('JSON parse error:', parseError);
|
|
showDebugInfo('Tags Response Parse Error', responseText);
|
|
throw new Error('Invalid JSON response from server');
|
|
}
|
|
|
|
if (data.success) {
|
|
availableTags = data.tags;
|
|
renderTagSelector();
|
|
showStatus(`${availableTags.length} tags loaded successfully`, 'success');
|
|
} else {
|
|
throw new Error(data.error || 'Failed to load tags');
|
|
}
|
|
|
|
} catch (error) {
|
|
console.error('Error loading tags:', error);
|
|
showStatus('Failed to load tags: ' + error.message, 'error');
|
|
} finally {
|
|
showLoading(false);
|
|
}
|
|
}
|
|
|
|
// Render tag selector checkboxes
|
|
function renderTagSelector() {
|
|
const selector = document.getElementById('tagSelector');
|
|
|
|
if (availableTags.length === 0) {
|
|
selector.innerHTML = '<div style="text-align: center; padding: 20px; color: #e74c3c;">No tags found in database</div>';
|
|
return;
|
|
}
|
|
|
|
let html = '';
|
|
availableTags.forEach((tag, index) => {
|
|
const safeId = `tag_${index}`;
|
|
html += `
|
|
<div class="tag-item">
|
|
<input type="checkbox" id="${safeId}" value="${tag}">
|
|
<label for="${safeId}">${tag}</label>
|
|
</div>
|
|
`;
|
|
});
|
|
|
|
selector.innerHTML = html;
|
|
}
|
|
|
|
// Set default date range (last 24 hours)
|
|
function setDefaultDateRange() {
|
|
const now = new Date();
|
|
const yesterday = new Date(now.getTime() - 24 * 60 * 60 * 1000);
|
|
|
|
document.getElementById('endDate').value = formatDateForInput(now);
|
|
document.getElementById('startDate').value = formatDateForInput(yesterday);
|
|
}
|
|
|
|
// Format date for datetime-local input
|
|
function formatDateForInput(date) {
|
|
const year = date.getFullYear();
|
|
const month = String(date.getMonth() + 1).padStart(2, '0');
|
|
const day = String(date.getDate()).padStart(2, '0');
|
|
const hours = String(date.getHours()).padStart(2, '0');
|
|
const minutes = String(date.getMinutes()).padStart(2, '0');
|
|
return `${year}-${month}-${day}T${hours}:${minutes}`;
|
|
}
|
|
|
|
// Set quick date ranges
|
|
function setQuickRange(range) {
|
|
const now = new Date();
|
|
let start;
|
|
|
|
switch (range) {
|
|
case '1h':
|
|
start = new Date(now.getTime() - 60 * 60 * 1000);
|
|
break;
|
|
case '24h':
|
|
start = new Date(now.getTime() - 24 * 60 * 60 * 1000);
|
|
break;
|
|
case '7d':
|
|
start = new Date(now.getTime() - 7 * 24 * 60 * 60 * 1000);
|
|
break;
|
|
default:
|
|
return;
|
|
}
|
|
|
|
document.getElementById('startDate').value = formatDateForInput(start);
|
|
document.getElementById('endDate').value = formatDateForInput(now);
|
|
}
|
|
|
|
// Select/deselect all tags
|
|
function selectAllTags() {
|
|
const checkboxes = document.querySelectorAll('#tagSelector input[type="checkbox"]');
|
|
checkboxes.forEach(cb => cb.checked = true);
|
|
}
|
|
|
|
function clearAllTags() {
|
|
const checkboxes = document.querySelectorAll('#tagSelector input[type="checkbox"]');
|
|
checkboxes.forEach(cb => cb.checked = false);
|
|
}
|
|
|
|
// Generate pivot chart and table
|
|
async function generatePivotChart() {
|
|
try {
|
|
// Validate inputs
|
|
const selectedTags = getSelectedTags();
|
|
if (selectedTags.length === 0) {
|
|
showStatus('Please select at least one tag to analyze', 'error');
|
|
return;
|
|
}
|
|
|
|
const startDate = document.getElementById('startDate').value;
|
|
const endDate = document.getElementById('endDate').value;
|
|
|
|
if (!startDate || !endDate) {
|
|
showStatus('Please select both start and end dates', 'error');
|
|
return;
|
|
}
|
|
|
|
if (new Date(startDate) >= new Date(endDate)) {
|
|
showStatus('Start date must be before end date', 'error');
|
|
return;
|
|
}
|
|
|
|
showLoading(true);
|
|
document.getElementById('generateBtn').disabled = true;
|
|
|
|
// Prepare request data
|
|
const formData = new FormData();
|
|
formData.append('action', 'get_pivot_data');
|
|
formData.append('tags', JSON.stringify(selectedTags));
|
|
formData.append('start_date', startDate);
|
|
formData.append('end_date', endDate);
|
|
formData.append('group_by', document.getElementById('groupBy').value);
|
|
formData.append('aggregation', document.getElementById('aggregation').value);
|
|
formData.append('limit', document.getElementById('dataLimit').value);
|
|
|
|
console.log('Sending request with tags:', selectedTags);
|
|
|
|
const response = await fetch(window.location.href, {
|
|
method: 'POST',
|
|
body: formData
|
|
});
|
|
|
|
const responseText = await response.text();
|
|
console.log('Pivot data response:', responseText);
|
|
|
|
let result;
|
|
try {
|
|
result = JSON.parse(responseText);
|
|
} catch (parseError) {
|
|
console.error('JSON parse error:', parseError);
|
|
showDebugInfo('Pivot Data Response Parse Error', responseText);
|
|
throw new Error('Invalid JSON response from server');
|
|
}
|
|
|
|
if (result.success) {
|
|
currentData = result;
|
|
console.log('Received data:', result);
|
|
|
|
if (result.totalRows === 0) {
|
|
showStatus('No data found for the selected criteria. Try different tags or date range.', 'info');
|
|
return;
|
|
}
|
|
|
|
renderPivotChart();
|
|
renderPivotTable();
|
|
showResults();
|
|
enableExportButtons(true);
|
|
|
|
const message = `Analysis complete! Generated ${result.totalRows} data points across ${result.tagNames.length} tags (${result.rawResultCount} raw records processed).`;
|
|
showStatus(message, 'success');
|
|
|
|
// Show debug info if available
|
|
if (result.query_info) {
|
|
showDebugInfo('Query Information', JSON.stringify(result.query_info, null, 2));
|
|
}
|
|
} else {
|
|
throw new Error(result.error || 'Failed to generate pivot analysis');
|
|
}
|
|
|
|
} catch (error) {
|
|
console.error('Error generating pivot chart:', error);
|
|
showStatus('Failed to generate analysis: ' + error.message, 'error');
|
|
} finally {
|
|
showLoading(false);
|
|
document.getElementById('generateBtn').disabled = false;
|
|
}
|
|
}
|
|
|
|
// Get selected tags
|
|
function getSelectedTags() {
|
|
const checkboxes = document.querySelectorAll('#tagSelector input[type="checkbox"]:checked');
|
|
return Array.from(checkboxes).map(cb => cb.value);
|
|
}
|
|
|
|
// Render pivot chart
|
|
function renderPivotChart() {
|
|
const ctx = document.getElementById('pivotChart').getContext('2d');
|
|
|
|
// Destroy existing chart
|
|
if (pivotChart) {
|
|
pivotChart.destroy();
|
|
}
|
|
|
|
// Check if we have data
|
|
if (!currentData || !currentData.data || currentData.data.length === 0) {
|
|
ctx.fillStyle = '#7f8c8d';
|
|
ctx.font = '16px Arial';
|
|
ctx.textAlign = 'center';
|
|
ctx.fillText('No data to display', ctx.canvas.width / 2, ctx.canvas.height / 2);
|
|
return;
|
|
}
|
|
|
|
// Prepare chart data
|
|
const labels = currentData.timeGroups;
|
|
const datasets = currentData.tagNames.map((tagName, index) => {
|
|
const colors = [
|
|
'#3498db', '#e74c3c', '#27ae60', '#f39c12', '#9b59b6',
|
|
'#1abc9c', '#34495e', '#e67e22', '#2ecc71', '#8e44ad'
|
|
];
|
|
|
|
const color = colors[index % colors.length];
|
|
|
|
return {
|
|
label: tagName,
|
|
data: currentData.data.map(row => row[tagName]),
|
|
borderColor: color,
|
|
backgroundColor: color + '20',
|
|
borderWidth: 2,
|
|
fill: false,
|
|
tension: 0.1,
|
|
pointRadius: 3,
|
|
pointHoverRadius: 5
|
|
};
|
|
});
|
|
|
|
const chartType = document.getElementById('chartType').value;
|
|
|
|
pivotChart = new Chart(ctx, {
|
|
type: chartType === 'area' ? 'line' : chartType,
|
|
data: {
|
|
labels: labels,
|
|
datasets: datasets
|
|
},
|
|
options: {
|
|
responsive: true,
|
|
maintainAspectRatio: false,
|
|
scales: {
|
|
x: {
|
|
display: true,
|
|
title: {
|
|
display: true,
|
|
text: 'Time Period',
|
|
font: { weight: 'bold' }
|
|
},
|
|
ticks: {
|
|
maxTicksLimit: 12
|
|
}
|
|
},
|
|
y: {
|
|
display: true,
|
|
title: {
|
|
display: true,
|
|
text: `Value (${document.getElementById('aggregation').value.toUpperCase()})`,
|
|
font: { weight: 'bold' }
|
|
}
|
|
}
|
|
},
|
|
plugins: {
|
|
legend: {
|
|
display: true,
|
|
position: 'top'
|
|
},
|
|
tooltip: {
|
|
mode: 'index',
|
|
intersect: false,
|
|
callbacks: {
|
|
title: function(context) {
|
|
return 'Time: ' + context[0].label;
|
|
},
|
|
label: function(context) {
|
|
const value = context.parsed.y;
|
|
return context.dataset.label + ': ' + (value !== null ? value.toFixed(2) : 'No Data');
|
|
}
|
|
}
|
|
}
|
|
},
|
|
interaction: {
|
|
mode: 'nearest',
|
|
axis: 'x',
|
|
intersect: false
|
|
}
|
|
}
|
|
});
|
|
|
|
// Apply area fill if selected
|
|
if (chartType === 'area') {
|
|
pivotChart.data.datasets.forEach(dataset => {
|
|
dataset.fill = true;
|
|
});
|
|
pivotChart.update();
|
|
}
|
|
}
|
|
|
|
// Render pivot table
|
|
function renderPivotTable() {
|
|
const tableHead = document.getElementById('pivotTableHead');
|
|
const tableBody = document.getElementById('pivotTableBody');
|
|
|
|
if (!currentData || !currentData.data || currentData.data.length === 0) {
|
|
tableHead.innerHTML = '<tr><th>No Data Available</th></tr>';
|
|
tableBody.innerHTML = '<tr><td>No data found for the selected criteria</td></tr>';
|
|
return;
|
|
}
|
|
|
|
// Create header
|
|
let headerHtml = '<tr><th>Time Period</th>';
|
|
currentData.tagNames.forEach(tagName => {
|
|
headerHtml += `<th>${tagName}</th>`;
|
|
});
|
|
headerHtml += '</tr>';
|
|
tableHead.innerHTML = headerHtml;
|
|
|
|
// Create body
|
|
let bodyHtml = '';
|
|
currentData.data.forEach(row => {
|
|
bodyHtml += `<tr><td class="timestamp">${row.time_group}</td>`;
|
|
currentData.tagNames.forEach(tagName => {
|
|
const value = row[tagName];
|
|
if (value !== null && value !== undefined) {
|
|
bodyHtml += `<td class="numeric-cell">${value.toFixed(2)}</td>`;
|
|
} else {
|
|
bodyHtml += `<td class="null-cell">--</td>`;
|
|
}
|
|
});
|
|
bodyHtml += '</tr>';
|
|
});
|
|
tableBody.innerHTML = bodyHtml;
|
|
}
|
|
|
|
// Update chart type
|
|
function updateChartType() {
|
|
if (currentData) {
|
|
renderPivotChart();
|
|
}
|
|
}
|
|
|
|
// Show/hide results container
|
|
function showResults() {
|
|
document.getElementById('resultsContainer').style.display = 'block';
|
|
}
|
|
|
|
// Clear results
|
|
function clearResults() {
|
|
document.getElementById('resultsContainer').style.display = 'none';
|
|
if (pivotChart) {
|
|
pivotChart.destroy();
|
|
pivotChart = null;
|
|
}
|
|
currentData = null;
|
|
enableExportButtons(false);
|
|
clearStatus();
|
|
hideDebugInfo();
|
|
}
|
|
|
|
// Enable/disable export buttons
|
|
function enableExportButtons(enabled) {
|
|
document.getElementById('exportCsvBtn').disabled = !enabled;
|
|
document.getElementById('exportJsonBtn').disabled = !enabled;
|
|
}
|
|
|
|
// Export data
|
|
function exportData(format) {
|
|
if (!currentData) {
|
|
showStatus('No data available to export', 'error');
|
|
return;
|
|
}
|
|
|
|
const timestamp = new Date().toISOString().slice(0, 19).replace(/:/g, '-');
|
|
|
|
if (format === 'csv') {
|
|
exportToCSV(`pivot_analysis_${timestamp}.csv`);
|
|
} else if (format === 'json') {
|
|
exportToJSON(`pivot_analysis_${timestamp}.json`);
|
|
}
|
|
}
|
|
|
|
// Export to CSV
|
|
function exportToCSV(filename) {
|
|
let csv = 'Time Period,' + currentData.tagNames.join(',') + '\n';
|
|
|
|
currentData.data.forEach(row => {
|
|
let line = '"' + row.time_group + '",';
|
|
const values = currentData.tagNames.map(tagName => {
|
|
const value = row[tagName];
|
|
return value !== null && value !== undefined ? value.toFixed(2) : '';
|
|
});
|
|
line += values.join(',');
|
|
csv += line + '\n';
|
|
});
|
|
|
|
downloadFile(csv, filename, 'text/csv');
|
|
showStatus('CSV exported successfully', 'success');
|
|
}
|
|
|
|
// Export to JSON
|
|
function exportToJSON(filename) {
|
|
const exportData = {
|
|
metadata: {
|
|
export_date: new Date().toISOString(),
|
|
query_info: currentData.query_info,
|
|
total_rows: currentData.totalRows,
|
|
tag_names: currentData.tagNames
|
|
},
|
|
data: currentData.data
|
|
};
|
|
|
|
downloadFile(JSON.stringify(exportData, null, 2), filename, 'application/json');
|
|
showStatus('JSON exported successfully', 'success');
|
|
}
|
|
|
|
// Download file helper
|
|
function downloadFile(content, filename, mimeType) {
|
|
const blob = new Blob([content], { type: mimeType });
|
|
const link = document.createElement('a');
|
|
link.href = window.URL.createObjectURL(blob);
|
|
link.download = filename;
|
|
document.body.appendChild(link);
|
|
link.click();
|
|
document.body.removeChild(link);
|
|
window.URL.revokeObjectURL(link.href);
|
|
}
|
|
|
|
// Loading overlay
|
|
function showLoading(show) {
|
|
document.getElementById('loadingOverlay').style.display = show ? 'flex' : 'none';
|
|
}
|
|
|
|
// Status messages
|
|
function showStatus(message, type) {
|
|
const container = document.getElementById('statusMessages');
|
|
const div = document.createElement('div');
|
|
div.className = `status-message ${type}`;
|
|
div.textContent = message;
|
|
|
|
container.innerHTML = '';
|
|
container.appendChild(div);
|
|
|
|
// Auto-clear success messages
|
|
if (type === 'success') {
|
|
setTimeout(() => {
|
|
if (div.parentNode === container) {
|
|
container.removeChild(div);
|
|
}
|
|
}, 5000);
|
|
}
|
|
}
|
|
|
|
function clearStatus() {
|
|
document.getElementById('statusMessages').innerHTML = '';
|
|
}
|
|
|
|
// Debug information functions
|
|
function showDebugInfo(title, content) {
|
|
const debugDiv = document.getElementById('debugInfo');
|
|
debugDiv.innerHTML = `
|
|
<h4>${title}</h4>
|
|
<pre>${content}</pre>
|
|
`;
|
|
debugDiv.style.display = 'block';
|
|
}
|
|
|
|
function hideDebugInfo() {
|
|
document.getElementById('debugInfo').style.display = 'none';
|
|
}
|
|
</script>
|
|
</body>
|
|
</html>
|