Changeset 201 for branches/new-search-20050223

Timestamp:

03/17/05 18:02:08 (20 years ago)

Author:

Kris Deugau

Message:

/branches/new-search-20050223

Functional multi-component search, minus detail on include/exclude.
Also missing details of IP address search code.

File:

• branches/new-search-20050223/cgi-bin/search.cgi (modified) (2 diffs)

branches/new-search-20050223/cgi-bin/search.cgi

@@ -62 +62 @@
     $webvar{input} =~ s/^\s+//;
     $webvar{input} =~ s/\s+$//;
-    if ($webvar{input} =~ /^(\d{1,3}\.){3}\d{1,3}\/\d{1,3}$/) {
-      # "Perfect" IP subnet match
-      $searchfor = "ipblock";
-    } elsif ($webvar{input} =~ /^(\d{1,3}\.){3}\d{1,3}$/) {
-      # "Perfect" IP address match (shows containing block)
-      $searchfor = "ipblock";
-    } elsif ($webvar{input} =~ /^(\d{1,3}\.){2}\d{1,3}(\.\d{1,3}?)?/) {
-      # Partial IP match
+    if ($webvar{input} =~ /^[\d\.]+(\/\d{2})?$/) {
+      # IP addresses should only have numbers, digits, and maybe a slash+netmask
       $searchfor = "ipblock";
     } elsif ($webvar{input} =~ /^\d+$/) {
@@ -85 +79 @@
   print "Complex Search...............\n";
 
-print "<pre>\n";
-foreach my $key (keys %webvar) {
-  print "key: $key      value: $webvar{$key}\n";
-}
-print "</pre>\n";
+  # Several major cases, and a whole raft of individual cases.
+  # -> Show all types means we do not need to limit records retrieved by type
+  # -> Show all cities means we do not need to limit records retrieved by city
+  # Individual cases are for the CIDR/IP, CustID, Description, Notes, and individual type
+  # requests.
+
+my $sql = "SELECT cidr,custid,type,city,description FROM searchme";
+
+my $sqlconcat;
+if ($webvar{which} eq 'all') {
+  # Must match *all* specified criteria.        ## use INTERSECT or EXCEPT
+$sqlconcat = "INTERSECT";
+#  $sqlconcat = "and";
+} elsif ($webvar{which} eq 'any') {
+  # Match on any specified criteria             ## use UNION
+$sqlconcat = "UNION";
+#  $sqlconcat="or";
+} else {
+  # We can't get here.  PTHBTT!
+  printAndExit "PTHBTT!!  Your search has been rejected due to Microsoft excuse #4432:  Not enough mana";
+}
+
+# Cases:
+# -> No search terms.  Display everything.
+# -> One or more terms (cidr/custid/desc/notes)
+
+# -> Type/city restrictions
+
+my $cols = "cidr,custid,type,city,description";
+
+$sql = "(select $cols from searchme where $webvar{custexclude} custid ilike '%$webvar{custid}%')".
+        " $sqlconcat (select $cols from searchme where $webvar{descexclude} description ilike '%$webvar{desc}%')".
+        " $sqlconcat (select $cols from searchme where $webvar{notesexclude} notes ilike '%$webvar{notes}%')";
+
+if ($webvar{alltypes} ne 'on') {
+  $sql .= " $sqlconcat (select $cols from searchme where $webvar{typeexclude} type in (";
+  foreach my $key (keys %webvar) {
+    $sql .= "'$1'," if $key =~ /type\[(..)\]/;
+  }
+  chop $sql;
+  $sql .= "))";
+}
+
+if ($webvar{allcities} ne 'on') {
+  $sql .= " $sqlconcat (select $cols from searchme where $webvar{cityexclude} city in (";
+  $sth = $ip_dbh->prepare("select city from cities where id=?");
+  foreach my $key (keys %webvar) {
+    if ($key =~ /city\[(\d+)\]/) {
+      $sth->execute($1);
+      my $city;
+      $sth->bind_columns(\$city);
+      $sth->fetch;
+      $city =~ s/'/''/;
+      $sql .= "'$city',";
+    }
+  }
+  chop $sql;
+  $sql .= "))";
+}
+
+
+# gotta find a way to search cleanly...
+
+#if (!(!$webvar{cidr} && !$webvar{custid} && !$webvar{desc} && !$webvar{notes} &&
+#       $webvar{alltypes} && $webvar{allcities})) {
+#  $sql .= " INTERSECT ";
+#}
+#
+#$sql .=
+# ($webvar{custid} eq '' ? '' : "SELECT cidr,custid,type,city,description FROM searchme WHERE custid LIKE '%$webvar{custid}%'").
+# ($webvar{desc} eq '' ? '' : " $sqlconcat SELECT cidr,custid,type,city,description FROM searchme WHERE description like '%$webvar{desc}%'").
+# ($webvar{notes} eq '' ? '' : " $sqlconcat SELECT cidr,custid,type,city,description FROM searchme WHERE notes like '%$webvar{notes}%'").
+#"";
+
+print $sql."\n";
+
+$sth = $ip_dbh->prepare($sql);
+$sth->execute;
+
+print "<table border=1>\n";
+while (my @data = $sth->fetchrow_array) {
+  print "<tr><td>$data[0]</td><td>$data[1]</td><td>$data[2]</td><td>$data[3]</td><td>$data[4]</td></tr>\n";
+}
+
+#print "<pre>\n";
+#foreach my $key (keys %webvar) {
+#  print "key: $key     value: -'$webvar{$key}'-\n";
+#}
+#print "</pre>\n";
 
 } else {