[civicrm-core.git] / tools / sites / stats / stats.php

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
  <title>CiviCRM usage statistics</title>
  <meta http-equiv="Content-type" content="text/html; charset=utf-8" />
  <meta http-equiv="Content-Language" content="en" />
  <meta name="Author" content="Piotr Szotkowski" />
</head>
<body>
<h1>CiviCRM usage statistics</h1>
<?php
/*
 +--------------------------------------------------------------------+
 | CiviCRM version 4.7                                                |
 +--------------------------------------------------------------------+
 | Copyright CiviCRM LLC (c) 2004-2015                                |
 +--------------------------------------------------------------------+
 | This file is a part of CiviCRM.                                    |
 |                                                                    |
 | CiviCRM is free software; you can copy, modify, and distribute it  |
 | under the terms of the GNU Affero General Public License           |
 | Version 3, 19 November 2007.                                       |
 |                                                                    |
 | CiviCRM is distributed in the hope that it will be useful, but     |
 | WITHOUT ANY WARRANTY; without even the implied warranty of         |
 | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.               |
 | See the GNU Affero General Public License for more details.        |
 |                                                                    |
 | You should have received a copy of the GNU Affero General Public   |
 | License along with this program; if not, contact CiviCRM LLC       |
 | at info[AT]civicrm[DOT]org. If you have questions about the        |
 | GNU Affero General Public License or the licensing of CiviCRM,     |
 | see the CiviCRM license FAQ at http://civicrm.org/licensing        |
 +--------------------------------------------------------------------+
*/

/**
 *
 * @package CRM
 * @copyright CiviCRM LLC (c) 2004-2015
 * $Id$
 *
 */

$user = $pass = FALSE;
require_once 'config.php';
mysql_connect('localhost', $user, $pass);
mysql_select_db('stats');

require_once 'graphs.php';

$charts = array(
  array('title' => 'Distinct installations',
    'query' => 'SELECT COUNT(DISTINCT hash) data, YEAR(time) year, MONTH(time) month FROM stats GROUP BY year, month ORDER BY year, month',
    'type' => 'trend',
  ),
  array('title' => 'UF usage',
    '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',
    'type' => 'compare',
  ),
  array('title' => 'CiviCRM versions',
    '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',
    'type' => 'compare',
  ),
  array('title' => 'Drupal versions',
    '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',
    'type' => 'compare',
  ),
  array('title' => 'Joomla versions',
    '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',
    'type' => 'compare',
  ),
  array('title' => 'MySQL versions',
    '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',
    'type' => 'compare',
  ),
  array('title' => 'PHP versions',
    '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',
    'type' => 'compare',
  ),
  array('title' => 'Default languages',
    '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',
    'type' => 'compare',
  ),
);

switch ($_GET['current']) {
  case FALSE:
    print '<p><a href="?current=1">include partial data for current month</a></p>';
    break;

  case TRUE:
    print '<p><a href="?">drop partial data for current month</a></p>';
    break;
}

$fields = array('Activity', 'Case', 'Contact', 'Contribution', 'ContributionPage', 'ContributionProduct', 'Discount', 'Event', 'Friend', 'Grant', 'Mailing', 'Membership', 'MembershipBlock', 'Participant', 'Pledge', 'PledgeBlock', 'PriceSetEntity', 'Relationship', 'UFGroup', 'Widget');

print '<p>jump to comonent stats: ';
foreach ($fields as $field) print "<a href='#$field'>$field</a> ";
print '</p>';

foreach ($charts as $chart) {
  switch ($chart['type']) {
    case 'trend':
      $result = trend($chart['query']);
      print "<h2>{$chart['title']} (last: {$result['last']})</h2>";
      print "<p><img src='{$result['url']}' /></p>";
      break;

    case 'compare':
      $result = compare($chart['query']);
      print "<h2>{$chart['title']}</h2>";
      print "<p><img src='{$result['url']}' /> <img src='{$result['last']}' /></p>";
      break;
  }
}

$year = date('Y');
$month = date('n');

if (!$_GET['current']) {
  $month--;
  if ($month == 0) {
    $year--;
    $month = 12;
  }
}

mysql_query("CREATE TEMPORARY TABLE latest_ids SELECT MAX(id) id FROM stats WHERE YEAR(time) = $year AND MONTH(time) = $month GROUP BY hash");
mysql_query('CREATE INDEX latest_ids_id ON latest_ids (id)');
mysql_query('CREATE TEMPORARY TABLE latest_stats SELECT * FROM stats WHERE id IN (SELECT * FROM latest_ids)');
foreach ($fields as $field) {
  mysql_query("CREATE INDEX latest_stats_$field ON latest_stats (`$field`)");
}

foreach ($fields as $field) {
  print "<h2 id='$field'>$field</h2>";

  $clauses   = array("( `$field` IS NOT NULL )");
  $clauses[] = "( `$field` > 0 )";
  $clause    = implode(' AND ', $clauses);

  $totals = mysql_query("SELECT COUNT(*) count FROM latest_stats WHERE $clause");
  while ($total = mysql_fetch_object($totals)) {
    print "Total: {$total->count}<p>";
  }

  $tops = mysql_query("SELECT `$field` field, COUNT(*) count FROM latest_stats WHERE $clause GROUP BY field ORDER BY count DESC LIMIT 5");
  print '<p>five most popular counts: ';
  $first = $second = NULL;
  while ($top = mysql_fetch_object($tops)) {
    if ($first !== NULL and $second === NULL) {
      $second = $top->field;
    }
    if ($first === NULL) {
      $first = $top->field;
    }
    print "{$top->field}&nbsp;" . strtolower($field) . "s&nbsp;({$top->count}&nbsp;installs), ";
  }
  print '</p>';

  $stat = mysql_fetch_object(mysql_query("SELECT MAX(`$field`) max, ROUND(AVG(`$field`)) avg FROM latest_stats"));
  print "<h3>$field with all counts – max: {$stat->max}, avg: {$stat->avg}</h3>";
  print '<table><tr><th colspan="4">range</th><th>count</th></tr>';
  $high = -1;
  $pieces = $stat->max > 100 ? 100 : $stat->max;
  for ($i = 1; $i <= $pieces; $i++) {
    $low   = $high + 1;
    $high  = round($i * $stat->max / $pieces);
    $count = mysql_fetch_object(mysql_query("SELECT COUNT(*) count FROM latest_stats WHERE `$field` BETWEEN $low AND $high AND $clause"));
    if ($count->count) {
      print "<tr style='text-align: right'><td>$low</td><td>–</td><td>$high</td><td>(" . $i * 100 / $pieces . "%)</td><td>$count->count</td></tr>";
    }
  }
  print '</table>';

  $stat = mysql_fetch_object(mysql_query("SELECT MAX(`$field`) max, ROUND(AVG(`$field`)) avg FROM latest_stats WHERE `$field` != $first"));
  print "<h3>$field sans the $first count – max: {$stat->max}, avg: {$stat->avg}</h3>";
  print '<table><tr><th colspan="4">range</th><th>count</th></tr>';
  $high = -1;
  $pieces = $stat->max > 100 ? 100 : $stat->max;
  for ($i = 1; $i <= $pieces; $i++) {
    $low   = $high + 1;
    $high  = round($i * $stat->max / $pieces);
    $count = mysql_fetch_object(mysql_query("SELECT COUNT(*) count FROM latest_stats WHERE `$field` BETWEEN $low AND $high AND `$field` != $first"));
    if ($count->count) {
      print "<tr style='text-align: right'><td>$low</td><td>–</td><td>$high</td><td>(" . $i * 100 / $pieces . "%)</td><td>$count->count</td></tr>";
    }
  }
  print '</table>';

  $stat = mysql_fetch_object(mysql_query("SELECT MAX(`$field`) max, ROUND(AVG(`$field`)) avg FROM latest_stats WHERE `$field` != $first AND `$field` != $second"));
  print "<h3>$field sans the $first and $second counts – max: {$stat->max}, avg: {$stat->avg}</h3>";
  print '<table><tr><th colspan="4">range</th><th>count</th></tr>';
  $high = -1;
  $pieces = $stat->max > 100 ? 100 : $stat->max;
  for ($i = 1; $i <= $pieces; $i++) {
    $low   = $high + 1;
    $high  = round($i * $stat->max / $pieces);
    $count = mysql_fetch_object(mysql_query("SELECT COUNT(*) count FROM latest_stats WHERE `$field` BETWEEN $low AND $high AND `$field` != $first AND `$field` != $second"));
    if ($count->count) {
      print "<tr style='text-align: right'><td>$low</td><td>–</td><td>$high</td><td>(" . $i * 100 / $pieces . "%)</td><td>$count->count</td></tr>";
    }
  }
  print '</table>';
}

?>
</body>
</html>
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	+--------------------------------------------------------------------+
81621fee	14	\| CiviCRM version 4.7 \|
6a488035	15	+--------------------------------------------------------------------+
e7112fa7	16	\| Copyright CiviCRM LLC (c) 2004-2015 \|
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
e7112fa7	40	* @copyright CiviCRM LLC (c) 2004-2015
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