Created
January 11, 2014 00:09
-
-
Save pasamio/8365139 to your computer and use it in GitHub Desktop.
Joomla! CLI Script to collect user stats such as active sessions and also to calculate churn. Includes install script to create tables.
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 | |
/** | |
* @package Joomla.CLI | |
* @subpackage cli_userstats | |
*/ | |
// Make sure we're being called from the command line, not a web interface | |
if (array_key_exists('REQUEST_METHOD', $_SERVER)) die(); | |
/** | |
* CLI Bootstrap | |
* | |
* Run the framework bootstrap with a couple of mods based on the script's needs | |
*/ | |
// We are a valid entry point. | |
define('_JEXEC', 1); | |
define('DS', DIRECTORY_SEPARATOR); | |
// Load system defines | |
if (file_exists(dirname(dirname(__FILE__)) . '/defines.php')) | |
{ | |
require_once dirname(dirname(__FILE__)) . '/defines.php'; | |
} | |
if (!defined('_JDEFINES')) | |
{ | |
define('JPATH_BASE', dirname(dirname(__FILE__))); | |
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; | |
// Configure error reporting to maximum for CLI output. | |
error_reporting(E_ALL); | |
ini_set('display_errors', 1); | |
// Load Library language | |
$lang = JFactory::getLanguage(); | |
/** | |
* A command line cron job to run the userstats collection. | |
* | |
* @package Joomla.CLI | |
* @subpackage cli_userstats | |
*/ | |
class UserstatsCli extends JApplicationCli | |
{ | |
/** | |
* Entry point for CLI script | |
* | |
* @return void | |
*/ | |
public function doExecute() | |
{ | |
$db = JFactory::getDbo(); | |
$db->setQuery("set time_zone = '+00:00'"); | |
$db->execute(); | |
try { | |
if ($this->input->get('install')) | |
{ | |
$this->install($db); | |
} | |
if ($this->input->get('sessions')) | |
{ | |
$this->sessions($db); | |
} | |
if ($this->input->get('churn')) | |
{ | |
$this->churn($db); | |
} | |
} catch (Exception $e) | |
{ | |
$this->out('Error processing request: ' . $e->getMessage()); | |
} | |
} | |
protected function install($db) | |
{ | |
$db->setQuery(<<<INSTALL | |
CREATE TABLE IF NOT EXISTS `#__userstats_sessions` ( | |
`measurement_date` datetime NOT NULL, | |
`active_sessions` int(11) DEFAULT NULL, | |
`guest_sessions` int(11) DEFAULT NULL, | |
PRIMARY KEY (`measurement_date`) | |
) ENGINE=InnoDB DEFAULT CHARSET=utf8; | |
INSTALL | |
); | |
$db->execute(); | |
$this->out('Sessions Table Installed'); | |
$db->setQuery(<<<INSTALL | |
CREATE TABLE IF NOT EXISTS `#__userstats_logins` ( | |
`measurement_date` date NOT NULL, | |
`last_2days` int(11) DEFAULT NULL, | |
`last_7days` int(11) DEFAULT NULL, | |
`last_30days` int(11) DEFAULT NULL, | |
`last_45days` int(11) DEFAULT NULL, | |
`last_60days` int(11) DEFAULT NULL, | |
PRIMARY KEY (`measurement_date`) | |
) ENGINE=InnoDB DEFAULT CHARSET=utf8; | |
INSTALL | |
); | |
$db->execute(); | |
$this->out('Logins Table Installed'); | |
} | |
protected function sessions($db) | |
{ | |
$session = JFactory::getSession(); | |
$query = $db->getQuery(true) | |
->delete($db->quoteName('#__session')) | |
->where($db->quoteName('time') . ' < ' . $db->quote((int) (time() - $session->getExpire()))); | |
$db->setQuery($query); | |
$db->execute(); | |
$db->setQuery('insert into #__userstats_sessions SELECT NOW(), count(*), guests from #__session s, (SELECT count(*) as guests FROM #__session where guest = 1) guests '); | |
$db->execute(); | |
$this->out("Sessions stored."); | |
} | |
protected function churn($db) | |
{ | |
$db->setQuery(<<<QUERY | |
REPLACE into #__userstats_logins | |
select now(), last_2days, last_7days, last_30days, last_45days, last_60days | |
FROM | |
(select count(*) last_2days from #__users where DATEDIFF(NOW(), lastvisitdate) <= 2) day2, | |
(select count(*) last_7days from #__users where DATEDIFF(NOW(), lastvisitdate) <= 7) day7, | |
(select count(*) last_30days from #__users where DATEDIFF(NOW(), lastvisitdate) <= 30) day30, | |
(select count(*) last_45days from #__users where DATEDIFF(NOW(), lastvisitdate) <= 45) day45, | |
(select count(*) last_60days from #__users where DATEDIFF(NOW(), lastvisitdate) <= 60) day60 | |
QUERY | |
); | |
$db->execute(); | |
$this->out("Churn values stored."); | |
} | |
} | |
// Instantiate the application object, passing the class name to JCli::getInstance | |
// and use chaining to execute the application. | |
JApplicationCli::getInstance('UserstatsCli')->execute(); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment