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.OrderBy;
import org.jfw.apt.orm.annotation.dao.method.operator.Insert;
import org.jfw.apt.orm.annotation.dao.method.operator.SelectList;
import org.jfw.apt.orm.annotation.dao.method.operator.SelectOne;
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.Consult;
import com.ekexiu.portal.pojo.EditProfessor;

@DAO
public abstract class ConsultDao {
	@Autowrie
	private ProfessorDao professorDao;
	
	public ProfessorDao getProfessorDao() {
		return professorDao;
	}

	public void setProfessorDao(ProfessorDao professorDao) {
		this.professorDao = professorDao;
	}

	@Insert
	public abstract int insert(Connection con, Consult consult) throws SQLException;
	
	@UpdateWith
	@From(Consult.class)
	public abstract int updateFinishTime(Connection con,String consultId,@Set String finishTime)throws SQLException;
	
	@UpdateWith
	@From(Consult.class)
	public abstract int updateConsultStatus(Connection con,String consultId,@Set Integer consultStatus)throws SQLException;
	
	public int updateReadStatus(Connection con,String consultId) throws SQLException{
        int _m_1 = 1;
        String sql ="UPDATE CONSULT SET READ_STATUS = 1 WHERE CONSULT_ID = ? ";
        PreparedStatement ps = con.prepareStatement(sql);
        try{
            ps.setString(_m_1++,consultId);
            return ps.executeUpdate();
        }finally{
            try{ps.close();}catch(Exception _m_2){}
        }
    }
	
	/**
	 * 更新完成时间和完成状态
	 * @param con 
	 * @param consultId 咨询ID
	 * @param consultStatus 咨询状态
	 * @return 
	 * @throws SQLException
	 */
	public int updateFinishTime(Connection con,String consultId,int consultStatus) throws SQLException{
        int _m_2 = 1;
        String sql ="UPDATE CONSULT SET FINISH_TIME= TO_CHAR(NOW(),'YYYYMMDDHH24MISS'),CONSULT_STATUS= ? WHERE CONSULT_ID = ?";
        PreparedStatement ps = con.prepareStatement(sql);
        try{
        	ps.setInt(_m_2++, consultStatus);
            ps.setString(_m_2++,consultId);
            return ps.executeUpdate();
        }finally{
            try{ps.close();}catch(Exception _m_3){}
        }
    }
	
	public int updateRevoveryTime(Connection con,String consultId) throws SQLException{
        int _m_1 = 1;
        String sql ="UPDATE CONSULT SET REVOVERY_TIME= TO_CHAR(NOW(),'YYYYMMDDHH24MISS') WHERE CONSULT_ID = ?";
        PreparedStatement ps = con.prepareStatement(sql);
        try{
            ps.setString(_m_1++,consultId);
            return ps.executeUpdate();
        }finally{
            try{ps.close();}catch(Exception _m_2){}
        }
    }
	
	public int updateAssess(Connection con, String consultId, int assessStatus, int assessStar, String assessContant) throws SQLException{
        int _m_2 = 1;
        boolean _m_3 = null == assessContant;
        String sql ="UPDATE CONSULT SET ASSESS_TIME= TO_CHAR(NOW(),'YYYYMMDDHH24MISS'),ASSESS_CONTANT= ?,ASSESS_STAR= ?,ASSESS_STATUS= ? WHERE CONSULT_ID = ?";
        PreparedStatement ps = con.prepareStatement(sql);
        try{
            if(_m_3){
                ps.setNull(_m_2++,12);
            }else{
                ps.setString(_m_2++,assessContant);
            }
            ps.setInt(_m_2++,assessStar);
            ps.setInt(_m_2++, assessStatus);
            ps.setString(_m_2++,consultId);
            return ps.executeUpdate();
        }finally{
            try{ps.close();}catch(Exception _m_4){}
        }
    }
	
	public int updateThanks(Connection con,String consultId,int thanksStatus,BigDecimal thanksMoney) throws SQLException{
        int _m_2 = 1;
        boolean _m_3 = null == thanksMoney;
        String sql ="UPDATE CONSULT SET THANKS_TIME= TO_CHAR(NOW(),'YYYYMMDDHH24MISS'),THANKS_MONEY= ?,THANKS_STATUS= ? WHERE CONSULT_ID = ?";
        PreparedStatement ps = con.prepareStatement(sql);
        try{
            if(_m_3){
                ps.setNull(_m_2++,3);
            }else{
                ps.setBigDecimal(_m_2++,thanksMoney);
            }
            ps.setInt(_m_2++,thanksStatus);
            ps.setString(_m_2++,consultId);
            return ps.executeUpdate();
        }finally{
            try{ps.close();}catch(Exception _m_4){}
        }
    }
	
	/**
	 * 通过需求ID和专家ID查询咨询表中是否有该专家接受需求的咨询记录
	 * @param con
	 * @param professorId 专家ID
	 * @param demandId 需求ID
	 * @return 若有记录返回咨询ID，否则返回null
	 * @throws SQLException
	 */
	public String queryDemand(Connection con,String professorId,String demandId) throws SQLException{
        int index = 1;
        String sql = "SELECT CONSULT_ID FROM CONSULT WHERE DEMAND_ID = ? AND PROFESSOR_ID = ?";
        PreparedStatement ps = con.prepareStatement(sql);
        try{
            ps.setString(index++,demandId);
            ps.setString(index++,professorId);
            ResultSet rs = ps.executeQuery();
            try{
                if(rs.next()){
                    return rs.getString(1);
                }else{
                    return null;
                }
            }finally{
                try{rs.close();}catch(Exception e1){}
            }
        }finally{
            try{ps.close();}catch(Exception e2){}
        }
    }
	
	/**
	 * 查询当前用户接受咨询被评价的平均星级
	 * @param con
	 * @param professorId 用户ID
	 * @return 返回用户被评价的平均星级
	 * @throws SQLException
	 */
	public BigDecimal queryStarLevel(Connection con, String professorId) throws SQLException {
		int _m_2 = 1;
		String sql = " SELECT AVG(ASSESS_STAR) FROM CONSULT WHERE ASSESS_STATUS = 1 AND PROFESSOR_ID = ? ";
		PreparedStatement ps = con.prepareStatement(sql);
		try {
			ps.setString(_m_2++, professorId);
			ResultSet rs = ps.executeQuery();
			try{
				rs.next();
				BigDecimal avg = rs.getBigDecimal(1);
				return avg;
			}finally{
                try{rs.close();}catch(Exception _m_3){}
            }
		} finally{
            try{ps.close();}catch(Exception _m_4){}
        }
	}
	
	/**
	 * 查询专家合作历史及评价接口
	 * @param con
	 * @param professorId 专家ID
	 * @param pageSize
	 * @param pageNo
	 * @return 返回所有已评价的咨询及评价详情
	 * @throws SQLException
	 */
	public PageQueryResult<Consult> queryAssessHis(Connection con,String professorId,int pageSize,int pageNo) throws SQLException{
        int _m_1 = 0;
        PageQueryResult<Consult> _result = new PageQueryResult<Consult>();
        int _m_2 = 1;
        String sql = null;
        sql = "  WHERE PROFESSOR_ID = ? AND ASSESS_STATUS = 1 ";
        String _m_3 = sql;
        sql = "SELECT COUNT(1) FROM CONSULT";
        sql = sql + _m_3;
        PreparedStatement ps = con.prepareStatement(sql);
        try{
            ps.setString(_m_2++,professorId);
            _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(java.util.Collections.<Consult>emptyList());
            return _result;
        }
        _m_2 = 1;
        boolean _m_6 = (1 == pageNo);
        if(_m_6){
            _result.setPageNo(1);
            sql = "SELECT CONSULT_ID,CONSULT_TITLE,CONSULTANT_ID,ASSESS_STAR,ASSESS_CONTANT,ASSESS_TIME,CREATE_TIME FROM CONSULT";
            sql = sql + _m_3;
            sql = sql + " ORDER BY ASSESS_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 CONSULT_ID,CONSULT_TITLE,CONSULTANT_ID,ASSESS_STAR,ASSESS_CONTANT,ASSESS_TIME,CREATE_TIME FROM CONSULT";
            sql = sql + _m_3;
            sql = sql + " ORDER BY ASSESS_TIME DESC ";
            sql = sql + " LIMIT " + pageSize+ " OFFSET "+_m_8;
        }
        ps = con.prepareStatement(sql);
        try{
            ps.setString(_m_2++,professorId);
            ResultSet rs = ps.executeQuery();
            try{
                java.util.List<Consult> _m_9 = new java.util.ArrayList<Consult>();
                _result.setData(_m_9);
                int _m_10 = 0;
                while((_m_10<pageSize) && rs.next()){
                    ++_m_10;
                    Consult consult =  new Consult();
                    consult.setConsultId(rs.getString(1));
                    consult.setConsultTitle(rs.getString(2));
                    consult.setConsultantId(rs.getString(3));
                    consult.setAssessStar(rs.getInt(4));
                    consult.setAssessContant(rs.getString(5));
                    consult.setAssessTime(rs.getString(6));
                    consult.setCreateTime(rs.getString(7));
                    _m_9.add(consult);
                }
                return _result;
            }finally{
                try{rs.close();}catch(Exception _m_11){}
            }
        }finally{
            try{ps.close();}catch(Exception _m_12){}
        }
    }
	
	@SelectOne
	@Nullable
	public abstract Consult query(Connection con, String consultId) throws SQLException;
	
	@SelectList
	@OrderBy("ORDER BY CREATE_TIME DESC")
	public abstract List<Consult> query(Connection con) throws SQLException;
	
	/**
	 * 统计未查看的咨询条数
	 * @param con
	 * @param professorId 登陆者ID
	 * @return 返回未查看的资源条数
	 * @throws SQLException
	 */
	public int queryReadStatus(Connection con, String professorId) throws SQLException {
		int _m_2 = 1;
		String sql = " SELECT COUNT(1) FROM CONSULT WHERE READ_STATUS = 0 AND PROFESSOR_ID = ? ";
		PreparedStatement ps = con.prepareStatement(sql);
		try {
			ps.setString(_m_2++, professorId);
			ResultSet rs = ps.executeQuery();
			try{
				rs.next();
				int count = rs.getInt(1);
				return count;
			}finally{
                try{rs.close();}catch(Exception _m_3){}
            }
		} finally{
            try{ps.close();}catch(Exception _m_4){}
        }
	}
	
	/**
	 * 统计所有未查看的消息记录（包括咨询别人和别人咨询自己的所有未读消息）
	 * @param con
	 * @param professorId 登陆者ID
	 * @return 返回所有未查看的消息条数
	 * @throws SQLException
	 */
	public int queryNotReadReply(Connection con, String professorId) throws SQLException {
		int _m_2 = 1;
		String sql = " SELECT COUNT(1) FROM TIDINGS "
				+ " INNER JOIN CONSULT ON TIDINGS.CONSULT_ID = CONSULT.CONSULT_ID AND (TIDINGS.READ_STATUS = 0) "
				+ " AND (CONSULT.CONSULTANT_ID = ? OR CONSULT.PROFESSOR_ID = ?) AND (TIDINGS.SENDER_ID != ?) ";
		PreparedStatement ps = con.prepareStatement(sql);
		try {
			ps.setString(_m_2++, professorId);
			ps.setString(_m_2++, professorId);
			ps.setString(_m_2++, professorId);
			ResultSet rs = ps.executeQuery();
			try{
				rs.next();
				int count = rs.getInt(1);
				return count;
			}finally{
                try{rs.close();}catch(Exception _m_3){}
            }
		} finally{
            try{ps.close();}catch(Exception _m_4){}
        }
	}
	
	/**
	 * 我的工作台中显示接受咨询的总次数(已完成状态)
	 * @param con
	 * @param professorId 登陆者ID
	 * @return 返回接受咨询已完成的总次数
	 * @throws SQLException
	 */
	public int queryReceiveConsult(Connection con, String professorId) throws SQLException {
		int _m_2 = 1;
		String sql = " SELECT COUNT(1) FROM CONSULT WHERE (CONSULT_STATUS = 1) AND (PROFESSOR_ID = ?) ";
		PreparedStatement ps = con.prepareStatement(sql);
		try {
			ps.setString(_m_2++, professorId);
			ResultSet rs = ps.executeQuery();
			try{
				rs.next();
				int count = rs.getInt(1);
				return count;
			}finally{
                try{rs.close();}catch(Exception _m_3){}
            }
		} finally{
            try{ps.close();}catch(Exception _m_4){}
        }
	}
	
	/**
	 * 我的工作台最新咨询
	 * @param con
	 * @param professorId 专家ID
	 * @return 返回最新未读咨询
	 * @throws SQLException
	 */
	public Consult queryNewConsult(Connection con, String professorId) throws SQLException {
		int m1 = 1;
		String sql = " SELECT CONSULT_ID,CONSULT_TYPE,CONSULT_TITLE,CONSULT_CONTANT,"
				+ "CONSULT.CREATE_TIME,CONSULT.PROFESSOR_ID,CONSULTANT_ID,READ_STATUS"
				+ " FROM CONSULT WHERE CONSULT.CREATE_TIME "
				+ " IN (SELECT MAX(CREATE_TIME) FROM CONSULT WHERE READ_STATUS = 0 AND PROFESSOR_ID = ?) ";
		PreparedStatement ps = con.prepareStatement(sql);
		try{
			ps.setString(m1++, professorId);
			ResultSet rs = ps.executeQuery();
			try{
				if(rs.next()){
					Consult consult = new Consult();
					consult.setConsultId(rs.getString(1));
					consult.setConsultType(rs.getString(2));
					consult.setConsultTitle(rs.getString(3));
					consult.setConsultContant(rs.getString(4));
					consult.setCreateTime(rs.getString(5));
					consult.setProfessorId(rs.getString(6));
					consult.setConsultantId(rs.getString(7));
					consult.setReadStatus(rs.getInt(8));
					return consult;
				}else{
					return null;
				}
			}finally{
                try{rs.close();}catch(Exception _m_7){}
            }
        }finally{
            try{ps.close();}catch(Exception _m_8){}
        }
	}
	
	public Consult queryPro(Connection con,String consultId) throws SQLException{
        int _m_1 = 1;
        String sql = "SELECT CONSULT_ID,CONSULT_TYPE,CONSULT_TITLE,CONSULT_CONTANT,PROFESSOR_ID,CONSULTANT_ID,"
        		+ " CONSULT_STATUS,FINISH_TIME,ASSESS_STATUS,ASSESS_STAR,ASSESS_CONTANT,ASSESS_TIME,THANKS_STATUS,"
        		+ " THANKS_MONEY,THANKS_TIME,REVOVERY_TIME,CONSULT.READ_STATUS,CONSULT.CREATE_TIME,PROFESSOR.NAME "
        		+ " FROM CONSULT LEFT JOIN PROFESSOR ON CONSULT.CONSULTANT_ID = PROFESSOR.ID WHERE CONSULT_ID = ?";
        PreparedStatement ps = con.prepareStatement(sql);
        try{
            ps.setString(_m_1++,consultId);
            ResultSet rs = ps.executeQuery();
            try{
                if(rs.next()){
                    Consult _result = new Consult();
                    _result.setConsultId(rs.getString(1));
                    _result.setConsultType(rs.getString(2));
                    _result.setConsultTitle(rs.getString(3));
                    _result.setConsultContant(rs.getString(4));
                    _result.setProfessorId(rs.getString(5));
                    EditProfessor professor = new EditProfessor();
                    professor.setId(rs.getString(6));
                    _result.setConsultStatus(rs.getInt(7));
                    String _m_2 = rs.getString(8);
                    if(rs.wasNull()){
                        _m_2 = null;
                    }
                    _result.setFinishTime(_m_2);
                    _result.setAssessStatus(rs.getInt(9));
                    _result.setAssessStar(rs.getInt(10));
                    String _m_3 = rs.getString(11);
                    if(rs.wasNull()){
                        _m_3 = null;
                    }
                    _result.setAssessContant(_m_3);
                    String _m_4 = rs.getString(12);
                    if(rs.wasNull()){
                        _m_4 = null;
                    }
                    _result.setAssessTime(_m_4);
                    _result.setThanksStatus(rs.getInt(13));
                    BigDecimal _m_5 = rs.getBigDecimal(14);
                    if(rs.wasNull()){
                        _m_5 = null;
                    }
                    _result.setThanksMoney(_m_5);
                    String _m_6 = rs.getString(15);
                    if(rs.wasNull()){
                        _m_6 = null;
                    }
                    _result.setThanksTime(_m_6);
                    _result.setRevoveryTime(rs.getString(16));
                    _result.setReadStatus(rs.getInt(17));
                    _result.setCreateTime(rs.getString(18));
                    professor.setName(rs.getString(19));
                    _result.setProfessor(professor);
                    return _result;
                }else{
                    return null;
                }
            }finally{
                try{rs.close();}catch(Exception _m_7){}
            }
        }finally{
            try{ps.close();}catch(Exception _m_8){}
        }
    }
	
	public Consult queryCon(Connection con,String consultId) throws SQLException{
        int _m_1 = 1;
        String sql = "SELECT CONSULT_ID,CONSULT_TYPE,CONSULT_TITLE,CONSULT_CONTANT,PROFESSOR_ID,CONSULTANT_ID,"
        		+ " CONSULT_STATUS,FINISH_TIME,ASSESS_STATUS,ASSESS_STAR,ASSESS_CONTANT,ASSESS_TIME,THANKS_STATUS,"
        		+ " THANKS_MONEY,THANKS_TIME,REVOVERY_TIME,CONSULT.READ_STATUS,CONSULT.CREATE_TIME,PROFESSOR.NAME "
        		+ " FROM CONSULT LEFT JOIN PROFESSOR ON CONSULT.PROFESSOR_ID = PROFESSOR.ID WHERE CONSULT_ID = ?";
        PreparedStatement ps = con.prepareStatement(sql);
        try{
            ps.setString(_m_1++,consultId);
            ResultSet rs = ps.executeQuery();
            try{
                if(rs.next()){
                    Consult _result = new Consult();
                    _result.setConsultId(rs.getString(1));
                    _result.setConsultType(rs.getString(2));
                    _result.setConsultTitle(rs.getString(3));
                    _result.setConsultContant(rs.getString(4));
                    EditProfessor professor = new EditProfessor();
                    professor.setId(rs.getString(5));
                    _result.setConsultantId(rs.getString(6));
                    _result.setConsultStatus(rs.getInt(7));
                    String _m_2 = rs.getString(8);
                    if(rs.wasNull()){
                        _m_2 = null;
                    }
                    _result.setFinishTime(_m_2);
                    _result.setAssessStatus(rs.getInt(9));
                    _result.setAssessStar(rs.getInt(10));
                    String _m_3 = rs.getString(11);
                    if(rs.wasNull()){
                        _m_3 = null;
                    }
                    _result.setAssessContant(_m_3);
                    String _m_4 = rs.getString(12);
                    if(rs.wasNull()){
                        _m_4 = null;
                    }
                    _result.setAssessTime(_m_4);
                    _result.setThanksStatus(rs.getInt(13));
                    BigDecimal _m_5 = rs.getBigDecimal(14);
                    if(rs.wasNull()){
                        _m_5 = null;
                    }
                    _result.setThanksMoney(_m_5);
                    String _m_6 = rs.getString(15);
                    if(rs.wasNull()){
                        _m_6 = null;
                    }
                    _result.setThanksTime(_m_6);
                    _result.setRevoveryTime(rs.getString(16));
                    _result.setReadStatus(rs.getInt(17));
                    _result.setCreateTime(rs.getString(18));
                    professor.setName(rs.getString(19));
                    _result.setProfessor(professor);
                    return _result;
                }else{
                    return null;
                }
            }finally{
                try{rs.close();}catch(Exception _m_7){}
            }
        }finally{
            try{ps.close();}catch(Exception _m_8){}
        }
    }	
	
	public PageQueryResult<Consult> queryPageProBase(Connection con, String professorId, int status, 
			int timeType, int sortType, int pageSize, int pageNo) throws SQLException{
		PageQueryResult<Consult> queryResult = new PageQueryResult<Consult>();
		StringBuilder sql = new StringBuilder();
		sql.append(" WHERE PROFESSOR_ID = ? ");
		if(1 == status) {
			sql.append(" AND CONSULT_STATUS = 2 ");
		}else if(2 == status) {
			sql.append(" AND CONSULT_STATUS = 0 ");
		}else if(3 == status) {
			sql.append(" AND CONSULT_STATUS = 3 ");
		}else if(4 == status) {
			sql.append(" AND CONSULT_STATUS = 1 ");
		}else if(5 == status) {
			sql.append(" AND CONSULT_STATUS = 1 AND ASSESS_STATUS = 0 ");
		}
		StringBuilder whereSql = sql;
		sql = new StringBuilder();
		sql.append("SELECT COUNT(1) FROM CONSULT");
		if (whereSql.length() > 0) {
			sql.append(whereSql);
		}
		PreparedStatement ps = con.prepareStatement(sql.toString());
		int paramIndex = 1;
		int totalSize = 0;
		try {
			ps.setString(paramIndex++, professorId);
			queryResult.setPageSize(pageSize);
			ResultSet resultSet = ps.executeQuery();
			try {
				resultSet.next();
				totalSize = resultSet.getInt(1);
			} finally {
				try {
					resultSet.close();
				} catch (Exception _m_6) {
				}
			}
		} finally {
			try {
				ps.close();
			} catch (Exception _m_7) {
			}
		}
		queryResult.setTotal(totalSize);
		if (0 == totalSize) {
			queryResult.setPageNo(1);
			queryResult.setData(Collections.<Consult> emptyList());
			return queryResult;
		}
		paramIndex = 1;
		if (1 == pageNo) {
			queryResult.setPageNo(1);
			sql = new StringBuilder();
			sql.append(" SELECT CONSULT_ID,CONSULT_TYPE,CONSULT_TITLE,CONSULT_CONTANT,PROFESSOR_ID,CONSULTANT_ID,"
					+ " CONSULT_STATUS,FINISH_TIME,ASSESS_STATUS,ASSESS_STAR,ASSESS_CONTANT,ASSESS_TIME,THANKS_STATUS,"
					+ " THANKS_MONEY,THANKS_TIME,REVOVERY_TIME,READ_STATUS,CREATE_TIME FROM CONSULT ");
			if (whereSql.length() > 0) {
				sql.append(whereSql);
			}
			if(0 == timeType) {
				sql.append(" ORDER BY CREATE_TIME ");
			}else if(1 == timeType) {
				sql.append(" ORDER BY REVOVERY_TIME ");
			}else if(2 == timeType) {
				sql.append(" ORDER BY FINISH_TIME ");
			}
			if(0 == sortType) {
				sql.append(" DESC ");
			}
			sql.append(" LIMIT ").append(pageSize);
		} else {
			int _pageSize = totalSize / pageSize;
			if (totalSize % pageSize != 0) {
				++_pageSize;
			}
			if (pageNo > _pageSize) {
				pageNo = _pageSize;
			}
			queryResult.setPageNo(pageNo);
			--pageNo;
			int _m_10 = (pageNo * pageSize);
			sql = new StringBuilder();
			sql.append(" SELECT CONSULT_ID,CONSULT_TYPE,CONSULT_TITLE,CONSULT_CONTANT,PROFESSOR_ID,CONSULTANT_ID,"
					+ " CONSULT_STATUS,FINISH_TIME,ASSESS_STATUS,ASSESS_STAR,ASSESS_CONTANT,ASSESS_TIME,THANKS_STATUS,"
					+ " THANKS_MONEY,THANKS_TIME,REVOVERY_TIME,READ_STATUS,CREATE_TIME FROM CONSULT ");
			if (whereSql.length() > 0) {
				sql.append(whereSql);
			}
			if(0 == timeType) {
				sql.append(" ORDER BY CREATE_TIME ");
			}else if(1 == timeType) {
				sql.append(" ORDER BY REVOVERY_TIME ");
			}else if(2 == timeType) {
				sql.append(" ORDER BY FINISH_TIME ");
			}
			if(0 == sortType) {
				sql.append(" DESC ");
			}
			sql.append(" LIMIT ").append(pageSize).append(" OFFSET ").append(_m_10);
		}
		ps = con.prepareStatement(sql.toString());
		try {
			ps.setString(paramIndex++, professorId);
			ResultSet rs = ps.executeQuery();
			try {
				List<Consult> _m_11 = new ArrayList<Consult>();
				queryResult.setData(_m_11);
				while (rs.next()) {
					Consult consult = new Consult();
					consult.setConsultId(rs.getString(1));
					consult.setConsultType(rs.getString(2));
					consult.setConsultTitle(rs.getString(3));
					consult.setConsultContant(rs.getString(4));
					consult.setProfessorId(rs.getString(5));
					consult.setConsultantId(rs.getString(6));
					Integer m1 = rs.getInt(7);
					if (rs.wasNull()) {
						m1 = null;
					}
					consult.setConsultStatus(m1);
					String m2 = rs.getString(8);
					if (rs.wasNull()) {
						m2 = null;
					}
					consult.setFinishTime(m2);
					Integer m3 = rs.getInt(9);
					if (rs.wasNull()) {
						m3 = null;
					}
					consult.setAssessStatus(m3);
					consult.setAssessStar(rs.getInt(10));
					String m5 = rs.getString(11);
					if (rs.wasNull()) {
						m5 = null;
					}
					consult.setAssessContant(m5);
					String m6 = rs.getString(12);
					if (rs.wasNull()) {
						m6 = null;
					}
					consult.setAssessTime(m6);
					Integer m7 = rs.getInt(13);
					if (rs.wasNull()) {
						m7 = null;
					}
					consult.setThanksStatus(m7);
					BigDecimal m8 = rs.getBigDecimal(14);
					if (rs.wasNull()) {
						m8 = null;
					}
					consult.setThanksMoney(m8);
					String m9 = rs.getString(15);
					if (rs.wasNull()) {
						m9 = null;
					}
					consult.setThanksTime(m9);
					consult.setRevoveryTime(rs.getString(16));
					consult.setReadStatus(rs.getInt(17));
					consult.setCreateTime(rs.getString(18));
					_m_11.add(consult);
				}
				return queryResult;
			} finally {
				try {
					rs.close();
				} catch (Exception _m_17) {
				}
			}
		} finally {
			try {
				ps.close();
			} catch (Exception _m_18) {
			}
		}
	}
	
	public PageQueryResult<Consult> queryPagePro(Connection con, String professorId, int status, int timeType, int sortType, int pageSize, int pageNo) throws SQLException{
		PageQueryResult<Consult> queryResult = new PageQueryResult<Consult>();
		StringBuilder sql = new StringBuilder();
		sql.append(" WHERE CONSULT.PROFESSOR_ID = ? ");
		if(1 == status) {
			sql.append(" AND CONSULT_STATUS = 2 ");
		}else if(2 == status) {
			sql.append(" AND CONSULT_STATUS = 0 ");
		}else if(3 == status) {
			sql.append(" AND CONSULT_STATUS = 3 ");
		}else if(4 == status) {
			sql.append(" AND CONSULT_STATUS = 1 ");
		}else if(5 == status) {
			sql.append(" AND CONSULT_STATUS = 1 AND ASSESS_STATUS = 0 ");
		}
		StringBuilder whereSql = sql;
		sql = new StringBuilder();
		sql.append("SELECT COUNT(1) FROM CONSULT");
		if (whereSql.length() > 0) {
			sql.append(whereSql);
		}
		PreparedStatement ps = con.prepareStatement(sql.toString());
		int paramIndex = 1;
		int totalSize = 0;
		try {
			ps.setString(paramIndex++, professorId);
			queryResult.setPageSize(pageSize);
			ResultSet resultSet = ps.executeQuery();
			try {
				resultSet.next();
				totalSize = resultSet.getInt(1);
			} finally {
				try {
					resultSet.close();
				} catch (Exception _m_6) {
				}
			}
		} finally {
			try {
				ps.close();
			} catch (Exception _m_7) {
			}
		}
		queryResult.setTotal(totalSize);
		if (0 == totalSize) {
			queryResult.setPageNo(1);
			queryResult.setData(Collections.<Consult> emptyList());
			return queryResult;
		}
		paramIndex = 1;
		if (1 == pageNo) {
			queryResult.setPageNo(1);
			sql = new StringBuilder();
			sql.append(" SELECT CONSULT_ID,CONSULT_TYPE,CONSULT_TITLE,CONSULT_CONTANT,CONSULT.PROFESSOR_ID,CONSULTANT_ID,"
					+ " CONSULT_STATUS,FINISH_TIME,ASSESS_STATUS,ASSESS_STAR,ASSESS_CONTANT,ASSESS_TIME,THANKS_STATUS,"
					+ " THANKS_MONEY,THANKS_TIME,REVOVERY_TIME,READ_STATUS,CONSULT.CREATE_TIME,PROFESSOR.NAME,DEPARTMENT,"
					+ " PROFESSOR.TITLE,ADDRESS,OFFICE,AUTHENTICATION,AUTH_TYPE,AUTH_STATUS,ORGANIZATION.NAME "
					+ " FROM CONSULT INNER JOIN PROFESSOR ON CONSULTANT_ID = PROFESSOR.ID "
					+ " LEFT JOIN ORGANIZATION ON PROFESSOR.ORG_ID = ORGANIZATION.ID ");
			if (whereSql.length() > 0) {
				sql.append(whereSql);
			}
			if(0 == timeType) {
				sql.append(" ORDER BY CONSULT.CREATE_TIME ");
			}else if(1 == timeType) {
				sql.append(" ORDER BY REVOVERY_TIME ");
			}else if(2 == timeType) {
				sql.append(" ORDER BY FINISH_TIME ");
			}
			if(0 == sortType) {
				sql.append(" DESC ");
			}
			sql.append(" LIMIT ").append(pageSize);
		} else {
			int _pageSize = totalSize / pageSize;
			if (totalSize % pageSize != 0) {
				++_pageSize;
			}
			if (pageNo > _pageSize) {
				pageNo = _pageSize;
			}
			queryResult.setPageNo(pageNo);
			--pageNo;
			int _m_10 = (pageNo * pageSize);
			sql = new StringBuilder();
			sql.append(" SELECT CONSULT_ID,CONSULT_TYPE,CONSULT_TITLE,CONSULT_CONTANT,CONSULT.PROFESSOR_ID,CONSULTANT_ID,"
					+ " CONSULT_STATUS,FINISH_TIME,ASSESS_STATUS,ASSESS_STAR,ASSESS_CONTANT,ASSESS_TIME,THANKS_STATUS,"
					+ " THANKS_MONEY,THANKS_TIME,REVOVERY_TIME,READ_STATUS,CONSULT.CREATE_TIME,PROFESSOR.NAME,DEPARTMENT,"
					+ " PROFESSOR.TITLE,ADDRESS,OFFICE,AUTHENTICATION,AUTH_TYPE,AUTH_STATUS,ORGANIZATION.NAME "
					+ " FROM CONSULT INNER JOIN PROFESSOR ON CONSULTANT_ID = PROFESSOR.ID "
					+ " LEFT JOIN ORGANIZATION ON PROFESSOR.ORG_ID = ORGANIZATION.ID ");
			if (whereSql.length() > 0) {
				sql.append(whereSql);
			}
			if(0 == timeType) {
				sql.append(" ORDER BY CONSULT.CREATE_TIME ");
			}else if(1 == timeType) {
				sql.append(" ORDER BY REVOVERY_TIME ");
			}else if(2 == timeType) {
				sql.append(" ORDER BY FINISH_TIME ");
			}
			if(0 == sortType) {
				sql.append(" DESC ");
			}
			sql.append(" LIMIT ").append(pageSize).append(" OFFSET ").append(_m_10);
		}
		ps = con.prepareStatement(sql.toString());
		try {
			ps.setString(paramIndex++, professorId);
			ResultSet rs = ps.executeQuery();
			try {
				List<Consult> _m_11 = new ArrayList<Consult>();
				queryResult.setData(_m_11);
				while (rs.next()) {
					Consult consult = new Consult();
					consult.setConsultId(rs.getString(1));
					consult.setConsultType(rs.getString(2));
					consult.setConsultTitle(rs.getString(3));
					consult.setConsultContant(rs.getString(4));
					consult.setProfessorId(rs.getString(5));
					EditProfessor professor = new EditProfessor();
					professor.setId(rs.getString(6));
					Integer m1 = rs.getInt(7);
					if (rs.wasNull()) {
						m1 = null;
					}
					consult.setConsultStatus(m1);
					String m2 = rs.getString(8);
					if (rs.wasNull()) {
						m2 = null;
					}
					consult.setFinishTime(m2);
					Integer m3 = rs.getInt(9);
					if (rs.wasNull()) {
						m3 = null;
					}
					consult.setAssessStatus(m3);
					Integer m4 = rs.getInt(10);
					if (rs.wasNull()) {
						m4 = null;
					}
					consult.setAssessStar(m4);
					String m5 = rs.getString(11);
					if (rs.wasNull()) {
						m5 = null;
					}
					consult.setAssessContant(m5);
					String m6 = rs.getString(12);
					if (rs.wasNull()) {
						m6 = null;
					}
					consult.setAssessTime(m6);
					Integer m7 = rs.getInt(13);
					if (rs.wasNull()) {
						m7 = null;
					}
					consult.setThanksStatus(m7);
					BigDecimal m8 = rs.getBigDecimal(14);
					if (rs.wasNull()) {
						m8 = null;
					}
					consult.setThanksMoney(m8);
					String m9 = rs.getString(15);
					if (rs.wasNull()) {
						m9 = null;
					}
					consult.setThanksTime(m9);
					consult.setRevoveryTime(rs.getString(16));
					consult.setReadStatus(rs.getInt(17));
					consult.setCreateTime(rs.getString(18));
					professor.setName(rs.getString(19));
					professor.setDepartment(rs.getString(20));
					professor.setTitle(rs.getString(21));
					String m10 = rs.getString(22);
					if (rs.wasNull()) {
						m10 = null;
					}
					professor.setAddress(m10);
					professor.setOffice(rs.getString(23));
					professor.setAuthentication(rs.getInt(24));
					professor.setAuthType(rs.getInt(25));
					professor.setAuthStatus(rs.getInt(26));
					String orgName = rs.getString(27);
					if(rs.wasNull()){
						orgName = null;
					}
					professor.setOrgName(orgName);
					consult.setProfessor(professor);
					_m_11.add(consult);
				}
				return queryResult;
			} finally {
				try {
					rs.close();
				} catch (Exception _m_17) {
				}
			}
		} finally {
			try {
				ps.close();
			} catch (Exception _m_18) {
			}
		}
	}
	
	public PageQueryResult<Consult> queryPageConBase(Connection con, String consultantId, int status, int timeType, int sortType, int pageSize, int pageNo) throws SQLException{
		PageQueryResult<Consult> queryResult = new PageQueryResult<Consult>();
		StringBuilder sql = new StringBuilder();
		sql.append(" WHERE CONSULTANT_ID = ? ");
		if(1 == status) {
			sql.append(" AND CONSULT_STATUS = 2 ");
		}else if(2 == status) {
			sql.append(" AND CONSULT_STATUS = 0 ");
		}else if(3 == status) {
			sql.append(" AND CONSULT_STATUS = 3 ");
		}else if(4 == status) {
			sql.append(" AND CONSULT_STATUS = 1 ");
		}else if(5 == status) {
			sql.append(" AND CONSULT_STATUS = 1 AND ASSESS_STATUS = 0 ");
		}
		StringBuilder whereSql = sql;
		sql = new StringBuilder();
		sql.append("SELECT COUNT(1) FROM CONSULT");
		if (whereSql.length() > 0) {
			sql.append(whereSql);
		}
		PreparedStatement ps = con.prepareStatement(sql.toString());
		int paramIndex = 1;
		int totalSize = 0;
		try {
			ps.setString(paramIndex++, consultantId);
			ResultSet resultSet = ps.executeQuery();
			try {
				resultSet.next();
				totalSize = resultSet.getInt(1);
			} finally {
				try {
					resultSet.close();
				} catch (Exception _m_6) {
				}
			}
		} finally {
			try {
				ps.close();
			} catch (Exception _m_7) {
			}
		}
		queryResult.setTotal(totalSize);
		if (0 == totalSize) {
			queryResult.setPageNo(1);
			queryResult.setData(Collections.<Consult> emptyList());
			return queryResult;
		}
		paramIndex = 1;
		if (1 == pageNo) {
			queryResult.setPageNo(1);
			sql = new StringBuilder();
			sql.append(" SELECT CONSULT_ID,CONSULT_TYPE,CONSULT_TITLE,CONSULT_CONTANT,PROFESSOR_ID,CONSULTANT_ID,"
					+ " CONSULT_STATUS,FINISH_TIME,ASSESS_STATUS,ASSESS_STAR,ASSESS_CONTANT,ASSESS_TIME,THANKS_STATUS,"
					+ " THANKS_MONEY,THANKS_TIME,REVOVERY_TIME,READ_STATUS,CREATE_TIME FROM CONSULT ");
			if (whereSql.length() > 0) {
				sql.append(whereSql);
			}
			if(0 == timeType) {
				sql.append(" ORDER BY CREATE_TIME ");
			}else if(1 == timeType) {
				sql.append(" ORDER BY REVOVERY_TIME ");
			}else if(2 == timeType) {
				sql.append(" ORDER BY FINISH_TIME ");
			}
			if(0 == sortType) {
				sql.append(" DESC ");
			}
			sql.append(" LIMIT ").append(pageSize);
		} else {
			int _pageSize = totalSize / pageSize;
			if (totalSize % pageSize != 0) {
				++_pageSize;
			}
			if (pageNo > _pageSize) {
				pageNo = _pageSize;
			}
			queryResult.setPageNo(pageNo);
			--pageNo;
			int _m_10 = (pageNo * pageSize);
			sql = new StringBuilder();
			sql.append(" SELECT CONSULT_ID,CONSULT_TYPE,CONSULT_TITLE,CONSULT_CONTANT,PROFESSOR_ID,CONSULTANT_ID,"
					+ " CONSULT_STATUS,FINISH_TIME,ASSESS_STATUS,ASSESS_STAR,ASSESS_CONTANT,ASSESS_TIME,THANKS_STATUS,"
					+ " THANKS_MONEY,THANKS_TIME,REVOVERY_TIME,READ_STATUS,CREATE_TIME FROM CONSULT ");
			if (whereSql.length() > 0) {
				sql.append(whereSql);
			}
			if(0 == timeType) {
				sql.append(" ORDER BY CREATE_TIME ");
			}else if(1 == timeType) {
				sql.append(" ORDER BY REVOVERY_TIME ");
			}else if(2 == timeType) {
				sql.append(" ORDER BY FINISH_TIME ");
			}
			if(0 == sortType) {
				sql.append(" DESC ");
			}
			sql.append(" LIMIT ").append(pageSize).append(" OFFSET ").append(_m_10);
		}
		ps = con.prepareStatement(sql.toString());
		try {
			ps.setString(paramIndex++, consultantId);
			ResultSet rs = ps.executeQuery();
			try {
				List<Consult> _m_11 = new ArrayList<Consult>();
				queryResult.setData(_m_11);
				while (rs.next()) {
					Consult consult = new Consult();
					consult.setConsultId(rs.getString(1));
					consult.setConsultType(rs.getString(2));
					consult.setConsultTitle(rs.getString(3));
					consult.setConsultContant(rs.getString(4));
					consult.setProfessorId(rs.getString(5));
					consult.setConsultantId(rs.getString(6));
					Integer m1 = rs.getInt(7);
					if (rs.wasNull()) {
						m1 = null;
					}
					consult.setConsultStatus(m1);
					String m2 = rs.getString(8);
					if (rs.wasNull()) {
						m2 = null;
					}
					consult.setFinishTime(m2);
					Integer m3 = rs.getInt(9);
					if (rs.wasNull()) {
						m3 = null;
					}
					consult.setAssessStatus(m3);
					consult.setAssessStar(rs.getInt(10));
					String m5 = rs.getString(11);
					if (rs.wasNull()) {
						m5 = null;
					}
					consult.setAssessContant(m5);
					String m6 = rs.getString(12);
					if (rs.wasNull()) {
						m6 = null;
					}
					consult.setAssessTime(m6);
					Integer m7 = rs.getInt(13);
					if (rs.wasNull()) {
						m7 = null;
					}
					consult.setThanksStatus(m7);
					BigDecimal m8 = rs.getBigDecimal(14);
					if (rs.wasNull()) {
						m8 = null;
					}
					consult.setThanksMoney(m8);
					String m9 = rs.getString(15);
					if (rs.wasNull()) {
						m9 = null;
					}
					consult.setThanksTime(m9);
					consult.setRevoveryTime(rs.getString(16));
					consult.setReadStatus(rs.getInt(17));
					consult.setCreateTime(rs.getString(18));
					_m_11.add(consult);
				}
				return queryResult;
			} finally {
				try {
					rs.close();
				} catch (Exception _m_17) {
				}
			}
		} finally {
			try {
				ps.close();
			} catch (Exception _m_18) {
			}
		}
	}
	
	public PageQueryResult<Consult> queryPageCon(Connection con, String consultantId, int status, int timeType, int sortType, int pageSize, int pageNo) throws SQLException{
		PageQueryResult<Consult> queryResult = new PageQueryResult<Consult>();
		StringBuilder sql = new StringBuilder();
		sql.append(" WHERE CONSULT.CONSULTANT_ID = ? ");
		if(1 == status) {
			sql.append(" AND CONSULT_STATUS = 2 ");
		}else if(2 == status) {
			sql.append(" AND CONSULT_STATUS = 0 ");
		}else if(3 == status) {
			sql.append(" AND CONSULT_STATUS = 3 ");
		}else if(4 == status) {
			sql.append(" AND CONSULT_STATUS = 1 ");
		}else if(5 == status) {
			sql.append(" AND CONSULT_STATUS = 1 AND ASSESS_STATUS = 0 ");
		}
		StringBuilder whereSql = sql;
		sql = new StringBuilder();
		sql.append("SELECT COUNT(1) FROM CONSULT");
		if (whereSql.length() > 0) {
			sql.append(whereSql);
		}
		PreparedStatement ps = con.prepareStatement(sql.toString());
		int paramIndex = 1;
		int totalSize = 0;
		try {
			ps.setString(paramIndex++, consultantId);
			ResultSet resultSet = ps.executeQuery();
			try {
				resultSet.next();
				totalSize = resultSet.getInt(1);
			} finally {
				try {
					resultSet.close();
				} catch (Exception _m_6) {
				}
			}
		} finally {
			try {
				ps.close();
			} catch (Exception _m_7) {
			}
		}
		queryResult.setTotal(totalSize);
		if (0 == totalSize) {
			queryResult.setPageNo(1);
			queryResult.setData(Collections.<Consult> emptyList());
			return queryResult;
		}
		paramIndex = 1;
		if (1 == pageNo) {
			queryResult.setPageNo(1);
			sql = new StringBuilder();
			sql.append(" SELECT CONSULT_ID,CONSULT_TYPE,CONSULT_TITLE,CONSULT_CONTANT,CONSULT.PROFESSOR_ID,CONSULTANT_ID,"
					+ " CONSULT_STATUS,FINISH_TIME,ASSESS_STATUS,ASSESS_STAR,ASSESS_CONTANT,ASSESS_TIME,THANKS_STATUS,"
					+ " THANKS_MONEY,THANKS_TIME,REVOVERY_TIME,CONSULT.READ_STATUS,CONSULT.CREATE_TIME,PROFESSOR.NAME,"
					+ " DEPARTMENT,TITLE,ADDRESS,OFFICE,AUTHENTICATION,AUTH_TYPE,AUTH_STATUS,ORGANIZATION.NAME "
					+ " FROM CONSULT INNER JOIN PROFESSOR ON PROFESSOR_ID = PROFESSOR.ID "
					+ " LEFT JOIN ORGANIZATION ON PROFESSOR.ORG_ID = ORGANIZATION.ID ");
			if (whereSql.length() > 0) {
				sql.append(whereSql);
			}
			if(0 == timeType) {
				sql.append(" ORDER BY CONSULT.CREATE_TIME ");
			}else if(1 == timeType) {
				sql.append(" ORDER BY REVOVERY_TIME ");
			}else if(2 == timeType) {
				sql.append(" ORDER BY FINISH_TIME ");
			}
			if(0 == sortType) {
				sql.append(" DESC ");
			}
			sql.append(" LIMIT ").append(pageSize);
		} else {
			int _pageSize = totalSize / pageSize;
			if (totalSize % pageSize != 0) {
				++_pageSize;
			}
			if (pageNo > _pageSize) {
				pageNo = _pageSize;
			}
			queryResult.setPageNo(pageNo);
			--pageNo;
			int _m_10 = (pageNo * pageSize);
			sql = new StringBuilder();
			sql.append(" SELECT CONSULT_ID,CONSULT_TYPE,CONSULT_TITLE,CONSULT_CONTANT,CONSULT.PROFESSOR_ID,CONSULTANT_ID,"
					+ " CONSULT_STATUS,FINISH_TIME,ASSESS_STATUS,ASSESS_STAR,ASSESS_CONTANT,ASSESS_TIME,THANKS_STATUS,"
					+ " THANKS_MONEY,THANKS_TIME,REVOVERY_TIME,CONSULT.READ_STATUS,CONSULT.CREATE_TIME,PROFESSOR.NAME,"
					+ " DEPARTMENT,TITLE,ADDRESS,OFFICE,AUTHENTICATION,AUTH_TYPE,AUTH_STATUS,ORGANIZATION.NAME "
					+ " FROM CONSULT INNER JOIN PROFESSOR ON PROFESSOR_ID = PROFESSOR.ID "
					+ " LEFT JOIN ORGANIZATION ON PROFESSOR.ORG_ID = ORGANIZATION.ID ");
			if (whereSql.length() > 0) {
				sql.append(whereSql);
			}
			if(0 == timeType) {
				sql.append(" ORDER BY CONSULT.CREATE_TIME ");
			}else if(1 == timeType) {
				sql.append(" ORDER BY REVOVERY_TIME ");
			}else if(2 == timeType) {
				sql.append(" ORDER BY FINISH_TIME ");
			}
			if(0 == sortType) {
				sql.append(" DESC ");
			}
			sql.append(" LIMIT ").append(pageSize).append(" OFFSET ").append(_m_10);
		}
		ps = con.prepareStatement(sql.toString());
		try {
			ps.setString(paramIndex++, consultantId);
			ResultSet rs = ps.executeQuery();
			try {
				List<Consult> _m_11 = new ArrayList<Consult>();
				queryResult.setData(_m_11);
				while (rs.next()) {
					Consult consult = new Consult();
					consult.setConsultId(rs.getString(1));
					consult.setConsultType(rs.getString(2));
					consult.setConsultTitle(rs.getString(3));
					consult.setConsultContant(rs.getString(4));
					EditProfessor professor = new EditProfessor();
					professor.setId(rs.getString(5));
					consult.setConsultantId(rs.getString(6));
					Integer m1 = rs.getInt(7);
					if (rs.wasNull()) {
						m1 = null;
					}
					consult.setConsultStatus(m1);
					String m2 = rs.getString(8);
					if (rs.wasNull()) {
						m2 = null;
					}
					consult.setFinishTime(m2);
					Integer m3 = rs.getInt(9);
					if (rs.wasNull()) {
						m3 = null;
					}
					consult.setAssessStatus(m3);
					consult.setAssessStar(rs.getInt(10));
					String m5 = rs.getString(11);
					if (rs.wasNull()) {
						m5 = null;
					}
					consult.setAssessContant(m5);
					String m6 = rs.getString(12);
					if (rs.wasNull()) {
						m6 = null;
					}
					consult.setAssessTime(m6);
					Integer m7 = rs.getInt(13);
					if (rs.wasNull()) {
						m7 = null;
					}
					consult.setThanksStatus(m7);
					BigDecimal m8 = rs.getBigDecimal(14);
					if (rs.wasNull()) {
						m8 = null;
					}
					consult.setThanksMoney(m8);
					String m9 = rs.getString(15);
					if (rs.wasNull()) {
						m9 = null;
					}
					consult.setThanksTime(m9);
					consult.setRevoveryTime(rs.getString(16));
					consult.setReadStatus(rs.getInt(17));
					consult.setCreateTime(rs.getString(18));
					professor.setName(rs.getString(19));
					professor.setDepartment(rs.getString(20));
					professor.setTitle(rs.getString(21));
					String m10 = rs.getString(22);
					if (rs.wasNull()) {
						m10 = null;
					}
					professor.setAddress(m10);
					professor.setOffice(rs.getString(23));
					professor.setAuthentication(rs.getInt(24));
					professor.setAuthType(rs.getInt(25));
					professor.setAuthStatus(rs.getInt(26));
					String orgName = rs.getString(27);
					if(rs.wasNull()){
						orgName = null;
					}
					professor.setOrgName(orgName);
					consult.setProfessor(professor);
					_m_11.add(consult);
				}
				return queryResult;
			} finally {
				try {
					rs.close();
				} catch (Exception _m_17) {
				}
			}
		} finally {
			try {
				ps.close();
			} catch (Exception _m_18) {
			}
		}
	}
	
	/**
	 * 按条件搜索所有咨询的接口
	 * @param con
	 * @param professorId 用户ID
	 * @param consultOrNeed 接受咨询或咨询别人的状态值，1-收到咨询，2-我的需求,默认为1
	 * @param consultType 咨询类型，包括技术咨询、资源咨询、其他事务
	 * @param status 查询状态 0-全部，1-待回复/新咨询，2-进行中，3-已谢绝，4-已完成，5-待评价，默认为0
	 * @param timeType 排序类型 0-按发起时间，1-按最后回复时间，2-按完成时间，默认为0
	 * @param pageSize
	 * @param pageNo
	 * @return 返回咨询列表
	 * @throws SQLException
	 */
	public PageQueryResult<Consult> queryPageBase(Connection con, String professorId, int consultOrNeed, 
			String consultType, int status, int timeType, int pageSize, int pageNo) throws SQLException{
		PageQueryResult<Consult> queryResult = new PageQueryResult<Consult>();
		StringBuilder sql = new StringBuilder();
		if(0 == consultOrNeed){
			sql.append(" WHERE (PROFESSOR_ID = ? OR CONSULTANT_ID = ?) ");
		}else if(1 == consultOrNeed){
			sql.append(" WHERE PROFESSOR_ID = ? ");
		}else if(2 == consultOrNeed){
			sql.append(" WHERE CONSULTANT_ID = ? ");
		}
		if(consultType != null){
			if("技术咨询".equals(consultType)){
				sql.append(" AND CONSULT_TYPE = '技术咨询' ");
			}else if("资源咨询".equals(consultType)){
				sql.append(" AND CONSULT_TYPE = '资源咨询' ");
			}else if("其他事务".equals(consultType)){
				sql.append(" AND CONSULT_TYPE = '其他事务' ");
			}
		}
		if(1 == status) {
			sql.append(" AND CONSULT_STATUS = 2 ");
		}else if(2 == status) {
			sql.append(" AND CONSULT_STATUS = 0 ");
		}else if(3 == status) {
			sql.append(" AND CONSULT_STATUS = 3 ");
		}else if(4 == status) {
			sql.append(" AND CONSULT_STATUS = 1 ");
		}else if(5 == status) {
			sql.append(" AND CONSULT_STATUS = 1 AND ASSESS_STATUS = 0 ");
		}
		StringBuilder whereSql = sql;
		sql = new StringBuilder();
		sql.append("SELECT COUNT(1) FROM CONSULT");
		if (whereSql.length() > 0) {
			sql.append(whereSql);
		}
		PreparedStatement ps = con.prepareStatement(sql.toString());
		int paramIndex = 1;
		int totalSize = 0;
		try {
			ps.setString(paramIndex++, professorId);
			if(0 == consultOrNeed){
				ps.setString(paramIndex++, professorId);
			}
			queryResult.setPageSize(pageSize);
			ResultSet resultSet = ps.executeQuery();
			try {
				resultSet.next();
				totalSize = resultSet.getInt(1);
			} finally {
				try {
					resultSet.close();
				} catch (Exception _m_6) {
				}
			}
		} finally {
			try {
				ps.close();
			} catch (Exception _m_7) {
			}
		}
		queryResult.setTotal(totalSize);
		if (0 == totalSize) {
			queryResult.setPageNo(1);
			queryResult.setData(Collections.<Consult> emptyList());
			return queryResult;
		}
		paramIndex = 1;
		if (1 == pageNo) {
			queryResult.setPageNo(1);
			sql = new StringBuilder();
			sql.append(" SELECT CONSULT_ID,CONSULT_TYPE,CONSULT_TITLE,CONSULT_CONTANT,PROFESSOR_ID,CONSULTANT_ID,"
					+ " CONSULT_STATUS,FINISH_TIME,ASSESS_STATUS,ASSESS_STAR,ASSESS_CONTANT,ASSESS_TIME,THANKS_STATUS,"
					+ " THANKS_MONEY,THANKS_TIME,REVOVERY_TIME,READ_STATUS,CREATE_TIME FROM CONSULT ");
			if (whereSql.length() > 0) {
				sql.append(whereSql);
			}
			if(0 == timeType) {
				sql.append(" ORDER BY CREATE_TIME ");
			}else if(1 == timeType) {
				sql.append(" ORDER BY REVOVERY_TIME DESC ");
			}else if(2 == timeType) {
				sql.append(" ORDER BY FINISH_TIME DESC ");
			}
			sql.append(" LIMIT ").append(pageSize);
		} else {
			int _pageSize = totalSize / pageSize;
			if (totalSize % pageSize != 0) {
				++_pageSize;
			}
			if (pageNo > _pageSize) {
				pageNo = _pageSize;
			}
			queryResult.setPageNo(pageNo);
			--pageNo;
			int _m_10 = (pageNo * pageSize);
			sql = new StringBuilder();
			sql.append(" SELECT CONSULT_ID,CONSULT_TYPE,CONSULT_TITLE,CONSULT_CONTANT,PROFESSOR_ID,CONSULTANT_ID,"
					+ " CONSULT_STATUS,FINISH_TIME,ASSESS_STATUS,ASSESS_STAR,ASSESS_CONTANT,ASSESS_TIME,THANKS_STATUS,"
					+ " THANKS_MONEY,THANKS_TIME,REVOVERY_TIME,READ_STATUS,CREATE_TIME FROM CONSULT ");
			if (whereSql.length() > 0) {
				sql.append(whereSql);
			}
			if(0 == timeType) {
				sql.append(" ORDER BY CREATE_TIME ");
			}else if(1 == timeType) {
				sql.append(" ORDER BY REVOVERY_TIME DESC ");
			}else if(2 == timeType) {
				sql.append(" ORDER BY FINISH_TIME DESC ");
			}
			sql.append(" LIMIT ").append(pageSize).append(" OFFSET ").append(_m_10);
		}
		ps = con.prepareStatement(sql.toString());
		try {
			ps.setString(paramIndex++, professorId);
			if(0 == consultOrNeed){
				ps.setString(paramIndex++, professorId);
			}
			ResultSet rs = ps.executeQuery();
			try {
				List<Consult> _m_11 = new ArrayList<Consult>();
				queryResult.setData(_m_11);
				while (rs.next()) {
					Consult consult = new Consult();
					consult.setConsultId(rs.getString(1));
					consult.setConsultType(rs.getString(2));
					consult.setConsultTitle(rs.getString(3));
					consult.setConsultContant(rs.getString(4));
					consult.setProfessorId(rs.getString(5));
					consult.setConsultantId(rs.getString(6));
					Integer m1 = rs.getInt(7);
					if (rs.wasNull()) {
						m1 = null;
					}
					consult.setConsultStatus(m1);
					String m2 = rs.getString(8);
					if (rs.wasNull()) {
						m2 = null;
					}
					consult.setFinishTime(m2);
					Integer m3 = rs.getInt(9);
					if (rs.wasNull()) {
						m3 = null;
					}
					consult.setAssessStatus(m3);
					consult.setAssessStar(rs.getInt(10));
					String m5 = rs.getString(11);
					if (rs.wasNull()) {
						m5 = null;
					}
					consult.setAssessContant(m5);
					String m6 = rs.getString(12);
					if (rs.wasNull()) {
						m6 = null;
					}
					consult.setAssessTime(m6);
					Integer m7 = rs.getInt(13);
					if (rs.wasNull()) {
						m7 = null;
					}
					consult.setThanksStatus(m7);
					BigDecimal m8 = rs.getBigDecimal(14);
					if (rs.wasNull()) {
						m8 = null;
					}
					consult.setThanksMoney(m8);
					String m9 = rs.getString(15);
					if (rs.wasNull()) {
						m9 = null;
					}
					consult.setThanksTime(m9);
					consult.setRevoveryTime(rs.getString(16));
					consult.setReadStatus(rs.getInt(17));
					consult.setCreateTime(rs.getString(18));
					_m_11.add(consult);
				}
				return queryResult;
			} finally {
				try {
					rs.close();
				} catch (Exception _m_17) {
				}
			}
		} finally {
			try {
				ps.close();
			} catch (Exception _m_18) {
			}
		}
	}
	
}