+ /** 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;
+ }
+ }
+
+ /** Returns a list of either issues or articles corresponding to the search.
+ * @p $search The searched pattern.
+ * @p $field The fields where to search, if none given, search in all possible fields.
+ * @return The list of object found.
+ */
+ public function issueSearch($search, $field, $user)
+ {
+ $search = XDB::formatWildcards(XDB::WILDCARD_CONTAINS, $search);
+ if ($field == self::SEARCH_ALL) {
+ $where = '(title ' . $search . ' OR mail_title ' . $search . ' OR head ' . $search . ' OR signature ' . $search . ')';
+ } elseif ($field == self::SEARCH_TITLE) {
+ $where = '(title ' . $search . ' OR mail_title ' . $search . ')';
+ } else {
+ $where = $field . $search;
+ }
+ $list = XDB::fetchColumn('SELECT DISTINCT(id)
+ FROM newsletter_issues
+ WHERE nlid = {?} AND state = \'sent\' AND ' . $where . '
+ ORDER BY date DESC',
+ $this->id);
+
+ $issues = array();
+ foreach ($list as $id) {
+ $issue = new NLIssue($id, $this, false);
+ if ($issue->checkUser($user)) {
+ $issues[] = $issue;
+ }
+ }
+ return $issues;
+ }
+
+ public function articleSearch($search, $field, $user)
+ {
+ $search = XDB::formatWildcards(XDB::WILDCARD_CONTAINS, $search);
+ if ($field == self::SEARCH_ALL) {
+ $where = '(a.title ' . $search . ' OR a.body ' . $search . ' OR a.append ' . $search . ')';
+ } else {
+ $where = 'a.' . $field . $search;
+ }
+ $list = XDB::fetchAllAssoc('SELECT i.short_name, a.aid, i.id, a.title
+ FROM newsletter_art AS a
+ INNER JOIN newsletter_issues AS i ON (a.id = i.id)
+ WHERE i.nlid = {?} AND i.state = \'sent\' AND ' . $where . '
+ GROUP BY a.id, a.aid
+ ORDER BY i.date DESC, a.aid',
+ $this->id);
+
+ $articles = array();
+ foreach ($list as $item) {
+ $issue = new NLIssue($item['id'], $this, false);
+ if ($issue->checkUser($user)) {
+ $articles[] = $item;
+ }
+ }
+ return $articles;
+ }
+
+ // }}}
+ // {{{ Subscription related function
+
+ /** Unsubscribe a user from this newsletter
+ * @p $uid UID to unsubscribe from the newsletter; if null, use current user.
+ * @p $hash True if the uid is actually a hash.
+ * @return True if the user was successfully unsubscribed.
+ */
+ public function unsubscribe($issue_id = null, $uid = null, $hash = false)
+ {
+ if (is_null($uid) && $hash) {
+ // Unable to unsubscribe from an empty hash
+ return false;
+ }
+ $user = is_null($uid) ? S::user()->id() : $uid;
+ $field = $hash ? 'hash' : 'uid';
+ $res = XDB::query('SELECT uid
+ FROM newsletter_ins
+ WHERE nlid = {?} AND ' . $field . ' = {?}',
+ $this->id, $user);
+ if (!$res->numRows()) {
+ // No subscribed user with that UID/hash
+ return false;
+ }
+ $user = $res->fetchOneCell();
+
+ XDB::execute('DELETE FROM newsletter_ins
+ WHERE nlid = {?} AND uid = {?}',
+ $this->id, $user);
+ if (!is_null($issue_id)) {
+ XDB::execute('UPDATE newsletter_issues
+ SET unsubscribe = unsubscribe + 1
+ WHERE id = {?}',
+ $id);
+ }
+ return true;
+ }
+
+ /** Subscribe a user to a newsletter
+ * @p $user User to subscribe to the newsletter; if null, use current user.
+ */
+ public function subscribe($user = null)
+ {
+ if (is_null($user)) {
+ $user = S::user();
+ }
+ if (self::maySubscribe($user)) {
+ XDB::execute('INSERT IGNORE INTO newsletter_ins (nlid, uid, last, hash)
+ VALUES ({?}, {?}, NULL, hash)',
+ $this->id, $user->id());
+ }
+ }
+
+ /** Subscribe a batch of users to a newsletter.
+ * This skips 'maySubscribe' test.
+ *
+ * @p $user_ids Array of user IDs to subscribe to the newsletter.
+ */
+ public function bulkSubscribe($user_ids)
+ {
+ // TODO: use a 'bulkMaySubscribe'.
+ XDB::execute('INSERT IGNORE INTO newsletter_ins (nlid, uid, last, hash)
+ SELECT {?}, a.uid, NULL, NULL
+ FROM accounts AS a
+ WHERE a.uid IN {?}',
+ $this->id, $user_ids);
+ }
+
+ /** Retrieve subscription state of a user
+ * @p $user Target user; if null, use current user.
+ * @return Boolean: true if the user has subscribed to the NL.
+ */
+ public function subscriptionState($user = null)
+ {
+ if (is_null($user)) {
+ $user = S::user();
+ }
+ $res = XDB::query('SELECT 1
+ FROM newsletter_ins
+ WHERE nlid = {?} AND uid = {?}',
+ $this->id, $user->id());
+ return ($res->numRows() == 1);
+ }
+
+ /** Get the count of subscribers to the NL.
+ * @return Number of subscribers.
+ */
+ public function subscriberCount($lost = null, $sex = null, $grade = null, $first_promo = null, $last_promo = null)
+ {
+ $cond = new PFC_And(new UFC_NLSubscribed($this->id));
+ if (!is_null($sex)) {
+ $cond->addChild(new UFC_Sex($sex));
+ }
+ if (!is_null($grade)) {
+ $cond->addChild(new UFC_Promo('>=', $grade, $first_promo));
+ $cond->addChild(new UFC_Promo('<=', $grade, $last_promo));
+ }
+ if (!($lost === null)) {
+ if ($lost === true) {
+ $cond->addChild(new PFC_Not(new UFC_HasEmailRedirect()));
+ } else {
+ $cond->addChild(new UFC_HasEmailRedirect());
+ }
+ }
+ $uf = new UserFilter($cond);
+ return $uf->getTotalCount();
+ }
+
+ /** Get the count of subscribers with non valid redirection.
+ */
+ public function lostSubscriberCount($sex = null)
+ {
+ return $this->subscriberCount(true, $sex);
+ }
+
+ /** Get the number of subscribers to the NL whose last received mailing was $last.
+ * @p $last ID of the issue for which subscribers should be counted.
+ * @return Number of subscribers
+ */
+ public function subscriberCountForLast($last)
+ {
+ return XDB::fetchOneCell('SELECT COUNT(uid)
+ FROM newsletter_ins
+ WHERE nlid = {?} AND last = {?}', $this->id, $last);
+ }
+
+ /** Retrieve the list of newsletters a user has subscribed to
+ * @p $user User whose subscriptions should be retrieved (if null, use session user).
+ * @return Array of newsletter IDs
+ */
+ public static function getUserSubscriptions($user = null)
+ {
+ if (is_null($user)) {
+ $user = S::user();