1 files changed, 102 insertions, 0 deletions
diff --git a/library/Director/Web/Table/CustomvarTable.php b/library/Director/Web/Table/CustomvarTable.php
new file mode 100644
index 0000000..f9a3844
--- /dev/null
+++ b/library/Director/Web/Table/CustomvarTable.php
@@ -0,0 +1,102 @@
+<?php
+
+namespace Icinga\Module\Director\Web\Table;
+
+use ipl\Html\Html;
+use gipfl\IcingaWeb2\Link;
+use gipfl\IcingaWeb2\Table\ZfQueryBasedTable;
+use Zend_Db_Adapter_Abstract as ZfDbAdapter;
+use Zend_Db_Select as ZfDbSelect;
+
+class CustomvarTable extends ZfQueryBasedTable
+{
+    protected $searchColumns = array(
+        'varname',
+    );
+
+    public function renderRow($row)
+    {
+        $tr = $this::row([
+            new Link(
+                $row->varname,
+                'director/customvar/variants',
+                ['name' => $row->varname]
+            )
+        ]);
+
+        foreach ($this->getObjectTypes() as $type) {
+            $tr->add($this::td(Html::tag('nobr', null, sprintf(
+                $this->translate('%d / %d'),
+                $row->{"cnt_$type"},
+                $row->{"distinct_$type"}
+            ))));
+        }
+
+        return $tr;
+    }
+
+    public function getColumnsToBeRendered()
+    {
+        return array(
+            $this->translate('Variable name'),
+            $this->translate('Distinct Commands'),
+            $this->translate('Hosts'),
+            $this->translate('Services'),
+            $this->translate('Service Sets'),
+            $this->translate('Notifications'),
+            $this->translate('Users'),
+        );
+    }
+
+    protected function getObjectTypes()
+    {
+        return ['command', 'host', 'service', 'service_set', 'notification', 'user'];
+    }
+
+    public function prepareQuery()
+    {
+        $db = $this->db();
+        $varsColumns = ['varname' => 'v.varname'];
+        $varsTypes = $this->getObjectTypes();
+        foreach ($varsTypes as $type) {
+            $varsColumns["cnt_$type"] = '(0)';
+            $varsColumns["distinct_$type"] = '(0)';
+        }
+        $varsQueries = [];
+        foreach ($varsTypes as $type) {
+            $varsQueries[] = $this->makeVarSub($type, $varsColumns, $db);
+        }
+
+        $union = $db->select()->union($varsQueries, ZfDbSelect::SQL_UNION_ALL);
+
+        $columns = ['varname' => 'u.varname'];
+        foreach ($varsTypes as $column) {
+            $columns["cnt_$column"] = "SUM(u.cnt_$column)";
+            $columns["distinct_$column"] = "SUM(u.distinct_$column)";
+        }
+        return $db->select()->from(
+            array('u' => $union),
+            $columns
+        )->group('u.varname')->order('u.varname ASC')->limit(100);
+    }
+
+    /**
+     * @param string $type
+     * @param array $columns
+     * @param ZfDbAdapter $db
+     * @return ZfDbSelect
+     */
+    protected function makeVarSub($type, array $columns, ZfDbAdapter $db)
+    {
+        $columns["cnt_$type"] = 'COUNT(*)';
+        $columns["distinct_$type"] = 'COUNT(DISTINCT varvalue)';
+        return $db->select()->from(
+            ['v' => "icinga_${type}_var"],
+            $columns
+        )->join(
+            ['o' => "icinga_${type}"],
+            "o.id = v.${type}_id",
+            []
+        )->where('o.object_type != ?', 'external_object')->group('varname');
+    }
+}