Subversion Repositories php_library

<?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

*/

   define(REVISION, '2.18');

   define( HTML_QUOTE_CHAR, '"' );

   define( CONSTANT_NO_VALUE_DROPDOWN, '--------' );

   $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' ) {

      if (strlen($value) == 0) { // simply set any empty values to null

         return 'null';

      }

      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 . "'";

      }

      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) . ')';

      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)

   */

      function queryDatabaseExtended($query,$assoc=1,$showErrors=true) {

      // print "<pre>In Query database\n---------------------\n$query\n---------------------\n</pre>";

      printLog( $query );

      $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

         audit($query);

         return $f;

      }

      $count = @mysql_num_rows($r);

      $fieldMeta = array();

      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 $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 = '';

   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);

      }

      $params = '?' . implode( '&', $params );

   }

   if ( ! $protocol ) {

      $protocol = isset($_SERVER['HTTPS']) ? "https:" : "http:";

   }

   $location = $protocol . '//' . $host . $path . '/' . $page . $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;