Merge pull request #390 from aledeg/statistics

Ajout de statistiques de l'application

7 files changed, 308 insertions, 3 deletions

diff --git a/app/Controllers/indexController.php b/app/Controllers/indexController.php
index 45ded6fd4..84ed992f9 100755
--- a/app/Controllers/indexController.php
+++ b/app/Controllers/indexController.php
@@ -6,12 +6,14 @@ class FreshRSS_index_Controller extends Minz_ActionController {
 	private $entryDAO;
 	private $feedDAO;
 	private $catDAO;
+	private $statsDAO;
 
 	function __construct($router) {
 		parent::__construct($router);
 		$this->entryDAO = new FreshRSS_EntryDAO ();
 		$this->feedDAO = new FreshRSS_FeedDAO ();
 		$this->catDAO = new FreshRSS_CategoryDAO ();
+		$this->statsDAO = new FreshRSS_StatsDAO ();
 	}
 
 	public function indexAction () {
@@ -198,6 +200,14 @@ class FreshRSS_index_Controller extends Minz_ActionController {
 				return false;
 		}
 	}
+	
+	public function statsAction () {
+		Minz_View::appendScript (Minz_Url::display ('/scripts/flotr2.min.js?' . @filemtime(PUBLIC_PATH . '/scripts/flotr2.min.js')));
+		$this->view->repartition = $this->statsDAO->calculateEntryRepartition();
+		$this->view->count = ($this->statsDAO->calculateEntryCount());
+		$this->view->feedByCategory = $this->statsDAO->calculateFeedByCategory();
+		$this->view->entryByCategory = $this->statsDAO->calculateEntryByCategory();
+	}
 
 	public function aboutAction () {
 		Minz_View::prependTitle (Minz_Translate::t ('about') . ' · ');
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);
+	}
+
+}
diff --git a/app/i18n/en.php b/app/i18n/en.php
index 5d9b01f0b..963105f35 100644
--- a/app/i18n/en.php
+++ b/app/i18n/en.php
@@ -13,6 +13,7 @@ return array (
 	'category'			=> 'Category',
 	'shortcuts'			=> 'Shortcuts',
 	'about'				=> 'About',
+	'stats'				=> 'Statistics',
 
 	'your_rss_feeds'		=> 'Your RSS feeds',
 	'add_rss_feed'			=> 'Add a RSS feed',
@@ -20,7 +21,8 @@ return array (
 	'import_export_opml'		=> 'Import / export (OPML)',
 
 	'subscription_management'	=> 'Subscriptions management',
-	'all_feeds'			=> 'Main stream',
+	'main_stream'			=> 'Main stream',
+	'all_feeds'				=> 'All feeds',
 	'favorite_feeds'		=> 'Favourites (%d)',
 	'not_read'			=> '%d unread',
 	'not_reads'			=> '%d unread',
@@ -297,4 +299,14 @@ return array (
 	// format for date() function, %s allows to indicate month in letter
 	'format_date'			=> '%s j\<\s\u\p\>S\<\/\s\u\p\> Y',
 	'format_date_hour'		=> '%s j\<\s\u\p\>S\<\/\s\u\p\> Y \a\t H\:i',
+	
+	'status_favorites'	=> 'favourites',
+	'status_read'		=> 'read',
+	'status_unread'		=> 'unread',
+	'status_total'		=> 'total',
+	
+	'stats_entry_repartition'	=> 'Entry repartition',
+	'stats_entry_per_day'		=> 'Entry per day (last 30 days)',
+	'stats_feed_per_category'	=> 'Feed per category',
+	'stats_entry_per_category'	=> 'Entry per category',
 );
diff --git a/app/i18n/fr.php b/app/i18n/fr.php
index fd2257c70..c8b03ef8b 100644
--- a/app/i18n/fr.php
+++ b/app/i18n/fr.php
@@ -13,6 +13,7 @@ return array (
 	'category'			=> 'Catégorie',
 	'shortcuts'			=> 'Raccourcis',
 	'about'				=> 'À propos',
+	'stats'				=> 'Statistiques',
 
 	'your_rss_feeds'		=> 'Vos flux RSS',
 	'add_rss_feed'			=> 'Ajouter un flux RSS',
@@ -20,7 +21,8 @@ return array (
 	'import_export_opml'		=> 'Importer / exporter (OPML)',
 
 	'subscription_management'	=> 'Gestion des abonnements',
-	'all_feeds'			=> 'Flux principal',
+	'main_stream'			=> 'Flux principal',
+	'all_feeds'				=> 'Tous les flux',
 	'favorite_feeds'		=> 'Favoris (%d)',
 	'not_read'			=> '%d non lu',
 	'not_reads'			=> '%d non lus',
@@ -297,4 +299,14 @@ return array (
 	// format pour la fonction date(), %s permet d'indiquer le mois en toutes lettres
 	'format_date'			=> 'j %s Y',
 	'format_date_hour'		=> 'j %s Y \à H\:i',
+	
+	'status_favorites'	=> 'favoris',
+	'status_read'		=> 'lus',
+	'status_unread'		=> 'non lus',
+	'status_total'		=> 'total',
+	
+	'stats_entry_repartition'	=> 'Répartition des articles',
+	'stats_entry_per_day'		=> 'Nombre d’articles par jour (30 derniers jours)',
+	'stats_feed_per_category'	=> 'Flux par categorie',
+	'stats_entry_per_category'	=> 'Article par categorie',
 );
diff --git a/app/layout/aside_flux.phtml b/app/layout/aside_flux.phtml
index f7d8b12b9..1e3211886 100644
--- a/app/layout/aside_flux.phtml
+++ b/app/layout/aside_flux.phtml
@@ -23,7 +23,7 @@
 			<div class="category all">
 				<a data-unread="<?php echo $this->nb_not_read; ?>" class="btn<?php echo $this->get_c == 'a' ? ' active' : ''; ?>" href="<?php echo Minz_Url::display($arUrl); ?>">
 					<?php echo FreshRSS_Themes::icon('all'); ?>
-					<?php echo Minz_Translate::t ('all_feeds'); ?>
+					<?php echo Minz_Translate::t ('main_stream'); ?>
 				</a>
 			</div>
 		</li>
diff --git a/app/layout/header.phtml b/app/layout/header.phtml
index b00c30e71..eef53a3fd 100644
--- a/app/layout/header.phtml
+++ b/app/layout/header.phtml
@@ -74,6 +74,7 @@ if (Minz_Configuration::canLogIn()) {
 				<li class="separator"></li>
 				<li class="item"><a href="<?php echo _url ('configure', 'users'); ?>"><?php echo Minz_Translate::t ('users'); ?></a></li>
 				<li class="separator"></li>
+				<li class="item"><a href="<?php echo _url ('index', 'stats'); ?>"><?php echo Minz_Translate::t ('stats'); ?></a></li>
 				<li class="item"><a href="<?php echo _url ('index', 'about'); ?>"><?php echo Minz_Translate::t ('about'); ?></a></li>
 				<li class="item"><a href="<?php echo _url ('index', 'logs'); ?>"><?php echo Minz_Translate::t ('logs'); ?></a></li>
 				<?php
diff --git a/app/views/index/stats.phtml b/app/views/index/stats.phtml
new file mode 100644
index 000000000..ab9d0a881
--- /dev/null
+++ b/app/views/index/stats.phtml
@@ -0,0 +1,92 @@
+<div class="post content">
+	<a href="<?php echo _url ('index', 'index'); ?>"><?php echo Minz_Translate::t ('back_to_rss_feeds'); ?></a>
+	
+	<h1><?php echo Minz_Translate::t ('stats'); ?></h1>
+	
+	<div class="stat">
+		<h2><?php echo Minz_Translate::t ('stats_entry_repartition')?></h2>
+		<table>
+			<thead>
+				<tr>
+					<th>&nbsp;</th>
+					<th><?php echo Minz_Translate::t ('main_stream')?></th>
+					<th><?php echo Minz_Translate::t ('all_feeds')?></th>
+				</tr>
+			</thead>
+			<tbody>
+				<tr>
+					<th><?php echo Minz_Translate::t ('status_total')?></th>
+					<td><?php echo $this->repartition['main_stream']['total']?></td>
+					<td><?php echo $this->repartition['all_feeds']['total']?></td>
+				</tr>
+				<tr>
+					<th><?php echo Minz_Translate::t ('status_read')?></th>
+					<td><?php echo $this->repartition['main_stream']['read']?></td>
+					<td><?php echo $this->repartition['all_feeds']['read']?></td>
+				</tr>
+				<tr>
+					<th><?php echo Minz_Translate::t ('status_unread')?></th>
+					<td><?php echo $this->repartition['main_stream']['unread']?></td>
+					<td><?php echo $this->repartition['all_feeds']['unread']?></td>
+				</tr>
+				<tr>
+					<th><?php echo Minz_Translate::t ('status_favorites')?></th>
+					<td><?php echo $this->repartition['main_stream']['favorite']?></td>
+					<td><?php echo $this->repartition['all_feeds']['favorite']?></td>
+				</tr>
+			</tbody>
+		</table>
+	</div>
+	
+	<div class="stat">
+		<h2><?php echo Minz_Translate::t ('stats_entry_per_day')?></h2>
+		<div id="statsEntryPerDay" style="height: 300px"></div>
+	</div>
+	
+	<div class="stat">
+		<h2><?php echo Minz_Translate::t ('stats_feed_per_category')?></h2>
+		<div id="statsFeedPerCategory" style="height: 300px"></div>
+		<div id="statsFeedPerCategoryLegend"></div>
+	</div>
+	
+	<div class="stat">
+		<h2><?php echo Minz_Translate::t ('stats_entry_per_category')?></h2>
+		<div id="statsEntryPerCategory" style="height: 300px"></div>
+		<div id="statsEntryPerCategoryLegend"></div>
+	</div>
+	
+</div>
+
+<script>
+	// Entry per day
+	Flotr.draw(document.getElementById('statsEntryPerDay'),
+		[<?php echo $this->count ?>],
+		{
+			bars: {horizontal: false, show: true},
+			xaxis: {noTicks: 6, showLabels: false, tickDecimals: 0},
+			yaxis: {showLabels: false},
+			mouse: {relative: true, track: true, trackDecimals: 0,trackFormatter: function(obj) {return obj.y;}}
+		});
+	// Feed per category
+	Flotr.draw(document.getElementById('statsFeedPerCategory'),
+		<?php echo $this->feedByCategory ?>,
+		{
+			grid: {verticalLines: false, horizontalLines: false},
+			pie: {explode: 2, show: true},
+			xaxis: {showLabels: false},
+			yaxis: {showLabels: false},
+			mouse: {relative: true, track: true, trackDecimals: 0, trackFormatter: function(obj) {return obj.y;}},
+			legend: {container: document.getElementById('statsFeedPerCategoryLegend')}
+		});
+	// Entry per category
+	Flotr.draw(document.getElementById('statsEntryPerCategory'),
+		<?php echo $this->entryByCategory ?>,
+		{
+			grid: {verticalLines: false, horizontalLines: false},
+			pie: {explode: 2, show: true},
+			xaxis: {showLabels: false},
+			yaxis: {showLabels: false},
+			mouse: {relative: true, track: true, trackDecimals: 0, trackFormatter: function(obj) {return obj.y;}},
+			legend: {container: document.getElementById('statsEntryPerCategoryLegend')}
+		});
+</script>
\ No newline at end of file