private function getLinkAuthCode(Tx_Newsletter_Domain_Model_Email $email, $url, $isPreview, $isPlainText = false) { global $TYPO3_DB; $url = html_entity_decode($url); // First check in our local cache if (isset($this->linksCache[$url])) { $linkId = $this->linksCache[$url]; } elseif ($isPreview) { $linkId = count($this->linksCache); } else { // Look for the link database, it may already exist $res = $TYPO3_DB->sql_query('SELECT uid FROM tx_newsletter_domain_model_link WHERE url = "' . $url . '" AND newsletter = ' . $this->newsletter->getUid() . ' LIMIT 1'); $row = $TYPO3_DB->sql_fetch_row($res); if ($row) { $linkId = $row[0]; } else { $TYPO3_DB->exec_INSERTquery('tx_newsletter_domain_model_link', array('pid' => $this->newsletter->getPid(), 'url' => $url, 'newsletter' => $this->newsletter->getUid())); $linkId = $TYPO3_DB->sql_insert_id(); } } // Store link in cache $this->linksCache[$url] = $linkId; $authCode = md5($email->getAuthCode() . $linkId); $newUrl = Tx_Newsletter_Tools::buildFrontendUri('clicked', array(), 'Link') . '&url=' . urlencode($url) . '&n=' . $this->newsletter->getUid() . '&l=' . $authCode . ($isPlainText ? '&p=1' : ''); return $newUrl; }
/** * Returns newsletter statistics to be used for pie and timeline chart * We will get the full state for each time when something happened * @param Tx_Newsletter_Domain_Model_Newsletter $newsletter * @return array eg: array(array(time, emailNotSentCount, emailSentCount, emailOpenedCount, emailBouncedCount, emailCount, linkOpenedCount, linkCount, [and same fields but Percentage instead of Count] )) */ public function getStatistics(Tx_Newsletter_Domain_Model_Newsletter $newsletter) { $uidNewsletter = $newsletter->getUid(); $stateDifferences = array(); $emailCount = $this->fillStateDifferences($stateDifferences, 'tx_newsletter_domain_model_email', 'newsletter = ' . $uidNewsletter, array('end_time' => array('increment' => 'emailSentCount', 'decrement' => 'emailNotSentCount'), 'open_time' => array('increment' => 'emailOpenedCount', 'decrement' => 'emailSentCount'), 'bounce_time' => array('increment' => 'emailBouncedCount', 'decrement' => 'emailSentCount'))); $linkRepository = $this->objectManager->get('Tx_Newsletter_Domain_Repository_LinkRepository'); $linkCount = $linkRepository->getCount($uidNewsletter); $this->fillStateDifferences($stateDifferences, 'tx_newsletter_domain_model_link LEFT JOIN tx_newsletter_domain_model_linkopened ON (tx_newsletter_domain_model_linkopened.link = tx_newsletter_domain_model_link.uid)', 'tx_newsletter_domain_model_link.newsletter = ' . $uidNewsletter, array('open_time' => array('increment' => 'linkOpenedCount'))); // Find out the very first event (when the newsletter was planned) $plannedTime = $newsletter ? $newsletter->getPlannedTime() : null; $emailCount = $newsletter ? $newsletter->getEmailCount() : $emailCount; // We re-calculate email count so get correct number if newsletter is not sent yet $previousState = array('time' => $plannedTime ? (int) $plannedTime->format('U') : null, 'emailNotSentCount' => $emailCount, 'emailSentCount' => 0, 'emailOpenedCount' => 0, 'emailBouncedCount' => 0, 'emailCount' => $emailCount, 'linkOpenedCount' => 0, 'linkCount' => $linkCount, 'emailNotSentPercentage' => 100, 'emailSentPercentage' => 0, 'emailOpenedPercentage' => 0, 'emailBouncedPercentage' => 0, 'linkOpenedPercentage' => 0); // Find out what the best grouping step is according to number of states $stateCount = count($stateDifferences); if ($stateCount > 5000) { $groupingTimestep = 15 * 60; } elseif ($stateCount > 500) { $groupingTimestep = 5 * 60; } elseif ($stateCount > 50) { $groupingTimestep = 1 * 60; } else { $groupingTimestep = 0; } // no grouping at all $states = array($previousState); ksort($stateDifferences); $minimumTimeToInsert = 0; // First state must always be not grouped, so we don't increment here foreach ($stateDifferences as $time => $diff) { $newState = $previousState; $newState['time'] = $time; // Apply diff to previous state to get new state's absolute values foreach ($diff as $key => $value) { $newState[$key] += $value; } // Compute percentage for email states foreach (array('emailNotSent', 'emailSent', 'emailOpened', 'emailBounced') as $key) { $newState[$key . 'Percentage'] = $newState[$key . 'Count'] / $newState['emailCount'] * 100; } // Compute percentage for link states if ($newState['linkCount'] && $newState['emailCount']) { $newState['linkOpenedPercentage'] = $newState['linkOpenedCount'] / ($newState['linkCount'] * $newState['emailCount']) * 100; } else { $newState['linkOpenedPercentage'] = 0; } // Insert the state only if grouping allows it if ($time >= $minimumTimeToInsert) { $states[] = $newState; $minimumTimeToInsert = $time + $groupingTimestep; } $previousState = $newState; } // Don't forget to always add the very last state, if not already inserted if (!($time >= $minimumTimeToInsert)) { $states[] = $newState; } return $states; }
/** * Sends an email to the address configured in extension settings when a recipient unsubscribe * @param Tx_Newsletter_Domain_Model_Newsletter $newsletter * @param Tx_Newsletter_Domain_Model_RecipientList $recipientList * @param Tx_Newsletter_Domain_Model_Email $email * @return void */ protected function notifyUnsubscribe($newsletter, $recipientList, Tx_Newsletter_Domain_Model_Email $email) { $notificationEmail = Tx_Newsletter_Tools::confParam('notification_email'); // Use the page-owner as user if ($notificationEmail == 'user') { $rs = $GLOBALS['TYPO3_DB']->sql_query("SELECT email\n\t\t\tFROM be_users\n\t\t\tLEFT JOIN pages ON be_users.uid = pages.perms_userid\n\t\t\tWHERE pages.uid = " . $newsletter->getPid()); list($notificationEmail) = $GLOBALS['TYPO3_DB']->sql_fetch_row($rs); } // If cannot find valid email, don't send any notification if (!\TYPO3\CMS\Core\Utility\GeneralUtility::validEmail($notificationEmail)) { return; } // Build email texts $baseUrl = 'http://' . $newsletter->getDomain(); $urlRecipient = $baseUrl . '/typo3/alt_doc.php?&edit[tx_newsletter_domain_model_email][' . $email->getUid() . ']=edit'; $urlRecipientList = $baseUrl . '/typo3/alt_doc.php?&edit[tx_newsletter_domain_model_recipientlist][' . $recipientList->getUid() . ']=edit'; $urlNewsletter = $baseUrl . '/typo3/alt_doc.php?&edit[tx_newsletter_domain_model_newsletter][' . $newsletter->getUid() . ']=edit'; $subject = Tx_Extbase_Utility_Localization::translate('unsubscribe_notification_subject', 'newsletter'); $body = Tx_Extbase_Utility_Localization::translate('unsubscribe_notification_body', 'newsletter', array($email->getRecipientAddress(), $urlRecipient, $recipientList->getTitle(), $urlRecipientList, $newsletter->getTitle(), $urlNewsletter)); // Actually sends email $message = \TYPO3\CMS\Core\Utility\GeneralUtility::makeInstance('TYPO3\\CMS\\Core\\Mail\\MailMessage'); $message->setTo($notificationEmail)->setFrom(array($newsletter->getSenderEmail() => $newsletter->getSenderName()))->setSubject($subject)->setBody($body, 'text/html'); $message->send(); }
/** * Run the spool from a browser * This has some limitations. No load balance. Different permissions. And should have a mails_per_round-value * * @global \TYPO3\CMS\Core\Database\DatabaseConnection $TYPO3_DB * @return void */ public static function runSpoolOne(Tx_Newsletter_Domain_Model_Newsletter $newsletter) { global $TYPO3_DB; /* Do we any limit to this session? */ if ($mails_per_round = Tx_Newsletter_Tools::confParam('mails_per_round')) { $limit = " LIMIT 0, {$mails_per_round} "; } /* Find the receivers, select userdata, uid of target, uid of page, uid of logrecord */ $rs = $TYPO3_DB->sql_query("SELECT tx_newsletter_domain_model_newsletter.uid, tx_newsletter_domain_model_email.uid\n\t\t\t\t\t\tFROM tx_newsletter_domain_model_email\n\t\t\t\t\t\tLEFT JOIN tx_newsletter_domain_model_newsletter ON (tx_newsletter_domain_model_email.newsletter = tx_newsletter_domain_model_newsletter.uid)\n\t\t\t\t\t\tWHERE tx_newsletter_domain_model_newsletter.uid = " . $newsletter->getUid() . "\n\t\t\t\t\t\tAND tx_newsletter_domain_model_email.begin_time = 0\n\t\t\t\t\t\tORDER BY tx_newsletter_domain_model_email.newsletter " . $limit); /* Do it, if there is any records */ if ($numRows = $TYPO3_DB->sql_num_rows($rs)) { self::runSpool($rs); } return $numRows; }