/**
*
* @param Petition $petition
* @param string $ts_1
* @param string $ts_2
* @param PetitionSigning $existing_signing
* @return Doctrine_Query
*/
public function queryByTargetSelector(Petition $petition, $ts_1, $ts_2, $existing_signing = null)
{
$ts = $petition->getTargetSelectors();
$query = $this->createQuery('c')->where('c.mailing_list_id = ?', $petition->getMailingListId());
if ($existing_signing) {
$query->andWhere('c.id NOT IN (SELECT psc.contact_id FROM PetitionSigningContact psc WHERE psc.petition_signing_id = ?)', $existing_signing->getId());
}
if ($ts_1 && count($ts) > 0) {
$sel = $ts[0]['id'];
if (is_numeric($sel)) {
$is_mapping = array_key_exists('kind', $ts[0]) && $ts[0]['kind'] == MailingListMeta::KIND_MAPPING;
$is_choice = array_key_exists('kind', $ts[0]) && $ts[0]['kind'] == MailingListMeta::KIND_CHOICE;
$query->leftJoin('c.ContactMeta cm1');
if ($is_mapping) {
$mapped_ts1 = MappingPairTable::getInstance()->getMapByIdAndA($ts[0]['mapping_id'], $ts_1);
$query->andWhere('cm1.mailing_list_meta_id = ?', $ts[0]['meta_id'])->leftJoin('cm1.MailingListMetaChoice mlmc1')->andWhereIn('mlmc1.choice', $mapped_ts1);
} elseif ($is_choice && is_numeric($ts_1)) {
$query->andWhere('cm1.mailing_list_meta_id = ?', $sel)->andWhere('cm1.mailing_list_meta_choice_id = ?', $ts_1);
} else {
// should not happen
$query->andWhere('cm1.mailing_list_meta_id = ?', $sel)->andWhere('cm1.value = ?', $ts_1);
}
} else {
if ($sel === 'contact') {
if (is_numeric($ts_1)) {
$query->andWhere("c.id = ?", $ts_1);
}
} else {
if (is_string($ts_1) && $ts_1 != 'all') {
$query->andWhere("c.{$sel} = ?", $ts_1);
}
}
}
if ($ts_2 && count($ts) > 1) {
$sel = $ts[1]['id'];
if (is_numeric($sel)) {
if (is_numeric($ts_2)) {
$query->leftJoin('c.ContactMeta cm2')->andWhere('cm2.mailing_list_meta_id = ?', $sel)->andWhere('cm2.mailing_list_meta_choice_id = ?', $ts_2);
}
} else {
if (is_string($ts_2) && $ts_2 != 'all') {
$query->andWhere("c.{$sel} = ?", $ts_2);
}
}
} else {
if (is_numeric($ts_2)) {
$query->andWhere("c.id = ?", $ts_2);
}
}
}
return $query;
}
public function getChoices(Petition $petition, $first, $active_pledge_item_ids = false)
{
$ts = $petition->getTargetSelectors();
$choices = array();
$query = $this->createQuery('ml')->where('ml.id = ?', $petition->getMailingListId());
$fix_field = false;
$direct_contact = false;
if (count($ts) > 1) {
$second = $ts[1]['id'];
if (is_numeric($second)) {
$query->leftJoin('ml.MailingListMeta mlm2')->andWhere('mlm2.id = ?', $second)->leftJoin('mlm2.MailingListMetaChoice mlmc2')->leftJoin('mlmc2.ContactMeta cm2')->leftJoin('cm2.Contact c')->select('DISTINCT ml.id, mlm2.id, mlmc2.*');
} else {
$fix_field = true;
$col = $ts[1]['id'];
$query->leftJoin('ml.Contact c')->groupBy("c.{$col}")->select("DISTINCT ml.id, c.id, c.{$col}");
}
} else {
$direct_contact = true;
$query->leftJoin('ml.Contact c')->select('DISTINCT ml.id, c.firstname, c.lastname, c.country');
}
if (is_numeric($ts[0]['id'])) {
$query->leftJoin('c.ContactMeta cm1');
if (array_key_exists('kind', $ts[0]) && $ts[0]['kind'] == MailingListMeta::KIND_MAPPING) {
$query->andWhere('cm1.mailing_list_meta_choice_id IN (SELECT mlmc1.id FROM MailingListMetaChoice mlmc1 WHERE mlmc1.mailing_list_meta_id = ? AND mlmc1.choice = ?)', array($ts[0]['meta_id'], $first));
} else {
$query->andWhere('cm1.mailing_list_meta_choice_id = ?', $first);
}
} else {
$col = $ts[0]['id'];
$query->andWhere("c.{$col} = ?", $first);
// should be secure
}
$list = $query->execute();
$pledges = array();
$infos = array();
$pledge_table = PledgeTable::getInstance();
$pledge_info_columns = $petition->getPledgeInfoColumnsArray();
if ($list) {
if ($direct_contact) {
foreach ($list[0]['Contact'] as $choice) {
/* @var $choice Contact */
$choices[$choice['id']] = $choice['firstname'] . ' ' . $choice['lastname'];
}
$pledges = $pledge_table->getPledgesForContacts($list[0]['Contact'], $active_pledge_item_ids);
$infos = ContactTable::getInstance()->getPledgeInfoColumns($list[0]['Contact'], $pledge_info_columns);
} else {
if ($fix_field) {
$col = $ts[1]['id'];
foreach ($list[0]['Contact'] as $choice) {
$choices[$choice[$col]] = $choice[$col];
}
} else {
foreach ($list[0]['MailingListMeta'][0]['MailingListMetaChoice'] as $choice) {
$choices[$choice['id']] = $choice['choice'];
}
}
}
}
return array('choices' => $choices, 'pledges' => $direct_contact && $active_pledge_item_ids !== false ? $pledges : false, 'infos' => $infos);
}
