3 +--------------------------------------------------------------------+
5 +--------------------------------------------------------------------+
6 | Copyright CiviCRM LLC (c) 2004-2018 |
7 +--------------------------------------------------------------------+
8 | This file is a part of CiviCRM. |
10 | CiviCRM is free software; you can copy, modify, and distribute it |
11 | under the terms of the GNU Affero General Public License |
12 | Version 3, 19 November 2007 and the CiviCRM Licensing Exception. |
14 | CiviCRM is distributed in the hope that it will be useful, but |
15 | WITHOUT ANY WARRANTY; without even the implied warranty of |
16 | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. |
17 | See the GNU Affero General Public License for more details. |
19 | You should have received a copy of the GNU Affero General Public |
20 | License and the CiviCRM Licensing Exception along |
21 | with this program; if not, contact CiviCRM LLC |
22 | at info[AT]civicrm[DOT]org. If you have questions about the |
23 | GNU Affero General Public License or the licensing of CiviCRM, |
24 | see the CiviCRM license FAQ at http://civicrm.org/licensing |
25 +--------------------------------------------------------------------+
29 * Upgrade logic for 4.3
31 class CRM_Upgrade_Incremental_php_FourThree
extends CRM_Upgrade_Incremental_Base
{
34 * Compute any messages which should be displayed beforeupgrade.
36 * Note: This function is called iteratively for each upcoming
37 * revision to the database.
39 * @param string $preUpgradeMessage
41 * a version number, e.g. '4.3.alpha1', '4.3.beta3', '4.3.0'.
42 * @param null $currentVer
46 public function setPreUpgradeMessage(&$preUpgradeMessage, $rev, $currentVer = NULL) {
47 if ($rev == '4.3.beta3') {
49 //sql for checking orphaned contribution records
50 $sql = "SELECT COUNT(ct.id) FROM civicrm_contribution ct LEFT JOIN civicrm_contact c ON ct.contact_id = c.id WHERE c.id IS NULL";
51 $count = CRM_Core_DAO
::singleValueQuery($sql, array(), TRUE, FALSE);
54 $error = ts("There is a data integrity issue with this CiviCRM database. It contains %1 contribution records which are linked to contact records that have been deleted. You will need to correct this manually before you can run the upgrade. Use the following MySQL query to identify the problem records: %2 These records will need to be deleted or linked to an existing contact record.", array(
56 2 => '<em>SELECT ct.* FROM civicrm_contribution ct LEFT JOIN civicrm_contact c ON ct.contact_id = c.id WHERE c.id IS NULL;</em>',
58 CRM_Core_Error
::fatal($error);
62 if ($rev == '4.3.beta4' && CRM_Utils_Constant
::value('CIVICRM_UF', FALSE) == 'Drupal6') {
63 // CRM-11823 - Make sure the D6 HTML HEAD technique will work on
64 // upgrade pages ... except when we're in Drush.
65 if (!function_exists('drush_main')) {
66 theme('item_list', array()); // force-load theme registry
67 $theme_registry = theme_get_registry();
68 if (!isset($theme_registry['page']['preprocess functions']) ||
FALSE === array_search('civicrm_preprocess_page_inject', $theme_registry['page']['preprocess functions'])) {
69 CRM_Core_Error
::fatal('Please reset the Drupal cache (Administer => Site Configuration => Performance => Clear cached data))');
74 if ($rev == '4.3.6') {
75 $constraintArray = array(
76 'civicrm_contact' => 'contact_id',
77 'civicrm_payment_processor' => 'payment_processor_id',
80 if (version_compare('4.1alpha1', $currentVer) <= 0) {
81 $constraintArray['civicrm_campaign'] = 'campaign_id';
84 if (version_compare('4.3alpha1', $currentVer) <= 0) {
85 $constraintArray['civicrm_financial_type'] = 'financial_type_id';
88 foreach ($constraintArray as $key => $value) {
89 $query = "SELECT contri_recur.id FROM civicrm_contribution_recur contri_recur LEFT JOIN {$key} ON contri_recur.{$value} = {$key}.id
90 WHERE {$key}.id IS NULL";
91 if ($value != 'contact_id') {
92 $query .= " AND contri_recur.{$value} IS NOT NULL ";
94 $dao = CRM_Core_DAO
::executeQuery($query);
96 $invalidDataMessage = '<strong>Oops, it looks like you have orphaned recurring contribution records in your database. Before this upgrade can complete they will need to be fixed or deleted. <a href="http://wiki.civicrm.org/confluence/display/CRMDOC/Fixing+Orphaned+Contribution+Recur+Records" target="_blank">You can review steps to correct this situation on the documentation wiki.</a></strong>';
97 CRM_Core_Error
::fatal($invalidDataMessage);
105 * Compute any messages which should be displayed after upgrade.
107 * @param string $postUpgradeMessage
110 * an intermediate version; note that setPostUpgradeMessage is called repeatedly with different $revs.
113 public function setPostUpgradeMessage(&$postUpgradeMessage, $rev) {
114 if ($rev == '4.3.alpha1') {
115 // check if CiviMember component is enabled
116 $config = CRM_Core_Config
::singleton();
117 if (in_array('CiviMember', $config->enableComponents
)) {
118 $postUpgradeMessage .= '<br />' . ts('Membership renewal reminders must now be configured using the Schedule Reminders feature, which supports multiple renewal reminders (Administer > Communications > Schedule Reminders). The Update Membership Statuses scheduled job will no longer send membershp renewal reminders. You can use your existing renewal reminder message template(s) with the Schedule Reminders feature.');
119 $postUpgradeMessage .= '<br />' . ts('The Set Membership Reminder Dates scheduled job has been deleted since membership reminder dates stored in the membership table are no longer in use.');
123 //here we do the financial type check and migration
124 $isDefaultsModified = self
::_checkAndMigrateDefaultFinancialTypes();
125 if ($isDefaultsModified) {
126 $postUpgradeMessage .= '<br />' . ts('Please review all price set financial type assignments.');
128 list($context, $orgName) = self
::createDomainContacts();
129 if ($context == 'added') {
130 $postUpgradeMessage .= '<br />' . ts("A new organization contact has been added as the default domain contact using the information from your Organization Address and Contact Info settings: '%1'.", array(1 => $orgName));
132 elseif ($context == 'merged') {
133 $postUpgradeMessage .= '<br />' . ts("The existing organization contact record for '%1' has been marked as the default domain contact, and has been updated with information from your Organization Address and Contact Info settings.", array(1 => $orgName));
136 $providerExists = CRM_Core_DAO
::singleValueQuery("SELECT id FROM civicrm_sms_provider LIMIT 1");
137 if ($providerExists) {
138 $postUpgradeMessage .= '<br />' . ts('SMS providers were found to setup. Please note Clickatell / Twilio are now shipped as extensions and will require installing them to continue working. Extension could be downloaded and installed from <a href="%1">github</a>.', array(1 => 'https://github.com/civicrm/civicrm-core/tree/master/tools/extensions'));
142 if ($rev == '4.3.alpha2') {
145 FROM civicrm_action_schedule
146 WHERE entity_value = '' OR entity_value IS NULL
149 $dao = CRM_Core_DAO
::executeQuery($sql);
152 while ($dao->fetch()) {
153 $reminder[$dao->id
] = $dao->title
;
154 $list .= "<li>{$dao->title}</li>";
156 if (!empty($reminder)) {
157 $list = "<br /><ul>" . $list . "</ul>";
158 $postUpgradeMessage .= '<br />' . ts("Scheduled Reminders must be linked to one or more 'entities' (Events, Event Templates, Activity Types, Membership Types). The following reminders are not configured properly and will not be run. Please review them and update or delete them: %1", array(1 => $list));
161 if ($rev == '4.3.beta2') {
162 $postUpgradeMessage .= '<br />' . ts('Default versions of the following System Workflow Message Templates have been modified to handle new functionality: <ul><li>Events - Registration Confirmation and Receipt (on-line)</li><li>Events - Registration Confirmation and Receipt (off-line)</li><li>Pledges - Acknowledgement</li><li>Pledges - Payment Reminder</li><li>Contributions - Receipt (off-line)</li><li>Contributions - Receipt (on-line)</li><li>Memberships - Signup and Renewal Receipts (off-line)</li><li>Memberships - Receipt (on-line)</li><li>Personal Campaign Pages - Admin Notification</li></ul> If you have modified these templates, please review the new default versions and implement updates as needed to your copies (Administer > Communications > Message Templates > System Workflow Messages).');
165 if ($rev == '4.3.beta5') {
166 $postUpgradeMessage .= '<br />' . ts("If you are interested in trying out the new Accounting Integration features, please review user permissions and assign the new 'manual batch' permissions as appropriate.");
170 SELECT ceft.id FROM `civicrm_financial_trxn` cft
171 LEFT JOIN civicrm_entity_financial_trxn ceft
172 ON ceft.financial_trxn_id = cft.id AND ceft.entity_table = 'civicrm_contribution'
173 LEFT JOIN civicrm_contribution cc
174 ON cc.id = ceft.entity_id AND ceft.entity_table = 'civicrm_contribution'
178 $dao = CRM_Core_DAO
::executeQuery($query);
179 $isOrphanData = TRUE;
182 SELECT cli.id FROM civicrm_line_item cli
183 LEFT JOIN civicrm_contribution cc ON cli.entity_id = cc.id AND cli.entity_table = 'civicrm_contribution'
184 LEFT JOIN civicrm_participant cp ON cli.entity_id = cp.id AND cli.entity_table = 'civicrm_participant'
185 WHERE CASE WHEN cli.entity_table = 'civicrm_contribution'
190 $dao = CRM_Core_DAO
::executeQuery($query);
192 $revPattern = '/^((\d{1,2})\.\d{1,2})\.(\d{1,2}|\w{4,7})?$/i';
193 preg_match($revPattern, $currentVer, $version);
194 if ($version[1] >= 4.3) {
197 FROM civicrm_financial_item cfi
198 LEFT JOIN civicrm_entity_financial_trxn ceft ON ceft.entity_table = 'civicrm_financial_item' and cfi.id = ceft.entity_id
199 WHERE ceft.entity_id IS NULL;
201 $dao = CRM_Core_DAO
::executeQuery($query);
203 $isOrphanData = FALSE;
207 $isOrphanData = FALSE;
213 $postUpgradeMessage .= "</br> <strong>" . ts('Your database contains extraneous financial records related to deleted contacts and contributions. These records should not affect the site and will not appear in reports, search results or exports. However you may wish to clean them up. Refer to <a href="%1" target="_blank">this wiki page for details</a>.
214 ', array(1 => 'http://wiki.civicrm.org/confluence/display/CRMDOC/Clean+up+extraneous+financial+data+-+4.3+upgrades')) . "</strong>";
217 if ($rev == '4.3.4') {
218 $postUpgradeMessage .= '<br />' . ts('System Administrator Alert: If you are running scheduled jobs using CLI.php, you will need to reconfigure cron tasks to include a password. Scheduled jobs will no longer run if the password is not provided (<a href="%1" target="_blank">learn more</a>).',
219 array(1 => 'http://wiki.civicrm.org/confluence/display/CRMDOC/Managing+Scheduled+Jobs'));
221 if ($rev == '4.3.5') {
222 $postUpgradeMessage .= '<br />' . ts('Default versions of the following System Workflow Message Templates have been modified to handle new functionality: <ul><li>Events - Registration Confirmation and Receipt (on-line)</li><li>Events - Registration Confirmation and Receipt (off-line)</li></ul> If you have modified these templates, please review the new default versions and implement updates as needed to your copies (Administer > Communications > Message Templates > System Workflow Messages).');
224 if ($rev == '4.3.6') {
225 $flag = CRM_Core_DAO
::singleValueQuery('SELECT count(ccp.id) FROM civicrm_contribution_product ccp
226 INNER JOIN civicrm_product cp ON ccp.product_id = cp.id
227 WHERE ccp.financial_type_id IS NULL and cp.cost > 0');
229 $postUpgradeMessage .= '<br />' . ts('Your database contains one or more premiums which have a cost but are not linked to a financial type. If you are exporting transations to an accounting package, this will result in unbalanced transactions. <a href="%1" target="_blank">You can review steps to correct this situation on the wiki.</a>',
230 array(1 => 'http://wiki.civicrm.org/confluence/display/CRMDOC/Fixing+Issues+Caused+by+Missing+Cost+of+Goods+Account+-+4.3+Upgrades'));
240 public function upgrade_4_3_alpha1($rev) {
241 self
::task_4_3_alpha1_checkDBConstraints();
243 // add indexes for civicrm_entity_financial_trxn
245 $this->addTask('Check/Add indexes for civicrm_entity_financial_trxn', 'task_4_3_x_checkIndexes', $rev);
246 // task to process sql
247 $this->addTask(ts('Upgrade DB to %1: SQL', array(1 => '4.3.alpha1')), 'runSql', $rev);
250 $this->addTask('Populate financial type values for price records', 'assignFinancialTypeToPriceRecords');
251 //CRM-11514 create financial records for contributions
252 $this->addTask('Create financial records for contributions', 'createFinancialRecords');
254 $minId = CRM_Core_DAO
::singleValueQuery('SELECT coalesce(min(id),0) FROM civicrm_contact');
255 $maxId = CRM_Core_DAO
::singleValueQuery('SELECT coalesce(max(id),0) FROM civicrm_contact');
256 for ($startId = $minId; $startId <= $maxId; $startId +
= self
::BATCH_SIZE
) {
257 $endId = $startId + self
::BATCH_SIZE
- 1;
258 $title = "Upgrade timestamps ($startId => $endId)";
259 $this->addTask($title, 'convertTimestamps', $startId, $endId);
263 // fix WP access control
264 $config = CRM_Core_Config
::singleton();
265 if ($config->userFramework
== 'WordPress') {
266 civicrm_wp_set_capabilities();
269 // Update phones CRM-11292.
270 $this->addTask('Upgrade Phone Numbers', 'phoneNumeric');
278 public function upgrade_4_3_alpha2($rev) {
280 $isColumnPresent = CRM_Core_BAO_SchemaHandler
::checkIfFieldExists('civicrm_dedupe_rule_group', 'is_default');
281 if ($isColumnPresent) {
282 CRM_Core_DAO
::executeQuery('ALTER TABLE civicrm_dedupe_rule_group DROP COLUMN is_default');
284 $this->addTask(ts('Upgrade DB to %1: SQL', array(1 => '4.3.alpha2')), 'runSql', $rev);
290 public function upgrade_4_3_alpha3($rev) {
291 $this->addTask(ts('Upgrade DB to %1: SQL', array(1 => '4.3.alpha3')), 'runSql', $rev);
297 public function upgrade_4_3_beta2($rev) {
298 $this->addTask(ts('Upgrade DB to %1: SQL', array(1 => '4.3.beta2')), 'runSql', $rev);
302 CRM_Core_DAO
::checkTableExists('log_civicrm_line_item') &&
303 CRM_Core_BAO_SchemaHandler
::checkIfFieldExists('log_civicrm_line_item', 'label')
305 CRM_Core_DAO
::executeQuery('ALTER TABLE `log_civicrm_line_item` CHANGE `label` `label` VARCHAR(255) NULL DEFAULT NULL');
312 public function upgrade_4_3_beta3($rev) {
313 $this->addTask(ts('Upgrade DB to %1: SQL', array(1 => '4.3.beta3')), 'runSql', $rev);
315 $query = "SELECT id, form_values FROM civicrm_report_instance WHERE form_values LIKE '%contribution_type%'";
316 $this->addTask('Replace contribution_type to financial_type in table civicrm_report_instance', 'replaceContributionTypeId', $query, 'reportInstance');
317 $query = "SELECT * FROM civicrm_saved_search WHERE form_values LIKE '%contribution_type%'";
318 $this->addTask('Replace contribution_type to financial_type in table civicrm_saved_search', 'replaceContributionTypeId', $query, 'savedSearch');
324 public function upgrade_4_3_beta4($rev) {
325 $this->addTask(ts('Upgrade DB to %1: SQL', array(1 => '4.3.beta4')), 'runSql', $rev);
326 // add indexes for civicrm_entity_financial_trxn
328 $this->addTask('Check/Add indexes for civicrm_entity_financial_trxn', 'task_4_3_x_checkIndexes', $rev);
334 public function upgrade_4_3_beta5($rev) {
337 CRM_Core_DAO
::checkTableExists('log_civicrm_financial_trxn') &&
338 CRM_Core_BAO_SchemaHandler
::checkIfFieldExists('log_civicrm_financial_trxn', 'trxn_id')
340 CRM_Core_DAO
::executeQuery('ALTER TABLE `log_civicrm_financial_trxn` CHANGE `trxn_id` `trxn_id` VARCHAR(255) NULL DEFAULT NULL');
342 // CRM-12142 - some sites didn't get this column added yet, and sites which installed 4.3 from scratch will already have it
343 // CRM-12367 - add this column to single lingual sites only
344 $upgrade = new CRM_Upgrade_Form();
345 if (!$upgrade->multilingual
&&
346 !CRM_Core_BAO_SchemaHandler
::checkIfFieldExists('civicrm_premiums', 'premiums_nothankyou_label')
349 ALTER TABLE civicrm_premiums
350 ADD COLUMN premiums_nothankyou_label varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL
351 COMMENT 'Label displayed for No Thank-you option in premiums block (e.g. No thank you)'
353 CRM_Core_DAO
::executeQuery($query, array(), TRUE, NULL, FALSE, FALSE);
355 $this->addTask(ts('Upgrade DB to %1: SQL', array(1 => '4.3.beta5')), 'runSql', $rev);
361 public function upgrade_4_3_4($rev) {
362 $this->addTask(ts('Upgrade DB to %1: SQL', array(1 => '4.3.4')), 'runSql', $rev);
368 public function upgrade_4_3_5($rev) {
370 $config = CRM_Core_Config
::singleton();
371 $dbname = DB
::parseDSN($config->dsn
);
372 $sql = "SELECT DELETE_RULE
373 FROM information_schema.REFERENTIAL_CONSTRAINTS
374 WHERE CONSTRAINT_NAME = 'FK_civicrm_financial_item_contact_id'
375 AND CONSTRAINT_SCHEMA = %1";
376 $params = array(1 => array($dbname['database'], 'String'));
377 $onDelete = CRM_Core_DAO
::singleValueQuery($sql, $params, TRUE, FALSE);
379 if ($onDelete != 'CASCADE') {
380 $query = "ALTER TABLE `civicrm_financial_item`
381 DROP FOREIGN KEY FK_civicrm_financial_item_contact_id,
382 DROP INDEX FK_civicrm_financial_item_contact_id;";
383 CRM_Core_DAO
::executeQuery($query, array(), TRUE, NULL, FALSE, FALSE);
385 ALTER TABLE `civicrm_financial_item`
386 ADD CONSTRAINT `FK_civicrm_financial_item_contact_id` FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE;
388 CRM_Core_DAO
::executeQuery($query, array(), TRUE, NULL, FALSE, FALSE);
390 $this->addTask(ts('Upgrade DB to %1: SQL', array(1 => '4.3.5')), 'runSql', $rev);
396 public function upgrade_4_3_6($rev) {
398 $this->addTask(ts('Add missing constraints'), 'addMissingConstraints', $rev);
400 $this->addTask('Add ON DELETE Options for constraints', 'task_4_3_x_checkConstraints', $rev);
401 $this->addTask(ts('Upgrade DB to %1: SQL', array(1 => '4.3.6')), 'runSql', $rev);
403 // update line_item, financial_trxn and financial_item table for recurring contributions
404 $this->addTask('Update financial_account_id in financial_trxn table', 'updateFinancialTrxnData', $rev);
405 $this->addTask('Update Line Item Data', 'updateLineItemData', $rev);
412 public function assignFinancialTypeToPriceRecords() {
413 $upgrade = new CRM_Upgrade_Form();
414 //here we update price set entries
416 SELECT id, LCASE(name) name
417 FROM civicrm_financial_type
418 WHERE name IN ('Donation', 'Event Fee', 'Member Dues');
420 $daoFinancialIds = CRM_Core_DAO
::executeQuery($sqlFinancialIds);
421 while ($daoFinancialIds->fetch()) {
422 $financialIds[$daoFinancialIds->name
] = $daoFinancialIds->id
;
424 $sqlPriceSetUpdate = "
425 UPDATE civicrm_price_set ps
426 SET ps.financial_type_id =
428 WHEN ps.extends LIKE '%1%' THEN {$financialIds['event fee']}
429 WHEN ps.extends LIKE '2' THEN {$financialIds['donation']}
430 WHEN ps.extends LIKE '3' THEN {$financialIds['member dues']}
432 WHERE financial_type_id IS NULL
434 CRM_Core_DAO
::executeQuery($sqlPriceSetUpdate);
436 //here we update price field value rows
437 $sqlPriceFieldValueUpdate = "
438 UPDATE civicrm_price_field_value pfv
439 LEFT JOIN civicrm_membership_type mt ON (pfv.membership_type_id = mt.id)
440 INNER JOIN civicrm_price_field pf ON (pfv.price_field_id = pf.id)
441 INNER JOIN civicrm_price_set ps ON (pf.price_set_id = ps.id)
442 SET pfv.financial_type_id =
444 WHEN pfv.membership_type_id IS NOT NULL THEN mt.financial_type_id
445 WHEN pfv.membership_type_id IS NULL THEN ps.financial_type_id
448 CRM_Core_DAO
::executeQuery($sqlPriceFieldValueUpdate);
456 public static function _checkAndMigrateDefaultFinancialTypes() {
457 $modifiedDefaults = FALSE;
458 //insert types if not exists
461 FROM civicrm_contribution_type
462 WHERE name IN ('Donation', 'Event Fee', 'Member Dues') AND is_active =1
464 $daoFetchTypes = CRM_Core_DAO
::executeQuery($sqlFetchTypes);
466 if ($daoFetchTypes->N
< 3) {
467 $modifiedDefaults = TRUE;
468 $insertStatments = array(
469 'Donation' => "('Donation', 0, 1, 1)",
470 'Member' => "('Member Dues', 0, 1, 1)",
471 'Event Fee' => "('Event Fee', 0, 1, 0)",
473 foreach ($insertStatments as $values) {
475 INSERT INTO civicrm_contribution_type (name, is_reserved, is_active, is_deductible)
477 ON DUPLICATE KEY UPDATE is_active = 1
479 CRM_Core_DAO
::executeQuery($query);
482 return $modifiedDefaults;
488 public function createFinancialRecords() {
489 $upgrade = new CRM_Upgrade_Form();
491 // update civicrm_entity_financial_trxn.amount = civicrm_financial_trxn.total_amount
493 UPDATE civicrm_entity_financial_trxn ceft
494 LEFT JOIN civicrm_financial_trxn cft ON cft.id = ceft.financial_trxn_id
495 SET ceft.amount = total_amount
496 WHERE cft.net_amount IS NOT NULL
497 AND ceft.entity_table = 'civicrm_contribution'
499 CRM_Core_DAO
::executeQuery($query);
501 $contributionStatus = CRM_Contribute_PseudoConstant
::contributionStatus(NULL, 'name');
502 $completedStatus = array_search('Completed', $contributionStatus);
503 $pendingStatus = array_search('Pending', $contributionStatus);
504 $cancelledStatus = array_search('Cancelled', $contributionStatus);
505 $queryParams = array(
506 1 => array($completedStatus, 'Integer'),
507 2 => array($pendingStatus, 'Integer'),
508 3 => array($cancelledStatus, 'Integer'),
511 $accountType = key(CRM_Core_PseudoConstant
::accountOptionValues('financial_account_type', NULL, " AND v.name = 'Asset' "));
514 FROM civicrm_financial_account
516 AND financial_account_type_id = {$accountType}
518 $financialAccountId = CRM_Core_DAO
::singleValueQuery($query);
520 $accountRelationsips = CRM_Core_PseudoConstant
::get('CRM_Financial_DAO_EntityFinancialAccount',
521 'account_relationship', CRM_Core_DAO
::$_nullArray, 'validate');
523 $accountsReceivableAccount = array_search('Accounts Receivable Account is', $accountRelationsips);
524 $incomeAccountIs = array_search('Income Account is', $accountRelationsips);
525 $assetAccountIs = array_search('Asset Account is', $accountRelationsips);
526 $expenseAccountIs = array_search('Expense Account is', $accountRelationsips);
528 $financialItemStatus = CRM_Core_PseudoConstant
::get('CRM_Financial_DAO_FinancialItem', 'status_id',
529 CRM_Core_DAO
::$_nullArray, 'validate');
530 $unpaidStatus = array_search('Unpaid', $financialItemStatus);
531 $paidStatus = array_search('Paid', $financialItemStatus);
533 $validCurrencyCodes = CRM_Core_PseudoConstant
::currencyCode();
534 $validCurrencyCodes = implode("','", $validCurrencyCodes);
535 $config = CRM_Core_Config
::singleton();
536 $defaultCurrency = $config->defaultCurrency
;
537 $now = date('YmdHis');
539 //adding financial_trxn records and entity_financial_trxn records related to contribution
540 //Add temp column for easy entry in entity_financial_trxn
541 $sql = "ALTER TABLE civicrm_financial_trxn ADD COLUMN contribution_id INT DEFAULT NULL";
542 CRM_Core_DAO
::executeQuery($sql);
544 //pending pay later status handling
546 INSERT INTO civicrm_financial_trxn
547 (contribution_id, payment_instrument_id, currency, total_amount, net_amount, fee_amount, trxn_id, status_id,
548 check_number, to_financial_account_id, from_financial_account_id, trxn_date)
549 SELECT con.id as contribution_id, con.payment_instrument_id,
550 IF(con.currency IN ('{$validCurrencyCodes}'), con.currency, '{$defaultCurrency}') as currency,
551 con.total_amount, con.net_amount, con.fee_amount, con.trxn_id, con.contribution_status_id,
552 con.check_number, efa.financial_account_id as to_financial_account_id, NULL as from_financial_account_id,
553 REPLACE(REPLACE(REPLACE(
555 WHEN con.receive_date IS NOT NULL THEN
557 WHEN con.receipt_date IS NOT NULL THEN
562 , '-', ''), ':', ''), ' ', '') as trxn_date
563 FROM civicrm_contribution con
564 LEFT JOIN civicrm_entity_financial_account efa
565 ON (con.financial_type_id = efa.entity_id AND efa.entity_table = 'civicrm_financial_type'
566 AND efa.account_relationship = {$accountsReceivableAccount})
567 WHERE con.is_pay_later = 1
568 AND con.contribution_status_id = {$pendingStatus}
570 CRM_Core_DAO
::executeQuery($sql);
572 //create a temp table to hold financial account id related to payment instruments
573 $tempTableName1 = CRM_Core_DAO
::createTempTableName();
576 CREATE TEMPORARY TABLE {$tempTableName1}
577 SELECT ceft.financial_account_id financial_account_id, cov.value as instrument_id
578 FROM civicrm_entity_financial_account ceft
579 INNER JOIN civicrm_option_value cov ON cov.id = ceft.entity_id AND ceft.entity_table = 'civicrm_option_value'
580 INNER JOIN civicrm_option_group cog ON cog.id = cov.option_group_id
581 WHERE cog.name = 'payment_instrument'
583 CRM_Core_DAO
::executeQuery($sql);
586 $sql = "ALTER TABLE {$tempTableName1} ADD INDEX index_instrument_id (instrument_id(200));";
587 CRM_Core_DAO
::executeQuery($sql);
589 //create temp table to process completed / cancelled contribution
590 $tempTableName2 = CRM_Core_DAO
::createTempTableName();
592 CREATE TEMPORARY TABLE {$tempTableName2}
593 SELECT con.id as contribution_id, con.payment_instrument_id,
594 IF(con.currency IN ('{$validCurrencyCodes}'), con.currency, '{$defaultCurrency}') as currency,
595 con.total_amount, con.net_amount, con.fee_amount, con.trxn_id, con.contribution_status_id,
596 con.check_number, NULL as from_financial_account_id,
597 REPLACE(REPLACE(REPLACE(
599 WHEN con.receive_date IS NOT NULL THEN
601 WHEN con.receipt_date IS NOT NULL THEN
606 , '-', ''), ':', ''), ' ', '') as trxn_date,
608 WHEN con.payment_instrument_id IS NULL THEN
609 {$financialAccountId}
610 WHEN con.payment_instrument_id IS NOT NULL THEN
611 tpi.financial_account_id
612 END as to_financial_account_id,
613 IF(eft.financial_trxn_id IS NULL, 'insert', eft.financial_trxn_id) as action
614 FROM civicrm_contribution con
615 LEFT JOIN civicrm_entity_financial_trxn eft
616 ON (eft.entity_table = 'civicrm_contribution' AND eft.entity_id = con.id)
617 LEFT JOIN {$tempTableName1} tpi
618 ON con.payment_instrument_id = tpi.instrument_id
619 WHERE con.contribution_status_id IN ({$completedStatus}, {$cancelledStatus})
621 CRM_Core_DAO
::executeQuery($sql);
624 $sql = "ALTER TABLE {$tempTableName2} ADD INDEX index_action (action);";
625 CRM_Core_DAO
::executeQuery($sql);
627 //handling for completed contribution and cancelled contribution
628 //insertion of new records
630 INSERT INTO civicrm_financial_trxn
631 (contribution_id, payment_instrument_id, currency, total_amount, net_amount, fee_amount, trxn_id, status_id, check_number,
632 to_financial_account_id, from_financial_account_id, trxn_date)
633 SELECT tempI.contribution_id, tempI.payment_instrument_id, tempI.currency, tempI.total_amount, tempI.net_amount,
634 tempI.fee_amount, tempI.trxn_id, tempI.contribution_status_id, tempI.check_number,
635 tempI.to_financial_account_id, tempI.from_financial_account_id, tempI.trxn_date
636 FROM {$tempTableName2} tempI
637 WHERE tempI.action = 'insert'
639 CRM_Core_DAO
::executeQuery($sql);
641 //update of existing records
643 UPDATE civicrm_financial_trxn ft
644 INNER JOIN {$tempTableName2} tempU
645 ON (tempU.action != 'insert' AND ft.id = tempU.action)
646 SET ft.from_financial_account_id = NULL,
647 ft.to_financial_account_id = tempU.to_financial_account_id,
648 ft.status_id = tempU.contribution_status_id,
649 ft.payment_instrument_id = tempU.payment_instrument_id,
650 ft.check_number = tempU.check_number,
651 ft.contribution_id = tempU.contribution_id;";
652 CRM_Core_DAO
::executeQuery($sql);
654 //insert the -ve transaction rows for cancelled contributions
656 INSERT INTO civicrm_financial_trxn
657 (contribution_id, payment_instrument_id, currency, total_amount, net_amount, fee_amount, trxn_id, status_id,
658 check_number, to_financial_account_id, from_financial_account_id, trxn_date)
659 SELECT ft.contribution_id, ft.payment_instrument_id, ft.currency, -ft.total_amount, ft.net_amount, ft.fee_amount, ft.trxn_id,
660 ft.status_id, ft.check_number, ft.to_financial_account_id, ft.from_financial_account_id, ft.trxn_date
661 FROM civicrm_financial_trxn ft
662 WHERE ft.status_id = {$cancelledStatus};";
663 CRM_Core_DAO
::executeQuery($sql);
665 //inserting entity financial trxn entries if its not present in entity_financial_trxn for completed and pending contribution statuses
666 //this also handles +ve and -ve both transaction entries for a cancelled contribution
668 INSERT INTO civicrm_entity_financial_trxn (entity_table, entity_id, financial_trxn_id, amount)
669 SELECT 'civicrm_contribution', ft.contribution_id, ft.id, ft.total_amount as amount
670 FROM civicrm_financial_trxn ft
671 WHERE contribution_id IS NOT NULL AND
672 ft.id NOT IN (SELECT financial_trxn_id
673 FROM civicrm_entity_financial_trxn
674 WHERE entity_table = 'civicrm_contribution'
675 AND entity_id = ft.contribution_id)";
676 CRM_Core_DAO
::executeQuery($sql);
677 //end of adding financial_trxn records and entity_financial_trxn records related to contribution
679 //update all linked line_item rows
680 // set line_item.financial_type_id = contribution.financial_type_id if contribution page id is null and not participant line item
681 // set line_item.financial_type_id = price_field_value.financial_type_id if contribution page id is set and not participant line item
682 // set line_item.financial_type_id = event.financial_type_id if its participant line item and line_item.price_field_value_id is null
683 // set line_item.financial_type_id = price_field_value.financial_type_id if its participant line item and line_item.price_field_value_id is set
684 $updateLineItemSql = "
685 UPDATE civicrm_line_item li
686 LEFT JOIN civicrm_contribution con
687 ON (li.entity_id = con.id AND li.entity_table = 'civicrm_contribution')
688 LEFT JOIN civicrm_price_field_value cpfv
689 ON li.price_field_value_id = cpfv.id
690 LEFT JOIN civicrm_participant cp
691 ON (li.entity_id = cp.id AND li.entity_table = 'civicrm_participant')
692 LEFT JOIN civicrm_event ce
693 ON ce.id = cp.event_id
694 SET li.financial_type_id = CASE
695 WHEN (con.contribution_page_id IS NULL || li.price_field_value_id IS NULL) AND cp.id IS NULL THEN
696 con.financial_type_id
697 WHEN (con.contribution_page_id IS NOT NULL AND cp.id IS NULL) || (cp.id IS NOT NULL AND li.price_field_value_id IS NOT NULL) THEN
698 cpfv.financial_type_id
699 WHEN cp.id IS NOT NULL AND li.price_field_value_id IS NULL THEN
702 CRM_Core_DAO
::executeQuery($updateLineItemSql, $queryParams);
704 //add the financial_item entries
705 //add a temp column so that inserting entity_financial_trxn entries gets easy
706 $sql = "ALTER TABLE civicrm_financial_item ADD COLUMN f_trxn_id INT DEFAULT NULL";
707 CRM_Core_DAO
::executeQuery($sql);
709 //add financial_item entries for contribution completed / pending pay later / cancelled
710 $contributionlineItemSql = "
711 INSERT INTO civicrm_financial_item
712 (transaction_date, contact_id, amount, currency, entity_table, entity_id, description, status_id, financial_account_id, f_trxn_id)
714 SELECT REPLACE(REPLACE(REPLACE(ft.trxn_date, '-', ''), ':', ''), ' ', ''), con.contact_id,
715 IF(ft.total_amount < 0 AND con.contribution_status_id = %3, -li.line_total, li.line_total) as line_total, con.currency, 'civicrm_line_item',
716 li.id as line_item_id, li.label as line_item_label,
717 IF(con.contribution_status_id = {$pendingStatus}, {$unpaidStatus}, {$paidStatus}) as status_id, efa.financial_account_id as financial_account_id,
719 FROM civicrm_line_item li
720 INNER JOIN civicrm_contribution con
721 ON (li.entity_id = con.id AND li.entity_table = 'civicrm_contribution')
722 INNER JOIN civicrm_financial_trxn ft
723 ON (con.id = ft.contribution_id)
724 LEFT JOIN civicrm_entity_financial_account efa
725 ON (li.financial_type_id = efa.entity_id AND efa.entity_table = 'civicrm_financial_type'
726 AND efa.account_relationship = {$incomeAccountIs})
727 WHERE con.contribution_status_id IN (%1, %3) OR (con.is_pay_later = 1 AND con.contribution_status_id = %2)";
728 CRM_Core_DAO
::executeQuery($contributionlineItemSql, $queryParams);
730 //add financial_item entries for event
731 $participantLineItemSql = "
732 INSERT INTO civicrm_financial_item
733 (transaction_date, contact_id, amount, currency, entity_table, entity_id, description, status_id, financial_account_id, f_trxn_id)
735 SELECT REPLACE(REPLACE(REPLACE(ft.trxn_date, '-', ''), ':', ''), ' ', ''), con.contact_id,
736 IF(ft.total_amount < 0 AND con.contribution_status_id = %3, -li.line_total, li.line_total) as line_total,
737 con.currency, 'civicrm_line_item', li.id as line_item_id, li.label as line_item_label,
738 IF(con.contribution_status_id = {$pendingStatus}, {$unpaidStatus}, {$paidStatus}) as status_id,
739 efa.financial_account_id as financial_account_id, ft.id as f_trxn_id
740 FROM civicrm_line_item li
741 INNER JOIN civicrm_participant par
742 ON (li.entity_id = par.id AND li.entity_table = 'civicrm_participant')
743 INNER JOIN civicrm_participant_payment pp
744 ON (pp.participant_id = par.id)
745 INNER JOIN civicrm_contribution con
746 ON (pp.contribution_id = con.id)
747 INNER JOIN civicrm_financial_trxn ft
748 ON (con.id = ft.contribution_id)
749 LEFT JOIN civicrm_entity_financial_account efa
750 ON (li.financial_type_id = efa.entity_id AND
751 efa.entity_table = 'civicrm_financial_type' AND efa.account_relationship = {$incomeAccountIs})
752 WHERE con.contribution_status_id IN (%1, %3) OR (con.is_pay_later = 1 AND con.contribution_status_id = %2)";
753 CRM_Core_DAO
::executeQuery($participantLineItemSql, $queryParams);
755 //fee handling for contributions
756 //insert fee entries in financial_trxn for contributions
757 $sql = "ALTER TABLE civicrm_financial_trxn ADD COLUMN is_fee TINYINT DEFAULT NULL";
758 CRM_Core_DAO
::executeQuery($sql);
761 INSERT INTO civicrm_financial_trxn
762 (contribution_id, payment_instrument_id, currency, total_amount, net_amount, fee_amount, trxn_id, status_id, check_number,
763 to_financial_account_id, from_financial_account_id, trxn_date, payment_processor_id, is_fee)
765 SELECT DISTINCT con.id, ft.payment_instrument_id, ft.currency, ft.fee_amount, NULL, NULL, ft.trxn_id, %1 as status_id,
766 ft.check_number, efaFT.financial_account_id as to_financial_account_id, CASE
767 WHEN efaPP.financial_account_id IS NOT NULL THEN
768 efaPP.financial_account_id
769 WHEN tpi.financial_account_id IS NOT NULL THEN
770 tpi.financial_account_id
772 {$financialAccountId}
773 END as from_financial_account_id, ft.trxn_date, ft.payment_processor_id, 1 as is_fee
774 FROM civicrm_contribution con
775 INNER JOIN civicrm_financial_trxn ft
776 ON (ft.contribution_id = con.id)
777 LEFT JOIN civicrm_entity_financial_account efaFT
778 ON (con.financial_type_id = efaFT.entity_id AND efaFT.entity_table = 'civicrm_financial_type'
779 AND efaFT.account_relationship = {$expenseAccountIs})
780 LEFT JOIN civicrm_entity_financial_account efaPP
781 ON (ft.payment_processor_id = efaPP.entity_id AND efaPP.entity_table = 'civicrm_payment_processor'
782 AND efaPP.account_relationship = {$assetAccountIs})
783 LEFT JOIN {$tempTableName1} tpi
784 ON ft.payment_instrument_id = tpi.instrument_id
785 WHERE ft.fee_amount IS NOT NULL AND ft.fee_amount != 0 AND (con.contribution_status_id IN (%1, %3) OR (con.contribution_status_id =%2 AND con.is_pay_later = 1))";
786 CRM_Core_DAO
::executeQuery($sql, $queryParams);
788 //link financial_trxn to contribution
790 INSERT INTO civicrm_entity_financial_trxn
791 (entity_table, entity_id, financial_trxn_id, amount)
792 SELECT 'civicrm_contribution', ft.contribution_id, ft.id, ft.total_amount
793 FROM civicrm_financial_trxn ft
794 WHERE ft.is_fee = 1";
795 CRM_Core_DAO
::executeQuery($sql);
797 //add fee related entries to financial item table
798 $domainId = CRM_Core_Config
::domainID();
799 $domainContactId = CRM_Core_DAO
::getFieldValue('CRM_Core_DAO_Domain', $domainId, 'contact_id');
801 INSERT INTO civicrm_financial_item
802 (transaction_date, contact_id, amount, currency, entity_table, entity_id, description, status_id, financial_account_id, f_trxn_id)
803 SELECT ft.trxn_date, {$domainContactId} as contact_id, ft.total_amount, ft.currency, 'civicrm_financial_trxn', ft.id,
804 'Fee', {$paidStatus} as status_id, ft.to_financial_account_id as financial_account_id, ft.id as f_trxn_id
805 FROM civicrm_financial_trxn ft
806 WHERE ft.is_fee = 1;";
807 CRM_Core_DAO
::executeQuery($sql);
809 //add entries to entity_financial_trxn table
811 INSERT INTO civicrm_entity_financial_trxn (entity_table, entity_id, financial_trxn_id, amount)
812 SELECT 'civicrm_financial_item' as entity_table, fi.id as entity_id, fi.f_trxn_id as financial_trxn_id, fi.amount
813 FROM civicrm_financial_item fi";
814 CRM_Core_DAO
::executeQuery($sql);
816 //drop the temparory columns
817 $sql = "ALTER TABLE civicrm_financial_trxn
818 DROP COLUMN contribution_id,
819 DROP COLUMN is_fee;";
820 CRM_Core_DAO
::executeQuery($sql);
822 $sql = "ALTER TABLE civicrm_financial_item DROP f_trxn_id";
823 CRM_Core_DAO
::executeQuery($sql);
831 public function createDomainContacts() {
832 $domainParams = $context = array();
834 ALTER TABLE civicrm_domain ADD contact_id INT( 10 ) UNSIGNED NULL DEFAULT NULL COMMENT 'FK to Contact ID. This is specifically not an FK to avoid circular constraints',
835 ADD CONSTRAINT FK_civicrm_domain_contact_id FOREIGN KEY (contact_id) REFERENCES civicrm_contact(id);";
836 CRM_Core_DAO
::executeQuery($query, CRM_Core_DAO
::$_nullArray, TRUE, NULL, FALSE, FALSE);
839 SELECT cd.id, cd.name, ce.email FROM civicrm_domain cd
840 LEFT JOIN civicrm_loc_block clb ON clb.id = cd. loc_block_id
841 LEFT JOIN civicrm_email ce ON ce.id = clb.email_id ;
843 $dao = CRM_Core_DAO
::executeQuery($query);
844 while ($dao->fetch()) {
846 SELECT cc.id FROM civicrm_contact cc
847 LEFT JOIN civicrm_email ce ON ce.contact_id = cc.id
848 WHERE cc.contact_type = 'Organization' AND cc.organization_name = %1
850 $params = array(1 => array($dao->name
, 'String'));
852 $query .= " AND ce.email = %2 ";
853 $params[2] = array($dao->email
, 'String');
855 $contactID = CRM_Core_DAO
::singleValueQuery($query, $params);
856 $context[1] = $dao->name
;
857 if (empty($contactID)) {
859 'sort_name' => $dao->name
,
860 'display_name' => $dao->name
,
861 'legal_name' => $dao->name
,
862 'organization_name' => $dao->name
,
863 'contact_type' => 'Organization',
865 $contact = CRM_Contact_BAO_Contact
::add($params);
866 $contactID = $contact->id
;
867 $context[0] = 'added';
870 $context[0] = 'merged';
872 $domainParams['contact_id'] = $contactID;
873 CRM_Core_BAO_Domain
::edit($domainParams, $dao->id
);
878 public function task_4_3_alpha1_checkDBConstraints() {
879 //checking whether the foreign key exists before dropping it CRM-11260
880 $config = CRM_Core_Config
::singleton();
881 $dbUf = DB
::parseDSN($config->dsn
);
883 'autorenewal_msg_id' => array(
884 'tableName' => 'civicrm_membership_type',
885 'fkey' => 'FK_civicrm_membership_autorenewal_msg_id',
887 'to_account_id' => array(
888 'tableName' => 'civicrm_financial_trxn',
889 'constraintName' => 'civicrm_financial_trxn_ibfk_2',
891 'from_account_id' => array(
892 'tableName' => 'civicrm_financial_trxn',
893 'constraintName' => 'civicrm_financial_trxn_ibfk_1',
895 'contribution_type_id' => array(
896 'tableName' => 'civicrm_contribution_recur',
897 'fkey' => 'FK_civicrm_contribution_recur_contribution_type_id',
901 SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
902 WHERE table_name = 'civicrm_contribution_recur'
903 AND constraint_name = 'FK_civicrm_contribution_recur_contribution_type_id'
904 AND TABLE_SCHEMA = %1
906 $params = array(1 => array($dbUf['database'], 'String'));
907 $dao = CRM_Core_DAO
::executeQuery($query, $params, TRUE, NULL, FALSE, FALSE);
908 foreach ($tables as $columnName => $value) {
909 if ($value['tableName'] == 'civicrm_membership_type' ||
$value['tableName'] == 'civicrm_contribution_recur') {
910 $foreignKeyExists = CRM_Core_DAO
::checkConstraintExists($value['tableName'], $value['fkey']);
911 $fKey = $value['fkey'];
914 $foreignKeyExists = CRM_Core_DAO
::checkFKConstraintInFormat($value['tableName'], $columnName);
915 $fKey = "`FK_{$value['tableName']}_{$columnName}`";
917 if ($foreignKeyExists ||
$value['tableName'] == 'civicrm_financial_trxn') {
918 if ($value['tableName'] != 'civicrm_contribution_recur' ||
($value['tableName'] == 'civicrm_contribution_recur' && $dao->N
)) {
919 $constraintName = $foreignKeyExists ?
$fKey : $value['constraintName'];
920 $query = "ALTER TABLE {$value['tableName']} DROP FOREIGN KEY {$constraintName}";
921 CRM_Core_DAO
::executeQuery($query, $params, TRUE, NULL, FALSE, FALSE);
923 $query = "ALTER TABLE {$value['tableName']} DROP INDEX {$fKey}";
924 CRM_Core_DAO
::executeQuery($query, $params, TRUE, NULL, FALSE, FALSE);
927 // check if column contact_id is present or not in civicrm_financial_account
928 $fieldExists = CRM_Core_BAO_SchemaHandler
::checkIfFieldExists('civicrm_financial_account', 'contact_id', FALSE);
931 ALTER TABLE civicrm_financial_account
932 ADD contact_id int(10) unsigned DEFAULT NULL COMMENT 'Version identifier of financial_type' AFTER name,
933 ADD CONSTRAINT FK_civicrm_financial_account_contact_id FOREIGN KEY (contact_id) REFERENCES civicrm_contact(id);
935 CRM_Core_DAO
::executeQuery($query, $params, TRUE, NULL, FALSE, FALSE);
940 * Read creation and modification times from civicrm_log; add them to civicrm_contact.
942 * @param \CRM_Queue_TaskContext $ctx
943 * @param int $startId
948 public function convertTimestamps(CRM_Queue_TaskContext
$ctx, $startId, $endId) {
950 SELECT entity_id, min(modified_date) AS created, max(modified_date) AS modified
952 WHERE entity_table = 'civicrm_contact'
953 AND entity_id BETWEEN %1 AND %2
957 1 => array($startId, 'Integer'),
958 2 => array($endId, 'Integer'),
960 $dao = CRM_Core_DAO
::executeQuery($sql, $params);
961 while ($dao->fetch()) {
962 // FIXME civicrm_log.modified_date is DATETIME; civicrm_contact.modified_date is TIMESTAMP
963 CRM_Core_DAO
::executeQuery(
964 'UPDATE civicrm_contact SET created_date = FROM_UNIXTIME(UNIX_TIMESTAMP(%1)), modified_date = FROM_UNIXTIME(UNIX_TIMESTAMP(%2)) WHERE id = %3',
966 1 => array($dao->created
, 'String'),
967 2 => array($dao->modified
, 'String'),
968 3 => array($dao->entity_id
, 'Integer'),
977 * Change index and add missing constraints for civicrm_contribution_recur.
979 * @param \CRM_Queue_TaskContext $ctx
983 public function addMissingConstraints(CRM_Queue_TaskContext
$ctx) {
984 $query = "SHOW KEYS FROM `civicrm_contribution_recur` WHERE key_name = 'UI_contrib_payment_instrument_id'";
985 $dao = CRM_Core_DAO
::executeQuery($query);
987 CRM_Core_DAO
::executeQuery('ALTER TABLE civicrm_contribution_recur DROP INDEX UI_contrib_payment_instrument_id');
988 CRM_Core_DAO
::executeQuery('ALTER TABLE civicrm_contribution_recur ADD INDEX UI_contribution_recur_payment_instrument_id (payment_instrument_id)');
990 $constraintArray = array(
991 'contact_id' => " ADD CONSTRAINT `FK_civicrm_contribution_recur_contact_id` FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE ",
992 'payment_processor_id' => " ADD CONSTRAINT `FK_civicrm_contribution_recur_payment_processor_id` FOREIGN KEY (`payment_processor_id`) REFERENCES `civicrm_payment_processor` (`id`) ON DELETE SET NULL ",
993 'financial_type_id' => " ADD CONSTRAINT `FK_civicrm_contribution_recur_financial_type_id` FOREIGN KEY (`financial_type_id`) REFERENCES `civicrm_financial_type` (`id`) ON DELETE SET NULL ",
994 'campaign_id' => " ADD CONSTRAINT `FK_civicrm_contribution_recur_campaign_id` FOREIGN KEY (`campaign_id`) REFERENCES `civicrm_campaign` (`id`) ON DELETE SET NULL ",
996 $constraint = array();
997 foreach ($constraintArray as $constraintKey => $value) {
998 $foreignKeyExists = CRM_Core_DAO
::checkFKConstraintInFormat('civicrm_contribution_recur', $constraintKey);
999 if (!$foreignKeyExists) {
1000 $constraint[] = $value;
1003 if (!empty($constraint)) {
1004 $query = "ALTER TABLE civicrm_contribution_recur " . implode(' , ', $constraint);
1005 CRM_Core_DAO
::executeQuery($query);
1011 * Update financial_account_id for bad data in financial_trxn table.
1014 * @param \CRM_Queue_TaskContext $ctx
1018 public function updateFinancialTrxnData(CRM_Queue_TaskContext
$ctx) {
1019 $upgrade = new CRM_Upgrade_Form();
1020 $sql = "SELECT cc.id contribution_id, cc.contribution_recur_id, cft.payment_processor_id,
1021 cft.id financial_trxn_id, cfi.entity_table, cft.from_financial_account_id, cft.to_financial_account_id
1023 FROM `civicrm_contribution` cc
1024 LEFT JOIN civicrm_entity_financial_trxn ceft ON ceft.entity_id = cc.id
1025 LEFT JOIN civicrm_financial_trxn cft ON cft.id = ceft.financial_trxn_id
1026 LEFT JOIN civicrm_entity_financial_trxn ceft1 ON ceft1.financial_trxn_id = ceft.financial_trxn_id
1027 LEFT JOIN civicrm_financial_item cfi ON cfi.id = ceft1.entity_id
1028 WHERE ceft.entity_table = 'civicrm_contribution' AND cc.contribution_recur_id IS NOT NULL
1029 AND ceft1.entity_table = 'civicrm_financial_item' AND cft.id IS NOT NULL AND cft.payment_instrument_id = %1
1032 $paymentInstrument = CRM_Contribute_PseudoConstant
::paymentInstrument('name');
1033 $param = array(1 => array(array_search('Credit Card', $paymentInstrument), 'Integer'));
1034 $dao = CRM_Core_DAO
::executeQuery($sql, $param);
1035 $financialTrxn = array();
1036 $subsequentPayments = array();
1037 while ($dao->fetch()) {
1038 if (!array_key_exists($dao->contribution_recur_id
, $financialTrxn)) {
1039 $financialTrxn[$dao->contribution_recur_id
] = array(
1040 'from_financial_account_id' => $dao->to_financial_account_id
,
1041 'payment_processor_id' => $dao->payment_processor_id
,
1042 $dao->contribution_id
=> 1,
1044 if (!is_null($dao->from_financial_account_id
)) {
1045 $sql = 'UPDATE civicrm_financial_trxn SET from_financial_account_id = NULL WHERE id = %1';
1046 $params = array(1 => array($dao->financial_trxn_id
, 'Integer'));
1047 CRM_Core_DAO
::executeQuery($sql, $params);
1050 elseif (!array_key_exists($dao->contribution_id
, $financialTrxn[$dao->contribution_recur_id
])) {
1051 if (($dao->entity_table
== 'civicrm_line_item' && $dao->to_financial_account_id
== $financialTrxn[$dao->contribution_recur_id
]['from_financial_account_id'])
1052 ||
($dao->entity_table
== 'civicrm_financial_trxn' && $dao->from_financial_account_id
== $financialTrxn[$dao->contribution_recur_id
]['from_financial_account_id'])
1056 $subsequentPayments[$dao->contribution_recur_id
][$dao->entity_table
][] = $dao->financial_trxn_id
;
1059 foreach ($subsequentPayments as $key => $value) {
1060 foreach ($value as $table => $val) {
1061 if ($table == 'civicrm_financial_trxn') {
1062 $field = 'from_financial_account_id';
1065 $field = 'to_financial_account_id';
1067 $sql = "UPDATE civicrm_financial_trxn SET $field = " . $financialTrxn[$dao->contribution_recur_id
]['from_financial_account_id'] . ',
1068 payment_processor_id = ' . $financialTrxn[$dao->contribution_recur_id
]['payment_processor_id'] . ' WHERE
1069 id IN (' . implode(',', $val) . ')';
1070 CRM_Core_DAO
::executeQuery($sql);
1077 * Update financial_account_id for bad data in financial_trxn table.
1080 * @param \CRM_Queue_TaskContext $ctx
1084 public function updateLineItemData(CRM_Queue_TaskContext
$ctx) {
1085 $sql = "SELECT cc.id contribution_id, cc.contribution_recur_id,
1086 cc.financial_type_id contribution_financial_type,
1087 cli.financial_type_id line_financial_type_id,
1088 cli.price_field_id, cli.price_field_value_id, cli.label, cli.id line_item_id,
1089 cfi.financial_account_id
1090 FROM `civicrm_line_item` cli
1091 LEFT JOIN civicrm_contribution cc ON cc.id = cli.entity_id
1092 LEFT JOIN civicrm_financial_item cfi ON cfi.entity_id = cli.id
1093 LEFT JOIN civicrm_price_field cpf ON cpf.id = cli.price_field_id
1094 LEFT JOIN civicrm_price_set cps ON cps.id = cpf.price_set_id
1095 LEFT JOIN civicrm_price_field_value cpfv ON cpfv.id = cli.price_field_value_id
1096 WHERE cfi.entity_table = 'civicrm_line_item'
1097 AND cli.entity_table = 'civicrm_contribution'
1098 AND cps.is_quick_config = 1 AND cc.contribution_recur_id IS NOT NULL
1100 $dao = CRM_Core_DAO
::executeQuery($sql);
1101 $financialTrxn = $subsequentPayments = array();
1102 while ($dao->fetch()) {
1103 if (!array_key_exists($dao->contribution_recur_id
, $financialTrxn)) {
1104 $financialTrxn[$dao->contribution_recur_id
] = array(
1105 'price_field_id' => $dao->price_field_id
,
1106 'price_field_value_id' => $dao->price_field_value_id
,
1107 'label' => strval($dao->label
),
1108 'financial_account_id' => $dao->financial_account_id
,
1109 $dao->contribution_id
=> 1,
1113 if ($dao->price_field_value_id
== $financialTrxn[$dao->contribution_recur_id
]['price_field_value_id']) {
1116 $subsequentPayments[$dao->contribution_recur_id
][] = $dao->line_item_id
;
1119 foreach ($subsequentPayments as $key => $value) {
1120 $sql = "UPDATE civicrm_line_item cli
1121 LEFT JOIN civicrm_financial_item cfi ON cli.id = cfi.entity_id
1124 cli.price_field_id = %2,
1125 cli.price_field_value_id = %3,
1126 cfi.financial_account_id = %4,
1127 cfi.description = %5,
1128 cli.financial_type_id = %6
1129 WHERE cfi.entity_table = 'civicrm_line_item'
1130 AND cli.entity_table = 'civicrm_contribution' AND cli.id IN (" . implode(',', $value) . ');';
1132 1 => array($financialTrxn[$key]['label'], 'String'),
1133 2 => array($financialTrxn[$key]['price_field_id'], 'Integer'),
1134 3 => array($financialTrxn[$key]['price_field_value_id'], 'Integer'),
1135 4 => array($financialTrxn[$key]['financial_account_id'], 'Integer'),
1136 5 => array($financialTrxn[$key]['label'], 'String'),
1137 6 => array($dao->contribution_financial_type
, 'Integer'),
1139 CRM_Core_DAO
::executeQuery($sql, $params);
1145 * Replace contribution_type to financial_type in table.
1147 * Civicrm_saved_search and Structure civicrm_report_instance
1149 * @param \CRM_Queue_TaskContext $ctx
1150 * @param string $query
1151 * @param string $table
1155 public function replaceContributionTypeId(CRM_Queue_TaskContext
$ctx, $query, $table) {
1156 $dao = CRM_Core_DAO
::executeQuery($query);
1157 while ($dao->fetch()) {
1158 $formValues = unserialize($dao->form_values
);
1159 foreach (array('contribution_type_id_op', 'contribution_type_id_value', 'contribution_type_id') as $value) {
1160 if (array_key_exists($value, $formValues)) {
1161 $key = preg_replace('/contribution/', 'financial', $value);
1162 $formValues[$key] = $formValues[$value];
1163 unset($formValues[$value]);
1166 if ($table != 'savedSearch') {
1167 foreach (array('fields', 'group_bys') as $value) {
1168 if (array_key_exists($value, $formValues)) {
1169 if (array_key_exists('contribution_type_id', $formValues[$value])) {
1170 $formValues[$value]['financial_type_id'] = $formValues[$value]['contribution_type_id'];
1171 unset($formValues[$value]['contribution_type_id']);
1173 elseif (array_key_exists('contribution_type', $formValues[$value])) {
1174 $formValues[$value]['financial_type'] = $formValues[$value]['contribution_type'];
1175 unset($formValues[$value]['contribution_type']);
1179 if (array_key_exists('order_bys', $formValues)) {
1180 foreach ($formValues['order_bys'] as $key => $values) {
1181 if (preg_grep('/contribution_type/', $values)) {
1182 $formValues['order_bys'][$key]['column'] = preg_replace('/contribution_type/', 'financial_type', $values['column']);
1188 if ($table == 'savedSearch') {
1189 $saveDao = new CRM_Contact_DAO_SavedSearch();
1192 $saveDao = new CRM_Report_DAO_ReportInstance();
1194 $saveDao->id
= $dao->id
;
1196 if ($table == 'savedSearch') {
1197 if (array_key_exists('mapper', $formValues)) {
1198 foreach ($formValues['mapper'] as $key => $values) {
1199 foreach ($values as $k => $v) {
1200 if (preg_grep('/contribution_/', $v)) {
1201 $formValues['mapper'][$key][$k] = preg_replace('/contribution_type/', 'financial_type', $v);
1206 foreach (array('select_tables', 'where_tables') as $value) {
1207 if (preg_match('/contribution_type/', $dao->$value)) {
1208 $tempValue = unserialize($dao->$value);
1209 if (array_key_exists('civicrm_contribution_type', $tempValue)) {
1210 $tempValue['civicrm_financial_type'] = $tempValue['civicrm_contribution_type'];
1211 unset($tempValue['civicrm_contribution_type']);
1213 $saveDao->$value = serialize($tempValue);
1216 if (preg_match('/contribution_type/', $dao->where_clause
)) {
1217 $saveDao->where_clause
= preg_replace('/contribution_type/', 'financial_type', $dao->where_clause
);
1220 $saveDao->form_values
= serialize($formValues);
1228 * Add ON DELETE options for constraint if not present.
1229 * CRM-13088 && CRM-12156
1231 * @param CRM_Queue_TaskContext $ctx
1236 public function task_4_3_x_checkConstraints(CRM_Queue_TaskContext
$ctx) {
1237 CRM_Core_DAO
::executeQuery('ALTER TABLE `civicrm_financial_account` CHANGE `contact_id` `contact_id` INT( 10 ) UNSIGNED NULL DEFAULT NULL');
1238 $config = CRM_Core_Config
::singleton();
1239 $dbname = DB
::parseDSN($config->dsn
);
1240 $constraintArray = array(
1241 "'FK_civicrm_financial_account_contact_id'",
1242 "'FK_civicrm_financial_item_contact_id'",
1243 "'FK_civicrm_contribution_recur_financial_type_id'",
1244 "'FK_civicrm_line_item_financial_type_id'",
1245 "'FK_civicrm_product_financial_type_id'",
1246 "'FK_civicrm_premiums_product_financial_type_id'",
1247 "'FK_civicrm_price_field_value_financial_type_id'",
1248 "'FK_civicrm_contribution_product_financial_type_id'",
1249 "'FK_civicrm_price_set_financial_type_id'",
1250 "'FK_civicrm_grant_financial_type_id'",
1253 $sql = "SELECT DELETE_RULE, TABLE_NAME, CONSTRAINT_NAME
1254 FROM information_schema.REFERENTIAL_CONSTRAINTS
1255 WHERE CONSTRAINT_NAME IN (" . implode(',', $constraintArray) . ")
1256 AND CONSTRAINT_SCHEMA = %1";
1257 $params = array(1 => array($dbname['database'], 'String'));
1258 $onDelete = CRM_Core_DAO
::executeQuery($sql, $params, TRUE, FALSE);
1259 while ($onDelete->fetch()) {
1260 if (($onDelete->TABLE_NAME
!= 'civicrm_financial_item' && $onDelete->DELETE_RULE
!= 'SET NULL') ||
1261 ($onDelete->TABLE_NAME
== 'civicrm_financial_item' && $onDelete->DELETE_RULE
!= 'CASCADE')
1263 $tableName = 'civicrm_financial_type';
1264 $onDeleteOption = ' SET NULL ';
1265 $columnName = 'financial_type_id';
1266 if (preg_match('/contact_id/', $onDelete->CONSTRAINT_NAME
)) {
1267 $tableName = 'civicrm_contact';
1268 $columnName = 'contact_id';
1269 if ($onDelete->TABLE_NAME
== 'civicrm_financial_item') {
1270 $onDeleteOption = 'CASCADE';
1277 $query = "ALTER TABLE {$onDelete->TABLE_NAME}
1278 DROP FOREIGN KEY {$onDelete->CONSTRAINT_NAME},
1279 DROP INDEX {$onDelete->CONSTRAINT_NAME};";
1280 CRM_Core_DAO
::executeQuery($query, array(), TRUE, NULL, FALSE, FALSE);
1281 $query = " ALTER TABLE {$onDelete->TABLE_NAME}
1282 ADD CONSTRAINT {$onDelete->CONSTRAINT_NAME} FOREIGN KEY (`" . $columnName . "`) REFERENCES {$tableName} (`id`) ON DELETE {$onDeleteOption};
1284 CRM_Core_DAO
::executeQuery($query, array(), TRUE, NULL, FALSE, FALSE);
1290 * Check/Add INDEX CRM-12141
1292 * @param CRM_Queue_TaskContext $ctx
1297 public function task_4_3_x_checkIndexes(CRM_Queue_TaskContext
$ctx) {
1300 FROM civicrm_entity_financial_trxn
1301 WHERE key_name IN ('UI_entity_financial_trxn_entity_table', 'UI_entity_financial_trxn_entity_id')
1303 $dao = CRM_Core_DAO
::executeQuery($query);
1306 ALTER TABLE civicrm_entity_financial_trxn
1307 ADD INDEX UI_entity_financial_trxn_entity_table (entity_table),
1308 ADD INDEX UI_entity_financial_trxn_entity_id (entity_id);
1310 CRM_Core_DAO
::executeQuery($query);
1316 * Update phones CRM-11292
1318 * @param CRM_Queue_TaskContext $ctx
1323 public static function phoneNumeric(CRM_Queue_TaskContext
$ctx) {
1324 CRM_Core_DAO
::executeQuery(CRM_Contact_BAO_Contact
::DROP_STRIP_FUNCTION_43
);
1325 CRM_Core_DAO
::executeQuery(CRM_Contact_BAO_Contact
::CREATE_STRIP_FUNCTION_43
);
1326 CRM_Core_DAO
::executeQuery("UPDATE civicrm_phone SET phone_numeric = civicrm_strip_non_numeric(phone)");