/**
* Collect CiviCRM data into temporary working table.
*/
static function syncCollectCiviCRM($listID)
{
CRM_Mailchimp_Utils::checkDebug('Start-CRM_Mailchimp_Form_Sync syncCollectCiviCRM $listID= ', $listID);
// Nb. these are temporary tables but we don't use TEMPORARY table because they are
// needed over multiple sessions because of queue.
CRM_Core_DAO::executeQuery("DROP TABLE IF EXISTS tmp_mailchimp_push_c;");
$dao = CRM_Core_DAO::executeQuery("CREATE TABLE tmp_mailchimp_push_c (\n contact_id INT(10) UNSIGNED NOT NULL,\n email_id INT(10) UNSIGNED NOT NULL,\n email VARCHAR(200),\n first_name VARCHAR(100),\n last_name VARCHAR(100),\n hash CHAR(32),\n groupings VARCHAR(4096),\n PRIMARY KEY (email_id, email, hash)\n );");
// Cheekily access the database directly to obtain a prepared statement.
$db = $dao->getDatabaseConnection();
$insert = $db->prepare('INSERT INTO tmp_mailchimp_push_c VALUES(?, ?, ?, ?, ?, ?, ?)');
//create table for mailchim civicrm syn errors
$dao = CRM_Core_DAO::executeQuery("CREATE TABLE IF NOT EXISTS mailchimp_civicrm_syn_errors (\n id int(11) NOT NULL AUTO_INCREMENT,\n email VARCHAR(200),\n error VARCHAR(200),\n error_count int(10),\n group_id int(20),\n list_id VARCHAR(20),\n PRIMARY KEY (id)\n );");
// We need to know what groupings we have maps to.
// We only care about CiviCRM groups that are mapped to this MC List:
$mapped_groups = CRM_Mailchimp_Utils::getGroupsToSync(array(), $listID);
// First, get all subscribers from the membership group for this list.
// ... Find CiviCRM group id for the membership group.
// ... And while we're at it, build an SQL-safe array of groupIds for groups mapped to groupings.
// (we use that later)
$membership_group_id = FALSE;
$grouping_group_ids = array('normal' => array(), 'smart' => array());
$default_info = array();
foreach ($mapped_groups as $group_id => $details) {
CRM_Contact_BAO_GroupContactCache::loadAll($group_id);
if (!$details['grouping_id']) {
$membership_group_id = $group_id;
} else {
$grouping_group_ids[$details['civigroup_uses_cache'] ? 'smart' : 'normal'][] = (int) $group_id;
$default_info[$details['grouping_id']][$details['group_id']] = FALSE;
}
}
$grouping_group_ids['smart'] = implode(',', $grouping_group_ids['smart']);
$grouping_group_ids['normal'] = implode(',', $grouping_group_ids['normal']);
if (!$membership_group_id) {
throw new Exception("No CiviCRM group is mapped to determine membership of Mailchimp list {$listID}");
}
// ... Load all subscribers in $groupContact object
if (!($groupContact = CRM_Mailchimp_Utils::getGroupContactObject($membership_group_id))) {
CRM_Mailchimp_Utils::checkDebug('get group contact= ', $groupContact);
throw new Exception("No CiviCRM group is mapped to determine membership of Mailchimp list {$listID}. CiviCRM group {$membership_group_id} failed to load");
}
// Now we iterate through the subscribers, collecting data about the other mapped groups
// This is pretty inefficient :-(
while ($groupContact->fetch()) {
// Find the contact, for the name fields
$contact = new CRM_Contact_BAO_Contact();
$contact->id = $groupContact->contact_id;
$contact->is_deleted = 0;
if (!$contact->find(TRUE)) {
continue;
}
// Find their primary (bulk) email
$email = new CRM_Core_BAO_Email();
$email->contact_id = $groupContact->contact_id;
$email->is_primary = TRUE;
if (!$email->find(TRUE)) {
continue;
}
// If no email, it's like they're not there.
if (!$email->email || $email->on_hold || $contact->is_opt_out || $contact->do_not_email) {
//@todo update stats.
continue;
}
// Find out if they're in any groups that we care about.
// Start off as not in the groups...
$info = $default_info;
// We can do this with two queries, one for normal groups, one for smart groups.
// Normal groups.
if ($grouping_group_ids['normal']) {
$groupContact2 = new CRM_Contact_BAO_GroupContact();
$groupContact2->contact_id = $groupContact->contact_id;
$groupContact2->whereAdd("status = 'Added'");
$groupContact2->whereAdd("group_id IN ({$grouping_group_ids['normal']})");
$groupContact2->find();
while ($groupContact2->fetch()) {
// need MC grouping_id and group_id
$details = $mapped_groups[$groupContact2->group_id];
$info[$details['grouping_id']][$details['group_id']] = TRUE;
}
unset($groupContact2);
}
// Smart groups
if ($grouping_group_ids['smart']) {
$groupContactCache = new CRM_Contact_BAO_GroupContactCache();
$groupContactCache->contact_id = $groupContact->contact_id;
$groupContactCache->whereAdd("group_id IN ({$grouping_group_ids['smart']})");
$groupContactCache->find();
while ($groupContactCache->fetch()) {
// need MC grouping_id and group_id
$details = $mapped_groups[$groupContactCache->group_id];
$info[$details['grouping_id']][$details['group_id']] = TRUE;
}
unset($groupContactCache);
}
// OK we should now have all the info we need.
// Serialize the grouping array for SQL storage - this is the fastest way.
$info = serialize($info);
// we're ready to store this but we need a hash that contains all the info
// for comparison with the hash created from the CiviCRM data (elsewhere).
// email, first name, last name, groupings
$hash = md5($email->email . $contact->first_name . $contact->last_name . $info);
// run insert prepared statement
$db->execute($insert, array($contact->id, $email->id, $email->email, $contact->first_name, $contact->last_name, $hash, $info));
}
// Tidy up.
$db->freePrepared($insert);
// count
$dao = CRM_Core_DAO::executeQuery("SELECT COUNT(*) c FROM tmp_mailchimp_push_c");
$dao->fetch();
CRM_Mailchimp_Utils::checkDebug('End-CRM_Mailchimp_Form_Sync syncCollectCiviCRM $listID= ', $listID);
return $dao->c;
}
