From ccb56bcbf3ef69228ae4147a76cf3059f519bbf3 Mon Sep 17 00:00:00 2001
From: Alexandre Alapetite <alexandre@alapetite.fr>
Date: Sun, 11 Sep 2016 11:24:32 +0200
Subject: Simplify SQL in statistics

Reduce the use of product-specific date functions. Improve performances.
Remove redundant functions.
---
 app/Models/StatsDAO.php       | 61 +++++++------------------------------------
 app/Models/StatsDAOSQLite.php | 55 --------------------------------------
 2 files changed, 10 insertions(+), 106 deletions(-)

(limited to 'app/Models')

diff --git a/app/Models/StatsDAO.php b/app/Models/StatsDAO.php
index 4f83ff577..28882baab 100644
--- a/app/Models/StatsDAO.php
+++ b/app/Models/StatsDAO.php
@@ -61,14 +61,15 @@ SQL;
 	 */
 	public function calculateEntryCount() {
 		$count = $this->initEntryCountArray();
-		$period = self::ENTRY_COUNT_PERIOD;
+		$midnight = mktime(0, 0, 0);
+		$oldest = $midnight - (self::ENTRY_COUNT_PERIOD * 86400);
 
 		// Get stats per day for the last 30 days
 		$sql = <<<SQL
-SELECT DATEDIFF(FROM_UNIXTIME(e.date), NOW()) AS day,
-COUNT(1) AS count
-FROM {$this->prefix}entry AS e
-WHERE FROM_UNIXTIME(e.date, '%Y%m%d') BETWEEN DATE_FORMAT(DATE_ADD(NOW(), INTERVAL -{$period} DAY), '%Y%m%d') AND DATE_FORMAT(DATE_ADD(NOW(), INTERVAL -1 DAY), '%Y%m%d')
+SELECT FLOOR((date - {$midnight}) / 86400) AS day,
+COUNT(*) as count
+FROM {$this->prefix}entry
+WHERE date >= {$oldest} AND date < {$midnight}
 GROUP BY day
 ORDER BY day ASC
 SQL;
@@ -80,28 +81,7 @@ SQL;
 			$count[$value['day']] = (int) $value['count'];
 		}
 
-		return $this->convertToSerie($count);
-	}
-
-	/**
-	 * Calculates entry average per day on a 30 days period.
-	 *
-	 * @return integer
-	 */
-	public function calculateEntryAverage() {
-		$period = self::ENTRY_COUNT_PERIOD;
-
-		// Get stats per day for the last 30 days
-		$sql = <<<SQL
-SELECT COUNT(1) / {$period} AS average
-FROM {$this->prefix}entry AS e
-WHERE FROM_UNIXTIME(e.date, '%Y%m%d') BETWEEN DATE_FORMAT(DATE_ADD(NOW(), INTERVAL -{$period} DAY), '%Y%m%d') AND DATE_FORMAT(DATE_ADD(NOW(), INTERVAL -1 DAY), '%Y%m%d')
-SQL;
-		$stm = $this->bd->prepare($sql);
-		$stm->execute();
-		$res = $stm->fetch(PDO::FETCH_NAMED);
-
-		return round($res['average'], 2);
+		return $count;
 	}
 
 	/**
@@ -173,7 +153,7 @@ SQL;
 			$repartition[(int) $value['period']] = (int) $value['count'];
 		}
 
-		return $this->convertToSerie($repartition);
+		return $repartition;
 	}
 
 	/**
@@ -276,7 +256,7 @@ SQL;
 		$stm->execute();
 		$res = $stm->fetchAll(PDO::FETCH_ASSOC);
 
-		return $this->convertToPieSerie($res);
+		return $res;
 	}
 
 	/**
@@ -301,7 +281,7 @@ SQL;
 		$stm->execute();
 		$res = $stm->fetchAll(PDO::FETCH_ASSOC);
 
-		return $this->convertToPieSerie($res);
+		return $res;
 	}
 
 	/**
@@ -351,27 +331,6 @@ SQL;
 		return $stm->fetchAll(PDO::FETCH_ASSOC);
 	}
 
-	protected function convertToSerie($data) {
-		$serie = array();
-
-		foreach ($data as $key => $value) {
-			$serie[] = array($key, $value);
-		}
-
-		return $serie;
-	}
-
-	protected function convertToPieSerie($data) {
-		$serie = array();
-
-		foreach ($data as $value) {
-			$value['data'] = array(array(0, (int) $value['data']));
-			$serie[] = $value;
-		}
-
-		return $serie;
-	}
-
 	/**
 	 * Gets days ready for graphs
 	 *
diff --git a/app/Models/StatsDAOSQLite.php b/app/Models/StatsDAOSQLite.php
index 9bfe8b20a..e09d18c77 100644
--- a/app/Models/StatsDAOSQLite.php
+++ b/app/Models/StatsDAOSQLite.php
@@ -2,61 +2,6 @@
 
 class FreshRSS_StatsDAOSQLite extends FreshRSS_StatsDAO {
 
-	/**
-	 * Calculates entry count per day on a 30 days period.
-	 * Returns the result as a JSON object.
-	 *
-	 * @return JSON object
-	 */
-	public function calculateEntryCount() {
-		$count = $this->initEntryCountArray();
-		$period = parent::ENTRY_COUNT_PERIOD;
-
-		// Get stats per day for the last 30 days
-		$sql = <<<SQL
-SELECT round(julianday(e.date, 'unixepoch') - julianday('now')) AS day,
-COUNT(1) AS count
-FROM {$this->prefix}entry AS e
-WHERE strftime('%Y%m%d', e.date, 'unixepoch')
-	BETWEEN strftime('%Y%m%d', 'now', '-{$period} days')
-	AND strftime('%Y%m%d', 'now', '-1 day')
-GROUP BY day
-ORDER BY day ASC
-SQL;
-		$stm = $this->bd->prepare($sql);
-		$stm->execute();
-		$res = $stm->fetchAll(PDO::FETCH_ASSOC);
-
-		foreach ($res as $value) {
-			$count[(int) $value['day']] = (int) $value['count'];
-		}
-
-		return $this->convertToSerie($count);
-	}
-
-	/**
-	 * Calculates entry average per day on a 30 days period.
-	 *
-	 * @return integer
-	 */
-	public function calculateEntryAverage() {
-		$period = self::ENTRY_COUNT_PERIOD;
-
-		// Get stats per day for the last 30 days
-		$sql = <<<SQL
-SELECT COUNT(1) / {$period} AS average
-FROM {$this->prefix}entry AS e
-WHERE strftime('%Y%m%d', e.date, 'unixepoch')
-	BETWEEN strftime('%Y%m%d', 'now', '-{$period} days')
-	AND strftime('%Y%m%d', 'now', '-1 day')
-SQL;
-		$stm = $this->bd->prepare($sql);
-		$stm->execute();
-		$res = $stm->fetch(PDO::FETCH_NAMED);
-
-		return round($res['average'], 2);
-	}
-
 	protected function calculateEntryRepartitionPerFeedPerPeriod($period, $feed = null) {
 		if ($feed) {
 			$restrict = "WHERE e.id_feed = {$feed}";
-- 
cgit v1.2.3