0, 'users' => 0, 'pictures' => 0);
foreach ($data as $period => $stats){
foreach($stats as $param => $value){
$max[$param] = max($max[$param], $value);
}
}
$bars = array();
foreach ($data as $period => $stats){
foreach($stats as $param => $value){
$bars[$period][$param] = floor(($value / $max[$param]) * 100);
if ($value) $bars[$period][$param] = max($bars[$period][$param], 1);
}
}
return $bars;
}
pageheader('Statistik');
starttable('100%');
echo <<< EOT
| Datum |
Kommentare |
Bilder |
Neue Mitglieder |
EOT;
$years = array();
$sql = "SELECT YEAR(msg_date) AS period, COUNT(*) AS number
FROM {$CONFIG['TABLE_COMMENTS']}
GROUP BY period
ORDER BY period DESC";
$result = cpg_db_query($sql);
while ($row = mysql_fetch_assoc($result)) $years[$row['period']]['comments'] = $row['number'];
mysql_free_result($result);
$sql = "SELECT YEAR(FROM_UNIXTIME(ctime)) AS period, COUNT(*) AS number
FROM {$CONFIG['TABLE_PICTURES']}
GROUP BY period
ORDER BY period DESC";
$result = cpg_db_query($sql);
while ($row = mysql_fetch_assoc($result)) $years[$row['period']]['pictures'] = $row['number'];
mysql_free_result($result);
$sql = "SELECT YEAR(FROM_UNIXTIME({$cpg_udb->field['regdate']})) AS period, COUNT(*) AS number
FROM {$cpg_udb->usertable}
GROUP BY period
ORDER BY period DESC";
$result = cpg_db_query($sql);
while ($row = mysql_fetch_assoc($result)) $years[$row['period']]['users'] = $row['number'];
mysql_free_result($result);
krsort($years);
$ybars = getbars($years);
foreach ($years as $year => $stats){
makezeros($stats);
makezeros($ybars[$year]);
if (isset($_GET['year']) && $_GET['year'] == $year){
$type = 'collapse';
$y = '';
} else {
$type = 'expand';
$y = "?year=$year";
}
echo <<< EOT
 $year |
 {$stats['comments']} |
{$stats['pictures']} |
{$stats['users']} |
EOT;
if (isset($_GET['year']) && $_GET['year'] == $year){
$months = array();
$sql = "SELECT MONTH(msg_date) AS period, COUNT(*) AS number
FROM {$CONFIG['TABLE_COMMENTS']}
WHERE YEAR(msg_date) = $year
GROUP BY period
ORDER BY period DESC";
$result = cpg_db_query($sql);
while ($row = mysql_fetch_assoc($result)) $months[$row['period']]['comments'] = $row['number'];
mysql_free_result($result);
$sql = "SELECT MONTH(FROM_UNIXTIME(ctime)) AS period, COUNT(*) AS number
FROM {$CONFIG['TABLE_PICTURES']}
WHERE YEAR(FROM_UNIXTIME(ctime)) = $year
GROUP BY period
ORDER BY period DESC";
$result = cpg_db_query($sql);
while ($row = mysql_fetch_assoc($result)) $months[$row['period']]['pictures'] = $row['number'];
mysql_free_result($result);
$sql = "SELECT MONTH(FROM_UNIXTIME({$cpg_udb->field['regdate']})) AS period, COUNT(*) AS number
FROM {$cpg_udb->usertable}
WHERE YEAR(FROM_UNIXTIME({$cpg_udb->field['regdate']})) = $year
GROUP BY period
ORDER BY period DESC";
$result = cpg_db_query($sql);
while ($row = mysql_fetch_assoc($result)) $months[$row['period']]['users'] = $row['number'];
mysql_free_result($result);
krsort($months);
$mbars = getbars($months);
foreach ($months as $month => $stats){
makezeros($stats);
makezeros($mbars[$month]);
$monthname = date('F', strtotime("$month/01/$year"));
if (isset($_GET['month']) && $_GET['month'] == $month){
$type = 'collapse';
$m = '';
} else {
$type = 'expand';
$m = "&month=$month";
}
echo <<< EOT
| $monthname |
{$stats['comments']} |
{$stats['pictures']} |
{$stats['users']} |
EOT;
if (isset($_GET['month']) && $_GET['month'] == $month){
$days = array();
$sql = "SELECT DAY(msg_date) AS period, COUNT(*) AS number
FROM {$CONFIG['TABLE_COMMENTS']}
WHERE YEAR(msg_date) = $year
AND MONTH(msg_date) = $month
GROUP BY period
ORDER BY period DESC";
$result = cpg_db_query($sql);
while ($row = mysql_fetch_assoc($result)) $days[$row['period']]['comments'] = $row['number'];
mysql_free_result($result);
$sql = "SELECT DAY(FROM_UNIXTIME(ctime)) AS period, COUNT(*) AS number
FROM {$CONFIG['TABLE_PICTURES']}
WHERE YEAR(FROM_UNIXTIME(ctime)) = $year
AND MONTH(FROM_UNIXTIME(ctime)) = $month
GROUP BY period
ORDER BY period DESC";
$result = cpg_db_query($sql);
while ($row = mysql_fetch_assoc($result)) $days[$row['period']]['pictures'] = $row['number'];
mysql_free_result($result);
$sql = "SELECT DAY(FROM_UNIXTIME({$cpg_udb->field['regdate']})) AS period, COUNT(*) AS number
FROM {$cpg_udb->usertable}
WHERE YEAR(FROM_UNIXTIME({$cpg_udb->field['regdate']})) = $year
AND MONTH(FROM_UNIXTIME({$cpg_udb->field['regdate']})) = $month
GROUP BY period
ORDER BY period DESC";
$result = cpg_db_query($sql);
while ($row = mysql_fetch_assoc($result)) $days[$row['period']]['users'] = $row['number'];
mysql_free_result($result);
krsort($days);
$dbars = getbars($days);
foreach ($days as $day => $stats){
$dayname = date('Y-m-d', strtotime("$month/$day/$year"));
makezeros($stats);
makezeros($dbars[$day]);
echo <<< EOT
| $dayname |
{$stats['comments']} |
{$stats['pictures']} |
{$stats['users']} |
EOT;
}
}
}
}
}
//Activity Graph Function
function activityGraph($cellName, $tableName, $title, $barTile, $notCtime=false)
{
starttable(643, $title, 1);
$query = "SELECT $cellName FROM $tableName";
$result = cpg_db_query($query);
$rowset = cpg_db_fetch_rowset($result);
mysql_free_result($result);
$uploadTimes = array();
$total = 0;
foreach($rowset as $row)
{
if($notCtime)
{
//Get hours from date/time string
$split1 = explode(' ', $row[0]);
$split2 = explode(':', $split1[1]);
$hour = $split2[0];
}
else
{
$hour = strftime("%H",$row[0]);
}
switch($hour)
{
case '00':
$index = 0;
break;
case '01':
$index = 1;
break;
case '02':
$index = 2;
break;
case '03':
$index = 3;
break;
case '04':
$index = 4;
break;
case '05':
$index = 5;
break;
case '06':
$index = 6;
break;
case '07':
$index = 7;
break;
case '08':
$index = 8;
break;
case '09':
$index = 9;
break;
default:
$index = $hour;
}
$uploadTimes[$index] = @$uploadTimes[$index] + 1;
$total++;
}
ksort($uploadTimes, SORT_NUMERIC);
print('| Uhrzeit (GMT+2'.$CONFIG['time_offset'].') |
');
print(' |
');
print(' |
');
print(' |
');
print(' | ');
for($i = '0'; $i <= '23'; $i++)
{
if(isset($uploadTimes[$i]))
{
print(''.round($uploadTimes[$i]/$total*'100',0).'%
 | ');
}
else
{
print(' | ');
}
}
print(' |
');
print(' |
');
print(' |
');
print(' |
');
print(' | ');
for($i = '0'; $i <= '23'; $i++)
{
#if(isset($uploadTimes[$i]))
#{
# print(''.$i.' ('.round($uploadTimes[$i]/$total*'200',0).'%) | ');
#}
#else
#{
print(''.$i.' | ');
#}
}
print(' |
');
endtable();
}
//Start Most Viewed
starttable("99%", "Am meisten gesehene Bilder", 1);
$query = "SELECT pid, hits FROM {$CONFIG['TABLE_PICTURES']}";
$result = cpg_db_query($query);
$rowset = cpg_db_fetch_rowset($result);
mysql_free_result($result);
foreach($rowset as $row)
{
$mostViewed[$row[0]] = $row[1];
}
arsort($mostViewed, SORT_NUMERIC);
echo <<
EOT;
foreach(array_slice(array_keys($mostViewed),0,4) as $image)
{
$query = "SELECT filepath, filename, owner_name, owner_id FROM {$CONFIG['TABLE_PICTURES']} as p WHERE p.pid = '$image' ";
$result = cpg_db_query($query);
$rowset = cpg_db_fetch_rowset($result);
mysql_free_result($result);
$file = $rowset[0];
echo <<
EOT;
print("![\"\"](\"albums/")
{$file[2]}{$mostViewed[$image]} x angesehen");
echo <<
|
EOT;
}
endtable();
//End Most Viewed
//Start Album Most Viewed
starttable("99%", "Am meisten besuchte Alben", 1);
$query = "SELECT aid, hits FROM {$CONFIG['TABLE_PICTURES']}";
$result = cpg_db_query($query);
$rowset = cpg_db_fetch_rowset($result);
mysql_free_result($result);
foreach($rowset as $row)
{
$mostViewedAlbum[$row[0]] = @$mostViewedAlbum[$row[0]] + $row[1];
}
arsort($mostViewedAlbum, SORT_NUMERIC);
echo <<
EOT;
foreach(array_slice(array_keys($mostViewedAlbum),0,4) as $album)
{
$query = "SELECT filepath, filename, owner_name, owner_id FROM {$CONFIG['TABLE_PICTURES']} as p WHERE p.aid = '$album' ";
$result = cpg_db_query($query);
$rowset = cpg_db_fetch_rowset($result);
mysql_free_result($result);
$file = $rowset[0];
echo <<
EOT;
print("
{$file[2]}{$mostViewedAlbum[$album]} x angesehen");
echo <<
|
EOT;
}
endtable();
//End Album Most Viewed
//Start User Most Viewed
starttable("99%", "Mitglieder mit den meisten Besuchen in Ihren Alben", 1);
$query = "SELECT owner_id, hits FROM {$CONFIG['TABLE_PICTURES']}";
$result = cpg_db_query($query);
$rowset = cpg_db_fetch_rowset($result);
mysql_free_result($result);
foreach($rowset as $row)
{
$mostViewedUser[$row[0]] = @$mostViewedUser[$row[0]] + $row[1];
}
arsort($mostViewedUser, SORT_NUMERIC);
echo <<
EOT;
foreach(array_slice(array_keys($mostViewedUser),0,4) as $user)
{
$query = "SELECT filepath, filename, owner_name, owner_id FROM {$CONFIG['TABLE_PICTURES']} as p WHERE p.owner_id = '$user' ";
$result = cpg_db_query($query);
$rowset = cpg_db_fetch_rowset($result);
mysql_free_result($result);
$file = $rowset[0];
echo <<
EOT;
$cat = explode('/', $file[0]);
print("
{$file[2]}{$mostViewedUser[$user]} x angesehen");
echo <<
|
EOT;
}
endtable();
//End User Most Viewed
//Start Most Commented
starttable("99%", "Am meisten kommentierte Bilder", 1);
$query = "SELECT pid FROM {$CONFIG['TABLE_COMMENTS']}";
$result = cpg_db_query($query);
$rowset = cpg_db_fetch_rowset($result);
mysql_free_result($result);
foreach($rowset as $row)
{
$index = $row[0];
$mostCommented[$index] = @$mostCommented[$index] + 1;
}
arsort($mostCommented, SORT_NUMERIC);
echo <<
EOT;
foreach(array_slice(array_keys($mostCommented),0,4) as $image)
{
$query = "SELECT filepath, filename, owner_name, owner_id FROM {$CONFIG['TABLE_PICTURES']} as p WHERE p.pid = '$image' ";
$result = cpg_db_query($query);
$rowset = cpg_db_fetch_rowset($result);
mysql_free_result($result);
$file = $rowset[0];
echo <<
EOT;
print("
{$file[2]}{$mostCommented[$image]} Kommentare");
echo <<
|
EOT;
}
endtable();
//End Most Commented
//Start Album Most Commented
starttable("99%", "Am meisten kommentierte Alben", 1);
$query = "SELECT aid FROM {$CONFIG['TABLE_COMMENTS']} as c, {$CONFIG['TABLE_PICTURES']} as p WHERE c.pid = p.pid";
$result = cpg_db_query($query);
$rowset = cpg_db_fetch_rowset($result);
mysql_free_result($result);
foreach($rowset as $row)
{
$index = $row[0];
$mostCommentedAlbum[$index] = @$mostCommentedAlbum[$index] + 1;
}
arsort($mostCommentedAlbum, SORT_NUMERIC);
echo <<
EOT;
foreach(array_slice(array_keys($mostCommentedAlbum),0,4) as $album)
{
$query = "SELECT filepath, filename, owner_name, owner_id, pid FROM {$CONFIG['TABLE_PICTURES']} as p WHERE p.aid = '$album' ";
$result = cpg_db_query($query);
$rowset = cpg_db_fetch_rowset($result);
mysql_free_result($result);
$file = $rowset[0];
echo <<
EOT;
print("
{$file[2]}{$mostCommentedAlbum[$album]} Kommentare");
echo <<
|
EOT;
}
endtable();
//End Album Most Commented
//Start User Most Commenting
starttable("99%", "Top-Kommentatoren", 1);
$query = "SELECT author_id FROM {$CONFIG['TABLE_COMMENTS']} as c, {$CONFIG['TABLE_PICTURES']} as p WHERE c.pid = p.pid";
$result = cpg_db_query($query);
$rowset = cpg_db_fetch_rowset($result);
mysql_free_result($result);
foreach($rowset as $row)
{
$index = $row[0];
$mostCommentingUser[$index] = @$mostCommentingUser[$index] + 1;
}
arsort($mostCommentingUser, SORT_NUMERIC);
echo <<
EOT;
foreach(array_slice(array_keys($mostCommentingUser),0,4) as $user)
{
$query = "SELECT filepath, filename, owner_name, owner_id, pid FROM {$CONFIG['TABLE_PICTURES']} as p WHERE p.owner_id = '$user' ";
$result = cpg_db_query($query);
$rowset = cpg_db_fetch_rowset($result);
mysql_free_result($result);
$file = $rowset[0];
echo <<
EOT;
$cat = explode('/', $file[0]);
print("
{$file[2]}{$mostCommentingUser[$user]} Kommentare");
echo <<
|
EOT;
}
endtable();
//End User Most Commenting
//Start Viewing Activity By Time
activityGraph("sdate", $CONFIG['TABLE_HIT_STATS'], "Besuchs- Aktivität", "images/bar.gif");
//End Viewing Activity By Time
//Start Upload Activity By Time
activityGraph("ctime", $CONFIG['TABLE_PICTURES'], "Upload- Aktivität", "images/bar.gif");
//End Upload Activity By Time
//Start Comment Activity By Time
activityGraph("msg_date", $CONFIG['TABLE_COMMENTS'], "Kommentar- Aktivität", "images/bar.gif", true);
//End Comment Activity By Time
pagefooter();
$signature = 'Coppermine Photo Gallery ' . COPPERMINE_VERSION . ' ('. COPPERMINE_VERSION_STATUS . ')';
?>