From b067eaf9e40282c5b30e8ab7fa5f5d5aa67e0093 Mon Sep 17 00:00:00 2001 From: Chuck Scott Date: Fri, 2 Sep 2016 12:21:36 -0400 Subject: [PATCH] Added full list of bandwidth usage by all monitored Websites to management. --- classes/serverBandwidthSupport.php | 152 ++++++++++- models/admin/ajax/serverBandwidthGraphs.php | 16 +- models/admin/management/serverStats.php | 44 +++- views/admin/dashboardWidget/serverStats.html | 2 +- views/admin/management/serverStats.html | 263 ++++++++++++++----- 5 files changed, 401 insertions(+), 76 deletions(-) diff --git a/classes/serverBandwidthSupport.php b/classes/serverBandwidthSupport.php index d1d3b7e..7aa34c4 100644 --- a/classes/serverBandwidthSupport.php +++ b/classes/serverBandwidthSupport.php @@ -36,6 +36,13 @@ class GlmServerBandwidthSupport * @access public */ public $siteId = false; + /** + * Sort full list by + * + * @var $sortBy + * @access public + */ + public $sortBy = false; public function __construct() { @@ -102,6 +109,138 @@ class GlmServerBandwidthSupport } + /* + * Get bandwidth stats for the current day, and month for all sites + */ + public function bandwidthGetAllSitesStats($sortByRequest = 'name') + { + $stats = array(); + + // Determine output table sort order + $this->sortBy = 'name'; + + if (in_array($sortByRequest, array('name', 'today', 'thisMonth', 'lastMonth'))) { + $this->sortBy = $sortByRequest; + } + + // Check for good database connection + if ($this->bwdb == false) { + return false; + } + + // Get stats so far for today in Megabytes + $today = date('Y-m-d 0:0:0.0'); + $tomorrow = date('Y-m-d 0:0:0.0', strtotime(date('Y-m-d')." + 1day")); + $sql = " + SELECT B.websitefk as id, + W.name as name, + COALESCE ( SUM(B.bytesin)/1000000, 0 ) as data_in, + COALESCE ( SUM(B.bytesout)/1000000, 0 ) as data_out, + COALESCE ( SUM(B.total)/1000000, 0 ) as data_total + FROM bytes B, website W + WHERE W.websitepk = B.websitefk + AND time BETWEEN '$today' AND '$tomorrow' + GROUP BY B.websitefk + ORDER BY W.name + ;"; + $todayStats = $this->bwdb->query($sql); + $todayStats->data_seek(0); + while ($row = $todayStats->fetch_assoc()) { + $stats[$row['id']]['today'] = $row; + } + + // Get stats for this month in Gigabytes + $firstDayOfMonth = date('Y-m-01'); + $lastDayOfMonth = date('Y-m-t'); + $sql = " + SELECT R.websitefk as id, + W.name as name, + COALESCE ( SUM(R.bytesin)/1000000000, 0 ) as data_in, + COALESCE ( SUM(R.bytesout)/1000000000, 0 ) as data_out, + COALESCE ( SUM(R.total)/1000000000, 0 ) as data_total + FROM rollup R, website W + WHERE W.websitepk = R.websitefk + AND date BETWEEN '$firstDayOfMonth' AND '$lastDayOfMonth' + GROUP BY R.websitefk + ORDER BY W.name + ;"; + $thisMonthStats = $this->bwdb->query($sql); + $thisMonthStats->data_seek(0); + while ($row = $thisMonthStats->fetch_assoc()) { + $stats[$row['id']]['thisMonth'] = $row; + } + + // Get stats for last month in Gigabytes + $firstDayOfMonth = date('Y-m-01', strtotime(date('Y-m-01').' -1 month')); + $lastDayOfMonth = date('Y-m-t', strtotime($firstDayOfMonth)); + $sql = " + SELECT R.websitefk as id, + W.name as name, + COALESCE ( SUM(R.bytesin)/1000000000, 0 ) as data_in, + COALESCE ( SUM(R.bytesout)/1000000000, 0 ) as data_out, + COALESCE ( SUM(total)/1000000000, 0 ) as data_total + FROM rollup R, website W + WHERE W.websitepk = R.websitefk + AND date BETWEEN '$firstDayOfMonth' AND '$lastDayOfMonth' + GROUP BY R.websitefk + ORDER BY W.name + ;"; + $lastMonthStats = $this->bwdb->query($sql); + $lastMonthStats->data_seek(0); + while ($row = $lastMonthStats->fetch_assoc()) { + $stats[$row['id']]['lastMonth'] = $row; + } + + // Scan all results to fill in empty data for situations where there was no results + reset($stats); + while (list($key, $value) = each($stats)) { + + // Get name from the first available data + if (isset($value['today'])) { + $name = $value['today']['name']; + } elseif (isset($value['thisMonth'])) { + $name = $value['thisMonth']['name']; + } elseif (isset($value['lastMonth'])) { + $name = $value['lastMonth']['name']; + } + $stats[$key]['name'] = $name; + $stats[$key]['id'] = $key; + + // Fill in any missing results + if (!isset($value['today'])) { + $stats[$key]['today'] = array('id' => $key, 'name' => $name, 'data_in' => 0, 'data_out' => 0, 'data_total' => 0); + } + if (!isset($value['thisMonth'])) { + $stats[$key]['thisMonth'] = array('id' => $key, 'name' => $name, 'data_in' => 0, 'data_out' => 0, 'data_total' => 0); + } + if (!isset($value['lastMonth'])) { + $stats[$key]['lastMonth'] = array('id' => $key, 'name' => $name, 'data_in' => 0, 'data_out' => 0, 'data_total' => 0); + } + + } + + // Sort output array as requested. + uasort($stats, array( $this, 'bwSort')); + + return $stats; + + } + + public function bwSort($a, $b) { + + if ($this->sortBy == 'name') { + if ($a['name'] == $b['name']) { + return 0; + } + return ($a['name'] < $b['name']) ? -1 : 1; + } else { + if ($a[$this->sortBy]['data_total'] == $b[$this->sortBy]['data_total']) { + return 0; + } + return ($a[$this->sortBy]['data_total'] < $b[$this->sortBy]['data_total']) ? 1 : -1; + } + } + /* * Get bandwidth stats for the current day, and month */ @@ -166,8 +305,13 @@ class GlmServerBandwidthSupport /* * Get bandwidth graph data for Today, Last two months, Last two years */ - public function bandwidthGetGraphData($graphType = false) + public function bandwidthGetGraphData($graphType = false, $siteId = false) { + + if (!$siteId) { + $siteId = $this->siteId; + } + $bandwidth = array('data_in' => array(), 'data_out' => array(), 'data_total' => array()); // Produce data for specified intervals @@ -184,7 +328,7 @@ class GlmServerBandwidthSupport bytesout/1000000 as data_out, total/1000000 as data_total FROM bytes - WHERE websitefk = ".$this->siteId." + WHERE websitefk = $siteId AND time BETWEEN '$startOfToday' AND '$endOfToday' ORDER BY time ASC ;"; @@ -210,7 +354,7 @@ class GlmServerBandwidthSupport bytesout/1000000000 as data_out, total/1000000000 as data_total FROM rollup - WHERE websitefk = ".$this->siteId." + WHERE websitefk = $siteId AND date BETWEEN '$firstDayOfMonth' AND '$lastDayOfMonth' ORDER BY date ASC ;"; @@ -236,7 +380,7 @@ class GlmServerBandwidthSupport COALESCE ( SUM(bytesout)/1000000000, 0 ) as data_out, COALESCE ( SUM(total)/1000000000, 0 ) as data_total FROM rollup - WHERE websitefk = ".$this->siteId." + WHERE websitefk = $siteId AND date BETWEEN '$firstMonth' AND '$lastMonth' GROUP BY YEAR(date), MONTH(date) ORDER BY date ASC diff --git a/models/admin/ajax/serverBandwidthGraphs.php b/models/admin/ajax/serverBandwidthGraphs.php index 35ee9e5..1b6006d 100644 --- a/models/admin/ajax/serverBandwidthGraphs.php +++ b/models/admin/ajax/serverBandwidthGraphs.php @@ -94,6 +94,16 @@ class GlmMembersAdmin_ajax_serverBandwidthGraphs extends GlmServerBandwidthSupp { $graphType = $_REQUEST['graphType']; + $siteId = false; + if (isset($_REQUEST['siteId'])) { + $siteId = $_REQUEST['siteId']-0; + } + + $noTarget = false; + if (isset($_REQUEST['noTarget'])) { + $noTarget = true; + } + $interval = 1; $useTarget = false; switch($graphType) { @@ -113,7 +123,7 @@ class GlmMembersAdmin_ajax_serverBandwidthGraphs extends GlmServerBandwidthSupp } // Get bandwidth data for today - $data = $this->bandwidthGetGraphData($graphType); + $data = $this->bandwidthGetGraphData($graphType, $siteId); // Load PHPGraphLib require_once GLM_MEMBERS_PLUGIN_LIB_PATH.'/phpgraphlib-master/phpgraphlib.php'; @@ -122,7 +132,7 @@ class GlmMembersAdmin_ajax_serverBandwidthGraphs extends GlmServerBandwidthSupp $graph = new PHPGraphLib(1200,300); $graph->setXValues(true); $graph->setYValues(true); - $graph->setMinMaxY(10); // Set minimum top value to Y axis + $graph->setMinMaxY(1); // Set minimum top value to Y axis $graph->setXValuesInterval($interval); $graph->setXValuesHorizontal(false); $graph->setLineColor("blue", "red", "green"); @@ -138,7 +148,7 @@ class GlmMembersAdmin_ajax_serverBandwidthGraphs extends GlmServerBandwidthSupp $graph->addData($data['data_out']); // Set target bandwidth line. Must be set after data sets - if ($useTarget) { + if ($useTarget && !$noTarget) { $graph->setGoalLine(($this->target/1000), "black", "dashed"); } diff --git a/models/admin/management/serverStats.php b/models/admin/management/serverStats.php index 882048a..da83e67 100644 --- a/models/admin/management/serverStats.php +++ b/models/admin/management/serverStats.php @@ -80,10 +80,13 @@ class GlmMembersAdmin_management_serverStats extends GlmDataServerStatsManagemen public function modelAction($actionData = false) { + + $option = 'settings'; $serverStatsSettigns = false; $settingsUpdated = false; $settingsUpdateError = false; $dbError = false; + $sites = false; // Determine if current user can edit configurations @@ -100,8 +103,7 @@ class GlmMembersAdmin_management_serverStats extends GlmDataServerStatsManagemen } // Check for submission option - $option = ''; - if (isset($_REQUEST['option']) && $_REQUEST['option'] == 'submit') { + if (isset($_REQUEST['option'])) { $option = $_REQUEST['option']; } @@ -135,7 +137,39 @@ class GlmMembersAdmin_management_serverStats extends GlmDataServerStatsManagemen break; + case 'listSites': + + $serverStatsSettings = $this->getEntry(1); + + // Connect to the database + require_once GLM_MEMBERS_SERVERSTATS_PLUGIN_CLASS_PATH.'/serverBandwidthSupport.php'; + $BandwidthStats = new GlmServerBandwidthSupport(); + $res = $BandwidthStats->bandwidthDataConnect( + $serverStatsSettings['db_host'], + $serverStatsSettings['db_user'], + $serverStatsSettings['db_pass'], + $serverStatsSettings['db_name'], + $serverStatsSettings['website'] + ); + + // If no connection + if ($res != '') { + $dbError = $res." "; + } + + // Get sorting option + $sortBy = 'name'; + if (isset($_REQUEST['sortBy'])) { + $sortBy = trim($_REQUEST['sortBy']); + } + + // Produce summary data for all sites in the bandwidth database + $sites = $BandwidthStats->bandwidthGetAllSitesStats($sortBy); + + break; + // Default is to get the current settings and display the form + case 'settings': default: // Try to get the first (should be only) entry for general settings. @@ -155,11 +189,15 @@ class GlmMembersAdmin_management_serverStats extends GlmDataServerStatsManagemen // Compile template data $templateData = array( + 'option' => $option, + 'sortBy' => $sortBy, 'reason' => '', 'serverStatsSettings' => $serverStatsSettings, 'settingsUpdated' => $settingsUpdated, 'settingsUpdateError' => $settingsUpdateError, - 'dbError' => $dbError + 'dbError' => $dbError, + 'sites' => $sites, + 'thisDate' => date('m/d/Y') ); // Return status, suggested view, and data to controller diff --git a/views/admin/dashboardWidget/serverStats.html b/views/admin/dashboardWidget/serverStats.html index db4a08f..642ecfc 100644 --- a/views/admin/dashboardWidget/serverStats.html +++ b/views/admin/dashboardWidget/serverStats.html @@ -90,7 +90,7 @@ var position = $(this).offset(); $('#' + areaToPrint).printArea({ mode: 'popup', //printable window is either iframe or browser popup - popHt: 500, // popup window height + popHt: 1200, // popup window height popWd: 1200, // popup window width popX: 200, // popup window screen X position popY: 200, //popup window screen Y position diff --git a/views/admin/management/serverStats.html b/views/admin/management/serverStats.html index fb3484b..73e9bcf 100644 --- a/views/admin/management/serverStats.html +++ b/views/admin/management/serverStats.html @@ -1,82 +1,215 @@ {include file='admin/management/header.html'} - {if $dbError != ''} -

Unable to test database connection!

-

Error reported: {$dbError}

- {/if} -
- - +

+ Server Settings + Web Site Bandwidth Consumed +

+ + +
+ +{if $option == 'settings'} + {if $dbError != ''} +

Unable to test database connection!

+

Error reported: {$dbError}

+ {/if} + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + +
+ {if $settingsUpdated}

Settings Updated

{/if} + {if $settingsUpdateError}Settings Update Error{/if} +
Enabled: + +
Database Name: + + {if $serverStatsSettings.fieldFail.db_name}

{$serverStatsSettings.fieldFail.db_name}

{/if} +
Database Server Hostname: + + {if $serverStatsSettings.fieldFail.db_host}

{$serverStatsSettings.fieldFail.db_host}

{/if} +
Database Username: + + {if $serverStatsSettings.fieldFail.db_user}

{$serverStatsSettings.fieldFail.db_user}

{/if} +
Database Password: + + {if $serverStatsSettings.fieldFail.db_pass}

{$serverStatsSettings.fieldFail.db_pass}

{/if} +
Website Name: + +
This is the name of the Website as it's listed in the server stats database. Normally does not include 'www'. + {if $serverStatsSettings.fieldFail.website}

{$serverStatsSettings.fieldFail.website}

{/if} +
Monthly Target Bandwidth: + +
Monthly target bandwidth is entered in Megabytes without any fractions. + {if $serverStatsSettings.fieldFail.target}

{$serverStatsSettings.fieldFail.target}

{/if} +
+ + +{/if} - + + + +
+ +

+
+ + + Sort by: + + + +

- + +
+
+

+

+ {$thisDate} + Server Bandwidth Stats for Today by 10 Min Intervals - Megabytes +

+ +

+ {$thisDate} + Server Bandwidth Stats for last 2 Months by Day - Gigabytes +

+ +

+ {$thisDate} + Server Bandwidth Stats for last 2 Years by Month - Gigabytes +

+ +
Color Key: Incoming, Outgoing, Total
+
+
Print
+
+ -
- - - - - - - - - - - - - - - - - +{if $sites} +
- {if $settingsUpdated}

Settings Updated

{/if} - {if $settingsUpdateError}Settings Update Error{/if} -

General Server Stats Settings

-
Enabled: - -
Database Name: - - {if $serverStatsSettings.fieldFail.db_name}

{$serverStatsSettings.fieldFail.db_name}

{/if} -
Database Server Hostname: - - {if $serverStatsSettings.fieldFail.db_host}

{$serverStatsSettings.fieldFail.db_host}

{/if} -
Database Username: - - {if $serverStatsSettings.fieldFail.db_user}

{$serverStatsSettings.fieldFail.db_user}

{/if} -
+ + + + + + + {foreach $sites as $s} - - - - - - - - - - + + + + + + {/foreach}
Site  Today (MB)  This Month (GB)  Last Month (GB) 
Database Password: - - {if $serverStatsSettings.fieldFail.db_pass}

{$serverStatsSettings.fieldFail.db_pass}

{/if} -
Website Name: - -
This is the name of the Website as it's listed in the server stats database. Normally does not include 'www'. - {if $serverStatsSettings.fieldFail.website}

{$serverStatsSettings.fieldFail.website}

{/if} -
Monthly Target Bandwidth: - -
Monthly target bandwidth is entered in Megabytes without any fractions. - {if $serverStatsSettings.fieldFail.target}

{$serverStatsSettings.fieldFail.target}

{/if} - 		{$s.name} ({$s.id}){$s.today.data_total|number_format:3:".":","}{$s.thisMonth.data_total|number_format:3:".":","}{$s.lastMonth.data_total|number_format:3:".":","}
- - + +{/if} +