1#include "main/TxnHarnessDBInterface.h"
2
3using namespace duckdb;
4using namespace TPCE;
5
6// implementation according to TPC-E spec
7// see:
8// http://hstore.cs.brown.edu/wordpress/wp-content/uploads/2011/05/TPCE-v0.32.2g.pdf
9
10void CTradeOrderDBInterface::DoTradeOrderFrame1(const TTradeOrderFrame1Input *pIn, TTradeOrderFrame1Output *pOut) {
11 // auto id = pIn->acct_id;
12
13 // con.Query("BEGIN TRANSACTION");
14 // auto result = con.Query("select CA_NAME, CA_B_ID, CA_C_ID, CA_TAX_ST, C_F_NAME, C_L_NAME, "
15 // "C_TIER, C_TAX_ID, B_NAME from CUSTOMER_ACCOUNT, CUSTOMER, BROKER "
16 // "where CA_C_ID=C_ID AND CA_B_ID=B_ID AND CA_ID = " +
17 // to_string(id));
18 // pOut->num_found = result->collection.count;
19 // if (pOut->num_found > 0) {
20 // assert(pOut->num_found == 1);
21
22 // strcpy(pOut->acct_name, result->GetValue<const char *>(0, 0));
23 // pOut->broker_id = result->GetValue<int32_t>(1, 0);
24 // pOut->cust_id = result->GetValue<int32_t>(2, 0);
25 // pOut->tax_status = result->GetValue<int32_t>(3, 0);
26 // strcpy(pOut->cust_f_name, result->GetValue<const char *>(4, 0));
27 // strcpy(pOut->cust_l_name, result->GetValue<const char *>(5, 0));
28 // pOut->cust_tier = result->GetValue<int>(6, 0);
29 // strcpy(pOut->tax_id, result->GetValue<const char *>(7, 0));
30 // strcpy(pOut->broker_name, result->GetValue<const char *>(8, 0));
31 // }
32}
33
34void CTradeOrderDBInterface::DoTradeOrderFrame2(const TTradeOrderFrame2Input *pIn, TTradeOrderFrame2Output *pOut) {
35 // auto result =
36 // con.Query("select AP_ACL from ACCOUNT_PERMISSION where AP_CA_ID = " + to_string(pIn->acct_id) +
37 // " and AP_F_NAME = " + string(pIn->exec_f_name) + " and AP_L_NAME = " + string(pIn->exec_l_name) +
38 // " and AP_TAX_ID = " + string(pIn->exec_tax_id));
39 // if (result->collection.count > 0) {
40 // assert(result->collection.count == 1);
41 // strcpy(pOut->ap_acl, result->GetValue<const char *>(0, 0));
42 // } else {
43 // pOut->ap_acl[0] = '\0';
44 // }
45}
46
47void CTradeOrderDBInterface::DoTradeOrderFrame3(const TTradeOrderFrame3Input *pIn, TTradeOrderFrame3Output *pOut) {
48 // int exch_id;
49 // string symbol;
50 // if (pIn->symbol[0] == '\0') {
51 // // empty symbol
52 // auto result = con.Query("select S_EX_ID, S_NAME, S_SYMB from COMPANY, "
53 // "SECURITY where S_CO_ID=CO_ID AND CO_NAME = " +
54 // string(pIn->co_name) + " AND S_ISSUE=" + string(pIn->issue));
55 // assert(result->collection.count == 1);
56 // exch_id = result->GetValue<int>(0, 0);
57 // strcpy(pOut->s_name, result->GetValue<const char *>(1, 0));
58 // symbol = string(result->GetValue<const char *>(2, 0));
59 // strcpy(pOut->symbol, symbol.c_str());
60 // } else {
61 // symbol = string(pIn->symbol);
62
63 // auto result = con.Query("select CO_NAME, S_EX_ID, S_NAME from SECURITY, COMPANY "
64 // "where CO_ID = S_CO_ID AND S_SYMB = " +
65 // symbol);
66 // assert(result->collection.count == 1);
67 // strcpy(pOut->co_name, result->GetValue<const char *>(0, 0));
68 // exch_id = result->GetValue<int>(1, 0);
69 // strcpy(pOut->s_name, result->GetValue<const char *>(2, 0));
70 // }
71 // // Get current pricing information for the security select
72 // auto result = con.Query("SELECT market_price = LT_PRICE from LAST_TRADE where LT_S_SYMB = " + symbol);
73 // pOut->market_price = result->GetValue<double>(0, 0);
74
75 // // Set trade characteristics based on the type of trade. select
76 // result = con.Query("SELECT TT_IS_MRKT, TT_IS_SELL from TRADE_TYPE where TT_ID = " + string(pIn->trade_type_id));
77 // pOut->type_is_market = result->GetValue<bool>(0, 0);
78 // pOut->type_is_sell = result->GetValue<bool>(1, 0);
79
80 // // If this is a limit-order, then the requested_price was passed in to us,
81 // // but if this this a market-order, then we need to set the requested_price
82 // // to the current market price.
83 // if (pOut->type_is_market) {
84 // pOut->requested_price = pOut->market_price;
85 // }
86
87 // // Local frame variables used when estimating impact of this trade on
88 // // any current holdings of the same security.
89 // double buy_value = 0.0;
90 // double sell_value = 0.0;
91 // int needed_qty = pIn->trade_qty;
92
93 // result = con.Query("select HS_QTY from HOLDING_SUMMARY where HS_CA_ID = " + to_string(pIn->acct_id) +
94 // " and HS_S_SYMB = " + symbol);
95 // int hs_qty = result->GetValue<int>(0, 0);
96
97 // if (pOut->type_is_sell) {
98 // // This is a sell transaction, so estimate the impact to any currently
99 // // held long positions in the security.
100 // if (hs_qty > 0) {
101 // if (pIn->is_lifo) {
102 // // Estimates will be based on closing most recently acquired
103 // // holdings Could return 0, 1 or many rows
104 // result = con.Query("select H_QTY, H_PRICE from HOLDING where H_CA_ID = " + to_string(pIn->acct_id) +
105 // " and H_S_SYMB = " + symbol + " order by H_DTS desc");
106 // } else {
107 // // Estimates will be based on closing oldest holdings
108 // // Could return 0, 1 or many rows
109 // result = con.Query("select H_QTY, H_PRICE from HOLDING where H_CA_ID = " + to_string(pIn->acct_id) +
110 // " and H_S_SYMB = " + symbol + " order by H_DTS asc");
111 // }
112
113 // // Estimate, based on the requested price, any profit that may be
114 // // realized by selling current holdings for this security. The
115 // // customer may have multiple holdings for this security
116 // // (representing different purchases of this security at different
117 // // times and therefore, most likely, different prices).
118 // for (size_t i = 0; needed_qty != 0 || i < result->size(); i++) {
119 // int hold_qty = result->GetValue<int>(0, i);
120 // double hold_price = result->GetValue<double>(1, i);
121 // if (hold_qty > needed_qty) {
122 // // Only a portion of this holding would be sold as a result
123 // // of the // trade.
124 // buy_value += needed_qty * hold_price;
125 // sell_value += needed_qty * pIn->requested_price;
126 // needed_qty = 0;
127 // } else {
128 // // All of this holding would be sold as a result of this
129 // // trade.
130 // buy_value += hold_qty * hold_price;
131 // sell_value += hold_qty * pIn->requested_price;
132 // needed_qty = needed_qty - hold_qty;
133 // }
134 // }
135 // }
136 // // NOTE: If needed_qty is still greater than 0 at this point, then the
137 // // customer would be liquidating all current holdings for this security,
138 // // and then short-selling this remaining balance for the transaction.
139 // } else {
140 // // This is a buy transaction, so estimate the impact to any currently
141 // // held short positions in the security. These are represented as
142 // // negative H_QTY holdings. Short postions will be covered before
143 // // opening a long postion in // this security.
144 // if (hs_qty < 0) {
145 // if (pIn->is_lifo) {
146 // // Estimates will be based on closing most recently acquired
147 // // holdings Could return 0, 1 or many rows
148 // result = con.Query("select H_QTY, H_PRICE from HOLDING where H_CA_ID = " + to_string(pIn->acct_id) +
149 // " and H_S_SYMB = " + symbol + " order by H_DTS desc");
150 // } else {
151 // // Estimates will be based on closing oldest holdings
152 // // Could return 0, 1 or many rows
153 // result = con.Query("select H_QTY, H_PRICE from HOLDING where H_CA_ID = " + to_string(pIn->acct_id) +
154 // " and H_S_SYMB = " + symbol + " order by H_DTS asc");
155 // }
156 // // Estimate, based on the requested price, any profit that may be
157 // // realized by covering short postions currently held for this
158 // // security. The customer may have multiple holdings for this
159 // // security (representing different purchases of this security at
160 // // different times and therefore, most likely, different prices).
161 // for (size_t i = 0; needed_qty != 0 || i < result->size(); i++) {
162 // int hold_qty = result->GetValue<int>(0, i);
163 // double hold_price = result->GetValue<double>(1, i);
164 // if (hold_qty + needed_qty < 0) {
165 // // Only a portion of this holding would be covered (bought
166 // // back) as a result of this trade.
167 // sell_value += needed_qty * hold_price;
168 // buy_value += needed_qty * pIn->requested_price;
169 // needed_qty = 0;
170 // } else {
171 // // All of this holding would be covered (bought back) as
172 // // a result of this trade.
173 // // NOTE: Local variable hold_qty is made positive for easy
174 // // calculations
175 // hold_qty = -hold_qty;
176 // sell_value += hold_qty * hold_price;
177 // buy_value += hold_qty * pIn->requested_price;
178 // needed_qty = needed_qty - hold_qty;
179 // }
180 // }
181 // // NOTE: If needed_qty is still greater than 0 at this point, then
182 // // the customer would cover all current short positions for this
183 // // security, (if any) and then open a new long position for the
184 // // remaining balance // of this transaction.
185 // }
186 // }
187 // // Estimate any capital gains tax that would be incurred as a result of this
188 // // // transaction.
189 // pOut->tax_amount = 0.0;
190 // if ((sell_value > buy_value) && ((pIn->tax_status == 1) || (pIn->tax_status == 2))) {
191 // // Customer’s can be (are) subject to more than one tax rate.
192 // // For example, a state tax rate and a federal tax rate. Therefore,
193 // // get all tax rates the customer is subject to, and estimate overall
194 // // amount of tax that would result from this order.
195 // auto result = con.Query("select sum(TX_RATE) from TAXRATE where TX_ID in ( "
196 // "select CX_TX_ID from CUSTOMER_TAXRATE where CX_C_ID = " +
197 // to_string(pIn->cust_id) + ")");
198 // pOut->tax_amount = (sell_value - buy_value) * result->GetValue<double>(0, 0);
199 // }
200 // // Get administrative fees (e.g. trading charge, commision rate)
201 // result = con.Query("select CR_RATE from COMMISSION_RATE where CR_C_TIER = " + to_string(pIn->cust_tier) +
202 // " and CR_TT_ID = " + string(pIn->trade_type_id) + " and CR_EX_ID = " + to_string(exch_id) +
203 // " and CR_FROM_QTY <= " + to_string(pIn->trade_qty) +
204 // " and CR_TO_QTY >= " + to_string(pIn->trade_qty));
205 // pOut->comm_rate = result->GetValue<double>(0, 0);
206
207 // result = con.Query("select CH_CHRG from CHARGE where CH_C_TIER = " + to_string(pIn->cust_tier) +
208 // " and CH_TT_ID = " + string(pIn->trade_type_id));
209 // pOut->charge_amount = result->GetValue<double>(0, 0);
210
211 // pOut->acct_assets = 0;
212 // if (pIn->type_is_margin) {
213 // // get the current account balance
214 // result = con.Query("select CA_BAL from CUSTOMER_ACCOUNT where CA_ID = acct_id");
215 // double acct_bal = 0;
216 // if (result->collection.count > 0) {
217 // assert(result->collection.count == 1);
218 // acct_bal = result->GetValue<double>(0, 0);
219 // }
220 // // update the account balance with the new price
221 // result = con.Query("select sum(HS_QTY * LT_PRICE) from HOLDING_SUMMARY, LAST_TRADE "
222 // "where HS_CA_ID = acct_id and LT_S_SYMB = HS_S_SYMB");
223 // if (result->ValueIsNull(0, 0)) {
224 // /* account currently has no holdings */
225 // pOut->acct_assets = acct_bal;
226 // } else {
227 // pOut->acct_assets = result->GetValue<double>(0, 0) + acct_bal;
228 // }
229 // }
230 // if (pOut->type_is_market) {
231 // strcpy(pOut->status_id, pIn->st_submitted_id);
232 // } else {
233 // strcpy(pOut->status_id, pIn->st_pending_id);
234 // }
235}
236
237void CTradeOrderDBInterface::DoTradeOrderFrame4(const TTradeOrderFrame4Input *pIn, TTradeOrderFrame4Output *pOut) {
238 // // FIXME: auto increment column
239 // // FIXME: NOW() function
240 // // FIXME: get auto increment column value from insert?
241 // con.Query("insert into TRADE (T_DTS, T_ST_ID, T_TT_ID, T_IS_CASH, T_S_SYMB, "
242 // "T_QTY, T_BID_PRICE, T_CA_ID, T_EXEC_NAME, T_TRADE_PRICE, T_CHRG, "
243 // "T_COMM, T_TAX, T_LIFO) VALUES (NOW(), " +
244 // string(pIn->status_id) + ", " + string(pIn->trade_type_id) + ", " + to_string(pIn->is_cash) + ", " +
245 // string(pIn->symbol) + ", " + to_string(pIn->trade_qty) + ", " + to_string(pIn->requested_price) + ", "
246 // + to_string(pIn->acct_id) + ", " + string(pIn->exec_name) + ", NULL, " + to_string(pIn->charge_amount)
247 // +
248 // ", " + to_string(pIn->comm_amount) + ", 0, " + to_string(pIn->is_lifo) + ");");
249 // if (!pIn->type_is_market) {
250 // // con.Query("INSERT INTO TRADE_REQUEST (TR_T_ID, TR_TT_ID, TR_S_SYMB,
251 // // TR_QTY, TR_BID_PRICE, TR_CA_ID) VALUES ()");
252 // }
253 // assert(0);
254}
255
256void CTradeOrderDBInterface::DoTradeOrderFrame5(void) {
257 // con.Query("ROLLBACK");
258}
259
260void CTradeOrderDBInterface::DoTradeOrderFrame6(void) {
261 // con.Query("COMMIT");
262}
263