/** * 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(); }