Automagical household creation script

[twowheeler]

As I mentioned elsewhere elsewhere I am working on a script that is intended to:

    * Get a list of all individuals in DB
    * For each, search for a household with a matching address
    * If found, link individual to household
    * If individual address matches to household address, but last name is different, make the link but also tag the individual with "Household name mismatch" for later review.
    * If not found, create a new household with the individual's address and link to it
    * The households created have a name like "<last_name> Household"

It depends heavily on the street addresses being in similar format so that they match on a simple string search, so it helps a lot if they have been run through the USPS address verification feature.  For example, "boulevard" will not match "blvd" and you will get duplicate households.

Occasionally, the script stops unexpectedly and dumps core.  It always happens on a particular contact record.  If I go in and delete this particular contact, and restart the script, it carries on past that point, but then hits another problem record later.  Again, delete the offending record, restart, and it gets further.  In total, there are four contacts in my DB that make it stop.  After those four are removed, the script finished successfully.  There is nothing visibly different about these four contacts compared to any others.

I am working from a copy of the production database (obviously, or I would not be deleting records!) for testing purposes.  At least twice I have dumped the DB and refreshed it from the production server, with no change in behavior.  There are no error messages or other clues to why this is happening.

Clearly, deleting data is not a good solution to a coding problem!

Comments are welcome.

Code: [Select]

<?php



// Script to relate each individual to a household, creating the household 

// if necessary

// Designed and tested on CiviCRM 1.9 only

// Run from the command line with:

// sudo -u www-data php5 -f civihouseholds.php | tee capture.txt

// or similar

// Contributed to the CiviCRM project by twowheeler



// Change the following line to match your directory setup

require_once("/var/www/database/sites/default/civicrm.settings.php");



global $civicrm_root;

require_once($civicrm_root.'/CRM/Core/Config.php');

require_once($civicrm_root.'/api/crm.php');

require_once($civicrm_root.'/CRM/Contact/Form/Individual.php');

require_once($civicrm_root.'/CRM/Contact/Form/Edit.php');

require_once($civicrm_root.'/CRM/Contact/BAO/Contact.php');

$config =& CRM_Core_Config::singleton( );



function get_mail_setting( $pid ) {


$sql = "SELECT mail_to_household_id from civicrm_individual WHERE contact_id=$pid";

if (!$result = mysql_query( $sql )) {

echo "Mysql error for PID=$pid ".mysql_error();

return NULL;

}

$data = mysql_fetch_array($result);

return $data[0];

}



function set_mail_setting( $hhold_id, $pid ) {

$sql = "UPDATE civicrm_individual SET mail_to_household_id=$hhold_id WHERE contact_id=$pid";

if (!mysql_query( $sql ))

echo "Mysql error for PID=$pid ".mysql_error()."\n";

}



$Hcopycount = $Hcreatecount = $Hdupcount = $Pcount = 0;

// get the number of contacts to handle

$properties = array( 'last_name' => 1, 'street_address' => 1, 'supplemental_address_1' => 1,

'supplemental_address_2' => 1, 'supplemental_address_3' => 1, 'city' => 1, 'state_province' => 1,

'postal_code' => 1, 'postal_code_suffix' => 1, 'mail_to_household_id' => 1, 'country_id' => 1,

'is_primary' => 1, 'location_type' => 1 );

$params = array('location_type' => 'Home', 'contact_type' => 'Individual'); // home location

$total = crm_contact_search_count( $params );

/*** Returns a nested array where array[0] contains an array of contact values for each returned 

contact (where key is 'contact_id' and value is an array of 'name - value' pair) specified in return 

properties. ***/

echo "Individual count: $total\n";



// use paging so we don't exceed memory limit

$page = 0;

$limit = 100;

for ($offset = 0; $offset < $total; $offset += $limit ) {

$page++;

$people = crm_contact_search( $params, $properties, NULL, $offset, $limit );

foreach ($people[0] as $id => $person) {

$pid = $person['contact_id'];

$family = $person['last_name'];

$mail_to_household_id = get_mail_setting($pid);

$addr = $person['street_address'];

$zip = $person['postal_code'];

if (!$family) {

echo "No name, skipping record\n";

continue;

}

if (!$addr) {

echo "No address, skipping $family\n";

continue; // no address for this person

}

if ($mail_to_household_id) {

echo "Already in a household, skipping $family\n";

continue;  // already a household member

}



// search for a household at this address

$hh_params = array( 'contact_type' => 'Household', 'street_address' => $addr, 'postal_code' => $zip);

$Nhholds = crm_contact_search_count( $hh_params );

echo "Found $Nhholds household(s) for $family  ... ";



if ($Nhholds) {

// at least one household exists at this address

if ($Nhholds > 1) {

// got more than one 

echo "Error: Duplicate households\n";

print_r(crm_contact_search( $hh_params, $properties ));

echo "\n";

$Hdupcount++;

} else {

// ok, got one household

echo "Linking to household ... ";

$hh_properties = array('contact_id' => 1, 'street_address' => 1, 'postal_code' => 1, 

'household_name' => 1);

$hhold = crm_contact_search( $hh_params, $hh_properties );

list($hhold_id, $hhold_data) = each( $hhold[0] );



// assign individual to this household

$addr_params = array( 'contact_id' => $pid );

$addr_params['location'] = array( '1' => array(), '2' => NULL );

$addr_params['location']['1']['address'] = array();

foreach( $person as $item => $value ) 

$addr_params['location']['1']['address'][$item] = $value;

$addr_params['mail_to_household_id'] = $hhold_id;

$addr_params['use_household_address'] = 1;

CRM_Contact_Form_Individual::copyHouseholdAddress( $addr_params );

CRM_Contact_Form_Individual::handleSharedRelation( $pid, $addr_params );

set_mail_setting($hhold_id,$pid);



// if last names differ, set tag

$hhold_name = $hhold_data['household_name'];

sscanf($hhold_name, '%s Household', $hhold_last_name);

if (strcasecmp(trim($hhold_last_name), trim($family))) {

$tag_params = array( 'name' => 'Household name mismatch', 'domain_id' => 1 );  

$tag = crm_get_tag($tag_params);

if ( is_a( $tag, 'CRM_Core_Error' )) 

$tag = crm_create_tag($tag_params);

if ( is_a( $tag, 'CRM_Core_Error' )) 

echo 'Cannot create tag! ';

$item = array('contact_id' => $pid);

$contact = crm_get_contact($item);

if ( ! is_a( $contact, 'CRM_Core_Error' )) 

crm_create_entity_tag(&$tag, &$contact);

else 

echo "Failed to get contact id=$pid\n";

}

echo $hhold_id . "\n";

$Hcopycount++;

}

} else {

// create a new household & assign this individual to it

$addr_params = array('create_household' => $family. " Household");

$addr_params['location'] = array( '1' => array(), '2' => NULL );

$addr_params['location']['1']['address'] = array();



// transfer address data to params array

foreach( $person as $item => $value ) 

$addr_params['location']['1']['address'][$item] = $value;

$addr_params['use_household_address'] = 1;

unset($addr_params['location']['1']['address']['location_id']);

if (!CRM_Contact_Form_Individual::createSharedHousehold( $addr_params )) {

echo "Error creating household\n";

exit;

} 

$hhold_id = $addr_params['mail_to_household_id'];

set_mail_setting($hhold_id,$pid);



// create relationship of contact to household

echo "Making relationships ... ";

CRM_Contact_Form_Individual::handleSharedRelation( $pid, $addr_params );  

echo $addr_params['create_household'] . " created\n";

$Hcreatecount++;

}

$Pcount++;

break; // for testing only -- comment this out when we are ready to go

}

}



echo "$Pcount contacts with $Hcopycount copied, $Hcreatecount created, and $Hdupcount dups found.\n";



?>



[Chris Burgess]

Thanks! We changed this a fair bit to make it work how we wanted (including with CiviCRM 2.0), and this is the end result of our work.

This script doesn't create a household for each contact, like the above; it searches for instances where several individuals share an address, and generates a household for each address, setting the individual's mail_to_household_id accordingly, and generating household <=> individual relationships.

It also tags them accordingly with a suitable tag, so you can go check / clean afterwards.

There is one known issue with this script: it doesn't authenticate as a logged in user when you run the script, and consequently creates ChangeLog entries which show that a change was made by the contact being changed at the time the script was run.

Because CiviCRM's changelog doesn't show the details of the change (yet!), it's not obvious after the fact what this change was. We got a bit confused a week or two later when we noticed that contacts who shouldn't have logins on our system had been updating their own details - this was it.

I'd love some feedback on this, especially with regard to initialising the CiviCRM system - I'm sure there's a better way than hardcoding paths to Drupal and CiviCRM settings. (It would be nice if it wasn't Drupal-specific too.)

Code: [Select]

<?php

/**
 * Script to identify shared households in CiviCRM, find contacts
 * who reside at that address and merge them into a single household
 *
 * Requires CiviCRM 2.0
 * 
 * Based on a similar script by twowheeler which creates a household
 * for each individual in the DB
 *  
 * This code donated back to the community as a result of development
 * work done for Green Party of Aotearoa New Zealand - greens.org.nz
 * 
 * Code by Giant Robot - giantrobot.co.nz
 */

//
// Configure your settings here ========================================
// 

/**
 * Your Drupal config file
 *
 * Enter the full path to your Drupal config file here
 */

$drupal_settings_file = '/path/to/settings.php' ;

/**
 * Your CiviCRM config file
 *
 * Enter the full path to your CiviCRM config file here
 */

$civicrm_settings_file = '/path/to/civicrm.settings.php' ;

/**
 * Which address fields need to match in order to constitude a duplicate address
 *
 * Make sure that these fields are the ones which most commonly are
 * populated in your DB. They must match exactly as (for example)
 * "Blvd" and "Boulevard" in a street address will be read as
 * separate addresses.
 *
 * Do not include address fields which are not used.
 */

$fields_to_match = array( 'street_address', 'supplemental_address_1', 'supplemental_address_2', 'city', 'postal_code' ) ;

/**
 * Which address fields can be considered a match if BOTH values are NULL?
 *
 * We only check those from $fields_to_match, so this value should be a subset of that value.
 */

$allow_nulls = array( 'supplemental_address_1', 'supplemental_address_2', 'postal_code' ) ;

// ================================================================================
// end of config settings

// load civicrm config
require_once( $drupal_settings_file ) ;
require_once( $civicrm_settings_file ) ;
require_once($drupal_root.'./includes/bootstrap.inc') ;
drupal_bootstrap(DRUPAL_BOOTSTRAP_FULL);

// set up CiviCRM, include files
global $civicrm_root ;
require_once($civicrm_root.'/CRM/Core/Config.php');
require_once($civicrm_root.'/api/v2/utils.php');
require_once($civicrm_root.'/api/v2/Contact.php');
require_once($civicrm_root.'/api/v2/Location.php');
require_once($civicrm_root.'/api/v2/EntityTag.php');
require_once($civicrm_root.'/api/Tag.php');
require_once($civicrm_root.'/api/Relationship.php');
require_once($civicrm_root.'/api/v2/Relationship.php');
require_once($civicrm_root.'/CRM/Contact/Form/Individual.php');
require_once($civicrm_root.'/CRM/Contact/Form/Edit.php');
require_once($civicrm_root.'/CRM/Contact/BAO/Contact.php');
$config =& CRM_Core_Config::singleton( );

/**
 * search for addresses which have the same address details, and where
 * the contacts of those addresses are individuals
 *
 * @return array (assoc) of address values
 */
function find_shared_addresses() {
  global $db, $fields_to_match, $allow_nulls ;
  foreach ( $fields_to_match as $field_to_match ) {
    if ( in_array( $field_to_match, $allow_nulls ) ) {
      $restr[] = "( aa.$field_to_match=ab.$field_to_match OR ( aa.$field_to_match IS NULL AND ab.$field_to_match IS NULL ) )";
    } else {
      $restr[] = "aa.$field_to_match=ab.$field_to_match";
    }      
    $fields_to_get[] = "aa.$field_to_match" ;
  }
  $fields_to_get = implode(', ', $fields_to_get);
  $restr[] = "ca.id != cb.id" ;
  $restr[] = "ca.contact_type='Individual'" ;
  $restr[] = "cb.contact_type='Individual'" ;
  $restr[] = "aa.contact_id = ca.id" ;
  $restr[] = "ab.contact_id = cb.id" ;
  $restr[] = "( ca.mail_to_household_id IS NULL OR cb.mail_to_household_id IS NULL )" ;
  $sql = "SELECT DISTINCT $fields_to_get FROM civicrm_address aa, civicrm_address ab, civicrm_contact ca, civicrm_contact cb WHERE " .
    implode(' AND ', $restr) . "\n" /* . " ORDER BY ca.id DESC LIMIT 3" */ ;
  //    aa.street_address = b.street_address and a.postal_code = b.postal_code and a.id != b.id and a.contact_id != b.contact_id LIMIT 50" ;
  if ( $result = $db->GetAll( $sql ) ) {
    return $result ;
  }
  /**
   * CiviCRM will capture any PEAR::DB error anyway, so we are excused
   * from handling errors here - is that right?
   *
   * The error message will be in HTML, but anyone running this script
   * probably reads that anyway :)
   * 
   * Would be good to patch CiviCRM's error handler to produce good
   * console errors, though.
   */
}

/**
 * Get a tag to mark people who have been automatically assigned
 */
function hh_tag_assigned($cid) {
  global $tid ;
  if ( !isset($tid) ) {
    $tag_params = array( 'name' => 'Household assigned by script', 'domain_id' => 1 );  
    $tag = crm_get_tag($tag_params);
    if ( is_a( $tag, 'CRM_Core_Error' ) ) { 
      $tag = crm_create_tag($tag_params);
    }
    if ( is_a( $tag, 'CRM_Core_Error' ) ) {
      hm_error_add( 'Cannot create tag!' ) ;
      unset($tag);
    }
    else {
      $tid = $tag->id ;
    }
  }
  if ( isset($tid) ) {
    $params = array( 'contact_id' => $cid, 'tag_id' => $tid ) ;
    $res = civicrm_entity_tag_add($params);
  }
  if ( !civicrm_error($res) ) {
    print( "Tagging contact ($cid)\n" ) ;
  }
}

/**
 * Handle a given address
 *
 * Find all contacts at a given set of address details.
 *
 * Check if any of them are a household already. If so, then make sure
 * all the individuals have that household set as their household.
 *
 * If none of them are a household, then create a household name based
 * on their surnames, and set those individuals to have that
 * household.
 *
 * @param array A set of CiviCRM address details
 */
function hm_merge_individuals_to_address( $address ) {
  // we search for all contacts at this address, rather than searching
  // for a household and then individuals, because it's faster that
  // way
  $return = array( 'return.contact_type' => 1,
   'return.last_name' => 1,
   'return.first_name' => 1,
   'return.household_name' => 1,
   // these two seem to get returned anyway?
   'return.contact_id' => 1, 
   'return.address_id' => 1, 
   'return.mail_to_household_id' => 1,
   );
  $params = array_merge( $address, $return ) ;
  $address_contacts = civicrm_contact_search($params) ;
  if ( !civicrm_error( $address_contacts ) ) {
    // search for an existing household
    $household = array( 
       'surnames' => array(),
       'members'  => array(),
       'hh_count' => 0,
) ;
    foreach( $address_contacts as $contact ) {
      switch ( $contact['contact_type'] ) {
      case 'Household' : 
$household['name'] = $contact['household_name'] ;
$household['contact_id'] = $contact['contact_id'] ;
$household['details'] = $contact ;
$household['hh_count']++ ;
break ;
      case 'Individual' : 
// print_r($contact);
$household['surnames'][] = $contact['last_name'] ;
$household['members'][] = $contact ;
break ;
      default :
print( " ERR: Not sure what to do with contact type '{$contact['contact_type']}'\n" ) ;
      }
    }
    /**
     * only take action if there are members to this household
     */
    if ( isset( $household['members'] ) ) {
      /**
       * throw an error if multiple households were seen
       */
      if ( $household['hh_count'] > 1 ) {
hm_error_add("Several households found with these details: " . print_r($address,1));
      }
      else {
/**
 * use existing name, or generate a name for this household
 * based on member surnames
 */
if ( !isset( $household['name'] ) ) {
  $household['surnames'] = array_unique($household['surnames']);
  if ( sizeof( $household['surnames'] ) > 2 ) {
    $last_surname = array_pop( $household['surnames'] ) ;
    $household['name'] = implode( ", ", $household['surnames']) . " & " . $last_surname . " Household*" ;
  }
  else {
    // this works for one or two names
    $household['name'] = implode( " & ", $household['surnames'] ) . " Household" ;
  }
}
/**
 * check that the household exists, or create it
 */
if ( !isset( $household['details'] ) ) {
  print( "Creating household " . $household['name'] . "\n" );
  // copy details from household member
  $skip_details = array( 'contact_id', 'contact_type', 'first_name', 'last_name' ) ;
  $household['details']['household_name'] = $household['name'] ;
  $household['details']['contact_type'] = 'Household' ;
  $household['details']['location_type'] = 'Home' ;
  $household['details']['is_primary'] = 1 ;
  foreach( $household['members'][0] as $key => $value ) {
    if ( !in_array( $key, $skip_details ) ) {
      $household['details'][$key] = $value ;
    }
  }
  $hh_contact_details = $household['details'] ;
  $res = civicrm_contact_add( $hh_contact_details ) ;
  if ( civicrm_error( $res ) ) {
    hm_error_add( "Unable to create household: " . $household['details'] ) ;
  }
  else {
    $household['contact_id'] = $hh_contact_details['contact_id'] ;
    $hh_location_details = $household['details'] ;
    $hh_location_details['contact_id'] = $hh_contact_details['contact_id'] ;
    $res = civicrm_location_add( $hh_location_details ) ;
    if ( civicrm_error( $res ) ) {
      hm_error_add( "Unable to set household location: " . print_r(array($hh_location_details, $res),1) ) ;
    }
    else {
      print( "Added location {$hh_location_details['street_address']} for household {$household['name']}\n" ) ;
      //       print_r($hh_location_details);
    }
    hh_tag_assigned($hh_contact_details['contact_id']);
  }
}
else {
  print( "Merging members of household " . $household['name'] . " ({$household['details']['contact_id']})\n" );
  //   print_r($household);
}
/**
 * handle all the members of this household, merging them to
 * the household where appropriate
 */
foreach ( $household['members'] as $member ) {
  if ( $hid = hm_get_mail_setting($member['contact_id']) ) {
    if ( $hid == $household['details']['contact_id'] ) {
      print( "Skipping {$member['first_name']} {$member['last_name']} ({$member['contact_id']}) " .
     "who is already part of household {$household['name']} ({$household['contact_id']})\n" ) ;
    }
    else {
      $oparams = array( 'contact_id' => $hid ) ;
      $other_household = civicrm_contact_get($oparams) ;
      if ( !civicrm_error($other_household) ) {
$msg = "Skipping {$member['first_name']} {$member['last_name']} ({$member['contact_id']}) " .
  "who is part of another household, {$other_household['household_name']} ({$other_household['contact_id']})" ;
// print_r($other_household);
hm_error_add( $msg ) ;
print( $msg . "\n" );
      }
      else {
$msg = "{$member['first_name']} {$member['last_name']} is registered to unknown household $hid" ;
hm_error_add( $msg ) ;
print( $msg . "\n" ) ;
      }
    }
  }
  else {
    //     print_r($household['details']);
    print( "Merging {$member['first_name']} {$member['last_name']} ({$member['contact_id']}) " . 
   "to household {$household['name']} ({$household['contact_id']})\n" ) ;
    $contact = $member ;
    $contact['mail_to_household_id']  = $household['contact_id'] ;
    $contact['use_household_address'] = 1 ;
    $res = civicrm_contact_add($contact);
    /*
     * print_r( array( 'member' => $member,
     *                 'contact' => $contact ) ) ;
     */
    if ( civicrm_error( $res ) ) {
      hm_error_add( "Error setting contact to household: " . print_r($res,1) ) ;
    }
    else {
      hh_tag_assigned( $contact['contact_id'] ) ;
    }
    hm_add_household_member_relation( $contact['contact_id'], $household['contact_id'] ) ;
  }
}
      }
    }
    print("\n");
  }
  else {
    die( "CiviCRM ERR: " . print_r($address_contacts,1) ) ;
  }
}

/**
 * Get the contact's mail_to_household_id to see if they are already
 * associated with a household
 *
 * It would make perfect sense to already have the contact's
 * mail_to_household_id here from the civicrm_contact_search(), or
 * failing that to use the result of civicrm_contact_get(), but
 * neither of these returns the mail_to_household_id, so we have to
 * work around that.
 *
 * @param integer contact id of contact
 *
 * @todo remove once we work out how to access this value using CiviCRM API
 */
function hm_get_mail_setting($cid) {
  global $db ;
  $sql = "SELECT mail_to_household_id FROM civicrm_contact WHERE id = " . $db->Quote($cid);
  if ( $res = $db->GetOne($sql) ) {
    return $res ;
  }
}

/**
 * add an error to our swag of errors
 */
function hm_error_add($msg) {
  global $errors ;
  $errors[] = $msg ;
}

/**
 * print the errors recorded so far
 */
function hm_errors_show() {
  global $errors ;
  if ( is_array( $errors ) ) {
    print( "--------------------------------------------------------------------------------\n" . 
   "ERRORS\n" );
    foreach( $errors as $error ) {
      print($error."\n");
    }
  }
}

/**
 * a check to ensure we don't run out of RAM
 */
function hm_memory_check() {
  $amt_to_use = 0.75 ;
  global $memory_limit ;

  if ( !isset($memory_limit) || empty($memory_limit) ) {
    $memory_limit = let_to_num(ini_get('memory_limit')) ;
  }

  if ( memory_get_usage() > $memory_limit * $amt_to_use ) {
    hm_error_add("Running out of memory! Exiting.");
    hm_error_add(memory_get_usage()  . " > " . $memory_limit * $amt_to_use );
    hm_errors_show();
    die();
  }
}

/**
 * This function transforms the php.ini notation for numbers (eg
 * '2M') to an integer (eg 2*1024*1024)
 */
function let_to_num($v){ 
  $l = substr($v, -1);
  $ret = substr($v, 0, -1);
  switch(strtoupper($l)){
  case 'P':
    $ret *= 1024;
  case 'T':
    $ret *= 1024;
  case 'G':
    $ret *= 1024;
  case 'M':
    $ret *= 1024;
  case 'K':
    $ret *= 1024;
    break;
  }
  return $ret;
}

/**
 * Add household member relationship
 *
 * Note that the form of relationship and description are hardcoded
 * here - you'll need to change them if you've altered the CiviCRM
 * defaults, or aren't using the same localised version as we are
 * (perhaps).
 *
 * @param integer contact id
 * @param integer household id
 */
function hm_add_household_member_relation( $cid, $hid ) {
  global $hh_relationship_id ;
  if ( !isset($hh_relationship_id) || empty($hh_relationship_id) ) {
    $relationship_types = crm_get_relationship_types() ;
    if ( !is_a( $relationship_types, 'CRM_Core_Error' ) ) {
      foreach( $relationship_types as $relationship ) {
if ( $relationship->name_a_b == 'Household Member of' ) {
  $hh_relationship_id = $relationship->id ;
}
      }
    }
  }
  if ( isset($hh_relationship_id) ) {
    $params = array( 'contact_id_a' => $cid,
     'contact_id_b' => $hid,
     'relationship_type_id' => $hh_relationship_id,
     'start_date' => NULL,
     'is_active' => 1,
     );
    $res = civicrm_relationship_create( $params ) ;
    if ( !civicrm_error( $res ) ) {
      print( "Adding household relationship between contact ($cid) and household ($hid)\n" ) ;
    }
  }
}

/**
 * It's the main function!
 */ 
function main() {
  global $config, $db ;
  // not certain if this is the "correct" way to interact with the
  // CiviCRM DB? we use PEAR::DB from the CiviCRM packages
  $counter = 0 ;
  if ( isset( $config->dsn ) ) {
    if ( $db = DB::connect($config->dsn) ) {
      $db->SetFetchMode(DB_FETCHMODE_ASSOC);
      $shared_addresses = find_shared_addresses();
      print( "Got " . sizeof( $shared_addresses ) . " addresses to process.\n\n" );
      //      print_r($shared_addresses);
      // die();
      foreach ( $shared_addresses as $shared_address ) {
hm_memory_check() ;
hm_merge_individuals_to_address( $shared_address ) ;
$counter++ ;
      }
    }
    else {
      die("Unable to connect to DB - connection failed\n");
    }
  }
  else {
    die("Unable to connect to DB - DSN not configured in config object\n");
  }
  hm_errors_show();
}

main();