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"); }
public function groupBy() { $this->_groupBy = ""; 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->_groupBy[] = "{$this->_params['group_bys_freq'][$fieldName]}({$field['dbAlias']})"; $append = TRUE; } else { $this->_groupBy[] = $field['dbAlias']; } } } } } $this->_rollup = ' WITH ROLLUP'; $this->_select = CRM_Contact_BAO_Query::appendAnyValueToSelect($this->_selectClauses, array_filter($this->_groupBy)); $this->_groupBy = 'GROUP BY ' . implode(', ', array_filter($this->_groupBy)) . " {$this->_rollup} "; } else { $this->_groupBy = CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, "{$this->_aliases['civicrm_membership']}.join_date"); } }
/** * 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); if (!$this->_setVariable) { $this->_params['id_value'] = NULL; } $this->assign('statistics', $this->statistics($eventIDs)); }
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; }
/** * This function retrieve component related contact information. * * @param array $componentIds * Array of component Ids. * @param string $componentName * @param array $returnProperties * Array of return elements. * * @return array * array of contact info. */ public static function contactDetails($componentIds, $componentName, $returnProperties = array()) { $contactDetails = array(); if (empty($componentIds) || !in_array($componentName, array('CiviContribute', 'CiviMember', 'CiviEvent', 'Activity'))) { return $contactDetails; } if (empty($returnProperties)) { $autocompleteContactSearch = CRM_Core_BAO_Setting::valueOptions(CRM_Core_BAO_Setting::SYSTEM_PREFERENCES_NAME, 'contact_autocomplete_options'); $returnProperties = array_fill_keys(array_merge(array('sort_name'), array_keys($autocompleteContactSearch)), 1); } $compTable = NULL; if ($componentName == 'CiviContribute') { $compTable = 'civicrm_contribution'; } elseif ($componentName == 'CiviMember') { $compTable = 'civicrm_membership'; } elseif ($componentName == 'Activity') { $compTable = 'civicrm_activity'; $activityContacts = CRM_Core_OptionGroup::values('activity_contacts', FALSE, FALSE, FALSE, NULL, 'name'); } else { $compTable = 'civicrm_participant'; } $select = $from = array(); foreach ($returnProperties as $property => $ignore) { $value = in_array($property, array('city', 'street_address')) ? 'address' : $property; switch ($property) { case 'sort_name': if ($componentName == 'Activity') { $sourceID = CRM_Utils_Array::key('Activity Source', $activityContacts); $select[] = "contact.{$property} as {$property}"; $from[$value] = "\nINNER JOIN civicrm_activity_contact acs ON (acs.activity_id = {$compTable}.id AND acs.record_type_id = {$sourceID})\nINNER JOIN civicrm_contact contact ON ( contact.id = acs.contact_id )"; } else { $select[] = "{$property} as {$property}"; $from[$value] = "INNER JOIN civicrm_contact contact ON ( contact.id = {$compTable}.contact_id )"; } break; case 'target_sort_name': $targetID = CRM_Utils_Array::key('Activity Targets', $activityContacts); $select[] = "contact_target.sort_name as {$property}"; $from[$value] = "\nINNER JOIN civicrm_activity_contact act ON (act.activity_id = {$compTable}.id AND act.record_type_id = {$targetID})\nINNER JOIN civicrm_contact contact_target ON ( contact_target.id = act.contact_id )"; break; case 'email': case 'phone': case 'city': case 'street_address': $select[] = "{$property} as {$property}"; // Grab target contact properties if this is for activity if ($componentName == 'Activity') { $from[$value] = "LEFT JOIN civicrm_{$value} {$value} ON ( contact_target.id = {$value}.contact_id AND {$value}.is_primary = 1 ) "; } else { $from[$value] = "LEFT JOIN civicrm_{$value} {$value} ON ( contact.id = {$value}.contact_id AND {$value}.is_primary = 1 ) "; } break; case 'country': case 'state_province': $select[] = "{$property}.name as {$property}"; if (!in_array('address', $from)) { // Grab target contact properties if this is for activity if ($componentName == 'Activity') { $from['address'] = 'LEFT JOIN civicrm_address address ON ( contact_target.id = address.contact_id AND address.is_primary = 1) '; } else { $from['address'] = 'LEFT JOIN civicrm_address address ON ( contact.id = address.contact_id AND address.is_primary = 1) '; } } $from[$value] = " LEFT JOIN civicrm_{$value} {$value} ON ( address.{$value}_id = {$value}.id ) "; break; } } //finally retrieve contact details. if (!empty($select) && !empty($from)) { $fromClause = implode(' ', $from); $selectClause = implode(', ', $select); $whereClause = "{$compTable}.id IN (" . implode(',', $componentIds) . ')'; $groupBy = CRM_Contact_BAO_Query::getGroupByFromSelectColumns($select, array("{$compTable}.id", 'contact.id')); $query = "\n SELECT contact.id as contactId, {$compTable}.id as componentId, {$selectClause}\n FROM {$compTable} as {$compTable} {$fromClause}\n WHERE {$whereClause}\n {$groupBy}"; $contact = CRM_Core_DAO::executeQuery($query); while ($contact->fetch()) { $contactDetails[$contact->componentId]['contact_id'] = $contact->contactId; foreach ($returnProperties as $property => $ignore) { $contactDetails[$contact->componentId][$property] = $contact->{$property}; } } $contact->free(); } return $contactDetails; }
public function postProcess() { //reset value of activity_date if (!empty($this->_resetDateFilter)) { $this->_formValues["activity_date_time_relative"] = NULL; } $this->beginPostProcess(); //Assign those recordtype to array which have filter operator as 'Is not empty' or 'Is empty' $nullFilters = array(); foreach (array('target', 'source', 'assignee') as $type) { if (CRM_Utils_Array::value("contact_{$type}_op", $this->_params) == 'nnll' || !empty($this->_params["contact_{$type}_value"])) { $nullFilters[] = " civicrm_contact_contact_{$type}_id IS NOT NULL "; } elseif (CRM_Utils_Array::value("contact_{$type}_op", $this->_params) == 'nll') { $nullFilters[] = " civicrm_contact_contact_{$type}_id IS NULL "; } } // 1. fill temp table with target results $this->buildACLClause(array('civicrm_contact_target')); $this->select('target'); $this->from('target'); $this->customDataFrom(); $this->where('target'); $insertCols = implode(',', $this->_selectAliases); $tempQuery = "CREATE TEMPORARY TABLE civireport_activity_temp_target CHARACTER SET utf8 COLLATE utf8_unicode_ci AS\n{$this->_select} {$this->_from} {$this->_where} "; CRM_Core_DAO::executeQuery($tempQuery); // 2. add new columns to hold assignee and source results // fixme: add when required $tempQuery = "\n ALTER TABLE civireport_activity_temp_target\n MODIFY COLUMN civicrm_contact_contact_target_id VARCHAR(128),\n ADD COLUMN civicrm_contact_contact_assignee VARCHAR(128),\n ADD COLUMN civicrm_contact_contact_source VARCHAR(128),\n ADD COLUMN civicrm_contact_contact_assignee_id VARCHAR(128),\n ADD COLUMN civicrm_contact_contact_source_id VARCHAR(128),\n ADD COLUMN civicrm_phone_contact_assignee_phone VARCHAR(128),\n ADD COLUMN civicrm_phone_contact_source_phone VARCHAR(128),\n ADD COLUMN civicrm_email_contact_assignee_email VARCHAR(128),\n ADD COLUMN civicrm_email_contact_source_email VARCHAR(128)"; CRM_Core_DAO::executeQuery($tempQuery); // 3. fill temp table with assignee results $this->buildACLClause(array('civicrm_contact_assignee')); $this->select('assignee'); $this->from('assignee'); $this->customDataFrom(); $this->where('assignee'); $insertCols = implode(',', $this->_selectAliases); $tempQuery = "INSERT INTO civireport_activity_temp_target ({$insertCols})\n{$this->_select}\n{$this->_from} {$this->_where}"; CRM_Core_DAO::executeQuery($tempQuery); // 4. fill temp table with source results $this->buildACLClause(array('civicrm_contact_source')); $this->select('source'); $this->from('source'); $this->customDataFrom(); $this->where('source'); $insertCols = implode(',', $this->_selectAliases); $tempQuery = "INSERT INTO civireport_activity_temp_target ({$insertCols})\n{$this->_select}\n{$this->_from} {$this->_where}"; CRM_Core_DAO::executeQuery($tempQuery); // 5. show final result set from temp table $rows = array(); $this->select('final'); $this->_having = ""; if (!empty($nullFilters)) { $this->_having = "HAVING " . implode(' AND ', $nullFilters); } $this->orderBy(); foreach ($this->_sections as $alias => $section) { if (!empty($section) && $section['name'] == 'activity_date_time') { $this->alterSectionHeaderForDateTime('civireport_activity_temp_target', $section['tplField']); } } $this->limit(); $groupByFromSelect = CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, 'civicrm_activity_id'); $sql = "{$this->_select}\nFROM civireport_activity_temp_target tar\n{$groupByFromSelect} {$this->_having} {$this->_orderBy} {$this->_limit}"; $this->buildRows($sql, $rows); // format result set. $this->formatDisplay($rows); // assign variables to templates $this->doTemplateAssignment($rows); // do print / pdf / instance stuff if needed $this->endPostProcess($rows); }
public function groupBy() { $groupBy = array("{$this->_aliases['civicrm_mailing']}.id"); $this->_groupBy = CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, $groupBy); }
public function groupBy() { $this->_groupBy = CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, "{$this->_aliases['civicrm_participant']}.id"); }
public function groupBy() { if (!empty($this->_params['charts'])) { $groupBy = "{$this->_aliases['civicrm_mailing']}.id"; } else { $groupBy = "{$this->_aliases['civicrm_mailing_event_bounce']}.id"; } $this->_groupBy = CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, $groupBy); }
public function groupBy() { $groupBy = array("{$this->_aliases['civicrm_contribution']}.contact_id", "YEAR({$this->_aliases['civicrm_contribution']}.receive_date)"); $this->_groupBy = CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, $groupBy); }