ｓｐｒｉｎｇ＋ＭＶＣ项目代码

package com.citigroup.gfi.tps.posbrk.service; import java.math.BigDecimal; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.text.ParseException; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Calendar; import java.util.Date; import java.util.HashMap; import java.util.List; import java.util.Locale; import java.util.Map; import java.util.Set; import java.util.TimeZone; import org.apache.commons.chain.Context; import org.apache.commons.lang.StringUtils; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; import org.springframework.dao.DataAccessException; import org.springframework.jdbc.core.PreparedStatementCallback; import org.springframework.jdbc.core.PreparedStatementSetter; import org.springframework.util.CollectionUtils; import weblogic.wsee.util.StringUtil; import com.citigroup.gfi.tps.core.dao.TradeDao; import com.citigroup.gfi.tps.core.dao.jdbc.AbstractOracleDao; import com.citigroup.gfi.tps.core.dao.jdbc.DomainObj2OracleObjConverter; import com.citigroup.gfi.tps.core.domain.TpsProcessDate; import com.citigroup.gfi.tps.core.domain.Trade; import com.citigroup.gfi.tps.core.utils.BusinessDateFactory; import com.citigroup.gfi.tps.posbrk.domain.PosbrkExcelItem; import com.citigroup.gfi.tps.posbrk.domain.PositionItem; import com.citigroup.gfi.tps.posbrk.domain.TradeKey; import com.citigroup.gfi.tps.posbrk.util.DateUtils; import com.citigroup.gfi.tps.posbrk.util.PosBrkConstants; import com.citigroup.gfi.tps.posbrk.util.PosBrkUtil; //import weblogic.descriptor.descriptorgen.interfacegen; public class PosBrkDaoImpl extends AbstractOracleDao implements PosBrkDao { private static final Log log = LogFactory.getLog(PosBrkDaoImpl.class); public static String QUERY_TPS_ROLL_DATES = "SELECT DISTINCT TO_CHAR(MODIFIED,'YYYY-MM-DD HH24:MI:SS') AS MODIFIED FROM TPSCORPPOS_M.VIEW_TPS_TD_POSITION_HISTORY WHERE FIRM_ACCOUNT=? AND CUSIP=? AND REASON='Position Roll' AND MODIFIED > TRUNC(SYSDATE - 10) ORDER BY MODIFIED DESC"; public static String QUERY_GTPL_LOAD_DATES = "SELECT DISTINCT TO_CHAR(MODIFIED,'YYYY-MM-DD HH24:MI:SS') AS MODIFIED FROM TPSCORPPOS_M.VIEW_TPS_TD_POSITION_HISTORY WHERE FIRM_ACCOUNT=? AND CUSIP=? AND POSITION_TYPE = 'GTPL' AND REASON = 'ETPL GTPL GMI LOADING' AND MODIFIED > TRUNC(SYSDATE - 10) ORDER BY MODIFIED DESC"; public static String QUERY_ALL_GTPL_POSITIONS = "SELECT DISTINCT TO_CHAR(MODIFIED, 'YYYY-MM-DD HH24:MI:SS') AS MODIFIED, (SOD_BUY_CURRENT_FACE - SOD_SELL_CURRENT_FACE + BUY_CURRENT_FACE - SELL_CURRENT_FACE) AS POSITION FROM TPSCORPPOS_M.VIEW_TPS_TD_POSITION_HISTORY WHERE FIRM_ACCOUNT = ? AND CUSIP =? AND POSITION_TYPE = 'GTPL' AND REASON = 'ETPL GTPL GMI LOADING' AND TO_CHAR(MODIFIED, 'YYYY-MM-DD') >= ? ORDER BY MODIFIED DESC "; public static String QUERY_ALL_POSITION_ITMES = "SELECT DISTINCT TO_CHAR(HIST.MODIFIED, 'YYYY-MM-DD HH24:MI:SS') AS MODIFIED, HIST.FIRM_ACCOUNT,HIST.CUSIP,HIST.POSITION_TYPE,HIST.REASON," + " (CASE WHEN DESK_NAME IN ('GOVT', 'SWAPS') AND POSITION_TYPE IN ('REGULAR') AND REASON IN ('Position Roll') THEN (SOD_BUY_ORIGINAL_FACE - SOD_SELL_ORIGINAL_FACE) WHEN DESK_NAME IN ('EM', 'EMEA Credit Flow', 'EMEA-ABS', 'EG','EU Rates', 'Private Products', 'CAD', 'MBS') AND POSITION_TYPE IN ('REGULAR') AND REASON IN ('Position Roll') THEN (SOD_BUY_CURRENT_FACE - SOD_SELL_CURRENT_FACE) ELSE (SOD_BUY_CURRENT_FACE - SOD_SELL_CURRENT_FACE + BUY_CURRENT_FACE - SELL_CURRENT_FACE)END) POSITION," + " HIST.EVENT_TYPE, HIST.TRADE_ID, HIST.VERSION, HIST.TRADE_QUANTITY AS TRADE_ORIG_QTY,HIST.BUY_SELL, HIST.CURR_CODE,HIST.FACTOR,TRADE.TRADE_PRICE, TRADE.BO_TRADE_NUM AS BO_TRADE_NUM,TRADE.TXN_REQ_ID AS TXN_REQ_ID, HIST.POSITION_VERSION FROM TPSCORPPOS_M.VIEW_TPS_TD_POSITION_HISTORY HIST," + " TPSCORP_M.TPS_TRADE TRADE WHERE HIST.TRADE_ID = TRADE.TRADE_ID(+) AND HIST.VERSION = TRADE.TRADE_VERSION(+) AND FIRM_ACCOUNT = ? AND CUSIP =? AND REASON IN ('Trading Position Update', 'Trading', 'Trading Activity','Position Roll', 'ETPL GTPL GMI LOADING') AND HIST.MODIFIED > TRUNC(SYSDATE - ?) ORDER BY POSITION_VERSION DESC, MODIFIED DESC "; public static String QUERY_ALL_TPS_POSITIONS = "SELECT DISTINCT TO_CHAR(MODIFIED, 'YYYY-MM-DD HH24:MI:SS') AS MODIFIED,(CASE WHEN DESK_NAME IN ('GOVT', 'SWAPS') THEN (SOD_BUY_ORIGINAL_FACE - SOD_SELL_ORIGINAL_FACE) WHEN DESK_NAME IN ('EM', 'EMEA Credit Flow', 'EMEA-ABS', 'EG','EU Rates', 'Private Products', 'CAD', 'MBS') THEN (SOD_BUY_CURRENT_FACE - SOD_SELL_CURRENT_FACE) ELSE (SOD_BUY_CURRENT_FACE - SOD_SELL_CURRENT_FACE + BUY_CURRENT_FACE - SELL_CURRENT_FACE)END) POSITION FROM TPSCORPPOS_M.VIEW_TPS_TD_POSITION_HISTORY " + " WHERE FIRM_ACCOUNT = ? AND CUSIP = ? AND POSITION_TYPE = 'REGULAR' AND REASON = 'Position Roll' AND TO_CHAR(MODIFIED, 'YYYY-MM-DD') >= ? ORDER BY MODIFIED DESC "; public static String QUERY_ALL_TPS_POSITIONS_A = "SELECT DISTINCT TO_CHAR(MODIFIED, 'YYYY-MM-DD HH24:MI:SS') AS MODIFIED,(CASE WHEN DESK_NAME IN ('GOVT', 'SWAPS') THEN (SOD_BUY_ORIGINAL_FACE - SOD_SELL_ORIGINAL_FACE) WHEN DESK_NAME IN ('EM', 'EMEA Credit Flow', 'EMEA-ABS', 'EG','EU Rates', 'Private Products', 'CAD', 'MBS') THEN (SOD_BUY_CURRENT_FACE - SOD_SELL_CURRENT_FACE) ELSE (SOD_BUY_CURRENT_FACE - SOD_SELL_CURRENT_FACE + BUY_CURRENT_FACE - SELL_CURRENT_FACE)END) POSITION FROM TPSCORPPOS_M.VIEW_TPS_TD_POSITION_HISTORY " + " WHERE FIRM_ACCOUNT = ? AND CUSIP = ? AND POSITION_TYPE = 'REGULAR' AND REASON in ( 'Trading Position Update','Trading','Trading Activity') AND TO_CHAR(MODIFIED, 'YYYY-MM-DD') >= ? ORDER BY MODIFIED DESC "; public static String QUERY_LATEST_TRADE_STATE_PARTA = "SELECT TRADE_ID, TRADE_VERSION,TRADE_STATE FROM TPSCORP_M.TPS_TRADE WHERE TRADE_ID IN ( "; public static String QUERY_LATEST_TRADE_STATE_PARTB = " ) ORDER BY TRADE_ID ASC, TRADE_VERSION ASC "; public static String QUERY_TRADE_ID_TXN_REQ_ID_PARTA = "SELECT BO_TRADE_NUM, BUY_SELL, TRADE_ID, TXN_REQ_ID FROM TPSCORP_M.TPS_TRADE WHERE BO_TRADE_NUM IN ("; public static String QUERY_TRADE_ID_TXN_REQ_ID_PARTB = ") AND TRADE_ORIGINATION_SIDE ='T' AND TO_CHAR(LASTMOD_TIMESTAMP, 'YYYY') >= '2012' ORDER BY TRADE_ID DESC "; public static String QUERY_SUB_BUSINESS_BY_ACCOUNT_PARTA = "SELECT ACCOUNT_MNEMONIC, SUB_BUSINESS FROM TPSCORP_M.TPS_ACCOUNT WHERE ACCOUNT_TYPE = 'FIRM' AND ACCOUNT_MNEMONIC IN ("; public static String QUERY_SUB_BUSINESS_BY_ACCOUNT_PARTB = "' ) AND SUB_BUSINESS IS NOT NULL "; public static String QUERY_TPS_POSITION_AFTER_BEGIN_DATE = "SELECT HIST.FIRM_ACCOUNT,HIST.EVENT_TYPE,HIST.BUY_SELL,HIST.CURR_CODE,HIST.CUSIP," + "HIST.TRADE_QUANTITY AS TRADE_QTY,HIST.TRADE_ID,HIST.MODIFIED,HIST.VERSION,HIST.FACTOR,TRADE.BO_TRADE_NUM AS BO_TRADE_NUM, TRADE.TXN_REQ_ID AS TXN_REQ_ID" + " FROM TPSCORPPOS_M.VIEW_TPS_TD_POSITION_HISTORY HIST, TPSCORP_M.TPS_TRADE TRADE WHERE HIST.TRADE_ID =TRADE.TRADE_ID(+) AND HIST.VERSION=TRADE.TRADE_VERSION(+) AND FIRM_ACCOUNT=? AND CUSIP=? AND REASON IN ( 'Trading Position Update','Trading','Trading Activity') AND MODIFIED > TO_DATE(?,'YYYY-MM-DD HH24:MI:SS') "; public static String QUERY_TPS_POSITION_BEFORE_END_DATE = "SELECT HIST.FIRM_ACCOUNT,HIST.EVENT_TYPE,HIST.BUY_SELL,HIST.CURR_CODE,HIST.CUSIP," + "HIST.TRADE_QUANTITY AS TRADE_QTY,HIST.TRADE_ID,HIST.MODIFIED,HIST.VERSION,HIST.FACTOR,TRADE.BO_TRADE_NUM AS BO_TRADE_NUM, TRADE.TXN_REQ_ID AS TXN_REQ_ID" + " FROM TPSCORPPOS_M.VIEW_TPS_TD_POSITION_HISTORY HIST, TPSCORP_M.TPS_TRADE TRADE WHERE HIST.TRADE_ID =TRADE.TRADE_ID(+) AND HIST.VERSION=TRADE.TRADE_VERSION(+) AND FIRM_ACCOUNT=? AND CUSIP=? AND REASON IN ( 'Trading Position Update','Trading','Trading Activity') AND HIST.MODIFIED < TO_DATE(?,'YYYY-MM-DD HH24:MI:SS') "; public static String QUERY_TPS_POSITION_WITHIN = "SELECT HIST.FIRM_ACCOUNT,HIST.EVENT_TYPE,HIST.BUY_SELL,HIST.CURR_CODE,HIST.CUSIP," + "HIST.TRADE_QUANTITY AS TRADE_QTY,HIST.TRADE_