Symfony Profiler

<?php
namespace App\Application\Abonnent;
use App\Entity\Abonnent\Abonnent;
use App\Entity\Abonnent\Abonnenttoken;
use App\Entity\Abonnent\Exceptions\AbonnentLoginFailedException;
use App\Entity\Abonnent\Exceptions\AbonnentLoginInactiveException;
use App\Entity\Abonnent\Exceptions\AbonnentToManyDevicesException;
use App\Entity\Abonnent\Exceptions\AbonnentWithThisIdNotExistsException;
use App\Entity\Abonnent\Exceptions\AbonnentWithThisUsernameNotExistsException;
use App\Entity\Abonnent\ResetPasswordRequest;
use Doctrine\DBAL\Exception;
use Doctrine\ORM\EntityManagerInterface;
use Psr\Log\LoggerInterface;
use Symfony\Component\Filesystem\Filesystem;
use Symfony\Component\HttpFoundation\Request;
use Symfony\Component\PasswordHasher\Hasher\UserPasswordHasherInterface;
use Symfony\Component\Process\Process;
use Symfony\Component\Serializer\Encoder\CsvEncoder;
use Symfony\Component\Serializer\Serializer;
class AbonnentService
{
public const string TOKEN_COOKIE_NAME = 'uwabonnenttoken'; // attention: on change, all user will be logged out after deploy
public const int TOKEN_COOKIE_EXPIRE_IN_DAYS = 365;
private string $abonnentenImportFileLog;
public function __construct(
private readonly string $abonnentenImportFile,
private readonly EntityManagerInterface $em,
private readonly LoggerInterface $logger,
private readonly UserPasswordHasherInterface $userPasswordHasher
)
{
$this->abonnentenImportFileLog = $this->abonnentenImportFile.'.sync.log';
}
public function findAbonnenten(AbonnentDataTransformerInterface $dataTransformer, AbonnentServiceQuery $query)
{
$data_ = [];
$conn = $this->em->getConnection();
$sqlFromValues = null;
$sqlWhere = null;
$sqlOrder = null;
$sqlLimit = null;
// SQL FROM
$sqlFromValues = 'abonnent AS a';
// SQL Limit
$limit = $query->getPageSize();
$limit += ($query->getCalcTotalRows()) ? 0 : 1; // Falls nicht Total Rows berechnet werden soll, dann berechne ich NextPageExist Wert (Performance Trick einfach ein Record mehr laden, welcher unten dann wieder entfernt wird)
$offset = ($query->getPageNr() - 1) * $query->getPageSize();
$sqlLimit = 'LIMIT '.$limit.' OFFSET '.$offset;
// SQL WHERE bilden
$andWhere = [];
// SQL WHERE bilden ENDE
// SQL ORDER
if ($sort = $query->getSort()) {
// Übersetzung der ServiceQuery Fields zu den internen DB Fields
$sortFieldBridge = [
'username' => 'a.username',
];
$orderTmp = [];
foreach ($sort as $field => $order) {
$orderTmp[] = $sortFieldBridge[$field].' '.strtoupper((string) $order);
}
if ($orderTmp !== []) {
$sqlOrder = 'ORDER BY '.implode(',', $orderTmp);
}
}
// SQL ORDER ENDE
// SQL zusammenführen
$theSQL = "SELECT a.id FROM $sqlFromValues $sqlWhere $sqlOrder $sqlLimit";
// Alle Abonnenten IDs holen und in Array abspitzen
$result = $conn->fetchAllAssociative($theSQL);
foreach ($result as $id) {
$data_[$id['id']] = null; // ID's vorbelegen, damit unten korrekt einsortiert werden kann
}
// gezielt Abonnenten via Doctrine in Objekte hydrieren
if ($data_ !== []) {
$dql = $this->em->createQuery('SELECT a, tokens FROM '.Abonnent::class.' a LEFT JOIN a.tokens tokens WHERE a.id IN (:id)');
// evtl. bessere performance später: $dql = $this->em->createQuery('SELECT a, va FROM '. Artikel::class .' a LEFT JOIN a.verwandteArtikel va WHERE a.id IN (:id)');
$dql->setParameter('id', array_keys($data_));
$result = $dql->getResult();
foreach ($result as $abonnent) {
$dataTransformer->write($abonnent);
$data_[$abonnent->getId()] = $dataTransformer->read();
}
}
// Total Records (ohne Paging) berechnen
if ($query->getCalcTotalRows()) {
$theSQL = "SELECT COUNT('a.id') FROM $sqlFromValues $sqlWhere";
$totalRecords = (int) $conn->fetchOne($theSQL);
$query->setTotalRows($totalRecords);
} elseif (count($data_) > $query->getPageSize()) {
// Letzter Record wieder entfernen, welcher nur zur Berechnung des NextPageExist benötigt wurde
array_pop($data_);
$query->setPageNextExist(true);
} else {
$query->setPageNextExist(false);
}
return $data_;
}
public function getImportFilepath(): string
{
return $this->abonnentenImportFile;
}
public function getImportLogfilepath(): string
{
return $this->abonnentenImportFileLog;
}
/**
* @throws AbonnentWithThisUsernameNotExistsException|AbonnentLoginInactiveException
*/
protected function findAbonnentOrFail(
string $username,
string $password = null,
?string $token = null
): Abonnent
{
// input validation
if ($password && $token) {
throw new \InvalidArgumentException('Method only accept one of param $password or $token set');
}
$abonnent = null;
if ($password) {
$abonnent = $this->em->getRepository(Abonnent::class)->findOneBy(['username' => $username, 'isActive' => true]);
$multipleEmails = $this->em->getRepository(Abonnent::class)->findBy(['username' => $username]);
if (count($multipleEmails) && $abonnent === null) {
throw new AbonnentLoginInactiveException();
}
} elseif ($token) {
$token = $this->em->getRepository(Abonnenttoken::class)->findOneBy(['token' => $token]);
$abonnent = $token ? $token->getAbonnent() : null;
}
if (!$abonnent) {
throw new AbonnentWithThisUsernameNotExistsException();
}
return $abonnent;
}
/**
* @param mixed|null $id
*
* @return Abonnent
*
* @throws AbonnentWithThisIdNotExistsException
*/
protected function findAbonnentByIdOrFail($id): Abonnent
{
$abonnent = $this->em->getRepository(Abonnent::class)->find($id);
if (!$abonnent) {
throw new AbonnentWithThisIdNotExistsException();
}
return $abonnent;
}
/**
* @throws AbonnentWithThisUsernameNotExistsException
*/
protected function findAbonnentByUsernameOrFail(string $username): Abonnent
{
$abonnent = $this->em->getRepository(Abonnent::class)->findOneBy(['username' => $username]);
if (!$abonnent) {
throw new AbonnentWithThisUsernameNotExistsException();
}
return $abonnent;
}
/**
* Is abonnent active.
*/
public function isAuthenticated(Request $request): bool
{
return (bool) $this->authenticate($request);
}
/**
* Find and return an active, valid abonnent only.
*
* @param Request $request with token cookie in it
*
* @return Abonnent|null
*/
public function authenticate(Request $request): ?Abonnent
{
$tokenString = trim($request->cookies->get(self::TOKEN_COOKIE_NAME));
if ($tokenString === '' || $tokenString === '0') {
return null;
}
$token = $this->em->getRepository(Abonnenttoken::class)->findOneBy(['token' => $tokenString]);
if (!$token) {
return null;
}
// return abonnent only if active
if (!$token->hasExpired() && $token->getAbonnent()->isActive()) {
return $token->getAbonnent();
}
return null;
}
public function logout(Request $request): bool
{
$abonnent = $this->authenticate($request);
if ($abonnent) {
$token = $request->cookies->get(self::TOKEN_COOKIE_NAME);
$abonnent->removeToken($token);
$this->persistAbonnent($abonnent);
return true;
}
return false;
}
/**
* Abonnent login check, if ok, generate new token.
*
* @return Abonnenttoken
*/
public function login(string $username, string $password): Abonnenttoken
{
try {
$abonnent = $this->findAbonnentOrFail($username, $password);
} catch (AbonnentWithThisUsernameNotExistsException) {
throw new AbonnentLoginFailedException(sprintf('Abonnent with $username %s does not exist!', $username));
}
if (strlen($abonnent->getUsername() === 0 || $abonnent->getUsername() !== $username) !== 0) {
throw new AbonnentLoginFailedException(sprintf('Username abonnent with $username %s not correct!', $username));
}
if (!$this->userPasswordHasher->isPasswordValid($abonnent, $password)) {
throw new AbonnentLoginFailedException(sprintf('Wrong credentials for $username %s !', $abonnent->getUsername()));
}
if (!$abonnent->isActive()) {
throw new AbonnentLoginInactiveException('Abonnent is inactive. Login not allowed!');
}
// create new token
$expireAt = new \DateTime();
$expireAt->add(new \DateInterval('P'.self::TOKEN_COOKIE_EXPIRE_IN_DAYS.'D'));
try {
$token = $abonnent->createToken($expireAt);
$this->persistAbonnent($abonnent);
} catch (AbonnentToManyDevicesException) {
$abonnent->deleteAllTokens();
$token = $abonnent->createToken($expireAt);
$this->persistAbonnent($abonnent);
}
// end
return $token;
}
/**
* Import File Struktur:
*
* Username;Unique ID
* muster.franz@bluewin.ch;100
* muster2.franz2@bluewin.ch;23
* ...
* ...
* END;
*
* @return array Reportdata
*/
public function syncAbonnentenExportFileToDb(): array
{
$report = [
'sync_start' => date('d.m.Y H:i:s'),
'sync_end' => '',
'abonnenten_inserted' => [],
'abonnenten_updated' => [],
'abonnenten_activated' => [],
'abonnenten_deactivated' => [],
'abonnenten_deleted' => [],
'abonnenten_password_updated' => [],
'abonnenten_password_set' => [],
'parsed_lines' => 0,
'errors' => [],
];
$activeIds = []; // Ids from import file, which should only valid activ abonnenten ids
// get abonnenten from file
$serializer = new Serializer([], [new CsvEncoder([CsvEncoder::DELIMITER_KEY => ';', 'no_headers' => true])]);
$abonnenten = $serializer->decode(file_get_contents($this->abonnentenImportFile), 'csv');
// Backup
$name = date('Y-m-d-H-i-s_').'Online-Export.csv';
$path = dirname($this->abonnentenImportFile).'/backup/';
if (!file_exists($path)) {
mkdir($path, 0777, true);
}
copy($this->abonnentenImportFile, $path.$name);
$usernames = [];
// iterate trough abonnenten from import file
$importFileLineNr = 0; // neu 0 weil ohne header
foreach ($abonnenten as $item) {
++$importFileLineNr;
++$report['parsed_lines'];
// validate import line
if (!is_array($item) || count($item) < 2) {
$report['errors'][] = 'Line '.$importFileLineNr.': Has no valid data';
continue;
}
// get data from line
// Werte aus numerischen Indizes abrufen
$id = trim((string) $item[0]); // Index 0 für Unique ID
$username = strtolower(trim((string) $item[1])); // Index 1 für Username
// neu gibt es am Schluss kein END; mehr - lasse es aber drinnen falls es doch wieder auftauchen würde
// import file end reached
if ($username === 'end') {
break;
}
// not a valid record
if (!is_numeric($id)) {
$report['errors'][] = 'Line '.$importFileLineNr.': Id is not numeric';
continue;
}
if (!$username || !$id) {
$report['errors'][] = 'Line '.$importFileLineNr.': Username or Id is empty';
continue;
}
// Check if same email / username already processed
if (in_array($username, $usernames)) {
$report['errors'][] = 'Line '.$importFileLineNr.': ['.$id.'] Username: '.$username.' already exists ';
continue;
}
$usernames[] = $username;
try {
// update abonnent über id
$abonnent = $this->findAbonnentByIdOrFail($id);
if ($abonnent->getUsername() !== $username) {
// Wenn Benutzer mit dieser E-Mail existiert -> Benutzer löschen
$abonnentExists = $this->em->getRepository(Abonnent::class)->findOneBy(['username' => $username]);
if ($abonnentExists) {
$report['abonnenten_deleted'][] = 'Line : ['.$abonnentExists->getId().'] Username: '.$abonnentExists->getUserName().' deleted because Email is used for ['.$id.'] Username: '.$username;
// Tokens löschen da sonst Constraint Fehler
$abonnentExists->deleteAllTokens();
// Passwort requests löschen
$this->removePasswordRequests($abonnent);
// Benutzer mit gleicher E-Mail löschen
$em = $this->em;
$em->remove($abonnentExists);
$em->flush();
}
$report['abonnenten_updated'][] = '['.$id.'] '.$username.' updated username from '.$abonnent->getUsername();
$abonnent->aktualisiereAbonnent($username);
$this->persistAbonnent($abonnent);
}
if (!$abonnent->isActive()) {
$abonnent->aktualisiereAbonnent(null, 1);
$this->persistAbonnent($abonnent);
$report['abonnenten_activated'][] = '['.$id.'] '.$username;
}
} catch (AbonnentWithThisIdNotExistsException) {
try {
// update abonnent über username
$abonnent = $this->findAbonnentByUsernameOrFail($username);
if ($abonnent->getId() !== (int) $id) {
// Tokens löschen da sonst Constraint Fehler, wenn AboId geändert wird.
$abonnent->deleteAllTokens();
$this->persistAbonnent($abonnent);
// Passwort requests löschen
$this->removePasswordRequests($abonnent);
$report['abonnenten_updated'][] = '['.$id.'] '.$username.' updated AboNr from '.$abonnent->getId();
// Neue Abonummer updaten
$abonnent->setId($id);
// Passwort setzen, wenn nicht schon durch User gesetzt
if ($abonnent->getLastPasswordChange() <= new \DateTime('2022-01-01')) {
$abonnent->setPassword(
$this->userPasswordHasher->hashPassword($abonnent, $id)
);
$report['abonnenten_password_updated'][] = '['.$id.'] '.$username;
}
$this->persistAbonnent($abonnent);
}
if (!$abonnent->isActive()) {
$abonnent->aktualisiereAbonnent(null, 1);
$this->persistAbonnent($abonnent);
$report['abonnenten_activated'][] = '['.$id.'] '.$username;
}
} catch (AbonnentWithThisUsernameNotExistsException) {
// create abonnent
$abonnent = Abonnent::erstelleAbonnent($id, $username);
// Passwort initial setzen
$abonnent->setPassword(
$this->userPasswordHasher->hashPassword($abonnent, $id)
);
$this->persistAbonnent($abonnent);
$report['abonnenten_inserted'][] = '['.$id.'] '.$username;
$report['abonnenten_password_set'][] = '['.$id.'] '.$username;
}
}
// add active ids to list
$activeIds[] = $id;
}
// deactivate abonnenten not in list
$report['abonnenten_deactivated'] = $this->deactivateAbonnentenNotInList($activeIds);
$report['sync_end'] = date('d.m.Y H:i:s');
// write log to file
$this->writeSyncLog($report);
return $report;
}
/**
* Löscht Duplikate von Abonnenten mit gleicher E-Mail.
*
* @return float|int|mixed|string
*
* @throws Exception
*/
public function removeAbonnentenWithDuplicateUsernames(): mixed
{
// Manuelle Korrektur von Abo mit 2 Einträgen aber beide inaktiv.
// update abonnent set is_active = 1 where id in (1057234, 1045250, 1057630, 1058937, 1058226, 1057578, 1058526, 1058823)
$conn = $this->em->getConnection();
$sql = '
SELECT a.username,COUNT(*) as count FROM abonnent a
GROUP BY username
ORDER BY count DESC
';
$stmt = $conn->prepare($sql);
$resultSet = $stmt->executeQuery();
// returns an array of arrays (i.e. a raw data set)
$abonnenten = $resultSet->fetchAllAssociative();
// Duplicated Usernames with more than 1 occurrence
$duplicateUsernames = [];
foreach ($abonnenten as $abonnent) {
if ($abonnent['count'] > 1) {
$duplicateUsernames[] = $abonnent['username'];
}
}
// Select only inactive
$query = $this->em->createQuery('SELECT a FROM '.Abonnent::class.' a WHERE a.isActive = 0 AND a.username IN (:duplicateUsernames)');
// $query = $this->em->createQuery("SELECT a FROM ".Abonnent::class ." a WHERE a.username IN (:duplicateUsernames)");
$query->setParameters(['duplicateUsernames' => $duplicateUsernames]);
$abonnenten = $query->getResult();
$removed = [];
foreach ($abonnenten as $abonnent) {
$removed[$abonnent->getUsername()][] = $abonnent;
$this->em->remove($abonnent);
}
$this->em->flush();
return $abonnenten;
}
protected function writeSyncLog(array $report)
{
// format $report array to human-readable content
$content = '';
foreach ($report as $key => $value) {
$content .= strtoupper($key)."\n";
if (is_array($value)) {
$content .= implode("\n", $value)."\n";
} else {
$content .= $value."\n";
}
$content .= "\n\n";
}
$content .= "\n-----------------------------\n\n";
// end
// write to logfile
$fs = new Filesystem();
$fs->appendToFile($this->abonnentenImportFileLog, $content);
// rename and compress logfile if to big
if ($fs->exists($this->abonnentenImportFileLog)) {
$logfile = new \SplFileObject($this->abonnentenImportFileLog, 'r');
if ($logfile->getSize() > 5000000) {
$date = new \DateTime();
$appConsoleCommand = new Process('gzip -c '.$this->abonnentenImportFileLog.' >'.$this->abonnentenImportFileLog.'.'.$date->format('Ymd-His').'.gz');
$appConsoleCommand->run();
if ($appConsoleCommand->isSuccessful()) {
unlink($this->abonnentenImportFileLog);
} else {
throw new \Exception('Error on compress logfile '.$this->abonnentenImportFileLog.' occurred!');
}
}
}
// end
}
/**
* Deactivate abonnents where not in list.
*
* @param int[] $activeIds
*
* @return array
*/
protected function deactivateAbonnentenNotInList(array $activeIds): array
{
$abonnentenDeactivated = [];
if ($activeIds !== []) {
$q = $this->em->createQuery('SELECT a FROM '.Abonnent::class.' a WHERE a.isActive = 1 AND a.id NOT IN ('.implode(',', array_map('intval', $activeIds)).')');
$abonnenten = $q->getResult();
/**
* @var Abonnent $item
*/
foreach ($abonnenten as $item) {
$item->deaktiviereAbonnent();
$this->persistAbonnent($item);
$abonnentenDeactivated[] = '['.$item->getId().'] '.$item->getUsername();
}
}
return $abonnentenDeactivated;
}
private function persistAbonnent(Abonnent $abonnent): void
{
$em = $this->em;
$em->persist($abonnent);
$em->flush();
}
private function removePasswordRequests(Abonnent $abonnent): void
{
$resetPasswordRequests = $this->em->getRepository(ResetPasswordRequest::class)->findBy(['user' => $abonnent]);
foreach ($resetPasswordRequests as $resetPasswordRequest) {
$this->em->remove($resetPasswordRequest);
$this->em->flush();
}
}
}

src/Application/Abonnent/AbonnentService.php line 214