// info about the trade
voltQueueSQL(getTrade, trade_id);
VoltTable trade = voltExecuteSQL()[0];
assert trade.getRowCount() == 1;
VoltTableRow trade_row = trade.fetchRow(0);
long acct_id = trade_row.getLong("T_CA_ID");
String type_id = trade_row.getString("T_TT_ID");
String symbol = trade_row.getString("T_S_SYMB");
int trade_qty = (int)trade_row.getLong("T_QTY");
double charge = trade_row.getDouble("T_CHRG");
int is_lifo = (int)(int)trade_row.getLong("T_LIFO");
int trade_is_cash = (int)trade_row.getLong("T_IS_CASH");
// info about a type of the trade and customer's holdings for the symbol
voltQueueSQL(getTradeType, type_id);
voltQueueSQL(getHoldingSummary, acct_id, symbol);
VoltTable[] trade_type_hold = voltExecuteSQL();
VoltTable trade_type = trade_type_hold[0];
assert trade_type.getRowCount() == 1;
VoltTableRow trade_type_row = trade_type.fetchRow(0);
String type_name = trade_type_row.getString("TT_NAME");
int type_is_sell = (int)(int)trade_type_row.getLong("TT_IS_SELL");
int type_is_market = (int)trade_type_row.getLong("TT_IS_MRKT");
VoltTable hold_sum = trade_type_hold[1];
int hs_qty = 0;
if (hold_sum.getRowCount() > 0) {
assert hold_sum.getRowCount() == 1;
hs_qty = (int)hold_sum.fetchRow(0).getLong("HS_QTY");
}
// frame 2: modifying the customer's holdings
// first, some cusomer's account info
voltQueueSQL(getCustomerAccount, acct_id);
VoltTable account = voltExecuteSQL()[0];
assert account.getRowCount() == 1;
VoltTableRow account_row = account.fetchRow(0);
long broker_id = account_row.getLong("CA_B_ID");
long cust_id = account_row.getLong("CA_C_ID");
int tax_status = (int)account_row.getLong("CA_TAX_ST");
int needed_qty = trade_qty;
double buy_value = 0;
double sell_value = 0;
Date trade_dts = Calendar.getInstance().getTime();
if (type_is_sell == 1) {
if (hs_qty == 0) {
voltQueueSQL(insertHoldingSummary, acct_id, symbol, -trade_qty);
voltExecuteSQL();
}
else if (hs_qty != trade_qty) {
voltQueueSQL(updateHoldingSummary, hs_qty - trade_qty, acct_id, symbol);
voltExecuteSQL();
}
if (hs_qty > 0) {
if (is_lifo == 1) {
voltQueueSQL(getHoldingDesc, acct_id, symbol);
}
else {
voltQueueSQL(getHoldingAsc, acct_id, symbol);
}
// modify existing holdings
VoltTable hold_list = voltExecuteSQL()[0];
for (int i = 0; i < hold_list.getRowCount() && needed_qty != 0; i++) {
VoltTableRow hold = hold_list.fetchRow(i);
long hold_id = hold.getLong("H_T_ID");
int hold_qty = (int)hold.getLong("H_QTY");
double hold_price = hold.getDouble("H_PRICE");
if (hold_qty > needed_qty) {
voltQueueSQL(insertHoldingHistory, hold_id, trade_id, hold_qty, hold_qty - needed_qty);
voltQueueSQL(updateHolding, hold_qty - needed_qty, hold_id);
buy_value += needed_qty * hold_price;
sell_value += needed_qty * trade_price;
needed_qty = 0;
}
else {
voltQueueSQL(insertHoldingHistory, hold_id, trade_id, hold_qty, 0);
voltQueueSQL(deleteHolding, hold_id);
buy_value += hold_qty * hold_price;
sell_value += hold_qty * trade_price;
needed_qty = needed_qty - hold_qty;
}
}
// execute all updates from the above loop
voltExecuteSQL();
}
// need to sell more? go short
if (needed_qty > 0) {
voltQueueSQL(insertHoldingHistory, trade_id, trade_id, 0, -needed_qty);
voltQueueSQL(insertHolding, trade_id, acct_id, symbol, trade_dts, trade_price, -needed_qty);
voltExecuteSQL();
}
else if (hs_qty == trade_qty) {
voltQueueSQL(deleteHoldingSummary, acct_id, symbol);
voltExecuteSQL();
}
}
else { // buy trade
if (hs_qty == 0) {
voltQueueSQL(insertHoldingSummary, acct_id, symbol, trade_qty);
voltExecuteSQL();
}
else if (-hs_qty != trade_qty) {
voltQueueSQL(updateHoldingSummary, hs_qty + trade_qty, acct_id, symbol);
voltExecuteSQL();
}
if (hs_qty < 0) {
if (is_lifo == 1) {
voltQueueSQL(getHoldingDesc, acct_id, symbol);
}
else {
voltQueueSQL(getHoldingAsc, acct_id, symbol);
}
// modify existing holdings
VoltTable hold_list = voltExecuteSQL()[0];
for (int i = 0; i < hold_list.getRowCount() && needed_qty != 0; i++) {
VoltTableRow hold = hold_list.fetchRow(i);
long hold_id = hold.getLong("H_T_ID");
int hold_qty = (int)hold.getLong("H_QTY");
double hold_price = hold.getDouble("H_PRICE");
if (hold_qty + needed_qty < 0) {
voltQueueSQL(insertHoldingHistory, hold_id, trade_id, hold_qty, hold_qty + needed_qty);
voltQueueSQL(updateHolding, hold_qty + needed_qty, hold_id);
sell_value += needed_qty * hold_price;
buy_value += needed_qty * trade_price;
needed_qty = 0;
}
else {
voltQueueSQL(insertHoldingHistory, hold_id, trade_id, hold_qty, 0);
voltQueueSQL(deleteHolding, hold_id);
hold_qty = -hold_qty;
sell_value += hold_qty * hold_price;
buy_value += hold_qty * trade_price;
needed_qty = needed_qty - hold_qty;
}
}
// execute all updates from the above loop
voltExecuteSQL();
}
// all shorts are covered? a new long is created
if (needed_qty > 0) {
voltQueueSQL(insertHoldingHistory, trade_id, trade_id, 0, needed_qty);
voltQueueSQL(insertHolding, trade_id, acct_id, symbol, trade_dts, trade_price, needed_qty);
voltExecuteSQL();
}
else if (-hs_qty == trade_qty) {
voltQueueSQL(deleteHoldingSummary, acct_id, symbol);
voltExecuteSQL();
}
}
// frame 3: taxes
double tax_amount = 0;
if ((tax_status == 1 || tax_status == 2) && sell_value > buy_value) {
voltQueueSQL(getTaxrate, cust_id);
VoltTable tax_rate = voltExecuteSQL()[0];
assert tax_rate.getRowCount() == 1;
double tax_rates = tax_rate.fetchRow(0).getDouble(0);
tax_amount = (sell_value - buy_value) * tax_rates;
voltQueueSQL(updateTrade1, tax_amount, trade_id);
voltExecuteSQL();
}
// frame 4: calculate the broker's commission
voltQueueSQL(getSecurity, symbol);
voltQueueSQL(getCustomer, cust_id);
VoltTable[] sec_cust = voltExecuteSQL();
VoltTable sec = sec_cust[0];
VoltTable cust = sec_cust[1];
assert sec.getRowCount() == 1;
assert cust.getRowCount() == 1;
VoltTableRow sec_row = sec.fetchRow(0);
String s_ex_id = sec_row.getString("S_EX_ID");
String s_name = sec_row.getString("S_NAME");
int c_tier = (int)cust.fetchRow(0).getLong("C_TIER");
voltQueueSQL(getCommissionRate, c_tier, type_id, s_ex_id, trade_qty, trade_qty); // limit to 1 row
VoltTable comm = voltExecuteSQL()[0];