3

So I've been handed a custom administrator module which simply displays database table data in an HTML table. My task is to create a clickable button on the module that will generate the csv data on the fly and force the download of the same content as an excel document.

Andrew Eddie recommends these solutions, but I don't know how/where to implement them so that:

  1. I can authenticate that the downloading user is, in fact, the same logged in user with permission (I don't want someone to be able to learn the url and be able to directly access this private data. I assume a token is the best tool for this)
  2. I can enjoy the Joomla query builder methods to gather the necessary data
  3. I can avoid displaying any other administrator (menu dom) content with my generated data

It could divert to a different static method like this:

if (JFactory::getApplication()->input->get->get('export', 0, 'int'))
{
    mod[modname]Helper::exportToExcel();
}
else
{
    mod[modname]Helper::displayAsHTML();
}

to access:

public static function exportToExcel()
{
    header("Content-type: text/csv");
    header("Content-Disposition: attachment; filename=Some_Report_" . JFactory::getDate()->format('Y_m_d_h_i_s') . ".csv");  // @ https://docs.joomla.org/How_to_use_JDate it looks like someone got a bit spastic with the percent signs  (bookmarked for upcoming Pizza, Bugs, & Fun)
    header("Pragma: no-cache");
    header("Expires: 0");

    // get my db data and echo it as csv data

    // Close the application gracefully.
    JFactory::getApplication()->close();
}

But in doing so, my csv data is appended to the earlier loaded content -- no good. I wasn't able to ob_ the other content out; and I don't even know if this is a good approach to begin with.

The custom administrator module that I am looking at is as basic as it gets with files like:

  • mod_[modname].php
  • helper.php (previoius dev does all processing and displaying from here)
  • tmpl/default.php (previous dev didn't output anything from here)

My plan is to use javascript to append a button after the module's title:

<h3 class="module-title nav-header">[the module's title] [add my button here]</h3>

What I don't know is: Where do I park my new code and how should I craft the destination url to access it?

I'd like to learn how to write this custom code with the correct architecture and best practices in mind for my own education. I'm hoping someone is going to blow my mind with some elegant Joomla magic.

  • Have you tried adding ob_end_clean(); to the top of your function? – Lodder Feb 20 at 8:59
  • Yes, I tried that. I get the nav menu garbage in my generated file. I tried a few combinations of cleaning and flushing, to no avail. – mickmackusa Feb 20 at 9:02
3

If this needs to be done by the module (there's no accompanying component or the component is 3rd party and you can't touch it), use com_ajax.

Point the form to com_ajax:

<form method="post">
    <input type="hidden" name="option" value="com_ajax">
    <?php // Module name without mod_ prefix ?>
    <input type="hidden" name="module" value="custom">
    <?php // Output format. CSV is not supported, use raw ?>
    <input type="hidden" name="format" value="raw">
    <?php // Add session token ?>
    <?php echo HTMLHelper::_('form.token'); ?>
    <button type="submit">Download</button>
</form>

Create getAjax() method in your module's helper:

public static function getAjax()
{
    $app = Factory::getApplication();

    // Check for request forgeries
    if (!$app->getSession()->checkToken())
    {
        $app->enqueueMessage(Text::_('JINVALID_TOKEN'));
        $app->redirect('index.php');
    }

    // Check for user permissions (only example)
    if (!Factory::getUser()->authorise('core.admin'))
    {
        $app->enqueueMessage(Text::_('JERROR_ALERTNOAUTHOR'));
        $app->redirect('index.php?option=com_users&view=login');
    }

    self::exportToExcel();
}
  • For the record, the module is extracting data from EventBooking tables. – mickmackusa Feb 20 at 10:05
  • The other answer suitably removes the unwanted content, but if feels hacky. I am interested in hearing advice on best practices. – mickmackusa Feb 20 at 10:17
  • Ajax component was created for cases like this. From docs: "Examples of use cases include ... A module that interacts with a component that you did not develop". The problem with your current method is that modules do not have an endpoint and they should not be using query vars like that. By using a generic var like export you are potentially creating conflicts with components. – Sharky Feb 20 at 11:24
  • I'm not saying you can't use query vars in a module. Just not like that. It makes sense when functionality is based on components and restricted to specific component pages, e.g. showing search keyword when on search results page, showing related articles when on article page, implementing split menus, etc. But in this case it is indeed a hack. – Sharky Feb 20 at 11:26
  • I needed to declare use Joomla\CMS\HTML\HTMLHelper; and change Factory to JFactory, but as normal your answer was very helpful. – mickmackusa Feb 20 at 14:42
3

Regarding clearing the output buffer, some time ago I had a similar issue and this solved the issue, at least for me:

    // Close output buffers till the level reaches 0
    while (ob_get_level() > 0)
    {
        ob_end_clean();
    }

    // Start capturing output into a buffer
    ob_start();

    // Content to export
    echo $toExport;

    // Get the buffer and clear it
    $fileContent = ob_get_contents();
    ob_end_clean();

    // Echo will start the download
    echo $fileContent; 
  • This indeed works perfectly to strip the html output from my csv output. Nice work around. Are you able to explain why this works? What the while() loop is doing? Why the echoes are necessary? – mickmackusa Feb 20 at 10:15
  • I just wrote a $counter to increment inside the while loop and found that there were 4 iterations. I'd love to understand what was going on there. And for future researchers, echo $toExport; and $fileContent = can be safely omitted. – mickmackusa Feb 20 at 10:27
  • @mickmackusa In my case, the ob_clean() was clearing the inner output buffer. To clear all of them, I need to use the while loop. – n.h. Feb 20 at 11:03
2

It turned out that using @n.h.'s nifty output buffer cleansing workaround was not necessary after implementing @Sharky's advice regarding com_ajax and getAjax().

So that researchers will be able to access a complete solution for their own needs, I'll post a "boiled down" version of my complete working solution which can be easily amended to suit other scenarios. This is condensed into the mod_[modname].php** and helper.php files.

*Note that tmp/default.php was not used, though I typically use it for displaying html content.

**I will use the placeholder [modname] for all places where the actual/unique module name should be written.

Using fopen("php://output", "w") and fputcsv() is an integral piece of best practice inside of the exportToExcel() method to provide a "stable" document.

I deliberately used JHtml::date('now', 'Y_m_d_h_i_s', 'Australia/Brisbane') for the generated report's timestamp so that it will be consistent regardless of the user's profile timezone and regardless of the server's timezone.

The default behavior of this script will be to display an html table with the query data. The new excel document will be generated and forced to download after the "Download" button has been clicked.

mod_[modname].php

defined('_JEXEC') or die;
require_once dirname(__FILE__) . '/helper.php';
mod[modname]Helper::showAsHTML();

helper.php

defined('_JEXEC') or die;

use Joomla\CMS\HTML\HTMLHelper;

class mod[modname]Helper
{
    public static function getAjax()
    {
        $app = JFactory::getApplication();

        // Check for request forgeries
        if (!$app->getSession()->checkToken())
        {
            $app->enqueueMessage(Text::_('JINVALID_TOKEN'));
            $app->redirect('index.php');
        }

        // Check for user permissions (only example)
        if (!JFactory::getUser()->authorise('core.admin'))
        {
            $app->enqueueMessage(Text::_('JERROR_ALERTNOAUTHOR'));
            $app->redirect('index.php?option=com_users&view=login');
        }

        self::exportToExcel();
    }

    public static function getResultSet()
    {           
        $db = JFactory::getDbo();
        $query = $db->getQuery(true);

        // build your query; yatta-yatta

        $db->setQuery($query);

        // load the results
        try
        {
            if (!$resultset = $db->loadAssocList()) {
                return [['Status' => 'No Rows Found']];
            }
            return $resultset;
        }
        catch (Exception $e)
        {
            return [['Error' => 'Syntax Failure']]; //, ['Message' => $e->getMessage()], ['Query' => $query->dump()]];
        }
    }

    public static function exportToExcel()
    {
        // define output settings
        header("Content-type: text/csv");
        header("Content-Disposition: attachment; filename=Report_" . JHtml::date('now', 'Y_m_d_h_i_s', 'Australia/Brisbane') . ".csv");  // create unique document name
        header("Pragma: no-cache");
        header("Expires: 0");

        // get db data
        $resultset = self::getResultSet();
        $column_heads = array_map('ucwords', str_replace('_', ' ', array_keys($resultset[0])));  // pretty up the column headings

        // write output
        $fp = fopen("php://output", "w");
        fputcsv ($fp, $column_heads);
        foreach ($resultset as $row)
        {
            fputcsv($fp, $row);
        }
        fclose($fp);

        // Close the application gracefully.
        JFactory::getApplication()->close();
    }

    public static function showAsHTML()
    {
        $resultset    = self::getResultSet();
        $column_heads = array_map('ucwords', str_replace('_', ' ', array_keys($resultset[0])));  // pretty up the column headings

        ?>
        <h3>Report</h3>
        <form method="post" style="display:inline; margin-left:12px;">
            <input type="hidden" name="option" value="com_ajax">
            <input type="hidden" name="module" value="[mod_name]">
            <input type="hidden" name="format" value="raw">
            <?=HTMLHelper::_('form.token')?>
            <button type="submit">Download</button>
        </form> 
        <?php
        echo "<table>";
            echo "<tr><th>" , implode("</th><th>", $column_heads) , "</th></tr>";
            foreach ($resultset as $row) {
                echo "<tr><td>" , implode("</td><td>", array_map("htmlspecialchars", $row)) , "</td></tr>";
            }
        echo "</table>";
    }
}
  • 1
    You do not need the input check in mod_[modname].php, it could still cause conflicts with components. Render HTML layout as you would in any other module. CSV will be handled by com_ajax alone. – Sharky Feb 26 at 6:26
  • Right again, fixed. – mickmackusa Feb 26 at 23:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.