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;

}

?>