function standardizeFindOptions($findOptions)
{
$empty = array('order' => array(), 'limit' => null, 'group' => array());
$findOptions = array_merge($empty, $findOptions);
if (!empty($findOptions['model'])) {
$findOptions['table'] = $findOptions['model']->useTable;
$findOptions['alias'] = $findOptions['model']->alias;
}
unset($findOptions['model']);
if (!empty($findOptions['fields'])) {
$findOptions['fields'] = QueryUtil::aliasedFields($findOptions['fields']);
}
return $findOptions;
}
function byCode($code, $opt = array())
{
App::import('Lib', 'Newsletter.QueryUtil');
$findOpt = QueryUtil::mergeFindOpt(array('recursive' => -1), $opt, array('conditions' => array($this->alias . '.code' => $code)));
return $this->find('first', $findOpt);
}
function _fix_email_list_relation($sErrors, &$error)
{
$db =& ConnectionManager::getDataSource('default');
$this->NewsletterSendlistsEmail = ClassRegistry::init('Newsletter.NewsletterSendlistsEmail');
$this->NewsletterEmail = ClassRegistry::init('Newsletter.NewsletterEmail');
$queries = array();
$findOpt = array('fields' => array('NewsletterEmail.id', 'NewsletterEmail.id'), 'conditions' => array(), 'group' => '`email` HAVING COUNT(`id`) > 1', 'order' => 'COUNT(`id`) DESC', 'recursive' => -1);
$duplicated = $this->NewsletterEmail->find('list', $findOpt);
if (!empty($duplicated)) {
debug($duplicated);
$findOpt = array('fields' => array('NewsletterEmail.id', 'e2.sendlist_id'), 'conditions' => array('NewsletterEmail.id' => $duplicated), 'joins' => array(array('alias' => 'e2', 'table' => $this->NewsletterEmail->useTable, 'type' => 'INNER', 'conditions' => array('`e2`.`sendlist_id` != `NewsletterEmail`.`sendlist_id`', '`e2`.`email` = `NewsletterEmail`.`email`'))), 'group' => array('NewsletterEmail.id', 'e2.sendlist_id'), 'recursive' => -1, 'model' => $this->NewsletterEmail);
$query = $db->buildStatement(QueryUtil::standardizeFindOptions($findOpt), $findOpt['model']);
$insertStatement = 'INSERT INTO ' . $this->NewsletterSendlistsEmail->useTable . ' (`newsletter_email_id`,`newsletter_sendlist_id`) (' . $query . ')';
$queries[] = $insertStatement;
$findOpt = array('fields' => array('e2.*'), 'conditions' => array('NewsletterEmail.id' => $duplicated), 'joins' => array(array('alias' => 'e2', 'table' => $this->NewsletterEmail->useTable, 'type' => 'INNER', 'conditions' => array('`e2`.`id` != `NewsletterEmail`.`id`', '`e2`.`email` = `NewsletterEmail`.`email`'))), 'recursive' => -1, 'model' => $this->NewsletterEmail);
App::import('Lib', 'Newsletter.QueryUtil');
$query = $db->buildStatement(QueryUtil::standardizeFindOptions($findOpt), $findOpt['model']);
$queries[] = 'DELETE' . substr($query, 6);
}
$findOpt = array('fields' => array('NewsletterEmail.id', 'NewsletterEmail.sendlist_id'), 'conditions' => array('NewsletterEmail.sendlist_id IS NOT NULL'), 'recursive' => -1, 'model' => $this->NewsletterEmail);
$query = $db->buildStatement(QueryUtil::standardizeFindOptions($findOpt), $findOpt['model']);
$insertStatement = 'INSERT INTO ' . $this->NewsletterSendlistsEmail->useTable . ' (`newsletter_email_id`,`newsletter_sendlist_id`) (' . $query . ')';
$queries[] = $insertStatement;
$queries[] = 'ALTER TABLE `' . $this->NewsletterEmail->useTable . '` DROP `sendlist_id`;';
debug($queries);
foreach ($queries as $query) {
if (!$db->execute($query)) {
$error[] = __('Unable to execute query :', true) . ' ' . $query;
break;
}
}
}
}
}
} else {
if ('' == $obj['request']['mailUrl']) {
continue;
}
curl_setopt($ch, CURLOPT_URL, str_replace("{}", $this->email, $obj['request']['mailUrl']));
curl_setopt($ch, CURLOPT_POST, false);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
$result = curl_exec($ch);
if ($result == $obj['yesCode']['mailCode']) {
array_push($this->output, $obj);
} elseif (strlen($obj['yesCode']['mailCode']) > 5) {
if (strpos($result, $obj['yesCode']['mailCode']) !== false) {
array_push($this->output, $obj);
}
}
}
}
}
}
curl_close($ch);
closedir($handle);
}
}
}
$query = new QueryUtil("*****@*****.**");
echo count($query->check());
foreach ($query->output as $value) {
echo $value['name'] . "<br>";
}
<?php
include "DBConexion.php";
class QueryUtil extends DBConexion
{
/**
*
* @param String $query
* @param Array $arrayParameters
*/
function getPHPArray($query, $arrayParameters)
{
$stmt = mysqli_prepare($this->getConnection(), $query);
if (count($arrayParameters) > 0) {
//tenemos parametros para setear
foreach ($arrayParameters as $item) {
//echo $item["type"]."-".$item["value"]."<br />";
$stmt->bind_param($item["type"], $item["value"]);
}
}
$stmt->execute();
$stmt->bind_result($name, $code);
}
}
$query = "SELECT * FROM idiomas WHERE active = ?";
$arrayParameters = array();
$arrayParameters[] = array("type" => "i", "value" => 1);
$queryUtil = new QueryUtil();
$queryUtil->getPHPArray($query, $arrayParameters);
/**
* Returns a list of beans that matched the criteria
* given in the arguments
*
* @param db A database connection
* @param namedBean A template bean identifiying the query table/columns
* @param listBean Query parameters and results
* @param where optional where clause element(s)
* @param values optional values for prepared statement elements
* @return boolean true if the query was successful, else false
*/
public function select(&$db, $namedBean, &$listBean = null, $where = array(), $values = array())
{
Horde_Timer::singleton()->push();
if (empty($listBean)) {
$listBean = new ListBean();
}
$criteria = $listBean->get_criteria();
$currentPage = $listBean->get_current_page();
$pageSize = $listBean->get_page_size();
$orderBy = $listBean->get_order_by();
$cols = $namedBean->get_bean_name() . '.*';
// special handling to build timestamps from date columns
// This is done to preserve naming convention for dates
// while allowing the application to convert the value to a date OR
// timestamp as needed.
foreach ($namedBean as $key => $value) {
if (strpos(strtolower($key), 'timestamp') > 0) {
$cols .= ', TO_CHAR(' . str_replace('timestamp', 'date', $key) . ', \'YYYY-MM-DD HH24:MI:SS\') AS ' . $key;
}
}
foreach ($criteria as $property => $val) {
$opcode = '=';
$value = $val;
if (property_exists(get_class($namedBean), $property) && empty($value)) {
Horde::logMessage("NOTE: {$property} has empty value in _criteriaQuery", __FILE__, __LINE__, PEAR_LOG_WARNING);
}
if (property_exists(get_class($namedBean), $property) && !empty($value)) {
if (is_object($value) && get_class($value) == 'Criterion') {
$value = $val->get_value();
$opcode = $val->get_operator();
}
if (!is_numeric($value) && ($value == 'null' || $value == 'not null')) {
$where[] = $property . ' is ' . $value;
} else {
if (is_array($value)) {
$tokens = $delim = '';
foreach ($value as $var) {
$tokens .= $delim . '?';
$delim = ',';
$values[] = $var;
}
if (empty($tokens)) {
$where[] = '1=0';
} else {
if ($opcode == 'between' && count($value) == 2) {
$where[] = "{$property} between ? and ?";
} else {
$where[] = $property . ' in (' . $tokens . ')';
}
}
} else {
if (strpos(strtolower($property), 'timestamp') > 0) {
$where[] = $property . ' ' . $opcode . ' TO_DATE(?, \'YYYY-MM-DD HH24:MI:SS\')';
$values[] = date('Y-m-d H:i:s', strtotime($value));
} else {
if (strpos(strtolower($property), 'date') > 0) {
$where[] = 'trunc(' . $property . ') ' . $opcode . ' TO_DATE(?, \'YYYY-MM-DD\')';
$values[] = date('Y-m-d', strtotime($value));
} else {
if (is_string($value) && !is_numeric($value) && $opcode == '=') {
$where[] = $property . ' like ?';
$values[] = '%' . trim($value) . '%';
} else {
$where[] = $property . ' ' . $opcode . ' ?';
$values[] = $value;
}
}
}
}
}
}
}
$sql = 'SELECT ' . $cols . ' FROM ' . $namedBean->get_bean_name() . (count($where) ? ' WHERE ' . join(' AND ', $where) : '');
$record_count = $listBean->get_record_count();
if ($record_count == 0) {
$csql = str_replace($cols, 'COUNT(*) AS count', $sql);
Horde::logMessage($csql . "\n" . print_r($values, true), __FILE__, __LINE__, PEAR_LOG_DEBUG);
$result = $db->query($csql, $values);
if (is_a($result, 'PEAR_Error')) {
$listBean->setError($result);
QueryUtil::handleError($result, $csql, $values, __LINE__);
return false;
}
$row = $result->fetchRow(DB_FETCHMODE_ASSOC);
if (is_a($row, 'PEAR_Error')) {
$listBean->setError($row);
QueryUtil::handleError($row, $csql, $values, __LINE__);
return false;
}
$record_count = (int) $row['count'];
$listBean->set_record_count($record_count);
}
if ($pageSize < 0) {
$pageSize = $record_count;
}
$firstRow = $currentPage * $pageSize + 1;
if ($firstRow <= 0) {
$firstRow = 1;
$currentPage = 0;
}
$lastRow = ($currentPage + 1) * $pageSize;
if ($lastRow > $record_count) {
$lastRow = $record_count;
$currentPage = (int) (($record_count - 1) / $pageSize);
$firstRow = $currentPage * $pageSize + 1;
}
$listBean->set_current_page($currentPage);
$list = array();
if ($record_count > 0) {
global $conf;
if ($conf['data']['use_ci_sort']) {
foreach ($orderBy as &$sort) {
$parts = explode(' ', $sort);
$sort = 'NLS_UPPER(' . $parts[0] . ', \'NLS_SORT = GENERIC_BASELETTER\') ' . (count($parts) > 1 ? $parts[1] : '');
}
}
$psql = 'SELECT * FROM ( SELECT x.*, RowNum AS row_index FROM ( ' . $sql . (count($orderBy) ? ' ORDER BY ' . join(', ', $orderBy) : '') . ' ) x WHERE RowNum <= ' . $lastRow . ' ' . ' ) WHERE row_index >= ' . $firstRow;
Horde::logMessage($psql . "\n" . print_r($values, true), __FILE__, __LINE__, PEAR_LOG_DEBUG);
$beanClass = get_class($namedBean);
$beanKey = $namedBean->get_bean_key();
$resultSet = $db->query($psql, $values);
if (is_a($resultSet, 'PEAR_Error')) {
$listBean->setError($resultSet);
QueryUtil::handleError($resultSet, $psql, $values, __LINE__);
return false;
}
$row = $resultSet->fetchRow(DB_FETCHMODE_ASSOC);
while (!empty($row)) {
if (is_a($row, 'PEAR_Error')) {
$listBean->setError($row);
QueryUtil::handleError($row, $psql, $values, __LINE__);
return false;
}
if (is_array($beanKey)) {
$keyVars = array();
foreach ($beanKey as $key) {
$keyVars[] = $row[$key];
}
$listKey = join('|', $keyVars);
$list[$listKey] = new $beanClass($row);
} else {
$list[$row[$beanKey]] = new $beanClass($row);
}
$row = $resultSet->fetchRow(DB_FETCHMODE_ASSOC);
}
} else {
Horde::logMessage('No matching rows: ' . $sql, __FILE__, __LINE__, PEAR_LOG_INFO);
}
$listBean->set_list($list);
global $dispatcher;
$dispatcher->timers[__CLASS__][__FUNCTION__][] = Horde_Timer::singleton()->pop();
return true;
}
function _build($sending)
{
App::import('Lib', 'Newsletter.Sendlist');
if (is_numeric($sending)) {
$sending = $this->NewsletterSending->read(null, $sending);
}
if (empty($sending)) {
$this->_consoleOut(false, __d('newsletter', 'Invalid Newsletter Sending', true), array('exit' => true));
}
$id = $sending['NewsletterSending']['id'];
$db =& ConnectionManager::getDataSource($this->NewsletterSended->useDbConfig);
$this->_updateProcessTime($id);
$this->_consoleOut($id, __d('newsletter', 'Start Building Sending', true));
//=========================== Close Cancelled Sending ===========================
$toClose = $this->NewsletterSending->find('list', array('fields' => array('id', 'id'), 'conditions' => array('active' => 0, 'newsletter_id' => $sending['NewsletterSending']['newsletter_id'], 'not' => array('status' => array('done', 'cancelled'))), 'recursive' => -1));
if (!empty($toClose)) {
$this->NewsletterSending->cancel($toClose, false);
$this->_consoleOut($id, sprintf(__d('newsletter', '%s old sending cancelled', true), count($toClose)));
}
//=========================== Data ===========================
$basicInfo = array('active' => 1, 'status' => "ready", 'sending_id' => $id, 'newsletter_id' => $sending['NewsletterSending']['newsletter_id'], 'date' => date('Y-m-d H:i:s'), 'email' => null);
//=========================== Split Dynamic sendlists ===========================
$sendlists = array();
$dynSendlists = array();
if (!empty($sending['NewsletterSending']['selected_lists'])) {
foreach ($sending['NewsletterSending']['selected_lists'] as $newsletterSendlist) {
if (Sendlist::isTabled($newsletterSendlist)) {
$dynSendlists[] = $newsletterSendlist;
} else {
$sendlists[] = $newsletterSendlist;
}
}
}
//=========================== Grouping ===========================
$grouping = $sending['Newsletter']['TemplateConfig']->getGrouping($sending);
if (!empty($grouping)) {
$this->_consoleOut($id, __d('newsletter', 'Calculating variants', true));
$code = sha1(serialize($grouping['fields']));
$groups = array($code => array('active' => 1, 'conditions' => $grouping['fields'], 'newsletter_id' => $sending['NewsletterSending']['newsletter_id'], 'code' => $code));
$listsGroups = array();
if (!empty($sendlists)) {
//--------- normal sendlists ---------
$findOpt = Sendlist::addSendlistsEmailCond($sendlists, array('fields' => array('NewsletterEmail.id', 'NewsletterEmail.name', 'NewsletterEmail.email'), 'conditions' => array('NewsletterEmail.active' => 1), 'group' => 'NewsletterEmail.id', 'recursive' => -1));
$findOpt = $this->_groupingBaseFindOpt($grouping, array_keys($this->NewsletterSendlist->NewsletterEmail->schema()));
if (!empty($findOpt)) {
$lgroups = $this->NewsletterSendlist->NewsletterEmail->find('all', $findOpt);
foreach ($lgroups as $group) {
$listsGroups['Basic'][] = array('cond' => $group[$this->NewsletterSendlist->NewsletterEmail->alias]);
}
}
}
if (!empty($dynSendlists)) {
//--------- dynamic sendlists ---------
foreach ($dynSendlists as $newsletterSendlist) {
$sendlist = Sendlist::getSendlist($newsletterSendlist);
$cgrouping = $grouping;
if ($cgrouping['bySendlist']) {
$cgrouping = $sending['Newsletter']['TemplateConfig']->getGrouping($sending, $sendlist);
}
$findOpt = $this->_groupingBaseFindOpt($cgrouping, array_keys($sendlist->emailFields()));
if (!empty($findOpt)) {
$lgroups = $sendlist->findEmail('all', $findOpt);
foreach ($lgroups as $group) {
$listsGroups[$newsletterSendlist][] = array('cond' => $group[$sendlist->EmailModel->alias]);
}
}
}
}
foreach ($listsGroups as &$lgroup) {
foreach ($lgroup as &$group) {
$cond = array_merge($cgrouping['fields'], $group['cond']);
$code = sha1(serialize($cond));
if (!isset($groups[$code])) {
$groups[$code] = array('active' => 1, 'conditions' => $cond, 'newsletter_id' => $sending['NewsletterSending']['newsletter_id'], 'code' => $code);
}
$group['variant'] =& $groups[$code];
}
}
unset($lgroup);
unset($group);
$this->_consoleOut($id, __d('newsletter', 'Save variants', true));
$existing = $this->NewsletterVariant->find('list', array('fields' => array('code', 'id'), 'conditions' => array('newsletter_id' => $sending['NewsletterSending']['newsletter_id'], 'code' => array_keys($groups)), 'recursive' => -1));
foreach ($groups as &$group) {
if (isset($existing[$group['code']])) {
$group['id'] = $existing[$group['code']];
} else {
$this->NewsletterVariant->create();
$this->NewsletterVariant->save($group);
$group['id'] = $this->NewsletterVariant->id;
}
}
unset($group);
//debug($listsGroups);
}
//debug($sending);
$queries = array();
//=========================== Build normal sendlists ===========================
$this->_consoleOut($id, sprintf(__d('newsletter', '%s normal Sendlists found', true), count($sendlists)));
if (!empty($sendlists)) {
$lGroups = !empty($listsGroups['Basic']) ? $listsGroups['Basic'] : array(null);
foreach ($lGroups as $group) {
//If any sendeded has been added in this same group we assume it the group was allready added by an interucpted build or a parralel process and we skip the query
$findOpt = array('conditions' => array('tabledlist_id IS NULL', 'email_id IS NOT NULL', 'sending_id' => $id), 'recursive' => -1);
if (!empty($group)) {
$findOpt['conditions']['newsletter_variant_id'] = $group['variant']['id'];
}
if (!$this->NewsletterSended->find('count', $findOpt)) {
$findOpt = Sendlist::addSendlistsEmailCond($sendlists, array('fields' => array('NewsletterEmail.id', 'NewsletterEmail.name', 'NewsletterEmail.email'), 'conditions' => array('NewsletterEmail.active' => 1), 'group' => 'NewsletterEmail.id', 'recursive' => -1, 'msg' => ''));
if ($sending['NewsletterSending']['check_sended']) {
$findOpt['joins'][] = array('table' => $this->NewsletterSended->useTable, 'alias' => $this->NewsletterSended->alias, 'type' => 'left', 'foreignKey' => false, 'conditions' => array($this->NewsletterSendlist->NewsletterEmail->alias . '.id = ' . $this->NewsletterSended->alias . '.email_id', $this->NewsletterSended->alias . '.newsletter_id' => $sending['NewsletterSending']['newsletter_id'], 'not' => array($this->NewsletterSended->alias . '.status' => 'cancelled')));
$findOpt['conditions'][] = $this->NewsletterSended->alias . '.id IS NULL';
}
if (!empty($group)) {
$findOpt['conditions'][] = $group['cond'];
$findOpt['fields']['newsletter_variant_id'] = $group['variant']['id'];
$findOpt['msg'] .= sprintf(__d('newsletter', 'Variant id : %s', true), $group['variant']['id']);
} else {
$findOpt['msg'] .= __d('newsletter', 'Normal sendlists', true);
}
//debug($findOpt);
$findOpt['model'] = $this->NewsletterSendlist->NewsletterEmail;
$queries[] = $findOpt;
}
$this->_updateProcessTime($id, true);
}
}
/*
foreach($sendlists as $list){
$this->_consoleOut($id,sprintf(__d('newsletter','Get query for sendlist id : %s', true),$list));
$this->NewsletterSendlist->NewsletterEmail->bindModel(array(
'hasOne' => array(
'NewsletterSendlistsEmail' => array(
'className' => 'Newsletter.NewsletterSendlistsEmail'
)
)
),false);
$mailsFindOptions = array(
'fields'=>array('NewsletterEmail.id','NewsletterEmail.name','NewsletterEmail.email'),
'conditions'=>array('NewsletterSendlistsEmail.newsletter_sendlist_id'=>$list,'NewsletterEmail.active'=>1)
);
$lGroups = !empty($listsGroups[$list])?$listsGroups[$list]:array(null);
foreach($lGroups as $group){
$finalFindOptions = Set::merge($mailsFindOptions,(array)$findOptions);
if(!empty($group)){
$finalFindOptions['conditions'][] = $group['cond'];
$finalFindOptions['fields']['newsletter_variant_id'] = $group['variant']['id'];
}
$finalFindOptions['fields']['sendlist_id'] = $list;
$finalFindOptions['model'] = $this->NewsletterSendlist->NewsletterEmail;
$queries[] = $finalFindOptions;
}
}*/
//=========================== Build Dynamic sendlists ===========================
$this->_consoleOut($id, sprintf(__d('newsletter', '%s dynamic Sendlists found', true), count($dynSendlists)));
if (!empty($dynSendlists)) {
$findOptions = array();
$join = array('table' => $this->NewsletterSended->useTable, 'alias' => $this->NewsletterSended->alias, 'type' => 'left', 'foreignKey' => false, 'conditions' => array($this->NewsletterSendlist->NewsletterEmail->alias . '.email = ' . $this->NewsletterSended->alias . '.email', $this->NewsletterSended->alias . '.newsletter_id' => $sending['NewsletterSending']['newsletter_id'], 'not' => array($this->NewsletterSended->alias . '.status' => 'cancelled')));
if (!$sending['NewsletterSending']['check_sended']) {
$join['conditions'][$this->NewsletterSended->alias . '.sending_id'] = $id;
}
$opt = array('joins' => array($join), 'conditions' => array($this->NewsletterSended->alias . '.email IS NULL'));
$findOptions = Set::merge($findOptions, (array) $opt);
foreach ($dynSendlists as $list) {
$this->_consoleOut($id, sprintf(__d('newsletter', 'Get query for Dynamic sendlist id : %s', true), $list));
$tableSendlist = $this->NewsletterFunct->getTableSendlistID($list, true);
$lGroups = !empty($listsGroups[$list]) ? $listsGroups[$list] : array(null);
foreach ($lGroups as $group) {
$finalFindOptions = $findOptions;
$finalFindOptions['group'] = 'NewsletterEmail.email';
if (!empty($group)) {
$finalFindOptions['conditions'][] = $group['cond'];
}
if ($tableSendlist['modelClass']->useDbConfig != $this->NewsletterSended->useDbConfig) {
$finalFindOptions = array();
if (!empty($group)) {
$finalFindOptions['conditions'][] = $group['cond'];
}
$finalFindOptions = $this->NewsletterFunct->tabledEmailGetFindOptions($list, true);
$finalFindOptions['tableSendlist'] = $tableSendlist;
} else {
$finalFindOptions = $this->NewsletterFunct->tabledEmailGetFindOptions($list, true, $finalFindOptions);
}
$finalFindOptions['msg'] = sprintf(__d('newsletter', 'Dynamic sendlist id : %s', true), $list);
if (!empty($group)) {
$finalFindOptions['msg'] .= '; ' . sprintf(__d('newsletter', 'Variant id : %s', true), $group['variant']['id']);
$finalFindOptions['fields']['newsletter_variant_id'] = $group['variant']['id'];
}
$finalFindOptions['fields']['tabledlist_id'] = $list;
$queries[] = $finalFindOptions;
}
$this->_updateProcessTime($id, true);
}
}
/*if(!empty($grouping)){
debug($grouping);
}*/
/*foreach ($queries as $q) {
$q['model'] = $q['model']->alias;
debug($q);
}*/
//exit();
//=========================== Save Queries ===========================
foreach ($queries as $query) {
//--- normalize Queries ---
$fields = $this->NewsletterFunct->fieldsAddAlias($query['fields']);
$insertFields = $this->NewsletterSended->tcheckSaveFields(array_keys($fields));
//debug($insertFields);
$fields = array_intersect_key($fields, array_flip($insertFields));
$query['fields'] = $fields;
if ($query['model']->useDbConfig != $this->NewsletterSended->useDbConfig) {
//--------------- external database ---------------
$this->_consoleOut($id, sprintf(__d('newsletter', 'The sendlist id : %s Is using an external Database', true), $query['fields']['tabledlist_id']));
$this->_consoleOut($id, sprintf(__d('newsletter', 'Retrieving data', true), $query['fields']['tabledlist_id']));
$tableSendlist = $query['tableSendlist'];
//debug($tableSendlist);
unset($query['tableSendlist']);
$query['limit'] = 200;
$i = 0;
do {
$query['page'] = $i + 1;
App::import('Lib', 'Newsletter.QueryUtil');
$emails = $query['model']->find('all', QueryUtil::standardizeFindOptions($query));
if (!empty($emails)) {
$this->_consoleOut($id, sprintf(__d('newsletter', '%s Email read', true), count($emails)));
//debug($emails);
App::import('Lib', 'Newsletter.SetMulti');
$adresses = Set::extract('{n}.' . $query['model']->alias . '.email', $emails);
//debug($adresses);
//--- get duplicata ---
$dupFindOpt = array('fields' => array('id', 'email'), 'conditions' => array('email' => $adresses, 'newsletter_id' => $sending['NewsletterSending']['newsletter_id'], 'not' => array($this->NewsletterSended->alias . '.status' => 'cancelled')), 'recursive' => -1);
if (!$sending['NewsletterSending']['check_sended']) {
$dupFindOpt['conditions']['sending_id'] = $id;
}
$duplicata = $this->NewsletterSended->find('list', $dupFindOpt);
if (!empty($duplicata)) {
$this->_consoleOut($id, sprintf(__d('newsletter', '%s dupliqued email Ignored', true), count($duplicata)));
//debug($duplicata);
}
//--- format data ---
$toSave = array();
foreach ($emails as $mail) {
$mailData = $this->NewsletterFunct->tabledEmailGetFields($mail, $tableSendlist);
if (!in_array($mailData['email'], $duplicata)) {
$mailData = array_intersect_key($mailData, array_flip($insertFields));
$mailData['email_id'] = $mailData['id'];
unset($mailData['id']);
$mailData = array_merge($basicInfo, $mailData);
$toSave[] = $mailData;
}
}
//debug($toSave);
//--- save ---
if (!empty($toSave)) {
$toSaveSql = array();
foreach ($toSave as $d) {
$toSaveSql[] = "(" . implode(",", $this->NewsletterFunct->valFields($d)) . ")";
}
$insertStatement = 'INSERT INTO ' . $db->fullTableName($this->NewsletterSended) . ' (`' . implode("`,`", array_keys($toSave[0])) . '`) VALUES ' . implode(",", $toSaveSql);
//debug($insertStatement);
if ($db->execute($insertStatement)) {
$this->_consoleOut($id, sprintf(__d('newsletter', '%s saved Emails', true), count($toSave)));
}
/*if($this->NewsletterSended->saveAll($toSave)){
$this->_consoleOut($id,sprintf(__d('newsletter','%s saved Emails', true),count($toSave)));
}*/
}
$this->_updateProcessTime($id, true);
}
$i++;
/*if($i>=3){
$viewClass = $this->view;
if ($viewClass != 'View') {
list($plugin, $viewClass) = pluginSplit($viewClass);
$viewClass = $viewClass . 'View';
App::import('View', $this->view);
}
$View = new $viewClass($this, false);
echo $View->element('sql_dump');
exit();
}*/
} while (!empty($emails));
/*$this->_consoleOut($id,
__d('newsletter','External Database lists are not supported yet', true),
array('exit'=>true)
);*/
} else {
$query['fields']['email_id'] = $query['fields']['id'];
unset($query['fields']['id']);
$query['fields'] = array_merge($this->NewsletterFunct->valFields($basicInfo), $query['fields']);
App::import('Lib', 'Newsletter.QueryUtil');
$selectStatement = $db->buildStatement(QueryUtil::standardizeFindOptions($query), $query['model']);
//--- make insert Queries ---
$fields = $this->NewsletterFunct->fieldsAddAlias($query['fields']);
$insertFields = $this->NewsletterSended->tcheckSaveFields(array_keys($fields));
$insertQuery = array('table' => $db->fullTableName($this->NewsletterSended), 'fields' => array(), 'select' => $selectStatement);
foreach ($insertFields as $f) {
$insertQuery['fields'][] = $db->name($f);
}
$insertQuery['fields'] = implode(', ', $insertQuery['fields']);
$insertStatement = 'INSERT INTO ' . $insertQuery['table'] . ' (' . $insertQuery['fields'] . ') (' . $insertQuery['select'] . ')';
$msg = str_replace('%msg%', $query['msg'], __d('newsletter', 'Execute query : %msg%', true));
$this->_consoleOut($id, $msg);
if ($db->execute($insertStatement)) {
$this->_consoleOut($id, sprintf(__d('newsletter', '%s saved Emails', true), $this->NewsletterSended->getAffectedRows()));
} else {
$this->_consoleOut($id, __d('newsletter', 'Could not save emails', true), array('exit' => true));
}
$this->_updateProcessTime($id, true);
}
}
//=========================== Build Aditionnal Emails ===========================
if (!empty($sending['NewsletterSending']['additional_emails'])) {
$this->_consoleOut($id, __d('newsletter', 'Build Aditionnal Emails', true));
//--- format emails ---
$emails = explode(',', $sending['NewsletterSending']['additional_emails']);
$named = "/^<([^>]*)>(.*)\$/";
$add_emails = array();
foreach ($emails as $key => $email) {
$email = array('email' => trim($email));
if (preg_match($named, $email['email'], $match)) {
$email['name'] = $match[1];
$email['email'] = $match[2];
}
$email = array_merge($basicInfo, $email);
$add_emails[$email['email']] = $email;
}
//--- tcheck for duplicate ---
$findOpt = array('fields' => array('DISTINCT email'), 'conditions' => array('email' => array_keys($add_emails), 'newsletter_id' => $sending['NewsletterSending']['newsletter_id'], 'not' => array($this->NewsletterSended->alias . '.status' => 'cancelled')));
if (!$sending['NewsletterSending']['check_sended']) {
$findOpt['conditions']['sending_id'] = $id;
}
$this->NewsletterSended->recursive = -1;
$dub = $this->NewsletterSended->find('all', $findOpt);
if (!empty($dub)) {
$this->_consoleOut($id, sprintf(__d('newsletter', '%s dupliqued Email found, they will be ignored', true), count($dub)));
foreach ($dub as $key => $val) {
$dub[$key] = $val['NewsletterSended']['email'];
}
$add_emails = array_diff_key($add_emails, array_flip($dub));
}
//--- save ---
if (!empty($add_emails)) {
if ($this->NewsletterSended->createMany(array_keys($basicInfo), array_values($add_emails))) {
$this->_consoleOut($id, sprintf(__d('newsletter', '%s saved Emails', true), $this->NewsletterSended->getAffectedRows()));
} else {
$this->_consoleOut($id, __d('newsletter', 'Could not save emails', true), array('exit' => true));
}
} else {
$this->_consoleOut($id, __d('newsletter', 'No Emails saved', true));
}
}
//=========================== Done ===========================
$this->NewsletterSending->create();
$data = array('NewsletterSending' => array('id' => $id, 'status' => 'render'));
if ($this->NewsletterSending->save($data)) {
$this->_consoleOut($id, __('Building Complete.', true));
} else {
$this->_consoleOut($id, __('Could not start Sending', true), array('exit' => true));
}
return true;
}
function fix_emailListRelation($error, $opt, &$msg)
{
$_this =& NewsletterUpgrade::getInstance();
$db = ConnectionManager::getDataSource($_this->connection);
App::import('Lib', 'Newsletter.QueryUtil');
$this->NewsletterSendlistsEmail = ClassRegistry::init('Newsletter.NewsletterSendlistsEmail');
$this->NewsletterEmail = ClassRegistry::init('Newsletter.NewsletterEmail');
$query = 'SHOW INDEX FROM `' . $this->NewsletterEmail->useTable . '` WHERE KEY_NAME = \'newsletter_sendlist_id\'';
if (mysql_num_rows($db->execute($query))) {
$query = 'ALTER TABLE `' . $this->NewsletterEmail->useTable . '` DROP INDEX `newsletter_sendlist_id`;';
if (!$db->execute($query)) {
$msg[] = __('Unable to execute query :', true) . ' ' . $query;
return false;
}
}
$queries = array();
$findOpt = array('fields' => array('NewsletterEmail.id', 'NewsletterEmail.id'), 'conditions' => array(), 'group' => '`email` HAVING COUNT(`id`) > 1', 'order' => 'COUNT(`id`) DESC', 'recursive' => -1);
$duplicated = $this->NewsletterEmail->find('list', $findOpt);
if (!empty($duplicated)) {
//debug($duplicated);
$findOpt = array('fields' => array('NewsletterEmail.id', 'e2.sendlist_id'), 'conditions' => array('NewsletterEmail.id' => $duplicated), 'joins' => array(array('alias' => 'e2', 'table' => $this->NewsletterEmail->useTable, 'type' => 'INNER', 'conditions' => array('`e2`.`sendlist_id` != `NewsletterEmail`.`sendlist_id`', '`e2`.`email` = `NewsletterEmail`.`email`'))), 'group' => array('NewsletterEmail.id', 'e2.sendlist_id'), 'recursive' => -1, 'model' => $this->NewsletterEmail);
$query = $db->buildStatement(QueryUtil::standardizeFindOptions($findOpt), $findOpt['model']);
$insertStatement = 'INSERT INTO ' . $this->NewsletterSendlistsEmail->useTable . ' (`newsletter_email_id`,`newsletter_sendlist_id`) (' . $query . ')';
$queries[] = $insertStatement;
$findOpt = array('fields' => array('e2.*'), 'conditions' => array('NewsletterEmail.id' => $duplicated), 'joins' => array(array('alias' => 'e2', 'table' => $this->NewsletterEmail->useTable, 'type' => 'INNER', 'conditions' => array('`e2`.`id` != `NewsletterEmail`.`id`', '`e2`.`email` = `NewsletterEmail`.`email`'))), 'recursive' => -1, 'model' => $this->NewsletterEmail);
App::import('Lib', 'Newsletter.QueryUtil');
$query = $db->buildStatement(QueryUtil::standardizeFindOptions($findOpt), $findOpt['model']);
$queries[] = 'DELETE' . substr($query, 6);
}
$findOpt = array('fields' => array('NewsletterEmail.id', 'NewsletterEmail.sendlist_id'), 'conditions' => array('NewsletterEmail.sendlist_id IS NOT NULL'), 'recursive' => -1, 'model' => $this->NewsletterEmail);
$query = $db->buildStatement(QueryUtil::standardizeFindOptions($findOpt), $findOpt['model']);
$insertStatement = 'INSERT INTO ' . $this->NewsletterSendlistsEmail->useTable . ' (`newsletter_email_id`,`newsletter_sendlist_id`) (' . $query . ')';
$queries[] = $insertStatement;
$queries[] = 'ALTER TABLE `' . $this->NewsletterEmail->useTable . '` DROP `sendlist_id`;';
foreach ($queries as $query) {
if (!$db->execute($query)) {
$msg[] = __('Unable to execute query :', true) . ' ' . $query;
return false;
break;
}
}
return true;
}
