#!/usr/bin/perl # Check all customer IDs to see which are invalid ### # SVN revision info # $Date: 2005-02-07 17:00:47 +0000 (Mon, 07 Feb 2005) $ # SVN revision $Rev: 156 $ # Last update by $Author: kdeugau $ ### # Copyright (C) 2004 Kris Deugau use DBI; use IPDB 2.0 qw(:ALL); # We'll be hosing the server with several thousand queries. We # REALLY don't want the overhead and load of opening a new connection # for each query. #use CustIDCK; use NetAddr::IP; $priv1 = new NetAddr::IP '10.0.0.0/8'; $priv2 = new NetAddr::IP '172.16.0.0/12'; $priv3 = new NetAddr::IP '192.168.0.0/16'; print "Content-type: text/plain\n\n"; ($dbh,$errstr) = connectDB("ipdb", "ipdb", "ipdbpwd"); $IDH = DBI->connect ("DBI:Pg:host=newbilling;dbname=custids", "cidcheck", "c1dch4ck"); $sth = $dbh->prepare("select cidr,custid from searchme where not (custid='6750400') ". "and not (custid='STAFF') order by cidr"); $sth->execute; $IDS = $IDH->prepare("select custid from custid where custid=?"); $count = $bad = 0; while (@data = $sth->fetchrow_array) { $count++; $cidr = new NetAddr::IP $data[0]; if ($cidr->within($priv1) or $cidr->within($priv2) or $cidr->within($priv3)) { # no-op. we ignore these. } else { $IDS->execute($data[1]); $hr = $IDS->fetchrow_hashref(); if (!$hr->{custid}) { print " $data[0]\thas invalid CustID '$data[1]'\n"; $bad++; } $IDS->finish; } } $IDH->disconnect; $dbh->disconnect; print "$count customer blocks, $bad bad.\n"; exit 0; ### Ported subs of sorts from CustIDCK.pm