+ $res = XDB::query('SELECT id
+ FROM newsletter_issues
+ WHERE state = \'pending\' AND send_before <= NOW()');
+ $issues = array();
+ foreach ($res->fetchColumn() as $id) {
+ $issues[$id] = new NLIssue($id);
+ }
+ return $issues;
+ }
+
+ /** Retrieve a given issue of this NewsLetter
+ * @p $name Name or ID of the issue to retrieve.
+ * @return A NLIssue object.
+ *
+ * $name may be either a short_name, an ID or the special value 'last' which
+ * selects the latest sent NL.
+ * If $name is null, this will retrieve the current pending NL.
+ */
+ public function getIssue($name = null, $only_sent = true)
+ {
+ if ($name) {
+ if ($name == 'last') {
+ if ($only_sent) {
+ $where = 'state = \'sent\' AND ';
+ } else {
+ $where = '';
+ }
+ $res = XDB::query('SELECT MAX(id)
+ FROM newsletter_issues
+ WHERE ' . $where . ' nlid = {?}',
+ $this->id);
+ } else {
+ $res = XDB::query('SELECT id
+ FROM newsletter_issues
+ WHERE nlid = {?} AND (id = {?} OR short_name = {?})',
+ $this->id, $name, $name);
+ }
+ if (!$res->numRows()) {
+ throw new MailNotFound();
+ }
+ $id = $res->fetchOneCell();
+ } else {
+ $query = XDB::format('SELECT id
+ FROM newsletter_issues
+ WHERE nlid = {?} AND state = \'new\'
+ ORDER BY id DESC', $this->id);
+ $res = XDB::query($query);
+ if ($res->numRows()) {
+ $id = $res->fetchOneCell();
+ } else {
+ // Create a new, empty issue, and return it
+ $id = $this->createPending();
+ }
+ }
+
+ return new NLIssue($id, $this);
+ }
+
+ /** Create a new, empty, pending newsletter issue
+ * @p $nlid The id of the NL for which a new pending issue should be created.
+ * @return Id of the newly created issue.
+ */
+ public function createPending()
+ {
+ XDB::execute('INSERT INTO newsletter_issues
+ SET nlid = {?}, state=\'new\', date=NOW(),
+ title=\'to be continued\',
+ mail_title=\'to be continued\'',
+ $this->id);
+ return XDB::insertId();
+ }
+
+ /** Return all sent issues of this newsletter.
+ * @return An array of (id => NLIssue)
+ */
+ public function listSentIssues($check_user = false, $user = null)
+ {
+ if ($check_user && $user == null) {
+ $user = S::user();
+ }
+
+ $res = XDB::query('SELECT id
+ FROM newsletter_issues
+ WHERE nlid = {?} AND state = \'sent\'
+ ORDER BY date DESC', $this->id);
+ $issues = array();
+ foreach ($res->fetchColumn() as $id) {
+ $issue = new NLIssue($id, $this, false);
+ if (!$check_user || $issue->checkUser($user)) {
+ $issues[$id] = $issue;
+ }
+ }
+ return $issues;
+ }
+
+ /** Return all issues of this newsletter, including invalid and sent.
+ * @return An array of (id => NLIssue)
+ */
+ public function listAllIssues()
+ {
+ $res = XDB::query('SELECT id
+ FROM newsletter_issues
+ WHERE nlid = {?}
+ ORDER BY FIELD(state, \'pending\', \'new\') DESC, date DESC', $this->id);
+ $issues = array();
+ foreach ($res->fetchColumn() as $id) {
+ $issues[$id] = new NLIssue($id, $this, false);
+ }
+ return $issues;
+ }
+
+ /** Return the latest pending issue of the newsletter.
+ * @p $create Whether to create an empty issue if no pending issue exist.
+ * @return Either null, or a NL object.
+ */
+ public function getPendingIssue($create = false)
+ {
+ $res = XDB::query('SELECT MAX(id)
+ FROM newsletter_issues
+ WHERE nlid = {?} AND state = \'new\'',
+ $this->id);
+ $id = $res->fetchOneCell();
+ if ($id != null) {
+ return new NLIssue($id, $this);
+ } else if ($create) {
+ $id = $this->createPending();
+ return new NLIssue($id, $this);
+ } else {
+ return null;
+ }