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; } }