[civicrm-core.git] / CRM / Upgrade / Incremental / MessageTemplates.php

<?php
/*
 +--------------------------------------------------------------------+
 | Copyright CiviCRM LLC. All rights reserved.                        |
 |                                                                    |
 | This work is published under the GNU AGPLv3 license with some      |
 | permitted exceptions and without any warranty. For full license    |
 | and copyright information, see https://civicrm.org/licensing       |
 +--------------------------------------------------------------------+
 */

/**
 *
 * @package CRM
 * @copyright CiviCRM LLC https://civicrm.org/licensing
 */
class CRM_Upgrade_Incremental_MessageTemplates {

  /**
   * Version we are upgrading to.
   *
   * @var string
   */
  protected $upgradeVersion;

  /**
   * @return string
   */
  public function getUpgradeVersion() {
    return $this->upgradeVersion;
  }

  /**
   * @param string $upgradeVersion
   */
  public function setUpgradeVersion($upgradeVersion) {
    $this->upgradeVersion = $upgradeVersion;
  }

  /**
   * CRM_Upgrade_Incremental_MessageTemplates constructor.
   *
   * @param string $upgradeVersion
   */
  public function __construct($upgradeVersion) {
    $this->setUpgradeVersion($upgradeVersion);
  }

  /**
   * Get any templates that have been updated.
   *
   * @return array
   */
  protected function getTemplateUpdates() {
    return [
      [
        'version' => '5.4.alpha1',
        'upgrade_descriptor' => ts('Use email greeting at top where available'),
        'templates' => [
          ['name' => 'membership_online_receipt', 'type' => 'text'],
          ['name' => 'membership_online_receipt', 'type' => 'html'],
          ['name' => 'contribution_online_receipt', 'type' => 'text'],
          ['name' => 'contribution_online_receipt', 'type' => 'html'],
          ['name' => 'event_online_receipt', 'type' => 'text'],
          ['name' => 'event_online_receipt', 'type' => 'html'],
          ['name' => 'event_online_receipt', 'type' => 'subject'],
        ],
      ],
      [
        'version' => '5.7.alpha1',
        'upgrade_descriptor' => ts('Fix invoice number (human readable) instead of id (reference)'),
        'label' => ts('Contributions - Invoice'),
        'templates' => [
          ['name' => 'contribution_invoice_receipt', 'type' => 'html'],
        ],
      ],
      [
        'version' => '5.10.alpha1',
        'upgrade_descriptor' => ts('Show recurring cancel/update URLs in receipt based on payment processor capabilities'),
        'label' => ts('Receipts - cancel/update subscription URLs'),
        'templates' => [
          ['name' => 'contribution_online_receipt', 'type' => 'text'],
          ['name' => 'contribution_online_receipt', 'type' => 'html'],
          ['name' => 'contribution_recurring_notify', 'type' => 'text'],
          ['name' => 'contribution_recurring_notify', 'type' => 'html'],
          ['name' => 'membership_online_receipt', 'type' => 'text'],
          ['name' => 'membership_online_receipt', 'type' => 'html'],
        ],
      ],
      [
        'version' => '5.12.alpha1',
        'upgrade_descriptor' => ts('Update payment notification to remove print text, use email greeting'),
        'label' => ts('Payment notification'),
        'templates' => [
          ['name' => 'payment_or_refund_notification', 'type' => 'text'],
          ['name' => 'payment_or_refund_notification', 'type' => 'html'],
        ],
      ],
      [
        'version' => '5.15.alpha1',
        'upgrade_descriptor' => ts('Use email greeting and fix capitalization'),
        'label' => ts('Pledge acknowledgement'),
        'templates' => [
          ['name' => 'pledge_acknowledge', 'type' => 'text'],
          ['name' => 'pledge_acknowledge', 'type' => 'html'],
        ],
      ],
      [
        'version' => '5.20.alpha1',
        'upgrade_descriptor' => ts('Fix missing Email greetings'),
        'templates' => [
          ['name' => 'contribution_dupalert', 'type' => 'subject'],
          ['name' => 'contribution_invoice_receipt', 'type' => 'subject'],
          ['name' => 'contribution_offline_receipt', 'type' => 'html'],
          ['name' => 'contribution_offline_receipt', 'type' => 'subject'],
          ['name' => 'contribution_offline_receipt', 'type' => 'text'],
          ['name' => 'contribution_online_receipt', 'type' => 'subject'],
          ['name' => 'contribution_online_receipt', 'type' => 'html'],
          ['name' => 'contribution_recurring_billing', 'type' => 'html'],
          ['name' => 'contribution_recurring_billing', 'type' => 'subject'],
          ['name' => 'contribution_recurring_billing', 'type' => 'text'],
          ['name' => 'contribution_recurring_cancelled', 'type' => 'html'],
          ['name' => 'contribution_recurring_cancelled', 'type' => 'subject'],
          ['name' => 'contribution_recurring_cancelled', 'type' => 'text'],
          ['name' => 'contribution_recurring_edit', 'type' => 'html'],
          ['name' => 'contribution_recurring_edit', 'type' => 'subject'],
          ['name' => 'contribution_recurring_edit', 'type' => 'text'],
          ['name' => 'contribution_recurring_notify', 'type' => 'html'],
          ['name' => 'contribution_recurring_notify', 'type' => 'subject'],
          ['name' => 'contribution_recurring_notify', 'type' => 'text'],
          ['name' => 'event_offline_receipt', 'type' => 'html'],
          ['name' => 'event_offline_receipt', 'type' => 'subject'],
          ['name' => 'event_offline_receipt', 'type' => 'text'],
          ['name' => 'event_online_receipt', 'type' => 'html'],
          ['name' => 'event_online_receipt', 'type' => 'subject'],
          ['name' => 'event_online_receipt', 'type' => 'text'],
          ['name' => 'event_registration_receipt', 'type' => 'html'],
          ['name' => 'event_registration_receipt', 'type' => 'subject'],
          ['name' => 'event_registration_receipt', 'type' => 'text'],
          ['name' => 'membership_autorenew_billing', 'type' => 'html'],
          ['name' => 'membership_autorenew_billing', 'type' => 'subject'],
          ['name' => 'membership_autorenew_billing', 'type' => 'text'],
          ['name' => 'membership_autorenew_cancelled', 'type' => 'html'],
          ['name' => 'membership_autorenew_cancelled', 'type' => 'subject'],
          ['name' => 'membership_autorenew_cancelled', 'type' => 'text'],
          ['name' => 'membership_offline_receipt', 'type' => 'html'],
          ['name' => 'membership_offline_receipt', 'type' => 'subject'],
          ['name' => 'membership_offline_receipt', 'type' => 'text'],
          ['name' => 'membership_online_receipt', 'type' => 'subject'],
          ['name' => 'membership_online_receipt', 'type' => 'html'],
          ['name' => 'participant_cancelled', 'type' => 'html'],
          ['name' => 'participant_cancelled', 'type' => 'subject'],
          ['name' => 'participant_cancelled', 'type' => 'text'],
          ['name' => 'participant_confirm', 'type' => 'html'],
          ['name' => 'participant_confirm', 'type' => 'subject'],
          ['name' => 'participant_confirm', 'type' => 'text'],
          ['name' => 'participant_expired', 'type' => 'html'],
          ['name' => 'participant_expired', 'type' => 'subject'],
          ['name' => 'participant_expired', 'type' => 'text'],
          ['name' => 'participant_transferred', 'type' => 'html'],
          ['name' => 'participant_transferred', 'type' => 'subject'],
          ['name' => 'participant_transferred', 'type' => 'text'],
          ['name' => 'payment_or_refund_notification', 'type' => 'html'],
          ['name' => 'payment_or_refund_notification', 'type' => 'subject'],
          ['name' => 'payment_or_refund_notification', 'type' => 'text'],
          ['name' => 'pcp_notify', 'type' => 'subject'],
          ['name' => 'pcp_owner_notify', 'type' => 'html'],
          ['name' => 'pcp_owner_notify', 'type' => 'subject'],
          ['name' => 'pcp_owner_notify', 'type' => 'text'],
          ['name' => 'pcp_status_change', 'type' => 'subject'],
          ['name' => 'pcp_status_change', 'type' => 'html'],
          ['name' => 'pcp_supporter_notify', 'type' => 'html'],
          ['name' => 'pcp_supporter_notify', 'type' => 'subject'],
          ['name' => 'pcp_supporter_notify', 'type' => 'text'],
          ['name' => 'petition_confirmation_needed', 'type' => 'html'],
          ['name' => 'petition_confirmation_needed', 'type' => 'subject'],
          ['name' => 'petition_confirmation_needed', 'type' => 'text'],
          ['name' => 'petition_sign', 'type' => 'html'],
          ['name' => 'petition_sign', 'type' => 'subject'],
          ['name' => 'petition_sign', 'type' => 'text'],
          ['name' => 'pledge_acknowledge', 'type' => 'subject'],
          ['name' => 'pledge_acknowledge', 'type' => 'html'],
          ['name' => 'pledge_acknowledge', 'type' => 'text'],
          ['name' => 'pledge_reminder', 'type' => 'html'],
          ['name' => 'pledge_reminder', 'type' => 'subject'],
          ['name' => 'pledge_reminder', 'type' => 'text'],
          ['name' => 'uf_notify', 'type' => 'subject'],
          ['name' => 'uf_notify', 'type' => 'html'],
          ['name' => 'case_activity', 'type' => 'html'],
          ['name' => 'contribution_dupalert', 'type' => 'html'],
          ['name' => 'friend', 'type' => 'html'],
          ['name' => 'test_preview', 'type' => 'html'],
        ],
      ],
      [
        'version' => '5.21.beta1',
        'upgrade_descriptor' => ts('Fix Membership Receipt'),
        'templates' => [
          ['name' => 'membership_online_receipt', 'type' => 'html'],
        ],
      ],
      [
        'version' => '5.23.alpha1',
        'upgrade_descriptor' => ts('Add Contributor Name to Offline Contribution receipts; fix bad event self-service URL'),
        'templates' => [
          ['name' => 'contribution_offline_receipt', 'type' => 'text'],
          ['name' => 'contribution_offline_receipt', 'type' => 'html'],
          ['name' => 'participant_confirm', 'type' => 'html'],
        ],
      ],
      [
        'version' => '5.24.alpha1',
        'upgrade_descriptor' => ts('Layout fixes for the Contribution templates'),
        'templates' => [
          ['name' => 'contribution_invoice_receipt', 'type' => 'html'],
        ],
      ],
      [
        'version' => '5.30.alpha1',
        'upgrade_descriptor' => ts('Support negative hours for cancellation/transfer'),
        'templates' => [
          ['name' => 'participant_confirm', 'type' => 'html'],
          ['name' => 'participant_confirm', 'type' => 'text'],
          ['name' => 'event_online_receipt', 'type' => 'html'],
          ['name' => 'event_online_receipt', 'type' => 'text'],
        ],
      ],
      [
        'version' => '5.30.beta1',
        'upgrade_descriptor' => ts('Ensure that amount paid is shown even when fully paid'),
        'templates' => [
          ['name' => 'contribution_invoice_receipt', 'type' => 'html'],
        ],
      ],
      [
        'version' => '5.38.alpha1',
        'upgrade_descriptor' => ts('Fix Petition Confirmation email having a blank space at the end of url'),
        'templates' => [
          ['name' => 'petition_confirmation_needed', 'type' => 'html'],
        ],
      ],
      [
        'version' => '5.38.alpha1',
        'upgrade_descriptor' => ts('Fix Pledge and PCP urls to go to the front end site rather than backend site'),
        'templates' => [
          ['name' => 'pcp_notify', 'type' => 'html'],
          ['name' => 'pcp_notify', 'type' => 'text'],
          ['name' => 'pledge_reminder', 'type' => 'html'],
          ['name' => 'pledge_reminder', 'type' => 'text'],
        ],
      ],
      [
        'version' => '5.43.alpha1',
        'upgrade_descriptor' => ts('Missed templates from earlier versions'),
        'templates' => [
          ['name' => 'contribution_online_receipt', 'type' => 'text'],
          ['name' => 'case_activity', 'type' => 'html'],
          ['name' => 'case_activity', 'type' => 'text'],
          ['name' => 'case_activity', 'type' => 'subject'],
        ],
      ],
      [
        'version' => '5.44.alpha1',
        'upgrade_descriptor' => ts('Use domain tokens instead of smarty values'),
        'label' => ts('Pledge acknowledgement'),
        'templates' => [
          ['name' => 'pledge_acknowledge', 'type' => 'text'],
          ['name' => 'pledge_acknowledge', 'type' => 'html'],
        ],
      ],
      [
        'version' => '5.44.alpha1',
        'upgrade_descriptor' => ts('Use domain and contact tokens instead of smarty values'),
        'label' => ts('Pledge reminder'),
        'templates' => [
          ['name' => 'pledge_reminder', 'type' => 'text'],
          ['name' => 'pledge_reminder', 'type' => 'html'],
        ],
      ],
      [
        'version' => '5.44.alpha1',
        'upgrade_descriptor' => ts('Use domain and contact tokens instead of smarty values'),
        'label' => ts('Participant Transferred'),
        'templates' => [
          ['name' => 'participant_transferred', 'type' => 'text'],
          ['name' => 'participant_transferred', 'type' => 'html'],
        ],
      ],
      [
        'version' => '5.44.alpha1',
        'upgrade_descriptor' => ts('Use domain and contact tokens instead of smarty values'),
        'label' => ts('Participant Cancelled'),
        'templates' => [
          ['name' => 'participant_cancelled', 'type' => 'text'],
          ['name' => 'participant_cancelled', 'type' => 'html'],
        ],
      ],

      [
        'version' => '5.44.alpha1',
        'upgrade_descriptor' => ts('Use domain and contact tokens instead of smarty values'),
        'label' => ts('Participant Expired'),
        'templates' => [
          ['name' => 'participant_expired', 'type' => 'text'],
          ['name' => 'participant_expired', 'type' => 'html'],
        ],
      ],
      [
        'version' => '5.44.alpha1',
        'upgrade_descriptor' => ts('Use domain and contact tokens instead of smarty values'),
        'label' => ts('Participant Confirmed'),
        'templates' => [
          ['name' => 'participant_confirm', 'type' => 'text'],
          ['name' => 'participant_confirm', 'type' => 'html'],
        ],
      ],
    ];
  }

  /**
   * Get any required template updates.
   *
   * @return array
   */
  public function getTemplatesToUpdate() {
    $templates = $this->getTemplateUpdates();
    $return = [];
    foreach ($templates as $templateArray) {
      if ($templateArray['version'] === $this->getUpgradeVersion()) {
        foreach ($templateArray['templates'] as $template) {
          $return[$template['name'] . '_' . $template['type']] = array_merge($template, $templateArray);
        }
      }
    }
    return $return;
  }

  /**
   * Replace a token with the new preferred option.
   *
   * @param string $workflowName
   * @param string $old
   * @param string $new
   */
  public function replaceTokenInTemplate(string $workflowName, string $old, string $new): void {
    $oldToken = '{' . $old . '}';
    $newToken = '{' . $new . '}';
    CRM_Core_DAO::executeQuery("UPDATE civicrm_msg_template
      SET
        msg_text = REPLACE(msg_text, '$oldToken', '$newToken'),
        msg_subject = REPLACE(msg_subject, '$oldToken', '$newToken'),
        msg_html = REPLACE(msg_html, '$oldToken', '$newToken')
      WHERE workflow_name = '$workflowName'
    ");
  }

  /**
   * Replace a token with the new preferred option in non-workflow templates.
   *
   * @param string $old
   * @param string $new
   */
  public function replaceTokenInMessageTemplates(string $old, string $new): void {
    $oldToken = '{' . $old . '}';
    $newToken = '{' . $new . '}';
    CRM_Core_DAO::executeQuery("UPDATE civicrm_msg_template
      SET
        msg_text = REPLACE(msg_text, '$oldToken', '$newToken'),
        msg_subject = REPLACE(msg_subject, '$oldToken', '$newToken'),
        msg_html = REPLACE(msg_html, '$oldToken', '$newToken')
      WHERE workflow_name IS NULL
    ");
  }

  /**
   * Replace a token with the new preferred option.
   *
   * @param string $old
   * @param string $new
   */
  public function replaceTokenInActionSchedule(string $old, string $new): void {
    $oldToken = '{' . $old . '}';
    $newToken = '{' . $new . '}';
    CRM_Core_DAO::executeQuery("UPDATE civicrm_action_schedule
      SET
        body_text = REPLACE(body_text, '$oldToken', '$newToken'),
        subject = REPLACE(subject, '$oldToken', '$newToken'),
        body_html = REPLACE(body_html, '$oldToken', '$newToken')
    ");
  }

  /**
   * Replace a token with the new preferred option in a print label.
   *
   * @param string $old
   * @param string $new
   */
  public function replaceTokenInPrintLabel(string $old, string $new): void {
    $oldToken = '{' . $old . '}';
    $newToken = '{' . $new . '}';
    CRM_Core_DAO::executeQuery("UPDATE civicrm_print_label
      SET
        data = REPLACE(data, '$oldToken', '$newToken')
    ");
  }

  /**
   * Replace a token with the new preferred option in a print label.
   *
   * @param string $old
   * @param string $new
   *
   * @throws \API_Exception
   */
  public function replaceTokenInGreetingOptions(string $old, string $new): void {
    $oldToken = '{' . $old . '}';
    $newToken = '{' . $new . '}';
    $options = (array) Civi\Api4\OptionValue::get(FALSE)
      ->addWhere('option_group_id:name', 'IN', ['email_greeting', 'postal_greeting', 'addressee'])
      ->setSelect(['id'])->execute()->indexBy('id');
    CRM_Core_DAO::executeQuery("UPDATE civicrm_option_value
      SET
        label = REPLACE(label, '$oldToken', '$newToken'),
        name = REPLACE(name, '$oldToken', '$newToken')
      WHERE id IN (" . implode(',', array_keys($options)) . ')'
    );
  }

  /**
   * Get the upgrade messages.
   */
  public function getUpgradeMessages() {
    $updates = $this->getTemplatesToUpdate();
    $messages = [];
    $templateLabel = '';
    foreach ($updates as $key => $value) {
      try {
        $templateLabel = civicrm_api3('OptionValue', 'getvalue', [
          'return' => 'label',
          'name' => $value['name'],
          'options' => ['limit' => 1],
        ]);
      }
      catch (Exception $e) {
        if (!empty($value['label'])) {
          $templateLabel = $value['label'];
        }
      }
      $messages[$templateLabel] = $value['upgrade_descriptor'];
    }
    return $messages;
  }

  /**
   * Update message templates.
   */
  public function updateTemplates() {
    $templates = $this->getTemplatesToUpdate();
    foreach ($templates as $template) {
      $workFlowID = CRM_Core_DAO::singleValueQuery("SELECT MAX(id) as id FROM civicrm_option_value WHERE name = %1", [
        1 => [$template['name'], 'String'],
      ]);
      $content = file_get_contents(\Civi::paths()->getPath('[civicrm.root]/xml/templates/message_templates/' . $template['name'] . '_' . $template['type'] . '.tpl'));
      $templatesToUpdate = [];
      if (!empty($workFlowID)) {
        // This could be empty if the template was deleted. It should not happen,
        // but has been seen in the wild (ex: marketing/civicrm-website#163).
        $id = CRM_Core_DAO::singleValueQuery("SELECT id FROM civicrm_msg_template WHERE workflow_id = $workFlowID AND is_reserved = 1");
        if ($id) {
          $templatesToUpdate[] = $id;
        }
        $defaultTemplateID = CRM_Core_DAO::singleValueQuery("
          SELECT default_template.id FROM civicrm_msg_template reserved
          LEFT JOIN civicrm_msg_template default_template
            ON reserved.workflow_id = default_template.workflow_id
          WHERE reserved.workflow_id = $workFlowID
          AND reserved.is_reserved = 1 AND default_template.is_default = 1 AND reserved.id <> default_template.id
          AND reserved.msg_{$template['type']} = default_template.msg_{$template['type']}
        ");
        if ($defaultTemplateID) {
          $templatesToUpdate[] = $defaultTemplateID;
        }

        if (!empty($templatesToUpdate)) {
          CRM_Core_DAO::executeQuery("
            UPDATE civicrm_msg_template SET msg_{$template['type']} = %1 WHERE id IN (" . implode(',', $templatesToUpdate) . ")", [
              1 => [$content, 'String'],
            ]
          );
        }
      }
    }
  }

}
Commit	Line	Data
fe83c251	1	<?php
	2	/*
	3	+--------------------------------------------------------------------+
bc77d7c0	4	\| Copyright CiviCRM LLC. All rights reserved. \|
fe83c251	5	\| \|
bc77d7c0 TO	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 \|
fe83c251	9	+--------------------------------------------------------------------+
	10	*/
	11
	12	/**
	13	*
	14	* @package CRM
ca5cec67	15	* @copyright CiviCRM LLC https://civicrm.org/licensing
fe83c251	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'],
e74e138c	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'],
2db5cabf	66	['name' => 'event_online_receipt', 'type' => 'subject'],
3655bea4	67	],
fe83c251	68	],
9d404148	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'],
3655bea4	75	],
f926d56f MWMC	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'],
3655bea4	88	],
1e477c5b	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'],
3655bea4	97	],
1e477c5b	98	],
3eb013ed	99	[
c4ada33c	100	'version' => '5.15.alpha1',
3eb013ed P	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	],
56658066 AH	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'],
609ddd4b	118	['name' => 'contribution_online_receipt', 'type' => 'html'],
56658066 AH	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'],
609ddd4b	150	['name' => 'membership_online_receipt', 'type' => 'html'],
56658066 AH	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'],
609ddd4b	171	['name' => 'pcp_status_change', 'type' => 'html'],
56658066 AH	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'],
609ddd4b SL	182	['name' => 'pledge_acknowledge', 'type' => 'html'],
609ddd4b SL	183	['name' => 'pledge_acknowledge', 'type' => 'text'],
56658066 AH	184	['name' => 'pledge_reminder', 'type' => 'html'],
	185	['name' => 'pledge_reminder', 'type' => 'subject'],
	186	['name' => 'pledge_reminder', 'type' => 'text'],
	187	['name' => 'uf_notify', 'type' => 'subject'],
609ddd4b SL	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'],
eeab8851 ML	193	],
	194	],
	195	[
	196	'version' => '5.21.beta1',
	197	'upgrade_descriptor' => ts('Fix Membership Receipt'),
	198	'templates' => [
	199	['name' => 'membership_online_receipt', 'type' => 'html'],
448a7f8c AF	200	],
	201	],
	202	[
	203	'version' => '5.23.alpha1',
91794ad8	204	'upgrade_descriptor' => ts('Add Contributor Name to Offline Contribution receipts; fix bad event self-service URL'),
448a7f8c AF	205	'templates' => [
	206	['name' => 'contribution_offline_receipt', 'type' => 'text'],
	207	['name' => 'contribution_offline_receipt', 'type' => 'html'],
91794ad8	208	['name' => 'participant_confirm', 'type' => 'html'],
fa8f1417	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'],
a54cf42b JG	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'],
56658066 AH	226	],
56658066 AH	227	],
5e9334ea SL	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'],
9a33a6f2 SL	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'],
70d744ef SL	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'],
5e9334ea SL	250	],
5e9334ea SL	251	],
f0e84bbe	252	[
f0e84bbe	253	'version' => '5.43.alpha1',
239ef6fe	254	'upgrade_descriptor' => ts('Missed templates from earlier versions'),
f0e84bbe	255	'templates' => [
f0e84bbe	256	['name' => 'contribution_online_receipt', 'type' => 'text'],
239ef6fe	257	['name' => 'case_activity', 'type' => 'html'],
	258	['name' => 'case_activity', 'type' => 'text'],
	259	['name' => 'case_activity', 'type' => 'subject'],
f0e84bbe	260	],
f0e84bbe	261	],
aa7dd30f EM	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	],
21585345 EM	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	],
0d6fa54d EM	280	[
	281	'version' => '5.44.alpha1',
	282	'upgrade_descriptor' => ts('Use domain and contact tokens instead of smarty values'),
a8829c96	283	'label' => ts('Participant Transferred'),
0d6fa54d EM	284	'templates' => [
	285	['name' => 'participant_transferred', 'type' => 'text'],
	286	['name' => 'participant_transferred', 'type' => 'html'],
	287	],
	288	],
a8829c96 EM	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	],
6f09413b EM	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	],
fe83c251	317	];
	318	}
	319
	320	/**
	321	* Get any required template updates.
	322	*
	323	* @return array
	324	*/
	325	public function getTemplatesToUpdate() {
	326	$templates = $this->getTemplateUpdates();
	327	$return = [];
	328	foreach ($templates as $templateArray) {
	329	if ($templateArray['version'] === $this->getUpgradeVersion()) {
	330	foreach ($templateArray['templates'] as $template) {
	331	$return[$template['name'] . '_' . $template['type']] = array_merge($template, $templateArray);
	332	}
	333	}
	334	}
	335	return $return;
	336	}
	337
8515e10c EM	338	/**
	339	* Replace a token with the new preferred option.
	340	*
	341	* @param string $workflowName
	342	* @param string $old
	343	* @param string $new
	344	*/
	345	public function replaceTokenInTemplate(string $workflowName, string $old, string $new): void {
	346	$oldToken = '{' . $old . '}';
	347	$newToken = '{' . $new . '}';
	348	CRM_Core_DAO::executeQuery("UPDATE civicrm_msg_template
	349	SET
	350	msg_text = REPLACE(msg_text, '$oldToken', '$newToken'),
	351	msg_subject = REPLACE(msg_subject, '$oldToken', '$newToken'),
	352	msg_html = REPLACE(msg_html, '$oldToken', '$newToken')
	353	WHERE workflow_name = '$workflowName'
	354	");
	355	}
	356
fa547918 EM	357	/**
	358	* Replace a token with the new preferred option in non-workflow templates.
	359	*
	360	* @param string $old
	361	* @param string $new
	362	*/
	363	public function replaceTokenInMessageTemplates(string $old, string $new): void {
	364	$oldToken = '{' . $old . '}';
	365	$newToken = '{' . $new . '}';
	366	CRM_Core_DAO::executeQuery("UPDATE civicrm_msg_template
	367	SET
	368	msg_text = REPLACE(msg_text, '$oldToken', '$newToken'),
	369	msg_subject = REPLACE(msg_subject, '$oldToken', '$newToken'),
	370	msg_html = REPLACE(msg_html, '$oldToken', '$newToken')
	371	WHERE workflow_name IS NULL
	372	");
	373	}
	374
b2148ce1 EM	375	/**
	376	* Replace a token with the new preferred option.
	377	*
	378	* @param string $old
	379	* @param string $new
	380	*/
	381	public function replaceTokenInActionSchedule(string $old, string $new): void {
	382	$oldToken = '{' . $old . '}';
	383	$newToken = '{' . $new . '}';
	384	CRM_Core_DAO::executeQuery("UPDATE civicrm_action_schedule
	385	SET
	386	body_text = REPLACE(body_text, '$oldToken', '$newToken'),
	387	subject = REPLACE(subject, '$oldToken', '$newToken'),
	388	body_html = REPLACE(body_html, '$oldToken', '$newToken')
	389	");
	390	}
	391
8ff591cb EM	392	/**
	393	* Replace a token with the new preferred option in a print label.
	394	*
	395	* @param string $old
	396	* @param string $new
	397	*/
	398	public function replaceTokenInPrintLabel(string $old, string $new): void {
	399	$oldToken = '{' . $old . '}';
	400	$newToken = '{' . $new . '}';
c9ce3966	401	CRM_Core_DAO::executeQuery("UPDATE civicrm_print_label
8ff591cb EM	402	SET
	403	data = REPLACE(data, '$oldToken', '$newToken')
	404	");
	405	}
	406
2f7db718 EM	407	/**
	408	* Replace a token with the new preferred option in a print label.
	409	*
	410	* @param string $old
	411	* @param string $new
	412	*
	413	* @throws \API_Exception
	414	*/
	415	public function replaceTokenInGreetingOptions(string $old, string $new): void {
	416	$oldToken = '{' . $old . '}';
	417	$newToken = '{' . $new . '}';
	418	$options = (array) Civi\Api4\OptionValue::get(FALSE)
	419	->addWhere('option_group_id:name', 'IN', ['email_greeting', 'postal_greeting', 'addressee'])
	420	->setSelect(['id'])->execute()->indexBy('id');
	421	CRM_Core_DAO::executeQuery("UPDATE civicrm_option_value
	422	SET
	423	label = REPLACE(label, '$oldToken', '$newToken'),
	424	name = REPLACE(name, '$oldToken', '$newToken')
	425	WHERE id IN (" . implode(',', array_keys($options)) . ')'
	426	);
	427	}
	428
fe83c251	429	/**
	430	* Get the upgrade messages.
	431	*/
	432	public function getUpgradeMessages() {
	433	$updates = $this->getTemplatesToUpdate();
	434	$messages = [];
9d404148	435	$templateLabel = '';
fe83c251	436	foreach ($updates as $key => $value) {
9d404148	437	try {
	438	$templateLabel = civicrm_api3('OptionValue', 'getvalue', [
	439	'return' => 'label',
	440	'name' => $value['name'],
	441	'options' => ['limit' => 1],
	442	]);
	443	}
	444	catch (Exception $e) {
	445	if (!empty($value['label'])) {
	446	$templateLabel = $value['label'];
	447	}
	448	}
fe83c251	449	$messages[$templateLabel] = $value['upgrade_descriptor'];
	450	}
	451	return $messages;
	452	}
	453
	454	/**
	455	* Update message templates.
	456	*/
	457	public function updateTemplates() {
	458	$templates = $this->getTemplatesToUpdate();
	459	foreach ($templates as $template) {
	460	$workFlowID = CRM_Core_DAO::singleValueQuery("SELECT MAX(id) as id FROM civicrm_option_value WHERE name = %1", [
	461	1 => [$template['name'], 'String'],
	462	]);
	463	$content = file_get_contents(\Civi::paths()->getPath('[civicrm.root]/xml/templates/message_templates/' . $template['name'] . '_' . $template['type'] . '.tpl'));
	464	$templatesToUpdate = [];
9d404148	465	if (!empty($workFlowID)) {
e36d9057 ML	466	// This could be empty if the template was deleted. It should not happen,
	467	// but has been seen in the wild (ex: marketing/civicrm-website#163).
	468	$id = CRM_Core_DAO::singleValueQuery("SELECT id FROM civicrm_msg_template WHERE workflow_id = $workFlowID AND is_reserved = 1");
	469	if ($id) {
	470	$templatesToUpdate[] = $id;
	471	}
9d404148	472	$defaultTemplateID = CRM_Core_DAO::singleValueQuery("
	473	SELECT default_template.id FROM civicrm_msg_template reserved
	474	LEFT JOIN civicrm_msg_template default_template
	475	ON reserved.workflow_id = default_template.workflow_id
	476	WHERE reserved.workflow_id = $workFlowID
	477	AND reserved.is_reserved = 1 AND default_template.is_default = 1 AND reserved.id <> default_template.id
	478	AND reserved.msg_{$template['type']} = default_template.msg_{$template['type']}
	479	");
	480	if ($defaultTemplateID) {
	481	$templatesToUpdate[] = $defaultTemplateID;
	482	}
fe83c251	483
e36d9057 ML	484	if (!empty($templatesToUpdate)) {
	485	CRM_Core_DAO::executeQuery("
	486	UPDATE civicrm_msg_template SET msg_{$template['type']} = %1 WHERE id IN (" . implode(',', $templatesToUpdate) . ")", [
	487	1 => [$content, 'String'],
	488	]
	489	);
	490	}
9d404148	491	}
fe83c251	492	}
	493	}
	494
	495	}