[dw-free] Paginate sysban query results

[fu]

[commit: http://hg.dwscoalition.org/dw-free/rev/4e3be7dc55bb]

http://bugs.dwscoalition.org/show_bug.cgi?id=3016

Pagination for sysban page, to help when there are a lot of entries (e.g.,
spam)

Patch by kareila.

Files modified:

• cgi-bin/sysban.pl
• htdocs/admin/sysban.bml

--------------------------------------------------------------------------------
diff -r 68b079ecf8ce -r 4e3be7dc55bb cgi-bin/sysban.pl
--- a/cgi-bin/sysban.pl	Tue Sep 21 22:37:06 2010 +0800
+++ b/cgi-bin/sysban.pl	Tue Sep 21 22:56:16 2010 +0800
@@ -258,28 +258,29 @@ sub _db_sysban_populate {
 # returns: hashref on success, undef on failure
 # </LJFUNC>
 sub sysban_populate_full {
-    my ($where, $what) = @_;
-    return LJ::_db_sysban_populate_full($where, $what);
+    return LJ::_db_sysban_populate_full( @_ );
 }
 
 sub _db_sysban_populate_full {
-    my ($where, $what) = @_;
+    my ( $where, $what, $limit, $skip ) = @_;
     my $dbh = LJ::get_db_writer();
     return undef unless $dbh;
 
     # build cache from db
-    my $sth = $dbh->prepare("SELECT banid, value, 
-                                UNIX_TIMESTAMP(banuntil), note " .
-                            "FROM sysban " .
-                            "WHERE status='active' AND what=? " .
-                            "AND NOW() > bandate " .
-                            "AND (NOW() < banuntil OR banuntil IS NULL)");
-    $sth->execute($what);
+    my $limitsql = $limit ? " ORDER BY banid DESC LIMIT ? OFFSET ?" : "";
+    my $sth = $dbh->prepare( "SELECT banid, value, " .
+                             "UNIX_TIMESTAMP(banuntil), note " .
+                             "FROM sysban " .
+                             "WHERE status='active' AND what=? " .
+                             "AND NOW() > bandate " .
+                             "AND (NOW() < banuntil OR banuntil IS NULL)" .
+                             $limitsql );
+    $sth->execute( $what, $limit, $skip );
     return undef if $sth->err;
     while (my ($banid, $val, $exp, $note) = $sth->fetchrow_array) {
-        $where->{$val}->{'banid'} = $banid || 0;
-        $where->{$val}->{'expire'} = $exp || 0;
-        $where->{$val}->{'note'} = $note || 0;
+        $where->{$val}->{banid}  = $banid || 0;
+        $where->{$val}->{expire} = $exp   || 0;
+        $where->{$val}->{note}   = $note  || 0;
     }
 
     return $where;
diff -r 68b079ecf8ce -r 4e3be7dc55bb htdocs/admin/sysban.bml
--- a/htdocs/admin/sysban.bml	Tue Sep 21 22:37:06 2010 +0800
+++ b/htdocs/admin/sysban.bml	Tue Sep 21 22:56:16 2010 +0800
@@ -75,6 +75,7 @@ body<=
     else                      { $action = undef; }
 
     my $bantype = $POST{'bantype'};
+    my $skip = $POST{skip} ? $POST{skip} + 0 : 0;
 
     my $ret = "<form method='post' action='sysban'>";
     $ret   .= LJ::form_auth();
@@ -101,8 +102,9 @@ FORM
         return $err->("Invalid form") unless LJ::check_form_auth();
         
         my $existing_bans = {};
+        my $limit = 20;
 
-        LJ::sysban_populate_full( $existing_bans, $bantype );
+        LJ::sysban_populate_full( $existing_bans, $bantype, $limit, $skip );
 
         $ret = <<QUERYFORM;
 <table>
@@ -145,6 +147,26 @@ QUERYFORM
 QUERYFORM
         }
         $ret .= "</table>";
+
+        if ( keys %$existing_bans >= $limit ) {
+            $ret .= "\n<form method='post' action='sysban'>";
+            $ret .= LJ::form_auth();
+            $ret .= LJ::html_hidden( query => 'Query' );
+            $ret .= LJ::html_hidden( bantype => $bantype );
+            $ret .= LJ::html_hidden( skip => $skip + $limit );
+            $ret .= LJ::html_submit( submit => "<< Previous $limit" );
+            $ret .= "</form>";
+        }
+
+        if ( $skip ) {
+            $ret .= "\n<form method='post' action='sysban'>";
+            $ret .= LJ::form_auth();
+            $ret .= LJ::html_hidden( query => 'Query' );
+            $ret .= LJ::html_hidden( bantype => $bantype );
+            $ret .= LJ::html_hidden( skip => $skip - $limit );
+            $ret .= LJ::html_submit( submit => "Next $limit >>" );
+            $ret .= "</form>";
+        }
 
     }
 
--------------------------------------------------------------------------------