demo: simplify demoreload.sh
[racktables-contribs] / autoupdate.php
1 <?php
2
3 $dblink = mysql_connect('localhost', 'rackuser', 'rackpw');
4 if (!$dblink) {
5 die('Could not connect: ' . mysql_error());
6 }
7
8 mysql_select_db("racktables",$dblink);
9
10 //Object Information Posted
11 if(isset($_POST['name']))
12 $post_object['name']=$_POST['name']; //char(255)
13 if(isset($_POST['label']))
14 $post_object['label']=$_POST['label']; //char(255)
15 if(isset($_POST['barcode']))
16 $post_object['barcode']=$_POST['barcode']; //char(16)
17 if(isset($_POST['asset_no']))
18 $post_object['asset_no']=$_POST['asset_no']; //char(64)
19 if(isset($_POST['has_problems']))
20 $post_object['has_problems']=$_POST['has_problems']; //enum('yes','no')
21 if(isset($_POST['comment']))
22 $post_object['comment']=$_POST['comment']; //text
23
24 //Attribute Map
25 $attribute_id['serial']=1;
26 $attribute_id['contact_person']=14;
27 $attribute_id['serial_2']=20;
28 $attribute_id['ram_mb']=17;
29 $attribute_id['cpu_mhz']=18;
30 $attribute_id['warranty']=22;
31 $attribute_id['po_number']=10001;
32 $attribute_id['mac']=10011;
33 $attribute_id['serial_baseboard']=10016;
34 $attribute_id['bios_release_date']=10018;
35 $attribute_id['bios_version']=10017;
36 $attribute_id['processor_manufacturer']=10022;
37 $attribute_id['processor_name']=10024;
38
39
40 $unique_name="serial_baseboard";
41
42
43 //Set Unique Attribute Values or exit
44 $attribute_unique_id=$attribute_id[$unique_name];
45 if(isset($_POST['name']) && isset($_POST[$unique_name])) {
46 $unique_value=$_POST[$unique_name];
47 $name=$_POST['name'];
48 }
49 else
50 exit("no unique identifier (mac) and/or name");
51
52 //Set Authenticate Values or exit
53 if(isset($_POST['username']) && isset($_POST['password'])) {
54 $username=$_POST['username'];
55 $password=$_POST['password'];
56 }
57 else
58 exit("no username and/or password");
59
60 //Object Information in Database
61
62 $object_data[]=NULL;
63 $object_id=NULL;
64 $object_name=NULL;
65 $master_object_id=NULL;
66 $master_object_name=NULL;
67
68 $object_asset=NULL;
69
70 function authenticate() {
71
72 global $username;
73 global $password;
74
75 $passwordhash=hash('sha1',"$password");
76 $result=mysql_query("select user_password_hash from UserAccount where user_name=\"$username\"");
77 $row=mysql_fetch_row($result);
78 if ($row[0]==$passwordhash)
79 return 1;
80 else
81 return 0;
82 }
83
84 function lookup() {
85
86 global $object_data; //object values from database.
87
88 global $name; //$_POST['name']
89
90 global $object_id; //$object_id is set after a successful object lookup
91 global $object_name; //used to lookup master object
92 global $master_object_id; //$master_object_id is set after a successfull master object lookup (ie, C6100 chassis)
93 global $master_object_name; //used for master object update where the name ties the object to the master object (ie, r5288 is tied to the master object name "r5285 r5286 r5287 r5288")
94 global $unique_value;
95 global $attribute_unique_id;
96
97 // select RackObject.id,RackObject.name from RackObject,AttributeValue where RackObject.id=AttributeValue.object_id AND AttributeValue.attr_id=10011 AND AttributeValue.string_value="00:26:6C:F2:56:D4"
98 // select RackObject.id,RackObject.name from RackObject where RackObject.name="r5288";
99
100 //if $unique_value posted
101 // lookup object by unique
102 // if id returned
103 // if posted name exists in another object
104 // exit
105 // ok to update
106 // else lookup by name
107 // if id returned
108 // lookup unique by id
109 // if unique NULL
110 // ok to update
111 // else
112 // unique different, cannot update
113 // if object found
114 // lookup master object
115
116
117 if (isset($unique_value)) {
118 $result_by_unique=mysql_query("select RackObject.id,RackObject.name,RackObject.label,RackObject.barcode,RackObject.objtype_id,RackObject.asset_no,RackObject.has_problems,RackObject.comment from RackObject,AttributeValue where RackObject.id=AttributeValue.object_id AND AttributeValue.attr_id=$attribute_unique_id AND AttributeValue.string_value=\"$unique_value\"");
119 $row_by_unique=mysql_fetch_assoc($result_by_unique); //***copied to $object_data[] global***
120 if (isset($row_by_unique['id'])) { //if object found with unique id
121 $result_by_name=mysql_query("select id from RackObject where name=\"$name\""); //lookup object by posted name
122 $row_by_name=mysql_fetch_assoc($result_by_name);
123 if (isset($row_by_name['id']) && $row_by_name['id']!=$row_by_unique['id']) //if there is another record with that name
124 exit("duplicate record with that name"); //exit
125 $object_id=$row_by_unique['id'];
126 $object_name=$row_by_unique['name'];
127 $object_data=$row_by_unique; //make a copy of the object associative array for update() to use later
128 echo "found id,name by mac lookup: $row_by_unique[id] | $row_by_unique[name]\n";
129 } else {
130 $result_by_name=mysql_query("select RackObject.id,RackObject.name,RackObject.label,RackObject.barcode,RackObject.objtype_id,RackObject.asset_no,RackObject.has_problems,RackObject.comment from RackObject where RackObject.name=\"$name\"");
131 $row_by_name=mysql_fetch_assoc($result_by_name);
132 if (isset($row_by_name['id'])) {
133 echo "found id,name by name lookup: $row_by_name[id] | $row_by_name[name]\n";
134 $result_by_unique=mysql_query("select RackObject.id,RackObject.name,AttributeValue.string_value from RackObject,AttributeValue where AttributeValue.attr_id=$attribute_unique_id AND RackObject.id=AttributeValue.object_id And RackObject.name=\"$name\"");
135 $row_by_unique=mysql_fetch_row($result_by_unique);
136 if (!isset($row_by_unique[0])) { //ok to update if unique identifier is missing from AttributeValue. ***no unique identifier in AttributeValue will return an empty set.***
137 $object_id=$row_by_name['id'];
138 $object_name=$row_by_name['name'];
139 $object_data=$row_by_name; //make a copy of the object associative array for update() to use later
140 echo "mac NULL. ok to update\n";
141 } else {
142 echo "cannot update. different mac\n";
143 }
144 }
145 }
146 }
147 //Look for Master Object id and name
148 if (isset($object_id)) {
149 $result_like_name=mysql_query("select id, name from RackObject where name like \"$object_name %\" OR name like \"% $object_name %\" OR name like \"% $object_name\"");
150 $row_like_name=mysql_fetch_assoc($result_like_name);
151 $master_object_id=$row_like_name['id'];
152 $master_object_name=$row_like_name['name'];
153 echo "found master object_id: $master_object_id";
154
155 }
156 }
157
158 function update_object($object_id) {
159 global $username;
160 global $object_data;
161 global $post_object;
162 global $attribute_id; //Attribute id map
163 $result_attributes_stored=mysql_query("select attr_id,string_value,uint_value,float_value from AttributeValue where object_id=$object_id");
164
165 while($row=mysql_fetch_row($result_attributes_stored)) {
166 if (isset($row[1]))
167 $attributes_stored["$row[0]"]=$row[1]; //build index of stored attribute values, merging types string, uint, and float
168 elseif (isset($row[2]))
169 $attributes_stored["$row[0]"]=$row[2];
170 elseif (isset($row[3]))
171 $attributes_stored["$row[0]"]=$row[3];
172 }
173
174 //+---------+-------------------+------------+-------------+
175 //| attr_id | string_value | uint_value | float_value |
176 //+---------+-------------------+------------+-------------+
177 //| 1 | 125PNM1 | NULL | NULL |
178 //| 2 | NULL | 0 | NULL |
179 //| 4 | NULL | 0 | NULL |
180 //| 14 | Mark Brice | NULL | NULL |
181 //| 17 | NULL | 98994 | NULL |
182 //| 18 | NULL | 2930 | NULL |
183 //| 22 | 2/2/13 | NULL | NULL |
184 //| 10011 | 00:26:6C:F2:56:D4 | NULL | NULL |
185 //+---------+-------------------+------------+-------------+
186
187
188 $result_attribute_type=mysql_query("select Attribute.id,Attribute.type from Attribute,AttributeMap where AttributeMap.objtype_id=4 AND AttributeMap.attr_id=Attribute.id AND (Attribute.type=\"string\" OR Attribute.type=\"uint\")");
189 while($row=mysql_fetch_row($result_attribute_type))
190 $attribute_type["$row[0]"]=$row[1]; //build *attribute type* lookup map. used when we update AttributeValue
191 //+-------+--------+
192 //| id | type |
193 //+-------+--------+
194 //| 1 | string |
195 //| 14 | string |
196 //| 17 | uint |
197 //| 18 | uint |
198 //| 20 | string |
199 //| 21 | string |
200 //| 22 | string |
201 //| 24 | string |
202 //| 25 | string |
203 //| 10001 | string |
204 //| 10011 | string |
205 //+-------+--------+
206
207 //Update RackObject Attributes
208 //
209 // for each possible attribute
210 // if attribute was posted
211 // if attribute exists in database
212 // if posted attribute is different from attribute in database
213 // if attribute type == string
214 // update string_value of AttributeValue
215 // elseif attribute type == uint
216 // update uint_value of AttributeValue
217 // elseif attribute type == float
218 // update float_value of AttributeValue
219 // else
220 // if posted attribute is different from attribute in database
221 // if attribute type == string
222 // insert string_value of AttributeValue
223 // elseif attribute type == uint
224 // insert uint_value of AttributeValue
225 // elseif attribute type == float
226 // insert float_value of AttributeValue
227 //
228 //
229 //
230 foreach($attribute_id as $key => $value) { // go through each possible attribute. example: $key:warranty, $value:22
231 if (isset($_POST[$key])) { // if there is a post value for the attribute
232 $posted_value=$_POST[$key];
233 $attr_id=$value; //set the attribute id for lookup
234 if (isset($attributes_stored[$value])) { // if the valid attribute exists in AttributeValue ie 22 : 12/12/12
235 if ($_POST[$key]!=$attributes_stored[$value]) { // if the POSTED attribute is not equal to the value stored, update.
236 echo "value submitted ($_POST[$key]) differs from value stored ($attributes_stored[$value])\n";
237 //UPDATE ************ mysql update
238 if ($attribute_type[$attr_id] == "string")
239 mysql_query("update AttributeValue set string_value=\"$posted_value\" where object_id=$object_id and attr_id=\"$attr_id\"");
240 else if ($attribute_type[$attr_id] == "uint")
241 mysql_query("update AttributeValue set uint_value=$posted_value where object_id=$object_id and attr_id=\"$attr_id\"");
242 else if ($attribute_type[$attr_id] == "float")
243 mysql_query("update AttributeValue set float_value=$posted_value where object_id=$object_id and attr_id=\"$attr_id\"");
244
245 }
246 }
247 else { // valid attribute is not stored, we'll need to add a row to AttributeValue
248 echo "value submitted ($_POST[$key]) differs from value stored (NULL)\n";
249 //INSERT************ mysql insert
250 if ($attribute_type[$attr_id] == "string")
251 mysql_query("insert into AttributeValue (object_id,attr_id,string_value) values($object_id,$attr_id,\"$posted_value\")");
252 else if ($attribute_type[$attr_id] == "uint")
253 mysql_query("insert into AttributeValue (object_id,attr_id,uint_value) values($object_id,$attr_id,$posted_value)");
254 else if ($attribute_type[$attr_id] == "float")
255 mysql_query("insert into AttributeValue (object_id,attr_id,float_value) values($object_id,$attr_id,$posted_value)");
256 }
257 }
258 }
259
260 //Update RackObject and RackObjectHistory
261
262 //for each $object_data
263 // if $insert_labels string does not exist
264 // add first label to $insert_labels string
265 // add first values to $insert_values string
266 // elseif database value exists && post value exists
267 // add labels to $insert_labels string
268 // if post value exists
269 // if database value differs from post value
270 // add post value to $insert_values string
271 // else
272 // add database value to $insert_values string
273 // else
274 // add database value to $insert_values string
275 // if $update_values string does not exist && post value exists
276 // if post value differs from database value
277 // add first value to $update_values
278 // elseif posted value exists
279 // if posted value differs from database value
280 // add posted value to $update_values string
281 //
282 //RackObject update: update RackObject set label="testing1" where id=1164
283 //RackObjectHistory insert: insert into RackObjectHistory (id,name,label,objtype_id,has_problems,comment,user_name) values(1164,"r5288","testing1","4","no","","autoupdate")
284
285
286 foreach($object_data as $key => $value) {
287 if (!isset($insert_labels)) {
288 $insert_labels=$key; //first run will see object id, which is never posted
289 $insert_values=$value;
290 }
291 elseif (isset($value) || isset($post_object[$key])) {
292 $insert_labels=$insert_labels.",".$key;
293 if (isset($post_object[$key])) {
294 if ($value!=$post_object[$key])
295 $insert_values=$insert_values.",\"$post_object[$key]\"";
296 else
297 $insert_values=$insert_values.",\"$value\"";
298 }
299 else
300 $insert_values=$insert_values.",\"$value\"";
301 }
302 if (!isset($update_values) && isset($post_object[$key])) {
303 if ($post_object[$key]!=$value)
304 $update_values="$key=\"$post_object[$key]\"";
305 }
306 elseif (isset($post_object[$key])) {
307 if ($post_object[$key]!=$value)
308 $update_values=$update_values.", $key=\"$post_object[$key]\"";
309 }
310 }
311 if (isset($update_values)) {
312 $RackObject_statement="update RackObject set ".$update_values." where id=$object_id";
313 $RackObjectHistory_statement="insert into RackObjectHistory ($insert_labels,user_name) values($insert_values,\"$username\")\n";
314 mysql_query("$RackObject_statement");
315 mysql_query("$RackObjectHistory_statement");
316
317 echo "RackObject statement: $RackObject_statement\n";
318 echo "RackObjectHistory statement: $RackObjectHistory_statement\n";
319 }
320 }
321
322 function update_master_object($master_object_id) {
323 global $master_object_name;
324 global $object_name;
325 global $name;
326 if ($name!=$object_name) {
327 $master_object_name_new=preg_replace("/$object_name/",$name,$master_object_name);
328 //UPDATE ************ mysql update
329 $master_object_update_query="update RackObject set name=\"".$master_object_name_new."\" where id=".$master_object_id;
330 mysql_query("$master_object_update_query");
331 }
332 }
333
334
335 lookup();
336
337 if (authenticate()==0)
338 exit("incorrect username and/or password");
339
340 if ($object_id) //if lookup() set $object_id, there was a successfull object lookup
341 update_object($object_id);
342
343 if ($master_object_id) //if lookup() set $master_object_id, there was a successfull master object lookup
344 update_master_object($master_object_id);
345
346 mysql_close();