/**
* @param int $batchId
*
* @return Object
*/
public function generateExportQuery($batchId)
{
$sql = "SELECT\n ft.id as financial_trxn_id,\n ft.trxn_date,\n fa_to.accounting_code AS to_account_code,\n fa_to.name AS to_account_name,\n fa_to.account_type_code AS to_account_type_code,\n ft.total_amount AS debit_total_amount,\n ft.trxn_id AS trxn_id,\n cov.label AS payment_instrument,\n ft.check_number,\n c.source AS source,\n ft.currency AS currency,\n cov_status.label AS status,\n CASE\n WHEN efti.entity_id IS NOT NULL\n THEN efti.amount\n ELSE eftc.amount\n END AS amount,\n fa_from.account_type_code AS credit_account_type_code,\n fa_from.accounting_code AS credit_account,\n fa_from.name AS credit_account_name,\n fac.account_type_code AS from_credit_account_type_code,\n fac.accounting_code AS from_credit_account,\n fac.name AS from_credit_account_name,\n fi.description AS item_description\n FROM civicrm_entity_batch eb\n LEFT JOIN civicrm_financial_trxn ft ON (eb.entity_id = ft.id AND eb.entity_table = 'civicrm_financial_trxn')\n LEFT JOIN civicrm_financial_account fa_to ON fa_to.id = ft.to_financial_account_id\n LEFT JOIN civicrm_financial_account fa_from ON fa_from.id = ft.from_financial_account_id\n LEFT JOIN civicrm_option_group cog ON cog.name = 'payment_instrument'\n LEFT JOIN civicrm_option_value cov ON (cov.value = ft.payment_instrument_id AND cov.option_group_id = cog.id)\n LEFT JOIN civicrm_entity_financial_trxn eftc ON (eftc.financial_trxn_id = ft.id AND eftc.entity_table = 'civicrm_contribution')\n LEFT JOIN civicrm_contribution c ON c.id = eftc.entity_id\n LEFT JOIN civicrm_option_group cog_status ON cog_status.name = 'contribution_status'\n LEFT JOIN civicrm_option_value cov_status ON (cov_status.value = ft.status_id AND cov_status.option_group_id = cog_status.id)\n LEFT JOIN civicrm_entity_financial_trxn efti ON (efti.financial_trxn_id = ft.id AND efti.entity_table = 'civicrm_financial_item')\n LEFT JOIN civicrm_financial_item fi ON fi.id = efti.entity_id\n LEFT JOIN civicrm_financial_account fac ON fac.id = fi.financial_account_id\n LEFT JOIN civicrm_financial_account fa ON fa.id = fi.financial_account_id\n WHERE eb.batch_id = ( %1 )";
$params = array(1 => array($batchId, 'String'));
$dao = CRM_Core_DAO::executeQuery($sql, $params);
return $dao;
}
/**
* Example: Run an external SQL script when the module is uninstalled
*
public function uninstall() {
$this->executeSqlFile('sql/myuninstall.sql');
}
/**
* Example: Run a simple query when a module is enabled
*
public function enable() {
CRM_Core_DAO::executeQuery('UPDATE foo SET is_active = 1 WHERE bar = "whiz"');
}
/**
* Example: Run a simple query when a module is disabled
*
public function disable() {
CRM_Core_DAO::executeQuery('UPDATE foo SET is_active = 0 WHERE bar = "whiz"');
}
/**
* Example: Add start and and date for job roles
*
* @return TRUE on success
* @throws Exception
*
*/
public function upgrade_1001()
{
$this->ctx->log->info('Applying update for job role start and end dates');
CRM_Core_DAO::executeQuery("ALTER TABLE `civicrm_hrjobroles` ADD COLUMN `start_date` timestamp DEFAULT 0 COMMENT 'Start Date of the job role'");
CRM_Core_DAO::executeQuery("ALTER TABLE `civicrm_hrjobroles` ADD COLUMN `end_date` timestamp DEFAULT 0 COMMENT 'End Date of the job role'");
return TRUE;
}
function __construct()
{
$this->_columns = array('civicrm_contact' => array('dao' => 'CRM_Contact_DAO_Contact', 'fields' => array('sort_name' => array('title' => ts('Contact Name'), 'required' => TRUE, 'no_repeat' => TRUE), 'gender_id' => array('title' => ts('Gender'), 'default' => TRUE), 'birth_date' => array('title' => ts('Birthdate'), 'default' => FALSE), 'id' => array('no_display' => TRUE, 'required' => TRUE)), 'filters' => array('sort_name' => array('title' => ts('Contact Name'), 'operatorType' => CRM_Report_Form::OP_STRING), 'contact_type' => array('title' => ts('Contact Type'), 'operatorType' => CRM_Report_Form::OP_SELECT, 'options' => array('' => ts('-select-'), 'Individual' => ts('Individual'), 'Organization' => ts('Organization'), 'Household' => ts('Household')), 'default' => 'Individual'), 'id' => array('title' => ts('Contact ID'), 'no_display' => TRUE)), 'grouping' => 'contact-fields'), 'civicrm_email' => array('dao' => 'CRM_Core_DAO_Email', 'fields' => array('email' => array('title' => ts('Email'), 'no_repeat' => TRUE)), 'grouping' => 'contact-fields'), 'civicrm_address' => array('dao' => 'CRM_Core_DAO_Address', 'grouping' => 'contact-fields', 'fields' => array('street_address' => array('default' => FALSE), 'city' => array('default' => TRUE), 'postal_code' => NULL, 'state_province_id' => array('title' => ts('State/Province')), 'country_id' => array('title' => ts('Country'), 'default' => FALSE))), 'civicrm_phone' => array('dao' => 'CRM_Core_DAO_Phone', 'fields' => array('phone' => NULL), 'grouping' => 'contact-fields'), 'civicrm_activity' => array('dao' => 'CRM_Activity_DAO_Activity', 'fields' => array('id' => array('title' => ts('Activity ID'), 'no_display' => TRUE, 'required' => TRUE))), 'civicrm_case' => array('dao' => 'CRM_Case_DAO_Case', 'fields' => array('id' => array('title' => ts('Case ID'), 'required' => TRUE), 'start_date' => array('title' => ts('Case Start'), 'required' => TRUE), 'end_date' => array('title' => ts('Case End'), 'required' => TRUE)), 'filters' => array('case_id_filter' => array('name' => 'id', 'title' => ts('Cases?'), 'operatorType' => CRM_Report_Form::OP_SELECT, 'options' => array(1 => ts('Exclude non-case'), 2 => ts('Exclude cases'), 3 => ts('Include Both')), 'default' => 3), 'start_date' => array('title' => ts('Case Start'), 'operatorType' => CRM_Report_Form::OP_DATE), 'end_date' => array('title' => ts('Case End'), 'operatorType' => CRM_Report_Form::OP_DATE))), 'civicrm_group' => array('dao' => 'CRM_Contact_DAO_Group', 'alias' => 'cgroup', 'filters' => array('gid' => array('name' => 'group_id', 'title' => ts('Group'), 'operatorType' => CRM_Report_Form::OP_MULTISELECT, 'group' => TRUE, 'options' => CRM_Core_PseudoConstant::group()))));
$this->_tagFilter = TRUE;
$open_case_val = CRM_Core_OptionGroup::getValue('activity_type', 'Open Case', 'name');
$crmDAO =& CRM_Core_DAO::executeQuery("SELECT cg.table_name, cg.extends AS ext, cf.label, cf.column_name FROM civicrm_custom_group cg INNER JOIN civicrm_custom_field cf ON cg.id = cf.custom_group_id\nwhere (cg.extends='Contact' OR cg.extends='Individual' OR cg.extends_entity_column_value='{$open_case_val}') AND cg.is_active=1 AND cf.is_active=1 ORDER BY cg.table_name");
$curTable = '';
$curExt = '';
$curFields = array();
while ($crmDAO->fetch()) {
if ($curTable == '') {
$curTable = $crmDAO->table_name;
$curExt = $crmDAO->ext;
} elseif ($curTable != $crmDAO->table_name) {
// dummy DAO
$this->_columns[$curTable] = array('dao' => 'CRM_Contact_DAO_Contact', 'fields' => $curFields, 'ext' => $curExt);
$curTable = $crmDAO->table_name;
$curExt = $crmDAO->ext;
$curFields = array();
}
$curFields[$crmDAO->column_name] = array('title' => $crmDAO->label);
}
if (!empty($curFields)) {
// dummy DAO
$this->_columns[$curTable] = array('dao' => 'CRM_Contact_DAO_Contact', 'fields' => $curFields, 'ext' => $curExt);
}
$this->_genders = CRM_Core_PseudoConstant::get('CRM_Contact_DAO_Contact', 'gender_id');
parent::__construct();
}
function upgrade()
{
$currentDir = dirname(__FILE__);
$sqlFile = implode(DIRECTORY_SEPARATOR, array($currentDir, '../sql', 'misc.mysql'));
$this->source($sqlFile);
// CRM-3052, dropping location_id from group_contact
if (CRM_Core_DAO::checkFieldExists('civicrm_group_contact', 'location_id')) {
$query = "ALTER TABLE `civicrm_group_contact` \n DROP FOREIGN KEY `FK_civicrm_group_contact_location_id`,\n DROP location_id";
CRM_Core_DAO::executeQuery($query, CRM_Core_DAO::$_nullArray);
}
// CRM-3625, profile group_type upgrade
$query = "SELECT id FROM civicrm_uf_group";
$ufGroup = CRM_Core_DAO::executeQuery($query, CRM_Core_DAO::$_nullArray);
while ($ufGroup->fetch()) {
$query = "SELECT distinct `field_type` FROM `civicrm_uf_field` WHERE uf_group_id = %1";
$params = array(1 => array($ufGroup->id, 'Integer'));
$fieldType = CRM_Core_DAO::executeQuery($query, $params);
$types = array();
while ($fieldType->fetch()) {
$types[] = $fieldType->field_type;
}
if (count($types) >= 1) {
$query = "UPDATE `civicrm_uf_group` SET group_type = %1 WHERE id = %2";
$params = array(1 => array(implode(',', $types), 'String'), 2 => array($ufGroup->id, 'Integer'));
CRM_Core_DAO::executeQuery($query, $params);
}
}
$this->setVersion('2.03');
}
function run()
{
$sql = "SELECT * FROM civicrm_metrics_server ORDER BY site_name, timestamp";
$dao =& CRM_Core_DAO::executeQuery($sql);
$rows = array();
while ($dao->fetch()) {
$row = array();
$row['id'] = $dao->id;
$row['site_name'] = $dao->site_name;
$row['site_url'] = $dao->site_url;
$row['timestamp'] = $dao->timestamp;
$row['type'] = $dao->type;
$row['data'] = $dao->data;
$rows[] = $row;
}
if (array_key_exists("export", $_REQUEST) && $_REQUEST['export'] == 'csv') {
header('Content-type: text/csv');
header('Content-disposition: attachment;filename=metrics_data_' . date("Ymd_HiO") . '.csv');
$output = fopen('php://output', 'w');
$headers = array("Id", "Site Name", "Site URL", "Timestamp", "Metric Type", "Metric Data");
fputcsv($output, $headers);
foreach ($rows as $row) {
fputcsv($output, $row);
}
fclose($output);
die;
} else {
CRM_Utils_System::setTitle(ts('Metrics Report'));
$this->assign('data', $rows);
$this->assign('headers', array_keys($rows[0]));
parent::run();
}
}
function upgrade()
{
$currentDir = dirname(__FILE__);
// 1. remove domain_ids from the entire db
$sqlFile = implode(DIRECTORY_SEPARATOR, array($currentDir, '../sql', 'group_values.mysql'));
$this->source($sqlFile);
// 2. Add option group "safe_file_extension" and its option
// values to db, if not already present. CRM-3238
$query = "\nSELECT id FROM civicrm_option_group WHERE name = 'safe_file_extension'";
$sfeGroup = CRM_Core_DAO::executeQuery($query, CRM_Core_DAO::$_nullArray);
$sfeGroup->fetch();
if (!isset($sfeGroup->id)) {
$query = "\nINSERT INTO civicrm_option_group (name, description, is_reserved, is_active)\nVALUES ('safe_file_extension', 'Safe File Extension', 0, 1)";
$dao = CRM_Core_DAO::executeQuery($query, CRM_Core_DAO::$_nullArray);
$query = "\nSELECT id FROM civicrm_option_group WHERE name = 'safe_file_extension'";
$dao = CRM_Core_DAO::executeQuery($query, CRM_Core_DAO::$_nullArray);
$dao->fetch();
if ($dao->id) {
$query = "\nINSERT INTO `civicrm_option_value` (`option_group_id`, `label`, `value`, `name`, `grouping`, `filter`, `is_default`, `weight`, `description`, `is_optgroup`, `is_reserved`, `is_active`, `component_id`) \nVALUES \n( %1, 'jpg', 1, NULL, NULL, 0, 0, 1, NULL, 0, 0, 1, NULL),\n( %1, 'jpeg', 2, NULL, NULL, 0, 0, 2, NULL, 0, 0, 1, NULL),\n( %1, 'png', 3, NULL, NULL, 0, 0, 3, NULL, 0, 0, 1, NULL),\n( %1, 'gif', 4, NULL, NULL, 0, 0, 4, NULL, 0, 0, 1, NULL),\n( %1, 'txt', 5, NULL, NULL, 0, 0, 5, NULL, 0, 0, 1, NULL),\n( %1, 'pdf', 6, NULL, NULL, 0, 0, 6, NULL, 0, 0, 1, NULL),\n( %1, 'doc', 7, NULL, NULL, 0, 0, 7, NULL, 0, 0, 1, NULL),\n( %1, 'xls', 8, NULL, NULL, 0, 0, 8, NULL, 0, 0, 1, NULL),\n( %1, 'rtf', 9, NULL, NULL, 0, 0, 9, NULL, 0, 0, 1, NULL),\n( %1, 'csv', 10, NULL, NULL, 0, 0, 10, NULL, 0, 0, 1, NULL),\n( %1, 'ppt', 11, NULL, NULL, 0, 0, 11, NULL, 0, 0, 1, NULL)\n";
$params = array(1 => array($dao->id, 'Integer'));
$dao = CRM_Core_DAO::executeQuery($query, $params);
}
} else {
//fix for CRM-3252
//delete records from db.
$query = "\nDELETE \nFROM `civicrm_option_value` \nWHERE `option_group_id` = %1\nAND `label` IN ('html', 'htm')\n";
$params = array(1 => array($sfeGroup->id, 'Integer'));
$dao = CRM_Core_DAO::executeQuery($query, $params);
}
$this->setVersion('2.02');
}
function tearDown()
{
$this->quickCleanup(array('civicrm_contact', 'civicrm_phone', 'civicrm_address', 'civicrm_membership', 'civicrm_contribution'), TRUE);
$this->callAPISuccess('membership_type', 'delete', array('id' => $this->_membershipTypeID));
// ok can't be bothered wring an api to do this & truncating is crazy
CRM_Core_DAO::executeQuery(" DELETE FROM civicrm_uf_group WHERE id IN ({$this->_profileID}, 26)");
}
public static function update()
{
// lets build the tree in memory first
$sql = "\nSELECT n.child_group_id as child ,\n n.parent_group_id as parent\nFROM civicrm_group_nesting n,\n civicrm_group gc,\n civicrm_group gp\nWHERE n.child_group_id = gc.id\n AND n.parent_group_id = gp.id\n";
$dao = CRM_Core_DAO::executeQuery($sql);
$tree = array();
while ($dao->fetch()) {
if (!array_key_exists($dao->child, $tree)) {
$tree[$dao->child] = array('children' => array(), 'parents' => array());
}
if (!array_key_exists($dao->parent, $tree)) {
$tree[$dao->parent] = array('children' => array(), 'parents' => array());
}
$tree[$dao->child]['parents'][] = $dao->parent;
$tree[$dao->parent]['children'][] = $dao->child;
}
if (self::checkCyclicGraph($tree)) {
CRM_Core_Error::fatal(ts('We detected a cycle which we cant handle. aborting'));
}
// first reset the current cache entries
$sql = "\nUPDATE civicrm_group\nSET parents = null,\n children = null\n";
CRM_Core_DAO::executeQuery($sql);
$values = array();
foreach (array_keys($tree) as $id) {
$parents = implode(',', $tree[$id]['parents']);
$children = implode(',', $tree[$id]['children']);
$parents = $parents == NULL ? 'null' : "'{$parents}'";
$children = $children == NULL ? 'null' : "'{$children}'";
$sql = "\nUPDATE civicrm_group\nSET parents = {$parents} ,\n children = {$children}\nWHERE id = {$id}\n";
CRM_Core_DAO::executeQuery($sql);
}
// this tree stuff is quite useful, so lets store it in the cache
CRM_Core_BAO_Cache::setItem($tree, 'contact groups', 'nestable tree hierarchy');
}
/**
* @param int $compID
* @param null $grouping
*
* @return array
*/
public static function &info($compID = NULL, $grouping = NULL)
{
$all = CRM_Utils_Request::retrieve('all', 'Boolean', CRM_Core_DAO::$_nullObject, FALSE, NULL, 'GET');
$compClause = '';
if ($compID) {
if ($compID == 99) {
$compClause = " AND v.component_id IS NULL ";
} else {
$compClause = " AND v.component_id = {$compID} ";
}
} elseif ($grouping) {
$compClause = " AND v.grouping = '{$grouping}' ";
}
$sql = "\nSELECT v.id, v.value, v.label, v.description, v.component_id,\n CASE\n WHEN comp.name IS NOT NULL THEN SUBSTRING(comp.name, 5)\n WHEN v.grouping IS NOT NULL THEN v.grouping\n ELSE 'Contact'\n END as component_name,\n v.grouping,\n inst.id as instance_id\nFROM civicrm_option_value v\nINNER JOIN civicrm_option_group g\n ON (v.option_group_id = g.id AND g.name = 'report_template')\nLEFT JOIN civicrm_report_instance inst\n ON v.value = inst.report_id\nLEFT JOIN civicrm_component comp\n ON v.component_id = comp.id\n";
if (!$all) {
$sql .= " WHERE v.is_active = 1 {$compClause}";
}
$sql .= " ORDER BY v.weight ";
$dao = CRM_Core_DAO::executeQuery($sql);
$rows = array();
$config = CRM_Core_Config::singleton();
while ($dao->fetch()) {
if ($dao->component_name != 'Contact' && $dao->component_name != $dao->grouping && !in_array("Civi{$dao->component_name}", $config->enableComponents)) {
continue;
}
$rows[$dao->component_name][$dao->value]['title'] = ts($dao->label);
$rows[$dao->component_name][$dao->value]['description'] = ts($dao->description);
$rows[$dao->component_name][$dao->value]['url'] = CRM_Utils_System::url('civicrm/report/' . trim($dao->value, '/'), 'reset=1');
if ($dao->instance_id) {
$rows[$dao->component_name][$dao->value]['instanceUrl'] = CRM_Utils_System::url('civicrm/report/list', "reset=1&ovid={$dao->id}");
}
}
return $rows;
}
static function get_participant_sessions($main_event_participant_id)
{
$sql = <<<EOS
SELECT sub_event.* FROM civicrm_participant main_participant
JOIN civicrm_event sub_event ON sub_event.parent_event_id = main_participant.event_id
JOIN civicrm_participant sub_participant ON sub_participant.event_id = sub_event.id
LEFT JOIN
civicrm_option_value slot ON sub_event.slot_label_id = slot.value
LEFT JOIN
civicrm_option_group og ON slot.option_group_id = og.id
WHERE
main_participant.id = %1
AND sub_participant.contact_id = main_participant.contact_id
AND og.name = 'conference_slot'
ORDER BY
slot.weight,
sub_event.start_date
EOS;
$sql_args = array(1 => array($main_event_participant_id, 'Integer'));
$dao = CRM_Core_DAO::executeQuery($sql, $sql_args);
$smarty_sessions = array();
while ($dao->fetch()) {
$smarty_sessions[] = get_object_vars($dao);
}
if (empty($smarty_sessions)) {
return NULL;
}
return $smarty_sessions;
}
/**
* Add the membership Payments.
*
* @param array $params
* Reference array contains the values submitted by the form.
*
*
* @return object
*/
public static function create($params)
{
$hook = empty($params['id']) ? 'create' : 'edit';
CRM_Utils_Hook::pre($hook, 'MembershipPayment', CRM_Utils_Array::value('id', $params), $params);
$dao = new CRM_Member_DAO_MembershipPayment();
$dao->copyValues($params);
$dao->id = CRM_Utils_Array::value('id', $params);
//Fixed for avoiding duplicate entry error when user goes
//back and forward during payment mode is notify
if (!$dao->find(TRUE)) {
$dao->save();
}
CRM_Utils_Hook::post($hook, 'MembershipPayment', $dao->id, $dao);
// CRM-14197 we are in the process on phasing out membershipPayment in favour of storing both contribution_id & entity_id (membership_id) on the line items
// table. However, at this stage we have both - there is still quite a bit of refactoring to do to set the line_iten entity_id right the first time
// however, we can assume at this stage that any contribution id will have only one line item with that membership type in the line item table
// OR the caller will have taken responsibility for updating the line items themselves so we will update using SQL here
if (!isset($params['membership_type_id'])) {
$membership_type_id = civicrm_api3('membership', 'getvalue', array('id' => $dao->membership_id, 'return' => 'membership_type_id'));
} else {
$membership_type_id = $params['membership_type_id'];
}
$sql = "UPDATE civicrm_line_item li\n LEFT JOIN civicrm_price_field_value pv ON pv.id = li.price_field_value_id\n SET entity_table = 'civicrm_membership', entity_id = %1\n WHERE pv.membership_type_id = %2\n AND contribution_id = %3";
CRM_Core_DAO::executeQuery($sql, array(1 => array($dao->membership_id, 'Integer'), 2 => array($membership_type_id, 'Integer'), 3 => array($dao->contribution_id, 'Integer')));
return $dao;
}
static function postProcessStudent($pickupName, $studentID, $atSchoolMeeting = false)
{
static $_now = null;
static $_date = null;
if (!$_now) {
$_now = CRM_Utils_Date::getToday(null, 'YmdHis');
}
if (!$_date) {
$_date = CRM_Utils_Date::getToday(null, 'Y-m-d');
}
$atSchoolMeeting = $atSchoolMeeting ? '1' : '0';
$sql = "\nSELECT e.id, e.class\nFROM civicrm_value_extended_care_signout e\nWHERE entity_id = %1\nAND DATE(signin_time) = %2\nAND ( is_morning = 0 OR is_morning IS NULL )\n";
$params = array(1 => array($studentID, 'Integer'), 2 => array($_date, 'String'));
$dao = CRM_Core_DAO::executeQuery($sql, $params);
$params = array(1 => array($studentID, 'Integer'), 2 => array($pickupName, 'String'), 3 => array($_now, 'Timestamp'), 4 => array($atSchoolMeeting, 'Integer'));
$class = null;
if ($dao->fetch()) {
$class = $dao->class;
$sql = "\nUPDATE civicrm_value_extended_care_signout\nSET pickup_person_name = %2,\n signout_time = %3,\n at_school_meeting = %4\nWHERE id = %5\n";
$params[5] = array($dao->id, 'Integer');
} else {
$sql = "\nINSERT INTO civicrm_value_extended_care_signout\n( entity_id, pickup_person_name, signout_time, at_school_meeting, is_morning )\nVALUES\n( %1, %2, %3, %4, 0 )\n";
}
CRM_Core_DAO::executeQuery($sql, $params);
return $class;
}
public function isConditionValid(CRM_Civirules_EventData_EventData $eventData)
{
$contactId = $eventData->getContactId();
$entityData = $eventData->getEntityData('ContributionSoft');
$contacts = array();
$query = $this->conditionParams['smart_group_query'];
if (!empty($query)) {
$subQueries = explode(';', $query);
// To run only first and select statement in query string
if (!empty($subQueries) && !preg_match('/^(insert|update|delete|create|drop|replace)/i', $subQueries[0])) {
CRM_Core_Error::debug_var('CiviRules::Custom Query Contact Condition Query', $subQueries[0]);
CRM_Core_Error::debug_var('CiviRules::Custom Query Contact Condition Param Contribution', $entityData['contribution_id']);
$dao = CRM_Core_DAO::executeQuery('SELECT ' . $subQueries[0], array(1 => array($entityData['contribution_id'], 'Int')));
while ($dao->fetch()) {
$contacts[] = $dao->contact_id;
}
}
}
CRM_Core_Error::debug_var('CiviRules::Custom Query Contact Condition Contacts', $contacts);
if (!empty($contacts)) {
$eventData->setConditionOutputData('ContributionSoft', $contacts);
return TRUE;
} else {
return FALSE;
}
}
/**
* Function for Case Subject combo box
*/
function caseSubject(&$config)
{
require_once 'CRM/Utils/Type.php';
$whereclause = $caseIdClause = null;
if (isset($_GET['name'])) {
$name = CRM_Utils_Type::escape($_GET['name'], 'String');
$name = str_replace('*', '%', $name);
$whereclause = "civicrm_case.subject LIKE '%{$name}'";
}
if (isset($_GET['id'])) {
$caseId = CRM_Utils_Type::escape($_GET['id'], 'Integer');
$caseIdClause = " AND civicrm_case.id = {$caseId}";
}
$elements = array();
if ($name || $caseIdClause) {
if (is_numeric($_GET['c'])) {
$contactID = CRM_Utils_Type::escape($_GET['c'], 'Integer');
if ($contactID) {
$clause = "civicrm_case_contact.contact_id = {$contactID}";
$whereclause = $whereclause ? $whereclause . " AND " . $clause : $clause;
}
}
$query = "\nSELECT distinct(civicrm_case.subject) as subject, civicrm_case.id as id\nFROM civicrm_case\nLEFT JOIN civicrm_case_contact ON civicrm_case_contact.case_id = civicrm_case.id\nWHERE {$whereclause} {$caseIdClause}\nORDER BY subject";
$dao = CRM_Core_DAO::executeQuery($query);
while ($dao->fetch()) {
$elements[] = array('name' => $dao->subject, 'id' => $dao->id);
}
}
if (empty($elements)) {
$name = str_replace('%', '', $name);
$elements[] = array('name' => $name, 'id' => $name);
}
require_once "CRM/Utils/JSON.php";
echo CRM_Utils_JSON::encode($elements);
}
public function postProcess()
{
$params = $this->_submitValues;
$batchDetailsSql = " UPDATE civicrm_batch SET ";
$batchDetailsSql .= " title = %1 ";
$batchDetailsSql .= " , description = %2 ";
$batchDetailsSql .= " , banking_account = %3 ";
$batchDetailsSql .= " , banking_date = %4 ";
$batchDetailsSql .= " , exclude_from_posting = %5 ";
$batchDetailsSql .= " , contribution_type_id = %6 ";
$batchDetailsSql .= " , payment_instrument_id = %7 ";
$batchDetailsSql .= " WHERE id = %8 ";
$bankingDate = CRM_Utils_Date::processDate($params['banking_date']);
$sqlParams = array();
$sqlParams[1] = array((string) $params['batch_title'], 'String');
$sqlParams[2] = array((string) $params['description'], 'String');
$sqlParams[3] = array((string) $params['banking_account'], 'String');
$sqlParams[4] = array((string) $bankingDate, 'String');
$sqlParams[5] = array((string) $params['exclude_from_posting'], 'String');
$sqlParams[6] = array((string) $params['contribution_type_id'], 'String');
$sqlParams[7] = array((string) $params['payment_instrument_id'], 'String');
$sqlParams[8] = array((int) $params['id'], 'Integer');
CRM_Core_DAO::executeQuery($batchDetailsSql, $sqlParams);
drupal_goto('civicrm/batch/process', array('query' => array('bid' => $params['id'], 'reset' => '1')));
CRM_Utils_System::civiExit();
}
public function upgrade_4200()
{
$this->ctx->log->info('Applying update 4200');
CRM_Core_DAO::executeQuery('UPDATE civicrm_payment_processor_type SET class_name = "Payment_Ewayrecurring" WHERE class_name = "com.chrischinchilla.ewayrecurring"');
CRM_Core_DAO::executeQuery('UPDATE civicrm_payment_processor SET class_name = "Payment_Ewayrecurring" WHERE class_name = "com.chrischinchilla.ewayrecurring"');
return TRUE;
}
public function tearDown()
{
parent::tearDown();
$this->system = NULL;
$this->quickCleanup(array('civicrm_payment_processor'));
CRM_Core_DAO::executeQuery('DELETE FROM civicrm_payment_processor_type WHERE class_name = "test.extension.manager.paymenttest"');
}
static function updateSettingsWithoutDAO($params)
{
$sql = "INSERT into civicrm_training_settings (name, value) VALUES (%1, %2), (%3, %4) ON DUPLICATE KEY UPDATE value = VALUES(value)";
$sparams = array(1 => array('display_membership', 'String'), 2 => array((int) CRM_Utils_Array::value('display_membership', $params), 'Integer'), 3 => array('display_contribution_total', 'String'), 4 => array((int) CRM_Utils_Array::value('display_contribution_total', $params), 'Integer'));
CRM_Core_DAO::executeQuery($sql, $sparams);
return true;
}
public static function &info($ovID = null, &$title = null)
{
$report = '';
if ($ovID) {
$report = " AND v.id = {$ovID} ";
}
$sql = "\n SELECT inst.id, inst.title, inst.report_id, inst.description, v.label, \n ifnull( SUBSTRING(comp.name, 5), 'Contact' ) as compName\n FROM civicrm_option_group g\n LEFT JOIN civicrm_option_value v\n ON v.option_group_id = g.id AND\n g.name = 'report_template'\n LEFT JOIN civicrm_report_instance inst\n ON v.value = inst.report_id\n LEFT JOIN civicrm_component comp \n ON v.component_id = comp.id\n \n WHERE v.is_active = 1 {$report}\n\n ORDER BY v.weight\n ";
$dao = CRM_Core_DAO::executeQuery($sql);
$config = CRM_Core_Config::singleton();
$rows = array();
$url = 'civicrm/report/instance';
while ($dao->fetch()) {
$enabled = in_array("Civi{$dao->compName}", $config->enableComponents);
if ($dao->compName == 'Contact') {
$enabled = true;
}
//filter report listings by permissions
if (!($enabled && CRM_Report_Utils_Report::isInstancePermissioned($dao->id))) {
continue;
}
if (trim($dao->title)) {
if ($ovID) {
$title = ts("Report(s) created from the template: %1", array(1 => $dao->label));
}
$rows[$dao->compName][$dao->id]['title'] = $dao->title;
$rows[$dao->compName][$dao->id]['label'] = $dao->label;
$rows[$dao->compName][$dao->id]['description'] = $dao->description;
$rows[$dao->compName][$dao->id]['url'] = CRM_Utils_System::url("{$url}/{$dao->id}", "reset=1");
if (CRM_Core_Permission::check('administer Reports')) {
$rows[$dao->compName][$dao->id]['deleteUrl'] = CRM_Utils_System::url("{$url}/{$dao->id}", 'action=delete&reset=1');
}
}
}
return $rows;
}
/**
* Get headers, optionally with filters
*
* @param array $params Array of optional params. Providing 'id' as a param would return a particular header with the
* corresponding ID. Providing 'withFilters' as true would join the results with corresponding
* filters.
*
* @return array
* @throws CRM_Extension_Exception
*/
public static function getHeaders($params)
{
$whereClause = isset($params['id']) ? 'h.id = ' . (int) $params['id'] : 'true';
$withFilters = isset($params['withFilters']) && $params['withFilters'];
$query = "SELECT h.id, h.label, h.image, h.show_logo, h.logo_image";
$query .= $withFilters ? ", f.id AS filter_id, f.entity_table, f.entity_table, f.entity_id" : "";
$query .= " FROM civicrm_simplemailheader h";
$query .= $withFilters ? " RIGHT JOIN civicrm_simplemailheaderfilter f ON h.id = f.header_id" : "";
$query .= " WHERE {$whereClause}";
// Using UNION to simulate OUTER JOIN as MySQL does not support OUTER (or FULL) JOIN
$query .= $withFilters ? " UNION " . str_replace('RIGHT JOIN', 'LEFT JOIN', $query) : "";
// $query .= " ORDER BY h.id";
try {
/** @var CRM_Simplemail_DAO_SimpleMailHeader|CRM_Simplemail_DAO_SimpleMailHeaderFilter|CRM_Core_DAO $dao */
$dao = CRM_Core_DAO::executeQuery($query);
$headersWithFilters = array();
while ($dao->fetch()) {
$header = $dao->toArray();
$header['image_url'] = $dao->image ? static::getImageUrl($dao->image, 'image') : NULL;
$header['logo_image_url'] = $dao->logo_image ? static::getImageUrl($dao->logo_image, 'logo_image') : NULL;
$headersWithFilters[] = $header;
}
} catch (Exception $e) {
$dao = isset($dao) ? $dao : NULL;
throw new CRM_Extension_Exception('Failed to retrieve headers: ' . $e->getMessage(), 500, array('dao' => $dao));
}
return array('values' => $headersWithFilters, 'dao' => $dao);
}
/**
* Test creating an index.
*
* We want to be sure it creates an index and exits gracefully if the index
* already exists.
*/
public function testCombinedIndex()
{
$tables = array('civicrm_uf_join' => array('weight'));
CRM_Core_BAO_SchemaHandler::createIndexes($tables);
$tables = array('civicrm_uf_join' => array(array('weight', 'module')));
CRM_Core_BAO_SchemaHandler::createIndexes($tables);
$dao = CRM_Core_DAO::executeQuery("SHOW INDEX FROM civicrm_uf_join");
$weightCount = 0;
$combinedCount = 0;
$indexes = array();
while ($dao->fetch()) {
if ($dao->Column_name == 'weight') {
$weightCount++;
$indexes[$dao->Key_name] = $dao->Key_name;
}
if ($dao->Column_name == 'module') {
$combinedCount++;
$this->assertArrayHasKey($dao->Key_name, $indexes);
}
}
foreach (array_keys($indexes) as $index) {
CRM_Core_DAO::executeQuery("ALTER TABLE civicrm_uf_join DROP INDEX " . $index);
}
$this->assertEquals(2, $weightCount);
}
function postProcess()
{
// define some stats
$activities_total = count($this->_activityHolderIds);
$activities_processed = 0;
$activities_detected = 0;
$activities_fixed = 0;
// filter for relevant activities
$activity_type_id = (int) CRM_Householdmerge_Logic_Configuration::getCheckHouseholdActivityTypeID();
$activity_status_ids = CRM_Householdmerge_Logic_Configuration::getFixableActivityStatusIDs();
$activity_ids = implode(',', $this->_activityHolderIds);
$filter_query = "SELECT id AS activity_id FROM civicrm_activity\n WHERE civicrm_activity.activity_type_id = {$activity_type_id} \n AND civicrm_activity.status_id IN ({$activity_status_ids})\n AND civicrm_activity.id IN ({$activity_ids});";
$filtered_activities = CRM_Core_DAO::executeQuery($filter_query);
// go through all activites and try to fix them
while ($filtered_activities->fetch()) {
$activities_processed += 1;
$problem = CRM_Householdmerge_Logic_Problem::extractProblem($filtered_activities->activity_id);
if ($problem) {
$activities_detected += 1;
if ($problem->fix()) {
$activities_fixed += 1;
}
}
}
// show stats
CRM_Core_Session::setStatus(ts('%1 of the %2 selected activities were processed, %3 of them could be fixed.', array(1 => $activities_detected, 2 => $activities_total, 3 => $activities_fixed, 'domain' => 'de.systopia.householdmerge')), ts('%1 Household Problems Fixed', array(1 => $activities_fixed, 'domain' => 'de.systopia.householdmerge')), $activities_fixed > 0 ? 'info' : 'warn');
parent::postProcess();
}
/**
* Populate change of any due date (self_appraisal_due, manager_appraisal_due, grade_due)
* to all Appraisals of this Appraisal Cycle which have 'due_changed' = 0.
*/
public static function populateDueDates(array $params)
{
$populateData = array();
if (empty($params['id'])) {
throw new Exception("Cannot populate Appraisal due dates with no Appraisal Cycle 'id' given.");
}
if (isset($params['self_appraisal_due'])) {
$populateData['self_appraisal_due'] = $params['self_appraisal_due'];
}
if (isset($params['manager_appraisal_due'])) {
$populateData['manager_appraisal_due'] = $params['manager_appraisal_due'];
}
if (isset($params['grade_due'])) {
$populateData['grade_due'] = $params['grade_due'];
}
if (empty($populateData)) {
return false;
}
$queryParams = array();
$queryFieldSet = array();
$i = 1;
foreach ($populateData as $field => $value) {
$queryFieldSet[] = $field . ' = %' . $i;
$queryParams[$i++] = array($value, 'String');
}
$query = 'UPDATE civicrm_appraisal SET ' . implode(', ', $queryFieldSet) . ' WHERE appraisal_cycle_id = %' . $i . ' AND due_changed = 0';
$queryParams[$i] = array($params['id'], 'Integer');
CRM_Core_DAO::executeQuery($query, $queryParams);
return true;
}
private function _execute($query, $force = FALSE)
{
static $date;
if ($force || $query->is_active && $query->needsRunning()) {
$start = microtime(TRUE);
$dao = CRM_Core_DAO::executeQuery($query->query);
$finish = microtime(TRUE);
$execute = $finish - $start;
$time = array();
if ($execute >= 3600) {
$time[] = (int) ($execute / 3600) . 'h';
$execute = $execute % 3600;
}
if ($execute >= 60) {
$time[] = (int) ($execute / 60) . 'm';
$execute = $execute % 60;
}
if ($execute > 1) {
$time[] = number_format($execute, 4) . 's';
} else {
$time[] = number_format($execute * 1000, 4) . 'ms';
}
$time = implode(' ', $time);
echo "{$query->name}: {$dao->affectedRows()} row(s) affected, {$time}\n";
$date = $query->saveLastRun($date);
return TRUE;
}
return FALSE;
}
/**
* Creates or updates a participant payment record.
*
* @param array $params
* of values to initialize the record with.
* @param array $ids
* with one values of id for this participantPayment record (for update).
*
* @return object
* the partcipant payment record
*/
public static function create(&$params, &$ids)
{
if (isset($ids['id'])) {
CRM_Utils_Hook::pre('edit', 'ParticipantPayment', $ids['id'], $params);
} else {
CRM_Utils_Hook::pre('create', 'ParticipantPayment', NULL, $params);
}
$participantPayment = new CRM_Event_BAO_ParticipantPayment();
$participantPayment->copyValues($params);
if (isset($ids['id'])) {
$participantPayment->id = CRM_Utils_Array::value('id', $ids);
} else {
$participantPayment->find(TRUE);
}
$participantPayment->save();
if (isset($ids['id'])) {
CRM_Utils_Hook::post('edit', 'ParticipantPayment', $ids['id'], $participantPayment);
} else {
CRM_Utils_Hook::post('create', 'ParticipantPayment', NULL, $participantPayment);
}
//generally if people are creating participant_payments via the api they won't be setting the line item correctly - we can't help them if they are doing complex transactions
// but if they have a single line item for the contribution we can assume it should refer to the participant line
$lineItemCount = CRM_Core_DAO::singleValueQuery("select count(*) FROM civicrm_line_item WHERE contribution_id = %1", array(1 => array($participantPayment->contribution_id, 'Integer')));
if ($lineItemCount == 1) {
$sql = "UPDATE civicrm_line_item li\n SET entity_table = 'civicrm_participant', entity_id = %1\n WHERE contribution_id = %2 AND entity_table = 'civicrm_contribution'";
CRM_Core_DAO::executeQuery($sql, array(1 => array($participantPayment->participant_id, 'Integer'), 2 => array($participantPayment->contribution_id, 'Integer')));
}
return $participantPayment;
}
function __construct()
{
$fields = array();
$query = " \n SELECT column_name, label , option_group_id \n FROM civicrm_custom_field \n WHERE is_active = 1 AND column_name='" . $this->_gradeField['column_name'] . "' AND custom_group_id = ( SELECT id FROM civicrm_custom_group WHERE table_name='{$this->_customTable}' ) ";
$dao_column = CRM_Core_DAO::executeQuery($query);
$this->_optionFields = $this->_textFields = array();
while ($dao_column->fetch()) {
$fields[$dao_column->column_name] = array('required' => true, 'title' => $dao_column->label, 'no_display' => true);
$this->_gradeField['op_group_id'] = $dao_column->option_group_id;
}
$filters = array();
// filter for Grade
$options = array();
$query = "SELECT label , value FROM civicrm_option_value WHERE option_group_id =" . $this->_gradeField['op_group_id'] . " AND is_active=1";
$dao = CRM_Core_DAO::executeQuery($query);
while ($dao->fetch()) {
$options[$dao->value] = $dao->label;
}
$filters[$this->_gradeField['column_name']] = array('title' => ts('Grade'), 'operatorType' => CRM_Report_Form::OP_SELECT, 'options' => array('' => '-select-') + $options, 'type' => CRM_Utils_Type::T_STRING);
//get teacher with relatioship
$this->teacherOptions = array();
$query = " SELECT DISTINCT(contact.id) ,contact.display_name \n FROM civicrm_contact contact \n INNER JOIN civicrm_relationship relationship \n ON ( contact.id=relationship.{$this->_teacherRelContact} AND relationship.is_active=1 ) \n INNER JOIN {$this->_customTable} school_information\n ON ( school_information.entity_id = contact.id AND (school_information.subtype='Staff' OR school_information.subtype='Parent') )\n WHERE relationship.relationship_type_id={$this->_teacherRelationship} ";
$dao = CRM_Core_DAO::executeQuery($query);
while ($dao->fetch()) {
$this->teacherOptions[$dao->id] = $dao->display_name;
}
$this->_columns = array('civicrm_activity' => array('dao' => 'CRM_Activity_DAO_Activity', 'fields' => array('activity_date_time' => array('title' => ts('Date'), 'no_display' => true, 'required' => true, 'type' => CRM_Utils_Type::T_TIME))), $this->_customTable => array('dao' => 'CRM_Contact_DAO_Contact', 'fields' => $fields, 'filters' => $filters), 'civicrm_contact' => array('dao' => 'CRM_Contact_DAO_Contact', 'fields' => array('display_name' => array('no_display' => true, 'required' => true, 'title' => ts('Advisor')), 'id' => array('no_display' => true, 'required' => true)), 'filters' => array('id_teacher' => array('name' => 'id', 'title' => ts('Advisor'), 'operatorType' => CRM_Report_Form::OP_SELECT, 'options' => array('' => '-select-') + $this->teacherOptions, 'type' => CRM_Utils_Type::T_INT))), 'civicrm_contact_student' => array('dao' => 'CRM_Contact_DAO_Contact', 'fields' => array('display_name' => array('no_display' => true, 'required' => true, 'title' => ts('Student')), 'id' => array('no_display' => true, 'required' => true))), 'civicrm_contact_parent' => array('dao' => 'CRM_Contact_DAO_Contact', 'fields' => array('display_name' => array('no_display' => true, 'required' => true, 'title' => ts('Parent')), 'id' => array('no_display' => true, 'required' => true))));
parent::__construct();
}
/**
* Heart of the viewing process. The runner gets all the meta data for
* the contact and calls the appropriate type of page to view.
*
* @return void
*/
public function preProcess()
{
//fetch the dedupe exception contacts.
$dedupeExceptions = array();
$exception = new CRM_Dedupe_DAO_Exception();
$exception->find();
$contactIds = array();
while ($exception->fetch()) {
$key = "{$exception->contact_id1}_{$exception->contact_id2}";
$contactIds[$exception->contact_id1] = $exception->contact_id1;
$contactIds[$exception->contact_id2] = $exception->contact_id2;
$dedupeExceptions[$key] = array('main' => array('id' => $exception->contact_id1), 'other' => array('id' => $exception->contact_id2));
}
//get the dupe contacts display names.
if (!empty($dedupeExceptions)) {
$sql = 'select id, display_name from civicrm_contact where id IN ( ' . implode(', ', $contactIds) . ' )';
$contact = CRM_Core_DAO::executeQuery($sql);
$displayNames = array();
while ($contact->fetch()) {
$displayNames[$contact->id] = $contact->display_name;
}
foreach ($dedupeExceptions as $key => &$values) {
$values['main']['name'] = CRM_Utils_Array::value($values['main']['id'], $displayNames);
$values['other']['name'] = CRM_Utils_Array::value($values['other']['id'], $displayNames);
}
}
$this->assign('dedupeExceptions', $dedupeExceptions);
}
public function tearDown()
{
parent::tearDown();
CRM_Core_DAO::executeQuery('DROP TEMPORARY TABLE IF EXISTS civireport_contribution_detail_temp1');
CRM_Core_DAO::executeQuery('DROP TEMPORARY TABLE IF EXISTS civireport_contribution_detail_temp2');
CRM_Core_DAO::executeQuery('DROP TEMPORARY TABLE IF EXISTS civireport_contribution_detail_temp3');
}
/**
* Function for building Pledge Name combo box
*/
function pledgeName(&$config)
{
$getRecords = FALSE;
if (isset($_GET['name']) && $_GET['name']) {
$name = CRM_Utils_Type::escape($_GET['name'], 'String');
$name = str_replace('*', '%', $name);
$whereClause = "p.creator_pledge_desc LIKE '%{$name}%' ";
$getRecords = TRUE;
}
if (isset($_GET['id']) && is_numeric($_GET['id'])) {
$pledgeId = CRM_Utils_Type::escape($_GET['id'], 'Integer');
$whereClause = "p.id = {$pledgeId} ";
$getRecords = TRUE;
}
if ($getRecords) {
$query = "\nSELECT p.creator_pledge_desc, p.id\nFROM civicrm_pb_pledge p\nWHERE {$whereClause}\n";
$dao = CRM_Core_DAO::executeQuery($query);
$elements = array();
while ($dao->fetch()) {
$elements[] = array('name' => $dao->creator_pledge_desc, 'value' => $dao->id);
}
}
if (empty($elements)) {
$name = $_GET['name'];
if (!$name && isset($_GET['id'])) {
$name = $_GET['id'];
}
$elements[] = array('name' => trim($name, '*'), 'value' => trim($name, '*'));
}
echo CRM_Utils_JSON::encode($elements, 'value');
CRM_Utils_System::civiExit();
}
/**
* Test in which an outer function ($transactionalFunction_outermost) makes multiple calls
* to inner functions ($transactionalFunction_inner) but then rollsback the entire set.
*/
function testRollback_RawInsert_2xInner()
{
$cids = array();
$test = $this;
$transactionalFunction_inner = function () use(&$cids, $test) {
$tx = new CRM_Core_Transaction();
$r = CRM_Core_DAO::executeQuery("INSERT INTO civicrm_contact(first_name,last_name) VALUES ('ff', 'll')");
$cid = mysql_insert_id();
$cids[] = $cid;
$test->assertContactsExist($cids, TRUE);
// End of inner $tx; neither COMMIT nor ROLLBACK b/c another $tx remains
};
$transactionalFunction_outermost = function () use(&$cids, $test, $transactionalFunction_inner) {
$tx = new CRM_Core_Transaction();
$transactionalFunction_inner();
$transactionalFunction_inner();
$tx->rollback();
// Mark ROLLBACK, but don't execute yet
$test->assertContactsExist($cids, TRUE);
// not yet rolled back
// End of outermost $tx; ROLLBACK will execute ASAP
};
$transactionalFunction_outermost();
$test->assertContactsExist($cids, FALSE);
}
