1170 lines
34 KiB
PHP
1170 lines
34 KiB
PHP
<?php // phpcs:ignoreFile
|
|
require __DIR__ . '/../session.php';
|
|
require __DIR__ . '/../userAccess.php';
|
|
|
|
$pageTitle = 'Pivot Chart Analysis';
|
|
$pageSubtitle = 'Aggregate historian data by tag and timeframe.';
|
|
$pageDescription = 'Build pivoted charts and data tables from historian ' .
|
|
'metrics and export the results.';
|
|
$assetBasePath = '../';
|
|
$layoutWithoutSidebar = true;
|
|
$layoutReturnUrl = '../overview.php';
|
|
$layoutReturnLabel = 'Back to overview';
|
|
|
|
$chartJsCdn = 'https://cdn.jsdelivr.net/npm/chart.js@4.4.0/dist/chart.umd.js';
|
|
$momentJsCdn = 'https://cdn.jsdelivr.net/npm/moment@2.29.4/moment.min.js';
|
|
$chartMomentAdapterCdn = 'https://cdn.jsdelivr.net/npm/' .
|
|
'chartjs-adapter-moment@1.0.1/dist/chartjs-adapter-moment.min.js';
|
|
|
|
$servername = '192.168.0.13\\SQLEXPRESS';
|
|
$username = 'opce';
|
|
$password = 'opcelasuca';
|
|
$dbname = 'history';
|
|
|
|
try {
|
|
$pdo = new PDO(
|
|
"sqlsrv:Server=$servername;Database=$dbname",
|
|
$username,
|
|
$password,
|
|
[
|
|
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
|
|
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
|
|
]
|
|
);
|
|
} 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());
|
|
}
|
|
|
|
$normalizeDateTime = static function (?string $value): string {
|
|
if ($value === null || $value === '') {
|
|
return '';
|
|
}
|
|
|
|
$patterns = ['Y-m-d\TH:i:s', 'Y-m-d\TH:i', 'Y-m-d H:i:s'];
|
|
|
|
foreach ($patterns as $pattern) {
|
|
$date = DateTimeImmutable::createFromFormat($pattern, $value);
|
|
if ($date instanceof DateTimeImmutable) {
|
|
return $date->format('Y-m-d H:i:s');
|
|
}
|
|
}
|
|
|
|
return $value;
|
|
};
|
|
|
|
if (isset($_POST['action'])) {
|
|
ob_clean();
|
|
header('Content-Type: application/json');
|
|
|
|
try {
|
|
switch ($_POST['action']) {
|
|
case 'get_tags':
|
|
$tagQuery = <<<SQL
|
|
SELECT DISTINCT name
|
|
FROM dbo.id_names
|
|
WHERE name IS NOT NULL
|
|
AND name <> ''
|
|
ORDER BY name
|
|
SQL;
|
|
$stmt = $pdo->prepare($tagQuery);
|
|
$stmt->execute();
|
|
$tags = $stmt->fetchAll(PDO::FETCH_COLUMN);
|
|
|
|
echo json_encode(['success' => true, 'tags' => $tags]);
|
|
break;
|
|
|
|
case 'get_pivot_data':
|
|
$selectedTagsRaw = $_POST['tags'] ?? '[]';
|
|
|
|
if (is_string($selectedTagsRaw)) {
|
|
$selectedTags = json_decode($selectedTagsRaw, true);
|
|
if (json_last_error() !== JSON_ERROR_NONE) {
|
|
throw new Exception(
|
|
'Invalid tags JSON: ' . json_last_error_msg()
|
|
);
|
|
}
|
|
} else {
|
|
$selectedTags = $selectedTagsRaw;
|
|
}
|
|
|
|
if (empty($selectedTags) || !is_array($selectedTags)) {
|
|
throw new Exception(
|
|
'No tags selected or invalid tags format.'
|
|
);
|
|
}
|
|
|
|
$startDate = $normalizeDateTime($_POST['start_date'] ?? '');
|
|
$endDate = $normalizeDateTime($_POST['end_date'] ?? '');
|
|
$groupBy = $_POST['group_by'] ?? 'hour';
|
|
$aggregation = $_POST['aggregation'] ?? 'avg';
|
|
$limit = (int) ($_POST['limit'] ?? 1000);
|
|
|
|
if ($limit <= 0 || $limit > 50000) {
|
|
$limit = 1000;
|
|
}
|
|
|
|
$aggFunction = strtoupper($aggregation);
|
|
$validAggregations = ['AVG', 'SUM', 'MIN', 'MAX', 'COUNT'];
|
|
|
|
if (!in_array($aggFunction, $validAggregations, true)) {
|
|
$aggFunction = 'AVG';
|
|
}
|
|
|
|
$timeGroupExpressions = [
|
|
'minute' => 'CONVERT(varchar(19), DATEADD(MINUTE, ' .
|
|
'DATEDIFF(MINUTE, 0, h.TimeStamp), 0), 120)',
|
|
'hour' => 'CONVERT(varchar(19), DATEADD(HOUR, ' .
|
|
'DATEDIFF(HOUR, 0, h.TimeStamp), 0), 120)',
|
|
'day' => 'CONVERT(varchar(19), DATEADD(DAY, ' .
|
|
'DATEDIFF(DAY, 0, h.TimeStamp), 0), 120)',
|
|
'month' => 'CONVERT(varchar(19), CAST(DATEFROMPARTS(' .
|
|
'YEAR(h.TimeStamp), MONTH(h.TimeStamp), 1) AS ' .
|
|
'datetime), 120)',
|
|
];
|
|
|
|
$timeGroupExpr = $timeGroupExpressions[$groupBy]
|
|
?? $timeGroupExpressions['hour'];
|
|
|
|
if ($aggFunction === 'COUNT') {
|
|
$valueExpression = 'COUNT(*)';
|
|
} else {
|
|
$valueExpression = $aggFunction .
|
|
'(CAST(h.Value AS float))';
|
|
}
|
|
|
|
$placeholders = implode(',', array_fill(
|
|
0,
|
|
count($selectedTags),
|
|
'?'
|
|
));
|
|
$params = $selectedTags;
|
|
|
|
$sql = "
|
|
SELECT TOP {$limit}
|
|
{$timeGroupExpr} AS time_group,
|
|
n.name AS tag_name,
|
|
{$valueExpression} AS value,
|
|
COUNT(*) AS data_points
|
|
FROM dbo.historicaldata AS h
|
|
INNER JOIN dbo.id_names AS n
|
|
ON h.ID = n.idnumber
|
|
WHERE n.name IN ({$placeholders})
|
|
";
|
|
|
|
if ($startDate !== '') {
|
|
$sql .= ' AND h.TimeStamp >= ?';
|
|
$params[] = $startDate;
|
|
}
|
|
|
|
if ($endDate !== '') {
|
|
$sql .= ' AND h.TimeStamp <= ?';
|
|
$params[] = $endDate;
|
|
}
|
|
|
|
$sql .= '
|
|
GROUP BY ' . $timeGroupExpr . ', n.name
|
|
ORDER BY time_group ASC, n.name ASC
|
|
';
|
|
|
|
$stmt = $pdo->prepare($sql);
|
|
$stmt->execute($params);
|
|
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
|
|
|
|
$pivotData = [];
|
|
$timeGroups = [];
|
|
$actualTagNames = [];
|
|
|
|
foreach ($rows as $row) {
|
|
$timeGroup = $row['time_group'];
|
|
$tagName = $row['tag_name'];
|
|
$value = (float) $row['value'];
|
|
|
|
if (!in_array($timeGroup, $timeGroups, true)) {
|
|
$timeGroups[] = $timeGroup;
|
|
}
|
|
|
|
if (!in_array($tagName, $actualTagNames, true)) {
|
|
$actualTagNames[] = $tagName;
|
|
}
|
|
|
|
if (!isset($pivotData[$timeGroup])) {
|
|
$pivotData[$timeGroup] = ['time_group' => $timeGroup];
|
|
}
|
|
|
|
$pivotData[$timeGroup][$tagName] = $value;
|
|
}
|
|
|
|
foreach ($pivotData as &$rowValues) {
|
|
foreach ($actualTagNames as $tagName) {
|
|
if (!array_key_exists($tagName, $rowValues)) {
|
|
$rowValues[$tagName] = null;
|
|
}
|
|
}
|
|
}
|
|
unset($rowValues);
|
|
|
|
echo json_encode(
|
|
[
|
|
'success' => true,
|
|
'data' => array_values($pivotData),
|
|
'timeGroups' => $timeGroups,
|
|
'tagNames' => $actualTagNames,
|
|
'totalRows' => count($pivotData),
|
|
'rawResultCount' => count($rows),
|
|
'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;
|
|
}
|
|
|
|
require __DIR__ . '/../includes/layout/header.php';
|
|
?>
|
|
<div
|
|
class="pivot-overlay"
|
|
id="loadingOverlay"
|
|
aria-hidden="true"
|
|
>
|
|
<div
|
|
class="pivot-overlay__spinner"
|
|
role="status"
|
|
aria-label="Loading data"
|
|
></div>
|
|
</div>
|
|
|
|
<div class="app-content pivot-app">
|
|
<section class="data-panel pivot-config">
|
|
<div class="panel-intro">
|
|
<h2>Pivot analysis</h2>
|
|
<p>
|
|
Group historian tags by time bucket,
|
|
compare aggregates, and export the results.
|
|
</p>
|
|
</div>
|
|
|
|
<div class="pivot-config__grid">
|
|
<div
|
|
class="pivot-config__section"
|
|
aria-labelledby="pivot-tag-title"
|
|
>
|
|
<h3 class="pivot-config__title" id="pivot-tag-title">
|
|
Tag selection
|
|
</h3>
|
|
<p class="pivot-config__hint">
|
|
Choose one or more historian tags
|
|
to include in the analysis.
|
|
</p>
|
|
<div class="pivot-field">
|
|
<label class="pivot-field__label" for="tagSelector">
|
|
Available tags
|
|
</label>
|
|
<div class="pivot-tag-selector">
|
|
<p class="pivot-placeholder" id="tagPlaceholder">
|
|
Loading tags…
|
|
</p>
|
|
<div
|
|
class="pivot-tag-selector__grid"
|
|
id="tagSelector"
|
|
role="group"
|
|
aria-labelledby="pivot-tag-title"
|
|
></div>
|
|
</div>
|
|
</div>
|
|
<div class="pivot-config__actions">
|
|
<button
|
|
type="button"
|
|
class="button button--ghost"
|
|
onclick="selectAllTags()"
|
|
>
|
|
Select all
|
|
</button>
|
|
<button
|
|
type="button"
|
|
class="button button--ghost"
|
|
onclick="clearAllTags()"
|
|
>
|
|
Clear selection
|
|
</button>
|
|
</div>
|
|
</div>
|
|
|
|
<div
|
|
class="pivot-config__section"
|
|
aria-labelledby="pivot-range-title"
|
|
>
|
|
<h3 class="pivot-config__title" id="pivot-range-title">
|
|
Time range
|
|
</h3>
|
|
<div class="pivot-field">
|
|
<label class="pivot-field__label" for="startDate">
|
|
Start date and time
|
|
</label>
|
|
<input
|
|
type="datetime-local"
|
|
id="startDate"
|
|
class="pivot-input"
|
|
>
|
|
</div>
|
|
<div class="pivot-field">
|
|
<label class="pivot-field__label" for="endDate">
|
|
End date and time
|
|
</label>
|
|
<input
|
|
type="datetime-local"
|
|
id="endDate"
|
|
class="pivot-input"
|
|
>
|
|
</div>
|
|
<div class="pivot-quick-range">
|
|
<span class="pivot-quick-range__label">Quick ranges</span>
|
|
<div class="pivot-quick-range__buttons">
|
|
<button
|
|
type="button"
|
|
class="button button--ghost"
|
|
onclick="setQuickRange('1h')"
|
|
>
|
|
Last hour
|
|
</button>
|
|
<button
|
|
type="button"
|
|
class="button button--ghost"
|
|
onclick="setQuickRange('24h')"
|
|
>
|
|
Last 24 hours
|
|
</button>
|
|
<button
|
|
type="button"
|
|
class="button button--ghost"
|
|
onclick="setQuickRange('7d')"
|
|
>
|
|
Last 7 days
|
|
</button>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
|
|
<div
|
|
class="pivot-config__section"
|
|
aria-labelledby="pivot-settings-title"
|
|
>
|
|
<h3 class="pivot-config__title" id="pivot-settings-title">
|
|
Aggregation
|
|
</h3>
|
|
<div class="pivot-field">
|
|
<label class="pivot-field__label" for="groupBy">
|
|
Group data by
|
|
</label>
|
|
<select id="groupBy" class="pivot-select">
|
|
<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="pivot-field">
|
|
<label class="pivot-field__label" for="aggregation">
|
|
Aggregation function
|
|
</label>
|
|
<select id="aggregation" class="pivot-select">
|
|
<option value="avg" selected>Average</option>
|
|
<option value="sum">Sum</option>
|
|
<option value="min">Minimum</option>
|
|
<option value="max">Maximum</option>
|
|
<option value="count">Count</option>
|
|
</select>
|
|
</div>
|
|
<div class="pivot-field">
|
|
<label class="pivot-field__label" for="dataLimit">
|
|
Result limit
|
|
</label>
|
|
<select id="dataLimit" class="pivot-select">
|
|
<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>
|
|
|
|
<div class="pivot-actions">
|
|
<button
|
|
type="button"
|
|
class="button"
|
|
id="generateBtn"
|
|
onclick="generatePivotChart()"
|
|
>
|
|
Generate analysis
|
|
</button>
|
|
<button
|
|
type="button"
|
|
class="button button--success"
|
|
id="exportCsvBtn"
|
|
onclick="exportData('csv')"
|
|
disabled
|
|
>
|
|
Export CSV
|
|
</button>
|
|
<button
|
|
type="button"
|
|
class="button button--ghost"
|
|
id="exportJsonBtn"
|
|
onclick="exportData('json')"
|
|
disabled
|
|
>
|
|
Export JSON
|
|
</button>
|
|
<button
|
|
type="button"
|
|
class="button button--danger"
|
|
onclick="clearResults()"
|
|
>
|
|
Clear results
|
|
</button>
|
|
</div>
|
|
</section>
|
|
|
|
<div
|
|
id="statusMessages"
|
|
class="pivot-status"
|
|
aria-live="polite"
|
|
></div>
|
|
|
|
<section class="pivot-results is-hidden" id="resultsContainer">
|
|
<div class="pivot-results__grid">
|
|
<article class="data-panel pivot-results__panel">
|
|
<div class="pivot-results__header">
|
|
<div>
|
|
<h3>Pivot chart</h3>
|
|
<p>
|
|
Visualize the aggregated values for each selected tag
|
|
across time.
|
|
</p>
|
|
</div>
|
|
<div class="pivot-results__control">
|
|
<label for="chartType">Chart type</label>
|
|
<select
|
|
id="chartType"
|
|
class="pivot-select"
|
|
onchange="updateChartType()"
|
|
>
|
|
<option value="line">Line</option>
|
|
<option value="bar">Bar</option>
|
|
<option value="scatter">Scatter</option>
|
|
<option value="area">Area</option>
|
|
</select>
|
|
</div>
|
|
</div>
|
|
<div class="pivot-chart__wrapper">
|
|
<canvas id="pivotChart"></canvas>
|
|
</div>
|
|
</article>
|
|
|
|
<article class="data-panel pivot-results__panel">
|
|
<div class="pivot-results__header">
|
|
<div>
|
|
<h3>Pivot data table</h3>
|
|
<p>
|
|
Download-ready grid of each time bucket and tag value.
|
|
</p>
|
|
</div>
|
|
</div>
|
|
<div class="pivot-table__wrapper">
|
|
<table class="pivot-table" id="pivotTable">
|
|
<thead id="pivotTableHead"></thead>
|
|
<tbody id="pivotTableBody"></tbody>
|
|
</table>
|
|
</div>
|
|
</article>
|
|
</div>
|
|
</section>
|
|
|
|
<section class="data-panel pivot-debug is-hidden" id="debugPanel">
|
|
<div class="pivot-debug__header">
|
|
<div>
|
|
<h3>Query details</h3>
|
|
<p class="pivot-debug__subtitle" id="debugTitle"></p>
|
|
</div>
|
|
<button
|
|
type="button"
|
|
class="button button--ghost"
|
|
onclick="hideDebugInfo()"
|
|
>
|
|
Hide
|
|
</button>
|
|
</div>
|
|
<pre class="pivot-debug__content" id="debugInfo"></pre>
|
|
</section>
|
|
</div>
|
|
|
|
<script src="<?php echo htmlspecialchars($chartJsCdn, ENT_QUOTES); ?>"></script>
|
|
<script src="<?php echo htmlspecialchars($momentJsCdn, ENT_QUOTES); ?>"></script>
|
|
<script src="<?php echo htmlspecialchars($chartMomentAdapterCdn, ENT_QUOTES); ?>"></script>
|
|
|
|
<script>
|
|
let pivotChart = null;
|
|
let currentData = null;
|
|
let availableTags = [];
|
|
let elements = {};
|
|
|
|
document.addEventListener('DOMContentLoaded', () => {
|
|
elements = {
|
|
loadingOverlay: document.getElementById('loadingOverlay'),
|
|
tagSelector: document.getElementById('tagSelector'),
|
|
tagPlaceholder: document.getElementById('tagPlaceholder'),
|
|
startDate: document.getElementById('startDate'),
|
|
endDate: document.getElementById('endDate'),
|
|
groupBy: document.getElementById('groupBy'),
|
|
aggregation: document.getElementById('aggregation'),
|
|
dataLimit: document.getElementById('dataLimit'),
|
|
generateBtn: document.getElementById('generateBtn'),
|
|
resultsContainer: document.getElementById('resultsContainer'),
|
|
exportCsvBtn: document.getElementById('exportCsvBtn'),
|
|
exportJsonBtn: document.getElementById('exportJsonBtn'),
|
|
chartType: document.getElementById('chartType'),
|
|
pivotChart: document.getElementById('pivotChart'),
|
|
tableHead: document.getElementById('pivotTableHead'),
|
|
tableBody: document.getElementById('pivotTableBody'),
|
|
statusMessages: document.getElementById('statusMessages'),
|
|
debugPanel: document.getElementById('debugPanel'),
|
|
debugTitle: document.getElementById('debugTitle'),
|
|
debugInfo: document.getElementById('debugInfo'),
|
|
};
|
|
|
|
setDefaultDateRange();
|
|
loadAvailableTags();
|
|
});
|
|
|
|
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 rawText = await response.text();
|
|
let data;
|
|
|
|
try {
|
|
data = JSON.parse(rawText);
|
|
} catch (error) {
|
|
console.error('Tag JSON parse error', error);
|
|
showDebugInfo('Tag response parse error', rawText);
|
|
throw new Error('Invalid JSON when loading tags.');
|
|
}
|
|
|
|
if (!data.success) {
|
|
throw new Error(data.error || 'Failed to load tags.');
|
|
}
|
|
|
|
availableTags = Array.isArray(data.tags) ? data.tags : [];
|
|
renderTagSelector();
|
|
|
|
const message =
|
|
availableTags.length + ' tags loaded successfully.';
|
|
showStatus(message, 'success');
|
|
} catch (error) {
|
|
console.error('Error loading tags', error);
|
|
showStatus('Unable to load tags: ' + error.message, 'error');
|
|
renderTagSelector(true);
|
|
} finally {
|
|
showLoading(false);
|
|
}
|
|
}
|
|
|
|
function renderTagSelector(showError = false) {
|
|
const placeholder = elements.tagPlaceholder;
|
|
const container = elements.tagSelector;
|
|
|
|
container.innerHTML = '';
|
|
|
|
if (showError) {
|
|
placeholder.textContent =
|
|
'Failed to load tags. Try refreshing the page.';
|
|
placeholder.classList.remove('is-hidden');
|
|
return;
|
|
}
|
|
|
|
if (!availableTags.length) {
|
|
placeholder.textContent = 'No tags found in the database.';
|
|
placeholder.classList.remove('is-hidden');
|
|
return;
|
|
}
|
|
|
|
placeholder.classList.add('is-hidden');
|
|
|
|
availableTags.forEach((tag, index) => {
|
|
const wrapper = document.createElement('label');
|
|
wrapper.className = 'pivot-tag-item';
|
|
|
|
const checkbox = document.createElement('input');
|
|
checkbox.type = 'checkbox';
|
|
checkbox.id = 'tag-' + index;
|
|
checkbox.value = tag;
|
|
|
|
const text = document.createElement('span');
|
|
text.textContent = tag;
|
|
|
|
wrapper.appendChild(checkbox);
|
|
wrapper.appendChild(text);
|
|
container.appendChild(wrapper);
|
|
});
|
|
}
|
|
|
|
function setDefaultDateRange() {
|
|
const now = new Date();
|
|
const yesterday = new Date(now.getTime() - 24 * 60 * 60 * 1000);
|
|
|
|
elements.endDate.value = formatDateForInput(now);
|
|
elements.startDate.value = formatDateForInput(yesterday);
|
|
}
|
|
|
|
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
|
|
);
|
|
}
|
|
|
|
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;
|
|
}
|
|
|
|
elements.startDate.value = formatDateForInput(start);
|
|
elements.endDate.value = formatDateForInput(now);
|
|
}
|
|
|
|
function selectAllTags() {
|
|
const inputs = elements.tagSelector.querySelectorAll(
|
|
'input[type="checkbox"]'
|
|
);
|
|
inputs.forEach((input) => {
|
|
input.checked = true;
|
|
});
|
|
}
|
|
|
|
function clearAllTags() {
|
|
const inputs = elements.tagSelector.querySelectorAll(
|
|
'input[type="checkbox"]'
|
|
);
|
|
inputs.forEach((input) => {
|
|
input.checked = false;
|
|
});
|
|
}
|
|
|
|
async function generatePivotChart() {
|
|
try {
|
|
const selectedTags = getSelectedTags();
|
|
|
|
if (!selectedTags.length) {
|
|
showStatus(
|
|
'Please select at least one tag to analyze.',
|
|
'error'
|
|
);
|
|
return;
|
|
}
|
|
|
|
const startDate = elements.startDate.value;
|
|
const endDate = elements.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);
|
|
elements.generateBtn.disabled = true;
|
|
|
|
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', elements.groupBy.value);
|
|
formData.append('aggregation', elements.aggregation.value);
|
|
formData.append('limit', elements.dataLimit.value);
|
|
|
|
const response = await fetch(
|
|
window.location.href,
|
|
{
|
|
method: 'POST',
|
|
body: formData,
|
|
}
|
|
);
|
|
|
|
const rawText = await response.text();
|
|
let result;
|
|
|
|
try {
|
|
result = JSON.parse(rawText);
|
|
} catch (error) {
|
|
console.error('Pivot JSON parse error', error);
|
|
showDebugInfo('Pivot response parse error', rawText);
|
|
throw new Error('Invalid JSON when generating pivot data.');
|
|
}
|
|
|
|
if (!result.success) {
|
|
throw new Error(result.error || 'Failed to generate data.');
|
|
}
|
|
|
|
currentData = result;
|
|
|
|
if (!result.totalRows) {
|
|
showStatus(
|
|
'No data found for the selected criteria.',
|
|
'info'
|
|
);
|
|
return;
|
|
}
|
|
|
|
renderPivotChart();
|
|
renderPivotTable();
|
|
showResults();
|
|
enableExportButtons(true);
|
|
|
|
const summaryParts = [
|
|
'Analysis complete!',
|
|
'Generated ' + result.totalRows + ' data points',
|
|
'across ' + result.tagNames.length + ' tags.',
|
|
];
|
|
summaryParts.push(
|
|
'(' + result.rawResultCount + ' raw records processed.)'
|
|
);
|
|
|
|
showStatus(summaryParts.join(' '), 'success');
|
|
|
|
if (result.query_info) {
|
|
const queryText = JSON.stringify(result.query_info, null, 2);
|
|
showDebugInfo('Query information', queryText);
|
|
}
|
|
} catch (error) {
|
|
console.error('Error generating pivot chart', error);
|
|
showStatus(
|
|
'Failed to generate analysis: ' + error.message,
|
|
'error'
|
|
);
|
|
} finally {
|
|
showLoading(false);
|
|
elements.generateBtn.disabled = false;
|
|
}
|
|
}
|
|
|
|
function getSelectedTags() {
|
|
const inputs = elements.tagSelector.querySelectorAll(
|
|
'input[type="checkbox"]:checked'
|
|
);
|
|
return Array.from(inputs).map((input) => input.value);
|
|
}
|
|
|
|
function renderPivotChart() {
|
|
const canvas = elements.pivotChart;
|
|
const ctx = canvas.getContext('2d');
|
|
|
|
if (pivotChart) {
|
|
pivotChart.destroy();
|
|
pivotChart = null;
|
|
}
|
|
|
|
if (!currentData || !currentData.data.length) {
|
|
ctx.clearRect(0, 0, canvas.width, canvas.height);
|
|
ctx.fillStyle = '#7f8c8d';
|
|
ctx.font = '16px Arial';
|
|
ctx.textAlign = 'center';
|
|
ctx.fillText(
|
|
'No data to display',
|
|
canvas.width / 2,
|
|
canvas.height / 2
|
|
);
|
|
return;
|
|
}
|
|
|
|
const labels = currentData.timeGroups;
|
|
const palette = [
|
|
'#3498db',
|
|
'#e74c3c',
|
|
'#27ae60',
|
|
'#f39c12',
|
|
'#9b59b6',
|
|
'#1abc9c',
|
|
'#34495e',
|
|
'#e67e22',
|
|
'#2ecc71',
|
|
'#8e44ad',
|
|
];
|
|
|
|
const datasets = currentData.tagNames.map((tagName, index) => {
|
|
const color = palette[index % palette.length];
|
|
|
|
return {
|
|
label: tagName,
|
|
data: currentData.data.map((row) => row[tagName]),
|
|
borderColor: color,
|
|
backgroundColor: color + '33',
|
|
borderWidth: 2,
|
|
fill: false,
|
|
tension: 0.1,
|
|
pointRadius: 3,
|
|
pointHoverRadius: 5,
|
|
};
|
|
});
|
|
|
|
const chartType = elements.chartType.value;
|
|
const aggregateLabel = elements.aggregation.value.toUpperCase();
|
|
|
|
pivotChart = new Chart(ctx, {
|
|
type: chartType === 'area' ? 'line' : chartType,
|
|
data: {
|
|
labels,
|
|
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 (' + aggregateLabel + ')',
|
|
font: { weight: 'bold' },
|
|
},
|
|
},
|
|
},
|
|
plugins: {
|
|
legend: {
|
|
display: true,
|
|
position: 'top',
|
|
},
|
|
tooltip: {
|
|
mode: 'index',
|
|
intersect: false,
|
|
callbacks: {
|
|
title(context) {
|
|
return 'Time: ' + context[0].label;
|
|
},
|
|
label(context) {
|
|
const value = context.parsed.y;
|
|
if (value === null || value === undefined) {
|
|
return context.dataset.label + ': No data';
|
|
}
|
|
return (
|
|
context.dataset.label +
|
|
': ' +
|
|
Number(value).toFixed(2)
|
|
);
|
|
},
|
|
},
|
|
},
|
|
},
|
|
interaction: {
|
|
mode: 'nearest',
|
|
axis: 'x',
|
|
intersect: false,
|
|
},
|
|
},
|
|
});
|
|
|
|
if (chartType === 'area') {
|
|
pivotChart.data.datasets.forEach((dataset) => {
|
|
dataset.fill = true;
|
|
});
|
|
pivotChart.update();
|
|
}
|
|
}
|
|
|
|
function renderPivotTable() {
|
|
const head = elements.tableHead;
|
|
const body = elements.tableBody;
|
|
|
|
head.innerHTML = '';
|
|
body.innerHTML = '';
|
|
|
|
if (!currentData || !currentData.data.length) {
|
|
const headRow = document.createElement('tr');
|
|
const headCell = document.createElement('th');
|
|
headCell.textContent = 'No data available';
|
|
headRow.appendChild(headCell);
|
|
head.appendChild(headRow);
|
|
|
|
const bodyRow = document.createElement('tr');
|
|
const bodyCell = document.createElement('td');
|
|
bodyCell.textContent =
|
|
'No data found for the selected criteria.';
|
|
bodyRow.appendChild(bodyCell);
|
|
body.appendChild(bodyRow);
|
|
return;
|
|
}
|
|
|
|
const headRow = document.createElement('tr');
|
|
const timeHeader = document.createElement('th');
|
|
timeHeader.textContent = 'Time period';
|
|
headRow.appendChild(timeHeader);
|
|
|
|
currentData.tagNames.forEach((tagName) => {
|
|
const cell = document.createElement('th');
|
|
cell.textContent = tagName;
|
|
headRow.appendChild(cell);
|
|
});
|
|
|
|
head.appendChild(headRow);
|
|
|
|
const fragment = document.createDocumentFragment();
|
|
|
|
currentData.data.forEach((row) => {
|
|
const rowEl = document.createElement('tr');
|
|
|
|
const timeCell = document.createElement('td');
|
|
timeCell.className = 'timestamp';
|
|
timeCell.textContent = row.time_group;
|
|
rowEl.appendChild(timeCell);
|
|
|
|
currentData.tagNames.forEach((tagName) => {
|
|
const value = row[tagName];
|
|
const cell = document.createElement('td');
|
|
|
|
if (value === null || value === undefined) {
|
|
cell.className = 'null-cell';
|
|
cell.textContent = '--';
|
|
} else {
|
|
cell.className = 'numeric-cell';
|
|
cell.textContent = Number(value).toFixed(2);
|
|
}
|
|
|
|
rowEl.appendChild(cell);
|
|
});
|
|
|
|
fragment.appendChild(rowEl);
|
|
});
|
|
|
|
body.appendChild(fragment);
|
|
}
|
|
|
|
function updateChartType() {
|
|
if (currentData) {
|
|
renderPivotChart();
|
|
}
|
|
}
|
|
|
|
function showResults() {
|
|
elements.resultsContainer.classList.remove('is-hidden');
|
|
}
|
|
|
|
function clearResults() {
|
|
elements.resultsContainer.classList.add('is-hidden');
|
|
|
|
if (pivotChart) {
|
|
pivotChart.destroy();
|
|
pivotChart = null;
|
|
}
|
|
|
|
currentData = null;
|
|
enableExportButtons(false);
|
|
clearStatus();
|
|
hideDebugInfo();
|
|
}
|
|
|
|
function enableExportButtons(enabled) {
|
|
elements.exportCsvBtn.disabled = !enabled;
|
|
elements.exportJsonBtn.disabled = !enabled;
|
|
}
|
|
|
|
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');
|
|
}
|
|
}
|
|
|
|
function exportToCsv(filename) {
|
|
const header = ['Time period', ...currentData.tagNames];
|
|
let csv = header.join(',') + '\n';
|
|
|
|
currentData.data.forEach((row) => {
|
|
const values = currentData.tagNames.map((tagName) => {
|
|
const value = row[tagName];
|
|
return value === null || value === undefined
|
|
? ''
|
|
: Number(value).toFixed(2);
|
|
});
|
|
|
|
csv += '"' + row.time_group + '",' + values.join(',') + '\n';
|
|
});
|
|
|
|
downloadFile(csv, filename, 'text/csv');
|
|
showStatus('CSV exported successfully.', 'success');
|
|
}
|
|
|
|
function exportToJson(filename) {
|
|
const payload = {
|
|
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(payload, null, 2),
|
|
filename,
|
|
'application/json'
|
|
);
|
|
showStatus('JSON exported successfully.', 'success');
|
|
}
|
|
|
|
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);
|
|
}
|
|
|
|
function showLoading(show) {
|
|
elements.loadingOverlay.style.display = show ? 'flex' : 'none';
|
|
}
|
|
|
|
function showStatus(message, type) {
|
|
const container = elements.statusMessages;
|
|
container.innerHTML = '';
|
|
|
|
const status = document.createElement('div');
|
|
status.className = 'status-message status-message--' + type;
|
|
status.textContent = message;
|
|
|
|
container.appendChild(status);
|
|
|
|
if (type === 'success') {
|
|
setTimeout(() => {
|
|
if (status.parentNode === container) {
|
|
container.removeChild(status);
|
|
}
|
|
}, 5000);
|
|
}
|
|
}
|
|
|
|
function clearStatus() {
|
|
elements.statusMessages.innerHTML = '';
|
|
}
|
|
|
|
function showDebugInfo(title, content) {
|
|
elements.debugTitle.textContent = title;
|
|
elements.debugInfo.textContent = content;
|
|
elements.debugPanel.classList.remove('is-hidden');
|
|
}
|
|
|
|
function hideDebugInfo() {
|
|
elements.debugPanel.classList.add('is-hidden');
|
|
elements.debugTitle.textContent = '';
|
|
elements.debugInfo.textContent = '';
|
|
}
|
|
|
|
window.setQuickRange = setQuickRange;
|
|
window.selectAllTags = selectAllTags;
|
|
window.clearAllTags = clearAllTags;
|
|
window.generatePivotChart = generatePivotChart;
|
|
window.updateChartType = updateChartType;
|
|
window.exportData = exportData;
|
|
window.clearResults = clearResults;
|
|
window.hideDebugInfo = hideDebugInfo;
|
|
</script>
|
|
|
|
<?php require __DIR__ . '/../includes/layout/footer.php'; ?>
|