Callsign Lookup Action


See also:
  • Since: Wikka 1.2p1
This is the development page for the callsign lookup action.
 


A front-end that performs lookups of U.S. amateur radio callsigns against the FCC database. This action requires a local DB copy of the FCC amateur radio callsign database (files are available from the FCC; see below for instructions).

Screenshot


CallsignLookup action screenshot
 


Usage

{{callsignlookup}}

Code

Save the following as plugins/actions/callsignlookup.php:
/*
 * This program is free software; you can redistribute it and/or
 * modify it under the terms of the GNU General Public License as
 * published by the Free Software Foundation; either version 2 of the
 * License, or (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful, but
 * WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
 * General Public Licence for more details:
 *
 *                http://www.gnu.org/copyleft/gpl.html
 *
 * @author        {@link http://wikkawiki.org/BrianKoontz Brian Koontz} <brian@pongonova.net>
 * @copyright    Copyright (c) 2009, Brian Koontz <brian@wikkawiki.org>
 * @name        CallsignLookupAction
 * @package        Actions
 * @license        http://www.gnu.org/copyleft/gpl.html
 * @since        Wikka 1.2
 */
<?php
if(isset($_POST['callsign']))
{
    $callsign = strtoupper($this->GetSafeVar('callsign', 'post'));
    $query = "
        SELECT callsign, operator_class, previous_callsign, grant_date, expired_date, cancellation_date, effective_date, last_action_date, entity_name, first_name, mi, last_name, suffix, street_address, city, state, zip_code, po_box
        FROM am_db.AM INNER JOIN
            (am_db.HD INNER JOIN am_db.EN ON
            am_db.HD.unique_system_identifier=am_db.EN.unique_system_identifier)
        ON am_db.AM.unique_system_identifier=am_db.EN.unique_system_identifier
        WHERE am_db.AM.callsign='"
.mysql_real_escape_string($callsign)."'";
    if($records = $this->LoadAll($query))
    {
        foreach($records as $record)
        {
            if(!empty($record['cancellation_date']))
            {
                continue;
            }
            $operator_class = $record['operator_class'];
            if(!empty($operator_class))
            {
                switch($operator_class)
                {
                    case 'A': $operator_class = "ADVANCED"; break;
                    case 'E': $operator_class = "EXTRA"; break;
                    case 'G': $operator_class = "GENERAL"; break;
                    case 'N': $operator_class = "NOVICE"; break;
                    case 'P': $operator_class = "TECH PLUS"; break;
                    case 'T': $operator_class = "TECHNICIAN"; break;
                    default:  $operator_class = "UNKNOWN";
                }
            }
            $previous_callsign = $record['previous_callsign'];
            $grant_date = $record['grant_date'];
            $expired_date = $record['expired_date'];
            $cancellation_date = $record['cancellation_date'];
            $effective_date = $record['effective_date'];
            $last_action_date = $record['last_action_date'];
            $entity_name = $record['entity_name'];
            $first_name = $record['first_name'];
            $mi = $record['mi'];
            $last_name = $record['last_name'];
            $suffix = $record['suffix'];
            $street_address = $record['street_address'];
            $city = $record['city'];
            $state = $record['state'];
            $zip_code = $record['zip_code'];
            $po_box = $record['po_box'];
        }
    }
    else
    {
        $callsign_error = "<div style=\"color:red\">Callsign not found</div>";
    }
}

?>
<?php echo $this->FormOpen('callsignlookup', 'post'); ?>
Callsign: <input type="text" name="callsign" value="<?php echo $callsign; ?>" size="10" maxlength="10"/>
<input type="submit" name="submit" value="Search"/>
<br/>
<?php echo $callsign_error; ?>
<?php echo $this->FormClose(); ?>
<br/><br/>
<table>
    <tr>
        <td>Call</td>
        <td>Class</td>
        <td>Previous Call</td>
    </tr>
    <tr>
        <td><div style="font-size:150%"><?php echo $callsign; ?></div></td>
        <td><div style="font-weight:bold"><?php echo $operator_class; ?></td>
        <td><div style="font-size:150%"><?php echo $previous_callsign; ?></div></td>
    </tr>
    <tr>
        <td colspan="4">Entity Name</td>
    </tr>
    <tr>
        <td colspan="4"><div style="font-size:150%"><?php echo $entity_name; ?></div></td>
    </tr>
    <tr>
        <td>Name: </td>
        <td colspan="3"><div style="font-weight:bold"><?php echo $first_name." ".$mi." ".$last_name." ".$suffix; ?></div></td>
    </tr>
    <tr>
        <td>Address: </td>
        <td colspan="3"><div style="font-weight:bold"><?php echo $street_address; ?></div></td>
    </tr>
    <tr>
        <td>P.O. Box: </td>
        <td colspan="3"><div style="font-weight:bold"><?php echo $po_box; ?></div></td>
    </tr>
    <tr>
        <td>City: </td>
        <td colspan="3"><div style="font-weight:bold"><?php echo $city; ?></div></td>
    </tr>
    <tr>
        <td>State: </td>
        <td><div style="font-weight:bold"><?php echo $state; ?></div></td>
        <td>Zip Code: </td>
        <td><div style="font-weight:bold"><?php echo $zip_code; ?></div></td>
    </tr>
    <tr>
        <td>Grant Date: </td>
        <td><div style="font-weight:bold"><?php echo $grant_date; ?></div></td>
        <td>Effective Date:</td>
        <td><div style="font-weight:bold"><?php echo $effective_date; ?></div></td>
    </tr>
    <tr>
        <td>Expired Date: </td>
        <td><div style="font-weight:bold"><?php echo $expired_date; ?></div></td>
    </tr>
    <tr>
        <td>Cancel Date: </td>
        <td><div style="font-weight:bold"><?php echo $cancellation_date; ?></div></td>
        <td>Last Action Date: </td>
        <td><div style="font-weight:bold"><?php echo $last_action_date; ?></div></td>
    </tr>
</table>


Setting up the database

  1. Download the FCC weekly database (the "Licenses" link under "Amateur Radio Service" on the right sidebar).
  1. Only certain tables (AM, EN, HD) are used for this action. I've posted a perl script that performs the necessary table and field creations. If you choose to roll your own, read the comments at the start of the script for the fields that are required.
  1. To populate the database, create a new MySQL database, and modify the "DB access" parameters in the script below accordingly. Copy this perl script into the directory into which you extracted the DB files, and run. (Note: This will take a while...it took about 1/2 hour on my 733MHz iBook.)

#! /usr/bin/perl
#
# populateDB.pl - Populate various tables with ham radio callsign DB
# data. The following tables and fields must be defined:
#
# table AM
#       unique_system_identifier  numeric(9,0)         not null,
#       callsign                  char(10)             null,
#       operator_class            char(1)              null,
#       trustee_callsign          char(10)             null,
#       trustee_indicator         char(1)              null,
#       previous_callsign         char(10)             null,
#       trustee_name              varchar(50)          null
#
# table EN
#       unique_system_identifier  numeric(9,0)         not null,
#       entity_name               varchar(200)         null,
#       first_name                varchar(20)          null,
#       mi                        char(1)              null,
#       last_name                 varchar(20)          null,
#       suffix                    char(3)              null,
#       email                     varchar(50)          null,
#       street_address            varchar(60)          null,
#       city                      varchar(20)          null,
#       state                     char(2)              null,
#       zip_code                  char(9)              null,
#       po_box                    varchar(20)          null,
#
# table HD
#       unique_system_identifier  numeric(9,0)         not null,
#       license_status            char(1)              null,
#       grant_date                char(10)             null,
#       expired_date              char(10)             null,
#       cancellation_date         char(10)             null,
#       effective_date            char(10)             null,
#       last_action_date          char(10)             null,
#
# This program is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public License as
# published by the Free Software Foundation; either version 2 of the
# License, or (at your option) any later version.
#
# This program is distributed in the hope that it will be useful, but
# WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
# General Public Licence for more details:
#
#                http://www.gnu.org/copyleft/gpl.html
#
# Author: Brian Koontz <brian@wikkawiki.org>, Copyright 2009
#####################################################################

use strict;
use DBI();

### Modify these ###

# DB access #
my $am_database = "am_db";
my $am_host = "127.0.0.1";
my $am_username = "root";
my $am_password = "root";
my $RaiseError = 1;

### End modifications ###

my $dbh = DBI->connect("DBI:mysql:database=$am_database; host=$am_host",
                            "$am_username", "$am_password",
                            {'RaiseError' => $RaiseError});

# Create tables
$dbh->do("CREATE TABLE IF NOT EXISTS AM (
            unique_system_identifier  numeric(9,0)         not null,
            callsign                  char(10)             null,
            operator_class            char(1)              null,
            trustee_callsign          char(10)             null,
            trustee_indicator         char(1)              null,
            previous_callsign         char(10)             null,
            trustee_name              varchar(50)          null,
            PRIMARY KEY (unique_system_identifier))
            TYPE=MyISAM"
);

$dbh->do("CREATE TABLE IF NOT EXISTS EN (
           unique_system_identifier  numeric(9,0)         not null,
           entity_name               varchar(200)         null,
           first_name                varchar(20)          null,
           mi                        char(1)              null,
           last_name                 varchar(20)          null,
           suffix                    char(3)              null,
           email                     varchar(50)          null,
           street_address            varchar(60)          null,
           city                      varchar(20)          null,
           state                     char(2)              null,
           zip_code                  char(9)              null,
           po_box                    varchar(20)          null,
           PRIMARY KEY (unique_system_identifier))
           TYPE=MyISAM"
);

$dbh->do("CREATE TABLE IF NOT EXISTS HD (
           unique_system_identifier  numeric(9,0)         not null,
           license_status            char(1)              null,
           grant_date                char(10)             null,
           expired_date              char(10)             null,
           cancellation_date         char(10)             null,
           effective_date            char(10)             null,
           last_action_date          char(10)             null,
           PRIMARY KEY (unique_system_identifier))
           TYPE=MyISAM"
);

# Populate AM table
my $table = "AM.dat";
open(IN, "<$table") or die "Can't open $table for reading!";
my $sth = $dbh->prepare("INSERT INTO AM (unique_system_identifier,
                                         callsign,
                                         operator_class,
                                         trustee_callsign,
                                         trustee_indicator,
                                         previous_callsign,
                                         trustee_name)
                                    VALUES(?,?,?,?,?,?,?)"
);
=pod
while(<IN>)
{
    chomp $_;
    my($z, $a, $z, $z, $b, $c, $z, $z, $d, $e,
       $z, $z, $z, $z, $z, $f, $z, $g) =
        split(/\|/, $_);
    $sth->execute($a, $b, $c, $d, $e, $f, $g);
}
=cut

close IN;

# Populate EN table
$table = "EN.dat";
open(IN, "<$table") or die "Can't open $table for reading!";
$sth = $dbh->prepare("INSERT INTO EN (unique_system_identifier,
                                      entity_name,
                                      first_name,
                                      mi,
                                      last_name,
                                      suffix,
                                      email,
                                      street_address,
                                      city,
                                      state,
                                      zip_code,
                                      po_box)
                                    VALUES(?,?,?,?,?,?,?,?,?,?,?,?)"
);
while(<IN>)
{
    chomp $_;
    my($z, $a, $z, $z, $z, $z, $z, $b, $c, $d, $e, $f, $z, $z, $g,
       $h, $i, $j, $k, $l, $z) =
        split(/\|/, $_);
    $sth->execute($a, $b, $c, $d, $e, $f, $g, $h, $i, $j, $k, $l);
}
close IN;

# Populate HD table
$table = "HD.dat";
open(IN, "<$table") or die "Can't open $table for reading!";
$sth = $dbh->prepare("INSERT INTO HD (unique_system_identifier,
                                      license_status,
                                      grant_date,
                                      expired_date,
                                      cancellation_date,
                                      effective_date,
                                      last_action_date)
                                    VALUES(?,?,?,?,?,?,?)"
);
=pod
while(<IN>)
{
    chomp $_;
    my($z, $a, $z, $z, $z, $b, $z, $c, $d, $e, $z, $z, $z, $z, $z, $z,
        $z, $z, $z, $z, $z, $z, $z, $z, $z, $z, $z, $z, $z, $z, $z,
        $z, $z, $z, $z, $z, $z, $z, $z, $z, $f, $g, $z) =
        split(/\|/, $_);
    $sth->execute($a, $b, $c, $d, $e, $f, $g);
}
close IN;
=cut


$dbh->disconnect;






CategoryUserContributions
There are no comments on this page.
Valid XHTML :: Valid CSS: :: Powered by WikkaWiki