Symfony Profiler

<?php
declare(strict_types=1);
namespace App\UI\Admin\Controller;
use App\Domain\Payment\Model\RecurringMandateStatus;
use App\UI\Shared\Controller\AbstractController;
use Doctrine\DBAL\Connection;
use Doctrine\DBAL\ParameterType;
use Doctrine\Persistence\ManagerRegistry;
use Symfony\Component\HttpFoundation\JsonResponse;
use Symfony\Component\HttpFoundation\Request;
use Symfony\Component\HttpFoundation\Response;
use Symfony\Component\HttpFoundation\StreamedResponse;
use Symfony\Component\Routing\Annotation\Route;
#[Route('/recurring-payments')]
class RecurringPaymentController extends AbstractController
{
// Keyed by the actual <table> column index (see index.html.twig), not a sequential index -
// DataTables sends the real column position, and column 0/3/5/7/8/10 aren't orderable.
private const SORTABLE_COLUMNS = [
1 => 'rm.mandate_ref',
2 => 's.name',
4 => 'rpp.amount',
6 => 'rm.status',
8 => 'last_payment_at',
9 => 'rm.created_at',
];
private const MANDATE_LIST_SELECT = "
rm.id,
rm.mandate_ref,
rm.debitor_name,
rm.debitor_iban,
rm.debitor_email,
rm.status,
rm.created_at,
s.name AS site_name,
rpp.currency,
rpp.amount,
rpp.interval_value,
rpp.interval_unit,
rpp.max_count,
(SELECT COUNT(*) FROM recurring_payments rp WHERE rp.mandate_id = rm.id::text) AS payments_count,
(SELECT MAX(rp.created_at::bigint) FROM recurring_payments rp WHERE rp.mandate_id = rm.id::text) AS last_payment_at
";
private const MANDATE_LIST_FROM = '
FROM recurring_mandates rm
LEFT JOIN sites s ON s.id::text = rm.site_id
LEFT JOIN LATERAL (
SELECT currency, amount, interval_value, interval_unit, max_count
FROM recurring_payment_plans
WHERE mandate_id = rm.id::text
ORDER BY created_at::bigint DESC
LIMIT 1
) rpp ON true
';
#[Route(path: '/', name: 'admin_recurring_payments')]
public function index(): Response
{
return $this->render('admin/recurring_payment/index.html.twig');
}
#[Route(path: '/list', name: 'admin_recurring_payments_list', methods: ['GET'])]
public function list(Request $request, ManagerRegistry $doctrine): JsonResponse
{
$conn = $doctrine->getConnection();
$draw = (int) $request->query->get('draw', 1);
$start = max(0, (int) $request->query->get('start', 0));
$length = (int) $request->query->get('length', 10);
$length = $length > 0 ? min($length, 100) : 10;
$search = trim((string) $request->query->get('search', ''));
$orderColumnIndex = (int) $request->query->get('order_column', 9);
$orderDir = strtolower((string) $request->query->get('order_dir', 'desc')) === 'asc' ? 'ASC' : 'DESC';
$orderColumn = self::SORTABLE_COLUMNS[$orderColumnIndex] ?? 'rm.created_at';
$orderExpr = $orderColumn === 'rm.created_at' ? 'rm.created_at::bigint' : $orderColumn;
$where = '';
$params = [];
if ($search !== '') {
$where = 'WHERE rm.mandate_ref ILIKE :search
OR rm.debitor_name ILIKE :search
OR rm.debitor_email ILIKE :search
OR rm.debitor_iban ILIKE :search
OR rm.status ILIKE :search
OR s.name ILIKE :search';
$params['search'] = '%' . $search . '%';
}
$baseSql = self::MANDATE_LIST_FROM . $where;
$recordsTotal = (int) $conn->fetchOne('SELECT COUNT(*) FROM recurring_mandates');
$recordsFiltered = (int) $conn->fetchOne("SELECT COUNT(*) $baseSql", $params);
$rows = $conn->fetchAllAssociative(
'SELECT ' . self::MANDATE_LIST_SELECT . "
$baseSql
ORDER BY $orderExpr $orderDir
LIMIT :length OFFSET :start",
$params + ['length' => $length, 'start' => $start],
['length' => ParameterType::INTEGER, 'start' => ParameterType::INTEGER]
);
return new JsonResponse([
'draw' => $draw,
'recordsTotal' => $recordsTotal,
'recordsFiltered' => $recordsFiltered,
'data' => $rows,
]);
}
#[Route(path: '/export-all', name: 'admin_recurring_payments_export_all')]
public function exportAll(ManagerRegistry $doctrine): StreamedResponse
{
$conn = $doctrine->getConnection();
$rows = $conn->fetchAllAssociative(
'SELECT ' . self::MANDATE_LIST_SELECT . self::MANDATE_LIST_FROM . '
ORDER BY rm.created_at::bigint DESC'
);
$response = new StreamedResponse(function () use ($rows) {
$handle = fopen('php://output', 'w+');
fputcsv($handle, [
'Mandate Ref', 'Site', 'Debtor Name', 'Debtor Email', 'Debtor IBAN',
'Amount', 'Currency', 'Frequency', 'Status',
'Payments', 'Payments Total', 'Last Payment', 'Created At',
]);
foreach ($rows as $row) {
$total = $row['max_count'] !== null ? (1 + (int) $row['max_count']) : null;
fputcsv($handle, [
$row['mandate_ref'],
$row['site_name'],
$row['debitor_name'],
$row['debitor_email'],
$row['debitor_iban'],
$row['amount'],
$row['currency'],
$row['interval_value'] && $row['interval_unit'] ? 'Every ' . $row['interval_value'] . ' ' . strtolower((string) $row['interval_unit']) : '',
$row['status'],
$row['payments_count'],
$total ?? 'Unlimited',
$row['last_payment_at'] ? date('Y-m-d H:i:s', (int) $row['last_payment_at']) : '',
$row['created_at'] ? date('Y-m-d H:i:s', (int) $row['created_at']) : '',
]);
}
fclose($handle);
});
$filename = 'recurring_payments_' . date('Ymd_His') . '.csv';
$response->headers->set('Content-Type', 'text/csv');
$response->headers->set('Content-Disposition', 'attachment; filename="' . $filename . '"');
return $response;
}
#[Route(path: '/{mandateId}/payments', name: 'admin_recurring_payments_child', methods: ['GET'])]
public function payments(string $mandateId, ManagerRegistry $doctrine): JsonResponse
{
$conn = $doctrine->getConnection();
$rows = $conn->fetchAllAssociative(
'SELECT id, amount, currency, type, status, reference_id, retry_count, created_at
FROM recurring_payments
WHERE mandate_id = :mandateId
ORDER BY created_at::bigint DESC',
['mandateId' => $mandateId]
);
return new JsonResponse(['data' => $rows]);
}
#[Route(path: '/{mandateId}/stop', name: 'admin_recurring_payments_stop', methods: ['POST'])]
public function stop(string $mandateId, ManagerRegistry $doctrine): JsonResponse
{
$conn = $doctrine->getConnection();
$current = $conn->fetchOne('SELECT status FROM recurring_mandates WHERE id::text = :id', ['id' => $mandateId]);
if ($current === false) {
return $this->createErrorJsonResponse('Mandate not found.');
}
if ($current === RecurringMandateStatus::STOPPED) {
return new JsonResponse(['success' => true, 'status' => RecurringMandateStatus::STOPPED]);
}
$this->updateMandateStatus($conn, $mandateId, RecurringMandateStatus::STOPPED);
return new JsonResponse(['success' => true, 'status' => RecurringMandateStatus::STOPPED]);
}
private function updateMandateStatus(Connection $conn, string $mandateId, string $status): void
{
$conn->executeStatement(
'UPDATE recurring_mandates
SET status = :status, updated_at = EXTRACT(epoch FROM now())::bigint::text
WHERE id::text = :id',
['status' => $status, 'id' => $mandateId]
);
}
}

src/UI/Admin/Controller/RecurringPaymentController.php line 63