|
package com.ekexiu.portal.dao;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import org.jfw.apt.annotation.Autowrie;
import org.jfw.apt.annotation.Nullable;
import org.jfw.apt.orm.annotation.dao.DAO;
import org.jfw.apt.orm.annotation.dao.method.From;
import org.jfw.apt.orm.annotation.dao.method.operator.DeleteWith;
import org.jfw.apt.orm.annotation.dao.method.operator.Insert;
import org.jfw.apt.orm.annotation.dao.method.operator.Update;
import org.jfw.apt.orm.annotation.dao.method.operator.UpdateWith;
import org.jfw.apt.orm.annotation.dao.param.Set;
import org.jfw.util.PageQueryResult;
import com.ekexiu.portal.po.Image;
import com.ekexiu.portal.po.Operation;
import com.ekexiu.portal.po.Professor;
import com.ekexiu.portal.po.Resource;
@DAO
public abstract class OperationDao {
@Autowrie
private ImageDao imageDao;
public ImageDao getImageDao() {
return imageDao;
}
public void setImageDao(ImageDao imageDao) {
this.imageDao = imageDao;
}
@Insert
public abstract int insert(Connection con, Operation operation) throws SQLException;
@Update
public abstract int update(Connection con, Operation operation) throws SQLException;
@UpdateWith
@From(Operation.class)
public abstract int updateDetailDemand(Connection con, String operationId, @Set String detailDemand) throws SQLException;
@UpdateWith
@From(Operation.class)
public abstract int updateOperationStatus(Connection con, String operationId, @Set int operationStatus) throws SQLException;
@UpdateWith
@From(Operation.class)
public abstract int updateDeliverTime(Connection con, String operationId, @Set String deliverTime) throws SQLException;
@UpdateWith
@From(Operation.class)
public abstract int updateDealTime(Connection con, String operationId, @Set String dealTime) throws SQLException;
@UpdateWith
@From(Operation.class)
public abstract int updatePayMethod(Connection con, String operationId, @Set String payMethod) throws SQLException;
/**
* 查询有多少资源合作正在进行
* @param con
* @param professorId 登陆者ID
* @return 返回正在合作的资源申请数量
* @throws SQLException
*/
public int queryOnGoingOper(Connection con, @Nullable String professorId) throws SQLException {
String sql = " SELECT COUNT(1) FROM OPERATION O WHERE (OPERATION_STATUS IN (1,2,3,7)) AND (O.RESOURCE_ID IN (SELECT R.RESOURCE_ID FROM RESOURCE R WHERE R.PROFESSOR_ID = ?) OR O.DEMAND_ID = ?) ";
PreparedStatement ps = con.prepareStatement(sql);
ps.setString(1, professorId);
ps.setString(2, professorId);
ResultSet rs = ps.executeQuery();
rs.next();
int count = rs.getInt(1);
return count;
}
public Operation query(Connection con,String operationId) throws SQLException{
int _m_1 = 1;
String sql = "SELECT REPLY_NOTES,OPERATION.CREATE_TIME,OPERATION_ID,OPERATION_STATUS,DEMAND_ID,DETAIL_DEMAND,DELIVER_TIME,OPERATION.RESOURCE_ID,DEAL_PRICE,DEAL_TIME,PAY_METHOD,P1.NAME,RESOURCE_NAME,RESOURCE.PROFESSOR_ID,P2.NAME,APPLYSQUARE FROM OPERATION INNER JOIN PROFESSOR P1 ON DEMAND_ID = P1.ID INNER JOIN RESOURCE ON OPERATION.RESOURCE_ID = RESOURCE.RESOURCE_ID INNER JOIN PROFESSOR P2 ON RESOURCE.PROFESSOR_ID = P2.ID WHERE OPERATION_ID = ?";
PreparedStatement ps = con.prepareStatement(sql);
try{
ps.setString(_m_1++,operationId);
ResultSet rs = ps.executeQuery();
try{
if(rs.next()){
Operation _result = new Operation();
_result.setReplyNotes(rs.getString(1));
_result.setCreateTime(rs.getString(2));
_result.setOperationId(rs.getString(3));
_result.setOperationStatus(rs.getInt(4));
Professor professor = new Professor();
professor.setId(rs.getString(5));
_result.setDetailDemand(rs.getString(6));
_result.setDeliverTime(rs.getString(7));
Resource resource = new Resource();
resource.setResourceId(rs.getString(8));
_result.setDealPrice(rs.getBigDecimal(9));
_result.setDealTime(rs.getString(10));
_result.setPayMethod(rs.getString(11));
professor.setName(rs.getString(12));
_result.setProfessor(professor);
resource.setResourceName(rs.getString(13));
Professor professor2 = new Professor();
professor2.setId(rs.getString(14));
professor2.setName(rs.getString(15));
resource.setProfessor(professor2);
_result.setResource(resource);
_result.setApplysquare(rs.getString(16));
return _result;
}else{
return null;
}
}finally{
try{rs.close();}catch(Exception _m_2){}
}
}finally{
try{ps.close();}catch(Exception _m_3){}
}
}
public List<Operation> query(Connection con) throws SQLException{
String sql = "SELECT REPLY_NOTES,OPERATION.CREATE_TIME,OPERATION_ID,OPERATION_STATUS,DEMAND_ID,DETAIL_DEMAND,DELIVER_TIME,OPERATION.RESOURCE_ID,DEAL_PRICE,DEAL_TIME,PAY_METHOD,P1.NAME,RESOURCE_NAME,RESOURCE.PROFESSOR_ID,P2.NAME,APPLYSQUARE FROM OPERATION INNER JOIN PROFESSOR P1 ON DEMAND_ID = P1.ID INNER JOIN RESOURCE ON OPERATION.RESOURCE_ID = RESOURCE.RESOURCE_ID INNER JOIN PROFESSOR P2 ON RESOURCE.PROFESSOR_ID = P2.ID";
PreparedStatement ps = con.prepareStatement(sql);
try{
ResultSet rs = ps.executeQuery();
try{
List<Operation> _result = new ArrayList<Operation>();
while(rs.next()){
Operation _obj = new Operation();
_obj.setReplyNotes(rs.getString(1));
_obj.setCreateTime(rs.getString(2));
_obj.setOperationId(rs.getString(3));
_obj.setOperationStatus(rs.getInt(4));
Professor professor = new Professor();
professor.setId(rs.getString(5));
_obj.setDetailDemand(rs.getString(6));
_obj.setDeliverTime(rs.getString(7));
Resource resource = new Resource();
resource.setResourceId(rs.getString(8));
_obj.setDealPrice(rs.getBigDecimal(9));
_obj.setDealTime(rs.getString(10));
_obj.setPayMethod(rs.getString(11));
professor.setName(rs.getString(12));
_obj.setProfessor(professor);
resource.setResourceName(rs.getString(13));
Professor professor2 = new Professor();
professor2.setId(rs.getString(14));
professor2.setName(rs.getString(15));
resource.setProfessor(professor2);
_obj.setResource(resource);
_obj.setApplysquare(rs.getString(16));
_result.add(_obj);
}
return _result;
}finally{
try{rs.close();}catch(Exception _m_1){}
}
}finally{
try{ps.close();}catch(Exception _m_2){}
}
}
// /**
// * 按资源请求者ID查资源申请
// * @param con
// * @param demandId 请求者ID
// * @return
// * @throws SQLException
// */
// public List<Operation> queryDem(Connection con,String demandId) throws SQLException{
// int _m_1 = 1;
// String sql = "SELECT REPLY_NOTES,OPERATION.CREATE_TIME,OPERATION_ID,OPERATION_STATUS,DEMAND_ID,DETAIL_DEMAND,DELIVER_TIME,OPERATION.RESOURCE_ID,DEAL_PRICE,DEAL_TIME,PAY_METHOD,P1.NAME,RESOURCE_NAME,RESOURCE.PROFESSOR_ID,P2.NAME,APPLYSQUARE FROM OPERATION INNER JOIN PROFESSOR P1 ON DEMAND_ID = P1.ID INNER JOIN RESOURCE ON OPERATION.RESOURCE_ID = RESOURCE.RESOURCE_ID INNER JOIN PROFESSOR P2 ON RESOURCE.PROFESSOR_ID = P2.ID WHERE DEMAND_ID = ?";
// PreparedStatement ps = con.prepareStatement(sql);
// try{
// ps.setString(_m_1++,demandId);
// ResultSet rs = ps.executeQuery();
// try{
// List<Operation> _result = new ArrayList<Operation>();
// while(rs.next()){
// Operation _obj = new Operation();
// _obj.setReplyNotes(rs.getString(1));
// _obj.setCreateTime(rs.getString(2));
// _obj.setOperationId(rs.getString(3));
// _obj.setOperationStatus(rs.getInt(4));
// Professor professor = new Professor();
// professor.setId(rs.getString(5));
// _obj.setDetailDemand(rs.getString(6));
// _obj.setDeliverTime(rs.getString(7));
// Resource resource = new Resource();
// resource.setResourceId(rs.getString(8));
// _obj.setDealPrice(rs.getBigDecimal(9));
// _obj.setDealTime(rs.getString(10));
// _obj.setPayMethod(rs.getString(11));
// professor.setName(rs.getString(12));
// _obj.setProfessor(professor);
// resource.setResourceName(rs.getString(13));
// Professor professor2 = new Professor();
// professor2.setId(rs.getString(14));
// professor2.setName(rs.getString(15));
// resource.setProfessor(professor2);
// _obj.setResource(resource);
// _obj.setApplysquare(rs.getString(16));
// _result.add(_obj);
// }
// return _result;
// }finally{
// try{rs.close();}catch(Exception _m_2){}
// }
// }finally{
// try{ps.close();}catch(Exception _m_3){}
// }
// }
public PageQueryResult<Operation> queryPro(Connection con, @Nullable String id,int pageSize,int pageNo) throws SQLException{
int _m_1 = 0;
PageQueryResult<Operation> _result = new PageQueryResult<Operation>();
int _m_3 = 1;
boolean _m_2 = null == id;
StringBuilder sql = new StringBuilder();
boolean _m_5 = true;
if(!_m_2){
_m_5 = false;
sql.append(" WHERE O.RESOURCE_ID IN (SELECT R.RESOURCE_ID FROM RESOURCE R WHERE R.PROFESSOR_ID = ?)");
}
if(!_m_2){
if(_m_5){
_m_5 = false;
sql.append(" WHERE (O.DEMAND_ID = ?) ");
}else{
sql.append(" OR (O.DEMAND_ID = ?) ");
}
}
StringBuilder _m_4 = sql;
sql = new StringBuilder();
sql.append(" SELECT COUNT(1) FROM OPERATION O ");
if(_m_4.length()>0){
sql.append(_m_4);
}
PreparedStatement ps = con.prepareStatement(sql.toString());
try{
if(!_m_2){
ps.setString(_m_3++,id);
}
if(!_m_2){
ps.setString(_m_3++,id);
}
_result.setPageSize(pageSize);
ResultSet _pageRs = ps.executeQuery();
try{
_pageRs.next();
_m_1 = _pageRs.getInt(1);
}finally{
try{_pageRs.close();}catch(Exception _m_6){}
}
}finally{
try{ps.close();}catch(Exception _m_7){}
}
_result.setTotal(_m_1);
if(0== _m_1){
_result.setPageNo(1);
_result.setData(Collections.<Operation>emptyList());
return _result;
}
_m_3 = 1;
boolean _m_8 = (1 == pageNo);
if(_m_8){
_result.setPageNo(1);
sql = new StringBuilder();
sql.append("SELECT REPLY_NOTES,APPLYSQUARE,OPERATION_ID,OPERATION_STATUS,DEMAND_ID,DETAIL_DEMAND,DELIVER_TIME,O.RESOURCE_ID,DEAL_PRICE,DEAL_TIME,PAY_METHOD,O.CREATE_TIME,R.RESOURCE_NAME,SUPPORTED_SERVICES,R.DESCP,R.PROFESSOR_ID,P1.NAME,P2.NAME FROM OPERATION O LEFT JOIN RESOURCE R ON O.RESOURCE_ID = R.RESOURCE_ID LEFT JOIN PROFESSOR P1 ON DEMAND_ID = P1.ID LEFT JOIN PROFESSOR P2 ON R.PROFESSOR_ID = P2.ID");
if(_m_4.length()>0){
sql.append(_m_4);
}
sql.append(" ORDER BY O.CREATE_TIME DESC ");
sql.append(" LIMIT ").append(pageSize);
}else{
int _m_9 = _m_1 / pageSize;
if(_m_1 % pageSize != 0){
++_m_9;
}
if(pageNo > _m_9){
pageNo = _m_9;
}
_result.setPageNo(pageNo);
--pageNo;
int _m_10 = (pageNo * pageSize);
sql = new StringBuilder();
sql.append("SELECT REPLY_NOTES,APPLYSQUARE,OPERATION_ID,OPERATION_STATUS,DEMAND_ID,DETAIL_DEMAND,DELIVER_TIME,O.RESOURCE_ID,DEAL_PRICE,DEAL_TIME,PAY_METHOD,O.CREATE_TIME,R.RESOURCE_NAME,SUPPORTED_SERVICES,R.DESCP,R.PROFESSOR_ID,P1.NAME,P2.NAME FROM OPERATION O LEFT JOIN RESOURCE R ON O.RESOURCE_ID = R.RESOURCE_ID LEFT JOIN PROFESSOR P1 ON DEMAND_ID = P1.ID LEFT JOIN PROFESSOR P2 ON R.PROFESSOR_ID = P2.ID");
if(_m_4.length()>0){
sql.append(_m_4);
}
sql.append(" ORDER BY O.CREATE_TIME DESC ");
sql.append(" LIMIT ").append(pageSize).append(" OFFSET ").append(_m_10);
}
ps = con.prepareStatement(sql.toString());
try{
if(!_m_2){
ps.setString(_m_3++,id);
}
if(!_m_2){
ps.setString(_m_3++,id);
}
ResultSet rs = ps.executeQuery();
try{
List<Operation> _m_11 = new ArrayList<Operation>();
_result.setData(_m_11);
int _m_12 = 0;
while((_m_12<pageSize) && rs.next()){
++_m_12;
Operation _obj = new Operation();
String _m_13 = rs.getString(1);
if(rs.wasNull()){
_m_13 = null;
}
_obj.setReplyNotes(_m_13);
String _m_14 = rs.getString(2);
if(rs.wasNull()){
_m_14 = null;
}
_obj.setApplysquare(_m_14);
_obj.setOperationId(rs.getString(3));
_obj.setOperationStatus(rs.getInt(4));
Professor professor = new Professor();
professor.setId(rs.getString(5));
_obj.setDetailDemand(rs.getString(6));
_obj.setDeliverTime(rs.getString(7));
Resource resource = new Resource();
resource.setResourceId(rs.getString(8));
java.math.BigDecimal _m_15 = rs.getBigDecimal(9);
if(rs.wasNull()){
_m_15 = null;
}
_obj.setDealPrice(_m_15);
String _m_16 = rs.getString(10);
if(rs.wasNull()){
_m_16 = null;
}
_obj.setDealTime(_m_16);
String _m_17 = rs.getString(11);
if(rs.wasNull()){
_m_17 = null;
}
_obj.setPayMethod(_m_17);
_obj.setCreateTime(rs.getString(12));
resource.setResourceName(rs.getString(13));
resource.setSupportedServices(rs.getString(14));
resource.setDescp(rs.getString(15));
Professor professor2 = new Professor();
professor2.setId(rs.getString(16));
List<Image> images = this.imageDao.queryRes(con, _obj.getResourceId());
resource.setImages(images);
professor.setName(rs.getString(17));
_obj.setProfessor(professor);
professor2.setName(rs.getString(18));
resource.setProfessor(professor2);
_obj.setResource(resource);
_m_11.add(_obj);
}
return _result;
}finally{
try{rs.close();}catch(Exception _m_18){}
}
}finally{
try{ps.close();}catch(Exception _m_19){}
}
}
/**
* 分页按资源申请者ID查资源申请
* @param con
* @param demandId 资源申请者ID
* @param pageSize 每页记录数
* @param pageNo 当前页码
* @return 返回该用户ID申请的所有资源
* @throws SQLException
*/
public PageQueryResult<Operation> queryDemPage(Connection con,String demandId,int pageSize,int pageNo) throws SQLException{
int _m_1 = 0;
PageQueryResult<Operation> _result = new PageQueryResult<Operation>();
int _m_2 = 1;
String sql = null;
sql = " WHERE OPERATION.DEMAND_ID = ? ";
String _m_3 = sql;
sql = "SELECT COUNT(1) FROM OPERATION";
sql = sql + _m_3;
PreparedStatement ps = con.prepareStatement(sql);
try{
ps.setString(_m_2++,demandId);
_result.setPageSize(pageSize);
ResultSet _pageRs = ps.executeQuery();
try{
_pageRs.next();
_m_1 = _pageRs.getInt(1);
}finally{
try{_pageRs.close();}catch(Exception _m_4){}
}
}finally{
try{ps.close();}catch(Exception _m_5){}
}
_result.setTotal(_m_1);
if(0== _m_1){
_result.setPageNo(1);
_result.setData(Collections.<Operation>emptyList());
return _result;
}
_m_2 = 1;
boolean _m_6 = (1 == pageNo);
if(_m_6){
_result.setPageNo(1);
sql = "SELECT REPLY_NOTES,OPERATION.CREATE_TIME,APPLYSQUARE,OPERATION_ID,OPERATION_STATUS,DEMAND_ID,DETAIL_DEMAND,DELIVER_TIME,OPERATION.RESOURCE_ID,DEAL_PRICE,DEAL_TIME,PAY_METHOD,P1.NAME,RESOURCE_NAME,RESOURCE.PROFESSOR_ID,P2.NAME FROM OPERATION INNER JOIN PROFESSOR P1 ON DEMAND_ID = P1.ID INNER JOIN RESOURCE ON OPERATION.RESOURCE_ID = RESOURCE.RESOURCE_ID INNER JOIN PROFESSOR P2 ON RESOURCE.PROFESSOR_ID = P2.ID";
sql = sql + _m_3;
sql = sql + "ORDER BY OPERATION.CREATE_TIME DESC";
sql = sql + " LIMIT " + pageSize;
}else{
int _m_7 = _m_1 / pageSize;
if(_m_1 % pageSize != 0){
++_m_7;
}
if(pageNo > _m_7){
pageNo = _m_7;
}
_result.setPageNo(pageNo);
--pageNo;
int _m_8 = (pageNo * pageSize);
sql = "SELECT REPLY_NOTES,OPERATION.CREATE_TIME,APPLYSQUARE,OPERATION_ID,OPERATION_STATUS,DEMAND_ID,DETAIL_DEMAND,DELIVER_TIME,OPERATION.RESOURCE_ID,DEAL_PRICE,DEAL_TIME,PAY_METHOD,P1.NAME,RESOURCE_NAME,RESOURCE.PROFESSOR_ID,P2.NAME FROM OPERATION INNER JOIN PROFESSOR P1 ON DEMAND_ID = P1.ID INNER JOIN RESOURCE ON OPERATION.RESOURCE_ID = RESOURCE.RESOURCE_ID INNER JOIN PROFESSOR P2 ON RESOURCE.PROFESSOR_ID = P2.ID";
sql = sql + _m_3;
sql = sql + "ORDER BY OPERATION.CREATE_TIME DESC";
sql = sql + " LIMIT " + pageSize+ " OFFSET "+_m_8;
}
ps = con.prepareStatement(sql);
try{
ps.setString(_m_2++,demandId);
ResultSet rs = ps.executeQuery();
try{
List<Operation> _m_9 = new ArrayList<Operation>();
_result.setData(_m_9);
int _m_10 = 0;
while((_m_10<pageSize) && rs.next()){
++_m_10;
Operation _obj = new Operation();
String _m_11 = rs.getString(1);
if(rs.wasNull()){
_m_11 = null;
}
_obj.setReplyNotes(_m_11);
_obj.setCreateTime(rs.getString(2));
String _m_12 = rs.getString(3);
if(rs.wasNull()){
_m_12 = null;
}
_obj.setApplysquare(_m_12);
_obj.setOperationId(rs.getString(4));
_obj.setOperationStatus(rs.getInt(5));
Professor professor = new Professor();
professor.setId(rs.getString(6));
_obj.setDetailDemand(rs.getString(7));
_obj.setDeliverTime(rs.getString(8));
Resource resource = new Resource();
resource.setResourceId(rs.getString(9));
BigDecimal _m_13 = rs.getBigDecimal(10);
if(rs.wasNull()){
_m_13 = null;
}
_obj.setDealPrice(_m_13);
String _m_14 = rs.getString(11);
if(rs.wasNull()){
_m_14 = null;
}
_obj.setDealTime(_m_14);
String _m_15 = rs.getString(12);
if(rs.wasNull()){
_m_15 = null;
}
_obj.setPayMethod(_m_15);
professor.setName(rs.getString(13));
_obj.setProfessor(professor);
resource.setResourceName(rs.getString(14));
Professor professor2 = new Professor();
professor2.setId(rs.getString(15));
professor2.setName(rs.getString(16));
resource.setProfessor(professor2);
_obj.setResource(resource);
_m_9.add(_obj);
}
return _result;
}finally{
try{rs.close();}catch(Exception _m_16){}
}
}finally{
try{ps.close();}catch(Exception _m_17){}
}
}
/**
* 分页按资源ID查资源申请，返回资申请人姓名和资源名称
* @param con
* @param resourceId 资源ID
* @param pageSize 每页记录数
* @param pageNo 当前页码
* @return 返回当前资源ID下的所有资源申请记录
* @throws SQLException
*/
public PageQueryResult<Operation> queryResPage(Connection con,String resourceId,int pageSize,int pageNo) throws SQLException{
int _m_1 = 0;
PageQueryResult<Operation> _result = new PageQueryResult<Operation>();
int _m_2 = 1;
String sql = null;
sql = " WHERE OPERATION.RESOURCE_ID = ? ";
String _m_3 = sql;
sql = "SELECT COUNT(1) FROM OPERATION";
sql = sql + _m_3;
PreparedStatement ps = con.prepareStatement(sql);
try{
ps.setString(_m_2++,resourceId);
_result.setPageSize(pageSize);
ResultSet _pageRs = ps.executeQuery();
try{
_pageRs.next();
_m_1 = _pageRs.getInt(1);
}finally{
try{_pageRs.close();}catch(Exception _m_4){}
}
}finally{
try{ps.close();}catch(Exception _m_5){}
}
_result.setTotal(_m_1);
if(0== _m_1){
_result.setPageNo(1);
_result.setData(Collections.<Operation>emptyList());
return _result;
}
_m_2 = 1;
boolean _m_6 = (1 == pageNo);
if(_m_6){
_result.setPageNo(1);
sql = "SELECT REPLY_NOTES,OPERATION.CREATE_TIME,APPLYSQUARE,OPERATION.OPERATION_ID,OPERATION_STATUS,DEMAND_ID,DETAIL_DEMAND,DELIVER_TIME,OPERATION.RESOURCE_ID,DEAL_PRICE,DEAL_TIME,PAY_METHOD,P1.NAME,RESOURCE_NAME,RESOURCE.PROFESSOR_ID,P2.NAME FROM OPERATION INNER JOIN PROFESSOR P1 ON DEMAND_ID = P1.ID INNER JOIN RESOURCE ON OPERATION.RESOURCE_ID = RESOURCE.RESOURCE_ID INNER JOIN PROFESSOR P2 ON RESOURCE.PROFESSOR_ID = P2.ID ";
sql = sql + _m_3;
sql = sql + "ORDER BY OPERATION.CREATE_TIME DESC";
sql = sql + " LIMIT " + pageSize;
}else{
int _m_7 = _m_1 / pageSize;
if(_m_1 % pageSize != 0){
++_m_7;
}
if(pageNo > _m_7){
pageNo = _m_7;
}
_result.setPageNo(pageNo);
--pageNo;
int _m_8 = (pageNo * pageSize);
sql = "SELECT REPLY_NOTES,OPERATION.CREATE_TIME,APPLYSQUARE,OPERATION.OPERATION_ID,OPERATION_STATUS,DEMAND_ID,DETAIL_DEMAND,DELIVER_TIME,OPERATION.RESOURCE_ID,DEAL_PRICE,DEAL_TIME,PAY_METHOD,P1.NAME,RESOURCE_NAME,RESOURCE.PROFESSOR_ID,P2.NAME FROM OPERATION INNER JOIN PROFESSOR P1 ON DEMAND_ID = P1.ID INNER JOIN RESOURCE ON OPERATION.RESOURCE_ID = RESOURCE.RESOURCE_ID INNER JOIN PROFESSOR P2 ON RESOURCE.PROFESSOR_ID = P2.ID";
sql = sql + _m_3;
sql = sql + "ORDER BY OPERATION.CREATE_TIME DESC";
sql = sql + " LIMIT " + pageSize+ " OFFSET "+_m_8;
}
ps = con.prepareStatement(sql);
try{
ps.setString(_m_2++,resourceId);
ResultSet rs = ps.executeQuery();
try{
List<Operation> _m_9 = new ArrayList<Operation>();
_result.setData(_m_9);
int _m_10 = 0;
while((_m_10<pageSize) && rs.next()){
++_m_10;
Operation _obj = new Operation();
String _m_11 = rs.getString(1);
if(rs.wasNull()){
_m_11 = null;
}
_obj.setReplyNotes(_m_11);
_obj.setCreateTime(rs.getString(2));
String _m_12 = rs.getString(3);
if(rs.wasNull()){
_m_12 = null;
}
_obj.setApplysquare(_m_12);
_obj.setOperationId(rs.getString(4));
_obj.setOperationStatus(rs.getInt(5));
Professor professor = new Professor();
professor.setId(rs.getString(6));
_obj.setDetailDemand(rs.getString(7));
_obj.setDeliverTime(rs.getString(8));
Resource resource = new Resource();
resource.setResourceId(rs.getString(9));
BigDecimal _m_13 = rs.getBigDecimal(10);
if(rs.wasNull()){
_m_13 = null;
}
_obj.setDealPrice(_m_13);
String _m_14 = rs.getString(11);
if(rs.wasNull()){
_m_14 = null;
}
_obj.setDealTime(_m_14);
String _m_15 = rs.getString(12);
if(rs.wasNull()){
_m_15 = null;
}
_obj.setPayMethod(_m_15);
professor.setName(rs.getString(13));
_obj.setProfessor(professor);
resource.setResourceName(rs.getString(14));
Professor professor2 = new Professor();
professor2.setId(rs.getString(15));
professor2.setName(rs.getString(16));
resource.setProfessor(professor2);
_obj.setResource(resource);
_m_9.add(_obj);
}
return _result;
}finally{
try{rs.close();}catch(Exception _m_16){}
}
}finally{
try{ps.close();}catch(Exception _m_17){}
}
}
// /**
// * 按资源ID查资源申请，返回资申请人姓名和资源名称
// * @param con
// * @param resourceId 资源ID
// * @return
// * @throws SQLException
// */
// public List<Operation> queryRes(Connection con,String resourceId) throws SQLException{
// int _m_1 = 1;
// String sql = "SELECT REPLY_NOTES,OPERATION.CREATE_TIME,OPERATION_ID,OPERATION_STATUS,DEMAND_ID,DETAIL_DEMAND,DELIVER_TIME,OPERATION.RESOURCE_ID,DEAL_PRICE,DEAL_TIME,PAY_METHOD,P1.NAME,RESOURCE_NAME,RESOURCE.PROFESSOR_ID,P2.NAME,APPLYSQUARE FROM OPERATION INNER JOIN PROFESSOR P1 ON DEMAND_ID = P1.ID INNER JOIN RESOURCE ON OPERATION.RESOURCE_ID = RESOURCE.RESOURCE_ID INNER JOIN PROFESSOR P2 ON RESOURCE.PROFESSOR_ID = P2.ID WHERE OPERATION.RESOURCE_ID = ?";
// PreparedStatement ps = con.prepareStatement(sql);
// try{
// ps.setString(_m_1++,resourceId);
// ResultSet rs = ps.executeQuery();
// try{
// List<Operation> _result = new ArrayList<Operation>();
// while(rs.next()){
// Operation _obj = new Operation();
// _obj.setReplyNotes(rs.getString(1));
// _obj.setCreateTime(rs.getString(2));
// _obj.setOperationId(rs.getString(3));
// _obj.setOperationStatus(rs.getInt(4));
// Professor professor = new Professor();
// professor.setId(rs.getString(5));
// _obj.setDetailDemand(rs.getString(6));
// _obj.setDeliverTime(rs.getString(7));
// Resource resource = new Resource();
// resource.setResourceId(rs.getString(8));
// _obj.setDealPrice(rs.getBigDecimal(9));
// _obj.setDealTime(rs.getString(10));
// _obj.setPayMethod(rs.getString(11));
// professor.setName(rs.getString(12));
// _obj.setProfessor(professor);
// resource.setResourceName(rs.getString(13));
// Professor professor2 = new Professor();
// professor2.setId(rs.getString(14));
// professor2.setName(rs.getString(15));
// resource.setProfessor(professor2);
// _obj.setResource(resource);
// _obj.setApplysquare(rs.getString(16));
// _result.add(_obj);
// }
// return _result;
// }finally{
// try{rs.close();}catch(Exception _m_2){}
// }
// }finally{
// try{ps.close();}catch(Exception _m_3){}
// }
// }
@DeleteWith
@From(Operation.class)
public abstract int delete(Connection con, String operationId) throws SQLException;
@DeleteWith
@From(Operation.class)
public abstract int deleteProRes(Connection con, String demandId, String resourceId) throws SQLException;
/**
* 分页查询资源申请 多条件模糊查询
* @param con
* @param professorName 申请人姓名
* @param resourceName 资源名称
* @param firstTime 申请时间 查询初试时间
* @param lastTime 申请时间 查询结束时间
* @param lowPrice 成交价格 查询较低价格
* @param highPrice 成交时间 查询较高价格
* @param pageSize 每页记录数
* @param pageNo 当前页码
* @return
* @throws SQLException
*/
public PageQueryResult<Operation> queryPage(Connection con,String professorName,String resourceName,String firstTime,String lastTime,java.math.BigDecimal lowPrice,java.math.BigDecimal highPrice,int pageSize,int pageNo) throws SQLException{
int _m_1 = 0;
PageQueryResult<Operation> _result = new PageQueryResult<Operation>();
int _m_3 = 1;
boolean _m1 = null == professorName;
boolean _m2 = null == resourceName;
boolean _m3 = null == firstTime;
boolean _m4 = null == lastTime;
boolean _m5 = null == lowPrice;
boolean _m6 = null == highPrice;
StringBuilder sql = new StringBuilder();
boolean _m_5 = true;
if(!_m1){
_m_5 = false;
sql.append(" WHERE DEMAND_ID (SELECT PROFESSOR.PROFESSOR.ID FROM PROFESSOR WHERE PROFESSOR.NAME LIKE ?)");
}
if(!_m2){
if(_m_5){
_m_5 = false;
sql.append(" WHERE OPERATION.RESOURCE_ID IN (SELECT RESOURCE.RESOURCE_ID FROM RESOURCE WHERE RESOURCE_NAME LIKE ?)");
}else{
sql.append(" OR OPERATION.RESOURCE_ID IN (SELECT RESOURCE.RESOURCE_ID FROM RESOURCE WHERE RESOURCE_NAME LIKE ?)");
}
}
if(!_m3){
if(_m_5){
_m_5 = false;
sql.append(" WHERE OPERATION.CREATE_TIME > ?");
}else{
sql.append(" AND OPERATION.CREATE_TIME > ?");
}
}
if(!_m4){
if(_m_5){
_m_5 = false;
sql.append(" WHERE OPERATION.CREATE_TIME <= ?");
}else{
sql.append(" AND OPERATION.CREATE_TIME <= ?");
}
}
if(!_m5){
if(_m_5){
_m_5 = false;
sql.append(" WHERE DEAL_PRICE > ?");
}else{
sql.append(" AND DEAL_PRICE > ?");
}
}
if(!_m6){
if(_m_5){
_m_5 = false;
sql.append(" WHERE DEAL_PRICE <= ?");
}else{
sql.append(" AND DEAL_PRICE <= ?");
}
}
StringBuilder _m_4 = sql;
sql = new StringBuilder();
sql.append("SELECT COUNT(1) FROM OPERATION");
if(_m_4.length()>0){
sql.append(_m_4);
}
PreparedStatement ps = con.prepareStatement(sql.toString());
try{
if(!_m1){
ps.setString(_m_3++,professorName);
}
if(!_m2){
ps.setString(_m_3++,resourceName);
}
if(!_m3){
ps.setString(_m_3++,firstTime);
}
if(!_m4){
ps.setString(_m_3++,lastTime);
}
if(!_m5){
ps.setBigDecimal(_m_3++,lowPrice);
}
if(!_m6){
ps.setBigDecimal(_m_3++,highPrice);
}
_result.setPageSize(pageSize);
ResultSet _pageRs = ps.executeQuery();
try{
_pageRs.next();
_m_1 = _pageRs.getInt(1);
}finally{
try{_pageRs.close();}catch(Exception _m_6){}
}
}finally{
try{ps.close();}catch(Exception _m_7){}
}
_result.setTotal(_m_1);
if(0== _m_1){
_result.setPageNo(1);
_result.setData(Collections.<Operation>emptyList());
return _result;
}
_m_3 = 1;
boolean _m_8 = (1 == pageNo);
if(_m_8){
_result.setPageNo(1);
sql = new StringBuilder();
sql.append("SELECT REPLY_NOTES,OPERATION.CREATE_TIME,OPERATION_ID,OPERATION_STATUS,DEMAND_ID,DETAIL_DEMAND,DELIVER_TIME,OPERATION.RESOURCE_ID,DEAL_PRICE,DEAL_TIME,PAY_METHOD,P1.NAME,RESOURCE_NAME,RESOURCE.PROFESSOR_ID,P2.NAME,APPLYSQUARE FROM OPERATION LEFT JOIN PROFESSOR P1 ON DEMAND_ID = P1.ID LEFT JOIN RESOURCE ON OPERATION.RESOURCE_ID = RESOURCE.RESOURCE_ID LEFT JOIN PROFESSOR P2 ON RESOURCE.PROFESSOR_ID = P2.ID");
if(_m_4.length()>0){
sql.append(_m_4);
}
sql.append(" ORDER BY OPERATION.CREATE_TIME");
sql.append(" LIMIT ").append(pageSize);
}else{
int _m_9 = _m_1 / pageSize;
if(_m_1 % pageSize != 0){
++_m_9;
}
if(pageNo > _m_9){
pageNo = _m_9;
}
_result.setPageNo(pageNo);
--pageNo;
int _m_10 = (pageNo * pageSize);
sql = new StringBuilder();
sql.append("SELECT REPLY_NOTES,OPERATION.CREATE_TIME,OPERATION_ID,OPERATION_STATUS,DEMAND_ID,DETAIL_DEMAND,DELIVER_TIME,OPERATION.RESOURCE_ID,DEAL_PRICE,DEAL_TIME,PAY_METHOD,P1.NAME,RESOURCE_NAME,RESOURCE.PROFESSOR_ID,P2.NAME,APPLYSQUARE FROM OPERATION LEFT JOIN PROFESSOR P1 ON DEMAND_ID = P1.ID LEFT JOIN RESOURCE ON OPERATION.RESOURCE_ID = RESOURCE.RESOURCE_ID LEFT JOIN PROFESSOR P2 ON RESOURCE.PROFESSOR_ID = P2.ID");
if(_m_4.length()>0){
sql.append(_m_4);
}
sql.append(" ORDER BY OPERATION.CREATE_TIME");
sql.append(" LIMIT ").append(pageSize).append(" OFFSET ").append(_m_10);
}
ps = con.prepareStatement(sql.toString());
try{
if(!_m1){
ps.setString(_m_3++,professorName);
}
if(!_m2){
ps.setString(_m_3++,resourceName);
}
if(!_m3){
ps.setString(_m_3++,firstTime);
}
if(!_m4){
ps.setString(_m_3++,lastTime);
}
if(!_m5){
ps.setBigDecimal(_m_3++,lowPrice);
}
if(!_m6){
ps.setBigDecimal(_m_3++,highPrice);
}
ResultSet rs = ps.executeQuery();
try{
List<Operation> _m_11 = new ArrayList<Operation>();
_result.setData(_m_11);
int _m_12 = 0;
while((_m_12<pageSize) && rs.next()){
++_m_12;
Operation _obj = new Operation();
_obj.setReplyNotes(rs.getString(1));
_obj.setCreateTime(rs.getString(2));
_obj.setOperationId(rs.getString(3));
_obj.setOperationStatus(rs.getInt(4));
Professor professor = new Professor();
professor.setId(rs.getString(5));
_obj.setDetailDemand(rs.getString(6));
_obj.setDeliverTime(rs.getString(7));
Resource resource = new Resource();
resource.setResourceId(rs.getString(8));
_obj.setDealPrice(rs.getBigDecimal(9));
_obj.setDealTime(rs.getString(10));
_obj.setPayMethod(rs.getString(11));
professor.setName(rs.getString(12));
_obj.setProfessor(professor);
resource.setResourceName(rs.getString(13));
Professor professor2 = new Professor();
professor2.setId(rs.getString(14));
professor2.setName(rs.getString(15));
resource.setProfessor(professor2);
_obj.setResource(resource);
_obj.setApplysquare(rs.getString(16));
_m_11.add(_obj);
}
return _result;
}finally{
try{rs.close();}catch(Exception _m_13){}
}
}finally{
try{ps.close();}catch(Exception _m_14){}
}
}
}
|
