#!/usr/bin/perl
use strict;
my($app_svnid) = '$HeadURL$ $LastChangedRevision$';
#  Allow bare words, so &ade_err_error() calls look nicer.
no strict 'subs';
use lib substr `ade-config ade_include_prefix`,0,-1;

use ADE;
use Getopt::Long qw(:config no_ignore_case);
use DBI;
use Data::Dumper;
use Finance::Quote;

my(%iofdb_defined_errors) = (
    iofdb_err_misc         => { fmt => '%s' },
    iofdb_err_database     => { fmt => 'database error (%s)' },
);

#  Options
my($opt_displaytype, $opt_displayformat, $opt_displaycurrency, $opt_interactive);

#  Load the location where the database is stored.
my($iofdb_state_prefix);
eval `iofdb-config --format=perl iofdb_state_prefix`;

#  Other globals
my($get_price_from_internet_timeout) = 10;
my($get_price_from_internet_maxretries) = 5;
my($get_price_from_internet_retryinterval) = 10;
my($fmtstr_date) = '%-24s';
my($fmtstr_currency) = '%-22s';
my($fmtstr_price) = '%6.3f';
my($fmtstr_currency_type) = '%-14s';
my($fmtstr_id) = '%-35s';
my($fmtstr_status) = '%-6s';
my($fmtstr_quantity_currency) = '%9.2f';
my($fmtstr_quantity_listed_stock) = '%6d   ';
my($png_output_file) = "/tmp/$ade_app_progname.$$.png";
my($gnuplot_cmd) = 'gnuplot';
my($old_price_age) = 3600 * 24 * 7;

sub iofdb
{
    my($errstack_ref) = @_;
    my($rc, $listpaths, $optval);
    my($action, $objecttype, $dbh);

    ###########################################################################
    #
    #  Set ADE options
    #
    ###########################################################################

    #  Register application-specific errors
    &ade_err_registerdefderrs(\%iofdb_defined_errors);

    ##########################################################################
    #
    #  Process options
    #
    ##########################################################################

    #  Defaults for options
    $opt_displaytype     = 'current';
    $opt_displayformat   = 'human';
    $opt_displaycurrency = undef;
    $opt_interactive     = 1;

    #  Register options
    if (($rc=&ade_opt_register($errstack_ref, undef, "display-type:s,display-format:s,display-currency:s,interactive:s", "main::iofdb_opt_handler_%s")) != $ade_err_ok) {
        return($rc);
    }

    #  Register handler functions
    if (($rc=&ade_msg_register($errstack_ref, \&iofdb_usage, \&iofdb_version, \&iofdb_listpaths)) != $ade_err_ok) {
        return($rc);
    } 

    #  Process options
    &ade_err_debug($errstack_ref, 10, "iofdb: processing options ...");
    if (($rc=&ade_opt_process($errstack_ref)) != $ade_err_ok) {
        return($rc);
    }

    ##########################################################################
    #
    #  Process arguments
    #
    ##########################################################################

    &ade_err_debug($errstack_ref, 10, "main: processing arguments ...");
    #  Whatever we are doing an action and an object type must be specified.
    &ade_msg_usage($errstack_ref) if (!defined($ARGV[1]));
    $action = $ARGV[0];
    $objecttype = $ARGV[1];
    &ade_err_debug($errstack_ref, 10, "main: action=$action, objectype=$objecttype");

    #  Accept close instead of destroy.
    $action = 'destroy' if ($action eq 'close');

    if ($action !~ /^(?:update|create|destroy|display)$/) {
        ade_err_error($errstack_ref, iofdb_err_misc, "$action: invalid action");
        return($ade_err_fail);
    }

    if ($objecttype !~ /^(?:accounts?|prices?|balances?|holdings?)$/) {
        ade_err_error($errstack_ref, iofdb_err_misc, "$objecttype: invalid object type");
        return($ade_err_fail);
    }

    #  User must create and destroy singular objects (but internally everything plural)
    if ($action =~ /^(create|destroy)$/ && $objecttype =~ /s$/) {
        ade_err_error($errstack_ref, iofdb_err_misc, "$action: expects singular object");
        return($ade_err_fail);
    }

    #  User must pdate and display plural objects
    if ($action =~ /^(update|display)$/ && $objecttype !~ /s$/) {
        ade_err_error($errstack_ref, iofdb_err_misc, "$action: expects plural object");
        return($ade_err_fail);
    }

    ##########################################################################
    #
    #  Guts start here
    #
    ##########################################################################

    &ade_err_debug($errstack_ref, 10, "main: connecting ...");
    #  For CSV
    #$dbh = DBI->connect("DBI:CSV:f_dir=$iofdb_state_prefix");
    #  For SQLite
    $dbh = DBI->connect("DBI:SQLite:dbname=$iofdb_state_prefix/$ade_app_progname.db",'','');
    if (!$dbh) {
        &ade_err_error($errstack_ref, iofdb_err_database, $DBI::errstr);
        return($ade_err_fail);
    }
 
    #  Check access to required tables.
    &ade_err_debug($errstack_ref, 10, "main: checking table access ...");
    if (($rc=&check_table_access($errstack_ref, $dbh)) != $ade_err_ok) {
        $dbh->disconnect();
        return($rc);
    }
 
    ade_err_debug($errstack_ref, 10, "main: action=$action, objecttype=$objecttype");
    if ($action eq 'create') {
        $rc = &create($errstack_ref, $dbh, @ARGV[1..$#ARGV]);
    } elsif ($action eq 'destroy') {
        $rc = &destroy($errstack_ref, $dbh, @ARGV[1..$#ARGV]);
    } elsif ($action eq 'display') {
        $rc = &display($errstack_ref, $dbh, @ARGV[1..$#ARGV]);
    } elsif ($action eq 'update') {
        $rc = &update($errstack_ref, $dbh, @ARGV[1..$#ARGV]);
    } else {
        &ade_err_internal($errstack_ref, "main: we should never get here (action=$action)");
    }

    $dbh->disconnect();

    return($rc) if ($rc != $ade_err_ok);

    return($ade_err_ok);
}

sub iofdb_opt_handler_display_type
{
    my($errstack_ref, $displaytype) = @_;

    &ade_msg_usage($errstack_ref) if ($displaytype !~ /^(?:current|history|none)$/);

    $opt_displaytype = $displaytype;

    return($ade_err_ok);
}

sub iofdb_opt_handler_display_format
{
    my($errstack_ref, $displayformat) = @_;
    
    &ade_msg_usage($errstack_ref) if ($displayformat !~ /^(?:human|iofdb|png)$/);

    $opt_displayformat = $displayformat;

    return($ade_err_ok);
}

sub iofdb_opt_handler_display_currency
{
    my($errstack_ref, $displaycurrency) = @_;
    
    $opt_displaycurrency = $displaycurrency;

    return($ade_err_ok);
}

sub iofdb_opt_handler_interactive
{
    my($errstack_ref, $interactive) = @_;
    
    &ade_err_debug($errstack_ref, 10, "iofdb_opt_handler_interactive: interactive=$interactive");
    &ade_msg_usage($errstack_ref) if ($interactive ne "yes" && $interactive ne "no");

    $opt_interactive = ($interactive eq "yes") ? 1 : 0;

    return($ade_err_ok);
}

sub iofdb_version
{
    my($errstack_ref, $version_text_ref) = @_;

    return(&ade_smf_extractversionfromsvnstring($errstack_ref, $app_svnid, $version_text_ref));
}

sub iofdb_listpaths
{
    my($errstack_ref, $pathlist_text_ref) = @_;
    my($rc);

    ${$pathlist_text_ref} = undef;

    return($ade_err_ok);
}

sub iofdb_usage
{
    my($errstack_ref, $usage_text_ref, $passno) = @_;

    if ($passno == 1) {
        ${$usage_text_ref} = "<action> <object-type> [ <object-name> ... ]";
    } elsif ($passno == 2) {
        ${$usage_text_ref} = "             --display-currency=<currency>   specify local currency\n" .
                             "             --display-format=<format>       display in specified data format\n" .
                             "             --display-type=<type>           display only specified data type\n" .
                             "             --interactive={yes|no}          allow interaction or not\n" .
                             "\n" .
                             "Actions:         create                      create something\n" .
                             "                 update                      update something\n" .
                             "                 destroy                     destroy something\n" .
                             "                 display                     display something\n" .
                             "\n" .
                             "Object types:    account(s)                  do something to account(s)\n" .
                             "                 price(s)                    do something to price(s)\n" .
                             "                 holding(s)                  do something to holding(s)\n" .
                             "                 balance(s)                  do something to balance(s)\n" .
                             "Display types:   current                     only current data (default)\n" .
                             "                 history                     all available data\n" .
                             "                 none                        display no data\n" .
                             "\n" .
                             "Display formats: human                       human readable (default)\n" .
                             "                 png                         PNG image format";
    }

    return($ade_err_ok);
} 

##############################################################################
#
#  LEVEL ONE FUNCTIONS:  create/destroy/display/update
#
##############################################################################

sub create
{
    my($errstack_ref, $dbh, $objecttype, @parameters) = @_;
    my($rc);

    #  Note that *this* function create() is only ever invoked by the user, not
    #  called from elsewhere in this program. This means that we can safely
    #  tell the underlying functions to assign a timestamp themselves. (Whereas
    #  when these functions are called from inside update_*() then *those*
    #  functions assign a timestamp so as to ensure all updates get the same
    #  timestamp; in turn to allow multiplot graphs.)
    if ($objecttype eq 'account') {
        $rc = &create_account($errstack_ref, $dbh, undef, @parameters);
    } elsif ($objecttype eq 'balance') {
        $rc = &create_balance($errstack_ref, $dbh, undef, @parameters);
    } elsif ($objecttype eq 'holding') {
        $rc = &create_holding($errstack_ref, $dbh, undef, @parameters);
    } elsif ($objecttype eq 'price') {
        $rc = &create_price($errstack_ref, $dbh, undef, @parameters);
    } else {
        &ade_err_internal($errstack_ref, "create: iofdb() should ensure we never get here");
    }

    return($rc) if ($rc != $ade_err_ok);

    return($ade_err_ok);
}

sub destroy
{
    my($errstack_ref, $dbh, $objecttype, @parameters) = @_;
    my($rc);

    if ($objecttype eq 'account') {
        $rc = &destroy_account($errstack_ref, $dbh, @parameters);
    } elsif ($objecttype eq 'balance') {
        $rc = &destroy_balance($errstack_ref, $dbh, @parameters);
    } elsif ($objecttype eq 'holding') {
        $rc = &destroy_holding($errstack_ref, $dbh, @parameters);
    } elsif ($objecttype eq 'price') {
        $rc = &destroy_price($errstack_ref, $dbh, @parameters);
    } else {
        &ade_err_internal($errstack_ref, "destroy: iofdb() should ensure we never get here");
    }

    return($rc) if ($rc != $ade_err_ok);

    return($ade_err_ok);
}

sub display
{
    my($errstack_ref, $dbh, $objecttype, @parameters) = @_;
    my($rc, $out_handle);

    #  Post-processing prologue
    if ($opt_displayformat eq 'human') {
        $out_handle = \*STDOUT;
    } elsif ($opt_displayformat eq 'png') {
        &ade_tmp_registerfile($errstack_ref, $png_output_file);
        if (!open(GNUPLOT_HANDLE, "|$gnuplot_cmd")) {
            &ade_err_error($errstack_ref, ade_err_access, $gnuplot_cmd, "execute");
            return($ade_err_fail);
        }
        $out_handle = \*GNUPLOT_HANDLE;
    } else {
        &ade_err_internal($errstack_ref, "display_accounts: not implemented or this should not happen");
    }
    
    if ($objecttype eq 'accounts') {
        $rc = &display_accounts($errstack_ref, $dbh, $out_handle, @parameters);
    } elsif ($objecttype eq 'balances') {
        $rc = &display_balances($errstack_ref, $dbh, $out_handle, @parameters);
    } elsif ($objecttype eq 'holdings') {
        $rc = &display_holdings($errstack_ref, $dbh, $out_handle, @parameters);
    } elsif ($objecttype eq 'prices') {
        $rc = &display_prices($errstack_ref, $dbh, $out_handle, @parameters);
    } else {
        &ade_err_internal($errstack_ref, "display: iofdb() should ensure we never get here");
    }
    return($rc) if ($rc != $ade_err_ok);

    #  Post-processing epilogue
    if ($out_handle != \*STDOUT) {
        close $out_handle;
        if (!open(PNG_HANDLE, $png_output_file)) {
            &ade_err_error($errstack_ref, ade_err_access, $png_output_file, "read");
            return($ade_err_fail);
        }
        local $/;
        print <PNG_HANDLE>;
        close PNG_HANDLE;
        unlink($png_output_file);
        &ade_tmp_deregisterfile($errstack_ref, $png_output_file);
    }

    return($ade_err_ok);
}

sub update
{
    my($errstack_ref, $dbh, $objecttype, @parameters) = @_;
    my($rc);

    if ($objecttype eq 'accounts') {
        $rc = &update_accounts($errstack_ref, $dbh, @parameters);
    } elsif ($objecttype eq 'balances') {
        $rc = &update_balances($errstack_ref, $dbh, @parameters);
    } elsif ($objecttype eq 'holdings') {
        $rc = &update_holdings($errstack_ref, $dbh, @parameters);
    } elsif ($objecttype eq 'prices') {
        $rc = &update_prices($errstack_ref, $dbh, @parameters);
    } else {
        &ade_err_internal($errstack_ref, "update: iofdb() should ensure we never get here");
    }

    return($rc) if ($rc != $ade_err_ok);

    return($ade_err_ok);
}

##############################################################################
#
#  LEVEL TWO ACCOUNT FUNCTIONS:  create_account/destroy_account/display_accounts/update_accounts
#
##############################################################################

sub create_account
{
    my($errstack_ref, $dbh, $timestamp, @parameters) = @_;
    my($id, $type, $currency, $quoted_currency, $status);
    my($rc);
  
    #  Process parameters
    if ($#parameters+1 != 4) {
        &ade_err_internal($errstack_ref, "usage: create account <id> <type> <currency> <quoted-currency>");
    }
    $id = $parameters[0];
    $type = $parameters[1];
    $currency = $parameters[2];
    $quoted_currency = $parameters[3];
    &ade_err_debug($errstack_ref, 10, "create_account: id=\"$id\", type=\"$type\", currency=\"$currency\", quoted_currency=\"$quoted_currency\"");
 
    #  Sanity checks.
    if (($rc=&account2status($errstack_ref, $dbh, $id, \$status)) != $ade_err_ok) {
        return($rc);
    }
    if ($status eq 'OPEN') {
        &ade_err_error($errstack_ref, ade_err_misc, "$id: already open");
        return($ade_err_fail);
    }
    #  Type is either CURRENCY or LISTED_STOCK.
    if ($type ne 'CURRENCY' && $type ne 'LISTED_STOCK' && $type ne 'UNLISTED_STOCK') {
        &ade_err_error($errstack_ref, ade_err_misc, "$type: invalid account type");
        return($ade_err_fail);
    }
    #  Currencies have empty quoted_currency.
    if ($type eq 'CURRENCY' && $quoted_currency ne '') {
        &ade_err_error($errstack_ref, ade_err_misc, "$id: is a currency therefore quoted currency should be empty");
        return($ade_err_fail);
    }
    #  Listed stocks have non-empty quoted_currency.
    if (($type eq 'LISTED_STOCK' || $type eq 'UNLISTED_STOCK') && $quoted_currency eq '') {
        &ade_err_error($errstack_ref, ade_err_misc, "$id: is a listed or unlisted stock therefore quoted currency should not be empty");
        return($ade_err_fail);
    }

    #  Open the account.
    $timestamp = time if (!defined($timestamp));
    $status = 'OPEN';
    if (!$dbh->do("INSERT INTO accounts (timestamp, id, type, currency, quoted_currency, status) VALUES (?, ?, ?, ?, ?, ?);", undef, $timestamp, $id, $type, $currency, $quoted_currency, $status)) {
        &ade_err_error($errstack_ref, iofdb_err_database, $dbh->errstr);
        return($ade_err_fail);
    }
    
    #  Set initial quantity to 0.
    if (($rc=&create_holding($errstack_ref, $dbh, undef, $id, 0)) != $ade_err_ok) {
        return($rc);
    }

    return($ade_err_ok);
}

sub destroy_account
{
    my($errstack_ref, $dbh, @parameters) = @_;
    my($id, $status, $timestamp, $type, $currency, $quantity, $ary_ref);
    my($rc);
    
    #  Process parameters
    if ($#parameters+1 != 1) {
        &ade_err_internal($errstack_ref, "usage: destroy account <id>");
    }
    $id = $parameters[0];
    &ade_err_debug($errstack_ref, 10, "destroy_account: id=\"$id\"");
 
    if (($rc=&account2status($errstack_ref, $dbh, $id, \$status)) != $ade_err_ok) {
        return($rc);
    }

    #  Check account is open before closing it.
    if ($status ne 'OPEN') {
        &ade_err_error($errstack_ref, ade_err_misc, "$id: no such open account");
        return($ade_err_fail);
    }
    
    #  Check the account is empty before closing it.
    if (!($ary_ref = $dbh->selectcol_arrayref("SELECT quantity FROM current_holdings WHERE id == '$id';"))) {
        &ade_err_error($errstack_ref, iofdb_err_database, $dbh->errstr);
        return($ade_err_fail);
    }
    if ($#{$ary_ref}+1 != 1) {
        &ade_err_internal($errstack_ref, "destroy_account: more than one row returned");
    }
    if (${$ary_ref}[0] != 0) {
        &ade_err_error($errstack_ref, ade_err_misc, "$id: not empty");
        return($ade_err_fail);
    }

    $timestamp = time;
    $status = 'CLOSED';
    $type = 'NULL';
    $currency = 'NULL';
    if (!$dbh->do("INSERT INTO accounts (timestamp, id, type, currency, status) VALUES (?, ?, ?, ?, ?);", undef, $timestamp, $id, $type, $currency, $status)) {
                &ade_err_error($errstack_ref, iofdb_err_database, $dbh->errstr);
                return($ade_err_fail);
            }
    
    return($ade_err_ok);
}

sub display_accounts
{
    my($errstack_ref, $dbh, $out_handle, @parameters) = @_;
    my($timestamp, $id, $type, $currency, $status);
    my($row_refs_ref, $row_ref, $filter, $body);
  
    #  Process parameters
    if ($#parameters+1 > 1) {
        &ade_err_internal($errstack_ref, "usage: display accounts [ <id-regexp> ]");
    }
    $filter = (defined($parameters[0]) && $parameters[0] ne '') ? $parameters[0] : '.*';
    &ade_err_debug($errstack_ref, 10, "display_accounts: filter=\"$filter\"");
 
    #  Execute the SQL to get the data we want.
    if ($opt_displaytype eq 'current' && !($row_refs_ref=$dbh->selectall_arrayref("SELECT timestamp, id, type, currency, status FROM current_accounts ORDER BY type, id;", { Slice => {} }))) {
        &ade_err_error($errstack_ref, iofdb_err_database, $dbh->errstr);
        return($ade_err_fail);
    } elsif ($opt_displaytype eq 'history' && !($row_refs_ref = $dbh->selectall_arrayref("SELECT timestamp, id, type, currency, status FROM accounts ORDER BY timestamp;", { Slice => {} }))) {
        &ade_err_error($errstack_ref, iofdb_err_database, $dbh->errstr);
        return($ade_err_fail);
    } 
    
    #  Report header
    if ($opt_displayformat eq 'human') {
        print $out_handle "Accounts\n" . '=' x 78 . "\n";
    } elsif ($opt_displayformat eq 'png') {
        &print_gnuplot_preamble($errstack_ref, $out_handle, "Accounts ($filter)");
    } else {
        &ade_err_internal($errstack_ref, "display_accounts: not implemented or this should not happen");
    }

    #  Report body
    foreach $row_ref (@{$row_refs_ref}) {
        next if ($row_ref->{id} !~ $filter);
        if ($opt_displayformat eq 'human') {
            $body .= sprintf("$fmtstr_date $fmtstr_id $fmtstr_currency_type $fmtstr_currency $fmtstr_status\n", scalar(localtime($row_ref->{timestamp})), $row_ref->{id}, $row_ref->{type}, $row_ref->{currency}, $row_ref->{status});
        } elsif ($opt_displayformat eq 'png') {
            $body .= sprintf("%d %d\n", $row_ref->{timestamp}, ($row_ref->{status} eq 'OPEN') ? 1 : 0);
        } else {
            &ade_err_internal($errstack_ref, "display_accounts: not implemented or this should not happen");
        } 
    }
    print $out_handle $body;
 
    #  Report footer
    if ($opt_displayformat eq 'human') {
        print $out_handle "\n";
    } elsif ($opt_displayformat eq 'png') {
        print $out_handle "e\n";
    } else {
        &ade_err_internal($errstack_ref, "display_accounts: not implemented or this should not happen");
    } 

    return($ade_err_ok);
}

sub update_accounts
{
    my($errstack_ref, $dbh, @parameters) = @_;

    &ade_err_internal($errstack_ref, "update_accounts: not implemented");
}

##############################################################################
#
#  LEVEL TWO HOLDINGS FUNCTIONS:  create_holding/destroy_holding/display_holdings/update_holdings
#
##############################################################################

sub create_holding
{
    my($errstack_ref, $dbh, $timestamp, @parameters) = @_;
    my($id, $quantity, $status);
    my($rc);
  
    #  Process parameters
    if ($#parameters+1 != 2) {
        &ade_err_internal($errstack_ref, "usage: create holding <id> <quantity>");
    }
    $id = $parameters[0];
    $quantity = $parameters[1];
    &ade_err_debug($errstack_ref, 10, "create_holding: id=\"$id\", quantity=\"$quantity\"");
 
    #  Sanity checks.
    if (($rc=&account2status($errstack_ref, $dbh, $id, \$status)) != $ade_err_ok) {
        return($rc);
    }
    if ($status ne 'OPEN') {
        &ade_err_error($errstack_ref, ade_err_misc, "$id: account not open");
        return($ade_err_fail);
    }

    $timestamp = time if (!defined($timestamp));
    if (!$dbh->do("INSERT INTO holdings (timestamp, id, quantity) VALUES (?, ?, ?);", undef, $timestamp, $id, $quantity)) {
                &ade_err_error($errstack_ref, iofdb_err_database, $dbh->errstr);
                return($ade_err_fail);
            }
    
    return($ade_err_ok);
}

sub display_holdings
{
    my($errstack_ref, $dbh, $out_handle, @parameters) = @_;
    my($filter, $row_refs_ref, $row_ref, $body);
  
    #  Process parameters
    if ($#parameters+1 > 1) {
        &ade_err_internal($errstack_ref, "usage: display holdings [ <id-regexp> ]");
    }
    $filter = (defined($parameters[0]) && $parameters[0] ne '') ? $parameters[0] : '.*';
    &ade_err_debug($errstack_ref, 10, "display_holdings: filter=\"$filter\"");
 
    #  Prepare the SQL command needed to get the data we want.
    if ($opt_displaytype eq 'current' && !($row_refs_ref=$dbh->selectall_arrayref("SELECT current_holdings.timestamp AS timestamp, current_holdings.id AS id, current_holdings.quantity AS quantity, current_accounts.currency AS currency, current_accounts.type AS type FROM current_holdings, current_accounts WHERE current_holdings.id == current_accounts.id ORDER BY current_accounts.type, current_holdings.id;", { Slice => {} }))) {
        &ade_err_error($errstack_ref, iofdb_err_database, $dbh->errstr);
        return($ade_err_fail);
    #  Note here we select from latest_accounts, not current_accounts; this is because
    #  we're interested in *all* holding records, and their corresponding currencies;
    #  if the account is closed then current_accounts won't have info on the currency.
    } elsif ($opt_displaytype eq 'history' && !($row_refs_ref=$dbh->selectall_arrayref("SELECT holdings.timestamp AS timestamp, holdings.id AS id, holdings.quantity AS quantity, latest_accounts.currency AS currency, latest_accounts.type AS type FROM holdings, latest_accounts WHERE holdings.id == latest_accounts.id ORDER BY holdings.timestamp;", { Slice => {} }))) {
        &ade_err_error($errstack_ref, iofdb_err_database, $dbh->errstr);
        return($ade_err_fail);
    } 

    #  Report header
    if ($opt_displayformat eq 'human') {
        print $out_handle "Holdings\n" . '=' x 78 . "\n";
    } elsif ($opt_displayformat eq 'png') {
        &print_gnuplot_preamble($errstack_ref, $out_handle, "Holdings ($filter)");
    } else {
        &ade_err_internal($errstack_ref, "display_accounts: not implemented or this should not happen");
    }

    #  Report body
    foreach $row_ref (@{$row_refs_ref}) {
        next if ($row_ref->{id} !~ $filter);
        if ($opt_displayformat eq 'human') {
            $body .= sprintf("$fmtstr_date $fmtstr_id " . (($row_ref->{type} eq 'CURRENCY') ? $fmtstr_quantity_currency : $fmtstr_quantity_listed_stock) . " $fmtstr_currency\n", scalar(localtime($row_ref->{timestamp})), $row_ref->{id}, $row_ref->{quantity}, $row_ref->{currency});
        } elsif ($opt_displayformat eq 'png') {
            $body .= sprintf("%d %f\n", $row_ref->{timestamp}, $row_ref->{quantity});
        } else {
            &ade_err_internal($errstack_ref, "display_accounts: not implemented or this should not happen");
        } 
    }
    print $out_handle $body;
 
    #  Report footer
    if ($opt_displayformat eq 'human') {
        print $out_handle "\n";
    } elsif ($opt_displayformat eq 'png') {
        print $out_handle "e\n";
    } else {
        &ade_err_internal($errstack_ref, "display_accounts: not implemented or this should not happen");
    } 

    return($ade_err_ok);
}

sub destroy_holding
{
    my($errstack_ref, $dbh, @parameters) = @_;

    &ade_err_internal($errstack_ref, "destroy_holding: not implemented");
}

sub update_holdings
{
    my($errstack_ref, $dbh, @parameters) = @_;
    my($id, $type, $quantity, $timestamp);
    my($filter, $row_ref, $row_refs_ref, %types, $rc);
  
    #  Process parameters
    if ($#parameters+1 > 1) {
        &ade_err_internal($errstack_ref, "usage: update holdings [ <account-regexp> ]");
    }
    $filter = (defined($parameters[0]) && $parameters[0] ne '') ? $parameters[0] : '.*';
    &ade_err_debug($errstack_ref, 10, "update_holdings: filter=\"$filter\"");

    #  Get list of all open accounts
    &ade_err_debug($errstack_ref, 10, "update_holdings: getting list of all open accounts ...");
    if (!($row_refs_ref=$dbh->selectall_arrayref("SELECT id, type FROM current_accounts;", { Slice => {} }))) {
        &ade_err_error($errstack_ref, iofdb_err_database, $dbh->errstr);
        return($ade_err_fail);
    }

    #  Filter list according to caller-specified filter, noting the account id
    #  and the account type (the account type will be used to check that, if the
    #  account contains shares, that the user specifies an integer holding).
    &ade_err_debug($errstack_ref, 10, "update_holdings: filtering list of open accounts and noting types ...");
    foreach $row_ref (@{$row_refs_ref}) {
        next if ($row_ref->{id} !~ $filter);
        $types{$row_ref->{id}} = $row_ref->{type};
    }

    #  Note that *this* update_*() function will call create_*(). Those create_*()
    #  functions cannot be allowed to decide a timestamp for themselves because
    #  that would result in several practically-simultaneously-submitted entries
    #  getting different timestamps and this will screw up multiplot graphs. So
    #  we decide the timestamp here and pass it to all create_*() calls.
    $timestamp = time;
    #  For each noted account ...
    foreach $id (sort keys %types) {
        $type = $types{$id};

        #  ... get quantity from user ...
        if (($rc=&get_holding_from_user($errstack_ref, $id, $type, \$quantity)) != $ade_err_ok) {
            return($rc);
        }
        #  ... (accepting user may want to skip this account) ...
        next if ($quantity eq 'skip');

        #  ... and write an entry to the holdings table.
        if (($rc=&create_holding($errstack_ref, $dbh, $timestamp, ($id, $quantity))) != $ade_err_ok) {
            return($rc);
        }
    }

    return($ade_err_ok);
}

##############################################################################
#
#  LEVEL TWO PRICES FUNCTIONS:  create_price/destroy_price/display_prices/update_prices
#
##############################################################################

sub create_price
{
    my($errstack_ref, $dbh, $timestamp, @parameters) = @_;
    my($source_currency, $destination_currency, $price);
    my($rc);
  
    #  Process parameters
    if ($#parameters+1 != 3) {
        &ade_err_internal($errstack_ref, "usage: create price <source-currency> <destination-currency> <price>");
    }
    $source_currency = $parameters[0];
    $destination_currency = $parameters[1];
    $price = $parameters[2];
    &ade_err_debug($errstack_ref, 10, "create_price: source_currency=\"$source_currency\", destination_currency=\"$destination_currency\", price=\"$price\"");
 
    $timestamp = time if (!defined($timestamp));
    if (!$dbh->do("INSERT INTO prices (timestamp, source_currency, destination_currency, price) VALUES (?, ?, ?, ?);", undef, $timestamp, $source_currency, $destination_currency, $price)) {
        &ade_err_error($errstack_ref, iofdb_err_database, $dbh->errstr);
        return($ade_err_fail);
    }
    
    return($ade_err_ok);
}

sub display_prices
{
    my($errstack_ref, $dbh, $out_handle, @parameters) = @_;
    my($filter, $filter2, $row_refs_ref, $row_ref, $body);
  
    #  Process parameters
    if ($#parameters+1 > 2) {
        &ade_err_internal($errstack_ref, "usage: display prices [ <source-currency-regexp> [ <destination-currency-regexp> ] ]");
    }
    $filter = (defined($parameters[0]) && $parameters[0] ne '') ? $parameters[0] : '.*';
    $filter2 = (defined($parameters[1]) ? $parameters[1] : '.*');
    &ade_err_debug($errstack_ref, 10, "display_prices: filter=\"$filter\"");
 
    #  Prepare the SQL command needed to get the data we want.

    #  Note essential differences here:
    #  1) latest results come from latest_prices and are ordered on the currencies because that is nicer for the user
    #  2) history results come from prices and are ordered on the timestamp because that is a REQUIREMENT for history listings
    if ($opt_displaytype eq 'current' && !($row_refs_ref=$dbh->selectall_arrayref("SELECT timestamp, source_currency, destination_currency, price FROM current_prices ORDER BY source_currency, destination_currency;", { Slice => {} }))) {
        &ade_err_error($errstack_ref, iofdb_err_database, $dbh->errstr);
        return($ade_err_fail);
    } elsif ($opt_displaytype eq 'history' && !($row_refs_ref=$dbh->selectall_arrayref("SELECT timestamp, source_currency, destination_currency, price FROM prices ORDER BY timestamp;", { Slice => {} }))) {
        &ade_err_error($errstack_ref, iofdb_err_database, $dbh->errstr);
        return($ade_err_fail);
    } 

    #  Report header
    if ($opt_displayformat eq 'human') {
        print $out_handle "Prices\n" . '=' x 78 . "\n";
    } elsif ($opt_displayformat eq 'png') {
        &print_gnuplot_preamble($errstack_ref, $out_handle, "Prices ($filter, $filter2)");
    } else {
        &ade_err_internal($errstack_ref, "display_accounts: not implemented or this should not happen");
    }

    #  Report body
    foreach $row_ref (@{$row_refs_ref}) {
        next if ($row_ref->{source_currency} !~ $filter || $row_ref->{destination_currency} !~ $filter2);
        &ade_err_warning($errstack_ref, iofdb_err_misc, "$row_ref->{source_currency}/$row_ref->{destination_currency}: latest stored price is old; should you update prices?") if (time - $row_ref->{timestamp} > $old_price_age);
        if ($opt_displayformat eq 'human') {
            $body .= sprintf("$fmtstr_date $fmtstr_currency $fmtstr_currency $fmtstr_price\n", scalar(localtime($row_ref->{timestamp})), $row_ref->{source_currency}, $row_ref->{destination_currency}, $row_ref->{price});
        } elsif ($opt_displayformat eq 'png') {
            $body .= sprintf("%d %f\n", $row_ref->{timestamp}, $row_ref->{price});
        } else {
            &ade_err_internal($errstack_ref, "display_accounts: not implemented or this should not happen");
        } 
    }
    print $out_handle $body;
 
    #  Report footer
    if ($opt_displayformat eq 'human') {
        print $out_handle "\n";
    } elsif ($opt_displayformat eq 'png') {
        print $out_handle "e\n";
    } else {
        &ade_err_internal($errstack_ref, "display_accounts: not implemented or this should not happen");
    } 

    return($ade_err_ok);
}

sub destroy_price
{
    my($errstack_ref, $dbh, @parameters) = @_;

    &ade_err_internal($errstack_ref, "destroy_price: not implemented");
}

sub update_prices
{
    my($errstack_ref, $dbh, @parameters) = @_;
    my($source_currency, $destination_currency, $price, $timestamp);
    my($row_refs_ref, $row_ref, %source_types, $source_type, $rc, $filter);
  
    #  Process parameters
    if ($#parameters+1 > 1) {
        &ade_err_internal($errstack_ref, "usage: update prices [ <currency-regexp> ]");
    }
    $filter = (defined($parameters[0]) && $parameters[0] ne '') ? $parameters[0] : '.*';
    &ade_err_debug($errstack_ref, 10, "update_prices: filter=\"$filter\"");

    #  Get a list of all open accounts
    #  prices yet, because there might be duplicates and we'll want to avoid looking
    #  up a price twice.
    &ade_err_debug($errstack_ref, 10, "update_prices: getting open accounts ...");
    if (!($row_refs_ref=$dbh->selectall_arrayref("SELECT id, type, currency, quoted_currency FROM current_accounts;", { Slice => {} }))) {
        &ade_err_error($errstack_ref, iofdb_err_database, $dbh->errstr);
        return($ade_err_fail);
    }

    #  Filter list according to caller-specified filter, noting the currencies
    #  who prices will be needed.
    &ade_err_debug($errstack_ref, 10, "update_prices: filtering list and noting currencies pairs ...");
    foreach $row_ref( @{$row_refs_ref} ) {
        #  'REGEXP' not implemented in DBD::SQLite, so must do in Perl, not SQL.
        next if ($row_ref->{currency} !~ $filter);
        #  If the account holds the local currency then there is no price to calculate.
        if ($row_ref->{type} eq 'CURRENCY' && $row_ref->{currency} eq $opt_displaycurrency) {
        #  If the account holds another currency then we need to get its price, but this is
        #  easy; Yahoo can do any combination of currencies.
        } elsif ($row_ref->{type} eq 'CURRENCY') {
            $source_types{$row_ref->{currency}}{$opt_displaycurrency} = $row_ref->{type};
        #  In the case of listed stocks, there are two prices to get. The first is
        #  the price of the stock in the currency in which it is quoted. The second
        #  is the price of currency in which the stock is quoted in the local  
        #  currency (but only if those are different).
        } elsif ($row_ref->{type} eq 'LISTED_STOCK' || $row_ref->{type} eq 'UNLISTED_STOCK') {
            $source_types{$row_ref->{currency}}{$row_ref->{quoted_currency}} = $row_ref->{type};
            $source_types{$row_ref->{quoted_currency}}{$opt_displaycurrency} = 'CURRENCY' if ($row_ref->{quoted_currency} ne $opt_displaycurrency);
        }
    }

    #  Note that *this* update_*() function will call create_*(). Those create_*()
    #  functions cannot be allowed to decide a timestamp for themselves because
    #  that would result in several practically-simultaneously-submitted entries
    #  getting different timestamps and this will screw up multiplot graphs. So
    #  we decide the timestamp here and pass it to all create_*() calls.
    $timestamp = time;
    #  For each noted currency pair ...
    foreach $source_currency (keys %source_types) {
        foreach $destination_currency (keys %{$source_types{$source_currency}}) {
            $source_type = $source_types{$source_currency}{$destination_currency};

            #  ... get price from internet ...
            if (($rc=&get_price_from_internet($errstack_ref, $source_type, $source_currency, $destination_currency, \$price)) != $ade_err_ok) {
                return($rc);
            }
        
            #  ... (accepting user may want to skip this currency if it is an unlisted stock) ...
            next if ($price eq 'skip');

            #  ... and write an entry to the prices table.
            if (($rc=&create_price($errstack_ref, $dbh, $timestamp, ($source_currency, $destination_currency, $price))) != $ade_err_ok) {
                return($rc);
            }
        }
    }

    return($ade_err_ok);
}

##############################################################################
#
#  LEVEL TWO PRICES FUNCTIONS:  create_balance/destroy_balance/display_balances/update_balances
#
##############################################################################

sub create_balance
{
    my($errstack_ref, $dbh, $timestamp, @parameters) = @_;
    my($currency, $balance);
    my($rc);
  
    #  Process parameters
    if ($#parameters+1 != 2) {
        &ade_err_internal($errstack_ref, "usage: create price <currency> <balance>");
    }
    $currency = $parameters[0];
    $balance = $parameters[1];
    &ade_err_debug($errstack_ref, 10, "create_balance: currency=\"$currency\", balance=\"$balance\"");
 
    $timestamp = time if (!defined($timestamp));
    if (!$dbh->do("INSERT INTO balances (timestamp, currency, balance) VALUES (?, ?, ?);", undef, $timestamp, $currency, $balance)) {
        &ade_err_error($errstack_ref, iofdb_err_database, $dbh->errstr);
        return($ade_err_fail);
    }
    
    return($ade_err_ok);
}

sub display_balances
{
    my($errstack_ref, $dbh, $out_handle, @parameters) = @_;
    my($row_refs_ref, $row_ref, $body);
  
    #  Process parameters
    if ($#parameters+1 > 0) {
        &ade_err_internal($errstack_ref, "usage: display balances");
    }
 
    #  Prepare the SQL command needed to get the data we want. (Normally filters are regexps,
    #  but here they are not, so we can put them in the select statement and do no filtering
    #  in perl.)
    if ($opt_displaytype eq 'current' && !($row_refs_ref=$dbh->selectall_arrayref("SELECT timestamp, currency, balance FROM current_balances WHERE currency = '$opt_displaycurrency' ORDER BY timestamp;", { Slice => {} }))) {
        &ade_err_error($errstack_ref, iofdb_err_database, $dbh->errstr);
        return($ade_err_fail);
    } elsif ($opt_displaytype eq 'history' && !($row_refs_ref=$dbh->selectall_arrayref("SELECT timestamp, currency, balance FROM balances WHERE currency = '$opt_displaycurrency' ORDER BY timestamp;", { Slice => {} }))) {
        &ade_err_error($errstack_ref, iofdb_err_database, $dbh->errstr);
        return($ade_err_fail);
    } 

    #  Report header
    if ($opt_displayformat eq 'human') {
        print $out_handle "Balances\n" . '=' x 78 . "\n";
    } elsif ($opt_displayformat eq 'png') {
        &print_gnuplot_preamble($errstack_ref, $out_handle, "Balances ($opt_displaycurrency)");
    } else {
        &ade_err_internal($errstack_ref, "display_balances: not implemented or this should not happen");
    }

    #  Report body
    foreach $row_ref (@{$row_refs_ref}) {
        next if ($row_ref->{currency} !~ $opt_displaycurrency);
        if ($opt_displayformat eq 'human') {
            $body .= sprintf("$fmtstr_date $fmtstr_currency $fmtstr_quantity_currency\n", scalar(localtime($row_ref->{timestamp})), $row_ref->{currency}, $row_ref->{balance});
        } elsif ($opt_displayformat eq 'png') {
            $body .= sprintf("%d %f\n", $row_ref->{timestamp}, $row_ref->{balance});
        } else {
            &ade_err_internal($errstack_ref, "not implemented");
        } 
    }
    print $out_handle $body;
 
    #  Report footer
    if ($opt_displayformat eq 'human') {
        print $out_handle "\n";
    } elsif ($opt_displayformat eq 'png') {
        print $out_handle "e\n";
    } else {
        &ade_err_internal($errstack_ref, "display_accounts: not implemented or this should not happen");
    } 

    return($ade_err_ok);
}

sub destroy_balance
{
    my($errstack_ref, $dbh, @parameters) = @_;

    &ade_err_internal($errstack_ref, "destroy_price: not implemented");
}

sub update_balances
{
    my($errstack_ref, $dbh, @parameters) = @_;
    my($balance, $price, $timestamp);
    my($row_refs_ref, $row_ref, $rc);
  
    #  Process parameters
    if ($#parameters+1 > 1) {
        &ade_err_internal($errstack_ref, "usage: update balance");
    }

    #  Prepare the SQL command needed to get the data we want.
    if (!($row_refs_ref=$dbh->selectall_arrayref("SELECT current_holdings.id AS id, current_accounts.currency AS currency, current_holdings.quantity AS quantity FROM current_holdings, current_accounts WHERE current_holdings.id == current_accounts.id ORDER BY current_holdings.id;", { Slice => {} }))) {
        &ade_err_error($errstack_ref, iofdb_err_database, $dbh->errstr);
        return($ade_err_fail);
    } 

    #  Note that *this* update_*() function will call create_*(). Those create_*()
    #  functions cannot be allowed to decide a timestamp for themselves because
    #  that would result in several practically-simultaneously-submitted entries
    #  getting different timestamps and this will screw up multiplot graphs. So
    #  we decide the timestamp here and pass it to all create_*() calls. (Actually
    #  this is kind of pointless for the current update_balances() function which
    #  only updates *one* balance so there is no danger of too-spread-out
    #  timestamps on those balances, but we do it for consistency and future
    #  proofing.)
    $timestamp = time;
    #  Calculate new balance.
    $balance = 0.0;
    foreach $row_ref (@{$row_refs_ref}) {
        &ade_err_debug($errstack_ref, 10, "update_balance: currency=$row_ref->{currency}, quantity=$row_ref->{quantity}");
        if (($rc=&get_price_from_database($errstack_ref, $dbh, $row_ref->{currency}, $opt_displaycurrency, \$price)) != $ade_err_ok) {
            return($rc);
        }
        $balance += $row_ref->{quantity} * $price;
        &ade_err_debug($errstack_ref, 10, sprintf("update_balance: balance=%.2f", $balance));
    }

    if (($rc=&create_balance($errstack_ref, $dbh, $timestamp, ($opt_displaycurrency, $balance))) != $ade_err_ok) {
        return($rc);
    }

    return($ade_err_ok);
}

##############################################################################
#
#  SUPPORT FUNCTIONS
#
##############################################################################

sub check_table_access
{
    my($errstack_ref, $dbh) = @_;
    my($name, $sql, $exists, @tables);
    my($row_refs_ref, $row_ref, $tables_row_ref);
 
    @tables = (
        { name => 'accounts',             sql => "CREATE TABLE accounts (timestamp INTEGER, id CHAR(128), type CHAR(32), currency CHAR(32), quoted_currency CHAR(32), status CHAR(16), PRIMARY KEY (timestamp, id));" },
        { name => 'latest_accounts_keys', sql => "CREATE VIEW latest_accounts_keys AS SELECT max(timestamp) AS timestamp, id FROM accounts GROUP BY id;" },
        { name => 'latest_accounts',      sql => "CREATE VIEW latest_accounts AS SELECT accounts.timestamp AS timestamp, accounts.id AS id, accounts.type AS type, accounts.currency AS currency, accounts.quoted_currency AS quoted_currency, accounts.status AS status FROM latest_accounts_keys, accounts WHERE latest_accounts_keys.id == accounts.id AND latest_accounts_keys.timestamp == accounts.timestamp;" },
        { name => 'current_accounts',     sql => "CREATE VIEW current_accounts AS SELECT accounts.timestamp AS timestamp, accounts.id AS id, accounts.type AS type, accounts.currency AS currency, accounts.quoted_currency AS quoted_currency, accounts.status AS status FROM latest_accounts_keys, accounts WHERE latest_accounts_keys.id == accounts.id AND latest_accounts_keys.timestamp == accounts.timestamp AND accounts.status != 'CLOSED';" },

        { name => 'holdings',             sql => "CREATE TABLE holdings (timestamp INTEGER, id CHAR(128), quantity REAL, PRIMARY KEY (timestamp, id));" },
        { name => 'latest_holdings_keys', sql => "CREATE VIEW latest_holdings_keys AS SELECT max(timestamp) AS timestamp, id FROM holdings GROUP BY id;" },
        { name => 'latest_holdings',      sql => "CREATE VIEW latest_holdings AS SELECT holdings.timestamp AS timestamp, holdings.id AS id, holdings.quantity AS quantity FROM latest_holdings_keys, holdings WHERE latest_holdings_keys.id == holdings.id AND latest_holdings_keys.timestamp == holdings.timestamp;" },
        { name => 'current_holdings',     sql => "CREATE VIEW current_holdings AS SELECT latest_holdings.timestamp AS timestamp, latest_holdings.id AS id, latest_holdings.quantity AS quantity FROM latest_holdings, current_accounts WHERE latest_holdings.id == current_accounts.id;" },
        { name => 'prices',               sql => "CREATE TABLE prices (timestamp INTEGER, source_currency CHAR(32), destination_currency CHAR(32), price REAL, PRIMARY KEY (timestamp, source_currency, destination_currency));" },
        { name => 'latest_prices_keys',   sql => "CREATE VIEW latest_prices_keys AS SELECT max(timestamp) AS timestamp, source_currency, destination_currency FROM prices GROUP BY source_currency, destination_currency;" },
        { name => 'latest_prices',        sql => "CREATE VIEW latest_prices AS SELECT prices.timestamp AS timestamp, prices.source_currency AS source_currency, prices.destination_currency AS destination_currency, prices.price AS price FROM latest_prices_keys, prices WHERE latest_prices_keys.source_currency == prices.source_currency AND latest_prices_keys.destination_currency == prices.destination_currency AND latest_prices_keys.timestamp == prices.timestamp;" },
        { name => 'current_prices',       sql => "CREATE VIEW current_prices AS SELECT prices.timestamp AS timestamp, prices.source_currency AS source_currency, prices.destination_currency AS destination_currency, prices.price AS price FROM latest_prices_keys, prices, (SELECT DISTINCT currency FROM current_accounts UNION SELECT DISTINCT quoted_currency AS currency FROM current_accounts WHERE type != 'CURRENCY') AS unique_used_currencies WHERE latest_prices_keys.source_currency == prices.source_currency AND latest_prices_keys.destination_currency == prices.destination_currency AND latest_prices_keys.timestamp == prices.timestamp AND prices.source_currency = unique_used_currencies.currency;" },
        { name => 'balances',             sql => "CREATE TABLE balances (timestamp INTEGER, currency CHAR(32), balance REAL, PRIMARY KEY (timestamp));" },
        { name => 'latest_balances_keys', sql => "CREATE VIEW latest_balances_keys AS SELECT max(timestamp) AS timestamp, currency FROM balances GROUP BY currency;" },
        { name => 'latest_balances',      sql => "CREATE VIEW latest_balances AS SELECT balances.timestamp AS timestamp, balances.currency AS currency, balances.balance AS balance FROM latest_balances_keys, balances WHERE latest_balances_keys.currency == balances.currency AND latest_balances_keys.timestamp == balances.timestamp;" },
        { name => 'current_balances',     sql => "CREATE VIEW current_balances AS SELECT * from latest_balances;" },
    );

    for $tables_row_ref (@tables) {
        $name = $tables_row_ref->{name};
        $sql  = $tables_row_ref->{sql};
        &ade_err_debug($errstack_ref, 10, "check_table_access: name=$name, sql=\"$sql\"");
        #  Assume the worst.
        $exists = 0;

        #  For CSV
        #$exists = 1 if (-f "$iofdb_state_prefix/$name");

        #  For SQLite
        if (!($row_refs_ref = $dbh->selectall_arrayref("SELECT name FROM sqlite_master WHERE name='$name';", { Slice => {} }))) {
            &ade_err_error($errstack_ref, iofdb_err_database, $dbh->errstr);
            return($ade_err_fail);
        }
        $exists = 1 if ($#{$row_refs_ref}+1 == 1);
        &ade_err_debug($errstack_ref, 10, "check_table_access: exists=$exists");
 
        #  Don't creating already existing tables.
        next if ($exists);

        &ade_err_warning($errstack_ref, iofdb_err_misc, "$name: does not exist; creating ...");
        if (!$dbh->do($sql)) {
            &ade_err_error($errstack_ref, iofdb_err_database, $dbh->errstr);
            return($ade_err_fail);
        }
        &ade_err_debug($errstack_ref, 10, "check_table_access: $name: created");
    }

    return($ade_err_ok);
}
 
sub account2status
{
    my($errstack_ref, $dbh, $id, $status_ref) = @_;
    my($ary_ref);

    #  Note we select from latest_accounts, which includes 'CLOSED' records. This is ok.
    if (!($ary_ref=$dbh->selectcol_arrayref("SELECT status FROM latest_accounts WHERE id == '$id';"))) {
        &ade_err_error($errstack_ref, iofdb_err_database, $dbh->errstr);
        return($ade_err_fail);
    }
    
    if ($#{$ary_ref}+1 > 1) {
        &ade_err_internal($errstack_ref, "account2status: more than one row returned");

    } elsif ($#{$ary_ref}+1 == 1) {
        ${$status_ref} = ${$ary_ref}[0];

    } else {
        ${$status_ref} = 'NONEXISTENT';
    }

    return($ade_err_ok);
}

sub get_holding_from_user
{
    my($errstack_ref, $id, $type, $quantity_ref) = @_;
    my($validate_funcref, $rc);

    if ($type eq 'CURRENCY') {
        $validate_funcref = \&validate_float_or_skip;
    } else {
        $validate_funcref = \&validate_integer_or_skip;
    }

    if (!$opt_interactive) {
        #  To get here is stupid! The user called this program with --no-interactive
        #  but then asked to update holdings, which can only be interactive!
        &ade_err_error($errstack_ref, iofdb_err_misc, "holdings cannot be updated in non-interactive mode");
        return($ade_err_fail);

    } elsif (($rc=&ade_msg_askquestion($errstack_ref, undef, $id, 'skip', $validate_funcref, undef, $quantity_ref)) != $ade_err_ok) {
        return($rc);
    }

    return($ade_err_ok);
}

sub get_price_from_internet
{
    my($errstack_ref, $sourcetype, $source_currency, $destination_currency, $price_ref) = @_;
    my($rc);

    if ($sourcetype eq 'LISTED_STOCK' && ($rc=&get_price_from_internet_of_listed_stock($errstack_ref, $source_currency, $destination_currency, $price_ref)) != $ade_err_ok) {
        return($rc);
    } elsif ($sourcetype eq 'LISTED_STOCK') {
        #  successfully converted
    } elsif ($sourcetype eq 'UNLISTED_STOCK' && ($rc=&get_price_from_user_of_unlisted_stock($errstack_ref, $source_currency, $destination_currency, $price_ref)) != $ade_err_ok) {
        return($rc);
    } elsif ($sourcetype eq 'UNLISTED_STOCK') {
        #  successfully converted
    } elsif ($sourcetype eq 'CURRENCY' && ($rc=&get_price_from_internet_of_currency($errstack_ref, $source_currency, $destination_currency, $price_ref)) != $ade_err_ok) {
        return($rc);
    } elsif ($sourcetype eq 'CURRENCY') {
        #  successfully converted
    } else {
        &ade_err_error($errstack_ref, iofdb_err_misc, "$sourcetype: invalid source type");
        return($ade_err_fail);
    }

    return($ade_err_ok);
}

sub get_price_from_database
{
    my ($errstack_ref, $dbh, $source_currency, $destination_currency, $price_ref) = @_;
    my ($row_refs_ref, $row_ref, $rc);

    &ade_err_debug($errstack_ref, 10, "get_price_from_database: source_currency=$source_currency, destination_currency=$destination_currency");
    #  This block wrapper is just to be able to 'last' out of prematurely once a match is made.
    {
        #  If currencies are same then conversion factor is 1.
        &ade_err_debug($errstack_ref, 10, "get_price_from_database: checking if currencies match ...");
        if ($source_currency eq $destination_currency) {
            &ade_err_debug($errstack_ref, 10, "get_price_from_database: currencies match; returning 1.0 ...");
            ${$price_ref} = 1.0;
            last;
        }
        &ade_err_debug($errstack_ref, 10, "get_price_from_database: currencies do not match");

        #  Try looking up a direct conversion price. (Should we use latest_prices or current prices?)
        &ade_err_debug($errstack_ref, 10, "get_price_from_database: looking for direct conversion price ...");
        if (!($row_refs_ref=$dbh->selectall_arrayref("SELECT price, timestamp FROM latest_prices WHERE source_currency = '$source_currency' AND destination_currency = '$destination_currency';", { Slice => {} }))) {
            &ade_err_error($errstack_ref, iofdb_err_database, $dbh->errstr);
            return($ade_err_fail);
        }
        if (scalar(@{$row_refs_ref}) == 1) {
            &ade_err_debug($errstack_ref, 10, "get_price_from_database: found direct conversion price");
            $row_ref = ${$row_refs_ref}[0];
            &ade_err_warning($errstack_ref, iofdb_err_misc, "$source_currency/$destination_currency: latest stored direct price is old; should you update prices?") if (time - $row_ref->{timestamp} > $old_price_age);
            ${$price_ref} = $row_ref->{price};
            last;
        } 
        &ade_err_debug($errstack_ref, 10, "get_price_from_database: no direct conversion price found");
    
        #  Try looking up a conversion price using one intermediate currency. (Should we use 
        #  latest_prices or current prices?)
        &ade_err_debug($errstack_ref, 10, "get_price_from_database: looking for conversion price with one intermediate ...");
        #  Note we pick the *worst* date from the two selected rows to be the timestamp of this price.
        #  This is 'cos we'll warn if the date is too old and when there are two dates it make sense
        #  to pick the older date.
        if (!($row_refs_ref=$dbh->selectall_arrayref("SELECT latest_prices_1.price * latest_prices_2.price AS price, MIN(latest_prices_1.timestamp, latest_prices_2.timestamp) AS timestamp FROM latest_prices AS latest_prices_1, latest_prices AS latest_prices_2 WHERE latest_prices_1.destination_currency = latest_prices_2.source_currency AND latest_prices_1.source_currency = '$source_currency' AND latest_prices_2.destination_currency = '$destination_currency';", { Slice => {} }))) {
            &ade_err_error($errstack_ref, iofdb_err_database, $dbh->errstr);
            return($ade_err_fail);
        }
        if (scalar(@{$row_refs_ref}) == 1) {
            &ade_err_debug($errstack_ref, 10, "get_price_from_database: found conversion price with one intermediate; returning it ...");
            $row_ref = ${$row_refs_ref}[0];
            &ade_err_warning($errstack_ref, iofdb_err_misc, "$source_currency/$destination_currency: latest stored price with one imtermediary is old; should you update prices?") if (time - $row_ref->{timestamp} > $old_price_age);
            ${$price_ref} = $row_ref->{price};
            last;
        } 
        &ade_err_debug($errstack_ref, 10, "get_price_from_database: no conversion price with one intermediate found");
        ade_err_error($errstack_ref, iofdb_err_misc, "$source_currency-->$destination_currency: failed to get conversion factor");
        return($ade_err_fail);
    }

    return($ade_err_ok);
}

sub get_price_from_user_of_unlisted_stock
{
    my ($errstack_ref, $iofdb_stock_code, $currency, $price_ref) = @_;
    my ($rc);

    if (!$opt_interactive) {
        ${$price_ref} = 'skip';
    } elsif (($rc=&ade_msg_askquestion($errstack_ref, undef, "$iofdb_stock_code (in $currency)", 'skip', \&validate_float_or_skip, undef, $price_ref)) != $ade_err_ok) {
        return($rc);
    }

    return($ade_err_ok);
}

sub get_price_from_internet_of_listed_stock
{
    my ($errstack_ref, $iofdb_stock_code, $currency, $price_ref) = @_;
    my ($country, $code, $stock, $exchange, $quote_handle, %quote_info, $reason, $remaining_retries);
    my ($quote_price);

    ($exchange, $stock) = split(/\//, $iofdb_stock_code, 2);
    if (!defined($stock)) {
        &ade_err_error($errstack_ref, iofdb_err_misc, "$iofdb_stock_code: badly formatted IOFDB stock code");
        return($ade_err_fail);
    #  This list taken from the 'FETCH' section of the Finance::Quote(3) man page.
    } elsif ($exchange =~ /^(?:australia|dwsfunds|fidelity|tiaacref|troweprice|europe|canada|usa|nyse|nasdaq|uk_unit_trusts|vanguard|vwd)$/) {
        ;
    } else {
        &ade_err_error($errstack_ref, iofdb_err_misc, "$exchange: invalid exchange");
        return($ade_err_fail);
    }
        
    for ($remaining_retries=$get_price_from_internet_maxretries; ; $remaining_retries--) {

        #
        #  Attempt to get the price (in a re-entrant way)
        #
        
        undef $quote_handle;
        $quote_handle = Finance::Quote->new; 
        $quote_handle->timeout($get_price_from_internet_timeout); 
        $quote_handle->set_currency($currency); 
        $quote_handle->require_labels(qw/price/);  
        &ade_err_info($errstack_ref, "looking up $stock on $exchange ...");
        &ade_err_debug($errstack_ref, 10, "get_price_from_internet_of_listed_stock: calling fetch() method ...");
        #  Previously we called:
        #      %quote_info = $quote_handle->fetch($exchange,$stock);
        #  but if there is an error (e.g. the stock is unlisted) then this will
        #  cause this script to bomb out immediately with:
        #      Can't call method "rows" on an undefined value at /usr/share/perl5/HTML/TableExtract.pm line 224.
        #  Finance::Quote(3) does not document any way to trap such errors, therefore
        #      we must use the eval trick (see perldoc -f eval).
        eval '%quote_info = $quote_handle->fetch($exchange,$stock);';
        if ($@) {
            &ade_err_error($errstack_ref, iofdb_err_misc, "$exchange/$stock: lookup failed (are exchange and ticker correct?)");
            return($ade_err_fail);
        }
        &ade_err_debug($errstack_ref, 10, "get_price_from_internet_of_listed_stock: extracting price field ...");
        $quote_price = $quote_info{$stock,'price'};

        #
        #  If quote functions report success and price isn't empty/zero/undefined
        #  then break out of retry loop.
        #
        
        last if ($quote_info{$stock,'success'} && $quote_price);
        &ade_err_debug($errstack_ref, 10, "get_price_from_internet_of_listed_stock: price didn't pass tests");

        #
        #  Did we run out of retries?
        #
    
        if (!--$remaining_retries) {
            &ade_err_error($errstack_ref, iofdb_err_misc, sprintf("failed to retrieve price for $iofdb_stock_code in $currency (%s)", ($reason=$quote_info{$stock,"errormsg"}) ? $reason : "no explanation available"));
            return($ade_err_fail);
        } 

        #
        #  Sleep a moment and then loop round for retry.
        #
        
        &ade_err_debug($errstack_ref, 10, "get_price_from_internet_of_listed_stock: pausing before looping ...");
        sleep($get_price_from_internet_retryinterval);
    }

    ${$price_ref} = $quote_price;

    return($ade_err_ok);
}

sub get_price_from_internet_of_currency
{
    my ($errstack_ref, $source_currency, $destination_currency, $price_ref) = @_;
    my ($quote_handle, $quote_price, $remaining_retries);

    &ade_err_info($errstack_ref, "looking up $source_currency:$destination_currency price ...");
    for ($remaining_retries=$get_price_from_internet_maxretries; ; $remaining_retries--) {

        #
        #  Attempt to get the price (in a re-entrant way)
        #
        
        undef $quote_handle;
        $quote_handle = Finance::Quote->new; 
        $quote_handle->timeout($get_price_from_internet_timeout); 
        $quote_price = $quote_handle->currency($source_currency, $destination_currency);

        #
        #  If price isn't empty/zero/undefined then break out of retry loop.
        #

        last if ($quote_price);
        &ade_err_debug($errstack_ref, 10, "get_price_from_internet_of_currency: price didn't pass tests");

        #
        #  Did we run out of retries?
        #
    
        if (!--$remaining_retries) {
            &ade_err_error($errstack_ref, iofdb_err_misc, "failed to retrieve price for $source_currency in $destination_currency (are currencies both valid?)");
            return($ade_err_fail);
        } 

        #
        #  Sleep a moment and then loop round for retry.
        #
        
        &ade_err_debug($errstack_ref, 10, "get_price_from_internet_of_currency: pausing before looping ...");
        sleep($get_price_from_internet_retryinterval);
    }

    ${$price_ref} = $quote_price;
    
    return($ade_err_ok);
}

sub validate_float_or_skip
{
    my($errstack_ref, $value, $validated_ref) = @_;
    my($rc);

    if ($value eq 'skip') {
        ${$validated_ref} = 1;
    } elsif (($rc=&ade_smf_validate_float($errstack_ref, $value, $validated_ref)) != $ade_err_ok) {
        return($rc);
    }

    return($ade_err_ok);
}

sub validate_integer_or_skip
{
    my($errstack_ref, $value, $validated_ref) = @_;
    my($rc);

    if ($value eq 'skip') {
        ${$validated_ref} = 1;
    } elsif (($rc=&ade_smf_validate_integer($errstack_ref, $value, $validated_ref)) != $ade_err_ok) {
        return($rc);
    }

    return($ade_err_ok);
}

sub print_gnuplot_preamble
{
    my($errstack_ref, $out_handle, $title) = @_;
    my($style);

    #  Accounts remaining in last state until changed; i.e. step style.
    if ($title =~ /^account/i) {
        $style = 'steps';
    } else {
        $style = 'lines';
    }
    &ade_err_debug($errstack_ref, 10, "print_gnuplot_preamble: style=$style");

    #print $out_handle "set size 1600/640, 1200/480\n";
    print $out_handle "set term png small\n";
    print $out_handle "set timefmt \"%s\"\n";
    print $out_handle "set xdata time\n";
    #print $out_handle "set xtics 604800\n";
    print $out_handle "set format x \"%d/%m/%Y\"\n";
    print $out_handle "set nomxtics\n";
    print $out_handle "set output \"/tmp/$ade_app_progname.$$.png\"\n";
    print $out_handle "plot '-' using 1:2 title \"$title\" with $style\n";
  
    return($ade_err_ok);
}

&ade_gep_main(\&iofdb);
