Ignore:
Timestamp:
03/22/05 14:53:24 (20 years ago)
Author:
Kris Deugau
Message:

/branches/new-search-20050223

All search criteria complete.

File:
1 edited

Legend:

Unmodified
Added
Removed
  • branches/new-search-20050223/cgi-bin/search.cgi

    r202 r207  
    7676} elsif ($webvar{stype} eq 'c') {
    7777  # Complex search.
    78   print "Complex Search...............\n";
    7978
    8079  # Several major cases, and a whole raft of individual cases.
     
    8483  # requests.
    8584
    86 my $sqlconcat;
    87 if ($webvar{which} eq 'all') {
    88   # Must match *all* specified criteria.        ## use INTERSECT or EXCEPT
    89 $sqlconcat = "INTERSECT";
    90 #  $sqlconcat = "and";
    91 } elsif ($webvar{which} eq 'any') {
    92   # Match on any specified criteria             ## use UNION
    93 $sqlconcat = "UNION";
    94 #  $sqlconcat="or";
    95 } else {
    96   # We can't get here.  PTHBTT!
    97   printAndExit "PTHBTT!!  Your search has been rejected due to Microsoft excuse #4432:  Not enough mana";
    98 }
     85  my $sqlconcat;
     86  if ($webvar{which} eq 'all') {
     87    # Must match *all* specified criteria.      ## use INTERSECT or EXCEPT
     88    $sqlconcat = "INTERSECT";
     89  } elsif ($webvar{which} eq 'any') {
     90    # Match on any specified criteria           ## use UNION
     91    $sqlconcat = "UNION";
     92  } else {
     93    # We can't get here.  PTHBTT!
     94    printAndExit "PTHBTT!!  Your search has been rejected due to Microsoft excuse #4432: ".
     95        "Not enough mana";
     96  }
    9997
    10098# We actually construct a monster SQL statement for all criteria.
    10199# Iff something has been entered, it will be used as a filter.
    102100
    103 # Columns actually returned.  Slightly better than hardcoding it
    104 # in each (sub)select
    105 my $cols = "cidr,custid,type,city,description";
    106 
    107 # First chunk of SQL.  Filter on custid, description, and notes as necessary.
    108 my $sql = "(select $cols from searchme where $webvar{custexclude} custid ilike '%$webvar{custid}%')".
     101  # Columns actually returned.  Slightly better than hardcoding it
     102  # in each (sub)select
     103  my $cols = "cidr,custid,type,city,description";
     104
     105  # First chunk of SQL.  Filter on custid, description, and notes as necessary.
     106  my $sql = "(select $cols from searchme where $webvar{custexclude} custid ilike '%$webvar{custid}%')".
    109107        " $sqlconcat (select $cols from searchme where $webvar{descexclude} description ilike '%$webvar{desc}%')".
    110108        " $sqlconcat (select $cols from searchme where $webvar{notesexclude} notes ilike '%$webvar{notes}%')";
    111109
    112 if ($webvar{alltypes} ne 'on') {
    113   $sql .= " $sqlconcat (select $cols from searchme where $webvar{typeexclude} type in (";
    114   foreach my $key (keys %webvar) {
    115     $sql .= "'$1'," if $key =~ /type\[(..)\]/;
    116   }
    117   chop $sql;
    118   $sql .= "))";
    119 }
    120 
    121 if ($webvar{allcities} ne 'on') {
    122   $sql .= " $sqlconcat (select $cols from searchme where $webvar{cityexclude} city in (";
    123   $sth = $ip_dbh->prepare("select city from cities where id=?");
    124   foreach my $key (keys %webvar) {
    125     if ($key =~ /city\[(\d+)\]/) {
    126       $sth->execute($1);
    127       my $city;
    128       $sth->bind_columns(\$city);
    129       $sth->fetch;
    130       $city =~ s/'/''/;
    131       $sql .= "'$city',";
    132     }
    133   }
    134   chop $sql;
    135   $sql .= "))";
    136 }
    137 
    138 
    139 # gotta find a way to search cleanly...
    140 
    141 #if (!(!$webvar{cidr} && !$webvar{custid} && !$webvar{desc} && !$webvar{notes} &&
    142 #       $webvar{alltypes} && $webvar{allcities})) {
    143 #  $sql .= " INTERSECT ";
    144 #}
    145 #
    146 #$sql .=
    147 # ($webvar{custid} eq '' ? '' : "SELECT cidr,custid,type,city,description FROM searchme WHERE custid LIKE '%$webvar{custid}%'").
    148 # ($webvar{desc} eq '' ? '' : " $sqlconcat SELECT cidr,custid,type,city,description FROM searchme WHERE description like '%$webvar{desc}%'").
    149 # ($webvar{notes} eq '' ? '' : " $sqlconcat SELECT cidr,custid,type,city,description FROM searchme WHERE notes like '%$webvar{notes}%'").
    150 #"";
    151 
    152 my $offset = ($webvar{page}-1)*$RESULTS_PER_PAGE;
    153 
    154 print $sql;
    155 
    156 my $count = countRows($sql);
    157 $sql .= " order by cidr limit $RESULTS_PER_PAGE offset $offset";
    158 queryResults($sql, $webvar{page}, $count);
    159 
    160 
    161 print "<pre>\n";
    162 foreach my $key (keys %webvar) {
    163   print "key: $key      value: -'$webvar{$key}'-\n";
    164 }
    165 print "</pre>\n";
    166 
    167 } else {
     110  # If we're not supposed to search for all types, search for the selected types.
     111  if ($webvar{alltypes} ne 'on') {
     112    $sql .= " $sqlconcat (select $cols from searchme where $webvar{typeexclude} type in (";
     113    foreach my $key (keys %webvar) {
     114      $sql .= "'$1'," if $key =~ /type\[(..)\]/;
     115    }
     116    chop $sql;
     117    $sql .= "))";
     118  }
     119
     120  # If we're not supposed to search for all cities, search for the selected cities.
     121  # This could be vastly improved with proper foreign keys in the database.
     122  if ($webvar{allcities} ne 'on') {
     123    $sql .= " $sqlconcat (select $cols from searchme where $webvar{cityexclude} city in (";
     124    $sth = $ip_dbh->prepare("select city from cities where id=?");
     125    foreach my $key (keys %webvar) {
     126      if ($key =~ /city\[(\d+)\]/) {
     127        $sth->execute($1);
     128        my $city;
     129        $sth->bind_columns(\$city);
     130        $sth->fetch;
     131        $city =~ s/'/''/;
     132        $sql .= "'$city',";
     133      }
     134    }
     135    chop $sql;
     136    $sql .= "))";
     137  }
     138
     139  ## CIDR query options.
     140  $webvar{cidr} =~ s/\s+//;     # Hates the nasty spaceseseses we does.
     141  if ($webvar{cidr} =~ /\//) {
     142    # 209.91.179/26 should show all /26 subnets in 209.91.179
     143    my ($net,$maskbits) = split /\//, $webvar{cidr};
     144    if ($webvar{cidr} =~ /^(\d{1,3}\.){3}\d{1,3}\/\d{2}$/) {
     145      # /0->/9 are silly to worry about right now.  I don't think
     146      # we'll be getting a class A anytime soon.  <g>
     147      $sql .= " $sqlconcat (select $cols from searchme where ".
     148        "$webvar{cidrexclude} cidr='$webvar{cidr}')";
     149    } else {
     150      # Partial match;  beginning of subnet and maskbits are provided
     151      # Show any blocks with the leading octet(s) and that masklength
     152      $sql .= " $sqlconcat (select $cols from searchme where $webvar{cidrexclude} ".
     153        "(text(cidr) like '$net%' and masklen(cidr)=$maskbits))";
     154    }
     155  } elsif ($webvar{cidr} =~ /^(\d{1,3}\.){3}\d{1,3}$/) {
     156    # Specific IP address match.  Will show either a single netblock,
     157    # or a static pool plus an IP.
     158    $sql .= " $sqlconcat (select $cols from searchme where $webvar{cidrexclude} ".
     159        "cidr >>= '$webvar{cidr}')";
     160  } elsif ($webvar{cidr} =~ /^\d{1,3}(\.(\d{1,3}(\.(\d{1,3}\.?)?)?)?)?$/) {
     161    # Leading octets in CIDR
     162    $sql .= " $sqlconcat (select $cols from searchme where $webvar{cidrexclude} ".
     163        "text(cidr) like '$webvar{cidr}%')";
     164  } else {
     165    # This shouldn't happen, but if it does, whoever gets it deserves what they get...
     166    printAndExit("Invalid netblock query.");
     167  } # done with CIDR query options.
     168
     169  # Find the offset for multipage results
     170  my $offset = ($webvar{page}-1)*$RESULTS_PER_PAGE;
     171
     172  # Find out how many rows the "core" query will return.
     173  my $count = countRows($sql);
     174
     175  if ($count == 0) {
     176    printError "No matches found.  Try eliminating one of the criteria,".
     177        " or making one or more criteria more general.";
     178  } else {
     179    # Add the limit/offset clauses
     180    $sql .= " order by cidr limit $RESULTS_PER_PAGE offset $offset";
     181    # And tell the user.
     182    print "<div class=heading>Searching...............</div>\n";
     183    queryResults($sql, $webvar{page}, $count);
     184  }
     185
     186} else { # how script was called.  General case is to show the search criteria page.
     187
    168188  # Display search page.  We have to do this here, because otherwise
    169189  # we can't retrieve data from the database for the types and cities.  >:(
     
    210230  $citytable .= "</table>\n";
    211231
    212 
    213232  $html =~ s/\$\$TYPELIST\$\$/$typetable/;
    214233  $html =~ s/\$\$CITYLIST\$\$/$citytable/;
     
    216235  print $html;
    217236}
    218 
    219 #  # This is unpossible!
    220 #  print "This is UnPossible!  You can't get here!\n";
    221237
    222238# Shut down and clean up.
     
    226242exit 0;
    227243
     244
     245# viewBy()
     246# The quick search
     247# Takes a category descriptor and a query string
     248# Creates appropriate SQL to run the search and display the results
     249# with queryResults()
    228250sub viewBy($$) {
    229251  my ($category,$query) = @_;
     
    232254  my $sql;
    233255
    234 #print "<pre>\n";
    235 
    236 #print "start querysub: query '$query'\n";
    237 # this may happen with more than one subcategory.  Unlikely, but possible.
    238 
    239256  # Calculate start point for LIMIT clause
    240257  my $offset = ($webvar{page}-1)*$RESULTS_PER_PAGE;
    241258
    242259# Possible cases:
    243 # 1) Partial IP/subnet.  Treated as "first-three-octets-match" in old IPDB,
    244 #    I should be able to handle it similarly here.
    245 # 2a) CIDR subnet.  Treated more or less as such in old IPDB.
    246 # 2b) CIDR netmask.  Not sure how it's treated.
    247 # 3) Customer ID.  Not handled in old IPDB
    248 # 4) Description.
     260# 1) Partial IP/subnet.  Treated as "octet-prefix".
     261# 2a) CIDR subnet.  Exact match.
     262# 2b) CIDR netmask.  YMMV but it should be octet-prefix-with-netmask
     263#       (ie, all matches with the octet prefix *AND* that netmask)
     264# 3) Customer ID.  "Match-any-segment"
     265# 4) Description.  "Match-any-segment"
    249266# 5) Invalid data which might be interpretable as an IP or something, but
    250267#    which probably shouldn't be for reasons of sanity.
     
    355372
    356373
    357 # Display certain types of search query.  Note that this can't be
    358 # cleanly reused much of anywhere else as the data isn't neatly tabulated.
    359 # This is tied to the search sub tightly enough I may just gut it and provide
    360 # more appropriate tables directly as needed.
     374# queryResults()
     375# Display search queries based on the passed SQL.
     376# Takes SQL, page number (for multipage search results), and a total count.
    361377sub queryResults($$$) {
    362378  my ($sql, $pageNo, $rowCount) = @_;
     
    446462  return $a[0];
    447463}
    448 
Note: See TracChangeset for help on using the changeset viewer.