Rev 3 | Blame | Compare with Previous | Last modification | View Log | Download | RSS feed
<?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;
}
?>