/**
  * Get QueryBuilder for getting active treatments that can be used for dropdown field types
  *
  * @param Specialization $specialization optional
  */
 public function getQueryBuilderForGettingAvailableSubSpecializations(Specialization $specialization = null)
 {
     $qb = $this->getEntityManager()->createQueryBuilder()->add('select', 't')->add('from', 'TreatmentBundle:SubSpecialization t')->add('where', 't.status = :active');
     if ($specialization && $specialization->getId()) {
         $qb->andWhere('t.specialization = :specializationId')->setParameter('specializationId', $specialization->getId());
     }
     $qb->setParameter('active', SubSpecialization::STATUS_ACTIVE);
     return $qb;
 }
 /**
  * Find active search terms by specialization
  *
  * @param Specialization $specialization
  */
 public function findBySpecialization(Specialization $specialization)
 {
     $qb = $this->getQueryBuilderByDocumentIdAndType($specialization->getId(), TermDocument::TYPE_SPECIALIZATION);
     return $qb->getQuery()->getResult();
 }
 /**
  * Get QueryBuilder for getting active Treatments by Specialization
  *
  * @param Specialization $medicalCenter
  * @return QueryBuilder
  */
 public function getQueryBuilderForActiveTreatmentsBySpecialization(Specialization $specialization)
 {
     $qb = $this->getEntityManager()->createQueryBuilder()->select('a', 'b')->from('TreatmentBundle:Treatment', 'a')->innerJoin('a.specialization', 'b')->where('b.id = :specialization')->andWhere('a.status = :activeStatus')->orderBy('a.name')->setParameter('specialization', $specialization->getId())->setParameter('activeStatus', Treatment::STATUS_ACTIVE);
     return $qb;
 }
 public function getTreatmentsBySpecializationGroupedBySubSpecialization(Specialization $specialization)
 {
     return $this->getTreatmentsBySpecializationIdGroupedBySubSpecialization($specialization->getId());
 }
 /**
  * Migrate and convert $fromSpecialization as a new Sub Specialization of $toSpecialization
  * 
  * Operation is really risky!
  * 
  * @param Specialization $fromSpecialization
  * @param Specialization $toSpecialization
  * @author allejochrisvelarde
  */
 public function migrateSpecializationToAnotherSpecialization(Specialization $fromSpecialization, Specialization $toSpecialization)
 {
     /***
      * Steps for migration
      * 1. Move all institution_specializations of $fromSpecialization to $toSpecialization. Considerations:
      *      a. Check if institution_medical_center_id - $toSpecialization.id combination already exists
      *      b. If institution_medical_center_id - $toSpecialization.id combination exists, update institution_treatments of this IMC with institution specialization belonging to $fromSpecialization
      * 2. Move all treatments of fromSpecialization to toSpecialization. Considerations:
      *      a. Check for existing treatments.name - specialization.id combination in treatments table
      *      b. Link these treatments to the converted subspecialization [the old fromSpecialization]
      */
     $connection = $this->doctrine->getConnection();
     $em = $this->doctrine->getManager();
     //----- step 1
     $sql = "UPDATE IGNORE `institution_specializations` inst_sp SET inst_sp.specialization_id = :toSpecializationId WHERE inst_sp.specialization_id = :fromSpecializationId";
     $statement = $connection->prepare($sql);
     $statement->bindValue('fromSpecializationId', $fromSpecialization->getId());
     $statement->bindValue('toSpecializationId', $toSpecialization->getId());
     $statement->execute();
     // what would be left are those medical centers that also have $toSpecialization,
     // so we will just update the institution treatments and point it to the institution specialization with specialization = $toSpecialization
     // get remaining imcs with $fromSpecialization
     $sql = "SELECT imc.*, inst_sp.id as fromInstitutionSpecializationId\n                FROM `institution_medical_centers` imc INNER JOIN `institution_specializations` inst_sp ON inst_sp.`institution_medical_center_id` = imc.`id`\n                WHERE inst_sp.`specialization_id` = :fromSpecializationId";
     $statement = $connection->prepare($sql);
     $statement->bindValue('fromSpecializationId', $fromSpecialization->getId());
     $statement->execute();
     $imcs = $statement->fetchAll(\PDO::FETCH_ASSOC);
     foreach ($imcs as $imc) {
         // get the institiution_specialization with $toSpecialization of this imc
         $sql = "SELECT `id` \n                FROM `institution_specializations` inst_sp \n                WHERE inst_sp.`specialization_id` = :toSpecializationId \n                AND inst_sp.`institution_medical_center_id` = :imcId \n                LIMIT 1";
         $statement = $connection->prepare($sql);
         $statement->bindValue('toSpecializationId', $toSpecialization->getId());
         $statement->bindValue('imcId', $imc['id']);
         $statement->execute();
         $data = $statement->fetchAll(\PDO::FETCH_ASSOC);
         if (!empty($data)) {
             $toInstitutionSpecializationId = $data[0]['id'];
             // update instituion_treatments of this imc linked to $fromSpecialization
             $sql = "UPDATE `institution_treatments` inst_t \n                        SET inst_t.institution_specialization_id = :toInstitutionSpecializationId\n                        WHERE inst_t.institution_specialization_id = :fromInstitutionSpecializationId";
             $statement = $connection->prepare($sql);
             $statement->bindValue('toInstitutionSpecializationId', $toInstitutionSpecializationId);
             $statement->bindValue('fromInstitutionSpecializationId', $imc['fromInstitutionSpecializationId']);
             $statement->execute();
         }
     }
     // delete institution specializations linked to $fromSpecialization that were ignored in the above operation
     $qb = $em->createQueryBuilder();
     $qb->delete('InstitutionBundle:InstitutionSpecialization', 'inst_sp')->where('inst_sp.specialization = :fromSpecialization')->setParameter('fromSpecialization', $fromSpecialization);
     $qb->getQuery()->execute();
     //----- end of step 1
     //-------------------
     //----- step 2
     // we check first for already existing $toSpecialization-treatment name combination
     $sql = "SELECT  fromSp.id \n            FROM `treatments` fromSp\n            INNER JOIN\n            (SELECT * FROM `treatments` WHERE `specialization_id` = :toSpecializationId ) toSp\n            WHERE toSp.`name` = fromSp.`name`\n            AND fromSp.specialization_id = :fromSpecializationId";
     $statement = $connection->prepare($sql);
     $statement->bindValue('fromSpecializationId', $fromSpecialization->getId());
     $statement->bindValue('toSpecializationId', $toSpecialization->getId());
     $statement->execute();
     $existingTreatments = array();
     while ($row = $statement->fetch()) {
         $existingTreatments[] = $row['id'];
     }
     // check if subspecialization with $fromSpecialization->getName() already exists for $toSpecialization
     $qb = $em->createQueryBuilder();
     $qb->select('subSp')->from('TreatmentBundle:SubSpecialization', 'subSp')->where('subSp.specialization = :toSpecialization')->setParameter('toSpecialization', $toSpecialization)->andWhere('subSp.name = :subSpecializationName')->setParameter('subSpecializationName', $fromSpecialization->getName());
     $existingSubSpecializationWithSameName = $qb->getQuery()->getOneOrNullResult();
     if (!$existingSubSpecializationWithSameName) {
         // create sub specialization based on $fromSpecialization
         $subSpecialization = new SubSpecialization();
         $subSpecialization->setName($fromSpecialization->getName());
         $subSpecialization->setDescription($fromSpecialization->getDescription());
         $subSpecialization->setSpecialization($toSpecialization);
         // set to $toSpecialization
         $subSpecialization->setStatus($fromSpecialization->getStatus());
         $em->persist($subSpecialization);
     } else {
         // use the existing sub specialization
         $subSpecialization = $existingSubSpecializationWithSameName;
     }
     foreach ($fromSpecialization->getTreatments() as $treatment) {
         //if ($treatment instanceof Treatment){}
         // detach current su
         foreach ($treatment->getSubSpecializations() as $treatmentSub) {
             $treatment->removeSubSpecialization($treatmentSub);
         }
         // add this treatment to the converted sub specialization
         $treatment->addSubSpecialization($subSpecialization);
         // set specialization to $toSpecialization
         $treatment->setSpecialization($toSpecialization);
         // if treatment name already exists in $toSpecialization, rename it
         if (\in_array($treatment->getId(), $existingTreatments)) {
             $newName = \trim($treatment->getName() . ' - ' . $fromSpecialization->getName());
             $treatment->setName($newName);
         }
         $em->persist($treatment);
     }
     // delete all subspecializations of $fromSpecialization
     foreach ($fromSpecialization->getSubSpecializations() as $oldSub) {
         $em->remove($oldSub);
     }
     // delete fromSpecialization
     $em->remove($fromSpecialization);
     $em->flush();
     //----- end of step 2
 }