protected function execute($arguments = array(), $options = array())
{
// initialize the database connection
$databaseManager = new sfDatabaseManager($this->configuration);
$connection = $databaseManager->getDatabase($options['connection'])->getConnection();
$signings_table = PetitionSigningTable::getInstance();
$search_table = PetitionSigningSearchTable::getInstance();
$q = $signings_table->createQuery('s')->select('min(s.id)');
if ($options['min']) {
$q->andWhere('s.id >= ?', $options['min']);
}
$min = $q->fetchArray();
$min = reset($min);
$min = reset($min);
$q = $signings_table->createQuery('s')->select('max(s.id)');
if ($options['max']) {
$q->andWhere('s.id <= ?', $options['max']);
}
$max = $q->fetchArray();
$max = reset($max);
$max = reset($max);
printf("min: %s\n", $min);
printf("max: %s\n", $max);
$pos = $min;
if ($options['spawn']) {
$step = 10000;
while ($pos <= $max) {
echo 'CALL ' . $pos . ' ' . ($pos + $step) . PHP_EOL;
$lastline = system('php symfony policat:signings-search --min=' . $pos . ' --max=' . ($pos + $step));
echo $lastline . PHP_EOL;
$pos += $step;
}
return;
}
$step = 1000;
while ($pos <= $max) {
$query = $signings_table->createQuery('s')->where('s.id >= ? and s.id < ?', array($pos, $pos + $step));
$signings = $query->execute();
printf("step: %s - %s\n", $pos, $pos + $step - 1);
foreach ($signings as $signing) {
/* @var $signing PetitionSigning */
// printf("s: %s\n", $signing->getId());
$search_table->savePetitionSigning($signing);
$signing->free();
}
$query->free();
$pos += $step;
}
}
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();
}
}
}
public function executeSign(sfWebRequest $request)
{
// hash check
$id = $request->getParameter('id');
$hash = $request->getParameter('hash');
if (!is_numeric($id) || !is_string($hash)) {
$this->forward404();
}
$id = ltrim($id, ' 0');
if (!Widget::isValidLastHash($id, $hash)) {
$this->forward404();
}
$this->setLayout(false);
$this->fetchWidget();
$this->petition = $this->widget['Petition'];
$this->petition_text = $this->widget['PetitionText'];
$this->lang = $this->petition_text['language_id'];
$this->getUser()->setCulture($this->lang);
$widget_texts = $this->petition->getWidgetIndividualiseText();
$this->title = $widget_texts && !empty($this->widget['title']) ? $this->widget['title'] : $this->petition_text['title'];
$this->target = $widget_texts ? $this->widget['target'] : $this->petition_text['target'];
$this->background = $widget_texts ? $this->widget['background'] : $this->petition_text['background'];
$this->paypal_email = StoreTable::value(StoreTable::DONATIONS_PAYPAL) ? $this->widget->getFinalPaypalEmail() : '';
$this->paypal_ref = sprintf("%s%s%s", $this->petition['id'], $this->petition_text['language_id'], $this->widget['id']);
$this->read_more_url = $this->petition['read_more_url'];
$this->width = $this->widget->getStyling('width');
$this->font_family = $this->petition->getStyleFontFamily();
$widget_colors = $this->petition->getWidgetIndividualiseDesign();
foreach (array('title_color', 'body_color', 'button_color', 'bg_left_color', 'bg_right_color', 'form_title_color') as $style) {
if ($widget_colors) {
$this->{$style} = $this->widget->getStyling($style, $this->petition['style_' . $style]);
} else {
$this->{$style} = $this->petition['style_' . $style];
}
}
$sign = new PetitionSigning();
$sign['Petition'] = $this->widget['Petition'];
$sign['Widget'] = $this->widget;
if ($request->isMethod('post') && $request->hasParameter('widget')) {
$form_param = $request->getParameter('widget');
if (is_scalar($form_param['edit_code']) && !empty($form_param['edit_code'])) {
$new_widget = Doctrine_Core::getTable('Widget')->createQuery('w')->where('w.id = ?', $id)->andWhere('w.edit_code = ?', $form_param['edit_code'])->addFrom('w.Campaign, w.Petition, w.PetitionText')->fetchOne();
}
}
if (!isset($new_widget)) {
$new_widget = new Widget();
$new_widget['Parent'] = $this->widget;
$new_widget['Campaign'] = $this->widget['Campaign'];
$new_widget['Petition'] = $this->widget['Petition'];
$new_widget['PetitionText'] = $this->widget['PetitionText'];
}
$this->form = new PetitionSigningForm($sign, array('validation_kind' => PetitionSigning::VALIDATION_KIND_EMAIL));
$this->form_embed = new WidgetPublicForm($new_widget);
$extra = array();
if ($this->getRequest()->isMethod('post')) {
$this->getResponse()->setContentType('text/javascript');
$ajax_response_form = null;
// It is a signing form
if ($request->hasParameter($this->form->getName())) {
if ($this->petition->isBefore() || $this->petition->isAfter()) {
return $this->renderText(json_encode(array('over' => true)));
}
$ajax_response_form = $this->form;
$this->form->bind($request->getPostParameter($this->form->getName()));
if ($this->form->isValid()) {
$this->form->save();
if (sfConfig::get('sf_environment') === 'stress') {
// ONLY FOR STRESS TEST !!!
$extra['code'] = $this->form->getObject()->getId() . '-' . $this->form->getObject()->getValidationData();
}
$search_table = PetitionSigningSearchTable::getInstance();
$search_table->savePetitionSigning($sign, false);
$con = $search_table->getConnection();
$ref = $this->form->getValue(Petition::FIELD_REF);
if (!(strpos($ref, 'http://') === 0 || strpos($ref, 'http://') === 0)) {
$ref = null;
}
$sql_time = gmdate('Y-m-d H:i:s');
// DQL query would invalidate petition cache so let's use SQL
$con->exec('update petition set activity_at = ? where id = ?', array($sql_time, $this->petition->getId()));
if ($ref) {
$con->exec('update widget set activity_at = ?, last_ref = ? where id = ?', array($sql_time, $ref, $this->widget->getId()));
} else {
$con->exec('update widget set activity_at = ? where id = ?', array($sql_time, $this->widget->getId()));
}
}
} else {
if ($request->hasParameter($this->form_embed->getName())) {
$ajax_response_form = $this->form_embed;
$this->form_embed->bind($request->getPostParameter($this->form_embed->getName()));
if ($this->form_embed->isValid()) {
$this->form_embed->save();
$extra['id'] = $this->form_embed->getObject()->getId();
$extra['edit_code'] = $this->form_embed->getObject()->getEditCode();
$extra['markup'] = UtilLink::widgetMarkup($extra['id']);
} else {
}
} else {
if ($request->hasParameter('target_selector')) {
$target_selector = $request->getParameter('target_selector');
if (is_scalar($target_selector)) {
return $this->renderText(json_encode($this->petition->getTargetSelectorChoices($target_selector)));
}
} else {
if ($request->hasParameter('target_selector1') && $request->hasParameter('target_selector2')) {
$target_selector1 = $request->getParameter('target_selector1');
$target_selector2 = $request->getParameter('target_selector2');
if (is_scalar($target_selector1) && is_scalar($target_selector2)) {
return $this->renderText(json_encode($this->petition->getTargetSelectorChoices2($target_selector1, $target_selector2)));
}
}
}
}
}
return $this->renderPartial('json_form', array('form' => $ajax_response_form, 'extra' => $extra));
}
}
/**
*
* @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;
}
protected function execute($arguments = array(), $options = array())
{
// initialize the database connection
$databaseManager = new sfDatabaseManager($this->configuration);
$connection = $databaseManager->getDatabase($options['connection'])->getConnection();
$filename = $arguments['filename'];
$petition_id = $arguments['petition_id'];
$search_table = PetitionSigningSearchTable::getInstance();
$petition = PetitionTable::getInstance()->findById($petition_id, true);
if (empty($petition)) {
echo "Petition not found.\n";
return;
}
$campaign = $petition->getCampaign();
$formfields = $petition->getFormfields();
$formfields[] = Petition::FIELD_REF;
$table = Doctrine_Core::getTable('PetitionSigning');
printf("Campaign: %s\nPetition: %s\n", $campaign['name'], $petition['name']);
$first_id = false;
$first_line = null;
if (($handle = @fopen($filename, "r")) !== false) {
$con = $table->getConnection();
$con->beginTransaction();
echo "Begin transaction\n";
try {
$emails = array();
$i = 0;
while (($data = fgetcsv($handle, 1000, ",")) !== false) {
$i++;
if ($i % 100 == 0) {
echo "{$i}\n";
}
//if ($i > 300) break;
if (!is_array($first_line)) {
$first_line = $data;
} else {
$line = array_combine($first_line, $data);
$signing = new PetitionSigning();
$signing->setPetitionId($petition_id);
$signing->setStatus(PetitionSigning::STATUS_VERIFIED);
foreach ($formfields as $formfield) {
switch ($formfield) {
// case 'created_at':
// $signing->setCreatedAt($line[$formfield]);
// break;
// case 'updated_at':
// $signing->setUpdatedAt($line[$formfield]);
// break;
case Petition::FIELD_FULLNAME:
$fullname = array();
if (isset($line[Petition::FIELD_FIRSTNAME])) {
$fullname[] = trim($line[Petition::FIELD_FIRSTNAME]);
}
if (isset($line[Petition::FIELD_LASTNAME])) {
$fullname[] = trim($line[Petition::FIELD_LASTNAME]);
}
if (isset($line[Petition::FIELD_FULLNAME])) {
$fullname[] = trim($line[Petition::FIELD_FULLNAME]);
}
$fullname = join(' ', $fullname);
$signing->setField(Petition::FIELD_FULLNAME, $fullname);
break;
case Petition::FIELD_SUBSCRIBE:
if (isset($line[Petition::FIELD_SUBSCRIBE]) && $line[Petition::FIELD_SUBSCRIBE] == '1') {
$signing->setField(Petition::FIELD_SUBSCRIBE, array('yes'));
} else {
$signing->setField(Petition::FIELD_SUBSCRIBE, array());
}
break;
default:
if (isset($line[$formfield])) {
$signing->setField($formfield, $line[$formfield]);
}
}
}
$signing->save();
$search_table->savePetitionSigning($signing, false);
if ($first_id === false) {
$first_id = $signing->getId();
}
$email = $signing->getField(Petition::FIELD_EMAIL);
if (is_string($email)) {
$emails[] = trim($email);
}
$signing->free();
}
}
echo "checking duplicates\n";
while (count($emails) > 0) {
$i = 0;
$where_param = array();
$where = array();
while ($i++ < 100) {
$email = array_shift($emails);
if (empty($email)) {
$break;
}
$where_param[] = $email;
$where[] = '(LOWER(ps.email) = LOWER(?))';
}
echo count($emails) . "\n";
$duplicates = $table->createQuery('ps')->where('ps.petition_id = ?', $petition_id)->andWhere('ps.id < ?', $first_id)->andWhere(join(' OR ', $where), $where_param)->execute();
foreach ($duplicates as $duplicate) {
$duplicate->setStatus(PetitionSigning::STATUS_DUPLICATE);
}
$duplicates->save();
$duplicates->free();
}
echo "Commit transaction";
$con->commit();
echo ".\n";
} catch (Exception $e) {
$con->rollback();
echo "DB error. (rollback)\n";
}
fclose($handle);
} else {
echo "File error.\n";
return;
}
}
