PHP CRM_Contact_BAO_Query::getWhereClause方法代码示例

 /**
  * Returns array of contacts who are members of the specified group.
  *
  * @param CRM_Contact $group                A valid group object (passed by reference)
  * @param array       $returnProperties     Which properties
  *                    should be included in the returned Contact object(s). If NULL,
  *                    the default set of contact properties will be
  *                    included. group_contact properties (such as 'status',
  * '                  in_date', etc.) are included automatically.Note:Do not inclue
  *                    Id releted properties.  
  * @param text        $status               A valid status value ('Added', 'Pending', 'Removed').
  * @param text        $sort                 Associative array of
  *                    one or more "property_name"=>"sort direction"
  *                    pairs which will control order of Contact objects returned.
  * @param Int         $offset               Starting row index.
  * @param Int         $row_count            Maximum number of rows to returns.
  *
  *
  * @return            $contactArray         Array of contacts who are members of the specified group
  *
  * @access public
  */
 static function getGroupContacts(&$group, $returnProperties = null, $status = 'Added', $sort = null, $offset = null, $row_count = null, $includeChildGroups = false)
 {
     $groupDAO =& new CRM_Contact_DAO_Group();
     $groupDAO->id = $group->id;
     if (!$groupDAO->find(true)) {
         return CRM_Core_Error::createError("Could not locate group with id: {$id}");
     }
     // make sure user has got permission to view this group
     require_once 'CRM/Contact/BAO/Group.php';
     if (!CRM_Contact_BAO_Group::checkPermission($groupDAO->id, $groupDAO->title)) {
         return CRM_Core_Error::createError("You do not have permission to access group with id: {$id}");
     }
     $query = '';
     if (empty($returnProperties)) {
         $query = "SELECT contact_a.id as contact_id,\n                      civicrm_email.email as email";
     } else {
         $query = "SELECT contact_a.id as contact_id , {$grpStatus} as status,";
         $query .= implode(',', $returnProperties);
     }
     $params = array();
     if ($includeChildGroups) {
         require_once 'CRM/Contact/BAO/GroupNesting.php';
         $groupIds = CRM_Contact_BAO_GroupNesting::getDescendentGroupIds(array($group->id));
     } else {
         $groupIds = array($group->id);
     }
     foreach ($groupIds as $groupId) {
         $params[] = array('group', 'IN', array($group->id => true), 0, 0);
     }
     require_once 'CRM/Core/BAO/Email.php';
     require_once 'CRM/Contact/BAO/Contact.php';
     $tables = array(CRM_Core_BAO_Email::getTableName() => true, CRM_Contact_BAO_Contact::getTableName() => true);
     $inner = array();
     $whereTables = array();
     $where = CRM_Contact_BAO_Query::getWhereClause($params, null, $tables, $whereTables);
     $permission = CRM_Core_Permission::whereClause(CRM_Core_Permission::VIEW, $tables, $whereTables);
     $from = CRM_Contact_BAO_Query::fromClause($tables, $inner);
     $query .= " {$from} WHERE {$permission} AND {$where} ";
     if ($sort != null) {
         $order = array();
         foreach ($sort as $key => $direction) {
             $order[] = " {$key} {$direction} ";
         }
         $query .= " ORDER BY " . implode(',', $order);
     }
     if (!is_null($offset) && !is_null($row_count)) {
         $query .= " LIMIT {$offset}, {$row_count}";
     }
     $dao =& new CRM_Contact_DAO_Contact();
     $dao->query($query);
     // this is quite inefficient, we need to change the return
     // values in docs
     $contactArray = array();
     while ($dao->fetch()) {
         $contactArray[] = clone $dao;
     }
     return $contactArray;
 }

 /**
  * Given a saved search compute the clause and the tables and store it for future use.
  */
 public function buildClause()
 {
     $fv = unserialize($this->form_values);
     if ($this->mapping_id) {
         $params = CRM_Core_BAO_Mapping::formattedFields($fv);
     } else {
         $params = CRM_Contact_BAO_Query::convertFormValues($fv);
     }
     if (!empty($params)) {
         $tables = $whereTables = array();
         $this->where_clause = CRM_Contact_BAO_Query::getWhereClause($params, NULL, $tables, $whereTables);
         if (!empty($tables)) {
             $this->select_tables = serialize($tables);
         }
         if (!empty($whereTables)) {
             $this->where_tables = serialize($whereTables);
         }
     }
 }

 /**
  * given a saved search compute the clause and the tables
  * and store it for future use
  */
 function buildClause()
 {
     $params = array(array('group', 'IN', array($this->id => 1), 0, 0));
     if (!empty($params)) {
         $tables = $whereTables = array();
         require_once 'CRM/Contact/BAO/Query.php';
         $this->where_clause = CRM_Contact_BAO_Query::getWhereClause($params, null, $tables, $whereTables);
         if (!empty($tables)) {
             $this->select_tables = serialize($tables);
         }
         if (!empty($whereTables)) {
             $this->where_tables = serialize($whereTables);
         }
     }
     return;
 }

 /**
  * get the where clause for a saved search
  *
  * @param int $id saved search id
  * @param  array $tables (reference ) add the tables that are needed for the select clause
  * @param  array $whereTables (reference ) add the tables that are needed for the where clause
  *
  * @return string the where clause for this saved search
  * @access public
  * @static
  */
 function whereClause($id, &$tables, &$whereTables)
 {
     $fv = CRM_Core_DAO::getFieldValue('CRM_Contact_DAO_SavedSearch', $id, 'form_values');
     if ($fv) {
         $fv = unserialize($fv);
         return CRM_Contact_BAO_Query::getWhereClause($fv, null, $tables, $whereTables);
     }
     return null;
 }

 /**
  * Given a saved search compute the clause and the tables
  * and store it for future use
  */
 public function buildClause()
 {
     $params = array(array('group', 'IN', array($this->id), 0, 0));
     if (!empty($params)) {
         $tables = $whereTables = array();
         $this->where_clause = CRM_Contact_BAO_Query::getWhereClause($params, NULL, $tables, $whereTables);
         if (!empty($tables)) {
             $this->select_tables = serialize($tables);
         }
         if (!empty($whereTables)) {
             $this->where_tables = serialize($whereTables);
         }
     }
 }

 /**
  * given a saved search compute the clause and the tables
  * and store it for future use
  */
 function buildClause()
 {
     $fv = unserialize($this->form_values);
     if ($this->mapping_id) {
         require_once 'CRM/Core/BAO/Mapping.php';
         $params = CRM_Core_BAO_Mapping::formattedFields($fv);
     } else {
         require_once 'CRM/Contact/BAO/Query.php';
         $params = CRM_Contact_BAO_Query::convertFormValues($fv);
     }
     if (!empty($params)) {
         $tables = $whereTables = array();
         $this->where_clause = CRM_Contact_BAO_Query::getWhereClause($params, null, $tables, $whereTables);
         if (!empty($tables)) {
             $this->select_tables = serialize($tables);
         }
         if (!empty($whereTables)) {
             $this->where_tables = serialize($whereTables);
         }
     }
     return;
 }

 /**
  * Returns array of contacts who are members of the specified group.
  *
  * @param CRM_Contact $group                A valid group object (passed by reference)
  * @param array       $returnProperties     Which properties
  *                    should be included in the returned Contact object(s). If NULL,
  *                    the default set of contact properties will be
  *                    included. group_contact properties (such as 'status',
  * '                  in_date', etc.) are included automatically.Note:Do not inclue
  *                    Id releted properties.  
  * @param text        $status               A valid status value ('Added', 'Pending', 'Removed').
  * @param text        $sort                 Associative array of
  *                    one or more "property_name"=>"sort direction"
  *                    pairs which will control order of Contact objects returned.
  * @param Int         $offset               Starting row index.
  * @param Int         $row_count            Maximum number of rows to returns.
  *
  *
  * @return            $contactArray         Array of contacts who are members of the specified group
  *
  * @access public
  */
 function getGroupContacts(&$group, $returnProperties = null, $status = 'Added', $sort = null, $offset = null, $row_count = null)
 {
     $query = "SELECT * FROM civicrm_group WHERE id = " . CRM_Utils_Type::escape($group->id, 'Integer');
     $groupDAO =& new CRM_Contact_DAO_Group();
     $groupDAO->id = $group->id;
     if (!$groupDAO->find(true)) {
         return CRM_Core_Error::createError("Could not locate group with id: {$id}");
     }
     // make sure user has got permission to view this group
     if (!CRM_Contact_BAO_Group::checkPermission($groupDAO->id, $groupDAO->title)) {
         return CRM_Core_Error::createError("You do not have permission to access group with id: {$id}");
     }
     $query = '';
     if (empty($returnProperties)) {
         $query = "SELECT civicrm_contact.id as contact_id,\n                      civicrm_email.email as email";
         //$query = "SELECT *,civicrm_contact.id as contact_id, (talk to lobo before re-enabling this)
         //civicrm_email.email as email";
     } else {
         $query = "SELECT civicrm_contact.id as contact_id ,";
         $query .= implode(',', $returnProperties);
     }
     $fv = array('group' => array($group->id => true));
     if ($status) {
         $fv['group_contact_status'] = array($status => true);
     } else {
         $fv['group_contact_status'] = array('Added' => true, 'Removed' => true, 'Pending' => true);
     }
     $tables = array(CRM_Contact_BAO_GroupContact::getTableName() => true, CRM_Core_BAO_Email::getTableName() => true, CRM_Contact_BAO_Contact::getTableName() => true, CRM_Contact_BAO_Group::getTableName() => true);
     $inner = array();
     $whereTables = array();
     $where = CRM_Contact_BAO_Query::getWhereClause($fv, null, $tables, $whereTables);
     $permission = CRM_Core_Permission::whereClause(CRM_CORE_PERMISSION_VIEW, $tables, $whereTables);
     $from = CRM_Contact_BAO_Query::fromClause($tables, $inner);
     $query .= " {$from} WHERE {$permission} AND {$where} ";
     if ($sort != null) {
         $order = array();
         foreach ($sort as $key => $direction) {
             $order[] = " {$key} {$direction} ";
         }
         $query .= " ORDER BY " . implode(',', $order);
     }
     if ($offset != null && $row_count != null) {
         $query .= " LIMIT {$offset}, {$row_count}";
     }
     // CRM_Core_Error::debug( 'q', $query );
     $dao =& new CRM_Contact_DAO_Contact();
     $dao->query($query);
     // this is quite inefficient, we need to change the return
     // values in docs
     $contactArray = array();
     while ($dao->fetch()) {
         $contactArray[] = clone $dao;
     }
     return $contactArray;
 }

 /**
  * function to get match clasue for dupe checking
  *
  * @param array   $params  the list of values to be used in the where clause
  * @param  array $tables (reference ) add the tables that are needed for the select clause
  *
  * @return string the where clause to include in a sql query
  * @static
  * @access public
  *
  */
 function getWhereClause($params, &$tables)
 {
     require_once 'CRM/Core/DAO/DupeMatch.php';
     if (is_array($params)) {
         if (is_array($params['location'])) {
             $params['email'] = array();
             $params['phone'] = array();
             $params['im'] = array();
             foreach ($params['location'] as $loc) {
                 foreach (array('email', 'phone', 'im') as $key) {
                     if (is_array($loc[$key])) {
                         foreach ($loc[$key] as $value) {
                             if (!empty($value[$key])) {
                                 $value[$key] = strtolower($value[$key]);
                                 $params[$key][] = '"' . addslashes($value[$key]) . '"';
                             }
                         }
                     }
                 }
             }
         }
         foreach (array('email', 'phone', 'im') as $key) {
             if (count($params[$key]) == 0) {
                 unset($params[$key]);
             }
         }
         foreach (array('street_address', 'supplemental_address_1', 'supplemental_address_2', 'state_province_id', 'postal_code', 'country_id') as $fld) {
             if (!empty($params['location'][1]['address'][$fld])) {
                 $params[$fld] = $params['location'][1]['address'][$fld];
             }
         }
         if (is_array($params['custom'])) {
             foreach ($params['custom'] as $key => $value) {
                 $params['custom_' . $value['custom_field_id']] = $value['value'];
             }
         }
     }
     $importableFields = CRM_Contact_BAO_Contact::importableFields();
     $dupeMatchDAO =& new CRM_Core_DAO_DupeMatch();
     $dupeMatchDAO->find();
     while ($dupeMatchDAO->fetch()) {
         $rule = explode('AND', $dupeMatchDAO->rule);
         foreach ($rule as $name) {
             $name = trim($name);
             $fields[$name] = array('name' => $name, 'title' => $importableFields[$name]['title'], 'where' => $importableFields[$name]['where']);
         }
     }
     require_once 'CRM/Contact/BAO/Query.php';
     //this is the fix to ignore the groups/ tags for dupe checking CRM-664, since we never use them for dupe checking
     $params['group'] = array();
     $params['tag'] = array();
     $whereTables = array();
     return CRM_Contact_BAO_Query::getWhereClause($params, $fields, $tables, $whereTables, true);
 }

 /**
  * Function to deal with groups that may have been mis-saved during a glitch.
  *
  * This deals with groups that may have been saved with differing mapping parameters than
  * the latest supported ones.
  *
  * @param int $id
  * @param array $formValues
  */
 public function tempFixFormValues($id, &$formValues)
 {
     $mappingID = CRM_Core_DAO::singleValueQuery('SELECT mapping_id FROM civicrm_saved_search WHERE id = %1', array(1 => array($id, 'Integer')));
     $addressFields = civicrm_api3('Address', 'getfields');
     $options = self::fieldOptions();
     $entities = array('contact', 'address', 'activity', 'participant', 'pledge', 'membership', 'contribution', 'case', 'grant');
     if (empty($formValues['mapper']) && $mappingID) {
         $mappingFields = CRM_Core_BAO_Mapping::getMappingFields($mappingID);
         foreach ($mappingFields[0][1] as $index => $mappingField) {
             $formValues['mapper'][1][$index] = array($mappingFields[1][1][$index], $mappingFields[0][1][$index]);
             $formValues['operator'][1][$index] = $mappingFields[6][1][$index];
             $formValues['value'][1][$index] = $mappingFields[7][1][$index];
         }
     }
     /*
       foreach ($formValues['mapper'] as $index => $fields) {
         foreach ($fields as $fieldIndex => $field) {
           $entity = $options[$field[1]];
           if ($entity == 'contact' || isset($addressFields['values'][$field[1]])) {
     
             $existing = (CRM_Core_DAO::singleValueQuery("SELECT contact_type FROM civicrm_mapping_field WHERE mapping_id = $mappingID AND grouping = $index AND column_number = $fieldIndex"));
             if ($existing != 'Contact') {
               $formValues['mapper'][$index][$fieldIndex][0] = 'Contact';
               //CRM_Core_DAO::executeQuery("UPDATE civicrm_mapping_field SET contact_type = 'Contact' WHERE mapping_id = $mappingID AND grouping = $index AND column_number = $fieldIndex");
             }
           }
           if (in_array($entity, $entities)) {
             $values = civicrm_api3($entity, 'getoptions', array('field' => $field[1]));
             $currentValue = $formValues['value'][$index][$fieldIndex];
             if (!isset($values['values'][$currentValue]) && in_array($currentValue, $values['values'])) {
               $newValue = array_search($currentValue, $values['values']);
               $formValues['value'][$index][$fieldIndex] = $newValue;
               //CRM_Core_DAO::executeQuery("UPDATE civicrm_mapping_field SET value = $newValue WHERE mapping_id = $mappingID AND grouping = $index AND column_number = $fieldIndex");
     
             }
           }
           /*
           $i = $index;
           while ($i > 0) {
             if ($fields == $formValues['mapper'][$i] && ($formValues['operator'][$i] == $formValues['operator'][$index]) && '=' == $formValues['operator'][$index]) {
             }
             $i--;
           }
     
         }
       }
     */
     $savedSearch = new CRM_Contact_DAO_SavedSearch();
     $savedSearch->id = $id;
     $savedSearch->find(TRUE);
     $tables = $whereTables = array();
     $savedSearch->where_clause = CRM_Contact_BAO_Query::getWhereClause($formValues, NULL, $tables, $whereTables);
     if (!empty($tables)) {
         $savedSearch->select_tables = serialize($tables);
     }
     if (!empty($whereTables)) {
         $savedSearch->where_tables = serialize($whereTables);
     }
     $savedSearch->form_values = serialize($formValues);
     $savedSearch->save();
 }