4 +--------------------------------------------------------------------+
5 | CiviCRM version 4.3 |
6 +--------------------------------------------------------------------+
7 | Copyright CiviCRM LLC (c) 2004-2013 |
8 +--------------------------------------------------------------------+
9 | This file is a part of CiviCRM. |
11 | CiviCRM is free software; you can copy, modify, and distribute it |
12 | under the terms of the GNU Affero General Public License |
13 | Version 3, 19 November 2007. |
15 | CiviCRM is distributed in the hope that it will be useful, but |
16 | WITHOUT ANY WARRANTY; without even the implied warranty of |
17 | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. |
18 | See the GNU Affero General Public License for more details. |
20 | You should have received a copy of the GNU Affero General Public |
21 | License along 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 +--------------------------------------------------------------------+
31 * @copyright CiviCRM LLC (c) 2004-2013
35 class CRM_Upgrade_Incremental_php_FourThree
{
36 const BATCH_SIZE
= 5000;
38 function verifyPreDBstate(&$errors) {
43 * Compute any messages which should be displayed beforeupgrade
45 * Note: This function is called iteratively for each upcoming
46 * revision to the database.
48 * @param $postUpgradeMessage string, alterable
49 * @param $rev string, a version number, e.g. '4.3.alpha1', '4.3.beta3', '4.3.0'
52 function setPreUpgradeMessage(&$preUpgradeMessage, $rev, $currentVer = NULL) {
53 if ($rev == '4.3.beta3') {
55 //sql for checking orphaned contribution records
56 $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";
57 $count = CRM_Core_DAO
::singleValueQuery($sql, array(), TRUE, FALSE);
60 $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(1 => $count, 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>'));
61 CRM_Core_Error
::fatal($error);
65 if ($rev == '4.3.beta4' && CRM_Utils_Constant
::value('CIVICRM_UF', FALSE) == 'Drupal6') {
66 // CRM-11823 - Make sure the D6 HTML HEAD technique will work on upgrade pages
67 theme('item_list', array()); // force-load theme registry
68 $theme_registry = theme_get_registry();
70 !isset($theme_registry['page']['preprocess functions']) ||
71 FALSE === array_search('civicrm_preprocess_page_inject', $theme_registry['page']['preprocess functions'])
73 CRM_Core_Error
::fatal('Please reset the Drupal cache (Administer => Site Configuration => Performance => Clear cached data))');
79 * Compute any messages which should be displayed after upgrade
81 * @param $postUpgradeMessage string, alterable
82 * @param $rev string, an intermediate version; note that setPostUpgradeMessage is called repeatedly with different $revs
85 function setPostUpgradeMessage(&$postUpgradeMessage, $rev) {
86 if ($rev == '4.3.alpha1') {
87 // check if CiviMember component is enabled
88 $config = CRM_Core_Config
::singleton();
89 if (in_array('CiviMember', $config->enableComponents
)) {
90 $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.');
91 $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.');
95 //here we do the financial type check and migration
96 $isDefaultsModified = self
::_checkAndMigrateDefaultFinancialTypes();
97 if($isDefaultsModified) {
98 $postUpgradeMessage .= '<br />' . ts('Please review all price set financial type assignments.');
100 list($context, $orgName) = self
::createDomainContacts();
101 if ($context == 'added') {
102 $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: '{$orgName}'.");
104 elseif ($context == 'merged') {
105 $postUpgradeMessage .= '<br />' . ts("The existing organization contact record for '{$orgName}' has been marked as the default domain contact, and has been updated with information from your Organization Address and Contact Info settings.");
108 $providerExists = CRM_Core_DAO
::singleValueQuery("SELECT id FROM civicrm_sms_provider LIMIT 1");
109 if ($providerExists) {
110 $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'));
114 if ($rev == '4.3.alpha2') {
117 FROM civicrm_action_schedule
118 WHERE entity_value = '' OR entity_value IS NULL
121 $dao = CRM_Core_DAO
::executeQuery($sql);
124 while ($dao->fetch()) {
125 $reminder[$dao->id
] = $dao->title
;
126 $list .= "<li>{$dao->title}</li>";
128 if (!empty($reminder)) {
129 $list = "<br /><ul>" . $list . "</ul>";
130 $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));
134 if ($rev == '4.3.beta2') {
135 $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).');
138 if ($rev == '4.3.beta5') {
139 $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.");
142 $query = "SELECT ceft.id FROM `civicrm_financial_trxn` cft
143 LEFT JOIN civicrm_entity_financial_trxn ceft
144 ON ceft.financial_trxn_id = cft.id AND ceft.entity_table = 'civicrm_contribution'
145 LEFT JOIN civicrm_contribution cc ON cc.id = ceft.entity_id AND ceft.entity_table = 'civicrm_contribution'
146 WHERE cc.id IS NULL";
148 $dao = CRM_Core_DAO
::executeQuery($query);
149 $isOrphanData = TRUE;
151 $query = "SELECT cli.id FROM civicrm_line_item cli
152 LEFT JOIN civicrm_contribution cc ON cli.entity_id = cc.id AND cli.entity_table = 'civicrm_contribution'
153 LEFT JOIN civicrm_participant cp ON cli.entity_id = cp.id AND cli.entity_table = 'civicrm_participant'
154 WHERE CASE WHEN cli.entity_table = 'civicrm_contribution'
158 $dao = CRM_Core_DAO
::executeQuery($query);
160 $revPattern = '/^((\d{1,2})\.\d{1,2})\.(\d{1,2}|\w{4,7})?$/i';
161 preg_match($revPattern, $currentVer, $version);
162 if ($version[1] >= 4.3) {
163 $query = "SELECT cfi.id FROM civicrm_financial_item cfi
164 LEFT JOIN civicrm_entity_financial_trxn ceft ON ceft.entity_table = 'civicrm_financial_item' and cfi.id = ceft.entity_id
165 WHERE ceft.entity_id IS NULL;";
166 $dao = CRM_Core_DAO
::executeQuery($query);
168 $isOrphanData = FALSE;
172 $isOrphanData = FALSE;
178 $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">this wiki page for details</a>.
179 ', array( 1 => 'http://wiki.civicrm.org/confluence/display/CRMDOC/Clean+up+extraneous+financial+data+-+4.3+upgrades')) . "</strong>";
184 function upgrade_4_3_alpha1($rev) {
185 self
::task_4_3_alpha1_checkDBConstraints();
187 // add indexes for civicrm_entity_financial_trxn
189 $this->addTask(ts('Check/Add indexes for civicrm_entity_financial_trxn'), 'task_4_3_x_checkIndexes', $rev);
190 // task to process sql
191 $this->addTask(ts('Upgrade DB to 4.3.alpha1: SQL'), 'task_4_3_x_runSql', $rev);
194 $this->addTask(ts('Populate financial type values for price records'), 'assignFinancialTypeToPriceRecords');
195 //CRM-11514 create financial records for contributions
196 $this->addTask(ts('Create financial records for contributions'), 'createFinancialRecords');
198 $minId = CRM_Core_DAO
::singleValueQuery('SELECT coalesce(min(id),0) FROM civicrm_contact');
199 $maxId = CRM_Core_DAO
::singleValueQuery('SELECT coalesce(max(id),0) FROM civicrm_contact');
200 for ($startId = $minId; $startId <= $maxId; $startId +
= self
::BATCH_SIZE
) {
201 $endId = $startId + self
::BATCH_SIZE
- 1;
202 $title = ts('Upgrade timestamps (%1 => %2)', array(
206 $this->addTask($title, 'convertTimestamps', $startId, $endId);
210 // fix WP access control
211 $config = CRM_Core_Config
::singleton( );
212 if ($config->userFramework
== 'WordPress') {
213 civicrm_wp_set_capabilities( );
216 // Update phones CRM-11292.
217 $this->addTask(ts('Upgrade Phone Numbers'), 'phoneNumeric');
222 function upgrade_4_3_alpha2($rev) {
224 $isColumnPresent = CRM_Core_DAO
::checkFieldExists('civicrm_dedupe_rule_group', 'is_default');
225 if ($isColumnPresent) {
226 CRM_Core_DAO
::executeQuery('ALTER TABLE civicrm_dedupe_rule_group DROP COLUMN is_default');
228 $this->addTask(ts('Upgrade DB to 4.3.alpha2: SQL'), 'task_4_3_x_runSql', $rev);
231 function upgrade_4_3_alpha3($rev) {
232 $this->addTask(ts('Upgrade DB to 4.3.alpha3: SQL'), 'task_4_3_x_runSql', $rev);
235 function upgrade_4_3_beta2($rev) {
236 $this->addTask(ts('Upgrade DB to 4.3.beta2: SQL'), 'task_4_3_x_runSql', $rev);
240 CRM_Core_DAO
::checkTableExists('log_civicrm_line_item') &&
241 CRM_Core_DAO
::checkFieldExists('log_civicrm_line_item', 'label')
243 CRM_Core_DAO
::executeQuery('ALTER TABLE `log_civicrm_line_item` CHANGE `label` `label` VARCHAR(255) NULL DEFAULT NULL');
247 function upgrade_4_3_beta3($rev) {
248 $this->addTask(ts('Upgrade DB to 4.3.beta3: SQL'), 'task_4_3_x_runSql', $rev);
250 $query = "SELECT id, form_values FROM civicrm_report_instance WHERE form_values LIKE '%contribution_type%'";
251 $this->addTask('Replace contribution_type to financial_type in table civicrm_report_instance', 'replaceContributionTypeId', $query, 'reportInstance');
252 $query = "SELECT * FROM civicrm_saved_search WHERE form_values LIKE '%contribution_type%'";
253 $this->addTask('Replace contribution_type to financial_type in table civicrm_saved_search', 'replaceContributionTypeId', $query, 'savedSearch');
256 function upgrade_4_3_beta4($rev) {
257 $this->addTask(ts('Upgrade DB to 4.3.beta4: SQL'), 'task_4_3_x_runSql', $rev);
258 // add indexes for civicrm_entity_financial_trxn
260 $this->addTask(ts('Check/Add indexes for civicrm_entity_financial_trxn'), 'task_4_3_x_checkIndexes', $rev);
263 function upgrade_4_3_beta5($rev) {
264 $this->addTask(ts('Upgrade DB to 4.3.beta5: SQL'), 'task_4_3_x_runSql', $rev);
267 CRM_Core_DAO
::checkTableExists('log_civicrm_financial_trxn') &&
268 CRM_Core_DAO
::checkFieldExists('log_civicrm_financial_trxn', 'trxn_id')
270 CRM_Core_DAO
::executeQuery('ALTER TABLE `log_civicrm_financial_trxn` CHANGE `trxn_id` `trxn_id` VARCHAR(255) NULL DEFAULT NULL');
275 function assignFinancialTypeToPriceRecords() {
276 $upgrade = new CRM_Upgrade_Form();
277 //here we update price set entries
278 $sqlFinancialIds = "SELECT id, name FROM civicrm_financial_type
279 WHERE name IN ('Donation', 'Event Fee', 'Member Dues');";
280 $daoFinancialIds = CRM_Core_DAO
::executeQuery($sqlFinancialIds);
281 while($daoFinancialIds->fetch()) {
282 $financialIds[$daoFinancialIds->name
] = $daoFinancialIds->id
;
284 $sqlPriceSetUpdate = "UPDATE civicrm_price_set ps
285 SET ps.financial_type_id = CASE
286 WHEN ps.extends LIKE '%1%' THEN {$financialIds['Event Fee']}
287 WHEN ps.extends LIKE '2' THEN {$financialIds['Donation']}
288 WHEN ps.extends LIKE '3' THEN {$financialIds['Member Dues']}
290 WHERE financial_type_id IS NULL";
291 CRM_Core_DAO
::executeQuery($sqlPriceSetUpdate);
293 //here we update price field value rows
294 $sqlPriceFieldValueUpdate = "UPDATE civicrm_price_field_value pfv
295 LEFT JOIN civicrm_membership_type mt ON (pfv.membership_type_id = mt.id)
296 INNER JOIN civicrm_price_field pf ON (pfv.price_field_id = pf.id)
297 INNER JOIN civicrm_price_set ps ON (pf.price_set_id = ps.id)
298 SET pfv.financial_type_id = CASE
299 WHEN pfv.membership_type_id IS NOT NULL THEN mt.financial_type_id
300 WHEN pfv.membership_type_id IS NULL THEN ps.financial_type_id
302 CRM_Core_DAO
::executeQuery($sqlPriceFieldValueUpdate);
307 static function _checkAndMigrateDefaultFinancialTypes() {
308 $modifiedDefaults = FALSE;
309 //insert types if not exists
310 $sqlFetchTypes = "SELECT id, name FROM civicrm_contribution_type
311 WHERE name IN ('Donation', 'Event Fee', 'Member Dues') AND is_active =1;";
312 $daoFetchTypes = CRM_Core_DAO
::executeQuery($sqlFetchTypes);
314 if ($daoFetchTypes->N
< 3) {
315 $modifiedDefaults = TRUE;
316 $insertStatments = array (
317 'Donation' => "('Donation', 0, 1, 1)",
318 'Member' => "('Member Dues', 0, 1, 1)",
319 'Event Fee' => "('Event Fee', 0, 1, 0)",
321 foreach ($insertStatments as $values) {
322 $query = "INSERT INTO civicrm_contribution_type (name, is_reserved, is_active, is_deductible)
324 ON DUPLICATE KEY UPDATE is_active = 1;";
325 CRM_Core_DAO
::executeQuery($query);
328 return $modifiedDefaults;
331 function createFinancialRecords() {
332 $upgrade = new CRM_Upgrade_Form();
334 // update civicrm_entity_financial_trxn.amount = civicrm_financial_trxn.total_amount
335 $query = "UPDATE civicrm_entity_financial_trxn ceft
336 LEFT JOIN civicrm_financial_trxn cft ON cft.id = ceft.financial_trxn_id
337 SET ceft.amount = total_amount
338 WHERE cft.net_amount IS NOT NULL AND ceft.entity_table = 'civicrm_contribution';";
339 CRM_Core_DAO
::executeQuery($query);
341 $contributionStatus = CRM_Contribute_PseudoConstant
::contributionStatus(NULL, 'name');
342 $completedStatus = array_search('Completed', $contributionStatus);
343 $pendingStatus = array_search('Pending', $contributionStatus);
344 $cancelledStatus = array_search('Cancelled', $contributionStatus);
345 $queryParams = array(
346 1 => array($completedStatus, 'Integer'),
347 2 => array($pendingStatus, 'Integer'),
348 3 => array($cancelledStatus, 'Integer')
351 $accountType = key(CRM_Core_PseudoConstant
::accountOptionValues('financial_account_type', NULL, " AND v.name = 'Asset' "));
352 $financialAccountId =
353 CRM_Core_DAO
::singleValueQuery("SELECT id FROM civicrm_financial_account WHERE is_default = 1 AND financial_account_type_id = {$accountType}");
355 $accountRelationsips = CRM_Core_PseudoConstant
::accountOptionValues('account_relationship', NULL);
357 $accountsReceivableAccount = array_search('Accounts Receivable Account is', $accountRelationsips);
358 $incomeAccountIs = array_search('Income Account is', $accountRelationsips);
359 $assetAccountIs = array_search('Asset Account is', $accountRelationsips);
360 $expenseAccountIs = array_search('Expense Account is', $accountRelationsips);
362 $financialItemStatus = CRM_Core_PseudoConstant
::accountOptionValues('financial_item_status');
363 $unpaidStatus = array_search('Unpaid', $financialItemStatus);
364 $paidStatus = array_search('Paid', $financialItemStatus);
366 $validCurrencyCodes = CRM_Core_PseudoConstant
::currencyCode();
367 $validCurrencyCodes = implode("','", $validCurrencyCodes);
368 $config = CRM_Core_Config
::singleton();
369 $defaultCurrency = $config->defaultCurrency
;
370 $now = date( 'YmdHis' );
372 //adding financial_trxn records and entity_financial_trxn records related to contribution
373 //Add temp column for easy entry in entity_financial_trxn
374 $sql = "ALTER TABLE civicrm_financial_trxn ADD COLUMN contribution_id INT DEFAULT NULL";
375 CRM_Core_DAO
::executeQuery($sql);
377 //pending pay later status handling
379 INSERT INTO civicrm_financial_trxn
380 (contribution_id, payment_instrument_id, currency, total_amount, net_amount, fee_amount, trxn_id, status_id,
381 check_number, to_financial_account_id, from_financial_account_id, trxn_date)
383 SELECT con.id as contribution_id, con.payment_instrument_id, IF(con.currency IN ('{$validCurrencyCodes}'), con.currency, '{$defaultCurrency}')
384 as currency, con.total_amount, con.net_amount, con.fee_amount, con.trxn_id, con.contribution_status_id,
385 con.check_number, efa.financial_account_id as to_financial_account_id, NULL as from_financial_account_id,
386 REPLACE(REPLACE(REPLACE(
388 WHEN con.receive_date IS NOT NULL THEN
390 WHEN con.receipt_date IS NOT NULL THEN
395 , '-', ''), ':', ''), ' ', '') as trxn_date
396 FROM civicrm_contribution con
397 LEFT JOIN civicrm_entity_financial_account efa
398 ON (con.financial_type_id = efa.entity_id AND efa.entity_table = 'civicrm_financial_type'
399 AND efa.account_relationship = {$accountsReceivableAccount})
400 WHERE con.is_pay_later = 1 AND con.contribution_status_id = {$pendingStatus}";
401 CRM_Core_DAO
::executeQuery($sql);
403 //create a temp table to hold financial account id related to payment instruments
404 $tempTableName1 = CRM_Core_DAO
::createTempTableName();
406 $sql = "CREATE TEMPORARY TABLE {$tempTableName1}
408 SELECT ceft.financial_account_id financial_account_id, cov.value as instrument_id
409 FROM civicrm_entity_financial_account ceft
410 INNER JOIN civicrm_option_value cov ON cov.id = ceft.entity_id AND ceft.entity_table = 'civicrm_option_value'
411 INNER JOIN civicrm_option_group cog ON cog.id = cov.option_group_id
412 WHERE cog.name = 'payment_instrument'";
413 CRM_Core_DAO
::executeQuery($sql);
415 $sql = "ALTER TABLE {$tempTableName1} ADD INDEX index_instrument_id (instrument_id);";
416 CRM_Core_DAO
::executeQuery($sql);
418 //create temp table to process completed / cancelled contribution
419 $tempTableName2 = CRM_Core_DAO
::createTempTableName();
420 $sql = "CREATE TEMPORARY TABLE {$tempTableName2}
422 SELECT con.id as contribution_id, con.payment_instrument_id, IF(con.currency IN ('{$validCurrencyCodes}'), con.currency, '{$defaultCurrency}') as currency,
423 con.total_amount, con.net_amount, con.fee_amount, con.trxn_id, con.contribution_status_id, con.check_number, NULL as from_financial_account_id,
424 REPLACE(REPLACE(REPLACE(
426 WHEN con.receive_date IS NOT NULL THEN
428 WHEN con.receipt_date IS NOT NULL THEN
433 , '-', ''), ':', ''), ' ', '') as trxn_date,
435 WHEN con.payment_instrument_id IS NULL THEN
436 {$financialAccountId}
437 WHEN con.payment_instrument_id IS NOT NULL THEN
438 tpi.financial_account_id
439 END as to_financial_account_id,
440 IF(eft.financial_trxn_id IS NULL, 'insert', eft.financial_trxn_id) as action
441 FROM civicrm_contribution con
442 LEFT JOIN civicrm_entity_financial_trxn eft
443 ON (eft.entity_table = 'civicrm_contribution' AND eft.entity_id = con.id)
444 LEFT JOIN {$tempTableName1} tpi
445 ON con.payment_instrument_id = tpi.instrument_id
446 WHERE con.contribution_status_id IN ({$completedStatus}, {$cancelledStatus})";
447 CRM_Core_DAO
::executeQuery($sql);
449 $sql = "ALTER TABLE {$tempTableName2} ADD INDEX index_action (action);";
450 CRM_Core_DAO
::executeQuery($sql);
452 //handling for completed contribution and cancelled contribution
453 //insertion of new records
455 INSERT INTO civicrm_financial_trxn
456 (contribution_id, payment_instrument_id, currency, total_amount, net_amount, fee_amount, trxn_id, status_id, check_number,
457 to_financial_account_id, from_financial_account_id, trxn_date)
458 SELECT tempI.contribution_id, tempI.payment_instrument_id, tempI.currency, tempI.total_amount, tempI.net_amount,
459 tempI.fee_amount, tempI.trxn_id, tempI.contribution_status_id, tempI.check_number,
460 tempI.to_financial_account_id, tempI.from_financial_account_id, tempI.trxn_date
461 FROM {$tempTableName2} tempI
462 WHERE tempI.action = 'insert';";
463 CRM_Core_DAO
::executeQuery($sql);
465 //update of existing records
467 UPDATE civicrm_financial_trxn ft
468 INNER JOIN {$tempTableName2} tempU
469 ON (tempU.action != 'insert' AND ft.id = tempU.action)
470 SET ft.from_financial_account_id = NULL,
471 ft.to_financial_account_id = tempU.to_financial_account_id,
472 ft.status_id = tempU.contribution_status_id,
473 ft.payment_instrument_id = tempU.payment_instrument_id,
474 ft.check_number = tempU.check_number,
475 ft.contribution_id = tempU.contribution_id;";
476 CRM_Core_DAO
::executeQuery($sql);
478 //insert the -ve transaction rows for cancelled contributions
480 INSERT INTO civicrm_financial_trxn
481 (contribution_id, payment_instrument_id, currency, total_amount, net_amount, fee_amount, trxn_id, status_id,
482 check_number, to_financial_account_id, from_financial_account_id, trxn_date)
483 SELECT ft.contribution_id, ft.payment_instrument_id, ft.currency, -ft.total_amount, ft.net_amount, ft.fee_amount, ft.trxn_id,
484 ft.status_id, ft.check_number, ft.to_financial_account_id, ft.from_financial_account_id, ft.trxn_date
485 FROM civicrm_financial_trxn ft
486 WHERE ft.status_id = {$cancelledStatus};";
487 CRM_Core_DAO
::executeQuery($sql);
489 //inserting entity financial trxn entries if its not present in entity_financial_trxn for completed and pending contribution statuses
490 //this also handles +ve and -ve both transaction entries for a cancelled contribution
492 INSERT INTO civicrm_entity_financial_trxn (entity_table, entity_id, financial_trxn_id, amount)
493 SELECT 'civicrm_contribution', ft.contribution_id, ft.id, ft.total_amount as amount
494 FROM civicrm_financial_trxn ft
495 WHERE contribution_id IS NOT NULL AND
496 ft.id NOT IN (SELECT financial_trxn_id
497 FROM civicrm_entity_financial_trxn
498 WHERE entity_table = 'civicrm_contribution'
499 AND entity_id = ft.contribution_id)";
500 CRM_Core_DAO
::executeQuery($sql);
501 //end of adding financial_trxn records and entity_financial_trxn records related to contribution
503 //update all linked line_item rows
504 // set line_item.financial_type_id = contribution.financial_type_id if contribution page id is null and not participant line item
505 // set line_item.financial_type_id = price_field_value.financial_type_id if contribution page id is set and not participant line item
506 // set line_item.financial_type_id = event.financial_type_id if its participant line item and line_item.price_field_value_id is null
507 // 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
508 $updateLineItemSql = "
509 UPDATE civicrm_line_item li
510 LEFT JOIN civicrm_contribution con
511 ON (li.entity_id = con.id AND li.entity_table = 'civicrm_contribution')
512 LEFT JOIN civicrm_price_field_value cpfv
513 ON li.price_field_value_id = cpfv.id
514 LEFT JOIN civicrm_participant cp
515 ON (li.entity_id = cp.id AND li.entity_table = 'civicrm_participant')
516 LEFT JOIN civicrm_event ce
517 ON ce.id = cp.event_id
518 SET li.financial_type_id = CASE
519 WHEN (con.contribution_page_id IS NULL || li.price_field_value_id IS NULL) AND cp.id IS NULL THEN
520 con.financial_type_id
521 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
522 cpfv.financial_type_id
523 WHEN cp.id IS NOT NULL AND li.price_field_value_id IS NULL THEN
526 CRM_Core_DAO
::executeQuery($updateLineItemSql, $queryParams);
528 //add the financial_item entries
529 //add a temp column so that inserting entity_financial_trxn entries gets easy
530 $sql = "ALTER TABLE civicrm_financial_item ADD COLUMN f_trxn_id INT DEFAULT NULL";
531 CRM_Core_DAO
::executeQuery($sql);
533 //add financial_item entries for contribution completed / pending pay later / cancelled
534 $contributionlineItemSql = "
535 INSERT INTO civicrm_financial_item
536 (transaction_date, contact_id, amount, currency, entity_table, entity_id, description, status_id, financial_account_id, f_trxn_id)
538 SELECT REPLACE(REPLACE(REPLACE(ft.trxn_date, '-', ''), ':', ''), ' ', ''), con.contact_id,
539 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',
540 li.id as line_item_id, li.label as line_item_label,
541 IF(con.contribution_status_id = {$pendingStatus}, {$unpaidStatus}, {$paidStatus}) as status_id, efa.financial_account_id as financial_account_id,
543 FROM civicrm_line_item li
544 INNER JOIN civicrm_contribution con
545 ON (li.entity_id = con.id AND li.entity_table = 'civicrm_contribution')
546 INNER JOIN civicrm_financial_trxn ft
547 ON (con.id = ft.contribution_id)
548 LEFT JOIN civicrm_entity_financial_account efa
549 ON (li.financial_type_id = efa.entity_id AND efa.entity_table = 'civicrm_financial_type'
550 AND efa.account_relationship = {$incomeAccountIs})
551 WHERE con.contribution_status_id IN (%1, %3) OR (con.is_pay_later = 1 AND con.contribution_status_id = %2)";
552 CRM_Core_DAO
::executeQuery($contributionlineItemSql, $queryParams);
554 //add financial_item entries for event
555 $participantLineItemSql = "
556 INSERT INTO civicrm_financial_item
557 (transaction_date, contact_id, amount, currency, entity_table, entity_id, description, status_id, financial_account_id, f_trxn_id)
559 SELECT REPLACE(REPLACE(REPLACE(ft.trxn_date, '-', ''), ':', ''), ' ', ''), con.contact_id,
560 IF(ft.total_amount < 0 AND con.contribution_status_id = %3, -li.line_total, li.line_total) as line_total,
561 con.currency, 'civicrm_line_item', li.id as line_item_id, li.label as line_item_label,
562 IF(con.contribution_status_id = {$pendingStatus}, {$unpaidStatus}, {$paidStatus}) as status_id,
563 efa.financial_account_id as financial_account_id, ft.id as f_trxn_id
564 FROM civicrm_line_item li
565 INNER JOIN civicrm_participant par
566 ON (li.entity_id = par.id AND li.entity_table = 'civicrm_participant')
567 INNER JOIN civicrm_participant_payment pp
568 ON (pp.participant_id = par.id)
569 INNER JOIN civicrm_contribution con
570 ON (pp.contribution_id = con.id)
571 INNER JOIN civicrm_financial_trxn ft
572 ON (con.id = ft.contribution_id)
573 LEFT JOIN civicrm_entity_financial_account efa
574 ON (li.financial_type_id = efa.entity_id AND
575 efa.entity_table = 'civicrm_financial_type' AND efa.account_relationship = {$incomeAccountIs})
576 WHERE con.contribution_status_id IN (%1, %3) OR (con.is_pay_later = 1 AND con.contribution_status_id = %2)";
577 CRM_Core_DAO
::executeQuery($participantLineItemSql, $queryParams);
579 //fee handling for contributions
580 //insert fee entries in financial_trxn for contributions
581 $sql = "ALTER TABLE civicrm_financial_trxn ADD COLUMN is_fee TINYINT DEFAULT NULL";
582 CRM_Core_DAO
::executeQuery($sql);
585 INSERT INTO civicrm_financial_trxn
586 (contribution_id, payment_instrument_id, currency, total_amount, net_amount, fee_amount, trxn_id, status_id, check_number,
587 to_financial_account_id, from_financial_account_id, trxn_date, payment_processor_id, is_fee)
589 SELECT con.id, ft.payment_instrument_id, ft.currency, ft.fee_amount, NULL, NULL, ft.trxn_id, %1 as status_id,
590 ft.check_number, efaFT.financial_account_id as to_financial_account_id, CASE
591 WHEN efaPP.financial_account_id IS NOT NULL THEN
592 efaPP.financial_account_id
593 WHEN tpi.financial_account_id IS NOT NULL THEN
594 tpi.financial_account_id
596 {$financialAccountId}
597 END as from_financial_account_id, ft.trxn_date, ft.payment_processor_id, 1 as is_fee
598 FROM civicrm_contribution con
599 INNER JOIN civicrm_financial_trxn ft
600 ON (ft.contribution_id = con.id)
601 LEFT JOIN civicrm_entity_financial_account efaFT
602 ON (con.financial_type_id = efaFT.entity_id AND efaFT.entity_table = 'civicrm_financial_type'
603 AND efaFT.account_relationship = {$expenseAccountIs})
604 LEFT JOIN civicrm_entity_financial_account efaPP
605 ON (ft.payment_processor_id = efaPP.entity_id AND efaPP.entity_table = 'civicrm_payment_processor'
606 AND efaPP.account_relationship = {$assetAccountIs})
607 LEFT JOIN {$tempTableName1} tpi
608 ON ft.payment_instrument_id = tpi.instrument_id
609 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))
611 CRM_Core_DAO
::executeQuery($sql, $queryParams);
613 //link financial_trxn to contribution
615 INSERT INTO civicrm_entity_financial_trxn
616 (entity_table, entity_id, financial_trxn_id, amount)
617 SELECT 'civicrm_contribution', ft.contribution_id, ft.id, ft.total_amount
618 FROM civicrm_financial_trxn ft
619 WHERE ft.is_fee = 1";
620 CRM_Core_DAO
::executeQuery($sql);
622 //add fee related entries to financial item table
623 $domainId = CRM_Core_Config
::domainID();
624 $domainContactId = CRM_Core_DAO
::getFieldValue('CRM_Core_DAO_Domain', $domainId, 'contact_id');
626 INSERT INTO civicrm_financial_item
627 (transaction_date, contact_id, amount, currency, entity_table, entity_id, description, status_id, financial_account_id, f_trxn_id)
628 SELECT ft.trxn_date, {$domainContactId} as contact_id, ft.total_amount, ft.currency, 'civicrm_financial_trxn', ft.id,
629 'Fee', {$paidStatus} as status_id, ft.to_financial_account_id as financial_account_id, ft.id as f_trxn_id
630 FROM civicrm_financial_trxn ft
631 WHERE ft.is_fee = 1;";
632 CRM_Core_DAO
::executeQuery($sql);
634 //add entries to entity_financial_trxn table
636 INSERT INTO civicrm_entity_financial_trxn (entity_table, entity_id, financial_trxn_id, amount)
637 SELECT 'civicrm_financial_item' as entity_table, fi.id as entity_id, fi.f_trxn_id as financial_trxn_id, fi.amount
638 FROM civicrm_financial_item fi";
639 CRM_Core_DAO
::executeQuery($sql);
641 //drop the temparory columns
642 $sql = "ALTER TABLE civicrm_financial_trxn
643 DROP COLUMN contribution_id,
644 DROP COLUMN is_fee;";
645 CRM_Core_DAO
::executeQuery($sql);
647 $sql = "ALTER TABLE civicrm_financial_item DROP f_trxn_id";
648 CRM_Core_DAO
::executeQuery($sql);
653 function createDomainContacts() {
654 $domainParams = $context = array();
656 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',
657 ADD CONSTRAINT `FK_civicrm_domain_contact_id` FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact` (`id`);";
658 CRM_Core_DAO
::executeQuery($query, CRM_Core_DAO
::$_nullArray, TRUE, NULL, FALSE, FALSE);
660 $query = 'SELECT cd.id, cd.name, ce.email FROM `civicrm_domain` cd
661 LEFT JOIN civicrm_loc_block clb ON clb.id = cd. loc_block_id
662 LEFT JOIN civicrm_email ce ON ce.id = clb.email_id ;' ;
663 $dao = CRM_Core_DAO
::executeQuery($query);
664 while($dao->fetch()) {
666 'sort_name' => $dao->name
,
667 'display_name' => $dao->name
,
668 'legal_name' => $dao->name
,
669 'organization_name' => $dao->name
,
670 'contact_type' => 'Organization'
672 $query = "SELECT cc.id FROM `civicrm_contact` cc
673 LEFT JOIN civicrm_email ce ON ce.contact_id = cc.id
674 WHERE cc.contact_type = 'Organization' AND cc.organization_name = '{$dao->name}' ";
676 $query .= " AND ce.email = '{$dao->email}' ";
678 $contactID = CRM_Core_DAO
::singleValueQuery($query);
679 $context[1] = $dao->name
;
680 if (empty($contactID)) {
681 $contact = CRM_Contact_BAO_Contact
::add($params);
682 $contactID = $contact->id
;
683 $context[0] = 'added';
686 $context[0] = 'merged';
688 $domainParams['contact_id'] = $contactID;
689 CRM_Core_BAO_Domain
::edit($domainParams, $dao->id
);
694 function task_4_3_alpha1_checkDBConstraints() {
695 //checking whether the foreign key exists before dropping it CRM-11260
696 $config = CRM_Core_Config
::singleton();
697 $dbUf = DB
::parseDSN($config->dsn
);
700 'autorenewal_msg_id' => array('tableName' => 'civicrm_membership_type', 'fkey' => 'FK_civicrm_membership_autorenewal_msg_id'),
701 'to_account_id' => array('tableName' => 'civicrm_financial_trxn', 'constraintName' => 'civicrm_financial_trxn_ibfk_2'),
702 'from_account_id' => array('tableName' => 'civicrm_financial_trxn', 'constraintName' => 'civicrm_financial_trxn_ibfk_1'),
703 'contribution_type_id' => array('tableName' => 'civicrm_contribution_recur', 'fkey' => 'FK_civicrm_contribution_recur_contribution_type_id'),
705 $query = "SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
706 WHERE table_name = 'civicrm_contribution_recur'
707 AND constraint_name = 'FK_civicrm_contribution_recur_contribution_type_id'
708 AND TABLE_SCHEMA = '{$dbUf['database']}'";
710 $dao = CRM_Core_DAO
::executeQuery($query, $params, TRUE, NULL, FALSE, FALSE);
711 foreach($tables as $columnName => $value){
712 if ($value['tableName'] == 'civicrm_membership_type' ||
$value['tableName'] == 'civicrm_contribution_recur') {
713 $foreignKeyExists = CRM_Core_DAO
::checkConstraintExists($value['tableName'], $value['fkey']);
714 $fKey = $value['fkey'];
716 $foreignKeyExists = CRM_Core_DAO
::checkFKConstraintInFormat($value['tableName'], $columnName);
717 $fKey = "`FK_{$value['tableName']}_{$columnName}`";
719 if ($foreignKeyExists ||
$value['tableName'] == 'civicrm_financial_trxn') {
720 if ($value['tableName'] != 'civicrm_contribution_recur' ||
($value['tableName'] == 'civicrm_contribution_recur' && $dao->N
)) {
721 $constraintName = $foreignKeyExists ?
$fKey : $value['constraintName'];
722 CRM_Core_DAO
::executeQuery("ALTER TABLE {$value['tableName']} DROP FOREIGN KEY {$constraintName}", $params, TRUE, NULL, FALSE, FALSE);
724 CRM_Core_DAO
::executeQuery("ALTER TABLE {$value['tableName']} DROP INDEX {$fKey}", $params, TRUE, NULL, FALSE, FALSE);
727 // check if column contact_id is present or not in civicrm_financial_account
728 $fieldExists = CRM_Core_DAO
::checkFieldExists('civicrm_financial_account', 'contact_id', FALSE);
730 $query = "ALTER TABLE civicrm_financial_account ADD `contact_id` int(10) unsigned DEFAULT NULL COMMENT 'Version identifier of financial_type' AFTER `name`, ADD CONSTRAINT `FK_civicrm_financial_account_contact_id` FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact`(id);";
731 CRM_Core_DAO
::executeQuery($query, $params, TRUE, NULL, FALSE, FALSE);
736 * Read creation and modification times from civicrm_log; add
737 * them to civicrm_contact.
739 function convertTimestamps(CRM_Queue_TaskContext
$ctx, $startId, $endId) {
741 SELECT entity_id, min(modified_date) AS created, max(modified_date) AS modified
743 WHERE entity_table = 'civicrm_contact'
744 AND entity_id BETWEEN %1 AND %2
748 1 => array($startId, 'Integer'),
749 2 => array($endId, 'Integer'),
751 $dao = CRM_Core_DAO
::executeQuery($sql, $params);
752 while ($dao->fetch()) {
753 // FIXME civicrm_log.modified_date is DATETIME; civicrm_contact.modified_date is TIMESTAMP
754 CRM_Core_DAO
::executeQuery(
755 'UPDATE civicrm_contact SET created_date = %1, modified_date = %2 WHERE id = %3',
757 1 => array($dao->created
, 'String'),
758 2 => array($dao->modified
, 'String'),
759 3 => array($dao->entity_id
, 'Integer'),
768 * replace contribution_type to financial_type in table
769 * civicrm_saved_search and Structure civicrm_report_instance
771 function replaceContributionTypeId(CRM_Queue_TaskContext
$ctx, $query, $table) {
772 $dao = CRM_Core_DAO
::executeQuery($query);
773 while ($dao->fetch()) {
774 $formValues = unserialize($dao->form_values
);
775 foreach (array('contribution_type_id_op', 'contribution_type_id_value', 'contribution_type_id') as $value) {
776 if (array_key_exists($value, $formValues)) {
777 $key = preg_replace('/contribution/', 'financial', $value);
778 $formValues[$key] = $formValues[$value];
779 unset($formValues[$value]);
782 if ($table != 'savedSearch') {
783 foreach (array('fields', 'group_bys') as $value) {
784 if (array_key_exists($value, $formValues)) {
785 if (array_key_exists('contribution_type_id', $formValues[$value])) {
786 $formValues[$value]['financial_type_id'] = $formValues[$value]['contribution_type_id'];
787 unset($formValues[$value]['contribution_type_id']);
789 else if (array_key_exists('contribution_type', $formValues[$value])) {
790 $formValues[$value]['financial_type'] = $formValues[$value]['contribution_type'];
791 unset($formValues[$value]['contribution_type']);
795 if (array_key_exists('order_bys', $formValues)) {
796 foreach ($formValues['order_bys'] as $key => $values) {
797 if (preg_grep('/contribution_type/', $values)) {
798 $formValues['order_bys'][$key]['column'] = preg_replace('/contribution_type/', 'financial_type', $values['column']);
804 if ($table == 'savedSearch') {
805 $saveDao = new CRM_Contact_DAO_SavedSearch();
808 $saveDao = new CRM_Report_DAO_Instance();
810 $saveDao->id
= $dao->id
;
812 if ($table == 'savedSearch') {
813 if (array_key_exists('mapper', $formValues)) {
814 foreach ($formValues['mapper'] as $key => $values) {
815 foreach ($values as $k => $v) {
816 if (preg_grep('/contribution_/', $v)) {
817 $formValues['mapper'][$key][$k] = preg_replace('/contribution_type/', 'financial_type', $v);
822 foreach (array('select_tables', 'where_tables') as $value) {
823 if (preg_match('/contribution_type/', $dao->$value)) {
824 $tempValue = unserialize($dao->$value);
825 if (array_key_exists('civicrm_contribution_type', $tempValue)) {
826 $tempValue['civicrm_financial_type'] = $tempValue['civicrm_contribution_type'];
827 unset($tempValue['civicrm_contribution_type']);
829 $saveDao->$value = serialize($tempValue);
832 if (preg_match('/contribution_type/', $dao->where_clause
)) {
833 $saveDao->where_clause
= preg_replace('/contribution_type/', 'financial_type', $dao->where_clause
);
836 $saveDao->form_values
= serialize($formValues);
844 * Check/Add INDEX CRM-12141
846 * @return bool TRUE for success
848 function task_4_3_x_checkIndexes(CRM_Queue_TaskContext
$ctx) {
849 $query = "SHOW KEYS FROM `civicrm_entity_financial_trxn`
850 WHERE key_name IN ('UI_entity_financial_trxn_entity_table', 'UI_entity_financial_trxn_entity_id');";
851 $dao = CRM_Core_DAO
::executeQuery($query);
853 CRM_Core_DAO
::executeQuery("ALTER TABLE civicrm_entity_financial_trxn
854 ADD INDEX UI_entity_financial_trxn_entity_table (entity_table),
855 ADD INDEX UI_entity_financial_trxn_entity_id (entity_id);");
861 * Update phones CRM-11292
863 * @return bool TRUE for success
865 static function phoneNumeric(CRM_Queue_TaskContext
$ctx) {
866 CRM_Core_DAO
::executeQuery(CRM_Contact_BAO_Contact
::DROP_STRIP_FUNCTION_43
);
867 CRM_Core_DAO
::executeQuery(CRM_Contact_BAO_Contact
::CREATE_STRIP_FUNCTION_43
);
868 CRM_Core_DAO
::executeQuery("UPDATE civicrm_phone SET phone_numeric = civicrm_strip_non_numeric(phone)");
873 * (Queue Task Callback)
875 static function task_4_3_x_runSql(CRM_Queue_TaskContext
$ctx, $rev) {
876 $upgrade = new CRM_Upgrade_Form();
877 $upgrade->processSQL($rev);
883 * Syntatic sugar for adding a task which (a) is in this class and (b) has
886 * After passing the $funcName, you can also pass parameters that will go to
887 * the function. Note that all params must be serializable.
889 protected function addTask($title, $funcName) {
890 $queue = CRM_Queue_Service
::singleton()->load(array(
892 'name' => CRM_Upgrade_Form
::QUEUE_NAME
,
895 $args = func_get_args();
896 $title = array_shift($args);
897 $funcName = array_shift($args);
898 $task = new CRM_Queue_Task(
899 array(get_class($this), $funcName),
903 $queue->createItem($task, array('weight' => -1));