<?php
/**
 **	database_functions.inc
 **		function definition file
 **
 **	Original Author:	Robby Anderson
 **	Original Date:		March 2001
 **
 **	Modified By:
 **	Modified On:
 **	Modifications:
 **
 **	Contains the following functions:
 **		nextID
 **		formatSQL
 **		arrayToSQL
 **		listToSQL
 **		sysPass
 **/

	function nextID ( $tbl ) {

		// Create the nextID object and populate some of its values
		$nID = new objSQL;

		// The nextID function uses the 'system' connection type
		$nID->setConnection('system');


		// Create the first SQL statement. This locks the table and gets the
		// current value of next ID
		$thisSQL = array(
			"lock tables NEXT_ID write",
			"select NEXT_ID from NEXT_ID where NEXT_TABLE = '$tbl'"
		);

		// Turn the tran status off by turning the default off. This should make sure we don't issue any commits,
		// and make sure that the lock table stays locked until we unlock it
		$nID->defaultTranStatus = false;


		// Set and execute the statement
		$nID->setStatement($thisSQL);
		$nID->execute();


		// Move to the first item and retrieve the value. If we don't have a value,
		// then start at 1
		$thisID = ( $nID->first() ? $nID->field('NEXT_ID') : 1 );

		// Update the original value
		$nextID = $thisID + 1;

		// Add the new value and unlock the table
		$thisSQL = array(
			"replace into NEXT_ID ( NEXT_TABLE, NEXT_ID ) values ( '$tbl', '$nextID' )",
			"unlock tables"
		);


		// Set and execute the statement
		$nID->setStatement($thisSQL);
		$nID->execute();

		// close the connection
		//$nID->close();

		return $thisID;
	} // end function nextID


	function formatSQL ( $sqlValue, $sqlType, $args = '' ) {

		// Check to see if the value is empty. If it is and there is no default,
		// return NULL, otherwise, set the value to the default
		if ( $sqlValue === '' || $sqlValue === false ) {
			// If we did not received an arg (for a default), return NULL
			if ( !$args ) {
				return 'NULL';
			// Otherwise, set the value to the default and continue on
			} else {
				$sqlValue = $args;
			}
		}

		// Otherwise, the prop type to set the DB friendly value
		switch ( $sqlType ) {
			case 'boolean':
				// Booleans in the database are tinyints (0 or 1)
				return $sqlValue ? 1 : 0;
				break;
			case 'dateobj':
				// we're going to assume we've been passed a valid object,
				// so all we have to do is to call the value command and stick
				// then return this as a properly formatted string
				return formatSQL($sqlValue->value('dbdate'), 'string');
				break;
			case 'dateobjshort':
				// we're going to assume we've been passed a valid object,
				// so all we have to do is to call the value command and stick
				// then return this as a properly formatted string
				return formatSQL($sqlValue->value('dbdateshort'), 'string');
				break;
			case 'timestamp':
				// with a timestamp, we have to create the date object, and then
				// return the formatted date object
				$dobj = new objDate();
				$dobj->setFromTS($sqlValue);
				return formatSQL($dobj,'dateobj');
				break;
			case 'arraystring':
				// replace single quotes with escaped values
				$sqlValue = preg_replace("/'/", "''", $sqlValue);
				return arrayToSQL($sqlValue, 'string');
				break;
			case 'arrayinteger':
				return arrayToSQL($sqlValue, 'integer');
				break;
			case 'string':
			default:
				// replace single quotes with escaped values and delimit the return value
				$sqlValue = str_replace("'", "''", $sqlValue);
				$sqlValue = str_replace('\\', '\\\\', $sqlValue);
				$sqlValue = trim($sqlValue);

				return "'$sqlValue'";
				break;

				// Old default - using the same as string now
				//return $sqlValue;
				//break;
		}
	} // end function formatSQL


	function listToSQL ( $sqlList, $type ) {
		return arrayToSQL(preg_split('/ /',$sqlList),$type);
	}


	function arrayToSQL ( $sqlArray, $type ) {

		// Clear the sqlOut var
		$sqlOut = '';

		// Assign a delimter character. This is used if the list type is 'string'
		if ( $type == 'string' ) {
			$delim = "'";
		} else {
			$delim = "";
		}

		// Loop through each array item, adding it to sqlOut. If sqlOut isn't empty, add a
		// comma before adding the array value
		foreach ( $sqlArray as $thisItem ) {
			if(!is_object($thisItem)){
				if ( $sqlOut ) { $sqlOut .= ', '; }
				if ( $type == 'string' ) { $thisItem = str_replace("'","''",$thisItem); }

				$sqlOut .= $delim.$thisItem.$delim;
			}
		}

		// Return the final sql-ready list
		return $sqlOut;
	}


?>