/** * Get statistics from DB and compile them. * * @param array $row DB record * * @return string Statistics of a mail */ function cmd_stats($row) { if (GeneralUtility::_GP("recalcCache")) { $this->makeStatTempTableContent($row); } $thisurl = BackendUtility::getModuleUrl('txdirectmailM1_txdirectmailM4') . '&id=' . $this->id . '&sys_dmail_uid=' . $row['uid'] . '&CMD=' . $this->CMD . '&recalcCache=1'; $output = $this->directMail_compactView($row); // ***************************** // Mail responses, general: // ***************************** $mailingId = intval($row['uid']); $queryArray = array('response_type,count(*) as counter', 'sys_dmail_maillog', 'mid=' . $mailingId, 'response_type'); $table = $this->getQueryRows($queryArray, 'response_type'); // Plaintext/HTML $res = $GLOBALS["TYPO3_DB"]->exec_SELECTquery('html_sent,count(*) as counter', 'sys_dmail_maillog', 'mid=' . $mailingId . ' AND response_type=0', 'html_sent'); $textHtml = array(); while ($row2 = $GLOBALS["TYPO3_DB"]->sql_fetch_assoc($res)) { // 0:No mail; 1:HTML; 2:TEXT; 3:HTML+TEXT $textHtml[$row2['html_sent']] = $row2['counter']; } $GLOBALS["TYPO3_DB"]->sql_free_result($res); // Unique responses, html $res = $GLOBALS["TYPO3_DB"]->exec_SELECTquery('count(*) as counter', 'sys_dmail_maillog', 'mid=' . $mailingId . ' AND response_type=1', 'rid,rtbl', 'counter'); $uniqueHtmlResponses = $GLOBALS["TYPO3_DB"]->sql_num_rows($res); $GLOBALS["TYPO3_DB"]->sql_free_result($res); // Unique responses, Plain $res = $GLOBALS["TYPO3_DB"]->exec_SELECTquery('count(*) as counter', 'sys_dmail_maillog', 'mid=' . $mailingId . ' AND response_type=2', 'rid,rtbl', 'counter'); $uniquePlainResponses = $GLOBALS["TYPO3_DB"]->sql_num_rows($res); $GLOBALS["TYPO3_DB"]->sql_free_result($res); // Unique responses, pings $res = $GLOBALS["TYPO3_DB"]->exec_SELECTquery('count(*) as counter', 'sys_dmail_maillog', 'mid=' . $mailingId . ' AND response_type=-1', 'rid,rtbl', 'counter'); $uniquePingResponses = $GLOBALS["TYPO3_DB"]->sql_num_rows($res); $GLOBALS["TYPO3_DB"]->sql_free_result($res); $tblLines = array(); $tblLines[] = array('', $this->getLanguageService()->getLL('stats_total'), $this->getLanguageService()->getLL('stats_HTML'), $this->getLanguageService()->getLL('stats_plaintext')); $totalSent = intval($textHtml['1'] + $textHtml['2'] + $textHtml['3']); $htmlSent = intval($textHtml['1'] + $textHtml['3']); $plainSent = intval($textHtml['2']); $tblLines[] = array($this->getLanguageService()->getLL('stats_mails_sent'), $totalSent, $htmlSent, $plainSent); $tblLines[] = array($this->getLanguageService()->getLL('stats_mails_returned'), $this->showWithPercent($table['-127']['counter'], $totalSent)); $tblLines[] = array($this->getLanguageService()->getLL('stats_HTML_mails_viewed'), '', $this->showWithPercent($uniquePingResponses, $htmlSent)); $tblLines[] = array($this->getLanguageService()->getLL('stats_unique_responses'), $this->showWithPercent($uniqueHtmlResponses + $uniquePlainResponses, $totalSent), $this->showWithPercent($uniqueHtmlResponses, $htmlSent), $this->showWithPercent($uniquePlainResponses, $plainSent ? $plainSent : $htmlSent)); $output .= '<br /><h2>' . $this->getLanguageService()->getLL('stats_general_information') . '</h2>'; $output .= DirectMailUtility::formatTable($tblLines, array('nowrap', 'nowrap', 'nowrap', 'nowrap'), 1, array()); // ****************** // Links: // ****************** // initialize $urlCounter $urlCounter = array('total' => array(), 'plain' => array(), 'html' => array()); // Most popular links, html: $queryArray = array('url_id,count(*) as counter', 'sys_dmail_maillog', 'mid=' . intval($row['uid']) . ' AND response_type=1', 'url_id', 'counter'); $htmlUrlsTable = $this->getQueryRows($queryArray, 'url_id'); // Most popular links, plain: $queryArray = array('url_id,count(*) as counter', 'sys_dmail_maillog', 'mid=' . intval($row['uid']) . ' AND response_type=2', 'url_id', 'counter'); $plainUrlsTable = $this->getQueryRows($queryArray, 'url_id'); // Find urls: $unpackedMail = unserialize(base64_decode($row['mailContent'])); // this array will include a unique list of all URLs that are used in the mailing $urlArr = array(); $urlMd5Map = array(); if (is_array($unpackedMail['html']['hrefs'])) { foreach ($unpackedMail['html']['hrefs'] as $k => $v) { // convert & of query params back $urlArr[$k] = html_entity_decode($v['absRef']); $urlMd5Map[md5($v['absRef'])] = $k; } } if (is_array($unpackedMail['plain']['link_ids'])) { foreach ($unpackedMail['plain']['link_ids'] as $k => $v) { $urlArr[intval(-$k)] = $v; } } // Traverse plain urls: $mappedPlainUrlsTable = array(); foreach ($plainUrlsTable as $id => $c) { $url = $urlArr[intval($id)]; if (isset($urlMd5Map[md5($url)])) { $mappedPlainUrlsTable[$urlMd5Map[md5($url)]] = $c; } else { $mappedPlainUrlsTable[$id] = $c; } } $urlCounter['total'] = array(); // Traverse html urls: $urlCounter['html'] = array(); if (count($htmlUrlsTable) > 0) { foreach ($htmlUrlsTable as $id => $c) { $urlCounter['html'][$id]['counter'] = $urlCounter['total'][$id]['counter'] = $c['counter']; } } // Traverse plain urls: $urlCounter['plain'] = array(); foreach ($mappedPlainUrlsTable as $id => $c) { // Look up plain url in html urls $htmlLinkFound = FALSE; foreach ($urlCounter['html'] as $htmlId => $_) { if ($urlArr[$id] == $urlArr[$htmlId]) { $urlCounter['html'][$htmlId]['plainId'] = $id; $urlCounter['html'][$htmlId]['plainCounter'] = $c['counter']; $urlCounter['total'][$htmlId]['counter'] = $urlCounter['total'][$htmlId]['counter'] + $c['counter']; $htmlLinkFound = TRUE; break; } } if (!$htmlLinkFound) { $urlCounter['plain'][$id]['counter'] = $c['counter']; $urlCounter['total'][$id]['counter'] = $urlCounter['total'][$id]['counter'] + $c['counter']; } } $tblLines = array(); $tblLines[] = array('', $this->getLanguageService()->getLL('stats_total'), $this->getLanguageService()->getLL('stats_HTML'), $this->getLanguageService()->getLL('stats_plaintext')); $tblLines[] = array($this->getLanguageService()->getLL('stats_total_responses'), $table['1']['counter'] + $table['2']['counter'], $table['1']['counter'] ? $table['1']['counter'] : '0', $table['2']['counter'] ? $table['2']['counter'] : '0'); $tblLines[] = array($this->getLanguageService()->getLL('stats_unique_responses'), $this->showWithPercent($uniqueHtmlResponses + $uniquePlainResponses, $totalSent), $this->showWithPercent($uniqueHtmlResponses, $htmlSent), $this->showWithPercent($uniquePlainResponses, $plainSent ? $plainSent : $htmlSent)); $tblLines[] = array($this->getLanguageService()->getLL('stats_links_clicked_per_respondent'), $uniqueHtmlResponses + $uniquePlainResponses ? number_format(($table['1']['counter'] + $table['2']['counter']) / ($uniqueHtmlResponses + $uniquePlainResponses), 2) : '-', $uniqueHtmlResponses ? number_format($table['1']['counter'] / $uniqueHtmlResponses, 2) : '-', $uniquePlainResponses ? number_format($table['2']['counter'] / $uniquePlainResponses, 2) : '-'); $output .= '<br /><h2>' . $this->getLanguageService()->getLL('stats_response') . '</h2>'; $output .= DirectMailUtility::formatTable($tblLines, array('nowrap', 'nowrap', 'nowrap', 'nowrap'), 1, array(0, 0, 0, 0)); arsort($urlCounter['total']); arsort($urlCounter['html']); arsort($urlCounter['plain']); reset($urlCounter['total']); $tblLines = array(); $tblLines[] = array('', $this->getLanguageService()->getLL('stats_HTML_link_nr'), $this->getLanguageService()->getLL('stats_plaintext_link_nr'), $this->getLanguageService()->getLL('stats_total'), $this->getLanguageService()->getLL('stats_HTML'), $this->getLanguageService()->getLL('stats_plaintext'), ''); // HTML mails if (intval($row['sendOptions']) & 0x2) { $htmlContent = $unpackedMail['html']['content']; $htmlLinks = array(); if (is_array($unpackedMail['html']['hrefs'])) { foreach ($unpackedMail['html']['hrefs'] as $jumpurlId => $data) { $htmlLinks[$jumpurlId] = array('url' => $data['ref'], 'label' => ''); } } // Parse mail body $dom = new \DOMDocument(); @$dom->loadHTML($htmlContent); $links = array(); // Get all links foreach ($dom->getElementsByTagName('a') as $node) { $links[] = $node; } // Process all links found foreach ($links as $link) { /* @var \DOMElement $link */ $url = $link->getAttribute('href'); if (empty($url)) { // Drop a tags without href continue; } if (GeneralUtility::isFirstPartOfStr($url, 'mailto:')) { // Drop mail links continue; } $parsedUrl = GeneralUtility::explodeUrl2Array($url); if (!array_key_exists('jumpurl', $parsedUrl)) { // Ignore non-jumpurl links continue; } $jumpurlId = $parsedUrl['jumpurl']; $targetUrl = $htmlLinks[$jumpurlId]['url']; $title = $link->getAttribute('title'); if (!empty($title)) { // no title attribute $label = '<span title="' . $title . '">' . GeneralUtility::fixed_lgd_cs(substr($targetUrl, -40), 40) . '</span>'; } else { $label = '<span title="' . $targetUrl . '">' . GeneralUtility::fixed_lgd_cs(substr($targetUrl, -40), 40) . '</span>'; } $htmlLinks[$jumpurlId]['label'] = $label; } } foreach ($urlCounter['total'] as $id => $_) { // $id is the jumpurl ID $origId = $id; $id = abs(intval($id)); $url = $htmlLinks[$id]['url'] ? $htmlLinks[$id]['url'] : $urlArr[$origId]; // a link to this host? $uParts = @parse_url($url); $urlstr = $this->getUrlStr($uParts); $label = $this->getLinkLabel($url, $urlstr, FALSE, $htmlLinks[$id]['label']); $img = '<a href="' . $urlstr . '" target="_blank">' . $this->iconFactory->getIcon('apps-toolbar-menu-search', Icon::SIZE_SMALL) . '</a>'; if (isset($urlCounter['html'][$id]['plainId'])) { $tblLines[] = array($label, $id, $urlCounter['html'][$id]['plainId'], $urlCounter['total'][$origId]['counter'], $urlCounter['html'][$id]['counter'], $urlCounter['html'][$id]['plainCounter'], $img); } else { $html = empty($urlCounter['html'][$id]['counter']) ? 0 : 1; $tblLines[] = array($label, $html ? $id : '-', $html ? '-' : $id, $html ? $urlCounter['html'][$id]['counter'] : $urlCounter['plain'][$origId]['counter'], $urlCounter['html'][$id]['counter'], $urlCounter['plain'][$origId]['counter'], $img); } } // go through all links that were not clicked yet and that have a label $clickedLinks = array_keys($urlCounter['total']); foreach ($urlArr as $id => $link) { if (!in_array($id, $clickedLinks) && isset($htmlLinks['id'])) { // a link to this host? $uParts = @parse_url($link); $urlstr = $this->getUrlStr($uParts); $label = $htmlLinks[$id]['label'] . ' (' . ($urlstr ? $urlstr : '/') . ')'; $img = '<a href="' . htmlspecialchars($link) . '" target="_blank">' . $this->iconFactory->getIcon('apps-toolbar-menu-search', Icon::SIZE_SMALL) . '</a>'; $tblLines[] = array($label, $html ? $id : '-', $html ? '-' : abs($id), $html ? $urlCounter['html'][$id]['counter'] : $urlCounter['plain'][$id]['counter'], $urlCounter['html'][$id]['counter'], $urlCounter['plain'][$id]['counter'], $img); } } if ($urlCounter['total']) { $output .= '<br /><h2>' . $this->getLanguageService()->getLL('stats_response_link') . '</h2>'; $output .= DirectMailUtility::formatTable($tblLines, array('nowrap', 'nowrap width="100"', 'nowrap width="100"', 'nowrap', 'nowrap', 'nowrap', 'nowrap'), 1, array(1, 0, 0, 0, 0, 0, 1)); } // ****************** // Returned mails // ****************** // The icons: $listIcons = $this->iconFactory->getIcon('actions-system-list-open', Icon::SIZE_SMALL); $csvIcons = $this->iconFactory->getIcon('actions-document-export-csv', Icon::SIZE_SMALL); if (ExtensionManagementUtility::isLoaded('tt_address')) { $iconPath = ExtensionManagementUtility::extRelPath('tt_address') . 'ext_icon__h.gif'; $iconParam = 'width="18" height="16"'; } else { $iconPath = 'gfx/button_hide.gif'; $iconParam = 'width="11" height="10"'; } $hideIcons = '<img ' . IconUtility::skinImg($GLOBALS["BACK_PATH"], $iconPath, $iconParam . ' alt=""') . ' />'; // Icons mails returned $iconsMailReturned[] = '<a href="' . $thisurl . '&returnList=1" class="bubble"><span class="help" title="' . $this->getLanguageService()->getLL('stats_list_returned') . '"> ' . $listIcons . '</span></a>'; $iconsMailReturned[] = '<a href="' . $thisurl . '&returnDisable=1" class="bubble"><span class="help" title="' . $this->getLanguageService()->getLL('stats_disable_returned') . '"> ' . $hideIcons . '</span></a>'; $iconsMailReturned[] = '<a href="' . $thisurl . '&returnCSV=1" class="bubble"><span class="help" title="' . $this->getLanguageService()->getLL('stats_CSV_returned') . '"> ' . $csvIcons . '</span></a>'; // Icons unknown recip $iconsUnknownRecip[] = '<a href="' . $thisurl . '&unknownList=1" class="bubble"><span class="help" title="' . $this->getLanguageService()->getLL('stats_list_returned_unknown_recipient') . '"> ' . $listIcons . '</span></a>'; $iconsUnknownRecip[] = '<a href="' . $thisurl . '&unknownDisable=1" class="bubble"><span class="help" title="' . $this->getLanguageService()->getLL('stats_disable_returned_unknown_recipient') . '"> ' . $hideIcons . '</span></a>'; $iconsUnknownRecip[] = '<a href="' . $thisurl . '&unknownCSV=1" class="bubble"><span class="help" title="' . $this->getLanguageService()->getLL('stats_CSV_returned_unknown_recipient') . '"> ' . $csvIcons . '</span></a>'; // Icons mailbox full $iconsMailbox[] = '<a href="' . $thisurl . '&fullList=1" class="bubble"><span class="help" title="' . $this->getLanguageService()->getLL('stats_list_returned_mailbox_full') . '"> ' . $listIcons . '</span></a>'; $iconsMailbox[] = '<a href="' . $thisurl . '&fullDisable=1" class="bubble"><span class="help" title="' . $this->getLanguageService()->getLL('stats_disable_returned_mailbox_full') . '"> ' . $hideIcons . '</span></a>'; $iconsMailbox[] = '<a href="' . $thisurl . '&fullCSV=1" class="bubble"><span class="help" title="' . $this->getLanguageService()->getLL('stats_CSV_returned_mailbox_full') . '"> ' . $csvIcons . '</span></a>'; // Icons bad host $iconsBadhost[] = '<a href="' . $thisurl . '&badHostList=1" class="bubble"><span class="help" title="' . $this->getLanguageService()->getLL('stats_list_returned_bad_host') . '"> ' . $listIcons . '</span></a>'; $iconsBadhost[] = '<a href="' . $thisurl . '&badHostDisable=1" class="bubble"><span class="help" title="' . $this->getLanguageService()->getLL('stats_disable_returned_bad_host') . '"> ' . $hideIcons . '</span></a>'; $iconsBadhost[] = '<a href="' . $thisurl . '&badHostCSV=1" class="bubble"><span class="help" title="' . $this->getLanguageService()->getLL('stats_CSV_returned_bad_host') . '"> ' . $csvIcons . '</span></a>'; // Icons bad header $iconsBadheader[] = '<a href="' . $thisurl . '&badHeaderList=1" class="bubble"><span class="help" title="' . $this->getLanguageService()->getLL('stats_list_returned_bad_header') . '"> ' . $listIcons . '</span></a>'; $iconsBadheader[] = '<a href="' . $thisurl . '&badHeaderDisable=1" class="bubble"><span class="help" title="' . $this->getLanguageService()->getLL('stats_disable_returned_bad_header') . '"> ' . $hideIcons . '</span></a>'; $iconsBadheader[] = '<a href="' . $thisurl . '&badHeaderCSV=1" class="bubble"><span class="help" title="' . $this->getLanguageService()->getLL('stats_CSV_returned_bad_header') . '"> ' . $csvIcons . '</span></a>'; // Icons unknown reasons // TODO: link to show all reason $iconsUnknownReason[] = '<a href="' . $thisurl . '&reasonUnknownList=1" class="bubble"><span class="help" title="' . $this->getLanguageService()->getLL('stats_list_returned_reason_unknown') . '"> ' . $listIcons . '</span></a>'; $iconsUnknownReason[] = '<a href="' . $thisurl . '&reasonUnknownDisable=1" class="bubble"><span class="help" title="' . $this->getLanguageService()->getLL('stats_disable_returned_reason_unknown') . '"> ' . $hideIcons . '</span></a>'; $iconsUnknownReason[] = '<a href="' . $thisurl . '&reasonUnknownCSV=1" class="bubble"><span class="help" title="' . $this->getLanguageService()->getLL('stats_CSV_returned_reason_unknown') . '"> ' . $csvIcons . '</span></a>'; // Table with Icon $queryArray = array('count(*) as counter,return_code', 'sys_dmail_maillog', 'mid=' . intval($row['uid']) . ' AND response_type=-127', 'return_code'); $responseResult = $this->getQueryRows($queryArray, 'return_code'); $tblLines = array(); $tblLines[] = array('', $this->getLanguageService()->getLL('stats_count'), ''); $tblLines[] = array($this->getLanguageService()->getLL('stats_total_mails_returned'), $table['-127']['counter'] ? number_format(intval($table['-127']['counter'])) : '0', implode(' ', $iconsMailReturned)); $tblLines[] = array($this->getLanguageService()->getLL('stats_recipient_unknown'), $this->showWithPercent($responseResult['550']['counter'] + $responseResult['553']['counter'], $table['-127']['counter']), implode(' ', $iconsUnknownRecip)); $tblLines[] = array($this->getLanguageService()->getLL('stats_mailbox_full'), $this->showWithPercent($responseResult['551']['counter'], $table['-127']['counter']), implode(' ', $iconsMailbox)); $tblLines[] = array($this->getLanguageService()->getLL('stats_bad_host'), $this->showWithPercent($responseResult['552']['counter'], $table['-127']['counter']), implode(' ', $iconsBadhost)); $tblLines[] = array($this->getLanguageService()->getLL('stats_error_in_header'), $this->showWithPercent($responseResult['554']['counter'], $table['-127']['counter']), implode(' ', $iconsBadheader)); $tblLines[] = array($this->getLanguageService()->getLL('stats_reason_unkown'), $this->showWithPercent($responseResult['-1']['counter'], $table['-127']['counter']), implode(' ', $iconsUnknownReason)); $output .= '<br /><h2>' . $this->getLanguageService()->getLL('stats_mails_returned') . '</h2>'; $output .= DirectMailUtility::formatTable($tblLines, array('nowrap', 'nowrap', ''), 1, array(0, 0, 1)); // Find all returned mail if (GeneralUtility::_GP('returnList') || GeneralUtility::_GP('returnDisable') || GeneralUtility::_GP('returnCSV')) { $res = $GLOBALS["TYPO3_DB"]->exec_SELECTquery('rid,rtbl,email', 'sys_dmail_maillog', 'mid=' . intval($row['uid']) . ' AND response_type=-127'); $idLists = array(); while ($rrow = $GLOBALS["TYPO3_DB"]->sql_fetch_assoc($res)) { switch ($rrow['rtbl']) { case 't': $idLists['tt_address'][] = $rrow['rid']; break; case 'f': $idLists['fe_users'][] = $rrow['rid']; break; case 'P': $idLists['PLAINLIST'][] = $rrow['email']; break; default: $idLists[$rrow['rtbl']][] = $rrow['rid']; } } if (GeneralUtility::_GP('returnList')) { if (is_array($idLists['tt_address'])) { $output .= '<h3>' . $this->getLanguageService()->getLL('stats_emails') . '</h3>' . DirectMailUtility::getRecordList(DirectMailUtility::fetchRecordsListValues($idLists['tt_address'], 'tt_address'), 'tt_address', $this->id, 1, $this->sys_dmail_uid); } if (is_array($idLists['fe_users'])) { $output .= '<h3>' . $this->getLanguageService()->getLL('stats_website_users') . '</h3>' . DirectMailUtility::getRecordList(DirectMailUtility::fetchRecordsListValues($idLists['fe_users'], 'fe_users'), 'fe_users', $this->id, 1, $this->sys_dmail_uid); } if (is_array($idLists['PLAINLIST'])) { $output .= '<h3>' . $this->getLanguageService()->getLL('stats_plainlist') . '</h3>'; $output .= '<ul><li>' . join('</li><li>', $idLists['PLAINLIST']) . '</li></ul>'; } } if (GeneralUtility::_GP('returnDisable')) { if (is_array($idLists['tt_address'])) { $c = $this->disableRecipients(DirectMailUtility::fetchRecordsListValues($idLists['tt_address'], 'tt_address'), 'tt_address'); $output .= '<br />' . $c . ' ' . $this->getLanguageService()->getLL('stats_emails_disabled'); } if (is_array($idLists['fe_users'])) { $c = $this->disableRecipients(DirectMailUtility::fetchRecordsListValues($idLists['fe_users'], 'fe_users'), 'fe_users'); $output .= '<br />' . $c . ' ' . $this->getLanguageService()->getLL('stats_website_users_disabled'); } } if (GeneralUtility::_GP('returnCSV')) { $emails = array(); if (is_array($idLists['tt_address'])) { $arr = DirectMailUtility::fetchRecordsListValues($idLists['tt_address'], 'tt_address'); foreach ($arr as $v) { $emails[] = $v['email']; } } if (is_array($idLists['fe_users'])) { $arr = DirectMailUtility::fetchRecordsListValues($idLists['fe_users'], 'fe_users'); foreach ($arr as $v) { $emails[] = $v['email']; } } if (is_array($idLists['PLAINLIST'])) { $emails = array_merge($emails, $idLists['PLAINLIST']); } $output .= '<br />' . $this->getLanguageService()->getLL('stats_emails_returned_list') . '<br />'; $output .= '<textarea' . $this->doc->formWidth() . ' rows="6" name="nothing">' . LF . htmlspecialchars(implode(LF, $emails)) . '</textarea>'; } } // Find Unknown Recipient if (GeneralUtility::_GP('unknownList') || GeneralUtility::_GP('unknownDisable') || GeneralUtility::_GP('unknownCSV')) { $res = $GLOBALS["TYPO3_DB"]->exec_SELECTquery('rid,rtbl,email', 'sys_dmail_maillog', 'mid=' . intval($row['uid']) . ' AND response_type=-127' . ' AND (return_code=550 OR return_code=553)'); $idLists = array(); while ($rrow = $GLOBALS["TYPO3_DB"]->sql_fetch_assoc($res)) { switch ($rrow['rtbl']) { case 't': $idLists['tt_address'][] = $rrow['rid']; break; case 'f': $idLists['fe_users'][] = $rrow['rid']; break; case 'P': $idLists['PLAINLIST'][] = $rrow['email']; break; default: $idLists[$rrow['rtbl']][] = $rrow['rid']; } } if (GeneralUtility::_GP('unknownList')) { if (is_array($idLists['tt_address'])) { $output .= '<br />' . $this->getLanguageService()->getLL('stats_emails') . '<br />' . DirectMailUtility::getRecordList(DirectMailUtility::fetchRecordsListValues($idLists['tt_address'], 'tt_address'), 'tt_address', $this->id, 1, $this->sys_dmail_uid); } if (is_array($idLists['fe_users'])) { $output .= '<br />' . $this->getLanguageService()->getLL('stats_website_users') . DirectMailUtility::getRecordList(DirectMailUtility::fetchRecordsListValues($idLists['fe_users'], 'fe_users'), 'fe_users', $this->id, 1, $this->sys_dmail_uid); } if (is_array($idLists['PLAINLIST'])) { $output .= '<br />' . $this->getLanguageService()->getLL('stats_plainlist'); $output .= '<ul><li>' . join('</li><li>', $idLists['PLAINLIST']) . '</li></ul>'; } } if (GeneralUtility::_GP('unknownDisable')) { if (is_array($idLists['tt_address'])) { $c = $this->disableRecipients(DirectMailUtility::fetchRecordsListValues($idLists['tt_address'], 'tt_address'), 'tt_address'); $output .= '<br />' . $c . ' ' . $this->getLanguageService()->getLL('stats_emails_disabled'); } if (is_array($idLists['fe_users'])) { $c = $this->disableRecipients(DirectMailUtility::fetchRecordsListValues($idLists['fe_users'], 'fe_users'), 'fe_users'); $output .= '<br />' . $c . ' ' . $this->getLanguageService()->getLL('stats_website_users_disabled'); } } if (GeneralUtility::_GP('unknownCSV')) { $emails = array(); if (is_array($idLists['tt_address'])) { $arr = DirectMailUtility::fetchRecordsListValues($idLists['tt_address'], 'tt_address'); foreach ($arr as $v) { $emails[] = $v['email']; } } if (is_array($idLists['fe_users'])) { $arr = DirectMailUtility::fetchRecordsListValues($idLists['fe_users'], 'fe_users'); foreach ($arr as $v) { $emails[] = $v['email']; } } if (is_array($idLists['PLAINLIST'])) { $emails = array_merge($emails, $idLists['PLAINLIST']); } $output .= '<br />' . $this->getLanguageService()->getLL('stats_emails_returned_unknown_recipient_list') . '<br />'; $output .= '<textarea' . $this->doc->formWidth() . ' rows="6" name="nothing">' . LF . htmlspecialchars(implode(LF, $emails)) . '</textarea>'; } } // Mailbox Full if (GeneralUtility::_GP('fullList') || GeneralUtility::_GP('fullDisable') || GeneralUtility::_GP('fullCSV')) { $res = $GLOBALS["TYPO3_DB"]->exec_SELECTquery('rid,rtbl,email', 'sys_dmail_maillog', 'mid=' . intval($row['uid']) . ' AND response_type=-127' . ' AND return_code=551'); $idLists = array(); while ($rrow = $GLOBALS["TYPO3_DB"]->sql_fetch_assoc($res)) { switch ($rrow['rtbl']) { case 't': $idLists['tt_address'][] = $rrow['rid']; break; case 'f': $idLists['fe_users'][] = $rrow['rid']; break; case 'P': $idLists['PLAINLIST'][] = $rrow['email']; break; default: $idLists[$rrow['rtbl']][] = $rrow['rid']; } } if (GeneralUtility::_GP('fullList')) { if (is_array($idLists['tt_address'])) { $output .= '<br />' . $this->getLanguageService()->getLL('stats_emails') . '<br />' . DirectMailUtility::getRecordList(DirectMailUtility::fetchRecordsListValues($idLists['tt_address'], 'tt_address'), 'tt_address', $this->id, 1, $this->sys_dmail_uid); } if (is_array($idLists['fe_users'])) { $output .= '<br />' . $this->getLanguageService()->getLL('stats_website_users') . DirectMailUtility::getRecordList(DirectMailUtility::fetchRecordsListValues($idLists['fe_users'], 'fe_users'), 'fe_users', $this->id, 1, $this->sys_dmail_uid); } if (is_array($idLists['PLAINLIST'])) { $output .= '<br />' . $this->getLanguageService()->getLL('stats_plainlist'); $output .= '<ul><li>' . join('</li><li>', $idLists['PLAINLIST']) . '</li></ul>'; } } if (GeneralUtility::_GP('fullDisable')) { if (is_array($idLists['tt_address'])) { $c = $this->disableRecipients(DirectMailUtility::fetchRecordsListValues($idLists['tt_address'], 'tt_address'), 'tt_address'); $output .= '<br />' . $c . ' ' . $this->getLanguageService()->getLL('stats_emails_disabled'); } if (is_array($idLists['fe_users'])) { $c = $this->disableRecipients(DirectMailUtility::fetchRecordsListValues($idLists['fe_users'], 'fe_users'), 'fe_users'); $output .= '<br />' . $c . ' ' . $this->getLanguageService()->getLL('stats_website_users_disabled'); } } if (GeneralUtility::_GP('fullCSV')) { $emails = array(); if (is_array($idLists['tt_address'])) { $arr = DirectMailUtility::fetchRecordsListValues($idLists['tt_address'], 'tt_address'); foreach ($arr as $v) { $emails[] = $v['email']; } } if (is_array($idLists['fe_users'])) { $arr = DirectMailUtility::fetchRecordsListValues($idLists['fe_users'], 'fe_users'); foreach ($arr as $v) { $emails[] = $v['email']; } } if (is_array($idLists['PLAINLIST'])) { $emails = array_merge($emails, $idLists['PLAINLIST']); } $output .= '<br />' . $this->getLanguageService()->getLL('stats_emails_returned_mailbox_full_list') . '<br />'; $output .= '<textarea' . $this->doc->formWidth() . ' rows="6" name="nothing">' . LF . htmlspecialchars(implode(LF, $emails)) . '</textarea>'; } } // find Bad Host if (GeneralUtility::_GP('badHostList') || GeneralUtility::_GP('badHostDisable') || GeneralUtility::_GP('badHostCSV')) { $res = $GLOBALS["TYPO3_DB"]->exec_SELECTquery('rid,rtbl,email', 'sys_dmail_maillog', 'mid=' . intval($row['uid']) . ' AND response_type=-127' . ' AND return_code=552'); $idLists = array(); while ($rrow = $GLOBALS["TYPO3_DB"]->sql_fetch_assoc($res)) { switch ($rrow['rtbl']) { case 't': $idLists['tt_address'][] = $rrow['rid']; break; case 'f': $idLists['fe_users'][] = $rrow['rid']; break; case 'P': $idLists['PLAINLIST'][] = $rrow['email']; break; default: $idLists[$rrow['rtbl']][] = $rrow['rid']; } } if (GeneralUtility::_GP('badHostList')) { if (is_array($idLists['tt_address'])) { $output .= '<br />' . $this->getLanguageService()->getLL('stats_emails') . '<br />' . DirectMailUtility::getRecordList(DirectMailUtility::fetchRecordsListValues($idLists['tt_address'], 'tt_address'), 'tt_address', $this->id, 1, $this->sys_dmail_uid); } if (is_array($idLists['fe_users'])) { $output .= '<br />' . $this->getLanguageService()->getLL('stats_website_users') . DirectMailUtility::getRecordList(DirectMailUtility::fetchRecordsListValues($idLists['fe_users'], 'fe_users'), 'fe_users', $this->id, 1, $this->sys_dmail_uid); } if (is_array($idLists['PLAINLIST'])) { $output .= '<br />' . $this->getLanguageService()->getLL('stats_plainlist'); $output .= '<ul><li>' . join('</li><li>', $idLists['PLAINLIST']) . '</li></ul>'; } } if (GeneralUtility::_GP('badHostDisable')) { if (is_array($idLists['tt_address'])) { $c = $this->disableRecipients(DirectMailUtility::fetchRecordsListValues($idLists['tt_address'], 'tt_address'), 'tt_address'); $output .= '<br />' . $c . ' ' . $this->getLanguageService()->getLL('stats_emails_disabled'); } if (is_array($idLists['fe_users'])) { $c = $this->disableRecipients(DirectMailUtility::fetchRecordsListValues($idLists['fe_users'], 'fe_users'), 'fe_users'); $output .= '<br />' . $c . ' ' . $this->getLanguageService()->getLL('stats_website_users_disabled'); } } if (GeneralUtility::_GP('badHostCSV')) { $emails = array(); if (is_array($idLists['tt_address'])) { $arr = DirectMailUtility::fetchRecordsListValues($idLists['tt_address'], 'tt_address'); foreach ($arr as $v) { $emails[] = $v['email']; } } if (is_array($idLists['fe_users'])) { $arr = DirectMailUtility::fetchRecordsListValues($idLists['fe_users'], 'fe_users'); foreach ($arr as $v) { $emails[] = $v['email']; } } if (is_array($idLists['PLAINLIST'])) { $emails = array_merge($emails, $idLists['PLAINLIST']); } $output .= '<br />' . $this->getLanguageService()->getLL('stats_emails_returned_bad_host_list') . '<br />'; $output .= '<textarea' . $this->doc->formWidth() . ' rows="6" name="nothing">' . LF . htmlspecialchars(implode(LF, $emails)) . '</textarea>'; } } // find Bad Header if (GeneralUtility::_GP('badHeaderList') || GeneralUtility::_GP('badHeaderDisable') || GeneralUtility::_GP('badHeaderCSV')) { $res = $GLOBALS["TYPO3_DB"]->exec_SELECTquery('rid,rtbl,email', 'sys_dmail_maillog', 'mid=' . intval($row['uid']) . ' AND response_type=-127' . ' AND return_code=554'); $idLists = array(); while ($rrow = $GLOBALS["TYPO3_DB"]->sql_fetch_assoc($res)) { switch ($rrow['rtbl']) { case 't': $idLists['tt_address'][] = $rrow['rid']; break; case 'f': $idLists['fe_users'][] = $rrow['rid']; break; case 'P': $idLists['PLAINLIST'][] = $rrow['email']; break; default: $idLists[$rrow['rtbl']][] = $rrow['rid']; } } if (GeneralUtility::_GP('badHeaderList')) { if (is_array($idLists['tt_address'])) { $output .= '<br />' . $this->getLanguageService()->getLL('stats_emails') . '<br />' . DirectMailUtility::getRecordList(DirectMailUtility::fetchRecordsListValues($idLists['tt_address'], 'tt_address'), 'tt_address', $this->id, 1, $this->sys_dmail_uid); } if (is_array($idLists['fe_users'])) { $output .= '<br />' . $this->getLanguageService()->getLL('stats_website_users') . DirectMailUtility::getRecordList(DirectMailUtility::fetchRecordsListValues($idLists['fe_users'], 'fe_users'), 'fe_users', $this->id, 1, $this->sys_dmail_uid); } if (is_array($idLists['PLAINLIST'])) { $output .= '<br />' . $this->getLanguageService()->getLL('stats_plainlist'); $output .= '<ul><li>' . join('</li><li>', $idLists['PLAINLIST']) . '</li></ul>'; } } if (GeneralUtility::_GP('badHeaderDisable')) { if (is_array($idLists['tt_address'])) { $c = $this->disableRecipients(DirectMailUtility::fetchRecordsListValues($idLists['tt_address'], 'tt_address'), 'tt_address'); $output .= '<br />' . $c . ' ' . $this->getLanguageService()->getLL('stats_emails_disabled'); } if (is_array($idLists['fe_users'])) { $c = $this->disableRecipients(DirectMailUtility::fetchRecordsListValues($idLists['fe_users'], 'fe_users'), 'fe_users'); $output .= '<br />' . $c . ' ' . $this->getLanguageService()->getLL('stats_website_users_disabled'); } } if (GeneralUtility::_GP('badHeaderCSV')) { $emails = array(); if (is_array($idLists['tt_address'])) { $arr = DirectMailUtility::fetchRecordsListValues($idLists['tt_address'], 'tt_address'); foreach ($arr as $v) { $emails[] = $v['email']; } } if (is_array($idLists['fe_users'])) { $arr = DirectMailUtility::fetchRecordsListValues($idLists['fe_users'], 'fe_users'); foreach ($arr as $v) { $emails[] = $v['email']; } } if (is_array($idLists['PLAINLIST'])) { $emails = array_merge($emails, $idLists['PLAINLIST']); } $output .= '<br />' . $this->getLanguageService()->getLL('stats_emails_returned_bad_header_list') . '<br />'; $output .= '<textarea' . $this->doc->formWidth() . ' rows="6" name="nothing">' . LF . htmlspecialchars(implode(LF, $emails)) . '</textarea>'; } } // find Unknown Reasons // TODO: list all reason if (GeneralUtility::_GP('reasonUnknownList') || GeneralUtility::_GP('reasonUnknownDisable') || GeneralUtility::_GP('reasonUnknownCSV')) { $res = $GLOBALS["TYPO3_DB"]->exec_SELECTquery('rid,rtbl,email', 'sys_dmail_maillog', 'mid=' . intval($row['uid']) . ' AND response_type=-127' . ' AND return_code=-1'); $idLists = array(); while ($rrow = $GLOBALS["TYPO3_DB"]->sql_fetch_assoc($res)) { switch ($rrow['rtbl']) { case 't': $idLists['tt_address'][] = $rrow['rid']; break; case 'f': $idLists['fe_users'][] = $rrow['rid']; break; case 'P': $idLists['PLAINLIST'][] = $rrow['email']; break; default: $idLists[$rrow['rtbl']][] = $rrow['rid']; } } if (GeneralUtility::_GP('reasonUnknownList')) { if (is_array($idLists['tt_address'])) { $output .= '<br />' . $this->getLanguageService()->getLL('stats_emails') . '<br />' . DirectMailUtility::getRecordList(DirectMailUtility::fetchRecordsListValues($idLists['tt_address'], 'tt_address'), 'tt_address', $this->id, 1, $this->sys_dmail_uid); } if (is_array($idLists['fe_users'])) { $output .= '<br />' . $this->getLanguageService()->getLL('stats_website_users') . DirectMailUtility::getRecordList(DirectMailUtility::fetchRecordsListValues($idLists['fe_users'], 'fe_users'), 'fe_users', $this->id, 1, $this->sys_dmail_uid); } if (is_array($idLists['PLAINLIST'])) { $output .= '<br />' . $this->getLanguageService()->getLL('stats_plainlist'); $output .= '<ul><li>' . join('</li><li>', $idLists['PLAINLIST']) . '</li></ul>'; } } if (GeneralUtility::_GP('reasonUnknownDisable')) { if (is_array($idLists['tt_address'])) { $c = $this->disableRecipients(DirectMailUtility::fetchRecordsListValues($idLists['tt_address'], 'tt_address'), 'tt_address'); $output .= '<br />' . $c . ' ' . $this->getLanguageService()->getLL('stats_emails_disabled'); } if (is_array($idLists['fe_users'])) { $c = $this->disableRecipients(DirectMailUtility::fetchRecordsListValues($idLists['fe_users'], 'fe_users'), 'fe_users'); $output .= '<br />' . $c . ' ' . $this->getLanguageService()->getLL('stats_website_users_disabled'); } } if (GeneralUtility::_GP('reasonUnknownCSV')) { $emails = array(); if (is_array($idLists['tt_address'])) { $arr = DirectMailUtility::fetchRecordsListValues($idLists['tt_address'], 'tt_address'); foreach ($arr as $v) { $emails[] = $v['email']; } } if (is_array($idLists['fe_users'])) { $arr = DirectMailUtility::fetchRecordsListValues($idLists['fe_users'], 'fe_users'); foreach ($arr as $v) { $emails[] = $v['email']; } } if (is_array($idLists['PLAINLIST'])) { $emails = array_merge($emails, $idLists['PLAINLIST']); } $output .= '<br />' . $this->getLanguageService()->getLL('stats_emails_returned_reason_unknown_list') . '<br />'; $output .= '<textarea' . $this->doc->formWidth() . ' rows="6" name="nothing">' . LF . htmlspecialchars(implode(LF, $emails)) . '</textarea>'; } } /** * Hook for cmd_stats_postProcess * insert a link to open extended importer */ if (is_array($GLOBALS['TYPO3_CONF_VARS']['EXTCONF']['direct_mail']['mod4']['cmd_stats'])) { $hookObjectsArr = array(); foreach ($GLOBALS['TYPO3_CONF_VARS']['EXTCONF']['direct_mail']['mod4']['cmd_stats'] as $classRef) { $hookObjectsArr[] =& GeneralUtility::getUserObj($classRef); } // assigned $output to class property to make it acesssible inside hook $this->output = $output; // and clear the former $output to collect hoot return code there $output = ''; foreach ($hookObjectsArr as $hookObj) { if (method_exists($hookObj, 'cmd_stats_postProcess')) { $output .= $hookObj->cmd_stats_postProcess($row, $this); } } } $this->noView = 1; // put all the stats tables in a section $theOutput = $this->doc->section($this->getLanguageService()->getLL('stats_direct_mail'), $output, 1, 1, 0, TRUE); $theOutput .= '<div style="padding-top: 20px;"></div>'; $link = '<p><a style="text-decoration: underline;" href="' . $thisurl . '">' . $this->getLanguageService()->getLL('stats_recalculate_stats') . '</a></p>'; $theOutput .= $this->doc->section($this->getLanguageService()->getLL('stats_recalculate_cached_data'), $link, 1, 1, 0, TRUE); return $theOutput; }
/** * Display infos of the mail group * * @param array $result Array containing list of recipient uid * * @return string list of all recipient (HTML) */ public function cmd_displayMailGroup($result) { $totalRecipients = 0; $idLists = $result['queryInfo']['id_lists']; if (is_array($idLists['tt_address'])) { $totalRecipients += count($idLists['tt_address']); } if (is_array($idLists['fe_users'])) { $totalRecipients += count($idLists['fe_users']); } if (is_array($idLists['PLAINLIST'])) { $totalRecipients += count($idLists['PLAINLIST']); } if (is_array($idLists[$this->userTable])) { $totalRecipients += count($idLists[$this->userTable]); } $group = BackendUtility::getRecord('sys_dmail_group', intval(GeneralUtility::_GP('group_uid'))); $out = $this->iconFactory->getIconForRecord('sys_dmail_group', $group, Icon::SIZE_SMALL) . htmlspecialchars($group['title']); $lCmd = GeneralUtility::_GP('lCmd'); $mainC = $this->getLanguageService()->getLL('mailgroup_recip_number') . ' <strong>' . $totalRecipients . '</strong>'; if (!$lCmd) { $mainC .= '<br /><br /><strong><a class="t3-link" href="' . GeneralUtility::linkThisScript(array('lCmd' => 'listall')) . '">' . $this->getLanguageService()->getLL('mailgroup_list_all') . '</a></strong>'; } $theOutput = $this->doc->section($this->getLanguageService()->getLL('mailgroup_recip_from') . ' ' . $out, $mainC, 1, 1, 0, true); $theOutput .= '<div style="padding-top: 20px;"></div>'; // do the CSV export $csvValue = GeneralUtility::_GP('csv'); if ($csvValue) { if ($csvValue == 'PLAINLIST') { $this->downloadCSV($idLists['PLAINLIST']); } elseif (GeneralUtility::inList('tt_address,fe_users,' . $this->userTable, $csvValue)) { $this->downloadCSV(DirectMailUtility::fetchRecordsListValues($idLists[$csvValue], $csvValue, ($csvValue == 'fe_users' ? str_replace('phone', 'telephone', $this->fieldList) : $this->fieldList) . ',tstamp')); } } switch ($lCmd) { case 'listall': if (is_array($idLists['tt_address'])) { $theOutput .= $this->doc->section($this->getLanguageService()->getLL('mailgroup_table_address'), DirectMailUtility::getRecordList(DirectMailUtility::fetchRecordsListValues($idLists['tt_address'], 'tt_address'), 'tt_address', $this->id)); $theOutput .= '<div style="padding-top: 20px;"></div>'; } if (is_array($idLists['fe_users'])) { $theOutput .= $this->doc->section($this->getLanguageService()->getLL('mailgroup_table_fe_users'), DirectMailUtility::getRecordList(DirectMailUtility::fetchRecordsListValues($idLists['fe_users'], 'fe_users'), 'fe_users', $this->id)); $theOutput .= '<div style="padding-top: 20px;"></div>'; } if (is_array($idLists['PLAINLIST'])) { $theOutput .= $this->doc->section($this->getLanguageService()->getLL('mailgroup_plain_list'), DirectMailUtility::getRecordList($idLists['PLAINLIST'], 'default', $this->id)); $theOutput .= '<div style="padding-top: 20px;"></div>'; } if (is_array($idLists[$this->userTable])) { $theOutput .= $this->doc->section($this->getLanguageService()->getLL('mailgroup_table_custom') . ' ' . $this->userTable, DirectMailUtility::getRecordList(DirectMailUtility::fetchRecordsListValues($idLists[$this->userTable], $this->userTable), $this->userTable, $this->id)); } break; default: if (is_array($idLists['tt_address']) && count($idLists['tt_address'])) { $recipContent = $this->getLanguageService()->getLL('mailgroup_recip_number') . ' ' . count($idLists['tt_address']) . '<br /><a href="' . GeneralUtility::linkThisScript(array('csv' => 'tt_address')) . '" class="t3-link">' . $this->getLanguageService()->getLL('mailgroup_download') . '</a>'; $theOutput .= $this->doc->section($this->getLanguageService()->getLL('mailgroup_table_address'), $recipContent); $theOutput .= '<div style="padding-top: 20px;"></div>'; } if (is_array($idLists['fe_users']) && count($idLists['fe_users'])) { $recipContent = $this->getLanguageService()->getLL('mailgroup_recip_number') . ' ' . count($idLists['fe_users']) . '<br /><a href="' . GeneralUtility::linkThisScript(array('csv' => 'fe_users')) . '" class="t3-link">' . $this->getLanguageService()->getLL('mailgroup_download') . '</a>'; $theOutput .= $this->doc->section($this->getLanguageService()->getLL('mailgroup_table_fe_users'), $recipContent); $theOutput .= '<div style="padding-top: 20px;"></div>'; } if (is_array($idLists['PLAINLIST']) && count($idLists['PLAINLIST'])) { $recipContent = $this->getLanguageService()->getLL('mailgroup_recip_number') . ' ' . count($idLists['PLAINLIST']) . '<br /><a href="' . GeneralUtility::linkThisScript(array('csv' => 'PLAINLIST')) . '" class="t3-link">' . $this->getLanguageService()->getLL('mailgroup_download') . '</a>'; $theOutput .= $this->doc->section($this->getLanguageService()->getLL('mailgroup_plain_list'), $recipContent); $theOutput .= '<div style="padding-top: 20px;"></div>'; } if (is_array($idLists[$this->userTable]) && count($idLists[$this->userTable])) { $recipContent = $this->getLanguageService()->getLL('mailgroup_recip_number') . ' ' . count($idLists[$this->userTable]) . '<br /><a href="' . GeneralUtility::linkThisScript(array('csv' => $this->userTable)) . '" class="t3-link">' . $this->getLanguageService()->getLL('mailgroup_download') . '</a>'; $theOutput .= $this->doc->section($this->getLanguageService()->getLL('mailgroup_table_custom'), $recipContent); $theOutput .= '<div style="padding-top: 20px;"></div>'; } if ($group['type'] == 3) { if ($GLOBALS["BE_USER"]->check('tables_modify', 'sys_dmail_group')) { $theOutput .= $this->cmd_specialQuery($group); } } } return $theOutput; }
/** * Display the test mail group, which configured in the configuration module * * @param array $result Lists of the recipient IDs based on directmail DB record * * @return string List of the recipient (in HTML) */ public function cmd_displayMailGroup_test($result) { $idLists = $result['queryInfo']['id_lists']; $out = ''; if (is_array($idLists['tt_address'])) { $out .= $this->getRecordList(DirectMailUtility::fetchRecordsListValues($idLists['tt_address'], 'tt_address'), 'tt_address'); } if (is_array($idLists['fe_users'])) { $out .= $this->getRecordList(DirectMailUtility::fetchRecordsListValues($idLists['fe_users'], 'fe_users'), 'fe_users'); } if (is_array($idLists['PLAINLIST'])) { $out .= $this->getRecordList($idLists['PLAINLIST'], 'default'); } if (is_array($idLists[$this->userTable])) { $out .= $this->getRecordList(DirectMailUtility::fetchRecordsListValues($idLists[$this->userTable], $this->userTable), $this->userTable); } return $out; }
/** * get statistics from DB and compile them. * * @param array $row: DB record * @return string statistics of a mail */ function cmd_stats($row) { if (GeneralUtility::_GP("recalcCache")) { $this->makeStatTempTableContent($row); } $thisurl = 'index.php?id=' . $this->id . '&sys_dmail_uid=' . $row['uid'] . '&CMD=' . $this->CMD . '&recalcCache=1'; $output = $this->directMail_compactView($row); // ***************************** // Mail responses, general: // ***************************** $mailingId = intval($row['uid']); $queryArray = array('response_type,count(*) as counter', 'sys_dmail_maillog', 'mid=' . $mailingId, 'response_type'); $table = $this->getQueryRows($queryArray, 'response_type'); // Plaintext/HTML $res = $GLOBALS["TYPO3_DB"]->exec_SELECTquery('html_sent,count(*) as counter', 'sys_dmail_maillog', 'mid=' . $mailingId . ' AND response_type=0', 'html_sent'); $text_html = array(); while ($row2 = $GLOBALS["TYPO3_DB"]->sql_fetch_assoc($res)) { // 0:No mail; 1:HTML; 2:TEXT; 3:HTML+TEXT $text_html[$row2['html_sent']] = $row2['counter']; } $GLOBALS["TYPO3_DB"]->sql_free_result($res); // Unique responses, html $res = $GLOBALS["TYPO3_DB"]->exec_SELECTquery('count(*) as counter', 'sys_dmail_maillog', 'mid=' . $mailingId . ' AND response_type=1', 'rid,rtbl', 'counter'); $unique_html_responses = $GLOBALS["TYPO3_DB"]->sql_num_rows($res); $GLOBALS["TYPO3_DB"]->sql_free_result($res); // Unique responses, Plain $res = $GLOBALS["TYPO3_DB"]->exec_SELECTquery('count(*) as counter', 'sys_dmail_maillog', 'mid=' . $mailingId . ' AND response_type=2', 'rid,rtbl', 'counter'); $unique_plain_responses = $GLOBALS["TYPO3_DB"]->sql_num_rows($res); $GLOBALS["TYPO3_DB"]->sql_free_result($res); // Unique responses, pings $res = $GLOBALS["TYPO3_DB"]->exec_SELECTquery('count(*) as counter', 'sys_dmail_maillog', 'mid=' . $mailingId . ' AND response_type=-1', 'rid,rtbl', 'counter'); $unique_ping_responses = $GLOBALS["TYPO3_DB"]->sql_num_rows($res); $GLOBALS["TYPO3_DB"]->sql_free_result($res); $tblLines = array(); $tblLines[] = array('', $GLOBALS["LANG"]->getLL('stats_total'), $GLOBALS["LANG"]->getLL('stats_HTML'), $GLOBALS["LANG"]->getLL('stats_plaintext')); $sent_total = intval($text_html['1'] + $text_html['2'] + $text_html['3']); $sent_html = intval($text_html['1'] + $text_html['3']); $sent_plain = intval($text_html['2']); $tblLines[] = array($GLOBALS["LANG"]->getLL('stats_mails_sent'), $sent_total, $sent_html, $sent_plain); $tblLines[] = array($GLOBALS["LANG"]->getLL('stats_mails_returned'), $this->showWithPercent($table['-127']['counter'], $sent_total)); $tblLines[] = array($GLOBALS["LANG"]->getLL('stats_HTML_mails_viewed'), '', $this->showWithPercent($unique_ping_responses, $sent_html)); $tblLines[] = array($GLOBALS["LANG"]->getLL('stats_unique_responses'), $this->showWithPercent($unique_html_responses + $unique_plain_responses, $sent_total), $this->showWithPercent($unique_html_responses, $sent_html), $this->showWithPercent($unique_plain_responses, $sent_plain ? $sent_plain : $sent_html)); $output .= '<br /><h2>' . $GLOBALS["LANG"]->getLL('stats_general_information') . '</h2>'; $output .= DirectMailUtility::formatTable($tblLines, array('nowrap', 'nowrap align="right"', 'nowrap align="right"', 'nowrap align="right"'), 1, array(), 'cellspacing="0" cellpadding="3" class="stats-table"'); // ****************** // Links: // ****************** // initialize $urlCounter $urlCounter = array('total' => array(), 'plain' => array(), 'html' => array()); // Most popular links, html: $queryArray = array('url_id,count(*) as counter', 'sys_dmail_maillog', 'mid=' . intval($row['uid']) . ' AND response_type=1', 'url_id', 'counter'); $htmlUrlsTable = $this->getQueryRows($queryArray, 'url_id'); // Most popular links, plain: $queryArray = array('url_id,count(*) as counter', 'sys_dmail_maillog', 'mid=' . intval($row['uid']) . ' AND response_type=2', 'url_id', 'counter'); $plainUrlsTable = $this->getQueryRows($queryArray, 'url_id'); // Find urls: $temp_unpackedMail = unserialize(base64_decode($row['mailContent'])); // this array will include a unique list of all URLs that are used in the mailing $urlArr = array(); $urlMd5Map = array(); if (is_array($temp_unpackedMail['html']['hrefs'])) { foreach ($temp_unpackedMail['html']['hrefs'] as $k => $v) { $urlArr[$k] = html_entity_decode($v['absRef']); // convert & of query params back $urlMd5Map[md5($v['absRef'])] = $k; } } if (is_array($temp_unpackedMail['plain']['link_ids'])) { foreach ($temp_unpackedMail['plain']['link_ids'] as $k => $v) { $urlArr[intval(-$k)] = $v; } } // Traverse plain urls: $plainUrlsTable_mapped = array(); foreach ($plainUrlsTable as $id => $c) { $url = $urlArr[intval($id)]; if (isset($urlMd5Map[md5($url)])) { $plainUrlsTable_mapped[$urlMd5Map[md5($url)]] = $c; } else { $plainUrlsTable_mapped[$id] = $c; } } $urlCounter['total'] = array(); // Traverse html urls: $urlCounter['html'] = array(); if (count($htmlUrlsTable) > 0) { foreach ($htmlUrlsTable as $id => $c) { $urlCounter['html'][$id]['counter'] = $urlCounter['total'][$id]['counter'] = $c['counter']; } } // Traverse plain urls: $urlCounter['plain'] = array(); foreach ($plainUrlsTable_mapped as $id => $c) { // Look up plain url in html urls $htmlLinkFound = FALSE; foreach ($urlCounter['html'] as $htmlId => $htmlLink) { if ($urlArr[$id] == $urlArr[$htmlId]) { $urlCounter['html'][$htmlId]['plainId'] = $id; $urlCounter['html'][$htmlId]['plainCounter'] = $c['counter']; $urlCounter['total'][$htmlId]['counter'] = $urlCounter['total'][$htmlId]['counter'] + $c['counter']; $htmlLinkFound = TRUE; break; } } if (!$htmlLinkFound) { $urlCounter['plain'][$id]['counter'] = $c['counter']; $urlCounter['total'][$id]['counter'] = $urlCounter['total'][$id]['counter'] + $c['counter']; } } $tblLines = array(); $tblLines[] = array('', $GLOBALS["LANG"]->getLL('stats_total'), $GLOBALS["LANG"]->getLL('stats_HTML'), $GLOBALS["LANG"]->getLL('stats_plaintext')); $tblLines[] = array($GLOBALS["LANG"]->getLL('stats_total_responses'), $table['1']['counter'] + $table['2']['counter'], $table['1']['counter'] ? $table['1']['counter'] : '0', $table['2']['counter'] ? $table['2']['counter'] : '0'); $tblLines[] = array($GLOBALS["LANG"]->getLL('stats_unique_responses'), $this->showWithPercent($unique_html_responses + $unique_plain_responses, $sent_total), $this->showWithPercent($unique_html_responses, $sent_html), $this->showWithPercent($unique_plain_responses, $sent_plain ? $sent_plain : $sent_html)); $tblLines[] = array($GLOBALS["LANG"]->getLL('stats_links_clicked_per_respondent'), $unique_html_responses + $unique_plain_responses ? number_format(($table['1']['counter'] + $table['2']['counter']) / ($unique_html_responses + $unique_plain_responses), 2) : '-', $unique_html_responses ? number_format($table['1']['counter'] / $unique_html_responses, 2) : '-', $unique_plain_responses ? number_format($table['2']['counter'] / $unique_plain_responses, 2) : '-'); $output .= '<br /><h2>' . $GLOBALS["LANG"]->getLL('stats_response') . '</h2>'; $output .= DirectMailUtility::formatTable($tblLines, array('nowrap', 'nowrap align="right"', 'nowrap align="right"', 'nowrap align="right"'), 1, array(0, 0, 0, 0), 'cellspacing="0" cellpadding="3" class="stats-table"'); arsort($urlCounter['total']); arsort($urlCounter['html']); arsort($urlCounter['plain']); reset($urlCounter['total']); $tblLines = array(); $tblLines[] = array('', $GLOBALS["LANG"]->getLL('stats_HTML_link_nr'), $GLOBALS["LANG"]->getLL('stats_plaintext_link_nr'), $GLOBALS["LANG"]->getLL('stats_total'), $GLOBALS["LANG"]->getLL('stats_HTML'), $GLOBALS["LANG"]->getLL('stats_plaintext'), ''); // HTML mails if (intval($row['sendOptions']) & 0x2) { $HTMLContent = $temp_unpackedMail['html']['content']; $HTMLlinks = array(); if (is_array($temp_unpackedMail['html']['hrefs'])) { foreach ($temp_unpackedMail['html']['hrefs'] as $jumpurlId => $data) { $HTMLlinks[$jumpurlId] = array('url' => $data['ref'], 'label' => ''); } } // get body if (strstr($HTMLContent, '<BODY')) { $tmp = explode('<BODY', $HTMLContent); } else { $tmp = explode('<body', $HTMLContent); } $bodyPart = explode('<', $tmp[1]); // load all <a href="*" parts into $tempHref array, in a 2-dimensional array // where the lower level of the array contains two values, the URL and the unique ID (see $urlArr) foreach ($bodyPart as $k => $str) { if (preg_match('/a.href/', $str)) { $tagAttr = GeneralUtility::get_tag_attributes($bodyPart[$k]); if (strpos($str, '>') === strlen($str) - 1) { if ($tagAttr['href'][0] != '#') { list(, $jumpurlId) = explode('jumpurl=', $tagAttr['href']); $url = $HTMLlinks[$jumpurlId]['url']; // Use the link title if it exists - otherwise use the URL if (strlen($tagAttr['title'])) { $label = $GLOBALS["LANG"]->getLL('stats_img_link') . '<span title="' . $tagAttr['title'] . '">' . GeneralUtility::fixed_lgd_cs(substr($url, 7), 40) . '</span>'; } else { $label = $GLOBALS["LANG"]->getLL('stats_img_link') . '<span title="' . $url . '">' . GeneralUtility::fixed_lgd_cs(substr($url, 7), 40) . '</span>'; } $HTMLlinks[$jumpurlId]['label'] = $label; } } else { if ($tagAttr['href'][0] != '#') { list($url, $jumpurlId) = explode('jumpurl=', $tagAttr['href']); $wordPos = strpos($str, '>'); $label = substr($str, $wordPos + 1); $HTMLlinks[$jumpurlId]['label'] = $label; } } } } } foreach ($urlCounter['total'] as $id => $hits) { // $id is the jumpurl ID $origId = $id; $id = abs(intval($id)); $url = $HTMLlinks[$id]['url'] ? $HTMLlinks[$id]['url'] : $urlArr[$origId]; // a link to this host? $uParts = @parse_url($url); $urlstr = $this->getUrlStr($uParts); $label = $this->getLinkLabel($url, $urlstr, FALSE, $HTMLlinks[$id]['label']); $img = '<a href="' . $urlstr . '" target="_blank"><img ' . IconUtility::skinImg($GLOBALS["BACK_PATH"], 'gfx/zoom.gif', 'width="12" height="12"') . ' title="' . htmlspecialchars($label) . '" /></a>'; if (isset($urlCounter['html'][$id]['plainId'])) { $tblLines[] = array($label, $id, $urlCounter['html'][$id]['plainId'], $urlCounter['total'][$origId]['counter'], $urlCounter['html'][$id]['counter'], $urlCounter['html'][$id]['plainCounter'], $img); } else { $html = empty($urlCounter['html'][$id]['counter']) ? 0 : 1; $tblLines[] = array($label, $html ? $id : '-', $html ? '-' : $id, $html ? $urlCounter['html'][$id]['counter'] : $urlCounter['plain'][$origId]['counter'], $urlCounter['html'][$id]['counter'], $urlCounter['plain'][$origId]['counter'], $img); } } // go through all links that were not clicked yet and that have a label $clickedLinks = array_keys($urlCounter['total']); foreach ($urlArr as $id => $link) { if (!in_array($id, $clickedLinks) && isset($HTMLlinks['id'])) { // a link to this host? $uParts = @parse_url($link); $urlstr = $this->getUrlStr($uParts); $label = $HTMLlinks[$id]['label'] . ' (' . ($urlstr ? $urlstr : '/') . ')'; $img = '<a href="' . htmlspecialchars($link) . '" target="_blank"><img ' . IconUtility::skinImg($GLOBALS["BACK_PATH"], 'gfx/zoom.gif', 'width="12" height="12"') . ' title="' . htmlspecialchars($link) . '" /></a>'; $tblLines[] = array($label, $html ? $id : '-', $html ? '-' : abs($id), $html ? $urlCounter['html'][$id]['counter'] : $urlCounter['plain'][$id]['counter'], $urlCounter['html'][$id]['counter'], $urlCounter['plain'][$id]['counter'], $img); } } if ($urlCounter['total']) { $output .= '<br /><h2>' . $GLOBALS["LANG"]->getLL('stats_response_link') . '</h2>'; $output .= DirectMailUtility::formatTable($tblLines, array('nowrap', 'nowrap width="100"', 'nowrap width="100"', 'nowrap align="right"', 'nowrap align="right"', 'nowrap align="right"', 'nowrap align="right"'), 1, array(1, 0, 0, 0, 0, 0, 1), ' cellspacing="0" cellpadding="3" class="stats-table"'); } // ****************** // Returned mails // ****************** //The icons: $listIcons = '<img ' . IconUtility::skinImg($GLOBALS["BACK_PATH"], 'gfx/list.gif', 'width="12" height="12" alt=""') . ' />'; $csvIcons = '<img ' . IconUtility::skinImg($GLOBALS["BACK_PATH"], 'gfx/csv.gif', 'width="27" height="12" alt=""') . ' />'; if (ExtensionManagementUtility::isLoaded('tt_address')) { $iconPath = ExtensionManagementUtility::extRelPath('tt_address') . 'ext_icon__h.gif'; $iconParam = 'width="18" height="16"'; } else { $iconPath = 'gfx/button_hide.gif'; $iconParam = 'width="11" height="10"'; } $hideIcons = '<img ' . IconUtility::skinImg($GLOBALS["BACK_PATH"], $iconPath, $iconParam . ' alt=""') . ' />'; //icons mails returned $iconsMailReturned[] = '<a href="' . $thisurl . '&returnList=1" class="bubble">' . $listIcons . '<span class="help">' . $GLOBALS["LANG"]->getLL('stats_list_returned') . '</span></a>'; $iconsMailReturned[] = '<a href="' . $thisurl . '&returnDisable=1" class="bubble">' . $hideIcons . '<span class="help">' . $GLOBALS["LANG"]->getLL('stats_disable_returned') . '</span></a>'; $iconsMailReturned[] = '<a href="' . $thisurl . '&returnCSV=1" class="bubble">' . $csvIcons . '<span class="help">' . $GLOBALS["LANG"]->getLL('stats_CSV_returned') . '</span></a>'; //icons unknown recip $iconsUnknownRecip[] = '<a href="' . $thisurl . '&unknownList=1" class="bubble">' . $listIcons . '<span class="help">' . $GLOBALS["LANG"]->getLL('stats_list_returned_unknown_recipient') . '</span></a>'; $iconsUnknownRecip[] = '<a href="' . $thisurl . '&unknownDisable=1" class="bubble">' . $hideIcons . '<span class="help">' . $GLOBALS["LANG"]->getLL('stats_disable_returned_unknown_recipient') . '</span></a>'; $iconsUnknownRecip[] = '<a href="' . $thisurl . '&unknownCSV=1" class="bubble">' . $csvIcons . '<span class="help">' . $GLOBALS["LANG"]->getLL('stats_CSV_returned_unknown_recipient') . '</span></a>'; //icons mailbox full $iconsMailbox[] = '<a href="' . $thisurl . '&fullList=1" class="bubble">' . $listIcons . '<span class="help">' . $GLOBALS["LANG"]->getLL('stats_list_returned_mailbox_full') . '</span></a>'; $iconsMailbox[] = '<a href="' . $thisurl . '&fullDisable=1" class="bubble">' . $hideIcons . '<span class="help">' . $GLOBALS["LANG"]->getLL('stats_disable_returned_mailbox_full') . '</span></a>'; $iconsMailbox[] = '<a href="' . $thisurl . '&fullCSV=1" class="bubble">' . $csvIcons . '<span class="help">' . $GLOBALS["LANG"]->getLL('stats_CSV_returned_mailbox_full') . '</span></a>'; //icons bad host $iconsBadhost[] = '<a href="' . $thisurl . '&badHostList=1" class="bubble">' . $listIcons . '<span class="help">' . $GLOBALS["LANG"]->getLL('stats_list_returned_bad_host') . '</span></a>'; $iconsBadhost[] = '<a href="' . $thisurl . '&badHostDisable=1" class="bubble">' . $hideIcons . '<span class="help">' . $GLOBALS["LANG"]->getLL('stats_disable_returned_bad_host') . '</span></a>'; $iconsBadhost[] = '<a href="' . $thisurl . '&badHostCSV=1" class="bubble">' . $csvIcons . '<span class="help">' . $GLOBALS["LANG"]->getLL('stats_CSV_returned_bad_host') . '</span></a>'; //icons bad header $iconsBadheader[] = '<a href="' . $thisurl . '&badHeaderList=1" class="bubble">' . $listIcons . '<span class="help">' . $GLOBALS["LANG"]->getLL('stats_list_returned_bad_header') . '</span></a>'; $iconsBadheader[] = '<a href="' . $thisurl . '&badHeaderDisable=1" class="bubble">' . $hideIcons . '<span class="help">' . $GLOBALS["LANG"]->getLL('stats_disable_returned_bad_header') . '</span></a>'; $iconsBadheader[] = '<a href="' . $thisurl . '&badHeaderCSV=1" class="bubble">' . $csvIcons . '<span class="help">' . $GLOBALS["LANG"]->getLL('stats_CSV_returned_bad_header') . '</span></a>'; //icons unknown reasons //TODO: link to show all reason $iconsUnknownReason[] = '<a href="' . $thisurl . '&reasonUnknownList=1" class="bubble">' . $listIcons . '<span class="help">' . $GLOBALS["LANG"]->getLL('stats_list_returned_reason_unknown') . '</span></a>'; $iconsUnknownReason[] = '<a href="' . $thisurl . '&reasonUnknownDisable=1" class="bubble">' . $hideIcons . '<span class="help">' . $GLOBALS["LANG"]->getLL('stats_disable_returned_reason_unknown') . '</span></a>'; $iconsUnknownReason[] = '<a href="' . $thisurl . '&reasonUnknownCSV=1" class="bubble">' . $csvIcons . '<span class="help">' . $GLOBALS["LANG"]->getLL('stats_CSV_returned_reason_unknown') . '</span></a>'; //Table with Icon $queryArray = array('count(*) as counter,return_code', 'sys_dmail_maillog', 'mid=' . intval($row['uid']) . ' AND response_type=-127', 'return_code'); $table_ret = $this->getQueryRows($queryArray, 'return_code'); $tblLines = array(); $tblLines[] = array('', $GLOBALS["LANG"]->getLL('stats_count'), ''); $tblLines[] = array($GLOBALS["LANG"]->getLL('stats_total_mails_returned'), $table['-127']['counter'] ? number_format(intval($table['-127']['counter'])) : '0', implode(' ', $iconsMailReturned)); $tblLines[] = array($GLOBALS["LANG"]->getLL('stats_recipient_unknown'), $this->showWithPercent($table_ret['550']['counter'] + $table_ret['553']['counter'], $table['-127']['counter']), implode(' ', $iconsUnknownRecip)); $tblLines[] = array($GLOBALS["LANG"]->getLL('stats_mailbox_full'), $this->showWithPercent($table_ret['551']['counter'], $table['-127']['counter']), implode(' ', $iconsMailbox)); $tblLines[] = array($GLOBALS["LANG"]->getLL('stats_bad_host'), $this->showWithPercent($table_ret['552']['counter'], $table['-127']['counter']), implode(' ', $iconsBadhost)); $tblLines[] = array($GLOBALS["LANG"]->getLL('stats_error_in_header'), $this->showWithPercent($table_ret['554']['counter'], $table['-127']['counter']), implode(' ', $iconsBadheader)); $tblLines[] = array($GLOBALS["LANG"]->getLL('stats_reason_unkown'), $this->showWithPercent($table_ret['-1']['counter'], $table['-127']['counter']), implode(' ', $iconsUnknownReason)); $output .= '<br /><h2>' . $GLOBALS["LANG"]->getLL('stats_mails_returned') . '</h2>'; $output .= DirectMailUtility::formatTable($tblLines, array('nowrap', 'nowrap align="right"', ''), 1, array(0, 0, 1), 'cellspacing="0" cellpadding="3" class="stats-table"'); //Find all returned mail if (GeneralUtility::_GP('returnList') || GeneralUtility::_GP('returnDisable') || GeneralUtility::_GP('returnCSV')) { $res = $GLOBALS["TYPO3_DB"]->exec_SELECTquery('rid,rtbl,email', 'sys_dmail_maillog', 'mid=' . intval($row['uid']) . ' AND response_type=-127'); $idLists = array(); while ($rrow = $GLOBALS["TYPO3_DB"]->sql_fetch_assoc($res)) { switch ($rrow['rtbl']) { case 't': $idLists['tt_address'][] = $rrow['rid']; break; case 'f': $idLists['fe_users'][] = $rrow['rid']; break; case 'P': $idLists['PLAINLIST'][] = $rrow['email']; break; default: $idLists[$rrow['rtbl']][] = $rrow['rid']; break; } } if (GeneralUtility::_GP('returnList')) { if (is_array($idLists['tt_address'])) { $output .= '<br />' . $GLOBALS["LANG"]->getLL('stats_emails') . '<br />' . DirectMailUtility::getRecordList(DirectMailUtility::fetchRecordsListValues($idLists['tt_address'], 'tt_address'), 'tt_address', $this->id, 1, $this->sys_dmail_uid); } if (is_array($idLists['fe_users'])) { $output .= '<br />' . $GLOBALS["LANG"]->getLL('stats_website_users') . DirectMailUtility::getRecordList(DirectMailUtility::fetchRecordsListValues($idLists['fe_users'], 'fe_users'), 'fe_users', $this->id, 1, $this->sys_dmail_uid); } if (is_array($idLists['PLAINLIST'])) { $output .= '<br />' . $GLOBALS["LANG"]->getLL('stats_plainlist'); $output .= '<ul><li>' . join('</li><li>', $idLists['PLAINLIST']) . '</li></ul>'; } } if (GeneralUtility::_GP('returnDisable')) { if (is_array($idLists['tt_address'])) { $c = $this->disableRecipients(DirectMailUtility::fetchRecordsListValues($idLists['tt_address'], 'tt_address'), 'tt_address'); $output .= '<br />' . $c . ' ' . $GLOBALS["LANG"]->getLL('stats_emails_disabled'); } if (is_array($idLists['fe_users'])) { $c = $this->disableRecipients(DirectMailUtility::fetchRecordsListValues($idLists['fe_users'], 'fe_users'), 'fe_users'); $output .= '<br />' . $c . ' ' . $GLOBALS["LANG"]->getLL('stats_website_users_disabled'); } } if (GeneralUtility::_GP('returnCSV')) { $emails = array(); if (is_array($idLists['tt_address'])) { $arr = DirectMailUtility::fetchRecordsListValues($idLists['tt_address'], 'tt_address'); foreach ($arr as $v) { $emails[] = $v['email']; } } if (is_array($idLists['fe_users'])) { $arr = DirectMailUtility::fetchRecordsListValues($idLists['fe_users'], 'fe_users'); foreach ($arr as $v) { $emails[] = $v['email']; } } if (is_array($idLists['PLAINLIST'])) { $emails = array_merge($emails, $idLists['PLAINLIST']); } $output .= '<br />' . $GLOBALS["LANG"]->getLL('stats_emails_returned_list') . '<br />'; $output .= '<textarea' . $GLOBALS["TBE_TEMPLATE"]->formWidthText() . ' rows="6" name="nothing">' . GeneralUtility::formatForTextarea(implode(LF, $emails)) . '</textarea>'; } } //Find Unknown Recipient if (GeneralUtility::_GP('unknownList') || GeneralUtility::_GP('unknownDisable') || GeneralUtility::_GP('unknownCSV')) { $res = $GLOBALS["TYPO3_DB"]->exec_SELECTquery('rid,rtbl,email', 'sys_dmail_maillog', 'mid=' . intval($row['uid']) . ' AND response_type=-127' . ' AND (return_code=550 OR return_code=553)'); $idLists = array(); while ($rrow = $GLOBALS["TYPO3_DB"]->sql_fetch_assoc($res)) { switch ($rrow['rtbl']) { case 't': $idLists['tt_address'][] = $rrow['rid']; break; case 'f': $idLists['fe_users'][] = $rrow['rid']; break; case 'P': $idLists['PLAINLIST'][] = $rrow['email']; break; default: $idLists[$rrow['rtbl']][] = $rrow['rid']; break; } } if (GeneralUtility::_GP('unknownList')) { if (is_array($idLists['tt_address'])) { $output .= '<br />' . $GLOBALS["LANG"]->getLL('stats_emails') . '<br />' . DirectMailUtility::getRecordList(DirectMailUtility::fetchRecordsListValues($idLists['tt_address'], 'tt_address'), 'tt_address', $this->id, 1, $this->sys_dmail_uid); } if (is_array($idLists['fe_users'])) { $output .= '<br />' . $GLOBALS["LANG"]->getLL('stats_website_users') . DirectMailUtility::getRecordList(DirectMailUtility::fetchRecordsListValues($idLists['fe_users'], 'fe_users'), 'fe_users', $this->id, 1, $this->sys_dmail_uid); } if (is_array($idLists['PLAINLIST'])) { $output .= '<br />' . $GLOBALS["LANG"]->getLL('stats_plainlist'); $output .= '<ul><li>' . join('</li><li>', $idLists['PLAINLIST']) . '</li></ul>'; } } if (GeneralUtility::_GP('unknownDisable')) { if (is_array($idLists['tt_address'])) { $c = $this->disableRecipients(DirectMailUtility::fetchRecordsListValues($idLists['tt_address'], 'tt_address'), 'tt_address'); $output .= '<br />' . $c . ' ' . $GLOBALS["LANG"]->getLL('stats_emails_disabled'); } if (is_array($idLists['fe_users'])) { $c = $this->disableRecipients(DirectMailUtility::fetchRecordsListValues($idLists['fe_users'], 'fe_users'), 'fe_users'); $output .= '<br />' . $c . ' ' . $GLOBALS["LANG"]->getLL('stats_website_users_disabled'); } } if (GeneralUtility::_GP('unknownCSV')) { $emails = array(); if (is_array($idLists['tt_address'])) { $arr = DirectMailUtility::fetchRecordsListValues($idLists['tt_address'], 'tt_address'); foreach ($arr as $v) { $emails[] = $v['email']; } } if (is_array($idLists['fe_users'])) { $arr = DirectMailUtility::fetchRecordsListValues($idLists['fe_users'], 'fe_users'); foreach ($arr as $v) { $emails[] = $v['email']; } } if (is_array($idLists['PLAINLIST'])) { $emails = array_merge($emails, $idLists['PLAINLIST']); } $output .= '<br />' . $GLOBALS["LANG"]->getLL('stats_emails_returned_unknown_recipient_list') . '<br />'; $output .= '<textarea' . $GLOBALS["TBE_TEMPLATE"]->formWidthText() . ' rows="6" name="nothing">' . GeneralUtility::formatForTextarea(implode(LF, $emails)) . '</textarea>'; } } //Mailbox Full if (GeneralUtility::_GP('fullList') || GeneralUtility::_GP('fullDisable') || GeneralUtility::_GP('fullCSV')) { $res = $GLOBALS["TYPO3_DB"]->exec_SELECTquery('rid,rtbl,email', 'sys_dmail_maillog', 'mid=' . intval($row['uid']) . ' AND response_type=-127' . ' AND return_code=551'); $idLists = array(); while ($rrow = $GLOBALS["TYPO3_DB"]->sql_fetch_assoc($res)) { switch ($rrow['rtbl']) { case 't': $idLists['tt_address'][] = $rrow['rid']; break; case 'f': $idLists['fe_users'][] = $rrow['rid']; break; case 'P': $idLists['PLAINLIST'][] = $rrow['email']; break; default: $idLists[$rrow['rtbl']][] = $rrow['rid']; break; } } if (GeneralUtility::_GP('fullList')) { if (is_array($idLists['tt_address'])) { $output .= '<br />' . $GLOBALS["LANG"]->getLL('stats_emails') . '<br />' . DirectMailUtility::getRecordList(DirectMailUtility::fetchRecordsListValues($idLists['tt_address'], 'tt_address'), 'tt_address', $this->id, 1, $this->sys_dmail_uid); } if (is_array($idLists['fe_users'])) { $output .= '<br />' . $GLOBALS["LANG"]->getLL('stats_website_users') . DirectMailUtility::getRecordList(DirectMailUtility::fetchRecordsListValues($idLists['fe_users'], 'fe_users'), 'fe_users', $this->id, 1, $this->sys_dmail_uid); } if (is_array($idLists['PLAINLIST'])) { $output .= '<br />' . $GLOBALS["LANG"]->getLL('stats_plainlist'); $output .= '<ul><li>' . join('</li><li>', $idLists['PLAINLIST']) . '</li></ul>'; } } if (GeneralUtility::_GP('fullDisable')) { if (is_array($idLists['tt_address'])) { $c = $this->disableRecipients(DirectMailUtility::fetchRecordsListValues($idLists['tt_address'], 'tt_address'), 'tt_address'); $output .= '<br />' . $c . ' ' . $GLOBALS["LANG"]->getLL('stats_emails_disabled'); } if (is_array($idLists['fe_users'])) { $c = $this->disableRecipients(DirectMailUtility::fetchRecordsListValues($idLists['fe_users'], 'fe_users'), 'fe_users'); $output .= '<br />' . $c . ' ' . $GLOBALS["LANG"]->getLL('stats_website_users_disabled'); } } if (GeneralUtility::_GP('fullCSV')) { $emails = array(); if (is_array($idLists['tt_address'])) { $arr = DirectMailUtility::fetchRecordsListValues($idLists['tt_address'], 'tt_address'); foreach ($arr as $v) { $emails[] = $v['email']; } } if (is_array($idLists['fe_users'])) { $arr = DirectMailUtility::fetchRecordsListValues($idLists['fe_users'], 'fe_users'); foreach ($arr as $v) { $emails[] = $v['email']; } } if (is_array($idLists['PLAINLIST'])) { $emails = array_merge($emails, $idLists['PLAINLIST']); } $output .= '<br />' . $GLOBALS["LANG"]->getLL('stats_emails_returned_mailbox_full_list') . '<br />'; $output .= '<textarea' . $GLOBALS["TBE_TEMPLATE"]->formWidthText() . ' rows="6" name="nothing">' . GeneralUtility::formatForTextarea(implode(LF, $emails)) . '</textarea>'; } } //find Bad Host if (GeneralUtility::_GP('badHostList') || GeneralUtility::_GP('badHostDisable') || GeneralUtility::_GP('badHostCSV')) { $res = $GLOBALS["TYPO3_DB"]->exec_SELECTquery('rid,rtbl,email', 'sys_dmail_maillog', 'mid=' . intval($row['uid']) . ' AND response_type=-127' . ' AND return_code=552'); $idLists = array(); while ($rrow = $GLOBALS["TYPO3_DB"]->sql_fetch_assoc($res)) { switch ($rrow['rtbl']) { case 't': $idLists['tt_address'][] = $rrow['rid']; break; case 'f': $idLists['fe_users'][] = $rrow['rid']; break; case 'P': $idLists['PLAINLIST'][] = $rrow['email']; break; default: $idLists[$rrow['rtbl']][] = $rrow['rid']; break; } } if (GeneralUtility::_GP('badHostList')) { if (is_array($idLists['tt_address'])) { $output .= '<br />' . $GLOBALS["LANG"]->getLL('stats_emails') . '<br />' . DirectMailUtility::getRecordList(DirectMailUtility::fetchRecordsListValues($idLists['tt_address'], 'tt_address'), 'tt_address', $this->id, 1, $this->sys_dmail_uid); } if (is_array($idLists['fe_users'])) { $output .= '<br />' . $GLOBALS["LANG"]->getLL('stats_website_users') . DirectMailUtility::getRecordList(DirectMailUtility::fetchRecordsListValues($idLists['fe_users'], 'fe_users'), 'fe_users', $this->id, 1, $this->sys_dmail_uid); } if (is_array($idLists['PLAINLIST'])) { $output .= '<br />' . $GLOBALS["LANG"]->getLL('stats_plainlist'); $output .= '<ul><li>' . join('</li><li>', $idLists['PLAINLIST']) . '</li></ul>'; } } if (GeneralUtility::_GP('badHostDisable')) { if (is_array($idLists['tt_address'])) { $c = $this->disableRecipients(DirectMailUtility::fetchRecordsListValues($idLists['tt_address'], 'tt_address'), 'tt_address'); $output .= '<br />' . $c . ' ' . $GLOBALS["LANG"]->getLL('stats_emails_disabled'); } if (is_array($idLists['fe_users'])) { $c = $this->disableRecipients(DirectMailUtility::fetchRecordsListValues($idLists['fe_users'], 'fe_users'), 'fe_users'); $output .= '<br />' . $c . ' ' . $GLOBALS["LANG"]->getLL('stats_website_users_disabled'); } } if (GeneralUtility::_GP('badHostCSV')) { $emails = array(); if (is_array($idLists['tt_address'])) { $arr = DirectMailUtility::fetchRecordsListValues($idLists['tt_address'], 'tt_address'); foreach ($arr as $v) { $emails[] = $v['email']; } } if (is_array($idLists['fe_users'])) { $arr = DirectMailUtility::fetchRecordsListValues($idLists['fe_users'], 'fe_users'); foreach ($arr as $v) { $emails[] = $v['email']; } } if (is_array($idLists['PLAINLIST'])) { $emails = array_merge($emails, $idLists['PLAINLIST']); } $output .= '<br />' . $GLOBALS["LANG"]->getLL('stats_emails_returned_bad_host_list') . '<br />'; $output .= '<textarea' . $GLOBALS["TBE_TEMPLATE"]->formWidthText() . ' rows="6" name="nothing">' . GeneralUtility::formatForTextarea(implode(LF, $emails)) . '</textarea>'; } } //find Bad Header if (GeneralUtility::_GP('badHeaderList') || GeneralUtility::_GP('badHeaderDisable') || GeneralUtility::_GP('badHeaderCSV')) { $res = $GLOBALS["TYPO3_DB"]->exec_SELECTquery('rid,rtbl,email', 'sys_dmail_maillog', 'mid=' . intval($row['uid']) . ' AND response_type=-127' . ' AND return_code=554'); $idLists = array(); while ($rrow = $GLOBALS["TYPO3_DB"]->sql_fetch_assoc($res)) { switch ($rrow['rtbl']) { case 't': $idLists['tt_address'][] = $rrow['rid']; break; case 'f': $idLists['fe_users'][] = $rrow['rid']; break; case 'P': $idLists['PLAINLIST'][] = $rrow['email']; break; default: $idLists[$rrow['rtbl']][] = $rrow['rid']; break; } } if (GeneralUtility::_GP('badHeaderList')) { if (is_array($idLists['tt_address'])) { $output .= '<br />' . $GLOBALS["LANG"]->getLL('stats_emails') . '<br />' . DirectMailUtility::getRecordList(DirectMailUtility::fetchRecordsListValues($idLists['tt_address'], 'tt_address'), 'tt_address', $this->id, 1, $this->sys_dmail_uid); } if (is_array($idLists['fe_users'])) { $output .= '<br />' . $GLOBALS["LANG"]->getLL('stats_website_users') . DirectMailUtility::getRecordList(DirectMailUtility::fetchRecordsListValues($idLists['fe_users'], 'fe_users'), 'fe_users', $this->id, 1, $this->sys_dmail_uid); } if (is_array($idLists['PLAINLIST'])) { $output .= '<br />' . $GLOBALS["LANG"]->getLL('stats_plainlist'); $output .= '<ul><li>' . join('</li><li>', $idLists['PLAINLIST']) . '</li></ul>'; } } if (GeneralUtility::_GP('badHeaderDisable')) { if (is_array($idLists['tt_address'])) { $c = $this->disableRecipients(DirectMailUtility::fetchRecordsListValues($idLists['tt_address'], 'tt_address'), 'tt_address'); $output .= '<br />' . $c . ' ' . $GLOBALS["LANG"]->getLL('stats_emails_disabled'); } if (is_array($idLists['fe_users'])) { $c = $this->disableRecipients(DirectMailUtility::fetchRecordsListValues($idLists['fe_users'], 'fe_users'), 'fe_users'); $output .= '<br />' . $c . ' ' . $GLOBALS["LANG"]->getLL('stats_website_users_disabled'); } } if (GeneralUtility::_GP('badHeaderCSV')) { $emails = array(); if (is_array($idLists['tt_address'])) { $arr = DirectMailUtility::fetchRecordsListValues($idLists['tt_address'], 'tt_address'); foreach ($arr as $v) { $emails[] = $v['email']; } } if (is_array($idLists['fe_users'])) { $arr = DirectMailUtility::fetchRecordsListValues($idLists['fe_users'], 'fe_users'); foreach ($arr as $v) { $emails[] = $v['email']; } } if (is_array($idLists['PLAINLIST'])) { $emails = array_merge($emails, $idLists['PLAINLIST']); } $output .= '<br />' . $GLOBALS["LANG"]->getLL('stats_emails_returned_bad_header_list') . '<br />'; $output .= '<textarea' . $GLOBALS["TBE_TEMPLATE"]->formWidthText() . ' rows="6" name="nothing">' . GeneralUtility::formatForTextarea(implode(LF, $emails)) . '</textarea>'; } } //find Unknown Reasons //TODO: list all reason if (GeneralUtility::_GP('reasonUnknownList') || GeneralUtility::_GP('reasonUnknownDisable') || GeneralUtility::_GP('reasonUnknownCSV')) { $res = $GLOBALS["TYPO3_DB"]->exec_SELECTquery('rid,rtbl,email', 'sys_dmail_maillog', 'mid=' . intval($row['uid']) . ' AND response_type=-127' . ' AND return_code=-1'); $idLists = array(); while ($rrow = $GLOBALS["TYPO3_DB"]->sql_fetch_assoc($res)) { switch ($rrow['rtbl']) { case 't': $idLists['tt_address'][] = $rrow['rid']; break; case 'f': $idLists['fe_users'][] = $rrow['rid']; break; case 'P': $idLists['PLAINLIST'][] = $rrow['email']; break; default: $idLists[$rrow['rtbl']][] = $rrow['rid']; break; } } if (GeneralUtility::_GP('reasonUnknownList')) { if (is_array($idLists['tt_address'])) { $output .= '<br />' . $GLOBALS["LANG"]->getLL('stats_emails') . '<br />' . DirectMailUtility::getRecordList(DirectMailUtility::fetchRecordsListValues($idLists['tt_address'], 'tt_address'), 'tt_address', $this->id, 1, $this->sys_dmail_uid); } if (is_array($idLists['fe_users'])) { $output .= '<br />' . $GLOBALS["LANG"]->getLL('stats_website_users') . DirectMailUtility::getRecordList(DirectMailUtility::fetchRecordsListValues($idLists['fe_users'], 'fe_users'), 'fe_users', $this->id, 1, $this->sys_dmail_uid); } if (is_array($idLists['PLAINLIST'])) { $output .= '<br />' . $GLOBALS["LANG"]->getLL('stats_plainlist'); $output .= '<ul><li>' . join('</li><li>', $idLists['PLAINLIST']) . '</li></ul>'; } } if (GeneralUtility::_GP('reasonUnknownDisable')) { if (is_array($idLists['tt_address'])) { $c = $this->disableRecipients(DirectMailUtility::fetchRecordsListValues($idLists['tt_address'], 'tt_address'), 'tt_address'); $output .= '<br />' . $c . ' ' . $GLOBALS["LANG"]->getLL('stats_emails_disabled'); } if (is_array($idLists['fe_users'])) { $c = $this->disableRecipients(DirectMailUtility::fetchRecordsListValues($idLists['fe_users'], 'fe_users'), 'fe_users'); $output .= '<br />' . $c . ' ' . $GLOBALS["LANG"]->getLL('stats_website_users_disabled'); } } if (GeneralUtility::_GP('reasonUnknownCSV')) { $emails = array(); if (is_array($idLists['tt_address'])) { $arr = DirectMailUtility::fetchRecordsListValues($idLists['tt_address'], 'tt_address'); foreach ($arr as $v) { $emails[] = $v['email']; } } if (is_array($idLists['fe_users'])) { $arr = DirectMailUtility::fetchRecordsListValues($idLists['fe_users'], 'fe_users'); foreach ($arr as $v) { $emails[] = $v['email']; } } if (is_array($idLists['PLAINLIST'])) { $emails = array_merge($emails, $idLists['PLAINLIST']); } $output .= '<br />' . $GLOBALS["LANG"]->getLL('stats_emails_returned_reason_unknown_list') . '<br />'; $output .= '<textarea' . $GLOBALS["TBE_TEMPLATE"]->formWidthText() . ' rows="6" name="nothing">' . GeneralUtility::formatForTextarea(implode(LF, $emails)) . '</textarea>'; } } /** * Hook for cmd_stats_postProcess * insert a link to open extended importer */ if (is_array($GLOBALS['TYPO3_CONF_VARS']['EXTCONF']['direct_mail']['mod4']['cmd_stats'])) { $hookObjectsArr = array(); foreach ($GLOBALS['TYPO3_CONF_VARS']['EXTCONF']['direct_mail']['mod4']['cmd_stats'] as $classRef) { $hookObjectsArr[] =& GeneralUtility::getUserObj($classRef); } $this->output = $output; // assigned $output to class property to make it acesssible inside hook $output = ''; // and clear the former $output to collect hoot return code there foreach ($hookObjectsArr as $hookObj) { if (method_exists($hookObj, 'cmd_stats_postProcess')) { $output .= $hookObj->cmd_stats_postProcess($row, $this); } } } $this->noView = 1; // put all the stats tables in a section $theOutput = $this->doc->section($GLOBALS["LANG"]->getLL('stats_direct_mail'), $output, 1, 1, 0, TRUE); $theOutput .= $this->doc->spacer(20); $link = '<p><a style="text-decoration: underline;" href="' . $thisurl . '">' . $GLOBALS["LANG"]->getLL('stats_recalculate_stats') . '</a></p>'; $theOutput .= $this->doc->section($GLOBALS["LANG"]->getLL('stats_recalculate_cached_data'), $link, 1, 1, 0, TRUE); return $theOutput; }