PHP CRM_Contact_BAO_Query::appendAnyValueToSelect方法代码示例

 /**
  * Set group by clause.
  */
 public function groupBy()
 {
     $this->_groupBy = "";
     $append = FALSE;
     if (!empty($this->_params['group_bys']) && is_array($this->_params['group_bys'])) {
         foreach ($this->_columns as $tableName => $table) {
             if (array_key_exists('group_bys', $table)) {
                 foreach ($table['group_bys'] as $fieldName => $field) {
                     if (!empty($this->_params['group_bys'][$fieldName])) {
                         if (!empty($field['chart'])) {
                             $this->assign('chartSupported', TRUE);
                         }
                         if (!empty($table['group_bys'][$fieldName]['frequency']) && !empty($this->_params['group_bys_freq'][$fieldName])) {
                             $append = "YEAR({$field['dbAlias']});;";
                             if (in_array(strtolower($this->_params['group_bys_freq'][$fieldName]), array('year'))) {
                                 $append = '';
                             }
                             if ($this->_params['group_bys_freq'][$fieldName] == 'FISCALYEAR') {
                                 $this->_groupBy[] = self::fiscalYearOffset($field['dbAlias']);
                             } else {
                                 $this->_groupBy[] = "{$append} {$this->_params['group_bys_freq'][$fieldName]}({$field['dbAlias']})";
                             }
                             $append = TRUE;
                         } else {
                             $this->_groupBy[] = $field['dbAlias'];
                         }
                     }
                 }
             }
         }
         if (!empty($this->_statFields) && ($append && count($this->_groupBy) <= 1 || !$append) && !$this->_having) {
             $this->_rollup = " WITH ROLLUP";
         }
         $groupBy = array();
         foreach ($this->_groupBy as $key => $val) {
             if (strpos($val, ';;') !== FALSE) {
                 $groupBy = array_merge($groupBy, explode(';;', $val));
             } else {
                 $groupBy[] = $this->_groupBy[$key];
             }
         }
         $this->_groupBy = "GROUP BY " . implode(', ', $groupBy);
     } else {
         $groupBy = "{$this->_aliases['civicrm_contact']}.id";
         $this->_groupBy = "GROUP BY {$this->_aliases['civicrm_contact']}.id";
     }
     $this->_groupBy .= $this->_rollup;
     // append select with ANY_VALUE() keyword
     $this->_select = CRM_Contact_BAO_Query::appendAnyValueToSelect($this->_selectClauses, $groupBy);
 }

 public function groupBy()
 {
     $this->assign('chartSupported', TRUE);
     $this->_rollup = " WITH ROLLUP";
     $this->_select = CRM_Contact_BAO_Query::appendAnyValueToSelect($this->_selectClauses, "{$this->_aliases['civicrm_event']}.id");
     $this->_groupBy = " GROUP BY {$this->_aliases['civicrm_event']}.id {$this->_rollup}";
 }

 /**
  * Calculate section totals.
  *
  * When "order by" fields are marked as sections, this assigns to the template
  * an array of total counts for each section. This data is used by the Smarty
  * plugin {sectionTotal}.
  */
 public function sectionTotals()
 {
     // Reports using order_bys with sections must populate $this->_selectAliases in select() method.
     if (empty($this->_selectAliases)) {
         return;
     }
     if (!empty($this->_sections)) {
         // build the query with no LIMIT clause
         $select = str_ireplace('SELECT SQL_CALC_FOUND_ROWS ', 'SELECT ', $this->_select);
         $sql = "{$select} {$this->_from} {$this->_where} {$this->_groupBy} {$this->_having} {$this->_orderBy}";
         // pull section aliases out of $this->_sections
         $sectionAliases = array_keys($this->_sections);
         $ifnulls = array();
         foreach (array_merge($sectionAliases, $this->_selectAliases) as $alias) {
             $ifnulls[] = "ifnull({$alias}, '') as {$alias}";
         }
         $this->_select = "SELECT " . implode(", ", $ifnulls);
         $this->_select = CRM_Contact_BAO_Query::appendAnyValueToSelect($ifnulls, $sectionAliases);
         // Group (un-limited) report by all aliases and get counts. This might
         // be done more efficiently when the contents of $sql are known, ie. by
         // overriding this method in the report class.
         $query = $this->_select . ", count(*) as ct from ({$sql}) as subquery group by " . implode(", ", $sectionAliases);
         // initialize array of total counts
         $totals = array();
         $dao = CRM_Core_DAO::executeQuery($query);
         while ($dao->fetch()) {
             // let $this->_alterDisplay translate any integer ids to human-readable values.
             $rows[0] = $dao->toArray();
             $this->alterDisplay($rows);
             $row = $rows[0];
             // add totals for all permutations of section values
             $values = array();
             $i = 1;
             $aliasCount = count($sectionAliases);
             foreach ($sectionAliases as $alias) {
                 $values[] = $row[$alias];
                 $key = implode(CRM_Core_DAO::VALUE_SEPARATOR, $values);
                 if ($i == $aliasCount) {
                     // the last alias is the lowest-level section header; use count as-is
                     $totals[$key] = $dao->ct;
                 } else {
                     // other aliases are higher level; roll count into their total
                     $totals[$key] += $dao->ct;
                 }
             }
         }
         $this->assign('sectionTotals', $totals);
     }
 }

 public function groupBy()
 {
     $this->assign('chartSupported', TRUE);
     $fiscalYearOffset = self::fiscalYearOffset("{$this->_aliases['civicrm_contribution']}.receive_date");
     $this->_groupBy = "GROUP BY {$this->_aliases['civicrm_contribution']}.contact_id, {$fiscalYearOffset}";
     $this->_select = CRM_Contact_BAO_Query::appendAnyValueToSelect($this->_selectClauses, array("{$this->_aliases['civicrm_contribution']}.contact_id", $fiscalYearOffset));
     $this->_groupBy .= " {$this->_rollup}";
 }

 public function sectionTotals()
 {
     // Reports using order_bys with sections must populate $this->_selectAliases in select() method.
     if (empty($this->_selectAliases)) {
         return;
     }
     if (!empty($this->_sections)) {
         // pull section aliases out of $this->_sections
         $sectionAliases = array_keys($this->_sections);
         $ifnulls = array();
         foreach (array_merge($sectionAliases, $this->_selectAliases) as $alias) {
             $ifnulls[] = "ifnull({$alias}, '') as {$alias}";
         }
         $this->_select = "SELECT " . implode(", ", $ifnulls);
         $this->_select = CRM_Contact_BAO_Query::appendAnyValueToSelect($ifnulls, $sectionAliases);
         $query = $this->_select . ", count(DISTINCT civicrm_activity_id) as ct from civireport_activity_temp_target group by " . implode(", ", $sectionAliases);
         // initialize array of total counts
         $totals = array();
         $dao = CRM_Core_DAO::executeQuery($query);
         while ($dao->fetch()) {
             // let $this->_alterDisplay translate any integer ids to human-readable values.
             $rows[0] = $dao->toArray();
             $this->alterDisplay($rows);
             $row = $rows[0];
             // add totals for all permutations of section values
             $values = array();
             $i = 1;
             $aliasCount = count($sectionAliases);
             foreach ($sectionAliases as $alias) {
                 $values[] = $row[$alias];
                 $key = implode(CRM_Core_DAO::VALUE_SEPARATOR, $values);
                 if ($i == $aliasCount) {
                     // the last alias is the lowest-level section header; use count as-is
                     $totals[$key] = $dao->ct;
                 } else {
                     // other aliases are higher level; roll count into their total
                     $totals[$key] += $dao->ct;
                 }
             }
         }
         $this->assign('sectionTotals', $totals);
     }
 }

 /**
  * Get the list Activities.
  *
  * @param array $input
  *   Array of parameters.
  *    Keys include
  *    - contact_id  int            contact_id whose activities we want to retrieve
  *    - offset      int            which row to start from ?
  *    - rowCount    int            how many rows to fetch
  *    - sort        object|array   object or array describing sort order for sql query.
  *    - admin       boolean        if contact is admin
  *    - caseId      int            case ID
  *    - context     string         page on which selector is build
  *    - activity_type_id int|string the activitiy types we want to restrict by
  *
  * @return array
  *   Relevant data object values of open activities
  */
 public static function &getActivities($input)
 {
     // Step 1: Get the basic activity data.
     $bulkActivityTypeID = CRM_Core_OptionGroup::getValue('activity_type', 'Bulk Email', 'name');
     $activityContacts = CRM_Core_OptionGroup::values('activity_contacts', FALSE, FALSE, FALSE, NULL, 'name');
     $sourceID = CRM_Utils_Array::key('Activity Source', $activityContacts);
     $assigneeID = CRM_Utils_Array::key('Activity Assignees', $activityContacts);
     $targetID = CRM_Utils_Array::key('Activity Targets', $activityContacts);
     $config = CRM_Core_Config::singleton();
     $randomNum = md5(uniqid());
     $activityTempTable = "civicrm_temp_activity_details_{$randomNum}";
     $tableFields = array('activity_id' => 'int unsigned', 'activity_date_time' => 'datetime', 'source_record_id' => 'int unsigned', 'status_id' => 'int unsigned', 'subject' => 'varchar(255)', 'source_contact_name' => 'varchar(255)', 'activity_type_id' => 'int unsigned', 'activity_type' => 'varchar(128)', 'case_id' => 'int unsigned', 'case_subject' => 'varchar(255)', 'campaign_id' => 'int unsigned');
     $sql = "CREATE TEMPORARY TABLE {$activityTempTable} ( ";
     $insertValueSQL = array();
     // The activityTempTable contains the sorted rows
     // so in order to maintain the sort order as-is we add an auto_increment
     // field; we can sort by this later to ensure the sort order stays correct.
     $sql .= " fixed_sort_order INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,";
     foreach ($tableFields as $name => $desc) {
         $sql .= "{$name} {$desc},\n";
         $insertValueSQL[] = $name;
     }
     // add unique key on activity_id just to be sure
     // this cannot be primary key because we need that for the auto_increment
     // fixed_sort_order field
     $sql .= "\n          UNIQUE KEY ( activity_id )\n        ) ENGINE=HEAP DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci\n        ";
     CRM_Core_DAO::executeQuery($sql);
     $insertSQL = "INSERT INTO {$activityTempTable} (" . implode(',', $insertValueSQL) . " ) ";
     $order = $limit = $groupBy = '';
     $groupBy = " GROUP BY tbl.activity_id, tbl.activity_type, tbl.case_id, tbl.case_subject ";
     if (!empty($input['sort'])) {
         if (is_a($input['sort'], 'CRM_Utils_Sort')) {
             $orderBy = $input['sort']->orderBy();
             if (!empty($orderBy)) {
                 $order = " ORDER BY {$orderBy}";
             }
         } elseif (trim($input['sort'])) {
             $sort = CRM_Utils_Type::escape($input['sort'], 'String');
             $order = " ORDER BY {$sort} ";
         }
     }
     if (empty($order)) {
         // context = 'activity' in Activities tab.
         $order = CRM_Utils_Array::value('context', $input) == 'activity' ? " ORDER BY tbl.activity_date_time desc " : " ORDER BY tbl.status_id asc, tbl.activity_date_time asc ";
     }
     if (!empty($input['rowCount']) && $input['rowCount'] > 0) {
         $limit = " LIMIT {$input['offset']}, {$input['rowCount']} ";
     }
     $input['count'] = FALSE;
     list($sqlClause, $params) = self::getActivitySQLClause($input);
     $query = "{$insertSQL}\n       SELECT DISTINCT tbl.*  from ( {$sqlClause} )\nas tbl ";
     // Filter case activities - CRM-5761.
     $components = self::activityComponents();
     if (!in_array('CiviCase', $components)) {
         $query .= "\nLEFT JOIN  civicrm_case_activity ON ( civicrm_case_activity.activity_id = tbl.activity_id )\n    WHERE  civicrm_case_activity.id IS NULL";
     }
     $query = $query . $groupBy . $order . $limit;
     $dao = CRM_Core_DAO::executeQuery($query, $params);
     // step 2: Get target and assignee contacts for above activities
     // create temp table for target contacts
     $activityContactTempTable = "civicrm_temp_activity_contact_{$randomNum}";
     $query = "CREATE TEMPORARY TABLE {$activityContactTempTable} (\n                activity_id int unsigned, contact_id int unsigned, record_type_id varchar(16),\n                 contact_name varchar(255), is_deleted int unsigned, counter int unsigned, INDEX index_activity_id( activity_id ) )\n                ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci";
     CRM_Core_DAO::executeQuery($query);
     // note that we ignore bulk email for targets, since we don't show it in selector
     $query = "\nINSERT INTO {$activityContactTempTable} ( activity_id, contact_id, record_type_id, contact_name, is_deleted )\nSELECT     ac.activity_id,\n           ac.contact_id,\n           ac.record_type_id,\n           c.sort_name,\n           c.is_deleted\nFROM       {$activityTempTable}\nINNER JOIN civicrm_activity a ON ( a.id = {$activityTempTable}.activity_id )\nINNER JOIN civicrm_activity_contact ac ON ( ac.activity_id = {$activityTempTable}.activity_id )\nINNER JOIN civicrm_contact c ON c.id = ac.contact_id\nWHERE ac.record_type_id != %1\n";
     $params = array(1 => array($targetID, 'Integer'));
     CRM_Core_DAO::executeQuery($query, $params);
     $activityFields = array("ac.activity_id", "ac.contact_id", "ac.record_type_id", "c.sort_name", "c.is_deleted");
     $select = CRM_Contact_BAO_Query::appendAnyValueToSelect($activityFields, "ac.activity_id");
     // for each activity insert one target contact
     // if we load all target contacts the performance will suffer a lot for mass-activities.
     $query = "\nINSERT INTO {$activityContactTempTable} ( activity_id, contact_id, record_type_id, contact_name, is_deleted, counter )\n{$select}, count(ac.contact_id)\nFROM       {$activityTempTable}\nINNER JOIN civicrm_activity a ON ( a.id = {$activityTempTable}.activity_id )\nINNER JOIN civicrm_activity_contact ac ON ( ac.activity_id = {$activityTempTable}.activity_id )\nINNER JOIN civicrm_contact c ON c.id = ac.contact_id\nWHERE ac.record_type_id = %1\nGROUP BY ac.activity_id\n";
     CRM_Core_DAO::executeQuery($query, $params);
     // step 3: Combine all temp tables to get final query for activity selector
     // sort by the original sort order, stored in fixed_sort_order
     $query = "\nSELECT     {$activityTempTable}.*,\n           {$activityContactTempTable}.contact_id,\n           {$activityContactTempTable}.record_type_id,\n           {$activityContactTempTable}.contact_name,\n           {$activityContactTempTable}.is_deleted,\n           {$activityContactTempTable}.counter,\n           re.parent_id as is_recurring_activity\nFROM       {$activityTempTable}\nINNER JOIN {$activityContactTempTable} on {$activityTempTable}.activity_id = {$activityContactTempTable}.activity_id\nLEFT JOIN civicrm_recurring_entity re on {$activityContactTempTable}.activity_id = re.entity_id\nORDER BY    fixed_sort_order\n        ";
     $dao = CRM_Core_DAO::executeQuery($query);
     // CRM-3553, need to check user has access to target groups.
     $mailingIDs = CRM_Mailing_BAO_Mailing::mailingACLIDs();
     $accessCiviMail = CRM_Core_Permission::check('access CiviMail') || CRM_Mailing_Info::workflowEnabled() && CRM_Core_Permission::check('create mailings');
     // Get all campaigns.
     $allCampaigns = CRM_Campaign_BAO_Campaign::getCampaigns(NULL, NULL, FALSE, FALSE, FALSE, TRUE);
//.........这里部分代码省略.........

 public function groupBy()
 {
     $this->_groupBy = "";
     $append = FALSE;
     if (is_array($this->_params['group_bys']) && !empty($this->_params['group_bys'])) {
         foreach ($this->_columns as $tableName => $table) {
             if (array_key_exists('group_bys', $table)) {
                 foreach ($table['group_bys'] as $fieldName => $field) {
                     if (!empty($this->_params['group_bys'][$fieldName])) {
                         if (!empty($field['chart'])) {
                             $this->assign('chartSupported', TRUE);
                         }
                         if (!empty($table['group_bys'][$fieldName]['frequency']) && !empty($this->_params['group_bys_freq'][$fieldName])) {
                             $append = "YEAR({$field['dbAlias']}),";
                             if (in_array(strtolower($this->_params['group_bys_freq'][$fieldName]), array('year'))) {
                                 $append = '';
                             }
                             $this->_groupBy[] = "{$append} {$this->_params['group_bys_freq'][$fieldName]}({$field['dbAlias']})";
                             $append = TRUE;
                         } else {
                             $this->_groupBy[] = $field['dbAlias'];
                         }
                     }
                 }
             }
         }
         if (!empty($this->_statFields) && ($append && count($this->_groupBy) <= 1 || !$append) && !$this->_having) {
             $this->_rollup = " WITH ROLLUP";
         }
         $groupBy = $this->_groupBy;
         $this->_groupBy = "GROUP BY " . implode(', ', $this->_groupBy);
     } else {
         $groupBy = "{$this->_aliases['civicrm_contact']}.id";
         $this->_groupBy = "GROUP BY {$groupBy}";
     }
     $this->_select = CRM_Contact_BAO_Query::appendAnyValueToSelect($this->_selectClauses, $groupBy);
     $this->_groupBy .= " {$this->_rollup}";
 }

 public function sectionTotals()
 {
     // Reports using order_bys with sections must populate $this->_selectAliases in select() method.
     if (empty($this->_selectAliases)) {
         return;
     }
     if (!empty($this->_sections)) {
         // build the query with no LIMIT clause
         $select = str_ireplace('SELECT SQL_CALC_FOUND_ROWS ', 'SELECT ', $this->_select);
         $sql = "{$select} {$this->_from} {$this->_where} {$this->_groupBy} {$this->_having} {$this->_orderBy}";
         // pull section aliases out of $this->_sections
         $sectionAliases = array_keys($this->_sections);
         $ifnulls = array();
         foreach (array_merge($sectionAliases, $this->_selectAliases) as $alias) {
             $ifnulls[] = "ifnull({$alias}, '') as {$alias}";
         }
         $this->_select = "SELECT " . implode(", ", $ifnulls);
         $this->_select = CRM_Contact_BAO_Query::appendAnyValueToSelect($ifnulls, $sectionAliases);
         /* Group (un-limited) report by all aliases and get counts. This might
          * be done more efficiently when the contents of $sql are known, ie. by
          * overriding this method in the report class.
          */
         $addtotals = '';
         if (array_search("civicrm_contribution_total_amount_sum", $this->_selectAliases) !== FALSE) {
             $addtotals = ", sum(civicrm_contribution_total_amount_sum) as sumcontribs";
             $showsumcontribs = TRUE;
         }
         $query = $this->_select . "{$addtotals}, count(*) as ct from civireport_contribution_detail_temp3 group by " . implode(", ", $sectionAliases);
         // initialize array of total counts
         $sumcontribs = $totals = array();
         $dao = CRM_Core_DAO::executeQuery($query);
         while ($dao->fetch()) {
             // let $this->_alterDisplay translate any integer ids to human-readable values.
             $rows[0] = $dao->toArray();
             $this->alterDisplay($rows);
             $row = $rows[0];
             // add totals for all permutations of section values
             $values = array();
             $i = 1;
             $aliasCount = count($sectionAliases);
             foreach ($sectionAliases as $alias) {
                 $values[] = $row[$alias];
                 $key = implode(CRM_Core_DAO::VALUE_SEPARATOR, $values);
                 if ($i == $aliasCount) {
                     // the last alias is the lowest-level section header; use count as-is
                     $totals[$key] = $dao->ct;
                     if ($showsumcontribs) {
                         $sumcontribs[$key] = $dao->sumcontribs;
                     }
                 } else {
                     // other aliases are higher level; roll count into their total
                     $totals[$key] = array_key_exists($key, $totals) ? $totals[$key] + $dao->ct : $dao->ct;
                     if ($showsumcontribs) {
                         $sumcontribs[$key] = array_key_exists($key, $sumcontribs) ? $sumcontribs[$key] + $dao->sumcontribs : $dao->sumcontribs;
                     }
                 }
             }
         }
         if ($showsumcontribs) {
             $totalandsum = array();
             // ts exception to avoid having ts("%1 %2: %3")
             $title = '%1 contributions / soft-credits: %2';
             if (CRM_Utils_Array::value('contribution_or_soft_value', $this->_params) == 'contributions_only') {
                 $title = '%1 contributions: %2';
             } elseif (CRM_Utils_Array::value('contribution_or_soft_value', $this->_params) == 'soft_credits_only') {
                 $title = '%1 soft-credits: %2';
             }
             foreach ($totals as $key => $total) {
                 $totalandsum[$key] = ts($title, array(1 => $total, 2 => CRM_Utils_Money::format($sumcontribs[$key])));
             }
             $this->assign('sectionTotals', $totalandsum);
         } else {
             $this->assign('sectionTotals', $totals);
         }
     }
 }

 public function groupBy()
 {
     $this->_rollup = 'WITH ROLLUP';
     $this->_select = CRM_Contact_BAO_Query::appendAnyValueToSelect($this->selectClause, array("{$this->_aliases['civicrm_contribution_soft']}.contact_id", "constituentname.id"));
     $this->_groupBy = "\nGROUP BY {$this->_aliases['civicrm_contribution_soft']}.contact_id, constituentname.id {$this->_rollup}";
 }