/** * Retrieve financial items assigned for a batch. * * @param int $entityID * @param array $returnValues * @param bool $notPresent * @param array $params * @param bool $getCount * * @return CRM_Core_DAO */ public static function getBatchFinancialItems($entityID, $returnValues, $notPresent = NULL, $params = NULL, $getCount = FALSE) { if (!$getCount) { if (!empty($params['rowCount']) && $params['rowCount'] > 0) { $limit = " LIMIT {$params['offset']}, {$params['rowCount']} "; } } // action is taken depending upon the mode $select = 'civicrm_financial_trxn.id '; if (!empty($returnValues)) { $select .= " , " . implode(' , ', $returnValues); } $orderBy = " ORDER BY civicrm_financial_trxn.id"; if (!empty($params['sort'])) { $orderBy = ' ORDER BY ' . CRM_Utils_Type::escape($params['sort'], 'String'); } $from = "civicrm_financial_trxn\nLEFT JOIN civicrm_entity_financial_trxn ON civicrm_entity_financial_trxn.financial_trxn_id = civicrm_financial_trxn.id\nLEFT JOIN civicrm_entity_batch ON civicrm_entity_batch.entity_table = 'civicrm_financial_trxn'\nAND civicrm_entity_batch.entity_id = civicrm_financial_trxn.id\nLEFT JOIN civicrm_contribution ON civicrm_contribution.id = civicrm_entity_financial_trxn.entity_id\nLEFT JOIN civicrm_financial_type ON civicrm_financial_type.id = civicrm_contribution.financial_type_id\nLEFT JOIN civicrm_contact contact_a ON contact_a.id = civicrm_contribution.contact_id\nLEFT JOIN civicrm_contribution_soft ON civicrm_contribution_soft.contribution_id = civicrm_contribution.id\n"; $searchFields = array('sort_name', 'financial_type_id', 'contribution_page_id', 'payment_instrument_id', 'contribution_trxn_id', 'contribution_source', 'contribution_currency_type', 'contribution_pay_later', 'contribution_recurring', 'contribution_test', 'contribution_thankyou_date_is_not_null', 'contribution_receipt_date_is_not_null', 'contribution_pcp_made_through_id', 'contribution_pcp_display_in_roll', 'contribution_date_relative', 'contribution_amount_low', 'contribution_amount_high', 'contribution_in_honor_of', 'contact_tags', 'group', 'contribution_date_relative', 'contribution_date_high', 'contribution_date_low', 'contribution_check_number', 'contribution_status_id'); $values = array(); foreach ($searchFields as $field) { if (isset($params[$field])) { $values[$field] = $params[$field]; if ($field == 'sort_name') { $from .= " LEFT JOIN civicrm_contact contact_b ON contact_b.id = civicrm_contribution.contact_id\n LEFT JOIN civicrm_email ON contact_b.id = civicrm_email.contact_id"; } if ($field == 'contribution_in_honor_of') { $from .= " LEFT JOIN civicrm_contact contact_b ON contact_b.id = civicrm_contribution.contact_id"; } if ($field == 'contact_tags') { $from .= " LEFT JOIN civicrm_entity_tag `civicrm_entity_tag-{$params[$field]}` ON `civicrm_entity_tag-{$params[$field]}`.entity_id = contact_a.id"; } if ($field == 'group') { $from .= " LEFT JOIN civicrm_group_contact `civicrm_group_contact-{$params[$field]}` ON contact_a.id = `civicrm_group_contact-{$params[$field]}`.contact_id "; } if ($field == 'contribution_date_relative') { $relativeDate = explode('.', $params[$field]); $date = CRM_Utils_Date::relativeToAbsolute($relativeDate[0], $relativeDate[1]); $values['contribution_date_low'] = $date['from']; $values['contribution_date_high'] = $date['to']; } $searchParams = CRM_Contact_BAO_Query::convertFormValues($values); $query = new CRM_Contact_BAO_Query($searchParams, CRM_Contribute_BAO_Query::defaultReturnProperties(CRM_Contact_BAO_Query::MODE_CONTRIBUTE, FALSE), NULL, FALSE, FALSE, CRM_Contact_BAO_Query::MODE_CONTRIBUTE); if ($field == 'contribution_date_high' || $field == 'contribution_date_low') { $query->dateQueryBuilder($params[$field], 'civicrm_contribution', 'contribution_date', 'receive_date', 'Contribution Date'); } } } if (!empty($query->_where[0])) { $where = implode(' AND ', $query->_where[0]) . " AND civicrm_entity_batch.batch_id IS NULL\n AND civicrm_entity_financial_trxn.entity_table = 'civicrm_contribution'"; $where = str_replace('civicrm_contribution.payment_instrument_id', 'civicrm_financial_trxn.payment_instrument_id', $where); $searchValue = TRUE; } else { $searchValue = FALSE; } if (!$searchValue) { if (!$notPresent) { $where = " ( civicrm_entity_batch.batch_id = {$entityID}\n AND civicrm_entity_batch.entity_table = 'civicrm_financial_trxn'\n AND civicrm_entity_financial_trxn.entity_table = 'civicrm_contribution') "; } else { $where = " ( civicrm_entity_batch.batch_id IS NULL\n AND civicrm_entity_financial_trxn.entity_table = 'civicrm_contribution')"; } } $sql = "\nSELECT {$select}\nFROM {$from}\nWHERE {$where}\n {$orderBy}\n"; if (isset($limit)) { $sql .= "{$limit}"; } $result = CRM_Core_DAO::executeQuery($sql); return $result; }
/** * Generate where for a single parameter. * * @param array $values * @param CRM_Contact_BAO_Query $query */ public static function whereClauseSingle(&$values, &$query) { list($name, $op, $value, $grouping) = $values; switch ($name) { case 'member_join_date_low': case 'member_join_date_high': $query->dateQueryBuilder($values, 'civicrm_membership', 'member_join_date', 'join_date', 'Member Since'); return; case 'member_start_date_low': case 'member_start_date_high': $query->dateQueryBuilder($values, 'civicrm_membership', 'member_start_date', 'start_date', 'Start Date'); return; case 'member_end_date_low': case 'member_end_date_high': $query->dateQueryBuilder($values, 'civicrm_membership', 'member_end_date', 'end_date', 'End Date'); return; case 'member_join_date': $op = '>='; $date = CRM_Utils_Date::format($value); if ($date) { $query->_where[$grouping][] = "civicrm_membership.join_date {$op} {$date}"; $format = CRM_Utils_Date::customFormat(CRM_Utils_Date::format(array_reverse($value), '-')); $query->_qill[$grouping][] = ts('Member Since %2 %1', array(1 => $format, 2 => $op)); } return; case 'member_source': $strtolower = function_exists('mb_strtolower') ? 'mb_strtolower' : 'strtolower'; $value = $strtolower(CRM_Core_DAO::escapeString(trim($value))); $query->_where[$grouping][] = "civicrm_membership.source {$op} '{$value}'"; $query->_qill[$grouping][] = ts('Source %2 %1', array(1 => $value, 2 => $op)); $query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1; return; // CRM-17011 These 2 variants appear in some smart groups saved at some time prior to 4.6.6. // CRM-17011 These 2 variants appear in some smart groups saved at some time prior to 4.6.6. case 'member_status_id': case 'member_membership_type_id': if (is_array($value)) { $op = 'IN'; $value = array_keys($value); } case 'membership_type_id': // CRM-17075 we are specifically handling the possibility we are dealing with the entity reference field // for membership_type_id here (although status would be handled if converted). The unhandled pathway at the moment // is from groupContactCache::load and this is a small fix to get the entity reference field to work. // However, it would seem the larger fix would be to NOT invoke the form formValues for // the load function. The where clause and the whereTables are saved so they should suffice to generate the query // to get a contact list. But, better to deal with in 4.8 now... if (is_string($value) && strpos($value, ',') && $op == '=') { $value = array('IN' => explode(',', $value)); } case 'membership_status': case 'membership_status_id': case 'membership_type': case 'member_id': if (strstr($name, 'status') && is_string($value) && !is_numeric($value)) { $value = CRM_Core_PseudoConstant::getKey('CRM_Member_BAO_Membership', 'status_id', $value); } if (strpos($name, 'status') !== FALSE) { $name = 'status_id'; $qillName = 'Membership Status(s)'; } elseif ($name == 'member_id') { $name = 'id'; $qillName = 'Membership ID'; } else { $name = 'membership_type_id'; $qillName = 'Membership Type(s)'; } $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_membership.{$name}", $op, $value, "Integer"); list($op, $value) = CRM_Contact_BAO_Query::buildQillForFieldValue('CRM_Member_DAO_Membership', $name, $value, $op); $query->_qill[$grouping][] = ts('%1 %2 %3', array(1 => $qillName, 2 => $op, 3 => $value)); $query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1; return; case 'member_test': // We dont want to include all tests for sql OR CRM-7827 if (!$value || $query->getOperator() != 'OR') { $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_membership.is_test", $op, $value, "Boolean"); if ($value) { $query->_qill[$grouping][] = ts('Membership is a Test'); } } $query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1; return; case 'member_auto_renew': $op = "!="; if ($value) { $query->_where[$grouping][] = " civicrm_membership.contribution_recur_id IS NOT NULL"; $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("ccr.contribution_status_id", $op, array_search('Cancelled', CRM_Contribute_PseudoConstant::contributionStatus(NULL, 'name')), "Integer"); $query->_qill[$grouping][] = ts("Membership is Auto-Renew"); } else { $query->_where[$grouping][] = " civicrm_membership.contribution_recur_id IS NULL"; $query->_qill[$grouping][] = ts("Membership is NOT Auto-Renew"); } $query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1; return; case 'member_pay_later': $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_membership.is_pay_later", $op, $value, "Integer"); if ($value) { $query->_qill[$grouping][] = ts("Membership is Pay Later"); } else { $query->_qill[$grouping][] = ts("Membership is NOT Pay Later"); } $query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1; return; case 'member_is_primary': if ($value) { $query->_where[$grouping][] = " civicrm_membership.owner_membership_id IS NULL"; $query->_qill[$grouping][] = ts("Primary Members Only"); } else { $query->_where[$grouping][] = " civicrm_membership.owner_membership_id IS NOT NULL"; $query->_qill[$grouping][] = ts("Related Members Only"); } $query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1; return; case 'member_is_override': $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_membership.is_override", $op, $value, "Boolean"); $query->_qill[$grouping][] = $value ? ts("Is Membership Status overriden? Yes") : ts("Is Membership Status overriden? No"); $query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1; return; case 'member_campaign_id': if (CRM_Utils_Array::value($op, $value)) { $value = $value[$op]; } $campParams = array('op' => $op, 'campaign' => $value, 'grouping' => $grouping, 'tableName' => 'civicrm_membership'); CRM_Campaign_BAO_Query::componentSearchClause($campParams, $query); return; } }
/** * Where clause for a single field. * * @param array $values * @param CRM_Contact_BAO_Query $query */ public static function whereClauseSingle(&$values, &$query) { list($name, $op, $value, $grouping) = $values; $fields = CRM_Activity_BAO_Activity::exportableFields(); $query->_tables['civicrm_activity'] = $query->_whereTables['civicrm_activity'] = 1; if ($query->_mode & CRM_Contact_BAO_Query::MODE_ACTIVITY) { $query->_skipDeleteClause = TRUE; } switch ($name) { case 'activity_type_id': case 'activity_status_id': case 'activity_engagement_level': case 'activity_subject': case 'activity_id': case 'activity_campaign_id': $qillName = $name; if (in_array($name, array('activity_engagement_level', 'activity_id'))) { $name = $qillName = str_replace('activity_', '', $name); } if (in_array($name, array('activity_status_id', 'activity_subject'))) { $name = str_replace('activity_', '', $name); $qillName = str_replace('_id', '', $qillName); } if ($name == 'activity_campaign_id') { $name = 'campaign_id'; } $dataType = !empty($fields[$qillName]['type']) ? CRM_Utils_Type::typeToString($fields[$qillName]['type']) : 'String'; $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_activity.{$name}", $op, $value, $dataType); list($op, $value) = CRM_Contact_BAO_Query::buildQillForFieldValue('CRM_Activity_DAO_Activity', $name, $value, $op); $query->_qill[$grouping][] = ts('%1 %2 %3', array(1 => $fields[$qillName]['title'], 2 => $op, 3 => $value)); break; case 'activity_type': case 'activity_status': $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("{$name}.label", $op, $value, 'String'); list($op, $value) = CRM_Contact_BAO_Query::buildQillForFieldValue('CRM_Activity_DAO_Activity', $name, $value, $op); $query->_qill[$grouping][] = ts('%1 %2 %3', array(1 => $fields[$name]['title'], 2 => $op, 3 => $value)); $query->_tables[$name] = $query->_whereTables[$name] = 1; break; case 'activity_survey_id': if (!$value) { break; } $value = CRM_Utils_Type::escape($value, 'Integer'); $query->_where[$grouping][] = " civicrm_activity.source_record_id = {$value}"; $query->_qill[$grouping][] = ts('Survey') . ' - ' . CRM_Core_DAO::getFieldValue('CRM_Campaign_DAO_Survey', $value, 'title'); break; case 'activity_role': CRM_Contact_BAO_Query::$_activityRole = $values[2]; $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); if ($values[2]) { $query->_tables['civicrm_activity_contact'] = $query->_whereTables['civicrm_activity_contact'] = 1; if ($values[2] == 1) { $query->_where[$grouping][] = " civicrm_activity_contact.record_type_id = {$sourceID}"; $query->_qill[$grouping][] = ts('Activity created by'); } elseif ($values[2] == 2) { $query->_where[$grouping][] = " civicrm_activity_contact.record_type_id = {$assigneeID}"; $query->_qill[$grouping][] = ts('Activity assigned to'); } elseif ($values[2] == 3) { $query->_where[$grouping][] = " civicrm_activity_contact.record_type_id = {$targetID}"; $query->_qill[$grouping][] = ts('Activity targeted to'); } } break; case 'activity_test': // We don't want to include all tests for sql OR CRM-7827 if (!$value || $query->getOperator() != 'OR') { $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_activity.is_test", $op, $value, "Boolean"); if ($value) { $query->_qill[$grouping][] = ts('Activity is a Test'); } } break; case 'activity_date': case 'activity_date_low': case 'activity_date_high': $query->dateQueryBuilder($values, 'civicrm_activity', 'activity_date', 'activity_date_time', ts('Activity Date')); break; case 'activity_taglist': $taglist = $value; $value = array(); foreach ($taglist as $val) { if ($val) { $val = explode(',', $val); foreach ($val as $tId) { if (is_numeric($tId)) { $value[$tId] = 1; } } } } case 'activity_tags': $value = array_keys($value); $activityTags = CRM_Core_PseudoConstant::get('CRM_Core_DAO_EntityTag', 'tag_id', array('onlyActive' => FALSE)); $names = array(); if (is_array($value)) { foreach ($value as $k => $v) { $names[] = $activityTags[$v]; } } $query->_where[$grouping][] = "civicrm_activity_tag.tag_id IN (" . implode(",", $value) . ")"; $query->_qill[$grouping][] = ts('Activity Tag %1', array(1 => $op)) . ' ' . implode(' ' . ts('OR') . ' ', $names); $query->_tables['civicrm_activity_tag'] = $query->_whereTables['civicrm_activity_tag'] = 1; break; case 'activity_result': if (is_array($value)) { $safe = NULL; while (list(, $k) = each($value)) { $safe[] = "'" . CRM_Utils_Type::escape($k, 'String') . "'"; } $query->_where[$grouping][] = "civicrm_activity.result IN (" . implode(',', $safe) . ")"; $query->_qill[$grouping][] = ts("Activity Result - %1", array(1 => implode(' or ', $safe))); } break; case 'parent_id': if ($value == 1) { $query->_where[$grouping][] = "parent_id.parent_id IS NOT NULL"; $query->_qill[$grouping][] = ts('Activities which have Followup Activities'); } elseif ($value == 2) { $query->_where[$grouping][] = "parent_id.parent_id IS NULL"; $query->_qill[$grouping][] = ts('Activities without Followup Activities'); } break; case 'followup_parent_id': if ($value == 1) { $query->_where[$grouping][] = "civicrm_activity.parent_id IS NOT NULL"; $query->_qill[$grouping][] = ts('Activities which are Followup Activities'); } elseif ($value == 2) { $query->_where[$grouping][] = "civicrm_activity.parent_id IS NULL"; $query->_qill[$grouping][] = ts('Activities which are not Followup Activities'); } break; } }
/** * Generate where for a single parameter. * * @param array $values * @param CRM_Contact_BAO_Query $query */ public static function whereClauseSingle(&$values, &$query) { list($name, $op, $value, $grouping) = $values; switch ($name) { case 'member_join_date_low': case 'member_join_date_high': $query->dateQueryBuilder($values, 'civicrm_membership', 'member_join_date', 'join_date', 'Member Since'); return; case 'member_start_date_low': case 'member_start_date_high': $query->dateQueryBuilder($values, 'civicrm_membership', 'member_start_date', 'start_date', 'Start Date'); return; case 'member_end_date_low': case 'member_end_date_high': $query->dateQueryBuilder($values, 'civicrm_membership', 'member_end_date', 'end_date', 'End Date'); return; case 'member_join_date': $op = '>='; $date = CRM_Utils_Date::format($value); if ($date) { $query->_where[$grouping][] = "civicrm_membership.join_date {$op} {$date}"; $format = CRM_Utils_Date::customFormat(CRM_Utils_Date::format(array_reverse($value), '-')); $query->_qill[$grouping][] = ts('Member Since %2 %1', array(1 => $format, 2 => $op)); } return; case 'member_source': $strtolower = function_exists('mb_strtolower') ? 'mb_strtolower' : 'strtolower'; $value = $strtolower(CRM_Core_DAO::escapeString(trim($value))); $query->_where[$grouping][] = "civicrm_membership.source {$op} '{$value}'"; $query->_qill[$grouping][] = ts('Source %2 %1', array(1 => $value, 2 => $op)); $query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1; return; // CRM-17011 These 2 variants appear in some smart groups saved at some time prior to 4.6.6. // CRM-17011 These 2 variants appear in some smart groups saved at some time prior to 4.6.6. case 'member_status_id': case 'member_membership_type_id': if (is_array($value)) { $op = 'IN'; $value = array_keys($value); } case 'membership_status': case 'membership_status_id': case 'membership_type': case 'membership_type_id': case 'member_id': case 'member_campaign_id': if (strpos($name, 'status') !== FALSE) { $name = 'status_id'; $qillName = 'Membership Status(s)'; } elseif ($name == 'member_id') { $name = 'id'; $qillName = 'Membership ID'; } elseif ($name == 'member_campaign_id') { $name = 'campaign_id'; $qillName = 'Campaign'; } else { $name = 'membership_type_id'; $qillName = 'Membership Type(s)'; } $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_membership.{$name}", $op, $value, "Integer"); list($op, $value) = CRM_Contact_BAO_Query::buildQillForFieldValue('CRM_Member_DAO_Membership', $name, $value, $op); $query->_qill[$grouping][] = ts('%1 %2 %3', array(1 => $qillName, 2 => $op, 3 => $value)); $query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1; return; case 'member_test': // We dont want to include all tests for sql OR CRM-7827 if (!$value || $query->getOperator() != 'OR') { $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_membership.is_test", $op, $value, "Boolean"); if ($value) { $query->_qill[$grouping][] = ts('Membership is a Test'); } } $query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1; return; case 'member_auto_renew': $op = "!="; if ($value) { $query->_where[$grouping][] = " civicrm_membership.contribution_recur_id IS NOT NULL"; $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("ccr.contribution_status_id", $op, array_search('Cancelled', CRM_Contribute_PseudoConstant::contributionStatus(NULL, 'name')), "Integer"); $query->_qill[$grouping][] = ts("Membership is Auto-Renew"); } else { $query->_where[$grouping][] = " civicrm_membership.contribution_recur_id IS NULL"; $query->_qill[$grouping][] = ts("Membership is NOT Auto-Renew"); } $query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1; return; case 'member_pay_later': $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_membership.is_pay_later", $op, $value, "Integer"); if ($value) { $query->_qill[$grouping][] = ts("Membership is Pay Later"); } else { $query->_qill[$grouping][] = ts("Membership is NOT Pay Later"); } $query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1; return; case 'member_is_primary': if ($value) { $query->_where[$grouping][] = " civicrm_membership.owner_membership_id IS NULL"; $query->_qill[$grouping][] = ts("Primary Members Only"); } else { $query->_where[$grouping][] = " civicrm_membership.owner_membership_id IS NOT NULL"; $query->_qill[$grouping][] = ts("Related Members Only"); } $query->_tables['civicrm_membership'] = $query->_whereTables['civicrm_membership'] = 1; return; } }
/** * Where clause for a single field. * * CRM-17120 adds a test that checks the Qill on some of these parameters. * However, I couldn't find a way, other than via test, to access the * case_activity options in the code below and invalid sql was returned. * Perhaps the options are just legacy? * * Also, CRM-17120 locks in the Qill - but it probably is not quite right as I * see 'Activity Type = Scheduled' (rather than activity status). * * See CRM_Case_BAO_QueryTest for more. * * @param array $values * @param CRM_Contact_BAO_Query $query */ public static function whereClauseSingle(&$values, &$query) { list($name, $op, $value, $grouping, $wildcard) = $values; $val = $names = array(); switch ($name) { case 'case_type_id': case 'case_type': case 'case_status': case 'case_status_id': case 'case_id': if (strpos($name, 'type')) { $name = 'case_type_id'; $label = 'Case Type(s)'; } elseif (strpos($name, 'status')) { $name = 'status_id'; $label = 'Case Status(s)'; } else { $name = 'id'; $label = 'Case ID'; } $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_case.{$name}", $op, $value, "Integer"); list($op, $value) = CRM_Contact_BAO_Query::buildQillForFieldValue('CRM_Case_DAO_Case', $name, $value, $op); $query->_qill[$grouping][] = ts('%1 %2 %3', array(1 => $label, 2 => $op, 3 => $value)); $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1; return; case 'case_owner': case 'case_mycases': if (!empty($value)) { if ($value == 2) { $session = CRM_Core_Session::singleton(); $userID = $session->get('userID'); $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("case_relationship.contact_id_b", $op, $userID, 'Int'); $query->_qill[$grouping][] = ts('Case %1 My Cases', array(1 => $op)); $query->_tables['case_relationship'] = $query->_whereTables['case_relationship'] = 1; } elseif ($value == 1) { $query->_qill[$grouping][] = ts('Case %1 All Cases', array(1 => $op)); $query->_where[$grouping][] = "civicrm_case_contact.contact_id = contact_a.id"; } $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1; $query->_tables['civicrm_case_contact'] = $query->_whereTables['civicrm_case_contact'] = 1; } return; case 'case_deleted': $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_case.is_deleted", $op, $value, 'Boolean'); if ($value) { $query->_qill[$grouping][] = ts("Find Deleted Cases"); } $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1; return; case 'case_activity_subject': $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("case_activity.subject", $op, $value, 'String'); $query->_qill[$grouping][] = ts("Activity Subject %1 '%2'", array(1 => $op, 2 => $value)); $query->_tables['case_activity'] = $query->_whereTables['case_activity'] = 1; $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1; $query->_tables['civicrm_case_contact'] = $query->_whereTables['civicrm_case_contact'] = 1; return; case 'case_subject': $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_case.subject", $op, $value, 'String'); $query->_qill[$grouping][] = ts("Case Subject %1 '%2'", array(1 => $op, 2 => $value)); $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1; $query->_tables['civicrm_case_contact'] = $query->_whereTables['civicrm_case_contact'] = 1; return; case 'case_source_contact_id': $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_case_reporter.sort_name", $op, $value, 'String'); $query->_qill[$grouping][] = ts("Activity Reporter %1 '%2'", array(1 => $op, 2 => $value)); $query->_tables['case_activity'] = $query->_whereTables['case_activity'] = 1; $query->_tables['civicrm_case_reporter'] = $query->_whereTables['civicrm_case_reporter'] = 1; $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1; $query->_tables['civicrm_case_contact'] = $query->_whereTables['civicrm_case_contact'] = 1; return; case 'case_recent_activity_date': $date = CRM_Utils_Date::format($value); $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("case_activity.activity_date_time", $op, $date, 'Date'); if ($date) { $date = CRM_Utils_Date::customFormat($date); $query->_qill[$grouping][] = ts("Activity Actual Date %1 %2", array(1 => $op, 2 => $date)); } $query->_tables['case_activity'] = $query->_whereTables['case_activity'] = 1; $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1; $query->_tables['civicrm_case_contact'] = $query->_whereTables['civicrm_case_contact'] = 1; return; case 'case_scheduled_activity_date': $date = CRM_Utils_Date::format($value); $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("case_activity.activity_date_time", $op, $date, 'Date'); if ($date) { $date = CRM_Utils_Date::customFormat($date); $query->_qill[$grouping][] = ts("Activity Schedule Date %1 %2", array(1 => $op, 2 => $date)); } $query->_tables['case_activity'] = $query->_whereTables['case_activity'] = 1; $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1; $query->_tables['civicrm_case_contact'] = $query->_whereTables['civicrm_case_contact'] = 1; return; case 'case_recent_activity_type': $names = $value; if (($activityType = CRM_Core_PseudoConstant::getLabel('CRM_Activity_BAO_Activity', 'activity_type_id', $value)) != FALSE) { $names = $activityType; } $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("case_activity.activity_type_id", $op, $value, 'Int'); $query->_qill[$grouping][] = ts("Activity Type %1 %2", array(1 => $op, 2 => $names)); $query->_tables['case_activity'] = $query->_whereTables['case_activity'] = 1; $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1; $query->_tables['case_activity_type'] = 1; $query->_tables['civicrm_case_contact'] = $query->_whereTables['civicrm_case_contact'] = 1; return; case 'case_activity_status_id': $names = $value; if (($activityStatus = CRM_Core_PseudoConstant::getLabel('CRM_Activity_BAO_Activity', 'status_id', $value)) != FALSE) { $names = $activityStatus; } $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("case_activity.status_id", $op, $value, 'Int'); $query->_qill[$grouping][] = ts("Activity Type %1 %2", array(1 => $op, 2 => $names)); $query->_tables['case_activity'] = $query->_whereTables['case_activity'] = 1; $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1; $query->_tables['case_activity_status'] = 1; $query->_tables['civicrm_case_contact'] = $query->_whereTables['civicrm_case_contact'] = 1; return; case 'case_activity_duration': $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("case_activity.duration", $op, $value, 'Int'); $query->_qill[$grouping][] = ts("Activity Duration %1 %2", array(1 => $op, 2 => $value)); $query->_tables['case_activity'] = $query->_whereTables['case_activity'] = 1; $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1; $query->_tables['civicrm_case_contact'] = $query->_whereTables['civicrm_case_contact'] = 1; return; case 'case_activity_medium_id': $names = $value; if (($activityMedium = CRM_Core_PseudoConstant::getLabel('CRM_Activity_BAO_Activity', 'medium_id', $value)) != FALSE) { $names = $activityMedium; } $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("case_activity.medium_id", $op, $value, 'Int'); $query->_qill[$grouping][] = ts("Activity Medium %1 %2", array(1 => $op, 2 => $names)); $query->_tables['case_activity'] = $query->_whereTables['case_activity'] = 1; $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1; $query->_tables['case_activity_medium'] = 1; $query->_tables['civicrm_case_contact'] = $query->_whereTables['civicrm_case_contact'] = 1; return; case 'case_activity_details': $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("case_activity.details", $op, $value, 'String'); $query->_qill[$grouping][] = ts("Activity Details %1 '%2'", array(1 => $op, 2 => $value)); $query->_tables['case_activity'] = $query->_whereTables['case_activity'] = 1; $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1; $query->_tables['civicrm_case_contact'] = $query->_whereTables['civicrm_case_contact'] = 1; return; case 'case_activity_is_auto': $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("case_activity.is_auto", $op, $value, 'Boolean'); $query->_qill[$grouping][] = ts("Activity Auto Genrated %1 '%2'", array(1 => $op, 2 => $value)); $query->_tables['case_activity'] = $query->_whereTables['case_activity'] = 1; $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1; $query->_tables['civicrm_case_contact'] = $query->_whereTables['civicrm_case_contact'] = 1; return; // adding where clause for case_role // adding where clause for case_role case 'case_role': $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("case_relation_type.name_b_a", $op, $value, 'String'); $query->_qill[$grouping][] = ts("Role in Case %1 '%2'", array(1 => $op, 2 => $value)); $query->_tables['case_relation_type'] = $query->_whereTables['case_relationship_type'] = 1; $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1; $query->_tables['civicrm_case_contact'] = $query->_whereTables['civicrm_case_contact'] = 1; return; case 'case_from_start_date_low': case 'case_from_start_date_high': $query->dateQueryBuilder($values, 'civicrm_case', 'case_from_start_date', 'start_date', 'Start Date'); return; case 'case_to_end_date_low': case 'case_to_end_date_high': $query->dateQueryBuilder($values, 'civicrm_case', 'case_to_end_date', 'end_date', 'End Date'); return; case 'case_start_date': $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_case.start_date", $op, $value, 'Int'); $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1; return; case 'case_end_date': $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_case.end_date", $op, $value, 'Int'); $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1; return; case 'case_taglist': $taglist = $value; $value = array(); foreach ($taglist as $val) { if ($val) { $val = explode(',', $val); foreach ($val as $tId) { if (is_numeric($tId)) { $value[$tId] = 1; } } } } case 'case_tags': $tags = CRM_Core_PseudoConstant::get('CRM_Core_DAO_EntityTag', 'tag_id', array('onlyActive' => FALSE)); if (is_array($value)) { foreach ($value as $k => $v) { if ($v) { $val[$k] = $k; $names[] = $tags[$k]; } } } $query->_where[$grouping][] = " civicrm_case_tag.tag_id IN (" . implode(',', $val) . " )"; $query->_qill[$grouping][] = ts('Case Tags %1', array(1 => $op)) . ' ' . implode(' ' . ts('or') . ' ', $names); $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1; $query->_tables['civicrm_case_contact'] = $query->_whereTables['civicrm_case_contact'] = 1; $query->_tables['civicrm_case_tag'] = $query->_whereTables['civicrm_case_tag'] = 1; return; } }