# displays some cached site activity info

# a "few" variables to use
my $statm_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}->{activitym_stats_num} \|\| 1001;

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

# get the top sid for the past month
my ($rv,$sth) = $S->db_select({
FROM => 'comments',
WHAT => 'sid,count(*) as c',
WHERE => "TO_DAYS(NOW()) - 30 <= 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();
$statm_hash->{sid} = $tmphash->{sid};
$statm_hash->{sid_count} = $tmphash->{c};

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

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

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

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

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

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

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

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

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

return $S->{DBH}->errstr() unless $rv;
$tmphash = $sth->fetchrow_hashref;
$statm_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='$statm_hash->{sid}'",
});
return $S->{DBH}->errstr() unless $rv;

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

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

} else {
$statm_hash = $S->cache->{CACHE}->{activitym_stats};
$S->cache->{CACHE}->{activitym_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 Month</b>:<br> <a href="/?op=displaystory;sid=$statm_hash->{sid}">$statm_hash->{title}</a> with <b>$statm_hash->{sid_count}</b> comments in the past month.<br><br>\n];
$content .= qq[Numbers for the past month:
<ul><li> <b>$statm_hash->{story_count}</b> stories <li><b>$statm_hash->{diary_count}</b> diaries <li><b>$statm_hash->{comment_count}</b> comments</ul>
<br>\n];
$content .= qq[Welcome the <b>$statm_hash->{uid_count}</b> users that have joined in the past month, which gives us <b>$statm_hash->{top_uid}</b> users total!</FONT><br>\n];

return {content => $content};

Post A Comment