3 +--------------------------------------------------------------------+
4 | CiviCRM version 4.6 |
5 +--------------------------------------------------------------------+
6 | Copyright CiviCRM LLC (c) 2004-2014 |
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. |
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 along with this program; if not, contact CiviCRM LLC |
21 | at info[AT]civicrm[DOT]org. If you have questions about the |
22 | GNU Affero General Public License or the licensing of CiviCRM, |
23 | see the CiviCRM license FAQ at http://civicrm.org/licensing |
24 +--------------------------------------------------------------------+
30 * @copyright CiviCRM LLC (c) 2004-2014
34 class CRM_Upgrade_Incremental_php_FourThree
{
35 const BATCH_SIZE
= 5000;
42 public function verifyPreDBstate(&$errors) {
47 * Compute any messages which should be displayed beforeupgrade
49 * Note: This function is called iteratively for each upcoming
50 * revision to the database.
52 * @param $preUpgradeMessage
54 * a version number, e.g. '4.3.alpha1', '4.3.beta3', '4.3.0'.
55 * @param null $currentVer
59 public function setPreUpgradeMessage(&$preUpgradeMessage, $rev, $currentVer = NULL) {
60 if ($rev == '4.3.beta3') {
62 //sql for checking orphaned contribution records
63 $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";
64 $count = CRM_Core_DAO
::singleValueQuery($sql, array(), TRUE, FALSE);
67 $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(
69 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>',
71 CRM_Core_Error
::fatal($error);
75 if ($rev == '4.3.beta4' && CRM_Utils_Constant
::value('CIVICRM_UF', FALSE) == 'Drupal6') {
76 // CRM-11823 - Make sure the D6 HTML HEAD technique will work on
77 // upgrade pages ... except when we're in Drush.
78 if (!function_exists('drush_main')) {
79 theme('item_list', array()); // force-load theme registry
80 $theme_registry = theme_get_registry();
81 if (!isset($theme_registry['page']['preprocess functions']) ||
FALSE === array_search('civicrm_preprocess_page_inject', $theme_registry['page']['preprocess functions'])) {
82 CRM_Core_Error
::fatal('Please reset the Drupal cache (Administer => Site Configuration => Performance => Clear cached data))');
87 if ($rev == '4.3.6') {
88 $constraintArray = array(
89 'civicrm_contact' => 'contact_id',
90 'civicrm_payment_processor' => 'payment_processor_id',
93 if (version_compare('4.1alpha1', $currentVer) <= 0) {
94 $constraintArray['civicrm_campaign'] = 'campaign_id';
97 if (version_compare('4.3alpha1', $currentVer) <= 0) {
98 $constraintArray['civicrm_financial_type'] = 'financial_type_id';
101 foreach ($constraintArray as $key => $value) {
102 $query = "SELECT contri_recur.id FROM civicrm_contribution_recur contri_recur LEFT JOIN {$key} ON contri_recur.{$value} = {$key}.id
103 WHERE {$key}.id IS NULL";
104 if ($value != 'contact_id') {
105 $query .= " AND contri_recur.{$value} IS NOT NULL ";
107 $dao = CRM_Core_DAO
::executeQuery($query);
109 $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>';
110 CRM_Core_Error
::fatal($invalidDataMessage);
118 * Compute any messages which should be displayed after upgrade
120 * @param string $postUpgradeMessage
123 * an intermediate version; note that setPostUpgradeMessage is called repeatedly with different $revs.
126 public function setPostUpgradeMessage(&$postUpgradeMessage, $rev) {
127 if ($rev == '4.3.alpha1') {
128 // check if CiviMember component is enabled
129 $config = CRM_Core_Config
::singleton();
130 if (in_array('CiviMember', $config->enableComponents
)) {
131 $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.');
132 $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.');
136 //here we do the financial type check and migration
137 $isDefaultsModified = self
::_checkAndMigrateDefaultFinancialTypes();
138 if ($isDefaultsModified) {
139 $postUpgradeMessage .= '<br />' . ts('Please review all price set financial type assignments.');
141 list($context, $orgName) = self
::createDomainContacts();
142 if ($context == 'added') {
143 $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));
145 elseif ($context == 'merged') {
146 $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));
149 $providerExists = CRM_Core_DAO
::singleValueQuery("SELECT id FROM civicrm_sms_provider LIMIT 1");
150 if ($providerExists) {
151 $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'));
155 if ($rev == '4.3.alpha2') {
158 FROM civicrm_action_schedule
159 WHERE entity_value = '' OR entity_value IS NULL
162 $dao = CRM_Core_DAO
::executeQuery($sql);
165 while ($dao->fetch()) {
166 $reminder[$dao->id
] = $dao->title
;
167 $list .= "<li>{$dao->title}</li>";
169 if (!empty($reminder)) {
170 $list = "<br /><ul>" . $list . "</ul>";
171 $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));
174 if ($rev == '4.3.beta2') {
175 $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).');
178 if ($rev == '4.3.beta5') {
179 $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.");
183 SELECT ceft.id FROM `civicrm_financial_trxn` cft
184 LEFT JOIN civicrm_entity_financial_trxn ceft
185 ON ceft.financial_trxn_id = cft.id AND ceft.entity_table = 'civicrm_contribution'
186 LEFT JOIN civicrm_contribution cc
187 ON cc.id = ceft.entity_id AND ceft.entity_table = 'civicrm_contribution'
191 $dao = CRM_Core_DAO
::executeQuery($query);
192 $isOrphanData = TRUE;
195 SELECT cli.id FROM civicrm_line_item cli
196 LEFT JOIN civicrm_contribution cc ON cli.entity_id = cc.id AND cli.entity_table = 'civicrm_contribution'
197 LEFT JOIN civicrm_participant cp ON cli.entity_id = cp.id AND cli.entity_table = 'civicrm_participant'
198 WHERE CASE WHEN cli.entity_table = 'civicrm_contribution'
203 $dao = CRM_Core_DAO
::executeQuery($query);
205 $revPattern = '/^((\d{1,2})\.\d{1,2})\.(\d{1,2}|\w{4,7})?$/i';
206 preg_match($revPattern, $currentVer, $version);
207 if ($version[1] >= 4.3) {
210 FROM civicrm_financial_item cfi
211 LEFT JOIN civicrm_entity_financial_trxn ceft ON ceft.entity_table = 'civicrm_financial_item' and cfi.id = ceft.entity_id
212 WHERE ceft.entity_id IS NULL;
214 $dao = CRM_Core_DAO
::executeQuery($query);
216 $isOrphanData = FALSE;
220 $isOrphanData = FALSE;
226 $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>.
227 ', array(1 => 'http://wiki.civicrm.org/confluence/display/CRMDOC/Clean+up+extraneous+financial+data+-+4.3+upgrades')) . "</strong>";
230 if ($rev == '4.3.4') {
231 $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>).',
232 array(1 => 'http://wiki.civicrm.org/confluence/display/CRMDOC/Managing+Scheduled+Jobs'));
234 if ($rev == '4.3.5') {
235 $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).');
237 if ($rev == '4.3.6') {
238 $flag = CRM_Core_DAO
::singleValueQuery('SELECT count(ccp.id) FROM civicrm_contribution_product ccp
239 INNER JOIN civicrm_product cp ON ccp.product_id = cp.id
240 WHERE ccp.financial_type_id IS NULL and cp.cost > 0');
242 $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>',
243 array(1 => 'http://wiki.civicrm.org/confluence/display/CRMDOC/Fixing+Issues+Caused+by+Missing+Cost+of+Goods+Account+-+4.3+Upgrades'));
253 public function upgrade_4_3_alpha1($rev) {
254 self
::task_4_3_alpha1_checkDBConstraints();
256 // add indexes for civicrm_entity_financial_trxn
258 $this->addTask('Check/Add indexes for civicrm_entity_financial_trxn', 'task_4_3_x_checkIndexes', $rev);
259 // task to process sql
260 $this->addTask(ts('Upgrade DB to %1: SQL', array(1 => '4.3.alpha1')), 'task_4_3_x_runSql', $rev);
263 $this->addTask('Populate financial type values for price records', 'assignFinancialTypeToPriceRecords');
264 //CRM-11514 create financial records for contributions
265 $this->addTask('Create financial records for contributions', 'createFinancialRecords');
267 $minId = CRM_Core_DAO
::singleValueQuery('SELECT coalesce(min(id),0) FROM civicrm_contact');
268 $maxId = CRM_Core_DAO
::singleValueQuery('SELECT coalesce(max(id),0) FROM civicrm_contact');
269 for ($startId = $minId; $startId <= $maxId; $startId +
= self
::BATCH_SIZE
) {
270 $endId = $startId + self
::BATCH_SIZE
- 1;
271 $title = ts('Upgrade timestamps (%1 => %2)', array(1 => $startId, 2 => $endId));
272 $this->addTask($title, 'convertTimestamps', $startId, $endId);
276 // fix WP access control
277 $config = CRM_Core_Config
::singleton();
278 if ($config->userFramework
== 'WordPress') {
279 civicrm_wp_set_capabilities();
282 // Update phones CRM-11292.
283 $this->addTask('Upgrade Phone Numbers', 'phoneNumeric');
291 public function upgrade_4_3_alpha2($rev) {
293 $isColumnPresent = CRM_Core_DAO
::checkFieldExists('civicrm_dedupe_rule_group', 'is_default');
294 if ($isColumnPresent) {
295 CRM_Core_DAO
::executeQuery('ALTER TABLE civicrm_dedupe_rule_group DROP COLUMN is_default');
297 $this->addTask(ts('Upgrade DB to %1: SQL', array(1 => '4.3.alpha2')), 'task_4_3_x_runSql', $rev);
303 public function upgrade_4_3_alpha3($rev) {
304 $this->addTask(ts('Upgrade DB to %1: SQL', array(1 => '4.3.alpha3')), 'task_4_3_x_runSql', $rev);
310 public function upgrade_4_3_beta2($rev) {
311 $this->addTask(ts('Upgrade DB to %1: SQL', array(1 => '4.3.beta2')), 'task_4_3_x_runSql', $rev);
315 CRM_Core_DAO
::checkTableExists('log_civicrm_line_item') &&
316 CRM_Core_DAO
::checkFieldExists('log_civicrm_line_item', 'label')
318 CRM_Core_DAO
::executeQuery('ALTER TABLE `log_civicrm_line_item` CHANGE `label` `label` VARCHAR(255) NULL DEFAULT NULL');
325 public function upgrade_4_3_beta3($rev) {
326 $this->addTask(ts('Upgrade DB to %1: SQL', array(1 => '4.3.beta3')), 'task_4_3_x_runSql', $rev);
328 $query = "SELECT id, form_values FROM civicrm_report_instance WHERE form_values LIKE '%contribution_type%'";
329 $this->addTask('Replace contribution_type to financial_type in table civicrm_report_instance', 'replaceContributionTypeId', $query, 'reportInstance');
330 $query = "SELECT * FROM civicrm_saved_search WHERE form_values LIKE '%contribution_type%'";
331 $this->addTask('Replace contribution_type to financial_type in table civicrm_saved_search', 'replaceContributionTypeId', $query, 'savedSearch');
337 public function upgrade_4_3_beta4($rev) {
338 $this->addTask(ts('Upgrade DB to %1: SQL', array(1 => '4.3.beta4')), 'task_4_3_x_runSql', $rev);
339 // add indexes for civicrm_entity_financial_trxn
341 $this->addTask('Check/Add indexes for civicrm_entity_financial_trxn', 'task_4_3_x_checkIndexes', $rev);
347 public function upgrade_4_3_beta5($rev) {
350 CRM_Core_DAO
::checkTableExists('log_civicrm_financial_trxn') &&
351 CRM_Core_DAO
::checkFieldExists('log_civicrm_financial_trxn', 'trxn_id')
353 CRM_Core_DAO
::executeQuery('ALTER TABLE `log_civicrm_financial_trxn` CHANGE `trxn_id` `trxn_id` VARCHAR(255) NULL DEFAULT NULL');
355 // CRM-12142 - some sites didn't get this column added yet, and sites which installed 4.3 from scratch will already have it
356 // CRM-12367 - add this column to single lingual sites only
357 $upgrade = new CRM_Upgrade_Form();
358 if (!$upgrade->multilingual
&&
359 !CRM_Core_DAO
::checkFieldExists('civicrm_premiums', 'premiums_nothankyou_label')
362 ALTER TABLE civicrm_premiums
363 ADD COLUMN premiums_nothankyou_label varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL
364 COMMENT 'Label displayed for No Thank-you option in premiums block (e.g. No thank you)'
366 CRM_Core_DAO
::executeQuery($query, array(), TRUE, NULL, FALSE, FALSE);
368 $this->addTask(ts('Upgrade DB to %1: SQL', array(1 => '4.3.beta5')), 'task_4_3_x_runSql', $rev);
374 public function upgrade_4_3_4($rev) {
375 $this->addTask(ts('Upgrade DB to %1: SQL', array(1 => '4.3.4')), 'task_4_3_x_runSql', $rev);
381 public function upgrade_4_3_5($rev) {
383 $config = CRM_Core_Config
::singleton();
384 $dbname = DB
::parseDSN($config->dsn
);
385 $sql = "SELECT DELETE_RULE
386 FROM information_schema.REFERENTIAL_CONSTRAINTS
387 WHERE CONSTRAINT_NAME = 'FK_civicrm_financial_item_contact_id'
388 AND CONSTRAINT_SCHEMA = %1";
389 $params = array(1 => array($dbname['database'], 'String'));
390 $onDelete = CRM_Core_DAO
::singleValueQuery($sql, $params, TRUE, FALSE);
392 if ($onDelete != 'CASCADE') {
393 $query = "ALTER TABLE `civicrm_financial_item`
394 DROP FOREIGN KEY FK_civicrm_financial_item_contact_id,
395 DROP INDEX FK_civicrm_financial_item_contact_id;";
396 CRM_Core_DAO
::executeQuery($query, array(), TRUE, NULL, FALSE, FALSE);
398 ALTER TABLE `civicrm_financial_item`
399 ADD CONSTRAINT `FK_civicrm_financial_item_contact_id` FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE;
401 CRM_Core_DAO
::executeQuery($query, array(), TRUE, NULL, FALSE, FALSE);
403 $this->addTask(ts('Upgrade DB to %1: SQL', array(1 => '4.3.5')), 'task_4_3_x_runSql', $rev);
409 public function upgrade_4_3_6($rev) {
411 $this->addTask(ts('Add missing constraints'), 'addMissingConstraints', $rev);
413 $this->addTask('Add ON DELETE Options for constraints', 'task_4_3_x_checkConstraints', $rev);
414 $this->addTask(ts('Upgrade DB to %1: SQL', array(1 => '4.3.6')), 'task_4_3_x_runSql', $rev);
416 // update line_item, financial_trxn and financial_item table for recurring contributions
417 $this->addTask('Update financial_account_id in financial_trxn table', 'updateFinancialTrxnData', $rev);
418 $this->addTask('Update Line Item Data', 'updateLineItemData', $rev);
425 public function assignFinancialTypeToPriceRecords() {
426 $upgrade = new CRM_Upgrade_Form();
427 //here we update price set entries
429 SELECT id, LCASE(name) name
430 FROM civicrm_financial_type
431 WHERE name IN ('Donation', 'Event Fee', 'Member Dues');
433 $daoFinancialIds = CRM_Core_DAO
::executeQuery($sqlFinancialIds);
434 while ($daoFinancialIds->fetch()) {
435 $financialIds[$daoFinancialIds->name
] = $daoFinancialIds->id
;
437 $sqlPriceSetUpdate = "
438 UPDATE civicrm_price_set ps
439 SET ps.financial_type_id =
441 WHEN ps.extends LIKE '%1%' THEN {$financialIds['event fee']}
442 WHEN ps.extends LIKE '2' THEN {$financialIds['donation']}
443 WHEN ps.extends LIKE '3' THEN {$financialIds['member dues']}
445 WHERE financial_type_id IS NULL
447 CRM_Core_DAO
::executeQuery($sqlPriceSetUpdate);
449 //here we update price field value rows
450 $sqlPriceFieldValueUpdate = "
451 UPDATE civicrm_price_field_value pfv
452 LEFT JOIN civicrm_membership_type mt ON (pfv.membership_type_id = mt.id)
453 INNER JOIN civicrm_price_field pf ON (pfv.price_field_id = pf.id)
454 INNER JOIN civicrm_price_set ps ON (pf.price_set_id = ps.id)
455 SET pfv.financial_type_id =
457 WHEN pfv.membership_type_id IS NOT NULL THEN mt.financial_type_id
458 WHEN pfv.membership_type_id IS NULL THEN ps.financial_type_id
461 CRM_Core_DAO
::executeQuery($sqlPriceFieldValueUpdate);
469 public static function _checkAndMigrateDefaultFinancialTypes() {
470 $modifiedDefaults = FALSE;
471 //insert types if not exists
474 FROM civicrm_contribution_type
475 WHERE name IN ('Donation', 'Event Fee', 'Member Dues') AND is_active =1
477 $daoFetchTypes = CRM_Core_DAO
::executeQuery($sqlFetchTypes);
479 if ($daoFetchTypes->N
< 3) {
480 $modifiedDefaults = TRUE;
481 $insertStatments = array(
482 'Donation' => "('Donation', 0, 1, 1)",
483 'Member' => "('Member Dues', 0, 1, 1)",
484 'Event Fee' => "('Event Fee', 0, 1, 0)",
486 foreach ($insertStatments as $values) {
488 INSERT INTO civicrm_contribution_type (name, is_reserved, is_active, is_deductible)
490 ON DUPLICATE KEY UPDATE is_active = 1
492 CRM_Core_DAO
::executeQuery($query);
495 return $modifiedDefaults;
501 public function createFinancialRecords() {
502 $upgrade = new CRM_Upgrade_Form();
504 // update civicrm_entity_financial_trxn.amount = civicrm_financial_trxn.total_amount
506 UPDATE civicrm_entity_financial_trxn ceft
507 LEFT JOIN civicrm_financial_trxn cft ON cft.id = ceft.financial_trxn_id
508 SET ceft.amount = total_amount
509 WHERE cft.net_amount IS NOT NULL
510 AND ceft.entity_table = 'civicrm_contribution'
512 CRM_Core_DAO
::executeQuery($query);
514 $contributionStatus = CRM_Contribute_PseudoConstant
::contributionStatus(NULL, 'name');
515 $completedStatus = array_search('Completed', $contributionStatus);
516 $pendingStatus = array_search('Pending', $contributionStatus);
517 $cancelledStatus = array_search('Cancelled', $contributionStatus);
518 $queryParams = array(
519 1 => array($completedStatus, 'Integer'),
520 2 => array($pendingStatus, 'Integer'),
521 3 => array($cancelledStatus, 'Integer'),
524 $accountType = key(CRM_Core_PseudoConstant
::accountOptionValues('financial_account_type', NULL, " AND v.name = 'Asset' "));
527 FROM civicrm_financial_account
529 AND financial_account_type_id = {$accountType}
531 $financialAccountId = CRM_Core_DAO
::singleValueQuery($query);
533 $accountRelationsips = CRM_Core_PseudoConstant
::get('CRM_Financial_DAO_EntityFinancialAccount',
534 'account_relationship', CRM_Core_DAO
::$_nullArray, 'validate');
536 $accountsReceivableAccount = array_search('Accounts Receivable Account is', $accountRelationsips);
537 $incomeAccountIs = array_search('Income Account is', $accountRelationsips);
538 $assetAccountIs = array_search('Asset Account is', $accountRelationsips);
539 $expenseAccountIs = array_search('Expense Account is', $accountRelationsips);
541 $financialItemStatus = CRM_Core_PseudoConstant
::get('CRM_Financial_DAO_FinancialItem', 'status_id',
542 CRM_Core_DAO
::$_nullArray, 'validate');
543 $unpaidStatus = array_search('Unpaid', $financialItemStatus);
544 $paidStatus = array_search('Paid', $financialItemStatus);
546 $validCurrencyCodes = CRM_Core_PseudoConstant
::currencyCode();
547 $validCurrencyCodes = implode("','", $validCurrencyCodes);
548 $config = CRM_Core_Config
::singleton();
549 $defaultCurrency = $config->defaultCurrency
;
550 $now = date('YmdHis');
552 //adding financial_trxn records and entity_financial_trxn records related to contribution
553 //Add temp column for easy entry in entity_financial_trxn
554 $sql = "ALTER TABLE civicrm_financial_trxn ADD COLUMN contribution_id INT DEFAULT NULL";
555 CRM_Core_DAO
::executeQuery($sql);
557 //pending pay later status handling
559 INSERT INTO civicrm_financial_trxn
560 (contribution_id, payment_instrument_id, currency, total_amount, net_amount, fee_amount, trxn_id, status_id,
561 check_number, to_financial_account_id, from_financial_account_id, trxn_date)
562 SELECT con.id as contribution_id, con.payment_instrument_id,
563 IF(con.currency IN ('{$validCurrencyCodes}'), con.currency, '{$defaultCurrency}') as currency,
564 con.total_amount, con.net_amount, con.fee_amount, con.trxn_id, con.contribution_status_id,
565 con.check_number, efa.financial_account_id as to_financial_account_id, NULL as from_financial_account_id,
566 REPLACE(REPLACE(REPLACE(
568 WHEN con.receive_date IS NOT NULL THEN
570 WHEN con.receipt_date IS NOT NULL THEN
575 , '-', ''), ':', ''), ' ', '') as trxn_date
576 FROM civicrm_contribution con
577 LEFT JOIN civicrm_entity_financial_account efa
578 ON (con.financial_type_id = efa.entity_id AND efa.entity_table = 'civicrm_financial_type'
579 AND efa.account_relationship = {$accountsReceivableAccount})
580 WHERE con.is_pay_later = 1
581 AND con.contribution_status_id = {$pendingStatus}
583 CRM_Core_DAO
::executeQuery($sql);
585 //create a temp table to hold financial account id related to payment instruments
586 $tempTableName1 = CRM_Core_DAO
::createTempTableName();
589 CREATE TEMPORARY TABLE {$tempTableName1}
590 SELECT ceft.financial_account_id financial_account_id, cov.value as instrument_id
591 FROM civicrm_entity_financial_account ceft
592 INNER JOIN civicrm_option_value cov ON cov.id = ceft.entity_id AND ceft.entity_table = 'civicrm_option_value'
593 INNER JOIN civicrm_option_group cog ON cog.id = cov.option_group_id
594 WHERE cog.name = 'payment_instrument'
596 CRM_Core_DAO
::executeQuery($sql);
599 $sql = "ALTER TABLE {$tempTableName1} ADD INDEX index_instrument_id (instrument_id(200));";
600 CRM_Core_DAO
::executeQuery($sql);
602 //create temp table to process completed / cancelled contribution
603 $tempTableName2 = CRM_Core_DAO
::createTempTableName();
605 CREATE TEMPORARY TABLE {$tempTableName2}
606 SELECT con.id as contribution_id, con.payment_instrument_id,
607 IF(con.currency IN ('{$validCurrencyCodes}'), con.currency, '{$defaultCurrency}') as currency,
608 con.total_amount, con.net_amount, con.fee_amount, con.trxn_id, con.contribution_status_id,
609 con.check_number, NULL as from_financial_account_id,
610 REPLACE(REPLACE(REPLACE(
612 WHEN con.receive_date IS NOT NULL THEN
614 WHEN con.receipt_date IS NOT NULL THEN
619 , '-', ''), ':', ''), ' ', '') as trxn_date,
621 WHEN con.payment_instrument_id IS NULL THEN
622 {$financialAccountId}
623 WHEN con.payment_instrument_id IS NOT NULL THEN
624 tpi.financial_account_id
625 END as to_financial_account_id,
626 IF(eft.financial_trxn_id IS NULL, 'insert', eft.financial_trxn_id) as action
627 FROM civicrm_contribution con
628 LEFT JOIN civicrm_entity_financial_trxn eft
629 ON (eft.entity_table = 'civicrm_contribution' AND eft.entity_id = con.id)
630 LEFT JOIN {$tempTableName1} tpi
631 ON con.payment_instrument_id = tpi.instrument_id
632 WHERE con.contribution_status_id IN ({$completedStatus}, {$cancelledStatus})
634 CRM_Core_DAO
::executeQuery($sql);
637 $sql = "ALTER TABLE {$tempTableName2} ADD INDEX index_action (action);";
638 CRM_Core_DAO
::executeQuery($sql);
640 //handling for completed contribution and cancelled contribution
641 //insertion of new records
643 INSERT INTO civicrm_financial_trxn
644 (contribution_id, payment_instrument_id, currency, total_amount, net_amount, fee_amount, trxn_id, status_id, check_number,
645 to_financial_account_id, from_financial_account_id, trxn_date)
646 SELECT tempI.contribution_id, tempI.payment_instrument_id, tempI.currency, tempI.total_amount, tempI.net_amount,
647 tempI.fee_amount, tempI.trxn_id, tempI.contribution_status_id, tempI.check_number,
648 tempI.to_financial_account_id, tempI.from_financial_account_id, tempI.trxn_date
649 FROM {$tempTableName2} tempI
650 WHERE tempI.action = 'insert'
652 CRM_Core_DAO
::executeQuery($sql);
654 //update of existing records
656 UPDATE civicrm_financial_trxn ft
657 INNER JOIN {$tempTableName2} tempU
658 ON (tempU.action != 'insert' AND ft.id = tempU.action)
659 SET ft.from_financial_account_id = NULL,
660 ft.to_financial_account_id = tempU.to_financial_account_id,
661 ft.status_id = tempU.contribution_status_id,
662 ft.payment_instrument_id = tempU.payment_instrument_id,
663 ft.check_number = tempU.check_number,
664 ft.contribution_id = tempU.contribution_id;";
665 CRM_Core_DAO
::executeQuery($sql);
667 //insert the -ve transaction rows for cancelled contributions
669 INSERT INTO civicrm_financial_trxn
670 (contribution_id, payment_instrument_id, currency, total_amount, net_amount, fee_amount, trxn_id, status_id,
671 check_number, to_financial_account_id, from_financial_account_id, trxn_date)
672 SELECT ft.contribution_id, ft.payment_instrument_id, ft.currency, -ft.total_amount, ft.net_amount, ft.fee_amount, ft.trxn_id,
673 ft.status_id, ft.check_number, ft.to_financial_account_id, ft.from_financial_account_id, ft.trxn_date
674 FROM civicrm_financial_trxn ft
675 WHERE ft.status_id = {$cancelledStatus};";
676 CRM_Core_DAO
::executeQuery($sql);
678 //inserting entity financial trxn entries if its not present in entity_financial_trxn for completed and pending contribution statuses
679 //this also handles +ve and -ve both transaction entries for a cancelled contribution
681 INSERT INTO civicrm_entity_financial_trxn (entity_table, entity_id, financial_trxn_id, amount)
682 SELECT 'civicrm_contribution', ft.contribution_id, ft.id, ft.total_amount as amount
683 FROM civicrm_financial_trxn ft
684 WHERE contribution_id IS NOT NULL AND
685 ft.id NOT IN (SELECT financial_trxn_id
686 FROM civicrm_entity_financial_trxn
687 WHERE entity_table = 'civicrm_contribution'
688 AND entity_id = ft.contribution_id)";
689 CRM_Core_DAO
::executeQuery($sql);
690 //end of adding financial_trxn records and entity_financial_trxn records related to contribution
692 //update all linked line_item rows
693 // set line_item.financial_type_id = contribution.financial_type_id if contribution page id is null and not participant line item
694 // set line_item.financial_type_id = price_field_value.financial_type_id if contribution page id is set and not participant line item
695 // set line_item.financial_type_id = event.financial_type_id if its participant line item and line_item.price_field_value_id is null
696 // 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
697 $updateLineItemSql = "
698 UPDATE civicrm_line_item li
699 LEFT JOIN civicrm_contribution con
700 ON (li.entity_id = con.id AND li.entity_table = 'civicrm_contribution')
701 LEFT JOIN civicrm_price_field_value cpfv
702 ON li.price_field_value_id = cpfv.id
703 LEFT JOIN civicrm_participant cp
704 ON (li.entity_id = cp.id AND li.entity_table = 'civicrm_participant')
705 LEFT JOIN civicrm_event ce
706 ON ce.id = cp.event_id
707 SET li.financial_type_id = CASE
708 WHEN (con.contribution_page_id IS NULL || li.price_field_value_id IS NULL) AND cp.id IS NULL THEN
709 con.financial_type_id
710 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
711 cpfv.financial_type_id
712 WHEN cp.id IS NOT NULL AND li.price_field_value_id IS NULL THEN
715 CRM_Core_DAO
::executeQuery($updateLineItemSql, $queryParams);
717 //add the financial_item entries
718 //add a temp column so that inserting entity_financial_trxn entries gets easy
719 $sql = "ALTER TABLE civicrm_financial_item ADD COLUMN f_trxn_id INT DEFAULT NULL";
720 CRM_Core_DAO
::executeQuery($sql);
722 //add financial_item entries for contribution completed / pending pay later / cancelled
723 $contributionlineItemSql = "
724 INSERT INTO civicrm_financial_item
725 (transaction_date, contact_id, amount, currency, entity_table, entity_id, description, status_id, financial_account_id, f_trxn_id)
727 SELECT REPLACE(REPLACE(REPLACE(ft.trxn_date, '-', ''), ':', ''), ' ', ''), con.contact_id,
728 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',
729 li.id as line_item_id, li.label as line_item_label,
730 IF(con.contribution_status_id = {$pendingStatus}, {$unpaidStatus}, {$paidStatus}) as status_id, efa.financial_account_id as financial_account_id,
732 FROM civicrm_line_item li
733 INNER JOIN civicrm_contribution con
734 ON (li.entity_id = con.id AND li.entity_table = 'civicrm_contribution')
735 INNER JOIN civicrm_financial_trxn ft
736 ON (con.id = ft.contribution_id)
737 LEFT JOIN civicrm_entity_financial_account efa
738 ON (li.financial_type_id = efa.entity_id AND efa.entity_table = 'civicrm_financial_type'
739 AND efa.account_relationship = {$incomeAccountIs})
740 WHERE con.contribution_status_id IN (%1, %3) OR (con.is_pay_later = 1 AND con.contribution_status_id = %2)";
741 CRM_Core_DAO
::executeQuery($contributionlineItemSql, $queryParams);
743 //add financial_item entries for event
744 $participantLineItemSql = "
745 INSERT INTO civicrm_financial_item
746 (transaction_date, contact_id, amount, currency, entity_table, entity_id, description, status_id, financial_account_id, f_trxn_id)
748 SELECT REPLACE(REPLACE(REPLACE(ft.trxn_date, '-', ''), ':', ''), ' ', ''), con.contact_id,
749 IF(ft.total_amount < 0 AND con.contribution_status_id = %3, -li.line_total, li.line_total) as line_total,
750 con.currency, 'civicrm_line_item', li.id as line_item_id, li.label as line_item_label,
751 IF(con.contribution_status_id = {$pendingStatus}, {$unpaidStatus}, {$paidStatus}) as status_id,
752 efa.financial_account_id as financial_account_id, ft.id as f_trxn_id
753 FROM civicrm_line_item li
754 INNER JOIN civicrm_participant par
755 ON (li.entity_id = par.id AND li.entity_table = 'civicrm_participant')
756 INNER JOIN civicrm_participant_payment pp
757 ON (pp.participant_id = par.id)
758 INNER JOIN civicrm_contribution con
759 ON (pp.contribution_id = con.id)
760 INNER JOIN civicrm_financial_trxn ft
761 ON (con.id = ft.contribution_id)
762 LEFT JOIN civicrm_entity_financial_account efa
763 ON (li.financial_type_id = efa.entity_id AND
764 efa.entity_table = 'civicrm_financial_type' AND efa.account_relationship = {$incomeAccountIs})
765 WHERE con.contribution_status_id IN (%1, %3) OR (con.is_pay_later = 1 AND con.contribution_status_id = %2)";
766 CRM_Core_DAO
::executeQuery($participantLineItemSql, $queryParams);
768 //fee handling for contributions
769 //insert fee entries in financial_trxn for contributions
770 $sql = "ALTER TABLE civicrm_financial_trxn ADD COLUMN is_fee TINYINT DEFAULT NULL";
771 CRM_Core_DAO
::executeQuery($sql);
774 INSERT INTO civicrm_financial_trxn
775 (contribution_id, payment_instrument_id, currency, total_amount, net_amount, fee_amount, trxn_id, status_id, check_number,
776 to_financial_account_id, from_financial_account_id, trxn_date, payment_processor_id, is_fee)
778 SELECT con.id, ft.payment_instrument_id, ft.currency, ft.fee_amount, NULL, NULL, ft.trxn_id, %1 as status_id,
779 ft.check_number, efaFT.financial_account_id as to_financial_account_id, CASE
780 WHEN efaPP.financial_account_id IS NOT NULL THEN
781 efaPP.financial_account_id
782 WHEN tpi.financial_account_id IS NOT NULL THEN
783 tpi.financial_account_id
785 {$financialAccountId}
786 END as from_financial_account_id, ft.trxn_date, ft.payment_processor_id, 1 as is_fee
787 FROM civicrm_contribution con
788 INNER JOIN civicrm_financial_trxn ft
789 ON (ft.contribution_id = con.id)
790 LEFT JOIN civicrm_entity_financial_account efaFT
791 ON (con.financial_type_id = efaFT.entity_id AND efaFT.entity_table = 'civicrm_financial_type'
792 AND efaFT.account_relationship = {$expenseAccountIs})
793 LEFT JOIN civicrm_entity_financial_account efaPP
794 ON (ft.payment_processor_id = efaPP.entity_id AND efaPP.entity_table = 'civicrm_payment_processor'
795 AND efaPP.account_relationship = {$assetAccountIs})
796 LEFT JOIN {$tempTableName1} tpi
797 ON ft.payment_instrument_id = tpi.instrument_id
798 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))
800 CRM_Core_DAO
::executeQuery($sql, $queryParams);
802 //link financial_trxn to contribution
804 INSERT INTO civicrm_entity_financial_trxn
805 (entity_table, entity_id, financial_trxn_id, amount)
806 SELECT 'civicrm_contribution', ft.contribution_id, ft.id, ft.total_amount
807 FROM civicrm_financial_trxn ft
808 WHERE ft.is_fee = 1";
809 CRM_Core_DAO
::executeQuery($sql);
811 //add fee related entries to financial item table
812 $domainId = CRM_Core_Config
::domainID();
813 $domainContactId = CRM_Core_DAO
::getFieldValue('CRM_Core_DAO_Domain', $domainId, 'contact_id');
815 INSERT INTO civicrm_financial_item
816 (transaction_date, contact_id, amount, currency, entity_table, entity_id, description, status_id, financial_account_id, f_trxn_id)
817 SELECT ft.trxn_date, {$domainContactId} as contact_id, ft.total_amount, ft.currency, 'civicrm_financial_trxn', ft.id,
818 'Fee', {$paidStatus} as status_id, ft.to_financial_account_id as financial_account_id, ft.id as f_trxn_id
819 FROM civicrm_financial_trxn ft
820 WHERE ft.is_fee = 1;";
821 CRM_Core_DAO
::executeQuery($sql);
823 //add entries to entity_financial_trxn table
825 INSERT INTO civicrm_entity_financial_trxn (entity_table, entity_id, financial_trxn_id, amount)
826 SELECT 'civicrm_financial_item' as entity_table, fi.id as entity_id, fi.f_trxn_id as financial_trxn_id, fi.amount
827 FROM civicrm_financial_item fi";
828 CRM_Core_DAO
::executeQuery($sql);
830 //drop the temparory columns
831 $sql = "ALTER TABLE civicrm_financial_trxn
832 DROP COLUMN contribution_id,
833 DROP COLUMN is_fee;";
834 CRM_Core_DAO
::executeQuery($sql);
836 $sql = "ALTER TABLE civicrm_financial_item DROP f_trxn_id";
837 CRM_Core_DAO
::executeQuery($sql);
845 public function createDomainContacts() {
846 $domainParams = $context = array();
848 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',
849 ADD CONSTRAINT FK_civicrm_domain_contact_id FOREIGN KEY (contact_id) REFERENCES civicrm_contact(id);";
850 CRM_Core_DAO
::executeQuery($query, CRM_Core_DAO
::$_nullArray, TRUE, NULL, FALSE, FALSE);
853 SELECT cd.id, cd.name, ce.email FROM civicrm_domain cd
854 LEFT JOIN civicrm_loc_block clb ON clb.id = cd. loc_block_id
855 LEFT JOIN civicrm_email ce ON ce.id = clb.email_id ;
857 $dao = CRM_Core_DAO
::executeQuery($query);
858 while ($dao->fetch()) {
860 SELECT cc.id FROM civicrm_contact cc
861 LEFT JOIN civicrm_email ce ON ce.contact_id = cc.id
862 WHERE cc.contact_type = 'Organization' AND cc.organization_name = %1
864 $params = array(1 => array($dao->name
, 'String'));
866 $query .= " AND ce.email = %2 ";
867 $params[2] = array($dao->email
, 'String');
869 $contactID = CRM_Core_DAO
::singleValueQuery($query, $params);
870 $context[1] = $dao->name
;
871 if (empty($contactID)) {
873 'sort_name' => $dao->name
,
874 'display_name' => $dao->name
,
875 'legal_name' => $dao->name
,
876 'organization_name' => $dao->name
,
877 'contact_type' => 'Organization',
879 $contact = CRM_Contact_BAO_Contact
::add($params);
880 $contactID = $contact->id
;
881 $context[0] = 'added';
884 $context[0] = 'merged';
886 $domainParams['contact_id'] = $contactID;
887 CRM_Core_BAO_Domain
::edit($domainParams, $dao->id
);
892 public function task_4_3_alpha1_checkDBConstraints() {
893 //checking whether the foreign key exists before dropping it CRM-11260
894 $config = CRM_Core_Config
::singleton();
895 $dbUf = DB
::parseDSN($config->dsn
);
897 'autorenewal_msg_id' => array(
898 'tableName' => 'civicrm_membership_type',
899 'fkey' => 'FK_civicrm_membership_autorenewal_msg_id',
901 'to_account_id' => array(
902 'tableName' => 'civicrm_financial_trxn',
903 'constraintName' => 'civicrm_financial_trxn_ibfk_2',
905 'from_account_id' => array(
906 'tableName' => 'civicrm_financial_trxn',
907 'constraintName' => 'civicrm_financial_trxn_ibfk_1',
909 'contribution_type_id' => array(
910 'tableName' => 'civicrm_contribution_recur',
911 'fkey' => 'FK_civicrm_contribution_recur_contribution_type_id',
915 SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
916 WHERE table_name = 'civicrm_contribution_recur'
917 AND constraint_name = 'FK_civicrm_contribution_recur_contribution_type_id'
918 AND TABLE_SCHEMA = %1
920 $params = array(1 => array($dbUf['database'], 'String'));
921 $dao = CRM_Core_DAO
::executeQuery($query, $params, TRUE, NULL, FALSE, FALSE);
922 foreach ($tables as $columnName => $value) {
923 if ($value['tableName'] == 'civicrm_membership_type' ||
$value['tableName'] == 'civicrm_contribution_recur') {
924 $foreignKeyExists = CRM_Core_DAO
::checkConstraintExists($value['tableName'], $value['fkey']);
925 $fKey = $value['fkey'];
928 $foreignKeyExists = CRM_Core_DAO
::checkFKConstraintInFormat($value['tableName'], $columnName);
929 $fKey = "`FK_{$value['tableName']}_{$columnName}`";
931 if ($foreignKeyExists ||
$value['tableName'] == 'civicrm_financial_trxn') {
932 if ($value['tableName'] != 'civicrm_contribution_recur' ||
($value['tableName'] == 'civicrm_contribution_recur' && $dao->N
)) {
933 $constraintName = $foreignKeyExists ?
$fKey : $value['constraintName'];
934 $query = "ALTER TABLE {$value['tableName']} DROP FOREIGN KEY {$constraintName}";
935 CRM_Core_DAO
::executeQuery($query, $params, TRUE, NULL, FALSE, FALSE);
937 $query = "ALTER TABLE {$value['tableName']} DROP INDEX {$fKey}";
938 CRM_Core_DAO
::executeQuery($query, $params, TRUE, NULL, FALSE, FALSE);
941 // check if column contact_id is present or not in civicrm_financial_account
942 $fieldExists = CRM_Core_DAO
::checkFieldExists('civicrm_financial_account', 'contact_id', FALSE);
945 ALTER TABLE civicrm_financial_account
946 ADD contact_id int(10) unsigned DEFAULT NULL COMMENT 'Version identifier of financial_type' AFTER name,
947 ADD CONSTRAINT FK_civicrm_financial_account_contact_id FOREIGN KEY (contact_id) REFERENCES civicrm_contact(id);
949 CRM_Core_DAO
::executeQuery($query, $params, TRUE, NULL, FALSE, FALSE);
954 * Read creation and modification times from civicrm_log; add
955 * them to civicrm_contact.
957 public function convertTimestamps(CRM_Queue_TaskContext
$ctx, $startId, $endId) {
959 SELECT entity_id, min(modified_date) AS created, max(modified_date) AS modified
961 WHERE entity_table = 'civicrm_contact'
962 AND entity_id BETWEEN %1 AND %2
966 1 => array($startId, 'Integer'),
967 2 => array($endId, 'Integer'),
969 $dao = CRM_Core_DAO
::executeQuery($sql, $params);
970 while ($dao->fetch()) {
971 // FIXME civicrm_log.modified_date is DATETIME; civicrm_contact.modified_date is TIMESTAMP
972 CRM_Core_DAO
::executeQuery(
973 'UPDATE civicrm_contact SET created_date = FROM_UNIXTIME(UNIX_TIMESTAMP(%1)), modified_date = FROM_UNIXTIME(UNIX_TIMESTAMP(%2)) WHERE id = %3',
975 1 => array($dao->created
, 'String'),
976 2 => array($dao->modified
, 'String'),
977 3 => array($dao->entity_id
, 'Integer'),
986 * Change index and add missing constraints for civicrm_contribution_recur
988 public function addMissingConstraints(CRM_Queue_TaskContext
$ctx) {
989 $query = "SHOW KEYS FROM `civicrm_contribution_recur` WHERE key_name = 'UI_contrib_payment_instrument_id'";
990 $dao = CRM_Core_DAO
::executeQuery($query);
992 CRM_Core_DAO
::executeQuery('ALTER TABLE civicrm_contribution_recur DROP INDEX UI_contrib_payment_instrument_id');
993 CRM_Core_DAO
::executeQuery('ALTER TABLE civicrm_contribution_recur ADD INDEX UI_contribution_recur_payment_instrument_id (payment_instrument_id)');
995 $constraintArray = array(
996 'contact_id' => " ADD CONSTRAINT `FK_civicrm_contribution_recur_contact_id` FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE ",
997 '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 ",
998 '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 ",
999 'campaign_id' => " ADD CONSTRAINT `FK_civicrm_contribution_recur_campaign_id` FOREIGN KEY (`campaign_id`) REFERENCES `civicrm_campaign` (`id`) ON DELETE SET NULL ",
1001 $constraint = array();
1002 foreach ($constraintArray as $constraintKey => $value) {
1003 $foreignKeyExists = CRM_Core_DAO
::checkFKConstraintInFormat('civicrm_contribution_recur', $constraintKey);
1004 if (!$foreignKeyExists) {
1005 $constraint[] = $value;
1008 if (!empty($constraint)) {
1009 $query = "ALTER TABLE civicrm_contribution_recur " . implode(' , ', $constraint);
1010 CRM_Core_DAO
::executeQuery($query);
1016 * Update financial_account_id for bad data in financial_trxn table
1019 public function updateFinancialTrxnData(CRM_Queue_TaskContext
$ctx) {
1020 $upgrade = new CRM_Upgrade_Form();
1021 $sql = "SELECT cc.id contribution_id, cc.contribution_recur_id, cft.payment_processor_id,
1022 cft.id financial_trxn_id, cfi.entity_table, cft.from_financial_account_id, cft.to_financial_account_id
1024 FROM `civicrm_contribution` cc
1025 LEFT JOIN civicrm_entity_financial_trxn ceft ON ceft.entity_id = cc.id
1026 LEFT JOIN civicrm_financial_trxn cft ON cft.id = ceft.financial_trxn_id
1027 LEFT JOIN civicrm_entity_financial_trxn ceft1 ON ceft1.financial_trxn_id = ceft.financial_trxn_id
1028 LEFT JOIN civicrm_financial_item cfi ON cfi.id = ceft1.entity_id
1029 WHERE ceft.entity_table = 'civicrm_contribution' AND cc.contribution_recur_id IS NOT NULL
1030 AND ceft1.entity_table = 'civicrm_financial_item' AND cft.id IS NOT NULL AND cft.payment_instrument_id = %1
1033 $paymentInstrument = CRM_Contribute_PseudoConstant
::paymentInstrument('name');
1034 $param = array(1 => array(array_search('Credit Card', $paymentInstrument), 'Integer'));
1035 $dao = CRM_Core_DAO
::executeQuery($sql, $param);
1036 $financialTrxn = array();
1037 $subsequentPayments = array();
1038 while ($dao->fetch()) {
1039 if (!array_key_exists($dao->contribution_recur_id
, $financialTrxn)) {
1040 $financialTrxn[$dao->contribution_recur_id
] = array(
1041 'from_financial_account_id' => $dao->to_financial_account_id
,
1042 'payment_processor_id' => $dao->payment_processor_id
,
1043 $dao->contribution_id
=> 1,
1045 if (!is_null($dao->from_financial_account_id
)) {
1046 $sql = 'UPDATE civicrm_financial_trxn SET from_financial_account_id = NULL WHERE id = %1';
1047 $params = array(1 => array($dao->financial_trxn_id
, 'Integer'));
1048 CRM_Core_DAO
::executeQuery($sql, $params);
1051 elseif (!array_key_exists($dao->contribution_id
, $financialTrxn[$dao->contribution_recur_id
])) {
1052 if (($dao->entity_table
== 'civicrm_line_item' && $dao->to_financial_account_id
== $financialTrxn[$dao->contribution_recur_id
]['from_financial_account_id'])
1053 ||
($dao->entity_table
== 'civicrm_financial_trxn' && $dao->from_financial_account_id
== $financialTrxn[$dao->contribution_recur_id
]['from_financial_account_id'])
1057 $subsequentPayments[$dao->contribution_recur_id
][$dao->entity_table
][] = $dao->financial_trxn_id
;
1060 foreach ($subsequentPayments as $key => $value) {
1061 foreach ($value as $table => $val) {
1062 if ($table == 'civicrm_financial_trxn') {
1063 $field = 'from_financial_account_id';
1066 $field = 'to_financial_account_id';
1068 $sql = "UPDATE civicrm_financial_trxn SET $field = " . $financialTrxn[$dao->contribution_recur_id
]['from_financial_account_id'] . ',
1069 payment_processor_id = ' . $financialTrxn[$dao->contribution_recur_id
]['payment_processor_id'] . ' WHERE
1070 id IN (' . implode(',', $val) . ')';
1071 CRM_Core_DAO
::executeQuery($sql);
1078 * Update financial_account_id for bad data in financial_trxn table
1081 public function updateLineItemData(CRM_Queue_TaskContext
$ctx) {
1082 $sql = "SELECT cc.id contribution_id, cc.contribution_recur_id,
1083 cc.financial_type_id contribution_financial_type,
1084 cli.financial_type_id line_financial_type_id,
1085 cli.price_field_id, cli.price_field_value_id, cli.label, cli.id line_item_id,
1086 cfi.financial_account_id
1087 FROM `civicrm_line_item` cli
1088 LEFT JOIN civicrm_contribution cc ON cc.id = cli.entity_id
1089 LEFT JOIN civicrm_financial_item cfi ON cfi.entity_id = cli.id
1090 LEFT JOIN civicrm_price_field cpf ON cpf.id = cli.price_field_id
1091 LEFT JOIN civicrm_price_set cps ON cps.id = cpf.price_set_id
1092 LEFT JOIN civicrm_price_field_value cpfv ON cpfv.id = cli.price_field_value_id
1093 WHERE cfi.entity_table = 'civicrm_line_item'
1094 AND cli.entity_table = 'civicrm_contribution'
1095 AND cps.is_quick_config = 1 AND cc.contribution_recur_id IS NOT NULL
1097 $dao = CRM_Core_DAO
::executeQuery($sql);
1098 $financialTrxn = $subsequentPayments = array();
1099 while ($dao->fetch()) {
1100 if (!array_key_exists($dao->contribution_recur_id
, $financialTrxn)) {
1101 $financialTrxn[$dao->contribution_recur_id
] = array(
1102 'price_field_id' => $dao->price_field_id
,
1103 'price_field_value_id' => $dao->price_field_value_id
,
1104 'label' => strval($dao->label
),
1105 'financial_account_id' => $dao->financial_account_id
,
1106 $dao->contribution_id
=> 1,
1110 if ($dao->price_field_value_id
== $financialTrxn[$dao->contribution_recur_id
]['price_field_value_id']) {
1113 $subsequentPayments[$dao->contribution_recur_id
][] = $dao->line_item_id
;
1116 foreach ($subsequentPayments as $key => $value) {
1117 $sql = "UPDATE civicrm_line_item cli
1118 LEFT JOIN civicrm_financial_item cfi ON cli.id = cfi.entity_id
1121 cli.price_field_id = %2,
1122 cli.price_field_value_id = %3,
1123 cfi.financial_account_id = %4,
1124 cfi.description = %5,
1125 cli.financial_type_id = %6
1126 WHERE cfi.entity_table = 'civicrm_line_item'
1127 AND cli.entity_table = 'civicrm_contribution' AND cli.id IN (" . implode(',', $value) . ');';
1129 1 => array($financialTrxn[$key]['label'], 'String'),
1130 2 => array($financialTrxn[$key]['price_field_id'], 'Integer'),
1131 3 => array($financialTrxn[$key]['price_field_value_id'], 'Integer'),
1132 4 => array($financialTrxn[$key]['financial_account_id'], 'Integer'),
1133 5 => array($financialTrxn[$key]['label'], 'String'),
1134 6 => array($dao->contribution_financial_type
, 'Integer'),
1136 CRM_Core_DAO
::executeQuery($sql, $params);
1142 * Replace contribution_type to financial_type in table
1143 * civicrm_saved_search and Structure civicrm_report_instance
1145 public function replaceContributionTypeId(CRM_Queue_TaskContext
$ctx, $query, $table) {
1146 $dao = CRM_Core_DAO
::executeQuery($query);
1147 while ($dao->fetch()) {
1148 $formValues = unserialize($dao->form_values
);
1149 foreach (array('contribution_type_id_op', 'contribution_type_id_value', 'contribution_type_id') as $value) {
1150 if (array_key_exists($value, $formValues)) {
1151 $key = preg_replace('/contribution/', 'financial', $value);
1152 $formValues[$key] = $formValues[$value];
1153 unset($formValues[$value]);
1156 if ($table != 'savedSearch') {
1157 foreach (array('fields', 'group_bys') as $value) {
1158 if (array_key_exists($value, $formValues)) {
1159 if (array_key_exists('contribution_type_id', $formValues[$value])) {
1160 $formValues[$value]['financial_type_id'] = $formValues[$value]['contribution_type_id'];
1161 unset($formValues[$value]['contribution_type_id']);
1163 elseif (array_key_exists('contribution_type', $formValues[$value])) {
1164 $formValues[$value]['financial_type'] = $formValues[$value]['contribution_type'];
1165 unset($formValues[$value]['contribution_type']);
1169 if (array_key_exists('order_bys', $formValues)) {
1170 foreach ($formValues['order_bys'] as $key => $values) {
1171 if (preg_grep('/contribution_type/', $values)) {
1172 $formValues['order_bys'][$key]['column'] = preg_replace('/contribution_type/', 'financial_type', $values['column']);
1178 if ($table == 'savedSearch') {
1179 $saveDao = new CRM_Contact_DAO_SavedSearch();
1182 $saveDao = new CRM_Report_DAO_ReportInstance();
1184 $saveDao->id
= $dao->id
;
1186 if ($table == 'savedSearch') {
1187 if (array_key_exists('mapper', $formValues)) {
1188 foreach ($formValues['mapper'] as $key => $values) {
1189 foreach ($values as $k => $v) {
1190 if (preg_grep('/contribution_/', $v)) {
1191 $formValues['mapper'][$key][$k] = preg_replace('/contribution_type/', 'financial_type', $v);
1196 foreach (array('select_tables', 'where_tables') as $value) {
1197 if (preg_match('/contribution_type/', $dao->$value)) {
1198 $tempValue = unserialize($dao->$value);
1199 if (array_key_exists('civicrm_contribution_type', $tempValue)) {
1200 $tempValue['civicrm_financial_type'] = $tempValue['civicrm_contribution_type'];
1201 unset($tempValue['civicrm_contribution_type']);
1203 $saveDao->$value = serialize($tempValue);
1206 if (preg_match('/contribution_type/', $dao->where_clause
)) {
1207 $saveDao->where_clause
= preg_replace('/contribution_type/', 'financial_type', $dao->where_clause
);
1210 $saveDao->form_values
= serialize($formValues);
1218 * Add ON DELETE options for constraint if not present
1219 * CRM-13088 && CRM-12156
1221 * @param CRM_Queue_TaskContext $ctx
1226 public function task_4_3_x_checkConstraints(CRM_Queue_TaskContext
$ctx) {
1227 CRM_Core_DAO
::executeQuery('ALTER TABLE `civicrm_financial_account` CHANGE `contact_id` `contact_id` INT( 10 ) UNSIGNED NULL DEFAULT NULL');
1228 $config = CRM_Core_Config
::singleton();
1229 $dbname = DB
::parseDSN($config->dsn
);
1230 $constraintArray = array(
1231 "'FK_civicrm_financial_account_contact_id'",
1232 "'FK_civicrm_financial_item_contact_id'",
1233 "'FK_civicrm_contribution_recur_financial_type_id'",
1234 "'FK_civicrm_line_item_financial_type_id'",
1235 "'FK_civicrm_product_financial_type_id'",
1236 "'FK_civicrm_premiums_product_financial_type_id'",
1237 "'FK_civicrm_price_field_value_financial_type_id'",
1238 "'FK_civicrm_contribution_product_financial_type_id'",
1239 "'FK_civicrm_price_set_financial_type_id'",
1240 "'FK_civicrm_grant_financial_type_id'",
1243 $sql = "SELECT DELETE_RULE, TABLE_NAME, CONSTRAINT_NAME
1244 FROM information_schema.REFERENTIAL_CONSTRAINTS
1245 WHERE CONSTRAINT_NAME IN (" . implode(',', $constraintArray) . ")
1246 AND CONSTRAINT_SCHEMA = %1";
1247 $params = array(1 => array($dbname['database'], 'String'));
1248 $onDelete = CRM_Core_DAO
::executeQuery($sql, $params, TRUE, FALSE);
1249 while ($onDelete->fetch()) {
1250 if (($onDelete->TABLE_NAME
!= 'civicrm_financial_item' && $onDelete->DELETE_RULE
!= 'SET NULL') ||
1251 ($onDelete->TABLE_NAME
== 'civicrm_financial_item' && $onDelete->DELETE_RULE
!= 'CASCADE')
1253 $tableName = 'civicrm_financial_type';
1254 $onDeleteOption = ' SET NULL ';
1255 $columnName = 'financial_type_id';
1256 if (preg_match('/contact_id/', $onDelete->CONSTRAINT_NAME
)) {
1257 $tableName = 'civicrm_contact';
1258 $columnName = 'contact_id';
1259 if ($onDelete->TABLE_NAME
== 'civicrm_financial_item') {
1260 $onDeleteOption = 'CASCADE';
1267 $query = "ALTER TABLE {$onDelete->TABLE_NAME}
1268 DROP FOREIGN KEY {$onDelete->CONSTRAINT_NAME},
1269 DROP INDEX {$onDelete->CONSTRAINT_NAME};";
1270 CRM_Core_DAO
::executeQuery($query, array(), TRUE, NULL, FALSE, FALSE);
1271 $query = " ALTER TABLE {$onDelete->TABLE_NAME}
1272 ADD CONSTRAINT {$onDelete->CONSTRAINT_NAME} FOREIGN KEY (`" . $columnName . "`) REFERENCES {$tableName} (`id`) ON DELETE {$onDeleteOption};
1274 CRM_Core_DAO
::executeQuery($query, array(), TRUE, NULL, FALSE, FALSE);
1280 * Check/Add INDEX CRM-12141
1282 * @param CRM_Queue_TaskContext $ctx
1287 public function task_4_3_x_checkIndexes(CRM_Queue_TaskContext
$ctx) {
1290 FROM civicrm_entity_financial_trxn
1291 WHERE key_name IN ('UI_entity_financial_trxn_entity_table', 'UI_entity_financial_trxn_entity_id')
1293 $dao = CRM_Core_DAO
::executeQuery($query);
1296 ALTER TABLE civicrm_entity_financial_trxn
1297 ADD INDEX UI_entity_financial_trxn_entity_table (entity_table),
1298 ADD INDEX UI_entity_financial_trxn_entity_id (entity_id);
1300 CRM_Core_DAO
::executeQuery($query);
1306 * Update phones CRM-11292
1308 * @param CRM_Queue_TaskContext $ctx
1313 public static function phoneNumeric(CRM_Queue_TaskContext
$ctx) {
1314 CRM_Core_DAO
::executeQuery(CRM_Contact_BAO_Contact
::DROP_STRIP_FUNCTION_43
);
1315 CRM_Core_DAO
::executeQuery(CRM_Contact_BAO_Contact
::CREATE_STRIP_FUNCTION_43
);
1316 CRM_Core_DAO
::executeQuery("UPDATE civicrm_phone SET phone_numeric = civicrm_strip_non_numeric(phone)");
1321 * (Queue Task Callback)
1323 public static function task_4_3_x_runSql(CRM_Queue_TaskContext
$ctx, $rev) {
1324 $upgrade = new CRM_Upgrade_Form();
1325 $upgrade->processSQL($rev);
1331 * Syntatic sugar for adding a task which (a) is in this class and (b) has
1334 * After passing the $funcName, you can also pass parameters that will go to
1335 * the function. Note that all params must be serializable.
1337 protected function addTask($title, $funcName) {
1338 $queue = CRM_Queue_Service
::singleton()->load(array(
1340 'name' => CRM_Upgrade_Form
::QUEUE_NAME
,
1343 $args = func_get_args();
1344 $title = array_shift($args);
1345 $funcName = array_shift($args);
1346 $task = new CRM_Queue_Task(
1347 array(get_class($this), $funcName),
1351 $queue->createItem($task, array('weight' => -1));