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);
}
