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 = << '' 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'; ?>

Pivot analysis

Group historian tags by time bucket, compare aggregates, and export the results.

Tag selection

Choose one or more historian tags to include in the analysis.

Loading tags…

Time range

Quick ranges

Aggregation