问题描述

跪求高手指教:将此JAVA方法改写成sqlserver存储过程,

/**
* 计算并保存遗漏值
*
* @CreateDate Dec 16, 2013 - 2:48:44 PM
* @param request
*/
@SuppressWarnings("unchecked")
public void saveOmitValue(HttpServletRequest request) {
// 获取当前日期
String date = DateUtils.formatDate(new Date());
// 获取前一天日期
String BeforeDay = DateUtils.getTheDayBeforeDay();
// 存储执行sql的变量
StringBuilder strBui = new StringBuilder(1000000);
// 查询方案一列中最大的中奖号码的排列号需要的map
Map map = new HashMap();
// 查询用sql
StringBuilder sql = new StringBuilder(4000);
// 查询用条件
StringBuilder searchCondition = new StringBuilder(4000);
// 获取当前登录的用户
User user = (User) request.getSession().getAttribute(Constants.USER_KEY);
// 获取开奖号对应的代码
String[] winningNoCode = request.getParameterValues("winningNoCode");
// 临时的前一天遗漏值
int temOmitValue = 0;
// 得到查询条件
for (int k = 0; k < winningNoCode.length; k++) {
if (k == 0) {
searchCondition.append("(B.NO_MSG = " + winningNoCode[k] + " AND A.SCHEME_SEQ = " + (k + 1) + ")");
}
searchCondition.append("OR(B.NO_MSG = " + winningNoCode[k] + " AND A.SCHEME_SEQ = " + (k + 1) + ")");
}
// 删除当天的遗漏值记录
sql.append("DELETE OMIT_VALUE_COORD WHERE BET_MODEL_ID='" + user.getBetModelId() + "' AND CREATE_DATE='" + date
+ "'");
Pwfci.SQLdeleteValue(sql.toString());
// 查询前100条方案
sql.setLength(0);
sql.append("select TOP 100 * from TWO_SCHEME_F WHERE DEL_FLAG = '1' AND BET_MODEL_ID = '"
+ user.getBetModelId() + "' ORDER BY ID");
List tsfs = (List) Pwfci.queryAllBySQL(sql.toString(), TwoSchemeF.class);

    for (TwoSchemeF tsf : tsfs) {
        System.out.println("计算遗漏值时间--Bengin       >>>>>>> "+DateUtils.formatDateTime(new Date()));
        // 和值方案遗漏值插入
        if (tsf.getType() == 1) {
            for (int j = 2; j < 112; j++) {
                // 查询方案一列中最大的中奖号码的排列号
                sql.setLength(0);
                sql.append("SELECT MAX(A.SCHEME_SEQ) AS MAX_COUNT "
                        + "FROM SCHEME_SEQ A LEFT JOIN IDENTIFY_NO B ON A.IDENTIFY_NO_XCOORD=B.XCOORD "
                        + "AND B.CODE = " + tsf.getType() + " AND B.YIDENTIFY = " + j + " WHERE A.PID = "
                        + tsf.getId() + " AND A.SCHEME_SEQ <= " + winningNoCode.length + " AND(");
                sql.append(searchCondition);
                sql.append(");");
                map = Pwfci.getMapBySQL(sql.toString());
                String maxCount = (String) map.get("MAX_COUNT");
                // 如果等于空说明当天此方案此列没有中奖,遗漏值为开奖期数加上前一天的遗漏值
                if (maxCount.equals("")) {
                    // 查询前一天的遗漏值
                    sql.setLength(0);
                    sql.append("SELECT OMIT_VALUE FROM OMIT_VALUE_COORD WHERE PID=" + tsf.getId()
                            + " AND YCOORD = " + j + " AND CREATE_DATE = '" + BeforeDay
                            + "' AND DEL_FLAG='1' AND BET_MODEL_ID='" + user.getBetModelId() + "'");
                    map = Pwfci.getMapBySQL(sql.toString());
                    temOmitValue = map.get("OMIT_VALUE") != null ? Integer.parseInt(map.get("OMIT_VALUE")
                            .toString()) : 0;
                    // 插入遗漏值
                    sql.setLength(0);
                    sql.append("INSERT INTO OMIT_VALUE_COORD(ID,OMIT_VALUE,PID,YCOORD,DEL_FLAG,"
                            + "CREATE_DATE,BET_MODEL_ID) VALUES('" + UUID.randomUUID() + "',"
                            + (winningNoCode.length + temOmitValue) + "," + tsf.getId() + "," + j + ",'1','" + date
                            + "','" + user.getBetModelId() + "');");
                    strBui.append(sql);
                } else {
                    // 如果不等于空,遗漏值为此排列号之后的总数
                    sql.setLength(0);
                    sql.append("INSERT INTO OMIT_VALUE_COORD(ID,OMIT_VALUE,PID,YCOORD,DEL_FLAG,"
                            + "CREATE_DATE,BET_MODEL_ID) " + "SELECT '" + UUID.randomUUID() + "',COUNT(ID),"
                            + tsf.getId() + "," + j + ",'1','" + date + "','" + user.getBetModelId()
                            + "' FROM SCHEME_SEQ WHERE PID = " + tsf.getId() + " AND SCHEME_SEQ <= "
                            + winningNoCode.length + " AND SCHEME_SEQ > " + maxCount + " ;");
                    strBui.append(sql);
                }
            }
        }
        // 差额方案遗漏值插入
        if (tsf.getType() == 0) {
            for (int j = 0; j < 110; j++) {
                // 查询方案一列中最大的中奖号码的排列号
                sql.setLength(0);
                sql.append("SELECT MAX(A.SCHEME_SEQ) AS MAX_COUNT "
                        + "FROM SCHEME_SEQ A LEFT JOIN IDENTIFY_NO B ON A.IDENTIFY_NO_XCOORD=B.XCOORD "
                        + "AND B.CODE = " + tsf.getType() + " AND B.YIDENTIFY = " + j + " WHERE A.PID = "
                        + tsf.getId() + " AND A.SCHEME_SEQ <= " + winningNoCode.length + " AND(");
                sql.append(searchCondition);
                sql.append(");");
                map = Pwfci.getMapBySQL(sql.toString());
                String maxCount = (String) map.get("MAX_COUNT");
                // 如果等于空说明当天此方案此列没有中奖,遗漏值为开奖期数加上前一天的遗漏值
                if (maxCount.equals("")) {
                    // 查询前一天的遗漏值
                    sql.setLength(0);
                    sql.append("SELECT OMIT_VALUE FROM OMIT_VALUE_COORD WHERE PID=" + tsf.getId()
                            + " AND YCOORD = " + j + " AND CREATE_DATE = '" + BeforeDay
                            + "' AND DEL_FLAG='1' AND BET_MODEL_ID='" + user.getBetModelId() + "'");
                    map = Pwfci.getMapBySQL(sql.toString());
                    temOmitValue = map.get("OMIT_VALUE") != null ? Integer.parseInt(map.get("OMIT_VALUE")
                            .toString()) : 0;
                    // 插入遗漏值
                    sql.setLength(0);
                    sql.append("INSERT INTO OMIT_VALUE_COORD(ID,OMIT_VALUE,PID,YCOORD,DEL_FLAG,"
                            + "CREATE_DATE,BET_MODEL_ID) VALUES('" + UUID.randomUUID() + "',"
                            + (winningNoCode.length + temOmitValue) + "," + tsf.getId() + "," + j + ",'1','" + date
                            + "','" + user.getBetModelId() + "');");
                    strBui.append(sql);
                } else {
                    // 如果不等于空,遗漏值为此排列号之后的总数
                    sql.setLength(0);
                    sql.append("INSERT INTO OMIT_VALUE_COORD(ID,OMIT_VALUE,PID,YCOORD,DEL_FLAG,"
                            + "CREATE_DATE,BET_MODEL_ID) " + "SELECT '" + UUID.randomUUID() + "',COUNT(ID),"
                            + tsf.getId() + "," + j + ",'1','" + date + "','" + user.getBetModelId()
                            + "' FROM SCHEME_SEQ WHERE PID = " + tsf.getId() + " AND SCHEME_SEQ <= "
                            + winningNoCode.length + " AND SCHEME_SEQ > " + maxCount);
                    strBui.append(sql);
                }
            }
        }
        System.out.println("计算遗漏值时间--End      >>>>>>> "+DateUtils.formatDateTime(new Date()));
        Pwfci.SQLinsertValue(strBui.toString());
        strBui.setLength(0);
    }
}