<?php
/*
Sample Live Lookup with Active Directory implemented through a linked server.
Live Lookup documentation: http://support.helpspot.com/index.php?pg=kb.page&id=6
Fields passed in via $_GET:
customer_id
first_name
last_name
email
phone
*/

header('Content-type: text/xml');

// MSSQL Connection parameters
$myServer = "SERVERNAME";
$myUser = "USERNAME";
$myPass = "PASSWORD";
$myDB = "HelpSpot";

// Specify only those parameters we're interested in displaying
// These are the fields you will use below to create the Live Lookup XML
$attrs = array('givenname','sn','mail','samaccountname','company','telephonenumber','title');

/***** LIVE LOOKUP LOGIC *****/

//This is very simple logic. You could expand on this by adding a first name + last name serach, search by email domains, etc
if(!empty($_GET['customer_id'])){	//If an ID is passed in use that to make a direct lookup
	
	$filter = "AND samaccountname=''".$_GET['customer_id']."''";	
	
}elseif(!empty($_GET['email'])){			//If no ID then try email
	
	$filter = "AND mail=''".$_GET['email']."''";

}elseif(!empty($_GET['last_name'])){	//If no ID or email then search on last name
	
	$filter = "AND sn=''".$_GET['last_name']."''";
	
}elseif(!empty($_GET['first_name'])){	//Try first name if no ID,email,last name
	
	$filter = "AND givenname=''".$_GET['first_name']."''";	

}else{
	$filter = '';	//Return everyone
}

//connection to the database
$dbhandle = mssql_connect($myServer, $myUser, $myPass)
  or die("Couldn't connect to SQL Server on $myServer");

//select a database to work with
$selected = mssql_select_db($myDB, $dbhandle)
  or die("Couldn't open database $myDB");

mssql_query("SET ANSI_WARNINGS ON");
mssql_query("SET ANSI_NULLS ON");

//declare the SQL statement that will query the database
$query = "SELECT * FROM "; 
$query .= "OpenQuery(ADSI, 'SELECT givenname,sn,mail,samaccountname,company,telephonenumber,title FROM ''LDAP://DC=example,DC=com'' ";
$query .= "WHERE objectClass = ''User'' ".$filter." ')";

//echo $query;

//execute the SQL query and return records
$entries = mssql_query($query);

echo '<?xml version="1.0" encoding="utf-8"?>';
?>
<livelookup version="1.0" columns="first_name,last_name,email">
        <?php while($row = mssql_fetch_array($entries)) { ?>
        <customer>
                <customer_id><?php if(!empty($row['samaccountname'])) { echo $row['samaccountname']; } else { echo 'Not available'; }?></customer_id>
                <first_name><?php if(!empty($row['givenname'])) { echo $row['givenname']; } else { echo 'Not available'; }?></first_name>
                <last_name><?php if(!empty($row['sn'])) { echo $row['sn']; } else { echo 'Not available'; }?></last_name>
                <email><?php if(!empty($row['mail'])) { echo $row['mail']; } else { echo 'Not available'; }?></email>
                <phone><?php if(!empty($row['telephonenumber'])) { echo $row['telephonenumber']; } else { echo 'Not available'; }?></phone>
                <company><?php if(!empty($row['company'])) { echo $row['company']; } else { echo 'Not available'; }?></company>
                <title><?php if(!empty($row['title'])) { echo $row['title']; } else { echo 'Not available'; }?></title>
        </customer>
        <?php } ?>
</livelookup>

<?php mssql_close($dbhandle); ?>