public function savePetitionSigning(PetitionSigning $signing, $clean = true)
{
$string = '';
foreach (array(Petition::FIELD_FULLNAME => 'fullname', Petition::FIELD_FIRSTNAME => 'firstname', Petition::FIELD_LASTNAME => 'lastname', Petition::FIELD_EMAIL => 'email', Petition::FIELD_ADDRESS => 'address', Petition::FIELD_CITY => 'city', Petition::FIELD_POSTCODE => 'post_code') as $field_name) {
$field = trim($signing->getField($field_name, ''));
if ($field) {
$string .= ' ' . $field;
}
}
$parts = explode(' ', PetitionSigningSearchTable::normalize($string));
$final_parts = array();
foreach ($parts as $part) {
$part = trim($part);
$len = mb_strlen($part, 'UTF-8');
if ($len > 2) {
if ($len > 48) {
$final_parts[] = mb_substr($part, 0, 48, 'UTF-8');
} else {
$final_parts[] = $part;
}
}
}
$unique_array = array_unique($final_parts, SORT_STRING);
if (count($unique_array)) {
if ($clean && $signing->getId()) {
$this->createQuery('pss')->where('pss.id = ?', $signing->getId())->delete()->execute();
}
$i = 0;
try {
$con = $this->getConnection();
$con->beginTransaction();
foreach ($unique_array as $keyword) {
if ($i++ > 10) {
break;
}
$psk = new PetitionSigningSearch();
$psk->setPetitionSigning($signing);
$psk->setKeyword($keyword);
$psk->save();
$psk->free();
}
$con->commit();
} catch (Exception $e) {
$con->rollback();
}
}
}
/**
*
* @param array $options
* @return Doctrine_Query
* @throws Exception
*/
public function query(array $options)
{
$query = $this->queryAll('ps')->leftJoin('ps.Widget w')->leftJoin('w.WidgetOwner wo')->leftJoin('w.PetitionText pt');
$options = array_merge(self::$DEFAULT_OPTIONS, $options);
$language = $options[self::LANGUAGE];
$country = $options[self::COUNTRY];
$subscriber = $options[self::SUBSCRIBER];
$campaign = $options[self::CAMPAIGN];
$petition = $options[self::PETITION];
$status = $options[self::STATUS];
$widget = $options[self::WIDGET];
$user = $options[self::USER];
$search = $options[self::SEARCH];
$order = $options[self::ORDER];
$widget_filter = $options[self::WIDGET_FILTER];
if ($status) {
$query->andWhere('ps.status = ?', $status);
}
if (!($petition || $campaign || $widget)) {
throw new Exception('campaign or petition required');
}
if ($petition) {
if (is_array($petition)) {
$ids = array();
foreach ($petition as $p) {
$ids[] = is_object($p) ? $p->getId() : $p;
}
$query->andWhereIn('ps.petition_id', $ids);
} else {
$query->andWhere('ps.petition_id = ?', is_object($petition) ? $petition->getId() : $petition);
}
}
if ($campaign) {
$query->leftJoin('ps.Petition p')->andWhere('p.status != ?', Petition::STATUS_DELETED);
if (is_array($campaign)) {
$ids = array();
foreach ($campaign as $c) {
$ids[] = is_object($c) ? $c->getId() : $c;
}
$query->andWhereIn('p.campaign_id', $ids);
} else {
$query->andWhere('p.campaign_id = ?', is_object($campaign) ? $campaign->getId() : $campaign);
}
}
if ($widget) {
$widget_id = is_object($widget) ? $widget->getId() : $widget;
if (is_array($widget_id)) {
$query->andWhereIn('ps.widget_id', $widget_id);
} else {
$query->andWhere('ps.widget_id = ?', $widget_id);
}
}
if ($language) {
if (is_array($language)) {
$query->andWhereIn('pt.language_id', $language);
} else {
$query->andWhere('pt.language_id = ?', $language);
}
}
if ($country) {
if (is_array($country)) {
$query->andWhereIn('ps.country', $country);
} else {
$query->andWhere('ps.country = ?', $country);
}
}
if ($subscriber) {
$query->andWhere('ps.subscribe = ?', PetitionSigning::SUBSCRIBE_YES);
if ($user) {
$user_id = is_object($user) ? $user->getId() : $user;
$query->andWhere('w.user_id = ? AND w.data_owner = ?', array($user_id, WidgetTable::DATA_OWNER_YES));
} else {
$query->andWhere('w.user_id is null OR w.data_owner = ?', WidgetTable::DATA_OWNER_NO);
}
}
if ($search) {
$search_normalized = PetitionSigningSearchTable::normalize($search);
$likes_dql = array();
$likes_param = array();
$i = 0;
foreach (explode(' ', $search_normalized) as $part) {
if ($i > 5) {
break;
}
$len = mb_strlen($part, 'UTF-8');
if ($len > 2) {
if ($len > 48) {
$part = mb_substr($part, 0, 48, 'UTF-8');
}
$i++;
$query->andWhere('ps.id in (SELECT search' . $i . '.id FROM PetitionSigningSearch search' . $i . ' WHERE search' . $i . '.keyword LIKE ?)', $part . '%');
}
}
}
switch ($order) {
case self::ORDER_ASC:
$query->orderBy('ps.id ASC');
break;
case self::ORDER_DESC:
$query->orderBy('ps.id DESC');
break;
}
if ($widget_filter) {
if (preg_match('/^u(\\d+)$/', $widget_filter, $matches)) {
$query->andWhere('w.user_id = ?', $matches[1]);
} elseif (preg_match('/^w(\\d+)$/', $widget_filter, $matches)) {
$query->andWhere('w.id = ?', $matches[1]);
} else {
$query->andWhere('w.organisation = ?', $widget_filter);
}
}
return $query;
}
