Changeset 202 for branches/new-search-20050223
- Timestamp:
- 03/18/05 18:16:13 (20 years ago)
- Location:
- branches/new-search-20050223
- Files:
-
- 2 edited
Legend:
- Unmodified
- Added
- Removed
-
branches/new-search-20050223/cgi-bin/search.cgi
r201 r202 37 37 38 38 # Global variables 39 my $RESULTS_PER_PAGE = 50;39 my $RESULTS_PER_PAGE = 10; 40 40 my %webvar = parse_post(); 41 41 cleanInput(\%webvar); … … 57 57 # Search term entered. Display matches. 58 58 # We should really sanitize $webvar{input}, no? 59 # need to munge up data for $webvar{searchfor}, rather than breaking things here.60 59 my $searchfor; 61 60 # Chew up leading and trailing whitespace 62 61 $webvar{input} =~ s/^\s+//; 63 62 $webvar{input} =~ s/\s+$//; 64 if ($webvar{input} =~ /^[\d\.]+(\/\d{ 2})?$/) {63 if ($webvar{input} =~ /^[\d\.]+(\/\d{1,3})?$/) { 65 64 # IP addresses should only have numbers, digits, and maybe a slash+netmask 66 65 $searchfor = "ipblock"; … … 84 83 # Individual cases are for the CIDR/IP, CustID, Description, Notes, and individual type 85 84 # requests. 86 87 my $sql = "SELECT cidr,custid,type,city,description FROM searchme";88 85 89 86 my $sqlconcat; … … 101 98 } 102 99 103 # Cases: 104 # -> No search terms. Display everything. 105 # -> One or more terms (cidr/custid/desc/notes) 106 107 # -> Type/city restrictions 108 100 # We actually construct a monster SQL statement for all criteria. 101 # Iff something has been entered, it will be used as a filter. 102 103 # Columns actually returned. Slightly better than hardcoding it 104 # in each (sub)select 109 105 my $cols = "cidr,custid,type,city,description"; 110 106 111 $sql = "(select $cols from searchme where $webvar{custexclude} custid ilike '%$webvar{custid}%')". 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}%')". 112 109 " $sqlconcat (select $cols from searchme where $webvar{descexclude} description ilike '%$webvar{desc}%')". 113 110 " $sqlconcat (select $cols from searchme where $webvar{notesexclude} notes ilike '%$webvar{notes}%')"; … … 153 150 #""; 154 151 155 print $sql."\n"; 156 157 $sth = $ip_dbh->prepare($sql); 158 $sth->execute; 159 160 print "<table border=1>\n"; 161 while (my @data = $sth->fetchrow_array) { 162 print "<tr><td>$data[0]</td><td>$data[1]</td><td>$data[2]</td><td>$data[3]</td><td>$data[4]</td></tr>\n"; 163 } 164 165 #print "<pre>\n"; 166 #foreach my $key (keys %webvar) { 167 # print "key: $key value: -'$webvar{$key}'-\n"; 168 #} 169 #print "</pre>\n"; 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"; 170 166 171 167 } else { … … 258 254 print qq(<div class="heading">Showing all netblock and static-IP allocations</div><br>\n); 259 255 $sql = "select * from searchme"; 260 my $count = countRows( "select count(*) from ($sql) foo");256 my $count = countRows($sql); 261 257 $sql .= " order by cidr limit $RESULTS_PER_PAGE offset $offset"; 262 258 queryResults($sql, $webvar{page}, $count); … … 269 265 # as we have non-numeric custIDs in the legacy data. :/ 270 266 $sql = "select * from searchme where custid ilike '%$query%'"; 271 my $count = countRows( "select count(*) from ($sql) foo");267 my $count = countRows($sql); 272 268 $sql .= " order by cidr limit $RESULTS_PER_PAGE offset $offset"; 273 269 queryResults($sql, $webvar{page}, $count); … … 278 274 # Query based on description (includes "name" from old DB). 279 275 $sql = "select * from searchme where description ilike '%$query%'"; 280 my $count = countRows( "select count(*) from ($sql) foo");276 my $count = countRows($sql); 281 277 $sql .= " order by cidr limit $RESULTS_PER_PAGE offset $offset"; 282 278 queryResults($sql, $webvar{page}, $count); … … 301 297 $sql = "select * from searchme where text(cidr) like '$net%' and ". 302 298 "text(cidr) like '%$maskbits'"; 303 my $count = countRows( "select count(*) from ($sql) foo");299 my $count = countRows($sql); 304 300 $sql .= " order by cidr limit $RESULTS_PER_PAGE offset $offset"; 305 301 queryResults($sql, $webvar{page}, $count); … … 318 314 } 319 315 } 320 } elsif ($query =~ /^ \d{1,3}\.\d{1,3}\.\d{1,3}\.?$/) {321 print "Finding matches w here the first three octets are$query<br>\n";316 } elsif ($query =~ /^(\d{1,3}\.){1,3}\d{1,3}\.?$/) { 317 print "Finding matches with leading octet(s) $query<br>\n"; 322 318 $sql = "select * from searchme where text(cidr) like '$query%'"; 323 my $count = countRows( "select count(*) from ($sql) foo");319 my $count = countRows($sql); 324 320 $sql .= " order by cidr limit $RESULTS_PER_PAGE offset $offset"; 325 321 queryResults($sql, $webvar{page}, $count); … … 375 371 376 372 while (my @data = $sth->fetchrow_array) { 373 377 374 # cidr,custid,type,city,description,notes 378 # Fix up types from pools (which are single-char) 379 # Fixing the database would be... painful. :( 380 ##fixme LEGACY CODE 381 if ($data[2] =~ /^[cdsmw]$/) { 382 $data[2] .= 'i'; 383 } 384 my @row = (qq(<a href="/ip/cgi-bin/main.cgi?action=edit&block=$data[0]">$data[0]</a>), 375 # Another bit of HairyPerl(TM) to prefix subblocks with "Sub" 376 my @row = (($data[2] =~ /^.r$/ ? 'Sub ' : ''). 377 qq(<a href="/ip/cgi-bin/main.cgi?action=edit&block=$data[0]">$data[0]</a>), 385 378 $data[1], $disp_alloctypes{$data[2]}, $data[3], $data[4]); 386 379 # Allow listing of pool if desired/required. … … 399 392 400 393 my $upper = $offset+$count; 401 print "<tr><td colspan=10 bgcolor=white class=regular>Records found: $rowCount<br><i>Displaying: $offset- $upper</i></td></tr>\n";394 print "<tr><td colspan=10 bgcolor=white class=regular>Records found: $rowCount<br><i>Displaying: ".($offset+1)." - $upper</i></td></tr>\n"; 402 395 print "</table></center>\n"; 403 396 … … 410 403 print "<b>$i </b>\n"; 411 404 } else { 412 print qq(<a href="/ip/cgi-bin/main.cgi?page=$i&input=$webvar{input}&action=search&searchfor=$webvar{searchfor}">$i</a> \n); 405 print qq(<a href="/ip/cgi-bin/search.cgi?page=$i&stype=$webvar{stype}&); 406 if ($webvar{stype} eq 'c') { 407 print "cidr=$webvar{cidr}&custid=$webvar{custid}&desc=$webvar{desc}&". 408 "notes=$webvar{notes}&which=$webvar{which}&alltypes=$webvar{alltypes}&". 409 "allcities=$webvar{allcities}&"; 410 foreach my $key (keys %webvar) { 411 if ($key =~ /^(?:type|city)\[/) { 412 print "$key=$webvar{$key}&"; 413 } 414 } 415 } else { 416 print "input=$webvar{input}&"; 417 } 418 print qq(">$i</a> \n); 413 419 } 414 420 } … … 430 436 431 437 432 # Return first element of passed SQL query 438 # Return count of rows to be returned in a "real" query 439 # with the passed SQL statement 433 440 sub countRows($) { 434 my $sth = $ip_dbh->prepare($_[0]); 441 # Note that the "as foo" is required 442 my $sth = $ip_dbh->prepare("select count(*) from ($_[0]) as foo"); 435 443 $sth->execute(); 436 444 my @a = $sth->fetchrow_array(); -
branches/new-search-20050223/compsearch.html
r197 r202 6 6 <td colspan=2>Match on:<input type=radio checked name=which value="all">All 7 7 <input type=radio name=which value="any">Any 8 < div align=right><input type=submit value="Search Now"></div>8 <td colspan=2 align=right><input type=submit value="Search Now"></td> 9 9 </td> 10 10 <tr class="color1"> 11 <td>IP/netblock:</td><td><input name=cidr></td> 11 <td>Exclude?<input type=checkbox name=cidrexclude value='not'></td> 12 <td>IP/netblock:</td><td colspan=2><input name=cidr></td> 12 13 </tr><tr class="color2"> 13 <td>CustID:</td><td><input name=custid></td> 14 <td>Exclude?<input type=checkbox name=custexclude value='not'></td> 15 <td>CustID:</td><td colspan=2><input name=custid></td> 14 16 </tr><tr class="color1"> 15 <td>Description:</td><td><input name=desc></td> 17 <td>Exclude?<input type=checkbox name=descexclude value='not'></td> 18 <td>Description:</td><td colspan=2><input name=desc></td> 16 19 </tr><tr class="color2"> 17 <td>Notes:</td><td><input name=notes></td> 20 <td>Exclude?<input type=checkbox name=notesexclude value='not'></td> 21 <td>Notes:</td><td colspan=2><input name=notes></td> 18 22 </tr><tr class="color1"> 19 <td>Types:</td><td><input type=checkbox name=alltypes checked>Show all types 23 <td>Exclude?<input type=checkbox name=typeexclude value='not'></td> 24 <td>Types:</td><td colspan=2><input type=checkbox name=alltypes checked>Show all types 20 25 $$TYPELIST$$</td> 21 26 </tr><tr class="color2"> 22 <td>Cities:</td><td><input type=checkbox name=allcities checked>Show all cities 27 <td>Exclude?<input type=checkbox name=cityexclude value='not'></td> 28 <td>Cities:</td><td colspan=2><input type=checkbox name=allcities checked>Show all cities 23 29 $$CITYLIST$$</td> 24 30 </tr> 25 31 <input type=hidden name=stype value=c> 32 <input type=hidden name=page value=1> 26 33 </table> 27 34 </form>
Note:
See TracChangeset
for help on using the changeset viewer.