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);
68 * Compute any messages which should be displayed after upgrade
70 * @param $postUpgradeMessage string, alterable
71 * @param $rev string, an intermediate version; note that setPostUpgradeMessage is called repeatedly with different $revs
74 function setPostUpgradeMessage(&$postUpgradeMessage, $rev) {
75 if ($rev == '4.3.alpha1') {
76 // check if CiviMember component is enabled
77 $config = CRM_Core_Config
::singleton();
78 if (in_array('CiviMember', $config->enableComponents
)) {
79 $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.');
80 $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.');
84 //here we do the financial type check and migration
85 $isDefaultsModified = self
::_checkAndMigrateDefaultFinancialTypes();
86 if($isDefaultsModified) {
87 $postUpgradeMessage .= '<br />' . ts('Please review all price set financial type assignments.');
89 list($context, $orgName) = self
::createDomainContacts();
90 if ($context == 'added') {
91 $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}'.");
93 elseif ($context == 'merged') {
94 $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.");
97 $providerExists = CRM_Core_DAO
::singleValueQuery("SELECT id FROM civicrm_sms_provider LIMIT 1");
98 if ($providerExists) {
99 $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'));
103 if ($rev == '4.3.alpha2') {
106 FROM civicrm_action_schedule
107 WHERE entity_value = '' OR entity_value IS NULL
110 $dao = CRM_Core_DAO
::executeQuery($sql);
113 while ($dao->fetch()) {
114 $reminder[$dao->id
] = $dao->title
;
115 $list .= "<li>{$dao->title}</li>";
117 if (!empty($reminder)) {
118 $list = "<br /><ul>" . $list . "</ul>";
119 $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));
123 if ($rev == '4.3.beta2') {
124 $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).');
128 function upgrade_4_3_alpha1($rev) {
129 self
::task_4_3_alpha1_checkDBConstraints();
131 // task to process sql
132 $this->addTask(ts('Upgrade DB to 4.3.alpha1: SQL'), 'task_4_3_x_runSql', $rev);
135 $this->addTask(ts('Populate financial type values for price records'), 'assignFinancialTypeToPriceRecords');
136 //CRM-11514 create financial records for contributions
137 $this->addTask(ts('Create financial records for contributions'), 'createFinancialRecords');
139 $minId = CRM_Core_DAO
::singleValueQuery('SELECT coalesce(min(id),0) FROM civicrm_contact');
140 $maxId = CRM_Core_DAO
::singleValueQuery('SELECT coalesce(max(id),0) FROM civicrm_contact');
141 for ($startId = $minId; $startId <= $maxId; $startId +
= self
::BATCH_SIZE
) {
142 $endId = $startId + self
::BATCH_SIZE
- 1;
143 $title = ts('Upgrade timestamps (%1 => %2)', array(
147 $this->addTask($title, 'convertTimestamps', $startId, $endId);
151 // fix WP access control
152 $config = CRM_Core_Config
::singleton( );
153 if ($config->userFramework
== 'WordPress') {
154 civicrm_wp_set_capabilities( );
157 // Update phones CRM-11292.
158 $this->addTask(ts('Upgrade Phone Numbers'), 'phoneNumeric');
163 function upgrade_4_3_alpha2($rev) {
165 $isColumnPresent = CRM_Core_DAO
::checkFieldExists('civicrm_dedupe_rule_group', 'is_default');
166 if ($isColumnPresent) {
167 CRM_Core_DAO
::executeQuery('ALTER TABLE civicrm_dedupe_rule_group DROP COLUMN is_default');
169 $this->addTask(ts('Upgrade DB to 4.3.alpha2: SQL'), 'task_4_3_x_runSql', $rev);
172 function upgrade_4_3_alpha3($rev) {
173 $this->addTask(ts('Upgrade DB to 4.3.alpha3: SQL'), 'task_4_3_x_runSql', $rev);
176 function upgrade_4_3_beta2($rev) {
177 $this->addTask(ts('Upgrade DB to 4.3.beta2: SQL'), 'task_4_3_x_runSql', $rev);
180 CRM_Core_DAO
::checkTableExists('log_civicrm_line_item') &&
181 CRM_Core_DAO
::checkFieldExists('log_civicrm_line_item', 'label')
183 CRM_Core_DAO
::executeQuery('ALTER TABLE `log_civicrm_line_item` CHANGE `label` `label` VARCHAR(255) NULL DEFAULT NULL');
188 function assignFinancialTypeToPriceRecords() {
189 $upgrade = new CRM_Upgrade_Form();
190 //here we update price set entries
191 $sqlFinancialIds = "SELECT id, name FROM civicrm_financial_type
192 WHERE name IN ('Donation', 'Event Fee', 'Member Dues');";
193 $daoFinancialIds = CRM_Core_DAO
::executeQuery($sqlFinancialIds);
194 while($daoFinancialIds->fetch()) {
195 $financialIds[$daoFinancialIds->name
] = $daoFinancialIds->id
;
197 $sqlPriceSetUpdate = "UPDATE civicrm_price_set ps
198 SET ps.financial_type_id = CASE
199 WHEN ps.extends LIKE '%1%' THEN {$financialIds['Event Fee']}
200 WHEN ps.extends LIKE '2' THEN {$financialIds['Donation']}
201 WHEN ps.extends LIKE '3' THEN {$financialIds['Member Dues']}
203 WHERE financial_type_id IS NULL";
204 CRM_Core_DAO
::executeQuery($sqlPriceSetUpdate);
206 //here we update price field value rows
207 $sqlPriceFieldValueUpdate = "UPDATE civicrm_price_field_value pfv
208 LEFT JOIN civicrm_membership_type mt ON (pfv.membership_type_id = mt.id)
209 INNER JOIN civicrm_price_field pf ON (pfv.price_field_id = pf.id)
210 INNER JOIN civicrm_price_set ps ON (pf.price_set_id = ps.id)
211 SET pfv.financial_type_id = CASE
212 WHEN pfv.membership_type_id IS NOT NULL THEN mt.financial_type_id
213 WHEN pfv.membership_type_id IS NULL THEN ps.financial_type_id
215 CRM_Core_DAO
::executeQuery($sqlPriceFieldValueUpdate);
220 static function _checkAndMigrateDefaultFinancialTypes() {
221 $modifiedDefaults = FALSE;
222 //insert types if not exists
223 $sqlFetchTypes = "SELECT id, name FROM civicrm_contribution_type
224 WHERE name IN ('Donation', 'Event Fee', 'Member Dues') AND is_active =1;";
225 $daoFetchTypes = CRM_Core_DAO
::executeQuery($sqlFetchTypes);
227 if ($daoFetchTypes->N
< 3) {
228 $modifiedDefaults = TRUE;
229 $insertStatments = array (
230 'Donation' => "('Donation', 0, 1, 1)",
231 'Member' => "('Member Dues', 0, 1, 1)",
232 'Event Fee' => "('Event Fee', 0, 1, 0)",
234 foreach ($insertStatments as $values) {
235 $query = "INSERT INTO civicrm_contribution_type (name, is_reserved, is_active, is_deductible)
237 ON DUPLICATE KEY UPDATE is_active = 1;";
238 CRM_Core_DAO
::executeQuery($query);
241 return $modifiedDefaults;
244 function createFinancialRecords() {
245 $upgrade = new CRM_Upgrade_Form();
247 // update civicrm_entity_financial_trxn.amount = civicrm_financial_trxn.total_amount
248 $query = "UPDATE civicrm_entity_financial_trxn ceft
249 LEFT JOIN civicrm_financial_trxn cft ON cft.id = ceft.financial_trxn_id
250 SET ceft.amount = total_amount
251 WHERE cft.net_amount IS NOT NULL AND ceft.entity_table = 'civicrm_contribution';";
252 CRM_Core_DAO
::executeQuery($query);
254 $contributionStatus = CRM_Contribute_PseudoConstant
::contributionStatus(NULL, 'name');
255 $completedStatus = array_search('Completed', $contributionStatus);
256 $pendingStatus = array_search('Pending', $contributionStatus);
257 $cancelledStatus = array_search('Cancelled', $contributionStatus);
258 $queryParams = array(
259 1 => array($completedStatus, 'Integer'),
260 2 => array($pendingStatus, 'Integer'),
261 3 => array($cancelledStatus, 'Integer')
264 $accountType = key(CRM_Core_PseudoConstant
::accountOptionValues('financial_account_type', NULL, " AND v.name = 'Asset' "));
265 $financialAccountId =
266 CRM_Core_DAO
::singleValueQuery("SELECT id FROM civicrm_financial_account WHERE is_default = 1 AND financial_account_type_id = {$accountType}");
268 $accountRelationsips = CRM_Core_PseudoConstant
::accountOptionValues('account_relationship', NULL);
270 $accountsReceivableAccount = array_search('Accounts Receivable Account is', $accountRelationsips);
271 $incomeAccountIs = array_search('Income Account is', $accountRelationsips);
272 $assetAccountIs = array_search('Asset Account is', $accountRelationsips);
273 $expenseAccountIs = array_search('Expense Account is', $accountRelationsips);
275 $financialItemStatus = CRM_Core_PseudoConstant
::accountOptionValues('financial_item_status');
276 $unpaidStatus = array_search('Unpaid', $financialItemStatus);
277 $paidStatus = array_search('Paid', $financialItemStatus);
279 $validCurrencyCodes = CRM_Core_PseudoConstant
::currencyCode();
280 $validCurrencyCodes = implode("','", $validCurrencyCodes);
281 $config = CRM_Core_Config
::singleton();
282 $defaultCurrency = $config->defaultCurrency
;
283 $now = date( 'YmdHis' );
285 //adding financial_trxn records and entity_financial_trxn records related to contribution
286 //Add temp column for easy entry in entity_financial_trxn
287 $sql = "ALTER TABLE civicrm_financial_trxn ADD COLUMN contribution_id INT DEFAULT NULL";
288 CRM_Core_DAO
::executeQuery($sql);
290 //pending pay later status handling
292 INSERT INTO civicrm_financial_trxn
293 (contribution_id, payment_instrument_id, currency, total_amount, net_amount, fee_amount, trxn_id, status_id,
294 check_number, to_financial_account_id, from_financial_account_id, trxn_date)
296 SELECT con.id as contribution_id, con.payment_instrument_id, IF(con.currency IN ('{$validCurrencyCodes}'), con.currency, '{$defaultCurrency}')
297 as currency, con.total_amount, con.net_amount, con.fee_amount, con.trxn_id, con.contribution_status_id,
298 con.check_number, efa.financial_account_id as to_financial_account_id, NULL as from_financial_account_id,
299 REPLACE(REPLACE(REPLACE(
301 WHEN con.receive_date IS NOT NULL THEN
303 WHEN con.receipt_date IS NOT NULL THEN
308 , '-', ''), ':', ''), ' ', '') as trxn_date
309 FROM civicrm_contribution con
310 LEFT JOIN civicrm_entity_financial_account efa
311 ON (con.financial_type_id = efa.entity_id AND efa.entity_table = 'civicrm_financial_type'
312 AND efa.account_relationship = {$accountsReceivableAccount})
313 WHERE con.is_pay_later = 1 AND con.contribution_status_id = {$pendingStatus}";
314 CRM_Core_DAO
::executeQuery($sql);
316 //create a temp table to hold financial account id related to payment instruments
317 $tempTableName1 = CRM_Core_DAO
::createTempTableName();
319 $sql = "CREATE TEMPORARY TABLE {$tempTableName1}
321 SELECT ceft.financial_account_id financial_account_id, cov.value as instrument_id
322 FROM civicrm_entity_financial_account ceft
323 INNER JOIN civicrm_option_value cov ON cov.id = ceft.entity_id AND ceft.entity_table = 'civicrm_option_value'
324 INNER JOIN civicrm_option_group cog ON cog.id = cov.option_group_id
325 WHERE cog.name = 'payment_instrument'";
326 CRM_Core_DAO
::executeQuery($sql);
328 //create temp table to process completed / cancelled contribution
329 $tempTableName2 = CRM_Core_DAO
::createTempTableName();
330 $sql = "CREATE TEMPORARY TABLE {$tempTableName2}
332 SELECT con.id as contribution_id, con.payment_instrument_id, IF(con.currency IN ('{$validCurrencyCodes}'), con.currency, '{$defaultCurrency}') as currency,
333 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,
334 REPLACE(REPLACE(REPLACE(
336 WHEN con.receive_date IS NOT NULL THEN
338 WHEN con.receipt_date IS NOT NULL THEN
343 , '-', ''), ':', ''), ' ', '') as trxn_date,
345 WHEN con.payment_instrument_id IS NULL THEN
346 {$financialAccountId}
347 WHEN con.payment_instrument_id IS NOT NULL THEN
348 tpi.financial_account_id
349 END as to_financial_account_id,
350 IF(eft.financial_trxn_id IS NULL, 'insert', eft.financial_trxn_id) as action
351 FROM civicrm_contribution con
352 LEFT JOIN civicrm_entity_financial_trxn eft
353 ON (eft.entity_table = 'civicrm_contribution' AND eft.entity_id = con.id)
354 LEFT JOIN {$tempTableName1} tpi
355 ON con.payment_instrument_id = tpi.instrument_id
356 WHERE con.contribution_status_id IN ({$completedStatus}, {$cancelledStatus})";
357 CRM_Core_DAO
::executeQuery($sql);
359 //handling for completed contribution and cancelled contribution
360 //insertion of new records
362 INSERT INTO civicrm_financial_trxn
363 (contribution_id, payment_instrument_id, currency, total_amount, net_amount, fee_amount, trxn_id, status_id, check_number,
364 to_financial_account_id, from_financial_account_id, trxn_date)
365 SELECT tempI.contribution_id, tempI.payment_instrument_id, tempI.currency, tempI.total_amount, tempI.net_amount,
366 tempI.fee_amount, tempI.trxn_id, tempI.contribution_status_id, tempI.check_number,
367 tempI.to_financial_account_id, tempI.from_financial_account_id, tempI.trxn_date
368 FROM {$tempTableName2} tempI
369 WHERE tempI.action = 'insert';";
370 CRM_Core_DAO
::executeQuery($sql);
372 //update of existing records
374 UPDATE civicrm_financial_trxn ft
375 INNER JOIN {$tempTableName2} tempU
376 ON (tempU.action != 'insert' AND ft.id = tempU.action)
377 SET ft.from_financial_account_id = NULL,
378 ft.to_financial_account_id = tempU.to_financial_account_id,
379 ft.status_id = tempU.contribution_status_id,
380 ft.payment_instrument_id = tempU.payment_instrument_id,
381 ft.check_number = tempU.check_number,
382 ft.contribution_id = tempU.contribution_id;";
383 CRM_Core_DAO
::executeQuery($sql);
385 //insert the -ve transaction rows for cancelled contributions
387 INSERT INTO civicrm_financial_trxn
388 (contribution_id, payment_instrument_id, currency, total_amount, net_amount, fee_amount, trxn_id, status_id,
389 check_number, to_financial_account_id, from_financial_account_id, trxn_date)
390 SELECT ft.contribution_id, ft.payment_instrument_id, ft.currency, -ft.total_amount, ft.net_amount, ft.fee_amount, ft.trxn_id,
391 ft.status_id, ft.check_number, ft.to_financial_account_id, ft.from_financial_account_id, ft.trxn_date
392 FROM civicrm_financial_trxn ft
393 WHERE ft.status_id = {$cancelledStatus};";
394 CRM_Core_DAO
::executeQuery($sql);
396 //inserting entity financial trxn entries if its not present in entity_financial_trxn for completed and pending contribution statuses
397 //this also handles +ve and -ve both transaction entries for a cancelled contribution
399 INSERT INTO civicrm_entity_financial_trxn (entity_table, entity_id, financial_trxn_id, amount)
400 SELECT 'civicrm_contribution', ft.contribution_id, ft.id, ft.total_amount as amount
401 FROM civicrm_financial_trxn ft
402 WHERE contribution_id IS NOT NULL AND
403 ft.id NOT IN (SELECT financial_trxn_id
404 FROM civicrm_entity_financial_trxn
405 WHERE entity_table = 'civicrm_contribution'
406 AND entity_id = ft.contribution_id)";
407 CRM_Core_DAO
::executeQuery($sql);
408 //end of adding financial_trxn records and entity_financial_trxn records related to contribution
410 //update all linked line_item rows
411 // set line_item.financial_type_id = contribution.financial_type_id if contribution page id is null and not participant line item
412 // set line_item.financial_type_id = price_field_value.financial_type_id if contribution page id is set and not participant line item
413 // set line_item.financial_type_id = event.financial_type_id if its participant line item and line_item.price_field_value_id is null
414 // 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
415 $updateLineItemSql = "
416 UPDATE civicrm_line_item li
417 LEFT JOIN civicrm_contribution con
418 ON (li.entity_id = con.id AND li.entity_table = 'civicrm_contribution')
419 LEFT JOIN civicrm_price_field_value cpfv
420 ON li.price_field_value_id = cpfv.id
421 LEFT JOIN civicrm_participant cp
422 ON (li.entity_id = cp.id AND li.entity_table = 'civicrm_participant')
423 LEFT JOIN civicrm_event ce
424 ON ce.id = cp.event_id
425 SET li.financial_type_id = CASE
426 WHEN (con.contribution_page_id IS NULL || li.price_field_value_id IS NULL) AND cp.id IS NULL THEN
427 con.financial_type_id
428 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
429 cpfv.financial_type_id
430 WHEN cp.id IS NOT NULL AND li.price_field_value_id IS NULL THEN
433 CRM_Core_DAO
::executeQuery($updateLineItemSql, $queryParams);
435 //add the financial_item entries
436 //add a temp column so that inserting entity_financial_trxn entries gets easy
437 $sql = "ALTER TABLE civicrm_financial_item ADD COLUMN f_trxn_id INT DEFAULT NULL";
438 CRM_Core_DAO
::executeQuery($sql);
440 //add financial_item entries for contribution completed / pending pay later / cancelled
441 $contributionlineItemSql = "
442 INSERT INTO civicrm_financial_item
443 (transaction_date, contact_id, amount, currency, entity_table, entity_id, description, status_id, financial_account_id, f_trxn_id)
445 SELECT REPLACE(REPLACE(REPLACE(ft.trxn_date, '-', ''), ':', ''), ' ', ''), con.contact_id,
446 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',
447 li.id as line_item_id, li.label as line_item_label,
448 IF(con.contribution_status_id = {$pendingStatus}, {$unpaidStatus}, {$paidStatus}) as status_id, efa.financial_account_id as financial_account_id,
450 FROM civicrm_line_item li
451 INNER JOIN civicrm_contribution con
452 ON (li.entity_id = con.id AND li.entity_table = 'civicrm_contribution')
453 INNER JOIN civicrm_financial_trxn ft
454 ON (con.id = ft.contribution_id)
455 LEFT JOIN civicrm_entity_financial_account efa
456 ON (li.financial_type_id = efa.entity_id AND efa.entity_table = 'civicrm_financial_type'
457 AND efa.account_relationship = {$incomeAccountIs})
458 WHERE con.contribution_status_id IN (%1, %3) OR (con.is_pay_later = 1 AND con.contribution_status_id = %2)";
459 CRM_Core_DAO
::executeQuery($contributionlineItemSql, $queryParams);
461 //add financial_item entries for event
462 $participantLineItemSql = "
463 INSERT INTO civicrm_financial_item
464 (transaction_date, contact_id, amount, currency, entity_table, entity_id, description, status_id, financial_account_id, f_trxn_id)
466 SELECT REPLACE(REPLACE(REPLACE(ft.trxn_date, '-', ''), ':', ''), ' ', ''), con.contact_id,
467 IF(ft.total_amount < 0 AND con.contribution_status_id = %3, -li.line_total, li.line_total) as line_total,
468 con.currency, 'civicrm_line_item', li.id as line_item_id, li.label as line_item_label,
469 IF(con.contribution_status_id = {$pendingStatus}, {$unpaidStatus}, {$paidStatus}) as status_id,
470 efa.financial_account_id as financial_account_id, ft.id as f_trxn_id
471 FROM civicrm_line_item li
472 INNER JOIN civicrm_participant par
473 ON (li.entity_id = par.id AND li.entity_table = 'civicrm_participant')
474 INNER JOIN civicrm_participant_payment pp
475 ON (pp.participant_id = par.id)
476 INNER JOIN civicrm_contribution con
477 ON (pp.contribution_id = con.id)
478 INNER JOIN civicrm_financial_trxn ft
479 ON (con.id = ft.contribution_id)
480 LEFT JOIN civicrm_entity_financial_account efa
481 ON (li.financial_type_id = efa.entity_id AND
482 efa.entity_table = 'civicrm_financial_type' AND efa.account_relationship = {$incomeAccountIs})
483 WHERE con.contribution_status_id IN (%1, %3) OR (con.is_pay_later = 1 AND con.contribution_status_id = %2)";
484 CRM_Core_DAO
::executeQuery($participantLineItemSql, $queryParams);
486 //fee handling for contributions
487 //insert fee entries in financial_trxn for contributions
488 $sql = "ALTER TABLE civicrm_financial_trxn ADD COLUMN is_fee TINYINT DEFAULT NULL";
489 CRM_Core_DAO
::executeQuery($sql);
492 INSERT INTO civicrm_financial_trxn
493 (contribution_id, payment_instrument_id, currency, total_amount, net_amount, fee_amount, trxn_id, status_id, check_number,
494 to_financial_account_id, from_financial_account_id, trxn_date, payment_processor_id, is_fee)
496 SELECT con.id, ft.payment_instrument_id, ft.currency, ft.fee_amount, NULL, NULL, ft.trxn_id, %1 as status_id,
497 ft.check_number, efaFT.financial_account_id as to_financial_account_id, CASE
498 WHEN efaPP.financial_account_id IS NOT NULL THEN
499 efaPP.financial_account_id
500 WHEN tpi.financial_account_id IS NOT NULL THEN
501 tpi.financial_account_id
503 {$financialAccountId}
504 END as from_financial_account_id, ft.trxn_date, ft.payment_processor_id, 1 as is_fee
505 FROM civicrm_contribution con
506 INNER JOIN civicrm_financial_trxn ft
507 ON (ft.contribution_id = con.id)
508 LEFT JOIN civicrm_entity_financial_account efaFT
509 ON (con.financial_type_id = efaFT.entity_id AND efaFT.entity_table = 'civicrm_financial_type'
510 AND efaFT.account_relationship = {$expenseAccountIs})
511 LEFT JOIN civicrm_entity_financial_account efaPP
512 ON (ft.payment_processor_id = efaPP.entity_id AND efaPP.entity_table = 'civicrm_payment_processor'
513 AND efaPP.account_relationship = {$assetAccountIs})
514 LEFT JOIN {$tempTableName1} tpi
515 ON ft.payment_instrument_id = tpi.instrument_id
516 WHERE con.fee_amount IS NOT NULL AND (con.contribution_status_id IN (%1, %3) OR (con.contribution_status_id =%2 AND con.is_pay_later = 1))
518 CRM_Core_DAO
::executeQuery($sql, $queryParams);
520 //link financial_trxn to contribution
522 INSERT INTO civicrm_entity_financial_trxn
523 (entity_table, entity_id, financial_trxn_id, amount)
524 SELECT 'civicrm_contribution', ft.contribution_id, ft.id, ft.total_amount
525 FROM civicrm_financial_trxn ft
526 WHERE ft.is_fee = 1";
527 CRM_Core_DAO
::executeQuery($sql);
529 //add fee related entries to financial item table
530 $domainId = CRM_Core_Config
::domainID();
531 $domainContactId = CRM_Core_DAO
::getFieldValue('CRM_Core_DAO_Domain', $domainId, 'contact_id');
533 INSERT INTO civicrm_financial_item
534 (transaction_date, contact_id, amount, currency, entity_table, entity_id, description, status_id, financial_account_id, f_trxn_id)
535 SELECT ft.trxn_date, {$domainContactId} as contact_id, ft.total_amount, ft.currency, 'civicrm_financial_trxn', ft.id,
536 'Fee', {$paidStatus} as status_id, ft.to_financial_account_id as financial_account_id, ft.id as f_trxn_id
537 FROM civicrm_financial_trxn ft
538 WHERE ft.is_fee = 1;";
539 CRM_Core_DAO
::executeQuery($sql);
541 //add entries to entity_financial_trxn table
543 INSERT INTO civicrm_entity_financial_trxn (entity_table, entity_id, financial_trxn_id, amount)
544 SELECT 'civicrm_financial_item' as entity_table, fi.id as entity_id, fi.f_trxn_id as financial_trxn_id, fi.amount
545 FROM civicrm_financial_item fi";
546 CRM_Core_DAO
::executeQuery($sql);
548 //drop the temparory columns
549 $sql = "ALTER TABLE civicrm_financial_trxn
550 DROP COLUMN contribution_id,
551 DROP COLUMN is_fee;";
552 CRM_Core_DAO
::executeQuery($sql);
554 $sql = "ALTER TABLE civicrm_financial_item DROP f_trxn_id";
555 CRM_Core_DAO
::executeQuery($sql);
560 function createDomainContacts() {
561 $domainParams = $context = array();
563 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',
564 ADD CONSTRAINT `FK_civicrm_domain_contact_id` FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact` (`id`);";
565 CRM_Core_DAO
::executeQuery($query, CRM_Core_DAO
::$_nullArray, TRUE, NULL, FALSE, FALSE);
567 $query = 'SELECT cd.id, cd.name, ce.email FROM `civicrm_domain` cd
568 LEFT JOIN civicrm_loc_block clb ON clb.id = cd. loc_block_id
569 LEFT JOIN civicrm_email ce ON ce.id = clb.email_id ;' ;
570 $dao = CRM_Core_DAO
::executeQuery($query);
571 while($dao->fetch()) {
573 'sort_name' => $dao->name
,
574 'display_name' => $dao->name
,
575 'legal_name' => $dao->name
,
576 'organization_name' => $dao->name
,
577 'contact_type' => 'Organization'
579 $query = "SELECT cc.id FROM `civicrm_contact` cc
580 LEFT JOIN civicrm_email ce ON ce.contact_id = cc.id
581 WHERE cc.contact_type = 'Organization' AND cc.organization_name = '{$dao->name}' ";
583 $query .= " AND ce.email = '{$dao->email}' ";
585 $contactID = CRM_Core_DAO
::singleValueQuery($query);
586 $context[1] = $dao->name
;
587 if (empty($contactID)) {
588 $contact = CRM_Contact_BAO_Contact
::add($params);
589 $contactID = $contact->id
;
590 $context[0] = 'added';
593 $context[0] = 'merged';
595 $domainParams['contact_id'] = $contactID;
596 CRM_Core_BAO_Domain
::edit($domainParams, $dao->id
);
601 function task_4_3_alpha1_checkDBConstraints() {
602 //checking whether the foreign key exists before dropping it CRM-11260
603 $config = CRM_Core_Config
::singleton();
604 $dbUf = DB
::parseDSN($config->dsn
);
607 'autorenewal_msg_id' => array('tableName' => 'civicrm_membership_type', 'fkey' => 'FK_civicrm_membership_autorenewal_msg_id'),
608 'to_account_id' => array('tableName' => 'civicrm_financial_trxn', 'constraintName' => 'civicrm_financial_trxn_ibfk_2'),
609 'from_account_id' => array('tableName' => 'civicrm_financial_trxn', 'constraintName' => 'civicrm_financial_trxn_ibfk_1'),
610 'contribution_type_id' => array('tableName' => 'civicrm_contribution_recur', 'fkey' => 'FK_civicrm_contribution_recur_contribution_type_id'),
612 $query = "SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
613 WHERE table_name = 'civicrm_contribution_recur'
614 AND constraint_name = 'FK_civicrm_contribution_recur_contribution_type_id'
615 AND TABLE_SCHEMA = '{$dbUf['database']}'";
617 $dao = CRM_Core_DAO
::executeQuery($query, $params, TRUE, NULL, FALSE, FALSE);
618 foreach($tables as $columnName => $value){
619 if ($value['tableName'] == 'civicrm_membership_type' ||
$value['tableName'] == 'civicrm_contribution_recur') {
620 $foreignKeyExists = CRM_Core_DAO
::checkConstraintExists($value['tableName'], $value['fkey']);
621 $fKey = $value['fkey'];
623 $foreignKeyExists = CRM_Core_DAO
::checkFKConstraintInFormat($value['tableName'], $columnName);
624 $fKey = "`FK_{$value['tableName']}_{$columnName}`";
626 if ($foreignKeyExists ||
$value['tableName'] == 'civicrm_financial_trxn') {
627 if ($value['tableName'] != 'civicrm_contribution_recur' ||
($value['tableName'] == 'civicrm_contribution_recur' && $dao->N
)) {
628 $constraintName = $foreignKeyExists ?
$fKey : $value['constraintName'];
629 CRM_Core_DAO
::executeQuery("ALTER TABLE {$value['tableName']} DROP FOREIGN KEY {$constraintName}", $params, TRUE, NULL, FALSE, FALSE);
631 CRM_Core_DAO
::executeQuery("ALTER TABLE {$value['tableName']} DROP INDEX {$fKey}", $params, TRUE, NULL, FALSE, FALSE);
634 // check if column contact_id is present or not in civicrm_financial_account
635 $fieldExists = CRM_Core_DAO
::checkFieldExists('civicrm_financial_account', 'contact_id', FALSE);
637 $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);";
638 CRM_Core_DAO
::executeQuery($query, $params, TRUE, NULL, FALSE, FALSE);
643 * Read creation and modification times from civicrm_log; add
644 * them to civicrm_contact.
646 function convertTimestamps(CRM_Queue_TaskContext
$ctx, $startId, $endId) {
648 SELECT entity_id, min(modified_date) AS created, max(modified_date) AS modified
650 WHERE entity_table = 'civicrm_contact'
651 AND entity_id BETWEEN %1 AND %2
655 1 => array($startId, 'Integer'),
656 2 => array($endId, 'Integer'),
658 $dao = CRM_Core_DAO
::executeQuery($sql, $params);
659 while ($dao->fetch()) {
660 // FIXME civicrm_log.modified_date is DATETIME; civicrm_contact.modified_date is TIMESTAMP
661 CRM_Core_DAO
::executeQuery(
662 'UPDATE civicrm_contact SET created_date = %1, modified_date = %2 WHERE id = %3',
664 1 => array($dao->created
, 'String'),
665 2 => array($dao->modified
, 'String'),
666 3 => array($dao->entity_id
, 'Integer'),
675 * Update phones CRM-11292
677 * @return bool TRUE for success
679 static function phoneNumeric(CRM_Queue_TaskContext
$ctx) {
680 CRM_Core_DAO
::executeQuery(CRM_Contact_BAO_Contact
::DROP_STRIP_FUNCTION_43
);
681 CRM_Core_DAO
::executeQuery(CRM_Contact_BAO_Contact
::CREATE_STRIP_FUNCTION_43
);
682 CRM_Core_DAO
::executeQuery("UPDATE civicrm_phone SET phone_numeric = civicrm_strip_non_numeric(phone)");
687 * (Queue Task Callback)
689 static function task_4_3_x_runSql(CRM_Queue_TaskContext
$ctx, $rev) {
690 $upgrade = new CRM_Upgrade_Form();
691 $upgrade->processSQL($rev);
697 * Syntatic sugar for adding a task which (a) is in this class and (b) has
700 * After passing the $funcName, you can also pass parameters that will go to
701 * the function. Note that all params must be serializable.
703 protected function addTask($title, $funcName) {
704 $queue = CRM_Queue_Service
::singleton()->load(array(
706 'name' => CRM_Upgrade_Form
::QUEUE_NAME
,
709 $args = func_get_args();
710 $title = array_shift($args);
711 $funcName = array_shift($args);
712 $task = new CRM_Queue_Task(
713 array(get_class($this), $funcName),
717 $queue->createItem($task, array('weight' => -1));