Symfony Profiler

<?php
namespace App\BackendBundle\Helper;
use DateTime;
use Doctrine\DBAL\Connection;
use Doctrine\ORM\EntityManagerInterface;
use Doctrine\ORM\Query;
use App\BackendBundle\Helper\MediaHelper;
use App\Entity\Address;
use App\Entity\CompanyLocation;
use App\Entity\CompanyProfile;
use App\Entity\Contact;
use App\Entity\Job;
use App\Entity\JobMarket;
use App\Entity\JobMarketApplyType;
use App\Entity\JobMarketPositionType;
use App\Entity\JobMarketStartType;
use App\Entity\JobMarketState;
use App\Entity\JobMarketViewType;
use App\Entity\User;
class JobMarketHelper {
private EntityManagerInterface $em;
private Connection $connection;
private MediaHelper $mediaHelper;
public function __construct(EntityManagerInterface $em, MediaHelper $mediaHelper) {
$this->em = $em;
$this->connection = $this->em->getConnection();
$this->mediaHelper = $mediaHelper;
}
public function createJobOffer($data) {
$jobOffer = new JobMarket();
$jobOffer->setName($data['name']);
$jobOffer->setImage($data['image']);
$jobOffer->setCustomAdvertismentImage($data['customAdvertismentImage']);
$jobOffer->setJobMarketPositionType($this->getJobMarketPositionTypeById($data['jobMarketPositionTypeId']));
$jobOffer->setJobMarketApplyType($this->getJobMarketApplyTypeById($data['jobMarketApplyTypeId']));
$jobOffer->setJobMarketStartType($this->getJobMarketStartTypeById($data['jobMarketStartTypeId']));
$jobOffer->setCompanyProfile($data['companyProfile']);
$jobOffer->setUser($data['user']);
$jobOffer->setContact($data['contact']);
foreach ($data['addresses'] as $address) {
$jobOffer->addAddress($address);
}
foreach ($data['jobs'] as $job) {
$jobOffer->addJob($job);
}
$jobOffer->setNumberOfPositions($data['numberOfPositions']);
$jobOffer->setStartDate($data['startDate']);
$jobOffer->setApplyUntil($data['applyUntil']);
$jobOffer->setDescription($data['description']);
$jobOffer->setRequirements($data['requirements']);
$jobOffer->setSalary($data['salary']);
$jobOffer->setDetails($data['details']);
$jobOffer->setOpenDate($data['openDate']);
$jobOffer->setClosedDate($data['closedDate']);
$jobOffer->setUrl($data['url']);
$jobOffer->setAdditionalInfo($data['additionalInfo']);
$jobOffer->setJobMarketState($this->getJobMarketStateById($data['jobMarketStateId']));
$this->em->persist($jobOffer);
$this->em->flush();
return $jobOffer;
}
public function deleteFull(JobMarket $jobMarket) {
$customAdvertismentMedia = $jobMarket->getCustomAdvertismentImage();
$this->deleteJobMarketAddressMappings($jobMarket);
$this->em->remove($jobMarket);
$this->em->flush();
if (!empty($customAdvertismentMedia)) {
$this->mediaHelper->deleteMedia($customAdvertismentMedia, null, true);
}
}
private function deleteJobMarketAddressMappings(JobMarket $jobMarket) {
$jobMarketID = $jobMarket->getId();
$sql = "DELETE FROM job_market_address_mapping WHERE job_market_id=:jmid";
$stmt = $this->connection->prepare($sql);
$stmt->bindValue('jmid', $jobMarketID);
$stmt->execute();
}
public function editJobOffer(JobMarket $jobOffer, $data) {
$jobOffer->setName($data['name']);
$jobOffer->setImage($data['image']);
$jobOffer->setCustomAdvertismentImage($data['customAdvertismentImage']);
$jobOffer->setJobMarketPositionType($this->getJobMarketPositionTypeById($data['jobMarketPositionTypeId']));
$jobOffer->setJobMarketApplyType($this->getJobMarketApplyTypeById($data['jobMarketApplyTypeId']));
$jobOffer->setJobMarketStartType($this->getJobMarketStartTypeById($data['jobMarketStartTypeId']));
$jobOffer->setCompanyProfile($data['companyProfile']);
$jobOffer->setUser($data['user']);
$jobOffer->setContact($data['contact']);
$jobOffer->getAddresses()->clear();
foreach ($data['addresses'] as $address) {
$jobOffer->addAddress($address);
}
$jobOffer->getJobs()->clear();
foreach ($data['jobs'] as $job) {
$jobOffer->addJob($job);
}
if ($data['image'] != null) {
$jobOffer->setImage($data['image']);
}
$jobOffer->setNumberOfPositions($data['numberOfPositions']);
$jobOffer->setStartDate($data['startDate']);
$jobOffer->setApplyUntil($data['applyUntil']);
$jobOffer->setDescription($data['description']);
$jobOffer->setRequirements($data['requirements']);
$jobOffer->setSalary($data['salary']);
$jobOffer->setDetails($data['details']);
$jobOffer->setOpenDate($data['openDate']);
$jobOffer->setClosedDate($data['closedDate']);
$jobOffer->setUrl($data['url']);
$jobOffer->setAdditionalInfo($data['additionalInfo']);
$this->em->persist($jobOffer);
$this->em->flush();
return $jobOffer;
}
public function getJobOfferById($id) {
return $this->em->getRepository(JobMarket::class)->findOneBy(array('id' => $id));
}
public function getJobOffersByCompanyProfile(CompanyProfile $companyProfile = null, $jobOfferStateArray = null, $filterDates = true) {
if (empty($companyProfile)) {
return null;
}
$companyProfileID = $companyProfile->getId();
$whereDates = '';
if ($filterDates) {
$whereDates = 'AND j.open_date <= :now AND j.closed_date >= :now';
}
$whereStates = $this->getJobOffersStateWhere($jobOfferStateArray);
$sqlText = "SELECT j FROM App\Entity\JobMarket j "
. "LEFT JOIN j.companyProfile as cp "
. "WHERE cp.id = :companyProfileID "
. "$whereDates $whereStates"
. "ORDER BY j.name ASC";
$query = $this->em->createQuery($sqlText);
$query->setParameter('companyProfileID', $companyProfileID);
if ($filterDates) {
$query->setParameter('now', new DateTime());
}
if (!empty($jobOfferStateArray)) {
$index = 0;
foreach ($jobOfferStateArray as $jobOfferState) {
$query->setParameter('state' . $index, $jobOfferState);
$index++;
}
}
$result = $query->getResult();
return $result;
}
private function getJobOffersStateWhere($jobOfferStateArray) {
if (empty($jobOfferStateArray)) {
return '';
}
$sqlText = ' AND (';
$index = 0;
foreach ($jobOfferStateArray as $jobOfferState) {
if ($index > 0) {
$sqlText .= ' OR ';
}
$sqlText .= 'j.jobMarketState = :state' . $index;
$index++;
}
$sqlText .= ')';
return $sqlText;
}
public function getJobOffersByUser(User $user, $jobOfferStateArray) {
$sqlText = 'SELECT j FROM App\Entity\JobMarket j
WHERE j.user = :user';
if ($jobOfferStateArray != null) {
$sqlText .= ' AND (';
$index = 0;
foreach ($jobOfferStateArray as $jobOfferState) {
if ($index > 0) {
$sqlText .= ' OR ';
}
$sqlText .= 'j.jobMarketState = :state' . $index;
$index++;
}
$sqlText .= ')';
}
$query = $this->em->createQuery($sqlText);
$query->setParameter('user', $user);
if ($jobOfferStateArray != null) {
$index = 0;
foreach ($jobOfferStateArray as $jobOfferState) {
$query->setParameter('state' . $index, $jobOfferState);
$index++;
}
}
$result = $query->getResult();
return $result;
}
public function getJobOffersByLimitOffset($limit, $offset) {
$dql = 'SELECT jm '
. 'FROM App\Entity\JobMarket jm '
. 'JOIN jm.jobMarketState state '
. 'WHERE state.id = 1 AND jm.closed_date > :now '
. 'ORDER BY jm.id ASC';
/* @var $query Query */
$query = $this->em->createQuery($dql);
$now = new DateTime();
$query->setParameter("now", $now);
$query->setMaxResults($limit);
$query->setFirstResult($offset);
return $query->getResult();
}
public function getActiveJobOffersByJob(Job $job) {
$sqlText = 'SELECT jm FROM App\Entity\JobMarket jm
JOIN jm.jobs jobs
JOIN jm.jobMarketState state
WHERE jobs = :job
AND state.id = 1
AND jm.closed_date >= CURRENT_TIMESTAMP()';
$query = $this->em->createQuery($sqlText);
$query->setParameter('job', $job);
$result = $query->getResult();
return $result;
}
public function getJobMarketStateById($id) {
return $this->em->getRepository(JobMarketState::class)->findOneBy(array('id' => $id));
}
public function getJobMarketPositionTypeById($id) {
return $this->em->getRepository(JobMarketPositionType::class)->findOneBy(array('id' => $id));
}
public function getJobMarketPositionTypeByString($strText) {
$strLower = strtolower($strText);
switch ($strLower) {
case "lehrstelle":
return $this->getJobMarketPositionTypeById(1);
case "ferialjob":
return $this->getJobMarketPositionTypeById(2);
case "praktikum":
return $this->getJobMarketPositionTypeById(3);
default:
return null;
}
}
public function getJobMarketPositionTypes() {
return $this->em->getRepository(JobMarketPositionType::class)->findBy(array(), array('position' => 'ASC'));
}
public function getJobMarketApplyTypeById($id) {
return $this->em->getRepository(JobMarketApplyType::class)->findOneBy(array('id' => $id));
}
public function getJobMarketApplyTypes() {
return $this->em->getRepository(JobMarketApplyType::class)->findBy(array('isVisible' => true), array('description' => 'ASC'));
}
public function getJobMarketApplyTypeByString($strText) {
$strLower = strtolower($strText);
switch ($strLower) {
case "online":
return $this->getJobMarketApplyTypeById(1);
case "email":
return $this->getJobMarketApplyTypeById(2);
case "postal":
return $this->getJobMarketApplyTypeById(4);
default:
return null;
}
}
public function getJobMarketViewTypeByID($id) {
return $this->em->getRepository(JobMarketViewType::class)->find($id);
}
public function getJobMarketViewTypeByString($strText) {
$strLower = strtolower($strText);
switch ($strLower) {
case "default":
return $this->getJobMarketViewTypeById(1);
case "pdf":
return $this->getJobMarketViewTypeById(2);
default:
return null;
}
}
public function getJobMarketStartTypeById($id) {
return $this->em->getRepository(JobMarketStartType::class)->findOneBy(array('id' => $id));
}
public function getJobMarketByAddress(Address $address = null) {
if (empty($address)) {
return null;
}
$addressID = $address->getId();
$sqlText = "SELECT jm "
. "FROM App\Entity\JobMarket jm "
. "JOIN jm.addresses address "
. "WHERE address.id = :addressid";
$query = $this->em->createQuery($sqlText);
$query->setParameter('addressid', $addressID);
return $query->getResult();
}
public function getJobMarketsByContact(Contact $contact) {
return $this->em->getRepository(JobMarket::class)->findOneBy(array('contact' => $contact));
}
public function getJobMarketsByCompanyProfile(CompanyProfile $companyProfile) {
return $this->em->getRepository(JobMarket::class)->findBy(array('companyProfile' => $companyProfile), array('name' => 'ASC'));
}
public function increaseViewCount(JobMarket $jobMarket) {
$id = $jobMarket->getId();
$newViewCount = $jobMarket->getViewCount() + 1;
$connection = $this->em->getConnection();
$sql = "UPDATE job_market SET view_count=:viewcount WHERE id=:id";
$stmt = $connection->prepare($sql);
$stmt->bindValue('id', $id);
$stmt->bindValue('viewcount', $newViewCount);
$stmt->execute();
}
public function isCompanyLocationInUse(CompanyLocation $companyLocation) {
$address = $companyLocation->getAddress();
if (empty($address)) {
return false;
}
$addressID = $address->getId();
$sql = "SELECT count(*) as numEntries FROM job_market_address_mapping WHERE address_id=$addressID";
$arrResult = $this->connection->query($sql)->fetchAll();
if ($arrResult[0]['numEntries'] > 0) {
return true;
}
return false;
}
public function isContactInUse(Contact $contact = null) {
if (empty($contact)) {
return false;
}
$contactID = $contact->getId();
$sql = "SELECT count(*) as numEntries FROM job_market WHERE contact_id=$contactID";
$arrResult = $this->connection->query($sql)->fetchAll();
if ($arrResult[0]['numEntries'] > 0) {
return true;
}
return false;
}
public function setState(JobMarket $jobMarket, JobMarketState $state) {
$stateID = $state->getId();
// $stateID 3 = delete
if ($stateID == 3) {
$this->deleteFull($jobMarket);
} else {
$jobMarket->setJobMarketState($state);
$this->em->persist($jobMarket);
$this->em->flush();
}
}
}

src/BackendBundle/Helper/JobMarketHelper.php line 29