From b5fce4e414842b47d8729af206d5e020dcf6479a Mon Sep 17 00:00:00 2001
From: Erich Eckner <git@eckner.net>
Date: Thu, 26 Jul 2018 10:17:46 +0200
Subject: buildmaster/status.php: show average age of testing packages

---
 buildmaster/status.php | 34 ++++++++++++++++++++
 lib/converter.php      | 86 ++++++++++++++++++++++++++++++++++++++++++++++++++
 2 files changed, 120 insertions(+)
 create mode 100644 lib/converter.php

diff --git a/buildmaster/status.php b/buildmaster/status.php
index edb4455..5a5cf4a 100644
--- a/buildmaster/status.php
+++ b/buildmaster/status.php
@@ -2,6 +2,7 @@
 require_once "../init.php";
 include BASE . "/lib/mysql.php";
 include BASE . "/lib/style.php";
+include BASE . "/lib/converter.php";
 
 $result = mysql_run_query(
   "SELECT MAX(`package_sources`.`commit_time`) AS `last_commit`" .
@@ -36,6 +37,34 @@ if ($result -> num_rows > 0) {
   $last_moved = $result["last_moved"];
 }
 
+$result = mysql_run_query(
+  "SELECT " .
+  "STDDEV(UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(`binary_packages_in_repositories`.`first_last_moved`)) AS `stddev`," .
+  "AVG(UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(`binary_packages_in_repositories`.`first_last_moved`)) AS `avg`" .
+  " FROM `binary_packages`" .
+  " JOIN (" .
+    "SELECT " .
+    "`binary_packages_in_repositories`.`package`," .
+    "MIN(`binary_packages_in_repositories`.`last_moved`) AS `first_last_moved`" .
+    " FROM `binary_packages_in_repositories`" .
+    " JOIN `repositories`" .
+    " ON `binary_packages_in_repositories`.`repository`=`repositories`.`id`" .
+    " JOIN `repository_stabilities`" .
+    " ON `repositories`.`stability`=`repository_stabilities`.`id`" .
+    " WHERE `repository_stabilities`.`name`=\"testing\"" .
+    " GROUP BY `binary_packages_in_repositories`.`package`" .
+  ") AS `binary_packages_in_repositories`" .
+  " ON `binary_packages_in_repositories`.`package`=`binary_packages`.`id`" .
+  " WHERE NOT `binary_packages`.`has_issues`" .
+  " AND NOT `binary_packages`.`is_tested`"
+);
+
+if ($result -> num_rows > 0) {
+  $result = $result->fetch_assoc();
+  foreach ($result as $key => $val)
+    $testing[$key] = format_time_duration($val);
+}
+
 print_header("Build Master Status");
 
 if (isset($last_commit))
@@ -47,4 +76,9 @@ if (isset($last_return))
 if (isset($last_moved))
   print "      latest package move was on " . $last_moved . ".<br>\n";
 
+if (isset($testing))
+  print "      age of testing-packages: " . 
+    $testing["avg"] . " &pm; " . 
+    $testing["stddev"] . ".<br>\n";
+
 print_footer();
diff --git a/lib/converter.php b/lib/converter.php
new file mode 100644
index 0000000..41951c7
--- /dev/null
+++ b/lib/converter.php
@@ -0,0 +1,86 @@
+<?php
+
+# do not include twice
+if (function_exists("format_time_duration"))
+  return;
+
+require_once "../init.php";
+
+function format_time_duration($val) {
+  $val = floor($val);
+  $result = "";
+  $result =
+    sprintf(
+      "%02d",
+      $val % 60
+    );
+  $val = floor($val / 60);
+  if ($val == 0)
+    return $result;
+  $result =
+    sprintf(
+      "%02d:%s",
+      $val % 60,
+      $result
+    );
+  $val = floor($val / 60);
+  if ($val == 0)
+    return $result;
+  $result =
+    sprintf(
+      "%d:%s",
+      $val % 24,
+      $result
+    );
+  $val = floor($val / 24);
+  if ($val == 0)
+    return $result;
+  $tmp = $val % 7;
+  $printed_conjunction = true;
+  if ($tmp > 1)
+    $result =
+      sprintf(
+        "%d days and %s",
+        $tmp,
+        $result
+      );
+  elseif ($tmp == 1)
+    $result =
+      sprintf(
+        "%d day and %s",
+        $tmp,
+        $result
+      );
+  else
+    $printed_conjunction = false;
+  $val = floor($val / 7);
+  if ($val == 0)
+    return $result;
+  if ($printed_conjunction)
+    $result =
+      sprintf(
+        ", %s",
+        $result
+      );
+  else
+    $result =
+      sprintf(
+        " and %s",
+        $result
+      );
+  if ($val>1)
+    $result =
+      sprintf(
+        "%d weeks%s",
+        $val,
+        $result
+      );
+  else
+    $result =
+      sprintf(
+        "%d week%s",
+        $val,
+        $result
+      );
+  return $result;
+}
-- 
cgit v1.2.3-70-g09d2