Symfony Profiler

<?php
namespace OceanExpertBundle\Controller;
use Doctrine\ORM\AbstractQuery;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
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;
use Symfony\Component\HttpFoundation\ResponseHeaderBag;
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();
//get all the possible calendar groups
$sitesGroups = $this->getDoctrine()
->getRepository('OceanExpertBundle:Groups')
->createQueryBuilder('g')
->select('g.idGroup, g.groupname, g.description')
->where('g.hasSite = 1')
->orderBy('g.groupname', 'ASC')
->getQuery()
->getResult(AbstractQuery::HYDRATE_ARRAY);
$data['groups'] = $sitesGroups;
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);
}
/**
* create the custom report using the given parameters
*
* @param Request $request
*
* @return Response
*/
public function customReportAction(Request $request): Response
{
// request contains:
//startDate, endDate, groupsselect
$params = $request->request->all();
//dump($params['startDate']);
//dump($params['endDate']);
//dump($params['groupsselect']);
//get all the events that match the given parameters
$events = $this->getDoctrine()->getRepository('OceanExpertBundle:Events')
->createQueryBuilder('e')
->select('e.idEvent,
e.startOn,
e.endOn,
et.eventtypeName AS eventType,
e.title'
)
->leftJoin(
'OceanExpertBundle:EventGroups',
'eg',
'WITH',
'e.idEvent = eg.idEvent'
)
->leftJoin(
'OceanExpertBundle:Eventtypes',
'et',
'WITH',
'e.idEventtype = et.idEventtype'
)
->distinct();
//->where('1');
if (
isset($params['startDate'])
&& $params['startDate'] != ''
) {
$events->andWhere('e.startOn >= :startDate')
->setParameter('startDate', $params['startDate']);
}
if (
isset($params['endDate'])
&& $params['endDate'] != ''
) {
$events->andWhere('e.startOn <= :endDate')
->setParameter('endDate', $params['endDate']);
}
if (
isset($params['idGroup'])
&& $params['idGroup'] != ''
) {
$events->andWhere('eg.idGroup = :idGroup')
->setParameter('idGroup', $params['idGroup']);
}
$events->orderBy('e.startOn', 'ASC');
//dump($events->getQuery());
$events = $events->getQuery()->getResult(AbstractQuery::HYDRATE_ARRAY);
//dump($events);
//die();
//get all the countries
$countries = $this->getDoctrine()
->getManager()
->getRepository('OceanExpertBundle:Countries')
->findAll();
//we don't need all the info, just id id_country and country
$countries = array_reduce(
$countries,
function ($result, $country) {
$result[$country->getIdCountry()] = array(
'country' => $country->getCountry(),
'countryCode' => $country->getCountryCode()
);
return $result;
},
array()
);
//dump($countries);
//make an excel file with the participants per event
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setTitle('OceanExpert Custom Report');
$sheet->setCellValue('A1', 'event title');
$sheet->setCellValue('B1', 'event type');
$sheet->setCellValue('C1', 'starts on');
$sheet->setCellValue('D1', 'ends on');
$sheet->setCellValue('E1', 'first name');
$sheet->setCellValue('F1', 'surname');
$sheet->setCellValue('G1', 'gender');
$sheet->setCellValue('H1', 'nationality');
$sheet->setCellValue('I1', 'work location/country');
$sheetRow = 2;
//get all the participants of the events
foreach ($events as $keyEvents => $event) {
//dump($event);
$idEvent = $event['idEvent'];
//dump($idEvent);
$participants = $this->getDoctrine()->getRepository('OceanExpertBundle:Indiv')
->createQueryBuilder('i')
->select('
i.idInd,
i.fname,
i.sname,
i.gender,
i.idNationality,
i.useInstAddr,
i.countryCode,
ii.idInst'
)
->leftJoin(
'OceanExpertBundle:EventParticipants',
'ep',
'WITH',
'i.idInd = ep.idInd'
)
->leftJoin(
'OceanExpertBundle:IndivInstitution',
'ii',
'WITH',
'i.idInd = ii.idInd'
)
->where('ep.idEvent = :idEvent')
->setParameter('idEvent', $idEvent)
->getQuery()
->getResult(AbstractQuery::HYDRATE_ARRAY);
//cleanup the data
//dump($participants);
//die();
foreach ($participants as $key => $participant) {
//this info is the same for all participants of this event
$sheet->setCellValue('A' . $sheetRow, $event['title']);
$sheet->setCellValue('B' . $sheetRow, $event['eventType']);
$sheet->setCellValue('C' . $sheetRow, $event['startOn']->format('Y-m-d'));
$sheet->setCellValue('D' . $sheetRow, $event['endOn']->format('Y-m-d'));
//add the country name to the participant
if (isset($participant['idNationality'])) {
//experts can have more than one nationality
$nationalities = explode(',', $participant['idNationality']);
$finalNationalities = array();
foreach ($nationalities as $nationality) {
if (isset($countries[$nationality])) {
$finalNationalities[] = $countries[$nationality]['country'];
} else {
$finalNationalities[] = 'wrong value';
}
}
$nationality = implode(',', $finalNationalities);
} else {
//should not happen, but just in case
$nationality = 'unknown';
}
//add the workaddress/country name to the participant
if (isset($participant['countryCode'])
&& $participant['useInstAddr'] != 1
&& isset($countries[$participant['countryCode']])
) {
$workAddressCountry = $countries[$participant['countryCode']]['country'];
} elseif(isset($participant['countryCode'])
&& $participant['useInstAddr'] == 1
&& $participant['idInst'] != null
) {
$inst = $this->getDoctrine()
->getRepository('OceanExpertBundle:Institutions')
->find($participant['idInst']);
if ($inst
&& $inst->getCountryCode() != null
){
$workAddressCountry = $countries[$inst->getCountryCode()]['country'];;
} else {
//should not happen, but just in case
$workAddressCountry = 'unknown institute country';
}
} else {
//should not happen, but just in case
$workAddressCountry = 'unknown';
}
//this is info specific for the participant
$sheet->setCellValue('E' . $sheetRow, $participant['fname']);
$sheet->setCellValue('F' . $sheetRow, $participant['sname']);
$sheet->setCellValue('G' . $sheetRow, $participant['gender']);
$sheet->setCellValue('H' . $sheetRow, $nationality);
$sheet->setCellValue('I' . $sheetRow, $workAddressCountry);
$sheetRow ++;
unset($participants[$key]);
}
unset($events[$keyEvents]);
//dump($participants);
}
// Create your Office 2007 Excel (XLSX Format)
$writer = new Xlsx($spreadsheet);
// Create a Temporary file in the system
$fileName = 'OceanExpertCustomReport.xlsx';
$temp_file = tempnam(sys_get_temp_dir(), $fileName);
// Create the excel file in the tmp directory of the system
$writer->save($temp_file);
// Return the excel file as an attachment
return $this->file(
$temp_file,
$fileName,
ResponseHeaderBag::DISPOSITION_INLINE
);
}
/**
* 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:Searegions')
->findBy(
[],
['name' => 'ASC']
);
$availableSeas = array();
foreach ($allSeas as $value) {
$availableSeas[$value->getIdSearegion()] = $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:Searegions')
->findOneBy(array('idSearegion' => $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:Searegions');
$instSeaAreas = $repository->createQueryBuilder('r')
->select('r.idSearegion, r.name')
->where('r.idSearegion 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);
}
/**
* get the list of institution types
*
* @todo : check if this is still needed, same functionality is available in the SearchController
*
* @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 107