gistfile1.txt

get_covar_all.pl:    ($count, $mean_f1,$std_f1, $mean_f2, $std_f2) = ExecStockSQL("ROW",$sql);
get_covar_all.pl:      ($covar{$s1}{$s2}) = ExecStockSQL("ROW",$sql);
get_covar.pl:    ($count, $mean_f1,$std_f1, $mean_f2, $std_f2) = ExecStockSQL("ROW",$sql);
get_covar.pl:      ($covar{$s1}{$s2}) = ExecStockSQL("ROW",$sql);
get_data_for_predict.pl:my $data = ExecStockSQL("TEXT",$sql);
get_data_for_strat.pl:my $data = ExecStockSQL("TEXT",$sql);
get_data.pl:my $data = ExecStockSQL("TEXT",$sql);
get_info_beta.pl:    $beta = ExecStockSQL("ROW",$sql2);
get_info_cvb.pl:  ($n,$mean,$std,$min,$max) = ExecStockSQL("ROW",$sql);
get_info_cvb.pl:  #$beta = ExecStockSQL("ROW",$sql2);
get_info.pl:  ($n,$mean,$std,$min,$max) = ExecStockSQL("ROW",$sql);
get_random_symbol.pl:print ExecStockSQL("TEXT",$sql);
get_symbols.pl:print ExecStockSQL("TEXT",
header.pl:	eval {@col=ExecSQL($dbuser,$dbpasswd, "select count(*) from stockuser where email='$user' and password='$password'",undef);};
header.pl:# @list=ExecSQL($user, $password, $querystring, $type, @fill);
header.pl:# Executes a SQL statement.  If $type is "ROW", returns first row in list
header.pl:# ExecSQL executes "die" on failure.
header.pl:sub ExecSQL {
login_register.pl:    eval { ExecSQL($dbuser,$dbpasswd,"insert into stockuser (email, password) values (?, ?)", undef, $user, $pass);};
login_register.pl:    #eval { ExecSQL($dbuser,$dbpasswd,"insert into transaction (symbol, price, quantity, type, cashholding, email) values (?, ?, ?, ?, ?, ?)", undef, "cash", "0", "0", "cash", $cash, $user);};
manage_portfolios.pl:  eval {@row=ExecSQL($dbuser, $dbpasswd, "select sys_guid() from dual", undef)};
manage_portfolios.pl:  eval {@row=ExecSQL($dbuser, $dbpasswd, "insert into portfolio (id, email) values ('$portfolio_id', '$login')", undef)};
manage_portfolios.pl:  eval {@row=ExecSQL($dbuser, $dbpasswd, "insert into transaction (symbol, price, quantity, type, cashholding, email, portfolio_id)
plot_stock.pl:my @rows = ExecStockSQL("2D","select timestamp, close from ".GetStockPrefix()."StocksDaily where symbol=rpad(?,16)",$symbol);
README:    print ExecStockSQL("TEXT", "select * from ...");
register.pl:	eval { ExecSQL($dbuser,$dbpasswd,"insert into stockuser (email, password) values (?, ?)", undef, $user, $pass);};
register.pl:  #eval { ExecSQL($dbuser,$dbpasswd,"insert into transaction (symbol, price, quantity, type, cashholding, email) values (?, ?, ?, ?, ?, ?)", undef, "cash", "0", "0", "cash", $cash, $user);};
stock_data_access.pm:@EXPORT=qw(ExecStockSQL GetStockPrefix);
stock_data_access.pm:# @list or $string = ExecStockSQL($type, $querystring @fill);
stock_data_access.pm:# Executes a SQL statement given in $querystring
stock_data_access.pm:# ExecStockSQL executes "die" on failure.
stock_data_access.pm:sub ExecStockSQL {
stock_data_access.pm:    return ExecStockCQL(@_);
stock_data_access.pm:# @list = ExecStockCQL($type, $querystring);
stock_data_access.pm:# Executes a CQL statement given in $querystring
stock_data_access.pm:# ExecStockCQL executes "die" on failure.
stock_data_access.pm:sub ExecStockCQL {

$ret = sql_jon_version("select cashholding from (select * from transaction where email='$login' and portfolio_id='$portfolio_id' order by ts DESC) where rownum<=1");
$ret = sql_jon_version("select sum(close_amount) from most_recent_stockinfo natural join hasstock where portfolio_id = '$portfolio_id'");
$ret = sql_jon_version("select symbol, amount from stocks natural join hasstock where portfolio_id='$portfolio_id'");
$ret = sql_jon_version("select cashholding from (select * from transaction where email='$login' and portfolio_id='$portfolio_id' order by ts DESC) where rownum<=1");
$ret = sql_jon_version("insert into transaction (symbol, price, quantity, type, cashholding, email, portfolio_id) values('cash', 0, $amount, 'Withdraw', $new_holdings, '$login', '$portfolio_id')");
$ret = sql_jon_version("insert into transaction (symbol, price, quantity, type, cashholding, email, portfolio_id) values('cash', 0, $amount, 'Deposit', $new_holdings, '$login', '$portfolio_id')");
$ret = sql_jon_version("select symbol, close from most_recent_stockinfo where close is not null order by symbol");
$ret = sql_jon_version("select count(_) from cs339.stockssymbols where symbol=rpad('$symbol',16)");
$ret = sql_jon_version("select close from (select * from stocksdaily where symbol='$symbol' order by ts desc) where rownum <=1");
$ret = sql_jon_version("select cashholding from (select * from transaction where email='$login' and portfolio_id='$portfolio_id' order by ts DESC) where rownum<=1");
$ret = sql_jon_version("select amount from hasstock where symbol = '$symbol' and portfolio_id='$portfolio_id'");
$ret = sql_jon_version("insert into transaction (symbol, price, quantity, type, cashholding, email, portfolio_id) values('$symbol', $price, $amount, 'buy', $new_holdings, '$login', '$portfolio_id')");
sql_jon_version("insert into hasstock (portfolio_id, symbol, amount) values ('$portfolio_id', '$symbol', $new_amount)");
sql_jon_version("update hasstock set amount=$new_amount where portfolio_id='$portfolio_id' and symbol='$symbol'");
sql_jon_version("insert into transaction (symbol, price, quantity, type, cashholding, email, portfolio_id) values('$symbol', $price, $amount, 'sell', $new_holdings, '$login', '$portfolio_id')");
sql_jon_version("update hasstock set amount=$new_amount where portfolio_id='$portfolio_id' and symbol='$symbol'");
sql_jon_version("delete from hasstock where portfolio_id='$portfolio_id' and symbol='$symbol'");

Get CashHoldings in a portfolio:
"select cashholding from (select * from transaction where email='$login' and portfolio_id='$portfolio_id' order by ts DESC) where rownum<=1"

Portfolio Value:
"select sum(close*amount) from most_recent_stockinfo natural join hasstock where portfolio_id = '$portfolio_id'"

All stocks a user owns and the number of shares:
"select symbol, amount from stocks natural join hasstock where portfolio_id='$portfolio_id'"

Withdraw money from an account
insert into transaction (symbol, price, quantity, type, cashholding, email, portfolio_id) values('cash', 0, $amount, 'Withdraw', $new_holdings, '$login', '$portfolio_id')"

Deposit money to an account
"insert into transaction (symbol, price, quantity, type, cashholding, email, portfolio_id) values('cash', 0, $amount, 'Deposit', $new_holdings, '$login', '$portfolio_id')"

Buy a stock
"insert into transaction (symbol, price, quantity, type, cashholding, email, portfolio_id) values('$symbol', $price, $amount, 'buy', $new_holdings, '$login', '$portfolio_id')"

Sell a stock
insert into transaction (symbol, price, quantity, type, cashholding, email, portfolio_id) values('$symbol', $price, $amount, 'sell', $new_holdings, '$login', '$portfolio_id')"

Check if a user is valid

select count(*) from stockuser where email='$user' and password='$password'",undef