databasehandler.php

Network Administration Visualized 网络管理可视化源码

<?php
/*
 * $Id: databaseHandler.php 3725 2006-11-09 13:39:07Z mortenv $
 *
 * A library for managing the accounts and account profiles stored in
 * the navprofiles database.
 *
 * Copyright 2002-2004 UNINETT AS
 * 
 * This file is part of Network Administration Visualized (NAV)
 *
 * NAV is free software; you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation; either version 2 of the License, or
 * (at your option) any later version.
 *
 * NAV is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with NAV; if not, write to the Free Software
 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
 *
 *
 * Authors: Andreas Aakre Solberg <[e-mail]>
 *
 */

class DBH {

	// M氓 ha inn en ferdig oppkoblet databasekobling til postgres
	var $connection;


	// Konstruktor
	function DBH($connection) {
		$this->connection = $connection;
	}
  
  
    function permissionAddress($uid, $aid) {
        $querystring = "
SELECT * FROM alarmadresse 
WHERE (accountid = " . addslashes($uid) . " AND id = " . addslashes($aid) . ")";
//echo "<pre>" . $querystring . "</pre>";
        if ( $query = @pg_exec($this->connection, $querystring) ) { 
            if (pg_numrows($query) > 0 ) return true;
        }
        return false;
    }
  
    function permissionProfile($uid, $pid) {
        $querystring = "
SELECT * FROM brukerprofil 
WHERE (accountid = " . addslashes($uid) . " AND id = " . addslashes($pid) . ")";
//echo "<pre>" . $querystring . "</pre>";
        if ( $query = @pg_exec($this->connection, $querystring) ) { 
            if (pg_numrows($query) > 0 ) return true;
        }
        return false;
    }
    
    function permissionEquipmentGroup($uid, $id) {
        $querystring = "
SELECT * FROM utstyrgruppe 
WHERE (accountid = " . addslashes($uid) . " AND id = " . addslashes($id) . ")";
//echo "<pre>" . $querystring . "</pre>";
        if ( $query = @pg_exec($this->connection, $querystring) ) { 
            if (pg_numrows($query) > 0 ) return true;
        }
        return false;
    }    
    
    
    function permissionEquipmentFilter($uid, $id) {
        $querystring = "
SELECT * FROM utstyrfilter 
WHERE (accountid = " . addslashes($uid) . " AND id = " . addslashes($id) . ")";
//echo "<pre>" . $querystring . "</pre>";
        if ( $query = @pg_exec($this->connection, $querystring) ) { 
            if (pg_numrows($query) > 0 ) return true;
        }
        return false;
    }       
    
  
  function listBrukere($sort) {
    
    $brukere = NULL;
    
    $sorts = array ('login',
		    'name',
		    'name',
		    'name',
		    'queuelength, name',
		    'pa, name',
		    'aa, name');
		    
    $querystring = "SELECT Account.id, Account.login, Account.name, null as admin, null as sms, Preference.queuelength, " .
      "profiler.pa, adresser.aa FROM Preference, Account LEFT OUTER JOIN " .
      "(SELECT count(Brukerprofil.id) AS pa, Brukerprofil.accountid AS uid " . 
      "FROM Brukerprofil GROUP BY (Brukerprofil.accountid)) AS profiler ON (Account.id = profiler.uid) " .
      "LEFT OUTER JOIN " .
      "(SELECT count(Alarmadresse.id) AS aa, Alarmadresse.accountid AS uid " .
      "FROM Alarmadresse GROUP BY (Alarmadresse.accountid)) AS adresser ON (Account.id = adresser.uid) " .
        "WHERE (Preference.accountid = Account.id) " .
      "ORDER BY " . $sorts[$sort];

    //echo "<p>Query: " . $querystring;
    if ( $query = @pg_exec($this->connection, $querystring) ) {
		$tot = pg_numrows($query); $row = 0;

	while ( $row < $tot) {
		$data = pg_fetch_array($query, $row, PGSQL_ASSOC);
		$brukere[$row][0] = $data["id"];
		$brukere[$row][1] = $data["login"];
		$brukere[$row][2] = $data["name"];
		$brukere[$row][3] = $data["admin"];
		$brukere[$row][4] = $data["sms"];
		$brukere[$row][5] = $data["pa"];
		$brukere[$row][6] = $data["aa"];
		$brukere[$row][7] = $data["queuelength"];
		$row++;
      } 
    }  else {
      $error = new Error(2);
      $bruker{'errmsg'}= "Feil med datbasesp酶rring.";
    }
    
    return $brukere;
  }


	// lister alle brukerne og om de tilh酶rer en bestemt gruppe
	function listGrBrukere($gid, $sort) {
    
    	$brukere = NULL;
    
   		$sorts = array ('login',
			'name',
		    'name, login');
		    
    	$querystring = "SELECT id, login, name, (Medlem.groupid > 0) AS medlem 
FROM Account LEFT OUTER JOIN ( 
	SELECT groupid, accountid 
	FROM AccountInGroup 
	WHERE (groupid = " . addslashes($gid) . ") 
) AS Medlem 
ON (Account.id = Medlem.accountid) 
ORDER BY " . $sorts[$sort];

    //echo "<p>Query: " . $querystring;

    if ( $query = @pg_exec($this->connection, $querystring) ) {
		$tot = pg_numrows($query); $row = 0;

	while ( $row < $tot) {
		$data = pg_fetch_array($query, $row, PGSQL_ASSOC);
		$brukere[$row][0] = $data["id"];
		$brukere[$row][1] = $data["login"];
		$brukere[$row][2] = $data["name"];
		$brukere[$row][3] = $data["medlem"];
		$row++;
      } 
    }  else {
      $error = new Error(2);
      $bruker{'errmsg'}= "Feil med datbasesp酶rring.";
    }
    
		return $brukere;
	}






  function listLogg($sort) {
    
    $logg = NULL;
    
    $sorts = array ('Logg.type, tid DESC',
		    'Account.name, tid DESC',
		    'tid DESC',
		    'Logg.descr, tid DESC');
		    
	$querystring = "SELECT Logg.type, Logg.descr, date_part('epoch', Logg.tid) AS tid, Account.name 
FROM Account, Logg 
WHERE (	Account.id = Logg.accountid ) 
ORDER BY " . $sorts[$sort] . " LIMIT 100";

	//print "<pre>" . $querystring . "</pre>";

    if ( $query = pg_exec($this->connection, $querystring) ) {
		$tot = pg_numrows($query); $row = 0;

	while ( $row < $tot) {
		$data = pg_fetch_array($query, $row, PGSQL_ASSOC);
		$logg[$row][0] = $data["type"];
		$logg[$row][1] = $data["descr"];
		$logg[$row][2] = $data["tid"];
		$logg[$row][3] = $data["name"];
		$row++;
      } 
    } 
    
    return $logg;
  }


    // Liste over filtermatch som er tilgjengelige for valg
  function listFilterMatchAdm($sort) {
    
    $fm = NULL;
    
    $sorts = array (
        'matchfieldid',
        'name',
        'valueid');
		    
	$querystring = "SELECT matchfieldid, name, valueid 
FROM MatchField 
ORDER BY " . $sorts[$sort];

	//print "<pre>" . $querystring . "</pre>";

    if ( $query = pg_exec($this->connection, $querystring) ) {
		$tot = pg_numrows($query); $row = 0;

	while ( $row < $tot) {
		$data = pg_fetch_array($query, $row, PGSQL_ASSOC);
		$fm[$row][0] = $data["matchfieldid"];
		$fm[$row][1] = $data["name"];
		$fm[$row][2] = $data["valueid"];
		$row++;
      } 
    } 
    
    return $fm;
  }




  function listBrukerGrupper($sort) {
    
    $brukere = NULL;
    
    $sorts = array ('name',
		    'ab, name',
		    'ar, name',
		    'ad, name',
		    'adf, name');
		    
	$querystring = "SELECT id, name, descr, BCount.ab, Rcount.ar, Dcount.ad, DFCount.adf  
FROM Accountgroup 
LEFT OUTER JOIN (
	SELECT count(accountid) AS ab, groupid
	FROM AccountInGroup
	GROUP BY groupid
) AS BCount 
ON (id = BCount.groupid)
LEFT OUTER JOIN (
	SELECT count(utstyrgruppeid) AS ar, accountgroupid 
	FROM Rettighet 
	GROUP BY accountgroupid 
) AS RCount 
ON (id = RCount.accountgroupid) 
LEFT OUTER JOIN (
	SELECT count(utstyrgruppeid) AS ad, accountgroupid
	FROM DefaultUtstyr 
	GROUP BY accountgroupid 
) AS DCount 
ON (id = DCount.accountgroupid) 
LEFT OUTER JOIN (
	SELECT count(utstyrfilterid) AS adf, accountgroupid
	FROM DefaultFilter 
	GROUP BY accountgroupid 	
) AS DFCount 
ON (id = DFCount.accountgroupid)    
ORDER BY " . $sorts[$sort];

	//print "<pre>" . $querystring . "</pre>";

    if ( $query = pg_exec($this->connection, $querystring) ) {
		$tot = pg_numrows($query); $row = 0;

	while ( $row < $tot) {
		$data = pg_fetch_array($query, $row, PGSQL_ASSOC);
		$brukere[$row][0] = $data["id"];
		$brukere[$row][1] = $data["name"];
		$brukere[$row][2] = $data["descr"];
		$brukere[$row][3] = $data["ab"];
		$brukere[$row][4] = $data["ar"];
		$brukere[$row][5] = $data["ad"];
		$brukere[$row][6] = $data["adf"];		
		$row++;
      } 
    }  else {
      $error = new Error(2);
      $bruker{'errmsg'}= "Feil med datbasesp酶rring.";
    }
    
    return $brukere;
  }


	// list alle gruppene en Account er medlem av.
  function listBrukersGrupper($uid, $sort) {
    
    $bruker = NULL;
    
    $sorts = array ('name',
		    'name',
		    'name',
		    'name',
		    'pa, name',
		    'aa, name');
		    
	$querystring = "SELECT id, name, descr 
FROM AccountGroup, AccountInGroup 
WHERE (AccountInGroup.groupid = Accountgroup.id) AND 
(AccountInGroup.accountid = " . $uid . ") 
ORDER BY name ";

    if ( $query = pg_exec($this->connection, $querystring) ) {
		$tot = pg_numrows($query); $row = 0;

		while ( $row < $tot) {
			$data = pg_fetch_array($query, $row, PGSQL_ASSOC);
			$grupper[$row][0] = $data["id"];
			$grupper[$row][1] = $data["name"];
			$grupper[$row][2] = $data["descr"];
			$row++;
      	}	 
    }  else {
      $error = new Error(2);
      $bruker{'errmsg'}= "Feil med datbasesp酶rring.";
    }
    
    return $grupper;
  }


	// Lister alle gruppene en Account er medlem av med avansert visning (tellere)
  function listBrukersGrupperAdv($sort, $uid) {
    
    $brukere = NULL;
    
    $sorts = array ('name',
		    'ab, name',
		    'ar, name',
		    'ad, name');
		    
	$querystring = "SELECT id, name, descr, BCount.ab, Rcount.ar, Dcount.ad, (Medlem.groupid > 0) AS medl 
FROM AccountGroup 
LEFT OUTER JOIN (
	SELECT count(accountid) AS ab, groupid
	FROM AccountInGroup
	GROUP BY groupid
) AS BCount 
ON (id = BCount.groupid)
LEFT OUTER JOIN (
	SELECT count(utstyrgruppeid) AS ar, accountgroupid 
	FROM Rettighet 
	GROUP BY accountgroupid 
) AS RCount 
ON (id = RCount.accountgroupid) 
LEFT OUTER JOIN (
	SELECT count(utstyrgruppeid) AS ad, accountgroupid
	FROM DefaultUtstyr 
	GROUP BY accountgroupid 
) AS DCount 
ON (id = DCount.accountgroupid) 
LEFT OUTER JOIN (
	SELECT accountid, groupid FROM AccountInGroup 
	WHERE (accountid = " . addslashes($uid) . ") 
) AS Medlem 
ON (id = Medlem.groupid) 
ORDER BY " . $sorts[$sort];

	//print "<pre>" . $querystring . "</pre>";

    if ( $query = pg_exec($this->connection, $querystring) ) {
		$tot = pg_numrows($query); $row = 0;

	while ( $row < $tot) {
		$data = pg_fetch_array($query, $row, PGSQL_ASSOC);
		$brukere[$row][0] = $data["id"];
		$brukere[$row][1] = $data["name"];
		$brukere[$row][2] = $data["descr"];
		$brukere[$row][3] = $data["ab"];
		$brukere[$row][4] = $data["ar"];
		$brukere[$row][5] = $data["ad"];
		$brukere[$row][6] = $data["medl"];
		$row++;
      } 
    }  else {
      $error = new Error(2);
      $bruker{'errmsg'}= "Feil med datbasesp酶rring.";
    }
    
    return $brukere;
  }


  function listAdresser($uid, $sort) {
    
    $adr = NULL;
    
    $sorts = array ('type, adresse', 'adresse');

    $querystring = "SELECT id, adresse, type " .
    	"FROM Alarmadresse " .
    	"WHERE (accountid = " . addslashes($uid) . ") " .
    	"ORDER BY " . $sorts[$sort];

    if ( $query = @pg_exec($this->connection, $querystring) ) {
      $tot = pg_numrows($query); $row = 0;

      while ( $row < $tot) {
		$data = pg_fetch_array($query, $row, PGSQL_ASSOC);
		$adr[$row][0] = $data["id"];
		$adr[$row][1] = $data["adresse"];
		$adr[$row][2] = $data["type"];
		$row++;
      } 
    }  else {
      $error = new Error(2);
      $bruker{'errmsg'}= "Feil med datbasesp酶rring.";
    }
    
    return $adr;
  }