/**
* 分页查询发电量评估纵向历史对比数据
*
* @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;
}分页查询发电量评估纵向历史对比数据(项目保留),布布扣,bubuko.com
原文：http://blog.csdn.net/sgx425021234/article/details/21462115