Ugly? You bet... This is a first attempt to create a schematic of the data connections within a site (think RackRow table). It's an ugly hack and I've never coded in PHP before so please be nice. I'm intending to continue on this but comments from the community are welcome. CC
<?php
// Our purpose is to produce a graphic
header ("Content-type: image/png");
function GetMySQLData(&$devices, &$links, &$sites, &$port_count, &$ports) {
$sql_query = "
select distinct RO.id as ROid1, RO2.id as ROid2, RO.name as ROname1, RO2.name
as ROname2, P.name as pname1, P2.name as pname2, L.porta, RR.name as site
from RackObject RO, RackObject RO2, Port P, Link L, Link L2, Port P2, RackRow
RR, Rack R, RackSpace RS
where RO.name != RO2.name and
RR.id = R.row_id and
R.id = RS.rack_id and
RS.object_id = RO2.id and
RO.id = P.object_id and
P.id = L.porta and
RO2.id = P2.object_id and
(P2.id = L2.porta or P2.id = L2.portb) and
(P.type = 24 or P.type = 19) and
(L2.porta = L.porta and L2.portb = L.portb)
order by L.porta";
include "/var/www/racktables/inc/secret.php";
$dblink = mysql_connect("localhost", $db_username, $db_password) or
die(mysql_error());
mysql_select_db("racktables", $dblink) or die(mysql_error());
$result = mysql_query($sql_query, $dblink) or die(mysql_error());
while($row = mysql_fetch_assoc($result)) {
if (isset($devices[$row['ROname1']])) {
$devices[$row['ROname1']]++;
}
else {
$devices[$row['ROname1']] = 1;
}
if (isset($devices[$row['ROname2']])) {
$devices[$row['ROname2']]++;
}
else {
$devices[$row['ROname2']] = 1;
}
$links[$row['porta']] = array(
"Site" => $row['site'],
"Device1" => $row['ROname1'],
"Device2" => $row['ROname2'],
"Port1" => $row['pname1'],
"Port2" => $row['pname2']);
$sites[$row['ROname1']] = $row['site'];
$sites[$row['ROname2']] = $row['site'];
$presult = mysql_query("SELECT name FROM Port where (type = 19
or type = 24) and object_id = ".$row['ROid1'], $dblink);
$port_count[$row['ROname1']] = mysql_num_rows($presult);
while($prow = mysql_fetch_assoc($presult)) {
$ports[$row['ROname1']][$prow['name']] = 1;
}
$presult = mysql_query("SELECT * FROM Port where (type = 19 or
type = 24) and object_id = ".$row['ROid2'], $dblink);
$port_count[$row['ROname2']] = mysql_num_rows($presult);
while($prow = mysql_fetch_assoc($presult)) {
$ports[$row['ROname2']][$prow['name']] = 1;
}
}
// arsort($devices);
}
function Draw($devices, $links, $site, $sites, $port_count, $ports) {
// I know this function is too big but I'm lacking fu in php
// Create A3 sixed image
$x_max = 1587;
$y_max = 1122;
$handle = ImageCreate ($x_max, $y_max) or die ("Cannot Create image");
$bg_color = ImageColorAllocate ($handle, 255, 255, 255);
$txt_color = ImageColorAllocate ($handle, 0, 0, 0);
$pink = ImageColorAllocate($handle, 255, 105, 180);
$white = ImageColorAllocate($handle, 255, 255, 255);
$green = ImageColorAllocate($handle, 132, 135, 28);
$black = ImageColorAllocate($handle, 0, 0, 0);
$blue = ImageColorAllocate($handle, 0, 0, 255);
ImageString($handle, 5, 750, 18, "RackTables Visualised ($site}",
$txt_color);
$page_size = 4;
$page_counter = 0;
$x_offset = 25;
$y_offset = 50;
$width = 350;
$height = 50;
$x_buffer = $width + intval($width * 0.5);
$y_buffer = $height + 100;
// In this block, $key is the device and $value is the number of links
that device has to other objects
foreach($devices as $key => $value) {
if ($sites[$key] != $site) {
continue;
}
$x = $x_offset;
if ($page_counter == 0) {
$y = $y_offset;
}
else {
$y = $y_buffer * $page_counter + 50;
}
$coord[$key] = array('x' => $x, 'y' => intval($y + $height /
2));
// Draw the device
ImageRectangle($handle, $x, $y, $x + $width, $y + $height,
$black);
// Label the device
ImageString($handle, 2, $x + 5, $y + 5, $key, $txt_color);
$page_counter++;
if ($page_counter >= $page_size) {
$x_offset = $x_offset + $x_buffer;
$page_counter = 0;
}
}
$tmp_dev = array();
foreach($links as $aa) {
// Should only need to test one end of the link for site
if ($sites[$aa['Device1']] != $site) {
continue;
}
if (isset($tmp_dev[$aa['Device1']])) {
$tmp_dev[$aa['Device1']]++;
}
else {
$tmp_dev[$aa['Device1']] = 1;
}
if (isset($tmp_dev[$aa['Device2']])) {
$tmp_dev[$aa['Device2']]++;
}
else {
$tmp_dev[$aa['Device2']] = 1;
}
// '10' is the margin, '20' includes left and right margins
$unit1 = (($width - 20) / ($port_count[$aa['Device1']] + 1)) /
2;
$d1x = $coord[$aa['Device1']]['x'] + 10 + ($unit1 *
$tmp_dev[$aa['Device1']]);
$d1y = $coord[$aa['Device1']]['y'];
$unit2 = (($width - 20) / ($port_count[$aa['Device2']] + 1)) /
2;
$d2x = $coord[$aa['Device2']]['x'] + 10 + ($unit2 *
$tmp_dev[$aa['Device2']]);
$d2y = $coord[$aa['Device2']]['y'];
$d1_text_offset = ($tmp_dev[$aa['Device1']] % 2) ? -5 : 5;
$d2_text_offset = ($tmp_dev[$aa['Device2']] % 2) ? -5 : 5;
ImageString($handle, 1, $d1x + 2, $d1y + $d1_text_offset,
$aa['Port1'], $txt_color);
ImageString($handle, 1, $d2x + 2, $d2y + $d2_text_offset,
$aa['Port2'], $txt_color);
ImageLine($handle, $d1x, $d1y, $d2x, $d2y, $blue);
}
ob_start();
ImagePng($handle);
$contents = ob_get_contents();
ob_end_clean();
$fh = fopen("/tmp/cc-$site.png", "w");
fwrite($fh, $contents);
fclose($fh);
}
$devices = array();
$links = array();
$link_count = array();
$sites = array();
$ports = array();
$port_count = array();
GetMySQLData($devices, $links, $sites, $port_count, $ports);
foreach($sites as $site) {
Draw($devices, $links, $site, $sites, $port_count, $ports);
}
?>
Attachment:
image.png
Description: PNG image
