1 <!DOCTYPE html
PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
2 <html xmlns
="http://www.w3.org/1999/xhtml" xml
:lang
="en" lang
="en">
4 <title
>CiviCRM usage statistics
</title
>
5 <meta http
-equiv
="Content-type" content
="text/html; charset=utf-8" />
6 <meta http
-equiv
="Content-Language" content
="en" />
7 <meta name
="Author" content
="Piotr Szotkowski" />
10 <h1
>CiviCRM usage statistics
</h1
>
13 +--------------------------------------------------------------------+
14 | CiviCRM version 4.7 |
15 +--------------------------------------------------------------------+
16 | Copyright CiviCRM LLC (c) 2004-2015 |
17 +--------------------------------------------------------------------+
18 | This file is a part of CiviCRM. |
20 | CiviCRM is free software; you can copy, modify, and distribute it |
21 | under the terms of the GNU Affero General Public License |
22 | Version 3, 19 November 2007. |
24 | CiviCRM is distributed in the hope that it will be useful, but |
25 | WITHOUT ANY WARRANTY; without even the implied warranty of |
26 | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. |
27 | See the GNU Affero General Public License for more details. |
29 | You should have received a copy of the GNU Affero General Public |
30 | License along with this program; if not, contact CiviCRM LLC |
31 | at info[AT]civicrm[DOT]org. If you have questions about the |
32 | GNU Affero General Public License or the licensing of CiviCRM, |
33 | see the CiviCRM license FAQ at http://civicrm.org/licensing |
34 +--------------------------------------------------------------------+
40 * @copyright CiviCRM LLC (c) 2004-2015
45 $user = $pass = FALSE;
46 require_once 'config.php';
47 mysql_connect('localhost', $user, $pass);
48 mysql_select_db('stats');
50 require_once 'graphs.php';
53 array('title' => 'Distinct installations',
54 'query' => 'SELECT COUNT(DISTINCT hash) data, YEAR(time) year, MONTH(time) month FROM stats GROUP BY year, month ORDER BY year, month',
57 array('title' => 'UF usage',
58 'query' => 'SELECT COUNT(DISTINCT hash) data, YEAR(time) year, MONTH(time) month, uf compare FROM stats GROUP BY year, month, uf ORDER BY year, month',
61 array('title' => 'CiviCRM versions',
62 'query' => 'SELECT COUNT(DISTINCT hash) data, YEAR(time) year, MONTH(time) month, SUBSTR(version, 1, 3) compare FROM stats WHERE version LIKE "_.%" GROUP BY year, month, SUBSTR(version, 1, 3) ORDER BY year, month',
65 array('title' => 'Drupal versions',
66 'query' => 'SELECT COUNT(DISTINCT hash) data, YEAR(time) year, MONTH(time) month, SUBSTR(ufv, 1, 1) compare FROM stats WHERE uf = "Drupal" GROUP BY year, month, SUBSTR(ufv, 1, 1) ORDER BY year, month',
69 array('title' => 'Joomla versions',
70 'query' => 'SELECT COUNT(DISTINCT hash) data, YEAR(time) year, MONTH(time) month, SUBSTR(ufv, 1, 3) compare FROM stats WHERE uf = "Joomla" GROUP BY year, month, SUBSTR(ufv, 1, 3) ORDER BY year, month',
73 array('title' => 'MySQL versions',
74 'query' => 'SELECT COUNT(DISTINCT hash) data, YEAR(time) year, MONTH(time) month, SUBSTR(MySQL, 1, 3) compare FROM stats GROUP BY year, month, SUBSTR(MySQL, 1, 3) ORDER BY year, month',
77 array('title' => 'PHP versions',
78 'query' => 'SELECT COUNT(DISTINCT hash) data, YEAR(time) year, MONTH(time) month, SUBSTR(PHP, 1, 3) compare FROM stats GROUP BY year, month, SUBSTR(PHP, 1, 3) ORDER BY year, month',
81 array('title' => 'Default languages',
82 'query' => 'SELECT COUNT(DISTINCT hash) data, YEAR(time) year, MONTH(time) month, lang compare FROM stats GROUP BY year, month, lang ORDER BY year, month',
87 switch ($_GET['current']) {
89 print '<p><a href="?current=1">include partial data for current month</a></p>';
93 print '<p><a href="?">drop partial data for current month</a></p>';
97 $fields = array('Activity', 'Case', 'Contact', 'Contribution', 'ContributionPage', 'ContributionProduct', 'Discount', 'Event', 'Friend', 'Grant', 'Mailing', 'Membership', 'MembershipBlock', 'Participant', 'Pledge', 'PledgeBlock', 'PriceSetEntity', 'Relationship', 'UFGroup', 'Widget');
99 print '<p>jump to comonent stats: ';
100 foreach ($fields as $field) print "<a href='#$field'>$field</a> ";
103 foreach ($charts as $chart) {
104 switch ($chart['type']) {
106 $result = trend($chart['query']);
107 print "<h2>{$chart['title']} (last: {$result['last']})</h2>";
108 print "<p><img src='{$result['url']}' /></p>";
112 $result = compare($chart['query']);
113 print "<h2>{$chart['title']}</h2>";
114 print "<p><img src='{$result['url']}' /> <img src='{$result['last']}' /></p>";
122 if (!$_GET['current']) {
130 mysql_query("CREATE TEMPORARY TABLE latest_ids SELECT MAX(id) id FROM stats WHERE YEAR(time) = $year AND MONTH(time) = $month GROUP BY hash");
131 mysql_query('CREATE INDEX latest_ids_id ON latest_ids (id)');
132 mysql_query('CREATE TEMPORARY TABLE latest_stats SELECT * FROM stats WHERE id IN (SELECT * FROM latest_ids)');
133 foreach ($fields as $field) {
134 mysql_query("CREATE INDEX latest_stats_$field ON latest_stats (`$field`)");
137 foreach ($fields as $field) {
138 print "<h2 id='$field'>$field</h2>";
140 $clauses = array("( `$field` IS NOT NULL )");
141 $clauses[] = "( `$field` > 0 )";
142 $clause = implode(' AND ', $clauses);
144 $totals = mysql_query("SELECT COUNT(*) count FROM latest_stats WHERE $clause");
145 while ($total = mysql_fetch_object($totals)) {
146 print "Total: {$total->count}<p>";
149 $tops = mysql_query("SELECT `$field` field, COUNT(*) count FROM latest_stats WHERE $clause GROUP BY field ORDER BY count DESC LIMIT 5");
150 print '<p>five most popular counts: ';
151 $first = $second = NULL;
152 while ($top = mysql_fetch_object($tops)) {
153 if ($first !== NULL and $second === NULL) {
154 $second = $top->field
;
156 if ($first === NULL) {
157 $first = $top->field
;
159 print "{$top->field} " . strtolower($field) . "s ({$top->count} installs), ";
163 $stat = mysql_fetch_object(mysql_query("SELECT MAX(`$field`) max, ROUND(AVG(`$field`)) avg FROM latest_stats"));
164 print "<h3>$field with all counts – max: {$stat->max}, avg: {$stat->avg}</h3>";
165 print '<table><tr><th colspan="4">range</th><th>count</th></tr>';
167 $pieces = $stat->max
> 100 ?
100 : $stat->max
;
168 for ($i = 1; $i <= $pieces; $i++
) {
170 $high = round($i * $stat->max
/ $pieces);
171 $count = mysql_fetch_object(mysql_query("SELECT COUNT(*) count FROM latest_stats WHERE `$field` BETWEEN $low AND $high AND $clause"));
173 print "<tr style='text-align: right'><td>$low</td><td>–</td><td>$high</td><td>(" . $i * 100 / $pieces . "%)</td><td>$count->count</td></tr>";
178 $stat = mysql_fetch_object(mysql_query("SELECT MAX(`$field`) max, ROUND(AVG(`$field`)) avg FROM latest_stats WHERE `$field` != $first"));
179 print "<h3>$field sans the $first count – max: {$stat->max}, avg: {$stat->avg}</h3>";
180 print '<table><tr><th colspan="4">range</th><th>count</th></tr>';
182 $pieces = $stat->max
> 100 ?
100 : $stat->max
;
183 for ($i = 1; $i <= $pieces; $i++
) {
185 $high = round($i * $stat->max
/ $pieces);
186 $count = mysql_fetch_object(mysql_query("SELECT COUNT(*) count FROM latest_stats WHERE `$field` BETWEEN $low AND $high AND `$field` != $first"));
188 print "<tr style='text-align: right'><td>$low</td><td>–</td><td>$high</td><td>(" . $i * 100 / $pieces . "%)</td><td>$count->count</td></tr>";
193 $stat = mysql_fetch_object(mysql_query("SELECT MAX(`$field`) max, ROUND(AVG(`$field`)) avg FROM latest_stats WHERE `$field` != $first AND `$field` != $second"));
194 print "<h3>$field sans the $first and $second counts – max: {$stat->max}, avg: {$stat->avg}</h3>";
195 print '<table><tr><th colspan="4">range</th><th>count</th></tr>';
197 $pieces = $stat->max
> 100 ?
100 : $stat->max
;
198 for ($i = 1; $i <= $pieces; $i++
) {
200 $high = round($i * $stat->max
/ $pieces);
201 $count = mysql_fetch_object(mysql_query("SELECT COUNT(*) count FROM latest_stats WHERE `$field` BETWEEN $low AND $high AND `$field` != $first AND `$field` != $second"));
203 print "<tr style='text-align: right'><td>$low</td><td>–</td><td>$high</td><td>(" . $i * 100 / $pieces . "%)</td><td>$count->count</td></tr>";