/**
* 计算并保存遗漏值
*
* @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);
}
}