view release on metacpan or  search on metacpan

lib/App/idxdb.pm  view on Meta::CPAN

            tags => ['category:action'],
        },
        %argopt_graph,
    },
    examples => [
        {
            summary => 'Show legends instead (e.g. ForeignIB = foreign bank, etc)',
            args => {legend=>1},
            test => 0,
        },
    ],
};
sub ownership {
    my %args = @_;
    my $stock = $args{stock};
    my $fields = $args{fields};

    my $state = _init(\%args, 'ro');
    my $dbh = $state->{dbh};

    if ($args{legend}) {
        return [200, "OK", \%ownership_fields];
    }

    my @wheres;
    my @binds;
    push @wheres, "Code=?";
    push @binds, $stock;
    if ($args{date_start}) {
        push @wheres, "date >= '".$args{date_start}->ymd."'";
    }
    if ($args{date_end}) {
        push @wheres, "date <= '".$args{date_end}->ymd."'";
    }

    my $sth = $dbh->prepare("SELECT * FROM stock_ownership WHERE ".join(" AND ", @wheres)." ORDER BY date");
    $sth->execute(@binds);
    my @rows;

    while (my $row = $sth->fetchrow_hashref) {
        delete $row->{Code};
        delete $row->{Price};
        delete $row->{ctime};
        delete $row->{mtime};
        my $total = $row->{LocalTotal} + $row->{ForeignTotal};
        for (@ownership_fields) {
            $row->{$_} = sprintf(
                ($args{graph} ? "%.f":"%5.2f%%"), $row->{$_}/$total*100);
        }
        for my $f (@ownership_fields) { delete $row->{$f} unless (grep {$_ eq $f} @$fields) }
        push @rows, $row;
    }

    if ($args{graph}) {
        require Chart::Gnuplot;
        require Color::RGB::Util;
        require ColorTheme::Distinct::WhiteBG;
        require File::Temp;

        my ($tempfh, $tempfilename) = File::Temp::tempfile();
        $tempfilename .= ".png";

        my $theme = ColorTheme::Distinct::WhiteBG->new;
        my @colors = map { '#'.$theme->get_item_color($_) } ($theme->list_items);

        my $chart = Chart::Gnuplot->new(
            output   => $tempfilename,
            title    => "$stock ownership from ".$args{date_start}->ymd." to ".$args{date_end}->ymd,
            xlabel   => 'date',
            ylabel   => "\%",
            timeaxis => 'x',
            xtics    => {labelfmt=>'%Y-%m-%d', rotate=>"30 right"},
            #yrange   => [0, 100],
        );
        my $i = -1;
        my @datasets;
        for my $field (@$fields) {
            $i++;
            push @datasets, Chart::Gnuplot::DataSet->new(
                xdata   => [map { $_->{date} } @rows],
                ydata   => [map { $_->{$field} } @rows],
                timefmt => '%Y-%m-%d',
                title   => $field,
                color   => $colors[$i],
                style   => 'lines',
            );
        }
        $chart->plot2d(@datasets);

        require Browser::Open;
        Browser::Open::open_browser("file:$tempfilename");

        return [200];
    }

    [200, "OK", \@rows, {'table.fields'=>['date']}];
}

$SPEC{daily} = {
    v => 1.1,
    summary => 'Show data from daily stock/trading summary',
    args => {
        %arg0_stocks,
        %argsopt_filter_date,
        %argopt_fields_daily,
        total => {
            schema => 'bool*',
        },
        %argopt_graph,
    },
};
sub daily {
    my %args = @_;
    my $stocks = $args{stocks};
    my $fields = $args{fields};

    my $state = _init(\%args, 'ro');
    my $dbh = $state->{dbh};

    my @wheres;
    my @binds;
    push @wheres, "StockCode IN (".join(",", map {$dbh->quote($_)} @$stocks).")";
    if ($args{date_start}) {
        push @wheres, "date >= '".$args{date_start}->ymd."'";
    }
    if ($args{date_end}) {
        push @wheres, "date <= '".$args{date_end}->ymd."'";
    }

    my $sth = $dbh->prepare("SELECT * FROM daily_trading_summary WHERE ".join(" AND ", @wheres)." ORDER BY date,StockCode");
    $sth->execute(@binds);
    my %stock_rows;   # key=stock code, value[row, ...]
    my %stock_totals; # key=stock code, value={ field=>TOTAL, ... }

    my ($mindate, $maxdate);
    while (my $row = $sth->fetchrow_hashref) {
        my $code = $row->{StockCode};
        $mindate //= $row->{Date};
        $maxdate   = $row->{Date};

        $stock_rows{$code} //= [];

        # calculated fields
        $row->{ForeignNetBuy}      = $row->{ForeignBuy} - $row->{ForeignSell};
        $row->{AccumForeignBuy}    = (@{ $stock_rows{$code} } ? $stock_rows{$code}[-1]{AccumForeignBuy}    : 0) + $row->{ForeignBuy}     if grep {$_ eq 'AccumForeignBuy'} @$fields;
        $row->{AccumForeignSell}   = (@{ $stock_rows{$code} } ? $stock_rows{$code}[-1]{AccumForeignSell}   : 0) + $row->{ForeignSell}    if grep {$_ eq 'AccumForeignSell'} @$fields;
        $row->{AccumForeignNetBuy} = (@{ $stock_rows{$code} } ? $stock_rows{$code}[-1]{AccumForeignNetBuy} : 0) + $row->{ForeignNetBuy}  if grep {$_ eq 'AccumForeignNetBuy'} @$fields;

        # calculate total
        if ($args{total}) {
            for my $f (@daily_fields) {
                my $spec = $daily_fields{$f};
                next unless $spec->{type} =~ /^(volume|money|freq)$/;
                $stock_totals{$code}{$f} += $row->{$f}  if defined $row->{$f};
            }
        }

        delete $row->{StockCode};
        delete $row->{persen};
        delete $row->{percentage};
        delete $row->{ctime};
        delete $row->{mtime};
        for my $f (@daily_fields) { delete $row->{$f} unless (grep {$_ eq $f} @$fields) }
        push @{ $stock_rows{$code} }, $row;
    }

    if ($args{graph}) {
        require Chart::Gnuplot;
        require Color::RGB::Util;
        require ColorTheme::Distinct::WhiteBG;
        require File::Temp;

        my ($tempfh, $tempfilename) = File::Temp::tempfile();
        $tempfilename .= ".png";

        my $theme = ColorTheme::Distinct::WhiteBG->new;
        my @colors = map { '#'.$theme->get_item_color($_) } ($theme->list_items);

        my $chart = Chart::Gnuplot->new(
            output   => $tempfilename,
            title    => join(",", @$fields)." of ".join(",",@$stocks)." from $mindate to $maxdate",
            xlabel   => 'date',
            ylabel   => $fields->[0],
            (@$fields > 1 ? (y2label  =>
                                 $fields->[1] .
                                 (@$fields > 2 ? ", $fields->[2]" : "") .
                                 (@$fields > 3 ? ", ...":"")) : ()),
            timeaxis => 'x',
            xtics    => {labelfmt=>'%Y-%m-%d', rotate=>"30 right"},
            #yrange   => [0, 5000],
            #y2range  => [-0, 1000_000_000],
            ytics    => {mirror=>'off'}, # no effect?
            y2tics   => {mirror=>'off'}, # no effect?
        );
        my $i = -1;
        my @datasets;
      STOCK:
        for my $stock (@$stocks) {
          FIELD:
            for my $field (@$fields) {
                $i++;
                push @datasets, Chart::Gnuplot::DataSet->new(
                    xdata   => [map { $_->{Date} }   @{ $stock_rows{$stock} }],
                    ydata   => [map { $_->{$field} } @{ $stock_rows{$stock} }],
                    timefmt => '%Y-%m-%d',
                    title   => "$stock.$field",
                    color   => $colors[$i],
                    style   => 'lines',
                    ($i ? (axes => "x1y2") : ()),
                );
            }
        }
        $chart->plot2d(@datasets);

        require Browser::Open;
        Browser::Open::open_browser("file:$tempfilename");

        return [200];
    }

    for my $stock (@$stocks) {
        if ($args{total} && @{ $stock_rows{$stock}[$_] }) {
            for my $f (keys %{ $stock_totals{$stock} }) {
                delete $stock_totals{$stock}{$f} unless (grep {$_ eq $f} @$fields);
            }
            $stock_totals{$stock}{Date} = 'TOTAL';
            push @{ $stock_rows{$stock} }, $stock_totals{$stock};
        }
    }

    my $rows;
    my (@ff, @ffa, @fffmt);

    if (@$stocks > 1) {