#!/usr/bin/perl -w 
use strict; 
use XBase; 
use DBI; 

#function prototypes 
sub extMSCode($); 



print "Opening Original file...\n"; 
my $dbf = new XBase "input.dbf" or die XBase->errstr; 
print "Creating New Table...\n"; 
my $new_dbf = XBase->create("name" => "output.dbf", 
                            "field_names" => [$dbf->field_names, "COUNTED","SEQUENCE"], 
                            "field_types" => [$dbf->field_types, "C","N"], 
                            "field_lengths" => [$dbf->field_lengths,20,10], 
                            "field_decimals" => [$dbf->field_decimals,undef,0] ) or die $dbf->errstr; 

my $dbh = DBI->connect("DBI:XBase:") or die $DBI::errstr; 

print "Extracting Mailsort Codes...\n"; 
my %ms_code = extMSCode($dbh); 

print "Writing data...\n\n"; 

my ($select_sql, $insert_sql); 
my $loop; 
my @fields = $dbf->field_names; 
foreach my $key (sort{$ms_code{$a}<=>$ms_code{$b}} keys(%ms_code)){ 
    #Sort by Count 
    $select_sql = "select * from input.dbf where MS_SELCODE = $key ORDER BY MS_SELCODE"; 
    $insert_sql = qq{INSERT INTO output.dbf (".join(",",@fields).") VALUES ('?)}; 
    my $sth_select = $dbh->prepare($select_sql); 
    my $sth_insert = $dbh->prepare($insert_sql); 
    $sth_select->execute(); 
    while(my @data = $sth_select->fetchrow_array()){ 
        my $temp = join("\',\'", @data); 
        $sth_insert->bind_param(1,join("\',\'", @data)); 
        $sth_insert->execute(); 
        print $loop++." \n"; 
    } 
    
} 
$dbh->disconnect; 
# Function Declarations and Code 

sub extMSCode($){ 
    # Extracts Mailsort Codes and returns in a hash 
    # with a count per code 
    
    my $code_index = $dbh->prepare("select MS_SELCODE from input.dbf order by MS_SELCODE") or die $dbh->errstr(); 
    $code_index->execute() or die $code_index->errstr(); 
    
    my %ms_code = (); 
    while(my @data = $code_index->fetchrow_array()){ 
        if(defined($ms_code{$data["MS_SELCODE"]})){ 
            $ms_code{$data["MS_SELCODE"]}++; 
        }else{ 
            $ms_code{$data["MS_SELCODE"]} = 1; 
        } 
    } 
    return %ms_code; 
}