#!/usr/bin/env bash

: <<'END'
hamdb - https://github.com/k3ng/hamdb
modified for MariaDB > version 10.5

Documentation:  https://blog.radioartisan.com/fcc-callsign-database-script/
END
 
# Hamdb
# Copyright (C) 2001-2024 Anthony Good
 
# 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 License for more details.
 
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.
 
# You may contact the author at anthony.good@gmail.com

# To create the database user:
#
# sudo mysql 
#
# CREATE USER hamdb@localhost IDENTIFIED BY 'yourpassword'
#
# GRANT ALL PRIVILEGES ON *.* TO 'hamdb'@localhost IDENTIFIED BY 'yourpassword';


# 2024.12.09.1500 : Update from Chris Kinsler to to pull PO Box into address field if address field is blank

# 2024.12.10.0016 : Added importation of FCC HS table
#                   Added last_issued table which is used for lastissued commands
#                   Created history <callsign> command for searchign callsign history
#                   Created lastissued command to display the most recently issued licenses
#                   Created lastissued <state> command to show most recently issued licenses in a state
#                   Created lastissued <zipcode> command to show most recently issued licenses in a zip code (partial zip codes are OK)

# 2024.12.18.2000 : wget stopped working for some unknown reason; updated the wget --user-agent and it started working again


STOREDIR=~/hamdb.temp
VERSION=2024.12.18.2000
CONFIGFILE=~/.hamdb.cnf
DEBUG=0
USE_CURL_NOT_WGET=0
WGET=/usr/bin/wget
CURL=/usr/bin/curl
UNZIP=/usr/bin/unzip
DAY=`date -d "-1 day" | tr '[:upper:]' '[:lower:]' | sed 's/\([a-z]*\).*/\1/'`

WGET_OPTIONS="-nd -d"
WGET_HEADER="Accept: text/html"
#WGET_USER_AGENT="Mozilla/5.0 (Macintosh; Intel Mac OS X 10.8; rv:21.0) Gecko/20100101 Firefox/21.0"
WGET_USER_AGENT="Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko)"

# if today is Monday, get the full file, otherwise get a smaller daily update file
if [ ${DAY} == sun ]; then
  FULLIMPORT=1
else
  FULLIMPORT=0
fi

if [ ${1} == full ]; then
  FULLIMPORT=1
fi

if [ ${1} == getfccfile ]; then
  FULLIMPORT=1
fi


if [ ${FULLIMPORT} -eq 1 ]; then
  FCC_FILE_LOCATION=https://data.fcc.gov/download/pub/uls/complete/l_amat.zip
  DOWNLOAD_FILE=l_amat.zip
else
  FCC_FILE_LOCATION=https://data.fcc.gov/download/pub/uls/daily/l_am_${DAY}.zip
  DOWNLOAD_FILE=l_am_${DAY}.zip
fi

if [ ${DEBUG} -eq 1 ]; then
  echo "In debug mode..."
fi
 
if [ -f ${CONFIGFILE} ]; then
  . ${CONFIGFILE}
else
  echo -n "Hamdb configuration file ${CONFIGFILE} not found.  Would you like to create the file? [y/N]"
  read userinput
  echo -n "Enter MySQL username: "
  read MYSQLUSERNAME
  echo -n "Enter MySQL user's password: "
  read MYSQLPASSWD
  #echo "show databases;" | mysql --user=${MYSQLUSERNAME} --password=${MYSQLPASSWD}
  echo "#!/bin/sh" > ${CONFIGFILE}
  echo "MYSQLUSERNAME=$MYSQLUSERNAME" >> ${CONFIGFILE}
  echo "MYSQLPASSWD=$MYSQLPASSWD" >> ${CONFIGFILE}
  chmod 700 ${CONFIGFILE}
fi
 
if [ ${DEBUG} -eq 1 ]; then
  echo "Username is ${MYSQLUSERNAME}"
  echo "Password is ${MYSQLPASSWD}"
  echo "\s" | mysql --user=${MYSQLUSERNAME} --password=${MYSQLPASSWD}|grep "Server version:" | awk '{print $3}'
fi

if [ ${1} = removedb ]; then
  echo "drop database fcc_amateur;"|mysql --user=${MYSQLUSERNAME} --password=${MYSQLPASSWD}
  exit 0
fi


 
if [ `echo "show databases;"|mysql --user=${MYSQLUSERNAME} --password=${MYSQLPASSWD}|grep -c fcc_amateur` -eq 0 ]; then
  echo "No database found; creating..."
  echo "create database fcc_amateur;"|mysql --user=${MYSQLUSERNAME} --password=${MYSQLPASSWD}
  echo "use fcc_amateur;create table en (fccid int not null, callsign varchar(8) not null, primary key(fccid),full_name varchar(128),first varchar(20),middle varchar(1), last varchar(20), address1 varchar(64), city varchar(20), state varchar(2), zip varchar(10), index idx_zip (zip), index idx_callsign (callsign));"|mysql --user=${MYSQLUSERNAME} --password=${MYSQLPASSWD}
  echo "use fcc_amateur;create table am (fccid int not null, callsign varchar(8) not null, primary key(fccid), class varchar(1), col4 varchar(1), col5 varchar(2), col6 varchar(3), former_call varchar(8), former_class varchar(1), index idx_callsign (callsign), index idx_class(class), index idx_former_call(former_call));"|mysql --user=${MYSQLUSERNAME} --password=${MYSQLPASSWD}
  echo "use fcc_amateur;create table hd (fccid int not null, callsign varchar(8) not null, primary key(fccid), status varchar(1), radio_service_code varchar(2), grant_date datetime, expired_data datetime, cancellation_date datetime, index idx_callsign (callsign), index idx_status (status) );"|mysql --user=${MYSQLUSERNAME} --password=${MYSQLPASSWD}
  echo "use fcc_amateur;create table hs (fccid int not null, callsign varchar(8) not null, action_date datetime, action_code varchar(8), index idx_action_code (action_code), index idx_fccid(fccid), index idx_callsign (callsign) );"|mysql --user=${MYSQLUSERNAME} --password=${MYSQLPASSWD}
fi

if [ `echo "use fcc_amateur;select COUNT(fccid) from en;"|mysql --user=${MYSQLUSERNAME} --password=${MYSQLPASSWD} -s` -eq 0 ]; then
  FULLIMPORT=1
  FCC_FILE_LOCATION=https://data.fcc.gov/download/pub/uls/complete//l_amat.zip
  DOWNLOAD_FILE=l_amat.zip
fi

if [ ${DEBUG} -eq 1 ]; then
  echo "FCC_FILE_LOCATION: $FCC_FILE_LOCATION"
  echo "DOWNLOAD_FILE: $DOWNLOAD_FILE"
  echo "DAY: $DAY"
  echo "FULLIMPORT: $FULLIMPORT"
fi

case "$1" in
 
  getfccfile)
      echo "Downloading database from FCC..."
      if [ $USE_CURL_NOT_WGET -eq 1 ]; then
        $CURL -O $FCC_FILE_LOCATION
      else
        $WGET $WGET_OPTIONS --header="$WGET_HEADER" --user-agent="$WGET_USER_AGENT" $FCC_FILE_LOCATION
      fi
  ;;
 
  full | populatedb | populatedatabase | updatedatabase | updatedb | update)
    if [ ! -d ${STOREDIR} ]; then
      mkdir ${STOREDIR}
    fi
    cd ${STOREDIR}
 
    if [ ! -f "${DOWNLOAD_FILE}" ]; then
      echo "Downloading database from FCC..."
      if [ $USE_CURL_NOT_WGET -eq 1 ]; then
        $CURL -O $FCC_FILE_LOCATION
      else
        #$WGET -nd -d --header="Accept: text/html" --user-agent="Mozilla/5.0 (Macintosh; Intel Mac OS X 10.8; rv:21.0) Gecko/20100101 Firefox/21.0" $FCC_FILE_LOCATION
        $WGET $WGET_OPTIONS --header="$WGET_HEADER" --user-agent="$WGET_USER_AGENT" $FCC_FILE_LOCATION
      fi
    fi
 
    if [ ! -f EN.dat ]; then
      echo "Unzipping database file EN.dat..."
      ${UNZIP} ./${DOWNLOAD_FILE} EN.dat
    fi
 
    if [ ! -f AM.dat ]; then
      echo "Unzipping database file AM.dat..."
      ${UNZIP} ./${DOWNLOAD_FILE} AM.dat
    fi
 
    if [ ! -f HD.dat ]; then
      echo "Unzipping database file HD.dat..."
      ${UNZIP} ./${DOWNLOAD_FILE} HD.dat
    fi

    if [ ! -f HS.dat ]; then
      echo "Unzipping database file HS.dat..."
      #${UNZIP} ./${DOWNLOAD_FILE} HS.dat
      ${UNZIP} -p ${DOWNLOAD_FILE} HS.dat | sed -e 's/[ \t]*\r$//' > HS.dat
    fi

    #if [ ! -f en_temp.txt ]; then
    #  echo "Creating temporary file for table en..."
    #  cat ./EN.dat |sed s/[\\\"]//g|awk -F "|" '{print "\""$2"\",\""$5"\",\""$8"\",\""$9"\",\""$10"\",\""$11"\",\""$16"\",\""$17"\",\""$18"\",\""$19"\""}'>./en_temp.txt
    #fi

    if [ ! -f en_temp.txt ]; then
      echo "Creating temporary file for table en..."
      cat ./EN.dat | sed s/[\\\"]//g | awk -F "|" '{
        address = $16;
        pobox = $20;
        if (address == "") {
            address = "PO Box " pobox;
        }
        print "\"" $2 "\",\"" $5 "\",\"" $8 "\",\"" $9 "\",\"" $10 "\",\"" $11 "\",\"" address "\",\"" $17 "\",\"" $18 "\",\"" $19 "\""
    }' > ./en_temp.txt
    fi
 
    if [ ! -f am_temp.txt ]; then
      echo "Creating temporary file for table am..."
      cat ./AM.dat |sed s/[\\\"]//g|awk -F "|" '{print "\""$2"\",\""$5"\",\""$6"\",\""$7"\",\""$8"\",\""$10"\",\""$16"\",\""$17"\""}'>./am_temp.txt
    fi
 
    if [ ! -f hd_temp.txt ]; then
      echo "Creating temporary file for table hd..."
      cat ./HD.dat |sed s/[\\\"]//g|awk -F "|" '{print "\""$2"\",\""$5"\",\""$6"\",\""$7"\",\""$8"\",\""$9"\",\""$10"\""}'>./hd_temp_temp.txt
      awk -F, '{
      # Remove quotes for processing
      gsub(/"/, "", $0);
      # Reformat dates (5th, 6th, and 7th fields) from MM/DD/YYYY to YYYY-MM-DD
      for (i=5; i<=7; i++) {
        split($i, date_parts, "/");
        $i = date_parts[3] "-" date_parts[1] "-" date_parts[2];
      }
      # Print the reformatted line with quotes restored
      print "\"" $1 "\",\"" $2 "\",\"" $3 "\",\"" $4 "\",\"" $5 "\",\"" $6 "\",\"" $7 "\""
      }' "./hd_temp_temp.txt" > "./hd_temp.txt"

    fi

    if [ ! -f hs_temp.txt ]; then
      echo "Creating temporary file for table hs..."

      awk -F "|" '{
        # Trim whitespace from all fields
        gsub(/^[ \t]+|[ \t]+$/, "", $2);
        gsub(/^[ \t]+|[ \t]+$/, "", $4);
        gsub(/^[ \t]+|[ \t]+$/, "", $5);
        gsub(/^[ \t]+|[ \t]+$/, "", $6);

        # Reformat date from MM/DD/YYYY to YYYY-MM-DD
        split($5, date_parts, "/");
        reformatted_date = date_parts[3] "-" date_parts[1] "-" date_parts[2];

        # Print fields in MySQL-compatible format (tab-separated)
        print $2 "\t" $4 "\t" reformatted_date "\t" $6;
      }' "./HS.dat" > "./hs_temp.txt"

    fi

    if [ ${FULLIMPORT} -eq 1 ]; then

      echo "Doing full database import..."
 
      echo "Creating temporary tables in Mysql..."
 
      echo "use fcc_amateur;drop table if exists en_temp;create table en_temp (fccid int not null, callsign varchar(8) not null, primary key(fccid),full_name varchar(128),first varchar(20),middle varchar(1), last varchar(20), address1 varchar(64), city varchar(20), state varchar(2), zip varchar(10), index idx_zip (zip), index idx_callsign (callsign));"|mysql --user=${MYSQLUSERNAME} --password=${MYSQLPASSWD}
 
      echo "use fcc_amateur;drop table if exists am_temp;create table am_temp (fccid int not null, callsign varchar(8) not null, primary key(fccid), class varchar(1), col4 varchar(1), col5 varchar(2), col6 varchar(3), former_call varchar(8), former_class varchar(1), index idx_callsign (callsign), index idx_class(class), index idx_former_call(former_call));"|mysql --user=${MYSQLUSERNAME} --password=${MYSQLPASSWD}
 
      echo "use fcc_amateur;drop table if exists hd_temp;create table hd_temp (fccid int not null, callsign varchar(8) not null, primary key(fccid), status varchar(1), radio_service_code varchar(2), grant_date datetime, expired_data datetime, cancellation_date datetime, index idx_callsign (callsign), index idx_status (status) );"|mysql --user=${MYSQLUSERNAME} --password=${MYSQLPASSWD}
      
      echo "use fcc_amateur;drop table if exists hs_temp;create table hs_temp (fccid int not null, callsign varchar(8) not null, index idx_fccid (fccid), action_date datetime, action_code varchar(8), index idx_action_code (action_code), index idx_callsign (callsign) );"|mysql --user=${MYSQLUSERNAME} --password=${MYSQLPASSWD}
 
      echo "Populating database table en..."

      mariadb-import --user=${MYSQLUSERNAME} --password=${MYSQLPASSWD} --fields-terminated-by=',' --fields-enclosed-by='\"' --lines-terminated-by='\n' --local fcc_amateur ${STOREDIR}/en_temp.txt

      echo "use fcc_amateur;rename table en to en_old, en_temp to en;drop table en_old;"|mysql --user=${MYSQLUSERNAME} --password=${MYSQLPASSWD}
 
      echo "Populating database table am..."

      mariadb-import --user=${MYSQLUSERNAME} --password=${MYSQLPASSWD} --fields-terminated-by=',' --fields-enclosed-by='\"' --lines-terminated-by='\n' --local fcc_amateur ${STOREDIR}/am_temp.txt
 
      echo "use fcc_amateur;rename table am to am_old, am_temp to am;drop table am_old;"|mysql --user=${MYSQLUSERNAME} --password=${MYSQLPASSWD}
 
      echo "Populating database table hd..."
 
      mariadb-import --user=${MYSQLUSERNAME} --password=${MYSQLPASSWD} --fields-terminated-by=',' --fields-enclosed-by='\"' --lines-terminated-by='\n' --local fcc_amateur ${STOREDIR}/hd_temp.txt

      echo "use fcc_amateur;rename table hd to hd_old, hd_temp to hd;drop table hd_old;"|mysql --user=${MYSQLUSERNAME} --password=${MYSQLPASSWD}

      echo "Populating database table hs..."

      mysql --user=${MYSQLUSERNAME} --password=${MYSQLPASSWD} "fcc_amateur" <<EOF
LOAD DATA LOCAL INFILE './hs_temp.txt'
INTO TABLE hs_temp
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
(fccid, callsign, action_date, action_code);
EOF


      echo "use fcc_amateur;rename table hs to hs_old, hs_temp to hs;drop table hs_old;"|mysql --user=${MYSQLUSERNAME} --password=${MYSQLPASSWD}

      echo "Populating last_issued table..."

      mysql --user=${MYSQLUSERNAME} --password=${MYSQLPASSWD} "fcc_amateur" <<EOF
DROP TABLE IF EXISTS last_issued;
CREATE TABLE last_issued (
    fccid INT NOT NULL,
    callsign VARCHAR(8) NOT NULL,
    date DATE,
    class VARCHAR(10),
    full_name VARCHAR(255),
    address1 VARCHAR(255),
    city VARCHAR(100),
    state VARCHAR(2),
    zip VARCHAR(10),
    index idx_fccid (fccid),
    index idx_callsign (callsign),
    index idx_date (date),
    index idx_class (class),
    index idx_state (state)
);
INSERT INTO last_issued (fccid, callsign, date, class, full_name, address1, city, state, zip)
SELECT hs.fccid, hs.callsign, DATE(action_date) AS date, am.class AS class, full_name, address1, city, state, zip
FROM hs
JOIN am ON am.fccid = hs.fccid
JOIN en ON en.fccid = hs.fccid
JOIN hd ON hd.fccid = hs.fccid
WHERE action_code = "LIISS"
ORDER BY action_date DESC;
EOF


    else

      echo "Doing update with a daily update file..."

      # mariadb-import uses the filename to determine the table name (dumb) 
      mv ${STOREDIR}/en_temp.txt ${STOREDIR}/en.txt
      mv ${STOREDIR}/am_temp.txt ${STOREDIR}/am.txt
      mv ${STOREDIR}/hd_temp.txt ${STOREDIR}/hd.txt
      mv ${STOREDIR}/hs_temp.txt ${STOREDIR}/hs.txt
 
      echo "Updating database table en..."
    
      mariadb-import --user=${MYSQLUSERNAME} --password=${MYSQLPASSWD} --fields-terminated-by=',' --fields-enclosed-by='\"' --lines-terminated-by='\n' --local --replace fcc_amateur ${STOREDIR}/en.txt
 
      echo "Updating database table am..."
 
      mariadb-import --user=${MYSQLUSERNAME} --password=${MYSQLPASSWD} --fields-terminated-by=',' --fields-enclosed-by='\"' --lines-terminated-by='\n' --local --replace fcc_amateur ${STOREDIR}/am.txt
 
      echo "Updating database table hd..."
 
      mariadb-import --user=${MYSQLUSERNAME} --password=${MYSQLPASSWD} --fields-terminated-by=',' --fields-enclosed-by='\"' --lines-terminated-by='\n' --local --replace fcc_amateur ${STOREDIR}/hd.txt
      echo "Updating database table hs..."
      
      mariadb-import --user=${MYSQLUSERNAME} --password=${MYSQLPASSWD} --fields-terminated-by='\t' --lines-terminated-by='\n' --local --replace fcc_amateur ${STOREDIR}/hs.txt
 
    fi
    if [ ${DEBUG} -eq 0 ]; then
      echo "Cleaning up..."
      rm -f ${STOREDIR}/*_temp.txt
      rm -f ${STOREDIR}/en.txt
      rm -f ${STOREDIR}/am.txt
      rm -f ${STOREDIR}/hd.txt
      rm -f ${STOREDIR}/hs.txt
      rm -f ${STOREDIR}/*.dat
      rm -f ${STOREDIR}/*.zip
      rm -f ${STOREDIR}/counts
      cd -
      rmdir ${STOREDIR}
    fi
 
    echo "Done..."
 
    ;;


  cleanup) 
    echo "Cleaning up..."
    cd ${STOREDIR} 
    rm -f ${STOREDIR}/*_temp.txt
    rm -f ${STOREDIR}/en.txt
    rm -f ${STOREDIR}/am.txt
    rm -f ${STOREDIR}/hd.txt
    rm -f ${STOREDIR}/hs.txt
    rm -f ${STOREDIR}/*.dat
    rm -f ${STOREDIR}/*.zip
    rm -f ${STOREDIR}/counts
    cd -
    rmdir ${STOREDIR}
  ;; 

  makedatabase | makedb)
 
    echo "Creating database fcc_amateur...";
 
    echo "create database fcc_amateur;"|mysql --user=${MYSQLUSERNAME} --password=${MYSQLPASSWD}
 
    echo "use fcc_amateur;create table en (fccid int not null, callsign varchar(8) not null, primary key(fccid),full_name varchar(128),first varchar(20),middle varchar(1), last varchar(20), address1 varchar(64), city varchar(20), state varchar(2), zip varchar(10), index idx_zip (zip), index idx_callsign (callsign));"|mysql --user=${MYSQLUSERNAME} --password=${MYSQLPASSWD}
    echo "use fcc_amateur;create table am (fccid int not null, callsign varchar(8) not null, primary key(fccid), class varchar(1), col4 varchar(1), col5 varchar(2), col6 varchar(3), former_call varchar(8), former_class varchar(1), index idx_callsign (callsign), index idx_class(class), index idx_former_call(former_call));"|mysql --user=${MYSQLUSERNAME} --password=${MYSQLPASSWD}
    echo "use fcc_amateur;create table hd (fccid int not null, callsign varchar(8) not null, primary key(fccid), status varchar(1), index idx_callsign (callsign), index idx_status (status) );"|mysql --user=${MYSQLUSERNAME} --password=${MYSQLPASSWD} 
    echo "use fcc_amateur;create table hs (fccid int not null, callsign varchar(8) not null, index idx_fccid (fccid), action_date datetime, action_code varchar(8), index idx_action_code (action_code), index idx_callsign (callsign) );"|mysql --user=${MYSQLUSERNAME} --password=${MYSQLPASSWD}
  ;;
 
  removetables)
    echo "use fcc_amateur;drop table if exists en;drop table if exists am;drop table if exists hd;drop table if exists hs;drop table if exists en_temp;drop table if exists am_temp;drop table if exists hd_temp"|mysql --user=${MYSQLUSERNAME} --password=${MYSQLPASSWD}
  ;;
 
  zipcode)
    echo "use fcc_amateur;select en.callsign,class,first,last,address1,city,state,zip from en, am, hd  where en.fccid=am.fccid and en.fccid=hd.fccid and hd.status=\"A\" and zip like \"${2}%\";" | mysql --user=${MYSQLUSERNAME} --password=${MYSQLPASSWD} -N --batch | awk -F "\t" '{print $1","$2","$3","$4","$5","$6","$7","$8}'
  ;;

  state)
    echo "use fcc_amateur;select en.callsign,class,first,last,address1,city,state,zip from en, am, hd  where en.fccid=am.fccid and en.fccid=hd.fccid and hd.status=\"A\" and state =  \"${2}\";" | mysql --user=${MYSQLUSERNAME} --password=${MYSQLPASSWD} -N --batch | awk -F "\t" '{print $1","$2","$3","$4","$5","$6","$7","$8}'
  ;;
 
  dumptableen)
    echo "use fcc_amateur;select * from en;"|mysql --user=${MYSQLUSERNAME} --password=${MYSQLPASSWD}
  ;;
 
  dbcount | count | counts)
    echo "Table en count: "
    echo "use fcc_amateur;select COUNT(fccid) from en;"|mysql --user=${MYSQLUSERNAME} --password=${MYSQLPASSWD}
    echo "Table am count: "
    echo "use fcc_amateur;select COUNT(fccid) from am;"|mysql --user=${MYSQLUSERNAME} --password=${MYSQLPASSWD}
    echo "Table hd count: "
    echo "use fcc_amateur;select COUNT(fccid) from hd;"|mysql --user=${MYSQLUSERNAME} --password=${MYSQLPASSWD}
    echo "Table hd status counts: "
    echo "use fcc_amateur;select status, count(fccid) from hd group by status;"|mysql --user=${MYSQLUSERNAME} --password=${MYSQLPASSWD}
  ;;
 
  -h | --help | help)
    echo -e "Help:\r\n\r\n"
    echo -e "\t<CALLSIGN>\t- Search for a callsign"
    echo -e "\tpopulate\t- Perform a full download or partial update based on the day"
    echo -e "\tfull\t\t- Force a full update"
    echo -e "\tmakedb\t\t- Just make the database"
    echo -e "\tremovedb\t- Remove the database"
    echo -e "\tcount\t\t- Show counts of various things in the database"
    echo -e "\tzipcode <ZIPCODE>\t- Show all amateurs in a zip code"
    echo -e "\tstate <ST>\t- Show all amateurs in a state"
    echo -e "\tlike <CALLSIGN>\t- Perform a search with % wildcard(s)"
    echo -e "\tgetfccfile\t- Just download the full FCC file"
    echo -e "\tsql \"<SQL STATEMENT>\"\t- Send SQL query directly to the server"
    ;;
 
  -v | --version | version)
    echo ${VERSION}
    ;;

  sql)
    mysql --user=${MYSQLUSERNAME} --password=${MYSQLPASSWD} -e "${2}" fcc_amateur
  ;;
 
  like)
    CALLSIGN=`echo ${2}|sed y/abcdefghijklmnopqrstuvwxyz/ABCDEFGHIJKLMNOPQRSTUVWXYZ/`
    echo "use fcc_amateur;select en.callsign, am.class, full_name, address1, city, state, zip, former_call from en, am, hd where en.fccid=am.fccid and en.fccid=hd.fccid and hd.status=\"A\" and en.callsign like  \"${CALLSIGN}%\";"|mysql --user=${MYSQLUSERNAME} --password=${MYSQLPASSWD}
  ;;

  last)
    echo "use fcc_amateur;select en.callsign, am.class, full_name, address1, city, state, zip, former_call from en, am, hd where en.fccid=am.fccid and en.fccid=hd.fccid and en.last like  \"${2}\";"|mysql --user=${MYSQLUSERNAME} --password=${MYSQLPASSWD}
  ;;

  former)
    CALLSIGN=`echo ${2}|sed y/abcdefghijklmnopqrstuvwxyz/ABCDEFGHIJKLMNOPQRSTUVWXYZ/`
    #echo "use fcc_amateur;select en.callsign, am.class, full_name, address1, city, state, zip, former_call from en, am, hd where en.fccid=am.fccid and en.fccid=hd.fccid and hd.status=\"A\" and (en.callsign=\"$CALLSIGN\" or former_call=\"$CALLSIGN\");"|mysql --user=${MYSQLUSERNAME} --password=${MYSQLPASSWD}
    echo "use fcc_amateur;select * from en where en.callsign=\"${CALLSIGN}\";"|mysql --user=${MYSQLUSERNAME} --password=${MYSQLPASSWD}
    echo "use fcc_amateur;select fccid, callsign, former_call from am where (am.former_call=\"${CALLSIGN}\" or am.callsign=\"${CALLSIGN}\");"|mysql --user=${MYSQLUSERNAME} --password=${MYSQLPASSWD}
    ;;

  lastname)
    #echo "use fcc_amateur;select * from en where en.last like \"${2}\";"|mysql --user=${MYSQLUSERNAME} --password=${MYSQLPASSWD}
    echo "use fcc_amateur;select en.callsign, am.class, full_name,mariadb-import address1, city, state, zip, former_call from en, am, hd where en.fccid=am.fccid and en.fccid=hd.fccid and hd.status=\"A\" and en.last like \"${2}\";"|mysql --user=${MYSQLUSERNAME} --password=${MYSQLPASSWD}
    ;;

  history)
    echo "select fccid, callsign, DATE(action_date) as 'date', action_code as action from hs where callsign = \"${2}\" order by action_date;"|mysql --user=${MYSQLUSERNAME} --password=${MYSQLPASSWD} "fcc_amateur"
    ;;

  lastissued)
    echo "select * from last_issued order by date desc;"|mysql --user=${MYSQLUSERNAME} --password=${MYSQLPASSWD} "fcc_amateur"|more
    ;;
  lastissuedstate)
    echo "select * from last_issued where state = \"${2}\" order by date desc;"|mysql --user=${MYSQLUSERNAME} --password=${MYSQLPASSWD} "fcc_amateur"|more
    ;;
  lastissuedzip)
    echo "select * from last_issued where zip like \"${2}%\" order by date desc;"|mysql --user=${MYSQLUSERNAME} --password=${MYSQLPASSWD} "fcc_amateur"|more
    ;;
 
  *)
    CALLSIGN=`echo ${1}|sed y/abcdefghijklmnopqrstuvwxyz/ABCDEFGHIJKLMNOPQRSTUVWXYZ/`
    echo "use fcc_amateur;select * from en where en.callsign=\"${CALLSIGN}\";"|mysql --user=${MYSQLUSERNAME} --password=${MYSQLPASSWD}
    exit 0
esac

