function save($configObject)
{
$query = 'REPLACE INTO ' . acymailing_table('config') . ' (namekey,value) VALUES ';
$params = array();
$i = 0;
foreach ($configObject as $namekey => $value) {
$i++;
if ($i > 100) {
$query .= implode(',', $params);
$this->database->setQuery($query);
if (!$this->database->query()) {
return false;
}
$i = 0;
$query = 'REPLACE INTO ' . acymailing_table('config') . ' (namekey,value) VALUES ';
$params = array();
}
if (empty($this->values[$namekey])) {
$this->values[$namekey] = new stdClass();
}
$this->values[$namekey]->value = $value;
$params[] = '(' . $this->database->Quote(strip_tags($namekey)) . ',' . $this->database->Quote(strip_tags($value)) . ')';
}
if (empty($params)) {
return true;
}
$query .= implode(',', $params);
$this->database->setQuery($query);
return $this->database->query();
}
function get($urlid, $default = null)
{
$column = is_numeric($urlid) ? 'urlid' : 'url';
$query = 'SELECT * FROM ' . acymailing_table('url') . ' WHERE ' . $column . ' = ' . $this->database->Quote($urlid) . ' LIMIT 1';
$this->database->setQuery($query);
return $this->database->loadObject();
}
function saveStats()
{
$subid = JRequest::getInt('subid');
$mailid = JRequest::getInt('mailid');
if (empty($subid) or empty($mailid)) {
return false;
}
$db = JFactory::getDBO();
$db->setQuery('SELECT open FROM ' . acymailing_table('userstats') . ' WHERE mailid = ' . $mailid . ' AND subid = ' . $subid . ' LIMIT 1');
$actual = $db->loadObject();
if (empty($actual)) {
return false;
}
$open = 0;
if (empty($actual->open)) {
$open = 1;
$unique = ',openunique = openunique +1';
} elseif ($this->countReturn) {
$open = $actual->open + 1;
$unique = '';
}
if (empty($open)) {
return true;
}
$db->setQuery('UPDATE ' . acymailing_table('userstats') . ' SET open = ' . $open . ', opendate = ' . time() . ' WHERE mailid = ' . $mailid . ' AND subid = ' . $subid . ' LIMIT 1');
$db->query();
$db->setQuery('UPDATE ' . acymailing_table('stats') . ' SET opentotal = opentotal +1 ' . $unique . ' WHERE mailid = ' . $mailid . ' LIMIT 1');
$db->query();
if (!empty($subid)) {
$filterClass = acymailing_get('class.filter');
$filterClass->subid = $subid;
$filterClass->trigger('opennews');
}
return true;
}
function acymailingtagsubscriber_show()
{
$descriptions['subid'] = JText::_('SUBSCRIBER_ID');
$descriptions['email'] = JText::_('SUBSCRIBER_EMAIL');
$descriptions['name'] = JText::_('SUBSCRIBER_NAME');
$descriptions['userid'] = JText::_('SUBSCRIBER_USERID');
$descriptions['ip'] = JText::_('SUBSCRIBER_IP');
$descriptions['created'] = JText::_('SUBSCRIBER_CREATED');
$text = '<table class="adminlist" cellpadding="1">';
$db =& JFactory::getDBO();
$tableInfos = $db->getTableFields(acymailing_table('subscriber'));
$others = array();
$others['{subtag:name|part:first|ucfirst}'] = array('name' => JText::_('SUBSCRIBER_FIRSTPART'), 'desc' => JText::_('SUBSCRIBER_FIRSTPART') . ' ' . JText::_('SUBSCRIBER_FIRSTPART_DESC'));
$others['{subtag:name|part:last|ucfirst}'] = array('name' => JText::_('SUBSCRIBER_LASTPART'), 'desc' => JText::_('SUBSCRIBER_LASTPART') . ' ' . JText::_('SUBSCRIBER_LASTPART_DESC'));
$k = 0;
$fields = reset($tableInfos);
foreach ($fields as $fieldname => $oneField) {
if (!isset($descriptions[$fieldname]) and $oneField == 'tinyint') {
continue;
}
$type = '';
if ($fieldname == 'created') {
$type = '|type:time';
}
$text .= '<tr style="cursor:pointer" class="row' . $k . '" onclick="setTag(\'{subtag:' . $fieldname . $type . '}\');insertTag();" ><td class="acytdcheckbox"></td><td>' . $fieldname . '</td><td>' . @$descriptions[$fieldname] . '</td></tr>';
$k = 1 - $k;
}
foreach ($others as $tagname => $tag) {
$text .= '<tr style="cursor:pointer" class="row' . $k . '" onclick="setTag(\'' . $tagname . '\');insertTag();" ><td class="acytdcheckbox"></td><td>' . $tag['name'] . '</td><td>' . $tag['desc'] . '</td></tr>';
$k = 1 - $k;
}
$text .= '</table>';
echo $text;
}
function insert($subid, $action, $data = array(), $mailid = 0)
{
$user =& JFactory::getUser();
if (!empty($user->id)) {
$data[] = 'EXECUTED_BY::' . $user->id . ' ( ' . $user->username . ' )';
}
$history = new stdClass();
$history->subid = intval($subid);
$history->action = strip_tags($action);
$history->data = implode("\n", $data);
if (strlen($history->data) > 100000) {
$history->data = substr($history->data, 0, 10000);
}
$history->date = time();
$history->mailid = $mailid;
$userHelper = acymailing_get('helper.user');
$history->ip = $userHelper->getIP();
if (!empty($_SERVER)) {
$source = array();
$vars = array('HTTP_REFERER', 'HTTP_USER_AGENT', 'HTTP_HOST', 'SERVER_ADDR', 'REMOTE_ADDR', 'REQUEST_URI', 'QUERY_STRING');
foreach ($vars as $oneVar) {
if (!empty($_SERVER[$oneVar])) {
$source[] = $oneVar . '::' . strip_tags($_SERVER[$oneVar]);
}
}
$history->source = implode("\n", $source);
}
return $this->database->insertObject(acymailing_table('history'), $history);
}
function get($ruleid, $default = null)
{
$query = 'SELECT * FROM ' . acymailing_table('rules') . ' WHERE `ruleid` = ' . intval($ruleid) . ' LIMIT 1';
$this->database->setQuery($query);
$rule = $this->database->loadObject();
return $this->_prepareRule($rule);
}
function addClick($urlid,$mailid,$subid){
$mailid = intval($mailid);
$urlid = intval($urlid);
$subid = intval($subid);
if(empty($mailid) OR empty($urlid) OR empty($subid)) return true;
$statsClass = acymailing_get('class.stats');
$statsClass->countReturn = false;
$statsClass->mailid = $mailid;
$statsClass->subid = $subid;
if(!$statsClass->saveStats()) return true;
$date = time();
$ipClass = acymailing_get('helper.user');
$ip = $ipClass->getIP();
$query = 'INSERT IGNORE INTO '.acymailing_table('urlclick').' (urlid,mailid,subid,date,click,ip) VALUES ('.$urlid.','.$mailid.','.$subid.','.$date.',1,'.$this->database->Quote($ip).')';
$this->database->setQuery($query);
if(!$this->database->query()){
acymailing_display($this->database->getErrorMsg(),'error');
exit;
}
if(!$this->database->getAffectedRows()){
$query = 'UPDATE '.acymailing_table('urlclick').' SET click = click +1,`date` = '.$date.' WHERE mailid = '.$mailid.' AND urlid = '.$urlid.' AND subid = '.$subid.' LIMIT 1';
$this->database->setQuery($query);
$this->database->query();
}
$query = 'SELECT SUM(click) FROM '.acymailing_table('urlclick').' WHERE mailid = '.$mailid.' AND subid = '.$subid;
$this->database->setQuery($query);
$totalUserClick = $this->database->loadResult();
$query = 'UPDATE '.acymailing_table('stats').' SET clicktotal = clicktotal + 1 ';
if($totalUserClick <= 1){
$query .= ' , clickunique = clickunique + 1';
}
$query .= ' WHERE mailid = '.$mailid.' LIMIT 1';
$this->database->setQuery($query);
$this->database->query();
$this->database->setQuery('UPDATE #__acymailing_subscriber SET lastclick_date = '.time().' WHERE subid = '.$subid);
$this->database->query();
$filterClass = acymailing_get('class.filter');
$filterClass->subid = $subid;
$filterClass->trigger('clickurl');
$classGeoloc = acymailing_get('class.geolocation');
$classGeoloc->saveGeolocation('clic', $subid);
JPluginHelper::importPlugin('acymailing');
$dispatcher = JDispatcher::getInstance();
$dispatcher->trigger('onAcyClickLink',array($subid,$mailid,$urlid));
return true;
}
function unsubscribe($subid, $listids)
{
$app = JFactory::getApplication();
if (acymailing_level(3)) {
$campaignClass = acymailing_get('helper.campaign');
$campaignClass->stop($subid, $listids);
}
$config = acymailing_config();
static $alreadySent = false;
if ($this->sendNotif and !$alreadySent and $config->get('notification_unsub') and !$app->isAdmin()) {
$alreadySent = true;
$mailer = acymailing_get('helper.mailer');
$mailer->report = false;
$mailer->autoAddUser = true;
$mailer->checkConfirmField = false;
$userClass = acymailing_get('class.subscriber');
$subscriber = $userClass->get($subid);
$ipClass = acymailing_get('helper.user');
$mailer->addParam('survey', $this->survey);
$listSubClass = acymailing_get('class.listsub');
$mailer->addParam('user:subscription', $listSubClass->getSubscriptionString($subscriber->subid));
$mailer->addParam('user:subscriptiondates', $listSubClass->getSubscriptionString($subscriber->subid, true));
$mailer->addParamInfo();
$subscriber->ip = $ipClass->getIP();
foreach ($subscriber as $fieldname => $value) {
$mailer->addParam('user:'******',', $config->get('notification_unsub'));
foreach ($allUsers as $oneUser) {
$mailer->sendOne('notification_unsub', $oneUser);
}
}
$db = JFactory::getDBO();
if ($this->forceConf || ($this->sendConf and !$app->isAdmin())) {
$db->setQuery('SELECT DISTINCT `unsubmailid` FROM ' . acymailing_table('list') . ' WHERE `listid` IN (' . implode(',', $listids) . ') AND `published` = 1 AND `unsubmailid` > 0');
$messages = acymailing_loadResultArray($db);
if (!empty($messages)) {
$config = acymailing_config();
$mailHelper = acymailing_get('helper.mailer');
$mailHelper->report = $config->get('unsub_message', true);
$mailHelper->checkAccept = false;
foreach ($messages as $mailid) {
$mailHelper->trackEmail = true;
$mailHelper->sendOne($mailid, $subid);
}
}
}
//end only frontend
$db->setQuery('DELETE FROM ' . acymailing_table('queue') . ' WHERE `subid` = ' . (int) $subid . ' AND `mailid` IN (SELECT `mailid` FROM ' . acymailing_table('listmail') . ' WHERE `listid` IN (' . implode(',', $listids) . '))');
$db->query();
JPluginHelper::importPlugin('acymailing');
$dispatcher = JDispatcher::getInstance();
$resultsTrigger = $dispatcher->trigger('onAcyUnsubscribe', array($subid, $listids));
}
function detailstatsbounceType()
{
$query = 'SELECT DISTINCT bouncerule FROM ' . acymailing_table('userstats') . ' WHERE bouncerule IS NOT NULL';
$db = JFactory::getDBO();
$db->setQuery($query);
$bouncerules = $db->loadObjectList();
$this->values = array();
$this->values[] = JHTML::_('select.option', 0, JText::_('ALL_RULES'));
foreach ($bouncerules as $oneRule) {
$this->values[] = JHTML::_('select.option', $oneRule->bouncerule, $oneRule->bouncerule);
}
}
function queuemailType()
{
$query = 'SELECT count(distinct a.subid) as totalsub, b.subject, a.mailid FROM ' . acymailing_table('queue') . ' as a';
$query .= ' LEFT JOIN ' . acymailing_table('mail') . ' as b on a.mailid = b.mailid GROUP BY a.mailid ORDER BY b.subject ASC';
$db =& JFactory::getDBO();
$db->setQuery($query);
$emails = $db->loadObjectList();
$this->values = array();
$this->values[] = JHTML::_('select.option', '0', JText::_('ALL_EMAILS'));
foreach ($emails as $oneMail) {
$this->values[] = JHTML::_('select.option', $oneMail->mailid, $oneMail->subject . ' ( ' . $oneMail->totalsub . ' )');
}
}
function detailstatsmailType()
{
$query = 'SELECT b.subject, a.mailid FROM ' . acymailing_table('stats') . ' as a';
$query .= ' JOIN ' . acymailing_table('mail') . ' as b on a.mailid = b.mailid ORDER BY a.senddate DESC LIMIT 200';
$db = JFactory::getDBO();
$db->setQuery($query);
$emails = $db->loadObjectList();
$this->values = array();
$this->values[] = JHTML::_('select.option', '0', JText::_('ALL_EMAILS'));
foreach ($emails as $oneMail) {
$this->values[] = JHTML::_('select.option', $oneMail->mailid, $oneMail->subject);
}
}
function welcomeType()
{
$query = 'SELECT `subject`, `mailid` FROM ' . acymailing_table('mail') . ' WHERE `type`= \'welcome\'';
$db = JFactory::getDBO();
$db->setQuery($query);
$messages = $db->loadObjectList();
$this->values = array();
$this->values[] = JHTML::_('select.option', '0', JText::_('NO_WELCOME_MESSAGE'));
$this->values[] = JHTML::_('select.option', '-1', JText::_('LATEST_NEWSLETTER'));
foreach ($messages as $oneMessage) {
$this->values[] = JHTML::_('select.option', $oneMessage->mailid, '[' . JText::_('ACY_ID') . ' ' . $oneMessage->mailid . '] ' . $oneMessage->subject);
}
}
function urlmailType(){
$query = 'SELECT b.subject,b.mailid,count(distinct a.urlid) as totalmail FROM '.acymailing_table('urlclick').' as a';
$query .= ' JOIN '.acymailing_table('mail').' as b ON a.mailid = b.mailid';
$query .= ' GROUP BY a.mailid ORDER BY a.mailid DESC';
$db = JFactory::getDBO();
$db->setQuery($query);
$mails = $db->loadObjectList();
$this->values = array();
$this->values[] = JHTML::_('select.option', '0', JText::_('ALL_EMAILS') );
foreach($mails as $oneMail){
$this->values[] = JHTML::_('select.option', $oneMail->mailid, $oneMail->subject.' ( '.$oneMail->totalmail.' )' );
}
}
function listcreatorType()
{
$query = 'SELECT b.name,a.userid,count(distinct a.listid) as total FROM ' . acymailing_table('list') . ' as a ';
$query .= ' LEFT JOIN ' . acymailing_table('users', false) . ' as b on a.userid = b.id WHERE a.type = \'list\' GROUP BY a.userid ORDER BY b.name ASC';
$db =& JFactory::getDBO();
$db->setQuery($query);
$creators = $db->loadObjectList();
$this->values = array();
$this->values[] = JHTML::_('select.option', '0', JText::_('ALL_CREATORS'));
foreach ($creators as $oneCreator) {
if (!empty($oneCreator->userid)) {
$this->values[] = JHTML::_('select.option', $oneCreator->userid, $oneCreator->name . ' ( ' . $oneCreator->total . ' )');
}
}
}
function unsubType()
{
$query = 'SELECT `subject`, `mailid` FROM ' . acymailing_table('mail') . ' WHERE `type`= \'unsub\'';
$db = JFactory::getDBO();
$db->setQuery($query);
$messages = $db->loadObjectList();
$this->values = array();
$this->values[] = JHTML::_('select.option', '0', JText::_('NO_UNSUB_MESSAGE'));
foreach ($messages as $oneMessage) {
$this->values[] = JHTML::_('select.option', $oneMessage->mailid, '[' . JText::_('ACY_ID') . ' ' . $oneMessage->mailid . '] ' . $oneMessage->subject);
}
$js = "function changeMessage(idField,value){\r\n\t\t\tlinkEdit = idField+'_edit';\r\n\t\t\tif(value>0){\r\n\t\t\t\twindow.document.getElementById(linkEdit).href = 'index.php?option=com_acymailing&tmpl=component&ctrl=email&task=edit&mailid='+value;\r\n\t\t\t\twindow.document.getElementById(linkEdit).style.display = 'inline';\r\n\t\t\t}else{\r\n\t\t\t\twindow.document.getElementById(linkEdit).style.display = 'none';\r\n\t\t\t}\r\n\t\t}";
$doc = JFactory::getDocument();
$doc->addScriptDeclaration($js);
}
function saveStats()
{
$subid = empty($this->subid) ? JRequest::getInt('subid') : $this->subid;
$mailid = empty($this->mailid) ? JRequest::getInt('mailid') : $this->mailid;
if (empty($subid) or empty($mailid)) {
return false;
}
$db = JFactory::getDBO();
$db->setQuery('SELECT open FROM ' . acymailing_table('userstats') . ' WHERE `mailid` = ' . $mailid . ' AND `subid` = ' . intval($subid) . ' LIMIT 1');
$actual = $db->loadObject();
if (empty($actual)) {
return false;
}
$userHelper = acymailing_get('helper.user');
$this->database->setQuery('UPDATE #__acymailing_subscriber SET `lastopen_date` = ' . time() . ', `lastopen_ip` = ' . $this->database->Quote($userHelper->getIP()) . ' WHERE `subid` = ' . intval($subid));
$this->database->query();
$open = 0;
if (empty($actual->open)) {
$open = 1;
$unique = ',openunique = openunique +1';
} elseif ($this->countReturn) {
$open = $actual->open + 1;
$unique = '';
}
if (empty($open)) {
return true;
}
$ipClass = acymailing_get('helper.user');
$ip = $ipClass->getIP();
$db->setQuery('UPDATE ' . acymailing_table('userstats') . ' SET open = ' . $open . ', opendate = ' . time() . ', `ip`= ' . $db->Quote($ip) . ' WHERE mailid = ' . $mailid . ' AND subid = ' . $subid . ' LIMIT 1');
if (!$db->query()) {
acymailing_display($db->getErrorMsg(), 'error');
exit;
}
$db->setQuery('UPDATE ' . acymailing_table('stats') . ' SET opentotal = opentotal +1 ' . $unique . ' WHERE mailid = ' . $mailid . ' LIMIT 1');
$db->query();
if (!empty($subid)) {
$filterClass = acymailing_get('class.filter');
$filterClass->subid = $subid;
$filterClass->trigger('opennews');
}
$classGeoloc = acymailing_get('class.geolocation');
$classGeoloc->saveGeolocation('open', $subid);
JPluginHelper::importPlugin('acymailing');
$dispatcher = JDispatcher::getInstance();
$dispatcher->trigger('onAcyOpenMail', array($subid, $mailid));
return true;
}
function display($map, $value)
{
$query = 'SELECT DISTINCT bouncerule FROM ' . acymailing_table('userstats') . ' WHERE bouncerule IS NOT NULL';
$db = JFactory::getDBO();
$db->setQuery($query);
$bouncerules = $db->loadObjectList();
if (empty($bouncerules)) {
return '';
}
$valueBounce = array();
$valueBounce[] = JHTML::_('select.option', 0, JText::_('ALL_RULES'));
foreach ($bouncerules as $oneRule) {
$valueBounce[] = JHTML::_('select.option', $oneRule->bouncerule, $oneRule->bouncerule);
}
return JHTML::_('select.genericlist', $valueBounce, $map, 'class="inputbox" size="1" onchange="document.adminForm.submit( );"', 'value', 'text', $value);
}
function export()
{
$listClass = acymailing_get('class.list');
$db =& JFactory::getDBO();
$fields = reset($db->getTableFields(acymailing_table('subscriber')));
$config = acymailing_config();
$selectedFields = explode(',', $config->get('export_fields', 'email,name'));
acymailing_setTitle(JText::_('ACY_EXPORT'), 'acyexport', 'data&task=export');
$bar =& JToolBar::getInstance('toolbar');
JToolBarHelper::custom('doexport', 'acyexport', '', JText::_('ACY_EXPORT'), false);
$bar->appendButton('Link', 'cancel', JText::_('ACY_CANCEL'), acymailing_completeLink('subscriber'));
JToolBarHelper::divider();
$bar->appendButton('Pophelp', 'data-export');
$charsetType = acymailing_get('type.charset');
$this->assignRef('charset', $charsetType);
$lists = $listClass->getLists();
$this->assignRef('lists', $lists);
$this->assignRef('fields', $fields);
$this->assignRef('selectedfields', $selectedFields);
$this->assignRef('config', $config);
$whereSubscribers = '';
if (JRequest::getInt('sessionvalues') and !empty($_SESSION['acymailing']['exportusers'])) {
$i = 1;
$subids = array();
foreach ($_SESSION['acymailing']['exportusers'] as $subid) {
$subids[] = (int) $subid;
$i++;
if ($i > 10) {
break;
}
}
$whereSubscribers = implode(',', $subids);
}
if (JRequest::getInt('sessionquery')) {
$currentSession =& JFactory::getSession();
$exportQuery = $currentSession->get('acyexportquery');
if (!empty($exportQuery)) {
$whereSubscribers = $exportQuery;
}
}
if (!empty($whereSubscribers)) {
$db->setQuery('SELECT `name`,`email` FROM `#__acymailing_subscriber` WHERE `subid` IN (' . $whereSubscribers . ') LIMIT 10');
$users = $db->loadObjectList();
$this->assignRef('users', $users);
}
}
static function add_sub($list_id, $user_id)
{
require_once JPATH_ADMINISTRATOR . '/' . 'components' . '/' . 'com_acymailing' . '/' . 'helpers' . '/' . 'helper.php';
$db = JFactory::getDBO();
$sub_id = JoomdleHelperAcymailing::get_sub_id($user_id);
// Should not happen after syncing joomla users at acymailing install
if (!$sub_id) {
return 0;
}
$listsub = new JObject();
$listsub->listid = $list_id;
$listsub->subid = $sub_id;
$listsub->subdate = time();
$listsub->status = 1;
$status = $db->insertObject(acymailing_table('listsub'), $listsub);
return $status;
}
function editorType()
{
if (!ACYMAILING_J16) {
$query = 'SELECT DISTINCT element,name FROM ' . acymailing_table('plugins', false) . ' WHERE folder=\'editors\' AND published=1 ORDER BY ordering ASC, name ASC';
} else {
$query = 'SELECT DISTINCT element,name FROM ' . acymailing_table('extensions', false) . ' WHERE folder=\'editors\' AND enabled=1 AND type=\'plugin\' ORDER BY ordering ASC, name ASC';
}
$db = JFactory::getDBO();
$db->setQuery($query);
$joomEditors = $db->loadObjectList();
$this->values = array();
$this->values[] = JHTML::_('select.option', '0', JText::_('ACY_DEFAULT'));
if (!empty($joomEditors)) {
foreach ($joomEditors as $myEditor) {
$this->values[] = JHTML::_('select.option', $myEditor->element, $myEditor->name);
}
}
}
function load()
{
$query = 'SELECT b.name,a.listid,count(distinct a.mailid) as totalmail FROM ' . acymailing_table('mail') . ' as c';
$query .= ' LEFT JOIN ' . acymailing_table('listmail') . ' as a ON a.mailid = c.mailid';
$query .= ' LEFT JOIN ' . acymailing_table('list') . ' as b on a.listid = b.listid';
$query .= ' WHERE c.type = \'' . $this->type . '\' GROUP BY a.listid ORDER BY b.ordering ASC';
$db =& JFactory::getDBO();
$db->setQuery($query);
$lists = $db->loadObjectList();
$this->values = array();
$this->values[] = JHTML::_('select.option', '0', JText::_('ALL_LISTS'));
foreach ($lists as $onelist) {
if (empty($onelist->listid)) {
continue;
}
$this->values[] = JHTML::_('select.option', $onelist->listid, $onelist->name . ' ( ' . $onelist->totalmail . ' )');
}
}
function save($configObject)
{
$query = 'REPLACE INTO ' . acymailing_table('config') . ' (namekey,value) VALUES ';
$params = array();
$i = 0;
foreach ($configObject as $namekey => $value) {
if (strpos($namekey, 'password') !== false && !empty($value) && trim($value, '*') == '') {
continue;
}
$i++;
if (is_array($value)) {
$value = implode(',', $value);
}
if ($i > 100) {
$query .= implode(',', $params);
$this->database->setQuery($query);
if (!$this->database->query()) {
return false;
}
$i = 0;
$query = 'REPLACE INTO ' . acymailing_table('config') . ' (namekey,value) VALUES ';
$params = array();
}
if (empty($this->values[$namekey])) {
$this->values[$namekey] = new stdClass();
}
$this->values[$namekey]->value = $value;
$params[] = '(' . $this->database->Quote(strip_tags($namekey)) . ',' . $this->database->Quote(strip_tags($value)) . ')';
}
if (empty($params)) {
return true;
}
$query .= implode(',', $params);
$this->database->setQuery($query);
try {
$status = $this->database->query();
} catch (Exception $e) {
$status = false;
}
if (!$status) {
acymailing_display(isset($e) ? $e->getMessage() : substr(strip_tags($this->database->getErrorMsg()), 0, 200) . '...', 'error');
}
return $status;
}
function send()
{
if (!$this->isAllowed('newsletters', 'send')) {
return;
}
JRequest::checkToken() or die('Invalid Token');
JRequest::setVar('tmpl', 'component');
$mailid = acymailing_getCID('mailid');
if (empty($mailid)) {
exit;
}
$user = JFactory::getUser();
$time = time();
$queueClass = acymailing_get('class.queue');
$queueClass->onlynew = JRequest::getInt('onlynew');
$queueClass->mindelay = JRequest::getInt('mindelay');
$totalSub = $queueClass->queue($mailid, $time);
if (empty($totalSub)) {
acymailing_display(JText::_('NO_RECEIVER'), 'warning');
return;
}
$mailObject = new stdClass();
$mailObject->senddate = $time;
$mailObject->published = 1;
$mailObject->mailid = $mailid;
$mailObject->sentby = $user->id;
$db = JFactory::getDBO();
$db->updateObject(acymailing_table('mail'), $mailObject, 'mailid');
$config =& acymailing_config();
$queueType = $config->get('queue_type');
if ($queueType == 'onlyauto') {
$messages = array();
$messages[] = JText::sprintf('ADDED_QUEUE', $totalSub);
$messages[] = JText::_('AUTOSEND_CONFIRMATION');
acymailing_display($messages, 'success');
return;
} else {
JRequest::setVar('totalsend', $totalSub);
$app = JFactory::getApplication();
$app->redirect(acymailing_completeLink('send&task=continuesend&mailid=' . $mailid . '&totalsend=' . $totalSub, true, true));
exit;
}
}
function urlType(){
$selectedMail = JRequest::getInt('filter_mail');
if(!empty($selectedMail)){
$query = 'SELECT DISTINCT a.name,a.urlid FROM '.acymailing_table('urlclick').' as b JOIN '.acymailing_table('url').' as a on a.urlid = b.urlid WHERE b.mailid = '.$selectedMail.' ORDER BY a.name LIMIT 300';
}else{
$query = 'SELECT DISTINCT a.name,a.urlid FROM '.acymailing_table('urlclick').' as b JOIN '.acymailing_table('url').' as a on a.urlid = b.urlid ORDER BY a.name LIMIT 300';
}
$db = JFactory::getDBO();
$db->setQuery($query);
$urls = $db->loadObjectList();
$this->values = array();
$this->values[] = JHTML::_('select.option', '0', JText::_('ALL_URLS') );
foreach($urls as $onrUrl){
if(strlen($onrUrl->name)>55) $onrUrl->name = substr($onrUrl->name,0,20).'...'.substr($onrUrl->name,-30);
$this->values[] = JHTML::_('select.option', $onrUrl->urlid, $onrUrl->name );
}
}
function queueScheduled()
{
$this->messages = array();
$mailReady = $this->getReadyMail();
if (empty($mailReady)) {
$this->messages[] = JText::_('NO_SCHED');
return false;
}
$this->nbNewsletterScheduled = count($mailReady);
$queueClass = acymailing_get('class.queue');
foreach ($mailReady as $mailid => $mail) {
$nbQueue = $queueClass->queue($mailid, $mail->senddate);
$this->messages[] = JText::sprintf('ADDED_QUEUE_SCHEDULE', $nbQueue, $mailid, '<b><i>' . $mail->subject . '</i></b>');
}
$db = JFactory::getDBO();
$db->setQuery('UPDATE ' . acymailing_table('mail') . ' SET published = 1 WHERE mailid IN (' . implode(',', array_keys($mailReady)) . ')');
$db->query();
return true;
}
function save($action)
{
if (empty($action->action_id) && empty($action->userid)) {
$user = JFactory::getUser();
$action->userid = $user->id;
}
JPluginHelper::importPlugin('acymailing');
$dispatcher = JDispatcher::getInstance();
if (empty($action->action_id)) {
$dispatcher->trigger('onAcyBeforeActionCreate', array(&$action));
$status = $this->database->insertObject(acymailing_table('action'), $action);
} else {
$dispatcher->trigger('onAcyBeforeActionModify', array(&$action));
$status = $this->database->updateObject(acymailing_table('action'), $action, 'action_id');
}
if ($status) {
return empty($action->action_id) ? $this->database->insertid() : $action->action_id;
}
return false;
}
function load($table)
{
$db = JFactory::getDBO();
$query = 'SELECT COUNT(*) as total,userid FROM ' . acymailing_table($table) . ' WHERE `userid` > 0';
if (!empty($this->type)) {
$query .= ' AND `type` = ' . $db->quote($this->type);
}
$query .= ' GROUP BY userid';
$db->setQuery($query);
$allusers = $db->loadObjectList('userid');
$allnames = array();
if (!empty($allusers)) {
$db->setQuery('SELECT name,id FROM #__users WHERE id IN (' . implode(',', array_keys($allusers)) . ') ORDER BY name ASC');
$allnames = $db->loadObjectList('id');
}
$this->values = array();
$this->values[] = JHTML::_('select.option', '0', JText::_('ALL_CREATORS'));
foreach ($allnames as $userid => $oneCreator) {
$this->values[] = JHTML::_('select.option', $userid, $oneCreator->name . ' ( ' . $allusers[$userid]->total . ' )');
}
}
function getDefault()
{
$queryDefaultTemp = 'SELECT * FROM ' . acymailing_table('template') . ' WHERE premium = 1 AND published = 1 ORDER BY ordering ASC LIMIT 1';
if (acymailing_level(3)) {
$my = JFactory::getUser();
if (!ACYMAILING_J16) {
$groups = $my->gid;
$condGroup = ' OR access LIKE (\'%,' . $groups . ',%\')';
} else {
jimport('joomla.access.access');
$groups = JAccess::getGroupsByUser($my->id, false);
$condGroup = '';
foreach ($groups as $group) {
$condGroup .= ' OR access LIKE (\'%,' . $group . ',%\')';
}
}
$queryDefaultTemp = 'SELECT * FROM ' . acymailing_table('template') . ' WHERE premium = 1 AND published = 1 AND (access = \'all\' ' . $condGroup . ') ORDER BY ordering ASC LIMIT 1';
}
$this->database->setQuery($queryDefaultTemp);
$template = $this->database->loadObject();
return $this->_prepareTemplate($template);
}
function display($tpl = null)
{
$config = acymailing_config();
$doc = JFactory::getDocument();
$acyToolbar = acymailing::get('helper.toolbar');
$acyToolbar->help('dashboard');
$acyToolbar->setTitle(JText::_('ACY_CPANEL'), 'dashboard');
$acyToolbar->display();
$db = JFactory::getDBO();
$userQuery = 'SELECT (confirmed + enabled) AS addition, COUNT(subid) AS total FROM #__acymailing_subscriber GROUP BY addition';
$db->setQuery($userQuery);
$userResult = $db->loadObjectList('addition');
$userStats = new stdClass();
$userStats->nbUnconfirmedAndDisabled = empty($userResult[0]->total) ? 0 : $userResult[0]->total;
$userStats->nbConfirmed = empty($userResult[1]->total) ? 0 : $userResult[1]->total;
$userStats->nbConfirmed += empty($userResult[2]->total) ? 0 : $userResult[2]->total;
$userStats->total = $userStats->nbConfirmed + $userStats->nbUnconfirmedAndDisabled;
$userStats->confirmedPercent = empty($userStats->total) ? 0 : round($userStats->nbConfirmed * 100 / $userStats->total, 0);
$listsQuery = "SELECT COUNT(DISTINCT(l.listid)) FROM #__acymailing_list as l LEFT JOIN #__acymailing_listsub as ls ON l.listid=ls.listid WHERE l.type='list' AND ls.status=1 AND ls.subid IS NOT NULL";
$db->setQuery($listsQuery);
$atLeastOneSub = $db->loadResult();
$db->setQuery('SELECT COUNT(listid) FROM #__acymailing_list WHERE type = "list"');
$nbLists = $db->loadResult();
$listStats = new stdClass();
$listStats->atLeastOneSub = $atLeastOneSub;
$listStats->noSub = $nbLists - $atLeastOneSub;
$listStats->total = $nbLists;
$listStats->subscribedPercent = empty($nbLists) ? 0 : round($atLeastOneSub * 100 / $nbLists, 0);
$nlQuery = 'SELECT count(mailid) AS total, published FROM #__acymailing_mail WHERE type = "news" GROUP BY published';
$db->setQuery($nlQuery);
$nlResult = $db->loadObjectList('published');
$nlStats = new stdClass();
$nlStats->nbUnpublished = empty($nlResult[0]->total) ? 0 : $nlResult[0]->total;
$nlStats->nbpublished = empty($nlResult[1]->total) ? 0 : $nlResult[1]->total;
$nlStats->total = $nlStats->nbpublished + $nlStats->nbUnpublished;
$nlStats->publishedPercent = empty($nlStats->total) ? 0 : round($nlStats->nbpublished * 100 / $nlStats->total, 0);
$this->assignRef('nlStats', $nlStats);
$this->assignRef('userStats', $userStats);
$this->assignRef('listStats', $listStats);
$this->assignRef('config', $config);
$geolocParam = $config->get('geolocation');
if (!empty($geolocParam) && $geolocParam != 1) {
$condition = '';
if (strpos($geolocParam, 'creation') !== false) {
$condition = " WHERE geolocation_type='creation'";
}
$db = JFactory::getDBO();
$nbUsersToGet = 100;
$query = 'SELECT geolocation_type, geolocation_subid, geolocation_country_code, geolocation_city, geolocation_country, geolocation_state';
$query .= ' FROM #__acymailing_geolocation' . $condition . ' GROUP BY geolocation_subid ORDER BY geolocation_created DESC LIMIT ' . $nbUsersToGet;
$db->setQuery($query);
$geoloc = $db->loadObjectList();
if (!empty($geoloc)) {
$markCities = array();
$diffCountries = false;
$dataDetails = array();
$addresses = array();
foreach ($geoloc as $mark) {
$indexCity = array_search($mark->geolocation_city, $markCities);
if ($indexCity === false) {
array_push($markCities, $mark->geolocation_city);
array_push($dataDetails, 1);
$addresses[] = $mark->geolocation_city . ' ' . $mark->geolocation_state . ' ' . $mark->geolocation_country;
} else {
$dataDetails[$indexCity] += 1;
}
if (!$diffCountries) {
if (!empty($region) && $region != $mark->geolocation_country_code) {
$region = 'world';
$diffCountries = true;
} else {
$region = $mark->geolocation_country_code;
}
}
}
$this->assignRef('geoloc_city', $markCities);
$this->assignRef('geoloc_details', $dataDetails);
$this->assignRef('geoloc_region', $region);
$this->assignRef('geoloc_addresses', $addresses);
$this->assign('nbUsersToGet', $nbUsersToGet);
}
}
$doc->addScript("https://www.google.com/jsapi");
$db->setQuery("SELECT count(`subid`) as total, DATE_FORMAT(FROM_UNIXTIME(`created`),'%Y-%m-%d') as subday FROM " . acymailing_table('subscriber') . " WHERE `created` > 100000 GROUP BY subday ORDER BY subday DESC LIMIT 15");
$statsusers = $db->loadObjectList();
$this->assignRef('statsusers', $statsusers);
$db = JFactory::getDBO();
$db->setQuery('SELECT name,email,html,confirmed,subid,created FROM ' . acymailing_table('subscriber') . ' ORDER BY subid DESC LIMIT 10');
$users10 = $db->loadObjectList();
$this->assignRef('users', $users10);
$toggleClass = acymailing_get('helper.toggle');
$this->assignRef('toggleClass', $toggleClass);
$listStatusQuery = 'SELECT count(subid) AS total, list.name AS listname, list.listid, listsub.status FROM #__acymailing_list AS list JOIN #__acymailing_listsub AS listsub ON list.listid = listsub.listid GROUP BY listsub.listid, listsub.status';
$db->setQuery($listStatusQuery);
$listStatusResult = $db->loadObjectList();
$listStatusData = array();
foreach ($listStatusResult as $oneResult) {
$listStatusData[$oneResult->listname][$oneResult->status] = $oneResult->total;
}
$this->assignRef('listStatusData', $listStatusData);
$db->setQuery("SELECT count(userstats.`mailid`) as total, DATE_FORMAT(FROM_UNIXTIME(`senddate`), '%Y-%m-%d') AS send_date,\r\n\t\t\t\t\t\tSUM(CASE WHEN fail>0 THEN 1 ELSE 0 END) AS nbFailed\r\n\t\t\t\t\t\tFROM " . acymailing_table('userstats') . " AS userstats\r\n\t\t\t\t\t\tWHERE userstats.senddate > " . intval(time() - 2628000) . "\r\n\t\t\t\t\t\tGROUP BY send_date\r\n\t\t\t\t\t\tORDER BY send_date DESC");
$newsletters = $db->loadObjectList();
$this->assignRef('newsletters', $newsletters);
$progressBarSteps = new stdClass();
$progressBarSteps->listCreated = !empty($listStats->total) ? 1 : 0;
$progressBarSteps->contactCreated = !empty($userStats->total) ? 1 : 0;
$progressBarSteps->newsletterCreated = !empty($nlStats->total) ? 1 : 0;
$db->setQuery('SELECT subid FROM #__acymailing_userstats LIMIT 1');
$result = $db->loadResult();
$progressBarSteps->newsletterSent = !empty($result) ? 1 : 0;
$this->assignRef('progressBarSteps', $progressBarSteps);
parent::display($tpl);
}
function saveStats()
{
$subid = empty($this->subid) ? JRequest::getInt('subid') : $this->subid;
$mailid = empty($this->mailid) ? JRequest::getInt('mailid') : $this->mailid;
if (empty($subid) or empty($mailid)) {
return false;
}
$db = JFactory::getDBO();
$db->setQuery('SELECT `open` FROM ' . acymailing_table('userstats') . ' WHERE `mailid` = ' . intval($mailid) . ' AND `subid` = ' . intval($subid) . ' LIMIT 1');
$actual = $db->loadObject();
if (empty($actual)) {
return false;
}
$userHelper = acymailing_get('helper.user');
$db->setQuery('UPDATE #__acymailing_subscriber SET `lastopen_date` = ' . time() . ', `lastopen_ip` = ' . $db->Quote($userHelper->getIP()) . ' WHERE `subid` = ' . intval($subid));
try {
$results = $db->query();
} catch (Exception $e) {
$results = null;
}
if ($results === null) {
acymailing_display(isset($e) ? $e->getMessage() : substr(strip_tags($db->getErrorMsg()), 0, 200) . '...', 'error');
exit;
}
$open = 0;
if (empty($actual->open)) {
$open = 1;
$unique = ',openunique = openunique +1';
} elseif ($this->countReturn) {
$open = $actual->open + 1;
$unique = '';
}
if (empty($open)) {
return true;
}
$ipClass = acymailing_get('helper.user');
$ip = $ipClass->getIP();
$db->setQuery('UPDATE ' . acymailing_table('userstats') . ' SET open = ' . $open . ', opendate = ' . time() . ', `ip`= ' . $db->Quote($ip) . ' WHERE mailid = ' . $mailid . ' AND subid = ' . $subid . ' LIMIT 1');
try {
$results = $db->query();
} catch (Exception $e) {
$results = null;
}
if ($results === null) {
acymailing_display(isset($e) ? $e->getMessage() : substr(strip_tags($db->getErrorMsg()), 0, 200) . '...', 'error');
exit;
}
$browsers = array("abrowse", "abolimba", "3ds", "acoo browser", "alienforce", "amaya", "amigavoyager", "antfresco", "aol", "arora", "avant", "baidubrowser", "beamrise", "beonex", "blackbird", "blackhawk", "bolt", "browsex", "browzar", "bunjalloo", "camino", "charon", "chromium", "columbus", "cometbird", "dragon", "conkeror", "coolnovo", "corom", "deepnet explorer", "demeter", "deskbrowse", "dillo", "dooble", "dplus", "edbrowse", "element browser", "elinks", "epic", "epiphany", "firebird", "flock", "fluid", "galeon", "globalmojo", "greenbrowser", "hotjava", "hv3", "hydra", "ibrowse", "icab", "icebrowser", "iceape", "icecat", "icedragon", "iceweasel", "surfboard", "irider", "iron", "meleon", "ninja", "kapiko", "kazehakase", "strata", "kkman", "konqueror", "kylo", "lbrowser", "links", "lobo", "lolifox", "lunascape", "lynx", "maxthon", "midori", "minibrowser", "mosaic", "multizilla", "myibrow", "netcaptor", "netpositive", "netscape", "navigator", "netsurf", "nintendobrowser", "offbyone", "omniweb", "orca", "oregano", "otter", "palemoon", "patriott", "perk", "phaseout", "phoenix", "polarity", "playstation 4", "qtweb internet browser", "qupzilla", "rekonq", "retawq", "roccat", "rockmelt", "ryouko", "saayaa", "seamonkey", "shiira", "sitekiosk", "skipstone", "sleipnir", "slimboat", "slimbrowser", "metasr", "stainless", "sundance", "sundial", "sunrise", "superbird", "surf", "swiftweasel", "tenfourfox", "theworld", "tjusig", "tencenttraveler", "ultrabrowser", "usejump", "uzbl", "vonkeror", "v3m", "webianshell", "webrender", "weltweitimnetzbrowser", "whitehat aviator", "wkiosk", "worldwideweb", "wyzo", "smiles", "yabrowser", "yrcweblink", "zbrowser", "zipzap", "firefox", "msie", "opera", "chrome", "safari", "thunderbird", "outlook", "airmail", "barca", "eudora", "gcmail", "lotus", "pocomail", "postbox", "shredder", "sparrow", "spicebird", "bat!", "tizenbrowser", "mozilla", "gecko");
$name = "unknown";
$version = "";
if (isset($_SERVER['HTTP_USER_AGENT'])) {
$agent = strtolower($_SERVER['HTTP_USER_AGENT']);
} else {
$agent = "unknown";
}
foreach ($browsers as $oneBrowser) {
if (preg_match("#({$oneBrowser})[/ ]?([0-9]*)#", $agent, $match)) {
$name = $match[1];
$version = $match[2];
break;
}
}
$isMobile = 0;
$osName = '';
if (preg_match('/(android|bb\\d+|meego).+mobile|avantgo|bada\\/|blackberry|blazer|compal|elaine|fennec|hiptop|iemobile|ip(hone|od)|iris|kindle|lge |maemo|midp|mmp|mobile.+firefox|netfront|opera m(ob|in)i|palm( os)?|phone|p(ixi|re)\\/|plucker|pocket|psp|series(4|6)0|symbian|treo|up\\.(browser|link)|vodafone|wap|windows (ce|phone)|xda|xiino/', $agent) || preg_match('/1207|6310|6590|3gso|4thp|50[1-6]i|770s|802s|a wa|abac|ac(er|oo|s\\-)|ai(ko|rn)|al(av|ca|co)|amoi|an(ex|ny|yw)|aptu|ar(ch|go)|as(te|us)|attw|au(di|\\-m|r |s )|avan|be(ck|ll|nq)|bi(lb|rd)|bl(ac|az)|br(e|v)w|bumb|bw\\-(n|u)|c55\\/|capi|ccwa|cdm\\-|cell|chtm|cldc|cmd\\-|co(mp|nd)|craw|da(it|ll|ng)|dbte|dc\\-s|devi|dica|dmob|do(c|p)o|ds(12|\\-d)|el(49|ai)|em(l2|ul)|er(ic|k0)|esl8|ez([4-7]0|os|wa|ze)|fetc|fly(\\-|_)|g1 u|g560|gene|gf\\-5|g\\-mo|go(\\.w|od)|gr(ad|un)|haie|hcit|hd\\-(m|p|t)|hei\\-|hi(pt|ta)|hp( i|ip)|hs\\-c|ht(c(\\-| |_|a|g|p|s|t)|tp)|hu(aw|tc)|i\\-(20|go|ma)|i230|iac( |\\-|\\/)|ibro|idea|ig01|ikom|im1k|inno|ipaq|iris|ja(t|v)a|jbro|jemu|jigs|kddi|keji|kgt( |\\/)|klon|kpt |kwc\\-|kyo(c|k)|le(no|xi)|lg( g|\\/(k|l|u)|50|54|\\-[a-w])|libw|lynx|m1\\-w|m3ga|m50\\/|ma(te|ui|xo)|mc(01|21|ca)|m\\-cr|me(rc|ri)|mi(o8|oa|ts)|mmef|mo(01|02|bi|de|do|t(\\-| |o|v)|zz)|mt(50|p1|v )|mwbp|mywa|n10[0-2]|n20[2-3]|n30(0|2)|n50(0|2|5)|n7(0(0|1)|10)|ne((c|m)\\-|on|tf|wf|wg|wt)|nok(6|i)|nzph|o2im|op(ti|wv)|oran|owg1|p800|pan(a|d|t)|pdxg|pg(13|\\-([1-8]|c))|phil|pire|pl(ay|uc)|pn\\-2|po(ck|rt|se)|prox|psio|pt\\-g|qa\\-a|qc(07|12|21|32|60|\\-[2-7]|i\\-)|qtek|r380|r600|raks|rim9|ro(ve|zo)|s55\\/|sa(ge|ma|mm|ms|ny|va)|sc(01|h\\-|oo|p\\-)|sdk\\/|se(c(\\-|0|1)|47|mc|nd|ri)|sgh\\-|shar|sie(\\-|m)|sk\\-0|sl(45|id)|sm(al|ar|b3|it|t5)|so(ft|ny)|sp(01|h\\-|v\\-|v )|sy(01|mb)|t2(18|50)|t6(00|10|18)|ta(gt|lk)|tcl\\-|tdg\\-|tel(i|m)|tim\\-|t\\-mo|to(pl|sh)|ts(70|m\\-|m3|m5)|tx\\-9|up(\\.b|g1|si)|utst|v400|v750|veri|vi(rg|te)|vk(40|5[0-3]|\\-v)|vm40|voda|vulc|vx(52|53|60|61|70|80|81|83|85|98)|w3c(\\-| )|webc|whit|wi(g |nc|nw)|wmlb|wonu|x700|yas\\-|your|zeto|zte\\-/i', substr($agent, 0, 4))) {
$isMobile = 1;
$osName = "unknown";
$mobileOs = array("bada" => "Bada", "ubuntu; mobile" => "Ubuntu", "ubuntu; tablet" => "Ubuntu", "tizen" => "Tizen", "palm os" => "Palm", "meego" => "meeGo", "symbian" => "Symbian", "symbos" => "Symbian", "blackberry" => "BlackBerry", "windows ce" => "Windows Phone", "windows mobile" => "Windows Phone", "windows phone" => "Windows Phone", "iphone" => "iOS", "ipad" => "iOS", "ipod" => "iOS", "android" => "Android");
$mobileOsKeys = array_keys($mobileOs);
foreach ($mobileOsKeys as $oneMobileOsKey) {
if (preg_match("/({$oneMobileOsKey})/", $agent, $match2)) {
$osName = $mobileOs[$match2[1]];
break;
}
}
}
$db->setQuery('UPDATE ' . acymailing_table('userstats') . ' SET `is_mobile` = ' . intval($isMobile) . ', `mobile_os` = ' . $db->Quote($osName) . ', `browser` = ' . $db->Quote($name) . ', browser_version = ' . intval($version) . ', user_agent = ' . $db->Quote($agent) . ' WHERE mailid = ' . $mailid . ' AND subid = ' . $subid . ' LIMIT 1');
try {
$results = $db->query();
} catch (Exception $e) {
$results = null;
}
if ($results === null) {
acymailing_display(isset($e) ? $e->getMessage() : substr(strip_tags($db->getErrorMsg()), 0, 200) . '...', 'error');
exit;
}
$db->setQuery('UPDATE ' . acymailing_table('stats') . ' SET opentotal = opentotal +1 ' . $unique . ' WHERE mailid = ' . $mailid . ' LIMIT 1');
$db->query();
if (!empty($subid)) {
$filterClass = acymailing_get('class.filter');
$filterClass->subid = $subid;
$filterClass->trigger('opennews');
}
$classGeoloc = acymailing_get('class.geolocation');
$classGeoloc->saveGeolocation('open', $subid);
JPluginHelper::importPlugin('acymailing');
$dispatcher = JDispatcher::getInstance();
$dispatcher->trigger('onAcyOpenMail', array($subid, $mailid));
return true;
}
