csvimport: convert all instances of "SELECT *"
[racktables-contribs] / csvimport / plugin.php
1 <?php
2
3 # Copyright (c) 2014, Erik Ruiter, SURFsara BV, Amsterdam, The Netherlands
4 # All rights reserved.
5 #
6 # This program is free software; you can redistribute it and/or modify
7 # it under the terms of the GNU General Public License as published by
8 # the Free Software Foundation; version 2 of the License.
9
10 /*
11 -----------------------------------------
12
13 csvimport/plugin.php
14
15 Description:
16
17 This is a Racktables plugin which enables loading batches of data into racktables.
18 It adds an CSV import page on the configuration menu screen.
19 From here you can choose to either import a CSV file, or paste some manual CSV lines in a textbox.
20 CSV files can contain multiple types of import data.
21 The script currently supports importing of Objects, Racks, VLANs and IP space.
22 It also supports linking ports, and assigning rackspace to objects.
23
24 The newest version of this plugin can be found at: https://github.com/RackTables/racktables-contribs
25 -----------------------------------------
26 # Plugin updated June 2020 by matt32106@github for compatibility with Racktables version 0.21 new plugin format
27 # Functions added
28 # plugin_csvimport_info
29 # plugin_csvimport_init
30 # plugin_csvimport_install
31 # plugin_csvimport_uninstall
32 # plugin_csvimport_upgrade
33 # Uninstall previous version first (the code does not handle it) before using this one !!!
34 # TODO: use the install/upgrade function to remove previous version
35 # TODO: check if the add functions are still consistent with 0.21 functions
36
37 Usage:
38
39 * Importing Objects:
40
41 Syntax: OBJECT; Objecttype ; Common name ; Visible label ; Asset tag; portname,portname,etc ; porttype,porttype,etc
42
43 Value 1, OBJECT
44 Value 2, Objectype: Can be one of the predefined types (SERVER, PATCHPANEL, SWITCH, VM), or a numeric value indicating the object type from Racktables
45 Value 3, Common name: Common name string
46 Value 4, Visible label: Visible label string
47 Value 5, Asset tag: Asset tag string
48 Value 6, Port array: This is an optional field where you can create ports for the objects, separated by a comma. When you use this , you also need to add the Port type array
49 An individual port field can be a range of ports. Eg. 'eth[0-9]' creates ten ports ranging from eth0 to eth9.
50 Value 7, Port type array: This is an array which maps to the previous port array. This allows you to specify the interface type of the ports. It takes the form 'a-b'. Where a is the inner interface type, and b the outer interface type. Both a and b are numeric values.
51 New inner / outer interface pair types can be linked using the configuration -> Enabled port types page. When the 'a-' value is ommited, the inner port type defaults to 'hardwire'.
52 Examples:
53
54 OBJECT;SERVER;myServer;www.server.com;SRV001;IPMI,eth[0-2];1-24,3-24
55 Creates a Server object named myServer having 4x 1000-Base-T interfaces, named IPMI (hardwired inner iface, 1gbps), eth0, eth1 and eth2 (gbic inner iface, 1gbps)
56
57 OBJECT;SWITCH;myAccessSwitch1;testswitch;SW0001;ge-0/0/[0-11],fe-0/1/[0-11];24,19
58 Creates a Switch object named myAccessSwitch1 having 12x 1000-Base-T interfaces named ge-0/0/0 to ge-0/0/11. And also 12x 100-Base-TX interfaces named fe-0/1/0 to fe-0/1/11.
59
60
61 * Importing Racks
62
63 Syntax: RACK; Location ; Location child ; Row ; Rack; Height
64 Value 1, RACK
65 Value 2, Location ; Specifies the location where the rack is to be placed. This can be the location name string of an existing location. If the location does not exist, it will be created.
66 Value 3, Location child ; Specifies the child location (eg room) where the rack is to be placed. This can be the name of an existing location. If the location does not exist, it will be created.
67 Value 4, Row: Specifies the row where the rack is to be placed. This can be the name of an existing row. If the row does not exist, it will be created
68 Value 5, Rack: Name of the rack that is to be created.
69 Value 6, Height: Sets the Height of the rack in rackunits. When omitted, the default value is 46 units.
70
71 Examples:
72
73 RACK;Datacenter AMS01; Room 0.08; R01; AA-1
74 Creates a rack named AA-1 in Room 0.08 of location Datacenter AMS01, with a height of 46 units.
75
76
77 * Assigning Rackspace
78
79 Syntax: RACKASSIGNMENT; Object name ;Rack; units ; fib
80 Value 1, RACKASSIGNMENT
81 Value 2, Object name: Name of the Racktables object
82 Value 3, Rack; NAme of the rack where the object is to be placed.
83 Value 4, units: List of units to be assigned to the object. The unit numbers are separated by a comma. 0 for Zero-U.
84 Value 5, fib: List of Front / Interior / Back indication. This list maps directly to the previous unit list.
85
86 Examples:
87
88 RACKASSIGNMENT;myServer;AA-1;32,33,34,35;fi,fi,fi,fi
89 Mounts the myServer object in Rack AA-1 on rackunits 32-35, using front and interior part of the rack units.
90
91
92 * Linking ports
93
94 Syntax:CABLELINK; Objectname A; Portname A; Objectname B; Portname B; Cable ID
95 Value 1, CABLELINK
96 Value 2, Objectname A: Specifies the name of the object on the A-side of the link
97 Value 3, Portname A: Specifies the name of the port on the A-side of the link
98 Value 4, Objectname B: Specifies the name of the object on the B-side of the link
99 Value 5, Portname B: Specifies the name of the port on the B-side of the link
100 Value 6, Cable ID: Specifies the Cable ID. It can be numeric or string.
101
102 Examples:
103
104 CABLELINK;myServer;eth1;myAccessSwitch1;ge-0/0/1;0080123
105 Connects the eth1 port of myServer to the ge-0/0/1 port of myAccessSwitch1, using cable ID 0080123
106
107
108 * Importing VLANs
109
110 Syntax: VLAN; VLAN domain; VLAN name; VLAN ID ; Propagation; Attached IP
111 Value 1, VLAN
112 Value 2, VLAN domain: Specifies the name of the VLAN domain where the VLAN is to be added. If the domain does not exist, it will be created.
113 Value 3, VLAN name: Specifies the name of the to be created VLAN.
114 Value 4, Propagation: Sets the Racktables propagation feature for the VLAN, options are ondemand or compulsory. When ommitted the value defaults to compulsory.
115 Value 5, Attached IP: This is an optional list of existing IPv4/IPv6 networks which can be assigned to the VLAN. The ranges should not have netmasks, and each range is separated by a comma.
116
117 Examples:
118
119 VLAN;Private;Netops;1020;compulsory;10.1.3.0,2001:610:1020::0
120 Creates VLAN 1020, named Netops having the IPv4 range 10.1.3.0 and the IPv6 range 2001:610:1020::0 attached.
121
122
123 * Importing IP space
124
125 Syntax: IP; Prefix; Name; is_connected; VLAN domain; VLAN ID
126 Value 1, IP
127 Value 2, Prefix: Specifies the IPv4 / IPv6 prefix of the network, including netmask.
128 Value 3, Name: Specifies the name of the network which is to be added.
129 Value 4, is_connected: Specifies if broadcast and network address in the subnet need to be reserved. Can be TRUE or FALSE. When omitted, the default is FALSE
130 Value 5, VLAN domain: This is an optional value which can be used to set the VLAN domain of the network. You have to specifiy the name of the VLAN domain.
131 Value 6, VLAN ID: This is an optional numeric value setting the VLAN ID of the network. It is to be used in conjunction with the previous VLAN domain value.
132
133 Examples:
134
135 IP;10.1.3.0/24;Netops network;TRUE;SURFsara;1020
136 Creates the IP network 10.1.3.0/24 called 'Netops network' and attaches it to VLAN 1020 in the SURFsara VLAN domain.
137
138
139 * Importing Object IP interfaces
140
141 Syntax: OBJECTIP; Objectname; OS Interface name; IP address; Type
142 Value 1, OBJECTIP
143 Value 2, Objectname: Specifies the name of the object
144 Value 3, OS Interface name: Specifies the name of the interface to be added
145 Value 4, IP address: Specifies the ip address of the interface to b e added (IPv4 or Ipv6) no subnet mask required
146 Value 5, Type: Chooses the type of interface to be added. Can be: regular, virtual, shared, router, point2point. The default type is: router
147
148 Examples:
149
150 OBJECTIP;myRouter;eth0;10.1.3.1;regular
151 Creates an IP interface name eth0, with address 10.1.3.1 and type 'regular', which is added to the myRouter object.
152
153 * Setting Object Attributes:
154
155 Syntax: OBJECTATTRIBUTE
156 Value 1, OBJECTATTRIBUTE
157 Value 2, Objectname: Specifies the name of the object
158 Value 3, attribute id: Specifies the numeric ID of the attribute (can be looked up in Attribute table), also some general attributes are supported, in this case use: NAME / LABEL / ASSETTAG / HASPROBLEMS (yes|no) / COMMENT
159 Value 4, attribute value; Specificies the value to be set for the attribute
160
161 Examples:
162 OBJECTATTRIBUTE;myRouter;3;mgmt.myrouter.com
163 Sets the FQDN (3) for the myRouter object.
164
165 OBJECTATTRIBUTE;myRouter;COMMENT;This is a comment
166 Sets the comment field for the myRouter object.
167
168 * Creating Container Link:
169
170 Syntax: CONTAINERLINK
171 Value 1, CONTAINERLINK
172 Value 2, Parent Object Name : Specify the name of the Parent Object (eg. Hypervisor Server)
173 Value 3, Child Object Name : Specify the name of the Child Object (eg. VM)
174
175 Examples:
176 CONTAINERLINK;ESX_Host1;VM_1
177 Adds VM_1 as a member of the Container ESX_Host1
178
179 * Object Tags:
180
181 Syntax: OBJECTTAG
182 Value 1, OBJECTTAG
183 Value 2, Object Name : Specify the name of the Object to add the tag to(eg. Server)
184 Value 3, Tag Name : Specify the name of the Tag (eg. VM)
185
186 Examples:
187 OBJECTTAG;Server1;Tag1
188 Adds the tag called Tag1 to server object called Server1
189
190 * UPDATEIP
191 Syntax: UPDATEIP
192 Value 1: UPDATEIP
193 Value 2: IP Address
194 Value 3: Name
195 Value 4: Reseverd: yes or no
196 Value 5: Comment
197
198 Examples:
199 UPDATEIP;192.168.1.2;Test Address;no;Testing only
200 Updates IP 192.168.1.2 with Name Test, Reserved no and Comment "Testing only"
201
202
203 */
204
205 function plugin_csvimport_info()
206 {
207 return array
208 (
209 'name' => 'csvimport',
210 'longname' => 'CSV Import tool',
211 'version' => '2.0',
212 'home_url' => 'https://github.com/RackTables/racktables-contribs'
213 );
214 }
215
216 function plugin_csvimport_init()
217 {
218 global $page, $tab;
219
220 // Build Navigation
221 $page['import']['title'] = 'Import CSV data';
222 $page['import']['parent'] = 'config';
223 $tab['import']['default'] = 'Import';
224 registerTabHandler ('import', 'default', 'import_csv_data');
225 registerOpHandler ('import', 'default', 'importData', 'importData');
226
227 // Work in progress
228 //$tab['import']['delete'] = 'Delete';
229 //registerTabHandler ('import', 'delete', 'delete_csv_data');
230 //registerOpHandler ('import', 'delete', 'importData', 'deleteData');
231 }
232
233 function plugin_csvimport_install()
234 {
235 return TRUE;
236 }
237
238 function plugin_csvimport_uninstall()
239 {
240 return TRUE;
241 }
242
243 function plugin_csvimport_upgrade ()
244 {
245 return TRUE;
246 }
247
248 // tabhandler
249 function import_csv_data ()
250 {
251 // Used for uploading a csv file, or manually pasting csv data
252 startPortlet ('Import Racktables data');
253 printOpFormIntro ('importData', array (), TRUE);
254 echo "<table border=0 cellspacing=0 cellpadding='5' align='center'>";
255 echo "<tr><td class=tdleft><label>File: <input type='file' size='10' name='file' tabindex=100></label></td><td class=tdcenter>";
256 printImageHREF ('CREATE', 'Import file', TRUE, 102);
257 echo '</td></tr>';
258 echo '<tr><td>Manual input field</td></tr>';
259 echo '<tr><td valign=top colspan=2><textarea tabindex=101 name=csv_text rows=10 cols=80></textarea></td>';
260 echo '<td rowspan=2>';
261 echo '</td></tr>';
262 echo "</table></form><br>";
263 finishPortlet();
264 }
265
266 // tabhandler
267 function delete_csv_data ()
268 {
269 // Used for uploading a csv file, or manually pasting csv data
270 startPortlet ('Delete Racktables data');
271 printOpFormIntro ('importData', array (), TRUE);
272 echo "<table border=0 cellspacing=0 cellpadding='5' align='center'>";
273 echo "<tr><td class=tdleft><label>File: <input type='file' size='10' name='file' tabindex=100></label></td><td class=tdcenter>";
274 printImageHREF ('CREATE', 'Import file', TRUE, 102);
275 echo '</td></tr>';
276 echo '<tr><td>Manual input field</td></tr>';
277 echo '<tr><td valign=top colspan=2><textarea tabindex=101 name=csv_text rows=10 cols=80></textarea></td>';
278 echo '<td rowspan=2>';
279 echo '</td></tr>';
280 echo "</table></form><br>";
281 finishPortlet();
282 }
283
284 function deleteData()
285 {
286 setFuncMessages (__FUNCTION__, array ('OK' => 0, 'ERR1' => 207));
287 assertStringArg ('csv_text', TRUE);
288 $row = 1;
289
290 // if the manual input is empty, load the selected file
291 if (strlen(($_REQUEST['csv_text'])) == 0)
292 {
293 if ($_FILES['file']['error'])
294 return showFuncMessage (__FUNCTION__, 'ERR1', array ($_FILES['file']['error']));
295
296 // manage files from different OSes
297 ini_set("auto_detect_line_endings", TRUE);
298
299 if (($handle = fopen($_FILES['file']['tmp_name'], "r")) !== FALSE)
300 {
301 showNotice ("Deleting from ".$_FILES['file']['name']);
302 while (($csvdata = fgetcsv($handle, 1000, ";")) !== FALSE)
303 {
304 $result = usePreparedSelectBlade ("SELECT Object.id FROM Object WHERE Object.name='".$csvdata[0]."';");
305 $object = $result->fetch (PDO::FETCH_ASSOC);
306 if (!$object)
307 showError ("Line ".$row.": Object ".$csvdata[0]. " not found");
308 else
309 {
310 commitDeleteObject ($object['id']);
311 showSuccess ("Line $row: Object ".$csvdata[0]. " deleted");
312 }
313 $row++;
314 }
315 fclose($handle);
316 }
317 }
318 else
319 {
320 $data = explode("\n",$_REQUEST['csv_text']);
321 showNotice ("Deleting from manual input field");
322 foreach ($data as $dataitem)
323 {
324 $csvdata = str_getcsv($dataitem,";");
325 addServerObject($csvdata,$row);
326 $row++;
327 }
328 }
329
330 return showFuncMessage (__FUNCTION__, 'OK', array (htmlspecialchars ("Deleting finished.")));
331 }
332
333 function importData()
334 {
335 setFuncMessages (__FUNCTION__, array ('OK' => 0, 'ERR1' => 207));
336 assertStringArg ('csv_text', TRUE);
337 $row_number = 1;
338 // if the manual input is empty, load the selected file
339 if (strlen(($_REQUEST['csv_text'])) == 0)
340 {
341 ini_set("auto_detect_line_endings", TRUE);
342
343 if (($handle = fopen($_FILES['file']['tmp_name'], "r")) !== FALSE)
344 {
345 showNotice ("Importing ".$_FILES['file']['name']);
346 while (($csvdata = fgetcsv($handle, 1000, ";")) !== FALSE)
347 {
348 $csvdata[0] = trim($csvdata[0]);
349 if ($csvdata[0] == "OBJECT") addObject($csvdata,$row_number);
350 if ($csvdata[0] == "RACK") addRackImport($csvdata,$row_number);
351 if ($csvdata[0] == "RACKASSIGNMENT") addRackAssignment($csvdata,$row_number);
352 if ($csvdata[0] == "VLAN") addVLAN($csvdata,$row_number);
353 if ($csvdata[0] == "CABLELINK") addCableLink($csvdata,$row_number);
354 if ($csvdata[0] == "IP") addIP($csvdata,$row_number);
355 if ($csvdata[0] == "OBJECTIP") addObjectIP($csvdata,$row_number);
356 if ($csvdata[0] == "OBJECTATTRIBUTE") setObjectAttributes($csvdata,$row_number);
357 if ($csvdata[0] == "CONTAINERLINK") addContainerLink($csvdata,$row_number);
358 if ($csvdata[0] == "OBJECTTAG") addObjectTag($csvdata,$row_number);
359 if ($csvdata[0] == "UPDATEIP") updateIP($csvdata,$row_number);
360 $row_number++;
361 }
362 fclose($handle);
363 }
364 else
365 {
366 return showFuncMessage (__FUNCTION__, 'ERR1', array ($_FILES['file']['error']));
367 }
368 }
369 else
370 {
371 $data = explode("\n",$_REQUEST['csv_text']);
372 showNotice ("Importing from manual input field");
373 foreach ($data as $dataitem)
374 {
375 $csvdata = str_getcsv($dataitem,";");
376 $csvdata[0] = trim($csvdata[0]);
377 if ($csvdata[0] == "OBJECT") addObject($csvdata,$row_number);
378 if ($csvdata[0] == "RACK") addRackImport($csvdata,$row_number);
379 if ($csvdata[0] == "RACKASSIGNMENT") addRackAssignment($csvdata,$row_number);
380 if ($csvdata[0] == "VLAN") addVLAN($csvdata,$row_number);
381 if ($csvdata[0] == "CABLELINK") addCableLink($csvdata,$row_number);
382 if ($csvdata[0] == "IP") addIP($csvdata,$row_number);
383 if ($csvdata[0] == "OBJECTIP") addObjectIP($csvdata,$row_number);
384 if ($csvdata[0] == "OBJECTATTRIBUTE") setObjectAttributes($csvdata,$row_number);
385 if ($csvdata[0] == "CONTAINERLINK") addContainerLink($csvdata,$row_number);
386 if ($csvdata[0] == "OBJECTTAG") addObjectTag($csvdata,$row_number);
387 if ($csvdata[0] == "UPDATEIP") updateIP($csvdata,$row_number);
388 $row_number++;
389 }
390 }
391 return showFuncMessage (__FUNCTION__, 'OK', array (htmlspecialchars ("Import finished.")));
392 }
393
394 // This function adds a object to racktables and report appropriate results in the GUI
395 function addObject($csvdata,$row_number)
396 {
397 $object_type = trim ($csvdata[1]);
398 $object_name = trim ($csvdata[2]);
399 $object_label = trim ($csvdata[3]);
400 $object_assettag = trim ($csvdata[4]);
401 $ifName = explode(',',$csvdata[5]);
402 $ifType = explode(',',$csvdata[6]);
403
404 // Check Objecttype
405 if ($object_type == "SERVER") $object_type = 4;
406 if ($object_type == "PATCHPANEL") $object_type = 9;
407 if ($object_type == "SWITCH") $object_type = 8;
408 if ($object_type == "VM") $object_type = 1504;
409 if (is_numeric($object_type))
410 {
411 $result = usePreparedSelectBlade ("SELECT Dictionary.dict_value FROM Dictionary WHERE Dictionary.dict_key=".$object_type.";");
412 $db_object_type = $result->fetch (PDO::FETCH_ASSOC);
413 if ($db_object_type)
414 $object_type_name = $db_object_type['dict_value'];
415 else
416 {
417 showError("line $row_number: Object type ".$object_type. " does not exist. Import FAILED.");
418 return FALSE;
419 }
420 }
421 else
422 {
423 showError("line $row_number: Object type ".$object_type. " does not exist. Import FAILED.");
424 return FALSE;
425 }
426
427 if (strlen($object_name) > 0)
428 {
429 try
430 {
431 $object_id = commitAddObject ( $object_name, $object_label, $object_type, $object_assettag, array());
432 }
433 catch (Exception $e)
434 {
435 showError("line $row_number: Import ". $object_type_name. " Object ".$object_name. " FAILED; object already exists");
436 return FALSE;
437 }
438 }
439
440 // When available, import the port information
441
442 if ((count($ifName) > 0) & (count($ifType > 0)) & (count($ifName) == count($ifType)) )
443 {
444 // temporary disable autocreation of ports
445 $tempAUTOPORTS_CONFIG = getConfigVar ('AUTOPORTS_CONFIG');
446 setConfigVar ('AUTOPORTS_CONFIG',"");
447
448 for ($i=0 ; $i < count($ifName); $i++ )
449 {
450 if (strlen($ifName[$i]) > 0)
451 {
452 /*to do
453 Add Check for port compatibility, specified itType should be linked it iif_type 1 ('hardwired')
454 Else an foreign key error is thrown
455 */
456
457 $prefix = "";
458 $suffix = "";
459 $pattern = "!(?<=[[])[^]]+(?=[]])!";
460 preg_match($pattern,$ifName[$i],$match);
461
462 if ((count($match) > 0) & (strpos($match[0],'-') !== FALSE))
463 {
464 $prefix = substr($ifName[$i],0, strpos($ifName[$i],'['));
465 $suffix = substr($ifName[$i],strpos($ifName[$i],']')+1, strlen($ifName[$i])-1);
466 $portlist = explode('-',$match[0]);
467 if ((is_numeric($portlist[0])) & (is_numeric($portlist[1])) & ($portlist[0] < $portlist[1]))
468 {
469 for ($p = $portlist[0]; $p <= $portlist[1]; $p++)
470 $new_port_id = commitAddPort ( $object_id, $prefix.$p.$suffix, trim ($ifType[$i]), "", "" );
471 }
472 }
473 else
474 $new_port_id = commitAddPort ( $object_id, trim ($ifName[$i]), trim ($ifType[$i]), "", "" );
475 }
476 }
477
478 setConfigVar ('AUTOPORTS_CONFIG',$tempAUTOPORTS_CONFIG);
479 }
480 else
481 {
482 showNotice("No valid Port information found, skipping port import.");
483 }
484 showSuccess("line $row_number: Import ". $object_type_name. " Object ".$object_name. " successful; object_id=".$object_id);
485 }
486
487 function addRackImport($csvdata,$row_number)
488 {
489 $location = trim($csvdata[1]);
490 $location_child = trim($csvdata[2]);
491 $rackrow = trim($csvdata[3]);
492 $rack = trim($csvdata[4]);
493 if (!isset($csvdata[5]))
494 $rack_height = 46;
495 else
496 $rack_height = $csvdata[5];
497
498 // Handle Location entry
499 if (strlen($location ) > 0)
500 {
501 $result = usePreparedSelectBlade ("SELECT Object.id, Object.objtype_id FROM Object WHERE Object.name='".$location."';");
502 $db_location = $result->fetch (PDO::FETCH_ASSOC);
503 // Object already exists
504 if ($db_location)
505 {
506 $location_id = $db_location['id'];
507 // Object already exists but is not a Location (objecttype 1562) cannot continue
508 if ($db_location['objtype_id'] != 1562)
509 {
510 showError("Line $row_number: Location " . $location . " already exists as another Objecttype, Import FAILED.");
511 return FALSE;
512 }
513 }
514 // Object does not exist, create new location
515 else
516 {
517 $location_id = commitAddObject ($location, "", 1562, "", array());
518 showSuccess ("Line $row_number: Location ".$location. " imported; object_id=".$location_id);
519 }
520 }
521
522 //Handle Child location entry
523 if (strlen($location_child) > 0)
524 {
525 $location_child_id = 0;
526 $result = usePreparedSelectBlade ("SELECT o.id, o.objtype_id, o.name, e.parent_entity_id FROM Object o LEFT JOIN EntityLink e ON e.child_entity_id=o.id WHERE name ='".$location_child."';");
527 $db_location_child = $result->fetch (PDO::FETCH_ASSOC);
528
529 if ($db_location_child) { // Object already exists
530 $location_child_id = $db_location_child['id'];
531
532 if ($db_location_child['objtype_id'] != 1562) { // Object already exists but is not a Location (objecttype 1562) cannot continue
533 showError("Line $row_number: Location Child " . $location_child . " already exists as another Objecttype, Import FAILED.");
534 return FALSE;
535 }
536 if ($db_location_child['parent_entity_id'] != $location_id) { // The child Location id doesnt not match with the parent location ID
537 showError("Line $row_number: Location Child " . $location_child . " mismatch with parent location_id, Import FAILED.");
538 return FALSE;
539 }
540 }
541 else { // Location child does not exist, create new object and link to parent location
542 $location_child_id = commitAddObject ($location_child, "", 1562, "", array());
543 commitLinkEntities ('location', $location_id , 'location', $location_child_id );
544 showSuccess ("Line $row_number: Child Location ".$location_child. " imported; object_id=".$location_child_id);
545 }
546 }
547
548 //Handle Row entry
549 if (strlen($rackrow) > 0)
550 {
551 $result = usePreparedSelectBlade ("SELECT o.id, o.objtype_id, o.name, e.parent_entity_id FROM Object o LEFT JOIN EntityLink e ON e.child_entity_id=o.id WHERE name ='".$rackrow."';");
552 $db_rackrow = $result->fetch (PDO::FETCH_ASSOC);
553 // Object already exists
554 if ($db_rackrow)
555 {
556 $rackrow_id = $db_rackrow['id'];
557 // Object already exists but is not a Row (objecttype 1561) cannot continue
558 if ($db_rackrow['objtype_id'] != 1561)
559 {
560 showError("Line $row_number: Row " . $rackrow. $db_rackrow['objtype_id'] . " already exists as another Objecttype, Import FAILED.");
561 return FALSE;
562 }
563 // The Row doesnt not match with the parent or child location ID
564 if (($db_rackrow['parent_entity_id'] != $location_id) & ($db_rackrow['parent_entity_id'] != $location_child_id))
565 {
566 showError("Line $row_number: Row " . $rackrow . " mismatch with parent location_id, Import FAILED.". $db_rackrow['parent_entity_id']. " , " . $location_id . " , " . $location_child_id);
567 return FALSE;
568 }
569 }
570 // Row does not exist, create new object and link to parent location
571 else
572 {
573 $rackrow_id = commitAddObject ($rackrow, "", 1561, "", array());
574 if ( $location_child_id == 0)
575 commitLinkEntities ('location', $location_id , 'row', $rackrow_id );
576 else
577 commitLinkEntities ('location', $location_child_id , 'row', $rackrow_id );
578 showSuccess ("Line $row_number: Row ".$rackrow. " imported; object_id=".$rackrow_id);
579 }
580 }
581
582 //Handle Rack entry
583 if (strlen($rack) > 0)
584 {
585 $result = usePreparedSelectBlade ("SELECT Object.id, Object.objtype_id FROM Object WHERE Object.name='".$rack."';");
586 $db_rack = $result->fetch (PDO::FETCH_ASSOC);
587
588 // Rack Object already exists
589 if ($db_rack)
590 {
591 $rack_id = $db_rack['id'];
592 // Object already exists but is not a Location (objecttype 1562) cannot continue
593 if ($db_rack['objtype_id'] != 1560)
594 {
595 showError("Line $row_number: Rack " . $rack . " already exists as another Objecttype, Import FAILED.");
596 return FALSE;
597 }
598 }
599 // Rack Object does not exist, create new rack
600 else
601 {
602 $rack_id = commitAddObject ($rack, "", 1560, "", array()); // Object type 1560 = rack
603 commitLinkEntities ('row', $rackrow_id , 'rack', $rack_id );
604 commitUpdateAttrValue ($rack_id, 27, $rack_height); // attribute type 27 = height
605
606 // The new rack(s) should be placed on the bottom of the list, sort-wise
607 $rowInfo = getRowInfo($rackrow_id);
608 $sort_order = $rowInfo['count']+1;
609 commitUpdateAttrValue ($rack_id, 29, $sort_order);
610
611 showSuccess ("Line $row_number: Rack ".$rack. " imported; object_id=".$rack_id);
612 }
613 }
614 }
615
616 // This function adds Rack assignment info for an object
617 function addRackAssignment($csvdata,$row_number)
618 {
619
620 $object = trim ($csvdata[1]);
621 $rack = trim ($csvdata[2]);
622 $rackUnits = explode(',',$csvdata[3]);
623 $fib = explode(',',$csvdata[4]);
624
625 if (strlen($object ) > 0)
626 {
627 $result = usePreparedSelectBlade ("SELECT Object.id, Object.objtype_id FROM Object WHERE Object.name='".$object."';");
628 $db_object = $result->fetch (PDO::FETCH_ASSOC);
629
630 $result = usePreparedSelectBlade ("SELECT Object.id, Object.objtype_id FROM Object WHERE Object.name='".$rack."';");
631 $db_rack = $result->fetch (PDO::FETCH_ASSOC);
632 // Go ahead when Rack and object exists
633 if (($db_object) & ($db_rack))
634 {
635 for ($i=0 ; $i < count($rackUnits); $i++ )
636 {
637 try
638 {
639 if($rackUnits[$i] == 0)
640 {
641 // Zero-U
642 commitLinkEntities ('rack', $db_rack['id'], 'object', $db_object['id']);
643 }
644 else
645 {
646 if (strpos($fib[$i],'f') !== FALSE)
647 usePreparedInsertBlade ('RackSpace', array ('rack_id' => $db_rack['id'], 'unit_no' => $rackUnits[$i], 'atom' => 'front', 'state' => 'T', 'object_id' => $db_object['id']));
648 if (strpos($fib[$i],'i') !== FALSE)
649 usePreparedInsertBlade ('RackSpace', array ('rack_id' => $db_rack['id'], 'unit_no' => $rackUnits[$i], 'atom' => 'interior', 'state' => 'T', 'object_id' => $db_object['id']));
650 if (strpos($fib[$i],'b') !== FALSE)
651 usePreparedInsertBlade ('RackSpace', array ('rack_id' => $db_rack['id'], 'unit_no' => $rackUnits[$i], 'atom' => 'rear', 'state' => 'T', 'object_id' => $db_object['id']));
652 }
653
654 usePreparedDeleteBlade ('RackThumbnail', array ('rack_id' => $db_rack['id'])); //Updates the thumbnail of the rack
655 }
656 catch(Exception $e)
657 {
658 showWarning("Line $row_number: \"$object\" \"$rack\" ".$fib[$i]." failure. $e");
659 continue;
660 }
661 }
662 showSuccess("line $row_number: Rack Assignment for ".$object. " successful");
663 }
664 else
665 {
666 showError("Line $row_number: Object " . $object . " or Rack " . $rack. " does not exist. Import FAILED.");
667 return FALSE;
668 }
669 }
670 }
671
672 function addCableLink($csvdata,$row_number)
673 {
674 $object_a = trim ($csvdata[1]);
675 $port_a = trim ($csvdata[2]);
676 $object_b = trim ($csvdata[3]);
677 $port_b = trim ($csvdata[4]);
678 $cable_id = trim ($csvdata[5]);
679
680 // Check if object_a and port_a exist, if not; stop and return false
681 $result = usePreparedSelectBlade ("SELECT Port.id, Object.name FROM Port, Object WHERE Port.object_id = Object.id AND Port.name='".$port_a."' AND Object.name='".$object_a."';");
682 $db_result_a = $result->fetch (PDO::FETCH_ASSOC);
683 if (!$db_result_a)
684 {
685 showError("line $row_number: Import CableLink ". $cable_id. " FAILED; The object-port combination ".$object_a." ".$port_a." does not exist.");
686 return FALSE;
687 }
688
689 // Check if object_a and port_a exist, if not; stop and return false
690 $result = usePreparedSelectBlade ("SELECT Port.id, Object.name FROM Port, Object WHERE Port.object_id = Object.id AND Port.name='".$port_b."' AND Object.name='".$object_b."';");
691 $db_result_b = $result->fetch (PDO::FETCH_ASSOC);
692 if (!$db_result_b)
693 {
694 showError("line $row_number: Import CableLink ". $cable_id. " FAILED; The object-port combination ".$object_b." ".$port_b." does not exist.");
695 return FALSE;
696 }
697
698 // Check if port types are compatible
699 // Prevent SQL LOCK TABLES errors
700 $port1 = getPortInfo($db_result_a['id']);
701 $port2 = getPortInfo($db_result_b['id']);
702
703 if (!arePortTypesCompatible($port1['oif_id'], $port2['oif_id']))
704 {
705 showError("line $row_number: Import CableLink $cable_id FAILED; The porttypes mismatch $object_a $port_a -> $object_b $port_b. ".$port1['oif_name']." != ".$port2['oif_name']);
706 return FALSE;
707 }
708
709 // Create Link
710 try
711 {
712 $linkresult = linkPorts ($db_result_a['id'], $db_result_b['id'], $cable_id);
713
714 // port already linked
715 if(!is_numeric($linkresult))
716 {
717 showError("line $row_number: Import CableLink ". $cable_id." FAILED. $object_a $port_a -> $object_b $port_b \"".$linkresult."\" Link exists?!");
718 return FALSE;
719 }
720 }
721 catch (Exception $e)
722 {
723 showError("line $row_number: Import CableLink ". $cable_id." FAILED. Possible porttype mismatch. Complete Exception data: ".$e);
724 return FALSE;
725 }
726 showSuccess ("Line $row_number: Import CableLink ".$cable_id. " imported.");
727 }
728
729 function addVLAN($csvdata,$row_number)
730 {
731 $vlan_domain = trim ($csvdata[1]);
732 $vlan_name = trim ($csvdata[2]);
733 $vlan_id = trim ($csvdata[3]);
734 $vlan_propagation = trim ($csvdata[4]);
735 if ($vlan_propagation != 'ondemand') $vlan_propagation = "compulsory";
736 $ip_ranges = explode(',',$csvdata[5]);
737
738 // Check if VLAN domain exists
739 $result = usePreparedSelectBlade ("SELECT id FROM VLANDomain WHERE description='". $vlan_domain . "';");
740 $db_result = $result->fetch (PDO::FETCH_ASSOC);
741
742 // If VLAN domain does not exists, create domain
743 if (!$db_result)
744 {
745 usePreparedInsertBlade ('VLANDomain', array ('description' => $vlan_domain));
746 $result = usePreparedSelectBlade ("SELECT id FROM VLANDomain WHERE description ='". $vlan_domain . "';");
747 $db_result = $result->fetch (PDO::FETCH_ASSOC);
748 showSuccess ("Line $row_number: VLAN Domain ".$vlan_domain. " imported; object_id=".$db_result['id']);
749 }
750 $domain_id = $db_result['id'];
751
752 $catched = FALSE;
753 // Create VLAN
754 try
755 {
756 usePreparedInsertBlade ("VLANDescription", array('domain_id' => $domain_id , 'vlan_id' => $vlan_id, 'vlan_type' => $vlan_propagation, 'vlan_descr' => $vlan_name));
757 }
758 catch (Exception $e)
759 {
760 showError("line $row_number: Import ". $vlan_name. " vlan_id ".$vlan_id. " FAILED; VLAN already exists");
761 $catched = TRUE;
762 }
763
764 if(!$catched)
765 showSuccess ("Line $row_number: VLAN ".$vlan_name. " imported; vlan_id=".$vlan_id);
766
767 // Try to attach VLANs to IP ranges
768 foreach ($ip_ranges as $ip_range)
769 {
770 try
771 {
772 $net = spotNetworkByIP (ip_parse($ip_range));
773 }
774 catch (Exception $e)
775 {
776 showError("line $row_number: Unable to find/parse IP network address ". $ip_range);
777 }
778 if (isset($net['id']))
779 {
780 try
781 {
782 if (strpos($ip_range,".")) commitSupplementVLANIPv4 ($domain_id."-".$vlan_id, $net['id']);
783 if (strpos($ip_range,":")) commitSupplementVLANIPv6 ($domain_id."-".$vlan_id, $net['id']);
784 showSuccess ("Line $row_number: VLAN ".$vlan_name. " attached to IP range ".$ip_range);
785 }
786 catch (Exception $e)
787 {
788 showWarning ("Line $row_number: VLAN ".$vlan_name. " unable to attach to range $ip_range. $e");
789 }
790 }
791 else
792 {
793 showError ("Line $row_number: VLAN ".$vlan_name. " unable to attach to range ".$ip_range);
794 }
795 }
796 }
797
798
799 function addIP($csvdata,$row_number)
800 {
801 $prefix = trim ($csvdata[1]);
802 $ip_name= trim ($csvdata[2]);
803 $is_connected = trim ($csvdata[3]);
804 $vlan_domain = trim ($csvdata[4]);
805 $vlan_id = trim ($csvdata[5]);
806 $vlan_ck = NULL;
807
808 // Check if vlan domain - vlan combination exists
809 if ((strlen($vlan_domain) > 0) & (strlen($vlan_id) > 0))
810 {
811 $result = usePreparedSelectBlade ("SELECT VLANDescription.domain_id, VLANDomain.description FROM VLANDescription, VLANDomain WHERE VLANDomain.id = VLANDescription.domain_id AND VLANDescription.vlan_id=".$vlan_id." AND VLANDomain.description ='".$vlan_domain."';");
812 $vlan_result = $result->fetch (PDO::FETCH_ASSOC);
813 if (!$vlan_result)
814 {
815 showError("line $row_number: Import IP ". $cable_id. " FAILED; The VLAN domain - VLAN combination ".$vlan_domain." - ".$vlan_id." does not exist.");
816 return FALSE;
817 }
818 else
819 {
820 $vlan_ck = $vlan_result['domain_id']."-".$vlan_id;
821 }
822 }
823
824 // Create IP range
825 try
826 {
827 if (strpos($prefix,".")) createIPv4Prefix($prefix, $ip_name, $is_connected, array(), $vlan_ck);
828 if (strpos($prefix,":")) createIPv6Prefix($prefix, $ip_name, $is_connected, array(), $vlan_ck);
829 }
830 catch (Exception $e)
831 {
832 showError("line $row_number: Import IP ". $prefix." FAILED. Complete Exception data: ".$e);
833 return FALSE;
834 }
835 showSuccess ("Line $row_number: Import IP ".$prefix. " imported. ".$vlan_ck);
836 }
837
838 function addObjectIP($csvdata,$row_number)
839 {
840 $objectName = trim ($csvdata[1]);
841 $ifName = trim ($csvdata[2]);
842 $ipAddress = trim ($csvdata[3]);
843 if (!isset($csvdata[4]))
844 $type = "router";
845 else
846 $type = trim (strtolower($csvdata[4]));
847
848 //Check if object exists, and return object_id
849 $result = usePreparedSelectBlade ("SELECT Object.id FROM Object WHERE Object.name='".$objectName."';");
850 $db_object = $result->fetch (PDO::FETCH_ASSOC);
851
852 //if object exists, create IP interface
853 if ($db_object)
854 {
855 try
856 {
857 bindIPToObject (ip_parse($ipAddress), $db_object['id'], $ifName, $type);
858 }
859 catch (Exception $e)
860 {
861 showError("line $row_number: IP interface ". $ifName. " import FAILED" . "Reason: ". $e);
862 return FALSE;
863 }
864 showSuccess ("Line $row_number: IP interface ".$ifName. " imported.");
865 }
866 else
867 {
868 showError("Line $row_number: IP interface, Object " .$objectName. " does not exist. Import FAILED.");
869 }
870 }
871
872 // This function sets attributes for an object
873 function setObjectAttributes($csvdata,$row_number)
874 {
875 $object = trim ($csvdata[1]);
876 $attr_id = trim ($csvdata[2]);
877 $attr_value = trim ($csvdata[3]);
878
879 if (strlen($object ) > 0)
880 {
881 $result = usePreparedSelectBlade ("SELECT id, name, label, asset_no, has_problems, comment FROM Object WHERE Object.name='".$object."';");
882 $db_object = $result->fetch (PDO::FETCH_ASSOC);
883
884 // Go ahead when object exists
885 if ($db_object)
886 {
887 if ($attr_id == "NAME") $db_object['name'] = $attr_value;
888 if ($attr_id == "LABEL") $db_object['label'] = $attr_value;
889 if ($attr_id == "HASPROBLEMS") $db_object['has_problems'] = $attr_value;
890 if ($attr_id == "ASSETTAG") $db_object['asset_no'] = $attr_value;
891 if ($attr_id == "COMMENT") $db_object['comment'] = $attr_value;
892
893 if (preg_match('/NAME|LABEL|HASPROBLEMS|ASSETTAG|COMMENT/',$attr_id))
894 {
895 commitUpdateObject ($db_object['id'],$db_object['name'],$db_object['label'],$db_object['has_problems'],$db_object['asset_no'],$db_object['comment']);
896 }
897 else
898 {
899 commitUpdateAttrValue ($db_object['id'], $attr_id, $attr_value);
900 }
901 showSuccess("line $row_number: attribute for ".$object. ": ".$attr_id." ".$attr_value." updated");
902 }
903 else
904 {
905 showError("line $row_number: attribute for ".$object. ": ".$attr_id." ".$attr_value." not updated. Import FAILED.");
906 }
907 }
908 }
909
910 function addContainerLink($csvdata,$row_number)
911 {
912 $parentObjectName = trim ($csvdata[1]);
913 $childObjectName = trim ($csvdata[2]);
914
915 if ((strlen($parentObjectName) > 0) & (strlen($childObjectName) > 0))
916 {
917 // Check if parent object exists and return object_id
918 $parentResult = usePreparedSelectBlade ("SELECT Object.id FROM Object WHERE Object.name='".$parentObjectName."';");
919 $parentDB_object = $parentResult->fetch (PDO::FETCH_ASSOC);
920
921 // Check if child object exists and return object_id
922 $childResult = usePreparedSelectBlade ("SELECT Object.id FROM Object WHERE Object.name='".$childObjectName."';");
923 $childDB_object = $childResult->fetch (PDO::FETCH_ASSOC);
924
925 // if both objects exist, create an EntityLink between them
926 if (($parentDB_object) & ($childDB_object))
927 {
928 $object_parent_id = $parentDB_object['id'];
929 $object_child_id = $childDB_object['id'];
930 commitLinkEntities ('object', $object_parent_id , 'object', $object_child_id );
931 showSuccess ("Line $row_number: Added ".$childObjectName. " to parent container ".$parentObjectName.".");
932 }
933 else
934 {
935 showError("Line $row_number: Unable to add ".$childObjectName. " to parent container ".$parentObjectName.". One of the objects does not exist.");
936 }
937 }
938 }
939
940 function addObjectTag($csvdata,$row_number)
941 {
942 $objectName = trim ($csvdata[1]);
943 $tagName = trim ($csvdata[2]);
944
945 if ((strlen($objectName) > 0) & (strlen($tagName) > 0))
946 {
947 // Check if object exists and return object_id
948 $objectResult = usePreparedSelectBlade ("SELECT Object.id FROM Object WHERE Object.name='".$objectName."';");
949 $db_Object = $objectResult->fetch (PDO::FETCH_ASSOC);
950
951 // Check if tag exists and return tag_id
952 $tagResult = usePreparedSelectBlade ("SELECT TagTree.id FROM TagTree WHERE TagTree.tag='".$tagName."';");
953 $db_Tag = $tagResult->fetch (PDO::FETCH_ASSOC);
954
955 // if both the object and the tag exist, create an entry in the TagStorage table
956 if (($db_Object) & ($db_Tag))
957 {
958 $object_id = $db_Object['id'];
959 $tag_id = $db_Tag['id'];
960 addTagForEntity ('object', $object_id, $tag_id );
961 showSuccess ("Line $row_number: Added tag ".$tagName. " to object ".$objectName.".");
962 }
963 else
964 {
965 showError("Line $row_number: Unable to add tag ".$tagName. " to object ".$objectName.". Either the object of the tag does not exist.");
966 }
967 }
968 }
969
970 function updateIP($csvdata,$row_number)
971 {
972 $ipaddress = trim ($csvdata[1]);
973 $name = trim ($csvdata[2]);
974 $reserved = trim ($csvdata[3]);
975 $comment = trim ($csvdata[4]);
976
977 if(isset($csvdata[5]))
978 $user = trim ($csvdata[5]);
979 else
980 $user = FALSE;
981
982 $ip_bin = ip_parse($ipaddress);
983
984 $netaddress = getIPAddressNetworkID($ip_bin);
985 if(empty($netaddress))
986 {
987 showError("line $row_number: FAILED. update IP $ipaddress does not exist!");
988 return FALSE;
989 }
990
991 $address = getIPAddress($ip_bin);
992 if($address['reserved'] == 'yes')
993 {
994 showError("line $row_number: FAILED. update IP $ipaddress already reserved!");
995 return FALSE;
996 }
997
998 try
999 {
1000 if($user)
1001 addIPLogEntry_User($ip_bin, "Import Source Username", $user);
1002
1003 updateAddress ($ip_bin, $name, $reserved, $comment);
1004 }
1005 catch (Exception $e)
1006 {
1007 showError("line $row_number: update IP $ipaddress FAILED" . "Reason: ". $e);
1008 return FALSE;
1009 }
1010
1011 showSuccess ("Line $row_number: IP $ipaddress updated.");
1012 }
1013
1014 function addIPLogEntry_User($ip_bin, $message, $username)
1015 {
1016
1017 switch (strlen ($ip_bin))
1018 {
1019 case 4:
1020 usePreparedExecuteBlade
1021 (
1022 "INSERT INTO IPv4Log (ip, date, user, message) VALUES (?, NOW(), ?, ?)",
1023 array (ip4_bin2db ($ip_bin), $username, $message)
1024 );
1025 break;
1026 case 16:
1027 usePreparedExecuteBlade
1028 (
1029 "INSERT INTO IPv6Log (ip, date, user, message) VALUES (?, NOW(), ?, ?)",
1030 array ($ip_bin, $username, $message)
1031 );
1032 break;
1033 default: throw new InvalidArgException ('ip_bin', $ip_bin, "Invalid binary IP");
1034 }
1035 }