CRM/Upgrade/Incremental/MessageTemplates.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | Copyright CiviCRM LLC. All rights reserved.                        |
   5  |                                                                    |
   6  | This work is published under the GNU AGPLv3 license with some      |
   7  | permitted exceptions and without any warranty. For full license    |
   8  | and copyright information, see https://civicrm.org/licensing       |
   9  +--------------------------------------------------------------------+
  10  */
  11
  12 /**
  13  *
  14  * @package CRM
  15  * @copyright CiviCRM LLC https://civicrm.org/licensing
  16  */
  17 class CRM_Upgrade_Incremental_MessageTemplates {
  18
  19   /**
  20    * Version we are upgrading to.
  21    *
  22    * @var string
  23    */
  24   protected $upgradeVersion;
  25
  26   /**
  27    * @return string
  28    */
  29   public function getUpgradeVersion() {
  30     return $this->upgradeVersion;
  31   }
  32
  33   /**
  34    * @param string $upgradeVersion
  35    */
  36   public function setUpgradeVersion($upgradeVersion) {
  37     $this->upgradeVersion = $upgradeVersion;
  38   }
  39
  40   /**
  41    * CRM_Upgrade_Incremental_MessageTemplates constructor.
  42    *
  43    * @param string $upgradeVersion
  44    */
  45   public function __construct($upgradeVersion) {
  46     $this->setUpgradeVersion($upgradeVersion);
  47   }
  48
  49   /**
  50    * Get any templates that have been updated.
  51    *
  52    * @return array
  53    */
  54   protected function getTemplateUpdates() {
  55     return [
  56       [
  57         'version' => '5.4.alpha1',
  58         'upgrade_descriptor' => ts('Use email greeting at top where available'),
  59         'templates' => [
  60           ['name' => 'membership_online_receipt', 'type' => 'text'],
  61           ['name' => 'membership_online_receipt', 'type' => 'html'],
  62           ['name' => 'contribution_online_receipt', 'type' => 'text'],
  63           ['name' => 'contribution_online_receipt', 'type' => 'html'],
  64           ['name' => 'event_online_receipt', 'type' => 'text'],
  65           ['name' => 'event_online_receipt', 'type' => 'html'],
  66           ['name' => 'event_online_receipt', 'type' => 'subject'],
  67         ],
  68       ],
  69       [
  70         'version' => '5.7.alpha1',
  71         'upgrade_descriptor' => ts('Fix invoice number (human readable) instead of id (reference)'),
  72         'label' => ts('Contributions - Invoice'),
  73         'templates' => [
  74           ['name' => 'contribution_invoice_receipt', 'type' => 'html'],
  75         ],
  76       ],
  77       [
  78         'version' => '5.10.alpha1',
  79         'upgrade_descriptor' => ts('Show recurring cancel/update URLs in receipt based on payment processor capabilities'),
  80         'label' => ts('Receipts - cancel/update subscription URLs'),
  81         'templates' => [
  82           ['name' => 'contribution_online_receipt', 'type' => 'text'],
  83           ['name' => 'contribution_online_receipt', 'type' => 'html'],
  84           ['name' => 'contribution_recurring_notify', 'type' => 'text'],
  85           ['name' => 'contribution_recurring_notify', 'type' => 'html'],
  86           ['name' => 'membership_online_receipt', 'type' => 'text'],
  87           ['name' => 'membership_online_receipt', 'type' => 'html'],
  88         ],
  89       ],
  90       [
  91         'version' => '5.12.alpha1',
  92         'upgrade_descriptor' => ts('Update payment notification to remove print text, use email greeting'),
  93         'label' => ts('Payment notification'),
  94         'templates' => [
  95           ['name' => 'payment_or_refund_notification', 'type' => 'text'],
  96           ['name' => 'payment_or_refund_notification', 'type' => 'html'],
  97         ],
  98       ],
  99       [
 100         'version' => '5.15.alpha1',
 101         'upgrade_descriptor' => ts('Use email greeting and fix capitalization'),
 102         'label' => ts('Pledge acknowledgement'),
 103         'templates' => [
 104           ['name' => 'pledge_acknowledge', 'type' => 'text'],
 105           ['name' => 'pledge_acknowledge', 'type' => 'html'],
 106         ],
 107       ],
 108       [
 109         'version' => '5.20.alpha1',
 110         'upgrade_descriptor' => ts('Fix missing Email greetings'),
 111         'templates' => [
 112           ['name' => 'contribution_dupalert', 'type' => 'subject'],
 113           ['name' => 'contribution_invoice_receipt', 'type' => 'subject'],
 114           ['name' => 'contribution_offline_receipt', 'type' => 'html'],
 115           ['name' => 'contribution_offline_receipt', 'type' => 'subject'],
 116           ['name' => 'contribution_offline_receipt', 'type' => 'text'],
 117           ['name' => 'contribution_online_receipt', 'type' => 'subject'],
 118           ['name' => 'contribution_online_receipt', 'type' => 'html'],
 119           ['name' => 'contribution_recurring_billing', 'type' => 'html'],
 120           ['name' => 'contribution_recurring_billing', 'type' => 'subject'],
 121           ['name' => 'contribution_recurring_billing', 'type' => 'text'],
 122           ['name' => 'contribution_recurring_cancelled', 'type' => 'html'],
 123           ['name' => 'contribution_recurring_cancelled', 'type' => 'subject'],
 124           ['name' => 'contribution_recurring_cancelled', 'type' => 'text'],
 125           ['name' => 'contribution_recurring_edit', 'type' => 'html'],
 126           ['name' => 'contribution_recurring_edit', 'type' => 'subject'],
 127           ['name' => 'contribution_recurring_edit', 'type' => 'text'],
 128           ['name' => 'contribution_recurring_notify', 'type' => 'html'],
 129           ['name' => 'contribution_recurring_notify', 'type' => 'subject'],
 130           ['name' => 'contribution_recurring_notify', 'type' => 'text'],
 131           ['name' => 'event_offline_receipt', 'type' => 'html'],
 132           ['name' => 'event_offline_receipt', 'type' => 'subject'],
 133           ['name' => 'event_offline_receipt', 'type' => 'text'],
 134           ['name' => 'event_online_receipt', 'type' => 'html'],
 135           ['name' => 'event_online_receipt', 'type' => 'subject'],
 136           ['name' => 'event_online_receipt', 'type' => 'text'],
 137           ['name' => 'event_registration_receipt', 'type' => 'html'],
 138           ['name' => 'event_registration_receipt', 'type' => 'subject'],
 139           ['name' => 'event_registration_receipt', 'type' => 'text'],
 140           ['name' => 'membership_autorenew_billing', 'type' => 'html'],
 141           ['name' => 'membership_autorenew_billing', 'type' => 'subject'],
 142           ['name' => 'membership_autorenew_billing', 'type' => 'text'],
 143           ['name' => 'membership_autorenew_cancelled', 'type' => 'html'],
 144           ['name' => 'membership_autorenew_cancelled', 'type' => 'subject'],
 145           ['name' => 'membership_autorenew_cancelled', 'type' => 'text'],
 146           ['name' => 'membership_offline_receipt', 'type' => 'html'],
 147           ['name' => 'membership_offline_receipt', 'type' => 'subject'],
 148           ['name' => 'membership_offline_receipt', 'type' => 'text'],
 149           ['name' => 'membership_online_receipt', 'type' => 'subject'],
 150           ['name' => 'membership_online_receipt', 'type' => 'html'],
 151           ['name' => 'participant_cancelled', 'type' => 'html'],
 152           ['name' => 'participant_cancelled', 'type' => 'subject'],
 153           ['name' => 'participant_cancelled', 'type' => 'text'],
 154           ['name' => 'participant_confirm', 'type' => 'html'],
 155           ['name' => 'participant_confirm', 'type' => 'subject'],
 156           ['name' => 'participant_confirm', 'type' => 'text'],
 157           ['name' => 'participant_expired', 'type' => 'html'],
 158           ['name' => 'participant_expired', 'type' => 'subject'],
 159           ['name' => 'participant_expired', 'type' => 'text'],
 160           ['name' => 'participant_transferred', 'type' => 'html'],
 161           ['name' => 'participant_transferred', 'type' => 'subject'],
 162           ['name' => 'participant_transferred', 'type' => 'text'],
 163           ['name' => 'payment_or_refund_notification', 'type' => 'html'],
 164           ['name' => 'payment_or_refund_notification', 'type' => 'subject'],
 165           ['name' => 'payment_or_refund_notification', 'type' => 'text'],
 166           ['name' => 'pcp_notify', 'type' => 'subject'],
 167           ['name' => 'pcp_owner_notify', 'type' => 'html'],
 168           ['name' => 'pcp_owner_notify', 'type' => 'subject'],
 169           ['name' => 'pcp_owner_notify', 'type' => 'text'],
 170           ['name' => 'pcp_status_change', 'type' => 'subject'],
 171           ['name' => 'pcp_status_change', 'type' => 'html'],
 172           ['name' => 'pcp_supporter_notify', 'type' => 'html'],
 173           ['name' => 'pcp_supporter_notify', 'type' => 'subject'],
 174           ['name' => 'pcp_supporter_notify', 'type' => 'text'],
 175           ['name' => 'petition_confirmation_needed', 'type' => 'html'],
 176           ['name' => 'petition_confirmation_needed', 'type' => 'subject'],
 177           ['name' => 'petition_confirmation_needed', 'type' => 'text'],
 178           ['name' => 'petition_sign', 'type' => 'html'],
 179           ['name' => 'petition_sign', 'type' => 'subject'],
 180           ['name' => 'petition_sign', 'type' => 'text'],
 181           ['name' => 'pledge_acknowledge', 'type' => 'subject'],
 182           ['name' => 'pledge_acknowledge', 'type' => 'html'],
 183           ['name' => 'pledge_acknowledge', 'type' => 'text'],
 184           ['name' => 'pledge_reminder', 'type' => 'html'],
 185           ['name' => 'pledge_reminder', 'type' => 'subject'],
 186           ['name' => 'pledge_reminder', 'type' => 'text'],
 187           ['name' => 'uf_notify', 'type' => 'subject'],
 188           ['name' => 'uf_notify', 'type' => 'html'],
 189           ['name' => 'case_activity', 'type' => 'html'],
 190           ['name' => 'contribution_dupalert', 'type' => 'html'],
 191           ['name' => 'friend', 'type' => 'html'],
 192           ['name' => 'test_preview', 'type' => 'html'],
 193         ],
 194       ],
 195       [
 196         'version' => '5.21.beta1',
 197         'upgrade_descriptor' => ts('Fix Membership Receipt'),
 198         'templates' => [
 199           ['name' => 'membership_online_receipt', 'type' => 'html'],
 200         ],
 201       ],
 202       [
 203         'version' => '5.23.alpha1',
 204         'upgrade_descriptor' => ts('Add Contributor Name to Offline Contribution receipts; fix bad event self-service URL'),
 205         'templates' => [
 206           ['name' => 'contribution_offline_receipt', 'type' => 'text'],
 207           ['name' => 'contribution_offline_receipt', 'type' => 'html'],
 208           ['name' => 'participant_confirm', 'type' => 'html'],
 209         ],
 210       ],
 211       [
 212         'version' => '5.24.alpha1',
 213         'upgrade_descriptor' => ts('Layout fixes for the Contribution templates'),
 214         'templates' => [
 215           ['name' => 'contribution_invoice_receipt', 'type' => 'html'],
 216         ],
 217       ],
 218       [
 219         'version' => '5.30.alpha1',
 220         'upgrade_descriptor' => ts('Support negative hours for cancellation/transfer'),
 221         'templates' => [
 222           ['name' => 'participant_confirm', 'type' => 'html'],
 223           ['name' => 'participant_confirm', 'type' => 'text'],
 224           ['name' => 'event_online_receipt', 'type' => 'html'],
 225           ['name' => 'event_online_receipt', 'type' => 'text'],
 226         ],
 227       ],
 228       [
 229         'version' => '5.30.beta1',
 230         'upgrade_descriptor' => ts('Ensure that amount paid is shown even when fully paid'),
 231         'templates' => [
 232           ['name' => 'contribution_invoice_receipt', 'type' => 'html'],
 233         ],
 234       ],
 235       [
 236         'version' => '5.38.alpha1',
 237         'upgrade_descriptor' => ts('Fix Petition Confirmation email having a blank space at the end of url'),
 238         'templates' => [
 239           ['name' => 'petition_confirmation_needed', 'type' => 'html'],
 240         ],
 241       ],
 242       [
 243         'version' => '5.38.alpha1',
 244         'upgrade_descriptor' => ts('Fix Pledge and PCP urls to go to the front end site rather than backend site'),
 245         'templates' => [
 246           ['name' => 'pcp_notify', 'type' => 'html'],
 247           ['name' => 'pcp_notify', 'type' => 'text'],
 248           ['name' => 'pledge_reminder', 'type' => 'html'],
 249           ['name' => 'pledge_reminder', 'type' => 'text'],
 250         ],
 251       ],
 252       [
 253         'version' => '5.43.alpha1',
 254         'upgrade_descriptor' => ts('Missed templates from earlier versions'),
 255         'templates' => [
 256           ['name' => 'contribution_online_receipt', 'type' => 'text'],
 257           ['name' => 'case_activity', 'type' => 'html'],
 258           ['name' => 'case_activity', 'type' => 'text'],
 259           ['name' => 'case_activity', 'type' => 'subject'],
 260         ],
 261       ],
 262       [
 263         'version' => '5.44.alpha1',
 264         'upgrade_descriptor' => ts('Use domain tokens instead of smarty values'),
 265         'label' => ts('Pledge acknowledgement'),
 266         'templates' => [
 267           ['name' => 'pledge_acknowledge', 'type' => 'text'],
 268           ['name' => 'pledge_acknowledge', 'type' => 'html'],
 269         ],
 270       ],
 271       [
 272         'version' => '5.44.alpha1',
 273         'upgrade_descriptor' => ts('Use domain and contact tokens instead of smarty values'),
 274         'label' => ts('Pledge reminder'),
 275         'templates' => [
 276           ['name' => 'pledge_reminder', 'type' => 'text'],
 277           ['name' => 'pledge_reminder', 'type' => 'html'],
 278         ],
 279       ],
 280       [
 281         'version' => '5.44.alpha1',
 282         'upgrade_descriptor' => ts('Use domain and contact tokens instead of smarty values'),
 283         'label' => ts('Participant Transferred'),
 284         'templates' => [
 285           ['name' => 'participant_transferred', 'type' => 'text'],
 286           ['name' => 'participant_transferred', 'type' => 'html'],
 287         ],
 288       ],
 289       [
 290         'version' => '5.44.alpha1',
 291         'upgrade_descriptor' => ts('Use domain and contact tokens instead of smarty values'),
 292         'label' => ts('Participant Cancelled'),
 293         'templates' => [
 294           ['name' => 'participant_cancelled', 'type' => 'text'],
 295           ['name' => 'participant_cancelled', 'type' => 'html'],
 296         ],
 297       ],
 298
 299       [
 300         'version' => '5.44.alpha1',
 301         'upgrade_descriptor' => ts('Use domain and contact tokens instead of smarty values'),
 302         'label' => ts('Participant Expired'),
 303         'templates' => [
 304           ['name' => 'participant_expired', 'type' => 'text'],
 305           ['name' => 'participant_expired', 'type' => 'html'],
 306         ],
 307       ],
 308       [
 309         'version' => '5.44.alpha1',
 310         'upgrade_descriptor' => ts('Use domain and contact tokens instead of smarty values'),
 311         'label' => ts('Participant Confirmed'),
 312         'templates' => [
 313           ['name' => 'participant_confirm', 'type' => 'text'],
 314           ['name' => 'participant_confirm', 'type' => 'html'],
 315         ],
 316       ],
 317       [
 318         'version' => '5.48.alpha1',
 319         'upgrade_descriptor' => ts('Replace {receipt_text_renewal} with {receipt_text}'),
 320         'templates' => [
 321           ['name' => 'membership_offline_receipt', 'type' => 'html'],
 322           ['name' => 'membership_offline_receipt', 'type' => 'text'],
 323         ],
 324       ],
 325       [
 326         'version' => '5.48.beta2',
 327         'upgrade_descriptor' => ts('Revert time zone for Event dates'),
 328         'templates' => [
 329           ['name' => 'event_online_receipt', 'type' => 'html'],
 330           ['name' => 'event_online_receipt', 'type' => 'text'],
 331           ['name' => 'event_offline_receipt', 'type' => 'html'],
 332           ['name' => 'event_offline_receipt', 'type' => 'text'],
 333           ['name' => 'participant_cancelled', 'type' => 'html'],
 334           ['name' => 'participant_cancelled', 'type' => 'text'],
 335           ['name' => 'participant_confirm', 'type' => 'html'],
 336           ['name' => 'participant_confirm', 'type' => 'text'],
 337           ['name' => 'participant_expired', 'type' => 'html'],
 338           ['name' => 'participant_expired', 'type' => 'text'],
 339           ['name' => 'participant_transferred', 'type' => 'html'],
 340           ['name' => 'participant_transferred', 'type' => 'text'],
 341           ['name' => 'payment_or_refund_notification', 'type' => 'html'],
 342           ['name' => 'payment_or_refund_notification', 'type' => 'text'],
 343         ],
 344       ],
 345     ];
 346   }
 347
 348   /**
 349    * Get any required template updates.
 350    *
 351    * @return array
 352    */
 353   public function getTemplatesToUpdate() {
 354     $templates = $this->getTemplateUpdates();
 355     $return = [];
 356     foreach ($templates as $templateArray) {
 357       if ($templateArray['version'] === $this->getUpgradeVersion()) {
 358         foreach ($templateArray['templates'] as $template) {
 359           $return[$template['name'] . '_' . $template['type']] = array_merge($template, $templateArray);
 360         }
 361       }
 362     }
 363     return $return;
 364   }
 365
 366   /**
 367    * Replace a token with the new preferred option.
 368    *
 369    * @param string $workflowName
 370    * @param string $old
 371    * @param string $new
 372    */
 373   public function replaceTokenInTemplate(string $workflowName, string $old, string $new): void {
 374     $oldToken = '{' . $old . '}';
 375     $newToken = '{' . $new . '}';
 376     CRM_Core_DAO::executeQuery("UPDATE civicrm_msg_template
 377       SET
 378         msg_text = REPLACE(msg_text, '$oldToken', '$newToken'),
 379         msg_subject = REPLACE(msg_subject, '$oldToken', '$newToken'),
 380         msg_html = REPLACE(msg_html, '$oldToken', '$newToken')
 381       WHERE workflow_name = '$workflowName'
 382     ");
 383   }
 384
 385   /**
 386    * Replace a token with the new preferred option in non-workflow templates.
 387    *
 388    * @param string $old
 389    * @param string $new
 390    */
 391   public function replaceTokenInMessageTemplates(string $old, string $new): void {
 392     $oldToken = '{' . $old . '}';
 393     $newToken = '{' . $new . '}';
 394     CRM_Core_DAO::executeQuery("UPDATE civicrm_msg_template
 395       SET
 396         msg_text = REPLACE(msg_text, '$oldToken', '$newToken'),
 397         msg_subject = REPLACE(msg_subject, '$oldToken', '$newToken'),
 398         msg_html = REPLACE(msg_html, '$oldToken', '$newToken')
 399       WHERE workflow_name IS NULL
 400     ");
 401   }
 402
 403   /**
 404    * Replace a token with the new preferred option.
 405    *
 406    * @param string $old
 407    * @param string $new
 408    */
 409   public function replaceTokenInActionSchedule(string $old, string $new): void {
 410     $oldToken = '{' . $old . '}';
 411     $newToken = '{' . $new . '}';
 412     CRM_Core_DAO::executeQuery("UPDATE civicrm_action_schedule
 413       SET
 414         body_text = REPLACE(body_text, '$oldToken', '$newToken'),
 415         subject = REPLACE(subject, '$oldToken', '$newToken'),
 416         body_html = REPLACE(body_html, '$oldToken', '$newToken')
 417     ");
 418   }
 419
 420   /**
 421    * Replace a token with the new preferred option in a print label.
 422    *
 423    * @param string $old
 424    * @param string $new
 425    */
 426   public function replaceTokenInPrintLabel(string $old, string $new): void {
 427     $oldToken = '{' . $old . '}';
 428     $newToken = '{' . $new . '}';
 429     CRM_Core_DAO::executeQuery("UPDATE civicrm_print_label
 430       SET
 431         data = REPLACE(data, '$oldToken', '$newToken')
 432     ");
 433   }
 434
 435   /**
 436    * Replace a token with the new preferred option in a print label.
 437    *
 438    * @param string $old
 439    * @param string $new
 440    *
 441    * @throws \API_Exception
 442    */
 443   public function replaceTokenInGreetingOptions(string $old, string $new): void {
 444     $oldToken = '{' . $old . '}';
 445     $newToken = '{' . $new . '}';
 446     $options = (array) Civi\Api4\OptionValue::get(FALSE)
 447       ->addWhere('option_group_id:name', 'IN', ['email_greeting', 'postal_greeting', 'addressee'])
 448       ->setSelect(['id'])->execute()->indexBy('id');
 449     CRM_Core_DAO::executeQuery("UPDATE civicrm_option_value
 450       SET
 451         label = REPLACE(label, '$oldToken', '$newToken'),
 452         name = REPLACE(name, '$oldToken', '$newToken')
 453       WHERE id IN (" . implode(',', array_keys($options)) . ')'
 454     );
 455   }
 456
 457   /**
 458    * Get the upgrade messages.
 459    */
 460   public function getUpgradeMessages() {
 461     $updates = $this->getTemplatesToUpdate();
 462     $messages = [];
 463     $templateLabel = '';
 464     foreach ($updates as $key => $value) {
 465       try {
 466         $templateLabel = civicrm_api3('OptionValue', 'getvalue', [
 467           'return' => 'label',
 468           'name' => $value['name'],
 469           'options' => ['limit' => 1],
 470         ]);
 471       }
 472       catch (Exception $e) {
 473         if (!empty($value['label'])) {
 474           $templateLabel = $value['label'];
 475         }
 476       }
 477       $messages[$templateLabel] = $value['upgrade_descriptor'];
 478     }
 479     return $messages;
 480   }
 481
 482   /**
 483    * Update message templates.
 484    */
 485   public function updateTemplates() {
 486     $templates = $this->getTemplatesToUpdate();
 487     foreach ($templates as $template) {
 488       $workFlowID = CRM_Core_DAO::singleValueQuery("SELECT MAX(id) as id FROM civicrm_option_value WHERE name = %1", [
 489         1 => [$template['name'], 'String'],
 490       ]);
 491       $content = file_get_contents(\Civi::paths()->getPath('[civicrm.root]/xml/templates/message_templates/' . $template['name'] . '_' . $template['type'] . '.tpl'));
 492       $templatesToUpdate = [];
 493       if (!empty($workFlowID)) {
 494         // This could be empty if the template was deleted. It should not happen,
 495         // but has been seen in the wild (ex: marketing/civicrm-website#163).
 496         $id = CRM_Core_DAO::singleValueQuery("SELECT id FROM civicrm_msg_template WHERE workflow_id = $workFlowID AND is_reserved = 1");
 497         if ($id) {
 498           $templatesToUpdate[] = $id;
 499         }
 500         $defaultTemplateID = CRM_Core_DAO::singleValueQuery("
 501           SELECT default_template.id FROM civicrm_msg_template reserved
 502           LEFT JOIN civicrm_msg_template default_template
 503             ON reserved.workflow_id = default_template.workflow_id
 504           WHERE reserved.workflow_id = $workFlowID
 505           AND reserved.is_reserved = 1 AND default_template.is_default = 1 AND reserved.id <> default_template.id
 506           AND reserved.msg_{$template['type']} = default_template.msg_{$template['type']}
 507         ");
 508         if ($defaultTemplateID) {
 509           $templatesToUpdate[] = $defaultTemplateID;
 510         }
 511
 512         if (!empty($templatesToUpdate)) {
 513           CRM_Core_DAO::executeQuery("
 514             UPDATE civicrm_msg_template SET msg_{$template['type']} = %1 WHERE id IN (" . implode(',', $templatesToUpdate) . ")", [
 515               1 => [$content, 'String'],
 516             ]
 517           );
 518         }
 519       }
 520     }
 521   }
 522
 523 }