WebSVN – computer_asset_manager_v1 – Diff – /modules/attributes/functions.php

       $attribute = makeSafeSQLConstant( $attribute, 's', '' );
       if ( ! $attribute ) return 0;
       $return = getOneDBValue( "select attrib_id from attrib where name = $attribute" );
       if ( ! $return && $createIfNotFound ) {
          $return = queryDatabaseExtended( "insert into attrib( name, added_date, removed_date ) values ( $attribute, now(), null )" );
-         return $result['insert_id'] ? $result['insert_id'] : 0;
+         return $return['insert_id'] ? $return['insert_id'] : 0;
+      }
       return $return;
+   }
    /*
     */
    function parseTabDelimFile ( $contents, $createAttributeIfNotFound = false, $createDeviceIfNotFound = false, $default_device_id='', $default_site_id='', $default_client_id='' ) {
       $data = tabDelimToArray( $contents );
       // we'll put our SQL into an array, then dump it.
       $sql = array();
+      // and track any errors here
+      $errors = array();
       /*
       print "<pre>";
       var_dump( $createAttributeIfNotFound, $createDeviceIfNotFound, $default_device_id, $default_site_id, $default_client_id );
       print "</pre>";
       return $sql;
       // the following two arrays will store attributes and devices as we find them
       // we can then FIRST look them up here, in memory, and go to database only when we don't know them
       $attributesFromDatabase = array(); // uniquely store our attributes here
       $deviceFromDatabase = array();
       for ( $i = 0; $i < count( $data ); $i++ ) {  // go through each line and grab fields we need
          // get device_id
          if ( ! $data[$i]['device_id'] ) {
             if ( $data[$i]['device'] ) {
                if ( isset( $deviceFromDatabase[$data[$i]['device']] ) ) {
                   $data[$i]['device_id'] = $deviceFromDatabase[$data[$i]['device']];
+            }
             if ( ! $data[$i]['device_id'] ) {
                if ( $default_device_id ) {
                   $data[$i]['device_id'] = $default_device_id;
                } else {
-                  $sql[] = "/* Can not locate device in line $i */";
+                  $errors[] = "Can not locate device [" . $data[$i]['device'] . "] in line $i";
                   continue;
+               }
+            }
+         }
+               }
                if ( $data[$i]['attrib_id'] )
                   $attributesFromDatabase[$data[$i]['attribute']] = $data[$i]['attrib_id'];
+            }
             if ( ! $data[$i]['attrib_id'] ) {
-               $sql[] = "/* Can not locate attribute in line $i */";
+               $errors[] = 'Can not locate attribute [' . $data[$i]['attribute'] . "] in line $i";
                continue;
+            }
+         }
          if ( ! $data[$i]['value'] ) {
-            $sql[] = "/* No Value given for line $i, skipped */";
+            $errors[] = "No Value given for line $i, skipped";
             continue;
+         }
-         $value = makeSafeSQLConstant( $data[$i]['value'] );
+         $sql = array_merge( $sql, makeSQL( $data[$i] ) );
-         $attrib_id = makeSafeSQLConstant( $data[$i]['attrib_id'], 'i' );
-         $device_id = makeSafeSQLConstant( $data[$i]['device_id'],'i' );
-         // standard SQL that does an insert if the value doesn't already exist.
-         $sql[] =
-            "insert into attrib_device ( device_id, attrib_id,value, added_date )
-               select $device_id,$attrib_id, $value, now()
-               from dual
-               where
-                  not exists (
-                     select * from attrib_device join device using (device_id) join attrib using (attrib_id) where device_id = $device_id and attrib_id = $attrib_id
-                     );";
+      }
+      return array( 'errors' => $errors, 'sql' => $sql );
+   }
+   /*
+    * makeSQL
+    * creates one or more SQL statements to upload attribute/value into the database.
+    * if unique is true, will not do an insert. Instead, it will only update an existing
+    * row if device_id and attrib_id pair already match.
+    * if unique is false, will only do an insert of a new (possibly duplicated) value
+    * WARNING: If you have multiple rows with device_id/attrib_id pair, this could delete all of them.
+    */
+   function makeSQL ( $data, $unique = false ) {
+      $value = makeSafeSQLConstant( $data['value'] );
+      $attrib_id = makeSafeSQLConstant( $data['attrib_id'], 'i' );
+      $device_id = makeSafeSQLConstant( $data['device_id'],'i' );
+      $sql = array();
+      // standard SQL that does an insert if the value doesn't already exist.
+      // remove any existing records
+      $sql[] =
+         "update attrib_device
+            set removed_date = now()
+            where
+               device_id = $device_id
+               and attrib_id = $attrib_id
+               and removed_date is null
+               and attrib_id in (select attrib_id from attrib where multiples is null);";
+      $sql[] =
+         "update attrib_device
+            set value = $value
+            where
+               device_id = $device_id
+               and attrib_id = $attrib_id
+               and attrib_id in (select attrib_id from attrib where multiples is null)
+               and not exists (
+                     select * from (select * from attrib_device ) as m2 where device_id = $device_id and attrib_id = $attrib_id and value = $value
+                  );";
+      $sql[] =
+         "insert into attrib_device ( device_id, attrib_id,value, added_date )
+            select $device_id,$attrib_id, $value, now()
+            from dual
+            where
+               not exists (
+                  select *
+                  from
+                     attrib_device
+                  where
+                     device_id = $device_id
+                     and attrib_id = $attrib_id
+                     and value = $value
+                     and removed_date is null
+                  );";
       return $sql;
+   } // getSQL
+   function updateDatabase ( $queries ) {
+      $count = 0;
+      for( $i = 0; $i < count( $queries ); $i++ ) {
+         $result = doSQL( $queries[$i] );
+         $count += $result['affected_rows'];
+      }
+      return "$count entries added or updated";
+   }
    /*
     * following block of code is duplicated from the files module. It should instead be placed in root/include/library.php or something

Subversion Repositories computer_asset_manager_v1

(root)/modules/attributes/functions.php – Rev 100 → 102