/**
* investigates if the given household still complies
* with all the requirements for a proper household entity
*
*/
function checkHousehold($household_id)
{
$problems_identified = array();
// load household
$household = civicrm_api3('Contact', 'getsingle', array('id' => $household_id));
// load members
$members = $this->getMembers($household_id);
// CHECK 1: number of members
if (count($members) < CRM_Householdmerge_Logic_Configuration::getMinimumMemberCount()) {
if (count($members) == 0) {
$problems_identified[] = CRM_Householdmerge_Logic_Problem::createProblem('HOM0', $household_id);
} else {
$problems_identified[] = CRM_Householdmerge_Logic_Problem::createProblem('HOMX', $household_id, array('count' => count($members)));
}
}
// HEAD related checks
if ('hierarchy' == CRM_Householdmerge_Logic_Configuration::getHouseholdMode()) {
$heads = array();
foreach ($members as $member) {
if ($member['hh_relation'] == 'head') {
$heads[] = $member;
}
}
// CHECK 2: is there still a head?
if (empty($heads)) {
$problems_identified[] = CRM_Householdmerge_Logic_Problem::createProblem('HHN0', $household_id);
}
// CHECK 3: is there more than one head?
if (count($heads) > 1) {
$problems_identified[] = CRM_Householdmerge_Logic_Problem::createProblem('HHN2', $household_id);
}
// CHECK 4: does the head have a DO NOT mail/phone/sms/email
$donts = CRM_Householdmerge_Logic_Configuration::getDontXXXChecks();
foreach ($heads as $head) {
foreach ($donts as $field_name) {
if (!empty($head[$field_name])) {
$problems_identified[] = CRM_Householdmerge_Logic_Problem::createProblem('HHNC', $household_id);
break;
}
}
}
// CHECK 5: does the head have certain tags
$bad_tags = CRM_Householdmerge_Logic_Configuration::getBadHeadTags();
foreach ($heads as $head) {
$tags = CRM_Core_BAO_EntityTag::getContactTags($head['id']);
foreach ($tags as $tag) {
if (in_array($tag, $bad_tags)) {
$problems_identified[] = CRM_Householdmerge_Logic_Problem::createProblem('HHTG', $household_id, array('tag' => $tag));
}
}
}
// CHECK 6: is the head also head of another household?
foreach ($heads as $head) {
$head_relation_id = CRM_Householdmerge_Logic_Configuration::getHeadRelationID();
$relationships_a = civicrm_api3('Relationship', 'get', array('contact_id_a' => $head['id'], 'relationship_type_id' => $head_relation_id, 'is_active' => 1));
$relationships_b = civicrm_api3('Relationship', 'get', array('contact_id_b' => $head['id'], 'relationship_type_id' => $head_relation_id, 'is_active' => 1));
if ($relationships_a['count'] + $relationships_b['count'] > 1) {
$problems_identified[] = CRM_Householdmerge_Logic_Problem::createProblem('HHMM', $household_id);
}
}
}
// all member checks
// CHECK 7: does one of the members not have the household address any more?
$this->checkAddresses($household, $members, $problems_identified);
// CHECK 8: Is there a potential new member for this household?
$this->findNewMembers($household, $members, $problems_identified);
// if (!empty($problems_identified)) {
// $this->createActivity($household, $problems_identified, $members);
// }
foreach ($problems_identified as $problem) {
$problem->createActivity();
}
}
/**
* find the contact_ids of some potential candidates
*/
protected function findCandidates($count)
{
$minimum_member_count = (int) CRM_Householdmerge_Logic_Configuration::getMinimumMemberCount();
if ($count == 'all') {
$limit_clause = '';
} else {
$count = (int) $count;
$limit_clause = "LIMIT {$count}";
}
// compile relationship conditions ("NOT ALREADY MEMBER OF")
$RELATIONSHIP_CONDITION = $RELATIONSHIP_JOIN = '';
$member_relation_id = CRM_Householdmerge_Logic_Configuration::getMemberRelationID();
$head_relation_id = CRM_Householdmerge_Logic_Configuration::getHeadRelationID();
if ($member_relation_id) {
$relationship_ids = array($member_relation_id);
if ($head_relation_id) {
$relationship_ids[] = $head_relation_id;
}
$relationship_id_list = implode(',', $relationship_ids);
// try joining valid, active household relationships...
$RELATIONSHIP_JOIN = "\n LEFT JOIN civicrm_relationship relation_ab ON contact_id = relation_ab.contact_id_a AND relation_ab.relationship_type_id IN ({$relationship_id_list}) AND (relation_ab.end_date IS NULL OR relation_ab.end_date > NOW())\n LEFT JOIN civicrm_relationship relation_ba ON contact_id = relation_ba.contact_id_b AND relation_ba.relationship_type_id IN ({$relationship_id_list}) AND (relation_ba.end_date IS NULL OR relation_ba.end_date > NOW())\n ";
// ...and then make sure there are none
$RELATIONSHIP_CONDITION = "\n AND relation_ab.id IS NULL AND relation_ba.id IS NULL\n ";
}
$candidates = array();
$scanner_sql = "\n SELECT *\n FROM (SELECT civicrm_contact.id AS contact_id,\n GROUP_CONCAT(civicrm_contact.id SEPARATOR '||') AS contact_ids,\n GROUP_CONCAT(civicrm_contact.display_name SEPARATOR '||') AS display_names,\n GROUP_CONCAT(civicrm_contact.gender_id SEPARATOR '||') AS gender_ids,\n civicrm_contact.last_name AS last_name,\n civicrm_address.street_address AS street_address,\n civicrm_address.supplemental_address_1 AS supplemental_address_1,\n civicrm_address.supplemental_address_2 AS supplemental_address_2,\n civicrm_address.postal_code AS postal_code,\n civicrm_address.city AS city,\n civicrm_address.country_id AS country_id,\n COUNT(DISTINCT(civicrm_contact.id)) AS mitgliederzahl\n FROM civicrm_contact\n LEFT JOIN civicrm_address ON civicrm_address.contact_id = civicrm_contact.id\n {$RELATIONSHIP_JOIN}\n WHERE (civicrm_contact.is_deleted IS NULL OR civicrm_contact.is_deleted = 0)\n AND civicrm_contact.contact_type = 'Individual'\n AND civicrm_address.id IS NOT NULL\n AND (civicrm_address.street_address IS NOT NULL AND civicrm_address.street_address != '')\n AND (civicrm_address.postal_code IS NOT NULL AND civicrm_address.postal_code != '')\n AND (civicrm_address.city IS NOT NULL AND civicrm_address.city != '')\n {$RELATIONSHIP_CONDITION}\n GROUP BY civicrm_contact.last_name, civicrm_address.street_address, civicrm_address.postal_code, civicrm_address.city) households\n WHERE mitgliederzahl >= {$minimum_member_count}\n {$limit_clause};\n ";
$scanner = CRM_Core_DAO::executeQuery($scanner_sql);
while ($scanner->fetch()) {
$candidates[$scanner->contact_id] = array('contact_ids' => $scanner->contact_ids, 'display_names' => $scanner->display_names, 'gender_ids' => $scanner->gender_ids, 'street_address' => $scanner->street_address, 'supplemental_address_1' => $scanner->supplemental_address_1, 'supplemental_address_2' => $scanner->supplemental_address_2, 'postal_code' => $scanner->postal_code, 'city' => $scanner->city, 'country_id' => $scanner->country_id, 'last_name' => $scanner->last_name);
}
return $candidates;
}
