add_log_entry_functions.php

<?php 

// Functions to validate and insert new log entries

#############################
# Data Validation Functions #
#############################

# Validate Date Function
include_once("validation/validate_date.php");

function old_validate_date($date){
  // NOT USED (but it does actually work quite well)
   if ($date == "" ) { return '00:00:00' ; }
  # create temp table and attempt insert there
  $query = "CREATE TEMP TABLE validation (test date) ";
  $result = do_sql($query);

  # Check if date is valid format by entering it into temp table
  $query = "INSERT INTO validation (test) values ( '$date' )";
  $result = do_sql($query);

  if ( $result ) {
    do_sql("DROP TABLE validation") ;
    return "$date" ;
  }
  else {  
    do_sql("DROP TABLE validation") ;
    return false ;
  }
} 

# Validate Interval (time elapsed) Function
function validate_interval($interval){
   # if empty string then assume zero
   if ($interval == "" ) { return '00:00:00' ; }
  # create temp table and attempt insert there
  $query = "CREATE TEMP TABLE validation (test interval) ";
  $result = do_sql($query);

  $query = "INSERT INTO validation (test) values ( '$interval' )";
  $result = do_sql($query);

  if ( $result ) {
    do_sql("DROP TABLE validation") ;
    return "$interval" ;
  }
  else {  
    do_sql("DROP TABLE validation") ;
    return false ;
  }
}

function validate_boolean($bool){
   # if empty string then assume false
   if ($bool == "" ) { return true ; }
   $bool = strtolower($bool);

   $true = array("y","yes","t","true","n","no","f","false");
   if ( in_array($bool, $true ) ) {
     return true ;
   }

  if ( is_bool($bool) ) {
    return true ;
  }
  else {  
    return false ;
  }
}

# Validate distance function
function validate_distance($distance){
  # Take an entry and return the distance and units
   # if empty string then assume zero
   if ($distance == "" ) { return '0' ; }

  $miles = array('miles','mi');
  $metres = array('metres','m');
  $kmetres = array('kilometres','km');
  
  # example input: 12   12km3m  12km 3m
  # split by spaces or letters then identify units  
  preg_match_all("/(\d+[\.][\d]?|[\w]?)/", $distance, $matches );
  $parts=$matches[0];
  
    if (  preg_match("/\d+[\.][\d]?/", $part[0] ) ){
       $distance1= $part[0];
    };
  
  # create temp table and attempt insert there
  $query = "CREATE TEMP TABLE validation (test interval) ";
  $result = do_sql($query);

  $query = "INSERT INTO validation (test) values ( '$interval' )";
  $result = do_sql($query);

  if ( $result ) {
    do_sql("DROP TABLE validation") ;
    return "$interval" ;
  }
  else {  
    do_sql("DROP TABLE validation") ;
    return false ;
  }
}

function bad_data($column, $type, $entry ){
  #Format of a field is bad so return to the previous screen
  $_SESSION['message']="<b>Invalid $type format for $column field: $entry </b> ";
  #header("Location:add_log_entry.php");
  include("add_log_entry.php");
  exit();
}

#################################

function validate_form_data($details,$columns) {
  # Validate Form Data
  # check Date fields
  foreach ($details as $column) {
    # Get Form Data
    ${$column} = $columns[$column] ;

    # if a field has been left blank then remove it from the columns list
    # prevents attempts to add a null value to a not null field type 
    # (eg. integer).  Will have to add an extra check for mandatory fields tba.
    if ( "X${$column}" == "X" ){
      unset( $columns[$column] );
      # Skip the rest of this foreach loop
      continue ;
    }
    
    $query = "SELECT $column from log limit 1";
    #$result = pg_query($query) or die('Query failed: ' . pg_last_error());
    $result = do_sql($query) ;
    $type= pg_field_type($result,0);

    // Check some specific columns
    // Distance: if not set then set to zero
    switch($column){
      case "distance":
        if ( "X$columns[$column]" == "X" ) {
          $columns[$column] = 0 ;
        }
        #elseif ( preg_match("/\s*(\d+?\.?\d*?)\s*(\w*?)\s*/U", $columns[$column], $matches ) ) {
        elseif ( preg_match("/\s*(\d+\.?\d*)\s*(\w*)\s*/", $columns[$column], $parts ) ) {
	   #$parts = $matches[0];
           $units = array( "km","m","miles","mi" ) ;
           if ( in_array($parts[2], $units) ) {
             $columns[$column] = $parts[1] ; 
	     $columns["distance_units"] = $parts[2] ;
           }
           else { 
             $columns[$column] = $parts[1] ; 
           }
        }
      break;
    }

    // Check that all field entries are valid for their data type.
    switch($type){
      case "date":
        if ( $formatted_date = validate_date( ${ $column }, "eu" , "eu") ) { 
	  #${$column}=$formatted_date ;
	  $columns[$column] = $formatted_date ;
	} else {
	  bad_data($column, $type, $columns[$column] ) ; 
	}
	break;
      case "interval":
	if ( $formatted_interval = validate_interval( ${ $column } ) ) { 
	  #${$column}=$formatted_interval ;
	  $columns[$column] = $formatted_interval ;
	} else {
	  bad_data($column, $type , $columns[$column] ) ; 
	}
	break;
      case "bool":
	if ( "X$columns[$column]" == "X" ) {
	  // if blank then assume false.
	  $columns[$column] = "n" ;
	}
	elseif ( ! validate_boolean( ${ $column } ) ) { 
	  bad_data($column, $type , $columns[$column] ) ; 
	}
	break;
      case "int4":
        if ( (is_int(intval($columns[$column])) ) || ( $columns[$column] == 0 ) ) {
          $columns[$column] = intval($columns[$column]) ;
        } else {
	  bad_data($column, $type , $columns[$column] ) ; 
        }
	break;
      case "float4":
        if ( is_numeric($columns[$column]) ) {
          break;
        } else {
	  bad_data($column, $type , $columns[$column] ) ; 
        }
	break;
      case "text":
	# Might want to limit length here
	break;
    }
  }

  // return the array of corrected columns
  return $columns ;

## END OF FUNCTION
}

################
# MAIN Function - inserts row into log table
################
function insert_log_row($athlete_id,$details,$post_data) {
  // Find out who owns this session and check that this user
  // has permission to edit the owner's log
  $session_owner = $athlete_id ;
  $athlete_id = $_SESSION['athlete_id'];

  if ( $session_owner == $athlete_id ) { ; }
  elseif ( $session_owner != $athlete_id && check_share_permission($session_owner,"edit log $athlete_id") ) {
    $athlete_id = $session_owner ;
  }
  else {
    echo "You do not have permission to edit this athlete's log<br>";
    return false;
  }

  $columns = array();
  $columns = validate_form_data($details,$post_data);

  # Get next session id
  $query = "Select counter from session_counter";
  $result = do_sql($query) or die('Query failed: ' . pg_last_error());

  $session_counter = pg_fetch_array($result, null, PGSQL_ASSOC) ;
  $new_session_id =  $session_counter[counter] ;

  # Build insert query
  $query = "INSERT into log ( athlete_id, session_id, "; 
  #foreach ($details as $column){ 
  foreach ( array_keys($columns) as $column ){
	$query = "$query $column ,";
	}
  # remove final comma from query
  $query = substr($query,0, strlen($query)-1) ;

  $query = "$query ) VALUES ( $athlete_id, $new_session_id, ";
  #foreach ($details as $column){ 
  foreach ( array_keys($columns) as $column ){
	$query = "$query '$columns[$column]' ,";
	}
  # remove final comma from query
  $query = substr($query,0, strlen($query)-1) ;
  $query = "$query )";

  #echo "New entry added.<br>$query";

  # Insert new session using build INSERT query
  #echo "<br>DEBUG: $query <br>";
  $result = do_sql($query) or die('Query failed: ' . pg_last_error());

## END OF FUNCTION
}

function update_log_row($athlete_id,$details,$post_data) {
  $columns = array();
  $columns = validate_form_data($details,$post_data);
  $session_id = $columns['session_id'];

  // Find out who owns this session and check that this user
  // has permission to edit the owner's log
  $session_owner = get_session_owner( $session_id );
  
  if ( $athlete_id == $session_owner ) { ; }
  elseif ( $athlete_id != $session_owner && check_share_permission($session_owner,"edit log $athlete_id") ) {
    $athlete_id = $session_owner ;
  }
  else {
    echo "You do not have permission to edit this athlete's log<br>";
    return false;
  }

  # Build insert query
  $query = "UPDATE log SET ";
  #foreach ($details as $column){ 
  foreach ( array_keys($columns) as $column ){
	$entry = $columns[$column];
	$query = "$query $column = '$entry' ,";
	}
  # remove final comma from query
  $query = substr($query,0, strlen($query)-1) ;

  $query = "$query WHERE athlete_id = $athlete_id AND session_id = $session_id ";

  #echo "DEBUG Entry update query:<br>$query";

  # Update session using build UPDATE query
  $result = do_sql($query) or die('Query failed: ' . pg_last_error());

## END OF FUNCTION
}
?>