/*
* Licensed to the Apache Software Foundation (ASF) under one or more
* contributor license agreements. See the NOTICE file distributed with
* this work for additional information regarding copyright ownership.
* The ASF licenses this file to You under the Apache License, Version 2.0
* (the "License"); you may not use this file except in compliance with
* the License. You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
package org.apache.stonehenge.stocktrader.mssql;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.stonehenge.stocktrader.CustomMarketSummaryBean;
import org.apache.stonehenge.stocktrader.CustomQuoteBean;
import org.apache.stonehenge.stocktrader.dal.DAOException;
import org.apache.stonehenge.stocktrader.dal.MarketSummaryDAO;
import org.apache.stonehenge.stocktrader.util.StockTraderSQLUtil;
import org.apache.stonehenge.stocktrader.util.StockTraderUtility;
public class MSSQLMarketSummaryDAO extends AbstractMSSQLDAO implements
MarketSummaryDAO {
private static final Log logger = LogFactory
.getLog(MSSQLMarketSummaryDAO.class);
private static final String SQL_SELECT_QUOTE = "Set NOCOUNT ON; SELECT symbol, companyname, volume, price, open1, low, high, change1 from dbo.quote with (ROWLOCK) where symbol = ?";
private static final String SQL_SELECT_QUOTE_NOLOCK = "Set NOCOUNT ON; SELECT symbol, companyname, volume, price, open1, low, high, change1 from dbo.quote with (NOLOCK) where symbol = ?";
private static final String SQL_UPDATE_STOCKPRICEVOLUME = "UPDATE dbo.QUOTE WITH (ROWLOCK) SET PRICE=?, Low=?, High=?, Change1=?-open1, VOLUME=VOLUME+? WHERE SYMBOL=?";
private static final String SQL_SELECT_MARKETSUMMARY_GAINERS = "Set NOCOUNT ON; SELECT symbol, companyname, volume, price, open1, low, high, change1 from dbo.quote with (NOLOCK) where symbol like 's:1__' order by change1 desc";
private static final String SQL_SELECT_MARKETSUMMARY_LOSERS = "Set NOCOUNT ON; SELECT symbol, companyname, volume, price, open1, low, high, change1 from dbo.quote with (NOLOCK) where symbol like 's:1__' order by change1";
private static final String SQL_SELECT_MARKETSUMMARY_TSIA = "Set NOCOUNT ON; select SUM(price)/count(*) as TSIA from dbo.quote where symbol like 's:1__'";
private static final String SQL_SELECT_MARKETSUMMARY_OPENTSIA = "Set NOCOUNT ON; select SUM(open1)/count(*) as openTSIA from dbo.quote where symbol like 's:1__'";
private static final String SQL_SELECT_MARKETSUMMARY_VOLUME = "Set NOCOUNT ON; SELECT SUM(volume) from dbo.quote where symbol like 's:1__'";
public MSSQLMarketSummaryDAO(Connection sqlConnection) throws DAOException {
super(sqlConnection);
}
public CustomQuoteBean getQuote(String symbol) throws DAOException {
if (logger.isDebugEnabled()) {
logger.debug("MarketSummaryDAO.getQouteForUpdate(String)\nSymbol :"
+ symbol);
}
PreparedStatement selectQuote = null;
try {
selectQuote = sqlConnection
.prepareStatement(SQL_SELECT_QUOTE_NOLOCK);
selectQuote.setString(1, symbol);
ResultSet rs = selectQuote.executeQuery();
try {
CustomQuoteBean quote = null;
if (rs.next()) {
quote = new CustomQuoteBean(rs.getString(1), rs
.getString(2), rs.getDouble(3),
rs.getBigDecimal(4), rs.getBigDecimal(5), rs
.getBigDecimal(6), rs.getBigDecimal(7), rs
.getDouble(8));
}
return quote;
} finally {
try {
rs.close();
} catch (SQLException e) {
logger.debug("", e);
}
}
} catch (SQLException e) {
throw new DAOException("", e);
} finally {
try {
if (selectQuote != null) {
selectQuote.close();
}
} catch (SQLException e) {
logger.debug("", e);
}
}
}
public CustomQuoteBean getQuoteForUpdate(String symbol) throws DAOException {
if (logger.isDebugEnabled()) {
logger.debug("MarketSummaryDAO.getQouteForUpdate(String)\nSymbol :"
+ symbol);
}
PreparedStatement qouteForUpdateStat = null;
try {
qouteForUpdateStat = sqlConnection
.prepareStatement(SQL_SELECT_QUOTE);
CustomQuoteBean quote = null;
qouteForUpdateStat.setString(1, symbol);
ResultSet rs = qouteForUpdateStat.executeQuery();
if (rs.next()) {
quote = new CustomQuoteBean(rs.getString(1), rs.getString(2),
rs.getDouble(3), rs.getBigDecimal(4), rs
.getBigDecimal(5), rs.getBigDecimal(6), rs
.getBigDecimal(7), rs.getDouble(8));
try {
rs.close();
} catch (SQLException e) {
logger.debug("", e);
}
return quote;
} else {
throw new DAOException("No quote entry found");
}
} catch (SQLException e) {
throw new DAOException("", e);
} finally {
try {
if (qouteForUpdateStat != null) {
qouteForUpdateStat.close();
}
} catch (SQLException e) {
logger.debug("", e);
}
}
}
public void updateStockPriceVolume(double quantity, CustomQuoteBean quote)
throws DAOException {
BigDecimal priceChangeFactor = StockTraderUtility
.getRandomPriceChangeFactor(quote.getPrice());
BigDecimal newPrice = quote.getPrice().multiply(priceChangeFactor);
if (newPrice.compareTo(quote.getLow()) == -1) {
quote.setLow(newPrice);
}
if (newPrice.compareTo(quote.getHigh()) == 1) {
quote.setHigh(newPrice);
}
PreparedStatement updateStockPriceVolumeStat = null;
try {
updateStockPriceVolumeStat = sqlConnection
.prepareStatement(SQL_UPDATE_STOCKPRICEVOLUME);
updateStockPriceVolumeStat.setBigDecimal(1, newPrice);
updateStockPriceVolumeStat.setBigDecimal(2, quote.getLow());
updateStockPriceVolumeStat.setBigDecimal(3, quote.getHigh());
updateStockPriceVolumeStat.setBigDecimal(4, newPrice);
updateStockPriceVolumeStat.setFloat(5, (float) quantity);
updateStockPriceVolumeStat.setString(6, quote.getSymbol());
updateStockPriceVolumeStat.executeUpdate();
} catch (SQLException e) {
throw new DAOException("", e);
} finally {
try {
if (updateStockPriceVolumeStat != null) {
updateStockPriceVolumeStat.close();
}
} catch (SQLException e) {
logger.debug("", e);
}
}
}
public CustomMarketSummaryBean getCustomMarketSummary() throws DAOException {
BigDecimal tSIA = (BigDecimal) StockTraderSQLUtil.executeScalarNoParm(
SQL_SELECT_MARKETSUMMARY_TSIA, sqlConnection);
BigDecimal openTSIA = (BigDecimal) StockTraderSQLUtil
.executeScalarNoParm(SQL_SELECT_MARKETSUMMARY_OPENTSIA,
sqlConnection);
double totalVolume = ((Double) StockTraderSQLUtil.executeScalarNoParm(
SQL_SELECT_MARKETSUMMARY_VOLUME, sqlConnection)).doubleValue();
List<CustomQuoteBean> topGainers = new ArrayList<CustomQuoteBean>();
PreparedStatement gainers = null;
try {
gainers = sqlConnection
.prepareStatement(SQL_SELECT_MARKETSUMMARY_GAINERS);
ResultSet rs = gainers.executeQuery();
try {
for (int i = 0; rs.next() && i < 5; i++) {
CustomQuoteBean quote = new CustomQuoteBean(
rs.getString(1), rs.getString(2), rs.getDouble(3),
rs.getBigDecimal(4), rs.getBigDecimal(5), rs
.getBigDecimal(6), rs.getBigDecimal(7), rs
.getDouble(8));
topGainers.add(quote);
}
} finally {
try {
rs.close();
} catch (SQLException e) {
logger.debug("", e);
}
}
} catch (SQLException e) {
throw new DAOException("", e);
} finally {
if (gainers != null) {
try {
gainers.close();
} catch (SQLException e) {
logger.debug("", e);
}
}
}
List<CustomQuoteBean> topLosers = new ArrayList<CustomQuoteBean>();
PreparedStatement losers = null;
try {
losers = sqlConnection
.prepareStatement(SQL_SELECT_MARKETSUMMARY_LOSERS);
ResultSet rs = losers.executeQuery();
try {
for (int i = 0; rs.next() && i < 5; i++) {
CustomQuoteBean quote = new CustomQuoteBean(
rs.getString(1), rs.getString(2), rs.getDouble(3),
rs.getBigDecimal(4), rs.getBigDecimal(5), rs
.getBigDecimal(6), rs.getBigDecimal(7), rs
.getDouble(8));
topLosers.add(quote);
}
} finally {
try {
rs.close();
} catch (SQLException e) {
logger.debug("", e);
}
}
} catch (SQLException e) {
throw new DAOException("", e);
} finally {
if (losers != null) {
try {
losers.close();
} catch (SQLException e) {
logger.debug("", e);
}
}
}
CustomMarketSummaryBean marketSummary = new CustomMarketSummaryBean(
tSIA, openTSIA, totalVolume, topGainers, topLosers);
return marketSummary;
}
}