'192.168.0.16', 'database' => 'lasucaai', 'username' => 'lasucaai', 'password' => 'is413#dfslw', ]; // ============================================================================ // Database Connection // ============================================================================ function getMillDataConnection($config) { $connectionOptions = [ "Database" => $config['database'], "Uid" => $config['username'], "PWD" => $config['password'], "TrustServerCertificate" => true, "Encrypt" => false, ]; $conn = sqlsrv_connect($config['server'], $connectionOptions); if ($conn === false) { return null; } return $conn; } // ============================================================================ // Data Queries // ============================================================================ function getMillReports($conn) { $sql = "SELECT ReportId, SourceFileName, MillName, ReportTitle, BeginningDate, EndingDate, CropDays, ProcessedAt FROM dbo.MillDataReports ORDER BY ProcessedAt DESC"; $stmt = sqlsrv_query($conn, $sql); $reports = []; while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) { $reports[] = $row; } return $reports; } function getMillSourceFiles($conn) { $sql = "SELECT DISTINCT SourceFileName, MIN(BeginningDate) as BeginningDate, MAX(EndingDate) as EndingDate, MAX(ProcessedAt) as ProcessedAt FROM dbo.MillDataReports GROUP BY SourceFileName ORDER BY MAX(ProcessedAt) DESC"; $stmt = sqlsrv_query($conn, $sql); $files = []; while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) { $files[] = $row; } return $files; } function getMillsForSourceFile($conn, $sourceFileName) { $sql = "SELECT ReportId, MillName FROM dbo.MillDataReports WHERE SourceFileName = ? ORDER BY MillName"; $stmt = sqlsrv_query($conn, $sql, [$sourceFileName]); if ($stmt === false) { return []; } $mills = []; while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) { $mills[] = $row; } return $mills; } function getMillMetrics($conn, $reportId, $search = '', $category = '') { $params = [$reportId]; $sql = "SELECT MetricId, ItemNumber, MetricName, RunValue, RunValueNumeric, ToDateValue, ToDateValueNumeric, Unit, Category FROM dbo.MillDataMetrics WHERE ReportId = ?"; if (!empty($search)) { $sql .= " AND MetricName LIKE ?"; $params[] = '%' . $search . '%'; } if (!empty($category)) { $sql .= " AND Category = ?"; $params[] = $category; } $sql .= " ORDER BY MetricId"; $stmt = sqlsrv_query($conn, $sql, $params); $metrics = []; while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) { $metrics[] = $row; } return $metrics; } function getMillCategories($conn) { $sql = "SELECT DISTINCT Category FROM dbo.MillDataMetrics WHERE Category IS NOT NULL ORDER BY Category"; $stmt = sqlsrv_query($conn, $sql); $categories = []; while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) { $categories[] = $row['Category']; } return $categories; } function getMillReportStats($conn) { $sql = "SELECT COUNT(DISTINCT r.ReportId) as TotalReports, COUNT(m.MetricId) as TotalMetrics, MIN(r.BeginningDate) as EarliestDate, MAX(r.EndingDate) as LatestDate FROM dbo.MillDataReports r LEFT JOIN dbo.MillDataMetrics m ON r.ReportId = m.ReportId"; $stmt = sqlsrv_query($conn, $sql); return sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC); } function formatMillDate($date) { if ($date instanceof DateTime) { return $date->format('m/d/Y'); } return $date ?? '-'; } // ============================================================================ // Main Logic // ============================================================================ $conn = getMillDataConnection($config); $connectionError = $conn === null; $reports = []; $sourceFiles = []; $millsForFile = []; $categories = []; $stats = ['TotalReports' => 0, 'TotalMetrics' => 0]; $metrics = []; $selectedReport = null; $selectedFile = ''; $selectedFileInfo = null; $millNameLookup = []; if (!$connectionError) { // Load mill name mappings $millNameLookup = getMillNames($conn); $selectedFile = isset($_GET['file']) ? trim($_GET['file']) : ''; $selectedReportId = isset($_GET['report']) ? intval($_GET['report']) : 0; $searchTerm = isset($_GET['search']) ? trim($_GET['search']) : ''; $selectedCategory = isset($_GET['category']) ? trim($_GET['category']) : ''; $reports = getMillReports($conn); $sourceFiles = getMillSourceFiles($conn); $categories = getMillCategories($conn); $stats = getMillReportStats($conn); // Default to first file if none selected if (empty($selectedFile) && !empty($sourceFiles)) { $selectedFile = $sourceFiles[0]['SourceFileName']; } // Get mills for selected file $millsForFile = !empty($selectedFile) ? getMillsForSourceFile($conn, $selectedFile) : []; // Default to first mill if no report selected if ($selectedReportId === 0 && !empty($millsForFile)) { $selectedReportId = $millsForFile[0]['ReportId']; } // Validate report belongs to selected file $validReport = false; foreach ($millsForFile as $m) { if ($m['ReportId'] == $selectedReportId) { $validReport = true; break; } } if (!$validReport && !empty($millsForFile)) { $selectedReportId = $millsForFile[0]['ReportId']; } $metrics = $selectedReportId ? getMillMetrics($conn, $selectedReportId, $searchTerm, $selectedCategory) : []; // Find selected report details foreach ($reports as $r) { if ($r['ReportId'] == $selectedReportId) { $selectedReport = $r; break; } } // Find selected file details foreach ($sourceFiles as $f) { if ($f['SourceFileName'] == $selectedFile) { $selectedFileInfo = $f; break; } } sqlsrv_close($conn); } else { $selectedReportId = 0; $searchTerm = ''; $selectedCategory = ''; } // ============================================================================ // Page Layout // ============================================================================ $pageTitle = 'Mill Data Viewer'; $pageSubtitle = 'Sugar Mill Production Report Data'; $pageDescription = 'View extracted mill production data from daily reports.'; $layoutWithoutSidebar = true; $layoutReturnUrl = '../overview.php'; $layoutReturnLabel = 'Back to overview'; $assetBasePath = '../'; require __DIR__ . '/../includes/layout/header.php'; ?>
Unable to connect to the database. Please check the connection settings.
Mills
Metrics
Earliest
Latest
🔀 Compare Mills 📊 Dashboard

📅 - 🏭
Clear
Showing metrics matching ""
Item Metric RUN TO DATE Category
No metrics found

Select a report from the sidebar to view metrics