#!/usr/bin/perl

# ©2014 Biblionix LLC.  All rights reserved.

use strict;
use Getopt::Std;
use CGI qw(escapeHTML);
use Digest::MD5 qw(md5_hex);
use Email::Valid;
use MARC::File::XML;
use MIME::Base64;
use Math::Round;
use Data::Dumper;
use XML::Simple;
use DBI;
use Date::Parse;
use Time::localtime;

binmode(STDOUT, ':utf8');

my $ldif_version = '020';
my $version = '2014-10-07';

my @problems;
my %amount_owed_by_patron;
my %items_to_problems;
my %citystate_to_city_and_state;
my %cities_to_states;
my $dbh; 
my $current_year;
my %tables;
my $authorised_values_exists;
my $branch_count = 0;


main();

sub main {
    getopt('chdup');
    our ( $opt_c, $opt_h, $opt_d, $opt_u, $opt_p);
    unless ( $opt_c || ($opt_h && $opt_d && $opt_u && $opt_p)) {
        print "usage: $0 [-c koha_conf] [-h hostname -d database -u db_user -p db_password] \n";
        print "\n";
        print "  Provide either the Koha configuration xml (-c), or manually provide database credentials (-h, -d, -u, -p)\n";
        print "  -c configuration file for koha.  Generally in KOHA_ROOT/etc/koha-conf.xml\n";
        print "\n";
        print "  -h hostname of database server\n";
        print "  -d database name\n";
        print "  -u database user name\n";
        print "  -p database password\n";
        exit;
    } 

    my $hostname = $opt_h;
    my $database = $opt_d;
    my $username = $opt_u;
    my $password = $opt_p;
    if ($opt_c) {
      ($hostname,$database,$username,$password) = get_connection_details_from_config($opt_c);
    }

    $dbh = get_connection($hostname,$database,$username,$password);
    
    ($current_year) = $dbh->selectrow_array(q{SELECT YEAR(CURDATE())});
    print get_ldif_xml_start( $ldif_version, $version, "kohadb_to_ldif" );

    ($authorised_values_exists) = $dbh->selectrow_array(
        q{SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
          WHERE TABLE_NAME = 'authorised_values'
          AND TABLE_SCHEMA = ?}, undef, ($database)); 

    {
        my $sth = $dbh->prepare(q{SHOW TABLES});
        $sth->execute();
        while( my $table = $sth->fetchrow_array() )
        { $tables{$table} = 1; }
    }

    if($tables{'branches'})
    { ($branch_count) = $dbh->selectrow_array(q{SELECT COUNT(*) FROM branches}); }
    if($branch_count > 1)
    { do_locations(); }

    pre_process_checkouts();
    do_holidays() if $tables{'repeatable_holidays'} && $tables{'special_holidays'};
    do_holding_types();
    do_patron_types();
    get_states();
    do_patrons();
    do_biblios();
    do_holdings();
    do_problems();
    do_checkouts();

    if($tables{'virtualshelfcontents'} && $tables{'virtualshelves'})
    { do_booklists(); }

    print qq{</ldif:library>\n};
    $dbh->disconnect();

}

sub get_connection_details_from_config {
    my $koha_conf = shift;

    warn "koha_conf = " . $koha_conf; 
    my $root = XMLin($koha_conf, GroupTags => { searchpath => 'config' });

    my $user = $root->{config}->{user};
    my $password = $root->{config}->{pass};
    my $db = $root->{config}->{database};
    my $hostname = $root->{config}->{hostname};
    return ($db,$hostname,$user,$password);
}


sub get_connection {
    my ($hostname,$db,$user,$password) = @_;

    my $dsn = "DBI:mysql:database=$db;host=$hostname";

    my $dbh = DBI->connect(
        $dsn, $user,$password,
        {
            AutoCommit => 0
            ,RaiseError => 0
        }
    );

    return $dbh;
}

sub do_locations
{
    print qq{  <locations>\n};

    my $sth = $dbh->prepare(
        q{SELECT branchcode, branchname
            FROM branches});
    $sth->execute();
    while( my ($branchcode, $branchname) = $sth->fetchrow_array())
    {
        my $branchid = hex(substr(md5_hex($branchcode), 27));
        print join(' ',
            q{    <location},
            qq{id="l$branchid"},
            q{name="}.escapeHTML($branchname).q{"},
            qq{/>\n},
            );
    }

    print qq{  </locations>\n};
}

sub do_holidays
{
    my %days_of_week_closed;
    my %holidays;

    # 2006-01-01 was a Sunday
    my $sth = $dbh->prepare(
        q{SELECT LOWER(DAYNAME('2006-01-01' + INTERVAL weekday DAY))
          FROM repeatable_holidays
          WHERE COALESCE(branchcode, '') = ''
          AND weekday IS NOT NULL});
    $sth->execute();
    while( my ($day) = $sth->fetchrow_array())
    { $days_of_week_closed{$day} = 1; }

    my $latest_special_year;
    my $sth = $dbh->prepare(
        q{SELECT DATE_FORMAT(CONCAT(year, '-', month, '-', day), '%Y-%m-%d'), year
          FROM special_holidays});
    $sth->execute();
    while( my ($date, $year) = $sth->fetchrow_array())
    {
        if($year > $latest_special_year)
        { $latest_special_year = $year; }
        $holidays{$date} = 1;
    }

    my $sth = $dbh->prepare(
        q{SELECT LPAD(month, 2, '0'), LPAD(day, 2, '0')
          FROM repeatable_holidays
          WHERE weekday IS NULL});
    $sth->execute();
    while( my ($month, $day) = $sth->fetchrow_array())
    {
        my $end_year =
            $latest_special_year >= $current_year ?
                $latest_special_year :
                $current_year;

        for($current_year..$end_year)
        { $holidays{join('-', $_, $month, $day)} = 1; }
    }

    if(keys(%days_of_week_closed) || keys(%holidays))
    {
        print qq{  <holidays>\n};
        if(keys(%days_of_week_closed))
        {
            print qq{    <days_of_week_closed};
            foreach my $day(keys(%days_of_week_closed))
            { print qq{ $day="true"}; }
            print qq{/>\n};
        }

        foreach my $date(sort(keys(%holidays)))
        { print qq{    <date>$date</date>\n}; }

        print qq{  </holidays>\n};
    }
}

sub do_holding_types
{
    print qq{  <holdingMembershipLists>\n};

    print qq{    <holdingMembershipList id="hml1" name="material_types">\n};
    my $sth = $dbh->prepare(
        q{SELECT * FROM itemtypes});
    $sth->execute();
    while( my $ref = $sth->fetchrow_hashref())
    {
        my $type_id = hex(substr(md5_hex($ref->{'itemtype'}), 27));
        print join(' ',
            q{      <holdingMembership},
           qq{id="hm$type_id"},
            q{name="}.escapeHTML($ref->{'description'}).q{"},
           qq{/>\n},
          );
    }
    print qq{    </holdingMembershipList>\n};

    my ($secondary_types_exist) = $dbh->selectrow_array(
        q{SELECT COUNT(DISTINCT(ccode)) FROM items});
    if($secondary_types_exist)
    {
        print qq{    <holdingMembershipList id="hml2" name="material_types_2">\n};
        my $sth = $dbh->prepare(
            q{SELECT DISTINCT(ccode) FROM items
              WHERE ccode IS NOT NULL});
        $sth->execute();
        while( my ($ccode) = $sth->fetchrow_array())
        {
            my $type_id = hex(substr(md5_hex($ccode), 27))+1000000000;
            print join(' ',
                q{      <holdingMembership},
               qq{id="hm$type_id"},
                q{name="}.escapeHTML($ccode).q{"},
               qq{/>\n},
            );
        }
        print qq{    </holdingMembershipList>\n};
    }

    my ($shelf_locations_exist) = $dbh->selectrow_array(
        q{SELECT COUNT(DISTINCT(location)) FROM items});
    if($shelf_locations_exist)
    {
        print qq{    <holdingMembershipList id="hml3" name="shelf_locations">\n};
        my $sth = $dbh->prepare(
            q{SELECT DISTINCT(location) FROM items
              WHERE location IS NOT NULL});
        $sth->execute();
        while( my ($ccode) = $sth->fetchrow_array())
        {
            my $type_id = hex(substr(md5_hex($ccode), 27))+2000000000;
            print join(' ',
                q{      <holdingMembership},
               qq{id="hm$type_id"},
                q{name="}.escapeHTML($ccode).q{"},
               qq{/>\n},
            );
        }
        print qq{    </holdingMembershipList>\n};
    }

    my ($vendors_exist) = $dbh->selectrow_array(
        q{SELECT COUNT(DISTINCT(booksellerid)) FROM items});
    if($vendors_exist)
    {
        print qq{    <holdingMembershipList id="hml4" name="vendors">\n};
        my $sth = $dbh->prepare(
            q{SELECT DISTINCT(booksellerid) FROM items
              WHERE booksellerid IS NOT NULL});
        $sth->execute();
        while( my ($ccode) = $sth->fetchrow_array())
        {
            my $type_id = hex(substr(md5_hex($ccode), 27))+3000000000;
            print join(' ',
                q{      <holdingMembership},
               qq{id="hm$type_id"},
                q{name="}.escapeHTML($ccode).q{"},
               qq{/>\n},
            );
        }
        print qq{    </holdingMembershipList>\n};
    }

    print qq{  </holdingMembershipLists>\n};
}

sub do_patron_types
{
    print qq{  <patronMembershipLists>\n};

    print qq{    <patronMembershipList id="pml1" name="patron_types">\n};
    my $sth = $dbh->prepare(q{SELECT * FROM categories});
    $sth->execute();
    while( my $ref = $sth->fetchrow_hashref())
    {
        my $type_id = hex(substr(md5_hex(lc($ref->{'categorycode'})), 27));
        print join(' ',
            q{      <patronMembership},
           qq{id="pm$type_id"},
            q{name="}.escapeHTML($ref->{'description'}).q{"},
            (defined($ref->{'enrolmentperiod'}) ? qq{membershipLengthMonths="$ref->{'enrolmentperiod'}"} : ''),
            (defined($ref->{'enrolmentfee'}) ? q{renewalFeeCents="}.round($ref->{'enrolmentfee'}*100).q{"} : ''),
           qq{/>\n},
        );
    }
    print qq{    </patronMembershipList>\n};

    print qq{    <patronMembershipList id="pml2" name="patron_types_2">\n};
    my $sth = $dbh->prepare(
        q{SELECT DISTINCT(sort1) FROM borrowers
          WHERE LENGTH(sort1) > 0});
    $sth->execute();
    while( my ($code) = $sth->fetchrow_array())
    {
        my $type_id = hex(substr(md5_hex($code), 27))+1000000000;
        print join(' ',
            q{      <patronMembership},
           qq{id="pm$type_id"},
            q{name="}.escapeHTML($code).q{"},
           qq{/>\n},
          );
    }
    print qq{    </patronMembershipList>\n};

    print qq{    <patronMembershipList id="pml3" name="patron_types_3">\n};
    my $sth = $dbh->prepare(
        q{SELECT DISTINCT(sort2) FROM borrowers
          WHERE LENGTH(sort2) > 0});
    $sth->execute();
    while( my ($code) = $sth->fetchrow_array())
    {
        my $type_id = hex(substr(md5_hex($code), 27))+2000000000;
        print join(' ',
            q{      <patronMembership},
           qq{id="pm$type_id"},
            q{name="}.escapeHTML($code).q{"},
           qq{/>\n},
          );
    }
    print qq{    </patronMembershipList>\n};

    print qq{  </patronMembershipLists>\n};
}

sub get_states
{
    warn 'getting states';

    my $sth = $dbh->prepare(q{SELECT * FROM borrowers});
    $sth->execute();
    while( my $ref = $sth->fetchrow_hashref())
    {
        foreach my $key(qw(city B_city altcity))
        {
            next unless $ref->{$key};

            my $citystate_ref = $citystate_to_city_and_state{$ref->{$key}};

            if(!$citystate_ref)
            {
                my ($city, $state) = split(/\s*,\s*/, $ref->{$key});
                if($state)
                { $state = state($state, 1); }
                else
                {
                    if($ref->{$key} =~ /(.+)\W{1,2}(\w+)$/)
                    {
                        my $city_temp = $1;
                        my $state_temp = $2;

                        $state_temp = state($state_temp, 1);
                        if($state_temp)
                        {
                            $city = $city_temp;
                            $state = $state_temp;
                        }
                    }
                }

                my %hash;
                $hash{'city'} = sanitize($city);
                $hash{'state'} = $state;
                $citystate_ref = \%hash;

                $citystate_to_city_and_state{$ref->{$key}} = $citystate_ref;
            }

            if($citystate_ref->{'state'})
            {
                ++$cities_to_states{$citystate_ref->{'city'}}{$citystate_ref->{'state'}};
            }
        }
    }

    foreach my $key(keys(%citystate_to_city_and_state))
    {
        my $citystate_ref = $citystate_to_city_and_state{$key};
        if(!$citystate_ref->{'state'})
        {
            my $potential_states = $cities_to_states{$citystate_ref->{'city'}};
            if($potential_states && scalar(keys(%{$potential_states})) == 1)
            {
                my ($state) = keys(%{$potential_states});
                $citystate_to_city_and_state{$key}{'state'} = $state;
            }
        }
    }

    warn 'finished getting states';
}

sub do_patrons
{
warn 'starting patrons';
    my $email_count = 0;
    my $phone_count = 0;

    print qq{  <patrons>\n};

    my $sth = $dbh->prepare(q{SELECT * FROM borrowers});
    $sth->execute();
    while( my $ref = $sth->fetchrow_hashref())
    {
        next unless $ref->{'surname'};
        next unless $ref->{'borrowernumber'} =~ /^\d+$/;

        print join(' ',
            q{    <patron},
           qq{id="p$ref->{'borrowernumber'}"},
            q{barcode="}.escapeHTML($ref->{'cardnumber'}).q{"},
            ($ref->{'dateexpiry'} && $ref->{'dateexpiry'} !~ /00-00/ ? q{expiration="}.escapeHTML($ref->{'dateexpiry'}).q{"} : ''),
            q{lastName="}.escapeHTML($ref->{'surname'}).q{"},
            ($ref->{'dateenrolled'} && $ref->{'dateenrolled'} !~ /00-00/ ? q{created="}.escapeHTML($ref->{'dateenrolled'}).q{"}: ''),
            ($branch_count > 1 && $ref->{'branchcode'} ? q{preferredLocation="l}.hex(substr(md5_hex($ref->{'branchcode'}), 27)).q{"} : ''),
           qq{>\n},
        );

        if($ref->{'firstname'} || $ref->{'dateofbirth'})
        {
            my $firstname = sanitize($ref->{'firstname'});

            print join(' ',
               qq{      <firstNames>\n},
                q{        <firstName},
                q{name="}.escapeHTML($firstname).q{"},
                ($ref->{'dateofbirth'} && $ref->{'dateofbirth'} !~ /-00/ ? q{birthdate="}.escapeHTML($ref->{'dateofbirth'}).q{"} : ''),
               qq{/>\n},
               qq{      </firstNames>\n},
            );
        }

        my @memberships;
        if($ref->{'categorycode'})
        { push(@memberships, 'pm'.hex(substr(md5_hex(lc($ref->{'categorycode'})), 27))); }
        if($ref->{'sort1'})
        { push(@memberships, 'pm'.(hex(substr(md5_hex(lc($ref->{'sort1'})), 27))+1000000000)); }
        if($ref->{'sort2'})
        { push(@memberships, 'pm'.(hex(substr(md5_hex(lc($ref->{'sort2'})), 27))+2000000000)); }

        for(@memberships)
        { print qq{      <membership>$_</membership>\n}; }

        if($ref->{'address'} || $ref->{'B_address'} || $ref->{'streetaddress'} || $ref->{'altstreetaddress'} || $ref->{'physstreet'})
        {
            print qq{      <addresses>\n};
            if($ref->{'address'})
            {
                my $citystate_ref = $citystate_to_city_and_state{$ref->{'city'}};
                my ($city, $state) = ($citystate_ref->{'city'}, $citystate_ref->{'state'});
                
                print join(' ',
                    q{        <address},
                    ($city ? q{locality="}.escapeHTML($city).q{"} : ''),
                    ($state ? q{countryDivision="}.escapeHTML($state).q{"} : ''),
                    q{country="US"},
                    ($ref->{'zipcode'} ? q{postalCode="}.escapeHTML($ref->{'zipcode'}).q{"} : ''),
                    ($ref->{'B_address'} ? '' : q{mailing="true"}),
                   qq{>\n},
                );

                for($ref->{'address'}, $ref->{'address2'})
                {
                    next unless $_;
                    print q{          <line>}.escapeHTML($_).qq{</line>\n};
                }

                print qq{        </address>\n};
            }
            if($ref->{'B_address'})
            {
                my $citystate_ref = $citystate_to_city_and_state{$ref->{'city'}};
                my ($city, $state) = ($citystate_ref->{'city'}, $citystate_ref->{'state'});

                print join(' ',
                    q{        <address},
                    ($city ? q{locality="}.escapeHTML($city).q{"} : ''),
                    ($state ? q{countryDivision="}.escapeHTML($state).q{"} : ''),
                    q{country="US"},
                    ($ref->{'B_zipcode'} ? q{postalCode="}.escapeHTML($ref->{'B_zipcode'}).q{"} : ''),
                    q{mailing="true"},
                qq{>\n},
                );

                for($ref->{'B_address'}, $ref->{'B_address2'})
                {
                    next unless $_;
                    print q{          <line>}.escapeHTML($_).qq{</line>\n};
                }

                print qq{        </address>\n};
            }


            elsif($ref->{'streetaddress'})
            {
                my $citystate_ref = $citystate_to_city_and_state{$ref->{'city'}};
                my ($city, $state) = ($citystate_ref->{'city'}, $citystate_ref->{'state'});

                print join(' ',
                    q{        <address},
                    ($city ? q{locality="}.escapeHTML($city).q{"} : ''),
                    ($state ? q{countryDivision="}.escapeHTML($state).q{"} : ''),
                    q{country="US"},
                    ($ref->{'zipcode'} ? q{postalCode="}.escapeHTML($ref->{'zipcode'}).q{"} : ''),
                    ($ref->{'physstreet'} ? '' : q{mailing="true"}),
                qq{>\n},
                );

                for($ref->{'streetaddress'})
                {
                    next unless $_;
                    print q{          <line>}.escapeHTML($_).qq{</line>\n};
                }

                print qq{        </address>\n};
            }
            if($ref->{'altstreetaddress'})
            {
                my $citystate_ref = $citystate_to_city_and_state{$ref->{'city'}};
                my ($city, $state) = ($citystate_ref->{'city'}, $citystate_ref->{'state'});

                print join(' ',
                    q{        <address},
                    ($city ? q{locality="}.escapeHTML($city).q{"} : ''),
                    ($state ? q{countryDivision="}.escapeHTML($state).q{"} : ''),
                    q{country="US"},
    #                 ($ref->{'B_zipcode'} ? q{postalCode="}.escapeHTML($ref->{'B_zipcode'}).q{"} : ''),
                    q{mailing="true"},
                qq{>\n},
                );

                for($ref->{'altstreetaddress'})
                {
                    next unless $_;
                    print q{          <line>}.escapeHTML($_).qq{</line>\n};
                }

                print qq{        </address>\n};
            }
            if($ref->{'physstreet'})
            {
                my $citystate_ref = $citystate_to_city_and_state{$ref->{'city'}};
                my ($city, $state) = ($citystate_ref->{'city'}, $citystate_ref->{'state'});

                print join(' ',
                    q{        <address},
                    ($city ? q{locality="}.escapeHTML($city).q{"} : ''),
                    ($state ? q{countryDivision="}.escapeHTML($state).q{"} : ''),
                    q{country="US"},
    #                 ($ref->{'B_zipcode'} ? q{postalCode="}.escapeHTML($ref->{'B_zipcode'}).q{"} : ''),
                qq{>\n},
                );

                for($ref->{'physstreet'})
                {
                    next unless $_;
                    print q{          <line>}.escapeHTML($_).qq{</line>\n};
                }

                print qq{        </address>\n};
            }

            print qq{      </addresses>\n};
        }

        my @phones;
        phone(\@phones, "$ref->{'phone'} home");
        phone(\@phones, "$ref->{'B_phone'} home");
        phone(\@phones, "$ref->{'mobile'} cell");
        phone(\@phones, "$ref->{'phonepro'} work");
        phone(\@phones, "$ref->{'altphone'}");

        if(@phones)
        {
            print qq{      <phones>\n};

            for(@phones)
            {
                ++$phone_count;
                print join(' ',
                    q{        <phone},
                qq{id="ph$phone_count"},
                );
                foreach my $key(keys(%{$_}))
                { print qq{ $key="$_->{$key}"}; }
                print qq{/>\n};
            }

            print qq{      </phones>\n};
        }

        my @emails;
        foreach my $email($ref->{'email'}, $ref->{'B_email'}, $ref->{'emailaddress'})
        {
            if(email_valid($email))
            { push(@emails, $email); }
        }
        if(@emails)
        {
            print qq{      <emails>\n};

            for(@emails)
            {
                ++$email_count;
                print join(' ',
                    q{        <email},
                qq{id="em$email_count"},
                    q{address="}.escapeHTML($_).q{"},
                qq{/>\n}),
            }

            print qq{      </emails>\n};
        }

        my ($message_count) = $dbh->selectrow_array(
            q{SELECT COUNT(*) FROM messages
              WHERE borrowernumber = ?},{},
            ($ref->{'borrowernumber'}));

        if($message_count || $ref->{'borrowernotes'})
        {
            print qq{      <patronNotes>\n};
            my $sth = $dbh->prepare(
                q{SELECT *
                  FROM messages
                  WHERE borrowernumber = ?});
            $sth->execute($ref->{'borrowernumber'});
            while( my $message_data = $sth->fetchrow_hashref())
            {
                print join(' ',
                    q{        <patronNote},
                    q{message="}.escapeHTML($message_data->{'message'}).q{"},
                    ($message_data->{'message_date'} ? q{dateAdded="}.tmstr_to_iso($message_data->{'message_date'}).q{"} : ''),
                    ($message_data->{'message_date'} ? q{lastUpdated="}.tmstr_to_iso($message_data->{'message_date'}, 1).q{"} : ''),
                    ($message_data->{'message_type'} eq 'L' ? q{urgent="true"} : ''),
                    q{active="true"},
                qq{/>\n},
                );
            }
            if($ref->{'borrowernotes'})
            {
                print join(' ',
                    q{        <patronNote},
                    q{message="}.escapeHTML($ref->{'borrowernotes'}).q{"},
                    q{urgent="true"},
                    q{active="true"},
                qq{/>\n},
                );
            }
            print qq{      </patronNotes>\n};
        }

        {
            my $reserve_header_printed;

            my $sth = $dbh->prepare(
                q{SELECT * FROM reserves
                  WHERE borrowernumber = ?
                  AND found != 'F'});
            $sth->execute($ref->{'borrowernumber'});
            while( my $reserve_ref = $sth->fetchrow_hashref())
            {
                my ($biblioitemnumber) = $dbh->selectrow_array(
                    q{SELECT biblioitemnumber
                      FROM biblioitems
                      WHERE biblionumber = ?},{},
                    ($reserve_ref->{'biblionumber'}));
                if(!$biblioitemnumber)
                {
                    warn "no biblionumber $reserve_ref->{'biblionumber'} for reserve $reserve_ref->{'reserve_id'}";
                    next;
                }

                my $pickup_location;
                if($branch_count > 1 && $reserve_ref->{'branchcode'})
                {
                    $pickup_location = 'l'.hex(substr(md5_hex($reserve_ref->{'branchcode'}), 27));
                }

                if(!$reserve_header_printed)
                {
                    print qq{      <reserves>\n};
                    $reserve_header_printed = 1;
                }

                print join(' ',
                    q{        <reserve},
                   qq{id="r$reserve_ref->{'reserve_id'}"},
                    q{status="pending"},
                   qq{biblio="b$biblioitemnumber"},
                    ($reserve_ref->{'reservedate'} && $reserve_ref->{'reservedate'} !~ /0000/ ? qq{placed="$reserve_ref->{'reservedate'}T00:00:00"} : ''),
                    ($pickup_location ? qq{pickupLocation="$pickup_location"} : ''),
                   qq{/>\n},
                  );
            }
            print qq{      </reserves>\n} if $reserve_header_printed;
        }

        my $fine_header_printed;
        {
            my $current_owe;
            my @patron_issues;
            my %out_to_patron_items;

            my $sth = $dbh->prepare(
                q{SELECT * FROM accountlines
                  WHERE borrowernumber = ?
                  ORDER BY timestamp});
            $sth->execute($ref->{'borrowernumber'});
            while( my $account_ref = $sth->fetchrow_hashref())
            {
                $account_ref->{'amount'} = round($account_ref->{'amount'}*100);
                $account_ref->{'amountoutstanding'} = round($account_ref->{'amountoutstanding'}*100);

                if($account_ref->{'itemnumber'})
                {
                    ($account_ref->{'itemstatus_raw'}) = $dbh->selectrow_array(
                        q{SELECT itemlost
                          FROM items
                          WHERE itemnumber = ?},{},
                        ($account_ref->{'itemnumber'}));

                    if($authorised_values_exists)
                    {
                        ($account_ref->{'itemstatus_description'}) = $dbh->selectrow_array(
                            q{SELECT lib
                              FROM authorised_values
                              WHERE category = 'LOST'
                              AND authorised_value = ?},{},
                        ($account_ref->{'itemstatus_raw'}));
                    }

                    ($account_ref->{'checkout'}) = $dbh->selectrow_array(
                        q{SELECT id
                          FROM checkouts
                          WHERE borrowernumber = ?
                          AND itemnumber = ?
                          AND returndate IS NULL},{},
                        ($ref->{'borrowernumber'}, $account_ref->{'itemnumber'}));
                    if($account_ref->{'checkout'})
                    { $out_to_patron_items{$account_ref->{'itemnumber'}} = 1; }
                }

# warn join("\t", $ref->{'borrowernumber'}, $account_ref->{'amountoutstanding'}, $current_owe);
                if($account_ref->{'amountoutstanding'} != 0 &&
                   $account_ref->{'amountoutstanding'} == -1*$current_owe)
                {
# warn 'removing all';
                    #It looks like we're paying for the amounts owed so far.
                    #So we won't push this row onto the stack, and we'll remove
                    #anything with an outstanding amount from the stack.
                    my @new_patron_issues;
                    for(@patron_issues)
                    {
                        if($_->{'amountoutstanding'} == 0)
                        { push(@new_patron_issues, $_); }
                    }
# warn "$ref->{'borrowernumber'} removed ".(scalar(@patron_issues)-scalar(@new_patron_issues));
                    @patron_issues = @new_patron_issues;
                    $current_owe = 0;
                }
                elsif($account_ref->{'amountoutstanding'} != 0)
                {
#Look for a single problem/fine resolved by this payment
                    my $removed_one;
                    for(my $i=0; $i<@patron_issues; ++$i)
                    {
                        my $account_ref_temp = $patron_issues[$i];

                        if($account_ref->{'amountoutstanding'} == -1*$account_ref_temp->{'amountoutstanding'})
                        {
                            splice(@patron_issues, $i, 1);
                            $current_owe -= $account_ref_temp->{'amountoutstanding'};
                            $removed_one = 1;
                            last;
                        }
                    }

                    if(!$removed_one)
                    {
                        $current_owe += $account_ref->{'amountoutstanding'};
                        push(@patron_issues, $account_ref);
# warn 'pushed '.scalar(@patron_issues);
                    }
                    else
                    {
# warn 'removed one '.scalar(@patron_issues);
                    }
# warn "$ref->{'borrowernumber'} removed ".(scalar(@patron_issues)-scalar(@new_patron_issues));
                }
                elsif($account_ref->{'amount'} != 0 && $account_ref->{'amountoutstanding'} == 0)
                {
# Don't hang onto it if there was an amount and it's over
# warn 'doing nothing';
                }
                else
                {
                    $current_owe += $account_ref->{'amountoutstanding'};
                    push(@patron_issues, $account_ref);
# warn 'pushed '.scalar(@patron_issues);
                }
            }

            foreach my $item(keys(%out_to_patron_items))
            {
                my $lost_index;
                my $processing_index;

                for(my $i=0; $i<@patron_issues; ++$i)
                {
                    my $account_ref = $patron_issues[$i];
                    next unless $account_ref->{'itemnumber'} == $item;

                    if($account_ref->{'description'} =~ /^lost/i)
                    { $lost_index = $i; }
                    elsif($account_ref->{'description'} =~ /^process/i)
                    { $processing_index = $i; }
                }

                if($lost_index && $processing_index)
                {
                    my $lost_ref = $patron_issues[$lost_index];
                    my $processing_ref = $patron_issues[$processing_index];

                    $lost_ref->{'amount'} += $processing_ref->{'amount'};
                    $lost_ref->{'amountoutstanding'} += $processing_ref->{'amountoutstanding'};
                    splice(@patron_issues, $processing_index, 1);
                }
            }

            my $header_printed;
            my %checkouts_seen;
            for(sort { $b->{'amount'} <=> $a->{'amount'} } @patron_issues)
            {
                if($_->{'accounttype'} ne 'F' ||
                    $_->{'amountoutstanding'} == 0 ||
                    $_->{'amountoutstanding'} >= 500 ||
                    $_->{'description'} =~ /^lost/i ||
                    $_->{'description'} =~ /^processing/i)
                {
                    push(@problems, $_);
                    if($_->{'itemnumber'})
                    { push(@{$items_to_problems{$_->{'itemnumber'}}}, $_); }

                    if($_->{'checkout'} && !$checkouts_seen{$_->{'checkout'}})
                    {
                        $checkouts_seen{$_->{'checkout'}} = 1;

                        if(!$fine_header_printed)
                        {
                            print qq{      <fines>\n};
                            $fine_header_printed = 1;
                        }

                        print join(' ',
                            q{        <fine},
                           qq{id="f$_->{'accountlines_id'}"},
                            q{status="converted"},
                           qq{checkout="c$_->{'checkout'}"},
                            q{amountCents="0"},
                            qq{/>\n},
                            );
                    }
                }
                else
                {
                    my $amount;
                    my $amount_paid;
                    if($_->{'amount'} >= 0 && $_->{'amountoutstanding'} >= 0)
                    {
                        $amount = $_->{'amount'};
                        $amount_paid = $_->{'amount'} - $_->{'amountoutstanding'};
                        if($amount_paid < 0)
                        {
                            warn "Weird: amountoutstanding greater than amount";
                            $amount = $_->{'amountoutstanding'};
                            $amount_paid = 0;
#                             die join("\t", $_->{'borrowernumber'}, $_->{'amount'}, $_->{'amountoutstanding'});
                        }
                    }
                    elsif($_->{'amount'} >= 0 && $_->{'amountoutstanding'} < 0)
                    {
                        die join("\t", $_->{'borrowernumber'}, $_->{'amount'}, $_->{'amountoutstanding'});
                    }
                    elsif($_->{'amount'} < 0 && $_->{'amountoutstanding'} >= 0)
                    {
                        $amount = $_->{'amountoutstanding'};
                        $amount_paid = 0;
        #                 warn join("\t", $_->{'borrowernumber'}, $_->{'amount'}, $_->{'amountoutstanding'});
                    }
                    elsif($_->{'amount'} < 0 && $_->{'amountoutstanding'} < 0)
                    {
                        if($_->{'amount'} == $_->{'amountoutstanding'})
                        {
                            $amount = 0;
                            $amount_paid = -1*$_->{'amountoutstanding'};
                        }
                        else
                        { die join("\t", $_->{'borrowernumber'}, $_->{'amount'}, $_->{'amountoutstanding'}); }
                    }
                    $amount_owed_by_patron{$_->{'borrowernumber'}} += $amount - $amount_paid;



                    if(!$fine_header_printed)
                    {
                        print qq{      <fines>\n};
                        $fine_header_printed = 1;
                    }
# TODO: add them to %amount_owed_by_patron

# warn join("\t", $ref->{'borrowernumber'}, $_->{'amount'}, $_->{'amountoutstanding'}) if $_->{'amount'} != $_->{'amountoutstanding'};

                    print join(' ',
                        q{        <fine},
                       qq{id="f$_->{'accountlines_id'}"},
                        q{status="open"},
                        ($_->{'itemnumber'} ? qq{holding="h$_->{'itemnumber'}"} : ''),
                        ($amount ? qq{amountCents="$amount"} : ''),
                        ($amount_paid ? qq{amountPaidCents="$amount_paid"} : ''),
                       qq{/>\n},
                    );
                }
            }
        }

        if($fine_header_printed)
        { print qq{      </fines>\n}; }

        if($ref->{'password'} &&
           $ref->{'password'} ne 'X03MO1qnZdYdgyfeuILPmQ' && #"password"
           $ref->{'password'} ne 'MZ9NJuPFNrXdhxuyxS4xeA') #"PASSWORD"
        {
            print qq{      <passwords>\n};

            if($ref->{'password'} =~ /^\$2a\$08\$(.{22})(.{31})$/)
            {
                my $raw_salt = $1;
                my $raw_pw = $2;

                my $decoded_salt = MIME::Base64::decode_base64($raw_salt);
                my $encoded_salt = MIME::Base64::encode_base64($decoded_salt, '');
                my $decoded_pw = MIME::Base64::decode_base64($raw_pw);
                my $encoded_pw = MIME::Base64::encode_base64($decoded_pw, '');

                print join(' ',
                    q{        <password},
                    q{hashType="bcrypt"},
                    q{value="}."$encoded_pw".q{"},
                    q{salt="}."$encoded_salt".q{"},
                qq{/>\n},
                );
            }
            elsif($ref->{'password'} =~ /^\$2/)
            { die $ref->{'password'}; }
            else #assume MD5
            {
                my $decoded = MIME::Base64::decode_base64($ref->{'password'});
                my $encoded = MIME::Base64::encode_base64($decoded, '');

                print join(' ',
                    q{        <password},
                    q{hashType="md5"},
                    q{value="}."$encoded".q{"},
                qq{/>\n},
                );
            }

            print qq{      </passwords>\n};
        }

        print qq{    </patron>\n};
    }

    print qq{  </patrons>\n};

warn 'finished patrons';
}

sub do_biblios
{
warn 'starting biblios';
    my $count = 0;
    print qq{  <biblios>\n};
    my $sth = $dbh->prepare(
        q{SELECT biblioitemnumber,
                 marcxml,
                 (SELECT EXISTS
                            (SELECT * FROM items
                             WHERE items.biblioitemnumber = biblioitems.biblioitemnumber)
                   AND
                     NOT EXISTS
                            (SELECT * FROM items
                             WHERE items.biblioitemnumber = biblioitems.biblioitemnumber
                             AND COALESCE(withdrawn, FALSE) = FALSE)) AS deleted
          FROM biblioitems});
    $sth->execute();
    while( my ($biblioitemnumber, $marcxml, $deleted) = $sth->fetchrow_array())
    {
# next unless rand() > .9999;
        ++$count;
        warn $count unless $count % 500;

        my $marc;
        eval
        { $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8'); };
        unless($marc)
        {
            warn 'unable to read MARC XML';
            next;
        }
        
        my $biblio_added;
        foreach my $field($marc->field('005'))
        {
            my $field_data = $field->data();
            next unless $field_data =~ /^(\d{4})(\d{2})(\d{2})\d{6}\.\d$/;
            $biblio_added = "$1-$2-$3";
        }

        my $marc_fixed;
        eval
        { $marc_fixed = trim_marc_xml($marc); };
        if(!$marc_fixed)
        {
            warn 'unable to generate MARC XML';
            next;
        }

        print join(' ',
            q{    <biblio},
           qq{id="b$biblioitemnumber"},
            q{status="}.($deleted ? 'deleted' : 'active').q{"},
            ($biblio_added && $biblio_added !~ /00/ ? qq{added="$biblio_added"} : ''),
           qq{>\n},
          );

        print $marc_fixed;

        print qq{    </biblio>\n};
    }
    print qq{  </biblios>\n};

warn 'finished biblios';
}

sub do_holdings
{
warn 'starting holdings';
    print qq{  <holdings>\n};

    my $sth = $dbh->prepare(
        q{SELECT * FROM items});
    $sth->execute();
    while( my $ref = $sth->fetchrow_hashref())
    {
        my $problems = $items_to_problems{$ref->{'itemnumber'}};

        if($ref->{'itemlost'})
        {
            my $problem_found;
            if($problems)
            {
                for(reverse @{$problems})
                {
                    next unless $_->{'amount'} > 0;
                    $_->{'itemlost'} = $ref->{'itemlost'};
                    $problem_found = 1;
                    last;
                }
            }

            if(!$problem_found)
            {
                my %hash;
                $hash{'itemnumber'} = $ref->{'itemnumber'};
                $hash{'itemlost'} = $ref->{'itemlost'};
                push(@problems, \%hash);
                push(@{$items_to_problems{$ref->{'itemnumber'}}}, \%hash);
            }
        }
        elsif($ref->{'damaged'})
        {
            my $problem_found;
            if($problems)
            {
                for(reverse @{$problems})
                {
                    next unless $_->{'amount'} > 0;
                    $_->{'damaged'} = $ref->{'damaged'};
                    $problem_found = 1;
                    last;
                }
            }

            if(!$problem_found)
            {
                my %hash;
                $hash{'itemnumber'} = $ref->{'itemnumber'};
                $hash{'damaged'} = $ref->{'damaged'};
                push(@problems, \%hash);
                push(@{$items_to_problems{$ref->{'itemnumber'}}}, \%hash);
            }
        }


        my @memberships;
        my @notes;
        
        if($ref->{'itemnotes'} =~ /\S/)
        { push(@notes, $ref->{'itemnotes'}); }

        if($ref->{'itemtype'})
        {
            my $type_id = hex(substr(md5_hex($ref->{'itemtype'}), 27));
            push(@memberships, $type_id);
        }
        if($ref->{'ccode'})
        {
            my $type_id = hex(substr(md5_hex($ref->{'ccode'}), 27))+1000000000;
            push(@memberships, $type_id);
        }
        if($ref->{'location'})
        {
            my $type_id = hex(substr(md5_hex($ref->{'location'}), 27))+2000000000;
            push(@memberships, $type_id);
        }
        if($ref->{'booksellerid'})
        {
            my $type_id = hex(substr(md5_hex($ref->{'booksellerid'}), 27))+3000000000;
            push(@memberships, $type_id);
        }

        my $edited = $ref->{'timestamp'};
        ($edited) = split(/ /, $edited);
        my $added = $ref->{'dateaccessioned'};
        ($added) = split(/ /, $added);

        my ($list_price, $paid_price);
        if($ref->{'price'})
        { $paid_price = $ref->{'price'}; }
        if($ref->{'replacementprice'} && $ref->{'replacementprice'} != $ref->{'price'})
        { $list_price = $ref->{'replacementprice'}; }

        my $branch;
        if($branch_count > 1)
        { $branch = 'l'.hex(substr(md5_hex($ref->{'homebranch'}), 27)); }

        print join(' ',
            qq{    <holding},
           qq{id="h$ref->{'itemnumber'}"},
             ($ref->{'issues'} ? qq{usageCount="$ref->{'issues'}"} : ''),
            q{status="}.($ref->{'withdrawn'} ? 'deleted' : 'active').q{"},
            q{barcode="}.escapeHTML($ref->{'barcode'}).q{"},
           qq{biblio="b$ref->{'biblioitemnumber'}"},
            q{call="}.escapeHTML($ref->{'itemcallnumber'}).q{"},
            ($paid_price ? q{priceCents="}.round($paid_price*100).q{"} : ''),
            ($list_price ? q{priceListCents="}.round($list_price*100).q{"} : ''),
            ($added && $added !~ /-00/ ? qq{added="$added"} : ''),
            ($edited && $edited ne /-00/ ? qq{edited="$edited"} : ''),
            ($branch ? qq{location="$branch"} : ''),
            (@memberships || @notes) ? qq{>\n} : qq{/>\n},
        );

        if(@memberships || @notes)
        {
            if(@memberships)
            {
                for(@memberships)
                {
                    print qq{      <membership>hm$_</membership>\n};
                }
            }

            if(@notes)
            {
                print qq{      <holdingNotes>\n};
                for(@notes)
                {
                    print join(' ',
                        q{        <holdingNote},
                        q{active="true"},
                        q{note="}.escapeHTML($_).q{"},
                       qq{/>\n},
                      );
                }
                print qq{      </holdingNotes>\n};
            }

            print qq{    </holding>\n};
        }
    }

    print qq{  </holdings>\n};
warn 'finished holdings';
}

sub do_problems
{
    warn 'starting problems';

    my $header_printed;
    my $count = 0;
    for(@problems)
    {
        ++$count;

        if(!$header_printed)
        {
            print qq{  <problems>\n};
            $header_printed = 1;
        }

        my $problem_id = $_->{'accountlines_id'} ?
            $_->{'accountlines_id'} :
            $count + 4000000000;

        my $type;
        if($_->{'itemlost'})
        { $type = 'lost'; }
        elsif($_->{'damaged'})
        { $type = 'damaged'; }
        else
        { $type = 'other'; }

        my @line_array;

        push(@line_array,
            q{    <problem},
           qq{id="pr$problem_id"},
           qq{type="$type"},
          );

        if($_->{'borrowernumber'})
        {
# warn Dumper($_);
            next if !$_->{'itemlost'} &&
                    !$_->{'itemdamaged'} &&
                    !$_->{'amountoutstanding'};

            my $amount;
            my $amount_paid;
            if($_->{'amount'} >= 0 && $_->{'amountoutstanding'} >= 0)
            {
                $amount = $_->{'amount'};
                $amount_paid = $_->{'amount'} - $_->{'amountoutstanding'};
                if($amount_paid < 0)
                {
                    warn "Weird: amountoutstanding greater than amount";
                    $amount = $_->{'amountoutstanding'};
                    $amount_paid = 0;
#                     die join("\t", $_->{'borrowernumber'}, $_->{'amount'}, $_->{'amountoutstanding'});
                }
            }
            elsif($_->{'amount'} >= 0 && $_->{'amountoutstanding'} < 0)
            {
                die join("\t", $_->{'borrowernumber'}, $_->{'amount'}, $_->{'amountoutstanding'});
            }
            elsif($_->{'amount'} < 0 && $_->{'amountoutstanding'} >= 0)
            {
                $amount = $_->{'amountoutstanding'};
                $amount_paid = 0;
#                 warn join("\t", $_->{'borrowernumber'}, $_->{'amount'}, $_->{'amountoutstanding'});
            }
            elsif($_->{'amount'} < 0 && $_->{'amountoutstanding'} < 0)
            {
                if($_->{'amount'} == $_->{'amountoutstanding'})
                {
                    $amount = 0;
                    $amount_paid = -1*$_->{'amountoutstanding'};
                }
                else
                { die join("\t", $_->{'borrowernumber'}, $_->{'amount'}, $_->{'amountoutstanding'}); }
            }

            push(@line_array,
               qq{patron="p$_->{'borrowernumber'}"},
                q{statusPatron="}.($amount > 0 && $amount == $amount_paid ? 'resolved' : 'open').q{"},
              );

            if($amount)
            { push(@line_array, qq{amountChargedCents="$amount"}); }
            if($amount_paid)
            { push(@line_array, qq{amountPaidCents="$amount_paid"}); }
            $amount_owed_by_patron{$_->{'borrowernumber'}} += $amount - $amount_paid;
        }

        if($_->{'checkout'})
        {
            push(@line_array,
                qq{convertedFromFineId="f$_->{'accountlines_id'}"},
              );
        }
        if($_->{'itemnumber'})
        {
            push(@line_array,
               qq{holding="h$_->{'itemnumber'}"},
                q{statusHolding="}.($_->{'itemlost'} || $_->{'damaged'} ? 'open' : 'resolved').q{"},
              );
        }

        print join(' ', @line_array, '>')."\n";

        if($_->{'description'})
        {
            print qq{      <problemNotes>\n};
            print join(' ',
                q{        <problemNote},
                q{note="}.escapeHTML($_->{'description'}).q{"},
               qq{/>\n},
              );
            print qq{      </problemNotes>\n};
        }

        if($_->{'itemlost'} || $_->{'damaged'})
        {
            print qq{      <problemHoldingMovement>\n};
            print join(' ',
                q{        <problemHoldingLocation},
                q{location="}.($_->{'itemlost'} ? 'Missing' : 'Tech Serv').q{"},
               qq{/>\n},
              );
            print qq{      </problemHoldingMovement>\n};
        }

        print qq{    </problem>\n};
    }

    my $sth = $dbh->prepare(
        q{SELECT borrowernumber
          FROM borrowers});
    $sth->execute();
    while( my ($patron) = $sth->fetchrow_array())
    {
        my $amount = $amount_owed_by_patron{$patron};
        my ($correct_amount) = $dbh->selectrow_array(
            q{SELECT SUM(amountoutstanding)
              FROM accountlines
              WHERE borrowernumber = ?},{},
            ($patron));
        $correct_amount = round($correct_amount*100);
        warn join("\t", 'BAD OVERALL AMOUNT!!!!', $patron, $amount, $correct_amount, $amount-$correct_amount) if $amount != $correct_amount;
    }

    if($header_printed)
    { print qq{  </problems>\n}; }

    warn 'finished problems';
}

sub pre_process_checkouts
{
    warn 'preprocessing checkouts';

    $dbh->do(
        q{CREATE TEMPORARY TABLE checkouts
          LIKE old_issues}) or die $!;
    $dbh->do(
        q{INSERT INTO checkouts
          (SELECT * FROM issues)}) or die $!;
    $dbh->do(
        q{INSERT INTO checkouts
          (SELECT * FROM old_issues)}) or die $!;
    $dbh->do(
        q{ALTER TABLE checkouts
          ADD COLUMN id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
          ADD INDEX borrowernumber (borrowernumber),
          ADD INDEX itemnumber (itemnumber)}) or die $!;

    warn 'finished preprocessing checkouts';
}

sub do_checkouts
{
warn 'starting checkouts';
    print qq{  <checkouts>\n};

    my $sth = $dbh->prepare(
        q{SELECT * FROM checkouts
          ORDER BY timestamp, borrowernumber, itemnumber});
    $sth->execute();
    while( my $ref = $sth->fetchrow_hashref())
    {
# next unless rand() > .9999;

        my $out = $ref->{'issuedate'} ? $ref->{'issuedate'} : $ref->{'timestamp'};
        $out =~ s/ /T/;
        
        my $returned = $ref->{'returndate'};
        $returned =~ s/ /T/;

        my $due = $ref->{'duedate'} ? $ref->{'duedate'} : $ref->{'date_due'};
        ($due) = split(/ /, $due);

        my $renewals = $ref->{'renewals'};

        my $branch;
        if($branch_count > 1)
        { $branch = 'l'.hex(substr(md5_hex($ref->{'branchcode'}), 27)); }

        print join(' ',
            q{    <checkout},
           qq{id="c$ref->{'id'}"},
            q{type="normal"},
           ($ref->{'itemnumber'} ? qq{holding="h$ref->{'itemnumber'}"} : ''),
           ($out && $out ne '0000-00-00' ? qq{out="$out}.($out =~ /T/ ? '' : q{T00:00:00}).q{"} : ''),
            $due && $due !~ /-00/ ? qq{due="$due"} : '',
            ($ref->{'borrowernumber'} ? qq{patron="p$ref->{'borrowernumber'}"} : ''),
            ($returned && $returned !~ /0000-00-00/ ? qq{returned="$returned}.($returned =~ /T/ ? '' : q{T00:00:00}).q{"} : ''),
            ($returned && $returned !~ /0000-00-00/ ? '' : q{status="out"}),
            ($branch ? qq{outLocation="$branch"} : ''),
           ($renewals ? qq{>\n} : qq{/>\n}),
        );

        if($renewals)
        {
            print qq{      <renewals>\n};
            for(1..$renewals)
            { print qq{        <renewal/>\n}; }
            print qq{      </renewals>\n};

            print qq{    </checkout>\n};
        }
    }

    my $count = 1000000000;
# reserves in transit
    my $sth = $dbh->prepare(
        q{SELECT * FROM reserves
          WHERE found = 'T'});
    $sth->execute();
    while( my $ref = $sth->fetchrow_hashref())
    {
        ++$count;
        my $branchid = hex(substr(md5_hex($ref->{'branchcode'}), 27));

        my $out = $ref->{'timestamp'};
        $out =~ s/ /T/;

        print join(' ',
            q{    <checkout},
           qq{id="c$count"},
            q{type="transit"},
           qq{transitTo="l$branchid"},
           qq{holding="h$ref->{'itemnumber'}"},
           ($out && $out ne '0000-00-00' ? qq{out="$out}.($out =~ /T/ ? '' : q{T00:00:00}).q{"} : ''),
           qq{reserveId="r$ref->{'reserve_id'}"},
            q{status="out"},
           qq{/>\n},
        );
    }

# ready reserves
    my $sth = $dbh->prepare(
        q{SELECT * FROM reserves
          WHERE found = 'T'});
    $sth->execute();
    while( my $ref = $sth->fetchrow_hashref())
    {
        ++$count;
        my $branchid = hex(substr(md5_hex($ref->{'branchcode'}), 27));

        my $out = $ref->{'waitingdate'};
        $out =~ s/ /T/;

        print join(' ',
            q{    <checkout},
           qq{id="c$count"},
            q{type="reserve"},
           qq{outLocation="l$branchid"},
           qq{holding="h$ref->{'itemnumber'}"},
           ($out && $out ne '0000-00-00' ? qq{out="$out}.($out =~ /T/ ? '' : q{T00:00:00}).q{"} : ''),
            ($ref->{'borrowernumber'} ? qq{patron="p$ref->{'borrowernumber'}"} : ''),
           qq{reserveId="r$ref->{'reserve_id'}"},
            q{status="out"},
           qq{/>\n},
        );
    }    

    print qq{  </checkouts>\n};

warn 'finished checkouts';
}

sub do_booklists
{
# TODO: patron lists?
    warn 'starting booklists';

    my ($count) = $dbh->selectrow_array(
        q{SELECT COUNT(*)
          FROM virtualshelves
          WHERE category = 2});
    if($count)
    {
        print qq{  <booklists>\n};
        
        my $sth = $dbh->prepare(
            q{SELECT shelfnumber, shelfname
              FROM virtualshelves
              WHERE category = 2});
        $sth->execute();
        while( my ($shelf_id, $shelf_name) = $sth->fetchrow_array())
        {
            print join(' ',
                q{    <booklist},
               qq{id="bl$shelf_id"},
                q{name="}.escapeHTML($shelf_name).q{"},
               qq{>\n},
              );

            my $sth = $dbh->prepare(
                q{SELECT
                    (SELECT biblioitemnumber
                     FROM biblioitems
                     WHERE biblioitems.biblionumber = virtualshelfcontents.biblionumber)
                  FROM virtualshelfcontents
                  WHERE shelfnumber = ?});
            $sth->execute($shelf_id);
            while( my ($biblio) = $sth->fetchrow_array())
            {
                print qq{      <biblio>b$biblio</biblio>\n};
            }

            print qq{    </booklist>\n};
        }
        
        print qq{  </booklists>\n};
    }

    warn 'finished booklists';
}


sub state
{
    my ($input, $correct) = @_;
    $input = uc($input);
    my %hash = (
        'AL' => 'Alabama',
        'AK' => 'Alaska',
        'AS' => 'American Samoa',
        'AZ' => 'Arizona',
        'AR' => 'Arkansas',
        'CA' => 'California',
        'CO' => 'Colorado',
        'CT' => 'Connecticut',
        'DE' => 'Delaware',
        'DC' => 'Washington, D.C.',
        'FL' => 'Florida',
        'GA' => 'Georgia',
        'GU' => 'Guam',
        'HI' => 'Hawaii',
        'ID' => 'Idaho',
        'IL' => 'Illinois',
        'IN' => 'Indiana',
        'IA' => 'Iowa',
        'KS' => 'Kansas',
        'KY' => 'Kentucky',
        'LA' => 'Louisiana',
        'ME' => 'Maine',
        'MD' => 'Maryland',
        'MA' => 'Massachussets',
        'MI' => 'Michigan',
        'MN' => 'Minnesota',
        'MS' => 'Mississippi',
        'MO' => 'Missouri',
        'MT' => 'Montana',
        'NE' => 'Nebraska',
        'NV' => 'Nevada',
        'NH' => 'New Hampshire',
        'NJ' => 'New Jersey',
        'NM' => 'New Mexico',
        'NY' => 'New York',
        'NC' => 'North Carolina',
        'ND' => 'North Dakota',
        'MP' => 'Northern Mariana Islands',
        'OH' => 'Ohio',
        'OK' => 'Oklahoma',
        'OR' => 'Oregon',
        'PA' => 'Pennsylvania',
        'PR' => 'Puerto Rico',
        'RI' => 'Rhode Island',
        'SC' => 'South Carolina',
        'SD' => 'South Dakota',
        'TN' => 'Tennessee',
        'TX' => 'Texas',
        'UM' => 'United States Minor Outlying Islands',
        'UT' => 'Utah',
        'VT' => 'Vermont',
        'VI' => 'Virgin Islands, U.S.',
        'VA' => 'Virginia',
        'WA' => 'Washington',
        'WV' => 'West Virginia',
        'WI' => 'Wisconsin',
        'WY' => 'Wyoming',
      );
    if($hash{$input})
    { return $input; }

    if($correct)
    {
        my %reverse_hash;
        for(keys(%hash))
        { $reverse_hash{uc($hash{$_})} = $_; }
        if($reverse_hash{$input})
        { return $reverse_hash{$input}; }

        return &state_correct($input);
    }

    return;
}

sub state_correct
{
    my ($input) = @_;

    return;
}


sub sanitize
{
    my ($input) = @_;

    $input =~ s/^\s*//;
    $input =~ s/\s*$//;
    $input =~ s/\s+/ /g;

    if($input =~ /\(/ && $input !~ /\)/)
    { $input = "$input)"; }
    if($input =~ /\[/ && $input !~ /\]/)
    { $input = "$input]"; }

    return $input;
}

sub get_ldif_xml_start {
    my ($ldif_version,$version,$name) = @_;
    return (
        qq{<?xml version="1.0" encoding="UTF-8"?>\n}.
        join(
            "\n",
            q{<ldif:library name=""},
            q{    xmlns:marc="http://www.loc.gov/MARC21/slim"},
            q{    xmlns:ldif="http://biblionix.com/ldif"},
            qq{    version="$ldif_version">},
            q{},
            qq{  <system name="$name" vendor="biblionix" version="$version"/>\n}
        )
    );
}


# takes an phone array ref, and phone string and pushes onto the 
# array if the phone is good
sub phone {
    my ($phone_numbers,$raw,$phone_count)    = @_;
    my $number = $raw;
    if ( $number =~ /^(.+?)\S*x.+/ ) {
        $number = $1;
    }
    $number =~ s/\D//g;
    $number =~ s/^1//;
    return unless $number;
    
    unless ( $number =~ /^1?(\d{3})?(\d{7})$/ ) {
#         warn "bad number: $number";
        return;
    }

    my ( $area, $main_number ) = ( $1, $2 );

    my %phone_hash;
    $phone_hash{'countryCode'} = 1;
    if ($area) { $phone_hash{'areaCode'} = $area; }
    $phone_hash{'number'} = $main_number;
    $phone_hash{'id'} = "ph$phone_count" if ($phone_count);

    my $type = $raw;
    $type =~ s/\d//g;
    $type =~ s/\W//g;
    $type = sanitize($type);
    if ($type) {
        if (   $type =~ /work/i
            || $type =~ /wk/i
            || $type =~ /office/i
            || $type =~ /^w$/i )
        {
            $phone_hash{'type'} = 'work';
        }
        elsif ( $type =~ /cell/i || $type =~ /^c$/i ) {
            $phone_hash{'type'} = 'mobile';
        }
        elsif ( $type =~ /hm/i || $type =~ /home/i ) {
            $phone_hash{'type'} = 'home';
        }
        else { $phone_hash{'type'} = 'other'; }
    }
    push(@$phone_numbers,\%phone_hash);
}

sub email_valid {
    my ($email) = @_;
    if ( $email && Email::Valid->address( -address => $email ) &&
           $email =~ /^([\.a-zA-Z0-9_\\\-\/])+@([a-zA-Z0-9_\-])+(([a-zA-Z0-9_\-])*\.([a-zA-Z0-9_\-])+)+$/ ) {
      return 1;
    }
    return 0;
}


sub tmstr_to_iso {
    my ($tm_raw, $show_time) = @_;
    return unless ($tm_raw);
    #print ("str2time=".str2time($tm_raw)."\n");
    my $tm = localtime(str2time($tm_raw));
    return &tm_to_iso($tm,$show_time);
}

sub tm_to_iso
{
    my ($tm, $show_time) = @_;
    my $return =
            join('-',
                $tm->year+1900,
                sprintf("%02d", $tm->mon+1),
                sprintf("%02d", $tm->mday),
              );

    if($show_time)
    {
        $return .= 'T'.
            join(':',
                sprintf("%02d",$tm->hour),
                sprintf("%02d",$tm->min),
                sprintf("%02d",$tm->sec),
              );
    }
    return $return;
}


sub tmstr_to_iso {
    my ($tm_raw, $show_time) = @_;
    return unless ($tm_raw);
    #print ("str2time=".str2time($tm_raw)."\n");
    my $tm = localtime(str2time($tm_raw));
    return &tm_to_iso($tm,$show_time);
}

sub tm_to_iso
{
    my ($tm, $show_time) = @_;
    my $return =
            join('-',
                $tm->year+1900,
                sprintf("%02d", $tm->mon+1),
                sprintf("%02d", $tm->mday),
              );

    if($show_time)
    {
        $return .= 'T'.
            join(':',
                sprintf("%02d",$tm->hour),
                sprintf("%02d",$tm->min),
                sprintf("%02d",$tm->sec),
              );
    }
    return $return;
}



sub trim_marc_xml
{
    my ($data) = @_;

    if(ref($data)) #we have a MARC::Record object
    {
        my $marc = $data;
        foreach my $field($marc->fields())
        {
            if(!$field->is_control_field())
            {
                foreach my $subfield($field->subfields())
                {
                    if(length($subfield->[0]) > 1 ||
                       $subfield->[0] eq '[' ||
                       $subfield->[0] eq ']')
                    { $field->delete_subfield(code => $subfield->[0]); }
                }
            }
        }

        $data = $marc->as_xml_record();
    }

    my @return;
    my @marc_xml = split(/\n/, $data);
    my $leader_found;
    my $datafield_found;
    my $out_of_order;
    for(@marc_xml)
    {
        next if $_ =~ /tag="000"/;

        if(!$leader_found)
        {
            if($_ =~ /^\s+<leader>/)
            {
                $leader_found = 1;
                push(@return, q{      <marc:record>});
            }
        }

        if($leader_found)
        {
            my $value = $_;
            if($value =~ /subfield code="(.)"/ && $1 !~ /[a-z]/i)
            {
#                 $value =~ s/subfield code="."/subfield code=" "/;
            }

            if($value =~ /^\s*<leader>/)
            {
                # Some of our deleted biblios seem to have three spaces
                # where there should be something else
                $value =~ s/^(\s*<leader>\d{5})   /\1cam/;

                $value =~ /^(.+)(.{4})<\/leader>$/;
                my ($first_part, $last4) = ($1, $2);
                if($last4 ne '    ' && $last4 ne '4500')
                { $value = $first_part.'4500</leader>'; }

                $value =~ /^(\s*<leader>.{8})(.)(.+)/;
                my ($first_part, $control_type, $last_part) = ($1, $2, $3);
                if($control_type !~ /[ A-Za-z]/)
                { $value = $first_part.' '.$last_part; }

                $value =~ /^(\s*<leader>.{17})(.)(.+)/;
                my ($first_part, $encoding_level, $last_part) = ($1, $2, $3);
                if($encoding_level !~ /[ A-Za-z]/)
                { $value = $first_part.' '.$last_part; }

                $value =~ /^(\s*<leader>.{18})(.)(.+)/;
                my ($first_part, $descriptive_form, $last_part) = ($1, $2, $3);
                if($descriptive_form !~ /[ A-Za-z]/)
                { $value = $first_part.' '.$last_part; }

                if($value !~ /[\d ]{5}[\dA-Za-z ]{1}[\dA-Za-z]{1}[\dA-Za-z ]{3}(2| )(2| )[\d ]{5}[\dA-Za-z ]{3}(4500|    )/)
                {
                    warn "Attempting to correct invalid leader: $value";

                    my $record_length;
                    my $base_address;
                    my $unicode;

                    if($value =~ /^\s*<leader>(\d{5})/)
                    { $record_length = $1; }
                    if($value =~ /([ a])22(\d{5}).{7}<\/leader>$/i)
                    {
                        $unicode = $1 eq 'a';
                        $base_address = $2;
                    }

                    if($record_length && $base_address)
                    {
                        $value = join('',
                            '<leader>',
                            $record_length,
                            'cam ',
                            ($unicode ? 'a' : ' '),
                            '22',
                            $base_address,
                            '   4500',
                            '</leader>');
                    }
                    else
                    { warn "BAD LEADER $value"; return; }
                }
            }

            if($value =~ /ind1="(.)"/)
            {
                my $ind1 = $1;
                if($ind1 !~ /[ 0-9]/)
                { $value =~ s/ind1="(.)"/ind1=" "/; }
            }
            if($value =~ /ind2="(.)"/)
            {
                my $ind2 = $1;
                if($ind2 !~ /[ 0-9]/)
                { $value =~ s/ind2="(.)"/ind2=" "/; }
            }

            $value =~ s/^(\s*<\/?)(.+?)>/\1marc:\2>/;
            $value =~ s/\S(.*)<\/(.+?)>/\1<\/marc:\2>/;
            if($value =~ /^(\s*)(.+)/)
            {
                my ($spaces, $data) = ($1, $2);
                if($data =~ /^marc:/)
                { $value = "$spaces<$data"; }
            }
            if(!$out_of_order)
            {
                if(!$datafield_found)
                {
                    if($value =~ /^  <marc:datafield/)
                    { $datafield_found = 1; }
                }
                elsif($value =~ /^  <marc:controlfield/)
                { $out_of_order = 1; }
            }
            push(@return, q{      }.xml($value));
        }
    }

    if($out_of_order)
    {
        my @controlfields;
        my @datafields;
        my @new_return;
# start with <marc:record> and the leader
        push(@new_return, shift(@return));
        push(@new_return, shift(@return));
        my $footer = pop(@return);

        my $marc_xml = join('___SEPARATOR___', @return);
        while($marc_xml =~ s/(\s+<marc:controlfield.+?<\/marc:controlfield>)//)
        { push(@controlfields, $1); }
        while($marc_xml =~ s/(\s+<marc:datafield.+?<\/marc:datafield>)//)
        {
            my $data = $1;
            $data =~ s/___SEPARATOR___/\n/g;
            push(@datafields, $data);
        }

        push(@new_return, @controlfields);
        push(@new_return, @datafields);
        push(@new_return, $footer);

        @return = @new_return;
    }

    return join("\n", @return)."\n";
}


sub xml
{
    my @input = @_;

    my @result;
    foreach my $data(@input)
    {  
        $data =~ tr/\x00-\x1f//d;
        $data =~ s/\xff\xfe//g;
        $data =~ s/\xff\xff//g;
        push(@result, $data);
    }

    return wantarray ? @result : $result[0];
}