From e98ac32716dfe9343baa5bce7ad2c1e98c40540c Mon Sep 17 00:00:00 2001
From: Alexis Degrugillier <github@ainw.org>
Date: Wed, 22 Jan 2014 19:06:46 -0500
Subject: Ajout de statistiques de l'application

Conflicts:
	app/i18n/en.php
	app/i18n/fr.php
---
 app/Models/StatsDAO.php | 178 ++++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 178 insertions(+)
 create mode 100644 app/Models/StatsDAO.php

(limited to 'app/Models/StatsDAO.php')

diff --git a/app/Models/StatsDAO.php b/app/Models/StatsDAO.php
new file mode 100644
index 000000000..a317c8f27
--- /dev/null
+++ b/app/Models/StatsDAO.php
@@ -0,0 +1,178 @@
+<?php
+
+class FreshRSS_StatsDAO extends Minz_ModelPdo {
+
+	/**
+	 * Calculates entry repartition for all feeds and for main stream.
+	 * The repartition includes:
+	 *   - total entries
+	 *   - read entries
+	 *   - unread entries
+	 *   - favorite entries
+	 * 
+	 * @return type
+	 */
+	public function calculateEntryRepartition() {
+		$repartition = array();
+
+		// Generates the repartition for the main stream of entry
+		$sql = <<<SQL
+SELECT COUNT(1) AS `total`,
+COUNT(1) - SUM(e.is_read) AS `unread`,
+SUM(e.is_read) AS `read`,
+SUM(e.is_favorite) AS `favorite`
+FROM {$this->prefix}entry AS e
+, {$this->prefix}feed AS f
+WHERE e.id_feed = f.id
+AND f.priority = 10
+SQL;
+		$stm = $this->bd->prepare($sql);
+		$stm->execute();
+		$res = $stm->fetchAll(PDO::FETCH_ASSOC);
+		$repartition['main_stream'] = $res[0];
+
+		// Generates the repartition for all entries
+		$sql = <<<SQL
+SELECT COUNT(1) AS `total`,
+COUNT(1) - SUM(e.is_read) AS `unread`,
+SUM(e.is_read) AS `read`,
+SUM(e.is_favorite) AS `favorite`
+FROM {$this->prefix}entry AS e
+SQL;
+		$stm = $this->bd->prepare($sql);
+		$stm->execute();
+		$res = $stm->fetchAll(PDO::FETCH_ASSOC);
+		$repartition['all_feeds'] = $res[0];
+
+		return $repartition;
+	}
+
+	/**
+	 * Calculates entry count per day on a 30 days period.
+	 * Returns the result as a JSON string.
+	 * 
+	 * @return string
+	 */
+	public function calculateEntryCount() {
+		$count = array();
+
+		// Generates a list of 30 last day to be sure we always have 30 days.
+		// If we do not do that kind of thing, we'll end up with holes in the
+		// days if the user do not have a lot of feeds.
+		$sql = <<<SQL
+SELECT - (tens.val + units.val + 1) AS day
+FROM (
+    SELECT 0 AS val
+    UNION ALL SELECT 1
+    UNION ALL SELECT 2
+    UNION ALL SELECT 3
+    UNION ALL SELECT 4
+    UNION ALL SELECT 5
+    UNION ALL SELECT 6
+    UNION ALL SELECT 7
+    UNION ALL SELECT 8
+    UNION ALL SELECT 9
+) AS units
+CROSS JOIN (
+    SELECT 0 AS val
+    UNION ALL SELECT 10
+    UNION ALL SELECT 20
+) AS tens
+ORDER BY day ASC
+SQL;
+		$stm = $this->bd->prepare($sql);
+		$stm->execute();
+		$res = $stm->fetchAll(PDO::FETCH_ASSOC);
+		foreach ($res as $value) {
+			$count[$value['day']] = 0;
+		}
+
+		// Get stats per day for the last 30 days and applies the result on 
+		// the array created with the last query.
+		$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 -30 DAY), '%Y%m%d') AND DATE_FORMAT(DATE_ADD(NOW(), INTERVAL -1 DAY), '%Y%m%d')
+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[$value['day']] = (int) $value['count'];
+		}
+
+		return $this->convertToSerie($count);
+	}
+
+	/**
+	 * Calculates feed count per category.
+	 * Returns the result as a JSON string.
+	 * 
+	 * @return string
+	 */
+	public function calculateFeedByCategory() {
+		$sql = <<<SQL
+SELECT c.name AS label
+, COUNT(f.id) AS data
+FROM {$this->prefix}category AS c,
+{$this->prefix}feed AS f
+WHERE c.id = f.category
+GROUP BY label
+SQL;
+		$stm = $this->bd->prepare($sql);
+		$stm->execute();
+		$res = $stm->fetchAll(PDO::FETCH_ASSOC);
+
+		return $this->convertToPieSerie($res);
+	}
+
+	/**
+	 * Calculates entry count per category.
+	 * Returns the result as a JSON string.
+	 * 
+	 * @return string
+	 */
+	public function calculateEntryByCategory() {
+		$sql = <<<SQL
+SELECT c.name AS label
+, COUNT(e.id) AS data
+FROM {$this->prefix}category AS c,
+{$this->prefix}feed AS f,
+{$this->prefix}entry AS e
+WHERE c.id = f.category
+AND f.id = e.id_feed
+GROUP BY label
+SQL;
+		$stm = $this->bd->prepare($sql);
+		$stm->execute();
+		$res = $stm->fetchAll(PDO::FETCH_ASSOC);
+
+		return $this->convertToPieSerie($res);
+	}
+
+	private function convertToSerie($data) {
+		$serie = array();
+
+		foreach ($data as $key => $value) {
+			$serie[] = array($key, $value);
+		}
+
+		return json_encode($serie);
+	}
+
+	private function convertToPieSerie($data) {
+		$serie = array();
+
+		foreach ($data as $value) {
+			$value['data'] = array(array(0, (int)$value['data']));
+			$serie[] = $value;
+		}
+
+		return json_encode($serie);
+	}
+
+}
-- 
cgit v1.2.3