Subversion Repositories computer_asset_manager_v1

<?php

/*
 * these are some global functions which we need since their classes have not
 * been created yet. The code that calls these should be modified once CAMPv2
 * is complete. At that point, these should be calls to those classes.
 * 
 * 20160914 RWR
 * 
 * Modified fuzzyFindMachineID to look for device based solely on device name
 * if client name is not included.
 * 
 * 20161023 RWR
 * 
 * Removed fuzzyFindMachineID and changed getMachineID to use one single query
 * 
 */
 
   function makeSafeSQLValue ( $value, $type='S' ) {
      if(get_magic_quotes_gpc()) {
           $value = stripslashes($value);
       }
      $value = mysql_real_escape_string( $value );
      if (($type == 'S') and strlen($value)  > 0) { // put quotes around strings
         $value = "'" . $value . "'";
      } elseif ($type == 'D') {
         if ( $result = strtotime( $value ) ) {
            $value = Date( 'Y-m-d', $result);
         } else {
            $value = '0000-00-00';
         }
         $value = "'" . $value . "'";
      } elseif ($type == 'DT') {
         if ( $result = strtotime( $value ) ) {
            $value = Date( 'Y-m-d H:i:s', $result);
         } else {
            $value = '0000-00-00';
         }
         $value = "'" . $value . "'";
      } elseif (strlen($value) == 0) { // and substitue null for empty values otherwise;
         $value = 'null';
      }
      return $value;
   }
   

   /*
      Taken from comments at http://www.php.net/manual/en/function.mysql-query.php
      function originally named 'q'
      $r = q('Select id,foo FROM blah');
      echo $r[0]['id']; // first row, field 'id'
   
      // for single field single row selects
      // only the value is returned
      $count = q('SELECT count(*) from blah');
      // $count is the number
   
      Returns affected_rows and/or insert_id for anything other than select's.
      If you dont want field name keys then pass 0 for second parameter.
      
      For a query returning multiple rows, will return an associative array
         return['data'] contains an two dimensional array of all data received from the query
         return['meta']
            array of associative arrays. Each row in the array corresponds to a column in the query return
            Each array row contains the following:
               'name'   name of the column
               'length' maximum width of the column FOR THIS QUERY
               'numeric'true if the column is numeric
               'type'   type of the column (database dependant)

   */

      function queryDatabaseExtended($query,$assoc=1,$showErrors=true) {
      // print "<pre>In Query database\n---------------------\n$query\n---------------------\n</pre>";
      $r = @mysql_query($query);
      if( mysql_errno() ) {
         $error = 'MYSQL ERROR #'.mysql_errno().' : <small>' . mysql_error(). "</small><br><VAR>$query</VAR>";
         if ( $showErrors ) echo($error);
         return FALSE;
      }
      if( ! preg_match ( '/^\s*select/i', $query ) ) {
         $f = array( 'affected_rows' => mysql_affected_rows(),'insert_id' => mysql_insert_id());
         // create audit trail
         return $f;
      }
      $count = @mysql_num_rows($r);
      $fieldMeta = array();
      $i = 0;
      while ($i++ < mysql_num_fields($r)) {
         $meta = mysql_fetch_field ( $r );
         //objectDebugScreen($meta);
         $fieldMeta[] = array('name' => $meta->name, 'length' => $meta->max_length, 'numeric' => $meta->numeric, 'type' => $meta->type );
      }
      if( ! $count ) return '';
      $all = array();
      for( $i = 0; $i < $count; $i++ ) {
        if( $assoc ) $f = mysql_fetch_assoc($r);
        else $f = mysql_fetch_row($r);
        $all[] = $f;
      }
      mysql_free_result($r);
      return array( 'meta' => $fieldMeta, 'data' => $all, 'count' => $count);
   } // function queryDatabaseExtended


   // function returns the first column of the first row of data returned from query
   // or null no value returned
   function getOneDBValue( $sql ) {
      $data = queryDatabaseExtended( $sql, false ); // get the query results into a standard array
      return isset( $data['count'] ) ? $data['data'][0][0] : null;
   }
   
   function getOneUniqueDBValue ( $sql ) {
      $data = queryDatabaseExtended( $sql, false ); // get the query results into a standard array
      if ( isset( $data['count'] ) and $data['count'] > 1 ) {
         throw new Exception ('Duplicate Values in Database' );
      }
      return isset( $data['count'] ) ? $data['data'][0][0] : null;
   }
      
  
  function countNumberOfRows ( $sql ) {
     $count = queryDatabaseExtended("select count(*) numRows from ($sql) test");
     return $count['data'][0]['numRows'];
  }

   function makeWhereClause ($conditions) {
      $whereClause = ' where ' . implode (' and ', $conditions );
      return $whereClause;
   }
   
   function insertValuesIntoQuery( $query, $values ) {
      foreach ( $values as $name => $value ) {
         $query = search_replace_string($query, "<$name>", $value );
      }
      return $query;
   }

 
function getClientID ( $name ) {
   $client = getOneDBValue( "select client_id from client where name = '$name' and removed_date is null
             union
             select client_id from client_alias where alias = '$name' and removed_date is null");
   return $client;
}


function getMachineID ( $name, $clientID = null, $serialNumber = null ) {
   if ( $serialNumber ) {
      $query = "select device_id from device where serial = '$serialNumber'";
      try {
         $machineID = getOneUniqueDBValue( $query );
      }
      catch ( Exception $e ) { // we have too many with the same serial number
         $machineID = null;
      }
   }
   if ( ! isset( $machineID ) ) {
      $whereClause[] = "(device.name = '$name' or alias.alias = '$name') and device.removed_date is null";
      if ( $clientID )     $whereClause[] = "site.client_id = $clientID";
      $query = 'select device_id from device left outer join alias using (device_id) join site using (site_id) where ' . implode( ' and ', $whereClause );
      try {
         $machineID = getOneUniqueDBValue( $query );
      }
      catch ( Exception $e ) {
         throw $e;
      }
   }
   return $machineID;
}

?>