rendered paste bodyfunction 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.<br />"; } 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.<br />"; } 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.<br />"; } 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.<br />"; } $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.<br />"; } 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;}