Merge remote-tracking branch 'upstream/4.4' into 4.4-master-2014-04-03-23-46-36
[civicrm-core.git] / CRM / Upgrade / Incremental / php / FourThree.php
1 <?php
2
3 /*
4 +--------------------------------------------------------------------+
5 | CiviCRM version 4.5 |
6 +--------------------------------------------------------------------+
7 | Copyright CiviCRM LLC (c) 2004-2014 |
8 +--------------------------------------------------------------------+
9 | This file is a part of CiviCRM. |
10 | |
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. |
14 | |
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. |
19 | |
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 +--------------------------------------------------------------------+
26 */
27
28 /**
29 *
30 * @package CRM
31 * @copyright CiviCRM LLC (c) 2004-2014
32 * $Id$
33 *
34 */
35 class CRM_Upgrade_Incremental_php_FourThree {
36 const BATCH_SIZE = 5000;
37
38 function verifyPreDBstate(&$errors) {
39 return TRUE;
40 }
41
42 /**
43 * Compute any messages which should be displayed beforeupgrade
44 *
45 * Note: This function is called iteratively for each upcoming
46 * revision to the database.
47 *
48 * @param $postUpgradeMessage string, alterable
49 * @param $rev string, a version number, e.g. '4.3.alpha1', '4.3.beta3', '4.3.0'
50 * @return void|bool
51 */
52 function setPreUpgradeMessage(&$preUpgradeMessage, $rev, $currentVer = NULL) {
53 if ($rev == '4.3.beta3') {
54 //CRM-12084
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);
58
59 if ($count > 0) {
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);
62 return FALSE;
63 }
64 }
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
67 // upgrade pages ... except when we're in Drush.
68 if (!function_exists('drush_main')) {
69 theme('item_list', array()); // force-load theme registry
70 $theme_registry = theme_get_registry();
71 if (!isset($theme_registry['page']['preprocess functions']) || FALSE === array_search('civicrm_preprocess_page_inject', $theme_registry['page']['preprocess functions'])) {
72 CRM_Core_Error::fatal('Please reset the Drupal cache (Administer => Site Configuration => Performance => Clear cached data))');
73 }
74 }
75 }
76
77 if ($rev == '4.3.6') {
78 $constraintArray = array(
79 'civicrm_contact' => 'contact_id',
80 'civicrm_payment_processor' => 'payment_processor_id',
81 );
82
83 if (version_compare('4.1alpha1', $currentVer) <= 0) {
84 $constraintArray['civicrm_campaign'] = 'campaign_id';
85 }
86
87 if (version_compare('4.3alpha1', $currentVer) <= 0) {
88 $constraintArray['civicrm_financial_type'] = 'financial_type_id';
89 }
90
91 foreach ($constraintArray as $key => $value) {
92 $query = "SELECT contri_recur.id FROM civicrm_contribution_recur contri_recur LEFT JOIN {$key} ON contri_recur.{$value} = {$key}.id
93 WHERE {$key}.id IS NULL";
94 if ($value != 'contact_id') {
95 $query .= " AND contri_recur.{$value} IS NOT NULL ";
96 }
97 $dao = CRM_Core_DAO::executeQuery($query);
98 if ($dao->N) {
99 $invalidDataMessage = '<strong>Oops, it looks like you have orphaned recurring contribution records in your database. Before this upgrade can complete they will need to be fixed or deleted. <a href="http://wiki.civicrm.org/confluence/display/CRMDOC/Fixing+Orphaned+Contribution+Recur+Records" target="_blank">You can review steps to correct this situation on the documentation wiki.</a></strong>';
100 CRM_Core_Error::fatal($invalidDataMessage);
101 return FALSE;
102 }
103 }
104 }
105 }
106
107 /**
108 * Compute any messages which should be displayed after upgrade
109 *
110 * @param $postUpgradeMessage string, alterable
111 * @param $rev string, an intermediate version; note that setPostUpgradeMessage is called repeatedly with different $revs
112 * @return void
113 */
114 function setPostUpgradeMessage(&$postUpgradeMessage, $rev) {
115 if ($rev == '4.3.alpha1') {
116 // check if CiviMember component is enabled
117 $config = CRM_Core_Config::singleton();
118 if (in_array('CiviMember', $config->enableComponents)) {
119 $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.');
120 $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.');
121 }
122
123 //CRM-11636
124 //here we do the financial type check and migration
125 $isDefaultsModified = self::_checkAndMigrateDefaultFinancialTypes();
126 if($isDefaultsModified) {
127 $postUpgradeMessage .= '<br />' . ts('Please review all price set financial type assignments.');
128 }
129 list($context, $orgName) = self::createDomainContacts();
130 if ($context == 'added') {
131 $postUpgradeMessage .= '<br />' . ts("A new organization contact has been added as the default domain contact using the information from your Organization Address and Contact Info settings: '%1'.", array(1 => $orgName));
132 }
133 elseif ($context == 'merged') {
134 $postUpgradeMessage .= '<br />' . ts("The existing organization contact record for '%1' has been marked as the default domain contact, and has been updated with information from your Organization Address and Contact Info settings.", array(1 => $orgName));
135 }
136
137 $providerExists = CRM_Core_DAO::singleValueQuery("SELECT id FROM civicrm_sms_provider LIMIT 1");
138 if ($providerExists) {
139 $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'));
140 }
141 }
142
143 if ($rev == '4.3.alpha2') {
144 $sql = "
145 SELECT title, id
146 FROM civicrm_action_schedule
147 WHERE entity_value = '' OR entity_value IS NULL
148 ";
149
150 $dao = CRM_Core_DAO::executeQuery($sql);
151 $reminder = array();
152 $list = '';
153 while ($dao->fetch()) {
154 $reminder[$dao->id] = $dao->title;
155 $list .= "<li>{$dao->title}</li>";
156 }
157 if (!empty($reminder)) {
158 $list = "<br /><ul>" . $list . "</ul>";
159 $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));
160 }
161 }
162 if ($rev == '4.3.beta2') {
163 $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).');
164 }
165
166 if ($rev == '4.3.beta5') {
167 $postUpgradeMessage .= '<br />' . ts("If you are interested in trying out the new Accounting Integration features, please review user permissions and assign the new 'manual batch' permissions as appropriate.");
168
169 // CRM-12155
170 $query = "
171 SELECT ceft.id FROM `civicrm_financial_trxn` cft
172 LEFT JOIN civicrm_entity_financial_trxn ceft
173 ON ceft.financial_trxn_id = cft.id AND ceft.entity_table = 'civicrm_contribution'
174 LEFT JOIN civicrm_contribution cc
175 ON cc.id = ceft.entity_id AND ceft.entity_table = 'civicrm_contribution'
176 WHERE cc.id IS NULL
177 ";
178
179 $dao = CRM_Core_DAO::executeQuery($query);
180 $isOrphanData = TRUE;
181 if (!$dao->N) {
182 $query = "
183 SELECT cli.id FROM civicrm_line_item cli
184 LEFT JOIN civicrm_contribution cc ON cli.entity_id = cc.id AND cli.entity_table = 'civicrm_contribution'
185 LEFT JOIN civicrm_participant cp ON cli.entity_id = cp.id AND cli.entity_table = 'civicrm_participant'
186 WHERE CASE WHEN cli.entity_table = 'civicrm_contribution'
187 THEN cc.id IS NULL
188 ELSE cp.id IS NULL
189 END
190 ";
191 $dao = CRM_Core_DAO::executeQuery($query);
192 if (!$dao->N) {
193 $revPattern = '/^((\d{1,2})\.\d{1,2})\.(\d{1,2}|\w{4,7})?$/i';
194 preg_match($revPattern, $currentVer, $version);
195 if ($version[1] >= 4.3) {
196 $query = "
197 SELECT cfi.id
198 FROM civicrm_financial_item cfi
199 LEFT JOIN civicrm_entity_financial_trxn ceft ON ceft.entity_table = 'civicrm_financial_item' and cfi.id = ceft.entity_id
200 WHERE ceft.entity_id IS NULL;
201 ";
202 $dao = CRM_Core_DAO::executeQuery($query);
203 if (!$dao->N) {
204 $isOrphanData = FALSE;
205 }
206 }
207 else {
208 $isOrphanData = FALSE;
209 }
210 }
211 }
212
213 if ($isOrphanData) {
214 $postUpgradeMessage .= "</br> <strong>" . ts('Your database contains extraneous financial records related to deleted contacts and contributions. These records should not affect the site and will not appear in reports, search results or exports. However you may wish to clean them up. Refer to <a href="%1" target="_blank">this wiki page for details</a>.
215 ', array( 1 => 'http://wiki.civicrm.org/confluence/display/CRMDOC/Clean+up+extraneous+financial+data+-+4.3+upgrades')) . "</strong>";
216 }
217 }
218 if ($rev == '4.3.4') {
219 $postUpgradeMessage .= '<br />' . ts('System Administrator Alert: If you are running scheduled jobs using CLI.php, you will need to reconfigure cron tasks to include a password. Scheduled jobs will no longer run if the password is not provided (<a href="%1" target="_blank">learn more</a>).',
220 array( 1 => 'http://wiki.civicrm.org/confluence/display/CRMDOC/Managing+Scheduled+Jobs'));
221 }
222 if ($rev == '4.3.5') {
223 $postUpgradeMessage .= '<br />' . ts('Default versions of the following System Workflow Message Templates have been modified to handle new functionality: <ul><li>Events - Registration Confirmation and Receipt (on-line)</li><li>Events - Registration Confirmation and Receipt (off-line)</li></ul> If you have modified these templates, please review the new default versions and implement updates as needed to your copies (Administer > Communications > Message Templates > System Workflow Messages).');
224 }
225 if ($rev == '4.3.6') {
226 $flag = CRM_Core_DAO::singleValueQuery('SELECT count(ccp.id) FROM civicrm_contribution_product ccp
227 INNER JOIN civicrm_product cp ON ccp.product_id = cp.id
228 WHERE ccp.financial_type_id IS NULL and cp.cost > 0');
229 if ($flag) {
230 $postUpgradeMessage .= '<br />' . ts('Your database contains one or more premiums which have a cost but are not linked to a financial type. If you are exporting transations to an accounting package, this will result in unbalanced transactions. <a href="%1" target="_blank">You can review steps to correct this situation on the wiki.</a>',
231 array( 1 => 'http://wiki.civicrm.org/confluence/display/CRMDOC/Fixing+Issues+Caused+by+Missing+Cost+of+Goods+Account+-+4.3+Upgrades'));
232 }
233 }
234 }
235
236 function upgrade_4_3_alpha1($rev) {
237 self::task_4_3_alpha1_checkDBConstraints();
238
239 // add indexes for civicrm_entity_financial_trxn
240 // CRM-12141
241 $this->addTask('Check/Add indexes for civicrm_entity_financial_trxn', 'task_4_3_x_checkIndexes', $rev);
242 // task to process sql
243 $this->addTask(ts('Upgrade DB to %1: SQL', array(1 => '4.3.alpha1')), 'task_4_3_x_runSql', $rev);
244
245 //CRM-11636
246 $this->addTask('Populate financial type values for price records', 'assignFinancialTypeToPriceRecords');
247 //CRM-11514 create financial records for contributions
248 $this->addTask('Create financial records for contributions', 'createFinancialRecords');
249
250 $minId = CRM_Core_DAO::singleValueQuery('SELECT coalesce(min(id),0) FROM civicrm_contact');
251 $maxId = CRM_Core_DAO::singleValueQuery('SELECT coalesce(max(id),0) FROM civicrm_contact');
252 for ($startId = $minId; $startId <= $maxId; $startId += self::BATCH_SIZE) {
253 $endId = $startId + self::BATCH_SIZE - 1;
254 $title = ts('Upgrade timestamps (%1 => %2)', array(1 => $startId, 2 => $endId));
255 $this->addTask($title, 'convertTimestamps', $startId, $endId);
256 }
257
258 // CRM-10893
259 // fix WP access control
260 $config = CRM_Core_Config::singleton( );
261 if ($config->userFramework == 'WordPress') {
262 civicrm_wp_set_capabilities( );
263 }
264
265 // Update phones CRM-11292.
266 $this->addTask('Upgrade Phone Numbers', 'phoneNumeric');
267
268 return TRUE;
269 }
270
271 function upgrade_4_3_alpha2($rev) {
272 //CRM-11847
273 $isColumnPresent = CRM_Core_DAO::checkFieldExists('civicrm_dedupe_rule_group', 'is_default');
274 if ($isColumnPresent) {
275 CRM_Core_DAO::executeQuery('ALTER TABLE civicrm_dedupe_rule_group DROP COLUMN is_default');
276 }
277 $this->addTask(ts('Upgrade DB to %1: SQL', array(1 => '4.3.alpha2')), 'task_4_3_x_runSql', $rev);
278 }
279
280 function upgrade_4_3_alpha3($rev) {
281 $this->addTask(ts('Upgrade DB to %1: SQL', array(1 => '4.3.alpha3')), 'task_4_3_x_runSql', $rev);
282 }
283
284 function upgrade_4_3_beta2($rev) {
285 $this->addTask(ts('Upgrade DB to %1: SQL', array(1 => '4.3.beta2')), 'task_4_3_x_runSql', $rev);
286
287 // CRM-12002
288 if (
289 CRM_Core_DAO::checkTableExists('log_civicrm_line_item') &&
290 CRM_Core_DAO::checkFieldExists('log_civicrm_line_item', 'label')
291 ) {
292 CRM_Core_DAO::executeQuery('ALTER TABLE `log_civicrm_line_item` CHANGE `label` `label` VARCHAR(255) NULL DEFAULT NULL');
293 }
294 }
295
296 function upgrade_4_3_beta3($rev) {
297 $this->addTask(ts('Upgrade DB to %1: SQL', array(1 => '4.3.beta3')), 'task_4_3_x_runSql', $rev);
298 // CRM-12065
299 $query = "SELECT id, form_values FROM civicrm_report_instance WHERE form_values LIKE '%contribution_type%'";
300 $this->addTask('Replace contribution_type to financial_type in table civicrm_report_instance', 'replaceContributionTypeId', $query, 'reportInstance');
301 $query = "SELECT * FROM civicrm_saved_search WHERE form_values LIKE '%contribution_type%'";
302 $this->addTask('Replace contribution_type to financial_type in table civicrm_saved_search', 'replaceContributionTypeId', $query, 'savedSearch');
303 }
304
305 function upgrade_4_3_beta4($rev) {
306 $this->addTask(ts('Upgrade DB to %1: SQL', array(1 => '4.3.beta4')), 'task_4_3_x_runSql', $rev);
307 // add indexes for civicrm_entity_financial_trxn
308 // CRM-12141
309 $this->addTask('Check/Add indexes for civicrm_entity_financial_trxn', 'task_4_3_x_checkIndexes', $rev);
310 }
311
312 function upgrade_4_3_beta5($rev) {
313 // CRM-12205
314 if (
315 CRM_Core_DAO::checkTableExists('log_civicrm_financial_trxn') &&
316 CRM_Core_DAO::checkFieldExists('log_civicrm_financial_trxn', 'trxn_id')
317 ) {
318 CRM_Core_DAO::executeQuery('ALTER TABLE `log_civicrm_financial_trxn` CHANGE `trxn_id` `trxn_id` VARCHAR(255) NULL DEFAULT NULL');
319 }
320 // CRM-12142 - some sites didn't get this column added yet, and sites which installed 4.3 from scratch will already have it
321 // CRM-12367 - add this column to single lingual sites only
322 $upgrade = new CRM_Upgrade_Form();
323 if (!$upgrade->multilingual &&
324 !CRM_Core_DAO::checkFieldExists('civicrm_premiums', 'premiums_nothankyou_label')
325 ) {
326 $query = "
327 ALTER TABLE civicrm_premiums
328 ADD COLUMN premiums_nothankyou_label varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL
329 COMMENT 'Label displayed for No Thank-you option in premiums block (e.g. No thank you)'
330 ";
331 CRM_Core_DAO::executeQuery($query, array(), TRUE, NULL, FALSE, FALSE);
332 }
333 $this->addTask(ts('Upgrade DB to %1: SQL', array(1 => '4.3.beta5')), 'task_4_3_x_runSql', $rev);
334 }
335
336 function upgrade_4_3_4($rev) {
337 $this->addTask(ts('Upgrade DB to %1: SQL', array(1 => '4.3.4')), 'task_4_3_x_runSql', $rev);
338 }
339
340 function upgrade_4_3_5($rev) {
341 // CRM-12156
342 $config = CRM_Core_Config::singleton();
343 $dbname = DB::parseDSN($config->dsn);
344 $sql = "SELECT DELETE_RULE
345 FROM information_schema.REFERENTIAL_CONSTRAINTS
346 WHERE CONSTRAINT_NAME = 'FK_civicrm_financial_item_contact_id'
347 AND CONSTRAINT_SCHEMA = %1";
348 $params = array(1 => array($dbname['database'], 'String'));
349 $onDelete = CRM_Core_DAO::singleValueQuery($sql, $params, TRUE, FALSE);
350
351 if ($onDelete != 'CASCADE') {
352 $query = "ALTER TABLE `civicrm_financial_item`
353 DROP FOREIGN KEY FK_civicrm_financial_item_contact_id,
354 DROP INDEX FK_civicrm_financial_item_contact_id;";
355 CRM_Core_DAO::executeQuery($query, array(), TRUE, NULL, FALSE, FALSE);
356 $query = "
357 ALTER TABLE `civicrm_financial_item`
358 ADD CONSTRAINT `FK_civicrm_financial_item_contact_id` FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE;
359 ";
360 CRM_Core_DAO::executeQuery($query, array(), TRUE, NULL, FALSE, FALSE);
361 }
362 $this->addTask(ts('Upgrade DB to %1: SQL', array(1 => '4.3.5')), 'task_4_3_x_runSql', $rev);
363 }
364
365 function upgrade_4_3_6($rev) {
366 //CRM-13094
367 $this->addTask(ts('Add missing contraints'), 'addMissingConstraints', $rev);
368 //CRM-13088
369 $this->addTask('Add ON DELETE Options for constraints', 'task_4_3_x_checkConstraints', $rev);
370 $this->addTask(ts('Upgrade DB to %1: SQL', array(1 => '4.3.6')), 'task_4_3_x_runSql', $rev);
371 // CRM-12844
372 // update line_item, financial_trxn and financial_item table for recurring contributions
373 $this->addTask('Update financial_account_id in financial_trxn table', 'updateFinancialTrxnData', $rev);
374 $this->addTask('Update Line Item Data', 'updateLineItemData', $rev);
375 }
376
377 //CRM-11636
378 function assignFinancialTypeToPriceRecords() {
379 $upgrade = new CRM_Upgrade_Form();
380 //here we update price set entries
381 $sqlFinancialIds = "
382 SELECT id, LCASE(name) name
383 FROM civicrm_financial_type
384 WHERE name IN ('Donation', 'Event Fee', 'Member Dues');
385 ";
386 $daoFinancialIds = CRM_Core_DAO::executeQuery($sqlFinancialIds);
387 while($daoFinancialIds->fetch()) {
388 $financialIds[$daoFinancialIds->name] = $daoFinancialIds->id;
389 }
390 $sqlPriceSetUpdate = "
391 UPDATE civicrm_price_set ps
392 SET ps.financial_type_id =
393 CASE
394 WHEN ps.extends LIKE '%1%' THEN {$financialIds['event fee']}
395 WHEN ps.extends LIKE '2' THEN {$financialIds['donation']}
396 WHEN ps.extends LIKE '3' THEN {$financialIds['member dues']}
397 END
398 WHERE financial_type_id IS NULL
399 ";
400 CRM_Core_DAO::executeQuery($sqlPriceSetUpdate);
401
402 //here we update price field value rows
403 $sqlPriceFieldValueUpdate = "
404 UPDATE civicrm_price_field_value pfv
405 LEFT JOIN civicrm_membership_type mt ON (pfv.membership_type_id = mt.id)
406 INNER JOIN civicrm_price_field pf ON (pfv.price_field_id = pf.id)
407 INNER JOIN civicrm_price_set ps ON (pf.price_set_id = ps.id)
408 SET pfv.financial_type_id =
409 CASE
410 WHEN pfv.membership_type_id IS NOT NULL THEN mt.financial_type_id
411 WHEN pfv.membership_type_id IS NULL THEN ps.financial_type_id
412 END
413 ";
414 CRM_Core_DAO::executeQuery($sqlPriceFieldValueUpdate);
415
416 return TRUE;
417 }
418
419 static function _checkAndMigrateDefaultFinancialTypes() {
420 $modifiedDefaults = FALSE;
421 //insert types if not exists
422 $sqlFetchTypes = "
423 SELECT id, name
424 FROM civicrm_contribution_type
425 WHERE name IN ('Donation', 'Event Fee', 'Member Dues') AND is_active =1
426 ";
427 $daoFetchTypes = CRM_Core_DAO::executeQuery($sqlFetchTypes);
428
429 if ($daoFetchTypes->N < 3) {
430 $modifiedDefaults = TRUE;
431 $insertStatments = array (
432 'Donation' => "('Donation', 0, 1, 1)",
433 'Member' => "('Member Dues', 0, 1, 1)",
434 'Event Fee' => "('Event Fee', 0, 1, 0)",
435 );
436 foreach ($insertStatments as $values) {
437 $query = "
438 INSERT INTO civicrm_contribution_type (name, is_reserved, is_active, is_deductible)
439 VALUES $values
440 ON DUPLICATE KEY UPDATE is_active = 1
441 ";
442 CRM_Core_DAO::executeQuery($query);
443 }
444 }
445 return $modifiedDefaults;
446 }
447
448 function createFinancialRecords() {
449 $upgrade = new CRM_Upgrade_Form();
450
451 // update civicrm_entity_financial_trxn.amount = civicrm_financial_trxn.total_amount
452 $query = "
453 UPDATE civicrm_entity_financial_trxn ceft
454 LEFT JOIN civicrm_financial_trxn cft ON cft.id = ceft.financial_trxn_id
455 SET ceft.amount = total_amount
456 WHERE cft.net_amount IS NOT NULL
457 AND ceft.entity_table = 'civicrm_contribution'
458 ";
459 CRM_Core_DAO::executeQuery($query);
460
461 $contributionStatus = CRM_Contribute_PseudoConstant::contributionStatus(NULL, 'name');
462 $completedStatus = array_search('Completed', $contributionStatus);
463 $pendingStatus = array_search('Pending', $contributionStatus);
464 $cancelledStatus = array_search('Cancelled', $contributionStatus);
465 $queryParams = array(
466 1 => array($completedStatus, 'Integer'),
467 2 => array($pendingStatus, 'Integer'),
468 3 => array($cancelledStatus, 'Integer')
469 );
470
471 $accountType = key(CRM_Core_PseudoConstant::accountOptionValues('financial_account_type', NULL, " AND v.name = 'Asset' "));
472 $query = "
473 SELECT id
474 FROM civicrm_financial_account
475 WHERE is_default = 1
476 AND financial_account_type_id = {$accountType}
477 ";
478 $financialAccountId = CRM_Core_DAO::singleValueQuery($query);
479
480 $accountRelationsips = CRM_Core_PseudoConstant::get('CRM_Financial_DAO_EntityFinancialAccount',
481 'account_relationship', CRM_Core_DAO::$_nullArray, 'validate');
482
483 $accountsReceivableAccount = array_search('Accounts Receivable Account is', $accountRelationsips);
484 $incomeAccountIs = array_search('Income Account is', $accountRelationsips);
485 $assetAccountIs = array_search('Asset Account is', $accountRelationsips);
486 $expenseAccountIs = array_search('Expense Account is', $accountRelationsips);
487
488 $financialItemStatus = CRM_Core_PseudoConstant::get('CRM_Financial_DAO_FinancialItem', 'status_id',
489 CRM_Core_DAO::$_nullArray, 'validate');
490 $unpaidStatus = array_search('Unpaid', $financialItemStatus);
491 $paidStatus = array_search('Paid', $financialItemStatus);
492
493 $validCurrencyCodes = CRM_Core_PseudoConstant::currencyCode();
494 $validCurrencyCodes = implode("','", $validCurrencyCodes);
495 $config = CRM_Core_Config::singleton();
496 $defaultCurrency = $config->defaultCurrency;
497 $now = date( 'YmdHis' );
498
499 //adding financial_trxn records and entity_financial_trxn records related to contribution
500 //Add temp column for easy entry in entity_financial_trxn
501 $sql = "ALTER TABLE civicrm_financial_trxn ADD COLUMN contribution_id INT DEFAULT NULL";
502 CRM_Core_DAO::executeQuery($sql);
503
504 //pending pay later status handling
505 $sql = "
506 INSERT INTO civicrm_financial_trxn
507 (contribution_id, payment_instrument_id, currency, total_amount, net_amount, fee_amount, trxn_id, status_id,
508 check_number, to_financial_account_id, from_financial_account_id, trxn_date)
509 SELECT con.id as contribution_id, con.payment_instrument_id,
510 IF(con.currency IN ('{$validCurrencyCodes}'), con.currency, '{$defaultCurrency}') as currency,
511 con.total_amount, con.net_amount, con.fee_amount, con.trxn_id, con.contribution_status_id,
512 con.check_number, efa.financial_account_id as to_financial_account_id, NULL as from_financial_account_id,
513 REPLACE(REPLACE(REPLACE(
514 CASE
515 WHEN con.receive_date IS NOT NULL THEN
516 con.receive_date
517 WHEN con.receipt_date IS NOT NULL THEN
518 con.receipt_date
519 ELSE
520 {$now}
521 END
522 , '-', ''), ':', ''), ' ', '') as trxn_date
523 FROM civicrm_contribution con
524 LEFT JOIN civicrm_entity_financial_account efa
525 ON (con.financial_type_id = efa.entity_id AND efa.entity_table = 'civicrm_financial_type'
526 AND efa.account_relationship = {$accountsReceivableAccount})
527 WHERE con.is_pay_later = 1
528 AND con.contribution_status_id = {$pendingStatus}
529 ";
530 CRM_Core_DAO::executeQuery($sql);
531
532 //create a temp table to hold financial account id related to payment instruments
533 $tempTableName1 = CRM_Core_DAO::createTempTableName();
534
535 $sql = "
536 CREATE TEMPORARY TABLE {$tempTableName1}
537 SELECT ceft.financial_account_id financial_account_id, cov.value as instrument_id
538 FROM civicrm_entity_financial_account ceft
539 INNER JOIN civicrm_option_value cov ON cov.id = ceft.entity_id AND ceft.entity_table = 'civicrm_option_value'
540 INNER JOIN civicrm_option_group cog ON cog.id = cov.option_group_id
541 WHERE cog.name = 'payment_instrument'
542 ";
543 CRM_Core_DAO::executeQuery($sql);
544
545 //CRM-12141
546 $sql = "ALTER TABLE {$tempTableName1} ADD INDEX index_instrument_id (instrument_id(200));";
547 CRM_Core_DAO::executeQuery($sql);
548
549 //create temp table to process completed / cancelled contribution
550 $tempTableName2 = CRM_Core_DAO::createTempTableName();
551 $sql = "
552 CREATE TEMPORARY TABLE {$tempTableName2}
553 SELECT con.id as contribution_id, con.payment_instrument_id,
554 IF(con.currency IN ('{$validCurrencyCodes}'), con.currency, '{$defaultCurrency}') as currency,
555 con.total_amount, con.net_amount, con.fee_amount, con.trxn_id, con.contribution_status_id,
556 con.check_number, NULL as from_financial_account_id,
557 REPLACE(REPLACE(REPLACE(
558 CASE
559 WHEN con.receive_date IS NOT NULL THEN
560 con.receive_date
561 WHEN con.receipt_date IS NOT NULL THEN
562 con.receipt_date
563 ELSE
564 {$now}
565 END
566 , '-', ''), ':', ''), ' ', '') as trxn_date,
567 CASE
568 WHEN con.payment_instrument_id IS NULL THEN
569 {$financialAccountId}
570 WHEN con.payment_instrument_id IS NOT NULL THEN
571 tpi.financial_account_id
572 END as to_financial_account_id,
573 IF(eft.financial_trxn_id IS NULL, 'insert', eft.financial_trxn_id) as action
574 FROM civicrm_contribution con
575 LEFT JOIN civicrm_entity_financial_trxn eft
576 ON (eft.entity_table = 'civicrm_contribution' AND eft.entity_id = con.id)
577 LEFT JOIN {$tempTableName1} tpi
578 ON con.payment_instrument_id = tpi.instrument_id
579 WHERE con.contribution_status_id IN ({$completedStatus}, {$cancelledStatus})
580 ";
581 CRM_Core_DAO::executeQuery($sql);
582
583 // CRM-12141
584 $sql = "ALTER TABLE {$tempTableName2} ADD INDEX index_action (action);";
585 CRM_Core_DAO::executeQuery($sql);
586
587 //handling for completed contribution and cancelled contribution
588 //insertion of new records
589 $sql = "
590 INSERT INTO civicrm_financial_trxn
591 (contribution_id, payment_instrument_id, currency, total_amount, net_amount, fee_amount, trxn_id, status_id, check_number,
592 to_financial_account_id, from_financial_account_id, trxn_date)
593 SELECT tempI.contribution_id, tempI.payment_instrument_id, tempI.currency, tempI.total_amount, tempI.net_amount,
594 tempI.fee_amount, tempI.trxn_id, tempI.contribution_status_id, tempI.check_number,
595 tempI.to_financial_account_id, tempI.from_financial_account_id, tempI.trxn_date
596 FROM {$tempTableName2} tempI
597 WHERE tempI.action = 'insert'
598 ";
599 CRM_Core_DAO::executeQuery($sql);
600
601 //update of existing records
602 $sql = "
603 UPDATE civicrm_financial_trxn ft
604 INNER JOIN {$tempTableName2} tempU
605 ON (tempU.action != 'insert' AND ft.id = tempU.action)
606 SET ft.from_financial_account_id = NULL,
607 ft.to_financial_account_id = tempU.to_financial_account_id,
608 ft.status_id = tempU.contribution_status_id,
609 ft.payment_instrument_id = tempU.payment_instrument_id,
610 ft.check_number = tempU.check_number,
611 ft.contribution_id = tempU.contribution_id;";
612 CRM_Core_DAO::executeQuery($sql);
613
614 //insert the -ve transaction rows for cancelled contributions
615 $sql = "
616 INSERT INTO civicrm_financial_trxn
617 (contribution_id, payment_instrument_id, currency, total_amount, net_amount, fee_amount, trxn_id, status_id,
618 check_number, to_financial_account_id, from_financial_account_id, trxn_date)
619 SELECT ft.contribution_id, ft.payment_instrument_id, ft.currency, -ft.total_amount, ft.net_amount, ft.fee_amount, ft.trxn_id,
620 ft.status_id, ft.check_number, ft.to_financial_account_id, ft.from_financial_account_id, ft.trxn_date
621 FROM civicrm_financial_trxn ft
622 WHERE ft.status_id = {$cancelledStatus};";
623 CRM_Core_DAO::executeQuery($sql);
624
625 //inserting entity financial trxn entries if its not present in entity_financial_trxn for completed and pending contribution statuses
626 //this also handles +ve and -ve both transaction entries for a cancelled contribution
627 $sql = "
628 INSERT INTO civicrm_entity_financial_trxn (entity_table, entity_id, financial_trxn_id, amount)
629 SELECT 'civicrm_contribution', ft.contribution_id, ft.id, ft.total_amount as amount
630 FROM civicrm_financial_trxn ft
631 WHERE contribution_id IS NOT NULL AND
632 ft.id NOT IN (SELECT financial_trxn_id
633 FROM civicrm_entity_financial_trxn
634 WHERE entity_table = 'civicrm_contribution'
635 AND entity_id = ft.contribution_id)";
636 CRM_Core_DAO::executeQuery($sql);
637 //end of adding financial_trxn records and entity_financial_trxn records related to contribution
638
639 //update all linked line_item rows
640 // set line_item.financial_type_id = contribution.financial_type_id if contribution page id is null and not participant line item
641 // set line_item.financial_type_id = price_field_value.financial_type_id if contribution page id is set and not participant line item
642 // set line_item.financial_type_id = event.financial_type_id if its participant line item and line_item.price_field_value_id is null
643 // 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
644 $updateLineItemSql = "
645 UPDATE civicrm_line_item li
646 LEFT JOIN civicrm_contribution con
647 ON (li.entity_id = con.id AND li.entity_table = 'civicrm_contribution')
648 LEFT JOIN civicrm_price_field_value cpfv
649 ON li.price_field_value_id = cpfv.id
650 LEFT JOIN civicrm_participant cp
651 ON (li.entity_id = cp.id AND li.entity_table = 'civicrm_participant')
652 LEFT JOIN civicrm_event ce
653 ON ce.id = cp.event_id
654 SET li.financial_type_id = CASE
655 WHEN (con.contribution_page_id IS NULL || li.price_field_value_id IS NULL) AND cp.id IS NULL THEN
656 con.financial_type_id
657 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
658 cpfv.financial_type_id
659 WHEN cp.id IS NOT NULL AND li.price_field_value_id IS NULL THEN
660 ce.financial_type_id
661 END";
662 CRM_Core_DAO::executeQuery($updateLineItemSql, $queryParams);
663
664 //add the financial_item entries
665 //add a temp column so that inserting entity_financial_trxn entries gets easy
666 $sql = "ALTER TABLE civicrm_financial_item ADD COLUMN f_trxn_id INT DEFAULT NULL";
667 CRM_Core_DAO::executeQuery($sql);
668
669 //add financial_item entries for contribution completed / pending pay later / cancelled
670 $contributionlineItemSql = "
671 INSERT INTO civicrm_financial_item
672 (transaction_date, contact_id, amount, currency, entity_table, entity_id, description, status_id, financial_account_id, f_trxn_id)
673
674 SELECT REPLACE(REPLACE(REPLACE(ft.trxn_date, '-', ''), ':', ''), ' ', ''), con.contact_id,
675 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',
676 li.id as line_item_id, li.label as line_item_label,
677 IF(con.contribution_status_id = {$pendingStatus}, {$unpaidStatus}, {$paidStatus}) as status_id, efa.financial_account_id as financial_account_id,
678 ft.id as f_trxn_id
679 FROM civicrm_line_item li
680 INNER JOIN civicrm_contribution con
681 ON (li.entity_id = con.id AND li.entity_table = 'civicrm_contribution')
682 INNER JOIN civicrm_financial_trxn ft
683 ON (con.id = ft.contribution_id)
684 LEFT JOIN civicrm_entity_financial_account efa
685 ON (li.financial_type_id = efa.entity_id AND efa.entity_table = 'civicrm_financial_type'
686 AND efa.account_relationship = {$incomeAccountIs})
687 WHERE con.contribution_status_id IN (%1, %3) OR (con.is_pay_later = 1 AND con.contribution_status_id = %2)";
688 CRM_Core_DAO::executeQuery($contributionlineItemSql, $queryParams);
689
690 //add financial_item entries for event
691 $participantLineItemSql = "
692 INSERT INTO civicrm_financial_item
693 (transaction_date, contact_id, amount, currency, entity_table, entity_id, description, status_id, financial_account_id, f_trxn_id)
694
695 SELECT REPLACE(REPLACE(REPLACE(ft.trxn_date, '-', ''), ':', ''), ' ', ''), con.contact_id,
696 IF(ft.total_amount < 0 AND con.contribution_status_id = %3, -li.line_total, li.line_total) as line_total,
697 con.currency, 'civicrm_line_item', li.id as line_item_id, li.label as line_item_label,
698 IF(con.contribution_status_id = {$pendingStatus}, {$unpaidStatus}, {$paidStatus}) as status_id,
699 efa.financial_account_id as financial_account_id, ft.id as f_trxn_id
700 FROM civicrm_line_item li
701 INNER JOIN civicrm_participant par
702 ON (li.entity_id = par.id AND li.entity_table = 'civicrm_participant')
703 INNER JOIN civicrm_participant_payment pp
704 ON (pp.participant_id = par.id)
705 INNER JOIN civicrm_contribution con
706 ON (pp.contribution_id = con.id)
707 INNER JOIN civicrm_financial_trxn ft
708 ON (con.id = ft.contribution_id)
709 LEFT JOIN civicrm_entity_financial_account efa
710 ON (li.financial_type_id = efa.entity_id AND
711 efa.entity_table = 'civicrm_financial_type' AND efa.account_relationship = {$incomeAccountIs})
712 WHERE con.contribution_status_id IN (%1, %3) OR (con.is_pay_later = 1 AND con.contribution_status_id = %2)";
713 CRM_Core_DAO::executeQuery($participantLineItemSql, $queryParams);
714
715 //fee handling for contributions
716 //insert fee entries in financial_trxn for contributions
717 $sql = "ALTER TABLE civicrm_financial_trxn ADD COLUMN is_fee TINYINT DEFAULT NULL";
718 CRM_Core_DAO::executeQuery($sql);
719
720 $sql = "
721 INSERT INTO civicrm_financial_trxn
722 (contribution_id, payment_instrument_id, currency, total_amount, net_amount, fee_amount, trxn_id, status_id, check_number,
723 to_financial_account_id, from_financial_account_id, trxn_date, payment_processor_id, is_fee)
724
725 SELECT con.id, ft.payment_instrument_id, ft.currency, ft.fee_amount, NULL, NULL, ft.trxn_id, %1 as status_id,
726 ft.check_number, efaFT.financial_account_id as to_financial_account_id, CASE
727 WHEN efaPP.financial_account_id IS NOT NULL THEN
728 efaPP.financial_account_id
729 WHEN tpi.financial_account_id IS NOT NULL THEN
730 tpi.financial_account_id
731 ELSE
732 {$financialAccountId}
733 END as from_financial_account_id, ft.trxn_date, ft.payment_processor_id, 1 as is_fee
734 FROM civicrm_contribution con
735 INNER JOIN civicrm_financial_trxn ft
736 ON (ft.contribution_id = con.id)
737 LEFT JOIN civicrm_entity_financial_account efaFT
738 ON (con.financial_type_id = efaFT.entity_id AND efaFT.entity_table = 'civicrm_financial_type'
739 AND efaFT.account_relationship = {$expenseAccountIs})
740 LEFT JOIN civicrm_entity_financial_account efaPP
741 ON (ft.payment_processor_id = efaPP.entity_id AND efaPP.entity_table = 'civicrm_payment_processor'
742 AND efaPP.account_relationship = {$assetAccountIs})
743 LEFT JOIN {$tempTableName1} tpi
744 ON ft.payment_instrument_id = tpi.instrument_id
745 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))
746 GROUP BY con.id";
747 CRM_Core_DAO::executeQuery($sql, $queryParams);
748
749 //link financial_trxn to contribution
750 $sql = "
751 INSERT INTO civicrm_entity_financial_trxn
752 (entity_table, entity_id, financial_trxn_id, amount)
753 SELECT 'civicrm_contribution', ft.contribution_id, ft.id, ft.total_amount
754 FROM civicrm_financial_trxn ft
755 WHERE ft.is_fee = 1";
756 CRM_Core_DAO::executeQuery($sql);
757
758 //add fee related entries to financial item table
759 $domainId = CRM_Core_Config::domainID();
760 $domainContactId = CRM_Core_DAO::getFieldValue('CRM_Core_DAO_Domain', $domainId, 'contact_id');
761 $sql = "
762 INSERT INTO civicrm_financial_item
763 (transaction_date, contact_id, amount, currency, entity_table, entity_id, description, status_id, financial_account_id, f_trxn_id)
764 SELECT ft.trxn_date, {$domainContactId} as contact_id, ft.total_amount, ft.currency, 'civicrm_financial_trxn', ft.id,
765 'Fee', {$paidStatus} as status_id, ft.to_financial_account_id as financial_account_id, ft.id as f_trxn_id
766 FROM civicrm_financial_trxn ft
767 WHERE ft.is_fee = 1;";
768 CRM_Core_DAO::executeQuery($sql);
769
770 //add entries to entity_financial_trxn table
771 $sql = "
772 INSERT INTO civicrm_entity_financial_trxn (entity_table, entity_id, financial_trxn_id, amount)
773 SELECT 'civicrm_financial_item' as entity_table, fi.id as entity_id, fi.f_trxn_id as financial_trxn_id, fi.amount
774 FROM civicrm_financial_item fi";
775 CRM_Core_DAO::executeQuery($sql);
776
777 //drop the temparory columns
778 $sql = "ALTER TABLE civicrm_financial_trxn
779 DROP COLUMN contribution_id,
780 DROP COLUMN is_fee;";
781 CRM_Core_DAO::executeQuery($sql);
782
783 $sql = "ALTER TABLE civicrm_financial_item DROP f_trxn_id";
784 CRM_Core_DAO::executeQuery($sql);
785
786 return TRUE;
787 }
788
789 function createDomainContacts() {
790 $domainParams = $context = array();
791 $query = "
792 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',
793 ADD CONSTRAINT FK_civicrm_domain_contact_id FOREIGN KEY (contact_id) REFERENCES civicrm_contact(id);";
794 CRM_Core_DAO::executeQuery($query, CRM_Core_DAO::$_nullArray, TRUE, NULL, FALSE, FALSE);
795
796 $query = '
797 SELECT cd.id, cd.name, ce.email FROM civicrm_domain cd
798 LEFT JOIN civicrm_loc_block clb ON clb.id = cd. loc_block_id
799 LEFT JOIN civicrm_email ce ON ce.id = clb.email_id ;
800 ' ;
801 $dao = CRM_Core_DAO::executeQuery($query);
802 while($dao->fetch()) {
803 $query = "
804 SELECT cc.id FROM civicrm_contact cc
805 LEFT JOIN civicrm_email ce ON ce.contact_id = cc.id
806 WHERE cc.contact_type = 'Organization' AND cc.organization_name = %1
807 ";
808 $params = array(1 => array($dao->name, 'String'));
809 if ($dao->email) {
810 $query .= " AND ce.email = %2 ";
811 $params[2] = array($dao->email, 'String');
812 }
813 $contactID = CRM_Core_DAO::singleValueQuery($query, $params);
814 $context[1] = $dao->name;
815 if (empty($contactID)) {
816 $params = array(
817 'sort_name' => $dao->name,
818 'display_name' => $dao->name,
819 'legal_name' => $dao->name,
820 'organization_name' => $dao->name,
821 'contact_type' => 'Organization'
822 );
823 $contact = CRM_Contact_BAO_Contact::add($params);
824 $contactID = $contact->id;
825 $context[0] = 'added';
826 }
827 else {
828 $context[0] = 'merged';
829 }
830 $domainParams['contact_id'] = $contactID;
831 CRM_Core_BAO_Domain::edit($domainParams, $dao->id);
832 }
833 return $context;
834 }
835
836 function task_4_3_alpha1_checkDBConstraints() {
837 //checking whether the foreign key exists before dropping it CRM-11260
838 $config = CRM_Core_Config::singleton();
839 $dbUf = DB::parseDSN($config->dsn);
840 $tables = array(
841 'autorenewal_msg_id' => array('tableName' => 'civicrm_membership_type', 'fkey' => 'FK_civicrm_membership_autorenewal_msg_id'),
842 'to_account_id' => array('tableName' => 'civicrm_financial_trxn', 'constraintName' => 'civicrm_financial_trxn_ibfk_2'),
843 'from_account_id' => array('tableName' => 'civicrm_financial_trxn', 'constraintName' => 'civicrm_financial_trxn_ibfk_1'),
844 'contribution_type_id' => array('tableName' => 'civicrm_contribution_recur', 'fkey' => 'FK_civicrm_contribution_recur_contribution_type_id'),
845 );
846 $query = "
847 SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
848 WHERE table_name = 'civicrm_contribution_recur'
849 AND constraint_name = 'FK_civicrm_contribution_recur_contribution_type_id'
850 AND TABLE_SCHEMA = %1
851 ";
852 $params = array(1 => array($dbUf['database'], 'String'));
853 $dao = CRM_Core_DAO::executeQuery($query, $params, TRUE, NULL, FALSE, FALSE);
854 foreach($tables as $columnName => $value){
855 if ($value['tableName'] == 'civicrm_membership_type' || $value['tableName'] == 'civicrm_contribution_recur') {
856 $foreignKeyExists = CRM_Core_DAO::checkConstraintExists($value['tableName'], $value['fkey']);
857 $fKey = $value['fkey'];
858 } else {
859 $foreignKeyExists = CRM_Core_DAO::checkFKConstraintInFormat($value['tableName'], $columnName);
860 $fKey = "`FK_{$value['tableName']}_{$columnName}`";
861 }
862 if ($foreignKeyExists || $value['tableName'] == 'civicrm_financial_trxn') {
863 if ($value['tableName'] != 'civicrm_contribution_recur' || ($value['tableName'] == 'civicrm_contribution_recur' && $dao->N)) {
864 $constraintName = $foreignKeyExists ? $fKey : $value['constraintName'];
865 $query = "ALTER TABLE {$value['tableName']} DROP FOREIGN KEY {$constraintName}";
866 CRM_Core_DAO::executeQuery($query, $params, TRUE, NULL, FALSE, FALSE);
867 }
868 $query = "ALTER TABLE {$value['tableName']} DROP INDEX {$fKey}";
869 CRM_Core_DAO::executeQuery($query, $params, TRUE, NULL, FALSE, FALSE);
870 }
871 }
872 // check if column contact_id is present or not in civicrm_financial_account
873 $fieldExists = CRM_Core_DAO::checkFieldExists('civicrm_financial_account', 'contact_id', FALSE);
874 if (!$fieldExists) {
875 $query = "
876 ALTER TABLE civicrm_financial_account
877 ADD contact_id int(10) unsigned DEFAULT NULL COMMENT 'Version identifier of financial_type' AFTER name,
878 ADD CONSTRAINT FK_civicrm_financial_account_contact_id FOREIGN KEY (contact_id) REFERENCES civicrm_contact(id);
879 ";
880 CRM_Core_DAO::executeQuery($query, $params, TRUE, NULL, FALSE, FALSE);
881 }
882 }
883
884 /**
885 * Read creation and modification times from civicrm_log; add
886 * them to civicrm_contact.
887 */
888 function convertTimestamps(CRM_Queue_TaskContext $ctx, $startId, $endId) {
889 $sql = "
890 SELECT entity_id, min(modified_date) AS created, max(modified_date) AS modified
891 FROM civicrm_log
892 WHERE entity_table = 'civicrm_contact'
893 AND entity_id BETWEEN %1 AND %2
894 GROUP BY entity_id
895 ";
896 $params = array(
897 1 => array($startId, 'Integer'),
898 2 => array($endId, 'Integer'),
899 );
900 $dao = CRM_Core_DAO::executeQuery($sql, $params);
901 while ($dao->fetch()) {
902 // FIXME civicrm_log.modified_date is DATETIME; civicrm_contact.modified_date is TIMESTAMP
903 CRM_Core_DAO::executeQuery(
904 'UPDATE civicrm_contact SET created_date = FROM_UNIXTIME(UNIX_TIMESTAMP(%1)), modified_date = FROM_UNIXTIME(UNIX_TIMESTAMP(%2)) WHERE id = %3',
905 array(
906 1 => array($dao->created, 'String'),
907 2 => array($dao->modified, 'String'),
908 3 => array($dao->entity_id, 'Integer'),
909 )
910 );
911 }
912
913 return TRUE;
914 }
915
916 /**
917 * change index and add missing constraints for civicrm_contribution_recur
918 */
919 function addMissingConstraints(CRM_Queue_TaskContext $ctx) {
920 $query = "SHOW KEYS FROM `civicrm_contribution_recur` WHERE key_name = 'UI_contrib_payment_instrument_id'";
921 $dao = CRM_Core_DAO::executeQuery($query);
922 if ($dao->N) {
923 CRM_Core_DAO::executeQuery('ALTER TABLE civicrm_contribution_recur DROP INDEX UI_contrib_payment_instrument_id');
924 CRM_Core_DAO::executeQuery('ALTER TABLE civicrm_contribution_recur ADD INDEX UI_contribution_recur_payment_instrument_id (payment_instrument_id)');
925 }
926 $constraintArray = array(
927 'contact_id' => " ADD CONSTRAINT `FK_civicrm_contribution_recur_contact_id` FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE ",
928 'payment_processor_id' => " ADD CONSTRAINT `FK_civicrm_contribution_recur_payment_processor_id` FOREIGN KEY (`payment_processor_id`) REFERENCES `civicrm_payment_processor` (`id`) ON DELETE SET NULL ",
929 'financial_type_id' => " ADD CONSTRAINT `FK_civicrm_contribution_recur_financial_type_id` FOREIGN KEY (`financial_type_id`) REFERENCES `civicrm_financial_type` (`id`) ON DELETE SET NULL ",
930 'campaign_id' => " ADD CONSTRAINT `FK_civicrm_contribution_recur_campaign_id` FOREIGN KEY (`campaign_id`) REFERENCES `civicrm_campaign` (`id`) ON DELETE SET NULL ",
931 );
932 $constraint = array();
933 foreach ($constraintArray as $constraintKey => $value) {
934 $foreignKeyExists = CRM_Core_DAO::checkFKConstraintInFormat('civicrm_contribution_recur', $constraintKey);
935 if (!$foreignKeyExists) {
936 $constraint[] = $value;
937 }
938 }
939 if (!empty($constraint)) {
940 $query = "ALTER TABLE civicrm_contribution_recur " . implode(' , ', $constraint);
941 CRM_Core_DAO::executeQuery($query);
942 }
943 return TRUE;
944 }
945
946 /**
947 * Update financial_account_id for bad data in financial_trxn table
948 * CRM-12844
949 *
950 */
951 function updateFinancialTrxnData(CRM_Queue_TaskContext $ctx) {
952 $upgrade = new CRM_Upgrade_Form();
953 $sql = "SELECT cc.id contribution_id, cc.contribution_recur_id, cft.payment_processor_id,
954 cft.id financial_trxn_id, cfi.entity_table, cft.from_financial_account_id, cft.to_financial_account_id
955
956 FROM `civicrm_contribution` cc
957 LEFT JOIN civicrm_entity_financial_trxn ceft ON ceft.entity_id = cc.id
958 LEFT JOIN civicrm_financial_trxn cft ON cft.id = ceft.financial_trxn_id
959 LEFT JOIN civicrm_entity_financial_trxn ceft1 ON ceft1.financial_trxn_id = ceft.financial_trxn_id
960 LEFT JOIN civicrm_financial_item cfi ON cfi.id = ceft1.entity_id
961 WHERE ceft.entity_table = 'civicrm_contribution' AND cc.contribution_recur_id IS NOT NULL
962 AND ceft1.entity_table = 'civicrm_financial_item' AND cft.id IS NOT NULL AND cft.payment_instrument_id = %1
963
964 ORDER BY cft.id ";
965 $paymentInstrument = CRM_Contribute_PseudoConstant::paymentInstrument('name');
966 $param = array(1 => array(array_search('Credit Card', $paymentInstrument), 'Integer'));
967 $dao = CRM_Core_DAO::executeQuery($sql, $param);
968 $financialTrxn = array();
969 $subsequentPayments = array();
970 while ($dao->fetch()) {
971 if (!array_key_exists($dao->contribution_recur_id, $financialTrxn)) {
972 $financialTrxn[$dao->contribution_recur_id] = array(
973 'from_financial_account_id' => $dao->to_financial_account_id,
974 'payment_processor_id' => $dao->payment_processor_id,
975 $dao->contribution_id => 1,
976 );
977 if (!is_null($dao->from_financial_account_id)) {
978 $sql = 'UPDATE civicrm_financial_trxn SET from_financial_account_id = NULL WHERE id = %1';
979 $params = array(1 => array($dao->financial_trxn_id, 'Integer'));
980 CRM_Core_DAO::executeQuery($sql, $params);
981 }
982 }
983 elseif (!array_key_exists($dao->contribution_id, $financialTrxn[$dao->contribution_recur_id])) {
984 if (($dao->entity_table == 'civicrm_line_item' && $dao->to_financial_account_id == $financialTrxn[$dao->contribution_recur_id]['from_financial_account_id'])
985 || ($dao->entity_table == 'civicrm_financial_trxn' && $dao->from_financial_account_id == $financialTrxn[$dao->contribution_recur_id]['from_financial_account_id'])) {
986 continue;
987 }
988 $subsequentPayments[$dao->contribution_recur_id][$dao->entity_table][] = $dao->financial_trxn_id;
989 }
990 }
991 foreach ($subsequentPayments as $key => $value) {
992 foreach ($value as $table => $val) {
993 if ($table == 'civicrm_financial_trxn') {
994 $field = 'from_financial_account_id';
995 }
996 else {
997 $field = 'to_financial_account_id';
998 }
999 $sql = "UPDATE civicrm_financial_trxn SET $field = " . $financialTrxn[$dao->contribution_recur_id]['from_financial_account_id'] . ',
1000 payment_processor_id = ' . $financialTrxn[$dao->contribution_recur_id]['payment_processor_id'] . ' WHERE
1001 id IN (' . implode(',', $val) . ')';
1002 CRM_Core_DAO::executeQuery($sql);
1003 }
1004 }
1005 return TRUE;
1006 }
1007
1008 /**
1009 * Update financial_account_id for bad data in financial_trxn table
1010 * CRM-12844
1011 *
1012 */
1013 function updateLineItemData(CRM_Queue_TaskContext $ctx) {
1014 $sql = "SELECT cc.id contribution_id, cc.contribution_recur_id,
1015 cc.financial_type_id contribution_financial_type,
1016 cli.financial_type_id line_financial_type_id,
1017 cli.price_field_id, cli.price_field_value_id, cli.label, cli.id line_item_id,
1018 cfi.financial_account_id
1019 FROM `civicrm_line_item` cli
1020 LEFT JOIN civicrm_contribution cc ON cc.id = cli.entity_id
1021 LEFT JOIN civicrm_financial_item cfi ON cfi.entity_id = cli.id
1022 LEFT JOIN civicrm_price_field cpf ON cpf.id = cli.price_field_id
1023 LEFT JOIN civicrm_price_set cps ON cps.id = cpf.price_set_id
1024 LEFT JOIN civicrm_price_field_value cpfv ON cpfv.id = cli.price_field_value_id
1025 WHERE cfi.entity_table = 'civicrm_line_item'
1026 AND cli.entity_table = 'civicrm_contribution'
1027 AND cps.is_quick_config = 1 AND cc.contribution_recur_id IS NOT NULL
1028 ORDER BY cli.id";
1029 $dao = CRM_Core_DAO::executeQuery($sql);
1030 $financialTrxn = $subsequentPayments = array();
1031 while ($dao->fetch()) {
1032 if (!array_key_exists($dao->contribution_recur_id, $financialTrxn)) {
1033 $financialTrxn[$dao->contribution_recur_id] = array(
1034 'price_field_id' => $dao->price_field_id,
1035 'price_field_value_id' => $dao->price_field_value_id,
1036 'label' => strval($dao->label),
1037 'financial_account_id' => $dao->financial_account_id,
1038 $dao->contribution_id => 1,
1039 );
1040 }
1041 else {
1042 if ($dao->price_field_value_id == $financialTrxn[$dao->contribution_recur_id]['price_field_value_id']) {
1043 continue;
1044 }
1045 $subsequentPayments[$dao->contribution_recur_id][] = $dao->line_item_id;
1046 }
1047 }
1048 foreach ($subsequentPayments as $key => $value) {
1049 $sql = "UPDATE civicrm_line_item cli
1050 LEFT JOIN civicrm_financial_item cfi ON cli.id = cfi.entity_id
1051 SET
1052 cli.label = %1,
1053 cli.price_field_id = %2,
1054 cli.price_field_value_id = %3,
1055 cfi.financial_account_id = %4,
1056 cfi.description = %5,
1057 cli.financial_type_id = %6
1058 WHERE cfi.entity_table = 'civicrm_line_item'
1059 AND cli.entity_table = 'civicrm_contribution' AND cli.id IN (" . implode(',', $value). ');';
1060 $params = array(
1061 1 => array($financialTrxn[$key]['label'], 'String'),
1062 2 => array($financialTrxn[$key]['price_field_id'], 'Integer'),
1063 3 => array($financialTrxn[$key]['price_field_value_id'], 'Integer'),
1064 4 => array($financialTrxn[$key]['financial_account_id'], 'Integer'),
1065 5 => array($financialTrxn[$key]['label'], 'String'),
1066 6 => array($dao->contribution_financial_type, 'Integer'),
1067 );
1068 CRM_Core_DAO::executeQuery($sql, $params);
1069 }
1070 return TRUE;
1071 }
1072
1073 /**
1074 * replace contribution_type to financial_type in table
1075 * civicrm_saved_search and Structure civicrm_report_instance
1076 */
1077 function replaceContributionTypeId(CRM_Queue_TaskContext $ctx, $query, $table) {
1078 $dao = CRM_Core_DAO::executeQuery($query);
1079 while ($dao->fetch()) {
1080 $formValues = unserialize($dao->form_values);
1081 foreach (array('contribution_type_id_op', 'contribution_type_id_value', 'contribution_type_id') as $value) {
1082 if (array_key_exists($value, $formValues)) {
1083 $key = preg_replace('/contribution/', 'financial', $value);
1084 $formValues[$key] = $formValues[$value];
1085 unset($formValues[$value]);
1086 }
1087 }
1088 if ($table != 'savedSearch') {
1089 foreach (array('fields', 'group_bys') as $value) {
1090 if (array_key_exists($value, $formValues)) {
1091 if (array_key_exists('contribution_type_id', $formValues[$value])) {
1092 $formValues[$value]['financial_type_id'] = $formValues[$value]['contribution_type_id'];
1093 unset($formValues[$value]['contribution_type_id']);
1094 }
1095 else if (array_key_exists('contribution_type', $formValues[$value])) {
1096 $formValues[$value]['financial_type'] = $formValues[$value]['contribution_type'];
1097 unset($formValues[$value]['contribution_type']);
1098 }
1099 }
1100 }
1101 if (array_key_exists('order_bys', $formValues)) {
1102 foreach ($formValues['order_bys'] as $key => $values) {
1103 if (preg_grep('/contribution_type/', $values)) {
1104 $formValues['order_bys'][$key]['column'] = preg_replace('/contribution_type/', 'financial_type', $values['column']);
1105 }
1106 }
1107 }
1108 }
1109
1110 if ($table == 'savedSearch') {
1111 $saveDao = new CRM_Contact_DAO_SavedSearch();
1112 }
1113 else {
1114 $saveDao = new CRM_Report_DAO_ReportInstance();
1115 }
1116 $saveDao->id = $dao->id;
1117
1118 if ($table == 'savedSearch') {
1119 if (array_key_exists('mapper', $formValues)) {
1120 foreach ($formValues['mapper'] as $key => $values) {
1121 foreach ($values as $k => $v) {
1122 if (preg_grep('/contribution_/', $v)) {
1123 $formValues['mapper'][$key][$k] = preg_replace('/contribution_type/', 'financial_type', $v);
1124 }
1125 }
1126 }
1127 }
1128 foreach (array('select_tables', 'where_tables') as $value) {
1129 if (preg_match('/contribution_type/', $dao->$value)) {
1130 $tempValue = unserialize($dao->$value);
1131 if (array_key_exists('civicrm_contribution_type', $tempValue)) {
1132 $tempValue['civicrm_financial_type'] = $tempValue['civicrm_contribution_type'];
1133 unset($tempValue['civicrm_contribution_type']);
1134 }
1135 $saveDao->$value = serialize($tempValue);
1136 }
1137 }
1138 if (preg_match('/contribution_type/', $dao->where_clause)) {
1139 $saveDao->where_clause = preg_replace('/contribution_type/', 'financial_type', $dao->where_clause);
1140 }
1141 }
1142 $saveDao->form_values = serialize($formValues);
1143
1144 $saveDao->save();
1145 }
1146 return TRUE;
1147 }
1148
1149 /**
1150 * Add ON DELETE options for constraint if not present
1151 * CRM-13088 && CRM-12156
1152 *
1153 * @return bool TRUE for success
1154 */
1155 function task_4_3_x_checkConstraints(CRM_Queue_TaskContext $ctx) {
1156 CRM_Core_DAO::executeQuery('ALTER TABLE `civicrm_financial_account` CHANGE `contact_id` `contact_id` INT( 10 ) UNSIGNED NULL DEFAULT NULL');
1157 $config = CRM_Core_Config::singleton();
1158 $dbname = DB::parseDSN($config->dsn);
1159 $constraintArray = array(
1160 "'FK_civicrm_financial_account_contact_id'",
1161 "'FK_civicrm_financial_item_contact_id'",
1162 "'FK_civicrm_contribution_recur_financial_type_id'",
1163 "'FK_civicrm_line_item_financial_type_id'",
1164 "'FK_civicrm_product_financial_type_id'",
1165 "'FK_civicrm_premiums_product_financial_type_id'",
1166 "'FK_civicrm_price_field_value_financial_type_id'",
1167 "'FK_civicrm_contribution_product_financial_type_id'",
1168 "'FK_civicrm_price_set_financial_type_id'",
1169 "'FK_civicrm_grant_financial_type_id'",
1170 );
1171
1172 $sql = "SELECT DELETE_RULE, TABLE_NAME, CONSTRAINT_NAME
1173 FROM information_schema.REFERENTIAL_CONSTRAINTS
1174 WHERE CONSTRAINT_NAME IN (" . implode(',', $constraintArray) . ")
1175 AND CONSTRAINT_SCHEMA = %1";
1176 $params = array(1 => array($dbname['database'], 'String'));
1177 $onDelete = CRM_Core_DAO::executeQuery($sql, $params, TRUE, FALSE);
1178 while ($onDelete->fetch()) {
1179 if (($onDelete->TABLE_NAME != 'civicrm_financial_item' && $onDelete->DELETE_RULE != 'SET NULL') ||
1180 ($onDelete->TABLE_NAME == 'civicrm_financial_item' && $onDelete->DELETE_RULE != 'CASCADE')) {
1181 $tableName = 'civicrm_financial_type';
1182 $onDeleteOption = ' SET NULL ';
1183 $columnName = 'financial_type_id';
1184 if (preg_match('/contact_id/', $onDelete->CONSTRAINT_NAME)) {
1185 $tableName = 'civicrm_contact';
1186 $columnName = 'contact_id';
1187 if ($onDelete->TABLE_NAME == 'civicrm_financial_item') {
1188 $onDeleteOption = 'CASCADE';
1189 }
1190 }
1191 }
1192 else {
1193 continue;
1194 }
1195 $query = "ALTER TABLE {$onDelete->TABLE_NAME}
1196 DROP FOREIGN KEY {$onDelete->CONSTRAINT_NAME},
1197 DROP INDEX {$onDelete->CONSTRAINT_NAME};";
1198 CRM_Core_DAO::executeQuery($query, array(), TRUE, NULL, FALSE, FALSE);
1199 $query = " ALTER TABLE {$onDelete->TABLE_NAME}
1200 ADD CONSTRAINT {$onDelete->CONSTRAINT_NAME} FOREIGN KEY (`" . $columnName . "`) REFERENCES {$tableName} (`id`) ON DELETE {$onDeleteOption};
1201 ";
1202 CRM_Core_DAO::executeQuery($query, array(), TRUE, NULL, FALSE, FALSE);
1203 }
1204 return TRUE;
1205 }
1206
1207 /**
1208 * Check/Add INDEX CRM-12141
1209 *
1210 * @return bool TRUE for success
1211 */
1212 function task_4_3_x_checkIndexes(CRM_Queue_TaskContext $ctx) {
1213 $query = "
1214 SHOW KEYS
1215 FROM civicrm_entity_financial_trxn
1216 WHERE key_name IN ('UI_entity_financial_trxn_entity_table', 'UI_entity_financial_trxn_entity_id')
1217 ";
1218 $dao = CRM_Core_DAO::executeQuery($query);
1219 if (!$dao->N) {
1220 $query = "
1221 ALTER TABLE civicrm_entity_financial_trxn
1222 ADD INDEX UI_entity_financial_trxn_entity_table (entity_table),
1223 ADD INDEX UI_entity_financial_trxn_entity_id (entity_id);
1224 ";
1225 CRM_Core_DAO::executeQuery($query);
1226 }
1227 return TRUE;
1228 }
1229
1230 /**
1231 * Update phones CRM-11292
1232 *
1233 * @return bool TRUE for success
1234 */
1235 static function phoneNumeric(CRM_Queue_TaskContext $ctx) {
1236 CRM_Core_DAO::executeQuery(CRM_Contact_BAO_Contact::DROP_STRIP_FUNCTION_43);
1237 CRM_Core_DAO::executeQuery(CRM_Contact_BAO_Contact::CREATE_STRIP_FUNCTION_43);
1238 CRM_Core_DAO::executeQuery("UPDATE civicrm_phone SET phone_numeric = civicrm_strip_non_numeric(phone)");
1239 return TRUE;
1240 }
1241
1242 /**
1243 * (Queue Task Callback)
1244 */
1245 static function task_4_3_x_runSql(CRM_Queue_TaskContext $ctx, $rev) {
1246 $upgrade = new CRM_Upgrade_Form();
1247 $upgrade->processSQL($rev);
1248
1249 return TRUE;
1250 }
1251
1252 /**
1253 * Syntatic sugar for adding a task which (a) is in this class and (b) has
1254 * a high priority.
1255 *
1256 * After passing the $funcName, you can also pass parameters that will go to
1257 * the function. Note that all params must be serializable.
1258 */
1259 protected function addTask($title, $funcName) {
1260 $queue = CRM_Queue_Service::singleton()->load(array(
1261 'type' => 'Sql',
1262 'name' => CRM_Upgrade_Form::QUEUE_NAME,
1263 ));
1264
1265 $args = func_get_args();
1266 $title = array_shift($args);
1267 $funcName = array_shift($args);
1268 $task = new CRM_Queue_Task(
1269 array(get_class($this), $funcName),
1270 $args,
1271 $title
1272 );
1273 $queue->createItem($task, array('weight' => -1));
1274 }
1275 }