From 891de7a051afece74a14bf7de1aa0ce26d63c5b9 Mon Sep 17 00:00:00 2001 From: Marcel Werk Date: Sun, 13 Apr 2014 23:19:53 +0200 Subject: [PATCH] Added stat handlers (WIP) --- com.woltlab.wcf/cronjob.xml | 17 ++++++- com.woltlab.wcf/objectType.xml | 13 +++++ com.woltlab.wcf/objectTypeDefinition.xml | 5 ++ .../cronjob/StatDailyBuilderCronjob.class.php | 45 +++++++++++++++++ .../stat/AbstractStatDailyHandler.class.php | 49 +++++++++++++++++++ .../stat/AttachmentStatDailyHandler.class.php | 39 +++++++++++++++ .../system/stat/IStatDailyHandler.class.php | 22 +++++++++ .../stat/UserStatDailyHandler.class.php | 24 +++++++++ wcfsetup/setup/db/install.sql | 12 +++++ 9 files changed, 225 insertions(+), 1 deletion(-) create mode 100644 wcfsetup/install/files/lib/system/cronjob/StatDailyBuilderCronjob.class.php create mode 100644 wcfsetup/install/files/lib/system/stat/AbstractStatDailyHandler.class.php create mode 100644 wcfsetup/install/files/lib/system/stat/AttachmentStatDailyHandler.class.php create mode 100644 wcfsetup/install/files/lib/system/stat/IStatDailyHandler.class.php create mode 100644 wcfsetup/install/files/lib/system/stat/UserStatDailyHandler.class.php diff --git a/com.woltlab.wcf/cronjob.xml b/com.woltlab.wcf/cronjob.xml index e4e3b1dbcd..de2e28988d 100644 --- a/com.woltlab.wcf/cronjob.xml +++ b/com.woltlab.wcf/cronjob.xml @@ -123,7 +123,22 @@ - + + + 0 + 1 + * + * + * + 1 + 1 + 1 + + + + + + 0 1 * diff --git a/com.woltlab.wcf/objectType.xml b/com.woltlab.wcf/objectType.xml index bc9bcb8645..9c74b0cefc 100644 --- a/com.woltlab.wcf/objectType.xml +++ b/com.woltlab.wcf/objectType.xml @@ -311,5 +311,18 @@ 100 + + + + com.woltlab.wcf.user + com.woltlab.wcf.statDailyHandler + + + + com.woltlab.wcf.attachment + com.woltlab.wcf.statDailyHandler + + + \ No newline at end of file diff --git a/com.woltlab.wcf/objectTypeDefinition.xml b/com.woltlab.wcf/objectTypeDefinition.xml index 8f4baff22b..aa717e42fb 100644 --- a/com.woltlab.wcf/objectTypeDefinition.xml +++ b/com.woltlab.wcf/objectTypeDefinition.xml @@ -143,5 +143,10 @@ com.woltlab.wcf.rebuildData + + + com.woltlab.wcf.statDailyHandler + + diff --git a/wcfsetup/install/files/lib/system/cronjob/StatDailyBuilderCronjob.class.php b/wcfsetup/install/files/lib/system/cronjob/StatDailyBuilderCronjob.class.php new file mode 100644 index 0000000000..2704311efa --- /dev/null +++ b/wcfsetup/install/files/lib/system/cronjob/StatDailyBuilderCronjob.class.php @@ -0,0 +1,45 @@ + + * @package com.woltlab.wcf + * @subpackage system.cronjob + * @category Community Framework + */ +class StatDailyBuilderCronjob extends AbstractCronjob { + /** + * @see \wcf\system\cronjob\ICronjob::execute() + */ + public function execute(Cronjob $cronjob) { + parent::execute($cronjob); + + // get date + $d = DateUtil::getDateTimeByTimestamp(TIME_NOW); + $d->setTimezone(new \DateTimeZone(TIMEZONE)); + $d->sub(new \DateInterval('P1D')); + $d->setTime(0, 0); + $date = $d->getTimestamp(); + + // prepare insert statement + $sql = "INSERT IGNORE INTO wcf".WCF_N."_stat_daily + (objectTypeID, date, counter, total) + VALUES (?, ?, ?, ?)"; + $statement = WCF::getDB()->prepareStatement($sql); + + // get object types + foreach (ObjectTypeCache::getInstance()->getObjectTypes('com.woltlab.wcf.statDailyHandler') as $objectType) { + $data = $objectType->getProcessor()->getData($date); + + $statement->execute(array($objectType->objectTypeID, $d->format('Y-m-d'), $data['counter'], $data['total'])); + } + } +} diff --git a/wcfsetup/install/files/lib/system/stat/AbstractStatDailyHandler.class.php b/wcfsetup/install/files/lib/system/stat/AbstractStatDailyHandler.class.php new file mode 100644 index 0000000000..df2d31416d --- /dev/null +++ b/wcfsetup/install/files/lib/system/stat/AbstractStatDailyHandler.class.php @@ -0,0 +1,49 @@ + + * @package com.woltlab.wcf + * @subpackage system.stat + * @category Community Framework + */ +abstract class AbstractStatDailyHandler implements IStatDailyHandler { + /** + * Counts the number of rows for a single day. + * + * @param integer $date + * @param string $tableName + * @param string $dateColumnName + * @return integer + */ + protected function getCounter($date, $tableName, $dateColumnName) { + $sql = "SELECT COUNT(*) AS count + FROM " . $tableName . " + WHERE " . $dateColumnName . " BETWEEN ? AND ?"; + $statement = WCF::getDB()->prepareStatement($sql); + $statement->execute(array($date, $date + 86400)); + return $statement->fetchColumn(); + } + + /** + * Counts the total number of rows. + * + * @param integer $date + * @param string $tableName + * @param string $dateColumnName + * @return integer + */ + protected function getTotal($date, $tableName, $dateColumnName) { + $sql = "SELECT COUNT(*) AS count + FROM " . $tableName . " + WHERE " . $dateColumnName . " < ?"; + $statement = WCF::getDB()->prepareStatement($sql); + $statement->execute(array($date + 86400)); + return $statement->fetchColumn(); + } +} diff --git a/wcfsetup/install/files/lib/system/stat/AttachmentStatDailyHandler.class.php b/wcfsetup/install/files/lib/system/stat/AttachmentStatDailyHandler.class.php new file mode 100644 index 0000000000..44395d582c --- /dev/null +++ b/wcfsetup/install/files/lib/system/stat/AttachmentStatDailyHandler.class.php @@ -0,0 +1,39 @@ + + * @package com.woltlab.wcf + * @subpackage system.stat + * @category Community Framework + */ +class AttachmentStatDailyHandler extends AbstractStatDailyHandler { + /** + * @see \wcf\system\stat\IStatDailyHandler::getData() + */ + public function getData($date) { + $sql = "SELECT SUM(filesize) + FROM wcf".WCF_N."_attachment + WHERE uploadTime BETWEEN ? AND ?"; + $statement = WCF::getDB()->prepareStatement($sql); + $statement->execute(array($date, $date + 86400)); + $counter = intval($statement->fetchColumn()); + + $sql = "SELECT SUM(filesize) + FROM wcf".WCF_N."_attachment + WHERE uploadTime < ?"; + $statement = WCF::getDB()->prepareStatement($sql); + $statement->execute(array($date + 86400)); + $total = intval($statement->fetchColumn()); + + return array( + 'counter' => $counter, + 'total' => $total + ); + } +} diff --git a/wcfsetup/install/files/lib/system/stat/IStatDailyHandler.class.php b/wcfsetup/install/files/lib/system/stat/IStatDailyHandler.class.php new file mode 100644 index 0000000000..ef1d3d873f --- /dev/null +++ b/wcfsetup/install/files/lib/system/stat/IStatDailyHandler.class.php @@ -0,0 +1,22 @@ + + * @package com.woltlab.wcf + * @subpackage system.stat + * @category Community Framework + */ +interface IStatDailyHandler { + /** + * Returns the stats. + * + * @param integer $date + * @return array + */ + public function getData($date); +} diff --git a/wcfsetup/install/files/lib/system/stat/UserStatDailyHandler.class.php b/wcfsetup/install/files/lib/system/stat/UserStatDailyHandler.class.php new file mode 100644 index 0000000000..4ebae6560d --- /dev/null +++ b/wcfsetup/install/files/lib/system/stat/UserStatDailyHandler.class.php @@ -0,0 +1,24 @@ + + * @package com.woltlab.wcf + * @subpackage system.stat + * @category Community Framework + */ +class UserStatDailyHandler extends AbstractStatDailyHandler { + /** + * @see \wcf\system\stat\IStatDailyHandler::getData() + */ + public function getData($date) { + return array( + 'counter' => $this->getCounter($date, 'wcf'.WCF_N.'_user', 'registrationDate'), + 'total' => $this->getTotal($date, 'wcf'.WCF_N.'_user', 'registrationDate') + ); + } +} diff --git a/wcfsetup/setup/db/install.sql b/wcfsetup/setup/db/install.sql index fe09ea57c4..552c78298c 100644 --- a/wcfsetup/setup/db/install.sql +++ b/wcfsetup/setup/db/install.sql @@ -846,6 +846,16 @@ CREATE TABLE wcf1_spider ( UNIQUE KEY spiderIdentifier (spiderIdentifier) ); +DROP TABLE IF EXISTS wcf1_stat_daily; +CREATE TABLE wcf1_stat_daily ( + objectTypeID INT(10) NOT NULL, + date DATE NOT NULL, + counter INT(10) NOT NULL DEFAULT 0, + total INT(10) NOT NULL DEFAULT 0, + + UNIQUE KEY (objectTypeID, date) +); + DROP TABLE IF EXISTS wcf1_style; CREATE TABLE wcf1_style ( styleID INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY, @@ -1534,6 +1544,8 @@ ALTER TABLE wcf1_tag_to_object ADD FOREIGN KEY (tagID) REFERENCES wcf1_tag (tagI ALTER TABLE wcf1_tag_to_object ADD FOREIGN KEY (languageID) REFERENCES wcf1_language (languageID) ON DELETE CASCADE; ALTER TABLE wcf1_tag_to_object ADD FOREIGN KEY (objectTypeID) REFERENCES wcf1_object_type (objectTypeID) ON DELETE CASCADE; +ALTER TABLE wcf1_stat_daily ADD FOREIGN KEY (objectTypeID) REFERENCES wcf1_object_type (objectTypeID) ON DELETE CASCADE; + ALTER TABLE wcf1_search_index ADD FOREIGN KEY (objectTypeID) REFERENCES wcf1_object_type (objectTypeID) ON DELETE CASCADE; ALTER TABLE wcf1_search_index ADD FOREIGN KEY (languageID) REFERENCES wcf1_language (languageID) ON DELETE SET NULL; -- 2.20.1