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("", "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"); 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 = array_search($month1, $months); $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 a ending date:
\n"; if(! $submitErr) { $monthkey2 = array_search($month2, $months); $daykey2 = array_search($day2, $days); $yearkey2 = array_search($year2, $years); } printSelectInput("month2", $months, $monthkey2); printSelectInput("day2", $days, $daykey2); printSelectInput("year2", $years, $yearkey2); print "
\n"; if(checkUserHasPerm('View Statistics by Affiliation')) { 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"; } $cont = addContinuationsEntry('viewstats'); 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']); $affils = getAffiliations(); if(! checkUserHasPerm('View Statistics by Affiliation') || ($affilid != 0 && ! array_key_exists($affilid, $affils))) $affilid = $user['affiliationid']; $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); print "

Statistic Information

\n"; print "

Reservation information between $month1/$day1/$year1 and "; print "$month2/$day2/$year2:\n"; print "

\n"; $reloadid = getUserlistID('vclreload@Local'); $query = "SELECT l.userid, " . "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 "; 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); 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"; if($unixend - $unixstart > SECINMONTH) print "(this graph only available for up to a month of data)
\n"; else { $cdata['divid'] = 'maxconcurresday'; $cont = addContinuationsEntry('AJgetStatData', $cdata); print "\n"; print "
Loading graph data...
\n"; } print "

Max Concurrent Blade Reservations By Day

\n"; if($unixend - $unixstart > SECINMONTH) print "(this graph only available for up to a month of data)
\n"; else { $cdata['divid'] = 'maxconcurbladeday'; $cont = addContinuationsEntry('AJgetStatData', $cdata); print "\n"; print "
Loading graph data...
\n"; } print "

Max Concurrent Virtual Machine Reservations By Day

\n"; if($unixend - $unixstart > SECINMONTH) print "(this graph only available for up to a month of data)
\n"; else { $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'); if($divid == 'resbyday') $data = getStatGraphDayData($start, $end, $affilid); elseif($divid == 'maxconcurresday') $data = getStatGraphDayConUsersData($start, $end, $affilid); elseif($divid == 'maxconcurbladeday') $data = getStatGraphConBladeUserData($start, $end, $affilid); elseif($divid == 'maxconcurvmday') $data = getStatGraphConVMUserData($start, $end, $affilid); elseif($divid == 'resbyhour') $data = getStatGraphHourData($start, $end, $affilid); $data['id'] = $divid; sendJSON($data); } //////////////////////////////////////////////////////////////////////////////// /// /// \fn getStatGraphDayData($start, $end, $affilid) /// /// \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 /// /// \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) { $startunix = datetimeToUnix($start . " 00:00:00"); $endunix = datetimeToUnix($end . " 23:59:59"); $data = array(); $data["points"] = array(); $data['xlabels'] = array(); $data['maxy'] = 0; $reloadid = getUserlistID('vclreload@Local'); $cnt = 0; for($i = $startunix; $i < $endunix; $i += SECINDAY) { $cnt++; $startdt = unixToDatetime($i); $enddt = unixToDatetime($i + SECINDAY); 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 { $query = "SELECT count(l.id) " . "FROM log l " . "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; $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); } return($data); } //////////////////////////////////////////////////////////////////////////////// /// /// \fn getStatGraphHourData($start, $end, $affilid) /// /// \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 /// /// \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) { $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 { $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"; } $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) /// /// \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 /// /// \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) { $startdt = $start . " 00:00:00"; $enddt = $end . " 23:59:59"; $startunix = datetimeToUnix($startdt); $endunix = datetimeToUnix($enddt) + 1; $days = ($endunix - $startunix) / SECINDAY; $data = array(); $data["points"] = array(); $data["xlabels"] = array(); $data["maxy"] = 0; $reloadid = getUserlistID('vclreload@Local'); $cnt = 0; for($daystart = $startunix; $daystart < $endunix; $daystart += SECINDAY) { $cnt++; $count = array(); for($j = 0; $j < 24; $j++) { $count[$j] = 0; } $startdt = unixToDatetime($daystart); $enddt = unixToDatetime($daystart + SECINDAY); 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 { $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"; } $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); $label = date('m/d/Y', $daystart); $data["points"][] = array('y' => $count[0], 'tooltip' => "$label: {$count[0]}"); if($count[0] > $data['maxy']) $data['maxy'] = $count[0]; $data['xlabels'][] = array('value' => $cnt, 'text' => $label); } return($data); } //////////////////////////////////////////////////////////////////////////////// /// /// \fn getStatGraphConBladeUserData($start, $end, $affilid) /// /// \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 /// /// \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) { $startdt = $start . " 00:00:00"; $enddt = $end . " 23:59:59"; $startunix = datetimeToUnix($startdt); $endunix = datetimeToUnix($enddt) + 1; $days = ($endunix - $startunix) / SECINDAY; $data = array(); $data["points"] = array(); $data["xlabels"] = array(); $data["maxy"] = 0; $reloadid = getUserlistID('vclreload@Local'); $cnt = 0; for($daystart = $startunix; $daystart < $endunix; $daystart += SECINDAY) { $cnt++; $count = array(); for($j = 0; $j < 24; $j++) { $count[$j] = 0; } $startdt = unixToDatetime($daystart); $enddt = unixToDatetime($daystart + SECINDAY); 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 { $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'))"; } $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); $label = date('m/d/Y', $daystart); $data["points"][] = array('y' => $count[0], 'tooltip' => "$label: {$count[0]}"); if($count[0] > $data['maxy']) $data['maxy'] = $count[0]; $data['xlabels'][] = array('value' => $cnt, 'text' => $label); } return($data); } //////////////////////////////////////////////////////////////////////////////// /// /// \fn getStatGraphConVMUserData($start, $end, $affilid) /// /// \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 /// /// \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) { $startdt = $start . " 00:00:00"; $enddt = $end . " 23:59:59"; $startunix = datetimeToUnix($startdt); $endunix = datetimeToUnix($enddt) + 1; $days = ($endunix - $startunix) / SECINDAY; $data = array(); $data["points"] = array(); $data["xlabels"] = array(); $data["maxy"] = 0; $reloadid = getUserlistID('vclreload@Local'); $cnt = 0; for($daystart = $startunix; $daystart < $endunix; $daystart += SECINDAY) { $cnt++; $count = array(); for($j = 0; $j < 24; $j++) { $count[$j] = 0; } $startdt = unixToDatetime($daystart); $enddt = unixToDatetime($daystart + SECINDAY); 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 { $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"; } $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); $label = date('m/d/Y', $daystart); $data["points"][] = array('y' => $count[0], 'tooltip' => "$label: {$count[0]}"); if($count[0] > $data['maxy']) $data['maxy'] = $count[0]; $data['xlabels'][] = array('value' => $cnt, 'text' => $label); } return($data); } ?>