PHP CRM_Contact_BAO_Query::getGroupByFromSelectColumns方法代码示例

 public function groupBy()
 {
     parent::groupBy();
     if (empty($this->_groupBy) && $this->_totalPaid) {
         $groupBy = array("{$this->_aliases['civicrm_pledge']}.id", "{$this->_aliases['civicrm_pledge']}.currency");
         $this->_groupBy = CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, $groupBy);
     }
 }

 public function groupBy()
 {
     $this->assign('chartSupported', TRUE);
     $this->_groupBy = CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, "{$this->_aliases['civicrm_event']}.id");
 }

 public function groupBy()
 {
     $groupBy = array("{$this->_aliases['civicrm_relationship']}.{$this->householdContact}", "{$this->_aliases['civicrm_relationship']}.{$this->otherContact}", "{$this->_aliases['civicrm_contribution']}.id", "{$this->_aliases['civicrm_relationship']}.relationship_type_id");
     $this->_groupBy = CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, $groupBy);
 }

 public function groupBy()
 {
     // We override this function because we use GROUP functions in the
     // SELECT clause, therefore we have to group by *something*. If the
     // user doesn't select a column to group by, we should group by participant id.
     parent::groupBy();
     if (empty($this->_groupBy)) {
         $this->_groupBy = CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, "{$this->_aliases['civicrm_participant']}.id");
     }
 }

 public function groupBy()
 {
     $this->_groupBy = CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, "{$this->_aliases['civicrm_contribution_recur']}.id");
 }

 /**
  * Build group by clause.
  */
 public function groupBy()
 {
     $groupBys = array();
     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])) {
                         $groupBys[] = $field['dbAlias'];
                     }
                 }
             }
         }
     }
     if (!empty($groupBys)) {
         $this->_groupBy = CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, $groupBys);
     }
 }

 public function groupBy()
 {
     $groupBy = array("{$this->_aliases['civicrm_pledge']}.contact_id", "{$this->_aliases['civicrm_pledge']}.id", "{$this->_aliases['civicrm_pledge']}.currency");
     $this->_groupBy = CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, $groupBy);
 }

 /**
  * Build event report.
  *
  * @param array $eventIDs
  */
 public function buildEventReport($eventIDs)
 {
     $this->assign('events', $eventIDs);
     $eventID = implode(',', $eventIDs);
     $participantStatus = CRM_Event_PseudoConstant::participantStatus(NULL, "is_counted = 1");
     $participantRole = CRM_Event_PseudoConstant::participantRole();
     $paymentInstruments = CRM_Contribute_PseudoConstant::paymentInstrument();
     $rows = $eventSummary = $roleRows = $statusRows = $instrumentRows = $count = array();
     $optionGroupDAO = new CRM_Core_DAO_OptionGroup();
     $optionGroupDAO->name = 'event_type';
     $optionGroupId = NULL;
     if ($optionGroupDAO->find(TRUE)) {
         $optionGroupId = $optionGroupDAO->id;
     }
     //show the income of active participant status (Counted = filter = 1)
     $activeParticipantStatusIDArray = $activeParticipantStatusLabelArray = array();
     foreach ($participantStatus as $id => $label) {
         $activeParticipantStatusIDArray[] = $id;
         $activeParticipantStatusLabelArray[] = $label;
     }
     $activeParticipantStatus = implode(',', $activeParticipantStatusIDArray);
     $activeparticipnatStutusLabel = implode(', ', $activeParticipantStatusLabelArray);
     $activeParticipantClause = " AND civicrm_participant.status_id IN ( {$activeParticipantStatus} ) ";
     $select = array("civicrm_event.id as event_id", "civicrm_event.title as event_title", "civicrm_event.max_participants as max_participants", "civicrm_event.start_date as start_date", "civicrm_event.end_date as end_date", "civicrm_option_value.label as event_type", "civicrm_participant.fee_currency as currency");
     $groupBy = CRM_Contact_BAO_Query::getGroupByFromSelectColumns($select);
     $sql = "\n            SELECT  " . implode(', ', $select) . ",\n                    SUM(civicrm_participant.fee_amount) as total,\n                    COUNT(civicrm_participant.id)       as participant\n\n            FROM       civicrm_event\n            LEFT JOIN  civicrm_option_value\n                   ON  ( civicrm_event.event_type_id = civicrm_option_value.value AND\n                         civicrm_option_value.option_group_id = {$optionGroupId} )\n            LEFT JOIN  civicrm_participant ON ( civicrm_event.id = civicrm_participant.event_id\n                       {$activeParticipantClause} AND civicrm_participant.is_test  = 0 )\n\n            WHERE      civicrm_event.id IN( {$eventID}) {$groupBy}";
     $eventDAO = CRM_Core_DAO::executeQuery($sql);
     $currency = array();
     while ($eventDAO->fetch()) {
         $eventSummary[$eventDAO->event_id]['Title'] = $eventDAO->event_title;
         $eventSummary[$eventDAO->event_id]['Max Participants'] = $eventDAO->max_participants;
         $eventSummary[$eventDAO->event_id]['Start Date'] = CRM_Utils_Date::customFormat($eventDAO->start_date);
         $eventSummary[$eventDAO->event_id]['End Date'] = CRM_Utils_Date::customFormat($eventDAO->end_date);
         $eventSummary[$eventDAO->event_id]['Event Type'] = $eventDAO->event_type;
         $eventSummary[$eventDAO->event_id]['Event Income'] = CRM_Utils_Money::format($eventDAO->total, $eventDAO->currency);
         $eventSummary[$eventDAO->event_id]['Registered Participant'] = "{$eventDAO->participant} ({$activeparticipnatStutusLabel})";
         $currency[$eventDAO->event_id] = $eventDAO->currency;
     }
     $this->assign_by_ref('summary', $eventSummary);
     //Total Participant Registerd for the Event
     $pariticipantCount = "\n            SELECT COUNT(civicrm_participant.id ) as count, civicrm_participant.event_id as event_id\n\n            FROM     civicrm_participant\n\n            WHERE    civicrm_participant.event_id IN( {$eventID}) AND\n                     civicrm_participant.is_test  = 0\n                     {$activeParticipantClause}\n            GROUP BY civicrm_participant.event_id\n             ";
     $counteDAO = CRM_Core_DAO::executeQuery($pariticipantCount);
     while ($counteDAO->fetch()) {
         $count[$counteDAO->event_id] = $counteDAO->count;
     }
     // Count the Participant by Role ID for Event.
     $role = "\n            SELECT civicrm_participant.role_id         as ROLEID,\n                   COUNT( civicrm_participant.id )     as participant,\n                   SUM(civicrm_participant.fee_amount) as amount,\n                   civicrm_participant.event_id        as event_id,\n                   civicrm_participant.fee_currency    as currency\n            FROM     civicrm_participant\n\n            WHERE    civicrm_participant.event_id IN ( {$eventID}) AND\n                     civicrm_participant.is_test  = 0\n                     {$activeParticipantClause}\n            GROUP BY civicrm_participant.role_id, civicrm_participant.event_id, civicrm_participant.fee_currency\n            ";
     $roleDAO = CRM_Core_DAO::executeQuery($role);
     while ($roleDAO->fetch()) {
         // fix for multiple role, CRM-6507
         $roles = explode(CRM_Core_DAO::VALUE_SEPARATOR, $roleDAO->ROLEID);
         foreach ($roles as $roleId) {
             if (!isset($roleRows[$roleDAO->event_id][$participantRole[$roleId]])) {
                 $roleRows[$roleDAO->event_id][$participantRole[$roleId]]['total'] = 0;
                 $roleRows[$roleDAO->event_id][$participantRole[$roleId]]['round'] = 0;
                 $roleRows[$roleDAO->event_id][$participantRole[$roleId]]['amount'] = 0;
             }
             $roleRows[$roleDAO->event_id][$participantRole[$roleId]]['total'] += $roleDAO->participant;
             $roleRows[$roleDAO->event_id][$participantRole[$roleId]]['amount'] += $roleDAO->amount;
         }
     }
     foreach ($roleRows as $eventId => $roleInfo) {
         foreach ($participantRole as $roleName) {
             if (isset($roleInfo[$roleName])) {
                 $roleRows[$eventId][$roleName]['round'] = round($roleRows[$eventId][$roleName]['total'] / $count[$eventId] * 100, 2);
             }
             if (!empty($roleRows[$eventId][$roleName])) {
                 $roleRows[$eventId][$roleName]['amount'] = CRM_Utils_Money::format($roleRows[$eventId][$roleName]['amount'], $currency[$eventId]);
             }
         }
     }
     $rows['Role'] = $roleRows;
     // Count the Participant by status ID for Event.
     $status = "\n            SELECT civicrm_participant.status_id       as STATUSID,\n                   COUNT( civicrm_participant.id )     as participant,\n                   SUM(civicrm_participant.fee_amount) as amount,\n                   civicrm_participant.event_id        as event_id\n\n            FROM     civicrm_participant\n\n            WHERE    civicrm_participant.event_id IN ({$eventID}) AND\n                     civicrm_participant.is_test  = 0\n                     {$activeParticipantClause}\n            GROUP BY civicrm_participant.status_id, civicrm_participant.event_id\n            ";
     $statusDAO = CRM_Core_DAO::executeQuery($status);
     while ($statusDAO->fetch()) {
         $statusRows[$statusDAO->event_id][$participantStatus[$statusDAO->STATUSID]]['total'] = $statusDAO->participant;
         $statusRows[$statusDAO->event_id][$participantStatus[$statusDAO->STATUSID]]['round'] = round($statusDAO->participant / $count[$statusDAO->event_id] * 100, 2);
         $statusRows[$statusDAO->event_id][$participantStatus[$statusDAO->STATUSID]]['amount'] = CRM_Utils_Money::format($statusDAO->amount, $currency[$statusDAO->event_id]);
     }
     $rows['Status'] = $statusRows;
     //Count the Participant by payment instrument ID for Event
     //e.g. Credit Card, Check,Cash etc
     $paymentInstrument = "\n            SELECT c.payment_instrument_id               as INSTRUMENT,\n                   COUNT( civicrm_participant.id )       as participant,\n                   SUM( civicrm_participant.fee_amount ) as amount,\n                   civicrm_participant.event_id          as event_id\n\n            FROM      civicrm_participant,\n            civicrm_participant_payment pp\n            LEFT JOIN civicrm_contribution c ON ( pp.contribution_id = c.id)\n\n            WHERE     civicrm_participant.event_id IN ( {$eventID} )\n                      AND civicrm_participant.is_test  = 0\n                      {$activeParticipantClause}\n                      AND ((pp.participant_id = civicrm_participant.id )\n                           OR (pp.participant_id = civicrm_participant.registered_by_id ))\n            GROUP BY  c.payment_instrument_id, civicrm_participant.event_id\n            ";
     $instrumentDAO = CRM_Core_DAO::executeQuery($paymentInstrument);
     while ($instrumentDAO->fetch()) {
         //allow only if instrument is present in contribution table
         if ($instrumentDAO->INSTRUMENT) {
             $instrumentRows[$instrumentDAO->event_id][$paymentInstruments[$instrumentDAO->INSTRUMENT]]['total'] = $instrumentDAO->participant;
             $instrumentRows[$instrumentDAO->event_id][$paymentInstruments[$instrumentDAO->INSTRUMENT]]['round'] = round($instrumentDAO->participant / $count[$instrumentDAO->event_id] * 100, 2);
             $instrumentRows[$instrumentDAO->event_id][$paymentInstruments[$instrumentDAO->INSTRUMENT]]['amount'] = CRM_Utils_Money::format($instrumentDAO->amount, $currency[$instrumentDAO->event_id]);
         }
     }
     $rows['Payment Method'] = $instrumentRows;
     $this->assign_by_ref('rows', $rows);
//.........这里部分代码省略.........

 public function groupBy()
 {
     $this->_groupBy = CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, array("{$this->_aliases['civicrm_contact']}.id", "{$this->_aliases['civicrm_contribution']}.currency"));
 }

 /**
  * Get Query Group By Clause
  * @param int $exportMode
  *   Export Mode
  * @param string $queryMode
  *   Query Mode
  * @param array $returnProperties
  *   Return Properties
  * @param object $query
  *   CRM_Contact_BAO_Query
  *
  * @return string $groupBy
  *   Group By Clause
  */
 public static function getGroupBy($exportMode, $queryMode, $returnProperties, $query)
 {
     $groupBy = '';
     if (!empty($returnProperties['tags']) || !empty($returnProperties['groups']) || CRM_Utils_Array::value('notes', $returnProperties) || $queryMode & CRM_Contact_BAO_Query::MODE_CONTACTS && $query->_useGroupBy) {
         $groupBy = "contact_a.id";
     }
     switch ($exportMode) {
         case CRM_Export_Form_Select::CONTRIBUTE_EXPORT:
             $groupBy = 'civicrm_contribution.id';
             if (CRM_Contribute_BAO_Query::isSoftCreditOptionEnabled()) {
                 // especial group by  when soft credit columns are included
                 $groupBy = array('contribution_search_scredit_combined.id', 'contribution_search_scredit_combined.scredit_id');
             }
             break;
         case CRM_Export_Form_Select::EVENT_EXPORT:
             $groupBy = 'civicrm_participant.id';
             break;
         case CRM_Export_Form_Select::MEMBER_EXPORT:
             $groupBy = "civicrm_membership.id";
             break;
     }
     if ($queryMode & CRM_Contact_BAO_Query::MODE_ACTIVITY) {
         $groupBy = "civicrm_activity.id ";
     }
     if (!empty($groupBy)) {
         $groupBy = CRM_Contact_BAO_Query::getGroupByFromSelectColumns($query->_select, $groupBy);
     }
     return $groupBy;
 }

 public function groupBy()
 {
     $this->_groupBy = '';
     if ($this->has_grouping) {
         $groupBy = array("{$this->_aliases['civicrm_contact']}.id", "civicrm_activity_activity_date_time");
         if ($this->has_activity_type) {
             $groupBy[] = "{$this->_aliases['civicrm_activity']}.activity_type_id";
         }
         $this->_groupBy = CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, $groupBy);
     }
 }

 public function groupBy()
 {
     $this->_groupBy = " ";
     $groupBy = array();
     if ($this->relationType == 'a_b') {
         $groupBy[] = " {$this->_aliases['civicrm_contact']}.id";
     } elseif ($this->relationType == 'b_a') {
         $groupBy[] = " {$this->_aliases['civicrm_contact_b']}.id";
     }
     if (!empty($groupBy)) {
         $groupBy[] = "{$this->_aliases['civicrm_relationship']}.id";
     } else {
         $groupBy = array("{$this->_aliases['civicrm_relationship']}.id");
     }
     $this->_groupBy = CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, $groupBy);
 }

 public function groupBy()
 {
     $groupBy = array("{$this->_aliases['civicrm_entity_financial_trxn']}.id", "{$this->_aliases['civicrm_line_item']}.id");
     $this->_groupBy = CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, $groupBy);
 }

 public function groupBy($includeSelectCol = TRUE)
 {
     $this->_groupBy = array();
     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 = '';
                             }
                             $this->_groupBy[] = "{$append} {$this->_params['group_bys_freq'][$fieldName]}({$field['dbAlias']})";
                             $append = TRUE;
                         } else {
                             $this->_groupBy[] = $field['dbAlias'];
                         }
                     }
                 }
             }
         }
         $groupBy = $this->_groupBy;
         $this->_groupBy = "GROUP BY " . implode(', ', $this->_groupBy);
     } else {
         $groupBy = "{$this->_aliases['civicrm_activity']}.id";
         $this->_groupBy = "GROUP BY {$this->_aliases['civicrm_activity']}.id ";
     }
     if ($includeSelectCol) {
         $this->_groupBy = CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, $groupBy);
     }
 }

 /**
  * Get the rows for a browse operation.
  *
  * @param int $offset
  *   The row number to start from.
  * @param int $rowCount
  *   The nmber of rows to return.
  * @param string $sort
  *   The sql string that describes the sort order.
  *
  * @param null $additionalClause
  * @param array $additionalParams
  *
  * @return array
  *   The rows
  */
 public function &getRows($offset, $rowCount, $sort, $additionalClause = NULL, $additionalParams = NULL)
 {
     $mailing = self::getTableName();
     $job = CRM_Mailing_BAO_MailingJob::getTableName();
     $group = CRM_Mailing_DAO_MailingGroup::getTableName();
     $session = CRM_Core_Session::singleton();
     $mailingACL = self::mailingACL();
     //get all campaigns.
     $allCampaigns = CRM_Campaign_BAO_Campaign::getCampaigns(NULL, NULL, FALSE, FALSE, FALSE, TRUE);
     $select = array("{$mailing}.id", "{$mailing}.name", "{$job}.status", "{$mailing}.approval_status_id", "createdContact.sort_name as created_by", "scheduledContact.sort_name as scheduled_by", "{$mailing}.created_id as created_id", "{$mailing}.scheduled_id as scheduled_id", "{$mailing}.is_archived as archived", "{$mailing}.created_date as created_date", "campaign_id", "{$mailing}.sms_provider_id as sms_provider_id", "{$mailing}.language");
     // we only care about parent jobs, since that holds all the info on
     // the mailing
     $selectClause = implode(', ', $select);
     $groupFromSelect = CRM_Contact_BAO_Query::getGroupByFromSelectColumns($select, "{$mailing}.id");
     $query = "\n            SELECT      {$selectClause},\n                        MIN({$job}.scheduled_date) as scheduled_date,\n                        MIN({$job}.start_date) as start_date,\n                        MAX({$job}.end_date) as end_date\n            FROM        {$mailing}\n            LEFT JOIN   {$job} ON ( {$job}.mailing_id = {$mailing}.id AND {$job}.is_test = 0 AND {$job}.parent_id IS NULL )\n            LEFT JOIN   civicrm_contact createdContact ON ( civicrm_mailing.created_id = createdContact.id )\n            LEFT JOIN   civicrm_contact scheduledContact ON ( civicrm_mailing.scheduled_id = scheduledContact.id )\n            WHERE       {$mailingACL} {$additionalClause}";
     if (!empty($groupFromSelect)) {
         $query .= $groupFromSelect;
     }
     if ($sort) {
         $orderBy = trim($sort->orderBy());
         if (!empty($orderBy)) {
             $query .= " ORDER BY {$orderBy}";
         }
     }
     if ($rowCount) {
         $offset = CRM_Utils_Type::escape($offset, 'Int');
         $rowCount = CRM_Utils_Type::escape($rowCount, 'Int');
         $query .= " LIMIT {$offset}, {$rowCount} ";
     }
     if (!$additionalParams) {
         $additionalParams = array();
     }
     $dao = CRM_Core_DAO::executeQuery($query, $additionalParams);
     $rows = array();
     while ($dao->fetch()) {
         $rows[] = array('id' => $dao->id, 'name' => $dao->name, 'status' => $dao->status ? $dao->status : 'Not scheduled', 'created_date' => CRM_Utils_Date::customFormat($dao->created_date), 'scheduled' => CRM_Utils_Date::customFormat($dao->scheduled_date), 'scheduled_iso' => $dao->scheduled_date, 'start' => CRM_Utils_Date::customFormat($dao->start_date), 'end' => CRM_Utils_Date::customFormat($dao->end_date), 'created_by' => $dao->created_by, 'scheduled_by' => $dao->scheduled_by, 'created_id' => $dao->created_id, 'scheduled_id' => $dao->scheduled_id, 'archived' => $dao->archived, 'approval_status_id' => $dao->approval_status_id, 'campaign_id' => $dao->campaign_id, 'campaign' => empty($dao->campaign_id) ? NULL : $allCampaigns[$dao->campaign_id], 'sms_provider_id' => $dao->sms_provider_id, 'language' => $dao->language);
     }
     return $rows;
 }