Daily report of RSForm!Pro form submissions

formsreport.php

<?php
/**
 * @package    Formreport CLI
 *
 * @copyright  Copyright (c)2016 René Kreijveld
 * @license    GNU General Public License version 2 or later
 */

/**
 * Form Check CLI.
 *
 * This is a command-line script that reports all RSForm!Pro form submissions of the last 24 hours.
 * Written by René Kreijveld, René Kreijveld Webdevelopment
 *
 * Make sure you input your own emailaddress at line 134.
 *
 */

// Make sure we're being called from the command line, not a web interface
if (PHP_SAPI !== 'cli')
{
	die('This is a command line only application.');
}

// We are a valid entry point.
const _JEXEC = 1;

// Load system defines
if (file_exists(dirname(__DIR__) . '/defines.php'))
{
	require_once dirname(__DIR__) . '/defines.php';
}

if (!defined('_JDEFINES'))
{
	define('JPATH_BASE', dirname(__DIR__));
	require_once JPATH_BASE . '/includes/defines.php';
}

// Get the framework.
require_once JPATH_LIBRARIES . '/import.legacy.php';

// Bootstrap the CMS libraries.
require_once JPATH_LIBRARIES . '/cms.php';

// Import the configuration.
require_once JPATH_CONFIGURATION . '/configuration.php';

// System configuration.
$config = new JConfig;

print_r($config->fromname);die();

class FormsCli extends JApplicationCli
{
	public function doExecute()
	{

		// Database connector
		$db = JFactory::getDBO();

		$style = ' style="font-family: Arial,Helvetica,sans-serif;font-size:12px;"';

		$output = '<p' . $style . '>The past 24 hours the following form-submissions were done:</p>';

		// Set date, past 24 hours
		$datum = date("Y-m-d H:m:s", strtotime('-24 hours', time()));

		// Get forms
		$query = $db->getQuery(true)
			->select($db->quoteName(array('FormId','FormName')))
			->from($db->quoteName('#__rsform_forms'))
			->where($db->quoteName('Published') . ' = 1')
			->order($db->quoteName('FormId') . ' ASC');
		$db->setQuery($query);
		$forms = $db->loadObjectList();

		foreach ($forms as $form)
		{
			$formId = $form->FormId;
			$formname = $form->FormName;

			// Get submissions
			$query = $db->getQuery(true)
				->select($db->quoteName(array('SubmissionId','DateSubmitted')))
				->from($db->quoteName('#__rsform_submissions'))
				->where($db->quoteName('DateSubmitted') . ' > "' . $datum . '"')
				->where($db->quoteName('FormId') . ' = ' . (int) $formId);
			$db->setQuery($query);
			$submissions = $db->loadObjectList();
			if ($submissions)
			{
				$output .= '<h2' . $style . '><strong>' . $formname . '</strong> (form id ' . $formId . ')</h2>';
				$output .= '<table cellspacing="0" cellpadding="2" border="1">';
				$first = true;
				foreach ($submissions as $submission)
				{
					// Get details for each submission
					$query = $db->getQuery(true)
						->select('*')
						->from($db->quoteName('#__rsform_submission_values'))
						->where($db->quoteName('FormId') . ' = ' . (int) $formId)
						->where($db->quoteName('SubmissionId') . ' = ' . (int) $submission->SubmissionId)
						->order($db->quoteName('SubmissionValueId') . ' ASC');
					$db->setQuery($query);
					$submissionvalues = $db->loadObjectList();
					if ($first)
					{
						$output .= '<tr>';
						$output .= '<th' . $style . '>datum</th>';
						foreach ($submissionvalues as $sv) {
							if ($sv->FieldName !== 'formId') $output .= '<th' . $style . '>' . $sv->FieldName . '</th>';
						}
						$output .= '</tr>';
						$first = false;
					}
					$output .= '<tr>';
					$output .= '<td' . $style . '>' . $submission->DateSubmitted . '</td>';
					foreach ($submissionvalues as $sv) {
						if ($sv->FieldName !== 'formId') $output .= '<td' . $style . '>' . $sv->FieldValue . '</td>';
					}
					$output .= '</tr>';
				}
				$output .= '</table>';
			}
		}

		// Send email
		$mail = JFactory::getMailer();
		$mail->setSubject("Daily report RSForm!Pro form submissions");
		$mail->setBody($output);
		$mail->IsHTML(true);
		$mail->setSender(array($config->mailfrom, $config->fromname));
		$mail->addRecipient('you@yourdomain.com');
		$mail->Send();

	}

}

// Instantiate the application object, passing the class name to JCli::getInstance
// and use chaining to execute the application.
JApplicationCli::getInstance('FormsCli')->execute();