# displays some cached site activity info

# a "few" variables to use
my $stat_hash = {
title => '',
sid => '',
sid_count => 0,
top_uid => 0,
uid_count => 0,
diary_count => 0,
story_count => 0,
comment_count => 0 };

# refresh every 101
my $refresh = $S->cache->{CACHE}->{activity_stats_num} || 1001;

if( $refresh > 1000 ) {
$S->cache->{CACHE}->{activity_stats_num} = 1;
$S->cache->{CACHE}->{activity_stats} = {};

# get the top sid for the past week
my ($rv,$sth) = $S->db_select({
FROM => 'comments',
WHAT => 'sid,count(*) as c',
WHERE => "TO_DAYS(NOW()) - 7 <= TO_DAYS(date)",
GROUP_BY => 'sid',
ORDER_BY => 'c desc',
LIMIT => 1,
});

return $S->{DBH}->errstr() unless( $rv );
my $tmphash = $sth->fetchrow_hashref;
$sth->finish();
$stat_hash->{sid} = $tmphash->{sid};
$stat_hash->{sid_count} = $tmphash->{c};

# now get the title
($rv,$sth) = $S->db_select({
FROM => 'stories',
WHAT => 'title',
WHERE => "sid='$stat_hash->{sid}'",
});

return $S->{DBH}->errstr() unless $rv;
$tmphash = $sth->fetchrow_hashref;
$stat_hash->{title} = $tmphash->{title};
$sth->finish();

# get top uid, and the number in the past week
($rv,$sth) = $S->db_select({
FROM => 'users',
WHAT => 'uid',
WHERE => 'TO_DAYS(NOW()) - 7 <= TO_DAYS(creation_time)',
ORDER_BY => 'uid desc',
});

return $S->{DBH}->errstr() unless( $rv );
my $tmp_arr = $sth->fetchall_arrayref();
$sth->finish();
$stat_hash->{uid_count} = scalar( @{$tmp_arr} );
my $tmptmp_arr = shift @$tmp_arr;
$stat_hash->{top_uid} = $tmptmp_arr->[0];

# Comment count for past week
my ($rv,$sth) = $S->db_select({
FROM => 'comments',
WHAT => 'count(*) as c',
WHERE => "TO_DAYS(NOW()) - 7 <= TO_DAYS(date)",
});

return $S->{DBH}->errstr() unless( $rv );
$tmphash = $sth->fetchrow_hashref;
$sth->finish();
$stat_hash->{comment_count} = $tmphash->{c};

# now get story count for past week
($rv,$sth) = $S->db_select({
FROM => 'stories',
WHAT => 'count(*) as c',
WHERE => "section != 'Diary' and TO_DAYS(NOW()) - 7 <= TO_DAYS(time)",
});

return $S->{DBH}->errstr() unless $rv;
$tmphash = $sth->fetchrow_hashref;
$stat_hash->{story_count} = $tmphash->{c};
$sth->finish();

# now get diary count for past week
($rv,$sth) = $S->db_select({
FROM => 'stories',
WHAT => 'count(*) as c',
WHERE => "section = 'Diary' and TO_DAYS(NOW()) - 7 <= TO_DAYS(time)",
});

return $S->{DBH}->errstr() unless $rv;
$tmphash = $sth->fetchrow_hashref;
$stat_hash->{diary_count} = $tmphash->{c};
$sth->finish();


# just in case the top story is a poll
($rv,$sth) = $S->db_select({
FROM => 'pollquestions',
WHAT => 'qid,question',
WHERE => "qid='$stat_hash->{sid}'",
});
return $S->{DBH}->errstr() unless $rv;

$tmphash = $sth->fetchrow_hashref;
if( $tmphash->{qid} eq $stat_hash->{sid} ) {
$stat_hash->{title} = $tmphash->{question};
}
$sth->finish();

# store it in the cache now
$S->cache->{CACHE}->{activity_stats} = $stat_hash;

} else {
$stat_hash = $S->cache->{CACHE}->{activity_stats};
$S->cache->{CACHE}->{activity_stats_num} += 1;
}

# Now print neatly
my $content .= qq[<br><FONT FACE="verdana, arial, helvetica, sans-serif" SIZE="2"><b>Top Story of the past Week</b>:<br>         <a href="/?op=displaystory;sid=$stat_hash->{sid}">$stat_hash->{title}</a> with <b>$stat_hash->{sid_count}</b> comments in the past week.<br><br>\n];
$content .= qq[Numbers for the past week:
<ul><li> <b>$stat_hash->{story_count}</b> stories <li><b>$stat_hash->{diary_count}</b> diaries <li><b>$stat_hash->{comment_count}</b> comments</ul>
<br>\n];
$content .= qq[Welcome the <b>$stat_hash->{uid_count}</b> users that have joined in the past week, which gives us <b>$stat_hash->{top_uid}</b> users total!</FONT><br>\n];

return {content => $content};

Post A Comment