/**
* identify potential new household members
*/
protected function findNewMembers(&$household, &$members, &$problems_identified)
{
if (empty($household['household_name']) || empty($household['street_address']) || empty($household['postal_code']) || empty($household['city'])) {
// not enough information...
return;
}
$member_relation_id = CRM_Householdmerge_Logic_Configuration::getMemberRelationID();
$head_relation_id = CRM_Householdmerge_Logic_Configuration::getHeadRelationID();
if (!$member_relation_id) {
return;
}
$relationship_ids = array($member_relation_id);
if ($head_relation_id) {
$relationship_ids[] = $head_relation_id;
}
$relationship_id_list = implode(',', $relationship_ids);
$search_sql = "SELECT DISTINCT(civicrm_contact.id) AS contact_id\n FROM civicrm_contact\n LEFT JOIN civicrm_address ON civicrm_address.contact_id = civicrm_contact.id\n WHERE (civicrm_contact.is_deleted IS NULL OR civicrm_contact.is_deleted = 0)\n AND civicrm_contact.contact_type = 'Individual'\n AND civicrm_contact.last_name = %1\n AND civicrm_address.street_address = %2\n AND civicrm_address.postal_code = %3\n AND civicrm_address.city = %4\n AND NOT EXISTS (SELECT id \n FROM civicrm_relationship \n WHERE (contact_id_a = civicrm_contact.id OR contact_id_b = civicrm_contact.id)\n AND (relationship_type_id IN ({$relationship_id_list}))\n AND (end_date IS NULL OR end_date > NOW())\n AND (is_active = 1)\n );";
$queryParameters = array(1 => array($household['household_name'], 'String'), 2 => array($household['street_address'], 'String'), 3 => array($household['postal_code'], 'String'), 4 => array($household['city'], 'String'));
$new_members = CRM_Core_DAO::executeQuery($search_sql, $queryParameters);
while ($new_members->fetch()) {
$problems_identified[] = CRM_Householdmerge_Logic_Problem::createProblem('HMNW', $household['id'], array('member_id' => $new_members->contact_id));
}
}
