Changeset 207 for branches/new-search-20050223

Timestamp:

03/22/05 14:53:24 (20 years ago)

Author:

Kris Deugau

Message:

/branches/new-search-20050223

All search criteria complete.

File:

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

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

@@ -76 +76 @@
 } elsif ($webvar{stype} eq 'c') {
   # Complex search.
-  print "Complex Search...............\n";
 
   # Several major cases, and a whole raft of individual cases.
@@ -84 +83 @@
   # requests.
 
-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";
-}
+  my $sqlconcat;
+  if ($webvar{which} eq 'all') {
+    # Must match *all* specified criteria.      ## use INTERSECT or EXCEPT
+    $sqlconcat = "INTERSECT";
+  } elsif ($webvar{which} eq 'any') {
+    # Match on any specified criteria           ## use UNION
+    $sqlconcat = "UNION";
+  } else {
+    # We can't get here.  PTHBTT!
+    printAndExit "PTHBTT!!  Your search has been rejected due to Microsoft excuse #4432: ".
+        "Not enough mana";
+  }
 
 # We actually construct a monster SQL statement for all criteria.
 # Iff something has been entered, it will be used as a filter.
 
-# Columns actually returned.  Slightly better than hardcoding it
-# in each (sub)select
-my $cols = "cidr,custid,type,city,description";
-
-# First chunk of SQL.  Filter on custid, description, and notes as necessary.
-my $sql = "(select $cols from searchme where $webvar{custexclude} custid ilike '%$webvar{custid}%')".
+  # Columns actually returned.  Slightly better than hardcoding it
+  # in each (sub)select
+  my $cols = "cidr,custid,type,city,description";
+
+  # First chunk of SQL.  Filter on custid, description, and notes as necessary.
+  my $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}%'").
-#"";
-
-my $offset = ($webvar{page}-1)*$RESULTS_PER_PAGE;
-
-print $sql;
-
-my $count = countRows($sql);
-$sql .= " order by cidr limit $RESULTS_PER_PAGE offset $offset";
-queryResults($sql, $webvar{page}, $count);
-
-
-print "<pre>\n";
-foreach my $key (keys %webvar) {
-  print "key: $key      value: -'$webvar{$key}'-\n";
-}
-print "</pre>\n";
-
-} else {
+  # If we're not supposed to search for all types, search for the selected types.
+  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 we're not supposed to search for all cities, search for the selected cities.
+  # This could be vastly improved with proper foreign keys in the database.
+  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 .= "))";
+  }
+
+  ## CIDR query options.
+  $webvar{cidr} =~ s/\s+//;     # Hates the nasty spaceseseses we does.
+  if ($webvar{cidr} =~ /\//) {
+    # 209.91.179/26 should show all /26 subnets in 209.91.179
+    my ($net,$maskbits) = split /\//, $webvar{cidr};
+    if ($webvar{cidr} =~ /^(\d{1,3}\.){3}\d{1,3}\/\d{2}$/) {
+      # /0->/9 are silly to worry about right now.  I don't think
+      # we'll be getting a class A anytime soon.  <g>
+      $sql .= " $sqlconcat (select $cols from searchme where ".
+        "$webvar{cidrexclude} cidr='$webvar{cidr}')";
+    } else {
+      # Partial match;  beginning of subnet and maskbits are provided
+      # Show any blocks with the leading octet(s) and that masklength
+      $sql .= " $sqlconcat (select $cols from searchme where $webvar{cidrexclude} ".
+        "(text(cidr) like '$net%' and masklen(cidr)=$maskbits))";
+    }
+  } elsif ($webvar{cidr} =~ /^(\d{1,3}\.){3}\d{1,3}$/) {
+    # Specific IP address match.  Will show either a single netblock,
+    # or a static pool plus an IP.
+    $sql .= " $sqlconcat (select $cols from searchme where $webvar{cidrexclude} ".
+        "cidr >>= '$webvar{cidr}')";
+  } elsif ($webvar{cidr} =~ /^\d{1,3}(\.(\d{1,3}(\.(\d{1,3}\.?)?)?)?)?$/) {
+    # Leading octets in CIDR
+    $sql .= " $sqlconcat (select $cols from searchme where $webvar{cidrexclude} ".
+        "text(cidr) like '$webvar{cidr}%')";
+  } else {
+    # This shouldn't happen, but if it does, whoever gets it deserves what they get...
+    printAndExit("Invalid netblock query.");
+  } # done with CIDR query options.
+
+  # Find the offset for multipage results
+  my $offset = ($webvar{page}-1)*$RESULTS_PER_PAGE;
+
+  # Find out how many rows the "core" query will return.
+  my $count = countRows($sql);
+
+  if ($count == 0) {
+    printError "No matches found.  Try eliminating one of the criteria,".
+        " or making one or more criteria more general.";
+  } else {
+    # Add the limit/offset clauses
+    $sql .= " order by cidr limit $RESULTS_PER_PAGE offset $offset";
+    # And tell the user.
+    print "<div class=heading>Searching...............</div>\n";
+    queryResults($sql, $webvar{page}, $count);
+  }
+
+} else { # how script was called.  General case is to show the search criteria page.
+
   # Display search page.  We have to do this here, because otherwise
   # we can't retrieve data from the database for the types and cities.  >:(
@@ -210 +230 @@
   $citytable .= "</table>\n";
 
-
   $html =~ s/\$\$TYPELIST\$\$/$typetable/;
   $html =~ s/\$\$CITYLIST\$\$/$citytable/;
@@ -216 +235 @@
   print $html;
 }
-
-#  # This is unpossible!
-#  print "This is UnPossible!  You can't get here!\n";
 
 # Shut down and clean up.
@@ -226 +242 @@
 exit 0;
 
+
+# viewBy()
+# The quick search
+# Takes a category descriptor and a query string
+# Creates appropriate SQL to run the search and display the results
+# with queryResults()
 sub viewBy($$) {
   my ($category,$query) = @_;
@@ -232 +254 @@
   my $sql;
 
-#print "<pre>\n";
-
-#print "start querysub: query '$query'\n";
-# this may happen with more than one subcategory.  Unlikely, but possible.
-
   # Calculate start point for LIMIT clause
   my $offset = ($webvar{page}-1)*$RESULTS_PER_PAGE;
 
 # Possible cases:
-# 1) Partial IP/subnet.  Treated as "first-three-octets-match" in old IPDB,
-#    I should be able to handle it similarly here.
-# 2a) CIDR subnet.  Treated more or less as such in old IPDB.
-# 2b) CIDR netmask.  Not sure how it's treated.
-# 3) Customer ID.  Not handled in old IPDB
-# 4) Description.
+# 1) Partial IP/subnet.  Treated as "octet-prefix".
+# 2a) CIDR subnet.  Exact match.
+# 2b) CIDR netmask.  YMMV but it should be octet-prefix-with-netmask
+#       (ie, all matches with the octet prefix *AND* that netmask)
+# 3) Customer ID.  "Match-any-segment"
+# 4) Description.  "Match-any-segment"
 # 5) Invalid data which might be interpretable as an IP or something, but
 #    which probably shouldn't be for reasons of sanity.
@@ -355 +372 @@
 
 
-# Display certain types of search query.  Note that this can't be
-# cleanly reused much of anywhere else as the data isn't neatly tabulated.
-# This is tied to the search sub tightly enough I may just gut it and provide
-# more appropriate tables directly as needed.
+# queryResults()
+# Display search queries based on the passed SQL.
+# Takes SQL, page number (for multipage search results), and a total count.
 sub queryResults($$$) {
   my ($sql, $pageNo, $rowCount) = @_;
@@ -446 +462 @@
   return $a[0];
 }
-