Статистика. Добавленные документы, комментарии и регистрации по месяцам
<?php
global $user;
if (!$user->uid or $user->uid!=1 )
{
print '<p>';
print 'No access';
}
else
{
// Количество добавленных документов и комментариев в каждом месяце
$query="SELECT Year, Month, sum(pcs) from ((SELECT FROM_UNIXTIME(node.created,'%Y') as Year, FROM_UNIXTIME(node.created,'%m') as Month, count(node.created) as pcs FROM node group by 1,2) union (select FROM_UNIXTIME(comments.timestamp,'%Y'),FROM_UNIXTIME(comments.timestamp,'%m'), count(comments.timestamp) FROM comments group by 1,2)) as sii group by 1,2 order by 1 asc, 2 asc";
print '<h2> Документы и комментарии </h2>';
$result=mysql_query($query);
print '<table><tr><td>';
print 'Месяц';
print '</td><td>';
print 'Год';
print '</td><td>';
print 'Количество';
print '</td></tr>';
while($row = mysql_fetch_array($result)){
print '<tr><td>';
print $row[1];
print '</td><td>';
print $row[0];
print '</td><td>';
print $row[2];
print '</td></tr>';}
print '</table>';
// Модули Guestbook и Privatemsg
$query="SELECT Year, Month, sum(pcs) from ((SELECT FROM_UNIXTIME(privatemsg.timestamp,'%Y') as Year, FROM_UNIXTIME(privatemsg.timestamp,'%m') as Month, count(privatemsg.timestamp) as pcs FROM privatemsg group by 1,2) union (select FROM_UNIXTIME(guestbook.created,'%Y'),FROM_UNIXTIME(guestbook.created,'%m'), count(guestbook.created) FROM guestbook group by 1,2)) as sii group by 1,2 order by 1 asc, 2 asc";
print '<h2> Модули Guestbook и Privatemsg </h2>';
$result=mysql_query($query);
print '<table><tr><td>';
print 'Месяц';
print '</td><td>';
print 'Год';
print '</td><td>';
print 'Количество';
print '</td></tr>';
while($row = mysql_fetch_array($result)){
print '<tr><td>';
print $row[1];
print '</td><td>';
print $row[0];
print '</td><td>';
print $row[2];
print '</td></tr>';}
print '</table>';
// Количество зарегистрировавшихся пользователей в каждом месяце
$query="SELECT FROM_UNIXTIME(users.created,'%Y') as Year, FROM_UNIXTIME(users.created,'%m') as Month, count(users.created) as pcs FROM users WHERE users.access >0 group by 1,2 order by 1 asc, 2 asc";
print '<h2> Регистрация пользователей </h2>';
$result=mysql_query($query);
print '<table><tr><td>';
print 'Месяц';
print '</td><td>';
print 'Год';
print '</td><td>';
print 'Количество';
print '</td></tr>';
while($row = mysql_fetch_array($result)){
print '<tr><td>';
print $row[1];
print '</td><td>';
print $row[0];
print '</td><td>';
print $row[2];
print '</td></tr>';}
print '</table>';
}
?>
02.09.2007
Comments
Правильная версия для Drupal :)
<?php
global $user;
if (!$user->uid || $user->uid != 1) {
drupal_access_denied();
}
else {
function _get_table($sql, $caption = NULL) {
$header = array (
t('Месяц'),
t('Год'),
t('Количество'),
);
$result = db_query($sql);
$rows = array ();
while ($row = db_fetch_object($result)) {
$rows[] = array (
$row->month,
$row->year,
$row->pcs,
);
}
return theme('table', $header, $rows, NULL, $caption);
}
$output = '';
// Количество добавленных документов и комментариев в каждом месяце
$sql = '
SELECT
year,
month,
SUM(pcs) AS pcs
FROM
(
(
SELECT
FROM_UNIXTIME(n.created, \'%Y\') AS year,
FROM_UNIXTIME(n.created, \'%m\') AS month,
COUNT(n.created) AS pcs
FROM
{node} n
GROUP BY
year,
month
)
) AS sii
GROUP BY
year,
month
ORDER BY
year DESC,
month DESC
';
$output .= _get_table($sql, t('Документы'));
$sql = '
SELECT
year,
month,
SUM(pcs) AS pcs
FROM
(
(
SELECT
FROM_UNIXTIME(c.timestamp, \'%Y\') AS year,
FROM_UNIXTIME(c.timestamp, \'%m\') AS month,
COUNT(c.timestamp) AS pcs
FROM
{comments} c
GROUP BY
year,
month
)
) AS sii
GROUP BY
year,
month
ORDER BY
year DESC,
month DESC
';
$output .= _get_table($sql, t('Комментарии'));
// Модуль Guestbook
if (module_exists('guestbook')) {
$sql = '
SELECT
year,
month,
SUM(pcs) AS pcs
FROM
(
(
SELECT
FROM_UNIXTIME(g.created, \'%Y\') AS year,
FROM_UNIXTIME(g.created, \'%m\') AS month,
COUNT(g.created) AS pcs
FROM
{guestbook} g
GROUP BY
year,
month
)
) AS sii
GROUP BY
year,
month
ORDER BY
year DESC,
month DESC
';
$output .= _get_table($sql, t('Guestbook'));
}
// Модуль Privatemsg
if (module_exists('privatemsg')) {
$sql = '
SELECT
year,
month,
SUM(pcs) AS pcs
FROM
(
(
SELECT
FROM_UNIXTIME(p.timestamp, \'%Y\') AS year,
FROM_UNIXTIME(p.timestamp, \'%m\') AS month,
COUNT(p.timestamp) AS pcs
FROM
{privatemsg} p
GROUP BY
year,
month
)
) AS sii
GROUP BY
year,
month
ORDER BY
year DESC,
month DESC
';
$output .= _get_table($sql, t('Privatemsg'));
}
// Количество зарегистрировавшихся пользователей в каждом месяце
$sql = '
SELECT
FROM_UNIXTIME(u.created, \'%Y\') AS year,
FROM_UNIXTIME(u.created, \'%m\') AS month,
COUNT(u.created) AS pcs
FROM
{users} u
WHERE
u.access > 0
GROUP BY
year,
month
ORDER BY
year DESC,
month DESC
';
$output .= _get_table($sql, t('Регистрация пользователей'));
print $output;
}
?>
Post new comment