/**
* Added/Updated recentlyApproved institution type listing. Removed when updated from APPROVED to INACTIVE, SUSPENDED or INACTIVE
* @param Institution $institution
*/
function updateInstitutionListing(Institution $institution)
{
$criteria = array('institution' => $institution->getId(), 'institutionMedicalCenter' => null);
$recentlyApprovedListing = $this->em->getRepository('AdminBundle:RecentlyApprovedListing')->findOneBy($criteria);
if ($recentlyApprovedListing) {
if ($institution->getStatus() == InstitutionStatus::getBitValueForActiveAndApprovedStatus()) {
$recentlyApprovedListing->setDateUpdated(new \DateTime());
$this->em->persist($recentlyApprovedListing);
} else {
$this->em->remove($recentlyApprovedListing);
}
$this->em->flush();
} else {
if ($institution->getStatus() == InstitutionStatus::getBitValueForActiveAndApprovedStatus()) {
$recentlyApprovedListingService = new RecentlyApprovedListingService();
$recentlyApprovedListingService->setEntityManager($this->em);
$recentlyApprovedListing = new RecentlyApprovedListing();
$recentlyApprovedListing->setInstitution($institution);
$recentlyApprovedListing->setInstitutionMedicalCenter(null);
$recentlyApprovedListing->setDateUpdated(new \DateTime());
$recentlyApprovedListing->setStatus(1);
$this->em->persist($recentlyApprovedListing);
$this->em->flush($recentlyApprovedListing);
}
}
}
function setInstitutionFilterOption()
{
$qb = $this->doctrine->getEntityManager()->createQueryBuilder();
$qb->select('a.id, a.name')->from('InstitutionBundle:Institution', 'a')->where('a.status = :status')->orderBy('a.name', 'ASC')->setParameter('status', InstitutionStatus::getBitValueForActiveAndApprovedStatus());
$institutions = $qb->getQuery()->getResult(Query::HYDRATE_ARRAY);
$options = array(ListFilter::FILTER_KEY_ALL => ListFilter::FILTER_LABEL_ALL);
foreach ($institutions as $each) {
$options[$each['id']] = $each['name'];
}
$this->filterOptions['institution'] = array('label' => 'Institution', 'selected' => $this->queryParams['institution'], 'options' => $options);
}
public function getAllInstitutionByParams($params)
{
$query = $this->createQueryBuilder('a')->where('a.status = :status')->andWhere('a.name LIKE :searchTerm')->setParameter('status', InstitutionStatus::getBitValueForActiveAndApprovedStatus())->setParameter('searchTerm', '%' . $params['searchTerm'] . '%');
if ($params['countryId'] != 'all') {
$query->andWhere('a.country = :country')->setParameter('country', $params['countryId']);
}
if ($params['cityId'] != 'all') {
$query->andWhere('a.city = :city')->setParameter('city', $params['cityId']);
}
$query = $query->orderBy('a.name');
return $query->getQuery()->getResult();
}
/**
* Get the top specializations and treatments for specified city
*
* @param unknown $country
* @param number $max
* @return multitype:unknown
*/
public function getCityTopTreatments($city, $max = 5)
{
$connection = $this->getEntityManager()->getConnection();
if (is_object($city)) {
$city = $city->getId();
}
$stmt = $connection->prepare('
SELECT b.id, b.name AS name, b.slug AS specialization_slug, COUNT(DISTINCT d.city_id) AS count
FROM institution_specializations a
LEFT JOIN specializations b ON a.specialization_id = b.id
INNER JOIN institution_medical_centers c ON a.institution_medical_center_id = c.id
LEFT JOIN institutions AS d ON c.institution_id = d.id
WHERE d.city_id = :city
AND d.status = :institutionStatus
AND c.status = :imcStatus
GROUP BY b.id
ORDER BY count DESC
LIMIT :max
');
$stmt->bindValue('institutionStatus', InstitutionStatus::getBitValueForActiveAndApprovedStatus(), \PDO::PARAM_INT);
$stmt->bindValue('imcStatus', InstitutionMedicalCenterStatus::APPROVED, \PDO::PARAM_INT);
$stmt->bindValue('city', $city, \PDO::PARAM_INT);
$stmt->bindValue('max', $max, \PDO::PARAM_INT);
$stmt->execute();
$topSpecializations = $stmt->fetchAll();
$stmt = $connection->prepare('
SELECT c.id, c.name AS name, c.slug AS treatment_slug, COUNT(DISTINCT e.city_id) AS count, z.slug AS specialization_slug
FROM institution_specializations a
INNER JOIN institution_treatments b ON a.id = b.institution_specialization_id
LEFT JOIN treatments c ON b.treatment_id = c.id
INNER JOIN institution_medical_centers d ON a.institution_medical_center_id = d.id
LEFT JOIN institutions AS e ON d.institution_id = e.id
LEFT JOIN specializations AS z ON a.specialization_id = z.id
WHERE e.city_id = :city
AND c.id IS NOT NULL
AND e.status = :institutionStatus
AND d.status = :imcStatus
GROUP BY c.id
ORDER BY count DESC
LIMIT :max
');
$stmt->bindValue('institutionStatus', InstitutionStatus::getBitValueForActiveAndApprovedStatus(), \PDO::PARAM_INT);
$stmt->bindValue('imcStatus', InstitutionMedicalCenterStatus::APPROVED, \PDO::PARAM_INT);
$stmt->bindValue('city', $city, \PDO::PARAM_INT);
$stmt->bindValue('max', $max, \PDO::PARAM_INT);
$stmt->execute();
$topTreatments = $stmt->fetchAll();
return array($topSpecializations, $topTreatments);
}
private function searchCitiesByNameWithTreatment(SearchParameterBag $searchParams)
{
$connection = $this->container->get('doctrine')->getEntityManager()->getConnection();
$treatmentId = null;
$name = '"%' . $searchParams->get('searchedTerm') . '%"';
$institutionStatus = InstitutionStatus::getBitValueForActiveAndApprovedStatus();
$imcStatus = InstitutionMedicalCenterStatus::APPROVED;
switch ($searchParams->get('treatmentType')) {
case 'specialization':
$treatmentId = $searchParams->get('specializationId');
$sql = "\n SELECT a.id AS city_id, a.name AS city_name, b.id AS country_id, b.name AS country_name\n FROM cities AS a\n LEFT JOIN countries AS b ON a.country_id = b.id\n INNER JOIN institutions AS c ON a.id = c.city_id AND b.id = c.country_id\n INNER JOIN institution_medical_centers AS d ON c.id = d.institution_id\n INNER JOIN institution_specializations AS e ON d.id = e.institution_medical_center_id\n WHERE a.name LIKE {$name}\n AND c.status = {$institutionStatus}\n AND d.status = {$imcStatus}\n AND e.specialization_id = {$treatmentId}\n ";
break;
case 'subSpecialization':
$treatmentId = $searchParams->get('subSpecializationId');
$sql = "\n SELECT a.id AS city_id, a.name AS city_name, b.id AS country_id, b.name AS country_name\n FROM cities AS a\n LEFT JOIN countries AS b ON a.country_id = b.id\n INNER JOIN institutions AS c ON a.id = c.city_id AND b.id = c.country_id\n INNER JOIN institution_medical_centers AS d ON c.id = d.institution_id\n INNER JOIN institution_specializations AS e ON d.id = e.institution_medical_center_id\n INNER JOIN institution_treatments AS f ON f.specialization_id = e.id\n LEFT JOIN treatments AS g ON f.treatment_id = g.id\n INNER JOIN treatment_sub_specializations AS h ON g.id = h.treatment_id\n WHERE a.name LIKE {$name}\n AND c.status = {$institutionStatus}\n AND d.status = {$imcStatus}\n AND h.sub_specialization_id = {$treatmentId}\n ";
break;
case 'treatment':
$treatmentId = $searchParams->get('treatmentId');
$sql = "\n SELECT a.id AS city_id, a.name AS city_name, b.id AS country_id, b.name AS country_name\n FROM cities AS a\n LEFT JOIN countries AS b ON a.country_id = b.id\n INNER JOIN institutions AS c ON a.id = c.city_id AND b.id = c.country_id\n INNER JOIN institution_medical_centers AS d ON c.id = d.institution_id\n INNER JOIN institution_specializations AS e ON d.id = e.institution_medical_center_id\n INNER JOIN institution_treatments AS f ON f.specialization_id = e.id\n WHERE a.name LIKE {$name}\n AND c.status = {$institutionStatus}\n AND d.status = {$imcStatus}\n AND f.treatment_id = {$treatmentId}\n ";
break;
}
$stmt = $connection->executeQuery($sql);
//TODO: use prepared statements. there seems to be a bug though: the code
//below will return an empty result
// $stmt = $connection->prepare($sql);
// $stmt->bindValue('name', '"%'.$searchParams->get('term').'%"');
// $stmt->bindValue('treatmentId', $treatmentId, \PDO::PARAM_INT);
//$stmt->bindValue('institutionStatus', InstitutionStatus::INACTIVE, \PDO::PARAM_INT);
return $stmt->fetchAll();
}
