#!/usr/bin/perl
#@ _COMMENT_
#Created by GA on 2017-09-17
#Script that does the following:
#    1. Shell script that first requires the user to enter the current date.
#    2. Invoke DBI module to interact with MySQL database. Creates a database handle assigned to $dbh, and $sth a query handle. 
#    3. To populate the data record identification key a query is issued to get the highest data record and assignes that number to $row variable
#    4. To prepare for first record the record count is incremented. 
#    5. The query handle is closed to prepare for next query.
#    6. Second query issued reads in all the active equities from the list table.
#    7. Foreach loop goes through each equity in the list, fetches data for a symbol, does some basic processing, then writes to the portfolio database.  
use strict;
use Finance::YahooQuote;
$Finance::YahooQuote::TIMEOUT = 60;
useExtendedQueryFormat(); # switch to extended-time query format
use DBI;
#@ _USE_

#@ _VARDECL_
my $dsn = "DBI:mysql:test";	#data source name
my $user = "ga";		# user name
my $password = "vankix4";	# password
my($query); #Declare query string variable
my (@quote); #Declare array variable for quote 
my ($recid, $symbol,$stkPrice,$keyNum,$curdate,$eps,$pe,$ps,$pb,$ds,$div_yield,$volume,$cap); #Declare varilables for database queries
my ($mag); #Declare manipulation variables to convert cap into a number
my ($row, $record, $result); #declarations for tracking equities
#@ _VARDECL_

#ask user for current date
print stdout "Enter current date use (20XX-month-day, ISO format)";
chomp($curdate = <STDIN>);

#@ _CONNECT_
# connect to database
my $dbh = DBI->connect ($dsn, $user, $password) 
	or die $DBI::errstr;
#@ _CONNECT_

#@ _ISSUE_QUERY_To find the Record key
my $query = qq{SELECT MAX(recid) FROM portfolio }; #issue query
my $sth = $dbh->prepare ($query);
$sth->execute ();
#@ _ISSUE_QUERY_

my $row = $sth->fetchrow_array();

# Increment the record count by one
if(defined $row){ #Use an if else loop to test if row variable is defined. if it is then increment variable, else 1
	$keyNum = $row + 1; #if data exists in database then increment
}
else {
	$keyNum = 1; #If there are no records in database assign number 1
}

$sth->finish ();
#@ _End FETCH_LOOP_

#@ _FETCH_Symbols from list data base_
# read results of query
my $query = qq{ Select tckr 
FROM list
WHERE mrkt like 'NYSE' and active like 'yes' or mrkt like 'NM' and active like 'yes'
ORDER BY tckr asc};
my $sth = $dbh->prepare ($query);
$sth->execute ();
my $result = $sth->fetchall_arrayref();
$sth->finish ();

#iterate over record set
foreach $record (@{$result})
{
#	assign fields to variables
	$symbol = $record->[0];
	print $symbol;
	@quote = getonequote $symbol; #retrieve stock quote data 
	#Now that you got the financial data just read it into the database variables 
	$stkPrice= $quote[2];
	$eps=$quote[15];
	if ($eps == " ") { $eps =0.0; }
	$pe= $stkPrice / ($eps +.00001);
	if ($pe == " ") { $pe =0.0; }
	if ($pe < 0) { $pe =0.0; }
	$ps=$quote[32];
	if ($ps == " ") { $ps =0.0; }
	$pb =$quote[31];
	if ($pb == " ") { $pb =0.0; }
	$ds=$quote[18];
	if ($ds == " ") { $ds =0.0; }
	$div_yield=$quote[19];
	if ($div_yield == " ") { $div_yield =0.0; }
	$volume=$quote[7];
        $cap=$quote[20];
             $mag = substr($cap,-1); #Use substring command to extract the last character, which we will use to tell us whether cap is millions or billions.
             if ($mag eq "B") {$cap = $cap * 1e9; }
             elsif($mag eq "M") {$cap = $cap * 1e6; }  #Turn the cap variable to a proper number. 
	#now read equity data into the profile database
	my $query = qq{ INSERT INTO portfolio values($keyNum,'$symbol','$curdate',$stkPrice,$eps,$pe,$ps,$pb,$ds,$div_yield,$volume,'$cap') };  
	my $sth = $dbh->prepare($query);
	$sth->execute;
	#prepare() is invoked using the database handle; it passes the SQL statement to the driver for preprocessing
	#execute() causes the statement to be performed
	$sth->finish (); #the call to finish frees up any temporary resources allocated to $sth
	$keyNum = $keyNum +1; #increment primary key
}

#@ _TERMINATE_
$dbh->disconnect ();

exit (0);
#@ _TERMINATE Program_