<?php
include_once 'LFG_Model.class.php';

class LFG_Model_MySQL extends LFG_Model {
	
    private $database_host;
    private $database_user;
    private $database_pwd;
    private $database_name;
    private $database_conn;
        
	public function __construct($host, $user, $password, $name) {
        $this->database_host = $host;
        $this->database_user = $user;
	    $this->database_pwd = $password;
	    $this->database_name = $name;
	    $this->database_conn = null;
	}
	
	public function __destruct() {
        if ( !is_null($this->database_conn) ) {
        	$this->database_conn = null;
        }
	}
	
    private function _build_where_clause($type, $values, $anded) {
        $this->_connect();

        $where_clause = "";
        $operator = ( $anded ) ? " AND " : " OR ";
        foreach( $values as $name => $value ) {
            if ( !in_array($name, LFG_Model::$Properties[$type]) ) {
                LFG_Log("ERROR", "property [$name] does not exist in table [$type]");
            }
            $where_clause .= ( is_null($value) )
                ? $name . " IS NULL" . $operator
                : $name . " = '" . mysql_real_escape_string($value, $this->database_conn) . "'" . $operator;
        }
        if ( strlen($where_clause) > 0 ) {
            $where_clause = " WHERE " . substr($where_clause, 0, strlen($where_clause) - strlen($operator));
        }
        return $where_clause;
    }
    
	private function _connect() {
    	if ( !isset($this->database_conn) ) {
            $this->database_conn = @mysql_connect($this->database_host, $this->database_user, $this->database_pwd);
            if ( $this->database_conn == 0 ) {
            	$this->database_conn = null;
                LFG_Log(LFG_ERROR_INTERNAL, "", mysql_error());
            }
            else if ( mysql_select_db($this->database_name, $this->database_conn) == false ) {
                $error_msg = mysql_error($this->database_conn);
                $this->database_conn = null;
                LFG_Log(LFG_ERROR_INTERNAL, "", $error_msg);
            }
        }
    }

    private function _create_object($record_set, $fields, $prefix = "") {
    	$object = array();
    	foreach ($fields as $field) {
    		$object[$field] = ( array_key_exists($field, $record_set) ) ? $record_set[$field] : null;
    	}
    	return $object;
    }

    private function _query($query, $expected_results = -1, $is_select = true) {
        $this->_connect();

        $query_object = mysql_query($query, $this->database_conn);
        if ( $query_object == 0 ) {
            LFG_Log("INTERNAL", "", "SQL: [$query], Error: ".mysql_error($this->database_conn));
        }
        $amount_results = ( $is_select )
            ? mysql_num_rows($query_object)
            : mysql_affected_rows($this->database_conn);
        if ( $expected_results >= 0 && $expected_results != $amount_results ) {
            if ( $is_select) {
               mysql_free_result($query_object);
            }
            LFG_Log("INTERNAL", "", "expected $expected_results, got $amount_results with query: [$query]");
        }
        
        return ( $is_select ) ? $query_object : $amount_results;
    }

    private function _query_to_object($type, $object) {
        $result = array();
        while ( $row = mysql_fetch_assoc($object) ) {
        	$result[] = $row;
        }        
        mysql_free_result($object);
        return $result;
    }
    
    public function add($type, $args) {
    	$this->_connect();
        $names = "";
        $values = "";
        foreach ($args as $name => $value)
        {
            $names .= ", $name";
            $values .= ", '".mysql_real_escape_string($value)."'";
        }
        $names = substr($names, 2);
        $values = substr($values, 2);
        $this->_query("INSERT INTO ".$this->database_name.".".LFG_Model::$Names[$type]." ($names) VALUES ($values)", 1, false);
    }
    
    public function delete($type, $id) {
        $this->_query("DELETE FROM $this->database_name.".LFG_Model::$Names[$type]." WHERE id = $id", 1, false);
    }
    
    public function edit($type, $id, $args) {
        $phrase = "";
        foreach ( $args as $name => $value ) {
            if ( !in_array($name, LFG_Model::$Properties[$type]) ) {
                LFG_Log(LFG_ERROR_INTERNAL, '', "invalid property for [$type]: [$name]");
            }
            $phrase .= ", $name = '$value'";
        }
        if ( strlen($phrase) == 0 ) {
            LFG_Log(LFG_ERROR_INTERNAL, '', 'no parameters to update');
        }
        $phrase = substr($phrase, 2);
        $this->_query("UPDATE $this->database_name.".LFG_Model::$Names[$type]." SET $phrase WHERE id = $id", 1, false);
    }
    
    public function get($type, $args, $expected_results = -1, $anded = false) {
        $query = $this->_query("SELECT * FROM $this->database_name.".LFG_Model::$Names[$type].$this->_build_where_clause($type, $args, $anded), $expected_results, true);
        return $this->_query_to_object($type, $query);
    }

    public function getHierarchyItem($type, $sub_type, $id) {
        // Returned array will contain elements as such:
        //
        // [0] = id
        // [1] = type
        // [2] = name
        // [3] = sub_type
        // (every other element is a list element, 1st item is a list)
        $query_object = 0;
        $result = array();
        $result[] = array(0, LFG_GAME, "", "0");
        switch ( $type ) {
            case LFG_GAME:

                $query_object = $this->_query("SELECT name, id FROM $this->database_name.games WHERE games.id = $id", 1);
                $query_result = mysql_fetch_row($query_object);
                $result[] = array($query_result[1], LFG_GAME, $query_result[0], "0");
                break;
                
            case LFG_SERVER:

                $query_object = $this->_query("SELECT servers.name, servers.id, games.name, games.id FROM $this->database_name.servers, $this->database_name.games WHERE servers.id = $id AND games.id = servers.game", 1);
                $query_result = mysql_fetch_row($query_object);
                $result[] = array($query_result[3], LFG_GAME, $query_result[2], "0");
                $result[] = array($query_result[3], LFG_SERVER, "", "0");
                $result[] = array($query_result[1], LFG_SERVER, $query_result[0], "0");
                break;

            case LFG_CLASS:

                $query_object = $this->_query("SELECT classes.name, classes.id, games.name, games.id FROM $this->database_name.classes, $this->database_name.games WHERE classes.id = $id AND games.id = classes.game", 1);
                $query_result = mysql_fetch_row($query_object);
                $result[] = array($query_result[3], LFG_GAME, $query_result[2], "0");
                $result[] = array($query_result[3], LFG_CLASS, "", "0");
                $result[] = array($query_result[1], LFG_CLASS, $query_result[0], "0");
                break;

            case LFG_ZONE:

                $query_object = $this->_query("SELECT zones.name, zones.id, games.name, games.id FROM $this->database_name.zones, $this->database_name.games WHERE zones.id = $id AND games.id = zones.game", 1);
                $query_result = mysql_fetch_row($query_object);
                $result[] = array($query_result[3], LFG_GAME, $query_result[2], "0");
                $result[] = array($query_result[1], LFG_ZONE, "", "0");
                $result[] = array($query_result[1], LFG_ZONE, $query_result[0], "0");
                break;

            case LFG_GUILD:

                $query_object = $this->_query("SELECT guilds.name, guilds.id, servers.name, servers.id, games.name, games.id FROM $this->database_name.guilds, $this->database_name.servers, $this->database_name.games WHERE guilds.id = $id AND servers.id = guilds.server AND games.id = servers.game", 1);
                $query_result = mysql_fetch_row($query_object);
                $result[] = array($query_result[5], LFG_GAME, $query_result[4], "0");
                $result[] = array($query_result[5], LFG_SERVER, "", "0");
                $result[] = array($query_result[3], LFG_SERVER, $query_result[2], "0");
                $result[] = array($query_result[3], LFG_GUILD, "", "0");
                $result[] = array($query_result[1], LFG_GUILD, $query_result[0], "0");
                break;

            case LFG_CHARACTER:

                switch ( $sub_type ) {
                    case LFG_SERVER:
                        
                        $query_object = $this->_query("SELECT characters.name, characters.id, servers.name, servers.id, games.name, games.id FROM $this->database_name.servers, $this->database_name.games WHERE characters.id = $id AND servers.id = characters.owner AND games.id = servers.game", 1);
                        $query_result = mysql_fetch_row($query_object);
                        $result[] = array($query_result[5], LFG_GAME, $query_result[4], "0");
                        $result[] = array($query_result[5], LFG_SERVER, "", "0");
                        $result[] = array($query_result[3], LFG_SERVER, $query_result[2], "0");
                        $result[] = array($query_result[3], LFG_CHARACTER, "",LFG_SERVER);
                        $result[] = array($query_result[1], LFG_CHARACTER, $query_result[0], LFG_SERVER);
                        break;
                        
                   case LFG_GUILD:
                        
                        $query_object = $this->_query("SELECT characters.name, characters.id, guilds.name, guilds.id, servers.name, servers.id, games.name, games.id FROM $this->database_name.guilds, $this->database_name.servers, games WHERE characters.id = $id AND guilds.id = characters.guild AND servers.id = guilds.server AND games.id = servers.game", 1);
                        $query_result = mysql_fetch_row($query_object);
                        $result[] = array($query_result[7], LFG_GAME, $query_result[6], "0");
                        $result[] = array($query_result[7], LFG_SERVER, "", "0");
                        $result[] = array($query_result[5], LFG_SERVER, $query_result[4], "0");
                        $result[] = array($query_result[5], LFG_GUILD, "", "0");
                        $result[] = array($query_result[3], LFG_GUILD, $query_result[2], "0");
                        $result[] = array($query_result[3], LFG_CHARACTER, "", LFG_GUILD);
                        $result[] = array($query_result[1], LFG_CHARACTER, $query_result[0], LFG_GUILD);
                        break;

                   case LFG_INSTANCE:

                        $sql = <<<EOF
SELECT characters.name, characters.id, zones.name, instances.id, servers.name, servers.id, games.name, games.id
FROM $this->database_name.characters, $this->database_name.instances, $this->database_name.zones, $this->database_name.servers, $this->database_name.games
WHERE characters.id = $id, zones.id = instances.zone AND server.game = game.id AND
EXISTS (SELECT * FROM instance_members WHERE character = characters.id AND instance = instances.id)
EOF;
                        $query_object = $this->_query($sql, 1);
                        $query_result = mysql_fetch_row($query_object);
                        $result[] = array($query_result[7], LFG_GAME, $query_result[6], "0");
                        $result[] = array($query_result[7], LFG_SERVER, "", "0");
                        $result[] = array($query_result[5], LFG_SERVER, $query_result[4], "0");
                        $result[] = array($query_result[5], LFG_INSTANCE, "", "0");
                        $result[] = array($query_result[3], LFG_INSTANCE, $query_result[2], "0");
                        $result[] = array($query_result[3], LFG_CHARACTER, "", LFG_INSTANCE);
                        $result[] = array($query_result[1], LFG_CHARACTER, $query_result[0], LFG_INSTANCE);
                        break;

                   case LFG_USER:

                        $query_object = $this->_query("SELECT characters.name, characters.id FROM $this->database_name.characters WHERE user = $id", 1);
                        $query_result = mysql_fetch_row($query_object);
                        $result = array(array(0, LFG_USER, "", "0"));
                        $result[] = array($_SESSION['user_id'], LFG_CHARACTER, "", LFG_USER);
                        $result[] = array($query_result[1], LFG_CHARACTER, $query_result[0], LFG_USER);
                        break;

                    default:

                        LFG_Log(LFG_ERROR_INTERNAL, "", "incorrect sub_type for $type [$sub_type]");
                        break;
                }
                break;

            case LFG_INSTANCE:

                switch ( $sub_type ) {
                    case LFG_SERVER:
                        
                        $sql = <<<EOF
SELECT zones.name, instances.id, servers.name, servers.id, games.name, games.id
FROM $this->database_name.instances, $this->database_name.zones, $this->database_name.servers, $this->database_name.games
WHERE instances.id = $id AND zones.id = instances.zone AND servers.game = games.id AND
server.id IN (SELECT characters.server FROM characters WHERE characters.id = instances.owner) 
EOF;
                        $query_object = $this->_query($sql, 1);
                        $query_result = mysql_fetch_row($query_object);
                        $result[] = array($query_result[5], LFG_GAME, $query_result[4], "0");
                        $result[] = array($query_result[5], LFG_SERVER, "", "0");
                        $result[] = array($query_result[3], LFG_SERVER, $query_result[2], "0");
                        $result[] = array($query_result[3], LFG_INSTANCE, "", "0");
                        $result[] = array($query_result[1], LFG_INSTANCE, $query_result[0], LFG_SERVER);
                        break;
                        
                    case LFG_CHARACTER:
                        
                        $sql = <<<EOF
SELECT zones.name, instances.id, characters.name, characters.id, servers.name, servers.id, games.name, games.id
FROM $this->database_name.instances, $this->database_name.characters, $this->database_name.servers, $this->database_name.zones, $this->database_name.games
WHERE instances.id = $id AND zones.id = instances.zone AND characters.id = instances.owner AND characters.server = servers.id AND server.game = games.id
EOF;
                        $query_object = $this->_query($sql, 1);
                        $query_result = mysql_fetch_row($query_object);
                        $result[] = array($query_result[7], LFG_GAME, $query_result[6], "0");
                        $result[] = array($query_result[7], LFG_SERVER, "", "0");
                        $result[] = array($query_result[5], LFG_SERVER, $query_result[4], "0");
                        $result[] = array($query_result[5], LFG_CHARACTER, "", "0");
                        $result[] = array($query_result[3], LFG_CHARACTER, $query_result[2], "0");                        
                        $result[] = array($query_result[3], LFG_INSTANCE, "", LFG_CHARACTER);
                        $result[] = array($query_result[1], LFG_INSTANCE, $query_result[0], LFG_CHARACTER);
                        break;
                        
                    case LFG_ZONE:
                        
                        $sql = <<<EOF
SELECT zones.name, instances.id, servers.name, servers.id, game.name, game.id
FROM $this->database_name.instances, $this->database_name.zones, $this->database_name.servers, $this->database_name.games
WHERE instances.id = $id AND instances.zone = zone.id AND games.id = severs.game AND
server.id IN (SELECT characters.server FROM characters WHERE characters.id = instances.owner)
EOF;
                        $query_object = $this->_query($sql, 1);
                        $query_result = mysql_fetch_row($query_object);
                        $result[] = array($query_result[5], LFG_GAME, $query_result[4], "0");
                        $result[] = array($query_result[5], LFG_SERVER, "", "0");
                        $result[] = array($query_result[3], LFG_SERVER, $query_result[2], "0");
                        $result[] = array($query_result[3], LFG_INSTANCE, "", LFG_SERVER);
                        $result[] = array($query_result[1], LFG_INSTANCE, $query_result[0], LFG_SERVER);
                        break;
                        
                    default:
                        
                        LFG_Log(LFG_ERROR_INTERNAL, "", "incorrect sub_type for $type [$sub_type]");
                        break;
                }
                break;
        }
        if ( $query_object ) {
            mysql_free_result($query_object);
        }
        return $result;
    }
    
    public function getHierarchyList($type, $sub_type, $id) {
        // Returned array will contain elements as such:
        //
        // [0] = id
        // [1] = type
        // [2] = name
        // [3] = sub_type
        // (every other element is a list element, 1st item is a list)
        $query_object = 0;
        $result = array();
        $result[] = array(0, LFG_GAME, "", 0);
        switch ( $type ) {
            case LFG_GAME:
                break;

            case LFG_SERVER:

                $query_object = $this->_query("SELECT name FROM $this->database_name.games WHERE id = $id", 1);
                $query_result = mysql_fetch_row($query_object);
                $result[] = array($id, LFG_GAME, $query_result[0], "0");
                $result[] = array($id, LFG_SERVER, "", "0");
                break;

            case LFG_CLASS:

                $query_object = $this->_query("SELECT name FROM $this->database_name.games WHERE id = $id", 1);
                $query_result = mysql_fetch_row($query_object);
                $result[] = array($id, LFG_GAME, $query_result[0], "0");
                $result[] = array($id, LFG_CLASS, "", "0");
                break;

            case LFG_ZONE:

                $query_object = $this->_query("SELECT name FROM $this->database_name.games WHERE id = $id", 1);
                $query_result = mysql_fetch_row($query_object);
                $result[] = array($id, LFG_GAME, $query_result[0], "0");
                $result[] = array($id, LFG_ZONE, "", "0");
                break;

            case LFG_GUILD:

                $query_object = $this->_query("SELECT servers.name, servers.id, games.name, games.id FROM $this->database_name.servers, $this->database_name.games WHERE servers.id = $id AND games.id = servers.game", 1);
                $query_result = mysql_fetch_row($query_object);
                $result[] = array($query_result[3], LFG_GAME, $query_result[2], "0");
                $result[] = array($query_result[3], LFG_SERVER, "", "0");
                $result[] = array($query_result[1], LFG_SERVER, $query_result[0], "0");
                $result[] = array($query_result[1], LFG_GUILD, "", "0");
                break;
                
            case LFG_CHARACTER:
                
                switch ( $sub_type ) {
                    case LFG_SERVER:
                        
                        $query_object = $this->_query("SELECT servers.name, servers.id, games.name, games.id FROM $this->database_name.servers, $this->database_name.games WHERE servers.id = $id AND games.id = servers.game", 1);
                        $query_result = mysql_fetch_row($query_object);
                        $result[] = array($query_result[3], LFG_GAME, $query_result[2], "0");
                        $result[] = array($query_result[3], LFG_SERVER, "", "0");
                        $result[] = array($query_result[1], LFG_SERVER, $query_result[0], "0");
                        $result[] = array($query_result[1], LFG_CHARACTER, "", LFG_SERVER);
                        break;
                        
                   case LFG_GUILD:
                        
                        $query_object = $this->_query("SELECT guilds.id, guilds.name, servers.name, servers.id, games.name, games.id FROM $this->database_name.guilds, $this->database_name.servers, $this->database_name.games WHERE guilds.id = $id AND servers.id = guilds.server AND games.id = servers.game", 1);
                        $query_result = mysql_fetch_row($query_object);
                        $result[] = array($query_result[5], LFG_GAME, $query_result[4], "0");
                        $result[] = array($query_result[5], LFG_SERVER, "", "0");
                        $result[] = array($query_result[3], LFG_SERVER, $query_result[2], "0");
                        $result[] = array($query_result[3], LFG_GUILD, "", "0");
                        $result[] = array($query_result[0], LFG_GUILD, $query_result[1], "0");
                        $result[] = array($query_result[0], LFG_CHARACTER, "", LFG_GUILD);
                        break;

                   case LFG_INSTANCE:

                        $sql = <<<EOF
SELECT instances.id, zones.name, servers.name, servers.id, games.name, games.id
FROM $this->database_name.instances, $this->database_name.zones, $this->database_name.servers, $this->database_name.games
WHERE instances.id = $id AND zones.id = instances.zone AND server.game = game.id AND
servers.id IN (SELECT characters.owner FROM characters WHERE characters.id = instances.owner)
EOF;
                        $query_object = $this->_query($sql, 1);
                        $query_result = mysql_fetch_row($query_object);
                        $result[] = array($query_result[5], LFG_GAME, $query_result[4], "0");
                        $result[] = array($query_result[5], LFG_SERVER, "", "0");
                        $result[] = array($query_result[3], LFG_SERVER, $query_result[2], "0");
                        $result[] = array($query_result[3], LFG_INSTANCE, "", "0");
                        $result[] = array($query_result[0], LFG_INSTANCE, $query_result[1], "0");
                        $result[] = array($query_result[0], LFG_CHARACTER, "", LFG_INSTANCE);
                        break;

                   case LFG_USER:

                        $result = array(array(0, LFG_USER));
                        $result[] = array($_SESSION['user_id'], LFG_CHARACTER, "", LFG_USER);
                        break;

                    default:

                        LFG_Log(LFG_ERROR_INTERNAL, "", "incorrect sub_type for $type [$sub_type]");
                        break;
                }
                break;

            case LFG_INSTANCE:

                switch ( $sub_type ) {
                    case LFG_SERVER:
                        
                        $sql = <<<EOF
SELECT servers.name, servers.id, games.name, games.id
FROM $this->database_name.servers
INNER JOIN $this->database_name.games
ON servers.game = games.id
WHERE servers.id = $id
EOF;
                        $query_object = $this->_query($sql, 1);
                        $query_result = mysql_fetch_row($query_object);
                        $result[] = array($query_result[3], LFG_GAME, $query_result[2], '0');
                        $result[] = array($query_result[3], LFG_SERVER, '', '0');
                        $result[] = array($query_result[1], LFG_SERVER, $query_result[0], '0');
                        $result[] = array($query_result[1], LFG_INSTANCE, '', '0');
                        break;
                        
                    case LFG_CHARACTER:
                        
                        $sql = <<<EOF
SELECT characters.name, characters.id, servers.name, servers.id, games.name, games.id
FROM $this->database_name.characters, $this->database_name.servers, $this->database_name.games
WHERE characters.id = $id AND characters.server = servers.id AND server.game = game.id
EOF;
                        $query_object = $this->_query($sql, 1);
                        $query_result = mysql_fetch_row($query_object);
                        $result[] = array($query_result[5], LFG_GAME, $query_result[4], '0');
                        $result[] = array($query_result[5], LFG_SERVER, '', '0');
                        $result[] = array($query_result[3], LFG_SERVER, $query_result[2], '0');
                        $result[] = array($query_result[3], LFG_CHARACTER, '', '0');
                        $result[] = array($query_result[1], LFG_CHARACTER, $query_result[0], '0');                        
                        $result[] = array($query_result[1], LFG_INSTANCE, '', LFG_CHARACTER);
                        break;
                        
                    case LFG_ZONE:
                        
                        $sql = <<<EOF
SELECT servers.name, servers.id, game.name, game.id
FROM $this->database_name.servers, $this->database_name.games
WHERE servers.id = $id AND game.id = severs.game
EOF;
                        $query_object = $this->_query($sql, 1);
                        $query_result = mysql_fetch_row($query_object);
                        $result[] = array($query_result[3], LFG_GAME, $query_result[2], '0');
                        $result[] = array($query_result[3], LFG_SERVER, '', '0');
                        $result[] = array($query_result[1], LFG_SERVER, $query_result[0], '0');
                        $result[] = array($query_result[1], LFG_INSTANCE, '', LFG_SERVER);
                        break;

                    default:

                        LFG_Log(LFG_ERROR_INTERNAL, "", "incorrect sub_type for $type [$sub_type]");
                        break;
                }
                break;

            default:
                
                LFG_Log(LFG_ERROR_INTERNAL, "", "incorrect type [$type]");
                break;
        }
        if ( $query_object ) {
            mysql_free_result($query_object);
        }
        return $result;
    }

    private function getCharacter($id, $sub_type, $sub_id, $user) {
    	switch ( $sub_type ) {
    		
    		case LFG_SERVER:
    			
    			if ( is_null($user) ) {
    				if ( is_null($id) ) {
    				    return "SELECT * FROM $this->database_name.characters WHERE server = $sub_id AND visibility = 3";
    				}
    				else {
    					return "SELECT * FROM $this->database_name.characters WHERE id = $id AND server = $sub_id AND visibility = 3";
    				}
    			}
    			else {
    				if ( is_null($id) ) {
    				    return <<<EOF
SELECT * FROM $this->database_name.characters WHERE
server = $sub_id (
    user = $user OR
     visibility = 3 OR
    (visibility = 2 AND server IN (SELECT server FROM $this->database_name.characters WHERE user = $user) OR
    (visibility = 1 AND guild IS NOT NULL AND guild IN (SELECT guild FROM $this->database_name.characters WHERE user = $user)
) 
EOF;
    				}
    				else {
    					return <<<EOF
SELECT * FROM $this->database_name.characters WHERE
id = $id AND 
server = $sub_id AND ( 
    user = $user OR
     visibility = 3 OR
    (visibility = 2 AND server IN (SELECT server FROM $this->database_name.characters WHERE user = $user) OR
    (visibility = 1 AND guild IS NOT NULL AND guild IN (SELECT guild FROM $this->database_name.characters WHERE user = $user)
) 
EOF;
    				}
    			}
    			
    		case LFG_GUILD:

    			if ( is_null($user) ) {
    				if ( is_null($id) ) {
    				    return "SELECT * FROM $this->database_name.characters WHERE guild = $id AND visibility = 3";
    				}
    				else {
    					return "SELECT * FROM $this->database_name.characters WHERE id = $id AND guild = $sub_id AND visibility = 3";
    				}
    			}
    			else {
    				if ( is_null($id)) {
    				    return <<<EOF
SELECT * FROM $this->database_name.characters WHERE
guild = $sub_id AND (
    user = $user OR
     visibility = 3 OR
    (visibility = 2 AND server IN (SELECT server FROM $this->database_name.characters WHERE user = $user) OR
    (visibility = 1 AND guild IS NOT NULL AND guild IN (SELECT guild FROM $this->database_name.characters WHERE user = $user)
) 
EOF;
    				}
                    else {
                        return <<<EOF
SELECT * FROM $this->database_name.characters WHERE
id = $id AND
guild = $sub_id AND (
    user = $user OR
     visibility = 3 OR
    (visibility = 2 AND server IN (SELECT server FROM $this->database_name.characters WHERE user = $user) OR
    (visibility = 1 AND guild IS NOT NULL AND guild IN (SELECT guild FROM $this->database_name.characters WHERE user = $user)
) 
EOF;
                    }
    			}

    		default:
    			LFG_Log(LFG_ERROR_INTERNAL, "", "invalid sub_type for character [$type]");
    			break;
    	}
    }
    
    private function getClass($id, $sub_type, $sub_id, $user) {
        //if ( $sub_type != LFG_GAME ) {
        //    LFG_Log("INTERNAL", "", "invalid sub_type for class [$sub_type]");
        //}
        if ( is_null($sub_id) ) {
            if ( is_null($id) ) {
            	LFG_Log(LFG_ERROR_INTERNAL, "", "cannot query without a qualifier");
            }
            return "SELECT * FROM $this->database_name.classes WHERE id = $id";
        }
        else {
	        return ( is_null($id) )
	            ? "SELECT * FROM $this->database_name.classes WHERE game = $sub_id"
	            : "SELECT * FROM $this->database_name.classes WHERE id = $id AND game = $sub_id";
        }
    }
    
    private function getGame($id, $sub_type, $sub_id, $user) {
    	return ( is_null($id) )
            ? "SELECT * FROM $this->database_name.games"
            : "SELECT * FROM $this->database_name.games WHERE id = $id";
    }

    private function getGuild($id, $sub_type, $sub_id, $user) {
        if ( $sub_type != LFG_SERVER ) {
            LFG_Log(LFG_ERROR_INTERNAL, "", "invalid sub_type for guild [$sub_type]");
        }

        if ( is_null($user) ) {
        	if ( is_null($id) ) {
        		return "SELECT * FROM $this->database_name.guilds WHERE server = $sub_id AND visibility = 3";
        	}
        	else {
                return "SELECT * FROM $this->database_name.guilds WHERE id = $id AND server = $sub_id AND visibility = 3";
        	}
        }
        else {
	        if ( is_null($id) ) {
	            return <<<EOF
SELECT * FROM $this->database_name.guilds WHERE
server = $sub_id AND (
     visibility = 3 OR
    (visibility = 2 AND server IN (SELECT server FROM $this->database_name.characters WHERE user = $user)) OR
    id IN (SELECT guild FROM $this->database_name.characters WHERE user = $user)
)
EOF;
	        }
	        else {
	            return <<<EOF
SELECT * FROM $this->database_name.guilds WHERE
id = $id AND
server = $sub_id AND (
visibility = 3 OR
    (visibility = 2 AND server IN (SELECT server FROM $this->database_name.characters WHERE user = $user)) OR
    id IN (SELECT guild FROM $this->database_name.characters WHERE user = $user)
)
EOF;
            }
        }
    }
    
    private function getInstance($id, $sub_type, $sub_id, $user) {

        switch ( $sub_type ) {

        	case LFG_SERVER:
        		
        		if ( is_null($user) ) {
        			if ( is_null($id) ) {
        				return <<<EOF
SELECT * FROM $this->database_name.instances
INNER JOIN $this->database_name.characters ON
instances.owner = characters.id WHERE
instances.visibility = 3 AND 
characters.server = $sub_id
EOF;
        			}
        			else {
        				return <<<EOF
SELECT * FROM $this->database_name.instances
INNER JOIN $this->database_name.characters ON
instances.owner = characters.id WHERE
instances.id = $id AND
instances.visibility = 3 AND 
characters.server = $sub_id
EOF;
        			}
        		}
        		else {
                    if ( is_null($id) ) {
                        return <<<EOF
SELECT * FROM $this->database_name.instances i
INNER JOIN $this->database_name.characters ON
i.owner = characters.id WHERE
characters.server = $sub_id AND (
     i.visibility = 3 OR
    (i.visibility = 2 AND (SELECT server FROM $this->database_name.characters WHERE user = i.owner) IN (SELECT server FROM $this->database_name.characters WHERE user = $user)) OR
    (i.visibility = 1 AND (SELECT guild FROM $this->database_name.characters WHERE user = i.owner) IN (SELECT guild FROM $this->database_name.characters WHERE user = $user)) OR
    (i.visibility = 0 AND EXISTS (SELECT * FROM $this->database_name.instance_members WHERE instance = i.id AND member IN (SELECT id FROM $this->database_name.characters WHERE user = $user))) 
)
EOF;
                    }
                    else {
                        return <<<EOF
SELECT * FROM $this->database_name.instances i
INNER JOIN $this->database_name.characters ON
i.owner = characters.id WHERE
i.id = $id AND
characters.server = $sub_id AND (
     i.visibility = 3 OR
    (i.visibility = 2 AND (SELECT server FROM $this->database_name.characters WHERE user = i.owner) IN (SELECT server FROM $this->database_name.characters WHERE user = $user)) OR
    (i.visibility = 1 AND (SELECT guild FROM $this->database_name.characters WHERE user = i.owner) IN (SELECT guild FROM $this->database_name.characters WHERE user = $user)) OR
    (i.visibility = 0 AND EXISTS (SELECT * FROM $this->database_name.instance_members WHERE instance = i.id AND member IN (SELECT id FROM $this->database_name.characters WHERE user = $user))) 
)
EOF;
                    }
                }
                
        	case LFG_GUILD:

                if ( is_null($user) ) {
                    if ( is_null($id) ) {
                        return "SELECT * FROM $this->database_name.instances WHERE guild = $sub_id AND visibility = 3";
                    }
                    else {
                        return "SELECT * FROM $this->database_name.instances WHERE id = $id AND guild = $sub_id AND visibility = 3";
                    }
                }
                else {
                    if ( is_null($id) ) {
                        return <<<EOF
SELECT * FROM $this->database_name.instances i WHERE
i.server = $sub_id AND (
     i.visibility = 3 OR
    (i.visibility = 2 AND (SELECT server FROM $this->database_name.characters WHERE user = i.owner) IN (SELECT server FROM $this->database_name.characters WHERE user = $user)) OR
    (i.visibility = 1 AND (SELECT guild FROM $this->database_name.characters WHERE user = i.owner) IN (SELECT guild FROM $this->database_name.characters WHERE user = $user)) OR
    (i.visibility = 0 AND EXISTS (SELECT * FROM $this->database_name.instance_members WHERE instance = i.id AND member IN (SELECT id FROM $this->database_name.characters WHERE user = $user)) 
)
EOF;
                    }
                    else {
                        return <<<EOF
SELECT * FROM $this->database_name.instances i WHERE
i.id = $id AND
i.server = $sub_id AND (
i.visibility = 3 OR
    (i.visibility = 2 AND (SELECT server FROM $this->database_name.characters WHERE user = i.owner) IN (SELECT server FROM $this->database_name.characters WHERE user = $user)) OR
    (i.visibility = 1 AND (SELECT guild FROM $this->database_name.characters WHERE user = i.owner) IN (SELECT guild FROM $this->database_name.characters WHERE user = $user)) OR
    (i.visibility = 0 AND EXISTS (SELECT * FROM $this->database_name.instance_members WHERE instance = i.id AND member IN (SELECT id FROM $this->database_name.characters WHERE user = $user)) 
)
EOF;
                    }
                }

        	case LFG_CHARACTER:

                if ( is_null($user) ) {
                    if ( is_null($id) ) {
                        return <<<EOF
SELECT * FROM $this->database_name.instances WHERE
    visibility = 3 AND
    id IN (SELECT instance FROM $this->database_name.instance_members WHERE member = $sub_id) 
EOF;
                    }
                    else {
                        return <<<EOF
SELECT * FROM $this->database_name.instances WHERE
    visibility = 3 AND
    id = $id AND
    id IN (SELECT instance FROM $this->database_name.instance_members WHERE member = $sub_id) 
EOF;
                    }
                }
                else {
                    if ( is_null($id) ) {
                        return <<<EOF
SELECT * FROM $this->database_name.instances i WHERE
EXISTS (SELECT * FROM $this->database_name.instance_members WHERE instance = i.id AND member = $sub_id) AND (
     i.visibility = 3 OR
    (i.visibility = 2 AND (SELECT server FROM $this->database_name.characters WHERE user = i.owner) IN (SELECT server FROM $this->database_name.characters WHERE user = $user)) OR
    (i.visibility = 1 AND (SELECT guild FROM $this->database_name.characters WHERE user = i.owner) IN (SELECT guild FROM $this->database_name.characters WHERE user = $user)) OR
    (i.visibility = 0 AND EXISTS (SELECT * FROM $this->database_name.instance_members WHERE instance = i.id AND member IN (SELECT id FROM $this->database_name.characters WHERE user = $user)) 
)
EOF;
                    }
                    else {
                        return <<<EOF
SELECT * FROM $this->database_name.instances i WHERE
id = $id AND
EXISTS (SELECT * FROM $this->database_name.instance_members WHERE instance = i.id AND member = $sub_id) AND (
     i.visibility = 3 OR
    (i.visibility = 2 AND (SELECT server FROM $this->database_name.characters WHERE user = i.owner) IN (SELECT server FROM $this->database_name.characters WHERE user = $user)) OR
    (i.visibility = 1 AND (SELECT guild FROM $this->database_name.characters WHERE user = i.owner) IN (SELECT guild FROM $this->database_name.characters WHERE user = $user)) OR
    (i.visibility = 0 AND EXISTS (SELECT * FROM $this->database_name.instance_members WHERE instance = i.id AND member IN (SELECT id FROM $this->database_name.characters WHERE user = $user)) 
)
EOF;
                    }
                }
                
        	default:
        		LFG_Log(LFG_ERROR_INTERNAL, "", "invalid sub_type for instance [$sub_type]");
        }
    }
    
    private function getServer($id, $sub_type, $sub_id, $user) {
        //if ( $sub_type != LFG_GAME ) {
        //    LFG_Log(LFG_ERROR_INTERNAL, "", "invalid sub_type for server [$sub_type]");
        //}
        if ( is_null($sub_id) ) {
            if ( is_null($id) ) {
                LFG_Log(LFG_ERROR_INTERNAL, "", "cannot query without a qualifier");
            }
            return "SELECT * FROM $this->database_name.servers WHERE id = $id";
        }
        else {
            return ( is_null($id) )
                ? "SELECT * FROM $this->database_name.servers WHERE game = $sub_id"
                : "SELECT * FROM $this->database_name.servers WHERE id = $id AND game = $sub_id";
        }
    }
    
    public function getZone($id, $sub_type, $sub_id, $user) {
        if ( is_null($sub_id) ) {
            if ( is_null($id) ) {
                LFG_Log(LFG_ERROR_INTERNAL, "", "cannot query without a qualifier");
            }
            return "SELECT * FROM $this->database_name.zones WHERE id = $id";
        }
        else {
            return ( is_null($id) )
                ? "SELECT * FROM $this->database_name.zones WHERE game = $sub_id"
                : "SELECT * FROM $this->database_name.zones WHERE id = $id AND game = $sub_id";
        }
    }
    
    public function getRestricted($type, $id, $sub_type, $sub_id, $user) {
    	$sql = "";
    	switch ( $type ) {
    		case LFG_CHARACTER:
    			$sql = $this->getCharacter($id, $sub_type, $sub_id, $user);
    			break;

            case LFG_CLASS:
                $sql = $this->getClass($id, $sub_type, $sub_id, $user);
                break;

            case LFG_GAME:
                $sql = $this->getGame($id, $sub_type, $sub_id, $user);
                break;

            case LFG_GUILD:
                $sql = $this->getGuild($id, $sub_type, $sub_id, $user);
                break;

            case LFG_INSTANCE:
                $sql = $this->getInstance($id, $sub_type, $sub_id, $user);
                break;

            case LFG_SERVER:
                $sql = $this->getServer($id, $sub_type, $sub_id, $user);
                break;

            case LFG_ZONE:
                $sql = $this->getZone($id, $sub_type, $sub_id, $user);
                break;
                
            default:
            	LFG_Log("INTERNAL", "", "invalid type [$type]");
    	}

    	return $this->_query_to_object($type, $this->_query($sql));
    }
}
?>