/**
* @param issueNumber
* @param fast3CountList
* @throws SQLException 四码预测插入预测计划方法
*/
private void insertData2Db(String issueNumber, List<Fast3Count> fast3CountList)
throws SQLException {
Connection conn = ConnectSrcDb.getSrcConnection();
String sql =
"insert into "
+ App.simaTbName
+ " (YUCE_ISSUE_START,YUCE_ISSUE_STOP,DROWN_PLAN,CREATE_TIME) values(?,?,?,?)";
String code1 = App.getNextIssueByCurrentIssue(issueNumber);
String code2 = App.getNextIssueByCurrentIssue(code1);
String code3 = App.getNextIssueByCurrentIssue(code2);
int[] numArr = {
fast3CountList.get(0).getNumber(),
fast3CountList.get(1).getNumber(),
fast3CountList.get(2).getNumber(),
fast3CountList.get(3).getNumber()
};
Arrays.sort(numArr);
PreparedStatement pstmt = (PreparedStatement) conn.prepareStatement(sql);
pstmt.setString(1, code1);
pstmt.setString(2, code3);
pstmt.setString(
3,
Integer.toString(numArr[0])
+ Integer.toString(numArr[1])
+ Integer.toString(numArr[2])
+ Integer.toString(numArr[3]));
pstmt.setTimestamp(4, new java.sql.Timestamp(new Date().getTime()));
pstmt.executeUpdate();
}
/** * @Description: 在源库中查找最新的预测计划，并保证期号在预测范围内，否则报错 * * @author [email protected] * @date Feb 15, 2016 3:29:13 PM * @return */ public Fast3SiMa getSiMaYuceRecordByIssueCode(String issueNumber) { Connection srcConn = ConnectSrcDb.getSrcConnection(); PreparedStatement pstmt = null; Fast3SiMa data = null; String sql = "SELECT ID,YUCE_ISSUE_START,YUCE_ISSUE_STOP,DROWN_PLAN,DROWN_CYCLE,DROWN_ISSUE_NUMBER FROM " + App.simaTbName + " WHERE " + issueNumber + " BETWEEN YUCE_ISSUE_START AND YUCE_ISSUE_STOP ORDER BY ID DESC LIMIT 1"; try { pstmt = (PreparedStatement) srcConn.prepareStatement(sql); ResultSet rs = pstmt.executeQuery(); while (rs.next()) { data = new Fast3SiMa(); data.setId(rs.getInt(1)); data.setYuceIssueStart(rs.getString(2)); data.setYuceIssueStop(rs.getString(3)); data.setDrownPlan(rs.getString(4)); data.setDrownCycle(rs.getInt(5)); data.setDrownIssueNumber(rs.getString(6)); } if (rs != null && !rs.isClosed()) { rs.close(); } } catch (SQLException e) { LogUtil.error(e.getMessage(), "sima"); } return data; }
/**
* @Description: 在源库中查找最新的期号
*
* @author songjia
* @date Feb 15, 2016 3:29:13 PM
* @return
*/
public SrcDataBean getRecordByIssueNumber(String issueNumber) {
Connection srcConn = ConnectSrcDb.getSrcConnection();
PreparedStatement pstmt = null;
SrcDataBean srcDataBean = null;
String sql =
"SELECT issue_number,no1,no2,no3 FROM "
+ App.srcNumberTbName
+ " WHERE ISSUE_NUMBER = '"
+ issueNumber
+ "'";
try {
pstmt = (PreparedStatement) srcConn.prepareStatement(sql);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
srcDataBean = new SrcDataBean();
srcDataBean.setIssueId(rs.getString(1));
srcDataBean.setNo1(rs.getInt(2));
srcDataBean.setNo2(rs.getInt(3));
srcDataBean.setNo3(rs.getInt(4));
}
if (rs != null && !rs.isClosed()) {
rs.close();
}
} catch (SQLException e) {
LogUtil.error(e.getMessage(), "sima");
}
return srcDataBean;
}
/**
* @param status
* @throws SQLException 更新胆码表状态内容
*/
private void updateDanMaStatus(Fast3SiMa fast3SiMa) throws SQLException {
Connection conn = ConnectSrcDb.getSrcConnection();
String sql =
"UPDATE "
+ App.simaTbName
+ " SET DROWN_ISSUE_NUMBER=?,DROWN_NUMBER=?,status = ?,DROWN_CYCLE=? where ID = ?";
// System.out.println(sql);
PreparedStatement pstmt = (PreparedStatement) conn.prepareStatement(sql);
pstmt.setString(1, fast3SiMa.getDrownIssueNumber());
pstmt.setString(2, fast3SiMa.getDrownNumber());
pstmt.setString(3, fast3SiMa.getStatus());
pstmt.setInt(4, fast3SiMa.getDrownCycle());
pstmt.setInt(5, fast3SiMa.getId());
pstmt.executeUpdate();
}
/** * @Description: 根据期号在源数据库中获取记录 * * @author [email protected] * @date Feb 15, 2016 4:24:40 PM * @param issueId * @return */ public List<SrcDataBean> getYucePool(String issueCode) { Connection srcConn = ConnectSrcDb.getSrcConnection(); List<SrcDataBean> srcList = new ArrayList<SrcDataBean>(); PreparedStatement pstmt = null; String startDay = DateUtil.getNextNDay(-7); // System.out.println("开始日期"+startDay); String endDay = DateUtil.getNextNDay(-1); // System.out.println("结束日期"+endDay); String code1 = DateUtil.getNextIssueCodeByCurrentIssue(issueCode); String code2 = DateUtil.getNextIssueCodeByCurrentIssue(code1); String code3 = DateUtil.getNextIssueCodeByCurrentIssue(code2); String sql = "SELECT issue_number,no1,no2,no3 FROM " + App.srcNumberTbName + " WHERE substr(ISSUE_NUMBER,1,6) between '" + startDay + "' and '" + endDay + "' AND substr(ISSUE_NUMBER,8) IN ('" + code1 + "','" + code2 + "','" + code3 + "') ORDER BY ISSUE_NUMBER DESC"; // System.out.println(sql); try { pstmt = (PreparedStatement) srcConn.prepareStatement(sql); ResultSet rs = pstmt.executeQuery(); while (rs.next()) { SrcDataBean srcDataBean = new SrcDataBean(); srcDataBean.setIssueId(rs.getString(1)); srcDataBean.setNo1(rs.getInt(2)); srcDataBean.setNo2(rs.getInt(3)); srcDataBean.setNo3(rs.getInt(4)); srcList.add(srcDataBean); } if (rs != null && !rs.isClosed()) { rs.close(); } } catch (SQLException e) { e.printStackTrace(); LogUtil.error(e.getMessage(), "sima/"); } // System.out.println(srcList.size()); return srcList; }
/** * @Description: 在源库中查找最新的期号 * * @author [email protected] * @date Feb 15, 2016 3:29:13 PM * @return */ public boolean hasRecordByIssueNumber(String issueNumber, String tbName) { Connection srcConn = ConnectSrcDb.getSrcConnection(); boolean flag = false; int count = 0; PreparedStatement pstmt = null; String sql = "SELECT count(*) count FROM " + tbName + " where issue_number = '" + issueNumber + "'"; try { pstmt = (PreparedStatement) srcConn.prepareStatement(sql); ResultSet rs = pstmt.executeQuery(); while (rs.next()) { count = rs.getInt(1); } if (count > 0) { flag = true; } if (rs != null && !rs.isClosed()) { rs.close(); } } catch (SQLException e) { LogUtil.error(e.getMessage(), "sima"); } return flag; }