Wiki source for CallsignLookupAction
=====Callsign Lookup Action=====
>>==See also:==
~-Documentation: CallsignLookupActionInfo
~-Since: Wikka 1.2p1
>>This is the development page for the callsign lookup action.::c::
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===
<<
{{image url="images/callsignlookup.jpg" alt="CallsignLookup action screenshot" title="CallsignLookup action screenshot"}}
<<::c::
===Usage===
##""{{callsignlookup}}""##
===Code===
Save the following as ##plugins/actions/callsignlookup.php##:
%%(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 [[http://wireless.fcc.gov/uls/index.htm?job=transaction&page=weekly | 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.//)
%%(perl)
#! /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
>>==See also:==
~-Documentation: CallsignLookupActionInfo
~-Since: Wikka 1.2p1
>>This is the development page for the callsign lookup action.::c::
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===
<<
{{image url="images/callsignlookup.jpg" alt="CallsignLookup action screenshot" title="CallsignLookup action screenshot"}}
<<::c::
===Usage===
##""{{callsignlookup}}""##
===Code===
Save the following as ##plugins/actions/callsignlookup.php##:
%%(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 [[http://wireless.fcc.gov/uls/index.htm?job=transaction&page=weekly | 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.//)
%%(perl)
#! /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
