Orca, I have added code to delete contacts who were added from the go_users table by this sync script, if they are no longer active in the go_users table or have been deleted from the go_users table, which means that any contacts added manually by the Owner of this addressbook should not be deleted.
This is the code that I am currently using, so it should work reasonably well.
I had found a few bugs from earlier posted code, so please review this and update if you have been using the above code. One bug was I forgot to sync the ZIP code, as stated earlier.
You may prefer to sync different User fields to the Global Addressbook than the fields that I have chosen, hopefully this is not too difficult for anyone. Our Users do not enter their personal (home) details, so I am using their accounts Personal details as their work address, and if this does not differ from their company details then their Company details should be the same data as their personal details. One day I intend to write code which automatically copies from their Personal details to their Company details, if their company details are not entered.
- Code: Select all
<?php
/* Connect to database */
$host = "localhost"; //localhost or the name of your MySQL database server
$user = "groupoffice"; // the userid of the account you use to access your GroupOffice database
$password = "yourPassword"; // The password of the account you use to access your GroupOffice database
$dbase = "groupofficedb"; // The name of your GroupOffice database
$addressbookOwnerID = '1'; // This is usually the UserID of the Group Office Admin accout that is sharing their address book as a Global Address book
$addressbookToSync_id = '676'; // The ID of which calendar is being shared globally to all uses as the Global Address book
$DATAb = mysql_connect($host,$user,$password);
mysql_select_db($dbase,$DATAb);
// exit();
/* need to lock the database tables "next sequence and ab_contact ?? just now so no one else can create a record */
/* Get a list of all the company names and ID numbers from the go_users table */
$query_companies = "Select id, name FROM ab_companies ";
global $result_companies;
$result_companies = mysql_query($query_companies);
/* Get all the active users from the go_users table */
$query_users = "Select * FROM go_users WHERE enabled = '1' ORDER BY go_users.id ASC";
$result_users = mysql_query($query_users);
// print "Number of go_users are ".mysql_num_rows($result_users)."\n\n";
while($go_users = mysql_fetch_array($result_users))
{
// print "\nUserID: $go_users[id], $go_users[first_name] $go_users[last_name] -> ";
/* Select Contacts from ab_contacts Global Address book records */
$query_contacts = "Select ab_contacts.*, ab_companies.name AS company FROM ab_contacts LEFT JOIN ab_companies ON (ab_contacts.company_id = ab_companies.id ) WHERE ab_contacts.addressbook_id = '$addressbookToSync_id' AND ab_contacts.user_id = '$addressbookOwnerID' AND ab_contacts.go_users_id = '$go_users[id]' ";
$result_contacts = mysql_query($query_contacts);
// print "Number of ab_contacts records are ".mysql_num_rows($result_contacts)."\n\n"; // for debugging, not required for normal logging.
// exit();
if(mysql_num_rows($result_contacts) > 0)
{ /* there should only be one record */
$contacts_users=mysql_fetch_array($result_contacts);
/* check record details to see if any chages had been made */
$Changes = false;
// print " UPDATE: "; // for debugging, not required for normal logging.
$id = 0;
$UpdateCmd = "UPDATE ab_contacts SET ";
if (strcmp($go_users['first_name'], $contacts_users['first_name']) != 0) {
$Changes = true; $UpdateCmd .= " first_name = '".mysql_real_escape_string($go_users['first_name'])."',";
}
if (strcmp($go_users['middle_name'], $contacts_users['middle_name']) != 0) {
$Changes = true; $UpdateCmd .= " middle_name = '".mysql_real_escape_string($go_users['middle_name'])."',";
}
if (strcmp($go_users['last_name'], $contacts_users['last_name']) != 0) {
$Changes = true; $UpdateCmd .= " last_name = '".mysql_real_escape_string($go_users['last_name'])."',";
}
if (strcmp($go_users['initials'], $contacts_users['initials']) != 0) {
$Changes = true; $UpdateCmd .= " initials = '".mysql_real_escape_string($go_users['initials'])."',";
}
if (strcmp($go_users['title'], $contacts_users['title']) != 0) {
$Changes = true; $UpdateCmd .= " title = '".mysql_real_escape_string($go_users['title'])."',";
}
if (strcmp($go_users['sex'], $contacts_users['sex']) != 0) {
$Changes = true; $UpdateCmd .= " sex = '".$go_users['sex']."',";
}
/* if (strcmp($go_users['birthday'], $contacts_users['birthday']) != 0) // For security reasons, providing birthday is too personal for a Global Address book
{
$Changes = true; $UpdateCmd .= " birthday = '".$go_users['birthday']."',";
} */
if (strcmp($go_users['email'], $contacts_users['email']) != 0) {
$Changes = true; $UpdateCmd .= " email = '".mysql_real_escape_string($go_users['email'])."',";
}
$company_id = "";
if (strcmp($go_users['company'], $contacts_users['company']) != 0) {
$company_id = companyID($result_companies, $go_users['company']);
if ($company_id > 0) { $Changes = true; $UpdateCmd .= " company_id = '".$company_id."',"; }
else {
print "\nUserID: $go_users[id], $go_users[first_name] $go_users[last_name] -> "; // disable if debugging
print " Could not find company '".($go_users['company'])."' -> "; }
}
if (strcmp($go_users['department'], $contacts_users['department']) != 0) {
$Changes = true; $UpdateCmd .= " department = '".mysql_real_escape_string($go_users['department'])."',";
}
if (strcmp($go_users['function'], $contacts_users['function']) != 0) {
$Changes = true; $UpdateCmd .= " function = '".mysql_real_escape_string($go_users['function'])."',";
}
if (strcmp($go_users['home_phone'], $contacts_users['home_phone']) != 0) {
$Changes = true; $UpdateCmd .= " home_phone = '".mysql_real_escape_string($go_users['home_phone'])."',";
}
if (strcmp($go_users['work_phone'], $contacts_users['work_phone']) != 0) {
$Changes = true; $UpdateCmd .= " work_phone = '".mysql_real_escape_string($go_users['work_phone'])."',";
}
if (strcmp($go_users['fax'], $contacts_users['fax']) != 0) {
$Changes = true; $UpdateCmd .= " fax = '".mysql_real_escape_string($go_users['fax'])."',";
}
if (strcmp($go_users['cellular'], $contacts_users['cellular']) != 0) {
$Changes = true; $UpdateCmd .= " cellular = '".mysql_real_escape_string($go_users['cellular'])."',";
}
if (strcmp($go_users['country'], $contacts_users['country']) != 0) {
$Changes = true; $UpdateCmd .= " country = '".mysql_real_escape_string($go_users['country'])."',";
}
if (strcmp($go_users['state'], $contacts_users['state']) != 0) {
$Changes = true; $UpdateCmd .= " state = '".mysql_real_escape_string($go_users['state'])."',";
}
if (strcmp($go_users['zip'], $contacts_users['zip']) != 0) {
$Changes = true; $UpdateCmd .= " zip = '".mysql_real_escape_string($go_users['zip'])."',";
}
if (strcmp($go_users['city'], $contacts_users['city']) != 0) {
$Changes = true; $UpdateCmd .= " city = '".mysql_real_escape_string($go_users['city'])."',";
}
if (strcmp($go_users['address'], $contacts_users['address']) != 0) {
$Changes = true; $UpdateCmd .= " address = '".mysql_real_escape_string($go_users['address'])."',";
}
if (strcmp($go_users['address_no'], $contacts_users['address_no']) != 0) {
$Changes = true; $UpdateCmd .= " address_no = '".mysql_real_escape_string($go_users['address_no'])."',";
}
if ($Changes)
{ // do we really want email_allowed to be updated? what does it do anyway?, so we will set it back to what it oringinally was.
// Set the user_id and addressbook_id of your adminstrator's shared addressbook which you use as your Global Addressbook
// To link this user with their corresponding record in the Global Address book, you need to create a new field called 'go_users_id' in the ab_conatacts table.
$UpdateCmd .= " email_allowed = '$contacts_users[email_allowed]' WHERE addressbook_id = '$addressbookToSync_id' AND user_id = '$addressbookOwnerID' AND go_users_id = '$go_users[id]'";
$update_result = mysql_query($UpdateCmd);
print $UpdateCmd."\n\n";
//exit(); /* For testing only - tests one record at a time. */
} else {
// print " ab_contacts record '$contacts_users[id]' did not require changes. ($UpdateCmd)\n"; // for debugging, not required for normal logging.
}
}
else
{ /* this user does not yet have a Global Addressbook Contact record, so lets insert a new record for them. */
/* Set the user_id and addressbook_id of your adminstrator's shared addressbook which you use as your Global Addressbook */
print " INSERT: ";
$newid = nextSegNo("ab_contacts");
$InsertCmd = "INSERT INTO ab_contacts SET id = '$newid', user_id = '$addressbookOwnerID', addressbook_id = '$addressbookToSync_id' , go_users_id = '$go_users[id]', ";
if (!empty($go_users['first_name'])) $InsertCmd .= " first_name = '".mysql_real_escape_string($go_users['first_name'])."',";
if (!empty($go_users['middle_name'])) $InsertCmd .= " middle_name = '".mysql_real_escape_string($go_users['middle_name'])."',";
if (!empty($go_users['last_name'])) $InsertCmd .= " last_name = '".mysql_real_escape_string($go_users['last_name'])."',";
if (!empty($go_users['initials'])) $InsertCmd .= " initials = '".mysql_real_escape_string($go_users['initials'])."',";
if (!empty($go_users['title'])) $InsertCmd .= " title = '".mysql_real_escape_string($go_users['title'])."',";
if (!empty($go_users['sex'])) $InsertCmd .= " sex = '".$go_users['sex']."',";
// if (is_date($go_users['birthday'])) $InsertCmd .= " birthday = '".$go_users['birthday']."',"; // For security reasons, providing birthday is too personal for a Global Address book
if (!empty($go_users['email'])) $InsertCmd .= " email = '".mysql_real_escape_string($go_users['email'])."',";
if (!empty($go_users['company'])) /* company is a dropdown list */
{
$company_id = companyID($result_companies, $go_users['company']);
if ($company_id > 0) { $InsertCmd .= " company_id = '".$company_id."',"; }
else { $InsertCmd .= " comment = '".mysql_real_escape_string($go_users['company'])."',"; }
}
if (!empty($go_users['department'])) { $InsertCmd .= " department = '".mysql_real_escape_string($go_users['department'])."',"; }
if (!empty($go_users['function'])) { $InsertCmd .= " function = '".mysql_real_escape_string($go_users['function'])."',"; }
if (!empty($go_users['home_phone'])) { $InsertCmd .= " home_phone = '".mysql_real_escape_string($go_users['home_phone'])."',"; }
if (!empty($go_users['work_phone'])) { $InsertCmd .= " work_phone = '".mysql_real_escape_string($go_users['work_phone'])."',"; }
if (!empty($go_users['fax'])) { $InsertCmd .= " fax = '".mysql_real_escape_string($go_users['fax'])."',"; }
if (!empty($go_users['cellular'])) { $InsertCmd .= " cellular = '".mysql_real_escape_string($go_users['cellular'])."',"; }
if (!empty($go_users['country'])) { $InsertCmd .= " country = '".mysql_real_escape_string($go_users['country'])."',"; }
if (!empty($go_users['state'])) { $InsertCmd .= " state = '".mysql_real_escape_string($go_users['state'])."',"; }
if (!empty($go_users['zip'])) { $InsertCmd .= " zip = '".mysql_real_escape_string($go_users['zip'])."',"; }
if (!empty($go_users['city'])) { $InsertCmd .= " city = '".mysql_real_escape_string($go_users['city'])."',"; }
if (!empty($go_users['address'])) { $InsertCmd .= " address = '".mysql_real_escape_string($go_users['address'])."',"; }
if (!empty($go_users['address_no'])) { $InsertCmd .= " address_no = '".mysql_real_escape_string($go_users['address_no'])."',"; }
$InsertCmd .= " email_allowed = '1', iso_address_format = 'AU' "; /* By default set email_allowed to true */
$Insert_result = mysql_query($InsertCmd);
print "\n".$InsertCmd."\n\n";
//exit(); /* For testing only - tests one record at a time. */
} /* end of Update/Insert Contact details for this User. */
}
print "\nChecking for users who should be removed from the Contacts Addressbook\n";
/* Delete contacts who were added from the go_users table by the sync process if they are no longer active in the go_users table or
have been deleted from the go_users table, which means that any contacts added specifically by the Owner of this addressbook, should not be deleted.
The code "ab_contacts.first_name AS fname, ab_contacts.last_name AS sname," is only required for debugging purposes, and could be removed if you wanted to.
*/
$query_contacts = "SELECT ne.* FROM (SELECT ab_contacts.id as ab_id, ab_contacts.first_name AS fname, ab_contacts.last_name AS sname,
ab_contacts.go_users_id AS go_user_id, go_users.enabled AS enabled
FROM ab_contacts LEFT JOIN go_users ON (ab_contacts.go_users_id = go_users.id)
WHERE ab_contacts.go_users_id > 0 AND ab_contacts.addressbook_id = '$addressbookToSync_id' AND ab_contacts.user_id = '$addressbookOwnerID'
) ne WHERE ne.enabled = '0' OR ne.enabled IS NULL";
$result_users_contacts = mysql_query($query_contacts);
while($ab_user = mysql_fetch_array($result_users_contacts))
{
/* Note: Check for "ab_contacts.addressbook_id = '$addressbookToSync_id' AND ab_contacts.user_id = '$addressbookOwnerID' " should not be required, but I included for extra assurance I am not deleteing from someone else's addressbook */
$DeleteCmd = "DELETE FROM ab_contacts WHERE ab_contacts.id = '".$ab_user['ab_id']."' AND ab_contacts.addressbook_id = '".$addressbookToSync_id."' AND ab_contacts.user_id = '".$addressbookOwnerID."' ";
$update_result = mysql_query($DeleteCmd);
print "$ab_user[fname] $ab_user[sname] - ".$DeleteCmd."\n"; // Remark this line out if you do not want the extra logging.
}
Print "\n============ Global Addressbook synchronisation completed ====================\n\n\n";
#===== PHP Functions below =================================================================================================================
function nextSegNo($tableName)
{ /* as found in base_db.class.inc.php */
/* WARNING: need to lock the database just now so no one else can create a record, but I have not done this yet,! */
$query_seq = "Select * FROM go_db_sequence WHERE seq_name = '$tableName' ";
$result_seq= mysql_query($query_seq);
if (mysql_num_rows($result_seq) > 0) {
$result_rec = mysql_fetch_array($result_seq);
$newID = $result_rec['nextid'] + 1;
$sql_result = mysql_query("UPDATE go_db_sequence SET nextid = '$newID' WHERE seq_name = '$tableName' ");
return $newID;
} else { print ">>>>>> We had a major database error attempting to update the '".$tableName."' sequence number!"; exit(3); }
}
function companyID($companies, $company_name)
{
$query_companies = "Select id, name FROM ab_companies ";
$result_companies = mysql_query($query_companies);
$company_id = 0;
$company_name = trim($company_name); /* $result_companies*/
while($companydetails = mysql_fetch_array($result_companies))
{
$company = trim($companydetails['name']);
// print "Company ++> '".$company."' \n";
if (strcasecmp($company_name, $company) == 0 )
{
$company_id = $companydetails['id'];
// print "=====> ".$company_id." ".$company_name." = ".$companydetails['name']."\n";
return $company_id;
}
}
// print "-----> '".$company_name."' not found \n"; // Print out all company names which could not be matched, so we can create Company records as required.
return $company_id;
}
function is_date( $str )
{
$stamp = strtotime( $str );
if (!is_numeric($stamp)) return FALSE;
$month = date( 'm', $stamp );
$day = date( 'd', $stamp );
$year = date( 'Y', $stamp );
if (checkdate($month, $day, $year)) return TRUE;
return FALSE;
}
?>
I hope this code helps others, please refer to my earlier postings on how to run this script via a cron job.
I look forward to when the "Addressbook" module in Group Office can include the Users Contact details just as the "E-mail" module addressbook does, and my above script is no longer required.
How is it possible to have a Global Address Book (managed by an administrator) where everyone who is a Group Office user is automatically added to the global address book in "Contacts" at the time when the user's account is created ? 
