Thank you very much. -- Richard Cook [EMAIL PROTECTED] Tel: 705-497-9320 - ext 2010
-----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Scott Lykens Sent: Friday, September 24, 2004 11:03 AM To: Asterisk Users Mailing List - Non-Commercial Discussion Subject: Re: [Asterisk-Users] Billing Fun - anybody know where to get aNPA/NXXdb? On Fri, 24 Sep 2004 15:02:53 +0200, Patrick <[EMAIL PROTECTED]> wrote: > Would you mind sharing the perl script and the database schema? Perl script and database layout are below. Its not pretty since I never intended it for external consumption but it does get the job done. If you unzip the files you get from the previous post I made you need to run this perl script against each file individually, ie script.pl esutld.txt I'm using npa-nxx as the key in the database as it is much much faster to query based on the key than to do a "npa=212 and nxx=555" type query with some other field as a key. On the P3-800 I'm hosting this on right now the difference was 20 fold, from 0.2s to 0.01s. Also, as far as I know, NANPA doesn't list individual carriers in NXXs that are split up, for example NXXs where several different carriers are assigned blocks of 1000 numbers. This database set up certainly doesn't take that into account. I would assume, however, that rate center information would be the same for all numbers in a particular NXX. Longer term, access to a LNP database may be needed to accurately determine rate centers. For example, in my town, State College, PA, the A and B cellular carriers do not have exchanges in the State College rate center but rather in nearby rate centers that are local calls. The A carrier is in Boalsburg and the B carrier is in Bellefonte, each a distinct rate center, but it is possible to port numbers between them. This means that someone in an outlying rate center here, for example, Port Matilda, could believe they are making a local call to a Bellefonte mobile number but since that number has been ported to the other mobile carrier it becomes a regional toll call to Boalsburg. I don't know how the big telcos are dealing with this but I can't imagine they're giving up the opportunity for revenue. sl -- perl script #!/usr/bin/perl use DBI; my $dbh = DBI->connect("DBI:mysql:pbx:localhost", "user", "pass") or die $DBI::errstr; my $sth; open (IN,"$ARGV[0]"); foreach (<IN>) { s/\x22//g; @npanxx = split("\t"); if ($npanxx[0] eq "State") { next; } # print "$npanxx[1] / $npanxx[4], $npanxx[0] / $npanxx[3] ($npanxx[2]) $npanxx[5] / $npanxx[7]\n"; $sth = $dbh->prepare("INSERT INTO npanxx VALUES (\"$npanxx[0]\", \"$npanxx[1]\", \"$npanxx[2]\", \"$npanxx[3]\", \"$npanxx[4]\", \"$npanxx[5]\", \"$npanxx[7]\" , NULL)"); $rv = $sth->execute(); } -- MySQL Table CREATE TABLE npanxx ( state CHAR(2), npanxx CHAR(7) NOT NULL PRIMARY KEY, ocn CHAR(4), company CHAR(52), ratecenter CHAR(11), switch CHAR(11), usetype CHAR(2), timestamp TIMESTAMP); _______________________________________________ Asterisk-Users mailing list [EMAIL PROTECTED] http://lists.digium.com/mailman/listinfo/asterisk-users To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users _______________________________________________ Asterisk-Users mailing list [EMAIL PROTECTED] http://lists.digium.com/mailman/listinfo/asterisk-users To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users