$reporttime"; }; my $dellink = sub { my ($by, $what, $text) = @_; return ""; }; # login check my $remote = LJ::get_remote(); return $error->("You must be logged in to be here.") unless $remote; # priv check return $error->("You do not have the necessary privilege to be here.") unless LJ::check_priv($remote, 'siteadmin', 'spamreports'); # show the top 10 spam reports by IP my $mode = lc($GET{mode} || $POST{mode}); $mode = '' if $mode =~ /^del/ && !LJ::did_post() && !LJ::check_referer('/admin/spamreports.bml'); # combined/user/anon viewing? my $view = $mode =~ /_([cua])$/ ? $1 : 'c'; my ($extrawhere, $extratitle); if ($view eq 'c') { $extrawhere = '1'; } elsif ($view eq 'u') { $extrawhere = 'posterid > 0'; $extratitle = " - Users Only"; } elsif ($view eq 'a') { $extrawhere = 'posterid = 0'; $extratitle = " - Anonymous Only"; } $mode =~ s/_[cua]$//; # strip out viewing option my $dbr = LJ::get_db_reader(); return $error->("Unable to get database reader handle.") unless $dbr; my @rows; my @headers; if ($mode eq 'top10ip') { # top 10 by ip $title = "Spam Reports - Top 10 by IP Address"; @headers = ('Number of Reports', 'IP Address', 'Most Recent Report'); my $res = $dbr->selectall_arrayref('SELECT COUNT(ip) AS num, ip, MAX(reporttime) FROM spamreports ' . "WHERE state = 'open' AND ip IS NOT NULL " . 'GROUP BY ip ORDER BY num DESC LIMIT 10'); foreach (@$res) { push @rows, [ $_->[0], $_->[1], $makelink->('ip', $_->[1], 'open', $_->[2]) ]; } } elsif ($mode eq 'top10user') { # top 10 by user $title = "Spam Reports - Top 10 by User"; @headers = ('Number of Reports', 'Posted By User', 'Most Recent Report'); my $res = $dbr->selectall_arrayref('SELECT COUNT(posterid) AS num, posterid, MAX(reporttime) FROM spamreports ' . "WHERE state = 'open' AND posterid > 0 " . 'GROUP BY posterid ORDER BY num DESC LIMIT 10'); foreach (@$res) { my $u = LJ::load_userid($_->[1]); push @rows, [ $_->[0], LJ::ljuser($u), $makelink->('posterid', $_->[1], 'open', $_->[2]) ]; } } elsif ($mode eq 'tlast10') { # most recent 10 reports $title = "Spam Reports - Last 10$extratitle"; @headers = ('Posted By', 'Posted In', 'Report Time'); my $res = $dbr->selectall_arrayref('SELECT posterid, ip, journalid, reporttime FROM spamreports ' . "WHERE state = 'open' AND $extrawhere ORDER BY reporttime DESC LIMIT 10"); foreach (@$res) { my $u2 = LJ::load_userid($_->[2]); if ($_->[0] > 0) { my $u = LJ::load_userid($_->[0]); push @rows, [ LJ::ljuser($u), LJ::ljuser($u2), $makelink->('posterid', $_->[0], 'open', $_->[3]) ]; } else { push @rows, [ "$_->[1]", LJ::ljuser($u2), $makelink->('ip', $_->[1], 'open', $_->[3]) ]; } } } elsif ($mode =~ /^last(\d+)hr$/) { # reports in last X hours my $hours = $1+0; my $secs = $hours * 3600; # seconds in an hour $title = "Spam Reports - Last $hours Hour" . ($hours == 1 ? '' : 's') . $extratitle; @headers = ('Number of Reports', 'Posted By', 'Report Time'); my $res = $dbr->selectall_arrayref('SELECT journalid, ip, posterid, reporttime FROM spamreports ' . "WHERE $extrawhere AND reporttime > (UNIX_TIMESTAMP() - $secs) LIMIT 1000"); # count up items and their most recent report my %hits; my %times; foreach (@$res) { my $key; if ($_->[2] > 0) { my $u = LJ::load_userid($_->[2]); next unless $u; $key = $u->{userid}; } else { next unless $_->[1]; $key = $_->[1]; } $hits{$key}++; $times{$key} = $_->[3] unless $times{$key} gt $_->[3]; } # now reverse to number => item list my %revhits; foreach (keys %hits) { if ($revhits{$hits{$_}}) { push @{$revhits{$hits{$_}}}, $_; } else { $revhits{$hits{$_}} = [ $_ ]; } } # now push them onto @rows foreach (sort { $b <=> $a } keys %revhits) { my $r = $revhits{$_}; foreach (@$r) { my $isip = $_ =~ /\./ ? 1 : 0; push @rows, [ $hits{$_}, $isip ? $_ : LJ::ljuser(LJ::load_userid($_)), $makelink->($isip ? 'ip' : 'posterid', $_, 'open', $times{$_}) ]; } } } elsif ($mode eq 'view') { # view a particular report my ($by, $what, $state) = (lc($GET{by}), $GET{what}, lc($GET{state})); $by = '' unless $by =~ /^(?:ip|poster(?:id)?)$/; $state = 'open' unless $state =~ /^(?:open|closed)$/; $body .= "<< Front Page ] "; # open/closed links my $eargs = LJ::eurl("?by=$by&what=$what&state"); if ($state eq 'open') { $body .= " [ " . $makelink->($by, $what, 'closed', undef, "View Closed Reports") . " ]"; } else { $body .= " [ " . $makelink->($by, $what, 'open', undef, "View Open Reports") . " ]"; } $body .= " p?>\n"; # setup title and verify that the data is right if ($by eq 'posterid') { $what += 0; my $u = LJ::load_userid($what); return $error->('No such posterid.') unless $u; $title = "Spam Reports - By $u->{user} ($state)"; } elsif ($by eq 'poster') { my $u = LJ::load_user($what); return $error->('No such user.') unless $u; $title = "Spam Reports - Comments By $u->{user}"; # Now just pretend that user used 'posterid' $by = 'posterid'; $what = $u->{userid}; } elsif ($by eq 'ip') { # check for right format x.x.x.x, not necessarily a valid IP return $error->('No such IP.') if $what !~ /^\d+\.\d+\.\d+\.\d+$/ or length $what > 15; $title = "Spam Reports - By IP $what ($state)"; } # see if we should call a hook for extra actions? $body .= LJ::run_hook('spamreport_notification', $remote, { $by => $what }) if $state eq 'open' && $by eq 'posterid'; # now the general info gathering my $res = $dbr->selectall_arrayref('SELECT reporttime, journalid, subject, body, ip, posttime, report_type ' . "FROM spamreports WHERE state=? AND $by=? ORDER BY reporttime DESC LIMIT 1000", undef, $state, $what); unless ($res && @$res) { $body .= "No reports found."; return undef; } $body .= ''; foreach (@$res) { my $u2 = LJ::load_userid($_->[1]); my $x = $by eq 'ip' ? 4 : 1; my $comment_body = $_->[3]; LJ::text_uncompress(\$comment_body); my $spamlocation = ($_->[6] eq 'entry') ? 'Entry' : 'Comment'; $body .= ''; } $body .= "

' . ($state eq 'open' ? $dellink->("del$by", "$_->[0]:$_->[$x]", 'Close') : '') . '	' . "$spamlocation in: " . LJ::ljuser($u2) . ' ' . 'Report Time: ' . LJ::mysql_time($_->[0]) . ' ' . "$spamlocation Time: " . ($_->[5] ? LJ::mysql_time($_->[5]) : 'not recorded') . ' ' . 'Subject: ' . LJ::ehtml($_->[2] \|\| 'no subject') . ' ' . 'Body: ' . LJ::ehtml($comment_body \|\| 'no body') . ' ' . '

" . ($state eq 'open' ? $dellink->("delby$by", $what, 'Close All') : ''); } elsif ($mode =~ /^del/) { # figure out our combination my $dbh = LJ::get_db_writer(); return $error->("Unable to get database writer handle.") unless $dbh; my ($sql, $count, $backlink); if ($mode =~ /^delby/) { # enmasse deletion my $where = $mode =~ /ip$/ ? 'ip' : 'posterid'; $sql = "UPDATE spamreports SET state='closed' WHERE $where=?"; $count = $dbh->do($sql, undef, $POST{what}); return $error->($dbh->errstr) if $dbh->err; } else { # single item deletion my $where = $mode =~ /ip$/ ? 'ip' : 'journalid'; my ($time, $data) = ($1, $2) if $POST{what} =~ /^(\d+):(.+)$/; $data ||= $POST{what}; $count = $dbh->do("UPDATE spamreports SET state='closed' WHERE reporttime=? AND $where=? AND state='open'", undef, $time, $data); return $error->($dbh->errstr) if $dbh->err; $backlink = "[ << Go Back ]"; } $title = "Close Reports"; $body .= "<< Front Page ] $backlink p?>\n"; my $s = $count == 1 ? '' : 's'; $body .= "Closed $count report$s.\n"; } else { # standard my %modes = (top10user => 'Top 10 by User', top10ip => 'Top 10 by IP Address', tlast10 => 'Last 10 Reports', last01hr => 'Last 1 Hour', last06hr => 'Last 6 Hours', last24hr => 'Last 24 Hours'); $body .= "\n

$modes{$_}"; if ($_ =~ /last/) { # this is a last view, so we have other options $body .= " [users, "; $body .= "anonymous]"; } $body .= "
Reports for user:

\n"; } # now spit out the requested table return unless @headers; $body .= "<< Front Page ] p?>"; $body .= "\n"; $body .= "" foreach @headers; $body .= "\n"; foreach (@rows) { $body .= ""; $body .= "" foreach @$_; $body .= "\n"; } $body .= "

$_
$_

\n"; return; } _code?> body=> page?>