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).');
139 function upgrade_4_3_alpha1($rev) {
140 self
::task_4_3_alpha1_checkDBConstraints();
142 // task to process sql
143 $this->addTask(ts('Upgrade DB to 4.3.alpha1: SQL'), 'task_4_3_x_runSql', $rev);
146 $this->addTask(ts('Populate financial type values for price records'), 'assignFinancialTypeToPriceRecords');
147 //CRM-11514 create financial records for contributions
148 $this->addTask(ts('Create financial records for contributions'), 'createFinancialRecords');
150 $minId = CRM_Core_DAO
::singleValueQuery('SELECT coalesce(min(id),0) FROM civicrm_contact');
151 $maxId = CRM_Core_DAO
::singleValueQuery('SELECT coalesce(max(id),0) FROM civicrm_contact');
152 for ($startId = $minId; $startId <= $maxId; $startId +
= self
::BATCH_SIZE
) {
153 $endId = $startId + self
::BATCH_SIZE
- 1;
154 $title = ts('Upgrade timestamps (%1 => %2)', array(
158 $this->addTask($title, 'convertTimestamps', $startId, $endId);
162 // fix WP access control
163 $config = CRM_Core_Config
::singleton( );
164 if ($config->userFramework
== 'WordPress') {
165 civicrm_wp_set_capabilities( );
168 // Update phones CRM-11292.
169 $this->addTask(ts('Upgrade Phone Numbers'), 'phoneNumeric');
174 function upgrade_4_3_alpha2($rev) {
176 $isColumnPresent = CRM_Core_DAO
::checkFieldExists('civicrm_dedupe_rule_group', 'is_default');
177 if ($isColumnPresent) {
178 CRM_Core_DAO
::executeQuery('ALTER TABLE civicrm_dedupe_rule_group DROP COLUMN is_default');
180 $this->addTask(ts('Upgrade DB to 4.3.alpha2: SQL'), 'task_4_3_x_runSql', $rev);
183 function upgrade_4_3_alpha3($rev) {
184 $this->addTask(ts('Upgrade DB to 4.3.alpha3: SQL'), 'task_4_3_x_runSql', $rev);
187 function upgrade_4_3_beta2($rev) {
188 $this->addTask(ts('Upgrade DB to 4.3.beta2: SQL'), 'task_4_3_x_runSql', $rev);
191 CRM_Core_DAO
::checkTableExists('log_civicrm_line_item') &&
192 CRM_Core_DAO
::checkFieldExists('log_civicrm_line_item', 'label')
194 CRM_Core_DAO
::executeQuery('ALTER TABLE `log_civicrm_line_item` CHANGE `label` `label` VARCHAR(255) NULL DEFAULT NULL');
198 function upgrade_4_3_beta3($rev) {
199 $this->addTask(ts('Upgrade DB to 4.3.beta3: SQL'), 'task_4_3_x_runSql', $rev);
201 $query = "SELECT id, form_values FROM civicrm_report_instance WHERE form_values LIKE '%contribution_type%'";
202 $this->addTask('Replace contribution_type to financial_type in table civicrm_report_instance', 'replaceContributionTypeId', $query, 'reportInstance');
203 $query = "SELECT * FROM civicrm_saved_search WHERE form_values LIKE '%contribution_type%'";
204 $this->addTask('Replace contribution_type to financial_type in table civicrm_saved_search', 'replaceContributionTypeId', $query, 'savedSearch');
208 function assignFinancialTypeToPriceRecords() {
209 $upgrade = new CRM_Upgrade_Form();
210 //here we update price set entries
211 $sqlFinancialIds = "SELECT id, name FROM civicrm_financial_type
212 WHERE name IN ('Donation', 'Event Fee', 'Member Dues');";
213 $daoFinancialIds = CRM_Core_DAO
::executeQuery($sqlFinancialIds);
214 while($daoFinancialIds->fetch()) {
215 $financialIds[$daoFinancialIds->name
] = $daoFinancialIds->id
;
217 $sqlPriceSetUpdate = "UPDATE civicrm_price_set ps
218 SET ps.financial_type_id = CASE
219 WHEN ps.extends LIKE '%1%' THEN {$financialIds['Event Fee']}
220 WHEN ps.extends LIKE '2' THEN {$financialIds['Donation']}
221 WHEN ps.extends LIKE '3' THEN {$financialIds['Member Dues']}
223 WHERE financial_type_id IS NULL";
224 CRM_Core_DAO
::executeQuery($sqlPriceSetUpdate);
226 //here we update price field value rows
227 $sqlPriceFieldValueUpdate = "UPDATE civicrm_price_field_value pfv
228 LEFT JOIN civicrm_membership_type mt ON (pfv.membership_type_id = mt.id)
229 INNER JOIN civicrm_price_field pf ON (pfv.price_field_id = pf.id)
230 INNER JOIN civicrm_price_set ps ON (pf.price_set_id = ps.id)
231 SET pfv.financial_type_id = CASE
232 WHEN pfv.membership_type_id IS NOT NULL THEN mt.financial_type_id
233 WHEN pfv.membership_type_id IS NULL THEN ps.financial_type_id
235 CRM_Core_DAO
::executeQuery($sqlPriceFieldValueUpdate);
240 static function _checkAndMigrateDefaultFinancialTypes() {
241 $modifiedDefaults = FALSE;
242 //insert types if not exists
243 $sqlFetchTypes = "SELECT id, name FROM civicrm_contribution_type
244 WHERE name IN ('Donation', 'Event Fee', 'Member Dues') AND is_active =1;";
245 $daoFetchTypes = CRM_Core_DAO
::executeQuery($sqlFetchTypes);
247 if ($daoFetchTypes->N
< 3) {
248 $modifiedDefaults = TRUE;
249 $insertStatments = array (
250 'Donation' => "('Donation', 0, 1, 1)",
251 'Member' => "('Member Dues', 0, 1, 1)",
252 'Event Fee' => "('Event Fee', 0, 1, 0)",
254 foreach ($insertStatments as $values) {
255 $query = "INSERT INTO civicrm_contribution_type (name, is_reserved, is_active, is_deductible)
257 ON DUPLICATE KEY UPDATE is_active = 1;";
258 CRM_Core_DAO
::executeQuery($query);
261 return $modifiedDefaults;
264 function createFinancialRecords() {
265 $upgrade = new CRM_Upgrade_Form();
267 // update civicrm_entity_financial_trxn.amount = civicrm_financial_trxn.total_amount
268 $query = "UPDATE civicrm_entity_financial_trxn ceft
269 LEFT JOIN civicrm_financial_trxn cft ON cft.id = ceft.financial_trxn_id
270 SET ceft.amount = total_amount
271 WHERE cft.net_amount IS NOT NULL AND ceft.entity_table = 'civicrm_contribution';";
272 CRM_Core_DAO
::executeQuery($query);
274 $contributionStatus = CRM_Contribute_PseudoConstant
::contributionStatus(NULL, 'name');
275 $completedStatus = array_search('Completed', $contributionStatus);
276 $pendingStatus = array_search('Pending', $contributionStatus);
277 $cancelledStatus = array_search('Cancelled', $contributionStatus);
278 $queryParams = array(
279 1 => array($completedStatus, 'Integer'),
280 2 => array($pendingStatus, 'Integer'),
281 3 => array($cancelledStatus, 'Integer')
284 $accountType = key(CRM_Core_PseudoConstant
::accountOptionValues('financial_account_type', NULL, " AND v.name = 'Asset' "));
285 $financialAccountId =
286 CRM_Core_DAO
::singleValueQuery("SELECT id FROM civicrm_financial_account WHERE is_default = 1 AND financial_account_type_id = {$accountType}");
288 $accountRelationsips = CRM_Core_PseudoConstant
::accountOptionValues('account_relationship', NULL);
290 $accountsReceivableAccount = array_search('Accounts Receivable Account is', $accountRelationsips);
291 $incomeAccountIs = array_search('Income Account is', $accountRelationsips);
292 $assetAccountIs = array_search('Asset Account is', $accountRelationsips);
293 $expenseAccountIs = array_search('Expense Account is', $accountRelationsips);
295 $financialItemStatus = CRM_Core_PseudoConstant
::accountOptionValues('financial_item_status');
296 $unpaidStatus = array_search('Unpaid', $financialItemStatus);
297 $paidStatus = array_search('Paid', $financialItemStatus);
299 $validCurrencyCodes = CRM_Core_PseudoConstant
::currencyCode();
300 $validCurrencyCodes = implode("','", $validCurrencyCodes);
301 $config = CRM_Core_Config
::singleton();
302 $defaultCurrency = $config->defaultCurrency
;
303 $now = date( 'YmdHis' );
305 //adding financial_trxn records and entity_financial_trxn records related to contribution
306 //Add temp column for easy entry in entity_financial_trxn
307 $sql = "ALTER TABLE civicrm_financial_trxn ADD COLUMN contribution_id INT DEFAULT NULL";
308 CRM_Core_DAO
::executeQuery($sql);
310 //pending pay later status handling
312 INSERT INTO civicrm_financial_trxn
313 (contribution_id, payment_instrument_id, currency, total_amount, net_amount, fee_amount, trxn_id, status_id,
314 check_number, to_financial_account_id, from_financial_account_id, trxn_date)
316 SELECT con.id as contribution_id, con.payment_instrument_id, IF(con.currency IN ('{$validCurrencyCodes}'), con.currency, '{$defaultCurrency}')
317 as currency, con.total_amount, con.net_amount, con.fee_amount, con.trxn_id, con.contribution_status_id,
318 con.check_number, efa.financial_account_id as to_financial_account_id, NULL as from_financial_account_id,
319 REPLACE(REPLACE(REPLACE(
321 WHEN con.receive_date IS NOT NULL THEN
323 WHEN con.receipt_date IS NOT NULL THEN
328 , '-', ''), ':', ''), ' ', '') as trxn_date
329 FROM civicrm_contribution con
330 LEFT JOIN civicrm_entity_financial_account efa
331 ON (con.financial_type_id = efa.entity_id AND efa.entity_table = 'civicrm_financial_type'
332 AND efa.account_relationship = {$accountsReceivableAccount})
333 WHERE con.is_pay_later = 1 AND con.contribution_status_id = {$pendingStatus}";
334 CRM_Core_DAO
::executeQuery($sql);
336 //create a temp table to hold financial account id related to payment instruments
337 $tempTableName1 = CRM_Core_DAO
::createTempTableName();
339 $sql = "CREATE TEMPORARY TABLE {$tempTableName1}
341 SELECT ceft.financial_account_id financial_account_id, cov.value as instrument_id
342 FROM civicrm_entity_financial_account ceft
343 INNER JOIN civicrm_option_value cov ON cov.id = ceft.entity_id AND ceft.entity_table = 'civicrm_option_value'
344 INNER JOIN civicrm_option_group cog ON cog.id = cov.option_group_id
345 WHERE cog.name = 'payment_instrument'";
346 CRM_Core_DAO
::executeQuery($sql);
348 //create temp table to process completed / cancelled contribution
349 $tempTableName2 = CRM_Core_DAO
::createTempTableName();
350 $sql = "CREATE TEMPORARY TABLE {$tempTableName2}
352 SELECT con.id as contribution_id, con.payment_instrument_id, IF(con.currency IN ('{$validCurrencyCodes}'), con.currency, '{$defaultCurrency}') as currency,
353 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,
354 REPLACE(REPLACE(REPLACE(
356 WHEN con.receive_date IS NOT NULL THEN
358 WHEN con.receipt_date IS NOT NULL THEN
363 , '-', ''), ':', ''), ' ', '') as trxn_date,
365 WHEN con.payment_instrument_id IS NULL THEN
366 {$financialAccountId}
367 WHEN con.payment_instrument_id IS NOT NULL THEN
368 tpi.financial_account_id
369 END as to_financial_account_id,
370 IF(eft.financial_trxn_id IS NULL, 'insert', eft.financial_trxn_id) as action
371 FROM civicrm_contribution con
372 LEFT JOIN civicrm_entity_financial_trxn eft
373 ON (eft.entity_table = 'civicrm_contribution' AND eft.entity_id = con.id)
374 LEFT JOIN {$tempTableName1} tpi
375 ON con.payment_instrument_id = tpi.instrument_id
376 WHERE con.contribution_status_id IN ({$completedStatus}, {$cancelledStatus})";
377 CRM_Core_DAO
::executeQuery($sql);
379 //handling for completed contribution and cancelled contribution
380 //insertion of new records
382 INSERT INTO civicrm_financial_trxn
383 (contribution_id, payment_instrument_id, currency, total_amount, net_amount, fee_amount, trxn_id, status_id, check_number,
384 to_financial_account_id, from_financial_account_id, trxn_date)
385 SELECT tempI.contribution_id, tempI.payment_instrument_id, tempI.currency, tempI.total_amount, tempI.net_amount,
386 tempI.fee_amount, tempI.trxn_id, tempI.contribution_status_id, tempI.check_number,
387 tempI.to_financial_account_id, tempI.from_financial_account_id, tempI.trxn_date
388 FROM {$tempTableName2} tempI
389 WHERE tempI.action = 'insert';";
390 CRM_Core_DAO
::executeQuery($sql);
392 //update of existing records
394 UPDATE civicrm_financial_trxn ft
395 INNER JOIN {$tempTableName2} tempU
396 ON (tempU.action != 'insert' AND ft.id = tempU.action)
397 SET ft.from_financial_account_id = NULL,
398 ft.to_financial_account_id = tempU.to_financial_account_id,
399 ft.status_id = tempU.contribution_status_id,
400 ft.payment_instrument_id = tempU.payment_instrument_id,
401 ft.check_number = tempU.check_number,
402 ft.contribution_id = tempU.contribution_id;";
403 CRM_Core_DAO
::executeQuery($sql);
405 //insert the -ve transaction rows for cancelled contributions
407 INSERT INTO civicrm_financial_trxn
408 (contribution_id, payment_instrument_id, currency, total_amount, net_amount, fee_amount, trxn_id, status_id,
409 check_number, to_financial_account_id, from_financial_account_id, trxn_date)
410 SELECT ft.contribution_id, ft.payment_instrument_id, ft.currency, -ft.total_amount, ft.net_amount, ft.fee_amount, ft.trxn_id,
411 ft.status_id, ft.check_number, ft.to_financial_account_id, ft.from_financial_account_id, ft.trxn_date
412 FROM civicrm_financial_trxn ft
413 WHERE ft.status_id = {$cancelledStatus};";
414 CRM_Core_DAO
::executeQuery($sql);
416 //inserting entity financial trxn entries if its not present in entity_financial_trxn for completed and pending contribution statuses
417 //this also handles +ve and -ve both transaction entries for a cancelled contribution
419 INSERT INTO civicrm_entity_financial_trxn (entity_table, entity_id, financial_trxn_id, amount)
420 SELECT 'civicrm_contribution', ft.contribution_id, ft.id, ft.total_amount as amount
421 FROM civicrm_financial_trxn ft
422 WHERE contribution_id IS NOT NULL AND
423 ft.id NOT IN (SELECT financial_trxn_id
424 FROM civicrm_entity_financial_trxn
425 WHERE entity_table = 'civicrm_contribution'
426 AND entity_id = ft.contribution_id)";
427 CRM_Core_DAO
::executeQuery($sql);
428 //end of adding financial_trxn records and entity_financial_trxn records related to contribution
430 //update all linked line_item rows
431 // set line_item.financial_type_id = contribution.financial_type_id if contribution page id is null and not participant line item
432 // set line_item.financial_type_id = price_field_value.financial_type_id if contribution page id is set and not participant line item
433 // set line_item.financial_type_id = event.financial_type_id if its participant line item and line_item.price_field_value_id is null
434 // 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
435 $updateLineItemSql = "
436 UPDATE civicrm_line_item li
437 LEFT JOIN civicrm_contribution con
438 ON (li.entity_id = con.id AND li.entity_table = 'civicrm_contribution')
439 LEFT JOIN civicrm_price_field_value cpfv
440 ON li.price_field_value_id = cpfv.id
441 LEFT JOIN civicrm_participant cp
442 ON (li.entity_id = cp.id AND li.entity_table = 'civicrm_participant')
443 LEFT JOIN civicrm_event ce
444 ON ce.id = cp.event_id
445 SET li.financial_type_id = CASE
446 WHEN (con.contribution_page_id IS NULL || li.price_field_value_id IS NULL) AND cp.id IS NULL THEN
447 con.financial_type_id
448 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
449 cpfv.financial_type_id
450 WHEN cp.id IS NOT NULL AND li.price_field_value_id IS NULL THEN
453 CRM_Core_DAO
::executeQuery($updateLineItemSql, $queryParams);
455 //add the financial_item entries
456 //add a temp column so that inserting entity_financial_trxn entries gets easy
457 $sql = "ALTER TABLE civicrm_financial_item ADD COLUMN f_trxn_id INT DEFAULT NULL";
458 CRM_Core_DAO
::executeQuery($sql);
460 //add financial_item entries for contribution completed / pending pay later / cancelled
461 $contributionlineItemSql = "
462 INSERT INTO civicrm_financial_item
463 (transaction_date, contact_id, amount, currency, entity_table, entity_id, description, status_id, financial_account_id, f_trxn_id)
465 SELECT REPLACE(REPLACE(REPLACE(ft.trxn_date, '-', ''), ':', ''), ' ', ''), con.contact_id,
466 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',
467 li.id as line_item_id, li.label as line_item_label,
468 IF(con.contribution_status_id = {$pendingStatus}, {$unpaidStatus}, {$paidStatus}) as status_id, efa.financial_account_id as financial_account_id,
470 FROM civicrm_line_item li
471 INNER JOIN civicrm_contribution con
472 ON (li.entity_id = con.id AND li.entity_table = 'civicrm_contribution')
473 INNER JOIN civicrm_financial_trxn ft
474 ON (con.id = ft.contribution_id)
475 LEFT JOIN civicrm_entity_financial_account efa
476 ON (li.financial_type_id = efa.entity_id AND efa.entity_table = 'civicrm_financial_type'
477 AND efa.account_relationship = {$incomeAccountIs})
478 WHERE con.contribution_status_id IN (%1, %3) OR (con.is_pay_later = 1 AND con.contribution_status_id = %2)";
479 CRM_Core_DAO
::executeQuery($contributionlineItemSql, $queryParams);
481 //add financial_item entries for event
482 $participantLineItemSql = "
483 INSERT INTO civicrm_financial_item
484 (transaction_date, contact_id, amount, currency, entity_table, entity_id, description, status_id, financial_account_id, f_trxn_id)
486 SELECT REPLACE(REPLACE(REPLACE(ft.trxn_date, '-', ''), ':', ''), ' ', ''), con.contact_id,
487 IF(ft.total_amount < 0 AND con.contribution_status_id = %3, -li.line_total, li.line_total) as line_total,
488 con.currency, 'civicrm_line_item', li.id as line_item_id, li.label as line_item_label,
489 IF(con.contribution_status_id = {$pendingStatus}, {$unpaidStatus}, {$paidStatus}) as status_id,
490 efa.financial_account_id as financial_account_id, ft.id as f_trxn_id
491 FROM civicrm_line_item li
492 INNER JOIN civicrm_participant par
493 ON (li.entity_id = par.id AND li.entity_table = 'civicrm_participant')
494 INNER JOIN civicrm_participant_payment pp
495 ON (pp.participant_id = par.id)
496 INNER JOIN civicrm_contribution con
497 ON (pp.contribution_id = con.id)
498 INNER JOIN civicrm_financial_trxn ft
499 ON (con.id = ft.contribution_id)
500 LEFT JOIN civicrm_entity_financial_account efa
501 ON (li.financial_type_id = efa.entity_id AND
502 efa.entity_table = 'civicrm_financial_type' AND efa.account_relationship = {$incomeAccountIs})
503 WHERE con.contribution_status_id IN (%1, %3) OR (con.is_pay_later = 1 AND con.contribution_status_id = %2)";
504 CRM_Core_DAO
::executeQuery($participantLineItemSql, $queryParams);
506 //fee handling for contributions
507 //insert fee entries in financial_trxn for contributions
508 $sql = "ALTER TABLE civicrm_financial_trxn ADD COLUMN is_fee TINYINT DEFAULT NULL";
509 CRM_Core_DAO
::executeQuery($sql);
512 INSERT INTO civicrm_financial_trxn
513 (contribution_id, payment_instrument_id, currency, total_amount, net_amount, fee_amount, trxn_id, status_id, check_number,
514 to_financial_account_id, from_financial_account_id, trxn_date, payment_processor_id, is_fee)
516 SELECT con.id, ft.payment_instrument_id, ft.currency, ft.fee_amount, NULL, NULL, ft.trxn_id, %1 as status_id,
517 ft.check_number, efaFT.financial_account_id as to_financial_account_id, CASE
518 WHEN efaPP.financial_account_id IS NOT NULL THEN
519 efaPP.financial_account_id
520 WHEN tpi.financial_account_id IS NOT NULL THEN
521 tpi.financial_account_id
523 {$financialAccountId}
524 END as from_financial_account_id, ft.trxn_date, ft.payment_processor_id, 1 as is_fee
525 FROM civicrm_contribution con
526 INNER JOIN civicrm_financial_trxn ft
527 ON (ft.contribution_id = con.id)
528 LEFT JOIN civicrm_entity_financial_account efaFT
529 ON (con.financial_type_id = efaFT.entity_id AND efaFT.entity_table = 'civicrm_financial_type'
530 AND efaFT.account_relationship = {$expenseAccountIs})
531 LEFT JOIN civicrm_entity_financial_account efaPP
532 ON (ft.payment_processor_id = efaPP.entity_id AND efaPP.entity_table = 'civicrm_payment_processor'
533 AND efaPP.account_relationship = {$assetAccountIs})
534 LEFT JOIN {$tempTableName1} tpi
535 ON ft.payment_instrument_id = tpi.instrument_id
536 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))
538 CRM_Core_DAO
::executeQuery($sql, $queryParams);
540 //link financial_trxn to contribution
542 INSERT INTO civicrm_entity_financial_trxn
543 (entity_table, entity_id, financial_trxn_id, amount)
544 SELECT 'civicrm_contribution', ft.contribution_id, ft.id, ft.total_amount
545 FROM civicrm_financial_trxn ft
546 WHERE ft.is_fee = 1";
547 CRM_Core_DAO
::executeQuery($sql);
549 //add fee related entries to financial item table
550 $domainId = CRM_Core_Config
::domainID();
551 $domainContactId = CRM_Core_DAO
::getFieldValue('CRM_Core_DAO_Domain', $domainId, 'contact_id');
553 INSERT INTO civicrm_financial_item
554 (transaction_date, contact_id, amount, currency, entity_table, entity_id, description, status_id, financial_account_id, f_trxn_id)
555 SELECT ft.trxn_date, {$domainContactId} as contact_id, ft.total_amount, ft.currency, 'civicrm_financial_trxn', ft.id,
556 'Fee', {$paidStatus} as status_id, ft.to_financial_account_id as financial_account_id, ft.id as f_trxn_id
557 FROM civicrm_financial_trxn ft
558 WHERE ft.is_fee = 1;";
559 CRM_Core_DAO
::executeQuery($sql);
561 //add entries to entity_financial_trxn table
563 INSERT INTO civicrm_entity_financial_trxn (entity_table, entity_id, financial_trxn_id, amount)
564 SELECT 'civicrm_financial_item' as entity_table, fi.id as entity_id, fi.f_trxn_id as financial_trxn_id, fi.amount
565 FROM civicrm_financial_item fi";
566 CRM_Core_DAO
::executeQuery($sql);
568 //drop the temparory columns
569 $sql = "ALTER TABLE civicrm_financial_trxn
570 DROP COLUMN contribution_id,
571 DROP COLUMN is_fee;";
572 CRM_Core_DAO
::executeQuery($sql);
574 $sql = "ALTER TABLE civicrm_financial_item DROP f_trxn_id";
575 CRM_Core_DAO
::executeQuery($sql);
580 function createDomainContacts() {
581 $domainParams = $context = array();
583 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',
584 ADD CONSTRAINT `FK_civicrm_domain_contact_id` FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact` (`id`);";
585 CRM_Core_DAO
::executeQuery($query, CRM_Core_DAO
::$_nullArray, TRUE, NULL, FALSE, FALSE);
587 $query = 'SELECT cd.id, cd.name, ce.email FROM `civicrm_domain` cd
588 LEFT JOIN civicrm_loc_block clb ON clb.id = cd. loc_block_id
589 LEFT JOIN civicrm_email ce ON ce.id = clb.email_id ;' ;
590 $dao = CRM_Core_DAO
::executeQuery($query);
591 while($dao->fetch()) {
593 'sort_name' => $dao->name
,
594 'display_name' => $dao->name
,
595 'legal_name' => $dao->name
,
596 'organization_name' => $dao->name
,
597 'contact_type' => 'Organization'
599 $query = "SELECT cc.id FROM `civicrm_contact` cc
600 LEFT JOIN civicrm_email ce ON ce.contact_id = cc.id
601 WHERE cc.contact_type = 'Organization' AND cc.organization_name = '{$dao->name}' ";
603 $query .= " AND ce.email = '{$dao->email}' ";
605 $contactID = CRM_Core_DAO
::singleValueQuery($query);
606 $context[1] = $dao->name
;
607 if (empty($contactID)) {
608 $contact = CRM_Contact_BAO_Contact
::add($params);
609 $contactID = $contact->id
;
610 $context[0] = 'added';
613 $context[0] = 'merged';
615 $domainParams['contact_id'] = $contactID;
616 CRM_Core_BAO_Domain
::edit($domainParams, $dao->id
);
621 function task_4_3_alpha1_checkDBConstraints() {
622 //checking whether the foreign key exists before dropping it CRM-11260
623 $config = CRM_Core_Config
::singleton();
624 $dbUf = DB
::parseDSN($config->dsn
);
627 'autorenewal_msg_id' => array('tableName' => 'civicrm_membership_type', 'fkey' => 'FK_civicrm_membership_autorenewal_msg_id'),
628 'to_account_id' => array('tableName' => 'civicrm_financial_trxn', 'constraintName' => 'civicrm_financial_trxn_ibfk_2'),
629 'from_account_id' => array('tableName' => 'civicrm_financial_trxn', 'constraintName' => 'civicrm_financial_trxn_ibfk_1'),
630 'contribution_type_id' => array('tableName' => 'civicrm_contribution_recur', 'fkey' => 'FK_civicrm_contribution_recur_contribution_type_id'),
632 $query = "SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
633 WHERE table_name = 'civicrm_contribution_recur'
634 AND constraint_name = 'FK_civicrm_contribution_recur_contribution_type_id'
635 AND TABLE_SCHEMA = '{$dbUf['database']}'";
637 $dao = CRM_Core_DAO
::executeQuery($query, $params, TRUE, NULL, FALSE, FALSE);
638 foreach($tables as $columnName => $value){
639 if ($value['tableName'] == 'civicrm_membership_type' ||
$value['tableName'] == 'civicrm_contribution_recur') {
640 $foreignKeyExists = CRM_Core_DAO
::checkConstraintExists($value['tableName'], $value['fkey']);
641 $fKey = $value['fkey'];
643 $foreignKeyExists = CRM_Core_DAO
::checkFKConstraintInFormat($value['tableName'], $columnName);
644 $fKey = "`FK_{$value['tableName']}_{$columnName}`";
646 if ($foreignKeyExists ||
$value['tableName'] == 'civicrm_financial_trxn') {
647 if ($value['tableName'] != 'civicrm_contribution_recur' ||
($value['tableName'] == 'civicrm_contribution_recur' && $dao->N
)) {
648 $constraintName = $foreignKeyExists ?
$fKey : $value['constraintName'];
649 CRM_Core_DAO
::executeQuery("ALTER TABLE {$value['tableName']} DROP FOREIGN KEY {$constraintName}", $params, TRUE, NULL, FALSE, FALSE);
651 CRM_Core_DAO
::executeQuery("ALTER TABLE {$value['tableName']} DROP INDEX {$fKey}", $params, TRUE, NULL, FALSE, FALSE);
654 // check if column contact_id is present or not in civicrm_financial_account
655 $fieldExists = CRM_Core_DAO
::checkFieldExists('civicrm_financial_account', 'contact_id', FALSE);
657 $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);";
658 CRM_Core_DAO
::executeQuery($query, $params, TRUE, NULL, FALSE, FALSE);
663 * Read creation and modification times from civicrm_log; add
664 * them to civicrm_contact.
666 function convertTimestamps(CRM_Queue_TaskContext
$ctx, $startId, $endId) {
668 SELECT entity_id, min(modified_date) AS created, max(modified_date) AS modified
670 WHERE entity_table = 'civicrm_contact'
671 AND entity_id BETWEEN %1 AND %2
675 1 => array($startId, 'Integer'),
676 2 => array($endId, 'Integer'),
678 $dao = CRM_Core_DAO
::executeQuery($sql, $params);
679 while ($dao->fetch()) {
680 // FIXME civicrm_log.modified_date is DATETIME; civicrm_contact.modified_date is TIMESTAMP
681 CRM_Core_DAO
::executeQuery(
682 'UPDATE civicrm_contact SET created_date = %1, modified_date = %2 WHERE id = %3',
684 1 => array($dao->created
, 'String'),
685 2 => array($dao->modified
, 'String'),
686 3 => array($dao->entity_id
, 'Integer'),
695 * replace contribution_type to financial_type in table
696 * civicrm_saved_search and Structure civicrm_report_instance
698 function replaceContributionTypeId(CRM_Queue_TaskContext
$ctx, $query, $table) {
699 $dao = CRM_Core_DAO
::executeQuery($query);
700 while ($dao->fetch()) {
701 $formValues = unserialize($dao->form_values
);
702 foreach (array('contribution_type_id_op', 'contribution_type_id_value', 'contribution_type_id') as $value) {
703 if (array_key_exists($value, $formValues)) {
704 $key = preg_replace('/contribution/', 'financial', $value);
705 $formValues[$key] = $formValues[$value];
706 unset($formValues[$value]);
709 if ($table != 'savedSearch') {
710 foreach (array('fields', 'group_bys') as $value) {
711 if (array_key_exists($value, $formValues)) {
712 if (array_key_exists('contribution_type_id', $formValues[$value])) {
713 $formValues[$value]['financial_type_id'] = $formValues[$value]['contribution_type_id'];
714 unset($formValues[$value]['contribution_type_id']);
716 else if (array_key_exists('contribution_type', $formValues[$value])) {
717 $formValues[$value]['financial_type'] = $formValues[$value]['contribution_type'];
718 unset($formValues[$value]['contribution_type']);
722 if (array_key_exists('order_bys', $formValues)) {
723 foreach ($formValues['order_bys'] as $key => $values) {
724 if (preg_grep('/contribution_type/', $values)) {
725 $formValues['order_bys'][$key]['column'] = preg_replace('/contribution_type/', 'financial_type', $values['column']);
731 if ($table == 'savedSearch') {
732 $saveDao = new CRM_Contact_DAO_SavedSearch();
735 $saveDao = new CRM_Report_DAO_Instance();
737 $saveDao->id
= $dao->id
;
739 if ($table == 'savedSearch') {
740 if (array_key_exists('mapper', $formValues)) {
741 foreach ($formValues['mapper'] as $key => $values) {
742 foreach ($values as $k => $v) {
743 if (preg_grep('/contribution_/', $v)) {
744 $formValues['mapper'][$key][$k] = preg_replace('/contribution_type/', 'financial_type', $v);
749 foreach (array('select_tables', 'where_tables') as $value) {
750 if (preg_match('/contribution_type/', $dao->$value)) {
751 $tempValue = unserialize($dao->$value);
752 if (array_key_exists('civicrm_contribution_type', $tempValue)) {
753 $tempValue['civicrm_financial_type'] = $tempValue['civicrm_contribution_type'];
754 unset($tempValue['civicrm_contribution_type']);
756 $saveDao->$value = serialize($tempValue);
759 if (preg_match('/contribution_type/', $dao->where_clause
)) {
760 $saveDao->where_clause
= preg_replace('/contribution_type/', 'financial_type', $dao->where_clause
);
763 $saveDao->form_values
= serialize($formValues);
771 * Update phones CRM-11292
773 * @return bool TRUE for success
775 static function phoneNumeric(CRM_Queue_TaskContext
$ctx) {
776 CRM_Core_DAO
::executeQuery(CRM_Contact_BAO_Contact
::DROP_STRIP_FUNCTION_43
);
777 CRM_Core_DAO
::executeQuery(CRM_Contact_BAO_Contact
::CREATE_STRIP_FUNCTION_43
);
778 CRM_Core_DAO
::executeQuery("UPDATE civicrm_phone SET phone_numeric = civicrm_strip_non_numeric(phone)");
783 * (Queue Task Callback)
785 static function task_4_3_x_runSql(CRM_Queue_TaskContext
$ctx, $rev) {
786 $upgrade = new CRM_Upgrade_Form();
787 $upgrade->processSQL($rev);
793 * Syntatic sugar for adding a task which (a) is in this class and (b) has
796 * After passing the $funcName, you can also pass parameters that will go to
797 * the function. Note that all params must be serializable.
799 protected function addTask($title, $funcName) {
800 $queue = CRM_Queue_Service
::singleton()->load(array(
802 'name' => CRM_Upgrade_Form
::QUEUE_NAME
,
805 $args = func_get_args();
806 $title = array_shift($args);
807 $funcName = array_shift($args);
808 $task = new CRM_Queue_Task(
809 array(get_class($this), $funcName),
813 $queue->createItem($task, array('weight' => -1));