first version of masterinterface

3 files changed, 162 insertions, 0 deletions

diff --git a/lib/MasterWebInterface/Database/Games.pm b/lib/MasterWebInterface/Database/Games.pm
new file mode 100755
index 0000000..02b6ad9
--- /dev/null
+++ b/lib/MasterWebInterface/Database/Games.pm
@@ -0,0 +1,61 @@
+package MasterWebInterface::Database::Games;
+use strict;
+use warnings;
+use Exporter 'import';
+our @EXPORT = qw| dbGameListGet dbGetGameDesc |;
+
+# Get list of games
+sub dbGameListGet 
+{
+    my $s = shift;
+    my %o = (
+        page    => 1, 
+        results => 50, 
+        sort    => '', 
+        @_
+    );
+    
+    # search criteria
+    my %where = (
+        $o{search} ? ('lower(label) LIKE lower(?)' => "%$o{search}%") : (),
+        !$o{all}   ? (             'num_total > ?' => 0)              : (),
+    );
+    
+    # what to get from db
+    my @select = ( 
+        qw| label gamename num_direct num_total |
+    );
+    
+    # sort order
+    my $order = sprintf {
+        label       => 'label %s',
+        gamename    => 'gamename %s',
+        num_total   => 'num_total %s',
+    }->{ $o{sort}||'num_total' }, $o{reverse} ? 'DESC' : 'ASC';
+    
+    # query
+    my($r, $np) = $s->dbPage(
+        \%o, 
+        q| SELECT !s FROM gameinfo !W ORDER BY !s|,
+        join(', ', @select), 
+        \%where, 
+        $order
+    );
+    
+    # page numbering
+    my $p = $s->dbAll( 
+        q| SELECT COUNT(*) AS num FROM gameinfo !W|, 
+        \%where,
+    )->[0]{num};
+    
+    return wantarray ? ($r, $np, $p) : $r;
+}
+
+# Get description for a game by gamename
+sub dbGetGameDesc 
+{
+    my ($self, $gn) = @_;
+    return $self->dbAll("SELECT label FROM gameinfo WHERE gamename = ?", $gn)->[0]{label};
+}
+
+1;
diff --git a/lib/MasterWebInterface/Database/ServerInfo.pm b/lib/MasterWebInterface/Database/ServerInfo.pm
new file mode 100755
index 0000000..6aeddd4
--- /dev/null
+++ b/lib/MasterWebInterface/Database/ServerInfo.pm
@@ -0,0 +1,40 @@
+package MasterWebInterface::Database::ServerInfo;
+use strict;
+use warnings;
+use Exporter 'import';
+our @EXPORT = qw| dbGetServerInfo dbGetPlayerInfoList |;
+
+## get server details for list of servers (gamename/all/recent)
+sub dbGetServerInfo 
+{
+  my $s = shift;
+  my %o = @_;
+  
+  my %where = (
+    $o{ip}        ? (  'ip = ?'           => $o{ip})       : (),
+    $o{port}      ? (  'queryport = ?'    => $o{port})     : (),
+    $o{hostport}  ? (  'hostport = ?'     => $o{hostport}) : (),
+  );
+
+  return $s->dbAll( q|SELECT * FROM serverlist
+                      LEFT JOIN serverinfo ON serverlist.id = serverinfo.sid
+                      !W LIMIT 1|, \%where );
+}
+
+
+## get player details for one particular server
+sub dbGetPlayerInfoList 
+{
+  my $s = shift;
+  my %o = (sort => '', @_ );
+  
+  my %where = (
+    $o{sid} ? ( 'sid = ?' => $o{sid})    : (),
+  );
+  
+  my @select = ( qw| name team frags mesh skin face ping | );
+
+  return $s->dbAll( q|SELECT * FROM playerinfo !W ORDER BY team, name|, \%where );
+}
+
+1;
diff --git a/lib/MasterWebInterface/Database/Servers.pm b/lib/MasterWebInterface/Database/Servers.pm
new file mode 100755
index 0000000..d02bcd7
--- /dev/null
+++ b/lib/MasterWebInterface/Database/Servers.pm
@@ -0,0 +1,61 @@
+package MasterWebInterface::Database::Servers;
+use strict;
+use warnings;
+use Exporter 'import';
+our @EXPORT = qw| dbServerListGet |;
+
+################################################################################
+## get the serverlist
+################################################################################
+sub dbServerListGet {
+  my $s = shift;
+  my %o = ( page => 1, 
+            results => 50, 
+            gamename => "all", 
+            @_ 
+  );
+    
+  my %where = (
+    # gamename and char are "all" or value
+    $o{gamename} !~ /all/ ? ('serverlist.gamename = ?'       => $o{gamename})       : (),
+    $o{nolist}            ? ('serverlist.gamename <> ?'      => $o{nolist})         : (),
+    $o{search}            ? ('LOWER(hostname) LIKE LOWER(?)' => "%$o{search}%")     : (),
+    $o{gametype}          ? ('LOWER(gametype) LIKE LOWER(?)' => $o{gametype})       : (),
+    $o{updated}           ? ('dt_updated > ?'                => (time-$o{updated})) : (),
+    ('hostport >= ?' => 0), # sanity check
+  );
+  
+  my @select = ( qw| id ip hostport hostname serverlist.gamename country numplayers maxplayers maptitle mapname gametype dt_added label dt_updated| );
+
+  my $order = sprintf {
+    hostname    => 'hostname %s',
+    gamename    => 'serverlist.gamename %s, gametype',
+    country     => 'country %s',
+    dt_added    => 'dt_added %s',
+    gametype    => 'gametype %s, mapname',
+    numplayers  => 'numplayers %s, maxplayers',
+    maptitle    => 'maptitle %s',
+    mapname     => 'mapname %s',
+  }->{ $o{sort} // 'dt_added' }, $o{reverse} ? 'DESC' : 'ASC';
+  
+  my($r, $np) = $s->dbPage(\%o, q|
+    SELECT !s FROM serverlist
+      LEFT JOIN serverinfo ON serverlist.id = serverinfo.sid
+      LEFT JOIN gameinfo ON serverlist.gamename = gameinfo.gamename
+      !W
+      ORDER BY !s |,
+    join(', ', @select), \%where, $order
+  );
+
+  my $p = $s->dbAll( q|
+    SELECT COUNT(*) AS num
+    FROM serverlist
+    LEFT JOIN serverinfo ON serverlist.id = serverinfo.sid
+    !W|, \%where,
+  )->[0]{num};
+  return wantarray ? ($r, $np, $p) : $r;
+
+}
+
+
+1;