// 删除
 public int delete(int rid) {
   Connection dbConn = DataBaseTool.getConnection();
   String sql = "delete from t_register where id=" + rid;
   int count = DataBaseTool.executeSQL(dbConn, sql);
   DataBaseTool.close(null, null, dbConn);
   return count;
 }
 // 根据id查询
 public TRegister searchByID(int id) throws Exception {
   Connection dbConn = DataBaseTool.getConnection();
   String sql = "select * from t_register where id=" + id;
   ResultSet rs = DataBaseTool.executeQuery(dbConn, sql);
   TRegister register = null;
   while (rs.next()) {
     register = new TRegister();
     setOne(register, rs);
   }
   DataBaseTool.close(rs, rs.getStatement(), dbConn);
   return register;
 }
 // 查询所有
 public ArrayList<TRegister> searchAll() throws Exception {
   Connection dbConn = DataBaseTool.getConnection();
   String sql = "select * from t_register";
   ResultSet rs = DataBaseTool.executeQuery(dbConn, sql);
   ArrayList<TRegister> list = new ArrayList<TRegister>();
   TRegister register = null;
   while (rs.next()) {
     register = new TRegister();
     setOne(register, rs);
     list.add(register);
   }
   DataBaseTool.close(rs, rs.getStatement(), dbConn);
   return list;
 }
 // 查询 top number
 public ArrayList<TTableRegister> searchGuahao() throws Exception {
   Connection dbConn = DataBaseTool.getConnection();
   String sql =
       "select top 20 a.date as date,d.name as doctorName,room.name as roomName,a.expense as expense,r.id as registerId,r.arrangement_id as arrangementId,r.user_id as userId,r.type as type,r.number as number from t_arrangement as a,t_register as r,t_doctor as d,t_room as room "
           + " where a.id=r.arrangement_id and a.doctor_id=d.id and a.room_id=room.id and r.type="
           + TRegister.TYPE_GUAHAO
           + " order by date desc";
   ResultSet rs = DataBaseTool.executeQuery(dbConn, sql);
   ArrayList<TTableRegister> list = new ArrayList<TTableRegister>();
   TTableRegister item = null;
   while (rs.next()) {
     item = new TTableRegister();
     setOneTableRegister(item, rs);
     list.add(item);
   }
   DataBaseTool.close(rs, rs.getStatement(), dbConn);
   return list;
 }
 // 添加
 public int add(TRegister register) throws Exception {
   Connection dbConn = DataBaseTool.getConnection();
   String sql =
       "insert into t_register(user_id,arrangement_id,number,type,validate) values("
           + register.getUserId()
           + ","
           + register.getArrangementId()
           + ",'"
           + register.getNumber()
           + "',"
           + register.getType()
           + ","
           + register.getValidate()
           + ")";
   int count = DataBaseTool.executeSQL(dbConn, sql);
   DataBaseTool.close(null, null, dbConn);
   return count;
 }
 // 查询预约
 public ArrayList<TTableRegister> searchYuyueByCardnum(String cardnum) throws Exception {
   Connection dbConn = DataBaseTool.getConnection();
   String sql =
       "select a.date as date,d.name as doctorName,room.name as roomName,a.expense as expense,r.id as registerId,r.arrangement_id as arrangementId,r.user_id as userId,r.type as type,r.number as number from t_arrangement as a,t_register as r,t_doctor as d,t_room as room,t_user as u "
           + "where a.id=r.arrangement_id and a.doctor_id=d.id and a.room_id=room.id and r.user_id=u.id and DATEDIFF(DAY,a.date,GETDATE())=0 and u.cardnum='"
           + cardnum
           + "' and r.type="
           + TRegister.TYPE_YUYUE;
   ResultSet rs = DataBaseTool.executeQuery(dbConn, sql);
   ArrayList<TTableRegister> list = new ArrayList<TTableRegister>();
   TTableRegister item = null;
   while (rs.next()) {
     item = new TTableRegister();
     setOneTableRegister(item, rs);
     list.add(item);
   }
   DataBaseTool.close(rs, rs.getStatement(), dbConn);
   return list;
 }
 // 预约操作:调用存储过程 ProcExcel
 public String callProcYuyue(int userid, int aid, int type, int validate, String number)
     throws SQLException {
   System.out.println("call yuyue");
   Connection dbConn = DataBaseTool.getConnection();
   CallableStatement statement = dbConn.prepareCall("{call p_useryuyue(?,?,?,?,?,?,?,?,?)}");
   statement.setInt(1, SystemUtil.COUNT1_VALUE);
   statement.setInt(2, SystemUtil.COUNT2_VALUE);
   statement.setInt(3, SystemUtil.COUNT3_VALUE);
   statement.setInt(4, userid);
   statement.setInt(5, aid);
   statement.setInt(6, type);
   statement.setInt(7, validate);
   statement.setString(8, number);
   statement.registerOutParameter(9, java.sql.Types.VARCHAR);
   statement.execute();
   String result = statement.getString(9);
   DataBaseTool.close(null, statement, dbConn);
   return result;
 }
 // 更新
 public int update(TRegister register) throws Exception {
   Connection dbConn = DataBaseTool.getConnection();
   String sql =
       "update t_register set user_id="
           + register.getUserId()
           + " , arrangement_id="
           + register.getArrangementId()
           + " , type="
           + register.getType()
           + " , validate="
           + register.getValidate()
           + " , number='"
           + register.getNumber()
           + "' where id="
           + register.getId();
   int count = DataBaseTool.executeSQL(dbConn, sql);
   DataBaseTool.close(null, null, dbConn);
   return count;
 }
 // 查询预约
 public ArrayList<TTableRegister> search(int userid, boolean isAll) throws Exception {
   Connection dbConn = DataBaseTool.getConnection();
   StringBuffer sql = new StringBuffer("select ");
   if (!isAll) {
     sql.append(" top 3 ");
   }
   sql.append(
       " a.date as date,d.name as doctorName,room.name as roomName,a.expense as expense,r.id as registerId,r.arrangement_id as arrangementId,r.user_id as userId,r.type as type,r.number as number from t_arrangement as a,t_register as r,t_doctor as d,t_room as room "
           + " where r.user_id="
           + userid
           + " and a.id=r.arrangement_id and a.doctor_id=d.id and a.room_id=room.id and r.type="
           + TRegister.TYPE_YUYUE
           + " order by date desc");
   ResultSet rs = DataBaseTool.executeQuery(dbConn, sql.toString());
   ArrayList<TTableRegister> list = new ArrayList<TTableRegister>();
   TTableRegister item = null;
   while (rs.next()) {
     item = new TTableRegister();
     setOneTableRegister(item, rs);
     list.add(item);
   }
   DataBaseTool.close(rs, rs.getStatement(), dbConn);
   return list;
 }