<?
/****************************************************************************
 * Name:		network.php
 * Desc:		Class for retrieving information about networks, nodes and
 *				clients from the wavewatcher database.
 * Authors:		Niklas Påhlsson, Pierre Svärd, Petter Miller, Mats Hörberg
/***************************************************************************/

//include the DBConnection class.
require_once("../classes/dbconnection.php");

class Network
{
	//DBConnection object
	var $dbcn;

	//Constructor
	function Network()
	{
		//Create new instance of DBConnection
		$this->dbcn = new DBConnection();
	}

	/********************************** NETWORK FUNCTIONS ******************************************/

	//Retrieves information about all networks with nodes
	//connected to the user with user_id = $user_id
	function GetNetworks($user_id = 0, $all = "no")
	{
		$cn = $this->dbcn->Connect();
		if($user_id == 0)
		{
			if($all == "no")
				$result = $this->dbcn->Query("SELECT DISTINCT networks.network_id, networks.network_name FROM networks INNER JOIN nodes ON networks.network_id = nodes.network_id WHERE nodes.monitor = 'Y' order by networks.network_name", array("networks", "READ", "nodes", "READ"));
			elseif($all == "yes")
				$result = $this->dbcn->Query("SELECT DISTINCT networks.network_id, networks.network_name FROM networks order by networks.network_name", array("networks", "READ"));
		}
		else
			$result = $this->dbcn->Query("SELECT DISTINCT networks.network_id, networks.network_name FROM networks INNER JOIN nodes ON networks.network_id = nodes.network_id INNER JOIN nodes_users ON nodes.node_id = nodes_users.node_id WHERE nodes_users.user_id = $user_id AND nodes.monitor = 'Y' order by networks.network_name", array("networks", "READ", "nodes", "READ", "nodes_users", "READ"));
		$this->dbcn->Disconnect();
		return $result;
	}

	//Returns the name of network with id $network_id
	function GetNetworkName($network_id)
	{
		$cn = $this->dbcn->Connect();
		List($network_name) = $this->FetchRow($this->dbcn->Query("SELECT network_name FROM networks WHERE network_id = $network_id", array("networks", "READ")));
		$this->dbcn->Disconnect();
		return $network_name;
	}

	//Returns the status of a network.
	//0 - OK
	//1 - One or more nodes are unreachable
	function GetNetworkStatus($network_id, $user_id)
	{
        $cn = $this->dbcn->Connect();
        $result = $this->dbcn->Query("SELECT current_ping_times.status FROM current_ping_times INNER JOIN nodes ON nodes.node_id = current_ping_times.node_id INNER JOIN nodes_users ON nodes.node_id = nodes_users.node_id INNER JOIN networks ON networks.network_id = nodes.network_id WHERE networks.network_id = $network_id AND nodes_users.user_id = $user_id", array("current_ping_times", "READ", "nodes", "READ", "nodes_users", "READ", "networks", "READ"));
		$network_status = 0;
		while (List($node_status) = $this->FetchRow($result))
		{
			if($node_status == 'Down')
			{
				$network_status = 1;
				break;
			}
		}
        $this->dbcn->Disconnect();
		return $network_status;
	}

	//For updating och creating networks.
	function SetNetworkDetails($network_id, $network_name)
	{
		$cn = $this->dbcn->Connect();
		if($network_id < 1)
		{
			//Add new node
			$this->dbcn->Query("INSERT INTO networks(network_name) VALUES('$network_name')", array("networks", "WRITE"));
		}
		else
		{
			//Update existing node
			$this->dbcn->Query("UPDATE networks SET network_name = '$network_name' WHERE network_id = $network_id", array("networks", "WRITE"));
		}
		$this->dbcn->Disconnect();
	}
	//For deleting networks.
        function DelNetwork($network_id, $network_name){
        	$cn = $this->dbcn->Connect();
        	//delete a network only if it is empty
		$result = $this->dbcn->Query("SELECT count(node_id) FROM nodes WHERE network_id = $network_id", array("nodes", "READ"));
		List($count) = $this->FetchRow($result);
		if ($count==0){
			$result = $this->dbcn->Query("DELETE FROM networks where network_id = $network_id", array("networks", "WRITE"));
		}
        $this->dbcn->Disconnect();
        }
	/********************************** NODE FUNCTIONS ******************************************/

	//Gets the number of interfaces for a node
	function GetNumberOfInterfaces($node_id)
	{
		$cn = $this->dbcn->Connect();

		//List($count) = $this->FetchRow($this->dbcn->Query("SELECT COUNT(DISTINCT(interface)) FROM node_interface_log WHERE node_id = $node_id", array("node_interface_log", "READ")));

		List($count) = $this->FetchRow($this->dbcn->Query("SELECT node_types.interfaces FROM node_types INNER JOIN nodes ON nodes.node_type_id = node_types.node_type_id WHERE nodes.node_id = $node_id", array("nodes", "READ", "node_types", "READ")));
		$this->dbcn->Disconnect();
		return $count;
	}

	//Retrieves information about all nodes connected to
	//user $user_id in network $network_id.
	//If $user_id = 0 then all nodes for the network $network_id are retrieved
	//If $network_id = 0 then all nodes for $user_id are retrieved
	//If both parameters = 0 then all nodes are retrieved
	function GetNodes($user_id = 0, $network_id = 0)
	{
		$cn = $this->dbcn->Connect();
		if($user_id == 0 && $network_id == 0)
			$result = $this->dbcn->Query("SELECT nodes.node_id, nodes.parent_node_id, nodes.node_name, nodes.node_pass, nodes.node_ip, nodes.location, nodes.monitor, nodes.log, nodes.signal, nodes.alarm, node_types.node_type_name, networks.network_name FROM nodes INNER JOIN node_types ON nodes.node_type_id = node_types.node_type_id INNER JOIN networks ON nodes.network_id = networks.network_id WHERE nodes.monitor = 'Y' order by nodes.node_name", array("nodes", "READ", "node_types", "READ", "nodes_users", "READ", "networks", "READ"));
		elseif($user_id == 0 && $network_id != 0)
			$result = $this->dbcn->Query("SELECT nodes.node_id, nodes.parent_node_id, nodes.node_name, nodes.node_pass, nodes.node_ip, nodes.location, nodes.monitor, nodes.log, nodes.signal, nodes.alarm, node_types.node_type_name, networks.network_name FROM nodes INNER JOIN node_types ON nodes.node_type_id = node_types.node_type_id INNER JOIN networks ON nodes.network_id = networks.network_id WHERE nodes.network_id = $network_id AND nodes.monitor = 'Y' order by nodes.node_name", array("nodes", "READ", "node_types", "READ", "nodes_users", "READ", "networks", "READ"));
		elseif($user_id != 0 && $network_id == 0)
			$result = $this->dbcn->Query("SELECT nodes.node_id, nodes.parent_node_id, nodes.node_name, nodes.node_pass, nodes.node_ip, nodes.location, nodes.monitor, nodes.log, nodes.signal, nodes.alarm, node_types.node_type_name, networks.network_name FROM nodes INNER JOIN nodes_users ON nodes_users.node_id = nodes.node_id INNER JOIN node_types ON nodes.node_type_id = node_types.node_type_id INNER JOIN networks ON nodes.network_id = networks.network_id WHERE user_id = $user_id AND nodes.monitor = 'Y' order by nodes.node_name", array("nodes", "READ", "node_types", "READ", "nodes_users", "READ", "networks", "READ"));
		else
			$result = $this->dbcn->Query("SELECT nodes.node_id, nodes.parent_node_id, nodes.node_name, nodes.node_pass, nodes.node_ip, nodes.location, nodes.monitor, nodes.log, nodes.signal, nodes.alarm, node_types.node_type_name, networks.network_name FROM nodes INNER JOIN nodes_users ON nodes_users.node_id = nodes.node_id INNER JOIN node_types ON nodes.node_type_id = node_types.node_type_id INNER JOIN networks ON nodes.network_id = networks.network_id WHERE user_id = $user_id AND nodes.network_id = $network_id AND nodes.monitor = 'Y' order by nodes.node_name", array("nodes", "READ", "node_types", "READ", "nodes_users", "READ", "networks", "READ"));
		$this->dbcn->Disconnect();
		return $result;
	}

	//Returns the table node_types
	function GetNodeTypes()
	{
		$cn = $this->dbcn->Connect();
		$result = $this->dbcn->Query("SELECT node_types.node_type_id, node_types.node_type_name, node_types.interfaces FROM node_types order by node_type_name", array("node_types", "READ"));
		$this->dbcn->Disconnect();
		return $result;
	}
	//Returns the process_id types

	//Gets the number of nodes with status Up for network
	function GetNodesUpCount($network_id, $user_id)
	{
		$cn = $this->dbcn->Connect();
		$result = $this->dbcn->Query("SELECT COUNT(*) FROM nodes INNER JOIN nodes_users ON nodes_users.node_id = nodes.node_id INNER JOIN current_ping_times ON current_ping_times.node_id = nodes.node_id WHERE user_id = $user_id AND status='Up' AND network_id = $network_id", array("nodes", "READ", "current_ping_times", "READ", "nodes_users", "READ"));
		$this->dbcn->Disconnect();
		List($nodes_up) = $this->FetchRow($result);
		return $nodes_up;
	}

	//Gets the number of nodes with status down for a network
	function GetNodesDnCount($network_id, $user_id)
	{
		$cn = $this->dbcn->Connect();
		$result = $this->dbcn->Query("SELECT COUNT(*) FROM nodes INNER JOIN nodes_users ON nodes_users.node_id = nodes.node_id INNER JOIN current_ping_times ON current_ping_times.node_id = nodes.node_id WHERE user_id = $user_id AND status='Down' AND network_id = $network_id", array("nodes", "READ", "current_ping_times", "READ", "nodes_users", "READ"));
		$this->dbcn->Disconnect();
		List($nodes_up) = $this->FetchRow($result);
		return $nodes_up;
	}
	
	//Returns all the nodes names and Ids for the user $user_id.
	//If the parameter is 0 then all nodes are included in the result
	function GetNodeNamesAndIDs($user_id = 0)
	{
		$cn = $this->dbcn->Connect();
		$result;
		if($user_id == 0)
			$result = $this->dbcn->Query("SELECT nodes.node_id, nodes.node_name FROM nodes order by nodes.node_name", array("nodes", "READ"));
		else
			$result = $this->dbcn->Query("SELECT nodes.node_id, nodes.node_name FROM nodes INNER JOIN nodes_users ON nodes_users.node_id = nodes.node_id WHERE nodes_users.user_id = $user_id order by nodes.node_name", array("nodes", "READ", "nodes_users", "READ"));
		$this->dbcn->Disconnect();
		return $result;
	}

	//Returns the name of the node with id $node_id
	function GetNodeName($node_id)
	{
		$cn = $this->dbcn->Connect();
		List($node_name) = $this->FetchRow($this->dbcn->Query("SELECT node_name FROM nodes WHERE node_id = $node_id", array("nodes", "READ")));
		$this->dbcn->Disconnect();
		return $node_name;
	}

	//Returns a result with all fields for node $node_id
	function GetNodeDetails($node_id)
	{
		$cn = $this->dbcn->Connect();
		$result = $this->dbcn->Query("SELECT nodes.node_type_id, nodes.network_id, nodes.parent_node_id, nodes.node_name, nodes.node_pass, nodes.node_ip, nodes.location, nodes.monitor, nodes.log, nodes.signal, nodes.alarm, node_types.node_type_name, networks.network_name, nodes.process_id FROM nodes INNER JOIN node_types ON nodes.node_type_id = node_types.node_type_id INNER JOIN networks ON nodes.network_id = networks.network_id WHERE node_id = $node_id", array("nodes", "READ", "node_types", "READ", "networks", "READ"));
		$this->dbcn->Disconnect();
		return $result;
	}

	//Get the status of a node.
	//0 - OK
	//1 - The node is unreachable
	//2 - One or more parentnodes are unreachable
	function GetNodeStatus($node_id)
	{
        $cn = $this->dbcn->Connect();
        $result = $this->dbcn->Query("SELECT current_ping_times.status FROM current_ping_times WHERE node_id = $node_id", array("current_ping_times", "READ"));
		$status = 0;
		if (List($node_status) = $this->FetchRow($result))
			if($node_status == 'Down')
				$status = 1;
		if($status == 1)
			if (List($parent_node_id) = $this->FetchRow($this->dbcn->Query("SELECT parent_node_id FROM nodes WHERE node_id = $node_id", array("nodes", "READ"))))
				if($parent_node_id != 0)
					if($this->GetNodeStatus($parent_node_id) != 0)
						$status = 2;
        $this->dbcn->Disconnect();
		return $status;
	}

	//Adds or updates the nodes table if the parameter $node_id
	//is less than 1 then a new rocord is added to the table,
	//else the record with node_id = $node_id is updated.
	function SetNodeDetails($node_id, $node_type_id, $network_id, $parent_node_id, $node_name, $node_pass, $node_ip, $location, $monitor, $log, $signal, $alarm, $process_id)
	{
		$cn = $this->dbcn->Connect();
		if($node_id == 0)
		{
			//Add new node
			$this->dbcn->Query("INSERT INTO nodes(node_type_id, network_id, parent_node_id, node_name, node_pass, node_ip, location, monitor, log, signal, alarm, process_id) VALUES($node_type_id, $network_id, $parent_node_id, '$node_name', '$node_pass', '$node_ip', '$location', '$monitor', '$log', '$signal', '$alarm', $process_id)", array("nodes", "WRITE"));
		}
		else
		{
			//Update existing node
			$this->dbcn->Query("UPDATE nodes SET node_type_id = $node_type_id, network_id = $network_id, parent_node_id = $parent_node_id, node_name = '$node_name', node_pass = \"$node_pass\", node_ip= '$node_ip', location = '$location', monitor = '$monitor', log = '$log', signal = '$signal', alarm = '$alarm', process_id = $process_id WHERE node_id = $node_id", array("nodes", "WRITE"));
		}
		$this->dbcn->Disconnect();
	}
	function DelNode($node_id){
        	$cn = $this->dbcn->Connect();
        	//delete a node
        	$result = $this->dbcn->Query("DELETE FROM nodes where node_id = $node_id", array("nodes", "WRITE"));
		$result = $this->dbcn->Query("DELETE FROM nodes_users where node_id = $node_id", array("nodes_users", "WRITE"));
        	$this->dbcn->Disconnect();
        }


	/********************************** PROCESS FUNCTIONS ******************************************/


	function GetProcess_idTypes()
	{
		$cn = $this->dbcn->Connect();
		$result = $this->dbcn->Query("SELECT process_info.process_id FROM process_info", array("process_info", "READ"));
		$this->dbcn->Disconnect();
		return $result;
	}



	//Gets the process information
	function GetProcessDetails()
	{
		$cn = $this->dbcn->Connect();

		$result = $this->dbcn->Query("SELECT process_id,sleep_time_snmp,sleep_time_data,sleep_time_ping,start_time_snmp,stop_time_snmp,start_time_data,stop_time_data,start_time_ping,stop_time_ping FROM process_info", array("process_info", "READ"));

		$this->dbcn->Disconnect();
		return $result;
	}

	function updateProcessInfo($process_id,$sleep_time_snmp,$sleep_time_data,$sleep_time_ping)
	{
		$cn = $this->dbcn->Connect();
		$result =$this->dbcn->Query("UPDATE process_info SET  sleep_time_snmp =$sleep_time_snmp,sleep_time_data=$sleep_time_data,sleep_time_ping=$sleep_time_ping WHERE process_id = $process_id", array("process_info", "WRITE"));
		
		$this->dbcn->Disconnect();
		return $result;
	}


	/********************************** CLIENT FUNCTIONS ******************************************/

function GetClientList()
        {
                
		# this returns all currently connected clients together with associated accesspoint
		$cn = $this->dbcn->Connect();
    $result = $this->dbcn->Query("SELECT if_name, mac, remsig, locsig, node_name, node_ip, location FROM current_signal_levels, connections, nodes where connections.connection_id=current_signal_levels.connection_id AND nodes.node_id=connections.node_id AND current_signal_levels.status = 'OK' order by remsig", array("current_signal_levels", "READ","connections","READ","nodes","READ"));

                $this->dbcn->Disconnect();
                return $result;
        }


	//Retrieves information about all clients connected
	//to node $node_id.
	function GetClients($node_id)
	{
		$cn = $this->dbcn->Connect();
		$result = $this->dbcn->Query("SELECT connections.connection_id, connections.mac, connections.if_name, connections.node_id, connections.alias, connections.sig_alarm_level, connections.noise_alarm_level, connections.sig_alarm_active, connections.noise_alarm_active, current_signal_levels.locsig, current_signal_levels.locnoi, current_signal_levels.remsig, current_signal_levels.remnoi, current_signal_levels.status FROM current_signal_levels INNER JOIN connections ON connections.connection_id = current_signal_levels.connection_id WHERE connections.node_id = $node_id order by connections.mac", array("current_signal_levels", "READ", "connections", "READ"));
		$this->dbcn->Disconnect();
		return $result;
	}

	//Retrieves information about a client
	function GetClientDetails($connection_id)
	{
		$cn = $this->dbcn->Connect();
		$result = $this->dbcn->Query("SELECT connections.mac, connections.if_name, connections.alias, connections.sig_alarm_level, connections.noise_alarm_level, sig_alarm_active, noise_alarm_active, current_signal_levels.locsig, current_signal_levels.locnoi, current_signal_levels.remsig, current_signal_levels.remnoi, current_signal_levels.status, nodes.node_name FROM current_signal_levels INNER JOIN connections ON connections.connection_id = current_signal_levels.connection_id INNER JOIN nodes ON nodes.node_id = connections.node_id WHERE connections.connection_id = $connection_id", array("current_signal_levels", "READ", "connections", "READ", "nodes", "READ"));
		$this->dbcn->Disconnect();
		return $result;
	}

	//Update record in connections with connection_id $connection_id
	function SetClientDetails($connection_id, $alias, $sig_alarm_level, $noise_alarm_level, $sig_alarm_active, $noise_alarm_active)
	{
		$cn = $this->dbcn->Connect();
		$result = $this->dbcn->Query("UPDATE connections SET connections.alias = '".$alias."', connections.sig_alarm_level = $sig_alarm_level, connections.noise_alarm_level = $noise_alarm_level, connections.sig_alarm_active = '".$sig_alarm_active."', connections.noise_alarm_active = '".$noise_alarm_active."' WHERE connection_id = $connection_id", array("connections", "WRITE"));
		$this->dbcn->Disconnect();
	}

	//Gets the number of clients connected to $node_id
	function GetClientCount($node_id)
	{
		$cn = $this->dbcn->Connect();
		$result = $this->dbcn->Query("SELECT COUNT(*) FROM current_signal_levels INNER JOIN connections ON connections.connection_id = current_signal_levels.connection_id WHERE connections.node_id = $node_id", array("current_signal_levels", "READ", "connections", "READ"));
		$this->dbcn->Disconnect();
		List($count) = $this->FetchRow($result);
		return $count;
	}

	//Gets the number of clients with status up for node $node_id
	function GetClientsUpCount($node_id)
	{
		$cn = $this->dbcn->Connect();
		$result = $this->dbcn->Query("SELECT COUNT(*) FROM current_signal_levels INNER JOIN connections ON connections.connection_id = current_signal_levels.connection_id WHERE connections.node_id = $node_id AND current_signal_levels.status = 'OK'", array("current_signal_levels", "READ", "connections", "READ"));
		$this->dbcn->Disconnect();
		List($clients_up) = $this->FetchRow($result);
		return $clients_up;
	}

	//Gets the number of clients with status down for node $node_id
	function GetClientsDnCount($node_id)
	{
		$cn = $this->dbcn->Connect();
		$result = $this->dbcn->Query("SELECT COUNT(*) FROM current_signal_levels INNER JOIN connections ON connections.connection_id = current_signal_levels.connection_id WHERE connections.node_id = $node_id AND current_signal_levels.status = 'Down'", array("current_signal_levels", "READ", "connections", "READ"));
		$this->dbcn->Disconnect();
		List($clients_dn) = $this->FetchRow($result);
		return $clients_dn;
	}
	
	//Gets the number of clients with status critical for node $node_id
	function GetClientsWnCount($node_id)
	{
		$cn = $this->dbcn->Connect();
		$result = $this->dbcn->Query("SELECT COUNT(*) FROM current_signal_levels INNER JOIN connections ON connections.connection_id = current_signal_levels.connection_id WHERE connections.node_id = $node_id AND current_signal_levels.status = 'Critical'", array("current_signal_levels", "READ", "connections", "READ"));
		$this->dbcn->Disconnect();
		List($clients_wn) = $this->FetchRow($result);
		return $clients_wn;
	}

	//Removes all clients for node $node_id
	function RemoveClientsOnNode($node_id)
	{
		$cn = $this->dbcn->Connect();
		$result = $this->dbcn->Query("SELECT current_signal_levels.connection_id FROM current_signal_levels INNER JOIN connections ON current_signal_levels.connection_id = connections.connection_id WHERE connections.node_id = $node_id", array("current_signal_levels", "READ", "connections", "READ"));
		while(List($connection_id) = $this->dbcn->FetchRow($result))
		{
			$result2 = $this->dbcn->Query("DELETE FROM current_signal_levels WHERE current_signal_levels.connection_id = $connection_id", array("current_signal_levels", "WRITE"));
		}
		$this->dbcn->Disconnect();
	}

	//Removes all clients for the network $network_id
	function RemoveClientsOnNetwork($network_id)
	{
		$cn = $this->dbcn->Connect();

		$result = $this->dbcn->Query("SELECT current_signal_levels.connection_id FROM current_signal_levels INNER JOIN connections ON current_signal_levels.connection_id = connections.connection_id INNER JOIN nodes ON nodes.node_id = connections.node_id WHERE nodes.network_id = $network_id", array("current_signal_levels", "READ", "connections", "READ", "nodes", "READ"));
		while(List($connection_id) = $this->dbcn->FetchRow($result))
		{
			$result2 = $this->dbcn->Query("DELETE FROM current_signal_levels WHERE current_signal_levels.connection_id = $connection_id", array("current_signal_levels", "WRITE"));
		}
		$this->dbcn->Disconnect();
	}

	//Remove record from current_signal_levels where connection_id = $connection_id
	function RemoveClient($connection_id)
	{
		$cn = $this->dbcn->Connect();
		$result = $this->dbcn->Query("DELETE FROM current_signal_levels WHERE connection_id = $connection_id", array("current_signal_levels", "WRITE"));
		$this->dbcn->Disconnect();
	}

	//Checks if there is a record in current_signal_level with connection_id $connection_id
	function ClientExists($connection_id)
	{
		$cn = $this->dbcn->Connect();
		List($count) = $this->FetchRow($this->dbcn->Query("SELECT COUNT(*) FROM current_signal_levels WHERE connection_id = $connection_id", array("current_signal_levels", "READ")));
		$this->dbcn->Disconnect();
		if($count == 0)
			return false;
		else
			return true;
	}

	/********************************** MISCELLANEOUS ******************************************/	

	//Get database information
	function GetDatabaseInfo()
	{
		$cn = $this->dbcn->Connect();
		$result = $this->dbcn->Query("SELECT owner, description, support_phone, support_email FROM database_info LIMIT 1", array("database_info", "READ"));
		$this->dbcn->Disconnect();
		return $result;
	}

    //Returns the next row from a resultset
    function FetchRow($result)
    {
        if (!$result)
            return FALSE;
        return $this->dbcn->FetchRow($result);
    }

    // Move to a specified row in a resultset
    function DataSeek($result, $row)
    {
        if (!$result or $row < 0)
             return FALSE;
        return $this->dbcn->DataSeek($result, $row);
    }
}
?>