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 after upgrade
45 * @param $postUpgradeMessage string, alterable
46 * @param $rev string, an intermediate version; note that setPostUpgradeMessage is called repeatedly with different $revs
49 function setPostUpgradeMessage(&$postUpgradeMessage, $rev) {
50 if ($rev == '4.3.alpha1') {
51 // check if CiviMember component is enabled
52 $config = CRM_Core_Config
::singleton();
53 if (in_array('CiviMember', $config->enableComponents
)) {
54 $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.');
55 $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.');
59 //here we do the financial type check and migration
60 $isDefaultsModified = self
::_checkAndMigrateDefaultFinancialTypes();
61 if($isDefaultsModified) {
62 $postUpgradeMessage .= '<br />' . ts('Please review all price set financial type assignments.');
64 list($context, $orgName) = self
::createDomainContacts();
65 if ($context == 'added') {
66 $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}'.");
68 elseif ($context == 'merged') {
69 $postUpgradeMessage .= '<br />' . ts("The existing organization contact record for '{$orgName}' has marked as the default domain contact, and has been updated with information from your Organization Address and Contact Info settings.");
72 $providerExists = CRM_Core_DAO
::singleValueQuery("SELECT id FROM civicrm_sms_provider LIMIT 1");
73 if ($providerExists) {
74 $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'));
78 if ($rev == '4.3.alpha2') {
81 FROM civicrm_action_schedule
82 WHERE entity_value = '' OR entity_value IS NULL
85 $dao = CRM_Core_DAO
::executeQuery($sql);
88 while ($dao->fetch()) {
89 $reminder[$dao->id
] = $dao->title
;
90 $list .= "<li>{$dao->title}</li>";
92 if (!empty($reminder)) {
93 $list = "<br /><ul>" . $list . "</ul>";
94 $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));
98 if ($rev == '4.3.beta2') {
99 $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).');
104 function upgrade_4_3_alpha1($rev) {
105 self
::task_4_3_alpha1_checkDBConstraints();
107 // task to process sql
108 $this->addTask(ts('Upgrade DB to 4.3.alpha1: SQL'), 'task_4_3_x_runSql', $rev);
111 $this->addTask(ts('Populate financial type values for price records'), 'assignFinancialTypeToPriceRecords');
112 //CRM-11514 create financial records for contributions
113 $this->addTask(ts('Create financial records for contributions'), 'createFinancialRecords');
115 $minId = CRM_Core_DAO
::singleValueQuery('SELECT coalesce(min(id),0) FROM civicrm_contact');
116 $maxId = CRM_Core_DAO
::singleValueQuery('SELECT coalesce(max(id),0) FROM civicrm_contact');
117 for ($startId = $minId; $startId <= $maxId; $startId +
= self
::BATCH_SIZE
) {
118 $endId = $startId + self
::BATCH_SIZE
- 1;
119 $title = ts('Upgrade timestamps (%1 => %2)', array(
123 $this->addTask($title, 'convertTimestamps', $startId, $endId);
127 // fix WP access control
128 $config = CRM_Core_Config
::singleton( );
129 if ($config->userFramework
== 'WordPress') {
130 civicrm_wp_set_capabilities( );
133 // Update phones CRM-11292.
134 $this->addTask(ts('Upgrade Phone Numbers'), 'phoneNumeric');
139 function upgrade_4_3_alpha2($rev) {
141 $isColumnPresent = CRM_Core_DAO
::checkFieldExists('civicrm_dedupe_rule_group', 'is_default');
142 if ($isColumnPresent) {
143 CRM_Core_DAO
::executeQuery('ALTER TABLE civicrm_dedupe_rule_group DROP COLUMN is_default');
145 $this->addTask(ts('Upgrade DB to 4.3.alpha2: SQL'), 'task_4_3_x_runSql', $rev);
148 function upgrade_4_3_alpha3($rev) {
149 $this->addTask(ts('Upgrade DB to 4.3.alpha3: SQL'), 'task_4_3_x_runSql', $rev);
152 function upgrade_4_3_beta2($rev) {
153 $this->addTask(ts('Upgrade DB to 4.3.beta2: SQL'), 'task_4_3_x_runSql', $rev);
156 CRM_Core_DAO
::checkTableExists('log_civicrm_line_item') &&
157 CRM_Core_DAO
::checkFieldExists('log_civicrm_line_item', 'label')
159 CRM_Core_DAO
::executeQuery('ALTER TABLE `log_civicrm_line_item` CHANGE `label` `label` VARCHAR(255) NULL DEFAULT NULL');
164 function assignFinancialTypeToPriceRecords() {
165 $upgrade = new CRM_Upgrade_Form();
166 //here we update price set entries
167 $sqlFinancialIds = "SELECT id, name FROM civicrm_financial_type
168 WHERE name IN ('Donation', 'Event Fee', 'Member Dues');";
169 $daoFinancialIds = CRM_Core_DAO
::executeQuery($sqlFinancialIds);
170 while($daoFinancialIds->fetch()) {
171 $financialIds[$daoFinancialIds->name
] = $daoFinancialIds->id
;
173 $sqlPriceSetUpdate = "UPDATE civicrm_price_set ps
174 SET ps.financial_type_id = CASE
175 WHEN ps.extends LIKE '%1%' THEN {$financialIds['Event Fee']}
176 WHEN ps.extends LIKE '2' THEN {$financialIds['Donation']}
177 WHEN ps.extends LIKE '3' THEN {$financialIds['Member Dues']}
179 WHERE financial_type_id IS NULL";
180 CRM_Core_DAO
::executeQuery($sqlPriceSetUpdate);
182 //here we update price field value rows
183 $sqlPriceFieldValueUpdate = "UPDATE civicrm_price_field_value pfv
184 LEFT JOIN civicrm_membership_type mt ON (pfv.membership_type_id = mt.id)
185 INNER JOIN civicrm_price_field pf ON (pfv.price_field_id = pf.id)
186 INNER JOIN civicrm_price_set ps ON (pf.price_set_id = ps.id)
187 SET pfv.financial_type_id = CASE
188 WHEN pfv.membership_type_id IS NOT NULL THEN mt.financial_type_id
189 WHEN pfv.membership_type_id IS NULL THEN ps.financial_type_id
191 CRM_Core_DAO
::executeQuery($sqlPriceFieldValueUpdate);
196 static function _checkAndMigrateDefaultFinancialTypes() {
197 $modifiedDefaults = FALSE;
198 //insert types if not exists
199 $sqlFetchTypes = "SELECT id, name FROM civicrm_contribution_type
200 WHERE name IN ('Donation', 'Event Fee', 'Member Dues') AND is_active =1;";
201 $daoFetchTypes = CRM_Core_DAO
::executeQuery($sqlFetchTypes);
203 if ($daoFetchTypes->N
< 3) {
204 $modifiedDefaults = TRUE;
205 $insertStatments = array (
206 'Donation' => "('Donation', 0, 1, 1)",
207 'Member' => "('Member Dues', 0, 1, 1)",
208 'Event Fee' => "('Event Fee', 0, 1, 0)",
210 foreach ($insertStatments as $values) {
211 $query = "INSERT INTO civicrm_contribution_type (name, is_reserved, is_active, is_deductible)
213 ON DUPLICATE KEY UPDATE is_active = 1;";
214 CRM_Core_DAO
::executeQuery($query);
217 return $modifiedDefaults;
220 function createFinancialRecords() {
221 $upgrade = new CRM_Upgrade_Form();
223 // update civicrm_entity_financial_trxn.amount = civicrm_financial_trxn.total_amount
224 $query = "UPDATE civicrm_entity_financial_trxn ceft
225 LEFT JOIN civicrm_financial_trxn cft ON cft.id = ceft.financial_trxn_id
226 SET ceft.amount = total_amount
227 WHERE cft.net_amount IS NOT NULL AND ceft.entity_table = 'civicrm_contribution';";
228 CRM_Core_DAO
::executeQuery($query);
230 $contributionStatus = CRM_Contribute_PseudoConstant
::contributionStatus(NULL, 'name');
231 $completedStatus = array_search('Completed', $contributionStatus);
232 $pendingStatus = array_search('Pending', $contributionStatus);
233 $cancelledStatus = array_search('Cancelled', $contributionStatus);
234 $queryParams = array(
235 1 => array($completedStatus, 'Integer'),
236 2 => array($pendingStatus, 'Integer'),
237 3 => array($cancelledStatus, 'Integer')
240 $accountType = key(CRM_Core_PseudoConstant
::accountOptionValues('financial_account_type', NULL, " AND v.name = 'Asset' "));
241 $financialAccountId =
242 CRM_Core_DAO
::singleValueQuery("SELECT id FROM civicrm_financial_account WHERE is_default = 1 AND financial_account_type_id = {$accountType}");
244 $accountRelationsips = CRM_Core_PseudoConstant
::accountOptionValues('account_relationship', NULL);
246 $accountsReceivableAccount = array_search('Accounts Receivable Account is', $accountRelationsips);
247 $incomeAccountIs = array_search('Income Account is', $accountRelationsips);
248 $assetAccountIs = array_search('Asset Account is', $accountRelationsips);
249 $expenseAccountIs = array_search('Expense Account is', $accountRelationsips);
251 $financialItemStatus = CRM_Core_PseudoConstant
::accountOptionValues('financial_item_status');
252 $unpaidStatus = array_search('Unpaid', $financialItemStatus);
253 $paidStatus = array_search('Paid', $financialItemStatus);
255 $validCurrencyCodes = CRM_Core_PseudoConstant
::currencyCode();
256 $validCurrencyCodes = implode("','", $validCurrencyCodes);
257 $config = CRM_Core_Config
::singleton();
258 $defaultCurrency = $config->defaultCurrency
;
259 $now = date( 'YmdHis' );
261 //adding financial_trxn records and entity_financial_trxn records related to contribution
262 //Add temp column for easy entry in entity_financial_trxn
263 $sql = "ALTER TABLE civicrm_financial_trxn ADD COLUMN contribution_id INT DEFAULT NULL";
264 CRM_Core_DAO
::executeQuery($sql);
266 //pending pay later status handling
268 INSERT INTO civicrm_financial_trxn
269 (contribution_id, payment_instrument_id, currency, total_amount, net_amount, fee_amount, trxn_id, status_id,
270 check_number, to_financial_account_id, from_financial_account_id, trxn_date)
272 SELECT con.id as contribution_id, con.payment_instrument_id, IF(con.currency IN ('{$validCurrencyCodes}'), con.currency, '{$defaultCurrency}')
273 as currency, con.total_amount, con.net_amount, con.fee_amount, con.trxn_id, con.contribution_status_id,
274 con.check_number, efa.financial_account_id as to_financial_account_id, NULL as from_financial_account_id,
275 REPLACE(REPLACE(REPLACE(
277 WHEN con.receive_date IS NOT NULL THEN
279 WHEN con.receipt_date IS NOT NULL THEN
284 , '-', ''), ':', ''), ' ', '') as trxn_date
285 FROM civicrm_contribution con
286 LEFT JOIN civicrm_entity_financial_account efa
287 ON (con.financial_type_id = efa.entity_id AND efa.entity_table = 'civicrm_financial_type'
288 AND efa.account_relationship = {$accountsReceivableAccount})
289 WHERE con.is_pay_later = 1 AND con.contribution_status_id = {$pendingStatus}";
290 CRM_Core_DAO
::executeQuery($sql);
292 //create a temp table to hold financial account id related to payment instruments
293 $tempTableName1 = CRM_Core_DAO
::createTempTableName();
295 $sql = "CREATE TEMPORARY TABLE {$tempTableName1}
297 SELECT ceft.financial_account_id financial_account_id, cov.value as instrument_id
298 FROM civicrm_entity_financial_account ceft
299 INNER JOIN civicrm_option_value cov ON cov.id = ceft.entity_id AND ceft.entity_table = 'civicrm_option_value'
300 INNER JOIN civicrm_option_group cog ON cog.id = cov.option_group_id
301 WHERE cog.name = 'payment_instrument'";
302 CRM_Core_DAO
::executeQuery($sql);
304 //create temp table to process completed / cancelled contribution
305 $tempTableName2 = CRM_Core_DAO
::createTempTableName();
306 $sql = "CREATE TEMPORARY TABLE {$tempTableName2}
308 SELECT con.id as contribution_id, con.payment_instrument_id, IF(con.currency IN ('{$validCurrencyCodes}'), con.currency, '{$defaultCurrency}') as currency,
309 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,
310 REPLACE(REPLACE(REPLACE(
312 WHEN con.receive_date IS NOT NULL THEN
314 WHEN con.receipt_date IS NOT NULL THEN
319 , '-', ''), ':', ''), ' ', '') as trxn_date,
321 WHEN con.payment_instrument_id IS NULL THEN
322 {$financialAccountId}
323 WHEN con.payment_instrument_id IS NOT NULL THEN
324 tpi.financial_account_id
325 END as to_financial_account_id,
326 IF(eft.financial_trxn_id IS NULL, 'insert', eft.financial_trxn_id) as action
327 FROM civicrm_contribution con
328 LEFT JOIN civicrm_entity_financial_trxn eft
329 ON (eft.entity_table = 'civicrm_contribution' AND eft.entity_id = con.id)
330 LEFT JOIN {$tempTableName1} tpi
331 ON con.payment_instrument_id = tpi.instrument_id
332 WHERE con.contribution_status_id IN ({$completedStatus}, {$cancelledStatus})";
333 CRM_Core_DAO
::executeQuery($sql);
335 //handling for completed contribution and cancelled contribution
336 //insertion of new records
338 INSERT INTO civicrm_financial_trxn
339 (contribution_id, payment_instrument_id, currency, total_amount, net_amount, fee_amount, trxn_id, status_id, check_number,
340 to_financial_account_id, from_financial_account_id, trxn_date)
341 SELECT tempI.contribution_id, tempI.payment_instrument_id, tempI.currency, tempI.total_amount, tempI.net_amount,
342 tempI.fee_amount, tempI.trxn_id, tempI.contribution_status_id, tempI.check_number,
343 tempI.to_financial_account_id, tempI.from_financial_account_id, tempI.trxn_date
344 FROM {$tempTableName2} tempI
345 WHERE tempI.action = 'insert';";
346 CRM_Core_DAO
::executeQuery($sql);
348 //update of existing records
350 UPDATE civicrm_financial_trxn ft
351 INNER JOIN {$tempTableName2} tempU
352 ON (tempU.action != 'insert' AND ft.id = tempU.action)
353 SET ft.from_financial_account_id = NULL,
354 ft.to_financial_account_id = tempU.to_financial_account_id,
355 ft.status_id = tempU.contribution_status_id,
356 ft.payment_instrument_id = tempU.payment_instrument_id,
357 ft.check_number = tempU.check_number,
358 ft.contribution_id = tempU.contribution_id;";
359 CRM_Core_DAO
::executeQuery($sql);
361 //insert the -ve transaction rows for cancelled contributions
363 INSERT INTO civicrm_financial_trxn
364 (contribution_id, payment_instrument_id, currency, total_amount, net_amount, fee_amount, trxn_id, status_id,
365 check_number, to_financial_account_id, from_financial_account_id, trxn_date)
366 SELECT ft.contribution_id, ft.payment_instrument_id, ft.currency, -ft.total_amount, ft.net_amount, ft.fee_amount, ft.trxn_id,
367 ft.status_id, ft.check_number, ft.to_financial_account_id, ft.from_financial_account_id, ft.trxn_date
368 FROM civicrm_financial_trxn ft
369 WHERE ft.status_id = {$cancelledStatus};";
370 CRM_Core_DAO
::executeQuery($sql);
372 //inserting entity financial trxn entries if its not present in entity_financial_trxn for completed and pending contribution statuses
373 //this also handles +ve and -ve both transaction entries for a cancelled contribution
375 INSERT INTO civicrm_entity_financial_trxn (entity_table, entity_id, financial_trxn_id, amount)
376 SELECT 'civicrm_contribution', ft.contribution_id, ft.id, ft.total_amount as amount
377 FROM civicrm_financial_trxn ft
378 WHERE contribution_id IS NOT NULL AND
379 ft.id NOT IN (SELECT financial_trxn_id
380 FROM civicrm_entity_financial_trxn
381 WHERE entity_table = 'civicrm_contribution'
382 AND entity_id = ft.contribution_id)";
383 CRM_Core_DAO
::executeQuery($sql);
384 //end of adding financial_trxn records and entity_financial_trxn records related to contribution
386 //update all linked line_item rows
387 // set line_item.financial_type_id = contribution.financial_type_id if contribution page id is null and not participant line item
388 // set line_item.financial_type_id = price_field_value.financial_type_id if contribution page id is set and not participant line item
389 // set line_item.financial_type_id = event.financial_type_id if its participant line item and line_item.price_field_value_id is null
390 // 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
391 $updateLineItemSql = "
392 UPDATE civicrm_line_item li
393 LEFT JOIN civicrm_contribution con
394 ON (li.entity_id = con.id AND li.entity_table = 'civicrm_contribution')
395 LEFT JOIN civicrm_price_field_value cpfv
396 ON li.price_field_value_id = cpfv.id
397 LEFT JOIN civicrm_participant cp
398 ON (li.entity_id = cp.id AND li.entity_table = 'civicrm_participant')
399 LEFT JOIN civicrm_event ce
400 ON ce.id = cp.event_id
401 SET li.financial_type_id = CASE
402 WHEN (con.contribution_page_id IS NULL || li.price_field_value_id IS NULL) AND cp.id IS NULL THEN
403 con.financial_type_id
404 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
405 cpfv.financial_type_id
406 WHEN cp.id IS NOT NULL AND li.price_field_value_id IS NULL THEN
409 CRM_Core_DAO
::executeQuery($updateLineItemSql, $queryParams);
411 //add the financial_item entries
412 //add a temp column so that inserting entity_financial_trxn entries gets easy
413 $sql = "ALTER TABLE civicrm_financial_item ADD COLUMN f_trxn_id INT DEFAULT NULL";
414 CRM_Core_DAO
::executeQuery($sql);
416 //add financial_item entries for contribution completed / pending pay later / cancelled
417 $contributionlineItemSql = "
418 INSERT INTO civicrm_financial_item
419 (transaction_date, contact_id, amount, currency, entity_table, entity_id, description, status_id, financial_account_id, f_trxn_id)
421 SELECT REPLACE(REPLACE(REPLACE(ft.trxn_date, '-', ''), ':', ''), ' ', ''), con.contact_id,
422 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',
423 li.id as line_item_id, li.label as line_item_label,
424 IF(con.contribution_status_id = {$pendingStatus}, {$unpaidStatus}, {$paidStatus}) as status_id, efa.financial_account_id as financial_account_id,
426 FROM civicrm_line_item li
427 INNER JOIN civicrm_contribution con
428 ON (li.entity_id = con.id AND li.entity_table = 'civicrm_contribution')
429 INNER JOIN civicrm_financial_trxn ft
430 ON (con.id = ft.contribution_id)
431 LEFT JOIN civicrm_entity_financial_account efa
432 ON (li.financial_type_id = efa.entity_id AND efa.entity_table = 'civicrm_financial_type'
433 AND efa.account_relationship = {$incomeAccountIs})
434 WHERE con.contribution_status_id IN (%1, %3) OR (con.is_pay_later = 1 AND con.contribution_status_id = %2)";
435 CRM_Core_DAO
::executeQuery($contributionlineItemSql, $queryParams);
437 //add financial_item entries for event
438 $participantLineItemSql = "
439 INSERT INTO civicrm_financial_item
440 (transaction_date, contact_id, amount, currency, entity_table, entity_id, description, status_id, financial_account_id, f_trxn_id)
442 SELECT REPLACE(REPLACE(REPLACE(ft.trxn_date, '-', ''), ':', ''), ' ', ''), con.contact_id,
443 IF(ft.total_amount < 0 AND con.contribution_status_id = %3, -li.line_total, li.line_total) as line_total,
444 con.currency, 'civicrm_line_item', li.id as line_item_id, li.label as line_item_label,
445 IF(con.contribution_status_id = {$pendingStatus}, {$unpaidStatus}, {$paidStatus}) as status_id,
446 efa.financial_account_id as financial_account_id, ft.id as f_trxn_id
447 FROM civicrm_line_item li
448 INNER JOIN civicrm_participant par
449 ON (li.entity_id = par.id AND li.entity_table = 'civicrm_participant')
450 INNER JOIN civicrm_participant_payment pp
451 ON (pp.participant_id = par.id)
452 INNER JOIN civicrm_contribution con
453 ON (pp.contribution_id = con.id)
454 INNER JOIN civicrm_financial_trxn ft
455 ON (con.id = ft.contribution_id)
456 LEFT JOIN civicrm_entity_financial_account efa
457 ON (li.financial_type_id = efa.entity_id AND
458 efa.entity_table = 'civicrm_financial_type' AND efa.account_relationship = {$incomeAccountIs})
459 WHERE con.contribution_status_id IN (%1, %3) OR (con.is_pay_later = 1 AND con.contribution_status_id = %2)";
460 CRM_Core_DAO
::executeQuery($participantLineItemSql, $queryParams);
462 //fee handling for contributions
463 //insert fee entries in financial_trxn for contributions
464 $sql = "ALTER TABLE civicrm_financial_trxn ADD COLUMN is_fee TINYINT DEFAULT NULL";
465 CRM_Core_DAO
::executeQuery($sql);
468 INSERT INTO civicrm_financial_trxn
469 (contribution_id, payment_instrument_id, currency, total_amount, net_amount, fee_amount, trxn_id, status_id, check_number,
470 to_financial_account_id, from_financial_account_id, trxn_date, payment_processor_id, is_fee)
472 SELECT con.id, ft.payment_instrument_id, ft.currency, ft.fee_amount, NULL, NULL, ft.trxn_id, %1 as status_id,
473 ft.check_number, efaFT.financial_account_id as to_financial_account_id, CASE
474 WHEN efaPP.financial_account_id IS NOT NULL THEN
475 efaPP.financial_account_id
476 WHEN tpi.financial_account_id IS NOT NULL THEN
477 tpi.financial_account_id
479 {$financialAccountId}
480 END as from_financial_account_id, ft.trxn_date, ft.payment_processor_id, 1 as is_fee
481 FROM civicrm_contribution con
482 INNER JOIN civicrm_financial_trxn ft
483 ON (ft.contribution_id = con.id)
484 LEFT JOIN civicrm_entity_financial_account efaFT
485 ON (con.financial_type_id = efaFT.entity_id AND efaFT.entity_table = 'civicrm_financial_type'
486 AND efaFT.account_relationship = {$expenseAccountIs})
487 LEFT JOIN civicrm_entity_financial_account efaPP
488 ON (ft.payment_processor_id = efaPP.entity_id AND efaPP.entity_table = 'civicrm_payment_processor'
489 AND efaPP.account_relationship = {$assetAccountIs})
490 LEFT JOIN {$tempTableName1} tpi
491 ON ft.payment_instrument_id = tpi.instrument_id
492 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))
494 CRM_Core_DAO
::executeQuery($sql, $queryParams);
496 //link financial_trxn to contribution
498 INSERT INTO civicrm_entity_financial_trxn
499 (entity_table, entity_id, financial_trxn_id, amount)
500 SELECT 'civicrm_contribution', ft.contribution_id, ft.id, ft.total_amount
501 FROM civicrm_financial_trxn ft
502 WHERE ft.is_fee = 1";
503 CRM_Core_DAO
::executeQuery($sql);
505 //add fee related entries to financial item table
506 $domainId = CRM_Core_Config
::domainID();
507 $domainContactId = CRM_Core_DAO
::getFieldValue('CRM_Core_DAO_Domain', $domainId, 'contact_id');
509 INSERT INTO civicrm_financial_item
510 (transaction_date, contact_id, amount, currency, entity_table, entity_id, description, status_id, financial_account_id, f_trxn_id)
511 SELECT ft.trxn_date, {$domainContactId} as contact_id, ft.total_amount, ft.currency, 'civicrm_financial_trxn', ft.id,
512 'Fee', {$paidStatus} as status_id, ft.to_financial_account_id as financial_account_id, ft.id as f_trxn_id
513 FROM civicrm_financial_trxn ft
514 WHERE ft.is_fee = 1;";
515 CRM_Core_DAO
::executeQuery($sql);
517 //add entries to entity_financial_trxn table
519 INSERT INTO civicrm_entity_financial_trxn (entity_table, entity_id, financial_trxn_id, amount)
520 SELECT 'civicrm_financial_item' as entity_table, fi.id as entity_id, fi.f_trxn_id as financial_trxn_id, fi.amount
521 FROM civicrm_financial_item fi";
522 CRM_Core_DAO
::executeQuery($sql);
524 //drop the temparory columns
525 $sql = "ALTER TABLE civicrm_financial_trxn
526 DROP COLUMN contribution_id,
527 DROP COLUMN is_fee;";
528 CRM_Core_DAO
::executeQuery($sql);
530 $sql = "ALTER TABLE civicrm_financial_item DROP f_trxn_id";
531 CRM_Core_DAO
::executeQuery($sql);
536 function createDomainContacts() {
537 $domainParams = $context = array();
539 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',
540 ADD CONSTRAINT `FK_civicrm_domain_contact_id` FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact` (`id`);";
541 CRM_Core_DAO
::executeQuery($query, CRM_Core_DAO
::$_nullArray, TRUE, NULL, FALSE, FALSE);
543 $query = 'SELECT cd.id, cd.name, ce.email FROM `civicrm_domain` cd
544 LEFT JOIN civicrm_loc_block clb ON clb.id = cd. loc_block_id
545 LEFT JOIN civicrm_email ce ON ce.id = clb.email_id ;' ;
546 $dao = CRM_Core_DAO
::executeQuery($query);
547 while($dao->fetch()) {
549 'sort_name' => $dao->name
,
550 'display_name' => $dao->name
,
551 'legal_name' => $dao->name
,
552 'organization_name' => $dao->name
,
553 'contact_type' => 'Organization'
555 $query = "SELECT cc.id FROM `civicrm_contact` cc
556 LEFT JOIN civicrm_email ce ON ce.contact_id = cc.id
557 WHERE cc.contact_type = 'Organization' AND cc.organization_name = '{$dao->name}' ";
559 $query .= " AND ce.email = '{$dao->email}' ";
561 $contactID = CRM_Core_DAO
::singleValueQuery($query);
562 $context[1] = $dao->name
;
563 if (empty($contactID)) {
564 $contact = CRM_Contact_BAO_Contact
::add($params);
565 $contactID = $contact->id
;
566 $context[0] = 'added';
569 $context[0] = 'merged';
571 $domainParams['contact_id'] = $contactID;
572 CRM_Core_BAO_Domain
::edit($domainParams, $dao->id
);
577 function task_4_3_alpha1_checkDBConstraints() {
578 //checking whether the foreign key exists before dropping it CRM-11260
579 $config = CRM_Core_Config
::singleton();
580 $dbUf = DB
::parseDSN($config->dsn
);
583 'autorenewal_msg_id' => array('tableName' => 'civicrm_membership_type', 'fkey' => 'FK_civicrm_membership_autorenewal_msg_id'),
584 'to_account_id' => array('tableName' => 'civicrm_financial_trxn', 'constraintName' => 'civicrm_financial_trxn_ibfk_2'),
585 'from_account_id' => array('tableName' => 'civicrm_financial_trxn', 'constraintName' => 'civicrm_financial_trxn_ibfk_1'),
586 'contribution_type_id' => array('tableName' => 'civicrm_contribution_recur', 'fkey' => 'FK_civicrm_contribution_recur_contribution_type_id'),
588 $query = "SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
589 WHERE table_name = 'civicrm_contribution_recur'
590 AND constraint_name = 'FK_civicrm_contribution_recur_contribution_type_id'
591 AND TABLE_SCHEMA = '{$dbUf['database']}'";
593 $dao = CRM_Core_DAO
::executeQuery($query, $params, TRUE, NULL, FALSE, FALSE);
594 foreach($tables as $columnName => $value){
595 if ($value['tableName'] == 'civicrm_membership_type' ||
$value['tableName'] == 'civicrm_contribution_recur') {
596 $foreignKeyExists = CRM_Core_DAO
::checkConstraintExists($value['tableName'], $value['fkey']);
597 $fKey = $value['fkey'];
599 $foreignKeyExists = CRM_Core_DAO
::checkFKConstraintInFormat($value['tableName'], $columnName);
600 $fKey = "`FK_{$value['tableName']}_{$columnName}`";
602 if ($foreignKeyExists ||
$value['tableName'] == 'civicrm_financial_trxn') {
603 if ($value['tableName'] != 'civicrm_contribution_recur' ||
($value['tableName'] == 'civicrm_contribution_recur' && $dao->N
)) {
604 $constraintName = $foreignKeyExists ?
$fKey : $value['constraintName'];
605 CRM_Core_DAO
::executeQuery("ALTER TABLE {$value['tableName']} DROP FOREIGN KEY {$constraintName}", $params, TRUE, NULL, FALSE, FALSE);
607 CRM_Core_DAO
::executeQuery("ALTER TABLE {$value['tableName']} DROP INDEX {$fKey}", $params, TRUE, NULL, FALSE, FALSE);
610 // check if column contact_id is present or not in civicrm_financial_account
611 $fieldExists = CRM_Core_DAO
::checkFieldExists('civicrm_financial_account', 'contact_id', FALSE);
613 $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);";
614 CRM_Core_DAO
::executeQuery($query, $params, TRUE, NULL, FALSE, FALSE);
619 * Read creation and modification times from civicrm_log; add
620 * them to civicrm_contact.
622 function convertTimestamps(CRM_Queue_TaskContext
$ctx, $startId, $endId) {
624 SELECT entity_id, min(modified_date) AS created, max(modified_date) AS modified
626 WHERE entity_table = 'civicrm_contact'
627 AND entity_id BETWEEN %1 AND %2
631 1 => array($startId, 'Integer'),
632 2 => array($endId, 'Integer'),
634 $dao = CRM_Core_DAO
::executeQuery($sql, $params);
635 while ($dao->fetch()) {
636 // FIXME civicrm_log.modified_date is DATETIME; civicrm_contact.modified_date is TIMESTAMP
637 CRM_Core_DAO
::executeQuery(
638 'UPDATE civicrm_contact SET created_date = %1, modified_date = %2 WHERE id = %3',
640 1 => array($dao->created
, 'String'),
641 2 => array($dao->modified
, 'String'),
642 3 => array($dao->entity_id
, 'Integer'),
651 * Update phones CRM-11292
653 * @return bool TRUE for success
655 static function phoneNumeric(CRM_Queue_TaskContext
$ctx) {
656 CRM_Core_DAO
::executeQuery(CRM_Contact_BAO_Contact
::DROP_STRIP_FUNCTION_43
);
657 CRM_Core_DAO
::executeQuery(CRM_Contact_BAO_Contact
::CREATE_STRIP_FUNCTION_43
);
658 CRM_Core_DAO
::executeQuery("UPDATE civicrm_phone SET phone_numeric = civicrm_strip_non_numeric(phone)");
663 * (Queue Task Callback)
665 static function task_4_3_x_runSql(CRM_Queue_TaskContext
$ctx, $rev) {
666 $upgrade = new CRM_Upgrade_Form();
667 $upgrade->processSQL($rev);
673 * Syntatic sugar for adding a task which (a) is in this class and (b) has
676 * After passing the $funcName, you can also pass parameters that will go to
677 * the function. Note that all params must be serializable.
679 protected function addTask($title, $funcName) {
680 $queue = CRM_Queue_Service
::singleton()->load(array(
682 'name' => CRM_Upgrade_Form
::QUEUE_NAME
,
685 $args = func_get_args();
686 $title = array_shift($args);
687 $funcName = array_shift($args);
688 $task = new CRM_Queue_Task(
689 array(get_class($this), $funcName),
693 $queue->createItem($task, array('weight' => -1));