#!./perl
#
# usps_import -- Import USPS standard information - States
#
#	Written by Clint Goss <clint@goss.com>, November 1998
#

BEGIN {
	# Access Perl modules

	#push (@INC, ...your lib directory here...);

	# Point to our preferred installation of Oracle

	#$ENV{'ORACLE_HOME'} = ...your Oracle Home here...);
}

# Perl Modules

use OraConnection;	# Connection to Oracle, incl DBI and DBD::Oracle
use FileHandle;
use strict;		# Restrict unsafe variables, refs, barewords

# Set up the connection to Oracle

my ($database, $username, $password) = qw (ora9 demo demo);
my ($dbc) = new OraConnection;
$dbc->connect ($database, $username, $password);

# Statistics

my ($countStaIns) = 0;

&importRef ("usps.txt");

print "Inserted $countStaIns into STATES_PROVS table.\n";

exit;

# Import one USPS States file containing Reference table information

sub importRef {
	my ($fileName) = @_;

	open (USPS_LOG, "<" . $fileName) ||
			die "Can't open " . $fileName;

	# Flag if we have found the "State/Possession" line which
	# marks the start of the State data.

	my ($foundHeader);

	my ($line);
	while ($line = <USPS_LOG>) {

		# "Just tidy us up a bit"

		chomp $line;
		$line =~ s/^\s*//;
		$line =~ s/\s*$//;

		# Check if this is the header line

		if (! $foundHeader) {
			if ($line =~ /^State\/Possession/) {
				$foundHeader = 1;
			}
			next;
		}

		# Here if we've already found header
		# Check if we're at the end of the data

		if ($line =~ /^Military /) { last; }
		if ($line eq "") { next; }

		# Here if we got a real line of State data.

# The format is (for example):
#          1         2         3         4         5         6         7
# 1234567890123456789012345678901234567890123456789012345678901234567890
# ALABAMA                         AL
# FEDERATED STATES OF MICRONESIA  FM

		my ($uName) = substr ($line, 0, 31);
		$uName =~ s/^\s*//;
		$uName =~ s/\s*$//;

		my ($a2) = substr ($line, 32, 2);

		# Convert the State name to upper lower case

		my ($ulName) = ucfirst lc $uName;

		# Convert the first character of the second name
		# of two-part and hyphenated names to upper case.

		$ulName =~ s/([ -\/])([a-z])/$1.uc($2)/ge;

		# Restore English-language articles back to lower case

		$ulName =~ s/ Of / of /g;

		#print "States $a2 $ulName\n";
		#next;

		# Insert into COUNTRIES table

		my ($stmt) = "INSERT into STATES_PROVS (" .
			"sta_code_a2, " .
			"sta_cnt_code_a3, " .
			"sta_name_upper_case, " .
			"sta_name_mixed_case) " .
		"VALUES (" .
			$dbc->quote ($a2)	. ", " .
			$dbc->quote ("USA")	. ", " .
			$dbc->quote ($uName)	. ", " .
			$dbc->quote ($ulName)	. ")";

		$dbc->exec ($stmt);
		$countStaIns++;
	}
}