r4381 an autoupdate script by anonymous contributor
authorDenis Ovsienko <infrastation@yandex.ru>
Wed, 23 Mar 2011 16:01:59 +0000 (16:01 +0000)
committerDenis Ovsienko <infrastation@yandex.ru>
Mon, 26 Mar 2012 10:12:24 +0000 (14:12 +0400)
autoupdate.php [new file with mode: 0644]

diff --git a/autoupdate.php b/autoupdate.php
new file mode 100644 (file)
index 0000000..f57e5e8
--- /dev/null
@@ -0,0 +1,348 @@
+<?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();
+
+?>