SupportController::executeSQL_old_reg_date() - Code Metrics - Inspection of "OCC-102 show cache history in support center #done" - OpencachingDeutschland/oc-server3 - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Pull Request — development (#829)

unknown

created 2021-05-02 20:04 UTC

SupportController::executeSQL_old_reg_date() A

↳ Parent: Project

Complexity

Conditions	1
Paths	1

Size

Total Lines

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
cc	1
nc	1
nop	1
dl	0
loc	19
rs	9.6333
c	0
b	0
f	0

<?php

declare(strict_types=1);

namespace Oc\Controller\Backend;

use Doctrine\DBAL\Connection;
use Oc\Form\SupportAdminComment;
use Oc\Form\SupportSearchCaches;
use Oc\Form\SupportSQLFlexForm;
use Oc\Repository\CacheAdoptionsRepository;
use Oc\Repository\CacheCoordinatesRepository;
use Oc\Repository\CacheLogsArchivedRepository;
use Oc\Repository\CacheReportsRepository;
use Oc\Repository\CachesRepository;
use Oc\Repository\CacheStatusModifiedRepository;
use Oc\Repository\CacheStatusRepository;
use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
use Symfony\Component\HttpFoundation\Request;
use Symfony\Component\HttpFoundation\Response;
use Symfony\Component\Routing\Annotation\Route;

/**
 * Class SupportController
 *
 * @package Oc\Controller\Backend
 */
class SupportController extends AbstractController
{
    /** @var Connection */
    private Connection $connection;


    /** @var CacheAdoptionsRepository */
    private CacheAdoptionsRepository $cacheAdoptionsRepository;

    /** @var CacheCoordinatesRepository */
    private CacheCoordinatesRepository $cacheCoordinatesRepository;

    /** @var CacheLogsArchivedRepository */
    private CacheLogsArchivedRepository $cacheLogsArchivedRepository;

    /** @var CachesRepository */
    private CachesRepository $cachesRepository;

    /** @var CacheReportsRepository */
    private CacheReportsRepository $cacheReportsRepository;

    /** @var CacheStatusModifiedRepository */
    private CacheStatusModifiedRepository $cacheStatusModifiedRepository;

    /** @var CacheStatusRepository */
    private CacheStatusRepository $cacheStatusRepository;

    /**
     * SupportController constructor.
     *
     * @param Connection $connection
     * @param CacheAdoptionsRepository $cacheAdoptionsRepository
     * @param CacheCoordinatesRepository $cacheCoordinatesRepository
     * @param CacheLogsArchivedRepository $cacheLogsArchivedRepository
     * @param CachesRepository $cachesRepository
     * @param CacheReportsRepository $cacheReportsRepository
     * @param CacheStatusModifiedRepository $cacheStatusModifiedRepository
     * @param CacheStatusRepository $cacheStatusRepository
     */
    public function __construct(
        Connection $connection,
        CacheAdoptionsRepository $cacheAdoptionsRepository,
        CacheCoordinatesRepository $cacheCoordinatesRepository,
        CacheLogsArchivedRepository $cacheLogsArchivedRepository,
        CachesRepository $cachesRepository,
        CacheReportsRepository $cacheReportsRepository,
        CacheStatusModifiedRepository $cacheStatusModifiedRepository,
        CacheStatusRepository $cacheStatusRepository
    ) {
        $this->connection = $connection;
        $this->cacheAdoptionsRepository = $cacheAdoptionsRepository;
        $this->cacheCoordinatesRepository = $cacheCoordinatesRepository;
        $this->cacheLogsArchivedRepository = $cacheLogsArchivedRepository;
        $this->cachesRepository = $cachesRepository;
        $this->cacheReportsRepository = $cacheReportsRepository;
        $this->cacheStatusModifiedRepository = $cacheStatusModifiedRepository;
        $this->cacheStatusRepository = $cacheStatusRepository;
    }

    /**
     * @return Response
     * @Route("/support", name="support_index")
     */
    public function index()
    : Response
    {
        return $this->render('backend/support/index.html.twig');
    }

    /**
     * @param Request $request
     *
     * @return Response
     * @Route("/supportSearch", name="support_search")
     */
    public function searchCachesAndUser(Request $request)
    : Response {
        $fetchedCaches = '';
        $limit = false;

        $formSearch = $this->createForm(SupportSearchCaches::class);

        $formSearch->handleRequest($request);
        if ($formSearch->isSubmitted() && $formSearch->isValid()) {
            $inputData = $formSearch->getData();

            if ($formSearch->getClickedButton() === $formSearch->get('search_One')) {
                $limit = true;
            }

            $fetchedCaches = $this->getCachesForSearchField($inputData['content_support_searchfield'], $limit);
        }

        return $this->render(
            'backend/support/searchedCaches.html.twig', [
                                                          'supportCachesForm' => $formSearch->createView(),
                                                          'foundCaches' => $fetchedCaches
                                                      ]
        );
    }

    /**
     * @return Response
     * @throws \Oc\Repository\Exception\RecordsNotFoundException
     * @Route("/reportedCaches", name="support_reported_caches")
     */
    public function listReportedCaches()
    : Response
    {
        $fetchedReports = $this->getReportedCaches();

        $formSearch = $this->createForm(SupportSearchCaches::class);

        return $this->render(
            'backend/support/reportedCaches.html.twig', [
                                                          'supportCachesForm' => $formSearch->createView(),
                                                          'reportedCaches_by_id' => $fetchedReports
                                                      ]
        );
    }

    /**
     * @param Request $request
     *
     * @return Response
     * @Route("/dbQueries", name="support_db_queries")
     */
    public function listDbQueries(Request $request)
    : Response {
        $fetchedInformation = [];

        $formSearch = $this->createForm(SupportSearchCaches::class);

        $form = $this->createForm(SupportSQLFlexForm::class);

        $form->handleRequest($request);

        if ($form->isSubmitted() && $form->isValid()) {
            $inputData = $form->getData();

            $fetchedInformation = $this->executeSQL_flexible($inputData['content_WHAT'], $inputData['content_TABLE']);

            $countFetched = count($fetchedInformation);
            for ($i = 0; $i < $countFetched; $i ++) {
                if (array_key_exists('password', $fetchedInformation[$i])) {
                    $fetchedInformation[$i]['password'] = '-';
                }
                if (array_key_exists('logpw', $fetchedInformation[$i])) {
                    $fetchedInformation[$i]['logpw'] = '-';
                }
                if (array_key_exists('admin_password', $fetchedInformation[$i])) {
                    $fetchedInformation[$i]['admin_password'] = '-';
                }
            }
        }

        return $this->render(
            'backend/support/databaseQueries.html.twig', [
                                                           'supportCachesForm' => $formSearch->createView(),
                                                           'SQLFlexForm' => $form->createView(),
                                                           'suppSQLqueryFlex' => $fetchedInformation
                                                       ]
        );
    }

    /**
     * @param int $repID
     *
     * @return Response
     * @throws \Oc\Repository\Exception\RecordNotFoundException
     * @throws \Oc\Repository\Exception\RecordsNotFoundException
     * @Route("/repCaches/{repID}", name="support_reported_cache")
     */
    public function list_reported_cache_details(int $repID)
    : Response {
        $formSearch = $this->createForm(SupportSearchCaches::class);
        $formComment = $this->createForm(SupportAdminComment::class);

        $fetchedReport = $this->cacheReportsRepository->fetchOneBy(['id' => $repID]);

        $fetchedStatus = $this->cacheStatusRepository->fetchAll();

        $fetchedStatusModfied = $this->cacheStatusModifiedRepository->fetchBy(['cache_id' => $fetchedReport->cacheid]);

        return $this->render(
            'backend/support/reportedCacheDetails.html.twig', [
                                                                'supportCachesForm' => $formSearch->createView(),
                                                                'supportAdminCommentForm' => $formComment->createView(),
                                                                'reported_cache_by_id' => $fetchedReport,
                                                                'cache_status' => $fetchedStatus,
                                                                'report_status_modified' => $fetchedStatusModfied
                                                            ]
        );
    }

    /**
     * @param Request $request
     *
     * @return Response
     * @throws \Doctrine\DBAL\DBALException
     * @throws \Oc\Repository\Exception\RecordNotFoundException
     * @throws \Oc\Repository\Exception\RecordNotPersistedException
     * @Route("/repCachesSaveText", name="support_reported_cache_save_text")
     */
    public function repCaches_saveTextArea(Request $request)
    : Response {
        $form = $this->createForm(SupportAdminComment::class)->handleRequest($request);

        if ($form->isSubmitted() && $form->isValid()) {
            $inputData = $form->getData();

            $entity = $this->cacheReportsRepository->fetchOneBy(['id' => (int) $inputData['hidden_repID']]);
            $entity->comment = $inputData['support_admin_comment'];

            $this->cacheReportsRepository->update($entity);

            return $this->redirectToRoute('backend_support_reported_cache', ['repID' => $entity->id]);
        }

        return $this->redirectToRoute('backend_support_reported_caches');
    }

    /**
     * @param string $wpID
     *
     * @return Response
     * @throws \Oc\Repository\Exception\RecordNotFoundException
     * @throws \Oc\Repository\Exception\RecordsNotFoundException
     * @throws \Exception
     * @Route("/cacheHistory/{wpID}", name="support_cache_history")
     */
    public function list_cache_history(string $wpID)
    : Response {
        $formSearch = $this->createForm(SupportSearchCaches::class);

        $fetchedId = $this->cachesRepository->getIdByWP($wpID);

        $fetchedReports = $this->cacheReportsRepository->fetchBy(['cacheid' => $fetchedId]);

        $fetchedLogDeletes = $this->cacheLogsArchivedRepository->fetchBy(['cache_id' => $fetchedId]);

        $fetchedStatusModfied = $this->cacheStatusModifiedRepository->fetchBy(['cache_id' => $fetchedId]);

        $fetchedCoordinates = $this->cacheCoordinatesRepository->fetchBy(['cache_id' => $fetchedId]);

        $fetchedAdoptions = $this->cacheAdoptionsRepository->fetchBy(['cache_id' => $fetchedId]);

        return $this->render(
            'backend/support/cacheHistory.html.twig', [
                                                        'supportCachesForm' => $formSearch->createView(),
                                                        'cache_reports' => $fetchedReports,
                                                        'deleted_logs' => $fetchedLogDeletes,
                                                        'report_status_modified' => $fetchedStatusModfied,
                                                        'changed_coordinates' => $fetchedCoordinates,
                                                        'cache_adoptions' => $fetchedAdoptions,
                                                    ]
        );
    }

    /**
     * @param string $searchtext
     * @param bool $limit
     *
     * @return array
     */
    public function getCachesForSearchField(string $searchtext, bool $limit = false)
    : array {
        //      so sieht die SQL-Vorlage aus..
        //        SELECT name, wp_oc, wp_gc, wp_gc_maintained, user.username, user.email
        //        FROM caches
        //        INNER JOIN user ON caches.user_id = user.user_id
        //        WHERE wp_oc         =       "' . $searchtext . '"
        //        OR wp_gc            =       "' . $searchtext . '"
        //        OR wp_gc_maintained =       "' . $searchtext . '"
        //        OR caches.name     LIKE    "%' . $searchtext . '%"'
        //        OR user.username   LIKE    "%' . $searchtext . '%"'
        //        OR user.email      LIKE    "%' . $searchtext . '%"'
        //        LIMIT $limit
        $qb = $this->connection->createQueryBuilder();
        $qb->select('caches.name', 'caches.wp_oc', 'caches.wp_gc', 'caches.wp_gc_maintained', 'user.username', 'user.email')
            ->from('caches')
            ->innerJoin('caches', 'user', 'user', 'caches.user_id = user.user_id')
            ->where('caches.wp_oc = :searchTerm')
            ->orWhere('caches.wp_gc = :searchTerm')
            ->orWhere('caches.wp_gc_maintained = :searchTerm')
            ->orWhere('caches.name LIKE :searchTermLIKE')
            ->orWhere('user.username LIKE :searchTermLIKE')
            ->orWhere('user.email LIKE :searchTermLIKE')
            ->setParameters(['searchTerm' => $searchtext, 'searchTermLIKE' => '%' . $searchtext . '%'])
            ->orderBy('caches.wp_oc', 'ASC');

        if ($limit === true) {
            $qb->setMaxResults(1);
        }

        return $qb->execute()->fetchAll();
    }

    /**
     * @return array
     * @throws \Oc\Repository\Exception\RecordsNotFoundException
     */
    public function getReportedCaches()
    : array
    {
        return $this->cacheReportsRepository->fetchAll();
    }

    /**
     * @param int $days
     *
     * @return Response
     * @Route("/dbQueries1/{days}", name="support_db_queries_1")
     */
    public function executeSQL_caches_old_reg_date(int $days = 31) // List caches from users whose registration date is not older than x days.
    : Response
    {
        $formSearch = $this->createForm(SupportSearchCaches::class);

        $qb = $this->connection->createQueryBuilder();
        $qb->select('caches.name', 'user.username', 'user.date_created', 'user.last_login')
            ->from('caches')
            ->innerJoin('caches', 'user', 'user', 'caches.user_id = user.user_id')
            ->where('user.date_created > now() - interval :searchTerm DAY')
            ->andWhere('caches.user_id = user.user_id')
            ->setParameters(['searchTerm' => $days])
            ->orderBy('date_created', 'DESC');

        return $this->render(
            'backend/support/databaseQueries.html.twig', [
                                                           'supportCachesForm' => $formSearch->createView(),
                                                           'suppSQLquery1' => $qb->execute()->fetchAll()
                                                       ]
        );
    }

    /**
     * @param int $days
     *
     * @return Response
     * @Route("/dbQueries2/{days}", name="support_db_queries_2")
     */
    public function executeSQL_old_reg_date(int $days) // List user whose registration date is no older than x days.
    : Response
    {
        $formSearch = $this->createForm(SupportSearchCaches::class);

        $qb = $this->connection->createQueryBuilder();
        $qb->select('username', 'date_created', 'last_login')
            ->from('user')
            ->where('date_created > now() - interval :searchTerm DAY')
            ->setParameters(['searchTerm' => $days])
            ->orderBy('date_created', 'DESC');

        return $this->render(
            'backend/support/databaseQueries.html.twig', [
                                                           'supportCachesForm' => $formSearch->createView(),
                                                           'suppSQLquery2' => $qb->execute()->fetchAll()
                                                       ]
        );
    }

    /**
     * @return Response
     * @Route("/dbQueries4", name="support_db_queries_4")
     */
    public function executeSQL_caches_old_login_date(
    ) // List (non-archived, non-locked) caches from users whose last login date is older than one year.
    : Response
    {
        $formSearch = $this->createForm(SupportSearchCaches::class);

        $qb = $this->connection->createQueryBuilder();
        $qb->select('caches.name', 'caches.cache_id', 'caches.status', 'user.username', 'user.last_login')
            ->from('caches')
            ->innerJoin('caches', 'user', 'user', 'caches.user_id = user.user_id')
            ->where('user.last_login < now() - interval :searchTerm YEAR')
            ->andWhere('caches.status <= 2')
            ->andWhere(('caches.user_id = user.user_id'))
            ->setParameters(['searchTerm' => 1])
            ->orderBy('user.last_login', 'ASC');

        return $this->render(
            'backend/support/databaseQueries.html.twig', [
                                                           'supportCachesForm' => $formSearch->createView(),
                                                           'suppSQLquery4' => $qb->execute()->fetchAll()
                                                       ]
        );
    }

    /**
     * @param string $what
     * @param string $table
     *
     * @return array
     */
    public function executeSQL_flexible(string $what, string $table)
    : array {
        $qb = $this->connection->createQueryBuilder();
        $qb->select($what)
            ->from($table);

        return ($qb->execute()->fetchAll());
    }
}


1			<?php
2
3			declare(strict_types=1);
4
5			namespace Oc\Controller\Backend;
6
7			use Doctrine\DBAL\Connection;
8			use Oc\Form\SupportAdminComment;
9			use Oc\Form\SupportSearchCaches;
10			use Oc\Form\SupportSQLFlexForm;
11			use Oc\Repository\CacheAdoptionsRepository;
12			use Oc\Repository\CacheCoordinatesRepository;
13			use Oc\Repository\CacheLogsArchivedRepository;
14			use Oc\Repository\CacheReportsRepository;
15			use Oc\Repository\CachesRepository;
16			use Oc\Repository\CacheStatusModifiedRepository;
17			use Oc\Repository\CacheStatusRepository;
18			use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
19			use Symfony\Component\HttpFoundation\Request;
20			use Symfony\Component\HttpFoundation\Response;
21			use Symfony\Component\Routing\Annotation\Route;
22
23			/**
24			* Class SupportController
25			*
26			* @package Oc\Controller\Backend
27			*/
28			class SupportController extends AbstractController
29			{
30			/** @var Connection */
31			private Connection $connection;
			0 ignored issues – show Bug introduced 2021-05-02 20:08 UTC by Report Bug Copy Issue Report This code did not parse for me. Apparently, there is an error somewhere around this line: Syntax error, unexpected T_STRING, expecting T_FUNCTION or T_CONST Loading history...
32
33			/** @var CacheAdoptionsRepository */
34			private CacheAdoptionsRepository $cacheAdoptionsRepository;
35
36			/** @var CacheCoordinatesRepository */
37			private CacheCoordinatesRepository $cacheCoordinatesRepository;
38
39			/** @var CacheLogsArchivedRepository */
40			private CacheLogsArchivedRepository $cacheLogsArchivedRepository;
41
42			/** @var CachesRepository */
43			private CachesRepository $cachesRepository;
44
45			/** @var CacheReportsRepository */
46			private CacheReportsRepository $cacheReportsRepository;
47
48			/** @var CacheStatusModifiedRepository */
49			private CacheStatusModifiedRepository $cacheStatusModifiedRepository;
50
51			/** @var CacheStatusRepository */
52			private CacheStatusRepository $cacheStatusRepository;
53
54			/**
55			* SupportController constructor.
56			*
57			* @param Connection $connection
58			* @param CacheAdoptionsRepository $cacheAdoptionsRepository
59			* @param CacheCoordinatesRepository $cacheCoordinatesRepository
60			* @param CacheLogsArchivedRepository $cacheLogsArchivedRepository
61			* @param CachesRepository $cachesRepository
62			* @param CacheReportsRepository $cacheReportsRepository
63			* @param CacheStatusModifiedRepository $cacheStatusModifiedRepository
64			* @param CacheStatusRepository $cacheStatusRepository
65			*/
66			public function __construct(
67			Connection $connection,
68			CacheAdoptionsRepository $cacheAdoptionsRepository,
69			CacheCoordinatesRepository $cacheCoordinatesRepository,
70			CacheLogsArchivedRepository $cacheLogsArchivedRepository,
71			CachesRepository $cachesRepository,
72			CacheReportsRepository $cacheReportsRepository,
73			CacheStatusModifiedRepository $cacheStatusModifiedRepository,
74			CacheStatusRepository $cacheStatusRepository
75			) {
76			$this->connection = $connection;
77			$this->cacheAdoptionsRepository = $cacheAdoptionsRepository;
78			$this->cacheCoordinatesRepository = $cacheCoordinatesRepository;
79			$this->cacheLogsArchivedRepository = $cacheLogsArchivedRepository;
80			$this->cachesRepository = $cachesRepository;
81			$this->cacheReportsRepository = $cacheReportsRepository;
82			$this->cacheStatusModifiedRepository = $cacheStatusModifiedRepository;
83			$this->cacheStatusRepository = $cacheStatusRepository;
84			}
85
86			/**
87			* @return Response
88			* @Route("/support", name="support_index")
89			*/
90			public function index()
91			: Response
92			{
93			return $this->render('backend/support/index.html.twig');
94			}
95
96			/**
97			* @param Request $request
98			*
99			* @return Response
100			* @Route("/supportSearch", name="support_search")
101			*/
102			public function searchCachesAndUser(Request $request)
103			: Response {
104			$fetchedCaches = '';
105			$limit = false;
106
107			$formSearch = $this->createForm(SupportSearchCaches::class);
108
109			$formSearch->handleRequest($request);
110			if ($formSearch->isSubmitted() && $formSearch->isValid()) {
111			$inputData = $formSearch->getData();
112
113			if ($formSearch->getClickedButton() === $formSearch->get('search_One')) {
114			$limit = true;
115			}
116
117			$fetchedCaches = $this->getCachesForSearchField($inputData['content_support_searchfield'], $limit);
118			}
119
120			return $this->render(
121			'backend/support/searchedCaches.html.twig', [
122			'supportCachesForm' => $formSearch->createView(),
123			'foundCaches' => $fetchedCaches
124			]
125			);
126			}
127
128			/**
129			* @return Response
130			* @throws \Oc\Repository\Exception\RecordsNotFoundException
131			* @Route("/reportedCaches", name="support_reported_caches")
132			*/
133			public function listReportedCaches()
134			: Response
135			{
136			$fetchedReports = $this->getReportedCaches();
137
138			$formSearch = $this->createForm(SupportSearchCaches::class);
139
140			return $this->render(
141			'backend/support/reportedCaches.html.twig', [
142			'supportCachesForm' => $formSearch->createView(),
143			'reportedCaches_by_id' => $fetchedReports
144			]
145			);
146			}
147
148			/**
149			* @param Request $request
150			*
151			* @return Response
152			* @Route("/dbQueries", name="support_db_queries")
153			*/
154			public function listDbQueries(Request $request)
155			: Response {
156			$fetchedInformation = [];
157
158			$formSearch = $this->createForm(SupportSearchCaches::class);
159
160			$form = $this->createForm(SupportSQLFlexForm::class);
161
162			$form->handleRequest($request);
163
164			if ($form->isSubmitted() && $form->isValid()) {
165			$inputData = $form->getData();
166
167			$fetchedInformation = $this->executeSQL_flexible($inputData['content_WHAT'], $inputData['content_TABLE']);
168
169			$countFetched = count($fetchedInformation);
170			for ($i = 0; $i < $countFetched; $i ++) {
171			if (array_key_exists('password', $fetchedInformation[$i])) {
172			$fetchedInformation[$i]['password'] = '-';
173			}
174			if (array_key_exists('logpw', $fetchedInformation[$i])) {
175			$fetchedInformation[$i]['logpw'] = '-';
176			}
177			if (array_key_exists('admin_password', $fetchedInformation[$i])) {
178			$fetchedInformation[$i]['admin_password'] = '-';
179			}
180			}
181			}
182
183			return $this->render(
184			'backend/support/databaseQueries.html.twig', [
185			'supportCachesForm' => $formSearch->createView(),
186			'SQLFlexForm' => $form->createView(),
187			'suppSQLqueryFlex' => $fetchedInformation
188			]
189			);
190			}
191
192			/**
193			* @param int $repID
194			*
195			* @return Response
196			* @throws \Oc\Repository\Exception\RecordNotFoundException
197			* @throws \Oc\Repository\Exception\RecordsNotFoundException
198			* @Route("/repCaches/{repID}", name="support_reported_cache")
199			*/
200			public function list_reported_cache_details(int $repID)
201			: Response {
202			$formSearch = $this->createForm(SupportSearchCaches::class);
203			$formComment = $this->createForm(SupportAdminComment::class);
204
205			$fetchedReport = $this->cacheReportsRepository->fetchOneBy(['id' => $repID]);
206
207			$fetchedStatus = $this->cacheStatusRepository->fetchAll();
208
209			$fetchedStatusModfied = $this->cacheStatusModifiedRepository->fetchBy(['cache_id' => $fetchedReport->cacheid]);
210
211			return $this->render(
212			'backend/support/reportedCacheDetails.html.twig', [
213			'supportCachesForm' => $formSearch->createView(),
214			'supportAdminCommentForm' => $formComment->createView(),
215			'reported_cache_by_id' => $fetchedReport,
216			'cache_status' => $fetchedStatus,
217			'report_status_modified' => $fetchedStatusModfied
218			]
219			);
220			}
221
222			/**
223			* @param Request $request
224			*
225			* @return Response
226			* @throws \Doctrine\DBAL\DBALException
227			* @throws \Oc\Repository\Exception\RecordNotFoundException
228			* @throws \Oc\Repository\Exception\RecordNotPersistedException
229			* @Route("/repCachesSaveText", name="support_reported_cache_save_text")
230			*/
231			public function repCaches_saveTextArea(Request $request)
232			: Response {
233			$form = $this->createForm(SupportAdminComment::class)->handleRequest($request);
234
235			if ($form->isSubmitted() && $form->isValid()) {
236			$inputData = $form->getData();
237
238			$entity = $this->cacheReportsRepository->fetchOneBy(['id' => (int) $inputData['hidden_repID']]);
239			$entity->comment = $inputData['support_admin_comment'];
240
241			$this->cacheReportsRepository->update($entity);
242
243			return $this->redirectToRoute('backend_support_reported_cache', ['repID' => $entity->id]);
244			}
245
246			return $this->redirectToRoute('backend_support_reported_caches');
247			}
248
249			/**
250			* @param string $wpID
251			*
252			* @return Response
253			* @throws \Oc\Repository\Exception\RecordNotFoundException
254			* @throws \Oc\Repository\Exception\RecordsNotFoundException
255			* @throws \Exception
256			* @Route("/cacheHistory/{wpID}", name="support_cache_history")
257			*/
258			public function list_cache_history(string $wpID)
259			: Response {
260			$formSearch = $this->createForm(SupportSearchCaches::class);
261
262			$fetchedId = $this->cachesRepository->getIdByWP($wpID);
263
264			$fetchedReports = $this->cacheReportsRepository->fetchBy(['cacheid' => $fetchedId]);
265
266			$fetchedLogDeletes = $this->cacheLogsArchivedRepository->fetchBy(['cache_id' => $fetchedId]);
267
268			$fetchedStatusModfied = $this->cacheStatusModifiedRepository->fetchBy(['cache_id' => $fetchedId]);
269
270			$fetchedCoordinates = $this->cacheCoordinatesRepository->fetchBy(['cache_id' => $fetchedId]);
271
272			$fetchedAdoptions = $this->cacheAdoptionsRepository->fetchBy(['cache_id' => $fetchedId]);
273
274			return $this->render(
275			'backend/support/cacheHistory.html.twig', [
276			'supportCachesForm' => $formSearch->createView(),
277			'cache_reports' => $fetchedReports,
278			'deleted_logs' => $fetchedLogDeletes,
279			'report_status_modified' => $fetchedStatusModfied,
280			'changed_coordinates' => $fetchedCoordinates,
281			'cache_adoptions' => $fetchedAdoptions,
282			]
283			);
284			}
285
286			/**
287			* @param string $searchtext
288			* @param bool $limit
289			*
290			* @return array
291			*/
292			public function getCachesForSearchField(string $searchtext, bool $limit = false)
293			: array {
294			// so sieht die SQL-Vorlage aus..
295			// SELECT name, wp_oc, wp_gc, wp_gc_maintained, user.username, user.email
296			// FROM caches
297			// INNER JOIN user ON caches.user_id = user.user_id
298			// WHERE wp_oc = "' . $searchtext . '"
299			// OR wp_gc = "' . $searchtext . '"
300			// OR wp_gc_maintained = "' . $searchtext . '"
301			// OR caches.name LIKE "%' . $searchtext . '%"'
302			// OR user.username LIKE "%' . $searchtext . '%"'
303			// OR user.email LIKE "%' . $searchtext . '%"'
304			// LIMIT $limit
305			$qb = $this->connection->createQueryBuilder();
306			$qb->select('caches.name', 'caches.wp_oc', 'caches.wp_gc', 'caches.wp_gc_maintained', 'user.username', 'user.email')
307			->from('caches')
308			->innerJoin('caches', 'user', 'user', 'caches.user_id = user.user_id')
309			->where('caches.wp_oc = :searchTerm')
310			->orWhere('caches.wp_gc = :searchTerm')
311			->orWhere('caches.wp_gc_maintained = :searchTerm')
312			->orWhere('caches.name LIKE :searchTermLIKE')
313			->orWhere('user.username LIKE :searchTermLIKE')
314			->orWhere('user.email LIKE :searchTermLIKE')
315			->setParameters(['searchTerm' => $searchtext, 'searchTermLIKE' => '%' . $searchtext . '%'])
316			->orderBy('caches.wp_oc', 'ASC');
317
318			if ($limit === true) {
319			$qb->setMaxResults(1);
320			}
321
322			return $qb->execute()->fetchAll();
323			}
324
325			/**
326			* @return array
327			* @throws \Oc\Repository\Exception\RecordsNotFoundException
328			*/
329			public function getReportedCaches()
330			: array
331			{
332			return $this->cacheReportsRepository->fetchAll();
333			}
334
335			/**
336			* @param int $days
337			*
338			* @return Response
339			* @Route("/dbQueries1/{days}", name="support_db_queries_1")
340			*/
341			public function executeSQL_caches_old_reg_date(int $days = 31) // List caches from users whose registration date is not older than x days.
342			: Response
343			{
344			$formSearch = $this->createForm(SupportSearchCaches::class);
345
346			$qb = $this->connection->createQueryBuilder();
347			$qb->select('caches.name', 'user.username', 'user.date_created', 'user.last_login')
348			->from('caches')
349			->innerJoin('caches', 'user', 'user', 'caches.user_id = user.user_id')
350			->where('user.date_created > now() - interval :searchTerm DAY')
351			->andWhere('caches.user_id = user.user_id')
352			->setParameters(['searchTerm' => $days])
353			->orderBy('date_created', 'DESC');
354
355			return $this->render(
356			'backend/support/databaseQueries.html.twig', [
357			'supportCachesForm' => $formSearch->createView(),
358			'suppSQLquery1' => $qb->execute()->fetchAll()
359			]
360			);
361			}
362
363			/**
364			* @param int $days
365			*
366			* @return Response
367			* @Route("/dbQueries2/{days}", name="support_db_queries_2")
368			*/
369			public function executeSQL_old_reg_date(int $days) // List user whose registration date is no older than x days.
370			: Response
371			{
372			$formSearch = $this->createForm(SupportSearchCaches::class);
373
374			$qb = $this->connection->createQueryBuilder();
375			$qb->select('username', 'date_created', 'last_login')
376			->from('user')
377			->where('date_created > now() - interval :searchTerm DAY')
378			->setParameters(['searchTerm' => $days])
379			->orderBy('date_created', 'DESC');
380
381			return $this->render(
382			'backend/support/databaseQueries.html.twig', [
383			'supportCachesForm' => $formSearch->createView(),
384			'suppSQLquery2' => $qb->execute()->fetchAll()
385			]
386			);
387			}
388
389			/**
390			* @return Response
391			* @Route("/dbQueries4", name="support_db_queries_4")
392			*/
393			public function executeSQL_caches_old_login_date(
394			) // List (non-archived, non-locked) caches from users whose last login date is older than one year.
395			: Response
396			{
397			$formSearch = $this->createForm(SupportSearchCaches::class);
398
399			$qb = $this->connection->createQueryBuilder();
400			$qb->select('caches.name', 'caches.cache_id', 'caches.status', 'user.username', 'user.last_login')
401			->from('caches')
402			->innerJoin('caches', 'user', 'user', 'caches.user_id = user.user_id')
403			->where('user.last_login < now() - interval :searchTerm YEAR')
404			->andWhere('caches.status <= 2')
405			->andWhere(('caches.user_id = user.user_id'))
406			->setParameters(['searchTerm' => 1])
407			->orderBy('user.last_login', 'ASC');
408
409			return $this->render(
410			'backend/support/databaseQueries.html.twig', [
411			'supportCachesForm' => $formSearch->createView(),
412			'suppSQLquery4' => $qb->execute()->fetchAll()
413			]
414			);
415			}
416
417			/**
418			* @param string $what
419			* @param string $table
420			*
421			* @return array
422			*/
423			public function executeSQL_flexible(string $what, string $table)
424			: array {
425			$qb = $this->connection->createQueryBuilder();
426			$qb->select($what)
427			->from($table);
428
429			return ($qb->execute()->fetchAll());
430			}
431			}
432

OpencachingDeutschland / oc-server3

Pull Request — development (#829)

SupportController::executeSQL_old_reg_date() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like