Commit | Line | Data |
---|---|---|
6a488035 TO |
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"> | |
3 | <head> | |
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" /> | |
8 | </head> | |
9 | <body> | |
10 | <h1>CiviCRM usage statistics</h1> | |
11 | <?php | |
12 | /* | |
13 | +--------------------------------------------------------------------+ | |
39de6fd5 | 14 | | CiviCRM version 4.6 | |
6a488035 | 15 | +--------------------------------------------------------------------+ |
34cd78e1 | 16 | | Copyright CiviCRM LLC (c) 2004-2014 | |
6a488035 TO |
17 | +--------------------------------------------------------------------+ |
18 | | This file is a part of CiviCRM. | | |
19 | | | | |
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. | | |
23 | | | | |
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. | | |
28 | | | | |
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 | +--------------------------------------------------------------------+ | |
35 | */ | |
36 | ||
37 | /** | |
38 | * | |
39 | * @package CRM | |
34cd78e1 | 40 | * @copyright CiviCRM LLC (c) 2004-2014 |
6a488035 TO |
41 | * $Id$ |
42 | * | |
43 | */ | |
44 | ||
45 | $user = $pass = FALSE; | |
46 | require_once 'config.php'; | |
47 | mysql_connect('localhost', $user, $pass); | |
48 | mysql_select_db('stats'); | |
49 | ||
50 | require_once 'graphs.php'; | |
51 | ||
52 | $charts = array( | |
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', | |
55 | 'type' => 'trend', | |
56 | ), | |
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', | |
59 | 'type' => 'compare', | |
60 | ), | |
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', | |
63 | 'type' => 'compare', | |
64 | ), | |
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', | |
67 | 'type' => 'compare', | |
68 | ), | |
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', | |
71 | 'type' => 'compare', | |
72 | ), | |
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', | |
75 | 'type' => 'compare', | |
76 | ), | |
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', | |
79 | 'type' => 'compare', | |
80 | ), | |
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', | |
83 | 'type' => 'compare', | |
84 | ), | |
85 | ); | |
86 | ||
87 | switch ($_GET['current']) { | |
88 | case FALSE: | |
89 | print '<p><a href="?current=1">include partial data for current month</a></p>'; | |
90 | break; | |
91 | ||
92 | case TRUE: | |
93 | print '<p><a href="?">drop partial data for current month</a></p>'; | |
94 | break; | |
95 | } | |
96 | ||
97 | $fields = array('Activity', 'Case', 'Contact', 'Contribution', 'ContributionPage', 'ContributionProduct', 'Discount', 'Event', 'Friend', 'Grant', 'Mailing', 'Membership', 'MembershipBlock', 'Participant', 'Pledge', 'PledgeBlock', 'PriceSetEntity', 'Relationship', 'UFGroup', 'Widget'); | |
98 | ||
99 | print '<p>jump to comonent stats: '; | |
100 | foreach ($fields as $field) print "<a href='#$field'>$field</a> "; | |
101 | print '</p>'; | |
102 | ||
103 | foreach ($charts as $chart) { | |
104 | switch ($chart['type']) { | |
105 | case 'trend': | |
106 | $result = trend($chart['query']); | |
107 | print "<h2>{$chart['title']} (last: {$result['last']})</h2>"; | |
108 | print "<p><img src='{$result['url']}' /></p>"; | |
109 | break; | |
110 | ||
111 | case 'compare': | |
112 | $result = compare($chart['query']); | |
113 | print "<h2>{$chart['title']}</h2>"; | |
114 | print "<p><img src='{$result['url']}' /> <img src='{$result['last']}' /></p>"; | |
115 | break; | |
116 | } | |
117 | } | |
118 | ||
119 | $year = date('Y'); | |
120 | $month = date('n'); | |
121 | ||
122 | if (!$_GET['current']) { | |
123 | $month--; | |
124 | if ($month == 0) { | |
125 | $year--; | |
126 | $month = 12; | |
127 | } | |
128 | } | |
129 | ||
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`)"); | |
135 | } | |
136 | ||
137 | foreach ($fields as $field) { | |
138 | print "<h2 id='$field'>$field</h2>"; | |
139 | ||
140 | $clauses = array("( `$field` IS NOT NULL )"); | |
141 | $clauses[] = "( `$field` > 0 )"; | |
142 | $clause = implode(' AND ', $clauses); | |
143 | ||
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>"; | |
147 | } | |
148 | ||
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; | |
155 | } | |
156 | if ($first === NULL) { | |
157 | $first = $top->field; | |
158 | } | |
159 | print "{$top->field} " . strtolower($field) . "s ({$top->count} installs), "; | |
160 | } | |
161 | print '</p>'; | |
162 | ||
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>'; | |
166 | $high = -1; | |
167 | $pieces = $stat->max > 100 ? 100 : $stat->max; | |
168 | for ($i = 1; $i <= $pieces; $i++) { | |
169 | $low = $high + 1; | |
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")); | |
172 | if ($count->count) { | |
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>"; | |
174 | } | |
175 | } | |
176 | print '</table>'; | |
177 | ||
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>'; | |
181 | $high = -1; | |
182 | $pieces = $stat->max > 100 ? 100 : $stat->max; | |
183 | for ($i = 1; $i <= $pieces; $i++) { | |
184 | $low = $high + 1; | |
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")); | |
187 | if ($count->count) { | |
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>"; | |
189 | } | |
190 | } | |
191 | print '</table>'; | |
192 | ||
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>'; | |
196 | $high = -1; | |
197 | $pieces = $stat->max > 100 ? 100 : $stat->max; | |
198 | for ($i = 1; $i <= $pieces; $i++) { | |
199 | $low = $high + 1; | |
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")); | |
202 | if ($count->count) { | |
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>"; | |
204 | } | |
205 | } | |
206 | print '</table>'; | |
207 | } | |
208 | ||
209 | ?> | |
210 | </body> | |
211 | </html> | |
212 |