/**
* 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
}