function GetDeviceByWarranty($year) { global $dbh; $deviceList = array(); if ($year >= 0) { $previous_year = $year - 1; $selectSQL = sprintf("SELECT DeviceID, Label, Owner, WarrantyExpire, PrimaryContact FROM fac_Device WHERE (DATEDIFF(WarrantyExpire, NOW())/365)>=%d AND (DATEDIFF(WarrantyExpire, NOW())/365)<%d ORDER BY Owner, WarrantyExpire, Label;", $previous_year, $year); foreach ($dbh->query($selectSQL) as $deviceRow) { $deviceList[$deviceRow['DeviceID']] = Device::RowToObject($deviceRow); } } else { $selectSQL = "SELECT DeviceID, Label, Owner, WarrantyExpire, PrimaryContact FROM fac_Device WHERE (DATEDIFF(NOW(), WarrantyExpire))>0 AND WarrantyExpire>'1969-12-31' ORDER BY Owner, WarrantyExpire, Label;"; foreach ($dbh->query($selectSQL) as $deviceRow) { $deviceList[$deviceRow['DeviceID']] = Device::RowToObject($deviceRow); } } return $deviceList; }
function GetDeviceByAge($year) { global $dbh; $deviceList = array(); if ($year <= 5) { $previous_year = $year - 1; $selectSQL = sprintf("SELECT DeviceID, Label, Owner, MfgDate, PrimaryContact FROM fac_Device WHERE (DATEDIFF(NOW(), (CASE WHEN MfgDate>'1969-12-31' THEN MfgDate ELSE InstallDate END))/365)<%d AND (DATEDIFF(NOW(), (CASE WHEN MfgDate>'1969-12-31' THEN MfgDate ELSE InstallDate END))/365)>=%d ORDER BY Owner, MfgDate ASC, Label", $year, $previous_year); foreach ($dbh->query($selectSQL) as $deviceRow) { $deviceList[$deviceRow['DeviceID']] = Device::RowToObject($deviceRow); } } else { $selectSQL = "SELECT DeviceID, Label, Owner, MfgDate, PrimaryContact FROM fac_Device WHERE (DATEDIFF(NOW(), (CASE WHEN MfgDate>'1969-12-31' THEN MfgDate ELSE InstallDate END))/365)>5 AND (CASE WHEN MfgDate>'1969-12-31' THEN MfgDate ELSE InstallDate END)>'1970-01-01' ORDER BY Owner, MfgDate ASC, Label"; foreach ($dbh->query($selectSQL) as $deviceRow) { $deviceList[$deviceRow['DeviceID']] = Device::RowToObject($deviceRow); } } return $deviceList; }
static function getPatchCandidates($DeviceID, $PortNum = null, $listports = null, $patchpanels = null, $scopelimit = null) { /* * $DeviceID = ID of the device that you are wanting to make a connection from * $PortNum(optional) = Port Number on the device you are wanting to connect, * mandatory if media enforcing is on * $listports(optional) = Any value will trigger this to kick back a list of * valid points that this port can connect to instead of the default list * of valid devices that it can connect to. */ global $dbh; global $config; global $person; $dev = new Device(); // make sure we have a real device first $dev->DeviceID = $DeviceID; if (!$dev->GetDevice()) { return false; } $mediaenforce = ""; if ($config->ParameterArray["MediaEnforce"] == 'enabled' && !is_null($PortNum)) { $dp = new DevicePorts(); $dp->DeviceID = $DeviceID; $dp->PortNumber = $PortNum; $dp->getPort(); $mt = new MediaTypes(); $mt->MediaID = $dp->MediaID; $mt->GetType(); $mediaenforce = " AND MediaID={$mt->MediaID}"; } elseif ($config->ParameterArray["MediaEnforce"] == 'enabled' && is_null($PortNum)) { // Media Type Enforcing is enabled and you didn't supply a port to match type on return false; } $limiter = ''; if (!is_null($scopelimit)) { $cab = new Cabinet(); $cab->CabinetID = $dev->Cabinet; $cab->GetCabinet(); switch ($scopelimit) { case 'cabinet': $limiter = " AND Cabinet={$dev->Cabinet}"; break; case 'row': $limiter = " AND Cabinet IN (SELECT CabinetID FROM fac_Cabinet WHERE CabRowID={$cab->CabRowID} AND CabRowID>0)"; break; case 'zone': $limiter = " AND Cabinet IN (SELECT CabinetID FROM fac_Cabinet WHERE ZoneID={$cab->ZoneID} AND ZoneID>0)"; break; case 'datacenter': $limiter = " AND Cabinet IN (SELECT CabinetID FROM fac_Cabinet WHERE DataCenterID={$cab->DataCenterID})"; break; default: break; } } $pp = ""; if (!is_null($patchpanels)) { $pp = ' AND DeviceType="Patch Panel"'; } $candidates = array(); if (is_null($listports)) { $currentperson = $person; if (!$currentperson->WriteAccess) { $groups = $currentperson->isMemberOf(); // list of groups the current user is member of $rights = null; foreach ($groups as $index => $DeptID) { if (is_null($rights)) { $rights = "Owner={$DeptID}"; } else { $rights .= " OR Owner={$DeptID}"; } } $rights = is_null($rights) ? null : " AND ({$rights})"; } else { $rights = null; } $cabinetID = $dev->GetDeviceCabinetID(); $sqlSameCabDevice = "SELECT * FROM fac_Device WHERE Ports>0 AND \r\n\t\t\t\tCabinet={$cabinetID} {$rights}{$pp}{$limiter} GROUP BY DeviceID ORDER BY Position \r\n\t\t\t\tDESC, Label ASC;"; $sqlDiffCabDevice = "SELECT * FROM fac_Device WHERE Ports>0 AND \r\n\t\t\t\tCabinet!={$cabinetID} {$rights}{$pp}{$limiter} GROUP BY DeviceID ORDER BY Label ASC;"; foreach (array($sqlSameCabDevice, $sqlDiffCabDevice) as $sql) { foreach ($dbh->query($sql) as $row) { // false to skip rights check we filtered using sql above $tmpDev = Device::RowToObject($row, false); $candidates[] = array("DeviceID" => $tmpDev->DeviceID, "Label" => $tmpDev->Label, "CabinetID" => $tmpDev->Cabinet); } } } else { $sql = "SELECT a.*, b.Cabinet as CabinetID FROM fac_Ports a, fac_Device b WHERE \r\n\t\t\t\tPorts>0 AND Cabinet>-1 AND a.DeviceID=b.DeviceID AND \r\n\t\t\t\ta.DeviceID!={$dev->DeviceID} AND ConnectedDeviceID IS NULL{$mediaenforce}{$pp};"; foreach ($dbh->query($sql) as $row) { $candidates[] = array("DeviceID" => $row["DeviceID"], "Label" => $row["Label"], "CabinetID" => $row["CabinetID"]); } } return $candidates; }
function GetMissingMfgDates() { $this->MakeSafe(); $sql = "SELECT a.* FROM fac_Device a, fac_DeviceTemplate b WHERE\n\t\t\ta.TemplateID=b.TemplateID AND b.ManufacturerID={$this->ManufacturerID} AND \n\t\t\ta.MfgDate<'1970-01-01'"; $devList = array(); foreach ($this->query($sql) as $row) { $devList[] = Device::RowToObject($row); } $this->MakeDisplay(); return $devList; }