classes/mail/EmailTemplateDAO.inc.php

<?php

// $Id: EmailTemplateDAO.inc.php,v 1.26 2008/07/01 01:16:10 asmecher Exp $


import('mail.EmailTemplate');

class EmailTemplateDAO extends DAO {
   function &getBaseEmailTemplate($emailKey, $journalId) {
      $result = &$this->retrieve(
         'SELECT  d.email_key,
            d.can_edit,
            d.can_disable,
            COALESCE(e.enabled, 1) AS enabled,
            e.email_id,
            e.journal_id,
            d.from_role_id,
            d.to_role_id
         FROM  email_templates_default d
            LEFT JOIN email_templates e ON (d.email_key = e.email_key AND e.journal_id = ?)
         WHERE d.email_key = ?',
         array($journalId, $emailKey)
      );

      $returner = null;
      if ($result->RecordCount() != 0) {
         $returner = &$this->_returnBaseEmailTemplateFromRow($result->GetRowAssoc(false));
      }

      $result->Close();
      unset($result);

      return $returner;
   }

   function &getLocaleEmailTemplate($emailKey, $journalId) {
      $result = &$this->retrieve(
         'SELECT  d.email_key,
            d.can_edit,
            d.can_disable,
            COALESCE(e.enabled, 1) AS enabled,
            e.email_id,
            e.journal_id,
            d.from_role_id,
            d.to_role_id
         FROM  email_templates_default d
            LEFT JOIN email_templates e ON (d.email_key = e.email_key AND e.journal_id = ?)
         WHERE d.email_key = ?',
         array($journalId, $emailKey)
      );

      $returner = null;
      if ($result->RecordCount() != 0) {
         $returner = &$this->_returnLocaleEmailTemplateFromRow($result->GetRowAssoc(false));
      } else {
         $result->Close();
         unset($result);

         // Check to see if there's a custom email template. This is done in PHP to avoid
         // having to do a full outer join or union in SQL.
         $result = &$this->retrieve(
            'SELECT  e.email_key,
               1 AS can_edit,
               1 AS can_disable,
               e.enabled,
               e.email_id,
               e.journal_id,
               NULL AS from_role_id,
               NULL AS to_role_id
            FROM  email_templates e
               LEFT JOIN email_templates_default d ON (e.email_key = d.email_key)
            WHERE d.email_key IS NULL AND
               e.journal_id = ? AND
               e.email_key = ?',
            array($journalId, $emailKey)
         );
         if ($result->RecordCount() != 0) {
            $returner = &$this->_returnLocaleEmailTemplateFromRow($result->GetRowAssoc(false));
         }
      }

      $result->Close();
      unset($result);

      return $returner;
   }

   function &getEmailTemplate($emailKey, $locale, $journalId) {
      $result = &$this->retrieve(
         'SELECT  COALESCE(ed.subject, dd.subject) AS subject,
            COALESCE(ed.body, dd.body) AS body,
            COALESCE(e.enabled, 1) AS enabled,
            d.email_key, d.can_edit, d.can_disable,
            e.journal_id, e.email_id,
            dd.locale,
            d.from_role_id, d.to_role_id
         FROM  email_templates_default d
            LEFT JOIN email_templates_default_data dd ON (dd.email_key = d.email_key)
            LEFT JOIN email_templates e ON (d.email_key = e.email_key AND e.journal_id = ?)
            LEFT JOIN email_templates_data ed ON (ed.email_key = e.email_key AND ed.journal_id = e.journal_id AND ed.locale = dd.locale)
         WHERE d.email_key = ? AND
            dd.locale = ?',
         array($journalId, $emailKey, $locale)
      );

      $returner = null;
      if ($result->RecordCount() != 0) {
         $returner = &$this->_returnEmailTemplateFromRow($result->GetRowAssoc(false));
         $returner->setCustomTemplate(false);
      } else {
         $result->Close();
         unset($result);

         // Check to see if there's a custom email template. This is done in PHP to avoid
         // having to do a full outer join or union in SQL.
         $result = &$this->retrieve(
            'SELECT  ed.subject,
               ed.body,
               1 AS enabled,
               e.email_key,
               1 AS can_edit,
               0 AS can_disable,
               e.journal_id,
               e.email_id,
               ed.locale,
               NULL AS from_role_id,
               NULL AS to_role_id
            FROM  email_templates e
               LEFT JOIN email_templates_data ed ON (ed.email_key = e.email_key AND ed.journal_id = e.journal_id)
               LEFT JOIN email_templates_default d ON (e.email_key = d.email_key)
            WHERE d.email_key IS NULL AND
               e.journal_id = ? AND
               e.email_key = ? AND
               ed.locale = ?',
            array($journalId, $emailKey, $locale)
         );
         if ($result->RecordCount() != 0) {
            $returner = &$this->_returnEmailTemplateFromRow($result->GetRowAssoc(false));
            $returner->setCustomTemplate(true);
         }
      }

      $result->Close();
      unset($result);

      return $returner;
   }

   function &_returnBaseEmailTemplateFromRow(&$row) {
      $emailTemplate = &new BaseEmailTemplate();
      $emailTemplate->setEmailId($row['email_id']);
      $emailTemplate->setJournalId($row['journal_id']);
      $emailTemplate->setEmailKey($row['email_key']);
      $emailTemplate->setEnabled($row['enabled'] == null ? 1 : $row['enabled']);
      $emailTemplate->setCanDisable($row['can_disable']);
      $emailTemplate->setFromRoleId($row['from_role_id']);
      $emailTemplate->setToRoleId($row['to_role_id']);

      HookRegistry::call('EmailTemplateDAO::_returnBaseEmailTemplateFromRow', array(&$emailTemplate, &$row));

      return $emailTemplate;
   }

   function &_returnLocaleEmailTemplateFromRow(&$row) {
      $emailTemplate = &new LocaleEmailTemplate();
      $emailTemplate->setEmailId($row['email_id']);
      $emailTemplate->setJournalId($row['journal_id']);
      $emailTemplate->setEmailKey($row['email_key']);
      $emailTemplate->setEnabled($row['enabled'] == null ? 1 : $row['enabled']);
      $emailTemplate->setCanDisable($row['can_disable']);
      $emailTemplate->setFromRoleId($row['from_role_id']);
      $emailTemplate->setToRoleId($row['to_role_id']);

      $emailTemplate->setCustomTemplate(false);

      if (!HookRegistry::call('EmailTemplateDAO::_returnLocaleEmailTemplateFromRow', array(&$emailTemplate, &$row))) {
         $result = &$this->retrieve(
            'SELECT  dd.locale,
               dd.description,
               COALESCE(ed.subject, dd.subject) AS subject,
               COALESCE(ed.body, dd.body) AS body
            FROM  email_templates_default_data dd
               LEFT JOIN email_templates_data ed ON (dd.email_key = ed.email_key AND dd.locale = ed.locale AND ed.journal_id = ?)
            WHERE dd.email_key = ?',
            array($row['journal_id'], $row['email_key'])
         );

         while (!$result->EOF) {
            $dataRow = &$result->GetRowAssoc(false);
            $emailTemplate->addLocale($dataRow['locale']);
            $emailTemplate->setSubject($dataRow['locale'], $dataRow['subject']);
            $emailTemplate->setBody($dataRow['locale'], $dataRow['body']);
            $emailTemplate->setDescription($dataRow['locale'], $dataRow['description']);
            $result->MoveNext();
         }
         $result->Close();
         unset($result);

         // Retrieve custom email contents as well; this is done in PHP to avoid
         // using a SQL outer join or union.
         $result = &$this->retrieve(
            'SELECT  ed.locale,
               ed.subject,
               ed.body
            FROM  email_templates_data ed
               LEFT JOIN email_templates_default_data dd ON (ed.email_key = dd.email_key AND dd.locale = ed.locale)
            WHERE ed.journal_id = ? AND
               ed.email_key = ? AND
               dd.email_key IS NULL',
            array($row['journal_id'], $row['email_key'])
         );

         while (!$result->EOF) {
            $dataRow = &$result->GetRowAssoc(false);
            $emailTemplate->addLocale($dataRow['locale']);
            $emailTemplate->setSubject($dataRow['locale'], $dataRow['subject']);
            $emailTemplate->setBody($dataRow['locale'], $dataRow['body']);
            $result->MoveNext();

            $emailTemplate->setCustomTemplate(true);
         }

         $result->Close();
         unset($result);
      }

      return $emailTemplate;
   }

   function &_returnEmailTemplateFromRow(&$row, $isCustomTemplate=null) {
      $emailTemplate = &new EmailTemplate();
      $emailTemplate->setEmailId($row['email_id']);
      $emailTemplate->setJournalId($row['journal_id']);
      $emailTemplate->setEmailKey($row['email_key']);
      $emailTemplate->setLocale($row['locale']);
      $emailTemplate->setSubject($row['subject']);
      $emailTemplate->setBody($row['body']);
      $emailTemplate->setEnabled($row['enabled'] == null ? 1 : $row['enabled']);
      $emailTemplate->setCanDisable($row['can_disable']);
      $emailTemplate->setFromRoleId($row['from_role_id']);
      $emailTemplate->setToRoleId($row['to_role_id']);

      if ($isCustomTemplate !== null) {
         $emailTemplate->setCustomTemplate($isCustomTemplate);
      }

      HookRegistry::call('EmailTemplateDAO::_returnEmailTemplateFromRow', array(&$emailTemplate, &$row));

      return $emailTemplate;
   }

   function insertBaseEmailTemplate(&$emailTemplate) {
      return $this->update(
         'INSERT INTO email_templates
            (journal_id, email_key, enabled)
            VALUES
            (?, ?, ?)',
         array(
            $emailTemplate->getJournalId(),
            $emailTemplate->getEmailKey(),
            $emailTemplate->getEnabled() == null ? 0 : 1
         )
      );
      $emailTemplate->setEmailId($this->getInsertEmailId());
      return $emailTemplate->getEmailId();
   }

   function updateBaseEmailTemplate(&$emailTemplate) {
      return $this->update(
         'UPDATE  email_templates
         SET   enabled = ?
         WHERE email_id = ?',
         array(
            $emailTemplate->getEnabled() == null ? 0 : 1,
            $emailTemplate->getEmailId()
         )
      );
   }

   function insertLocaleEmailTemplate(&$emailTemplate) {
      $this->insertBaseEmailTemplate($emailTemplate);
      return $this->updateLocaleEmailTemplateData($emailTemplate);
   }

   function updateLocaleEmailTemplate(&$emailTemplate) {
      $this->updateBaseEmailTemplate($emailTemplate);
      return $this->updateLocaleEmailTemplateData($emailTemplate);
   }

   function updateLocaleEmailTemplateData(&$emailTemplate) {
      foreach ($emailTemplate->getLocales() as $locale) {
         $result = &$this->retrieve(
            'SELECT  COUNT(*)
            FROM  email_templates_data
            WHERE email_key = ? AND
               locale = ? AND
               journal_id = ?',
            array($emailTemplate->getEmailKey(), $locale, $emailTemplate->getJournalId())
         );

         if ($result->fields[0] == 0) {
            $this->update(
               'INSERT INTO email_templates_data
               (email_key, locale, journal_id, subject, body)
               VALUES
               (?, ?, ?, ?, ?)',
               array($emailTemplate->getEmailKey(), $locale, $emailTemplate->getJournalId(), $emailTemplate->getSubject($locale), $emailTemplate->getBody($locale))
            );

         } else {
            $this->update(
               'UPDATE  email_templates_data
               SET   subject = ?,
                  body = ?
               WHERE email_key = ? AND
                  locale = ? AND
                  journal_id = ?',
               array($emailTemplate->getSubject($locale), $emailTemplate->getBody($locale), $emailTemplate->getEmailKey(), $locale, $emailTemplate->getJournalId())
            );
         }

         $result->Close();
         unset($result);
      }
   }

   function deleteEmailTemplateByKey($emailKey, $journalId) {
      $this->update(
         'DELETE FROM email_templates_data WHERE email_key = ? AND journal_id = ?',
         array($emailKey, $journalId)
      );
      return $this->update(
         'DELETE FROM email_templates WHERE email_key = ? AND journal_id = ?',
         array($emailKey, $journalId)
      );
   }

   function &getEmailTemplates($locale, $journalId, $rangeInfo = null) {
      $emailTemplates = array();

      $result = &$this->retrieveRange(
         'SELECT  COALESCE(ed.subject, dd.subject) AS subject,
            COALESCE(ed.body, dd.body) AS body,
            COALESCE(e.enabled, 1) AS enabled,
            d.email_key, d.can_edit, d.can_disable,
            e.journal_id, e.email_id,
            dd.locale,
            d.from_role_id, d.to_role_id
         FROM  email_templates_default d
            LEFT JOIN email_templates_default_data dd ON (dd.email_key = d.email_key)
            LEFT JOIN email_templates e ON (d.email_key = e.email_key AND e.journal_id = ?)
            LEFT JOIN email_templates_data ed ON (ed.email_key = e.email_key AND ed.journal_id = e.journal_id AND ed.locale = dd.locale)
         WHERE dd.locale = ?',
         array($journalId, $locale),
         $rangeInfo
      );

      while (!$result->EOF) {
         $emailTemplates[] = &$this->_returnEmailTemplateFromRow($result->GetRowAssoc(false), false);
         $result->moveNext();
      }
      $result->Close();
      unset($result);

      // Fetch custom email templates as well; this is done in PHP
      // to avoid a union or full outer join call in SQL.
      $result = &$this->retrieve(
         'SELECT  ed.subject,
            ed.body,
            e.enabled,
            e.email_key,
            1 AS can_edit,
            1 AS can_disable,
            e.journal_id,
            e.email_id,
            ed.locale,
            NULL AS from_role_id,
            NULL AS to_role_id
         FROM  email_templates e
            LEFT JOIN email_templates_data ed ON (e.email_key = ed.email_key AND ed.journal_id = e.journal_id AND ed.locale = ?)
            LEFT JOIN email_templates_default d ON (e.email_key = d.email_key)
         WHERE e.journal_id = ? AND
            d.email_key IS NULL',
         array($locale, $journalId)
      );

      while (!$result->EOF) {
         $row = $result->getRowAssoc(false);
         $emailTemplates[] = &$this->_returnEmailTemplateFromRow($result->GetRowAssoc(false), true);
         $result->moveNext();
      }
      $result->Close();
      unset($result);

      // Sort all templates by email key.
      $compare = create_function('$t1, $t2', 'return strcmp($t1->getEmailKey(), $t2->getEmailKey());');
      usort ($emailTemplates, $compare);

      return $emailTemplates;
   }

   function getInsertEmailId() {
      return $this->getInsertId('email_templates', 'emailId');
   }

   function deleteEmailTemplatesByJournal($journalId) {
      $this->update(
         'DELETE FROM email_templates_data WHERE journal_id = ?', $journalId
      );
      return $this->update(
         'DELETE FROM email_templates WHERE journal_id = ?', $journalId
      );
   }

   function deleteEmailTemplatesByLocale($locale) {
      $this->update(
         'DELETE FROM email_templates_data WHERE locale = ?', $locale
      );
   }

   function deleteDefaultEmailTemplatesByLocale($locale) {
      $this->update(
         'DELETE FROM email_templates_default_data WHERE locale = ?', $locale
      );
   }

   function templateExistsByKey($emailKey, $journalId) {
      $result = &$this->retrieve(
         'SELECT  COUNT(*)
         FROM  email_templates
         WHERE email_key = ? AND
            journal_id = ?',
         array(
            $emailKey,
            $journalId
         )
      );
      if (isset($result->fields[0]) && $result->fields[0] != 0) {
         $result->Close();
         unset($result);
         return true;
      }

      $result->Close();
      unset($result);

      $result = &$this->retrieve(
         'SELECT COUNT(*)
            FROM email_templates_default
            WHERE email_key = ?',
         $emailKey
      );
      if (isset($result->fields[0]) && $result->fields[0] != 0) {
         $returner = true;
      } else {
         $returner = false;
      }

      $result->Close();
      unset($result);

      return $returner;
   }

   function customTemplateExistsByKey($emailKey, $journalId) {
      $result = &$this->retrieve(
         'SELECT  COUNT(*)
         FROM  email_templates e
            LEFT JOIN email_templates_default d ON (e.email_key = d.email_key)
         WHERE e.email_key = ? AND
            d.email_key IS NULL AND
            e.journal_id = ?',
         array(
            $emailKey,
            $journalId
         )
      );
      $returner = (isset($result->fields[0]) && $result->fields[0] != 0);

      $result->Close();
      unset($result);

      return $returner;
   }
}

?>

---