--- /dev/null
+<?php
+
+$dblink = mysql_connect('localhost', 'rackuser', 'rackpw');
+if (!$dblink) {
+ die('Could not connect: ' . mysql_error());
+}
+
+mysql_select_db("racktables",$dblink);
+
+//Object Information Posted
+if(isset($_POST['name']))
+ $post_object['name']=$_POST['name']; //char(255)
+if(isset($_POST['label']))
+ $post_object['label']=$_POST['label']; //char(255)
+if(isset($_POST['barcode']))
+ $post_object['barcode']=$_POST['barcode']; //char(16)
+if(isset($_POST['asset_no']))
+ $post_object['asset_no']=$_POST['asset_no']; //char(64)
+if(isset($_POST['has_problems']))
+ $post_object['has_problems']=$_POST['has_problems']; //enum('yes','no')
+if(isset($_POST['comment']))
+ $post_object['comment']=$_POST['comment']; //text
+
+//Attribute Map
+$attribute_id['serial']=1;
+$attribute_id['contact_person']=14;
+$attribute_id['serial_2']=20;
+$attribute_id['ram_mb']=17;
+$attribute_id['cpu_mhz']=18;
+$attribute_id['warranty']=22;
+$attribute_id['po_number']=10001;
+$attribute_id['mac']=10011;
+$attribute_id['serial_baseboard']=10016;
+$attribute_id['bios_release_date']=10018;
+$attribute_id['bios_version']=10017;
+$attribute_id['processor_manufacturer']=10022;
+$attribute_id['processor_name']=10024;
+
+
+$unique_name="serial_baseboard";
+
+
+//Set Unique Attribute Values or exit
+$attribute_unique_id=$attribute_id[$unique_name];
+if(isset($_POST['name']) && isset($_POST[$unique_name])) {
+ $unique_value=$_POST[$unique_name];
+ $name=$_POST['name'];
+}
+else
+ exit("no unique identifier (mac) and/or name");
+
+//Set Authenticate Values or exit
+if(isset($_POST['username']) && isset($_POST['password'])) {
+ $username=$_POST['username'];
+ $password=$_POST['password'];
+}
+else
+ exit("no username and/or password");
+
+//Object Information in Database
+
+$object_data[]=NULL;
+$object_id=NULL;
+$object_name=NULL;
+$master_object_id=NULL;
+$master_object_name=NULL;
+
+$object_asset=NULL;
+
+function authenticate() {
+
+ global $username;
+ global $password;
+
+ $passwordhash=hash('sha1',"$password");
+ $result=mysql_query("select user_password_hash from UserAccount where user_name=\"$username\"");
+ $row=mysql_fetch_row($result);
+ if ($row[0]==$passwordhash)
+ return 1;
+ else
+ return 0;
+}
+
+function lookup() {
+
+ global $object_data; //object values from database.
+
+ global $name; //$_POST['name']
+
+ global $object_id; //$object_id is set after a successful object lookup
+ global $object_name; //used to lookup master object
+ global $master_object_id; //$master_object_id is set after a successfull master object lookup (ie, C6100 chassis)
+ 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")
+ global $unique_value;
+ global $attribute_unique_id;
+
+// 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"
+// select RackObject.id,RackObject.name from RackObject where RackObject.name="r5288";
+
+//if $unique_value posted
+// lookup object by unique
+// if id returned
+// if posted name exists in another object
+// exit
+// ok to update
+// else lookup by name
+// if id returned
+// lookup unique by id
+// if unique NULL
+// ok to update
+// else
+// unique different, cannot update
+// if object found
+// lookup master object
+
+
+ if (isset($unique_value)) {
+ $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\"");
+ $row_by_unique=mysql_fetch_assoc($result_by_unique); //***copied to $object_data[] global***
+ if (isset($row_by_unique['id'])) { //if object found with unique id
+ $result_by_name=mysql_query("select id from RackObject where name=\"$name\""); //lookup object by posted name
+ $row_by_name=mysql_fetch_assoc($result_by_name);
+ if (isset($row_by_name['id']) && $row_by_name['id']!=$row_by_unique['id']) //if there is another record with that name
+ exit("duplicate record with that name"); //exit
+ $object_id=$row_by_unique['id'];
+ $object_name=$row_by_unique['name'];
+ $object_data=$row_by_unique; //make a copy of the object associative array for update() to use later
+ echo "found id,name by mac lookup: $row_by_unique[id] | $row_by_unique[name]\n";
+ } else {
+ $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\"");
+ $row_by_name=mysql_fetch_assoc($result_by_name);
+ if (isset($row_by_name['id'])) {
+ echo "found id,name by name lookup: $row_by_name[id] | $row_by_name[name]\n";
+ $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\"");
+ $row_by_unique=mysql_fetch_row($result_by_unique);
+ 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.***
+ $object_id=$row_by_name['id'];
+ $object_name=$row_by_name['name'];
+ $object_data=$row_by_name; //make a copy of the object associative array for update() to use later
+ echo "mac NULL. ok to update\n";
+ } else {
+ echo "cannot update. different mac\n";
+ }
+ }
+ }
+ }
+//Look for Master Object id and name
+ if (isset($object_id)) {
+ $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\"");
+ $row_like_name=mysql_fetch_assoc($result_like_name);
+ $master_object_id=$row_like_name['id'];
+ $master_object_name=$row_like_name['name'];
+ echo "found master object_id: $master_object_id";
+
+ }
+}
+
+function update_object($object_id) {
+ global $username;
+ global $object_data;
+ global $post_object;
+ global $attribute_id; //Attribute id map
+ $result_attributes_stored=mysql_query("select attr_id,string_value,uint_value,float_value from AttributeValue where object_id=$object_id");
+
+ while($row=mysql_fetch_row($result_attributes_stored)) {
+ if (isset($row[1]))
+ $attributes_stored["$row[0]"]=$row[1]; //build index of stored attribute values, merging types string, uint, and float
+ elseif (isset($row[2]))
+ $attributes_stored["$row[0]"]=$row[2];
+ elseif (isset($row[3]))
+ $attributes_stored["$row[0]"]=$row[3];
+ }
+
+//+---------+-------------------+------------+-------------+
+//| attr_id | string_value | uint_value | float_value |
+//+---------+-------------------+------------+-------------+
+//| 1 | 125PNM1 | NULL | NULL |
+//| 2 | NULL | 0 | NULL |
+//| 4 | NULL | 0 | NULL |
+//| 14 | Mark Brice | NULL | NULL |
+//| 17 | NULL | 98994 | NULL |
+//| 18 | NULL | 2930 | NULL |
+//| 22 | 2/2/13 | NULL | NULL |
+//| 10011 | 00:26:6C:F2:56:D4 | NULL | NULL |
+//+---------+-------------------+------------+-------------+
+
+
+ $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\")");
+ while($row=mysql_fetch_row($result_attribute_type))
+ $attribute_type["$row[0]"]=$row[1]; //build *attribute type* lookup map. used when we update AttributeValue
+ //+-------+--------+
+ //| id | type |
+ //+-------+--------+
+ //| 1 | string |
+ //| 14 | string |
+ //| 17 | uint |
+ //| 18 | uint |
+ //| 20 | string |
+ //| 21 | string |
+ //| 22 | string |
+ //| 24 | string |
+ //| 25 | string |
+ //| 10001 | string |
+ //| 10011 | string |
+ //+-------+--------+
+
+//Update RackObject Attributes
+//
+// for each possible attribute
+// if attribute was posted
+// if attribute exists in database
+// if posted attribute is different from attribute in database
+// if attribute type == string
+// update string_value of AttributeValue
+// elseif attribute type == uint
+// update uint_value of AttributeValue
+// elseif attribute type == float
+// update float_value of AttributeValue
+// else
+// if posted attribute is different from attribute in database
+// if attribute type == string
+// insert string_value of AttributeValue
+// elseif attribute type == uint
+// insert uint_value of AttributeValue
+// elseif attribute type == float
+// insert float_value of AttributeValue
+//
+//
+//
+ foreach($attribute_id as $key => $value) { // go through each possible attribute. example: $key:warranty, $value:22
+ if (isset($_POST[$key])) { // if there is a post value for the attribute
+ $posted_value=$_POST[$key];
+ $attr_id=$value; //set the attribute id for lookup
+ if (isset($attributes_stored[$value])) { // if the valid attribute exists in AttributeValue ie 22 : 12/12/12
+ if ($_POST[$key]!=$attributes_stored[$value]) { // if the POSTED attribute is not equal to the value stored, update.
+ echo "value submitted ($_POST[$key]) differs from value stored ($attributes_stored[$value])\n";
+ //UPDATE ************ mysql update
+ if ($attribute_type[$attr_id] == "string")
+ mysql_query("update AttributeValue set string_value=\"$posted_value\" where object_id=$object_id and attr_id=\"$attr_id\"");
+ else if ($attribute_type[$attr_id] == "uint")
+ mysql_query("update AttributeValue set uint_value=$posted_value where object_id=$object_id and attr_id=\"$attr_id\"");
+ else if ($attribute_type[$attr_id] == "float")
+ mysql_query("update AttributeValue set float_value=$posted_value where object_id=$object_id and attr_id=\"$attr_id\"");
+
+ }
+ }
+ else { // valid attribute is not stored, we'll need to add a row to AttributeValue
+ echo "value submitted ($_POST[$key]) differs from value stored (NULL)\n";
+ //INSERT************ mysql insert
+ if ($attribute_type[$attr_id] == "string")
+ mysql_query("insert into AttributeValue (object_id,attr_id,string_value) values($object_id,$attr_id,\"$posted_value\")");
+ else if ($attribute_type[$attr_id] == "uint")
+ mysql_query("insert into AttributeValue (object_id,attr_id,uint_value) values($object_id,$attr_id,$posted_value)");
+ else if ($attribute_type[$attr_id] == "float")
+ mysql_query("insert into AttributeValue (object_id,attr_id,float_value) values($object_id,$attr_id,$posted_value)");
+ }
+ }
+ }
+
+//Update RackObject and RackObjectHistory
+
+//for each $object_data
+// if $insert_labels string does not exist
+// add first label to $insert_labels string
+// add first values to $insert_values string
+// elseif database value exists && post value exists
+// add labels to $insert_labels string
+// if post value exists
+// if database value differs from post value
+// add post value to $insert_values string
+// else
+// add database value to $insert_values string
+// else
+// add database value to $insert_values string
+// if $update_values string does not exist && post value exists
+// if post value differs from database value
+// add first value to $update_values
+// elseif posted value exists
+// if posted value differs from database value
+// add posted value to $update_values string
+//
+//RackObject update: update RackObject set label="testing1" where id=1164
+//RackObjectHistory insert: insert into RackObjectHistory (id,name,label,objtype_id,has_problems,comment,user_name) values(1164,"r5288","testing1","4","no","","autoupdate")
+
+
+ foreach($object_data as $key => $value) {
+ if (!isset($insert_labels)) {
+ $insert_labels=$key; //first run will see object id, which is never posted
+ $insert_values=$value;
+ }
+ elseif (isset($value) || isset($post_object[$key])) {
+ $insert_labels=$insert_labels.",".$key;
+ if (isset($post_object[$key])) {
+ if ($value!=$post_object[$key])
+ $insert_values=$insert_values.",\"$post_object[$key]\"";
+ else
+ $insert_values=$insert_values.",\"$value\"";
+ }
+ else
+ $insert_values=$insert_values.",\"$value\"";
+ }
+ if (!isset($update_values) && isset($post_object[$key])) {
+ if ($post_object[$key]!=$value)
+ $update_values="$key=\"$post_object[$key]\"";
+ }
+ elseif (isset($post_object[$key])) {
+ if ($post_object[$key]!=$value)
+ $update_values=$update_values.", $key=\"$post_object[$key]\"";
+ }
+ }
+ if (isset($update_values)) {
+ $RackObject_statement="update RackObject set ".$update_values." where id=$object_id";
+ $RackObjectHistory_statement="insert into RackObjectHistory ($insert_labels,user_name) values($insert_values,\"$username\")\n";
+ mysql_query("$RackObject_statement");
+ mysql_query("$RackObjectHistory_statement");
+
+echo "RackObject statement: $RackObject_statement\n";
+echo "RackObjectHistory statement: $RackObjectHistory_statement\n";
+ }
+}
+
+function update_master_object($master_object_id) {
+ global $master_object_name;
+ global $object_name;
+ global $name;
+ if ($name!=$object_name) {
+ $master_object_name_new=preg_replace("/$object_name/",$name,$master_object_name);
+ //UPDATE ************ mysql update
+ $master_object_update_query="update RackObject set name=\"".$master_object_name_new."\" where id=".$master_object_id;
+ mysql_query("$master_object_update_query");
+ }
+}
+
+
+lookup();
+
+if (authenticate()==0)
+ exit("incorrect username and/or password");
+
+if ($object_id) //if lookup() set $object_id, there was a successfull object lookup
+ update_object($object_id);
+
+if ($master_object_id) //if lookup() set $master_object_id, there was a successfull master object lookup
+ update_master_object($master_object_id);
+
+mysql_close();
+
+?>