Statistic Information\n"); if($submitErr) { printSubmitErr(STARTERR); printSubmitErr(ENDERR); printSubmitErr(ORDERERR); $monthkey1 = processInputVar("month1", ARG_NUMERIC); $daykey1 = processInputVar("day1", ARG_NUMERIC); $yearkey1 = processInputVar("year1", ARG_NUMERIC); $monthkey2 = processInputVar("month2", ARG_NUMERIC); $daykey2 = processInputVar("day2", ARG_NUMERIC); $yearkey2 = processInputVar("year2", ARG_NUMERIC); $affilid = processInputVar("affilid", ARG_NUMERIC); } else $affilid = $user['affiliationid']; print _("Select a starting date:
\n"); $months = array(''); for($i = 2 * SECINDAY, $cnt = 1; $cnt < 13; $i += SECINMONTH, $cnt++) $months[$cnt] = strftime('%B', $i); unset($months[0]); $days = array(); for($i = 0; $i < 32; $i++) { array_push($days, $i); } unset($days[0]); $years = array(); for($i = 2004; $i <= $year2; $i++) { $years[$i] = $i; } if(! $submitErr) { $monthkey1 = $month1; $daykey1 = array_search($day1, $days); $yearkey1 = array_search($year1, $years); } print "
\n"; printSelectInput("month1", $months, $monthkey1); printSelectInput("day1", $days, $daykey1); printSelectInput("year1", $years, $yearkey1); print "
\n"; print _("Select an ending date:
\n"); if(! $submitErr) { $monthkey2 = $month2; $daykey2 = array_search($day2, $days); $yearkey2 = array_search($year2, $years); } printSelectInput("month2", $months, $monthkey2); printSelectInput("day2", $days, $daykey2); printSelectInput("year2", $years, $yearkey2); print "
\n"; $cont = addContinuationsEntry('viewstats'); if(checkUserHasPerm('View Statistics by Affiliation')) { print "\n"; print " - \n"; print _("Select an affiliation:\n"); $affils = getAffiliations(); if(! array_key_exists($affilid, $affils)) $affilid = $user['affiliationid']; $affils = array_reverse($affils, TRUE); $affils[0] = "All"; $affils = array_reverse($affils, TRUE); printSelectInput("affilid", $affils, $affilid); print "
\n"; $query = "SELECT id, " . "prettyname " . "FROM provisioning " . "ORDER BY prettyname"; $qh = doQuery($query); $provs = array(); while($row = mysql_fetch_assoc($qh)) $provs[$row['id']] = $row['prettyname']; $cdata = array('mode' => 'provisioning', 'provs' => $provs); $cont2 = addContinuationsEntry('viewstats', $cdata); print "\n"; print ":\n"; printSelectInput("provid", $provs); print "
\n"; } else print "\n"; print _("\n"); print "
\n"; } //////////////////////////////////////////////////////////////////////////////// /// /// \fn viewStatistics /// /// \brief prints statistic information /// //////////////////////////////////////////////////////////////////////////////// function viewStatistics() { global $submitErr, $submitErrMsg, $user; define("30MIN", 1800); define("1HOUR", 3600); define("2HOURS", 7200); define("4HOURS", 14400); $month1 = processInputVar("month1", ARG_NUMERIC); $day1 = processInputVar("day1", ARG_NUMERIC); $year1 = processInputVar("year1", ARG_NUMERIC); $month2 = processInputVar("month2", ARG_NUMERIC); $day2 = processInputVar("day2", ARG_NUMERIC); $year2 = processInputVar("year2", ARG_NUMERIC); $affilid = processInputVar("affilid", ARG_NUMERIC, $user['affiliationid']); $mode2 = getContinuationVar('mode', 'default'); $provid = processInputVar('provid', ARG_NUMERIC, 0); if($mode2 == 'provisioning') { $affilid = 0; $provs = getContinuationVar('provs'); if(! array_key_exists($provid, $provs)) { $ids = array_keys($provs); $provid = $ids[0]; } } else $provid = 0; $affils = getAffiliations(); if(! checkUserHasPerm('View Statistics by Affiliation') || ($affilid != 0 && ! array_key_exists($affilid, $affils))) $affilid = $user['affiliationid']; if($affilid == 0) $statsfor = "All Affiliations"; else $statsfor = $affils[$affilid]; $start = "$year1-$month1-$day1 00:00:00"; $end = "$year2-$month2-$day2 23:59:59"; if(! checkdate($month1, $day1, $year1)) { $submitErr |= STARTERR; $submitErrMsg[STARTERR] = _("The selected start date is not valid. Please ") . _("select a valid date.
\n"); } if(! checkdate($month2, $day2, $year2)) { $submitErr |= ENDERR; $submitErrMsg[ENDERR] = _("The selected end date is not valid. Please ") . _("select a valid date.
\n"); } if(datetimeToUnix($start) > datetimeToUnix($end)) { $submitErr |= ORDERERR; $submitErrMsg[ORDERERR] = _("The selected end date is before the selected ") . _("start date. Please select an end date equal ") . _("to or greater than the start date.
\n"); } if($submitErr) { selectStatistics(); return; } $timestart = microtime(1); if($mode2 == 'default') print "

" . _("Statistic Information for") . " $statsfor

\n"; elseif($mode2 == 'provisioning') print "

" . _("Statistic Information for") . " {$provs[$provid]}

\n"; print _("

Reservation information between "); $tmp = mktime(0, 0, 0, $month1, $day1, $year1); print strftime('%x', $tmp); print _(" and "); $tmp = mktime(0, 0, 0, $month2, $day2, $year2); print strftime('%x', $tmp); print ":

\n"; $reloadid = getUserlistID('vclreload@local'); if($mode2 == 'default') { $query = "SELECT l.userid, " . "u.affiliationid, " . "l.nowfuture, " . "UNIX_TIMESTAMP(l.start) AS start, " . "(UNIX_TIMESTAMP(l.loaded) - UNIX_TIMESTAMP(l.start)) AS loadtime, " . "UNIX_TIMESTAMP(l.finalend) AS finalend, " . "l.wasavailable, " . "l.ending, " . "i.prettyname, " . "o.prettyname AS OS " . "FROM log l, " . "image i, " . "user u, " . "OS o " . "WHERE l.start >= '$start' AND " . "l.finalend <= '$end' AND " . "i.id = l.imageid AND " . "i.OSid = o.id AND " . "l.userid != $reloadid AND "; } elseif($mode2 == 'provisioning') { $query = "SELECT l.userid, " . "u.affiliationid, " . "l.nowfuture, " . "UNIX_TIMESTAMP(l.start) AS start, " . "(UNIX_TIMESTAMP(l.loaded) - UNIX_TIMESTAMP(l.start)) AS loadtime, " . "UNIX_TIMESTAMP(l.finalend) AS finalend, " . "l.wasavailable, " . "l.ending, " . "i.prettyname, " . "o.prettyname AS OS " . "FROM image i, " . "user u, " . "OS o, " . "log l " . "JOIN (" . "SELECT s.logid, " . "MIN(s.computerid) AS computerid " . "FROM sublog s, " . "computer c " . "WHERE s.computerid = c.id AND " . "c.provisioningid = $provid " . "GROUP BY logid " . ") AS s ON (s.logid = l.id) " . "WHERE l.start >= '$start' AND " . "l.finalend <= '$end' AND " . "i.id = l.imageid AND " . "i.OSid = o.id AND " . "l.userid != $reloadid AND "; } if($affilid != 0) $query .= "u.affiliationid = $affilid AND "; $query .= "l.userid = u.id " . "ORDER BY i.prettyname"; $qh = doQuery($query, 275); $totalreservations = 0; $users = array(); $nows = 0; $futures = 0; $notavailable = 0; $loadtimes = array("2less" => 0, "2more" => 0); $ending = array("deleted" => 0, "released" => 0, "failed" => 0, "noack" => 0, "nologin" => 0, "timeout" => 0, "EOR" => 0, "none" => 0); $imagecount = array(); $imageusers = array(); $imagehours = array(); $imageload2less = array(); $imageload2more = array(); $imagefails = array(); $lengths = array("30min" => 0, "1hour" => 0, "2hours" => 0, "4hours" => 0, "4hrsplus" => 0); $totalhours = 0; $osusers = array(); while($row = mysql_fetch_assoc($qh)) { if(! array_key_exists($row["prettyname"], $imageload2less)) $imageload2less[$row["prettyname"]] = 0; if(! array_key_exists($row["prettyname"], $imageload2more)) $imageload2more[$row["prettyname"]] = 0; # notavailable if($row["wasavailable"] == 0) { $notavailable++; } else { $totalreservations++; # load times if($row['loadtime'] < 120) { $loadtimes['2less']++; # imageload2less $imageload2less[$row['prettyname']]++; } else { $loadtimes['2more']++; # imageload2more $imageload2more[$row['prettyname']]++; } } # users $users[$row['userid']] = 1; # nowfuture if($row["nowfuture"] == "now") $nows++; else $futures++; # ending $ending[$row["ending"]]++; # imagecount if(! array_key_exists($row["prettyname"], $imagecount)) $imagecount[$row["prettyname"]] = 0; $imagecount[$row["prettyname"]]++; # imageusers if(! array_key_exists($row["prettyname"], $imageusers)) $imageusers[$row["prettyname"]] = array(); $imageusers[$row['prettyname']][$row['userid']] = 1; # lengths $length = $row["finalend"] - $row["start"]; if($length < 0) $length = 0; if($length <= 1800) $lengths["30min"]++; elseif($length <= 3600) $lengths["1hour"]++; elseif($length <= 7200) $lengths["2hours"]++; elseif($length <= 14400) $lengths["4hours"]++; else $lengths["4hrsplus"]++; # imagehours if(! array_key_exists($row["prettyname"], $imagehours)) $imagehours[$row["prettyname"]] = 0; $imagehours[$row["prettyname"]] += ($length / 3600); # imagefails if(! array_key_exists($row["prettyname"], $imagefails)) $imagefails[$row["prettyname"]] = 0; if($row['ending'] == 'failed') $imagefails[$row["prettyname"]] += 1; # total hours $totalhours += ($length / 3600); # osusers if(! array_key_exists($row["OS"], $osusers)) $osusers[$row["OS"]] = array(); $osusers[$row['OS']][$row['userid']] = 1; } print "
\n"; print "\n"; print " \n"; print _(" \n"); print " \n"; print " \n"; print " \n"; print _(" \n"); print " \n"; print " \n"; print " \n"; print _(" \n"); print " \n"; print " \n"; print " \n"; print _(" \n"); print " \n"; print " \n"; print " \n"; print _(" \n"); print " \n"; print " \n"; print " \n"; print _(" \n"); print " \n"; print " \n"; print " \n"; print _(" \n"); print " \n"; print " \n"; print " \n"; print _(" \n"); print " \n"; print " \n"; foreach(array_keys($osusers) as $key) { print " \n"; print _(" \n"); print " \n"; print " \n"; } print "
Total Reservations:$totalreservations
Total Hours Used:" . (int)$totalhours . "
\"Now\" Reservations:$nows
\"Later\" Reservations:$futures
Unavailable:$notavailable
Load times < 2 minutes:{$loadtimes['2less']}
Load times >= 2 minutes:{$loadtimes['2more']}
Total Unique Users:" . count($users) . "
Unique Users of ") . $key . _(":" . count($osusers[$key]) . "
\n"; print "\n"; print " \n"; print " \n"; print _(" \n"); print _(" \n"); print _(" \n"); print _(" \n"); print _(" \n"); print _(" \n"); print " \n"; foreach($imagecount as $key => $value) { print " \n"; print " \n"; print " \n"; print " \n"; if(((int)$imagehours[$key]) == 0) print " \n"; else print " \n"; print " \n"; print " \n"; if($imagefails[$key]) { $percent = $imagefails[$key] * 100 / $value; if($percent < 1) $percent = sprintf('%.1f%%', $percent); else $percent = sprintf('%d%%', $percent); print " \n"; } else print " \n"; print " \n"; } print "
ReservationsUnique UsersHours Used< 2 min load time>= 2 min load timeFailures
$key:$value" . count($imageusers[$key]) . "1" . (int)$imagehours[$key] . "{$imageload2less[$key]}{$imageload2more[$key]}{$imagefails[$key]} "; print "($percent){$imagefails[$key]}
\n"; print _("

Durations:

\n"); print "\n"; print " \n"; print _(" \n"); print " \n"; print " \n"; print " \n"; print _(" \n"); print " \n"; print " \n"; print " \n"; print _(" \n"); print " \n"; print " \n"; print " \n"; print _(" \n"); print " \n"; print " \n"; print " \n"; print _(" \n"); print " \n"; print " \n"; print "
0 - 30 Min:" . $lengths["30min"] . "
30 Min - 1 Hour:" . $lengths["1hour"] . "
1 Hour - 2 Hours:" . $lengths["2hours"] . "
2 Hours - 4 Hours:" . $lengths["4hours"] . "
> 4 Hours:" . $lengths["4hrsplus"] . "
\n"; print _("

Ending information:

\n"); print "\n"; print " \n"; print _(" \n"); print " \n"; print " \n"; print _(" \n"); print " \n"; print " \n"; print _(" \n"); print " \n"; print _(" \n"); print " \n"; print " \n"; print _(" \n"); print " \n"; print _(" \n"); print " \n"; print " \n"; print _(" \n"); print " \n"; print _(" \n"); print " \n"; print " \n"; print _(" \n"); print " \n"; print _(" \n"); print " \n"; print " \n"; print _(" \n"); print " \n"; print _(" \n"); print " \n"; print " \n"; print _(" \n"); print " \n"; print _(" \n"); print " \n"; print "
Deleted:" . $ending["deleted"] . "(Future reservation deleted before start time reached)
Released:" . $ending["released"] . "(Reservation released before end time reached)
Not Acknowledged:" . $ending["noack"] . "(\"Connect!\" button never clicked)
No Login:" . $ending["nologin"] . "(User never logged in)
End of Reservation:" . $ending["EOR"] . "(End of reservation reached)
Timed Out:" . $ending["timeout"] . "(Disconnect and no reconnection within 15 minutes)
Failed:" . $ending["failed"] . "(Reserved computer failed to get prepared for user)
\n"; print "
\n"; print "
\n"; $unixstart = datetimeToUnix($start); $unixend = datetimeToUnix($end); $start = date('Y-m-d', $unixstart); $end = date('Y-m-d', $unixend); $cdata = array('start' => $start, 'end' => $end, 'affilid' => $affilid, 'mode' => $mode2, 'provid' => $provid); print _("

Reservations by Day

\n"); print "" . _("(Reservations with start time on given day)") . "
\n"; $cdata['divid'] = 'resbyday'; $cont = addContinuationsEntry('AJgetStatData', $cdata); print "\n"; print "
(Loading...)
\n"; print _("

Max Concurrent Reservations By Day

\n"); $cdata['divid'] = 'maxconcurresday'; $cont = addContinuationsEntry('AJgetStatData', $cdata); print "\n"; print "
Loading graph data...
\n"; print _("

Max Concurrent Blade Reservations By Day

\n"); $cdata['divid'] = 'maxconcurbladeday'; $cont = addContinuationsEntry('AJgetStatData', $cdata); print "\n"; print "
Loading graph data...
\n"; print "

" . _("Max Concurrent Virtual Machine Reservations By Day") . "

\n"; $cdata['divid'] = 'maxconcurvmday'; $cont = addContinuationsEntry('AJgetStatData', $cdata); print "\n"; print "
Loading graph data...
\n"; print _("

Reservations by Hour

\n"); print "(" . _("Active reservations during given hour averaged over selected dates") . ")

\n"; $cdata['divid'] = 'resbyhour'; $cont = addContinuationsEntry('AJgetStatData', $cdata); print "\n"; print "
Loading graph data...
\n"; $endtime = microtime(1); $end = $endtime - $timestart; #print "running time: $endtime - $timestart = $end
\n"; } //////////////////////////////////////////////////////////////////////////////// /// /// \fn AJgetStatData() /// /// \brief gets statistical data for a dojox chart and returns it in json format /// //////////////////////////////////////////////////////////////////////////////// function AJgetStatData() { $start = getContinuationVar("start"); $end = getContinuationVar("end"); $affilid = getContinuationVar("affilid"); $divid = getContinuationVar('divid'); $mode = getContinuationVar('mode'); $provid = getContinuationVar('provid'); if($divid == 'resbyday') $data = getStatGraphDayData($start, $end, $affilid, $mode, $provid); elseif($divid == 'maxconcurresday') $data = getStatGraphDayConUsersData($start, $end, $affilid, $mode, $provid); elseif($divid == 'maxconcurbladeday') $data = getStatGraphConBladeUserData($start, $end, $affilid, $mode, $provid); elseif($divid == 'maxconcurvmday') $data = getStatGraphConVMUserData($start, $end, $affilid, $mode, $provid); elseif($divid == 'resbyhour') $data = getStatGraphHourData($start, $end, $affilid, $mode, $provid); elseif(preg_match('/^resbyday/', $divid)) $data = getStatGraphDayData($start, $end, $affilid, $mode, $provid); elseif(preg_match('/^maxconcurresday/', $divid)) $data = getStatGraphDayConUsersData($start, $end, $affilid, $mode, $provid); elseif(preg_match('/^maxconcurbladeday/', $divid)) $data = getStatGraphConBladeUserData($start, $end, $affilid, $mode, $provid); elseif(preg_match('/^maxconcurvmday/', $divid)) $data = getStatGraphConVMUserData($start, $end, $affilid, $mode, $provid); $data['id'] = $divid; sendJSON($data); } //////////////////////////////////////////////////////////////////////////////// /// /// \fn getStatGraphDayData($start, $end, $affilid, $mode, $provid) /// /// \param $start - starting day in YYYY-MM-DD format /// \param $end - ending day in YYYY-MM-DD format /// \param $affilid - affiliationid of data to gather /// \param $mode - stat mode - 'default' or 'provisioning' /// \param $provid - provisioning id - set to 0 for $mode = 'default', set to /// > 0 otherwise /// /// \return an array with three keys:\n /// \b points - an array with y and tooltip keys that have the same value which /// is the y value at that point\n /// \b xlabels - an array with value and text keys, value's value is just an /// increasing integer starting from 1, text's value is the label /// to display on the x axis for the poing\n /// \b maxy - the max y value of the data /// /// \brief queries the log table to get reservations between $start and $end /// and creates an array with the number of reservations on each day /// //////////////////////////////////////////////////////////////////////////////// function getStatGraphDayData($start, $end, $affilid, $mode, $provid) { $startunix = datetimeToUnix($start . " 00:00:00"); $endunix = datetimeToUnix($end . " 23:59:59"); $data = array(); $data["points"] = array(); $data['xlabels'] = array(); $data['maxy'] = 0; $cachepts = array(); $addcache = array(); $reloadid = getUserlistID('vclreload@Local'); $cnt = 0; $query = "SELECT statdate, " . "value " . "FROM statgraphcache " . "WHERE graphtype = 'totalres' AND " . "affiliationid = $affilid AND " . "statdate >= '$start' AND " . "statdate <= '$end' AND " . "provisioningid = $provid"; $qh = doQuery($query, 101); while($row = mysql_fetch_assoc($qh)) $cachepts[$row['statdate']] = $row['value']; for($i = $startunix; $i < $endunix; $i += SECINDAY) { $cnt++; $startdt = unixToDatetime($i); $enddt = unixToDatetime($i + SECINDAY); $tmp = explode(' ', $startdt); $key = $tmp[0]; if(array_key_exists($key, $cachepts)) $value = $cachepts[$key]; else { if($affilid != 0) { $query = "SELECT count(l.id) " . "FROM log l, " . "user u " . "WHERE l.start >= '$startdt' AND " . "l.start < '$enddt' AND " . "l.userid != $reloadid AND " . "l.wasavailable = 1 AND " . "l.userid = u.id AND " . "u.affiliationid = $affilid"; } else { if($mode == 'default') { $query = "SELECT count(l.id) " . "FROM log l " . "WHERE l.start >= '$startdt' AND " . "l.start < '$enddt' AND " . "l.userid != $reloadid AND " . "l.wasavailable = 1"; } elseif($mode == 'provisioning') { $query = "SELECT count(l.id) " . "FROM log l " . "JOIN (" . "SELECT s.logid, " . "MIN(s.computerid) AS computerid " . "FROM sublog s, " . "computer c " . "WHERE s.computerid = c.id AND " . "c.provisioningid = $provid " . "GROUP BY logid " . ") AS s ON (s.logid = l.id) " . "WHERE l.start >= '$startdt' AND " . "l.start < '$enddt' AND " . "l.userid != $reloadid AND " . "l.wasavailable = 1"; } } $qh = doQuery($query, 295); if($row = mysql_fetch_row($qh)) $value = $row[0]; else $value = 0; $addcache[$startdt] = (int)$value; } $label = date('m/d/Y', $i); $data['points'][] = array('y' => (int)$value, 'tooltip' => "$label: " . (int)$value); if($value > $data['maxy']) $data['maxy'] = (int)$value; $data['xlabels'][] = array('value' => $cnt, 'text' => $label); } if(count($addcache)) addToStatGraphCache('totalres', $addcache, $affilid, $provid); return($data); } //////////////////////////////////////////////////////////////////////////////// /// /// \fn getStatGraphHourData($start, $end, $affilid, $mode, $provid) /// /// \param $start - starting day in YYYY-MM-DD format /// \param $end - ending day in YYYY-MM-DD format /// \param $affilid - affiliationid of data to gather /// \param $mode - stat mode - 'default' or 'provisioning' /// \param $provid - provisioning id - ignored unless $mode is 'provisioning' /// /// \return an array with two keys:\n /// \b points - an array with 5 keys:\n /// \t x - increasing integer starting from 1\n /// \t y - y value for the point\n /// \t value - same as x\n /// \t text - label for x axis for the point\n /// \t tooltip - tooltip to be displayed when point is hovered\n /// \b maxy - the max y value of the data /// /// \return an array whose keys are the days (in YYYY-MM-DD format) between /// $start and $end, inclusive, and whose values are the number of reservations /// on each day /// /// \brief queries the log table to get reservations between $start and $end /// and creates an array with the number of reservations on each day /// //////////////////////////////////////////////////////////////////////////////// function getStatGraphHourData($start, $end, $affilid, $mode, $provid) { $startdt = $start . " 00:00:00"; $enddt = $end . " 23:59:59"; $startunix = datetimeToUnix($startdt); $endunix = datetimeToUnix($enddt) + 1; $enddt = unixToDatetime($endunix); $days = ($endunix - $startunix) / SECINDAY; $data = array(); $data["points"] = array(); for($i = 0; $i < 24; $i++) { $data["points"][$i] = array('x' => $i, 'y' => 0, 'value' => $i, 'text' => statHourFormatX($i), 'tooltip' => 0); } $data["maxy"] = 0; $reloadid = getUserlistID('vclreload@Local'); if($affilid != 0) { $query = "SELECT DATE_FORMAT(l.start, '%k') AS shour, " . "DATE_FORMAT(l.start, '%i') AS smin, " . "DATE_FORMAT(l.finalend, '%k') AS ehour, " . "DATE_FORMAT(l.finalend, '%i') AS emin " . "FROM log l, " . "user u " . "WHERE l.start < '$enddt' AND " . "l.finalend > '$startdt' AND " . "l.userid != $reloadid AND " . "l.userid = u.id AND " . "l.wasavailable = 1 AND " . "u.affiliationid = $affilid"; } else { if($mode == 'default') { $query = "SELECT DATE_FORMAT(l.start, '%k') AS shour, " . "DATE_FORMAT(l.start, '%i') AS smin, " . "DATE_FORMAT(l.finalend, '%k') AS ehour, " . "DATE_FORMAT(l.finalend, '%i') AS emin " . "FROM log l " . "WHERE l.start < '$enddt' AND " . "l.finalend > '$startdt' AND " . "l.userid != $reloadid AND " . "l.wasavailable = 1"; } else { $query = "SELECT DATE_FORMAT(l.start, '%k') AS shour, " . "DATE_FORMAT(l.start, '%i') AS smin, " . "DATE_FORMAT(l.finalend, '%k') AS ehour, " . "DATE_FORMAT(l.finalend, '%i') AS emin " . "FROM log l " . "JOIN (" . "SELECT s.logid, " . "MIN(s.computerid) AS computerid " . "FROM sublog s, " . "computer c " . "WHERE s.computerid = c.id AND " . "c.provisioningid = $provid " . "GROUP BY logid " . ") AS s ON (s.logid = l.id) " . "WHERE l.start < '$enddt' AND " . "l.finalend > '$startdt' AND " . "l.userid != $reloadid AND " . "l.wasavailable = 1"; } } $qh = doQuery($query, 296); while($row = mysql_fetch_assoc($qh)) { $startmin = ($row['shour'] * 60) + $row['smin']; $endmin = ($row['ehour'] * 60) + $row['emin']; for($binstart = 0, $binend = 60, $binindex = 0; $binend <= 1440; $binstart += 60, $binend += 60, $binindex++) { if($binend <= $startmin) continue; elseif($startmin < $binend && $endmin > $binstart) { $data["points"][$binindex]['y']++; } elseif($binstart >= $endmin) break; } } # comment this to change graph to be aggregate instead of average foreach($data["points"] as $key => $val) { $newval = $val['y'] / $days; if($newval - (int)$newval != 0) $newval = sprintf('%.2f', $newval); $data['points'][$key]['y'] = $newval; $data['points'][$key]['tooltip'] = $newval; if($newval > $data['maxy']) $data['maxy'] = $newval; } return($data); } //////////////////////////////////////////////////////////////////////////////// /// /// \fn statHourFormatX($val) /// /// \param $val - hour of day (0-23) /// /// \return day of week /// /// \brief formats $val into "hour am/pm" /// //////////////////////////////////////////////////////////////////////////////// function statHourFormatX($val) { if($val == 0) return "12 am "; elseif($val < 12) return "$val am "; elseif($val == 12) return "$val pm "; else return $val - 12 . " pm "; } //////////////////////////////////////////////////////////////////////////////// /// /// \fn getStatGraphDayConUsersData($start, $end, $affilid, $mode, $provid) /// /// \param $start - starting day in YYYY-MM-DD format /// \param $end - ending day in YYYY-MM-DD format /// \param $affilid - affiliationid of data to gather /// \param $mode - stat mode - 'default' or 'provisioning' /// \param $provid - provisioning id - ignored unless $mode is 'provisioning' /// /// \return an array with three keys:\n /// \b points - an array with y and tooltip keys that have the same value which /// is the y value at that point\n /// \b xlabels - an array with value and text keys, value's value is just an /// increasing integer starting from 1, text's value is the label /// to display on the x axis for the poing\n /// \b maxy - the max y value of the data /// /// \brief queries the log table to get reservations between $start and $end /// and creates an array with the max concurrent users per day /// //////////////////////////////////////////////////////////////////////////////// function getStatGraphDayConUsersData($start, $end, $affilid, $mode, $provid) { $startdt = $start . " 00:00:00"; $enddt = $end . " 23:59:59"; $startunix = datetimeToUnix($startdt); $endunix = datetimeToUnix($enddt) + 1; $daycnt = ($endunix - $startunix) / SECINDAY; if($daycnt - (int)$daycnt > 0.5) $daycnt = (int)$daycnt + 1; else $daycnt = (int)$daycnt; if($endunix >= time()) $daycnt--; $data = array(); $data["points"] = array(); $data["xlabels"] = array(); $data["maxy"] = 0; $cachepts = array(); $addcache = array(); $reloadid = getUserlistID('vclreload@Local'); $cnt = 0; $query = "SELECT statdate, " . "value " . "FROM statgraphcache " . "WHERE graphtype = 'concurres' AND " . "affiliationid = $affilid AND " . "statdate >= '$start' AND " . "statdate <= '$end' AND " . "provisioningid = $provid"; $qh = doQuery($query, 101); while($row = mysql_fetch_assoc($qh)) $cachepts[$row['statdate']] = $row['value']; if((count($cachepts) + 31) < $daycnt) { $data = array('nodata' => _('(too much computational time required to generate this graph)')); return $data; } for($daystart = $startunix; $daystart < $endunix; $daystart += SECINDAY) { $cnt++; $startdt = unixToDatetime($daystart); $enddt = unixToDatetime($daystart + SECINDAY); $tmp = explode(' ', $startdt); $key = $tmp[0]; if(array_key_exists($key, $cachepts)) $value = $cachepts[$key]; else { $count = array(); for($j = 0; $j < 24; $j++) $count[$j] = 0; if($affilid != 0) { $query = "SELECT UNIX_TIMESTAMP(l.start) AS start, " . "UNIX_TIMESTAMP(l.finalend) AS end " . "FROM log l, " . "user u " . "WHERE l.start < '$enddt' AND " . "l.finalend > '$startdt' AND " . "l.userid != $reloadid AND " . "l.userid = u.id AND " . "u.affiliationid = $affilid"; } else { if($mode == 'default') { $query = "SELECT UNIX_TIMESTAMP(l.start) AS start, " . "UNIX_TIMESTAMP(l.finalend) AS end " . "FROM log l " . "WHERE l.start < '$enddt' AND " . "l.finalend > '$startdt' AND " . "l.userid != $reloadid"; } elseif($mode == 'provisioning') { $query = "SELECT UNIX_TIMESTAMP(l.start) AS start, " . "UNIX_TIMESTAMP(l.finalend) AS end " . "FROM log l " . "JOIN (" . "SELECT s.logid, " . "MIN(s.computerid) AS computerid " . "FROM sublog s, " . "computer c " . "WHERE s.computerid = c.id AND " . "c.provisioningid = $provid " . "GROUP BY logid " . ") AS s ON (s.logid = l.id) " . "WHERE l.start < '$enddt' AND " . "l.finalend > '$startdt' AND " . "l.userid != $reloadid"; } } $qh = doQuery($query, 101); while($row = mysql_fetch_assoc($qh)) { $unixstart = $row["start"]; $unixend = $row["end"]; for($binstart = $daystart, $binend = $daystart + 3600, $binindex = 0; $binstart <= $unixend && $binend <= ($daystart + SECINDAY); $binstart += 3600, $binend += 3600, $binindex++) { if($binend <= $unixstart) { continue; } elseif($unixstart < $binend && $unixend > $binstart) { $count[$binindex]++; } elseif($binstart >= $unixend) { break; } } } rsort($count); $value = $count[0]; $addcache[$startdt] = (int)$value; } $label = date('m/d/Y', $daystart); $data["points"][] = array('y' => $value, 'tooltip' => "$label: {$value}"); if($value > $data['maxy']) $data['maxy'] = $value; $data['xlabels'][] = array('value' => $cnt, 'text' => $label); } if(count($addcache)) addToStatGraphCache('concurres', $addcache, $affilid, $provid); return($data); } //////////////////////////////////////////////////////////////////////////////// /// /// \fn getStatGraphConBladeUserData($start, $end, $affilid, $mode, $provid) /// /// \param $start - starting day in YYYY-MM-DD format /// \param $end - ending day in YYYY-MM-DD format /// \param $affilid - affiliationid of data to gather /// \param $mode - stat mode - 'default' or 'provisioning' /// \param $provid - provisioning id - ignored unless $mode is 'provisioning' /// /// \return an array with three keys:\n /// \b points - an array with y and tooltip keys that have the same value which /// is the y value at that point\n /// \b xlabels - an array with value and text keys, value's value is just an /// increasing integer starting from 1, text's value is the label /// to display on the x axis for the poing\n /// \b maxy - the max y value of the data /// /// \brief queries the log table to get reservations between $start and $end /// and creates an array with the max concurrent users of blades per day /// //////////////////////////////////////////////////////////////////////////////// function getStatGraphConBladeUserData($start, $end, $affilid, $mode, $provid) { $startdt = $start . " 00:00:00"; $enddt = $end . " 23:59:59"; $startunix = datetimeToUnix($startdt); $endunix = datetimeToUnix($enddt) + 1; $daycnt = ($endunix - $startunix) / SECINDAY; if($daycnt - (int)$daycnt > 0.5) $daycnt = (int)$daycnt + 1; else $daycnt = (int)$daycnt; if($endunix >= time()) $daycnt--; $data = array(); $data["points"] = array(); $data["xlabels"] = array(); $data["maxy"] = 0; $cachepts = array(); $addcache = array(); $reloadid = getUserlistID('vclreload@Local'); $cnt = 0; $query = "SELECT statdate, " . "value " . "FROM statgraphcache " . "WHERE graphtype = 'concurblade' AND " . "affiliationid = $affilid AND " . "statdate >= '$start' AND " . "statdate <= '$end' AND " . "provisioningid = $provid"; $qh = doQuery($query, 101); while($row = mysql_fetch_assoc($qh)) $cachepts[$row['statdate']] = $row['value']; if((count($cachepts) + 31) < $daycnt) { $data = array('nodata' => _('(too much computational time required to generate this graph)')); return $data; } for($daystart = $startunix; $daystart < $endunix; $daystart += SECINDAY) { $cnt++; $startdt = unixToDatetime($daystart); $enddt = unixToDatetime($daystart + SECINDAY); $tmp = explode(' ', $startdt); $key = $tmp[0]; if(array_key_exists($key, $cachepts)) $value = $cachepts[$key]; else { $count = array(); for($j = 0; $j < 24; $j++) $count[$j] = 0; if($affilid != 0) { $query = "SELECT s.hostcomputerid, " . "l.start AS start, " . "l.finalend AS end, " . "c.type " . "FROM log l, " . "user u, " . "sublog s " . "LEFT JOIN computer c ON (s.computerid = c.id) " . "LEFT JOIN computer c2 ON (s.hostcomputerid = c2.id) " . "WHERE l.userid = u.id AND " . "l.start < '$enddt' AND " . "l.finalend > '$startdt' AND " . "s.logid = l.id AND " . "l.wasavailable = 1 AND " . "l.userid != $reloadid AND " . "(c.type = 'blade' OR " . " (c.type = 'virtualmachine' AND c2.type = 'blade')) AND " . "u.affiliationid = $affilid"; } else { if($mode == 'default') { $query = "SELECT s.hostcomputerid, " . "l.start AS start, " . "l.finalend AS end, " . "c.type " . "FROM log l, " . "sublog s " . "LEFT JOIN computer c ON (s.computerid = c.id) " . "LEFT JOIN computer c2 ON (s.hostcomputerid = c2.id) " . "WHERE l.start < '$enddt' AND " . "l.finalend > '$startdt' AND " . "s.logid = l.id AND " . "l.wasavailable = 1 AND " . "l.userid != $reloadid AND " . "(c.type = 'blade' OR " . " (c.type = 'virtualmachine' AND c2.type = 'blade'))"; } elseif($mode == 'provisioning') { $query = "SELECT s.hostcomputerid, " . "l.start AS start, " . "l.finalend AS end, " . "c.type " . "FROM log l " . "JOIN (" . "SELECT s.logid, " . "MIN(s.computerid) AS computerid " . "FROM sublog s, " . "computer c " . "WHERE s.computerid = c.id AND " . "c.provisioningid = $provid " . "GROUP BY logid " . ") AS s2 ON (s2.logid = l.id), " . "sublog s " . "LEFT JOIN computer c ON (s.computerid = c.id) " . "LEFT JOIN computer c2 ON (s.hostcomputerid = c2.id) " . "WHERE l.start < '$enddt' AND " . "l.finalend > '$startdt' AND " . "s.logid = l.id AND " . "l.wasavailable = 1 AND " . "l.userid != $reloadid AND " . "(c.type = 'blade' OR " . " (c.type = 'virtualmachine' AND c2.type = 'blade'))"; } } $qh = doQuery($query, 101); $comps = array(); while($row = mysql_fetch_assoc($qh)) { $unixstart = datetimeToUnix($row["start"]); $unixend = datetimeToUnix($row["end"]); for($binstart = $daystart, $binend = $daystart + 3600, $binindex = 0; $binstart <= $unixend && $binend <= ($daystart + SECINDAY); $binstart += 3600, $binend += 3600, $binindex++) { if($row['type'] == 'virtualmachine') { if(array_key_exists($binindex, $comps) && array_key_exists($row['hostcomputerid'], $comps[$binindex])) continue; $comps[$binindex][$row['hostcomputerid']] = 1; } if($binend <= $unixstart) { continue; } elseif($unixstart < $binend && $unixend > $binstart) { $count[$binindex]++; } elseif($binstart >= $unixend) { break; } } } rsort($count); $value = $count[0]; $addcache[$startdt] = (int)$value; } $label = date('m/d/Y', $daystart); $data["points"][] = array('y' => $value, 'tooltip' => "$label: {$value}"); if($value > $data['maxy']) $data['maxy'] = $value; $data['xlabels'][] = array('value' => $cnt, 'text' => $label); } if(count($addcache)) addToStatGraphCache('concurblade', $addcache, $affilid, $provid); return($data); } //////////////////////////////////////////////////////////////////////////////// /// /// \fn getStatGraphConVMUserData($start, $end, $affilid, $mode, $provid) /// /// \param $start - starting day in YYYY-MM-DD format /// \param $end - ending day in YYYY-MM-DD format /// \param $affilid - affiliationid of data to gather /// \param $mode - stat mode - 'default' or 'provisioning' /// \param $provid - provisioning id - ignored unless $mode is 'provisioning' /// /// \return an array with three keys:\n /// \b points - an array with y and tooltip keys that have the same value which /// is the y value at that point\n /// \b xlabels - an array with value and text keys, value's value is just an /// increasing integer starting from 1, text's value is the label /// to display on the x axis for the poing\n /// \b maxy - the max y value of the data /// /// \brief queries the log table to get reservations between $start and $end /// and creates an array with the max concurrent users of vms per day /// //////////////////////////////////////////////////////////////////////////////// function getStatGraphConVMUserData($start, $end, $affilid, $mode, $provid) { $startdt = $start . " 00:00:00"; $enddt = $end . " 23:59:59"; $startunix = datetimeToUnix($startdt); $endunix = datetimeToUnix($enddt) + 1; $daycnt = ($endunix - $startunix) / SECINDAY; if($daycnt - (int)$daycnt > 0.5) $daycnt = (int)$daycnt + 1; else $daycnt = (int)$daycnt; if($endunix >= time()) $daycnt--; $data = array(); $data["points"] = array(); $data["xlabels"] = array(); $data["maxy"] = 0; $cachepts = array(); $addcache = array(); $reloadid = getUserlistID('vclreload@Local'); $cnt = 0; $query = "SELECT statdate, " . "value " . "FROM statgraphcache " . "WHERE graphtype = 'concurvm' AND " . "affiliationid = $affilid AND " . "statdate >= '$start' AND " . "statdate <= '$end' AND " . "provisioningid = $provid"; $qh = doQuery($query, 101); while($row = mysql_fetch_assoc($qh)) $cachepts[$row['statdate']] = $row['value']; if((count($cachepts) + 31) < $daycnt) { $data = array('nodata' => _('(too much computational time required to generate this graph)')); return $data; } for($daystart = $startunix; $daystart < $endunix; $daystart += SECINDAY) { $cnt++; $startdt = unixToDatetime($daystart); $enddt = unixToDatetime($daystart + SECINDAY); $tmp = explode(' ', $startdt); $key = $tmp[0]; if(array_key_exists($key, $cachepts)) $value = $cachepts[$key]; else { $count = array(); for($j = 0; $j < 24; $j++) $count[$j] = 0; if($affilid != 0) { $query = "SELECT l.start AS start, " . "l.finalend AS end " . "FROM log l, " . "sublog s, " . "computer c, " . "user u " . "WHERE l.userid = u.id AND " . "l.start < '$enddt' AND " . "l.finalend > '$startdt' AND " . "s.logid = l.id AND " . "s.computerid = c.id AND " . "l.wasavailable = 1 AND " . "c.type = 'virtualmachine' AND " . "l.userid != $reloadid AND " . "u.affiliationid = $affilid"; } else { if($mode == 'default') { $query = "SELECT l.start AS start, " . "l.finalend AS end " . "FROM log l, " . "sublog s, " . "computer c " . "WHERE l.start < '$enddt' AND " . "l.finalend > '$startdt' AND " . "s.logid = l.id AND " . "s.computerid = c.id AND " . "l.wasavailable = 1 AND " . "c.type = 'virtualmachine' AND " . "l.userid != $reloadid"; } elseif($mode == 'provisioning') { $query = "SELECT l.start AS start, " . "l.finalend AS end " . "FROM computer c, " . "sublog s, " . "log l " . "JOIN (" . "SELECT s.logid, " . "MIN(s.computerid) AS computerid " . "FROM sublog s, " . "computer c " . "WHERE s.computerid = c.id AND " . "c.provisioningid = $provid " . "GROUP BY logid " . ") AS s2 ON (s2.logid = l.id) " . "WHERE l.start < '$enddt' AND " . "l.finalend > '$startdt' AND " . "s.logid = l.id AND " . "s.computerid = c.id AND " . "l.wasavailable = 1 AND " . "c.type = 'virtualmachine' AND " . "l.userid != $reloadid"; } } $qh = doQuery($query, 101); while($row = mysql_fetch_assoc($qh)) { $unixstart = datetimeToUnix($row["start"]); $unixend = datetimeToUnix($row["end"]); for($binstart = $daystart, $binend = $daystart + 3600, $binindex = 0; $binstart <= $unixend && $binend <= ($daystart + SECINDAY); $binstart += 3600, $binend += 3600, $binindex++) { if($binend <= $unixstart) { continue; } elseif($unixstart < $binend && $unixend > $binstart) { $count[$binindex]++; } elseif($binstart >= $unixend) { break; } } } rsort($count); $value = $count[0]; $addcache[$startdt] = (int)$value; } $label = date('m/d/Y', $daystart); $data["points"][] = array('y' => $value, 'tooltip' => "$label: {$value}"); if($value > $data['maxy']) $data['maxy'] = $value; $data['xlabels'][] = array('value' => $cnt, 'text' => $label); } if(count($addcache)) addToStatGraphCache('concurvm', $addcache, $affilid, $provid); return($data); } //////////////////////////////////////////////////////////////////////////////// /// /// \fn addToStatGraphCache($type, $addcache, $affilid, $provid) /// /// \param $type - type of data to add, one of totalres, concurres, concurblade, /// or concurvm /// \param $addcache - array of data where the keys are a date and the values /// are the stat value for that date /// \param $affilid - affiliation id for which the data is associated /// \param $provid - provisioning id - 0 for all provisioning ids, > 0 otherwise /// /// \brief adds passed in data to statgraphcache table /// //////////////////////////////////////////////////////////////////////////////// function addToStatGraphCache($type, $addcache, $affilid, $provid) { $nosave = time() - SECINDAY; $values = array(); foreach($addcache as $date => $value) { $startts = datetimeToUnix($date); if($startts < $nosave) { $tmp = explode(' ', $date); $statdate = $tmp[0]; $values[] = "('$type', '$statdate', $affilid, $value, $provid)"; } } if(count($values)) { $insval = implode(',', $values); $query = "INSERT IGNORE INTO statgraphcache " . "(graphtype, statdate, affiliationid, value, provisioningid) " . "VALUES $insval"; doQuery($query, 101); } } ?>