<?php
require_once '../../../lib/user.class.php';
$objUser = new User();
$listAllUser = $objUser->GetUserList();
$rowListAllUser = count($listAllUser);
?>
<form id="listAllUser" name="listAllUser" method="post" action="" class="thickbox">
<div id="note"> </div>
<table width="100%" border="0">
<tr>
<td colspan="12"><a class="thickbox" href="/iims/home.php" title="Manufacture List" ></a></td>
</tr>
<tr>
<td width="142"><strong>User Name </strong></td>
<td width="138"><strong>Login Name </strong></td>
<td width="139"><b>Password</b></td>
<td width="142"><b>E-mail</b></td>
<td width="169"><strong>Address</strong></td>
<td width="169"><strong>Contact</strong></td>
<td width="169"><strong>Section</strong></td>
<td width="169"><strong>Department</strong></td>
<td width="169"><strong>Office</strong></td>
<td width="169"><strong>Designation</strong></td>
<td colspan="2"><strong>Level</strong></td>
</tr>
<?php
for ($i = 0; $i < $rowListAllUser; $i++) {
if ($i % 2 == 0) {
$bgc = "#F7F7F7";
function upgrade()
{
global $version;
global $config;
global $results;
global $dbh;
global $errormsg;
if ($version == "") {
// something borked re-apply all database updates and pray for forgiveness
$version = "1.2";
}
if ($version == "1.2") {
// Do 1.2 to 1.3 Update
$results[] = applyupdate("db-1.2-to-1.3.sql");
$version = "1.3";
}
if ($version == "1.3") {
// Do 1.3 to 1.4 Update
// Clean the configuration table of any duplicate values that might have been added.
$config->rebuild();
$results[] = applyupdate("db-1.3-to-1.4.sql");
$version = "1.4";
}
if ($version == "1.4") {
// Do 1.4 to 1.5 Update
// A few of the database changes require some tests to ensure that they will be able to apply.
// Both of these need to return 0 results before we continue or the database schema update will not complete.
$conflicts = 0;
$sql = "SELECT PDUID, CONCAT(PDUID,'-',PDUPosition) AS KEY1, COUNT(PDUID) AS Count FROM fac_PowerConnection GROUP BY KEY1 HAVING (COUNT(KEY1)>1) ORDER BY PDUID ASC;";
$sth = $dbh->prepare($sql);
$sth->execute();
$conflicts += $sth->rowCount() > 0 ? 1 : 0;
$sql = "SELECT DeviceID, CONCAT(DeviceID,'-',DeviceConnNumber) AS KEY2, COUNT(DeviceID) AS Count FROM fac_PowerConnection GROUP BY KEY2 HAVING (COUNT(KEY2)>1) ORDER BY DeviceID ASC;";
$sth = $dbh->prepare($sql);
$sth->execute();
$conflicts += $sth->rowCount() > 0 ? 1 : 0;
$sql = "SELECT SwitchDeviceID, CONCAT(SwitchDeviceID,'-',SwitchPortNumber) AS KEY1, COUNT(SwitchDeviceID) AS Count FROM fac_SwitchConnection GROUP BY KEY1 HAVING (COUNT(KEY1)>1) ORDER BY SwitchDeviceID ASC;";
$sth = $dbh->prepare($sql);
$sth->execute();
$conflicts += $sth->rowCount() > 0 ? 1 : 0;
$sql = "SELECT SwitchDeviceID, SwitchPortNumber, EndpointDeviceID, EndpointPort, CONCAT(EndpointDeviceID,'-',EndpointPort) AS KEY2, COUNT(EndpointDeviceID) AS Count FROM fac_SwitchConnection GROUP BY KEY2 HAVING (COUNT(KEY2)>1) ORDER BY EndpointDeviceID ASC;";
$sth = $dbh->prepare($sql);
$sth->execute();
$conflicts += $sth->rowCount() > 0 ? 1 : 0;
require_once "facilities.inc.php";
if ($conflicts != 0) {
header('Location: ' . redirect("conflicts.php"));
exit;
}
$config->rebuild();
$results[] = applyupdate("db-1.4-to-1.5.sql");
$version = "1.5";
}
if ($version == "1.5") {
// Do the 1.5 to 2.0 Update
// Get a list of all Manufacturers that are duplicated
$sql = "SELECT ManufacturerID,Name FROM fac_Manufacturer GROUP BY Name HAVING COUNT(*)>1;";
foreach ($dbh->query($sql) as $row) {
// Set all devices with that Manufacturer to the ID of just one
$sql = "UPDATE fac_DeviceTemplate SET ManufacturerID={$row["ManufacturerID"]} WHERE ManufacturerID IN (SELECT ManufacturerID FROM fac_Manufacturer WHERE Name=\"{$row["Name"]}\");";
$dbh->query($sql);
// Delete all the duplicates other than the one you set everything to
$sql = "DELETE FROM fac_Manufacturer WHERE Name=\"{$row["Name"]}\" and ManufacturerID!={$row["ManufacturerID"]};";
$dbh->query($sql);
}
// Repeat for Templates
$sql = "SELECT TemplateID,ManufacturerID,Model FROM fac_DeviceTemplate GROUP BY ManufacturerID,Model HAVING COUNT(*)>1;";
foreach ($dbh->query($sql) as $row) {
$sql = "UPDATE fac_Device SET TemplateID={$row["TemplateID"]} WHERE TemplateID IN (SELECT TemplateID FROM fac_DeviceTemplate WHERE ManufacturerID={$row["ManufacturerID"]} AND Model=\"{$row["Model"]}\");";
$dbh->query($sql);
$sql = "DELETE FROM fac_DeviceTemplate WHERE ManufacturerID={$row["ManufacturerID"]} AND TemplateID!={$row["TemplateID"]};";
$dbh->query($sql);
}
// And finally, Departments
$sql = "SELECT DeptID, Name FROM fac_Department GROUP BY Name HAVING COUNT(*)>1;";
foreach ($dbh->query($sql) as $row) {
$sql = "UPDATE fac_Device SET Owner={$row["DeptID"]} WHERE Owner IN (SELECT DeptID FROM fac_Department WHERE Name=\"{$row["Name"]}\");";
$dbh->query($sql);
// Yes, I know, this may create duplicates
$sql = "UPDATE fac_DeptContacts SET DeptID={$row["DeptID"]} WHERE DeptID IN (SELECT DeptID FROM fac_Department WHERE Name=\"{$row["Name"]}\");";
$dbh->query($sql);
$sql = "DELETE FROM fac_Department WHERE Name=\"{$row["Name"]}\" AND DeptID!={$row["DeptID"]};";
$dbh->query($sql);
}
// So delete the potential duplicate contact links created in the last step
$sql = "SELECT DeptID,ContactID FROM fac_DeptContacts GROUP BY DeptID,ContactID HAVING COUNT(*)>1;";
foreach ($dbh->query($sql) as $row) {
$sql = "DELETE FROM fac_DeptContacts WHERE DeptID={$row["DeptID"]} AND ContactID={$row["ContactID"]};";
$dbh->query($sql);
$sql = "INSERT INTO fac_DeptContacts VALUES ({$row["DeptID"]},{$row["ContactID"]});";
$dbh->query($sql);
}
/*
/ Clean up multiple key issues.
/
/ 1. Identify Multiple Keys
/ 2. Remove them
/ 3. Recreate keys based on structure in create.sql
/
*/
$array = array();
$sql = "SHOW INDEXES FROM fac_PowerConnection;";
foreach ($dbh->query($sql) as $row) {
$array[$row["Key_name"]] = 1;
}
foreach ($array as $key => $garbage) {
$sql = "ALTER TABLE fac_PowerConnection DROP INDEX {$key};";
$dbh->query($sql);
}
$sql = "ALTER TABLE fac_PowerConnection ADD UNIQUE KEY PDUID (PDUID,PDUPosition);";
$dbh->query($sql);
$sql = "ALTER TABLE fac_PowerConnection ADD UNIQUE KEY DeviceID (DeviceID,DeviceConnNumber);";
$dbh->query($sql);
// Just removing keys from fac_CabinetAudit
$array = array();
$sql = "SHOW INDEXES FROM fac_CabinetAudit;";
foreach ($dbh->query($sql) as $row) {
$array[$row["Key_name"]] = 1;
}
foreach ($array as $key => $garbage) {
$sql = "ALTER TABLE fac_CabinetAudit DROP INDEX {$key};";
$dbh->query($sql);
}
$array = array();
$sql = "SHOW INDEXES FROM fac_Department;";
foreach ($dbh->query($sql) as $row) {
$array[$row["Key_name"]] = 1;
}
foreach ($array as $key => $garbage) {
$sql = "ALTER TABLE fac_Department DROP INDEX {$key};";
$dbh->query($sql);
}
$sql = "ALTER TABLE fac_Department ADD PRIMARY KEY (DeptID);";
$dbh->query($sql);
$sql = "ALTER TABLE fac_Department ADD UNIQUE KEY Name (Name);";
$dbh->query($sql);
$config->rebuild();
$results[] = applyupdate("db-1.5-to-2.0.sql");
$version = "2.0";
}
if ($version == "2.0") {
$sql = "select InputAmperage from fac_PowerDistribution limit 1";
// See if the field exists - some people have manually added the missing one already, so we can't add what's already there
if (!$dbh->query($sql)) {
// if(mysql_errno($facDB)==1054){
$sql = "ALTER TABLE fac_PowerDistribution ADD COLUMN InputAmperage INT(11) NOT NULL AFTER PanelPole";
$dbh->query($sql);
}
$sql = 'UPDATE fac_Config SET Value="2.0.1" WHERE Parameter="Version"';
$dbh->query($sql);
$version = "2.0.1";
}
if ($version == "2.0.1") {
// Get a list of all Manufacturers that are duplicated
$sql = "SELECT ManufacturerID,Name FROM fac_Manufacturer GROUP BY Name HAVING COUNT(*)>1;";
foreach ($dbh->query($sql) as $row) {
// Set all devices with that Manufacturer to the ID of just one
$sql = "UPDATE fac_DeviceTemplate SET ManufacturerID={$row["ManufacturerID"]} WHERE ManufacturerID IN (SELECT ManufacturerID FROM fac_Manufacturer WHERE Name=\"{$row["Name"]}\");";
$dbh->query($sql);
// Delete all the duplicates other than the one you set everything to
$sql = "DELETE FROM fac_Manufacturer WHERE Name=\"{$row["Name"]}\" and ManufacturerID!={$row["ManufacturerID"]};";
$dbh->query($sql);
}
// Repeat for Templates
$sql = "SELECT TemplateID,ManufacturerID,Model FROM fac_DeviceTemplate GROUP BY ManufacturerID,Model HAVING COUNT(*)>1;";
foreach ($dbh->query($sql) as $row) {
$sql = "UPDATE fac_Device SET TemplateID={$row["TemplateID"]} WHERE TemplateID IN (SELECT TemplateID FROM fac_DeviceTemplate WHERE ManufacturerID={$row["ManufacturerID"]} AND Model=\"{$row["Model"]}\");";
$dbh->query($sql);
$sql = "DELETE FROM fac_DeviceTemplate WHERE ManufacturerID={$row["ManufacturerID"]} AND TemplateID!={$row["TemplateID"]};";
$dbh->query($sql);
}
// Clean up multiple indexes in fac_Department
$array = array();
$sql = "SHOW INDEXES FROM fac_Department;";
foreach ($dbh->query($sql) as $row) {
$array[$row["Key_name"]] = 1;
}
foreach ($array as $key => $garbage) {
$sql = "ALTER TABLE fac_Department DROP INDEX {$key};";
$dbh->query($sql);
}
$sql = "ALTER TABLE fac_Department ADD PRIMARY KEY (DeptID);";
$dbh->query($sql);
$sql = "ALTER TABLE fac_Department ADD UNIQUE KEY Name (Name);";
$dbh->query($sql);
// Clean up multiple indexes in fac_DeviceTemplate
$array = array();
$sql = "SHOW INDEXES FROM fac_DeviceTemplate;";
foreach ($dbh->query($sql) as $row) {
$array[$row["Key_name"]] = 1;
}
foreach ($array as $key => $garbage) {
$sql = "ALTER TABLE fac_Department DROP INDEX {$key};";
$dbh->query($sql);
}
$sql = "ALTER TABLE fac_DeviceTemplate ADD PRIMARY KEY (TemplateID);";
$dbh->query($sql);
$sql = "ALTER TABLE fac_DeviceTemplate ADD UNIQUE KEY ManufacturerID (ManufacturerID,Model);";
$dbh->query($sql);
// Apply SQL Updates
$results[] = applyupdate("db-2.0-to-2.1.sql");
// Add new field for the ConnectionID
$dbh->query('ALTER TABLE fac_SwitchConnection ADD ConnectionID INT NULL DEFAULT NULL;');
$sql = "SELECT * FROM fac_SwitchConnection;";
foreach ($dbh->query($sql) as $row) {
$insert = "INSERT INTO fac_DevicePorts VALUES (NULL , '{$row['EndpointDeviceID']}', '{$row['EndpointPort']}', NULL , '{$row['Notes']}');";
$dbh->query($insert);
$update = "UPDATE fac_SwitchConnection SET ConnectionID='" . $dbh->lastInsertId() . "' WHERE EndpointDeviceID='{$row['EndpointDeviceID']}' AND EndpointPort='{$row['EndpointPort']}';";
$dbh->query($update);
}
// Clear eany old primary key information
$dbh->query('ALTER TABLE fac_SwitchConnection DROP PRIMARY KEY;');
// Ensure new ConnectionID is unique
$dbh->query('ALTER TABLE fac_SwitchConnection ADD UNIQUE(ConnectionID);');
// Rebuild the config table just in case. I dunno gremlins.
$config->rebuild();
$version = "2.1";
}
if ($version == "2.1") {
// First apply the schema updates needed.
$results[] = applyupdate("db-2.1-to-3.0.sql");
print "Update blade devices for new front/rear tracking method<br>\n";
$sql = "UPDATE fac_Device SET BackSide=1, ChassisSlots=0 WHERE ChassisSlots=1 AND ParentDevice>0;";
$dbh->query($sql);
// Port conversion
$numports = $dbh->query('SELECT SUM(Ports) + (SELECT SUM(Ports) FROM fac_Device WHERE DeviceType="Patch Panel") as TotalPorts, COUNT(DeviceID) as Devices FROM fac_Device')->fetch();
print "Creating {$numports['TotalPorts']} ports for {$numports['Devices']} devices. <b>THIS MAY TAKE A WHILE</b><br>\n";
// Retrieve a list of all devices and make ports for them.
$sql = 'SELECT DeviceID,Ports,DeviceType from fac_Device WHERE
DeviceType!="Physical Infrastructure" AND Ports>0;';
$errors = array();
$ports = array();
foreach ($dbh->query($sql) as $row) {
for ($x = 1; $x <= $row['Ports']; $x++) {
// Create a port for every device
$ports[$row['DeviceID']][$x]['Label'] = $x;
if ($row['DeviceType'] == 'Patch Panel') {
// Patch panels needs rear ports as well
$ports[$row['DeviceID']][-$x]['Label'] = "Rear {$x}";
}
}
}
$findswitch = $dbh->prepare('SELECT * FROM fac_SwitchConnection WHERE EndpointDeviceID=:deviceid ORDER BY EndpointPort ASC;');
foreach ($ports as $deviceid => $port) {
$findswitch->execute(array(':deviceid' => $deviceid));
$defined = $findswitch->fetchAll();
foreach ($defined as $row) {
// Weed out any port numbers that have been defined outside the range of
// valid ports for the device
if (isset($ports[$deviceid][$row['EndpointPort']])) {
// Device Ports
$ports[$deviceid][$row['EndpointPort']]['Notes'] = $row['Notes'];
$ports[$deviceid][$row['EndpointPort']]['Connected Device'] = $row['SwitchDeviceID'];
$ports[$deviceid][$row['EndpointPort']]['Connected Port'] = $row['SwitchPortNumber'];
// Switch Ports
$ports[$row['SwitchDeviceID']][$row['SwitchPortNumber']]['Notes'] = $row['Notes'];
$ports[$row['SwitchDeviceID']][$row['SwitchPortNumber']]['Connected Device'] = $row['EndpointDeviceID'];
$ports[$row['SwitchDeviceID']][$row['SwitchPortNumber']]['Connected Port'] = $row['EndpointPort'];
} else {
// Either display this as a log item later or possibly backfill empty
// ports with this data
$errors[$deviceid][$row['EndpointPort']]['Notes'] = $row['Notes'];
$errors[$deviceid][$row['EndpointPort']]['Connected Device'] = $row['SwitchDeviceID'];
$errors[$deviceid][$row['EndpointPort']]['Connected Port'] = $row['SwitchPortNumber'];
}
}
}
$findpatch = $dbh->prepare('SELECT * FROM fac_PatchConnection WHERE FrontEndpointDeviceID=:deviceid ORDER BY FrontEndpointPort ASC;');
foreach ($ports as $deviceid => $port) {
$findpatch->execute(array(':deviceid' => $deviceid));
$defined = $findpatch->fetchAll();
foreach ($defined as $row) {
// Weed out any port numbers that have been defined outside the range of
// valid ports for the device
if (isset($ports[$deviceid][$row['FrontEndpointPort']])) {
// Connect the device to the panel
$ports[$deviceid][$row['FrontEndpointPort']]['Notes'] = $row['FrontNotes'];
$ports[$deviceid][$row['FrontEndpointPort']]['Connected Device'] = $row['PanelDeviceID'];
$ports[$deviceid][$row['FrontEndpointPort']]['Connected Port'] = $row['PanelPortNumber'];
// Connect the panel to the device
$ports[$row['PanelDeviceID']][$row['PanelPortNumber']]['Connected Device'] = $row['FrontEndpointDeviceID'];
$ports[$row['PanelDeviceID']][$row['PanelPortNumber']]['Connected Port'] = $row['FrontEndpointPort'];
$ports[$row['PanelDeviceID']][$row['PanelPortNumber']]['Notes'] = $row['FrontNotes'];
} else {
// Either display this as a log item later or possibly backfill empty
// ports with this data
$errors[$deviceid][$row['FrontEndpointPort']]['Notes'] = $row['FrontNotes'];
$errors[$deviceid][$row['FrontEndpointPort']]['Connected Device'] = $row['PanelDeviceID'];
$errors[$deviceid][$row['FrontEndpointPort']]['Connected Port'] = $row['PanelPortNumber'];
}
}
}
foreach ($dbh->query('SELECT * FROM fac_PatchConnection;') as $row) {
// Read all the patch connections again to get the rear connection info
$ports[$row['RearEndpointDeviceID']][-$row['RearEndpointPort']]['Connected Device'] = $row['PanelDeviceID'];
$ports[$row['RearEndpointDeviceID']][-$row['RearEndpointPort']]['Connected Port'] = -$row['PanelPortNumber'];
$ports[$row['RearEndpointDeviceID']][-$row['RearEndpointPort']]['Notes'] = $row['RearNotes'];
$ports[$row['PanelDeviceID']][-$row['PanelPortNumber']]['Connected Device'] = $row['RearEndpointDeviceID'];
$ports[$row['PanelDeviceID']][-$row['PanelPortNumber']]['Connected Port'] = -$row['RearEndpointPort'];
$ports[$row['PanelDeviceID']][-$row['PanelPortNumber']]['Notes'] = $row['RearNotes'];
}
// Backfill the extra data
foreach ($errors as $deviceid => $row) {
$numPorts = count($ports[$deviceid]) + 1;
foreach ($row as $portnum => $port) {
for ($n = 1; $n < $numPorts; $n++) {
if (!isset($ports[$deviceid][$n]['Notes'])) {
$ports[$deviceid][$n] = $port;
// connect up the other side as well
$ports[$port['Connected Device']][$port['Connected Port']]['Connected Device'] = $deviceid;
$ports[$port['Connected Device']][$port['Connected Port']]['Connected Port'] = $n;
$ports[$port['Connected Device']][$port['Connected Port']]['Notes'] = $port['Notes'];
unset($errors[$deviceid][$portnum]);
// Remove it from the backfill data
break;
}
}
}
}
$incdataports = $dbh->prepare('UPDATE fac_Device SET Ports=:n WHERE DeviceID=:deviceid;');
// Anything left in $errors now is an extra port that exists outside the number of designated deviceports.
foreach ($errors as $deviceid => $row) {
foreach ($row as $portnum => $port) {
$n = count($ports[$deviceid]) + 1;
$ports[$deviceid][] = $port;
// connect up the other side as well, $n will give us the new port number
// since it is outside the defined range for the device
$ports[$port['Connected Device']][$port['Connected Port']]['Connected Device'] = $deviceid;
$ports[$port['Connected Device']][$port['Connected Port']]['Connected Port'] = $n;
$ports[$port['Connected Device']][$port['Connected Port']]['Notes'] = $port['Notes'];
// Update the number of ports on this device to match the corrected value
$incdataports->execute(array(":n" => $n, ":deviceid" => $deviceid));
unset($errors[$deviceid][$portnum]);
// Remove it from the backfill data
}
}
$n = 1;
$insertsql = '';
$insertlimit = 100;
$insertprogress = 100 / ($numports['TotalPorts'] / $insertlimit);
$insertprogress = intval($insertprogress) > 0 ? intval($insertprogress) : 1;
// if this is gonna do more than 100 inserts we might have issues
echo "<br>\nConversion process: <br>\n<table style=\"border: 1px solid black; border-collapse: collapse; width: 100%;\"><tr>";
flush();
// All the ports should be in the array now, use the prepared statement to load them all
foreach ($ports as $deviceid => $row) {
foreach ($row as $portnum => $port) {
$null = null;
$blank = "";
$cdevice = isset($port['Connected Device']) ? $port['Connected Device'] : 'NULL';
$cport = isset($port['Connected Port']) ? $port['Connected Port'] : 'NULL';
$notes = isset($port['Notes']) ? $port['Notes'] : '';
$insertsql .= "({$deviceid},{$portnum},\"\",0,0,\"\",{$cdevice},{$cport},\"{$notes}\")";
if ($n % $insertlimit != 0) {
$insertsql .= " ,";
} else {
$dbh->exec('INSERT INTO fac_Ports VALUES' . $insertsql);
$insertsql = '';
print "<td width=\"{$insertprogress}%\" style=\"background-color: green\"> </td>";
flush();
// attempt to update the progress as this goes on.
}
++$n;
}
}
//do one last insert
$insertsql = substr($insertsql, 0, -1);
// shave off that last comma
$dbh->exec('INSERT INTO fac_Ports VALUES' . $insertsql);
echo "</tr></table>";
print "<br>\nPort conversion complete.<br>\n";
// Rebuild the config table just in case.
$config->rebuild();
$version = "3.0";
}
if ($version == "3.0") {
// First apply the schema updates needed.
$results[] = applyupdate("db-3.0-to-3.1.sql");
// Rebuild the config table just in case.
$config->rebuild();
$version = "3.1";
}
if ($version == "3.1") {
// First apply the schema updates needed.
$results[] = applyupdate("db-3.1-to-3.2.sql");
// Rebuild the config table just in case.
$config->rebuild();
$version = "3.2";
}
if ($version == "3.2") {
// First apply the schema updates needed.
$results[] = applyupdate("db-3.2-to-3.3.sql");
// Rebuild the config table just in case.
$config->rebuild();
$version = "3.3";
}
if ($version == "3.3") {
// First apply the schema updates needed.
$results[] = applyupdate("db-3.3-to-4.0.sql");
// Rebuild the config table just in case.
$config->rebuild();
// We added in some new config items and one of them is referenced in misc.
// Reload the config;
$config->Config();
// We have several things to convert this time around.
// Bring up the rest of the classes
require_once "facilities.inc.php";
// People conversion
$p = new People();
$c = new Contact();
$u = new User();
$plist = $p->GetUserList();
// Check if we have an empty fac_People table then merge if that's the case
if (sizeof($plist) == 0) {
$clist = $c->GetContactList();
foreach ($clist as $tmpc) {
foreach ($tmpc as $prop => $val) {
$p->{$prop} = $val;
}
// we're keeping the Contact ID so assign it to the PersonID
$p->PersonID = $tmpc->ContactID;
$u->UserID = $p->UserID;
$u->GetUserRights();
foreach ($u as $prop => $val) {
$p->{$prop} = $val;
}
// This shouldn't be necessary but...
$p->MakeSafe();
$sql = "INSERT INTO fac_People SET PersonID={$p->PersonID}, UserID=\"{$p->UserID}\", \n\t\t\t\t\tAdminOwnDevices={$p->AdminOwnDevices}, ReadAccess={$p->ReadAccess}, \n\t\t\t\t\tWriteAccess={$p->WriteAccess}, DeleteAccess={$p->DeleteAccess}, \n\t\t\t\t\tContactAdmin={$p->ContactAdmin}, RackRequest={$p->RackRequest}, \n\t\t\t\t\tRackAdmin={$p->RackAdmin}, SiteAdmin={$p->SiteAdmin}, Disabled={$p->Disabled}, \n\t\t\t\t\tLastName=\"{$p->LastName}\", FirstName=\"{$p->FirstName}\", \n\t\t\t\t\tPhone1=\"{$p->Phone1}\", Phone2=\"{$p->Phone2}\", Phone3=\"{$p->Phone3}\", \n\t\t\t\t\tEmail=\"{$p->Email}\";";
$dbh->query($sql);
}
$ulist = $u->GetUserList();
foreach ($ulist as $tmpu) {
/* This time around we have to see if the User is already in the fac_People table */
$p->UserID = $tmpu->UserID;
if (!$p->GetPersonByUserID()) {
foreach ($tmpu as $prop => $val) {
$p->{$prop} = $val;
}
// Names have changed formats between the user table and the people table
$p->LastName = $tmpu->Name;
$p->CreatePerson();
}
}
}
// END - People conversion
// CDU template conversion, to be done prior to device conversion
/*
I made a poor asumption on the initial build of this that we'd always have fewer
CDU templates than device templates. We're seeing an overlap conversion that is
screwing the pooch. This will find the highest template id from the two sets then
we'll jump the line on the device_template id's and get them lined up.
*/
$sql = "SELECT TemplateID FROM fac_CDUTemplate UNION SELECT TemplateID FROM \n\t\t\tfac_DeviceTemplate ORDER BY TemplateID DESC LIMIT 1;";
$baseid = $dbh->query($sql)->fetchColumn();
class PowerTemplate extends DeviceTemplate
{
function CreateTemplate($templateid = null)
{
global $dbh;
$this->MakeSafe();
$sqlinsert = is_null($templateid) ? '' : " TemplateID={$templateid},";
$sql = "INSERT INTO fac_DeviceTemplate SET ManufacturerID={$this->ManufacturerID}, \n\t\t\t\t\tModel=\"{$this->Model}\", Height={$this->Height}, Weight={$this->Weight}, \n\t\t\t\t\tWattage={$this->Wattage}, DeviceType=\"{$this->DeviceType}\", \n\t\t\t\t\tSNMPVersion=\"{$this->SNMPVersion}\", PSCount={$this->PSCount}, \n\t\t\t\t\tNumPorts={$this->NumPorts}, Notes=\"{$this->Notes}\", \n\t\t\t\t\tFrontPictureFile=\"{$this->FrontPictureFile}\", \n\t\t\t\t\tRearPictureFile=\"{$this->RearPictureFile}\",{$sqlinsert}\n\t\t\t\t\tChassisSlots={$this->ChassisSlots}, RearChassisSlots={$this->RearChassisSlots};";
if (!$dbh->exec($sql)) {
error_log("SQL Error: " . $sql);
return false;
} else {
$this->TemplateID = $dbh->lastInsertId();
class_exists('LogActions') ? LogActions::LogThis($this) : '';
$this->MakeDisplay();
return true;
}
}
static function Convert($row)
{
$ct = new stdClass();
$ct->TemplateID = $row["TemplateID"];
$ct->ManufacturerID = $row["ManufacturerID"];
$ct->Model = $row["Model"];
$ct->PSCount = $row["NumOutlets"];
$ct->SNMPVersion = $row["SNMPVersion"];
return $ct;
}
}
$converted = array();
//index old id, value new id
$sql = "SELECT * FROM fac_CDUTemplate;";
foreach ($dbh->query($sql) as $cdutemplate) {
$ct = PowerTemplate::Convert($cdutemplate);
$dt = new PowerTemplate();
$dt->TemplateID = ++$baseid;
$dt->ManufacturerID = $ct->ManufacturerID;
$dt->Model = "CDU {$ct->Model}";
$dt->PSCount = $ct->PSCount;
$dt->DeviceType = "CDU";
$dt->SNMPVersion = $ct->SNMPVersion;
$dt->CreateTemplate($dt->TemplateID);
$converted[$ct->TemplateID] = $dt->TemplateID;
}
// Update all the records with their new templateid
foreach ($converted as $oldid => $newid) {
$dbh->query("UPDATE fac_CDUTemplate SET TemplateID={$newid} WHERE TemplateID={$oldid};");
$dbh->query("UPDATE fac_PowerDistribution SET TemplateID={$newid} WHERE TemplateID={$oldid}");
}
// END - CDU template conversion
// Store a list of existing CDU ids and their converted DeviceID
$ConvertedCDUs = array();
// Store a list of the cdu template ids and the number of power connections they support
$CDUTemplates = array();
// List of ports we are going to create for every device in the system
$PowerPorts = array();
// These should only apply to me but the possibility exists
$PreNamedPorts = array();
class PowerDevice extends Device
{
/*
to be efficient we don't want to create ports right now so we're extending
the class to override the create function
*/
function CreateDevice()
{
global $dbh;
$this->MakeSafe();
$this->Label = transform($this->Label);
$this->SerialNo = transform($this->SerialNo);
$this->AssetTag = transform($this->AssetTag);
$sql = "INSERT INTO fac_Device SET Label=\"{$this->Label}\", SerialNo=\"{$this->SerialNo}\", AssetTag=\"{$this->AssetTag}\", \n\t\t\t\t\tPrimaryIP=\"{$this->PrimaryIP}\", SNMPCommunity=\"{$this->SNMPCommunity}\", ESX={$this->ESX}, Owner={$this->Owner}, \n\t\t\t\t\tEscalationTimeID={$this->EscalationTimeID}, EscalationID={$this->EscalationID}, PrimaryContact={$this->PrimaryContact}, \n\t\t\t\t\tCabinet={$this->Cabinet}, Position={$this->Position}, Height={$this->Height}, Ports={$this->Ports}, \n\t\t\t\t\tFirstPortNum={$this->FirstPortNum}, TemplateID={$this->TemplateID}, NominalWatts={$this->NominalWatts}, \n\t\t\t\t\tPowerSupplyCount={$this->PowerSupplyCount}, DeviceType=\"{$this->DeviceType}\", ChassisSlots={$this->ChassisSlots}, \n\t\t\t\t\tRearChassisSlots={$this->RearChassisSlots},ParentDevice={$this->ParentDevice}, \n\t\t\t\t\tMfgDate=\"" . date("Y-m-d", strtotime($this->MfgDate)) . "\", \n\t\t\t\t\tInstallDate=\"" . date("Y-m-d", strtotime($this->InstallDate)) . "\", WarrantyCo=\"{$this->WarrantyCo}\", \n\t\t\t\t\tWarrantyExpire=\"" . date("Y-m-d", strtotime($this->WarrantyExpire)) . "\", Notes=\"{$this->Notes}\", \n\t\t\t\t\tReservation={$this->Reservation}, HalfDepth={$this->HalfDepth}, BackSide={$this->BackSide};";
if (!$dbh->exec($sql)) {
$info = $dbh->errorInfo();
error_log("PDO Error: {$info[2]} SQL={$sql}");
return false;
}
$this->DeviceID = $dbh->lastInsertId();
class_exists('LogActions') ? LogActions::LogThis($this) : '';
return $this->DeviceID;
}
}
// Create new devices from existing CDUs
$sql = "SELECT * FROM fac_PowerDistribution;";
foreach ($dbh->query($sql) as $row) {
$dev = new PowerDevice();
$dev->Label = $row['Label'];
$dev->Cabinet = $row['CabinetID'];
$dev->TemplateID = $row['TemplateID'];
$dev->PrimaryIP = $row['IPAddress'];
$dev->SNMPCommunity = $row['SNMPCommunity'];
$dev->Position = 0;
$dev->Height = 0;
$dev->Ports = 1;
if (!isset($CDUTemplates[$dev->TemplateID])) {
$CDUTemplates[$dev->TemplateID] = $dbh->query("SELECT NumOutlets FROM fac_CDUTemplate WHERE TemplateID={$dev->TemplateID} LIMIT 1;")->fetchColumn();
}
$dev->PowerSupplyCount = $CDUTemplates[$dev->TemplateID];
$dev->PowerSupplyCount;
$dev->DeviceType = 'CDU';
$ConvertedCDUs[$row['PDUID']] = $dev->CreateDevice();
}
// Create a list of all ports that we need to create, no need to look at children or any device with no defined power supplies
$sql = "SELECT DeviceID, PowerSupplyCount FROM fac_Device WHERE ParentDevice=0 AND PowerSupplyCount>0;";
foreach ($dbh->query($sql) as $row) {
for ($x = 1; $x <= $row['PowerSupplyCount']; $x++) {
$PowerPorts[$row['DeviceID']][$x]['label'] = $x;
}
}
function workdamnit($numeric = true, &$PreNamedPorts, &$PowerPorts, &$ConvertedCDUs)
{
// a PDUID of 0 is considered an error, data fragment, etc. F**k em, not dealing with em.
global $dbh;
$sql = "SELECT * FROM fac_PowerConnection;";
foreach ($dbh->query($sql) as $row) {
// something is going stupid so assign everythign to variables
$pduid = intval($row['PDUID']);
$pdupos = $row['PDUPosition'];
$devid = intval($row['DeviceID']);
$devcon = $row['DeviceConnNumber'];
$newpduid = $ConvertedCDUs[$pduid];
$port = '';
if (is_numeric($pdupos) && $numeric && $pduid > 0) {
$port = $pdupos;
} elseif (!is_numeric($pdupos) && !$numeric && $pduid > 0) {
$newPDUID = $newpduid;
if (!isset($PreNamedPorts[$newPDUID][$pdupos])) {
// Move the array pointer to the end of the ports array
end($PowerPorts[$newPDUID]);
$max = key($PowerPorts[$newPDUID]);
++$max;
// Create a new port for the named port, this will likely extend past the valid amount of ports on the device.
$PowerPorts[$newPDUID][$max]['label'] = $pdupos;
// Store a pointer between the name and new port index
$PreNamedPorts[$newPDUID][$pdupos] = $max;
}
$port = $PreNamedPorts[$newPDUID][$pdupos];
}
if (is_numeric($pdupos) && $numeric || !is_numeric($pdupos) && !$numeric && $pduid > 0) {
// Create primary connections
$PowerPorts[$devid][$devcon]['ConnectedDeviceID'] = $newpduid;
$PowerPorts[$devid][$devcon]['ConnectedPort'] = $port;
// Create reverse of primary
$PowerPorts[$newpduid][$port]['ConnectedDeviceID'] = $devid;
$PowerPorts[$newpduid][$port]['ConnectedPort'] = $devcon;
}
}
}
// We need to get a list of all existing power connections
workdamnit(true, $PreNamedPorts, $PowerPorts, $ConvertedCDUs);
// First time through setting up all numeric ports
workdamnit(false, $PreNamedPorts, $PowerPorts, $ConvertedCDUs);
// Run through again but this time only deal with named ports and append them to the end of the numeric
/*
* Debug Info
print "Converted CDUs:\n<br>";
print_r($ConvertedCDUs);
print "Port list:\n<br>";
print_r($PowerPorts);
print "SQL entries:\n<br>";
*/
$n = 1;
$insertsql = '';
$insertlimit = 100;
foreach ($PowerPorts as $DeviceID => $PowerPort) {
foreach ($PowerPort as $PortNum => $PortDetails) {
$label = isset($PortDetails['label']) ? $PortDetails['label'] : $PortNum;
$cdevice = isset($PortDetails['ConnectedDeviceID']) ? $PortDetails['ConnectedDeviceID'] : 'NULL';
$cport = isset($PortDetails['ConnectedPort']) ? $PortDetails['ConnectedPort'] : 'NULL';
$insertsql .= "({$DeviceID},{$PortNum},\"{$label}\",{$cdevice},{$cport},\"\")";
if ($n % $insertlimit != 0) {
$insertsql .= " ,";
} else {
$dbh->exec('INSERT INTO fac_PowerPorts VALUES' . $insertsql);
// Debug for sql
// print "$insertsql\n\n<br><br>";
// print_r($dbh->errorInfo());
$insertsql = '';
}
$n++;
}
}
//do one last insert
$insertsql = substr($insertsql, 0, -1);
// shave off that last comma
$dbh->exec('INSERT INTO fac_PowerPorts VALUES' . $insertsql);
// Debug for sql
// print "$insertsql\n\n<br><br>";
// print_r($dbh->errorInfo());
// Update all the records with their new deviceid
foreach ($ConvertedCDUs as $oldid => $newid) {
$dbh->query("UPDATE fac_PowerDistribution SET PDUID = '{$newid}' WHERE PDUID={$oldid};");
}
// Since we moved SNMPVersion out of the subtemplates and into the main one, we need one last cleanup
$st = $dbh->prepare("select * from fac_DeviceTemplate where DeviceType='CDU'");
$st->execute();
$up = $dbh->prepare("update fac_Device set SNMPVersion=:SNMPVersion where TemplateID=:TemplateID");
while ($row = $st->fetch()) {
$up->execute(array(":SNMPVersion" => $row["SNMPVersion"], ":TemplateID" => $row["TemplateID"]));
}
// END - CDU template conversion, to be done prior to device conversion
// Sensor template conversion
// Step one - convert individual SensorTemplates into just Templates
$s = $dbh->prepare("select * from fac_SensorTemplate");
$s->execute();
while ($row = $s->fetch()) {
// Create fresh instances
$st = new SensorTemplate();
$dt = new DeviceTemplate();
$dt->ManufacturerID = $row["ManufacturerID"];
$dt->Model = $row["Model"];
$dt->Height = 0;
$dt->Weight = 0;
$dt->Wattage = 0;
$dt->DeviceType = "Sensor";
$dt->PSCount = 0;
$dt->NumPorts = 0;
$dt->Notes = "Converted from version 3.3 format.";
$dt->FrontPictureFile = '';
$dt->RearPictureFile = '';
$dt->ChassisSlots = 0;
$dt->RearChassisSlots = 0;
$dt->CreateTemplate();
// The DeviceTemplate::CreateTemplate() method created a new SensorTemplate already
if ($dt->TemplateID < 1) {
error_log("DeviceTemplate creation failed.");
} else {
$st->TemplateID = $dt->TemplateID;
$st->GetTemplate();
$st->SNMPVersion = $row["SNMPVersion"];
$st->TemperatureOID = $row["TemperatureOID"];
$st->HumidityOID = $row["HumidityOID"];
$st->TempMultiplier = $row["TempMultiplier"];
$st->HumidityMultiplier = $row["HumidityMultiplier"];
$st->mUnits = $row["mUnits"];
$st->UpdateTemplate();
// Even though this is just temporary, update all existing references to the new TemplateID
$sql = "update fac_Cabinet set SensorTemplateID=:NewID where SensorTemplateID=:OldID";
$q = $dbh->prepare($sql);
$q->execute(array(":NewID" => $dt->TemplateID, ":OldID" => $row["TemplateID"]));
// Delete the original template entry in the fac_SensorTemplate table
$st->TemplateID = $row["TemplateID"];
$st->DeleteTemplate();
}
}
$ds = $dbh->prepare("alter table fac_SensorTemplate drop column SNMPVersion");
// Step two - pull sensors from the Cabinets and create as new devices
$s = $dbh->prepare("select * from fac_Cabinet where SensorIPAddress!=''");
$s->execute();
while ($row = $s->fetch()) {
$dev = new Device();
$dev->Label = $row["Location"] . " - Sensor";
$dev->SNMPCommunity = $row["SensorCommunity"];
$dev->PrimaryIP = $row["SensorIPAddress"];
$dev->TemplateID = $row["SensorTemplateID"];
$dev->DeviceType = "Sensor";
$dev->Cabinet = $row["CabinetID"];
$dev->CreateDevice();
}
// END - Sensor template conversion
// Power panel conversion
$dbh->beginTransaction();
$ss = $dbh->prepare("select * from fac_PowerSource");
$ss->execute();
$ps = $dbh->prepare("insert into fac_PowerPanel set PanelLabel=:PanelLabel");
$us = $dbh->prepare("update fac_PowerPanel set ParentPanelID=:PanelID where PowerSourceID=:SourceID");
while ($row = $ss->fetch()) {
$ps->execute(array(":PanelLabel" => $row["SourceName"]));
$us->execute(array(":PanelID" => $dbh->LastInsertId(), ":SourceID" => $row["PowerSourceID"]));
}
$dbh->commit();
// END - Power panel conversion
// Get rid of the original PowerSource table since it is no longer in use
$drop = $dbh->prepare("drop table fac_PowerSource");
$drop->execute();
$drop = $dbh->prepare("alter table fac_PowerPanel drop column PowerSourceID");
$drop->execute();
// Make sure all child devices have updated cabinet information
$sql = "SELECT DISTINCT ParentDevice AS DeviceID FROM fac_Device WHERE \n\t\t\tParentDevice>0 ORDER BY ParentDevice ASC;";
foreach ($dbh->query($sql) as $row) {
$d = new Device();
$d->DeviceID = $row['DeviceID'];
$d->GetDevice();
$d->UpdateDevice();
}
$version = "4.0";
}
if ($version == "4.0") {
// First apply the schema updates needed.
$results[] = applyupdate("db-4.0-to-4.0.1.sql");
// Rebuild the config table just in case.
$config->rebuild();
}
}
<?php
require_once 'admin.inc.php';
require_once '../include/user.class.php';
$curpage = $_GET["p"];
$curpage = isset($curpage) && is_numeric($curpage) ? $curpage : 1;
if ($curpage < 1) {
$curpage = 1;
}
$userdata = new User();
$usercount = $userdata->GetUserList();
$total = count($usercount);
$take = 20;
$skip = ($curpage - 1) * $take;
$totalpage = (int) ($total % $take == 0 ? $total / $take : $total / $take + 1);
$userlist = $userdata->TakeUserList($skip, $take);
$action = $_POST["action"];
if ($action == "delete") {
$idarr = $_POST["chk"];
if (count($idarr) > 0) {
foreach ($idarr as $id) {
if (is_numeric($id)) {
$sql = "DELETE FROM yiqi_users WHERE uid = '{$id}' limit 1";
$yiqi_db->query(CheckSql($sql));
}
}
ShowMsg("指定用户删除成功");
}
}
$adminpagetitle = "用户列表";
include "admin.header.php";
