advanced filtering

1 files changed, 51 insertions, 0 deletions

diff --git a/lib/MasterWebInterface/Database/AdvancedSearch.pm b/lib/MasterWebInterface/Database/AdvancedSearch.pm
new file mode 100755
index 0000000..a21a3c3
--- /dev/null
+++ b/lib/MasterWebInterface/Database/AdvancedSearch.pm
@@ -0,0 +1,51 @@
+package MasterWebInterface::Database::AdvancedSearch;
+use strict;
+use warnings;
+use Exporter 'import';
+our @EXPORT = qw| dbGetGameTypes dbGetCountries |;
+
+sub dbGetGameTypes 
+{
+  my $s = shift;
+  my %o = ( @_ );
+    
+  my %where = (
+    # gamename and char are "all" or value
+    $o{gamename}          ? ('serverlist.gamename = ?'       => $o{gamename})       : (),
+    ('gametype IS NOT NULL' => ""),
+
+    # do not filter by country. the gametype can still exist for that game while there are no online servers for it.    
+    #$o{country}           ? ('country         LIKE UPPER(?)' => $o{country})         : (),
+  );
+  
+  return $s->dbAll( q|
+    SELECT DISTINCT gametype FROM serverlist
+      LEFT JOIN serverinfo ON serverlist.id = serverinfo.sid
+      !W ORDER BY lower(gametype) ASC|,
+    \%where,
+  );
+}
+
+sub dbGetCountries 
+{
+  my $s = shift;
+  my %o = ( @_ );
+    
+  my %where = (
+    $o{gamename}          ? ('serverlist.gamename = ?'       => $o{gamename})       : (),
+    $o{hostname}          ? ('LOWER(hostname) LIKE LOWER(?)' => "%$o{hostname}%")     : (),
+    $o{mapname}           ? ('(LOWER(mapname) LIKE LOWER(?) OR LOWER(maptitle) LIKE LOWER(?))' => ["%$o{mapname}%", "%$o{mapname}%"]) : (),
+    $o{gametype}          ? ('LOWER(gametype) LIKE LOWER(?)' => $o{gametype})         : (),
+
+    ("COUNTRY IS NOT NULL" => ""),
+  );
+  
+  return $s->dbAll( q|
+    SELECT DISTINCT country FROM serverlist
+      LEFT JOIN serverinfo ON serverlist.id = serverinfo.sid
+      !W ORDER BY lower(country) ASC|,
+    \%where,
+  );
+}
+
+1;