· 6 years ago · Aug 19, 2019, 10:08 PM
1package mr.Finance;
2
3import java.sql.Connection;
4import java.sql.DriverManager;
5import java.sql.ResultSet;
6import java.sql.SQLException;
7import java.sql.Statement;
8import java.text.DecimalFormat;
9import java.text.SimpleDateFormat;
10import java.util.ArrayList;
11import java.util.Collections;
12import java.util.Comparator;
13import java.util.Date;
14import java.util.HashMap;
15import java.util.LinkedHashMap;
16import java.util.LinkedList;
17import java.util.List;
18import java.util.Map;
19import java.util.Properties;
20
21public class AccountManager {
22
23 DownloadData d;
24 Double startAmount = 100000.0;
25 DecimalFormat formatter = new DecimalFormat("###,###,###.00");
26
27 List<Account> accounts = new ArrayList<>();
28
29 AccountManager(DownloadData d) {
30 this.d = d;
31
32 }
33
34 public boolean addNewAccount(String nickname, Double cash) {
35
36 Connection conn = null;
37 Properties connectionProps = new Properties();
38 connectionProps.put("user", "postgres");
39 connectionProps.put("password", "*pass word here*");
40
41 try {
42 conn = DriverManager.getConnection("jdbc:postgresql://localhost:5432/test", connectionProps);
43 if (!conn.isClosed()) {
44
45 String checkQuery = "select * from user_accounts where user_nickname ='" + nickname + "'";
46 Statement smt = conn.createStatement();
47 ResultSet rs = smt.executeQuery(checkQuery);
48 if (rs.next()) {
49
50 return false;
51 }
52
53 String insertQuery = "insert into user_accounts(user_nickname, cash_balance )values ('" + nickname
54 + "','" + cash + "');";
55
56 smt.executeUpdate(insertQuery);
57
58 }
59
60 } catch (Exception e) {
61 e.printStackTrace();
62 } finally {
63
64 try {
65 conn.close();
66 } catch (SQLException e) {
67
68 e.printStackTrace();
69 }
70 }
71
72 return true;
73
74 }
75
76 public Connection restart() {
77 Connection conn = null;
78 Properties connectionProps = new Properties();
79 connectionProps.put("user", "postgres");
80 connectionProps.put("password", "*pass word here*");
81
82 try {
83 conn = DriverManager.getConnection("jdbc:postgresql://localhost:5432/test", connectionProps);
84 if (!conn.isClosed()) {
85 System.out.println("Connected to database");
86
87 try {
88
89 String comm = "drop table user_accounts;";
90 comm += "drop table orders";
91
92 Statement smt = conn.createStatement();
93 smt.executeUpdate(comm);
94
95 System.out.println("Dropping user_accounts");
96 System.out.println("Dropping positions");
97
98 } catch (Exception e) {
99
100 e.printStackTrace();
101 }
102 try {
103
104 String comm = "create table user_accounts ( USER_ID serial primary key, "
105 + "USER_NICKNAME varchar(40) not null," + " CASH_BALANCE decimal not null" + " );";
106
107 comm += "create table orders (ORDER_ID serial primary key, USER_ID integer not null,"
108 + " TICKER varchar(40) not null," + " EXCHANGE varchar(40) not null,"
109 + "HOLD_METHOD varchar(40) not null," + "UNITS decimal not null," + "COST decimal not null"
110 + ")";
111
112 Statement smt = conn.createStatement();
113 smt.executeUpdate(comm);
114
115 System.out.println("Creating user_accounts");
116 System.out.println("Creating orders");
117
118 } catch (Exception e) {
119 e.printStackTrace();
120
121 }
122
123 finally {
124
125 conn.close();
126 }
127
128 }
129 } catch (Exception e) {
130
131 e.printStackTrace();
132
133 }
134 return conn;
135
136 }
137
138 public String openPosition(String nickName, Integer number, String tickerName, String exchange, String type) {
139
140 try {
141
142 Double value = getPositionValue(tickerName, exchange);
143
144 if (value != 0.0) {
145
146 int userId = getUserId(nickName);
147 if (userId == 0) {
148 return "User not in the system, create new account by typing .openaccount";
149 }
150
151 // check money in the bank
152 Double balance = getCashBalance(nickName);
153 if ((value * number) > balance) {
154
155 System.out.println(value * number + " " + getCashBalance(nickName));
156 return "Not enough money in bank to process this order.";
157 }
158
159 String query = "";
160
161 Connection conn = null;
162 Properties connectionProps = new Properties();
163 connectionProps.put("user", "postgres");
164 connectionProps.put("password", "*pass word here*");
165
166 conn = DriverManager.getConnection("jdbc:postgresql://localhost:5432/test", connectionProps);
167 if (!conn.isClosed()) {
168
169 // see if order exists
170
171 query = "select orders.units, orders.cost, orders.order_id from orders inner join user_accounts on user_accounts.user_id = orders.user_id where orders.ticker ='"
172 + tickerName + "' AND orders.hold_method='" + type + "' and user_accounts.user_nickname='"
173 + nickName + "'";
174
175 Statement smt = conn.createStatement();
176 ResultSet rs = smt.executeQuery(query);
177
178 Double orderAmount = 0.0;
179 Double orderCost = 0.0;
180 Integer orderId = 0;
181 while (rs.next()) {
182
183 orderAmount = rs.getDouble("UNITS");
184 orderCost = rs.getDouble("COST");
185 orderId = rs.getInt("ORDER_ID");
186 }
187
188 if (orderId == 0) {
189 System.out.println("New Order");
190 orderAmount = (double) number;
191 orderCost = value * number;
192 query = "insert into orders(USER_ID, TICKER, EXCHANGE, HOLD_METHOD, UNITS, COST ) values ('"
193 + userId + "','" + tickerName + "','" + exchange + "','" + type + "', '" + orderAmount
194 + "', '" + orderCost + "') ;";
195
196 } else {
197
198 System.out.println("Order Already Exists");
199 orderAmount += number;
200 orderCost += (value * number);
201
202 query = "UPDATE orders SET COST ='" + orderCost + "', UNITS ='" + orderAmount
203 + "' WHERE order_id = '" + orderId + "' ;";
204
205 }
206
207 query += "update user_accounts set cash_balance='" + (balance - (value * number))
208 + "' where user_nickname='" + nickName + "';";
209
210 smt.executeUpdate(query);
211
212 }
213
214 } else {
215
216 return "Ticker not found at this moment.";
217
218 }
219 } catch (Exception e) {
220
221 e.printStackTrace();
222 return "Something went wrong.";
223 }
224 return "Order processed";
225
226 }
227
228 public String closePosition(String nickName, Integer number, String tickerName, String exchange, String type) {
229 int positionNum = 0;
230
231 Double value = getPositionValue(tickerName, exchange);
232
233 if(value == 0.0) {
234
235 return "Order failed try again later.";
236 }
237
238
239 Connection conn = null;
240 Properties connectionProps = new Properties();
241 connectionProps.put("user", "postgres");
242 connectionProps.put("password", "*pass word here*");
243 try {
244 conn = DriverManager.getConnection("jdbc:postgresql://localhost:5432/test", connectionProps);
245 if (!conn.isClosed()) {
246
247 String query = "select orders.order_id, orders.cost, orders.units from orders"
248 + " inner join user_accounts on user_accounts.user_id = orders.user_id"
249 + " where orders.ticker='" + tickerName + "' and user_accounts.user_nickname='" + nickName
250 + "' and orders.hold_method= '" + type + "' and orders.exchange ='" + exchange + "';";
251 Statement smt = conn.createStatement();
252 ResultSet rs = smt.executeQuery(query);
253 Double returnOnInvestment = 0.0;
254 query = "";
255
256 Integer orderId = 0;
257 Double orderCost = 0.0;
258 Double orderUnits = 0.0;
259 while (rs.next()) {
260
261 orderId = rs.getInt("ORDER_ID");
262 orderCost = rs.getDouble("COST");
263 orderUnits = rs.getDouble("UNITS");
264 }
265
266 if (orderUnits < number) {
267
268 return "You only have " + orderUnits + " " + tickerName + ".";
269 } else if (orderUnits == 0.0) {
270
271 return "You don't own this position.";
272 }
273
274 Double costBasis = (orderCost / orderUnits) * number;
275
276 query = "UPDATE orders SET units ='" + (orderUnits - number) + "',cost='" + (orderCost - costBasis)
277 + "' WHERE order_id ='" + orderId + "' ;";
278
279 if (type.contentEquals("long")) {
280
281 returnOnInvestment = value * number;
282
283 } else if (type.contentEquals("short")) {
284 returnOnInvestment = costBasis + (costBasis - (value * number));
285
286 }
287
288 System.out.println(getCashBalance(nickName) + " " + returnOnInvestment);
289 query += "UPDATE user_accounts SET cash_balance ='" + (getCashBalance(nickName) + returnOnInvestment)
290 + "' WHERE user_nickname ='" + nickName + "';";
291 smt.executeUpdate(query);
292
293 }
294 } catch (Exception e) {
295
296 e.printStackTrace();
297
298 return "Something went wrong.";
299 }
300
301 return "Order processed. " + number + " positions closed.";
302
303 }
304
305 public Integer getUserId(String nickName) {
306
307 Connection conn = null;
308 Properties connectionProps = new Properties();
309 connectionProps.put("user", "postgres");
310 connectionProps.put("password", "*pass word here*");
311 Integer userId = 0;
312 try {
313 conn = DriverManager.getConnection("jdbc:postgresql://localhost:5432/test", connectionProps);
314
315 if (!conn.isClosed()) {
316 String comm = "select user_id from user_accounts where user_nickname='" + nickName + "'";
317 Statement smt = conn.createStatement();
318 ResultSet rs = smt.executeQuery(comm);
319
320 while (rs.next()) {
321 userId = rs.getInt("USER_ID");
322
323 }
324 }
325
326 } catch (Exception e) {
327 e.printStackTrace();
328
329 }
330
331 return userId;
332 }
333
334 public Double getCashBalance(String nickName) {
335
336 Connection conn = null;
337 Properties connectionProps = new Properties();
338 connectionProps.put("user", "postgres");
339 connectionProps.put("password", "*pass word here*");
340 Double cash = 0.0;
341 try {
342 conn = DriverManager.getConnection("jdbc:postgresql://localhost:5432/test", connectionProps);
343
344 if (!conn.isClosed()) {
345 String comm = "select cash_balance from user_accounts where user_nickname='" + nickName + "'";
346 Statement smt = conn.createStatement();
347 ResultSet rs = smt.executeQuery(comm);
348
349 while (rs.next()) {
350 cash = rs.getDouble("CASH_BALANCE");
351
352 }
353 }
354
355 } catch (Exception e) {
356 e.printStackTrace();
357
358 }
359
360 return cash;
361 }
362
363 public Double getPositionValue(String ticker, String exchange) {
364
365 Double value = 0.0;
366 System.out.println(ticker);
367 System.out.println(exchange);
368 if (exchange.contentEquals("equity")) {
369
370 Object data[] = d.stock.getJsoupPrice(ticker.toLowerCase(), "");
371 // Object data[] = d.stock.getCommodity(ticker.toLowerCase(), false);
372 value = (Double) data[0];
373 System.out.println(value);
374
375 } else if (exchange.contentEquals("crypto")) {
376
377 Object data[] = d.stock.getCrypto(ticker);
378 value = (Double) data[0];
379 }
380
381 return value;
382 }
383
384 public List<String> getPortfolio(String nickName) {
385
386 HashMap<String, Double> tickerPrice = new HashMap<String, Double>();
387 Double marketValue = 0.0;
388 List<String> port = new ArrayList<>();
389 Connection conn = null;
390 Properties connectionProps = new Properties();
391 connectionProps.put("user", "postgres");
392 connectionProps.put("password", "*pass word here*");
393
394 try {
395
396 conn = DriverManager.getConnection("jdbc:postgresql://localhost:5432/test", connectionProps);
397 if (!conn.isClosed()) {
398 System.out.println("Connected to database");
399
400 try {
401 System.out.println(nickName);
402 String query = "select orders.ticker , orders.exchange " + "from orders "
403 + "inner join user_accounts " + "on user_accounts.user_id = orders.user_id "
404 + "where user_accounts.user_nickname='" + nickName + "'";
405
406 Statement smt = conn.createStatement();
407
408 ResultSet rs = smt.executeQuery(query);
409
410 while (rs.next()) {
411
412 String ticker = rs.getString("TICKER");
413 if (tickerPrice.get(ticker) == null) {
414
415 tickerPrice.put(ticker, getPositionValue(ticker, rs.getString("EXCHANGE")));
416 }
417
418 }
419 port.add("Ticker Qty Cost Basis Cur Price Percentage");
420 port.add("====================================================");
421 port.add(" ");
422 port.add("long ");
423 port.add("----------------------------------------------------");
424
425 for (Map.Entry<String, Double> entry : tickerPrice.entrySet()) {
426
427 query = "select orders.units, orders.cost " + "from orders " + "inner join user_accounts "
428 + "on user_accounts.user_id = orders.user_id "
429 + "where user_accounts.user_nickname='" + nickName
430 + "' and orders.hold_method ='long' and orders.ticker='"
431 + entry.getKey() + "'";
432
433 rs = smt.executeQuery(query);
434 Double orderCost = 0.0;
435 Double quantity = 0.0;
436 while (rs.next()) {
437
438 quantity = rs.getDouble("UNITS");
439 orderCost = rs.getDouble("COST");
440 }
441
442 Double costBasis = (orderCost / quantity);
443 if (quantity != 0.0) {
444 port.add(entry.getKey().toUpperCase() + " " + quantity + " "
445 + formatter.format(costBasis) + " " + formatter.format(entry.getValue())
446 + " " + formatter.format(((entry.getValue() / costBasis) - 1) * 100) + "%");
447 marketValue += (entry.getValue() * quantity);
448 }
449 }
450
451 port.add(" ");
452 port.add("short ");
453 port.add("----------------------------------------------------");
454
455 for (Map.Entry<String, Double> entry : tickerPrice.entrySet()) {
456
457 query = "select orders.units, orders.cost " + "from orders " + "inner join user_accounts "
458 + "on user_accounts.user_id = orders.user_id "
459 + "where user_accounts.user_nickname='" + nickName
460 + "' and orders.hold_method ='short' and orders.ticker='"
461 + entry.getKey() + "'";
462
463 rs = smt.executeQuery(query);
464 Double orderCost = 0.0;
465 Double quantity = 0.0;
466 while (rs.next()) {
467
468 quantity = rs.getDouble("UNITS");
469 orderCost = rs.getDouble("COST");
470 }
471
472 Double costBasis = (orderCost / quantity);
473 if (quantity != 0.0) {
474 port.add(entry.getKey().toUpperCase() + " " + quantity + " "
475 + formatter.format(costBasis) + " " + formatter.format(entry.getValue())
476 + " " + formatter.format(((costBasis / entry.getValue()) - 1) * 100) + "%");
477
478 marketValue += orderCost + (orderCost - (entry.getValue() * quantity));
479 }
480 }
481
482 query = "select user_accounts.cash_balance from user_accounts where user_nickname ='" + nickName
483 + "'";
484 rs = smt.executeQuery(query);
485 Double cash = 0.0;
486 while (rs.next()) {
487
488 cash = rs.getDouble("CASH_BALANCE");
489
490 }
491 port.add(" ");
492 port.add("----------------------------------------------------");
493 port.add("Cash Balance: $ " + formatter.format(cash));
494 port.add("====================================================");
495 port.add("Total: $ " + formatter.format(cash + marketValue));
496 port.add(Double.toString(cash + marketValue));
497
498 } catch (Exception e) {
499 e.printStackTrace();
500
501 }
502 }
503 } catch (Exception e) {
504
505 e.printStackTrace();
506 }
507
508 return port;
509 }
510
511 public static HashMap<String, Double> sortByValue(HashMap<String, Double> hm) {
512
513 List<Map.Entry<String, Double>> list = new LinkedList<Map.Entry<String, Double>>(hm.entrySet());
514
515 Collections.sort(list, new Comparator<Map.Entry<String, Double>>() {
516 public int compare(Map.Entry<String, Double> o1, Map.Entry<String, Double> o2) {
517 return (o2.getValue()).compareTo(o1.getValue());
518 }
519 });
520
521 HashMap<String, Double> temp = new LinkedHashMap<String, Double>();
522 for (Map.Entry<String, Double> aa : list) {
523 temp.put(aa.getKey(), aa.getValue());
524 }
525 return temp;
526 }
527
528 public List<String> getScore() {
529
530 List<String> scores = new ArrayList<>();
531 HashMap<String, Double> userValue = new HashMap<String, Double>();
532
533 Connection conn = null;
534 Properties connectionProps = new Properties();
535 connectionProps.put("user", "postgres");
536 connectionProps.put("password", "*pass word here*");
537
538 try {
539 conn = DriverManager.getConnection("jdbc:postgresql://localhost:5432/test", connectionProps);
540 if (!conn.isClosed()) {
541
542 String query = "select user_nickname from user_accounts";
543
544 Statement smt = conn.createStatement();
545
546 ResultSet rs = smt.executeQuery(query);
547
548 while (rs.next()) {
549
550 String nickname = rs.getString("USER_NICKNAME");
551 List<String> portfolio = getPortfolio(nickname);
552 userValue.put(nickname, Double.parseDouble(portfolio.get(portfolio.size() - 1)));
553
554 }
555 HashMap<String, Double> sort = sortByValue(userValue);
556
557 Integer rank = 0;
558 for (Map.Entry<String, Double> entry : sort.entrySet()) {
559 rank++;
560
561 String line = rank + ".";
562 for (int i = 0; i < (3 - Integer.toString(rank).length()); i++) {
563
564 line += " ";
565 }
566
567 line += entry.getKey();
568
569 for (int i = 0; i < (16 - entry.getKey().length()); i++) {
570
571 line += " ";
572 }
573 line += "$" + formatter.format(entry.getValue());
574 scores.add(line);
575
576 }
577
578 }
579
580 } catch (Exception e) {
581
582 }
583
584 return scores;
585 }
586
587}