A Joomla CLI script to send database query output as a CSV attachment via email

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:

	<?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();

view raw joomla-cli-script-email-csv-attachment.php hosted with ❤ by GitHub

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.