<?php
//========================================================================
// XPS
//========================================================================

class Database 
{

    private $conn = ''; //PDO conntection
    private $dsn;
    private $user;
    private $password;
    private $db_type;
    private $prefix;
	private $trans = true;
	public  $ERROR = '';

    function __construct($dsn='', $user='', $password='', $db_type='', $prefix='') 
	{
        $this->dsn = $dsn;
        $this->user = $user;
        $this->password = $password;
        $this->db_type = $db_type;
        $this->prefix = $prefix;
		$this->connect();
	}

    private function connect() 
	{
        try 
		{
            $this->conn = new PDO($this->dsn, $this->user, $this->password);
            $this->conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);  //throws any error as an exception
        } 
		catch (PDOException $e) 
		{
            $this->ERROR.=  $e->getMessage() . '<br/>';
            //die($this->ERROR);
        }
    }

    function disconnect() 
	{
        $this->conn = null;
    }

	/**
	* Does an INSERT, UPDATE, or DELETE on the database
	*
	* @access public
	* @param $statement the PDO prepared statement
	* @author Brian Cook <hide@address.com>
	* @return boolean
	*/
    private function execute($statement) 
	{
    	try 
		{
	        // if statement is a string it needs to be "prepared"
	        $this->conn->beginTransaction();
			
			if(is_string($statement)) 
			{
	            $statement = $this->conn->prepare($statement);			
	        } 	        
			$statement->execute();
			$this->conn->commit();
	        return true;
        }
		catch (PDOException $e) 
		{
			echo $e->getMessage() . '<br/>';
			$this->conn->rollBack();
			
			return false;
		}
    }

	/**
	* Does a SELECT statement on the database
	*
	* @access public
	* @param $statement the PDO prepared statement
	* @author Brian Cook <hide@address.com>
	* @return $recordset the SELECTed recordset.
	*/
    private function select($statement) 
	{
        //returns recordset
        try 
		{
            if(is_string($statement)) 
			{ //SQL
                $statement = $this->conn->prepare($statement);
            }
            $statement->execute();
            return $statement->fetchAll();
        } 
		catch (PDOException $e) 
		{
            $this->ERROR.= $e->getMessage().'<br />';
            return false;
        }
    }

	/**
	* Retrieves an SQL statement from an XML file.
	*
	* @access private
	* @param $id the identification value for the SQL statement.
	* @author Brian Cook <hide@address.com>
	* @return $sql_statement_node->item(0)->nodeValue the SQL statement.
	*/
	private function get_sql_statement($id, $file_location)
	{
		$dom = new DOMDocument;
		$dom->preserveWhiteSpace = false;
		
		if($file_location == 'core')
		{
			$dom->load(CONFIGS . '/sql_statements.xml');
		}
		else
		{
			$dom->load(MODULES . '/' . $file_location . '/configs/sql_statements.xml');
		}
			
		$xpath = new DOMXPath($dom);
			
		$sql_statement_node = $xpath->query('//sql_statement[@id="'.$id.'"]/sql[@db_lang="'.$this->db_type.'"]');
				
		if((count($sql_statement_node) <= 0) or ($sql_statement_node->item(0) == NULL))
		{
				$sql_statement_node = $xpath->query('//sql_statement[@id="'.$id.'"]/sql[@db_lang="default"]'); 		 
		}
				
		switch($this->db_type)
		{
		 	case 'oracle':
		 		$replacement = $this->prefix . '.';
		 		break;
		 		
		 	default:
		 		$replacement = '';
				break;		 
		}
				
		if(count($sql_statement_node) > 0)
	 	{
			
			$sql = $sql_statement_node->item(0)->nodeValue;
			return str_replace('{table_prefix}', $replacement, $sql);
	 	}
	 	else
	 	{
	 	 	//return //insert code to get error message here
	 		return false; 
	 	}
	}    

    /**
	* Prepares a SQL statement to be executed by PDO
	*
	* @access public
	* @param $sql The SQL statement
	* @param $params An array of parameters to bind to the values in the SQL statement
	* @author heidtc <hide@address.com>, Brian Cook <hide@address.com>
	* @return $statement The prepared statement
	*/	
	private function get_prepared_statement($sql, $params=array())
	{
	 	$statement = $this->conn->prepare($sql);

	 	foreach($params as $key => $val)
	 	{
			$statement->bindParam(':'.$key, $$val);//the double $ is on purpose
	 	}
	
		foreach($params as $val)
	 	{
			$$val = $val;//the double $ is on purpose
	 	}

		return $statement;
	}
	
	/**
	* Retrieves a count() for the number of records that would be found by executing a SQL statement
	*
	* @access public
	* @param $id The identification of the SQL statement stored in an XML file
	* @param $params An array of parameters to bind to the values in the SQL statement
	* @author Brian Cook <hide@address.com>
	* @return $rs[0]['RECORD_COUNT'] The integer value for the count.
	*/
	public function get_count($file_location, $params=array())
	{
		$sql = $this->get_sql_statement('count_table', $file_location);

		$new_sql = str_replace('{table_name}', $params['table_name'], $sql);

		$statement = $this->get_prepared_statement($new_sql, $params);
		$rs = $this->select($statement);

		return $rs[0]['SOME_COUNT'];
	}
	
	/**
	* Gets a recordset.
	*
	* @access public
	* @param $id the identification value for the SQL statement.
	* @param $params an associative array of table names for keys with values.
	* @author Brian Cook <hide@address.com>
	* @return recordset the database recordset.
	*/
	public function get_recordset($id, $file_location, $params=array())
	{

		$sql = $this->get_sql_statement($id, $file_location);
		
		if((array_key_exists('LIMIT', $params)) and (array_key_exists('OFFSET', $params)))
		{
			$sql = $this->set_offset($sql, $params);
			unset($params['LIMIT'], $params['OFFSET'], $params['ORDER_BY']);		 
		}

		$statement = $this->get_prepared_statement($sql, $params);

		$recordset = $this->select($statement);
		
		$recordset = $this->format_recordset($recordset);

		return $recordset;
	}
	
	/**
	* Executes a predefined SQL statement.
	*
	* @access private
	* @param $id the identification value for the SQL statement.
	* @param $params an associative array of table names for keys with values.
	* @author Brian Cook <hide@address.com>
	* @return boolean true if no errors and false if error encountered.
	*/
	public function execute_sql($id, $file_location, $params=array())
	{
		$db_lang = '';
		$sql = $this->get_sql_statement($id, $file_location);
		$params = $this->format_params($params);		
		$statement = $this->get_prepared_statement($sql, $params);
		$bool = $this->execute($statement);

		return $bool;
	}

	//this might be used for file i/o
	public function fetch_statement($id, $file_location, $params=array())
	{
	 	$sql = $this->get_sql_statement($id, $file_location);

		$statement = $this->get_prepared_statement($sql, $params);
	 	
	 	$bool = $this->execute($statement);
	 	$row = $statement->fetch();
	 	$output = stream_get_contents($row[0]);
	 
	 	return $output;	
	 }

	/*************************************
	* Retrieves the x,y coordinates of a widget
	*
	* @access	public
	* @param	$params['WIDGET_ID'] the id of the widget
	* @author	Brian Cook <hide@address.com>
	* @return	$position the x,y coordinates of the widget
	*************************************/
	 public function get_position($params=array())
	 {
	 	$position = array();
		 
		$sql = $this->get_sql_statement('get_position', 'core');

		$statement = $this->get_prepared_statement($sql, $params);
		
		$coords = $this->select($statement);
		$position['x'] = $coords['POSITION_X'];
		$position['y'] = $coords['POSITION_Y'];
		
		return $position;
	 }

	/*************************************
	* Sets the x,y coordinates of a widget
	* 
	* @access	public
	* @param	$params['WIDGET_ID'] the id of the widget
	* @param	$params['POSITION_X'] the x coordinate of the widget
	* @param	$params['POSITION_Y'] the y coordinate of the widget
	* @return	boolean
	*************************************/
	public function set_position($params=array())
	{
		$sql = $this->get_sql_statement('set_position', 'core');
		
	 	$statement = $this->get_prepared_statement($sql, $params);
	 	
	 	$bool = $this->execute($statement);
	 	
	 	return $bool;
	}

	/*************************************
	* Sets the offset for page numbering
	* 
	* @access	public
	* @param	$params['LIMIT'] the limit of items per page
	* @param	$params['OFFSET'] determines current page being viewed
	* @return	modified $sql
	*************************************/
	public function set_offset($sql, $params)
	{
		$new_sql = str_replace('{LIMIT}', $params['LIMIT'], $sql);
		$new_sql = str_replace('{OFFSET}', $params['OFFSET'], $new_sql);
		$new_sql = str_replace('{ORDER_BY}', $params['ORDER_BY'], $new_sql);
		
		return $new_sql;
	}
	
	/*************************************
	* Strips html markup from params before inserting into a database
	*
	* @access	private
	* @author	Brian Cook < hide@address.com >
	* @param	$params an array of values to be bound in a sql statement
	* @return	$params the modified params array
	*************************************/
	private function format_params($params)
	{
		foreach($params as $key => $val)
		{
		 	$params[$key] = htmlentities($val);
		}
		return $params;
	}
	
	/*************************************
	* Converts all recordset HTML entities to their applicable characters
	*
	* @access	private
	* @author	Brian Cook < hide@address.com >
	* @param	$recordset a list of records returned from the database
	* @return	$recordset the modified recordset array
	*************************************/
	private function format_recordset($recordset)
	{
		if(is_array($recordset))
		{
			foreach($recordset as $key => $val)
			{
				foreach($val as $key1 => $val1)
				{
			 		$recordset[$key][$key1] = html_entity_decode($val1);
				}
			}
		}
		return $recordset;
	}
	
	public function execute_sql_line($sql)
	{
	 	$statement = $this->get_prepared_statement($sql, array());
		return $this->execute($statement);
	}
}
?>