Rev 58 | Blame | Compare with Previous | Last modification | View Log | Download | RSS feed
<?php
/*
Copyright 2007
Daily Data, Inc.
All rights reserved
Description:
Library of routines for TimeTracker
Revision History:
Revision 10 - 20090115 - R. W. Rodolico
Modified editData routine to grab display query from table definition if key field not defined in complex join definition
*/
const REVISION = '2.18';
const HTML_QUOTE_CHAR = '"' ;
const CONSTANT_NO_VALUE_DROPDOWN = '--------' ;
// following field types are numeric (with length)
const NUMERICS = [
'BIT'=> 16,
'TINYINT'=> 1,
'BOOL'=> 1,
'SMALLINT'=> 2,
'MEDIUMINT'=> 9,
'INTEGER'=> 3,
'BIGINT'=> 8,
'SERIAL'=> 8,
'FLOAT'=> 4,
'DOUBLE'=> 5,
'DECIMAL'=> 246,
'NUMERIC'=> 246,
'FIXED'=> 246
];
// the following are the type names which are dates (with length)
const DATES = [
'DATE'=> 10,
'DATETIME'=> 12,
'TIMESTAMP'=> 7,
'TIME'=> 11,
'YEAR'=> 13
];
$LOGIN_PAGE = 'login.html';
function getParameter( $parameterName, $default = '' ) {
if (isset($_POST[$parameterName])) {
return $_POST[$parameterName];
}
if (isset($_GET[$parameterName])) {
return $_GET[$parameterName];
}
return $default;
}
function printLog ( $string ) {
if ( 0 ) {
$fh = fopen('/tmp/queryDatabaseExtended.sql', 'a');
fwrite($fh, $string . "\n");
fclose($fh);
}
}
function whoami () {
$output = '';
$i = queryDatabase("select concat(surname,', ',given_name) name from worker where worker_id = " . $_SESSION['effective_worker_id']);
$output .= $i;
if ( $_SESSION['effective_worker_id'] != $_SESSION['worker_id']) {
$i = queryDatabase("select concat(surname,', ',given_name) name from worker where worker_id = " . $_SESSION['worker_id']);
$output .= " ($i)";
}
return $output;
}
function logOut() {
unset( $_SESSION['user'] );
redirectPage($LOGIN_PAGE,array('message'=>'Logged Out, please log back in to continue'));
}
function objectDebugScreen ( $obj ) {
print '<pre>';
print_r ($obj);
print "</pre>\n";
}
/* function verifyLogin( $loginID, $password ) {
if ( strlen($loginID) > 10 ) {
$loginID = substr($loginID,1,10);
}
$sql = "select count(*) numRows, min(worker_id) worker_id from login where username = " .
makeSafeSQLValue($loginID) . ' and pass = md5(' . makeSafeSQLValue($password) . ") and enabled = 'Y'";
$info = queryDatabase( $sql );
if ( $info[0]['numRows'] == 1 ) {
$_SESSION['worker_id'] = ($info[0]['worker_id'] ? $info[0]['worker_id'] : -1); // keep track of the current worker
$_SESSION['effective_worker_id'] = $info[0]['worker_id']; // This allows superusers to enter info as if they were a different worker
$_SESSION['user'] = $loginID;
$sql = "select permission_id from user_permission where username ='" . $_SESSION['user'] . "'";
$info = queryDatabase( $sql );
for ( $i = 0; $i < count($info); $i++ ) {
$permission[$info[$i]['permission_id']] = true;
}
$_SESSION['permission'] = $permission;
validateDatabaseVersion();
redirectPage('user_menu.html');
} else {
return false;
}
}
*/
function makeSafeSQLValue ( $value, $type='S' ) {
global $databaseConnection;
if (strlen($value) == 0) { // simply set any empty values to null
return 'null';
}
//if(get_magic_quotes_gpc()) {
// $value = stripslashes($value);
// }
$value = mysqli_real_escape_string( $databaseConnection,$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 . "'";
}
return $value;
}
/*
creates audit trail of modifications to the database
*/
function audit ($sql ) {
return ;
$query = 'insert into _audit (_audit.user_id,_audit.sql) values ( ' . $_SESSION['worker_id'] . ', ' . makeSafeSQLValue($sql) . ')';
doSQL( $query );
#mysql_query( $query );
#if( mysql_errno() ) {
# $error = 'MYSQL ERROR #'.mysql_errno().' : <small>' . mysql_error(). "</small><br><VAR>$query</VAR>";
# echo($error);
#}
}
/*
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)
*/
/* Modified for mysqli. Uses a global variable, $databaseConnection, which must exist */
function queryDatabaseExtended($query,$assoc=1,$showErrors=true) {
global $databaseConnection;
// print "<pre>In Query database\n---------------------\n$query\n---------------------\n</pre>";
printLog( $query );
// we use the @ symbol to suppress warnings and errors when calling mysql_query
$r = @mysqli_query($databaseConnection,$query);
if( mysqli_errno($databaseConnection) ) {
$error = 'MYSQL ERROR #'.mysqli_errno($databaseConnection).' : <small>' . mysqli_error($databaseConnection). "</small><br><VAR>$query</VAR>";
if ( $showErrors ) echo($error);
return FALSE;
}
if( ! preg_match ( '/^\s*select/i', $query ) ) {
$f = array( 'affected_rows' => mysqli_affected_rows($databaseConnection),'insert_id' => mysqli_insert_id($databaseConnection));
// create audit trail
audit($query);
return $f;
}
$count = @mysqli_num_rows($r);
$fieldMeta = array();
$i = 0;
while ($i++ < mysqli_num_fields($r)) {
$meta = mysqli_fetch_field ( $r );
//objectDebugScreen($meta);
$fieldMeta[] = array('name' => $meta->name, 'length' => $meta->max_length, 'numeric' => array_key_exists( $meta->type, NUMERICS ), 'date' => array_key_exists( $meta->type, DATES ), 'type' => $meta->type );
}
if( ! $count ) return '';
$all = array();
for( $i = 0; $i < $count; $i++ ) {
if( $assoc ) $f = mysqli_fetch_assoc($r);
else $f = mysqli_fetch_row($r);
$all[] = $f;
}
mysqli_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 $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 showUserMenu () {
$permission = $_SESSION['permission'];
// objectDebugScreen($_SESSION['permission']);
// print "Effective User = " . $_SESSION['effective_worker_id'];
$result = '<ul>';
if ( $permission[1] ) {
$result .= '<li><a href="timesheet_input.html">Add/Enter Time Sheet/Expenses</a></li>';
$result .= '<li><A href="edit_personal.html">Edit Personal Data</A></li>';
$result .= '<li><A href="view_personal_payroll.html">View Past Payroll</A></li>';
$result .= '<li><a href="docs/user_manual.html" target="_blank">User Manual (in separate window)</a></li>';
}
if ( $permission[2]) {
$result .= '<li><a href="create_invoice.html">Create Invoice</a></li>';
$result .= '<li><a href="view_invoice.html">View Invoices</a></li>';
}
if ( $permission[3]) {
$result .= '<li><a href="create_payroll.html">Create Payroll</a></li>';
$result .= '<li>View Payroll</li>';
$result .= '<li><a href="docs/payroll_manual.html" target="_blank">Payroll Manual (in separate window)</a></li>';
}
if ( $permission[4]) {
$result .= '<li>Create Worker</li>';
$result .= '<li>View Worker</li>';
}
if ( $permission[5]) {
$result .= '<li>Add Client</li>';
}
if ( $permission[6]) {
$result .= '<li>Add Project</li>';
}
if ( $permission[7]) {
$result .= '<li><A href="reports.html">View Payroll Reports</A></li>';
}
if ( $permission[8] ) {
$result .= '<li><A href="becomeuser.html">Become another User</A></li>';
}
$result .= "<li><a href='/common-cgi/contact_us.php' target='_blank'>Submit Bug Report or Enhancement Request</a>";
$result .= "<li><a href='viewBugz.html' >View Bugs or Enhancments Request</a>";
$result .= "<li><a href='login.html?command=logout'>Log Out</a></ul>";
return $result;
}
function getProjectName( $projectID ) {
$sql = "select concat(client.name,' - ', project.project_name) project_name
from project join client on project.client_id = client.client_id
where project.project_id = $projectID
";
return queryDatabase( $sql );
}
function getExpenseReason( $expenseReasonID ) {
$sql ="select description
from expense_reason
where expense_reason_id = $expenseReasonID
";
return queryDatabase( $sql );
}
*/
function addDateRange ( $dateStart, $dateEnd ) {
$dateWhere = array();
if ( strlen($dateStart) > 0 ) {
array_push($dateWhere, "start_time >= '$dateStart'" );
}
if ( strlen($dateEnd) > 0 ) {
array_push($dateWhere, "end_time <= '$dateEnd'");
}
return $dateWhere;
}
function search_replace_string($string, $searchFor, $replaceWith ) {
$string = str_replace ( $searchFor, $replaceWith, $string );
return $string;
}
/*
simple function that breaks a multi line variable apart into an array of lines
removes any blank lines or trailing newlines
*/
function textArea2Array ( $textarea ) {
$results = preg_replace("/[\r\n]+/", "\n", $textarea ); // convert any combinations of \r and \n to one \n
$results = preg_replace("/\n$/", "", $results ); // remove any trailing newlines
return explode( "\n", $results );
}
/*
Function takes an SQL statement and converts it to an HTML table.
Return Value: HTML table representation of the query
Parameters:
$sql A valid SQL query to run
$format An optional array of format strings (suitable for printf) for each column (empty strings ignored)
$makeTableDef If True, the resulting HTML has the <table></table> tags; otherwise starts with header
$append Arbitrary string that is appended as a single column to each row
NOTE ON $append
$append may optionally contain variables of the form %colname%, which will be replaced
with values from the current row. $colname is taken from the META data from the query, thus
the query select foo from bar would have a meta of header of foo for column 0.
$append is searched for strings of form %foo% in that case, and the current value of column
foo replaces the tag %foo%.
%foo% is ignored if foo is not a valid column name in a query.
thus, a query such as select zip,city,state from zip_codes and a string of Hello %city% I'm glad
you are in %State% will not replace the second as State is not a column of this query (it is case
sensitive). Also, beware of items like select zip,concat(state,', ', city) from zip_codes. It is
much better to rewrite that as select zip,concat(state,', ', city) mytown from zip_codes.
If you don't know what all that means, get a book on SQL
*/
function queryToTable ( $sql, $format = '', $makeTableDef = true, $append='' ) {
//print "\n\n$sql\n\n";
$tdTextDefinition = '<td valign=' . HTML_QUOTE_CHAR . 'top' . HTML_QUOTE_CHAR . '>';
$tdNumberDefinition = '<td valign=' . HTML_QUOTE_CHAR .'top' . HTML_QUOTE_CHAR . 'align=' . HTML_QUOTE_CHAR . 'right' . HTML_QUOTE_CHAR . '>';
$rows = array();
$html = '';
$fields;
$info = array();
if ( $result = queryDatabaseExtended($sql,0) ) {
$meta = $result['meta'];
// Build the searchFor array for $append
$searchFor = array();
foreach ( $meta as $field ) {
$searchFor[] = '%' . $field['name'] . '%';
}
$info = $result['data'];
unset ($result);
/* special condition where only one row is returned. In that case, $info is not an array
of associations, but a simple association. In this case, we need to convert it
*/
if (count($info[0]) == 1) { // convert from association to single row array of associations
$temp = array();
foreach ($info as $column => $value) {
$temp[0][$column] = $value;
} // foreach
$info = $temp;
}
if (count($format) > 0 ) { // we have some formats, so let's do it the hard, slow way
for ( $row = 0; $row < count($info); $row++) {
$rows[$row] = '';
for ( $column = 0; $column < count($info[$row]); $column++ ) {
$rows[$row] .= strlen($format[$column]) && isset($info[$row][$column])> 0 ?
( $tdNumberDefinition . sprintf($format[$column],$info[$row][$column]) . '</td>')
: ($tdTextDefinition . $info[$row][$column] . '</td>');
} // for $column
/*
let's append some stuff to the row if it exists.
We will take the array of SearchFor containing the column names
and the array of values from this row, then look for matches and replace them
with the correct values. Note, the column names in $append should have percent
signs appended and prepended, thus a column name of joe would be %joe% in %append
*/
if (strlen($append) > 0) { // let's append some stuff to the row
$rows[$row] .= $tdTextDefinition . str_replace ( $searchFor, $info[$row], $append ) . '</td>';
}
} // for $row
} else { // no formatting, so we just slam the stuff together
for ( $row = 0; $row < count($info); $row++) {
$currentValue = $tdTextDefinition . implode( '</td>' . $tdTextDefinition, $info[$row]) . '</td>';
if (strlen($append) > 0) { // see explaination in if part of this structure
$currentValue .= $tdTextDefinition . str_replace ( $searchFor, $info[$row], $append ) . '</td>';
}
$rows[] = $currentValue;
}
}
// ok, let's get the field headers from the table
$html .= '<tr>';
foreach ( $meta as $field ) {
$html .= '<th>' . $field['name'] . '</th>';
}
$html .= '</tr>';
// we now have all the info, so let's make it into a table
$html .= '<tr>' . implode('</tr><tr>', $rows ) . '</tr>';
if ( $makeTableDef ) { // they want the <table> def, so let's give it to them
$html = '<table border=' . HTML_QUOTE_CHAR . '1' . HTML_QUOTE_CHAR . '>' . $html . '</table>';
} // if ( $makeTableDef
} // if ( $info = queryDatabase($sql,0) )
// print '<pre>'; print_r ($info); print '</pre>';
return $html;
}
/*
function executes a query, then returns an array containing only the values of the first
field in the return as the keys to an associative array. NOTE: any duplicates will be
discarded. This is useful when you simply want to know if a value exists, ie if you
are building an html select from a query, and a separate query needs to hold the
values that are currently selected
*/
function sqlValuesToKeys ($sql) {
$returnValue = array();
if ( $info = queryDatabaseExtended($sql,0) ) { // note we are turning off the associative array here
$info = $info['data'];
foreach ( $info as $key => $value ) { // only thing I know to do is iterate
$returnValue[$value[0]] = true;
}
} // if
return $returnValue;
}
/* this function will take the result of an SQL query that returns at least two columns.
The first column becomes the keys in an associative array, and the second column
becomes the value.
Note, because it is an associative array, duplicates of the first column will only contain
the last value.
*/
function sqlColumnColumnArray ( $sql ) {
$returnValue = array();
if ( $info = queryDatabaseExtended($sql,0) ) { // note we are turning off the associative array here
$info = $info['data'];
foreach ( $info as $key => $value ) { // only thing I know to do is iterate
$returnValue[$value[0]] = $value[1];
}
} // if
return $returnValue;
}
/* the first returned column becomes the value, the second becomes the display element
*/
function queryToSelect ( $sql, $selectedFieldsQuery = '' ) {
$selectedFields = array();
if ( preg_match ( '/^\s*select/i', $selectedFieldsQuery ) ) { // They passed in a query
if (strlen($selectedFieldsQuery) ) {
$selectedFields = sqlValuesToKeys($selectedFieldsQuery);
}
} else { // assume the passed in a value
$selectedFields[$selectedFieldsQuery] = 1;
}
$info = queryDatabaseExtended( $sql, false );
if ($info['count'] == 0) { // we had no entries
return '';
}
$info = $info['data'];
$html = '';
for ( $i = 0; $i < count($info); $i++ ) {
$html .= '<option value="' . $info[$i][0] . '"';
if ( $selectedFields[$info[$i][0]] ) {
$html .= ' selected';
}
$html .= '>' . $info[$i][1] . '</option>';
}
return $html;
}
/* function will take a query and turn it into a series of check boxes. It must contain
two columns, with the first becoming the the name of the checkbox
and the second becoming the displayed value. an optional third column will be used
to match if the $checkedValuesQuery is used.
if $checkedValuesQuery is not empty, it will be run to see what boxes need to be
checked by being compared to the third column of the $sql query.
$htmlBefore will be placed before each check box, and $htmlAfter will be placed after
each checkbox.
if $tableColumns is set to a number, the checkboxes will be embedded in a group of
<tr></tr>, each containing table columns of $tableColumns width. In this case, $htmlBefore
will have <td> prepended and $htmlAfter will have </td> appended, meaning any passed
values will be INSIDE of the td. NOTE: the <table></table> tags are NOT put in.
NOTE: currently, using the table stuff will leave a dangling row with 0 elements if
the number of elements equal the number of columns.
*/
function queryToCheckBoxes ( $sql, $checkedValuesQuery = '', $htmlBefore = '', $htmlAfter = '', $table_columns='' ) {
$html = '';
if ($table_columns) {
$htmlBefore = '<td>' . $htmlBefore;
$htmlAfter .= '</td>';
$html .= '<tr>';
}
$numColumns = 0;
$checkBoxes = queryDatabaseExtended( $sql,0 );
$selectedFields = array();
if (strlen($checkedValuesQuery) ) {
$selectedFields = sqlValuesToKeys($checkedValuesQuery);
}
foreach ($checkBoxes['data'] as $row => $values) {
if ($table_columns && ++$numColumns == $table_columns) {
$html .= '</tr><tr>';
$numColumns = 0;
}
//objectDebugScreen($row);
$html .= $htmlBefore . '<input type="checkbox" name="' . $values[0] . '"';
if ( $selectedFields[$values[2]] ) {
$html .= ' checked';
}
$html .= '>' . $values[1] . $htmlAfter;
//<INPUT type="checkbox" checked name="temp">
} // foreach
$html .= '</tr>';
return $html;
}
function makeMoney( $value ) {
return sprintf( '%0.2f', $value );
}
function queryToCSV ( $sql ) {
$rows = array();
$CSV = '';
$info = array();
if ( $result = queryDatabaseExtended($sql,0) ) {
$meta = $result['meta'];
//objectDebugScreen($result);
$info = $result['data'];
unset ($result);
$headers = array();
foreach ( $meta as $field ) {
$headers[] = $field['name'];
} // foreach
$rows[] = implode("\t", $headers);
/* special condition where only one row is returned. In that case, $info is not an array
of associations, but a simple association. In this case, we need to convert it
*/
if (count($info[0]) == 1) { // convert from association to single row array of associations
$temp = array();
foreach ($info as $column => $value) {
$temp[0][$column] = $value;
} // foreach
$info = $temp;
}
for ( $row = 0; $row < count($info); $row++) {
$rows[] = implode("\t", $info[$row]);
}
// we now have all the info, so let's make it into a table
$CSV .= implode("\n", $rows );
} // if ( $info = queryDatabase($sql,0) )
// print '<pre>'; print_r ($info); print '</pre>';
return $CSV;
}
/* This function should only be run once, when the original data has been ported from the other system */
function normalizeTime () {
//queryDatabase('update time_tracker set payroll_id = null where start_time > 20070101');
$info = queryDatabase('select * from payroll where payroll_date >= 20070101 order by worker_id,payroll_date');
for ( $i = 0; $i < count($info); $i++) {
$payroll_id = $info[$i]['payroll_id'];
$workerID = $info[$i]['worker_id'];
$payroll_date = $info[$i]['payroll_date'];
queryDatabase("update time_tracker set time_tracker.payroll_id = $payroll_id where payroll_id is null and worker_id = $workerID and start_time <= '$payroll_date'");
queryDatabase("update expense set payroll_id = $payroll_id where payroll_id is null and worker_id = $workerID and expense_date <= 'payroll_date'");
}
}
/*
redirects to a different page (ie, sends out a location header)
$page is required
$parameters can be blank but, if they are not, they are a URL encoded string
$path defaults to current uri if it is not passed in
$host defaults to current server name if not passed in
*/
function redirectPage ( $page,$parameters=array(),$path='',$host='', $protocol='') {
$params = array();
if ( strlen($host) == 0 ) $host = $_SERVER['HTTP_HOST'];
if ( strlen($path) == 0 ) $path = rtrim(dirname($_SERVER['PHP_SELF']), '/\\');
if ( count($parameters) > 0 ) {
foreach ($parameters as $var => $value ) {
$params[] = $var . '=' . rawurlencode($value);
}
}
if ( ! $protocol ) {
$protocol = isset($_SERVER['HTTPS']) ? "https:" : "http:";
}
$location = $protocol . '//' . $host . $path . '/' . $page . ( sizeof($params) ? ('?' . implode( '&', $params )) : '');
header("Location: $location", true);
exit;
}
function FileErrorMessage ( $errorCode ) {
switch ($errorCode) {
case 1:
return("The uploaded file exceeds the upload_max_filesize directive (" . ini_get("upload_max_filesize") . ") in php.ini.");
case 2:
return("The uploaded file exceeds the MAX_FILE_SIZE directive (" . MAX_UPLOAD_FILE_SIZE . ").");
case 3:
return("The uploaded file was only partially uploaded.");
case 4:
return("No file was uploaded.");
case 6:
return("Missing a temporary folder.");
case 7:
return("Failed to write file to disk");
default:
return("Unknown File Error");
}
}
/* this function will clean up nasty stuff on the uploaded file name before
allowing it to be used to store the file to disk.
It removes any non-alphanumerics, underscores, periods and dashes
*/
function fixFileName ( $filename ) {
$filename = strtolower( $filename );
$filename = preg_replace('/\s+/', '_', $filename); // convert all spaces to underscore
$filename = preg_replace( '/[^a-z0-9._-]/', '', $filename );
return $filename;
}
/* function will take a hash, and return the hash with the values modified
to the form $key$delimiter$value
thus, a hash 'col1'=>'value1', 'col2'=>'value2' would return
col1=>'col1=value1', col2=>'col2=value2'
This is useful for creating an update or where clause, as the user can have
a hash of conditions (or updates to make), call this function, then
implode. Thus, in the above case, if we wanted to have a where clause
we could say implode( ' and ', makeEqualsFromHash($conditions) ) and
get col1=value1 and col2=value2 as the result.
*/
function makeEqualsFromHash( $hash, $delimiter='=' ) {
foreach ( $hash as $key => $value ) {
$hash[$key] = $key . $delimiter . $value ? $value : 'null';
}
return $hash;
}
/*
function will takes fieldList, a hash of column names and values, and either
updates or inserts depending upon whether the record exists.
It will do a query by taking $existsColumns to determine if the record exists.
($existsColumns is another hash of column names and values) As a convenience,
if $existsColumns is empty, or the value is empty, will assume an insert
If record exists
creates an update out of $fieldList
else
creates an insert out of $fieldList
then, executes the query
Returns the value of lastInsert on insert
NOTE: if the key field(s) are not automatically created on insert, they
must be included in $fieldList also.
*/
function addOrUpdate ( $tableName, $existsColumns, $fieldList ) {
$sql = '';
$insert = true;
// assume we will have a whereClause
$whereClause = true;
// are there any null values?
foreach($existsColumns as $key => $value) {
if (strlen($value) == 0) {
$whereClause = false;
}
}
if ($whereClause and count($existsColumns) ) {
$whereClause = implode(' and ', makeEqualsFromHash($existsColumns) );
$result = queryDatabaseExtended( "select * from $tableName where $whereClause" );
if ($result['count'] == 1) {
$insert = false;
}
}
if ($insert) { // we must be adding a record, so do an insert
$sql = "insert into $tableName(";
$sql .= implode(',',array_keys($fieldList) );
$sql .= ') values (';
$sql .= implode(',', $fieldList);
$sql .= ')';
$result = queryDatabaseExtended($sql);
return ($result['insert_id']);
} else { // must be a true update
$sql = "update $tableName set " . implode(',',makeEqualsFromHash($fieldList) ) . " where $whereClause" ;
queryDatabaseExtended( $sql );
}
}
/* function generates a random password. Shamelessly stolen from
http://www.laughing-buddha.net/jon/php/password/
*/
function generatePassword ($length = 8) {
// start with a blank password
$password = "";
// define possible characters
$possible = "0123456789bcdfghjkmnpqrstvwxyz";
// set up a counter
$i = 0;
// add random characters to $password until $length is reached
while ($i < $length) {
// pick a random character from the possible ones
$char = substr($possible, mt_rand(0, strlen($possible)-1), 1);
// we don't want this character if it's already in the password
if (!strstr($password, $char)) {
$password .= $char;
$i++;
}
}
// done!
return $password;
}
function showDateWithNulls( $date, $prompt='' ) {
if ( is_null($date) or $date == '0000-00-00' ) {
return $prompt;
} else {
return $date;
}
} // function showDateWithNulls
// functions merged from previous version
// Quote variable to make safe
function makeQuerySafe ( $fieldValue, $canBeNull = false ) {
if ( $canBeNull && strlen($fieldValue) == 0 ) { // if empty string and it can be null
return 'NULL'; // just return null
}
// Stripslashes
if (get_magic_quotes_gpc()) {
$fieldValue = stripslashes($fieldValue);
}
// Quote if not a number or a numeric string
if (!is_numeric($value)) {
$fieldValue = "'" . mysql_real_escape_string($fieldValue) . "'";
}
return $fieldValue;
}
function HumanReadable2Number ( $hr ) {
$hr = strtolower($hr);
$num = $hr;
if ( preg_match('/([0-9]+)([tgmk])/', $hr ,$matches) ) {
$num = $matches[1];
$modifier = $matches[2];
if ($modifier == 'g') {
$num *= 1024 * 1024 * 1024;
} elseif ($modifier == 'm' ) {
$num *= 1024 * 1024;
} elseif ($modifier == 'k' ) {
$num *= 1024;
} else {
return "Unable to decipher the number $hr";
}
}
return $num;
}
function Number2HumanReadable( $num ) {
if ( $num > 1024 * 1024 * 1024 ) {
return round( $num / (1024*1024 * 1024) ) . 'G';
} elseif ( $num > 1024 * 1024 ) {
return round( $num / (1024*1024) ) . 'M';
} elseif ( $num > 1024 ) {
return round( $num / 1024 ) . 'k';
} else {
return $num . " bytes";
}
}
function doSQL( $sql ) {
// print '<pre>' . "$sql\n" . '</pre>';
return queryDatabaseExtended($sql);
#mysql_query ($sql);
}
function deleteData ( $tableInfo, $id ) {
$sql = 'delete from ' . $tableInfo['table name'] . ' where ' . $tableInfo['key field'] . " = $id";
queryDatabaseExtended($sql);
#mysql_query ($sql);
return '';
}
function makeAddFieldHTML ( $columnDef, $fieldName, $defaults = array() ) {
if ( $columnDef['readonly'] ) {
$result .= 'null';
} else {
if ( $defaults[$fieldName] ) {
$default = $defaults[$fieldName];
}
if ( $columnDef['type'] == 'string' ) {
$maxWidth = $columnDef['width'] or MAX_INPUT_FIELD_DISPLAY;
$displayWidth = $maxWidth < MAX_INPUT_FIELD_DISPLAY ? $maxWidth : MAX_INPUT_FIELD_DISPLAY;
$result .= "<input type='text' name='$fieldName' size='$displayWidth' maxlength='$maxWidth' value=''>";
} elseif ($columnDef['type'] == 'datetime') {
$value = '';
if ($columnDef['default'] != 'null') {
$value = $columnDef['default'];
if ( $columnDef['required'] and ! $value ) {
$value = date('Y-m-d');
}
}
$result .= "<input type='text' name='$fieldName' size='$displayWidth' maxlength='$maxWidth' value='$value'>";
} elseif ($columnDef['type'] == 'password') {
$result .= "<input type='text' name='$fieldName' size='$displayWidth' maxlength='$maxWidth' value=''>";
} elseif ( $columnDef['type'] == 'text' ) {
$width = $columnDef['width'] ? $columnDef['width'] : MAX_INPUT_FIELD_DISPLAY;
$rows = $columnDef['height'] or DEFAULT_TEXTAREA_HEIGHT;
$result .= "<textarea name='$fieldName' rows='$rows' cols='$width'>" . $existingValue . "</textarea>";
} elseif ( $columnDef['type'] == 'file' ) {
$result .= "<input type='file' name='$fieldName' value=''>";
} elseif ( $columnDef['type'] == 'lookup' ) {
$result .= "<select name='$fieldName'>";
$result .= makeDropDown ( $columnDef['table'], $columnDef['keyfield'], $columnDef['display_field'], ( $default ? $default : 0 ) );
$result .= "</select>";
} elseif ( $columnDef['type'] == 'bool' ) {
$result .= "<input type='radio' name='$fieldName' value='1'";
$result .= ">True<input type='radio' name='$fieldName' value='0'";
$result .= " checked>False";
} elseif ( $columnDef['type'] == 'datetime' ) {
} else {
$result .= "<input type='text' name='$fieldName' value=''>";
}
}
return $result;
}
function addData ( $tableInfo ) {
$maxFileSize = min(HumanReadable2Number(MAX_UPLOAD_FILE_SIZE),HumanReadable2Number(ini_get("upload_max_filesize")));
$result = '';
$result .= "<p><form enctype='multipart/form-data' action='" . $_SERVER['PHP_SELF'] . "' method='post'>";
$result .= "<input type='hidden' name='currentdb' value='" . $tableInfo['table name'] . "' />";
$result .= "<input type='hidden' name='MAX_FILE_SIZE' value='$maxFileSize' />";
$result .= '<table border="1">';
foreach ($tableInfo['field info'] as $field => $value) {
$displayName = $value['display name'] ? $value['display name'] : $field;
$result .= "<tr><td valign='top'>$displayName";
if ( $value['type'] == 'file' ) {
$result .= "<br><font size='-2'>Max Size " . Number2HumanReadable($maxFileSize) . "</font>";
}
$result .= "</td><td>";
$result .= makeAddFieldHTML ( $value, $field );
$result .= "</td></tr>\n";
}
// process any multi selects we may have
if ($tableInfo['complex join']) {
foreach ($tableInfo['complex join'] as $table => $value) {
$result .= '<tr><td>' . $table . '</td><td>';
$result .= makeMultiSelect( $tableInfo['table name'], $tableInfo['key field'], $id, $value);
$result .= '</td></tr>';
} // foreach
} // if
$result .= "<tr><td colspan='2' align='center'><input type='submit' /></td></tr>
<input type=hidden name='mode' value='added' />
<input type=hidden name='id' value='";
$result .= $id;
$result .= "'></table></form> <p>";
return $result;
}
function saveFilesUploaded( $fieldName ) {
$result = '';
$uploaddir = $_SERVER['DOCUMENT_ROOT'] . IMAGE_DIRECTORY;
$result = fixFileName( basename($_FILES[$fieldName]['name']) );
if (! move_uploaded_file($_FILES[$fieldName]['tmp_name'], $uploaddir . $result)) {
$result = '';
}
return $result;
}
function makeDropDown ( $table, $index_field, $display_field = '', $keyvalue = '' ) {
/*
this executes a query on $table (using $index_field and $display_field), then
uses the result to populate a list of <option> tags suitable for inclusion in
a <SELECT>. If $index_field for a row equals $keyvalue, that option has its
SELECT paramter turned on
*/
$returnValue = '';
if ( $display_field ) { // they are passing in a table, index field, display field and key value
$sql = "select $index_field,$display_field from $table";
} else { // in the two parameter form, first parameter is query, second is keyvalue
$sql = $table;
$keyvalue = $index_field;
}
$data = queryDatabaseExtended($sql);
if ( ! $data ) {
$returnValue = '<option>No Values Found</option>\n';
} else {
$index_field = $data['meta'][0]['name'];
$display_field = $data['meta'][1]['name'];
foreach ($data['data'] as $info) {
$returnValue .= "<option value='" . $info[$index_field] . "'";
if ( $info[$index_field] == $keyvalue ) {
$returnValue .= ' selected' ;
}
$returnValue .= '>' . $info[$display_field] . "</option>\n";
}
}
return $returnValue;
}
function makeMultiSelect ( $thisTable, $thisKeyfield, $thisValue, $multiSelectDefinition ){
/*
This is some funky code that creates a multi select box for when the current table has a one to many relationship
with another table through an intermediate table, ie professionals joined to projects through an intermediate table,
professionals_projects.
It creates a query of the form
select dislayfield, keyfield, nullfield
from foreign_table left outer join
(this_table join joining_table on join_condition)
on join_condition
where this_table.keyfield = this_record_id;
Display Field generally comes from the foreign table, as does keyfield. A multi select box is created which contains
the keyfield as the value and the display field displayed. If nullfield is not null, the item is option is selected.
The following real world example may help:
table professionals
professionals_id (key field)
name (the name of the professional)
table projects (the current one being edited)
projects_id (key field)
other stuff
table projects_professionals
projects_id (fk into projects)
professionals_id (fk into professionals)
A query such as
select professionals.professionals_id, professionals.name, professionals_projects.projects_id
from professionals left outer join
(projects join professionals_projects on projects.project_id = professionals_projects.projects_id)
on professionals.professionals_id = professionals_projects.professionals_id
where projects.project_id = $id;
would return a row for every entry in the professionals table, but with a null value in the projects_id
column if there was no matching entry in the professionals_projects table. This can be used to build
the select
*/
if ($thisValue) {
// first build the query
$sql = 'select ';
$sql .= $multiSelectDefinition['values table']['table name'] . '.' . $multiSelectDefinition['values table']['display field'] . ',' ;
$sql .= $multiSelectDefinition['values table']['table name'] . '.' . $multiSelectDefinition['values table']['key field'] . ', ' ;
$sql .= $multiSelectDefinition['null field'] . ' ';
$sql .= 'from ' . $multiSelectDefinition['values table']['table name'] . ' left outer join (';
$sql .= $thisTable . ' join ' . $multiSelectDefinition['join table']['table name'] . ' on ';
$sql .= $multiSelectDefinition['join table']['join condition'] . ') on ';
$sql .= $multiSelectDefinition['values table']['join condition'] . " where $thisTable.$thisKeyfield = $thisValue";
} else {
$sql = 'select ';
$sql .= $multiSelectDefinition['values table']['table name'] . '.' . $multiSelectDefinition['values table']['display field'] . ',' ;
$sql .= $multiSelectDefinition['values table']['table name'] . '.' . $multiSelectDefinition['values table']['key field'] . ', ' ;
$sql .= ' null ';
$sql .= 'from ' . $multiSelectDefinition['values table']['table name'];
}
print "<pre>$sql</pre>";
// now, run it
$result = "\n<SELECT name='" . $multiSelectDefinition['values table']['table name'] . "[]' multiple>\n";
$data = queryDatabaseExtended($sql);
foreach ( $data['data'] as $info ) {
#$data = mysql_query( $sql ) or die(mysql_error());
#while ( $info = mysql_fetch_array( $data ) ) {
/*
we will refer to fields by number due to the inconsistency of labeling returned rows, ie the query may
request table.fieldname or simply fieldname, but the result set will always call it simply fieldname
since we control the query, we know that field 0 is the display name, field 1 is the keyfield and
field 2 is the field that will be null or not
*/
$result .= "<option value=" . $info[1] ;
if ($info[2]) { $result .= " selected"; }
$result .= '>' . $info[0] . "</option>\n";
} // while
$result .= "</SELECT>\n";
return $result;
}
function makeEditFieldHTML ($columnDef, $existingValue, $fieldName ) {
if ( $columnDef['readonly'] ) {
$result .= $existingValue;
} else {
if ( $columnDef['type'] == 'string' ) {
$maxWidth = $columnDef['width'] or MAX_INPUT_FIELD_DISPLAY;
$displayWidth = $maxWidth < MAX_INPUT_FIELD_DISPLAY ? $maxWidth : MAX_INPUT_FIELD_DISPLAY;
$result .= "<input type='text' name='$fieldName' size='$displayWidth' maxlength='$maxWidth' value='" . $existingValue . "'>";
} elseif ( $columnDef['type'] == 'password') {
$maxWidth = $columnDef['width'] or MAX_INPUT_FIELD_DISPLAY;
$displayWidth = $maxWidth < MAX_INPUT_FIELD_DISPLAY ? $maxWidth : MAX_INPUT_FIELD_DISPLAY;
$result .= "<input type='text' name='$fieldName' size='$displayWidth' maxlength='$maxWidth' value=''>";
} elseif ( $columnDef['type'] == 'text' ) {
$width = $columnDef['width'] ? $columnDef['width'] : MAX_INPUT_FIELD_DISPLAY;
$rows = $columnDef['height'] or DEFAULT_TEXTAREA_HEIGHT;
$result .= "<textarea name='$fieldName' rows='$rows' cols='$width'>" . $existingValue . "</textarea>";
} elseif ( $columnDef['type'] == 'file' ) {
$result .= "<input type='hidden' name='MAX_FILE_SIZE' value=value'" . MAX_UPLOAD_FILE_SIZE . "' />";
if ( $columnDef['filetype'] == 'picture' ) {
$result .= "<img src='" . IMAGE_DIRECTORY . $existingValue . "' height='" . EDIT_IMAGE_HEIGHT . "' alt='Image'>";
}
$result .= "<br>";
$result .= "<input type='file' name='$fieldName' value='" . $existingValue . "'>";
} elseif ( $columnDef['type'] == 'lookup' ) {
$result .= "<select name='$fieldName'>";
if ( $columnDef['null_ok'] ) {
$result .= '<option value="' . CONSTANT_NO_VALUE_DROPDOWN . '">' . CONSTANT_NO_VALUE_DROPDOWN . '</option>';
}
if ($columnDef['query']) { // they want to pass a query, so we'll do that. Query has key in first column, display in second
$result .= makeDropDown ($columnDef['query'], $existingValue );
} else { // no query, so we give the table name, keyfield, and display field
$result .= makeDropDown ( $columnDef['table'], $columnDef['keyfield'], $columnDef['display_field'], $existingValue );
}
$result .= "</select>";
} elseif ( $columnDef['type'] == 'bool' ) {
$result .= "<input type='radio' name='$fieldName' value='1'";
if ( $existingValue ) {
$result .= ' checked';
}
$result .= ">True<input type='radio' name='$fieldName' value='0'";
if ( ! $existingValue ) {
$result .= ' checked';
}
$result .= ">False";
} else {
$result .= "<input type='text' name='$fieldName' value='" . $existingValue . "'>";
}
}
return $result;
}
function editData ( $tableInfo, $id ) {
$maxFileSize = min(HumanReadable2Number(MAX_UPLOAD_FILE_SIZE),HumanReadable2Number(ini_get("upload_max_filesize")));
$sql = "SELECT * FROM " . $tableInfo['table name'] . " where " . $tableInfo['key field'] . " = $id";
// $result = "<pre>$sql</pre><br />";
$result .= "<p><form enctype='multipart/form-data' action='" . $_SERVER['PHP_SELF'] . "' method='post'>";
$result .= "<input type='hidden' name='currentdb' value='" . $tableInfo['table name'] . "' />";
$result .= "<input type='hidden' name='MAX_FILE_SIZE' value='$maxFileSize' />";
$result .= '<table border="1">';
$data = queryDatabaseExtended($sql);
$info = $data['data'][0];
foreach ($tableInfo['field info'] as $field => $value) {
$displayName = $value['display name'] ? $value['display name'] : $field;
$result .= "<tr><td valign='top'>$displayName";
if ( $value['type'] == 'file' ) {
$result .= "<br><font size='-2'>Max Size " . Number2HumanReadable($maxFileSize) . "</font>";
}
$result .= "</td><td>";
$result .= makeEditFieldHTML ($value, $info[$field], $field );
$result .= "</td></tr>\n";
}
global $DATABASE_DEFINITION;
// process any multi selects we may have
if ($tableInfo['complex join']) {
foreach ($tableInfo['complex join'] as $table => $value) {
if ($value['values table']['key field']) {
$valueQuery = 'select ' . $value['values table']['key field'] . ',' . $value['values table']['display field'] . ' from ' . $value['values table']['table name'];
} else {
$valueQuery = $DATABASE_DEFINITION[$value['values table']['table name']]['display query'];
}
$selectedFieldsQuery = 'select ' . $value['join table']['values link'] . ' from ' . $value['join table']['table name'] . ' where ' . $value['join table']['my link'] . " = $id";
$result .= "<tr><td>$table</td><td>\n";
$result .= "\n<SELECT name='" . $value['values table']['table name'] . "[]' multiple>";
$result .= queryToSelect( $valueQuery, $selectedFieldsQuery);
$result .= '</select>';
$result .= '</td></tr>';
} // foreach
} // if
if ( $tableInfo['child tables'] ) { // process any children tables we may have
foreach ( $tableInfo['child tables'] as $table => $value ) {
$idColumn = $thisTableDef['key field']; // figure out which is the keyfield for the child table
// now, let's figure out what the key is that links the two. If parent key is defined, use it. Otherwise, look
// for a column with the same name our our key field
$parentKeyColumn = $value['parent key'] ? $value['parent key'] : $tableInfo['key field'];
// $result .= "Parent Key Column is $parentKeyColumn\n<br>";
$result .= "<tr><td colspan='2'>\n";
$result .= editDataTable( $DATABASE_DEFINITION[$table], // the definition of the sub-table
"$parentKeyColumn=$id", // the condition for limiting the sub-table
array( $parentKeyColumn => $id ) // the columns to pre-populate and mark read-only
);
$result .= "</td></tr>\n";
} // foreach
} // if
$result .= "<tr><td colspan='2' align='center'><input type='submit' /></td></tr>
<input type=hidden name='mode' value='edited'>
<input type=hidden name='id' value='";
$result .= $id;
$result .= "'>
</table>
</form>";
return $result;
} // editData
/*
function will create a table with all rows and columns from the database inside it, ready for editing.
It will limit the rows shown based on $whereClause, and not allow editing of columns listed in $defaultValues
One "empty" row will be included for adding. This row will have $defaultValues filled in with $defaultValues
and will be marked read-only also.
The INPUT tags created will be of the form fieldname concated with some special stuff to allow updateData and insertData
to find them. See documentation for additional information
*/
function editDataTable ( $tableInfo, $whereClause, $defaultValues = '') {
// print "<pre>"; print_r( $defaultValues ); print "</pre>\n";
$idColumn = $tableInfo['key field'];
$sql = "SELECT * FROM " . $tableInfo['table name'] . ($whereClause ? " where $whereClause" : '');
$data = queryDatabaseExtended($sql);
// Now, create a table to display this child
// we will create a single td, that colspans everything else, and put the table into it
// simultaneously, let's create a blank line so the user can fill it in
$blankLine = '';
$result .= "<table border='1'>\n";
$result .= "<caption>" . ( $tableInfo['display name'] ? $tableInfo['display name'] : $tableInfo['table name'] ) . "</caption>\n";
$result .= "<thead bgcolor='lightGray'><tr>\n";
// create a key for adding a new entry. This is recognized by updateData and insertData. We will tag all INPUT's in the insert row
// with this.
$key = CHILD_KEY_DELIMITER . CHILD_KEY_TAG . CHILD_KEY_DELIMITER . $tableInfo['table name'] . CHILD_KEY_DELIMITER . CHILD_KEY_INSERT_TAG;
foreach ( $tableInfo['field info'] as $column => $value ) {
if ( $column == $idColumn ) { continue; } // we assume the $idColumn is auto-populated
if ( $defaultValues[$column] ) { // this is one of the read-only fields
$blankLine .= "<input type='hidden' name='" . $column . $key . "' value='$defaultValues[$column]'>\n";
} else {
$result .= '<th>';
$result .= $value['display name'] ? $value['display name'] : $column;
$result .= '</th>';
$blankLine .= '<td>' . makeAddFieldHTML ( $value, $column . $key ) . '</td>';
}
}
$result .= "</tr></thead>\n";
$result .= '<tr>' . $blankLine . '</tr>';
// ok, we have a pretty header, now let's do all the actual data
if ($data) { // if we have some data to display
foreach ( $data['data'] as $info ) {
$result .= '<tr>';
// create a special key so updateData will be able to recognize these rows.
$key = CHILD_KEY_DELIMITER . CHILD_KEY_TAG . CHILD_KEY_DELIMITER . $tableInfo['table name'] . CHILD_KEY_DELIMITER . $info[$idColumn];
foreach ($tableInfo['field info'] as $field => $value) {
if ( $field == $idColumn ) { continue; }
if ( $defaultValues[$field] ) { // if this is the linkage to the parent, don't change it. But, we need it for the post.
$result .= "<input type='hidden' name='" . $field . $key . "' value='$defaultValues[$field]'>\n";
} else {
$result .= "<td>";
$result .= makeEditFieldHTML ($value, $info[$field], $field . $key );
$result .= "</td>\n";
}
}
$result .= '</tr>';
}
}
$result .= "</table><!--save--></td></tr>\n";
return $result;
}
function makeList( $currentDB, $sql, $display_list, $keyfield ) {
$result = '';
#$data = mysql_query($sql) or die(mysql_error());
$result .= "<table cellpadding=3>";
$result .= '<tr>';
foreach ($display_list as $field ) {
$result .= "<th>" . $field . "</th> ";
}
$result .= "</tr>\n";
$result .= "<td colspan=5 align=right><a href=" .$_SERVER['PHP_SELF']. "?currentdb=$currentDB&mode=add>Add</a></td>";
$data = queryDatabaseExtended($sql);
foreach ( $data['data'] as $info ) {
# while($info = mysql_fetch_array( $data )) {
$result .= '<tr>';
foreach ($display_list as $field ) {
$result .= "<td>" . $info[$field] . "</td> ";
}
$result .= "<td><a href=" . $_SERVER['PHP_SELF'] . "?id=" . $info[$keyfield] . "¤tdb=$currentDB&mode=edit>Edit</a></td>";
$result .= "<td><a href=" .$_SERVER['PHP_SELF']. "?id=" . $info[$keyfield] . "¤tdb=$currentDB&mode=remove>Remove</a></td></tr>";
}
$result .= "</table>";
return $result;
}
/*
function is called after information is modified via the editData function above.
Strictly takes the information from the form, then updates the database.
Will add $suffix (if defined) to all field names to get information from form. This allows
us to process multiple entries of the same data. For example, if we have an HTML table that
has entries in the form fieldname-id, fieldname will be taken from $tableInfo, and "-id" will
be appended when getting information from the form.
*/
function updateData( $tableInfo, $id, $suffix = '' ) {
$sql = ''; // we will build the resulting SQL here
$result = ''; // our output string, ie what we will be sending back to the calling routine
$fileName = ''; // used to store the modified file name, if the field has a file upload
// for each field in the table definition
foreach ($tableInfo['field info'] as $field => $value) {
if ( $value['type'] == 'file' && $_FILES[$field]['name'] ) {
$fileName = saveFilesUploaded( $field . $suffix );
if ( ! $fileName ) {
$result .= "Error Saving File $field: " . FileErrorMessage($_FILES[$field]['error']) . "\n<br>";
}
}
if ( isset($_POST[$field . $suffix]) || $_FILES[$field]['name'] ) {
if ( $value['type'] == 'password' && strlen($_POST[$field]) == 0 ) { // we don't update passwords unless they put something in
continue;
}
if ( $sql ) { $sql .= ','; } // put in a comma if we already have info in the $sql
$fieldList .= $field;
if ( $_POST[ $field . $suffix] ) {
if ( $_POST[$field . $suffix] == CONSTANT_NO_VALUE_DROPDOWN ) {
$sql .= $field . '= NULL';
} elseif ($value['type'] == 'password') {
$sql .= $field . '=' . 'md5(' . makeQuerySafe($_POST[$field . $suffix]) . ')';
} else {
$sql .= $field . "=" . makeQuerySafe($_POST[$field . $suffix]);
}
} else { // if no value entered on form, set it to null if possible, otherwise set it to an empty string
$sql .= $field . "=" . makeQuerySafe($fileName, $value['default'] === null || strtolower($value['default']) == 'null' );
}
}
}
// the sql created above needs some stuff added, do that here. We will put the "update" and table name, then
// limit based upon the id passed in.
$result = doSQL( 'update '. $tableInfo['table name'] . ' set ' . $sql . " where " . $tableInfo['key field'] . " = $id " );
if ($tableInfo['complex join']) { // we possibly had a change in the multi table
foreach ($tableInfo['complex join'] as $table => $value) {
$result .= doSQL ('delete from ' . $value['join table']['table name'] . ' where ' . $value['join table']['my link'] . '=' . $id );
$currentItem = $value['values table']['table name'];
$items = $_POST[$currentItem . $suffix];
$sql = '';
foreach ( $items as $item => $secondary_key ) {
$result .= doSQL(
'insert into ' . $value['join table']['table name'] . '(' .
$value['join table']['values link'] . ',' . $value['join table']['my link'] .
") values ($secondary_key, $id)"
);
}
} // foreach
} // if
// Now, if this table has children, let's see if function editDataTable was used to create a table of children to work with.
// if so, we will recursively call ourself to update those tables also.
if ( $tableInfo['child tables'] ) {
global $DATABASE_DEFINITION;
foreach ( $tableInfo['child tables'] as $tableName => $information ) {
$regex = CHILD_KEY_DELIMITER . CHILD_KEY_TAG . CHILD_KEY_DELIMITER . $tableName . CHILD_KEY_DELIMITER . '(\d+)';
$processed = array();
foreach ( $_POST as $parameter => $value) {
if ( preg_match( "/$regex/", $parameter, $returnValues ) ) {
//print_r($returnValues); print "<br />";
$keyField = $returnValues[1];
if ( ! $processed[$keyField] ) { // found a key we haven't processed yet
//print "Would update $tableName using key $keyField<br />\n";
//print "<pre>" ; print_r ($DATABASE_DEFINITION[$tableName]); print "</pre>\n";
updateData( $DATABASE_DEFINITION[$tableName], // process the row found
$keyField,
CHILD_KEY_DELIMITER . CHILD_KEY_TAG . CHILD_KEY_DELIMITER . $tableName . CHILD_KEY_DELIMITER . $keyField );
$processed[$keyField] = 1; // mark it as processed
}
}
}
// now, see if they added any records
$key = CHILD_KEY_DELIMITER . CHILD_KEY_TAG . CHILD_KEY_DELIMITER . $tableName . CHILD_KEY_DELIMITER . CHILD_KEY_INSERT_TAG;
foreach ( $DATABASE_DEFINITION[$tableName]['new record indicator'] as $columnName ) {
if ( $_POST[$columnName . $key] ) { // yes, at least one of them has been changed
insertData( $DATABASE_DEFINITION[$tableName], $key );
break;
} // if
} // foreach
} // foreach
} // if
return true;
}
/*
function is called after addData. This does the actual insert into the database
*/
function insertData ( $tableInfo, $suffix = '' ) {
$result = '';
$sql = '';
$fieldList = array();
$valueList = array();
$fileName = '';
foreach ($tableInfo['field info'] as $field => $value) {
$canBeNull = (strtolower($value['default']) == 'null');
if ( $value['type'] == 'file' && $_FILES[$field]['name'] ) {
$fileName = saveFilesUploaded( $field );
if ( ! $fileName ) {
$result .= "Error Saving File $field: " . FileErrorMessage($_FILES[$field]['error']) . "\n<br>";
}
}
if ( $_POST[$field . $suffix] || $_FILES[$field]['name'] ) {
$fieldList[] = $field;
if ( $_POST[$field . $suffix] ) {
if ( $value['type'] == 'password' ) {
$valueList[] = 'md5(' . makeQuerySafe($_POST[$field . $suffix], $canBeNull) . ')';
} else {
$valueList[] = makeQuerySafe($_POST[$field . $suffix], $canBeNull);
}
} else {
$valueList[] = makeQuerySafe($fileName, $canBeNull);
}
} elseif ($value['default'] ) { // we didn't have a value, so if there is a default let's use it.
$fieldList[] = $field;
$valueList[] = makeQuerySafe($value['default'], $canBeNull );
}
}
$sql = "Insert into " . $tableInfo['table name'] . '(' . implode(',',$fieldList) . ') values (' . implode(',', $valueList) . ')';
// print "<pre>$sql\n</pre>";
// return '';
$result = doSQL( $sql );
if ($tableInfo['complex join']) { // we possibly had a change in the multi table
foreach ($tableInfo['complex join'] as $table => $value) {
$result .= doSQL ('delete from ' . $value['join table']['table name'] . ' where ' . $value['join table']['my link'] . '=' . $id );
$currentItem = $value['values table']['table name'];
$items = $_POST[$currentItem . $suffix];
$sql = '';
foreach ( $items as $item => $secondary_key ) {
$result .= doSQL( 'insert into ' . $value['join table']['table name'] . '(' .
$value['join table']['values link'] . ',' . $value['join table']['my link'] .
") values ($secondary_key, $id)"
);
}
} // foreach
} // if
return $result;
}
function doAdmin() {
global $DATABASE_DEFINITION;
foreach ( $DATABASE_DEFINITION as $key => $field ) {
print '<li><a href=' . $_SERVER['PHP_SELF'] . '?currentdb=' . $field['table name'] . '>' . ($field['display name'] ? $field['display name'] : $field['table name']) . '</a></li>';
}
print '</ul>';
$currentDB = $_GET['currentdb'];
if (! $currentDB ) {
$currentDB = $_POST['currentdb'];
if (! $currentDB ) {
$currentDB = DEFAULT_TABLE;
}
}
$dbDisplayName = $DATABASE_DEFINITION[$currentDB]['display name'] ? $DATABASE_DEFINITION[$currentDB]['display name'] : $currentDB;
// load our two global parameters, check for get, then post
$id = $_GET['id'];
$mode = $_GET['mode'];
if (! $id ) { $id = $_POST['id']; }
if (! $mode ) { $mode = $_POST['mode']; }
$mode = escapeshellcmd( $mode );
$id = escapeshellcmd( $id );
if ( $mode=="add") {
Print '<h2>Add $dbDisplayName</h2>';
print addData( $DATABASE_DEFINITION[$currentDB] );
}
if ( $mode=="added")
{
print insertData( $DATABASE_DEFINITION[$currentDB] );
print "Record Added";
}
if ( $mode=="edit")
{
print "<h2>Edit $dbDisplayName</h2>";
print editData( $DATABASE_DEFINITION[$currentDB], $id );
}
if ( $mode=="edited") {
updateData( $DATABASE_DEFINITION[$currentDB], $id );
Print "$currentDB Updated!<p>";
}
if ( $mode=="remove") {
print deleteData( $DATABASE_DEFINITION[$currentDB], $id );
Print "$currentDB has been removed <p>";
}
Print "<h2>$dbDisplayName</h2><p>";
print makeList( $currentDB, $DATABASE_DEFINITION[$currentDB]['display query'], $DATABASE_DEFINITION[$currentDB]['display columns'], $DATABASE_DEFINITION[$currentDB]['key field'] );
}
/*
function will encrypt $message using the key whose fingerprint is $key_fingerprint
in the gpg store in $homedir.
To get the fingerprint of a key, execute
gpg --homedir $homedir --fingerprint (replacing $homedir with the .gpg directory)
returns the encrypted string, or false if there was an error
Ensure $homedir can be read by the web server
Note, this function requires the PHP interface to gpgme be installed. It is named
gnupg, and is available with the following command:
pecl install gnupg (then, install in php.ini as the instructions say)
Under Debian and Ubuntu, you must have the php dev and gpgme packages installed also
apt-get install php5-dev libgpgme11-dev libgpg-error-dev libgpgme11
Example:
print gpg_encrypt_information( '76DDD066339769A61F0FF8EEB9563752960C9534',
'just a test',
'/home/http/.gnupg' );
*/
function gpg_encrypt_information( $key_fingerprint, $message, $gpgdir = '' ) {
if (strlen($gpgdir) == 0 ) {
$gpgdir = GPGDIR;
}
putenv("GNUPGHOME=$gpgdir");
$res = gnupg_init();
//print "$res<br>\n";
if ( gnupg_addencryptkey($res,$key_fingerprint) ) {
$enc = gnupg_encrypt($res, $message);
return $enc;
} else { // we failed somewhere
print "Failed to find key in $homedir for key $key_fingerprint<br>\n";
return false;
}
} // function gpg_encrypt_information
// if worker_id is null and the file we are calling is not login,
// redirect to login
/*if ( ! $_SESSION['worker_id'] && basename($_SERVER['PHP_SELF']) != $LOGIN_PAGE) {
redirectPage($LOGIN_PAGE,array('message'=>'Session Timeout, Please Log In'));
}*/
?>