test1/diff/myLDAP.php

<?php
// Update knei: impüortuser um objectGUID erweitert (analog SID angelegt)

/*
Examples:

include 'myldap.php';
$myldap = new MyLDAP;

$myldap->Login("praes", "vortrag");  -> TRUE
$myldap->Login("preas", "vortrag");  -> FALSE

$myldap->ImportUsers(); -> Import Users from LDAP to MySQL

$myldap->GetSid($username); -> Get SID from Username
$myldap->GetObjectGUID($username); -> Get object from Username (hex as str whitout separators)
$myldap->GetUsername($sid); -> Get Username from SID
$myldap->GetGroups($username); -> Get groups (comma separated) // username or sid
$myldap->IsUserInGroup($username, $groupname); return true or false

$myldap->GetUserInformation($username); -> will return an array  // username, displayname or sid is allowed
	array: $user["sid"] $user["username"] $user["email"] $user["displayname"] $user["description"] $user["title"] $user["forename"] $user["surname"]
	       $user["room"] $user["department"] $user["departmentnumber"] $user["telephone"] $user["mobilephone"] $user["manager"] $user["groups"]


$myldap->$myldap->GetUsersFromGroup("group"); -> will return an array with usernames
*/


class MyLDAP
{
    // Config
    protected $ldaphost    = "ads.iwmh.fraunhofer.de";
    protected $ldapuser    = "unix";
    protected $ldappasswd  = "tunix+-";
    protected $ldapbasedn  = "CN=Users,DC=iwmh,DC=fraunhofer,DC=de";

    protected $mysqlhost   = "localhost";
    protected $mysqldb     = "adsiwmh";
    protected $mysqluser   = "unix";
    protected $mysqlpasswd = "tunix+-";

    protected $ignoreUsers = "Administrator,opsihelper,mailer,unix,ASPNET,netapp-ad,ILS_ANONYMOUS_US,IWAM_SRV-ADS1,standard,Gast,dirxml-user,pcpatch,orgadmin,blackberry,Johnson.Controls,olat-admin,pwdsync,bereitschaft,SP_Dienste,Xpression";


    public function Login($username, $passwd)
    {
        $ldaphost = $this->ldaphost;
        $ldapbasedn = $this->ldapbasedn;

        if ( ($username == "") || ($username == "NULL") || ($username == null)) return false;

        $attribut = array("displayname", "sAMAccountName");
        $username = str_replace("IWMH\\", "", $username);
        $username = str_replace("Iwmh\\", "", $username);
        $ldapuser = $username."@iwmh.fhg.de";
        $ldappasswd = $passwd;
        $ldapquery = "(&(objectClass=Person)(!(userAccountControl:1.2.840.113556.1.4.803:=2))(sAMAccountName=$username))";

        $ds = ldap_connect("LDAP://".$ldaphost);

        if (!$ds) exit ("Verbindung zum LDAP Server nicht m?glich");

        if (!ldap_set_option($ds, LDAP_OPT_PROTOCOL_VERSION, 3)) exit ("LDAP: Kann das Protokoll nicht auf Version 3 setzen");

        $ldapbind = ldap_bind($ds, $ldapuser, $ldappasswd);
        if (!$ldapbind)	return false;

        $sr = ldap_search($ds, $ldapbasedn, $ldapquery);
        if (!$sr) exit ("LDAP search fehlgeschlagen...");

        for ($entry=ldap_first_entry($ds,$sr), $i=0; $entry!=false; $entry=ldap_next_entry($ds,$entry), $i++)
        {
            for ($j=0; $j<count($attribut); $j++)
            {
                $tmp = ldap_get_values($ds, $entry, $attribut[$j]);
                $info[$i][$j] = $tmp[0];
            }
        }

        ldap_close($ds);

        for ($i=0; $i<count($info); $i++)
        {
            if ((strcasecmp($info[$i][1], $username) == 0))
                return true;
        }

        return false;
    }

    public function GetSid($username)
    {
        if ($username == "") return "";

        $ldaphost   = $this->ldaphost;
        $ldapbasedn = $this->ldapbasedn;
        $ldapuser   = $this->ldapuser;
        $ldappasswd = $this->ldappasswd;

//		echo "GetSid:".$username."\n";

        $ds = ldap_connect("LDAP://".$ldaphost);
        ldap_set_option($ds, LDAP_OPT_PROTOCOL_VERSION, 3);
        ldap_set_option($ds, LDAP_OPT_REFERRALS, 0);

        $ldapbind = ldap_bind($ds, $ldapuser, $ldappasswd);
        if (!$ldapbind)	return "ERROR";

//		echo $username."\n";

        $sr = ldap_search($ds, $ldapbasedn, "(sAMAccountName=".$username.")");
        $entries = ldap_get_entries($ds, $sr);

        if (is_bool($sr)) echo "GetSid:".$username."\n";

        if (count($entries) == 2)
        {
            // All SID's begin with S-
            $sid = "S-"; // Convert Bin to Hex and split into byte chunks
            $sidinhex = str_split(bin2hex($entries[0]['objectsid'][0]), 2); // Byte 0 = Revision Level
            if (count($sidinhex) == 28)
            {
                $sid = $sid.hexdec($sidinhex[0])."-"; // Byte 1-7 = 48 Bit Authority
                $sid = $sid.hexdec($sidinhex[6].$sidinhex[5].$sidinhex[4].$sidinhex[3].$sidinhex[2].$sidinhex[1]); // Byte 8 count of sub authorities - Get number of sub-authorities
                $subauths = hexdec($sidinhex[7]); //Loop through Sub Authorities

                for($i = 0; $i < $subauths; $i++)
                {
                    $start = 8 + (4 * $i); // X amount of 32Bit (4 Byte) Sub Authorities
                    $sid = $sid."-".hexdec($sidinhex[$start+3].$sidinhex[$start+2].$sidinhex[$start+1].$sidinhex[$start]);
                }
            }
        }
        else
            return "S-0-0";
        return $sid;
    }

    public function getObjectGUID($username)
    {
        if ($username == "") return "";

        $ldaphost   = $this->ldaphost;
        $ldapbasedn = $this->ldapbasedn;
        $ldapuser   = $this->ldapuser;
        $ldappasswd = $this->ldappasswd;

        $ds = ldap_connect("LDAP://".$ldaphost);
        ldap_set_option($ds, LDAP_OPT_PROTOCOL_VERSION, 3);
        ldap_set_option($ds, LDAP_OPT_REFERRALS, 0);

        $ldapbind = ldap_bind($ds, $ldapuser, $ldappasswd);
        if (!$ldapbind)	return "ERROR";

        $sr = ldap_search($ds, $ldapbasedn, "(sAMAccountName=".$username.")");
        $entries = ldap_first_entry($ds, $sr);

        $object_guid = ldap_get_values_len($ds, $entries, 'objectGUID');

        if(count($object_guid) ==2)
            $result = strtoupper(bin2hex($object_guid[0]));
        else
            $result = "0";

        return $result;
    }

    public function GetwhenCreated($username)
    {
        if ($username == "") return "";

        $ldaphost   = $this->ldaphost;
        $ldapbasedn = $this->ldapbasedn;
        $ldapuser   = $this->ldapuser;
        $ldappasswd = $this->ldappasswd;

        $ds = ldap_connect("LDAP://".$ldaphost);
        ldap_set_option($ds, LDAP_OPT_PROTOCOL_VERSION, 3);
        ldap_set_option($ds, LDAP_OPT_REFERRALS, 0);

        $ldapbind = ldap_bind($ds, $ldapuser, $ldappasswd);
        if (!$ldapbind)	return "ERROR";

        $sr = ldap_search($ds, $ldapbasedn, "(sAMAccountName=".$username.")");
        $entries = ldap_first_entry($ds, $sr);

        $whenCreated = ldap_get_values($ds, $entries, 'whenCreated');

        if(count($whenCreated) ==2)
        {
            $timeStrInUTC = preg_replace("/(\d{4})(\d{2})(\d{2})(\d{2})(\d{2})(\d{2}).+/","$1-$2-$3 $4:$5:$6", $whenCreated[0]);
            $timeInUTC = strtotime($timeStrInUTC.' UTC');
            $result = date("Y-m-d H:i:s", $timeInUTC);
        }
        else

            $result = "0";

        return $result;
    }

    private function Ldap_Query($ldapquery, $attribut)
    {
        $ldaphost   = $this->ldaphost;
        $ldapbasedn = $this->ldapbasedn;
        $ldapuser   = $this->ldapuser;
        $ldappasswd = $this->ldappasswd;

        $ds = ldap_connect("LDAP://".$ldaphost);
        $ldapbind = ldap_bind($ds, $ldapuser, $ldappasswd);
        $sr = ldap_search($ds, $ldapbasedn, $ldapquery);
        $i=0;
        $j=0;
        for ($entry=ldap_first_entry($ds,$sr), $i=0; $entry!=false; $entry=ldap_next_entry($ds,$entry), $i++)
        {
            $test = ldap_get_attributes($ds, $entry);
            for ($j=0; $j<count($attribut); $j++)
            {
                if(array_search($attribut[$j], $test))
                    $tmp = ldap_get_values($ds, $entry, $attribut[$j]);
                else
                    $tmp = array("");

                $ldap_result[$i][$attribut[$j]] = "";	// set array
                for($k=0; $k<count($tmp); $k++)
                {
                    if (isset($tmp[$k])) $ldap_result[$i][$attribut[$j]] .= $tmp[$k];
                }

                $ldap_result[$i][$attribut[$j]] = str_replace("CN=Builtin,DC=iwmh,DC=fraunhofer,DC=de", "", $ldap_result[$i][$attribut[$j]]);  // trim groups
                $ldap_result[$i][$attribut[$j]] = str_replace($ldapbasedn, "", $ldap_result[$i][$attribut[$j]]); // trim groups
                $ldap_result[$i][$attribut[$j]] = str_replace(",CN=", ";", $ldap_result[$i][$attribut[$j]]);  // trim groups
                $ldap_result[$i][$attribut[$j]] = str_replace("CN=", "", $ldap_result[$i][$attribut[$j]]);  // trim groups
                $ldap_result[$i][$attribut[$j]] = str_replace("\,", ",", $ldap_result[$i][$attribut[$j]]);  // trim displaynames
                $ldap_result[$i][$attribut[$j]] = rtrim($ldap_result[$i][$attribut[$j]], ",");


                //echo $attribut[$j]." - ".$ldap_result[$i][$attribut[$j]]."<br>";
            }
        }
        ldap_close($ds);

        return $ldap_result;
    }


    private function MySQLConnect()
    {
        $mysqlhost   = $this->mysqlhost;
        $mysqldb     = $this->mysqldb;
        $mysqluser   = $this->mysqluser;
        $mysqlpasswd = $this->mysqlpasswd;

        $mysql = mysql_connect($mysqlhost, $mysqluser, $mysqlpasswd) or die("Keine MySQL-Verbindung: " . mysql_error());
        mysql_select_db($mysqldb, $mysql) or die("Auswahl der Datenbank fehlgeschlagen!"."<br>". mysql_error()."<br>DB:".$mysqldb);

        mysql_query("SET NAMES 'utf8'", $mysql);
        mysql_query("SET CHARACTER SET 'utf8'", $mysql);

        return $mysql;
    }

    private function CreateMySQLUserTable($ldap)
    {
//		$apos = "&apos;"; Apostroph ersetzen
        $apos = "\'";

        $mysql = $this->MySQLConnect();
        $ignoreUsers = explode(",",$this->ignoreUsers);
        $error = true;
        mysql_query("TRUNCATE adsuser;", $mysql); // Delete Data
        for($i=0; $i<count($ldap); $i++)
        {
            $import = true;
            if("" == $ldap[$i]["sAMAccountName"]) $import = false;
            if("" == $ldap[$i]["sid"]) $import = false;
            for($j=0; $j < count($ignoreUsers); $j++)
            {
                if($ignoreUsers[$j] == $ldap[$i]["sAMAccountName"]) $import = false;
            }

            if ($import == true)
            {

                $sqlquery  = "INSERT INTO `adsuser` (`sid`, `username`, `fhgusername`, `email`, `forename`, `surname`, `room`, `institute`, `department`, `departmentnumber`, `telephone`, `fax`, `mobilephone`, `displayname`,`groups`, `manager`, `description`,`title`, `employeeid`, `employeetype`, `accountexpires`, `lastlogin`, `whencreated`, `objectGUID`) VALUES";
                $sqlquery .= "('".$ldap[$i]["sid"]."', ";
                $sqlquery .= "'".strtolower($ldap[$i]["sAMAccountName"])."', ";
                $sqlquery .= "'".$ldap[$i]["extensionAttribute6"]."', ";
                $sqlquery .= "'".strtolower(str_replace("'", $apos, $ldap[$i]["mail"]))."', ";
                $sqlquery .= "'".str_replace("'", $apos, $ldap[$i]["givenName"])."', ";
                $sqlquery .= "'".str_replace("'", $apos, $ldap[$i]["sn"])."', ";
                $sqlquery .= "'".$ldap[$i]["physicalDeliveryOfficeName"]."', ";
                $sqlquery .= "'".$ldap[$i]["extensionAttribute5"]."', ";
                $sqlquery .= "'".$ldap[$i]["department"]."', ";
                $sqlquery .= "'".$ldap[$i]["departmentNumber"]."', ";
                $sqlquery .= "'".$ldap[$i]["telephoneNumber"]."', ";
                $sqlquery .= "'".$ldap[$i]["facsimileTelephoneNumber"]."', ";
                $sqlquery .= "'".$ldap[$i]["mobile"]."', ";
                $sqlquery .= "'".str_replace("'", $apos, $ldap[$i]["displayName"])."', ";
                $sqlquery .= "'".$ldap[$i]["memberOf"]."', ";
                $sqlquery .= "'".$ldap[$i]["manager"]."', ";
                $sqlquery .= "'".str_replace("'", $apos, $ldap[$i]["description"])."', ";
                $sqlquery .= "'".$ldap[$i]["personalTitle"]."', ";
                $sqlquery .= "'".$ldap[$i]["employeeID"]."', ";
                $sqlquery .= "'".$ldap[$i]["employeeType"]."', ";

                // ADS Eintrag (nie) abfangen
                if ($ldap[$i]["accountExpires"] != "" and $ldap[$i]["accountExpires"] != "0" and $ldap[$i]["accountExpires"] != "9223372036854775807")
                    $sqlquery .= "'".date('Y-m-d H:i:s', $ldap[$i]["accountExpires"] / 10000000 - 11644473600)."', ";  // ADS Epoch -> Unix Timestamp
                else
                    $sqlquery .= "NULL, ";

                // ADS Eintrag (nie) abfangen
                if ($ldap[$i]["lastLogon"] != "" and $ldap[$i]["lastLogon"] != "0" and $ldap[$i]["lastLogon"] != "9223372036854775807")
                    $sqlquery .= "'".date('Y-m-d H:i:s', $ldap[$i]["lastLogon"]  / 10000000 - 11644473600)."', "; // ADS Epoch -> Unix Timestamp
                else
                    $sqlquery .= "NULL, ";
                $sqlquery .= "'".$ldap[$i]["whenCreated"]."', ";
                $sqlquery .= "'".$ldap[$i]["objectGUID"]."'); ";

//				$sqlquery .= "'".date('Y-m-d H:i:s', $ldap[$i]["accountExpires"])."', ";
//				$sqlquery .= "'".date('Y-m-d H:i:s', $ldap[$i]["lastLogon"])."'); ";
                $sqlquery = utf8_encode($sqlquery);

                //echo $sqlquery."\n\n";

                if (!mysql_query($sqlquery, $mysql))
                {
                    error_log("ERROR:".$sqlquery."".mysql_error()."<br>");
                    $error = false;
                }
            }

        }

        return $error;
    }

    private function CreateMySQLGroupTable($ldap)
    {
        $mysql = $this->MySQLConnect();

//		$apos = "&apos;";
        $apos = "\'";

        $error = true;
        mysql_query("TRUNCATE adsgroup;", $mysql); // Delete Data
        for($i=0; $i<count($ldap); $i++)
        {
            if($ldap[$i]["sid"] != "S-0-0")
            {
                $sqlquery  = "INSERT INTO `adsgroup` (`sid`, `name`, `members`, `description`, `email`) VALUES";
                $sqlquery .= "('".$ldap[$i]["sid"]."', ";
                $sqlquery .= "'".str_replace("'", $apos, $ldap[$i]["name"])."', ";
                $sqlquery .= "'".str_replace("'", $apos, $ldap[$i]["member"])."', ";

                $sqlquery .= "'".$ldap[$i]["description"]."', ";
                $sqlquery .= "'".$ldap[$i]["mail"]."'); ";
                $sqlquery = utf8_encode($sqlquery);

                if (!mysql_query($sqlquery, $mysql))
                {
                    error_log("ERROR:".$sqlquery."".mysql_error()."<br>");
                    $error = false;
                }
            }
        }
        return $error;
    }

    public function ImportUsers()
    {
        $ldapquery = "(&(objectClass=Person)(!(userAccountControl:1.2.840.113556.1.4.803:=2))(cn=*))";
        $search = array("sAMAccountName", "extensionAttribute6", "mail", "givenName", "sn", "physicalDeliveryOfficeName", "extensionAttribute5", "department", "departmentNumber", "description", "telephoneNumber", "mobile", "memberOf", "title", "personalTitle", "manager", "displayName", "sn", "employeeID", "employeeType", "lastLogon", "accountExpires", "facsimileTelephoneNumber");

        // Import Groups first
        $ldap_groups = $this->ImportGroups();
        // Query Attributes
        $ldap_result = $this->Ldap_Query($ldapquery, $search);

        if (count($ldap_result) < 100) die();  // LDAP-Error

        // get SIDs
        for($i=0; $i<count($ldap_result); $i++)
        {
            // Formatierung verschiedener Attribute
            $ldap_result[$i]["sid"] = $this->GetSid($ldap_result[$i]["sAMAccountName"]);
            $ldap_result[$i]["objectGUID"] = $this->GetObjectGUID($ldap_result[$i]["sAMAccountName"]);
            $ldap_result[$i]["whenCreated"] = $this->GetwhenCreated($ldap_result[$i]["sAMAccountName"]);

            // Get Groups recursive
            $group = explode (",", $ldap_result[$i]["memberOf"]);

            for($j=0; $j<count($group); $j++)
            {
                if (isset($ldap_groups[$group[$j]]))
                {
                    $group[$j] = $ldap_groups[$group[$j]];
                }
            }
            $memberOf = "";
            for($j=0; $j<count($group); $j++)
            {
                $memberOf .= $group[$j].";";
            }
            //echo "[] ".$ldap_result[$i]["sAMAccountName"]." -> ". $ldap_result[$i]["memberOf"]."<br>";
            //echo "[][] ".$ldap_result[$i]["sAMAccountName"]." -> ". $memberOf."<br>";
            // add recursive groups to string
            //$memberOf = rtrim($memberOf, ",");
            $ldap_result[$i]["memberOf"] = $memberOf;
        }
        //var_dump($ldap_result);

        $error = $this->CreateMySQLUserTable($ldap_result);
        $this->UpdateSIDs();
        return $error;
    }

    public function getDeletedUsers()
    {
        $ldapquery = "(&(objectClass=Person)(userAccountControl:1.2.840.113556.1.4.803:=2)(cn=*))";
        $search = array("sAMAccountName");

        // Query Attributes
        $ldap_result = $this->Ldap_Query($ldapquery, $search);

        return $ldap_result;
    }

    public function ImportGroups()
    {
        $ldapquery = "(&(objectClass=Group)(!(userAccountControl:1.2.840.113556.1.4.803:=2))(cn=*))";
        $search = array("sAMAccountName", "memberOf", "member", "mail", "description");

        // Query Attributes
        $ldap_result = $this->Ldap_Query($ldapquery, $search);

        for($i=0; $i<count($ldap_result); $i++)
        {
            if (!$ldap_result[$i]["memberOf"] == "")
            {
                // add recursive groups
                $group_result[$ldap_result[$i]["sAMAccountName"]] = $ldap_result[$i]["sAMAccountName"].",".$ldap_result[$i]["memberOf"];
                //echo $ldap_result[$i]["sAMAccountName"]." - ".$ldap_result[$i]["memberOf"]." - ".$ldap_result[$i]["member"]."<br>";
            }
            //echo $ldap_result[$i]["sAMAccountName"]." -> ".$this->GetSid($ldap_result[$i]["sAMAccountName"])."<br>";
            $group_map[$i]["name"] =  $ldap_result[$i]["sAMAccountName"];
            $group_map[$i]["member"] = $ldap_result[$i]["member"];
            $group_map[$i]["mail"] = $ldap_result[$i]["mail"];
            $group_map[$i]["description"] = $ldap_result[$i]["description"];
            $group_map[$i]["sid"] = $this->GetSid($ldap_result[$i]["sAMAccountName"]);
        }
        $this->CreateMySQLGroupTable($group_map);
        return $group_result;

    }


    private function UpdateSIDs()
    {
        $mysql = $this->MySQLConnect();

        // create mapping array
        $sql = "SELECT displayname, sid FROM adsuser;";
        $result = mysql_query ($sql, $mysql);
        while ( $row = mysql_fetch_row ($result))
        {
            $sidmap[$row[0]] = $row[1];
        }

        $sql = "SELECT name, sid FROM adsgroup;";
        $result = mysql_query ($sql, $mysql);
        while ( $row = mysql_fetch_row ( $result ) )
        {
            $sidmap[$row[0]] = $row[1];
        }

        // Update SIDs in Grouptable
        $sql = "SELECT sid, members FROM adsgroup;";
        $i = 0;
        $sids="";
        $result = mysql_query ($sql, $mysql);
        while ( $row = mysql_fetch_row ( $result ) )
        {
            $names = explode(";", $row[1]);
            for ($j = 0; $j < count($names); $j++)
            {
                if(isset($sidmap[$names[$j]]))
                {
                    $sids .= $sidmap[$names[$j]].";";
                }
            }
            $sids = rtrim($sids, ";");
            $sql1[$i] = "UPDATE adsgroup SET membersids='".$sids."' WHERE sid='".$row[0]."'";
            $i++;
            $sids = "";
        }

        for($j = 0; $j < count($sql1); $j++)
        {
            mysql_query ($sql1[$j], $mysql);
            //echo $sql1[$j]."<br><br>";
        }



        // Update SIDs in Usertable

        $sql = "SELECT sid, groups FROM adsuser;";
        $i = 0;
        $result = mysql_query ( $sql, $mysql);
        while ( $row = mysql_fetch_row ( $result ) )
        {


            $groups = explode(";", $row[1]);
            //var_dump($groups);
            //echo "<br>";
            for ($j = 0; $j < count($groups); $j++)
            {
                if(isset($sidmap[$groups[$j]]))
                {

                    $sids .= $sidmap[$groups[$j]].";";
                }
            }

            $sids = rtrim($sids, ";");
            $sql2[$i] = "UPDATE adsuser SET groupsids='".$sids."' WHERE sid='".$row[0]."'";
            $i++;
            $sids="";
        }

        for($j = 0; $j < count($sql2); $j++)
        {
            mysql_query ($sql2[$j], $mysql);
            //echo $sql2[$j]."<br><br>";
        }



    }


    /*

        ****************************

    */


    public function SQLQuery($sql)
    {
        $mysql = $this->MySQLConnect();

        $result = mysql_query($sql, $mysql);
        if ($result == false) error_log("ERROR: " . $sql);

        if (!is_resource($result)) return $result;

        $i = 0;
        while ($row = mysql_fetch_array($result))
        {
            $array[$i] = $row;
            $i++;
        }
        if (!isset($array)) return "";
        return $array;
    }



    // sid or username
    public function GetGroups($sid)
    {
        $mysql = $this->MySQLConnect();
        $sql = "SELECT groups, groupsids FROM adsuser WHERE username='".$sid."' OR sid='".$sid."';";
        $result = mysql_query ($sql, $mysql);

        while ( $row = mysql_fetch_row ( $result ) )
        {
            return $row[0].";".$row[1];
        }

        return "";
    }


    // alle Gruppen
    public function GetAllGroups()
    {
        $mysql = $this->MySQLConnect();
        $sql = "SELECT DISTINCT groupname FROM groupmembership ORDER BY groupname;";
        $result = mysql_query ($sql, $mysql);

        $i = 0;
        while ( $row = mysql_fetch_row ( $result ) )
        {
            $groups[$i] = $row[0];
            $i++;
        }

        return $groups;
    }

    // Username or SID
    public function IsUserInGroup($sid, $groupsid)
    {
        $mysql = $this->MySQLConnect();
//		$sql = "SELECT * FROM groupmembership WHERE user_sid='".$sid."' AND group_sid = '".$groupsid."';";
        $sql = "SELECT * FROM groupmembership WHERE (user_sid='".$sid."' OR username='".$sid."') AND (group_sid = '".$groupsid."' OR groupname = '".$groupsid."');";
        $result = mysql_query ($sql, $mysql);

        while ( $row = mysql_fetch_array ( $result ) )
        {
            return true;
        }
        return false;


        /*
                $groups = $this->GetGroups($sid);
                $group = explode (";", $groups);
                for ($i=0; $i<count($group); $i++)
                {
                    if ($group[$i] == $groupsid) return true;
                }
                return false;
        */
    }

    public function GetUsername($sid)
    {
        $mysql = $this->MySQLConnect();
        $sql = "SELECT username FROM adsuser WHERE sid='".$sid."';";
        $result = mysql_query ($sql, $mysql);

        while ( $row = mysql_fetch_row ( $result ) )
        {
            return $row[0];
        }
        return "";
    }

    public function GetDisplayname($sid)
    {
        $mysql = $this->MySQLConnect();
        $sql = "SELECT displayname FROM adsuser WHERE sid='".$sid."';";
        $result = mysql_query ($sql, $mysql);

        while ( $row = mysql_fetch_row ( $result ) )
        {
            return $row[0];
        }
        return "";
    }

    public function GetTelephone($sid)
    {
        $mysql = $this->MySQLConnect();
        $sql = "SELECT telephone FROM adsuser WHERE sid='".$sid."';";
        $result = mysql_query ($sql, $mysql);

        while ( $row = mysql_fetch_row ( $result ) )
        {
            return $row[0];
        }
        return "";
    }

    public function GetUserData($sids)
    {
        $mysql = $this->MySQLConnect();

        foreach ($sids as $sid)
        {
            $sql = "SELECT username, display FROM adsuser WHERE sid='".$sid."';";
            $result = mysql_query ($sql, $mysql);

            while ( $row = mysql_fetch_row ( $result ) )
            {
                return $row[0];
            }
        }
        return "";

    }


    public function GetTransponder($sid)
    {
        $mysql = $this->MySQLConnect();
        $sql = "SELECT transponder.id FROM transponder, adsuser WHERE adsuser.displayname = transponder.name AND (adsuser.username='".$sid."' OR adsuser.sid='".$sid."');";
        $result = mysql_query ($sql, $mysql);

        while ( $row = mysql_fetch_row ( $result ) )
        {
            return $row[0];
        }

        return "";
    }


    public function GetSIDfromGroups()
    {
        $mysql = $this->MySQLConnect();
        $sql = "SELECT sid, name FROM adsgroup";
        $result = mysql_query($sql, $mysql);

        $groupmap = "";
        while ($row = mysql_fetch_row($result))
        {
            $groupmap[$row[1]] = $row[0];
        }

        return $groupmap;
    }

    // SID, Username or Displayname
    /*    public function GetUserInformation($sid)
        {
            $mysql = $this->MySQLConnect();
            $sql  = "SELECT sid, username, email, displayname, description, title, forename, surname, room, department, departmentnumber, telephone, mobilephone, manager, groups, employeeid  FROM adsuser ";
            $sql .= "WHERE username='".$sid."' OR sid='".$sid."' OR displayname='".$sid."';";



            $result = mysql_query($sql, $mysql);
            if ($result == false) error_log("ERROR: " . $sql);

            while ($row = mysql_fetch_row($result))
            {
                $userdata["sid"] = $row[0];
                $userdata["username"] = $row[1];
                $userdata["email"] = $row[2];
                $userdata["displayname"] = $row[3];
                $userdata["description"] = $row[4];
                $userdata["title"] = $row[5];
                $userdata["forename"] = $row[6];
                $userdata["surname"] = $row[7];
                $userdata["room"] = $row[8];
                $userdata["department"] = $row[9];
                $userdata["departmentnumber"] = $row[10];
                $userdata["telephone"] = $row[11];
                $userdata["mobilephone"] = $row[12];
                $userdata["manager"] = $row[13];
                $userdata["groups"] = $row[14];
                $userdata["employeeid"] = $row[15];

                return $userdata;
            }
            return false;
        }
    */
    public function GetUserInformation($sid)
    {
        $mysql = $this->MySQLConnect();
        $sql  = "SELECT sid, username, email, displayname, description, title, forename, surname, room, department, departmentnumber, telephone, mobilephone, manager,";
        $sql .= "groups, employeeid, position_de, position_en, proxy, proxy2, secretary, transponder, access, employeetype, institute, accountexpires, lastlogin FROM adsuser ";
        $sql .= "WHERE username='".$sid."' OR sid='".$sid."' OR displayname='".$sid."';";

        $result = mysql_query($sql, $mysql);
        if ($result == false) error_log("ERROR: " . $sql);

        while ($row = mysql_fetch_row($result))
        {
            $userdata["sid"] = $row[0];
            $userdata["username"] = $row[1];
            $userdata["email"] = $row[2];
            $userdata["displayname"] = $row[3];
            $userdata["description"] = $row[4];
            $userdata["title"] = $row[5];
            $userdata["forename"] = $row[6];
            $userdata["surname"] = $row[7];
            $userdata["room"] = $row[8];
            $userdata["department"] = $row[9];
            $userdata["departmentnumber"] = $row[10];
            $userdata["telephone"] = $row[11];
            $userdata["mobilephone"] = $row[12];
            $userdata["manager"] = $row[13];
            $userdata["groups"] = $row[14];
            $userdata["employeeid"] = $row[15];
            $userdata["position_de"] = $row[16];
            $userdata["position_en"] = $row[17];
            $userdata["proxy"] = $row[18];
            $userdata["proxy2"] = $row[19];
            $userdata["secretary"] = $row[20];
            $userdata["transponder"] = $row[21];
            $userdata["access"] = $row[22];
            $userdata["employeetype"] = $row[23];
            $userdata["institute"] = $row[24];
            $userdata["accountexpires"] = $row[25];
            $userdata["lastlogin"] = $row[26];

            return $userdata;
        }
        return false;
    }

    public function GetGroupInformation($sid)
    {
        $mysql = $this->MySQLConnect();
        $sql  = "SELECT sid, name, email, description FROM adsgroup ";
        $sql .= "WHERE name='".$sid."' OR sid='".$sid."';";

        $result = mysql_query($sql, $mysql);
        if ($result == false) error_log("ERROR: " . $sql);

        while ($row = mysql_fetch_row($result))
        {
            $userdata["sid"] = $row[0];
            $userdata["name"] = $row[1];
            $userdata["email"] = $row[2];
            $userdata["description"] = $row[3];
            return $userdata;
        }
        return false;
    }

    // sid or name
    public function GetUserInformationFromGroup($group)
    {
        $mysql = $this->MySQLConnect();
        $sql  = "SELECT sid, username, email, displayname, description, title, forename, surname, room, department, departmentnumber, telephone, mobilephone, manager, groups  FROM adsuser ";
        $sql .= "WHERE groups LIKE '%".$group."%' OR groupsids LIKE '%".$group."%' ORDER BY displayname;";
        $result = mysql_query($sql, $mysql);
        if ($result == false) echo "ERROR: " . $sql;

        $userdata[0][0] = "";
        $i = 0;
        while ($row = mysql_fetch_row($result))
        {
            $userdata[$i]["sid"] = $row[0];
            $userdata[$i]["username"] = $row[1];
            $userdata[$i]["email"] = $row[2];
            $userdata[$i]["displayname"] = $row[3];
            $userdata[$i]["description"] = $row[4];
            $userdata[$i]["title"] = $row[5];
            $userdata[$i]["forename"] = $row[6];
            $userdata[$i]["surname"] = $row[7];
            $userdata[$i]["room"] = $row[8];
            $userdata[$i]["department"] = $row[9];
            $userdata[$i]["departmentnumber"] = $row[10];
            $userdata[$i]["telephone"] = $row[11];
            $userdata[$i]["mobilephone"] = $row[12];
            $userdata[$i]["manager"] = $row[13];
            $userdata[$i]["groups"] = $row[14];
            $i++;
        }
        return $userdata;
    }

    // sid or name
    private function GetMembersFromGroup($group)
    {
        $mysql = $this->MySQLConnect();
        $sql = "SELECT membersids FROM adsgroup WHERE name = '".$group."' OR sid = '".$group."'";
        $result = mysql_query($sql, $mysql);

        $j = 0;
        while ( $row = mysql_fetch_row ( $result ) )
        {
            $members = $row[0];
            $j++;
        }
        if ($j > 0)
        {
            $user = explode(";", $members);
            for ($i = 0; $i<count($user); $i++)
            {
                $groups = $this->GetMembersFromGroup($user[$i]);
                if ($groups != null)
                {
                    $user[$i] = $groups;
                }
            }

            return $user;
        }
        return null;

    }

    // sid or name
    public function GetUserSidsFromGroup($group)
    {
        $user = $this->GetMembersFromGroup($group);
        return explode(";",$this->MergeArray($user));
    }

    // sid
    public function GetUsersFromGroup($group)
    {
        $mysql = $this->MySQLConnect();
        $sql  = "SELECT DISTINCT group_sid, groupname, user_sid, displayname, username FROM groupmembership ";
        $sql .= "WHERE group_sid='".$group."' OR groupname='".$group."' ";
        $sql .= "ORDER BY displayname;";
        $result = mysql_query($sql, $mysql);
        $i = 0;
        $user[$i] = "";
        while ( $row = mysql_fetch_row ( $result ) )
        {
            if ($row[0] != "")
            {
                $user[$i]["sid"] = $row[2];
                $user[$i]["displayname"] = $row[3];
                $user[$i]["username"] = $row[4];
                $i++;
            }
        }

        return $user;
    }

    public function GetDepartmentNumbers()
    {
        $mysql = $this->MySQLConnect();
        $sql = "SELECT DISTINCT departmentnumber, department FROM adsuser ORDER BY departmentnumber;";
        $result = mysql_query($sql, $mysql);

        $i = 0;
        $user[$i] = "";
        while ( $row = mysql_fetch_row ( $result ) )
        {
            if ($row[0] != "")
            {
                $user[$i]["nr"] = $row[0];
                $user[$i]["name"] = $row[1];
                $i++;
            }
        }

        return $user;
    }

    public function GetUsersFromDepartmentNumber($departmentNumber)
    {
        $mysql = $this->MySQLConnect();
        $sql = "SELECT DISTINCT username, displayname, sid FROM adsuser WHERE departmentnumber = ".$departmentNumber.";";
        $result = mysql_query($sql, $mysql);

        $i = 0;
        $user[$i] = "";
        while ( $row = mysql_fetch_row ( $result ) )
        {
            $user[$i]["username"] = $row[0];
            $user[$i]["displayname"] = $row[1];
            $user[$i]["sid"] = $row[2];
            $i++;
        }

        return $user;
    }


    public function GetRooms()
    {
        $mysql = $this->MySQLConnect();
        $sql = "SELECT * FROM telephone ORDER BY roomnr;";
        $result = mysql_query($sql, $mysql);

        $i = 0;
        $room[$i] = "";
        while ( $row = mysql_fetch_row ( $result ) )
        {
            $room[$i]["id"] = $row[0];
            $room[$i]["roomnr"] = $row[1];
            $room[$i]["description"] = $row[2];
            $room[$i]["telephonenr"] = $row[3];
            $i++;
        }

        return $room;
    }

    public function GetRoom($id)
    {
        $mysql = $this->MySQLConnect();
        $sql = "SELECT * FROM telephone WHERE id = $id;";
        $result = mysql_query($sql, $mysql);

        $i = 0;
        $room = "";
        if ( $row = mysql_fetch_row ( $result ) )
        {
            $room["id"] = $row[0];
            $room["roomnr"] = $row[1];
            $room["description"] = $row[2];
            $room["telephonenr"] = $row[3];
        }

        return $room;
    }

    public function GetUserAccessFromDepartmentNumber($departmentNumber, $accessrights)
    {
        $mysql = $this->MySQLConnect();
        $sql = "SELECT DISTINCT username, displayname, sid FROM adsuser WHERE departmentnumber = ".$departmentNumber." AND access = '".$accessrights."';";
        $result = mysql_query($sql, $mysql);

        $i = 0;
        $user[$i] = "";
        while ( $row = mysql_fetch_row ( $result ) )
        {
            $user[$i]["username"] = $row[0];
            $user[$i]["displayname"] = $row[1];
            $user[$i]["sid"] = $row[2];
            $i++;
        }

        return $user;
    }

    private function MergeArray($array)
    {
        $merged = "";
        for($i=0; $i<count($array); $i++)
        {
            if (!is_array($array[$i]))
            {
                $merged .= $array[$i].";";
            }
            else
            {
                $merged .= $this->MergeArray($array[$i]).";";
            }

        }
        $merged = str_replace(";;",";", $merged);
        $merged = trim($merged, ";");
        return $merged;
    }

    public function GetUserMap($group)
    {
        $mysql = $this->MySQLConnect();
        $sql = "SELECT username, user_sid FROM groupmembership WHERE groupname = '".$group."';";
        //error_log($sql);
        $result = mysql_query($sql, $mysql);
        while ( $row = mysql_fetch_row ( $result ) )
        {
            if ($row[0] != "")
            {
                $user[$row[1]] = $row[0];
            }
        }

        return $user;



    }


    public function UpdateGroupMembership()
    {
        $apos = "\'";

        $mysql = $this->MySQLConnect();

        $groups = $this->GetSIDfromGroups();

        $sql = "SELECT sid, username, displayname FROM adsuser;";

        $result = mysql_query($sql, $mysql);
        while ( $row = mysql_fetch_row ( $result ) )
        {
            $usersid[$row[0]]["username"] = $row[1];
            $usersid[$row[0]]["displayname"] = str_replace("'", $apos, $row[2]);
        }


        $sql = "SELECT sid, name, description FROM adsgroup;";
        $result = mysql_query($sql, $mysql);
        while ( $row = mysql_fetch_row ( $result ) )
        {
            $groupsid[$row[0]]["name"] = $row[1];
            $groupsid[$row[0]]["description"] = $row[2];
        }


        $sql = "TRUNCATE groupmembership;";
        mysql_query($sql, $mysql);

        foreach ($groups as $group)
        {
            $users = $this->GetUserSidsFromGroup($group);
            foreach ($users as $user)
            {
                if (isset($usersid[$user]["username"]))
                {
                    $sql =  "INSERT INTO groupmembership (group_sid, groupname, user_sid, username, displayname, description) ";
                    $sql .= "VALUES ('".$group."', '".$groupsid[$group]["name"]."', '".$user."', '".$usersid[$user]["username"]."', '".$usersid[$user]["displayname"]."', '".$groupsid[$group]["description"]."');";
                    //if ($usersid[$user]["username"] == "mllu") echo $sql."\n";
                    mysql_query($sql, $mysql);
                }
            }
        }

    }

    public function UpdatePosition()
    {
        $mysql = $this->MySQLConnect();

        $sql  = "SELECT position.sid, position.position_de, position.position_en, position.proxy, position.proxy2, position.access, position.manager FROM adsuser, position WHERE adsuser.sid = position.sid ";
        $result = mysql_query($sql, $mysql);
        while ( $row = mysql_fetch_row ( $result ) )
        {
            $proxy = $this->GetDisplayname($row[3]);
            $proxy2 = $this->GetDisplayname($row[4]);
            $manager = $this->GetDisplayname($row[6]);

            $sql =  "UPDATE adsuser SET position_de = '".$row[1]."', position_en = '".$row[2]."', proxy = '".$proxy."', proxy2 = '".$proxy2."', access = '".$row[5]."', manager = '".$manager."' ";
            $sql .= "WHERE sid = '".$row[0]."';";
            //echo $sql."\n";
            mysql_query($sql, $mysql);
        }

        $sql  = "SELECT DISTINCT adsuser.sid, adsuser.username, departments.secretary FROM departments, adsuser WHERE departments.oe = adsuser.departmentnumber AND departments.institute = adsuser.institute AND adsuser.departmentnumber != 'all';";
        $result = mysql_query($sql, $mysql);
        while ( $row = mysql_fetch_row ( $result ) )
        {
            $secretary = $this->GetDisplayname($row[2]);


            $sql =  "UPDATE adsuser SET secretary = '".$secretary."' WHERE sid = '".$row[0]."';";
            mysql_query($sql, $mysql);
        }

    }


    public function UpdatePresence()
    {
        $mysql = $this->MySQLConnect();
        $sql  = "SELECT * FROM presence;";
        $i = 0;
        $result = mysql_query($sql, $mysql);
        while ( $row = mysql_fetch_array ( $result ) )
        {
            if ($row["sigma"] != NULL or $row["manual"] != NULL)
                $sql =  "UPDATE adsuser SET present = 'N' ";
            else
                $sql =  "UPDATE adsuser SET present = 'Y' ";
            $sql .= "WHERE username = '".$row["username"]."';";
            //echo $sql."\n";
            mysql_query($sql, $mysql);
        }
    }

    public function UpdateTransponder()
    {
        $mysql = $this->MySQLConnect();

        $sql  = "SELECT transponder.id, adsuser.sid FROM transponder, adsuser WHERE adsuser.employeeid = transponder.employeeid AND adsuser.employeeid != ''";
        $result = mysql_query($sql, $mysql);
        while ( $row = mysql_fetch_row ( $result ) )
        {
            $sql =  "UPDATE adsuser SET transponder = '".$row[0]."' ";
            $sql .= "WHERE sid = '".$row[1]."';";
            //echo $sql."\n";
            mysql_query($sql, $mysql);
        }
    }

    public function Query($query)
    {
        $mysql = $this->MySQLConnect();

        if (isset($_GET["debug"])) echo $query;
        //error_log($query);
        $result = mysql_query($query,$mysql);
        $i = 0;
        while ($row = mysql_fetch_array($result))
        {
            $array[$i] = $row;
            $i++;
        }
        if (!isset($array)) return "";
        return $array;
    }


}

?>