static function getGroupSubscriptionforMailchimpList($listID, $contactID)
 {
     CRM_Mailchimp_Utils::checkDebug('Start-CRM_Mailchimp_Utils getGroupSubscriptionforMailchimpList $listID', $listID);
     CRM_Mailchimp_Utils::checkDebug('Start-CRM_Mailchimp_Utils getGroupSubscriptionforMailchimpList $contactID', $contactID);
     if (empty($listID) || empty($contactID)) {
         return NULL;
     }
     $civiMcGroups = array();
     $query = "\n      SELECT  entity_id\n      FROM    civicrm_value_mailchimp_settings mcs\n      WHERE   mc_list_id = %1";
     $params = array('1' => array($listID, 'String'));
     $dao = CRM_Core_DAO::executeQuery($query, $params);
     while ($dao->fetch()) {
         $groupContact = new CRM_Contact_BAO_GroupContact();
         $groupContact->group_id = $dao->entity_id;
         $groupContact->contact_id = $contactID;
         $groupContact->whereAdd("status = 'Added'");
         $groupContact->find();
         if ($groupContact->fetch()) {
             $civiMcGroups[] = $dao->entity_id;
         }
     }
     CRM_Mailchimp_Utils::checkDebug('End-CRM_Mailchimp_Utils getGroupSubscriptionforMailchimpList $listID', $listID);
     CRM_Mailchimp_Utils::checkDebug('End-CRM_Mailchimp_Utils getGroupSubscriptionforMailchimpList $contactID', $contactID);
     CRM_Mailchimp_Utils::checkDebug('End-CRM_Mailchimp_Utils getGroupSubscriptionforMailchimpList $civiGroups', $civiGroups);
     return $civiMcGroups;
 }
 /**
  * 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;
 }