ekexiu.com.portal/src/main/java/com/ekexiu/portal/dao/TidingsDao.java

package com.ekexiu.portal.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.jfw.apt.orm.annotation.dao.DAO;
import org.jfw.apt.orm.annotation.dao.method.operator.Insert;

import com.ekexiu.portal.po.Consult;
import com.ekexiu.portal.po.Professor;
import com.ekexiu.portal.po.Tidings;

@DAO
public abstract class TidingsDao {
	@Insert
	public abstract int insert(Connection con, Tidings tidings) throws SQLException;
	
	public int updateReadStatus(Connection con,String senderId,String consultId) throws SQLException{
        int _m_1 = 1;
        String sql ="UPDATE TIDINGS SET READ_STATUS = 1 WHERE READ_STATUS = 0 AND CONSULT_ID = ? AND SENDER_ID != ? ";
        PreparedStatement ps = con.prepareStatement(sql);
        try{
            ps.setString(_m_1++,consultId);
            ps.setString(_m_1++,senderId);
            return ps.executeUpdate();
        }finally{
            try{ps.close();}catch(Exception _m_2){}
        }
    }
	
	/**
	 * 查询当前咨询有几条未读消息
	 * @param con
	 * @param consultId 咨询ID
	 * @param senderId 登录者ID
	 * @return 返回该咨询中对方发送过来未查看的信息条数
	 * @throws SQLException
	 */
	public int queryNotReadTidings(Connection con, String consultId, String senderId) throws SQLException {
		String sql = " SELECT COUNT(1) FROM TIDINGS WHERE READ_STATUS = 0 AND CONSULT_ID = ? AND SENDER_ID != ? ";
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setString(1, consultId);
		ps.setString(2, senderId);
		ResultSet rs = ps.executeQuery();
		rs.next();
		int count = rs.getInt(1);
		return count;
	}
	
	/**
	 * 我的工作台最新回复
	 * @param con
	 * @param consultantId 咨询者ID
	 * @return 返回最新回复信息(包括咨询别人和别人咨询自己的最新回复)
	 * @throws SQLException
	 */
	public Tidings queryNewReply(Connection con, String consultantId) throws SQLException{
		int _m_1 = 1;
        String sql = "SELECT TIDINGS_ID,TIDINGS_CONTANT,SENDER_ID,TIDINGS.CONSULT_ID,"
        		+ " TIDINGS.CREATE_TIME,TIDINGS.READ_STATUS,CONSULT.CONSULT_TITLE"
        		+ " FROM TIDINGS LEFT JOIN CONSULT ON TIDINGS.CONSULT_ID = CONSULT.CONSULT_ID "
        		+ " WHERE TIDINGS.CREATE_TIME IN "
        		+ " (SELECT MAX(TIDINGS.CREATE_TIME) 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_1++,consultantId);
            ps.setString(_m_1++,consultantId);
            ps.setString(_m_1++,consultantId);
            ResultSet rs = ps.executeQuery();
            try{
                if(rs.next()){
                	Tidings tidings = new Tidings();
                	tidings.setTidingsId(rs.getString(1));
                	tidings.setTidingsContant(rs.getString(2));
                	tidings.setSenderId(rs.getString(3));
                	Consult consult = new Consult();
                	consult.setConsultId(rs.getString(4));
                	tidings.setCreateTime(rs.getString(5));
                	tidings.setReadStatus(rs.getInt(6));
                	consult.setConsultTitle(rs.getString(7));
                	tidings.setConsult(consult);
                	return tidings;
                }else{
                    return null;
                }
            }finally{
                try{rs.close();}catch(Exception _m_8){}
            }
        }finally{
            try{ps.close();}catch(Exception _m_9){}
        }
	}
	
	/**
	 * 咨询页面显示最后回复信息
	 * @param con
	 * @param consultId 咨询ID
	 * @param senderId 登陆者ID
	 * @return 返回对方最后回复的信息
	 * @throws SQLException
	 */
	public Tidings queryLastRevovery(Connection con,String consultId,String senderId) throws SQLException{
        int _m_1 = 1;
        String sql = "SELECT TIDINGS_ID,TIDINGS_CONTANT,SENDER_ID,CONSULT_ID,CREATE_TIME FROM TIDINGS "
        		+ " WHERE CREATE_TIME IN (SELECT MAX(CREATE_TIME) FROM TIDINGS WHERE (CONSULT_ID = ?) AND (SENDER_ID != ?)) ";
        PreparedStatement ps = con.prepareStatement(sql);
        try{
            ps.setString(_m_1++,consultId);
            ps.setString(_m_1++,senderId);
            ResultSet rs = ps.executeQuery();
            try{
                if(rs.next()){
                    Tidings _result = new Tidings();
                    _result.setTidingsId(rs.getString(1));
                    _result.setTidingsContant(rs.getString(2));
                    _result.setSenderId(rs.getString(3));
                    _result.setConsultId(rs.getString(4));
                    _result.setCreateTime(rs.getString(5));
                    return _result;
                }else{
                    return null;
                }
            }finally{
                try{rs.close();}catch(Exception _m_2){}
            }
        }finally{
            try{ps.close();}catch(Exception _m_3){}
        }
    }
	
	public Tidings query(Connection con,String tidingsId) throws SQLException{
        int _m_1 = 1;
        String sql = "SELECT TIDINGS_ID,TIDINGS_CONTANT,SENDER_ID,CONSULT_ID,TIDINGS.CREATE_TIME,PROFESSOR.NAME "
        		+ " FROM TIDINGS LEFT JOIN PROFESSOR ON SENDER_ID = PROFESSOR.ID WHERE TIDINGS_ID = ?";
        PreparedStatement ps = con.prepareStatement(sql);
        try{
            ps.setString(_m_1++,tidingsId);
            ResultSet rs = ps.executeQuery();
            try{
                if(rs.next()){
                    Tidings _result = new Tidings();
                    _result.setTidingsId(rs.getString(1));
                    _result.setTidingsContant(rs.getString(2));
                    Professor professor = new Professor();
                    professor.setId(rs.getString(3));
                    _result.setConsultId(rs.getString(4));
                    _result.setCreateTime(rs.getString(5));
                    professor.setName(rs.getString(6));
                    _result.setProfessor(professor);
                    return _result;
                }else{
                    return null;
                }
            }finally{
                try{rs.close();}catch(Exception _m_2){}
            }
        }finally{
            try{ps.close();}catch(Exception _m_3){}
        }
    }
	
	public java.util.List<Tidings> queryCon(Connection con,String consultId) throws SQLException{
        int _m_1 = 1;
        String sql = "SELECT TIDINGS_ID,TIDINGS_CONTANT,SENDER_ID,TIDINGS.CONSULT_ID,TIDINGS.CREATE_TIME,PROFESSOR.NAME "
        		+ " FROM TIDINGS LEFT JOIN PROFESSOR ON SENDER_ID = PROFESSOR.ID WHERE TIDINGS.CONSULT_ID = ? ORDER BY TIDINGS.CREATE_TIME";
        PreparedStatement ps = con.prepareStatement(sql);
        try{
            ps.setString(_m_1++,consultId);
            ResultSet rs = ps.executeQuery();
            try{
                java.util.List<Tidings> _result = new java.util.ArrayList<Tidings>();
                while(rs.next()){
                    Tidings _obj =  new Tidings();
                    _obj.setTidingsId(rs.getString(1));
                    _obj.setTidingsContant(rs.getString(2));
                    Professor professor = new Professor();
                    professor.setId(rs.getString(3));
                    _obj.setConsultId(rs.getString(4));
                    _obj.setCreateTime(rs.getString(5));
                    professor.setName(rs.getString(6));
                    _obj.setProfessor(professor);
                    _result.add(_obj);
                }
                return _result;
            }finally{
                try{rs.close();}catch(Exception _m_2){}
            }
        }finally{
            try{ps.close();}catch(Exception _m_3){}
        }
    }
	
}