diff options
Diffstat (limited to 'src')
3 files changed, 128 insertions, 15 deletions
diff --git a/src/main/java/edu/brown/cs/student/term/DatabaseQuerier.java b/src/main/java/edu/brown/cs/student/term/DatabaseQuerier.java index 688270f..6900a19 100644 --- a/src/main/java/edu/brown/cs/student/term/DatabaseQuerier.java +++ b/src/main/java/edu/brown/cs/student/term/DatabaseQuerier.java @@ -1,24 +1,29 @@ package edu.brown.cs.student.term; + import edu.brown.cs.student.term.hub.Holder; +import edu.brown.cs.student.term.profit.ProfitCalculation; import edu.brown.cs.student.term.trade.Trade; import java.sql.*; import java.time.Instant; import java.util.ArrayList; +import java.util.LinkedList; import java.util.List; public class DatabaseQuerier { private static Connection conn = null; //TODO: Be prepared to overhaul this to account for IDs + /** * Makes a database querier for a particular sqlite database + * * @param filename - String representing filepath of database * @throws SQLException * @throws ClassNotFoundException */ public DatabaseQuerier(String filename) throws SQLException, - ClassNotFoundException { + ClassNotFoundException { Class.forName("org.sqlite.JDBC"); String urlToDB = "jdbc:sqlite:" + filename; // AutoClosable TRY-WITH-RESOURCES ensures database connection will be closed when it is done @@ -31,8 +36,9 @@ public class DatabaseQuerier { /** * Gets the names of all stocks traded between start and end ddate + * * @param startDate - the start date - * @param endDate - the end date + * @param endDate - the end date * @return a list of stock names * @throws SQLException */ @@ -40,13 +46,13 @@ public class DatabaseQuerier { List<String> stocks = new ArrayList<>(); PreparedStatement prep = conn.prepareStatement( - "SELECT DISTINCT stock_name FROM trades WHERE trade_timestamp <= ? AND trade_timestamp >= ?"); + "SELECT DISTINCT stock_name FROM trades WHERE trade_timestamp <= ? AND trade_timestamp >= ?"); prep.setLong(1, endDate.toEpochMilli()); prep.setLong(2, startDate.toEpochMilli()); ResultSet rs = prep.executeQuery(); - while(rs.next()){ + while (rs.next()) { stocks.add(rs.getString(1)); } @@ -57,16 +63,18 @@ public class DatabaseQuerier { /** * Gets all the trades in by stock and buy type, ordered by time + * * @param startDate - the start date of these trades - * @param endDate - the end date of these trades + * @param endDate - the end date of these trades * @return a list of list of trades as specified above * @throws SQLException - if something goes wrong with connection */ - public List<List<Trade>> getAllTradesByStock(Instant startDate, Instant endDate) throws SQLException { + public List<List<Trade>> getAllTradesByStock(Instant startDate, Instant endDate) + throws SQLException { List<List<Trade>> allTrades = new ArrayList<>(); List<String> stocks = getRecentStocks(startDate, endDate); //get the buys and sells for each stock - for(String stock: stocks){ + for (String stock : stocks) { allTrades.add(getTradeByStock(stock, 1, startDate, endDate)); allTrades.add(getTradeByStock(stock, 0, startDate, endDate)); } @@ -75,19 +83,21 @@ public class DatabaseQuerier { /** * Gets a single stock's list of trades for that time period (either buy or sell) - * @param stock - string name of the stock to get the trades for - * @param isBuy - integer whether it's a buy or sell + * + * @param stock - string name of the stock to get the trades for + * @param isBuy - integer whether it's a buy or sell * @param startDate - an Instant representing the start of the time period - * @param endDate - an Instant representing the end of the time period + * @param endDate - an Instant representing the end of the time period * @return - a list of trades for that stock * @throws SQLException - if issue getting connection */ - public List<Trade> getTradeByStock(String stock, int isBuy, Instant startDate, Instant endDate) throws SQLException{ + public List<Trade> getTradeByStock(String stock, int isBuy, Instant startDate, Instant endDate) + throws SQLException { List<Trade> trades = new ArrayList<>(); PreparedStatement prep = conn.prepareStatement( - "SELECT * FROM trades WHERE (stock_name = ? AND is_buy = ?) " - + "AND (trade_timestamp <= ? AND trade_timestamp >= ?) ORDER BY trade_timestamp"); + "SELECT * FROM trades WHERE (stock_name = ? AND is_buy = ?) " + + "AND (trade_timestamp <= ? AND trade_timestamp >= ?) ORDER BY trade_timestamp"); prep.setString(1, stock); prep.setInt(2, isBuy); @@ -95,7 +105,7 @@ public class DatabaseQuerier { prep.setLong(4, startDate.toEpochMilli()); ResultSet rs = prep.executeQuery(); - while(rs.next()){ + while (rs.next()) { trades.add(new Trade(rs.getInt(1), rs.getString(2), rs.getLong(4), rs.getInt(5), rs.getInt(6), new Holder(rs.getInt(7), rs.getString(3)), @@ -107,4 +117,34 @@ public class DatabaseQuerier { return trades; } + + public List<Trade> getAllTradesByHolder(String person, Date startDate, Date endDate) { + LinkedList<Trade> trades = new LinkedList<>(); + + try { + PreparedStatement prep; + prep = + conn.prepareStatement("SELECT * FROM \'trades\' WHERE holder_name= ? " + + " AND trade_timestamp BETWEEN ? AND ?" + + "order by trade_timestamp asc;"); + prep.setString(1, person); + prep.setDate(2, startDate); + prep.setDate(3, endDate); + ResultSet rs = prep.executeQuery(); + + while (rs.next()) { + trades.addFirst(new Trade(rs.getInt("trade_id"), + rs.getString("stock_name"), + rs.getDouble("trade_timestamp"), + rs.getInt("is_buy"), + rs.getInt("number_of_shares"), + new Holder(rs.getInt("holder_id"), rs.getString("holder_name")), + rs.getDouble("share_price"))); + } + prep.close(); + } catch (SQLException e) { + System.out.println("ERROR: sql error getting trades"); + } + return trades; + } } diff --git a/src/main/java/edu/brown/cs/student/term/Main.java b/src/main/java/edu/brown/cs/student/term/Main.java index 3b0a258..4238f67 100644 --- a/src/main/java/edu/brown/cs/student/term/Main.java +++ b/src/main/java/edu/brown/cs/student/term/Main.java @@ -9,6 +9,7 @@ import edu.brown.cs.student.term.repl.Command; import edu.brown.cs.student.term.repl.REPL; import edu.brown.cs.student.term.repl.commands.LoadCommand; import edu.brown.cs.student.term.repl.commands.SetupCommand; +import edu.brown.cs.student.term.trade.Trade; import joptsimple.OptionParser; import joptsimple.OptionSet; @@ -121,6 +122,7 @@ public final class Main { Spark.before((request, response) -> response.header("Access-Control-Allow-Origin", "*")); Spark.post("/data", new SuspicionRankHandler()); Spark.post("/profit", new ProfitQueryHandler()); + Spark.post("/trade-lookup", new TradeQueryHandler()); } /** @@ -169,7 +171,6 @@ public final class Main { double sp500PercentGain = 0.0; double sp500Gain = 0.0; try { - DatabaseQuerier db = new DatabaseQuerier("data/trades.sqlite3"); profit = new ProfitCalculation(DatabaseQuerier.getConn(), person, startPeriod, endPeriod); holdings = profit.getHoldingsList(); @@ -194,6 +195,22 @@ public final class Main { } + private static class TradeQueryHandler implements Route { + @Override + public Object handle(Request request, Response response) throws Exception { + JSONObject req = new JSONObject(request.body()); + String person = req.getString("person"); + Date startPeriod = new Date(req.getLong("startTime")); + Date endPeriod = new Date(req.getLong("endTime")); + + DatabaseQuerier db = SetupCommand.getDq(); + List<Trade> trades = db.getAllTradesByHolder(person, startPeriod, endPeriod); + + return GSON.toJson(trades); + + } + } + /** * Display an error page when an exception occurs in the server. */ diff --git a/src/test/java/edu/brown/cs/student/ProfitCalculationTest.java b/src/test/java/edu/brown/cs/student/ProfitCalculationTest.java new file mode 100644 index 0000000..99500d0 --- /dev/null +++ b/src/test/java/edu/brown/cs/student/ProfitCalculationTest.java @@ -0,0 +1,56 @@ +package edu.brown.cs.student; + +import edu.brown.cs.student.term.DatabaseQuerier; +import edu.brown.cs.student.term.hub.Holder; +import edu.brown.cs.student.term.hub.HubSearch; +import edu.brown.cs.student.term.hub.LinkMapper; +import org.junit.After; +import org.junit.Before; +import org.junit.Test; + +import java.time.Instant; +import java.util.Map; + +import static org.junit.Assert.assertEquals; +import static org.junit.Assert.assertTrue; + +public class ProfitCalculationTest { + + /** these should span the entire mock dataset */ + //12 am on 3/11 in UTC + private Instant start = Instant.parse("2021-03-11T05:00:00.00Z"); + //12 am on 3/28 in UTC + private Instant end = Instant.parse("2021-03-28T05:00:00.00Z"); + + private DatabaseQuerier db; + + @Before + public void setUp() { + try{ + db = new DatabaseQuerier("data/lil_mock.sqlite3"); + } catch(Exception e){ + System.out.println("DBQuerier Test, couldn't connect to db???"); + } + } + + @After + public void tearDown() { + db = null; + } + + @Test + public void testEmptyDB(){ + try{ + db = new DatabaseQuerier("data/empty.sqlite3"); + } catch(Exception e){ + System.out.println("DBQuerier Test, couldn't connect to db???"); + } + LinkMapper lm = new LinkMapper(db); + HubSearch hub = new HubSearch(lm); + Map<Holder, Double> hubRanks = hub.runHubSearch(start, end); + assertTrue(hubRanks.isEmpty()); + tearDown(); + } + + +}
\ No newline at end of file |