分页查询发电量评估纵向历史对比数据(项目保留)

/** * 分页查询发电量评估纵向历史对比数据 *  * @param condition * @param currentPage * @param pageSize * @return */public ResultInfo getAllEnergyMeasuredSummaryDataList(EnergyExpectedSummaryData condition, int currentPage, int pageSize) {String sql = null;Pagination<EnergyExpectedSummaryData> pagination = null;if (condition.getPeriodType() == 1) {//mysql/*sql = "select c.updateTime updateTime ,b.yearNum,b.monthNum,b.dayNum ,"+ "round(a.dataValue,2) realDataValue,round(b.dataValue,2) dataValue,"+ "round(c.dataValue,2) deviationDataValue from tEnergyExpectedSummaryData b "+ "LEFT JOIN tEnergyMeasuredSummaryData a on a.stationID=b.stationID "+ "and a.turbineID=b.turbineID and a.yearNum=b.yearNum and a.monthNum=b.monthNum "+ "and a.dayNum=b.dayNum and a.timestamp=b.timestamp "+ "left join tBiasStat c "+ "on b.yearNum=c.yearNum and b.monthNum=c.monthNum and b.dayNum = c.dayNum "+ "and a.timestamp=b.timestamp and c.statItemID = 3 "+ "left join tBiasStatItem d "+ "on c.statItemID=d.statItemID and  b.stationID = d.stationID "+ "and b.schemeID = d.schemeID and b.turbineID = d.parentID and d.typeID=2 "+ "and d.biasTypeID=1 and d.elementID=3 and c.periodType=" + condition.getPeriodType() + " "+ "where b.stationID=? and b.turbineID=? and  b.schemeID= ? and b.dayNum<>0 and b.timestamp>?"+ "and b.timestamp<? order by b.timestamp";*///sqlserver/*sql = "select c.updateTime updateTime ,b.yearNum,b.monthNum,b.dayNum ,"+ "round(a.dataValue,2) realDataValue,round(b.dataValue,2) dataValue,"+ "round(c.dataValue,2) deviationDataValue from tEnergyExpectedSummaryData b "+ "LEFT JOIN tEnergyMeasuredSummaryData a on a.stationID=b.stationID "+ "and a.turbineID=b.turbineID and a.yearNum=b.yearNum and a.monthNum=b.monthNum "+ "and a.dayNum=b.dayNum and a.timestamp=b.timestamp "+ "left join tBiasStat c "+ "on b.yearNum=c.yearNum and b.monthNum=c.monthNum and b.dayNum = c.dayNum "+ "and a.timestamp=b.timestamp and c.statItemID = 3 "+ "left join tBiasStatItem d "+ "on c.statItemID=d.statItemID and  b.stationID = d.stationID "+ "and b.schemeID = d.schemeID and b.turbineID = d.parentID and d.typeID=2 "+ "and d.biasTypeID=1 and d.elementID=3 and c.periodType=" + condition.getPeriodType() + " "+ "where b.stationID=" + condition.getStationID() + " "+ "and b.turbineID=" + condition.getTurbineID() + " "+ "and  b.schemeID= " + condition.getSchemeID() + " and b.dayNum<>0 "+ "and b.timestamp>'" + condition.getQueryStartData() + "' "+ "and b.timestamp<'" + condition.getQueryEndData() + "' order by b.timestamp";*/sql = "select c.updateTime updateTime ,b.yearNum,b.monthNum,b.dayNum ,"+ "round(a.dataValue,2) realDataValue,round(b.dataValue,2) dataValue,"+ "round(c.dataValue,2) deviationDataValue from tEnergyExpectedSummaryData b "+ "LEFT JOIN tEnergyMeasuredSummaryData a on a.stationID=b.stationID "+ "and a.turbineID=b.turbineID and a.yearNum=b.yearNum and a.monthNum=b.monthNum "+ "and a.dayNum=b.dayNum and a.timestamp=b.timestamp "+ "left join tBiasStat c "+ "on b.yearNum=c.yearNum and b.monthNum=c.monthNum and b.dayNum = c.dayNum "+ "and a.timestamp=b.timestamp and c.statItemID = 3 "+ "left join tBiasStatItem d "+ "on c.statItemID=d.statItemID and  b.stationID = d.stationID "+ "and b.schemeID = d.schemeID and b.turbineID = d.parentID and d.typeID=2 "+ "and d.biasTypeID=1 and d.elementID=3 and c.periodType=? "+ "where b.stationID=? and b.turbineID=? and  b.schemeID=? and b.dayNum<>0 "+ "and b.timestamp>=to_date(?,'YYYY-MM-DD HH24:MI:SS') "+ "and b.timestamp<to_date(?,'YYYY-MM-DD HH24:MI:SS') order by b.timestamp";pagination = new Pagination<EnergyExpectedSummaryData>(sql,currentPage, pageSize, jdbcTemplate,new DAORowMapper<EnergyExpectedSummaryData>(EnergyExpectedSummaryData.class),new Object[]{condition.getPeriodType(),condition.getStationID(),condition.getTurbineID(),condition.getSchemeID(),condition.getQueryStartData(),condition.getQueryEndData()});} else if (condition.getPeriodType() == 2) {//mysql/*sql = "select c.updateTime updateTime ,b.yearNum,b.monthNum,b.dayNum ,"+ "round(a.dataValue,2) realDataValue,round(b.dataValue,2) dataValue,"+ "round(c.dataValue,2) deviationDataValue from tEnergyExpectedSummaryData b "+ "LEFT JOIN tEnergyMeasuredSummaryData a on a.stationID=b.stationID "+ "and a.turbineID=b.turbineID and a.yearNum=b.yearNum and a.monthNum=b.monthNum "+ "and a.dayNum=b.dayNum and a.timestamp=b.timestamp left join tBiasStat c "+ "on b.yearNum=c.yearNum and b.monthNum=c.monthNum and b.dayNum = c.dayNum "+ "and a.timestamp=b.timestamp  and c.statItemID = 3 left join  tBiasStatItem d "+ "on c.statItemID=d.statItemID and  b.stationID = d.stationID "+ "and b.schemeID = d.schemeID and b.turbineID = d.parentID and d.typeID=2 "+ "and d.biasTypeID=1 and d.elementID=3 and c.periodType= ? "+ "where b.stationID = ? and b.turbineID=? and  b.schemeID=? and b.dayNum=0 "+ "and b.timestamp>? and b.timestamp<? order by b.timestamp";*///sql server/*sql = "select c.updateTime updateTime ,b.yearNum,b.monthNum,b.dayNum ,"+ "round(a.dataValue,2) realDataValue,round(b.dataValue,2) dataValue,"+ "round(c.dataValue,2) deviationDataValue from tEnergyExpectedSummaryData b "+ "LEFT JOIN tEnergyMeasuredSummaryData a on a.stationID=b.stationID "+ "and a.turbineID=b.turbineID and a.yearNum=b.yearNum and a.monthNum=b.monthNum "+ "and a.dayNum=b.dayNum and a.timestamp=b.timestamp left join tBiasStat c "+ "on b.yearNum=c.yearNum and b.monthNum=c.monthNum and b.dayNum = c.dayNum "+ "and a.timestamp=b.timestamp  and c.statItemID = 3 left join  tBiasStatItem d "+ "on c.statItemID=d.statItemID and  b.stationID = d.stationID "+ "and b.schemeID = d.schemeID and b.turbineID = d.parentID and d.typeID=2 "+ "and d.biasTypeID=1 and d.elementID=3 and c.periodType= " + condition.getPeriodType() +" "+ "where b.stationID = " + condition.getStationID() + " "+ "and b.turbineID=" + condition.getTurbineID() + " "+ "and  b.schemeID=" + condition.getSchemeID() + " and b.dayNum=0 "+ "and b.timestamp>'" + condition.getQueryStartData() + "' "+ "and b.timestamp<'" + condition.getQueryEndData() + "' order by b.timestamp";*/sql = "select c.updateTime updateTime ,b.yearNum,b.monthNum,b.dayNum ,"+ "round(a.dataValue,2) realDataValue,round(b.dataValue,2) dataValue,"+ "round(c.dataValue,2) deviationDataValue from tEnergyExpectedSummaryData b "+ "LEFT JOIN tEnergyMeasuredSummaryData a on a.stationID=b.stationID "+ "and a.turbineID=b.turbineID and a.yearNum=b.yearNum and a.monthNum=b.monthNum "+ "and a.dayNum=b.dayNum and a.timestamp=b.timestamp left join tBiasStat c "+ "on b.yearNum=c.yearNum and b.monthNum=c.monthNum and b.dayNum = c.dayNum "+ "and a.timestamp=b.timestamp  and c.statItemID = 3 left join  tBiasStatItem d "+ "on c.statItemID=d.statItemID and  b.stationID = d.stationID "+ "and b.schemeID = d.schemeID and b.turbineID = d.parentID and d.typeID=2 "+ "and d.biasTypeID=1 and d.elementID=3 and c.periodType= ? "+ "where b.stationID = ? and b.turbineID=? and  b.schemeID=? and b.dayNum=0 "+ "and b.timestamp>=to_date(?,'YYYY-MM-DD HH24:MI:SS') "+ "and b.timestamp<to_date(?,'YYYY-MM-DD HH24:MI:SS') order by b.timestamp";pagination = new Pagination<EnergyExpectedSummaryData>(sql,currentPage, pageSize, jdbcTemplate,new DAORowMapper<EnergyExpectedSummaryData>(EnergyExpectedSummaryData.class),new Object[]{condition.getPeriodType(),condition.getStationID(),condition.getTurbineID(),condition.getSchemeID(),condition.getQueryStartData(),condition.getQueryEndData()});}pagination.query();ResultInfo ri = new ResultInfo(true);ri.setTotal(pagination.getTotalRows());ri.setRows(pagination.getResultList());return ri;}