public function getMailboxesForBounsecheck() { $db = JFactory::getDbo(); // Get default SMTP and Mailbox profile ids $smtpId = MailHelper::getDefaultSmtp('idOnly'); $mailboxId = MailHelper::getDefaultMailbox('idOnly'); // Get mailbox ids with start mailing date $db->setQuery(' SELECT DISTINCT mp.mailbox_profile_id, min(q.created) AS startdate ' . ' FROM #__newsletter_mailbox_profiles AS mp ' . ' RIGHT JOIN #__newsletter_smtp_profiles AS sp ' . 'ON (sp.mailbox_profile_id = mp.mailbox_profile_id) ' . 'OR (sp.mailbox_profile_id = ' . NewsletterTableMailboxprofile::MAILBOX_DEFAULT . ' AND mp.mailbox_profile_id=' . $mailboxId . ') ' . ' JOIN #__newsletter_newsletters AS n ' . 'ON (n.smtp_profile_id = sp.smtp_profile_id) ' . 'OR (n.smtp_profile_id = ' . NewsletterModelEntitySmtpprofile::DEFAULT_SMTP_ID . ' AND sp.smtp_profile_id=' . $smtpId . ') ' . ' JOIN #__newsletter_queue AS q ON q.newsletter_id = n.newsletter_id ' . ' WHERE q.state = ' . NewsletterTableQueue::STATE_SENT . ' GROUP BY mp.mailbox_profile_id'); // Get mailboxes and their start dates $db->setQuery(' SELECT DISTINCT mp.*, t.startdate' . ' FROM #__newsletter_mailbox_profiles AS mp ' . ' JOIN (' . (string) $db->getQuery() . ') as t ON mp.mailbox_profile_id=t.mailbox_profile_id'); $result = $db->loadAssocList(); foreach ($result as &$mb) { if (!empty($mb['data'])) { $mb['data'] = (array) json_decode($mb['data']); } else { $mb['data'] = array('lastDate' => NULL, 'ids' => array()); } $mb['password'] = base64_decode($mb['password']); } return $result; }
public function getMailProfiles($nid) { $db = JFactory::getDbo(); // Get default SMTP and Mailbox profile ids $smtpId = MailHelper::getDefaultSmtp('idOnly'); $mailboxId = MailHelper::getDefaultMailbox('idOnly'); $newsletter = JTable::getInstance('Newsletter', 'NewsletterTable'); $newsletter->load($nid); // The default profile is the SMTP profile if ($newsletter->smtp_profile_id == 0) { $smtp = (array) MailHelper::getJoomlaProfile(); $mailbox = (array) MailHelper::getDefaultMailbox(); $smtp['mailbox_profile_id'] = !empty($mailbox['mailbox_profile_id']) ? $mailbox['mailbox_profile_id'] : 0; $res = array('mailbox' => $mailbox, 'smtp' => $smtp); } else { $db->setQuery('SELECT DISTINCT ' . 'mp.mailbox_profile_id AS mp_mailbox_profile_id, ' . 'mp.mailbox_profile_name AS mp_mailbox_profile_name, ' . 'mp.mailbox_server AS mp_mailbox_server, ' . 'mp.mailbox_server_type AS mp_mailbox_server_type, ' . 'mp.mailbox_port AS mp_mailbox_port, ' . 'mp.is_ssl AS mp_is_ssl, ' . 'mp.username AS mp_username, ' . 'mp.password AS mp_password, ' . 'sp.smtp_profile_id AS sp_smtp_profile_id, ' . 'sp.smtp_profile_name AS sp_smtp_profile_name, ' . 'sp.from_name AS sp_from_name, ' . 'sp.from_email AS sp_from_email, ' . 'sp.reply_to_name AS sp_reply_to_name, ' . 'sp.reply_to_email AS sp_reply_to_email, ' . 'sp.smtp_server AS sp_smtp_server, ' . 'sp.smtp_port AS sp_smtp_port, ' . 'sp.is_ssl AS sp_is_ssl, ' . 'sp.pop_before_smtp AS sp_pop_before_smtp, ' . 'sp.username AS sp_username, ' . 'sp.password AS sp_password, ' . 'sp.mailbox_profile_id AS sp_mailbox_profile_id ' . 'FROM #__newsletter_mailbox_profiles AS mp ' . 'JOIN #__newsletter_smtp_profiles AS sp ' . 'ON (sp.mailbox_profile_id = mp.mailbox_profile_id) ' . 'OR (sp.mailbox_profile_id = ' . NewsletterTableMailboxprofile::MAILBOX_DEFAULT . ' AND mp.mailbox_profile_id=' . $mailboxId . ') ' . 'JOIN #__newsletter_newsletters AS n ' . 'ON (n.smtp_profile_id = sp.smtp_profile_id) ' . 'OR (n.smtp_profile_id = ' . NewsletterModelEntitySmtpprofile::DEFAULT_SMTP_ID . ' AND sp.smtp_profile_id=' . $smtpId . ') ' . 'WHERE n.newsletter_id = ' . (int) $nid); //echo (string)$db->getQuery(); $result = $db->loadAssoc(); $res = array('mailbox' => array('mailbox_profile_id' => $result['mp_mailbox_profile_id'], 'mailbox_profile_name' => $result['mp_mailbox_profile_name'], 'mailbox_server' => $result['mp_mailbox_server'], 'mailbox_server_type' => $result['mp_mailbox_server_type'], 'mailbox_port' => $result['mp_mailbox_port'], 'is_ssl' => $result['mp_is_ssl'], 'username' => $result['mp_username'], 'password' => $result['mp_password']), 'smtp' => array('smtp_profile_id' => $result['sp_smtp_profile_id'], 'smtp_profile_name' => $result['sp_smtp_profile_name'], 'from_name' => $result['sp_from_name'], 'from_email' => $result['sp_from_email'], 'reply_to_name' => $result['sp_reply_to_name'], 'reply_to_email' => $result['sp_reply_to_email'], 'smtp_server' => $result['sp_smtp_server'], 'smtp_port' => $result['sp_smtp_port'], 'is_ssl' => $result['sp_is_ssl'], 'pop_before_smtp' => $result['sp_pop_before_smtp'], 'username' => $result['sp_username'], 'password' => $result['sp_password'], 'mailbox_profile_id' => $result['sp_mailbox_profile_id'])); } return $res; }