#!/usr/bin/perl

# ---------- introductory data ---------- #

# program name: download_tickers_final.pl
# author: Mark Dalius
# purpose: downloads recent data for stocks in database
# input: none
# process: gets ticker and index list from database. cats together into
#          one list. one at a time, downloads data from finwin, parses wddx,
#          and then updates the database with the parsed data. 
# output: stock data from finwin, parsed out of wddx packets, and into database
# usage: ./download_tickers.pl & (should be run as a daemon)
# external files: must have wddx packets available from finwin, data from database
# potential bugs: none
# created: 20030124
# last modified: NA

# ---------- variables ---------- #

# ---------- functions ---------- #

# this function returns a database handle to connect to a db

sub databaseConnect { 
  use DBI;
  my ($DBConnectMethod,$DBName,$HostName,$Username,$Password) = @_; 
  DBI->connect("dbi:$DBConnectMethod:$DBName;host=$HostName", $Username, $Password) || die "couldn't connect to database: \n" . $DBI::errstr;
}

# this function returns a distinct list of stocks from a specified local database and table

sub getLocalList {
  my ($database,$table) = @_;

  # talk to the databaseConnect() function

  $dbh = &databaseConnect("mysql","$database","216.127.156.26:3306","username","password");

  # prepare and run the query

  $query =  "SELECT DISTINCT ticker FROM $database.$table";
  $sth = $dbh->prepare($query) || die "couldn't prepare query: " . $DBI::errstr;
  $sth->execute() || die "couldn't execute query: " . $DBI::errstr;
  $rowcount = $sth->rows;
 
  # extract the results of the query into a list array 

  $ticker_array = ();
  for ($i=0; $i<$rowcount; $i++) {
    $sth->bind_col(1, \$this_ticker);
    $sth->fetch;
    @ticker_array["$i"] = $this_ticker;
  }

  return @ticker_array;

}

# this function gets a WDDX packet from FinWin, then parses it into printable values

sub getAndParsePacket {
 
  my($username,$password,$symbol) = @_;

  #my($symbol) = "AMZN";

  use WDDX;
  use LWP::UserAgent;

  $ua = new LWP::UserAgent;

  my $req = new HTTP::Request POST =>'http://www.finwin.com/processct/processquotetag.cfm';
  my $content_string = "username=" . $username . "&password=" . $password . "&request=quote&id=814&type=array&symbol=" . $symbol;

  $req->content_type('application/x-www-form-urlencoded');
  $req->content($content_string);

  my $res = $ua->request($req);

  if (!$res->is_success) {
    print "Error retrieving content\n";
    # add an error emailing function here MWD
    exit;
  }

  # now that we have the result, assign it to a variable...

  $result = $res->content;

  # and now create a new WDDX object to parse it out

  $parser = new WDDX();
  my $packet = $parser->deserialize($result);

  # now, get the length of the total packet

  my $length = $packet->length;

  # for each row in the packet, go through and extract the values
  # we have to do that to get the values out of the WDDX array and put them
  # into a perl array (since I can't quite get wddxtoperl() to work)

  %return_hash = ();

  for ($i=0; $i<$length; $i++) {

    # extract the array
    $this_value = $packet->get_element($i); 
    
    # the first value in the array is the field name, the second is the field value
    $field_name_encoded = $this_value->get_element(0);
    $field_value_encoded = $this_value->get_element(1);

    # convert them into printable values
    $field_name = $field_name_encoded->as_scalar;
    $field_value = $field_value_encoded->as_scalar;
   
    #print "$field_name --> $field_value\n";
    $return_hash{"$field_name"} = "$field_value";

  }

  @keys = keys(%return_hash);
  foreach $key(@keys) {
    #print "key-value: $key -> $return_hash{$key}\n";
  } 

  return %return_hash;

}

# this function updates the funds database with the newly downloaded data

sub updateLocalDatabase {
  my ($database,$table,%finwin_data) = @_;
  
  @keys = keys(%finwin_data);
  foreach $key(@keys) {
    #print "key-value: $key -> $finwin_data{$key}\n";
  }

  # talk to the databaseConnect() function

  $dbh = &databaseConnect("mysql","$database","216.127.156.26:3306","username","password");

  # prepare and run the update query
  # use the DBI "quote" function to escape all special characters
  # first, determine if the stock is already listed in the database

  $check_query = "SELECT id FROM $database.$table WHERE SYMBOL = " . $dbh->quote($finwin_data{'SYM'});
  
  $sth = $dbh->prepare($check_query) || die "couldn't prepare query: " . $DBI::errstr;
  $sth->execute() || die "couldn't execute query: " . $DBI::errstr;  
  $rowcount = $sth->rows;

  # set some variable names

$symbol = $finwin_data{"SYM"};
$primary_mkt_name = $finwin_data{"EXCHANGE"};
$dividend = $finwin_data{"DIV"};
$per_change = $finwin_data{"PERCCHANGE"};
$last_tr_minute = $finwin_data{"LTTIME"};
$last_tr_hour = $finwin_data{"LTTIME"};
$annhigh = $finwin_data{"TMONTHHIGH"};
$bid_mkt = $finwin_data{"BID"};
$close = $finwin_data{"CLOSE"};
$annlow = $finwin_data{"TMONTHLOW"};
$pe = $finwin_data{"PE"};
$eps = $finwin_data{"EPS"};
$low = $finwin_data{"LOW"};
$change = $finwin_data{"CHANGE"};
$open = $finwin_data{"OPEN"};
$high = $finwin_data{"HIGH"};
$volume = $finwin_data{"VOLUME"};
$last = $finwin_data{"LAST"};
$ask = $finwin_data{"ASK"};
$bid = $finwin_data{"BID"};
$yield = $finwin_data{"DIVYLD"};
$time = $finwin_data{"TIME"};
$description = $finwin_data{"DESC"};
$tmonhighday = $finwin_data{"TMONTHHIGHDAY"};
$tmonhighmon = $finwin_data{"TMONTHHIGHMON"};
$tmonlowday = $finwin_data{"TMONTHLOWDAY"};
$tmonlowmon = $finwin_data{"TMONTHLOWMON"};
$tradestoday = $finwin_data{"TRADESTODAY"};
$sharesout = $finwin_data{"SHARESOUT"};
$eeps = $finwin_data{"EEPS"};
$divyld = $finwin_data{"DIVYLD"};
$divmon = $finwin_data{"DIVMON"};
$divday = $finwin_data{"DIVDAY"};
$divfrq = $finwin_data{"DIVFRQ"};
$news = $finwin_data{"NEWS"};
$titles = $finwin_data{"TITLES"};
$storynumbers = $finwin_data{"STORYNUMBERS"};

  # if it is in the database, update the data

  if ($rowcount > 0) {
 
    # prepare the query so we can minimize the number of database calls

    #my $sth = $dbh->prepare($query);
    my $query = "
    UPDATE $database.$table SET 
    
    SYMBOL=?, PRIMARY_MKT_NAME=?, DIVIDEND=?, PER_CHANGE=?, LAST_TR_MINUTE=?, LAST_TR_HOUR=?,
    ANNHIGH=?, BID_MKT=?, CLOSE=?, ANNLOW=?, PE=?, EPS=?, LOW=?, _CHANGE=?, OPEN=?, HIGH=?,
    VOLUME=?, LAST=?, ASK=?, BID=?, YIELD=?, time=?, DESCRIPTION=?, TMONHIGHDAY=?,
    TMONHIGHMON=?, TMONLOWDAY=?, TMONLOWMON=?, TRADESTODAY=?, SHARESOUT=?,
    EEPS=?, DIVYLD=?, DIVMON=?, DIVDAY=?, DIVFRQ=?, NEWS=?, TITLES=?, STORYNUMBERS=?
    
    WHERE SYMBOL = ? 
    ";

    #print "query: $query\n";

    my $sth = $dbh->prepare($query);
    
    # execute the query


    $sth->execute($symbol,$primary_mkt_name,$dividend,$per_change,$last_tr_minute,$last_tr_hour,$annhigh,$bid_mkt,$close,$annlow,$pe,$eps,$low,$change,$open,$high,$volume,$last,$ask,$bid,$yield,$time,$description,$tmonhighday,$tmonhighmon,$tmonlowday,$tmonlowmon,$tradestoday,$sharesout,$eeps,$divyld,$divmon,$divday,$divfrq,$news,$titles,$storynumbers,$symbol); 

  # if not, insert a new record 

  } else {

    # prepare the query so we can minimize the number of database calls

    my $query = "
    INSERT INTO $database.$table
    (
    SYMBOL, PRIMARY_MKT_NAME, DIVIDEND, PER_CHANGE, LAST_TR_MINUTE, LAST_TR_HOUR,
    ANNHIGH, BID_MKT, CLOSE, ANNLOW, PE, EPS, LOW, _CHANGE, OPEN, HIGH,
    VOLUME, LAST, ASK, BID, YIELD, time, DESCRIPTION, TMONHIGHDAY,
    TMONHIGHMON, TMONLOWDAY, TMONLOWMON, TRADESTODAY, SHARESOUT,
    EEPS, DIVYLD, DIVMON, DIVDAY, DIVFRQ, NEWS, TITLES, STORYNUMBERS
    )
    VALUES
    (
    ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
    ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
    ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
    ?
    )
    ";

    #print "query insert: $query\n";

    my $sth = $dbh->prepare($query);

    # execute the query
    
    $sth->execute($symbol,$primary_mkt_name,$dividend,$per_change,$last_tr_minute,$last_tr_hour,$annhigh,$bid_mkt,$close,$annlow,$pe,$eps,$low,$change,$open,$high,$volume,$last,$ask,$bid,$yield,$time,$description,$tmonhighday,$tmonhighmon,$tmonlowday,$tmonlowmon,$tradestoday,$sharesout,$eeps,$divyld,$divmon,$divday,$divfrq,$news,$titles,$storynumbers); 

  }

}

# ---------- main program body ---------- #

# pass the function a database and a table name to get a ticker list from

@local_tickers = &getLocalList("advisorportfolios","portfolio_data");
@local_indices = &getLocalList("funds","indices"); # NOTE table should change to "indices" after DB change
@local_all = (@local_tickers, @local_indices);

foreach $ticker (@local_all) {
  %remote_values = &getAndParsePacket("USERNAME","PASSWORD","$ticker");
  &updateLocalDatabase("funds","data_stocks",%remote_values);
}

print "final count: $tickercount\n";

# ---------- EOF ---------- #