source: trunk/cgi-bin/checkcusts.pl@ 400

#!/usr/bin/perl
# Check all customer IDs to see which are invalid
###
# SVN revision info
# $Date: 2010-05-11 21:06:26 +0000 (Tue, 11 May 2010) $
# SVN revision $Rev: 400 $
# Last update by $Author: kdeugau $
###
# Copyright (C) 2004-2006 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=billing;dbname=custids", "cidcheck", "c1dch4ck");

$sth = $dbh->prepare("select distinct def_custid from alloctypes where listorder >=40");
$sth->execute;
while (@data = $sth->fetchrow_array) {
  push @def_custids, $data[0];
}
$sth = $dbh->prepare("select cidr,custid from searchme where not (custid='6750400') ".
        "and not (custid='STAFF') order by cidr");
#$sth = $dbh->prepare("select cidr,custid from searchme order by cidr");
$sth->execute;

$IDS = $IDH->prepare("select custid from custid where custid=?");

$count = $bad = 0;
while (@data = $sth->fetchrow_array) {
  $cidr = new NetAddr::IP $data[0];
  if ($cidr->within($priv1) or $cidr->within($priv2) or $cidr->within($priv3) or
        (grep /$data[1]/, @def_custids)) {
    # no-op.  we ignore these.
  } else {
    $count++;
    $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