[DOC] Add example in documentation how to manually retrieve mails + answers

[georgringer]

I needed a custom export of mails + answers. while this is not that difficult to do, it could also be added in the docs as an example

<?php

declare(strict_types=1);

namespace Vendor\ExtensionKey\Service;

use TYPO3\CMS\Core\Database\ConnectionPool;
use TYPO3\CMS\Core\Utility\GeneralUtility;

class PowermailExportService
{

    public function get(int $pageUid, int $formUid, array $skippedFieldIds = []): array
    {
        $rows = $this->getData($pageUid, $formUid, $skippedFieldIds);

        $groupedRows = [];
        $availableFields = [];
        foreach ($rows as $row) {
            $groupedRows[$row['mailUid']]['mail'] = [
                'tstamp' => $row['tstamp'],
                'sender_name' => $row['sender_name'],
                'sender_mail' => $row['sender_mail'],
                'subject' => $row['subject'],
                'receiver_mail' => $row['receiver_mail'],
                'spam_factor' => $row['spam_factor'],
            ];
            $groupedRows[$row['mailUid']]['answers'][$row['fieldUid']] = [
                'fieldTitle' => $row['fieldTitle'],
                'fieldType' => $row['fieldType'],
                'answerValue' => $row['answerValue'],
            ];
            $availableFields[$row['fieldUid']] = sprintf('%s [%s]', $row['fieldTitle'], $row['fieldUid']);
        }
        $computedList = [];
        foreach ($groupedRows as $row) {
            $item = $row['mail'];
            $item['tstamp'] = $this->convertTimestamp($item['tstamp']);

            foreach ($availableFields as $fieldUid => $name) {

                if (isset($row['answers'][$fieldUid])) {
                    $item[$name] = $row['answers'][$fieldUid]['answerValue'];
                } else {
                    $item[$name] = '';
                }
            }
            $computedList[] = $item;
        }
        return $computedList;
    }

    protected function convertTimestamp(int $timestamp): string
    {
        return date('Y-m-d H:i:s', $timestamp);
    }

    protected function getData(int $pageUid, int $formUid, array $skippedFieldIds): array
    {
        $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)
            ->getQueryBuilderForTable('tx_powermail_domain_model_mail');

        $constraints = [
            $queryBuilder->expr()->eq('mail.form', $queryBuilder->createNamedParameter($formUid, \PDO::PARAM_INT)),
            $queryBuilder->expr()->eq('mail.pid', $queryBuilder->createNamedParameter($pageUid, \PDO::PARAM_INT)),
        ];

        if (!empty($skippedFieldIds)) {
            $constraints[] = $queryBuilder->expr()->notIn('field.uid', $skippedFieldIds);
        }
        $queryBuilder->getRestrictions()->removeAll();
        $res = $queryBuilder
            ->select(
                'mail.uid as mailUid', 'mail.tstamp', 'mail.sender_name', 'mail.sender_mail', 'mail.subject', 'mail.receiver_mail', 'mail.spam_factor',
                'answer.uid as answerUid', 'answer.value as answerValue',
                'field.title as fieldTitle', 'field.type as fieldType', 'field.auto_marker as fieldMarker', 'field.uid as fieldUid'
            )
            ->from('tx_powermail_domain_model_mail', 'mail')
            ->rightJoin(
                'mail',
                'tx_powermail_domain_model_answer',
                'answer',
                $queryBuilder->expr()->eq('mail.uid', $queryBuilder->quoteIdentifier('answer.mail')))
            ->rightJoin(
                'mail',
                'tx_powermail_domain_model_field',
                'field',
                $queryBuilder->expr()->eq('field.uid', $queryBuilder->quoteIdentifier('answer.field')))
            ->where(...$constraints)
            ->orderBy('mail.uid', 'ASC');

        return $res->executeQuery()
            ->fetchAllAssociative();
    }
}

this will return an array which can be used by CSV export.

if you don't want to merge that in docs, I am of course fine as well