<?php
/**
* CriteriaPlugin for phplist.
*
* This file is a part of CriteriaPlugin.
*
* CriteriaPlugin is free software: you can redistribute it and/or modify
* it under the terms of the GNU General Public License as published by
* the Free Software Foundation, either version 3 of the License, or
* (at your option) any later version.
* CriteriaPlugin is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU General Public License for more details.
*
* @category phplist
*
* @author Duncan Cameron
* @copyright 2014-2016 Duncan Cameron
* @license http://www.gnu.org/licenses/gpl.html GNU General Public License, Version 3
*/
/**
* DAO class that encapsulates the database access.
*
* @category phplist
*/
class SegmentPlugin_DAO extends CommonPlugin_DAO
{
private function formatInList(array $values)
{
return '(' . implode(', ', $values) . ')';
}
private function exclude($messageId)
{
$sql = <<<END
SELECT data
FROM {$this->tables['messagedata']}
WHERE name = 'excludelist' AND id = $messageId
END;
$excludeSubquery = '';
if ($data = $this->dbCommand->queryOne($sql, 'data')) {
$excluded = unserialize(substr($data, 4));
if (($key = array_search(-1, $excluded)) !== false) {
unset($excluded[$key]);
}
if (count($excluded) > 0) {
$inList = $this->formatInList($excluded);
$excludeSubquery = <<<END
u.id NOT IN (
SELECT userid
FROM {$this->tables['listuser']}
WHERE listid IN $inList
)
END;
}
}
return $excludeSubquery;
}
private function buildSubscriberQuery($select, $messageId, array $joins, $combine, $limit = 0)
{
$excludeSubquery = '';
if (USE_LIST_EXCLUDE) {
if ($excludeSubquery = $this->exclude($messageId)) {
$excludeSubquery = "AND (\n$excludeSubquery\n)";
}
}
$booleanOp = ($combine == SegmentPlugin_Operator::ONE) ? 'OR' : 'AND';
$extraJoin = '';
if (count($joins) > 0) {
$extraWhere = array();
foreach ($joins as $p) {
$extraJoin .= $p->join ? $p->join . "\n" : '';
$extraWhere[] = $p->where;
}
$w = "AND (\n" . implode("\n$booleanOp ", $extraWhere) . "\n)";
} else {
$w = '';
}
$query = <<<END
SELECT $select
FROM {$this->tables['user']} u
JOIN {$this->tables['listuser']} lu0 ON u.id = lu0.userid
JOIN {$this->tables['listmessage']} lm0 ON lm0.listid = lu0.listid AND lm0.messageid = $messageId
LEFT JOIN {$this->tables['usermessage']} um0 ON um0.userid = u.id AND um0.messageid = $messageId
$extraJoin
WHERE u.confirmed = 1
AND u.blacklisted = 0
AND (um0.status IS NULL OR um0.status IN ('not sent', 'todo'))
$excludeSubquery
$w
END;
if ($limit > 0) {
$query .= <<<END
LIMIT 0, $limit
END;
}
return $query;
}
/**
* Retrieves the values for a select/radio button attribute.
*
* @param array $attribute an attribute
*
* @return Iterator
*/
public function selectData(array $attribute)
{
$tableName = $this->table_prefix . 'listattr_' . $attribute['tablename'];
return $this->dbCommand->queryAll(<<<END
SELECT id, name
FROM $tableName
ORDER BY listorder, id
END
);
return $this->dbCommand->queryAll($sql);
}
/**
* Retrieves campaigns.
*
* @param string $loginId login id of the current admin
* @param int $max Maximum number of campaigns to be returned
* @param array $lists Lists to which the campaign is to be sent
*
* @return Iterator
*/
public function campaigns($loginId, $max, $lists)
{
$owner = $loginId ? "AND m.owner = $loginId" : '';
$inList = $this->formatInList($lists);
$sql = <<<END
SELECT DISTINCT m.id, CONCAT_WS(' - ',m.subject, DATE_FORMAT(m.sent,'%d/%m/%y')) AS subject, m.sent
FROM {$this->tables['message']} m
JOIN {$this->tables['listmessage']} lm ON m.id = lm.messageid AND lm.listid IN $inList
WHERE m.status = 'sent'
$owner
ORDER BY m.sent DESC
LIMIT $max
END;
return $this->dbCommand->queryAll($sql);
}
public function deleteNotSent($campaign)
{
$sql = "DELETE FROM {$this->tables['usermessage']}
WHERE status = 'not sent'
AND messageid = $campaign
";
return $this->dbCommand->queryAffectedRows($sql);
}
/**
* Returns the id of each subscriber to which the message will be sent.
*
* @param int $messageId message id
* @param array $joins
* @param int $combine whether to AND or OR conditions
*
* @return Iterator
*/
public function subscribers($messageId, array $joins, $combine)
{
$query = $this->buildSubscriberQuery('DISTINCT u.id', $messageId, $joins, $combine);
return $this->dbCommand->queryAll($query);
}
/**
* Returns the total number of subscribers to which the message will be sent and the email address of the
* first N subscribers.
*
* @param int $messageId message id
* @param array $joins
* @param int $combine whether to AND or OR conditions
* @param int $limit the maximum number of subscriber email addresses to return
*
* @return array [int, Iterator]
*/
public function calculateSubscribers($messageId, array $joins, $combine, $limit)
{
$query = $this->buildSubscriberQuery('SQL_CALC_FOUND_ROWS DISTINCT u.email', $messageId, $joins, $combine, $limit);
$subscribers = $this->dbCommand->queryAll($query);
$totalRows = $this->dbCommand->queryOne('SELECT FOUND_ROWS()');
return [$totalRows, $subscribers];
}
/**
* Returns the highest value of id from the user table.
*
* @return int the highest value of id
*/
public function highestSubscriberId()
{
$sql = "
SELECT MAX(id)
FROM {$this->tables['user']}
";
return $this->dbCommand->queryOne($sql);
}
/**
* Validate the syntax of a regular expression by trying to use it in a query.
*
* @return bool
*/
public function isRegexValid($regexp)
{
$regexp = sql_escape($regexp);
$sql = "SELECT '' REGEXP '$regexp'";
try {
$this->dbCommand->queryOne($sql);
} catch (\Exception $e) {
return false;
}
return true;
}
}