From 8d91758c7c606f8d31a559df39bb3af9c84bcd59 Mon Sep 17 00:00:00 2001 From: Chuck Scott Date: Tue, 11 Oct 2016 16:35:18 -0400 Subject: [PATCH] Added date selection and data export to site bandwidth graphs. Updated datetimepicker. --- classes/serverBandwidthSupport.php | 167 +++++++++++++++++-- models/admin/ajax/serverBandwidthGraphs.php | 94 ++++++++++- models/admin/management/serverStats.php | 21 ++- views/admin/dashboardWidget/serverStats.html | 49 ++++-- views/admin/management/serverStats.html | 16 +- 5 files changed, 295 insertions(+), 52 deletions(-) diff --git a/classes/serverBandwidthSupport.php b/classes/serverBandwidthSupport.php index 7aa34c4..3b7c8fd 100644 --- a/classes/serverBandwidthSupport.php +++ b/classes/serverBandwidthSupport.php @@ -91,6 +91,8 @@ class GlmServerBandwidthSupport public function bandwidthGetWebsiteID($website) { + $this->siteId = false; + // Get Website ID $sql = " SELECT websitepk @@ -98,14 +100,17 @@ class GlmServerBandwidthSupport WHERE name = '$website' ;"; $websiteResult = $this->bwdb->query($sql); - if (!$websiteResult) { - trigger_error("Website ID query failed: " . mysqli_error($bwdb) ); - } - $row = mysqli_fetch_assoc($websiteResult); - if (!$row){ - trigger_error("Website '$website' not found."); + if ($websiteResult) { + + // We have a result, so try to get the site id + $row = mysqli_fetch_assoc($websiteResult); + if ($row){ + $this->siteId = $row['websitepk']; + } + } - $this->siteId = $row['websitepk']; + + return $this->siteId; } @@ -305,7 +310,7 @@ class GlmServerBandwidthSupport /* * Get bandwidth graph data for Today, Last two months, Last two years */ - public function bandwidthGetGraphData($graphType = false, $siteId = false) + public function bandwidthGetGraphData($graphType = false, $siteId = false, $refDate = false) { if (!$siteId) { @@ -314,14 +319,22 @@ class GlmServerBandwidthSupport $bandwidth = array('data_in' => array(), 'data_out' => array(), 'data_total' => array()); + // If reference Date is not supplied, use today + if (!$refDate) { + $refDate = date('m/d/Y'); + } + $refDateTime = strtotime($refDate); + // Produce data for specified intervals switch ($graphType) { case 'twoDay': // Get data for today in Megabytes (10 min intervals) - $startOfToday = date('Y-m-d 0:0:0.0', strtotime('today - 1day')); - $endOfToday = date('Y-m-d 23:59:59.9999'); + $startOfYesterday = date('Y-m-d 0:0:0', strtotime($refDate.' - 1day')); + $endOfToday = date('Y-m-d 23:59:59', $refDateTime); + $bandwidth['start'] = $startOfYesterday; + $bandwidth['end'] = $endOfToday; $sql = " SELECT time, bytesin/1000000 as data_in, @@ -329,25 +342,41 @@ class GlmServerBandwidthSupport total/1000000 as data_total FROM bytes WHERE websitefk = $siteId - AND time BETWEEN '$startOfToday' AND '$endOfToday' + AND time BETWEEN '$startOfYesterday' AND '$endOfToday' ORDER BY time ASC ;"; $dayData = $this->bwdb->query($sql); $dayData->data_seek(0); while ($row = $dayData->fetch_assoc()) { - $time = date('d-H:i', strtotime($row['time'])); + $time = date('Y-m-d-H:i', strtotime($row['time'])); $bandwidth['data_in'][$time] = $row['data_in']; $bandwidth['data_out'][$time] = $row['data_out']; $bandwidth['data_total'][$time] = $row['data_total']; } + // Also get the site name + $sql = " + SELECT * + FROM website + WHERE websitepk = $siteId + ;"; + $targetRes = $this->bwdb->query($sql); + $siteName = ''; + if ($targetRes) { + $targetData = mysqli_fetch_assoc($targetRes); + $siteName = $targetData['name']; + } + $bandwidth['site_name'] = $siteName; + break; case 'twoMonth': // Get data for this month in Gigabytes (1 day intervals) - $firstDayOfMonth = date('Y-m-01', strtotime('today - 1month')); - $lastDayOfMonth = date('Y-m-t'); + $firstDayOfMonth = date('Y-m-01', strtotime($refDate.' - 1month')); + $lastDayOfMonth = date('Y-m-t', $refDateTime); + $bandwidth['start'] = $firstDayOfMonth; + $bandwidth['end'] = $lastDayOfMonth; $sql = " SELECT date as time, bytesin/1000000000 as data_in, @@ -361,19 +390,35 @@ class GlmServerBandwidthSupport $monthData = $this->bwdb->query($sql); $monthData->data_seek(0); while ($row = $monthData->fetch_assoc()) { - $time = date('m-d', strtotime($row['time'])); + $time = date('Y-m-d', strtotime($row['time'])); $bandwidth['data_in'][$time] = $row['data_in']; $bandwidth['data_out'][$time] = $row['data_out']; $bandwidth['data_total'][$time] = $row['data_total']; } + // Also get the site name + $sql = " + SELECT * + FROM website + WHERE websitepk = $siteId + ;"; + $targetRes = $this->bwdb->query($sql); + $siteName = ''; + if ($targetRes) { + $targetData = mysqli_fetch_assoc($targetRes); + $siteName = $targetData['name']; + } + $bandwidth['site_name'] = $siteName; + break; case 'twoYear': // Get stats for this year and last year in Gigabytes - $firstMonth = date('Y-01-01', strtotime('today -1 year')); - $lastMonth = date('Y-12-31'); + $firstMonth = date('Y-01-01', strtotime($refDate.' -1 year')); + $lastMonth = date('Y-12-31', $refDateTime); + $bandwidth['start'] = $firstMonth; + $bandwidth['end'] = $lastMonth; $sql = " SELECT date as time, COALESCE ( SUM(bytesin)/1000000000, 0 ) as data_in, @@ -394,6 +439,70 @@ class GlmServerBandwidthSupport $bandwidth['data_total'][$time] = $row['data_total']; } + // Also get the target bandwidth + $sql = " + SELECT * + FROM website + WHERE websitepk = $siteId + ;"; + $targetRes = $this->bwdb->query($sql); + $target = false; + $siteName = ''; + if ($targetRes) { + $targetData = mysqli_fetch_assoc($targetRes); + $target = $targetData['target']; + $siteName = $targetData['name']; + } + $bandwidth['target'] = $target; + $bandwidth['site_name'] = $siteName; + + break; + + case 'allDays': + + // Get stats for this year and last year in Gigabytes + $sql = " + SELECT date as time, + bytesin/1000000000 as data_in, + bytesout/1000000000 as data_out, + total/1000000000 as data_total + FROM rollup + WHERE websitefk = $siteId + ORDER BY date ASC + ;"; + $allData = $this->bwdb->query($sql); + $allData->data_seek(0); + while ($row = $allData->fetch_assoc()) { + $time = date('Y-m-d', strtotime($row['time'])); + $bandwidth['data_in'][$time] = $row['data_in']; + $bandwidth['data_out'][$time] = $row['data_out']; + $bandwidth['data_total'][$time] = $row['data_total']; + } + + break; + + case 'allMonths': + + // Get stats for this year and last year in Gigabytes + $sql = " + SELECT date as time, + COALESCE ( SUM(bytesin)/1000000000, 0 ) as data_in, + COALESCE ( SUM(bytesout)/1000000000, 0 ) as data_out, + COALESCE ( SUM(total)/1000000000, 0 ) as data_total + FROM rollup + WHERE websitefk = $siteId + GROUP BY YEAR(date), MONTH(date) + ORDER BY date ASC + ;"; + + $allData = $this->bwdb->query($sql); + $allData->data_seek(0); + while ($row = $allData->fetch_assoc()) { + $time = date('Y-m', strtotime($row['time'])); + $bandwidth['data_in'][$time] = $row['data_in']; + $bandwidth['data_out'][$time] = $row['data_out']; + $bandwidth['data_total'][$time] = $row['data_total']; + } break; default: @@ -408,5 +517,29 @@ class GlmServerBandwidthSupport } + /* + * Store target monthly (billed) bandwidth in "target" in "website" table. + */ + public function bandwidthSetTarget($website, $target) + { + + // Get the Website ID for the supplied website name + $websiteId = $this->bandwidthGetWebsiteID($website); + + // Make sure both Website ID and Target are integers + $target = intval( $target - 0 ); + + // Write target value to specified Website record + $sql = " + UPDATE website + SET target = $target + WHERE websitepk = $websiteId + ;"; + $this->bwdb->query($sql); + + return; + + } + } \ No newline at end of file diff --git a/models/admin/ajax/serverBandwidthGraphs.php b/models/admin/ajax/serverBandwidthGraphs.php index 1b6006d..6fa917d 100644 --- a/models/admin/ajax/serverBandwidthGraphs.php +++ b/models/admin/ajax/serverBandwidthGraphs.php @@ -104,26 +104,108 @@ class GlmMembersAdmin_ajax_serverBandwidthGraphs extends GlmServerBandwidthSupp $noTarget = true; } + // Set reference Date + $refDate = date('m/d/Y'); + if (isset($_REQUEST['refDate'])) { + $refDate = $_REQUEST['refDate']; + } + $interval = 1; $useTarget = false; switch($graphType) { + case 'twoDay': $interval = 8; + $title = 'Server bandwidth usage by 10 minute Intervals in Megabytes'; break; + case 'twoMonth': $interval = 2; + $title = 'Server bandwidth usage by 1 day intervals in Gigabytes'; break; + case 'twoYear': $interval = 1; $useTarget = true; + $title = 'Server bandwidth usage by 1 month Intervals in Gigabytes'; + break; + + case 'export': + + if (isset($_GET['option'])) { + $graphOption = $_GET['option']; + } else { + exit; + } + + $resData = $this->bandwidthGetGraphData($graphOption, $siteId); + + $data = array(); + + $graphTitle = 'Month'; + $graphDateFormat = 'Y-m'; + if ($graphOption == 'allDays') { + $graphTitle = 'Day'; + $graphDateFormat = 'Y-m-d'; + } + + if (count($resData) > 0) { + + // Find min and max dates + $minDate = false; + $maxDate = false; + foreach ($resData['data_total'] as $date=>$val) { + if (!$minDate || $date < $minDate) { + $minDate = $date; + } + if (!$maxDate || $date > $maxDate) { + $maxDate = $date; + } + } + + // Build default date values + $interval = ($graphOption=='allDays'?'day':'month'); + + $data = array(); + for ($t=strtotime($minDate) ; $t<=strtotime($maxDate) ; $t=strtotime(date('Y-m-d',$t).' + 1 '.$interval) ) { + $d = date($graphDateFormat,$t); + $data[$d] = array( + 'data_in' => (isset($resData['data_in'][$d])?$resData['data_in'][$d]:0), + 'data_out' => (isset($resData['data_out'][$d])?$resData['data_out'][$d]:0), + 'data_total' => (isset($resData['data_total'][$d])?$resData['data_total'][$d]:0) + ); + } + + } else { + exit; + } + + $csv = '"'.$graphTitle.'","Data In","Data Out","Data Total","","All data in GigaBytes"'."\n"; + foreach ($data as $date => $val) { + $csv .= '"'.date($graphDateFormat,strtotime($date)).'","'.$val['data_in'].'","'.$val['data_out'].'","'.$val['data_total'].'"'."\n"; + } + + $filename = 'bandwidth_consumed_by_'.strtolower($graphTitle); + header('Content-type: application/octet-stream'); + header('Content-Disposition: filename="'.$filename.'.csv"'); + header("Content-length: ".strlen($csv)); + echo $csv; + exit; + + + + exit; + break; + default: die("Invalid bandwidth graph type: $graphType"); break; + } // Get bandwidth data for today - $data = $this->bandwidthGetGraphData($graphType, $siteId); + $data = $this->bandwidthGetGraphData($graphType, $siteId, $refDate); // Load PHPGraphLib require_once GLM_MEMBERS_PLUGIN_LIB_PATH.'/phpgraphlib-master/phpgraphlib.php'; @@ -138,7 +220,7 @@ class GlmMembersAdmin_ajax_serverBandwidthGraphs extends GlmServerBandwidthSupp $graph->setLineColor("blue", "red", "green"); $graph->setDataPointSize(4); $graph->setTextColor("black"); - $graph->setBackgroundColor('245,245,245'); +// $graph->setBackgroundColor('249,249,249'); $graph->setLine(true); $graph->setBars(false); $graph->setDataPoints(false); @@ -147,12 +229,18 @@ class GlmMembersAdmin_ajax_serverBandwidthGraphs extends GlmServerBandwidthSupp $graph->addData($data['data_in']); $graph->addData($data['data_out']); + // Check if we received a target value with our graph data + if ($useTarget && isset($data['target']) && $data['target'] > 0) { + $this->target = $data['target']; + $noTarget = false; + } + // Set target bandwidth line. Must be set after data sets if ($useTarget && !$noTarget) { $graph->setGoalLine(($this->target/1000), "black", "dashed"); } - // $graph->setTitle($graphTitle); - In this case the graph title is on the pop-up dialog box. + $graph->setTitle(' Site: '.$data['site_name'].' '.$title.' From '.substr($data['start'],0,10).' Through '.substr($data['end'],0,10)); $graph->createGraph(); diff --git a/models/admin/management/serverStats.php b/models/admin/management/serverStats.php index 512948c..b26ac58 100644 --- a/models/admin/management/serverStats.php +++ b/models/admin/management/serverStats.php @@ -134,6 +134,9 @@ class GlmMembersAdmin_management_serverStats extends GlmDataServerStatsManagemen if ($res != '') { $dbError = $res." "; + } else { + // Store Target Value in "bandwidth" table for monthly billed usage level + $BandwidthStats->bandwidthSetTarget($serverStatsSettings['fieldData']['website'], $serverStatsSettings['fieldData']['target']); } break; @@ -156,6 +159,7 @@ class GlmMembersAdmin_management_serverStats extends GlmDataServerStatsManagemen // If no connection if ($res != '') { $dbError = $res." "; + break; } // Get sorting option @@ -169,22 +173,21 @@ class GlmMembersAdmin_management_serverStats extends GlmDataServerStatsManagemen 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. - $serverStatsSettings = $this->editEntry(1); + break; - if ($serverStatsSettings === false) { + } - if (GLM_MEMBERS_PLUGIN_ADMIN_DEBUG) { - glmMembersAdmin::addNotice("  /models/admin/management/serverStats.php: Unable to load server stats management settings.", 'Alert'); - } + // Get current server stats settings to use for Server Settings form + $serverStatsSettings = $this->editEntry(1); - } + if ($serverStatsSettings === false) { - break; + if (GLM_MEMBERS_PLUGIN_ADMIN_DEBUG) { + glmMembersAdmin::addNotice("  /models/admin/management/serverStats.php: Unable to load server stats management settings.", 'Alert'); + } } diff --git a/views/admin/dashboardWidget/serverStats.html b/views/admin/dashboardWidget/serverStats.html index 642ecfc..6caaca3 100644 --- a/views/admin/dashboardWidget/serverStats.html +++ b/views/admin/dashboardWidget/serverStats.html @@ -52,26 +52,21 @@
+
+

 

+ Date Selected: + + +     
Print
     + Export Days + Export Months +
-

{$glmSiteTitle}

-

- {$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
@@ -79,6 +74,26 @@ -{if $sites} +{if $option == 'listSites'} + {if $dbError != ''} +

Unable to connect to database! Check Server Settings.

+

Error reported: {$dbError}

+ {else} + @@ -143,7 +146,8 @@ {/foreach}
Site
- + {/if} + {/if} -- 2.17.1