/**
* Return Contact entities based on a selection SQL using a native SQL query
*
* @param Selection $selection
*
* @return Entity\Contact[]
*/
public function findContactsBySelectionContact(Selection $selection)
{
$qb = $this->_em->createQueryBuilder();
$qb->select('c');
$qb->from("Contact\\Entity\\Contact", 'c');
$qb->join('c.selectionContact', 'sc');
$qb->distinct('c.id');
$qb->andWhere($qb->expr()->isNull('c.dateEnd'));
$qb->andWhere('sc.selection = ?1');
$qb->setParameter(1, $selection->getId());
$qb->orderBy('c.lastName');
return $qb->getQuery()->getResult();
}
/**
* @param Selection $selection
*
* @return Contact[]
*/
public function findContactsInSelectionAsArray(Selection $selection)
{
/*
* A selection can have 2 methods, either SQL or a contacts. We need to query both
*/
if (!is_null($selection->getSql())) {
//We have a dynamic query, check if the contact is in the selection
$contacts = $this->getEntityManager()->getRepository(Contact::class)->findContactsBySelectionSQL($selection->getSql(), true);
} else {
$contacts = $this->getEntityManager()->getRepository(Contact::class)->findContactsBySelectionContact($selection, true);
}
return $contacts;
}
/**
* @param $selectionId
* @param $selectionName
*
* @return null
*/
private function setSelectionFromFromData($selectionId, $selectionName)
{
if (empty($selectionId) && empty($selectionName)) {
return null;
}
/** Parse the $selectionId if not empty */
if (!empty($selectionId)) {
$selectionService = $this->getSelectionService()->setSelectionId((int) $selectionId);
if (!$selectionService->isEmpty()) {
$this->setSelection($selectionService->getSelection());
}
}
if (!empty($selectionName)) {
$selection = new Selection();
$selection->setSelection($selectionName);
$selection->setContact($this->getServiceLocator()->get('Application\\Authentication\\Service')->getIdentity());
$selection = $this->getContactService()->newEntity($selection);
$this->setSelection($selection);
}
}
/**
* @param Selection $selection
*
* @return Contact[]
*/
public function findContactsInSelection(Selection $selection)
{
/**
* A selection can have 2 methods, either SQL or a contacts. We need to query both
*/
if (!is_null($selection->getSql())) {
//We have a dynamic query, check if the contact is in the selection
return $this->getEntityManager()->getRepository($this->getFullEntityName('Contact'))->findContactsBySelectionSQL($selection->getSql());
} else {
return $this->getEntityManager()->getRepository($this->getFullEntityName('Contact'))->findContactsBySelectionContact($selection);
}
}
/**
* @param array $data
*
* array (size=5)
* 'type' => string '2' (length=1)
* 'added' => string '20388' (length=5)
* 'removed' => string '' (length=0)
* 'sql' => string '' (length=0)
*
*
*/
public function updateSelectionContacts(Selection $selection, array $data)
{
/**
* First update the selection based on the type
*/
if ((int) $data['type'] === Selection::TYPE_FIXED) {
//remove the query
if (!is_null($sql = $selection->getSql())) {
$this->removeEntity($sql);
}
//Update the contacts
if (!empty($data['added'])) {
foreach (explode(',', $data['added']) as $contactId) {
$contact = $this->getContactService()->findEntityById('contact', $contactId);
if (!$contact->isEmpty() && !$this->getContactService()->contactInSelection($contact, $selection)) {
$selectionContact = new SelectionContact();
$selectionContact->setContact($contact);
$selectionContact->setSelection($selection);
$this->newEntity($selectionContact);
}
}
}
//Update the contacts
if (!empty($data['removed'])) {
foreach (explode(',', $data['removed']) as $contactId) {
foreach ($selection->getSelectionContact() as $selectionContact) {
if ($selectionContact->getContact()->getId() === (int) $contactId) {
$this->removeEntity($selectionContact);
}
}
}
}
} else {
$selectionSql = $selection->getSql();
if (is_null($selectionSql)) {
$selectionSql = new SelectionSql();
$selectionSql->setSelection($selection);
}
$selectionSql->setQuery($data['sql']);
$this->updateEntity($selectionSql);
}
}
/**
* @param $steeringboard_service
* @return string
*/
public function parseSteeringboardList()
{
$selection = new Selection();
$selection->setId(133);
$contacts = $this->getContactService()->findContactsInSelection($selection);
// generate steering board
return $this->getZfcTwigRenderer()->render('member/partial/steeringboard', ['contacts' => $contacts]);
}
/**
* Return Contact entities based on a selection SQL using a native SQL query.
*
* @param Selection $selection
* @param bool $toArray
*
* @return Entity\Contact[]
*/
public function findContactsBySelectionContact(Selection $selection, $toArray = false)
{
$qb = $this->_em->createQueryBuilder();
$qb->select('c', 'co', 'o', 'cy');
$qb->from("Contact\\Entity\\Contact", 'c');
$qb->join('c.selectionContact', 'sc');
$qb->leftJoin('c.contactOrganisation', 'co');
$qb->join('co.organisation', 'o');
$qb->join('o.country', 'cy');
$qb->distinct('c.id');
$qb->andWhere($qb->expr()->isNull('c.dateEnd'));
$qb->andWhere('sc.selection = ?1');
$qb->setParameter(1, $selection->getId());
$qb->orderBy('c.lastName');
if ($toArray) {
return $this->reIndexContactArray($qb->getQuery()->getResult(AbstractQuery::HYDRATE_SCALAR));
} else {
return $qb->getQuery()->getResult();
}
}
public function isSql()
{
return !is_null($this->selection->getSql());
}