Symfony Profiler

<?php
namespace OceanExpertBundle\Controller;
use Doctrine\ORM\AbstractQuery;
use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
use Symfony\Component\HttpFoundation\JsonResponse;
use Symfony\Component\HttpFoundation\Request;
use Symfony\Component\HttpFoundation\Response;
use Symfony\Component\Security\Core\Exception\AuthenticationException;
use Symfony\Component\Security\Core\Security;
use Symfony\Component\Security\Core\SecurityContextInterface;
class DefaultController extends AbstractController
{
/**
* @param Request $request
*
* @return Response
*/
public function indexAction(Request $request): Response
{
// this should be handled already by nelmio_cors package
// see config/packages/nelmio_cors.yaml
// see https://github.com/iobis/web/issues/133
// header('Access-Control-Allow-Origin: *');
$session = $request->getSession();
$authErrorKey = Security::AUTHENTICATION_ERROR;
$lastUsernameKey = Security::LAST_USERNAME;
// get the error if any (works with forward and redirect -- see below)
if ($request->attributes->has($authErrorKey)) {
$error = $request->attributes->get($authErrorKey);
} elseif (null !== $session
&& $session->has($authErrorKey)
) {
$error = $session->get($authErrorKey);
$session->remove($authErrorKey);
} else {
$error = null;
}
if (!$error instanceof AuthenticationException) {
$error = null; // The value does not come from the security component.
}
// last username entered by the user
$lastUsername = (null === $session) ? '' : $session->get($lastUsernameKey);
if ($request->attributes->has($authErrorKey)) {
$error = $request->attributes->get($authErrorKey);
} elseif (null !== $session
&& $session->has($authErrorKey)
) {
$error = $session->get($authErrorKey);
$session->remove($authErrorKey);
} else {
$error = null;
}
if ($this->has('security.csrf.token_manager')) {
$csrfToken = $this->get('security.csrf.token_manager')
->getToken('authenticate')
->getValue();
} else {
// BC for SF < 2.4
$csrfToken = $this->has('form.csrf_provider')
? $this->get('form.csrf_provider')
->generateCsrfToken('authenticate')
: null;
}
$indSeaAreas = array();
$instSeaAreas = array();
$subjects = array();
$instType = array();
$resultLname = array();
$countryList = array();
$instNameList = array();
$instCountryList = array();
$searchOptions['indSeas'] = $indSeaAreas;
$searchOptions['instSeas'] = $instSeaAreas;
$searchOptions['subjects'] = $subjects;
$searchOptions['instType'] = $instType;
$searchOptions['expertLname'] = $resultLname;
$searchOptions['countryNames'] = $countryList;
$searchOptions['instName'] = $instNameList;
$searchOptions['instCountryList'] = $instCountryList;
$noresult = array();
if (null !== $request->attributes->get('noresult')) {
$noresult['message'] = $request->attributes->get('noresult');
$noresult['query'] = $request->attributes->get('query');
}
return $this->render(
'index.html.twig',
array(
'last_username' => $lastUsername,
'error' => $error,
'csrf_token' => $csrfToken,
'searchOptions' => $searchOptions,
'noresult' => $noresult,
)
);
}
/**
* show some stats about OE
* - number of experts
* - number of institutes
* - number of events
* - number of documents
*
* @return Response
*/
public function statisticsAction(): Response
{
//get info about the countries
$countryInfo = array();
$countries = $this->getDoctrine()
->getManager()
->getRepository('OceanExpertBundle:Countries')
->findAll();
$data = array();
$em = $this->getDoctrine()->getManager();
//info about the number of experts
$qb = $em->createQueryBuilder();
$expr = $qb->expr();
$qb->select(
'count(i.idInd)'
)->from(
'OceanExpertBundle:Indiv',
'i'
)->where(
'i.status = 1'
)->andWhere(
$expr->neq('i.deceased', 1)
);
$data['countExperts'] = $qb->getQuery()->getSingleScalarResult();
//info about the number of experts per country
//this is done in 2 steps as we need those with their own address
//and those that use the address of their institute
$data['expertsPerCountry'] = $this->getExpertsPerCountry();
//info about the number of institutes
$qb = $em->createQueryBuilder();
$qb->select('count(i.idInst)');
$qb->from('OceanExpertBundle:Institutions', 'i');
$qb->where('i.activated = 1');
$data['countInstitutes'] = $qb->getQuery()->getSingleScalarResult();
//info about the number of events
$qb = $em->createQueryBuilder();
$qb->select('count(e.idEvent)');
$qb->from('OceanExpertBundle:Events', 'e');
$qb->where('e.status = 1');
$data['countEvents'] = $qb->getQuery()->getSingleScalarResult();
//info about the number of documents
$qb = $em->createQueryBuilder();
$qb->select('count(d.idDoc)');
$qb->from('OceanExpertBundle:Documents', 'd');
$qb->where('d.approved = 1');
$data['countDocuments'] = $qb->getQuery()->getSingleScalarResult();
return $this->render(
'statistics.html.twig',
array(
'data' => $data
)
);
}
/**
* show the disclaimer
*
* @return Response
*/
public function disclaimerAction(): Response
{
return $this->render('Static/disclaimer.html.twig');
}
/**
* download the list of experts per country as CSV
*
* @return Response
**/
public function downloadExpertsPerCountryAction()
{
$expertsPerCountry = $this->getExpertsPerCountry();
$response = new Response();
$response->headers->set('Content-Type', 'text/csv; charset=utf-8');
$response->headers->set(
'Content-Disposition',
'attachment; filename="experts_per_country.csv"'
);
$fp = fopen('php://output', 'w');
fputcsv($fp, array('Country', 'Number of experts'));
foreach ($expertsPerCountry as $countryId => $expert ) {
fputcsv($fp, array($expert['countryName'], $expert['nrOfExperts']));
}
fclose($fp);
return $response;
}
/**
* create the json files that are needed to make the map on the homepage
* - experts
* - institutes
*
* @return Response
*/
public function createMapJsonAction(): Response
{
$em = $this->get('doctrine')->getManager();
$connection = $em->getConnection();
$stmtInd = $connection->prepare(
"SELECT (
CASE
WHEN i.use_inst_addr = 1
THEN inst.country_code
ELSE i.country_code END
) AS country_id
FROM indiv i
LEFT JOIN indiv_institution iinst ON i.id_ind = iinst.id_ind
LEFT JOIN institutions inst ON inst.id_inst = iinst.id_inst
WHERE i.status = 1
ORDER BY country_id ASC"
);
$stmtInd->execute();
$indiv = $stmtInd->fetchAll();
$countries = array();
foreach ($indiv as $value) {
if ((trim($value['country_id']) != '')
&& ($value['country_id']) != 0
) {
$countries[] = $value['country_id'];
}
}
$count = array_count_values($countries);
$statement = $connection->prepare(
"SELECT
latitude,
longitude,
country,
id_country
FROM countries"
);
$statement->execute();
$countryList = $statement->fetchAll();
$countryData = array();
foreach ($countryList as $value) {
$countryData[$value['id_country']]['latitude'] = $value['latitude'];
$countryData[$value['id_country']]['longitude'] = $value['longitude'];
$countryData[$value['id_country']]['country'] = $value['country'];
if (!isset($count[$value['id_country']])) {
$count[$value['id_country']] = 0;
}
$countryData[$value['id_country']]['count'] = $count[$value['id_country']];
}
//we want to put the files here
$filePath = $this->get('kernel')->getProjectDir();
$filePath .= '/web/js/';
//make the experts json file
$fp = fopen($filePath . 'experts.json', 'w');
fwrite($fp, "var experts = [");
fwrite($fp, json_encode($countryData));
fwrite($fp, "]");
fclose($fp);
$statement = $connection->prepare("
SELECT
latitude,
longitude,
country,
count(*) AS count
FROM institutions
LEFT JOIN countries ON institutions.country_code = countries.id_country
WHERE activated = 1
AND country IS NOT NULL
GROUP BY country");
$statement->execute();
$countryList = $statement->fetchAll();
$fp = fopen($filePath . 'institutes.json', 'w');
fwrite($fp, "var institutes = [");
fwrite($fp, json_encode($countryList));
fwrite($fp, "]");
fclose($fp);
$response = "Generated <a href='/js/experts.json'>experts.json</a> and <a href='/js/institutes.json'>institutes.json</a>";
return new Response($response);
}
/**
* give the number of experts per searegion
*
* @return JsonResponse
*/
public function getExpertsSeaRegionsAction(): JsonResponse
{
$repository = $this->getDoctrine()->getRepository('OceanExpertBundle:Indiv');
$seaRegionsInd = $repository->createQueryBuilder('i')
->select('DISTINCT i.studyregion')
->getQuery()
->getResult();
$seaRegions = array();
foreach ($seaRegionsInd as $value) {
foreach (explode(',', $value['studyregion']) as $item) {
if (is_numeric($item)) {
$seaRegions[] = trim($this->getRegionById($item));
}
}
}
$seaRegions = array_unique($seaRegions);
$allSeas = $this->getDoctrine()
->getManager()
->getRepository('OceanExpertBundle:Regions')
->findBy(
[],
['name' => 'ASC']
);
$availableSeas = array();
foreach ($allSeas as $value) {
$availableSeas[$value->getIdRegion()] = $value->getName();
}
$indSeaAreas = array_intersect(
$availableSeas,
$seaRegions
);
return new JsonResponse($indSeaAreas);
}
/**
* give the name of a region using the id
*
* @param int $id id of the region
*
* @return string
*/
public function getRegionById(int $id): string
{
$region = $this->getDoctrine()
->getRepository('OceanExpertBundle:Regions')
->findOneBy(array('idRegion' => $id));
if ($region) {
return $region->getName();
} else {
return '';
}
}
/**
* @return JsonResponse
*/
function getInstitutesSeaRegionsAction(): JsonResponse
{
$repository = $this->getDoctrine()
->getRepository('OceanExpertBundle:Institutions');
$seaRegionsInst = $repository->createQueryBuilder('i')
->select('DISTINCT i.instRegion')
->getQuery()
->getResult(AbstractQuery::HYDRATE_ARRAY);
$seaRegions = array();
foreach ($seaRegionsInst as $value) {
foreach (explode(',', $value['instRegion']) as $item) {
if (trim($item) != '') {
array_push($seaRegions, trim($item));
}
}
}
$seaRegions = array_unique($seaRegions);
$repository = $this->getDoctrine()->getRepository('OceanExpertBundle:Regions');
$instSeaAreas = $repository->createQueryBuilder('r')
->select('r.idRegion, r.name')
->where('r.idRegion IN(:seaRegions) ')
->setParameter('seaRegions', array_values($seaRegions))
->getQuery()->getResult();
return new JsonResponse($instSeaAreas);
}
/**
* @return JsonResponse
*/
function getExpertsSubjectsAction(): JsonResponse
{
$subjects = $this->getDoctrine()
->getRepository('OceanExpertBundle:Subjects')
->createQueryBuilder('s')
->select('s.subname, s.idSub')
->orderBy('s.subname', 'ASC')
->getQuery()
->getResult(AbstractQuery::HYDRATE_ARRAY);
return new JsonResponse($subjects);
}
/**
* get all possible first letters of the experts names
*
* @return JsonResponse
*/
function getExpertsLastNameAction(): JsonResponse
{
$em = $this->getDoctrine()->getManager();
$connection = $em->getConnection();
$statement = $connection->prepare("SELECT DISTINCT
UPPER(LEFT(sname, 1)) AS letter
FROM indiv
ORDER BY letter"
);
$statement->execute();
$lnameList = $statement->fetchAll();
$resultLname = array_column($lnameList, 'letter');
return new JsonResponse($resultLname);
}
/**
* @return JsonResponse
*/
function getExpertCountryListAction(): JsonResponse
{
$em = $this->getDoctrine()->getManager();
$connection = $em->getConnection();
$statement = $connection->prepare("
SELECT DISTINCT
UPPER(LEFT(country, 1)) AS letter
FROM countries
WHERE id_country IN (SELECT DISTINCT(country_code) FROM indiv)
ORDER BY letter"
);
$statement->execute();
$countries = $statement->fetchAll();
$countryList = array_column($countries, 'letter');
return new JsonResponse($countryList);
}
/**
* @return JsonResponse
*/
function getInstitutionTypesAction(): JsonResponse
{
$instType = $this->getDoctrine()
->getRepository('OceanExpertBundle:Insttypes')
->createQueryBuilder('i')
->select('i.insttypeName, i.idInsttype')
->orderBy('i.insttypeName', 'ASC')
->getQuery()
->getResult(AbstractQuery::HYDRATE_ARRAY);
return new JsonResponse($instType);
}
/**
* @return JsonResponse
*/
function getInstitutionNameListAction(): JsonResponse
{
$em = $this->getDoctrine()->getManager();
$connection = $em->getConnection();
$statement = $connection->prepare(
"SELECT DISTINCT
UPPER(LEFT(inst_name, 1)) AS letter
FROM institutions
ORDER BY letter"
);
$statement->execute();
$institutes = $statement->fetchAll();
$instNameList = array_column($institutes, 'letter');
return new JsonResponse($instNameList);
}
/**
* @return JsonResponse
*/
function getInstitutionCountryListAction(): JsonResponse
{
$em = $this->getDoctrine()->getManager();
$connection = $em->getConnection();
$statement = $connection->prepare(
"SELECT DISTINCT
UPPER(LEFT(country, 1)) AS letter
FROM countries
WHERE id_country IN (SELECT DISTINCT(country_code)
FROM institutions)
ORDER BY letter"
);
$statement->execute();
$instCountries = $statement->fetchAll();
$instCountryList = array_column($instCountries, 'letter');
return new JsonResponse($instCountryList);
}
/**
* make a list of the number of experts per country
*
* @return array
*/
private function getExpertsPerCountry()
{
//get info about the countries
$countryInfo = array();
$countries = $this->getDoctrine()
->getManager()
->getRepository('OceanExpertBundle:Countries')
->findAll();
$returnValue = array();
$em = $this->getDoctrine()->getManager();
//step 1 : those with their own address
$qb = $em->createQueryBuilder();
$expr = $qb->expr();
$qb->select(
"count('i.idInd') AS nr",
'c.country'
)->from(
'OceanExpertBundle:Indiv',
'i'
)->leftJoin(
'OceanExpertBundle:Countries',
'c',
'WITH',
'i.countryCode = c.idCountry'
)->where(
'i.status = 1'
)->andWhere(
$expr->neq('i.deceased', 1)
)->andWhere(
$expr->neq('i.retired', 1)
)->andWhere(
$expr->neq('i.useInstAddr', 1)
)->andWhere(
'c.idCountry IS NOT NULL'
)->groupBy(
'i.countryCode'
);
$experts = $qb->getQuery()->getResult(AbstractQuery::HYDRATE_ARRAY);
foreach ($experts as $expert) {
$expertsPerCountry[$expert['country']] = intval($expert['nr']);
}
//step 2 : those that use the address of their institute
$qb = $em->createQueryBuilder();
$expr = $qb->expr();
$qb->select(
"count('i.idInd') AS nr",
'c.country'
)->from(
'OceanExpertBundle:Indiv',
'i'
)->leftJoin(
'OceanExpertBundle:IndivInstitution',
'ii',
'WITH',
'i.idInd = ii.idInd'
)->leftJoin(
'OceanExpertBundle:Institutions',
'inst',
'WITH',
'ii.idInst = inst.idInst'
)->leftJoin(
'OceanExpertBundle:Countries',
'c',
'WITH',
'inst.countryCode = c.idCountry'
)->where(
'i.status = 1'
)->andWhere(
$expr->neq('i.deceased', 1)
)->andWhere(
$expr->neq('i.retired', 1)
)->andWhere(
'i.useInstAddr = 1'
)->andWhere(
'c.idCountry IS NOT NULL'
)->groupBy(
'inst.countryCode'
);
$experts = $qb->getQuery()->getResult(AbstractQuery::HYDRATE_ARRAY);
foreach ($experts as $expert) {
$expertsPerInstCountry[$expert['country']] = intval($expert['nr']);
}
foreach ($countries as $country) {
$countryId = $country->getIdCountry();
$countryName = $country->getCountry();
$countryCode = $country->getCountryCode();
$nrOfExperts = 0;
if (isset($expertsPerCountry[$countryName])) {
$nrOfExperts += $expertsPerCountry[$countryName];
}
if (isset($expertsPerInstCountry[$countryName])) {
$nrOfExperts += $expertsPerInstCountry[$countryName];
}
$returnValue[$countryId]['nrOfExperts'] = $nrOfExperts;
$returnValue[$countryId]['countryName'] = $countryName;
$returnValue[$countryId]['countryId'] = $countryId;
$returnValue[$countryId]['countryCode'] = $countryCode;
}
ksort($returnValue);
return $returnValue;
}
}

src/OceanExpertBundle/Controller/DefaultController.php line 121