function initQuery($params)
{
$tables = Subscriptions::getTables();
$ttable = 'subscription_topic';
$tcolumns =& $tables[$ttable]['columns'];
$etable = 'subscription_email';
$ecolumns =& $tables[$etable]['columns'];
$atable = 'email_address';
$acolumns =& $tables[$atable]['columns'];
$this->addSelectTable($ttable);
$this->addSelectField($acolumns['email_id']);
$this->addSelectField($acolumns['email']);
$this->addSelectField($ecolumns['topic_id']);
$this->SelectGroup($ecolumns['email_id']);
$this->SelectGroup($ecolumns['topic_id']);
$this->addInnerJoin($etable, $ecolumns['topic_id'], DB_EQ, $tcolumns['topic_id']);
if (isset($params['email_id'])) {
$this->WhereValue($ecolumns['email_id'], DB_EQ, $email);
} else {
$this->addInnerJoin($atable, $acolumns['email_id'], DB_EQ, $ecolumns['email_id']);
$this->Where($acolumns['email'], DB_IN, DBQuery::arrayToIn($params['emails']));
}
if (isset($params['signed_in'])) {
$this->WhereAND();
$access = array(SUBSCRIPTION_TOPIC_FULL_ACCESS, $params['signed_in'] ? SUBSCRIPTION_TOPIC_REGISTERED_ONLY : SUBSCRIPTION_TOPIC_GUEST_ONLY);
$this->Where($tcolumns['topic_access'], DB_IN, DBQuery::arrayToIn($access));
}
if (!empty($params['active_only'])) {
$this->WhereAND();
$this->WhereValue($tcolumns['topic_status'], DB_EQ, SUBSCRIPTION_TOPIC_ACTIVE);
}
}
function getTopicsEmailsCount($topics_ids, $unique = true)
{
global $application;
if (empty($topics_ids)) {
return array();
}
$tables = $this->getTables();
$etable = 'subscription_email';
$ecolumns =& $tables[$etable]['columns'];
$query = new DB_Select($etable);
if ($unique) {
$query->addSelectField(DB_Select::fCountDistinct($ecolumns['email_id']), 'email_count');
} else {
$query->addSelectField(DB_Select::fCount($ecolumns['email_id']), 'email_count');
}
$query->Where($ecolumns['topic_id'], DB_IN, DBQuery::arrayToIn($topics_ids));
$res = $application->db->getDB_Result($query);
return $res[0]['email_count'];
}
function createUnsubscribeKeys($letter_id, $delivery_num, $topics_ids)
{
global $application;
$str_topics_ids = implode(',', $topics_ids);
// copy emails to unsubscribe table
$ntables = $this->getTables();
$stables = modApiFunc('Subscriptions', 'getTables');
$utable = 'newsletter_unsubscribe';
$ucolumns = $ntables[$utable]['columns'];
$ltable = 'newsletter_topics';
$lcolumns = $ntables[$ltable]['columns'];
$etable = 'subscription_email';
$ecolumns = $stables[$etable]['columns'];
$atable = 'email_address';
$acolumns = $stables[$atable]['columns'];
$iquery = new DB_Insert_Select($utable);
$iquery->setModifiers(DB_IGNORE);
$iquery->setInsertFields(array('key_unsubscribe', 'delivery_num', 'letter_id', 'email_id', 'topics_ids'));
$squery = new DB_Select($etable);
$squery->addSelectField('MD5(CONCAT("' . $delivery_num . '", "' . $letter_id . '", "' . $str_topics_ids . '", NOW(), ' . $ecolumns['email_id'] . ', ' . $acolumns['email'] . '))');
$squery->addSelectField($delivery_num . '-0', 'delivery_num');
$squery->addSelectField($letter_id . '+0', 'letter_id');
$squery->addSelectField($ecolumns['email_id']);
$squery->addSelectField(DBQuery::quoteValue($str_topics_ids));
$squery->addInnerJoin($atable, $ecolumns['email_id'], DB_EQ, $acolumns['email_id']);
$squery->Where($ecolumns['topic_id'], DB_IN, DBQuery::arrayToIn($topics_ids));
$squery->SelectGroup($ecolumns['email_id']);
$squery->SelectOrder($ecolumns['email_id'], 'ASC');
$iquery->setSelectQuery($squery);
$application->db->getDB_Result($iquery);
$this->_totalRecipients = $this->countTempEmails($delivery_num);
$this->_sentCountTotal = 0;
$result = array('Errors' => array($application->db->_getSQL($iquery)), 'Warnings' => array(), 'TotalCount' => $this->_totalRecipients, 'Num' => $delivery_num);
return $result;
}
