RSForm!Pro cleanup old submissions

rsformprocleanup.php

<?php
/**
 * @package	RSFormCleanUp
 * @copyright	Copyright 2018 René Kreijveld, DSD Business Internet.
 * @license	GNU General Public License version 2 or later.
 */

/**
 * A command line cron job to remove submission data from RSFormPro database tables.
 * Based on the excellent work of Peter Martin, db8.nl, see https://gist.github.com/pe7er/47bf1020b12ef29df8603fa80d1fdccd
 * Joomla namespacing was removed to make this script compatible with Joomla 2.5.
 * Added command-line variables to specify a form id and the number of months te keep old data.
 */

 // We are a valid entry point.
const _JEXEC = 1;
error_reporting(E_ALL);
ini_set('display_errors', 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.php';

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

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

/**
 * @package  Joomla.CLI
 * @since    3.0
 */
class RSFormCleanup extends JApplicationCli
{
    /**
     * Entry point for CLI script
     *
     * @return  void
     *
     * @since   3.0
     */
    public function doExecute()
    {
        // Database connector
        $db = JFactory::getDbo();

        // Default settings
        // Which IDs to clean? 0 = submissions for all forms
        $cleanFormId = 0;
        // How many months to keep data? Default is 3 months;
        $monthsToClean = 3;
        // Default settings for help and messages
        $displayHelp = false;
        $verbose = true;

        // Get command-line options
        $options = getopt("hqf:m:");
        foreach($options as $option => $option_value)
        {						
            if (strtolower($option) == "h") $displayHelp = true;
            if (strtolower($option) == "q") $verbose = false;
            if (strtolower($option) == "f")
            {
                if ($option_value == "0")
                {
                    $cleanFormId = 0;
                }
                else
                {
                    $option_value = (int) $option_value;
                    if ($option_value == 0)
                    {
                        if ($verbose)
                        {
                            $this->out("Unknown formid value, exiting.");
                            $this->out("Usage: php rsformprocleanup.php [-h] [-q] [-f<form id to clean>] [-m<months to keep>]");
                        }
                        exit;
                    }
                    else
                    {
                        $cleanFormId = $option_value;
                    }
                }
            }
            if (strtolower($option) == "m")
            {
                if ($option_value == "0")
                {
                    $monthsToClean = 0;
                }
                else
                {
                    $option_value = (int) $option_value;
                    if ($option_value == 0)
                    {
                        if ($verbose)
                        {
                            $this->out("Unknown number of months to clean, exiting.");
                            $this->out("Usage: php rsformprocleanup.php [-h] [-q] [-f<form id to clean>] [-m<months to keep>]");
                        }
                        exit;
                    }
                    else
                    {
                        $monthsToClean = $option_value;
                    }
                }
            }
        }

        if ($displayHelp)
        {
            $this->out("RSFormCleanup");
            $this->out("This CLI script cleans up old RSForm!Pro submissions.");
            $this->out("Usage: php rsformprocleanup.php [-h] [-q] [-f<form id to clean>] [-m<months to keep>]");
            $this->out("-h: Display this help.");
            $this->out("-q: Quiet, display no messages.");
            $this->out("-f: Form id of the form to clean submissions, 0 means clean for all forms. Default: 0.");
            $this->out("-m: Nr. of months to keep submissions, 2 means keep the last 2 months of submissions. Default: 3.");
            exit;
        }

        $removeBeforeDate = $db->quote(JFactory::getDate('now -' . $monthsToClean . ' month')->toSql());

        if ($verbose)
        {
            $cleanTxt = "Cleaning up all RSForm!Pro submissions before $removeBeforeDate ";
            if ($cleanFormId != 0)
            {
                $cleanTxt .= "for form id $cleanFormId.";
            }
            else
            {
                $cleanTxt .= "for all forms.";
            }
            $this->out("RSFormCleanup");
            $this->out($cleanTxt);
        }

        // Find all Submission IDs that need to get removed
        $query = $db->getQuery(true);
        $query
            ->select($db->quoteName('SubmissionId'))
            ->from($db->quoteName('#__rsform_submissions'))
            ->where($db->quoteName('DateSubmitted') . ' < ' . $removeBeforeDate);
        if ($cleanFormId != 0) $query->where($db->quoteName('FormId') . ' = ' . $cleanFormId);
        $db->setQuery($query);
        $submissionIds = implode(',', $db->loadColumn());

        // Check if there is data to be removed. If not, then quit.
        if(!$submissionIds)
        {
            // if no submissions found, stop processing
            if ($verbose) $this->out('No data found that needed to be removed.');
            exit;		
        }

        // Remove all submitted data from #__rsform_submission_values
        $query = $db->getQuery(true)
            ->delete($db->quoteName('#__rsform_submission_values'))
            ->where($db->quoteName('SubmissionId') . ' IN (' . $submissionIds . ')');
        if ($cleanFormId != 0) $query->where($db->quoteName('FormId') . ' = ' . $cleanFormId);
        $db->setQuery($query);
        $db->execute();

        // Remove form submission info from #__rsform_submissions
        $query = $db->getQuery(true)
            ->delete($db->quoteName('#__rsform_submissions'))
            ->where($db->quoteName('SubmissionId') . ' IN (' . $submissionIds . ')');
        if ($cleanFormId != 0) $query->where($db->quoteName('FormId') . ' = ' . $cleanFormId);
        $db->setQuery($query);
        $db->execute();
        if ($verbose) $this->out('The data from ' . $db->getAffectedRows() . ' submitted forms have been removed.');
    }
}

JApplicationCli::getInstance('RSFormCleanup')->execute();