function civicrm_search_for_voters($query, $offset = 0, $limit = 50, $field = 'name', $sort = 'asc') {
$rows = array();
$contact_ids = array();
if ($query['postal_code'] || $query['city']) {
$conditionals = array(); // eventually ANDed together.
if ($query['city']) { $conditionals[] = 'ca.city = "'.addslashes($query['city']).'"'; }
if ($query['postal_code']) { $conditionals[] = "ca.postal_code = $query[postal_code]"; }
if (count($contact_ids) > 1) { $conditionals[] = 'cl.entity_id IN ( '.implode(', ', $contact_ids).' ) '; }
$conditional_sql = implode(" AND ", $conditionals); // zomg, it's like magick. a really really really really really lame kind of magick.
$results = _civicrm_db_query('SELECT cl.entity_id FROM civicrm_location cl LEFT JOIN civicrm_address ca ON (cl.id = ca.location_id) WHERE '.$conditional_sql);
$contact_ids = array(); while ($result = db_fetch_object($results)) { $contact_ids[] = $result->entity_id; }
// print count($contact_ids)." results against postal codes and city.
";
}
if (is_numeric($query['age_min']) || is_numeric($query['age_max'])) {
$conditionals = array(); // eventually ANDed together.
if ($query['age_min']) { $conditionals[] = 'DATE_SUB(CURDATE(), INTERVAL '.addslashes($query['age_min']).' YEAR) >= birth_date'; }
if ($query['age_max']) { $conditionals[] = 'DATE_SUB(CURDATE(), INTERVAL '.addslashes($query['age_max']).' YEAR) <= birth_date'; }
if (count($contact_ids) > 1) { $conditionals[] = 'contact_id IN ( '.implode(', ', $contact_ids).' ) '; }
$conditional_sql = implode(" AND ", $conditionals); // zomg, it's like magick. a really lame kind of magick.
$results = _civicrm_db_query('SELECT contact_id FROM civicrm_individual WHERE '.$conditional_sql);
$contact_ids = array(); while ($result = db_fetch_object($results)) { $contact_ids[] = $result->contact_id; }
// print count($contact_ids)." results against age matching.
";
}
if ($query['party_code']) {
$conditionals = array(); // eventually ANDed together.
if ($query['party_code']) { $conditionals[] = 'char_data = "'.addslashes($query['party_code']).'"'; }
if (count($contact_ids) > 1) { $conditionals[] = 'entity_id IN ( '.implode(', ', $contact_ids).' ) '; }
$conditional_sql = implode(" AND ", $conditionals); // zomg, it's like magick. a lame kind of magick.
$results = _civicrm_db_query('SELECT entity_id FROM civicrm_custom_value WHERE '.$conditional_sql.' AND custom_field_id = 7');
$contact_ids = array(); while ($result = db_fetch_object($results)) { $contact_ids[] = $result->entity_id; }
// print count($contact_ids)." results against party code.
";
}
if ($query['county'] && !$query['city']) {
$conditionals = array(); // eventually ANDed together.
if ($query['county']) { $conditionals[] = 'char_data = "'.addslashes($query['county']).'"'; }
if (count($contact_ids) > 1) { $conditionals[] = 'entity_id IN ( '.implode(', ', $contact_ids).' ) '; }
$conditional_sql = implode(" AND ", $conditionals); // zomg, it's like magick. a lame kind of magick.
$results = _civicrm_db_query('SELECT entity_id FROM civicrm_custom_value WHERE '.$conditional_sql.' AND custom_field_id = 1');
$contact_ids = array(); while ($result = db_fetch_object($results)) { $contact_ids[] = $result->entity_id; }
// print count($contact_ids)." results against county.
";
}
$vote_counts = array(); // optimization trick: hold all the voting counts from here.
if (is_numeric($query['election_vote_count']) && $query['election_vote_count_condition']) {
$conditionals = array(); // eventually ANDed together. @todo we don't input check the _condition.
if (count($contact_ids) > 1) { $conditionals[] = 'entity_id IN ( '.implode(', ', $contact_ids).' ) '; }
$conditionals[] = 'custom_field_id IN (39, 40, 41, 42, 43)'; // supa thunderdrome thunder shower reet!
$conditional_sql = implode(" AND ", $conditionals); // zomg, it's like magick. a lame kind of magick.
$results = _civicrm_db_query("SELECT entity_id, COUNT(*) AS voted FROM civicrm_custom_value WHERE $conditional_sql GROUP BY
entity_id HAVING voted $query[election_vote_count_condition] $query[election_vote_count]"); // what the... stupid cache.
$contact_ids = array(); while ($result = db_fetch_object($results)) { $contact_ids[] = $result->entity_id; $vote_counts[$result->entity_id] = $result->voted; }
// print count($contact_ids)." results against vote count.
";
}
if (count($contact_ids) > 0) { // only keep on truckin' if we actually got results.
$results = _civicrm_db_query('SELECT contact_id, first_name, last_name, FLOOR((TO_DAYS(CURDATE()) - TO_DAYS(birth_date))/365)
AS age FROM civicrm_individual WHERE contact_id IN ( '.implode(', ', $contact_ids).' )');
while ($result = db_fetch_object($results)) { // now, with all our filtered IDs, attempt to make a final dataset.
$rows[$result->contact_id]['name'] = $result->last_name.', '.$result->first_name;
$rows[$result->contact_id]['age'] = $result->age; // we are the incredibly elite not-cross-dbms sql mastahs. oh yah.
$rows[$result->contact_id]['vname'] = l($result->last_name.', '.$result->first_name, 'civicrm/contact/view', NULL, 'reset=1&cid='.$result->contact_id);
}
// quickie trick: if the party code is passed in...
if (!$query['party_code']) { // ...we set it to user data later.
$results = _civicrm_db_query('SELECT entity_id, char_data FROM civicrm_custom_value WHERE entity_id IN ( '.implode(', ', $contact_ids).' ) AND custom_field_id = 7');
while ($result = db_fetch_object($results)) { $rows[$result->entity_id]['party_code'] = $result->char_data; }
}
// fill in missing data: addresses.
$rows = civicrm_search_map_addresses($contact_ids, $rows);
// fill in missing data: party code.
foreach ($rows as $key => $values) { if (!$rows[$key]['party_code'] && $query['party_code']) { $rows[$key]['party_code'] = $query['party_code']; } }
// fill in missing data: election counts.
if (count($vote_counts) > 1) { foreach ($rows as $key => $values) { $rows[$key]['voted'] = $vote_counts[$key]; } }
else { // if the vote count hasn't been sucked in from our previous queries, we'll have to load it in manually.
$results = _civicrm_db_query('SELECT entity_id, COUNT(*) AS voted FROM civicrm_custom_value WHERE entity_id IN ( '.implode(', ', $contact_ids).' ) AND custom_field_id IN (39, 40, 41, 42, 43) GROUP BY entity_id');
while ($result = db_fetch_object($results)) { $rows[$result->entity_id]['voted'] = $result->voted; }
}
// sort and splice by desired keys and offset/limits,
// then reorder the columns to something we like better.
$rows = array_splice(_array_sort_by_2nd_key($rows, $field, $sort), $offset, $limit);
$rows = _array_reorder_columns($rows, array('vname', 'street_address', 'city', 'state', 'postal_code', 'party_code', 'age', 'voted'));
}
return $rows;
}