} return -1; } $compName = cleanCompName(sqlite_escape_string($_POST['computerName'])); //$compProcesses = explode("|", $_POST['processes']); if ($_POST['processes']) { $compProcesses = sqlite_escape_string($_POST['processes']); } $timeNow = time(); if ($compName != "") { $dbTrackHandler = connectDb(); // Translate from computer name to Id $compNames = getCompNamesId($dbTrackHandler); $compId = $compNames[$compName]; if (intval($compId) == 0) { // If the computer has not been seen before // Then we have to figure out what to do with it. // Let's see if the prefix of the computer matches any known region name // if that's the case, then we create a new entry in the computer table to track it. if ($add_unknown_computers) { $dbTrackHandler = connectDb(); $regNames = getRegionNamesId($dbTrackHandler, true); $thisCompRegId = getCompRegion($compName, $regNames); $dbTrackHandler->query('INSERT INTO computers ' . '(name,x,y,region) ' . 'VALUES ("' . $compName . '", 0, 0, ' . $thisCompRegId . ');'); $compNames = getCompNamesId($dbTrackHandler, true); } } if (intval($compId) != 0) { $dbTrackHandler->query('UPDATE computers ' . 'SET laststatus=' . AVAIBILITY_TYPE_BUSY . ', lastsignal=' . $timeNow . ' ' . 'WHERE id=' . $compId . '; ' . 'INSERT INTO trackrecords (`compid`, `time`, `status`) ' . 'VALUES ("' . $compId . '", ' . $timeNow . ', ' . AVAIBILITY_TYPE_BUSY . '); ' . ($compProcesses != "" ? 'INSERT INTO miscrecords (`compid`, `time`, `recordtype`, `data`) ' . 'VALUES ("' . $compId . '", ' . $timeNow . ', ' . RECORDTYPE_PROGRAMS . ', "' . $compProcesses . '"); ' : '')); } }
function generateStatsBag($args, &$dbHandler) { //$batchRecordProcess = 10000; // analyse 10000 at a time $recordTableName = ''; $sql_cmd = ''; $compIds = getCompIdsName($dbHandler); switch ($args['reportType']) { //////////////////////////////////////////////////////////////////////////////////////////////////// case 'computerStats': $recordTableName = 'trackrecords'; $sql_cmd = 'SELECT trackrecords.time, trackrecords.status, trackrecords.compid FROM trackrecords '; /* 'SELECT trackrecords.time, trackrecords.status, trackrecords.compid, computers.name, computers.id, computers.region FROM trackrecords ' . 'LEFT OUTER JOIN computers ON trackrecords.compid = computers.id '; */ $firstWHERE = true; // Now select zone if ($args['computerRange'] == 'zone') { $allZones = getAllComputersZones($dbHandler); $affectedComps = $allZones[intval($args['computersRangeParam'])]; $sql_cmd .= ($firstWHERE ? 'WHERE ' : 'AND ') . 'compid IN (' . implode(', ', $affectedComps) . ') '; $firstWHERE = false; } // Now select computer if ($args['computerRange'] == 'computer') { $compId = getCompNamesId($dbTrackHandler); $sql_cmd .= ($firstWHERE ? 'WHERE ' : 'AND ') . ' compid = "' . $compId[$args['computersRangeParam']] . '" '; $firstWHERE = false; } // Now select time frame if (intval($args['timeFrame']) != 0) { $sql_cmd .= ($firstWHERE ? 'WHERE ' : 'AND ') . $recordTableName . '.time > ' . (time() - intval($args['timeFrame'])) . " "; $firstWHERE = false; } if (intval($args['endTimeFrame']) != 0) { $sql_cmd .= ($firstWHERE ? 'WHERE ' : 'AND ') . $recordTableName . '.time < ' . (time() - intval($args['endTimeFrame'])) . " "; $firstWHERE = false; } // Now select week days if (intval($args['filterWeekends']) != 0) { $sql_cmd .= ($firstWHERE ? 'WHERE ' : 'AND ') . ' DAYOFWEEK(FROM_UNIXTIME(' . $recordTableName . '.time)) > 1 AND ' . 'DAYOFWEEK(FROM_UNIXTIME(' . $recordTableName . '.time)) < 7 '; $firstWHERE = false; } // Now select daytime only if (intval($args['filterNights']) != 0) { $sql_cmd .= ($firstWHERE ? 'WHERE ' : 'AND ') . ' HOUR(FROM_UNIXTIME(' . $recordTableName . '.time)) > 7 ' . 'OR ((HOUR(FROM_UNIXTIME(' . $recordTableName . '.time)) > 0) AND (HOUR(FROM_UNIXTIME(' . $recordTableName . '.time)) < 1)) '; $firstWHERE = false; } // Now select the computer conditions switch ($args['computerStatus']) { case 'occupied': $sql_cmd .= ($firstWHERE ? 'WHERE ' : 'AND ') . $recordTableName . '.status = \'' . AVAIBILITY_TYPE_BUSY . '\' '; $firstWHERE = false; break; case 'available': $sql_cmd .= ($firstWHERE ? 'WHERE ' : 'AND ') . $recordTableName . '.status = \'' . AVAIBILITY_TYPE_AVAILABLE . '\' '; $firstWHERE = false; break; case 'offline': $sql_cmd .= ($firstWHERE ? 'WHERE ' : 'AND ') . $recordTableName . '.status = \'' . AVAIBILITY_TYPE_OFFLINE . '\' '; $firstWHERE = false; break; default: } break; //////////////////////////////////////////////////////////////////////////////////////////////////// //////////////////////////////////////////////////////////////////////////////////////////////////// case 'programUsage': $recordTableName = 'miscrecords'; $sql_cmd = 'SELECT miscrecords.time, miscrecords.data, miscrecords.compid FROM miscrecords ' . 'WHERE ' . $recordTableName . '.recordtype = ' . RECORDTYPE_PROGRAMS . ' '; // Now select zone if ($args['computerRange'] == 'zone') { $allZones = getAllComputersZones($dbHandler); $affectedComps = $allZones[intval($args['computersRangeParam'])]; $sql_cmd .= ($firstWHERE ? 'WHERE ' : 'AND ') . 'compid IN (' . implode(', ', $affectedComps) . ') '; $firstWHERE = false; } // Now select time frame if (intval($args['timeFrame']) != 0) { $sql_cmd .= 'AND ' . $recordTableName . '.time > ' . (time() - intval($args['timeFrame'])) . " "; } if (intval($args['endTimeFrame']) != 0) { $sql_cmd .= 'AND ' . $recordTableName . '.time < ' . (time() - intval($args['endTimeFrame'])) . " "; } break; //////////////////////////////////////////////////////////////////////////////////////////////////// //////////////////////////////////////////////////////////////////////////////////////////////////// default: die('invalid arguments'); break; //////////////////////////////////////////////////////////////////////////////////////////////////// } //echo $sql_cmd; // Now select the computer range switch ($args['computerRange']) { } $sql_cmd .= ''; // ---------------------------------------------------- // Connect to the database and query it $resultBag = array(); $results = $dbHandler->query($sql_cmd . ';'); $sortMode = 0; // group by computer name switch ($args['groupType']) { case 'hourByHour': $sortMode = 1; break; case 'dayofWeek': $sortMode = 2; break; case 'individualDay': $sortMode = 3; break; case 'oneBigChart': $sortMode = 4; break; case 'hourDayCombined': $sortMode = 5; break; default: $sortMode = 0; break; } $computerRecords = array(); foreach ($results as $result) { $currentComputer = $result['compid']; $currentRecordedStatus = null; $currentRecordedTime = $result['time']; switch ($args['reportType']) { case 'computerStats': $currentRecordedStatus = $result['status']; break; default: $currentRecordedStatus = explode("|", $result['data']); break; } if (!isDupeRecord($currentComputer, $currentRecordedTime, $computerRecords)) { switch ($sortMode) { case 1: // Hour by Hour $resultGroup = date("H", $currentRecordedTime) . ":00"; break; case 2: // Day of Week $resultGroup = date("N:l", $currentRecordedTime); break; case 3: // Individual Days $resultGroup = date("y/m/d", $currentRecordedTime); break; case 4: // One Big Chart $resultGroup = "Big Chart"; break; case 5: // Hour by Hour Day-by-Day of Week $resultGroup = date("N:l H:00", $currentRecordedTime); break; default: // Computer Name $resultGroup = $compIds[$currentComputer]; break; } // switch $sortmode addToResultBag($resultBag, $resultGroup, $currentRecordedStatus); } // if dupe records } // while results are valid // Sort results ksort($resultBag); return $resultBag; }