image from: https://pixabay.com/en/linux-window-terminal-command-154766/
This Joomla CLI script lets you send a DB query output as a CSV attachment via Email.
You need to have a working Joomla website instance with emails setup done.
PHP Joomla CLI Script:
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <?php | |
| /** | |
| * @version 3.6 | |
| * @package CLI | |
| * @author Manoj L<manoj_l@techjoomla.com> | |
| * @copyright Copyright (c) 2009-2017 Manoj L. All rights reserved | |
| * @license GNU General Public License version 2, or later | |
| */ | |
| // Make sure this is being called from the command line | |
| if (PHP_SAPI !== 'cli') | |
| { | |
| die('This is a command line only application.'); | |
| } | |
| // Ensure, this has a valid entry point. | |
| const _JEXEC = 1; | |
| // Import necessary class files | |
| define('JPATH_BASE', realpath(dirname(__FILE__) . '/..')); | |
| require_once JPATH_BASE . '/includes/defines.php'; | |
| require_once JPATH_BASE . '/includes/framework.php'; | |
| require_once JPATH_BASE . '/libraries/import.php'; | |
| // Load Joomla configuration file | |
| require_once JPATH_CONFIGURATION . '/configuration.php'; | |
| // Import joomla cli app file | |
| jimport('joomla.application.cli'); | |
| // Error reporting | |
| ini_set('display_errors', 'On'); | |
| /** | |
| * Class for cli - send csv email | |
| * | |
| * @since 3.6 | |
| */ | |
| class SendCsvEmail extends JApplicationCli | |
| { | |
| /** | |
| * Execute cli task | |
| * | |
| * @return void | |
| * | |
| * @since 3.6 | |
| */ | |
| public function execute() | |
| { | |
| // Define vars | |
| $app = JFactory::getApplication('site'); | |
| $db = JFactory::getDbo(); | |
| $query = $db->getQuery(true); | |
| // Query | |
| $query->select('u.id AS userid, u.name, u.email'); | |
| $query->from('#__users as u'); | |
| $query->order('u.id'); | |
| $db->setQuery($query); | |
| $users = $db->loadObjectList(); | |
| // Genrate CSV | |
| $filePath = JPATH_SITE . '/tmp/users-report-' . date('Y-m-d') . '-.csv'; | |
| $fp = fopen($filePath, 'w'); | |
| // Add CSV first row | |
| $csvFirstRow = array('userid', 'name', 'email'); | |
| fputcsv($fp, $csvFirstRow); | |
| foreach ($users as $user) | |
| { | |
| // Convert to array for fputcsv | |
| $user = (array) $user; | |
| fputcsv($fp, $user); | |
| } | |
| // Get mail config from Joomla config | |
| $from = $app->getCfg('mailfrom'); | |
| $fromname = $app->getCfg('fromname'); | |
| $emailSubject = 'Users report ' . date('Y-m-d'); | |
| $emailBody = 'Hi, <br/><br/>Please find attached user information CSV export.<br/><br/>Regards.'; | |
| $mailer = JFactory::getMailer(); | |
| $mailer->isHTML(true); | |
| $mailer->Encoding = 'base64'; | |
| // Add recepient | |
| $mailer->addRecipient('manoj@dummy-domain.com'); | |
| $mailer->setSender(array($from, $fromname)); | |
| $mailer->setSubject($emailSubject); | |
| $mailer->setBody($emailBody); | |
| // Add CSV attachment | |
| $mailer->addAttachment($filePath); | |
| if ($mailer->Send()) | |
| { | |
| $this->out(sprintf('Email sent')); | |
| } | |
| else | |
| { | |
| $this->out(sprintf('Email sending failed')); | |
| } | |
| // Delete temp. csv file | |
| @unlink($filePath); | |
| } | |
| } | |
| // Call task | |
| JApplicationCli::getInstance('SendCsvEmail')->execute(); |
To use this:
1. Open terminal, Browse to Joomla root
cd /var/www/my-joomla-site
2. Execute PHP file
php cli/joomla-cli-script-email-csv-attachment.php
3. You will see this as o/p on successful email sending
Email sent
You can also setup cronjob to automate email sending.
