Get CVS from remote host then save to a MySQL database. Updated with insert to map table.

OCVS2MYSQL_V2.pl

use strict;
use warnings;
use DBI;

use Time::Local;
use LWP::Simple;

sub trim {
    my $s = shift;
    $s =~ s/^"|"$//g;
    $s =~ s/^\s+|\s+$//g;
    return $s;
}

sub cna {
    my $s = shift;
    return (trim($s) eq 'N/A') ? 0 : trim($s);
}

my $dsn = 'dbi:mysql:dbname=khfinance;host=localhost;port=3306';
my $dbh = DBI->connect($dsn, 'root', 'toor', {AutoCommit=>1, RaiseError=>1, PrintError=>0});


my $sth = $dbh->prepare('SELECT * FROM khf_symbols');
$sth->execute();

while (my $ref = $sth->fetchrow_hashref()) {
    my $symbol = $ref->{'c_symbol'};
    my $file = "http://download.finance.yahoo.com/d/quotes.csv?s=$symbol&f=sl1d1t1c1ohgv&e=.csv";
    print "Found SS: $file \n";
    
    my $content = get($file);

    print $content;
    my @fields = split(/,/, $content);

    my ($tm, $td, $ty) = (trim($fields[2]) eq 'N/A') ? (5, 1, 2014) : split(/[\/]+/, $fields[2]);
    my $tdate = "$ty-$tm-$td";

    $dbh->do('INSERT INTO khf_archived_quotes (s_id, s_opening_price, s_closing_price, s_highest_price, s_lowest_price, s_volume_share, s_transact_date) VALUES (0, ?, ?, ?, ?, ?, ?)',
        undef,
        cna($fields[5]), # Open
        cna($fields[1]), # Close
        cna($fields[6]), # High
        cna($fields[7]), # Low
        cna($fields[8]), # Volume
        trim($tdate)
    );

    my $h = $dbh->prepare('SELECT LAST_INSERT_ID()');
    $h->execute();

    my ($var) = $h->fetchrow_array();

    $dbh->do('INSERT INTO khf_map_symbol_quotes (c_id, s_id) VALUES (?, ?)',
        undef,
        trim($ref->{'c_id'}),
        trim($var),
    );

    $h->finish();
}

$sth->finish();
$dbh->disconnect();