1 | #include "main/TxnHarnessDBInterface.h" |
2 | |
3 | using namespace duckdb; |
4 | using 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 | |
10 | void 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 | |
34 | void 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 | |
47 | void 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 | |
237 | void 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 | |
256 | void CTradeOrderDBInterface::DoTradeOrderFrame5(void) { |
257 | // con.Query("ROLLBACK"); |
258 | } |
259 | |
260 | void CTradeOrderDBInterface::DoTradeOrderFrame6(void) { |
261 | // con.Query("COMMIT"); |
262 | } |
263 | |