public static function getSubscribers($start = -1, $limit = -1, $emailsearch, &$total, $listId, $mailingId, $blackList = 0, $confirmed = 0, $order = '', $mailId = '', $ownedlists = 0, $setSort = null, $subType = 0, $countUser = false)
{
//added one parameter mailid
$db = JFactory::getDBO();
$my = JFactory::getUser();
$flag = false;
$limitFlag = false;
$mainframe = JFactory::getApplication();
if (version_compare(JVERSION, '1.6.0', '>=')) {
//j16
$gid = JAccess::getGroupsByUser($my->id, false);
$my->gid = $gid[0];
}
$gidAdmins = array(24, 25, 7, 8);
$query = $countUser ? 'SELECT COUNT(M.id) ' : 'SELECT DISTINCT M.*,U.username ';
//for multiple lists
if ($listId == 0) {
$query .= ' FROM `#__jnews_subscribers` AS M LEFT JOIN `#__users` as U ON M.user_id = U.id ';
if (!$mainframe->isAdmin() and ($ownedlists != 0 and !empty($ownedlists)) and !in_array($my->gid, $gidAdmins)) {
$query .= 'LEFT JOIN `#__jnews_listssubscribers` AS N ON N.subscriber_id=M.id WHERE N.list_id IN (' . implode($ownedlists, ',') . ')';
} else {
if (!$mainframe->isAdmin() and ($ownedlists == 0 or empty($ownedlists))) {
//return;
}
}
} else {
$query .= ' FROM `#__jnews_subscribers` AS M LEFT JOIN `#__users` as U ON M.user_id = U.id LEFT JOIN `#__jnews_listssubscribers` AS N';
$query .= ' ON M.id = N.subscriber_id WHERE ';
if (!empty($mailId)) {
$lists = jNews_Mailing::getMailingList($mailId);
$query .= 'N.list_id IN (';
$query .= 'SELECT `list_id` FROM `#__jnews_listmailings` WHERE `mailing_id`=' . $mailId . ')';
} else {
//if the mailing is sent to one or more lists
if (is_array($listId)) {
$lsId = count($listId) > 1 ? implode(',', $listId) : $listId[0];
if (!empty($ownedlists)) {
$lsId = $ownedlists;
}
$query .= 'N.list_id IN (' . $lsId . ')';
} else {
$query .= 'N.list_id =' . $listId;
}
}
$flag = true;
}
//type of subscirber to show used in the listing
switch ($subType) {
case 1:
if (!$flag) {
$query .= ' LEFT JOIN `#__jnews_listssubscribers` AS N ON N.subscriber_id=M.id ';
}
$query .= $flag ? ' AND ' : ' WHERE ';
$query .= ' N.unsubscribe = 0 ';
$flag = true;
break;
case 2:
if (!$flag) {
$query .= ' LEFT JOIN `#__jnews_listssubscribers` AS N ON N.subscriber_id=M.id ';
}
$query .= $flag ? ' AND ' : ' WHERE ';
$query .= ' N.unsubscribe = 1 ';
$flag = true;
break;
case 3:
//waiting confirmation
$query .= $flag ? ' AND ' : ' WHERE ';
$flag = true;
$query .= ' M.confirmed = 0 ';
break;
case 4:
// blocked
$query .= $flag ? ' AND ' : ' WHERE ';
$flag = true;
$query .= ' M.blacklist = 1 ';
break;
case '0':
default:
break;
}
if ($mailingId > 0 and $flag) {
$query .= ' AND N.mailing_id = ' . $mailingId;
}
if ($blackList == 1) {
$query .= $flag ? ' AND ' : ' WHERE ';
if ($confirmed == 1) {
$query .= ' M.blacklist = 0 AND M.confirmed = 1 ';
$flag = true;
} else {
$query .= ' M.blacklist = 0 ';
$flag = true;
}
}
if (!empty($emailsearch)) {
if ($flag) {
$query .= ' AND ( M.email LIKE \'%' . $emailsearch . '%\' OR M.name LIKE \'%' . $emailsearch . '%\' ';
if (is_numeric($emailsearch)) {
$query .= ' OR M.id=' . $emailsearch . ' ';
}
$query .= ' ) ';
} else {
if ($mainframe->isAdmin()) {
$query .= ' WHERE ( M.email LIKE \'%' . $emailsearch . '%\' OR M.name LIKE \'%' . $emailsearch . '%\' ';
} else {
if (($ownedlists != 0 || !empty($ownedlists) && $listId == 0) && $flag) {
$query .= ' AND ( M.email LIKE \'%' . $emailsearch . '%\' OR M.name LIKE \'%' . $emailsearch . '%\' ';
} else {
$query .= ' WHERE ( M.email LIKE \'%' . $emailsearch . '%\' OR M.name LIKE \'%' . $emailsearch . '%\' ';
}
//endelse
}
//endelse
if (is_numeric($emailsearch)) {
$query .= ' OR M.id=' . $emailsearch . ' ';
}
$query .= ' ) ';
}
}
if ($listId != 0 && !$countUser) {
$query .= ' GROUP BY M.id ';
}
// no sorting or limit if we count
if (!$countUser) {
if (!empty($setSort)) {
$s = is_int($setSort->orderValue) ? "{$setSort->orderValue}" : "`{$setSort->orderValue}`";
$query .= "ORDER BY {$s} {$setSort->orderDir}";
} else {
if (!empty($order)) {
$query .= jnews::orderBy($order);
}
}
if ($start != -1 && $limit != -1) {
$query .= ' LIMIT ' . $start . ', ' . $limit;
$limitFlag = true;
}
}
$db->setQuery($query);
if (!$countUser) {
$subscribers = $db->loadObjectList();
} else {
$subscribers = $db->loadResult();
}
return $subscribers;
}
public static function unsubscribe($subscriber, $subscriberId, $cle = '', $mailingId, $action)
{
$Itemid = JRequest::getInt('Itemid');
$db = JFactory::getDBO();
echo '<div class="jNewsUnsubscribe">';
if (!empty($subscriberId) and !empty($cle)) {
$i = 0;
if (md5($subscriber->email) == $cle) {
//$subscriber
frontHTML::formStart(_JNEWS_SUBSCRIPTIONS, 0, 'unsubscribe');
$query = 'SELECT L.`list_name`, L.`list_desc`, L.`hidden` ,L.`acc_level`, L.`id`, LM.`list_id`,L.`list_type` FROM `#__jnews_lists` AS L ' . 'LEFT JOIN `#__jnews_listmailings` AS LM ON L.`id` = LM.`list_id` ' . 'LEFT JOIN `#__jnews_listssubscribers` AS LS ON L.`id` = LS.`list_id` ' . 'WHERE LM.`mailing_id`=' . $mailingId . ' AND LS.`subscriber_id`=' . $subscriberId;
$query .= ' AND LS.`unsubscribe`= 0';
// $query .= ' AND ( L.`hidden`!=0 AND L.`published`!=0 )';//7788744
$query .= ' AND ( L.`published`!=0 )';
$query .= jnews::orderBy('list_idA');
$db->setQuery($query);
$lists = $db->loadObjectList();
if (empty($lists)) {
//invisible or unpublished
jnews::printM('ok', _JNEWS_LISTS_UNSUBMSG);
return false;
}
$checked = 1;
$checkedPrint = $checked != 0 ? 'checked="checked"' : '';
$mainLink = 'option=' . JNEWS_OPTION;
$mainLink = jNews_Tools::completeLink($mainLink, false, false);
echo '<form method="post" action="' . $mainLink . '" onsubmit="submitbutton();return false;" name="mosForm" >' . "\n\r";
echo '<input type="hidden" name="Itemid" value="' . $Itemid . '" />';
if (!empty($lists)) {
foreach ($lists as $list) {
$i++;
$checkedPrint = $checked != 0 ? 'checked="checked"' : '';
echo "\n" . '<input id="wz_3' . $i . '" type="checkbox" class="inputbox" value="1" name="unsubscribed[' . $i . ']" ' . $checkedPrint . ' />';
echo "\n" . '<input type="hidden" name="sub_list_id[' . $i . ']" value="' . $list->id . '" />';
echo "\n" . '<span class="aca_list_name"';
echo '>' . jNews_Tools::toolTip($list->list_desc, $list->list_name, '', '', $list->list_name, '', 1) . '</span>';
echo '<br>';
}
}
echo '<br>';
$link = 'option=' . JNEWS_OPTION . '&act=change&subscriber=' . $subscriberId . '&cle=' . $cle . '&Itemid=' . $Itemid;
$link = jNews_Tools::completeLink($link, false);
?>
<input type="hidden" name="option" value="<?php
echo JNEWS_OPTION;
?>
" />
<input type="hidden" name="act" value="<?php
echo $action;
?>
" />
<input type="hidden" name="task" value="" />
<input type="hidden" name="boxchecked" value="0" />
<input type="hidden" name="subscriber_id" value="<?php
echo $subscriber->id;
?>
" />
<input type="hidden" name="cle" value="<?php
echo md5($subscriber->email);
?>
" />
<div class="subscribe">
<?php
if ($i == 1) {
echo _JNEWS_UNSUBSCRIBE_MESSAGE . '<br /><br />';
} else {
echo _JNEWS_UNSUBSTOLISTS_MESSAGE;
}
?>
</div>
<div id="otherreasons">
<label for="other"><?php
echo _JNEWS_UNSUBSCRIBE_MESSAGE_TEXTAREA;
?>
</label><br>
<textarea rows="5" cols="50" id="other" name="textareamess"></textarea>
</div>
<?php
frontHTML::formEndUnsubscribe($link, $cle, $subscriberId);
//for unsubscribe all
if ($GLOBALS[JNEWS . 'show_unsubscribe_all']) {
$link = 'option=' . JNEWS_OPTION . '&act=unsubscribeall&subscriber=' . $subscriberId . '&cle=' . $cle . '&Itemid=' . $Itemid;
$link = jNews_Tools::completeLink($link, false);
echo '<a href="' . $link . '"> ' . _JNEWS_UNSUBSCRIBE_ALL_OR . '</a>';
echo '<br /><br /><br />';
}
} else {
return false;
}
} else {
return false;
}
echo '</div>';
}
public static function getLists($listId, $listType, $author = null, $order = 'listnameA', $autoAdd = false, $onlyPublished = true, $onlyName = false, $notification = false, $onlyVisible = false, $listsearch = '', $setLimit = null, $setSort = null, $ownedlists = 0, $ownerid = 0)
{
$db = JFactory::getDBO();
$acl = JFactory::getACL();
$my = JFactory::getUser();
$mainframe = JFactory::getApplication();
$gid = !empty($GLOBALS[JNEWS . 'list_creatorfe']) ? $GLOBALS[JNEWS . 'list_creatorfe'] : 0;
$gids = array();
$gids = explode(',', $gid);
if (empty($gids)) {
$gids = $gid;
}
if ($onlyName) {
$query = 'SELECT `id` AS id, `list_name` AS list_name, `list_desc` AS list_desc, `list_type` AS list_type, `hidden` FROM `#__jnews_lists` ';
} else {
$query = 'SELECT * FROM `#__jnews_lists` ';
}
$where = array();
if ($listId > 0) {
$where[] = ' `id`=' . intval($listId);
}
if ($listType > 0) {
// $where[] = ' `list_type`='.intval($listType);
if (is_array($listType)) {
$where[] = ' `list_type` IN (' . jnews::implode(',', $listType) . ') ';
} else {
$where[] = ' `list_type`=' . intval($listType);
}
}
if ($autoAdd) {
$where[] = ' `auto_add`=1 ';
}
if ($onlyPublished == true) {
$where[] = ' `published`=1 ';
}
if ($onlyVisible == true) {
$where[] = ' `hidden`=1 ';
}
if (!empty($listsearch)) {
if (is_numeric($listsearch)) {
$where[] = ' `id`=' . $listsearch;
} else {
$where[] = ' (list_name LIKE \'%' . $listsearch . '%\' OR sendername LIKE \'%' . $listsearch . '%\')';
}
}
if (class_exists('jNews_Pro') && isset($author)) {
static $accIds = array();
if (!isset($accIds[$my->id])) {
if ($mainframe->isAdmin()) {
if (version_compare(JVERSION, '1.6.0', '<')) {
$acl = JFactory::getACL();
$usergroups = $acl->get_group_children_tree(null, 'USERS', false);
} else {
$db->setQuery('SELECT a.*, a.title as text, a.id as value FROM #__usergroups AS a ORDER BY a.lft ASC');
$usergroups = $db->loadObjectList();
}
foreach ($usergroups as $usergroup) {
$allgroups[] = $usergroup->value;
}
$accIds[$my->id] = implode(',', $allgroups);
$subaccess = $accIds[$my->id];
//$where[] = " (`acc_id` IN ($subaccess) OR `acc_id` LIKE '%all%' )"; //we should not have this where clause since this is the backend 8855445
} else {
if (version_compare(JVERSION, '1.6.0', '>=')) {
//j16
$my->gid = JAccess::getGroupsByUser($my->id, false);
$accIds[$my->id] = $my->gid[0];
} else {
$accIds[$my->id] = $my->gid;
}
$subaccess = $accIds[$my->id];
$where[] = " (`acc_id` LIKE '%{$subaccess},%' OR `acc_id` LIKE '%all%' )";
}
}
}
if (!empty($my->id)) {
$ownedlists = jNews_Lists::getOwnedlists($my->id);
}
$gidAdmins = array(24, 25, 7, 8);
if (version_compare(JVERSION, '1.6.0', '>=')) {
//j16
$gid = JAccess::getGroupsByUser($my->id, false);
$my->gid = $gid[0];
}
if (JRequest::getVar('act', '', '', 'WORD') != 'show') {
//we don't filter the lists in the "My Subscriptions"
//filter lists by ownerid
if (!empty($my->id) && !$mainframe->isAdmin() && !in_array($my->gid, $gidAdmins) && !empty($ownedlists)) {
$allUserAccessA = jNews_Lists::getUserGroups($my);
//$my->getAuthorisedGroups();
// $where[] = "( `acc_level` LIKE '%" . implode( ",%' OR `acc_level` LIKE '%", $allUserAccessA ) . ",%' OR `owner`=".$my->id.")";
$where[] = "( `acc_level` LIKE '%" . implode(",%' OR `acc_level` LIKE '%", $allUserAccessA) . ",%' OR `acc_level` LIKE '%" . implode("%' OR `acc_level` LIKE '%", $allUserAccessA) . "%' OR `owner`=" . $my->id . ")";
}
//filter lists by acclevel
if (!empty($my->id) && !$mainframe->isAdmin() && !in_array($my->gid, $gidAdmins) && empty($ownedlists)) {
$allUserAccessA = jNews_Lists::getUserGroups($my);
//$my->getAuthorisedGroups();
//$where[] = "( `acc_level` LIKE '%all%' OR `acc_level` LIKE '%" . implode( ",%' OR `acc_level` LIKE '%", $allUserAccessA ) . ",%' )";
}
if (!$mainframe->isAdmin() && (!empty($ownedlists) or $ownedlists != 0) && !empty($my->id) && !in_array($my->gid, $gidAdmins) && in_array($my->gid, $gids)) {
$allUserAccessA = jNews_Lists::getUserGroups($my);
//$my->getAuthorisedGroups();
// $where[] = " ( `acc_level` LIKE '%" . implode( ",%' OR `acc_level` LIKE '%", $allUserAccessA ) . ",%' )";
$where[] = " ( `acc_level` LIKE '%" . implode(",%' OR `acc_level` LIKE '%", $allUserAccessA) . ",%' OR `acc_level` LIKE '%" . implode("%' OR `acc_level` LIKE '%", $allUserAccessA) . "%' )";
}
}
if (JRequest::getVar('act', '', '', 'WORD') == 'show') {
//filter lists by acclevel
if (!empty($my->id) && !$mainframe->isAdmin() && !in_array($my->gid, $gidAdmins)) {
$where[] = "(`acc_id` LIKE '%{$my->gid},%' OR `acc_id` LIKE '%all%')";
}
}
$wheretag = count($where) ? ' WHERE ' . implode(' AND ', $where) : '';
$query .= $wheretag;
//sorting of columns
if (!empty($setSort)) {
$query .= " ORDER BY `{$setSort->orderValue}` {$setSort->orderDir}";
} else {
$query .= class_exists('jnews') ? jnews::orderBy($order) : '';
}
//var_dump(($setSort->orderValue)) ; die;
if (!empty($setLimit)) {
$limitStart = !empty($setLimit->start) ? $setLimit->start : 0;
//$setLimit->start;
$limitEnd = !empty($setLimit->end) ? $setLimit->end : '-1';
$db->setQuery($query, $limitStart, $limitEnd);
} else {
$db->setQuery($query);
}
$lists = $db->loadObjectList();
if (!empty($lists)) {
foreach ($lists as $key => $list) {
$lists[$key]->list_name = stripslashes($lists[$key]->list_name);
$lists[$key]->list_desc = stripslashes($lists[$key]->list_desc);
}
}
return $lists;
}
public static function getMailings($listId, $listType, $start = -1, $limit = -1, $emailsearch = '', $order = '', $showOnlyPublished = true, $viewArchive = false, $setSort = null)
{
$my = JFactory::getUser();
$db = JFactory::getDBO();
$where = array();
$flag = false;
$sortList = false;
$query = 'SELECT * FROM `#__jnews_mailings`';
if ($listType > 0) {
if (is_array($listType)) {
$where[] = ' `mailing_type` IN (' . jnews::implode(',', $listType) . ') ';
} else {
$where[] = ' `mailing_type` = ' . $listType . ' ';
}
}
if ($listId > 0) {
// will filter the mailings shown based on the listid passed on the url
$query2 = 'SELECT B.`mailing_id` FROM `#__jnews_listmailings` AS B WHERE B.`list_id` = ' . $listId;
$db->setQuery($query2);
$result = $db->loadObjectList();
$resArr = "";
foreach ($result as $rstl) {
$resArr[] = $rstl->mailing_id;
}
if (!empty($resArr)) {
$results = jnews::implode(',', $resArr);
if (!empty($results)) {
$where[] = ' `id` IN ( ' . $results . ' )';
}
$sortList = true;
} else {
return array();
}
}
if ($showOnlyPublished) {
$where[] = ' `published` =1 ';
$where[] = ' `visible`=1 ';
} else {
$where[] = ' `published`<>-1 ';
}
if (class_exists('jNews_Pro') && $sortList) {
} elseif (!jnews::checkPermissions('admin') && !$viewArchive) {
$where[] = ' `author_id` = ' . $my->id;
}
//added this if statement to filter only all the published and visible mailings in the archive in the fe
if ($viewArchive && !$showOnlyPublished) {
// && $my->id <=0
$where[] = ' `published` =1 ';
$where[] = ' `visible`=1 ';
}
if (!empty($emailsearch)) {
if (is_numeric($emailsearch)) {
$where[] = ' `id`=' . $emailsearch;
} else {
$where[] = ' (subject LIKE \'%' . $emailsearch . '%\' OR fromname LIKE \'%' . $emailsearch . '%\') ';
}
}
$query .= count($where) ? " WHERE " . implode(' AND ', $where) : "";
if (!empty($setSort->orderValue) && !empty($setSort->orderDir)) {
$query .= " ORDER BY `{$setSort->orderValue}` {$setSort->orderDir}";
} else {
if (empty($order)) {
$order = 'idD';
}
$query .= jnews::orderBy($order);
}
if ($start >= 0 && $limit > 0) {
$db->setQuery($query, $start, $limit);
} else {
$db->setQuery($query);
}
$mailing = $db->loadObjectList();
if (!empty($mailing)) {
foreach ($mailing as $key => $mail) {
$mailing[$key]->htmlcontent = stripslashes($mailing[$key]->htmlcontent);
$mailing[$key]->subject = stripslashes($mailing[$key]->subject);
$mailing[$key]->attachments = stripslashes($mailing[$key]->attachments);
$mailing[$key]->images = stripslashes($mailing[$key]->images);
$mailing[$key]->textonly = stripslashes($mailing[$key]->textonly);
$mailing[$key]->send_date = stripslashes($mailing[$key]->send_date);
}
}
return $mailing;
}
function getSubscriberLists($userId)
{
$db = JFactory::getDBO();
if ($userId > 0) {
$query = 'SELECT * FROM `#__jnews_queue` WHERE `subscriber_id`=' . $userId . " AND type != 99";
$query .= jnews::orderBy('list_idA');
$db->setQuery($query);
$queue = $db->loadObjectList();
return $queue;
} else {
return '';
}
}
public static function getSubscriberLists($userId)
{
$db = JFactory::getDBO();
if ($userId > 0) {
$query = 'SELECT LS.*, L.list_name, L.list_desc, L.acc_level, L.id, L.hidden, L.published,L.list_type FROM `#__jnews_lists` AS L LEFT JOIN `#__jnews_listssubscribers` AS LS' . ' ON L.id = LS.list_id WHERE LS.subscriber_id=' . $userId;
$query .= ' AND LS.`unsubscribe`= 0';
$query .= jnews::orderBy('list_idA');
$db->setQuery($query);
$queue = $db->loadObjectList();
return $queue;
} else {
return '';
}
}
