105 lines
3.9 KiB
Plaintext
105 lines
3.9 KiB
Plaintext
<?page
|
|
title=>Comments Posted
|
|
body<=
|
|
|
|
<?_code
|
|
|
|
return "<B>Down for Maintenance</B><BR>This page used database queries that were horribly unoptimized and killed the rest of the site. This is currently being fixed on the LiveJournal development server, and when it's fixed, the main server will be updated and this page will be available again.";
|
|
|
|
my $dbh = LJ::get_db_writer();
|
|
|
|
my $ret = "";
|
|
|
|
my $user = lc($FORM{'user'});
|
|
my $quser = $dbh->quote($user);
|
|
my $month = $FORM{'month'};
|
|
my $year;
|
|
|
|
if ($month =~ /^(\d\d\d\d)-(\d\d)$/) {
|
|
$year = $1;
|
|
$month = $2;
|
|
} else {
|
|
my @nowtime = localtime();
|
|
$year = $nowtime[5]+1900;
|
|
$month = sprintf("%02d", $nowtime[4]+1);
|
|
}
|
|
my $qyear = $dbh->quote($year);
|
|
my $qmonth = $dbh->quote($month);
|
|
|
|
$sth = $dbh->prepare("SELECT * FROM user WHERE user=$quser");
|
|
$sth->execute;
|
|
my $u = $sth->fetchrow_hashref;
|
|
|
|
unless ($u) {
|
|
return "<?h1 Unknown user h1?><?p There doth not exist a user with the username <B>$user</B>. Sorry. p?>";
|
|
}
|
|
|
|
# get number of posts per month
|
|
$sth = $dbh->prepare("SELECT DATE_FORMAT(datepost, '%Y-%m') AS 'month', DATE_FORMAT(datepost, '%Y-%M') AS 'monthpretty', COUNT(*) AS 'count' FROM talk WHERE posterid=$u->{'userid'} GROUP BY 1, 2 ORDER BY 1");
|
|
$sth->execute;
|
|
%permonth = ();
|
|
while ($_ = $sth->fetchrow_hashref) {
|
|
$permonth{$_->{'month'}} = { format => $_->{'monthpretty'},
|
|
count => $_->{'count'} };
|
|
}
|
|
|
|
$ret .= "<?h1 Comments Posted in $year/$month h1?><?p Below are a list of comments left in journals by <A HREF=\"/userinfo.bml?user=$user\">$u->{'name'}</A>. You can also view other months this user has posted comments: p?>";
|
|
$ret .= "<FORM><INPUT TYPE=HIDDEN NAME=user VALUE=$user><CENTER>Month: <SELECT NAME=month>";
|
|
foreach (reverse sort keys %permonth) {
|
|
my $sel = $_ eq $FORM{'month'} ? "SELECTED" : "";
|
|
$ret .= "<OPTION VALUE=\"$_\" $sel>$permonth{$_}->{'format'}: $permonth{$_}->{'count'}\n";
|
|
}
|
|
$ret .= "</SELECT><INPUT TYPE=SUBMIT VALUE=\"View\"></CENTER><P>";
|
|
|
|
|
|
# load the comment numbers
|
|
$sth = $dbh->prepare("SELECT t.datepost, uj.user as 'userjournal', t.nodeid AS 'itemid', t.parenttalkid, tt.subject FROM talk t, talktext tt, user uj WHERE t.talkid=tt.talkid AND t.journalid=uj.userid AND t.posterid=$u->{'userid'} AND t.state<>'D' AND t.nodetype='L' AND YEAR(t.datepost)=$qyear AND MONTH(t.datepost)=$qmonth ORDER BY t.datepost");
|
|
$sth->execute;
|
|
if ($dbh->err) { return $dbh->errstr; }
|
|
@posts = ();
|
|
push @posts, $_ while ($_ = $sth->fetchrow_hashref);
|
|
|
|
$TRUNCATE_SIZE = 50;
|
|
|
|
# load the comments
|
|
my $itemid_in = join(", ", 0, map { $_->{'itemid'} } @posts);
|
|
$sth = $dbh->prepare("SELECT l.itemid, l.eventtime, LEFT(lt.event, $TRUNCATE_SIZE) AS 'eventtrunc', LENGTH(lt.event) as 'eventlength' FROM log l, logtext lt WHERE l.itemid IN ($itemid_in) AND l.itemid=lt.itemid");
|
|
$sth->execute;
|
|
if ($dbh->err) { return $dbh->errstr; }
|
|
my %item = ();
|
|
$item{$_->{'itemid'}} = $_ while ($_ = $sth->fetchrow_hashref);
|
|
|
|
$ret .= "<P><TABLE CELLPADDING=4 BORDER=1>\n";
|
|
$ret .= "<TR BGCOLOR=#E0E0E0><TD><B>Date</B></TD><TD><B>Entry</B></TD><TD NOWRAP><B>Subject</B></TD></TR>\n";
|
|
|
|
foreach my $p (@posts)
|
|
{
|
|
$id = $p->{'itemid'};
|
|
$date = $p->{'datepost'};
|
|
$date =~ s/ /<BR>/;
|
|
$ret .= "<TR><TD ALIGN=CENTER NOWRAP><FONT SIZE=-1>$date</FONT></TD>";
|
|
|
|
if ($p->{'parenttalkid'} == 0) {
|
|
$event = $item{$id}->{'eventtrunc'};
|
|
$event =~ s/\<[^\>]*$//;
|
|
LJ::CleanHTML::clean_event(\$event);
|
|
if ($item{$id}->{'eventlength'} > $TRUNCATE_LENGTH) { $event .= "..."; }
|
|
$ret .= "<TD><B><A HREF=\"/userinfo.bml?user=$p->{'userjournal'}\">$p->{'userjournal'}</A></B>: $event</TD>";
|
|
} else {
|
|
$ret .= "<TD><FONT SIZE=-2 COLOR=#C0C0C0>(reply to other comment)</FONT></TD>";
|
|
}
|
|
|
|
$p->{'subject'} ||= "(no subject)";
|
|
$ret .= "<TD><A HREF=\"/talkread.bml?itemid=$id\">$p->{'subject'}</A></TD></TR>\n";
|
|
|
|
}
|
|
$ret .= "</TABLE>\n";
|
|
return $ret;
|
|
|
|
_code?>
|
|
|
|
|
|
|
|
<=body
|
|
page?>
|