commonPhpFunctions.i

<?php
/* File: functions.php  
 * Purpose: There are multiple functions here that help out data.php.  
 * SPB-- created to reduce redundant code looping through resultsets and validating data.
 */

/* For running any single non-select queries.  It rolls back if there are any errors.
 * Parameters: String $sql-- SQL to run, Database $db_con-- database connection to run SQL with
 * Returns: Array returnArray.sql-- there is only one sql to run but it wraps it in an array for consistency, 
 * Array returnArray.error-- there also (probably) be at most one error but wrapped in an array.
 */
function runExecStatement($sql, $dbh) 
{
    $errors= array();
    $sth = db_query($sql,$dbh);
    $newError= trim(db_last_error());
    if ($newError != "")
        $errors[]= $newError;

    db_free_result($sth);

    return array("sql" => array($sql), "error" => $errors);
}

/* For running a group of update, insert, or delete statements that must be all executed or none executed.
 * Parameters: Array $sqls-- list of SQL to run, Database $db_con-- database connection to run SQL with
 * Returns: Array returnArray.sql-- there is only one sql to run but it wraps it in an array for consistency, 
 * Array returnArray.error-- there also (probably) be at most one error but wrapped in an array.
 */
function runTransaction($sqls, $dbh)
{
    $errors= array();
    
    db_query("begin", $dbh);
    foreach($sqls as $sql)
    {
        db_query($sql, $dbh);
        $error= trim(db_last_error());
        if ($error != "")
        {
            $errors[]= $error;
            db_query("rollback", $dbh);
            break;
        }    
    }
    db_query("commit", $dbh);
    
    return array("sql" => $sqls, "error" => $errors);
}

/* For running a select statement where the result is needed.
 * Parameters: String $sql-- select statement to run, Database $dbh-- database handle, 
 *      Array $translationArray-- Associative array from the actual column name to the alias name in the corresponding grid to populate (anything returned by the query but not in this array is lost),
 *      Array (optional) $changeToBooleanArray-- Associative array from the actual column name to a value that is true (typically "Y").  If column is this value, then return true, else false
 * Returns: Array returnArray.sql-- the SQL wrapped in an array, Array returnArray.error-- any errors from execution, 
 *      Array returnArray.record-- dataset with column names from the $translationArray and values either 1) trimmed strings from the table column or 2) booleans if column in the booleanArray.
 */
function runSelectStatement($sql, $dbh, $translationArray, $changeToBooleanArray= array())
{
    $errors= array();
    $sth = db_query($sql,$dbh);
    $newError= trim(db_last_error());
    $gridData= array();
    if ($newError != "") 
    {
        $errors[]= $newError;
        db_free_result($sth);
    }
    else
    {
        $gridRow= array();
        $i= 0;
        while($dRecord = db_fetch_assoc($sth, $i++))
        {
            foreach($translationArray as $column => $value)
            {
                $columnValue= trim($dRecord[$column]);
                if (isset($changeToBooleanArray[$column]))
                    $columnValue= $columnValue == $changeToBooleanArray[$column];
                $gridRow[$value]= $columnValue;
            }
            $gridData[]= $gridRow;
        }
        db_free_result($sth);
    }
    
    return array("sql" => array($sql), "error" => $errors, "record" => $gridData);    
}

// This function will return a map with key => value where key is the first column in the select statement and value is the second column in the select statement.
function runSelectStatementMap($sql, $dbh)
{
    $errors= array();
    $sth = db_query($sql,$dbh);
    $newError= trim(db_last_error());
    $gridData= array();
    if ($newError != "") 
    {
        $errors[]= $newError;
        db_free_result($sth);
    }
    else
    {
        $gridRow= array();
        $i= 0;
        while($dRecord = db_fetch_row($sth, $i++))
        {
            $gridData[$dRecord[0]]= $dRecord[1];
        }
        db_free_result($sth);
    }
    
    return array("sql" => array($sql), "error" => $errors, "record" => $gridData);    
}


/* Function to truncate string to the maxlength and escape characters that will fail Postgres.  Copied from hcuScripts/hcuinc -> db_func.php
 * Parameters: String $text-- string to prepare, Number (optional) $msize-- if specified, maxlength to truncate string at; else no limit ("text" postgres type)
 * Returns: String $text-- string truncated at the max character amount and escaped for Postgres.
 */
function prepSave($text, $msize=0) 
{
    if ($msize > 0)
        $text = substr($text, 0, $msize);
    $text = pg_escape_string(trim($text));
    return $text;
}

/* Parameters: String $text-- string to prepare
 * Returns: String $text-- if "", text isn't a valid phone number so it is cleared; else, text is a valid phone number and changed to the format 123-456-7890
 */
function cleanPhone($text)
{
    $text= trim($text);
    $normalPat= '/^[1-9]\d{2}-\d{3}-\d{4}$/'; // 123-456-7890
    $pats= array();
    $pats[]= '/^[1-9]\d{9}$/'; // 1234567890
    $pats[]= '/^\(\s*[1-9]\d{2}\s*\)\s*\d{3}\s*-\s*\d{4}$/'; // (123 )  456- 7890
    $pats[]= '/^[1-9]\d{2}\s*-\s*\d{3}\s*-\s*\d{4}$/'; // 123- 456 -  7890
    $pats[]= '/^[1-9]\d{2}\s*\.\s*\d{3}\s*\.\s*\d{4}$/'; // 123. 456 .  7890
    $patReplace= '/[\s\.\-\(\)]*/';
    if (preg_match($normalPat, $text))
        return $text;
    if (preg_match($pats[0], $text))
        return substr($text, 0,3) . "-" . substr($text, 3,3) . "-" . substr($text, 6,4);
    if (preg_match($pats[1], $text) || preg_match($pats[2], $text) || preg_match($pats[3], $text))
    {
        $text= preg_replace($patReplace, "", $text);
        $text= substr($text, 0,3) . "-" . substr($text, 3,3) . "-" . substr($text, 6,4);
        return $text;
    }
        
    return "";
}

/* Parameters: String $text-- string to prepare, Number (optional) $maxlength-- if set, truncate email to maxlength first and test regex
 * Returns: String $text-- if "", text isn't a valid email so it is cleared; else, truncated text is a valid email and returned
 */
function cleanEmail($text, $maxlength=0)
{
    $text= trim(prepSave($text, $maxlength));
    
    /* This cannot contain any whitespace.  Only one @ symbol is valid in the email.  
     * Periods cannot be directly behind or in front of the @.  They must separate non-whitespace characters and cannot appear twice in a row.
     * There must be at least one period after the @ symbol.  The domain extension (after the last period) has to be more than two characters.
     */
    $pattern= '/^[^\s@\.]+(\.[^\s@\.]+)*@[^\s@\.]+(\.[^\s@\.]+)*\.\w{2,}$/'; // domain extension: {2,} is 2 or more chars
    if (preg_match($pattern, $text))
        return $text;
    return "";
}

/* Parameters: String $text-- string to prepare
 * Returns: String $text-- if "", text isn't a valid date so it is cleared; else, date is valid and returned in the format Y-m-d e.g. 2015-01-01.
 */
function cleanDate($text)
{
    $text= trim($text);
    if (preg_match('/^\d{8}$/', $text)) // in the case of yyyyMMdd (see login's BakeCookie).
        $text= substr($text, 0, 4) . "-" . substr($text, 4,2) . "-" . substr($text, 6,2);
    if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $text))
        return "";
    $timestamp= date_create_from_format('Y-m-d', $text);
    if ($timestamp == false)
        return "";
    return $text;
}

/* Parameters: String $text-- string to prepare
 * Returns: String $text-- if "", text isn't a valid time so it is cleared; else, time is valid and returned in the format H:m.
 */
function cleanTime($text)
{
    $text= trim($text);
    if (preg_match('/^\d{2}:\d{2}(:\d{2})?$/', $text)) // Changed so that the seconds are optional
    {
        $hours= intval(substr($text, 0,2));
        $minutes= intval(substr($text, 3,2));
        if ($hours >= 0 && $hours <= 23 && $minutes >= 0 && $minutes <= 59)
            return $text;
    }
    return "";
}

/* Parameters: String $text-- string to prepare, String (optional) $subtype-- if "month", between 0 and 12 is valid, if "year", either 4 digits or 2 digits is valid,
 *      Boolean (optional) $allowNegatives-- if true, integers < 0 are legal
 * Returns: String $text-- if "ERROR", text isn't a valid number is it is cleared; else, number is returned.
 */
function cleanInteger($text, $allowNegatives= false, $subtype= "")
{
    $text= trim($text);
    $pattern= '/^[+-]?\d+$/';
    if (preg_match($pattern, $text))
    {
        $number= intval($text);
        if (!$allowNegatives && $number < 0)
            return "ERROR";
        if (trim($subtype) != "")
            switch($subtype)
            {
                case "month": 
                    if ($number >= 0 && $number <= 12)
                        return $number;
                    else
                        return "ERROR";
                case "year":
                    if ($number >= 1000 && $number <= 9999)
                        return $number;
                    else if ($number >= 1 && $number <= 99)
                        return 2000+$number;
                    else 
                        return "ERROR";
                default:
                    return "ERROR";
            }
        return strval($number);
    }
    return "ERROR";  
}

/* Parameters: String $text-- string to prepare
 * Returns: String $text-- if "ERROR", text isn't a valid number is it is cleared; else, number is returned.
 */
function cleanNumber($text)
{
    $text= trim($text);
    $pattern= '/^[+-]?\d+\.?\d*$/';
    if (preg_match($pattern, $text))
        return strval($text);
    return "ERROR";
}

/* Parameters: String $text-- the text to clean, String (optional) $firstValue-- what to store a TRUE value into the database, 
 *      String (optional) $secondValue is what to store a FALSE value into the database.
 * Returns: String $text-- if either $firstValue or $secondValue, returns itself; else if $required is true, returns ""; else returns $secondValue
 */
function cleanBoolean($text, $required, $firstValue = "Y", $secondValue = "N")
{
    $text= trim(strtoupper($text));
    $firstValue= trim(strtoupper($firstValue));
    $secondValue= trim(strtoupper($secondValue));
    if ($text == $firstValue || $text == $secondValue)
        return $text;
    
    if ($required)
        return "";
    else
        return $secondValue;
}

/* Goes through parameter list ($parameters) and validates it against array $validation.  Formats to what the database expects (if able).
 * Outputs a list of errors.  $validation looks like $validation[name]= array("required" => (true/false), "type" => (string));
 * Type can be one of the following: string, phone, email, date, int, and boolean.  An "maxlength" attribute is required for string and email.
 * Boolean takes in two more (optional) parameters: "firstValue" and "secondValue".
 */
function cleanValuesForDatabase(&$parameters, $validation)
{  
    $errors= array();
    
    if (!is_array($parameters))
        $errors[]= "Expected an array but parameters is not an array.";
    if (!is_array($validation))
        $errors[]= "Expected an array but validation is not an array.";
    
    if (count($errors) == 0)
        foreach($parameters as $key => $value)
        {
            if (!isset($validation[$key]))
                continue;
            
            $record= $validation[$key];
            if (!isset($record["required"]) || !isset($record["type"]))
            {
                $errors[]= 'validation record for ' . $key . ' is malformed.  "required" and "type" attributes are required.';
                continue;
            }

            if (trim($value) == "")
            {
                $parameters[$key]= "";
                if ($record["required"] == true)
                    $errors[]= "$key is required.";  
                else if ($record["type"] == "number")
                    $parameters[$key]= 0;
                continue;
            }

            switch ($record["type"])
            {
                case "string":
                    if (isset($record["maxlength"]))
                        $parameters[$key]= prepSave($value, intval($record["maxlength"]));
                    else
                        $parameters[$key]= prepSave($value);
                    break;
                case "phone":
                    $phone= cleanPhone($value);
                    if ($phone == "")
                        $errors[]= "$key has an invalid phone format.";
                    $parameters[$key]= $phone;
                    break;
                case "email":
                    if (isset($record["maxlength"]))
                        $email= cleanEmail($value, intval($record["maxlength"]));
                    else
                        $email= cleanEmail($value);
                    if ($email == "")
                        $errors[]= "$key has an invalid email format.";
                    $parameters[$key]= $email;
                    break;
                case "date":
                    $date= cleanDate($value);
                    if ($date == "")
                        $errors[]= "$key has an invalid date format.";
                    $parameters[$key]= $date;
                    break;  
                case "int":
                case "integer":
                    $allowNegatives= isset($record["allowNegatives"]) && $record["allowNegatives"];
                    if (isset($record["subtype"]))
                        $number= cleanInteger($value, $allowNegatives, $record["subtype"]);
                    else
                        $number= cleanInteger($value, $allowNegatives);
                    if ($number == "ERROR")
                        $errors[]= "$key has an invalid integer format.";
                    $parameters[$key]= $number;
                    break; 
                case "number":
                    $number= cleanNumber($value);
                    if ($number == "ERROR")
                        $errors[]= "$key has an invalid number format.";
                    $parameters[$key]= $number;
                    break;
                case "boolean":
                    if (isset($record["firstValue"]) && isset($record["secondValue"]))
                        $boolean= cleanBoolean($value, $record["required"], $record["firstValue"], $record["secondValue"]);
                    else
                        $boolean= cleanBoolean($value, $record["required"]);
                    if ($boolean == "")
                        $errors[]= "$key has an invalid boolean format.";
                    $parameters[$key]= $boolean;
                    break;  
                case "time":
                    $time= cleanTime($value);
                    if ($time == "")
                        $errors[]= "$key has an invalid time format.";
                    $parameters[$key]= $time;
                    break;                                     
                default: 
                    $parameters[$key]= "";
                    $errors[]= "$key doesn't have an appropriate type.";
            }
        }
    return $errors;
}

function dbErrorNoFormatting() {
    global $dbplat;
    switch ($dbplat)
    {
        case "Sybase":
            return @sybase_get_last_message();
        case "MySql":
            return @mysql_error();
        case "Postgres":
            return @pg_errormessage();
    }
}